如何解析的Excel行回使用EPPlus类型 [英] How to parse excel rows back to types using EPPlus

问题描述

EPPlus有一个方便的 LoadFromCollection< T> 的方法来得到我自己的类型的数据到工作表

EPPlus has a convenient LoadFromCollection<T> method to get data of my own type into a worksheet.

例如，如果我有一个类：

For example if I have a class:

public class Customer
{
    public int Id { get; set; }
    public string Firstname { get; set; }
    public string Surname { get; set; }
    public DateTime Birthdate { get; set; }
}

然后将以下代码：

Then the following code:

var package = new ExcelPackage();
var sheet = package.Workbook.Worksheets.Add("Customers");
var customers = new List<Customer>{
    new Customer{
        Id = 1,
        Firstname = "John",
        Surname = "Doe",
        Birthdate = new DateTime(2000, 1, 1)
    },
    new Customer{
        Id = 2,
        Firstname = "Mary",
        Surname = "Moe",
        Birthdate = new DateTime(2001, 2, 2)
    }
};
sheet.Cells[1, 1].LoadFromCollection(customers);
package.Save();

...将增加2行到一个名为客户的工作表。

...will add 2 rows to a worksheet called "Customers".

我的问题是，如果有一个方便的对应从Excel中提取的行（例如一些修改已经作出之后）回到我的类型。

My question is if there is a convenient counterpart to extract the rows from excel (for example after some modifications have been made) back into my types.

是这样的：

var package = new ExcelPackage(inputStream);
var customers = sheet.Dimension.SaveToCollection<Customer>() ??

我

I have

• 一直在寻找通过EPPlus代码库


• 搜索任何的节省问题


• 搜索任何的解析问题


• 在阅读单个细胞问题

• been looking through the EPPlus codebase
• searched for any saving questions
• searched for any parsing questions
• seen this question on reading single cells

...但发现如何简单的行解析到我喜欢的类型无关。

... but found nothing on how to simply parse the rows to my type.

推荐答案

这是我把它稍微不同的路线上的启发。

Inspired by the above I took it a slightly different route.

1. 我创建了一个属性，映射每个属性一列。


2. 我用的是DTO类型来定义什么我希望每一列是


3. 允许不requried列


4. 使用EPPlus转换的类型

1. I created an attribute and mapped each property to a column.
2. I use the DTO type to define what I expect each column to be
3. Allow columns to not be requried
4. Use EPPlus to convert the types

这样它可以让我用传统的模型验证，拥抱变化列标题

By doing so it allows me to use traditional model validation, and embrace changes to column headers

-
用法：

-- Usage:

using(FileStream fileStream = new FileStream(_fileName, FileMode.Open)){
      ExcelPackage excel = new ExcelPackage(fileStream);
      var workSheet = excel.Workbook.Worksheets[RESOURCES_WORKSHEET];

      IEnumerable<ExcelResourceDto> newcollection = workSheet.ConvertSheetToObjects<ExcelResourceDto>();
      newcollection.ToList().ForEach(x => Console.WriteLine(x.Title));
 }

DTO映射到Excel

Dto that maps to excel

public class ExcelResourceDto
{
    [Column(1)]
    [Required]
    public string Title { get; set; }

    [Column(2)]
    [Required]
    public string SearchTags { get; set; }
}

这是属性定义

[AttributeUsage(AttributeTargets.All)]
public class Column : System.Attribute
{
    public int ColumnIndex { get; set; }


    public Column(int column) 
    {
        ColumnIndex = column;
    }
} 

扩展类来处理映射行DTO

Extension class to handle mapping rows to DTO

public static class EPPLusExtensions
{
   public static IEnumerable<T> ConvertSheetToObjects<T>(this ExcelWorksheet worksheet) where T : new()
    {

        Func<CustomAttributeData, bool> columnOnly = y => y.AttributeType == typeof(Column);

        var columns = typeof(T)
                .GetProperties()
                .Where(x => x.CustomAttributes.Any(columnOnly))
        .Select(p => new
        {
            Property = p,
            Column = p.GetCustomAttributes<Column>().First().ColumnIndex //safe because if where above
        }).ToList();


        var rows= worksheet.Cells
            .Select(cell => cell.Start.Row)
            .Distinct()
            .OrderBy(x=>x);


        //Create the collection container
        var collection = rows.Skip(1)
            .Select(row =>
            {
                var tnew = new T();
                columns.ForEach(col =>
                {
                    //This is the real wrinkle to using reflection - Excel stores all numbers as double including int
                    var val = worksheet.Cells[row, col.Column];
                    //If it is numeric it is a double since that is how excel stores all numbers
                    if (val.Value == null)
                    {
                        col.Property.SetValue(tnew, null);
                        return;
                    }
                    if (col.Property.PropertyType == typeof(Int32))
                    {
                        col.Property.SetValue(tnew, val.GetValue<int>());
                        return;
                    }
                    if (col.Property.PropertyType == typeof(double))
                    {
                        col.Property.SetValue(tnew, val.GetValue<double>());
                        return;
                    }
                    if (col.Property.PropertyType == typeof(DateTime))
                    {
                        col.Property.SetValue(tnew, val.GetValue<DateTime>());
                        return;
                    }
                    //Its a string
                    col.Property.SetValue(tnew, val.GetValue<string>());
                });

                return tnew;
            });


        //Send it back
        return collection;
    }
}

这篇关于如何解析的Excel行回使用EPPlus类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！