Perl and Excel - reading and writing formulas

[CherylD]

I'm working with a template excel file on a linux machine. I read from this file, use sql to gather a few bits of info, and then write this file again with the info filled in.

The reason Excel is being used is because the management created this file and will frequently change it, but will always needs the same input onto it.

I'm reading and writing everything just fine, except for the formulas. This is what I am trying to use:

$dest_sheet->write($row, $col, $storage_book->{$sheet}->{$row}->{$col}->{'Formula'});

But when I look at the new excel file, the cells that have formulas in the template, have nothign in the new file. Any ideas?

 

[svar]

Look at

http://homepage/tinet.ie/~jmcnamara/perl/WriteExcel.html

I assume you have a data structure
$storage_book->{$sheet}->{$row}->{$col}->{'Formula'}='somestring';

I suspect that the mistake is that you really have a hash
%storage_book

in which case what you want to say is
$storage_book{$sheet}->{$row}->{$col}->{'Formula'}

no-> between book at {$sheet}

Does this help?
svar

 

[CherylD]

Well, that didn't change anything, so as a work around I have altered the spreadsheet so that there is a quote before the formula, making the formula the text value of a cell. This will do.

My next problem is that when I have a link in that Excel spreadsheet that links to other sheets in the workbook. Even if I have this sheet first in the workbook, the error is:

Unknown sheet name Sales_AGLX in formula at excel_testing.pl line 213

The link in the file is:
'=Sales_AGLX!B1

Any more ideas?