Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
799 views
in Technique[技术] by (71.8m points)

c# - EPPlus - Read Excel Table

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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());
            }
        }
    }
}

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...