Python operations Excel

The legendary Python operation MS Office features the most powerful is the win32com (it is said that as long as the human can operate it can be achieved, not yet tried not to know true or false), but for the more simple requirements seem a little fuss. So take a look at the simple, xlrd and XLWT modules, respectively. (already included in Python)

Xlrd

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

Simple to use

Import

Import xlrd

Open Excel

data = Xlrd.open_workbook (' Demo.xls ') #注意这里的workbook首字母是小写

View the name of the file that contains the sheet

Data.sheet_names ()

Get the first worksheet, or by index order or sheet name

Table = data.sheets () [0]

Table = Data.sheet_by_index (0)

Table = data.sheet_by_name (U ' Sheet1 ')

Get the number of rows and columns

nrows = Table.nrows

Ncols = Table.ncols

Get the values (arrays) for the entire row and the entire column

Table.row_values (i)

Table.col_values (i)

Looping rows, getting the list of indexes

For rownum in range (table.nrows):

Print Table.row_values (rownum)

Cell

CELL_A1 = Table.cell (0,0). Value

CELL_C4 = Table.cell (2,3). Value

Use row and column indexes, respectively

CELL_A1 = Table.row (0) [0].value

CELL_A2 = Table.col (1) [0].value

A simple write

Row = 0

Col = 0

CType = 1 # type 0 empty,1 string, 2 number, 3 date, 4 Boolean, 5 error

Value = ' Lixiaoluo '

XF = 0 # Extended Formatting (default is 0)

Table.put_cell (Row, col, CType, value, XF)

Table.cell (0,0) # Text: U ' lixiaoluo '

Table.cell (0,0). Value # ' Lixiaoluo '

Xlwt

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

Simple to use

Import XLWT

Import XLWT

Create a new Excel file

File = XLWT. Workbook () #注意这里的Workbook首字母是大写, no words

Create a new sheet

Table = File.add_sheet (' sheet name ')

Write Data Table.write (rows, columns, value)

Table.write (0,0, ' test ')

If you repeat the operation on a cell, it throws a

Returns error:

# exception:attempt to overwrite cell:

# sheetname=u ' sheet 1 ' rowx=0 colx=0

So when opened, add cell_overwrite_ok=true to solve

Table = File.add_sheet (' sheet name ', cell_overwrite_ok=true)

Save File

File.save (' Demo.xls ')

In addition, use the style

style = XLWT. Xfstyle () #初始化样式

Font = XLWT. Font () #为样式创建字体

Font.Name = ' Times New Roman '

Font.Bold = True

Style.font = Font #为样式设置字体

Table.write (0, 0, ' Some bold times text ', style) # using styles

XLWT allows you to format cells or entire rows. You can also add links and formulas. You can read the source code, where there are 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.

A concrete example can be seen:

http://scienceoss.com/write-excel-files-with-python-using-xlwt/

Google Forums:

http://groups.google.com/group/python-excel/

Python operations Excel