How to export a list of Datasets in separate sheets in XLSX?

Question

I can export a Dataset correctly using:

$dataset=Dataset[...];
Export["...path...file.xlsx",$dataset,{"XLSX"}]

Also, I can export Values in a Dataset on separate sheets using:

$datasets={"Sheet1"->Values@Normal@Dataset[...],"Sheet2"->Values@Normal@Dataset[...],...};
Export["...path...file.xlsx",$dataset,{"XLSX","Rules"}]

How do I export a list of Datasets not the values in their association in separate sheets in XLSX? I tried the following:

$datasets={"Sheet1"->Dataset[...],"Sheet2"->Dataset[...],...};
Export["...path...file.xlsx",$dataset,{"XLSX","Rules"}]

It doesn't work. I don't understand how these specifications work. Help please!

Answer 1

Either it is not supported yet or there is some weird syntax for it. Anyway, you can make it work by evaluating the following code:

System`Convert`ExcelDump`ToSheets[{data_Dataset}] := (
    Dataset;
    System`Convert`ExcelDump`ToSheets @ Dataset`DatasetSVList @ data
);

Then, the following code should work (at least it does in v14.1):

d1 = ExampleData[{"Dataset", "Titanic"}];
d2 = ExampleData[{"Dataset", "Planets"}];
Export["test.xlsx", "Sheets" -> {"Titanic" -> d1, "Planets" -> d2}, "Rules"]

Answer 2

This is fixed in 14.2. Thanks for the proposed workaround @Domen. I would suggest this slight change for 14.1 and prior, but if that ends up working too, great :)

System`Convert`ExcelDump`ToSheets[data:{__Dataset}] := (
    Dataset;
    ToSheets[Dataset`DatasetSVList/@ data]
)