Background
Hi All, I am building a new web based application for planning and financial reporting.
The reporting and planning tools are heavily spreadsheet orientated and requires a robust, yet flexible grid system to render the reporting and tools back in the UI in a logical format that the users are accustomed to (current baseline reporting systems = MS Excel and Google Sheets).
The Planning tools will be read & write, the reporting tools will be read only
I have spent the last 2 weeks exploring some of the options available and I initially began the MVP (for the planning workbench) with a very basic and light weight framework knowns as Mindmup EditableTable. It has served its purpose for the prototype, but its clear that it lacks more advanced features necessary for the reporting component of the project.
Other frameworks explored:
While doing some research, I came across the below frameworks
Both seem very well supported and based on the engagement here on Stack Overflow have active communities.
Questions
- What is the best jQuery / Javascript Grid solution to use for more advanced features
Best being defined as:
Easiest to deploy (from a code / development POV)
Most Robust (can handle larger datasets)
Quick (doesn't lag and have performance issues)
Advanced defined as:
Facilitates easy data binding / loading
Facilitates data persistence (ie. updating editable portions of grid, pushes data and updates the source)
Selected MS Excel type UI features (as described below in the sample data and pivot table example)
Sample of the Data
Raw Data
For the example in the pivot table below, the underlying data would look like this...
| Data | Financial Year | Quarter | Month | Customer | Product | Sales |
|---|---|---|---|---|---|---|
| 02/08/2023 | FY24 | Q1 | August | Acme Trading | Product 1 | 77,832 |
| 09/08/2023 | FY24 | Q1 | August | Acme Trading | Product 1 | 49,406 |
| 16/08/2023 | FY24 | Q1 | August | Acme Trading | Product 1 | 131,299 |
| 23/08/2023 | FY24 | Q1 | August | Acme Trading | Product 1 | 93,136 |
Pivot Table Example
An example of the capability I would be looking for in a pivot table is shown in the image below and is based on the dataset provided above.
Key features:
- Row Grouping
- Column Grouping
- Expand / Collapse individual Rows or Columns
- Aggregation of groups (ie. when you roll the structure up or down the the grouping should provide sub totals, dynamically recalculating as needed)
- Filtering capability
Ag-grid is a paid and much more polished product, I don't know it well, but it may well have pivoting built in. They have a comprehensve set of examples.
Slickgrid is more of a toolkit. You can definitely do the things you want, but there may be quite a bit of ancilliary coding to get there. It doesn't support pivot natively, although I have some pivot code I could provide. Essentially it's a display tool, and a great one, but it's quite agnostic about data. There are a lot of examples for Slickgrid as well - check them out. Disclaimer: I am a maintainer of Slickgrid.
EDIT
I've created a new example for pivoting: https://6pac.github.io/SlickGrid/examples/example-pivot.html
This is read-only, but you can see from the underlying code, in the original page I did allow editing of a specific column and then cherry pick it from the data and send it back from the server for persistence.
The real issue is the fact that pivoting completely restructures the data. I decided that it was best to keep all this on the client side, so what would be needed for full editing would be to update the original dataset with some 'un-pivot' operation and then send that back to the server. That's probably not too hard if you created a pivot-aware editor that would save the data intelligently to the right spot in the original data. I didn't have that requirement, so I didn't get that far, but I think it would only take a few hours to set that up.
If you do, let me know and I'll add it to the example.