Export Images in excel using OpenXml SDK?

Question

I am facing a problem while exporting multiple images in Excel Cell. I am doing it in a simple button click in a page .

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using A = DocumentFormat.OpenXml.Drawing;
using Xdr = DocumentFormat.OpenXml.Drawing.Spreadsheet;
using A14 = DocumentFormat.OpenXml.Office2010.Drawing;
using System.IO;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;

namespace OpenXMLExport
{
    public partial class _Default : System.Web.UI.Page
    {
        public static string ImageFile = HttpContext.Current.Server.MapPath(@"~\Data\Sunset.jpg");
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            DataTable table = GetTable();
            DataSet ds = new DataSet();
            ds.Tables.Add(table);
            ExportDataSet(ds, HttpContext.Current.Server.MapPath(@"~\Data\ImageExport.xlsx"));

        }
        /// <summary>
        /// This example method generates a DataTable.
        /// </summary>
        static DataTable GetTable()
        {
            //
            // Here we create a DataTable with four columns.
            //
            DataTable table = new DataTable();
            table.Columns.Add("Dosage", typeof(int));
            table.Columns.Add("Drug", typeof(string));
            table.Columns.Add("Patient", typeof(string));
            table.Columns.Add("Image", typeof(string));

            //
            // Here we add five DataRows.
            //
            table.Rows.Add(25, "Indocin", "David");
            table.Rows.Add(50, "Enebrel", "Sam");
            //table.Rows.Add(10, "Hydralazine", "Christoff");
            return table;
        }
        private void ExportDataSet(DataSet ds, string destination)
        {
            using (var workbook = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Create(
                destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
            {
                var workbookPart = workbook.AddWorkbookPart();

                workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

                workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();

                foreach (System.Data.DataTable table in ds.Tables)
                {

                    var sheetPart = workbook.WorkbookPart.AddNewPart<DocumentFormat.OpenXml.Packaging.WorksheetPart>();
                    var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
                    sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);


                    //DocumentFormat.OpenXml.Spreadsheet.SheetFormatProperties sheetFormatProperties2 = new DocumentFormat.OpenXml.Spreadsheet.SheetFormatProperties() { DefaultRowHeight = 15D };
                    //sheetPart.Worksheet.Append(sheetFormatProperties2);


                    DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = 
                        workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
                    string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);

                    uint sheetId = 1;
                    if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
                    {
                        sheetId =
                            sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                    }

                    DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
                    { Id = relationshipId, SheetId = sheetId, Name = table.TableName };
                    sheets.Append(sheet);

                    DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();

                    List<String> columns = new List<string>();
                    foreach (System.Data.DataColumn column in table.Columns)
                    {
                        columns.Add(column.ColumnName);

                        DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                        cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                        cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
                        headerRow.AppendChild(cell);
                    }


                    sheetData.AppendChild(headerRow);

                    foreach (System.Data.DataRow dsrow in table.Rows)
                    {
                        DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                        foreach (String col in columns)
                        {
                            if (col.ToString() != "Image")
                            {
                                DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                                cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                                cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
                                newRow.AppendChild(cell);
                            }
                            else
                            {
                                DocumentFormat.OpenXml.Packaging.WorksheetPart sheet1 = GetSheetByName(workbookPart, "sheet");
                                InsertImage(sheet1, 1, 3, 3, 6, new FileStream(ImageFile, FileMode.Open,FileAccess.ReadWrite));
                                workbook.WorkbookPart.Workbook.Save();

                            }
                        }

                        sheetData.AppendChild(newRow);
                    }
                    // Close the document handle.

                    workbook.Close();
                    DownloadFile(HttpContext.Current.Server.MapPath(@"~\Data\ImageExport.xlsx"));
                    //System.Diagnostics.Process.Start(HttpContext.Current.Server.MapPath(@"\ImageExport.xlsx"));
                }
            }
        }

        public static void DownloadFile(string filePath)
        {
            string path = filePath;// HttpContext.Current.Server.MapPath(filePath);
            System.IO.FileInfo file = new System.IO.FileInfo(path);
            if (file.Exists)
            {
                HttpContext.Current.Response.Clear();
                HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + file.Name);
                HttpContext.Current.Response.AddHeader("Content-Length", file.Length.ToString());
                HttpContext.Current.Response.ContentType = "application/octet-stream";
                HttpContext.Current.Response.WriteFile(file.FullName);
                HttpContext.Current.Response.End();
            }
        }
        /// <summary>
        /// Returns the WorksheetPart for the specified sheet name
        /// </summary>
        /// <param name="workbookpart">The WorkbookPart</param>
        /// <param name="sheetName">The name of the worksheet</param>
        /// <returns>Returns the WorksheetPart for the specified sheet name</returns>
        private static DocumentFormat.OpenXml.Packaging.WorksheetPart GetSheetByName(DocumentFormat.OpenXml.Packaging.WorkbookPart workbookpart, string sheetName)
        {
            foreach (DocumentFormat.OpenXml.Packaging.WorksheetPart sheetPart in workbookpart.WorksheetParts)
            {
                string uri = sheetPart.Uri.ToString();
                if (uri.EndsWith(sheetName + ".xml"))
                    return sheetPart;
            }
            return null;
        }
        /// <summary>
        /// Inserts the image at the specified location 
        /// </summary>
        /// <param name="sheet1">The WorksheetPart where image to be inserted</param>
        /// <param name="startRowIndex">The starting Row Index</param>
        /// <param name="startColumnIndex">The starting column index</param>
        /// <param name="endRowIndex">The ending row index</param>
        /// <param name="endColumnIndex">The ending column index</param>
        /// <param name="imageStream">Stream which contains the image data</param>
        private static void InsertImage(DocumentFormat.OpenXml.Packaging.WorksheetPart sheet1, 
            int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex, Stream imageStream)
        {
            //Inserting a drawing element in worksheet
            //Make sure that the relationship id is same for drawing element in worksheet and its relationship part
            int drawingPartId = GetNextRelationShipID(sheet1);
            DocumentFormat.OpenXml.Spreadsheet.Drawing drawing1 = new DocumentFormat.OpenXml.Spreadsheet.Drawing() 
            { Id = "rId" + drawingPartId.ToString() };

            //Check whether the WorksheetPart contains VmlDrawingParts (LegacyDrawing element)
            if (sheet1.VmlDrawingParts == null)
            {
                //if there is no VMLDrawing part (LegacyDrawing element) exists, just append the drawing part to the sheet
                sheet1.Worksheet.Append(drawing1);
            }
            else
            {
                //if VmlDrawingPart (LegacyDrawing element) exists, then find the index of legacy drawing in the sheet and inserts the new drawing element before VMLDrawing part
                int legacyDrawingIndex = GetIndexofLegacyDrawing(sheet1);
                if (legacyDrawingIndex != -1)
                    sheet1.Worksheet.InsertAt<DocumentFormat.OpenXml.OpenXmlElement>(drawing1, legacyDrawingIndex);
                else
                    sheet1.Worksheet.Append(drawing1);
            }
            //Adding the drawings.xml part

            DocumentFormat.OpenXml.Packaging.DrawingsPart drawingsPart1
            = sheet1.AddNewPart<DocumentFormat.OpenXml.Packaging.DrawingsPart>("rId" + drawingPartId.ToString());
            GenerateDrawingsPart1Content(drawingsPart1, startRowIndex, startColumnIndex, endRowIndex, endColumnIndex);
            //Adding the image
            DocumentFormat.OpenXml.Packaging.ImagePart imagePart1 = drawingsPart1.AddNewPart<DocumentFormat.OpenXml.Packaging.ImagePart>("image/jpeg", "rId1");
            imagePart1.FeedData(imageStream);

        }

        // Generates content of drawingsPart1.
        private static void GenerateDrawingsPart1Content(DocumentFormat.OpenXml.Packaging.DrawingsPart drawingsPart1, int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex)
        {
            Xdr.WorksheetDrawing worksheetDrawing1 = new Xdr.WorksheetDrawing();
            worksheetDrawing1.AddNamespaceDeclaration("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
            worksheetDrawing1.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main");

            Xdr.TwoCellAnchor twoCellAnchor1 = new Xdr.TwoCellAnchor() { EditAs = Xdr.EditAsValues.OneCell };

            Xdr.FromMarker fromMarker1 = new Xdr.FromMarker();
            Xdr.ColumnId columnId1 = new Xdr.ColumnId();
            columnId1.Text = startColumnIndex.ToString();
            Xdr.ColumnOffset columnOffset1 = new Xdr.ColumnOffset();
            columnOffset1.Text = "38100";
            Xdr.RowId rowId1 = new Xdr.RowId();
            rowId1.Text = startRowIndex.ToString();
            Xdr.RowOffset rowOffset1 = new Xdr.RowOffset();
            rowOffset1.Text = "0";

            fromMarker1.Append(columnId1);
            fromMarker1.Append(columnOffset1);
            fromMarker1.Append(rowId1);
            fromMarker1.Append(rowOffset1);

            Xdr.ToMarker toMarker1 = new Xdr.ToMarker();
            Xdr.ColumnId columnId2 = new Xdr.ColumnId();
            columnId2.Text = endColumnIndex.ToString();
            Xdr.ColumnOffset columnOffset2 = new Xdr.ColumnOffset();
            columnOffset2.Text = "542925";
            Xdr.RowId rowId2 = new Xdr.RowId();
            rowId2.Text = endRowIndex.ToString();
            Xdr.RowOffset rowOffset2 = new Xdr.RowOffset();
            rowOffset2.Text = "161925";

            toMarker1.Append(columnId2);
            toMarker1.Append(columnOffset2);
            toMarker1.Append(rowId2);
            toMarker1.Append(rowOffset2);

            Xdr.Picture picture1 = new Xdr.Picture();

            Xdr.NonVisualPictureProperties nonVisualPictureProperties1 = new Xdr.NonVisualPictureProperties();
            Xdr.NonVisualDrawingProperties nonVisualDrawingProperties1 = new Xdr.NonVisualDrawingProperties() { Id = (DocumentFormat.OpenXml.UInt32Value)2U, Name = "Picture 1" };

            //DocumentFormat.OpenXml.Spreadsheet.SheetFormatProperties sheetFormatProperties3 
            //    = new DocumentFormat.OpenXml.Spreadsheet.SheetFormatProperties() { DefaultRowHeight = 15D ,DefaultColumnWidth = 25D};


            Xdr.NonVisualPictureDrawingProperties nonVisualPictureDrawingProperties1 = new Xdr.NonVisualPictureDrawingProperties();
            A.PictureLocks pictureLocks1 = new A.PictureLocks() { NoChangeAspect = true };

            nonVisualPictureDrawingProperties1.Append(pictureLocks1);

            nonVisualPictureProperties1.Append(nonVisualDrawingProperties1);
            nonVisualPictureProperties1.Append(nonVisualPictureDrawingProperties1);

            Xdr.BlipFill blipFill1 = new Xdr.BlipFill();

            A.Blip blip1 = new A.Blip() { Embed = "rId1", CompressionState = A.BlipCompressionValues.Print };
            blip1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");

            A.BlipExtensionList blipExtensionList1 = new A.BlipExtensionList();

            A.BlipExtension blipExtension1 = new A.BlipExtension() { Uri = "{28A0092B-C50C-407E-A947-70E740481C1C}" };

            A14.UseLocalDpi useLocalDpi1 = new A14.UseLocalDpi() { Val = false };
            useLocalDpi1.AddNamespaceDeclaration("a14", "http://schemas.microsoft.com/office/drawing/2010/main");

            blipExtension1.Append(useLocalDpi1);

            blipExtensionList1.Append(blipExtension1);

            blip1.Append(blipExtensionList1);

            A.Stretch stretch1 = new A.Stretch();
            A.FillRectangle fillRectangle1 = new A.FillRectangle();

            stretch1.Append(fillRectangle1);

            blipFill1.Append(blip1);
            blipFill1.Append(stretch1);

            Xdr.ShapeProperties shapeProperties1 = new Xdr.ShapeProperties();

            A.Transform2D transform2D1 = new A.Transform2D();
            A.Offset offset1 = new A.Offset() { X = 1257300L, Y = 762000L };
            A.Extents extents1 = new A.Extents() { Cx = 2943225L, Cy = 2257425L };

            transform2D1.Append(offset1);
            transform2D1.Append(extents1);

            A.PresetGeometry presetGeometry1 = new A.PresetGeometry() { Preset = A.ShapeTypeValues.Rectangle };
            A.AdjustValueList adjustValueList1 = new A.AdjustValueList();

            presetGeometry1.Append(adjustValueList1);

            shapeProperties1.Append(transform2D1);
            shapeProperties1.Append(presetGeometry1);

            picture1.Append(nonVisualPictureProperties1);
            picture1.Append(blipFill1);
            picture1.Append(shapeProperties1);
            Xdr.ClientData clientData1 = new Xdr.ClientData();


            //CellStyleFormats cellStyleFormats1 = new CellStyleFormats() { Count = (UInt32Value)1U };
            //CellFormat cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };

            //cellStyleFormats1.Append(cellFormat1);

            //CellFormats cellFormats1 = new CellFormats() { Count = (UInt32Value)4U };
            //CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
            //CellFormat cellFormat3 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
            //CellFormat cellFormat4 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
            //CellFormat cellFormat5 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)4U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };

            //cellFormats1.Append(cellFormat2);
            //cellFormats1.Append(cellFormat3);
            //cellFormats1.Append(cellFormat4);
            //cellFormats1.Append(cellFormat5);

            //CellStyles cellStyles1 = new CellStyles() { Count = (UInt32Value)1U };
            //CellStyle cellStyle1 = new CellStyle() { Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };

            //cellStyles1.Append(cellStyle1);

            //twoCellAnchor1.Append(cellStyles1);
            //twoCellAnchor1.Append(cellFormats1);
            twoCellAnchor1.Append(fromMarker1);
            twoCellAnchor1.Append(toMarker1);
            twoCellAnchor1.Append(picture1);
            twoCellAnchor1.Append(clientData1);
            //twoCellAnchor1.Append(sheetFormatProperties3);
            worksheetDrawing1.Append(twoCellAnchor1);

            drawingsPart1.WorksheetDrawing = worksheetDrawing1;
        }

        /// <summary>
        /// Get the index of legacy drawing element in the specified WorksheetPart
        /// </summary>
        /// <param name="sheet1">The worksheetPart</param>
        /// <returns>Index of legacy drawing</returns>
        private static int GetIndexofLegacyDrawing(DocumentFormat.OpenXml.Packaging.WorksheetPart sheet1)
        {
            for (int i = 0; i < sheet1.Worksheet.ChildElements.Count; i++)
            {
                DocumentFormat.OpenXml.OpenXmlElement element = sheet1.Worksheet.ChildElements[i];
                if (element is DocumentFormat.OpenXml.Spreadsheet.LegacyDrawing)
                    return i;
            }
            return -1;
        }

        /// <summary>
        /// Returns the next relationship id for the specified WorksheetPart
        /// </summary>
        /// <param name="sheet1">The worksheetPart</param>
        /// <returns>Returns the next relationship id </returns>
        private static int GetNextRelationShipID(DocumentFormat.OpenXml.Packaging.WorksheetPart sheet1)
        {
            int nextId = 0;
            List<int> ids = new List<int>();
            foreach (DocumentFormat.OpenXml.Packaging.IdPartPair part in sheet1.Parts)
            {
                ids.Add(int.Parse(part.RelationshipId.Replace("rId", string.Empty)));
            }
            if (ids.Count > 0)
                nextId = ids.Max() + 1;
            else
                nextId = 1;
            return nextId;
        }
    }
}

If i try to export a table with one row its working fine .but i am getting problem for multiple rows

DocumentFormat.OpenXml.Packaging.DrawingsPart drawingsPart1
            = sheet1.AddNewPart<DocumentFormat.OpenXml.Packaging.DrawingsPart>("rId" + drawingPartId.ToString());

While adding Drawing part of 2nd Row i am getting Error "Only one instance of the type is allowed for this parent."

Kind of Same Error i found here http://social.msdn.microsoft.com/Forums/office/en-US/8ac6040f-8599-4e20-84fb-4b2390847373/excel-style-part-using-openxml-in-c

But still unable to solve in my case ...I need to use OpenXMl only

Answer 1

Please find the code snippet to import the simple DataTable into Excel workbook using open xml sdf. I hope this will be helpful to achieve your requirement.

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
        ExcelBuilder obj = new ExcelBuilder();
        obj.SetDataSource(GetTable());
        obj.CreatePackage(@"output.xlsx");

        System.Diagnostics.Process.Start("output.xlsx");
    }
    /// <summary>
    /// Get the DataTable instance
    /// </summary>
    /// <returns>Returns the DataTable instance</returns>
    private DataTable GetTable()
    {
        DataTable table = new DataTable();
        table.Columns.Add("Name", typeof(string));
        table.Columns.Add("Image", typeof(Image));

        table.Rows.Add("AAAA", GetImageFrom64("iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAFqSURBVDhPY/hPAph45sP/xs0XoDwIIMqA2v0v/kfu/PA//tTP/ylLTkBFIQCvARk7X/0P2/3pv8/O7/9ddv4E47jV56CyEIDVgL6jL/4nHP3533f/z/+OQE12G96DbS5fe4I4AyYdfwHW7Lz8wf+OragagpachrIgAKsBzUfe/k9dchzKQwUesw5BWRCA1YDKw+//N607DOWhAqIMqAG6oHz5ASgPFXhOJ8KA8kPv/5cu3gXl/f8/9eTz/26rHvxXmffkv8/kvVBRCMDpgry5m6G8//+1lz7+L9x/7T9f553/rj0Ig0EAqwFh24FeWLAVzC49+uk/b9eV/7xNZ8EGlM3bCRaHARQDbDa+/q+24uV/2RUf/rcs3wMV/f+fr/rIf5G6o/8TJ26BiiAA3ADP9U//S8979F9s4rX/wjOe/W9fdRAqgx/ADbCfexmsUWjCQ7BtxAIULyTP2Pk/eyaqHwkBrIFIPPj/HwAXQanDAoJm4wAAAABJRU5ErkJggg=="));
        table.Rows.Add("BBBB", GetImageFrom64("iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAEoSURBVDhPjZM9S8NQFIb9VcUf4NRNRHCxW4sOji4GK8UspRAIghAEBR0yBSczlSKiEiS0XRR0iODg4H+Q17zn3AZDc68+cLlfnOfkHG5W4CBNU7OyYxUkSQLP82R2YRVEUYSbu1zmIAjM6TJWQetojNXBRIbv++Z0Gatg7fILZy9A7/pTSrHhFIxmQCcu6oL3E+BtZDZ/CI5zYOPitfkL7rvAw55bcJBpL8IwNKe/KIMx2fyfoBEK0naD4Glf6qwJWDPHtA/ku2XmLclOKsH380Av8g4w36kLHrf1jllnQxOhqKC4EoFI2JwyEwVsoLUEgwrKIAmeH8qW8AnzX3C9QqKC2/WqpgVZllUSFyr4OFdJcSrbBXEcm5WdqokCO80us2kUcjgBfgCofKZ+pmmmjQAAAABJRU5ErkJggg=="));
        table.Rows.Add("CCCC", GetImageFrom64("iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAEUSURBVDhPpZIhc4QwFIQzESdO9gdW9gecrEAgEAgEAoFEIJAIJALJD6hAIBAIBAKBQNw2SydMgNzN3FV8mUne7ibvgSiKAmVZoqoq1HW9wT3PWc/zHFmWIUkSxHGMKIoQBAF838caME0T5nm2wppmHMeVYRhWtgAKl2U58ShkF8DnHQNCeVnRIXp/DGAbgr2ZZk8J79cPK6ydAtI03QV88yZ5tcLaqQVO1mzhpkQ/D2DtFMDPwt70wL6kRKmENljT5i0gDMPdlD+V6Bna3Pc9PM+DYAqfZYZoeK4xb6a56zq4rgvBxRSamKajuW1bOI4DwcUmPEKjaW6a5i+A023ehF5hm/YriEwt/0Hw9+QP8g6uvOAX9G/HbwDThCwAAAAASUVORK5CYII="));

        return table;
    }
    /// <summary>
    /// Converts the From64 data into Image instance
    /// </summary>
    /// <param name="base64String">Image stream in Base64 encoding</param>
    /// <returns>Returns the Image instance</returns>
    private Image GetImageFrom64(string base64String)
    {
        MemoryStream stream = new MemoryStream(System.Convert.FromBase64String(base64String));
        return Image.FromStream(stream);
    }
}

public class ExcelBuilder
{
    /// <summary>
    /// static field to maintain the track the relationship id
    /// </summary>
    private static int s_rId;
    /// <summary>
    /// Field for Data source
    /// </summary>
    private DataTable m_table;
    /// <summary>
    /// Collection to maintain string value of cell and to serialize the content in SharedString xml part
    /// </summary>
    private List<string> sharedStrings = new List<string>();
    /// <summary>
    /// Collection to maintain the image collection added into the excel workbook
    /// </summary>
    private Dictionary<string, Image> ImageCollection = new Dictionary<string, Image>();
    /// <summary>
    /// Set the DataSource of the Excel builder
    /// </summary>
    /// <param name="table"></param>
    public void SetDataSource(DataTable table)
    {
        m_table = table;
    }
    /// <summary>
    /// Create a new Excel file
    /// </summary>
    /// <param name="filePath">Path of the output file</param>
    public void CreatePackage(string filePath)
    {
        using (SpreadsheetDocument package = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
        {
            CreateParts(package);
        }
    }

    // Adds child parts and generates content of the specified part.
    private void CreateParts(SpreadsheetDocument workbook)
    {
        WorkbookPart workbookPart1 = workbook.AddWorkbookPart();
        GenerateWorkbookPart1Content(workbookPart1);

        WorksheetPart worksheetPart1 = workbookPart1.AddNewPart<WorksheetPart>(GetNextRelationShipId());
        GenerateWorksheetPart1Content(worksheetPart1);

        SharedStringTablePart sharedStringTablePart1 = workbookPart1.AddNewPart<SharedStringTablePart>(GetNextRelationShipId());
        GenerateSharedStringTablePart1Content(sharedStringTablePart1);

    }

    // Generates content of workbookPart1.
    private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
    {
        Workbook workbook1 = new Workbook() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x15" } };
        workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
        workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
        workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");

        Sheets sheets1 = new Sheets();
        Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
        sheets1.Append(sheet1);

        workbook1.Append(sheets1);
        workbookPart1.Workbook = workbook1;
    }

    // Generates content of worksheetPart1.
    private void GenerateWorksheetPart1Content(WorksheetPart worksheetPart1)
    {
        Worksheet worksheet1 = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
        worksheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
        worksheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
        worksheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");

        SheetData sheetData1 = new SheetData();
        string drawingrID = GetNextRelationShipId();
        AppendSheetData(sheetData1, worksheetPart1, drawingrID);
        worksheet1.Append(sheetData1);
        if (worksheetPart1.DrawingsPart != null && worksheetPart1.DrawingsPart.WorksheetDrawing != null)
        {
            Drawing drawing1 = new Drawing() { Id = drawingrID };
            worksheet1.Append(drawing1);
        }
        worksheetPart1.Worksheet = worksheet1;
    }

    private void AppendSheetData(SheetData sheetData1, WorksheetPart worksheetPart, string drawingrID)
    {
        for (int rowIndex = 0; rowIndex < m_table.Rows.Count; rowIndex++)
        {
            Row row = new Row() { RowIndex = (UInt32Value)(rowIndex + 1U) };
            DataRow tableRow = m_table.Rows[rowIndex];
            for (int colIndex = 0; colIndex < tableRow.ItemArray.Length; colIndex++)
            {
                Cell cell = new Cell();
                CellValue cellValue = new CellValue();
                object data = tableRow.ItemArray[colIndex];
                if (data is int || data is float || data is double)
                {
                    cellValue.Text = data.ToString();
                    cell.Append(cellValue);
                }
                else if (data is string)
                {
                    cell.DataType = CellValues.SharedString;
                    string text = data.ToString();
                    if (!sharedStrings.Contains(text))
                        sharedStrings.Add(text);

                    cellValue.Text = sharedStrings.IndexOf(text).ToString();
                    cell.Append(cellValue);
                }
                else if (data is Image)
                {
                    DrawingsPart drawingsPart = null;
                    Xdr.WorksheetDrawing worksheetDrawing = null;

                    if (worksheetPart.DrawingsPart == null)
                    {
                        drawingsPart = worksheetPart.AddNewPart<DrawingsPart>(drawingrID);
                        worksheetDrawing = new Xdr.WorksheetDrawing();
                        worksheetDrawing.AddNamespaceDeclaration("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
                        worksheetDrawing.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main");
                        drawingsPart.WorksheetDrawing = worksheetDrawing;
                    }
                    else if (worksheetPart.DrawingsPart != null && worksheetPart.DrawingsPart.WorksheetDrawing != null)
                    {
                        drawingsPart = worksheetPart.DrawingsPart;
                        worksheetDrawing = worksheetPart.DrawingsPart.WorksheetDrawing;
                    }
                    string imagerId = GetNextRelationShipId();
                    Xdr.TwoCellAnchor cellAnchor = AddTwoCellAnchor(rowIndex, 1, rowIndex, 1, imagerId);
                    worksheetDrawing.Append(cellAnchor);
                    ImagePart imagePart = drawingsPart.AddNewPart<ImagePart>("image/png", imagerId);
                    GenerateImagePartContent(imagePart, data as Image);
                }
                row.Append(cell);
            }
            sheetData1.Append(row);
        }
    }
    // Generates content of imagePart1.
    private void GenerateImagePartContent(ImagePart imagePart, Image image)
    {
        MemoryStream memStream = new MemoryStream();
        image.Save(memStream, ImageFormat.Png);
        memStream.Position = 0;
        imagePart.FeedData(memStream);
        memStream.Close();
    }

    private Xdr.TwoCellAnchor AddTwoCellAnchor(int startRow, int startColumn, int endRow, int endColumn, string imagerId)
    {
        Xdr.TwoCellAnchor twoCellAnchor1 = new Xdr.TwoCellAnchor() { EditAs = Xdr.EditAsValues.OneCell };

        Xdr.FromMarker fromMarker1 = new Xdr.FromMarker();
        Xdr.ColumnId columnId1 = new Xdr.ColumnId();
        columnId1.Text = startColumn.ToString();
        Xdr.ColumnOffset columnOffset1 = new Xdr.ColumnOffset();
        columnOffset1.Text = "0";
        Xdr.RowId rowId1 = new Xdr.RowId();
        rowId1.Text = startRow.ToString();
        Xdr.RowOffset rowOffset1 = new Xdr.RowOffset();
        rowOffset1.Text = "0";

        fromMarker1.Append(columnId1);
        fromMarker1.Append(columnOffset1);
        fromMarker1.Append(rowId1);
        fromMarker1.Append(rowOffset1);

        Xdr.ToMarker toMarker1 = new Xdr.ToMarker();
        Xdr.ColumnId columnId2 = new Xdr.ColumnId();
        columnId2.Text = endColumn.ToString();
        Xdr.ColumnOffset columnOffset2 = new Xdr.ColumnOffset();
        columnOffset2.Text = "152381";
        Xdr.RowId rowId2 = new Xdr.RowId();
        rowId2.Text = endRow.ToString();
        Xdr.RowOffset rowOffset2 = new Xdr.RowOffset();
        rowOffset2.Text = "152381";

        toMarker1.Append(columnId2);
        toMarker1.Append(columnOffset2);
        toMarker1.Append(rowId2);
        toMarker1.Append(rowOffset2);

        Xdr.Picture picture1 = new Xdr.Picture();

        Xdr.NonVisualPictureProperties nonVisualPictureProperties1 = new Xdr.NonVisualPictureProperties();
        Xdr.NonVisualDrawingProperties nonVisualDrawingProperties1 = new Xdr.NonVisualDrawingProperties() { Id = (UInt32Value)2U, Name = "Picture 1" };

        Xdr.NonVisualPictureDrawingProperties nonVisualPictureDrawingProperties1 = new Xdr.NonVisualPictureDrawingProperties();
        A.PictureLocks pictureLocks1 = new A.PictureLocks() { NoChangeAspect = true };

        nonVisualPictureDrawingProperties1.Append(pictureLocks1);

        nonVisualPictureProperties1.Append(nonVisualDrawingProperties1);
        nonVisualPictureProperties1.Append(nonVisualPictureDrawingProperties1);

        Xdr.BlipFill blipFill1 = new Xdr.BlipFill();

        A.Blip blip1 = new A.Blip() { Embed = imagerId };
        blip1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");

        A.BlipExtensionList blipExtensionList1 = new A.BlipExtensionList();

        A.BlipExtension blipExtension1 = new A.BlipExtension() { Uri = "{28A0092B-C50C-407E-A947-70E740481C1C}" };

        A14.UseLocalDpi useLocalDpi1 = new A14.UseLocalDpi() { Val = false };
        useLocalDpi1.AddNamespaceDeclaration("a14", "http://schemas.microsoft.com/office/drawing/2010/main");

        blipExtension1.Append(useLocalDpi1);

        blipExtensionList1.Append(blipExtension1);

        blip1.Append(blipExtensionList1);

        A.Stretch stretch1 = new A.Stretch();
        A.FillRectangle fillRectangle1 = new A.FillRectangle();

        stretch1.Append(fillRectangle1);

        blipFill1.Append(blip1);
        blipFill1.Append(stretch1);

        Xdr.ShapeProperties shapeProperties1 = new Xdr.ShapeProperties();

        A.Transform2D transform2D1 = new A.Transform2D();
        A.Offset offset1 = new A.Offset() { X = 0L, Y = 0L };
        A.Extents extents1 = new A.Extents() { Cx = 152381L, Cy = 152381L };

        transform2D1.Append(offset1);
        transform2D1.Append(extents1);

        A.PresetGeometry presetGeometry1 = new A.PresetGeometry() { Preset = A.ShapeTypeValues.Rectangle };
        A.AdjustValueList adjustValueList1 = new A.AdjustValueList();

        presetGeometry1.Append(adjustValueList1);

        shapeProperties1.Append(transform2D1);
        shapeProperties1.Append(presetGeometry1);

        picture1.Append(nonVisualPictureProperties1);
        picture1.Append(blipFill1);
        picture1.Append(shapeProperties1);
        Xdr.ClientData clientData1 = new Xdr.ClientData();

        twoCellAnchor1.Append(fromMarker1);
        twoCellAnchor1.Append(toMarker1);
        twoCellAnchor1.Append(picture1);
        twoCellAnchor1.Append(clientData1);

        return twoCellAnchor1;
    }
    /// <summary>
    /// Generates the SharedString xml part using the string collection in SharedStrings (List<string>)
    /// </summary>
    /// <param name="part"></param>
    private void GenerateSharedStringTablePart1Content(SharedStringTablePart part)
    {
        SharedStringTable sharedStringTable1 = new SharedStringTable();
        sharedStringTable1.Count = new UInt32Value((uint)sharedStrings.Count);
        sharedStringTable1.UniqueCount = new UInt32Value((uint)sharedStrings.Count);

        foreach (string item in sharedStrings)
        {
            SharedStringItem sharedStringItem = new SharedStringItem();
            Text text = new Text();
            text.Text = item;

            sharedStringItem.Append(text);
            sharedStringTable1.Append(sharedStringItem);
        }
        part.SharedStringTable = sharedStringTable1;
    }
    /// <summary>
    /// Gets the next relationship id
    /// </summary>
    /// <returns></returns>
    private string GetNextRelationShipId()
    {
        s_rId++;
        return "rId" + s_rId.ToString();
    }
}

Answer 2

In this code, you have been trying to add a new drawing part for every image that you place within worksheet. As per the open xml file format specification for Excel package, there shall be only one drawing part for worksheet and chartsheet. So change your code to append the images (multiple twoCellAnchor tags) within a single drawing part for worksheet. In this way, you can avoid this exception.