Write pandas output to formatted excel file (don't lose it when writing it)

[SSMK-wq]

Hi, I am posting this here as I came to know about Styleframe from SO.

Would like to know whether it is possible to write a pandas output to formatted excel file?

I have a dataframe like as shown below

Date,cust,region,Abr,Number,         
12/01/2010,Company_Name,Somecity,Chi,36,
12/02/2010,Company_Name,Someothercity,Nyc,156,

df = pd.read_clipboard(sep=',')

I would like to write this dataframe to a specific sheet (called temp_data) in the file output.xlsx

Therfore I tried the below

import pandas
from openpyxl import load_workbook

book = load_workbook('output.xlsx')
writer = pandas.ExcelWriter('output.xlsx', engine='openpyxl') 
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

I also tried the below

path = 'output.xlsx'

with pd.ExcelWriter(path) as writer:
    writer.book = openpyxl.load_workbook(path)
    final_df.to_excel(writer, sheet_name='temp_data',startrow=10)
writer.save()

But am not sure whether I am overcomplicating it. I get an error like as shown below. But I verifiedd in task manager, no excel file/task is running

BadZipFile: File is not a zip file

Moreover, I also lose my formatting of the output.xlsx file when I manage to write the file based on below suggestions. I already have a neatly formatted font,color file etc and just need to put the data inside.

Is there anyway to write the pandas dataframe to a specific sheet in an existing excel file? WITHOUT LOSING FORMATTING OF THE DESTIATION FILE

[DeepSpace2]

If I understand correctly, all you have to do is make sure you are using ExcelWriter with mode='a'.

from styleframe import StyleFrame

sf = StyleFrame({'b': [5, 6, 7, 8]})
writer = StyleFrame.ExcelWriter('existing.xlsx', mode='a')
sf.to_excel(writer, sheet_name='new sheet')
writer.save()

This will preserve all styling of existing sheets while adding a new sheet called 'new sheet'.

[SSMK-wq]

@DeepSpace2 - No, i wish to write to an already formatted sheet in an excel file. We manually did all the formatting like color, font, merged cells etc as a header. Now, i want my pandas output to be written to that specific formatted sheet ( where i start from row = 8). Hope this helps. I don't wish to add a new sheet

[SSMK-wq]

@DeepSpace2 - In addition to my comment above, when i specify the exact sheet that i want to write to with append mode and sheet exists = overlay, it strips all the formatting. Thats the problem. Or you mean to say that I should use StyleFrame dataframe only and not pandas dataframe?

[SSMK-wq]

@DeepSpace2 - Thanks, it works now. However, one question. Is it possible to write to the file keeping excel open? Currently, the permission is denied to write if I keep the file open. Is there anyway, that I can do this wriite operation by keeping the file open?

[DeepSpace2]

@SSMK-wq No, that's a limit imposed by your OS.

[Cata801]

@DeepSpace2 - No, i wish to write to an already formatted sheet in an excel file. We manually did all the formatting like color, font, merged cells etc as a header. Now, i want my pandas output to be written to that specific formatted sheet ( where i start from row = 8). Hope this helps. I don't wish to add a new sheet

Hello @SSMK-wq
I have exactly the same problem. Can you tell me how you solved it?

[DeepSpace2]

@Cata801 Can you please check if StyleFrame.read_excel_as_template works for you in this case? You read the excel, update the values you want and save (for tests I suggest saving as another file :) )