Bug report: formula parsing in Spreadsheet::WriteExcel

[Ugo]

#!/usr/bin/env perl

# Bug report for Spreadsheed::WriteExcel

use warnings;
use strict;
use Spreadsheet::WriteExcel;

my $workbook=Spreadsheet::WriteExcel->new("bad_file.xls");

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

$sheet->write_col(0,0,[1 .. 10]);
$sheet->write_formula(0,1,3.14159);

$sheet->write_formula(1,1,'=MATCH(B1,A1:A10)'); # <-- Problem on cell
B2
# No problem if we use a number instead of a cell reference,
# as a first argument of the function MATCH(), as in this example:
# $sheet->write_formula(1,1,'=MATCH(3.14159,One!$A$1:$A$10)');

# The file bad_file.xls works fine on OpenOffice. If it is opened with
Excel
# (Excel 2003 on Windows XP) it contains the error message #VALUE! on
cell B2.
# When the formula in B2 is edited and recalculated, then it works
properly.

# Workaround: If I use the module Spreadsheet::WriteExcelXML the final
document
# works properly in Excel (but I cannot open it with OpenOffice).

__END__

Perl version : 5.010001 (ActiveState perl)
OS name : MSWin32 (same problem detected on Linux)
Module versions: (not all are required)
Spreadsheet::WriteExcel 2.25
Parse::RecDescent 1.962.2
File::Temp 0.22
OLE::Storage_Lite 0.18
IO::Stringy 2.110

[jmcnamara]

On Oct 21, 8:47 am, Ugo <u.tartagl...@googlemail.com> wrote:
> #!/usr/bin/env perl
>
> #   Bug report for Spreadsheed::WriteExcel

Hi,

This is a known (and admittedly frustrating) issue with the WriteExcel
formula parser. You can workaround it by massaging the parsed formula
as follows:

#!/usr/bin/perl

use warnings;
use strict;

use Spreadsheet::WriteExcel;

my $workbook = Spreadsheet::WriteExcel->new("bad_file3.xls");

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

$sheet->write_col( 0, 0, [ 1 .. 10 ] );

$sheet->write_formula( 0, 1, 3.14159 );

# Workaround for incorrectly parsed formula.
my $match_formula = $sheet->store_formula('=MATCH(B1,A1:A10)');
@$match_formula = map { s/_ref2d/_ref2dV/; $_ } @$match_formula;

$sheet->repeat_formula( 1, 1, $match_formula );



John.
--