PERL Miscellaneous - Re: Spreadsheet::WriteExcel, Excel formula won't calculate

Author

Re: Spreadsheet::WriteExcel, Excel formula won't calculate

Jon Hairr

2004-07-28, 9:01 pm

I would be interested in the post-parser workaround

Below please find a simplified example program testing several reference
methods which all fail the same. The excel file produced by this program
exhibits the same behavior when run against Spreadsheet-WriteExcel-2.03. I
have also tried setting the volatile flag for the COUNTIF function in
Spreadsheet::WriteExcel::Formula, to no avail.

### BEGIN cut and paste

#!/usr/bin/perl

# xl_test_form.pl
# used to test formula storage idiosyncrasy in CPAN module
Spreadsheet::WriteExcel

use strict;
use Spreadsheet::WriteExcel;

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

my ($row,$column) = (0,0);
my $loop1 = 0;
my $loop2 = 0;
my $format = undef;
my $number = 0;
my $upper = 5;
my $ref_row = $row;
$ref_row = $row;
my $value = 0;
my $formula_row = 0;

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

$worksheet->write_string(
$ref_row
,$column+0
,"number"
,$format
);
$worksheet->write_string(
$ref_row
,$column+1
,"count columns (formulas)"
,$format
);

my $formula = $worksheet-> store_formula('=COUNTIF(A$1:A$999,A1)');

foreach $loop1 ( 0 .. $upper - 1 )
{
foreach $loop2 ( 0 .. $loop1 )
{
$ref_row ++;
$value = $loop2 + 1;
$formula_row = $ref_row + 1;
$worksheet->write_number(
$ref_row
,$column+0
,$value
,$format
);
$worksheet->write_formula(
$ref_row
,$column+1
,'=COUNTIF(A:A,A'.$formula_row.')'
,$format
);
$worksheet->write(
$ref_row
,$column+2
,'=COUNTIF(A:A,A'.$formula_row.')'
,$format
);
$worksheet->write(
$ref_row
,$column+3
,'=COUNTIF($A:$A,A'.$formula_row.')'
,$format
);
$worksheet->write(
$ref_row
,$column+4
,'=COUNTIF(A$1:A$999,A'.$formula_row.')'
,$format
);
$worksheet->write(
$ref_row
,$column+5
,'=COUNTIF(A1:A999,A'.$formula_row.')'
,$format
);
$worksheet->repeat_formula(
$ref_row
,$column+6
,$formula
,$format
,"A1"
,"A".$formula_row
);
}
}

$workbook->close;

### END cut and paste

Thanks in advance,

Jon Hairr

"John McNamara" <jmcnamara@cpan.org> wrote in message
news:8cceb2da.0309171722.1f6826b6@posting.google.com...
> Sven Jungnickel wrote:
>
> Spreadsheet::WriteExcel's formula parser doesn't always parse complex
formulas
> correctly. Specifically it can incorrectly assign the class of certain
reference
> tokens used internally by Excel.
>
> This will be fixed at a later stage when I get time to rewrite the formula
> parser.
>
> In the meantime, it is possible to post-process the output from the parser
to
> correct this. Send me a short example program that demonstrates the
problem
> and I'll let you know how to fix it.
>
> John.
> --
> perl -MCPAN -e 'install jmcnamara & _ x ord $ ;' | tail -1

John McNamara

2004-07-28, 9:01 pm

Jon Hairr wrote:

> I would be interested in the post-parser workaround

Thanks for the detailed bug report.

The best way to workaround this is to use store_formula() and
repeat_formula() and massage the parsed tokens.

So using your example, adding the following substitution to the end
will fix the problem:

...

$worksheet->repeat_formula(
$ref_row
,$column+6
,$formula
,$format
,"A1"
,"A".$formula_row
,'_ref2d' => '_ref2dV'
);

...

Hopefully, I will get the parser fixed soon so that this type of
hackery isn't required.

Drop me a line if you need further information.

John.
--
# Sum the numbers in the first column of a file
perl -lpe '$,+=$_}{$_=+$,' file

2004-07-28, 9:01 pm

Excellent!

I did it to just the repeat_formula column to test it, and it appears to
have worked.

I take it that even if you hack the volatile flag for the COUNTIF function
in
Spreadsheet::WriteExcel::Formula, it is still getting changed/reset again
later by the parser or ignored?

--Jon

"John McNamara" <jmcnamara@cpan.org> wrote in message
news:8cceb2da.0407281518.491d708b@posting.google.com...
> Jon Hairr wrote:
>
>
> Thanks for the detailed bug report.
>
> The best way to workaround this is to use store_formula() and
> repeat_formula() and massage the parsed tokens.
>
> So using your example, adding the following substitution to the end
> will fix the problem:
>
> ...
>
> $worksheet->repeat_formula(
> $ref_row
> ,$column+6
> ,$formula
> ,$format
> ,"A1"
> ,"A".$formula_row
> ,'_ref2d' => '_ref2dV'
> );
>
> ...
>
>
> Hopefully, I will get the parser fixed soon so that this type of
> hackery isn't required.
>
> Drop me a line if you need further information.
>
> John.
> --
> # Sum the numbers in the first column of a file
> perl -lpe '$,+=$_}{$_=+$,' file

Sponsored Links