This project has moved. For the latest updates, please go here.

Insert image from file

Apr 4, 2011 at 9:21 AM
Edited Apr 4, 2011 at 9:23 AM

Hi,

I want to insert an image from file aligned with a range topright position. Is this possible with ClosedXML?

Thanks in advance.

Coordinator
Apr 5, 2011 at 5:26 AM

Not yet, it's on the to-do-list though...

Apr 7, 2011 at 7:02 AM

OK, I'm waiting for it. I temporarily solved the problem in OpenXML.

May 20, 2011 at 11:13 AM

hi Maci,

can you please help me to short out how to insert an image file in existing excel file's particular sheet(say Sheet1/Sheet2) and Cell(say O24:W140)

Thanks

Chandan

May 21, 2011 at 9:52 AM
Edited May 21, 2011 at 9:55 AM

Hi Chandan,

Unfortunately I don’t have a general solution for this task, because I’ve needed it only once. During the task I had to place a picture in the first column of the first sheet ( the picture is 4 columns wide: 0 - 3 ), but depending on the content I’ve placed it in various rows. I’ve completed the task by this way: 

  1. I created two Excel Files: an empty one ( Empty.xlsx ), and another one which contained the image ( Image.xlsx ). In the Image.xlsx I set the properties of the image to the final status.
  2. In the “Compare files…” function of the Open XML SDK 2.0 Productivity Tool I added the Empty.xlsx file to the sources, and added the Image.xlsx file as the target. ( ->OK )
  3. In the “File Comparison” window I choose the “View Package Code” option, copied the code into a file and modified that the number of the rows of the image could be adjusted. The position of the sheet and the columns also can be made dynamic, but I needn’t do that. The picture will be embedded into the code, but it’s not hard to modify.
  4. In the ASP.NET application: after I had processed the export file in ClosedXML, with the help of the class which was created in the 3rd step I placed the picture in it.

My code:

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using A = DocumentFormat.OpenXml.Drawing;
using Xdr = DocumentFormat.OpenXml.Drawing.Spreadsheet;

namespace MyApp.Services
{
  public class GeneratedClass
  {
    private System.Collections.Generic.IDictionary<System.String, OpenXmlPart> UriPartDictionary = new System.Collections.Generic.Dictionary<System.String, OpenXmlPart>();
    private System.Collections.Generic.IDictionary<System.String, DataPart> UriNewDataPartDictionary = new System.Collections.Generic.Dictionary<System.String, DataPart>();
    private SpreadsheetDocument document;

    public void InsertImage(string filePath, int row)
    {
      using (document = SpreadsheetDocument.Open(filePath, true))
      {
        ChangeParts(row);
      }
    }

    private void ChangeParts(int row)
    {
      //Stores the referrences to all the parts in a dictionary.
      BuildUriPartDictionary();
      //Adds new parts or new relationships.
      AddParts(row);
      ChangeWorksheetPart(((WorksheetPart)UriPartDictionary["/xl/worksheets/sheet1.xml"]));
    }

    /// <summary>
    /// Stores the references to all the parts in the package.
    /// They could be retrieved by their URIs later.
    /// </summary>
    private void BuildUriPartDictionary()
    {
      System.Collections.Generic.Queue<OpenXmlPartContainer> queue = new System.Collections.Generic.Queue<OpenXmlPartContainer>();
      queue.Enqueue(document);
      while (queue.Count > 0)
      {
        foreach (var part in queue.Dequeue().Parts)
        {
          if (!UriPartDictionary.Keys.Contains(part.OpenXmlPart.Uri.ToString()))
          {
            UriPartDictionary.Add(part.OpenXmlPart.Uri.ToString(), part.OpenXmlPart);
            queue.Enqueue(part.OpenXmlPart);
          }
        }
      }
    }

    /// <summary>
    /// Adds new parts or new relationship between parts.
    /// </summary>
    private void AddParts(int row)
    {
      //Generate new parts.
      DrawingsPart drawingsPart1 = UriPartDictionary["/xl/worksheets/sheet1.xml"].AddNewPart<DrawingsPart>("rId2");
      GenerateDrawingsPart1Content(drawingsPart1, row);

      ImagePart imagePart1 = drawingsPart1.AddNewPart<ImagePart>("image/png", "rId1");
      GenerateImagePart1Content(imagePart1);

    }

    private void GenerateDrawingsPart1Content(DrawingsPart drawingsPart1, int row)
    {
      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();

      Xdr.FromMarker fromMarker1 = new Xdr.FromMarker();
      Xdr.ColumnId columnId1 = new Xdr.ColumnId();
      columnId1.Text = "0";
      Xdr.ColumnOffset columnOffset1 = new Xdr.ColumnOffset();
      columnOffset1.Text = "981075";
      Xdr.RowId rowId1 = new Xdr.RowId();
      rowId1.Text = row.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 = "3";
      Xdr.ColumnOffset columnOffset2 = new Xdr.ColumnOffset();
      columnOffset2.Text = "94596";
      Xdr.RowId rowId2 = new Xdr.RowId();
      rowId2.Text = row.ToString();
      Xdr.RowOffset rowOffset2 = new Xdr.RowOffset();
      rowOffset2.Text = "952381";

      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 = "Kép 1", Description = "4MArrow.png" };

      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.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 = 981075L, Y = 1485900L };
      A.Extents extents1 = new A.Extents() { Cx = 5238096L, Cy = 952381L };

      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;
    }

    private void GenerateImagePart1Content(ImagePart imagePart1)
    {
      System.IO.Stream data = GetBinaryDataStream(imagePart1Data);
      imagePart1.FeedData(data);
      data.Close();
    }

    private void ChangeWorksheetPart(WorksheetPart worksheetPart2)
    {
      Worksheet ws = worksheetPart2.Worksheet;
      TableParts tps = ws.GetFirstChild<TableParts>();
      Drawing d = new Drawing() { Id = "rId2" };
      ws.InsertBefore(d, tps);
      tps.Remove();
    }

    #region Binary Data

    private string imagePart1Data = ""; // TODO: Image's BASE64 string goes here

    private System.IO.Stream GetBinaryDataStream(string base64String)
    {
      return new System.IO.MemoryStream(System.Convert.FromBase64String(base64String));
    }

    #endregion
  }
}

Greets

Maci

Oct 9, 2013 at 1:59 PM
I wonder, is inserting images still on the todo-list? (I cannot find it...)

Benno
May 22, 2014 at 11:08 AM
very sad ;(
Jul 8, 2015 at 6:12 PM
I added (limited) support for this in a branch which can be found at
https://closedxml.codeplex.com/SourceControl/network/forks/ajwhiteway/ClosedXMLImageSupport

I give a quick run down of how to use it at
http://stackoverflow.com/questions/18661239/closedxml-add-image

My testing code is in Program.cs in the Sandbox of the code.

Hopefully this helps someone else.

Cheers