to_excel cannot handle large sheet

[Pzoom522]

Problem description

For extremely large sheets (row_num > 1048576, col_num > 16384), see the code of XlsxWriter Engine, area which exceeds the limit won't be printed.
However, no error or warning is raised.

Expected Output

This sheet is too large! Area off (1048576, 1638) will not be printed.

Output of pd.show_versions()

latest (v0.24.2)

[chris-b1]

A little strange that nothing happens on the xlsxwriter side, but yes would definitely take a PR that guards and raises on our end

[WillAyd]

I think this makes more sense as an enhancement request for xlsxwriter that would just pass through here rather than doing it on our end

[anordin95]

I'm new to pandas-dev. Does the closed tag on the feature request I see above indicate the work is already done and this entire issue should be closed?

[chris-b1]

I think we could handle on the pandas side - Xlsxwriter's interface is essentially cell-based - there isn't a way to know in advance if those limits are going to be broken. Because pandas know the total table size in advance, I think it makes sense to check and raise.

[anordin95]

I agree @chris-b1. I assume this hasn't been addressed, but I'm uncertain due to the red "Closed" indicator I see beside the feature request above my original comment.

[chris-b1]

That closed is from the linked issue in the xlsxwriter repo

[anordin95]

Ah, makes sense. Thank you!

[anordin95]

Going to raise a ValueError unless anyone has a better suggestion.

[anordin95]

I believe xlsx writer hangs when passed a sheet that is too large. I test whether calling df.to_excel raises a ValueError when given a sheet that is too large. In that case, the test passes. However, if too large of a sheet is passed, and the code to check input size is not there, the to_excel call will hang resulting in the test stalling. Any ideas on how to best proceed? I believe the best course would be to ignore testing the new checking logic, because adding the test I previously described would just add unhelpful code.

[anordin95]

Solved.

[anubhav0fnu]

@anordin95, it still fails,

  File "//anaconda3/envs/proteomics/lib/python3.7/site-packages/pandas/core/generic.py", line 2180, in to_excel
    engine=engine,
  File "//anaconda3/envs/proteomics/lib/python3.7/site-packages/pandas/io/formats/excel.py", line 719, in write
    f"This sheet is too large! Your sheet size is: {num_rows}, {num_cols} "
ValueError: This sheet is too large! Your sheet size is: 1209019, 51 Max sheet size is: 1048576, 16384

Any suggestions on increasing the limit to say in python sys.maxsize? so that it should only fails once the system's m/m get's bloated! this way only User has to handle that and not the to_excel().?

[anordin95]

This looks like it should fail. The maximum sheet size in excel is being exceeded. You have ~1.2M rows and excel can only handle ~1M.

https://support.office.com/en-us/article/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3

I'm not sure what m/m means. Writing your data to another format may better suit your usage, such as hdf.

[Pzoom522]

Had the UK's public health system seen this issue...
https://www.bbc.co.uk/news/uk-54422505

[ghost]

is there a way to auto add new sheet in pandas.ExcelWriter if a sheet reaches maximum limit?