| 1 | """Minimal workbook generation for native chart data.""" |
| 2 | |
| 3 | from __future__ import annotations |
| 4 | |
| 5 | import io |
| 6 | import zipfile |
| 7 | from typing import Any |
| 8 | |
| 9 | try: |
| 10 | from xlsxwriter import Workbook as XlsxWriterWorkbook |
| 11 | except ImportError: # pragma: no cover - optional compatibility enhancement |
| 12 | XlsxWriterWorkbook = None |
| 13 | |
| 14 | try: |
| 15 | from openpyxl import Workbook as OpenpyxlWorkbook |
| 16 | except ImportError: # pragma: no cover - optional compatibility enhancement |
| 17 | OpenpyxlWorkbook = None |
| 18 | |
| 19 | from ..drawingml.utils import _xml_escape |
| 20 | from .marker_common import _excel_col |
| 21 | |
| 22 | |
| 23 | def _xlsx_cell_ref(row: int, col: int) -> str: |
| 24 | return f"{_excel_col(col)}{row}" |
| 25 | |
| 26 | |
| 27 | def _xlsx_cell(value: Any, row: int, col: int) -> str: |
| 28 | if value is None: |
| 29 | return "" |
| 30 | ref = _xlsx_cell_ref(row, col) |
| 31 | if isinstance(value, (int, float)) and not isinstance(value, bool): |
| 32 | return f'<c r="{ref}"><v>{value}</v></c>' |
| 33 | return ( |
| 34 | f'<c r="{ref}" t="inlineStr"><is><t>{_xml_escape(str(value))}</t></is></c>' |
| 35 | ) |
| 36 | |
| 37 | |
| 38 | def _minimal_workbook(rows: list[list[Any]]) -> bytes: |
| 39 | if XlsxWriterWorkbook is not None: |
| 40 | buffer = io.BytesIO() |
| 41 | workbook = XlsxWriterWorkbook(buffer, { |
| 42 | "in_memory": True, |
| 43 | "strings_to_formulas": False, |
| 44 | "strings_to_urls": False, |
| 45 | }) |
| 46 | worksheet = workbook.add_worksheet("Sheet1") |
| 47 | for row_index, row in enumerate(rows): |
| 48 | for col_index, value in enumerate(row): |
| 49 | worksheet.write(row_index, col_index, value) |
| 50 | workbook.close() |
| 51 | return buffer.getvalue() |
| 52 | |
| 53 | if OpenpyxlWorkbook is not None: |
| 54 | workbook = OpenpyxlWorkbook() |
| 55 | worksheet = workbook.active |
| 56 | worksheet.title = "Sheet1" |
| 57 | for row_index, row in enumerate(rows, start=1): |
| 58 | for col_index, value in enumerate(row, start=1): |
| 59 | cell = worksheet.cell(row=row_index, column=col_index, value=value) |
| 60 | if isinstance(value, str): |
| 61 | cell.data_type = "s" |
| 62 | buffer = io.BytesIO() |
| 63 | workbook.save(buffer) |
| 64 | workbook.close() |
| 65 | return buffer.getvalue() |
| 66 | |
| 67 | sheet_rows = [] |
| 68 | for row_index, values in enumerate(rows, start=1): |
| 69 | cells = "".join( |
| 70 | _xlsx_cell(value, row_index, col_index) |
| 71 | for col_index, value in enumerate(values, start=1) |
| 72 | ) |
| 73 | sheet_rows.append(f'<row r="{row_index}">{cells}</row>') |
| 74 | |
| 75 | entries = { |
| 76 | "[Content_Types].xml": '''<?xml version="1.0" encoding="UTF-8" standalone="yes"?> |
| 77 | <Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types"> |
| 78 | <Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/> |
| 79 | <Default Extension="xml" ContentType="application/xml"/> |
| 80 | <Override PartName="/xl/workbook.xml" |
| 81 | ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/> |
| 82 | <Override PartName="/xl/worksheets/sheet1.xml" |
| 83 | ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/> |
| 84 | <Override PartName="/xl/styles.xml" |
| 85 | ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/> |
| 86 | </Types>''', |
| 87 | "_rels/.rels": '''<?xml version="1.0" encoding="UTF-8" standalone="yes"?> |
| 88 | <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"> |
| 89 | <Relationship Id="rId1" |
| 90 | Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" |
| 91 | Target="xl/workbook.xml"/> |
| 92 | </Relationships>''', |
| 93 | "xl/workbook.xml": '''<?xml version="1.0" encoding="UTF-8" standalone="yes"?> |
| 94 | <workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" |
| 95 | xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"> |
| 96 | <sheets><sheet name="Sheet1" sheetId="1" r:id="rId1"/></sheets> |
| 97 | </workbook>''', |
| 98 | "xl/_rels/workbook.xml.rels": '''<?xml version="1.0" encoding="UTF-8" standalone="yes"?> |
| 99 | <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"> |
| 100 | <Relationship Id="rId1" |
| 101 | Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" |
| 102 | Target="worksheets/sheet1.xml"/> |
| 103 | <Relationship Id="rId2" |
| 104 | Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" |
| 105 | Target="styles.xml"/> |
| 106 | </Relationships>''', |
| 107 | "xl/worksheets/sheet1.xml": f'''<?xml version="1.0" encoding="UTF-8" standalone="yes"?> |
| 108 | <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" |
| 109 | xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"> |
| 110 | <sheetData>{''.join(sheet_rows)}</sheetData> |
| 111 | </worksheet>''', |
| 112 | "xl/styles.xml": '''<?xml version="1.0" encoding="UTF-8" standalone="yes"?> |
| 113 | <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> |
| 114 | <fonts count="1"><font><sz val="11"/><name val="Calibri"/></font></fonts> |
| 115 | <fills count="1"><fill><patternFill patternType="none"/></fill></fills> |
| 116 | <borders count="1"><border><left/><right/><top/><bottom/><diagonal/></border></borders> |
| 117 | <cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs> |
| 118 | <cellXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/></cellXfs> |
| 119 | </styleSheet>''', |
| 120 | } |
| 121 | |
| 122 | buffer = io.BytesIO() |
| 123 | with zipfile.ZipFile(buffer, "w", compression=zipfile.ZIP_DEFLATED) as zout: |
| 124 | for name, data in entries.items(): |
| 125 | zout.writestr(name, data.encode("utf-8")) |
| 126 | return buffer.getvalue() |
| 127 | |
| 128 | |
| 129 | def _minimal_category_chart_workbook(chart_data: dict[str, Any]) -> bytes: |
| 130 | if chart_data.get("kind") == "combo" and chart_data.get("independent_categories"): |
| 131 | plots = chart_data["plots"] |
| 132 | column_count = max( |
| 133 | int(plot["start_column"]) + len(plot["series"]) - 1 |
| 134 | for plot in plots |
| 135 | ) |
| 136 | rows: list[list[Any]] = [[None] * column_count] |
| 137 | for plot in plots: |
| 138 | category_column = int(plot["category_column"]) - 1 |
| 139 | start_column = int(plot["start_column"]) - 1 |
| 140 | for offset, series in enumerate(plot["series"]): |
| 141 | rows[0][start_column + offset] = series["name"] |
| 142 | for point_index, category in enumerate(plot["categories"], start=1): |
| 143 | while len(rows) <= point_index: |
| 144 | rows.append([None] * column_count) |
| 145 | rows[point_index][category_column] = category |
| 146 | for offset, series in enumerate(plot["series"]): |
| 147 | rows[point_index][start_column + offset] = ( |
| 148 | series["values"][point_index - 1] |
| 149 | ) |
| 150 | return _minimal_workbook(rows) |
| 151 | |
| 152 | categories = chart_data["categories"] |
| 153 | series = chart_data["series"] |
| 154 | rows: list[list[Any]] = [[None] + [item["name"] for item in series]] |
| 155 | for row_index, category in enumerate(categories): |
| 156 | rows.append([category] + [item["values"][row_index] for item in series]) |
| 157 | return _minimal_workbook(rows) |
| 158 | |
| 159 | |
| 160 | def _minimal_xy_chart_workbook(chart_data: dict[str, Any]) -> bytes: |
| 161 | series = chart_data["series"] |
| 162 | is_bubble = chart_data["type"] == "bubble" |
| 163 | rows: list[list[Any]] = [[]] |
| 164 | for item in series: |
| 165 | rows[0].extend([f"{item['name']} X", item["name"]]) |
| 166 | if is_bubble: |
| 167 | rows[0].append(f"{item['name']} Size") |
| 168 | |
| 169 | max_points = max(len(item["x"]) for item in series) |
| 170 | for point_idx in range(max_points): |
| 171 | row: list[Any] = [] |
| 172 | for item in series: |
| 173 | if point_idx < len(item["x"]): |
| 174 | row.extend([item["x"][point_idx], item["y"][point_idx]]) |
| 175 | if is_bubble: |
| 176 | row.append(item["sizes"][point_idx]) |
| 177 | else: |
| 178 | row.extend(["", ""]) |
| 179 | if is_bubble: |
| 180 | row.append("") |
| 181 | rows.append(row) |
| 182 | return _minimal_workbook(rows) |
| 183 | |
| 184 | |
| 185 | def _minimal_chart_ex_workbook(chart_data: dict[str, Any]) -> bytes: |
| 186 | chart_type = chart_data["type"] |
| 187 | if chart_type in {"sunburst", "treemap"}: |
| 188 | levels = chart_data["levels"] |
| 189 | rows: list[list[Any]] = [[f"Level {idx + 1}" for idx in range(len(levels))] + ["Value"]] |
| 190 | for row_idx, value in enumerate(chart_data["values"]): |
| 191 | rows.append([level[row_idx] for level in levels] + [value]) |
| 192 | return _minimal_workbook(rows) |
| 193 | if chart_type == "histogram": |
| 194 | return _minimal_workbook([["Value"]] + [[value] for value in chart_data["values"]]) |
| 195 | if chart_type in {"funnel", "pareto", "waterfall"}: |
| 196 | rows = [["Category", "Value"]] |
| 197 | rows.extend( |
| 198 | [category, chart_data["values"][idx]] |
| 199 | for idx, category in enumerate(chart_data["categories"]) |
| 200 | ) |
| 201 | return _minimal_workbook(rows) |
| 202 | if chart_type == "box_whisker": |
| 203 | rows = [[]] |
| 204 | for item in chart_data["series"]: |
| 205 | rows[0].extend([f"{item['name']} Category", item["name"]]) |
| 206 | max_rows = max(len(item["values"]) for item in chart_data["series"]) |
| 207 | for row_idx in range(max_rows): |
| 208 | row: list[Any] = [] |
| 209 | for item in chart_data["series"]: |
| 210 | if row_idx < len(item["values"]): |
| 211 | row.extend([item["categories"][row_idx], item["values"][row_idx]]) |
| 212 | else: |
| 213 | row.extend(["", ""]) |
| 214 | rows.append(row) |
| 215 | return _minimal_workbook(rows) |
| 216 | raise RuntimeError(f"Native PPTX {chart_type} chart is outside current basic chart support") |
| 217 |