Home / Questions / 323699
Accepted
Huntzberger
Asked: 2020-01-23 09:37:09 +0800 CST 2020-01-23 09:37:09 +0800 CST

Export database data to Excel file

  • 772

I have the following code to download data from sql in an excel, the problem is that it downloads the empty excel, only with the headers, I already debugged it and the query if it brings me data. I have guided myself with this tutorial but I don't know what I have done wrong. I have declared this int rowStart = 42; because my query returns 42 rows

public void ExportToExcel()
{
  ExcelPackage excel = new ExcelPackage();
  ExcelWorksheet ws = excel.Workbook.Worksheets.Add("Report");

  ws.Cells["A1"].Value = "Titulo";
  ws.Cells["A2"].Value = "Id";
  ws.Cells["B2"].Value = "Nombre";
  ws.Cells["C2"].Value = "Edad";
           
  var data = bd.Database.SqlQuery<MyClass>("Select ...").ToList();

   int rowStart = 42;
   foreach (var item in data)
   {
   ws.Cells[string.Format("A{0}", rowStart)].Value = item.Id;
   ws.Cells[string.Format("B{0}", rowStart)].Value = item.Nombre;
   ws.Cells[string.Format("C{0}", rowStart)].Value = item.Edad;
   rowStart++;
   }

   ws.Cells["A:AZ"].AutoFitColumns();
   Response.Clear();
   Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
   Response.AddHeader("content-disposition", "attachment: filename=" + "ExcelReport.xlsx");
   Response.BinaryWrite(excel.GetAsByteArray());
   Response.End();
}

asp.net-mvc

1 Answers

  1. Best Answer

    Try to start the variable rowStart at 3 if you start it at 42 your data will start to show from line 42 check your files below.

    enter image description here

    Changing the variable to start at 3 looks like this:

    enter image description here

    I attach the code I use, it is an api method in .net core:

    [HttpPost]
        [AllowAnonymous]
        [Route("testExcel")]
        public IActionResult testExcel()
        {

            MemoryStream stream = new MemoryStream();
            ExcelPackage excel = new ExcelPackage(stream);
            ExcelWorksheet ws = excel.Workbook.Worksheets.Add("Report");


            ws.Cells["A1"].Value = "Titulo";
            ws.Cells["A2"].Value = "Id";
            ws.Cells["B2"].Value = "Nombre";
            ws.Cells["C2"].Value = "Edad";

            List<Test> data = new List<Test>()
            {
                new Test() { Edad = 20, Id = 1, Nombre = "Test1"},
                new Test() { Edad = 20, Id = 1, Nombre = "Test2"}
            };

            int rowStart = 3;
            foreach (var item in data)
            {
                ws.Cells[string.Format("A{0}", rowStart)].Value = item.Id;
                ws.Cells[string.Format("B{0}", rowStart)].Value = item.Nombre;
                ws.Cells[string.Format("C{0}", rowStart)].Value = item.Edad;
                rowStart++;
            }

            ws.Cells["A:AZ"].AutoFitColumns();

            excel.Save();
            stream.Position = 0;

            return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "nombreExcel.xlsx");

        }

    I hope it helps you.

    • 0