记录首次用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} 秒")
