Excel and XML export
Last updated
Last updated
This is an Orbeon Forms PE feature.
Orbeon Forms includes a few ways to export form definitions and form data. This page documents a very specific capability: exporting a form definition, optionally alongside a single piece of form data, in Excel or XML format, appropriate for the data into Orbeon Forms at a later time.
For a different type of export, see also .
You can export using an "Excel Export" or "XML Export" button from the following locations:
Form Builder
No
Yes
Form Runner Detail page
Yes
With Form Builder, the Excel Export button is available by default. You can control its availability with the following property, by adding the excel-export
button token. We show here the default value of this property for Orbeon Forms 2023.1:
Note the |
which denotes combined buttons.
Detail page:
In addition, you must set the following property to enable export with data on the Summary and Detail pages:
The reason for this extra setting is that, even if the user has read access to the form, exporting a form in Excel or XML format can reveal some hidden data.
Form Runner can export in two formats:
excel-named-ranges
: Excel document containing the structure of the form, using named ranges (see below) to map form controls to cells
xml-form-structure-and-data
: XML document with indications about the form structure, alongside XML data
In each case, when form data is requested, the export is done on the basis of a single document.
The Excel document contains all the fields of the form, attempting to mirror the layout of the original form. This includes section titles and field labels.
The export feature produces an Excel document that look like this:
Orbeon Forms includes, in the generated Excel document, so-called named ranges which match the control names assigned in Form Builder. This allows reimporting data in those cells during a subsequent import.
Excel names do not support all the characters supported in Form Runner names. In addition, Excel has some very specific and poorly documented rules for name ranges for names that look like cell references. Such names are modified by Orbeon Forms when needed with an _
prefix. The following shows some modification that are performed by Orbeon Forms:
C
_C
c
_c
R
_R
r
_r
B1
_B1
C1_FOO
_C1_FOO
C123456
_C123456
AA1
_AA1
AAA1
_AAA1
Excel allows you to see the name of a given cell or range:
In the case of repeated grids, groups of cells share the same name.
Repeated grids, or repeated sections containing only non-repeated grids, are "flattened". That is, all form controls are presented in a single Excel row, with one row for each repetition.
Lists of choices are exported as text, showing the value of the choice between parentheses, and the label next to it.
When importing, the cell value must contain the value of the choice, not the label.
Multiple-selection controls require entering the values as a space-separated list of tokens.
As available in Orbeon Forms 2021.1, the new Excel export feature has the following limitations:
The 24-column mode is not supported.
Dynamic list of choices (for example dynamic dropdowns) are not supported.
Nested repeats are not supported.
Repeated grids or sections are always flattened.
Section templates are not supported.
Some form controls are not supported, including:
Formatted Text
Handwritten Signature
Attachments
No calculations or validations are exported.
This format exports an XML document with the form structure and the data.
The XML document is structured as follows (with ...
indicating omitted content).
The XML document contains the following sections:
orbeon-export
: root element
export-date
attribute: date and time of the export
export-lang
attribute: language of the form at the time of the export
export-format-version
attribute: version of the export format
form-metadata
: enclosing element for form metadata
application-name
: application name
form-name
: form name
title
: form title
description
: form description
form-structure
: enclosing element for the structure of the form
section
: form section
label
: section label
grid
: grid in the section
control
: control in the grid
label
: control label
hint
: control hint
form-data
: enclosing element for the form data
Form Runner
With Form Runner, you can enable the export button with the following properties, by including the same excel-export
button token. :
The Excel export feature is aimed at organizations whose users are familiar with Excel spreadsheets. Users can export a form to a spreadsheet and fill it out on their computers. They can pass the spreadsheet around for other users to complete it. When they are done, they can the resulting spreadsheet back to Orbeon Forms, where the data is captured, validated, shown to the user for review, and then saved to the Orbeon Forms database.
For more about the import feature, see .
The purpose of this export is to allow you to save the form data in a format that can be reimported into Orbeon Forms at a later time. The form structure is included to provide context for the data. It doesn't replace the form definition as you can seen in Form Builder's Edit Source as well as .
The form-data
section contains the form data in the version indicated by the fr:data-format-version
attribute, see .
You can also export to Excel and XML .
Blog post: