New to Telerik UI for Blazor? Download free 30-day trial

Importing an Excel Files in the Grid

Environment

Product Grid for Blazor

Description

How can I import an Excel file data in the Grid component by using the Telerik.Documents.SpreadsheetStreaming package?

Solution

A possible way to import an Excel file in the Grid is by utilizing the Telerik.Documents.SpreadsheetStreaming library. This package provides an API for accessing Excel files and enabling straightforward cell-by-cell reading.

To import the data of an Excel file in the Grid:

  1. Read the value of each cell.
  2. Save the data in a collection of objects.
  3. Bind the Grid to the collection.

Import an Excel file in the Grid

@* Import Excel File in the Grid *@

@using System.IO;
@using System.Data;
@using Telerik.Documents.SpreadsheetStreaming;

<TelerikFileSelect AllowedExtensions="@AllowedExtensions"
                   Multiple="false"
                   OnSelect="@OnSelectHandler" />

<TelerikGrid Data=@GridData.AsEnumerable()
             TItem="DataRow"
             Height="400px">
    <GridColumns>
        @foreach (DataColumn col in GridData.Columns)
        {
            <GridColumn Field="@col.ColumnName" Title="@col.ColumnName">
                <Template>
                    @((context as DataRow).ItemArray[col.Ordinal].ToString())
                </Template>
            </GridColumn>
        }
    </GridColumns>
</TelerikGrid>

@code {
    DataTable GridData = new DataTable();
    List<string> AllowedExtensions { get; set; } = new List<string>() { ".xlsx" };

    async Task OnSelectHandler(FileSelectEventArgs args)
    {
        foreach (var file in args.Files)
        {
            var fileData = new byte[file.Stream.Length];
            await file.Stream.ReadAsync(fileData);
            var ms = new MemoryStream(fileData);

            using (IWorkbookImporter workBookImporter = SpreadImporter.CreateWorkbookImporter(SpreadDocumentFormat.Xlsx, ms))
            {
                foreach (IWorksheetImporter worksheetImporter in workBookImporter.WorksheetImporters)
                {
                    foreach (IRowImporter rowImporter in worksheetImporter.Rows)
                    {
                        if (rowImporter.RowIndex == 0)
                        {
                            foreach (ICellImporter cell in rowImporter.Cells)
                            {
                                GridData.Columns.Add(cell.Value);
                            }
                        }
                        else
                        {
                            var newRow = GridData.NewRow();
                            var cellIndex = 0;
                            foreach (ICellImporter cell in rowImporter.Cells)
                            {
                                newRow[cellIndex] = cell.Value;
                                cellIndex++;
                            }
                            GridData.Rows.Add(newRow);
                        }
                    }
                }
            }
        }
    }
}

See Also

In this article