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.
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.
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()