BUG: df.to_excel() with openpyxl engine doesn't use write-optimized mode, resulting in higher memory consumption

[Kharms]

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

---

Problem description

openpyxl has fairly high memory consumption by default. To address this for write-only operations, they recommend two things:

1. Install the lxml package, which is more optimized than the default xml parser.
2. Use the write_only mode on the workbook.

In pandas/pandas/io/excel/_openpyxl.py we have the a comment which states intent - # Create workbook object with default optimized_write=True.

However openpyxl depreciated the optimized_write option in version 2.4 and now recommends setting write_only=True on workbook creation. Pandas requires 2.6 so the result is that writes to excel use more memory than necessary. Switching to write_only=True has other ramifications, like only being able to write out once, so would need to be tested.

Proposed solution

1. Print out a warning about lxml being missing if writing to excel in an environment that doesn't have it installed.
2. Use the write_only mode if possible.

other context

Running on a GCP cloud function, I had a df = pd.DataFrame() of size ~150mb. df.to_excel() without lxml used >2gb memory, crashing the function. With lxml it used ~1.4gb memory, returning successfully but getting too close. Adding in write-only should lower that by an order of magnitude to <100mb.

[mzeitlin11]

Thanks for looking into this @Kharms. This all sounds quite reasonable, would you be interested in putting up a pr?

[jordanrmerrick]

I'm happy to take this on. The caveats of using lxml and write_only mode are:

1. Worksheets are not initialized automatically, we have to use create_sheet on the book.
2. Rows can only be appended, we can't go back to arbitrary locations to write data.
3. The book can only be saved once.
4. Everything that shows up in the file needs to be created before being added (like frozen cells).

The first doesn't seem to be an issue given that pandas already removes the sheets when it initializes a Workbook() object. Are there use cases that would break with no arbitrary cell writing, or only being able to save the book once?

[jordanrmerrick]

I've created a pull request as shown above. The main thing I'm concerned about is how it handles lxml not being present - excepting an ImportError and initializing a regular workbook. Further, right now there isn't an option for the user to disable write_only mode except by removing lxml from their environment. Should I add an option to disable it, and if so, at what level would be appropriate (e.g. at the lowest level of the OpenpyxlWriter class, or higher up at df.to_excel(write_only=True))?

[rmhowe425]

@mroeschke @jordanrmerrick

I would like to tackle this issue if we think that its still relevant and Jordan is no longer interested in taking it on.