2

I need to write pandas dataframe (df_new, in my case) into an xlsb file which has some formulas. I am stuck on the code below and do not know what to do next:

with open_workbook('NiSource SLA_xlsb.xlsb') as wb:
    with wb.get_sheet("SL Dump") as sheet:

can anyone suggest me how to write dataframe into xlsb file

vishal
  • 209
  • 2
  • 4
  • 14

1 Answers1

1

You could try reading the xlsb file as a dataframe and then concating the two.

import pandas as pd

existingdf = pd.DataFrame()
originaldf = pd.read_excel('./NiSource SLA_xlsb.xlsb'
twodflist = [originaldf, df_new]
existingdf = pd.concat(twodflist)
existingdf.reset_index(drop = True)
existingdf.to_excel(r'PATH\filename.xlsb')

Change the path to wherever you want the output to go to and change filename to what you want the output to be named. Let me know if this works.

oroy
  • 82
  • 16
  • but in my NiSource SLA_xlsb.xlsb i have some formulas, so if i read that and write into the dataframe then those formulas wont work right .? – vishal Jul 16 '19 at 16:17
  • Oh, I wrote the answer before the edit. I will try to provide a method to input without getting rid of the formulas. – oroy Jul 16 '19 at 16:19
  • please do . Thank you oroy – vishal Jul 16 '19 at 16:20
  • You can try using [this](https://pbpython.com/improve-pandas-excel-output.html) to find if that works for what you are trying to do. – oroy Jul 16 '19 at 16:21
  • that one is not what i am looking for ..could you tell me another solution – vishal Jul 17 '19 at 15:09
  • You could try using openpyxl to append the columns of the dataframes to the right xlsb columns – oroy Jul 17 '19 at 15:58