diff options
Diffstat (limited to '.venv/lib/python3.12/site-packages/openpyxl/worksheet')
32 files changed, 6037 insertions, 0 deletions
diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/__init__.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/__init__.py new file mode 100644 index 00000000..ab6cdead --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/__init__.py @@ -0,0 +1 @@ +# Copyright (c) 2010-2024 openpyxl diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/_read_only.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/_read_only.py new file mode 100644 index 00000000..95852f21 --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/_read_only.py @@ -0,0 +1,190 @@ +# Copyright (c) 2010-2024 openpyxl + +""" Read worksheets on-demand +""" + +from .worksheet import Worksheet +from openpyxl.cell.read_only import ReadOnlyCell, EMPTY_CELL +from openpyxl.utils import get_column_letter + +from ._reader import WorkSheetParser +from openpyxl.workbook.defined_name import DefinedNameDict + + +def read_dimension(source): + parser = WorkSheetParser(source, []) + return parser.parse_dimensions() + + +class ReadOnlyWorksheet: + + _min_column = 1 + _min_row = 1 + _max_column = _max_row = None + + # from Standard Worksheet + # Methods from Worksheet + cell = Worksheet.cell + iter_rows = Worksheet.iter_rows + values = Worksheet.values + rows = Worksheet.rows + __getitem__ = Worksheet.__getitem__ + __iter__ = Worksheet.__iter__ + + + def __init__(self, parent_workbook, title, worksheet_path, shared_strings): + self.parent = parent_workbook + self.title = title + self.sheet_state = 'visible' + self._current_row = None + self._worksheet_path = worksheet_path + self._shared_strings = shared_strings + self._get_size() + self.defined_names = DefinedNameDict() + + + def _get_size(self): + src = self._get_source() + parser = WorkSheetParser(src, []) + dimensions = parser.parse_dimensions() + src.close() + if dimensions is not None: + self._min_column, self._min_row, self._max_column, self._max_row = dimensions + + + def _get_source(self): + """Parse xml source on demand, must close after use""" + return self.parent._archive.open(self._worksheet_path) + + + def _cells_by_row(self, min_col, min_row, max_col, max_row, values_only=False): + """ + The source worksheet file may have columns or rows missing. + Missing cells will be created. + """ + filler = EMPTY_CELL + if values_only: + filler = None + + max_col = max_col or self.max_column + max_row = max_row or self.max_row + empty_row = [] + if max_col is not None: + empty_row = (filler,) * (max_col + 1 - min_col) + + counter = min_row + idx = 1 + with self._get_source() as src: + parser = WorkSheetParser(src, + self._shared_strings, + data_only=self.parent.data_only, + epoch=self.parent.epoch, + date_formats=self.parent._date_formats, + timedelta_formats=self.parent._timedelta_formats) + + for idx, row in parser.parse(): + if max_row is not None and idx > max_row: + break + + # some rows are missing + for _ in range(counter, idx): + counter += 1 + yield empty_row + + # return cells from a row + if counter <= idx: + row = self._get_row(row, min_col, max_col, values_only) + counter += 1 + yield row + + if max_row is not None and max_row < idx: + for _ in range(counter, max_row+1): + yield empty_row + + + def _get_row(self, row, min_col=1, max_col=None, values_only=False): + """ + Make sure a row contains always the same number of cells or values + """ + if not row and not max_col: # in case someone wants to force rows where there aren't any + return () + + max_col = max_col or row[-1]['column'] + row_width = max_col + 1 - min_col + + new_row = [EMPTY_CELL] * row_width + if values_only: + new_row = [None] * row_width + + for cell in row: + counter = cell['column'] + if min_col <= counter <= max_col: + idx = counter - min_col # position in list of cells returned + new_row[idx] = cell['value'] + if not values_only: + new_row[idx] = ReadOnlyCell(self, **cell) + + return tuple(new_row) + + + def _get_cell(self, row, column): + """Cells are returned by a generator which can be empty""" + for row in self._cells_by_row(column, row, column, row): + if row: + return row[0] + return EMPTY_CELL + + + def calculate_dimension(self, force=False): + if not all([self.max_column, self.max_row]): + if force: + self._calculate_dimension() + else: + raise ValueError("Worksheet is unsized, use calculate_dimension(force=True)") + return f"{get_column_letter(self.min_column)}{self.min_row}:{get_column_letter(self.max_column)}{self.max_row}" + + + def _calculate_dimension(self): + """ + Loop through all the cells to get the size of a worksheet. + Do this only if it is explicitly requested. + """ + + max_col = 0 + for r in self.rows: + if not r: + continue + cell = r[-1] + max_col = max(max_col, cell.column) + + self._max_row = cell.row + self._max_column = max_col + + + def reset_dimensions(self): + """ + Remove worksheet dimensions if these are incorrect in the worksheet source. + NB. This probably indicates a bug in the library or application that created + the workbook. + """ + self._max_row = self._max_column = None + + + @property + def min_row(self): + return self._min_row + + + @property + def max_row(self): + return self._max_row + + + @property + def min_column(self): + return self._min_column + + + @property + def max_column(self): + return self._max_column diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/_reader.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/_reader.py new file mode 100644 index 00000000..38240f03 --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/_reader.py @@ -0,0 +1,472 @@ +# Copyright (c) 2010-2024 openpyxl + +"""Reader for a single worksheet.""" +from copy import copy +from warnings import warn + +# compatibility imports +from openpyxl.xml.functions import iterparse + +# package imports +from openpyxl.cell import Cell, MergedCell +from openpyxl.cell.text import Text +from openpyxl.worksheet.dimensions import ( + ColumnDimension, + RowDimension, + SheetFormatProperties, +) + +from openpyxl.xml.constants import ( + SHEET_MAIN_NS, + EXT_TYPES, +) +from openpyxl.formatting.formatting import ConditionalFormatting +from openpyxl.formula.translate import Translator +from openpyxl.utils import ( + get_column_letter, + coordinate_to_tuple, + ) +from openpyxl.utils.datetime import from_excel, from_ISO8601, WINDOWS_EPOCH +from openpyxl.descriptors.excel import ExtensionList +from openpyxl.cell.rich_text import CellRichText + +from .formula import DataTableFormula, ArrayFormula +from .filters import AutoFilter +from .header_footer import HeaderFooter +from .hyperlink import HyperlinkList +from .merge import MergeCells +from .page import PageMargins, PrintOptions, PrintPageSetup +from .pagebreak import RowBreak, ColBreak +from .protection import SheetProtection +from .scenario import ScenarioList +from .views import SheetViewList +from .datavalidation import DataValidationList +from .table import TablePartList +from .properties import WorksheetProperties +from .dimensions import SheetDimension +from .related import Related + + +CELL_TAG = '{%s}c' % SHEET_MAIN_NS +VALUE_TAG = '{%s}v' % SHEET_MAIN_NS +FORMULA_TAG = '{%s}f' % SHEET_MAIN_NS +MERGE_TAG = '{%s}mergeCells' % SHEET_MAIN_NS +INLINE_STRING = "{%s}is" % SHEET_MAIN_NS +COL_TAG = '{%s}col' % SHEET_MAIN_NS +ROW_TAG = '{%s}row' % SHEET_MAIN_NS +CF_TAG = '{%s}conditionalFormatting' % SHEET_MAIN_NS +LEGACY_TAG = '{%s}legacyDrawing' % SHEET_MAIN_NS +PROT_TAG = '{%s}sheetProtection' % SHEET_MAIN_NS +EXT_TAG = "{%s}extLst" % SHEET_MAIN_NS +HYPERLINK_TAG = "{%s}hyperlinks" % SHEET_MAIN_NS +TABLE_TAG = "{%s}tableParts" % SHEET_MAIN_NS +PRINT_TAG = '{%s}printOptions' % SHEET_MAIN_NS +MARGINS_TAG = '{%s}pageMargins' % SHEET_MAIN_NS +PAGE_TAG = '{%s}pageSetup' % SHEET_MAIN_NS +HEADER_TAG = '{%s}headerFooter' % SHEET_MAIN_NS +FILTER_TAG = '{%s}autoFilter' % SHEET_MAIN_NS +VALIDATION_TAG = '{%s}dataValidations' % SHEET_MAIN_NS +PROPERTIES_TAG = '{%s}sheetPr' % SHEET_MAIN_NS +VIEWS_TAG = '{%s}sheetViews' % SHEET_MAIN_NS +FORMAT_TAG = '{%s}sheetFormatPr' % SHEET_MAIN_NS +ROW_BREAK_TAG = '{%s}rowBreaks' % SHEET_MAIN_NS +COL_BREAK_TAG = '{%s}colBreaks' % SHEET_MAIN_NS +SCENARIOS_TAG = '{%s}scenarios' % SHEET_MAIN_NS +DATA_TAG = '{%s}sheetData' % SHEET_MAIN_NS +DIMENSION_TAG = '{%s}dimension' % SHEET_MAIN_NS +CUSTOM_VIEWS_TAG = '{%s}customSheetViews' % SHEET_MAIN_NS + + +def _cast_number(value): + "Convert numbers as string to an int or float" + if "." in value or "E" in value or "e" in value: + return float(value) + return int(value) + + +def parse_richtext_string(element): + """ + Parse inline string and preserve rich text formatting + """ + value = CellRichText.from_tree(element) or "" + if len(value) == 1 and isinstance(value[0], str): + value = value[0] + return value + + +class WorkSheetParser: + + def __init__(self, src, shared_strings, data_only=False, + epoch=WINDOWS_EPOCH, date_formats=set(), + timedelta_formats=set(), rich_text=False): + self.min_row = self.min_col = None + self.epoch = epoch + self.source = src + self.shared_strings = shared_strings + self.data_only = data_only + self.shared_formulae = {} + self.row_counter = self.col_counter = 0 + self.tables = TablePartList() + self.date_formats = date_formats + self.timedelta_formats = timedelta_formats + self.row_dimensions = {} + self.column_dimensions = {} + self.number_formats = [] + self.keep_vba = False + self.hyperlinks = HyperlinkList() + self.formatting = [] + self.legacy_drawing = None + self.merged_cells = None + self.row_breaks = RowBreak() + self.col_breaks = ColBreak() + self.rich_text = rich_text + + + def parse(self): + dispatcher = { + COL_TAG: self.parse_column_dimensions, + PROT_TAG: self.parse_sheet_protection, + EXT_TAG: self.parse_extensions, + CF_TAG: self.parse_formatting, + LEGACY_TAG: self.parse_legacy, + ROW_BREAK_TAG: self.parse_row_breaks, + COL_BREAK_TAG: self.parse_col_breaks, + CUSTOM_VIEWS_TAG: self.parse_custom_views, + } + + properties = { + PRINT_TAG: ('print_options', PrintOptions), + MARGINS_TAG: ('page_margins', PageMargins), + PAGE_TAG: ('page_setup', PrintPageSetup), + HEADER_TAG: ('HeaderFooter', HeaderFooter), + FILTER_TAG: ('auto_filter', AutoFilter), + VALIDATION_TAG: ('data_validations', DataValidationList), + PROPERTIES_TAG: ('sheet_properties', WorksheetProperties), + VIEWS_TAG: ('views', SheetViewList), + FORMAT_TAG: ('sheet_format', SheetFormatProperties), + SCENARIOS_TAG: ('scenarios', ScenarioList), + TABLE_TAG: ('tables', TablePartList), + HYPERLINK_TAG: ('hyperlinks', HyperlinkList), + MERGE_TAG: ('merged_cells', MergeCells), + + } + + it = iterparse(self.source) # add a finaliser to close the source when this becomes possible + + for _, element in it: + tag_name = element.tag + if tag_name in dispatcher: + dispatcher[tag_name](element) + element.clear() + elif tag_name in properties: + prop = properties[tag_name] + obj = prop[1].from_tree(element) + setattr(self, prop[0], obj) + element.clear() + elif tag_name == ROW_TAG: + row = self.parse_row(element) + element.clear() + yield row + + + def parse_dimensions(self): + """ + Get worksheet dimensions if they are provided. + """ + it = iterparse(self.source) + + for _event, element in it: + if element.tag == DIMENSION_TAG: + dim = SheetDimension.from_tree(element) + return dim.boundaries + + elif element.tag == DATA_TAG: + # Dimensions missing + break + element.clear() + + + def parse_cell(self, element): + data_type = element.get('t', 'n') + coordinate = element.get('r') + style_id = element.get('s', 0) + if style_id: + style_id = int(style_id) + + if data_type == "inlineStr": + value = None + else: + value = element.findtext(VALUE_TAG, None) or None + + if coordinate: + row, column = coordinate_to_tuple(coordinate) + self.col_counter = column + else: + self.col_counter += 1 + row, column = self.row_counter, self.col_counter + + if not self.data_only and element.find(FORMULA_TAG) is not None: + data_type = 'f' + value = self.parse_formula(element) + + elif value is not None: + if data_type == 'n': + value = _cast_number(value) + if style_id in self.date_formats: + data_type = 'd' + try: + value = from_excel( + value, self.epoch, timedelta=style_id in self.timedelta_formats + ) + except (OverflowError, ValueError): + msg = f"""Cell {coordinate} is marked as a date but the serial value {value} is outside the limits for dates. The cell will be treated as an error.""" + warn(msg) + data_type = "e" + value = "#VALUE!" + elif data_type == 's': + value = self.shared_strings[int(value)] + elif data_type == 'b': + value = bool(int(value)) + elif data_type == "str": + data_type = "s" + elif data_type == 'd': + value = from_ISO8601(value) + + elif data_type == 'inlineStr': + child = element.find(INLINE_STRING) + if child is not None: + data_type = 's' + if self.rich_text: + value = parse_richtext_string(child) + else: + value = Text.from_tree(child).content + + return {'row':row, 'column':column, 'value':value, 'data_type':data_type, 'style_id':style_id} + + + def parse_formula(self, element): + """ + possible formulae types: shared, array, datatable + """ + formula = element.find(FORMULA_TAG) + formula_type = formula.get('t') + coordinate = element.get('r') + value = "=" + if formula.text is not None: + value += formula.text + + if formula_type == "array": + value = ArrayFormula(ref=formula.get('ref'), text=value) + + elif formula_type == "shared": + idx = formula.get('si') + if idx in self.shared_formulae: + trans = self.shared_formulae[idx] + value = trans.translate_formula(coordinate) + elif value != "=": + self.shared_formulae[idx] = Translator(value, coordinate) + + elif formula_type == "dataTable": + value = DataTableFormula(**formula.attrib) + + return value + + + def parse_column_dimensions(self, col): + attrs = dict(col.attrib) + column = get_column_letter(int(attrs['min'])) + attrs['index'] = column + self.column_dimensions[column] = attrs + + + def parse_row(self, row): + attrs = dict(row.attrib) + + if "r" in attrs: + try: + self.row_counter = int(attrs['r']) + except ValueError: + val = float(attrs['r']) + if val.is_integer(): + self.row_counter = int(val) + else: + raise ValueError(f"{attrs['r']} is not a valid row number") + else: + self.row_counter += 1 + self.col_counter = 0 + + keys = {k for k in attrs if not k.startswith('{')} + if keys - {'r', 'spans'}: + # don't create dimension objects unless they have relevant information + self.row_dimensions[str(self.row_counter)] = attrs + + cells = [self.parse_cell(el) for el in row] + return self.row_counter, cells + + + def parse_formatting(self, element): + try: + cf = ConditionalFormatting.from_tree(element) + self.formatting.append(cf) + except TypeError as e: + msg = f"Failed to load a conditional formatting rule. It will be discarded. Cause: {e}" + warn(msg) + + + def parse_sheet_protection(self, element): + protection = SheetProtection.from_tree(element) + password = element.get("password") + if password is not None: + protection.set_password(password, True) + self.protection = protection + + + def parse_extensions(self, element): + extLst = ExtensionList.from_tree(element) + for e in extLst.ext: + ext_type = EXT_TYPES.get(e.uri.upper(), "Unknown") + msg = "{0} extension is not supported and will be removed".format(ext_type) + warn(msg) + + + def parse_legacy(self, element): + obj = Related.from_tree(element) + self.legacy_drawing = obj.id + + + def parse_row_breaks(self, element): + brk = RowBreak.from_tree(element) + self.row_breaks = brk + + + def parse_col_breaks(self, element): + brk = ColBreak.from_tree(element) + self.col_breaks = brk + + + def parse_custom_views(self, element): + # clear page_breaks to avoid duplication which Excel doesn't like + # basically they're ignored in custom views + self.row_breaks = RowBreak() + self.col_breaks = ColBreak() + + +class WorksheetReader: + """ + Create a parser and apply it to a workbook + """ + + def __init__(self, ws, xml_source, shared_strings, data_only, rich_text): + self.ws = ws + self.parser = WorkSheetParser(xml_source, shared_strings, + data_only, ws.parent.epoch, ws.parent._date_formats, + ws.parent._timedelta_formats, rich_text) + self.tables = [] + + + def bind_cells(self): + for idx, row in self.parser.parse(): + for cell in row: + style = self.ws.parent._cell_styles[cell['style_id']] + c = Cell(self.ws, row=cell['row'], column=cell['column'], style_array=style) + c._value = cell['value'] + c.data_type = cell['data_type'] + self.ws._cells[(cell['row'], cell['column'])] = c + + if self.ws._cells: + self.ws._current_row = self.ws.max_row # use cells not row dimensions + + + def bind_formatting(self): + for cf in self.parser.formatting: + for rule in cf.rules: + if rule.dxfId is not None: + rule.dxf = self.ws.parent._differential_styles[rule.dxfId] + self.ws.conditional_formatting[cf] = rule + + + def bind_tables(self): + for t in self.parser.tables.tablePart: + rel = self.ws._rels.get(t.id) + self.tables.append(rel.Target) + + + def bind_merged_cells(self): + from openpyxl.worksheet.cell_range import MultiCellRange + from openpyxl.worksheet.merge import MergedCellRange + if not self.parser.merged_cells: + return + + ranges = [] + for cr in self.parser.merged_cells.mergeCell: + mcr = MergedCellRange(self.ws, cr.ref) + self.ws._clean_merge_range(mcr) + ranges.append(mcr) + self.ws.merged_cells = MultiCellRange(ranges) + + + def bind_hyperlinks(self): + for link in self.parser.hyperlinks.hyperlink: + if link.id: + rel = self.ws._rels.get(link.id) + link.target = rel.Target + if ":" in link.ref: + # range of cells + for row in self.ws[link.ref]: + for cell in row: + try: + cell.hyperlink = copy(link) + except AttributeError: + pass + else: + cell = self.ws[link.ref] + if isinstance(cell, MergedCell): + cell = self.normalize_merged_cell_link(cell.coordinate) + cell.hyperlink = link + + def normalize_merged_cell_link(self, coord): + """ + Returns the appropriate cell to which a hyperlink, which references a merged cell at the specified coordinates, + should be bound. + """ + for rng in self.ws.merged_cells: + if coord in rng: + return self.ws.cell(*rng.top[0]) + + def bind_col_dimensions(self): + for col, cd in self.parser.column_dimensions.items(): + if 'style' in cd: + key = int(cd['style']) + cd['style'] = self.ws.parent._cell_styles[key] + self.ws.column_dimensions[col] = ColumnDimension(self.ws, **cd) + + + def bind_row_dimensions(self): + for row, rd in self.parser.row_dimensions.items(): + if 's' in rd: + key = int(rd['s']) + rd['s'] = self.ws.parent._cell_styles[key] + self.ws.row_dimensions[int(row)] = RowDimension(self.ws, **rd) + + + def bind_properties(self): + for k in ('print_options', 'page_margins', 'page_setup', + 'HeaderFooter', 'auto_filter', 'data_validations', + 'sheet_properties', 'views', 'sheet_format', + 'row_breaks', 'col_breaks', 'scenarios', 'legacy_drawing', + 'protection', + ): + v = getattr(self.parser, k, None) + if v is not None: + setattr(self.ws, k, v) + + + def bind_all(self): + self.bind_cells() + self.bind_merged_cells() + self.bind_hyperlinks() + self.bind_formatting() + self.bind_col_dimensions() + self.bind_row_dimensions() + self.bind_tables() + self.bind_properties() diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/_write_only.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/_write_only.py new file mode 100644 index 00000000..0b1d027d --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/_write_only.py @@ -0,0 +1,160 @@ +# Copyright (c) 2010-2024 openpyxl + + +"""Write worksheets to xml representations in an optimized way""" + +from inspect import isgenerator + +from openpyxl.cell import Cell, WriteOnlyCell +from openpyxl.workbook.child import _WorkbookChild +from .worksheet import Worksheet +from openpyxl.utils.exceptions import WorkbookAlreadySaved + +from ._writer import WorksheetWriter + + +class WriteOnlyWorksheet(_WorkbookChild): + """ + Streaming worksheet. Optimised to reduce memory by writing rows just in + time. + Cells can be styled and have comments Styles for rows and columns + must be applied before writing cells + """ + + __saved = False + _writer = None + _rows = None + _rel_type = Worksheet._rel_type + _path = Worksheet._path + mime_type = Worksheet.mime_type + + # copy methods from Standard worksheet + _add_row = Worksheet._add_row + _add_column = Worksheet._add_column + add_chart = Worksheet.add_chart + add_image = Worksheet.add_image + add_table = Worksheet.add_table + tables = Worksheet.tables + print_titles = Worksheet.print_titles + print_title_cols = Worksheet.print_title_cols + print_title_rows = Worksheet.print_title_rows + freeze_panes = Worksheet.freeze_panes + print_area = Worksheet.print_area + sheet_view = Worksheet.sheet_view + _setup = Worksheet._setup + + def __init__(self, parent, title): + super().__init__(parent, title) + self._max_col = 0 + self._max_row = 0 + self._setup() + + @property + def closed(self): + return self.__saved + + + def _write_rows(self): + """ + Send rows to the writer's stream + """ + try: + xf = self._writer.xf.send(True) + except StopIteration: + self._already_saved() + + with xf.element("sheetData"): + row_idx = 1 + try: + while True: + row = (yield) + row = self._values_to_row(row, row_idx) + self._writer.write_row(xf, row, row_idx) + row_idx += 1 + except GeneratorExit: + pass + + self._writer.xf.send(None) + + + def _get_writer(self): + if self._writer is None: + self._writer = WorksheetWriter(self) + self._writer.write_top() + + + def close(self): + if self.__saved: + self._already_saved() + + self._get_writer() + + if self._rows is None: + self._writer.write_rows() + else: + self._rows.close() + + self._writer.write_tail() + + self._writer.close() + self.__saved = True + + + def append(self, row): + """ + :param row: iterable containing values to append + :type row: iterable + """ + + if (not isgenerator(row) and + not isinstance(row, (list, tuple, range)) + ): + self._invalid_row(row) + + self._get_writer() + + if self._rows is None: + self._rows = self._write_rows() + next(self._rows) + + self._rows.send(row) + + + def _values_to_row(self, values, row_idx): + """ + Convert whatever has been appended into a form suitable for work_rows + """ + cell = WriteOnlyCell(self) + + for col_idx, value in enumerate(values, 1): + if value is None: + continue + try: + cell.value = value + except ValueError: + if isinstance(value, Cell): + cell = value + else: + raise ValueError + + cell.column = col_idx + cell.row = row_idx + + if cell.hyperlink is not None: + cell.hyperlink.ref = cell.coordinate + + yield cell + + # reset cell if style applied + if cell.has_style or cell.hyperlink: + cell = WriteOnlyCell(self) + + + def _already_saved(self): + raise WorkbookAlreadySaved('Workbook has already been saved and cannot be modified or saved anymore.') + + + def _invalid_row(self, iterable): + raise TypeError('Value must be a list, tuple, range or a generator Supplied value is {0}'.format( + type(iterable)) + ) diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/_writer.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/_writer.py new file mode 100644 index 00000000..df381d2b --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/_writer.py @@ -0,0 +1,390 @@ +# Copyright (c) 2010-2024 openpyxl + +import atexit +from collections import defaultdict +from io import BytesIO +import os +from tempfile import NamedTemporaryFile +from warnings import warn + +from openpyxl.xml.functions import xmlfile +from openpyxl.xml.constants import SHEET_MAIN_NS + +from openpyxl.comments.comment_sheet import CommentRecord +from openpyxl.packaging.relationship import Relationship, RelationshipList +from openpyxl.styles.differential import DifferentialStyle + +from .dimensions import SheetDimension +from .hyperlink import HyperlinkList +from .merge import MergeCell, MergeCells +from .related import Related +from .table import TablePartList + +from openpyxl.cell._writer import write_cell + + +ALL_TEMP_FILES = [] + +@atexit.register +def _openpyxl_shutdown(): + for path in ALL_TEMP_FILES: + if os.path.exists(path): + os.remove(path) + + +def create_temporary_file(suffix=''): + fobj = NamedTemporaryFile(mode='w+', suffix=suffix, + prefix='openpyxl.', delete=False) + filename = fobj.name + fobj.close() + ALL_TEMP_FILES.append(filename) + return filename + + +class WorksheetWriter: + + + def __init__(self, ws, out=None): + self.ws = ws + self.ws._hyperlinks = [] + self.ws._comments = [] + if out is None: + out = create_temporary_file() + self.out = out + self._rels = RelationshipList() + self.xf = self.get_stream() + next(self.xf) # start generator + + + def write_properties(self): + props = self.ws.sheet_properties + self.xf.send(props.to_tree()) + + + def write_dimensions(self): + """ + Write worksheet size if known + """ + ref = getattr(self.ws, 'calculate_dimension', None) + if ref: + dim = SheetDimension(ref()) + self.xf.send(dim.to_tree()) + + + def write_format(self): + self.ws.sheet_format.outlineLevelCol = self.ws.column_dimensions.max_outline + fmt = self.ws.sheet_format + self.xf.send(fmt.to_tree()) + + + def write_views(self): + views = self.ws.views + self.xf.send(views.to_tree()) + + + def write_cols(self): + cols = self.ws.column_dimensions + self.xf.send(cols.to_tree()) + + + def write_top(self): + """ + Write all elements up to rows: + properties + dimensions + views + format + cols + """ + self.write_properties() + self.write_dimensions() + self.write_views() + self.write_format() + self.write_cols() + + + def rows(self): + """Return all rows, and any cells that they contain""" + # order cells by row + rows = defaultdict(list) + for (row, col), cell in sorted(self.ws._cells.items()): + rows[row].append(cell) + + # add empty rows if styling has been applied + for row in self.ws.row_dimensions.keys() - rows.keys(): + rows[row] = [] + + return sorted(rows.items()) + + + def write_rows(self): + xf = self.xf.send(True) + + with xf.element("sheetData"): + for row_idx, row in self.rows(): + self.write_row(xf, row, row_idx) + + self.xf.send(None) # return control to generator + + + def write_row(self, xf, row, row_idx): + attrs = {'r': f"{row_idx}"} + dims = self.ws.row_dimensions + attrs.update(dims.get(row_idx, {})) + + with xf.element("row", attrs): + + for cell in row: + if cell._comment is not None: + comment = CommentRecord.from_cell(cell) + self.ws._comments.append(comment) + if ( + cell._value is None + and not cell.has_style + and not cell._comment + ): + continue + write_cell(xf, self.ws, cell, cell.has_style) + + + def write_protection(self): + prot = self.ws.protection + if prot: + self.xf.send(prot.to_tree()) + + + def write_scenarios(self): + scenarios = self.ws.scenarios + if scenarios: + self.xf.send(scenarios.to_tree()) + + + def write_filter(self): + flt = self.ws.auto_filter + if flt: + self.xf.send(flt.to_tree()) + + + def write_sort(self): + """ + As per discusion with the OOXML Working Group global sort state is not required. + openpyxl never reads it from existing files + """ + pass + + + def write_merged_cells(self): + merged = self.ws.merged_cells + if merged: + cells = [MergeCell(str(ref)) for ref in self.ws.merged_cells] + self.xf.send(MergeCells(mergeCell=cells).to_tree()) + + + def write_formatting(self): + df = DifferentialStyle() + wb = self.ws.parent + for cf in self.ws.conditional_formatting: + for rule in cf.rules: + if rule.dxf and rule.dxf != df: + rule.dxfId = wb._differential_styles.add(rule.dxf) + self.xf.send(cf.to_tree()) + + + def write_validations(self): + dv = self.ws.data_validations + if dv: + self.xf.send(dv.to_tree()) + + + def write_hyperlinks(self): + + links = self.ws._hyperlinks + + for link in links: + if link.target: + rel = Relationship(type="hyperlink", TargetMode="External", Target=link.target) + self._rels.append(rel) + link.id = rel.id + + if links: + self.xf.send(HyperlinkList(links).to_tree()) + + + def write_print(self): + print_options = self.ws.print_options + if print_options: + self.xf.send(print_options.to_tree()) + + + def write_margins(self): + margins = self.ws.page_margins + if margins: + self.xf.send(margins.to_tree()) + + + def write_page(self): + setup = self.ws.page_setup + if setup: + self.xf.send(setup.to_tree()) + + + def write_header(self): + hf = self.ws.HeaderFooter + if hf: + self.xf.send(hf.to_tree()) + + + def write_breaks(self): + brks = (self.ws.row_breaks, self.ws.col_breaks) + for brk in brks: + if brk: + self.xf.send(brk.to_tree()) + + + def write_drawings(self): + if self.ws._charts or self.ws._images: + rel = Relationship(type="drawing", Target="") + self._rels.append(rel) + drawing = Related() + drawing.id = rel.id + self.xf.send(drawing.to_tree("drawing")) + + + def write_legacy(self): + """ + Comments & VBA controls use VML and require an additional element + that is no longer in the specification. + """ + if (self.ws.legacy_drawing is not None or self.ws._comments): + legacy = Related(id="anysvml") + self.xf.send(legacy.to_tree("legacyDrawing")) + + + def write_tables(self): + tables = TablePartList() + + for table in self.ws.tables.values(): + if not table.tableColumns: + table._initialise_columns() + if table.headerRowCount: + try: + row = self.ws[table.ref][0] + for cell, col in zip(row, table.tableColumns): + if cell.data_type != "s": + warn("File may not be readable: column headings must be strings.") + col.name = str(cell.value) + except TypeError: + warn("Column headings are missing, file may not be readable") + rel = Relationship(Type=table._rel_type, Target="") + self._rels.append(rel) + table._rel_id = rel.Id + tables.append(Related(id=rel.Id)) + + if tables: + self.xf.send(tables.to_tree()) + + + def get_stream(self): + with xmlfile(self.out) as xf: + with xf.element("worksheet", xmlns=SHEET_MAIN_NS): + try: + while True: + el = (yield) + if el is True: + yield xf + elif el is None: # et_xmlfile chokes + continue + else: + xf.write(el) + except GeneratorExit: + pass + + + def write_tail(self): + """ + Write all elements after the rows + calc properties + protection + protected ranges # + scenarios + filters + sorts # always ignored + data consolidation # + custom views # + merged cells + phonetic properties # + conditional formatting + data validation + hyperlinks + print options + page margins + page setup + header + row breaks + col breaks + custom properties # + cell watches # + ignored errors # + smart tags # + drawing + drawingHF # + background # + OLE objects # + controls # + web publishing # + tables + """ + self.write_protection() + self.write_scenarios() + self.write_filter() + self.write_merged_cells() + self.write_formatting() + self.write_validations() + self.write_hyperlinks() + self.write_print() + self.write_margins() + self.write_page() + self.write_header() + self.write_breaks() + self.write_drawings() + self.write_legacy() + self.write_tables() + + + def write(self): + """ + High level + """ + self.write_top() + self.write_rows() + self.write_tail() + self.close() + + + def close(self): + """ + Close the context manager + """ + if self.xf: + self.xf.close() + + + def read(self): + """ + Close the context manager and return serialised XML + """ + self.close() + if isinstance(self.out, BytesIO): + return self.out.getvalue() + with open(self.out, "rb") as src: + out = src.read() + + return out + + + def cleanup(self): + """ + Remove tempfile + """ + os.remove(self.out) + ALL_TEMP_FILES.remove(self.out) diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/cell_range.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/cell_range.py new file mode 100644 index 00000000..2fbf5e22 --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/cell_range.py @@ -0,0 +1,512 @@ +# Copyright (c) 2010-2024 openpyxl + +from copy import copy +from operator import attrgetter + +from openpyxl.descriptors import Strict +from openpyxl.descriptors import MinMax +from openpyxl.descriptors.sequence import UniqueSequence +from openpyxl.descriptors.serialisable import Serialisable + +from openpyxl.utils import ( + range_boundaries, + range_to_tuple, + get_column_letter, + quote_sheetname, +) + +class CellRange(Serialisable): + """ + Represents a range in a sheet: title and coordinates. + + This object is used to perform operations on ranges, like: + + - shift, expand or shrink + - union/intersection with another sheet range, + + We can check whether a range is: + + - equal or not equal to another, + - disjoint of another, + - contained in another. + + We can get: + + - the size of a range. + - the range bounds (vertices) + - the coordinates, + - the string representation, + + """ + + min_col = MinMax(min=1, max=18278, expected_type=int) + min_row = MinMax(min=1, max=1048576, expected_type=int) + max_col = MinMax(min=1, max=18278, expected_type=int) + max_row = MinMax(min=1, max=1048576, expected_type=int) + + + def __init__(self, range_string=None, min_col=None, min_row=None, + max_col=None, max_row=None, title=None): + if range_string is not None: + if "!" in range_string: + title, (min_col, min_row, max_col, max_row) = range_to_tuple(range_string) + else: + min_col, min_row, max_col, max_row = range_boundaries(range_string) + + self.min_col = min_col + self.min_row = min_row + self.max_col = max_col + self.max_row = max_row + self.title = title + + if min_col > max_col: + fmt = "{max_col} must be greater than {min_col}" + raise ValueError(fmt.format(min_col=min_col, max_col=max_col)) + if min_row > max_row: + fmt = "{max_row} must be greater than {min_row}" + raise ValueError(fmt.format(min_row=min_row, max_row=max_row)) + + + @property + def bounds(self): + """ + Vertices of the range as a tuple + """ + return self.min_col, self.min_row, self.max_col, self.max_row + + + @property + def coord(self): + """ + Excel-style representation of the range + """ + fmt = "{min_col}{min_row}:{max_col}{max_row}" + if (self.min_col == self.max_col + and self.min_row == self.max_row): + fmt = "{min_col}{min_row}" + + return fmt.format( + min_col=get_column_letter(self.min_col), + min_row=self.min_row, + max_col=get_column_letter(self.max_col), + max_row=self.max_row + ) + + @property + def rows(self): + """ + Return cell coordinates as rows + """ + for row in range(self.min_row, self.max_row+1): + yield [(row, col) for col in range(self.min_col, self.max_col+1)] + + + @property + def cols(self): + """ + Return cell coordinates as columns + """ + for col in range(self.min_col, self.max_col+1): + yield [(row, col) for row in range(self.min_row, self.max_row+1)] + + + @property + def cells(self): + from itertools import product + return product(range(self.min_row, self.max_row+1), range(self.min_col, self.max_col+1)) + + + def _check_title(self, other): + """ + Check whether comparisons between ranges are possible. + Cannot compare ranges from different worksheets + Skip if the range passed in has no title. + """ + if not isinstance(other, CellRange): + raise TypeError(repr(type(other))) + + if other.title and self.title != other.title: + raise ValueError("Cannot work with ranges from different worksheets") + + + def __repr__(self): + fmt = u"<{cls} {coord}>" + if self.title: + fmt = u"<{cls} {title!r}!{coord}>" + return fmt.format(cls=self.__class__.__name__, title=self.title, coord=self.coord) + + + def __hash__(self): + return hash((self.min_row, self.min_col, self.max_row, self.max_col)) + + + def __str__(self): + fmt = "{coord}" + title = self.title + if title: + fmt = u"{title}!{coord}" + title = quote_sheetname(title) + return fmt.format(title=title, coord=self.coord) + + + def __copy__(self): + return self.__class__(min_col=self.min_col, min_row=self.min_row, + max_col=self.max_col, max_row=self.max_row, + title=self.title) + + + def shift(self, col_shift=0, row_shift=0): + """ + Shift the focus of the range according to the shift values (*col_shift*, *row_shift*). + + :type col_shift: int + :param col_shift: number of columns to be moved by, can be negative + :type row_shift: int + :param row_shift: number of rows to be moved by, can be negative + :raise: :class:`ValueError` if any row or column index < 1 + """ + + if (self.min_col + col_shift <= 0 + or self.min_row + row_shift <= 0): + raise ValueError("Invalid shift value: col_shift={0}, row_shift={1}".format(col_shift, row_shift)) + self.min_col += col_shift + self.min_row += row_shift + self.max_col += col_shift + self.max_row += row_shift + + + def __ne__(self, other): + """ + Test whether the ranges are not equal. + + :type other: openpyxl.worksheet.cell_range.CellRange + :param other: Other sheet range + :return: ``True`` if *range* != *other*. + """ + try: + self._check_title(other) + except ValueError: + return True + + return ( + other.min_row != self.min_row + or self.max_row != other.max_row + or other.min_col != self.min_col + or self.max_col != other.max_col + ) + + + def __eq__(self, other): + """ + Test whether the ranges are equal. + + :type other: openpyxl.worksheet.cell_range.CellRange + :param other: Other sheet range + :return: ``True`` if *range* == *other*. + """ + return not self.__ne__(other) + + + def issubset(self, other): + """ + Test whether every cell in this range is also in *other*. + + :type other: openpyxl.worksheet.cell_range.CellRange + :param other: Other sheet range + :return: ``True`` if *range* <= *other*. + """ + self._check_title(other) + + return other.__superset(self) + + __le__ = issubset + + + def __lt__(self, other): + """ + Test whether *other* contains every cell of this range, and more. + + :type other: openpyxl.worksheet.cell_range.CellRange + :param other: Other sheet range + :return: ``True`` if *range* < *other*. + """ + return self.__le__(other) and self.__ne__(other) + + + def __superset(self, other): + return ( + (self.min_row <= other.min_row <= other.max_row <= self.max_row) + and + (self.min_col <= other.min_col <= other.max_col <= self.max_col) + ) + + + def issuperset(self, other): + """ + Test whether every cell in *other* is in this range. + + :type other: openpyxl.worksheet.cell_range.CellRange + :param other: Other sheet range + :return: ``True`` if *range* >= *other* (or *other* in *range*). + """ + self._check_title(other) + + return self.__superset(other) + + __ge__ = issuperset + + + def __contains__(self, coord): + """ + Check whether the range contains a particular cell coordinate + """ + cr = self.__class__(coord) + return self.__superset(cr) + + + def __gt__(self, other): + """ + Test whether this range contains every cell in *other*, and more. + + :type other: openpyxl.worksheet.cell_range.CellRange + :param other: Other sheet range + :return: ``True`` if *range* > *other*. + """ + return self.__ge__(other) and self.__ne__(other) + + + def isdisjoint(self, other): + """ + Return ``True`` if this range has no cell in common with *other*. + Ranges are disjoint if and only if their intersection is the empty range. + + :type other: openpyxl.worksheet.cell_range.CellRange + :param other: Other sheet range. + :return: ``True`` if the range has no cells in common with other. + """ + self._check_title(other) + + # Sort by top-left vertex + if self.bounds > other.bounds: + self, other = other, self + + return (self.max_col < other.min_col + or self.max_row < other.min_row + or other.max_row < self.min_row) + + + def intersection(self, other): + """ + Return a new range with cells common to this range and *other* + + :type other: openpyxl.worksheet.cell_range.CellRange + :param other: Other sheet range. + :return: the intersecting sheet range. + :raise: :class:`ValueError` if the *other* range doesn't intersect + with this range. + """ + if self.isdisjoint(other): + raise ValueError("Range {0} doesn't intersect {0}".format(self, other)) + + min_row = max(self.min_row, other.min_row) + max_row = min(self.max_row, other.max_row) + min_col = max(self.min_col, other.min_col) + max_col = min(self.max_col, other.max_col) + + return CellRange(min_col=min_col, min_row=min_row, max_col=max_col, + max_row=max_row) + + __and__ = intersection + + + def union(self, other): + """ + Return the minimal superset of this range and *other*. This new range + will contain all cells from this range, *other*, and any additional + cells required to form a rectangular ``CellRange``. + + :type other: openpyxl.worksheet.cell_range.CellRange + :param other: Other sheet range. + :return: a ``CellRange`` that is a superset of this and *other*. + """ + self._check_title(other) + + min_row = min(self.min_row, other.min_row) + max_row = max(self.max_row, other.max_row) + min_col = min(self.min_col, other.min_col) + max_col = max(self.max_col, other.max_col) + return CellRange(min_col=min_col, min_row=min_row, max_col=max_col, + max_row=max_row, title=self.title) + + __or__ = union + + + def __iter__(self): + """ + For use as a dictionary elsewhere in the library. + """ + for x in self.__attrs__: + if x == "title": + continue + v = getattr(self, x) + yield x, v + + + def expand(self, right=0, down=0, left=0, up=0): + """ + Expand the range by the dimensions provided. + + :type right: int + :param right: expand range to the right by this number of cells + :type down: int + :param down: expand range down by this number of cells + :type left: int + :param left: expand range to the left by this number of cells + :type up: int + :param up: expand range up by this number of cells + """ + self.min_col -= left + self.min_row -= up + self.max_col += right + self.max_row += down + + + def shrink(self, right=0, bottom=0, left=0, top=0): + """ + Shrink the range by the dimensions provided. + + :type right: int + :param right: shrink range from the right by this number of cells + :type down: int + :param down: shrink range from the top by this number of cells + :type left: int + :param left: shrink range from the left by this number of cells + :type up: int + :param up: shrink range from the bottom by this number of cells + """ + self.min_col += left + self.min_row += top + self.max_col -= right + self.max_row -= bottom + + + @property + def size(self): + """ Return the size of the range as a dictionary of rows and columns. """ + cols = self.max_col + 1 - self.min_col + rows = self.max_row + 1 - self.min_row + return {'columns':cols, 'rows':rows} + + + @property + def top(self): + """A list of cell coordinates that comprise the top of the range""" + return [(self.min_row, col) for col in range(self.min_col, self.max_col+1)] + + + @property + def bottom(self): + """A list of cell coordinates that comprise the bottom of the range""" + return [(self.max_row, col) for col in range(self.min_col, self.max_col+1)] + + + @property + def left(self): + """A list of cell coordinates that comprise the left-side of the range""" + return [(row, self.min_col) for row in range(self.min_row, self.max_row+1)] + + + @property + def right(self): + """A list of cell coordinates that comprise the right-side of the range""" + return [(row, self.max_col) for row in range(self.min_row, self.max_row+1)] + + +class MultiCellRange(Strict): + + + ranges = UniqueSequence(expected_type=CellRange) + + + def __init__(self, ranges=set()): + if isinstance(ranges, str): + ranges = [CellRange(r) for r in ranges.split()] + self.ranges = set(ranges) + + + def __contains__(self, coord): + if isinstance(coord, str): + coord = CellRange(coord) + for r in self.ranges: + if coord <= r: + return True + return False + + + def __repr__(self): + ranges = " ".join([str(r) for r in self.sorted()]) + return f"<{self.__class__.__name__} [{ranges}]>" + + + def __str__(self): + ranges = u" ".join([str(r) for r in self.sorted()]) + return ranges + + + def __hash__(self): + return hash(str(self)) + + + def sorted(self): + """ + Return a sorted list of items + """ + return sorted(self.ranges, key=attrgetter('min_col', 'min_row', 'max_col', 'max_row')) + + + def add(self, coord): + """ + Add a cell coordinate or CellRange + """ + cr = coord + if isinstance(coord, str): + cr = CellRange(coord) + elif not isinstance(coord, CellRange): + raise ValueError("You can only add CellRanges") + if cr not in self: + self.ranges.add(cr) + + + def __iadd__(self, coord): + self.add(coord) + return self + + + def __eq__(self, other): + if isinstance(other, str): + other = self.__class__(other) + return self.ranges == other.ranges + + + def __ne__(self, other): + return not self == other + + + def __bool__(self): + return bool(self.ranges) + + + def remove(self, coord): + if not isinstance(coord, CellRange): + coord = CellRange(coord) + self.ranges.remove(coord) + + + def __iter__(self): + for cr in self.ranges: + yield cr + + + def __copy__(self): + ranges = {copy(r) for r in self.ranges} + return MultiCellRange(ranges) diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/cell_watch.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/cell_watch.py new file mode 100644 index 00000000..dea89caf --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/cell_watch.py @@ -0,0 +1,34 @@ +#Autogenerated schema +from openpyxl.descriptors.serialisable import Serialisable +from openpyxl.descriptors import ( + Sequence, + String, +) + +# could be done using a nestedSequence + +class CellWatch(Serialisable): + + tagname = "cellWatch" + + r = String() + + def __init__(self, + r=None, + ): + self.r = r + + +class CellWatches(Serialisable): + + tagname = "cellWatches" + + cellWatch = Sequence(expected_type=CellWatch) + + __elements__ = ('cellWatch',) + + def __init__(self, + cellWatch=(), + ): + self.cellWatch = cellWatch + diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/controls.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/controls.py new file mode 100644 index 00000000..f1fd1c9e --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/controls.py @@ -0,0 +1,107 @@ +# Copyright (c) 2010-2024 openpyxl + +from openpyxl.descriptors.serialisable import Serialisable +from openpyxl.descriptors import ( + Typed, + Bool, + Integer, + String, + Sequence, +) + +from openpyxl.descriptors.excel import Relation +from .ole import ObjectAnchor + + +class ControlProperty(Serialisable): + + tagname = "controlPr" + + anchor = Typed(expected_type=ObjectAnchor, ) + locked = Bool(allow_none=True) + defaultSize = Bool(allow_none=True) + _print = Bool(allow_none=True) + disabled = Bool(allow_none=True) + recalcAlways = Bool(allow_none=True) + uiObject = Bool(allow_none=True) + autoFill = Bool(allow_none=True) + autoLine = Bool(allow_none=True) + autoPict = Bool(allow_none=True) + macro = String(allow_none=True) + altText = String(allow_none=True) + linkedCell = String(allow_none=True) + listFillRange = String(allow_none=True) + cf = String(allow_none=True) + id = Relation(allow_none=True) + + __elements__ = ('anchor',) + + def __init__(self, + anchor=None, + locked=True, + defaultSize=True, + _print=True, + disabled=False, + recalcAlways=False, + uiObject=False, + autoFill=True, + autoLine=True, + autoPict=True, + macro=None, + altText=None, + linkedCell=None, + listFillRange=None, + cf='pict', + id=None, + ): + self.anchor = anchor + self.locked = locked + self.defaultSize = defaultSize + self._print = _print + self.disabled = disabled + self.recalcAlways = recalcAlways + self.uiObject = uiObject + self.autoFill = autoFill + self.autoLine = autoLine + self.autoPict = autoPict + self.macro = macro + self.altText = altText + self.linkedCell = linkedCell + self.listFillRange = listFillRange + self.cf = cf + self.id = id + + +class Control(Serialisable): + + tagname = "control" + + controlPr = Typed(expected_type=ControlProperty, allow_none=True) + shapeId = Integer() + name = String(allow_none=True) + + __elements__ = ('controlPr',) + + def __init__(self, + controlPr=None, + shapeId=None, + name=None, + ): + self.controlPr = controlPr + self.shapeId = shapeId + self.name = name + + +class Controls(Serialisable): + + tagname = "controls" + + control = Sequence(expected_type=Control) + + __elements__ = ('control',) + + def __init__(self, + control=(), + ): + self.control = control + diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/copier.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/copier.py new file mode 100644 index 00000000..f6601540 --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/copier.py @@ -0,0 +1,70 @@ +# Copyright (c) 2010-2024 openpyxl + +#standard lib imports +from copy import copy + +from .worksheet import Worksheet + + +class WorksheetCopy: + """ + Copy the values, styles, dimensions, merged cells, margins, and + print/page setup from one worksheet to another within the same + workbook. + """ + + def __init__(self, source_worksheet, target_worksheet): + self.source = source_worksheet + self.target = target_worksheet + self._verify_resources() + + + def _verify_resources(self): + + if (not isinstance(self.source, Worksheet) + and not isinstance(self.target, Worksheet)): + raise TypeError("Can only copy worksheets") + + if self.source is self.target: + raise ValueError("Cannot copy a worksheet to itself") + + if self.source.parent != self.target.parent: + raise ValueError('Cannot copy between worksheets from different workbooks') + + + def copy_worksheet(self): + self._copy_cells() + self._copy_dimensions() + + self.target.sheet_format = copy(self.source.sheet_format) + self.target.sheet_properties = copy(self.source.sheet_properties) + self.target.merged_cells = copy(self.source.merged_cells) + self.target.page_margins = copy(self.source.page_margins) + self.target.page_setup = copy(self.source.page_setup) + self.target.print_options = copy(self.source.print_options) + + + def _copy_cells(self): + for (row, col), source_cell in self.source._cells.items(): + target_cell = self.target.cell(column=col, row=row) + + target_cell._value = source_cell._value + target_cell.data_type = source_cell.data_type + + if source_cell.has_style: + target_cell._style = copy(source_cell._style) + + if source_cell.hyperlink: + target_cell._hyperlink = copy(source_cell.hyperlink) + + if source_cell.comment: + target_cell.comment = copy(source_cell.comment) + + + def _copy_dimensions(self): + for attr in ('row_dimensions', 'column_dimensions'): + src = getattr(self.source, attr) + target = getattr(self.target, attr) + for key, dim in src.items(): + target[key] = copy(dim) + target[key].worksheet = self.target diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/custom.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/custom.py new file mode 100644 index 00000000..b3af5c91 --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/custom.py @@ -0,0 +1,35 @@ +#Autogenerated schema +from openpyxl.descriptors.serialisable import Serialisable +from openpyxl.descriptors import ( + String, + Sequence, +) + +# can be done with a nested sequence + + +class CustomProperty(Serialisable): + + tagname = "customProperty" + + name = String() + + def __init__(self, + name=None, + ): + self.name = name + + +class CustomProperties(Serialisable): + + tagname = "customProperties" + + customPr = Sequence(expected_type=CustomProperty) + + __elements__ = ('customPr',) + + def __init__(self, + customPr=(), + ): + self.customPr = customPr + diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/datavalidation.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/datavalidation.py new file mode 100644 index 00000000..f5077d97 --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/datavalidation.py @@ -0,0 +1,202 @@ +# Copyright (c) 2010-2024 openpyxl + +from collections import defaultdict +from itertools import chain +from operator import itemgetter + +from openpyxl.descriptors.serialisable import Serialisable +from openpyxl.descriptors import ( + Bool, + NoneSet, + String, + Sequence, + Alias, + Integer, + Convertible, +) +from openpyxl.descriptors.nested import NestedText + +from openpyxl.utils import ( + rows_from_range, + coordinate_to_tuple, + get_column_letter, +) + + +def collapse_cell_addresses(cells, input_ranges=()): + """ Collapse a collection of cell co-ordinates down into an optimal + range or collection of ranges. + + E.g. Cells A1, A2, A3, B1, B2 and B3 should have the data-validation + object applied, attempt to collapse down to a single range, A1:B3. + + Currently only collapsing contiguous vertical ranges (i.e. above + example results in A1:A3 B1:B3). + """ + + ranges = list(input_ranges) + + # convert cell into row, col tuple + raw_coords = (coordinate_to_tuple(cell) for cell in cells) + + # group by column in order + grouped_coords = defaultdict(list) + for row, col in sorted(raw_coords, key=itemgetter(1)): + grouped_coords[col].append(row) + + # create range string from first and last row in column + for col, cells in grouped_coords.items(): + col = get_column_letter(col) + fmt = "{0}{1}:{2}{3}" + if len(cells) == 1: + fmt = "{0}{1}" + r = fmt.format(col, min(cells), col, max(cells)) + ranges.append(r) + + return " ".join(ranges) + + +def expand_cell_ranges(range_string): + """ + Expand cell ranges to a sequence of addresses. + Reverse of collapse_cell_addresses + Eg. converts "A1:A2 B1:B2" to (A1, A2, B1, B2) + """ + # expand ranges to rows and then flatten + rows = (rows_from_range(rs) for rs in range_string.split()) # list of rows + cells = (chain(*row) for row in rows) # flatten rows + return set(chain(*cells)) + + +from .cell_range import MultiCellRange + + +class DataValidation(Serialisable): + + tagname = "dataValidation" + + sqref = Convertible(expected_type=MultiCellRange) + cells = Alias("sqref") + ranges = Alias("sqref") + + showDropDown = Bool(allow_none=True) + hide_drop_down = Alias('showDropDown') + showInputMessage = Bool(allow_none=True) + showErrorMessage = Bool(allow_none=True) + allowBlank = Bool(allow_none=True) + allow_blank = Alias('allowBlank') + + errorTitle = String(allow_none = True) + error = String(allow_none = True) + promptTitle = String(allow_none = True) + prompt = String(allow_none = True) + formula1 = NestedText(allow_none=True, expected_type=str) + formula2 = NestedText(allow_none=True, expected_type=str) + + type = NoneSet(values=("whole", "decimal", "list", "date", "time", + "textLength", "custom")) + errorStyle = NoneSet(values=("stop", "warning", "information")) + imeMode = NoneSet(values=("noControl", "off", "on", "disabled", + "hiragana", "fullKatakana", "halfKatakana", "fullAlpha","halfAlpha", + "fullHangul", "halfHangul")) + operator = NoneSet(values=("between", "notBetween", "equal", "notEqual", + "lessThan", "lessThanOrEqual", "greaterThan", "greaterThanOrEqual")) + validation_type = Alias('type') + + def __init__(self, + type=None, + formula1=None, + formula2=None, + showErrorMessage=False, + showInputMessage=False, + showDropDown=False, + allowBlank=False, + sqref=(), + promptTitle=None, + errorStyle=None, + error=None, + prompt=None, + errorTitle=None, + imeMode=None, + operator=None, + allow_blank=None, + ): + self.sqref = sqref + self.showDropDown = showDropDown + self.imeMode = imeMode + self.operator = operator + self.formula1 = formula1 + self.formula2 = formula2 + if allow_blank is not None: + allowBlank = allow_blank + self.allowBlank = allowBlank + self.showErrorMessage = showErrorMessage + self.showInputMessage = showInputMessage + self.type = type + self.promptTitle = promptTitle + self.errorStyle = errorStyle + self.error = error + self.prompt = prompt + self.errorTitle = errorTitle + + + def add(self, cell): + """Adds a cell or cell coordinate to this validator""" + if hasattr(cell, "coordinate"): + cell = cell.coordinate + self.sqref += cell + + + def __contains__(self, cell): + if hasattr(cell, "coordinate"): + cell = cell.coordinate + return cell in self.sqref + + +class DataValidationList(Serialisable): + + tagname = "dataValidations" + + disablePrompts = Bool(allow_none=True) + xWindow = Integer(allow_none=True) + yWindow = Integer(allow_none=True) + dataValidation = Sequence(expected_type=DataValidation) + + __elements__ = ('dataValidation',) + __attrs__ = ('disablePrompts', 'xWindow', 'yWindow', 'count') + + def __init__(self, + disablePrompts=None, + xWindow=None, + yWindow=None, + count=None, + dataValidation=(), + ): + self.disablePrompts = disablePrompts + self.xWindow = xWindow + self.yWindow = yWindow + self.dataValidation = dataValidation + + + @property + def count(self): + return len(self) + + + def __len__(self): + return len(self.dataValidation) + + + def append(self, dv): + self.dataValidation.append(dv) + + + def to_tree(self, tagname=None): + """ + Need to skip validations that have no cell ranges + """ + ranges = self.dataValidation # copy + self.dataValidation = [r for r in self.dataValidation if bool(r.sqref)] + xml = super().to_tree(tagname) + self.dataValidation = ranges + return xml diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/dimensions.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/dimensions.py new file mode 100644 index 00000000..482717a1 --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/dimensions.py @@ -0,0 +1,306 @@ +# Copyright (c) 2010-2024 openpyxl + +from copy import copy + +from openpyxl.compat import safe_string +from openpyxl.utils import ( + get_column_letter, + get_column_interval, + column_index_from_string, + range_boundaries, +) +from openpyxl.utils.units import DEFAULT_COLUMN_WIDTH +from openpyxl.descriptors import ( + Integer, + Float, + Bool, + Strict, + String, + Alias, +) +from openpyxl.descriptors.serialisable import Serialisable +from openpyxl.styles.styleable import StyleableObject +from openpyxl.utils.bound_dictionary import BoundDictionary +from openpyxl.xml.functions import Element + + +class Dimension(Strict, StyleableObject): + """Information about the display properties of a row or column.""" + __fields__ = ('hidden', + 'outlineLevel', + 'collapsed',) + + index = Integer() + hidden = Bool() + outlineLevel = Integer(allow_none=True) + outline_level = Alias('outlineLevel') + collapsed = Bool() + style = Alias('style_id') + + + def __init__(self, index, hidden, outlineLevel, + collapsed, worksheet, visible=True, style=None): + super().__init__(sheet=worksheet, style_array=style) + self.index = index + self.hidden = hidden + self.outlineLevel = outlineLevel + self.collapsed = collapsed + + + def __iter__(self): + for key in self.__fields__: + value = getattr(self, key, None) + if value: + yield key, safe_string(value) + + + def __copy__(self): + cp = self.__new__(self.__class__) + attrib = self.__dict__ + attrib['worksheet'] = self.parent + cp.__init__(**attrib) + cp._style = copy(self._style) + return cp + + + def __repr__(self): + return f"<{self.__class__.__name__} Instance, Attributes={dict(self)}>" + + +class RowDimension(Dimension): + """Information about the display properties of a row.""" + + __fields__ = Dimension.__fields__ + ('ht', 'customFormat', 'customHeight', 's', + 'thickBot', 'thickTop') + r = Alias('index') + s = Alias('style_id') + ht = Float(allow_none=True) + height = Alias('ht') + thickBot = Bool() + thickTop = Bool() + + def __init__(self, + worksheet, + index=0, + ht=None, + customHeight=None, # do not write + s=None, + customFormat=None, # do not write + hidden=False, + outlineLevel=0, + outline_level=None, + collapsed=False, + visible=None, + height=None, + r=None, + spans=None, + thickBot=None, + thickTop=None, + **kw + ): + if r is not None: + index = r + if height is not None: + ht = height + self.ht = ht + if visible is not None: + hidden = not visible + if outline_level is not None: + outlineLevel = outline_level + self.thickBot = thickBot + self.thickTop = thickTop + super().__init__(index, hidden, outlineLevel, + collapsed, worksheet, style=s) + + @property + def customFormat(self): + """Always true if there is a style for the row""" + return self.has_style + + @property + def customHeight(self): + """Always true if there is a height for the row""" + return self.ht is not None + + +class ColumnDimension(Dimension): + """Information about the display properties of a column.""" + + width = Float() + bestFit = Bool() + auto_size = Alias('bestFit') + index = String() + min = Integer(allow_none=True) + max = Integer(allow_none=True) + collapsed = Bool() + + __fields__ = Dimension.__fields__ + ('width', 'bestFit', 'customWidth', 'style', + 'min', 'max') + + def __init__(self, + worksheet, + index='A', + width=DEFAULT_COLUMN_WIDTH, + bestFit=False, + hidden=False, + outlineLevel=0, + outline_level=None, + collapsed=False, + style=None, + min=None, + max=None, + customWidth=False, # do not write + visible=None, + auto_size=None,): + self.width = width + self.min = min + self.max = max + if visible is not None: + hidden = not visible + if auto_size is not None: + bestFit = auto_size + self.bestFit = bestFit + if outline_level is not None: + outlineLevel = outline_level + self.collapsed = collapsed + super().__init__(index, hidden, outlineLevel, + collapsed, worksheet, style=style) + + + @property + def customWidth(self): + """Always true if there is a width for the column""" + return bool(self.width) + + + def reindex(self): + """ + Set boundaries for column definition + """ + if not all([self.min, self.max]): + self.min = self.max = column_index_from_string(self.index) + + @property + def range(self): + """Return the range of cells actually covered""" + return f"{get_column_letter(self.min)}:{get_column_letter(self.max)}" + + + def to_tree(self): + attrs = dict(self) + if attrs.keys() != {'min', 'max'}: + return Element("col", **attrs) + + +class DimensionHolder(BoundDictionary): + """ + Allow columns to be grouped + """ + + def __init__(self, worksheet, reference="index", default_factory=None): + self.worksheet = worksheet + self.max_outline = None + self.default_factory = default_factory + super().__init__(reference, default_factory) + + + def group(self, start, end=None, outline_level=1, hidden=False): + """allow grouping a range of consecutive rows or columns together + + :param start: first row or column to be grouped (mandatory) + :param end: last row or column to be grouped (optional, default to start) + :param outline_level: outline level + :param hidden: should the group be hidden on workbook open or not + """ + if end is None: + end = start + + if isinstance(self.default_factory(), ColumnDimension): + new_dim = self[start] + new_dim.outline_level = outline_level + new_dim.hidden = hidden + work_sequence = get_column_interval(start, end)[1:] + for column_letter in work_sequence: + if column_letter in self: + del self[column_letter] + new_dim.min, new_dim.max = map(column_index_from_string, (start, end)) + elif isinstance(self.default_factory(), RowDimension): + for el in range(start, end + 1): + new_dim = self.worksheet.row_dimensions[el] + new_dim.outline_level = outline_level + new_dim.hidden = hidden + + + def to_tree(self): + + def sorter(value): + value.reindex() + return value.min + + el = Element('cols') + outlines = set() + + for col in sorted(self.values(), key=sorter): + obj = col.to_tree() + if obj is not None: + outlines.add(col.outlineLevel) + el.append(obj) + + if outlines: + self.max_outline = max(outlines) + + if len(el): + return el # must have at least one child + + +class SheetFormatProperties(Serialisable): + + tagname = "sheetFormatPr" + + baseColWidth = Integer(allow_none=True) + defaultColWidth = Float(allow_none=True) + defaultRowHeight = Float() + customHeight = Bool(allow_none=True) + zeroHeight = Bool(allow_none=True) + thickTop = Bool(allow_none=True) + thickBottom = Bool(allow_none=True) + outlineLevelRow = Integer(allow_none=True) + outlineLevelCol = Integer(allow_none=True) + + def __init__(self, + baseColWidth=8, #according to spec + defaultColWidth=None, + defaultRowHeight=15, + customHeight=None, + zeroHeight=None, + thickTop=None, + thickBottom=None, + outlineLevelRow=None, + outlineLevelCol=None, + ): + self.baseColWidth = baseColWidth + self.defaultColWidth = defaultColWidth + self.defaultRowHeight = defaultRowHeight + self.customHeight = customHeight + self.zeroHeight = zeroHeight + self.thickTop = thickTop + self.thickBottom = thickBottom + self.outlineLevelRow = outlineLevelRow + self.outlineLevelCol = outlineLevelCol + + +class SheetDimension(Serialisable): + + tagname = "dimension" + + ref = String() + + def __init__(self, + ref=None, + ): + self.ref = ref + + + @property + def boundaries(self): + return range_boundaries(self.ref) diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/drawing.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/drawing.py new file mode 100644 index 00000000..45bf4d35 --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/drawing.py @@ -0,0 +1,14 @@ +# Copyright (c) 2010-2024 openpyxl + +from openpyxl.descriptors.serialisable import Serialisable +from openpyxl.descriptors.excel import Relation + + +class Drawing(Serialisable): + + tagname = "drawing" + + id = Relation() + + def __init__(self, id=None): + self.id = id diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/errors.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/errors.py new file mode 100644 index 00000000..1bed3f78 --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/errors.py @@ -0,0 +1,93 @@ +#Autogenerated schema +from openpyxl.descriptors.serialisable import Serialisable +from openpyxl.descriptors import ( + Typed, + String, + Bool, + Sequence, +) +from openpyxl.descriptors.excel import CellRange + + +class Extension(Serialisable): + + tagname = "extension" + + uri = String(allow_none=True) + + def __init__(self, + uri=None, + ): + self.uri = uri + + +class ExtensionList(Serialisable): + + tagname = "extensionList" + + # uses element group EG_ExtensionList + ext = Sequence(expected_type=Extension) + + __elements__ = ('ext',) + + def __init__(self, + ext=(), + ): + self.ext = ext + + +class IgnoredError(Serialisable): + + tagname = "ignoredError" + + sqref = CellRange + evalError = Bool(allow_none=True) + twoDigitTextYear = Bool(allow_none=True) + numberStoredAsText = Bool(allow_none=True) + formula = Bool(allow_none=True) + formulaRange = Bool(allow_none=True) + unlockedFormula = Bool(allow_none=True) + emptyCellReference = Bool(allow_none=True) + listDataValidation = Bool(allow_none=True) + calculatedColumn = Bool(allow_none=True) + + def __init__(self, + sqref=None, + evalError=False, + twoDigitTextYear=False, + numberStoredAsText=False, + formula=False, + formulaRange=False, + unlockedFormula=False, + emptyCellReference=False, + listDataValidation=False, + calculatedColumn=False, + ): + self.sqref = sqref + self.evalError = evalError + self.twoDigitTextYear = twoDigitTextYear + self.numberStoredAsText = numberStoredAsText + self.formula = formula + self.formulaRange = formulaRange + self.unlockedFormula = unlockedFormula + self.emptyCellReference = emptyCellReference + self.listDataValidation = listDataValidation + self.calculatedColumn = calculatedColumn + + +class IgnoredErrors(Serialisable): + + tagname = "ignoredErrors" + + ignoredError = Sequence(expected_type=IgnoredError) + extLst = Typed(expected_type=ExtensionList, allow_none=True) + + __elements__ = ('ignoredError', 'extLst') + + def __init__(self, + ignoredError=(), + extLst=None, + ): + self.ignoredError = ignoredError + self.extLst = extLst + diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/filters.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/filters.py new file mode 100644 index 00000000..a2cfd8eb --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/filters.py @@ -0,0 +1,486 @@ +# Copyright (c) 2010-2024 openpyxl + +import re + +from openpyxl.descriptors.serialisable import Serialisable +from openpyxl.descriptors import ( + Alias, + Typed, + Set, + Float, + DateTime, + NoneSet, + Bool, + Integer, + String, + Sequence, + MinMax, +) +from openpyxl.descriptors.excel import ExtensionList, CellRange +from openpyxl.descriptors.sequence import ValueSequence +from openpyxl.utils import absolute_coordinate + + +class SortCondition(Serialisable): + + tagname = "sortCondition" + + descending = Bool(allow_none=True) + sortBy = NoneSet(values=(['value', 'cellColor', 'fontColor', 'icon'])) + ref = CellRange() + customList = String(allow_none=True) + dxfId = Integer(allow_none=True) + iconSet = NoneSet(values=(['3Arrows', '3ArrowsGray', '3Flags', + '3TrafficLights1', '3TrafficLights2', '3Signs', '3Symbols', '3Symbols2', + '4Arrows', '4ArrowsGray', '4RedToBlack', '4Rating', '4TrafficLights', + '5Arrows', '5ArrowsGray', '5Rating', '5Quarters'])) + iconId = Integer(allow_none=True) + + def __init__(self, + ref=None, + descending=None, + sortBy=None, + customList=None, + dxfId=None, + iconSet=None, + iconId=None, + ): + self.descending = descending + self.sortBy = sortBy + self.ref = ref + self.customList = customList + self.dxfId = dxfId + self.iconSet = iconSet + self.iconId = iconId + + +class SortState(Serialisable): + + tagname = "sortState" + + columnSort = Bool(allow_none=True) + caseSensitive = Bool(allow_none=True) + sortMethod = NoneSet(values=(['stroke', 'pinYin'])) + ref = CellRange() + sortCondition = Sequence(expected_type=SortCondition, allow_none=True) + extLst = Typed(expected_type=ExtensionList, allow_none=True) + + __elements__ = ('sortCondition',) + + def __init__(self, + columnSort=None, + caseSensitive=None, + sortMethod=None, + ref=None, + sortCondition=(), + extLst=None, + ): + self.columnSort = columnSort + self.caseSensitive = caseSensitive + self.sortMethod = sortMethod + self.ref = ref + self.sortCondition = sortCondition + + + def __bool__(self): + return self.ref is not None + + + +class IconFilter(Serialisable): + + tagname = "iconFilter" + + iconSet = Set(values=(['3Arrows', '3ArrowsGray', '3Flags', + '3TrafficLights1', '3TrafficLights2', '3Signs', '3Symbols', '3Symbols2', + '4Arrows', '4ArrowsGray', '4RedToBlack', '4Rating', '4TrafficLights', + '5Arrows', '5ArrowsGray', '5Rating', '5Quarters'])) + iconId = Integer(allow_none=True) + + def __init__(self, + iconSet=None, + iconId=None, + ): + self.iconSet = iconSet + self.iconId = iconId + + +class ColorFilter(Serialisable): + + tagname = "colorFilter" + + dxfId = Integer(allow_none=True) + cellColor = Bool(allow_none=True) + + def __init__(self, + dxfId=None, + cellColor=None, + ): + self.dxfId = dxfId + self.cellColor = cellColor + + +class DynamicFilter(Serialisable): + + tagname = "dynamicFilter" + + type = Set(values=(['null', 'aboveAverage', 'belowAverage', 'tomorrow', + 'today', 'yesterday', 'nextWeek', 'thisWeek', 'lastWeek', 'nextMonth', + 'thisMonth', 'lastMonth', 'nextQuarter', 'thisQuarter', 'lastQuarter', + 'nextYear', 'thisYear', 'lastYear', 'yearToDate', 'Q1', 'Q2', 'Q3', 'Q4', + 'M1', 'M2', 'M3', 'M4', 'M5', 'M6', 'M7', 'M8', 'M9', 'M10', 'M11', + 'M12'])) + val = Float(allow_none=True) + valIso = DateTime(allow_none=True) + maxVal = Float(allow_none=True) + maxValIso = DateTime(allow_none=True) + + def __init__(self, + type=None, + val=None, + valIso=None, + maxVal=None, + maxValIso=None, + ): + self.type = type + self.val = val + self.valIso = valIso + self.maxVal = maxVal + self.maxValIso = maxValIso + + +class CustomFilter(Serialisable): + + tagname = "customFilter" + + val = String() + operator = Set(values=['equal', 'lessThan', 'lessThanOrEqual', + 'notEqual', 'greaterThanOrEqual', 'greaterThan']) + + def __init__(self, operator="equal", val=None): + self.operator = operator + self.val = val + + + def _get_subtype(self): + if self.val == " ": + subtype = BlankFilter + else: + try: + float(self.val) + subtype = NumberFilter + except ValueError: + subtype = StringFilter + return subtype + + + def convert(self): + """Convert to more specific filter""" + typ = self._get_subtype() + if typ in (BlankFilter, NumberFilter): + return typ(**dict(self)) + + operator, term = StringFilter._guess_operator(self.val) + flt = StringFilter(operator, term) + if self.operator == "notEqual": + flt.exclude = True + return flt + + +class BlankFilter(CustomFilter): + """ + Exclude blanks + """ + + __attrs__ = ("operator", "val") + + def __init__(self, **kw): + pass + + + @property + def operator(self): + return "notEqual" + + + @property + def val(self): + return " " + + +class NumberFilter(CustomFilter): + + + operator = Set(values= + ['equal', 'lessThan', 'lessThanOrEqual', + 'notEqual', 'greaterThanOrEqual', 'greaterThan']) + val = Float() + + def __init__(self, operator="equal", val=None): + self.operator = operator + self.val = val + + +string_format_mapping = { + "contains": "*{}*", + "startswith": "{}*", + "endswith": "*{}", + "wildcard": "{}", +} + + +class StringFilter(CustomFilter): + + operator = Set(values=['contains', 'startswith', 'endswith', 'wildcard'] + ) + val = String() + exclude = Bool() + + + def __init__(self, operator="contains", val=None, exclude=False): + self.operator = operator + self.val = val + self.exclude = exclude + + + def _escape(self): + """Escape wildcards ~, * ? when serialising""" + if self.operator == "wildcard": + return self.val + return re.sub(r"~|\*|\?", r"~\g<0>", self.val) + + + @staticmethod + def _unescape(value): + """ + Unescape value + """ + return re.sub(r"~(?P<op>[~*?])", r"\g<op>", value) + + + @staticmethod + def _guess_operator(value): + value = StringFilter._unescape(value) + endswith = r"^(?P<endswith>\*)(?P<term>[^\*\?]*$)" + startswith = r"^(?P<term>[^\*\?]*)(?P<startswith>\*)$" + contains = r"^(?P<contains>\*)(?P<term>[^\*\?]*)\*$" + d = {"wildcard": True, "term": value} + for pat in [contains, startswith, endswith]: + m = re.match(pat, value) + if m: + d = m.groupdict() + + term = d.pop("term") + op = list(d)[0] + return op, term + + + def to_tree(self, tagname=None, idx=None, namespace=None): + fmt = string_format_mapping[self.operator] + op = self.exclude and "notEqual" or "equal" + value = fmt.format(self._escape()) + flt = CustomFilter(op, value) + return flt.to_tree(tagname, idx, namespace) + + +class CustomFilters(Serialisable): + + tagname = "customFilters" + + _and = Bool(allow_none=True) + customFilter = Sequence(expected_type=CustomFilter) # min 1, max 2 + + __elements__ = ('customFilter',) + + def __init__(self, + _and=None, + customFilter=(), + ): + self._and = _and + self.customFilter = customFilter + + +class Top10(Serialisable): + + tagname = "top10" + + top = Bool(allow_none=True) + percent = Bool(allow_none=True) + val = Float() + filterVal = Float(allow_none=True) + + def __init__(self, + top=None, + percent=None, + val=None, + filterVal=None, + ): + self.top = top + self.percent = percent + self.val = val + self.filterVal = filterVal + + +class DateGroupItem(Serialisable): + + tagname = "dateGroupItem" + + year = Integer() + month = MinMax(min=1, max=12, allow_none=True) + day = MinMax(min=1, max=31, allow_none=True) + hour = MinMax(min=0, max=23, allow_none=True) + minute = MinMax(min=0, max=59, allow_none=True) + second = Integer(min=0, max=59, allow_none=True) + dateTimeGrouping = Set(values=(['year', 'month', 'day', 'hour', 'minute', + 'second'])) + + def __init__(self, + year=None, + month=None, + day=None, + hour=None, + minute=None, + second=None, + dateTimeGrouping=None, + ): + self.year = year + self.month = month + self.day = day + self.hour = hour + self.minute = minute + self.second = second + self.dateTimeGrouping = dateTimeGrouping + + +class Filters(Serialisable): + + tagname = "filters" + + blank = Bool(allow_none=True) + calendarType = NoneSet(values=["gregorian","gregorianUs", + "gregorianMeFrench","gregorianArabic", "hijri","hebrew", + "taiwan","japan", "thai","korea", + "saka","gregorianXlitEnglish","gregorianXlitFrench"]) + filter = ValueSequence(expected_type=str) + dateGroupItem = Sequence(expected_type=DateGroupItem, allow_none=True) + + __elements__ = ('filter', 'dateGroupItem') + + def __init__(self, + blank=None, + calendarType=None, + filter=(), + dateGroupItem=(), + ): + self.blank = blank + self.calendarType = calendarType + self.filter = filter + self.dateGroupItem = dateGroupItem + + +class FilterColumn(Serialisable): + + tagname = "filterColumn" + + colId = Integer() + col_id = Alias('colId') + hiddenButton = Bool(allow_none=True) + showButton = Bool(allow_none=True) + # some elements are choice + filters = Typed(expected_type=Filters, allow_none=True) + top10 = Typed(expected_type=Top10, allow_none=True) + customFilters = Typed(expected_type=CustomFilters, allow_none=True) + dynamicFilter = Typed(expected_type=DynamicFilter, allow_none=True) + colorFilter = Typed(expected_type=ColorFilter, allow_none=True) + iconFilter = Typed(expected_type=IconFilter, allow_none=True) + extLst = Typed(expected_type=ExtensionList, allow_none=True) + + __elements__ = ('filters', 'top10', 'customFilters', 'dynamicFilter', + 'colorFilter', 'iconFilter') + + def __init__(self, + colId=None, + hiddenButton=False, + showButton=True, + filters=None, + top10=None, + customFilters=None, + dynamicFilter=None, + colorFilter=None, + iconFilter=None, + extLst=None, + blank=None, + vals=None, + ): + self.colId = colId + self.hiddenButton = hiddenButton + self.showButton = showButton + self.filters = filters + self.top10 = top10 + self.customFilters = customFilters + self.dynamicFilter = dynamicFilter + self.colorFilter = colorFilter + self.iconFilter = iconFilter + if blank is not None and self.filters: + self.filters.blank = blank + if vals is not None and self.filters: + self.filters.filter = vals + + +class AutoFilter(Serialisable): + + tagname = "autoFilter" + + ref = CellRange() + filterColumn = Sequence(expected_type=FilterColumn, allow_none=True) + sortState = Typed(expected_type=SortState, allow_none=True) + extLst = Typed(expected_type=ExtensionList, allow_none=True) + + __elements__ = ('filterColumn', 'sortState') + + def __init__(self, + ref=None, + filterColumn=(), + sortState=None, + extLst=None, + ): + self.ref = ref + self.filterColumn = filterColumn + self.sortState = sortState + + + def __bool__(self): + return self.ref is not None + + + def __str__(self): + return absolute_coordinate(self.ref) + + + def add_filter_column(self, col_id, vals, blank=False): + """ + Add row filter for specified column. + + :param col_id: Zero-origin column id. 0 means first column. + :type col_id: int + :param vals: Value list to show. + :type vals: str[] + :param blank: Show rows that have blank cell if True (default=``False``) + :type blank: bool + """ + self.filterColumn.append(FilterColumn(colId=col_id, filters=Filters(blank=blank, filter=vals))) + + + def add_sort_condition(self, ref, descending=False): + """ + Add sort condition for cpecified range of cells. + + :param ref: range of the cells (e.g. 'A2:A150') + :type ref: string, is the same as that of the filter + :param descending: Descending sort order (default=``False``) + :type descending: bool + """ + cond = SortCondition(ref, descending) + if self.sortState is None: + self.sortState = SortState(ref=self.ref) + self.sortState.sortCondition.append(cond) diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/formula.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/formula.py new file mode 100644 index 00000000..7eb920e9 --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/formula.py @@ -0,0 +1,51 @@ +# Copyright (c) 2010-2024 openpyxl + +from openpyxl.compat import safe_string + +class DataTableFormula: + + + t = "dataTable" + + def __init__(self, + ref, + ca=False, + dt2D=False, + dtr=False, + r1=None, + r2=None, + del1=False, + del2=False, + **kw): + self.ref = ref + self.ca = ca + self.dt2D = dt2D + self.dtr = dtr + self.r1 = r1 + self.r2 = r2 + self.del1 = del1 + self.del2 = del2 + + + def __iter__(self): + for k in ["t", "ref", "dt2D", "dtr", "r1", "r2", "del1", "del2", "ca"]: + v = getattr(self, k) + if v: + yield k, safe_string(v) + + +class ArrayFormula: + + t = "array" + + + def __init__(self, ref, text=None): + self.ref = ref + self.text = text + + + def __iter__(self): + for k in ["t", "ref"]: + v = getattr(self, k) + if v: + yield k, safe_string(v) diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/header_footer.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/header_footer.py new file mode 100644 index 00000000..598aa23d --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/header_footer.py @@ -0,0 +1,270 @@ +# Copyright (c) 2010-2024 openpyxl + +# Simplified implementation of headers and footers: let worksheets have separate items + +import re +from warnings import warn + +from openpyxl.descriptors import ( + Alias, + Bool, + Strict, + String, + Integer, + MatchPattern, + Typed, +) +from openpyxl.descriptors.serialisable import Serialisable + + +from openpyxl.xml.functions import Element +from openpyxl.utils.escape import escape, unescape + + +FONT_PATTERN = '&"(?P<font>.+)"' +COLOR_PATTERN = "&K(?P<color>[A-F0-9]{6})" +SIZE_REGEX = r"&(?P<size>\d+\s?)" +FORMAT_REGEX = re.compile("{0}|{1}|{2}".format(FONT_PATTERN, COLOR_PATTERN, + SIZE_REGEX) + ) + +def _split_string(text): + """ + Split the combined (decoded) string into left, center and right parts + + # See http://stackoverflow.com/questions/27711175/regex-with-multiple-optional-groups for discussion + """ + + ITEM_REGEX = re.compile(""" + (&L(?P<left>.+?))? + (&C(?P<center>.+?))? + (&R(?P<right>.+?))? + $""", re.VERBOSE | re.DOTALL) + + m = ITEM_REGEX.match(text) + try: + parts = m.groupdict() + except AttributeError: + warn("""Cannot parse header or footer so it will be ignored""") + parts = {'left':'', 'right':'', 'center':''} + return parts + + +class _HeaderFooterPart(Strict): + + """ + Individual left/center/right header/footer part + + Do not use directly. + + Header & Footer ampersand codes: + + * &A Inserts the worksheet name + * &B Toggles bold + * &D or &[Date] Inserts the current date + * &E Toggles double-underline + * &F or &[File] Inserts the workbook name + * &I Toggles italic + * &N or &[Pages] Inserts the total page count + * &S Toggles strikethrough + * &T Inserts the current time + * &[Tab] Inserts the worksheet name + * &U Toggles underline + * &X Toggles superscript + * &Y Toggles subscript + * &P or &[Page] Inserts the current page number + * &P+n Inserts the page number incremented by n + * &P-n Inserts the page number decremented by n + * &[Path] Inserts the workbook path + * && Escapes the ampersand character + * &"fontname" Selects the named font + * &nn Selects the specified 2-digit font point size + + Colours are in RGB Hex + """ + + text = String(allow_none=True) + font = String(allow_none=True) + size = Integer(allow_none=True) + RGB = ("^[A-Fa-f0-9]{6}$") + color = MatchPattern(allow_none=True, pattern=RGB) + + + def __init__(self, text=None, font=None, size=None, color=None): + self.text = text + self.font = font + self.size = size + self.color = color + + + def __str__(self): + """ + Convert to Excel HeaderFooter miniformat minus position + """ + fmt = [] + if self.font: + fmt.append(u'&"{0}"'.format(self.font)) + if self.size: + fmt.append("&{0} ".format(self.size)) + if self.color: + fmt.append("&K{0}".format(self.color)) + return u"".join(fmt + [self.text]) + + def __bool__(self): + return bool(self.text) + + + + @classmethod + def from_str(cls, text): + """ + Convert from miniformat to object + """ + keys = ('font', 'color', 'size') + kw = dict((k, v) for match in FORMAT_REGEX.findall(text) + for k, v in zip(keys, match) if v) + + kw['text'] = FORMAT_REGEX.sub('', text) + + return cls(**kw) + + +class HeaderFooterItem(Strict): + """ + Header or footer item + + """ + + left = Typed(expected_type=_HeaderFooterPart) + center = Typed(expected_type=_HeaderFooterPart) + centre = Alias("center") + right = Typed(expected_type=_HeaderFooterPart) + + __keys = ('L', 'C', 'R') + + + def __init__(self, left=None, right=None, center=None): + if left is None: + left = _HeaderFooterPart() + self.left = left + if center is None: + center = _HeaderFooterPart() + self.center = center + if right is None: + right = _HeaderFooterPart() + self.right = right + + + def __str__(self): + """ + Pack parts into a single string + """ + TRANSFORM = {'&[Tab]': '&A', '&[Pages]': '&N', '&[Date]': '&D', + '&[Path]': '&Z', '&[Page]': '&P', '&[Time]': '&T', '&[File]': '&F', + '&[Picture]': '&G'} + + # escape keys and create regex + SUBS_REGEX = re.compile("|".join(["({0})".format(re.escape(k)) + for k in TRANSFORM])) + + def replace(match): + """ + Callback for re.sub + Replace expanded control with mini-format equivalent + """ + sub = match.group(0) + return TRANSFORM[sub] + + txt = [] + for key, part in zip( + self.__keys, [self.left, self.center, self.right]): + if part.text is not None: + txt.append(u"&{0}{1}".format(key, str(part))) + txt = "".join(txt) + txt = SUBS_REGEX.sub(replace, txt) + return escape(txt) + + + def __bool__(self): + return any([self.left, self.center, self.right]) + + + + def to_tree(self, tagname): + """ + Return as XML node + """ + el = Element(tagname) + el.text = str(self) + return el + + + @classmethod + def from_tree(cls, node): + if node.text: + text = unescape(node.text) + parts = _split_string(text) + for k, v in parts.items(): + if v is not None: + parts[k] = _HeaderFooterPart.from_str(v) + self = cls(**parts) + return self + + +class HeaderFooter(Serialisable): + + tagname = "headerFooter" + + differentOddEven = Bool(allow_none=True) + differentFirst = Bool(allow_none=True) + scaleWithDoc = Bool(allow_none=True) + alignWithMargins = Bool(allow_none=True) + oddHeader = Typed(expected_type=HeaderFooterItem, allow_none=True) + oddFooter = Typed(expected_type=HeaderFooterItem, allow_none=True) + evenHeader = Typed(expected_type=HeaderFooterItem, allow_none=True) + evenFooter = Typed(expected_type=HeaderFooterItem, allow_none=True) + firstHeader = Typed(expected_type=HeaderFooterItem, allow_none=True) + firstFooter = Typed(expected_type=HeaderFooterItem, allow_none=True) + + __elements__ = ("oddHeader", "oddFooter", "evenHeader", "evenFooter", "firstHeader", "firstFooter") + + def __init__(self, + differentOddEven=None, + differentFirst=None, + scaleWithDoc=None, + alignWithMargins=None, + oddHeader=None, + oddFooter=None, + evenHeader=None, + evenFooter=None, + firstHeader=None, + firstFooter=None, + ): + self.differentOddEven = differentOddEven + self.differentFirst = differentFirst + self.scaleWithDoc = scaleWithDoc + self.alignWithMargins = alignWithMargins + if oddHeader is None: + oddHeader = HeaderFooterItem() + self.oddHeader = oddHeader + if oddFooter is None: + oddFooter = HeaderFooterItem() + self.oddFooter = oddFooter + if evenHeader is None: + evenHeader = HeaderFooterItem() + self.evenHeader = evenHeader + if evenFooter is None: + evenFooter = HeaderFooterItem() + self.evenFooter = evenFooter + if firstHeader is None: + firstHeader = HeaderFooterItem() + self.firstHeader = firstHeader + if firstFooter is None: + firstFooter = HeaderFooterItem() + self.firstFooter = firstFooter + + + def __bool__(self): + parts = [getattr(self, attr) for attr in self.__attrs__ + self.__elements__] + return any(parts) + diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/hyperlink.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/hyperlink.py new file mode 100644 index 00000000..332b4154 --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/hyperlink.py @@ -0,0 +1,46 @@ +from openpyxl.descriptors.serialisable import Serialisable +from openpyxl.descriptors import ( + String, + Sequence, +) +from openpyxl.descriptors.excel import Relation + + +class Hyperlink(Serialisable): + + tagname = "hyperlink" + + ref = String() + location = String(allow_none=True) + tooltip = String(allow_none=True) + display = String(allow_none=True) + id = Relation() + target = String(allow_none=True) + + __attrs__ = ("ref", "location", "tooltip", "display", "id") + + def __init__(self, + ref=None, + location=None, + tooltip=None, + display=None, + id=None, + target=None, + ): + self.ref = ref + self.location = location + self.tooltip = tooltip + self.display = display + self.id = id + self.target = target + + +class HyperlinkList(Serialisable): + + tagname = "hyperlinks" + + __expected_type = Hyperlink + hyperlink = Sequence(expected_type=__expected_type) + + def __init__(self, hyperlink=()): + self.hyperlink = hyperlink diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/merge.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/merge.py new file mode 100644 index 00000000..a3a6bebd --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/merge.py @@ -0,0 +1,141 @@ +# Copyright (c) 2010-2024 openpyxl + +import copy + +from openpyxl.descriptors.serialisable import Serialisable +from openpyxl.descriptors import ( + Integer, + Sequence, +) + +from openpyxl.cell.cell import MergedCell +from openpyxl.styles.borders import Border + +from .cell_range import CellRange + + +class MergeCell(CellRange): + + tagname = "mergeCell" + ref = CellRange.coord + + __attrs__ = ("ref",) + + + def __init__(self, + ref=None, + ): + super().__init__(ref) + + + def __copy__(self): + return self.__class__(self.ref) + + +class MergeCells(Serialisable): + + tagname = "mergeCells" + + count = Integer(allow_none=True) + mergeCell = Sequence(expected_type=MergeCell, ) + + __elements__ = ('mergeCell',) + __attrs__ = ('count',) + + def __init__(self, + count=None, + mergeCell=(), + ): + self.mergeCell = mergeCell + + + @property + def count(self): + return len(self.mergeCell) + + +class MergedCellRange(CellRange): + + """ + MergedCellRange stores the border information of a merged cell in the top + left cell of the merged cell. + The remaining cells in the merged cell are stored as MergedCell objects and + get their border information from the upper left cell. + """ + + def __init__(self, worksheet, coord): + self.ws = worksheet + super().__init__(range_string=coord) + self.start_cell = None + self._get_borders() + + + def _get_borders(self): + """ + If the upper left cell of the merged cell does not yet exist, it is + created. + The upper left cell gets the border information of the bottom and right + border from the bottom right cell of the merged cell, if available. + """ + + # Top-left cell. + self.start_cell = self.ws._cells.get((self.min_row, self.min_col)) + if self.start_cell is None: + self.start_cell = self.ws.cell(row=self.min_row, column=self.min_col) + + # Bottom-right cell + end_cell = self.ws._cells.get((self.max_row, self.max_col)) + if end_cell is not None: + self.start_cell.border += Border(right=end_cell.border.right, + bottom=end_cell.border.bottom) + + + def format(self): + """ + Each cell of the merged cell is created as MergedCell if it does not + already exist. + + The MergedCells at the edge of the merged cell gets its borders from + the upper left cell. + + - The top MergedCells get the top border from the top left cell. + - The bottom MergedCells get the bottom border from the top left cell. + - The left MergedCells get the left border from the top left cell. + - The right MergedCells get the right border from the top left cell. + """ + + names = ['top', 'left', 'right', 'bottom'] + + for name in names: + side = getattr(self.start_cell.border, name) + if side and side.style is None: + continue # don't need to do anything if there is no border style + border = Border(**{name:side}) + for coord in getattr(self, name): + cell = self.ws._cells.get(coord) + if cell is None: + row, col = coord + cell = MergedCell(self.ws, row=row, column=col) + self.ws._cells[(cell.row, cell.column)] = cell + cell.border += border + + protected = self.start_cell.protection is not None + if protected: + protection = copy.copy(self.start_cell.protection) + for coord in self.cells: + cell = self.ws._cells.get(coord) + if cell is None: + row, col = coord + cell = MergedCell(self.ws, row=row, column=col) + self.ws._cells[(cell.row, cell.column)] = cell + + if protected: + cell.protection = protection + + + def __contains__(self, coord): + return coord in CellRange(self.coord) + + + def __copy__(self): + return self.__class__(self.ws, self.coord) diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/ole.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/ole.py new file mode 100644 index 00000000..61dc0048 --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/ole.py @@ -0,0 +1,133 @@ +# Copyright (c) 2010-2024 openpyxl + +from openpyxl.descriptors.serialisable import Serialisable +from openpyxl.descriptors import ( + Typed, + Integer, + String, + Set, + Bool, + Sequence, +) + +from openpyxl.drawing.spreadsheet_drawing import AnchorMarker +from openpyxl.xml.constants import SHEET_DRAWING_NS + + +class ObjectAnchor(Serialisable): + + tagname = "anchor" + + _from = Typed(expected_type=AnchorMarker, namespace=SHEET_DRAWING_NS) + to = Typed(expected_type=AnchorMarker, namespace=SHEET_DRAWING_NS) + moveWithCells = Bool(allow_none=True) + sizeWithCells = Bool(allow_none=True) + z_order = Integer(allow_none=True, hyphenated=True) + + + def __init__(self, + _from=None, + to=None, + moveWithCells=False, + sizeWithCells=False, + z_order=None, + ): + self._from = _from + self.to = to + self.moveWithCells = moveWithCells + self.sizeWithCells = sizeWithCells + self.z_order = z_order + + +class ObjectPr(Serialisable): + + tagname = "objectPr" + + anchor = Typed(expected_type=ObjectAnchor, ) + locked = Bool(allow_none=True) + defaultSize = Bool(allow_none=True) + _print = Bool(allow_none=True) + disabled = Bool(allow_none=True) + uiObject = Bool(allow_none=True) + autoFill = Bool(allow_none=True) + autoLine = Bool(allow_none=True) + autoPict = Bool(allow_none=True) + macro = String() + altText = String(allow_none=True) + dde = Bool(allow_none=True) + + __elements__ = ('anchor',) + + def __init__(self, + anchor=None, + locked=True, + defaultSize=True, + _print=True, + disabled=False, + uiObject=False, + autoFill=True, + autoLine=True, + autoPict=True, + macro=None, + altText=None, + dde=False, + ): + self.anchor = anchor + self.locked = locked + self.defaultSize = defaultSize + self._print = _print + self.disabled = disabled + self.uiObject = uiObject + self.autoFill = autoFill + self.autoLine = autoLine + self.autoPict = autoPict + self.macro = macro + self.altText = altText + self.dde = dde + + +class OleObject(Serialisable): + + tagname = "oleObject" + + objectPr = Typed(expected_type=ObjectPr, allow_none=True) + progId = String(allow_none=True) + dvAspect = Set(values=(['DVASPECT_CONTENT', 'DVASPECT_ICON'])) + link = String(allow_none=True) + oleUpdate = Set(values=(['OLEUPDATE_ALWAYS', 'OLEUPDATE_ONCALL'])) + autoLoad = Bool(allow_none=True) + shapeId = Integer() + + __elements__ = ('objectPr',) + + def __init__(self, + objectPr=None, + progId=None, + dvAspect='DVASPECT_CONTENT', + link=None, + oleUpdate=None, + autoLoad=False, + shapeId=None, + ): + self.objectPr = objectPr + self.progId = progId + self.dvAspect = dvAspect + self.link = link + self.oleUpdate = oleUpdate + self.autoLoad = autoLoad + self.shapeId = shapeId + + +class OleObjects(Serialisable): + + tagname = "oleObjects" + + oleObject = Sequence(expected_type=OleObject) + + __elements__ = ('oleObject',) + + def __init__(self, + oleObject=(), + ): + self.oleObject = oleObject + diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/page.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/page.py new file mode 100644 index 00000000..7d630c2c --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/page.py @@ -0,0 +1,174 @@ +# Copyright (c) 2010-2024 openpyxl + +from openpyxl.descriptors.serialisable import Serialisable +from openpyxl.descriptors import ( + Float, + Bool, + Integer, + NoneSet, + ) +from openpyxl.descriptors.excel import UniversalMeasure, Relation + + +class PrintPageSetup(Serialisable): + """ Worksheet print page setup """ + + tagname = "pageSetup" + + orientation = NoneSet(values=("default", "portrait", "landscape")) + paperSize = Integer(allow_none=True) + scale = Integer(allow_none=True) + fitToHeight = Integer(allow_none=True) + fitToWidth = Integer(allow_none=True) + firstPageNumber = Integer(allow_none=True) + useFirstPageNumber = Bool(allow_none=True) + paperHeight = UniversalMeasure(allow_none=True) + paperWidth = UniversalMeasure(allow_none=True) + pageOrder = NoneSet(values=("downThenOver", "overThenDown")) + usePrinterDefaults = Bool(allow_none=True) + blackAndWhite = Bool(allow_none=True) + draft = Bool(allow_none=True) + cellComments = NoneSet(values=("asDisplayed", "atEnd")) + errors = NoneSet(values=("displayed", "blank", "dash", "NA")) + horizontalDpi = Integer(allow_none=True) + verticalDpi = Integer(allow_none=True) + copies = Integer(allow_none=True) + id = Relation() + + + def __init__(self, + worksheet=None, + orientation=None, + paperSize=None, + scale=None, + fitToHeight=None, + fitToWidth=None, + firstPageNumber=None, + useFirstPageNumber=None, + paperHeight=None, + paperWidth=None, + pageOrder=None, + usePrinterDefaults=None, + blackAndWhite=None, + draft=None, + cellComments=None, + errors=None, + horizontalDpi=None, + verticalDpi=None, + copies=None, + id=None): + self._parent = worksheet + self.orientation = orientation + self.paperSize = paperSize + self.scale = scale + self.fitToHeight = fitToHeight + self.fitToWidth = fitToWidth + self.firstPageNumber = firstPageNumber + self.useFirstPageNumber = useFirstPageNumber + self.paperHeight = paperHeight + self.paperWidth = paperWidth + self.pageOrder = pageOrder + self.usePrinterDefaults = usePrinterDefaults + self.blackAndWhite = blackAndWhite + self.draft = draft + self.cellComments = cellComments + self.errors = errors + self.horizontalDpi = horizontalDpi + self.verticalDpi = verticalDpi + self.copies = copies + self.id = id + + + def __bool__(self): + return bool(dict(self)) + + + + + @property + def sheet_properties(self): + """ + Proxy property + """ + return self._parent.sheet_properties.pageSetUpPr + + + @property + def fitToPage(self): + return self.sheet_properties.fitToPage + + + @fitToPage.setter + def fitToPage(self, value): + self.sheet_properties.fitToPage = value + + + @property + def autoPageBreaks(self): + return self.sheet_properties.autoPageBreaks + + + @autoPageBreaks.setter + def autoPageBreaks(self, value): + self.sheet_properties.autoPageBreaks = value + + + @classmethod + def from_tree(cls, node): + self = super().from_tree(node) + self.id = None # strip link to binary settings + return self + + +class PrintOptions(Serialisable): + """ Worksheet print options """ + + tagname = "printOptions" + horizontalCentered = Bool(allow_none=True) + verticalCentered = Bool(allow_none=True) + headings = Bool(allow_none=True) + gridLines = Bool(allow_none=True) + gridLinesSet = Bool(allow_none=True) + + def __init__(self, horizontalCentered=None, + verticalCentered=None, + headings=None, + gridLines=None, + gridLinesSet=None, + ): + self.horizontalCentered = horizontalCentered + self.verticalCentered = verticalCentered + self.headings = headings + self.gridLines = gridLines + self.gridLinesSet = gridLinesSet + + + def __bool__(self): + return bool(dict(self)) + + +class PageMargins(Serialisable): + """ + Information about page margins for view/print layouts. + Standard values (in inches) + left, right = 0.75 + top, bottom = 1 + header, footer = 0.5 + """ + tagname = "pageMargins" + + left = Float() + right = Float() + top = Float() + bottom = Float() + header = Float() + footer = Float() + + def __init__(self, left=0.75, right=0.75, top=1, bottom=1, header=0.5, + footer=0.5): + self.left = left + self.right = right + self.top = top + self.bottom = bottom + self.header = header + self.footer = footer diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/pagebreak.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/pagebreak.py new file mode 100644 index 00000000..ad50a321 --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/pagebreak.py @@ -0,0 +1,94 @@ +# Copyright (c) 2010-2024 openpyxl + +from openpyxl.descriptors.serialisable import Serialisable +from openpyxl.descriptors import ( + Integer, + Bool, + Sequence, +) + + +class Break(Serialisable): + + tagname = "brk" + + id = Integer(allow_none=True) + min = Integer(allow_none=True) + max = Integer(allow_none=True) + man = Bool(allow_none=True) + pt = Bool(allow_none=True) + + def __init__(self, + id=0, + min=0, + max=16383, + man=True, + pt=None, + ): + self.id = id + self.min = min + self.max = max + self.man = man + self.pt = pt + + +class RowBreak(Serialisable): + + tagname = "rowBreaks" + + count = Integer(allow_none=True) + manualBreakCount = Integer(allow_none=True) + brk = Sequence(expected_type=Break, allow_none=True) + + __elements__ = ('brk',) + __attrs__ = ("count", "manualBreakCount",) + + def __init__(self, + count=None, + manualBreakCount=None, + brk=(), + ): + self.brk = brk + + + def __bool__(self): + return len(self.brk) > 0 + + + def __len__(self): + return len(self.brk) + + + @property + def count(self): + return len(self) + + + @property + def manualBreakCount(self): + return len(self) + + + def append(self, brk=None): + """ + Add a page break + """ + vals = list(self.brk) + if not isinstance(brk, Break): + brk = Break(id=self.count+1) + vals.append(brk) + self.brk = vals + + +PageBreak = RowBreak + + +class ColBreak(RowBreak): + + tagname = "colBreaks" + + count = RowBreak.count + manualBreakCount = RowBreak.manualBreakCount + brk = RowBreak.brk + + __attrs__ = RowBreak.__attrs__ diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/picture.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/picture.py new file mode 100644 index 00000000..8fff338a --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/picture.py @@ -0,0 +1,8 @@ +#Autogenerated schema +from openpyxl.descriptors.serialisable import Serialisable + +# same as related + +class SheetBackgroundPicture(Serialisable): + + tagname = "sheetBackgroundPicture" diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/print_settings.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/print_settings.py new file mode 100644 index 00000000..b4629df1 --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/print_settings.py @@ -0,0 +1,184 @@ +# Copyright (c) 2010-2024 openpyxl + +import re +from openpyxl.descriptors import ( + Strict, + Integer, + String, + Typed, +) +from openpyxl.utils import quote_sheetname, absolute_coordinate +from openpyxl.utils.cell import SHEET_TITLE, SHEETRANGE_RE, RANGE_EXPR + +from .cell_range import MultiCellRange + +COL_RANGE = r"""(?P<cols>[$]?(?P<min_col>[a-zA-Z]{1,3}):[$]?(?P<max_col>[a-zA-Z]{1,3}))""" +COL_RANGE_RE = re.compile(COL_RANGE) +ROW_RANGE = r"""(?P<rows>[$]?(?P<min_row>\d+):[$]?(?P<max_row>\d+))""" +ROW_RANGE_RE = re.compile(ROW_RANGE) +TITLES_REGEX = re.compile("""{0}{1}?,?{2}?,?""".format(SHEET_TITLE, ROW_RANGE, COL_RANGE), + re.VERBOSE) +PRINT_AREA_RE = re.compile(f"({SHEET_TITLE})?(?P<cells>{RANGE_EXPR})", re.VERBOSE) + +class ColRange(Strict): + """ + Represent a range of at least one column + """ + + min_col = String() + max_col = String() + + + def __init__(self, range_string=None, min_col=None, max_col=None): + if range_string is not None: + match = COL_RANGE_RE.match(range_string) + if not match: + raise ValueError(f"{range_string} is not a valid column range") + min_col, max_col = match.groups()[1:] + self.min_col = min_col + self.max_col = max_col + + + def __eq__(self, other): + if isinstance(other, self.__class__): + return (self.min_col == other.min_col + and + self.max_col == other.max_col) + elif isinstance(other, str): + return (str(self) == other + or + f"{self.min_col}:{self.max_col}") + return False + + + def __repr__(self): + return f"Range of columns from '{self.min_col}' to '{self.max_col}'" + + + def __str__(self): + return f"${self.min_col}:${self.max_col}" + + +class RowRange(Strict): + """ + Represent a range of at least one row + """ + + min_row = Integer() + max_row = Integer() + + def __init__(self, range_string=None, min_row=None, max_row=None): + if range_string is not None: + match = ROW_RANGE_RE.match(range_string) + if not match: + raise ValueError(f"{range_string} is not a valid row range") + min_row, max_row = match.groups()[1:] + self.min_row = min_row + self.max_row = max_row + + + def __eq__(self, other): + if isinstance(other, self.__class__): + return (self.min_row == other.min_row + and + self.max_row == other.max_row) + elif isinstance(other, str): + return (str(self) == other + or + f"{self.min_row}:{self.max_row}") + return False + + def __repr__(self): + return f"Range of rows from '{self.min_row}' to '{self.max_row}'" + + + def __str__(self): + return f"${self.min_row}:${self.max_row}" + + +class PrintTitles(Strict): + """ + Contains at least either a range of rows or columns + """ + + cols = Typed(expected_type=ColRange, allow_none=True) + rows = Typed(expected_type=RowRange, allow_none=True) + title = String() + + + def __init__(self, cols=None, rows=None, title=""): + self.cols = cols + self.rows = rows + self.title = title + + + @classmethod + def from_string(cls, value): + kw = dict((k, v) for match in TITLES_REGEX.finditer(value) + for k, v in match.groupdict().items() if v) + + if not kw: + raise ValueError(f"{value} is not a valid print titles definition") + + cols = rows = None + + if "cols" in kw: + cols = ColRange(kw["cols"]) + if "rows" in kw: + rows = RowRange(kw["rows"]) + + title = kw.get("quoted") or kw.get("notquoted") + + return cls(cols=cols, rows=rows, title=title) + + + def __eq__(self, other): + if isinstance(other, self.__class__): + return (self.cols == other.cols + and + self.rows == other.rows + and + self.title == other.title) + elif isinstance(other, str): + return str(self) == other + return False + + def __repr__(self): + return f"Print titles for sheet {self.title} cols {self.rows}, rows {self.cols}" + + + def __str__(self): + title = quote_sheetname(self.title) + titles = ",".join([f"{title}!{value}" for value in (self.rows, self.cols) if value]) + return titles or "" + + +class PrintArea(MultiCellRange): + + + @classmethod + def from_string(cls, value): + new = [] + for m in PRINT_AREA_RE.finditer(value): # can be multiple + coord = m.group("cells") + if coord: + new.append(coord) + return cls(new) + + + def __init__(self, ranges=(), title=""): + self.title = "" + super().__init__(ranges) + + + def __str__(self): + if self.ranges: + return ",".join([f"{quote_sheetname(self.title)}!{absolute_coordinate(str(range))}" + for range in self.sorted()]) + return "" + + + def __eq__(self, other): + super().__eq__(other) + if isinstance(other, str): + return str(self) == other diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/properties.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/properties.py new file mode 100644 index 00000000..e16d15be --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/properties.py @@ -0,0 +1,97 @@ +# Copyright (c) 2010-2024 openpyxl + +"""Worksheet Properties""" + +from openpyxl.descriptors.serialisable import Serialisable +from openpyxl.descriptors import String, Bool, Typed +from openpyxl.styles.colors import ColorDescriptor + + +class Outline(Serialisable): + + tagname = "outlinePr" + + applyStyles = Bool(allow_none=True) + summaryBelow = Bool(allow_none=True) + summaryRight = Bool(allow_none=True) + showOutlineSymbols = Bool(allow_none=True) + + + def __init__(self, + applyStyles=None, + summaryBelow=None, + summaryRight=None, + showOutlineSymbols=None + ): + self.applyStyles = applyStyles + self.summaryBelow = summaryBelow + self.summaryRight = summaryRight + self.showOutlineSymbols = showOutlineSymbols + + +class PageSetupProperties(Serialisable): + + tagname = "pageSetUpPr" + + autoPageBreaks = Bool(allow_none=True) + fitToPage = Bool(allow_none=True) + + def __init__(self, autoPageBreaks=None, fitToPage=None): + self.autoPageBreaks = autoPageBreaks + self.fitToPage = fitToPage + + +class WorksheetProperties(Serialisable): + + tagname = "sheetPr" + + codeName = String(allow_none=True) + enableFormatConditionsCalculation = Bool(allow_none=True) + filterMode = Bool(allow_none=True) + published = Bool(allow_none=True) + syncHorizontal = Bool(allow_none=True) + syncRef = String(allow_none=True) + syncVertical = Bool(allow_none=True) + transitionEvaluation = Bool(allow_none=True) + transitionEntry = Bool(allow_none=True) + tabColor = ColorDescriptor(allow_none=True) + outlinePr = Typed(expected_type=Outline, allow_none=True) + pageSetUpPr = Typed(expected_type=PageSetupProperties, allow_none=True) + + __elements__ = ('tabColor', 'outlinePr', 'pageSetUpPr') + + + def __init__(self, + codeName=None, + enableFormatConditionsCalculation=None, + filterMode=None, + published=None, + syncHorizontal=None, + syncRef=None, + syncVertical=None, + transitionEvaluation=None, + transitionEntry=None, + tabColor=None, + outlinePr=None, + pageSetUpPr=None + ): + """ Attributes """ + self.codeName = codeName + self.enableFormatConditionsCalculation = enableFormatConditionsCalculation + self.filterMode = filterMode + self.published = published + self.syncHorizontal = syncHorizontal + self.syncRef = syncRef + self.syncVertical = syncVertical + self.transitionEvaluation = transitionEvaluation + self.transitionEntry = transitionEntry + """ Elements """ + self.tabColor = tabColor + if outlinePr is None: + self.outlinePr = Outline(summaryBelow=True, summaryRight=True) + else: + self.outlinePr = outlinePr + + if pageSetUpPr is None: + pageSetUpPr = PageSetupProperties() + self.pageSetUpPr = pageSetUpPr diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/protection.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/protection.py new file mode 100644 index 00000000..7f931840 --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/protection.py @@ -0,0 +1,120 @@ +# Copyright (c) 2010-2024 openpyxl + +from openpyxl.descriptors import ( + Bool, + String, + Alias, + Integer, +) +from openpyxl.descriptors.serialisable import Serialisable +from openpyxl.descriptors.excel import ( + Base64Binary, +) +from openpyxl.utils.protection import hash_password + + +class _Protected: + _password = None + + def set_password(self, value='', already_hashed=False): + """Set a password on this sheet.""" + if not already_hashed: + value = hash_password(value) + self._password = value + + @property + def password(self): + """Return the password value, regardless of hash.""" + return self._password + + @password.setter + def password(self, value): + """Set a password directly, forcing a hash step.""" + self.set_password(value) + + +class SheetProtection(Serialisable, _Protected): + """ + Information about protection of various aspects of a sheet. True values + mean that protection for the object or action is active This is the + **default** when protection is active, ie. users cannot do something + """ + + tagname = "sheetProtection" + + sheet = Bool() + enabled = Alias('sheet') + objects = Bool() + scenarios = Bool() + formatCells = Bool() + formatColumns = Bool() + formatRows = Bool() + insertColumns = Bool() + insertRows = Bool() + insertHyperlinks = Bool() + deleteColumns = Bool() + deleteRows = Bool() + selectLockedCells = Bool() + selectUnlockedCells = Bool() + sort = Bool() + autoFilter = Bool() + pivotTables = Bool() + saltValue = Base64Binary(allow_none=True) + spinCount = Integer(allow_none=True) + algorithmName = String(allow_none=True) + hashValue = Base64Binary(allow_none=True) + + + __attrs__ = ('selectLockedCells', 'selectUnlockedCells', 'algorithmName', + 'sheet', 'objects', 'insertRows', 'insertHyperlinks', 'autoFilter', + 'scenarios', 'formatColumns', 'deleteColumns', 'insertColumns', + 'pivotTables', 'deleteRows', 'formatCells', 'saltValue', 'formatRows', + 'sort', 'spinCount', 'password', 'hashValue') + + + def __init__(self, sheet=False, objects=False, scenarios=False, + formatCells=True, formatRows=True, formatColumns=True, + insertColumns=True, insertRows=True, insertHyperlinks=True, + deleteColumns=True, deleteRows=True, selectLockedCells=False, + selectUnlockedCells=False, sort=True, autoFilter=True, pivotTables=True, + password=None, algorithmName=None, saltValue=None, spinCount=None, hashValue=None): + self.sheet = sheet + self.objects = objects + self.scenarios = scenarios + self.formatCells = formatCells + self.formatColumns = formatColumns + self.formatRows = formatRows + self.insertColumns = insertColumns + self.insertRows = insertRows + self.insertHyperlinks = insertHyperlinks + self.deleteColumns = deleteColumns + self.deleteRows = deleteRows + self.selectLockedCells = selectLockedCells + self.selectUnlockedCells = selectUnlockedCells + self.sort = sort + self.autoFilter = autoFilter + self.pivotTables = pivotTables + if password is not None: + self.password = password + self.algorithmName = algorithmName + self.saltValue = saltValue + self.spinCount = spinCount + self.hashValue = hashValue + + + def set_password(self, value='', already_hashed=False): + super().set_password(value, already_hashed) + self.enable() + + + def enable(self): + self.sheet = True + + + def disable(self): + self.sheet = False + + + def __bool__(self): + return self.sheet + diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/related.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/related.py new file mode 100644 index 00000000..2bf05019 --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/related.py @@ -0,0 +1,17 @@ +# Copyright (c) 2010-2024 openpyxl + +from openpyxl.descriptors.serialisable import Serialisable +from openpyxl.descriptors.excel import Relation + + +class Related(Serialisable): + + id = Relation() + + + def __init__(self, id=None): + self.id = id + + + def to_tree(self, tagname, idx=None): + return super().to_tree(tagname) diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/scenario.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/scenario.py new file mode 100644 index 00000000..3c86f607 --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/scenario.py @@ -0,0 +1,105 @@ +# Copyright (c) 2010-2024 openpyxl + +from openpyxl.descriptors.serialisable import Serialisable +from openpyxl.descriptors import ( + String, + Integer, + Bool, + Sequence, + Convertible, +) +from .cell_range import MultiCellRange + + +class InputCells(Serialisable): + + tagname = "inputCells" + + r = String() + deleted = Bool(allow_none=True) + undone = Bool(allow_none=True) + val = String() + numFmtId = Integer(allow_none=True) + + def __init__(self, + r=None, + deleted=False, + undone=False, + val=None, + numFmtId=None, + ): + self.r = r + self.deleted = deleted + self.undone = undone + self.val = val + self.numFmtId = numFmtId + + +class Scenario(Serialisable): + + tagname = "scenario" + + inputCells = Sequence(expected_type=InputCells) + name = String() + locked = Bool(allow_none=True) + hidden = Bool(allow_none=True) + user = String(allow_none=True) + comment = String(allow_none=True) + + __elements__ = ('inputCells',) + __attrs__ = ('name', 'locked', 'hidden', 'user', 'comment', 'count') + + def __init__(self, + inputCells=(), + name=None, + locked=False, + hidden=False, + count=None, + user=None, + comment=None, + ): + self.inputCells = inputCells + self.name = name + self.locked = locked + self.hidden = hidden + self.user = user + self.comment = comment + + + @property + def count(self): + return len(self.inputCells) + + +class ScenarioList(Serialisable): + + tagname = "scenarios" + + scenario = Sequence(expected_type=Scenario) + current = Integer(allow_none=True) + show = Integer(allow_none=True) + sqref = Convertible(expected_type=MultiCellRange, allow_none=True) + + __elements__ = ('scenario',) + + def __init__(self, + scenario=(), + current=None, + show=None, + sqref=None, + ): + self.scenario = scenario + self.current = current + self.show = show + self.sqref = sqref + + + def append(self, scenario): + s = self.scenario + s.append(scenario) + self.scenario = s + + + def __bool__(self): + return bool(self.scenario) + diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/smart_tag.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/smart_tag.py new file mode 100644 index 00000000..29fe1926 --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/smart_tag.py @@ -0,0 +1,78 @@ +#Autogenerated schema +from openpyxl.descriptors.serialisable import Serialisable +from openpyxl.descriptors import ( + Bool, + Integer, + String, + Sequence, +) + + +class CellSmartTagPr(Serialisable): + + tagname = "cellSmartTagPr" + + key = String() + val = String() + + def __init__(self, + key=None, + val=None, + ): + self.key = key + self.val = val + + +class CellSmartTag(Serialisable): + + tagname = "cellSmartTag" + + cellSmartTagPr = Sequence(expected_type=CellSmartTagPr) + type = Integer() + deleted = Bool(allow_none=True) + xmlBased = Bool(allow_none=True) + + __elements__ = ('cellSmartTagPr',) + + def __init__(self, + cellSmartTagPr=(), + type=None, + deleted=False, + xmlBased=False, + ): + self.cellSmartTagPr = cellSmartTagPr + self.type = type + self.deleted = deleted + self.xmlBased = xmlBased + + +class CellSmartTags(Serialisable): + + tagname = "cellSmartTags" + + cellSmartTag = Sequence(expected_type=CellSmartTag) + r = String() + + __elements__ = ('cellSmartTag',) + + def __init__(self, + cellSmartTag=(), + r=None, + ): + self.cellSmartTag = cellSmartTag + self.r = r + + +class SmartTags(Serialisable): + + tagname = "smartTags" + + cellSmartTags = Sequence(expected_type=CellSmartTags) + + __elements__ = ('cellSmartTags',) + + def __init__(self, + cellSmartTags=(), + ): + self.cellSmartTags = cellSmartTags + diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/table.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/table.py new file mode 100644 index 00000000..756345f9 --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/table.py @@ -0,0 +1,385 @@ +# Copyright (c) 2010-2024 openpyxl + +from openpyxl.descriptors.serialisable import Serialisable +from openpyxl.descriptors import ( + Descriptor, + Alias, + Typed, + Bool, + Integer, + NoneSet, + String, + Sequence, +) +from openpyxl.descriptors.excel import ExtensionList, CellRange +from openpyxl.descriptors.sequence import NestedSequence +from openpyxl.xml.constants import SHEET_MAIN_NS, REL_NS +from openpyxl.xml.functions import tostring +from openpyxl.utils import range_boundaries +from openpyxl.utils.escape import escape, unescape + +from .related import Related + +from .filters import ( + AutoFilter, + SortState, +) + +TABLESTYLES = tuple( + ["TableStyleMedium{0}".format(i) for i in range(1, 29)] + + ["TableStyleLight{0}".format(i) for i in range(1, 22)] + + ["TableStyleDark{0}".format(i) for i in range(1, 12)] +) + +PIVOTSTYLES = tuple( + ["PivotStyleMedium{0}".format(i) for i in range(1, 29)] + + ["PivotStyleLight{0}".format(i) for i in range(1, 29)] + + ["PivotStyleDark{0}".format(i) for i in range(1, 29)] +) + + +class TableStyleInfo(Serialisable): + + tagname = "tableStyleInfo" + + name = String(allow_none=True) + showFirstColumn = Bool(allow_none=True) + showLastColumn = Bool(allow_none=True) + showRowStripes = Bool(allow_none=True) + showColumnStripes = Bool(allow_none=True) + + def __init__(self, + name=None, + showFirstColumn=None, + showLastColumn=None, + showRowStripes=None, + showColumnStripes=None, + ): + self.name = name + self.showFirstColumn = showFirstColumn + self.showLastColumn = showLastColumn + self.showRowStripes = showRowStripes + self.showColumnStripes = showColumnStripes + + +class XMLColumnProps(Serialisable): + + tagname = "xmlColumnPr" + + mapId = Integer() + xpath = String() + denormalized = Bool(allow_none=True) + xmlDataType = String() + extLst = Typed(expected_type=ExtensionList, allow_none=True) + + __elements__ = () + + def __init__(self, + mapId=None, + xpath=None, + denormalized=None, + xmlDataType=None, + extLst=None, + ): + self.mapId = mapId + self.xpath = xpath + self.denormalized = denormalized + self.xmlDataType = xmlDataType + + +class TableFormula(Serialisable): + + tagname = "tableFormula" + + ## Note formula is stored as the text value + + array = Bool(allow_none=True) + attr_text = Descriptor() + text = Alias('attr_text') + + + def __init__(self, + array=None, + attr_text=None, + ): + self.array = array + self.attr_text = attr_text + + +class TableColumn(Serialisable): + + tagname = "tableColumn" + + id = Integer() + uniqueName = String(allow_none=True) + name = String() + totalsRowFunction = NoneSet(values=(['sum', 'min', 'max', 'average', + 'count', 'countNums', 'stdDev', 'var', 'custom'])) + totalsRowLabel = String(allow_none=True) + queryTableFieldId = Integer(allow_none=True) + headerRowDxfId = Integer(allow_none=True) + dataDxfId = Integer(allow_none=True) + totalsRowDxfId = Integer(allow_none=True) + headerRowCellStyle = String(allow_none=True) + dataCellStyle = String(allow_none=True) + totalsRowCellStyle = String(allow_none=True) + calculatedColumnFormula = Typed(expected_type=TableFormula, allow_none=True) + totalsRowFormula = Typed(expected_type=TableFormula, allow_none=True) + xmlColumnPr = Typed(expected_type=XMLColumnProps, allow_none=True) + extLst = Typed(expected_type=ExtensionList, allow_none=True) + + __elements__ = ('calculatedColumnFormula', 'totalsRowFormula', + 'xmlColumnPr', 'extLst') + + def __init__(self, + id=None, + uniqueName=None, + name=None, + totalsRowFunction=None, + totalsRowLabel=None, + queryTableFieldId=None, + headerRowDxfId=None, + dataDxfId=None, + totalsRowDxfId=None, + headerRowCellStyle=None, + dataCellStyle=None, + totalsRowCellStyle=None, + calculatedColumnFormula=None, + totalsRowFormula=None, + xmlColumnPr=None, + extLst=None, + ): + self.id = id + self.uniqueName = uniqueName + self.name = name + self.totalsRowFunction = totalsRowFunction + self.totalsRowLabel = totalsRowLabel + self.queryTableFieldId = queryTableFieldId + self.headerRowDxfId = headerRowDxfId + self.dataDxfId = dataDxfId + self.totalsRowDxfId = totalsRowDxfId + self.headerRowCellStyle = headerRowCellStyle + self.dataCellStyle = dataCellStyle + self.totalsRowCellStyle = totalsRowCellStyle + self.calculatedColumnFormula = calculatedColumnFormula + self.totalsRowFormula = totalsRowFormula + self.xmlColumnPr = xmlColumnPr + self.extLst = extLst + + + def __iter__(self): + for k, v in super().__iter__(): + if k == 'name': + v = escape(v) + yield k, v + + + @classmethod + def from_tree(cls, node): + self = super().from_tree(node) + self.name = unescape(self.name) + return self + + +class TableNameDescriptor(String): + + """ + Table names cannot have spaces in them + """ + + def __set__(self, instance, value): + if value is not None and " " in value: + raise ValueError("Table names cannot have spaces") + super().__set__(instance, value) + + +class Table(Serialisable): + + _path = "/tables/table{0}.xml" + mime_type = "application/vnd.openxmlformats-officedocument.spreadsheetml.table+xml" + _rel_type = REL_NS + "/table" + _rel_id = None + + tagname = "table" + + id = Integer() + name = String(allow_none=True) + displayName = TableNameDescriptor() + comment = String(allow_none=True) + ref = CellRange() + tableType = NoneSet(values=(['worksheet', 'xml', 'queryTable'])) + headerRowCount = Integer(allow_none=True) + insertRow = Bool(allow_none=True) + insertRowShift = Bool(allow_none=True) + totalsRowCount = Integer(allow_none=True) + totalsRowShown = Bool(allow_none=True) + published = Bool(allow_none=True) + headerRowDxfId = Integer(allow_none=True) + dataDxfId = Integer(allow_none=True) + totalsRowDxfId = Integer(allow_none=True) + headerRowBorderDxfId = Integer(allow_none=True) + tableBorderDxfId = Integer(allow_none=True) + totalsRowBorderDxfId = Integer(allow_none=True) + headerRowCellStyle = String(allow_none=True) + dataCellStyle = String(allow_none=True) + totalsRowCellStyle = String(allow_none=True) + connectionId = Integer(allow_none=True) + autoFilter = Typed(expected_type=AutoFilter, allow_none=True) + sortState = Typed(expected_type=SortState, allow_none=True) + tableColumns = NestedSequence(expected_type=TableColumn, count=True) + tableStyleInfo = Typed(expected_type=TableStyleInfo, allow_none=True) + extLst = Typed(expected_type=ExtensionList, allow_none=True) + + __elements__ = ('autoFilter', 'sortState', 'tableColumns', + 'tableStyleInfo') + + def __init__(self, + id=1, + displayName=None, + ref=None, + name=None, + comment=None, + tableType=None, + headerRowCount=1, + insertRow=None, + insertRowShift=None, + totalsRowCount=None, + totalsRowShown=None, + published=None, + headerRowDxfId=None, + dataDxfId=None, + totalsRowDxfId=None, + headerRowBorderDxfId=None, + tableBorderDxfId=None, + totalsRowBorderDxfId=None, + headerRowCellStyle=None, + dataCellStyle=None, + totalsRowCellStyle=None, + connectionId=None, + autoFilter=None, + sortState=None, + tableColumns=(), + tableStyleInfo=None, + extLst=None, + ): + self.id = id + self.displayName = displayName + if name is None: + name = displayName + self.name = name + self.comment = comment + self.ref = ref + self.tableType = tableType + self.headerRowCount = headerRowCount + self.insertRow = insertRow + self.insertRowShift = insertRowShift + self.totalsRowCount = totalsRowCount + self.totalsRowShown = totalsRowShown + self.published = published + self.headerRowDxfId = headerRowDxfId + self.dataDxfId = dataDxfId + self.totalsRowDxfId = totalsRowDxfId + self.headerRowBorderDxfId = headerRowBorderDxfId + self.tableBorderDxfId = tableBorderDxfId + self.totalsRowBorderDxfId = totalsRowBorderDxfId + self.headerRowCellStyle = headerRowCellStyle + self.dataCellStyle = dataCellStyle + self.totalsRowCellStyle = totalsRowCellStyle + self.connectionId = connectionId + self.autoFilter = autoFilter + self.sortState = sortState + self.tableColumns = tableColumns + self.tableStyleInfo = tableStyleInfo + + + def to_tree(self): + tree = super().to_tree() + tree.set("xmlns", SHEET_MAIN_NS) + return tree + + + @property + def path(self): + """ + Return path within the archive + """ + return "/xl" + self._path.format(self.id) + + + def _write(self, archive): + """ + Serialise to XML and write to archive + """ + xml = self.to_tree() + archive.writestr(self.path[1:], tostring(xml)) + + + def _initialise_columns(self): + """ + Create a list of table columns from a cell range + Always set a ref if we have headers (the default) + Column headings must be strings and must match cells in the worksheet. + """ + + min_col, min_row, max_col, max_row = range_boundaries(self.ref) + for idx in range(min_col, max_col+1): + col = TableColumn(id=idx, name="Column{0}".format(idx)) + self.tableColumns.append(col) + if self.headerRowCount and not self.autoFilter: + self.autoFilter = AutoFilter(ref=self.ref) + + + @property + def column_names(self): + return [column.name for column in self.tableColumns] + + +class TablePartList(Serialisable): + + tagname = "tableParts" + + count = Integer(allow_none=True) + tablePart = Sequence(expected_type=Related) + + __elements__ = ('tablePart',) + __attrs__ = ('count',) + + def __init__(self, + count=None, + tablePart=(), + ): + self.tablePart = tablePart + + + def append(self, part): + self.tablePart.append(part) + + + @property + def count(self): + return len(self.tablePart) + + + def __bool__(self): + return bool(self.tablePart) + + +class TableList(dict): + + + def add(self, table): + if not isinstance(table, Table): + raise TypeError("You can only add tables") + self[table.name] = table + + + def get(self, name=None, table_range=None): + if name is not None: + return super().get(name) + for table in self.values(): + if table_range == table.ref: + return table + + + def items(self): + return [(name, table.ref) for name, table in super().items()] diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/views.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/views.py new file mode 100644 index 00000000..27046b0d --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/views.py @@ -0,0 +1,155 @@ +# Copyright (c) 2010-2024 openpyxl + +from openpyxl.descriptors import ( + Bool, + Integer, + String, + Set, + Float, + Typed, + NoneSet, + Sequence, +) +from openpyxl.descriptors.excel import ExtensionList +from openpyxl.descriptors.serialisable import Serialisable + + +class Pane(Serialisable): + xSplit = Float(allow_none=True) + ySplit = Float(allow_none=True) + topLeftCell = String(allow_none=True) + activePane = Set(values=("bottomRight", "topRight", "bottomLeft", "topLeft")) + state = Set(values=("split", "frozen", "frozenSplit")) + + def __init__(self, + xSplit=None, + ySplit=None, + topLeftCell=None, + activePane="topLeft", + state="split"): + self.xSplit = xSplit + self.ySplit = ySplit + self.topLeftCell = topLeftCell + self.activePane = activePane + self.state = state + + +class Selection(Serialisable): + pane = NoneSet(values=("bottomRight", "topRight", "bottomLeft", "topLeft")) + activeCell = String(allow_none=True) + activeCellId = Integer(allow_none=True) + sqref = String(allow_none=True) + + def __init__(self, + pane=None, + activeCell="A1", + activeCellId=None, + sqref="A1"): + self.pane = pane + self.activeCell = activeCell + self.activeCellId = activeCellId + self.sqref = sqref + + +class SheetView(Serialisable): + + """Information about the visible portions of this sheet.""" + + tagname = "sheetView" + + windowProtection = Bool(allow_none=True) + showFormulas = Bool(allow_none=True) + showGridLines = Bool(allow_none=True) + showRowColHeaders = Bool(allow_none=True) + showZeros = Bool(allow_none=True) + rightToLeft = Bool(allow_none=True) + tabSelected = Bool(allow_none=True) + showRuler = Bool(allow_none=True) + showOutlineSymbols = Bool(allow_none=True) + defaultGridColor = Bool(allow_none=True) + showWhiteSpace = Bool(allow_none=True) + view = NoneSet(values=("normal", "pageBreakPreview", "pageLayout")) + topLeftCell = String(allow_none=True) + colorId = Integer(allow_none=True) + zoomScale = Integer(allow_none=True) + zoomScaleNormal = Integer(allow_none=True) + zoomScaleSheetLayoutView = Integer(allow_none=True) + zoomScalePageLayoutView = Integer(allow_none=True) + zoomToFit = Bool(allow_none=True) # Chart sheets only + workbookViewId = Integer() + selection = Sequence(expected_type=Selection) + pane = Typed(expected_type=Pane, allow_none=True) + + def __init__(self, + windowProtection=None, + showFormulas=None, + showGridLines=None, + showRowColHeaders=None, + showZeros=None, + rightToLeft=None, + tabSelected=None, + showRuler=None, + showOutlineSymbols=None, + defaultGridColor=None, + showWhiteSpace=None, + view=None, + topLeftCell=None, + colorId=None, + zoomScale=None, + zoomScaleNormal=None, + zoomScaleSheetLayoutView=None, + zoomScalePageLayoutView=None, + zoomToFit=None, + workbookViewId=0, + selection=None, + pane=None,): + self.windowProtection = windowProtection + self.showFormulas = showFormulas + self.showGridLines = showGridLines + self.showRowColHeaders = showRowColHeaders + self.showZeros = showZeros + self.rightToLeft = rightToLeft + self.tabSelected = tabSelected + self.showRuler = showRuler + self.showOutlineSymbols = showOutlineSymbols + self.defaultGridColor = defaultGridColor + self.showWhiteSpace = showWhiteSpace + self.view = view + self.topLeftCell = topLeftCell + self.colorId = colorId + self.zoomScale = zoomScale + self.zoomScaleNormal = zoomScaleNormal + self.zoomScaleSheetLayoutView = zoomScaleSheetLayoutView + self.zoomScalePageLayoutView = zoomScalePageLayoutView + self.zoomToFit = zoomToFit + self.workbookViewId = workbookViewId + self.pane = pane + if selection is None: + selection = (Selection(), ) + self.selection = selection + + +class SheetViewList(Serialisable): + + tagname = "sheetViews" + + sheetView = Sequence(expected_type=SheetView, ) + extLst = Typed(expected_type=ExtensionList, allow_none=True) + + __elements__ = ('sheetView',) + + def __init__(self, + sheetView=None, + extLst=None, + ): + if sheetView is None: + sheetView = [SheetView()] + self.sheetView = sheetView + + + @property + def active(self): + """ + Returns the first sheet view which is assumed to be active + """ + return self.sheetView[0] diff --git a/.venv/lib/python3.12/site-packages/openpyxl/worksheet/worksheet.py b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/worksheet.py new file mode 100644 index 00000000..b7ffbebc --- /dev/null +++ b/.venv/lib/python3.12/site-packages/openpyxl/worksheet/worksheet.py @@ -0,0 +1,907 @@ +# Copyright (c) 2010-2024 openpyxl + +"""Worksheet is the 2nd-level container in Excel.""" + + +# Python stdlib imports +from itertools import chain +from operator import itemgetter +from inspect import isgenerator +from warnings import warn + +# compatibility imports +from openpyxl.compat import ( + deprecated, +) + +# package imports +from openpyxl.utils import ( + column_index_from_string, + get_column_letter, + range_boundaries, + coordinate_to_tuple, +) +from openpyxl.cell import Cell, MergedCell +from openpyxl.formatting.formatting import ConditionalFormattingList +from openpyxl.packaging.relationship import RelationshipList +from openpyxl.workbook.child import _WorkbookChild +from openpyxl.workbook.defined_name import ( + DefinedNameDict, +) + +from openpyxl.formula.translate import Translator + +from .datavalidation import DataValidationList +from .page import ( + PrintPageSetup, + PageMargins, + PrintOptions, +) +from .dimensions import ( + ColumnDimension, + RowDimension, + DimensionHolder, + SheetFormatProperties, +) +from .protection import SheetProtection +from .filters import AutoFilter +from .views import ( + Pane, + Selection, + SheetViewList, +) +from .cell_range import MultiCellRange, CellRange +from .merge import MergedCellRange +from .properties import WorksheetProperties +from .pagebreak import RowBreak, ColBreak +from .scenario import ScenarioList +from .table import TableList +from .formula import ArrayFormula +from .print_settings import ( + PrintTitles, + ColRange, + RowRange, + PrintArea, +) + + +class Worksheet(_WorkbookChild): + """Represents a worksheet. + + Do not create worksheets yourself, + use :func:`openpyxl.workbook.Workbook.create_sheet` instead + + """ + + _rel_type = "worksheet" + _path = "/xl/worksheets/sheet{0}.xml" + mime_type = "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" + + BREAK_NONE = 0 + BREAK_ROW = 1 + BREAK_COLUMN = 2 + + SHEETSTATE_VISIBLE = 'visible' + SHEETSTATE_HIDDEN = 'hidden' + SHEETSTATE_VERYHIDDEN = 'veryHidden' + + # Paper size + PAPERSIZE_LETTER = '1' + PAPERSIZE_LETTER_SMALL = '2' + PAPERSIZE_TABLOID = '3' + PAPERSIZE_LEDGER = '4' + PAPERSIZE_LEGAL = '5' + PAPERSIZE_STATEMENT = '6' + PAPERSIZE_EXECUTIVE = '7' + PAPERSIZE_A3 = '8' + PAPERSIZE_A4 = '9' + PAPERSIZE_A4_SMALL = '10' + PAPERSIZE_A5 = '11' + + # Page orientation + ORIENTATION_PORTRAIT = 'portrait' + ORIENTATION_LANDSCAPE = 'landscape' + + def __init__(self, parent, title=None): + _WorkbookChild.__init__(self, parent, title) + self._setup() + + def _setup(self): + self.row_dimensions = DimensionHolder(worksheet=self, + default_factory=self._add_row) + self.column_dimensions = DimensionHolder(worksheet=self, + default_factory=self._add_column) + self.row_breaks = RowBreak() + self.col_breaks = ColBreak() + self._cells = {} + self._charts = [] + self._images = [] + self._rels = RelationshipList() + self._drawing = None + self._comments = [] + self.merged_cells = MultiCellRange() + self._tables = TableList() + self._pivots = [] + self.data_validations = DataValidationList() + self._hyperlinks = [] + self.sheet_state = 'visible' + self.page_setup = PrintPageSetup(worksheet=self) + self.print_options = PrintOptions() + self._print_rows = None + self._print_cols = None + self._print_area = PrintArea() + self.page_margins = PageMargins() + self.views = SheetViewList() + self.protection = SheetProtection() + self.defined_names = DefinedNameDict() + + self._current_row = 0 + self.auto_filter = AutoFilter() + self.conditional_formatting = ConditionalFormattingList() + self.legacy_drawing = None + self.sheet_properties = WorksheetProperties() + self.sheet_format = SheetFormatProperties() + self.scenarios = ScenarioList() + + + @property + def sheet_view(self): + return self.views.active + + + @property + def selected_cell(self): + return self.sheet_view.selection[0].sqref + + + @property + def active_cell(self): + return self.sheet_view.selection[0].activeCell + + + @property + def array_formulae(self): + """Returns a dictionary of cells with array formulae and the cells in array""" + result = {} + for c in self._cells.values(): + if c.data_type == "f": + if isinstance(c.value, ArrayFormula): + result[c.coordinate] = c.value.ref + return result + + + @property + def show_gridlines(self): + return self.sheet_view.showGridLines + + + @property + def freeze_panes(self): + if self.sheet_view.pane is not None: + return self.sheet_view.pane.topLeftCell + + + @freeze_panes.setter + def freeze_panes(self, topLeftCell=None): + if isinstance(topLeftCell, Cell): + topLeftCell = topLeftCell.coordinate + if topLeftCell == 'A1': + topLeftCell = None + + if not topLeftCell: + self.sheet_view.pane = None + return + + row, column = coordinate_to_tuple(topLeftCell) + + view = self.sheet_view + view.pane = Pane(topLeftCell=topLeftCell, + activePane="topRight", + state="frozen") + view.selection[0].pane = "topRight" + + if column > 1: + view.pane.xSplit = column - 1 + if row > 1: + view.pane.ySplit = row - 1 + view.pane.activePane = 'bottomLeft' + view.selection[0].pane = "bottomLeft" + if column > 1: + view.selection[0].pane = "bottomRight" + view.pane.activePane = 'bottomRight' + + if row > 1 and column > 1: + sel = list(view.selection) + sel.insert(0, Selection(pane="topRight", activeCell=None, sqref=None)) + sel.insert(1, Selection(pane="bottomLeft", activeCell=None, sqref=None)) + view.selection = sel + + + def cell(self, row, column, value=None): + """ + Returns a cell object based on the given coordinates. + + Usage: cell(row=15, column=1, value=5) + + Calling `cell` creates cells in memory when they + are first accessed. + + :param row: row index of the cell (e.g. 4) + :type row: int + + :param column: column index of the cell (e.g. 3) + :type column: int + + :param value: value of the cell (e.g. 5) + :type value: numeric or time or string or bool or none + + :rtype: openpyxl.cell.cell.Cell + """ + + if row < 1 or column < 1: + raise ValueError("Row or column values must be at least 1") + + cell = self._get_cell(row, column) + if value is not None: + cell.value = value + + return cell + + + def _get_cell(self, row, column): + """ + Internal method for getting a cell from a worksheet. + Will create a new cell if one doesn't already exist. + """ + if not 0 < row < 1048577: + raise ValueError(f"Row numbers must be between 1 and 1048576. Row number supplied was {row}") + coordinate = (row, column) + if not coordinate in self._cells: + cell = Cell(self, row=row, column=column) + self._add_cell(cell) + return self._cells[coordinate] + + + def _add_cell(self, cell): + """ + Internal method for adding cell objects. + """ + column = cell.col_idx + row = cell.row + self._current_row = max(row, self._current_row) + self._cells[(row, column)] = cell + + + def __getitem__(self, key): + """Convenience access by Excel style coordinates + + The key can be a single cell coordinate 'A1', a range of cells 'A1:D25', + individual rows or columns 'A', 4 or ranges of rows or columns 'A:D', + 4:10. + + Single cells will always be created if they do not exist. + + Returns either a single cell or a tuple of rows or columns. + """ + if isinstance(key, slice): + if not all([key.start, key.stop]): + raise IndexError("{0} is not a valid coordinate or range".format(key)) + key = "{0}:{1}".format(key.start, key.stop) + + if isinstance(key, int): + key = str(key + ) + min_col, min_row, max_col, max_row = range_boundaries(key) + + if not any([min_col, min_row, max_col, max_row]): + raise IndexError("{0} is not a valid coordinate or range".format(key)) + + if min_row is None: + cols = tuple(self.iter_cols(min_col, max_col)) + if min_col == max_col: + cols = cols[0] + return cols + if min_col is None: + rows = tuple(self.iter_rows(min_col=min_col, min_row=min_row, + max_col=self.max_column, max_row=max_row)) + if min_row == max_row: + rows = rows[0] + return rows + if ":" not in key: + return self._get_cell(min_row, min_col) + return tuple(self.iter_rows(min_row=min_row, min_col=min_col, + max_row=max_row, max_col=max_col)) + + + def __setitem__(self, key, value): + self[key].value = value + + + def __iter__(self): + return self.iter_rows() + + + def __delitem__(self, key): + row, column = coordinate_to_tuple(key) + if (row, column) in self._cells: + del self._cells[(row, column)] + + + @property + def min_row(self): + """The minimum row index containing data (1-based) + + :type: int + """ + min_row = 1 + if self._cells: + min_row = min(self._cells)[0] + return min_row + + + @property + def max_row(self): + """The maximum row index containing data (1-based) + + :type: int + """ + max_row = 1 + if self._cells: + max_row = max(self._cells)[0] + return max_row + + + @property + def min_column(self): + """The minimum column index containing data (1-based) + + :type: int + """ + min_col = 1 + if self._cells: + min_col = min(c[1] for c in self._cells) + return min_col + + + @property + def max_column(self): + """The maximum column index containing data (1-based) + + :type: int + """ + max_col = 1 + if self._cells: + max_col = max(c[1] for c in self._cells) + return max_col + + + def calculate_dimension(self): + """Return the minimum bounding range for all cells containing data (ex. 'A1:M24') + + :rtype: string + """ + if self._cells: + rows = set() + cols = set() + for row, col in self._cells: + rows.add(row) + cols.add(col) + max_row = max(rows) + max_col = max(cols) + min_col = min(cols) + min_row = min(rows) + else: + return "A1:A1" + + return f"{get_column_letter(min_col)}{min_row}:{get_column_letter(max_col)}{max_row}" + + + @property + def dimensions(self): + """Returns the result of :func:`calculate_dimension`""" + return self.calculate_dimension() + + + def iter_rows(self, min_row=None, max_row=None, min_col=None, max_col=None, values_only=False): + """ + Produces cells from the worksheet, by row. Specify the iteration range + using indices of rows and columns. + + If no indices are specified the range starts at A1. + + If no cells are in the worksheet an empty tuple will be returned. + + :param min_col: smallest column index (1-based index) + :type min_col: int + + :param min_row: smallest row index (1-based index) + :type min_row: int + + :param max_col: largest column index (1-based index) + :type max_col: int + + :param max_row: largest row index (1-based index) + :type max_row: int + + :param values_only: whether only cell values should be returned + :type values_only: bool + + :rtype: generator + """ + + if self._current_row == 0 and not any([min_col, min_row, max_col, max_row ]): + return iter(()) + + + min_col = min_col or 1 + min_row = min_row or 1 + max_col = max_col or self.max_column + max_row = max_row or self.max_row + + return self._cells_by_row(min_col, min_row, max_col, max_row, values_only) + + + def _cells_by_row(self, min_col, min_row, max_col, max_row, values_only=False): + for row in range(min_row, max_row + 1): + cells = (self.cell(row=row, column=column) for column in range(min_col, max_col + 1)) + if values_only: + yield tuple(cell.value for cell in cells) + else: + yield tuple(cells) + + + @property + def rows(self): + """Produces all cells in the worksheet, by row (see :func:`iter_rows`) + + :type: generator + """ + return self.iter_rows() + + + @property + def values(self): + """Produces all cell values in the worksheet, by row + + :type: generator + """ + for row in self.iter_rows(values_only=True): + yield row + + + def iter_cols(self, min_col=None, max_col=None, min_row=None, max_row=None, values_only=False): + """ + Produces cells from the worksheet, by column. Specify the iteration range + using indices of rows and columns. + + If no indices are specified the range starts at A1. + + If no cells are in the worksheet an empty tuple will be returned. + + :param min_col: smallest column index (1-based index) + :type min_col: int + + :param min_row: smallest row index (1-based index) + :type min_row: int + + :param max_col: largest column index (1-based index) + :type max_col: int + + :param max_row: largest row index (1-based index) + :type max_row: int + + :param values_only: whether only cell values should be returned + :type values_only: bool + + :rtype: generator + """ + + if self._current_row == 0 and not any([min_col, min_row, max_col, max_row]): + return iter(()) + + min_col = min_col or 1 + min_row = min_row or 1 + max_col = max_col or self.max_column + max_row = max_row or self.max_row + + return self._cells_by_col(min_col, min_row, max_col, max_row, values_only) + + + def _cells_by_col(self, min_col, min_row, max_col, max_row, values_only=False): + """ + Get cells by column + """ + for column in range(min_col, max_col+1): + cells = (self.cell(row=row, column=column) + for row in range(min_row, max_row+1)) + if values_only: + yield tuple(cell.value for cell in cells) + else: + yield tuple(cells) + + + @property + def columns(self): + """Produces all cells in the worksheet, by column (see :func:`iter_cols`)""" + return self.iter_cols() + + + @property + def column_groups(self): + """ + Return a list of column ranges where more than one column + """ + return [cd.range for cd in self.column_dimensions.values() if cd.min and cd.max > cd.min] + + + def set_printer_settings(self, paper_size, orientation): + """Set printer settings """ + + self.page_setup.paperSize = paper_size + self.page_setup.orientation = orientation + + + def add_data_validation(self, data_validation): + """ Add a data-validation object to the sheet. The data-validation + object defines the type of data-validation to be applied and the + cell or range of cells it should apply to. + """ + self.data_validations.append(data_validation) + + + def add_chart(self, chart, anchor=None): + """ + Add a chart to the sheet + Optionally provide a cell for the top-left anchor + """ + if anchor is not None: + chart.anchor = anchor + self._charts.append(chart) + + + def add_image(self, img, anchor=None): + """ + Add an image to the sheet. + Optionally provide a cell for the top-left anchor + """ + if anchor is not None: + img.anchor = anchor + self._images.append(img) + + + def add_table(self, table): + """ + Check for duplicate name in definedNames and other worksheet tables + before adding table. + """ + + if self.parent._duplicate_name(table.name): + raise ValueError("Table with name {0} already exists".format(table.name)) + if not hasattr(self, "_get_cell"): + warn("In write-only mode you must add table columns manually") + self._tables.add(table) + + + @property + def tables(self): + return self._tables + + + def add_pivot(self, pivot): + self._pivots.append(pivot) + + + def merge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None): + """ Set merge on a cell range. Range is a cell range (e.g. A1:E1) """ + if range_string is None: + cr = CellRange(range_string=range_string, min_col=start_column, min_row=start_row, + max_col=end_column, max_row=end_row) + range_string = cr.coord + mcr = MergedCellRange(self, range_string) + self.merged_cells.add(mcr) + self._clean_merge_range(mcr) + + + def _clean_merge_range(self, mcr): + """ + Remove all but the top left-cell from a range of merged cells + and recreate the lost border information. + Borders are then applied + """ + cells = mcr.cells + next(cells) # skip first cell + for row, col in cells: + self._cells[row, col] = MergedCell(self, row, col) + mcr.format() + + + @property + @deprecated("Use ws.merged_cells.ranges") + def merged_cell_ranges(self): + """Return a copy of cell ranges""" + return self.merged_cells.ranges[:] + + + def unmerge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None): + """ Remove merge on a cell range. Range is a cell range (e.g. A1:E1) """ + cr = CellRange(range_string=range_string, min_col=start_column, min_row=start_row, + max_col=end_column, max_row=end_row) + + if cr.coord not in self.merged_cells: + raise ValueError("Cell range {0} is not merged".format(cr.coord)) + + self.merged_cells.remove(cr) + + cells = cr.cells + next(cells) # skip first cell + for row, col in cells: + del self._cells[(row, col)] + + + def append(self, iterable): + """Appends a group of values at the bottom of the current sheet. + + * If it's a list: all values are added in order, starting from the first column + * If it's a dict: values are assigned to the columns indicated by the keys (numbers or letters) + + :param iterable: list, range or generator, or dict containing values to append + :type iterable: list|tuple|range|generator or dict + + Usage: + + * append(['This is A1', 'This is B1', 'This is C1']) + * **or** append({'A' : 'This is A1', 'C' : 'This is C1'}) + * **or** append({1 : 'This is A1', 3 : 'This is C1'}) + + :raise: TypeError when iterable is neither a list/tuple nor a dict + + """ + row_idx = self._current_row + 1 + + if (isinstance(iterable, (list, tuple, range)) + or isgenerator(iterable)): + for col_idx, content in enumerate(iterable, 1): + if isinstance(content, Cell): + # compatible with write-only mode + cell = content + if cell.parent and cell.parent != self: + raise ValueError("Cells cannot be copied from other worksheets") + cell.parent = self + cell.column = col_idx + cell.row = row_idx + else: + cell = Cell(self, row=row_idx, column=col_idx, value=content) + self._cells[(row_idx, col_idx)] = cell + + elif isinstance(iterable, dict): + for col_idx, content in iterable.items(): + if isinstance(col_idx, str): + col_idx = column_index_from_string(col_idx) + cell = Cell(self, row=row_idx, column=col_idx, value=content) + self._cells[(row_idx, col_idx)] = cell + + else: + self._invalid_row(iterable) + + self._current_row = row_idx + + + def _move_cells(self, min_row=None, min_col=None, offset=0, row_or_col="row"): + """ + Move either rows or columns around by the offset + """ + reverse = offset > 0 # start at the end if inserting + row_offset = 0 + col_offset = 0 + + # need to make affected ranges contiguous + if row_or_col == 'row': + cells = self.iter_rows(min_row=min_row) + row_offset = offset + key = 0 + else: + cells = self.iter_cols(min_col=min_col) + col_offset = offset + key = 1 + cells = list(cells) + + for row, column in sorted(self._cells, key=itemgetter(key), reverse=reverse): + if min_row and row < min_row: + continue + elif min_col and column < min_col: + continue + + self._move_cell(row, column, row_offset, col_offset) + + + def insert_rows(self, idx, amount=1): + """ + Insert row or rows before row==idx + """ + self._move_cells(min_row=idx, offset=amount, row_or_col="row") + self._current_row = self.max_row + + + def insert_cols(self, idx, amount=1): + """ + Insert column or columns before col==idx + """ + self._move_cells(min_col=idx, offset=amount, row_or_col="column") + + + def delete_rows(self, idx, amount=1): + """ + Delete row or rows from row==idx + """ + + remainder = _gutter(idx, amount, self.max_row) + + self._move_cells(min_row=idx+amount, offset=-amount, row_or_col="row") + + # calculating min and max col is an expensive operation, do it only once + min_col = self.min_column + max_col = self.max_column + 1 + for row in remainder: + for col in range(min_col, max_col): + if (row, col) in self._cells: + del self._cells[row, col] + self._current_row = self.max_row + if not self._cells: + self._current_row = 0 + + + def delete_cols(self, idx, amount=1): + """ + Delete column or columns from col==idx + """ + + remainder = _gutter(idx, amount, self.max_column) + + self._move_cells(min_col=idx+amount, offset=-amount, row_or_col="column") + + # calculating min and max row is an expensive operation, do it only once + min_row = self.min_row + max_row = self.max_row + 1 + for col in remainder: + for row in range(min_row, max_row): + if (row, col) in self._cells: + del self._cells[row, col] + + + def move_range(self, cell_range, rows=0, cols=0, translate=False): + """ + Move a cell range by the number of rows and/or columns: + down if rows > 0 and up if rows < 0 + right if cols > 0 and left if cols < 0 + Existing cells will be overwritten. + Formulae and references will not be updated. + """ + if isinstance(cell_range, str): + cell_range = CellRange(cell_range) + if not isinstance(cell_range, CellRange): + raise ValueError("Only CellRange objects can be moved") + if not rows and not cols: + return + + down = rows > 0 + right = cols > 0 + + if rows: + cells = sorted(cell_range.rows, reverse=down) + else: + cells = sorted(cell_range.cols, reverse=right) + + for row, col in chain.from_iterable(cells): + self._move_cell(row, col, rows, cols, translate) + + # rebase moved range + cell_range.shift(row_shift=rows, col_shift=cols) + + + def _move_cell(self, row, column, row_offset, col_offset, translate=False): + """ + Move a cell from one place to another. + Delete at old index + Rebase coordinate + """ + cell = self._get_cell(row, column) + new_row = cell.row + row_offset + new_col = cell.column + col_offset + self._cells[new_row, new_col] = cell + del self._cells[(cell.row, cell.column)] + cell.row = new_row + cell.column = new_col + if translate and cell.data_type == "f": + t = Translator(cell.value, cell.coordinate) + cell.value = t.translate_formula(row_delta=row_offset, col_delta=col_offset) + + + def _invalid_row(self, iterable): + raise TypeError('Value must be a list, tuple, range or generator, or a dict. Supplied value is {0}'.format( + type(iterable)) + ) + + + def _add_column(self): + """Dimension factory for column information""" + + return ColumnDimension(self) + + def _add_row(self): + """Dimension factory for row information""" + + return RowDimension(self) + + + @property + def print_title_rows(self): + """Rows to be printed at the top of every page (ex: '1:3')""" + if self._print_rows: + return str(self._print_rows) + + + @print_title_rows.setter + def print_title_rows(self, rows): + """ + Set rows to be printed on the top of every page + format `1:3` + """ + if rows is not None: + self._print_rows = RowRange(rows) + + + @property + def print_title_cols(self): + """Columns to be printed at the left side of every page (ex: 'A:C')""" + if self._print_cols: + return str(self._print_cols) + + + @print_title_cols.setter + def print_title_cols(self, cols): + """ + Set cols to be printed on the left of every page + format ``A:C` + """ + if cols is not None: + self._print_cols = ColRange(cols) + + + @property + def print_titles(self): + titles = PrintTitles(cols=self._print_cols, rows=self._print_rows, title=self.title) + return str(titles) + + + @property + def print_area(self): + """ + The print area for the worksheet, or None if not set. To set, supply a range + like 'A1:D4' or a list of ranges. + """ + self._print_area.title = self.title + return str(self._print_area) + + + @print_area.setter + def print_area(self, value): + """ + Range of cells in the form A1:D4 or list of ranges. Print area can be cleared + by passing `None` or an empty list + """ + if not value: + self._print_area = PrintArea() + elif isinstance(value, str): + self._print_area = PrintArea.from_string(value) + elif hasattr(value, "__iter__"): + self._print_area = PrintArea.from_string(",".join(value)) + + +def _gutter(idx, offset, max_val): + """ + When deleting rows and columns are deleted we rely on overwriting. + This may not be the case for a large offset on small set of cells: + range(cells_to_delete) > range(cell_to_be_moved) + """ + gutter = range(max(max_val+1-offset, idx), min(idx+offset, max_val)+1) + return gutter |