【python操作excel数据有效性(含excel的基本操作)】学习笔记_openpyxl 数据有效性

Excel的数据有效性功能，可以对输入单元格的数据进行内容上的限制。当我们利用 excel 生成代码时，可以通过此类方式规范输入。
以下是：【openpyxl官方文档】

excel的基本操作

• 生成xlsx

wb = Workbook()
wb.save(xls_path)
1
2

• 加载excel文件

# 加载excel文件(data_only=True 代表以数据形式而非公式)
wb = load_workbook(xls_path, data_only=True)
1
2

• 新建 sheet页

wb = load_workbook(xls_path, data_only=True)
wb.create_sheet(title=sheet_name)
1
2

• 删除 sheet页

wb = load_workbook(xls_path, data_only=True)
wb.remove(wb[sheet_name])
1
2

• 加载 sheet页

wb = load_workbook(xls_path, data_only=True)
xxx_sheet = wb[sheet_name]
1
2

• 设置列宽

wb = load_workbook(xls_path, data_only=True)
xxx_sheet = wb[sheet_name]
xxx_sheet.column_dimensions["A"].width = 20
1
2
3

• 加载 单元格

wb = load_workbook(xls_path, data_only=True)
xxx_sheet = wb[sheet_name]
cell = xxx_sheet.cell(row=1, column=1)  # 1行1列
1
2
3

• 设置单元格格式

wb = load_workbook(xls_path, data_only=True)
xxx_sheet = wb[sheet_name]
cell = xxx_sheet.cell(row=1, column=1)  # 1行1列

from openpyxl.styles import Font
font = Font(b=True, color="FF0000")  # 红色字体
cell.font = font
1
2
3
4
5
6
7

• 单元格赋值

wb = load_workbook(xls_path, data_only=True)
xxx_sheet = wb[sheet_name]
cell = xxx_sheet.cell(row=1, column=1)  # 1行1列
cell.value = 'YourName666'
1
2
3
4

• 获取 sheet页 最大列数、行数

wb = load_workbook(xls_path, data_only=True)
xxx_sheet = wb[sheet_name]
# 最大列数
print(xxx_sheet.max_column)
# 最大行数
print(xxx_sheet.max_row)
1
2
3
4
5
6

• 保存

wb = load_workbook(xls_path, data_only=True)
wb.save(xls_path)
1
2

excel数据有效性

wb = load_workbook(xls_path, data_only=True)
xxx_sheet= wb[sheet_name]

# 清空原有有效性校验规则
xxx_sheet.data_validations.dataValidation = []

# 建立有效性（以"序列"的形式）
dv = DataValidation(type="list", formula1=f'={sheet_name}!$A$1', allow_blank=True)
# 设置自定义提示消息
dv.promptTitle = "XXX"
dv.prompt = "XXX"
# 设置自定义错误消息
dv.errorTitle = "XXX"
dv.error = "XXX"

dv.add(xxx_sheet.cell(1, 1))
xxx_sheet.add_data_validation(dv)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

完整代码

# -*-coding: utf-8 -*-
import os
from time import sleep
from openpyxl import load_workbook, Workbook
from openpyxl.worksheet.datavalidation import DataValidation


class Data_Validation:

    def __init__(self, xls_path: str = 'demo.xlsx'):
        # 判断文件是否存在，若不存在，则生成该文件
        if os.path.exists(xls_path) is False:
            print(f"<{xls_path}> not exist, creat <{xls_path}>...")
            wb = Workbook()
            wb.save(xls_path)

        # 加载excel文件(data_only=True 代表以数据形式而非公式)
        self.wb = load_workbook(xls_path, data_only=True)
        self.sheet_init()

    def sheet_init(self):
        """
        初始化 sheet页，即删除不必要的 sheet 页，新建所需的 sheet 页，并附上测试数据
        :return:
        """
        sheet_need = ['DEMO', 'base']
        for sheet_name in self.wb.sheetnames:
            if sheet_name not in sheet_need:
                self.wb.remove(self.wb[sheet_name])
                print(f"<{sheet_name}> deleted")
        for sheet_name in sheet_need:
            if sheet_name not in self.wb.sheetnames:
                self.wb.create_sheet(title=sheet_name)
                print(f"<{sheet_name}> added")

        # 加载 sheet页
        base_sheet = self.wb['base']
        # 设置列宽
        base_sheet.column_dimensions["A"].width = 20
        # 加载 单元格
        cell = base_sheet.cell(row=1, column=1)

        # 设置单元格格式
        from openpyxl.styles import Font
        font = Font(b=True, color="FF0000")  # 红色字体
        cell.font = font

        # 单元格赋值
        cell.value = 'YourName666'

        # # 最大列数
        # print(base_sheet.max_column)
        # # 最大行数
        # print(base_sheet.max_row)

        self.wb.save('demo.xlsx')

    def load_validation(self, sheet_name: str = 'DEMO'):
        DEMO_sheet = self.wb[sheet_name]
        # 设置列宽
        DEMO_sheet.column_dimensions["A"].width = 20
        # 清空原有有效性校验规则
        DEMO_sheet.data_validations.dataValidation = []

        # 建立有效性（以"序列"的形式）
        dv = DataValidation(type="list", formula1=f'=base!$A$1', allow_blank=True)
        # 设置自定义提示消息
        dv.promptTitle = "SELECT DATA"
        dv.prompt = "Select data from <base>"
        # 设置自定义错误消息
        dv.errorTitle = "DATA ERROR"
        dv.error = "Select data from <base>"

        dv.add(DEMO_sheet.cell(1, 1))
        DEMO_sheet.add_data_validation(dv)
        
        print(f"<{sheet_name}> add data validation")

        self.wb.save('demo.xlsx')


if __name__ == '__main__':
    ins = Data_Validation()
    ins.load_validation()
    sleep(3)
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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85

运行结果(当前路径不存在 demo.xlsx 的情况下)

• 代码运行提示：

<demo.xlsx> not exist, creat <demo.xlsx>...
<Sheet> deleted
<DEMO> added
<base> added
<DEMO> add data validation
1
2
3
4
5

• 运行前目录文件结构

project
│   excel.py
1
2

• 运行后目录文件结构

project
│   demo.xlsx
│   excel.py
1
2
3

• 生产的 demo.xlsx 文件
  文件下有两个 sheet页，分别为 DEMO，base
  
  base内容如下
  
  DEMO内容如下