############################################################################### # # Worksheet - A class for writing the Excel XLSX Worksheet file. # # SPDX-License-Identifier: BSD-2-Clause # # Copyright (c) 2013-2025, John McNamara, jmcnamara@cpan.org # # pylint: disable=too-many-return-statements # Standard packages. import datetime import math import os import re import tempfile from collections import defaultdict, namedtuple from decimal import Decimal from fractions import Fraction from functools import wraps from io import StringIO from math import isinf, isnan from warnings import warn from xlsxwriter.comments import CommentType from xlsxwriter.image import Image from xlsxwriter.url import Url, UrlTypes from xlsxwriter.vml import ButtonType # Package imports. from . import xmlwriter from .drawing import Drawing, DrawingInfo, DrawingTypes from .exceptions import DuplicateTableName, OverlappingRange from .format import Format from .shape import Shape from .utility import ( _datetime_to_excel_datetime, _get_sparkline_style, _preserve_whitespace, _supported_datetime, _xl_color, quote_sheetname, xl_cell_to_rowcol, xl_col_to_name, xl_pixel_width, xl_range, xl_rowcol_to_cell, xl_rowcol_to_cell_fast, ) from .xmlwriter import XMLwriter re_dynamic_function = re.compile( r""" \bANCHORARRAY\( | \bBYCOL\( | \bBYROW\( | \bCHOOSECOLS\( | \bCHOOSEROWS\( | \bDROP\( | \bEXPAND\( | \bFILTER\( | \bHSTACK\( | \bLAMBDA\( | \bMAKEARRAY\( | \bMAP\( | \bRANDARRAY\( | \bREDUCE\( | \bSCAN\( | \bSEQUENCE\( | \bSINGLE\( | \bSORT\( | \bSORTBY\( | \bSWITCH\( | \bTAKE\( | \bTEXTSPLIT\( | \bTOCOL\( | \bTOROW\( | \bUNIQUE\( | \bVSTACK\( | \bWRAPCOLS\( | \bWRAPROWS\( | \bXLOOKUP\(""", re.VERBOSE, ) ############################################################################### # # Decorator functions. # ############################################################################### def convert_cell_args(method): """ Decorator function to convert A1 notation in cell method calls to the default row/col notation. """ @wraps(method) def cell_wrapper(self, *args, **kwargs): try: # First arg is an int, default to row/col notation. if args: first_arg = args[0] int(first_arg) except ValueError: # First arg isn't an int, convert to A1 notation. new_args = xl_cell_to_rowcol(first_arg) args = new_args + args[1:] return method(self, *args, **kwargs) return cell_wrapper def convert_range_args(method): """ Decorator function to convert A1 notation in range method calls to the default row/col notation. """ @wraps(method) def cell_wrapper(self, *args, **kwargs): try: # First arg is an int, default to row/col notation. if args: int(args[0]) except ValueError: # First arg isn't an int, convert to A1 notation. if ":" in args[0]: cell_1, cell_2 = args[0].split(":") row_1, col_1 = xl_cell_to_rowcol(cell_1) row_2, col_2 = xl_cell_to_rowcol(cell_2) else: row_1, col_1 = xl_cell_to_rowcol(args[0]) row_2, col_2 = row_1, col_1 new_args = [row_1, col_1, row_2, col_2] new_args.extend(args[1:]) args = new_args return method(self, *args, **kwargs) return cell_wrapper def convert_column_args(method): """ Decorator function to convert A1 notation in columns method calls to the default row/col notation. """ @wraps(method) def column_wrapper(self, *args, **kwargs): try: # First arg is an int, default to row/col notation. if args: int(args[0]) except ValueError: # First arg isn't an int, convert to A1 notation. cell_1, cell_2 = [col + "1" for col in args[0].split(":")] _, col_1 = xl_cell_to_rowcol(cell_1) _, col_2 = xl_cell_to_rowcol(cell_2) new_args = [col_1, col_2] new_args.extend(args[1:]) args = new_args return method(self, *args, **kwargs) return column_wrapper ############################################################################### # # Named tuples used for cell types. # ############################################################################### CellBlankTuple = namedtuple("Blank", "format") CellErrorTuple = namedtuple("Error", "error, format, value") CellNumberTuple = namedtuple("Number", "number, format") CellStringTuple = namedtuple("String", "string, format") CellBooleanTuple = namedtuple("Boolean", "boolean, format") CellFormulaTuple = namedtuple("Formula", "formula, format, value") CellDatetimeTuple = namedtuple("Datetime", "number, format") CellRichStringTuple = namedtuple("RichString", "string, format, raw_string") CellArrayFormulaTuple = namedtuple( "ArrayFormula", "formula, format, value, range, atype" ) ############################################################################### # # Worksheet Class definition. # ############################################################################### class Worksheet(xmlwriter.XMLwriter): """ A class for writing the Excel XLSX Worksheet file. """ ########################################################################### # # Public API. # ########################################################################### def __init__(self): """ Constructor. """ super().__init__() self.name = None self.index = None self.str_table = None self.palette = None self.constant_memory = 0 self.tmpdir = None self.is_chartsheet = False self.ext_sheets = [] self.fileclosed = 0 self.excel_version = 2007 self.excel2003_style = False self.xls_rowmax = 1048576 self.xls_colmax = 16384 self.xls_strmax = 32767 self.dim_rowmin = None self.dim_rowmax = None self.dim_colmin = None self.dim_colmax = None self.col_info = {} self.selections = [] self.hidden = 0 self.active = 0 self.tab_color = 0 self.top_left_cell = "" self.panes = [] self.active_pane = 3 self.selected = 0 self.page_setup_changed = False self.paper_size = 0 self.orientation = 1 self.print_options_changed = False self.hcenter = False self.vcenter = False self.print_gridlines = False self.screen_gridlines = True self.print_headers = False self.row_col_headers = False self.header_footer_changed = False self.header = "" self.footer = "" self.header_footer_aligns = True self.header_footer_scales = True self.header_images = [] self.footer_images = [] self.header_images_list = [] self.margin_left = 0.7 self.margin_right = 0.7 self.margin_top = 0.75 self.margin_bottom = 0.75 self.margin_header = 0.3 self.margin_footer = 0.3 self.repeat_row_range = "" self.repeat_col_range = "" self.print_area_range = "" self.page_order = 0 self.black_white = 0 self.draft_quality = 0 self.print_comments = 0 self.page_start = 0 self.fit_page = 0 self.fit_width = 0 self.fit_height = 0 self.hbreaks = [] self.vbreaks = [] self.protect_options = {} self.protected_ranges = [] self.num_protected_ranges = 0 self.set_cols = {} self.set_rows = defaultdict(dict) self.zoom = 100 self.zoom_scale_normal = 1 self.print_scale = 100 self.is_right_to_left = False self.show_zeros = 1 self.leading_zeros = 0 self.outline_row_level = 0 self.outline_col_level = 0 self.outline_style = 0 self.outline_below = 1 self.outline_right = 1 self.outline_on = 1 self.outline_changed = False self.original_row_height = 15 self.default_row_height = 15 self.default_row_pixels = 20 self.default_col_width = 8.43 self.default_col_pixels = 64 self.default_date_pixels = 68 self.default_row_zeroed = 0 self.names = {} self.write_match = [] self.table = defaultdict(dict) self.merge = [] self.merged_cells = {} self.table_cells = {} self.row_spans = {} self.has_vml = False self.has_header_vml = False self.has_comments = False self.comments = defaultdict(dict) self.comments_list = [] self.comments_author = "" self.comments_visible = False self.vml_shape_id = 1024 self.buttons_list = [] self.vml_header_id = 0 self.autofilter_area = "" self.autofilter_ref = None self.filter_range = [0, 9] self.filter_on = 0 self.filter_cols = {} self.filter_type = {} self.filter_cells = {} self.row_sizes = {} self.col_size_changed = False self.row_size_changed = False self.last_shape_id = 1 self.rel_count = 0 self.hlink_count = 0 self.hlink_refs = [] self.external_hyper_links = [] self.external_drawing_links = [] self.external_comment_links = [] self.external_vml_links = [] self.external_table_links = [] self.external_background_links = [] self.drawing_links = [] self.vml_drawing_links = [] self.charts = [] self.images = [] self.tables = [] self.sparklines = [] self.shapes = [] self.shape_hash = {} self.drawing = 0 self.drawing_rels = {} self.drawing_rels_id = 0 self.vml_drawing_rels = {} self.vml_drawing_rels_id = 0 self.background_image = None self.rstring = "" self.previous_row = 0 self.validations = [] self.cond_formats = {} self.data_bars_2010 = [] self.use_data_bars_2010 = False self.dxf_priority = 1 self.page_view = 0 self.vba_codename = None self.date_1904 = False self.hyperlinks = defaultdict(dict) self.strings_to_numbers = False self.strings_to_urls = True self.nan_inf_to_errors = False self.strings_to_formulas = True self.default_date_format = None self.default_url_format = None self.default_checkbox_format = None self.workbook_add_format = None self.remove_timezone = False self.max_url_length = 2079 self.row_data_filename = None self.row_data_fh = None self.worksheet_meta = None self.vml_data_id = None self.vml_shape_id = None self.row_data_filename = None self.row_data_fh = None self.row_data_fh_closed = False self.vertical_dpi = 0 self.horizontal_dpi = 0 self.write_handlers = {} self.ignored_errors = None self.has_dynamic_arrays = False self.use_future_functions = False self.ignore_write_string = False self.embedded_images = None # Utility function for writing different types of strings. def _write_token_as_string(self, token, row, col, *args): # Map the data to the appropriate write_*() method. if token == "": return self._write_blank(row, col, *args) if self.strings_to_formulas and token.startswith("="): return self._write_formula(row, col, *args) if token.startswith("{=") and token.endswith("}"): return self._write_formula(row, col, *args) # pylint: disable=too-many-boolean-expressions if ( ":" in token and self.strings_to_urls and ( re.match("(ftp|http)s?://", token) or re.match("mailto:", token) or re.match("(in|ex)ternal:", token) or re.match("file://", token) ) ): return self._write_url(row, col, *args) if self.strings_to_numbers: try: f = float(token) if self.nan_inf_to_errors or (not isnan(f) and not isinf(f)): return self._write_number(row, col, f, *args[1:]) except ValueError: # Not a number, write as a string. pass return self._write_string(row, col, *args) # We have a plain string. return self._write_string(row, col, *args) @convert_cell_args def write(self, row, col, *args): """ Write data to a worksheet cell by calling the appropriate write_*() method based on the type of data being passed. Args: row: The cell row (zero indexed). col: The cell column (zero indexed). *args: Args to pass to sub functions. Returns: 0: Success. -1: Row or column is out of worksheet bounds. other: Return value of called method. """ return self._write(row, col, *args) # Undecorated version of write(). def _write(self, row, col, *args): # pylint: disable=raise-missing-from # Check the number of args passed. if not args: raise TypeError("write() takes at least 4 arguments (3 given)") # The first arg should be the token for all write calls. token = args[0] # Avoid isinstance() for better performance. token_type = token.__class__ # Check for any user defined type handlers with callback functions. if token_type in self.write_handlers: write_handler = self.write_handlers[token_type] function_return = write_handler(self, row, col, *args) # If the return value is None then the callback has returned # control to this function and we should continue as # normal. Otherwise we return the value to the caller and exit. if function_return is None: pass else: return function_return # Write None as a blank cell. if token is None: return self._write_blank(row, col, *args) # Check for standard Python types. if token_type is bool: return self._write_boolean(row, col, *args) if token_type in (float, int, Decimal, Fraction): return self._write_number(row, col, *args) if token_type is str: return self._write_token_as_string(token, row, col, *args) if token_type in ( datetime.datetime, datetime.date, datetime.time, datetime.timedelta, ): return self._write_datetime(row, col, *args) # Resort to isinstance() for subclassed primitives. # Write number types. if isinstance(token, (float, int, Decimal, Fraction)): return self._write_number(row, col, *args) # Write string types. if isinstance(token, str): return self._write_token_as_string(token, row, col, *args) # Write boolean types. if isinstance(token, bool): return self._write_boolean(row, col, *args) # Write datetime objects. if _supported_datetime(token): return self._write_datetime(row, col, *args) # Write Url type. if isinstance(token, Url): return self._write_url(row, col, *args) # We haven't matched a supported type. Try float. try: f = float(token) return self._write_number(row, col, f, *args[1:]) except ValueError: pass except TypeError: raise TypeError(f"Unsupported type {type(token)} in write()") # Finally try string. try: str(token) return self._write_string(row, col, *args) except ValueError: raise TypeError(f"Unsupported type {type(token)} in write()") @convert_cell_args def write_string(self, row, col, string, cell_format=None): """ Write a string to a worksheet cell. Args: row: The cell row (zero indexed). col: The cell column (zero indexed). string: Cell data. Str. format: An optional cell Format object. Returns: 0: Success. -1: Row or column is out of worksheet bounds. -2: String truncated to 32k characters. """ return self._write_string(row, col, string, cell_format) # Undecorated version of write_string(). def _write_string(self, row, col, string, cell_format=None): str_error = 0 # Check that row and col are valid and store max and min values. if self._check_dimensions(row, col): return -1 # Check that the string is < 32767 chars. if len(string) > self.xls_strmax: string = string[: self.xls_strmax] str_error = -2 # Write a shared string or an in-line string in constant_memory mode. if not self.constant_memory: string_index = self.str_table._get_shared_string_index(string) else: string_index = string # Write previous row if in in-line string constant_memory mode. if self.constant_memory and row > self.previous_row: self._write_single_row(row) # Store the cell data in the worksheet data table. self.table[row][col] = CellStringTuple(string_index, cell_format) return str_error @convert_cell_args def write_number(self, row, col, number, cell_format=None): """ Write a number to a worksheet cell. Args: row: The cell row (zero indexed). col: The cell column (zero indexed). number: Cell data. Int or float. cell_format: An optional cell Format object. Returns: 0: Success. -1: Row or column is out of worksheet bounds. """ return self._write_number(row, col, number, cell_format) # Undecorated version of write_number(). def _write_number(self, row, col, number, cell_format=None): if isnan(number) or isinf(number): if self.nan_inf_to_errors: if isnan(number): return self._write_formula(row, col, "#NUM!", cell_format, "#NUM!") if number == math.inf: return self._write_formula(row, col, "1/0", cell_format, "#DIV/0!") if number == -math.inf: return self._write_formula(row, col, "-1/0", cell_format, "#DIV/0!") else: raise TypeError( "NAN/INF not supported in write_number() " "without 'nan_inf_to_errors' Workbook() option" ) if number.__class__ is Fraction: number = float(number) # Check that row and col are valid and store max and min values. if self._check_dimensions(row, col): return -1 # Write previous row if in in-line string constant_memory mode. if self.constant_memory and row > self.previous_row: self._write_single_row(row) # Store the cell data in the worksheet data table. self.table[row][col] = CellNumberTuple(number, cell_format) return 0 @convert_cell_args def write_blank(self, row, col, blank, cell_format=None): """ Write a blank cell with formatting to a worksheet cell. The blank token is ignored and the format only is written to the cell. Args: row: The cell row (zero indexed). col: The cell column (zero indexed). blank: Any value. It is ignored. cell_format: An optional cell Format object. Returns: 0: Success. -1: Row or column is out of worksheet bounds. """ return self._write_blank(row, col, blank, cell_format) # Undecorated version of write_blank(). def _write_blank(self, row, col, _, cell_format=None): # Don't write a blank cell unless it has a format. if cell_format is None: return 0 # Check that row and col are valid and store max and min values. if self._check_dimensions(row, col): return -1 # Write previous row if in in-line string constant_memory mode. if self.constant_memory and row > self.previous_row: self._write_single_row(row) # Store the cell data in the worksheet data table. self.table[row][col] = CellBlankTuple(cell_format) return 0 @convert_cell_args def write_formula(self, row, col, formula, cell_format=None, value=0): """ Write a formula to a worksheet cell. Args: row: The cell row (zero indexed). col: The cell column (zero indexed). formula: Cell formula. cell_format: An optional cell Format object. value: An optional value for the formula. Default is 0. Returns: 0: Success. -1: Row or column is out of worksheet bounds. -2: Formula can't be None or empty. """ # Check that row and col are valid and store max and min values. return self._write_formula(row, col, formula, cell_format, value) # Undecorated version of write_formula(). def _write_formula(self, row, col, formula, cell_format=None, value=0): if self._check_dimensions(row, col): return -1 if formula is None or formula == "": warn("Formula can't be None or empty") return -1 # Check for dynamic array functions. if re_dynamic_function.search(formula): return self.write_dynamic_array_formula( row, col, row, col, formula, cell_format, value ) # Hand off array formulas. if formula.startswith("{") and formula.endswith("}"): return self._write_array_formula( row, col, row, col, formula, cell_format, value ) # Modify the formula string, as needed. formula = self._prepare_formula(formula) # Write previous row if in in-line string constant_memory mode. if self.constant_memory and row > self.previous_row: self._write_single_row(row) # Store the cell data in the worksheet data table. self.table[row][col] = CellFormulaTuple(formula, cell_format, value) return 0 @convert_range_args def write_array_formula( self, first_row, first_col, last_row, last_col, formula, cell_format=None, value=0, ): """ Write a formula to a worksheet cell/range. Args: first_row: The first row of the cell range. (zero indexed). first_col: The first column of the cell range. last_row: The last row of the cell range. (zero indexed). last_col: The last column of the cell range. formula: Cell formula. cell_format: An optional cell Format object. value: An optional value for the formula. Default is 0. Returns: 0: Success. -1: Row or column is out of worksheet bounds. """ # Check for dynamic array functions. if re_dynamic_function.search(formula): return self.write_dynamic_array_formula( first_row, first_col, last_row, last_col, formula, cell_format, value ) return self._write_array_formula( first_row, first_col, last_row, last_col, formula, cell_format, value, "static", ) @convert_range_args def write_dynamic_array_formula( self, first_row, first_col, last_row, last_col, formula, cell_format=None, value=0, ): """ Write a dynamic array formula to a worksheet cell/range. Args: first_row: The first row of the cell range. (zero indexed). first_col: The first column of the cell range. last_row: The last row of the cell range. (zero indexed). last_col: The last column of the cell range. formula: Cell formula. cell_format: An optional cell Format object. value: An optional value for the formula. Default is 0. Returns: 0: Success. -1: Row or column is out of worksheet bounds. """ error = self._write_array_formula( first_row, first_col, last_row, last_col, formula, cell_format, value, "dynamic", ) if error == 0: self.has_dynamic_arrays = True return error # Utility method to strip equal sign and array braces from a formula and # also expand out future and dynamic array formulas. def _prepare_formula(self, formula, expand_future_functions=False): # Remove array formula braces and the leading =. if formula.startswith("{"): formula = formula[1:] if formula.startswith("="): formula = formula[1:] if formula.endswith("}"): formula = formula[:-1] # Check if formula is already expanded by the user. if "_xlfn." in formula: return formula # Expand dynamic formulas. formula = re.sub(r"\bANCHORARRAY\(", "_xlfn.ANCHORARRAY(", formula) formula = re.sub(r"\bBYCOL\(", "_xlfn.BYCOL(", formula) formula = re.sub(r"\bBYROW\(", "_xlfn.BYROW(", formula) formula = re.sub(r"\bCHOOSECOLS\(", "_xlfn.CHOOSECOLS(", formula) formula = re.sub(r"\bCHOOSEROWS\(", "_xlfn.CHOOSEROWS(", formula) formula = re.sub(r"\bDROP\(", "_xlfn.DROP(", formula) formula = re.sub(r"\bEXPAND\(", "_xlfn.EXPAND(", formula) formula = re.sub(r"\bFILTER\(", "_xlfn._xlws.FILTER(", formula) formula = re.sub(r"\bHSTACK\(", "_xlfn.HSTACK(", formula) formula = re.sub(r"\bLAMBDA\(", "_xlfn.LAMBDA(", formula) formula = re.sub(r"\bMAKEARRAY\(", "_xlfn.MAKEARRAY(", formula) formula = re.sub(r"\bMAP\(", "_xlfn.MAP(", formula) formula = re.sub(r"\bRANDARRAY\(", "_xlfn.RANDARRAY(", formula) formula = re.sub(r"\bREDUCE\(", "_xlfn.REDUCE(", formula) formula = re.sub(r"\bSCAN\(", "_xlfn.SCAN(", formula) formula = re.sub(r"\SINGLE\(", "_xlfn.SINGLE(", formula) formula = re.sub(r"\bSEQUENCE\(", "_xlfn.SEQUENCE(", formula) formula = re.sub(r"\bSORT\(", "_xlfn._xlws.SORT(", formula) formula = re.sub(r"\bSORTBY\(", "_xlfn.SORTBY(", formula) formula = re.sub(r"\bSWITCH\(", "_xlfn.SWITCH(", formula) formula = re.sub(r"\bTAKE\(", "_xlfn.TAKE(", formula) formula = re.sub(r"\bTEXTSPLIT\(", "_xlfn.TEXTSPLIT(", formula) formula = re.sub(r"\bTOCOL\(", "_xlfn.TOCOL(", formula) formula = re.sub(r"\bTOROW\(", "_xlfn.TOROW(", formula) formula = re.sub(r"\bUNIQUE\(", "_xlfn.UNIQUE(", formula) formula = re.sub(r"\bVSTACK\(", "_xlfn.VSTACK(", formula) formula = re.sub(r"\bWRAPCOLS\(", "_xlfn.WRAPCOLS(", formula) formula = re.sub(r"\bWRAPROWS\(", "_xlfn.WRAPROWS(", formula) formula = re.sub(r"\bXLOOKUP\(", "_xlfn.XLOOKUP(", formula) if not self.use_future_functions and not expand_future_functions: return formula formula = re.sub(r"\bACOTH\(", "_xlfn.ACOTH(", formula) formula = re.sub(r"\bACOT\(", "_xlfn.ACOT(", formula) formula = re.sub(r"\bAGGREGATE\(", "_xlfn.AGGREGATE(", formula) formula = re.sub(r"\bARABIC\(", "_xlfn.ARABIC(", formula) formula = re.sub(r"\bARRAYTOTEXT\(", "_xlfn.ARRAYTOTEXT(", formula) formula = re.sub(r"\bBASE\(", "_xlfn.BASE(", formula) formula = re.sub(r"\bBETA.DIST\(", "_xlfn.BETA.DIST(", formula) formula = re.sub(r"\bBETA.INV\(", "_xlfn.BETA.INV(", formula) formula = re.sub(r"\bBINOM.DIST.RANGE\(", "_xlfn.BINOM.DIST.RANGE(", formula) formula = re.sub(r"\bBINOM.DIST\(", "_xlfn.BINOM.DIST(", formula) formula = re.sub(r"\bBINOM.INV\(", "_xlfn.BINOM.INV(", formula) formula = re.sub(r"\bBITAND\(", "_xlfn.BITAND(", formula) formula = re.sub(r"\bBITLSHIFT\(", "_xlfn.BITLSHIFT(", formula) formula = re.sub(r"\bBITOR\(", "_xlfn.BITOR(", formula) formula = re.sub(r"\bBITRSHIFT\(", "_xlfn.BITRSHIFT(", formula) formula = re.sub(r"\bBITXOR\(", "_xlfn.BITXOR(", formula) formula = re.sub(r"\bCEILING.MATH\(", "_xlfn.CEILING.MATH(", formula) formula = re.sub(r"\bCEILING.PRECISE\(", "_xlfn.CEILING.PRECISE(", formula) formula = re.sub(r"\bCHISQ.DIST.RT\(", "_xlfn.CHISQ.DIST.RT(", formula) formula = re.sub(r"\bCHISQ.DIST\(", "_xlfn.CHISQ.DIST(", formula) formula = re.sub(r"\bCHISQ.INV.RT\(", "_xlfn.CHISQ.INV.RT(", formula) formula = re.sub(r"\bCHISQ.INV\(", "_xlfn.CHISQ.INV(", formula) formula = re.sub(r"\bCHISQ.TEST\(", "_xlfn.CHISQ.TEST(", formula) formula = re.sub(r"\bCOMBINA\(", "_xlfn.COMBINA(", formula) formula = re.sub(r"\bCONCAT\(", "_xlfn.CONCAT(", formula) formula = re.sub(r"\bCONFIDENCE.NORM\(", "_xlfn.CONFIDENCE.NORM(", formula) formula = re.sub(r"\bCONFIDENCE.T\(", "_xlfn.CONFIDENCE.T(", formula) formula = re.sub(r"\bCOTH\(", "_xlfn.COTH(", formula) formula = re.sub(r"\bCOT\(", "_xlfn.COT(", formula) formula = re.sub(r"\bCOVARIANCE.P\(", "_xlfn.COVARIANCE.P(", formula) formula = re.sub(r"\bCOVARIANCE.S\(", "_xlfn.COVARIANCE.S(", formula) formula = re.sub(r"\bCSCH\(", "_xlfn.CSCH(", formula) formula = re.sub(r"\bCSC\(", "_xlfn.CSC(", formula) formula = re.sub(r"\bDAYS\(", "_xlfn.DAYS(", formula) formula = re.sub(r"\bDECIMAL\(", "_xlfn.DECIMAL(", formula) formula = re.sub(r"\bERF.PRECISE\(", "_xlfn.ERF.PRECISE(", formula) formula = re.sub(r"\bERFC.PRECISE\(", "_xlfn.ERFC.PRECISE(", formula) formula = re.sub(r"\bEXPON.DIST\(", "_xlfn.EXPON.DIST(", formula) formula = re.sub(r"\bF.DIST.RT\(", "_xlfn.F.DIST.RT(", formula) formula = re.sub(r"\bF.DIST\(", "_xlfn.F.DIST(", formula) formula = re.sub(r"\bF.INV.RT\(", "_xlfn.F.INV.RT(", formula) formula = re.sub(r"\bF.INV\(", "_xlfn.F.INV(", formula) formula = re.sub(r"\bF.TEST\(", "_xlfn.F.TEST(", formula) formula = re.sub(r"\bFILTERXML\(", "_xlfn.FILTERXML(", formula) formula = re.sub(r"\bFLOOR.MATH\(", "_xlfn.FLOOR.MATH(", formula) formula = re.sub(r"\bFLOOR.PRECISE\(", "_xlfn.FLOOR.PRECISE(", formula) formula = re.sub( r"\bFORECAST.ETS.CONFINT\(", "_xlfn.FORECAST.ETS.CONFINT(", formula ) formula = re.sub( r"\bFORECAST.ETS.SEASONALITY\(", "_xlfn.FORECAST.ETS.SEASONALITY(", formula ) formula = re.sub(r"\bFORECAST.ETS.STAT\(", "_xlfn.FORECAST.ETS.STAT(", formula) formula = re.sub(r"\bFORECAST.ETS\(", "_xlfn.FORECAST.ETS(", formula) formula = re.sub(r"\bFORECAST.LINEAR\(", "_xlfn.FORECAST.LINEAR(", formula) formula = re.sub(r"\bFORMULATEXT\(", "_xlfn.FORMULATEXT(", formula) formula = re.sub(r"\bGAMMA.DIST\(", "_xlfn.GAMMA.DIST(", formula) formula = re.sub(r"\bGAMMA.INV\(", "_xlfn.GAMMA.INV(", formula) formula = re.sub(r"\bGAMMALN.PRECISE\(", "_xlfn.GAMMALN.PRECISE(", formula) formula = re.sub(r"\bGAMMA\(", "_xlfn.GAMMA(", formula) formula = re.sub(r"\bGAUSS\(", "_xlfn.GAUSS(", formula) formula = re.sub(r"\bHYPGEOM.DIST\(", "_xlfn.HYPGEOM.DIST(", formula) formula = re.sub(r"\bIFNA\(", "_xlfn.IFNA(", formula) formula = re.sub(r"\bIFS\(", "_xlfn.IFS(", formula) formula = re.sub(r"\bIMAGE\(", "_xlfn.IMAGE(", formula) formula = re.sub(r"\bIMCOSH\(", "_xlfn.IMCOSH(", formula) formula = re.sub(r"\bIMCOT\(", "_xlfn.IMCOT(", formula) formula = re.sub(r"\bIMCSCH\(", "_xlfn.IMCSCH(", formula) formula = re.sub(r"\bIMCSC\(", "_xlfn.IMCSC(", formula) formula = re.sub(r"\bIMSECH\(", "_xlfn.IMSECH(", formula) formula = re.sub(r"\bIMSEC\(", "_xlfn.IMSEC(", formula) formula = re.sub(r"\bIMSINH\(", "_xlfn.IMSINH(", formula) formula = re.sub(r"\bIMTAN\(", "_xlfn.IMTAN(", formula) formula = re.sub(r"\bISFORMULA\(", "_xlfn.ISFORMULA(", formula) formula = re.sub(r"\bISOMITTED\(", "_xlfn.ISOMITTED(", formula) formula = re.sub(r"\bISOWEEKNUM\(", "_xlfn.ISOWEEKNUM(", formula) formula = re.sub(r"\bLET\(", "_xlfn.LET(", formula) formula = re.sub(r"\bLOGNORM.DIST\(", "_xlfn.LOGNORM.DIST(", formula) formula = re.sub(r"\bLOGNORM.INV\(", "_xlfn.LOGNORM.INV(", formula) formula = re.sub(r"\bMAXIFS\(", "_xlfn.MAXIFS(", formula) formula = re.sub(r"\bMINIFS\(", "_xlfn.MINIFS(", formula) formula = re.sub(r"\bMODE.MULT\(", "_xlfn.MODE.MULT(", formula) formula = re.sub(r"\bMODE.SNGL\(", "_xlfn.MODE.SNGL(", formula) formula = re.sub(r"\bMUNIT\(", "_xlfn.MUNIT(", formula) formula = re.sub(r"\bNEGBINOM.DIST\(", "_xlfn.NEGBINOM.DIST(", formula) formula = re.sub(r"\bNORM.DIST\(", "_xlfn.NORM.DIST(", formula) formula = re.sub(r"\bNORM.INV\(", "_xlfn.NORM.INV(", formula) formula = re.sub(r"\bNORM.S.DIST\(", "_xlfn.NORM.S.DIST(", formula) formula = re.sub(r"\bNORM.S.INV\(", "_xlfn.NORM.S.INV(", formula) formula = re.sub(r"\bNUMBERVALUE\(", "_xlfn.NUMBERVALUE(", formula) formula = re.sub(r"\bPDURATION\(", "_xlfn.PDURATION(", formula) formula = re.sub(r"\bPERCENTILE.EXC\(", "_xlfn.PERCENTILE.EXC(", formula) formula = re.sub(r"\bPERCENTILE.INC\(", "_xlfn.PERCENTILE.INC(", formula) formula = re.sub(r"\bPERCENTRANK.EXC\(", "_xlfn.PERCENTRANK.EXC(", formula) formula = re.sub(r"\bPERCENTRANK.INC\(", "_xlfn.PERCENTRANK.INC(", formula) formula = re.sub(r"\bPERMUTATIONA\(", "_xlfn.PERMUTATIONA(", formula) formula = re.sub(r"\bPHI\(", "_xlfn.PHI(", formula) formula = re.sub(r"\bPOISSON.DIST\(", "_xlfn.POISSON.DIST(", formula) formula = re.sub(r"\bQUARTILE.EXC\(", "_xlfn.QUARTILE.EXC(", formula) formula = re.sub(r"\bQUARTILE.INC\(", "_xlfn.QUARTILE.INC(", formula) formula = re.sub(r"\bQUERYSTRING\(", "_xlfn.QUERYSTRING(", formula) formula = re.sub(r"\bRANK.AVG\(", "_xlfn.RANK.AVG(", formula) formula = re.sub(r"\bRANK.EQ\(", "_xlfn.RANK.EQ(", formula) formula = re.sub(r"\bRRI\(", "_xlfn.RRI(", formula) formula = re.sub(r"\bSECH\(", "_xlfn.SECH(", formula) formula = re.sub(r"\bSEC\(", "_xlfn.SEC(", formula) formula = re.sub(r"\bSHEETS\(", "_xlfn.SHEETS(", formula) formula = re.sub(r"\bSHEET\(", "_xlfn.SHEET(", formula) formula = re.sub(r"\bSKEW.P\(", "_xlfn.SKEW.P(", formula) formula = re.sub(r"\bSTDEV.P\(", "_xlfn.STDEV.P(", formula) formula = re.sub(r"\bSTDEV.S\(", "_xlfn.STDEV.S(", formula) formula = re.sub(r"\bT.DIST.2T\(", "_xlfn.T.DIST.2T(", formula) formula = re.sub(r"\bT.DIST.RT\(", "_xlfn.T.DIST.RT(", formula) formula = re.sub(r"\bT.DIST\(", "_xlfn.T.DIST(", formula) formula = re.sub(r"\bT.INV.2T\(", "_xlfn.T.INV.2T(", formula) formula = re.sub(r"\bT.INV\(", "_xlfn.T.INV(", formula) formula = re.sub(r"\bT.TEST\(", "_xlfn.T.TEST(", formula) formula = re.sub(r"\bTEXTAFTER\(", "_xlfn.TEXTAFTER(", formula) formula = re.sub(r"\bTEXTBEFORE\(", "_xlfn.TEXTBEFORE(", formula) formula = re.sub(r"\bTEXTJOIN\(", "_xlfn.TEXTJOIN(", formula) formula = re.sub(r"\bUNICHAR\(", "_xlfn.UNICHAR(", formula) formula = re.sub(r"\bUNICODE\(", "_xlfn.UNICODE(", formula) formula = re.sub(r"\bVALUETOTEXT\(", "_xlfn.VALUETOTEXT(", formula) formula = re.sub(r"\bVAR.P\(", "_xlfn.VAR.P(", formula) formula = re.sub(r"\bVAR.S\(", "_xlfn.VAR.S(", formula) formula = re.sub(r"\bWEBSERVICE\(", "_xlfn.WEBSERVICE(", formula) formula = re.sub(r"\bWEIBULL.DIST\(", "_xlfn.WEIBULL.DIST(", formula) formula = re.sub(r"\bXMATCH\(", "_xlfn.XMATCH(", formula) formula = re.sub(r"\bXOR\(", "_xlfn.XOR(", formula) formula = re.sub(r"\bZ.TEST\(", "_xlfn.Z.TEST(", formula) return formula # Escape/expand table functions. This mainly involves converting Excel 2010 # "@" table ref to 2007 "[#This Row],". We parse the string to avoid # replacements in string literals within the formula. @staticmethod def _prepare_table_formula(formula): if "@" not in formula: # No escaping required. return formula escaped_formula = [] in_string_literal = False for char in formula: # Match the start/end of string literals to avoid escaping # references in strings. if char == '"': in_string_literal = not in_string_literal # Copy the string literal. if in_string_literal: escaped_formula.append(char) continue # Replace table reference. if char == "@": escaped_formula.append("[#This Row],") else: escaped_formula.append(char) return ("").join(escaped_formula) # Undecorated version of write_array_formula() and # write_dynamic_array_formula(). def _write_array_formula( self, first_row, first_col, last_row, last_col, formula, cell_format=None, value=0, atype="static", ): # Swap last row/col with first row/col as necessary. if first_row > last_row: first_row, last_row = last_row, first_row if first_col > last_col: first_col, last_col = last_col, first_col # Check that row and col are valid and store max and min values. if self._check_dimensions(first_row, first_col): return -1 if self._check_dimensions(last_row, last_col): return -1 # Define array range if first_row == last_row and first_col == last_col: cell_range = xl_rowcol_to_cell(first_row, first_col) else: cell_range = ( xl_rowcol_to_cell(first_row, first_col) + ":" + xl_rowcol_to_cell(last_row, last_col) ) # Modify the formula string, as needed. formula = self._prepare_formula(formula) # Write previous row if in in-line string constant_memory mode. if self.constant_memory and first_row > self.previous_row: self._write_single_row(first_row) # Store the cell data in the worksheet data table. self.table[first_row][first_col] = CellArrayFormulaTuple( formula, cell_format, value, cell_range, atype ) # Pad out the rest of the area with formatted zeroes. if not self.constant_memory: for row in range(first_row, last_row + 1): for col in range(first_col, last_col + 1): if row != first_row or col != first_col: self._write_number(row, col, 0, cell_format) return 0 @convert_cell_args def write_datetime(self, row, col, date, cell_format=None): """ Write a date or time to a worksheet cell. Args: row: The cell row (zero indexed). col: The cell column (zero indexed). date: Date and/or time as a datetime object. cell_format: A cell Format object. Returns: 0: Success. -1: Row or column is out of worksheet bounds. """ return self._write_datetime(row, col, date, cell_format) # Undecorated version of write_datetime(). def _write_datetime(self, row, col, date, cell_format=None): # Check that row and col are valid and store max and min values. if self._check_dimensions(row, col): return -1 # Write previous row if in in-line string constant_memory mode. if self.constant_memory and row > self.previous_row: self._write_single_row(row) # Convert datetime to an Excel date. number = self._convert_date_time(date) # Add the default date format. if cell_format is None: cell_format = self.default_date_format # Store the cell data in the worksheet data table. self.table[row][col] = CellDatetimeTuple(number, cell_format) return 0 @convert_cell_args def write_boolean(self, row, col, boolean, cell_format=None): """ Write a boolean value to a worksheet cell. Args: row: The cell row (zero indexed). col: The cell column (zero indexed). boolean: Cell data. bool type. cell_format: An optional cell Format object. Returns: 0: Success. -1: Row or column is out of worksheet bounds. """ return self._write_boolean(row, col, boolean, cell_format) # Undecorated version of write_boolean(). def _write_boolean(self, row, col, boolean, cell_format=None): # Check that row and col are valid and store max and min values. if self._check_dimensions(row, col): return -1 # Write previous row if in in-line string constant_memory mode. if self.constant_memory and row > self.previous_row: self._write_single_row(row) if boolean: value = 1 else: value = 0 # Store the cell data in the worksheet data table. self.table[row][col] = CellBooleanTuple(value, cell_format) return 0 # Write a hyperlink. This is comprised of two elements: the displayed # string and the non-displayed link. The displayed string is the same as # the link unless an alternative string is specified. The display string # is written using the write_string() method. Therefore the max characters # string limit applies. # # The hyperlink can be to a http, ftp, mail, internal sheet, or external # directory urls. @convert_cell_args def write_url(self, row, col, url, cell_format=None, string=None, tip=None): """ Write a hyperlink to a worksheet cell. Args: row: The cell row (zero indexed). col: The cell column (zero indexed). url: Hyperlink url. format: An optional cell Format object. string: An optional display string for the hyperlink. tip: An optional tooltip. Returns: 0: Success. -1: Row or column is out of worksheet bounds. -2: String longer than 32767 characters. -3: URL longer than Excel limit of 255 characters. -4: Exceeds Excel limit of 65,530 urls per worksheet. """ return self._write_url(row, col, url, cell_format, string, tip) # Undecorated version of write_url(). def _write_url(self, row, col, url, cell_format=None, string=None, tip=None): # Check that row and col are valid and store max and min values if self._check_dimensions(row, col): return -1 # If the URL is a string convert it to a Url object. if not isinstance(url, Url): url = Url(url) if string is not None: url._text = string if tip is not None: url._tip = tip # Check the limit of URLs per worksheet. self.hlink_count += 1 if self.hlink_count > 65530: warn( f"Ignoring URL '{url._original_url}' since it exceeds Excel's limit of " f"65,530 URLs per worksheet." ) return -4 # Add the default URL format. if cell_format is None: cell_format = self.default_url_format if not self.ignore_write_string: # Write previous row if in in-line string constant_memory mode. if self.constant_memory and row > self.previous_row: self._write_single_row(row) # Write the hyperlink string. self._write_string(row, col, url.text, cell_format) # Store the hyperlink data in a separate structure. self.hyperlinks[row][col] = url return 0 @convert_cell_args def write_rich_string(self, row, col, *args): """ Write a "rich" string with multiple formats to a worksheet cell. Args: row: The cell row (zero indexed). col: The cell column (zero indexed). string_parts: String and format pairs. cell_format: Optional Format object. Returns: 0: Success. -1: Row or column is out of worksheet bounds. -2: String truncated to 32k characters. -3: 2 consecutive formats used. -4: Empty string used. -5: Insufficient parameters. """ return self._write_rich_string(row, col, *args) # Undecorated version of write_rich_string(). def _write_rich_string(self, row, col, *args): tokens = list(args) cell_format = None string_index = 0 raw_string = "" # Check that row and col are valid and store max and min values if self._check_dimensions(row, col): return -1 # If the last arg is a format we use it as the cell format. if isinstance(tokens[-1], Format): cell_format = tokens.pop() # Create a temp XMLWriter object and use it to write the rich string # XML to a string. fh = StringIO() self.rstring = XMLwriter() self.rstring._set_filehandle(fh) # Create a temp format with the default font for unformatted fragments. default = Format() # Convert list of format, string tokens to pairs of (format, string) # except for the first string fragment which doesn't require a default # formatting run. Use the default for strings without a leading format. fragments = [] previous = "format" pos = 0 if len(tokens) <= 2: warn( "You must specify more than 2 format/fragments for rich " "strings. Ignoring input in write_rich_string()." ) return -5 for token in tokens: if not isinstance(token, Format): # Token is a string. if previous != "format": # If previous token wasn't a format add one before string. fragments.append(default) fragments.append(token) else: # If previous token was a format just add the string. fragments.append(token) if token == "": warn( "Excel doesn't allow empty strings in rich strings. " "Ignoring input in write_rich_string()." ) return -4 # Keep track of unformatted string. raw_string += token previous = "string" else: # Can't allow 2 formats in a row. if previous == "format" and pos > 0: warn( "Excel doesn't allow 2 consecutive formats in rich " "strings. Ignoring input in write_rich_string()." ) return -3 # Token is a format object. Add it to the fragment list. fragments.append(token) previous = "format" pos += 1 # If the first token is a string start the element. if not isinstance(fragments[0], Format): self.rstring._xml_start_tag("r") # Write the XML elements for the $format $string fragments. for token in fragments: if isinstance(token, Format): # Write the font run. self.rstring._xml_start_tag("r") self._write_font(token) else: # Write the string fragment part, with whitespace handling. attributes = [] if _preserve_whitespace(token): attributes.append(("xml:space", "preserve")) self.rstring._xml_data_element("t", token, attributes) self.rstring._xml_end_tag("r") # Read the in-memory string. string = self.rstring.fh.getvalue() # Check that the string is < 32767 chars. if len(raw_string) > self.xls_strmax: warn( "String length must be less than or equal to Excel's limit " "of 32,767 characters in write_rich_string()." ) return -2 # Write a shared string or an in-line string in constant_memory mode. if not self.constant_memory: string_index = self.str_table._get_shared_string_index(string) else: string_index = string # Write previous row if in in-line string constant_memory mode. if self.constant_memory and row > self.previous_row: self._write_single_row(row) # Store the cell data in the worksheet data table. self.table[row][col] = CellRichStringTuple( string_index, cell_format, raw_string ) return 0 def add_write_handler(self, user_type, user_function): """ Add a callback function to the write() method to handle user defined types. Args: user_type: The user type() to match on. user_function: The user defined function to write the type data. Returns: Nothing. """ self.write_handlers[user_type] = user_function @convert_cell_args def write_row(self, row, col, data, cell_format=None): """ Write a row of data starting from (row, col). Args: row: The cell row (zero indexed). col: The cell column (zero indexed). data: A list of tokens to be written with write(). format: An optional cell Format object. Returns: 0: Success. other: Return value of write() method. """ for token in data: error = self._write(row, col, token, cell_format) if error: return error col += 1 return 0 @convert_cell_args def write_column(self, row, col, data, cell_format=None): """ Write a column of data starting from (row, col). Args: row: The cell row (zero indexed). col: The cell column (zero indexed). data: A list of tokens to be written with write(). format: An optional cell Format object. Returns: 0: Success. other: Return value of write() method. """ for token in data: error = self._write(row, col, token, cell_format) if error: return error row += 1 return 0 @convert_cell_args def insert_image(self, row, col, source, options=None): """ Insert an image with its top-left corner in a worksheet cell. Args: row: The cell row (zero indexed). col: The cell column (zero indexed). source: Filename, BytesIO, or Image object. options: Position, scale, url and data stream of the image. Returns: 0: Success. -1: Row or column is out of worksheet bounds. """ # Check insert (row, col) without storing. if self._check_dimensions(row, col, True, True): warn(f"Cannot insert image at ({row}, {col}).") return -1 # Convert the source to an Image object. image = self._image_from_source(source, options) image._row = row image._col = col image._set_user_options(options) self.images.append(image) return 0 @convert_cell_args def embed_image(self, row, col, source, options=None): """ Embed an image in a worksheet cell. Args: row: The cell row (zero indexed). col: The cell column (zero indexed). source: Filename, BytesIO, or Image object. options: Url and data stream of the image. Returns: 0: Success. -1: Row or column is out of worksheet bounds. """ # Check insert (row, col) without storing. if self._check_dimensions(row, col): warn(f"Cannot embed image at ({row}, {col}).") return -1 if options is None: options = {} # Convert the source to an Image object. image = self._image_from_source(source, options) image._set_user_options(options) cell_format = options.get("cell_format", None) if image.url: if cell_format is None: cell_format = self.default_url_format self.ignore_write_string = True self.write_url(row, col, image.url, cell_format) self.ignore_write_string = False image_index = self.embedded_images.get_image_index(image) # Store the cell error and image index in the worksheet data table. self.table[row][col] = CellErrorTuple("#VALUE!", cell_format, image_index) return 0 @convert_cell_args def insert_textbox(self, row, col, text, options=None): """ Insert an textbox with its top-left corner in a worksheet cell. Args: row: The cell row (zero indexed). col: The cell column (zero indexed). text: The text for the textbox. options: Textbox options. Returns: 0: Success. -1: Row or column is out of worksheet bounds. """ # Check insert (row, col) without storing. if self._check_dimensions(row, col, True, True): warn(f"Cannot insert textbox at ({row}, {col}).") return -1 if text is None: text = "" if options is None: options = {} x_offset = options.get("x_offset", 0) y_offset = options.get("y_offset", 0) x_scale = options.get("x_scale", 1) y_scale = options.get("y_scale", 1) anchor = options.get("object_position", 1) description = options.get("description", None) decorative = options.get("decorative", False) self.shapes.append( [ row, col, x_offset, y_offset, x_scale, y_scale, text, anchor, options, description, decorative, ] ) return 0 @convert_cell_args def insert_chart(self, row, col, chart, options=None): """ Insert an chart with its top-left corner in a worksheet cell. Args: row: The cell row (zero indexed). col: The cell column (zero indexed). chart: Chart object. options: Position and scale of the chart. Returns: 0: Success. -1: Row or column is out of worksheet bounds. """ # Check insert (row, col) without storing. if self._check_dimensions(row, col, True, True): warn(f"Cannot insert chart at ({row}, {col}).") return -1 if options is None: options = {} # Ensure a chart isn't inserted more than once. if chart.already_inserted or chart.combined and chart.combined.already_inserted: warn("Chart cannot be inserted in a worksheet more than once.") return -2 chart.already_inserted = True if chart.combined: chart.combined.already_inserted = True x_offset = options.get("x_offset", 0) y_offset = options.get("y_offset", 0) x_scale = options.get("x_scale", 1) y_scale = options.get("y_scale", 1) anchor = options.get("object_position", 1) description = options.get("description", None) decorative = options.get("decorative", False) # Allow Chart to override the scale and offset. if chart.x_scale != 1: x_scale = chart.x_scale if chart.y_scale != 1: y_scale = chart.y_scale if chart.x_offset: x_offset = chart.x_offset if chart.y_offset: y_offset = chart.y_offset self.charts.append( [ row, col, chart, x_offset, y_offset, x_scale, y_scale, anchor, description, decorative, ] ) return 0 @convert_cell_args def write_comment(self, row, col, comment, options=None): """ Write a comment to a worksheet cell. Args: row: The cell row (zero indexed). col: The cell column (zero indexed). comment: Cell comment. Str. options: Comment formatting options. Returns: 0: Success. -1: Row or column is out of worksheet bounds. -2: String longer than 32k characters. """ # Check that row and col are valid and store max and min values if self._check_dimensions(row, col): return -1 # Check that the comment string is < 32767 chars. if len(comment) > self.xls_strmax: return -2 self.has_vml = True self.has_comments = True # Store the options of the cell comment, to process on file close. comment = CommentType(row, col, comment, options) self.comments[row][col] = comment return 0 def show_comments(self): """ Make any comments in the worksheet visible. Args: None. Returns: Nothing. """ self.comments_visible = True def set_background(self, source, is_byte_stream=False): """ Set a background image for a worksheet. Args: source: Filename, BytesIO, or Image object. is_byte_stream: Deprecated. Use a BytesIO object instead. Returns: 0: Success. """ # Convert the source to an Image object. image = self._image_from_source(source) self.background_image = image if is_byte_stream: warn( "The `is_byte_stream` parameter in `set_background()` is deprecated. " "This argument can be omitted if you are using a BytesIO object." ) return 0 def set_comments_author(self, author): """ Set the default author of the cell comments. Args: author: Comment author name. String. Returns: Nothing. """ self.comments_author = author def get_name(self): """ Retrieve the worksheet name. Args: None. Returns: Nothing. """ # There is no set_name() method. Name must be set in add_worksheet(). return self.name def activate(self): """ Set this worksheet as the active worksheet, i.e. the worksheet that is displayed when the workbook is opened. Also set it as selected. Note: An active worksheet cannot be hidden. Args: None. Returns: Nothing. """ self.hidden = 0 self.selected = 1 self.worksheet_meta.activesheet = self.index def select(self): """ Set current worksheet as a selected worksheet, i.e. the worksheet has its tab highlighted. Note: A selected worksheet cannot be hidden. Args: None. Returns: Nothing. """ self.selected = 1 self.hidden = 0 def hide(self): """ Hide the current worksheet. Args: None. Returns: Nothing. """ self.hidden = 1 # A hidden worksheet shouldn't be active or selected. self.selected = 0 def very_hidden(self): """ Hide the current worksheet. This can only be unhidden by VBA. Args: None. Returns: Nothing. """ self.hidden = 2 # A hidden worksheet shouldn't be active or selected. self.selected = 0 def set_first_sheet(self): """ Set current worksheet as the first visible sheet. This is necessary when there are a large number of worksheets and the activated worksheet is not visible on the screen. Note: A selected worksheet cannot be hidden. Args: None. Returns: Nothing. """ self.hidden = 0 # Active worksheet can't be hidden. self.worksheet_meta.firstsheet = self.index @convert_column_args def set_column( self, first_col, last_col, width=None, cell_format=None, options=None ): """ Set the width, and other properties of a single column or a range of columns. Args: first_col: First column (zero-indexed). last_col: Last column (zero-indexed). Can be same as first_col. width: Column width. (optional). cell_format: Column cell_format. (optional). options: Dict of options such as hidden and level. Returns: 0: Success. -1: Column number is out of worksheet bounds. """ if options is None: options = {} # Ensure 2nd col is larger than first. if first_col > last_col: (first_col, last_col) = (last_col, first_col) # Don't modify the row dimensions when checking the columns. ignore_row = True # Set optional column values. hidden = options.get("hidden", False) collapsed = options.get("collapsed", False) level = options.get("level", 0) # Store the column dimension only in some conditions. if cell_format or (width and hidden): ignore_col = False else: ignore_col = True # Check that each column is valid and store the max and min values. if self._check_dimensions(0, last_col, ignore_row, ignore_col): return -1 if self._check_dimensions(0, first_col, ignore_row, ignore_col): return -1 # Set the limits for the outline levels (0 <= x <= 7). level = max(level, 0) level = min(level, 7) self.outline_col_level = max(self.outline_col_level, level) # Store the column data. for col in range(first_col, last_col + 1): self.col_info[col] = [width, cell_format, hidden, level, collapsed, False] # Store the column change to allow optimizations. self.col_size_changed = True return 0 @convert_column_args def set_column_pixels( self, first_col, last_col, width=None, cell_format=None, options=None ): """ Set the width, and other properties of a single column or a range of columns, where column width is in pixels. Args: first_col: First column (zero-indexed). last_col: Last column (zero-indexed). Can be same as first_col. width: Column width in pixels. (optional). cell_format: Column cell_format. (optional). options: Dict of options such as hidden and level. Returns: 0: Success. -1: Column number is out of worksheet bounds. """ if width is not None: width = self._pixels_to_width(width) return self.set_column(first_col, last_col, width, cell_format, options) def autofit(self, max_width=1790): """ Simulate autofit based on the data, and datatypes in each column. Args: max_width (optional): max column width to autofit, in pixels. Returns: Nothing. """ # pylint: disable=too-many-nested-blocks if self.constant_memory: warn("Autofit is not supported in constant_memory mode.") return # No data written to the target sheet; nothing to autofit if self.dim_rowmax is None: return # Store the max pixel width for each column. col_width_max = {} # Convert the autofit maximum pixel width to a column/character width, # but limit it to the Excel max limit. max_width = min(self._pixels_to_width(max_width), 255.0) # Create a reverse lookup for the share strings table so we can convert # the string id back to the original string. strings = sorted( self.str_table.string_table, key=self.str_table.string_table.__getitem__ ) for row_num in range(self.dim_rowmin, self.dim_rowmax + 1): if not self.table.get(row_num): continue for col_num in range(self.dim_colmin, self.dim_colmax + 1): if col_num in self.table[row_num]: cell = self.table[row_num][col_num] cell_type = cell.__class__.__name__ length = 0 if cell_type in ("String", "RichString"): # Handle strings and rich strings. # # For standard shared strings we do a reverse lookup # from the shared string id to the actual string. For # rich strings we use the unformatted string. We also # split multi-line strings and handle each part # separately. if cell_type == "String": string_id = cell.string string = strings[string_id] else: string = cell.raw_string if "\n" not in string: # Single line string. length = xl_pixel_width(string) else: # Handle multi-line strings. for string in string.split("\n"): seg_length = xl_pixel_width(string) length = max(length, seg_length) elif cell_type == "Number": # Handle numbers. # # We use a workaround/optimization for numbers since # digits all have a pixel width of 7. This gives a # slightly greater width for the decimal place and # minus sign but only by a few pixels and # over-estimation is okay. length = 7 * len(str(cell.number)) elif cell_type == "Datetime": # Handle dates. # # The following uses the default width for mm/dd/yyyy # dates. It isn't feasible to parse the number format # to get the actual string width for all format types. length = self.default_date_pixels elif cell_type == "Boolean": # Handle boolean values. # # Use the Excel standard widths for TRUE and FALSE. if cell.boolean: length = 31 else: length = 36 elif cell_type in ("Formula", "ArrayFormula"): # Handle formulas. # # We only try to autofit a formula if it has a # non-zero value. if isinstance(cell.value, (float, int)): if cell.value > 0: length = 7 * len(str(cell.value)) elif isinstance(cell.value, str): length = xl_pixel_width(cell.value) elif isinstance(cell.value, bool): if cell.value: length = 31 else: length = 36 # If the cell is in an autofilter header we add an # additional 16 pixels for the dropdown arrow. if self.filter_cells.get((row_num, col_num)) and length > 0: length += 16 # Add the string length to the lookup table. width_max = col_width_max.get(col_num, 0) if length > width_max: col_width_max[col_num] = length # Apply the width to the column. for col_num, pixel_width in col_width_max.items(): # Convert the string pixel width to a character width using an # additional padding of 7 pixels, like Excel. width = self._pixels_to_width(pixel_width + 7) # Limit the width to the maximum user or Excel value. width = min(width, max_width) # Add the width to an existing col info structure or add a new one. if self.col_info.get(col_num): # We only update the width for an existing column if it is # greater than the user defined value. This allows the user # to pre-load a minimum col width. col_info = self.col_info.get(col_num) user_width = col_info[0] hidden = col_info[5] if user_width is not None and not hidden: # Col info is user defined. if width > user_width: self.col_info[col_num][0] = width self.col_info[col_num][5] = True else: self.col_info[col_num][0] = width self.col_info[col_num][5] = True else: self.col_info[col_num] = [width, None, False, 0, False, True] def set_row(self, row, height=None, cell_format=None, options=None): """ Set the width, and other properties of a row. Args: row: Row number (zero-indexed). height: Row height. (optional). cell_format: Row cell_format. (optional). options: Dict of options such as hidden, level and collapsed. Returns: 0: Success. -1: Row number is out of worksheet bounds. """ if options is None: options = {} # Use minimum col in _check_dimensions(). if self.dim_colmin is not None: min_col = self.dim_colmin else: min_col = 0 # Check that row is valid. if self._check_dimensions(row, min_col): return -1 if height is None: height = self.default_row_height # Set optional row values. hidden = options.get("hidden", False) collapsed = options.get("collapsed", False) level = options.get("level", 0) # If the height is 0 the row is hidden and the height is the default. if height == 0: hidden = 1 height = self.default_row_height # Set the limits for the outline levels (0 <= x <= 7). level = max(level, 0) level = min(level, 7) self.outline_row_level = max(self.outline_row_level, level) # Store the row properties. self.set_rows[row] = [height, cell_format, hidden, level, collapsed] # Store the row change to allow optimizations. self.row_size_changed = True # Store the row sizes for use when calculating image vertices. self.row_sizes[row] = [height, hidden] return 0 def set_row_pixels(self, row, height=None, cell_format=None, options=None): """ Set the width (in pixels), and other properties of a row. Args: row: Row number (zero-indexed). height: Row height in pixels. (optional). cell_format: Row cell_format. (optional). options: Dict of options such as hidden, level and collapsed. Returns: 0: Success. -1: Row number is out of worksheet bounds. """ if height is not None: height = self._pixels_to_height(height) return self.set_row(row, height, cell_format, options) def set_default_row(self, height=None, hide_unused_rows=False): """ Set the default row properties. Args: height: Default height. Optional, defaults to 15. hide_unused_rows: Hide unused rows. Optional, defaults to False. Returns: Nothing. """ if height is None: height = self.default_row_height if height != self.original_row_height: # Store the row change to allow optimizations. self.row_size_changed = True self.default_row_height = height if hide_unused_rows: self.default_row_zeroed = 1 @convert_range_args def merge_range( self, first_row, first_col, last_row, last_col, data, cell_format=None ): """ Merge a range of cells. Args: first_row: The first row of the cell range. (zero indexed). first_col: The first column of the cell range. last_row: The last row of the cell range. (zero indexed). last_col: The last column of the cell range. data: Cell data. cell_format: Cell Format object. Returns: 0: Success. -1: Row or column is out of worksheet bounds. other: Return value of write(). """ # Merge a range of cells. The first cell should contain the data and # the others should be blank. All cells should have the same format. # Excel doesn't allow a single cell to be merged if first_row == last_row and first_col == last_col: warn("Can't merge single cell") return -1 # Swap last row/col with first row/col as necessary if first_row > last_row: (first_row, last_row) = (last_row, first_row) if first_col > last_col: (first_col, last_col) = (last_col, first_col) # Check that row and col are valid and store max and min values. if self._check_dimensions(first_row, first_col): return -1 if self._check_dimensions(last_row, last_col): return -1 # Check if the merge range overlaps a previous merged or table range. # This is a critical file corruption error in Excel. cell_range = xl_range(first_row, first_col, last_row, last_col) for row in range(first_row, last_row + 1): for col in range(first_col, last_col + 1): if self.merged_cells.get((row, col)): previous_range = self.merged_cells.get((row, col)) raise OverlappingRange( f"Merge range '{cell_range}' overlaps previous merge " f"range '{previous_range}'." ) if self.table_cells.get((row, col)): previous_range = self.table_cells.get((row, col)) raise OverlappingRange( f"Merge range '{cell_range}' overlaps previous table " f"range '{previous_range}'." ) self.merged_cells[(row, col)] = cell_range # Store the merge range. self.merge.append([first_row, first_col, last_row, last_col]) # Write the first cell self._write(first_row, first_col, data, cell_format) # Pad out the rest of the area with formatted blank cells. for row in range(first_row, last_row + 1): for col in range(first_col, last_col + 1): if row == first_row and col == first_col: continue self._write_blank(row, col, "", cell_format) return 0 @convert_range_args def autofilter(self, first_row, first_col, last_row, last_col): """ Set the autofilter area in the worksheet. Args: first_row: The first row of the cell range. (zero indexed). first_col: The first column of the cell range. last_row: The last row of the cell range. (zero indexed). last_col: The last column of the cell range. Returns: Nothing. """ # Reverse max and min values if necessary. if last_row < first_row: (first_row, last_row) = (last_row, first_row) if last_col < first_col: (first_col, last_col) = (last_col, first_col) # Build up the autofilter area range "Sheet1!$A$1:$C$13". area = self._convert_name_area(first_row, first_col, last_row, last_col) ref = xl_range(first_row, first_col, last_row, last_col) self.autofilter_area = area self.autofilter_ref = ref self.filter_range = [first_col, last_col] # Store the filter cell positions for use in the autofit calculation. for col in range(first_col, last_col + 1): # Check that the autofilter doesn't overlap a table filter. if self.filter_cells.get((first_row, col)): filter_type, filter_range = self.filter_cells.get((first_row, col)) if filter_type == "table": raise OverlappingRange( f"Worksheet autofilter range '{ref}' overlaps previous " f"Table autofilter range '{filter_range}'." ) self.filter_cells[(first_row, col)] = ("worksheet", ref) def filter_column(self, col, criteria): """ Set the column filter criteria. Args: col: Filter column (zero-indexed). criteria: Filter criteria. Returns: Nothing. """ if not self.autofilter_area: warn("Must call autofilter() before filter_column()") return # Check for a column reference in A1 notation and substitute. try: int(col) except ValueError: # Convert col ref to a cell ref and then to a col number. col_letter = col (_, col) = xl_cell_to_rowcol(col + "1") if col >= self.xls_colmax: warn(f"Invalid column '{col_letter}'") return (col_first, col_last) = self.filter_range # Reject column if it is outside filter range. if col < col_first or col > col_last: warn( f"Column '{col}' outside autofilter() column " f"range ({col_first}, {col_last})" ) return tokens = self._extract_filter_tokens(criteria) if len(tokens) not in (3, 7): warn(f"Incorrect number of tokens in criteria '{criteria}'") tokens = self._parse_filter_expression(criteria, tokens) # Excel handles single or double custom filters as default filters. # We need to check for them and handle them accordingly. if len(tokens) == 2 and tokens[0] == 2: # Single equality. self.filter_column_list(col, [tokens[1]]) elif len(tokens) == 5 and tokens[0] == 2 and tokens[2] == 1 and tokens[3] == 2: # Double equality with "or" operator. self.filter_column_list(col, [tokens[1], tokens[4]]) else: # Non default custom filter. self.filter_cols[col] = tokens self.filter_type[col] = 0 self.filter_on = 1 def filter_column_list(self, col, filters): """ Set the column filter criteria in Excel 2007 list style. Args: col: Filter column (zero-indexed). filters: List of filter criteria to match. Returns: Nothing. """ if not self.autofilter_area: warn("Must call autofilter() before filter_column()") return # Check for a column reference in A1 notation and substitute. try: int(col) except ValueError: # Convert col ref to a cell ref and then to a col number. col_letter = col (_, col) = xl_cell_to_rowcol(col + "1") if col >= self.xls_colmax: warn(f"Invalid column '{col_letter}'") return (col_first, col_last) = self.filter_range # Reject column if it is outside filter range. if col < col_first or col > col_last: warn( f"Column '{col}' outside autofilter() column range " f"({col_first},{col_last})" ) return self.filter_cols[col] = filters self.filter_type[col] = 1 self.filter_on = 1 @convert_range_args def data_validation(self, first_row, first_col, last_row, last_col, options=None): """ Add a data validation to a worksheet. Args: first_row: The first row of the cell range. (zero indexed). first_col: The first column of the cell range. last_row: The last row of the cell range. (zero indexed). last_col: The last column of the cell range. options: Data validation options. Returns: 0: Success. -1: Row or column is out of worksheet bounds. -2: Incorrect parameter or option. """ # Check that row and col are valid without storing the values. if self._check_dimensions(first_row, first_col, True, True): return -1 if self._check_dimensions(last_row, last_col, True, True): return -1 if options is None: options = {} else: # Copy the user defined options so they aren't modified. options = options.copy() # Valid input parameters. valid_parameters = { "validate", "criteria", "value", "source", "minimum", "maximum", "ignore_blank", "dropdown", "show_input", "input_title", "input_message", "show_error", "error_title", "error_message", "error_type", "other_cells", "multi_range", } # Check for valid input parameters. for param_key in options.keys(): if param_key not in valid_parameters: warn(f"Unknown parameter '{param_key}' in data_validation()") return -2 # Map alternative parameter names 'source' or 'minimum' to 'value'. if "source" in options: options["value"] = options["source"] if "minimum" in options: options["value"] = options["minimum"] # 'validate' is a required parameter. if "validate" not in options: warn("Parameter 'validate' is required in data_validation()") return -2 # List of valid validation types. valid_types = { "any": "none", "any value": "none", "whole number": "whole", "whole": "whole", "integer": "whole", "decimal": "decimal", "list": "list", "date": "date", "time": "time", "text length": "textLength", "length": "textLength", "custom": "custom", } # Check for valid validation types. if options["validate"] not in valid_types: warn( f"Unknown validation type '{options['validate']}' for parameter " f"'validate' in data_validation()" ) return -2 options["validate"] = valid_types[options["validate"]] # No action is required for validation type 'any' if there are no # input messages to display. if ( options["validate"] == "none" and options.get("input_title") is None and options.get("input_message") is None ): return -2 # The any, list and custom validations don't have a criteria so we use # a default of 'between'. if ( options["validate"] == "none" or options["validate"] == "list" or options["validate"] == "custom" ): options["criteria"] = "between" options["maximum"] = None # 'criteria' is a required parameter. if "criteria" not in options: warn("Parameter 'criteria' is required in data_validation()") return -2 # Valid criteria types. criteria_types = { "between": "between", "not between": "notBetween", "equal to": "equal", "=": "equal", "==": "equal", "not equal to": "notEqual", "!=": "notEqual", "<>": "notEqual", "greater than": "greaterThan", ">": "greaterThan", "less than": "lessThan", "<": "lessThan", "greater than or equal to": "greaterThanOrEqual", ">=": "greaterThanOrEqual", "less than or equal to": "lessThanOrEqual", "<=": "lessThanOrEqual", } # Check for valid criteria types. if options["criteria"] not in criteria_types: warn( f"Unknown criteria type '{options['criteria']}' for parameter " f"'criteria' in data_validation()" ) return -2 options["criteria"] = criteria_types[options["criteria"]] # 'Between' and 'Not between' criteria require 2 values. if options["criteria"] == "between" or options["criteria"] == "notBetween": if "maximum" not in options: warn( "Parameter 'maximum' is required in data_validation() " "when using 'between' or 'not between' criteria" ) return -2 else: options["maximum"] = None # Valid error dialog types. error_types = { "stop": 0, "warning": 1, "information": 2, } # Check for valid error dialog types. if "error_type" not in options: options["error_type"] = 0 elif options["error_type"] not in error_types: warn( f"Unknown criteria type '{options['error_type']}' " f"for parameter 'error_type'." ) return -2 else: options["error_type"] = error_types[options["error_type"]] # Convert date/times value if required. if ( options["validate"] in ("date", "time") and options["value"] and _supported_datetime(options["value"]) ): date_time = self._convert_date_time(options["value"]) # Format date number to the same precision as Excel. options["value"] = f"{date_time:.16g}" if options["maximum"] and _supported_datetime(options["maximum"]): date_time = self._convert_date_time(options["maximum"]) options["maximum"] = f"{date_time:.16g}" # Check that the input title doesn't exceed the maximum length. if options.get("input_title") and len(options["input_title"]) > 32: warn( f"Length of input title '{options['input_title']}' " f"exceeds Excel's limit of 32" ) return -2 # Check that the error title doesn't exceed the maximum length. if options.get("error_title") and len(options["error_title"]) > 32: warn( f"Length of error title '{options['error_title']}' " f"exceeds Excel's limit of 32" ) return -2 # Check that the input message doesn't exceed the maximum length. if options.get("input_message") and len(options["input_message"]) > 255: warn( f"Length of input message '{options['input_message']}' " f"exceeds Excel's limit of 255" ) return -2 # Check that the error message doesn't exceed the maximum length. if options.get("error_message") and len(options["error_message"]) > 255: warn( f"Length of error message '{options['error_message']}' " f"exceeds Excel's limit of 255" ) return -2 # Check that the input list doesn't exceed the maximum length. if options["validate"] == "list" and isinstance(options["value"], list): formula = self._csv_join(*options["value"]) if len(formula) > 255: warn( f"Length of list items '{formula}' exceeds Excel's limit of " f"255, use a formula range instead" ) return -2 # Set some defaults if they haven't been defined by the user. if "ignore_blank" not in options: options["ignore_blank"] = 1 if "dropdown" not in options: options["dropdown"] = 1 if "show_input" not in options: options["show_input"] = 1 if "show_error" not in options: options["show_error"] = 1 # These are the cells to which the validation is applied. options["cells"] = [[first_row, first_col, last_row, last_col]] # A (for now) undocumented parameter to pass additional cell ranges. if "other_cells" in options: options["cells"].extend(options["other_cells"]) # Override with user defined multiple range if provided. if "multi_range" in options: options["multi_range"] = options["multi_range"].replace("$", "") # Store the validation information until we close the worksheet. self.validations.append(options) return 0 @convert_range_args def conditional_format( self, first_row, first_col, last_row, last_col, options=None ): """ Add a conditional format to a worksheet. Args: first_row: The first row of the cell range. (zero indexed). first_col: The first column of the cell range. last_row: The last row of the cell range. (zero indexed). last_col: The last column of the cell range. options: Conditional format options. Returns: 0: Success. -1: Row or column is out of worksheet bounds. -2: Incorrect parameter or option. """ # Check that row and col are valid without storing the values. if self._check_dimensions(first_row, first_col, True, True): return -1 if self._check_dimensions(last_row, last_col, True, True): return -1 if options is None: options = {} else: # Copy the user defined options so they aren't modified. options = options.copy() # Valid input parameters. valid_parameter = { "type", "format", "criteria", "value", "minimum", "maximum", "stop_if_true", "min_type", "mid_type", "max_type", "min_value", "mid_value", "max_value", "min_color", "mid_color", "max_color", "min_length", "max_length", "multi_range", "bar_color", "bar_negative_color", "bar_negative_color_same", "bar_solid", "bar_border_color", "bar_negative_border_color", "bar_negative_border_color_same", "bar_no_border", "bar_direction", "bar_axis_position", "bar_axis_color", "bar_only", "data_bar_2010", "icon_style", "reverse_icons", "icons_only", "icons", } # Check for valid input parameters. for param_key in options.keys(): if param_key not in valid_parameter: warn(f"Unknown parameter '{param_key}' in conditional_format()") return -2 # 'type' is a required parameter. if "type" not in options: warn("Parameter 'type' is required in conditional_format()") return -2 # Valid types. valid_type = { "cell": "cellIs", "date": "date", "time": "time", "average": "aboveAverage", "duplicate": "duplicateValues", "unique": "uniqueValues", "top": "top10", "bottom": "top10", "text": "text", "time_period": "timePeriod", "blanks": "containsBlanks", "no_blanks": "notContainsBlanks", "errors": "containsErrors", "no_errors": "notContainsErrors", "2_color_scale": "2_color_scale", "3_color_scale": "3_color_scale", "data_bar": "dataBar", "formula": "expression", "icon_set": "iconSet", } # Check for valid types. if options["type"] not in valid_type: warn( f"Unknown value '{options['type']}' for parameter 'type' " f"in conditional_format()" ) return -2 if options["type"] == "bottom": options["direction"] = "bottom" options["type"] = valid_type[options["type"]] # Valid criteria types. criteria_type = { "between": "between", "not between": "notBetween", "equal to": "equal", "=": "equal", "==": "equal", "not equal to": "notEqual", "!=": "notEqual", "<>": "notEqual", "greater than": "greaterThan", ">": "greaterThan", "less than": "lessThan", "<": "lessThan", "greater than or equal to": "greaterThanOrEqual", ">=": "greaterThanOrEqual", "less than or equal to": "lessThanOrEqual", "<=": "lessThanOrEqual", "containing": "containsText", "not containing": "notContains", "begins with": "beginsWith", "ends with": "endsWith", "yesterday": "yesterday", "today": "today", "last 7 days": "last7Days", "last week": "lastWeek", "this week": "thisWeek", "next week": "nextWeek", "last month": "lastMonth", "this month": "thisMonth", "next month": "nextMonth", # For legacy, but incorrect, support. "continue week": "nextWeek", "continue month": "nextMonth", } # Check for valid criteria types. if "criteria" in options and options["criteria"] in criteria_type: options["criteria"] = criteria_type[options["criteria"]] # Convert boolean values if required. if "value" in options and isinstance(options["value"], bool): options["value"] = str(options["value"]).upper() # Convert date/times value if required. if options["type"] in ("date", "time"): options["type"] = "cellIs" if "value" in options: if not _supported_datetime(options["value"]): warn("Conditional format 'value' must be a datetime object.") return -2 date_time = self._convert_date_time(options["value"]) # Format date number to the same precision as Excel. options["value"] = f"{date_time:.16g}" if "minimum" in options: if not _supported_datetime(options["minimum"]): warn("Conditional format 'minimum' must be a datetime object.") return -2 date_time = self._convert_date_time(options["minimum"]) options["minimum"] = f"{date_time:.16g}" if "maximum" in options: if not _supported_datetime(options["maximum"]): warn("Conditional format 'maximum' must be a datetime object.") return -2 date_time = self._convert_date_time(options["maximum"]) options["maximum"] = f"{date_time:.16g}" # Valid icon styles. valid_icons = { "3_arrows": "3Arrows", # 1 "3_flags": "3Flags", # 2 "3_traffic_lights_rimmed": "3TrafficLights2", # 3 "3_symbols_circled": "3Symbols", # 4 "4_arrows": "4Arrows", # 5 "4_red_to_black": "4RedToBlack", # 6 "4_traffic_lights": "4TrafficLights", # 7 "5_arrows_gray": "5ArrowsGray", # 8 "5_quarters": "5Quarters", # 9 "3_arrows_gray": "3ArrowsGray", # 10 "3_traffic_lights": "3TrafficLights", # 11 "3_signs": "3Signs", # 12 "3_symbols": "3Symbols2", # 13 "4_arrows_gray": "4ArrowsGray", # 14 "4_ratings": "4Rating", # 15 "5_arrows": "5Arrows", # 16 "5_ratings": "5Rating", } # 17 # Set the icon set properties. if options["type"] == "iconSet": # An icon_set must have an icon style. if not options.get("icon_style"): warn( "The 'icon_style' parameter must be specified when " "'type' == 'icon_set' in conditional_format()." ) return -3 # Check for valid icon styles. if options["icon_style"] not in valid_icons: warn( f"Unknown icon_style '{options['icon_style']}' " f"in conditional_format()." ) return -2 options["icon_style"] = valid_icons[options["icon_style"]] # Set the number of icons for the icon style. options["total_icons"] = 3 if options["icon_style"].startswith("4"): options["total_icons"] = 4 elif options["icon_style"].startswith("5"): options["total_icons"] = 5 options["icons"] = self._set_icon_props( options.get("total_icons"), options.get("icons") ) # Swap last row/col for first row/col as necessary if first_row > last_row: first_row, last_row = last_row, first_row if first_col > last_col: first_col, last_col = last_col, first_col # Set the formatting range. cell_range = xl_range(first_row, first_col, last_row, last_col) start_cell = xl_rowcol_to_cell(first_row, first_col) # Override with user defined multiple range if provided. if "multi_range" in options: cell_range = options["multi_range"] cell_range = cell_range.replace("$", "") # Get the dxf format index. if "format" in options and options["format"]: options["format"] = options["format"]._get_dxf_index() # Set the priority based on the order of adding. options["priority"] = self.dxf_priority self.dxf_priority += 1 # Check for 2010 style data_bar parameters. # pylint: disable=too-many-boolean-expressions if ( self.use_data_bars_2010 or options.get("data_bar_2010") or options.get("bar_solid") or options.get("bar_border_color") or options.get("bar_negative_color") or options.get("bar_negative_color_same") or options.get("bar_negative_border_color") or options.get("bar_negative_border_color_same") or options.get("bar_no_border") or options.get("bar_axis_position") or options.get("bar_axis_color") or options.get("bar_direction") ): options["is_data_bar_2010"] = True # Special handling of text criteria. if options["type"] == "text": value = options["value"] length = len(value) criteria = options["criteria"] if options["criteria"] == "containsText": options["type"] = "containsText" options["formula"] = f'NOT(ISERROR(SEARCH("{value}",{start_cell})))' elif options["criteria"] == "notContains": options["type"] = "notContainsText" options["formula"] = f'ISERROR(SEARCH("{value}",{start_cell}))' elif options["criteria"] == "beginsWith": options["type"] = "beginsWith" options["formula"] = f'LEFT({start_cell},{length})="{value}"' elif options["criteria"] == "endsWith": options["type"] = "endsWith" options["formula"] = f'RIGHT({start_cell},{length})="{value}"' else: warn(f"Invalid text criteria '{criteria}' in conditional_format()") # Special handling of time time_period criteria. if options["type"] == "timePeriod": if options["criteria"] == "yesterday": options["formula"] = f"FLOOR({start_cell},1)=TODAY()-1" elif options["criteria"] == "today": options["formula"] = f"FLOOR({start_cell},1)=TODAY()" elif options["criteria"] == "tomorrow": options["formula"] = f"FLOOR({start_cell},1)=TODAY()+1" # fmt: off elif options["criteria"] == "last7Days": options["formula"] = ( f"AND(TODAY()-FLOOR({start_cell},1)<=6," f"FLOOR({start_cell},1)<=TODAY())" ) # fmt: on elif options["criteria"] == "lastWeek": options["formula"] = ( f"AND(TODAY()-ROUNDDOWN({start_cell},0)>=(WEEKDAY(TODAY()))," f"TODAY()-ROUNDDOWN({start_cell},0)<(WEEKDAY(TODAY())+7))" ) elif options["criteria"] == "thisWeek": options["formula"] = ( f"AND(TODAY()-ROUNDDOWN({start_cell},0)<=WEEKDAY(TODAY())-1," f"ROUNDDOWN({start_cell},0)-TODAY()<=7-WEEKDAY(TODAY()))" ) elif options["criteria"] == "nextWeek": options["formula"] = ( f"AND(ROUNDDOWN({start_cell},0)-TODAY()>(7-WEEKDAY(TODAY()))," f"ROUNDDOWN({start_cell},0)-TODAY()<(15-WEEKDAY(TODAY())))" ) elif options["criteria"] == "lastMonth": options["formula"] = ( f"AND(MONTH({start_cell})=MONTH(TODAY())-1," f"OR(YEAR({start_cell})=YEAR(" f"TODAY()),AND(MONTH({start_cell})=1,YEAR(A1)=YEAR(TODAY())-1)))" ) # fmt: off elif options["criteria"] == "thisMonth": options["formula"] = ( f"AND(MONTH({start_cell})=MONTH(TODAY())," f"YEAR({start_cell})=YEAR(TODAY()))" ) # fmt: on elif options["criteria"] == "nextMonth": options["formula"] = ( f"AND(MONTH({start_cell})=MONTH(TODAY())+1," f"OR(YEAR({start_cell})=YEAR(" f"TODAY()),AND(MONTH({start_cell})=12," f"YEAR({start_cell})=YEAR(TODAY())+1)))" ) else: warn( f"Invalid time_period criteria '{options['criteria']}' " f"in conditional_format()" ) # Special handling of blanks/error types. if options["type"] == "containsBlanks": options["formula"] = f"LEN(TRIM({start_cell}))=0" if options["type"] == "notContainsBlanks": options["formula"] = f"LEN(TRIM({start_cell}))>0" if options["type"] == "containsErrors": options["formula"] = f"ISERROR({start_cell})" if options["type"] == "notContainsErrors": options["formula"] = f"NOT(ISERROR({start_cell}))" # Special handling for 2 color scale. if options["type"] == "2_color_scale": options["type"] = "colorScale" # Color scales don't use any additional formatting. options["format"] = None # Turn off 3 color parameters. options["mid_type"] = None options["mid_color"] = None options.setdefault("min_type", "min") options.setdefault("max_type", "max") options.setdefault("min_value", 0) options.setdefault("max_value", 0) options.setdefault("min_color", "#FF7128") options.setdefault("max_color", "#FFEF9C") options["min_color"] = _xl_color(options["min_color"]) options["max_color"] = _xl_color(options["max_color"]) # Special handling for 3 color scale. if options["type"] == "3_color_scale": options["type"] = "colorScale" # Color scales don't use any additional formatting. options["format"] = None options.setdefault("min_type", "min") options.setdefault("mid_type", "percentile") options.setdefault("max_type", "max") options.setdefault("min_value", 0) options.setdefault("max_value", 0) options.setdefault("min_color", "#F8696B") options.setdefault("mid_color", "#FFEB84") options.setdefault("max_color", "#63BE7B") options["min_color"] = _xl_color(options["min_color"]) options["mid_color"] = _xl_color(options["mid_color"]) options["max_color"] = _xl_color(options["max_color"]) # Set a default mid value. if "mid_value" not in options: options["mid_value"] = 50 # Special handling for data bar. if options["type"] == "dataBar": # Color scales don't use any additional formatting. options["format"] = None if not options.get("min_type"): options["min_type"] = "min" options["x14_min_type"] = "autoMin" else: options["x14_min_type"] = options["min_type"] if not options.get("max_type"): options["max_type"] = "max" options["x14_max_type"] = "autoMax" else: options["x14_max_type"] = options["max_type"] options.setdefault("min_value", 0) options.setdefault("max_value", 0) options.setdefault("bar_color", "#638EC6") options.setdefault("bar_border_color", options["bar_color"]) options.setdefault("bar_only", False) options.setdefault("bar_no_border", False) options.setdefault("bar_solid", False) options.setdefault("bar_direction", "") options.setdefault("bar_negative_color", "#FF0000") options.setdefault("bar_negative_border_color", "#FF0000") options.setdefault("bar_negative_color_same", False) options.setdefault("bar_negative_border_color_same", False) options.setdefault("bar_axis_position", "") options.setdefault("bar_axis_color", "#000000") options["bar_color"] = _xl_color(options["bar_color"]) options["bar_border_color"] = _xl_color(options["bar_border_color"]) options["bar_axis_color"] = _xl_color(options["bar_axis_color"]) options["bar_negative_color"] = _xl_color(options["bar_negative_color"]) options["bar_negative_border_color"] = _xl_color( options["bar_negative_border_color"] ) # Adjust for 2010 style data_bar parameters. if options.get("is_data_bar_2010"): self.excel_version = 2010 if options["min_type"] == "min" and options["min_value"] == 0: options["min_value"] = None if options["max_type"] == "max" and options["max_value"] == 0: options["max_value"] = None options["range"] = cell_range # Strip the leading = from formulas. try: options["min_value"] = options["min_value"].lstrip("=") except (KeyError, AttributeError): pass try: options["mid_value"] = options["mid_value"].lstrip("=") except (KeyError, AttributeError): pass try: options["max_value"] = options["max_value"].lstrip("=") except (KeyError, AttributeError): pass # Store the conditional format until we close the worksheet. if cell_range in self.cond_formats: self.cond_formats[cell_range].append(options) else: self.cond_formats[cell_range] = [options] return 0 @convert_range_args def add_table(self, first_row, first_col, last_row, last_col, options=None): """ Add an Excel table to a worksheet. Args: first_row: The first row of the cell range. (zero indexed). first_col: The first column of the cell range. last_row: The last row of the cell range. (zero indexed). last_col: The last column of the cell range. options: Table format options. (Optional) Returns: 0: Success. -1: Row or column is out of worksheet bounds. -2: Incorrect parameter or option. -3: Not supported in constant_memory mode. """ table = {} col_formats = {} if options is None: options = {} else: # Copy the user defined options so they aren't modified. options = options.copy() if self.constant_memory: warn("add_table() isn't supported in 'constant_memory' mode") return -3 # Check that row and col are valid without storing the values. if self._check_dimensions(first_row, first_col, True, True): return -1 if self._check_dimensions(last_row, last_col, True, True): return -1 # Swap last row/col for first row/col as necessary. if first_row > last_row: (first_row, last_row) = (last_row, first_row) if first_col > last_col: (first_col, last_col) = (last_col, first_col) # Check if the table range overlaps a previous merged or table range. # This is a critical file corruption error in Excel. cell_range = xl_range(first_row, first_col, last_row, last_col) for row in range(first_row, last_row + 1): for col in range(first_col, last_col + 1): if self.table_cells.get((row, col)): previous_range = self.table_cells.get((row, col)) raise OverlappingRange( f"Table range '{cell_range}' overlaps previous " f"table range '{previous_range}'." ) if self.merged_cells.get((row, col)): previous_range = self.merged_cells.get((row, col)) raise OverlappingRange( f"Table range '{cell_range}' overlaps previous " f"merge range '{previous_range}'." ) self.table_cells[(row, col)] = cell_range # Valid input parameters. valid_parameter = { "autofilter", "banded_columns", "banded_rows", "columns", "data", "first_column", "header_row", "last_column", "name", "style", "total_row", } # Check for valid input parameters. for param_key in options.keys(): if param_key not in valid_parameter: warn(f"Unknown parameter '{param_key}' in add_table()") return -2 # Turn on Excel's defaults. options["banded_rows"] = options.get("banded_rows", True) options["header_row"] = options.get("header_row", True) options["autofilter"] = options.get("autofilter", True) # Check that there are enough rows. num_rows = last_row - first_row if options["header_row"]: num_rows -= 1 if num_rows < 0: warn("Must have at least one data row in in add_table()") return -2 # Set the table options. table["show_first_col"] = options.get("first_column", False) table["show_last_col"] = options.get("last_column", False) table["show_row_stripes"] = options.get("banded_rows", False) table["show_col_stripes"] = options.get("banded_columns", False) table["header_row_count"] = options.get("header_row", 0) table["totals_row_shown"] = options.get("total_row", False) # Set the table name. if "name" in options: name = options["name"] table["name"] = name if " " in name: warn(f"Name '{name}' in add_table() cannot contain spaces") return -2 # Warn if the name contains invalid chars as defined by Excel. if not re.match(r"^[\w\\][\w\\.]*$", name, re.UNICODE) or re.match( r"^\d", name ): warn(f"Invalid Excel characters in add_table(): '{name}'") return -2 # Warn if the name looks like a cell name. if re.match(r"^[a-zA-Z][a-zA-Z]?[a-dA-D]?\d+$", name): warn(f"Name looks like a cell name in add_table(): '{name}'") return -2 # Warn if the name looks like a R1C1 cell reference. if re.match(r"^[rcRC]$", name) or re.match(r"^[rcRC]\d+[rcRC]\d+$", name): warn(f"Invalid name '{name}' like a RC cell ref in add_table()") return -2 # Set the table style. if "style" in options: table["style"] = options["style"] if table["style"] is None: table["style"] = "" # Remove whitespace from style name. table["style"] = table["style"].replace(" ", "") else: table["style"] = "TableStyleMedium9" # Set the data range rows (without the header and footer). first_data_row = first_row last_data_row = last_row if options.get("header_row"): first_data_row += 1 if options.get("total_row"): last_data_row -= 1 # Set the table and autofilter ranges. table["range"] = xl_range(first_row, first_col, last_row, last_col) table["a_range"] = xl_range(first_row, first_col, last_data_row, last_col) # If the header row if off the default is to turn autofilter off. if not options["header_row"]: options["autofilter"] = 0 # Set the autofilter range. if options["autofilter"]: table["autofilter"] = table["a_range"] # Add the table columns. col_id = 1 table["columns"] = [] seen_names = {} for col_num in range(first_col, last_col + 1): # Set up the default column data. col_data = { "id": col_id, "name": "Column" + str(col_id), "total_string": "", "total_function": "", "custom_total": "", "total_value": 0, "formula": "", "format": None, "name_format": None, } # Overwrite the defaults with any user defined values. if "columns" in options: # Check if there are user defined values for this column. if col_id <= len(options["columns"]): user_data = options["columns"][col_id - 1] else: user_data = None if user_data: # Get the column format. xformat = user_data.get("format", None) # Map user defined values to internal values. if user_data.get("header"): col_data["name"] = user_data["header"] # Excel requires unique case insensitive header names. header_name = col_data["name"] name = header_name.lower() if name in seen_names: warn(f"Duplicate header name in add_table(): '{name}'") return -2 seen_names[name] = True col_data["name_format"] = user_data.get("header_format") # Handle the column formula. if "formula" in user_data and user_data["formula"]: formula = user_data["formula"] # Remove the formula '=' sign if it exists. if formula.startswith("="): formula = formula.lstrip("=") # Convert Excel 2010 "@" ref to 2007 "#This Row". formula = self._prepare_table_formula(formula) # Escape any future functions. formula = self._prepare_formula(formula, True) col_data["formula"] = formula # We write the formulas below after the table data. # Handle the function for the total row. if user_data.get("total_function"): function = user_data["total_function"] if function == "count_nums": function = "countNums" if function == "std_dev": function = "stdDev" subtotals = set( [ "average", "countNums", "count", "max", "min", "stdDev", "sum", "var", ] ) if function in subtotals: formula = self._table_function_to_formula( function, col_data["name"] ) else: formula = self._prepare_formula(function, True) col_data["custom_total"] = formula function = "custom" col_data["total_function"] = function value = user_data.get("total_value", 0) self._write_formula(last_row, col_num, formula, xformat, value) elif user_data.get("total_string"): # Total label only (not a function). total_string = user_data["total_string"] col_data["total_string"] = total_string self._write_string( last_row, col_num, total_string, user_data.get("format") ) # Get the dxf format index. if xformat is not None: col_data["format"] = xformat._get_dxf_index() # Store the column format for writing the cell data. # It doesn't matter if it is undefined. col_formats[col_id - 1] = xformat # Store the column data. table["columns"].append(col_data) # Write the column headers to the worksheet. if options["header_row"]: self._write_string( first_row, col_num, col_data["name"], col_data["name_format"] ) col_id += 1 # Write the cell data if supplied. if "data" in options: data = options["data"] i = 0 # For indexing the row data. for row in range(first_data_row, last_data_row + 1): j = 0 # For indexing the col data. for col in range(first_col, last_col + 1): if i < len(data) and j < len(data[i]): token = data[i][j] if j in col_formats: self._write(row, col, token, col_formats[j]) else: self._write(row, col, token, None) j += 1 i += 1 # Write any columns formulas after the user supplied table data to # overwrite it if required. for col_id, col_num in enumerate(range(first_col, last_col + 1)): column_data = table["columns"][col_id] if column_data and column_data["formula"]: formula_format = col_formats.get(col_id) formula = column_data["formula"] for row in range(first_data_row, last_data_row + 1): self._write_formula(row, col_num, formula, formula_format) # Store the table data. self.tables.append(table) # Store the filter cell positions for use in the autofit calculation. if options["autofilter"]: for col in range(first_col, last_col + 1): # Check that the table autofilter doesn't overlap a worksheet filter. if self.filter_cells.get((first_row, col)): filter_type, filter_range = self.filter_cells.get((first_row, col)) if filter_type == "worksheet": raise OverlappingRange( f"Table autofilter range '{cell_range}' overlaps previous " f"Worksheet autofilter range '{filter_range}'." ) self.filter_cells[(first_row, col)] = ("table", cell_range) return 0 @convert_cell_args def add_sparkline(self, row, col, options=None): """ Add sparklines to the worksheet. Args: row: The cell row (zero indexed). col: The cell column (zero indexed). options: Sparkline formatting options. Returns: 0: Success. -1: Row or column is out of worksheet bounds. -2: Incorrect parameter or option. """ # Check that row and col are valid without storing the values. if self._check_dimensions(row, col, True, True): return -1 sparkline = {"locations": [xl_rowcol_to_cell(row, col)]} if options is None: options = {} # Valid input parameters. valid_parameters = { "location", "range", "type", "high_point", "low_point", "negative_points", "first_point", "last_point", "markers", "style", "series_color", "negative_color", "markers_color", "first_color", "last_color", "high_color", "low_color", "max", "min", "axis", "reverse", "empty_cells", "show_hidden", "plot_hidden", "date_axis", "weight", } # Check for valid input parameters. for param_key in options.keys(): if param_key not in valid_parameters: warn(f"Unknown parameter '{param_key}' in add_sparkline()") return -1 # 'range' is a required parameter. if "range" not in options: warn("Parameter 'range' is required in add_sparkline()") return -2 # Handle the sparkline type. spark_type = options.get("type", "line") if spark_type not in ("line", "column", "win_loss"): warn( "Parameter 'type' must be 'line', 'column' " "or 'win_loss' in add_sparkline()" ) return -2 if spark_type == "win_loss": spark_type = "stacked" sparkline["type"] = spark_type # We handle single location/range values or list of values. if "location" in options: if isinstance(options["location"], list): sparkline["locations"] = options["location"] else: sparkline["locations"] = [options["location"]] if isinstance(options["range"], list): sparkline["ranges"] = options["range"] else: sparkline["ranges"] = [options["range"]] range_count = len(sparkline["ranges"]) location_count = len(sparkline["locations"]) # The ranges and locations must match. if range_count != location_count: warn( "Must have the same number of location and range " "parameters in add_sparkline()" ) return -2 # Store the count. sparkline["count"] = len(sparkline["locations"]) # Get the worksheet name for the range conversion below. sheetname = quote_sheetname(self.name) # Cleanup the input ranges. new_ranges = [] for spark_range in sparkline["ranges"]: # Remove the absolute reference $ symbols. spark_range = spark_range.replace("$", "") # Remove the = from formula. spark_range = spark_range.lstrip("=") # Convert a simple range into a full Sheet1!A1:D1 range. if "!" not in spark_range: spark_range = sheetname + "!" + spark_range new_ranges.append(spark_range) sparkline["ranges"] = new_ranges # Cleanup the input locations. new_locations = [] for location in sparkline["locations"]: location = location.replace("$", "") new_locations.append(location) sparkline["locations"] = new_locations # Map options. sparkline["high"] = options.get("high_point") sparkline["low"] = options.get("low_point") sparkline["negative"] = options.get("negative_points") sparkline["first"] = options.get("first_point") sparkline["last"] = options.get("last_point") sparkline["markers"] = options.get("markers") sparkline["min"] = options.get("min") sparkline["max"] = options.get("max") sparkline["axis"] = options.get("axis") sparkline["reverse"] = options.get("reverse") sparkline["hidden"] = options.get("show_hidden") sparkline["weight"] = options.get("weight") # Map empty cells options. empty = options.get("empty_cells", "") if empty == "zero": sparkline["empty"] = 0 elif empty == "connect": sparkline["empty"] = "span" else: sparkline["empty"] = "gap" # Map the date axis range. date_range = options.get("date_axis") if date_range and "!" not in date_range: date_range = sheetname + "!" + date_range sparkline["date_axis"] = date_range # Set the sparkline styles. style_id = options.get("style", 0) style = _get_sparkline_style(style_id) sparkline["series_color"] = style["series"] sparkline["negative_color"] = style["negative"] sparkline["markers_color"] = style["markers"] sparkline["first_color"] = style["first"] sparkline["last_color"] = style["last"] sparkline["high_color"] = style["high"] sparkline["low_color"] = style["low"] # Override the style colors with user defined colors. self._set_spark_color(sparkline, options, "series_color") self._set_spark_color(sparkline, options, "negative_color") self._set_spark_color(sparkline, options, "markers_color") self._set_spark_color(sparkline, options, "first_color") self._set_spark_color(sparkline, options, "last_color") self._set_spark_color(sparkline, options, "high_color") self._set_spark_color(sparkline, options, "low_color") self.sparklines.append(sparkline) return 0 @convert_range_args def set_selection(self, first_row, first_col, last_row, last_col): """ Set the selected cell or cells in a worksheet Args: first_row: The first row of the cell range. (zero indexed). first_col: The first column of the cell range. last_row: The last row of the cell range. (zero indexed). last_col: The last column of the cell range. Returns: 0: Nothing. """ pane = None # Range selection. Do this before swapping max/min to allow the # selection direction to be reversed. active_cell = xl_rowcol_to_cell(first_row, first_col) # Swap last row/col for first row/col if necessary if first_row > last_row: (first_row, last_row) = (last_row, first_row) if first_col > last_col: (first_col, last_col) = (last_col, first_col) sqref = xl_range(first_row, first_col, last_row, last_col) # Selection isn't set for cell A1. if sqref == "A1": return self.selections = [[pane, active_cell, sqref]] @convert_cell_args def set_top_left_cell(self, row=0, col=0): """ Set the first visible cell at the top left of a worksheet. Args: row: The cell row (zero indexed). col: The cell column (zero indexed). Returns: 0: Nothing. """ if row == 0 and col == 0: return self.top_left_cell = xl_rowcol_to_cell(row, col) def outline_settings( self, visible=1, symbols_below=1, symbols_right=1, auto_style=0 ): """ Control outline settings. Args: visible: Outlines are visible. Optional, defaults to True. symbols_below: Show row outline symbols below the outline bar. Optional, defaults to True. symbols_right: Show column outline symbols to the right of the outline bar. Optional, defaults to True. auto_style: Use Automatic style. Optional, defaults to False. Returns: 0: Nothing. """ self.outline_on = visible self.outline_below = symbols_below self.outline_right = symbols_right self.outline_style = auto_style self.outline_changed = True @convert_cell_args def freeze_panes(self, row, col, top_row=None, left_col=None, pane_type=0): """ Create worksheet panes and mark them as frozen. Args: row: The cell row (zero indexed). col: The cell column (zero indexed). top_row: Topmost visible row in scrolling region of pane. left_col: Leftmost visible row in scrolling region of pane. Returns: 0: Nothing. """ if top_row is None: top_row = row if left_col is None: left_col = col self.panes = [row, col, top_row, left_col, pane_type] @convert_cell_args def split_panes(self, x, y, top_row=None, left_col=None): """ Create worksheet panes and mark them as split. Args: x: The position for the vertical split. y: The position for the horizontal split. top_row: Topmost visible row in scrolling region of pane. left_col: Leftmost visible row in scrolling region of pane. Returns: 0: Nothing. """ # Same as freeze panes with a different pane type. self.freeze_panes(x, y, top_row, left_col, 2) def set_zoom(self, zoom=100): """ Set the worksheet zoom factor. Args: zoom: Scale factor: 10 <= zoom <= 400. Returns: Nothing. """ # Ensure the zoom scale is in Excel's range. if zoom < 10 or zoom > 400: warn(f"Zoom factor '{zoom}' outside range: 10 <= zoom <= 400") zoom = 100 self.zoom = int(zoom) def right_to_left(self): """ Display the worksheet right to left for some versions of Excel. Args: None. Returns: Nothing. """ self.is_right_to_left = True def hide_zero(self): """ Hide zero values in worksheet cells. Args: None. Returns: Nothing. """ self.show_zeros = 0 def set_tab_color(self, color): """ Set the color of the worksheet tab. Args: color: A #RGB color index. Returns: Nothing. """ self.tab_color = _xl_color(color) def protect(self, password="", options=None): """ Set the password and protection options of the worksheet. Args: password: An optional password string. options: A dictionary of worksheet objects to protect. Returns: Nothing. """ if password != "": password = self._encode_password(password) if not options: options = {} # Default values for objects that can be protected. defaults = { "sheet": True, "content": False, "objects": False, "scenarios": False, "format_cells": False, "format_columns": False, "format_rows": False, "insert_columns": False, "insert_rows": False, "insert_hyperlinks": False, "delete_columns": False, "delete_rows": False, "select_locked_cells": True, "sort": False, "autofilter": False, "pivot_tables": False, "select_unlocked_cells": True, } # Overwrite the defaults with user specified values. for key in options.keys(): if key in defaults: defaults[key] = options[key] else: warn(f"Unknown protection object: '{key}'") # Set the password after the user defined values. defaults["password"] = password self.protect_options = defaults def unprotect_range(self, cell_range, range_name=None, password=None): """ Unprotect ranges within a protected worksheet. Args: cell_range: The cell or cell range to unprotect. range_name: An optional name for the range. password: An optional password string. (undocumented) Returns: 0: Success. -1: Parameter error. """ if cell_range is None: warn("Cell range must be specified in unprotect_range()") return -1 # Sanitize the cell range. cell_range = cell_range.lstrip("=") cell_range = cell_range.replace("$", "") self.num_protected_ranges += 1 if range_name is None: range_name = "Range" + str(self.num_protected_ranges) if password: password = self._encode_password(password) self.protected_ranges.append((cell_range, range_name, password)) return 0 @convert_cell_args def insert_button(self, row, col, options=None): """ Insert a button form object into the worksheet. Args: row: The cell row (zero indexed). col: The cell column (zero indexed). options: Button formatting options. Returns: 0: Success. -1: Row or column is out of worksheet bounds. """ # Check insert (row, col) without storing. if self._check_dimensions(row, col, True, True): warn(f"Cannot insert button at ({row}, {col}).") return -1 if options is None: options = {} # Create a new button object. height = self.default_row_pixels width = self.default_col_pixels button_number = 1 + len(self.buttons_list) button = ButtonType(row, col, height, width, button_number, options) self.buttons_list.append(button) self.has_vml = True return 0 @convert_cell_args def insert_checkbox(self, row, col, boolean, cell_format=None): """ Insert a boolean checkbox in a worksheet cell. Args: row: The cell row (zero indexed). col: The cell column (zero indexed). boolean: The boolean value to display as a checkbox. cell_format: Cell Format object. (optional) Returns: 0: Success. -1: Row or column is out of worksheet bounds. """ # Ensure that the checkbox property is set in the user defined format. if cell_format and not cell_format.checkbox: # This needs to be fixed with a clone. cell_format.set_checkbox() # If no format is supplied create and/or use the default checkbox format. if not cell_format: if not self.default_checkbox_format: self.default_checkbox_format = self.workbook_add_format() self.default_checkbox_format.set_checkbox() cell_format = self.default_checkbox_format return self._write_boolean(row, col, boolean, cell_format) ########################################################################### # # Public API. Page Setup methods. # ########################################################################### def set_landscape(self): """ Set the page orientation as landscape. Args: None. Returns: Nothing. """ self.orientation = 0 self.page_setup_changed = True def set_portrait(self): """ Set the page orientation as portrait. Args: None. Returns: Nothing. """ self.orientation = 1 self.page_setup_changed = True def set_page_view(self, view=1): """ Set the page view mode. Args: 0: Normal view mode 1: Page view mode (the default) 2: Page break view mode Returns: Nothing. """ self.page_view = view def set_pagebreak_view(self): """ Set the page view mode. Args: None. Returns: Nothing. """ self.page_view = 2 def set_paper(self, paper_size): """ Set the paper type. US Letter = 1, A4 = 9. Args: paper_size: Paper index. Returns: Nothing. """ if paper_size: self.paper_size = paper_size self.page_setup_changed = True def center_horizontally(self): """ Center the page horizontally. Args: None. Returns: Nothing. """ self.print_options_changed = True self.hcenter = 1 def center_vertically(self): """ Center the page vertically. Args: None. Returns: Nothing. """ self.print_options_changed = True self.vcenter = 1 def set_margins(self, left=0.7, right=0.7, top=0.75, bottom=0.75): """ Set all the page margins in inches. Args: left: Left margin. right: Right margin. top: Top margin. bottom: Bottom margin. Returns: Nothing. """ self.margin_left = left self.margin_right = right self.margin_top = top self.margin_bottom = bottom def set_header(self, header="", options=None, margin=None): """ Set the page header caption and optional margin. Args: header: Header string. margin: Header margin. options: Header options, mainly for images. Returns: Nothing. """ header_orig = header header = header.replace("&[Picture]", "&G") if len(header) > 255: warn("Header string cannot be longer than Excel's limit of 255 characters") return if options is not None: # For backward compatibility allow options to be the margin. if not isinstance(options, dict): options = {"margin": options} else: options = {} # Copy the user defined options so they aren't modified. options = options.copy() # For backward compatibility. if margin is not None: options["margin"] = margin # Reset the list in case the function is called more than once. self.header_images = [] if options.get("image_left"): options["image_data"] = options.get("image_data_left") image = self._image_from_source(options.get("image_left"), options) image._header_position = "LH" self.header_images.append(image) if options.get("image_center"): options["image_data"] = options.get("image_data_center") image = self._image_from_source(options.get("image_center"), options) image._header_position = "CH" self.header_images.append(image) if options.get("image_right"): options["image_data"] = options.get("image_data_right") image = self._image_from_source(options.get("image_right"), options) image._header_position = "RH" self.header_images.append(image) placeholder_count = header.count("&G") image_count = len(self.header_images) if placeholder_count != image_count: warn( f"Number of footer images '{image_count}' doesn't match placeholder " f"count '{placeholder_count}' in string: {header_orig}" ) self.header_images = [] return if "align_with_margins" in options: self.header_footer_aligns = options["align_with_margins"] if "scale_with_doc" in options: self.header_footer_scales = options["scale_with_doc"] self.header = header self.margin_header = options.get("margin", 0.3) self.header_footer_changed = True if image_count: self.has_header_vml = True def set_footer(self, footer="", options=None, margin=None): """ Set the page footer caption and optional margin. Args: footer: Footer string. margin: Footer margin. options: Footer options, mainly for images. Returns: Nothing. """ footer_orig = footer footer = footer.replace("&[Picture]", "&G") if len(footer) > 255: warn("Footer string cannot be longer than Excel's limit of 255 characters") return if options is not None: # For backward compatibility allow options to be the margin. if not isinstance(options, dict): options = {"margin": options} else: options = {} # Copy the user defined options so they aren't modified. options = options.copy() # For backward compatibility. if margin is not None: options["margin"] = margin # Reset the list in case the function is called more than once. self.footer_images = [] if options.get("image_left"): options["image_data"] = options.get("image_data_left") image = self._image_from_source(options.get("image_left"), options) image._header_position = "LF" self.footer_images.append(image) if options.get("image_center"): options["image_data"] = options.get("image_data_center") image = self._image_from_source(options.get("image_center"), options) image._header_position = "CF" self.footer_images.append(image) if options.get("image_right"): options["image_data"] = options.get("image_data_right") image = self._image_from_source(options.get("image_right"), options) image._header_position = "RF" self.footer_images.append(image) placeholder_count = footer.count("&G") image_count = len(self.footer_images) if placeholder_count != image_count: warn( f"Number of footer images '{image_count}' doesn't match placeholder " f"count '{placeholder_count}' in string: {footer_orig}" ) self.footer_images = [] return if "align_with_margins" in options: self.header_footer_aligns = options["align_with_margins"] if "scale_with_doc" in options: self.header_footer_scales = options["scale_with_doc"] self.footer = footer self.margin_footer = options.get("margin", 0.3) self.header_footer_changed = True if image_count: self.has_header_vml = True def repeat_rows(self, first_row, last_row=None): """ Set the rows to repeat at the top of each printed page. Args: first_row: Start row for range. last_row: End row for range. Returns: Nothing. """ if last_row is None: last_row = first_row # Convert rows to 1 based. first_row += 1 last_row += 1 # Create the row range area like: $1:$2. area = f"${first_row}:${last_row}" # Build up the print titles area "Sheet1!$1:$2" sheetname = quote_sheetname(self.name) self.repeat_row_range = sheetname + "!" + area @convert_column_args def repeat_columns(self, first_col, last_col=None): """ Set the columns to repeat at the left hand side of each printed page. Args: first_col: Start column for range. last_col: End column for range. Returns: Nothing. """ if last_col is None: last_col = first_col # Convert to A notation. first_col = xl_col_to_name(first_col, 1) last_col = xl_col_to_name(last_col, 1) # Create a column range like $C:$D. area = first_col + ":" + last_col # Build up the print area range "=Sheet2!$C:$D" sheetname = quote_sheetname(self.name) self.repeat_col_range = sheetname + "!" + area def hide_gridlines(self, option=1): """ Set the option to hide gridlines on the screen and the printed page. Args: option: 0 : Don't hide gridlines 1 : Hide printed gridlines only 2 : Hide screen and printed gridlines Returns: Nothing. """ if option == 0: self.print_gridlines = 1 self.screen_gridlines = 1 self.print_options_changed = True elif option == 1: self.print_gridlines = 0 self.screen_gridlines = 1 else: self.print_gridlines = 0 self.screen_gridlines = 0 def print_row_col_headers(self): """ Set the option to print the row and column headers on the printed page. Args: None. Returns: Nothing. """ self.print_headers = True self.print_options_changed = True def hide_row_col_headers(self): """ Set the option to hide the row and column headers on the worksheet. Args: None. Returns: Nothing. """ self.row_col_headers = True @convert_range_args def print_area(self, first_row, first_col, last_row, last_col): """ Set the print area in the current worksheet. Args: first_row: The first row of the cell range. (zero indexed). first_col: The first column of the cell range. last_row: The last row of the cell range. (zero indexed). last_col: The last column of the cell range. Returns: 0: Success. -1: Row or column is out of worksheet bounds. """ # Set the print area in the current worksheet. # Ignore max print area since it is the same as no area for Excel. if ( first_row == 0 and first_col == 0 and last_row == self.xls_rowmax - 1 and last_col == self.xls_colmax - 1 ): return -1 # Build up the print area range "Sheet1!$A$1:$C$13". area = self._convert_name_area(first_row, first_col, last_row, last_col) self.print_area_range = area return 0 def print_across(self): """ Set the order in which pages are printed. Args: None. Returns: Nothing. """ self.page_order = 1 self.page_setup_changed = True def fit_to_pages(self, width, height): """ Fit the printed area to a specific number of pages both vertically and horizontally. Args: width: Number of pages horizontally. height: Number of pages vertically. Returns: Nothing. """ self.fit_page = 1 self.fit_width = width self.fit_height = height self.page_setup_changed = True def set_start_page(self, start_page): """ Set the start page number when printing. Args: start_page: Start page number. Returns: Nothing. """ self.page_start = start_page def set_print_scale(self, scale): """ Set the scale factor for the printed page. Args: scale: Print scale. 10 <= scale <= 400. Returns: Nothing. """ # Confine the scale to Excel's range. if scale < 10 or scale > 400: warn(f"Print scale '{scale}' outside range: 10 <= scale <= 400") return # Turn off "fit to page" option when print scale is on. self.fit_page = 0 self.print_scale = int(scale) self.page_setup_changed = True def print_black_and_white(self): """ Set the option to print the worksheet in black and white. Args: None. Returns: Nothing. """ self.black_white = True self.page_setup_changed = True def set_h_pagebreaks(self, breaks): """ Set the horizontal page breaks on a worksheet. Args: breaks: List of rows where the page breaks should be added. Returns: Nothing. """ self.hbreaks = breaks def set_v_pagebreaks(self, breaks): """ Set the horizontal page breaks on a worksheet. Args: breaks: List of columns where the page breaks should be added. Returns: Nothing. """ self.vbreaks = breaks def set_vba_name(self, name=None): """ Set the VBA name for the worksheet. By default this is the same as the sheet name: i.e., Sheet1 etc. Args: name: The VBA name for the worksheet. Returns: Nothing. """ if name is not None: self.vba_codename = name else: self.vba_codename = "Sheet" + str(self.index + 1) def ignore_errors(self, options=None): """ Ignore various Excel errors/warnings in a worksheet for user defined ranges. Args: options: A dict of ignore errors keys with cell range values. Returns: 0: Success. -1: Incorrect parameter or option. """ if options is None: return -1 # Copy the user defined options so they aren't modified. options = options.copy() # Valid input parameters. valid_parameters = { "number_stored_as_text", "eval_error", "formula_differs", "formula_range", "formula_unlocked", "empty_cell_reference", "list_data_validation", "calculated_column", "two_digit_text_year", } # Check for valid input parameters. for param_key in options.keys(): if param_key not in valid_parameters: warn(f"Unknown parameter '{param_key}' in ignore_errors()") return -1 self.ignored_errors = options return 0 ########################################################################### # # Private API. # ########################################################################### def _initialize(self, init_data): self.name = init_data["name"] self.index = init_data["index"] self.str_table = init_data["str_table"] self.worksheet_meta = init_data["worksheet_meta"] self.constant_memory = init_data["constant_memory"] self.tmpdir = init_data["tmpdir"] self.date_1904 = init_data["date_1904"] self.strings_to_numbers = init_data["strings_to_numbers"] self.strings_to_formulas = init_data["strings_to_formulas"] self.strings_to_urls = init_data["strings_to_urls"] self.nan_inf_to_errors = init_data["nan_inf_to_errors"] self.default_date_format = init_data["default_date_format"] self.default_url_format = init_data["default_url_format"] self.workbook_add_format = init_data["workbook_add_format"] self.excel2003_style = init_data["excel2003_style"] self.remove_timezone = init_data["remove_timezone"] self.max_url_length = init_data["max_url_length"] self.use_future_functions = init_data["use_future_functions"] self.embedded_images = init_data["embedded_images"] if self.excel2003_style: self.original_row_height = 12.75 self.default_row_height = 12.75 self.default_row_pixels = 17 self.margin_left = 0.75 self.margin_right = 0.75 self.margin_top = 1 self.margin_bottom = 1 self.margin_header = 0.5 self.margin_footer = 0.5 self.header_footer_aligns = False # Open a temp filehandle to store row data in constant_memory mode. if self.constant_memory: # This is sub-optimal but we need to create a temp file # with utf8 encoding in Python < 3. (fd, filename) = tempfile.mkstemp(dir=self.tmpdir) os.close(fd) self.row_data_filename = filename # pylint: disable=consider-using-with self.row_data_fh = open(filename, mode="w+", encoding="utf-8") # Set as the worksheet filehandle until the file is assembled. self.fh = self.row_data_fh def _assemble_xml_file(self): # Assemble and write the XML file. # Write the XML declaration. self._xml_declaration() # Write the root worksheet element. self._write_worksheet() # Write the worksheet properties. self._write_sheet_pr() # Write the worksheet dimensions. self._write_dimension() # Write the sheet view properties. self._write_sheet_views() # Write the sheet format properties. self._write_sheet_format_pr() # Write the sheet column info. self._write_cols() # Write the worksheet data such as rows columns and cells. if not self.constant_memory: self._write_sheet_data() else: self._write_optimized_sheet_data() # Write the sheetProtection element. self._write_sheet_protection() # Write the protectedRanges element. self._write_protected_ranges() # Write the phoneticPr element. if self.excel2003_style: self._write_phonetic_pr() # Write the autoFilter element. self._write_auto_filter() # Write the mergeCells element. self._write_merge_cells() # Write the conditional formats. self._write_conditional_formats() # Write the dataValidations element. self._write_data_validations() # Write the hyperlink element. self._write_hyperlinks() # Write the printOptions element. self._write_print_options() # Write the worksheet page_margins. self._write_page_margins() # Write the worksheet page setup. self._write_page_setup() # Write the headerFooter element. self._write_header_footer() # Write the rowBreaks element. self._write_row_breaks() # Write the colBreaks element. self._write_col_breaks() # Write the ignoredErrors element. self._write_ignored_errors() # Write the drawing element. self._write_drawings() # Write the legacyDrawing element. self._write_legacy_drawing() # Write the legacyDrawingHF element. self._write_legacy_drawing_hf() # Write the picture element, for the background. self._write_picture() # Write the tableParts element. self._write_table_parts() # Write the extLst elements. self._write_ext_list() # Close the worksheet tag. self._xml_end_tag("worksheet") # Close the file. self._xml_close() def _check_dimensions(self, row, col, ignore_row=False, ignore_col=False): # Check that row and col are valid and store the max and min # values for use in other methods/elements. The ignore_row / # ignore_col flags is used to indicate that we wish to perform # the dimension check without storing the value. The ignore # flags are use by set_row() and data_validate. # Check that the row/col are within the worksheet bounds. if row < 0 or col < 0: return -1 if row >= self.xls_rowmax or col >= self.xls_colmax: return -1 # In constant_memory mode we don't change dimensions for rows # that are already written. if not ignore_row and not ignore_col and self.constant_memory: if row < self.previous_row: return -2 if not ignore_row: if self.dim_rowmin is None or row < self.dim_rowmin: self.dim_rowmin = row if self.dim_rowmax is None or row > self.dim_rowmax: self.dim_rowmax = row if not ignore_col: if self.dim_colmin is None or col < self.dim_colmin: self.dim_colmin = col if self.dim_colmax is None or col > self.dim_colmax: self.dim_colmax = col return 0 def _convert_date_time(self, dt_obj): # Convert a datetime object to an Excel serial date and time. return _datetime_to_excel_datetime(dt_obj, self.date_1904, self.remove_timezone) def _convert_name_area(self, row_num_1, col_num_1, row_num_2, col_num_2): # Convert zero indexed rows and columns to the format required by # worksheet named ranges, eg, "Sheet1!$A$1:$C$13". range1 = "" range2 = "" area = "" row_col_only = 0 # Convert to A1 notation. col_char_1 = xl_col_to_name(col_num_1, 1) col_char_2 = xl_col_to_name(col_num_2, 1) row_char_1 = "$" + str(row_num_1 + 1) row_char_2 = "$" + str(row_num_2 + 1) # We need to handle special cases that refer to rows or columns only. if row_num_1 == 0 and row_num_2 == self.xls_rowmax - 1: range1 = col_char_1 range2 = col_char_2 row_col_only = 1 elif col_num_1 == 0 and col_num_2 == self.xls_colmax - 1: range1 = row_char_1 range2 = row_char_2 row_col_only = 1 else: range1 = col_char_1 + row_char_1 range2 = col_char_2 + row_char_2 # A repeated range is only written once (if it isn't a special case). if range1 == range2 and not row_col_only: area = range1 else: area = range1 + ":" + range2 # Build up the print area range "Sheet1!$A$1:$C$13". sheetname = quote_sheetname(self.name) area = sheetname + "!" + area return area def _sort_pagebreaks(self, breaks): # This is an internal method used to filter elements of a list of # pagebreaks used in the _store_hbreak() and _store_vbreak() methods. # It: # 1. Removes duplicate entries from the list. # 2. Sorts the list. # 3. Removes 0 from the list if present. if not breaks: return [] breaks_set = set(breaks) if 0 in breaks_set: breaks_set.remove(0) breaks_list = list(breaks_set) breaks_list.sort() # The Excel 2007 specification says that the maximum number of page # breaks is 1026. However, in practice it is actually 1023. max_num_breaks = 1023 if len(breaks_list) > max_num_breaks: breaks_list = breaks_list[:max_num_breaks] return breaks_list def _extract_filter_tokens(self, expression): # Extract the tokens from the filter expression. The tokens are mainly # non-whitespace groups. The only tricky part is to extract string # tokens that contain whitespace and/or quoted double quotes (Excel's # escaped quotes). # # Examples: 'x < 2000' # 'x > 2000 and x < 5000' # 'x = "foo"' # 'x = "foo bar"' # 'x = "foo "" bar"' # if not expression: return [] token_re = re.compile(r'"(?:[^"]|"")*"|\S+') tokens = token_re.findall(expression) new_tokens = [] # Remove single leading and trailing quotes and un-escape other quotes. for token in tokens: if token.startswith('"'): token = token[1:] if token.endswith('"'): token = token[:-1] token = token.replace('""', '"') new_tokens.append(token) return new_tokens def _parse_filter_expression(self, expression, tokens): # Converts the tokens of a possibly conditional expression into 1 or 2 # sub expressions for further parsing. # # Examples: # ('x', '==', 2000) -> exp1 # ('x', '>', 2000, 'and', 'x', '<', 5000) -> exp1 and exp2 if len(tokens) == 7: # The number of tokens will be either 3 (for 1 expression) # or 7 (for 2 expressions). conditional = tokens[3] if re.match("(and|&&)", conditional): conditional = 0 elif re.match(r"(or|\|\|)", conditional): conditional = 1 else: warn( f"Token '{conditional}' is not a valid conditional " f"in filter expression '{expression}'" ) expression_1 = self._parse_filter_tokens(expression, tokens[0:3]) expression_2 = self._parse_filter_tokens(expression, tokens[4:7]) return expression_1 + [conditional] + expression_2 return self._parse_filter_tokens(expression, tokens) def _parse_filter_tokens(self, expression, tokens): # Parse the 3 tokens of a filter expression and return the operator # and token. The use of numbers instead of operators is a legacy of # Spreadsheet::WriteExcel. operators = { "==": 2, "=": 2, "=~": 2, "eq": 2, "!=": 5, "!~": 5, "ne": 5, "<>": 5, "<": 1, "<=": 3, ">": 4, ">=": 6, } operator = operators.get(tokens[1], None) token = tokens[2] # Special handling of "Top" filter expressions. if re.match("top|bottom", tokens[0].lower()): value = int(tokens[1]) if value < 1 or value > 500: warn( f"The value '{token}' in expression '{expression}' " f"must be in the range 1 to 500" ) token = token.lower() if token not in ("items", "%"): warn( f"The type '{token}' in expression '{expression}' " f"must be either 'items' or '%%'" ) if tokens[0].lower() == "top": operator = 30 else: operator = 32 if tokens[2] == "%": operator += 1 token = str(value) if not operator and tokens[0]: warn( f"Token '{token[0]}' is not a valid operator " f"in filter expression '{expression}'." ) # Special handling for Blanks/NonBlanks. if re.match("blanks|nonblanks", token.lower()): # Only allow Equals or NotEqual in this context. if operator not in (2, 5): warn( f"The operator '{tokens[1]}' in expression '{expression}' " f"is not valid in relation to Blanks/NonBlanks'." ) token = token.lower() # The operator should always be 2 (=) to flag a "simple" equality # in the binary record. Therefore we convert <> to =. if token == "blanks": if operator == 5: token = " " else: if operator == 5: operator = 2 token = "blanks" else: operator = 5 token = " " # if the string token contains an Excel match character then change the # operator type to indicate a non "simple" equality. if operator == 2 and re.search("[*?]", token): operator = 22 return [operator, token] def _encode_password(self, password): # Hash a worksheet password. Based on the algorithm in # ECMA-376-4:2016, Office Open XML File Formats — Transitional # Migration Features, Additional attributes for workbookProtection # element (Part 1, §18.2.29). digest = 0x0000 for char in password[::-1]: digest = ((digest >> 14) & 0x01) | ((digest << 1) & 0x7FFF) digest ^= ord(char) digest = ((digest >> 14) & 0x01) | ((digest << 1) & 0x7FFF) digest ^= len(password) digest ^= 0xCE4B return f"{digest:X}" def _image_from_source(self, source, options=None): # Backward compatibility utility method to convert an input argument to # an Image object. The source can be a filename, BytesIO stream or # an existing Image object. if isinstance(source, Image): image = source elif options is not None and options.get("image_data"): image = Image(options["image_data"]) image.image_name = source else: image = Image(source) return image def _prepare_image( self, image: Image, image_id: int, drawing_id: int, ): # Set up images/drawings. # Get the effective image width and height in pixels. width = image._width * image._x_scale height = image._height * image._y_scale # Scale by non 96dpi resolutions. width *= 96.0 / image._x_dpi height *= 96.0 / image._y_dpi dimensions = self._position_object_emus( image._col, image._row, image._x_offset, image._y_offset, width, height, image._anchor, ) # Convert from pixels to emus. width = int(0.5 + (width * 9525)) height = int(0.5 + (height * 9525)) # Create a Drawing obj to use with worksheet unless one already exists. if not self.drawing: drawing = Drawing() drawing.embedded = 1 self.drawing = drawing self.external_drawing_links.append( ["/drawing", "../drawings/drawing" + str(drawing_id) + ".xml", None] ) else: drawing = self.drawing drawing_object = DrawingInfo() drawing_object._drawing_type = DrawingTypes.IMAGE drawing_object._dimensions = dimensions drawing_object._description = image.image_name drawing_object._width = width drawing_object._height = height drawing_object._shape = None drawing_object._anchor = image._anchor drawing_object._rel_index = 0 drawing_object._decorative = image._decorative if image.description is not None: drawing_object._description = image.description if image._url: url = image._url target = url._target() target_mode = url._target_mode() if not self.drawing_rels.get(url._link): self.drawing_links.append(["/hyperlink", target, target_mode]) url._rel_index = self._get_drawing_rel_index(url._link) drawing_object._url = url if not self.drawing_rels.get(image._digest): self.drawing_links.append( [ "/image", "../media/image" + str(image_id) + "." + image._image_extension, ] ) drawing_object._rel_index = self._get_drawing_rel_index(image._digest) drawing._add_drawing_object(drawing_object) def _prepare_shape(self, index, drawing_id): # Set up shapes/drawings. ( row, col, x_offset, y_offset, x_scale, y_scale, text, anchor, options, description, decorative, ) = self.shapes[index] width = options.get("width", self.default_col_pixels * 3) height = options.get("height", self.default_row_pixels * 6) width *= x_scale height *= y_scale dimensions = self._position_object_emus( col, row, x_offset, y_offset, width, height, anchor ) # Convert from pixels to emus. width = int(0.5 + (width * 9525)) height = int(0.5 + (height * 9525)) # Create a Drawing obj to use with worksheet unless one already exists. if not self.drawing: drawing = Drawing() drawing.embedded = 1 self.drawing = drawing self.external_drawing_links.append( ["/drawing", "../drawings/drawing" + str(drawing_id) + ".xml", None] ) else: drawing = self.drawing shape = Shape("rect", "TextBox", options) shape.text = text drawing_object = DrawingInfo() drawing_object._drawing_type = DrawingTypes.SHAPE drawing_object._dimensions = dimensions drawing_object._width = width drawing_object._height = height drawing_object._description = description drawing_object._shape = shape drawing_object._anchor = anchor drawing_object._rel_index = 0 drawing_object._decorative = decorative url = Url.from_options(options) if url: target = url._target() target_mode = url._target_mode() if not self.drawing_rels.get(url._link): self.drawing_links.append(["/hyperlink", target, target_mode]) url._rel_index = self._get_drawing_rel_index(url._link) drawing_object._url = url drawing._add_drawing_object(drawing_object) def _prepare_header_image(self, image_id, image): # Set up an image without a drawing object for header/footer images. # Strip the extension from the filename. image.image_name = re.sub(r"\..*$", "", image.image_name) if not self.vml_drawing_rels.get(image._digest): self.vml_drawing_links.append( [ "/image", "../media/image" + str(image_id) + "." + image._image_extension, ] ) image._ref_id = self._get_vml_drawing_rel_index(image._digest) self.header_images_list.append(image) def _prepare_background(self, image_id, image_extension): # Set up an image without a drawing object for backgrounds. self.external_background_links.append( ["/image", "../media/image" + str(image_id) + "." + image_extension] ) def _prepare_chart(self, index, chart_id, drawing_id): # Set up chart/drawings. ( row, col, chart, x_offset, y_offset, x_scale, y_scale, anchor, description, decorative, ) = self.charts[index] chart.id = chart_id - 1 # Use user specified dimensions, if any. width = int(0.5 + (chart.width * x_scale)) height = int(0.5 + (chart.height * y_scale)) dimensions = self._position_object_emus( col, row, x_offset, y_offset, width, height, anchor ) # Set the chart name for the embedded object if it has been specified. name = chart.chart_name # Create a Drawing obj to use with worksheet unless one already exists. if not self.drawing: drawing = Drawing() drawing.embedded = 1 self.drawing = drawing self.external_drawing_links.append( ["/drawing", "../drawings/drawing" + str(drawing_id) + ".xml"] ) else: drawing = self.drawing drawing_object = DrawingInfo() drawing_object._drawing_type = DrawingTypes.CHART drawing_object._dimensions = dimensions drawing_object._width = width drawing_object._height = height drawing_object._name = name drawing_object._shape = None drawing_object._anchor = anchor drawing_object._rel_index = self._get_drawing_rel_index() drawing_object._description = description drawing_object._decorative = decorative drawing._add_drawing_object(drawing_object) self.drawing_links.append( ["/chart", "../charts/chart" + str(chart_id) + ".xml"] ) def _position_object_emus( self, col_start, row_start, x1, y1, width, height, anchor ): # Calculate the vertices that define the position of a graphical # object within the worksheet in EMUs. # # The vertices are expressed as English Metric Units (EMUs). There are # 12,700 EMUs per point. Therefore, 12,700 * 3 /4 = 9,525 EMUs per # pixel ( col_start, row_start, x1, y1, col_end, row_end, x2, y2, x_abs, y_abs, ) = self._position_object_pixels( col_start, row_start, x1, y1, width, height, anchor ) # Convert the pixel values to EMUs. See above. x1 = int(0.5 + 9525 * x1) y1 = int(0.5 + 9525 * y1) x2 = int(0.5 + 9525 * x2) y2 = int(0.5 + 9525 * y2) x_abs = int(0.5 + 9525 * x_abs) y_abs = int(0.5 + 9525 * y_abs) return (col_start, row_start, x1, y1, col_end, row_end, x2, y2, x_abs, y_abs) # Calculate the vertices that define the position of a graphical object # within the worksheet in pixels. # # +------------+------------+ # | A | B | # +-----+------------+------------+ # | |(x1,y1) | | # | 1 |(A1)._______|______ | # | | | | | # | | | | | # +-----+----| OBJECT |-----+ # | | | | | # | 2 | |______________. | # | | | (B2)| # | | | (x2,y2)| # +---- +------------+------------+ # # Example of an object that covers some of the area from cell A1 to B2. # # Based on the width and height of the object we need to calculate 8 vars: # # col_start, row_start, col_end, row_end, x1, y1, x2, y2. # # We also calculate the absolute x and y position of the top left vertex of # the object. This is required for images. # # The width and height of the cells that the object occupies can be # variable and have to be taken into account. # # The values of col_start and row_start are passed in from the calling # function. The values of col_end and row_end are calculated by # subtracting the width and height of the object from the width and # height of the underlying cells. # def _position_object_pixels( self, col_start, row_start, x1, y1, width, height, anchor ): # col_start # Col containing upper left corner of object. # x1 # Distance to left side of object. # # row_start # Row containing top left corner of object. # y1 # Distance to top of object. # # col_end # Col containing lower right corner of object. # x2 # Distance to right side of object. # # row_end # Row containing bottom right corner of object. # y2 # Distance to bottom of object. # # width # Width of object frame. # height # Height of object frame. # # x_abs # Absolute distance to left side of object. # y_abs # Absolute distance to top side of object. x_abs = 0 y_abs = 0 # Adjust start column for negative offsets. # pylint: disable=chained-comparison while x1 < 0 and col_start > 0: x1 += self._size_col(col_start - 1) col_start -= 1 # Adjust start row for negative offsets. while y1 < 0 and row_start > 0: y1 += self._size_row(row_start - 1) row_start -= 1 # Ensure that the image isn't shifted off the page at top left. x1 = max(0, x1) y1 = max(0, y1) # Calculate the absolute x offset of the top-left vertex. if self.col_size_changed: for col_id in range(col_start): x_abs += self._size_col(col_id) else: # Optimization for when the column widths haven't changed. x_abs += self.default_col_pixels * col_start x_abs += x1 # Calculate the absolute y offset of the top-left vertex. if self.row_size_changed: for row_id in range(row_start): y_abs += self._size_row(row_id) else: # Optimization for when the row heights haven't changed. y_abs += self.default_row_pixels * row_start y_abs += y1 # Adjust start column for offsets that are greater than the col width. while x1 >= self._size_col(col_start, anchor): x1 -= self._size_col(col_start) col_start += 1 # Adjust start row for offsets that are greater than the row height. while y1 >= self._size_row(row_start, anchor): y1 -= self._size_row(row_start) row_start += 1 # Initialize end cell to the same as the start cell. col_end = col_start row_end = row_start # Don't offset the image in the cell if the row/col is hidden. if self._size_col(col_start, anchor) > 0: width = width + x1 if self._size_row(row_start, anchor) > 0: height = height + y1 # Subtract the underlying cell widths to find end cell of the object. while width >= self._size_col(col_end, anchor): width -= self._size_col(col_end, anchor) col_end += 1 # Subtract the underlying cell heights to find end cell of the object. while height >= self._size_row(row_end, anchor): height -= self._size_row(row_end, anchor) row_end += 1 # The end vertices are whatever is left from the width and height. x2 = width y2 = height return [col_start, row_start, x1, y1, col_end, row_end, x2, y2, x_abs, y_abs] def _size_col(self, col, anchor=0): # Convert the width of a cell from character units to pixels. Excel # rounds the column width to the nearest pixel. If the width hasn't # been set by the user we use the default value. A hidden column is # treated as having a width of zero unless it has the special # "object_position" of 4 (size with cells). max_digit_width = 7 # For Calibri 11. padding = 5 pixels = 0 # Look up the cell value to see if it has been changed. if col in self.col_info: width = self.col_info[col][0] hidden = self.col_info[col][2] if width is None: width = self.default_col_width # Convert to pixels. if hidden and anchor != 4: pixels = 0 elif width < 1: pixels = int(width * (max_digit_width + padding) + 0.5) else: pixels = int(width * max_digit_width + 0.5) + padding else: pixels = self.default_col_pixels return pixels def _size_row(self, row, anchor=0): # Convert the height of a cell from character units to pixels. If the # height hasn't been set by the user we use the default value. A # hidden row is treated as having a height of zero unless it has the # special "object_position" of 4 (size with cells). pixels = 0 # Look up the cell value to see if it has been changed if row in self.row_sizes: height = self.row_sizes[row][0] hidden = self.row_sizes[row][1] if hidden and anchor != 4: pixels = 0 else: pixels = int(4.0 / 3.0 * height) else: pixels = int(4.0 / 3.0 * self.default_row_height) return pixels def _pixels_to_width(self, pixels): # Convert the width of a cell from pixels to character units. max_digit_width = 7.0 # For Calabri 11. padding = 5.0 if pixels <= 12: width = pixels / (max_digit_width + padding) else: width = (pixels - padding) / max_digit_width return width def _pixels_to_height(self, pixels): # Convert the height of a cell from pixels to character units. return 0.75 * pixels def _comment_vertices(self, comment: CommentType): # Calculate the positions of the comment object. anchor = 0 vertices = self._position_object_pixels( comment.start_col, comment.start_row, comment.x_offset, comment.y_offset, comment.width, comment.height, anchor, ) # Add the width and height for VML. vertices.append(comment.width) vertices.append(comment.height) return vertices def _button_vertices(self, button: ButtonType): # Calculate the positions of the button object. anchor = 0 vertices = self._position_object_pixels( button.col, button.row, button.x_offset, button.y_offset, button.width, button.height, anchor, ) # Add the width and height for VML. vertices.append(button.width) vertices.append(button.height) return vertices def _prepare_vml_objects( self, vml_data_id, vml_shape_id, vml_drawing_id, comment_id ): comments = [] # Sort the comments into row/column order for easier comparison # testing and set the external links for comments and buttons. row_nums = sorted(self.comments.keys()) for row in row_nums: col_nums = sorted(self.comments[row].keys()) for col in col_nums: comment = self.comments[row][col] comment.vertices = self._comment_vertices(comment) # Set comment visibility if required and not user defined. if comment.is_visible is None: comment.is_visible = self.comments_visible # Set comment author if not already user defined. if comment.author is None: comment.author = self.comments_author comments.append(comment) for button in self.buttons_list: button.vertices = self._button_vertices(button) self.external_vml_links.append( ["/vmlDrawing", "../drawings/vmlDrawing" + str(vml_drawing_id) + ".vml"] ) if self.has_comments: self.comments_list = comments self.external_comment_links.append( ["/comments", "../comments" + str(comment_id) + ".xml"] ) count = len(comments) start_data_id = vml_data_id # The VML o:idmap data id contains a comma separated range when there # is more than one 1024 block of comments, like this: data="1,2". for i in range(int(count / 1024)): data_id = start_data_id + i + 1 vml_data_id = f"{vml_data_id},{data_id}" self.vml_data_id = vml_data_id self.vml_shape_id = vml_shape_id return count def _prepare_header_vml_objects(self, vml_header_id, vml_drawing_id): # Set up external linkage for VML header/footer images. self.vml_header_id = vml_header_id self.external_vml_links.append( ["/vmlDrawing", "../drawings/vmlDrawing" + str(vml_drawing_id) + ".vml"] ) def _prepare_tables(self, table_id, seen): # Set the table ids for the worksheet tables. for table in self.tables: table["id"] = table_id if table.get("name") is None: # Set a default name. table["name"] = "Table" + str(table_id) # Check for duplicate table names. name = table["name"].lower() if name in seen: raise DuplicateTableName( f"Duplicate name '{table['name']}' used in worksheet.add_table()." ) seen[name] = True # Store the link used for the rels file. self.external_table_links.append( ["/table", "../tables/table" + str(table_id) + ".xml"] ) table_id += 1 def _table_function_to_formula(self, function, col_name): # Convert a table total function to a worksheet formula. formula = "" # Escape special characters, as required by Excel. col_name = col_name.replace("'", "''") col_name = col_name.replace("#", "'#") col_name = col_name.replace("]", "']") col_name = col_name.replace("[", "'[") subtotals = { "average": 101, "countNums": 102, "count": 103, "max": 104, "min": 105, "stdDev": 107, "sum": 109, "var": 110, } if function in subtotals: func_num = subtotals[function] formula = f"SUBTOTAL({func_num},[{col_name}])" else: warn(f"Unsupported function '{function}' in add_table()") return formula def _set_spark_color(self, sparkline, options, user_color): # Set the sparkline color. if user_color not in options: return sparkline[user_color] = {"rgb": _xl_color(options[user_color])} def _get_range_data(self, row_start, col_start, row_end, col_end): # Returns a range of data from the worksheet _table to be used in # chart cached data. Strings are returned as SST ids and decoded # in the workbook. Return None for data that doesn't exist since # Excel can chart series with data missing. if self.constant_memory: return () data = [] # Iterate through the table data. for row_num in range(row_start, row_end + 1): # Store None if row doesn't exist. if row_num not in self.table: data.append(None) continue for col_num in range(col_start, col_end + 1): if col_num in self.table[row_num]: cell = self.table[row_num][col_num] cell_type = cell.__class__.__name__ if cell_type in ("Number", "Datetime"): # Return a number with Excel's precision. data.append(f"{cell.number:.16g}") elif cell_type == "String": # Return a string from it's shared string index. index = cell.string string = self.str_table._get_shared_string(index) data.append(string) elif cell_type in ("Formula", "ArrayFormula"): # Return the formula value. value = cell.value if value is None: value = 0 data.append(value) elif cell_type == "Blank": # Return a empty cell. data.append("") else: # Store None if column doesn't exist. data.append(None) return data def _csv_join(self, *items): # Create a csv string for use with data validation formulas and lists. # Convert non string types to string. items = [str(item) if not isinstance(item, str) else item for item in items] return ",".join(items) def _escape_url(self, url): # Don't escape URL if it looks already escaped. if re.search("%[0-9a-fA-F]{2}", url): return url # Can't use url.quote() here because it doesn't match Excel. url = url.replace("%", "%25") url = url.replace('"', "%22") url = url.replace(" ", "%20") url = url.replace("<", "%3c") url = url.replace(">", "%3e") url = url.replace("[", "%5b") url = url.replace("]", "%5d") url = url.replace("^", "%5e") url = url.replace("`", "%60") url = url.replace("{", "%7b") url = url.replace("}", "%7d") return url def _get_drawing_rel_index(self, target=None): # Get the index used to address a drawing rel link. if target is None: self.drawing_rels_id += 1 return self.drawing_rels_id if self.drawing_rels.get(target): return self.drawing_rels[target] self.drawing_rels_id += 1 self.drawing_rels[target] = self.drawing_rels_id return self.drawing_rels_id def _get_vml_drawing_rel_index(self, target=None): # Get the index used to address a vml drawing rel link. if self.vml_drawing_rels.get(target): return self.vml_drawing_rels[target] self.vml_drawing_rels_id += 1 self.vml_drawing_rels[target] = self.vml_drawing_rels_id return self.vml_drawing_rels_id ########################################################################### # # The following font methods are, more or less, duplicated from the # Styles class. Not the cleanest version of reuse but works for now. # ########################################################################### def _write_font(self, xf_format): # Write the element. xml_writer = self.rstring xml_writer._xml_start_tag("rPr") # Handle the main font properties. if xf_format.bold: xml_writer._xml_empty_tag("b") if xf_format.italic: xml_writer._xml_empty_tag("i") if xf_format.font_strikeout: xml_writer._xml_empty_tag("strike") if xf_format.font_outline: xml_writer._xml_empty_tag("outline") if xf_format.font_shadow: xml_writer._xml_empty_tag("shadow") # Handle the underline variants. if xf_format.underline: self._write_underline(xf_format.underline) # Handle super/subscript. if xf_format.font_script == 1: self._write_vert_align("superscript") if xf_format.font_script == 2: self._write_vert_align("subscript") # Write the font size xml_writer._xml_empty_tag("sz", [("val", xf_format.font_size)]) # Handle colors. if xf_format.theme == -1: # Ignore for excel2003_style. pass elif xf_format.theme: self._write_color("theme", xf_format.theme) elif xf_format.color_indexed: self._write_color("indexed", xf_format.color_indexed) elif xf_format.font_color: color = self._get_palette_color(xf_format.font_color) self._write_rstring_color("rgb", color) else: self._write_rstring_color("theme", 1) # Write some other font properties related to font families. xml_writer._xml_empty_tag("rFont", [("val", xf_format.font_name)]) xml_writer._xml_empty_tag("family", [("val", xf_format.font_family)]) if xf_format.font_name == "Calibri" and not xf_format.hyperlink: xml_writer._xml_empty_tag("scheme", [("val", xf_format.font_scheme)]) xml_writer._xml_end_tag("rPr") def _write_underline(self, underline): # Write the underline font element. attributes = [] # Handle the underline variants. if underline == 2: attributes = [("val", "double")] elif underline == 33: attributes = [("val", "singleAccounting")] elif underline == 34: attributes = [("val", "doubleAccounting")] self.rstring._xml_empty_tag("u", attributes) def _write_vert_align(self, val): # Write the font sub-element. attributes = [("val", val)] self.rstring._xml_empty_tag("vertAlign", attributes) def _write_rstring_color(self, name, value): # Write the element. attributes = [(name, value)] self.rstring._xml_empty_tag("color", attributes) def _get_palette_color(self, color): # Convert the RGB color. if color[0] == "#": color = color[1:] return "FF" + color.upper() def _opt_close(self): # Close the row data filehandle in constant_memory mode. if not self.row_data_fh_closed: self.row_data_fh.close() self.row_data_fh_closed = True def _opt_reopen(self): # Reopen the row data filehandle in constant_memory mode. if self.row_data_fh_closed: filename = self.row_data_filename # pylint: disable=consider-using-with self.row_data_fh = open(filename, mode="a+", encoding="utf-8") self.row_data_fh_closed = False self.fh = self.row_data_fh def _set_icon_props(self, total_icons, user_props=None): # Set the sub-properties for icons. props = [] # Set the defaults. for _ in range(total_icons): props.append({"criteria": False, "value": 0, "type": "percent"}) # Set the default icon values based on the number of icons. if total_icons == 3: props[0]["value"] = 67 props[1]["value"] = 33 if total_icons == 4: props[0]["value"] = 75 props[1]["value"] = 50 props[2]["value"] = 25 if total_icons == 5: props[0]["value"] = 80 props[1]["value"] = 60 props[2]["value"] = 40 props[3]["value"] = 20 # Overwrite default properties with user defined properties. if user_props: # Ensure we don't set user properties for lowest icon. max_data = len(user_props) if max_data >= total_icons: max_data = total_icons - 1 for i in range(max_data): # Set the user defined 'value' property. if user_props[i].get("value") is not None: props[i]["value"] = user_props[i]["value"] # Remove the formula '=' sign if it exists. tmp = props[i]["value"] if isinstance(tmp, str) and tmp.startswith("="): props[i]["value"] = tmp.lstrip("=") # Set the user defined 'type' property. if user_props[i].get("type"): valid_types = ("percent", "percentile", "number", "formula") if user_props[i]["type"] not in valid_types: warn( f"Unknown icon property type '{user_props[i]['type']}' " f"for sub-property 'type' in conditional_format()." ) else: props[i]["type"] = user_props[i]["type"] if props[i]["type"] == "number": props[i]["type"] = "num" # Set the user defined 'criteria' property. criteria = user_props[i].get("criteria") if criteria and criteria == ">": props[i]["criteria"] = True return props ########################################################################### # # XML methods. # ########################################################################### def _write_worksheet(self): # Write the element. This is the root element. schema = "http://schemas.openxmlformats.org/" xmlns = schema + "spreadsheetml/2006/main" xmlns_r = schema + "officeDocument/2006/relationships" xmlns_mc = schema + "markup-compatibility/2006" ms_schema = "http://schemas.microsoft.com/" xmlns_x14ac = ms_schema + "office/spreadsheetml/2009/9/ac" attributes = [("xmlns", xmlns), ("xmlns:r", xmlns_r)] # Add some extra attributes for Excel 2010. Mainly for sparklines. if self.excel_version == 2010: attributes.append(("xmlns:mc", xmlns_mc)) attributes.append(("xmlns:x14ac", xmlns_x14ac)) attributes.append(("mc:Ignorable", "x14ac")) self._xml_start_tag("worksheet", attributes) def _write_dimension(self): # Write the element. This specifies the range of # cells in the worksheet. As a special case, empty # spreadsheets use 'A1' as a range. if self.dim_rowmin is None and self.dim_colmin is None: # If the min dimensions are not defined then no dimensions # have been set and we use the default 'A1'. ref = "A1" elif self.dim_rowmin is None and self.dim_colmin is not None: # If the row dimensions aren't set but the column # dimensions are set then they have been changed via # set_column(). if self.dim_colmin == self.dim_colmax: # The dimensions are a single cell and not a range. ref = xl_rowcol_to_cell(0, self.dim_colmin) else: # The dimensions are a cell range. cell_1 = xl_rowcol_to_cell(0, self.dim_colmin) cell_2 = xl_rowcol_to_cell(0, self.dim_colmax) ref = cell_1 + ":" + cell_2 elif self.dim_rowmin == self.dim_rowmax and self.dim_colmin == self.dim_colmax: # The dimensions are a single cell and not a range. ref = xl_rowcol_to_cell(self.dim_rowmin, self.dim_colmin) else: # The dimensions are a cell range. cell_1 = xl_rowcol_to_cell(self.dim_rowmin, self.dim_colmin) cell_2 = xl_rowcol_to_cell(self.dim_rowmax, self.dim_colmax) ref = cell_1 + ":" + cell_2 self._xml_empty_tag("dimension", [("ref", ref)]) def _write_sheet_views(self): # Write the element. self._xml_start_tag("sheetViews") # Write the sheetView element. self._write_sheet_view() self._xml_end_tag("sheetViews") def _write_sheet_view(self): # Write the element. attributes = [] # Hide screen gridlines if required. if not self.screen_gridlines: attributes.append(("showGridLines", 0)) # Hide screen row/column headers. if self.row_col_headers: attributes.append(("showRowColHeaders", 0)) # Hide zeroes in cells. if not self.show_zeros: attributes.append(("showZeros", 0)) # Display worksheet right to left for Hebrew, Arabic and others. if self.is_right_to_left: attributes.append(("rightToLeft", 1)) # Show that the sheet tab is selected. if self.selected: attributes.append(("tabSelected", 1)) # Turn outlines off. Also required in the outlinePr element. if not self.outline_on: attributes.append(("showOutlineSymbols", 0)) # Set the page view/layout mode if required. if self.page_view == 1: attributes.append(("view", "pageLayout")) elif self.page_view == 2: attributes.append(("view", "pageBreakPreview")) # Set the first visible cell. if self.top_left_cell != "": attributes.append(("topLeftCell", self.top_left_cell)) # Set the zoom level. if self.zoom != 100: attributes.append(("zoomScale", self.zoom)) if self.page_view == 0 and self.zoom_scale_normal: attributes.append(("zoomScaleNormal", self.zoom)) if self.page_view == 1: attributes.append(("zoomScalePageLayoutView", self.zoom)) if self.page_view == 2: attributes.append(("zoomScaleSheetLayoutView", self.zoom)) attributes.append(("workbookViewId", 0)) if self.panes or self.selections: self._xml_start_tag("sheetView", attributes) self._write_panes() self._write_selections() self._xml_end_tag("sheetView") else: self._xml_empty_tag("sheetView", attributes) def _write_sheet_format_pr(self): # Write the element. default_row_height = self.default_row_height row_level = self.outline_row_level col_level = self.outline_col_level attributes = [("defaultRowHeight", default_row_height)] if self.default_row_height != self.original_row_height: attributes.append(("customHeight", 1)) if self.default_row_zeroed: attributes.append(("zeroHeight", 1)) if row_level: attributes.append(("outlineLevelRow", row_level)) if col_level: attributes.append(("outlineLevelCol", col_level)) if self.excel_version == 2010: attributes.append(("x14ac:dyDescent", "0.25")) self._xml_empty_tag("sheetFormatPr", attributes) def _write_cols(self): # Write the element and sub elements. # Exit unless some column have been formatted. if not self.col_info: return self._xml_start_tag("cols") # Use the first element of the column information structures to set # the initial/previous properties. first_col = (sorted(self.col_info.keys()))[0] last_col = first_col prev_col_options = self.col_info[first_col] del self.col_info[first_col] deleted_col = first_col deleted_col_options = prev_col_options for col in sorted(self.col_info.keys()): col_options = self.col_info[col] # Check if the column number is contiguous with the previous # column and if the properties are the same. if col == last_col + 1 and col_options == prev_col_options: last_col = col else: # If not contiguous/equal then we write out the current range # of columns and start again. self._write_col_info(first_col, last_col, prev_col_options) first_col = col last_col = first_col prev_col_options = col_options # We will exit the previous loop with one unhandled column range. self._write_col_info(first_col, last_col, prev_col_options) # Put back the deleted first column information structure. self.col_info[deleted_col] = deleted_col_options self._xml_end_tag("cols") def _write_col_info(self, col_min, col_max, col_info): # Write the element. (width, cell_format, hidden, level, collapsed, autofit) = col_info custom_width = 1 xf_index = 0 # Get the cell_format index. if cell_format: xf_index = cell_format._get_xf_index() # Set the Excel default column width. if width is None: if not hidden: width = 8.43 custom_width = 0 else: width = 0 elif width == 8.43: # Width is defined but same as default. custom_width = 0 # Convert column width from user units to character width. if width > 0: # For Calabri 11. max_digit_width = 7 padding = 5 if width < 1: width = ( int( (int(width * (max_digit_width + padding) + 0.5)) / float(max_digit_width) * 256.0 ) / 256.0 ) else: width = ( int( (int(width * max_digit_width + 0.5) + padding) / float(max_digit_width) * 256.0 ) / 256.0 ) attributes = [ ("min", col_min + 1), ("max", col_max + 1), ("width", f"{width:.16g}"), ] if xf_index: attributes.append(("style", xf_index)) if hidden: attributes.append(("hidden", "1")) if autofit: attributes.append(("bestFit", "1")) if custom_width: attributes.append(("customWidth", "1")) if level: attributes.append(("outlineLevel", level)) if collapsed: attributes.append(("collapsed", "1")) self._xml_empty_tag("col", attributes) def _write_sheet_data(self): # Write the element. if self.dim_rowmin is None: # If the dimensions aren't defined there is no data to write. self._xml_empty_tag("sheetData") else: self._xml_start_tag("sheetData") self._write_rows() self._xml_end_tag("sheetData") def _write_optimized_sheet_data(self): # Write the element when constant_memory is on. In this # case we read the data stored in the temp file and rewrite it to the # XML sheet file. if self.dim_rowmin is None: # If the dimensions aren't defined then there is no data to write. self._xml_empty_tag("sheetData") else: self._xml_start_tag("sheetData") # Rewind the filehandle that was used for temp row data. buff_size = 65536 self.row_data_fh.seek(0) data = self.row_data_fh.read(buff_size) while data: self.fh.write(data) data = self.row_data_fh.read(buff_size) self.row_data_fh.close() os.unlink(self.row_data_filename) self._xml_end_tag("sheetData") def _write_page_margins(self): # Write the element. attributes = [ ("left", self.margin_left), ("right", self.margin_right), ("top", self.margin_top), ("bottom", self.margin_bottom), ("header", self.margin_header), ("footer", self.margin_footer), ] self._xml_empty_tag("pageMargins", attributes) def _write_page_setup(self): # Write the element. # # The following is an example taken from Excel. # # # attributes = [] # Skip this element if no page setup has changed. if not self.page_setup_changed: return # Set paper size. if self.paper_size: attributes.append(("paperSize", self.paper_size)) # Set the print_scale. if self.print_scale != 100: attributes.append(("scale", self.print_scale)) # Set the "Fit to page" properties. if self.fit_page and self.fit_width != 1: attributes.append(("fitToWidth", self.fit_width)) if self.fit_page and self.fit_height != 1: attributes.append(("fitToHeight", self.fit_height)) # Set the page print direction. if self.page_order: attributes.append(("pageOrder", "overThenDown")) # Set start page for printing. if self.page_start > 1: attributes.append(("firstPageNumber", self.page_start)) # Set page orientation. if self.orientation: attributes.append(("orientation", "portrait")) else: attributes.append(("orientation", "landscape")) # Set the print in black and white option. if self.black_white: attributes.append(("blackAndWhite", "1")) # Set start page for printing. if self.page_start != 0: attributes.append(("useFirstPageNumber", "1")) # Set the DPI. Mainly only for testing. if self.is_chartsheet: if self.horizontal_dpi: attributes.append(("horizontalDpi", self.horizontal_dpi)) if self.vertical_dpi: attributes.append(("verticalDpi", self.vertical_dpi)) else: if self.vertical_dpi: attributes.append(("verticalDpi", self.vertical_dpi)) if self.horizontal_dpi: attributes.append(("horizontalDpi", self.horizontal_dpi)) self._xml_empty_tag("pageSetup", attributes) def _write_print_options(self): # Write the element. attributes = [] if not self.print_options_changed: return # Set horizontal centering. if self.hcenter: attributes.append(("horizontalCentered", 1)) # Set vertical centering. if self.vcenter: attributes.append(("verticalCentered", 1)) # Enable row and column headers. if self.print_headers: attributes.append(("headings", 1)) # Set printed gridlines. if self.print_gridlines: attributes.append(("gridLines", 1)) self._xml_empty_tag("printOptions", attributes) def _write_header_footer(self): # Write the element. attributes = [] if not self.header_footer_scales: attributes.append(("scaleWithDoc", 0)) if not self.header_footer_aligns: attributes.append(("alignWithMargins", 0)) if self.header_footer_changed: self._xml_start_tag("headerFooter", attributes) if self.header: self._write_odd_header() if self.footer: self._write_odd_footer() self._xml_end_tag("headerFooter") elif self.excel2003_style: self._xml_empty_tag("headerFooter", attributes) def _write_odd_header(self): # Write the element. self._xml_data_element("oddHeader", self.header) def _write_odd_footer(self): # Write the element. self._xml_data_element("oddFooter", self.footer) def _write_rows(self): # Write out the worksheet data as a series of rows and cells. self._calculate_spans() for row_num in range(self.dim_rowmin, self.dim_rowmax + 1): if ( row_num in self.set_rows or row_num in self.comments or self.table[row_num] ): # Only process rows with formatting, cell data and/or comments. span_index = int(row_num / 16) if span_index in self.row_spans: span = self.row_spans[span_index] else: span = None if self.table[row_num]: # Write the cells if the row contains data. if row_num not in self.set_rows: self._write_row(row_num, span) else: self._write_row(row_num, span, self.set_rows[row_num]) for col_num in range(self.dim_colmin, self.dim_colmax + 1): if col_num in self.table[row_num]: col_ref = self.table[row_num][col_num] self._write_cell(row_num, col_num, col_ref) self._xml_end_tag("row") elif row_num in self.comments: # Row with comments in cells. self._write_empty_row(row_num, span, self.set_rows[row_num]) else: # Blank row with attributes only. self._write_empty_row(row_num, span, self.set_rows[row_num]) def _write_single_row(self, current_row_num=0): # Write out the worksheet data as a single row with cells. # This method is used when constant_memory is on. A single # row is written and the data table is reset. That way only # one row of data is kept in memory at any one time. We don't # write span data in the optimized case since it is optional. # Set the new previous row as the current row. row_num = self.previous_row self.previous_row = current_row_num if row_num in self.set_rows or row_num in self.comments or self.table[row_num]: # Only process rows with formatting, cell data and/or comments. # No span data in optimized mode. span = None if self.table[row_num]: # Write the cells if the row contains data. if row_num not in self.set_rows: self._write_row(row_num, span) else: self._write_row(row_num, span, self.set_rows[row_num]) for col_num in range(self.dim_colmin, self.dim_colmax + 1): if col_num in self.table[row_num]: col_ref = self.table[row_num][col_num] self._write_cell(row_num, col_num, col_ref) self._xml_end_tag("row") else: # Row attributes or comments only. self._write_empty_row(row_num, span, self.set_rows[row_num]) # Reset table. self.table.clear() def _calculate_spans(self): # Calculate the "spans" attribute of the tag. This is an # XLSX optimization and isn't strictly required. However, it # makes comparing files easier. The span is the same for each # block of 16 rows. spans = {} span_min = None span_max = None for row_num in range(self.dim_rowmin, self.dim_rowmax + 1): if row_num in self.table: # Calculate spans for cell data. for col_num in range(self.dim_colmin, self.dim_colmax + 1): if col_num in self.table[row_num]: if span_min is None: span_min = col_num span_max = col_num else: span_min = min(span_min, col_num) span_max = max(span_max, col_num) if row_num in self.comments: # Calculate spans for comments. for col_num in range(self.dim_colmin, self.dim_colmax + 1): if row_num in self.comments and col_num in self.comments[row_num]: if span_min is None: span_min = col_num span_max = col_num else: span_min = min(span_min, col_num) span_max = max(span_max, col_num) if ((row_num + 1) % 16 == 0) or row_num == self.dim_rowmax: span_index = int(row_num / 16) if span_min is not None: span_min += 1 span_max += 1 spans[span_index] = f"{span_min}:{span_max}" span_min = None self.row_spans = spans def _write_row(self, row, spans, properties=None, empty_row=False): # Write the element. xf_index = 0 if properties: height, cell_format, hidden, level, collapsed = properties else: height, cell_format, hidden, level, collapsed = None, None, 0, 0, 0 if height is None: height = self.default_row_height attributes = [("r", row + 1)] # Get the cell_format index. if cell_format: xf_index = cell_format._get_xf_index() # Add row attributes where applicable. if spans: attributes.append(("spans", spans)) if xf_index: attributes.append(("s", xf_index)) if cell_format: attributes.append(("customFormat", 1)) if height != self.original_row_height or ( height == self.original_row_height and height != self.default_row_height ): attributes.append(("ht", f"{height:g}")) if hidden: attributes.append(("hidden", 1)) if height != self.original_row_height or ( height == self.original_row_height and height != self.default_row_height ): attributes.append(("customHeight", 1)) if level: attributes.append(("outlineLevel", level)) if collapsed: attributes.append(("collapsed", 1)) if self.excel_version == 2010: attributes.append(("x14ac:dyDescent", "0.25")) if empty_row: self._xml_empty_tag_unencoded("row", attributes) else: self._xml_start_tag_unencoded("row", attributes) def _write_empty_row(self, row, spans, properties=None): # Write and empty element. self._write_row(row, spans, properties, empty_row=True) def _write_cell(self, row, col, cell): # Write the element. # Note. This is the innermost loop so efficiency is important. cell_range = xl_rowcol_to_cell_fast(row, col) attributes = [("r", cell_range)] if cell.format: # Add the cell format index. xf_index = cell.format._get_xf_index() attributes.append(("s", xf_index)) elif row in self.set_rows and self.set_rows[row][1]: # Add the row format. row_xf = self.set_rows[row][1] attributes.append(("s", row_xf._get_xf_index())) elif col in self.col_info: # Add the column format. col_xf = self.col_info[col][1] if col_xf is not None: attributes.append(("s", col_xf._get_xf_index())) type_cell_name = cell.__class__.__name__ # Write the various cell types. if type_cell_name in ("Number", "Datetime"): # Write a number. self._xml_number_element(cell.number, attributes) elif type_cell_name in ("String", "RichString"): # Write a string. string = cell.string if not self.constant_memory: # Write a shared string. self._xml_string_element(string, attributes) else: # Write an optimized in-line string. # Convert control character to a _xHHHH_ escape. string = self._escape_control_characters(string) # Write any rich strings without further tags. if string.startswith("") and string.endswith(""): self._xml_rich_inline_string(string, attributes) else: # Add attribute to preserve leading or trailing whitespace. preserve = _preserve_whitespace(string) self._xml_inline_string(string, preserve, attributes) elif type_cell_name == "Formula": # Write a formula. First check the formula value type. value = cell.value if isinstance(cell.value, bool): attributes.append(("t", "b")) if cell.value: value = 1 else: value = 0 elif isinstance(cell.value, str): error_codes = ( "#DIV/0!", "#N/A", "#NAME?", "#NULL!", "#NUM!", "#REF!", "#VALUE!", ) if cell.value == "": # Allow blank to force recalc in some third party apps. pass elif cell.value in error_codes: attributes.append(("t", "e")) else: attributes.append(("t", "str")) self._xml_formula_element(cell.formula, value, attributes) elif type_cell_name == "ArrayFormula": # Write a array formula. if cell.atype == "dynamic": attributes.append(("cm", 1)) # First check if the formula value is a string. try: float(cell.value) except ValueError: attributes.append(("t", "str")) # Write an array formula. self._xml_start_tag("c", attributes) self._write_cell_array_formula(cell.formula, cell.range) self._write_cell_value(cell.value) self._xml_end_tag("c") elif type_cell_name == "Blank": # Write a empty cell. self._xml_empty_tag("c", attributes) elif type_cell_name == "Boolean": # Write a boolean cell. attributes.append(("t", "b")) self._xml_start_tag("c", attributes) self._write_cell_value(cell.boolean) self._xml_end_tag("c") elif type_cell_name == "Error": # Write a boolean cell. attributes.append(("t", "e")) attributes.append(("vm", cell.value)) self._xml_start_tag("c", attributes) self._write_cell_value(cell.error) self._xml_end_tag("c") def _write_cell_value(self, value): # Write the cell value element. if value is None: value = "" self._xml_data_element("v", value) def _write_cell_array_formula(self, formula, cell_range): # Write the cell array formula element. attributes = [("t", "array"), ("ref", cell_range)] self._xml_data_element("f", formula, attributes) def _write_sheet_pr(self): # Write the element for Sheet level properties. attributes = [] if ( not self.fit_page and not self.filter_on and not self.tab_color and not self.outline_changed and not self.vba_codename ): return if self.vba_codename: attributes.append(("codeName", self.vba_codename)) if self.filter_on: attributes.append(("filterMode", 1)) if self.fit_page or self.tab_color or self.outline_changed: self._xml_start_tag("sheetPr", attributes) self._write_tab_color() self._write_outline_pr() self._write_page_set_up_pr() self._xml_end_tag("sheetPr") else: self._xml_empty_tag("sheetPr", attributes) def _write_page_set_up_pr(self): # Write the element. if not self.fit_page: return attributes = [("fitToPage", 1)] self._xml_empty_tag("pageSetUpPr", attributes) def _write_tab_color(self): # Write the element. color = self.tab_color if not color: return attributes = [("rgb", color)] self._xml_empty_tag("tabColor", attributes) def _write_outline_pr(self): # Write the element. attributes = [] if not self.outline_changed: return if self.outline_style: attributes.append(("applyStyles", 1)) if not self.outline_below: attributes.append(("summaryBelow", 0)) if not self.outline_right: attributes.append(("summaryRight", 0)) if not self.outline_on: attributes.append(("showOutlineSymbols", 0)) self._xml_empty_tag("outlinePr", attributes) def _write_row_breaks(self): # Write the element. page_breaks = self._sort_pagebreaks(self.hbreaks) if not page_breaks: return count = len(page_breaks) attributes = [ ("count", count), ("manualBreakCount", count), ] self._xml_start_tag("rowBreaks", attributes) for row_num in page_breaks: self._write_brk(row_num, 16383) self._xml_end_tag("rowBreaks") def _write_col_breaks(self): # Write the element. page_breaks = self._sort_pagebreaks(self.vbreaks) if not page_breaks: return count = len(page_breaks) attributes = [ ("count", count), ("manualBreakCount", count), ] self._xml_start_tag("colBreaks", attributes) for col_num in page_breaks: self._write_brk(col_num, 1048575) self._xml_end_tag("colBreaks") def _write_brk(self, brk_id, brk_max): # Write the element. attributes = [("id", brk_id), ("max", brk_max), ("man", 1)] self._xml_empty_tag("brk", attributes) def _write_merge_cells(self): # Write the element. merged_cells = self.merge count = len(merged_cells) if not count: return attributes = [("count", count)] self._xml_start_tag("mergeCells", attributes) for merged_range in merged_cells: # Write the mergeCell element. self._write_merge_cell(merged_range) self._xml_end_tag("mergeCells") def _write_merge_cell(self, merged_range): # Write the element. (row_min, col_min, row_max, col_max) = merged_range # Convert the merge dimensions to a cell range. cell_1 = xl_rowcol_to_cell(row_min, col_min) cell_2 = xl_rowcol_to_cell(row_max, col_max) ref = cell_1 + ":" + cell_2 attributes = [("ref", ref)] self._xml_empty_tag("mergeCell", attributes) def _write_hyperlinks(self): # Process any stored hyperlinks in row/col order and write the # element. The attributes are different for internal # and external links. # Sort the hyperlinks into row order. row_nums = sorted(self.hyperlinks.keys()) # Exit if there are no hyperlinks to process. if not row_nums: return # Write the hyperlink elements. self._xml_start_tag("hyperlinks") # Iterate over the rows. for row_num in row_nums: # Sort the hyperlinks into column order. col_nums = sorted(self.hyperlinks[row_num].keys()) # Iterate over the columns. for col_num in col_nums: # Get the link data for this cell. url = self.hyperlinks[row_num][col_num] # If the cell was overwritten by the user and isn't a string # then we have to add the url as the string to display. if self.table and self.table[row_num] and self.table[row_num][col_num]: cell = self.table[row_num][col_num] if cell.__class__.__name__ != "String": url._is_object_link = True if url._link_type in (UrlTypes.URL, UrlTypes.EXTERNAL): # External link with rel file relationship. self.rel_count += 1 self._write_hyperlink_external( row_num, col_num, self.rel_count, url ) # Links for use by the packager. self.external_hyper_links.append( ["/hyperlink", url._target(), "External"] ) else: # Internal link with rel file relationship. self._write_hyperlink_internal(row_num, col_num, url) self._xml_end_tag("hyperlinks") def _write_hyperlink_external(self, row: int, col: int, id_num: int, url: Url): # Write the element for external links. ref = xl_rowcol_to_cell(row, col) r_id = "rId" + str(id_num) attributes = [("ref", ref), ("r:id", r_id)] if url._anchor: attributes.append(("location", url._anchor)) if url._is_object_link: attributes.append(("display", url._text)) if url._tip: attributes.append(("tooltip", url._tip)) self._xml_empty_tag("hyperlink", attributes) def _write_hyperlink_internal(self, row: int, col: int, url: Url): # Write the element for internal links. ref = xl_rowcol_to_cell(row, col) attributes = [("ref", ref), ("location", url._link)] if url._tip: attributes.append(("tooltip", url._tip)) attributes.append(("display", url._text)) self._xml_empty_tag("hyperlink", attributes) def _write_auto_filter(self): # Write the element. if not self.autofilter_ref: return attributes = [("ref", self.autofilter_ref)] if self.filter_on: # Autofilter defined active filters. self._xml_start_tag("autoFilter", attributes) self._write_autofilters() self._xml_end_tag("autoFilter") else: # Autofilter defined without active filters. self._xml_empty_tag("autoFilter", attributes) def _write_autofilters(self): # Function to iterate through the columns that form part of an # autofilter range and write the appropriate filters. (col1, col2) = self.filter_range for col in range(col1, col2 + 1): # Skip if column doesn't have an active filter. if col not in self.filter_cols: continue # Retrieve the filter tokens and write the autofilter records. tokens = self.filter_cols[col] filter_type = self.filter_type[col] # Filters are relative to first column in the autofilter. self._write_filter_column(col - col1, filter_type, tokens) def _write_filter_column(self, col_id, filter_type, filters): # Write the element. attributes = [("colId", col_id)] self._xml_start_tag("filterColumn", attributes) if filter_type == 1: # Type == 1 is the new XLSX style filter. self._write_filters(filters) else: # Type == 0 is the classic "custom" filter. self._write_custom_filters(filters) self._xml_end_tag("filterColumn") def _write_filters(self, filters): # Write the element. non_blanks = [filter for filter in filters if str(filter).lower() != "blanks"] attributes = [] if len(filters) != len(non_blanks): attributes = [("blank", 1)] if len(filters) == 1 and len(non_blanks) == 0: # Special case for blank cells only. self._xml_empty_tag("filters", attributes) else: # General case. self._xml_start_tag("filters", attributes) for autofilter in sorted(non_blanks): self._write_filter(autofilter) self._xml_end_tag("filters") def _write_filter(self, val): # Write the element. attributes = [("val", val)] self._xml_empty_tag("filter", attributes) def _write_custom_filters(self, tokens): # Write the element. if len(tokens) == 2: # One filter expression only. self._xml_start_tag("customFilters") self._write_custom_filter(*tokens) self._xml_end_tag("customFilters") else: # Two filter expressions. attributes = [] # Check if the "join" operand is "and" or "or". if tokens[2] == 0: attributes = [("and", 1)] else: attributes = [("and", 0)] # Write the two custom filters. self._xml_start_tag("customFilters", attributes) self._write_custom_filter(tokens[0], tokens[1]) self._write_custom_filter(tokens[3], tokens[4]) self._xml_end_tag("customFilters") def _write_custom_filter(self, operator, val): # Write the element. attributes = [] operators = { 1: "lessThan", 2: "equal", 3: "lessThanOrEqual", 4: "greaterThan", 5: "notEqual", 6: "greaterThanOrEqual", 22: "equal", } # Convert the operator from a number to a descriptive string. if operators[operator] is not None: operator = operators[operator] else: warn(f"Unknown operator = {operator}") # The 'equal' operator is the default attribute and isn't stored. if operator != "equal": attributes.append(("operator", operator)) attributes.append(("val", val)) self._xml_empty_tag("customFilter", attributes) def _write_sheet_protection(self): # Write the element. attributes = [] if not self.protect_options: return options = self.protect_options if options["password"]: attributes.append(("password", options["password"])) if options["sheet"]: attributes.append(("sheet", 1)) if options["content"]: attributes.append(("content", 1)) if not options["objects"]: attributes.append(("objects", 1)) if not options["scenarios"]: attributes.append(("scenarios", 1)) if options["format_cells"]: attributes.append(("formatCells", 0)) if options["format_columns"]: attributes.append(("formatColumns", 0)) if options["format_rows"]: attributes.append(("formatRows", 0)) if options["insert_columns"]: attributes.append(("insertColumns", 0)) if options["insert_rows"]: attributes.append(("insertRows", 0)) if options["insert_hyperlinks"]: attributes.append(("insertHyperlinks", 0)) if options["delete_columns"]: attributes.append(("deleteColumns", 0)) if options["delete_rows"]: attributes.append(("deleteRows", 0)) if not options["select_locked_cells"]: attributes.append(("selectLockedCells", 1)) if options["sort"]: attributes.append(("sort", 0)) if options["autofilter"]: attributes.append(("autoFilter", 0)) if options["pivot_tables"]: attributes.append(("pivotTables", 0)) if not options["select_unlocked_cells"]: attributes.append(("selectUnlockedCells", 1)) self._xml_empty_tag("sheetProtection", attributes) def _write_protected_ranges(self): # Write the element. if self.num_protected_ranges == 0: return self._xml_start_tag("protectedRanges") for cell_range, range_name, password in self.protected_ranges: self._write_protected_range(cell_range, range_name, password) self._xml_end_tag("protectedRanges") def _write_protected_range(self, cell_range, range_name, password): # Write the element. attributes = [] if password: attributes.append(("password", password)) attributes.append(("sqref", cell_range)) attributes.append(("name", range_name)) self._xml_empty_tag("protectedRange", attributes) def _write_drawings(self): # Write the elements. if not self.drawing: return self.rel_count += 1 self._write_drawing(self.rel_count) def _write_drawing(self, drawing_id): # Write the element. r_id = "rId" + str(drawing_id) attributes = [("r:id", r_id)] self._xml_empty_tag("drawing", attributes) def _write_legacy_drawing(self): # Write the element. if not self.has_vml: return # Increment the relationship id for any drawings or comments. self.rel_count += 1 r_id = "rId" + str(self.rel_count) attributes = [("r:id", r_id)] self._xml_empty_tag("legacyDrawing", attributes) def _write_legacy_drawing_hf(self): # Write the element. if not self.has_header_vml: return # Increment the relationship id for any drawings or comments. self.rel_count += 1 r_id = "rId" + str(self.rel_count) attributes = [("r:id", r_id)] self._xml_empty_tag("legacyDrawingHF", attributes) def _write_picture(self): # Write the element. if not self.background_image: return # Increment the relationship id. self.rel_count += 1 r_id = "rId" + str(self.rel_count) attributes = [("r:id", r_id)] self._xml_empty_tag("picture", attributes) def _write_data_validations(self): # Write the element. validations = self.validations count = len(validations) if not count: return attributes = [("count", count)] self._xml_start_tag("dataValidations", attributes) for validation in validations: # Write the dataValidation element. self._write_data_validation(validation) self._xml_end_tag("dataValidations") def _write_data_validation(self, options): # Write the element. sqref = "" attributes = [] # Set the cell range(s) for the data validation. for cells in options["cells"]: # Add a space between multiple cell ranges. if sqref != "": sqref += " " (row_first, col_first, row_last, col_last) = cells # Swap last row/col for first row/col as necessary if row_first > row_last: (row_first, row_last) = (row_last, row_first) if col_first > col_last: (col_first, col_last) = (col_last, col_first) sqref += xl_range(row_first, col_first, row_last, col_last) if options.get("multi_range"): sqref = options["multi_range"] if options["validate"] != "none": attributes.append(("type", options["validate"])) if options["criteria"] != "between": attributes.append(("operator", options["criteria"])) if "error_type" in options: if options["error_type"] == 1: attributes.append(("errorStyle", "warning")) if options["error_type"] == 2: attributes.append(("errorStyle", "information")) if options["ignore_blank"]: attributes.append(("allowBlank", 1)) if not options["dropdown"]: attributes.append(("showDropDown", 1)) if options["show_input"]: attributes.append(("showInputMessage", 1)) if options["show_error"]: attributes.append(("showErrorMessage", 1)) if "error_title" in options: attributes.append(("errorTitle", options["error_title"])) if "error_message" in options: attributes.append(("error", options["error_message"])) if "input_title" in options: attributes.append(("promptTitle", options["input_title"])) if "input_message" in options: attributes.append(("prompt", options["input_message"])) attributes.append(("sqref", sqref)) if options["validate"] == "none": self._xml_empty_tag("dataValidation", attributes) else: self._xml_start_tag("dataValidation", attributes) # Write the formula1 element. self._write_formula_1(options["value"]) # Write the formula2 element. if options["maximum"] is not None: self._write_formula_2(options["maximum"]) self._xml_end_tag("dataValidation") def _write_formula_1(self, formula): # Write the element. if isinstance(formula, list): formula = self._csv_join(*formula) formula = f'"{formula}"' else: # Check if the formula is a number. try: float(formula) except ValueError: # Not a number. Remove the formula '=' sign if it exists. if formula.startswith("="): formula = formula.lstrip("=") self._xml_data_element("formula1", formula) def _write_formula_2(self, formula): # Write the element. # Check if the formula is a number. try: float(formula) except ValueError: # Not a number. Remove the formula '=' sign if it exists. if formula.startswith("="): formula = formula.lstrip("=") self._xml_data_element("formula2", formula) def _write_conditional_formats(self): # Write the Worksheet conditional formats. ranges = sorted(self.cond_formats.keys()) if not ranges: return for cond_range in ranges: self._write_conditional_formatting( cond_range, self.cond_formats[cond_range] ) def _write_conditional_formatting(self, cond_range, params): # Write the element. attributes = [("sqref", cond_range)] self._xml_start_tag("conditionalFormatting", attributes) for param in params: # Write the cfRule element. self._write_cf_rule(param) self._xml_end_tag("conditionalFormatting") def _write_cf_rule(self, params): # Write the element. attributes = [("type", params["type"])] if "format" in params and params["format"] is not None: attributes.append(("dxfId", params["format"])) attributes.append(("priority", params["priority"])) if params.get("stop_if_true"): attributes.append(("stopIfTrue", 1)) if params["type"] == "cellIs": attributes.append(("operator", params["criteria"])) self._xml_start_tag("cfRule", attributes) if "minimum" in params and "maximum" in params: self._write_formula_element(params["minimum"]) self._write_formula_element(params["maximum"]) else: self._write_formula_element(params["value"]) self._xml_end_tag("cfRule") elif params["type"] == "aboveAverage": if re.search("below", params["criteria"]): attributes.append(("aboveAverage", 0)) if re.search("equal", params["criteria"]): attributes.append(("equalAverage", 1)) if re.search("[123] std dev", params["criteria"]): match = re.search("([123]) std dev", params["criteria"]) attributes.append(("stdDev", match.group(1))) self._xml_empty_tag("cfRule", attributes) elif params["type"] == "top10": if "criteria" in params and params["criteria"] == "%": attributes.append(("percent", 1)) if "direction" in params: attributes.append(("bottom", 1)) rank = params["value"] or 10 attributes.append(("rank", rank)) self._xml_empty_tag("cfRule", attributes) elif params["type"] == "duplicateValues": self._xml_empty_tag("cfRule", attributes) elif params["type"] == "uniqueValues": self._xml_empty_tag("cfRule", attributes) elif ( params["type"] == "containsText" or params["type"] == "notContainsText" or params["type"] == "beginsWith" or params["type"] == "endsWith" ): attributes.append(("operator", params["criteria"])) attributes.append(("text", params["value"])) self._xml_start_tag("cfRule", attributes) self._write_formula_element(params["formula"]) self._xml_end_tag("cfRule") elif params["type"] == "timePeriod": attributes.append(("timePeriod", params["criteria"])) self._xml_start_tag("cfRule", attributes) self._write_formula_element(params["formula"]) self._xml_end_tag("cfRule") elif ( params["type"] == "containsBlanks" or params["type"] == "notContainsBlanks" or params["type"] == "containsErrors" or params["type"] == "notContainsErrors" ): self._xml_start_tag("cfRule", attributes) self._write_formula_element(params["formula"]) self._xml_end_tag("cfRule") elif params["type"] == "colorScale": self._xml_start_tag("cfRule", attributes) self._write_color_scale(params) self._xml_end_tag("cfRule") elif params["type"] == "dataBar": self._xml_start_tag("cfRule", attributes) self._write_data_bar(params) if params.get("is_data_bar_2010"): self._write_data_bar_ext(params) self._xml_end_tag("cfRule") elif params["type"] == "expression": self._xml_start_tag("cfRule", attributes) self._write_formula_element(params["criteria"]) self._xml_end_tag("cfRule") elif params["type"] == "iconSet": self._xml_start_tag("cfRule", attributes) self._write_icon_set(params) self._xml_end_tag("cfRule") def _write_formula_element(self, formula): # Write the element. # Check if the formula is a number. try: float(formula) except ValueError: # Not a number. Remove the formula '=' sign if it exists. if formula.startswith("="): formula = formula.lstrip("=") self._xml_data_element("formula", formula) def _write_color_scale(self, param): # Write the element. self._xml_start_tag("colorScale") self._write_cfvo(param["min_type"], param["min_value"]) if param["mid_type"] is not None: self._write_cfvo(param["mid_type"], param["mid_value"]) self._write_cfvo(param["max_type"], param["max_value"]) self._write_color("rgb", param["min_color"]) if param["mid_color"] is not None: self._write_color("rgb", param["mid_color"]) self._write_color("rgb", param["max_color"]) self._xml_end_tag("colorScale") def _write_data_bar(self, param): # Write the element. attributes = [] # Min and max bar lengths in in the spec but not supported directly by # Excel. if "min_length" in param: attributes.append(("minLength", param["min_length"])) if "max_length" in param: attributes.append(("maxLength", param["max_length"])) if param.get("bar_only"): attributes.append(("showValue", 0)) self._xml_start_tag("dataBar", attributes) self._write_cfvo(param["min_type"], param["min_value"]) self._write_cfvo(param["max_type"], param["max_value"]) self._write_color("rgb", param["bar_color"]) self._xml_end_tag("dataBar") def _write_data_bar_ext(self, param): # Write the dataBar extension element. # Create a pseudo GUID for each unique Excel 2010 data bar. worksheet_count = self.index + 1 data_bar_count = len(self.data_bars_2010) + 1 guid = "{DA7ABA51-AAAA-BBBB-%04X-%012X}" % (worksheet_count, data_bar_count) # Store the 2010 data bar parameters to write the extLst elements. param["guid"] = guid self.data_bars_2010.append(param) self._xml_start_tag("extLst") self._write_ext("{B025F937-C7B1-47D3-B67F-A62EFF666E3E}") self._xml_data_element("x14:id", guid) self._xml_end_tag("ext") self._xml_end_tag("extLst") def _write_icon_set(self, param): # Write the element. attributes = [] # Don't set attribute for default style. if param["icon_style"] != "3TrafficLights": attributes = [("iconSet", param["icon_style"])] if param.get("icons_only"): attributes.append(("showValue", 0)) if param.get("reverse_icons"): attributes.append(("reverse", 1)) self._xml_start_tag("iconSet", attributes) # Write the properties for different icon styles. for icon in reversed(param["icons"]): self._write_cfvo(icon["type"], icon["value"], icon["criteria"]) self._xml_end_tag("iconSet") def _write_cfvo(self, cf_type, val, criteria=None): # Write the element. attributes = [("type", cf_type)] if val is not None: attributes.append(("val", val)) if criteria: attributes.append(("gte", 0)) self._xml_empty_tag("cfvo", attributes) def _write_color(self, name, value): # Write the element. attributes = [(name, value)] self._xml_empty_tag("color", attributes) def _write_selections(self): # Write the elements. for selection in self.selections: self._write_selection(*selection) def _write_selection(self, pane, active_cell, sqref): # Write the element. attributes = [] if pane: attributes.append(("pane", pane)) if active_cell: attributes.append(("activeCell", active_cell)) if sqref: attributes.append(("sqref", sqref)) self._xml_empty_tag("selection", attributes) def _write_panes(self): # Write the frozen or split elements. panes = self.panes if not panes: return if panes[4] == 2: self._write_split_panes(*panes) else: self._write_freeze_panes(*panes) def _write_freeze_panes(self, row, col, top_row, left_col, pane_type): # Write the element for freeze panes. attributes = [] y_split = row x_split = col top_left_cell = xl_rowcol_to_cell(top_row, left_col) active_pane = "" state = "" active_cell = "" sqref = "" # Move user cell selection to the panes. if self.selections: (_, active_cell, sqref) = self.selections[0] self.selections = [] # Set the active pane. if row and col: active_pane = "bottomRight" row_cell = xl_rowcol_to_cell(row, 0) col_cell = xl_rowcol_to_cell(0, col) self.selections.append(["topRight", col_cell, col_cell]) self.selections.append(["bottomLeft", row_cell, row_cell]) self.selections.append(["bottomRight", active_cell, sqref]) elif col: active_pane = "topRight" self.selections.append(["topRight", active_cell, sqref]) else: active_pane = "bottomLeft" self.selections.append(["bottomLeft", active_cell, sqref]) # Set the pane type. if pane_type == 0: state = "frozen" elif pane_type == 1: state = "frozenSplit" else: state = "split" if x_split: attributes.append(("xSplit", x_split)) if y_split: attributes.append(("ySplit", y_split)) attributes.append(("topLeftCell", top_left_cell)) attributes.append(("activePane", active_pane)) attributes.append(("state", state)) self._xml_empty_tag("pane", attributes) def _write_split_panes(self, row, col, top_row, left_col, _): # Write the element for split panes. attributes = [] has_selection = False active_pane = "" active_cell = "" sqref = "" y_split = row x_split = col # Move user cell selection to the panes. if self.selections: (_, active_cell, sqref) = self.selections[0] self.selections = [] has_selection = True # Convert the row and col to 1/20 twip units with padding. if y_split: y_split = int(20 * y_split + 300) if x_split: x_split = self._calculate_x_split_width(x_split) # For non-explicit topLeft definitions, estimate the cell offset based # on the pixels dimensions. This is only a workaround and doesn't take # adjusted cell dimensions into account. if top_row == row and left_col == col: top_row = int(0.5 + (y_split - 300) / 20 / 15) left_col = int(0.5 + (x_split - 390) / 20 / 3 * 4 / 64) top_left_cell = xl_rowcol_to_cell(top_row, left_col) # If there is no selection set the active cell to the top left cell. if not has_selection: active_cell = top_left_cell sqref = top_left_cell # Set the Cell selections. if row and col: active_pane = "bottomRight" row_cell = xl_rowcol_to_cell(top_row, 0) col_cell = xl_rowcol_to_cell(0, left_col) self.selections.append(["topRight", col_cell, col_cell]) self.selections.append(["bottomLeft", row_cell, row_cell]) self.selections.append(["bottomRight", active_cell, sqref]) elif col: active_pane = "topRight" self.selections.append(["topRight", active_cell, sqref]) else: active_pane = "bottomLeft" self.selections.append(["bottomLeft", active_cell, sqref]) # Format splits to the same precision as Excel. if x_split: attributes.append(("xSplit", f"{x_split:.16g}")) if y_split: attributes.append(("ySplit", f"{y_split:.16g}")) attributes.append(("topLeftCell", top_left_cell)) if has_selection: attributes.append(("activePane", active_pane)) self._xml_empty_tag("pane", attributes) def _calculate_x_split_width(self, width): # Convert column width from user units to pane split width. max_digit_width = 7 # For Calabri 11. padding = 5 # Convert to pixels. if width < 1: pixels = int(width * (max_digit_width + padding) + 0.5) else: pixels = int(width * max_digit_width + 0.5) + padding # Convert to points. points = pixels * 3 / 4 # Convert to twips (twentieths of a point). twips = points * 20 # Add offset/padding. width = twips + 390 return width def _write_table_parts(self): # Write the element. tables = self.tables count = len(tables) # Return if worksheet doesn't contain any tables. if not count: return attributes = [ ( "count", count, ) ] self._xml_start_tag("tableParts", attributes) for _ in tables: # Write the tablePart element. self.rel_count += 1 self._write_table_part(self.rel_count) self._xml_end_tag("tableParts") def _write_table_part(self, r_id): # Write the element. r_id = "rId" + str(r_id) attributes = [ ( "r:id", r_id, ) ] self._xml_empty_tag("tablePart", attributes) def _write_ext_list(self): # Write the element for data bars and sparklines. has_data_bars = len(self.data_bars_2010) has_sparklines = len(self.sparklines) if not has_data_bars and not has_sparklines: return # Write the extLst element. self._xml_start_tag("extLst") if has_data_bars: self._write_ext_list_data_bars() if has_sparklines: self._write_ext_list_sparklines() self._xml_end_tag("extLst") def _write_ext_list_data_bars(self): # Write the Excel 2010 data_bar subelements. self._write_ext("{78C0D931-6437-407d-A8EE-F0AAD7539E65}") self._xml_start_tag("x14:conditionalFormattings") # Write the Excel 2010 conditional formatting data bar elements. for data_bar in self.data_bars_2010: # Write the x14:conditionalFormatting element. self._write_conditional_formatting_2010(data_bar) self._xml_end_tag("x14:conditionalFormattings") self._xml_end_tag("ext") def _write_conditional_formatting_2010(self, data_bar): # Write the element. xmlns_xm = "http://schemas.microsoft.com/office/excel/2006/main" attributes = [("xmlns:xm", xmlns_xm)] self._xml_start_tag("x14:conditionalFormatting", attributes) # Write the x14:cfRule element. self._write_x14_cf_rule(data_bar) # Write the x14:dataBar element. self._write_x14_data_bar(data_bar) # Write the x14 max and min data bars. self._write_x14_cfvo(data_bar["x14_min_type"], data_bar["min_value"]) self._write_x14_cfvo(data_bar["x14_max_type"], data_bar["max_value"]) if not data_bar["bar_no_border"]: # Write the x14:borderColor element. self._write_x14_border_color(data_bar["bar_border_color"]) # Write the x14:negativeFillColor element. if not data_bar["bar_negative_color_same"]: self._write_x14_negative_fill_color(data_bar["bar_negative_color"]) # Write the x14:negativeBorderColor element. if ( not data_bar["bar_no_border"] and not data_bar["bar_negative_border_color_same"] ): self._write_x14_negative_border_color(data_bar["bar_negative_border_color"]) # Write the x14:axisColor element. if data_bar["bar_axis_position"] != "none": self._write_x14_axis_color(data_bar["bar_axis_color"]) self._xml_end_tag("x14:dataBar") self._xml_end_tag("x14:cfRule") # Write the xm:sqref element. self._xml_data_element("xm:sqref", data_bar["range"]) self._xml_end_tag("x14:conditionalFormatting") def _write_x14_cf_rule(self, data_bar): # Write the element. rule_type = "dataBar" guid = data_bar["guid"] attributes = [("type", rule_type), ("id", guid)] self._xml_start_tag("x14:cfRule", attributes) def _write_x14_data_bar(self, data_bar): # Write the element. min_length = 0 max_length = 100 attributes = [ ("minLength", min_length), ("maxLength", max_length), ] if not data_bar["bar_no_border"]: attributes.append(("border", 1)) if data_bar["bar_solid"]: attributes.append(("gradient", 0)) if data_bar["bar_direction"] == "left": attributes.append(("direction", "leftToRight")) if data_bar["bar_direction"] == "right": attributes.append(("direction", "rightToLeft")) if data_bar["bar_negative_color_same"]: attributes.append(("negativeBarColorSameAsPositive", 1)) if ( not data_bar["bar_no_border"] and not data_bar["bar_negative_border_color_same"] ): attributes.append(("negativeBarBorderColorSameAsPositive", 0)) if data_bar["bar_axis_position"] == "middle": attributes.append(("axisPosition", "middle")) if data_bar["bar_axis_position"] == "none": attributes.append(("axisPosition", "none")) self._xml_start_tag("x14:dataBar", attributes) def _write_x14_cfvo(self, rule_type, value): # Write the element. attributes = [("type", rule_type)] if rule_type in ("min", "max", "autoMin", "autoMax"): self._xml_empty_tag("x14:cfvo", attributes) else: self._xml_start_tag("x14:cfvo", attributes) self._xml_data_element("xm:f", value) self._xml_end_tag("x14:cfvo") def _write_x14_border_color(self, rgb): # Write the element. attributes = [("rgb", rgb)] self._xml_empty_tag("x14:borderColor", attributes) def _write_x14_negative_fill_color(self, rgb): # Write the element. attributes = [("rgb", rgb)] self._xml_empty_tag("x14:negativeFillColor", attributes) def _write_x14_negative_border_color(self, rgb): # Write the element. attributes = [("rgb", rgb)] self._xml_empty_tag("x14:negativeBorderColor", attributes) def _write_x14_axis_color(self, rgb): # Write the element. attributes = [("rgb", rgb)] self._xml_empty_tag("x14:axisColor", attributes) def _write_ext_list_sparklines(self): # Write the sparkline extension sub-elements. self._write_ext("{05C60535-1F16-4fd2-B633-F4F36F0B64E0}") # Write the x14:sparklineGroups element. self._write_sparkline_groups() # Write the sparkline elements. for sparkline in reversed(self.sparklines): # Write the x14:sparklineGroup element. self._write_sparkline_group(sparkline) # Write the x14:colorSeries element. self._write_color_series(sparkline["series_color"]) # Write the x14:colorNegative element. self._write_color_negative(sparkline["negative_color"]) # Write the x14:colorAxis element. self._write_color_axis() # Write the x14:colorMarkers element. self._write_color_markers(sparkline["markers_color"]) # Write the x14:colorFirst element. self._write_color_first(sparkline["first_color"]) # Write the x14:colorLast element. self._write_color_last(sparkline["last_color"]) # Write the x14:colorHigh element. self._write_color_high(sparkline["high_color"]) # Write the x14:colorLow element. self._write_color_low(sparkline["low_color"]) if sparkline["date_axis"]: self._xml_data_element("xm:f", sparkline["date_axis"]) self._write_sparklines(sparkline) self._xml_end_tag("x14:sparklineGroup") self._xml_end_tag("x14:sparklineGroups") self._xml_end_tag("ext") def _write_sparklines(self, sparkline): # Write the element and sub-elements. # Write the sparkline elements. self._xml_start_tag("x14:sparklines") for i in range(sparkline["count"]): spark_range = sparkline["ranges"][i] location = sparkline["locations"][i] self._xml_start_tag("x14:sparkline") self._xml_data_element("xm:f", spark_range) self._xml_data_element("xm:sqref", location) self._xml_end_tag("x14:sparkline") self._xml_end_tag("x14:sparklines") def _write_ext(self, uri): # Write the element. schema = "http://schemas.microsoft.com/office/" xmlns_x14 = schema + "spreadsheetml/2009/9/main" attributes = [ ("xmlns:x14", xmlns_x14), ("uri", uri), ] self._xml_start_tag("ext", attributes) def _write_sparkline_groups(self): # Write the element. xmlns_xm = "http://schemas.microsoft.com/office/excel/2006/main" attributes = [("xmlns:xm", xmlns_xm)] self._xml_start_tag("x14:sparklineGroups", attributes) def _write_sparkline_group(self, options): # Write the element. # # Example for order. # # # empty = options.get("empty") attributes = [] if options.get("max") is not None: if options["max"] == "group": options["cust_max"] = "group" else: attributes.append(("manualMax", options["max"])) options["cust_max"] = "custom" if options.get("min") is not None: if options["min"] == "group": options["cust_min"] = "group" else: attributes.append(("manualMin", options["min"])) options["cust_min"] = "custom" # Ignore the default type attribute (line). if options["type"] != "line": attributes.append(("type", options["type"])) if options.get("weight"): attributes.append(("lineWeight", options["weight"])) if options.get("date_axis"): attributes.append(("dateAxis", 1)) if empty: attributes.append(("displayEmptyCellsAs", empty)) if options.get("markers"): attributes.append(("markers", 1)) if options.get("high"): attributes.append(("high", 1)) if options.get("low"): attributes.append(("low", 1)) if options.get("first"): attributes.append(("first", 1)) if options.get("last"): attributes.append(("last", 1)) if options.get("negative"): attributes.append(("negative", 1)) if options.get("axis"): attributes.append(("displayXAxis", 1)) if options.get("hidden"): attributes.append(("displayHidden", 1)) if options.get("cust_min"): attributes.append(("minAxisType", options["cust_min"])) if options.get("cust_max"): attributes.append(("maxAxisType", options["cust_max"])) if options.get("reverse"): attributes.append(("rightToLeft", 1)) self._xml_start_tag("x14:sparklineGroup", attributes) def _write_spark_color(self, element, color): # Helper function for the sparkline color functions below. attributes = [] if color.get("rgb"): attributes.append(("rgb", color["rgb"])) if color.get("theme"): attributes.append(("theme", color["theme"])) if color.get("tint"): attributes.append(("tint", color["tint"])) self._xml_empty_tag(element, attributes) def _write_color_series(self, color): # Write the element. self._write_spark_color("x14:colorSeries", color) def _write_color_negative(self, color): # Write the element. self._write_spark_color("x14:colorNegative", color) def _write_color_axis(self): # Write the element. self._write_spark_color("x14:colorAxis", {"rgb": "FF000000"}) def _write_color_markers(self, color): # Write the element. self._write_spark_color("x14:colorMarkers", color) def _write_color_first(self, color): # Write the element. self._write_spark_color("x14:colorFirst", color) def _write_color_last(self, color): # Write the element. self._write_spark_color("x14:colorLast", color) def _write_color_high(self, color): # Write the element. self._write_spark_color("x14:colorHigh", color) def _write_color_low(self, color): # Write the element. self._write_spark_color("x14:colorLow", color) def _write_phonetic_pr(self): # Write the element. attributes = [ ("fontId", "0"), ("type", "noConversion"), ] self._xml_empty_tag("phoneticPr", attributes) def _write_ignored_errors(self): # Write the element. if not self.ignored_errors: return self._xml_start_tag("ignoredErrors") if self.ignored_errors.get("number_stored_as_text"): ignored_range = self.ignored_errors["number_stored_as_text"] self._write_ignored_error("numberStoredAsText", ignored_range) if self.ignored_errors.get("eval_error"): ignored_range = self.ignored_errors["eval_error"] self._write_ignored_error("evalError", ignored_range) if self.ignored_errors.get("formula_differs"): ignored_range = self.ignored_errors["formula_differs"] self._write_ignored_error("formula", ignored_range) if self.ignored_errors.get("formula_range"): ignored_range = self.ignored_errors["formula_range"] self._write_ignored_error("formulaRange", ignored_range) if self.ignored_errors.get("formula_unlocked"): ignored_range = self.ignored_errors["formula_unlocked"] self._write_ignored_error("unlockedFormula", ignored_range) if self.ignored_errors.get("empty_cell_reference"): ignored_range = self.ignored_errors["empty_cell_reference"] self._write_ignored_error("emptyCellReference", ignored_range) if self.ignored_errors.get("list_data_validation"): ignored_range = self.ignored_errors["list_data_validation"] self._write_ignored_error("listDataValidation", ignored_range) if self.ignored_errors.get("calculated_column"): ignored_range = self.ignored_errors["calculated_column"] self._write_ignored_error("calculatedColumn", ignored_range) if self.ignored_errors.get("two_digit_text_year"): ignored_range = self.ignored_errors["two_digit_text_year"] self._write_ignored_error("twoDigitTextYear", ignored_range) self._xml_end_tag("ignoredErrors") def _write_ignored_error(self, error_type, ignored_range): # Write the element. attributes = [ ("sqref", ignored_range), (error_type, 1), ] self._xml_empty_tag("ignoredError", attributes)