pythonexcelopenpyxlxlsxwriterstyleframe

Possible to Read Excel Tab into Code and Write Back that Same Tab With Formatting


I have code that creates an excel file named file_A using XlsxWriter. Users have sent me an excel file named file_b which just has one tab, named tab_b. They want tab_b appended to file_A. This tab_b includes a lot of formatting (some of it I believe falls under "rich" formatting") that I would rather not have to hardcode into the code base.

Is there a way to upload tab_b into the code base as some kind of tab object? Then, I could append this tab to file_A, and all formatting and hardcoding would be encapsulated in the tab object.

We're already using XlsxWriter and openpyxl in our code base, but I don't know if either were really made to do this sort of thing. I'm open to other modules if necessary, but it does have to be Python compatible.

Example code of how we currently create file_A looks like this:

writer = pd.ExcelWriter(output_file_path, engine='xlsxwriter')
someDataFrame.to_excel(writer, sheet_name='PreExisting_Tab')
writer.save()

Ideally, what I'm hoping to find out, is if there is real code for the following pseudocode below:

writer = pd.ExcelWriter(output_file_path, engine='xlsxwriter')
someDataFrame.to_excel(writer, sheet_name='PreExisting_Tab')

tab_b = readTabWithFormattingIntoPython("filePathToFile_B")
tab_b.to_Excel(writer,sheet_name='tab_b')

writer.save()

This screen shot below depicts tab_b inside of file_b.
A screenshot of a mockup of the excel tab they want appended is pictured here:

UPDATE Thank you @DeepSpace for bringing StyleFrame to my attention. It looks promising, but my initial attempt at proof of concept failed. Here is what I tried (it seems similar enough to the code in your example, not sure why it's failing):

    from StyleFrame import StyleFrame
    writer = pd.ExcelWriter(output_file_path, engine='openpyxl')

    tab_to_convert= StyleFrame.read_excel(file_B_file_path, read_style=True)

    tab_to_convert.to_excel(writer)
    writer.save()

The result of the code above is that an excel file gets created with one sheet that has no data or formatting from the file it was supposed to have read. Within cell A1, it says "This sheet contains FactSet XML data for use with this workbook's =FDS codes. Modifying the worksheet's contents may damage the wrokbook's = FDS functionality."

Not sure if this is why it's not working, but I had to specify, when creating the writer, "engine='openpyxl'". If I didn't specify that, it tried to use XlsxWriter and failed due to AttributeError: 'Workbook' object has no attribute 'get_sheet_by_name'

2ND UPDATE StyleFrame ended up working. See comments below in @DeepSpace's answer.


Solution

  • StyleFrame (which, as a disclaimer, I'm one of the authors of) can do that.

    from StyleFrame import StyleFrame
    
    writer = pd.ExcelWriter(output_file_path)
    someDataFrame.to_excel(writer, sheet_name='PreExisting_Tab')
    
    tab_b = StyleFrame.read_excel("filePathToFile_B", read_style=True)
    tab_b.to_Excel(writer, sheet_name='tab_b')
    
    writer.save()