none
Unable to add an image to an Excel Spreadsheet using OpenXML SDK 2.5 RRS feed

  • Question

  • I have been tasked with what I thought would be a relatively simple job of processing an Excel Spreadsheet, reading some values from a row, drawing an imaged based on that information and then place that image in a cell.

    I downloaded Open XML SDK 2.5, used Google and started. It's a lot more complicated than I thought, and by complicated I mean a lot more lines of code, but I found some examples and wrote the code. Parsing the information from the cells in the row was simple, as was drawing the image, but adding the image back to the worksheet so far has failed. In fact, in my initial trial, the resulting file, after adding 10 images is considerably smaller than the original and on top of that it refuses to open with a message that essential parts are missing.

    So instead of working with live data, I created an extremely simple spreadsheet. This has "A" in A1, 24 in B1 and 36 in B2. I parse this information, and draw a line from the top left to bottom right of a rectangle 24 by 36. Then I add the image back to the worksheet. Examination of the resulting file shows that the image is there, but opening the spreadsheet in Excel doesn't show the image.

    I used this as an example of code that is supposed to do this. I modified the code, mainly to make the function an extension Method on Worksheet. Other than that, the code is intended to be identical. But it doesn't work. 

    Below are all the various pieces of code. First the Main program:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    using DocumentFormat.OpenXml.Drawing;
    using DocumentFormat.OpenXml.Drawing.Pictures;
    using DocumentFormat.OpenXml.Drawing.Spreadsheet;
    using System.Drawing;
    using OpenXMLExtensions;
    using System.IO;
    
    namespace ExcelApp
    {
        delegate string GetCell(int i);
        class Program
        {
            enum columns { Mark, W, H };
            static void Main(string[] args)
            {
                using (SpreadsheetDocument mySpreadsheet = SpreadsheetDocument.Open(@"C:\Users\user\Documents\practise.xlsx", true))
                {
    
                    SharedStringTablePart sstp = mySpreadsheet.WorkbookPart.SharedStringTablePart;
                    SharedStringTable sst = sstp.SharedStringTable;
    
                    WorkbookPart wb = mySpreadsheet.WorkbookPart;
                    IEnumerable<Sheet> sheets = mySpreadsheet.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Sheet1");
                    if (sheets.Count() == 0) return;
    
                    WorksheetPart worksheetPart = (WorksheetPart)mySpreadsheet.WorkbookPart.GetPartById(sheets.First().Id);
    
                    Worksheet worksheet = worksheetPart.Worksheet;
                    int startRow = 1;
                    int lastRow = 1;
    
                    foreach (Row row in worksheet.Descendants<Row>().Where(r => r.RowIndex.Value >= startRow && r.RowIndex.Value <= lastRow))
                    {
                        uint rowIndex = row.RowIndex.Value;
                        Cell[] cells = row.Descendants<Cell>().ToArray();
                        GetCell gc = delegate(int i)
                        {
                            return (cells[i].CellValue == null) ? "0" : cells[i].CellValue.Text;
                        };
                        Artifact artifact = new Artifact();
                        artifact.Mark = sst.ElementAt(int.Parse(gc((int)columns.Mark))).InnerText;
                        artifact.H = double.Parse(gc((int)columns.H));
                        artifact.W = double.Parse(gc((int)columns.W));
                        Bitmap bmp = artifact.Draw();
                        worksheet.InsertImage(rowIndex * 100, rowIndex * 100, 100, 200, bmp, ImagePartType.Bmp);
                    }
                    worksheet.Save();
                }
            }
        }
    
    }
    

    And now the object that is drawn:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Drawing;
    
    namespace ExcelApp
    {
        class Artifact
        {
            private const int ppi = 8; // Since the dimensions are in inches ppi is the "Pixels per inch" for the drawing
            public string Mark { get; set; }
            public int Quantity { get; set; }
            public double W { get; set; }
            public double H { get; set; }
    
            public Artifact() { }
            public Bitmap Draw()
            {
                Bitmap bmp = new Bitmap((int)W * ppi, (int)H * ppi);
                using (Graphics go = Graphics.FromImage(bmp))
                {
                    Pen pen = new Pen(Color.Red);
                    go.DrawLine(pen, new Point(0, 0), new Point(bmp.Width - 1, bmp.Height - 1));
                                    return bmp;
                }
            }
        }
    }
    

    And now the Extension Method based on the link shyown above:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    using DocumentFormat.OpenXml.Drawing;
    using DocumentFormat.OpenXml.Drawing.Pictures;
    using S = DocumentFormat.OpenXml.Drawing.Spreadsheet;
    using System.IO;
    using System.Drawing;
    using DocumentFormat.OpenXml.Drawing.Spreadsheet;
    using System.Drawing.Imaging;
    
    namespace OpenXMLExtensions
    {
        public static class OpenXMLExtensions
        {
            public static void InsertImage(this Worksheet ws, long x, long y, long? width, long? height, Bitmap bm, ImagePartType ipt)
            {
                try
                {
                    WorksheetPart wsp = ws.WorksheetPart;
                    DrawingsPart dp;
                    ImagePart imgp;
                    WorksheetDrawing wsd;
    
                    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 (MemoryStream ms = new MemoryStream())
                    {
                        bm.Save(ms, ImageFormat.Bmp);
                        ms.Seek(0, SeekOrigin.Begin);
                        imgp.FeedData(ms);
                    }
    
                    int imageNumber = dp.ImageParts.Count<ImagePart>();
                    if (imageNumber == 1)
                    {
                        Drawing drawing = new Drawing();
                        drawing.Id = dp.GetIdOfPart(imgp);
                        ws.Append(drawing);
                    }
    
                    S.NonVisualDrawingProperties nvdp = new S.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;
                    S.NonVisualPictureDrawingProperties nvpdp = new S.NonVisualPictureDrawingProperties();
                    nvpdp.PictureLocks = picLocks;
                    S.NonVisualPictureProperties nvpp = new S.NonVisualPictureProperties();
                    nvpp.NonVisualDrawingProperties = nvdp;
                    nvpp.NonVisualPictureDrawingProperties = nvpdp;
    
                    DocumentFormat.OpenXml.Drawing.Stretch stretch = new DocumentFormat.OpenXml.Drawing.Stretch();
                    stretch.FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle();
    
                    S.BlipFill blipFill = new S.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(image);
    
                    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;
                    S.ShapeProperties sp = new S.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;
    
                    S.Position pos = new S.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
                }
            }
        }
    }
    

    If anyone can spot what I am doing wrong I would be grateful for the help.

    It also occurs to me that it would be a good idea to develop a lot of Extension Methods to augment the SDK because that extension method I wrote is a vast amount of code to write just to add a picture into a cell. If anyone has started such a project I'd be interested in hearing from them.

    Monday, September 15, 2014 4:48 AM

Answers

  • Hi Martin,

    Welcome to MSDN forum.

    To insert an image into a worksheet through OpenXML SDK, you could insert an image and save this workbook manually. After that, you could check the difference between old workbook and new workbook through OpenXML SDK tool.

    According to my investigation, there will be two elements added if I insert an image into a worksheet:

    1. DrawingsPart under worksheet part

    2. Drawing element under worksheet element

    Please note:

    1. FromMarker and ToMarker elements are used to locate the position of this image.

    2. BlipFill element needs to link to the ImagePart

    r:embed="ImagePartID"

    Here is a C# sample code for your reference:

    using Xdr = DocumentFormat.OpenXml.Drawing.Spreadsheet;
    using A = DocumentFormat.OpenXml.Drawing;
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    
    static void Main(string[] args)
            {
                AddImage(@"D:\Book4.xlsx", @"D:\temp\1.jpg");
            }
    
            static void AddImage(string filepath,string imagepath)
            {
                using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(filepath, true))
                {
                    WorkbookPart wb = spreadSheet.WorkbookPart;
                    WorksheetPart wp = wb.WorksheetParts.FirstOrDefault();
                    DrawingsPart drawingsPart1 = wp.AddNewPart<DrawingsPart>();
                    ImagePart imagePart = drawingsPart1.AddImagePart(ImagePartType.Jpeg);
                    using (FileStream stream = new FileStream(imagepath, FileMode.Open))
                    {
                        imagePart.FeedData(stream);
                    }
                    GenerateDrawingsPart1Content(drawingsPart1, drawingsPart1.GetIdOfPart(imagePart));
    
                    
                    Worksheet worksheet = wp.Worksheet;
                    Drawing drawing1 = new Drawing() { Id = wp.GetIdOfPart(drawingsPart1) };
                    worksheet.Append(drawing1);
                }
            }
    
            static void GenerateDrawingsPart1Content(DrawingsPart drawingsPart1,string partid)
            {
                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 = "7";
                Xdr.ColumnOffset columnOffset1 = new Xdr.ColumnOffset();
                columnOffset1.Text = "0";
                Xdr.RowId rowId1 = new Xdr.RowId();
                rowId1.Text = "9";
                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 = "23";
                Xdr.ColumnOffset columnOffset2 = new Xdr.ColumnOffset();
                columnOffset2.Text = "304800";
                Xdr.RowId rowId2 = new Xdr.RowId();
                rowId2.Text = "38";
                Xdr.RowOffset rowOffset2 = new Xdr.RowOffset();
                rowOffset2.Text = "13854";
    
                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 = partid };
                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 = 4267200L, Y = 1714500L };
                A.Extents extents1 = new A.Extents() { Cx = 10058400L, Cy = 5538354L };
    
                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;
            }

    In this sample, I provide two parameters:

    1. Workbook file path

    2. Image file path

    It is a little different from yours,

    Regards,

    George.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, September 16, 2014 3:25 AM
    Moderator
  • Hi Martin,

    Some code I provided was generated by OpenXML SDK tool.

    The steps:

    1. I inserted an image into a worksheet and close this workbook.

    2. open this workbook with OpenXML SDK tool. You could find this tool in OpenXML SDK (OpenXmlSdkTool.exe) installation directory.

    As I mentioned,  FromMarker and ToMarker elements are used to locate the position of this image. You could insert this image into the workbook manually and check these elements and reflected code.

    ColumnOffset is used to specify the column offset within a cell. The units for which this attribute is specified in reside within the simple type definition referenced below.

    The possible values for this element are defined by the ST_Coordinate simple type (§20.1.10.16).

    Regards,

    George.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, September 17, 2014 2:44 AM
    Moderator

All replies

  • Hi Martin,

    Welcome to MSDN forum.

    To insert an image into a worksheet through OpenXML SDK, you could insert an image and save this workbook manually. After that, you could check the difference between old workbook and new workbook through OpenXML SDK tool.

    According to my investigation, there will be two elements added if I insert an image into a worksheet:

    1. DrawingsPart under worksheet part

    2. Drawing element under worksheet element

    Please note:

    1. FromMarker and ToMarker elements are used to locate the position of this image.

    2. BlipFill element needs to link to the ImagePart

    r:embed="ImagePartID"

    Here is a C# sample code for your reference:

    using Xdr = DocumentFormat.OpenXml.Drawing.Spreadsheet;
    using A = DocumentFormat.OpenXml.Drawing;
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    
    static void Main(string[] args)
            {
                AddImage(@"D:\Book4.xlsx", @"D:\temp\1.jpg");
            }
    
            static void AddImage(string filepath,string imagepath)
            {
                using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(filepath, true))
                {
                    WorkbookPart wb = spreadSheet.WorkbookPart;
                    WorksheetPart wp = wb.WorksheetParts.FirstOrDefault();
                    DrawingsPart drawingsPart1 = wp.AddNewPart<DrawingsPart>();
                    ImagePart imagePart = drawingsPart1.AddImagePart(ImagePartType.Jpeg);
                    using (FileStream stream = new FileStream(imagepath, FileMode.Open))
                    {
                        imagePart.FeedData(stream);
                    }
                    GenerateDrawingsPart1Content(drawingsPart1, drawingsPart1.GetIdOfPart(imagePart));
    
                    
                    Worksheet worksheet = wp.Worksheet;
                    Drawing drawing1 = new Drawing() { Id = wp.GetIdOfPart(drawingsPart1) };
                    worksheet.Append(drawing1);
                }
            }
    
            static void GenerateDrawingsPart1Content(DrawingsPart drawingsPart1,string partid)
            {
                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 = "7";
                Xdr.ColumnOffset columnOffset1 = new Xdr.ColumnOffset();
                columnOffset1.Text = "0";
                Xdr.RowId rowId1 = new Xdr.RowId();
                rowId1.Text = "9";
                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 = "23";
                Xdr.ColumnOffset columnOffset2 = new Xdr.ColumnOffset();
                columnOffset2.Text = "304800";
                Xdr.RowId rowId2 = new Xdr.RowId();
                rowId2.Text = "38";
                Xdr.RowOffset rowOffset2 = new Xdr.RowOffset();
                rowOffset2.Text = "13854";
    
                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 = partid };
                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 = 4267200L, Y = 1714500L };
                A.Extents extents1 = new A.Extents() { Cx = 10058400L, Cy = 5538354L };
    
                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;
            }

    In this sample, I provide two parameters:

    1. Workbook file path

    2. Image file path

    It is a little different from yours,

    Regards,

    George.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, September 16, 2014 3:25 AM
    Moderator
  • I carefully reviewed your answer but apart from the fact that you use a TwoCellAnchor and my code uses an AbsoluteAnchor I saw no fundamental difference. So I examined the generated XML very carefully and I discovered the real difference was in the extents. So I examined my code, which I had copied, to see what I did for extents. My code uses the following snippet:

    if (width == null)    
        extents.Cx = (long)bm.Width * (long)((float)914400 /  bm.HorizontalResolution);
    else    
        extents.Cx = width;
    I had specified a value of 100 when I called the function. So I googled 914400 and discovered EMUs (English Metric Units). So I changed the height and width to nulls and it worked. I assume that 100 EMUs is so small it couldn't be seen.

    I will post an amended Extension Method when I have added some more options.
    Tuesday, September 16, 2014 5:42 PM
  •             Xdr.FromMarker fromMarker1 = new Xdr.FromMarker();
                Xdr.ColumnId columnId1 = new Xdr.ColumnId();
                columnId1.Text = "7";
                Xdr.ColumnOffset columnOffset1 = new Xdr.ColumnOffset();
                columnOffset1.Text = "0";
                Xdr.RowId rowId1 = new Xdr.RowId();
                rowId1.Text = "9";
                Xdr.RowOffset rowOffset1 = new Xdr.RowOffset();
                rowOffset1.Text = "0";
    

    There are lots of magical numbers in this text. Why is columnId1.Text set to 7? Or the rowId1.Text set to 9? And further down in your code:

                Xdr.ToMarker toMarker1 = new Xdr.ToMarker();
                Xdr.ColumnId columnId2 = new Xdr.ColumnId();
                columnId2.Text = "23";
                Xdr.ColumnOffset columnOffset2 = new Xdr.ColumnOffset();
                columnOffset2.Text = "304800";
                Xdr.RowId rowId2 = new Xdr.RowId();
                rowId2.Text = "38";
                Xdr.RowOffset rowOffset2 = new Xdr.RowOffset();
                rowOffset2.Text = "13854";
    
    Here there are truly magical numbers like 304800 and 13854? Are these magical numbers documented anywhere? I am going to make a wild assed guess that the big numbers in the offset fields are EMUs but what are they offsets from? Similarly I might guess that columnId 23 is column "X" - Am I right? I can find documentation that these fields exist in MarkerType class, but no documentation as to what they do.

    Tuesday, September 16, 2014 8:49 PM
  • Hi Martin,

    Some code I provided was generated by OpenXML SDK tool.

    The steps:

    1. I inserted an image into a worksheet and close this workbook.

    2. open this workbook with OpenXML SDK tool. You could find this tool in OpenXML SDK (OpenXmlSdkTool.exe) installation directory.

    As I mentioned,  FromMarker and ToMarker elements are used to locate the position of this image. You could insert this image into the workbook manually and check these elements and reflected code.

    ColumnOffset is used to specify the column offset within a cell. The units for which this attribute is specified in reside within the simple type definition referenced below.

    The possible values for this element are defined by the ST_Coordinate simple type (§20.1.10.16).

    Regards,

    George.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, September 17, 2014 2:44 AM
    Moderator