Export data to Excel in ASP.NET MVC
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 worksheet to data tables and collection objects including DynamicObjects. Exporting data from DataTable to 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 a new ASP.NET web application
Step 2: Complete the New ASP.NET Web Application – CreateXlsIOSample dialog:
- Select MVC.
- Click OK.
New ASP.NET Web Application dialog
Step 3: Install the Syncfusion.XlsIO.AspNet.Mvc5 NuGet package as reference to your .NET Framework application from NuGet.org.
Install NuGet package
Step 4: A default controller with name HomeController.cs gets added on creation of ASP.NET MVC project. Include the following namespaces in that HomeController.cs file.
C#
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 the 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.
C#
//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 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 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.
C#
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 Excel document
Take a moment to explore the rich set of Syncfusion Excel (XlsIO) library features.
Also, refer to the blog on 6 Easy Ways to Export Data to Excel in C#.
See Also:
Download Excel from Ajax call in ASP.NET MVC?
Export Excel file to DataTable
Create an Excel file in ASP.NET Core
Create an Excel file in ASP.NET MVC
Create an Excel file in ASP.NET Web Forms
Create an Excel file in Xamarin
Create an Excel file in Xamarin.Android
Create an Excel file in Xamarin.iOS
Create an Excel file in Azure platform
Starting with v16.2.0.x, if you reference Syncfusion assemblies from trial setup or from the NuGet feed, include a license key in your projects. Refer to the link to learn about generating and registering Syncfusion license key in your application to use the components without trial message.
