Excel::Writer::XLSX set_optimization() only writes numeric data

Question

I am using the Excel::Writer::XLSX perl module to export to Excel. It is working great except large exports take a long time. However, when I add $workbook->set_optimization(); I only get numeric data in my .xlsx file.

I have tried using both $worksheet->write() and $worksheet->write_string() but any fields that have a string rather than a number are being left out of the export when I use $workbook->set_optimization();

If I use the older Spreadsheet::WriteExcel module it works just fine but the file size of the export is substantially larger, 24MB vs. 7MB

Here is the code for creating the header row:

# Create a new workbook
my $workbook = Excel::Writer::XLSX->new($file_path);

$workbook->set_optimization();

my $worksheet = $workbook->add_worksheet();
# insert the header row
my $header_cnt = 0;
foreach my $header_cell (@header_values){#loop through each header column and add it to the sheet
    # print $header_cell." - ".$header_cnt."\n";
    # $worksheet->write_string("0", $header_cnt, "test");
    $worksheet->write_string("0", $header_cnt, $header_cell);
    $header_cnt++;
}

$workbook->close() or die "Error closing file: $!";

Answer 1

There shouldn't be a problem writing numbers or strings in set_optimization() mode. In fact there are several test cases just like that.

I ran a variant of your program with some sample data and the output showed up correctly.

#!/usr/bin/perl -w

use strict;
use Excel::Writer::XLSX;

my $workbook = Excel::Writer::XLSX->new('test.xlsx');
$workbook->set_optimization();

my $worksheet = $workbook->add_worksheet();


my @header_values = ( 1, 2, 3, 'foo', 'bar', 6, 7 );
my $header_cnt    = 0;

for my $header_cell (@header_values){
    $worksheet->write(0, $header_cnt, $header_cell);
    $header_cnt++;
}

$workbook->close();

Note that the strings are aligned to the left and the numbers to the right which indicates that Excel is treating them as string and numeric data.

Also, if you use the write_string() method then you are going to get a string in either case.

The issue that you are encountering may be that in optimization mode you need to write data in row-column order, i.e., each row and each column in that row must be written sequentially.

For example, if you added these lines after the for loop the second data won't show up in optimization mode:

$worksheet->write('A2', "This is fine");
$worksheet->write('A1', "I'm too late.");