Repository navigation
Write pandas output to formatted excel file (don't lose it when writing it) #119
Description
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
Activity
DeepSpace2 commentedon Mar 18, 2022
If I understand correctly, all you have to do is make sure you are using
ExcelWriterwithmode='a'.This will preserve all styling of existing sheets while adding a new sheet called 'new sheet'.
SSMK-wq commentedon Mar 18, 2022
@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 commentedon Mar 18, 2022
@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
StyleFramedataframe only and not pandas dataframe?SSMK-wq commentedon Mar 21, 2022
@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 commentedon Mar 24, 2022
@SSMK-wq No, that's a limit imposed by your OS.
Cata801 commentedon Jan 14, 2024
Hello @SSMK-wq
I have exactly the same problem. Can you tell me how you solved it?