hughhhh commented on code in PR #41133:
URL: https://github.com/apache/superset/pull/41133#discussion_r3591451728
##########
superset/dashboards/api.py:
##########
@@ -291,6 +305,10 @@ class DashboardRestApi(
method_permission_name = {
**MODEL_API_RW_METHOD_PERMISSION_MAP,
"restore": "write",
+ # Reuse the dashboard ``can_export`` permission (the frontend gates the
+ # menu item on it) instead of the ``can_export_xlsx`` FAB would
otherwise
+ # derive from the method name.
+ "export_xlsx": "export",
Review Comment:
The 401 was a bug in the test's login, not the permission mapping — fixed in
`5ac547a3d8`. `temporary_user(login=True)` was creating a passwordless user, so
FAB's login raised on `check_password_hash(None)` and the request arrived
**unauthenticated (401)**, not the **403** a permission mismatch would produce.
It now clones the Gamma user, which already carries dashboard `can_export`.
The mapping does take effect. In `flask_appbuilder/api/__init__.py`,
`get_method_permission()` is:
```python
if self.method_permission_name:
return self.method_permission_name.get(method_name, method_name)
```
When `method_permission_name` is set (it is on `DashboardRestApi`), FAB uses
the map and ignores any `@permission_name` decorator — so `export_xlsx` →
`"export"` → `can_export`, and `base_permissions` gets `can_export`, not
`can_export_xlsx`. This is the same path as the existing `"restore": "write"`.
It's also asserted by `test_info_security_dashboard`, whose exact-set (`==`)
permission check passes with `can_export` present and `can_export_xlsx` absent
— which it couldn't if the fallback to `can_export_xlsx` were still happening.
##########
superset/utils/excel_streaming.py:
##########
@@ -0,0 +1,254 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+"""
+Streaming XLSX writer for multi-sheet dashboard exports.
+
+Unlike :mod:`superset.utils.excel`, which builds an in-memory DataFrame per
+sheet and hands the whole thing to ``xlsxwriter`` at once, this writer opens
the
+workbook in ``constant_memory`` mode and writes rows one at a time, so
+``xlsxwriter`` keeps at most one row per sheet buffered on the writer side. The
+source records may still be materialized upstream (e.g. by the chart query
+response); this bounds only the writer's own footprint, not the caller's.
+"""
+
+from __future__ import annotations
+
+import math
+import numbers
+import re
+from collections.abc import Iterable, Sequence
+from datetime import date, datetime
+from decimal import Decimal
+from io import BytesIO
+from typing import Any
+
+import xlsxwriter
+
+from superset.utils.excel import NEUTRAL_DOCUMENT_PROPERTIES
+
+# Excel limits a sheet name to 31 characters and forbids these characters.
+MAX_SHEET_NAME_LEN = 31
+_INVALID_SHEET_CHARS_RE = re.compile(r"[\[\]:*?/\\]")
+# Excel reserves the sheet name "History" (case-insensitive).
+_RESERVED_SHEET_NAME = "history"
+
+# A worksheet holds at most 1,048,576 rows; one is reserved for the header.
+MAX_DATA_ROWS_PER_SHEET = 1_048_576 - 1
+
+# Leading characters that turn a cell into a formula in spreadsheet apps.
Mirrors
+# superset.utils.excel.quote_formulas so streamed exports get the same guard.
+_FORMULA_PREFIXES = {"=", "+", "-", "@"}
+
+# Excel cannot represent integers beyond 10**15 without precision loss.
+_MAX_EXCEL_INT = 10**15
+
+
+def _quote_if_formula(text: str) -> str:
+ """
+ Prefix formula-like text with an apostrophe so spreadsheet apps treat it as
+ literal text (defense against formula injection).
+
+ Leading whitespace is ignored when detecting a formula, because spreadsheet
+ apps still evaluate a cell whose formula prefix is preceded by spaces or
+ tabs (e.g. ``" =cmd"`` or ``"\\t=cmd"``).
+ """
+ stripped = text.lstrip()
+ return f"'{text}" if stripped and stripped[0] in _FORMULA_PREFIXES else
text
+
+
+def _coerce_float_cell(value: Any) -> Any:
+ """
+ Convert a ``Decimal``/real value to something ``xlsxwriter`` accepts.
+
+ ``float()`` on a non-finite ``Decimal`` ("NaN"/"Infinity") yields a value
+ xlsxwriter rejects, and an over-large value can raise ``OverflowError``;
+ blank the former and stringify the latter, and stringify magnitudes Excel
+ cannot represent precisely.
+ """
+ try:
+ number = float(value)
+ except (OverflowError, ValueError):
+ return str(value)
+ if not math.isfinite(number):
+ return ""
+ return str(number) if abs(number) > _MAX_EXCEL_INT else number
+
+
+def sanitize_sheet_name(raw: str, used: set[str]) -> str:
+ """
+ Produce a valid, unique Excel sheet name from ``raw``.
+
+ Replaces forbidden characters, strips surrounding apostrophes/whitespace,
+ avoids the reserved name "History", truncates to 31 characters, and
+ disambiguates case-insensitive collisions with ``~2``/``~3`` suffixes.
+ The chosen name (lower-cased) is added to ``used``.
+
+ :param raw: The desired sheet name (e.g. ``"42 - Sales by Region"``)
+ :param used: Lower-cased names already taken; mutated with the result
+ :returns: A sanitized, unique sheet name no longer than 31 characters
+ """
+ name = _INVALID_SHEET_CHARS_RE.sub("_", raw or "")
+ name = name.strip().strip("'").strip()
+ if not name:
+ name = "Sheet"
+ if name.lower() == _RESERVED_SHEET_NAME:
+ name = f"{name}_"
+ name = name[:MAX_SHEET_NAME_LEN]
+
+ if name.lower() not in used:
+ used.add(name.lower())
+ return name
+
+ suffix = 2
+ while True:
+ marker = f"~{suffix}"
+ candidate = name[: MAX_SHEET_NAME_LEN - len(marker)] + marker
+ if candidate.lower() not in used:
+ used.add(candidate.lower())
+ return candidate
+ suffix += 1
+
+
+def _sanitize_cell(value: Any) -> Any:
+ """
+ Coerce a single cell value into something safe for ``xlsxwriter``.
+
+ Quotes formula-like strings (defense against formula injection),
stringifies
+ integers/floats Excel cannot represent precisely, renders temporal values
as
+ ISO strings (timezones are not natively supported), and blanks out ``None``
+ and non-finite floats.
+ """
+ if value is None:
+ return ""
+ # bool is a subclass of int; preserve it before the numeric branches.
+ if isinstance(value, bool):
+ return value
+ if isinstance(value, str):
+ return _quote_if_formula(value)
+ if isinstance(value, (datetime, date)):
+ return value.isoformat()
+ if isinstance(value, Decimal):
+ return _coerce_float_cell(value)
+ if isinstance(value, numbers.Integral):
+ number = int(value)
+ return str(number) if abs(number) > _MAX_EXCEL_INT else number
+ if isinstance(value, numbers.Real):
+ return _coerce_float_cell(value)
+ # Anything else (lists, dicts, custom objects) is stringified, still
guarding
+ # against formula injection on the resulting text.
+ return _quote_if_formula(str(value))
+
+
+class StreamingXlsxWriter:
Review Comment:
Fair point on memory, and you're right that `ChartDataCommand.run()`
materializes each chart's rows — the module never claimed to fix that (the
docstring says it "bounds only the writer's own footprint, not the caller's").
What `constant_memory` still buys is separate from the source data: without it,
`xlsxwriter` retains **every written cell across all sheets** until `close()`.
A dashboard export is inherently multi-sheet, so bounding the writer's
accumulation is a real win even though each chart's source rows are transiently
materialized one at a time.
On reuse: `superset.utils.excel.df_to_excel` is single-sheet and
pandas-`DataFrame`→`BytesIO`; this path needs many sheets streamed to a temp
file for S3. Beyond multi-sheet, the new module adds things `excel.py` doesn't
have and that a dashboard (arbitrary user-named charts) requires:
- **Sheet-name sanitization + de-dup** — Excel's 31-char limit, forbidden
`[]:*?/\`, the reserved "History" name, case-insensitive collision suffixes.
pandas would raise or silently overwrite on invalid/duplicate names.
- **Stricter cell coercion** — `Decimal` NaN/Inf, ints > 10^15, tz-aware
datetimes, and formula-injection quoting that `lstrip`s first (so `" =cmd"` /
`"\t=cmd"` are caught — `excel.py`'s `quote_formulas` only checks `x[0]` and
misses those).
It already reuses what's shareable from `excel.py`, and I've just
consolidated the formula-prefix set into a single `FORMULA_PREFIXES` constant
there (`54ef804de4`) so both writers guard the same vectors from one source of
truth (`NEUTRAL_DOCUMENT_PROPERTIES` was already shared). Reusing `df_to_excel`
wholesale would mean building a DataFrame per chart — reintroducing exactly the
materialization you flagged — and still wouldn't cover multi-sheet-to-disk or
the sheet-name handling.
--
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]
