LoadFromCollection using Attributes

From EPPlus 5.5 you can decorate your classes with a new set of attributes from the namespace OfficeOpenXml.Attributes instead of using function parameters. These attributes provides a richer functionality than the previous versions of the functions - you can for example access more of the tables properties, control in which order the columns are mapped to the properties of the class and add calculated columns.

Here is an example that shows most of the functionality:

[EpplusTable(TableStyle = TableStyles.Dark1, PrintHeaders = true, AutofitColumns = true, AutoCalculate = true, ShowTotal = true, ShowFirstColumn = true)]
[
    EpplusFormulaTableColumn(Order = 6, NumberFormat = "€#,##0.00", Header = "Tax amount", FormulaR1C1 = "RC[-2] * RC[-1]", TotalsRowFunction = RowFunctions.Sum, TotalsRowNumberFormat = "€#,##0.00"),
    EpplusFormulaTableColumn(Order = 7, NumberFormat = "€#,##0.00", Header = "Net salary", Formula = "E2-G2", TotalsRowFunction = RowFunctions.Sum, TotalsRowNumberFormat = "€#,##0.00")
]
public class Actor
{
    [EpplusIgnore]
    public int Id { get; set; }

    [EpplusTableColumn(Order = 3)]
    public string LastName { get; set; }
    [EpplusTableColumn(Order = 1, Header = "First name")]
    public string FirstName { get; set; }
    [EpplusTableColumn(Order = 2)]
    public string MiddleName { get; set; }

    [EpplusTableColumn(Order = 0, NumberFormat = "yyyy-MM-dd", TotalsRowLabel = "Total")]
    public DateTime Birthdate { get; set; }

    [EpplusTableColumn(Order = 4, NumberFormat = "€#,##0.00", TotalsRowFunction = RowFunctions.Sum, TotalsRowNumberFormat = "€#,##0.00")]
    public double Salary { get; set; }

    [EpplusTableColumn(Order = 5, NumberFormat = "0%", TotalsRowFormula = "Table1[[#Totals],[Tax amount]]/Table1[[#Totals],[Salary]]", TotalsRowNumberFormat ="0 %")]
    public double Tax { get; set; }
}

Lets create a list with instances of this class:

var actors = new List<Actor>
{
    new Actor{ Salary = 256.24, Tax = 0.21, FirstName = "John", MiddleName = "Bernhard", LastName = "Doe", Birthdate = new DateTime(1950, 3, 15) },
    new Actor{ Salary = 278.55, Tax = 0.23, FirstName = "Sven", MiddleName = "Bertil", LastName = "Svensson", Birthdate = new DateTime(1962, 6, 10)},
    new Actor{ Salary = 315.34, Tax = 0.28, FirstName = "Lisa", MiddleName = "Maria", LastName = "Gonzales", Birthdate = new DateTime(1971, 10, 2)}
};

Now, the only thing you need to do is to call LoadFromCollection with no other arguments than this list:

using (var package = new ExcelPackage())
{
    var sheet = package.Workbook.Worksheets.Add("test");
    var tableRange = sheet.Cells["A1"].LoadFromCollection(actors);
}

And you will get this result:

Attributes

EPPlusTableAttribute

This attribute should be set on class/interface level and has properties to control the layout of the table. It also provides properties that indicates if EPPlus should autofit column width and/or calculate formulas in the table range after the data has been imported. Please note that this attribute only will be used by LoadFromCollection's method signature with one argument (the Enumerable of class instances). If you supply more arguments to the function, such as PrintHeaders and TableStyle, the EPPlusTable attribute will be ignored.

EpplusFormulaTableColumn

This attribute should be set on class/interface level and allows you to add additional columns that are based on a formula instead of data from your class instances. As shown in the example above the R1C1 format is useful here. The formula will be set as a shared formula for the entire column, so if you want to use the A1 format you need to set the formula with the first row of the table (EPPlus/Excel will then apply the formula for each row). The Order property controls which of the table's column (sort order starting from the left) should be used.

EpplusIgnore

This attribute should be set on property/member level. Any member decorated with this attribute will be ignored by the LoadFromCollection method and not included in the table.

EpplusTableColumn

This attribute should be set on property/member level and allows you to set Order, header, NumberFormat, TotalsRowFormula, etc.