none
Open XML SDK. Insert image to excel sheet not work if sheet contains comments RRS feed

  • Question

  • Hi, I'm trying insert image to excel sheet. And my code works. But if  sheet contains comments I get an error after image added.

    How could I solve the problem. Please, help. Thanks.

    Here is code:

    private void InsertImage (Image barcodeImage)
            {
                try
                {
                    
                    WorkbookPart workbookpart = _doc.WorkbookPart;
                    WorksheetPart worksheetPart = GetSheetPartByName(workbookpart);
                    
                    var stream = new System.IO.MemoryStream();
                    barcodeImage.Save(stream, System.Drawing.Imaging.ImageFormat.Jpeg);
                    stream.Position = 0;
    
                    var 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;
    
                    var imagePart = drawingsPart.AddImagePart(ImagePartType.Jpeg);
    
                    imagePart.FeedData(stream);
                    Bitmap bm = new Bitmap(barcodeImage);
    
                    DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents();
                    var extentsCx = (long)bm.Width * (long)((float)914400 / bm.HorizontalResolution);
                    var extentsCy = (long)bm.Height * (long)((float)914400 / bm.VerticalResolution);
                    bm.Dispose();
    
                    var colOffset = 0;
                    var rowOffset = 0;
                    int colNumber = 4;
                    int rowNumber = 4;
    
                    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 = "barcode" },
                                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);
    
                    _doc.WorkbookPart.Workbook.Save();                
                   
                }
                catch(Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }

    Tuesday, December 12, 2017 4:28 AM

Answers

  • Hello Nata_lia,

    It works for me to add picture. Here is my whole code. In my code, I used picture's file name and FileStream to create imagepart. Hope it won't effect the test result. You could refer to the code and test again.

    private void InsertImage(SpreadsheetDocument _doc)
            {
                try
                {
    
                    WorkbookPart workbookpart = _doc.WorkbookPart;
                    WorksheetPart worksheetPart = workbookpart.GetPartsOfType<WorksheetPart>().First();
    
    
                    var drawingsPart = worksheetPart.AddNewPart<DrawingsPart>();
    
                    if (!worksheetPart.Worksheet.ChildElements.OfType<Drawing>().Any())
                    {
                        
                        if (worksheetPart.Worksheet.Elements<LegacyDrawing>().Count() > 0)
                        {
                            LegacyDrawing lg = worksheetPart.Worksheet.Elements<LegacyDrawing>().First();
                            worksheetPart.Worksheet.InsertBefore<Drawing>(new Drawing { Id = worksheetPart.GetIdOfPart(drawingsPart) }, lg);
                        }
                        else {
                            worksheetPart.Worksheet.Append(new Drawing { Id = worksheetPart.GetIdOfPart(drawingsPart) });
                        }
                    }
    
                    if (drawingsPart.WorksheetDrawing == null)
                    {
                        drawingsPart.WorksheetDrawing = new Xdr.WorksheetDrawing();
                    }
    
                    var worksheetDrawing = drawingsPart.WorksheetDrawing;
    
                    var imagePart = drawingsPart.AddImagePart(ImagePartType.Jpeg);
                    String imageFileName = @"C:\Users\Administrator\Pictures\Capture.PNG";
                    using (FileStream stream = new FileStream(imageFileName, FileMode.Open))
                    {
                        imagePart.FeedData(stream);
                    }
    
                    Bitmap bm = new Bitmap(imageFileName);
    
                    DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents();
                    var extentsCx = (long)bm.Width * (long)((float)914400 / bm.HorizontalResolution);
                    var extentsCy = (long)bm.Height * (long)((float)914400 / bm.VerticalResolution);
                    bm.Dispose();
    
                    var colOffset = 0;
                    var rowOffset = 0;
                    int colNumber = 4;
                    int rowNumber = 4;
    
                    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 = "barcode" },
                                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);
    
                    _doc.WorkbookPart.Workbook.Save();
    
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }

    If the code still delete or hide comments for you, please share a simply excel file so we could try to reproduce your issue. You could share the file via Cloud Storage, such as One Drive, and the put address here. Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Nata_lia Monday, December 18, 2017 4:05 AM
    Friday, December 15, 2017 7:16 AM

All replies

  • Hello Nata_lia,

    What's Xdr and A? How do you get the Image object?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 13, 2017 10:20 AM
  • using Xdr = DocumentFormat.OpenXml.Drawing.Spreadsheet;
    using A = DocumentFormat.OpenXml.Drawing;

    Image it is a simple image loaded from disk. Image object is correct. If I try insert image to excel file without comments, it works. Perhaps, the reason of error is the conflict between DrawingPart and VmlDrawingPart or they links.



    • Edited by Nata_lia Thursday, December 14, 2017 3:58 AM
    Thursday, December 14, 2017 3:31 AM
  • Hello Nat_lia,

    I could reproduce your issue now. 

    I think you need add Drawing elements before LegacyDrawing elements instead of appending it to the end.

    Here is the example.

    if (!worksheetPart.Worksheet.ChildElements.OfType<Drawing>().Any())
                    {
                        
                        if (worksheetPart.Worksheet.Elements<LegacyDrawing>().Count() > 0)
                        {
                            LegacyDrawing lg = worksheetPart.Worksheet.Elements<LegacyDrawing>().First();
                            worksheetPart.Worksheet.InsertBefore<Drawing>(new Drawing { Id = worksheetPart.GetIdOfPart(drawingsPart) }, lg);
                        }
                        else {
                            worksheetPart.Worksheet.Append(new Drawing { Id = worksheetPart.GetIdOfPart(drawingsPart) });
                        }
                    }

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 14, 2017 9:05 AM
  • Thanks for reply.

    Code works, but comments are delete or not visible

    Thursday, December 14, 2017 10:57 AM
  • Hello Nata_lia,

    It works for me to add picture. Here is my whole code. In my code, I used picture's file name and FileStream to create imagepart. Hope it won't effect the test result. You could refer to the code and test again.

    private void InsertImage(SpreadsheetDocument _doc)
            {
                try
                {
    
                    WorkbookPart workbookpart = _doc.WorkbookPart;
                    WorksheetPart worksheetPart = workbookpart.GetPartsOfType<WorksheetPart>().First();
    
    
                    var drawingsPart = worksheetPart.AddNewPart<DrawingsPart>();
    
                    if (!worksheetPart.Worksheet.ChildElements.OfType<Drawing>().Any())
                    {
                        
                        if (worksheetPart.Worksheet.Elements<LegacyDrawing>().Count() > 0)
                        {
                            LegacyDrawing lg = worksheetPart.Worksheet.Elements<LegacyDrawing>().First();
                            worksheetPart.Worksheet.InsertBefore<Drawing>(new Drawing { Id = worksheetPart.GetIdOfPart(drawingsPart) }, lg);
                        }
                        else {
                            worksheetPart.Worksheet.Append(new Drawing { Id = worksheetPart.GetIdOfPart(drawingsPart) });
                        }
                    }
    
                    if (drawingsPart.WorksheetDrawing == null)
                    {
                        drawingsPart.WorksheetDrawing = new Xdr.WorksheetDrawing();
                    }
    
                    var worksheetDrawing = drawingsPart.WorksheetDrawing;
    
                    var imagePart = drawingsPart.AddImagePart(ImagePartType.Jpeg);
                    String imageFileName = @"C:\Users\Administrator\Pictures\Capture.PNG";
                    using (FileStream stream = new FileStream(imageFileName, FileMode.Open))
                    {
                        imagePart.FeedData(stream);
                    }
    
                    Bitmap bm = new Bitmap(imageFileName);
    
                    DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents();
                    var extentsCx = (long)bm.Width * (long)((float)914400 / bm.HorizontalResolution);
                    var extentsCy = (long)bm.Height * (long)((float)914400 / bm.VerticalResolution);
                    bm.Dispose();
    
                    var colOffset = 0;
                    var rowOffset = 0;
                    int colNumber = 4;
                    int rowNumber = 4;
    
                    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 = "barcode" },
                                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);
    
                    _doc.WorkbookPart.Workbook.Save();
    
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }

    If the code still delete or hide comments for you, please share a simply excel file so we could try to reproduce your issue. You could share the file via Cloud Storage, such as One Drive, and the put address here. Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Nata_lia Monday, December 18, 2017 4:05 AM
    Friday, December 15, 2017 7:16 AM
  • Sorry, code works. Perhaps, I forgot to delete method removing comments. Thank you very match, Terry
    Monday, December 18, 2017 4:04 AM