【Excel自动化办公】使用openpyxl对Excel进行读写操作

目录

一、环境安装

1.1 创建python项目

1.2 安装openpyxl依赖

二、Excel数据读取操作

三、Excel数据写入操作

3.1 创建空白工作簿

3.2 写数据

四、设置单元格样式

4.1 字体样式

4.2 设置单元格背景填充色

4.3 设置单元格边框样式

4.4 单元格对齐方式

4.5 数据筛选

全部筛选

设置筛选条件

排序

五、公式操作

5.1 设置公式

5.2 读取公式结果

六、设置行高列宽

七、单元格合并与拆分

7.1 合并

7.2 拆分

八、冻结窗口

8.1 冻结

8.2 解冻

九、绘制图表

---

一、环境安装

python处理Excel的方式：openpyxl

1.1 创建python项目

指定虚拟环境为python3.9版本...

1.2 安装openpyxl依赖

pip install openpyxl==3.0.7

二、Excel数据读取操作

我们先准备一个名为test.xlsx的表格。

import openpyxl# 创建一个工作簿对象
wb = openpyxl.load_workbook('./test.xlsx')# 获取工作簿的sheet表的名称
sheet_list = wb.sheetnames
print(sheet_list) # ['作家列表', '学生列表']sheet = wb['作家列表']# 获取活动表
print(wb.active) # <Worksheet "学生列表">cell = sheet['A3']
print(cell.value) # 余华
print(cell.row) # 3
print(cell.column) # 1
print(cell.coordinate) # A3# 获取第1行第2列的值
cell = sheet.cell(row=1, column=2).value
print(cell) # 书籍# 进行切片操作，从而取得电子表格中一行、一列或一个矩形区域中所有Cell对象
for cell_row in sheet['A1':'B4']:for cell in cell_row:print(cell.coordinate, cell.value)# 要访问特定行或列的单元格的值，也可以使用Worksheet对象的rows和columns属性
for cell in list(sheet.columns)[0]: # 获取第一列的cellprint(cell.value)# 获取工作表中行数和列数
print(sheet.max_row) # 4
print(sheet.max_column) # 2

三、Excel数据写入操作

3.1 创建空白工作簿

import openpyxl# 创建一个新的工作簿对象
wb = openpyxl.Workbook()
# 给工作簿设置名称
sheet = wb.active
sheet.title = '跟进记录表'# 保存工作表
wb.save('./第一个工作簿.xlsx')

3.2 写数据

import openpyxl# 创建一个新的工作簿对象
wb = openpyxl.load_workbook('./第一个工作簿.xlsx')
# 创建sheet
wb.create_sheet(title='销售记录')
wb.create_sheet(index=1, title='养殖技术')print(wb.sheetnames) # ['跟进记录表', '养殖技术', '销售记录']# 删除sheet页
del wb['养殖技术']
print(wb.sheetnames) # ['跟进记录表', '销售记录']sheet = wb['销售记录']
sheet['A1'] = 'hello'
sheet['B2'] = 'world'
wb.save('./第一个工作簿.xlsx')

四、设置单元格样式

4.1 字体样式

from openpyxl.styles import Font
import openpyxlwb = openpyxl.Workbook()
sheet = wb.active
sheet['A3'] = '字体'
sheet['A3'].font = Font(name='楷体', color='8470FF')
wb.save('./styles.xlsx')

Font()的参数有很多，比如：

• italic=True：设置斜体
• size=xxx：设置字体大小
• underline='sigle'：单下划线
• b=True：加粗
• ....

4.2 设置单元格背景填充色

from openpyxl.styles import Font, PatternFill
import openpyxlwb = openpyxl.Workbook()
sheet = wb.active
sheet['A3'] = '背景填充色'
sheet['A3'].fill = PatternFill(patternType='solid', fgColor='8470FF')
wb.save('./styles.xlsx')

4.3 设置单元格边框样式

from openpyxl.styles import Side, Border
import openpyxlwb = openpyxl.Workbook()
sheet = wb.active
sheet['F4'] = '效果1'
sheet['F5'] = '效果2'
s1 = Side(style='thin', color='8470FF')
s2 = Side(style='double', color='ff0000')
# 只作用上边框
sheet['F4'].border = Border(top=s1)
sheet['F5'].border = Border(top=s2, bottom=s1, left=s2, right=s1)
wb.save('./styles.xlsx')

4.4 单元格对齐方式

from openpyxl.styles import Alignment
import openpyxlwb = openpyxl.load_workbook('./cellBorder.xlsx')
sheet = wb['Sheet1']
# horizontal代表水平对齐  vertical代表垂直对齐
c1 = sheet['C1'].alignment = Alignment(horizontal='right', vertical='center') # 水平靠右对齐 垂直居中对齐
c2 = sheet['C2'].alignment = Alignment(vertical='center')
c3 = sheet['C3'].alignment = Alignment(vertical='top')
wb.save('./cellBorder.xlsx')

4.5 数据筛选

全部筛选

import openpyxlwb = openpyxl.load_workbook('./筛选器.xlsx')
sheet = wb['Sheet1']
# 创建筛选器对象：auto_filter
# ref：使得筛选器对象引用指定的区域
sheet.auto_filter.ref = 'A1:D7'
wb.save('./筛选器.xlsx')

设置筛选条件

import openpyxlwb = openpyxl.load_workbook('./筛选器.xlsx')
sheet = wb['Sheet1']
# 创建筛选器对象：auto_filter
# ref：使得筛选器对象引用指定的区域
sheet.auto_filter.ref = 'A1:D7'
# add_filter_column参数：参数1表示对指定区域哪一列进行设置筛选条件，参数2：筛选条件内容
sheet.auto_filter.add_filter_column(1, ['北京', '深圳'])
wb.save('./筛选器.xlsx')

排序

import openpyxlwb = openpyxl.load_workbook('./筛选器.xlsx')
sheet = wb['Sheet1']
# 创建筛选器对象：auto_filter
# ref：使得筛选器对象引用指定的区域
sheet.auto_filter.ref = 'A1:D7'
# 参数1：排序列  参数2：升降序 True为降序 false为升序
sheet.auto_filter.add_sort_condition(ref='D2:D7', descending=True)
wb.save('./筛选器.xlsx')

五、公式操作

5.1 设置公式

import openpyxlwb = openpyxl.Workbook()
sheet = wb.active
sheet['A1'] = 200
sheet['A2'] = 300
sheet['A3'] = '=SUM(A1:A2)'wb.save('./sum.xlsx')

5.2 读取公式结果

import openpyxlwb = openpyxl.load_workbook('./sum.xlsx')
sheet = wb.active
print(sheet['A3'].value) # =SUM(A1:A2)

这个结果居然是读取到了公式字符串，但我们想要的是公式计算的结果，也就是A3的结果，如何解决呢？

import openpyxlwb = openpyxl.load_workbook('./sum.xlsx', read_only=True)
sheet = wb.active
# 注意：如果返回的是None，则打开Excel工作簿，将内容手动保存下即可，不方便但是没办法
print(sheet['A3'].value)

六、设置行高列宽

设置行高和列宽：Worksheet对象有 row_dimensions 和 column_dimensions属性，控制行高和列宽。

import openpyxlwb = openpyxl.Workbook()
sheet = wb.active
# 设置行高
sheet.row_dimensions[2].height = 50
# 设置列宽
sheet.column_dimensions['A'].width = 80wb.save('./hw.xlsx')

七、单元格合并与拆分

7.1 合并

import openpyxlwb = openpyxl.Workbook()
sheet = wb.active
# 合并
sheet.merge_cells('A1:D7')
sheet['A1'] = 'Python'
wb.save('./merge.xlsx')

7.2 拆分

import openpyxlwb = openpyxl.load_workbook('./merge.xlsx')
sheet = wb.active
# 拆分
sheet.unmerge_cells('A1:D7')
wb.save('./merge.xlsx')

八、冻结窗口

8.1 冻结

import openpyxlwb = openpyxl.load_workbook('./produceSales.xlsx')
sheet = wb.active
# 冻结首行标题
sheet.freeze_panes = 'A2'
wb.save('./produceSales.xlsx')

8.2 解冻

import openpyxlwb = openpyxl.load_workbook('./produceSales.xlsx')
sheet = wb.active
# 冻结首行标题
sheet.freeze_panes = None
wb.save('./produceSales.xlsx')

九、绘制图表

openpyxl支持利用工作表中单元格的数据，创建条形图、折线图、散点图和饼图。要创建图表，需要做下列事情：

• 创建一个Reference对象，表示作用在图表中的数据区域
• 创建图表对象
• 往图表对象中添加数据
• 将图表添加到指定sheet中

import openpyxlwb = openpyxl.load_workbook('./echarts.xlsx')
sheet = wb.active# 1. 创建一个Reference对象，表示作用在图表中的数据区域
values = openpyxl.chart.Reference(sheet, min_row=1, min_col=1, max_row=10, max_col=5)# 2. 创建图表对象
chart = openpyxl.chart.BarChart()# 3. 往图表对象中添加数据
chart.add_data(values)# 4. 将图表添加到指定sheet中
sheet.add_chart(chart, 'G1')
wb.save('./echarts.xlsx')