AliIbnBotir opened a new issue, #45265: URL: https://github.com/apache/superset/issues/45265
## [SIP] Proposal for a Spreadsheet chart (Excel-like, editable) built on Univer ### Motivation Many Superset users still copy chart data into Excel to do the last mile of work: rename a header, add a total row, colour a few cells, write a formula next to the data, group rows with subtotals. They then send that file around instead of a Superset link. The Table, Pivot Table and AG Grid Table charts show data, but none of them lets a chart author shape the result like a spreadsheet and keep that shaping with the chart. A Spreadsheet chart would let authors do that last mile inside Superset. The result: - stays live, because the data refreshes on load; - shows on dashboards; - exports to Excel as it looks on screen. ### Proposed Change Add a new chart type, **Spreadsheet** (`viz_type: univer_sheet`, category Table). It is built on [Univer](https://github.com/dream-num/univer), an Apache-2.0 spreadsheet engine. #### User-facing behaviour - **In Explore**, the query result loads into a full spreadsheet editor: toolbar, formula bar, sheet tabs and context menu. The author can: - format cells, merge them and set number formats; - write formulas and rename headers; - add sheets, resize columns and insert rows. Edits are saved with the chart. **[drag `01-explore-editor.gif` here]** _Editing in Explore: rename a header, format cells, add a formula, save._ - **On dashboards and in embedded views**, the chart is a read-only sheet with the saved edits applied on top of fresh data. There is no toolbar and the sheet takes no focus. **[drag `02-dashboard.png` here]** _The same chart on a dashboard, read-only, with saved edits on fresh data._ - **Query modes:** - Aggregate: dimensions and metrics, like Table. - Raw records: columns, like Table. - Pivot: rows, columns, metrics and totals. This runs on the backend with the existing `pivot` and `flatten` post-processing, and shows stacked, merged header rows. **[drag `03-pivot.png` here]** _Pivot mode with stacked, merged column headers and totals._ - **Group rows by dimensions** (optional): Excel-style "Subtotal" groups. - Each group has a bold Subtotals row and can be collapsed with a +/- button. - Level buttons (1, 2, …) collapse every group to that level. - A Grand Total row goes at the bottom. - The collapsed state is saved with the chart. **[drag `04-row-groups.gif` here]** _Row groups: subtotals, collapsing with +/- and the level buttons._ - **Refresh data on load** (default on): the query result is rewritten into the top-left of the first sheet each time the chart loads. Formatting, formulas outside the data area, and other sheets are kept. - **Export to Excel** writes the sheet as shown: - renamed headers, formulas with cached values, styles, merges, column widths, frozen panes and pivot headers; - row groups as real Excel outline groups. "Export to full Excel" and CSV still export the raw query result. **[drag `05-excel-export.png` here]** _The exported .xlsx opened in Excel: formatting, formulas and outline groups kept._ #### Technical design **Frontend.** A new plugin, `superset-frontend/plugins/plugin-chart-univer`, registered in `MainPreset`. - Univer's open source packages, `@univerjs/presets` and `@univerjs/preset-sheets-core` (1.0.4, Apache-2.0), are normal dependencies. Univer Pro packages (`@univerjs-pro/*`) are **not** used. Pivot, row groups and Excel export are implemented in the plugin and backend instead. - The plugin uses `loadChart: () => import('../UniverChart')`, so Univer is only downloaded when a Spreadsheet chart renders. Bundle impact, measured with a production build of master: one lazy chunk of about 6.5 MB minified (1.7 MB gzipped) plus 119 KB of CSS. Nothing Univer-related is in the entry bundles; only the small control-panel code is. - All Univer calls are in one module (`src/univer/mountUniver.ts`), which the chart uses through a small handle (`save`, `setColumnWidths`, `dispose`). The grid, layout, saved-state and group logic is pure TypeScript with unit tests. **Saved state.** The control `univer_workbook` stores a versioned JSON string (`version: 1`) in the chart's `params`: - Univer's workbook snapshot; - the size of the data area; - the header layout; - the saved groups. With data refresh on, query values are **stripped** before saving, so the stored state holds only the user's edits and stays small. On load, the fresh result is written back into the data area. **Chart rendering context.** A small, generic change makes `ChartRenderer` pass `appSection` (Explore, Dashboard or Embedded) to `SuperChart`, so a plugin can tell where it renders. The Spreadsheet chart is editable only in Explore. `ChartProps.appSection` already exists; it just wasn't passed from `ChartRenderer`. **Backend Excel export.** In `QueryContextProcessor.get_data`, an XLSX request whose form data has `viz_type == "univer_sheet"` goes to `superset/utils/univer_excel.py` instead of `excel.df_to_excel`. All other charts are unchanged. - The module rebuilds the same sheet the browser shows (grid, pivot headers, groups, saved edits) and writes it with XlsxWriter, which is already a dependency. - It uses `openpyxl`'s formula translator for shared formulas. `openpyxl` is already installed through `pandas[excel]` and becomes a direct dependency. - Explore sends the current, unsaved form data, so the export includes unsaved edits. Dashboards and reports use the saved chart. **Bug fix found along the way.** `flatten` post-processing reset only the first level of a MultiIndex, so a pivot with two or more index columns lost columns. This is a separate PR. ### New or Changed Public Interfaces - New viz type `univer_sheet`, with its form-data keys: `univer_workbook`, `univer_live_data`, `univer_show_headers`, `univer_row_grouping`, `univer_pivot_rows`, `univer_pivot_columns` and `univer_pivot_totals`. - `ChartRenderer` passes `appSection` to plugins. - No REST API changes, no new endpoints and no DB migration. ### New dependencies - **Frontend:** `@univerjs/presets` and `@univerjs/preset-sheets-core` 1.0.4. They add 93 packages to the lockfile, all with ASF Category A licences: 53 MIT, 28 Apache-2.0, 10 BSD-3-Clause, 3 ISC. Notable transitive packages are Radix UI primitives, `@wendellhu/redi` (Univer's DI container), `protobufjs` and `@grpc/grpc-js` (from Univer's RPC package, not used in the browser path). - **Python:** `openpyxl` is declared directly (`>=3.1.5, <4`). It is already in `requirements/base.txt` via pandas. ### Migration Plan and Compatibility The chart is purely additive and needs no migration. Existing charts and exports are unaffected. The saved state carries a `version` field so its format can change later without breaking saved charts. Feature flag: this proposal registers the chart unconditionally. If the community prefers, it can sit behind a flag such as `SPREADSHEET_CHART` until it has had wider use, the way the AG Grid table started. ### Rejected Alternatives - **Univer Pro** (pivot, outline, xlsx exchange): needs a paid licence, which is not acceptable for an ASF project. - **Browser-side Excel export (exceljs):** this was built and rejected. A backend export reuses Superset's chart-data path, permissions and config, and also works for dashboards, reports and alerts. - **Extending the Table / AG Grid charts:** they are grids, not spreadsheets. Formulas, cell formatting, multiple sheets and saved per-cell edits would have to be built from scratch. - **Shipping it as an external plugin only:** possible, and a fallback if the community prefers not to add it to core. But the Excel export needs the small backend hook, and the `appSection` change is generic. ### Known Limitations - Totals, subtotals and the Grand Total are sums, so they are not meaningful for AVG, MIN or MAX metrics. - Groups follow the order values first appear in the query result. - Rich text in a cell exports as plain text. - The sheet layout logic exists in TypeScript (for display) and Python (for export). Both have unit tests over the same cases. A shared fixture test can keep them in sync. ### Implementation PRs 1. `fix(post-processing): flatten every index level`: independent bug fix, already open as #45263 2. `feat(explore): pass appSection to chart plugins` 3. `feat(plugin-chart-univer): add a Spreadsheet chart built on Univer` 4. `feat(chart-data): export the Spreadsheet chart to Excel with its edits` Reference branch: https://github.com/AliIbnBotir/superset/tree/feat/univer-chart-upstream -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
