记录首次用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[column_a].border = thin_border  # 设置边框
            sheet_to_client[column_a].alignment  = alignment_center  # 对齐方式: 水平居中 垂直居中
            sheet_chatgpt[column_a].alignment  = alignment_center  # 对齐方式: 水平居中 垂直居中
            sheet_to_client[column_a].font = font_style   # Font
            sheet_chatgpt[column_a].font = font_style   # Font


            # B列 対象日
            column_b = 'B' + str(row_idx)
            obj_date = str(current_row['date']).replace("-", "/")
            sheet_to_client[column_b] = obj_date
            sheet_chatgpt[column_b] = obj_date
            sheet_to_client[column_b].border = thin_border  # 设置边框
            sheet_chatgpt[column_b].border = thin_border  # 设置边框
            sheet_to_client[column_b].alignment  = alignment_center  # 对齐方式: 水平居中 垂直居中
            sheet_chatgpt[column_b].alignment  = alignment_center  # 对齐方式: 水平居中 垂直居中
            sheet_to_client[column_b].font = font_style   # Font
            sheet_chatgpt[column_b].font = font_style   # Font


            # C列 投稿時刻
            column_c = 'C' + str(row_idx)
            obj_time = current_row['time']
            sheet_to_client[column_c] = obj_time
            sheet_chatgpt[column_c] = obj_time
            sheet_to_client[column_c].border = thin_border  # 设置边框
            sheet_chatgpt[column_c].border = thin_border  # 设置边框
            sheet_to_client[column_c].alignment  = alignment_center  # 对齐方式: 水平居中 垂直居中
            sheet_chatgpt[column_c].alignment  = alignment_center  # 对齐方式: 水平居中 垂直居中
            sheet_to_client[column_c].font = font_style   # Font
            sheet_chatgpt[column_c].font = font_style   # Font


            # D列 URL
            column_d = 'D' + str(row_idx)
            url = current_row['url']
            sheet_to_client[column_d] = url
            sheet_chatgpt[column_d] = url
            sheet_to_client[column_d].border = thin_border  # 设置边框
            sheet_chatgpt[column_d].border = thin_border  # 设置边框
            sheet_to_client[column_d].alignment  = vertical_center  # 垂直居中
            sheet_chatgpt[column_d].alignment  = vertical_center  # 垂直居中
            sheet_to_client[column_d].font = font_style   # Font
            sheet_chatgpt[column_d].font = font_style   # Font


            # E列 タイトル
            column_e = 'E' + str(row_idx)
            if current_row['category'] != "yelp":
                # yelp no title
                title = current_row['title']
                sheet_to_client[column_e]   = title
                sheet_chatgpt[column_e]     = title
            sheet_to_client[column_e].border = thin_border  # 设置边框
            sheet_chatgpt[column_e].border = thin_border  # 设置边框
            sheet_to_client[column_e].alignment  = vertical_center  # 垂直居中
            sheet_chatgpt[column_e].alignment  = vertical_center  # 垂直居中
            sheet_to_client[column_e].font = font_style   # Font
            sheet_chatgpt[column_e].font = font_style   # Font


            # F列 サイトカテゴリ
            column_f = 'F' + str(row_idx)
            category = current_row['category'] + "検索結果"
            sheet_to_client[column_f]   = category
            sheet_chatgpt[column_f]     = category
            sheet_to_client[column_f].border = thin_border  # 设置边框
            sheet_chatgpt[column_f].border = thin_border  # 设置边框
            sheet_to_client[column_f].alignment  = alignment_center  # 对齐方式: 水平居中 垂直居中
            sheet_chatgpt[column_f].alignment  = alignment_center  # 对齐方式: 水平居中 垂直居中
            sheet_to_client[column_f].font = font_style   # Font
            sheet_chatgpt[column_f].font = font_style   # Font


            # G列 ユーザー名
            column_g = 'G' + str(row_idx)
            user_name = current_row['user_name']
            sheet_to_client[column_g]   = user_name
            sheet_chatgpt[column_g]     = user_name
            sheet_to_client[column_g].border = thin_border  # 设置边框
            sheet_chatgpt[column_g].border = thin_border  # 设置边框
            sheet_to_client[column_g].alignment  = alignment_center  # 对齐方式: 水平居中 垂直居中
            sheet_chatgpt[column_g].alignment  = alignment_center  # 对齐方式: 水平居中 垂直居中
            sheet_to_client[column_g].font = font_style   # Font
            sheet_chatgpt[column_g].font = font_style   # Font


            # H列 抜粋文
            column_h = 'H' + str(row_idx)
            content = current_row['content']
            sheet_to_client[column_h]   = content
            sheet_chatgpt[column_h]     = content
            sheet_to_client[column_h].border = thin_border  # 设置边框
            sheet_chatgpt[column_h].border = thin_border  # 设置边框
            sheet_to_client[column_h].alignment  = vertical_center  # 垂直居中
            sheet_chatgpt[column_h].alignment  = vertical_center  # 垂直居中
            sheet_to_client[column_h].alignment  = wrap_text_true  # 自动换行
            sheet_chatgpt[column_h].alignment  = wrap_text_true  # 自动换行
            sheet_to_client[column_h].font = font_style   # Font
            sheet_chatgpt[column_h].font = font_style   # Font


            # I列 自動翻訳
            column_i = 'I' + str(row_idx)
            sheet_to_client[column_i]   = ""
            # 判断语言, 非日语的才翻译
            if current_row['lang'] == 'Japanese':
                sheet_chatgpt[column_i] = content
            else:
                sheet_chatgpt[column_i] = f'=ChatGPT("将评论翻译成日语 " & H{row_idx})'
            sheet_to_client[column_i].border = thin_border  # 设置边框
            sheet_chatgpt[column_i].border = thin_border  # 设置边框
            sheet_to_client[column_i].alignment  = vertical_center  # 垂直居中
            sheet_chatgpt[column_i].alignment  = vertical_center  # 垂直居中
            sheet_to_client[column_i].alignment  = wrap_text_true  # 自动换行
            sheet_chatgpt[column_i].alignment  = wrap_text_true  # 自动换行
            sheet_to_client[column_i].font = font_style   # Font
            sheet_chatgpt[column_i].font = font_style   # Font


            # J列 レベル
            column_j = 'J' + str(row_idx)
            sheet_to_client[column_j]   = f'= IF(COUNTIF(U{row_idx}, "*positive*")>0, "ポジティブ", "ネガティブ")'
            sheet_chatgpt[column_j]     = f'= IF(COUNTIF(U{row_idx}, "*positive*")>0, "ポジティブ", "ネガティブ")'
            sheet_to_client[column_j].border = thin_border  # 设置边框
            sheet_chatgpt[column_j].border = thin_border  # 设置边框
            sheet_to_client[column_j].alignment  = alignment_center  # 对齐方式: 水平居中 垂直居中
            sheet_chatgpt[column_j].alignment  = alignment_center  # 对齐方式: 水平居中 垂直居中
            sheet_to_client[column_j].font = font_style   # Font
            sheet_chatgpt[column_j].font = font_style   # Font


            # K列 商品関連
            column_k = 'K' + str(row_idx)
            sheet_to_client[column_k]   = f'= IF(COUNTIF(U{row_idx}, "*商品関連*")>0, "●", "")'
            sheet_chatgpt[column_k]     = f'= IF(COUNTIF(U{row_idx}, "*商品関連*")>0, "●", "")'
            sheet_to_client[column_k].border = thin_border  # 设置边框
            sheet_chatgpt[column_k].border = thin_border  # 设置边框
            sheet_to_client[column_k].alignment  = alignment_center  # 对齐方式: 水平居中 垂直居中
            sheet_chatgpt[column_k].alignment  = alignment_center  # 对齐方式: 水平居中 垂直居中


            # L列 接客関連
            column_l = 'L' + str(row_idx)
            sheet_to_client[column_l]   = f'= IF(COUNTIF(U{row_idx}, "*接客関連*")>0, "●", "")'
            sheet_chatgpt[column_l]     = f'= IF(COUNTIF(U{row_idx}, "*接客関連*")>0, "●", "")'
            sheet_to_client[column_l].border = thin_border  # 设置边框
            sheet_chatgpt[column_l].border = thin_border  # 设置边框
            sheet_to_client[column_l].alignment  = alignment_center  # 对齐方式: 水平居中 垂直居中
            sheet_chatgpt[column_l].alignment  = alignment_center  # 对齐方式: 水平居中 垂直居中


            # M列 店舗関連
            column_m = 'M' + str(row_idx)
            sheet_to_client[column_m]   = f'= IF(COUNTIF(U{row_idx}, "*店舗関連*")>0, "●", "")'
            sheet_chatgpt[column_m]     = f'= IF(COUNTIF(U{row_idx}, "*店舗関連*")>0, "●", "")'
            sheet_to_client[column_m].border = thin_border  # 设置边框
            sheet_chatgpt[column_m].border = thin_border  # 设置边框
            sheet_to_client[column_m].alignment  = alignment_center  # 对齐方式: 水平居中 垂直居中
            sheet_chatgpt[column_m].alignment  = alignment_center  # 对齐方式: 水平居中 垂直居中


            # N列 在庫関連
            column_n = 'N' + str(row_idx)
            sheet_to_client[column_n]   = f'= IF(COUNTIF(U{row_idx}, "*在庫関連*")>0, "●", "")'
            sheet_chatgpt[column_n]     = f'= IF(COUNTIF(U{row_idx}, "*在庫関連*")>0, "●", "")'
            sheet_to_client[column_n].border = thin_border  # 设置边框
            sheet_chatgpt[column_n].border = thin_border  # 设置边框
            sheet_to_client[column_n].alignment  = alignment_center  # 对齐方式: 水平居中 垂直居中
            sheet_chatgpt[column_n].alignment  = alignment_center  # 对齐方式: 水平居中 垂直居中


            # O列 在庫関連
            column_o = 'O' + str(row_idx)
            sheet_to_client[column_o]   = f'= IF(COUNTIF(U{row_idx}, "*労務関連*")>0, "●", "")'
            sheet_chatgpt[column_o]     = f'= IF(COUNTIF(U{row_idx}, "*労務関連*")>0, "●", "")'
            sheet_to_client[column_o].border = thin_border  # 设置边框
            sheet_chatgpt[column_o].border = thin_border  # 设置边框
            sheet_to_client[column_o].alignment  = alignment_center  # 对齐方式: 水平居中 垂直居中
            sheet_chatgpt[column_o].alignment  = alignment_center  # 对齐方式: 水平居中 垂直居中


            # P列 トラブル関連
            column_p = 'P' + str(row_idx)
            sheet_to_client[column_p]   = f'= IF(COUNTIF(U{row_idx}, "*トラブル関連*")>0, "●", "")'
            sheet_chatgpt[column_p]     = f'= IF(COUNTIF(U{row_idx}, "*トラブル関連*")>0, "●", "")'
            sheet_to_client[column_p].border = thin_border  # 设置边框
            sheet_chatgpt[column_p].border = thin_border  # 设置边框
            sheet_to_client[column_p].alignment  = alignment_center  # 对齐方式: 水平居中 垂直居中
            sheet_chatgpt[column_p].alignment  = alignment_center  # 对齐方式: 水平居中 垂直居中

            # Q列 著作権侵害関連
            column_q = 'Q' + str(row_idx)
            sheet_to_client[column_q]   = f'= IF(COUNTIF(U{row_idx}, "*著作権侵害関連*")>0, "●", "")'
            sheet_chatgpt[column_q]     = f'= IF(COUNTIF(U{row_idx}, "*著作権侵害関連*")>0, "●", "")'
            sheet_to_client[column_q].border = thin_border  # 设置边框
            sheet_chatgpt[column_q].border = thin_border  # 设置边框
            sheet_to_client[column_q].alignment  = alignment_center  # 对齐方式: 水平居中 垂直居中
            sheet_chatgpt[column_q].alignment  = alignment_center  # 对齐方式: 水平居中 垂直居中

            # R列 下請法関連
            column_r = 'R' + str(row_idx)
            sheet_to_client[column_r] = f'= IF(COUNTIF(U{row_idx}, "*下請法関連*")>0, "●", "")'
            sheet_chatgpt[column_r] = f'= IF(COUNTIF(U{row_idx}, "*下請法関連*")>0, "●", "")'
            sheet_to_client[column_r].border = thin_border  # 设置边框
            sheet_chatgpt[column_r].border = thin_border  # 设置边框
            sheet_to_client[column_r].alignment  = alignment_center  # 对齐方式: 水平居中 垂直居中
            sheet_chatgpt[column_r].alignment  = alignment_center  # 对齐方式: 水平居中 垂直居中

            # S列 その他
            column_s = 'S' + str(row_idx)
            sheet_to_client[column_s] = f'= IF(COUNTIF(U{row_idx}, "*その他*")>0, "●", "")'
            sheet_chatgpt[column_s] = f'= IF(COUNTIF(U{row_idx}, "*その他*")>0, "●", "")'
            sheet_to_client[column_s].border = thin_border  # 设置边框
            sheet_chatgpt[column_s].border = thin_border  # 设置边框
            sheet_to_client[column_s].alignment  = alignment_center  # 对齐方式: 水平居中 垂直居中
            sheet_chatgpt[column_s].alignment  = alignment_center  # 对齐方式: 水平居中 垂直居中

            # U列 chatGPT 分类的列
            column_u = 'U' + str(row_idx)
            sheet_to_client[column_u] = f''
            sheet_chatgpt[column_u] = f'=ChatGPT($U$1 & H{row_idx})'

        # 保存修改后的Excel文件
        workbook_to_client.save(new_file_to_client_path)
        workbook_chatgpt.save(new_file_chartgtp_path)


        # 设置打印区域
        # 参考文档: https://www.jianshu.com/p/75eb9342da59
        end_row = row_cnt + 2
        excel_app = win32com.client.Dispatch('Excel.Application')
        excel_app.Visible = False
        excel_app.DisplayAlerts = False
        wb = excel_app.Workbooks.Open(new_file_to_client_path)
        ws = wb.Activesheet
        ws.PageSetup.PrintArea = f"$A$2:$S${end_row}"
        wb.Save()
        excel_app.Quit()

        sns_logger.info(f'文件已创建并处理:{new_file_to_client_path}')
        sns_logger.info(f'文件已创建并处理:{new_file_chartgtp_path}')

    except Exception as e:
        sns_logger.error(e)
        return False




if __name__ == '__main__':

    start_time = time.time()  # 记录开始时间

    # in_clients
    in_clients = sys.argv[1].lower()

    # init log
    init_log(in_clients, 'report')
    # set log level
    conf_env = env_args
    log_level = log_level_args[conf_env]
    sns_logger.setLevel(log_level)

    sns_logger.info("==========  Task Start!!!  ==========")
    sns_logger.info("clinet_cds: {}".format(in_clients))

    if len(sys.argv) < 4:
        sns_logger.error("params not enough, please check your params")
        exit()

    # in_start_date
    in_start_date = sys.argv[2]
    sns_logger.info("start_date: {},".format(in_start_date))
    if not util.is_valid_date(in_start_date):
        sns_logger.error("invalid start_date: {}, date format should be yyyy-mm-dd".format(in_start_date))
        exit()

    # in_end_date
    in_end_date = sys.argv[3]
    sns_logger.info("end_date: {}".format(in_end_date))
    if not util.is_valid_date(in_end_date):
        sns_logger.error("invalid end_date: {}, date format should be yyyy-mm-dd".format(in_end_date))
        exit()


    run(in_clients, in_start_date, in_end_date)

    sns_logger.info("==========  Task End!!!  ==========")


    end_time = time.time()  # 记录结束时间
    execution_time = end_time - start_time
    sns_logger.info("run(%s, %s, %s)" % (in_clients, in_start_date, in_end_date))
    sns_logger.info(f"程序执行了 {execution_time:.6f} 秒")

点赞(0) 打赏

评论列表 共有 0 条评论

暂无评论

微信公众账号

微信扫一扫加关注

发表
评论
返回
顶部