CODEWITHSUNDEEP
pandasexport-to-excelopenpyxl
Bw.Zhou
Bw.Zhou

Reputation: 31

How to write to an existing excel file without overwriting cell format with pandas(engine=openpyxl)?

I wanted to write large set of data into existing excel. I referred to following link and verified the write function. How to write to an existing excel file without breaking formulas with openpyxl? However, this function overwrote my cell format. For example, my target excel has cell boarders for all sheets. But the newly written row is in blank background (w/o cell boarder). Also, I've already formatted some cells to be 'Currency' but with openpyxl output Excel, all cells became 'General' format.

How can I write to an existing excel file without any change of the cell format?

Thank you all.

Original excel

With written contents

Code to set up Excel Application 

workbook = load_workbook(OutputFile,data_only=True)
writer =pd.ExcelWriter(OutputFile,engine='openpyxl')
writer.book = workbook
writer.sheets = dict((ws.title, ws) for ws in workbook.worksheets)

Code to write the results into excel

    FinalGen.append(FinalGenRow1)
    FinalGen.append(FinalGenRow1)
    df = pd.DataFrame(FinalGen)
    df.to_excel(writer,sheet_name='Test',startrow =3 ,startcol =0,index = False, header = False)

Upvotes: 2

Views: 2262

Answers (1)

Bw.Zhou
Bw.Zhou

Reputation: 31

The only solution I found is to set the cells format by each cell instead of by column. See this post for details. Preserve cell format with openpyxl

Upvotes: 0

Related Questions