影刀RPA Excel单元格样式字体、颜色、边框作者林焱什么情况用什么生成的报表直接发出去被人嫌丑——标题没加粗、数字没对齐、表格没边框、条件不达标的数据没标红。这些问题在影刀RPA里用openpyxl的样式系统可以批量设置不需要手动打开Excel一个个调。适用场景自动化报表美化、批量标注异常数据红色底色、统一多个文件的表格样式、生成带边框和配色的专业报表。怎么做字体设置importopenpyxlfromopenpyxl.stylesimportFont,PatternFill,Border,Side,Alignment wbopenpyxl.load_workbook(rC:\Reports\report.xlsx)wswb.active# 标题行加粗、白色字、14号ws[A1].fontFont(name微软雅黑,size14,boldTrue,colorFFFFFF)# 表头行加粗、深蓝色forcolinrange(1,6):[video(video-1jshCu6o-1783501051030)(type-csdn)(url-https://live.csdn.net/v/embed/525000)(image-https://v-blog.csdnimg.cn/asset/23da3fe1f67a47106d725406cfde9a97/cover/Cover0.jpg)(title-拼多多店群自动化上架方案)]cellws.cell(row2,columncol)cell.fontFont(name微软雅黑,size11,boldTrue,color1F4E79)# 数据行常规、黑色forrowinrange(3,ws.max_row1):forcolinrange(1,6):ws.cell(rowrow,columncol).fontFont(name微软雅黑,size10,color000000)wb.save(rC:\Reports\report.xlsx)背景色填充fromopenpyxl.stylesimportPatternFill# 标题行深蓝底header_fillPatternFill(start_color1F4E79,end_color1F4E79,fill_typesolid)forcolinrange(1,6):ws.cell(row1,columncol).fillheader_fill# 隔行变色斑马线even_fillPatternFill(start_colorF2F2F2,end_colorF2F2F2,fill_typesolid)forrowinrange(3,ws.max_row1):ifrow%20:forcolinrange(1,6):ws.cell(rowrow,columncol).filleven_fill# 异常数据标红red_fillPatternFill(start_colorFFC7CE,end_colorFFC7CE,fill_typesolid)red_fontFont(name微软雅黑,size10,color9C0006)forrowinrange(3,ws.max_row1):scorews.cell(rowrow,column4).valueifscoreisnotNoneandscore60:forcolinrange(1,6):ws.cell(rowrow,columncol).fillred_fill ws.cell(rowrow,columncol).fontred_font边框设置fromopenpyxl.stylesimportBorder,Side# 细边框thin_borderBorder(leftSide(stylethin,color000000),rightSide(stylethin,color000000),topSide(stylethin,color000000),bottomSide(stylethin,color000000))# 给数据区域加边框forrowinrange(1,ws.max_row1):forcolinrange(1,6):ws.cell(rowrow,columncol).borderthin_border# 表头加粗边框thick_bottomBorder(leftSide(stylethin),rightSide(stylethin),topSide(stylethin),bottomSide(stylemedium,color1F4E79)# 底部加粗)forcolinrange(1,6):ws.cell(row2,columncol).borderthick_bottom对齐方式fromopenpyxl.stylesimportAlignment# 表头居中center_alignAlignment(horizontalcenter,verticalcenter,wrap_textTrue)forcolinrange(1,6):ws.cell(row2,columncol).alignmentcenter_align# 数字右对齐right_alignAlignment(horizontalright,verticalcenter)forrowinrange(3,ws.max_row1):forcolinrange(3,6):# 第3-5列是数字ws.cell(rowrow,columncol).alignmentright_align# 文本左对齐left_alignAlignment(horizontalleft,verticalcenter)forrowinrange(3,ws.max_row1):ws.cell(rowrow,column1).alignmentleft_align一键美化函数defbeautify_report(ws,title_row1,header_row2,data_start_row3,num_cols6):一键美化表格# 颜色方案title_fillPatternFill(start_color1F4E79,end_color1F4E79,fill_typesolid)header_fillPatternFill(start_colorD6E4F0,end_colorD6E4F0,fill_typesolid)even_fillPatternFill(start_colorF8F9FA,end_colorF8F9FA,fill_typesolid)thinSide(stylethin,colorB0B0B0)borderBorder(leftthin,rightthin,topthin,bottomthin)# 标题行forcolinrange(1,num_cols1):cellws.cell(rowtitle_row,columncol)cell.fontFont(name微软雅黑,size14,boldTrue,colorFFFFFF)cell.filltitle_fill cell.alignmentAlignment(horizontalcenter,verticalcenter)cell.borderborder# 表头行forcolinrange(1,num_cols1):cellws.cell(rowheader_row,columncol)cell.fontFont(name微软雅黑,size11,boldTrue,color1F4E79)cell.fillheader_fill cell.alignmentAlignment(horizontalcenter,verticalcenter,wrap_textTrue)cell.borderborder# 数据行forrowinrange(data_start_row,ws.max_row1):forcolinrange(1,num_cols1):cellws.cell(rowrow,columncol)cell.fontFont(name微软雅黑,size10)cell.borderborderifrow%20:cell.filleven_fillifcol3:# 数字列右对齐cell.alignmentAlignment(horizontalright,verticalcenter)else:cell.alignmentAlignment(horizontalleft,verticalcenter)# 行高ws.row_dimensions[title_row].height30ws.row_dimensions[header_row].height25# 列宽自适应简易版forcolinrange(1,num_cols1):max_length0forrowinrange(1,ws.max_row1):valws.cell(rowrow,columncol).valueifval:# 中文字符算2个宽度lengthsum(2iford(c)127else1forcinstr(val))iflengthmax_length:max_lengthlength ws.column_dimensions[openpyxl.utils.get_column_letter(col)].widthmin(max_length4,50)# 在影刀中调用beautify_report(ws,num_cols5)wb.save(rC:\Reports\report.xlsx)有什么坑坑1样式覆盖问题给单元格设了字体后再设背景色字体样式没了——因为每次赋值都是完整替换不是追加# 错误先设font再设fillfont还在cell.fontFont(boldTrue)cell.fillPatternFill(...)# fill不会影响font没问题# 但这种情况会丢样式直接拷贝单元格格式cell.styleTitle# 用style属性会重置所有样式cell.fontFont(boldTrue)# 之前用style设的都没了坑2颜色值格式openpyxl的颜色用hex字符串不带#号。常见错误# 错误带了#号cell.fontFont(color#FF0000)# 不报错但不生效# 正确不带#号cell.fontFont(colorFF0000)# 纯红色# 带透明度的ARGB格式cell.fontFont(colorFFFF0000)# FF不透明FF0000红色坑3边框只设了部分方向TEMU店群如何管理运营想只加底部边框结果四边都有了——因为Border默认值不是None# 错误只传bottom其他方向默认有值cell.borderBorder(bottomSide(stylethin))# 可能出现意外边框# 正确显式指定不需要的方向为Nonecell.borderBorder(leftNone,rightNone,topNone,bottomSide(stylethin,color000000))坑4大量单元格设样式导致文件巨大给10000行×20列每个单元格单独设Font、Fill、Border对象文件从100KB膨胀到5MB。原因是每个样式对象都是独立的。解决方案——用命名样式fromopenpyxl.stylesimportNamedStyle# 创建命名样式只存一份data_styleNamedStyle(namedata_style)data_style.fontFont(name微软雅黑,size10)data_style.borderBorder(leftSide(stylethin),rightSide(stylethin),topSide(stylethin),bottomSide(stylethin))# 注册wb.add_named_style(data_style)# 应用引用同一个样式对象forrowinrange(3,10003):forcolinrange(1,21):ws.cell(rowrow,columncol).styledata_style坑5列宽自适应不准中文字符宽度计算不准导致列宽不够文字被截断。改进方案importopenpyxl.utilsdefauto_width(ws,col_num):更准确的列宽自适应max_length0col_letteropenpyxl.utils.get_column_letter(col_num)forrowinrange(1,ws.max_row1):cellws.cell(rowrow,columncol_num)ifcell.value:# 区分中英文length0forcharinstr(cell.value):iford(char)127:# 中文或全角length2.1else:length1iflengthmax_length:max_lengthlength# 加padding但不超过50ws.column_dimensions[col_letter].widthmin(max_length3,50)
影刀RPA Excel单元格样式：字体、颜色、边框
影刀RPA Excel单元格样式字体、颜色、边框作者林焱什么情况用什么生成的报表直接发出去被人嫌丑——标题没加粗、数字没对齐、表格没边框、条件不达标的数据没标红。这些问题在影刀RPA里用openpyxl的样式系统可以批量设置不需要手动打开Excel一个个调。适用场景自动化报表美化、批量标注异常数据红色底色、统一多个文件的表格样式、生成带边框和配色的专业报表。怎么做字体设置importopenpyxlfromopenpyxl.stylesimportFont,PatternFill,Border,Side,Alignment wbopenpyxl.load_workbook(rC:\Reports\report.xlsx)wswb.active# 标题行加粗、白色字、14号ws[A1].fontFont(name微软雅黑,size14,boldTrue,colorFFFFFF)# 表头行加粗、深蓝色forcolinrange(1,6):[video(video-1jshCu6o-1783501051030)(type-csdn)(url-https://live.csdn.net/v/embed/525000)(image-https://v-blog.csdnimg.cn/asset/23da3fe1f67a47106d725406cfde9a97/cover/Cover0.jpg)(title-拼多多店群自动化上架方案)]cellws.cell(row2,columncol)cell.fontFont(name微软雅黑,size11,boldTrue,color1F4E79)# 数据行常规、黑色forrowinrange(3,ws.max_row1):forcolinrange(1,6):ws.cell(rowrow,columncol).fontFont(name微软雅黑,size10,color000000)wb.save(rC:\Reports\report.xlsx)背景色填充fromopenpyxl.stylesimportPatternFill# 标题行深蓝底header_fillPatternFill(start_color1F4E79,end_color1F4E79,fill_typesolid)forcolinrange(1,6):ws.cell(row1,columncol).fillheader_fill# 隔行变色斑马线even_fillPatternFill(start_colorF2F2F2,end_colorF2F2F2,fill_typesolid)forrowinrange(3,ws.max_row1):ifrow%20:forcolinrange(1,6):ws.cell(rowrow,columncol).filleven_fill# 异常数据标红red_fillPatternFill(start_colorFFC7CE,end_colorFFC7CE,fill_typesolid)red_fontFont(name微软雅黑,size10,color9C0006)forrowinrange(3,ws.max_row1):scorews.cell(rowrow,column4).valueifscoreisnotNoneandscore60:forcolinrange(1,6):ws.cell(rowrow,columncol).fillred_fill ws.cell(rowrow,columncol).fontred_font边框设置fromopenpyxl.stylesimportBorder,Side# 细边框thin_borderBorder(leftSide(stylethin,color000000),rightSide(stylethin,color000000),topSide(stylethin,color000000),bottomSide(stylethin,color000000))# 给数据区域加边框forrowinrange(1,ws.max_row1):forcolinrange(1,6):ws.cell(rowrow,columncol).borderthin_border# 表头加粗边框thick_bottomBorder(leftSide(stylethin),rightSide(stylethin),topSide(stylethin),bottomSide(stylemedium,color1F4E79)# 底部加粗)forcolinrange(1,6):ws.cell(row2,columncol).borderthick_bottom对齐方式fromopenpyxl.stylesimportAlignment# 表头居中center_alignAlignment(horizontalcenter,verticalcenter,wrap_textTrue)forcolinrange(1,6):ws.cell(row2,columncol).alignmentcenter_align# 数字右对齐right_alignAlignment(horizontalright,verticalcenter)forrowinrange(3,ws.max_row1):forcolinrange(3,6):# 第3-5列是数字ws.cell(rowrow,columncol).alignmentright_align# 文本左对齐left_alignAlignment(horizontalleft,verticalcenter)forrowinrange(3,ws.max_row1):ws.cell(rowrow,column1).alignmentleft_align一键美化函数defbeautify_report(ws,title_row1,header_row2,data_start_row3,num_cols6):一键美化表格# 颜色方案title_fillPatternFill(start_color1F4E79,end_color1F4E79,fill_typesolid)header_fillPatternFill(start_colorD6E4F0,end_colorD6E4F0,fill_typesolid)even_fillPatternFill(start_colorF8F9FA,end_colorF8F9FA,fill_typesolid)thinSide(stylethin,colorB0B0B0)borderBorder(leftthin,rightthin,topthin,bottomthin)# 标题行forcolinrange(1,num_cols1):cellws.cell(rowtitle_row,columncol)cell.fontFont(name微软雅黑,size14,boldTrue,colorFFFFFF)cell.filltitle_fill cell.alignmentAlignment(horizontalcenter,verticalcenter)cell.borderborder# 表头行forcolinrange(1,num_cols1):cellws.cell(rowheader_row,columncol)cell.fontFont(name微软雅黑,size11,boldTrue,color1F4E79)cell.fillheader_fill cell.alignmentAlignment(horizontalcenter,verticalcenter,wrap_textTrue)cell.borderborder# 数据行forrowinrange(data_start_row,ws.max_row1):forcolinrange(1,num_cols1):cellws.cell(rowrow,columncol)cell.fontFont(name微软雅黑,size10)cell.borderborderifrow%20:cell.filleven_fillifcol3:# 数字列右对齐cell.alignmentAlignment(horizontalright,verticalcenter)else:cell.alignmentAlignment(horizontalleft,verticalcenter)# 行高ws.row_dimensions[title_row].height30ws.row_dimensions[header_row].height25# 列宽自适应简易版forcolinrange(1,num_cols1):max_length0forrowinrange(1,ws.max_row1):valws.cell(rowrow,columncol).valueifval:# 中文字符算2个宽度lengthsum(2iford(c)127else1forcinstr(val))iflengthmax_length:max_lengthlength ws.column_dimensions[openpyxl.utils.get_column_letter(col)].widthmin(max_length4,50)# 在影刀中调用beautify_report(ws,num_cols5)wb.save(rC:\Reports\report.xlsx)有什么坑坑1样式覆盖问题给单元格设了字体后再设背景色字体样式没了——因为每次赋值都是完整替换不是追加# 错误先设font再设fillfont还在cell.fontFont(boldTrue)cell.fillPatternFill(...)# fill不会影响font没问题# 但这种情况会丢样式直接拷贝单元格格式cell.styleTitle# 用style属性会重置所有样式cell.fontFont(boldTrue)# 之前用style设的都没了坑2颜色值格式openpyxl的颜色用hex字符串不带#号。常见错误# 错误带了#号cell.fontFont(color#FF0000)# 不报错但不生效# 正确不带#号cell.fontFont(colorFF0000)# 纯红色# 带透明度的ARGB格式cell.fontFont(colorFFFF0000)# FF不透明FF0000红色坑3边框只设了部分方向TEMU店群如何管理运营想只加底部边框结果四边都有了——因为Border默认值不是None# 错误只传bottom其他方向默认有值cell.borderBorder(bottomSide(stylethin))# 可能出现意外边框# 正确显式指定不需要的方向为Nonecell.borderBorder(leftNone,rightNone,topNone,bottomSide(stylethin,color000000))坑4大量单元格设样式导致文件巨大给10000行×20列每个单元格单独设Font、Fill、Border对象文件从100KB膨胀到5MB。原因是每个样式对象都是独立的。解决方案——用命名样式fromopenpyxl.stylesimportNamedStyle# 创建命名样式只存一份data_styleNamedStyle(namedata_style)data_style.fontFont(name微软雅黑,size10)data_style.borderBorder(leftSide(stylethin),rightSide(stylethin),topSide(stylethin),bottomSide(stylethin))# 注册wb.add_named_style(data_style)# 应用引用同一个样式对象forrowinrange(3,10003):forcolinrange(1,21):ws.cell(rowrow,columncol).styledata_style坑5列宽自适应不准中文字符宽度计算不准导致列宽不够文字被截断。改进方案importopenpyxl.utilsdefauto_width(ws,col_num):更准确的列宽自适应max_length0col_letteropenpyxl.utils.get_column_letter(col_num)forrowinrange(1,ws.max_row1):cellws.cell(rowrow,columncol_num)ifcell.value:# 区分中英文length0forcharinstr(cell.value):iford(char)127:# 中文或全角length2.1else:length1iflengthmax_length:max_lengthlength# 加padding但不超过50ws.column_dimensions[col_letter].widthmin(max_length3,50)
