python 操作excel文件
简介
在实际项目中，我们有时候会需要对Excel文件进行处理，python中有很多模块都可以对Excel文件进行相应处理，本文将对python的相关库做一下比较,如下是处理Excel文件的模块对比
xlrd
是python的常用的读取excel的模块，如果只有读取excel数据时，推荐使用，速度较快，另外，excel只兼容xls。
安装
pip install xlrd
常用的表格中数据类型
empty（空的）
string（text）
number
date
boolean
error
blank（空白表格）
测试的表格数据
常用的xlrd中的方法及属性
注意所有以索引获取的索引都是从0开始。
import os
excel_path = os.path.join(os.path.dirname(__file__), '**.xlsx')
# xlrd
import xlrd
from xlrd import Book
data:Book = xlrd.open_workbook(excel_path)
# 对于工作表的操作
table = data.sheets()[0]
print('使用sheets获取第一个工作表对象:', table)
table = data.sheet_by_index(1)
print('根据索引获取工作表对象:', table)
table = data.sheet_by_name('范例1')
print('根据工作表名称获取工作表对象:', table)
table_name = data.sheet_names()
print('工作表名称列表：', table_name)
# 根据index判断
print('第一个工作表是否导入完成:', data.sheet_loaded(0))
# 根据表名称判断
print('第二个工作表是否导入完成:', data.sheet_loaded('范例2'))
# 对于行的操作， 此处以第一个范例为示例
print('工作表的行数:', table.nrows)
print('工作表第一行数据为（包含表格中数据类型）:',table.row(0))
print('工作表中所有行有效数据中的最大长度(不管选择的是哪个):', table.row_len(0))
print('工作表第一行数据（直接显示对应数据）:', table.row_values(0))
print('工作表中对应的数据类型,empty为0,其余不为0', table.row_types(0))
print('工作表中第一行数据，第一列到第二列的数据:', table.row_slice(0, start_colx=0, end_colx=2))
# 对于列的操作，此处以第一个范例为示例
print('工作表的列数:',table.ncols)
print('工作表的第一列数据（包含类型）:',table.col(0))
print('工作表中第一列数据（不包含类型）:', table.col_values(0))
print('工作表中第一列数据的类型,empty为0,其余不为0:', table.col_types(0))
print('工作表中第一列数据，第一行到第二行的数据:',table.col_slice(0, start_rowx=0, end_rowx=2))
# 对于单元格的操作
print('工作表第二行第二列的单元格对象:', table.cell(1,1))
print('工作表第二行第二列的单元格数据类型:', table.cell_type(1,1))
print('工作表第二行第二列的单元格数据:', table.cell_value(1,1))
使用sheets获取第一个工作表对象: <xlrd.sheet.Sheet object at 0x7f2826d2a278>
根据索引获取工作表对象: <xlrd.sheet.Sheet object at 0x7f2826d2a2b0>
根据工作表名称获取工作表对象: <xlrd.sheet.Sheet object at 0x7f2826d2a278>
工作表名称列表： ['范例1', '范例2']
第一个工作表是否导入完成: True
第二个工作表是否导入完成: True
工作表的行数: 28
工作表第一行数据为（包含表格中数据类型）: [number:1.0, number:2.0, number:3.0, number:4.0, number:5.0, number:6.0, number:7.0, number:8.0, number:9.0, number:10.0]
工作表中所有行有效数据中的最大长度(不管选择的是哪个): 10
工作表第一行数据（直接显示对应数据）: [1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0]
工作表中对应的数据类型,empty为0,其余不为0 array('B', [2, 2, 2, 2, 2, 2, 2, 2, 2, 2])
工作表中第一行数据，第一列到第二列的数据: [number:1.0, number:2.0]
工作表的列数: 10
工作表的第一列数据（包含类型）: [number:1.0, number:2.0, number:3.0, number:4.0, number:5.0, number:6.0, number:7.0, number:8.0, number:9.0, number:10.0, number:11.0, number:12.0, number:13.0, number:14.0, number:15.0, number:16.0, number:17.0, number:18.0, number:19.0, number:20.0, number:21.0, number:22.0, number:23.0, number:24.0, number:25.0, number:26.0, number:27.0, number:28.0]
工作表中第一列数据（不包含类型）: [1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0, 11.0, 12.0, 13.0, 14.0, 15.0, 16.0, 17.0, 18.0, 19.0, 20.0, 21.0, 22.0, 23.0, 24.0, 25.0, 26.0, 27.0, 28.0]
工作表中第一列数据的类型,empty为0,其余不为0: [2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2]
工作表中第一列数据，第一行到第二行的数据: [number:1.0, number:2.0]
工作表第二行第二列的单元格对象: number:3.0
工作表第二行第二列的单元格数据类型: 2
工作表第二行第二列的单元格数据: 3.0
官方文档:xlrd官方文档
xlwt
xlwt用于新增表格及原有表格数据的修改，速度较快，如果涉及新增或者修改，推荐使用
安装
pip install xlwt
常用方法及属性
import xlwt, os
from xlwt import Worksheet
# excel_path = os.path.join(os.path.dirname(__file__), 't.xlsx')
excel_path = os.path.join(os.path.dirname(__file__), 't.xls')
workBook = xlwt.Workbook()
# 新建工作表1
workSheet:Worksheet = workBook.add_sheet('工作表1')
# 设置样式, 可以添加到对应单元格中
style = xlwt.XFStyle()
# 设置字体样式
font = xlwt.Font()
# 设置字体加粗
font.bold = True
# 设置字体下划线
font.underline = True
# 设置字体斜体
font.italic = True
# 设置字体类型
font.name = 'Times New Roman'
# 设置边框样式
# DASHED:虚线
# NO_LINE 没有线
# THIN:实线
borders = xlwt.Borders()
# 设置边框样式
borders.left = xlwt.Borders.DASHED
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
# 设置单元格样式
pattern = xlwt.Pattern()
# May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
# 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...
pattern.pattern_fore_colour = 5
# 设置单元格对齐
alignment = xlwt.Alignment()
# VERT_TOP = 0x00 上端对齐
# VERT_CENTER = 0x01 居中对齐（垂直方向上）
# VERT_BOTTOM = 0x02 低端对齐
# HORZ_LEFT = 0x01 左端对齐
# HORZ_CENTER = 0x02 居中对齐（水平方向上）
# HORZ_RIGHT = 0x03 右端对齐
alignment.horz = 0x02 # 设置水平居中
alignment.vert = 0x01 # 设置垂直居中
style.font = font
style.borders = borders
style.pattern = pattern
style.alignment = alignment
# 在工作表的第一行第一列写入test
workSheet.write(0, 0, 'test')
# 设置列宽,20个字符
"""
xlwt中列宽的值表示方法：默认字体0的1/256为衡量单位。
xlwt创建时使用的默认宽度为2960，既11个字符0的宽度
所以我们在设置列宽时可以用如下方法：
width = 256 * 20 256为衡量单位，20表示20个字符宽度
"""
workSheet.col(0).width = 256 * 20
# 设置行高
"""
在xlwt中设置行高,只能通过设置单元格的样式实现
"""
height_style = xlwt.easyxf('font:height 360;') # 18pt,类型小初的字号
row = workSheet.row(0)
row.set_style(height_style)
# 在工作表的第一行第二列写入test2
workSheet.write(0, 1, 'test2', style)
# 在工作表中合并单元格和行,参数依次为开始行数,结束行数,开始列数,结束列数,此处为第二行到第三行,第二列到第三列
workSheet.write_merge(1,2,1,2,'merge_test')
# 保存excel
workBook.save(excel_path)
官方文档:xlwt官方文档
xlutils
与xlwt类似,都是用于excel表格文件内容的新增与修改
安装
pip install xlutils
常用方法与属性
略,由于xlutils仍需要xlrd配合,不推荐使用
官方文档:xlutils官方文档
xlwings
注意事项：
传统上，xlwings需要安装Excel，因此只能在Windows和macOS上运行。请注意，macOS当前不支持UDF。
兼顾了xlrd和xlwt的读写excel数据的功能，并且支持的更为全面，有如下特点：
- xlwings能够非常方便的读写Excel文件中的数据，并且能够进行单元格格式的修改
- 可以和matplotlib以及pandas无缝连接，支持读写numpy、pandas数据类型，将matplotlib可视化图表导入到excel中。
- 可以调用Excel文件中VBA写好的程序，也可以让VBA调用用Python写的程序。
- 开源免费，一直在更新
安装
pip install xlwings
由于目前电脑为Linux且Windows没有excel，暂时不做介绍
官网地址：https://www.xlwings.org/
官方文档：https://docs.xlwings.org/en/stable/api.html
openpyxl
在openpyxl中，主要用到三个概念：Workbooks，Sheets，Cells。
Workbook就是一个excel工作表；
Sheet是工作表中的一张表页；
Cell就是简单的一个格。
openpyxl就是围绕着这三个概念进行的，不管读写都是“三板斧”：打开Workbook，定位Sheet，操作Cell。
安装
pip install openpyxl
注意事项
支持流行的lxml库，如果安装了该库，将使用该库。这在创建大型文件时特别有用。
为了能够将图像（jpeg、png、bmp…）包含到openpyxl文件中，您还需要“枕头”库，该库可以安装：
pip install pillow
新建文档及常用方法及属性
from openpyxl import Workbook
import datetime, os
new_excel_path = os.path.join(os.path.dirname(__file__), 'new_test.xlsx')
# 新建文件
wb = Workbook()
# 激活工作表
ws = wb.active
# 创建sheet
## 插入sheet到最后
ws_sheet = wb.create_sheet('sheet1')
## 插入sheet到最开始的位置, 可以指定插入到第几个位置
wb.create_sheet('sheet2', 0)
# 删除sheet：Use wb.remove(worksheet) or del wb[sheetname]
## 由于创建表格时会有一个默认的Sheet，因此可以先手动删除再做操作，默认的wb对象指向Sheet
del wb['Sheet']
# 写入数据
## 数据可以直接分配到单元格，并且支持输入公式
ws_sheet['A1'] = 42
## 数据依次填写表格中数据,一次为一行
ws_sheet.append([1,2,3])
## 支持python类型自动转换
ws_sheet['A3'] = datetime.datetime.now().strftime("%Y-%m-%d")
# 设置单元格的风格
from openpyxl.styles import Font, colors, Alignment
# 设置字体, 此处为字体为等线、大小24、斜体、加粗、颜色为蓝色
bold_itatic_24_font = Font(name='等线', size=24, italic=True, color=colors.BLUE, bold=True)
ws_sheet['A1'].font = bold_itatic_24_font
# 设置对齐方式，设置垂直水平居中，详细参数
"""
horizontal_alignments = (
"general", "left", "center", "right", "fill", "justify", "centerContinuous",
"distributed", )
vertical_aligments = (
"top", "center", "bottom", "justify", "distributed",
)
"""
ws_sheet['B1'].alignment = Alignment(horizontal='center', vertical='center')
# 设置行高、列宽
## 设置第二行行高为40
ws_sheet.row_dimensions[2].height = 40
## 设置第A列列宽
ws_sheet.column_dimensions['A'].width = 40
# 合并单元格，与excel软件一至，合并单元格如果存在数据只会保留左上角的数据
ws_sheet.merge_cells('A3:C3')
ws_sheet.merge_cells('D2:F4')
# 拆分单元格
ws_sheet.unmerge_cells('D2:F4')
# 保存数据
wb.save(new_excel_path)
读取原有文档及常用方法及属性
import os
from openpyxl import load_workbook
excel_path = os.path.join(os.path.dirname(__file__), 'test.xlsx')
# 打开已有文件
wb = load_workbook(excel_path)
# 选择表
ws2 = wb['范例1']
ws3 = wb.get_sheet_by_name('范例1')
print(ws2 is ws3)
print('-------------------------------')
# 访问单元格/获取单元格，默认都是以cell返回
# 单个单元格
## 直接通过索引的方式
print(ws2['A1'])
## 通过cell的行列获取单元格数据, 此方法也可以修改数据
d = ws2.cell(row=1,column=1,value=10)
print(d.value)
## 访问当前不存在的单元格,会自动创建对应的单元格对象
for i in range(1,100):
for j in range(1,100):
ws2.cell(row=i, column=j)
# 多个单元格
## 通过切片, 默认从左上角到右下角
data = ws2['A1':'C3']
print(data)
print(type(data))
for item in data:
for i in item:
print(i.value, end=',')
print()
## 通过行（列），一般获取的是整行整列数据
### 按照行，获取的是整行数据
print('获取C行数据:', ws2['C'])
print('获取C到D行数据:', ws2['C:D'])
### 按照列，获取的是整列数据
print('获取第10列的数据:', ws2[10])
print('获取6-10列数据:', ws2[6:10])
# 指定范围行列
## iter_rows与iter_cols功能类似，只不过遍历的顺序相反而已
## 从第一行、第三行到第一列、第三列，优先遍历列，再遍历行
for item in ws2.iter_rows(1,3,1,3):
for cell in item:
print(cell, end='')
print()
## 从第一行、第三行到第一列、第三列，优先遍历行，再遍历列
for item in ws2.iter_cols(1,3,1,3):
for cell in item:
print(cell, end='')
print()
# 遍历所有行或者列
print(tuple(ws2.rows))
print(tuple(ws2.columns))
print('-------------------------------')
# 修改单元格数据
## 访问第一行第一列的数据并修改为10
d = ws2.cell(row=1,column=1,value=10)
print(d.value)
print('-------------------------------')
# 修改sheet按钮的颜色
ws2.sheet_properties.tabColor = '1072BA'
print('-------------------------------')
# 获取表格的所有sheet名称
print(wb.sheetnames)
# 遍历获取表格的所有sheet名称
for item in wb:
print(item.title)
print('-------------------------------')
# 获取表格最大行和最大列
print('最大行为:', ws2.max_row)
print('最大列为:', ws2.max_column)
print('-------------------------------')
# 保存数据
wb.save(excel_path)
其他常用方法
from openpyxl.utils import get_column_letter, column_index_from_string
# 根据列的数字获得列对应的字母
print(get_column_letter(4))
# 根据列的字母获取对应的数字
print(column_index_from_string('E'))
生成2d图表
from openpyxl import Workbook
from openpyxl.chart import BarChart, Series, Reference
wb = Workbook(write_only=True)
ws = wb.create_sheet()
rows = [
('Number', 'Batch 1', 'Batch 2'),
(2, 10, 30),
(3, 40, 60),
(4, 50, 70),
(5, 20, 10),
(6, 10, 40),
(7, 50, 30),
]
for row in rows:
ws.append(row)
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Bar Chart"
chart1.y_axis.title = 'Test number'
chart1.x_axis.title = 'Sample length (mm)'
data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3)
cats = Reference(ws, min_col=1, min_row=2, max_row=7)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
ws.add_chart(chart1, "A10")
from copy import deepcopy
chart2 = deepcopy(chart1)
chart2.style = 11
chart2.type = "bar"
chart2.title = "Horizontal Bar Chart"
ws.add_chart(chart2, "G10")
chart3 = deepcopy(chart1)
chart3.type = "col"
chart3.style = 12
chart3.grouping = "stacked"
chart3.overlap = 100
chart3.title = 'Stacked Chart'
ws.add_chart(chart3, "A27")
chart4 = deepcopy(chart1)
chart4.type = "bar"
chart4.style = 13
chart4.grouping = "percentStacked"
chart4.overlap = 100
chart4.title = 'Percent Stacked Chart'
ws.add_chart(chart4, "G27")
wb.save("bar.xlsx")
3D图表
from openpyxl import Workbook
from openpyxl.chart import (
Reference,
Series,
BarChart3D,
)
wb = Workbook()
ws = wb.active
rows = [
(None, 2013, 2014),
("Apples", 5, 4),
("Oranges", 6, 2),
("Pears", 8, 3)
]
for row in rows:
ws.append(row)
data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=4)
titles = Reference(ws, min_col=1, min_row=2, max_row=4)
chart = BarChart3D()
chart.title = "3D Bar Chart"
chart.add_data(data=data, titles_from_data=True)
chart.set_categories(titles)
ws.add_chart(chart, "E5")
wb.save("bar3d.xlsx")
官方文档：https://openpyxl.readthedocs.io/en/stable/
xlswriter
安装
pip install XlsxWriter
持续更新中
