Python类库openpyxl处理Excel表格

openpyxl是一个用于读写Excel文件的Python库，支持.xlsx格式。它可以新建Excel文件、读取单元格数据、修改单元格数据、保存Excel文件。

openpyxl的主要功能包括如下：

1. 新建Excel文件、sheet。
2. 读取和写入单元格数据。
3. 修改单元格样式、格式。
4. 删除行列、插入行列。
5. 创建和删除Excel文件中的图表。
6. 往Excel文件添加图片。
7. 复制和移动单元格。
8. 设定单元格公式。
9. 转存为HTML、JSON等格式。

下面是一些常用的示例：

1. 打开Excel文件：

from openpyxl import load_workbook
workbook = load_workbook('example.xlsx')
sheet = workbook.active

2. 获取单元格数据：

cell_value = sheet.cell(row, col).value  # 获取第row行第col列单元格的值

3. 写入单元格数据：

sheet.cell(row, col).value = value  # 将value写入第row行第col列单元格

4. 设置单元格样式：

from openpyxl.styles import Font, colors
font = Font(color=colors.RED, bold=True)
sheet.cell(row, col).font = font  # 将第row行第col列单元格的字体颜色设为红色、加粗

5. 设置单元格格式：

from openpyxl.styles import numbers
sheet.cell(row, col).number_format = numbers.FORMAT_GENERAL  # 将第row行第col列单元格的数据格式设为“常规”

6. 删除行列：

sheet.delete_rows(2, 3)  # 删除第2行到第3行
sheet.delete_cols(2, 3)  # 删除第2列到第3列

7. 插入行列：

sheet.insert_rows(2, 5)  # 在第2行之后插入5行
sheet.insert_cols(2, 5)  # 在第2列之后插入5列

8. 新建Excel文件和sheet：

from openpyxl import Workbook
workbook = Workbook()
sheet = workbook.active
sheet.title = 'Sheet1'  # 设置sheet的名称

9. 创建Excel文件中的图表：

from openpyxl.chart import BarChart, Reference

data = Reference(sheet, min_col=2, min_row=1, max_col=3, max_row=6)
chart = BarChart()
chart.add_data(data)
sheet.add_chart(chart, "A10")  # 在A10单元格位置添加图表

10. 添加图片：

from openpyxl.drawing.image import Image

img = Image('test.png')
sheet.add_image(img, 'A1')  # 添加图片至A1单元格位置

11. 复制单元格：

sheet.cell(row=1, column=2).value = 4
sheet.cell(row=2, column=2).value = '=B1*3'

sheet.copy_cells(source_range="B2:B2", target_range="B3:B5")  # 将B2复制到B3:B5中

12. 移动单元格：

sheet.move_cell(start_row=1, start_column=2, rows=1, cols=1)  # 将B1移动到C2

13. 设定单元格公式：

sheet.cell(row=1, column=1).value = 10
sheet.cell(row=2, column=1).value = 20
sheet.cell(row=3, column=1).value = '=SUM(A1:A2)'  # 在第3行第1列单元格写入“=SUM(A1:A2)”公式

14. 存储Excel文件：

workbook.save("example.xlsx")

15. 获取单元格行列的坐标：

from openpyxl.utils import get_column_letter

col_index = sheet.cell(row=1, column=1).column  # 返回第1行第1列单元格所在列的编号（A对应1，B对应2）
col_letter = get_column_letter(col_index)  # 返回第1行第1列单元格所在列的字母表示

16. 获取单元格所在的行列范围：

range_str = sheet.cell(row=1, column=1).coordinate  # 返回第1行第1列单元格所在行列范围（如“A1”）

17. 查找指定文本所在的所有单元格：

find_text = 'apple'
for row in sheet.iter_rows(min_row=1, max_row=1000, min_col=1, max_col=26, values_only=True):
    if find_text in row:
        print('Find at row:', row[0])

18. 将Excel文件转存为HTML格式：

from openpyxl import Workbook
from openpyxl.utils import get_column_letter
from openpyxl.writer.html import HTMLWriter

workbook = load_workbook('example.xlsx')
sheet = workbook.active

output = open('example.html', 'w', encoding='utf-8')
writer = HTMLWriter(workbook)
writer.write(output, sheet)
output.close()

19. 将Excel文件转存为JSON格式：

import json
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

workbook = load_workbook("example.xlsx")
sheet = workbook.active

data = []
for row in sheet.iter_rows(min_row=2, max_col=3, max_row=7, values_only=True):
    data.append(dict(zip(('id', 'score_1', 'score_2'), row)))

json.dump(data, open("example.json", "w"), ensure_ascii=False)

20. 给指定范围内的单元格添加注释：

from openpyxl.comments import Comment

comment = Comment("This is a comment", "Author Name")
sheet['A1'].comment = comment  # 给A1单元格添加注释
sheet['B2:C3'].comment = Comment("Another comment", "Author")  # 给B2到C3范围内的单元格添加注释

workbook.save("example.xlsx")

整理不易，欢迎点赞转发收藏，关注我吗，每天分享运维知识。