[Python] 纯文本查看 复制代码
# -*- coding: UTF-8 -*-
import os, re, sys
import xlwings as xw
import tkinter as tk
from tkinter import filedialog
class Xlsx2Xlsx(object):
def __init__(self):
super(Xlsx2Xlsx, self).__init__()
self.pattern = re.compile(r"(\d{2,4})\D*(\d{0,2})\D*(\d{0,2})")
# files path
def filePath(self):
root = tk.Tk()
root.withdraw()
path = filedialog.askdirectory(title = "选择Excel文件目录")
model = filedialog.askopenfilename(title="选择Excel模板",filetypes=(('File', '*.*'),('XLSX', '*.xlsx'),('XLS','*.xls')))
xls = [i for i in os.listdir(path) if (i.endswith(".xlsx") or i.endswith(".xls")) and not i.startswith("~$")]
return path, xls, model
# xlsx 2 xlsx
def xlsx2xlsx(self, path, xls, model):
app = xw.App(visible=False, add_book=False)
#获取模板数据
wb_model = app.books.open(model)
data = []
for i in range(len(wb_model.sheets)):
ws_model = wb_model.sheets[i]
info = ws_model.used_range
nrows = info.last_cell.row
ncols = info.last_cell.column
data.append(ws_model.range((1, 1), (nrows, ncols)).value) #所有行数据
wb_model.close()
wb_result=app.books.add() #新建表格
row = 1
nums = len(xls)
size = 40 #符号数量即进度条长度
for r, x in enumerate(xls):
wb_xl = app.books.open(path + r"/" + x)
for s in range(len(wb_xl.sheets)):
#判断是否为空表
ws_xl = wb_xl.sheets[s]
ws_range = ws_xl.used_range
ws_info = ws_xl.range((1, 1), (ws_range.last_cell.row, ws_range.last_cell.column)).value
if ws_info is None or all(i is None for j in ws_info for i in j): #空表
continue
flag = 0 if len(data) == 1 else s #模板是否只有一个工作表
line = row if len(data) == 1 else r + 1
d = data[flag]
wsnames = [i.name for i in wb_result.sheets]
shtname = "Sheet%d" %(flag + 1)
ws_result = wb_result.sheets[shtname] if shtname in wsnames else wb_result.sheets.add(shtname, after = wsnames[-1])
for i, m in enumerate(d):
if any(m):
for j, n in enumerate(m):
if n: #需要提取该单元格
val = ws_xl.range((i + 1, j + 1)).value
if isinstance(n, float): #我这里如果模板为纯数字,读取后为1.0这样的浮点数,若报错回复我修改
ws_result.range((line, int(n))).value = val
elif "'" in n or "‘" in n or "’" in n or n.isdigit(): #存在单引号(这里半角单引号其实并无法读取,只显示为字符串的数字),防止错输入为全角
ser = int(n) if n.isdigit() else int(n[1:])
ws_result.range((line, ser)).value = r"'" + str(int(val)) if val else ''
elif "d" in n or "D" in n: #日期
sym = n[-1] if n[-1] != "d" and n[-1] != "D" else ""
ser = int(n.split("d")[0]) if "d" in n else int(n.split("D")[0])
if val:
date = self.pattern.findall(str(val))[0]
if date[2]:
date = "{}{}{:0>2s}{}{:0>2s}".format(date[0], sym, date[1], sym, date[2])
elif date[1]:
date = "{}{}{:0>2s}".format(date[0], sym, date[1])
else:
date = date[0]
ws_result.range((line, ser)).value = date
else:
ws_result.range((line, ser)).value = ''
elif "b" in n or "B" in n:
ser1 = int(n.split("-")[1])
ser2 = int(n.split("-")[2])
if ser1 != ser2:
ws_result.range((line, ser1)).value = x
ws_result.range((line, ser2)).value = ws_xl.name
else:
ws_result.range((line, ser1)).value = x + "-" + str(ws_xl.name)
row += 1
wb_xl.close()
bar = "\r{:>3.0f}%| ".format((r+1)/nums*100) + "{{:\u0020<{}s}}".format(size).format("\u2588"*int((r+1)/nums*size)) + " {{:0>{}d}}|{}".format(len(str(nums)), nums).format(r+1)
# sys.stdout.write(bar) #不换行打印,需环境支持
# sys.stdout.flush()
print(bar)
wb_result.save(os.path.dirname(model) + "/" + "result.xlsx") #保存到与模板文件相同位置
wb_result.close()
app.quit()
if __name__ == '__main__':
x2x = Xlsx2Xlsx()
path, xls, model = x2x.filePath() x2x.xlsx2xlsx(path, xls, model)[s]
[复制链接]
发表于 2021-6-9 09:34
收藏
淘帖
有用
分享到朋友圈
