How can I export a Dataset with graphics as an Excel file?

Question

I would like to export a Dataset to Excel. The Dataset contains graphics. But the following does not work:

{
    <|"img" -> Plot[Sin[x], {x, -5, 5}], "desc" -> "Plot of sin(x)"|>,
    <|"img" -> Plot[Cos[x], {x, -5, 5}], "desc" -> "Plot of cos(x)"|>
} //
Dataset //
Export["testtbl.xlsx", #, "XLSX"] &

The img column in the Excel file is just the string containing the Graphics expression in Wolfram Language.

How can I get the Export function to actually export the JPEG file of the plots?

Thanks in advance.

Answer 1

This solution will use external resources: python with a third-party library.

Requirements:

• Python: use the official site to install
• Python xlsxwriter library: site, will be used to create excel files from python

Save this code as a python file (I will save it as 'C:\file.py'):

import xlsxwriter
import io
import base64

def export(file,data,images_adr):  
    workbook   = xlsxwriter.Workbook(file)
    worksheet1 = workbook.add_worksheet()
    
    images_adr= list(map(list,images_adr))

    for rowi,row in enumerate(data):
        for coli,cell in enumerate(row):        
            if [rowi+1,coli+1] in images_adr:
                # is image
                worksheet1.insert_image(rowi,coli, f'python_{rowi}_{coli}.png', {'image_data':  io.BytesIO(base64.b64decode(cell)),'x_scale': 0.5, 'y_scale': 0.5,'x_offset':5,'y_offset':5})        
                worksheet1.set_row(rowi,120)
                worksheet1.set_column(coli,coli,35)
            else:
                # is text
                worksheet1.write(rowi,coli, cell)        

    workbook.close()

The python file has 3 arguments:

1- file: file path to create excel file (it a file exists, will override)
2- data: a 2-dimensional data matrix
3- images_adr: is the addresses of images in the data matrix

Use ExternalFunction (introduced in V12):

fn = ExternalFunction["Python", ReadString@"C:\\file.py"];

Register it as an export format:

ImportExport`RegisterExport["MyExportFormat", 
  MyExportFormat`Exporter ];
MyExportFormat`Exporter[filename_, data_, opts___] :=
 Module[{tdata, pos},
  pos = Position[data, _Graphics | _Graphics3D];
  tdata = MapAt[ExportString[#, {"Base64", "PNG"}] &, data, pos];
  fn[tdata, filename, pos];
  ]

Example 1

images = Dataset[{<|"img" -> Plot[Sin[x], {x, -5, 5}], 
     "desc" -> "Plot of sin(x)"|>, <|
     "img" -> Plot[Cos[x], {x, -5, 5}], 
     "desc" -> "Plot of cos(x)"|>}]

Output:

Convert dataset to a matrix:

data=Prepend[Normal@images[[Values]], Normal@First@images[[Keys]]]

Output (Grid):

Export the data using the defined format:

Export["D:\\file.xlsx", data, "MyExportFormat"]

Result:

Example 2

You can use any graphic (2D or 3D) or text with any arrangement as long as its a 2-dimensional matrix:

Here I use documentation examples:

data2 = {{ArrayPlot[RandomReal[1, {10, 20}], ColorFunction -> "Rainbow"]}, {ParametricPlot[r^2 { Sqrt[t] Cos[t], Sin[t]}, {t, 0, 3 Pi/2}, {r, 1, 2}]}, {ParametricPlot3D[{Cos[u], Sin[u] + Cos[v], Sin[v]}, {u, 0, 2 \[Pi]}, {v, -\[Pi], \[Pi]}]}};
Export["D:\\file2.xlsx", data2, "MyExportFormat"]

Keep in mind that row's height and column's width will be set to a fixed number if that row or column has an image. (will not respect image ratio)

Answer 2

Excel doesn't allow to embed an image into a cell, hence you cannot export a table with images diectly as an Excel sheet. Excel allows to place images on a sheet, but Mathematica currently doesn't allow to specify where an image should be placed exactly, and allows to place only one image on a sheet:

images = Dataset[{<|"img" -> Plot[Sin[x], {x, -5, 5}], "desc" -> "Plot of sin(x)"|>, 
                  <|"img" -> Plot[Cos[x], {x, -5, 5}], "desc" -> "Plot of cos(x)"|>}];
Export["images.xlsx", {{"Multiple Images"}}, "Images" -> Normal@ images[All, "img"]] // SystemOpen

Probably you can write a VBA macros to automate the process of importing external images into Excel worksheet as a workaround.