# How do I export to Excel in multiple, named worksheets?

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.

-
I presume you've already tried this example in the help file? (I can't test since I don't have Excel.) –  Ｊ. Ｍ. Feb 14 '12 at 6:29
I think your update is a very good question, and I don't believe it has yet been answered to my satisfaction. –  Mr.Wizard Feb 14 '12 at 7:54
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. :) –  Mike Honeychurch Feb 14 '12 at 8:04
I have made a first effort to understand this and posted my observation below. –  Mr.Wizard Feb 14 '12 at 8:21

Using Export and specifying rules to create the sheet names:

Export["sampledata.xlsx", {"list1" -> list1, "list2" -> list2}]

-
It's even in the help file! :D –  Ｊ. Ｍ. Feb 14 '12 at 6:41
@J.M., Of course! I'm too new to MMA to figure out the rules on my own. :-) –  Tim Mayes Feb 14 '12 at 6:42
@Mike under More Information, then under With the element specification, the rules in exprs can have the following basic forms: -- apparently "Rules" is implicit. –  Mr.Wizard Feb 14 '12 at 7:05
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 –  Ｊ. Ｍ. Feb 14 '12 at 7:10
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. –  Mike Honeychurch Feb 14 '12 at 7:42

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:

SystemConvertExcelDumpsheets$= "Data" /. "Data" :> {{{1, 2}, {3, 4}}, {{5, 6}, {7, 8}}} /. _Missing -> SystemConvertExcelDumpblank$


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


Excerpt:

SystemConvertExcelDumpsheets$= "Data" /. "Data" :> {"list1" -> {{1, 2}, {3, 4}}, "list2" -> {{5, 6}, {7, 8}}} /. _Missing -> SystemConvertExcelDumpblank$

-
thanks for this @Mr.Wizard ...at least it seems I am not failing to understand the documented syntax. i.e. not going crazy :) –  Mike Honeychurch Feb 14 '12 at 8:45
@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. –  Mr.Wizard Feb 14 '12 at 8:48