Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked

Re: WriteExcel and formulas

by jmcnamara (Monsignor)
on Jul 23, 2012 at 13:25 UTC ( #983171=note: print w/replies, xml ) Need Help??

in reply to WriteExcel and formulas

The code you show should work. I fleshed it out to a working example(*) and it shows the correct MAX and AVERAGE for valid cell ranges. If the cell range is empty then the result will be zero/#DIV0! like the corresponding Excel formula:

#!/usr/bin/perl use strict; use warnings; use Spreadsheet::WriteExcel; use Spreadsheet::WriteExcel::Utility; my $workbook = Spreadsheet::WriteExcel->new( 'test.xls' ); my $worksheet = $workbook->add_worksheet(); $worksheet->write( 'B2', 3 ); $worksheet->write( 'B3', 5 ); my @cols = ( 1, 2 ); my $row = 3; my $i = 1; for ( @cols ) { my $s = xl_rowcol_to_cell( 1, $i ); my $e = xl_rowcol_to_cell( $row - 1, $i ); my $range = "$s:$e"; print "$i:Range:$range\n"; $worksheet->write_formula( $row, $i, '=MAX(' . $range . ') +' ); $worksheet->write_formula( $row + 1, $i, '=AVERAGE(' . $range +. ')' ); $i++; } __END__

Spreadsheet::WriteExcel does sometimes mis-parse complex formulae resulting in an invalid 0 result. However, that isn't the case here.

* You should have done this when asking the questing. It only takes a few extra lines of code.


Replies are listed 'Best First'.
Re^2: WriteExcel and formulas
by rohit_raghu (Acolyte) on Jul 26, 2012 at 04:44 UTC
    It seems that formulas inserted using WriteExcel can't be read by Excel Viewer. Is that a problem with the viewer or the module?
    Rohit Raghunathan
      Is that a problem with the viewer or the module?

      A little bit of both.

      Spreadsheet::WriteExcel writes formulas in the required binary RPN format but it doesn't write the result of the formula since it wouldn't be practical to calculate the result of arbitrary formulae. So instead it writes 0 as a default formula result. Excel and most other spreadsheet applications recalculate the result of formulas when the file is loaded so that generally isn't an issue.

      However, Excel Viewer doesn't calculate formulae so all you see is the default 0 result.

      You can work around this by explicitly specifying the calculated value of the formula at the end of the argument list:

      $worksheet->write_fromula('A1', '=2+2', $format, 4); # Or: $worksheet->write('A1', '=2+2', $format, 4);



        I discovered another way by accident. Simply open the file on a system with excel and save it. MS Excel recalculates formulas in files last saved by an earlier version of excel.

        Rohit Raghunathan

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://983171]
[perldigious]: That stuff can hit fast... like what we just got where I'm at. Saw a few people who had slid OUT of the roundabout I have to drive through to get to work, not used to the ice yet, take awhile to get the hang of it again and make vehicle adjustments.
[stevieb]: man, when the roads are glare ice, I don't even bother going to work or out... unless I absolutely have to, or was already out in the first place
[choroba]: Tire chains are mandatory here in mountains, and the only unprepared each year are gritters
[stevieb]: I have a 3 day winter survival kit in my vehicle in the event I get snowed in in the mountains (which has happened before due to avalanches closing the roads (and once in the summer due to a massive forest fire that trapped us
[perldigious]: a handful of people in my work area did not make it, but I live pretty close and it's flat the whole way, so I didn't have any trouble. The roundabout is the worst thing I have to negotiate.
[thezip]: G'day all!
[perldigious]: Well, that and dodging the people who forget how ice works right away. :-)
[stevieb]: hey, thezip
[perldigious]: I don't miss having to drive up and down in elevation on roads that are iced over, I white knuckled gripping the steering wheel every time I had to where I used to live.
[perldigious]: So I don't envy you mountain guys if you have to drive in the winter, even with tire chains.

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (10)
As of 2016-12-06 16:40 GMT
Find Nodes?
    Voting Booth?
    On a regular basis, I'm most likely to spy upon:

    Results (112 votes). Check out past polls.