LoadFromCollection using Attributes - DON-PHAM/EPPlus GitHub Wiki
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.
This attributes are only used by the method signature LoadFromCollection(items), i.e. with no other argument than the collection itself. The reason for this is that all the subsequent parameters (like PrintHeaders, TableStyle, etc) can be set via the attributes instead (see below).
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);
// if you want to access the created table:
var table = sheet.Tables.GetFromRange(tableRange);
}And you will get this result:
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.
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.
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.
This attribute should be set on property/member level and allows you to set Order, header, NumberFormat, TotalsRowFormula, etc.
This attribute should be set on property/member level and allows you include a property of a complex type (see sample below). This attribute was added in EPPlus 5.8.1.
This functionality was added in EPPlus 5.8.1. If you want a property that is of a complex type to be included you should decorate that property with the EpplusNestedTableColumn attribute. This attribute has an Order property and this will define the order compared with the other properties in the class. In the sample below the Actor3 class has a property Name of type ActorName. Since this property´s attribute has Order = 1 it will be placed between Birthdate and Salary. The column order of the properties of the Actor Name class will be defined by its EpplusTableColumn attributes.
You can nest complex types, i.e. the ActorName class can in its turn also have one or more complex type property/properties.
[EpplusTable(TableStyle = TableStyles.Light14, PrintHeaders = true, AutofitColumns = true, AutoCalculate = true, ShowLastColumn = true)]
internal class Actor3
{
[EpplusIgnore]
public int Id { get; set; }
[EpplusNestedTableColumn(Order = 1)]
public ActorName Name { get; set; }
[EpplusTableColumn(Order = 0, NumberFormat = "yyyy-MM-dd", TotalsRowLabel = "Total")]
public DateTime Birthdate { get; set; }
[EpplusTableColumn(Order = 2, NumberFormat = "€#,##0.00", TotalsRowFunction = RowFunctions.Sum, TotalsRowNumberFormat = "€#,##0.00")]
public double Salary { get; set; }
[EpplusTableColumn(Order = 3, NumberFormat = "0%", TotalsRowFormula = "Table1[[#Totals],[Tax amount]]/Table1[[#Totals],[Salary]]", TotalsRowNumberFormat = "0 %")]
public double Tax { get; set; }
}
internal class ActorName
{
[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; }
}Usage of the classes above:
var complexTypeActors = new List<Actor3>
{
new Actor3{ Salary = 256.24, Tax = 0.21, Name = new ActorName{ FirstName="John", MiddleName="Bernhard", LastName="Doe" }, Birthdate = new DateTime(1950, 3, 15) },
new Actor3{ Salary = 278.55, Tax = 0.23, Name = new ActorName{ FirstName="Sven", MiddleName="Bertil", LastName="Svensson" }, Birthdate = new DateTime(1962, 6, 10)},
new Actor3{ Salary = 315.34, Tax = 0.28, Name = new ActorName{ FirstName="Lisa", MiddleName="Maria", LastName="Gonzales" }, Birthdate = new DateTime(1971, 10, 2)}
};
var complexTypePropertySheet = package.Workbook.Worksheets.Add("Complex type property");
complexTypePropertySheet.Cells["A1"].LoadFromCollection(complexTypeActors);Result:
If you don't want to decorate your classes with all these attributes you can create a subclass and use that as a model for the table:
[EpplusTable(TableStyle = TableStyles.Medium1, PrintHeaders = true, AutofitColumns = true, AutoCalculate = true, ShowLastColumn = true)]
internal class Actor2 : Actor
{
}