Excel export and import

Introduction

Orbeon Forms 2021.1 includes a new feature to import and export Excel files.¹

Previous Orbeon Forms versions already supported an Excel import feature. Here are the main differences between the two types of Excel imports:

The new document-based import feature does not replace the previous one but complements it. Each feature might be improved in the future.

The Excel export feature, on the other hand, is entirely new.

Scenarios

This 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 import 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.

In addition, 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 below), 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.

The export feature

The Excel export feature allows you to export a form definition to an Excel file. The Excel document contains all the fields of the form. Say you have the following form in Form Builder:

Example of form in Form Builder

Form Runner attempts to mirror the layout of the original form and includes section titles and field labels as well.

Example of exported form

Named ranges

The Excel file format supports so-called named ranges. This allows you to give one or more cells a name. Orbeon Forms uses this feature to assign every cell, or, in the case of repeated grids, groups of cells, a name which matches² the control name assigned in Form Builder. This also allows reimporting data in those cells.

Repeated grids

Repeated grids are supported. When repeating over more than one row, they are flattened to show one Excel row for reach repetition.³

Here is how the repeated grid appears in the Excel document:

Selection controls

Selection controls are represented with a list of items above the field. The user can then enter the value in the field for import.⁴

The import feature

How it works

You use the same Excel Import page as before. The page is configured to support the document-based Excel import. You start by uploading the document.

The uploaded document then goes through validation, and a summary is provided. In this case, there is a missing required field.

Finally, you have the opportunity to review the entire data before saving it. The user can choose to fix the error directly in the form and then save, or can fix the error in the Excel file and start over with the import process.

Cells in the Excel document are matched with the form’s cells using the named ranges. If you use an Excel document that has been exported by Orbeon Forms, then the named ranges are already included. But you can also modify the document produced by Orbeon Forms, or create your own spreadsheet from scratch. You just need to include the proper cell names that you need. You can even have field values that are calculated in your Excel document, although this is not generated by Orbeon Forms, and the result will be imported.⁵

Limitations

As available in Orbeon Forms 2021.1, the new Excel import and export feature has the following limitations:

• no 24-column mode
• no dynamic list of choices (for example dynamic dropdowns)
• no calculations or validations are exported
• no nested repeats
• repeated grids or sections are always flattened
• no section templates support
• some form controls are not supported, including
  • Formatted Text
  • Handwritten Signature
  • Attachments

Conclusion

We hope you will like this new and improved Excel import and export feature! It will be available with Orbeon Forms 2021.1 PE.

For more, see also the documentation:

• Excel and XML Import
• Excel and XML Export