【python】【openpyxl或其他】如何更高效的设置excel单元格格式

Question

想要设置一个excel格式，需求如下：
1、全体表格。字体=微软雅黑，右对齐，所有框线
2、标题行。上述基础之上，加粗+底色

 

现行代码是用【openpyxl】实现，个人理解是逐个单元格应用已经定义好的格式。直观的影响是：小数据量的excel执行速度还行，几万行时代码执行速度就非常非常慢

 

请问：现行代码应当怎样优化，可以让执行速度更快。
说明：不限定使用【openpyxl】。只要能实现目标，用python的其他库也是可以的

 

import openpyxl
import pandas as pd
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side, colors
from openpyxl.utils import get_column_letter


def excel_format(ws):  # 设置表格通用样式
    font = Font(name="微软雅黑", bold=False, italic=False, size=10)  # 全局字体
    font_head = Font(name="微软雅黑", bold=True, italic=False, size=10)  # 标题行字体
    alignment = Alignment(horizontal="right", vertical="center", wrap_text=False)  # 对齐
    border = Border(
        left=Side(border_style="thin", color=colors.BLACK),
        right=Side(border_style="thin", color=colors.BLACK),
        top=Side(border_style="thin", color=colors.BLACK),
        bottom=Side(border_style="thin", color=colors.BLACK),
    )  # 框线
    fill_head = PatternFill(
        fill_type=None, patternType="solid", start_color="33CCFF"
    )  # 标题行底色

    for column in ws.columns:  # 应用于所有
        for cell_1 in column:
            cell_1.alignment = alignment
            cell_1.font = font
            cell_1.border = border

    for cell in list(ws.rows)[0]:  # 应用于标题
        cell.font = font_head
        cell.fill = fill_head

    max_row = ws.max_row
    max_column = ws.max_column
    for i in range(1, max_row + 1):  # 设置全部行高
        ws.row_dimensions[i].height = 20
    for i in range(1, max_column + 1):  # 设置全部列宽
        ws.column_dimensions[get_column_letter(i)].width = 8
    return ws


dict = {
    "查询日期": {0: "11日", 1: "12日", 2: "合计"},
    "(0,1)": {0: 2877, 1: 4951, 2: 7828},
    "[1,2)": {0: 270, 1: 464, 2: 734},
    "[2,3)": {0: 131, 1: 290, 2: 421},
    "[3,4)": {0: 108, 1: 142, 2: 250},
    "[4,5)": {0: 82, 1: 127, 2: 209},
    "[5,8)": {0: 142, 1: 212, 2: 354},
    "[8,10)": {0: 32, 1: 34, 2: 66},
    "All": {0: 3702, 1: 6298, 2: 10000},
}

# 生成excel测试数据
path = r"C:\Users\Gavin\Desktop\test.xlsx"
df = pd.DataFrame(dict)
df.to_excel(path)

# 设置excel格式。这部分是请帮助优化的代码，也即请帮助优化【def excel_format(ws):】
wb = openpyxl.load_workbook(path)
ws1 = wb["Sheet1"]
w1 = excel_format(ws1)
wb.save(path)

Answer 1

https://blog.csdn.net/u013400654/article/details/50284983