Excel as data source - Microsoft Q&A (2023)

You tagged this as a Blazor question but EPPlus is a 3rd party library. If you have question about the EPPlus license, then you should contact EPPlus support or the NuGet package owners.

https://www.epplussoftware.com/

I wanted information about EPPLUs but after consulting the site I can't use it for non-commercial purposes in my case.

Do you have any suggestions for reading Excel files in Blazor wasn;

I am trying NPOI with the code below.

I received the message

Uncaught (in promise) Error: System.NullReferenceException: Object reference not set to an instance of an object.at AppWeb.Client.Pages.Gpao.ExcelR.ImportExcelFile(InputFileChangeEventArgs e) in C:\App\sbd\AppWeb_v1\AppWeb\Client\Pages\Gpao\ExcelR.razor:line 66

 DataTable dt = new DataTable(); async Task ImportExcelFile(InputFileChangeEventArgs e) { var fileStream = e.File.OpenReadStream(); var ms = new MemoryStream(); await fileStream.CopyToAsync(ms); fileStream.Close(); ms.Position = 0; ISheet sheet; var xsswb = new XSSFWorkbook(ms); sheet = xsswb.GetSheetAt(0); IRow hr = sheet.GetRow(0); var rl = new List<string>(); int cc = hr.LastCellNum; for(var j=0; j <= cc; j++) { ICell cell = hr.GetCell(j); dt.Columns.Add(cell.ToString()); } for(var j=(sheet.FirstRowNum+1); j <= sheet.LastRowNum; j++) { var r = sheet.GetRow(j); for(var i = r.FirstCellNum; i < cc; i++) { rl.Add(r.GetCell(i).ToString()); } if (rl.Count > 0) { dt.Rows.Add(rl.ToArray()); } rl.Clear(); } }

I used the link I used the link https://www.youtube.com/watch?v=ksMZZ09-oEw

So NPOI seems to work with blazor wasm!

Fantastic I'm not sure because I received this message

Have you an idea how I can fix this message?

Uncaught (in promise) Error: System.NullReferenceException: Object reference not set to an instance of an object.at AppWeb.Client.Pages.Gpao.ExcelR.ImportExcelFile(InputFileChangeEventArgs e) in C:\App\sbd\AppWeb_v1\AppWeb\Client\Pages\Gpao\ExcelR.razor:line 66

I'm a bit confused... So you did not figure it out?

The null reference exception is shows the line caused the error and usually it shows the object name. You must understand that we have no idea what is on line 66 or if the error stated elsewhere.

Is there some valid reason why you can't simply run your code through the debugger?

Hi @sblb ,

I tested it with the code you provided and there is no null reference exception. I followed the video link you provided to test it and it works fine. Can you provide more details?

Hi, it's same for me I follow up the video and I have no null reference exception.

My application is blazor wasm .Net6 Core.

I use the library NPOI 2.6.0.

I received the message in dev tool.

..\ExcelR.razor:line 65 is

blazor.webassembly.js:1 Uncaught (in promise) Error: System.NullReferenceException: Object reference not set to an instance of an object. at AppWeb.Client.Pages.Gpao.ExcelR.ImportExcelFile(InputFileChangeEventArgs e) in C:\App\sbd\AppWeb_v1\AppWeb\Client\Pages\Gpao\ExcelR.razor:line 65 at Microsoft.AspNetCore.Components.ComponentBase.CallStateHasChangedOnAsyncCompletion(Task task) at Object.endInvokeDotNetFromJS (blazor.webassembly.js:1:3531) at Object.Rt [as endInvokeDotNetFromJS] (blazor.webassembly.js:1:60651) at Object.St [as invokeJSFromDotNet] (blazor.webassembly.js:1:60134) at _mono_wasm_invoke_js_blazor (dotnet.6.0.10.1jop6oovn6.js:1:195300) at 00971d2a:0x1a492 at 00971d2a:0xce60 at 00971d2a:0xbd73 at 00971d2a:0xabebf at 00971d2a:0x6fc80 at 00971d2a:0x702edendInvokeDotNetFromJS @ blazor.webassembly.js:1Rt @ blazor.webassembly.js:1St @ blazor.webassembly.js:1_mono_wasm_invoke_js_blazor @ dotnet.6.0.10.1jop6oovn6.js:1$func219 @ 00971d2a:0x1a492$func167 @ 00971d2a:0xce60$func166 @ 00971d2a:0xbd73$func2815 @ 00971d2a:0xabebf$func1619 @ 00971d2a:0x6fc80$func1623 @ 00971d2a:0x702ed$func3217 @ 00971d2a:0xc4e51$mono_background_exec @ 00971d2a:0x9430aModule._mono_background_exec @ dotnet.6.0.10.1jop6oovn6.js:1pump_message @ dotnet.6.0.10.1jop6oovn6.js:1setTimeout (asynchrone)_schedule_background_exec @ dotnet.6.0.10.1jop6oovn6.js:1$func2392 @ 00971d2a:0x942bb$func3216 @ 00971d2a:0xc4de1$func219 @ 00971d2a:0x1a4fa$func167 @ 00971d2a:0xce60$func166 @ 00971d2a:0xbd73$func2815 @ 00971d2a:0xabebf$func1619 @ 00971d2a:0x6fc80$func1623 @ 00971d2a:0x702ed$mono_wasm_invoke_method @ 00971d2a:0x969fModule._mono_wasm_invoke_method @ dotnet.6.0.10.1jop6oovn6.js:1managed__Microsoft_AspNetCore_Components_WebAssembly__Microsoft_AspNetCore_Components_WebAssembly_Services_DefaultWebAssemblyJSRuntime_EndInvokeJS @ managed__Microsoft_AspNetCore_Components_WebAssembly__Microsoft_AspNetCore_Components_WebAssembly_Services_DefaultWebAssemblyJSRuntime_EndInvokeJS:16endInvokeJSFromDotNet @ blazor.webassembly.js:1(anonymes) @ blazor.webassembly.js:1Promise.then (asynchrone)beginInvokeJSFromDotNet @ blazor.webassembly.js:1St @ blazor.webassembly.js:1_mono_wasm_invoke_js_blazor @ dotnet.6.0.10.1jop6oovn6.js:1$func219 @ 00971d2a:0x1a492$func167 @ 00971d2a:0xce60$func166 @ 00971d2a:0xbd73$func2815 @ 00971d2a:0xabebf$func1619 @ 00971d2a:0x6fc80$func1623 @ 00971d2a:0x702ed$mono_wasm_invoke_method @ 00971d2a:0x969fModule._mono_wasm_invoke_method @ dotnet.6.0.10.1jop6oovn6.js:1managed__Microsoft_AspNetCore_Components_WebAssembly__Microsoft_AspNetCore_Components_WebAssembly_Services_DefaultWebAssemblyJSRuntime_EndInvokeJS @ managed__Microsoft_AspNetCore_Components_WebAssembly__Microsoft_AspNetCore_Components_WebAssembly_Services_DefaultWebAssemblyJSRuntime_EndInvokeJS:16endInvokeJSFromDotNet @ blazor.webassembly.js:1(anonymes) @ blazor.webassembly.js:1Promise.then (asynchrone)beginInvokeJSFromDotNet @ blazor.webassembly.js:1St @ blazor.webassembly.js:1_mono_wasm_invoke_js_blazor @ dotnet.6.0.10.1jop6oovn6.js:1$func219 @ 00971d2a:0x1a492$func167 @ 00971d2a:0xce60$func166 @ 00971d2a:0xbd73$func2815 @ 00971d2a:0xabebf$func1619 @ 00971d2a:0x6fc80$func1617 @ 00971d2a:0x6fbf2$func970 @ 00971d2a:0x50643$func219 @ 00971d2a:0x1a44b$func167 @ 00971d2a:0xce60$func166 @ 00971d2a:0xbd73$func2815 @ 00971d2a:0xabebf$func1619 @ 00971d2a:0x6fc80$func1617 @ 00971d2a:0x6fbf2$func970 @ 00971d2a:0x50643$func219 @ 00971d2a:0x1a44b$func167 @ 00971d2a:0xce60$func166 @ 00971d2a:0xbd73$func2815 @ 00971d2a:0xabebf$func1619 @ 00971d2a:0x6fc80$func1623 @ 00971d2a:0x702ed$mono_wasm_invoke_method @ 00971d2a:0x969fModule._mono_wasm_invoke_method @ dotnet.6.0.10.1jop6oovn6.js:1managed__Microsoft_AspNetCore_Components_WebAssembly__Microsoft_AspNetCore_Components_WebAssembly_Services_DefaultWebAssemblyJSRuntime_BeginInvokeDotNet @ managed__Microsoft_AspNetCore_Components_WebAssembly__Microsoft_AspNetCore_Components_WebAssembly_Services_DefaultWebAssemblyJSRuntime_BeginInvokeDotNet:19beginInvokeDotNetFromJS @ blazor.webassembly.js:1b @ blazor.webassembly.js:1invokeMethodAsync @ blazor.webassembly.js:1(anonymes) @ blazor.webassembly.js:1blazor.webassembly.js:1 Uncaught (in promise) Error: System.Data.DuplicateNameException: A column named 'test1' already belongs to this DataTable. at System.Data.DataColumnCollection.RegisterColumnName(String name, DataColumn column) at System.Data.DataColumnCollection.BaseAdd(DataColumn column) at System.Data.DataColumnCollection.AddAt(Int32 index, DataColumn column) at System.Data.DataColumnCollection.Add(DataColumn column) at System.Data.DataColumnCollection.Add(String columnName) at AppWeb.Client.Pages.Gpao.ExcelR.ImportExcelFile(InputFileChangeEventArgs e) in C:\App\sbd\AppWeb_v1\AppWeb\Client\Pages\Gpao\ExcelR.razor:line 65 at Microsoft.AspNetCore.Components.ComponentBase.CallStateHasChangedOnAsyncCompletion(Task task) at Object.endInvokeDotNetFromJS (blazor.webassembly.js:1:3531) at Object.Rt [as endInvokeDotNetFromJS] (blazor.webassembly.js:1:60651) at Object.St [as invokeJSFromDotNet] (blazor.webassembly.js:1:60134) at _mono_wasm_invoke_js_blazor (dotnet.6.0.10.1jop6oovn6.js:1:195300) at 00971d2a:0x1a492 at 00971d2a:0xce60 at 00971d2a:0xbd73 at 00971d2a:0xabebf at 00971d2a:0x6fc80 at 00971d2a:0x702ed

In excel file the name of columns were : test1, test2 and test3. I changed the name id, price and sold in this case the value of excel is rendering. But I don't understand why is not rendering as table see the result below

do you have any idea why this is not rendered with columns?

I see hthe data table.

I'm not sure that you understand. The code populate each row (cell(I,j) as the matrix) associate at each column.

So list <string> seems to be a good way for each columns. After that Each list <string> is added with type ToArray()

dt.Rows.Add(rl.ToArray());

So my question : why in user interface I don't have the result as a table?

So my question : why in user interface I don't have the result as a table?

After asking several times, you still have not provided the user interface so we can only guess what's wrong. We also cannot see the Excel spreadsheet format and using a DataTable is a rather odd design choice. Usually, a view model is used in .NET Core applications.

Is there some reason why you cannot review your code and fix this on your own? Perhaps fire up the Visual Studio debugger and single step through your design to find the bug(s).

 <table class="table"> <thead> <tr> @foreach(DataColumn col in dt.Columns) { <th>@col.ColumnName</th> } </tr> </thead> <tboby> @foreach(DataRow row in dt.Rows) { <tr> @foreach(DataColumn col in dt.Columns) { <td>@row[col.ColumnName].ToString()</td> } </tr> } </tboby> </table>

The screenshot shows all the data is under the Id header. We can only guess why this is happening because we do not have the code or the Excel spreadsheet. Basic troubleshooting should lead you the source of the problem.

For example, is the HTML table malformed? All you need to do is review the HTML.

Another way to determine a malformed HTML table is displaying all the table cell boarders rather than only the horizontal boarder. Both steps, viewing the HTML and setting the boarders, takes only takes a few seconds.

The Excel spreadsheet data source and can contribute to the problem too. We cannot see the Excel spreadsheet but you can.

The Visual Studio debugger is a very powerful tool. You get to set a break point and single step through the loops. Each time verifying the variable contents are what you expect.

The HTML table is based on the DataTable. The debugger can display the context of the DataTable. If the DataTable format is incorrect then there is a problem loading the Excel data. If the DataTable is correct then there is a problem populating the HTML. This is a basic troubleshooting step. The idea is to eliminate possible issues until you zero in on the problematic code.

Please make an effort to come up with a troubleshooting strategy and debug your own code.