How to write a large dataset to an xls file?

Question

I have a really big database that I want to write to the xlsx / xls file. I already tried to use xlwt , but it only allows you to write 65536 rows (some of my tables have more than 72 thousand rows). I also found openpyxl , but it is too slow and uses a huge amount of memory for large spreadsheets. Are there any other options for writing excel files?

edit: Following kennym's advice, I used Optimized Reader and Writer. Now it consumes less memory, but still requires a lot of time. Export takes more than an hour (for really large tables - up to 10 ^ 6 rows). Are there any other options? Perhaps you can export the whole table from the HDF5 database to excel , instead of doing it line by line - as it is now, in my code?

Answer 1

Try using XlsxWriter in read-only mode.

• Only for writing Excel xlsx / xlsm Excel 2007 files
• Runs a lot faster than Openpyxl
• Provide persistent memory mode: http://xlsxwriter.readthedocs.org/working_with_memory.html

For .xls files, I'm afraid there is no memory-optimized way. Did you find anything?

Answer 2

Use the Optimized Reader and Writer of the openpyxl package. An optimized reader and writer is much faster and uses much less memory than standard openpyxl methods.

Answer 3

XlsxWriter works for me. I am trying openpyxl, but that is a mistake. 22k * 400 r * c