Oracle APEX - IG Spreadsheet View Plugin

Sharing a plugin enabling you to add a Spreadsheet View to Interactive Grids. Offering the end user an extra editing view for fast data editing and support for copy-and-paste to and from Excel.

The Spreadsheet View has next features:

• support for editors Text, Number, Date Picker, Select List, Select One and Simple Choice columns: Checkbox, Radio, Switch, Pill Buttons

• typical spreadsheet editing features like type-to-replace, F2-edit, multi-line copy and paste, 'drag' edit, etc

• copy-and-paste to/from Excel

• undo / redo multi-level

• 'edit on focus' option

• scroll/page pagination (with instant switch)

• aggregates

• event handlers for programmatic support

• and many other features like add/delete rows, change / issue marking, column groups, load all, filter changes, search, show highlighting, maximize dialog, DA 'Synchronize' event, translation, etc.

Not all IG columns are supported, most notably (columns will be Read-Only):

• Columns involved in Cascading List of Values

• Multi-value columns

For Popup LOV column, Select One widget is used.

Where the IG native Grid View offers edits in a very controlled way, the Spreadsheet View is a more free style, optimistic editing view, enabling a faster interaction, where validation is afterwards.

The Spreadsheet View opens from a button in the IG toolbar:

and shows in a modal dialog. Upon opening, a copy of the IG model data is taken for editing in the Spreadsheet View. Upon finishing the editing, the changes are synchronized back into the IG model, from which they show automatically in the IG Grid. The saving of the changes will only happen in the IG Grid by using the save button.

Issue / change marking, selection:

Upon 'OK', the changes are synchronized to the IG Grid:

The IG Spreadsheet View is also having a 'Synchronize' button in the toolbar which can be used to synchronize with the IG Grid in between without closing the dialog. Like that, any model logic / validations which you have coded gets executed so those results also gets visible in the Spreadsheet View. So the Synchronize is two-way in that sense.

Copy-and-paste to Excel:

Copy-and-paste new rows from Excel into Spreadsheet View:

For simple IG use cases like editing Master Data, the Spreadsheet View can be used as-is or with minor adjustments. For more complicated use cases where (business) logic is involved and maybe server interaction, an additional effort might be required. This is because IG Spreadsheet View is not firing any ('on change') Dynamic Actions as that will be incompatible with the free editing style. A user can potentially first select all cells in the Spreadsheet View, hit 'delete' and then copy/paste updated data from an Excel sheet. It will be impossible to fire DA's with possibly server interaction. Solutions here are to delegate logic to the model layer, and for server interaction, do this upon the 'Synchronize' event for all modified rows. I'm offering some additional tooling for this. Details on those solutions you find on the github project page. Notice, if things are well, these solutions are not needed to be sure your data will be correctly saved as your current server-side logic (page processes, db logic) will already take care of that.

An Excel-like editing feature is regularly requested - I'm also aware using a plugin is not feasible for everybody, and hopefully one day it will become a native feature in APEX. It was already announced that in APEX 26.1, copy-and-paste from Excel (or clipboard in general) to IG Grid will be supported.

Project: github
Demo: example page

The plugin makes use of JSpreadsheet CE, a popular open-source lightweight spreadsheet framework component.