Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

Is it possible to use back references across worksheets with Excel::Template?

by normskib (Initiate)
on Oct 12, 2011 at 11:39 UTC ( [id://930984]=perlquestion: print w/replies, xml ) Need Help??

normskib has asked for the wisdom of the Perl Monks concerning the following question:

Greetings Wise Monks

I have created a simple test script for Excel::Template to experiment with formulae on one worksheet that reference cells values on another. The perl script and xml template are shown below

When I run the script I get the following error:

Can't use an undefined value as an ARRAY reference at /usr/local/share/perl/5.10.0/Excel/Template/Context.pm line 301.

The script/template work correctly when all three rows are on a single worksheet.

Are cross-worksheet references beyond the scope of this module? Any advice greatly appreciated.

perl script

#!/usr/bin/perl use strict; use warnings; use Excel::Template; my $template = new Excel::Template(); $template->parse( 'test.xml' ); $template->write_file( 'test.xls' );

xml template

<?xml version='1.0'?> <!DOCTYPE spreadsheet> <workbook> <worksheet name="Tab1"> <row> <cell ref="one" type="number" text="1" /> <cell ref="two" type="number" text="2" /> </row> </worksheet> <worksheet name="Tab2"> <row> <formula>=<backref ref="one" />+<backref ref="two" /></formula> </row> </worksheet> </workbook>

Replies are listed 'Best First'.
Re: Is it possible to use back refrences across worksheets with Excel::Template?
by jmcnamara (Monsignor) on Oct 12, 2011 at 12:53 UTC

    Looking through the code and the test cases for Excel::Template it looks like backref can only access references in the same worksheet scope.

    As an alternative you could look at Spreadsheet::WriteExcel::FromXML or Spreadsheet::WriteExcel itself which doesn't have this limitation.

    --
    John.

      Thanks for the feedback John

      I'm used to using HTML::Template so Excel::Template was an easy transition for me but I'm now beginning to see some of it's limitations.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://930984]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others musing on the Monastery: (9)
As of 2024-04-23 12:22 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found