AX1640

Editing grid contents in a spreadsheet editor

You can provide form users with a spreadsheet-style editor to edit the contents of a Formatted Grid component. This spreadsheet editor opens as a modal dialog in the browser, so users do not have to exit the current form in order to make spreadsheet edits. When the user is done making edits, the edits are posted back to the original grid in the form.

In the spreadsheet editor, users can do the following:

  • Copy and paste values from another spreadsheet to the grid (and vice versa)
  • Cut, copy, and paste values within the grid
  • Drag to copy and fill values across multiple cells
  • Use formulas to calculate values (though only the result value will be posted back to the grid in the form; the formula is not preserved)

Requirements and limitations

  • You can enable spreadsheet editing by using a Button component that is configured to use the Edit Grid Data in Spreadsheet button behavior. Button tags for Formatted Grid components can also use this behavior.

  • The Edit Grid Data in Spreadsheet button does not trigger the form update cycle. The current grid values are read from the form (including any unsubmitted changes) and then rendered in the spreadsheet editor. When the user closes the editor, the display of the form grid is updated for changed values, but these changed values are not submitted back to the source file (even if the grid is configured to auto-submit).

  • When the grid contents are displayed in the spreadsheet editor, the contents start at A1 and continue to the last cell displayed in the grid. Blank cells in the grid display as blank cells in the spreadsheet editor, including entirely blank rows and columns.

  • Only unlocked cells and cells with interactive controls are eligible to be edited in the grid. However, when the grid is opened in the spreadsheet editor, any cell can be changed in the spreadsheet editor. The spreadsheet editor does not provide any indication of which cells are eligible to be edited. When the user closes the spreadsheet editor to submit the changes back to the grid, only the changes to eligible cells will be posted back to the grid—any other changes will be lost.

  • Interactive controls such as drop-down lists and check boxes do not display in the spreadsheet editor. Instead, the current input or selected value is displayed as regular text and can be edited. For example, if a cell contains a Checkbox tag in the source file, and the interactive check box is currently selected in the form grid, that cell displays as 1 in the spreadsheet editor.

  • The spreadsheet editor does not display any formatting other than number formatting. Shading, bold, underline, etc. do not display. Special display elements such as symbols and sparkline charts do not display.

  • The user cannot add new rows or columns using the spreadsheet editor. Only the existing grid contents can be edited. If any content is added in a new row or column, it is lost when the user closes the spreadsheet editor.

  • When the Edit Grid Data in Spreadsheet button is clicked, the spreadsheet editor is opened in a system controlled dialog. Currently it is not possible to configure the size of the dialog or any other display properties of the dialog.

Setting up a button for spreadsheet editing

To configure a button to launch the spreadsheet editor, add the Button component to the Axiom form canvas and then configure the properties as follows:

  • Set Button Behavior to Edit Grid Data in Spreadsheet.
  • Set Formatted Grid to the name of the Formatted Grid component that you want to open in the spreadsheet editor. The Formatted Grid property is only visible once the button behavior has been set to Edit Grid Data in Spreadsheet.

All other visible button properties can be set as desired. Note that it is not possible to run a command or to save data to the database when using this button behavior.

Button tags for Formatted Grids can also use this button behavior. You may want to put the button in the grid itself instead of using a separate Button component. The Tag Editor and Data Source Assistant allow selecting this button behavior and specifying the Formatted Grid component to open. However, in this case you must manually type the name of the desired Formatted Grid component—the tag helpers do not have a drop-down list that allows you to choose component names.

Example

The following example form contains a Formatted Grid component where users can enter hours spent on a project. The form contains a button that is set up with the Edit Grid Data in Spreadsheet behavior, where the target is the hours grid.

Example grid to open in editor

When the user clicks the button, the contents of the target form are opened in the spreadsheet-style editor, in a modal dialog. The user can edit cells, copy/paste from Excel, or drag to copy or fill. In this example, the user has entered some time into the initial week of the project.

User modifies values in the spreadsheet editor

NOTE: The spreadsheet editor does not provide any indication of which cells are editable, and does not prevent edits in locked cells. If an edit is made in a locked cell, that edit is ignored when changes are posted back to the grid in the form.

When the user clicks OK in the spreadsheet editor, the editor is closed and the changes are posted back to the Formatted Grid component. In this example, the hours added to Week 1 are now reflected in the grid.

Changed values from the spreadsheet editor are posted to the grid

Notice that the totals in the last column have not updated for the added hours. This is because using the spreadsheet editor does not trigger a form update. The new values have not yet been submitted back to the source file, so the formula has not updated for the new values. In this example, a Refresh button has been provided on the form to update the grid for the new values.