Python read and Write Excel file method introduction

One, read Excel

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

First, open the workbook;
Copy the Code code as follows:

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

Check the name of the form:
Copy the Code code as follows:

Wb.sheet_names ()

Get the first form, two ways: Index and name
Copy CodeThe code is as follows:

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

Recursively print out each line of information:
Copy CodeThe code is 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 CodeThe code is as follows:

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

Note: The indexes here are all starting from 0.

Second, write Excel

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

Basic section

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

Copy the Code code as follows:

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

The form is created and the data is simple to write:

Copy the Code code as follows:

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

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

Wbk.save (' Test.xls ')

Explore in depth

Worksheet object, there is a warning prompt when you change the contents of the form.

Copy the 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 the worksheet:

Copy the 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 ')

This allows you to change the contents of the form 2.

More:

Copy the 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, 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 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, showing how to set different data formats
hyperlinks.py, showing 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 an Excel file, use XLWT (Excel write) to generate an Excel file (you can control the formatting of cells in Excel), and be aware that xlrd reads It is not possible to manipulate Excel: The Xlrd.open_workbook () method returns XLRD. Book type, which is read-only and cannot be manipulated. and XLWT. Workbook () returns the XLWT. The workbook type of Save (filepath) method can save an Excel file.

So it's easy to handle reading and generating Excel files, but making changes to an existing Excel file can be tricky. However, there is also a xlutils (dependent on xlrd and XLWT) that provides the ability to copy the contents of an Excel file and modify the file. Its reality is only in xlrd. There was a pipeline between book and Xlwt.workbook, such as:

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

Copy the Code code as follows:

From XLRD import Open_workbook
From xlutils.copy Import copy

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

The sheet () obtained by the #通过sheet_by_index () does not have the write () method
Copy CodeThe code is as follows:

rs = rb.sheet_by_index (0)

WB = Copy (RB)

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

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

Iv. references

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

