PERF: refactor ExcelFormatter

[chris-b1]

xref #11352

• use to_native_types by block to yield already formatted cells
• yield cells in row-oriented way Openpyxl22 #11144 (comment)

[jreback]

FYI, you might want to reach out o see if they can input these on a columnar basis to make this faster

• xlsxwriter cc @jmcnamara
• openpyxl cc @Themanwithoutaplan (already working on this)

[Themanwithoutaplan]

The output is row based so there has to be a conversion at some point from columns to rows. See https://bitbucket.org/snippets/openpyxl/jgbak for a barebones approach.

Will look to add support for Numpy types in a future version of openpyxl and using named styles for faster formatting.

[calvinwyoung]

Are there any plans to resolve this issue? We'd really love to be able to use constant_memory to output large XLSX files.

[Themanwithoutaplan]

@calvinwyoung this is possible in openpyxl using the dataframe_to_rows() function, which should also work with xlsxwriter. Having looked at the code for the Excelformatter I'm pretty sure that this will always be faster than using to_excel()

[jmcnamara]

@calvinwyoung One problem with using xlsxwriter's constant_memory mode from Pandas is that xlsxwriter doesn't support merged ranges across rows in that mode. As a result it would break the formatting for merged indices. If you specifically need constant_memory support you should probably look at writing the data from the dataframe yourself using xlsxwriter directly.

[calvinwyoung]

@Themanwithoutaplan @jmcnamara Thank you both for the feedback. It seems like neither dataframe_to_rows() nor constant_memory supports merged ranges across rows, but this is something I'd like to support if at all possible.

Is there a generally accepted strategy for supporting merged ranges and keeping memory consumption low? For our application, we're okay if the processing takes longer — it just so happens that the machines we're using are constrained by memory, and we aren't able to use swap.

[Themanwithoutaplan]

@calvinwyoung if you want to merge cells when writing straight to XML then you'll have to manage more of this yourself. Apart from the formatting it's not difficult. Details are out of scope for this ticket I think and should be discussed on the openpyxl mailing list.

[calvinwyoung]

Grea, thank you!

[mroeschke]

Seems like there's not been much development here and there doesn't seem to be a clear action item here so closing