Time to build excel grows exponentialy

[berberon]

This is:

- [X ] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

Time to create excel should be liniar in relation to the number of records.

What is the current behavior?

Time to create excel grows exponentially in relation to the number of records. With each new record, the time it takes to loop the relevant lines of code grows exponentially.

What are the steps to reproduce?

I noticed that an excel that takes 5 minutes to create with the old php excel takes over 12 hours to create on my new setup. I started adding microtime at numerous places in the code untill i narrowed the problem down to these two lines :

$objPHPExcel->getActiveSheet()->getStyle('L' , $i)->getAlignment()->setReadorder(Alignment::READORDER_RTL);
$objPHPExcel->getActiveSheet()->getStyle('L' , $i)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);

These lines run in a loop with other code but the time to run them starts with 250-300 microseconds (one loop). After 900 loops its already 10000 microseconds to run only these two lines once. After 2000 loops it's 21000 mirsoseconds to run only these two lines once. After 2500 loops... 50000 microseconds to run only these two lines once. After 5000 loops... 100000 microseconds to run only these two lines once. and it grows exponentially.

all other areas in the code take the same time to run.

Just commenting out the above two lines and an excel with 150K lines takes 4.82 minutes to create instead of over 12 hours.

It's as if with each loop of these two lines, something in PhpSpreadsheet runs code in an internal loop which coresponds to the number of loops already run.

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?php

require __DIR__ . '/vendor/autoload.php';

// Create new Spreadsheet object
$objPHPExcel = new Spreadsheet();

// add code that show the issue here...
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('L' . $i, $SaleDescription);
$objPHPExcel->getActiveSheet()->getStyle('L' , $i)->getAlignment()->setReadorder(Alignment::READORDER_RTL);
$objPHPExcel->getActiveSheet()->getStyle('L' , $i)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);

If this is an issue with reading a specific spreadsheet file, then it may be appropriate to provide a sample file that demonstrates the problem; but please keep it as small as possible, and sanitize any confidential information before uploading.

What features do you think are causing the issue

• [ ] Reader
• [ ] Writer
• [X ] Styles
• [ ] Data Validations
• [ ] Formula Calculations
• [ ] Charts
• [ ] AutoFilter
• [ ] Form Elements

Does an issue affect all spreadsheet file formats? If not, which formats are affected?

Which versions of PhpSpreadsheet and PHP are affected?

PHP 8.3.7 PhpSpreadsheet 2.1.0 Also PhpSpreadsheet 2.0.0

[oleibman]

It looks like you are trying to use using comma instead of period as your concatenation operator when calling getStyle below:

$objPHPExcel->getActiveSheet()->getStyle('L' , $i)->getAlignment()->setReadorder(Alignment::READORDER_RTL);
$objPHPExcel->getActiveSheet()->getStyle('L' , $i)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);

Correct that and see if your problem persists.

[berberon]

WOW, No idea how i missed that. I have been wondering how this is not affecting dosens of other scripts. Seems that with PHP 7.4.33 and PhpSpreadsheet 1.29.0 this works well but with PHP 8.3.7 and PhpSpreadsheet ^2.0 this is a problem.

Sorry for taking your time and thanks for the help.

[MarkBaker]

Also, if you're setting style for a number of cells individually (e.g. L1, L2, L3, L4, L5), and those cells can be expressed as a range, make a single call to getStyle() that passes the range (L1:L5).

[berberon]

I always wondered How much of a difference / importance it is to do something like :

run this once at the end : $highestRow = $objPHPExcel->getActiveSheet()->getHighestRow(); $objPHPExcel->getActiveSheet()->getStyle('L1:L' . $highestRow)->getAlignment()->setReadorder(Alignment::READORDER_RTL);

rather than applying for each row? $objPHPExcel->getActiveSheet()->getStyle('L' , $i)->getAlignment()->setReadorder(Alignment::READORDER_RTL);