Python Excel operations

1. Systematic learning

For Excel operations, the Xlrd/xlwt modules are required. The system learning URL is as follows:

Using python to read and write Excel -- using xlrd

Official documentation

Python uses Xlrd/xlwt to Operate Excel

Use Python to read and write Excel files

Ii. Practice

I helped my friend deal with an excel file. Fortunately, the data volume is small and tens of thousands of pieces of data. The requirements are as follows:

Specify to select three columns and then select all the data that meets the requirements of> = 1 or <=-1 at the same time and save it to the new csv table.

The procedure is as follows:

#! /Usr/bin/env python #-*-coding: UTF-8-*-# @ Date: 21:47:56 # @ Function: specify to select three columns and then select all the data that meets the requirements of> = 1 or <=-1 at the same time and save it to the new csv table # @ Author: beginManimport osimport stringimport xlrdimport xlwtdef get_data (): "Get excel Data Source" file = r'c: \ Users \ Administrator \ Desktop \ pytool \ xlrd \ initial_log_data.xls '# change to your own path filepath = raw_input (U'. paste the xls file path into it, if the file has been specified in the program, press Enter to continue: ') is_valid = False # verify the file try: Filepath = [file, filepath] [filepath! = ''] Print filepath # determine whether the given path is in the xls format if OS. path. isfile (filepath): filename = OS. path. basename (filepath) if filename. split ('. ') [1] = 'xls': is_valid = True data = None if is_valid: data = xlrd. open_workbook (filepath) failed t Exception, e: print U' your operation error: % s' % e return None return datadef handle_data (): "processing data" "data = get_data () if data: col_format = ['B', 'C ', 'D'] # specify the column "p" = raw_input (U'. Select the specified The three columns are separated by commas (,). The default values are B, C, and D (English comma, Case Insensitive). If the default value is selected, press Enter to continue: \ n'. try: P = P. split (',') col_format = [col_format, indium] [len ([I for I in indium if I in string. letters]) = 3] col_format = [I. upper () for I in col_format] # convert to big write table = data. sheet_by_index (0) # select the first workspace nrows = table. nrows # number of rows ncols = table. ncols # Number of columns str_upcase = [I for I in string. uppercase] # all uppercase letters I _upcase = range (len (str_upcase) # corresponding numbers Ncols_dir = dict (zip (str_upcase, I _upcase) # convert to the dictionary col_index = [ncols_dir.get (I) for I in col_format] # obtain the index corresponding to the specified column # Whether the selected three columns meet both the requirements of> = 1 or <=-1 print U ...... 'Count = 0 result = [] for I in xrange (nrows): cell_0 = table. cell (I, col_index [0]). value cell_1 = table. cell (I, col_index [1]). value cell_2 = table. cell (I, col_index [2]). value if (cell_0> = 1 and cell_1> = 1 and cell_2> = 1) or (cell_0 <=-1 and cell_1 <=-1 and cell_2 <=-1 ): result. append (table. row_values (I) # Add a qualified row to count + = 1 print U'. This file contains % s rows and % s columns, A total of % s of data entries matching the condition '% (nrows, ncols, count) print U' are writing data ...... 'Col_name = col_format [0] + col_format [1] + col_format [2] if write_data (result, col_name): print U' is written successfully! 'Failed t Exception, e: print U' your operation error: % s' % e return None else: print U' operation failed 'Return Nonedef write_data (data, name ): "write data. data is a list of qualified data. name indicates which three columns are specified. This name is" "file = xlwt. workbook () table = file. add_sheet (name, cell_overwrite_ OK = True) l = 0 # indicates the row for line in data: c = 0 # indicates the number of columns in a row for col in line: table. write (l, c, line [c]) c + = 1 l + = 1 defatul_f = r 'C: \ Users \ Administrator \ Desktop \ pytool \ xlrd '# default path Path f = raw_input (U' select the path to save the file: Press enter to skip: ') f_name = R' \ %s.xls' % name filepath = [defatul_f + f_name, f + f_name] [f! = ''] File. save (filepath) return Truedef main (): handle_data () if _ name _ = '_ main _': main ()

The running result is as follows:

Source data

Processed data