Excel处理——OpenPyXL样式设置

一、样式概述

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 = 30

OpenPyXL提供了完整的样式设置功能，通过组合字体、填充、边框、对齐等元素，可以创建专业美观的Excel文件。合理使用样式模板和批量应用可以提高开发效率，保持报表风格的一致性。