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.
For method 2, I found a library that could be the basis of our future implementation of https://jira.nuxeo.com/browse/NXROADMAP-159: handsontable.js. This awesome library implements the spreadsheet user experience in a JavaScript grid and provides all the plugability you would expect from such a component.
I have played with it a little bit using the nuxeo.js JavaScript client - the result is on github (pardon my JavaScript ;)) and I did a short video to share the result:
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.
- Plug the data of the Handsontable to the state of the content view: user filters data on the content view, then extracts the result into the JavaScript grid to edit it.
- 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).
As you can see, this is a full time job, but this first integration attempt is already promising! If you are curious for some more “hands-on” details, here are some of the interesting parts. I use the Nuxeo JavaScript client, so I need to instantiate it:
<br />var connectInfo = {<br />baseURL: "http://demo.nuxeo.com/nuxeo",<br />username: "Administrator",<br />password: "Administrator",<br />timeout: 300000,<br />schemas: "dublincore"<br />};<br />var client = new nuxeo.Client(connectInfo);<br />
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:
<<br />client.operation('Document.PageProvider') .params({ query: queryString, maxResults:"500", pageSize:"500" }) .execute (…)<br /><br />
Then in the call back function of the execute method, I instantiate the Handsontable:
<br />$("#example2").handsontable(handsonOptions); // #example2 is a div in the page, handsonOptions is an array with the options.<br />
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:
<br />columns: [ {<br />data: "properties.dc:title",<br />readOnly: true,<br />renderer: orangeRenderer<br />},<br />
or a more advanced one that leverages the select2 editor:
<br />{<br />data: "properties.ipr:media_type",<br />editor: "select2",<br />select2Options: { // these options are the select2 initialization options<br />formatResult: getDefaultLabel,<br />query: function (query) {<br />getVocabularyData("dam_media_type", function (data) {<br />var results = [];<br />query.callback({<br />results: data<br />});<br />});<br />},<br />dropdownAutoWidth: true,<br />allowClear: true,<br />width: "resolve"<br />}<br />}<br />
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.
<br />function getVocabularyData(directoryName, callback) {<br />client.operation("Directory.SuggestEntries").params({<br />"directoryName": directoryName,<br />"localize":true,<br />"lang”:"en"<br />}).execute(function (error, data) {<br />callback(data)<br />});<br />}<br />
I also added an “afterChange” callback:
afterChange: function (change, source) {<br />if (change != null) {<br />for (var i = 0; i < change.length; i++) {<br />listToUpdate[(myData[change[i][0]].uid)] = myData[change[i][0]];<br />}<br />ht.render();<br />}<br />}<br />
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.
You should have a deeper look at the Handsontable documentation for discovering all that can be done. You can also refer to the Nuxeo JavaScript client documentation to understand how to add more interactions with the repository. Do not hesitate to share your experiences here!