DevOps:Doc/FlexForm/2.5/input/file/Excel
No edit summary
No edit summary
 
(2 intermediate revisions by the same user not shown)
Line 9: Line 9:


'''Reference a Wiki Page (Recommended):''' Store your JSON mapping rules on a wiki page (e.g., <code>MyExcelRulesTemplate or Template:MyExcelRules</code>) and pass the page title to <code>excel_mapping</code>.<syntaxhighlight lang="html5">
'''Reference a Wiki Page (Recommended):''' Store your JSON mapping rules on a wiki page (e.g., <code>MyExcelRulesTemplate or Template:MyExcelRules</code>) and pass the page title to <code>excel_mapping</code>.<syntaxhighlight lang="html5">
<_input type="file" name="excel_file" id="excel_upload" target="ConvertedData" action="convertfrom:xlsx" slot="ws-class-props" excel_mapping="Template:MyExcelRules" />
<input type="file" name="excel_file" id="excelUpload" target="ConvertedData" action="convertfrom:xlsx" slot="ws-class-props" excel_mapping="Template:MyExcelRules" />
</syntaxhighlight>'''Inline JSON String:''' Pass the JSON rules directly inside the attribute (using single quotes for the attribute).<syntaxhighlight lang="html5">
</syntaxhighlight>'''Inline JSON String:''' Pass the JSON rules directly inside the attribute (using single quotes for the attribute).<syntaxhighlight lang="html5">
<_input type="file" name="excel_file" id="excel_upload" target="ConvertedData" action="convertfrom:xlsx" excel_mapping='{"customer":{"type":"cell","cell":"C5"}}' />
<input type="file" name="excel_file" id="excelUpload" target="ConvertedData" action="convertfrom:xlsx" excel_mapping='{"customer":{"type":"cell","cell":"C5"}}' />
</syntaxhighlight>
</syntaxhighlight>


Line 166: Line 166:


</syntaxhighlight>
</syntaxhighlight>
=== Links ===
[https://www.youtube.com/watch?v=97-Qi8Z639c Watch a video on Excel mapping]

Latest revision as of 21:31, 12 November 2026

Advanced Excel Mapping (excel_mapping) as of version 2.11.0

In addition to converting an entire worksheet directly into a flat array of rows, FlexForm allows you to extract specific cells, lists, key-value pairs, and sub-tables from anywhere within an Excel file using the excel_mapping parameter.

If excel_mapping is omitted, FlexForm defaults to its standard full-sheet conversion (ensuring 100% backwards compatibility).

How to use excel_mapping

You can pass a JSON mapping configuration to your file input in two ways:

Reference a Wiki Page (Recommended): Store your JSON mapping rules on a wiki page (e.g., MyExcelRulesTemplate or Template:MyExcelRules) and pass the page title to excel_mapping.

<input type="file" name="excel_file" id="excelUpload" target="ConvertedData" action="convertfrom:xlsx" slot="ws-class-props" excel_mapping="Template:MyExcelRules" />

Inline JSON String: Pass the JSON rules directly inside the attribute (using single quotes for the attribute).

<input type="file" name="excel_file" id="excelUpload" target="ConvertedData" action="convertfrom:xlsx" excel_mapping='{"customer":{"type":"cell","cell":"C5"}}' />

Supported Extraction Types (type)

A mapping configuration consists of a JSON object where each top-level key represents the desired property name in the resulting JSON output, and its value defines the extraction rule.

Rule Type Description Key Parameters
cell Extracts a single cell value. cell (e.g., "C5") or col ("C" or 3) and row (5).
list Extracts a 1D array of values from a single column across multiple rows. col, row_start, row_end (or rows), and optional skip_empty (default: true).
key_value Reads keys from one column and pairs them with values from another column. key_col, val_col, row_start, row_end (or rows).
table Extracts a structured array of objects from a table range, using either a header row in the sheet or custom-defined column keys. row_start, row_end (or rows), optional stop_on_empty_row (default: false), and either:


• Auto-headers: header_row, col_start, col_end

• Custom keys: columns (object mapping column letters/numbers to custom JSON keys).

Common Rule Options

  • Column Notation: Columns can be defined using either Excel letters ("A", "C", "AA") or 1-based column numbers (1, 3, 27).
  • Row Selection (row_start / row_end vs. rows):
    • Use "row_start": 5, "row_end": 11 to read a continuous range of rows. If row_end is omitted, FlexForm reads until the last data row of the sheet.
    • Use "rows": [5, 6, 7, 12] to cherry-pick specific rows and skip rows in between (applicable to list, key_value, and table).
  • Table Header Modes (header_row vs. columns):
    • Automatic headers from sheet: Specify "header_row": 15 (along with optional col_start and col_end) to use the text in row 15 as the JSON object keys.
    • Custom column keys: Specify "columns": {"B": "candidate_id", "C": "initials", "E": "last_name"} to define your own JSON keys per column and skip unwanted or merged columns automatically (no header_row required).
  • Stop on Empty Row (stop_on_empty_row): When set to true on a table rule, FlexForm automatically stops reading rows as soon as it encounters a completely empty row within the defined columns. This is ideal for dynamic tables where the exact number of filled rows varies per uploaded file.
  • Worksheet Selection per Rule (sheet_by_name / sheet_by_id): Each rule can optionally specify "sheet_by_name": "Sheet2" or "sheet_by_id": 1 to combine data from multiple tabs into a single JSON result. If omitted, it falls back to the input field's default sheet.
  • Value Formatting (value_type): Controls how cell data is read:
    • "calculated" (default): Evaluates Excel formulas and returns the raw data type (e.g., numbers as int/float).
    • "formatted": Applies Excel's cell formatting (essential for dates like "21-04-2026", times like "15:05 uur", or currency).
    • "raw": Returns the literal unparsed cell content (e.g., "=SUM(A1:A5)").
    • Per-column formatting in tables: For table rules, value_type can also be an object matching your column keys (e.g., {"default": "calculated", "birth_date": "formatted"}).

Complete Example

Suppose you have an Excel registration form containing a single document code cell, two blocks of key-value form fields, a list of remarks, a table using sheet headers, and a candidate table using custom JSON keys:

Mapping Configuration (Template:MyExcelRules):

{
  "document_code": {
    "type": "cell",
    "cell": "F12"
  },
  "registration_details": {
    "type": "key_value",
    "key_col": "B",
    "val_col": "C",
    "row_start": 5,
    "row_end": 11,
    "value_type": "formatted"
  },
  "contact_info": {
    "type": "key_value",
    "key_col": "E",
    "val_col": "F",
    "rows": [5, 6, 7, 12]
  },
  "notes": {
    "type": "list",
    "col": "C",
    "row_start": 20,
    "row_end": 25,
    "skip_empty": true
  },
  "candidates": {
    "type": "table",
    "row_start": 17,
    "stop_on_empty_row": true,
    "columns": {
      "B": "custom key",
      "C": "custom title key",
      "D": "custom first name key",
      "E": "custom last name key",
      "F": "custom birthday key"
    },
    "value_type": {
      "default": "calculated",
      "geboortedatum": "formatted"
    }
  },
  "options_table": {
    "type": "table",
    "sheet_by_id": 0,
    "header_row": 35,
    "row_start": 36,
    "row_end": 40,
    "col_start": "A",
    "col_end": "E",
    "stop_on_empty_row": true
  }
}

Resulting JSON save to the wiki:

{
    "document_code": "a931e",
    "registration_details": {
        "Company": "Wikibase Solutions",
        "Address": "Bredaseweg 108A",
        "Postcal code and city": "4902NS Oosterhout",
        "Date": "21-04-2026",
        "Location": "Meetingroom",
        "Starttime": "15:05 hour"
    },
    "contact_info": {
        "Contact person": "Charly",
        "Phone": '123123123',
        "Email": "charly@example.com",
        "Code": "a931e"
    },
    "notes": [
        "First remark",
        "Second remark"
    ],
    "candidates": [
        {
            "custom key": 1234,
            "custom title key": "J.",
            "custom first name key": "van",
            "custom last name key": "Delft",
            "custom birthday key": "10-04-2013"
        },
        {
            "custom key": 1235,
            "custom title key": "M.",
            "custom last name key": "Schuring",
            "custom birthday key": "17-03-2010"
        }
    ],
    "options_table": [
        {
            "options": "$wgDisableCounters",
            "description": "automatically show hitcounts on a page and Special page",
            "HitCounter": "yes",
            "WSStats": "no",
            "More": "WSStats allows for adding this to a page using parser function"
        }
    ]
}

Links

Watch a video on Excel mapping