用python读写excel（xlrd、xlwt）

曾经终败给现在 2022-08-01 15:57 313阅读 0赞

最近需要从多个excel表里面用各种方式整理一些数据，虽然说原来用过java做这类事情，但是由于最近在学python，所以当然就决定用python尝试一下了。发现python果然简洁很多。这里简单记录一下。（由于是用到什么学什么，所以不算太深入，高手勿喷，欢迎指导）

一、读excel表

读excel要用到xlrd模块，官网安装（[http://pypi.python.org/pypi/xlrd][http_pypi.python.org_pypi_xlrd]）。然后就可以跟着里面的例子稍微试一下就知道怎么用了。大概的流程是这样的：

1、导入模块

import xlrd

2、打开Excel文件读取数据

data = xlrd.open\_workbook('excel.xls')

3、获取一个工作表

1 table = data.sheets()\[0\] \#通过索引顺序获取  
2 table = data.sheet\_by\_index(0) \#通过索引顺序获取  
3 table = data.sheet\_by\_name(u'Sheet1')\#通过名称获取

4、获取整行和整列的值（返回数组）

table.row\_values(i)

table.col\_values(i)

5、获取行数和列数

table.nrows

table.ncols

6、获取单元格

table.cell(0,0).value

table.cell(2,3).value

就我自己使用的时候觉得还是获取cell最有用，这就相当于是给了你一个二维数组，余下你就可以想怎么干就怎么干了。得益于这个十分好用的库代码很是简洁。但是还是有若干坑的存在导致话了一定时间探索。现在列出来供后人参考吧：

1、首先就是我的统计是根据姓名统计各个表中的信息的，但是调试发现不同的表中各个名字貌似不能够匹配，开始怀疑过编码问题，不过后来发现是因为　　空格。因为在excel中输入的时候很可能会顺手在一些名字后面加上几个空格或是tab键，这样看起来没什么差别，但是程序处理的时候这就是两个完全　　不同的串了。我的解决方法是给每个获取的字符串都加上strip（）处理一下。效果良好

2、还是字符串的匹配，在判断某个单元格中的字符串（中文）是否等于我所给出的的时候发现无法匹配，并且各种unicode也不太奏效，百度过一些解决　　方案，但是都比较复杂或是没用。最后我采用了一个比较变通的方式：直接从excel中获取我想要的值再进行比较，效果是不错就是通用行不太好，个　　呢不能问题还没解决。

二、写excel表

写excel表要用到xlwt模块，官网下载（http://pypi.python.org/pypi/xlwt）。大致使用流程如下：

1、导入模块

import xlwt

2、创建workbook（其实就是excel，后来保存一下就行）

`　　workbook = xlwt.Workbook(encoding = 'ascii')`

`3、创建表 worksheet = workbook.add_sheet('My Worksheet')`

4、往单元格内写入内容

`　　worksheet.write(0, 0, label = 'Row 0, Column 0 Value')`

5、保存

`　　workbook.save('Excel_Workbook.xls')`

由于我的需求比较简单，所以这上面没遇到什么问题，唯一的就是建议还是用ascii编码，不然可能会有一些诡异的现象。

python中对Excel进行读写操作实例  
分别进入到xlrd和xlwt文件中对setup.py进行安装，安装命令为setup.py install  
进入python解释器，输入import xlwt,正常无报错即可  
下面是我写的一个将Excle文件中的数据读取到普通文本中，和从普通文本写到Excel的一个互换程序:  
 1 \#encoding:utf8  
 2 import xlrd  
 3 import xlwt  
 4  
 5 class OperExcel():  
 6 \#读取Excel表  
 7 def rExcel(self,inEfile,outfile):  
 8 rfile = xlrd.open\_workbook(inEfile)  
 9 \#创建索引顺序获取一个工作表  
10 table = rfile.sheet\_by\_index(0)  
11 \#其他方式  
12 \#table = rfile.sheets()\[0\]  
13 \#table = rfile.sheet\_by\_name(u'Sheet1')  
14  
15 \#获取整行，整列的值  
16 table.row\_values(0)  
17 table.col\_values(0)  
18  
19 \#获取行数和列数  
20 nrows = table.nrows - 1  
21 ncols = table.ncols  
22  
23 \#循环获取列表的数据  
24 \#for i in range(nrows):  
25 \# print table.row\_values(i)  
26 wfile = open(outfile,'w')  
27 \#获取第一列中的所有值  
28 for i in range(nrows):  
29 \#table.cell(i,0).value获取某一单元格的值  
30 wfile.write(table.cell(i,0).value.encode('utf8') + '\\n')  
31 wfile.close()  
32  
33 \#将数据写入Excel表  
34 def wExcel(self,infile,outEfile):  
35 rfile = open(infile,'r')  
36 buf = rfile.read().split('\\n')  
37 rfile.close()  
38  
39 w = xlwt.Workbook()  
40 sheet = w.add\_sheet('sheet1')  
41 for i in range(len(buf)):  
42 print buf\[i\]  
43 sheet.write(i,0,buf\[i\].decode('utf8'))  
44 w.save(outEfile)  
45  
46 if \_\_name\_\_ == '\_\_main\_\_':  
47 t = OperExcel()  
48 t.rExcel('test.xls','test')  
49 t.wExcel('test','1.xls')

当然xlwt功能远远不止这些，他甚至可以设置各种样式之类的。附上一点例子

1 Examples Generating Excel Documents Using Python’s xlwt  
2  
3 Here are some simple examples using Python’s xlwt library to dynamically generate Excel documents.  
4  
5 Please note a useful alternative may be ezodf, which allows you to generate ODS (Open Document Spreadsheet) files for LibreOffice / OpenOffice. You can check them out at:http://packages.python.org/ezodf/index.html  
6  
7 The Simplest Example  
8 import xlwt  
9 workbook = xlwt.Workbook(encoding = 'ascii')  
10 worksheet = workbook.add\_sheet('My Worksheet')  
11 worksheet.write(0, 0, label = 'Row 0, Column 0 Value')  
12 workbook.save('Excel\_Workbook.xls')  
13  
14 Formatting the Contents of a Cell  
15 import xlwt  
16 workbook = xlwt.Workbook(encoding = 'ascii')  
17 worksheet = workbook.add\_sheet('My Worksheet')  
18 font = xlwt.Font() \# Create the Font  
19 font.name = 'Times New Roman'  
20 font.bold = True  
21 font.underline = True  
22 font.italic = True  
23 style = xlwt.XFStyle() \# Create the Style  
24 style.font = font \# Apply the Font to the Style  
25 worksheet.write(0, 0, label = 'Unformatted value')  
26 worksheet.write(1, 0, label = 'Formatted value', style) \# Apply the Style to the Cell  
27 workbook.save('Excel\_Workbook.xls')  
28  
29 Attributes of the Font Object  
30 font.bold = True \# May be: True, False  
31 font.italic = True \# May be: True, False  
32 font.struck\_out = True \# May be: True, False  
33 font.underline = xlwt.Font.UNDERLINE\_SINGLE \# May be: UNDERLINE\_NONE, UNDERLINE\_SINGLE, UNDERLINE\_SINGLE\_ACC, UNDERLINE\_DOUBLE, UNDERLINE\_DOUBLE\_ACC  
34 font.escapement = xlwt.Font.ESCAPEMENT\_SUPERSCRIPT \# May be: ESCAPEMENT\_NONE, ESCAPEMENT\_SUPERSCRIPT, ESCAPEMENT\_SUBSCRIPT  
35 font.family = xlwt.Font.FAMILY\_ROMAN \# May be: FAMILY\_NONE, FAMILY\_ROMAN, FAMILY\_SWISS, FAMILY\_MODERN, FAMILY\_SCRIPT, FAMILY\_DECORATIVE  
36 font.charset = xlwt.Font.CHARSET\_ANSI\_LATIN \# May be: CHARSET\_ANSI\_LATIN, CHARSET\_SYS\_DEFAULT, CHARSET\_SYMBOL, CHARSET\_[APP][]LE\_ROMAN, CHARSET\_ANSI\_JAP\_SHIFT\_JIS, CHARSET\_ANSI\_KOR\_HANGUL, CHARSET\_ANSI\_KOR\_JOHAB, CHARSET\_ANSI\_CHINESE\_GBK, CHARSET\_ANSI\_CHINESE\_BIG5, CHARSET\_ANSI\_GREEK, CHARSET\_ANSI\_TURKISH, CHARSET\_ANSI\_VIETNAMESE, CHARSET\_ANSI\_HEBREW, CHARSET\_ANSI\_ARABIC, CHARSET\_ANSI\_BALTIC, CHARSET\_ANSI\_CYRILLIC, CHARSET\_ANSI\_THAI, CHARSET\_ANSI\_LATIN\_II, CHARSET\_OEM\_LATIN\_I  
37 font.colour\_index = ?  
38 font.get\_biff\_record = ?  
39 font.height = 0x00C8 \# C8 in Hex (in decimal) = 10 points in height.  
40 font.name = ?  
41 font.outline = ?  
42 font.shadow = ?  
43  
44 Setting the Width of a Cell  
45 import xltw  
46 workbook = xlwt.Workbook()  
47 worksheet = workbook.add\_sheet('My Sheet')  
48 worksheet.write(0, 0, 'My Cell Contents')  
49 worksheet.col(0).width = 3333 \# 3333 = 1" (one inch).  
50 workbook.save('Excel\_Workbook.xls')  
51  
52 Entering a Date into a Cell  
53 import xlwt  
54 import datetime  
55 workbook = xlwt.Workbook()  
56 worksheet = workbook.add\_sheet('My Sheet')  
57 style = xlwt.XFStyle()  
58 style.num\_format\_str = 'M/D/YY' \# Other options: D-MMM-YY, D-MMM, MMM-YY, h:mm, h:mm:ss, h:mm, h:mm:ss, M/D/YY h:mm, mm:ss, \[h\]:mm:ss, mm:ss.0  
59 worksheet.write(0, 0, datetime.datetime.now(), style)  
60 workbook.save('Excel\_Workbook.xls')  
61  
62 Adding a Formula to a Cell  
63 import xlwt  
64 workbook = xlwt.Workbook()  
65 worksheet = workbook.add\_sheet('My Sheet')  
66 worksheet.write(0, 0, 5) \# Outputs 5  
67 worksheet.write(0, 1, 2) \# Outputs 2  
68 worksheet.write(1, 0, xlwt.Formula('A1\*B1')) \# Should output "10" (A1\[5\] \* A2\[2\])  
69 worksheet.write(1, 1, xlwt.Formula('SUM(A1,B1)')) \# Should output "7" (A1\[5\] + A2\[2\])  
70 workbook.save('Excel\_Workbook.xls')  
71  
72 Adding a Hyperlink to a Cell  
73 import xlwt  
74 workbook = xlwt.Workbook()  
75 worksheet = workbook.add\_sheet('My Sheet')  
76 worksheet.write(0, 0, xlwt.Formula('HYPERLINK("http://www.[google][].com";"[Google][google]")')) \# Outputs the text "Google" linking to http://www.google.com  
77 workbook.save('Excel\_Workbook.xls')  
78  
79 Merging Columns and Rows  
80 import xlwt  
81 workbook = xlwt.Workbook()  
82 worksheet = workbook.add\_sheet('My Sheet')  
83 worksheet.write\_merge(0, 0, 0, 3, 'First Merge') \# Merges row 0's columns 0 through 3.  
84 font = xlwt.Font() \# Create Font  
85 font.bold = True \# Set font to Bold  
86 style = xlwt.XFStyle() \# Create Style  
87 style.font = font \# Add Bold Font to Style  
88 worksheet.write\_merge(1, 2, 0, 3, 'Second Merge', style) \# Merges row 1 through 2's columns 0 through 3.  
89 workbook.save('Excel\_Workbook.xls')  
90  
91 Setting the Alignment for the Contents of a Cell  
92 import xlwt  
93 workbook = xlwt.Workbook()  
94 worksheet = workbook.add\_sheet('My Sheet')  
95 alignment = xlwt.Alignment() \# Create Alignment  
96 alignment.horz = xlwt.Alignment.HORZ\_CENTER \# May be: HORZ\_GENERAL, HORZ\_LEFT, HORZ\_CENTER, HORZ\_RIGHT, HORZ\_FILLED, HORZ\_JUSTIFIED, HORZ\_CENTER\_ACROSS\_SEL, HORZ\_DISTRIBUTED  
97 alignment.vert = xlwt.Alignment.VERT\_CENTER \# May be: VERT\_TOP, VERT\_CENTER, VERT\_BOTTOM, VERT\_JUSTIFIED, VERT\_DISTRIBUTED  
98 style = xlwt.XFStyle() \# Create Style  
99 style.alignment = alignment \# Add Alignment to Style  
100 worksheet.write(0, 0, 'Cell Contents', style)  
101 workbook.save('Excel\_Workbook.xls')  
102  
103 Adding Borders to a Cell  
104 \# Please note: While I was able to find these constants within the source code, on my system (using LibreOffice,) I was only presented with a solid line, varying from thin to thick; no dotted or dashed lines.  
105 import xlwt  
106 workbook = xlwt.Workbook()  
107 worksheet = workbook.add\_sheet('My Sheet')  
108 borders = xlwt.Borders() \# Create Borders  
109 borders.left = xlwt.Borders.DASHED \# May be: NO\_LINE, THIN, MEDIUM, DASHED, DOTTED, THICK, DOUBLE, HAIR, MEDIUM\_DASHED, THIN\_DASH\_DOTTED, MEDIUM\_DASH\_DOTTED, THIN\_DASH\_DOT\_DOTTED, MEDIUM\_DASH\_DOT\_DOTTED, SLANTED\_MEDIUM\_DASH\_DOTTED, or 0x00 through 0x0D.  
110 borders.right = xlwt.Borders.DASHED  
111 borders.top = xlwt.Borders.DASHED  
112 borders.bottom = xlwt.Borders.DASHED  
113 borders.left\_colour = 0x40  
114 borders.right\_colour = 0x40  
115 borders.top\_colour = 0x40  
116 borders.bottom\_colour = 0x40  
117 style = xlwt.XFStyle() \# Create Style  
118 style.borders = borders \# Add Borders to Style  
119 worksheet.write(0, 0, 'Cell Contents', style)  
120 workbook.save('Excel\_Workbook.xls')  
121  
122 Setting the Background Color of a Cell  
123 import xlwt  
124 workbook = xlwt.Workbook()  
125 worksheet = workbook.add\_sheet('My Sheet')  
126 pattern = xlwt.Pattern() \# Create the Pattern  
127 pattern.pattern = xlwt.Pattern.SOLID\_PATTERN \# May be: NO\_PATTERN, SOLID\_PATTERN, or 0x00 through 0x12  
128 pattern.pattern\_fore\_colour = 5 \# May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on...  
129 style = xlwt.XFStyle() \# Create the Pattern  
130 style.pattern = pattern \# Add Pattern to Style  
131 worksheet.write(0, 0, 'Cell Contents', style)  
132 workbook.save('Excel\_Workbook.xls')  
133  
134 TODO: Things Left to Document  
135 - Panes -- separate views which are always in view  
136 - Border Colors (documented above, but not taking effect as it should)  
137 - Border Widths (document above, but not working as expected)  
138 - Protection  
139 - Row Styles  
140 - Zoom / Manification  
141 - WS Props?  
142 Source Code for reference available at: https://secure.simplistix.co.uk/svn/xlwt/trunk/xlwt/

转载：[用python读写excel（xlrd、xlwt） ][python_excel_xlrd_xlwt_]

[http_pypi.python.org_pypi_xlrd]: http://pypi.python.org/pypi/xlrd
[APP]: http://www.zijinxing.com/t/app
[google]: http://www.zijinxing.com/t/google
[python_excel_xlrd_xlwt_]: http://www.zijinxing.com/201405/46746.html