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 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();
-
- 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);
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.