5
$\begingroup$

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.

$\endgroup$
3
  • $\begingroup$ Have you tried mapping Rasterize over all the img keys? $\endgroup$ Mar 4, 2021 at 16:14
  • $\begingroup$ I did. Here's what gets displayed in the img column: Image[NumericArray[{{{255, 255, 255}, {255, 255, 255}, {255, 255, 255}, {255, 255, 255}, ...}...}]]. $\endgroup$ Mar 4, 2021 at 17:03
  • $\begingroup$ I have not found a solution yet. But for now I have managed to postpone the problem by exporting to PDF. $\endgroup$ Mar 4, 2021 at 17:38

2 Answers 2

5
$\begingroup$

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:

enter image description here

Convert dataset to a matrix:

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

Output (Grid):

enter image description here

Export the data using the defined format:

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

Result:

enter image description here

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"]

enter image description here

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)

$\endgroup$
1
  • $\begingroup$ If this is the only method that is available, this answer should be accepted. $\endgroup$ Mar 8, 2021 at 17:06
2
$\begingroup$

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

screenshot

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

$\endgroup$
1
  • $\begingroup$ Thanks a lot for the point about per-cell images. Yes, unfortunately, the task is not as straightforward as I had hoped. $\endgroup$ Mar 8, 2021 at 17:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.