I am using a modified version of this code to create an excel document, add cells, and style cells. I am trying to modify this code to also be able to add images to a sheet. I am getting nowhere and there really is nothing on the web that will help. I am trying to read through the OpenXML productivity tool diff. which is kind of unhelpful. Can anyone here point me in the right direction?
Thanks.
I looked at the answers from Vincent Tan and David Yates. Since it took me hours of coding to have the following simple requirements met, I'll post my solution here so the next person can save some time.
Requirements:
Usage
// Add via file to existing spreadsheet
try
{
ExcelTools.AddImage(false, filePath, "Sheet name",
imagePath, "Image description",
2 /* column */, 2 /* row */);
}
catch ...
// Add via stream to existing spreadsheet
try
{
ExcelTools.AddImage(false, filePath, "Sheet name",
imageStream, "Image description",
2 /* column */, 2 /* row */);
}
catch ...
// Create spreadsheet and add image via path
try
{
ExcelTools.AddImage(true, filePath, "Sheet name",
imagePath, "Image description",
2 /* column */, 2 /* row */);
}
catch ...
// Create spreadsheet and add image via stream
try
{
ExcelTools.AddImage(true, filePath, "Sheet name",
imageStream, "Image description",
2 /* column */, 2 /* row */);
}
catch ...
// Add multiple images or apply further changes
try
{
// Open spreadsheet
spreadsheetDocument = SpreadsheetDocument.Open(excelFile, true);
// Get WorksheetPart
worksheetPart = ExcelTools.GetWorksheetPartByName(spreadsheetDocument, "Some sheet name");
AddImage(worksheetPart, imagePath1, "My first image", 1, 1); // A1
AddImage(worksheetPart, imagePath2, "My second image", 1, 5); // A5
AddImage(worksheetPart, imagePath3, "My third image", 2, 7); // B7
// Other operations if needed
worksheetPart.Worksheet.Save();
spreadsheetDocument.Close();
}
catch ...
Code:
using System;
using System.Data;
using System.Linq;
using System.IO;
using System.Drawing;
using System.Drawing.Imaging;
using System.Collections.Generic;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using A = DocumentFormat.OpenXml.Drawing;
using Xdr = DocumentFormat.OpenXml.Drawing.Spreadsheet;
namespace Utilities
{
public class ExcelTools
{
public static ImagePartType GetImagePartTypeByBitmap(Bitmap image)
{
if (ImageFormat.Bmp.Equals(image.RawFormat))
return ImagePartType.Bmp;
else if (ImageFormat.Gif.Equals(image.RawFormat))
return ImagePartType.Gif;
else if (ImageFormat.Png.Equals(image.RawFormat))
return ImagePartType.Png;
else if (ImageFormat.Tiff.Equals(image.RawFormat))
return ImagePartType.Tiff;
else if (ImageFormat.Icon.Equals(image.RawFormat))
return ImagePartType.Icon;
else if (ImageFormat.Jpeg.Equals(image.RawFormat))
return ImagePartType.Jpeg;
else if (ImageFormat.Emf.Equals(image.RawFormat))
return ImagePartType.Emf;
else if (ImageFormat.Wmf.Equals(image.RawFormat))
return ImagePartType.Wmf;
else
throw new Exception("Image type could not be determined.");
}
public static WorksheetPart GetWorksheetPartByName(SpreadsheetDocument document, string sheetName)
{
IEnumerable<Sheet> sheets =
document.WorkbookPart.Workbook.GetFirstChild<Sheets>().
Elements<Sheet>().Where(s => s.Name == sheetName);
if (sheets.Count() == 0)
{
// The specified worksheet does not exist
return null;
}
string relationshipId = sheets.First().Id.Value;
return (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);
}
public static void AddImage(bool createFile, string excelFile, string sheetName,
string imageFileName, string imgDesc,
int colNumber, int rowNumber)
{
using (var imageStream = new FileStream(imageFileName, FileMode.Open))
{
AddImage(createFile, excelFile, sheetName, imageStream, imgDesc, colNumber, rowNumber);
}
}
public static void AddImage(WorksheetPart worksheetPart,
string imageFileName, string imgDesc,
int colNumber, int rowNumber)
{
using (var imageStream = new FileStream(imageFileName, FileMode.Open))
{
AddImage(worksheetPart, imageStream, imgDesc, colNumber, rowNumber);
}
}
public static void AddImage(bool createFile, string excelFile, string sheetName,
Stream imageStream, string imgDesc,
int colNumber, int rowNumber)
{
SpreadsheetDocument spreadsheetDocument = null;
WorksheetPart worksheetPart = null;
if (createFile)
{
// Create a spreadsheet document by supplying the filepath
spreadsheetDocument = SpreadsheetDocument.Create(excelFile, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart
worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Add Sheets to the Workbook
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = sheetName
};
sheets.Append(sheet);
}
else
{
// Open spreadsheet
spreadsheetDocument = SpreadsheetDocument.Open(excelFile, true);
// Get WorksheetPart
worksheetPart = GetWorksheetPartByName(spreadsheetDocument, sheetName);
}
AddImage(worksheetPart, imageStream, imgDesc, colNumber, rowNumber);
worksheetPart.Worksheet.Save();
spreadsheetDocument.Close();
}
public static void AddImage(WorksheetPart worksheetPart,
Stream imageStream, string imgDesc,
int colNumber, int rowNumber)
{
// We need the image stream more than once, thus we create a memory copy
MemoryStream imageMemStream = new MemoryStream();
imageStream.Position = 0;
imageStream.CopyTo(imageMemStream);
imageStream.Position = 0;
var drawingsPart = worksheetPart.DrawingsPart;
if (drawingsPart == null)
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;
Bitmap bm = new Bitmap(imageMemStream);
var imagePart = drawingsPart.AddImagePart(GetImagePartTypeByBitmap(bm));
imagePart.FeedData(imageStream);
A.Extents extents = new A.Extents();
var extentsCx = bm.Width * (long)(914400 / bm.HorizontalResolution);
var extentsCy = bm.Height * (long)(914400 / bm.VerticalResolution);
bm.Dispose();
var colOffset = 0;
var rowOffset = 0;
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 = imgDesc },
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);
}
}
}
I wrote something on this here in a blog post: http://polymathprogrammer.com/2009/12/21/advanced-styling-in-excel-open-xml/
Note that it was originally written for the Open XML SDK 2.0 CTP version, so beware of the change from NumberFormat class to NumberingFormat class (when the SDK 2.0 was finalised)
If you want to isolate the code to just the image insertion, take a look here: http://polymathprogrammer.com/2009/11/30/how-to-insert-an-image-in-excel-open-xml/
Here is a link to a MSDN example with code you can download.
There is also Eric White's example and You Tube video
