Python操作Excel(xlrd,xlwt)
Python操作Excel
python操作excel主要用到xlrd和xlwt这两个库,即xlrd是读excel,xlwt是写excel的库。可从这里下载 https://pypi.python.org/pypi。
当然还有其他python处理Excel的类库,主要有:openpyxl,xlsxwriter,xlutils;详见:http://www.python-excel.org/。
读excel--xlrd
python读取Excel以下表格数据为源,进行相关读取数据操作解释:
#!/usr/bin/python # coding=utf-8 import xlrd def read_excel(): # 打开Excel读取数据 data = xlrd.open_workbook('H://testData.xlsx') print type(data), data # 获取所有sheet页 sheets = data.sheet_names() print type(sheets), sheets # 根据sheet索引或者名称获取sheet内容 sheet1one = data.sheet_by_index(0) sheet1two = data.sheet_by_name(u'Sheet1') print type(sheet1one), sheet1one # sheet的名称,行数,列数 print sheet1one.name, sheet1one.nrows, sheet1one.ncols # 获取整行和整列的值(数组) rows = sheet1one.row_values(3) # 获取第四行内容 cols = sheet1one.col_values(2) # 获取第三列内容 print type(rows), rows print type(cols), cols # python读取excel中单元格的内容返回的有5种类型,即下面的ctype # ctype : 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error print sheet1one.cell(1, 0).ctype print sheet1one.cell(1, 0).value.encode('utf-8') print sheet1one.cell_value(1, 0).encode('utf-8') print sheet1one.row(1)[0].value.encode('utf-8') if __name__ == '__main__': read_excel()
输出:
<class 'xlrd.book.Book'> <xlrd.book.Book object at 0x0000000002887048>
<type 'list'> [u'Sheet1', u'Sheet2', u'Sheet3']
<class 'xlrd.sheet.Sheet'> <xlrd.sheet.Sheet object at 0x00000000028874A8>
Sheet1 8 5
<type 'list'> [u'\u5c0f\u660e', 26.0, 32854.0, u'\u6253\u6e38\u620f', u'\u540c\u5b66']
<type 'list'> [u'\u51fa\u751f\u65e5\u671f', 33553.0, 32926.0, 32854.0, 32760.0, 32363.0, 33156.0, u'\u6682\u65e0']
1
小杰
小杰
小杰
- 针对获取到时间的处理
即获取到数据类型ctype=3,这时需要使用xlrd的xldate_as_tuple来处理为date格式。
def handle_date(): # 打开Excel读取数据 data = xlrd.open_workbook('H://testData.xlsx') # 根据sheet索引或者名称获取sheet内容 sheet1one = data.sheet_by_index(0) print sheet1one.cell(1,0).value.encode('utf-8') print sheet1one.cell(1,1).value print sheet1one.cell(1,2).value print sheet1one.cell(1,2).ctype if sheet1one.cell(1,2).ctype == 3: datetmp = xlrd.xldate_as_tuple(sheet1one.cell(1,2).value, data.datemode) print datetmp print date(*datetmp[:3]) if __name__ == '__main__': handle_date()
输出:
小杰
24.0
33553.0
3
(1991, 11, 11, 0, 0, 0)
1991-11-11
- 处理单元格合并的问题
写excel--xlwt
python写入Excel数据:
#!/usr/bin/python # coding=utf-8 import xlwt from datetime import datetime #创建Excel对象 wb = xlwt.Workbook() #Excel对象添加Sheet页Test ws = wb.add_sheet("Test") #设置单元格格式 style0 = xlwt.easyxf('font: name Times New Roman, color-index red, bold on', num_format_str='#,##0.00') style1 = xlwt.easyxf(num_format_str='D-MMM-YY') #写入数据 ws.write(0, 0, 1) ws.write(0, 1, "test1") ws.write(0, 2, 123.45,style0) ws.write(1, 0, datetime.now(), style1) ws.write(1, 1, 2) ws.write(1, 2, "test2") ws.write(2, 2, xlwt.Formula("A1+B2")) #保存至指定目录下文件 wb.save(u"D:\\test.xls")
输出: