python基于openpyxl操作excel

Python3 操作Excel

• 环境: python3.11
• 系统: ubuntu20.04
• 使用依赖: openpyxl

1. 安装依赖

pip install openpyxl
1

跳转官网

2. 使用

2.1 创建工作簿

from openpyxl import Workbook
wb = Workbook()
1
2

工作簿至少要包含一个工作表(sheet), 可以使用如下来获取它

ws = wb.active
1

也可以创建自定义名称的工作表

ws1 = wb.create_sheet("name1")
1

2.2 查看工作簿下的所有工作表

print(wb.sheetnames)
1

2.3 访问/修改某个单元格数据

比如说修改A1单元格位置的参数

ws["A1"] = "姓名"
1

2.4 保存为文件

wb = Workbook()
wb.save('test.xlsx')
1
2

2.5 设置全局文字居中

from openpyxl import Workbook
from openpyxl.styles import Alignment


wb = Workbook()

sheet_name = "基础原料"
ws = wb.create_sheet(index=0, title=sheet_name)

# 填充标题内容 A1-C1
ws["A1"] = "名称"
ws["B1"] = "价格"
ws["C1"] = "描述"

# 设置居中样式
align = Alignment(horizontal='center', vertical='center', wrap_text=True)
# 设置所有文本居中对齐
for row in ws.iter_rows():
    for cell in row:
        cell.alignment = align
        
# 保存
wb.save('material.xlsx')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

2.6 单元格增加备注

from openpyxl import Workbook
from openpyxl.styles import Alignment
from openpyxl.comments import Comment

wb = Workbook()

sheet_name = "基础原料"
ws = wb.create_sheet(index=0, title=sheet_name)

# 填充标题内容 A1-C1
ws["A1"] = "名称"
ws["B1"] = "价格"
ws["C1"] = "描述"

# 给A1单元格增加备注
a1_coment = Comment(text="该单元格为名称", author="开发者(作者)")
ws["A1"].comment = a1_coment

# 设置居中样式
align = Alignment(horizontal='center', vertical='center', wrap_text=True)
# 设置所有文本居中对齐
for row in ws.iter_rows():
    for cell in row:
        cell.alignment = align
        
# 保存
wb.save('material.xlsx')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

2.7 合并单元格

ws.merge_cells(range_string='A1:B3')
1

如果在合并之前单元格中有数据，在合并之后数据可能会覆盖成空白，建议在使用时，先做结构，先合并之后，在插值

2. 8 以流形式返回

以Django为例

from django.http import HttpResponse

from openpyxl import Workbook
from openpyxl.styles import Alignment
from openpyxl.comments import Comment


def export_data():
    wb = Workbook()

    sheet_name = "基础原料"
    ws = wb.create_sheet(index=0, title=sheet_name)

    # 填充标题内容 A1-C1
    ws["A1"] = "名称"
    ws["B1"] = "价格"
    ws["C1"] = "描述"

    # 给A1单元格增加备注
    a1_coment = Comment(text="该单元格为名称", author="开发者(作者)")
    ws["A1"].comment = a1_coment

    # 设置居中样式
    align = Alignment(horizontal='center', vertical='center', wrap_text=True)
    # 设置所有文本居中对齐
    for row in ws.iter_rows():
        for cell in row:
            cell.alignment = align

    # 导出excel 表
    response = HttpResponse(content_type="application/msexcel")
    response["Access-Control-Expose-Headers"] = f"Content-Disposition"
    response[
        "Content-Disposition"
    ] = f'attachment;filename={quote(str(f"原料模板导出.xlsx"))}'
    wb.save(response)
return response
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37