c# – EPPlus – 阅读Excel表格

发布时间：2020-12-15 08:04:55 所属栏目：百科来源：网络整理

导读：使用EPPlus,我想读取一个excel表,然后将每列的所有内容存储到相应的List中.我希望它能识别表的标题并根据它对内容进行分类. 例如,如果我的excel表如下： Id Name Gender 1 John Male 2 Maria Female 3 Daniel Unknown 我希望数据存储在List ExcelData中哪里

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

例如,如果我的excel表如下：

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

这样我就可以使用标题名称调出内容.例如,当我这样做时：

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

它会给我这个输出：

1JohnMale
2MariaFemale
3DanielUnknown

这真的是我得到的：

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设法做到这一点,但它无法识别表.

解决方法

没有原生,但如果你使用我在这篇文章中添加的内容,那该怎么办：

How to parse excel rows back to types using EPPlus

如果您只想将其指向一个表,则需要对其进行修改.这样的事情应该这样做：

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 groups = table.WorkSheet.Cells[table.Address.Start.Row,table.Address.Start.Column,table.Address.End.Row,table.Address.End.Column]
        .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))
                {
                    //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:tempTable_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中的数字或字符串,请注意,因为类需要字符串.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!