Apache POI Excel parser plugin for Embulk

Parses Microsoft Excel files(xls, xlsx) read by other file input plugins.
This plugin uses Apache POI.

Overview

• Plugin type: parser
• Guess supported: no
• Embulk 0.9 or earlier (refer to https://github.com/hishidama/embulk-parser-excel-poi for 0.10 and later)

Example

in:
  type: any file input plugin type
  parser:
    type: poi_excel
    sheets: ["DQ10-orb"]
    skip_header_lines: 1	# first row is header.
    columns:
    - {name: row, type: long, value: row_number}
    - {name: get_date, type: timestamp, cell_column: A, value: cell_value}
    - {name: orb_type, type: string}
    - {name: orb_name, type: string}
    - {name: orb_shape, type: long}
    - {name: drop_monster_name, type: string}

if omit value, specified cell_value.
if omit cell_column when value is cell_value, specified next column.

Configuration

• sheets: sheet name. can use wildcards *, ?. (list of string, required)
• record_type: record type. (row, column or sheet. default: row)
• skip_header_lines: skip rows when record_type=row (skip columns when record_type=column). ignored when record_type=sheet. (integer, default: 0)
• columns: column definition. see below. (hash, required)
• sheet_options: sheet option. see below. (hash, default: null)

columns

• name: Embulk column name. (string, required)
• type: Embulk column type. (string, required)
• value: value type. see below. (string, default: cell_value)
• column_number: same as cell_column.
• cell_column: Excel column number. see below. (string, default: next column when record_type=row)
• cell_row: Excel row number. see below. (integer, default: next row when record_type=column)
• cell_address: Excel cell address such as A1, Sheet1!B3. (string, not required)
• numeric_format: format of numeric(double) to string such as %4.2f. (default: Java's Double.toString())
• attribute_name: use with value cell_style, cell_font, etc. see below. (list of string)
• on_cell_error: processing method of Cell error. see below. (string, default: constant)
• formula_handling: processing method of formula. see below. (evaluate or cashed_value. default: evaluate)
• on_evaluate_error: processing method of evaluate formula error. see below. (string, default: exception)
• formula_replace: replace formula before evaluate. see below.
• on_convert_error: processing method of convert error. see below. (string, default: exception)
• search_merged_cell: search merged cell when cell is BLANK. (none, linear_search, tree_search or hash_search, default: hash_search)

value

• cell_value: value in cell.

• cell_formula: formula in cell. (if cell is not formula, same cell_value.)

• cell_style: all cell style attributes. returned json string. see attribute_name. (type required string)

• cell_font: all cell font attributes. returned json string. see attribute_name. (type required string)

• cell_comment: all cell comment attributes. returned json string. see attribute_name. (type required string)

• cell_type: cell type. returned Cell.getCellType() of POI.

• cell_cached_type: cell cached formula result type. returned Cell.getCachedFormulaResultType() of POI when CellType==FORMULA, otherwise same as cell_type (returned Cell.getCellType()).

• sheet_name: sheet name.

• row_number: row number(1 origin).

• column_number: column number(1 origin).

• constant: constant value.

  • constant.value: specified value.
  • constant: null.

cell_column

Basically used for record_type=row.

• A,B,C,...: column number of "A1 format".
• number: column number (1 origin).
• +: next column.
• +name: next column of name.
• +number: number next column.
• -: previous column.
• -name: previous column of name.
• -number: number previous column.
• =: same column.
• =name: same column of name.

cell_row

Basically used for record_type=column.

• number: row number (1 origin).

attribute_name

valueがcell_style, cell_font, cell_commentのとき、デフォルトでは、全属性を取得してJSON文字列に変換します。
（JSON文字列を返すので、typeはstringである必要があります）

    columns:
    - {name: foo, type: string, cell_column: A, value: cell_style}

attribute_nameを指定することで、指定された属性だけを取得してJSON文字列に変換します。

• attribute_name: attribute names. (list of string)

    columns:
    - {name: foo, type: string, cell_column: A, value: cell_style, attribute_name: [border_top, border_bottom, border_left, border_right]}

また、cell_styleやcell_fontの直後にピリオドを付けて属性名を指定することにより、その属性だけを取得することが出来ます。
この場合はJSON文字列にはならず、属性の型に合うtypeを指定する必要があります。

    columns:
    - {name: foo, type: long, value: cell_style.border}
    - {name: bar, type: long, value: cell_font.color}

なお、cell_styleやcell_fontでは、cell_columnを省略した場合は直前と同じ列を対象とします。
（cell_valueでは、cell_columnを省略すると次の列に移る）

on_cell_error

Processing method of Cell error (#DIV/0!, #REF!, etc).

    columns:
    - {name: foo, type: string, cell_column: A, value: cell_value, on_cell_error: error_code}

• constant: set null. (default)
• constant.value: set specified value.
• error_code: set error code.
• exception: throw exception.

formula_handling

Processing method of formula.

    columns:
    - {name: foo, type: string, cell_column: A, value: cell_value, formula_handling: cashed_value}

• evaluate: evaluate formula. (default)
• cashed_value: cashed value in cell.

on_evaluate_error

Processing method of evaluate formula error.

    columns:
    - {name: foo, type: string, cell_column: A, value: cell_value, on_evaluate_error: constant}

• constant: set null.
• constant.value: set specified value.
• exception: throw exception. (default)

formula_replace

Replace formula before evaluate.

    columns:
    - {name: foo, type: string, cell_column: A, value: cell_value, formula_replace: [{regex: aaa, to: "A${row}"}, {regex: bbb, to: "B${row}"}]}

${row} is replaced with the current row number. ${column} is replaced with the current column string.

on_convert_error

Processing method of convert error. ex) Excel boolean to Embulk timestamp

    columns:
    - {name: foo, type: timestamp, format: "%Y/%m/%d", cell_column: A, value: cell_value, on_convert_error: constant.9999/12/31}

• constant: set null.
• constant.value: set specified value.
• exception: throw exception. (default)

sheet_options

Options of individual sheet.

  parser:
    type: poi_excel
    sheets: [Sheet1, Sheet2]
    columns:
    - {name: date, type: timestamp, cell_column: A}
    - {name: foo, type: string}
    - {name: bar, type: long}
    sheet_options:
      Sheet1:
        skip_header_lines: 1
        columns:
          foo: {cell_column: B}
          bar: {cell_column: C}
      Sheet2:
        skip_header_lines: 0
        columns:
          foo: {cell_column: D}
          bar: {value: constant.0}

sheet_options is map of sheet name.
Map values are skip_header_lines, columns.

columns is map of column name.
Map values are same columns in parser (excluding name, type).

Install

$ embulk gem install embulk-parser-poi_excel

Build

$ ./gradlew test
$ ./gradlew package