Inserting image in cell causes an error while opening the file

Question

I am using OpenXml (version 3.0.1) to insert an image in a cell. Although the code runs without any errors, when I export the Excel file and open it, I receive an error message and the image does not appear in the expected cell (A2).

I referred to this msdn-code-gallery but that code does not seem to work or perhaps is obsolete with the latest version of OpenXml.

Here's my code

using PIC = DocumentFormat.OpenXml.Drawing.Pictures;
using A = DocumentFormat.OpenXml.Drawing;

public byte[] AddImageToExcel(SheetExport sheetExport)
{
    using var memoryStream = new MemoryStream();
    var spreadSheetDocument = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook);

    var workbookPart = spreadSheetDocument.AddWorkbookPart();
    workbookPart.Workbook = new Workbook();

    // Add a WorksheetPart to the WorkbookPart.
    WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
    worksheetPart.Worksheet = new Worksheet(new SheetData());

    Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());

    var sheetToAppend = new Sheet()
    { Name = sheetExport.SheetName, SheetId = 1, Id = workbookPart.GetIdOfPart(worksheetPart) };
    sheets.Append(sheetToAppend);

    // Adding image to the worksheet
    InsertImageInCell(worksheetPart);

    workbookPart.Workbook.Save();
    spreadSheetDocument.Dispose();

    return memoryStream.ToArray();

}

private (long Width, long Height) GetImageExtents(string imagePath)
{
    using System.Drawing.Image image = System.Drawing.Image.FromFile(imagePath);
    long width = (long)((image.Width / image.HorizontalResolution) * 914400L);
    long height = (long)((image.Height / image.VerticalResolution) * 914400L);

    return (width, height);
}

private void InsertImageInCell(WorksheetPart worksheetPart)
{
    DrawingsPart drawingsPart;
    if (worksheetPart.DrawingsPart == null)
    {
        drawingsPart = worksheetPart.AddNewPart<DrawingsPart>();
        worksheetPart.Worksheet.Append(new Drawing { Id = worksheetPart.GetIdOfPart(drawingsPart) });
        worksheetPart.Worksheet.Save();
    }
    else
    {
        drawingsPart = worksheetPart.DrawingsPart;
    }

    var imagePath = Path.Combine(Directory.GetCurrentDirectory(), "Resources", "Banner.png");
    var imageExtents = GetImageExtents(imagePath);

    // Add the image
    ImagePart imagePart = drawingsPart.AddImagePart(ImagePartType.Png);
    using (var imageFileStream = new FileStream(imagePath, FileMode.Open, FileAccess.Read))
    {
        imagePart.FeedData(imageFileStream);
    }

    // Create the drawing elements
    var nvdp = new PIC.NonVisualDrawingProperties()
    {
        Id = (UInt32Value)1025U,
        Name = "Picture 1"
    };

    var nvpd = new PIC.NonVisualPictureDrawingProperties(new A.PictureLocks() { NoChangeAspect = true });

    var nvpp = new PIC.NonVisualPictureProperties()
    {
        NonVisualDrawingProperties = nvdp,
        NonVisualPictureDrawingProperties = nvpd
    };

    var blip = new A.Blip()
    {
        Embed = drawingsPart.GetIdOfPart(imagePart),
        CompressionState = A.BlipCompressionValues.Print
    };

    var blipFill = new PIC.BlipFill()
    {
        Blip = blip,
        SourceRectangle = new A.SourceRectangle()
    };

    blipFill.Append(new A.Stretch() { FillRectangle = new A.FillRectangle() });

    var sp = new PIC.ShapeProperties()
    {
        BlackWhiteMode = A.BlackWhiteModeValues.Auto
    };

    var transform2D = new A.Transform2D();
    var offset = new A.Offset() { X = 0L, Y = 0L };
    var extents = new A.Extents() { Cx = imageExtents.Width, Cy = imageExtents.Height };

    transform2D.Append(offset);
    transform2D.Append(extents);

    sp.Append(transform2D);
    sp.Append(new A.PresetGeometry(new A.AdjustValueList())
    {
        Preset = A.ShapeTypeValues.Rectangle
    });

    var picture = new PIC.Picture()
    {
        NonVisualPictureProperties = nvpp,
        BlipFill = blipFill,
        ShapeProperties = sp
    };

    var position = new Position() { X = 0L, Y = 0L };
    var extent = new Extent() { Cx = imageExtents.Width, Cy = imageExtents.Height };

    var absoluteAnchor = new AbsoluteAnchor()
    {
        Position = position,
        Extent = extent
    };

    absoluteAnchor.Append(picture);
    absoluteAnchor.Append(new ClientData());

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

    worksheetDrawing.Append(absoluteAnchor);
    drawingsPart.WorksheetDrawing.Save();

}

The inner xml of drawingPart.WorkSheetDrawing

<xdr:absoluteAnchor
    xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing">
    <xdr:pos x="0" y="0" />
    <xdr:ext cx="1781423" cy="628737" />
    <pic:pic
        xmlns:pic="http://schemas.openxmlformats.org/drawingml/2006/picture">
        <pic:nvPicPr>
            <pic:cNvPr id="1025" name="Picture 1" />
            <pic:cNvPicPr>
                <a:picLocks noChangeAspect="1"
                    xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" />
                </pic:cNvPicPr>
            </pic:nvPicPr>
            <pic:blipFill>
                <a:blip r:embed="R3a1e7131d49045f5" cstate="print"
                    xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
                    xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" />
                    <a:srcRect
                        xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" />
                        <a:stretch
                            xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">
                            <a:fillRect />
                        </a:stretch>
                    </pic:blipFill>
                    <pic:spPr bwMode="auto">
                        <a:xfrm
                            xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">
                            <a:off x="0" y="0" />
                            <a:ext cx="1781423" cy="628737" />
                        </a:xfrm>
                        <a:prstGeom prst="rect"
                            xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">
                            <a:avLst />
                        </a:prstGeom>
                    </pic:spPr>
                </pic:pic>
                <xdr:clientData />
            </xdr:absoluteAnchor>

I cannot use any other third party like EPPlus or ClosedXml it has to be done via OpenXml.

Answer 1

You use using PIC = DocumentFormat.OpenXml.Drawing.Pictures;
I think this is the wrong namespace.

For example:
var nvdp = new PIC.NonVisualDrawingProperties() must be var nvpp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureProperties()

Check wherever you use PIC and use DocumentFormat.OpenXml.Drawing.Spreadsheet