No edit summary |
|||
| (4 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
== 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., <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="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"> | |||
<input type="file" name="excel_file" id="excelUpload" target="ConvertedData" action="convertfrom:xlsx" excel_mapping='{"customer":{"type":"cell","cell":"C5"}}' /> | |||
</syntaxhighlight> | |||
==== Supported Extraction Types (<code>type</code>) ==== | |||
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. | |||
{| class="wikitable" | |||
|+ | |||
!Rule Type | |||
!Description | |||
!Key Parameters | |||
|- | |||
|'''<code>cell</code>''' | |||
|Extracts a single cell value. | |||
|<code>cell</code> (e.g., <code>"C5"</code>) '''or''' <code>col</code> (<code>"C"</code> or <code>3</code>) and <code>row</code> (<code>5</code>). | |||
|- | |||
|<code>'''list'''</code> | |||
|Extracts a 1D array of values from a single column across multiple rows. | |||
|<code>col</code>, <code>row_start</code>, <code>row_end</code> (or <code>rows</code>), and optional <code>skip_empty</code> (default: <code>true</code>). | |||
|- | |||
|'''<code>key_value</code>''' | |||
|Reads keys from one column and pairs them with values from another column. | |||
|<code>key_col</code>, <code>val_col</code>, <code>row_start</code>, <code>row_end</code> (or <code>rows</code>). | |||
|- | |||
|'''<code>table</code>''' | |||
|Extracts a structured array of objects from a table range, using either a header row in the sheet or custom-defined column keys. | |||
|<code>row_start</code>, <code>row_end</code> (or <code>rows</code>), optional <code>stop_on_empty_row</code> (default: <code>false</code>), and '''either''': | |||
• '''Auto-headers:''' <code>header_row</code>, <code>col_start</code>, <code>col_end</code> | |||
• '''Custom keys:''' <code>columns</code> (object mapping column letters/numbers to custom JSON keys). | |||
|} | |||
==== Common Rule Options ==== | |||
* '''Column Notation:''' Columns can be defined using either Excel letters (<code>"A"</code>, <code>"C"</code>, <code>"AA"</code>) or 1-based column numbers (<code>1</code>, <code>3</code>, <code>27</code>). | |||
* '''Row Selection (<code>row_start</code> / <code>row_end</code> vs. <code>rows</code>):''' | |||
** Use <code>"row_start": 5, "row_end": 11</code> to read a continuous range of rows. If <code>row_end</code> is omitted, FlexForm reads until the last data row of the sheet. | |||
** Use <code>"rows": [5, 6, 7, 12]</code> to cherry-pick specific rows and skip rows in between (applicable to <code>list</code>, <code>key_value</code>, and <code>table</code>). | |||
* '''Table Header Modes (<code>header_row</code> vs. <code>columns</code>):''' | |||
** '''Automatic headers from sheet:''' Specify <code>"header_row": 15</code> (along with optional <code>col_start</code> and <code>col_end</code>) to use the text in row 15 as the JSON object keys. | |||
** '''Custom column keys:''' Specify <code>"columns": {"B": "candidate_id", "C": "initials", "E": "last_name"}</code> to define your own JSON keys per column and skip unwanted or merged columns automatically (no <code>header_row</code> required). | |||
* '''Stop on Empty Row (<code>stop_on_empty_row</code>):''' When set to <code>true</code> on a <code>table</code> 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 (<code>sheet_by_name</code> / <code>sheet_by_id</code>):''' Each rule can optionally specify <code>"sheet_by_name": "Sheet2"</code> or <code>"sheet_by_id": 1</code> 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 (<code>value_type</code>):''' Controls how cell data is read: | |||
** <code>"calculated"</code> ''(default)'': Evaluates Excel formulas and returns the raw data type (e.g., numbers as <code>int</code>/<code>float</code>). | |||
** <code>"formatted"</code>: Applies Excel's cell formatting (essential for dates like <code>"21-04-2026"</code>, times like <code>"15:05 uur"</code>, or currency). | |||
** <code>"raw"</code>: Returns the literal unparsed cell content (e.g., <code>"=SUM(A1:A5)"</code>). | |||
** ''Per-column formatting in tables:'' For <code>table</code> rules, <code>value_type</code> can also be an object matching your column keys (e.g., <code>{"default": "calculated", "birth_date": "formatted"}</code>). | |||
==== 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 (<code>Template:MyExcelRules</code>):'''<syntaxhighlight lang="json"> | |||
{ | |||
"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 | |||
} | |||
} | |||
</syntaxhighlight>Resulting JSON save to the wiki:<syntaxhighlight lang="json"> | |||
{ | |||
"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" | |||
} | |||
] | |||
} | |||
</syntaxhighlight> | |||
=== Links === | |||
[https://www.youtube.com/watch?v=97-Qi8Z639c Watch a video on Excel mapping] | |||
| ws-base-props | |||
|---|---|---|---|
| Line 1: | Line 1: | ||
{{Base properties | {{Base properties | ||
|Title= | |Title=Excel | ||
|Class=Doc | |Class=Doc | ||
}} | }} | ||
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:
• 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"
}
]
}