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

Question

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.

Answer 1

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:

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:

---

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

Answer 2

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?

Answer 3

@Anonymous
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