Articles in this section
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;
    }
}

 

View Sample in GitHub

 

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!

 

 

 

Did you find this information helpful?
Yes
No
Help us improve this page
Please provide feedback or comments
Comments (0)
Please sign in to leave a comment
Access denied
Access denied