Python Excel operations

I. Read Operation--XLRD module

Import xlrd

Open a Worksheet

Wkb=xlrd.open_workbook (' Test.xls ')

Get sheet action

Wkb.sheet_names ()

Sheet=wkb.sheets () [0]

Sheet=wkb.sheet_by_index (0)

Sheet=wkb.sheet_by_name (' Sheet1 ')

Sheet operation gets the cell value in several ways:

Nrows=sheet.nrows Total number of rows

Ncols=sheet.ncols Total number of columns

Sheet.row_values (i) the value of line I

Sheet.col_values (i) The value of column I

Sheet.cell. Value 2nd row 3 column

Sheet.cell_value (3) The value of row 2nd column

Sheet.row (1) [2].value 2nd row 3 column value

Sheet.col (2) [1].value 2nd row 3 column value

Example one:

Text.xls table contents are as follows

Zhzhgo	25
Sister	28
Brother	30

Import Xlrdwbk=xlrd.open_workbook (' Test.xls ') #print wbk.sheet_names () #print wbk.sheets () [0] #print Wbk.sheet_by_ Index (0) st=wbk.sheet_by_name (' Sheet1 ') print st.nrows #获取表格行数print st.ncols #获取表格列数print st.row_values (0) # The first row of data print st.col_values (0) #第一列的数据print St.cell (0,0). Value #第一行一列的数据

>>>

3

2

[u ' Zhzhgo ', 25.0]

[u ' Zhzhgo ', U ' sister ', U ' brother ']

Zhzhgo

>>>

Two. Write Operation--XLWT Module

Import XLWT

WBK=XLWT. Workbook () Capital W

Sheet=wbk.add_sheet (' Sheet 1 ', cell_overwrite_ok=true)

Sheet.write (0,1, ' content ')

Wbk.save (' Test.xls ') only supports saving XLS format

Custom styles

STYLE=XLWT. Xfstyle ()

FONT=XLWT. Font ()

Font.name= ' Times New Roman '

Font.bold=true

Style.font=font

Sheet.write= (0,0, ' content ', style)

STYLE1=XLWT.EASYXF (' Pattern:pattern solid,fore_colour red;font:bold on; ')

Example two:

Import XLWTWBK=XLWT. Workbook () st=wbk.add_sheet (' Test ', cell_overwrite_ok=true) #如果test存在则覆盖st. Write (0,0, ' Zhzhgo ') st.write (0,1,25) STYLE=XLWT. Xfstyle () font=xlwt. Font () font.name= ' Times New Roman ' Font.bold=true #加粗style. FONT=FONTSTYLE1=XLWT.EASYXF (' Pattern:pattern solid,fore_ Colour red;font:bold on; ') St.write (1,0, ' Zhzhgo ', style) #设置字体样式st. Write (1,1,25,style1) #设置字体样式和背景颜色wbk. Save (' Test1.xls ')

After running, generate the Test1.xls file content style as follows:

Zhzhgo	25
Zhzhgo	25

Since XLWT creates a new XLS file each time it is saved, if the file does not exist then it is created, the presence is overwritten, so the update operation is not possible, and Python provides a more module to update the Excel operation.

Three. Modify Operation--xlutils Module

Xlutils.copy

From xlutils import Copy as Xcopy

Wkb_rd=xlrd.open_workbook (' Test.xls ')

Wkb_cp=xcopy.copy (WKB_RD) copies a copy of XLRD to XLWT

Sheet=wkb_cp.get_sheet (0)

Sheet.write (Row,col,value)

Wkb_cp.save (' Test_cp.xls ') only supports saving XLS format

Example three:

Import xlrdfrom xlutils Import copy wkb_rd=xlrd.open_workbook (' Test.xls ') wkb_cp=copy.copy (WKB_RD) sheet=wkb_cp.get_ Sheet (0) sheet.write (3,0, ' mother ') sheet.write (3,1, ' a ') wkb_cp.save (' Test.xls ')

After running the contents of the TEST.SLX file are as follows, adding a row of inserted data:

Zhzhgo	25
Sister	28
Brother	30
Mother	50

This article from "Today's efforts, tomorrow's success!" "Blog, be sure to keep this provenance http://zhzhgo.blog.51cto.com/10497096/1679858

Python Excel operations