3

I have multiple dataframes, df1, df2 etc. Each dataframe contains different number of columns and rows. Using the StyleFrame library, is it possible to copy all the dataframes one by one into a single sheet in an excel file using the to_excel method by specifying cell locations? Currently, here's what I am doing:

Write df1 into Sheet1 of an excel file

writer = StyleFrame.ExcelWriter("filename")
sf1 = StyleFrame(df1)

#Perform formatting using apply_column_style, apply_headers_style etc.

sf1.to_excel(writer, sheet_name='Sheet1')

Write df2 into Sheet1 of an excel file

writer = StyleFrame.ExcelWriter("filename")
sf2 = StyleFrame(df2)

#Perform formatting using apply_column_style, apply_headers_style etc.

sf2.to_excel(writer, sheet_name='Sheet1')    #Incorrectly overwrites df1 data in the excel

writer.save()
writer.close()

However, the sf2.to_excel() method overwrites df1 data in the excel. What I am looking for is to copy df1 data starting at cell location, say "A2", in Sheet1 and then sf2 data starting at location, say "B31", in the same sheet and so on.

viks_python
  • 77
  • 1
  • 6

1 Answers1

2

Yes. Just like pandas.to_excel supports startrow and startcol arguments, so does StyleFrame.to_excel.

The API is pretty much the same as pandas's: You need to create an excel_writer object, call to_excel on both styleframes and then call excel_writer.save:

from StyleFrame import StyleFrame, Styler

sf1 = StyleFrame({'a': [1]}, Styler(bg_color='yellow'))
sf2 = StyleFrame({'b': [1]}, Styler(bg_color='blue'))

excel_writer = StyleFrame.ExcelWriter('test.xlsx')
sf1.to_excel(excel_writer)
sf2.to_excel(excel_writer, startcol=3, startrow=2)  # both are zero-based
excel_writer.save()

Outputs

enter image description here

DeepSpace
  • 78,697
  • 11
  • 109
  • 154
  • Perfect! This is exactly what I was looking for. Thanks. Surprisingly, I couldn't find startcol and startrow in the library's documentation - [link](http://styleframe.readthedocs.io/en/latest/api_documentation.html). – viks_python May 04 '18 at 05:19
  • @viks_python Indeed it was a bit overlooked, I updated the docs - http://styleframe.readthedocs.io/en/latest/api_documentation.html#to-excel – DeepSpace May 04 '18 at 11:25
  • @DeepSpace - Can styleframe work when there is already formatted excel file stored in local path and I just wish to write my data to that existing excel file (without losing format). Currently, with excel writer to_excel method, it writes but strips all the format of destination file which we prepared. Can Styleframe help preserve the format of destination file? – The Great Mar 18 '22 at 14:53