15
$\begingroup$

If I have two lists:

list1 = {{1, 2}, {3, 4}};

list2 = {{5, 6}, {7, 8}};

I want to export these two lists to an Excel file and have list1 in a a worksheet called "list1" and list2 in a worksheet called list2. How do I do that ...using information contained in ref/format/XLSX and ref/format/XLX?

Edit

What I had in mind actually was from the Import and Export sections of the documentation for XLSX that give descriptions of the syntax for exporting.

Export["file.xlsx",expr, elem] creates an XLSX file by treating expr as specifying element elem.

Export["file.xlsx",{expr1,expr2,...},{{elem1,elem2,...}}] treats each expri as specifying the corresponding elemi.

Export["file.xlsx",expr,opt1->val1,...] exports expr with the specified option elements taken to have the specified values.

Export["file.xlsx",{elem1->expr1,elem2->expr2,...},"Rules"] uses rules to specify the elements to be exported.

Could I re-phrase and ask if someone could explain how the Export syntax examples above map to a working export to two worksheets? For example elements in the documentation, at the top of the documentation page anyway, are not listed for Export(other than "Data") but are listed as e.g. "Data","Formulas" etc. for Import. So I am unclear about how any of those export syntax examples correspond to something containing a worksheet name.

Improve this question
$\endgroup$
4
  • $\begingroup$ I presume you've already tried this example in the help file? (I can't test since I don't have Excel.) $\endgroup$
    – J. M.'s missing motivation ♦
    Commented Feb 14, 2012 at 6:29
  • $\begingroup$ I think your update is a very good question, and I don't believe it has yet been answered to my satisfaction. $\endgroup$
    – Mr.Wizard
    Commented Feb 14, 2012 at 7:54
  • $\begingroup$ I accepted the answer on the basis that it was an example of how to do what I asked in the (original) first part of the question. I suspect the answer to the second part involves magic. :) $\endgroup$
    – Mike Honeychurch
    Commented Feb 14, 2012 at 8:04
  • $\begingroup$ I have made a first effort to understand this and posted my observation below. $\endgroup$
    – Mr.Wizard
    Commented Feb 14, 2012 at 8:21

3 Answers 3

Reset to default
16
$\begingroup$

Using Export and specifying rules to create the sheet names:

Export["sampledata.xlsx", {"list1" -> list1, "list2" -> list2}]
Improve this answer
$\endgroup$
12
  • 1
    $\begingroup$ It's even in the help file! :D $\endgroup$
    – J. M.'s missing motivation ♦
    Commented Feb 14, 2012 at 6:41
  • $\begingroup$ @J.M., Of course! I'm too new to MMA to figure out the rules on my own. :-) $\endgroup$
    – Tim Mayes
    Commented Feb 14, 2012 at 6:42
  • 1
    $\begingroup$ @Mike under More Information, then under With the element specification, the rules in exprs can have the following basic forms: -- apparently "Rules" is implicit. $\endgroup$
    – Mr.Wizard
    Commented Feb 14, 2012 at 7:05
  • 2
    $\begingroup$ I think answers to this question, like this one, would benefit from adding an Excel screenshot, to confirm that the sheets indeed take the names they were given in Mathematica $\endgroup$
    – J. M.'s missing motivation ♦
    Commented Feb 14, 2012 at 7:10
  • 2
    $\begingroup$ Thanks @TimMayes but I guess this still comes back to my point that, IMO at least, this stuff isn't documented in the "more information" (equivalent) section that details the syntax. In other words we have "more information" syntax without usage examples and usage examples without "more information" syntax. It seems like a mess. $\endgroup$
    – Mike Honeychurch
    Commented Feb 14, 2012 at 7:42
5
$\begingroup$

Resonding to your updated question, this is the best I can determine the relationship between syntax forms given for the XLS format and this syntax:

Export["test2.xls", {"list1" -> list1, "list2" -> list2}]

It appears to be special format for type "Data", which is the default. The complete form is therefore:

Export["test2.xls", {"list1" -> list1, "list2" -> list2}, "Data"]

It is not in the form Export["file.xls",{elem1->expr1,elem2->expr2,...},"Rules"] :

Export["test2.xls", {"list1" -> list1, "list2" -> list2}, "Rules"]

During evaluation of In[102]:= Export::noelem: {list1,list2} is not a valid set of export elements for the XLS format. >>

Through Trace one can see that the sheet names are passed as part of the "Data" element:

Export["test2.xls", {list1, list2}] //Trace

Excerpt:

System`Convert`ExcelDump`sheets$ = 
  "Data" /. "Data" :> {{{1, 2}, {3, 4}}, {{5, 6}, {7, 8}}} /.
    _Missing -> System`Convert`ExcelDump`blank$

Export["test2.xls", {"list1" -> list1, "list2" -> list2}] //Trace

Excerpt:

System`Convert`ExcelDump`sheets$ = 
  "Data" /. "Data" :> {"list1" -> {{1, 2}, {3, 4}}, "list2" -> {{5, 6}, {7, 8}}} /.
    _Missing -> System`Convert`ExcelDump`blank$
Improve this answer
$\endgroup$
2
  • $\begingroup$ thanks for this @Mr.Wizard ...at least it seems I am not failing to understand the documented syntax. i.e. not going crazy :) $\endgroup$
    – Mike Honeychurch
    Commented Feb 14, 2012 at 8:45
  • $\begingroup$ @Mike hardly; there are a lot of inconsistencies to learn. Certainly there are instances, like this, where the method that works is more convenient than likely alternatives. $\endgroup$
    – Mr.Wizard
    Commented Feb 14, 2012 at 8:48
3
$\begingroup$

At the risk of raising a question from the grave its worth noting that this has been clarified/corrected at some point. Its not clear whether it was just a typo in the Help or its later functionality.

If you look at the XLSX help, the 4th Basic Example shows the correct syntax with Rules.

Export["test2.xls",  "Sheets" -> {"list1" -> list1, "list2" -> list2}, "Rules"]

The "Data" version used above still appears functionally equivalent.

Export["test2.xls", {"list1" -> list1, "list2" -> list2}, "Data"]
Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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