ExcelWriter won't write any row but the last when used with XlsxWriter in constant_memory mode

[tilacog]

Hi,

I'm noticing a problem with the xlsx files created by ExcelWriter when the engine is set to "xlsxwriter" and options={'constant_memory': True}) kwarg is passed.

No exceptions are raised, but the resulting file will have all rows empty but the last one. Indexes are still written as usual, but all rows are blank (except for the last).

Code to reproduce this bug:

import numpy as np
import pandas as pd

df = pd.DataFrame(np.random.randn(4,4))
df
#            0         1         2         3
#  0  0.342306 -0.408126 -1.146106 -0.968315
#  1  1.046181  0.579583  0.137602 -1.181519
#  2  2.198670  0.420310  0.827958 -1.130635
#  3  0.887260  0.985287 -0.349063  1.277966

writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter', options={'constant_memory': True})
df.to_excel(writer)
writer.save()

As mentioned, the result is a mostly empty Excel "table":

pd.read_excel('test.xlsx')
#            0         1         2         3
#  0       NaN       NaN       NaN       NaN
#  1       NaN       NaN       NaN       NaN
#  2       NaN       NaN       NaN       NaN
#  3  0.887260  0.985287 -0.349063  1.277966

I'm using:
pandas==0.17.0
XlsxWriter==0.7.0

[jreback]

not clear this is a pandas issue. pls trace whether the options are actually passed to xlsxwriter. you might want to raise an issue there as well (and link to here).

[chris-b1]

This is because constant_memory only works row-oriented, while the current impl is column oriented. Most likely could fixed by #11355

[jreback]

ok closing then