处理Excel文件
常见的 Excel 包:
- OpenPyXL 是个读写 Excel 2010 xlsx/xlsm/xltx/xltm 的 Python 库, 简单易用, 功能广泛, 单元格格式/图片/表格/公式/筛选/批注/文件保护等等功能应有尽有, 图表功能是其一大亮点;
- xlwings 是一个基于 BSD 授权协议的 Python 库, 可以轻松的使用 Python 操作 Excel, 也可以在 Excel 中调用 Python, 以接近 VBA 语法的实现 Excel 编程, 支持 Excel 宏, 并且可以作为 Web 服务器, 提供 REST API 接口;
- pandas 数据处理是 pandas 的立身之本, Excel 作为 pandas 输入/输出数据的容器;
- win32com 从命名上就可以看出, 这是一个处理 windows 应用的扩展, Excel 只是该库能实现的一小部分功能. 该库还支持 office 的众多操作. 需要注意的是, 该库不单独存在, 可通过安装 pypiwin32 或者 pywin32 获取;
- Xlsxwriter 拥有丰富的特性, 支持图片/表格/图表/筛选/格式/公式等, 功能与 openpyxl 相似, 优点是相比openpyxl 还支持 VBA 文件导入, 迷你图等功能, 缺点是不能打开/修改已有文件, 意味着使用 xlsxwriter 需要从零开始;
- DataNitro 一个 Excel 的付费插件, 内嵌到 Excel 中, 可完全替代 VBA, 在 Excel 中使用 python 脚本。既然被称为 Excel 中的 python, 同时可以与其他 python 库协同;
- xlutils 基于 xlrd/xlwt, 老牌 python 包, 算是该领域的先驱, 功能特点中规中矩, 比较大的缺点是仅支持 xls 文件.
.xls 和 .xlsx 格式文件
.xls: Office 2003及之前版本的 Excel 只能保存为 .xls 格式. 单个工作表最多支持65536行, 256列..xlsx: 2007及之后版本的 Excel 既能保存为 .xls , 又能保存为 .xlsx. 单个工作表最多支持1048576行, 16384列.
因此格式相互转换可能造成数据丢失.
xlwt, xlrd, xlutils
xlwt模块只能写xls文件, 不能写xlsx文件(写xlsx程序不会报错, 但最后文件无法直接打开, 会报错). 而pandas既可处理xls文件, 也可处理xlsx文件.
单元格索引从0开始.
import xlwt
| 代码 | 说明 |
|---|---|
| wb = xlwt.Workbook(encoding='utf-8') | 创建一个工作簿Book对象 |
| wb.add_sheet("Sheet1") | 添加名为Sheet1的工作表 |
| ws = wb.get_sheet(0) | 根据索引或名称返回Worksheet对象 |
| ws.write(0, 0, "123") | 将值填入指定位置 |
| r1 = ws.row(0) | 返回指定行 |
| c1 = ws.col(0) | 返回指定列 |
| r1.write(1, "456") | 在行的第二列写入"456" |
| worksheet.write_merge(2, 3, 0, 3, '合并从第三行到第四行，第一列到第四列') | 合并单元格 |
| style = xlwt.XFStyle() | 初始化样式 |
| font1 = xlwt.Font() | 创建字体对象 |
| font1.name = 'Times New Roman' | 设置字体 |
| font1.bold = True | 黑体 |
| font1.underline = True | 下划线 |
| font1.italic = True | 斜体 |
| style.font = font1 | 应用字体 |
| aligment1 = xlwt.Aligment() | 创建对齐格式对象 |
| alignment1.horz = xlwt.Alignment.HORZ_CENTE | 水平方向: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED |
| alignment1.vert = xlwt.Alignment.VERT_CENTER | 垂直方向: VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED |
| style.aligment = aligment1 | 应用对齐格式 |
| ws.write(1, 0, '带样式的单元格', style) | 带样式写入 |
| worksheet.write(1, 1, xlwt.Formula('HYPERLINK("https://www.cnblogs.com/khrushchefox/";"Khru")')) | 单元格添加超链接 |
| worksheet.write(1, 2, xlwt.Formula('SUM(A1,B1)')) | 单元格添加公式 |
| r1.height / c1.width = 300 | 赋值改变行高/列宽 (单位: 0.05pt) |
| wb.save("test.xls") | 保存文件 |
import xlrd
| 代码 | 说明 |
|---|---|
| wb = xlrd.open_workbook("test.xls", formatting_info=True) | 打开指定文件,返回Book对象, 保留格式信息 |
| wb.nsheets | 返回Sheet数目 |
| wb.sheets() | 返回所有Sheet对象的列表 |
| ws = wb.sheet_by_name("Sheet1") | 根据名字返回Sheet对象 |
| wb.sheet_by_index(0) | 根据索引返回Sheet对象 |
| ws.name | 返回表格名称 |
| ws.nrows / ws.ncols | 返回行/列数 |
| ws.row_len(0) | 返回指定行的长度 |
| cell1 = ws.cell(0, 0) | 根据位置获取Cell对象 |
| ws.cell_value(0, 0) | 根据位置获取值 |
| cell1.value | 返回单元格的值 |
| data_list1 = ws.row_values(rowx=0, start_colx=0, end_colx=None) | 获取指定行中的所有数据 |
| data_list2 = ws.col_values(colx=0, start_rowx=0, end_rowx=None) | 获取指定列中的所有数据 |
import xlultils
xlutils不能读,但可以修改 (追加写入) xls, 因为xlutils包依赖于xlwt和xlrd.
import xlrd
from xlutils.copy import copy
wb = xlrd.open_workbook("test.xls")
new_wb = copy(wb)
ws = new_wb.get_sheet(0)
ws.write(0, 0, "111")
new_wb.save("test2.xls")
openpyxl
openpyxl模块可实现对excel文件的读, 写和修改, 只能处理xlsx文件, 不能处理xls文件.
对于openpyxl, 单元格的行和列从1开始.
创建, 打开与保存
from openpyxl import Workbook, load_workbook
| 代码 | 说明 |
|---|---|
| wb = Workbook() | 创建一个Workbook工作簿对象, 自带一个名为 Sheet 的表单 |
| wb = load_workbook(dir) | 打开指定路径的文件 |
| wb.sheetnames() | 工作簿中所有表单名字的列表 |
| ws = wb.active | 创建的 workbook 会将第一个表单激活, 通过 wb.active 获取引用当前激活的表单 |
| ws2 = wb.create_sheet("Sheet2", index=1) | 新建一个工作表, 位于第二 |
| ws3 = wb["Sheet2"] | 获取指定名称的表单 |
| ws4 = wb.get_sheet_by_name(u"工作表1") | 同上 |
| ws.title | 表单的名字 Sheet |
| ws_copy = wb.copy_worksheet(ws) | 复制ws |
| wb.remove(ws_copy) | 删除ws_copy |
| wb.save("test.xlsx") | save 方法会立即保存, 不会有任何提示 |
单元格操作
| 代码 | 说明 |
|---|---|
| ws.append(iterable) | 按顺序(或字典的键)将可迭代对象的值填入 |
| cell1 = ws.cell(6, 1, "某个值") | 指定位置和值, 创建一个cell1对象 (如果有原值, 则原值被改变) |
| cell2 = ws.cell(6, 1) | 指定位置及其值, 创建一个cell2对象 |
| ws["a6"] = 123.11 | 对表格进行赋值 |
| cell2.value | cell2的值 |
| cell2.coordinate | cell2的坐标 ('A6') |
| cell2.row (cell2.column/cell2.col_idx) | cell2的行 (列) 号 |
| cell2.column_letter | cell2的列的字母形式 |
| ws["a:c"] (ws["a"]) | 按列遍历, 每一列组成元组, 返回一个二维元组 |
| ws[1:3] (ws[1]) | 按行遍历, 每一行组成元组, 返回一个二维元组 |
| ws["a1:c3"] | 按行遍历指定范围, 每一行组成元组, 返回一个二维元组 |
| ws.iter_rows(min_row=None, max_row=None, min_col=None, max_col=None, values_only=False) | 按行遍历, 返回一个生成器 |
| ws.iter_cols(values_only=True) | 按列遍历, 只读取值 |
| ws.rows (ws.columns) | 表单的所有行 (列) |
| ws.merge_cells() (unmerge_cells) | 合并单元格 (拆分单元格) |
| ws.insert_cols(2, 3) (insert_rows) | 从第二列开始插入三列 |
| ws.delete_cols(2, 1) (delete_rows) | 从第二列开始删除一列 |
| ws.move_range("c3:d4", -2, -2) | 将单元格上移2行, 左移2行 |
使用公式
from openpyxl.formula.translate import Translator
| 代码 | 说明 |
|---|---|
| ws["c2"] = "=SUM(a2:b2)" | 使用openpyxl.utils.FORMULAE集合中支持的公式 |
| ws["c3"] = Translator(formula="=SUM(a2:b2)", origin="c2").translate_formula("c3") | 对指定单元格翻译公式 |
只读和只写
-
只读模式, 不进行修改和保存.
from openpyxl import load_workbook wb = load_workbook(filename='test.xlsx', read_only=True) # 打开工作簿时指定只读模式 ws = wb['1'] # 读取数据 for row in ws.rows: for cell in row: print(cell.value) #需要手动关闭释放内存 wb.close() -
只写模式
from openpyxl import Workbook from openpyxl.cell import WriteOnlyCell from openpyxl.styles import Font wb = Workbook(write_only=True) # 创建工作簿时指定只写模式 ws = wb.create_sheet() # 只能通过create_sheet新建一个sheet for _ in range(100): ws.append([i for i in range(200)]) # 只能通过append写入数据 # 如果需要保留公式、注释等操作，可以使用WriteOnlyCell cell = WriteOnlyCell(ws, value="111") cell.font = Font(name='黑体', size=15) ws.append([cell]) wb.save('test.xlsx') # 保存后无法再修改