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

Hi, I want to use the Excel file as data source to populate the table

I used the package EPPlus.

I received the message :

OfficeOpenXml.LicenseException: Please set the ExcelPackage.LicenseContext property

In my service I've put :

ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

I don't understand why I received the message to set the package because I used Noncommercial package.

Have you an idea? or have you other open source and free to read excel file and populate in table?

Thanks in advance

0{count} votes

  1. Excel as data source - Microsoft Q&A (1)

    AgaveJoe 16,626Reputation points

    2023-01-12T12:37:23.0166667+00:00

    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/

  2. Excel as data source - Microsoft Q&A (2)

    sblb 831Reputation points

    2023-01-12T13:44:34.5633333+00:00

    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(); } }
  3. Excel as data source - Microsoft Q&A (3)

    AgaveJoe 16,626Reputation points

    2023-01-12T16:28:47.2666667+00:00

    Have you verified NPOI works in Blazor? I thought the code had to run on a server.

  4. Excel as data source - Microsoft Q&A (5)

    AgaveJoe 16,626Reputation points

    2023-01-12T19:15:45.1033333+00:00

    Fantastic! You figured it out.

  5. Excel as data source - Microsoft Q&A (6)

    sblb 831Reputation points

    2023-01-12T19:31:38+00:00

    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
  6. Excel as data source - Microsoft Q&A (7)

    AgaveJoe 16,626Reputation points

    2023-01-12T22:48:41.7466667+00:00

    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?

  7. Excel as data source - Microsoft Q&A (8)

    Chen Li - MSFT 171Reputation points Microsoft Employee

    2023-01-13T02:50:12.97+00:00

    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?

  8. Excel as data source - Microsoft Q&A (9)

    sblb 831Reputation points

    2023-01-13T09:52:34.2166667+00:00

    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

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

    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
  9. Excel as data source - Microsoft Q&A (11)

    sblb 831Reputation points

    2023-01-13T10:53:30.2333333+00:00

    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

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

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

  10. Excel as data source - Microsoft Q&A (13)

    AgaveJoe 16,626Reputation points

    2023-01-13T15:34:59.9633333+00:00

    The code you shared places the data is a List<string> then a data table row. We cannot see the user interface so we have no idea how the data is bound or what component is responsible for generating the HTML.

  11. Excel as data source - Microsoft Q&A (14)

    sblb 831Reputation points

    2023-01-14T11:55:47.1+00:00

    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?

  12. Excel as data source - Microsoft Q&A (15)

    AgaveJoe 16,626Reputation points

    2023-01-14T15:42:13.7666667+00:00

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

  13. Excel as data source - Microsoft Q&A (16)

    sblb 831Reputation points

    2023-01-15T12:03:48.6133333+00:00

     <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>
  14. Excel as data source - Microsoft Q&A (17)

    AgaveJoe 16,626Reputation points

    2023-01-15T13:13:35.5433333+00:00

    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.

  15. Excel as data source - Microsoft Q&A (18)

    sblb 831Reputation points

    2023-01-16T08:36:59.8+00:00

    Thanks to your explanation but I didn't see the mistake.

Sign in to comment

Top Articles
Latest Posts
Article information

Author: Maia Crooks Jr

Last Updated: 11/18/2022

Views: 6043

Rating: 4.2 / 5 (43 voted)

Reviews: 82% of readers found this page helpful

Author information

Name: Maia Crooks Jr

Birthday: 1997-09-21

Address: 93119 Joseph Street, Peggyfurt, NC 11582

Phone: +2983088926881

Job: Principal Design Liaison

Hobby: Web surfing, Skiing, role-playing games, Sketching, Polo, Sewing, Genealogy

Introduction: My name is Maia Crooks Jr, I am a homely, joyous, shiny, successful, hilarious, thoughtful, joyous person who loves writing and wants to share my knowledge and understanding with you.