Category / Section
How to import data from Excel sheet and bind to Blazor Grid?
2 mins read
This article explains how to import an Excel file and bind to Blazor Grid. You can upload an excel file and bind to blazor grid using ExpandoObject class.
In the following code example, the excel file is fetched and converted into an ExpandoObject list. The expando object list bind to Grid.
RAZOR
@using Syncfusion.XlsIO; @using System.IO; @using Syncfusion.Blazor.Grids @using Syncfusion.Blazor.Inputs @using System.Data @using System.Dynamic @using ServerApp.Data; @inject WeatherForecastService WeatherService <SfUploader AutoUpload="false"> <UploaderEvents ValueChange="OnChange"></UploaderEvents> </SfUploader> @if (Columns != null) { <SfGrid @ref="Grid" DataSource="@CustomerList" AllowFiltering="true" AllowPaging="true"> <GridColumns> @{ foreach (var val in Columns) { <GridColumn Field="@val"></GridColumn> } } </GridColumns> </SfGrid> } else { <div>Upload an excel file to show grid</div> } @code { SfGrid<ExpandoObject> Grid; public DataTable table = new DataTable(); private void OnChange(UploadChangeEventArgs args) { foreach (var file in args.Files) { var path = file.FileInfo.Name; ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2016; //get local wwwroot path of application var check = WeatherService.GetPath(path); //create new filestream into above path FileStream openFileStream = new FileStream(check, FileMode.OpenOrCreate); //write the uploaded memorystream to file stream file.Stream.WriteTo(openFileStream); //again open the filstream from that path FileStream fileStream = new FileStream(check, FileMode.Open, FileAccess.Read, FileShare.ReadWrite); //access the workbook from that filtestream IWorkbook workbook = application.Workbooks.Open(fileStream); IWorksheet worksheet = workbook.Worksheets[0]; //get datatable from workbook table = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames); //convert to dynamic list and append to Grid. GenerateListFromTable(table); } } string[] Columns; public List<ExpandoObject> CustomerList; public void GenerateListFromTable(DataTable input) { var list = new List<ExpandoObject>(); Columns = input.Columns.Cast<DataColumn>() .Select(x => x.ColumnName) .ToArray(); foreach (DataRow row in input.Rows) { System.Dynamic.ExpandoObject e = new System.Dynamic.ExpandoObject(); foreach (DataColumn col in input.Columns) e.TryAdd(col.ColumnName, row.ItemArray[col.Ordinal]); list.Add(e); } CustomerList = list; } }
See also
Import data from Excel sheet into Blazor Grid using DataTable
Refer to our documentation and online samples for more features. If you have any queries, please let us know in the comments below. You can also contact us through our Support forum or Support ticket. We are happy to assist you!