Openpyxl操作Excel

1.锁定列

'''
锁定列的整体逻辑：
	1.首先对整张表进行锁定并设置密码
	2.对指定的不需要锁定的列，每一个单元格进行解锁
'''
# 模块导入
import openpyxl
from openpyxl.styles import Protection

excel_path = r"C:\1.xlsx"
# 1.打开表，读取Sheet
wb = openpyxl.load_workbook(excel_path)
ws = wb.active				# 默认读取第一个Sheet
# ws = wb['Sheet4']			# 读取指定Sheet

# 2.锁定整张表
ws.protection.set_password('123')		# 锁定整张表并设置密码，可以不写这一行
ws.protection.enable()

# 3.对指定列进行解锁
rows = ws.max_row			# 计算总行数
for e_row in range(1,rows+1):
    ws[f"A{str(e_row)}"].protection = Protection(locked=False)
    ws[f"B{str(e_row)}"].protection = Protection(locked=False)
   

# 4.文件保存
wb.save(filename=excel_path)

2.数据有效性

2.1 需求1：整数范围

import openpyxl
from openpyxl.worksheet.datavalidation import DataValidation

excel_path = r"C:\text.xlsx"

# 打开文件并读取
wb = openpyxl.load_workbook(excel_path)
ws = wb['Sheet1']

'''
对B列设置有效性规则，只能输入1-10之间的整数
'''
dv = DataValidation(type='whole',
                            # operator='greaterThanOrEqual',
                            operator='between',
                            formula1=1,
                    		formula2=10,
                            errorTitle='错误',
                            error='必须是1-10之间的整数')
rows = ws.max_row
dv.add(f"B1:B{str(str(rows))}")
ws.add_data_validation(dv)

wb.save(excel_path)

2.2 需求2：选择下拉框

import openpyxl
from openpyxl.worksheet.datavalidation import DataValidation

excel_path = r""

wb = openpyxl.Workbook()		# 不设置Excel路径，默认为新建
ws = wb.active

# 选择列表
dv = DataValidation(type="list",
                                formula1='"a,b,c"',
                                sqref=('A1:A10'),
                                promptTitle='提醒',
                                prompt='请输入a,b,c中的一个')
ws.data_validations.append(dv)
wb.save(excel_path)

3.读表-读取问题

import openpyxl

# 读表：如有公式，读取结果为公式
wb = openpyxl.load_workbook(excel_path)
ws = wb.active				# 默认读取第一个Sheet
# ws = wb['Sheet2']			# 读取指定Sheet
	
# 读表：所有单元格读取的均为数据，保存时也会覆盖掉公式
wb_copy = openpyxl.load_workbook(excel_path, data_only=True)
ws_copy = wb_copy.active

4.DataValidation参数解读

·参数：【type】
	源码
        type = NoneSet(values=("whole", "decimal", "list", "date", "time",
                           "textLength", "custom"))
    解析：
    	【whole】：整数
        【decimal】：小数
        【list】：序列，类似于下拉框，选择指定元素
        【date】：日期
        【time】：时间
        【textLength】：文本长度
        【custom】：自定义
        
·参数：【operator】
	源码
    	operator = NoneSet(values=("between", "notBetween", "equal", "notEqual","lessThan", "lessThanOrEqual", "greaterThan", "greaterThanOrEqual"))
    解析：
    	【between】：介于
        【notBetween】：未介于
        【equal】：等于
        【notEqual】：不等于
        【lessThan】：小于
        【lessThanOrEqual】：小于或等于
        【greaterThan】：大于
        【greaterThanOrEqual】：大于或等于

·参数：【errorStyle】
	源码
    	errorStyle = NoneSet(values=("stop", "warning", "information"))
	解析：
    	【stop】：停止
        【warning】：警告
        【information】：信息
        
        
·参数：【sqref】    
	最后一个参数sqref，是填写要设置数据验证的区域
    

import openpyxl
from openpyxl.worksheet.datavalidation import DataValidation

excel_path = r""

wb = openpyxl.Workbook()
ws = wb.active

# 选择列表
dv = DataValidation(type="list",
                                formula1='"a,b,c"',
                                sqref=('A1:A10'),
                                promptTitle='提醒',
                                prompt='请输入a,b,c中的一个')

# 只能填入1-10之间的整数
dv2 = DataValidation(type="whole",
                                  formula1=1,
                                  formula2=10,
                                  sqref=('B1:B10'),
                                  errorTitle='错误',
                                  error='输入的必须在1-10之间的整数')

ws.data_validations.append(dv)

ws.data_validations.append(dv2)

'''
# 设定单元格有效性并生效的第二种方式
# 不使用参数sqref指定单元格时，使用以下方式是同样的效果
dv2 = DataValidation(type='whole',
                            operator='greaterThan',
                            formula1=0,
                            errorTitle='错误',
                            error='必须是正整数')
dv2.add(f"AI{str(e_row)}")
ws.add_data_validation(dv2)
'''


wb.save(excel_path)
        

5.单元格规则生效的两种方式

'''
1.使用DataValidation方法中的参数sqref指定
2.add_data_validation方法实现
'''

import openpyxl
from openpyxl.worksheet.datavalidation import DataValidation

excel_path = r""

wb = openpyxl.Workbook()
ws = wb.active

# 选择列表
dv = DataValidation(type="list",
                                formula1='"a,b,c"',
                                sqref=('A1:A10'),
                                promptTitle='提醒',
                                prompt='请输入a,b,c中的一个')

# 只能填入1-10之间的整数
dv2 = DataValidation(type="whole",
                                  formula1=1,
                                  formula2=10,
                                  sqref=('B1:B10'),
                                  errorTitle='错误',
                                  error='输入的必须在1-10之间的整数')

ws.data_validations.append(dv)

ws.data_validations.append(dv2)

'''
# 设定单元格有效性并生效的第二种方式
# 不使用参数sqref指定单元格时，使用以下方式是同样的效果
dv2 = DataValidation(type='whole',
                            operator='greaterThan',
                            formula1=0,
                            errorTitle='错误',
                            error='必须是正整数')
dv2.add(f"AI{str(e_row)}")
ws.add_data_validation(dv2)
'''


wb.save(excel_path)