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

Question

我有两个脚本，通过它们我通过输入工作簿名称来生成两个 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
}

Answer 1

我认为您正在用第二次调用覆盖数据。要将数据附加到现有的 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")