Table of Contents

Class ExcelTable

Namespace
OfficeOpenXml.Table
Assembly
EPPlus.dll

An Excel Table

public class ExcelTable : ExcelTableDxfBase, IEqualityComparer<ExcelTable>
Inheritance
ExcelTable
Implements
Inherited Members

Properties

Address

The address of the table

public ExcelAddressBase Address { get; }

Property Value

ExcelAddressBase

AutoFilter

Autofilter settings for the table

public ExcelAutoFilter AutoFilter { get; }

Property Value

ExcelAutoFilter

Columns

Collection of the columns in the table

public ExcelTableColumnCollection Columns { get; }

Property Value

ExcelTableColumnCollection

DataCellStyleName

Named style used for the data cells

public string DataCellStyleName { get; set; }

Property Value

string

HeaderRowBorderStyle

Sets differential formatting styles for the table header row border style.

public ExcelDxfBorderBase HeaderRowBorderStyle { get; set; }

Property Value

ExcelDxfBorderBase

HeaderRowCellStyle

Named style used for the header row

public string HeaderRowCellStyle { get; set; }

Property Value

string

Name

The name of the table object in Excel

public string Name { get; set; }

Property Value

string

Range

The table range

public ExcelRangeBase Range { get; }

Property Value

ExcelRangeBase

ShowColumnStripes

Display banded columns

public bool ShowColumnStripes { get; set; }

Property Value

bool

ShowFilter

If the header row has an autofilter

public bool ShowFilter { get; set; }

Property Value

bool

ShowFirstColumn

Display special formatting for the first row

public bool ShowFirstColumn { get; set; }

Property Value

bool

ShowHeader

If the header row is visible or not

public bool ShowHeader { get; set; }

Property Value

bool

ShowLastColumn

Display special formatting for the last row

public bool ShowLastColumn { get; set; }

Property Value

bool

ShowRowStripes

Display banded rows

public bool ShowRowStripes { get; set; }

Property Value

bool

ShowTotal

If the total row is visible or not

public bool ShowTotal { get; set; }

Property Value

bool

SortState

Gets the sort state of the table. Sort(Action<TableSortOptions>) Sort(TableSortOptions)

public SortState SortState { get; }

Property Value

SortState

StyleName

The style name for custum styles

public string StyleName { get; set; }

Property Value

string

TableBorderStyle

Sets differential formatting styles for the tables row border style.

public ExcelDxfBorderBase TableBorderStyle { get; set; }

Property Value

ExcelDxfBorderBase

TableStyle

The table style. If this property is custom, the style from the StyleName propery is used.

public TableStyles TableStyle { get; set; }

Property Value

TableStyles

TableUri

The package internal URI to the Table Xml Document.

public Uri TableUri { get; }

Property Value

Uri

TableXml

Provides access to the XML data representing the table in the package.

public XmlDocument TableXml { get; set; }

Property Value

XmlDocument

TotalsRowCellStyle

Named style used for the total row

public string TotalsRowCellStyle { get; set; }

Property Value

string

WorkSheet

The worksheet of the table

public ExcelWorksheet WorkSheet { get; set; }

Property Value

ExcelWorksheet

Methods

AddRow(int)

Adds new rows to the table.

public ExcelRangeBase AddRow(int rows = 1)

Parameters

rows int

Number of rows to add to the table. Default is 1

Returns

ExcelRangeBase

CreateHtmlExporter()

Creates an IExcelHtmlTableExporter object to export the table to HTML

public IExcelHtmlTableExporter CreateHtmlExporter()

Returns

IExcelHtmlTableExporter

The exporter object

DeleteRow(int, int)

Deletes one or more rows at the specified position in the table.

public ExcelRangeBase DeleteRow(int position, int rows = 1)

Parameters

position int

The position in the table where the row will be deleted. 0 will delete the first row.

rows int

Number of rows to delete.

Returns

ExcelRangeBase

Equals(ExcelTable, ExcelTable)

Checkes if two tables are the same

public bool Equals(ExcelTable x, ExcelTable y)

Parameters

x ExcelTable

Table 1

y ExcelTable

Table 2

Returns

bool

GetHashCode(ExcelTable)

Returns a hashcode generated from the TableXml

public int GetHashCode(ExcelTable obj)

Parameters

obj ExcelTable

The table

Returns

int

The hashcode

InsertRow(int, int, bool)

Inserts one or more rows before the specified position in the table.

public ExcelRangeBase InsertRow(int position, int rows = 1, bool copyStyles = true)

Parameters

position int

The position in the table where the row will be inserted. Default is in the end of the table. 0 will insert the row at the top. Any value larger than the number of rows in the table will insert a row at the bottom of the table.

rows int

Number of rows to insert.

copyStyles bool

Copy styles from the row above. If inserting a row at position 0, the first row will be used as a template.

Returns

ExcelRangeBase

The inserted range

SaveToJson(Stream)

Saves the table as a JSON string to a string

public void SaveToJson(Stream stream)

Parameters

stream Stream

The stream to write the JSON to.

SaveToJson(Stream, Action<JsonTableExportSettings>)

Saves the table as a JSON string to a string

public void SaveToJson(Stream stream, Action<JsonTableExportSettings> settings)

Parameters

stream Stream

The stream to write the JSON to.

settings Action<JsonTableExportSettings>

Settings to configure the JSON output

SaveToJsonAsync(Stream)

Save the table to json

public Task SaveToJsonAsync(Stream stream)

Parameters

stream Stream

The stream to save to.

Returns

Task

SaveToJsonAsync(Stream, Action<JsonTableExportSettings>)

Save the table to json

public Task SaveToJsonAsync(Stream stream, Action<JsonTableExportSettings> settings)

Parameters

stream Stream

The stream to save to.

settings Action<JsonTableExportSettings>

Settings for the json output.

Returns

Task

SaveToText(FileInfo, ExcelOutputTextFormat)

Exports the table to a file

public void SaveToText(FileInfo file, ExcelOutputTextFormat format)

Parameters

file FileInfo

The export file

format ExcelOutputTextFormat

Export options

See Also

SaveToText(Stream, ExcelOutputTextFormat)

Exports the table to a Stream

public void SaveToText(Stream stream, ExcelOutputTextFormat format)

Parameters

stream Stream

Data will be exported to this stream

format ExcelOutputTextFormat

Export options

See Also

SaveToTextAsync(FileInfo, ExcelOutputTextFormat)

Exports the table to a file

public Task SaveToTextAsync(FileInfo file, ExcelOutputTextFormat format)

Parameters

file FileInfo

Data will be exported to this stream

format ExcelOutputTextFormat

Export options

Returns

Task
See Also

SaveToTextAsync(Stream, ExcelOutputTextFormat)

Exports the table to a Stream

public Task SaveToTextAsync(Stream stream, ExcelOutputTextFormat format)

Parameters

stream Stream

Data will be exported to this stream

format ExcelOutputTextFormat

Export options

Returns

Task
See Also

Sort(TableSortOptions)

Sorts the data in the table according to the supplied RangeSortOptions

public void Sort(TableSortOptions options)

Parameters

options TableSortOptions

Examples

var options = new SortOptions();
options.SortBy.Column(0).ThenSortBy.Column(1, eSortDirection.Descending);

Sort(Action<TableSortOptions>)

Sorts the data in the table according to the supplied action of RangeSortOptions

public void Sort(Action<TableSortOptions> configuration)

Parameters

configuration Action<TableSortOptions>

An action with parameters for sorting

Examples

table.Sort(x => x.SortBy.Column(0).ThenSortBy.Column(1, eSortDirection.Descending);

SyncColumnNames(ApplyDataFrom, bool)

Update column names with cell values or cell values with column names

ColumnNamesToCells overwrites the top row cell values with the column names.

CellsToColumnNames overwrites the column names with the top row cell values. If the cell is empty it instead overwrites the cell value with the column name unless syncEmptyCells is set to false.

public void SyncColumnNames(ApplyDataFrom dataOrigin, bool syncEmptyCells = true)

Parameters

dataOrigin ApplyDataFrom

Target data to be overwritten

syncEmptyCells bool

Set to false to not fill empty cell with column name

ToCollectionWithMappings<T>(Func<ToCollectionRow, T>, ToCollectionTableOptions)

Returns a collection of T for the table. If the range contains multiple addresses the first range is used. The the table must have headers. Headers will be mapped to properties using the name or the attributes without white spaces. The attributes that can be used are: EpplusTableColumnAttributeBase.Header, DescriptionAttribute.Description or DisplayNameAttribute.Name.

public List<T> ToCollectionWithMappings<T>(Func<ToCollectionRow, T> setRow, ToCollectionTableOptions options)

Parameters

setRow Func<ToCollectionRow, T>

The call back function to map each row to the item of type T.

options ToCollectionTableOptions

Configures the settings for the function

Returns

List<T>

A list of T

Type Parameters

T

The type to map to

ToCollectionWithMappings<T>(Func<ToCollectionRow, T>, Action<ToCollectionTableOptions>)

Returns a collection of T for the table. If the range contains multiple addresses the first range is used. The the table must have headers. Headers will be mapped to properties using the name or the attributes without white spaces. The attributes that can be used are: EpplusTableColumnAttributeBase.Header, DescriptionAttribute.Description or DisplayNameAttribute.Name.

public List<T> ToCollectionWithMappings<T>(Func<ToCollectionRow, T> setRow, Action<ToCollectionTableOptions> options)

Parameters

setRow Func<ToCollectionRow, T>

The call back function to map each row to the item of type T.

options Action<ToCollectionTableOptions>

Configures the settings for the function

Returns

List<T>

A list of T

Type Parameters

T

The type to map to

ToCollection<T>()

Returns a collection of T for the tables data range. The total row is not included. The table must have headers. Headers will be mapped to properties using the name or the objects attributes without white spaces. The attributes that can be used are: EpplusTableColumnAttributeBase.Header, DescriptionAttribute.Description or DisplayNameAttribute.Name.

public List<T> ToCollection<T>()

Returns

List<T>

A list of T

Type Parameters

T

The type to map to

ToCollection<T>(ToCollectionTableOptions)

Returns a collection of T for the tables data range. The total row is not included. The table must have headers. Headers will be mapped to properties using the name or the property attributes without white spaces. The attributes that can be used are: EpplusTableColumnAttributeBase.Header, DescriptionAttribute.Description or DisplayNameAttribute.Name.

public List<T> ToCollection<T>(ToCollectionTableOptions options)

Parameters

options ToCollectionTableOptions

Settings for the method

Returns

List<T>

A list of T

Type Parameters

T

The type to map to

ToCollection<T>(Action<ToCollectionTableOptions>)

Returns a collection of T for the tables data range. The total row is not included. The table must have headers. Headers will be mapped to properties using the name or the property attributes without white spaces. The attributes that can be used are: EpplusTableColumnAttributeBase.Header, DescriptionAttribute.Description or DisplayNameAttribute.Name.

public List<T> ToCollection<T>(Action<ToCollectionTableOptions> options)

Parameters

options Action<ToCollectionTableOptions>

Configures the settings for the function

Returns

List<T>

A list of T

Type Parameters

T

The type to map to

ToCollection<T>(Func<ToCollectionRow, T>)

Returns a collection of T for the table. If the range contains multiple addresses the first range is used. The the table must have headers. Headers will be mapped to properties using the name or the attributes without white spaces. The attributes that can be used are: EpplusTableColumnAttributeBase.Header, DescriptionAttribute.Description or DisplayNameAttribute.Name.

public List<T> ToCollection<T>(Func<ToCollectionRow, T> setRow)

Parameters

setRow Func<ToCollectionRow, T>

The call back function to map each row to the item of type T.

Returns

List<T>

A list of T

Type Parameters

T

The type to map to

ToDataTable()

Exports the table to a DataTable

public DataTable ToDataTable()

Returns

DataTable

A DataTable containing the data in the table range

See Also

ToDataTable(ToDataTableOptions)

Exports the table to a DataTable

public DataTable ToDataTable(ToDataTableOptions options)

Parameters

options ToDataTableOptions

Returns

DataTable

A DataTable containing the data in the table range

See Also

ToDataTable(Action<ToDataTableOptions>)

Exports the table to a DataTable

public DataTable ToDataTable(Action<ToDataTableOptions> configHandler)

Parameters

configHandler Action<ToDataTableOptions>

Returns

DataTable

A DataTable containing the data in the table range

See Also

ToJson()

Returns the table as a JSON string

public string ToJson()

Returns

string

A string containing the JSON document.

ToJson(Action<JsonTableExportSettings>)

Returns the table as a JSON string

public string ToJson(Action<JsonTableExportSettings> settings)

Parameters

settings Action<JsonTableExportSettings>

Settings to configure the JSON output

Returns

string

A string containing the JSON document.

ToText()

Converts the table range to CSV format

public string ToText()

Returns

string
See Also

ToText(ExcelOutputTextFormat)

Converts the table range to CSV format

public string ToText(ExcelOutputTextFormat format)

Parameters

format ExcelOutputTextFormat

Parameters/options for conversion to text

Returns

string
See Also

ToTextAsync()

Converts the table range to CSV format

public Task<string> ToTextAsync()

Returns

Task<string>
See Also

ToTextAsync(ExcelOutputTextFormat)

Converts the table range to CSV format

public Task<string> ToTextAsync(ExcelOutputTextFormat format)

Parameters

format ExcelOutputTextFormat

Returns

Task<string>
See Also