Writing data into xlsx in different sheet

Question

I've two scripts, through which I'M generating two xlsx workbook by giving input of workbook name. I'm running both scripts on the main method. The scenario is when I'm running the main class object, it's running properly but in lieu of appending sheet on workbook created by the first script, it is replacing it. Would you please check where I'm doing thing wrong.Please mind typos and errors as I'm pretty new on here. Main method:

{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()}

{First Script:

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}

Second Script:

{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
}

where do you actually call to_excel? – Gabriel A Jan 20 '18 at 05:59 — Gabriel A, Jan 20 '18 at 05:59

Romain · Answer 1 · 2018-01-20T08:02:59.557

1

I think you are overwriting the data with the second call. To append data to an existing Excel file you have to use the engine openpyxl instead of the default xlsxwriter.

You will find more information here: 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")

edited Jan 20 '18 at 08:02

answered Jan 20 '18 at 07:54

Romain

19,910
6
56
65

this doesn't seems to be work as its giving error. workbook object has not attribute add_format – DKM Jan 20 '18 at 09:25
so what needs to be done on this. I've written all my logic on xlsxwriter and now I've to use openpyxl. any luck how to use it? – DKM Jan 20 '18 at 10:35

Writing data into xlsx in different sheet

1 Answers1