夜雨聆风一、样式概述
OpenPyXL提供了丰富的样式设置功能，包括字体、颜色、边框、对齐、数字格式等，让Excel文件更加专业美观。
from openpyxl import Workbookfrom openpyxl.styles import Font, PatternFill, Border, Side, Alignmentwb = Workbook()ws = wb.active# 基础样式示例cell = ws['A1']cell.value = '样式示例'cell.font = Font(bold=True, size=14)cell.fill = PatternFill(start_color='FFFF00', fill_type='solid')cell.alignment = Alignment(horizontal='center')wb.save('style_basic.xlsx')二、字体样式（Font）
2.1 基本字体设置
from openpyxl import Workbookfrom openpyxl.styles import Fontwb = Workbook()ws = wb.active# 字体样式列表font_styles = [ ('默认', Font()), ('粗体', Font(bold=True)), ('斜体', Font(italic=True)), ('粗斜体', Font(bold=True, italic=True)), ('下划线', Font(underline='single')), ('双下划线', Font(underline='double')), ('删除线', Font(strike=True)), ('红色', Font(color='FF0000')), ('蓝色', Font(color='0000FF')), ('大号', Font(size=20)), ('小号', Font(size=8)), ('宋体', Font(name='宋体')), ('微软雅黑', Font(name='微软雅黑')), ('Arial', Font(name='Arial')),]for idx, (text, font) inenumerate(font_styles, start=1): cell = ws[f'A{idx}'] cell.value = text cell.font = font# 设置列宽ws.column_dimensions['A'].width = 20wb.save('font_styles.xlsx')2.2 字体组合
from openpyxl import Workbookfrom openpyxl.styles import Fontwb = Workbook()ws = wb.active# 创建组合样式title_font = Font( name='微软雅黑', size=16, bold=True, color='FFFFFF')header_font = Font( name='微软雅黑', size=12, bold=True, color='333333')data_font = Font( name='宋体', size=10, color='000000')# 应用样式ws['A1'] = '标题'ws['A1'].font = title_fontws['A2'] = '表头'ws['A2'].font = header_fontws['A3'] = '数据'ws['A3'].font = data_fontwb.save('font_combinations.xlsx')三、填充样式（Fill）
3.1 纯色填充
from openpyxl import Workbookfrom openpyxl.styles import PatternFillwb = Workbook()ws = wb.active# 常用颜色colors = [ ('红色', 'FF0000'), ('蓝色', '0000FF'), ('绿色', '00FF00'), ('黄色', 'FFFF00'), ('橙色', 'FF9900'), ('紫色', '9900FF'), ('粉色', 'FF99CC'), ('灰色', 'CCCCCC'), ('深蓝', '003366'), ('深红', '990000'),]for idx, (name, color) inenumerate(colors, start=1): cell = ws[f'A{idx}'] cell.value = name cell.fill = PatternFill( start_color=color, end_color=color, fill_type='solid' ) cell.font = Font(color='FFFFFF'if idx <= 6else'000000')wb.save('color_fills.xlsx')3.2 填充类型
from openpyxl import Workbookfrom openpyxl.styles import PatternFillwb = Workbook()ws = wb.active# 填充类型fill_types = ['solid','darkGray','mediumGray','lightGray','gray125','gray0625',]for idx, fill_type inenumerate(fill_types, start=1): cell = ws[f'A{idx}'] cell.value = fill_type cell.fill = PatternFill( start_color='000000', end_color='000000', fill_type=fill_type )wb.save('fill_types.xlsx')3.3 渐变填充
from openpyxl import Workbookfrom openpyxl.styles import GradientFillwb = Workbook()ws = wb.active# 渐变填充gradients = [ ('红-黄', ['FF0000', 'FFFF00']), ('蓝-绿', ['0000FF', '00FF00']), ('红-蓝', ['FF0000', '0000FF']), ('黑-白', ['000000', 'FFFFFF']), ('彩虹', ['FF0000', 'FFFF00', '00FF00', '0000FF']),]for idx, (name, colors) inenumerate(gradients, start=1): cell = ws[f'A{idx}'] cell.value = name cell.fill = GradientFill( stop=colors,type='linear' ) cell.font = Font(color='FFFFFF'if idx <= 2else'000000')wb.save('gradient_fills.xlsx')四、边框样式（Border）
4.1 边框类型
from openpyxl import Workbookfrom openpyxl.styles import Border, Sidewb = Workbook()ws = wb.active# 边框样式border_styles = ['thin','medium','thick','dotted','dashed','double','dashDot','dashDotDot','slantDashDot',]for idx, style inenumerate(border_styles, start=1): cell = ws[f'B{idx}'] cell.value = style cell.border = Border( left=Side(style=style), right=Side(style=style), top=Side(style=style), bottom=Side(style=style) )# 设置列宽ws.column_dimensions['B'].width = 20wb.save('border_styles.xlsx')4.2 自定义边框
from openpyxl import Workbookfrom openpyxl.styles import Border, Sidewb = Workbook()ws = wb.active# 创建带颜色的边框color_border = Border( left=Side(style='thick', color='FF0000'), right=Side(style='thick', color='FF0000'), top=Side(style='double', color='0000FF'), bottom=Side(style='double', color='0000FF'))# 创建不对称边框mixed_border = Border( left=Side(style='thin'), right=Side(style='thick'), top=Side(style='dashed'), bottom=Side(style='dotted'))# 应用边框cell1 = ws['B2']cell1.value = '彩色边框'cell1.border = color_bordercell1.alignment = Alignment(horizontal='center', vertical='center')cell2 = ws['D2']cell2.value = '混合边框'cell2.border = mixed_bordercell2.alignment = Alignment(horizontal='center', vertical='center')# 设置行高列宽ws.row_dimensions[2].height = 30ws.column_dimensions['B'].width = 20ws.column_dimensions['D'].width = 20wb.save('custom_borders.xlsx')五、对齐方式（Alignment）
5.1 水平和垂直对齐
from openpyxl import Workbookfrom openpyxl.styles import Alignmentwb = Workbook()ws = wb.active# 设置列宽和行高for col in ['A', 'B', 'C', 'D', 'E']: ws.column_dimensions[col].width = 15for row inrange(1, 8): ws.row_dimensions[row].height = 30# 水平对齐horizontal = [ ('左对齐', 'left'), ('居中', 'center'), ('右对齐', 'right'), ('两端对齐', 'justify'), ('分散对齐', 'distributed'),]for idx, (text, align) inenumerate(horizontal, start=1): cell = ws[f'A{idx}'] cell.value = text cell.alignment = Alignment(horizontal=align)# 垂直对齐vertical = [ ('顶部', 'top'), ('居中', 'center'), ('底部', 'bottom'), ('两端对齐', 'justify'), ('分散对齐', 'distributed'),]for idx, (text, align) inenumerate(vertical, start=1): cell = ws[f'C{idx}'] cell.value = text cell.alignment = Alignment(vertical=align)# 组合对齐combinations = [ ('左上', 'left', 'top'), ('右上', 'right', 'top'), ('左下', 'left', 'bottom'), ('右下', 'right', 'bottom'), ('居中', 'center', 'center'),]for idx, (text, h_align, v_align) inenumerate(combinations, start=1): cell = ws[f'E{idx}'] cell.value = text cell.alignment = Alignment(horizontal=h_align, vertical=v_align)wb.save('alignment.xlsx')5.2 文本换行与缩进
from openpyxl import Workbookfrom openpyxl.styles import Alignmentwb = Workbook()ws = wb.active# 文本换行cell1 = ws['A1']cell1.value = '这是很长的一段文本，需要换行显示'cell1.alignment = Alignment(wrap_text=True)# 缩进cell2 = ws['A2']cell2.value = '缩进文本'cell2.alignment = Alignment(indent=4)# 文本方向cell3 = ws['A3']cell3.value = '旋转文本'cell3.alignment = Alignment(text_rotation=45)# 设置列宽ws.column_dimensions['A'].width = 30wb.save('text_align.xlsx')六、数字格式
6.1 数字格式设置
from openpyxl import Workbookfrom openpyxl.styles import numberswb = Workbook()ws = wb.active# 数字格式formats = [ ('通用', numbers.FORMAT_GENERAL), ('整数', '0'), ('千位分隔', '#,##0'), ('千位分隔+小数', '#,##0.00'), ('百分比', '0%'), ('百分比+小数', '0.00%'), ('科学计数', '0.00E+00'), ('货币', '"¥"#,##0.00'), ('美元', '"$"#,##0.00'), ('欧元', '"€"#,##0.00'), ('日期', numbers.FORMAT_DATE_YYYYMMDD2), ('时间', numbers.FORMAT_TIME_24), ('日期时间', 'yyyy-mm-dd hh:mm:ss'),]for idx, (name, fmt) inenumerate(formats, start=1): cell = ws[f'A{idx}'] cell.value = 12345.6789 cell.number_format = fmt# 单独显示格式名称for idx, (name, fmt) inenumerate(formats, start=1): cell = ws[f'B{idx}'] cell.value = name cell.font = Font(size=9)wb.save('number_formats.xlsx')6.2 自定义数字格式
from openpyxl import Workbookwb = Workbook()ws = wb.active# 自定义格式custom_formats = [ ('0.00', '12345.68'), ('#,##0.00', '12,345.68'), ('0.00%', '1234567.89%'), ('"¥"#,##0.00', '¥12,345.68'), ('0.00"元"', '12345.68元'), ('[红色]0.00', '12345.68'), ('[绿色]0.00', '12345.68'),]for idx, (fmt, display) inenumerate(custom_formats, start=1): cell = ws[f'A{idx}'] cell.value = 12345.6789 cell.number_format = fmt# 显示格式和显示值for idx, (fmt, display) inenumerate(custom_formats, start=1): cell = ws[f'B{idx}'] cell.value = fmt cell.font = Font(size=9)wb.save('custom_number_formats.xlsx')七、样式组合与模板
7.1 样式类封装
from openpyxl import Workbookfrom openpyxl.styles import Font, PatternFill, Border, Side, Alignmentfrom openpyxl.styles import numbersclassExcelStyles:"""Excel样式模板""" @staticmethoddefheader():"""表头样式"""return {'font': Font(bold=True, color='FFFFFF', size=12, name='微软雅黑'),'fill': PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid'),'alignment': Alignment(horizontal='center', vertical='center'),'border': Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin') ) } @staticmethoddeftitle():"""标题样式"""return {'font': Font(bold=True, size=16, name='微软雅黑'),'alignment': Alignment(horizontal='center', vertical='center'), } @staticmethoddefdata():"""数据样式"""return {'font': Font(size=10, name='宋体'),'alignment': Alignment(horizontal='left', vertical='center'),'border': Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin') ) } @staticmethoddefnumeric():"""数字样式"""return {'font': Font(size=10, name='宋体'),'alignment': Alignment(horizontal='right', vertical='center'),'border': Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin') ),'number_format': '#,##0.00' } @staticmethoddefhighlight():"""高亮样式"""return {'fill': PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid'),'font': Font(bold=True) } @staticmethoddefsuccess():"""成功样式"""return {'fill': PatternFill(start_color='90EE90', end_color='90EE90', fill_type='solid'),'font': Font(bold=True, color='006400') } @staticmethoddeferror():"""错误样式"""return {'fill': PatternFill(start_color='FF6B6B', end_color='FF6B6B', fill_type='solid'),'font': Font(bold=True, color='FFFFFF') } @staticmethoddefdate():"""日期样式"""return {'font': Font(size=10, name='宋体'),'alignment': Alignment(horizontal='center', vertical='center'),'border': Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin') ),'number_format': 'yyyy-mm-dd' }# 使用wb = Workbook()ws = wb.active# 应用样式ws['A1'] = '标题行'ExcelStyles.apply_style(ws['A1'], ExcelStyles.title())ws['A2'] = '表头'ExcelStyles.apply_style(ws['A2'], ExcelStyles.header())ws['A3'] = '数据'ExcelStyles.apply_style(ws['A3'], ExcelStyles.data())wb.save('style_template.xlsx')7.2 批量样式应用
from openpyxl import Workbookfrom openpyxl.styles import Font, PatternFill, Alignment, Border, Sidedefapply_style_to_range(ws, start_cell, end_cell, style_dict):"""批量应用样式到区域""" start_row = start_cell.row start_col = start_cell.column end_row = end_cell.row end_col = end_cell.columnfor row inrange(start_row, end_row + 1):for col inrange(start_col, end_col + 1): cell = ws.cell(row=row, column=col)for attr, value in style_dict.items():if attr == 'font': cell.font = valueelif attr == 'fill': cell.fill = valueelif attr == 'alignment': cell.alignment = valueelif attr == 'border': cell.border = valuedefcreate_table_style():"""创建表格样式"""return {'header': {'font': Font(bold=True, color='FFFFFF'),'fill': PatternFill(start_color='4472C4', fill_type='solid'),'alignment': Alignment(horizontal='center', vertical='center'),'border': Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin') ) },'data': {'font': Font(size=10),'alignment': Alignment(horizontal='left', vertical='center'),'border': Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin') ) },'numeric': {'font': Font(size=10),'alignment': Alignment(horizontal='right', vertical='center'),'border': Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin') ) } }# 创建示例表格wb = Workbook()ws = wb.active# 添加数据headers = ['姓名', '年龄', '工资', '部门']data = [ ['张三', 25, 8000, '技术部'], ['李四', 30, 12000, '销售部'], ['王五', 28, 9500, '技术部'], ['赵六', 35, 15000, '管理部']]ws.append(headers)for row in data: ws.append(row)# 应用样式styles = create_table_style()# 表头apply_style_to_range(ws, ws['A1'], ws['D1'], styles['header'])# 数据apply_style_to_range(ws, ws['A2'], ws['A5'], styles['data'])apply_style_to_range(ws, ws['B2'], ws['B5'], styles['numeric'])apply_style_to_range(ws, ws['C2'], ws['C5'], styles['numeric'])apply_style_to_range(ws, ws['D2'], ws['D5'], styles['data'])# 设置列宽ws.column_dimensions['A'].width = 15ws.column_dimensions['B'].width = 10ws.column_dimensions['C'].width = 15ws.column_dimensions['D'].width = 15wb.save('table_styles.xlsx')八、实战案例
8.1 销售报表样式
from openpyxl import Workbookfrom openpyxl.styles import Font, PatternFill, Alignment, Border, Sidefrom datetime import datetimeclassSalesReport:"""销售报表样式"""def__init__(self):self.wb = Workbook()self.ws = self.wb.activeself.ws.title = '销售报表'self._setup_styles()def_setup_styles(self):"""设置样式模板"""self.styles = {'title': {'font': Font(bold=True, size=16, name='微软雅黑'),'alignment': Alignment(horizontal='center', vertical='center') },'subtitle': {'font': Font(size=10, color='666666', italic=True),'alignment': Alignment(horizontal='center', vertical='center') },'header': {'font': Font(bold=True, color='FFFFFF', size=11, name='微软雅黑'),'fill': PatternFill(start_color='1E90FF', fill_type='solid'),'alignment': Alignment(horizontal='center', vertical='center'),'border': Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin') ) },'data': {'font': Font(size=10, name='宋体'),'alignment': Alignment(horizontal='left', vertical='center'),'border': Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin') ) },'numeric': {'font': Font(size=10, name='宋体'),'alignment': Alignment(horizontal='right', vertical='center'),'border': Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin') ) },'total': {'font': Font(bold=True, size=11, name='宋体'),'fill': PatternFill(start_color='E6F2FF', fill_type='solid'),'alignment': Alignment(horizontal='right', vertical='center'),'border': Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='double') ) },'highlight': {'fill': PatternFill(start_color='FFF3CD', fill_type='solid'),'font': Font(bold=True, color='856404') },'positive': {'font': Font(color='008000'),'fill': PatternFill(start_color='D4EDDA', fill_type='solid') },'negative': {'font': Font(color='CC0000'),'fill': PatternFill(start_color='F8D7DA', fill_type='solid') } }defapply_style(self, cell, style_name):"""应用样式"""if style_name inself.styles: style = self.styles[style_name]for attr, value in style.items():if attr == 'font': cell.font = valueelif attr == 'fill': cell.fill = valueelif attr == 'alignment': cell.alignment = valueelif attr == 'border': cell.border = valuedefcreate_report(self):"""创建报表"""# 标题self.ws.merge_cells('A1:F1') cell = self.ws['A1'] cell.value = '2024年度销售报表'self.apply_style(cell, 'title')# 副标题self.ws.merge_cells('A2:F2') cell = self.ws['A2'] cell.value = f'生成时间: {datetime.now().strftime("%Y-%m-%d %H:%M:%S")}'self.apply_style(cell, 'subtitle')# 表头 headers = ['月份', '产品A', '产品B', '产品C', '合计', '增长率']for col, header inenumerate(headers, start=1): cell = self.ws.cell(row=3, column=col) cell.value = headerself.apply_style(cell, 'header')# 数据 data = [ ['1月', 100, 120, 90, 310, '5%'], ['2月', 110, 130, 95, 335, '8%'], ['3月', 120, 140, 100, 360, '7%'], ['4月', 130, 150, 110, 390, '8%'], ['5月', 140, 160, 115, 415, '6%'], ['6月', 150, 170, 120, 440, '6%'], ]for row_idx, row_data inenumerate(data, start=4):for col_idx, value inenumerate(row_data, start=1): cell = self.ws.cell(row=row_idx, column=col_idx) cell.value = valueif col_idx in [2, 3, 4, 5]: # 数字列self.apply_style(cell, 'numeric')else:self.apply_style(cell, 'data')# 合计行 total_row = len(data) + 4 cell = self.ws.cell(row=total_row, column=1) cell.value = '合计'self.apply_style(cell, 'data')for col in [2, 3, 4, 5]: cell = self.ws.cell(row=total_row, column=col) cell.value = f'=SUM({chr(64+col)}4:{chr(64+col)}{total_row-1})'self.apply_style(cell, 'total')# 调整列宽self.ws.column_dimensions['A'].width = 12self.ws.column_dimensions['B'].width = 15self.ws.column_dimensions['C'].width = 15self.ws.column_dimensions['D'].width = 15self.ws.column_dimensions['E'].width = 15self.ws.column_dimensions['F'].width = 15defsave(self, filename):"""保存文件"""self.wb.save(filename)# 使用report = SalesReport()report.create_report()report.save('sales_report.xlsx')8.2 样式管理器
from openpyxl import load_workbookfrom openpyxl.styles import PatternFill, FontclassStyleManager:"""样式管理器"""def__init__(self, filepath):self.wb = load_workbook(filepath)self.ws = self.wb.activeself.style_cache = {}defget_or_create_style(self, style_name, **kwargs):"""获取或创建样式""" key = f"{style_name}_{str(kwargs)}"if key inself.style_cache:returnself.style_cache[key]# 创建样式if style_name == 'font': style = Font(**kwargs)elif style_name == 'fill': style = PatternFill(**kwargs)else: style = Noneif style:self.style_cache[key] = stylereturn styledefapply_cell_style(self, coordinate, style_name, **kwargs):"""应用样式到单元格""" style = self.get_or_create_style(style_name, **kwargs)if style: cell = self.ws[coordinate]if style_name == 'font': cell.font = styleelif style_name == 'fill': cell.fill = styledefapply_range_style(self, start_cell, end_cell, style_name, **kwargs):"""应用样式到区域""" style = self.get_or_create_style(style_name, **kwargs)ifnot style:return start_row = start_cell.row start_col = start_cell.column end_row = end_cell.row end_col = end_cell.columnfor row inrange(start_row, end_row + 1):for col inrange(start_col, end_col + 1): cell = self.ws.cell(row=row, column=col)if style_name == 'font': cell.font = styleelif style_name == 'fill': cell.fill = styledefcopy_style(self, source, target):"""复制样式""" source_cell = self.ws[source] target_cell = self.ws[target] target_cell.font = source_cell.font target_cell.fill = source_cell.fill target_cell.border = source_cell.border target_cell.alignment = source_cell.alignmentdefclear_style(self, coordinate):"""清除样式""" cell = self.ws[coordinate] cell.font = Font() cell.fill = PatternFill() cell.border = Border() cell.alignment = Alignment()defsave(self, filepath):"""保存文件"""self.wb.save(filepath)# 使用manager = StyleManager('data.xlsx')manager.apply_cell_style('A1', 'font', bold=True, size=14)manager.apply_range_style('A1', 'D1', 'fill', start_color='FFFF00', fill_type='solid')manager.save('styled_data.xlsx')九、总结
# 快速参考# 1. 字体样式cell.font = Font(name='宋体', size=12, bold=True, italic=True, color='FF0000')# 2. 填充样式cell.fill = PatternFill(start_color='FFFF00', fill_type='solid')cell.fill = GradientFill(stop=('FFFFFF', '0000FF'))# 3. 边框样式cell.border = Border( left=Side(style='thin', color='000000'), right=Side(style='thin', color='000000'), top=Side(style='thin', color='000000'), bottom=Side(style='thin', color='000000'))# 4. 对齐样式cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)# 5. 数字格式cell.number_format = '#,##0.00'cell.number_format = 'yyyy-mm-dd'# 6. 合并单元格ws.merge_cells('A1:D1')# 7. 列宽行高ws.column_dimensions['A'].width = 20ws.row_dimensions[1].height = 30OpenPyXL提供了完整的样式设置功能，通过组合字体、填充、边框、对齐等元素，可以创建专业美观的Excel文件。合理使用样式模板和批量应用可以提高开发效率，保持报表风格的一致性。