# -*- coding:utf-8 -*-
import os
import openpyxl
import PySimpleGUI as sg
import xlrd
from docx import Document
import time
import threading
"""
安装依赖 xlrd PySimpleGUI python-docx openpyxl
"""
def get_headers_from_xlsx(worksheet):
headers = []
for column in worksheet.iter_cols(1, worksheet.max_column):
if column[0].value and len(str(column[0].value).strip()) > 0:
headers.append(str(column[0].value).strip())
else:
headers.append('')
return headers
def get_headers_from_xls(worksheet):
num_cols = worksheet.ncols
headers = []
for curr_col in range(num_cols):
cell = worksheet.cell_value(0, curr_col)
if cell and len(str(cell).strip()) > 0:
headers.append(str(cell).strip())
else:
headers.append('')
return headers
def read_data_form_xlsx_file(excelFile):
# 打开一个workbook
try:
workbook = openpyxl.load_workbook(excelFile)
except FileNotFoundError as e:
# print(e)
print(f"当前文件不存在【{excelFile}】，程序自动退出！\n")
return ([], [])
except Exception as e:
print(e)
print(f"打开当前文件报错【{excelFile}】，程序自动退出！\n")
return ([], [])
# 抓取所有sheet页的名称
sheetnames = workbook.sheetnames
worksheets = workbook.worksheets
print(f"读取表名为：{sheetnames}")
if not worksheets or len(worksheets) == 0:
return ([], [])
worksheet = worksheets[0]
# 遍历sheet1中所有行row
num_rows = worksheet.max_row
num_cols = worksheet.max_column
print(f"当前excel共 {num_rows}行 {num_cols}列")
if num_rows <= 0 or num_cols <= 0:
return ([], [])
headers = get_headers_from_xlsx(worksheet)
print(f"开始读取表头：")
if len(headers) == 0:
return ([], [])
print(f"{headers}\n")
orderExpDatas = []
# 遍历sheet1中所有单元格cell 第一行是标题
for row in worksheet.iter_rows(2, worksheet.max_row):
orderExpData = {}
for coln in range(worksheet.max_column):
if len(headers[coln]) > 0:
cell = row[coln]
if not cell or not cell.value or str(cell.value).strip() == "":
# print(f"第 {rown}行 {coln}列的 {headers[coln]} 为空")
orderExpData[headers[coln]] = ""
else:
orderExpData[headers[coln]] = str(cell.value).strip()
orderExpDatas.append(orderExpData)
return (headers, orderExpDatas)
def read_data_form_xls_file(excelFile):
# 打开一个workbook
try:
workbook = xlrd.open_workbook(excelFile)
except FileNotFoundError as e:
# print(e)
print(f"当前文件不存在【{excelFile}】，程序自动退出！\n")
return ([], [])
except Exception as e:
print(e)
print(f"打开当前文件报错【{excelFile}】，程序自动退出！\n")
return ([], [])
# 抓取所有sheet页的名称
sheetnames = workbook.sheet_names()
worksheets = workbook.sheets()
print(f"读取表名为：{sheetnames}")
if not worksheets or len(worksheets) == 0:
return ([], [])
worksheet = worksheets[0]
# 遍历sheet1中所有行row
num_rows = worksheet.nrows
num_cols = worksheet.ncols
print(f"当前excel共 {num_rows}行 {num_cols}列")
if num_rows <= 0 or num_cols <= 0:
return ([], [])
headers = get_headers_from_xls(worksheet)
print(f"开始读取表头：")
if len(headers) == 0:
return ([], [])
print(f"{headers}\n")
orderExpDatas = []
# 遍历sheet1中所有单元格cell
for rown in range(num_rows):
if rown == 0:
continue
orderExpData = {}
for coln in range(len(headers)):
if len(headers[coln]) > 0:
cell = worksheet.cell_value(rown, coln)
if not cell or str(cell).strip() == "":
# print(f"第 {rown}行 {coln}列的 {headers[coln]} 为空")
orderExpData[headers[coln]] = ""
else:
orderExpData[headers[coln]] = str(cell).strip()
orderExpDatas.append(orderExpData)
return (headers, orderExpDatas)
def read_data_form_file(excelFile):
isXlsx = excelFile.strip().lower().endswith('xlsx')
return read_data_form_xlsx_file(excelFile) if isXlsx else read_data_form_xls_file(excelFile)
def replace_txt_by_data(text, restr, header_keys, header_datas):
if text:
for key in header_keys:
if len(key) <= 0:
continue
regTxt = restr % (key)
text = text.replace(regTxt, header_datas[key])
return text
def replace_all_tables(tables, restr, header_keys, header_datas):
if not tables or len(tables) == 0:
return
for tb in tables:
for row in tb.rows:
for cell in row.cells:
if cell.text:
cell.text = replace_txt_by_data(cell.text, restr, header_keys, header_datas)
def write_word_file_from_datas(word_file, header_keys, header_datas, output_path):
file_name = header_datas[header_keys[0]] if header_keys[0] else None
default_file_name_key = '文件名'
output_path_file = None
if default_file_name_key in header_datas and len(header_datas[default_file_name_key].strip()) > 0:
file_name = header_datas[default_file_name_key].strip()
if file_name and len(file_name) > 0:
doc = Document(word_file)
restr = '{%s}'
replace_all_tables(doc.tables, restr, header_keys, header_datas)
for p in doc.paragraphs:
if p and p.text:
p.text = replace_txt_by_data(p.text, restr, header_keys, header_datas)
if not os.path.exists(output_path):
os.makedirs(output_path, exist_ok=True)
output_path_file = os.path.join(output_path, '%s.docx' % file_name)
doc.save(output_path_file)
print('生成文件：%s' % output_path_file)
else:
print('文件名为空，跳过当前行')
return output_path_file
def start_to_convert(word_file, excel_file, output_path):
excel_headers, excel_datas = read_data_form_file(excel_file)
if not excel_headers or len(excel_headers) == 0:
print("excel表头信息为空，程序自动退出\n")
return
if not excel_datas or len(excel_datas) == 0:
print("excel数据为空，程序自动退出\n")
return
time_str = time.strftime("%d-%H-%M-%S")
output_path = os.path.join(output_path, 'docx', time_str)
# os.makedirs(output_path, exist_ok=True)
output_path_file_set = set()
for data in excel_datas:
output_path_file = write_word_file_from_datas(word_file, excel_headers, data, output_path)
if output_path_file:
output_path_file_set.add(output_path_file)
print("共生成 %s 个文件" % (len(output_path_file_set)))
print("\n")
class MyThread(threading.Thread):
def __init__(self, word_file, excel_file, output_path):
super(MyThread, self).__init__() # 重构run函数必须要写
self.daemon = True
self.word_file = word_file
self.excel_file = excel_file
self.output_path = output_path
self.running = False
self.complete = False
self.__lock = threading.Lock()
def run(self):
try:
with self.__lock:
if not self.running:
self.running = True
start_to_convert(self.word_file, self.excel_file, self.output_path)
self.running = False
