EPPlus-读取Excel表 [英] EPPlus - Read Excel Table

问题描述

使用EPPlus，我想读取一个excel表，然后将每列中的所有内容存储到其相应的List中.我希望它能够识别表的标题并根据该表对内容进行分类.

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.

例如，如果我的excel表如下:

For example, if my excel table is as below:

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

我希望数据存储在List<ExcelData>其中

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

它将给我以下输出:

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

请帮助:( 我花了很长时间搜索与此相关的示例代码，以便我可以从中学习，但无济于事.我也知道ExcelToLinQ可以做到这一点，但是它无法识别表.

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.

推荐答案

没有原生语言，但是如果您使用我在这篇文章中提到的内容，该怎么办:

There is no native but what if you use what I put in this post:

如何使用以下方法将excel行解析回类型EPPlus

如果要将其指向一个表，则只需对其进行修改.像这样的事情应该做到这一点:

If you want to point it at a table only it will need to be modified. Something like this should do it:

public static IEnumerable<T> ConvertTableToObjects<T>(this ExcelTable table) where T : new()
{
    //DateTime Conversion
    var convertDateTime = new Func<double, DateTime>(excelDate =>
    {
        if (excelDate < 1)
            throw new ArgumentException("Excel dates cannot be smaller than 0.");

        var dateOfReference = new DateTime(1900, 1, 1);

        if (excelDate > 60d)
            excelDate = excelDate - 2;
        else
            excelDate = excelDate - 1;
        return dateOfReference.AddDays(excelDate);
    });

    //Get the properties of T
    var tprops = (new T())
        .GetType()
        .GetProperties()
        .ToList();

    //Get the cells based on the table address
    var start = table.Address.Start;
    var end = table.Address.End;
    var cells = new List<ExcelRangeBase>();

    //Have to use for loops insteadof worksheet.Cells to protect against empties
    for (var r = start.Row; r <= end.Row; r++)
        for (var c = start.Column; c <= end.Column; c++)
            cells.Add(table.WorkSheet.Cells[r, c]);

    var groups = cells
        .GroupBy(cell => cell.Start.Row)
        .ToList();

    //Assume the second row represents column data types (big assumption!)
    var types = groups
        .Skip(1)
        .First()
        .Select(rcell => rcell.Value.GetType())
        .ToList();

    //Assume first row has the column names
    var colnames = groups
        .First()
        .Select((hcell, idx) => new { Name = hcell.Value.ToString(), index = idx })
        .Where(o => tprops.Select(p => p.Name).Contains(o.Name))
        .ToList();

    //Everything after the header is data
    var rowvalues = groups
        .Skip(1) //Exclude header
        .Select(cg => cg.Select(c => c.Value).ToList());

    //Create the collection container
    var collection = rowvalues
        .Select(row =>
        {
            var tnew = new T();
            colnames.ForEach(colname =>
            {
                //This is the real wrinkle to using reflection - Excel stores all numbers as double including int
                var val = row[colname.index];
                var type = types[colname.index];
                var prop = tprops.First(p => p.Name == colname.Name);

                //If it is numeric it is a double since that is how excel stores all numbers
                if (type == typeof(double))
                {
                    if (!string.IsNullOrWhiteSpace(val?.ToString()))
                    {
                        //Unbox it
                        var unboxedVal = (double)val;

                        //FAR FROM A COMPLETE LIST!!!
                        if (prop.PropertyType == typeof(Int32))
                            prop.SetValue(tnew, (int)unboxedVal);
                        else if (prop.PropertyType == typeof(double))
                            prop.SetValue(tnew, unboxedVal);
                        else if (prop.PropertyType == typeof(DateTime))
                            prop.SetValue(tnew, convertDateTime(unboxedVal));
                        else
                            throw new NotImplementedException(String.Format("Type '{0}' not implemented yet!", prop.PropertyType.Name));
                    }
                }
                else
                {
                    //Its a string
                    prop.SetValue(tnew, val);
                }
            });

            return tnew;
        });


    //Send it back
    return collection;
}

这是一种测试方法:

[TestMethod]
public void Table_To_Object_Test()
{
    //Create a test file
    var fi = new FileInfo(@"c:\temp\Table_To_Object.xlsx");

    using (var package = new ExcelPackage(fi))
    {
        var workbook = package.Workbook;
        var worksheet = workbook.Worksheets.First();
        var ThatList = worksheet.Tables.First().ConvertTableToObjects<ExcelData>();
        foreach (var data in ThatList)
        {
            Console.WriteLine(data.Id + data.Name + data.Gender);
        }

        package.Save();
    }
}

在控制台中将其保存:

1JohnMale
2MariaFemale
3DanielUnknown

如果您的ID字段是excel中的数字或字符串，请小心，因为类需要一个字符串.

Just be careful if you Id field is an number or string in excel since the class is expecting a string.

这篇关于EPPlus-读取Excel表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！