如何使用OpenPyXL创建带样式的Excel表格？

以下是基于openpyxl实现带自定义边框、合并单元格和单元格着色的Excel表格的Python脚本，可完成工作簿初始化、工作表添加，并应用多种样式效果。

完整实现代码 #

from openpyxl import Workbook
from openpyxl.styles import PatternFill, Border, Side, Alignment, Font, fills

# 定义不同类型的边框样式
THIN_BORDER = Border(left=Side(border_style='thin',color='FF000000'),
                    right=Side(border_style='thin',color='FF000000'),
                    top=Side(border_style='thin',color='FF000000'),
                    bottom=Side(border_style='thin',color='FF000000')
                    )
                    
THICK_BORDER = Border(left=Side(border_style='thin',color='FF000000'),
                    right=Side(border_style='thin',color='FF000000'),
                    top=Side(border_style='thin',color='FF000000'),
                    bottom=Side(border_style='medium',color='FF000000')
                    )
DOUBLE_BORDER = Border(left=Side(border_style='dashed',color='FF000000'),
                    right=Side(border_style='dashed',color='FF000000'),
                    top=Side(border_style='double',color='FF000000'),
                    bottom=Side(border_style='double',color='FF000000')
                    )
MEDIUM_BORDER = Border(left=Side(border_style='medium',color='FF000000'),
                    right=Side(border_style='medium',color='FF000000'),
                    top=Side(border_style='medium',color='FF000000'),
                    bottom=Side(border_style='medium',color='FF000000')
                    ) 

def create_styled_table():
        
    wb=Workbook()
    ws1=wb.active
    ws1.title = "Sheet1"
    ws1.sheet_properties.tabColor = '000000FF'
    
    # 定义单元格填充样式
    fill_cell = PatternFill(fill_type=fills.FILL_SOLID,start_color='00FFFF00',end_color='00FFFF00')
    # 定义表格尺寸：行数、列数
    row_num=9
    col_num=8 
    # 表格起始位置
    start_row, row_loc = 10, 10
    start_column, col_loc = 2, 2
    # 要创建的表格数量
    Table_num=1
    dis=2 # 多个表格之间的间距
    for _ in range(Table_num):
        for i in range (row_loc,row_loc+row_num):
            for j in range (col_loc,col_num+col_loc):
                # 默认应用细边框
                ws1.cell(row=i, column=j).border=THIN_BORDER
                # 表格首行应用双边框+黄色填充
                if i==row_loc:
                    ws1.cell(row=i, column=j).border=DOUBLE_BORDER
                    ws1.cell(row=i, column=j).fill=fill_cell
                # 表格末行应用底部中等边框
                if i==row_loc+row_num-1:
                    ws1.cell(row=i, column=j).border=THICK_BORDER
        # 更新下一个表格的起始行位置
        row_loc= row_loc+row_num+dis
        
    # 合并首行指定列范围的单元格，并应用中等边框
    ws1.merge_cells(start_row=start_row, start_column=start_column+2, end_row=start_row, end_column=start_column+col_num-1)
    ws1.cell(row=start_row, column=start_column+col_num-1).border=MEDIUM_BORDER
    
    # 合并首列的前两行单元格
    ws1.merge_cells(start_row=start_row, start_column=start_column, end_row=start_row+1, end_column=start_column)
    # 合并首列从第三行到表格末行的单元格
    ws1.merge_cells(start_row=start_row+2, start_column=start_column, end_row=start_row+row_num-1, end_column=start_column)
            
    wb.save('table.xlsx')

关键功能解析 #

• 边框样式定义：提前定义4种边框样式（细边框、底部加粗边框、上下双边框+左右虚线边框、全中等边框），便于后续批量调用。
• 单元格填充：通过PatternFill创建黄色纯色填充样式，用于表格首行的高亮区分。
• 表格布局控制：用变量定义表格的尺寸、起始位置、数量及间距，支持批量创建多个独立表格。
• 合并单元格：对首行和首列的特定区域进行单元格合并，模拟复杂表头结构。
• 样式批量应用：通过嵌套循环遍历表格单元格，根据行位置自动匹配对应样式，提升代码复用效率。

内容的提问来源于stack exchange，提问作者Yuval