3

I'm using the method LoadFromCollection to fill an excel with a List. However, the excel columns need to have a certain order to provide a better context for the user, so I'm trying to find out how to accomplish this.

One option I've is to set the order of the parameters inside the class, but I'm avoiding this because someone else can change their order and that will affect my code.

The second option I've is to use Linq:

var orderedColumns = (from p in myList 
                     select new { p.Name, p.Age, ...}).ToList();

This way I can also define the column order, but I don't like the idea of having to create an anonymous when I already have the list ready to be used (I also lose the DisplayNameAttribute that I defined for the class).

Do you guys have any ideas? Perhaps I could use MemberInfo[]?

Lukas
  • 1,699
  • 1
  • 16
  • 49
Pedro Faustino
  • 227
  • 3
  • 14
  • I would suggest to create an array (or csv field) which hardly define field order. With whis way, order will always be the same. Like FieldOrder = "Name,Age,..." You can put the csv in app.config to ma future maintenance easier. That's little bit more code, but you'll be 100% of result. – DanB Sep 28 '18 at 15:19
  • @DanielBlais sorry, I'm afraid I couldn't understand your idea. Could you explain it in more detail please? – Pedro Faustino Sep 28 '18 at 15:36
  • What I've achieved is having an array of MemberInfo with the properties in the correct order. Is this what you are suggesting @DanielBlais? – Pedro Faustino Sep 28 '18 at 15:44
  • I didn't really pay attention to LoadFromCollection, sorry. Here how I would do it in Linq : Add `string fieldOrder = "Name,Age,...".Split(',');` This will define the order. `int row = 0; foreach (var p in myList) { foreach (var column in fieldOrder) { int col = 0; worksheet.Cells[row, col++].Value = p.GetType().GetProperty(column).GetValue(p); } row++; }` – DanB Sep 28 '18 at 15:57
  • Have you tried using DataMember Order Annotations? Basically, you can define order of members in you class using [DataMember(Order = 0)] – Sanjay Oct 01 '18 at 03:39
  • @Sanjay I've added that attribute to my class, but the Epplus doesn't some to take it into account. However, I'm using that attribute together with a Linq query that will get my class properties ordered by the Order property. – Pedro Faustino Oct 01 '18 at 08:54

1 Answers1

7

I've found a solution that I'll share with you guys.

public class MyClass
{
   [DataMember(Order = 1)]
   public string PropertyA;

   [DataMember(Order = 2)]
   public int PropertyB

   [DataMember(Order = 0)]
   public bool propertyC
}

With the code like this, if I have a List<MyClass> and use the LoadFromCollection() from Epplus the resulting excel will show the columns in the order to which they appear in the class:

PropertyA | PropertyB | PropertyC

To solve this problem, we can do the following:

var orderedProperties = (from property in typeof(MyClass).GetProperties()
                         where Attribute.IsDefined(property, typeof(DataMemberAttribute))
                         orderby ((DataMemberAttribute)property
                                  .GetCustomAttributes(typeof(DataMemberAttribute), false)
                                  .Single()).Order
                         select property);

var ws = p.Workbook.Worksheets.Add("MySheet");
ws.Cells[1, 1].LoadFromCollection(myClassCollection, true, OfficeOpenXml.Table.TableStyles.Light1
   ,System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public
   ,orderedProperties.ToArray());

The resulting excel will show this columns order:

PropertyC | PropertyA | PropertyB

Note: only the columns with the [DataMember(Order = x)] attribute will show up on the excel, but I also wanted to achieve this because there are some columns of my class that I don't want to show in the excel.

Credits to @Sanjay for mentioning the DataMember attribute.

Pedro Faustino
  • 227
  • 3
  • 14