Optimizing Excel Report Generation: From OpenPyXL to XLSMerge — Processing 52 Columns, 200K Rows from 9 Minutes to Just 3 Minutes

Turbocharge Your Excel Reports: From 9 Minutes to Just 3!

Introduction

Generating large Excel reports efficiently is critical for data-driven applications. We encountered a requirement to develop an Excel report with 52 columns and 200,000 rows, ensuring completion within 5 minutes. However, our initial openpyxl approach took approximately 9 minutes, beyond the acceptable threshold.

We experimented with XlsxWriter, a more memory-efficient library for writing Excel files to optimize performance. The switch resulted in a drastic reduction in execution time to just 3 minutes, significantly improving efficiency.

The Challenge

Our Python script was designed to:

• Fetch data from multiple database tables.
• Perform real-time calculations for several columns.
• Write the processed data in an Excel file.

However, using openpyxl for this process was highly inefficient. It loaded the entire dataset into memory before writing, causing high memory usage and slow execution, making it impractical for large datasets.

OpenPyXL Implementation and Its Limitations

Code Snippet: OpenPyXL Approach

Requires Python >=3.6

from openpyxl import Workbook
import datetime

wb = Workbook()
ws = wb.active
# Adding headers
excelHeader = [exlData for exlData in excl_merged]
ws.append(excelHeader)
# Writing data
for rowCounterToStyleDate, value in enumerate(dataFrameValues, start=1):
    ws.append(value)
    ws[f'A{rowCounterToStyleDate}'].style = newStyle
# Generating the file name
filename_timestamp = datetime.datetime.now().strftime('%Y-%m-%d-%H-%M')
merged_file_name = f"{show_date}_production_merged_{meridian_formatted.lower()}_{kitchen_name_str}_{filename_timestamp}.xlsx"
wb.save(merged_file_name)
print(f"XLSX Merge Complete => {datetime.datetime.now().strftime('%H:%M:%S')}")

Performance Issues with OpenPyXL

• Execution Time: 9 minutes for 200,000 rows.
• Memory Usage: High, as a result of keeping the dataset in memory before writing
• Scalability Issues: Struggles with larger datasets.

The Solution: Switching to XlsxWriter

To improve efficiency, we replaced OpenPyXL with XlsxWriter, which streams data directly to the file, eliminating excessive memory consumption.

Code Snippet: Optimized XlsxWriter Approach

Requires Python >=3.8

import xlsxwriter
import datetime

# Generating the file name
filename_timestamp = datetime.datetime.now().strftime('%Y-%m-%d-%H-%M')
merged_file_name = f"{show_date}_production_merged_{meridian_formatted.lower()}_{kitchen_name_str}_{filename_timestamp}.xlsx"
workbook = xlsxwriter.Workbook(merged_file_name)
worksheet = workbook.add_worksheet("Sheet")
# Adding headers
worksheet.write_row(0, 0, excl_merged)
# Writing data efficiently
date_format = workbook.add_format({'num_format': 'dd/mm/yyyy'})
for row_num, row_data in enumerate(dataFrameValues, start=1):
    worksheet.write_row(row_num, 0, [handle_nan(value) for value in row_data])
    worksheet.write_datetime(row_num, 0, row_data[0], date_format)
workbook.close()
print(f"XLSX Merge Complete => {datetime.datetime.now().strftime('%H:%M:%S')}")

Why XlsxWriter?

✅ Speed Optimization: Writes Excel files significantly faster than OpenPyXL.

✅ Memory Efficiency: Streams data to the file instead of keeping it in memory.

✅ Handles Large Data Efficiently: Processes large datasets seamlessly.

Results After Optimization

By switching to XlsxWriter, we achieved:

• Execution Time Reduction: From 9 minutes to 3 minutes.
• Efficient Memory Usage: Eliminated excessive memory consumption.
• Seamless Handling of Large Datasets: Successfully processed 52 columns and 200,000 rows.

Conclusion

If you need to generate large Excel reports in Python, consider replacing OpenPyXL with XlsxWriter for significant performance improvements. OpenPyXL remains a great choice for modifying existing files, but for efficiently writing large datasets, XlsxWriter is the best option.

OpenPyXL vs XlsxWriter: A Quick Comparison

Contributors

This article was written by Debasish Nandi (Senior Developer) and Riju Das (Technical Lead), who specialize in optimizing large-scale data processing and automation solutions.

Get in touch with MSS to optimize and scale your application.

Have you faced similar challenges in handling large Excel reports? Share your experiences and solutions in the comments below!