AX1300

Exporting Formatted Grid contents to a spreadsheet

You can set up a form so that users can export the contents of a Formatted Grid component to a spreadsheet file. This might be done as a substitute for printing the form, or to allow users to perform further manipulations of the data within a spreadsheet.

Requirements and limitations

• You can enable export for a grid by using a Button component that is configured with the Export Grid command. Only one grid can be exported per command button.

• The form update and refresh process still occurs when the export button is used, however, the Axiom form itself is not updated when the process is complete. The purpose of the update is simply to prepare the grid for export. However, you should be aware that the refresh is occurring and disable Axiom queries as necessary so that they do not change the grid contents unintentionally. The Is Excel Export setting on the Form Control Sheet is automatically set to On when the process begins, and then set back to Off when the process is complete.

• Only the contents of the Grid data source are exported. Formulas are converted to values. Configuration details set on the Formatted Grid component itself are ignored, such as the title bar and overall component border.

• No formatting is applied to the exported data, other than honoring number formats.

• Once the grid contents have been exported to a spreadsheet file, that file is downloaded to the browser. The file name is the name of the formatted grid. The browser prompts the user to save or open the file. The specific behavior of this download prompt depends on the browser used.

• The export is not supported for use on iPad or other tablets.

If the grid contains content tags, these tags will be converted to values as follows:

Tag	Export Result
Button	Button text
Checkbox	Checked status (True or False)
DatePicker	Selected date as Excel date serial number (you must format the target cell as a date in order to show this value as a date in the exported data)
Format	Display text
Href	Display text (not a clickable hyperlink)
Select	Label for selected value
Sparkline	Blank cell
Symbol	Not supported for export; Symbol tag is returned
TextArea	Inputted text

If desired, you can modify the grid for export so that unsupported tags are not included. See Modifying the grid for export.

Setting up a button for grid export

To export the contents of a formatted grid to a spreadsheet, you must use a Button component that is configured with the Export Grid command. When the user clicks the button, the contents of the specified formatted grid will be exported.

To start off, add the Button component to the Axiom form canvas and then configure the properties as desired. The button text should be defined as something like "Export to Spreadsheet". You can then configure the Command for the component as follows:

1. In the Button component properties, click the [...] button to the right of the Command box.

2. In the Shortcut Properties dialog, click the [...] button to the right of the Shortcut Target box.

3. In the Axiom Explorer dialog, navigate to the Command Library. Select the Export Grid command, then click Open.

   The Export Grid command is now listed as the shortcut target, and the relevant shortcut parameters are now available.

4. In the Shortcut Parameters, for Grid Name, type the name of the component that you want to export.

   

   Example Shortcut Properties dialog

The button can now be used to export the contents of the specified formatted grid.

Modifying the grid for export

If desired, you can modify the grid before its contents are exported, by using dynamic formulas that reference the Is Excel Export setting on the Form Control Sheet. For example if the grid contains a column with Symbol tags, then you may want to omit that column from the export, or change the contents of those cells.

When the button with the Export Grid command is clicked, the following occurs:

• The Is Excel Export setting is toggled to On.
• Updated values from the form are submitted back to the source file, and the normal refresh process occurs. This allows the grid to dynamically adjust for the export, by using formulas that reference the Is Excel Export setting. Once the refresh is complete, the grid is exported to a spreadsheet file.
• The Is Excel Export setting is toggled back to Off, and the file is calculated so that any formulas referencing the setting will adjust as appropriate. Ideally, this restores the file back to the state it was in before the export process began.
• The Axiom form is not updated at the end of the process. This is because the purpose of the refresh is to prepare the grid for export, not to impact the display of the current form.

For example, if you want to hide a column that contains symbols for purposes of the export, you could use a formula such as the following to define the column tag:

The Is Excel Export setting should be used instead of the Triggering Component to impact the grid contents, because the export setting will be disabled at the end of the process, whereas the triggering component stays set until another component triggers an update. Using the triggering component can cause the grid contents in the source file to become out of sync with the grid contents presented in the Axiom form.