How To Import and Display Excel (.xlsx) File to Blazor WASM Client Side (Without Uploading to Server)

Viewed 2361

Like the title says. Lots of examples online for uploading files to backend but I'm after verification client side/offline followed by an upload of data only (no files). In theory, anything that runs on .Net5 should be able to run in WASM at a close performance. I'm trying to unload those hefty operations to clients' machines.

I've had some success with ClosedXML but when the file is a few 1000 rows it becomes incredibly slow. Using ClosedXML in Blazor Server-Side loads 100,000's rows with ease.

Notes:

  • Using MudBlazor UI Components
  • I have a stream from file instead of a file path for ClosedXML (like I would on a console app), I think this is the only way in WASM but I may be wrong.
  • I've ran the same in NPOI with similar results (slow in WASM, fast in Server Side).
  • I'd prefer to avoid EPPlus unless it has a magical fix.

Page:

@page "/upload"
@inject HttpClient Http

<h1>Upload Data</h1>

<p>This component demonstrates uploading data from Excel.</p>

<InputFile id="fileInput" OnChange="UploadFiles" hidden single />

<MudButton HtmlTag="label"
           Variant="Variant.Filled"
           Color="Color.Primary"
           StartIcon="@Icons.Filled.CloudUpload"
           for="fileInput">
    Upload Files
</MudButton>

@if (dataTable == null)
{
    <p><em>Please upload Excel File</em></p>
}
else
{
    <MudTable Items="@dataTable.AsEnumerable().Take(500)" Hover="true" Breakpoint="Breakpoint.Sm" T="DataRow" RowsPerPage="100">
        <HeaderContent>
            @foreach (DataColumn col in dataTable.Columns)
            {
                <MudTh>@col.ColumnName</MudTh>
            }
        </HeaderContent>
        <RowTemplate>
            @foreach (var cell in context.ItemArray)
            {
                <MudTd>@cell.ToString()</MudTd>
            }
        </RowTemplate>
    </MudTable>
}

@code {
    //private IList<IBrowserFile> files = new List<IBrowserFile>();
    private DataTable dataTable;

    protected override async Task OnInitializedAsync()
    {
    }

    private async Task UploadFiles(InputFileChangeEventArgs e)
    {
        dataTable = await ExcelHelper.GetDataTableFromExcel(e.File);
    }
}

Function:

public static async Task<DataTable> GetDataTableFromExcel(IBrowserFile file)
        {
            DataTable dtTable = new DataTable();

            using (MemoryStream memStream = new MemoryStream())
            {
                await file.OpenReadStream(file.Size).CopyToAsync(memStream);
                using (XLWorkbook workBook = new XLWorkbook(memStream, XLEventTracking.Disabled))
                {
                    //Read the first Sheet from Excel file.
                    IXLWorksheet workSheet = workBook.Worksheet(1);

                    //Loop through the Worksheet rows.
                    bool firstRow = true;
                    foreach (IXLRow row in workSheet.Rows())
                    {
                        //Use the first row to add columns to DataTable.
                        if (firstRow)
                        {
                            foreach (IXLCell cell in row.Cells())
                            {
                                dtTable.Columns.Add(cell.Value.ToString());
                            }
                            firstRow = false;
                        }
                        else
                        {
                            //Add rows to DataTable.
                            dtTable.Rows.Add();
                            int i = 0;

                            foreach (IXLCell cell in row.Cells(row.FirstCellUsed().Address.ColumnNumber, row.LastCellUsed().Address.ColumnNumber))
                            {
                                dtTable.Rows[dtTable.Rows.Count - 1][i] = cell.Value.ToString();
                                i++;
                            }
                        }
                    }
                }
            }
            return dtTable;
        }

The line causing the delay is:

using (XLWorkbook workBook = new XLWorkbook(memStream, XLEventTracking.Disabled))

I need help fixing the slow reading of the xlsx file, or a completely different approach if there's a better way! A better way achieving this goal, no cheating/uploading files to the server :)

0 Answers
Related