3. Reference Syntax

The capturing is preformed according to an excel like reference syntax and the non-empty cells of the targeted excel-sheet. The syntax is defined as follows:

[<excel>]#[<sheet>!]<st-cel>[(<moves>)][:<nd-cel>[(<moves>)]][:<expansion>][<filters>]

Note

The fields between square parenthesis are optionals.

Follows the description of the parameters:

  • excel: excel file path relative to the parent reference file directory. If not defined, the parent reference excel is inherited.

  • sheet: excel sheet name if not defined, the parent reference excel sheet name is inherited.

  • st-cel: first cell coordinate of excel range. The cell coordinate (i.e., <column><row>) is defined by a column (letter) and row (number), like in excel. xlref allows two special characters ^ and _, that represents the leftmost/topmost and rightmost/bottommost non-empty cell column/row.

  • moves: the sequence of primitive directions (i.e., L:left, U: up, R: right, D: down) that xlref uses iteratively for finding the first non-empty cell. The allowed primitive direction combinations are L, U, R, D, LD, LU, UL, UR, RU, RD, DL, and DR. The following diagram shows the graphically the moves from the starting cell X:

            U
     UL◄───┐▲┌───►UR
    LU     │││     RU
     ▲     │││     ▲
     │     │││     │
     └─────┼│┼─────┘
    L◄──────X──────►R
     ┌─────┼│┼─────┐
     │     │││     │
     ▼     │││     ▼
    LD     │││     RD
     DL◄───┘▼└───►DR
            D
    
  • nd-cel: second cell coordinate of excel range. It has the same syntax of the st-cel, but it has and extra special character .. This represents the column or row of the st-cel after the application of the moves.

  • expansion: the sequence of primitive directions to expand the captured range.

  • filters: list of string and or dictionaries that defines the filters to apply iteratively on the captured range.

3.1. Reference Reading Steps

The library performs the following steps to read a reference:

  1. Open the excel file or inherits the parent’s one,

  2. Open the sheet by its name or inherits the parent’s one,

  3. Set the first range cell,

  4. Move the first cell according to the specified moves until it finds the first non-empty cell,

  5. Set the second range cell or inherits the moved first range cell,

  6. Move the second cell like in point 4,

  7. Expand the range according to the defined expansions,

  8. Apply the iteratively the filters on the captured range.