|
Posted by Christopher C LaFrancis on August 25, 2004, 9:52 am
Please log in for more thread options
Hi,
First, thanks in advance to anyone who might be able to offer some
assistance.
Here is my situation:
Objective - to open an Excel workbook (aka spreadsheet), delete the
contents of a worksheet, populate that worksheet with new data, and
save the workbook.
Status - using the perl module Spreadsheet::ParseExcel::SaveParser, I
have been able to accomplish this task, but with one significant
problem...
Problem - when the workbook is parsed, cells that contained formulas
before (i.e., in the original Excel file) only contain the values of
those formulas afterwards.
The Excel files are version 2002. I am using Perl 5.8.3,
Spreadsheet-ParseExcel-0.2603, and Spreadsheet-WriteExcel-2.04.
The only relevant info I've been able to find on the web so far has
been a posting by John McNamara (author of Spreadsheet-WriteExcel) on
2003.02.09:
"Spreadsheet::ParseExcel returns the calculated value of the formula
and not the textual form of the formula. The reason for this is that
Excel stores a formula in two parts. The first is a binary,
pre-parsed, RPN encoding of the formula. The second is the result of
the formula. To deparse the encoded part and return the textual form
of the formula is a non-trivial task and currently
Spreadsheet::ParseExcel doesn't attempt it. Instead it returns the
calculated value of the formula. In most cases this is sufficient."
I really don't need to *do* anything with the formulas, they are not
even in the worksheet I am working with; I just need them to stay the
way they are, so they can be used in the output'd workbook.
Is it possible to simply leave them in their binary form? I began
looking at modifying the Spreadsheet::ParseExcel to try to skip over
cells with formulas in them, but have been unsuccessful thus far.
Sorry for the rather long post (my first one ever, by the way). Once
again, I appreciate any help anyone can give.
Kind Regards,
Chris LaFrancis
|
|
Posted by Christopher C LaFrancis on August 26, 2004, 2:45 am
Please log in for more thread options
To clarify something I should have mentioned in my first post, I have
to run this script on Solaris, so that rules out using the Win32::OLE
module.
clafrancis@hotmail.com (Christopher C LaFrancis) wrote in message
> Hi,
>
> First, thanks in advance to anyone who might be able to offer some
> assistance.
>
> Here is my situation:
>
> Objective - to open an Excel workbook (aka spreadsheet), delete the
> contents of a worksheet, populate that worksheet with new data, and
> save the workbook.
>
> Status - using the perl module Spreadsheet::ParseExcel::SaveParser, I
> have been able to accomplish this task, but with one significant
> problem...
>
> Problem - when the workbook is parsed, cells that contained formulas
> before (i.e., in the original Excel file) only contain the values of
> those formulas afterwards.
>
> The Excel files are version 2002. I am using Perl 5.8.3,
> Spreadsheet-ParseExcel-0.2603, and Spreadsheet-WriteExcel-2.04.
>
> The only relevant info I've been able to find on the web so far has
> been a posting by John McNamara (author of Spreadsheet-WriteExcel) on
> 2003.02.09:
> "Spreadsheet::ParseExcel returns the calculated value of the formula
> and not the textual form of the formula. The reason for this is that
> Excel stores a formula in two parts. The first is a binary,
> pre-parsed, RPN encoding of the formula. The second is the result of
> the formula. To deparse the encoded part and return the textual form
> of the formula is a non-trivial task and currently
> Spreadsheet::ParseExcel doesn't attempt it. Instead it returns the
> calculated value of the formula. In most cases this is sufficient."
>
> I really don't need to *do* anything with the formulas, they are not
> even in the worksheet I am working with; I just need them to stay the
> way they are, so they can be used in the output'd workbook.
>
> Is it possible to simply leave them in their binary form? I began
> looking at modifying the Spreadsheet::ParseExcel to try to skip over
> cells with formulas in them, but have been unsuccessful thus far.
>
> Sorry for the rather long post (my first one ever, by the way). Once
> again, I appreciate any help anyone can give.
>
> Kind Regards,
> Chris LaFrancis
|
|
Posted by John McNamara on August 27, 2004, 5:08 pm
Please log in for more thread options Christopher C LaFrancis wrote:
> Problem - when the workbook is parsed, cells that contained formulas
> before (i.e., in the original Excel file) only contain the values of
> those formulas afterwards.
I am the author of Spreadsheet::WriteExcel and this is a subject that
I am currently working on.
There are two possible approaches, both of which you refer to in your
post.
The first is to deparse the formula back to its textual
representation. I attempted this as a proof of concept and it isn't as
non-trivial as I claim in the above quote. I was able to deparse
simple arithmetic formulas but a full implementation would take some
time. It is feasible however.
The second is to parse the Excel file and leave the existing data in
place but to append new data or worksheets. This is what I am
currently working on with the view of adding charts to
Spreadsheet::WriteExcel files and eventually other data as well. The
charting version will be released soon. But the full parsing version
will probably take about 3 months.
John.
--
|
|
Posted by Christopher C LaFrancis on August 28, 2004, 1:55 am
Please log in for more thread options jmcnamara@cpan.org (John McNamara) wrote in message
> Christopher C LaFrancis wrote:
>
> > Problem - when the workbook is parsed, cells that contained formulas
> > before (i.e., in the original Excel file) only contain the values of
> > those formulas afterwards.
>
>
> I am the author of Spreadsheet::WriteExcel and this is a subject that
> I am currently working on.
>
> There are two possible approaches, both of which you refer to in your
> post.
>
> The first is to deparse the formula back to its textual
> representation. I attempted this as a proof of concept and it isn't as
> non-trivial as I claim in the above quote. I was able to deparse
> simple arithmetic formulas but a full implementation would take some
> time. It is feasible however.
>
> The second is to parse the Excel file and leave the existing data in
> place but to append new data or worksheets. This is what I am
> currently working on with the view of adding charts to
> Spreadsheet::WriteExcel files and eventually other data as well. The
> charting version will be released soon. But the full parsing version
> will probably take about 3 months.
>
> John.
> --
Thanks for your reply. I (and I'm sure anyone else who has used it)
appreciate the work you've done, and are doing, with
Spreadsheet::WriteExcel.
Chris LaFrancis
|
| Similar Threads | Posted | | Spreadsheet::ParseExcel | November 16, 2006, 6:55 am |
| Spreadsheet::ParseExcel error on Perl 5.8.0 | November 7, 2005, 6:02 am |
| FmtDefault-Warning in Spreadsheet-ParseExcel | December 20, 2005, 10:50 am |
| Spreadsheet::ParseExcel : read cell-notes | May 27, 2005, 11:54 am |
| Spreadsheet-ParseExcel: Parsing various MS Excel file versions / grabing checkbox values? | September 17, 2004, 3:11 am |
| ParseExcel::Simple not working? | September 21, 2005, 3:32 pm |
| Spreadsheet::WriteExcel | December 7, 2005, 3:50 pm |
| ANN: Spreadsheet::Read 0.15 | June 21, 2006, 12:40 pm |
| ANN: Spreadsheet::Read 0.16 | July 4, 2006, 7:01 am |
| Re: ANN: Spreadsheet::Read 0.16 | July 4, 2006, 9:36 am |
|
XML Sitemap