Nichols, Ron wrote:
> I am generating a spreadsheet using Spreadsheet::WriteExcel. Everything
> is working fine except a formula containing =SUMIF(A5:A10, C15,C5:C10).
> The formula gets generated correctly in the spreadsheet but in order to
> get the resulting value to be displayed in the cell, you have to edit
> the cell (i.e., click in the edit bar, hit enter). After performing an
> edit (without changing anything), the formula is evaluated correctly and
> displays the value.
>
> Is there any way to get the formula to evaluate without having to edit
> the cell? The spreadsheets that I am creating are locked so the user
> cannot make changes to the formulas. The script that creates the
> spreadsheet will create hundreds of spreadsheets each time it is run.
>
> I have attempted to force a recalculation of the spreadsheet from within
> Excel but that does not solve the issue.
>
> The version of the module as shown from PPM query is:
> Spreadsheet-WriteExcel-Simple [0.03] A simple single-sheet Excel document
>
> Your assistance is appreciated.
>
> *//**//**/Ronald G. Nichols/*
>
> */Director of Information Technology/*
>
> */Knouse Foods Cooperative/*
>
> */717 677-7111 Ext. 3470/*
>
> */[EMAIL PROTECTED]/*
>
> *//**//*
>
Spreadsheet::WriteExcel is an excellent module especially if you need
to create sn excel file on a *nix box, but AFAIK you have hit a
limitation of the module.
Suggest you consider switching to Win32::OLE which, as it is directly
driving Excel, will provide what you need.
The syntax isn't so very different to convert from your
Spreadsheet::WriteExcel script so far - I have made the conversion many
times myself. Short example -
#!perl -w
use strict;
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE qw(in with);
$Win32::OLE::Warn = 1;
my $Excel = Win32::OLE->new('Excel.Application'); #, 'Quit');
$Excel->{'Visible'} = 1;
my $xlFile = "e:/testing2/book.xls";
unlink($xlFile) if(-e $xlFile);
$Excel->{SheetsInNewWorkbook} = 1;
my $workbook = $Excel->Workbooks->Add();
print "$xlFile\n";
my $sheet = $workbook->Worksheets(1);
$sheet->{Name} = "My_Sheet";
my $col = "A";
my $row = 1;
foreach('Col1', 'Col2', 'Total' ) {
$sheet->Range($col . $row)->{Font}->{Bold} = 1;
$sheet->Range($col . $row)->{Value} = $_;
$col++;
}
$row += 1;
for(my $x = 10, my $y = 15; $y < 100; $x++, $y+= 3) {
$sheet->Range("A". $row)->{Value} = $x;
$sheet->Range("B". $row)->{Value} = $y;
$sheet->Range("C". $row)->{Value} = "=SUM(A" .$row ."+B" .$row . ")";
$row++;
}
$Excel->ActiveWorkbook->SaveAs({Filename => $xlFile});
HTH - Lynn.
_______________________________________________
Perl-Win32-Users mailing list
[email protected]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
