Date: Fri, 29 Mar 2024 17:12:55 +0200 (EET) Message-ID: <2043063642.1288.1711725175217@wiki.vertuna.com> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_1287_86965657.1711725175108" ------=_Part_1287_86965657.1711725175108 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
Since version 1.53.4 we have introduced couple of experi= mental APIs which help you to make a bridge between ConfiForms web= -services backed fields and Excel files attached to your Confluence page.= p>
3 services:
These 2 APIs are available at the following URLs:
File format | API URL | Method |
---|---|---|
Excel 97-2004 (xls) | https://YOUR_CONFLUENCE_SERVER/ajax/confiforms/rest/excel-2json.action= | GET |
Excel (xlsx) | https://YOUR_CONFLUENCE_SERVER/ajax/confiforms/rest/excel2007-2json.ac= tion | GET |
CSV (default format) | h= ttps://your_confluence_server/ajax/confiforms/rest/csv-2json.action | GET |
Services expect/support the following parameters:
Parameter | Description | Type | Required |
---|---|---|---|
attachmentId | Attachment ID of the Excel file attachment to us= e as source | Numeric | Yes |
skipRows | Number of rows to skip (from the top). Defaults = to 0 (rows to skip, from the top of the sheet) | Numeric | No |
idCellNum | Which column to use as ID | Numeric | No |
labelCellNum | Which column to use as label | Numeric | No |
lookupValue Since 2.27.21 |
Can supply a lookup value for the service to loo= kup. The subset returned will be filtered to match (as substring) the given= value | Text | No |
lookupField Since 2.27.23 <= /td> | Limiting the matching logic to specific field= (cell) Example: cell1 to limit to cell1 value only= . If not specified then the match for given lookupValue is done against the= whole row (all cells) |
Text | No |
exactMatch Since 2.27.24 = td> | You can set the search to use "exact match", = by default (when exactMatch is unspecified or false) the search is done as = text substring match (case insensitive) |
Boolean | No |
Column and rows numbering starts with 0
NB! Only the first sheet from Excel file is currently s= upported!
Examples:
Last example reads ALL the rows and columns (skipping just the first one= ) and creates a structure similar to the one listed below
[ { "cell2": "uno", "cell3": "in cell 3", "cell1": "1" }, { "cell2": "duo", "cell1": "2" }, { "cell2": "tres", "cell3": "here is cell three", "cell1": "3" }, { "cell2": "four", "cell1": "4" }, { "cell2": "five", "cell1": "5" }, { "cell2": "six", "cell1": "6" } ]
From the following excel file
As you can see, we can create a JSON Array structure that perfectly fits= our ConfiForms web-service enabled fields
So, here is how you can create a ConfiForms Form which uses this file an= d shows it's values in the dropdown
Configuration for ConfiForms looks like this
With "myvalues" field configured as web-service dropdown
We have defined new connection to use the above mentioned excel to JSON = APIs
And set the mapping to use cell1 as ID column for the dropdown and cell2= as a label
And you can see the online results right here
My excel values |
myvalues.id |
myvalues.cell1 |
myvalues.label |
myvalues.cell3 |
---|---|---|---|---|
duo | c81e728d9d4c2f636f067f89cc14862c | 2 | duo | |
five | e4da3b7fbbce2345d7772b0674a318d5 | 5 | five | |
six | 1679091c5a880faf6fb5e6087eb1b2dc | 6 | six | |
four | a87ff679a2f3e71d9181a67b7542122c | 4 | four | |
uno | c4ca4238a0b923820dcc509a6f75849b | 1 | uno | in cell 3 |
uno | c4ca4238a0b923820dcc509a6f75849b | 1 | uno | in cell 3 |
uno | c4ca4238a0b923820dcc509a6f75849b | 1 | uno | in cell 3 |
uno | c4ca4238a0b923820dcc509a6f75849b | 1 | uno | in cell 3 |
duo | c81e728d9d4c2f636f067f89cc14862c | 2 | duo | |
four | a87ff679a2f3e71d9181a67b7542122c | 4 | four | |
six | 1679091c5a880faf6fb5e6087eb1b2dc | 6 | six | |
uno | c4ca4238a0b923820dcc509a6f75849b | 1 | uno | in cell 3 |
six | 1679091c5a880faf6fb5e6087eb1b2dc | 6 | six | |
uno | c4ca4238a0b923820dcc509a6f75849b | 1 | uno | in cell 3 |
four | a87ff679a2f3e71d9181a67b7542122c | 4 | four | |
uno | c4ca4238a0b923820dcc509a6f75849b | 1 | uno | in cell 3 |
uno | c4ca4238a0b923820dcc509a6f75849b | 1 | uno | in cell 3 |
uno | c4ca4238a0b923820dcc509a6f75849b | 1 | uno | in cell 3 |
uno | c4ca4238a0b923820dcc509a6f75849b | 1 | uno | in cell 3 |
duo | c81e728d9d4c2f636f067f89cc14862c | 2 | duo | |
uno | c4ca4238a0b923820dcc509a6f75849b | 1 | uno | in cell 3 |
uno | c4ca4238a0b923820dcc509a6f75849b | 1 | uno | in cell 3 |
uno | c4ca4238a0b923820dcc509a6f75849b | 1 | uno | in cell 3 |
duo | c81e728d9d4c2f636f067f89cc14862c | 2 | duo | |
duo | c81e728d9d4c2f636f067f89cc14862c | 2 | duo | |
uno | c4ca4238a0b923820dcc509a6f75849b | 1 | uno | in cell 3 |