python 做成Excel并设置打印区域

记录首次用python处理Excel表格的过程。

参考文章：https://www.jianshu.com/p/5e00dc2c9f4c

程序要做的事情：

1. copy 模板文件到 output 文件夹并重命名为客户指定的文件名
2. 从 DB 查询数据并将数据写入 Excel
3. 写数据的同时， 设置每个单元格的样式
4. 设置打印区域

# -*- encoding: utf-8 -*-


import utils.util as util
from database import sns_db
from logger import init_log, sns_logger
import time
from config.config import log_level_args, env_args
import sys
import os
import shutil
from datetime import datetime
from openpyxl import load_workbook
from openpyxl.styles import Font, Color, PatternFill, Border, Side, Alignment
import win32com.client

sys.path.append('../')



def run(client_cds, start_date, end_date):
    """

    :param client_cds:
    :param start_date:
    :param end_date:
    :return:
    """

    try:
        # connect db
        sns_db.connect(client_cds)

        # get client info
        client_info = sns_db.get_clients_by_cds(in_clients)
        if len(client_info) < 1:
            sns_logger.error("No such client [%s] !!!" % in_clients)
            return

        client_name = client_info[0]['client_name']

        report_month = start_date[:7].replace("-", "")

        records = sns_db.get_report_data(client_cds, start_date, end_date)

        # 模板文件的路径
        template_to_client_path     = os.getcwd() + '/report/template/template-to-client.xlsx'
        template_chartgtp_path      = os.getcwd() + '/report/template/template_chartGTP.xlsm'

        # 获取当前日期并格式化为字符串（例如：'2023-10-23'）
        today = datetime.now().strftime('%Y-%m-%d')

        # 新文件的路径（使用当前日期作为文件名的一部分）
        new_file_to_client_path     = os.getcwd() + f'/report/output/【{client_name}】月次レポート{report_month}.xlsx'
        new_file_chartgtp_path      = os.getcwd() + f'/report/output/【{client_name}】月次レポート{report_month}_chatGTP.xlsx'

        # 复制模板文件
        shutil.copy2(template_to_client_path, new_file_to_client_path)
        shutil.copy2(template_chartgtp_path, new_file_chartgtp_path)

        # 现在我们可以打开新文件并处理数据
        # to_client file
        workbook_to_client  = load_workbook(new_file_to_client_path)
        sheet_to_client     = workbook_to_client['sheet1']
        # chatGPT file
        workbook_chatgpt    = load_workbook(new_file_chartgtp_path)
        sheet_chatgpt       = workbook_chatgpt['sheet1']

        # 设置边框样式，这里使用蓝色边框
        thin_border = Border(left=Side(border_style='thin', color='0070C0'),
                             right=Side(border_style='thin', color='0070C0'),
                             top=Side(border_style='thin', color='0070C0'),
                             bottom=Side(border_style='thin', color='0070C0'))

        # 对齐方式： 水平居中 垂直居中
        alignment_center = Alignment(horizontal='center', vertical='center')

        # 对齐方式： 垂直居中
        vertical_center = Alignment(vertical='center')

        # 自动换行
        wrap_text_true = Alignment(wrap_text=True)

        # Font
        font_style = Font(name='Yu Gothic UI', size=11)

        row_cnt = len(records)
        # 插入数据
        for i in range(row_cnt):
            current_row = records[i]
            row_idx = i + 3

            # A列 NO
            column_a = 'A' + str(row_idx)
            sheet_to_client[column_a] = '=ROW()-2'
            sheet_chatgpt[column_a] = '=ROW()-2'
            sheet_to_client[column_a].border = thin_border  # 设置边框
            sheet_chatgpt[