XLSX saving speed

[cwolcott]

Thank you, thank you, thank you. I have been using PhpSpreadsheet since 1.6.0 (Jan 2019). Current using 2.1.0 (May 2024). This library can do almost anything and trust me I have used a lot of features. The scientists that use the website are so happy that they can produce beautiful looking spreadsheets with a variety of their datasets just with a click of a button.

I am trying to understand if I can do anything different to speed up to creation of the current spreadsheet. Adding a couple of timing statements it is only taking 8 seconds to run through the code, retrieve the data from the database and produce the 4 worksheets, but 2 1/2 mins to save it. After saving the file size is 1.3 MB.

If there are any thoughts and questions please fire away. I am not seeing any issues in our apache logs. The current PHP memory_limit = 4086M

Worksheet 1: 10 rows 2 columns, basic data (No calculations), styling

Worksheet 2: 433 rows 7 columns, basic data (No calculations), styling and conditional formatting

Worksheet 3: 10 rows 3 columns, 8 normal formulas (AVG) and 8 array formulas (STDEV) against Worksheet 4, styling
This tab is actually created after Worksheet 4 since it uses the data.

Worksheet 4: 30,977 rows 8 column, basic data (No calculations), styling

I need to eventually add 2 more columns to worksheet 4.
Col 9 with reference 2 column in Worksheet 3 and 1 column in this worksheet
=(H2-VLOOKUP(F2,'SFP Calc'!A$3:B$10,2))/VLOOKUP(F2,'SFP Calc'!A$3:C$10,3)

Col 10 will just reference Col 9 to determine if the value is less than or greater than a value

[cwolcott]

Ok removing the code that began to calculate column 9 on worksheet 4 brought the save time down from 2.7 mins to 27 secs. So looking at the code I decide to only create the formula for the first row "I2".

        //-------------------------------------------------------------------=
        // Final Analysis of Extract Type tabs
        foreach ($extractTypes as $idx => $extractAbbr) {
            $worksheetName = $worksheetsAnalysis[$extractAbbr]['Worksheet'];
            $worksheetMaxRows = $worksheetsAnalysis[$extractAbbr]['MaxRows'];

            $colIdx1 = Coordinate::stringFromColumnIndex($idx * 4 + 1);
            $colIdx2 = Coordinate::stringFromColumnIndex($idx * 4 + 2);
            $colIdx3 = Coordinate::stringFromColumnIndex($idx * 4 + 3);

            $worksheet = $spreadsheet->getSheetByName($worksheetName);
            //$rowArray = [];
            //for($row = 2; $row <= $worksheetMaxRows; $row++) {
            for ($row = 2; $row <= 2; $row++) {
                //$rowArray[] = "=(H" . $row . "-VLOOKUP(F" . $row . ",'" . $worksheetSFPCalc . "'!" . $colIdx1 . "$3:" . $colIdx2 . "$10,2))/VLOOKUP(F" . $row . ",'" . $worksheetSFPCalc . "'!" . $colIdx1 . "$3:" . $colIdx3 . "$10,3)";
                $zScore = "=(H" . $row . "-VLOOKUP(F" . $row . ",'" . $worksheetSFPCalc . "'!" . $colIdx1 . "$3:" . $colIdx2 . "$10,2))/VLOOKUP(F" . $row . ",'" . $worksheetSFPCalc . "'!" . $colIdx1 . "$3:" . $colIdx3 . "$10,3)";
                $worksheet->setCellValue('I'.$row, $zScore);
            }
            //$worksheet->fromArray(array_chunk($rowArray, 1), NULL, 'I2');
        }

Just that 1 cell increased saving time from 27 secs to 2.7 mins. Adding the remaining 30,976 column "I" cells increased the save time by 30 secs.

So why that 1 cell caused the jump in saving time?

• Worksheet 3 (SPFCalc) references column D and F in worksheet 4.
• Worksheet 4 (Marine Analysis) reference columns A, B, C in worksheet 3
• There is no circular references but does the saving not like 2 worksheets referencing each other.

For fun I changed the "I2" formula to just a string value "ABC" and no increase in time, just 27 secs to save.

[cwolcott]

Will try to duplicate with a standalone example this weekend.

[MarkBaker]

A couple of suggestions that might help:

• Apply style settings to ranges of cells (e.g. column ranges) wherever possible.
• Disable precalculation when saving (unless you're using autosizing for columns)