In this article I will explain with an example, how to export data from SQL Server to Excel file in ASP.Net Core (.Net Core) Razor Pages.
 
 
 

Install ClosedXML package using Nuget

In order to install ClosedXML library using Nuget, please refer my article Install ClosedXML Nuget Package.
 
 

Database

I have made use of the following table Customers with the schema as follows.
ASP.Net Core Razor Pages: Export data from SQL Server to Excel
 
I have already inserted few records in the table.
ASP.Net Core Razor Pages: Export data from SQL Server to Excel
 
Note: You can download the database table SQL by clicking the download link below.
           Download SQL file
 
 

Connection Strings in AppSettings.json file

The connection string to the Database and Excel file are saved in the AppSettings.json file.
Note: For more details about saving and reading connection strings in AppSettings.json file, please refer .Net Core 7: Read Connection String from AppSettings.json file.
 
The DataSource property has been assigned a Placeholder {0}, which will be replaced by actual path of the File.
{
  "ConnectionStrings": {
    "constr": "Data Source=.\\SQL2022;Initial Catalog=AjaxSamples;Uid=sa;Pwd=pass@123;"
  }
}
 
 

Namespaces

You will need to import the following namespaces.
using System.Data;
using System.Data.SqlClient;
using ClosedXML.Excel;
 
 

Index PageModel (Code-Behind)

Inside the Controller, first the public property of IConfiguration interface is created.
Then, the IConfiguration interface is injected into the Constructor (IndexModel) using Dependency Injection method and the injected object is assigned to the public property of IConfiguration interface.
The PageModel consists of following Handler methods.

Handler method for handling GET operation

This Handler method left empty as it is not required.
 

Handler method for handling POST operation

This Handler method is executed when the Export Button is clicked.
Note: The following Action method performs File Download and hence the return type is set to FileResult.
 
Inside this Handler method, an object of XLWorkbook is created and the GetCustomers method (explained later) is called.
The records returned from the GetCustomers method (explained later) are stored into a DataTable object which is added as Worksheet collection to the XLWorkbook object.
Finally, the XLWorkbook object is saved to a MemoryStream class object then it is converted to BYTE Array and exported and downloaded as Excel file using the File function.
 
GetCustomers
Inside this method, first the connection is read from the ConnectionStrings section of the AppSettings.json file.
Then, a connection to the database is established using the SqlConnection class.
The records are fetched using SqlDataAdapter class and stored into a DataSet object which is returned.
Note: For more details on SqlDataAdapter, please refer my article ASP.Net Core Razor Pages: Implementing SqlDataAdapter.
 
public class IndexModel : PageModel
{
    public IConfiguration Configuration { get; set; }
    public IndexModel(IConfiguration configuration)
    {
        this.Configuration = configuration;
    }
 
    public void OnGet()
    {
            
    }
 
    public FileResult OnPostExport()
    {
        using (XLWorkbook wb = new XLWorkbook())
        {
            DataTable dt = this.GetCustomers().Tables[0];
            wb.Worksheets.Add(dt);
            using (MemoryStream stream = new MemoryStream())
            {
                wb.SaveAs(stream);
                return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Customers.xlsx");
            }
        }
    }
 
    private DataSet GetCustomers()
    {
        DataSet ds = new DataSet();
        string constr = this.Configuration.GetSection("ConnectionStrings")["MyConn"];
        string sql = "SELECT CustomerId, Name, Country FROM Customers";
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter(sql, con))
            {
                sda.Fill(ds);
            }
        }
 
        return ds;
    }
}
 
 

Razor Page (HTML)

HTML Markup

The Razor Page consists of an HTML INPUT Submit Button enclosed inside a Form.
When the Submit Button is clicked, the Form gets submitted.
Note: In the Razor PageModel, the Handler method name is OnPostExport but here it will be specified as Export when calling from the Razor HTML Page.
 
@page
@model DataTable_Excel_Core_Razor.Pages.IndexModel
@addTagHelper*, Microsoft.AspNetCore.Mvc.TagHelpers
@{
     Layout = null;
}
 
<!DOCTYPE html>
 
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
</head>
<body>
    <form method="post">
        <input type="submit" value="Export" asp-page-handler="Export" />
    </form>
</body>
</html>
 
 

Screenshots

Form

ASP.Net Core Razor Pages: Export data from SQL Server to Excel
 

The Excel File

ASP.Net Core Razor Pages: Export data from SQL Server to Excel
 
 

Demo

View Demo
 
 

Downloads

Download Code


Other available versions