4

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]
xzczd
  • 65,995
  • 9
  • 163
  • 468
internet
  • 763
  • 1
  • 10
  • 1
    Does Excel now support images embedded in spreadsheet cells? That didn't use to be possible. – MarcoB Aug 07 '23 at 08:21
  • An old post ; not sure if it related to the task at hand. – Syed Aug 07 '23 at 08:26
  • @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. – internet Aug 07 '23 at 08:34
  • @Syed I saw that but it exports images to different sheets instead. – internet Aug 07 '23 at 08:34
  • 1
    This page suggests that it is possible for newer versions. Unfortunately mine is from 2013. – Syed Aug 07 '23 at 08:44
  • 3
    who needs Excel when you have Mathematica? :) – Nasser Aug 07 '23 at 08:46
  • People doing accounting may need a Spreadsheet like Excel, everybody else using Excel is most likely using the wrong tool. – rhermans Aug 07 '23 at 09:35
  • @Nasser There are two reasons for that. The first reason is that Excel is more commonly used and easier to share, in my opinion. And I also think that using data filtering is probably easier for them on Excel. – internet Aug 07 '23 at 10:51
  • I want to export a large amount of data, like 100k rows to Excel or something common so people can do the filtering anyway that they like. – internet Aug 07 '23 at 11:08
  • @internet If the idea is to use something common so people can access it, why not use an Open Standard file format like "XML", "JSON" or "CSV" instead of a closed format from proprietary software? – rhermans Aug 07 '23 at 11:19
  • @rhermans That is because I'm not familiar with them. Also the data contains images. – internet Aug 07 '23 at 11:28
  • @internet you could embed files and images in a CSV with {3, ExportString[CompleteGraph[3], {"Base64", "GXL"}], ExportString[CompleteGraph[3], {"Base64", "PNG"}]}. Also, "HTML" could be another Open Standard file format. – rhermans Aug 07 '23 at 14:33
  • @rhermans Export["file.csv", {3, ExportString[CompleteGraph[3], {"Base64", "GXL"}], ExportString[CompleteGraph[3], {"Base64", "PNG"}]}, "CSV"] Is this correct? It's not working for me. The ExportString function is returning a wall of text instead of images. One of the reasons I like Excel is that you can apply filters in any way you want (although not on images). – internet Aug 07 '23 at 15:09
  • The files are encoded. How do you expect the people consuming your data to use it? – rhermans Aug 07 '23 at 16:35
  • @rhermans I want to display these graphs as images, purely for the purpose of providing extra information. For instance, let's take a single row from a pool of 100,000 rows, which could be structured like this: {1, data1, data2, data3, image}. Here, data1, data2, and data3 hold the filtering data that users can employ. Depending on the conditions applied to data1, data2, and data3, the row might either be retained or removed, but the image will always be associated with the row. – internet Aug 07 '23 at 16:55

2 Answers2

3

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}, 
    JLinkJavaBlock[JLinkLoadJavaClass["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

Rolf Mertig
  • 17,172
  • 1
  • 45
  • 76
1

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=&quot;data:file/glx;base64,1&quot; download=&quot;2&quot;>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

rhermans
  • 36,518
  • 4
  • 57
  • 149