Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

I would like to export an .XLS file complete with data and formulas. Is this possible using Export?

For example, here is data and a formula :-

enter image description here

I can import them, (separately), but cannot export them both.

enter image description here

I am looking into trying with .COM objects (as per Formatting in XLS(X) files).

share|improve this question
add comment

1 Answer 1

up vote 8 down vote accepted

There are a few different syntaxes for exporting formulas, all giving the same results. For example:

Export["test1.xlsx", {"Data" -> {1, 2, 3, 4, 5}, "Formulas" -> {{{"", "A1*27", ""}, 
{"", "A2/3", "B1*B2"}}}}, "Rules"]

Export["test2.xlsx", {{1, 2, 3, 4, 5}, {{{"", "A1*27", ""}, {"", "A2/3", "B1*B2"}}}}, 
{{"Data","Formulas"}}]

someData = {1, 2, 3, 4, 5};
someFormulas = {{{"", "A1*27", ""}, {"", "A2/3", "B1*B2"}}};
Export["test3.xlsx", {someData, someFormulas}, {{"Data", "Formulas"}}]

For your specific example:

Export["test4.xlsx", {{4}, {{{"", "IF(A1<4,0,1)"}}}}, {{"Data","Formulas"}}]

...and this is a screen grab of the exported Excel file "test4.xlsx":

enter image description here

share|improve this answer
    
Thanks. Unfortunately my application uses an add-in Excel formula, e.g. BDP($E2,F$1), which causes an export error: EXPORT::fmterr : Invalid XLSX format. –  Chris Degnen Jul 22 '13 at 12:48
    
The add-in formula can be written out to an .xlsx using the .COM object method as mentioned in the link, e.g. worksheet@Range["B1"]@Formula = "=BDP($E2,F$1)". –  Chris Degnen Jul 22 '13 at 14:36
    
@ChrisDegnen I see what you mean for that sort of formula. That might be something you need to contact Wolfram about :( –  Mike Honeychurch Jul 22 '13 at 21:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.