Bug 21560: report ODS export optimization

SQL reports can be exported with CSV, TSV of ODS format.
When report has thousands of rows, using ODS format is around 10 times longer than other formats.
It also loads CPU and RAM a lot.

The longest call is expandTable().
I found that the call on OpenOffice-OODoc lib can be optimized.
Based on https://grep.metacpan.org/search?qci=&q=expandTable&qft=&qd=OpenOffice-OODoc&f=examples%2Ftext2table

Test plan :
1) Don't apply patch yet
2) Create a new SQL report : Home > Reports > Create from SQL
3) Enter a SQL that will return thousands of results
4) Run report
5) Click Download > Semicolon separated text (.csv), look execution time
6) Click Download > Open Document Spreadsheet, look execution time
7) Apply patch
8) Redo 5) and 6) and compare times, CSV export should be the same but ODS export should be better

My tests shows 1,5 seconds for CSV export.
And for ODS export : 18 seconds without patch and 8 seconds with patch.

Signed-off-by: David Nind <david@davidnind.com>
Signed-off-by: Chris Cormack <chrisc@catalyst.net.nz>

Signed-off-by: David Nind <david@davidnind.com>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
(cherry picked from commit 28edd8848320fd0f0f284245f2f0567a5907619a)
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
(cherry picked from commit 0014f64348ea171300afce604cd7ec4f9a0444a7)

Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
(cherry picked from commit ba64a98d076f46e9355818efc278ed3c531d0e00)
Signed-off-by: Fridolin Somers <fridolin.somers@biblibre.com>

diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl
index 67d28c9..3f959bd 100755 (executable)
--- a/reports/guided_reports.pl
+++ b/reports/guided_reports.pl
@@ -871,37 +871,41 @@ elsif ($phase eq 'Export'){
                 my $ods_fh = File::Temp->new( UNLINK => 0 );
                 my $ods_filepath = $ods_fh->filename;
 
+                # Create document
                 use OpenOffice::OODoc;
                 my $tmpdir = dirname $ods_filepath;
                 odfWorkingDirectory( $tmpdir );
-                my $container = odfContainer( $ods_filepath, create => 'spreadsheet' );
-                my $doc = odfDocument (
-                    container => $container,
-                    part      => 'content'
-                );
-                my $table = $doc->getTable(0);
+                my $doc = odfDocument( file => $ods_filepath, create => 'spreadsheet' );
+
+                # Prepare sheet
                 my @headers = header_cell_values( $sth );
                 my $rows = $sth->fetchall_arrayref();
                 my ( $nb_rows, $nb_cols ) = ( 0, 0 );
                 $nb_rows = @$rows;
                 $nb_cols = @headers;
-                $doc->expandTable( $table, $nb_rows + 1, $nb_cols );
+                my $sheet = $doc->expandTable( 0, $nb_rows + 1, $nb_cols );
+                my @rows = $doc->getTableRows($sheet);
 
-                my $row = $doc->getRow( $table, 0 );
+                # Write headers row
+                my $row = $rows[0];
                 my $j = 0;
                 for my $header ( @headers ) {
                     $doc->cellValue( $row, $j, $header );
                     $j++;
                 }
+
+                # Write all rows
                 my $i = 1;
                 for ( @$rows ) {
-                    $row = $doc->getRow( $table, $i );
+                    $row = $rows[$i];
                     for ( my $j = 0 ; $j < $nb_cols ; $j++ ) {
                         my $value = Encode::encode( 'UTF8', $rows->[$i - 1][$j] );
                         $doc->cellValue( $row, $j, $value );
                     }
                     $i++;
                 }
+
+                # Done
                 $doc->save();
                 binmode(STDOUT);
                 open $ods_fh, '<', $ods_filepath;