Python implementation of Excel Read-write (implemented using the xlrd Module)

first, Install the XLRD module

The installation assumes that the Python environment is already installed:

1. Download HTTP://PYPI.PYTHON.ORG/PYPI/XLRD download package to Python website

2. Unzip the downloaded Compression pack

3. CD to unzip directory c:\users\lyj>cd/d G:\Python34\Lib\xlrd-1.0.0, execute python setup.py install

　　

second, the use of the introduction

1. Import Module

Import xlrd

2. Open Excel file to read data

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

3, the use of skills

Get a worksheet

Table = data.sheets () [0] #通过索引顺序获取 table = Data.sheet_by_index (0) #通过索引顺序获取

Table = Data.sheet_by_name (u ' Sheet1 ') #通过名称获取 get the values (arrays) for the entire row and the entire columnTable.row_values (i) table.col_values (i) get the number of rows and columnsnrows = table.nrows Ncols = Table.ncols cyclic row and column table dataFor I in range (nrows): print table.row_values (i) Cell cellCELL_A1 = Table.cell (0,0). Value cell_c4 = Table.cell (2,3). value using row and column indexesCELL_A1 = Table.row (0) [0].value cell_a2 = Table.col (1) [0].value a simple writerow = 0 col = 0 # type 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 Error CType = 1 value = ' cell values ' XF = 0 # extended lattice Type Table.put_cell (row, col, ctype, value, xf) table.cell (0,0) #单元格的值 ' Table.cell (0,0). value #单元格的值 '

three, Demo Code--example

The demo code is simple enough to read the Excel Data.

1 #-*-coding:utf-8-*-
2 Import xdrlib, sys
3 Import Xlrd
4 def open_excel (file= ' File.xls '):
5 try:
6 data = Xlrd.open_workbook (file)
7 Return Data
8 except Exception,e:
9 Print str (e)

10#根据索引获取Excel表格中的数据 parameter: file:excel file path colnameindex: index of row of table header column name, by_index: index of table
One def excel_table_byindex (file= ' File.xls ', colnameindex=0,by_index=0):
data = Open_excel (file)
Table = Data.sheets () [by_index]
nrows = Table.nrows #行数
Ncols = Table.ncols #列数
colnames = table.row_values (colnameindex) #某一行数据
List =[]
RowNum in range (1,nrows):
19
row = Table.row_values (rownum)
If Row:
App = {}
In range (len (colnames)):
[app[colnames[i]] = row[i]
List.append (app)
Return list
27
28#根据名称获取Excel表格中的数据 parameter: file:excel file path colnameindex: The index of the row that contains the header column name, by_name:sheet1 name
def excel_table_byname (file= ' File.xls ', colnameindex=0,by_name=u ' Sheet1 '):
data = Open_excel (file)
Table = Data.sheet_by_name (by_name)
nrows = Table.nrows #行数
colnames = table.row_values (colnameindex) #某一行数据
List =[]
RowNum in range (1,nrows):
row = Table.row_values (rownum)
PNS If Row:
App = {}
A-i in range (len (colnames)):
app[colnames[i]] = row[i]
List.append (app)
Return list
43
A. def main ():
tables = Excel_table_byindex ()
Tables for Row in:
Print row
48
tables = Excel_table_byname ()
For row in Tables:
Print row
52
If __name__== "__main__":
The main ()

Python implementation of Excel Read-write (implemented using the xlrd Module)