SHAREPOINT / .NET C# EXPORT DATATABLE TO EXCEL USING OPENXML DLL

Convert DataTable to Stream

private static MemoryStream WriteExcelFile(string sheetName, DataTable table)

{

 MemoryStream stream = new MemoryStream();

 using (SpreadsheetDocument document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook,true))

 {

  WorkbookPart workbookPart = document.AddWorkbookPart();

  workbookPart.Workbook = new Workbook();

  WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();

  var sheetData = new SheetData();

  worksheetPart.Worksheet = new Worksheet(sheetData);

  Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());

  Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = sheetName };

  sheets.Append(sheet);

  Row headerRow = new Row();

  List<String> columns = new List<string>();

  foreach (System.Data.DataColumn column in table.Columns)

  {

   columns.Add(column.ColumnName);

   Cell cell = new Cell();

   cell.DataType = CellValues.String;

   cell.CellValue = new CellValue(column.ColumnName);

   headerRow.AppendChild(cell);

  }

  sheetData.AppendChild(headerRow);

  foreach (DataRow dsrow in table.Rows)

  {

   Row newRow = new Row();

   foreach (String col in columns)

   {

    Cell cell = new Cell();

    cell.DataType = CellValues.String;

    cell.CellValue = new CellValue(dsrow[col].ToString());

    newRow.AppendChild(cell);

   }

   sheetData.AppendChild(newRow);

  }

  return stream;

 }

}

Button Click Call

MemoryStream ms =  WriteExcelFile(“Sheet 1”,dtTable);

Response.Clear();

Response.ContentType = “application/force-download”;
Response.AddHeader(“content-disposition”, “attachment; filename=Book1.xlsx”);
Response.BinaryWrite(ms.ToArray());
Response.End();