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 :)
