EPPlus - Read Excel Table

Question

Using EPPlus, I want to read an excel table, then store all the contents from each column into its corresponding List. I want it to recognize the table's heading and categorize the contents based on that.

For example, if my excel table is as below:

Id    Name     Gender  1    John     Male  2    Maria    Female  3    Daniel   Unknown 

I want the data to store in List<ExcelData> where

public class ExcelData {     public string Id { get; set; }     public string Name { get; set; }     public string Gender { get; set; } } 

So that I can call out the contents using the heading name. For example, when I do this:

foreach (var data in ThatList) {      Console.WriteLine(data.Id + data.Name + data.Gender); } 

It will give me this output:

1JohnMale 2MariaFemale 3DanielUnknown 

---

This is really all I got:

var package = new ExcelPackage(new FileInfo(@"C:\ExcelFile.xlsx")); ExcelWorksheet sheet = package.Workbook.Worksheets[1];  var table = sheet.Tables.First();  table.Columns.Something //I guess I can use this to do what I want 

Please help :( I have spent long hours searching for sample code regarding this so that I can learn from it but to no avail. I also understand ExcelToLinQ is managed to do that but it can't recognize table.

Answer 1

Not sure why but none of the above solution work for me. So sharing what worked:

public void readXLS(string FilePath) {     FileInfo existingFile = new FileInfo(FilePath);     using (ExcelPackage package = new ExcelPackage(existingFile))     {         //get the first worksheet in the workbook         ExcelWorksheet worksheet = package.Workbook.Worksheets[1];         int colCount = worksheet.Dimension.End.Column;  //get Column Count         int rowCount = worksheet.Dimension.End.Row;     //get row count         for (int row = 1; row <= rowCount; row++)         {             for (int col = 1; col <= colCount; col++)             {                 Console.WriteLine(" Row:" + row + " column:" + col + " Value:" + worksheet.Cells[row, col].Value?.ToString().Trim());             }         }     } }