Interface IXlsxSourceCommand

Defines mappings between the XLSX spreadsheet file source columns and downstream row columns. Note that multiple commands can be chained together, e.g.: xsc => xsc.Name("A", "RowNumber").Name("B", "Category"). Supports reading from a rectangular area on one sheet in the XLSX file. The default address is the top left cell (i.e. "A1") on the first sheet.

If HeaderRow is false or the column header is missing, use ordinal position or source column addresses ("B", "AD" etc.) to specify which columns to import.

Mapping commands that can map multiple columns ignore already mapped columns. Use Name(String, String) to map the same source column multiple times.

Note that some of the mapping commands explicitly control in which order to read and populate columns, while others use OrderAttribute added to the row members. You can mix the two approaches.

By default, any column errors are rejected to the ErrorOutputPort<TError>.

Also see XLSX Spreadsheet.

Inherited Members

IFluentInterface.GetType()

IFluentInterface.GetHashCode()

IFluentInterface.ToString()

IFluentInterface.Equals(Object)

Namespace: actionETL.EPPlus

Assembly: actionETL.dll

Syntax

public interface IXlsxSourceCommand : IFluentInterface

Methods

Address(String)

Sets the top left cell address to start reading from, e.g. "B5". This can optionally include a sheet name, e.g. "'My Sheet'!B5". The default address is the top left cell (i.e. "A1") on the first sheet. This method can only be called once, and must be the first command.

Declaration

IXlsxSourceCommand Address(string fromAddress)

Parameters

Type	Name	Description
String	fromAddress	Cell address, e.g. "B5" or "'My Sheet'!B5". Set to an empty string to get the default address: the top left cell (i.e. "A1") on the first sheet. The string is current culture case insensitive. Note that this is different from other interface members (due to processing in the underlying EPPlus library).

Returns

Type	Description
IXlsxSourceCommand	Returns IXlsxSourceCommand so that multiple commands can be chained together.

Exceptions

Type	Condition
ArgumentException	fromAddress - Must specify a cell address, e.g. 'B5'.
InvalidOperationException	Cannot call Address(String) twice. No worksheet found.

FailOnError()

Fails the worker on any column errors (InvalidCastException etc.) in the column(s) mapped by the previous column mapping command (i.e. Name(String, String), FromAllByName(String[]) etc.)

Declaration

IXlsxSourceCommand FailOnError()

Returns

Type	Description
IXlsxSourceCommand	Returns IXlsxSourceCommand so that multiple commands can be chained together.

FromAllByName(String[])

Map from all XLSX source columns (within the range specified by Address(String)) except for any specified exclusions, to (writable, see CanWrite) row output columns, which must have matching names. Any source or destination columns that are already mapped are also excluded. Also see ToAllByName(String[]) and ToAllByIndex(String[]).

Column name matching between spreadsheet column names and dataflow type names is ordinal case insensitive, but a case sensitive match takes precedence over a case insensitive match.

If HeaderRow is false, or the column is missing, the output row type must have member names matching the source column address ("B", "AD" etc.)

Declaration

IXlsxSourceCommand FromAllByName(params string[] exceptFromColumnNames)

Parameters

Type	Name	Description
String[]	exceptFromColumnNames	XLSX source column names to exclude. Use a column address ("B", "AD" etc.) if there is no header row or the column is missing. Column name matching is ordinal case insensitive.

Returns

Type	Description
IXlsxSourceCommand	Returns IXlsxSourceCommand so that multiple commands can be chained together.

Exceptions

Type	Condition
ArgumentException	Member in row type is not a supported type. Type is not supported by XlsxSource<TOutput> and XlsxTarget<TInput>.
InvalidOperationException	No worksheet found. FromAllByName(String[]) requires that the XlsxSource{TOutput}.XlsxSourceCommands range is larger than one cell.
NotSupportedException	This property or method is not supported for a Chart sheet.

IgnoreErrors()

Ignores any column errors (InvalidCastException etc.) in the column(s) mapped by the previous column command (i.e. Name(String, String), FromAllByName(String[]) etc.)

Declaration

IXlsxSourceCommand IgnoreErrors()

Returns

Type	Description
IXlsxSourceCommand	Returns IXlsxSourceCommand so that multiple commands can be chained together.

Name(String, String)

Map from one XLSX source column name to an output row column name.

Declaration

IXlsxSourceCommand Name(string fromColumnName, string toColumnName)

Parameters

Type	Name	Description
String	fromColumnName	Name of the XLSX column. Use a column address ("B", "AD" etc.) if there is no HeaderRow. Column name matching is ordinal case insensitive, but a case sensitive match takes precedence over a case insensitive match.
String	toColumnName	Name of a column in the output rows. Column name matching is ordinal case insensitive, but a case sensitive match takes precedence over a case insensitive match.

Returns

Type	Description
IXlsxSourceCommand	Returns IXlsxSourceCommand so that multiple commands can be chained together.

Exceptions

Type	Condition
ArgumentException	Destination column already mapped. Member toColumnName was found, but is not a column. Member in row type is not a supported type. Type is not supported by XlsxSource<TOutput> and XlsxTarget<TInput>.
InvalidOperationException	No worksheet found.
NotSupportedException	This property or method is not supported for a Chart sheet.

ToAllByIndex(String[])

Map source columns sequentially to all (writable, see CanWrite) row output columns, except for any specified exclusions. Any source or destination columns that are already mapped are also excluded.

The imported columns in the XLSX source file must be in the same order as the row output column order in the row class, as defined by any OrderAttribute added to the row columns members. Also see ToAllByName(String[]) and FromAllByName(String[]).

Declaration

IXlsxSourceCommand ToAllByIndex(params string[] exceptToSchemaNodeNames)

Parameters

Type	Name	Description
String[]	exceptToSchemaNodeNames	Row output columns or column schema names to exclude. Column name matching is ordinal case insensitive, but a case sensitive match takes precedence over a case insensitive match.

Returns

Type	Description
IXlsxSourceCommand	Returns IXlsxSourceCommand so that multiple commands can be chained together.

Exceptions

Type	Condition
ArgumentException	Member in row type is not a supported type. exceptToSchemaNodeNames not found. Type is not supported by XlsxSource<TOutput> and XlsxTarget<TInput>.
ArgumentNullException	exceptToSchemaNodeNames
InvalidOperationException	exceptToSchemaNodeNames - Found more than one member match in type. No worksheet found.
NotSupportedException	This property or method is not supported for a Chart sheet.

ToAllByName(String[])

Map to all (writable, see CanWrite) row output (writable) columns, except for any specified exclusions. Any source or destination columns that are already mapped are also excluded. The XLSX source columns must have the same name as the row output columns. Also see FromAllByName(String[]) and ToAllByIndex(String[]).

Column name matching is ordinal case insensitive, but a case sensitive match takes precedence over a case insensitive match.

Declaration

IXlsxSourceCommand ToAllByName(params string[] exceptToSchemaNodeNames)

Parameters

Type	Name	Description
String[]	exceptToSchemaNodeNames	Row output columns or column schema names to exclude.

Returns

Type	Description
IXlsxSourceCommand	Returns IXlsxSourceCommand so that multiple commands can be chained together.

Exceptions

Type	Condition
ArgumentException	Member in row type is not a supported type. exceptToSchemaNodeNames not found. Type is not supported by XlsxSource<TOutput> and XlsxTarget<TInput>.
ArgumentNullException	exceptToSchemaNodeNames
InvalidOperationException	exceptToSchemaNodeNames - Found more than one member match in type. No worksheet found.
NotSupportedException	This property or method is not supported for a Chart sheet.

ToNames(String[])

Specifies one or more row output columns that should be populated from the XLSX source. If a specified name is a column schema group of columns, all its columns will be included. Any source or destination columns that are already mapped are excluded. The XLSX source columns must have the same name as the row output columns.

Declaration

IXlsxSourceCommand ToNames(params string[] toSchemaNodeNames)

Parameters

Type	Name	Description
String[]	toSchemaNodeNames	Names of outgoing row columns and column schemas to include. Column name matching is ordinal case insensitive, but a case sensitive match takes precedence over a case insensitive match. If HeaderRow is false, the output row type must have member names matching the source column address ("B", "AD" etc.)

Returns

Type	Description
IXlsxSourceCommand	Returns IXlsxSourceCommand so that multiple commands can be chained together.

Exceptions

Type	Condition
ArgumentException	Member in row type is not a supported type. toSchemaNodeNames not found. Type is not supported by XlsxSource<TOutput> and XlsxTarget<TInput>.
ArgumentNullException	toSchemaNodeNames
InvalidOperationException	toSchemaNodeNames - Found more than one member match in type. No worksheet found.
NotSupportedException	This property or method is not supported for a Chart sheet.

ToNamesByIndex(String[])

Map to specified (writable, see CanWrite) row output columns. Any source or destination columns that are already mapped are excluded.

The imported columns in the XLSX source file must be in the same order as the row output column order in the row class, as defined by any OrderAttribute added to the row columns members.

Note that if a specified name is a column schema group of columns, all its columns will be included.

Declaration

IXlsxSourceCommand ToNamesByIndex(params string[] toSchemaNodeNames)

Parameters

Type	Name	Description
String[]	toSchemaNodeNames	Names of outgoing row columns and column schemas to include. Column name matching is ordinal case insensitive, but a case sensitive match takes precedence over a case insensitive match.

Returns

Type	Description
IXlsxSourceCommand	Returns IXlsxSourceCommand so that multiple commands can be chained together.

Exceptions

Type	Condition
ArgumentException	Member in row type is not a supported type. toSchemaNodeNames not found. Type is not supported by XlsxSource<TOutput> and XlsxTarget<TInput>.
ArgumentNullException	toSchemaNodeNames
InvalidOperationException	toSchemaNodeNames - Found more than one member match in type. No worksheet found.
NotSupportedException	This property or method is not supported for a Chart sheet.

See Also

XlsxSource<TOutput>

OrderAttribute

XlsxTargetFactory