Topic: Excel rows does not show overs 65536

I am using FrontAccountion version 1.1.0. Some of my report has over 65536 rows. But if i export report in excel format, it does not show over 65536 rows. Because FrontAccountion use Excel-97-2003.   Excel-97-2003 does not support over 65536 rows.
I have downloaded latest version of  FrontAccountion. But latest version also use Excel-97-2003 for export.

Now how can i solved my problem. If any idea please help me.

Thanks
Nashir Uddin

Re: Excel rows does not show overs 65536

FrontAccounting v1.1.0?
Where did you get it from?

Try using some filter to reduce the rows.

Alternatively try to generate the PDF first and then use some program / service to convert it to Excel 2007 format.

@joe: Should we not generate the xlsx (Excel 2007) format if there are more than 65535 rows?

Re: Excel rows does not show overs 65536

I don't know how to do this. Maybe we could wiki it until someone can help. Will you apmuthu, please?

Re: Excel rows does not show overs 65536

For now let is put a check for > 65535 lines and a appendage to the file name that it is truncated.
We use the reporting/includes/Workbook.php's BIFF Writer format that is probably the oldest and most compatible across spreadsheet applications.

$maxsize = 7087104;

The above limits filesize in Workbook.php.

Is there a way to flow the balance to new worksheets?
Or better still output a set of excel sheets sequentially numbered and zip them up....

The original PEAR package that has this file with all inclusions expanded out is at:
https://github.com/pear/Spreadsheet_Excel_Writer

http://hotexamples.com/examples/-/Spreadsheet_Excel_Writer_BIFFwriter/-/php-spreadsheet_excel_writer_biffwriter-class-examples.html

https://fossies.org/dox/SimpleGroupware_0.745/classSpreadsheet__Excel__Writer__BIFFwriter.html

Other Excel Libraries:
C++ - https://www.codeproject.com/Articles/42504/ExcelFormat-Library
PHP - https://github.com/PHPOffice/PHPExcel

Re: Excel rows does not show overs 65536

There is a catch 22 effect in counting the lines. The report Title is printed before the eventual counting of the printed lines could be done.

I guess it is quite rare to have that many rows in a report  If we have, say 60 rows per page, it will result in over 800 pages!

I guess it is better to just wiki this as I said before and let some skilled programmer fix this during the 2.4 series.

/Joe

Re: Excel rows does not show overs 65536

I found a PHP_XLSXWriter class here

https://github.com/mk-j/PHP_XLSXWriter

I will check if this class can be used. It handles 1048576 rows.

If it is ok, I will implement it during the 2.4 series. Maybe already the 2.4.2 release.

Joe

Re: Excel rows does not show overs 65536

Since the existing Workbook.php is small and provides for small sized old formal xls  files, let us implement the xlsx type format using any later excel library only for those reports that are over say 60K lines. Worksheets in the current instance need not be provided for each page. A new worksheet for every 60K lines may be the way forward without changing the library. This way both branches can benefit.

Re: Excel rows does not show overs 65536

@nashirbadu

I have included 2 files for testing of large files with more than 65535 rows. These files are only for testing, so DO NOT use them in production yet.

Will you download the files, rename Workbook.php1 to Workbook.php and replace the files in /reporting/includes folder.
Backup your original files during testing.

These files work with both small and large files with over 65530 rows. I have tested with 100000 rows without problem.
It will take some time with 100000 rows, about 2-3 minutes or more.
It works the way that when reaching the max row files, 65530, a new sheet is established and the rows roll over to this new sheet and so forth.

Please tell me if the files are ok.

In the file, excel_report.inc up at the top there is a define('MAX_ROW_SHEET', 65530);
You can, for testing, use a lower value, say 25,  and see the changing to a new sheet.

/Joe

Post's attachments

excel_report.inc 22.49 kb, 3 downloads since 2017-05-27 

Workbook.php1 288.36 kb, 3 downloads since 2017-05-27 

You don't have the permssions to download the attachments of this post.

Re: Excel rows does not show overs 65536

Is there something wrong with the function ymd2Date($year, $mon, $day) in excel_report.inc?

Try to echo ymd2Date(1985, 2, 24); and we get 31102.
Now check it out at Wolfram Alpha and we get 26th Feb 1985.

Excellent work @joe porting the PEAR/Perl code.
Further notes.

The files in @joe's post are for the FA 2.4.1 version and can be ported to v2.3.x as well.
Those who wish to test on FA 2.3.x can do so with the files in the attachment here.

Post's attachments

LargeExcelReports_FA23x.zip 57.83 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.

Re: Excel rows does not show overs 65536

@apmuthu

No, the dates are correctly displayed in the excel report, and that is the main issue smile

And yes, it is possible to backport the files to 2.3.x, however we are no longer maintaining this version, so you have to do it yourself.

But just wait a while to hear if the files are ok for shipping!

/Joe

Re: Excel rows does not show overs 65536

Many large users are still on FA 2.3.x and they would have the data to test this specific change for both the max lines and the time to generate the report and to see if there is any way to optimise the New Line check routine. An array of arrays that holds the segmented sets of records based on the max rows may be implemented to avoid checking on each new line that wastes time.

Re: Excel rows does not show overs 65536

The implemented algorithm is the fastest way of doing it. In C/C++ these check are internally converted to assembly instructions of one line.

Still, I am waiting for test result before committing it to 2.4. And as told, it is possible to use the files in 2.3.X also as the code are the same in both releases and files. Only you will have to replace the files yourself.

/Joe

Re: Excel rows does not show overs 65536

@Joe
Thanks for you replay. I will test with your two  new attache file. I

Thanks,
Nasir

Re: Excel rows does not show overs 65536

My Post #9 here has the FA 2.3.x version attached.

Re: Excel rows does not show overs 65536

@nashirbadu

Did you get the time to test these 2 files. It would be nice to commit them asap.

/Joe

Re: Excel rows does not show overs 65536

I think I will commit these files now. I have tested them without any problems.

/Joe

Re: Excel rows does not show overs 65536

@joe: Thanks for the commit in the 2.4.x branch. Those who want it for the v2.3.x branch can take it from the 9th post in this thread.