Python处理excel

本文主要记录一下Python处理excel表格的一些常用包,包括读取模块xlrd,写入模块xlwt和xlsxwriter

1. xlrd

note:

  • xlrd可以读取xls(2003)、xlsx(2007及以上)
  • xls的行数限制为65536,最大列数是256列;xlsx的行数限制为1048576,最大列数是1638列

基本操作:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
# 打开Excel表格
workbook = xlrd.open_workbook(r'D:\programming\pycharm\pythonProject\helic\test.xlsx')

# 获取所有sheet
print workbook.sheet_names() # [u'sheet1', u'sheet2']
sheet2_name = workbook.sheet_names()[1]

# 根据sheet索引或者名称获取sheet内容
sheet2 = workbook.sheet_by_index(1) # sheet索引从0开始
sheet2 = workbook.sheet_by_name('sheet2')

# sheet的名称,行数,列数
print sheet2.name,sheet2.nrows,sheet2.ncols

# 获取整行和整列的值(数组)
rows = sheet2.row_values(3) # 获取第四行内容
cols = sheet2.col_values(2) # 获取第三列内容

# 获取单元格内容
print sheet2.cell(1,0).value.encode('utf-8')
print sheet2.cell_value(1,0).encode('utf-8')
print sheet2.row(1)[0].value.encode('utf-8')

# 获取单元格内容的数据类型,类型 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
print sheet2.cell(1,0).ctype

python读取excel中单元格类型为date(有的日期是以string格式存储,无需用此方式读取,依照上述基本操作);date类型的ctype=3,这时需要使用xlrd的xldate_as_tuple来处理为date格式,先判断表格的ctype,如果等于3,则用时间格式处理:

if sheet.cell(row,col).ctype == 3:         
    date_value = xlrd.xldate_as_tuple(sheet.cell_value(rows,3),workbook.datemode)      # (1992, 2, 22, 0, 0, 0)
    date_tmp = date(*date_value[:3]).strftime('%Y/%m/%d') # '1992/02/22'

读取合并单元格的内容(合并的单元格只是合并的第一个单元格的有值,其它的为空;只能获取合并单元格的第一个cell的行列索引,才能读到值;合并行单元格读取行的第一个索引,合并列单元格读取列的第一个索引):

1
2
print sheet2.col_values(4)[1]  # 好朋友
print sheet2.row_values(7)[2] # 暂无

获取合并单元格的位置:读取文件的时候需要将formatting_info参数设置为True,默认是False

1
2
3
workbook = xlrd.open_workbook(r'F:\demo.xlsx',formatting_info=True)  
sheet2 = workbook.sheet_by_name('sheet2')
print sheet2.merged_cells # [(7, 8, 2, 5), (1, 3, 4, 5), (3, 6, 4, 5)]

merged_cells返回的这四个参数的含义是:(row,row_range,col,col_range),其中[row,row_range)包括row,不包括row_range,col也是一样,即(1, 3, 4, 5)的含义是:第2到3行(不包括第4行)合并,(7, 8, 2, 5)的含义是:第3到5列合并。获取merge_cells返回的row和col低位的索引即可

1
2
3
4
5
6
7
8
9
print sheet2.cell_value(1,4)  #(1, 3, 4, 5)  好朋友  
print sheet2.cell_value(3,4) #(3, 6, 4, 5) 同学
print sheet2.cell_value(7,2) #(7, 8, 2, 5) 暂无

merge = []
for (rlow,rhigh,clow,chigh) in sheet2.merged_cells: # [(7, 8, 2, 5), (1, 3, 4, 5), (3, 6, 4, 5)]
merge.append([rlow,clow])
for index in merge: # [[7, 2], [1, 4], [3, 4]]
print sheet2.cell_value(index[0],index[1])

2. xlwt 与 xlsxwriter

  • xlwt只能写.xls(2003)文件,xlsxwriter只能写.xlsx(2007及以后)表格

( 1 ) xlwt

1
2
3
4
5
6
7
8
style0 = xlwt.easyxf('font: name 等线, color-index black, bold off, height 220')
style1 = xlwt.easyxf('font: name 等线, color-index red, bold off, height 220')

f = xlwt.Workbook() # 创建工作簿
female = f.add_sheet(u'female', cell_overwrite_ok=True) # 创建sheet
female.write(0, 0, 'test', style1)
female.write(0, 1, 'test', style0)
f.save("demo.xls")

( 2 ) xlsxwriter

1
2
3
4
5
6
7
workbook = xlsxwriter.Workbook('hello.xlsx')  # 建立文件
# Add a bold format to use to highlight cells. 设置粗体,默认是False
bold = workbook.add_format({'bold': False, 'color': 'black', 'font_name': '等线', 'font_size': '11'})
# 建立sheet, 可以work.add_worksheet('employee')来指定sheet名,但中文名会报UnicodeDecodeErro的错误
worksheet = workbook.add_worksheet('female')
worksheet.write(0, 0, 'Hello world', bold) # 向A1写入
workbook.close()