Export data and formulas to multiple named sheets?

Questions
1

With help from the documentation, here and StackExchange, I have figured out how to:

  • Export data to multiple named sheets to Excel
  • Export data and functions to a single unnamed sheet
  • Export data and functions to multiple unnamed sheets

But, I cannot figure out how to export both data and functions to multiple unnamed sheets.

Here is what I have so far:

Export[
 "test.xlsx",
 <|
  "Data" ->
     {
    {{1., 2.}, {3., 4.}},
     {{5., 6.}, {7., 8.}}
    },
  "Formulas" -> {
    {{"", "", "A1+B1"}, {"", "", "A2+B2"}}, 
    {{"", "", "A1+B1"}, {"",  "", "A2*B2"}}
    }
  |>,
 "Rules"
 ];

This exports data and formulas to two sheets. I spent a couple of hours trying different ways to name the sheets, but so far I either get gibberish in the Excel file or an error such as:

  • Export: {Data, Sheets} is not a valid set of export elements for XLSX.

It would be great to have some real-world examples in the documentation.

2

It would be great to have some real-world examples in the documentation.

… but here it is (last one of “Basic Examples”):

3

It seems that all I needed to figure this out was take my dog Rex for a walk. Here is my current solution:

Export[
 "test.xlsx",
 <|
  "Sheets" ->
     {
    "a" -> {{1., 2.}, {3., 4.}},
     "b" -> {{5., 6.}, {7., 8.}}
    },
  "Formulas" -> {
     {{"", "", "A1+B1"}, {"", "", "A2+B2", "Sum(C1:C2)"}}, 
    {{"", "", "A1+B1"}, {"", "", "A2*B2", "Product(C1:C2)"}}
    }
  |>,
 "Rules"
 ];

I’m definitely interested in seeing how others solved this challenge.

THANKS

4

Thanks Henrik, but unless I’m missing something, the “basic examples” (Wolfram’s term, not mine) does not go into how do export formulas to Excel. Am I missing something?

That said, you are correct Henrik. There is an example. Though, to me, a spreadsheet is about more than numbers. It would be great to see a real-world example that utilized the capabilities of Excel beyond what a CSV file can handle.

Though this raises an interesting question, do others include formulas in the Excel exports?

THANKS

5

Hi Mike, I am sorry I misunderstood your question! I was regarding “formulas” in terms of Mathematica - instead in terms of Excel. So it is definitely a good question you posed! Regards – Henrik

6

Thanks Henrik.

I really do wish Excel would go away. But, from what I can tell, we becoming more entrenched in Excel. Of course, this does make a few people and companies lots of money.

It is great to see that Wolfram has embraced Excel with Excel-Link. Hopefully I will be able to convince those around me to give it a try.

7

Thank you for posting this Mike. Although it’s two years later, your solution is exactly what I needed. I spent all day struggling to decipher the documentation and a few StackExchange posts but never considered naming the “data” component of each Worksheet.

8

My objective is to export three distinct Datasets as individual sheets in an .xlsx file. When I follow the solution above, each exported Dataset appears as a single line in its respective sheet. Whereas if I export each Dataset as its own stand-alone .xlsx file, the exported Dataset are formatted correctly in their individual .xlsx files with correct column names and data rows.

9

Hi Chase,

You should always provide a MRE if possible to demonstrate the problem.

Generate sample datasets

titanic = ExampleData[{"Dataset", "Titanic"}];
{ds1, ds2, ds3} = Table[RandomSample[titanic, n], {n, {5, 10, 15}}]

This should work, but it does not, as you noted

Export["test.xlsx", "Sheets" -> {"a" -> ds1, "b" -> ds2, "c" -> ds3}, "Rules"];

Looks like a bug to me, you should report it to Wolfram Support.

Workaround

toList[ds_Dataset] := Values@Normal@ds // Prepend[Keys@First@ds]

Export["test.xlsx", 
  "Sheets" -> {"a" -> toList@ds1, "b" -> toList@ds2,  "c" -> toList@ds3}, "Rules"];
10

Thank you for coding the example and confirming it must be a bug. Case 5008589 is submitted to Wolfram Technical support and a link to this discussion was included in the notebook.