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

I have a highly dynamic spreadsheet that I'm generating with Spreadsheet::WriteExcel. However, I need to refer to another table of data that needs to be updated easily by the end user, preferably also in Excel format.

I thought a good solution might involve keeping the static data in a separate file, since Excel has support for referencing data in another file. Unfortunately, Spreadsheet::WriteExcel doesn't support this. The author suggested Spreadsheet::WriteExcelXML, which has this feature.

Unfortunately, it requires at least Excel 2002, which I think may be too high of a requirement. It doesn't help that these XML files don't open on my own Linux desktop in OOo 2 or Gnumeric either. (Or in Mac's Office.X for that matter).

What other Perl-based alternatives do I have, considering I have a lot invested in a Spreadsheet::WriteExcel-based solution already?

Thanks!

Mark Stosberg

  • Comment on Best practices for linking dynamic Excel data to static Excel data?

Replies are listed 'Best First'.
Re: Best practices for linking dynamic Excel data to static Excel data?
by thor (Priest) on Aug 22, 2005 at 21:26 UTC
    Can the other sheet be in the same file as the one that you're generating? As far as I remember, you can reference cells in other sheets in the same file with Spreadsheet::WriteExcel.

    thor

    Feel the white light, the light within
    Be your own disciple, fan the sparks of will
    For all of us waiting, your kingdom will come

[reply]
      Thanks for the response, thor.

      You are right that this feature exists, but the crux of the issue is that this static data needs to be easy to update, preferably through Excel. Is there way to use Spreadsheet::WriteExcel to generate part of Workbook dynamically, and include other worksheet pages as static documents?

      Mark Stosberg

[reply]
        Can't you read in the static data (from an Excel worksheet or elsewhere) and then generate your dynamic data and output the static data in the same (dynamic) sheet. As far as WriteExcel is concerned it doesn't matter whether the data is truly dynamic or just copies of static data.

        CountZero

        "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

[reply]

Back to Seekers of Perl Wisdom