Creating Excel spreadsheet - Advanced question

From: Lance Powers (lance_powers_at_yahoo.com)
Date: 07/21/04 

Date: Wed, 21 Jul 2004 10:43:36 -0700

Hi folks,

I've got a project where I need to retrieve data from a database and
insert that data into an Excel spreadsheet that can be downloaded or
streamed to a browser. I'm using Linux/Apache/Perl (I don't have the
option of using Windows).

The users who will use these spreadsheets are not very computer savvy
and so the end result needs to be as easy to use as possible.
Basically, they will go to a web page and "create a report" which will
then generate the spreadsheet with current numbers.

The problem I'm encountering is that the spreadsheet is fairly complex
and seems beyond the ability of Spreadsheet::WriteExcel or
Spreadsheet::ParseExcel::SaveParser.

The spreadsheet is composed of 4 worksheets that contain lots of
formulas, graphs and some graphic images and a whole lot of formatting.
  The spreadsheet pulls data from a 5th worksheet which is the data from
the database.

Our initial idea was to make things easy on ourselves and create a
template Excel file that has everything formatted and have all formulas
reference the 5th worksheet that would be added later. We tried using
Spreadsheet::ParseExcel::SaveParser to add the worksheet to the existing
template with the data from the database. We found out later that
Spreadsheet::ParseExcel::SaveParser is not capable of maintaining
formulas on the original template. It only keeps the value of the cells
(which in our case is "0" and not the formula - it appears the graphs
and images may be lost as well).

Our next idea was to use the excellent Spreadsheet::WriteExcel to create
the entire spreadsheet (all worksheets) from scratch, but this module
does not have the ability to create graphs at this time. Unfortunately,
I'm required to include the graphs.

I'm starting to run out of ideas and hope that someone may have some
thoughts that can help us solve our dilemma.

These are the remaining options we've thought of (none of which fulfills
all of our needs):

1) Forget Excel and create the "spreadsheet" as an HTML page. The
drawback to this is that the power of the spreadsheet is no longer
available. The end user cannot change numbers and do "what if". It
also makes it harder for the user to email the results to another user.

2) Create a master templated spreadsheet (as mentioned above) and have
all formulas reference another spreadsheet that we would create using
Spreadsheet::WriteExcel. This seems like it would work, but I'm not
sure how we could stream this to the user. It seems like the user would
have to keep the master spreadsheet on their computer and then download
the additional spreadsheet that we create with all the data in it and
save it in the correct location so that the formula references on the
master template work. I'm afraid this would be too complex for the users.

3) I'm not really very knowledgeable about Excel, but I've heard that we
could create the master spreadsheet with formula references that are
URLs. If this is possible, it would be an option. I'm worried about
the security of the system though. We would have to hardcode to URL
references into the "master" spreadsheet. Since we have to provide
these spreadsheets to numerous people, I'm afraid that somebody would
figure out how to change the URL references and use another user's data.

4) What would be ideal is if we could simply append a worksheet to the
existing templated Excel file without disturbing the existing data
(graphs, formulas, formatting, etc..). From what I've seen, this is not
possible with any of the current perl modules.

Anybody have any creative solutions?

Thanks,

Lance

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

Relevant Pages

  • RE: Cannot get code to work for API Save Dialog Box
    ... I thought I put the formula in the spreadsheet with the .cell, ... Your totals are not saved with it. ... difference at all to the output in excel. ... ' Declare necessary API routines: ...
    (microsoft.public.access.forms)
  • Re: Looking for Easiest Way to Create Report
    ... By recording Excel macros and editing them you may be able to get all this down to one button push in each application. ... run your query then that makes life very much easier than it would have been if you'd had to match values line by line. ... So, if you do go for the query and TransferSpreadsheet route, that leaves you with the problem of levering in the four header rows. ... My guess is that the simplest solution will be in Excel, if you're prepared to do the job in two stages: one which generates the spreadsheet from Access, and another which requires you to move to Excel to add the headers. ...
    (microsoft.public.access.gettingstarted)
  • Re: why>?
    ... properly conceived spreadsheet models ... I can do excel macros; i've written hundreds and thousands of pages ... Anyway, I don't produce reports, regularly scheduled or otherwise. ... Databases aren't unnecessarily 'complex'. ...
    (microsoft.public.excel)
  • Re: Excel data to a Word doc
    ... We use an Excel spreadsheet to price a job. ... the Job Pricing spreadsheet from a template. ... A different way, especially if your Excel file is not all that consistent, ...
    (microsoft.public.word.docmanagement)
  • Re: Parsing Excel spreadsheets
    ... I presume you talking about using Excel 2003 and saving as ... "XML Spreadsheet ". ... I extracted primarily cell contents, though I did some very limited ... I looked, briefly, at xlrd. ...
    (comp.lang.python)