【python操作excel数据有效性（含excel的基本操作）】学习笔记
Excel的数据有效性功能，可以对输⼊单元格的数据进⾏内容上的限制。当我们利⽤ excel ⽣成代码时，可以通过此类⽅式规范输⼊。以下是：
excel的基本操作
⽣成xlsx
wb = Workbook()
wb.save(xls_path)
加载excel⽂件
# 加载excel⽂件(data_only=True 代表以数据形式⽽⾮公式)
wb = load_workbook(xls_path, data_only=True)
新建 sheet页
wb = load_workbook(xls_path, data_only=True)
删除 sheet页
wb = load_workbook(xls_path, data_only=True)
加载 sheet页
wb = load_workbook(xls_path, data_only=True)
xxx_sheet = wb[sheet_name]
设置列宽
wb = load_workbook(xls_path, data_only=True)
xxx_sheet = wb[sheet_name]
lumn_dimensions["A"].width =20
加载 单元格
wb = load_workbook(xls_path, data_only=True)
xxx_sheet = wb[sheet_name]
cell = ll(row=1, column=1)# 1⾏1列
设置单元格格式
wb = load_workbook(xls_path, data_only=True)
python怎么读取excel的数据xxx_sheet = wb[sheet_name]
cell = ll(row=1, column=1)# 1⾏1列
from openpyxl.styles import Font
font = Font(b=True, color="FF0000")# 红⾊字体
cell.font = font
单元格赋值
wb = load_workbook(xls_path, data_only=True)
xxx_sheet = wb[sheet_name]
cell = ll(row=1, column=1)# 1⾏1列
cell.value ='YourName666'
获取 sheet页 最⼤列数、⾏数
wb = load_workbook(xls_path, data_only=True)
xxx_sheet = wb[sheet_name]
# 最⼤列数
print(xxx_sheet.max_column)
# 最⼤⾏数
print(xxx_sheet.max_row)
保存
wb = load_workbook(xls_path, data_only=True)
wb.save(xls_path)
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"
# 设置⾃定义错误消息
</div><div>< ="XXX"</span></div><div><span>dv.add(ll(1,1))</span></div><div><span>xxx_sheet.add_data_validation(dv)</span></div><div><span>完整代码</span></div><div><span># -*-coding: utf-8 -*-</span></div><div><span>import os</span></div><div><span>from time import sleep</span></div><div><span></span></div><div><span>from openpyxl import load_workbook, Workbook</span></div><div><span>from openpyxl.worksheet.datavalidation import DataValidation</span></div><div><span>class Data_Validation:</span></div><div><span>def__init__(self, xls_path:str='demo.xlsx'):</span></div><div><span># 判断⽂件是否存在，若不存在，则⽣成该⽂件</span></div><div><span>if ists(xls_path)is False:</span></div><div><span>print(f"<{xls_path}> not exist, creat <{xls_path}>...")</span></div><div><span>wb = Workbook()</span></div><div><span>wb.save(xls_path)</span></div><div><span># 加载excel⽂件(data_only=True 代表以数据形式⽽⾮公式)</span></div><div><span>self.wb = load_workbook(xls_path, data_only=True)</span></div><div><span></span></div><div><span>self.sheet_init()</span></div><div><span>def sheet_init(self):</span></div><div><span>"""</span></div><div><span>初始化 sheet页，即删除不必要的 sheet 页，新建所需的 sheet 页，并附上测试数据</span></div><div><span>初始化 sheet页，即删除不必要的 sheet 页，新建所需的 sheet 页，并附上测试数据 :return:</span></div><div><span>"""</span></div><div><span>sheet_need =['DEMO','base']</span></div><div><span>for sheet_name in self.wb.sheetnames:</span></div><div><span>if sheet_name not in sheet_need:</span></div><div><span>ve(self.wb[sheet_name])</span></div><div><span>print(f"<{sheet_name}> deleted")</span></div><div><span></span></div><div><span>for sheet_name in sheet_need:</span></div><div><span>if sheet_name not in self.wb.sheetnames:</span></div><div><span>ate_sheet(title=sheet_name)</span></div><div><span>print(f"<{sheet_name}> added")</span></div><div><span># 加载 sheet页</span></div><div><span>base_sheet = self.wb['base']</span></div><div><span># 设置列宽</span></div><div><span>lumn_dimensions["A"].width =20</span></div><div><span># 加载单元格</span></div><div><span>cell = ll(row=1, column=1)</span></div><div><span># 设置单元格格式</span></div><div><span></span></div><div><span>from openpyxl.styles import Font</span></div><div><span>font = Font(b=True, color="FF0000")# 红⾊字体</span></div><div><span>cell.font = font</span></div><div><span># 单元格赋值</span></div><div><span>cell.value ='YourName666'</span></div><div><span># # 最⼤列数</span></div><div><span># print(base_sheet.max_column)</span></div><div><span># # 最⼤⾏数</span></div><div><span># print(base_sheet.max_row)</span></div><div><span>self.wb.save('demo.xlsx')</span></div><div><span>def load_validation(self, sheet_name:str='DEMO'):</span></div><div><span></span></div><div><span>DEMO_sheet = self.wb[sheet_name]</span></div><div><span># 设置列宽</span></div><div><span>lumn_dimensions["A"].width =20</span></div><div><span># 清空原有有效性校验规则</span></div><div><span>DEMO_sheet.data_validations.dataValidation =[]</span></div><div><span># 建⽴有效性（以"序列"的形式）</span></div><div><span>dv = DataValidation(type="list", formula1=f'=base!$A$1', allow_blank=True) # 设置⾃定义提⽰消息</span></div><div><span>dv.promptTitle ="SELECT DATA"</span></div><div><span>dv.prompt ="Select data from <base>"</span></div><div><span># 设置⾃定义错误消息</span></div><div><Title ="DATA ERROR"</span></div><div><span></span></div><div>< ="Select data from <base>"</span></div><div><span>dv.add(ll(1,1))</span></div><div><span>DEMO_sheet.add_data_validation(dv)</span></div><div><span>print(f"<{sheet_name}> add data validation")</span></div><div><span>self.wb.save('demo.xlsx')</span></div><div><span>if __name__ =='__main__':</span></div><div><span>ins = Data_Validation()</span></div><div><span>ins.load_validation()</span></div><div><span>sleep(3)</span></div><div><span>运⾏结果(当前路径不存在 demo.xlsx 的情况下)</span></div><div><span>代码运⾏提⽰：</span></div><div><span></span></div><div><span><demo.xlsx> not exist, creat <demo.xlsx>...</span></div><div><span><Sheet> deleted</span></div><div><span><DEMO> added</span></div><div><span><base> added</span></div><div><span><DEMO> add data validation</span></div><div><span>运⾏前⽬录⽂件结构</span></div><div><span>project</span></div><div><span>│ excel.py</span></div><div><span>运⾏后⽬录⽂件结构</span></div><div><span>project</span></div><div><span>│ demo.xlsx</span></div><div><span></span></div><div><span>│ excel.py</span></div><div><span>⽣产的 demo.xlsx ⽂件</span></div><div><span>⽂件下有两个 sheet页，分别为 DEMO，base</span></div><div></div><div><span>base内容如下</span></div><div></div><div><span></span></div><div><span>DEMO内容如下</span></div><div></div>
<div class="umCopyright">
<p>版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。</p>
</div>
</div>
<div class="single-nepr">
<div class="entry-page-prev lazy" style="background:#364cc6"> <a href="https://www.wanfa688.com/html/Products/23762.html" title="Pythonpandas学习总结"><span>Pythonpandas学习总结</span></a>
<div class="entry-page-info"><span class="pull-left">« 上一篇</span></div>
</div>
<div class="entry-page-next lazy" style="background:#364cc6"> <a href="https://www.wanfa688.com/html/Products/23769.html" title="详解基于朴素贝叶斯的情感分析及Python实现"><span>详解基于朴素贝叶斯的情感分析及Python实现</span></a>
<div class="entry-page-info"><span class="pull-right">下一篇 »</span></div>
</div>
</div>
<div class="comments">
<div class="postcomm block-wrap" id="divCommentPost">
<h2 class="block-title"> 发表评论 </h2>
<form id="saypl" target="_self" method="post" name="saypl" action="https://www.wanfa688.com/e/pl/doaction.php">
<input name="classid" type="hidden" id="classid" value="1" />
<input name="id" type="hidden" id="id" value="23768" />
<input name="enews" type="hidden" id="enews" value="AddPl" />
<input name="repid" type="hidden" id="repid" value="0" />
<input type="hidden" name="ecmsfrom" value="https://www.wanfa688.com/html/Products/23768.html">
<input name="password" type="hidden" class="inputText" id="password" value="" size="16" />
<input name="nomember" type="hidden" id="nomember" value="1" checked="checked" />
<div class="comment-box comment-ul3">
<input type="text" name="username" id="inpName" class="text" value="" tabindex="1" placeholder="称呼(*)">
</div>
<div class="comment-box comment-ul3 comment-ul3-2">
<input type="text" name="inpEmail" id="inpEmail" class="text" value="" tabindex="2" placeholder="邮箱">
</div>
<div class="comment-box comment-ul3">
<input type="text" name="inpHomePage" id="inpHomePage" class="text" value="" tabindex="3" placeholder="网站">
</div>
<div class="comment-box comment-textarea">
<textarea name="saytext" id="txaArticle" class="text" cols="50" rows="4" tabindex="5" placeholder="评论内容"></textarea>
</div>
<div class="comment-box comment-verify">
<input type="text" name="key" id="inpVerify" class="text" value="" tabindex="5" placeholder="验证码" />
<img src="https://www.wanfa688.com/e/ShowKey/?v=pl" class="verifyimg" name="plKeyImg" id="plKeyImg" onclick="plKeyImg.src='https://www.wanfa688.com/e/ShowKey/?v=pl&t='+Math.random()" title="看不清楚,点击刷新" />
</div>
<input name="sumbit" type="submit" tabindex="6" value="发表评论" class="sub">
<a rel="nofollow" id="cancel-reply" href="#divCommentPost" style="display:none" class="button reply sub">取消回复</a>
</form>
</div>
<label id="AjaxCommentBegin"></label>
<script src="https://www.wanfa688.com/e/pl/more/?classid=1&id=23768&num=10"></script>
<div class="pagebar commentpagebar"> </div>
<label id="AjaxCommentEnd"></label>
</div>
</div>
</div>
<div class="aside">
<div id="asideurl">
</div>
<div id="directory"></div>
<div class="block-wrap" id="side-top-dxt">
<h2 class="block-title">推荐文章<i class="mcico mico-right"></i></h2>
<div class="mcdas">
<a href="https://www.wanfa688.com/html/Products/512986.html" class="photo-item" target="_blank">
<div class="photo-item-inner">
<h3 class="photo-item-title">一种基于正则表达式的DBC文件解析及报文分析方法[发明专利]</h3>
</div>
</a>
<a href="https://www.wanfa688.com/html/news/512985.html" class="photo-item" target="_blank">
<div class="photo-item-inner">
<h3 class="photo-item-title">工龄小数点提取</h3>
</div>
</a>
<a href="https://www.wanfa688.com/html/news/512984.html" class="photo-item" target="_blank">
<div class="photo-item-inner">
<h3 class="photo-item-title">非零金额 正则表达式</h3>
</div>
</a>
<a href="https://www.wanfa688.com/html/news/512983.html" class="photo-item" target="_blank">
<div class="photo-item-inner">
<h3 class="photo-item-title">提取文本中数字的函数</h3>
</div>
</a>
<a href="https://www.wanfa688.com/html/news/512982.html" class="photo-item" target="_blank">
<div class="photo-item-inner">
<h3 class="photo-item-title">vue数字相加小数点变长-概述说明以及解释</h3>
</div>
</a>
</div>
</div>
<div class="block-wrap" id="side-hot-cmt-item">
<h2 class="block-title">热门文章<i class="mcico mico-right"></i></h2>
<ul>
<li class="mclas">
<div class="list-content"><a title="excel文字递增函数公式" target="_blank" href="https://www.wanfa688.com/html/Products/512958.html">
<h3>excel文字递增函数公式</h3>
</a><span class="post-date">2025-02-08</span></div>
</li>
<li class="mclas">
<div class="list-content"><a title="数字递增公式" target="_blank" href="https://www.wanfa688.com/html/Products/512954.html">
<h3>数字递增公式</h3>
</a><span class="post-date">2025-02-08</span></div>
</li>
<li class="mclas">
<div class="list-content"><a title="notepad 正则变量运算" target="_blank" href="https://www.wanfa688.com/html/Products/512953.html">
<h3>notepad 正则变量运算</h3>
</a><span class="post-date">2025-02-08</span></div>
</li>
<li class="mclas">
<div class="list-content"><a title="C++regex库常用函数及实例" target="_blank" href="https://www.wanfa688.com/html/Products/512952.html">
<h3>C++regex库常用函数及实例</h3>
</a><span class="post-date">2025-02-08</span></div>
</li>
<li class="mclas">
<div class="list-content"><a title="js正则表达式之前瞻后顾与非捕获分组" target="_blank" href="https://www.wanfa688.com/html/Products/512951.html">
<h3>js正则表达式之前瞻后顾与非捕获分组</h3>
</a><span class="post-date">2025-02-08</span></div>
</li>
<li class="mclas">
<div class="list-content"><a title="indesign正则数字和英文之间的空格" target="_blank" href="https://www.wanfa688.com/html/Products/512949.html">
<h3>indesign正则数字和英文之间的空格</h3>
</a><span class="post-date">2025-02-08</span></div>
</li>
<li class="mclas">
<div class="list-content"><a title="C#匹配中文字符串的4种正则表达式分享" target="_blank" href="https://www.wanfa688.com/html/Products/512948.html">
<h3>C#匹配中文字符串的4种正则表达式分享</h3>
</a><span class="post-date">2025-02-08</span></div>
</li>
<li class="mclas">
<div class="list-content"><a title="PHP正则表达式匹配中文字符" target="_blank" href="https://www.wanfa688.com/html/Products/512946.html">
<h3>PHP正则表达式匹配中文字符</h3>
</a><span class="post-date">2025-02-08</span></div>
</li>
<li class="mclas">
<div class="list-content"><a title="匹配中文汉字的正则表达式介绍" target="_blank" href="https://www.wanfa688.com/html/Products/512945.html">
<h3>匹配中文汉字的正则表达式介绍</h3>
</a><span class="post-date">2025-02-08</span></div>
</li>
<li class="mclas">
<div class="list-content"><a title="Python正则表达式如何进行字符串替换" target="_blank" href="https://www.wanfa688.com/html/Products/512942.html">
<h3>Python正则表达式如何进行字符串替换</h3>
</a><span class="post-date">2025-02-08</span></div>
</li>
<li class="mclas">
<div class="list-content"><a title="orcl中用正则表达式" target="_blank" href="https://www.wanfa688.com/html/Products/512940.html">
<h3>orcl中用正则表达式</h3>
</a><span class="post-date">2025-02-08</span></div>
</li>
<li class="mclas">
<div class="list-content"><a title="sql正则表达式excel" target="_blank" href="https://www.wanfa688.com/html/Products/512939.html">
<h3>sql正则表达式excel</h3>
</a><span class="post-date">2025-02-08</span></div>
</li>
<li class="mclas">
<div class="list-content"><a title="dataframe正则表达式" target="_blank" href="https://www.wanfa688.com/html/Products/512938.html">
<h3>dataframe正则表达式</h3>
</a><span class="post-date">2025-02-08</span></div>
</li>
<li class="mclas">
<div class="list-content"><a title="postgress sql正则" target="_blank" href="https://www.wanfa688.com/html/Products/512937.html">
<h3>postgress sql正则</h3>
</a><span class="post-date">2025-02-08</span></div>
</li>
<li class="mclas">
<div class="list-content"><a title="el-upload accept 正则表达式" target="_blank" href="https://www.wanfa688.com/html/Products/512936.html">
<h3>el-upload accept 正则表达式</h3>
</a><span class="post-date">2025-02-08</span></div>
</li>
<li class="mclas">
<div class="list-content"><a title="半小时 正则表达式" target="_blank" href="https://www.wanfa688.com/html/Products/512935.html">
<h3>半小时 正则表达式</h3>
</a><span class="post-date">2025-02-08</span></div>
</li>
<li class="mclas">
<div class="list-content"><a title="判断科学计数法的正则" target="_blank" href="https://www.wanfa688.com/html/Products/512934.html">
<h3>判断科学计数法的正则</h3>
</a><span class="post-date">2025-02-08</span></div>
</li>
<li class="mclas">
<div class="list-content"><a title="根据url判断静态资源的方法" target="_blank" href="https://www.wanfa688.com/html/Products/512929.html">
<h3>根据url判断静态资源的方法</h3>
</a><span class="post-date">2025-02-08</span></div>
</li>
<li class="mclas">
<div class="list-content"><a title="Java正则表达式-匹配正负浮点数" target="_blank" href="https://www.wanfa688.com/html/Products/512927.html">
<h3>Java正则表达式-匹配正负浮点数</h3>
</a><span class="post-date">2025-02-08</span></div>
</li>
<li class="mclas">
<div class="list-content"><a title="替换模糊匹配正则-hive" target="_blank" href="https://www.wanfa688.com/html/Products/512925.html">
<h3>替换模糊匹配正则-hive</h3>
</a><span class="post-date">2025-02-08</span></div>
</li>
</ul>
</div>
<div class="block-wrap" id="side-hot-view-item">
<h2 class="block-title">最新文章<i class="mcico mico-right"></i></h2>
<ul>
<li class="mclas">
<div class="list-content"><a title="一种基于正则表达式的DBC文件解析及报文分析方法[发明专利]" target="_blank" href="https://www.wanfa688.com/html/Products/512986.html">
<h3>一种基于正则表达式的DBC文件解析及报文分析方法[发明专利]</h3>
</a><span class="post-date">2025-02-08</span></div>
</li>
<li class="mclas">
<div class="list-content"><a title="能被5整除的十进制整数的正规表达式" target="_blank" href="https://www.wanfa688.com/html/Products/512981.html">
<h3>能被5整除的十进制整数的正规表达式</h3>
</a><span class="post-date">2025-02-08</span></div>
</li>
<li class="mclas">
<div class="list-content"><a title="大于0小于等于1的正则表达式" target="_blank" href="https://www.wanfa688.com/html/Products/512977.html">
<h3>大于0小于等于1的正则表达式</h3>
</a><span class="post-date">2025-02-08</span></div>
</li>
<li class="mclas">
<div class="list-content"><a title="linux grep 26个字母" target="_blank" href="https://www.wanfa688.com/html/Products/512974.html">
<h3>linux grep 26个字母</h3>
</a><span class="post-date">2025-02-08</span></div>
</li>
<li class="mclas">
<div class="list-content"><a title="java pattern 正则表达式" target="_blank" href="https://www.wanfa688.com/html/Products/512971.html">
<h3>java pattern 正则表达式</h3>
</a><span class="post-date">2025-02-08</span></div>
</li>
<li class="mclas">
<div class="list-content"><a title="掌握文本编辑器中的搜索和替换技巧" target="_blank" href="https://www.wanfa688.com/html/Products/512969.html">
<h3>掌握文本编辑器中的搜索和替换技巧</h3>
</a><span class="post-date">2025-02-08</span></div>
</li>
</ul>
</div>
<div class="block-wrap" id="divTags">
<h2 class="block-title">标签列表<i class="mcico mico-right"></i></h2>
<ul>
<li><a title="数据" href="https://www.wanfa688.com/tags/shuju/">数据<span class="tag-count"> (55294)</span></a></li>
<li><a title="使用" href="https://www.wanfa688.com/tags/shiyong/">使用<span class="tag-count"> (50857)</span></a></li>
<li><a title="函数" href="https://www.wanfa688.com/tags/hanshu/">函数<span class="tag-count"> (40060)</span></a></li>
<li><a title="进行" href="https://www.wanfa688.com/tags/jinxing/">进行<span class="tag-count"> (34371)</span></a></li>
<li><a title="需要" href="https://www.wanfa688.com/tags/xuyao/">需要<span class="tag-count"> (32994)</span></a></li>
<li><a title="系统" href="https://www.wanfa688.com/tags/xitong/">系统<span class="tag-count"> (24236)</span></a></li>
<li><a title="代码" href="https://www.wanfa688.com/tags/daima/">代码<span class="tag-count"> (24128)</span></a></li>
<li><a title="数据库" href="https://www.wanfa688.com/tags/shujuku/">数据库<span class="tag-count"> (21362)</span></a></li>
<li><a title="字符串" href="https://www.wanfa688.com/tags/zifuchuan/">字符串<span class="tag-count"> (21331)</span></a></li>
<li><a title="文件" href="https://www.wanfa688.com/tags/wenjian/">文件<span class="tag-count"> (20167)</span></a></li>
</ul>
</div>
</div>
<script src="https://www.wanfa688.com/skin/static/js/asid.js"></script></div>
<div class="footer"> Copyright © 2024 baidu.com <br class="footer-br" />
<a href="https://www.wanfa688.com/" target="_blank">Powered By 688IT编程网</a> <a href="https://beian.miit.gov.cn/" target="_blank" rel="nofollow">豫ICP备2022007602号</a>
<img style="display: inline;margin: 0 5px;width: 16px;" class="logos" src="/static/1.png" data-v-561690e4="">
<a href="https://beian.mps.gov.cn/#/query/webSearch?code=41160202000601" rel="noreferrer" target="_blank">豫公网安备41160202000602</a>
</div>
<script>
var _hmt = _hmt || [];
(function() {
var hm = document.createElement("script");
hm.src = "https://hm.baidu.com/hm.js?ddbe5ccc5716533aed8b61c946fd8030";
var s = document.getElementsByTagName("script")[0];
s.parentNode.insertBefore(hm, s);
})();
</script>
<script src="https://www.wanfa688.com/skin/static/js/custom.js"></script>
</body>
<script>
(function(){
var bp = document.createElement('script');
var curProtocol = window.location.protocol.split(':')[0];
if (curProtocol === 'https') {
bp.src = 'https://zz.bdstatic.com/linksubmit/push.js';
}
else {
bp.src = 'http://push.zhanzhang.baidu.com/push.js';
}
var s = document.getElementsByTagName("script")[0];
s.parentNode.insertBefore(bp, s);
})();
</script>
</html> 