用python读写excel的方法
本文实例讲述了用python读写excel的方法。分享给大家供大家参考。具体如下：
最近需要从多个excel表里面用各种方式整理一些数据，虽然说原来用过java做这类事情，但是由于最近在学python，所以当然就决定用python尝试一下了。发现python果然简洁很多。这里简单记录一下。（由于是用到什么学什么，所以不算太深入，高手勿喷，欢迎指导）
一、读excel表
读excel要用到xlrd模块，官网安装（http://pypi.python.org/pypi/xlrd）。然后就可以跟着里面的例子稍微试一下就知道怎么用了。大概的流程是这样的：
1、导入模块
2、打开excel文件读取数据
3、获取一个工作表
① table = data.sheets()[0] #通过索引顺序获取
② table = data.sheet_by_index(0) #通过索引顺序获取
③ table = data.sheet_by_name(u'sheet1')#通过名称获取
4、获取整行和整列的值（返回数组）
table.col_values(i)
5、获取行数和列数
table.ncols
6、获取单元格
table.cell(2,3).value
就我自己使用的时候觉得还是获取cell最有用，这就相当于是给了你一个二维数组，余下你就可以想怎么干就怎么干了。得益于这个十分好用的库代码很是简洁。但是还是有若干坑的存在导致话了一定时间探索。现在列出来供后人参考吧：
1、首先就是我的统计是根据姓名统计各个表中的信息的，但是调试发现不同的表中各个名字貌似不能够匹配，开始怀疑过编码问题，不过后来发现是因为 空格。因为在excel中输入的时候很可能会顺手在一些名字后面加上几个空格或是tab键，这样看起来没什么差别，但是程序处理的时候这就是两个完全 不同的串了。我的解决方法是给每个获取的字符串都加上strip（）处理一下。效果良好
2、还是字符串的匹配，在判断某个单元格中的字符串（中文）是否等于我所给出的的时候发现无法匹配，并且各种unicode也不太奏效，百度过一些解决 方案，但是都比较复杂或是没用。最后我采用了一个比较变通的方式：直接从excel中获取我想要的值再进行比较，效果是不错就是通用行不太好，个 呢不能问题还没解决。
二、写excel表
写excel表要用到xlwt模块，官网下载（http://pypi.python.org/pypi/xlwt）。大致使用流程如下：
1、导入模块
2、创建workbook（其实就是excel，后来保存一下就行）
3、创建表
4、往单元格内写入内容
5、保存
由于我的需求比较简单，所以这上面没遇到什么问题，唯一的就是建议还是用ascii编码，不然可能会有一些诡异的现象。
当然xlwt功能远远不止这些，他甚至可以设置各种样式之类的。附上一点例子
examples generating excel documents using python's xlwt
here are some simple examples using python's xlwt library to dynamically generate excel documents.
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
the simplest example
import xlwt
workbook = xlwt.workbook(encoding = 'ascii')
worksheet = workbook.add_sheet('my worksheet')
worksheet.write(0, 0, label = 'row 0, column 0 value')
workbook.save('excel_workbook.xls')
formatting the contents of a cell
import xlwt
workbook = xlwt.workbook(encoding = 'ascii')
worksheet = workbook.add_sheet('my worksheet')
font = xlwt.font() # create the font
font.name = 'times new roman'
font.bold = true
font.underline = true
font.italic = true
style = xlwt.xfstyle() # create the style
style.font = font # apply the font to the style
worksheet.write(0, 0, label = 'unformatted value')
worksheet.write(1, 0, label = 'formatted value', style) # apply the style to the cell
workbook.save('excel_workbook.xls')
attributes of the font object
font.bold = true # may be: true, false
font.italic = true # may be: true, false
font.struck_out = true # may be: true, false
font.underline = xlwt.font.underline_single # may be: underline_none, underline_single, underline_single_acc, underline_double, underline_double_acc
font.escapement = xlwt.font.escapement_superscript # may be: escapement_none, escapement_superscript, escapement_subscript
font.family = xlwt.font.family_roman # may be: family_none, family_roman, family_swiss, family_modern, family_script, family_decorative
font.charset = xlwt.font.charset_ansi_latin # may be: charset_ansi_latin, charset_sys_default, charset_symbol, charset_apple_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
font.colour_index = ?
font.get_biff_record = ?
font.height = 0x00c8 # c8 in hex (in decimal) = 10 points in height.
font.name = ?
font.outline = ?
font.shadow = ?
setting the width of a cell
import xltw
workbook = xlwt.workbook()
worksheet = workbook.add_sheet('my sheet')
worksheet.write(0, 0, 'my cell contents')
worksheet.col(0).width = 3333 # 3333 = 1" (one inch).
workbook.save('excel_workbook.xls')
entering a date into a cell
import xlwt
import datetime
workbook = xlwt.workbook()
worksheet = workbook.add_sheet('my sheet')
style = xlwt.xfstyle()
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
worksheet.write(0, 0, datetime.datetime.now(), style)
workbook.save('excel_workbook.xls')
adding a formula to a cell
import xlwt
workbook = xlwt.workbook()
worksheet = workbook.add_sheet('my sheet')
worksheet.write(0, 0, 5) # outputs 5
worksheet.write(0, 1, 2) # outputs 2
worksheet.write(1, 0, xlwt.formula('a1*b1')) # should output "10" (a1[5] * a2[2])
worksheet.write(1, 1, xlwt.formula('sum(a1,b1)')) # should output "7" (a1[5] + a2[2])
workbook.save('excel_workbook.xls')
adding a hyperlink to a cell
import xlwt
workbook = xlwt.workbook()
worksheet = workbook.add_sheet('my sheet')
worksheet.write(0, 0, xlwt.formula('hyperlink("http://www.google.com";"google")')) # outputs the text "google" linking to http://www.google.com
workbook.save('excel_workbook.xls')
merging columns and rows
import xlwt
workbook = xlwt.workbook()
worksheet = workbook.add_sheet('my sheet')
worksheet.write_merge(0, 0, 0, 3, 'first merge') # merges row 0's columns 0 through 3.
font = xlwt.font() # create font
font.bold = true # set font to bold
style = xlwt.xfstyle() # create style
style.font = font # add bold font to style
worksheet.write_merge(1, 2, 0, 3, 'second merge', style) # merges row 1 through 2's columns 0 through 3.
workbook.save('excel_workbook.xls')
setting the alignment for the contents of a cell
import xlwt
workbook = xlwt.workbook()
worksheet = workbook.add_sheet('my sheet')
alignment = xlwt.alignment() # create alignment
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
alignment.vert = xlwt.alignment.vert_center # may be: vert_top, vert_center, vert_bottom, vert_justified, vert_distributed
style = xlwt.xfstyle() # create style
style.alignment = alignment # add alignment to style
worksheet.write(0, 0, 'cell contents', style)
workbook.save('excel_workbook.xls')
adding borders to a cell
# 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.
import xlwt
workbook = xlwt.workbook()
worksheet = workbook.add_sheet('my sheet')
borders = xlwt.borders() # create borders
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.
borders.right = xlwt.borders.dashed
borders.top = xlwt.borders.dashed
borders.bottom = xlwt.borders.dashed
borders.left_colour = 0x40
borders.right_colour = 0x40
borders.top_colour = 0x40
borders.bottom_colour = 0x40
style = xlwt.xfstyle() # create style
style.borders = borders # add borders to style
worksheet.write(0, 0, 'cell contents', style)
workbook.save('excel_workbook.xls')
setting the background color of a cell
import xlwt
workbook = xlwt.workbook()
worksheet = workbook.add_sheet('my sheet')
pattern = xlwt.pattern() # create the pattern
pattern.pattern = xlwt.pattern.solid_pattern # may be: no_pattern, solid_pattern, or 0x00 through 0x12
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...
style = xlwt.xfstyle() # create the pattern
style.pattern = pattern # add pattern to style
worksheet.write(0, 0, 'cell contents', style)
workbook.save('excel_workbook.xls')
todo: things left to document
- panes -- separate views which are always in view
- border colors (documented above, but not taking effect as it should)
- border widths (document above, but not working as expected)
- protection
- row styles
- zoom / manification
- ws props?
source code for reference available at: https://secure.simplistix.co.uk/svn/xlwt/trunk/xlwt/
希望本文所述对大家的python程序设计有所帮助。