使用说明
1、用于将相同格式的多个excel表格（xlsx,xls）合并为一个excel，相同格式指：excel中的各表名及顺序完全相同，各excel表文件的第一行为标题（合并的文件中删除了标题），第二行为表头（合并的文件中是第一行）
2、合并的数据并未累加，只是简单追加
3、本人应用场景：由于系统功能不完善，只能单独按天导出数据，需求将每天数据合并到一个表格中方便统计. 实际上和wps的会员功能：合并表格-基于同名工作表 功能类似
下载链接：https://wwl.lanzoul.com/iVynr267ifwb 密码:2oqd
[Python] 纯文本查看 复制代码 import os
import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
from openpyxl.styles import Alignment
import tkinter as tk
from tkinter import filedialog
def get_input_folder(entry):
folder_input = filedialog.askdirectory()
entry.delete(0, tk.END)
entry.insert(0, folder_input)
# 如果输入的不是绝对路径，将其视为当前目录下的文件夹名称
if not os.path.isabs(folder_input):
folder_input = os.path.join(os.getcwd(), folder_input)
return folder_input
def adjust_column_widths(workbook_path):
wb = load_workbook(workbook_path)
for sheet in wb.worksheets:
for column_cells in sheet.columns:
max_length = 0
column = column_cells[0].column
for cell in column_cells:
try:
cell_value = str(cell.value)
cell_value = cell_value.replace('\n', ' ').replace('\r', '')
cell_length = sum([2 if ord(c) > 255 else 1 for c in cell_value])
if cell_length > max_length:
max_length = cell_length
except:
pass
adjusted_width = (max_length + 2)
column_letter = get_column_letter(column)
sheet.column_dimensions[column_letter].width = adjusted_width
for cell in column_cells:
cell.alignment = Alignment(wrap_text=True)
wb.save(workbook_path)
def merge_excel_files(input_dir, output_file):
all_sheets_data = {}
for filename in os.listdir(input_dir):
if filename.endswith(('.xls', '.xlsx')):
filepath = os.path.join(input_dir, filename)
try:
if filename.endswith('.xls'):
xls = pd.ExcelFile(filepath)
else:
xls = pd.ExcelFile(filepath)
for sheet_name in xls.sheet_names:
data = pd.read_excel(xls, sheet_name=sheet_name, skiprows=[0], header=0)
data = data.dropna(how='all')
data['数据来自文件'] = os.path.splitext(filename)[0]
if sheet_name not in all_sheets_data:
all_sheets_data[sheet_name] = data
else:
all_sheets_data[sheet_name] = pd.concat([all_sheets_data[sheet_name], data], ignore_index=True)
except Exception as e:
print(f"Error processing {filename}: {e}")
with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
for sheet_name, data in all_sheets_data.items():
if '数据来自文件' not in data.columns[-1]:
data = data[[c for c in data.columns if c != '数据来自文件'] + ['数据来自文件']]
data.to_excel(writer, sheet_name=sheet_name, index=False, header=True, startrow=0)
workbook = writer.book
for sheet in workbook.worksheets:
sheet.sheet_state = 'visible'
adjust_column_widths(output_file)
def check_and_merge(entry, result_label):
input_dir = entry.get()
if not input_dir:
result_label.config(text="请输入文件夹路径或名称")
return
if not os.path.exists(input_dir) or not os.path.isdir(input_dir):
result_label.config(text="文件夹或路径输入有误")
return
excel_files = [f for f in os.listdir(input_dir) if f.endswith(('.xls', '.xlsx'))]
if not excel_files:
result_label.config(text="文件夹或路径输入有误，没有找到Excel文件")
return
output_file = os.path.splitext(os.path.basename(input_dir))[0] + "_合并数据.xlsx"
merge_excel_files(input_dir, output_file)
result_label.config(text="Excel文件合并完成，输出文件：{}".format(output_file))
def main():
root = tk.Tk()
root.title("Excel文件合并工具")
label = tk.Label(root, text="合并表格文件夹路径：")
label.pack(pady=2)
folder_entry = tk.Entry(root, width=50)
folder_entry.pack(pady=5, padx=20)
choose_folder_button = tk.Button(root, text="选择表格文件夹", command=lambda:get_input_folder(folder_entry))
choose_folder_button.pack(pady=10)
result_label = tk.Label(root, text="")
result_label.pack()
merge_button = tk.Button(root, text="合并表格", command=lambda:check_and_merge(folder_entry, result_label))
merge_button.pack(pady=10)
root.mainloop()
if __name__ == "__main__":
main()
| 
发表于 2024-8-2 01:26
