How to import data from Excel file and bind it for .NET MVC Grid?
In Certain cases, users may like to read data from excel worksheet and bind it as dataSource for the ASP.NET MVC Grid control. We can achieve this requirement using the Syncfusion.XlsIO namespace.
Solution:
Render the Grid control, by binding DataTable as dataSource using ViewBag.data
@(Html.EJ().Grid<object>("Grid")
.Datasource((System.Data.DataTable)ViewBag.data)
.Columns(col =>
{
col.Field("ID").Width(20).Add();
col.Field("Name").Width(20).Add();
col.Field("DateOfBirth").Format("{0:dd/MM/yyyy}").Width(20).Add();
col.Field("Address").Width(50).Add();
col.Field("MobileNumber").Width(50).Add();
})
)
In the Controller, we can load and open an existing excel workbook using the Open method of IWorkbook class. We can export the sheet data to data table using the ExportDataTable method of the IWorkSheet and store the returned DataTable object to the ViewBag.data
public ActionResult Index()
{
ExcelEngine excelEngine = new ExcelEngine();
//Loads or open an existing workbook through Open method of IWorkbooks
IWorkbook workbook = excelEngine.Excel.Workbooks.Open(AppDomain.CurrentDomain.BaseDirectory+"/DataSample.xls");
IWorksheet worksheet = workbook.Worksheets[0];
// Read data from the worksheet and Export to the DataTable.
DataTable table = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames);
ViewBag.data = table;
return View();
}
Conclusion
I hope you enjoyed learning on how to import data from Excel file and bind it for .NET MVC Grid.
You can refer to ASP.NET MVC Grid feature tour page to know about its other groundbreaking feature representations and documentation, and how to quickly get started for configuration specifications. You can also explore our ASP.NET MVC Grid example to understand how to create and manipulate data.
For current customers, you can check out our components from the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our other controls.
If you have any queries or require clarifications, please let us know in the comments section below. You can also contact us through our support forums, Direct-Trac, or feedback portal. We are always happy to assist you!
