How to insert multiple images in Excel Open XML

A blog reader, Fabrice Sacré, sent me an email with code about how to insert multiple images into an Excel file using Open XML. (I had fun playing Yanni’s “In The Mirror” on the virtual piano on his site.) He based it on my article on inserting just one single image into an Excel file. Based on his email address and signature, he works in the Red Cross. Wow! I’m honoured to be saving lives somehow, somewhere, even if in a long, circuitous and indirect manner. Possibly, I’m thinking too highly of myself and there’s no impact at all to the world at large, but I’m trying not to consider that.

Now when I started with the original code, I was in that “create xlsx, rename to zip, scrutinise xml till I fall asleep” mode. Frankly speaking, I had no idea what I was doing most of the time. And in this respect, the order of the individual code/data components was important, but I don’t know which one should come first (in fact, I still don’t). In particular, the code for inserting an image was before the code for inserting data. That worked fine for 1 image, but didn’t turn out so well when I tried with Fabrice’s code.

Ok, here’s the thing. Fabrice used an Excel template file (.xltx). He loaded that up into a new Excel file, then inserted images. Basically, you have an Excel file, then you obtained the worksheet you want, and then insert images to that worksheet. After I understood that (I had to ask him to send his full source code, because he only sent the image insertion function code), I made some changes to my original code to use his function code, and it worked! I’m not going to post the full source code he sent, because it might be sensitive (Red Cross! Saving lives, people!).

Here are the 3 images I used:
Cloud streaks
I like clouds and blue skies.

Crystallised pavillion
I used the “Crystalize” [sic] function in Paint.NET.

Dozing cat
Everyone loves cats, right?

So here’s the main body of the code:

static void Main(string[] args)
{
    string sFile = "ExcelOpenXmlMultipleImageInsert.xlsx";
    if (File.Exists(sFile))
    {
        File.Delete(sFile);
    }
    try
    {
        BuildWorkbook(sFile);
    }
    catch (Exception e)
    {
        Console.WriteLine(e.ToString());
    }
    Console.WriteLine("Program end");
}

protected static void BuildWorkbook(string filename)
{
    using (SpreadsheetDocument xl = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
    {
        WorkbookPart wbp = xl.AddWorkbookPart();
        WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
        Workbook wb = new Workbook();
        FileVersion fv = new FileVersion();
        fv.ApplicationName = "Microsoft Office Excel";
        Worksheet ws = new Worksheet();
        SheetData sd = new SheetData();

        WorkbookStylesPart wbsp = wbp.AddNewPart<WorkbookStylesPart>();
        wbsp.Stylesheet = CreateStylesheet();
        wbsp.Stylesheet.Save();

        Row r = new Row();
        r.RowIndex = 15;
        Cell c;
        c = new Cell();
        c.DataType = CellValues.String;
        c.StyleIndex = 1;
        c.CellReference = "G15";
        c.CellValue = new CellValue("We have multiple images!");
        r.Append(c);
        sd.Append(r);

        ws.Append(sd);                
        wsp.Worksheet = ws;

        // It happens the resolution for the 3 images are 72 dpi
        // Images are 240 by 180 pixels
        // Adjust as needed

        InsertImage(ws, 0, 0, "cloudstreaks.jpg");
        // 2286000 = 180 (pixels) * 914400 (magic number) / 72 (bitmap resolution)
        InsertImage(ws, 0, 2286000, "crystallisedpavilion.png");
        // 3048000 = 240 (pixels) * 914400 (magic number) / 72 (bitmap resolution)
        InsertImage(ws, 3048000, 0, "dozingcat.jpg");

        wsp.Worksheet.Save();
        Sheets sheets = new Sheets();
        Sheet sheet = new Sheet();
        sheet.Name = "Sheet1";
        sheet.SheetId = 1;
        sheet.Id = wbp.GetIdOfPart(wsp);
        sheets.Append(sheet);
        wb.Append(fv);
        wb.Append(sheets);

        xl.WorkbookPart.Workbook = wb;
        xl.WorkbookPart.Workbook.Save();
        xl.Close();
    }
}

I used the CreateStyleSheet() from this article. I want you to note that the data creation (the single Cell containing the sentence) is now before the image insertion. My approach was originally to model what I see on an Excel worksheet. I would most probably see the image(s) at the top, then the (table of) data below. Hence I modeled the coding to be as such. Apparently, that didn’t work out so well.

[UPDATE: For the CreateStyleSheet() function, it turns out that the NumberFormats class is no longer available in Open XML SDK 2.0. It’s now NumberingFormats. I’ve changed it for the source code for this article (but not for previous code articles)]

Fabrice’s approach was to use a template, then insert images to a particular worksheet. The data sheet was already created (via the template). My approach was to insert images and data in memory, so to speak, before committing the save to the worksheet/workbook parts. Basically, I was trying to create a worksheet out of thin air, add images, add data, and then plug it onto the workbook. Oh well, whatever works then…

Here’s the image insertion function Fabrice sent:

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

        ImagePartType ipt;
        switch (sImagePath.Substring(sImagePath.LastIndexOf('.') + 1).ToLower())
        {
            case "png":
                ipt = ImagePartType.Png;
                break;
            case "jpg":
            case "jpeg":
                ipt = ImagePartType.Jpeg;
                break;
            case "gif":
                ipt = ImagePartType.Gif;
                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 (FileStream fs = new FileStream(sImagePath, FileMode.Open))
        {
            imgp.FeedData(fs);
        }

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

        NonVisualDrawingProperties nvdp = new NonVisualDrawingProperties();
        nvdp.Id = new UInt32Value((uint)(1024 + imageNumber));
        nvdp.Name = "Picture " + imageNumber.ToString();
        nvdp.Description = "";
        DocumentFormat.OpenXml.Drawing.PictureLocks picLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks();
        picLocks.NoChangeAspect = true;
        picLocks.NoChangeArrowheads = true;
        NonVisualPictureDrawingProperties nvpdp = new NonVisualPictureDrawingProperties();
        nvpdp.PictureLocks = picLocks;
        NonVisualPictureProperties nvpp = new NonVisualPictureProperties();
        nvpp.NonVisualDrawingProperties = nvdp;
        nvpp.NonVisualPictureDrawingProperties = nvpdp;

        DocumentFormat.OpenXml.Drawing.Stretch stretch = new DocumentFormat.OpenXml.Drawing.Stretch();
        stretch.FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle();

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

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

        DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents();

        if (width == null)
            extents.Cx = (long)bm.Width * (long)((float)914400 / bm.HorizontalResolution);
        else
            extents.Cx = width;

        if (height == null)
            extents.Cy = (long)bm.Height * (long)((float)914400 / bm.VerticalResolution);
        else
            extents.Cy = height;

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

        DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture picture = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture();
        picture.NonVisualPictureProperties = nvpp;
        picture.BlipFill = blipFill;
        picture.ShapeProperties = sp;

        Position pos = new Position();
        pos.X = x;
        pos.Y = y;
        Extent ext = new Extent();
        ext.Cx = extents.Cx;
        ext.Cy = extents.Cy;
        AbsoluteAnchor 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)
    {
        throw ex; // or do something more interesting if you want
    }
}

protected static void InsertImage(Worksheet ws, long x, long y, string sImagePath)
{
    InsertImage(ws, x, y, null, null, sImagePath);
}

As of now, it supports only the image formats gif, jpg (jpeg) and png. However, there’s no stopping you from adding support for bmp, tiff, emf and other image formats supported by the ImagePartType enumeration.

Download the source code and the resulting Excel file.

Have fun.

There’s updated material and source code, together with more information on how to work with Open XML. Click here to find out more.

Sorry, comments are closed, but you can contact me directly if you like.