-
Notifications
You must be signed in to change notification settings - Fork 275
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
{
}
EPPlus Software AB - https://epplussoftware.com
- Getting Started
- The Sample Project
- EPPlus and Docker
- EPPlus and Blazor
- EPPlus and Linux
- Security
- Configuration
- Releases/versioning
- What is new in EPPlus 5+
- Breaking Changes in EPPlus 5
- Breaking Changes in EPPlus 6
- Breaking Changes in EPPlus 7
- Addressing a worksheet
- Dimension/Used range
- Copying ranges/sheets
- Insert/Delete
- Filling ranges
- Sorting ranges
- Taking and skipping columns/rows
- Data validation
- Comments
- Freeze and Split Panes
- Header and Footer
- Autofit columns
- Grouping and Ungrouping Rows and Columns
- Formatting and styling
- Conditional formatting
- Using Themes
- Working with custom named table- or slicer- styles