How to sort columns when serializing the properties of a class when used with Epplus

Question

I am using the Epplus library to export the data in a collection to an excel file. I want the output to be arranged in an order of attributes specified in the class. I did find out there is DataMember but it is only used in WCF. And I am developing an application using WPF

Answer 1

Are you asking to sort the Row based on attributes in the colunns? Or do you actually want to sort the columns as they are outputed. In either case, there is no attribute that you can use that Epplus natively supports AFAIK.

If you want to sort the rows by columns, check out the Sort function they add a few years back:

https://github.com/JanKallman/EPPlus/blob/4dacf27661b24d92e8ba3d03d51dd5468845e6c1/EPPlus/ExcelRangeBase.cs#L2934

But, from what you describe, you want to actually sort the order of the columns when exporting a collection of things with something like LoadFromCollection. You can easily create your own attribute and then use using the overload that takes a collection of MemberInfo:

public class ExportOrderAttribute : Attribute
{
    public ExportOrderAttribute(int order)
    {
        Order = order;
    }

    public int Order { get; set; }
}

public class TestObject
{
    /// <summary>
    /// Best to make static to avoid repeat calls to Reflections (if possible).
    /// </summary>
    public static MemberInfo[] MemberSortInfo { get; } = typeof(TestObject)
        .GetProperties()
        .Select(pi => new
        {
            Property = pi,
            Attribute = (ExportOrderAttribute) Attribute.GetCustomAttribute(
                pi
                , typeof(ExportOrderAttribute)
                , true
            )
        })
        .OrderBy(a => a.Attribute?.Order ?? -1)
        .Select(a => a.Property)
        .Cast<MemberInfo>()
        .ToArray();

    [ExportOrder(4)]
    public int IntCol1 { get; set; }

    [ExportOrder(3)]
    public int IntCol2 { get; set; }

    [ExportOrder(2)]
    public string StringCol { get; set; }

    [ExportOrder(1)]
    public DateTime DateCol { get; set; }

    [ExportOrder(0)]
    public int IntCol3 { get; set; }
}

[TestMethod]
public void Sort_Column_Output()
{
    //https://stackoverflow.com/questions/58177582/how-to-sort-columns-when-serializing-the-properties-of-a-class-when-used-with-ep

    var rnd = new Random();

    var testObjects = Enumerable
        .Range(0, 10)
        .Select(i => new TestObject
        {
            IntCol1 = i,
            IntCol2 = i * 10,
            StringCol = Path.GetRandomFileName(),
            DateCol = DateTime.Now.AddDays(rnd.Next(0, 100)),
            IntCol3 = rnd.Next(100, 10000)
        })
        .ToList();

    var fi = new FileInfo("c:\\temp\\Sort_Column_Output.xlsx");
    if (fi.Exists)
        fi.Delete();

    using (var pck = new ExcelPackage(fi))
    {
        var worksheet = pck.Workbook.Worksheets.Add("Sheet1");

        worksheet.Cells.LoadFromCollection(
            testObjects
            , true
            , TableStyles.None
            , BindingFlags.Instance | BindingFlags.Public
            , TestObject.MemberSortInfo  //CONTROLS THE SORTING
        );

        pck.Save();
    }
}

Which gives this: