python-3.xexceldataframestyleframe

Excel Workbook must be reopened to see updates from DataFrame / StyleFrame

I am writing a StyleFrame to a new Excel sheet, but I cannot see the changes until I reopen the Workbook. How can I see the update immediately? I tried using a DataFrame as well but with the same result.

writer = StyleFrame.ExcelWriter(cls.wb_path)
writer.book = cls.wb
sf.to_excel(writer, sheet_name="Extra fields", header=False, index=False)
writer.save()

Many thanks in advance!

Solution

  • According to pandas docs, you need to provide mode='a' when creating the ExcelWrite object.

    At the moment this is not possible through the StyleFrame.ExcelWriter shortcut, but you can use pandas.ExcelWriter:

    import pandas as pd

from styleframe import StyleFrame, Styler

sf = StyleFrame({'a': [1, 2, 3]}, styler_obj=Styler(bg_color='yellow'))
writer = pd.ExcelWriter('test.xlsx', mode='a')
sf.to_excel(writer, sheet_name="Extra fields")
writer.save()

    test.xlsx before running the above code:

    enter image description here

    test.xlsx after running the above code:

    enter image description here

    enter image description here

    Regarding

    I cannot see the changes until I reopen the Workbook

    This has nothing to do with pandas, styleframe, openpyxl or even Python in general. You only see the change when you reopen Excel (or any other spreadsheet software you are using) because that is how the said software (or even the OS you are using) is designed. Actually, I'm surprised you even managed to execute the code while the file was open. Usually, you get an explicit PermissionError error if you try to save a sheet to a file which is currently opened:

    writer.save()
PermissionError: [Errno 13] Permission denied: 'test.xlsx'