Articles in this section
Category / Section

How to import data from excel sheet and bind to Blazor Grid?

4 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

 

Conclusion

I hope you enjoyed learning how to import data from excel sheet and bind to Blazor Grid.

You can refer to our Blazor 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 Blazor 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 forumsDirect-Trac, or feedback portal. We are always happy to assist you!

 

 

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