Class XlsxSource<TOutput>

A dataflow worker with one Output and one ErrorOutput port that reads data records from a rectangular area on one sheet in an XLSX spreadsheet file, and sends them to a downstream worker. No driver installation is required (it uses the EPPlus library internally).

Also see XLSX Spreadsheet.

Inheritance
Object
WorkerParent
WorkerBase
WorkerBase<XlsxSource<TOutput>>
XlsxSource<TOutput>
Implements
Inherited Members
Namespace: actionETL.EPPlus
Assembly: actionETL.dll
Syntax
public class XlsxSource<TOutput> : WorkerBase<XlsxSource<TOutput>>, IDisposeOnFinished where TOutput : class, new()
Type Parameters
Name Description
TOutput

The type of each Output row.

Constructors

XlsxSource(WorkerParent, String, Func<Boolean>, String, Action<IXlsxSourceCommand>)

Initializes a new instance of the XlsxSource<TOutput> dataflow worker that reads data records from an XLSX spreadsheet file, and sends them to a downstream worker.

Declaration
public XlsxSource(WorkerParent workerParent, string workerName, Func<bool> isStartableFunc, string fileName, Action<IXlsxSourceCommand> xlsxSourceCommands)
Parameters
Type Name Description
WorkerParent workerParent

The parent worker or worker system that the new child worker will be added to. Cannot be null.
String workerName

Name of the worker.

Set to a prefix plus a trailing "/" (e.g. "MyPrefix-/") to generate a unique name from the prefix plus an increasing number starting at 1.

While less useful, set to null, whitespace or "/" to generate a unique name from the worker type plus an increasing number starting at 1.

The name cannot otherwise contain "/", and cannot start with double underscore "__".

Func<Boolean> isStartableFunc

Function to calculate the worker start constraint; it should return true for startable and false for not startable. Defaults to startable if null.
String fileName

The filename of the XLSX file.
Action<IXlsxSourceCommand> xlsxSourceCommands

The commands that defines which columns to include, and their failure policy, e.g.:

xsc => xsc
    .Address("'My Sheet'!B5")
    .ToNames("Category", "Subcategory")
    .Name("Id", "ProductId").Ignore()

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

The default address is the top left cell (i.e. "A1") on the first sheet. Can be null, but then XlsxSourceCommands must be set before the worker runs.

Exceptions
Type Condition
ArgumentNullException

xlsxSourceCommands
ArgumentException

workerName:

  • Workers with the same parent must have unique names.
  • Worker and worker system names cannot contain '/' or start with double underscore '__'.
ArgumentNullException

workerParent - All workers must have a parent. The top level workers have the worker system as parent.
InvalidOperationException
  • Cannot add child worker to parent which has completed. Are you adding it to the correct parent?
  • Cannot add worker to parent, since its children have been started. Are you adding it to the correct parent?

XlsxSource(WorkerParent, String, String, Action<IXlsxSourceCommand>)

Initializes a new instance of the XlsxSource<TOutput> dataflow worker that reads data records (by default starting at the top left corner of the first sheet) from an XLSX spreadsheet file, and sends them to a downstream worker.

Declaration
public XlsxSource(WorkerParent workerParent, string workerName, string fileName, Action<IXlsxSourceCommand> xlsxSourceCommands)
Parameters
Type Name Description
WorkerParent workerParent

The parent worker or worker system that the new child worker will be added to. Cannot be null.
String workerName

Name of the worker.

Set to a prefix plus a trailing "/" (e.g. "MyPrefix-/") to generate a unique name from the prefix plus an increasing number starting at 1.

While less useful, set to null, whitespace or "/" to generate a unique name from the worker type plus an increasing number starting at 1.

The name cannot otherwise contain "/", and cannot start with double underscore "__".

String fileName

The filename of the XLSX file.
Action<IXlsxSourceCommand> xlsxSourceCommands

The commands that defines which columns to include, and their failure policy, e.g.:

xsc => xsc
    .Address("'My Sheet'!B5")
    .ToNames("Category", "Subcategory")
    .Name("Id", "ProductId").Ignore()

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

The default address is the top left cell (i.e. "A1") on the first sheet. Can be null, but then XlsxSourceCommands must be set before the worker runs.

Exceptions
Type Condition
ArgumentNullException

xlsxSourceCommands
ArgumentException

workerName:

  • Workers with the same parent must have unique names.
  • Worker and worker system names cannot contain '/' or start with double underscore '__'.
ArgumentNullException

workerParent - All workers must have a parent. The top level workers have the worker system as parent.
InvalidOperationException
  • Cannot add child worker to parent which has completed. Are you adding it to the correct parent?
  • Cannot add worker to parent, since its children have been started. Are you adding it to the correct parent?

Properties

ErrorOutput

Gets the error output port for sending error rows to logging and an optional downstream worker.

Declaration
public ErrorOutputPort<StringRowErrors> ErrorOutput { get; }
Property Value
Type Description
ErrorOutputPort<StringRowErrors>

FileName

The filename to read from. Cannot be set after the worker has started running.

Note: This property is thread-safe.

Declaration
public string FileName { get; set; }
Property Value
Type Description
String
Exceptions
Type Condition
InvalidOperationException

Cannot set the value after the worker has started running.

HeaderRow

Gets or sets a value indicating whether the first line of the source data is a header row. Cannot be set after the worker has started running.

Note that if a header row is not used, the XLSX columns will be specified either by their ordinal position in the addressed range, or by their column address ("B", "AD" etc.)

Note: This property is thread-safe.

Declaration
public bool HeaderRow { get; set; }
Property Value
Type Description
Boolean

Set to true (the default) if the first line of the source data is a header row, otherwise false.
Exceptions
Type Condition
InvalidOperationException

Cannot set the value after the worker has started running.

Output

Gets the output port for sending rows to the downstream worker.

Declaration
public OutputPort<TOutput> Output { get; }
Property Value
Type Description
OutputPort<TOutput>

PasswordFunc

Gets or sets the password function. If set, this function is called to retrieve the password string when the worker opens the XLSX file. Cannot be set after the worker has started running. It defaults to null.

Note: This property is thread-safe.

Declaration
public Func<string> PasswordFunc { get; set; }
Property Value
Type Description
Func<String>

The password function, which must return the password to use, or null for no password.
Exceptions
Type Condition
InvalidOperationException

Cannot set the value after the worker has started running.

XlsxSourceCommands

Gets or sets the commands that defines where to read, which columns to include, and their failure policy, e.g.:

xsc => xsc
    .Address("'My Sheet'!B5")
    .ToNames("Category", "Subcategory")
    .Name("Id", "ProductId").Ignore()

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

The commands must be set before the worker runs, and cannot be set after the worker has started running.

The Address() specifies the top left cell to import, e.g. "B10", or a range, e.g. "A1:F15". The address can optionally include a sheet name, e.g. "'My Sheet'!A1". The default address is the top left cell (i.e. "A1") on the first sheet.

Note: This property is thread-safe.

Declaration
public Action<IXlsxSourceCommand> XlsxSourceCommands { get; set; }
Property Value
Type Description
Action<IXlsxSourceCommand>
Exceptions
Type Condition
InvalidOperationException

Cannot set the value after the worker has started running.

Methods

RunAsync()

This method can be overridden to add custom functionality to the derived worker that runs before and after the row processing. In this case, the base class base.RunAsync() must be called for the worker to function correctly.

Typically, this worker is used without overriding this method.

Declaration
protected override async Task<OutcomeStatus> RunAsync()
Returns
Type Description
Task<OutcomeStatus>

A Task describing the success or failure of the worker. An asynchronous async implementation would e.g. return OutcomeStatus.Succeeded on success, while a synchronous implementation would return OutcomeStatus.SucceededTask.
Overrides

Implements

See Also

XlsxTarget<TInput>