Orbeon Forms
  • Getting started
  • Installation
    • Logging
    • Configuration banner
    • Docker
    • Azure
    • Tomcat
    • WildFly
    • WebSphere
    • WebLogic
    • GlassFish
    • Caches
    • Replication
    • Upgrading
  • Configuration
    • Properties
      • General
        • HTTP client
      • Form Runner
        • Detail page
          • Attachments
          • Email properties
          • PDF
          • Table of contents
        • Persistence
        • Summary page
      • Form Builder
      • XForms
    • Advanced
      • Workflows
      • Session management
      • State handling
      • Client-side error handling
      • Clustering and High Availability
      • Configuring a Form Runner eXist database
      • Creating a production WAR
      • Environments
      • JavaScript and CSS assets
      • Limiter filter
      • Run modes
      • Security
        • Content-Security-Policy header
      • SAP Hybris Module
      • XForms logging
    • Troubleshooting
      • Troubleshooting with the orbeon.log
      • Memory and threads
      • Relational database logging
      • Misc
  • Form Builder
    • Form settings
      • Time window
    • Form editor
      • Form area
      • Toolbox
      • Buttons bar
      • Control settings
      • Dependent fields and sections
      • Validation
      • Choices editor
      • Publishing
      • Cut, copy and paste
      • Section and grid settings
      • Section settings
      • Grid settings
      • Quick control search
      • Repeat settings
      • Repeated grids
      • Undo and redo
      • Keyboard shortcuts
    • Formulas
      • Examples of formulas
      • Formulas inspector
      • Formulas console
    • Summary page
    • Form localization
    • Advanced
      • Edit source
      • Services and actions
        • HTTP services
        • Database services
        • Simple Actions
        • Action Syntax
        • Action Syntax examples
        • Synchronizing repeated content
      • Testing a form in web mode
      • Testing PDF production
      • Testing offline functionality
      • Email Settings dialog
      • Field-level encryption
      • Messages
      • Section templates
      • Template syntax
      • XML Schemas support
      • Extensibility
        • Extension API
        • Integration
        • Toolbox component metadata
  • Form Runner
    • Overview
      • Terminology
    • Pages
      • Landing page
      • Published Forms page
      • Forms Admin page
      • Summary page
    • Components
      • Alert dialog
      • Attachment
      • Autocomplete
      • Captcha
      • Character counter
      • Checkbox input
      • Currency
      • Date
      • Dropdown date
      • Static and dynamic dropdown
      • Error summary
      • Grid
      • Handwritten signature
      • Hidden field
      • Image
      • Image annotation
      • Image attachment
      • Number
      • Open selection
      • Repeater
      • Formatted Text / Rich Text Editor
      • Section
      • Single-selection tree
      • Source code editor
      • Time
      • US phone
      • US state
      • Video
      • Video attachment
      • Wizard
      • XForms inspector
      • Yes/No answer
    • Features
      • Automatic calculations dependencies
      • Datasets
      • Excel and XML import
      • Excel and XML export
      • Summary page Excel Export
      • Form definitions and form data Zip Export
      • Purging historical data
      • Lease
      • Localization
      • Supported languages
      • Mobile support
      • Multitenancy
      • Form Runner navigation bar
      • PDF production
        • Automatic PDF
        • Automatic PDF header and footer configuration
        • PDF templates
      • Responsive design
      • Revision history
      • S3 storage
      • Simple data migration
      • TIFF production
      • Versioning
      • Wizard view
      • Workflow stage
    • Persistence
      • Using a relational database
      • Relational database schema
      • Purging old data using SQL
      • Auditing
      • Autosave
      • Database support
      • Flat view
    • Linking and embedding
      • Linking
      • Java Embedding API
      • JavaScript Embedding API
      • Liferay full portlet
      • Liferay proxy portlet
      • Securing Form Runner access
      • Form Runner offline embedding API
      • Angular component
      • React component
    • Access control and permissions
      • Users
      • Login & Logout
      • Deployed forms
      • Form fields
      • Editing forms
      • Owner and group member
      • Organizations
      • Scenarios
      • Token-based permissions
    • Styling
      • CSS
      • Grids CSS
      • Automatic PDF styling and CSS
    • APIs
      • Authentication of server-side service APIs
      • Persistence API
        • CRUD API
        • Search API
        • List form data attachments API
        • Form Metadata API
        • Lease API
        • Reindexing API
        • Caching
        • Versioning
        • Revision History API
        • Zip Export API
        • Custom persistence providers
      • Other APIs
        • Connection context API
        • Duplicate form data API
        • File scan API
        • Form Runner JavaScript API
        • Generate XML Schema API
        • PDF API
        • Publish form definition API
        • Run form in the background API
      • Data formats
        • Form data
        • Date and time
        • Form definition
    • Architecture and integration
      • Architecture
      • Access form data
      • Integration
    • Advanced
      • Buttons and processes
        • Simple process syntax
        • Core actions
        • Form Runner actions
          • Save action
          • Send action
          • Email action
        • XForms actions
        • Predefined buttons, processes and dialogs
        • Summary page buttons and processes
      • Custom dialogs/model logic
      • Services
      • Singleton form
      • Monitoring HTTP requests
  • XForms
    • Core
      • Attribute Value Templates (AVTs)
      • Binds
      • Validation
      • Variables
      • Keyboard focus
      • XForms JavaScript API
      • Error handling
        • Detailed behavior
      • Model-Bind variables
      • XForms 2.0 support
    • Events
      • Standard support
      • UI refresh events
      • Keyboard events
      • Extension events
      • Extension context information
      • Other event extensions
    • Actions
      • Repeat, insert and delete
      • Scripting actions
      • Extensions
    • Controls
      • Label, hint, help
      • Input
      • Output
      • Text area
      • Button
      • Upload
      • Dialog
    • Submission
      • Standard support
      • JSON support
      • Asynchronous submissions
      • Caching extension
      • Other submission extensions
    • XPath
      • Type annotations
      • Expression analysis
      • Tips
      • Compatibility
      • Standard functions
      • Maps and arrays Functions
      • Extension functions
        • Core functions
        • Utility functions
        • Model functions
        • Controls functions
        • XML functions
        • JSON functions
        • HTTP functions
        • Form Runner functions
        • Other functions
        • Deprecated functions
    • XBL components
      • FAQ
      • Guide
        • XBL Tutorial
        • Bindings
        • XForms models
        • Including content
        • Event handling
        • Conventions
        • Map XBL example
        • Learning from existing components
      • Advanced topics
        • XBL Modes
        • JavaScript companion classes
        • XBL library
        • Extensions
        • Attachment controls
    • XForms tutorial
      • Introduction
      • Installation
      • The Hello application
      • The Bookcast application
        • The basic app
        • Database access
        • Polishing the app
        • Adding a feed
    • Using XForms from Java apps
  • XML Platform
    • Page Flow Controller
      • Basics
      • XML submission
      • Navigating between pages
      • Paths and matchers
      • Other configuration elements
      • Typical combinations of page model and page view
      • Examples
      • Authorizing pages and services
    • Processors
      • URL generator
      • Request generator
      • PDF to image converter
    • Resources
      • Resource managers
      • Setting up an external resources directory
    • Other
      • Binary and text documents
  • FAQ
    • Licensing
    • PE and Dev Support
    • Form Builder and Form Runner
    • Resources and support
    • Other technical questions
  • Contributors
    • Automated tests
    • Building Orbeon Forms
    • Localizing Orbeon Forms
    • Validation functions
    • Contributor License Agreement
  • Release notes
    • Orbeon Forms 2022.1.9
    • Orbeon Forms 2024.1.1
    • Orbeon Forms 2023.1.7
    • Orbeon Forms 2024.1
    • Orbeon Forms 2023.1.6
    • Orbeon Forms 2023.1.5
    • Orbeon Forms 2021.1.11
    • Orbeon Forms 2022.1.8
    • Orbeon Forms 2023.1.4
    • Orbeon Forms 2023.1.3
    • Orbeon Forms 2023.1.2
    • Orbeon Forms 2022.1.7
    • Orbeon Forms 2023.1.1
    • Orbeon Forms 2023.1
    • Orbeon Forms 2022.1.6
    • Orbeon Forms 2021.1.10
    • Orbeon Forms 2022.1.5
    • Orbeon Forms 2021.1.9
    • Orbeon Forms 2022.1.4
    • Orbeon Forms 2022.1.3
    • Orbeon Forms 2021.1.8
    • Orbeon Forms 2022.1.2
    • Orbeon Forms 2022.1.1
    • Orbeon Forms 2022.1
    • Orbeon Forms 2021.1.7
    • Orbeon Forms 2021.1.6
    • Orbeon Forms 2021.1.5
    • Orbeon Forms 2021.1.4
    • Orbeon Forms 2021.1.3
    • Orbeon Forms 2021.1.2
    • Orbeon Forms 2021.1.1
    • Orbeon Forms 2021.1
    • Orbeon Forms 2020.1.6
    • Orbeon Forms 2019.2.4
    • Orbeon Forms 2019.1.2
    • Orbeon Forms 2018.2.5
    • Orbeon Forms 2018.1.4
    • Orbeon Forms 2020.1.5
    • Orbeon Forms 2020.1.4
    • Orbeon Forms 2020.1.3
    • Orbeon Forms 2020.1.2
    • Orbeon Forms 2019.2.3
    • Orbeon Forms 2020.1.1
    • Orbeon Forms 2020.1
    • Orbeon Forms 2019.2.2
    • Orbeon Forms 2019.2.1
    • Orbeon Forms 2019.1.1
    • Orbeon Forms 2019.2
    • Orbeon Forms 2019.1
    • Orbeon Forms 2018.2.4
  • Release history
  • Use cases
  • Product roadmap
  • Index of features
Powered by GitBook
On this page
  • Availability
  • What it is
  • Allowed import formats
  • Import wizard steps
  • Access the Excel Import page
  • Select the form version
  • Upload the file
  • Validate the data
  • Import the data
  • Review the data
  • Form controls mapping
  • Excel Headings format
  • Excel Named ranges format
  • XML Form Structure and Data format
  • Handling dates and times
  • Other configurations
  • Allowing invalid data
  • Restricting to the latest version
  • Start Over button
  • Showing the application and form names
  • Forwarding parameters to the data review page
  • Passing a document id to the data review page
  • Customizing the buttons in the buttons bar
  • See also
  1. Form Runner
  2. Features

Excel and XML import

PreviousDatasetsNextExcel and XML export

Last updated 1 month ago

Availability

This is an Orbeon Forms PE feature.

What it is

This feature allows you to import data from a source Excel document or XML document.

A new Excel named ranges-based format is supported. Here are the main differences between the two types of Excel imports:

"Headings" format
"Named ranges" format

multiple form documents at a time (batch)

one form document at a time

row and column-based

named range based

no repeat support

repeat support

fixed layout

customizable layout

The Named ranges format does not replace the Headings format but complements it.

A new XML format is supported.

Allowed import formats

You can configure the Import page to support various import formats. These include:

  • excel-headings: The default Excel import format, where the first row of the Excel file contains the names of the form fields.

  • excel-named-ranges: The Excel import format where the Excel file uses named ranges to map to form fields.

  • xml-form-structure-and-data: The XML format where the XML file contains both the form structure and data.

The excel-headings format is enabled by default. It is exclusive with the excel-named-ranges format, which means that you can use none, or one of these tokens, but not both at the same time.

The default configuration is:

<property
    as="xs:string"
    name="oxf.fr.import.allowed-formats.*.*" 
    value="excel-headings xml-form-structure-and-data"/>

Here is an example of configuration which switches to the excel-named-ranges format:

<property
    as="xs:string"
    name="oxf.fr.import.allowed-formats.*.*" 
    value="excel-named-ranges xml-form-structure-and-data"/>

Import wizard steps

In order to import an Excel file, you follow a process in the Excel Import page as described below.

Access the Excel Import page

/fr/import/$app/$form
<property as="xs:string" name="oxf.fr.summary.buttons.acme.contact">
    home review pdf delete duplicate import new
</property>

Select the form version

[SINCE Orbeon Forms 2018.2]

If form definition versioning is supported, and if the given form definition has more than one version, you can select the form definition version. The dropdown menu shows all published versions for the given form name and application name.

Selecting a version shows the versioning comment, if any, associated with the version, as well as the number of data records available in the database for that version.

Upload the file

You navigate to the Upload section and select the Excel 2007 file to upload and import.

Validate the data

You navigate to the Validation section. From there, data validation starts automatically, giving you an indication of the progress.

With the excel-named-ranges and xml-form-structure-and-data formats, information about valid and invalid fields shows.

Import the data

NOTE: This step only applies to the excel-headings format.

Once validation is terminated, you navigate to the Import section. From there, you can start the data import. When using the Headings format, you have the option to add to existing data for the given form, or remove all existing data first.

Import takes place and gives you an indication of the progress.

NOTE: Only the Excel 2007 .xlsx format (Office Open XML) is supported. The older, .xls format is not supported.

Review the data

NOTE: This step only applies to the excel-named-ranges and xml-form-structure-and-data formats.

Before the data is saved to the database, you can review errors if any and decide to correct them or to perform a new import.

Form controls mapping

Excel Headings format

Excel file format

A given Excel file contains data for a single Orbeon Forms form.

The spreadsheet must follow this format:

  • only the first sheet is considered

  • the first row is a special header row, where each cell contains an identifier that matches a control name in the given form

  • each subsequent row contains data for a new instance of form data

In your form, you create controls with names that match the names in the first row (header row) of the Excel document.

Here is an example spreadsheet for the sample Orbeon Contact form:

NOTE: Only characters allowed in XML names are allowed as control names in Form Builder. In case your Excel header row requires names with non-XML characters (Form Builder will tell you the name is not allowed), simply replace them by "" in Form Builder._

Limitations

The import functionality with the excel-headings format does not support importing data into repeated grids.

Excel Named ranges format

You don’t have to export a form to Excel to import data: as long as you have a spreadsheet that includes the appropriate named ranges (see above), you can use it for importing. This means that you can reuse existing spreadsheets with a few additions, and keep a spreadsheet layout that users are familiar with.

XML Form Structure and Data format

When you import, the only part that matters is the <form-data> section of the XML document. The rest of the XML file is ignored.

Handling dates and times

[SINCE Orbeon Forms 2019.2]

Cells that contain values formatted as date/time, date, or time in the Excel spreadsheet are automatically converted to their respective ISO formats before being stored as form data.

Other configurations

Allowing invalid data

[SINCE Orbeon Forms 2017.2]

NOTE: This property only applies to the excel-headings format.

By default, invalid data rows are skipped during import.

You can enable the optional import of invalid data with the following property:

<property
    as="xs:boolean"
    name="oxf.fr.import.allow-invalid-documents.*.*"
    value="true"/>

By default, it is set to false and the user is not provided with an option to skip invalid data.

When set to true, the user is provided with an option to skip invalid data at the time of import:

Restricting to the latest version

[SINCE Orbeon Forms 2018.2]

The user can select which form definition version to import to.

You can disable the ability for the user to do so, and always automatically select the latest form definition version, with the following property:

<property
    as="xs:boolean"
    name="oxf.fr.import.restrict-to-latest-version.*.*"
    value="true"/>

Start Over button

By default, the "Start Over" button is not shown on the Import page. You can enable it with the following property:

<property 
    as="xs:boolean"
    name="oxf.fr.import.show-start-over-button.*.*"
    value="true"/>

Showing the application and form names

By default, the import page shows the application and form names. You can hide them with the following property:

<property 
    as="xs:boolean"
    name="oxf.fr.import.show-app-form-names.*.*"
    value="false"/>

Forwarding parameters to the data review page

NOTE: This property only applies to the excel-named-ranges and xml-form-structure-and-data formats.

This property allows you to forward specific URL parameters that were passed to the Import page to the data review page. By default, no parameters are forwarded. This example shows how to forward the foo and bar parameters:

<property 
    as="xs:string"
    name="oxf.fr.import.forward-parameters.*.*"
    value="foo bar"/>

Passing a document id to the data review page

NOTE: This property only applies to the excel-named-ranges and xml-form-structure-and-data formats.

You can pass a document id to the import page. This is useful if you want to propagate a document id to the data review page to update or merge with an existing document in the database. This example shows how to pass the document-id parameter:

/fr/import/orbeon/contact?document-id=123

Customizing the buttons in the buttons bar

This property allows you to configure the buttons at the bottom of the Import page. They are, by default:

<property 
    as="xs:string"
    name="oxf.fr.import.buttons.*.*"
    value="home summary close"/>

If, for example you don't want the summary button, then set the property to:

<property 
    as="xs:string"
    name="oxf.fr.import.buttons.*.*"
    value="home close"/>

See also

You start the import from the Form Runner Import page, accessible from the when the "Import" button is configured, or directly with the path:

You enable the "Import" button on the by adding the import token to the oxf.fr.summary.buttons.*.* property. Here for the ACME Contact form:

For more about the format, see .

For more about the format, see .

[SINCE Orbeon Forms 2021.1]
[SINCE Orbeon Forms 2021.1]
[SINCE Orbeon Forms 2021.1]
Summary page
Summary page
Excel and XML Export
Excel and XML Export
[SINCE Orbeon Forms 2020.1]
[SINCE Orbeon Forms 2021.1]
[SINCE Orbeon Forms 2021.1]
[SINCE Orbeon Forms 2021.1]
[SINCE Orbeon Forms 2021.1]
Excel and XML Export
Form definitions and form data Zip Export
Service calls