FrontAccounting forum — Excel rows does not show overs 65536

Re: Excel rows does not show overs 65536

2017-06-10T05:14:18Z

@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.

Re: Excel rows does not show overs 65536

2017-06-08T05:59:59Z

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

/Joe

Re: Excel rows does not show overs 65536

2017-06-05T15:15:40Z

@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

2017-05-29T09:52:07Z

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

Re: Excel rows does not show overs 65536

2017-05-29T05:56:17Z

@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

2017-05-28T22:09:16Z

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

2017-05-28T15:48:50Z

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

2017-05-28T07:18:17Z

@apmuthu

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

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

2017-05-28T04:01:58Z

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.

Re: Excel rows does not show overs 65536

2017-05-27T14:24:50Z

@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

Re: Excel rows does not show overs 65536

2017-05-23T16:23:56Z

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

2017-05-21T21:53:56Z

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

2017-05-21T21:28:11Z

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

2017-05-21T18:21:42Z

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

2017-05-21T14:30:14Z

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