Hello PerlMonks

After a year far from Perl, I've been drawn to it once again, and for the first time in a while, I've stumbled upon something I can't google my way out.

Here's the context: the current script I use for work creates a csv file, which is subsequently pasted into a worksheet, in an xlsx file. This works fine, but this week-end I decided to improve it, and to create the Excel file from scratch via the script, removing the human manipulation inbetween. It almost works, barring one detail: some formulas (Every formula referring to cells in another worksheet.) disappear when Microsoft Excel opens the file, with a not-really-helpful error message. The formulas are correct : they are exactly the same I used manually before, and if I input them myself in the newly created file, everything works.

As it may be obvious, I can't fully assert this is a Perl problem, but this is the track I want to follow first.

Below is a shortest-I-could version of the code I'm using (With random data and no formats, but the exact same structure regarding the problematic formula), which generates a file with the same error message on loading.

#!/usr/bin/env perl

use v5.010;
use warnings;
use strict;

use Excel::Writer::XLSX;

my $out = 'testfile';
my $excel = Excel::Writer::XLSX->new("$out.xlsx");

my $README = $excel->add_worksheet('README');
my $log_table = $excel->add_worksheet($out);

# Log table ___________________
$log_table->write_row('A1', ['ColA', 'ColB', 'ColC', 'ColD', 'ColE']);

sub randChar { @_ = qw/A B C D E/; return $_[(int rand 4)]; }

my $current_line = 2;
for (0 .. 9) {
    $log_table->write_row("A$current_line", [randChar(), randChar(), r
+andChar(), randChar(), lc randChar()]);
    $current_line++;
}
$log_table->autofilter("A1:K$current_line");

# README ___________________
$current_line = 1;
for (qw/a b c d e/) {
    $README->write_row("A$current_line", [$_, "=NB.SI('$out'!E:E;A$cur
+rent_line)"]);
    $current_line++;
}

$excel->close();
[download]

Has anyone ever encountered the same error and found how to fix it? I've been browsing the Excel doc, then the Excel::Writer::XLSX module doc, but I can't find anything related, and I prefer to ask for opinions before exploring in-depth how the module creates the files.. (There is no hurry, but I don't like leaving a script unfinished.)

(If relevant, this has only be tested in Windows 10 OS, since it's supposed to eventually run on a Windows server, I haven't tried it in a Linux OS yet.)

Thanks in advance for any help. \o/