spreadsheet formula issue

[PerlSufi]

Hello monks, I am having a problem getting my script to write formulas for subtraction. Here is my code:

$sth = $dbh->prepare($query);
$sth->execute();
# create new excel file with 2 worksheets
my $xWB = Spreadsheet::WriteExcel->new('trusmart_billing_201302.xls');
my $xWS = $xWB->add_worksheet('Summary');
#set formats

my $format = $xWB->add_format();
$format->set_bold();


$xWS->set_column('A:A', 20);
$xWS->set_column('E:E', 20, $format);
$xWS->set_column('C:C', 20, $format);
$xWS->set_column('B:B', 20, $format);



my $xWS2 = $xWB->add_worksheet('Details');
#set columns A through D
$xWS2->set_column('A:D', 20);

# Add column headers for first worksheet
my $R=0; my $C=0;
$xWS->write($R, $C++, $_) for @{$sth->{NAME}};

# Read the query results and write them into the spreadsheet
while (my $ar=$sth->fetchrow_arrayref) {
     ++$R; $C=0;
         $xWS->write($R, $C++, $_) for @$ar;
 }


$xWS->write(0,5, 'Additional Images');
$xWS->write_formula('E2', '=(D2-B2)');
$xWS->write_formula('E3', '=(D3-B3)');
$xWS->write_formula('E4', '=(D4-B4)');
$xWS->write_formula('E5', '=(D5-B5)');
$xWS->write_formula('E6', '=1+5'); # as a test, this also did not work

It is writing 0 as the results in the E column cells. why? Thank you for your input

[Kenosis]

I ran your script using the following modified lines, as it didn't require a database transaction:

#set columns A through D
$xWS2->set_column( 'A:D', 20 );

# Add column headers for first worksheet
my $R = 0;
my $C = 0;
$xWS->write( $R, $C++, $_ ) for 'A' .. 'Z';

# Read the query results and write them into the spreadsheet
for ( 1 .. 26 ) {
    ++$R;
    $C = 0;
    $xWS->write( $R, $C++, $_ );
}

Results in cells E2:E6:

0
0
0
0
6

The formulas in E2:E5 of the "Summary" sheet evalute to 0 because columns B and D are empty in the spreadsheet your script creates. However, you'll note that the '=1+5' in cell E6 is correctly evaluated.

Hope this is helpful.

[PerlSufi]

Ok. But why is it empty? because the workbook is still open? Do I need to close it and then re open it?

[hdb]

Have a look at this thread http://perlmonks.org/?node_id=1028610.

[PerlSufi]

Thanks. I was just reading the parse excel documentation. I will try that

[PerlSufi]

FIXED. The problem was that I did not enable editing when I saved and opened the excel file to view it!!