本篇介紹,把多個excel表分別寫到一個表格對應的多個sheet裏面,每一個表的名稱就是sheet的名字python
import os import pandas as pd dir = './table_dir' # 獲取目錄下全部的表 origin_file_list = os.listdir(dir) print(origin_file_list) with pd.ExcelWriter('result.xls') as writer: # 循環遍歷表格 for i in origin_file_list: # 拼接每一個文件的路徑 file_path = dir + '/' + i # 把表名賦予給對應的sheet sheet_name = i[:-4] df = pd.read_excel(file_path) #變相解決表格中第一行第一列爲空的缺陷 string = "".join(list(str(i) for i in df.index)) # 判斷若是索引都爲數字,則不保留索引(根據本身代碼調整) if string.isdigit(): df.to_excel(writer, sheet_name,index=False) else: df.to_excel(writer, sheet_name)
import os import openpyxl dir = './table_dir' # 獲取目錄下全部的表 origin_file_list = os.listdir(dir) wb2 = openpyxl.Workbook() for file in origin_file_list: file_path = dir + '/' + i sheet_name = file.split('/')[-1].split('.')[0] print(sheet_name) old_wb = openpyxl.load_workbook(file_path ) old_sheet_name = old_wb.get_sheet_names()[0] old_ws = old_wb[old_sheet_name] ws2 = wb2.create_sheet(sheet_name) for row in old_ws.values: ws2.append(row) wb2.save('result.xls')
例如圖:
git