C# & OpenXML: Fügen Sie ein Bild in eine excel-Dokument

Schrieb ich etwas dazu hier im blog:
http://polymathprogrammer.com/2009/12/21/advanced-styling-in-excel-open-xml/

Beachten Sie, dass es ursprünglich geschrieben wurde, für die Open XML SDK 2.0 CTP-version, so hüten Sie sich vor dem ändern von NumberFormat-Klasse NumberingFormat Klasse (wenn das SDK 2.0 wurde abgeschlossen)

Wenn Sie isolieren möchten, um den code einfach das Bild einfügen, dann schauen Sie hier:
http://polymathprogrammer.com/2009/11/30/how-to-insert-an-image-in-excel-open-xml/

InformationsquelleAutor Vincent Tan

Schaute ich auf die Antworten von Vincent Tan und David Yates. Da hat es mich Stunden der Kodierung die folgende einfache Voraussetzungen erfüllt sind, poste ich meine Lösung hier, so dass die nächste person kann etwas Zeit sparen.

Anforderungen:

• Ein Bild hinzufügen, um zu einer spezifischen Zelle in eine vorhandene excel-Tabelle/Blatt
• Erstellen Sie eine neue Tabelle und fügen Sie ein Bild zu einer bestimmten Zelle
• Ein Bild hinzufügen, um zu einer bestimmten Zelle eines WorksheetPart (also andere Operationen können durchgeführt werden, bevor es gespeichert)
• Fügen Sie das Bild aus einem stream
• Fügen Sie das Bild aus einer Datei
• Nutzen Sie verschiedene Bild-Formate

Nutzung

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

InformationsquelleAutor Benjamin Krupp