Comment analyser excel rangées à l'arrière à l'aide de types de EPPlus

EPPlus a une pratique LoadFromCollection<T> méthode pour obtenir des données de mon propre type de contenu dans une feuille de calcul.

Par exemple si j'ai une classe:

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

Alors le code suivant:

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

...va ajouter 2 lignes dans une feuille de calcul appelé "Clients".

Ma question est si il est un moyen pratique de contrepartie pour extraire les lignes à partir d'excel (par exemple, après certaines modifications ont été apportées), dans mon des types.

Quelque chose comme:

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

J'ai

de regarder à travers les EPPlus codebase
recherche toute d'économie d' questions
recherche toute l'analyse questions
vu cette question sur la lecture des cellules individuelles

... mais rien trouvé sur comment simplement analyser les lignes de mon type.

OriginalL'auteur Philip Bijker | 2015-10-30

c#epplus

Inspiré par le ci-dessus je l'ai pris une voie légèrement différente.

J'ai créé un attribut et cartographié chaque propriété à une colonne.
- Je utiliser la DTO type de définir ce que j'attends de chaque colonne à
Permettre colonnes de ne pas être exigé
Utilisation EPPlus pour convertir les types de

Ce faisant, elle me permet d'utiliser le modèle traditionnel de la validation, et de tenir compte des changements aux en-têtes de colonne

--
Utilisation:

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 que des cartes à excel

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

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

C'est la définition d'attribut

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


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

Classe d'Extension pour gérer la cartographie des lignes de 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;
}
}

Fonctionne très bien. Note pour les autres: const int RESOURCES_WORKSHEET = 1 (feuille de calcul des indices sont à 1)
Il est simple de modifier cela pour que la Colonne d'Attribut spécifie le Nom de la Colonne à la place de l'index de Colonne.
J'étais à la recherche d'un moyen efficace de cartographie de la PropertyInfo et de la Colonne dans une Liste, belle utilisation de Func et belle utilisation de Linq, j'ai de réduire le temps pour la même opération dans 7,5%, ce qui ne semble pas beaucoup, mais il va de 680k ms à 50 ms Cette solution serait plus rapide si vous appliquez Parallèle.foreach. J'ai beaucoup à apprendre de cette réponse. Merci!
pourriez-vous dire comment utiliser le Nom de la Colonne dans cet échantillon?
public class Colonne : Système.Attribut { public string Nom { get; set; } public Colonne(string nom) { Nom = nom; } } puis

var columns = typeof(T)                 .GetProperties()                 .Where(x => x.CustomAttributes.Any(columnOnly))                 .Select(p => new                 {                     Property = p,                     Column = p.GetCustomAttributes<Column>().First().Name,

OriginalL'auteur Nix

Il n'existe pas de méthode native de EPPlus malheureusement. Ses une noix dure à casser depuis que vous auriez à utiliser réflexions, si vous voulez vraiment qu'il soit générique. Et à cause de Excel le stockage de tous les numéros et les dates que le double que vous avez à traiter avec beaucoup d'unboxing et les vérifications de type.

C'est quelque chose que j'ai travaillé. C'est une méthode d'extension qui permettra de le faire via Generics. Il fonctionne, mais seulement dans un nombre limité de tests donc, assurez-vous que vous vérifiez vous-même. Je ne peux pas garantir qu'il est le plus optimisé (encore), mais il est assez décent à son point de vue. Vous pouvez l'utiliser comme ceci:

IEnumerable<TestObject> newcollection = worksheet.ConvertSheetToObjects<TestObject>();

L'extension:

public static IEnumerable<T> ConvertSheetToObjects<T>(this ExcelWorksheet worksheet) 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();
//Cells only contains references to cells with actual data
var groups = worksheet.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))
{
//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;
}

Un exemple COMPLET:

[TestMethod]
public void Read_To_Collection_Test()
{   
//A collection to Test
var objectcollection = new List<TestObject>();
for (var i = 0; i < 10; i++)
objectcollection.Add(new TestObject {Col1 = i, Col2 = i*10, Col3 = Path.GetRandomFileName(), Col4 = DateTime.Now.AddDays(i)});
//Create a test file to convert back
byte[] bytes;
using (var pck = new ExcelPackage())
{
//Load the random data
var workbook = pck.Workbook;
var worksheet = workbook.Worksheets.Add("data");
worksheet.Cells.LoadFromCollection(objectcollection, true);
bytes = pck.GetAsByteArray();
}
//*********************************
//Convert from excel to a collection
using (var pck = new ExcelPackage(new MemoryStream(bytes)))
{
var workbook = pck.Workbook;
var worksheet = workbook.Worksheets["data"];
var newcollection = worksheet.ConvertSheetToObjects<TestObject>();
newcollection.ToList().ForEach(to => Console.WriteLine("{{ Col1:{0}, Col2: {1}, Col3: \"{2}\", Col4: {3} }}", to.Col1, to.Col2, to.Col3, to.Col4.ToShortDateString()));
}
}
//test object class
public class TestObject
{
public int Col1 { get; set; }
public int Col2 { get; set; }
public string Col3 { get; set; }
public DateTime Col4 { get; set; }
}

Sortie De La Console:

{ Col1:0, Col2: 0, Col3: "wrulvxbx.wdv", Col4: 10/30/2015 }
{ Col1:1, Col2: 10, Col3: "wflh34yu.0pu", Col4: 10/31/2015 }
{ Col1:2, Col2: 20, Col3: "ps0f1jg0.121", Col4: 11/1/2015 }
{ Col1:3, Col2: 30, Col3: "skoc2gx1.2xs", Col4: 11/2/2015 }
{ Col1:4, Col2: 40, Col3: "urs3jnbb.ob1", Col4: 11/3/2015 }
{ Col1:5, Col2: 50, Col3: "m4l2fese.4yz", Col4: 11/4/2015 }
{ Col1:6, Col2: 60, Col3: "v3dselpn.rqq", Col4: 11/5/2015 }
{ Col1:7, Col2: 70, Col3: "v2ggbaar.r31", Col4: 11/6/2015 }
{ Col1:8, Col2: 80, Col3: "da4vd35p.msl", Col4: 11/7/2015 }
{ Col1:9, Col2: 90, Col3: "v5dtpuad.2ao", Col4: 11/8/2015 }

Génial @Ernie, merci pour le partage. En effet, tout à fait certaines hypothèses, mais donne une grande perspicacité dans la façon de résoudre ce problème.

OriginalL'auteur Ernie

Vous devez vous connecter pour publier un commentaire.