Thanks for your feedback. I was hoping that there was a way around the issue of the formula not being evaluated when the spreadsheet was opened.
I have created spreadsheets with both Spreadsheet::WriteExcel and Win32::OLE. I chose to use Spreadsheet::WriteExcel because it creates the spreadsheets much more quickly than Win32::OLE. In parallel with requesting assistance from the this support group, I wrote a Win32::OLE routine to open the spreadsheet files created by Spreadsheet::WriteExcel, read the '=SUMIF()' statements and write them back to the spreadsheet. This fix appears to work and should correct the formula evaluation process. I was hoping to avoid using this approach because I have some other plans for spreadsheets that would be created on a *NIX box and did not want to have to go through a WIN32 platform to correct any spreadsheets that were generated. Again, Thanks for the feedback. Ron Nichols Ronald G. Nichols Director of Information Technology Knouse Foods Cooperative 717 677-7111 Ext. 3470 [EMAIL PROTECTED] -----Original Message----- From: Lyndon Rickards [mailto:[EMAIL PROTECTED] Sent: Saturday, February 10, 2007 1:33 PM To: Nichols, Ron Cc: Perl-Win32-Users@listserv.ActiveState.com Subject: Re: Assistance with Spreadsheet::WriteExcel Formula 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 Perl-Win32-Users@listserv.ActiveState.com To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
