Skip to main content
 首页 » 编程设计

python之ExcelWriter 在书写时覆盖工作表

2024年11月24日14lori

该程序应将各个工作表的内容放入一个 Excel 工作簿中。它几乎做到了,但它是覆盖而不是将新工作表 append 到最终工作簿中。我读到 pandas excel writer 是解决这个问题的方法,关于为什么它有这种行为有什么想法吗?

import xlwt, csv, os 
from openpyxl import load_workbook 
import pandas as pd 
from pandas import ExcelWriter 
 
csv_folder = r'C:\Users\Me\Desktop\Test_Folder\\' 
 
for fil in os.listdir(csv_folder): 
 
    if '.xlsx' not in fil: 
        continue 
    else: 
        pass 
 
    df = pd.read_excel(csv_folder+fil, encoding = 'utf8') 
 
    file_name = fil.replace('.xlsx','') 
 
    writer = pd.ExcelWriter('condensed_output.xlsx', engine = 'xlsxwriter') 
    df.to_excel(writer, sheet_name = file_name) 
    writer.save() 
    #writer.close() 

请您参考如下方法:

确保 writer.save() 在循环之外。还要注意工作表名称的字符限制,因此如果文件名在某一点上相同,您也有可能以这种方式覆盖工作表名称。

import xlwt, csv, os 
from openpyxl import load_workbook 
import pandas as pd 
from pandas import ExcelWriter 
 
csv_folder = r'C:\Users\Me\Desktop\Test_Folder\\' 
writer = pd.ExcelWriter('condensed_output.xlsx', engine = 'xlsxwriter') 
for fil in os.listdir(csv_folder): 
 
    if '.xlsx' not in fil: 
        continue 
    else: 
        pass 
 
    df = pd.read_excel(csv_folder+fil, encoding = 'utf8') 
 
    file_name = fil.replace('.xlsx','') 
 
 
    df.to_excel(writer, sheet_name = file_name)  
 
writer.save() #make sure this is outside of the loop.  

ETA:也在循环外建立编写器