c# - Export Images in excel using OpenXml SDK? -

i facing problem while exporting multiple images in excel cell. doing in simple button click in 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 = 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>         /// example method generates datatable.         /// </summary>         static datatable gettable()         {             //             // here create datatable 4 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 add 5 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 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 worksheetpart specified sheet name         /// </summary>         /// <param name="workbookpart">the workbookpart</param>         /// <param name="sheetname">the name of worksheet</param>         /// <returns>returns worksheetpart 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 image @ specified location          /// </summary>         /// <param name="sheet1">the worksheetpart image 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 contains image data</param>         private static void insertimage(documentformat.openxml.packaging.worksheetpart sheet1,              int startrowindex, int startcolumnindex, int endrowindex, int endcolumnindex, stream imagestream)         {             //inserting drawing element in worksheet             //make sure relationship id same drawing element in worksheet , relationship part             int drawingpartid = getnextrelationshipid(sheet1);             documentformat.openxml.spreadsheet.drawing drawing1 = new documentformat.openxml.spreadsheet.drawing()              { id = "rid" + drawingpartid.tostring() };              //check whether worksheetpart contains vmldrawingparts (legacydrawing element)             if (sheet1.vmldrawingparts == null)             {                 //if there no vmldrawing part (legacydrawing element) exists, append drawing part sheet                 sheet1.worksheet.append(drawing1);             }             else             {                 //if vmldrawingpart (legacydrawing element) exists, find index of legacy drawing in sheet , inserts 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 drawings.xml part              documentformat.openxml.packaging.drawingspart drawingspart1             = sheet1.addnewpart<documentformat.openxml.packaging.drawingspart>("rid" + drawingpartid.tostring());             generatedrawingspart1content(drawingspart1, startrowindex, startcolumnindex, endrowindex, endcolumnindex);             //adding 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>         /// index of legacy drawing element in specified worksheetpart         /// </summary>         /// <param name="sheet1">the worksheetpart</param>         /// <returns>index of legacy drawing</returns>         private static int getindexoflegacydrawing(documentformat.openxml.packaging.worksheetpart sheet1)         {             (int = 0; < sheet1.worksheet.childelements.count; i++)             {                 documentformat.openxml.openxmlelement element = sheet1.worksheet.childelements[i];                 if (element documentformat.openxml.spreadsheet.legacydrawing)                     return i;             }             return -1;         }          /// <summary>         /// returns next relationship id specified worksheetpart         /// </summary>         /// <param name="sheet1">the worksheetpart</param>         /// <returns>returns 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 try export table 1 row working fine .but getting problem multiple rows

documentformat.openxml.packaging.drawingspart drawingspart1             = sheet1.addnewpart<documentformat.openxml.packaging.drawingspart>("rid" + drawingpartid.tostring()); 

while adding drawing part of 2nd row getting error "only 1 instance of type allowed parent."

kind of same error 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 solve in case ...i need use openxml only

in code, have been trying add new drawing part every image place within worksheet. per open xml file format specification excel package, there shall 1 drawing part worksheet , chartsheet. change code append images (multiple twocellanchor tags) within single drawing part worksheet. in way, can avoid exception.

let me know if have questions.

best, vijayakumar