[Python知识库]excel表格设计模块openpyxl使用案例实战

代码示例：

# !usr/bin/env python
# -*- coding:utf-8 -*-
'''
function：设计excel表格
author:AI算法联盟
date:20220401
'''
''' 若未安装openpyxl模块，先执行pip install openpyxl进行安装'''

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Border, Side, Font, Alignment
from openpyxl.styles import PatternFill, Color

def get_data():
    data = [["高一", 529, 797, 1326],
            ["高二", 502, 815, 1317],
            ["高三", 671, 749, 1420],
            ["总计", 1702, 2361, 4063]]
    data_df = pd.DataFrame(data, columns=["年级", "男生", "女生", "总人数"])
    return data_df

def excel_design(data_df):
    columns_num = len(data_df.columns)
    wb = Workbook()
    ws = wb[wb.sheetnames[0]]   # 选择第一张表
    ws.title = "demo" # 工作表重命名
    # 单元格背景颜色设计
    color_head = PatternFill("solid", fgColor="B1A0C7")
    color_title = PatternFill("solid", fgColor="EBF1DE")
    # 字体设计
    font_head = Font(size=16, bold=True, color="000000")
    font_title = Font(size=14, bold=True, color="000000")
    # 单元格边框设计
    border = Border(top=Side(border_style="thin", color="000000"),
                    bottom=Side(border_style="thin", color="000000"),
                    left=Side(border_style="thin", color="000000"),
                    right=Side(border_style="thin", color="000000"))
    # 居中对齐，允许换行
    align = Alignment(horizontal="center", vertical="center", wrap_text=True)
    # 头部单元格合并
    ws.merge_cells(start_row=1, end_row=1, start_column=1, end_column=columns_num)
    ws.cell(row=1, column=1, value="深圳市某高级中学学生人数统计").fill = color_head
    ws.cell(row=1, column=1).font = font_head  # 头部字体设置
    ws.cell(row=1, column=1).border = border   # 头部边框设置
    ws.cell(row=1, column=1).alignment = align # 头部对齐设置

    ws.row_dimensions[1].height = 36  # 头部行高设置36
    ws.row_dimensions[2].height = 24  # 标题行高设置24
    letters = ["A", "B", "C", "D", "E", "F"]
    for c in range(columns_num):
        ws.column_dimensions[letters[c]].width = 16  # 每列宽度设置16
        # 标题数据填充
        value = data_df.columns[c]
        # 列从1开始，故需加1
        ws.cell(row=2, column=c+1, value=value).fill = color_title  # 填充数据并设置背景颜色
        ws.cell(row=2, column=c+1).font = font_title                # 字体大小设置
        ws.cell(row=2, column=c+1).border = border                  # 添加边框
        ws.cell(row=2, column=c+1).alignment = align                # 对齐

    start_row = 3   # 从第3行开始填充数据
    for i in range(len(data_df)):
        start_col = 1  # 每次都是从第1列开始填充数据
        ws.row_dimensions[start_row].height = 24 # 行高设置为24
        for j in range(columns_num):
            value = data_df.iloc[i, j]
            ws.cell(row=start_row, column=start_col, value=value)  # 填充数据
            ws.cell(row=start_row, column=start_col).border = border # 添加边框
            ws.cell(row=start_row, column=start_col).alignment = align # 对齐
            start_col += 1
        start_row += 1

    # 保存excel表格
    wb.save("demo.xlsx")

if __name__ == "__main__":
    data = get_data()
    excel_design(data)

运行效果：

?

?