C# & OpenXML: Insert an image into an excel document

Question

I am using a modified version of this code to create an excel document, add cells, and style cells. I am trying to modify this code to also be able to add images to a sheet. I am getting nowhere and there really is nothing on the web that will help. I am trying to read through the OpenXML productivity tool diff. which is kind of unhelpful. Can anyone here point me in the right direction?

Thanks.

Answer 1

I looked at the answers from Vincent Tan and David Yates. Since it took me hours of coding to have the following simple requirements met, I'll post my solution here so the next person can save some time.

Requirements:

• Add an image to a specific cell of an existing excel spreadsheet/sheet
• Create a new spreadsheet and add an image to a specific cell
• Add an image to a specific cell of a WorksheetPart (so other operations can be performed before it's saved)
• Add the image from a stream
• Add the image from a file
• Use different image formats

Usage

// Add via file to existing spreadsheet
try
{
    ExcelTools.AddImage(false, filePath, "Sheet name",
                        imagePath, "Image description",
                        2 /* column */, 2 /* row */);
}
catch ...

// Add via stream to existing spreadsheet
try
{
    ExcelTools.AddImage(false, filePath, "Sheet name",
                        imageStream, "Image description",
                        2 /* column */, 2 /* row */);
}
catch ...

// Create spreadsheet and add image via path
try
{
    ExcelTools.AddImage(true, filePath, "Sheet name",
                        imagePath, "Image description",
                        2 /* column */, 2 /* row */);
}
catch ...

// Create spreadsheet and add image via stream
try
{
    ExcelTools.AddImage(true, filePath, "Sheet name",
                        imageStream, "Image description",
                        2 /* column */, 2 /* row */);
}
catch ...

// Add multiple images or apply further changes
try
{
    // Open spreadsheet
    spreadsheetDocument = SpreadsheetDocument.Open(excelFile, true);

    // Get WorksheetPart
    worksheetPart = ExcelTools.GetWorksheetPartByName(spreadsheetDocument, "Some sheet name");

    AddImage(worksheetPart, imagePath1, "My first image", 1, 1); // A1
    AddImage(worksheetPart, imagePath2, "My second image", 1, 5); // A5
    AddImage(worksheetPart, imagePath3, "My third image", 2, 7); // B7

    // Other operations if needed

    worksheetPart.Worksheet.Save();

    spreadsheetDocument.Close();
}
catch ...

Code:

using System;
using System.Data;
using System.Linq;
using System.IO;
using System.Drawing;
using System.Drawing.Imaging;
using System.Collections.Generic;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using A = DocumentFormat.OpenXml.Drawing;
using Xdr = DocumentFormat.OpenXml.Drawing.Spreadsheet;

namespace Utilities
{
    public class ExcelTools
    {
        public static ImagePartType GetImagePartTypeByBitmap(Bitmap image)
        {
            if (ImageFormat.Bmp.Equals(image.RawFormat))
                return ImagePartType.Bmp;
            else if (ImageFormat.Gif.Equals(image.RawFormat))
                return ImagePartType.Gif;
            else if (ImageFormat.Png.Equals(image.RawFormat))
                return ImagePartType.Png;
            else if (ImageFormat.Tiff.Equals(image.RawFormat))
                return ImagePartType.Tiff;
            else if (ImageFormat.Icon.Equals(image.RawFormat))
                return ImagePartType.Icon;
            else if (ImageFormat.Jpeg.Equals(image.RawFormat))
                return ImagePartType.Jpeg;
            else if (ImageFormat.Emf.Equals(image.RawFormat))
                return ImagePartType.Emf;
            else if (ImageFormat.Wmf.Equals(image.RawFormat))
                return ImagePartType.Wmf;
            else
                throw new Exception("Image type could not be determined.");
        }

        public static WorksheetPart GetWorksheetPartByName(SpreadsheetDocument document, string sheetName)
        {
            IEnumerable<Sheet> sheets =
               document.WorkbookPart.Workbook.GetFirstChild<Sheets>().
               Elements<Sheet>().Where(s => s.Name == sheetName);

            if (sheets.Count() == 0)
            {
                // The specified worksheet does not exist
                return null;
            }

            string relationshipId = sheets.First().Id.Value;
            return (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);
        }

        public static void AddImage(bool createFile, string excelFile, string sheetName,
                                    string imageFileName, string imgDesc,
                                    int colNumber, int rowNumber)
        {
            using (var imageStream = new FileStream(imageFileName, FileMode.Open))
            {
                AddImage(createFile, excelFile, sheetName, imageStream, imgDesc, colNumber, rowNumber);
            }
        }

        public static void AddImage(WorksheetPart worksheetPart,
                                    string imageFileName, string imgDesc,
                                    int colNumber, int rowNumber)
        {
            using (var imageStream = new FileStream(imageFileName, FileMode.Open))
            {
                AddImage(worksheetPart, imageStream, imgDesc, colNumber, rowNumber);
            }
        }

        public static void AddImage(bool createFile, string excelFile, string sheetName,
                                    Stream imageStream, string imgDesc,
                                    int colNumber, int rowNumber)
        {
            SpreadsheetDocument spreadsheetDocument = null;
            WorksheetPart worksheetPart = null;
            if (createFile)
            {
                // Create a spreadsheet document by supplying the filepath
                spreadsheetDocument = SpreadsheetDocument.Create(excelFile, SpreadsheetDocumentType.Workbook);

                // Add a WorkbookPart to the document
                WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                workbookpart.Workbook = new Workbook();

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

                // Add Sheets to the Workbook
                Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
                    AppendChild<Sheets>(new Sheets());

                // Append a new worksheet and associate it with the workbook
                Sheet sheet = new Sheet()
                {
                    Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
                    SheetId = 1,
                    Name = sheetName
                };
                sheets.Append(sheet);
            }
            else
            {
                // Open spreadsheet
                spreadsheetDocument = SpreadsheetDocument.Open(excelFile, true);

                // Get WorksheetPart
                worksheetPart = GetWorksheetPartByName(spreadsheetDocument, sheetName);
            }

            AddImage(worksheetPart, imageStream, imgDesc, colNumber, rowNumber);

            worksheetPart.Worksheet.Save();

            spreadsheetDocument.Close();
        }

        public static void AddImage(WorksheetPart worksheetPart,
                                    Stream imageStream, string imgDesc,
                                    int colNumber, int rowNumber)
        {
            // We need the image stream more than once, thus we create a memory copy
            MemoryStream imageMemStream = new MemoryStream();
            imageStream.Position = 0;
            imageStream.CopyTo(imageMemStream);
            imageStream.Position = 0;

            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 Xdr.WorksheetDrawing();
            }

            var worksheetDrawing = drawingsPart.WorksheetDrawing;

            Bitmap bm = new Bitmap(imageMemStream);
            var imagePart = drawingsPart.AddImagePart(GetImagePartTypeByBitmap(bm));
            imagePart.FeedData(imageStream);

            A.Extents extents = new A.Extents();
            var extentsCx = bm.Width * (long)(914400 / bm.HorizontalResolution);
            var extentsCy = bm.Height * (long)(914400 / bm.VerticalResolution);
            bm.Dispose();

            var colOffset = 0;
            var rowOffset = 0;

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

            var oneCellAnchor = new Xdr.OneCellAnchor(
                new Xdr.FromMarker
                {
                    ColumnId = new Xdr.ColumnId((colNumber - 1).ToString()),
                    RowId = new Xdr.RowId((rowNumber - 1).ToString()),
                    ColumnOffset = new Xdr.ColumnOffset(colOffset.ToString()),
                    RowOffset = new Xdr.RowOffset(rowOffset.ToString())
                },
                new Xdr.Extent { Cx = extentsCx, Cy = extentsCy },
                new Xdr.Picture(
                    new Xdr.NonVisualPictureProperties(
                        new Xdr.NonVisualDrawingProperties { Id = nvpId, Name = "Picture " + nvpId, Description = imgDesc },
                        new Xdr.NonVisualPictureDrawingProperties(new A.PictureLocks { NoChangeAspect = true })
                    ),
                    new Xdr.BlipFill(
                        new A.Blip { Embed = drawingsPart.GetIdOfPart(imagePart), CompressionState = A.BlipCompressionValues.Print },
                        new A.Stretch(new A.FillRectangle())
                    ),
                    new Xdr.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 Xdr.ClientData()
            );

            worksheetDrawing.Append(oneCellAnchor);
        }
    }
}

Answer 2

I wrote something on this here in a blog post: http://polymathprogrammer.com/2009/12/21/advanced-styling-in-excel-open-xml/

Note that it was originally written for the Open XML SDK 2.0 CTP version, so beware of the change from NumberFormat class to NumberingFormat class (when the SDK 2.0 was finalised)

If you want to isolate the code to just the image insertion, take a look here: http://polymathprogrammer.com/2009/11/30/how-to-insert-an-image-in-excel-open-xml/