Pandas dataframe to specific sheet in a excel file without losing formatting

Question

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

[df.to_excel(sheet_name='some_sheet')](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_excel.html) — It_is_Chris, Mar 18 '22 at 13:51
Sorry, I mean existing excel file (and not create a new excel file). — The Great, Mar 18 '22 at 13:56
Then use an existing file name. You should really read the documentation I linked to. — It_is_Chris, Mar 18 '22 at 13:56
what is the difference between this `with pd.ExcelWriter('output.xlsx', mode='a') as writer: df.to_excel(writer, sheet_name='Sheet_name_3')` and the option that you suggested? — The Great, Mar 18 '22 at 14:03
If you read the Panda's docs you'll see that ExcelWrite creates/overwrites the file. — Charlie Clark, Mar 18 '22 at 14:11
The problem is am not able to write to existing sheet (with certain formatting). When I do the above, i get an error that `ValueError: Sheet 'temp_data' already exists and if_sheet_exists is set to 'error'. I cannot replace the sheet. So, don't know what is the workaround — The Great, Mar 18 '22 at 14:37
Most of the solutions here, strips the formatting of the output excel fille — The Great, Mar 18 '22 at 14:42

Vaibhav Jadhav · Answer 1 · 2022-03-18T14:30:14.940

2

You need to just use to_excel from pandas dataframe.

Try below snippet:

df1.to_excel("output.xlsx",sheet_name='Sheet_name')

If there is existing data please try below snippet:

writer = pd.ExcelWriter('output.xlsx', engine='openpyxl')
# try to open an existing workbook
writer.book = load_workbook('output.xlsx')
df.to_excel(writer,index=False,header=False,startrow=len(reader)+1)
writer.save()
writer.close()

edited Mar 18 '22 at 14:30

answered Mar 18 '22 at 13:51

Vaibhav Jadhav

2,020
1
7
20

I wish to write to an existing sheet. do not wish to create a new sheet – The Great Mar 18 '22 at 13:54
Is there any data in existing sheet? – Vaibhav Jadhav Mar 18 '22 at 13:55
upvoted for the help. Does your code work for existing sheet as well? I understand your code will create a new file called `output.xlsx` but I already have `output.xlsx` – The Great Mar 18 '22 at 13:55
Yes, there are other sheets where there is data. In this specific sheet there is no data – The Great Mar 18 '22 at 13:55
Check the updated comment. If still it doesn't work. Let me try something else. – Vaibhav Jadhav Mar 18 '22 at 14:09
I get the same error bad zip file error – The Great Mar 18 '22 at 14:25
At the end also mention writer.save() and writer.close() On which line you are getting error? – Vaibhav Jadhav Mar 18 '22 at 14:29
The problem with your solution is , it makes my dest file lose all its format. Meaning, it treats my destination file `output.xlsx` as a plain excel file (and strips all of its formatting) – The Great Mar 18 '22 at 14:39

score 1 · Answer 2 · answered Mar 19 '22 at 21:18

Are you restricted to using pandas or openpyxl? Because if you're comfortable using other libraries, the easiest way is probably using win32com to puppet excel as if you were a user manually copying and pasting the information over.

import pandas as pd
import io
import win32com.client as win32
import os

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



df = pd.read_csv(io.StringIO(csv_text),sep = ',')
temp_path = r"C:\Users\[User]\Desktop\temp.xlsx" #temporary location where to write this dataframe
df.to_excel(temp_path,index = False) #temporarily write this file to excel, change the output path as needed

excel = win32.Dispatch("Excel.Application")
excel.Visible = True #Switch these attributes to False if you'd prefer Excel to be invisible while excecuting this script
excel.ScreenUpdating = True 


temp_wb = excel.Workbooks.Open(temp_path)
temp_ws = temp_wb.Sheets("Sheet1")

output_path = r"C:\Users\[User]\Desktop\output.xlsx" #Path to your output excel file
output_wb = excel.Workbooks.Open(output_path)
output_ws = output_wb.Sheets("Output_sheet")

temp_ws.Range('A1').CurrentRegion.Copy(Destination = output_ws.Range('A1')) # Feel free to modify the Cell where you'd like the data to be copied to
input('Check that output looks like you expected\n') # Added pause here to make sure script doesn't overwrite your file before you've looked at the output

temp_wb.Close()
output_wb.Close(True) #Close output workbook and save changes
excel.Quit() #Close excel
os.remove(temp_path) #Delete temporary excel file

Let me know if this achieves what you were after.

Fraser Hay · Answer 3 · 2022-07-25T23:44:06.190

I spent all day on this (and a co-worker of mine spent even longer). Thankfully, it seems to work for my purposes - pasting a dataframe into an Excel sheet without changing any of the Excel source formatting. It requires the pywin32 package, which "drives" Excel as if it a user, using VBA.

import pandas as pd
from win32com import client

# Grab your source data any way you please - I'm defining it manually here:
df = pd.DataFrame([
['LOOK','','','','','','','',''],
['','MA!','','','','','','',''],
['','','I pasted','','','','','',''],
['','','','into','','','','',''],
['','','','','Excel','','','',''],
['','','','','','without','','',''],
['','','','','','','breaking','',''],
['','','','','','','','all the',''],
['','','','','','','','','FORMATTING!']
])

# Copy the df to clipboard, so we can later paste it as text.
df.to_clipboard(index=False, header=False) 

excel_app = client.gencache.EnsureDispatch("Excel.Application") # Initialize instance

wb = excel_app.Workbooks.Open("Template.xlsx") # Load your (formatted) template workbook
ws = wb.Worksheets(1) # First worksheet becomes active - you could also refer to a sheet by name
ws.Range("A3").Select() # Only select a single cell using Excel nomenclature, otherwise this breaks
ws.PasteSpecial(Format='Unicode Text') # Paste as text
wb.SaveAs("Updated Template.xlsx") # Save our work
excel_app.Quit() # End the Excel instance

In general, when using the win32com approach, it's helpful to record yourself (with a macro) doing what you want to accomplish in Excel, then reading the generated macro code. Often this will give you excellent clues as to what commands you could invoke.

I found a wonderful package which did the work for me. Here it is https://github.com/Sydney-Informatics-Hub/copy_xlsx_styles. Just import and write one line of code, it copies all formatting from source sheet to destination sheet — The Great, Jul 27 '22 at 12:25

score 0 · Answer 4 · answered Mar 18 '22 at 14:13

0

The solution to your problem exists here: How to save a new sheet in an existing excel file, using Pandas?

To add a new sheet from a df:

import pandas as pd
from openpyxl import load_workbook
import os
import numpy as np

os.chdir(r'C:\workdir')

path = 'output.xlsx'
book = load_workbook(path)
writer = pd.ExcelWriter(path, engine = 'openpyxl')
writer.book = book
### replace with your df ###
x = np.random.randn(100, 2)
df = pd.DataFrame(x)


df.to_excel(writer, sheet_name = 'x')
writer.save()
writer.close()

answered Mar 18 '22 at 14:13

Olaf

146
1
8

This makes the excel lose the formatting – The Great Mar 18 '22 at 14:40
I see, sorry I missed that you want to preserve the format. Not too sure how to do this, but there are some resources that help you define a format (https://pbpython.com/improve-pandas-excel-output.html). In short, you can read the excel file, make formatting and then write to a new file. – Olaf Mar 18 '22 at 14:51
This package helped solve my problem - https://github.com/Sydney-Informatics-Hub/copy_xlsx_styles – The Great Jul 27 '22 at 12:26

score 0 · Answer 5 · answered Dec 20 '22 at 04:02

You can try xltpl.

Create a template file based on your output.xlsx file.
Render a file with your data.

from xltpl.writerx import BookWriterx  
writer = BookWriterx('template.xlsx')  
d = {'rows': df.values}
d['tpl_name'] = 'tpl_sheet'  
d['sheet_name'] = 'temp_data'  
writer.render_sheet(d)  
d['tpl_name'] = 'other_sheet'  
d['sheet_name'] = 'other'  
writer.render_sheet(d)  
writer.save('out.xls')

See examples.

Pandas dataframe to specific sheet in a excel file without losing formatting

5 Answers5

Linked