Python: a quick way to write pandas DataFrame in Excel on multiple sheets

Question

I need to export 24 pandas data frames (140 columns of 400 rows) to Excel , each on a different sheet.

I use pandas built-in ExcelWriter . Performing 24 scenarios, it is required:

51 seconds to write to the .xls file (using xlwt )

86 seconds to write to the .xlsx file (using XlsxWriter )

141 seconds to write to the .xlsm file (using openpyxl )

21 seconds to run the program (without Excel output)

The problem with writing to .xls is that the table does not contain formatting styles, so if I open it in Excel, select the column and click the comma button to format the numbers, this tells me: "style comma not found. I I am not going to write this problem in .xlsx , but it is even slower.

Any suggestions on expediting exports? I may not be the first to have this problem, but after several hours of searching on forums and websites, I have not found a definite solution.

The only thing I can think of is to use Python to export to CSV files and then write an Excel macro to combine all the CSV files into one table.

The .xls file is 10 MB, and the .xlsx 5.2 MB

Thanks!

Answer 1

Here is a tag for different Python modules for Excel .

And here is the output for 140 columns of x (400 x 24) rows using the latest modules at the time of publication:

 Versions: python : 2.7.7 openpyxl : 2.0.5 pyexcelerate: 0.6.3 xlsxwriter : 0.5.7 xlwt : 0.7.5 Dimensions: Rows = 9600 (400 x 24) Cols = 140 Times: pyexcelerate : 11.85 xlwt : 17.64 xlsxwriter (optimised): 21.63 xlsxwriter : 26.76 openpyxl (optimised): 95.18 openpyxl : 119.29 

As with any benchmark, the results will depend on the versions of Python / module, CPU, RAM and disk I / O and the test itself. Therefore, be sure to check these results for your own customization.

Also, since you asked a question about Pandas, please note that PyExcelerate is not supported .

Answer 2

What is it worth, so I will format the output in xlwt. The documentation (or at least it was) is quite spotty, so I had to guess most of it!

 import xlwt style = xlwt.XFStyle() style.font.name = 'Courier' style.font.height = 180 style.num_format_str = '#,##0' # ws0 is a worksheet ws0.write( row, col, value, style ) 

In addition, I believe that I duplicated your error message when trying to format the resulting table in excel (version of Office 2010). This is strange, but some of the options for the drop-down list of tools work, and some do not. But it looks like they all work fine if I switch to “cell formatting” with a right-click.