Re: Using Export to create a spreadsheet containing formulas
- To: mathgroup at smc.vnet.net
- Subject: [mg106932] Re: Using Export to create a spreadsheet containing formulas
- From: "Hans Michel" <hmichel at cox.net>
- Date: Fri, 29 Jan 2010 07:43:47 -0500 (EST)
- References: <hjonb7$4ii$1@smc.vnet.net>
You can try this
Export["C:\\Book2.xls", "Data" -> {1, 2, "=SUM(A1:B1)"}]
The resulting Excel file opens but the cell containing the Formula is realy
text and needs to be evaluated. Upon evaluating the cell the value of sum of
the range A1:B1 is displayed.
I have used POI (Perl and PHP coding -> Excel Binary) and the XML solutions
to generate files which will be read by Excel. In the end they were for
simple solutions.
There is java project called POI
http://poi.apache.org/spreadsheet/formula.html
(http://sc.openoffice.org/excelfileformat.pdf)
So this would require J/Link and some external Java coding.
There is for Excel 2002(3) and above using the XML version where Formulas
are an attribute to a Cell. The resulting file is an XML representation of
the Excel Workbook but upon opening Excel performs a conversion, evaluation
is performed as part of the conversion. But this format is prone to errors
if not followed carefully.
There is building you own BIFF writer in Mathematica. Or a C project thru
Math/Link.
Hans
"gekko" <pfalloon at gmail.com> wrote in message
news:hjonb7$4ii$1 at smc.vnet.net...
> Hi Group,
> I would like to know how (or if) I can use the Export function to
> create a spreadsheet with formulas in some of the cells. The
> documentation mentions how formulas can be Imported but it's not clear
> how to do the same with Export.
>
> Alternatively, if anyone knows of an alternative to Export that would
> be great too. (Note, I'm not really interested in an "interactive"
> solution, e.g. updating the spreadsheet while it's open. The goal is
> to automate the creation of a potentially large number of spreadsheets
> with a standard form).
>
> Many thanks in advance!
>
> Thanks, P.
>