EnxDev commented on code in PR #41133: URL: https://github.com/apache/superset/pull/41133#discussion_r3590092386
########## 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: @hughhhh the @betodealmeida 's question seems to be still open. Since `ChartDataCommand.run()` already loads all rows into memory, the new streaming module doesn't provide constant-memory exports. If that's the case, why introduce a new module instead of reusing `superset.utils.excel`? I think this should be answered before merging. ########## 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: `method_permission_name["export_xlsx"] = "export"` doesn't seem to take effect. Users with only the can_export permission still receive a 401, while the frontend shows the Export to Excel action to those same users. The existing test (test_export_xlsx_admitted_with_can_export_only) is failing for this reason. @hughhhh would make sense to verify that the endpoint is actually enforcing can_export instead of falling back to `can_export_xlsx`? -- 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]
