Python xlrd, XLWT, xlutils read, modify Excel files

Python xlrd, XLWT, xlutils read, modify Excel files

One, xlrd 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;
Import xlrd
WB = Xlrd.open_workbook (' Myworkbook.xls ')

Check the name of the form:
Wb.sheet_names ()

Get the first form, two ways: Index and name
SH = wb.sheet_by_index (0)
SH = wb.sheet_by_name (U ' Sheet1 ')

Recursively print out each line of information:
For rownum in range (sh.nrows):
Print Sh.row_values (rownum)

If you want to return only the first column of data:
First_column = sh.col_values (0)

To read data by index:
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, XLWT 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:
Import XLWT
WBK = XLWT. Workbook ()
Sheet = wbk.add_sheet (' Sheet 1 ')

The form is created and the data is simple to write:
# 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):
Wbk.save (' Test.xls ')

Explore in depth

Worksheet object, there is a warning prompt when you change the contents of the form.
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:
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
# 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.

Three xlutils modify Excel

Python typically uses xlrd (excel reaD) to read Excel files using XLWT (excel writ e) to generate an Excel file (you can control the formatting of cells in Excel), it is important to note that reading Excel with XLRD cannot manipulate it: 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:

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

Four references

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

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

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

This article integrates self-

http://www.leyond.info/write-excel-files-with-python-using-xlwt/

http://www.leyond.info/read-excel-from-python-using-xlrs/

http://www.zhlwish.com/2010/10/09/python_edit_excel/

Python xlrd, XLWT, xlutils read, modify Excel files