How to Download as excel file from selected rows in DB using Dotnet core web API?

Elaya Raja 96 Reputation points
2022-05-03T16:17:43.233+00:00

I am trying to collect the data from Mysql Table and my result is like one table( max 100 records). and wants to download this an excel file from .Net Core web Api response. So My API response is Excel file and for this I should not use any licensed packages. Can some one help for this to achive using dotnet core webapi.

ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,383 questions
0 comments No comments
{count} vote

3 answers

Sort by: Most helpful
  1. Zhi Lv - MSFT 32,091 Reputation points Microsoft Vendor
    2022-05-04T02:17:42.313+00:00

    Hi @Elaya Raja ,

    I assume you have already got the data from the database, then you could refer the following samples to export the data to an excel file:

    Sample 1: Without using any packages:

        [HttpGet("ExportRecordtoExcel")]  
        public ActionResult ExportRecordtoExcel()  
        {   
            //add test data  
            List<Record> obj = new List<Record>();  
            obj.Add(new Record { FName = "Smith", LName = "Singh", Address = "Knpur" });  
            obj.Add(new Record { FName = "John", LName = "Kumar", Address = "Lucknow" });  
            obj.Add(new Record { FName = "Vikram", LName = "Kapoor", Address = "Delhi" });  
            obj.Add(new Record { FName = "Tanya", LName = "Shrma", Address = "Banaras" });  
            obj.Add(new Record { FName = "Malini", LName = "Ahuja", Address = "Gujrat" });  
            obj.Add(new Record { FName = "Varun", LName = "Katiyar", Address = "Rajasthan" });  
            obj.Add(new Record { FName = "Arun  ", LName = "Singh", Address = "Jaipur" });  
            obj.Add(new Record { FName = "Ram", LName = "Kapoor", Address = "Panjab" });  
            obj.Add(new Record { FName = "Vishakha", LName = "Singh", Address = "Banglor" });  
            obj.Add(new Record { FName = "Tarun", LName = "Singh", Address = "Kannauj" });  
            obj.Add(new Record { FName = "Mayank", LName = "Dubey", Address = "Farrukhabad" });  
            //using System.Text;  
            StringBuilder str = new StringBuilder();  
            str.Append("<table border=`" + "1px" + "`b>");  
            str.Append("<tr>");  
            str.Append("<td><b><font face=Arial Narrow size=3>FName</font></b></td>");  
            str.Append("<td><b><font face=Arial Narrow size=3>LName</font></b></td>");  
            str.Append("<td><b><font face=Arial Narrow size=3>Address</font></b></td>");  
            str.Append("</tr>");  
            foreach (Record val in obj)  
            {  
                str.Append("<tr>");  
                str.Append("<td><font face=Arial Narrow size=" + "14px" + ">" + val.FName.ToString() + "</font></td>");  
                str.Append("<td><font face=Arial Narrow size=" + "14px" + ">" + val.LName.ToString() + "</font></td>");  
                str.Append("<td><font face=Arial Narrow size=" + "14px" + ">" + val.Address.ToString() + "</font></td>");  
                str.Append("</tr>");  
            }  
            str.Append("</table>");  
            HttpContext.Response.Headers.Add("content-disposition", "attachment; filename=Information" + DateTime.Now.Year.ToString() + ".xls");  
            this.Response.ContentType = "application/vnd.ms-excel";  
            byte[] temp = System.Text.Encoding.UTF8.GetBytes(str.ToString());  
            return File(temp, "application/vnd.ms-excel");  
        }    
    

    The result like this:

    198664-image.png

    Sample 2: Use the ClosedXML package. you can install this package via Nuget.

        [HttpGet("ExportToExcel")]  
        public IActionResult ExportToExcel()  
        {  
            var testdata = new List<Employee>()  
            {  
                new Employee(){ EmpID=101, EmpName="Johnny"},  
                new Employee(){ EmpID=102, EmpName="Tom"},  
                new Employee(){ EmpID=103, EmpName="Jack"},  
                new Employee(){ EmpID=104, EmpName="Vivian"},  
                new Employee(){ EmpID=105, EmpName="Edward"},  
            };  
            //using System.Data;  
            DataTable dt = new DataTable("Grid");  
            dt.Columns.AddRange(new DataColumn[2] { new DataColumn("EmpID"),  
                                        new DataColumn("EmpName") });  
                
            foreach (var emp in testdata)  
            {  
                dt.Rows.Add(emp.EmpID, emp.EmpName);  
            }  
            //using ClosedXML.Excel;  
            using (XLWorkbook wb = new XLWorkbook())  
            {  
                wb.Worksheets.Add(dt);  
                using (MemoryStream stream = new MemoryStream())  
                {  
                    wb.SaveAs(stream);  
                    return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Grid.xlsx");  
                }  
            }  
        }  
    

    Then, the result like this:

    198620-image.png


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Best regards,
    Dillion

    1 person found this answer helpful.
    0 comments No comments

  2. Elaya Raja 96 Reputation points
    2022-05-04T10:58:30.94+00:00

    Thanks ZhiLv-MSFT .
    I will check this, also I need to add some headers in the excel top and some statements in the end after the sql table values
    I mean some plain text I have to add in the excel sheet apart from the table values. Is it possible any reference pls?

    1 person found this answer helpful.

  3. Elaya Raja 96 Reputation points
    2022-06-10T03:28:07.76+00:00

    @Zhi Lv - MSFT
    This was working in windows environment and when I deploy my code into Kubernates it was not working and seems not supporting in kubernates. Can you suggest any other open source which will support in kubernates as well as windows

    Thanks in advance
    JO

    1 person found this answer helpful.
    0 comments No comments