参考文章#xff1a;https://www.jianshu.com/p/5e00dc2c9f4c

copy

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

copy

win32com.clientsys.path.append(../)def

run(client_cds,

sns_db.get_clients_by_cds(in_clients)if

len(client_info)

client_info[0][client_name]report_month

)records

sns_db.get_report_data(client_cds,

start_date,

/report/template/template-to-client.xlsxtemplate_chartgtp_path

os.getcwd()

/report/template/template_chartGTP.xlsm#

获取当前日期并格式化为字符串例如2023-10-23today

datetime.now().strftime(%Y-%m-%d)#

新文件的路径使用当前日期作为文件名的一部分new_file_to_client_path

os.getcwd()

f/report/output/【{client_name}】月次レポート{report_month}.xlsxnew_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,

to_client

load_workbook(new_file_to_client_path)sheet_to_client

chatGPT

load_workbook(new_file_chartgtp_path)sheet_chatgpt

Border(leftSide(border_stylethin,

color0070C0),rightSide(border_stylethin,

color0070C0),topSide(border_stylethin,

color0070C0),bottomSide(border_stylethin,

color0070C0))#

str(row_idx)sheet_to_client[column_a]

ROW()-2sheet_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

str(current_row[date]).replace(-,

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

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

current_row[url]sheet_to_client[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

current_row[title]sheet_to_client[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

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

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

current_row[content]sheet_to_client[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

str(row_idx)sheet_to_client[column_i]

判断语言

Japanese:sheet_chatgpt[column_i]

contentelse:sheet_chatgpt[column_i]

fChatGPT(将评论翻译成日语

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

str(row_idx)sheet_to_client[column_j]

*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

str(row_idx)sheet_to_client[column_k]

*商品関連*)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

str(row_idx)sheet_to_client[column_l]

*接客関連*)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

str(row_idx)sheet_to_client[column_m]

*店舗関連*)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

str(row_idx)sheet_to_client[column_n]

*在庫関連*)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

str(row_idx)sheet_to_client[column_o]

*労務関連*)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

str(row_idx)sheet_to_client[column_p]

*トラブル関連*)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

str(row_idx)sheet_to_client[column_q]

*著作権侵害関連*)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

str(row_idx)sheet_to_client[column_r]

*下請法関連*)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

str(row_idx)sheet_to_client[column_s]

*その他*)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

str(row_idx)sheet_to_client[column_u]

fChatGPT($U$1

保存修改后的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

win32com.client.Dispatch(Excel.Application)excel_app.Visible

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

log_level_args[conf_env]sns_logger.setLevel(log_level)sns_logger.info(

Task

sys.argv[2]sns_logger.info(start_date:

not

util.is_valid_date(in_start_date):sns_logger.error(invalid

start_date:

yyyy-mm-dd.format(in_start_date))exit()#

sys.argv[3]sns_logger.info(end_date:

not

util.is_valid_date(in_end_date):sns_logger.error(invalid

end_date:

yyyy-mm-dd.format(in_end_date))exit()run(in_clients,

in_start_date,

start_timesns_logger.info(run(%s,

%s,

in_end_date))sns_logger.info(f程序执行了

{execution_time:.6f}