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.

    (Video) How to use Excel as Data Source in SSRS Report - SSRS Interview Questions and Answers
  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?

    (Video) Microsoft Excel QA with Kyle Pew - Office Newb

    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)

    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)

    (Video) Using Power Query in Microsoft Excel to Join Multiple Data Sources

    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

    (Video) Microsoft Excel PivotTable and Dashboard QA with Kyle Pew - Office Newb

    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

Sign in to comment

(Video) Using Microsoft Excel Data Link and Refresh

FAQs

How do I create a data source in Excel? ›

Configuring Microsoft Excel or CSV Data Sources
  1. In the menu, click Project:<Project Name> > Project Settings .
  2. Click the Data Sources tab. ...
  3. Click New Data Source to open the New Data Source dialog box.
  4. Type a Name for the data source.

Does ADF support Excel? ›

The service supports both ". xls" and ". xlsx". Excel format is supported for the following connectors: Amazon S3, Amazon S3 Compatible Storage, Azure Blob, Azure Data Lake Storage Gen1, Azure Data Lake Storage Gen2, Azure Files, File System, FTP, Google Cloud Storage, HDFS, HTTP, Oracle Cloud Storage and SFTP.

How do I read data from Excel? ›

To read data from Excel cells, use the Excel runtime object. In some advanced cases, for instance, when you work with ranges of cells in Excel files, you can use the Excel. Application COM object.

What are the three 3 different types of data source? ›

Types of data sources
  • relational.
  • multidimensional (OLAP)
  • dimensionally modeled relational.

What is data source in Excel? ›

The Excel data source obtains data from the Microsoft Excel file. It assigns data from the columns to the properties in the order they are listed in the editor. That is, the value of the first column will be assigned to the first property, the second column - to the second property, and so on.

How do I automatically input data from another sheet in Excel? ›

Or, there's an easier option.
  1. Type = in the cell where you want to reference data from other sheets.
  2. Toggle to the source sheet.
  3. Click the cell being copied.
  4. Hit enter, and the function will automatically populate.

How do you automatically pull data from another sheet in Excel based on the criteria? ›

How to Pull Data From Another Sheet in Excel Using Cell References
  1. Click in the cell where you want the pulled data to appear.
  2. Type = (equals sign) followed by the name of the sheet you want to pull data from. ...
  3. Type ! ...
  4. Press Enter.
  5. The value from your other sheet will now appear in the cell.
May 20, 2022

How do I link data from one spreadsheet to another? ›

From source worksheet, select the cell that contains data or that you want to link to another worksheet, and copy it by pressing the Copy button from the Home tab or press CTRL+C. Go to the destination worksheet and click the cell where you want to link the cell from the source worksheet.

Which is better ADF or SSIS? ›

Azure Data Factory has built-in support for Azure HDInsight, a managed Hadoop service. This means that the service can be used to process big data sets, something that would be difficult to do with SSIS. Azure Data Factory supports both batch and streaming data processes while SSIS supports only batch processes.

Is ADF an ETL tool? ›

Azure Data Factory is a cloud-based ETL and data integration service to create workflows for moving and transforming data. With Data Factory you can create scheduled workflows (pipelines) in a code-free manner.

How do I convert Excel to CSV in ADF? ›

Using Excel packager API with Azure Data Factory
  1. Step 1: Create one csv file in blob storage for each tab with path of /EXCELFILENAME/SheetName.csv.
  2. Step 2: Call the API with the above details using Web or Webhook blocks.
  3. Step 3: Read and/or move the excel file to intended destination.
  4. Step 4: Cleanup files.

What is the fastest way to analyze data in Excel? ›

Instant charts using Quick Analysis
  1. Select a range of cells.
  2. Select the Quick Analysis button that appears at the bottom right corner of the selected data. ...
  3. Select Charts.
  4. Hover over the chart types to preview a chart, and then select the chart you want.
  5. Select More > All Charts to all available see all charts available.

What is the easiest way to get data in Excel? ›

Use the Go To command to quickly find and select all cells that contain specific types of data, such as formulas. Also, use Go To to find only the cells that meet specific criteria,—such as the last cell on the worksheet that contains data or formatting.

Can Excel pull data from a text file? ›

You can import data from a text file into an existing worksheet. Click the cell where you want to put the data from the text file. On the Data tab, in the Get External Data group, click From Text. In the Import Data dialog box, locate and double-click the text file that you want to import, and click Import.

What are the 5 sources of data? ›

The Top 5 Sources of Data on your Website
  • Reports: Conversion funnel and pathing. Study these reports. ...
  • Internal search queries. This seems like a no-brainer, but some brands forget to assess what people are searching for on their website. ...
  • Store locators. ...
  • Reviews & Customer Service inquiries. ...
  • Domain reports.

What are the 3 most important sources of data? ›

Sources of Data
  • Observation Method.
  • Survey Method.
  • Experimental Method.

What are the two main sources of data? ›

Data can be gathered from two places: internal and external sources. The information collected from internal sources is called “primary data,” while the information gathered from outside references is called “secondary data.” For data analysis, it all must be collected through primary or secondary research.

What is the main purpose of data source? ›

The purpose of a data source is to gather all of the technical information needed to access the data - the driver name, network address, network software, and so on - into a single place and hide it from the user.

What are the four types of data sources? ›

What are Data Source Types?
  • Databases.
  • Flat files.
  • Web services.
  • Other sources such as RSS feeds.

What are the 3 types of data in Excel? ›

The four types of data are text, number, logical and error. You may perform different functions with each type, so it's important to know which ones to use and when to use them.

How do you create a data source? ›

To create a dataset as a data source, run the Data Source Configuration Wizard by selecting Project > Add New Data Source. Choose the Database data-source type, and follow the prompts to specify either a new or existing database connection, or a database file.

How do I create a custom data source? ›

To create a custom datasource, we need to go to transaction RSO2. Select the type of datasource we want to create, For Transaction data or Master data or Texts… Specify the Datasource name and click on create button.

How do you add a source of data? ›

Just like sources such as books and journal articles, data used in research requires and deserves proper credits.
...
How to cite data?
  1. Author(s)
  2. Date of publication.
  3. Title of dataset.
  4. Publisher or distributor.
  5. Persistent locator/identifier (ex. URL or DOI)
  6. Version, when appropriate.
  7. Date accessed, when appropriate.
Jan 31, 2021

Where is data source option in Excel? ›

To display the Data Source Settings dialog box, do one of the following: In Power Query Select File > Home > Data Source Settings. In Excel Select Data > Get Data > Data Source Settings.

Videos

1. Using Excel as a Tableau Data Source - Tableau in Two Minutes
(Penguin Analytics)
2. Top 5 Excel Interview Questions
(The Career Force)
3. Creating a Database in Excel [Excel is a Database]
(That Office Guy)
4. Mail Merge from Excel to Microsoft Word
(Technology for Teachers and Students)
5. Cove Data Protection Q&A: Which files are best "not-to-backup" outside of the windows registry key?
(N-able MSP Institute)
6. Top Excel Interview Questions for Data Analytics Job
(Joseph Okon)
Top Articles
Latest Posts
Article information

Author: Rev. Porsche Oberbrunner

Last Updated: 12/24/2022

Views: 6343

Rating: 4.2 / 5 (73 voted)

Reviews: 88% of readers found this page helpful

Author information

Name: Rev. Porsche Oberbrunner

Birthday: 1994-06-25

Address: Suite 153 582 Lubowitz Walks, Port Alfredoborough, IN 72879-2838

Phone: +128413562823324

Job: IT Strategist

Hobby: Video gaming, Basketball, Web surfing, Book restoration, Jogging, Shooting, Fishing

Introduction: My name is Rev. Porsche Oberbrunner, I am a zany, graceful, talented, witty, determined, shiny, enchanting person who loves writing and wants to share my knowledge and understanding with you.