This is a continuation of a series of articles on using Excel Open XML. Previously we looked at how to create stylesheets. Today, we’ll look at inserting images. (You will need the Open XML SDK from Microsoft)
Similar to when I was writing code for the stylesheet creation, I have little idea on why any piece of code has to exist for an image to be inserted successfully. All I can advise you is to insert one image into an Excel file, save it as an Excel Open XML format, rename it to a .zip extension, unzip it, and study the contents like crazy. That’s what I did.
Here, I am going to show you the shortcut, and relieve you of all that pain. You will learn to create a simple Excel file, with just one Excel sheet in it. And on that one sheet, is an image. That’s it. You’ll be surprised to know how much code you have to write…
static void Main(string args)
string sFile = "ExcelOpenXmlWithImage.xlsx";
private static void BuildWorkbook(string filename)
using (SpreadsheetDocument xl = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
WorkbookPart wbp = xl.AddWorkbookPart();
WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
Workbook wb = new Workbook();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
Worksheet ws = new Worksheet();
SheetData sd = new SheetData();
string sImagePath = "polymathlogo.png";
DrawingsPart dp = wsp.AddNewPart<DrawingsPart>();
ImagePart imgp = dp.AddImagePart(ImagePartType.Png, wsp.GetIdOfPart(dp));
using (FileStream fs = new FileStream(sImagePath, FileMode.Open))
NonVisualDrawingProperties nvdp = new NonVisualDrawingProperties();
nvdp.Id = 1025;
nvdp.Name = "Picture 1";
nvdp.Description = "polymathlogo";
DocumentFormat.OpenXml.Drawing.PictureLocks picLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks();
picLocks.NoChangeAspect = true;
picLocks.NoChangeArrowheads = true;
NonVisualPictureDrawingProperties nvpdp = new NonVisualPictureDrawingProperties();
nvpdp.PictureLocks = picLocks;
NonVisualPictureProperties nvpp = new NonVisualPictureProperties();
nvpp.NonVisualDrawingProperties = nvdp;
nvpp.NonVisualPictureDrawingProperties = nvpdp;
DocumentFormat.OpenXml.Drawing.Stretch stretch = new DocumentFormat.OpenXml.Drawing.Stretch();
stretch.FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle();
BlipFill blipFill = new 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();
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(sImagePath);
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);
t2d.Extents = extents;
ShapeProperties sp = new 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();
DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture picture = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture();
picture.NonVisualPictureProperties = nvpp;
picture.BlipFill = blipFill;
picture.ShapeProperties = sp;
Position pos = new Position();
pos.X = 0;
pos.Y = 0;
Extent ext = new Extent();
ext.Cx = extents.Cx;
ext.Cy = extents.Cy;
AbsoluteAnchor anchor = new AbsoluteAnchor();
anchor.Position = pos;
anchor.Extent = ext;
WorksheetDrawing wsd = new WorksheetDrawing();
Drawing drawing = new Drawing();
drawing.Id = dp.GetIdOfPart(imgp);
wsp.Worksheet = ws;
Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = "Sheet1";
sheet.SheetId = 1;
sheet.Id = wbp.GetIdOfPart(wsp);
xl.WorkbookPart.Workbook = wb;
catch (Exception e)
It’s a console program, so create your C# project accordingly. There were 3 reference articles I used:
The use of the
ImagePart I can understand. It’s to load the image file you had in mind. The rest is pure gibberish until you hit the part on resolution. That’s where I used the Stack Overflow articles as reference. Then it was gibberish until the positioning of the image.
This is where I did something different. If you did the trick on inserting image into Excel, saving it as Open XML format, renaming to .zip, and unzipping that file, you might find this in the xl/drawings/drawing1.xml file:
This is for positioning the image. I’m using the
AbsoluteAnchor instead of the
TwoCellAnchor. After much hairtearing, I’ve decided the
TwoCellAnchor class is too hard to use. Let me tell you why.
[UPDATE: A reader Kevin noted the following:
I’ve been using the OneCellAnchor and find it perfect: position by row/column and size by image extent in EMU and best of all, the image will not resize when column widths are set.
So you can look at the
OneCellAnchor class and see if it suits you better.
from tag should be easy to understand. The
col tag refers to which Excel column (zero-based) is the left side of the image in. The
colOff tag is the offset. What offset you ask? The offset from the left-border of the column referenced in the
col tag, in EMU. Yes, that English Metric Unit. The
rowOff tags refer to the Excel row and offset from said Excel row (based on the top border of the image).
So far, it’s 0 for all 4 properties, because we’re inserting the image at the top left corner. Now comes the fun part.
to tag, the bottom right of the image is in the Excel cell, column 6, row 7. That’s not all. The bottom right of the image is 248195 EMUs from the left of column 6, and 152608 EMUs from the top of row 7. Note that the image is not 248195 EMUs wide nor 152608 high.
Since those offsets are calculated based on its current cell, if the widths and heights of previous cells (column and row indices less than current ones) change, these offsets change. This means, if I widen the first cell, the offset will change from 248195 EMUs to say 1000 EMUs.
The fun part is, if I widen the first cell enough, the bottom right of the image could end up in column 5. So the column and column offset values are in flux.
To really nail this part down, I will need to know the exact widths of every column up to where the bottom right of the image is. I will probably decide on some default column width in EMU, set every column to be that width. Then I’ll convert the width of the image from pixels to EMU and calculate the offset based on that default column width. And woe befall me if I happen to place the image other than at the top left corner, since I would have to do the same thing for the top left corner of the image.
That wouldn’t be feasible if your document happen to require a fixed width for some columns, due to the content of those columns. So your columns won’t all have the same width. So you’ll have to keep track of all the column widths.
Needless to say, I researched and found the
AbsoluteAnchor class. Much fewer properties to deal with…
Just in case you’re wondering, no, I don’t know why the ID of
NonVisualDrawingProperties is 1025. That’s the value I found when I did the renaming-unzipping-gawk-at-xlsx-content trick. I believe it can be some other value, but I don’t know the valid range. If you do know, chime in with a comment.
Oh and here’s the image used.
Lovely, isn’t it? *smile*
And you can download the resulting Excel file ExcelOpenXmlWithImage.xlsx.
There’s updated material and source code, together with more information on how to work with Open XML. Click here to find out more.