腾讯文档建立一个收集表，收集到的数据包括图片，下载的表格包含图片的链接，这个脚本可以将收集的文字和图片信息自动插入指定的word模板中。
腾讯文档下载图片及excel转word脚本操作过程
'''
需要安装的三方依赖包：
docxtpl
pandas
PySimpleGUI
aiohttp
'''
from docxtpl import InlineImage
from docx.shared import Mm
from docxtpl import DocxTemplate
import pandas as pd
import requests
from io import BytesIO
from os import path
from PIL import Image
import time
import sys
import shutil
import os
import PySimpleGUI as sg
import logging
import logging.config
import threading
import asyncio
import aiohttp
from pathlib import Path
# 根据链接下载图片,并保存到本地,使用下载的图片文件名保存
# 参数url表示下载网址，saveto表示本地保存路径，返回值是保存图片的本地全路径+文件名
def download_image(url, saveto, defaultpic):
# 如果图片无法下载，就返回默认图片的路径
savelocation = defaultpic
if not pd.isna(url):
if 'http' in url or 'https' in url:
time.sleep(0.5)
r = requests.get(url)
img = Image.open(BytesIO(r.content))
# 取网址参数中的图片文件名
filename = path.basename(url).split("?")[0]
#logger.info(f'正在下载图片......{filename}')
logging.info(f'正在下载图片......{filename}')
# 合并保存图片的本地全路径+文件名
savelocation = path.join(saveto, filename) # saveto r'D:\Users\admin\Desktop\img'
img.save(savelocation)
return savelocation
# 协程（多任务）下载
# 参数url表示下载网址，saveto表示本地保存路径，返回值是保存图片的本地全路径+文件名
# defaultpic是空白默认图片路径
async def down_img(session, url, saveto, defaultpic, df, colname, row):
# 如果图片无法下载，就返回默认图片的路径
savelocation = defaultpic
"""下载图片"""
# name = url.split('/')[-1] # 获得图片名字
if not pd.isna(url):
if 'http' in url or 'https' in url:
# 取网址参数中的图片文件名
filename = path.basename(url).split("?")[0]
# 合并保存图片的本地全路径+文件名
savelocation = path.join(saveto, filename)
img = await session.get(url)
# 触发到await就切换，等待get到数据
content = await img.read()
# 读取内容
with open(savelocation, 'wb') as f:
# 写入至文件
f.write(content)
print(f'{savelocation} 下载完成！')
df.at[row, f"{colname}_localpath"] = path.abspath(savelocation)
return savelocation
async def main(cfg_pic, data, image_path, defaultpic):
# 取excel表格的行数
num = data.shape[0]
n_cfg_pic = cfg_pic.shape[1]
# 使用协程加速图片下载
# 协程任务数组
tasks = []
conn = aiohttp.TCPConnector(limit=10)
# 建立会话session
async with aiohttp.ClientSession(connector=conn) as session:
for i in range(num):
for j in range(n_cfg_pic):
colname = cfg_pic.iloc[:,j].name
tasks.append(asyncio.create_task(down_img(session, data[colname][i], image_path, defaultpic, data, colname, i)))
# 触发await，等待任务完成
done, pending = await asyncio.wait(tasks)
all_results = [done_task.result() for done_task in done]
# 获取所有结果
print(f"ALL RESULT({len(all_results)}):{all_results}")
# 参数resume_table是腾讯文档导出的excel简历汇总表, word_template是word简历模板,
# default_pic是默认空白图片, image_path是图片保存路径, cfg_table是配置excel简历列名与word简历模板中每项对应关系的表
def xlsx2docx_resume_converter(resume_table, word_template, default_pic, image_path, cfg_table, docx_path):
# 程序开始时间
start_time = time.time()
print(f"{time.strftime('%X')}开始导出")
data = pd.read_excel(resume_table, dtype=str)
# 取excel表格的行数
num = data.shape[0]
tpl = DocxTemplate(word_template)
cfg_str = pd.read_excel(cfg_table, sheet_name='字符项')
n_cfg_str = cfg_str.shape[1]
cfg_date = pd.read_excel(cfg_table, sheet_name='日期项')
n_cfg_date = cfg_date.shape[1]
cfg_pic = pd.read_excel(cfg_table, sheet_name='图片项')
n_cfg_pic = cfg_pic.shape[1]
# 为原始表增加下载图片本地路径列
data.to_excel('添加列前.xlsx')
data = pd.concat([data, pd.DataFrame(columns=[f"{cfg_pic.iloc[:,j].name}_localpath" for j in range(n_cfg_pic)])])
# 将nan转为空字符串''，避免显示输出的在word文档中
g = lambda x : x if not pd.isna(x) else ''
# 如果表中不存在下载图片的本地路径，就改为默认空白图片
f = lambda x : x if not pd.isna(x) else default_pic
asyncio.run(main(cfg_pic, data, image_path, default_pic))
#print(data)
excel_filename,extension = path.splitext(path.basename(resume_table))
data.to_excel(f'{excel_filename}_简历列表及下载图片的本地路径.xlsx')
for i in range(num):
context = {}
for j in range(n_cfg_str):
key1 = cfg_str.iloc[:,j].iloc[0]
colname = cfg_str.iloc[:,j].name
value1 = g(data[colname][i])
#logger.info("简历表文字内容：",key1,colname,value1)
logging.info(f"简历表文字内容：{key1} {colname} {value1}")
context[key1] = value1
for j in range(n_cfg_date):
key1 = cfg_date.iloc[:,j].iloc[0]
colname = cfg_date.iloc[:,j].name
value1 = g(data[colname][i].split()[0])#strftime("%Y年%m月%d日"))
#logger.info("简历表日期：",key1,colname,value1)
logging.info(f"简历表日期：{key1} {colname} {value1}")
context[key1] = value1
for j in range(n_cfg_pic):
key1 = cfg_pic.iloc[:,j].iloc[0]
key2 = cfg_pic.iloc[:,j].iloc[1]
key3 = cfg_pic.iloc[:,j].iloc[2]
colname = cfg_pic.iloc[:,j].name
value1 = InlineImage(tpl, f(data[f"{colname}_localpath"][i]), width=Mm(key2),height=Mm(key3))
logging.info(f"简历表图片：{key1} {colname}")
#logger.info("简历表图片：",key1,colname)
context[key1] = value1
tpl.render(context)
j = data[cfg_str.iloc[:,0].name][i]
c = data[cfg_str.iloc[:,1].name][i]
n = data[cfg_str.iloc[:,2].name][i]
t = data[cfg_date.iloc[:,0].name][i].split()[0]#strftime("%Y年%m月%d日")
docx_filename = f'简历{i+1}_{j}{c}{n}{t}.docx'
#docx_filename = '简历'+str(i+1)+'_'+j+c+n+t+'.docx'
s2 = path.join(docx_path, docx_filename)
tpl.save(s2)
print(f'已导出第{i+1}份简历，姓名：{n}')
end_time = time.time() # 程序结束时间
run_time = end_time - start_time # 程序的运行时间，单位为秒
print(f"{time.strftime('%X')}导出结束，共导出{num}份简历，用时{round(run_time,2)}秒")
def start_thread(resume_table, word_template, default_pic, image_path, cfg_table, docx_path):
#print('开始执行')
# 让格式转换函数在子线程中运行
thread = threading.Thread(target=xlsx2docx_resume_converter, args=(resume_table, word_template, default_pic, image_path, cfg_table, docx_path))
# 下面是设置守护线程：如果在程序中将子线程设置为守护线程，则该子线程会在主线程结束时自动退出
thread.setDaemon(True)
thread.start() # 启动线程
if __name__ == '__main__':
#logging.config.fileConfig('logging.conf')
# create logger
#logger = logging.getLogger('simpleExample')
logging.basicConfig(filename='脚本执行日志.log',level=logging.INFO)
#2) 定义布局，确定行数
layout=[[sg.Text('请选择excel简历文件')],
[sg.In(key='-XLSX-'),sg.FileBrowse(button_text = '选择文件',target='-XLSX-',file_types = (('All Files','*.xlsx'),)),],
[sg.Text('请选择word模板文件')],
[sg.In(key='-DOCX-'),sg.FileBrowse(button_text = '选择文件',target='-DOCX-',file_types = (('All Files','*.docx'),)),],
[sg.Text('请选择默认空白图片')],
[sg.In(key='-defaultpic-'),sg.FileBrowse(button_text = '选择文件',target='-defaultpic-',file_types = (('All Files','*.png'),)),],
[sg.Text('请选择列名配置文件')],
[sg.In(key='-cfgtab-'),sg.FileBrowse(button_text = '选择文件',target='-cfgtab-',file_types = (('All Files','*.xlsx'),)),],
[sg.Text('图片下载路径')],
[sg.In(key='-imagepath-'),sg.FolderBrowse(button_text = '选择文件夹',target='-imagepath-'),],
[sg.Text('word简历保存路径')],
[sg.In(key='-docxResumePath-'),sg.FolderBrowse(button_text = '选择文件夹',target='-docxResumePath-'),],
[sg.Text('执行过程打印消息：')],
[sg.ML(default_text='',disabled=True,size=(50,6),reroute_stdout=True)],
[sg.Button('清空下载图片'),sg.Button('清空word简历'),sg.Button('确定'),sg.Button('取消')]]
#3) 创建窗口
window=sg.Window('简历excel转word工具',layout)
#4) 事件循环
while True:
event,values=window.read()#窗口的读取，有两个返回值(1.事件 2.值)
#logger.info("事件：",event,"值：",values)
logging.info(f"windows窗口事件：{event}，值：{values}")
if event == '确定':
start_thread(values['-XLSX-'], values['-DOCX-'], values['-defaultpic-'], values['-imagepath-'], values['-cfgtab-'], values['-docxResumePath-'])
if event == '清空下载图片':
imgdir = values['-imagepath-']
shutil.rmtree(imgdir)
os.mkdir(imgdir)
print(f'目录“{imgdir}”中内容已清空')
logging.info(f'目录“{imgdir}”中内容已清空')
if event == '清空word简历':
docxdir = values['-docxResumePath-']
shutil.rmtree(docxdir)
os.mkdir(docxdir)
print(f'目录“{docxdir}”中内容已清空')
logging.info(f'目录“{docxdir}”中内容已清空')
if event==None or event == sg.WINDOW_CLOSED or event == '取消':#窗口关闭事件
break
#5) 关闭窗口
window.close()
logging.info("窗口已关闭")
952
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
