Read Excel File

Workbook

The path to the file to read.

Relative paths are interpreted as relative to the flow file.

Evaluated for each input row

Sheets

The sheets to extract from the file.

If the value is nil, all sheets are extracted.

Otherwise the following types are supported:

List

If the list is empty, all sheets are extracted. Otherwise each sheet in the workbook is matched against each list item in order to decide whether to extract it. If any list entry matches the sheet, it is extracted.

List entries can take the following form:

• string entries are compared to the sheet name
• boolean entries of true indicate to import any sheet
• functions of the form (string name, long index, boolean visible) -> boolean are called with the sheet name, its index in the workbook and its visibility flag - a result of true indicates to import the sheet

String

A string is interpreted as the name of a sheet to extract - only the sheet of the given name is extracted, if present.

Function

Functions of the form (string name, long index, boolean visible) -> boolean are called with the sheet name, its index in the workbook and its visibility flag - a result of true indicates to import the sheet.

Example function that indicates to import only sheets whose name starts with ‘data_’ and which are not hidden in the workbook.

(name, index, visible) ->
  strings.starts_with?(name, "data_") && visible

Boolean

• if true, all sheets are extracted
• if false, no sheets are extracted

Evaluated for each input row

Sheets

Indicates the structure used to represent sheets in the result.

as dict - sheets are returned as a dict indexed by name i.e.

{
  :sheet_1 {...},
  :sheet_2 {...}
}

as list - sheets are returned as a list in order of appearance in the workbook i.e.

[
  {:name 'sheet_1', ...},
  {:name 'sheet_2', ...}
]

Evaluated for each input row

Cells

Defines how cells are represented in the result.

• value

  • cells are represented by their value only - blank cells are represented as nil

• extended

  • cells are represented as a dict containing cell type, format, value and any comment i.e.

{
    :value 100.0,
    :cell_type 'NUMERIC',  # excel cell type
    :value_type 'NUMERIC', # excel type of calculated value, if cell type is 'FORMULA'
    :format '0',
    :comment {
      :author 'Sherlock Holmes',
      :text 'Sherlock Holmes: Here is a curious data point.'
    },
  }

Temporal Values Excel saves date and time data internally as an anchored numeric value. The cell format makes it so that this number appears as a date or time value in Excel. Therefore the Excel value type of temporal values is ‘NUMERIC’.

The step detects temporal values and generates a proper datetime as the cell’s value.

Note

Excel saves data in a sparse table format, and empty cells that have never been edited or formatted are often technically missing from Excel workbook files.

Cells that are not technically present in the workbook are always represented as nil.

Evaluated for each input row

Read Data Rows

If checked, data rows are included in the result.

Evaluated for each input row

Read Text Boxes

If checked, text boxes data is included in the result.

Evaluated for each input row

Read Images

If checked, image data is included in the result.

Evaluated for each input row

Timezone

Timezone used for extracted datetimes.

Evaluated for each input row

Password

Password used to read password-protected xls files.

Evaluated for each input row

workbook

The complete contents of the Excel file as a dict.

It has the following structure:

{
  :file "/path/to/workbook.xlsx"
  :sheets {
    :sheet_1 {
      :name "sheet_1",
      :index 0
      :visible true
      :rows [
        [cell_A, cell_B, ...],
        [cell_A, cell_B, ...],
        ... additional rows ...
      ],
      :text_boxes: [
        {
          :name 'Text Box 1',
          :text 'Text Box Content'
        },
        ... additional text boxes ...
      ],
      :images: [
        {
          :name 'Picture 1',
          :mime_type 'image/png',
          :file_extension 'png'
          :data 0b89504e470d0a1a0a...
        },
        ... additional images ...
      ],

    },
    ... additional sheets ...
  }
}