Creating Excel spreadsheet - Advanced question
From: Lance Powers (lance_powers_at_yahoo.com)
Date: Wed, 21 Jul 2004 10:43:36 -0700
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
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
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?
-----= 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! =-----