How Python reads and writes Excel tables

Objective: To realize the reading, adding and modifying of Excel using Python.

Environment: Ubuntu 16.04 Python 3.5.2

Read and write the document in Python, usually the operation TXT file or can be opened with Notepad file, because this operation is very straightforward, do not need to import other modules, but if you want to work on the Excel table, you need to import other modules, including: xlrd (Read), XLWT (write), Xlutils (copy), typically these three modules, and need to be downloaded separately, http://pypi.python.org/pypi/module name.

Read the table:

The read only needs to import the XLRD module:

Import xlrdfilename = ' Test.xls ' # to xlrd open table to BookBook = Xlrd.open_workbook (filename) # gets one of the methods of the worksheet, using the subscript. Sheel_1 = Book.sheet_by_index (0) # One of the methods that prints the name of the first worksheet. Print ("Worksheet name (s):", Book.sheet_names () [0]) # Prints the number of worksheets in a tabular file print (' Book.nsheets ', book.nsheets) # Print Sheet name method Two and print the total number of rows and columns of the table. Print (' Sheel_1.name: ', Sheel_1.name, ' sheel_1.nrows: ', Sheel_1.nrows, ' Sheel_1.ncols: ', Sheel_1.ncols) # Prints the value of row 0 column 1 in the first worksheet, using subscript. Print (' A1: ', Sheel_1.cell_value (rowx=0,colx=1) #) # Another method of printing the cells print (' A2: ', Sheel_1.cell_value (0,2))

New in table:

New only need to import XLWT module:

Import xlwtfilename = ' test.xls ' book = XLWT. Workbook () sheet_1 = Book.add_sheet (' hello ') sheel_2 = Book.add_sheet (' word ') sheet_1.write (0,0, ' hello ') sheet_1.write (0,1, ' world ') Row1 = Sheet1.row (1) row1.write (0, ' A2 ') row1.write (1, ' B2 ') sheet_1.col (0). width = 10000 Sheet_2 = book.get_ Sheet (1) sheet_2.row (0). Write (0, ' sheet 2 A1 ') sheet_2.row (0). Write (1, ' sheet 2 B1 ') Sheet_2.flush_row_data () sheet_2. Write (1,0, ' Sheet 2 A3 ') sheet_2.col (0). width = 5000sheet_2.col (0). Hidden = True book.save (filename)

to modify a table that already exists :

General Modify Table step: Import module--XLRD read table--xlutils Copy read table--XLWT Modify table--XLWT Save table--delete old table.

Parsing: Excel at the same time read and write is not possible, careful will find Office Operation table is also this step, first read, if modified data, will be copied a copy, resulting in a temporary hidden file with $, the modification is on the temporary file operation, when the user saves the old file will be deleted, Name the temporary file as the old file name, it feels like reading and writing the table directly, so you do not have a normal closed table will have a temporary file, you do not save everything in the inside, may also be part.

Here's a demonstration of the actual Python application steps:

Import xlrdimport xlwtimport copy# file name variable filename = ' test.xls ' # read File Book_r = Xlrd.open_workbook (filename) # Copy original table Book_w = XL Utils.copy (Book_r) # The first worksheet that gets the file edited sheet_1 = Book_w.get_sheet (0) # defines what to enter text = ' This is a test of Ricky. ' # define the cell cells to write to the table, use subscript 1row = # to define the cell column number to write to the table, use subscript 2col = * To write the contents to the table Sheet_1.write (row, col, text) # Delete the original file os.remove (filename) # Save modified file as original file Book_w.save (filename)

Description

1) above so many definitions are to illustrate the position and meaning of the parameters, you can directly write the values to the parameter position, but the parameters can be easily reused variables, such as in the loop;

2) If you want to preserve the format of the source file, the open section needs to be written as:

Book_r = Xlrd.open_workbook (filename, formatting_info=true)

This article is from the "Rickyhul" blog, make sure to keep this source http://rickyh.blog.51cto.com/10934856/1943649

How Python reads and writes Excel tables