All geek questions in one place

C # and OpenXML: paste image into excel document

I am using a modified version of this code to create an excel document, add cells and styles. I am trying to modify this code to be able to add images to a sheet. I'm not leaving anywhere, and there is nothing on the Internet that will help. I am trying to read an OpenXML productivity tool. which is useless. Can someone here point me in the right direction?

Thanks.

+6
source share
3 answers

I wrote something about this here on the blog: http://polymathprogrammer.com/2009/12/21/advanced-styling-in-excel-open-xml/

Note that it was originally written for the CTP version of the Open XML SDK 2.0, so beware of changing the NumberFormat class to the NumberingFormat class (when the SDK 2.0 was completed)

If you want to isolate the code only from inserting an image, look here: http://polymathprogrammer.com/2009/11/30/how-to-insert-an-image-in-excel-open-xml/

+7
source

Here is a link to an example MSDN with code that you can download.

There is also an example of Eric White and the You Tube video.

+1
source

I looked at the answers from Vincent Tan and David Yates. Since it took me hours of coding to fulfill the following simple requirements, I will post my solution here so that the next person can save some time.

Requirements:

  • Add image to specific cell of existing Excel spreadsheet / sheet
  • Create a new table and add an image to a specific cell
  • Add an image to a specific cell in the Worksheet (so that other operations can be performed before it is saved)
  • Add image from stream
  • Add image from file
  • Using various image formats

Using

// 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 ...

The 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); } } }
0
source

Source: https://habr.com/ru/post/886722/

More articles:


All Articles