CODEWITHSUNDEEP
c#excelblazornpoi
user3656651
user3656651

Reputation: 820

Reading Excel File in Blazor wasm using NPOI

The following code works fine from a .NET Core app running on a PC. The code loads an excel file and reads it using the NPOI library.

public void ReadExcel()
        {
            DataTable dtTable = new DataTable();
            List<string> rowList = new List<string>();
            ISheet sheet;
            using (var stream = new FileStream("Test.xlsx", FileMode.Open))
            {
                stream.Position = 0;
                XSSFWorkbook xssWorkbook = new XSSFWorkbook(stream);
                sheet = xssWorkbook.GetSheetAt(0);
                IRow headerRow = sheet.GetRow(0);
                int cellCount = headerRow.LastCellNum;
                for (int j = 0; j < cellCount; j++)
                {
                    ICell cell = headerRow.GetCell(j);
                    if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) continue;
                    {
                        dtTable.Columns.Add(cell.ToString());
                    }
                }
                for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row == null) continue;
                    if (row.Cells.All(d => d.CellType == CellType.Blank)) continue;
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        if (row.GetCell(j) != null)
                        {
                            if (!string.IsNullOrEmpty(row.GetCell(j).ToString()) && !string.IsNullOrWhiteSpace(row.GetCell(j).ToString()))
                            {
                                rowList.Add(row.GetCell(j).ToString());
                            }
                        }
                    }
                    if (rowList.Count > 0)
                        dtTable.Rows.Add(rowList.ToArray());
                    rowList.Clear();
                }
            }
            return JsonConvert.SerializeObject(dtTable);
        }

I want to use this code in my Blazor app to be able to read an Excel file from a browser. I can use the InputFile component to get the file:

<InputFile OnChange="GetFile"/>

The question is how to get the the uploaded file as a stream that I can pass to the ReadExcel function? So it should be something like this:

public async Task GetFile(InputFileChangeEventArgs e) //get excel file
    {
        
        stream = e.File.OpenReadStream(); //need a stream here that ReadExcel() can use!
               
        ReadExcel();
    }

If I use the above stream in the ReadExcel function instead of the one it has, the code doesnt work. What is the correct way of forming this stream so that ReadExcel can use that instead of the one it has now?

Thanks, Amjad.

Upvotes: 2

Views: 3153

Answers (1)

Henk Holterman
Henk Holterman

Reputation: 273244

I think the major problem is that a ReadStream is not Seekable (CanSeek == false).

You can copy it to MemoryStream but do keep an eye on the size limits.

public async Task GetFile(InputFileChangeEventArgs e) //get excel file
{        
    var stream1 = e.File.OpenReadStream(); //need a stream here that ReadExcel() can use!
    var stream2 = new MemoryStream();
    await stream1.CopyToAsync(stream2);
    stream1.Close();
   
    ReadExcel(stream2);       
}

Upvotes: 2

Related Questions