How to Use python to process excel files (method summary)

Use python to automatically generate excel data files. Python processes excel files mainly through third-party module libraries xlrd, xlwt, xluntils, and pyExcelerator. In addition, python can also use win32com and openpyxl modules to automatically generate excel data files using python. Python processes excel files mainly through third-party module libraries xlrd, xlwt, xluntils, and pyExcelerator. In addition, python can also process excel files using win32com and openpyxl modules.

Method 1:

Luo asked me how to read data from excel, and then I made a record.

Excel Data graph (Mr. Luo said that the data should be kept confidential to the customer, and I wrote several lines of data as needed ):

Python reads the excel file code:

#! /Usr/bin/env python #-*-coding: UTF-8-*-# Read excel Data # Luo's requirements, take the data below the second row, import xlrddata = xlrd.open_workbook('test.xls ') # Open the xls file table = data. sheets () [0] # Open the first table nrows = table. nrows # obtain the number of rows in the table for I in range (nrows): # print row-by-row loop if I = 0: # skip the first row continueprint table. row_values (I) [: 13] # retrieve the first 13 Columns

When excel write operations are later used, record them

Method 2:

Use xlrd to read files and use xlwt to generate Excel files (you can control the format of cells in Excel ). However, when using xlrd to read an excel file, you cannot operate on it. When using xlwt to generate an excel file, you cannot modify it based on an existing excel file. to modify the file, you must use the xluntils module. The pyExcelerator module is similar to xlwt and can also be used to generate excel files.

1. [Code] test_xlrd.py

# Coding = UTF-8 #################################### #################### filename: test_xlrd.py # author: defias # date: xxxx-xx # function: read data from an excel file ################################### ################### import xlrd # open a workbookworkbook = xlrd. open_workbook ('e: \ Code \ Python \ testdata.xls ') # capture the names of all sheet pages. worksheets = workbook. sheet_names () print ('worksheets is % s' % worksheets) # locate sheet1worksheet1 = workbook. sheet_by_name (u 'sheet1') "# obtain worksheet1 = workbook in the order of indexes. sheets () [0] # Or worksheet1 = workbook. sheet_by_index (0) "# traverse all sheet objects for worksheet_name in worksheets: worksheet = workbook. sheet_by_name (worksheet_name) "# traverse all rows in sheet1 rownum_rows = worksheet1.nrowsfor curr_row in range (num_rows): row = worksheet1.row _ values (curr_row) print ('row % s is % s' % (curr_row, row) # traverse all columns in sheet1 colnum_cols = worksheet1.ncolsfor curr_col in range (num_cols ): col = worksheet1.col _ values (curr_col) print ('col % s is % s' % (curr_col, col) # traverse all cells in sheet1 cellfor rown in range (num_rows ): for coln in range (num_cols): cell = worksheet1.cell _ value (rown, coln) print cell "" # other Syntax: cell = worksheet1.cell (rown, coln ). valueprint cell # Or cell = worksheet1.row (rown) [coln]. valueprint cell # Or cell = worksheet1.col (coln) [rown]. valueprint cell # obtain the type of the median in a cell. The value type is 0 empty, 1 string, 2 number, 3 date, 4 boolean, 5 errorcell_type = worksheet1.cell _ type (rown, coln) print cell_type """

2. [Code] test_xlwt.py

# Coding = UTF-8 #################################### #################### filename: test_xlwt.py # author: defias # date: xxxx-xx # function: create an excel file and write data ################################## #################### import xlwt # create a workbook and sheet object workbook = xlwt. workbook () # note that W at the beginning of the Workbook should be capitalized sheet1 = workbook. add_sheet ('sheet1', cell_overwrite_ OK = True) sheet2 = workbook. add_sheet ('sheet2', cell_overwrite_ OK = True) # Forward to sheet Data sheet1.write (, 'this shoshould overwrite1 ') sheet1.write (, 'aaaaaaaaaaaaa') sheet2.write (, 'this shoshould overwrite2') sheet2.write (, 'bbbbbbbbbbbbbbbbbbbbbbbbbb ') "# ----------- use style ------------------------------------- # initialize style = xlwt. XFStyle () # create font = xlwt for the style. font () font. name = 'times New Roman 'font. bold = True # Set the style font style. font = font # Use the style sheet. write (, 'some bold Times text', style) "" # Save the excel file The excel file is created by overwriting workbook. save ('e: \ Code \ Python \ test2.xls ') print! '

3. [Code] test_xlutils.py

# Coding = UTF-8 #################################### #################### filename: test_xlutils.py # author: defias # date: xxxx-xx # function: write data into an excel file ################################## #################### import xlrdimport xlutils. copy # open a workbookrb = xlrd. open_workbook ('e: \ Code \ Python \ test1.xls ') wb = xlutils. copy. copy (rb) # Get the sheet object. The sheet object obtained through sheet_by_index () does not have the write () method ws = wb. get_sheet (0) # Write Data w S. write (1, 1, 'changed! ') # Add a sheet page wb. add_sheet ('sheetnnn2 ', cell_overwrite_ OK = True) # modify the excel file by overwriting the file with the same name during storage. Note that the unmodified content remains unchanged. wb. save ('e: \ Code \ Python \ test1.xls ')

4. [Code] test_pyExcelerator_read.py

# Coding = UTF-8 #################################### #################### filename: test_pyExcelerator_read.py # author: defias # date: xxxx-xx # function: read data from an excel file ################################### ################### import pyExcelerator # parse_xls returns a list, each item is the data of a sheet page. # Each item is a binary group (Table Name, cell data ). The cell data is a dictionary, and the key value is the index of the Cell (I, j ). If a cell has no data, the value sheets = pyExcelerator. parse_xls ('e: \ Code \ Python \ testdata.xls ') print sheets

5. [Code] test_pyExcelerator.py

# Coding = UTF-8 #################################### #################### filename: test_pyExcelerator.py # author: defias # date: xxxx-xx # function: create an excel file and write data ################################## #################### import pyExcelerator # create a workbook and a sheet object wb = pyExcelerator. workbook () ws = wb. add_sheet (u'page 1 ') # Set the style myfont = pyExcelerator. font () myfont. name = u'times New Roman 'myfont. bold = Truemystyle = pyExce Lerator. XFStyle () mystyle. font = myfont # write data using the style ws. write (, u'ni hao Passau! ', Mystyle) # Save the excel file. If there is a file with the same name, it will overwrite wb directly. save ('e: \ Code \ Python \ mini.xls ') print' the excel file has been created! '