Adding an image to excel using openxml sdk

Question

I'm trying to add an image to an excel document that has been generated by closedxml using openxml after the spreadsheet has been generated (since closedxml has no way to insert images currently).

I can generate the excel file correctly and it opens up fine however when I try inserting the image in and try to open it in excel It gives an error.

I have used a small console app and found the error to be

Error 1
Description: The element has unexpected child element 'http://schemas.openxmlfor
mats.org/spreadsheetml/2006/main:drawing'.
Path: /x:worksheet[1]
Part: /xl/worksheets/sheet.xml
-------------------------------------------

The spreadsheet looks like this:

<?xml version="1.0" encoding="utf-8"?>
<x:worksheet xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <x:sheetPr>
        <x:outlinePr summaryBelow="1" summaryRight="1" />
    </x:sheetPr>
    <x:dimension ref="A1:M4" />
    <x:sheetViews>
        <x:sheetView tabSelected="0" workbookViewId="0" />
    </x:sheetViews>
    <x:sheetFormatPr defaultRowHeight="15" />
    <x:cols>
        ...
    </x:cols>
    <x:sheetData>
        ...
    </x:sheetData>
    <x:printOptions horizontalCentered="0" verticalCentered="0" headings="0" gridLines="0" />
    <x:pageMargins left="0.75" right="0.75" top="0.75" bottom="0.5" header="0.5" footer="0.75" />
    <x:pageSetup paperSize="1" scale="100" pageOrder="downThenOver" orientation="default" blackAndWhite="0" draft="0" cellComments="none" errors="displayed" />
    <x:headerFooter />
    <x:tableParts count="0" />
    <x:drawing r:id="R701e4d0efd7143ee" />
</x:worksheet>

Taking the

section out seems to allow the file to validate correctly.

I have also tried creating a blank excel document manually and adding an image and it looks pretty similar:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
    <dimension ref="A1"/>
    <sheetViews>
        <sheetView tabSelected="1" workbookViewId="0">
            <selection activeCell="D14" sqref="D14"/>
        </sheetView>
    </sheetViews>
    <sheetFormatPr defaultRowHeight="15" x14ac:dyDescent="0.25"/>
    <sheetData/>
    <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
    <drawing r:id="rId1"/>
</worksheet>

One thing I notice is that they do not have a namespace (x) like the generated one does but I don't know if that is causing a problem or not.

Can anyone see what I'm missing?

The code im using to insert the image is as follows:

public static void InsertImage(this Worksheet ws, long x, long y, long? width, long? height, string sImagePath)
{
    try
    {
        var wsp = ws.WorksheetPart;
        var dp = default(DrawingsPart);
        var imgp = default(ImagePart);
        var wsd = default(WorksheetDrawing);

        var ipt = default(ImagePartType);
        switch (sImagePath.Substring(sImagePath.LastIndexOf('.') + 1).ToLower())
        {
            case "png":
                ipt = ImagePartType.Png;
                break; // TODO: might not be correct. Was : Exit Select

                break;
            case "jpg":
            case "jpeg":
                ipt = ImagePartType.Jpeg;
                break; // TODO: might not be correct. Was : Exit Select

                break;
            case "gif":
                ipt = ImagePartType.Gif;
                break; // TODO: might not be correct. Was : Exit Select

                break;
            default:
                return;
        }

        if (wsp.DrawingsPart == null)
        {
            //----- no drawing part exists, add a new one

            dp = wsp.AddNewPart<DrawingsPart>();
            imgp = dp.AddImagePart(ipt, wsp.GetIdOfPart(dp));
            wsd = new WorksheetDrawing();
        }
        else
        {
            //----- use existing drawing part

            dp = wsp.DrawingsPart;
            imgp = dp.AddImagePart(ipt);
            dp.CreateRelationshipToPart(imgp);
            wsd = dp.WorksheetDrawing;
        }

        using (var fs = new FileStream(sImagePath, FileMode.Open))
        {
            imgp.FeedData(fs);
        }

        var imageNumber = 1;
        if (imageNumber == 1)
        {
            var drawing = new Drawing();
            drawing.Id = dp.GetIdOfPart(imgp);
            ws.Append(drawing);
        }

        var nvdp = new NonVisualDrawingProperties();
        nvdp.Id = new UInt32Value(Convert.ToUInt32(1024 + imageNumber));
        nvdp.Name = "Picture " + imageNumber.ToString();
        nvdp.Description = "Picture";
        var picLocks = new PictureLocks();
        picLocks.NoChangeAspect = true;
        picLocks.NoChangeArrowheads = true;
        var nvpdp = new NonVisualPictureDrawingProperties();
        nvpdp.PictureLocks = picLocks;
        var nvpp = new NonVisualPictureProperties();
        nvpp.NonVisualDrawingProperties = nvdp;
        nvpp.NonVisualPictureDrawingProperties = nvpdp;

        var stretch = new Stretch();
        stretch.FillRectangle = new FillRectangle();

        var blipFill = new BlipFill();
        var blip = new Blip();
        blip.Embed = dp.GetIdOfPart(imgp);
        blip.CompressionState = BlipCompressionValues.Print;
        blipFill.Blip = blip;
        blipFill.SourceRectangle = new SourceRectangle();
        blipFill.Append(stretch);

        var t2d = new Transform2D();
        var offset = new Offset();
        offset.X = 0;
        offset.Y = 0;
        t2d.Offset = offset;
        var bm = new Bitmap(sImagePath);

        var extents = new Extents();

        if (width == null)
        {
            extents.Cx = Convert.ToInt64(bm.Width)*
                         Convert.ToInt64(Math.Truncate(Convert.ToSingle(914400)/bm.HorizontalResolution));
        }
        else
        {
            extents.Cx = width;
        }

        if (height == null)
        {
            extents.Cy = Convert.ToInt64(bm.Height)*
                         Convert.ToInt64(Math.Truncate(Convert.ToSingle(914400)/bm.VerticalResolution));
        }
        else
        {
            extents.Cy = height;
        }

        bm.Dispose();
        t2d.Extents = extents;
        var sp = new ShapeProperties();
        sp.BlackWhiteMode = BlackWhiteModeValues.Auto;
        sp.Transform2D = t2d;
        var prstGeom = new PresetGeometry();
        prstGeom.Preset = ShapeTypeValues.Rectangle;
        prstGeom.AdjustValueList = new AdjustValueList();
        sp.Append(prstGeom);
        sp.Append(new NoFill());

        var picture = new Picture();
        picture.NonVisualPictureProperties = nvpp;
        picture.BlipFill = blipFill;
        picture.ShapeProperties = sp;

        var pos = new Position();
        pos.X = x;
        pos.Y = y;
        var ext = new Extent();
        ext.Cx = extents.Cx;
        ext.Cy = extents.Cy;
        var anchor = new AbsoluteAnchor();
        anchor.Position = pos;
        anchor.Extent = ext;
        anchor.Append(picture);
        anchor.Append(new ClientData());
        wsd.Append(anchor);
        wsd.Save(dp);
    }
    catch (Exception ex)
    {
        // or do something more interesting if you want
        throw ex;
    }
}

public static void InsertImage(MemoryStream stream, long x, long y, string sImagePath)
{
    using (var mySpreadsheet = SpreadsheetDocument.Open(stream, true))
    {
        var workbookPart = mySpreadsheet.WorkbookPart;
        var sheets = mySpreadsheet.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
        var relationshipId = sheets.First().Id.Value;
        var worksheetPart = (WorksheetPart) mySpreadsheet.WorkbookPart.GetPartById(relationshipId);
        var workSheet = worksheetPart.Worksheet;
        //Dim sheets As worksheetpart = mySpreadsheet.WorkbookPart.Workbook.
        // For each sheet, display the sheet information.
        long width = 9525*193;
        long height = 9525*80;
        InsertImage(workSheet, x, y, width, height, sImagePath);
    }
}

Nick · Answer 1 · 2014-04-22T12:23:28.937

hahahaha this cracked me up when I saw it. I'm doing literally the exact same thing.

I'm generating a spreadsheet using closed XML and then inserting images using Vincent's code.

Unfortunately I have the same problem but it's not related to the imageNumber being declared as 1

However I have found a workaround:

after you've inserted the images simply re-open the document using closed xml and save it again.

I'm just using this

workbook = new XLWorkbook(filepath);
workbook.save();

Afterwards it should open without a problem.

Hope this helps anyone else with a similar issue

EDIT:

I was able to fix this problem completely by changing this segment:

var imageNumber = dp.ImageParts.Count<ImagePart>();
if (imageNumber == 1)
{
    var drawing = new Drawing();
    drawing.Id = dp.GetIdOfPart(imgp);
    ws.Append(drawing);
}

change ws.Append(drawing); to

ws.InsertBefore(drawing, ws.Last)

Then it will put it just before the last child which for me was <x:tableParts count="0"/>

putting it after that seems to be what causes the problem

score 1 · Accepted Answer · answered Oct 09 '12 at 14:28

1

The problem might be that you assigned imageNumber to be 1.

var imageNumber = 1;

You might find this to help:

var imageNumber = dp.ImageParts.Count<ImagePart>();

Also, the code looks suspiciously similar to what I posted on my blog post. That's ok. Also, you might find SpreadsheetLight to be helpful. It's a spreadsheet library like ClosedXml, and it can insert images just fine. Disclaimer: I wrote SpreadsheetLight.

answered Oct 09 '12 at 14:28

Vincent Tan

3,058
22
21

Not sure if it was from your blog or not, there are a few blogs floating around with fairly similar code, will checkout spreadsheetlite – Daniel Powell Oct 09 '12 at 22:14
never found spreadsheetlite before as its not on nuget is it? – Daniel Powell Oct 09 '12 at 22:14
There are similar code floating around. I just find a few of the comments particularly unique that's all. And no, SpreadsheetLight is not related to nuget. – Vincent Tan Oct 11 '12 at 11:34

score 1 · Answer 3 · answered Nov 01 '13 at 17:51

I had a similar problem. When I did an OpenXML Productivity Tools compare between the ClosedXML-created document and one where I placed the image in Excel, I found that ClosedXML inserted some element nodes that, while they were not invalid, seemed to make the sheet with the drawing element invalid. I removed those elements and the spreadsheet with the image opens fine in Excel. It's a kluge, but it is working. This code goes at the top of the InsertImage method:

    //Remove superfluous worksheet elements. These break the inlcusion of the logo image.
    var element = ws.FirstChild;
    do
    {
        if(element.LocalName == null)
            continue;
        switch (element.LocalName)
        {
            case "pageSetup":
            case "headerFooter":
            case "tableParts":
                var prevElement = element;
                element = element.NextSibling();
                ws.RemoveChild<OpenXmlElement>(prevElement);
                break;
            default:
                element = element.NextSibling();
                break;
        }
    }
    while(element != null);
    // End of element removal

HTH

Angus

score 0 · Answer 4 · answered Feb 24 '15 at 09:52

OpenXml is not the easiest thing to wrap your head around.

The one thing that they don't tell you is that order of the elements is often important. As mentioned, the index number isn't the cause, you need to re-order your xml tags so that the tableParts element comes before the drawing element.

Alternatively, since you have no TableParts, you could just remove the element altogether

So from your example

<?xml version="1.0" encoding="utf-8"?>
<x:worksheet xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <x:sheetPr>
        <x:outlinePr summaryBelow="1" summaryRight="1" />
    </x:sheetPr>
    <x:dimension ref="A1:M4" />
    <x:sheetViews>
        <x:sheetView tabSelected="0" workbookViewId="0" />
    </x:sheetViews>
    <x:sheetFormatPr defaultRowHeight="15" />
    <x:cols>
        ...
    </x:cols>
    <x:sheetData>
        ...
    </x:sheetData>
    <x:printOptions horizontalCentered="0" verticalCentered="0" headings="0" gridLines="0" />
    <x:pageMargins left="0.75" right="0.75" top="0.75" bottom="0.5" header="0.5" footer="0.75" />
    <x:pageSetup paperSize="1" scale="100" pageOrder="downThenOver" orientation="default" blackAndWhite="0" draft="0" cellComments="none" errors="displayed" />
    <x:headerFooter />
    <x:drawing r:id="R701e4d0efd7143ee" />
    <x:tableParts count="0" />
</x:worksheet>

score 0 · Answer 5 · answered May 12 '15 at 18:21

If you're writing to a new Excel file the following code is fine:

if( dp.ImageParts.Count<ImagePart>() == 1 )
{
    var drawing = new Drawing();
    drawing.Id = dp.GetIdOfPart(imgp);
    ws.Append(drawing);
}

If you're writing to an existing file that may already have a drawing section though (but no images...the shapes seem to be part of the drawing section) then this will cause your document to fail validation and even fail to open completely.

I fixed this by doing the following:

if (dp.ImageParts.Count<ImagePart>() == 1)
{
    Drawing drawing = workSheet.GetFirstChild<Drawing>();
    if (drawing == null)
    {
        drawing = new Drawing();
        drawing.Id = dp.GetIdOfPart(imgp);
        workSheet.InsertAfter(drawing, workSheet.LastChild);
    }
}

Seems there's only 1 drawing section per worksheet...took me a while to track down the problem, as it wasn't immediately obvious unfortunately.

Adding an image to excel using openxml sdk

5 Answers5