Create Excel reports with phpSpretadsheet (update)

Great job as always from you.
Regarding phspreadsheet speed this is the nature of the beast - it is good for small sets of data but you you have for example 10 000 records to export then you simply can't use it....
One solution is to increase memory limit in php.ini but this is not the recommended way and even that won't suffice.
I use for big projects Spout (https://opensource.box.com/spout/) even if it is no longer mainted and it is insanely fast.
Only two features:

• needs only 3MB of memory to process any file.
• streaming mechanism incredibly fast.

A big minus for it: it is not supporting those fancy styles that phpdpreadsheet does.

I also found this - FastExcelWriter (https://github.com/aVadim483/fast-excel-writer) and verifying the claims by the author I can say that it is faster than phpspreadshet but I would still not recommend it for large ammounts of data:

7-9 times faster
uses less memory by 8-10 times
supports writing huge 100K+ row spreadsheet

Fernando and cristi,

thank you for chiming in. It is good to know that there are faster alterbative available. We have choosen phpSpreadSheet because it seems to be the most widely used solution and the most comprehensive, functionality wise. It is good to know that if we need to implement some new feature, phpSpreadSheet is likely to support it. However, functionality often comes with the price, which is performance in this case.

I must add one thing. If you intend to export huge amounts of data, CSV is probably the best option. Excel is better for presentation purposes, not for a huge datasets transfer. After all, web applications aim to solve the exact issue that Excel creates, you do not want to pass bunch of data around.

Hi @cristi.

In the first version of the article, my example relied on the use of insertion of new "Rows" and problems were observed when the number of rows was large and made rows insertion operations into the spreadsheet.

Except for this case, the performance of the proposed solution was very good.

I studied the problem and I have seen that instead of using row insertion to drag the styles of that row, if the styles are copied, the time effect disappears and the result is very good. In the example, 192 pages take 13 seconds on a normal PC.

This solution is functionally very good and you just have to take into account this problem in large spreadsheets.

Between that I write in Spanish and translate from English to Spanish, I must express myself badly, because what I wanted to express in this article has not been understood.

Thanks for your words and for the references you have shared.

Greetings,
fernando

Hi @Sergey.

How I have explained to @cristi, if you have not understood what I wanted to explain in the article it is because I translate very badly to English or my text I was not sufficiently explained.

What I wanted to indicate is:

• Digital reports, at least all for internal use of a company, should not be PDF. It is much more appropriate to make them in spreadsheets (Excel), since all administrative users know how to use and exploit the data in this tool. The user can express their reports, perfectly with examples in Excel.

  
  

• Phprunner does not have a powerful solution to make reports and the excels that produc, from my point of view, it cannot be called reports.

  
  

• Office/phpspretadsheets, it is an excellent product in functionality and redness. In the first version of the article and the examples, I used the "rows" insertion technique to drag the style of the rows and columns and that operation, in addition to copying styles produces the review of the form of the sheet and when It requires this formula review, we must use the copy functionality of styles, because the resources used is very lower. This is not appreciated in small spreadsheets but it is a large spreadsheets calculation problems.

  
  

I hope now what I wanted to express is clearer and if not, I will try again.

Regarding the CSV format, I can agree that it is a fast information output, but I would never consider it a user report of an application.

Greetings,
fernando

@admin - CSV format was created mainly for machines - reading csv is perfectly possible but it is not a pleasant experience for humans...
I agree with fernando that PHPRunner needs a better approach for reports.
I am an avid user of PHPRunner since version 5 and I love it but the reporting...I always did that with external code and custom files because this is the weak point of PHPRunner (the only one in my opinion and maybe not even that - the reporting part is so custom for people needs that should just be left to custom coding ) and I salute the work of fernando that shows there are a lot of possibilities to have elegant, detailed reports using custom code and external free libraries...
About phpspreadsheet I like it but it is quite avid on server resources...in my opinion there are better implementations out there, maybe not the same array of features like phpspreadsheet but maybe you don't need some of them...
@fernando - you hit the nail on the head with the styles and phpspreedshet - they seem to create the biggest impact on performance - great job! - I was just pointing out that there may be better (in my opinion) implementations out there. Your examples could be easily replicated using those libraries for who wants to experiment.

Thank you very much @cristi