Manipulating Excel with Python

1. Use the XLRD module to read Excel

1) Installing the XLRD module

wget HTTPS://PYPI.PYTHON.ORG/PACKAGES/SOURCE/X/XLRD/XLRD-0.9.3.TAR.GZTAR-XZVF XLRD-0.9.3.TAR.GZCD Xlrd-0.9.3python setup.py Install

2) Import Module

Import xlrd

3) Open Excel file to read data

data = Xlrd.open_workbook (' Excelfile.xls ')

4) Tips for use

4.1 Get a worksheet

Table = data.sheets () [0] #通过索引顺序获取table = data.sheet_by_index (0) #通过索引顺序获取table = data.sheet_by_name (U ' Sheet1 ') #通过名称获取

4.2) Gets the value (array) of the entire row and column

Table.row_values (i) #数据类型为list table.col_values (i)

4.3) Get the number of rows and columns

nrows = Table.nrowsncols = Table.ncols

4.4) Cyclic row list data

For I in Range (nrows): Print table.row_values (i)

4.5) Get the value of the cell

CELL_A1 = Table.cell (0,0). Valuecell_c4 = Table.cell (2,3). Value

2. Writing to Excel using XLWD

1) Installing the XLWD module

wget HTTPS://PYPI.PYTHON.ORG/PACKAGES/SOURCE/X/XLWT/XLWT-0.7.5.TAR.GZTAR-XZVF XLWT-0.7.5.TAR.GZCD Xlwt-0.7.5python setup.py Install

2) Import XLWT

Import XLWT

3) Create a new Excel file

File = XLWT. Workbook () #注意这里的Workbook首字母是大写file = Workbook (encoding= ' utf-8 '), you can output Chinese in Excel, the default is ASCII

4) Create a new sheet

Table = File.add_sheet (' sheet name ')

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)

5) Write Data table.write (row, column, value)

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

6) Save File

File.save (' Demo.xls ')

In addition, use the style

style = XLWT. Xfstyle () # Initialize style

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

This article from "Leboit" blog, declined reprint!

Manipulating Excel with Python