Update charts after programmatically changing cell values

[ianscriven]

I'd like to be able to update charts in the spreadsheet after making changes to cell values in my code.

At the moment, the charts only update when the user makes changes to data.

I'd like a method that forced all charts to be redrawn/reloaded.

At the moment I've got a work-around where I extend Spreadsheet to make Spreadsheet#fireEvent(EventObject) publicly accessible, then manually firing CellValueChangeEvents, but this requires me to know the cells that contribute to the charts I want to update.

[vlukashov]

Hi @ianscriven! To force reload all charts on the current sheet you could use spreadsheet.setActiveSheetIndex(spreadsheet.getActiveSheetIndex()); without extending the Spreadsheet class on your own. That one-liner triggers full sheet reload (including styles, custom components, charts, etc) without you needing to specify the changed cells.

But I think we could do better than that in the future Spreadsheet versions. One idea that's being considered now is that server-side changes to a spreadsheet would trigger change events in the same way as client-side changes currently do.

Can you please share your usage scenario? Which API methods do you use to make changes to the spreadsheet on the server?

[ianscriven]

I had a different process (non-user) that was making changes to the underlying workbook (using POI directly) then calling Spreadsheet#refreshCells (or refreshAllCellValues) to show the updated values, but this didn't update the charts.

[vlukashov]

Thanks for the explanation!

Currently Vaadin Spreadsheet is not designed to monitor changes to the underlying POI workbook. It looks like in your scenario you would need to force reload either a single sheet or the entire workbook to make sure the changes are reflected into Vaadin Spreadsheet.

The method to reload the entire workbook is spreadsheet.reload(). It does a full spreadsheet reload and re-render, which might take noticeable time.

The way to reload a single sheet in the workbook is the one-liner I've mentioned earlier: spreadsheet.setActiveSheetIndex(spreadsheet.getActiveSheetIndex());. It is faster than reloading the entire workbook, but still might take noticeable time on large spreadsheets.