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.

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.

share|improve this question
    
I presume you've already tried this example in the help file? (I can't test since I don't have Excel.) –  J. M. 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
add comment

2 Answers 2

up vote 10 down vote accepted

Using Export and specifying rules to create the sheet names:

Export["sampledata.xlsx", {"list1" -> list1, "list2" -> list2}]
share|improve this answer
1  
It's even in the help file! :D –  J. M. 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
1  
@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
2  
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 –  J. M. Feb 14 '12 at 7:10
2  
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
show 7 more comments

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$
share|improve this answer
    
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
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.