Python使用xlwt给Excel进行居中对齐和自动换行

本文已参与「新人创作礼」活动，一起开启掘金创作之路。

任务背景：读取已有Excel表，往里面某些单元格填入内容，然后生成新的Excel文件

大致代码如下：

import xlwt
def set_style():
    style = xlwt.XFStyle()
    align = xlwt.Alignment()
    # 0x01(左端对齐)、0x02(水平方向上居中对齐)、0x03(右端对齐)
    align.horz = 0x02
    # 0x00(上端对齐)、 0x01(垂直方向上居中对齐)、0x02(底端对齐)
    align.vert = 0x01
    # 设置自动换行
    align.wrap = 1
    style.alignment = align

完整脚本内容如下：

#!/usr/bin/env python
from datetime import datetime
from pathlib import Path
from typing import List, Optional, Tuple, Union

import xlrd
import xlwt
from xlutils.copy import copy as xls_copy

from load_it import load, read_excel, remove_author
from settings import BASE_DIR, MEDIA_ROOT

SAMPLE = "salary_tips.xls"
DataType = Union[int, float, str, None]


def cell_style(is_top: bool = False, is_bottom: bool = False, has_border=True):
    """单元格样式"""
    style = xlwt.XFStyle()
    # 字体大小，11为字号，20为衡量单位
    # font = xlwt.Font()
    style.font.height = 20 * 9

    align = xlwt.Alignment()
    # 0x01(左端对齐)、0x02(水平方向上居中对齐)、0x03(右端对齐)
    align.horz = 0x02
    # 0x00(上端对齐)、 0x01(垂直方向上居中对齐)、0x02(底端对齐)
    align.vert = 0x01
    # 设置自动换行
    align.wrap = 1
    style.alignment = align

    # 设置边框
    # 细实线:1，小粗实线:2，细虚线:3，中细虚线:4，大粗实线:5，双线:6，细点虚线:7
    # 大粗虚线:8，细点划线:9，粗点划线:10，细双点划线:11，粗双点划线:12，斜点划线:13
    if has_border:
        borders = xlwt.Borders()
        borders.left = 2
        borders.right = 2
        borders.top = 1 + is_top
        borders.bottom = 1 + is_bottom
        style.borders = borders
    return style


def boom(tips: List[List[Tuple[int, int, DataType]]]) -> str:
    """将数据填入模板生成Excel表"""
    sample = BASE_DIR / SAMPLE
    xls = xls_copy(xlrd.open_workbook(sample, formatting_info=True))
    ws = xls.get_sheet(0)
    style = cell_style()
    top_style = cell_style(is_top=True)
    bottom_style = cell_style(is_bottom=True)
    plain_style = cell_style(has_border=False)
    last_index = 8
    for datas in tips:
        for i, d in enumerate(datas[:-1]):
            if i == 0:
                ws.write(*d, top_style)
            elif i == last_index:
                ws.write(*d, bottom_style)
            else:
                ws.write(*d, style)
        if _tc := datas[-1]:
            row, col, text = _tc
            if text:
                ws.write_merge(row, row, col - 1, col, text, plain_style)
    fname = MEDIA_ROOT / f"gzt_{datetime.now():%Y%m%d%H%M%S}.xls"
    try:
        xls.save(fname)
    except TypeError as e:
        print("May be you can look at this to fix it:")
        print("https://blog.csdn.net/zhangvalue/article/details/105170305")
        raise e
    return str(fname).replace(str(BASE_DIR), "")  # 返回相对路径


def build_tips(lines: List[List[DataType]]):
    row_delta = 10  # 每隔10行填下一排的数据
    col_delta = 3  # 每隔3列填下一组数据
    line_tip = 5  # 每行有5个工资条
    row_begin = 0  # 从第一行开始
    col_begin = 1  # 从第二列开始填数据（第一列是固定的表头）
    tips = []
    for tip_index, tip in enumerate(lines):
        first_row = row_begin + tip_index // line_tip * row_delta
        col_index = col_begin + tip_index % line_tip * col_delta
        d = [
            (row_index + first_row, col_index, value)
            for row_index, value in enumerate(tip)
        ]
        tips.append(d)
    return tips


def burn_life(content: bytes) -> str:
    return boom(build_tips(load(content)))


def main():
    import sys

    if not sys.argv[1:]:
        print("No args, do nothing.")
        return
    if (p := Path(sys.argv[1])).is_file():
        lines = load(p.read_bytes())
    else:
        day = f"{datetime.now():%Y.%m.%d}"
        ss = [
            "邹好好",
            160000,
            360,
            "休5天，请假7.5天 -40000",
            "迟到3次共16分钟",
            "扣社保-3073\n工龄+1000\n漏刷卡6次-300",
            987,
            "12月工资",
            day,
        ]
        lines = [ss, ss]
    print(boom(build_tips(lines)))


if __name__ == "__main__":
    main()