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.
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:
This functionality was introduced in EPPlus 7.1 and gives you the possibility to set number formats on columns in runtime. This can be usable for example if you want to use different number formats depending on language settings, etc. There is a new public interface in EPPlus: IExcelNumberFormatProvider This interface has just one method: GetFormat(int numberFormatId) which returns a string (the number format).
Logic for providing different formats depending on custom logic in runtime can be implemented via this interface, see example below.
If the new property NumberFormatId is set on the EPPlusTableColumnProperty EPPlus will call the NumberFormatProvider and set the format it returns as number format of the entire column in the range.
public class MyNumberFormatProvider : IExcelNumberFormatProvider
{
public const int CurrencyFormat = 1;
string IExcelNumberFormatProvider.GetFormat(int numberFormatId)
{
switch(numberFormatId)
{
case CurrencyFormat:
return "#,##0.00\\ \"kr\"";
default:
return string.Empty;
}
}
}As long as the implementing class has an empty constructor, formats can be set like this in LoadFromCollection:
[EpplusTable(NumberFormatProviderType = typeof(MyNumberFormatProvider))]
public class NumberFormatWithTableAttribute
{
[EpplusTableColumn(Header = "First name")]
public string Name { get; set; }
[EpplusTableColumn(Header = "Salary", NumberFormatId = MyNumberFormatProvider.CurrencyFormat)]
public decimal Salary { get; set; }
}If the implementing class needs constructor arguments it can be used like this:
_sheet.Cells["A1"].LoadFromCollection(items, o =>
{
o.PrintHeaders = true;
o.SetNumberFormatProvider(new MyNumberFormatProviderWithConstructorArgs(arg1, arg2));
});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
{
}