Inserting images inside excel cells using C# with the OpenXML 3.0.1.0 library

43 views Asked by At

I am working on a project in C# language using OpenXML 3.0.1.0 library and I need to insert inside dynamically generated excel files at runtine, rows containing cells that have as value an image.

Searching the internet I was able to find some solutions that allowed me to succeed in generating a file and inserting the images within an excel file by placing it at a cell but not inside it.

Below is a snippet of code from the solution I am currently using which works similarly to the solution proposed in the first answer of the question you can find at C# & OpenXML: Insert an image into an excel document:

 var drawingsPart = worksheetPart.DrawingsPart;
 if (drawingsPart == null)
     drawingsPart = worksheetPart.AddNewPart<DrawingsPart>();

 if (!worksheetPart.Worksheet.ChildElements.OfType<Drawing>().Any())
 {
     worksheetPart.Worksheet.Append(new Drawing { Id = worksheetPart.GetIdOfPart(drawingsPart) });
 }

 if (drawingsPart.WorksheetDrawing == null)
 {
     drawingsPart.WorksheetDrawing = new WorksheetDrawing();
 }

 var worksheetDrawing = drawingsPart.WorksheetDrawing;

 ImageFormatManager ifm = Configuration.Default.ImageFormatsManager;
 IImageFormat format = ifm.FindFormatByFileExtension(ext);
 var imagePart = drawingsPart.AddImagePart(GetImagePartTypeByBitmap(format.Name));

 using (var stream = new MemoryStream())
 {
     image.Save(stream, format);
     stream.ToArray();

     MemoryStream imageMemStream = new();
     stream.Position = 0;
     stream.CopyTo(imageMemStream);
     stream.Position = 0;

     imagePart.FeedData(stream);
     Bitmap bm = new(imageMemStream);
     A.Extents extents = new();
     var extentsCx = (long)bm.Width * (long)((float)914400 / bm.HorizontalResolution);
     var extentsCy = (long)bm.Height * (long)((float)914400 / bm.VerticalResolution);
     bm.Dispose();

     var colOffset = 0;
     var rowOffset = 0;

     var nvps = worksheetDrawing.Descendants<NonVisualDrawingProperties>();
     var nvpId = nvps.Count() > 0
         ? (UInt32Value)worksheetDrawing.Descendants<NonVisualDrawingProperties>().Max(p => p.Id.Value) + 1
         : 1U;

     var oneCellAnchor = new OneCellAnchor(
         new xdr.FromMarker
         {
             ColumnId = new ColumnId((colNumber).ToString()),
             RowId = new RowId((rowNumber).ToString()),
             ColumnOffset = new ColumnOffset(colOffset.ToString()),
             RowOffset = new RowOffset(rowOffset.ToString())
         },
         new Extent { Cx = extentsCx, Cy = extentsCy },
         new xdr.Picture(
             new NonVisualPictureProperties(
                 new NonVisualDrawingProperties { Id = nvpId, Name = "Picture " + nvpId, Description = imgNamespace },
                 new NonVisualPictureDrawingProperties(new A.PictureLocks { NoChangeAspect = true })
             ),
             new BlipFill(
                 new A.Blip { Embed = drawingsPart.GetIdOfPart(imagePart), CompressionState = A.BlipCompressionValues.Print },
                 new A.Stretch(new A.FillRectangle())
             ),
             new ShapeProperties(
                 new A.Transform2D(
                     new A.Offset { X = 0, Y = 0 },
                     new A.Extents { Cx = extentsCx, Cy = extentsCy }
                 ),
                 new A.PresetGeometry { Preset = A.ShapeTypeValues.Rectangle }
             )
         ),
         new ClientData()
     );

     worksheetDrawing.Append(oneCellAnchor);
 }

What I get, however, is visible in this image below:

Result

While what I want to obtain is something like this:

Desired result

I then went to unzip the xlsx file from which I got the image of the result I would like to get, and from what I could understand the association between image and cell is done taramite of metadata, but neither in the official documentation of the library, nor elsewhere could I find how it is possible to do this.

Can anyone suggest how I can do this?

0

There are 0 answers