4
$\begingroup$

I have a dataset:

set1 = Dataset[<|"s" -> <|"a" -> 1, "b" -> 2|>, "d" -> <|"a" -> 5, "b" -> 6|>|>]

My goal is to export several datasets (for simplicity here it is twice the same) into an Excel file. Each dataset shall be in a named sheet. Exporting a dataset to Excel works (file path is for windows, please change for your system):

Export["C:\\temp\\test.xlsx", set1]

But exporting a dataset to a named sheet already fails:

Export["C:\\temp\\test.xlsx", {"dd" -> set1}]

This does not give a table in the Excel-sheet as the first one.

My goal would be to have several named Excel-sheets with each having a dataset. I would expect this to work, but is doesn't:

Export["C:\\temp\\test.xlsx", {"dd" -> set1, "FF" -> set1}]

How do I get the tables from the datasets into named Excel sheets?

$\endgroup$

1 Answer 1

3
$\begingroup$

While Export["test.xlsx", "DD" -> set1] does not work to export a Dataset to a named worksheet, the syntax does work to export a rectangular data table. As a workaround, the dataset can be converted to a data table. One way to convert the dataset to a data table is to export it to CSV format, then import the data like this:

set1 = Dataset[<|"s" -> <|"a" -> 1, "b" -> 2|>, 
    "d" -> <|"a" -> 5, "b" -> 6|>|>];
ImportString[ExportString[set1, "CSV"], {"CSV", "Data"}]

(*  {{"", "a", "b"}, {"s", 1, 2}, {"d", 5, 6}}  *)

Multiple datasets can be exported to an Excel workbook with named worksheets like this:

set2 = Dataset[<|"ss" -> <|"aa" -> 1, "bb" -> 2, "cc" -> 3|>, 
    "dd" -> <|"aa" -> 5, "bb" -> 6, "cc" -> Missing[]|>|>];

Clear[convert]  (* converts the dataset to a data table *)
convert[d_Dataset] := 
    ImportString[ExportString[d, "CSV"], {"CSV", "Data"}]

Export["test.xlsx", {
     "Sh 1 DD" -> convert[set1],
     "Sh 2 FF" -> convert[set2]
   }];

The exported data can be recovered from the workbook as either a data table or a dataset like this:

{names, tables} = Import["test.xlsx", {"XLSX", {"Sheets", "Data"}}];
(* or *)
{names, sets} = Import["test.xlsx", {"XLSX", {"Sheets", "Dataset"}}];

Of course, the real test is to open the file in Excel and check the sheet names and the cell data there.

Note that this example uses Missing[] to emphasize the need to handle missing data explicitly. The method also works using, say, an empty string instead of Missing[]. The method works with full rectangular datasets, but does not work with "ragged" datasets.

$Version  (* 13.3.1 for Linux ARM (64-bit) (October 19, 2023) *);
$\endgroup$
1
  • $\begingroup$ It works also for me (Windows with 13.3.0). The Excel file is as expected. Nice work around the exporting importing. Thanks! $\endgroup$
    – Eisbär
    Commented Mar 19 at 9:30

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.