ClosedXML add image

Question

I am able to add in an image to an excel spreadsheet by using OpenXML. However for the rest of the program I use ClosedXML to add data. I can add data at a specific cell using the column and row index. If I can add an image to an excel (It currently is a separate layer it seems, hovering over cells), how can I add it to a cell using ClosedXML?

    //Adds an image to the excel file
    public void AddImageToExcel(SpreadsheetDocument sd, MemoryStream imagestream)
    {
        DrawingsPart dp = sd.WorkbookPart.WorksheetParts.First().AddNewPart<DrawingsPart>();
        ImagePart imgp = dp.AddImagePart(ImagePartType.Jpeg, sd.WorkbookPart.WorksheetParts.First().GetIdOfPart(dp));

        MemoryStream bmstream = new MemoryStream(imagestream.ToArray());
        bmstream.Seek(0, SeekOrigin.Begin);

        MemoryStream fs;
        using (fs = imagestream)
        {
            fs.Position = 0;
            imgp.FeedData(fs);
        }

        DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties nvdp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties();
        nvdp.Id = 1025;
        nvdp.Name = "Chart Image";
        nvdp.Description = "Image";
        DocumentFormat.OpenXml.Drawing.PictureLocks piclocks = new DocumentFormat.OpenXml.Drawing.PictureLocks();
        piclocks.NoChangeAspect = true;
        piclocks.NoChangeArrowheads = true;
        DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureDrawingProperties nvpdp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureDrawingProperties();
        nvpdp.PictureLocks = piclocks;
        DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureProperties nvpp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureProperties();
        nvpp.NonVisualDrawingProperties = nvdp;
        nvpp.NonVisualPictureDrawingProperties = nvpdp;

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

        DocumentFormat.OpenXml.Drawing.Spreadsheet.BlipFill blipfill = new DocumentFormat.OpenXml.Drawing.Spreadsheet.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(bmstream);

        DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents();
        extents.Cx = ((long)bm.Width * (long)((float)914400 / bm.HorizontalResolution));
        extents.Cy = ((long)bm.Height * (long)((float)914400 / bm.VerticalResolution));
        bm.Dispose();
        t2d.Extents = extents;
        DocumentFormat.OpenXml.Drawing.Spreadsheet.ShapeProperties sp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.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;

        DocumentFormat.OpenXml.Drawing.Spreadsheet.Position pos = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Position();

        //The position corrosponds these numbers. X= 600000 & y = 200000 adds up to 1 cell
        pos.X = 600000;
        pos.Y = 200000;

        Extent ext = new Extent();
        ext.Cx = extents.Cx;
        ext.Cy = extents.Cy;
        AbsoluteAnchor anchor = new AbsoluteAnchor();

        Xdr.Position pp = new Xdr.Position();
        pp.X = 0;
        pp.Y = 0;


        anchor.Position = pp;
        anchor.Position = pos;
        anchor.Extent = ext;
        anchor.Append(picture);
        anchor.Append(new ClientData());
        WorksheetDrawing wsd = new WorksheetDrawing();
        wsd.Append(anchor);
        Drawing drawing = new Drawing();
        drawing.Id = dp.GetIdOfPart(imgp);
        wsd.Save(dp);
        sd.WorkbookPart.WorksheetParts.First().Worksheet.Append(drawing);
        MessageBox.Show("Excel File created");
    }

This is the code I used, which I found here on stack overflow somewhere. I modified it to use a MemoryStream for the image. So firstly one of the problems I have is that I pass a spreadsheet document to the method, however I'm not sure what I can do in ClosedXML to fix this Really appreciate any help with how I could go about this. Ideally I would love to simply say

ws.Cell(colnum, rownum).Value = AddImageToExcel(wb, ImageToMemoryStream(imagelocation));

Thanks for any help!

Francois Botha · Answer 1 · 2021-01-14T14:06:24.813

10

ClosedXML now has basic image/picture support. As per https://github.com/ClosedXML/ClosedXML/wiki/How-can-I-insert-an-image :

using (var wb = new XLWorkbook())
{
  var ws = wb.AddWorksheet("Sheet1");

  var imagePath = @"c:\path\to\your\image.jpg";
  var image = ws.AddPicture(imagePath)
      .MoveTo(ws.Cell("B3").Address)
      .Scale(.5); // optional: resize picture
      
  wb.SaveAs("file.xlsx");
}

edited Jan 14 '21 at 14:06

answered Apr 17 '18 at 12:11

Francois Botha

4,520
1
34
46

Thank you sir. Is there a mistake in above code, is are imagePath and ImageLocation supposed to be one and the same? – Zeek2 Jan 13 '21 at 20:30
Also, is it possible to specify the actual image file content, for example as the location as in ASP markup, e.g. "data:Image/PNG, ...." – Zeek2 Jan 13 '21 at 20:33
1

@Zeek2 Please look at the range of overloads for `AddPicture` at https://github.com/ClosedXML/ClosedXML/blob/f2063a73cdd7c4d95f920de4241e95858e4b3b77/ClosedXML/Excel/IXLWorksheet.cs#L467 – Francois Botha Jan 14 '21 at 14:08

Ajwhiteway · Answer 2 · 2015-07-08T13:28:38.567

5

I little bit late here, but in case someone else comes looking I've added (limited) image support to ClosedXML.

Fork can be found at https://closedxml.codeplex.com/SourceControl/network/forks/ajwhiteway/ClosedXMLImageSupport

EDIT: Adding some detail to how it works.

New classes, XLPicture and XLMarker, have been added.

Picture can be created by

  XLPicture pic = new XLPicture
  {
    NoChangeAspect = true,
    NoMove = true,
    NoResize = true,
    ImageStream = fIn,
    Name = "Test Image"
  };

As of now it only accepts streams, not files, and it only spits out JPEGs. This can be changed if there is demand.

Once your picture is created you create a marker for where it goes

  XLMarker fMark = new XLMarker
  {
    ColumnId = 2,
    RowId = 2
  };
  pic.AddMarker(fMark);

If you add a single marker it will embed the image directly into the cell. If you add 2 markers it will span the image across the two markers.

Then to add it to the sheet just

 worksheet.AddPicture(pic);

Cheers guys.

edited Jul 08 '15 at 13:28

answered Jul 08 '15 at 13:22

Ajwhiteway

986
1
9
23

Fork link doesnt work. Could you please share the DLL file you created? Thank you for the effort. – Murat Nov 12 '15 at 09:11
1

Sure,https://drive.google.com/file/d/0BxZ-lcOB4nQTQjlOVjhhU3Eybmc/view?usp=sharing – Ajwhiteway Nov 12 '15 at 17:24
I used your dll with the following code and cannot open the excel file after inserting image. it gets corrupted: `FileStream fs = new FileStream("c:\\tempdir\\sign3.png", FileMode.Open); MemoryStream memo = new MemoryStream(); fs.CopyTo(memo); memo.Position = 0; XLPicture pic = new XLPicture{... ImageStream = memo,...}; XLMarker fMark = new XLMarker { ColumnId = 1, RowId = 1 }; pic.AddMarker(fMark); ws.AddPicture(pic);` – Murat Nov 17 '15 at 15:47
Currently I just coded support in for JPG's. Try converting to a JPG and see if that helps. Adding PNG's shouldn't be too hard I don't think.... I forgot that I take the stream and make a bitmap, then spit out JPGs, if you could attach the excel file I can look at the issue. – Ajwhiteway Nov 17 '15 at 17:23
Hi, i found the problem. If you open an existing excel file that already contains some images or drawings, it gives exception. If i remove the images, it adds the image without any problem, whether it is png or jpg, because i convert the image file to stream. Do you recommend any solution for adding image to an excel that already contains an image? – Murat Nov 18 '15 at 09:52
1

So I have an idea of what is going on. I think its adding a duplicate drawing part to the work sheet. I'll see if I can correct this and recompile the DLL and post it again when I have a minute. – Ajwhiteway Nov 18 '15 at 17:40
Works great and is easy to use. Thanks! – MIKE Mar 08 '16 at 22:30
2

Please note that ClosedXML now supports adding images without needing @Ajwhiteway's fork. The ClosedXML API is documented at https://github.com/ClosedXML/ClosedXML/wiki/How-can-I-insert-an-image – Francois Botha Apr 17 '18 at 12:10
@Francis Botha Any support for other file formats, esp. .png? – Zeek2 Jan 13 '21 at 16:22

score 2 · Answer 3 · answered Oct 29 '18 at 08:39

This feature has been included in closed XML from version 0.89.0

This code is for MVC5 but you can use it else where as well.

        public ActionResult Contact()
    {
        //Creating excelsheet and adding workbook with name Picture
        XLWorkbook workbook = new XLWorkbook();
        IXLWorksheet worksheet = workbook.Worksheets.Add("Picture");

        //Adjusting size of the excel cell as per requirement
        worksheet.Column(3).Width = 60;
        worksheet.Row(3).Height = 50;

        var imagePath = @"~/Images/Picture2.png";

        //Adding image to the worksheet and moving it to the cell
        var image = worksheet.AddPicture(Server.MapPath(imagePath))
            .MoveTo(worksheet.Cell(3, 3).Address);
        image.Name = "Logo";

        //Scaling down image as per our cell size
        image.ScaleWidth(.5);
        image.ScaleHeight(.3);

        //Formating the cell with border and color
        worksheet.Cell(3, 3).Style.Border.OutsideBorder = XLBorderStyleValues.Thick;
        worksheet.Cell(3, 3).Style.Border.OutsideBorderColor = XLColor.Blue;

        //asving worksheet in memory stream
        MemoryStream stream = new MemoryStream();
        workbook.SaveAs(stream);
        stream.Position = 0;
        //returning the final excelsheet with name Picture
        return new FileStreamResult(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
        { FileDownloadName = "Pictures.xlsx" };
    }

You can refer to the link for detailed explaination. Hope it helps somebody

https://www.youtube.com/watch?v=8X0kg0YzI2g&t=242s

ClosedXML add image

3 Answers3

Linked