**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.**

## Important

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).

## On this page

- [Usage](<#usage>)
- [Attributes](<#attributes>)
- [Set number formats on columns](<#set-number-format-on-columns>)

## Usage

Here is an example that shows most of the functionality:

```csharp
[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; }
}
```

```vbnet
<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 Property Id As Integer

    <EpplusTableColumn(Order:=3)>
    Public Property LastName As String
    <EpplusTableColumn(Order:=1, Header:="First name")>
    Public Property FirstName As String
    <EpplusTableColumn(Order:=2)>
    Public Property MiddleName As String

    <EpplusTableColumn(Order:=0, NumberFormat:="yyyy-MM-dd", TotalsRowLabel:="Total")>
    Public Property Birthdate As DateTime

    <EpplusTableColumn(Order:=4, NumberFormat:="€#,##0.00", TotalsRowFunction:=RowFunctions.Sum, TotalsRowNumberFormat:="€#,##0.00")>
    Public Property Salary As Double

    <EpplusTableColumn(Order:=5, NumberFormat:="0%", TotalsRowFormula:="Table1[[#Totals],[Tax amount]]/Table1[[#Totals],[Salary]]", TotalsRowNumberFormat:="0 %")>
    Public Property Tax As Double
End Class
```

Lets create a list with instances of this class:

```csharp
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)}
};
```

```vbnet
Dim actors = New List(Of Actor) From {
    New Actor With {.Salary = 256.24, .Tax = 0.21, .FirstName = "John", .MiddleName = "Bernhard", .LastName = "Doe", .Birthdate = New DateTime(1950, 3, 15)},
    New Actor With {.Salary = 278.55, .Tax = 0.23, .FirstName = "Sven", .MiddleName = "Bertil", .LastName = "Svensson", .Birthdate = New DateTime(1962, 6, 10)},
    New Actor With {.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:

```csharp
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);
}
```

```vbnet
Using package As New ExcelPackage()
    Dim sheet = package.Workbook.Worksheets.Add("test")
    Dim tableRange = sheet.Cells("A1").LoadFromCollection(actors)
    ' if you want to access the created table:
    Dim table = sheet.Tables.GetFromRange(tableRange)
End Using
```

And you will get this result:

## Attributes

### EPPlusTable

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.

### EpplusNestedTableColumn

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.

## Complex type properties

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.

```csharp
[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; }
}
```

```vbnet
<EpplusTable(TableStyle:=TableStyles.Light14, PrintHeaders:=True, AutofitColumns:=True, AutoCalculate:=True, ShowLastColumn:=True)>
Friend Class Actor3
    <EpplusIgnore>
    Public Property Id As Integer

    <EpplusNestedTableColumn(Order:=1)>
    Public Property Name As ActorName

    <EpplusTableColumn(Order:=0, NumberFormat:="yyyy-MM-dd", TotalsRowLabel:="Total")>
    Public Property Birthdate As DateTime

    <EpplusTableColumn(Order:=2, NumberFormat:="€#,##0.00", TotalsRowFunction:=RowFunctions.Sum, TotalsRowNumberFormat:="€#,##0.00")>
    Public Property Salary As Double

    <EpplusTableColumn(Order:=3, NumberFormat:="0%", TotalsRowFormula:="Table1[[#Totals],[Tax amount]]/Table1[[#Totals],[Salary]]", TotalsRowNumberFormat:="0 %")>
    Public Property Tax As Double
End Class

Friend Class ActorName
    <EpplusTableColumn(Order:=3)>
    Public Property LastName As String
    <EpplusTableColumn(Order:=1, Header:="First name")>
    Public Property FirstName As String
    <EpplusTableColumn(Order:=2)>
    Public Property MiddleName As String
End Class
```

Usage of the classes above:

```csharp
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);
```

```vbnet
Dim complexTypeActors = New List(Of Actor3) From {
    New Actor3 With {.Salary = 256.24, .Tax = 0.21, .Name = New ActorName With {.FirstName = "John", .MiddleName = "Bernhard", .LastName = "Doe"}, .Birthdate = New DateTime(1950, 3, 15)},
    New Actor3 With {.Salary = 278.55, .Tax = 0.23, .Name = New ActorName With {.FirstName = "Sven", .MiddleName = "Bertil", .LastName = "Svensson"}, .Birthdate = New DateTime(1962, 6, 10)},
    New Actor3 With {.Salary = 315.34, .Tax = 0.28, .Name = New ActorName With {.FirstName = "Lisa", .MiddleName = "Maria", .LastName = "Gonzales"}, .Birthdate = New DateTime(1971, 10, 2)}
}
Dim complexTypePropertySheet = package.Workbook.Worksheets.Add("Complex type property")
complexTypePropertySheet.Cells("A1").LoadFromCollection(complexTypeActors)
```

Result:

## Set number format on columns

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.

```csharp
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;
        } 
    }
}
```

```vbnet
Public Class MyNumberFormatProvider
    Implements IExcelNumberFormatProvider

    Public Const CurrencyFormat As Integer = 1

    Private Function GetFormat(numberFormatId As Integer) As String Implements IExcelNumberFormatProvider.GetFormat
        Select Case numberFormatId
            Case CurrencyFormat
                Return "#,##0.00\ ""kr"""
            Case Else
                Return String.Empty
        End Select
    End Function
End Class
```

As long as the implementing class has an empty constructor, formats can be set like this in `LoadFromCollection`:

```csharp
[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; }
}
```

```vbnet
<EpplusTable(NumberFormatProviderType:=GetType(MyNumberFormatProvider))>
Public Class NumberFormatWithTableAttribute
    <EpplusTableColumn(Header:="First name")>
    Public Property Name As String

    <EpplusTableColumn(Header:="Salary", NumberFormatId:=MyNumberFormatProvider.CurrencyFormat)>
    Public Property Salary As Decimal
End Class
```

If the implementing class needs constructor arguments it can be used like this:

```csharp
_sheet.Cells["A1"].LoadFromCollection(items, o =>
{
    o.PrintHeaders = true;
    o.SetNumberFormatProvider(new MyNumberFormatProviderWithConstructorArgs(arg1, arg2));
});
```

```vbnet
_sheet.Cells("A1").LoadFromCollection(items, Sub(o)
                                                 o.PrintHeaders = True
                                                 o.SetNumberFormatProvider(New MyNumberFormatProviderWithConstructorArgs(arg1, arg2))
                                             End Sub)
```

## Inheritance

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:

```csharp
[EpplusTable(TableStyle = TableStyles.Medium1, PrintHeaders = true, AutofitColumns = true, AutoCalculate = true, ShowLastColumn = true)]
internal class Actor2 : Actor
{

}
```

```vbnet
<EpplusTable(TableStyle:=TableStyles.Medium1, PrintHeaders:=True, AutofitColumns:=True, AutoCalculate:=True, ShowLastColumn:=True)>
Friend Class Actor2
    Inherits Actor

End Class
```

## See also

- [Sample 2.1 (C\#)](<https://github.com/EPPlusSoftware/EPPlus.Samples.CSharp/tree/master/02-Import and export/01-Load data from collection>) or [Sample 2.1 (VB)](<https://github.com/EPPlusSoftware/EPPlus.Samples.VB/tree/master/02-Import and export/01-Load data from collection>)
- [Import data](<https://epplussoftware.com/docs/import-export-data/import/index.md>)
- [LoadFromCollection](<https://epplussoftware.com/docs/import-export-data/import/loadfromcollection/index.md>)