How to save DataFrame to Sheet2 in xlsx file

Question

I created a simple DataFrame using Pandas, and I need to add/replace it to Sheet2 in my Xlsx File. Sheets in Xlsx files are: Sheet1, Sheet2 I have two problems:

First being that even tho I specify the sheetName, it all it does is removes all sheets that were already in the database, and creates only one with the name specified with the data.

Second is I can't remove the column index from the dataframe. I tried adding 'index_col=None' but all it does is causes errors.

Code So Far:

import pandas as pd
from pandas import ExcelWriter
from pandas import ExcelFile
import numpy as np

Number1 = '123456'
Number2 = '987654'
Number3 = '888888'
Comp = 'HelloAll'
excel_file = 'C:/Users/username/Desktop/testFile.xlsx'

data = {'Number1':  [Number1],
        'Number2':  [Number2],
        'Number3':  [Number3],
        'Comp':  [Comp]
        }

df = pd.DataFrame (data, columns = ['Number1','Number2', 'Number3', 'Comp'])
pd.set_option('display.max_columns', None)
print (df)
with ExcelWriter(excel_file) as writer:
    df.to_excel(writer, sheet_name='Sheet2')

Expected Result:

Number1 Number2 Number3 Comp
123456  987654  888888  HelloAll

Does this answer your question? [How to save a new sheet in an existing excel file, using Pandas?](https://stackoverflow.com/questions/42370977/how-to-save-a-new-sheet-in-an-existing-excel-file-using-pandas) — bharatk, Feb 12 '20 at 13:09

score 2 · Accepted Answer · answered Feb 12 '20 at 13:14

1) In order to preserve the sheets of your file the first thing you need to do is import all of them and then re-write them. One way that works good for me is using xlsxwriter:

import pandas as pd

# Read the 1st sheet and import it as first dataframe
#df1 = pd.read_excel('testFile.xlsx', sheet_name='Sheet1')
df1 = pd.DataFrame({'A': [1,2,3], 'B': [4,5,6]}) # for this example i created a simple df

# Read the 2nd sheet and import it as second dataframe
#df2 = pd.read_excel('testFile.xlsx', sheet_name='Sheet2')

# Do some data manipulation
Number1 = '123456'
Number2 = '987654'
Number3 = '888888'
Comp = 'HelloAll'

data = {'Number1': [Number1],
        'Number2': [Number2],
        'Number3': [Number3],
        'Comp': [Comp]}

df2 = pd.DataFrame (data, columns = ['Number1','Number2', 'Number3', 'Comp'])

# Kickstart the xlsxwriter
writer = pd.ExcelWriter('testFile.xlsx', engine='xlsxwriter')
df1.to_excel(writer, sheet_name='Sheet1', index=False)
df2.to_excel(writer, sheet_name='Sheet2', index=False)

# Finally write the file
writer.save()

2) About your second problem just use the argument index=False when you write the file.

This works as described, thanks. I did however forgot to mention that sheet1 is heavy stylized, it has a lot of merged cells, images etc. I wanted to use sheet2 to plot data for sheet1 and simply link them. With they way you described it all works great, but after i run the program, all styles from Sheet1 are removed. — babis95, Feb 12 '20 at 13:52
Hello Babi, you are right, all of the formats will be removed. You will need to format all of the sheets before you write the file. Xlsxwriter is a very good library for that, you can also check Openpyxl. Another option is to have a macro and format the sheet through excel. — Dimitris Thomas, Feb 12 '20 at 14:24

geekzeus · Answer 2 · 2020-02-12T14:58:28.573

0

#pip install openpyxl

writer = pd.ExcelWriter('output.xlsx')  
df.to_excel(writer,"Sheetnamethatyouwant")  
writer.save()

EDIT

#For adding new data

from openpyxl import load_workbook

path = r"path of xlsx that you want to add"
book = load_workbook(path)
writer = pd.ExcelWriter(path, engine = 'openpyxl')
writer.book = book

df.to_excel(writer, "sheetnamryouwant")
writer.save()

edited Feb 12 '20 at 14:58

answered Feb 12 '20 at 12:46

geekzeus

785
5
14

Thanks for answer, but this does exactly what my code does. Removes all sheets from the file, and creates new one with name specified. I need to keep all sheets already in the database, and only replace data from one selected sheet (Sheet2), – babis95 Feb 12 '20 at 12:53

How to save DataFrame to Sheet2 in xlsx file

2 Answers2