Skip to main content
 首页 » 编程设计

python之将数据写入不同工作表中的 xlsx

2025年01月19日10kevingrace

我有两个脚本,通过它们我通过输入工作簿名称来生成两个 xlsx 工作簿。我在 main 方法上运行这两个脚本。 场景是当我运行主类对象时,它运行正常,但代替在第一个脚本创建的工作簿上附加工作表,它正在替换它。请你检查一下我哪里做错了。请注意错别字和错误,因为我是新来的。 主要方法:

{import Eoc_Summary 
import Eoc_Daily 
 
if __name__ == '__main__': 
    IO_Name = input("Enter IO Name:") 
    IO_ID = int(input("Enter the IO:")) 
 
    obj_summary = Eoc_Summary.Summary(IO_Name, IO_ID) 
    obj_summary.main() 
 
    obj_daily = Eoc_Daily.Daily(IO_Name, IO_ID) 
    obj_daily.main()} 

{第一个脚本:

import pandas as pd 
import cx_Oracle 
import numpy as np 
import openpyxl 
from openpyxl import load_workbook 
from xlsxwriter.utility import xl_rowcol_to_cell 
 
class Summary(): 
    def __init__(self, IO_Name, IO_ID): 
        self.IO_Name=IO_Name 
        self.IO_ID= IO_ID 
 
        self.path = ("C://BiTeam-New-ProjectPython//Bi_Team_Project//Reports//{}({}).xlsx".format(self.IO_Name,self.IO_ID)) 
        self.writer = pd.ExcelWriter(self.path, engine="xlsxwriter", datetime_format="MM-DD-YYYY") 
 
    ####Rest Code 
 
def write_summary(self): 
    summary_old = self.adding_column_Spend() 
    data_common_columns = self.common_columns_summary() 
    summary_new = summary_old.fillna(0) 
 
    summary = data_common_columns[1].to_excel(self.writer, sheet_name="Summary({})".format(self.IO_ID), startcol=0, 
                                              startrow=7, index=False, header=False) 
 
    final_summary = summary_new.to_excel(self.writer, sheet_name="Summary({})".format(self.IO_ID),  startcol=0, startrow=12, 
                                         header=True, index=False) 
 
 
    return summary, final_summary, summary_old 
    def main(self): 
        self.common_columns_summary() 
        self.connect_TFR_summary() 
        self.read_query_summary() 
        self.access_data_summary() 
        self.summary_creation() 
        self.rename_cols_sumary() 
        self.adding_column_Delivery_summary() 
        self.adding_column_Spend() 
        self.write_summary() 
        self.common_summary() 
 
if __name__ == "__main__": 
    pass} 

第二个脚本:

{import pandas as pd 
import cx_Oracle 
import numpy as np 
from xlsxwriter.utility import xl_rowcol_to_cell 
class Daily(): 
    def __init__(self, IO_Name, IO_ID): 
        self.IO_Name=IO_Name 
        self.IO_ID= IO_ID 
        self.path=("C://BiTeam-New-ProjectPython//Bi_Team_Project//Reports//{}({}).xlsx".format(self.IO_Name,self.IO_ID)) 
        self.writer = pd.ExcelWriter(self.path,engine="xlsxwriter", datetime_format="MM-DD-YYYY") 
 
    ###Rest Code 
 
 
def write_KM_Sales_summary(self): 
    data_common_columns = self.common_Columns_daily() 
    accessing_KM_columns, accessing_sales_columns = self.adding_vcr_ctr_IR_ATS_daily() 
    replace_blank_with_zero_KM = accessing_KM_columns.fillna(0) 
    replace_blank_with_zero_sales = accessing_sales_columns.fillna(0) 
 
    writing_data_common_columns = data_common_columns[1].to_excel(self.writer, sheet_name="Daily Performance({})".format(self.IO_ID), startcol=0, startrow=7, index=False, header=False) 
 
    writing_KM_columns = replace_blank_with_zero_KM.to_excel(self.writer, sheet_name="Daily Performance({})".format(self.IO_ID), startcol=0, startrow=12, index=False, header=True) 
 
    writing_sales_columns = replace_blank_with_zero_sales.to_excel(self.writer, sheet_name="Daily Performance({})".format(self.IO_ID), 
                                                             startcol=0, startrow=len(accessing_KM_columns)+16, 
                                                             index=False, header=True) 
 
    return accessing_KM_columns, accessing_sales_columns, replace_blank_with_zero_KM, replace_blank_with_zero_sales 
 
 
    def main(self): 
        self.common_Columns_daily() 
        self.connect_TFR_daily() 
        self.read_Query_daily() 
        self.access_Data_KM_Sales_daily() 
        self.KM_Sales_daily() 
        self.rename_KM_Sales_daily() 
        self.adding_vcr_ctr_IR_ATS_daily() 
        self.write_KM_Sales_summary() 
        self.formatting_daily() 
        self.writer.close() 
 
if __name__ == "__main__": 
    pass 
} 

请您参考如下方法:

我认为您正在用第二次调用覆盖数据。 要将数据附加到现有的 Excel 文件,您必须使用引擎 openpyxl 而不是默认的 xlsxwriter。

您可以在此处找到更多信息: How to write to an existing excel file without overwriting data (using pandas)?

self.writer = pd.ExcelWriter(self.path, engine="openpyxl", datetime_format="MM-DD-YYYY")