Export an Excel (XLSX) From a Data Set

This blog is about exporting an Excel sheet from a data set. Add new sheets as per the tables in the data set.

Sometimes, we have situations where we get data into a data set from the database and have to export the data in an Excel sheet.

If we have to export a DataTable's data, we will simply export it into an Excel sheet as a single sheet, but for a data set, we have to loop through the number of tables and each table will create a new sheet. It will add a new sheet to Excel. 

Below is the code to export the data set's data into an Excel sheet (.XLSX format). 

public void ExportToExcelDataset(DataSet ds) {

 int TableCount = ds.Tables.Count;
 using(XLWorkbook wb = new XLWorkbook()) {
  for (int i = 0; i < TableCount; i++) {
   try {
    DataTable dt = ds.Tables[i];
    string[] columnNames = (from dc in dt.Columns.Cast < DataColumn > () select dc.ColumnName).ToArray();
    // int Cell = 0;

    int count = columnNames.Length;
    object[] array = new object[count];
    dt.Rows.Add(array);
    wb.Worksheets.Add(dt, ds.Tables[i].TableName);



   } catch (Exception ex) {
    objException = new BusinessLogicLayer.ExceptionHelper();
    // call the LogErrorToDB() method to save the error log into ErrorLog table.
    objException.LogErrorToDB(Convert.ToString(ex.Message), Convert.ToString(ex.StackTrace), Convert.ToString(ex.Source), Convert.ToString(ex.TargetSite), System.DateTime.Now);
   } finally {

   }
  }
  string ReportName = "attachment; filename=ModificationLogReport_" + DateTime.Now.ToString() + ".xlsx";
  Response.Clear();
  Response.Buffer = true;
  Response.Charset = "";
  Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
  Response.AddHeader("content-disposition", ReportName);
  using(MemoryStream MyMemoryStream = new MemoryStream()) {
   wb.SaveAs(MyMemoryStream);
   MyMemoryStream.WriteTo(Response.OutputStream);
   Response.Flush();
   Response.End();
  }
 }
}

It will add Excel columns of each sheet by checking the column count of each table of the data set.

For Microsoft 365 .XLSX, we use the line given below as content type.

Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

wb.Worksheets.Add(dt, ds.Tables[i].TableName); //This line will add the sheet with data and give it table Name

After adding all the sheets in the Workbook, as per the tables in the database, it will save the Excel sheet in the memory stream from where it will present an output.