Mathematica 9 is now available
Services & Resources / Wolfram Forums / MathGroup Archive
-----

MathGroup Archive 2010

[Date Index] [Thread Index] [Author Index]

Search the Archive

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.
> 



  • Prev by Date: Re: Can Mathematica interpolate non-uniform scatter data?
  • Next by Date: Re: Frame
  • Previous by thread: Using Export to create a spreadsheet containing formulas
  • Next by thread: Re: Using Export to create a spreadsheet containing formulas