4
$\begingroup$

How can I export data along with images into Excel, with the images inserted into cells as shown below?

enter image description here

data = {{1, CompleteGraph[3], CompleteGraph[4], 
    CompleteGraph[5]}, {2, CompleteGraph[6], CompleteGraph[7], 
    CompleteGraph[8]}};
Export["multipleImages.xls", data]
$\endgroup$
15
  • 1
    $\begingroup$ Does Excel now support images embedded in spreadsheet cells? That didn't use to be possible. $\endgroup$
    – MarcoB
    Aug 7, 2023 at 8:21
  • $\begingroup$ An old post ; not sure if it related to the task at hand. $\endgroup$
    – Syed
    Aug 7, 2023 at 8:26
  • $\begingroup$ @MarcoB You're probably right; it doesn't seem to support this feature. Actually, I want to place them into cells so that when I filter data, the image on that row also filtered. $\endgroup$
    – internet
    Aug 7, 2023 at 8:34
  • 1
    $\begingroup$ This page suggests that it is possible for newer versions. Unfortunately mine is from 2013. $\endgroup$
    – Syed
    Aug 7, 2023 at 8:44
  • 3
    $\begingroup$ who needs Excel when you have Mathematica? :) $\endgroup$
    – Nasser
    Aug 7, 2023 at 8:46

2 Answers 2

3
$\begingroup$

JLink helps for your simple example. I tested the code below on Windows and Linux. Seems to work just fine. However, I doubt using JLink will work reasonably fast for really big data, maybe with parallelization. Or you may want to resort to NETLink then. Or use a Python solution and call this from Mathematica.

Needs["JLink`"];

    checkType[val_Image]:=True;
    checkType[val_?NumberQ]:=True;
    checkType[val_String]:=True;
    checkType[_]=False;

ExcelExport[xlsFile_String, data:{ {__?checkType}.. }, rowHeight_:40] := 
  Module[{wb, sheet, writeCell}, 
    JLink`JavaBlock[JLink`LoadJavaClass["org.apache.poi.xssf.usermodel.XSSFColor"];
    wb = JLink`JavaNew["org.apache.poi.xssf.usermodel.XSSFWorkbook"];
     sheet = If[wb@getNumberOfSheets[] > 0, 
            wb@getSheetAt[0]
            ,
            wb@createSheet[$Version]
       ];
      
     writeCell[noImg_?NumberQ, cell_, ___]:=cell @ setCellValue[noImg];
     writeCell[str_String, cell_]:=cell @ setCellValue[str];
     writeCell[img_Image, _, ri_Integer, ci_Integer]:= Block[{bytes, pictureId, drawing, myAnchor, myPicture},
         bytes = Normal @ ExportByteArray[img, "PNG"];
         pictureId = wb@addPicture[bytes, wb@PICTUREUTYPEUPNG];
         drawing = sheet@createDrawingPatriarch[];
         myAnchor = JLink`JavaNew["org.apache.poi.xssf.usermodel.XSSFClientAnchor"];
         myAnchor@setCol1[ci-1];
         myAnchor@setRow1[ri-1];  
         myPicture = drawing@createPicture[myAnchor, pictureId];
         myPicture@resize[]
     ];
      
       Do[ 
         row[r] = sheet@createRow[r - 1];
         row[r]@setHeightInPoints[rowHeight];
           Do[
             cell[r, c] = row[r]@createCell[c - 1];
             writeCell[data[[r, c]], cell[r, c], r, c]
           , {c, Dimensions[data][[2]]}
           ]
         ,
         {r, Dimensions[data][[1]]}
         ];
         
         foutstream = JLink`JavaNew["java.io.FileOutputStream", xlsFile];
         wb@write[foutstream];
         foutstream @ close[];
         wb@close[];
         
    ];
         xlsFile
];
sq = ImageResize[Image[GraphPlot[#,PlotStyle->{Black,Thick},Background->LightBlue],ImageResolution->200], {100}]&;

data={{1,sq@CompleteGraph[3],sq@CompleteGraph[4],sq@CompleteGraph[5]},
      {2,sq@CompleteGraph[6],sq@CompleteGraph[7],sq@CompleteGraph[8]}};
test = ExcelExport["test.xlsx",data];
SystemOpen @ test  

enter image description here

$\endgroup$
1
$\begingroup$

Export as HTML

Here I ignore the request in the title to use MS Excel, but focus on the statement in the comments, where the user needs to browse the data.

HTML templates with StringTemplate

bodywrap = StringTemplate@"<!DOCTYPE html>
<html>
  <head>
    <title>Avoid using Excel</title>
  </head>
  <body>
    <div>``</div>
  </body>
</html>";

downloadLink = StringTemplate@"<a href=\"data:file/glx;base64,`1`\" download=\"`2`\">`3`</a>";

Export file and open

Export["test.html", 
  bodywrap@ExportString[
    TableForm@Table[
      {
       k,
       Rasterize[CompleteGraph[k]],
       downloadLink @@ {
         ExportString[CompleteGraph[k], {"Base64", "GXL"}],
         "Graph.GXL", "Download GXL File"
         }
       },
      {k, 3, 5}
      ], "HTMLFragment", ImageSize -> 100]
  , "String"] // SystemOpen

This will generate an "HTML" file (HTML is an Open Standard file format) with a table with images, data, and even files to download.

enter image description here

Images and files are embedded "Base64" encoded, not linked. I use two alternative solutions, one either using ExportString[expr, {"Base64", "Format"}] and a StringTemplate and another using directly ExportString[expr,"HTMLFragment"]

enter image description here

$\endgroup$
1
  • $\begingroup$ @internet Does this answer your needs? Or do you still want a solution based on Excel? $\endgroup$
    – rhermans
    Aug 9, 2023 at 8:17

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.