Export DataTable To XLSX Using Eppplus In C# MVC

Question

I am attempting to export a datatable to excel using epplus in my MVC app. This is my code, but instead of exporting the data from my datatable, what my excel file shows is

Capacity ---- Count
256 --------- 206

This is the code I am using:

public IActionResult ToExcelWeGo()
{
  string workbookName = null;
  var data = _context.testtable.FromSqlRaw("Select * from hiringInfo").ToList();
  DataTable dt = new DataTable();
  dt = ObjectToData(data);
  using (ExcelPackage pack = new ExcelPackage())
  {
    ExcelWorksheet objWorksheet = pck.WOrkbook.WOrksheets.Add("Sheet1");
    objWorksheet.Name = "Test";
    workbookName = "Test Workbook.xlsx";
    var contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    var fileStream = new MemoryStream();
    pck.SaveAs(fileStream);
    fileStream.Position = 0;
    var fsr = new FileStreamResult(fileStream, contentType);
    fsr.FileDownloadName = workbookName;
    return fsr;
  }
}
public static DataTable ObjectToData(object o)
{
  DataTable dt = new DataTable("OutputData");
  DataRow dr = dt.NewRow();
  dt.Rows.Add(dr);
  o.GetType().GetProperties().ToList().ForEach(f =>
  {
    try
    {
      f.GetValue(o, null);
      dt.Columns.Add(f.Name, f.PropertyType);
      dt.Rows[0][f.Name] = f.GetValue(o, null);
    }
    catch {}
  });
  return dt;
}

Why is my DataTable not showing up as expected?

Since you are passing the `data` object to the method it is doing a `GetType` at the wrong level so it is just getting the properties of what it is - in this case it looks like it is `Capacity` and `Count`. Since you are calling `ToList()`, can you use something like `LoadFromCollection`? https://stackoverflow.com/questions/37902846/epplus-example-of-loadfromcollectiontienumerablet-boolean-tablestyles-b/37903273#37903273 Or `LoadFromArray`. If performance is a concern and you want to write your own, then you should loop through the collection one at a time. — Ernie S, Dec 22 '19 at 13:25
What is the type for `var data`? Is it a generic list? If so, what is the type of `T`? — Ernie S, Dec 23 '19 at 21:59

score 1 · Answer 1 · answered Dec 21 '19 at 13:07

Create a DataExporter class:

     public static class DataExporter
    {

        static DataTable dtExcel = new DataTable();
        private static void ReadData(string query)
        {
            //Get Datatable here by query or you can use LINQtoDataTable
            dtExcel = DBQuery.GetDataTableByQuery(query);
        }

        private static Byte[] PrepareByte(DataTable dt)
        {
            Byte[] bytes;

            int colCount = dt.Columns.Count;

            ExcelPackage excel = new ExcelPackage();
            var workSheet = excel.Workbook.Worksheets.Add("Sheet1");
            workSheet.DefaultRowHeight = 12;
            workSheet.Row(1).Height = 20;
            workSheet.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            workSheet.Row(1).Style.Font.Bold = true;

            for (int looper = 1; looper <= colCount; looper++)
                workSheet.Cells[1, looper].Value = dt.Columns[looper - 1].ColumnName;

            for (int looper = 1; looper <= colCount; looper++)
            {
                workSheet.Cells[1, looper].Style.Border.Top.Style = ExcelBorderStyle.Thin;
                workSheet.Cells[1, looper].Style.Border.Left.Style = ExcelBorderStyle.Thin;
                workSheet.Cells[1, looper].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                workSheet.Cells[1, looper].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            }

            for (int rowNo = 0; rowNo < dt.Rows.Count; rowNo++)
            {
                for (int colNo = 0; colNo < colCount; colNo++)
                    workSheet.Cells[rowNo + 2, colNo + 1].Value = dt.Rows[rowNo][colNo];

                for (int innerLooper = 1; innerLooper <= colCount; innerLooper++)
                {
                    workSheet.Cells[rowNo + 2, innerLooper].Style.Border.Top.Style = ExcelBorderStyle.Thin;
                    workSheet.Cells[rowNo + 2, innerLooper].Style.Border.Left.Style = ExcelBorderStyle.Thin;
                    workSheet.Cells[rowNo + 2, innerLooper].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                    workSheet.Cells[rowNo + 2, innerLooper].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                }
            }

            for (int looper = 1; looper <= colCount; looper++)
                workSheet.Column(looper).AutoFit();

            bytes = excel.GetAsByteArray();

            return bytes;
        }

        public static Byte[] GetBytes(string query)
        {
            Byte[] bytes;
            ReadData(query);
            bytes = PrepareByte(dtExcel);
            return bytes;
        }


        public static Byte[] GetBytes(DataTable dt)
        {
            Byte[] bytes;
            bytes = PrepareByte(dt);
            return bytes;
        }
    }

Now from any page just call the DataExporter class and pass the datatable. Hopefully it will work:

            DataTable dt = new DataTable();
            dt.Columns.Add("EMP_ID", typeof(string));
            dt.Columns.Add("EMP_NAME", typeof(string));
            dt.Columns.Add("DEPARTMENT_NAME", typeof(string));
            dt.Columns.Add("DESIGNATION", typeof(string));
            dt.Columns.Add("BRANCH", typeof(string));
            dt.Columns.Add("AMOUNT", typeof(double));

            Byte[] bytes = DataExporter.GetBytes(dt);

            Response.ClearHeaders();
            Response.Clear();
            Response.Buffer = true;
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-length", bytes.Length.ToString());
            Response.AddHeader("content-disposition", "attachment; filename=AddDataUpload.xlsx");
            Response.OutputStream.Write(bytes, 0, bytes.Length);
            Response.Flush();
            HttpContext.Current.ApplicationInstance.CompleteRequest();

Hi - I get multiple compile errors using Response. For example, I get 'HttpResponse' does not contain a definition for 'ClearHeaders' and no accessible extension method accepting a first argument of type 'HttpResponse' could be found. I get that on ClearHeaders(), Buffer, AddHeader, OutputStream, Flush, HttpContext.Current.ApplicationInstance.CompleteRequest(); — HotTomales, Dec 21 '19 at 17:10
Please check this link for your problem: https://stackoverflow.com/questions/43178149/httpresponse-does-not-contain-a-definition-for-addheader-for-dot-net-core/43183327 — Syed Md. Kamruzzaman, Dec 22 '19 at 04:42

user3387600 · Answer 2 · 2021-02-03T13:45:04.950

public ActionResult ReportExcel() {

  DataTable dataTable = new System.Data.DataTable();
        //fill datatable by some data i just use empty databale
        System.Text.StringBuilder htmlstr = new System.Text.StringBuilder();

        htmlstr.Append("<table border='1px'>");
        htmlstr.Append("<tr>");
        foreach (System.Data.DataColumn column in dataTable.Columns)
        {
            htmlstr.Append($"<th>{column.ColumnName}</th>");
        }
        htmlstr.Append($"</tr>");
        foreach (System.Data.DataRow dr in dataTable.Rows)
        {
            htmlstr.Append("<tr>");
            foreach (System.Data.DataColumn column in dataTable.Columns)
            {
                htmlstr.Append($"<td>{dr[column.ColumnName]}</td>");
            }
            htmlstr.Append($"</tr>");
        }
htmlstr.Append($"</table>");
Response.AddHeader("content-disposition", "attachment;filename=report.xls");
return Content(htmlstr.ToString(), "application/ms-excel");

}

Please add comments to your code and explain your answer – phoenixstudio Jan 30 '21 at 15:05 — phoenixstudio, Jan 30 '21 at 15:05

Export DataTable To XLSX Using Eppplus In C# MVC

2 Answers2