set_optimization() and limitation with tables

[SebMourlhou]

I would like to use the Workbook set_optimization() method to reduce the memory usage because of the large amount of data in specific extractions. I have to use tables in this worksheets but, by now, tables don't work when set_optimization() mode in on.

I've seen the comment in Worksheet add_table() method : "We would need to order the write statements very carefully within this function to support optimisation mode." To try a fix, I disabled locally the limitation (commented the carp and return) and everything seems to work well : the tables are correctly added, the result is the same and above all, the memory usage is hugely reduced.

Example of use (tried also without the parameter "data", the table data being written separately) :

$worksheet->add_table(0, 0, scalar(@$data), scalar(@$headers)-1, {
  name       => $sheet_name,
  data       => $data,
  columns    => [ map { {header => $_} } @$headers ],
  autofilter => 1,
});

Is there still a reason to keep this limitation ?

[jmcnamara]

Is there still a reason to keep this limitation ?

The main reason is that this workaround only works in limited situations where the data can be written in row/column order.

If it works for you then you can disable the check in the code or try something like this without modifying the module (untested):

$worksheet->{_optimization} = 0; 

# Add table without data or formula parameters:
$worksheet->add_table(...);
$worksheet->{_optimization} = 1;

# Write the table data in row/column order with write():
$worksheet->write(...);

But overall this is a can't/won't fix.

[SebMourlhou]

Thanks, it works !

To avoid a warning opening the Excel file, I have to pass the parameter "columns" to add_table() AND write the headers before the data.

# Add table without data
{
  local $worksheet->{_optimization} = 0;
  $worksheet->add_table(0, 0, scalar(@$data), scalar(@$headers)-1, {
    name    => $sheet_name,
    columns => [ map { { header => $_ } } @$headers],
  });
}

# Write the table headers and data
my $n_row = 0;
$worksheet->write_row($n_row++, 0, $headers );
foreach my $row (@$data) {
  my $row_data = [ @{$row}{@$headers} ];
  $worksheet->write_row($n_row++, 0, $row_data );
}