Python read and Write Excel file method introduction _python

First, read Excel

Here is a nice package xlrs that can work on any platform. This means that you can read Excel files under Linux.

First, open the workbook;

Copy Code code as follows:

Import xlrd
WB = Xlrd.open_workbook (' Myworkbook.xls ')

Check the name of the form:

Copy Code code as follows:

Wb.sheet_names ()

Get the first form, two ways: Index and name

Copy Code code as follows:

SH = wb.sheet_by_index (0)
SH = wb.sheet_by_name (U ' Sheet1 ')

Recursively prints out each line of information:

Copy Code code as follows:

For rownum in range (sh.nrows):
Print Sh.row_values (rownum)

If you want to return only the first column of data:

Copy Code code as follows:

First_column = sh.col_values (0)
[Code]
Reading data by index:
[Code]
CELL_A1 = Sh.cell (0,0). Value
CELL_C4 = Sh.cell (rowx=3,colx=2). Value

Note: The index here starts at 0.

Second, write Excel

Here is a nice package xlwt that can work on any platform. This means you can save Excel files under Linux.

Basic part

Before you write to the Excel table, you must initialize the Workbook object, and then add a Workbook object. Like what:

Copy Code code as follows:

Import XLWT
WBK = XLWT. Workbook ()
Sheet = wbk.add_sheet (' Sheet 1 ')

So that the form is created and the data written is simple:

Copy Code code as follows:

# indexing is zero based, row then column
Sheet.write (0,1, ' Test text ')

After that, you can save the file (you don't need the close file as you want to open the file here):

Copy Code code as follows:

Wbk.save (' Test.xls ')

Deep Exploration

Worksheet object, when you change the content of the form, there will be a warning prompt.

Copy Code code as follows:

Sheet.write (0,0, ' test ')
Sheet.write (0,0, ' oops ')

# returns Error:
# exception:attempt to overwrite cell:
# sheetname=u ' sheet 1 ' rowx=0 colx=0

Workaround: Use Cell_overwrite_ok=true to create worksheet:

Copy Code code as follows:

Sheet2 = Wbk.add_sheet (' Sheet 2 ', cell_overwrite_ok=true)
Sheet2.write (0,0, ' some text ')
Sheet2.write (0,0, ' This should overwrite ')

So you can change the contents of form 2.

More:

Copy Code code as follows:

# Initialize a Style
style = XLWT. Xfstyle ()

# Create a font to use with the style
Font = XLWT. Font ()
Font.Name = ' Times New Roman '
Font.Bold = True

# Set the style ' s font to this new one your set up
Style.font = Font

# Use the ' style ' when writing
Sheet.write (0, 0, ' Some bold times text ', style)

XLWT allows you to format each grid or whole line. You can also allow you to add links and formulas. You can actually read the source code, where there are many examples:

dates.py, show how to set up different data formats
hyperlinks.py, show how to create a hyperlink (Hint:you need to use a formula)
merged.py, show how to merge lattice
row_styles.py, shows how to apply a style to an entire row of squares.

Third, modify Excel

In Python, you typically use XLRD (Excel read) to read Excel files, use XLWT (Excel write) to generate Excel files (you can control the formatting of cells in Excel), and note that using XLRD to read You cannot take Excel to manipulate it: the Xlrd.open_workbook () method returns XLRD. The book type is read-only and cannot be manipulated. and XLWT. Workbook () returns the XLWT. The workbook type's save (filepath) method can save Excel files.

So it's easy to read and build Excel files, but it's cumbersome to make changes to an existing Excel file. However, there is also a xlutils (dependent on xlrd and XLWT) that provides the ability to copy the contents of the Excel file and modify the file. It is actually only in xlrd. A pipe was established between book and Xlwt.workbook, as shown in the following figure:

The copy () method of the Xlutils.copy module implements this feature, and the sample code is as follows:

Copy Code code as follows:

From XLRD import Open_workbook
From xlutils.copy Import copy

RB = Open_workbook (' M:\\1.xls ')

The sheet () obtained by #通过sheet_by_index () does not have a write () method

Copy Code code as follows:

rs = rb.sheet_by_index (0)

WB = Copy (RB)

The sheet () obtained by #通过get_sheet () has the Write () method
WS = Wb.get_sheet (0)
Ws.write (0, 0, ' changed! ')

Wb.save (' M:\\1.xls ')

Iv. Reference

Http://pypi.python.org/pypi/xlrd
Http://pypi.python.org/pypi/xlwt
Http://pypi.python.org/pypi/xlutils