Saving XLSX performance

[cwolcott]

Currently running version 5.4.0 and plan to upgrade to 5.7.0 tonight. I am a huge fan of this library and have been using it since 1.6.0 (Jan 2019). Started out with simple spreadsheets and getting more involved over the years.

I have had this curiosity for a long time why it seems to take so long to save xlsx spreadsheets via the library. I will say that I use a lot of the attributes to make the spreadsheet pretty and user friendly. If any one has thoughts I would be happy to try anything to speed up the process.

Attached is a spreadsheet produced by my code. I have reduced the amount of records being processed from 300,608 down to 16,192 records for this example. After saving it via PhpSpreadsheet the size of the file is 1,137,259 bytes.

RealLifeExample.xlsx

Below are some print statements to track the progress. The entire job took 1:36 to execute, of which 1:27 was spent saving the file.

2026-04-23 12:42:40 | JOB-0262 | Bob Smith | NPNPD SFP | {"libraryId":"112","libraryName":"Library 01 [Pre-frac Extracts]","assayId":"58","assayName":"MPM","extractTypes":["C","N"],"cutoff":["lte","-3",""],"worksheets":[]}
2026-04-23 12:42:40 | JOB-0262 | Source Plates prepare to retrieve
2026-04-23 12:42:41 | JOB-0262 | Source Plates retrieved - 432
2026-04-23 12:42:41 | JOB-0262 | Results prepare to retrieve
2026-04-23 12:42:41 | JOB-0262 | Results retrieved - 16192
2026-04-23 12:42:47 | JOB-0262 | TAB: SDP Calc (Begin)
2026-04-23 12:42:47 | JOB-0262 | TAB: SDP Calc (Finished)
2026-04-23 12:42:47 | JOB-0262 | TAB: Marine Analysis (Begin ZScore Calc)
2026-04-23 12:42:48 | JOB-0262 | TAB: Marine Analysis(Finished)
2026-04-23 12:42:48 | JOB-0262 | TAB: Plant Analysis (Begin ZScore Calc)
2026-04-23 12:42:49 | JOB-0262 | TAB: Plant Analysis(Finished)
2026-04-23 12:42:49 | JOB-0262 | Report saving xlsx
2026-04-23 12:44:16 | JOB-0262 | Report saved
2026-04-23 12:44:16 | JOB-0262 | Report prepare email
2026-04-23 12:44:16 | JOB-0262 | Report emailed
2026-04-23 12:44:16 | JOB-0262 | Completed - primaryScreeningNpnpdSfp.MPM.20260423.124240.xlsx

The file consists of 5 tabs, the 3rd tab is set to active so the user sees it first when the file is open.

Tab 1: Info

• Attributes used: borders, fill, align, font
• Other: setAutoSize, setZoomScale
• Note: The Parameter 1 attribute can be changed to set a different cutoff that affects tabs 4 and 5 and subsequent tab 3.

Tab 2: Source Plates

• Attributes used: borders, fill, align, font, setFormatCode
• Other: 5 Conditional Formatting definitions, setAutoSize, setAutoFilter, setZoomScale, SetSelectedCells

Tab 3: SFP Calc

• Attributes used: borders, fill, align, font
• Other: setAutoSize, setZoomScale
• Formulas: Column D and H have formulas reference the 4th and 5th tab

Tab 4: Marine Analysis

• Attributes used: borders, fill, align, font
• Other: 1 Conditional Formatting definition, setAutoSize, setAutoFilter, setZoomScale, SetSelectedCells
• Formulas: Column L has formulas that reference the 1st tab

Tab 5: Plant Analysis

• Attributes used: borders, fill, align, font
• Other: 1 Conditional Formatting definition, setAutoSize, setAutoFilter, setZoomScale, SetSelectedCells
• Formulas: Column L has formulas that reference the 1st tab

[oleibman]

Much as I hate to admit to deficiencies, I have never been able to get a usable performance profile of a PhpSpreadsheet job. I know there are users who have had much more success in this area than I, and hopefully one of those might be able to contribute to identifying the problem.

I will note that sheets 4 and 5 are huge compared to the rest of the file - over 3 million bytes (unpacked) and over 6 million, where the other 3 sheets combined don't even use 150,000. I don't know that these are extraordinarily large numbers.

Just out of curiosity, do you see similar performance when you try to save your spreadsheet in a different format - Xls, Ods, Html (specifying all sheets)?

Also, is there any performance effect if you use setPreCalculateFormulas(false)?

[cwolcott]

I will get back to you with those answers later today. I will also comment out a lot of the other attributes to try and make the spreadsheet as vanilla as it can be to see if there are any affects.

As I said this is just a small example with 16,192 records. The typical report with have over 300,000 records

The entire job took 30:29 to execute, of which 27:27 was spent saving the file.

2026-04-23 13:51:15 | JOB-0265 | Bob Smith | NPNPD SFP | {"libraryId":"112","libraryName":"NPNPD Rel01 Mixed [Pre-frac Extracts]","assayId":"58","assayName":"MPM","extractTypes":["C","N"],"cutoff":["outside","-3","3"],"worksheets":[]}
2026-04-23 13:51:15 | JOB-0265 | Source Plates prepare to retrieve
2026-04-23 13:51:15 | JOB-0265 | Source Plates retrieved - 432
2026-04-23 13:51:16 | JOB-0265 | Results prepare to retrieve
2026-04-23 13:51:22 | JOB-0265 | Results retrieved - 300608
2026-04-23 13:53:15 | JOB-0265 | TAB: SDP Calc (Begin)
2026-04-23 13:53:16 | JOB-0265 | TAB: SDP Calc (Finished)
2026-04-23 13:53:16 | JOB-0265 | TAB: Marine Analysis (Begin ZScore Calc)
2026-04-23 13:53:28 | JOB-0265 | TAB: Marine Analysis(Finished)
2026-04-23 13:53:28 | JOB-0265 | TAB: Plant Analysis (Begin ZScore Calc)
2026-04-23 13:54:08 | JOB-0265 | TAB: Plant Analysis(Finished)
2026-04-23 13:54:08 | JOB-0265 | Report saving xlsx
2026-04-23 14:21:35 | JOB-0265 | Report saved
2026-04-23 14:21:43 | JOB-0265 | Report prepare email
2026-04-23 14:21:44 | JOB-0265 | Report emailed
2026-04-23 14:21:44 | JOB-0265 | Completed - primaryScreeningNpnpdSfp.MPM.20260423.135115.xlsx

[cwolcott]

@oleibman quick update:

• Upgraded to release 5.7.0 (No performance differences)
• setPreCalculateFormulas(false) was already set as false
• Saving as xls instead of xlsx
  • took 40 secs longer (2:10 vs 1:28)
  • increased in size (4MB vs 1.1MB)
• Saving as ods instead of xlsx
  • took basically the same amount of time (1:31 vs 1:28)
  • barely decreased the file size (1MB vs 1.1MB)

I will now continue to remove various stylings to see if saving time changes.

[cwolcott]

Potentially found the cause of the poor performance when saving. I only removed one styling, setAutoSize(true) from tab 1, 2, 4 and 5 (Was not applied to tab 3). The save time decreased to 2 secs verses 1:28.

Here is the same file as in the first comment minus the setAutoSize(true). You can see the setAutoFiler is still set.
RealLifeExample.RemovedSetAutoSize.xlsx

I added a couple more prints before and after each tab code segments

2026-04-24 11:46:47 | JOB-0279 | Bob Smith | NPNPD SFP | {"libraryId":"112","libraryName":"NPNPD Rel01 Mixed [Pre-frac Extracts]","assayId":"58","assayName":"MPM","extractTypes":["C","N"],"cutoff":["lte","-3",""],"worksheets":[]}
2026-04-24 11:46:47 | JOB-0279 | TAB: Info (Begin)
2026-04-24 11:46:47 | JOB-0279 | TAB: Info (Finished)
2026-04-24 11:46:47 | JOB-0279 | TAB: Source Plates (Begin)
2026-04-24 11:46:47 | JOB-0279 | Source Plates prepare to retrieve
2026-04-24 11:46:47 | JOB-0279 | Source Plates retrieved - 432
2026-04-24 11:46:47 | JOB-0279 | TAB: Source Plates (Finished)
2026-04-24 11:46:47 | JOB-0279 | Results prepare to retrieve
2026-04-24 11:46:48 | JOB-0279 | Results retrieved - 16192
2026-04-24 11:46:48 | JOB-0279 | TAB: Marine Analysis (Begin)
2026-04-24 11:46:50 | JOB-0279 | TAB: Marine Analysis (Finish)
2026-04-24 11:46:50 | JOB-0279 | TAB: Plant Analysis (Begin)
2026-04-24 11:46:53 | JOB-0279 | TAB: Plant Analysis (Finish)
2026-04-24 11:46:53 | JOB-0279 | TAB: SDP Calc (Begin)
2026-04-24 11:46:53 | JOB-0279 | TAB: SDP Calc (Finished)
2026-04-24 11:46:53 | JOB-0279 | TAB: Marine Analysis (Begin ZScore Calc)
2026-04-24 11:46:54 | JOB-0279 | TAB: Marine Analysis(Finished)
2026-04-24 11:46:54 | JOB-0279 | TAB: Plant Analysis (Begin ZScore Calc)
2026-04-24 11:46:56 | JOB-0279 | TAB: Plant Analysis(Finished)
2026-04-24 11:46:56 | JOB-0279 | Report saving xlsx
2026-04-24 11:48:24 | JOB-0279 | Report saved
2026-04-24 11:48:24 | JOB-0279 | Report prepare email
2026-04-24 11:48:24 | JOB-0279 | Report emailed
2026-04-24 11:48:24 | JOB-0279 | Completed - primaryScreeningNpnpdSfp.MPM.20260424.114647.xlsx

All together I tested:

• Removed setAutoSize(true) only (save Time 2 secs)
• Removed setAutoSize(true) and setAutoFilter (save Time 2 secs)
• Removed setAutoFilter only (save Time 87 secs)

**So my assumption is that setAutoSize is expensive based on my implementation **

Note: For all the spreadsheets created I perform the following:

        // Use TrueType fonts, which will allow autosize columns to work with AutoFilter
        Font::setTrueTypeFontPath("/usr/share/fonts/msttcore/");
        Font::setAutoSizeMethod(Font::AUTOSIZE_METHOD_EXACT);

protected static function autoFilterSizing($worksheet, $spreadsheet){
   $maxCol = $worksheet->getHighestColumn();
   foreach (range('A',$maxCol) as $col) { $worksheet->getColumnDimension($col)->setAutoSize(true); }
}

Found a reference from 2022 by @MarkBaker #2750 (comment)

[oleibman]

Excellent detective work! Excellent reference!

[cwolcott]

Guess I will have to give up accuracy for performance. Arg I wish I can have both or at least accuracy with slightly slower performance not 2 secs to 87 secs.

Removed the following:

// Use TrueType fonts, which will allow autosize columns to work with AutoFilter
Font::setTrueTypeFontPath("/usr/share/fonts/msttcore/");
Font::setAutoSizeMethod(Font::AUTOSIZE_METHOD_EXACT);

Added back setAutoSize(true) and this is the final spreadsheet. The columns are basically wider than before which I can live with.
RealLifeExample.UseSetAutoSize.LessAccurate.xlsx

The final timings are:

• Using Font functions above and setAutoSize(true) - 87 secs
• Only using setAutoSize(true) - 10 secs
• Do not auto size columns - 2 secs

Keeping this open for a couple of days to see if there is any additional comments.

The full report processes 300,608 records to provide a summary for the analyst. It takes 5:56 to execute of which 3:02 is used to save the file, based on the reduced accuracy for autoSize.

[oleibman]

If you are running on Windows and have Excel, the following vbs script might be useful (and is easy enough to adapt to your needs):

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(WScript.Arguments(0))
objExcel.Visible = False
' Autofit all columns in the first sheet
objWorkbook.Sheets(1).Cells.EntireColumn.AutoFit
objWorkbook.Save
objWorkbook.Close
objExcel.Quit

Run it via:

cscript autofit.vbs spreadsheetname.xlsx

I think LibreOffice has a command-line equivalent, but I have not been able to get it to work:

soffice --headless "macro:///Standard.Module1.autofit_columns" spreadsheetname

[cwolcott]

Interesting thought, the users that I am building the spreadsheets for are great scientist in their field but I don't want them to think about things like this. I might look at adding a vba script to autofit the tabs when the excel spreadsheet opens. I would assume easy enough to look at your example and ask ChatGPT.

The PHP code produces the spreadsheet on a Linux VM and then emails the spreadsheet to the user who is using a Windows or MacBook Pro laptop.

---

Update: Oops, just noticed that PhpSpreadsheet doesn't support .xlsm files

[oleibman]

PhpSpreadsheet does support xlsm files in a limited manner. It can load and correctly save such a file. It can't modify the macros or add new ones, and, of course, it can discard all the macros. You might be able to start by creating a file with the macros you want, loading that file into PhpSpreadsheet, adding your spreadsheet data to it, and then saving. It's not anything I've ever attempted.

[cwolcott]

Will give it a try this weekend while watching EPL and EFL football matches.

[oleibman]

While studying your spreadsheet, a thought occurred to me about how performance might be improved. You have a lot of cells in each column which are duplicates of other cells in the column. For example, Cell Line on Marine Analysis has only 2 unique values (plus the header value). The column width routine nevertheless calculates a width for each cell in the column. It is possible that we can cache the values for which we've calculated a width and avoid doing that whenever we come upon a match. It isn't guaranteed to help - we need a 6-dimensional array for our cache, and array processing doesn't offer great performance in Php - but we may as well try to see if it helps.

But I have a problem. On my system, when I load and save your spreadsheet, it takes between 1-2 minutes to load, and 10-12 minutes to save even though the widths are already pre-calculated so auto-fit isn't used. This makes it very impractical for me to test. Even if I were to adjust all your column dimensions to indicate "recalculate auto-fit value", my best-case scenario almost certainly can't do any better than 10-12 minutes.

It seems that performance on your system is light-years better than mine. Might you be able to test against PR #4862 (the only module changed is Worksheet/Worksheet)? If there are problems with the rendering, I'll work on them. If there isn't a significant performance saving, I can just scrap it. But maybe you'll find that it's worth pursuing.

[cwolcott]

I will try to carve out sometime early this week a try it.