DevOps:Doc/FlexForm/2.5/input/file/Excel
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:
• Custom 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_endvs.rows):- Use
"row_start": 5, "row_end": 11to read a continuous range of rows. Ifrow_endis 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 tolist,key_value, andtable).
- Use
- Table Header Modes (
header_rowvs.columns):- Automatic headers from sheet: Specify
"header_row": 15(along with optionalcol_startandcol_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 (noheader_rowrequired).
- Automatic headers from sheet: Specify
- Stop on Empty Row (
stop_on_empty_row): When set totrueon atablerule, 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": 1to 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 asint/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
tablerules,value_typecan 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"
}
]
}