A Spreadsheet Editor for Lists of Documents in the Nuxeo Platform
The Nuxeo roadmap put a lot of emphasis on working with large collections of assets or documents, leading to the new collections module, as well as some upcoming updates around the user experience when selecting values in a content view.
One particular item of the roadmap focuses on the ability to edit data in your repository as if you were in a spreadsheet-like grid. Indeed, spreadsheet copy-paste by dragging the cell's value is something very intuitive and very productive for handling bulk-editing of thousands of documents. We want to somehow emulate this behavior. One of the challenges is that when you handle documents in a repository, you have to respect some constraints:
- Some metadata is mandatory,
- Some have constrained values among a controlled list, that store the id value while it displays the label,
- You may even have more complex validation logic,
- Sometimes some information is read-only, you don’t want to let the user modify it,
- This is not a constraint, but a must: you want to be able to use Nuxeo Studio for configuring the spreadsheet instance, mapping that configuration to the notion of result layout.
You need to make sure your user interface will be able to enforce all those constraints. There are two ways to implement a spreadsheet editor:
- Produce a clean Excel/Open Office /CSV export of the data, let the user do their modification in the desktop application, then re-import the new table updating the related documents in the repository.
- Have a built-in table-like solution, that will emulate the user interface of Excel, to edit the data.
I have a reported implementation of method 1 by one of our customers. They used the XML Excel format for producing a file that has some locked cells, controlled lists, etc … The production of the file is based on rendering logic that injects values in the initial template. They reported having issues making sure the Excel file was okay, with the correct protection of cells, etc … But, this is still doable, with some limitations on the kind of controls that can be presented to the user.
Another approach still based on exporting an Excel file could be to use advanced templates based on JXLS library. I am pretty sure we could produce an interesting template, here also with some limitations to the kind of widgets available for editing the data.
In short, what the library allows you to do is:
- Define the datasource that you map to a Nuxeo query (preferably using the PageProvider operation, that allows you to control the max result). It is possible to have advanced mappings (subfields to column).
- Define the columns that should be displayed.
- Define their type, the renderer to use for presenting the value, as well as the editor for editing. You can write your own renderers and editors. For instance, I have adapted and integrated a select2 editor (that was implemented by someone else and found on github). This means we can theoretically map all of our widget library.
- Define which columns are editable or not.
- Define formatters, so as to control the police, the size and the colors of the cell depending on some business rules, as well as if it should be displayed as numbers, etc…
- Benefit from the undo/redo user experience.
- Provide built-in search filtering capabilities.
- Register to any of the events that happen on the table, to have a tight link of your backed model to what the user does on the table.
What we should do to integrate it nicely into the Nuxeo Platform:
- Integrate the table to content views. It could be either another result layout (like the lightbox one is going to be) or just a new button on the content views template that displays it in a pop up.
- Implement a homogeneous set of editors and renderers mapped to the main widgets of our layout library.
- Plug the Handsontable instantiation definition to the layout service so as to be able to configure it from Studio.
- Implement pagination logic for fetching larger sets of data.
- Implement a robust bulk-save of the rows updates (currently, I call the REST API row by row, which actually happens to give a very good results already).
I start by fetching the data (the whole code is wrapped in a function with queryString param, so as to easily instantiate the table with query based content. Note the use of Document.PageProvider, as it is paginated, versus Document.Query, which would not scale for a large repository:
Then in the call back function of the execute method, I instantiate the Handsontable:
The main options I use are:
data: the data to be injected in the table, it is the direct data result of the Document.PageProvider operation
columns: an array of the definition of the columns type. Ex:
or a more advanced one that leverages the select2 editor:
You may notice the “data” mapping, with the json properties traversal. For select2, I use the automation API to fetch the values. Actually I have the same one for the JSF implementation of the suggest widget.
I also added an “afterChange” callback:
This allows me to store modified cells so I can save them for later and change the colors of the cells that have been updated, letting the user know what is going to be changed.
Category: Product & Development