CSharp: Add Graphical Content inside Excel spreadsheet

In this blog post, I will show you how to add an image (certificate) inside an Excel sheet using OpenXML package in C# DotNet Framework. This is a useful technique when you need to insert some graphical elements into your spreadsheet, such as logos, charts, or certificates.

The basic steps are:

1. Create a spreadsheet document or open an existing one.
2. Add a worksheet part to the document or get an existing one by name.
3. Add a drawings part to the worksheet part and create a worksheet drawing object.
4. Add an image part to the drawings part and load the image (certificate) from a file or a stream.
5. Add a non-visual drawing properties object to the worksheet drawing object and set its attributes, such as id, name, and description.
6. Add a picture object to the worksheet drawing object and set its attributes, such as id, name, and description.
7. Add a shape properties object to the picture object and set its transform properties, such as offset and extent.
8. Add a picture fill object to the shape properties object and set its image reference.
9. Add a two-cell anchor object to the worksheet drawing object and set its attributes, such as editAs, from, and to.
10. Append the picture object to the two-cell anchor object and append the two-cell anchor object to the worksheet drawing object.

The following code snippet illustrates how to implement these steps using OpenXML:

	// Blog URL - https://jugad00.blogspot.com/2019/09/c-add-sheet-and-then-add-image-into.html
	/// <summary>
	/// Add sheet in existing Excel then add image into it.
	/// </summary>
	/// <param name="sFile">File path of Excel Spreadsheet</param>
	/// <param name="imageFileName">File path of Graphical Content</param>
	public void AddGraphicalContentInSpreadSheet(string sFile, string imageFileName)
	{
	try
	{
	// Create a spreadsheet document by supplying the filepath.
	using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
	Open(sFile, true))
	{
	// Add a blank WorksheetPart.
	WorksheetPart newWorksheetPart = spreadsheetDocument.WorkbookPart.AddNewPart();
	newWorksheetPart.Worksheet = new Worksheet(new SheetData());
	Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild();
	string relationshipId = spreadsheetDocument.WorkbookPart.GetIdOfPart(newWorksheetPart);
	// Get a unique ID for the new worksheet.
	uint sheetId = 1;
	if (sheets.Elements().Count() > 0)
	{
	sheetId = sheets.Elements().Select(s => s.SheetId.Value).Max() + 1;
	}
	// Give the new worksheet a name.
	string sheetName = "Sheet" + sheetId;
	// Append the new worksheet and associate it with the workbook.
	Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
	sheets.Append(sheet);
	// Add a drawings part to the worksheet part and create a worksheet drawing object.
	var drawingsPart = newWorksheetPart.AddNewPart();
	if (!newWorksheetPart.Worksheet.ChildElements.OfType().Any())
	{
	newWorksheetPart.Worksheet.Append(new DocumentFormat.OpenXml.Spreadsheet.Drawing { Id = newWorksheetPart.GetIdOfPart(drawingsPart) });
	}
	if (drawingsPart.WorksheetDrawing == null)
	{
	drawingsPart.WorksheetDrawing = new WorksheetDrawing();
	}
	var worksheetDrawing = drawingsPart.WorksheetDrawing;
	// Add an image part to the drawings part and load the image (certificate) from a file or a stream.
	var imagePart = drawingsPart.AddImagePart(ImagePartType.Jpeg);
	using (var stream = new FileStream(imageFileName, FileMode.Open))
	{
	imagePart.FeedData(stream);
	}
	// Add a shape properties object to the picture object and set its transform properties, such as offset and extent.
	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 = 5;
	int rowNumber = 10;
	var nvps = worksheetDrawing.Descendants();
	var nvpId = nvps.Count() > 0 ?
	(UInt32Value)worksheetDrawing.Descendants().Max(p => p.Id.Value) + 1 :
	1U;
	// Append the picture object to the two-cell anchor object and append the two-cell anchor object to the worksheet drawing object.
	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 = imageFileName },
	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);
	// workbookpart.Workbook.Save();
	// Close the document.
	spreadsheetDocument.Close();
	}
	}
	catch (Exception ex)
	{
	Console.WriteLine("Add sheet in existing Excel then add image into it, is malfunctioning " + ex.Message.ToString());
	// log.Error("error occur while creating sheet and adding image --> " + ex.Message.ToString());
	}
	}

view raw AddGraphicalContentInSpreadSheet.cs hosted with ❤ by GitHub