AF2C
/
CCR
23
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
CCR/.venv/lib/python3.12/site-packages/xlsxwriter/worksheet.py

8555 lines
284 KiB
Python
Raw Permalink Blame History

###############################################################################
#
# 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
# Package imports.
from . import xmlwriter
from .drawing import Drawing
from .exceptions import DuplicateTableName, OverlappingRange
from .format import Format
from .shape import Shape
from .utility import (
_datetime_to_excel_datetime,
_get_image_properties,
_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 = 0
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 = 0
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.background_bytes = False
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)
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)
)
):
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)
# 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
# Set the displayed string to the URL unless defined by the user.
if string is None:
string = url
# Default to external link type such as 'http://' or 'external:'.
link_type = 1
# Remove the URI scheme from internal links.
if url.startswith("internal:"):
url = url.replace("internal:", "")
string = string.replace("internal:", "")
link_type = 2
# Remove the URI scheme from external links and change the directory
# separator from Unix to Dos.
external = False
if url.startswith("external:"):
url = url.replace("external:", "")
url = url.replace("/", "\\")
string = string.replace("external:", "")
string = string.replace("/", "\\")
external = True
# Strip the mailto header.
string = string.replace("mailto:", "")
# Check that the string is < 32767 chars
str_error = 0
if len(string) > self.xls_strmax:
warn(
"Ignoring URL since it exceeds Excel's string limit of "
"32767 characters"
)
return -2
# Copy string for use in hyperlink elements.
url_str = string
# External links to URLs and to other Excel workbooks have slightly
# different characteristics that we have to account for.
if link_type == 1:
# Split url into the link and optional anchor/location.
if "#" in url:
url, url_str = url.split("#", 1)
else:
url_str = None
url = self._escape_url(url)
if url_str is not None and not external:
url_str = self._escape_url(url_str)
# Add the file:/// URI to the url for Windows style "C:/" link and
# Network shares.
if re.match(r"\w:", url) or re.match(r"\\", url):
url = "file:///" + url
# Convert a .\dir\file.xlsx link to dir\file.xlsx.
url = re.sub(r"^\.\\", "", url)
# Excel limits the escaped URL and location/anchor to 255 characters.
tmp_url_str = url_str or ""
max_url = self.max_url_length
if len(url) > max_url or len(tmp_url_str) > max_url:
warn(
f"Ignoring URL '{url}' with link or location/anchor > {max_url} "
f"characters since it exceeds Excel's limit for URLs."
)
return -3
# Check the limit of URLs per worksheet.
self.hlink_count += 1
if self.hlink_count > 65530:
warn(
f"Ignoring URL '{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, string, cell_format)
# Store the hyperlink data in a separate structure.
self.hyperlinks[row][col] = {
"link_type": link_type,
"url": url,
"str": url_str,
"tip": tip,
}
return str_error
@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 <r> 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, filename, 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).
filename: Path and filename for in supported formats.
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
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)
url = options.get("url", None)
tip = options.get("tip", None)
anchor = options.get("object_position", 2)
image_data = options.get("image_data", None)
description = options.get("description", None)
decorative = options.get("decorative", False)
# For backward compatibility with older parameter name.
anchor = options.get("positioning", anchor)
if not image_data and not os.path.exists(filename):
warn(f"Image file '{filename}' not found.")
return -1
self.images.append(
[
row,
col,
filename,
x_offset,
y_offset,
x_scale,
y_scale,
url,
tip,
anchor,
image_data,
description,
decorative,
]
)
return 0
@convert_cell_args
def embed_image(self, row, col, filename, options=None):
"""
Embed an image in a worksheet cell.
Args:
row: The cell row (zero indexed).
col: The cell column (zero indexed).
filename: Path and filename for in supported formats.
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 = {}
url = options.get("url", None)
tip = options.get("tip", None)
cell_format = options.get("cell_format", None)
image_data = options.get("image_data", None)
description = options.get("description", None)
decorative = options.get("decorative", False)
if not image_data and not os.path.exists(filename):
warn(f"Image file '{filename}' not found.")
return -1
if url:
if cell_format is None:
cell_format = self.default_url_format
self.ignore_write_string = True
self.write_url(row, col, url, cell_format, None, tip)
self.ignore_write_string = False
# Get the image properties, for the type and checksum.
(
image_type,
_,
_,
_,
_,
_,
digest,
) = _get_image_properties(filename, image_data)
image = [filename, image_type, image_data, description, decorative]
image_index = self.embedded_images.get_image_index(image, digest)
# 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.
"""
if options is None:
options = {}
# 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 = 1
self.has_comments = 1
# Store the options of the cell comment, to process on file close.
self.comments[row][col] = [row, col, comment, options]
return 0
def show_comments(self):
"""
Make any comments in the worksheet visible.
Args:
None.
Returns:
Nothing.
"""
self.comments_visible = 1
def set_background(self, filename, is_byte_stream=False):
"""
Set a background image for a worksheet.
Args:
filename: Path and filename for in supported formats.
is_byte_stream: File is a stream of bytes.
Returns:
0: Success.
-1: Image file not found.
"""
if not is_byte_stream and not os.path.exists(filename):
warn(f"Image file '{filename}' not found.")
return -1
self.background_bytes = is_byte_stream
self.background_image = filename
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 = 1
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 = {}
button = self._button_params(row, col, options)
self.buttons_list.append(button)
self.has_vml = 1
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"):
self.header_images.append(
[options.get("image_left"), options.get("image_data_left"), "LH"]
)
if options.get("image_center"):
self.header_images.append(
[options.get("image_center"), options.get("image_data_center"), "CH"]
)
if options.get("image_right"):
self.header_images.append(
[options.get("image_right"), options.get("image_data_right"), "RH"]
)
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"):
self.footer_images.append(
[options.get("image_left"), options.get("image_data_left"), "LF"]
)
if options.get("image_center"):
self.footer_images.append(
[options.get("image_center"), options.get("image_data_center"), "CF"]
)
if options.get("image_right"):
self.footer_images.append(
[options.get("image_right"), options.get("image_data_right"), "RF"]
)
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 _prepare_image(
self,
index,
image_id,
drawing_id,
width,
height,
name,
image_type,
x_dpi,
y_dpi,
digest,
):
# Set up images/drawings.
drawing_type = 2
(
row,
col,
_,
x_offset,
y_offset,
x_scale,
y_scale,
url,
tip,
anchor,
_,
description,
decorative,
) = self.images[index]
width *= x_scale
height *= y_scale
# Scale by non 96dpi resolutions.
width *= 96.0 / x_dpi
height *= 96.0 / y_dpi
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
drawing_object = drawing._add_drawing_object()
drawing_object["type"] = drawing_type
drawing_object["dimensions"] = dimensions
drawing_object["width"] = width
drawing_object["height"] = height
drawing_object["description"] = name
drawing_object["shape"] = None
drawing_object["anchor"] = anchor
drawing_object["rel_index"] = 0
drawing_object["url_rel_index"] = 0
drawing_object["tip"] = tip
drawing_object["decorative"] = decorative
if description is not None:
drawing_object["description"] = description
if url:
target = None
rel_type = "/hyperlink"
target_mode = "External"
if re.match("(ftp|http)s?://", url):
target = self._escape_url(url)
if re.match("^mailto:", url):
target = self._escape_url(url)
if re.match("external:", url):
target = url.replace("external:", "")
target = self._escape_url(target)
# Additional escape not required in worksheet hyperlinks.
target = target.replace("#", "%23")
if re.match(r"\w:", target) or re.match(r"\\", target):
target = "file:///" + target
else:
target = target.replace("\\", "/")
if re.match("internal:", url):
target = url.replace("internal:", "#")
target_mode = None
if target is not None:
if len(target) > self.max_url_length:
warn(
f"Ignoring URL '{url}' with link and/or anchor > "
f"{self.max_url_length} characters since it exceeds "
f"Excel's limit for URLs."
)
else:
if not self.drawing_rels.get(url):
self.drawing_links.append([rel_type, target, target_mode])
drawing_object["url_rel_index"] = self._get_drawing_rel_index(url)
if not self.drawing_rels.get(digest):
self.drawing_links.append(
["/image", "../media/image" + str(image_id) + "." + image_type]
)
drawing_object["rel_index"] = self._get_drawing_rel_index(digest)
def _prepare_shape(self, index, drawing_id):
# Set up shapes/drawings.
drawing_type = 3
(
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 = drawing._add_drawing_object()
drawing_object["type"] = drawing_type
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["url_rel_index"] = 0
drawing_object["tip"] = options.get("tip")
drawing_object["decorative"] = decorative
url = options.get("url", None)
if url:
target = None
rel_type = "/hyperlink"
target_mode = "External"
if re.match("(ftp|http)s?://", url):
target = self._escape_url(url)
if re.match("^mailto:", url):
target = self._escape_url(url)
if re.match("external:", url):
target = url.replace("external:", "file:///")
target = self._escape_url(target)
# Additional escape not required in worksheet hyperlinks.
target = target.replace("#", "%23")
if re.match("internal:", url):
target = url.replace("internal:", "#")
target_mode = None
if target is not None:
if len(target) > self.max_url_length:
warn(
f"Ignoring URL '{url}' with link and/or anchor > "
f"{self.max_url_length} characters since it exceeds "
f"Excel's limit for URLs."
)
else:
if not self.drawing_rels.get(url):
self.drawing_links.append([rel_type, target, target_mode])
drawing_object["url_rel_index"] = self._get_drawing_rel_index(url)
def _prepare_header_image(
self, image_id, width, height, name, image_type, position, x_dpi, y_dpi, digest
):
# Set up an image without a drawing object for header/footer images.
# Strip the extension from the filename.
name = re.sub(r"\..*$", "", name)
if not self.vml_drawing_rels.get(digest):
self.vml_drawing_links.append(
["/image", "../media/image" + str(image_id) + "." + image_type]
)
ref_id = self._get_vml_drawing_rel_index(digest)
self.header_images_list.append(
[width, height, name, position, x_dpi, y_dpi, ref_id]
)
def _prepare_background(self, image_id, image_type):
# Set up an image without a drawing object for backgrounds.
self.external_background_links.append(
["/image", "../media/image" + str(image_id) + "." + image_type]
)
def _prepare_chart(self, index, chart_id, drawing_id):
# Set up chart/drawings.
drawing_type = 1
(
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 = drawing._add_drawing_object()
drawing_object["type"] = drawing_type
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["url_rel_index"] = 0
drawing_object["tip"] = None
drawing_object["description"] = description
drawing_object["decorative"] = decorative
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_params(self, row, col, string, options):
# This method handles the additional optional parameters to
# write_comment() as well as calculating the comment object
# position and vertices.
default_width = 128
default_height = 74
anchor = 0
params = {
"author": None,
"color": "#ffffe1",
"start_cell": None,
"start_col": None,
"start_row": None,
"visible": None,
"width": default_width,
"height": default_height,
"x_offset": None,
"x_scale": 1,
"y_offset": None,
"y_scale": 1,
"font_name": "Tahoma",
"font_size": 8,
"font_family": 2,
}
# Overwrite the defaults with any user supplied values. Incorrect or
# misspelled parameters are silently ignored.
for key in options.keys():
params[key] = options[key]
# Ensure that a width and height have been set.
if not params["width"]:
params["width"] = default_width
if not params["height"]:
params["height"] = default_height
# Set the comment background color.
params["color"] = _xl_color(params["color"]).lower()
# Convert from Excel XML style color to XML html style color.
params["color"] = params["color"].replace("ff", "#", 1)
# Convert a cell reference to a row and column.
if params["start_cell"] is not None:
(start_row, start_col) = xl_cell_to_rowcol(params["start_cell"])
params["start_row"] = start_row
params["start_col"] = start_col
# Set the default start cell and offsets for the comment. These are
# generally fixed in relation to the parent cell. However there are
# some edge cases for cells at the, er, edges.
row_max = self.xls_rowmax
col_max = self.xls_colmax
if params["start_row"] is None:
if row == 0:
params["start_row"] = 0
elif row == row_max - 3:
params["start_row"] = row_max - 7
elif row == row_max - 2:
params["start_row"] = row_max - 6
elif row == row_max - 1:
params["start_row"] = row_max - 5
else:
params["start_row"] = row - 1
if params["y_offset"] is None:
if row == 0:
params["y_offset"] = 2
elif row == row_max - 3:
params["y_offset"] = 16
elif row == row_max - 2:
params["y_offset"] = 16
elif row == row_max - 1:
params["y_offset"] = 14
else:
params["y_offset"] = 10
if params["start_col"] is None:
if col == col_max - 3:
params["start_col"] = col_max - 6
elif col == col_max - 2:
params["start_col"] = col_max - 5
elif col == col_max - 1:
params["start_col"] = col_max - 4
else:
params["start_col"] = col + 1
if params["x_offset"] is None:
if col == col_max - 3:
params["x_offset"] = 49
elif col == col_max - 2:
params["x_offset"] = 49
elif col == col_max - 1:
params["x_offset"] = 49
else:
params["x_offset"] = 15
# Scale the size of the comment box if required.
if params["x_scale"]:
params["width"] = params["width"] * params["x_scale"]
if params["y_scale"]:
params["height"] = params["height"] * params["y_scale"]
# Round the dimensions to the nearest pixel.
params["width"] = int(0.5 + params["width"])
params["height"] = int(0.5 + params["height"])
# Calculate the positions of the comment object.
vertices = self._position_object_pixels(
params["start_col"],
params["start_row"],
params["x_offset"],
params["y_offset"],
params["width"],
params["height"],
anchor,
)
# Add the width and height for VML.
vertices.append(params["width"])
vertices.append(params["height"])
return [
row,
col,
string,
params["author"],
params["visible"],
params["color"],
params["font_name"],
params["font_size"],
params["font_family"],
] + [vertices]
def _button_params(self, row, col, options):
# This method handles the parameters passed to insert_button() as well
# as calculating the button object position and vertices.
default_height = self.default_row_pixels
default_width = self.default_col_pixels
anchor = 0
button_number = 1 + len(self.buttons_list)
button = {"row": row, "col": col, "font": {}}
params = {}
# Overwrite the defaults with any user supplied values. Incorrect or
# misspelled parameters are silently ignored.
for key in options.keys():
params[key] = options[key]
# Set the button caption.
caption = params.get("caption")
# Set a default caption if none was specified by user.
if caption is None:
caption = f"Button {button_number}"
button["font"]["caption"] = caption
# Set the macro name.
if params.get("macro"):
button["macro"] = "[0]!" + params["macro"]
else:
button["macro"] = f"[0]!Button{button_number}_Click"
# Set the alt text for the button.
button["description"] = params.get("description")
# Ensure that a width and height have been set.
params["width"] = params.get("width", default_width)
params["height"] = params.get("height", default_height)
# Set the x/y offsets.
params["x_offset"] = params.get("x_offset", 0)
params["y_offset"] = params.get("y_offset", 0)
# Scale the size of the button if required.
params["width"] = params["width"] * params.get("x_scale", 1)
params["height"] = params["height"] * params.get("y_scale", 1)
# Round the dimensions to the nearest pixel.
params["width"] = int(0.5 + params["width"])
params["height"] = int(0.5 + params["height"])
params["start_row"] = row
params["start_col"] = col
# Calculate the positions of the button object.
vertices = self._position_object_pixels(
params["start_col"],
params["start_row"],
params["x_offset"],
params["y_offset"],
params["width"],
params["height"],
anchor,
)
# Add the width and height for VML.
vertices.append(params["width"])
vertices.append(params["height"])
button["vertices"] = vertices
return button
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:
user_options = self.comments[row][col]
params = self._comment_params(*user_options)
self.comments[row][col] = params
# Set comment visibility if required and not user defined.
if self.comments_visible:
if self.comments[row][col][4] is None:
self.comments[row][col][4] = 1
# Set comment author if not already user defined.
if self.comments[row][col][3] is None:
self.comments[row][col][3] = self.comments_author
comments.append(self.comments[row][col])
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 <font> 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 <vertAlign> font sub-element.
attributes = [("val", val)]
self.rstring._xml_empty_tag("vertAlign", attributes)
def _write_rstring_color(self, name, value):
# Write the <color> 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 <worksheet> 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 <dimension> 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 <sheetViews> 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 <sheetViews> 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 <sheetFormatPr> 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 <cols> element and <col> 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 <col> 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 <sheetData> 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 <sheetData> 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 <pageMargins> 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 <pageSetup> element.
#
# The following is an example taken from Excel.
#
# <pageSetup
# paperSize="9"
# scale="110"
# fitToWidth="2"
# fitToHeight="2"
# pageOrder="overThenDown"
# orientation="portrait"
# blackAndWhite="1"
# draft="1"
# horizontalDpi="200"
# verticalDpi="200"
# r:id="rId1"
# />
#
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 <printOptions> 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 <headerFooter> 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 <headerFooter> element.
self._xml_data_element("oddHeader", self.header)
def _write_odd_footer(self):
# Write the <headerFooter> 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 <row> 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 <row> 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 <row> element.
self._write_row(row, spans, properties, empty_row=True)
def _write_cell(self, row, col, cell):
# Write the <cell> 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("<r>") and string.endswith("</r>"):
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 <v> 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 <f> element.
attributes = [("t", "array"), ("ref", cell_range)]
self._xml_data_element("f", formula, attributes)
def _write_sheet_pr(self):
# Write the <sheetPr> 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 <pageSetUpPr> element.
if not self.fit_page:
return
attributes = [("fitToPage", 1)]
self._xml_empty_tag("pageSetUpPr", attributes)
def _write_tab_color(self):
# Write the <tabColor> 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 <outlinePr> 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 <rowBreaks> 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 <colBreaks> 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 <brk> element.
attributes = [("id", brk_id), ("max", brk_max), ("man", 1)]
self._xml_empty_tag("brk", attributes)
def _write_merge_cells(self):
# Write the <mergeCells> 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 <mergeCell> 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
# <hyperlinks> element. The attributes are different for internal
# and external links.
hlink_refs = []
display = None
# 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
# 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.
link = self.hyperlinks[row_num][col_num]
link_type = link["link_type"]
# If the cell 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":
display = link["url"]
if link_type == 1:
# External link with rel file relationship.
self.rel_count += 1
hlink_refs.append(
[
link_type,
row_num,
col_num,
self.rel_count,
link["str"],
display,
link["tip"],
]
)
# Links for use by the packager.
self.external_hyper_links.append(
["/hyperlink", link["url"], "External"]
)
else:
# Internal link with rel file relationship.
hlink_refs.append(
[
link_type,
row_num,
col_num,
link["url"],
link["str"],
link["tip"],
]
)
# Write the hyperlink elements.
self._xml_start_tag("hyperlinks")
for args in hlink_refs:
link_type = args.pop(0)
if link_type == 1:
self._write_hyperlink_external(*args)
elif link_type == 2:
self._write_hyperlink_internal(*args)
self._xml_end_tag("hyperlinks")
def _write_hyperlink_external(
self, row, col, id_num, location=None, display=None, tooltip=None
):
# Write the <hyperlink> 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 location is not None:
attributes.append(("location", location))
if display is not None:
attributes.append(("display", display))
if tooltip is not None:
attributes.append(("tooltip", tooltip))
self._xml_empty_tag("hyperlink", attributes)
def _write_hyperlink_internal(
self, row, col, location=None, display=None, tooltip=None
):
# Write the <hyperlink> element for internal links.
ref = xl_rowcol_to_cell(row, col)
attributes = [("ref", ref), ("location", location)]
if tooltip is not None:
attributes.append(("tooltip", tooltip))
attributes.append(("display", display))
self._xml_empty_tag("hyperlink", attributes)
def _write_auto_filter(self):
# Write the <autoFilter> 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 <filterColumn> 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 <filters> 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 <filter> element.
attributes = [("val", val)]
self._xml_empty_tag("filter", attributes)
def _write_custom_filters(self, tokens):
# Write the <customFilters> 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 <customFilter> 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 <sheetProtection> 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 <protectedRanges> 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 <protectedRange> 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 <drawing> elements.
if not self.drawing:
return
self.rel_count += 1
self._write_drawing(self.rel_count)
def _write_drawing(self, drawing_id):
# Write the <drawing> 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 <legacyDrawing> 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 <legacyDrawingHF> 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 <picture> 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 <dataValidations> 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 <dataValidation> 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 <formula1> 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 <formula2> 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 <conditionalFormatting> 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 <cfRule> 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 <formula> 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 <colorScale> 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 <dataBar> 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 <extLst> 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 <iconSet> 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 <cfvo> 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 <color> element.
attributes = [(name, value)]
self._xml_empty_tag("color", attributes)
def _write_selections(self):
# Write the <selection> elements.
for selection in self.selections:
self._write_selection(*selection)
def _write_selection(self, pane, active_cell, sqref):
# Write the <selection> 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 <pane> 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 <pane> 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 <pane> element for split panes.
attributes = []
has_selection = 0
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 = 1
# 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 <tableParts> 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 <tablePart> 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 <extLst> 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 <x14:conditionalFormatting> 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 <x14:cfRule> 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 <x14:dataBar> 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 <x14:cfvo> 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 <x14:borderColor> element.
attributes = [("rgb", rgb)]
self._xml_empty_tag("x14:borderColor", attributes)
def _write_x14_negative_fill_color(self, rgb):
# Write the <x14:negativeFillColor> element.
attributes = [("rgb", rgb)]
self._xml_empty_tag("x14:negativeFillColor", attributes)
def _write_x14_negative_border_color(self, rgb):
# Write the <x14:negativeBorderColor> element.
attributes = [("rgb", rgb)]
self._xml_empty_tag("x14:negativeBorderColor", attributes)
def _write_x14_axis_color(self, rgb):
# Write the <x14:axisColor> 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 <x14:sparklines> element and <x14:sparkline> 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 <ext> 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 <x14:sparklineGroups> 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 <x14:sparklineGroup> element.
#
# Example for order.
#
# <x14:sparklineGroup
# manualMax="0"
# manualMin="0"
# lineWeight="2.25"
# type="column"
# dateAxis="1"
# displayEmptyCellsAs="span"
# markers="1"
# high="1"
# low="1"
# first="1"
# last="1"
# negative="1"
# displayXAxis="1"
# displayHidden="1"
# minAxisType="custom"
# maxAxisType="custom"
# rightToLeft="1">
#
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 <x14:colorSeries> element.
self._write_spark_color("x14:colorSeries", color)
def _write_color_negative(self, color):
# Write the <x14:colorNegative> element.
self._write_spark_color("x14:colorNegative", color)
def _write_color_axis(self):
# Write the <x14:colorAxis> element.
self._write_spark_color("x14:colorAxis", {"rgb": "FF000000"})
def _write_color_markers(self, color):
# Write the <x14:colorMarkers> element.
self._write_spark_color("x14:colorMarkers", color)
def _write_color_first(self, color):
# Write the <x14:colorFirst> element.
self._write_spark_color("x14:colorFirst", color)
def _write_color_last(self, color):
# Write the <x14:colorLast> element.
self._write_spark_color("x14:colorLast", color)
def _write_color_high(self, color):
# Write the <x14:colorHigh> element.
self._write_spark_color("x14:colorHigh", color)
def _write_color_low(self, color):
# Write the <x14:colorLow> element.
self._write_spark_color("x14:colorLow", color)
def _write_phonetic_pr(self):
# Write the <phoneticPr> element.
attributes = [
("fontId", "0"),
("type", "noConversion"),
]
self._xml_empty_tag("phoneticPr", attributes)
def _write_ignored_errors(self):
# Write the <ignoredErrors> 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 <ignoredError> element.
attributes = [
("sqref", ignored_range),
(error_type, 1),
]
self._xml_empty_tag("ignoredError", attributes)
Reference in New Issue View Git Blame Copy Permalink