280 lines
9.4 KiB
Python
280 lines
9.4 KiB
Python
# -*- coding:utf-8 -*-
|
||
################################
|
||
# excel 表格分级下沉填充
|
||
# 任意一个 1 级列的值发生变化，则 2级列停止下沉，
|
||
# 同理 任意一个 2级列的值发生了变化时， 3级列停止下沉，以此类推
|
||
#
|
||
#
|
||
#
|
||
################################
|
||
import os
|
||
import sys
|
||
|
||
import xlrd
|
||
import xlwt
|
||
from xlutils.copy import copy
|
||
import tkinter as tk
|
||
from tkinter import filedialog, dialog
|
||
import tkinter.messagebox
|
||
from tkinter import ttk
|
||
|
||
file_path = ''
|
||
output_file_path = ''
|
||
config_str = ''
|
||
|
||
window = tk.Tk()
|
||
root = tk.Frame(window)
|
||
root.pack()
|
||
|
||
progress_scale = tk.Scale()
|
||
message_label = tk.Label()
|
||
|
||
file_path_tk_var = tk.StringVar()
|
||
file_path_tk_var.set('点击上面按钮选择文件')
|
||
|
||
out_file_path_tk_var = tk.StringVar()
|
||
out_file_path_tk_var.set('点击上面按钮设置保存位置')
|
||
|
||
message_tk_var = tk.StringVar()
|
||
message_tk_var.set("请选择文件并填写处理规则")
|
||
|
||
progress = tk.IntVar()
|
||
progress.set(0)
|
||
|
||
|
||
def create_file(file_name):
|
||
workbook = xlwt.Workbook() # 新建一个工作簿
|
||
sheet = workbook.add_sheet('Sheet1')
|
||
sheet.write(0, 0, None)
|
||
workbook.save(file_name)
|
||
|
||
|
||
def append_write(rows, file_name):
|
||
global message_label
|
||
message_tk_var.set("正在保存，请稍后...")
|
||
message_label.update()
|
||
index = len(rows)
|
||
workbook = xlrd.open_workbook(file_name)
|
||
sheets = workbook.sheet_names()
|
||
worksheet = workbook.sheet_by_name(sheets[0])
|
||
rows_old = worksheet.nrows
|
||
new_workbook = copy(workbook)
|
||
new_worksheet = new_workbook.get_sheet(0)
|
||
for i in range(0, index):
|
||
for j in range(0, len(rows[i])):
|
||
new_worksheet.write(i + rows_old, j, rows[i][j])
|
||
new_workbook.save(file_name) # 保存工作簿
|
||
print("xls格式表格【追加】写入数据成功！")
|
||
|
||
|
||
def main():
|
||
global output_file_path
|
||
global file_path
|
||
global config_str
|
||
global progress
|
||
global message_label
|
||
create_file(output_file_path)
|
||
wb = xlrd.open_workbook(file_path)
|
||
st = wb.sheet_by_index(0)
|
||
title = st.row_values(0)
|
||
append_write([title], output_file_path)
|
||
message_tk_var.set("正在处理数据，请稍后...")
|
||
message_label.update()
|
||
batch_row = []
|
||
tmp_row = [None for _ in range(len(title))]
|
||
num_config = config_format()
|
||
tmp_array = [None for _ in range(len(num_config))] # [ {A = 1,B = 2},{I = 78},{K = 'ldk'} ]
|
||
for i in range(1, st.nrows):
|
||
row_v = st.row_values(i)
|
||
for step in range(0, len(num_config)): # 先检查缓存是否需要改变
|
||
cell = row_v[num_config[step][0]]
|
||
if (not (cell is None)) & (len(str(cell)) > 0):
|
||
for del_i in range(step, len(num_config)):
|
||
tmp_array[del_i] = None
|
||
new_dic = {}
|
||
for column_num in num_config[step]:
|
||
new_dic[column_num] = row_v[column_num]
|
||
tmp_array[step] = new_dic
|
||
tmp_row = [None for _ in range(len(title))]
|
||
for ci in range(0, len(row_v)):
|
||
tmp_row[ci] = row_v[ci]
|
||
for step in tmp_array:
|
||
if step is None:
|
||
continue
|
||
for k in step.keys():
|
||
tmp_row[k] = step[k]
|
||
batch_row.append(tmp_row)
|
||
if (len(batch_row) >= 100000) | (i == st.nrows - 1):
|
||
append_write(batch_row, output_file_path)
|
||
batch_row = []
|
||
message_tk_var.set("正在处理数据，请稍后...")
|
||
message_label.update()
|
||
if i % 100 == 0:
|
||
progress.set(i / st.nrows * 100)
|
||
global progress_scale
|
||
progress_scale.update()
|
||
tk.messagebox.showinfo(title='处理完成', message='文件保存至\n' + output_file_path)
|
||
progress.set(0)
|
||
message_tk_var.set("处理完成")
|
||
message_label.update()
|
||
|
||
|
||
def config_format():
|
||
global config_str
|
||
config_array = []
|
||
for line in config_str.split('\n'):
|
||
if len(line.replace(' ', '')) == 0:
|
||
continue
|
||
step_array = []
|
||
for column_str in line.split(','):
|
||
column_str = column_str.replace(' ', '')
|
||
if len(column_str) == 0:
|
||
continue
|
||
step_array.append(column_str)
|
||
if len(step_array) > 0:
|
||
config_array.append(step_array)
|
||
num_config = []
|
||
for step in config_array:
|
||
num_step = []
|
||
for column_name in step:
|
||
if len(column_name) == 1:
|
||
col = ord(column_name.upper()) - ord('A') + 1
|
||
num_step.append(col - 1)
|
||
# 输入为AA2类型
|
||
elif len(column_name) == 2:
|
||
col_1 = ord(column_name[0].upper()) - ord('A') + 1
|
||
col_2 = ord(column_name[1].upper()) - ord('A') + 1
|
||
col = col_1 * 26 + col_2
|
||
num_step.append(col - 1)
|
||
num_config.append(num_step)
|
||
return num_config
|
||
|
||
|
||
def open_file():
|
||
global file_path
|
||
file_path = filedialog.askopenfilename(title=u'选择文件', initialdir=(os.path.expanduser('~/')),
|
||
filetypes=[('Excel xls', '*.xls')])
|
||
if len(file_path) > 0:
|
||
file_path_tk_var.set(file_path)
|
||
|
||
|
||
def save_file():
|
||
global output_file_path
|
||
output_file_path = filedialog.asksaveasfilename(title=u'保存文件')
|
||
if len(output_file_path) > 0:
|
||
if not str(output_file_path).endswith('.xls'):
|
||
output_file_path = output_file_path + '.xls'
|
||
out_file_path_tk_var.set(output_file_path)
|
||
|
||
|
||
if __name__ == '__main__':
|
||
window.title('Excel 指定列下沉填充')
|
||
window.geometry('800x600')
|
||
window.minsize(500, 300)
|
||
|
||
|
||
def info():
|
||
info = """
|
||
excel 表格分级下沉填充
|
||
任意一个 1 级列的值发生变化，则 2级列停止下沉，
|
||
同理 任意一个 2级列的值发生了变化时， 3级列停止下沉，以此类推
|
||
|
||
规则填写方法：
|
||
在文本框中，每一行代表一个等级:如第一行代表等级1，第二行代表等级2，以此类推
|
||
每一行中填写需要下沉填充的列号，用逗号隔开，如 ： A,B,C,F,AE,AF
|
||
|
||
如，原表格结构:
|
||
--------------------------------------
|
||
A B C D E
|
||
1 100
|
||
2 45 89
|
||
3 iy qq
|
||
4 ip ui
|
||
--------------------------------------
|
||
|
||
使用规则
|
||
-----------------------
|
||
A
|
||
B,C
|
||
-----------------------
|
||
|
||
填充后的结果是：
|
||
--------------------------------------
|
||
A B C D E
|
||
1 100
|
||
2 100 45 89
|
||
3 100 45 89 iy qq
|
||
4 100 45 89 ip ui
|
||
--------------------------------------
|
||
"""
|
||
tk.messagebox.showinfo(title='使用说明', message=info)
|
||
|
||
|
||
bt0 = ttk.Button(root, text='使用说明', command=info)
|
||
bt0.pack()
|
||
|
||
bt1 = ttk.Button(root, text='打开文件', width=15, command=open_file)
|
||
bt1.pack()
|
||
file_path_label = tk.Label(root, text='', textvariable=file_path_tk_var, bg='grey', font=('Arial', 12))
|
||
file_path_label.pack()
|
||
|
||
f1 = ttk.Frame(root)
|
||
s1 = ttk.Scrollbar(f1, orient=tk.VERTICAL)
|
||
s2 = ttk.Scrollbar(f1, orient=tk.HORIZONTAL)
|
||
config_label = ttk.Label(f1, text='请输入需要下沉填充的列：列名用逗号隔开，一行表示一个等级')
|
||
config_label.pack()
|
||
config_text = tk.Text(f1, width=60, highlightthickness=5, highlightbackground='grey', undo=True,
|
||
yscrollcommand=s1.set,
|
||
xscrollcommand=s2.set,
|
||
wrap=tk.NONE)
|
||
s1.pack(side=tk.RIGHT, fill=tk.Y)
|
||
s1.config(command=config_text.yview)
|
||
s2.pack(side=tk.BOTTOM, fill=tk.X)
|
||
s2.config(command=config_text.xview)
|
||
config_text.pack()
|
||
|
||
f1.pack()
|
||
bt2 = ttk.Button(root, text='保存文件', width=15, command=save_file)
|
||
bt2.pack()
|
||
out_file_path_label = tk.Label(root, text='', textvariable=out_file_path_tk_var, bg='grey', font=('Arial', 12), )
|
||
out_file_path_label.pack()
|
||
|
||
|
||
def start():
|
||
|
||
global config_str
|
||
if len(str(file_path)) < 1:
|
||
tk.messagebox.showwarning(title='未选择文件', message='请点击 "打开文件" 按钮选择需要处理的文件')
|
||
return
|
||
if len(str(output_file_path)) < 1:
|
||
tk.messagebox.showwarning(title='未选择输出文件', message='请点击 "保存文件" 按钮选择输出文件')
|
||
return
|
||
bt3['state'] = tk.DISABLED
|
||
try:
|
||
config_str = config_text.get("0.0", "end")
|
||
try:
|
||
main()
|
||
except Exception as e:
|
||
message_tk_var.set("执行过程出现错误，请重试")
|
||
message_label.update()
|
||
print(e)
|
||
except Exception as e:
|
||
message_tk_var.set("列配置填写错误，请检查")
|
||
message_label.update()
|
||
print(e)
|
||
bt3['state'] = tk.NORMAL
|
||
|
||
|
||
bt3 = ttk.Button(root, text='开始处理', command=start)
|
||
bt3.pack()
|
||
|
||
progress_scale = tk.Scale(root, label='进度', from_=0, to=100, orient=tk.HORIZONTAL, length=200, showvalue=0,
|
||
tickinterval=20,
|
||
variable=progress)
|
||
|
||
message_label = tk.Label(window, text='', textvariable=message_tk_var, bg='yellow', font=('Arial', 12))
|
||
message_label.pack()
|
||
|
||
progress_scale.pack()
|
||
window.mainloop()
|