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