在python中简单地处理excel文件,有几个相关的模块,各有千秋,本文将不定时收录。
Python Excel网站收集了关于python处理excel文件的各种信息。
【注意】使用python处理excel文件前,请多备份文件,以防数据丢失。
------------------
0x01 xlrd
xlrd is a library for reading data and formatting information from Excel files, whether they are .xls or .xlsx files.
安装:
pip install xlrd
使用:
只能读.xls、.xlsx文件(xlrd0.8.0+版本支持读取xlsx文件)
importxlrd
book= xlrd.open_workbook("pcat.xls")print("The number of worksheets is {0}".format(book.nsheets))print("Worksheet name(s): {0}".format(book.sheet_names()))
sh=book.sheet_by_index(0)print("{0} {1} {2}".format(sh.name, sh.nrows, sh.ncols))print("Cell B3 is {0}".format(sh.cell_value(rowx=2, colx=1)))for rx inrange(sh.nrows):print(sh.row(rx))
0x02 xlwt
xlwt is a library for writing data and formatting information to older Excel files (ie: .xls)
pip install xlwt
使用:
用xlwt创建一个简单的.xls文件
importxlwtfrom datetime importdatetime
style0= xlwt.easyxf('font: name Times New Roman, color-index red, bold on',
num_format_str='#,##0.00')
style1= xlwt.easyxf(num_format_str='YYYY-MM-DD HH:MM:SS')
wb=xlwt.Workbook()
ws= wb.add_sheet('A Test Sheet')
ws.write(0, 0,1234.56, style0)
ws.write(1, 0, datetime.now(), style1)
ws.write(2, 0, 1)
ws.write(2, 1, 1)
ws.write(2, 2, xlwt.Formula("A3+B3"))
wb.save('example.xls')
0x03 xlutils
This package provides a collection of utilities for working with Excel files.
官方文档:https://xlutils.readthedocs.io/en/latest/api.html
github项目:https://github.com/python-excel/xlutils
安装:
(如果没安装xlrd、xlwt,会自动安装这2个模块)
pip install xlutils
使用:
importxlrdimportxlwtimportxlutilsimportxlutils.copy as copy
rdbook= xlrd.open_workbook('first.xls')
wtbook=copy.copy(rdbook)
wtsheet=wtbook.get_sheet(0)
type(wtsheet)
wtsheet.write(0,0,'pcat.cc')
wtbook.save('second.xls')
0x04 openpyxl
A Python library to read/write Excel 2010 xlsx/xlsm files.
官方文档:https://openpyxl.readthedocs.io/en/stable/
安装:
pip install openpyxl
使用:
写xlsx文件
from openpyxl importWorkbook
wb=Workbook()#grab the active worksheet
ws =wb.active#Data can be assigned directly to cells
ws['A1'] = 42
#Rows can also be appended
ws.append([1, 2, 3])#Python types will automatically be converted
importdatetime
ws['A2'] =datetime.datetime.now()#Save the file
wb.save("sample.xlsx")
读xlsx文件
from openpyxl importload_workbook
wb= load_workbook(filename='pcat.xlsx')
sheet_ranges= wb['Sheet1']print(sheet_ranges['A2'].value)
注意:
openpyxl不支持.xls格式。
读写文件前记得多备注,有时候可能有bug。
0x05 XlsxWriter
XlsxWriter is a Python module for creating Excel XLSX files.
官方文档: https://xlsxwriter.readthedocs.io/
github项目:https://github.com/jmcnamara/XlsxWriter
安装:
pip install xlsxwriter
使用:
importxlsxwriter
workbook= xlsxwriter.Workbook('hello_world.xlsx')
worksheet=workbook.add_worksheet()
worksheet.write('A1', 'Hello world')
workbook.close()
注意:
XlsxWriter不支持.xls格式。