记录首次用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.clientsys.path.append('../')def run(client_cds, start_date, end_date):""":param client_cds::param start_date::param end_date::return:"""try:# connect dbsns_db.connect(client_cds)# get client infoclient_info = sns_db.get_clients_by_cds(in_clients)if len(client_info) < 1:sns_logger.error("No such client [%s] !!!" % in_clients)returnclient_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 fileworkbook_to_client = load_workbook(new_file_to_client_path)sheet_to_client = workbook_to_client['sheet1']# chatGPT fileworkbook_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)# Fontfont_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列 NOcolumn_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 # Fontsheet_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_datesheet_chatgpt[column_b] = obj_datesheet_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 # Fontsheet_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_timesheet_chatgpt[column_c] = obj_timesheet_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 # Fontsheet_chatgpt[column_c].font = font_style # Font# D列 URLcolumn_d = 'D' + str(row_idx)url = current_row['url']sheet_to_client[column_d] = urlsheet_chatgpt[column_d] = urlsheet_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 # Fontsheet_chatgpt[column_d].font = font_style # Font# E列 タイトルcolumn_e = 'E' + str(row_idx)if current_row['category'] != "yelp":# yelp no titletitle = current_row['title']sheet_to_client[column_e] = titlesheet_chatgpt[column_e] = titlesheet_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 # Fontsheet_chatgpt[column_e].font = font_style # Font# F列 サイトカテゴリcolumn_f = 'F' + str(row_idx)category = current_row['category'] + "検索結果"sheet_to_client[column_f] = categorysheet_chatgpt[column_f] = categorysheet_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 # Fontsheet_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_namesheet_chatgpt[column_g] = user_namesheet_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 # Fontsheet_chatgpt[column_g].font = font_style # Font# H列 抜粋文column_h = 'H' + str(row_idx)content = current_row['content']sheet_to_client[column_h] = contentsheet_chatgpt[column_h] = contentsheet_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 # Fontsheet_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] = contentelse: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 # Fontsheet_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 # Fontsheet_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/75eb9342da59end_row = row_cnt + 2excel_app = win32com.client.Dispatch('Excel.Application')excel_app.Visible = Falseexcel_app.DisplayAlerts = Falsewb = excel_app.Workbooks.Open(new_file_to_client_path)ws = wb.Activesheetws.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 Falseif __name__ == '__main__':start_time = time.time() # 记录开始时间# in_clientsin_clients = sys.argv[1].lower()# init loginit_log(in_clients, 'report')# set log levelconf_env = env_argslog_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_datein_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_datein_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_timesns_logger.info("run(%s, %s, %s)" % (in_clients, in_start_date, in_end_date))sns_logger.info(f"程序执行了 {execution_time:.6f} 秒")
