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

Sorry, I mean existing excel file (and not create a new excel file). — The Great
– The Great, Commented Mar 18, 2022 at 13:56
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
– The Great, Commented Mar 18, 2022 at 14:37

Vaibhav Jadhav · Accepted Answer · 2022-03-18 14:30:14Z

5

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, 2022 at 14:30

answered Mar 18, 2022 at 13:51

Vaibhav Jadhav

2,0941 gold badge9 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

The Great Over a year ago

I wish to write to an existing sheet. do not wish to create a new sheet

Vaibhav Jadhav Over a year ago

Is there any data in existing sheet?

The Great Over a year ago

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 Over a year ago

Yes, there are other sheets where there is data. In this specific sheet there is no data

Vaibhav Jadhav Over a year ago

Check the updated comment. If still it doesn't work. Let me try something else.

|

Sector97 · Accepted Answer · 2022-03-19 21:18:29Z

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.

How would I go about replacing a number in this scenario; I want a number value to replace a number in a cell in my excel sheet. Thank you! :)

Fraser Hay · Accepted Answer · 2022-07-25 23:44:06Z

1

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.

edited Jul 25, 2022 at 23:44

answered Jul 25, 2022 at 23:32

Fraser Hay

731 silver badge4 bronze badges

2 Comments

The Great Over a year ago

I found a wonderful package which did the work for me. Here it is 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

Chadee Fouad Over a year ago

Nice but this script will fail if Windows is locked so the user must be logged in for this to work

Chadee Fouad · Accepted Answer · 2023-09-14 04:34:46Z

Please try this code below. It writes data to an existing sheet while keeping the formatting untouched :-)

from win32com.client import Dispatch
import pandas as pd


data= {
    'EmployeeID': [101, 102, 103, 104],
    'FirstName': ['Mohammed', 'Ahmed', 'Aly', 'Mostafa'],
    'City': ['Cairo', 'Suez', 'Sinai', 'Sharm El Sheikh'],
    'Department': ['HR', 'Finance', 'IT', 'Marketing'],
    'Salary': [60000, 75000, 80000, 70000]
}
df = pd.DataFrame(data)

xlApp = Dispatch("Excel.Application")
xlApp.Visible = 1
workbook_path = r'C:\test\test.xlsx'
xlApp.Workbooks.Open(workbook_path)

sheet_name = 'DATA" #Make sure this sheet exists!
sheet = xlApp.ActiveWorkbook.Sheets(sheet_name)
sheet.select
sheet.cells.clearcontents # keep as lowecase letters. Camel case doesn't work?!
y, x = 1 + df.shape[0], df.shape[1]
sheet.Range(sheet.Cells(1, 1), sheet.Cells(1, x)).Value = list(df.columns)
sheet.Range(sheet.Cells(2, 1), sheet.Cells(y, x)).Value = list(df.values)

xlApp.ActiveWorkbook.RefreshAll #Refresh Pivot Tables if any
xlApp.ActiveWorkbook.Save

send_email = False
if send_email == True: xlApp.Application.Run('Python_SendEmail') # In case you wanted to run vba macro
xlApp.ActiveWorkbook.Close(SaveChanges=True)
xlApp.Quit()
del xlApp

hyperzy · Accepted Answer · 2022-12-20 04:02:37Z

0

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.

answered Dec 20, 2022 at 4:02

hyperzy

111 bronze badge

Collectives™ on Stack Overflow

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

5 Answers 5

8 Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

8 Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related