none
Adding image at a particular cell in Excel Spreadsheet RRS feed

  • Question

  • Hi,

     

    I am new to OpenXML. I am trying to add image at a particular cell in Excel spreadsheet.

    To be simple, i need to insert a image (from file) at "D15" location of Excel sheet document using OpenXML.

    Please help me.

     

    Thanks and Regards,

    YKK Reddy

     

    Wednesday, February 1, 2012 6:17 AM

Answers

  • Hi Reddy,

    I have modified my input excel document as you mentioned (with comments) as shown in the below screen shot, which in turn add 2 more xml parts (vml drawing and comments) under the particular worksheet where I have inserted the comments.

    Screenshot of the input template:

    Input template with comments

    And I have generalized some of the functionality by using own helper methods for accessing the sheet and for calculating the next relationship.

    After that, most important point that I noticed was in the sheet1.xml (worksheet xml part), drawing element should be placed before legacy drawing element if the sheet has both drawing (drawing xml part) and legacy drawing (vml drawing xml part). Violating this case leads to document corruption.

    Please find the complete code snippet of my test application. I hope it will help you.

    using System.Linq;
    using System.IO;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Spreadsheet;
    using A = DocumentFormat.OpenXml.Drawing;
    using Xdr = DocumentFormat.OpenXml.Drawing.Spreadsheet;
    using A14 = DocumentFormat.OpenXml.Office2010.Drawing;
    using System;
    using System.Collections.Generic;
    
    
    namespace InsertingImagetoExcel
    {
        class Program
        {
            public static string sourceFile = @"..\..\Data\InputTemplate.xlsx";
            public static string targetFile = @"..\..\Data\Test.xlsx";
            public static string ImageFile = @"..\..\Data\Chrysanthemum.jpg";
            static void Main(string[] args)
            {
                File.Copy(sourceFile, targetFile, true);
    
                using (SpreadsheetDocument document = SpreadsheetDocument.Open(@"..\..\Data\Test.xlsx", true))
                {
                    WorkbookPart workbookpart = document.WorkbookPart;
                    //WorksheetPart sheet1 = workbookpart.WorksheetParts.First();
                    WorksheetPart sheet1 = GetSheetByName(workbookpart, "sheet1");
    
                    //insert Image by specifying two range
                    InsertImage(sheet1, 1, 1, 3, 3, new FileStream(ImageFile, FileMode.Open));
    
                    document.WorkbookPart.Workbook.Save();
                    // Close the document handle.
                    document.Close();
                }
                System.Diagnostics.Process.Start(@"..\..\Data\Test.xlsx");
            }
            /// <summary>
            /// Inserts the image at the specified location 
            /// </summary>
            /// <param name="sheet1">The WorksheetPart where image to be inserted</param>
            /// <param name="startRowIndex">The starting Row Index</param>
            /// <param name="startColumnIndex">The starting column index</param>
            /// <param name="endRowIndex">The ending row index</param>
            /// <param name="endColumnIndex">The ending column index</param>
            /// <param name="imageStream">Stream which contains the image data</param>
            private static void InsertImage(WorksheetPart sheet1, int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex, Stream imageStream)
            {
                //Inserting a drawing element in worksheet
                //Make sure that the relationship id is same for drawing element in worksheet and its relationship part
                int drawingPartId = GetNextRelationShipID(sheet1);
                Drawing drawing1 = new Drawing() { Id = "rId" + drawingPartId.ToString() };
    
                //Check whether the WorksheetPart contains VmlDrawingParts (LegacyDrawing element)
                if (sheet1.VmlDrawingParts == null)
                {
                    //if there is no VMLDrawing part (LegacyDrawing element) exists, just append the drawing part to the sheet
                    sheet1.Worksheet.Append(drawing1);
                }
                else
                {
                    //if VmlDrawingPart (LegacyDrawing element) exists, then find the index of legacy drawing in the sheet and inserts the new drawing element before VMLDrawing part
                    int legacyDrawingIndex = GetIndexofLegacyDrawing(sheet1);
                    if (legacyDrawingIndex != -1)
                        sheet1.Worksheet.InsertAt<OpenXmlElement>(drawing1, legacyDrawingIndex);
                    else
                        sheet1.Worksheet.Append(drawing1);
                }
                //Adding the drawings.xml part
                DrawingsPart drawingsPart1 = sheet1.AddNewPart<DrawingsPart>("rId" + drawingPartId.ToString());
                GenerateDrawingsPart1Content(drawingsPart1, startRowIndex, startColumnIndex, endRowIndex, endColumnIndex);
                //Adding the image
                ImagePart imagePart1 = drawingsPart1.AddNewPart<ImagePart>("image/jpeg", "rId1");
                imagePart1.FeedData(imageStream);
            }
            #region Helper methods
            /// <summary>
            /// Get the index of legacy drawing element in the specified WorksheetPart
            /// </summary>
            /// <param name="sheet1">The worksheetPart</param>
            /// <returns>Index of legacy drawing</returns>
            private static int GetIndexofLegacyDrawing(WorksheetPart sheet1)
            {
                for (int i = 0; i < sheet1.Worksheet.ChildElements.Count; i++)
                {
                    OpenXmlElement element = sheet1.Worksheet.ChildElements[i];
                    if (element is LegacyDrawing)
                        return i;
                }
                return -1;
            }
            /// <summary>
            /// Returns the WorksheetPart for the specified sheet name
            /// </summary>
            /// <param name="workbookpart">The WorkbookPart</param>
            /// <param name="sheetName">The name of the worksheet</param>
            /// <returns>Returns the WorksheetPart for the specified sheet name</returns>
            private static WorksheetPart GetSheetByName(WorkbookPart workbookpart, string sheetName)
            {
                foreach (WorksheetPart sheetPart in workbookpart.WorksheetParts)
                {
                    string uri = sheetPart.Uri.ToString();
                    if (uri.EndsWith(sheetName + ".xml"))
                        return sheetPart;
                }
                return null;
            }
            /// <summary>
            /// Returns the next relationship id for the specified WorksheetPart
            /// </summary>
            /// <param name="sheet1">The worksheetPart</param>
            /// <returns>Returns the next relationship id </returns>
            private static int GetNextRelationShipID(WorksheetPart sheet1)
            {
                int nextId = 0;
                List<int> ids = new List<int>();
                foreach (IdPartPair part in sheet1.Parts)
                {
                    ids.Add(int.Parse(part.RelationshipId.Replace("rId", string.Empty)));
                }
                if (ids.Count > 0)
                    nextId = ids.Max() + 1;
                else
                    nextId = 1;
                return nextId;
            }
    
            // Generates content of drawingsPart1.
            private static void GenerateDrawingsPart1Content(DrawingsPart drawingsPart1, int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex)
            {
                Xdr.WorksheetDrawing worksheetDrawing1 = new Xdr.WorksheetDrawing();
                worksheetDrawing1.AddNamespaceDeclaration("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
                worksheetDrawing1.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main");
    
                Xdr.TwoCellAnchor twoCellAnchor1 = new Xdr.TwoCellAnchor() { EditAs = Xdr.EditAsValues.OneCell };
    
                Xdr.FromMarker fromMarker1 = new Xdr.FromMarker();
                Xdr.ColumnId columnId1 = new Xdr.ColumnId();
                columnId1.Text = startColumnIndex.ToString();
                Xdr.ColumnOffset columnOffset1 = new Xdr.ColumnOffset();
                columnOffset1.Text = "38100";
                Xdr.RowId rowId1 = new Xdr.RowId();
                rowId1.Text = startRowIndex.ToString();
                Xdr.RowOffset rowOffset1 = new Xdr.RowOffset();
                rowOffset1.Text = "0";
    
                fromMarker1.Append(columnId1);
                fromMarker1.Append(columnOffset1);
                fromMarker1.Append(rowId1);
                fromMarker1.Append(rowOffset1);
    
                Xdr.ToMarker toMarker1 = new Xdr.ToMarker();
                Xdr.ColumnId columnId2 = new Xdr.ColumnId();
                columnId2.Text = endColumnIndex.ToString();
                Xdr.ColumnOffset columnOffset2 = new Xdr.ColumnOffset();
                columnOffset2.Text = "542925";
                Xdr.RowId rowId2 = new Xdr.RowId();
                rowId2.Text = endRowIndex.ToString();
                Xdr.RowOffset rowOffset2 = new Xdr.RowOffset();
                rowOffset2.Text = "161925";
    
                toMarker1.Append(columnId2);
                toMarker1.Append(columnOffset2);
                toMarker1.Append(rowId2);
                toMarker1.Append(rowOffset2);
    
                Xdr.Picture picture1 = new Xdr.Picture();
    
                Xdr.NonVisualPictureProperties nonVisualPictureProperties1 = new Xdr.NonVisualPictureProperties();
                Xdr.NonVisualDrawingProperties nonVisualDrawingProperties1 = new Xdr.NonVisualDrawingProperties() { Id = (UInt32Value)2U, Name = "Picture 1" };
    
                Xdr.NonVisualPictureDrawingProperties nonVisualPictureDrawingProperties1 = new Xdr.NonVisualPictureDrawingProperties();
                A.PictureLocks pictureLocks1 = new A.PictureLocks() { NoChangeAspect = true };
    
                nonVisualPictureDrawingProperties1.Append(pictureLocks1);
    
                nonVisualPictureProperties1.Append(nonVisualDrawingProperties1);
                nonVisualPictureProperties1.Append(nonVisualPictureDrawingProperties1);
    
                Xdr.BlipFill blipFill1 = new Xdr.BlipFill();
    
                A.Blip blip1 = new A.Blip() { Embed = "rId1", CompressionState = A.BlipCompressionValues.Print };
                blip1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
    
                A.BlipExtensionList blipExtensionList1 = new A.BlipExtensionList();
    
                A.BlipExtension blipExtension1 = new A.BlipExtension() { Uri = "{28A0092B-C50C-407E-A947-70E740481C1C}" };
    
                A14.UseLocalDpi useLocalDpi1 = new A14.UseLocalDpi() { Val = false };
                useLocalDpi1.AddNamespaceDeclaration("a14", "http://schemas.microsoft.com/office/drawing/2010/main");
    
                blipExtension1.Append(useLocalDpi1);
    
                blipExtensionList1.Append(blipExtension1);
    
                blip1.Append(blipExtensionList1);
    
                A.Stretch stretch1 = new A.Stretch();
                A.FillRectangle fillRectangle1 = new A.FillRectangle();
    
                stretch1.Append(fillRectangle1);
    
                blipFill1.Append(blip1);
                blipFill1.Append(stretch1);
    
                Xdr.ShapeProperties shapeProperties1 = new Xdr.ShapeProperties();
    
                A.Transform2D transform2D1 = new A.Transform2D();
                A.Offset offset1 = new A.Offset() { X = 1257300L, Y = 762000L };
                A.Extents extents1 = new A.Extents() { Cx = 2943225L, Cy = 2257425L };
    
                transform2D1.Append(offset1);
                transform2D1.Append(extents1);
    
                A.PresetGeometry presetGeometry1 = new A.PresetGeometry() { Preset = A.ShapeTypeValues.Rectangle };
                A.AdjustValueList adjustValueList1 = new A.AdjustValueList();
    
                presetGeometry1.Append(adjustValueList1);
    
                shapeProperties1.Append(transform2D1);
                shapeProperties1.Append(presetGeometry1);
    
                picture1.Append(nonVisualPictureProperties1);
                picture1.Append(blipFill1);
                picture1.Append(shapeProperties1);
                Xdr.ClientData clientData1 = new Xdr.ClientData();
    
                twoCellAnchor1.Append(fromMarker1);
                twoCellAnchor1.Append(toMarker1);
                twoCellAnchor1.Append(picture1);
                twoCellAnchor1.Append(clientData1);
    
                worksheetDrawing1.Append(twoCellAnchor1);
    
                drawingsPart1.WorksheetDrawing = worksheetDrawing1;
            }
            #endregion Helper methods
        }
    }
    

    Best,

    Vijayakumar

    Vijayakumar.bu@gmail.com | vijayakumars.svks@gmail.com


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Marked as answer by Kishor Reddy Sunday, February 12, 2012 2:30 PM
    Thursday, February 9, 2012 11:56 AM

All replies

  • Hi Reddy,

    In an Excel document, the images are represents by means of a Drawing xml part associated with sheet which may contain details about one or more images, shapes etc. Each and every drawing object can be represents by the following elements.

    1)      absoluteAnchor - used as an anchor placeholder for a shape or group of shapes. It anchors the object in the same position relative to sheet position

    2)      oneCellAnchor - specifies a one cell anchor placeholder for a group, a shape, or a drawing element. It moves with the cell.

    3)      twoCellAnchor - specifies a two cell anchor placeholder for a group, a shape, or a drawing element. It moves with cells. In most cases, MS Excel uses this element for inserting images.

    By using oneCellAnchor, we can specify only “from” (child) element, whereas by using twoCellAnchor we can specify “from” and “to” (child) elements.

    Where “from” - specifies the first anchor point for the drawing element. This is used to anchor the top and left sides of the image/shape within the excel sheet. “to” - specifies the second anchor point for the drawing element. This is used to anchor the bottom and right sides of the shape within the spreadsheet.

    Using “from” and “to” child elements of twoCellAnchor element, the open xml user can able to set the cell range of the image occurrence.

    Best,

    Vijayakumar

    Vijayakumars.svks@gmail.com | Vijayakumar.bu@gmail.com


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Thursday, February 2, 2012 6:45 AM
  • Hi Vijay,

    Thanks for your solution. Can you post a sample code ?

    Thanks and Regards,

    YKK Reddy

    Friday, February 3, 2012 7:29 AM
  • Hi Reddy,

     

    Please get the simple application code which opens an empty input excel document and inserts an image at the specified range.

     

    using System.Linq;
    using System.IO;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Spreadsheet;
    using A = DocumentFormat.OpenXml.Drawing;
    using Xdr = DocumentFormat.OpenXml.Drawing.Spreadsheet;
    using A14 = DocumentFormat.OpenXml.Office2010.Drawing;
    
    
    namespace InsertingImagetoExcel
    {
        class Program
        {
            public static string sourceFile = @"..\..\Data\InputTemplate.xlsx";
            public static string targetFile = @"..\..\Data\Test.xlsx";
            public static string ImageFile = @"..\..\Data\Chrysanthemum.jpg";
            static void Main(string[] args)
            {
                File.Copy(sourceFile, targetFile, true);
    
                using (SpreadsheetDocument document = SpreadsheetDocument.Open(@"..\..\Data\Test.xlsx", true))
                {
                    WorkbookPart workbookpart = document.WorkbookPart;
                    WorksheetPart sheet1 = workbookpart.WorksheetParts.First();
                    //insert Image by specifying two range
                    InsertImage(sheet1, 1, 1, 3,3, new FileStream(ImageFile, FileMode.Open));
                                  
                    document.WorkbookPart.Workbook.Save();
                    // Close the document handle.
                   document.Close();
                }
                System.Diagnostics.Process.Start(@"..\..\Data\Test.xlsx");
            }
    
            private static void InsertImage(WorksheetPart sheet1, int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex, Stream imageStream)
            {
                //Inserting a drawing element in worksheet
                //Make sure that the relationship id is same for drawing element in worksheet and its relationship part
                Drawing drawing1 = new Drawing() { Id = "rId1" };
                sheet1.Worksheet.Append(drawing1);
                //Adding the drawings.xml part
                DrawingsPart drawingsPart1 = sheet1.AddNewPart<DrawingsPart>("rId1");
                GenerateDrawingsPart1Content(drawingsPart1, startRowIndex, startColumnIndex, endRowIndex, endColumnIndex);
                //Adding the image
                ImagePart imagePart1 = drawingsPart1.AddNewPart<ImagePart>("image/jpeg", "rId1");
                imagePart1.FeedData(imageStream);
            }
    
            // Generates content of drawingsPart1.
            private static void GenerateDrawingsPart1Content(DrawingsPart drawingsPart1, int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex)
            {
                Xdr.WorksheetDrawing worksheetDrawing1 = new Xdr.WorksheetDrawing();
                worksheetDrawing1.AddNamespaceDeclaration("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
                worksheetDrawing1.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main");
    
                Xdr.TwoCellAnchor twoCellAnchor1 = new Xdr.TwoCellAnchor() { EditAs = Xdr.EditAsValues.OneCell };
    
                Xdr.FromMarker fromMarker1 = new Xdr.FromMarker();
                Xdr.ColumnId columnId1 = new Xdr.ColumnId();
                columnId1.Text = startColumnIndex.ToString();
                Xdr.ColumnOffset columnOffset1 = new Xdr.ColumnOffset();
                columnOffset1.Text = "38100";
                Xdr.RowId rowId1 = new Xdr.RowId();
                rowId1.Text = startRowIndex.ToString();
                Xdr.RowOffset rowOffset1 = new Xdr.RowOffset();
                rowOffset1.Text = "0";
    
                fromMarker1.Append(columnId1);
                fromMarker1.Append(columnOffset1);
                fromMarker1.Append(rowId1);
                fromMarker1.Append(rowOffset1);
    
                Xdr.ToMarker toMarker1 = new Xdr.ToMarker();
                Xdr.ColumnId columnId2 = new Xdr.ColumnId();
                columnId2.Text = endColumnIndex.ToString();
                Xdr.ColumnOffset columnOffset2 = new Xdr.ColumnOffset();
                columnOffset2.Text = "542925";
                Xdr.RowId rowId2 = new Xdr.RowId();
                rowId2.Text = endRowIndex.ToString();
                Xdr.RowOffset rowOffset2 = new Xdr.RowOffset();
                rowOffset2.Text = "161925";
    
                toMarker1.Append(columnId2);
                toMarker1.Append(columnOffset2);
                toMarker1.Append(rowId2);
                toMarker1.Append(rowOffset2);
    
                Xdr.Picture picture1 = new Xdr.Picture();
    
                Xdr.NonVisualPictureProperties nonVisualPictureProperties1 = new Xdr.NonVisualPictureProperties();
                Xdr.NonVisualDrawingProperties nonVisualDrawingProperties1 = new Xdr.NonVisualDrawingProperties() { Id = (UInt32Value)2U, Name = "Picture 1" };
    
                Xdr.NonVisualPictureDrawingProperties nonVisualPictureDrawingProperties1 = new Xdr.NonVisualPictureDrawingProperties();
                A.PictureLocks pictureLocks1 = new A.PictureLocks() { NoChangeAspect = true };
    
                nonVisualPictureDrawingProperties1.Append(pictureLocks1);
    
                nonVisualPictureProperties1.Append(nonVisualDrawingProperties1);
                nonVisualPictureProperties1.Append(nonVisualPictureDrawingProperties1);
    
                Xdr.BlipFill blipFill1 = new Xdr.BlipFill();
    
                A.Blip blip1 = new A.Blip() { Embed = "rId1", CompressionState = A.BlipCompressionValues.Print };
                blip1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
    
                A.BlipExtensionList blipExtensionList1 = new A.BlipExtensionList();
    
                A.BlipExtension blipExtension1 = new A.BlipExtension() { Uri = "{28A0092B-C50C-407E-A947-70E740481C1C}" };
    
                A14.UseLocalDpi useLocalDpi1 = new A14.UseLocalDpi() { Val = false };
                useLocalDpi1.AddNamespaceDeclaration("a14", "http://schemas.microsoft.com/office/drawing/2010/main");
    
                blipExtension1.Append(useLocalDpi1);
    
                blipExtensionList1.Append(blipExtension1);
    
                blip1.Append(blipExtensionList1);
    
                A.Stretch stretch1 = new A.Stretch();
                A.FillRectangle fillRectangle1 = new A.FillRectangle();
    
                stretch1.Append(fillRectangle1);
    
                blipFill1.Append(blip1);
                blipFill1.Append(stretch1);
    
                Xdr.ShapeProperties shapeProperties1 = new Xdr.ShapeProperties();
    
                A.Transform2D transform2D1 = new A.Transform2D();
                A.Offset offset1 = new A.Offset() { X = 1257300L, Y = 762000L };
                A.Extents extents1 = new A.Extents() { Cx = 2943225L, Cy = 2257425L };
    
                transform2D1.Append(offset1);
                transform2D1.Append(extents1);
    
                A.PresetGeometry presetGeometry1 = new A.PresetGeometry() { Preset = A.ShapeTypeValues.Rectangle };
                A.AdjustValueList adjustValueList1 = new A.AdjustValueList();
    
                presetGeometry1.Append(adjustValueList1);
    
                shapeProperties1.Append(transform2D1);
                shapeProperties1.Append(presetGeometry1);
    
                picture1.Append(nonVisualPictureProperties1);
                picture1.Append(blipFill1);
                picture1.Append(shapeProperties1);
                Xdr.ClientData clientData1 = new Xdr.ClientData();
    
                twoCellAnchor1.Append(fromMarker1);
                twoCellAnchor1.Append(toMarker1);
                twoCellAnchor1.Append(picture1);
                twoCellAnchor1.Append(clientData1);
    
                worksheetDrawing1.Append(twoCellAnchor1);
    
                drawingsPart1.WorksheetDrawing = worksheetDrawing1;
            }
    
        }
    }
    
    


    I hope it helps you.

     

    Best,

    Vijayakumar

    Vijayakumars.svks@gmail.com | Vijayakumar.bu@gmail.com

     


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Proposed as answer by Dricosr Wednesday, July 5, 2017 6:29 PM
    Friday, February 3, 2012 10:16 AM
  • Hi Vijaya,

    Thanks for the solution. I have applied this solution to my project. But for some reason, the image is getting inserted at the last sheet.

    The code "WorksheetPart sheet1 = workbookpart.WorksheetParts.First();" is taking the last sheet and inserting image.

    I have changed it to first sheet and tried to insert it. The problem i am facing now was my sheet contains few comments (the reference id "rId1" is already assigned to legacy drawing part). So the above code is throwing some exception.

    Can you help me overcome this problem also ?

    Thanks and Regards,

    YKK Reddy


    • Edited by Kishor Reddy Wednesday, February 8, 2012 7:03 AM
    Monday, February 6, 2012 7:25 AM
  • Hi Reddy,

    I have modified my input excel document as you mentioned (with comments) as shown in the below screen shot, which in turn add 2 more xml parts (vml drawing and comments) under the particular worksheet where I have inserted the comments.

    Screenshot of the input template:

    Input template with comments

    And I have generalized some of the functionality by using own helper methods for accessing the sheet and for calculating the next relationship.

    After that, most important point that I noticed was in the sheet1.xml (worksheet xml part), drawing element should be placed before legacy drawing element if the sheet has both drawing (drawing xml part) and legacy drawing (vml drawing xml part). Violating this case leads to document corruption.

    Please find the complete code snippet of my test application. I hope it will help you.

    using System.Linq;
    using System.IO;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Spreadsheet;
    using A = DocumentFormat.OpenXml.Drawing;
    using Xdr = DocumentFormat.OpenXml.Drawing.Spreadsheet;
    using A14 = DocumentFormat.OpenXml.Office2010.Drawing;
    using System;
    using System.Collections.Generic;
    
    
    namespace InsertingImagetoExcel
    {
        class Program
        {
            public static string sourceFile = @"..\..\Data\InputTemplate.xlsx";
            public static string targetFile = @"..\..\Data\Test.xlsx";
            public static string ImageFile = @"..\..\Data\Chrysanthemum.jpg";
            static void Main(string[] args)
            {
                File.Copy(sourceFile, targetFile, true);
    
                using (SpreadsheetDocument document = SpreadsheetDocument.Open(@"..\..\Data\Test.xlsx", true))
                {
                    WorkbookPart workbookpart = document.WorkbookPart;
                    //WorksheetPart sheet1 = workbookpart.WorksheetParts.First();
                    WorksheetPart sheet1 = GetSheetByName(workbookpart, "sheet1");
    
                    //insert Image by specifying two range
                    InsertImage(sheet1, 1, 1, 3, 3, new FileStream(ImageFile, FileMode.Open));
    
                    document.WorkbookPart.Workbook.Save();
                    // Close the document handle.
                    document.Close();
                }
                System.Diagnostics.Process.Start(@"..\..\Data\Test.xlsx");
            }
            /// <summary>
            /// Inserts the image at the specified location 
            /// </summary>
            /// <param name="sheet1">The WorksheetPart where image to be inserted</param>
            /// <param name="startRowIndex">The starting Row Index</param>
            /// <param name="startColumnIndex">The starting column index</param>
            /// <param name="endRowIndex">The ending row index</param>
            /// <param name="endColumnIndex">The ending column index</param>
            /// <param name="imageStream">Stream which contains the image data</param>
            private static void InsertImage(WorksheetPart sheet1, int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex, Stream imageStream)
            {
                //Inserting a drawing element in worksheet
                //Make sure that the relationship id is same for drawing element in worksheet and its relationship part
                int drawingPartId = GetNextRelationShipID(sheet1);
                Drawing drawing1 = new Drawing() { Id = "rId" + drawingPartId.ToString() };
    
                //Check whether the WorksheetPart contains VmlDrawingParts (LegacyDrawing element)
                if (sheet1.VmlDrawingParts == null)
                {
                    //if there is no VMLDrawing part (LegacyDrawing element) exists, just append the drawing part to the sheet
                    sheet1.Worksheet.Append(drawing1);
                }
                else
                {
                    //if VmlDrawingPart (LegacyDrawing element) exists, then find the index of legacy drawing in the sheet and inserts the new drawing element before VMLDrawing part
                    int legacyDrawingIndex = GetIndexofLegacyDrawing(sheet1);
                    if (legacyDrawingIndex != -1)
                        sheet1.Worksheet.InsertAt<OpenXmlElement>(drawing1, legacyDrawingIndex);
                    else
                        sheet1.Worksheet.Append(drawing1);
                }
                //Adding the drawings.xml part
                DrawingsPart drawingsPart1 = sheet1.AddNewPart<DrawingsPart>("rId" + drawingPartId.ToString());
                GenerateDrawingsPart1Content(drawingsPart1, startRowIndex, startColumnIndex, endRowIndex, endColumnIndex);
                //Adding the image
                ImagePart imagePart1 = drawingsPart1.AddNewPart<ImagePart>("image/jpeg", "rId1");
                imagePart1.FeedData(imageStream);
            }
            #region Helper methods
            /// <summary>
            /// Get the index of legacy drawing element in the specified WorksheetPart
            /// </summary>
            /// <param name="sheet1">The worksheetPart</param>
            /// <returns>Index of legacy drawing</returns>
            private static int GetIndexofLegacyDrawing(WorksheetPart sheet1)
            {
                for (int i = 0; i < sheet1.Worksheet.ChildElements.Count; i++)
                {
                    OpenXmlElement element = sheet1.Worksheet.ChildElements[i];
                    if (element is LegacyDrawing)
                        return i;
                }
                return -1;
            }
            /// <summary>
            /// Returns the WorksheetPart for the specified sheet name
            /// </summary>
            /// <param name="workbookpart">The WorkbookPart</param>
            /// <param name="sheetName">The name of the worksheet</param>
            /// <returns>Returns the WorksheetPart for the specified sheet name</returns>
            private static WorksheetPart GetSheetByName(WorkbookPart workbookpart, string sheetName)
            {
                foreach (WorksheetPart sheetPart in workbookpart.WorksheetParts)
                {
                    string uri = sheetPart.Uri.ToString();
                    if (uri.EndsWith(sheetName + ".xml"))
                        return sheetPart;
                }
                return null;
            }
            /// <summary>
            /// Returns the next relationship id for the specified WorksheetPart
            /// </summary>
            /// <param name="sheet1">The worksheetPart</param>
            /// <returns>Returns the next relationship id </returns>
            private static int GetNextRelationShipID(WorksheetPart sheet1)
            {
                int nextId = 0;
                List<int> ids = new List<int>();
                foreach (IdPartPair part in sheet1.Parts)
                {
                    ids.Add(int.Parse(part.RelationshipId.Replace("rId", string.Empty)));
                }
                if (ids.Count > 0)
                    nextId = ids.Max() + 1;
                else
                    nextId = 1;
                return nextId;
            }
    
            // Generates content of drawingsPart1.
            private static void GenerateDrawingsPart1Content(DrawingsPart drawingsPart1, int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex)
            {
                Xdr.WorksheetDrawing worksheetDrawing1 = new Xdr.WorksheetDrawing();
                worksheetDrawing1.AddNamespaceDeclaration("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
                worksheetDrawing1.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main");
    
                Xdr.TwoCellAnchor twoCellAnchor1 = new Xdr.TwoCellAnchor() { EditAs = Xdr.EditAsValues.OneCell };
    
                Xdr.FromMarker fromMarker1 = new Xdr.FromMarker();
                Xdr.ColumnId columnId1 = new Xdr.ColumnId();
                columnId1.Text = startColumnIndex.ToString();
                Xdr.ColumnOffset columnOffset1 = new Xdr.ColumnOffset();
                columnOffset1.Text = "38100";
                Xdr.RowId rowId1 = new Xdr.RowId();
                rowId1.Text = startRowIndex.ToString();
                Xdr.RowOffset rowOffset1 = new Xdr.RowOffset();
                rowOffset1.Text = "0";
    
                fromMarker1.Append(columnId1);
                fromMarker1.Append(columnOffset1);
                fromMarker1.Append(rowId1);
                fromMarker1.Append(rowOffset1);
    
                Xdr.ToMarker toMarker1 = new Xdr.ToMarker();
                Xdr.ColumnId columnId2 = new Xdr.ColumnId();
                columnId2.Text = endColumnIndex.ToString();
                Xdr.ColumnOffset columnOffset2 = new Xdr.ColumnOffset();
                columnOffset2.Text = "542925";
                Xdr.RowId rowId2 = new Xdr.RowId();
                rowId2.Text = endRowIndex.ToString();
                Xdr.RowOffset rowOffset2 = new Xdr.RowOffset();
                rowOffset2.Text = "161925";
    
                toMarker1.Append(columnId2);
                toMarker1.Append(columnOffset2);
                toMarker1.Append(rowId2);
                toMarker1.Append(rowOffset2);
    
                Xdr.Picture picture1 = new Xdr.Picture();
    
                Xdr.NonVisualPictureProperties nonVisualPictureProperties1 = new Xdr.NonVisualPictureProperties();
                Xdr.NonVisualDrawingProperties nonVisualDrawingProperties1 = new Xdr.NonVisualDrawingProperties() { Id = (UInt32Value)2U, Name = "Picture 1" };
    
                Xdr.NonVisualPictureDrawingProperties nonVisualPictureDrawingProperties1 = new Xdr.NonVisualPictureDrawingProperties();
                A.PictureLocks pictureLocks1 = new A.PictureLocks() { NoChangeAspect = true };
    
                nonVisualPictureDrawingProperties1.Append(pictureLocks1);
    
                nonVisualPictureProperties1.Append(nonVisualDrawingProperties1);
                nonVisualPictureProperties1.Append(nonVisualPictureDrawingProperties1);
    
                Xdr.BlipFill blipFill1 = new Xdr.BlipFill();
    
                A.Blip blip1 = new A.Blip() { Embed = "rId1", CompressionState = A.BlipCompressionValues.Print };
                blip1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
    
                A.BlipExtensionList blipExtensionList1 = new A.BlipExtensionList();
    
                A.BlipExtension blipExtension1 = new A.BlipExtension() { Uri = "{28A0092B-C50C-407E-A947-70E740481C1C}" };
    
                A14.UseLocalDpi useLocalDpi1 = new A14.UseLocalDpi() { Val = false };
                useLocalDpi1.AddNamespaceDeclaration("a14", "http://schemas.microsoft.com/office/drawing/2010/main");
    
                blipExtension1.Append(useLocalDpi1);
    
                blipExtensionList1.Append(blipExtension1);
    
                blip1.Append(blipExtensionList1);
    
                A.Stretch stretch1 = new A.Stretch();
                A.FillRectangle fillRectangle1 = new A.FillRectangle();
    
                stretch1.Append(fillRectangle1);
    
                blipFill1.Append(blip1);
                blipFill1.Append(stretch1);
    
                Xdr.ShapeProperties shapeProperties1 = new Xdr.ShapeProperties();
    
                A.Transform2D transform2D1 = new A.Transform2D();
                A.Offset offset1 = new A.Offset() { X = 1257300L, Y = 762000L };
                A.Extents extents1 = new A.Extents() { Cx = 2943225L, Cy = 2257425L };
    
                transform2D1.Append(offset1);
                transform2D1.Append(extents1);
    
                A.PresetGeometry presetGeometry1 = new A.PresetGeometry() { Preset = A.ShapeTypeValues.Rectangle };
                A.AdjustValueList adjustValueList1 = new A.AdjustValueList();
    
                presetGeometry1.Append(adjustValueList1);
    
                shapeProperties1.Append(transform2D1);
                shapeProperties1.Append(presetGeometry1);
    
                picture1.Append(nonVisualPictureProperties1);
                picture1.Append(blipFill1);
                picture1.Append(shapeProperties1);
                Xdr.ClientData clientData1 = new Xdr.ClientData();
    
                twoCellAnchor1.Append(fromMarker1);
                twoCellAnchor1.Append(toMarker1);
                twoCellAnchor1.Append(picture1);
                twoCellAnchor1.Append(clientData1);
    
                worksheetDrawing1.Append(twoCellAnchor1);
    
                drawingsPart1.WorksheetDrawing = worksheetDrawing1;
            }
            #endregion Helper methods
        }
    }
    

    Best,

    Vijayakumar

    Vijayakumar.bu@gmail.com | vijayakumars.svks@gmail.com


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Marked as answer by Kishor Reddy Sunday, February 12, 2012 2:30 PM
    Thursday, February 9, 2012 11:56 AM
  • Hi Vijaya,

    Thanks for your solution.Itworked  perfectly for me. Once again thanks a ton man.

    I have already marked your reply as Answer.

    I have one more doubt. If there is an image already present (or adding multiple images) what steps do we need to follow?

    Can you post it also ?

    Thanks and Regards,

    YKK Reddy

    Sunday, February 12, 2012 2:32 PM
  • This is an excellent article that has in one page explained to me how to insert an image into an Excel SpreadSheet  without having to actuall load Excel.

    I do however, have a couple of questions.

    Firstly, how do I insert my image and give it a starting position, say C5, and have it span 3 columns, and take as much space downwards as is required to maintain its aspect ratio? I see in your method InsertImage you describe the top left and bottom right corners of where the image should be, but that has destroyed the aspect of the Chrysanthemum.Jpg image that gets inserted. In GenerateDrawingsPart1Content you have a line that reads A.PictureLocks pictureLocks1 = new A.PictureLocks() { NoChangeAspect = true }; but that appears to have had no affect in preventing the aspect of the image changing?

    Secondly, could you help explain how the offset values are calculated? They seem to be very large numbers and yet move the image only slightly off the edge of the cell. For instance you have a line that reads columnOffset1.Text = "38100"; yet the image is only just off the side of the cell?

    Once again, Great article!

    Thanks,
    Karl

    Friday, May 25, 2012 11:45 AM