Post
Ask Question

Create a Simple Excel Spreadsheet in Visual Studio using Open XML SDK 2.0

C# Export DataTable values to Excel:

  1. public static void InsertWorksheet(DataTable table, string docName)  
  2. {  
  3.     try  
  4.     {  
  5.         SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(docName, SpreadsheetDocumentType.Workbook);  
  6.         // Add a WorkbookPart and Workbook objects.  
  7.         WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();  
  8.         workbookpart.Workbook = new Workbook();  
  9.         // Add a WorksheetPart.  
  10.         WorksheetPart worksheetPart = workbookpart.AddNewPart < WorksheetPart > ();  
  11.         // Create Worksheet and SheetData objects.  
  12.         worksheetPart.Worksheet = new Worksheet(new SheetData());  
  13.         // Add a Sheets object.  
  14.         Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild < Sheets > (new Sheets());  
  15.         // Append the new worksheet named “Report” and associate it  
  16.         // with the workbook.  
  17.         Sheet sheet = new Sheet()  
  18.         {  
  19.             Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),  
  20.                 SheetId = 1,  
  21.                 Name = “Report”  
  22.         };  
  23.         sheets.Append(sheet);  
  24.         // Get the sheetData cell table.  
  25.         SheetData sheetData = worksheetPart.Worksheet.GetFirstChild < SheetData > ();  
  26.         //////////////////////////////////  
  27.         // Add a row to the cell table.  
  28.         Row row;  
  29.         row = new Row()  
  30.         {  
  31.             RowIndex = 1  
  32.         };  
  33.         sheetData.Append(row);  
  34.         List < String > columns = new List < string > ();  
  35.         //Adding Header row  
  36.         foreach(System.Data.DataColumn column in table.Columns)  
  37.         {  
  38.             Cell refCell = null;  
  39.             Cell newCell = new Cell();  
  40.             row.InsertBefore(newCell, refCell);  
  41.             newCell.CellValue = new CellValue(column.ColumnName);  
  42.             columns.Add(column.ColumnName);  
  43.         }  
  44.         UInt32 iIndex = 1;  
  45.         //Adding data row  
  46.         foreach(System.Data.DataRow dsrow in table.Rows)  
  47.             {  
  48.                 Row rowdata;  
  49.                 rowdata = new Row()  
  50.                 {  
  51.                     RowIndex = iIndex + 1  
  52.                 };  
  53.                 sheetData.Append(rowdata);  
  54.                 foreach(String col in columns)  
  55.                 {  
  56.                     Cell refCell = null;  
  57.                     Cell newCell = new Cell();  
  58.                     rowdata.InsertBefore(newCell, refCell);  
  59.                     newCell.CellValue = new CellValue(dsrow[col].ToString());  
  60.                 }  
  61.                 iIndex++;  
  62.             }  
  63.             // Close the document.  
  64.         spreadsheetDocument.Close();  
  65.     }  
  66.     catch (Exception ex)  
  67.     {}  
  68. }