Hello everyone,
Recently I have been trying to make a basic C# WPF app that will read from an Excel spreadsheet and display the info in a DataTable. I am about a year into programming at this point and I just started learning C# and WPF by YouTube videos, a udemy course and breaking my application. Anyways, I have been toying with various NuGet packages such as the ExcelDataReader(I used this tutorial by Fox Learn).
I however did the tutorial with WPF instead of Forms, and with some struggle I had success with displaying the info in a DataTable. I have been trying to integrate this in a Revit plugin as a part of a bigger application. In Excel, one can display a sort of pulldown or combo box that includes more choices of data for a cell. I want to be able to replicate that in my window and I am just not sure how yet. So I went to my new friend ChatGPT and started asking it some questions about different NuGet packages for reading, writing, and creating Excel spreadsheets. I further researched and found most people liked EPPlus. So I have been using that, it is well documented and the tutorials are easy to follow. I ran into an issue, I was trying to store the worksheets in a method that returned the sheets and pass them around my application but I had kept receiving a NullReferenceException.
For example:

public ExcelWorksheets getSheets()
  {
    string fileName = txtFilename.Text;
    using (var package = new ExcelPackage(fileName))
      {
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        ExcelWorksheets Sheets = package.Workbook.Worksheets;
        return Sheets;
    }
 }

ExcelWorksheets sheets = getSheets();
addSheetsToComboBox(sheets);

public void addSheetsToComboBox(ExcelWorksheets sheets)
    {
//NullReferenceException thrown here
      foreach(sheet in sheets)
        cboSheet.Items.Add(sheet)
}

This had me banging my head against wall for a good part of my day. I have since refactored this a bit to where I have the data displaying in the DataTable now and I am not getting the Exception. I ended up using the 'using()' statement wherever I needed, just to move on which actually ended up working pretty well in the end. Another issue was getting the cell data to display properly and dynamically in for the DataTable. Which is what I am still tinkering with now. I was receiving several errors when trying to get the data from the cells into the DataTable.
See below:

using (var package = new ExcelPackage(fileName))
  {
    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
    Trace.WriteLine(cboSheet.SelectedIndex);
    ExcelWorksheet Sheet = 
    package.Workbook.Worksheets[cboSheet.SelectedIndex];
//I receive an InvalidOperationException from the below code, 'First row contains an empty cell at index 3'
    var cells = Sheet.Cells.ToDataTable();

This did not work, the package required me to use a specific range for the data table.
Like below:
var cells = Sheet.Cells["A1:C2"].ToDataTable();
This worked like a charm but, the issue is I am providing the option to select different sheets from the workbook. So if the above code is provided then I am out of luck and same if I choose another workbook. So I dynamically need a way to get the cells from each sheet to a DataTable. So I found a way to get all of the cells I need to a dictionary(via stackoverflow, which introduced me to LINQ).

var cells = Sheet.Cells;
var dict = cells
   .GroupBy(c => new { c.Start.Row, c.Start.Column })
   .ToDictionary(
         rcg => new KeyValuePair<int, int>(rcg.Key.Row, rcg.Key.Column),
         rcg => cells[rcg.Key.Row, rcg.Key.Column].Value);

Then I needed a way to get the dictionary to a DataTable(via stackoverflow, which introduced the different methods for creating a new DataTable).

DataTable dt = new DataTable();
dt.Columns.Add("Key", typeof(int));
dt.Columns.Add("Val", typeof(int));
dt.Columns.Add("Obj", typeof(string));
foreach (var item in dict)
  {
    DataRow dr = dt.NewRow();
    dr["Key"] = item.Key.Key;
    dr["Val"] = item.Key.Value;
    dr["Obj"] = item.Value;
    dt.Rows.Add(dr);
 }                  

dataGrid1.SetBinding(ItemsControl.ItemsSourceProperty, new Binding { Source = dt });

This has been both frustrating and loads of fun. I am learning a lot. The code feels like a bit of a mess which is something else I would like to fix as well. As I was writing this, I started wondering about the possibilities on how this could be done better. Anyways, if you read through this comments or feedback is always appreciated!