8
$\begingroup$

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

$\endgroup$
9
$\begingroup$

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

| improve this answer | |
$\endgroup$
  • $\begingroup$ 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. $\endgroup$ – Chris Degnen Jul 22 '13 at 12:48
  • $\begingroup$ 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)". $\endgroup$ – Chris Degnen Jul 22 '13 at 14:36
  • $\begingroup$ @ChrisDegnen I see what you mean for that sort of formula. That might be something you need to contact Wolfram about :( $\endgroup$ – Mike Honeychurch Jul 22 '13 at 21:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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