Export data to Excel in ASP.NET MVC

3 mins read

Syncfusion Excel (XlsIO) library is a .NET Excel library used to create, read, and edit Excel documents. Using this library, you can export data to Excel in ASP.NET MVC.

Syncfusion Excel (XlsIO) library supports exporting data from worksheets to data tables and collection objects, including DynamicObjects. Exporting data from DataTable to an Excel worksheet can be achieved through ImportDataTable.

Steps to export data to Excel in ASP.NET MVC programmatically:

Step 1: Create a new ASP.NET web application project.

Create ASP.NET web application project in Visual Studio

Create a new ASP.NET web application.

Step 2: Complete the New ASP.NET Web Application – CreateXlsIOSample dialog:

Select MVC.
Click OK.

Select MVC in the new ASP.NET web application dialog

New ASP.NET Web Application dialog.

Step 3: Install the Syncfusion.XlsIO.AspNet.Mvc5 NuGet package as a reference to your .NET Framework application from NuGet.org.

Add XlsIO reference to the project

Install NuGet package.

Step 4: A default controller named HomeController.cs gets added on the creation of the ASP.NET MVC project. Include the following namespaces in that HomeController.cs file.

using Syncfusion.XlsIO;
using System.Data;

VB.NET

Imports Syncfusion.XlsIO
Imports System.Data

Step 5: A default action method named Index will be present in HomeController.cs. Right-click on this action method and select "Go To View," where you will be directed to its associated view page, Index.cshtml.

Step 6: Add a new button in Index.cshtml as shown below.

CSHTML

@{Html.BeginForm("ExportDataToExcel", "Home", FormMethod.Get);
    {
        <div>
            <input type = "submit" value="ExportDataToExcel" style="width:150px;height:27px" />
        </div>
    }
    Html.EndForm();
}

VBHTML

@Using Html.BeginForm("ExportDataToExcel", "Home", FormMethod.Get)
@<div>
    <input type = "submit" value="ExportDataToExcel" style="width:150px;height:27px" />
</div>
End Using

Step 7: Add a new action method ExportDataToExcel() in HomeController.cs and include the below code snippet to export data to Excel in ASP.NET MVC and download it.

// Create an instance of ExcelEngine
using (ExcelEngine excelEngine = new ExcelEngine())
{
    // Initialize Application
    IApplication application = excelEngine.Excel;
 
    // Set the default application version as Excel 2016
    application.DefaultVersion = ExcelVersion.Excel2016;
 
    // Create a workbook with a worksheet
    IWorkbook workbook = application.Workbooks.Create(1);
 
    // Access the first worksheet from the workbook instance
    IWorksheet worksheet = workbook.Worksheets[0];
 
    // Export data to Excel
    DataTable dataTable = GetDataTable();
    worksheet.ImportDataTable(dataTable, true, 1, 1);
    worksheet.UsedRange.AutofitColumns();
 
    // Save the workbook to disk in xlsx format
    workbook.SaveAs("Output.xlsx", ExcelSaveType.SaveAsXLS, HttpContext.ApplicationInstance.Response, ExcelDownloadType.Open);
}

VB.NET

'Create an instance of ExcelEngine
Using excelEngine As ExcelEngine = New ExcelEngine
 
    'Initialize Application
    Dim application As IApplication = excelEngine.Excel
 
    'Set the default application version as Excel 2016
    application.DefaultVersion = ExcelVersion.Excel2016
 
    'Create a workbook with a worksheet
    Dim workbook As IWorkbook = application.Workbooks.Create(1)
 
    'Access the first worksheet from the workbook instance
    Dim worksheet As IWorksheet = workbook.Worksheets(0)
 
    'Export data to Excel
    Dim dataTable As DataTable = GetDataTable()
    worksheet.ImportDataTable(dataTable, True, 1, 1)
    worksheet.UsedRange.AutofitColumns()
 
    'Save the workbook to disk in xlsx format
    workbook.SaveAs("Output.xlsx", ExcelSaveType.SaveAsXLS, HttpContext.ApplicationInstance.Response, ExcelDownloadType.Open)
 
End Using

Step 8: Load the DataTable using the following simple static method.

private static DataTable GetDataTable()
{
    // Create a DataTable with four columns
    DataTable table = new DataTable();
    table.Columns.Add("Dosage", typeof(int));
    table.Columns.Add("Drug", typeof(string));
    table.Columns.Add("Patient", typeof(string));
    table.Columns.Add("Date", typeof(DateTime));
 
    // Add five DataRows
    table.Rows.Add(25, "Indocin", "David", DateTime.Now);
    table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
    table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
    table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
    table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
 
    return table;
}

VB.NET

Private Shared Function GetDataTable() As DataTable
 
    'Create a DataTable with four columns
    Dim table As DataTable = New DataTable
    table.Columns.Add("Dosage", GetType(System.Int32))
    table.Columns.Add("Drug", GetType(System.String))
    table.Columns.Add("Patient", GetType(System.String))
    table.Columns.Add("Date", GetType(DateTime))
 
    'Add five DataRows
    table.Rows.Add(25, "Indocin", "David", DateTime.Now)
    table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now)
    table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now)
    table.Rows.Add(21, "Combivent", "Janet", DateTime.Now)
    table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now)
 
    Return table
End Function

A complete working example of how to export data to Excel in ASP.NET MVC can be downloaded from Export data to Excel in ASP.NET MVC.zip.

By executing the program, you will get the output Excel file as shown below:

Output of Export data to Excel in ASP.NET MVC

Output Excel document

Also, refer to the blog on 6 Easy Ways to Export Data to Excel in C#.

Export data to Excel in ASP.NET MVC

Steps to export data to Excel in ASP.NET MVC programmatically:

See Also:

Access denied