0

Here is my DF.

data3 = {'DCF Years':  ['1st', '2nd', '3rd','4th','5th'],
    'DCF Amt': ['8.5', '6.5', '10.5', '4.5', '12.5']}
df = pd.DataFrame (data3, columns = ['DCF Years', 'DCF Amt'])
df = np.round(df, decimals=3)

And I want to save it to this excel sheet on the second tab (or worksheet). I don't want to use a name, but rather a number of the term sheet.

df.to_excel('/Users/AB/Drive/Earnings/Earnings.xlsx', 
           sheet_name= 1)
ABe
  • 15
  • 4
  • `.to_excel` with a filename doesn't work like that... you're attempting to create a brand new workbook which at that point doesn't actually have sheets you can reference by integer... – Jon Clements Apr 21 '22 at 15:15
  • It's probably simplest to read all the sheets from Earnings... swap the second one out with your new DF... then write all the sheets back... – Jon Clements Apr 21 '22 at 15:16
  • https://stackoverflow.com/questions/42370977/how-to-save-a-new-sheet-in-an-existing-excel-file-using-pandas has some approaches you should be able to use – Jon Clements Apr 21 '22 at 15:18
  • Hi JC. Thank you for your suggestion, however, the goal would be to save it to the existing workbook into a specific sheet, which name can change, but the sequence of the tabs wont. – ABe Apr 21 '22 at 15:36
  • So going by the link above... you can open the workbook... list the sheets to get their names... then use the second sheets name to ovewrite it, then write the whole workbook out again – Jon Clements Apr 21 '22 at 15:38

1 Answers1

0

Assuming your excel sheet already exists, I would use ExcelWriter in append mode - that would be:

with pd.ExcelWriter(r'/Users/AB/Drive/Earnings/Earnings.xlsx', engine='openpyxl', mode='a') as writer:  
    df.to_excel(writer, sheet_name='mydf')

This would 'append' a new sheet at the end of your excel file with 'mydf' as a name. Then everytime you want to append a new sheet to the end, you just rerun the code with a different sheet_name

Daniel Weigel
  • 1,097
  • 2
  • 8
  • 14
  • If you then want to reorder your sheets, here is a solution involving openpyxl https://stackoverflow.com/questions/51082458/move-a-worksheet-in-a-workbook-using-openpyxl-or-xl-or-xlsxwriter – Daniel Weigel Apr 23 '22 at 01:07
  • HI DW. Thank you for the recommendation. I knew this solution and I am looking for something, where you dont put the name, but rather the number of the sheet/tab. e.g.: instead of sheet_name='mydf' more like: sheet_name= [1]. do you know something like this? Thanks – ABe Apr 24 '22 at 19:56