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.

  • Limited formatting is applied to the exported data. For more information, see Formatting applied to exported grids.

  • Grid contents that are the result of content tags are converted to values when possible. For more information, see Treatment of content tags in exported grids.

  • 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.

  • Grid export is not supported for use on tablets.

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.

Using a Button tag in a Formatted Grid component

Button tags in thematic Formatted Grid components can also be configured to run this command. In this case, use the Command parameter within the tag to assign the command to the button. The easiest way to do this is to use the Tag Editor dialog or the Data Source Assistant to create the tag and edit the tag parameters. When using these helper dialogs, you can select the command and configure the shortcut parameters using the same method described previously for the Button component.

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:

=If(Control_Form!D23="On","NoColumn","[Column]")

Where Control_Form!D23 is the cell address of the Is Excel Export setting on the Form Control Sheet. The column will be visible normally, but when exporting grid contents the column will be hidden.

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.

Formatting applied to exported grids

When you export a formatted grid to a spreadsheet, the following formatting is applied to the spreadsheet contents. This formatting is based on the styles and other format settings defined in the Grid data source.

Format Export Notes

Row height

The row height in pixels is converted in the spreadsheet as follows: Pixels x .75. For example, a row height of 30px is converted to 22.5.

If the row height in the Grid data source is set as a percentage, the export uses the resulting pixel height in the form and then converts it as stated above.

Column width

Column widths in the Grid data source are converted as follows:

  • Column width in pixels is converted in the spreadsheet as follows: Pixels/8. For example, a column width of 100px is converted to 12.5. If the column width is defined using a range of pixels, the upper limit of the range is used for the export.

  • Column width in percent (single value or range) uses autofit in the spreadsheet.

Background color

No notes.

Font color, size, and weight

Font weight is either bold or normal.

Borders

If a border is used but no border color is defined in the style, the default border color in the spreadsheet may be different than the default border color applied by the skin used by the form. However, if a border color is defined in the style, that specific color will be used in the spreadsheet.

Number formats The number format of the cell in the Grid data source is applied to the spreadsheet.

Any other grid formatting is not applied to the spreadsheet contents.

Treatment of content tags in exported grids

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

Tag Export Result

Button

Button text

Checkbox

Checked status (True or False)

DatePicker

Selected date as Excel date serial number

NOTE: 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

NOTE: If the format tag is configured to span multiple columns, and the target cell is within those spanned columns, the target cell text will display in the exported grid. You can work around this by moving the target cell to a column that is not tagged for display in the grid.

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

NOTE: If the content tag is configured to span multiple columns, this does not result in merged cells within the exported grid. The converted contents of the content tag will display in the starting cell.

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