Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.11/site-packages/xlsxwriter/worksheet.py: 23%
Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1###############################################################################
2#
3# Worksheet - A class for writing the Excel XLSX Worksheet file.
4#
5# SPDX-License-Identifier: BSD-2-Clause
6#
7# Copyright (c) 2013-2026, John McNamara, jmcnamara@cpan.org
8#
10# pylint: disable=too-many-return-statements
12# Standard packages.
13import datetime
14import math
15import os
16import re
17import tempfile
18from collections import defaultdict, namedtuple
19from dataclasses import dataclass
20from decimal import Decimal
21from fractions import Fraction
22from functools import wraps
23from io import BytesIO, StringIO
24from math import isinf, isnan
25from typing import (
26 TYPE_CHECKING,
27 Any,
28 Callable,
29 Dict,
30 List,
31 Literal,
32 Optional,
33 TypeVar,
34 Union,
35)
36from warnings import warn
38# Package imports.
39from xlsxwriter import xmlwriter
40from xlsxwriter.chart import Chart
41from xlsxwriter.color import Color
42from xlsxwriter.comments import CommentType
43from xlsxwriter.drawing import Drawing, DrawingInfo, DrawingTypes
44from xlsxwriter.exceptions import DuplicateTableName, OverlappingRange
45from xlsxwriter.format import Format
46from xlsxwriter.image import Image
47from xlsxwriter.shape import Shape
48from xlsxwriter.url import Url, UrlTypes
49from xlsxwriter.utility import (
50 _datetime_to_excel_datetime,
51 _get_sparkline_style,
52 _has_invalid_name_chars,
53 _preserve_whitespace,
54 _supported_datetime,
55 _utf16_len,
56 _utf16_truncate,
57 quote_sheetname,
58 xl_cell_to_rowcol,
59 xl_col_to_name,
60 xl_pixel_width,
61 xl_range,
62 xl_rowcol_to_cell,
63 xl_rowcol_to_cell_fast,
64)
65from xlsxwriter.vml import ButtonType
66from xlsxwriter.xmlwriter import XMLwriter
68if TYPE_CHECKING:
69 from typing_extensions import Concatenate, ParamSpec, Protocol, overload
71 ReturnTypeT_co = TypeVar("ReturnTypeT_co", covariant=True)
72 P = ParamSpec("P")
74 class CellMethod(Protocol[P, ReturnTypeT_co]):
75 """Overloads to support cell notation."""
77 @overload
78 def __call__(
79 self, row: int, col: int, /, *args: P.args, **kwargs: P.kwargs
80 ) -> ReturnTypeT_co: ...
82 @overload
83 def __call__(
84 self, cell: str, /, *args: P.args, **kwargs: P.kwargs
85 ) -> ReturnTypeT_co: ...
87 class RangeMethod(Protocol[P, ReturnTypeT_co]):
88 """Overloads to support range notation."""
90 @overload
91 def __call__(
92 self,
93 first_row: int,
94 first_col: int,
95 last_row: int,
96 last_col: int,
97 /,
98 *args: P.args,
99 **kwargs: P.kwargs,
100 ) -> ReturnTypeT_co: ...
102 @overload
103 def __call__(
104 self, cell_range: str, /, *args: P.args, **kwargs: P.kwargs
105 ) -> ReturnTypeT_co: ...
107 class ColumnMethod(Protocol[P, ReturnTypeT_co]):
108 """Overloads to support column range notation."""
110 @overload
111 def __call__(
112 self, first_col: int, last_col: int, /, *args: P.args, **kwargs: P.kwargs
113 ) -> ReturnTypeT_co: ...
115 @overload
116 def __call__(
117 self, col_range: str, /, *args: P.args, **kwargs: P.kwargs
118 ) -> ReturnTypeT_co: ...
121re_dynamic_function = re.compile(
122 r"""
123 \bANCHORARRAY\( |
124 \bBYCOL\( |
125 \bBYROW\( |
126 \bCHOOSECOLS\( |
127 \bCHOOSEROWS\( |
128 \bDROP\( |
129 \bEXPAND\( |
130 \bFILTER\( |
131 \bHSTACK\( |
132 \bLAMBDA\( |
133 \bMAKEARRAY\( |
134 \bMAP\( |
135 \bRANDARRAY\( |
136 \bREDUCE\( |
137 \bSCAN\( |
138 \bSEQUENCE\( |
139 \bSINGLE\( |
140 \bSORT\( |
141 \bSORTBY\( |
142 \bSWITCH\( |
143 \bTAKE\( |
144 \bTEXTSPLIT\( |
145 \bTOCOL\( |
146 \bTOROW\( |
147 \bUNIQUE\( |
148 \bVSTACK\( |
149 \bWRAPCOLS\( |
150 \bWRAPROWS\( |
151 \bXLOOKUP\(""",
152 re.VERBOSE,
153)
156###############################################################################
157#
158# Decorator functions.
159#
160###############################################################################
161def convert_cell_args(
162 method: "Callable[Concatenate[Any, int, int, P], ReturnTypeT_co]",
163) -> "CellMethod[P, ReturnTypeT_co]":
164 """
165 Decorator function to convert A1 notation in cell method calls
166 to the default row/col notation.
168 """
170 @wraps(method)
171 def cell_wrapper(self, *args, **kwargs):
172 try:
173 # First arg is an int, default to row/col notation.
174 if args:
175 first_arg = args[0]
176 int(first_arg)
177 except ValueError:
178 # First arg isn't an int, convert to A1 notation.
179 new_args = xl_cell_to_rowcol(first_arg)
180 args = new_args + args[1:]
182 return method(self, *args, **kwargs)
184 return cell_wrapper
187def convert_range_args(
188 method: "Callable[Concatenate[Any, int, int, int, int, P], ReturnTypeT_co]",
189) -> "RangeMethod[P, ReturnTypeT_co]":
190 """
191 Decorator function to convert A1 notation in range method calls
192 to the default row/col notation.
194 """
196 @wraps(method)
197 def cell_wrapper(self, *args, **kwargs):
198 try:
199 # First arg is an int, default to row/col notation.
200 if args:
201 int(args[0])
202 except ValueError:
203 # First arg isn't an int, convert to A1 notation.
204 if ":" in args[0]:
205 cell_1, cell_2 = args[0].split(":")
206 row_1, col_1 = xl_cell_to_rowcol(cell_1)
207 row_2, col_2 = xl_cell_to_rowcol(cell_2)
208 else:
209 row_1, col_1 = xl_cell_to_rowcol(args[0])
210 row_2, col_2 = row_1, col_1
212 new_args = [row_1, col_1, row_2, col_2]
213 new_args.extend(args[1:])
214 args = new_args
216 return method(self, *args, **kwargs)
218 return cell_wrapper
221def convert_column_args(
222 method: "Callable[Concatenate[Any, int, int, P], ReturnTypeT_co]",
223) -> "ColumnMethod[P, ReturnTypeT_co]":
224 """
225 Decorator function to convert A1 notation in columns method calls
226 to the default row/col notation.
228 """
230 @wraps(method)
231 def column_wrapper(self, *args, **kwargs):
232 try:
233 # First arg is an int, default to row/col notation.
234 if args:
235 int(args[0])
236 except ValueError:
237 # First arg isn't an int, convert to A1 notation.
238 cell_1, cell_2 = [col + "1" for col in args[0].split(":")]
239 _, col_1 = xl_cell_to_rowcol(cell_1)
240 _, col_2 = xl_cell_to_rowcol(cell_2)
241 new_args = [col_1, col_2]
242 new_args.extend(args[1:])
243 args = new_args
245 return method(self, *args, **kwargs)
247 return column_wrapper
250###############################################################################
251#
252# Named tuples used for cell types.
253#
254###############################################################################
255CellBlankTuple = namedtuple("Blank", "format")
256CellErrorTuple = namedtuple("Error", "error, format, value")
257CellNumberTuple = namedtuple("Number", "number, format")
258CellStringTuple = namedtuple("String", "string, format")
259CellBooleanTuple = namedtuple("Boolean", "boolean, format")
260CellFormulaTuple = namedtuple("Formula", "formula, format, value")
261CellDatetimeTuple = namedtuple("Datetime", "number, format")
262CellRichStringTuple = namedtuple("RichString", "string, format, raw_string")
263CellArrayFormulaTuple = namedtuple(
264 "ArrayFormula", "formula, format, value, range, atype"
265)
267###############################################################################
268#
269# Helper classes and types.
270#
271###############################################################################
274@dataclass
275class ColumnInfo:
276 """Type to hold user modified properties for a column."""
278 width: Optional[int] = None
279 column_format: Optional["Format"] = None
280 hidden: bool = False
281 level: int = 0
282 collapsed: bool = False
283 autofit: bool = False
286@dataclass
287class RowInfo:
288 """Type to hold user modified properties for a row."""
290 height: Optional[int] = None
291 row_format: Optional["Format"] = None
292 hidden: bool = False
293 level: int = 0
294 collapsed: bool = False
297###############################################################################
298#
299# Worksheet Class definition.
300#
301###############################################################################
302class Worksheet(xmlwriter.XMLwriter):
303 """
304 A class for writing the Excel XLSX Worksheet file.
306 """
308 ###########################################################################
309 #
310 # Public API.
311 #
312 ###########################################################################
314 def __init__(self) -> None:
315 """
316 Constructor.
318 """
320 super().__init__()
322 self.name = None
323 self.index = None
324 self.str_table = None
325 self.palette = None
326 self.constant_memory = 0
327 self.tmpdir = None
328 self.is_chartsheet = False
330 self.fileclosed = 0
331 self.excel_version = 2007
332 self.excel2003_style = False
334 self.xls_rowmax = 1048576
335 self.xls_colmax = 16384
336 self.xls_strmax = 32767
337 self.dim_rowmin = None
338 self.dim_rowmax = None
339 self.dim_colmin = None
340 self.dim_colmax = None
342 self.col_info: Dict[int, ColumnInfo] = {}
343 self.row_info: Dict[int, RowInfo] = {}
344 self.default_row_height: int = 20
345 self.default_col_width: int = 64
346 self.cell_padding: int = 5
347 self.original_row_height: int = 20
348 self.max_digit_width: int = 7
349 self.max_col_width: int = 1790
350 self.default_date_width = 68
351 self.default_row_zeroed = 0
353 self.selections = []
354 self.hidden = 0
355 self.active = 0
356 self.tab_color = 0
357 self.top_left_cell = ""
359 self.panes = []
360 self.selected = 0
362 self.page_setup_changed = False
363 self.paper_size = 0
364 self.orientation = 1
366 self.print_options_changed = False
367 self.hcenter = False
368 self.vcenter = False
369 self.print_gridlines = False
370 self.screen_gridlines = True
371 self.print_headers = False
372 self.row_col_headers = False
374 self.header_footer_changed = False
375 self.header = ""
376 self.footer = ""
377 self.header_footer_aligns = True
378 self.header_footer_scales = True
379 self.header_images = []
380 self.footer_images = []
381 self.header_images_list = []
383 self.margin_left = 0.7
384 self.margin_right = 0.7
385 self.margin_top = 0.75
386 self.margin_bottom = 0.75
387 self.margin_header = 0.3
388 self.margin_footer = 0.3
390 self.repeat_row_range = ""
391 self.repeat_col_range = ""
392 self.print_area_range = ""
394 self.page_order = 0
395 self.black_white = 0
396 self.page_start = 0
398 self.fit_page = 0
399 self.fit_width = 0
400 self.fit_height = 0
402 self.hbreaks = []
403 self.vbreaks = []
405 self.protect_options = {}
406 self.protected_ranges = []
407 self.num_protected_ranges = 0
409 self.zoom = 100
410 self.zoom_scale_normal = True
411 self.zoom_to_fit = False
412 self.print_scale = 100
413 self.is_right_to_left = False
414 self.show_zeros = 1
416 self.outline_row_level = 0
417 self.outline_col_level = 0
418 self.outline_style = 0
419 self.outline_below = 1
420 self.outline_right = 1
421 self.outline_on = 1
422 self.outline_changed = False
424 self.table = defaultdict(dict)
425 self.merge = []
426 self.merged_cells = {}
427 self.table_cells = {}
428 self.row_spans = {}
430 self.has_vml = False
431 self.has_header_vml = False
432 self.has_comments = False
433 self.comments = defaultdict(dict)
434 self.comments_list = []
435 self.comments_author = ""
436 self.comments_visible = False
437 self.vml_shape_id = 1024
438 self.buttons_list = []
439 self.vml_header_id = 0
441 self.autofilter_area = ""
442 self.autofilter_ref = None
443 self.filter_range = [0, 9]
444 self.filter_on = 0
445 self.filter_cols = {}
446 self.filter_type = {}
447 self.filter_cells = {}
449 self.row_sizes = {}
450 self.col_size_changed = False
452 # The previously calculated (index, pixel offset) used to position
453 # objects, to avoid re-summing the sizes from the first row/col.
454 self.prev_col_offset = (0, 0)
455 self.prev_row_offset = (0, 0)
457 self.rel_count = 0
458 self.hlink_count = 0
459 self.external_hyper_links = []
460 self.external_drawing_links = []
461 self.external_comment_links = []
462 self.external_vml_links = []
463 self.external_table_links = []
464 self.external_background_links = []
465 self.drawing_links = []
466 self.vml_drawing_links = []
467 self.charts = []
468 self.images = []
469 self.tables = []
470 self.sparklines = []
471 self.shapes = []
472 self.shape_hash = {}
473 self.drawing = 0
474 self.drawing_rels = {}
475 self.drawing_rels_id = 0
476 self.vml_drawing_rels = {}
477 self.vml_drawing_rels_id = 0
478 self.background_image = None
480 self.rstring = ""
481 self.previous_row = 0
483 self.validations = []
484 self.cond_formats = {}
485 self.data_bars_2010 = []
486 self.use_data_bars_2010 = False
487 self.dxf_priority = 1
488 self.page_view = 0
490 self.vba_codename = None
492 self.date_1904 = False
493 self.hyperlinks = defaultdict(dict)
495 self.strings_to_numbers = False
496 self.strings_to_urls = True
497 self.nan_inf_to_errors = False
498 self.strings_to_formulas = True
500 self.default_date_format = None
501 self.default_url_format = None
502 self.default_checkbox_format = None
503 self.workbook_add_format = None
504 self.remove_timezone = False
505 self.max_url_length = 2079
507 self.row_data_filename = None
508 self.row_data_fh = None
509 self.worksheet_meta = None
510 self.vml_data_id = None
511 self.vml_shape_id = None
513 self.row_data_filename = None
514 self.row_data_fh = None
515 self.row_data_fh_closed = False
517 self.vertical_dpi = 0
518 self.horizontal_dpi = 0
520 self.write_handlers = {}
522 self.ignored_errors = None
524 self.has_dynamic_arrays = False
525 self.use_future_functions = False
526 self.ignore_write_string = False
527 self.embedded_images = None
529 # Utility function for writing different types of strings.
530 def _write_token_as_string(self, token, row: int, col: int, *args):
531 # Map the data to the appropriate write_*() method.
532 if token == "":
533 return self._write_blank(row, col, *args)
535 if self.strings_to_formulas and token.startswith("="):
536 return self._write_formula(row, col, *args)
538 if self.strings_to_formulas and token.startswith("{=") and token.endswith("}"):
539 return self._write_formula(row, col, *args)
541 # pylint: disable=too-many-boolean-expressions
542 if (
543 ":" in token
544 and self.strings_to_urls
545 and (
546 re.match("(ftp|http)s?://", token)
547 or re.match("mailto:", token)
548 or re.match("(in|ex)ternal:", token)
549 or re.match("file://", token)
550 )
551 ):
552 return self._write_url(row, col, *args)
554 if self.strings_to_numbers:
555 try:
556 f = float(token)
557 if self.nan_inf_to_errors or (not isnan(f) and not isinf(f)):
558 return self._write_number(row, col, f, *args[1:])
559 except ValueError:
560 # Not a number, write as a string.
561 pass
563 return self._write_string(row, col, *args)
565 # We have a plain string.
566 return self._write_string(row, col, *args)
568 @convert_cell_args
569 def write(self, row: int, col: int, *args) -> Union[Literal[0, -1], Any]:
570 """
571 Write data to a worksheet cell by calling the appropriate write_*()
572 method based on the type of data being passed.
574 Args:
575 row: The cell row (zero indexed).
576 col: The cell column (zero indexed).
577 *args: Args to pass to sub functions.
579 Returns:
580 0: Success.
581 -1: Row or column is out of worksheet bounds.
582 other: Return value of called method.
584 """
585 return self._write(row, col, *args)
587 # Undecorated version of write().
588 def _write(self, row: int, col: int, *args):
589 # pylint: disable=raise-missing-from
590 # Check the number of args passed.
591 if not args:
592 raise TypeError("write() takes at least 4 arguments (3 given)")
594 # The first arg should be the token for all write calls.
595 token = args[0]
597 # Avoid isinstance() for better performance.
598 token_type = token.__class__
600 # Check for any user defined type handlers with callback functions.
601 if token_type in self.write_handlers:
602 write_handler = self.write_handlers[token_type]
603 function_return = write_handler(self, row, col, *args)
605 # If the return value is None then the callback has returned
606 # control to this function and we should continue as
607 # normal. Otherwise we return the value to the caller and exit.
608 if function_return is None:
609 pass
610 else:
611 return function_return
613 # Write None as a blank cell.
614 if token is None:
615 return self._write_blank(row, col, *args)
617 # Check for standard Python types.
618 if token_type is bool:
619 return self._write_boolean(row, col, *args)
621 if token_type in (float, int, Decimal, Fraction):
622 return self._write_number(row, col, *args)
624 if token_type is str:
625 return self._write_token_as_string(token, row, col, *args)
627 if token_type in (
628 datetime.datetime,
629 datetime.date,
630 datetime.time,
631 datetime.timedelta,
632 ):
633 return self._write_datetime(row, col, *args)
635 # Resort to isinstance() for subclassed primitives.
637 # Write number types.
638 if isinstance(token, (float, int, Decimal, Fraction)):
639 return self._write_number(row, col, *args)
641 # Write string types.
642 if isinstance(token, str):
643 return self._write_token_as_string(token, row, col, *args)
645 # Write boolean types.
646 if isinstance(token, bool):
647 return self._write_boolean(row, col, *args)
649 # Write datetime objects.
650 if _supported_datetime(token):
651 return self._write_datetime(row, col, *args)
653 # Write Url type.
654 if isinstance(token, Url):
655 return self._write_url(row, col, *args)
657 # We haven't matched a supported type. Try float.
658 try:
659 f = float(token)
660 return self._write_number(row, col, f, *args[1:])
661 except ValueError:
662 pass
663 except TypeError:
664 raise TypeError(f"Unsupported type {type(token)} in write()")
666 # Finally try string.
667 try:
668 str(token)
669 return self._write_string(row, col, *args)
670 except ValueError:
671 raise TypeError(f"Unsupported type {type(token)} in write()")
673 @convert_cell_args
674 def write_string(
675 self, row: int, col: int, string: str, cell_format: Optional[Format] = None
676 ) -> Literal[0, -1, -2]:
677 """
678 Write a string to a worksheet cell.
680 Args:
681 row: The cell row (zero indexed).
682 col: The cell column (zero indexed).
683 string: Cell data. Str.
684 format: An optional cell Format object.
686 Returns:
687 0: Success.
688 -1: Row or column is out of worksheet bounds.
689 -2: String truncated to 32k characters.
691 """
692 return self._write_string(row, col, string, cell_format)
694 # Undecorated version of write_string().
695 def _write_string(
696 self, row: int, col: int, string: str, cell_format: Optional[Format] = None
697 ) -> Literal[0, -1, -2]:
698 str_error = 0
700 # Check that row and col are valid and store max and min values.
701 if self._check_dimensions(row, col):
702 return -1
704 # Check that the string is < 32767 UTF-16 units. A string can't exceed
705 # the limit if it is less than half of it, so skip the slower check.
706 if len(string) > self.xls_strmax // 2 and _utf16_len(string) > self.xls_strmax:
707 string = _utf16_truncate(string, self.xls_strmax)
708 str_error = -2
710 # Write a shared string or an in-line string in constant_memory mode.
711 if not self.constant_memory:
712 string_index = self.str_table._get_shared_string_index(string)
713 else:
714 string_index = string
716 # Write previous row if in in-line string constant_memory mode.
717 if self.constant_memory and row > self.previous_row:
718 self._write_single_row(row)
720 # Store the cell data in the worksheet data table.
721 self.table[row][col] = CellStringTuple(string_index, cell_format)
723 return str_error
725 @convert_cell_args
726 def write_number(
727 self,
728 row: int,
729 col: int,
730 number: Union[float, Fraction],
731 cell_format: Optional[Format] = None,
732 ) -> Literal[0, -1]:
733 """
734 Write a number to a worksheet cell.
736 Args:
737 row: The cell row (zero indexed).
738 col: The cell column (zero indexed).
739 number: Cell data. Int or float.
740 cell_format: An optional cell Format object.
742 Returns:
743 0: Success.
744 -1: Row or column is out of worksheet bounds.
746 """
747 return self._write_number(row, col, number, cell_format)
749 # Undecorated version of write_number().
750 def _write_number(
751 self,
752 row: int,
753 col: int,
754 number: Union[float, Fraction],
755 cell_format: Optional[Format] = None,
756 ) -> Literal[0, -1]:
757 if isnan(number) or isinf(number):
758 if self.nan_inf_to_errors:
759 if isnan(number):
760 return self._write_formula(row, col, "#NUM!", cell_format, "#NUM!")
762 if number == math.inf:
763 return self._write_formula(row, col, "1/0", cell_format, "#DIV/0!")
765 if number == -math.inf:
766 return self._write_formula(row, col, "-1/0", cell_format, "#DIV/0!")
767 else:
768 raise TypeError(
769 "NAN/INF not supported in write_number() "
770 "without 'nan_inf_to_errors' Workbook() option"
771 )
773 if number.__class__ is Fraction:
774 number = float(number)
776 # Check that row and col are valid and store max and min values.
777 if self._check_dimensions(row, col):
778 return -1
780 # Write previous row if in in-line string constant_memory mode.
781 if self.constant_memory and row > self.previous_row:
782 self._write_single_row(row)
784 # Store the cell data in the worksheet data table.
785 self.table[row][col] = CellNumberTuple(number, cell_format)
787 return 0
789 @convert_cell_args
790 def write_blank(
791 self, row: int, col: int, blank: Any, cell_format: Optional[Format] = None
792 ):
793 """
794 Write a blank cell with formatting to a worksheet cell. The blank
795 token is ignored and the format only is written to the cell.
797 Args:
798 row: The cell row (zero indexed).
799 col: The cell column (zero indexed).
800 blank: Any value. It is ignored.
801 cell_format: An optional cell Format object.
803 Returns:
804 0: Success.
805 -1: Row or column is out of worksheet bounds.
807 """
808 return self._write_blank(row, col, blank, cell_format)
810 # Undecorated version of write_blank().
811 def _write_blank(
812 self, row: int, col: int, _, cell_format: Optional[Format] = None
813 ) -> Literal[0, -1]:
814 # Don't write a blank cell unless it has a format.
815 if cell_format is None:
816 return 0
818 # Check that row and col are valid and store max and min values.
819 if self._check_dimensions(row, col):
820 return -1
822 # Write previous row if in in-line string constant_memory mode.
823 if self.constant_memory and row > self.previous_row:
824 self._write_single_row(row)
826 # Store the cell data in the worksheet data table.
827 self.table[row][col] = CellBlankTuple(cell_format)
829 return 0
831 @convert_cell_args
832 def write_formula(
833 self,
834 row: int,
835 col: int,
836 formula: str,
837 cell_format: Optional[Format] = None,
838 value=0,
839 ) -> Literal[0, -1, -2]:
840 """
841 Write a formula to a worksheet cell.
843 Args:
844 row: The cell row (zero indexed).
845 col: The cell column (zero indexed).
846 formula: Cell formula.
847 cell_format: An optional cell Format object.
848 value: An optional value for the formula. Default is 0.
850 Returns:
851 0: Success.
852 -1: Row or column is out of worksheet bounds.
853 -2: Formula can't be None or empty.
855 """
856 # Check that row and col are valid and store max and min values.
857 return self._write_formula(row, col, formula, cell_format, value)
859 # Undecorated version of write_formula().
860 def _write_formula(
861 self,
862 row: int,
863 col: int,
864 formula: str,
865 cell_format: Optional[Format] = None,
866 value=0,
867 ) -> Literal[0, -1, -2]:
868 if self._check_dimensions(row, col):
869 return -1
871 if formula is None or formula == "":
872 warn("Formula can't be None or empty")
873 return -1
875 # Check for dynamic array functions.
876 if re_dynamic_function.search(formula):
877 return self.write_dynamic_array_formula(
878 row, col, row, col, formula, cell_format, value
879 )
881 # Hand off array formulas.
882 if formula.startswith("{") and formula.endswith("}"):
883 return self._write_array_formula(
884 row, col, row, col, formula, cell_format, value
885 )
887 # Modify the formula string, as needed.
888 formula = self._prepare_formula(formula)
890 # Write previous row if in in-line string constant_memory mode.
891 if self.constant_memory and row > self.previous_row:
892 self._write_single_row(row)
894 # Store the cell data in the worksheet data table.
895 self.table[row][col] = CellFormulaTuple(formula, cell_format, value)
897 return 0
899 @convert_range_args
900 def write_array_formula(
901 self,
902 first_row: int,
903 first_col: int,
904 last_row: int,
905 last_col: int,
906 formula: str,
907 cell_format: Optional[Format] = None,
908 value=0,
909 ) -> Literal[0, -1]:
910 """
911 Write a formula to a worksheet cell/range.
913 Args:
914 first_row: The first row of the cell range. (zero indexed).
915 first_col: The first column of the cell range.
916 last_row: The last row of the cell range. (zero indexed).
917 last_col: The last column of the cell range.
918 formula: Cell formula.
919 cell_format: An optional cell Format object.
920 value: An optional value for the formula. Default is 0.
922 Returns:
923 0: Success.
924 -1: Row or column is out of worksheet bounds.
926 """
927 # Check for dynamic array functions.
928 if re_dynamic_function.search(formula):
929 return self.write_dynamic_array_formula(
930 first_row, first_col, last_row, last_col, formula, cell_format, value
931 )
933 return self._write_array_formula(
934 first_row,
935 first_col,
936 last_row,
937 last_col,
938 formula,
939 cell_format,
940 value,
941 "static",
942 )
944 @convert_range_args
945 def write_dynamic_array_formula(
946 self,
947 first_row: int,
948 first_col: int,
949 last_row: int,
950 last_col: int,
951 formula: str,
952 cell_format: Optional[Format] = None,
953 value=0,
954 ) -> Literal[0, -1]:
955 """
956 Write a dynamic array formula to a worksheet cell/range.
958 Args:
959 first_row: The first row of the cell range. (zero indexed).
960 first_col: The first column of the cell range.
961 last_row: The last row of the cell range. (zero indexed).
962 last_col: The last column of the cell range.
963 formula: Cell formula.
964 cell_format: An optional cell Format object.
965 value: An optional value for the formula. Default is 0.
967 Returns:
968 0: Success.
969 -1: Row or column is out of worksheet bounds.
971 """
972 error = self._write_array_formula(
973 first_row,
974 first_col,
975 last_row,
976 last_col,
977 formula,
978 cell_format,
979 value,
980 "dynamic",
981 )
983 if error == 0:
984 self.has_dynamic_arrays = True
986 return error
988 # Utility method to strip equal sign and array braces from a formula and
989 # also expand out future and dynamic array formulas.
990 def _prepare_formula(self, formula, expand_future_functions=False):
991 # Remove array formula braces and the leading =.
992 if formula.startswith("{"):
993 formula = formula[1:]
994 if formula.startswith("="):
995 formula = formula[1:]
996 if formula.endswith("}"):
997 formula = formula[:-1]
999 # Check if formula is already expanded by the user.
1000 if "_xlfn." in formula:
1001 return formula
1003 # Expand dynamic formulas.
1004 formula = re.sub(r"\bANCHORARRAY\(", "_xlfn.ANCHORARRAY(", formula)
1005 formula = re.sub(r"\bBYCOL\(", "_xlfn.BYCOL(", formula)
1006 formula = re.sub(r"\bBYROW\(", "_xlfn.BYROW(", formula)
1007 formula = re.sub(r"\bCHOOSECOLS\(", "_xlfn.CHOOSECOLS(", formula)
1008 formula = re.sub(r"\bCHOOSEROWS\(", "_xlfn.CHOOSEROWS(", formula)
1009 formula = re.sub(r"\bDROP\(", "_xlfn.DROP(", formula)
1010 formula = re.sub(r"\bEXPAND\(", "_xlfn.EXPAND(", formula)
1011 formula = re.sub(r"\bFILTER\(", "_xlfn._xlws.FILTER(", formula)
1012 formula = re.sub(r"\bHSTACK\(", "_xlfn.HSTACK(", formula)
1013 formula = re.sub(r"\bLAMBDA\(", "_xlfn.LAMBDA(", formula)
1014 formula = re.sub(r"\bMAKEARRAY\(", "_xlfn.MAKEARRAY(", formula)
1015 formula = re.sub(r"\bMAP\(", "_xlfn.MAP(", formula)
1016 formula = re.sub(r"\bRANDARRAY\(", "_xlfn.RANDARRAY(", formula)
1017 formula = re.sub(r"\bREDUCE\(", "_xlfn.REDUCE(", formula)
1018 formula = re.sub(r"\bSCAN\(", "_xlfn.SCAN(", formula)
1019 formula = re.sub(r"\SINGLE\(", "_xlfn.SINGLE(", formula)
1020 formula = re.sub(r"\bSEQUENCE\(", "_xlfn.SEQUENCE(", formula)
1021 formula = re.sub(r"\bSORT\(", "_xlfn._xlws.SORT(", formula)
1022 formula = re.sub(r"\bSORTBY\(", "_xlfn.SORTBY(", formula)
1023 formula = re.sub(r"\bSWITCH\(", "_xlfn.SWITCH(", formula)
1024 formula = re.sub(r"\bTAKE\(", "_xlfn.TAKE(", formula)
1025 formula = re.sub(r"\bTEXTSPLIT\(", "_xlfn.TEXTSPLIT(", formula)
1026 formula = re.sub(r"\bTOCOL\(", "_xlfn.TOCOL(", formula)
1027 formula = re.sub(r"\bTOROW\(", "_xlfn.TOROW(", formula)
1028 formula = re.sub(r"\bUNIQUE\(", "_xlfn.UNIQUE(", formula)
1029 formula = re.sub(r"\bVSTACK\(", "_xlfn.VSTACK(", formula)
1030 formula = re.sub(r"\bWRAPCOLS\(", "_xlfn.WRAPCOLS(", formula)
1031 formula = re.sub(r"\bWRAPROWS\(", "_xlfn.WRAPROWS(", formula)
1032 formula = re.sub(r"\bXLOOKUP\(", "_xlfn.XLOOKUP(", formula)
1034 if not self.use_future_functions and not expand_future_functions:
1035 return formula
1037 formula = re.sub(r"\bACOTH\(", "_xlfn.ACOTH(", formula)
1038 formula = re.sub(r"\bACOT\(", "_xlfn.ACOT(", formula)
1039 formula = re.sub(r"\bAGGREGATE\(", "_xlfn.AGGREGATE(", formula)
1040 formula = re.sub(r"\bARABIC\(", "_xlfn.ARABIC(", formula)
1041 formula = re.sub(r"\bARRAYTOTEXT\(", "_xlfn.ARRAYTOTEXT(", formula)
1042 formula = re.sub(r"\bBASE\(", "_xlfn.BASE(", formula)
1043 formula = re.sub(r"\bBETA.DIST\(", "_xlfn.BETA.DIST(", formula)
1044 formula = re.sub(r"\bBETA.INV\(", "_xlfn.BETA.INV(", formula)
1045 formula = re.sub(r"\bBINOM.DIST.RANGE\(", "_xlfn.BINOM.DIST.RANGE(", formula)
1046 formula = re.sub(r"\bBINOM.DIST\(", "_xlfn.BINOM.DIST(", formula)
1047 formula = re.sub(r"\bBINOM.INV\(", "_xlfn.BINOM.INV(", formula)
1048 formula = re.sub(r"\bBITAND\(", "_xlfn.BITAND(", formula)
1049 formula = re.sub(r"\bBITLSHIFT\(", "_xlfn.BITLSHIFT(", formula)
1050 formula = re.sub(r"\bBITOR\(", "_xlfn.BITOR(", formula)
1051 formula = re.sub(r"\bBITRSHIFT\(", "_xlfn.BITRSHIFT(", formula)
1052 formula = re.sub(r"\bBITXOR\(", "_xlfn.BITXOR(", formula)
1053 formula = re.sub(r"\bCEILING.MATH\(", "_xlfn.CEILING.MATH(", formula)
1054 formula = re.sub(r"\bCEILING.PRECISE\(", "_xlfn.CEILING.PRECISE(", formula)
1055 formula = re.sub(r"\bCHISQ.DIST.RT\(", "_xlfn.CHISQ.DIST.RT(", formula)
1056 formula = re.sub(r"\bCHISQ.DIST\(", "_xlfn.CHISQ.DIST(", formula)
1057 formula = re.sub(r"\bCHISQ.INV.RT\(", "_xlfn.CHISQ.INV.RT(", formula)
1058 formula = re.sub(r"\bCHISQ.INV\(", "_xlfn.CHISQ.INV(", formula)
1059 formula = re.sub(r"\bCHISQ.TEST\(", "_xlfn.CHISQ.TEST(", formula)
1060 formula = re.sub(r"\bCOMBINA\(", "_xlfn.COMBINA(", formula)
1061 formula = re.sub(r"\bCONCAT\(", "_xlfn.CONCAT(", formula)
1062 formula = re.sub(r"\bCONFIDENCE.NORM\(", "_xlfn.CONFIDENCE.NORM(", formula)
1063 formula = re.sub(r"\bCONFIDENCE.T\(", "_xlfn.CONFIDENCE.T(", formula)
1064 formula = re.sub(r"\bCOTH\(", "_xlfn.COTH(", formula)
1065 formula = re.sub(r"\bCOT\(", "_xlfn.COT(", formula)
1066 formula = re.sub(r"\bCOVARIANCE.P\(", "_xlfn.COVARIANCE.P(", formula)
1067 formula = re.sub(r"\bCOVARIANCE.S\(", "_xlfn.COVARIANCE.S(", formula)
1068 formula = re.sub(r"\bCSCH\(", "_xlfn.CSCH(", formula)
1069 formula = re.sub(r"\bCSC\(", "_xlfn.CSC(", formula)
1070 formula = re.sub(r"\bDAYS\(", "_xlfn.DAYS(", formula)
1071 formula = re.sub(r"\bDECIMAL\(", "_xlfn.DECIMAL(", formula)
1072 formula = re.sub(r"\bERF.PRECISE\(", "_xlfn.ERF.PRECISE(", formula)
1073 formula = re.sub(r"\bERFC.PRECISE\(", "_xlfn.ERFC.PRECISE(", formula)
1074 formula = re.sub(r"\bEXPON.DIST\(", "_xlfn.EXPON.DIST(", formula)
1075 formula = re.sub(r"\bF.DIST.RT\(", "_xlfn.F.DIST.RT(", formula)
1076 formula = re.sub(r"\bF.DIST\(", "_xlfn.F.DIST(", formula)
1077 formula = re.sub(r"\bF.INV.RT\(", "_xlfn.F.INV.RT(", formula)
1078 formula = re.sub(r"\bF.INV\(", "_xlfn.F.INV(", formula)
1079 formula = re.sub(r"\bF.TEST\(", "_xlfn.F.TEST(", formula)
1080 formula = re.sub(r"\bFILTERXML\(", "_xlfn.FILTERXML(", formula)
1081 formula = re.sub(r"\bFLOOR.MATH\(", "_xlfn.FLOOR.MATH(", formula)
1082 formula = re.sub(r"\bFLOOR.PRECISE\(", "_xlfn.FLOOR.PRECISE(", formula)
1083 formula = re.sub(
1084 r"\bFORECAST.ETS.CONFINT\(", "_xlfn.FORECAST.ETS.CONFINT(", formula
1085 )
1086 formula = re.sub(
1087 r"\bFORECAST.ETS.SEASONALITY\(", "_xlfn.FORECAST.ETS.SEASONALITY(", formula
1088 )
1089 formula = re.sub(r"\bFORECAST.ETS.STAT\(", "_xlfn.FORECAST.ETS.STAT(", formula)
1090 formula = re.sub(r"\bFORECAST.ETS\(", "_xlfn.FORECAST.ETS(", formula)
1091 formula = re.sub(r"\bFORECAST.LINEAR\(", "_xlfn.FORECAST.LINEAR(", formula)
1092 formula = re.sub(r"\bFORMULATEXT\(", "_xlfn.FORMULATEXT(", formula)
1093 formula = re.sub(r"\bGAMMA.DIST\(", "_xlfn.GAMMA.DIST(", formula)
1094 formula = re.sub(r"\bGAMMA.INV\(", "_xlfn.GAMMA.INV(", formula)
1095 formula = re.sub(r"\bGAMMALN.PRECISE\(", "_xlfn.GAMMALN.PRECISE(", formula)
1096 formula = re.sub(r"\bGAMMA\(", "_xlfn.GAMMA(", formula)
1097 formula = re.sub(r"\bGAUSS\(", "_xlfn.GAUSS(", formula)
1098 formula = re.sub(r"\bHYPGEOM.DIST\(", "_xlfn.HYPGEOM.DIST(", formula)
1099 formula = re.sub(r"\bIFNA\(", "_xlfn.IFNA(", formula)
1100 formula = re.sub(r"\bIFS\(", "_xlfn.IFS(", formula)
1101 formula = re.sub(r"\bIMAGE\(", "_xlfn.IMAGE(", formula)
1102 formula = re.sub(r"\bIMCOSH\(", "_xlfn.IMCOSH(", formula)
1103 formula = re.sub(r"\bIMCOT\(", "_xlfn.IMCOT(", formula)
1104 formula = re.sub(r"\bIMCSCH\(", "_xlfn.IMCSCH(", formula)
1105 formula = re.sub(r"\bIMCSC\(", "_xlfn.IMCSC(", formula)
1106 formula = re.sub(r"\bIMSECH\(", "_xlfn.IMSECH(", formula)
1107 formula = re.sub(r"\bIMSEC\(", "_xlfn.IMSEC(", formula)
1108 formula = re.sub(r"\bIMSINH\(", "_xlfn.IMSINH(", formula)
1109 formula = re.sub(r"\bIMTAN\(", "_xlfn.IMTAN(", formula)
1110 formula = re.sub(r"\bISFORMULA\(", "_xlfn.ISFORMULA(", formula)
1111 formula = re.sub(r"\bISOMITTED\(", "_xlfn.ISOMITTED(", formula)
1112 formula = re.sub(r"\bISOWEEKNUM\(", "_xlfn.ISOWEEKNUM(", formula)
1113 formula = re.sub(r"\bLET\(", "_xlfn.LET(", formula)
1114 formula = re.sub(r"\bLOGNORM.DIST\(", "_xlfn.LOGNORM.DIST(", formula)
1115 formula = re.sub(r"\bLOGNORM.INV\(", "_xlfn.LOGNORM.INV(", formula)
1116 formula = re.sub(r"\bMAXIFS\(", "_xlfn.MAXIFS(", formula)
1117 formula = re.sub(r"\bMINIFS\(", "_xlfn.MINIFS(", formula)
1118 formula = re.sub(r"\bMODE.MULT\(", "_xlfn.MODE.MULT(", formula)
1119 formula = re.sub(r"\bMODE.SNGL\(", "_xlfn.MODE.SNGL(", formula)
1120 formula = re.sub(r"\bMUNIT\(", "_xlfn.MUNIT(", formula)
1121 formula = re.sub(r"\bNEGBINOM.DIST\(", "_xlfn.NEGBINOM.DIST(", formula)
1122 formula = re.sub(r"\bNORM.DIST\(", "_xlfn.NORM.DIST(", formula)
1123 formula = re.sub(r"\bNORM.INV\(", "_xlfn.NORM.INV(", formula)
1124 formula = re.sub(r"\bNORM.S.DIST\(", "_xlfn.NORM.S.DIST(", formula)
1125 formula = re.sub(r"\bNORM.S.INV\(", "_xlfn.NORM.S.INV(", formula)
1126 formula = re.sub(r"\bNUMBERVALUE\(", "_xlfn.NUMBERVALUE(", formula)
1127 formula = re.sub(r"\bPDURATION\(", "_xlfn.PDURATION(", formula)
1128 formula = re.sub(r"\bPERCENTILE.EXC\(", "_xlfn.PERCENTILE.EXC(", formula)
1129 formula = re.sub(r"\bPERCENTILE.INC\(", "_xlfn.PERCENTILE.INC(", formula)
1130 formula = re.sub(r"\bPERCENTRANK.EXC\(", "_xlfn.PERCENTRANK.EXC(", formula)
1131 formula = re.sub(r"\bPERCENTRANK.INC\(", "_xlfn.PERCENTRANK.INC(", formula)
1132 formula = re.sub(r"\bPERMUTATIONA\(", "_xlfn.PERMUTATIONA(", formula)
1133 formula = re.sub(r"\bPHI\(", "_xlfn.PHI(", formula)
1134 formula = re.sub(r"\bPOISSON.DIST\(", "_xlfn.POISSON.DIST(", formula)
1135 formula = re.sub(r"\bQUARTILE.EXC\(", "_xlfn.QUARTILE.EXC(", formula)
1136 formula = re.sub(r"\bQUARTILE.INC\(", "_xlfn.QUARTILE.INC(", formula)
1137 formula = re.sub(r"\bQUERYSTRING\(", "_xlfn.QUERYSTRING(", formula)
1138 formula = re.sub(r"\bRANK.AVG\(", "_xlfn.RANK.AVG(", formula)
1139 formula = re.sub(r"\bRANK.EQ\(", "_xlfn.RANK.EQ(", formula)
1140 formula = re.sub(r"\bRRI\(", "_xlfn.RRI(", formula)
1141 formula = re.sub(r"\bSECH\(", "_xlfn.SECH(", formula)
1142 formula = re.sub(r"\bSEC\(", "_xlfn.SEC(", formula)
1143 formula = re.sub(r"\bSHEETS\(", "_xlfn.SHEETS(", formula)
1144 formula = re.sub(r"\bSHEET\(", "_xlfn.SHEET(", formula)
1145 formula = re.sub(r"\bSKEW.P\(", "_xlfn.SKEW.P(", formula)
1146 formula = re.sub(r"\bSTDEV.P\(", "_xlfn.STDEV.P(", formula)
1147 formula = re.sub(r"\bSTDEV.S\(", "_xlfn.STDEV.S(", formula)
1148 formula = re.sub(r"\bT.DIST.2T\(", "_xlfn.T.DIST.2T(", formula)
1149 formula = re.sub(r"\bT.DIST.RT\(", "_xlfn.T.DIST.RT(", formula)
1150 formula = re.sub(r"\bT.DIST\(", "_xlfn.T.DIST(", formula)
1151 formula = re.sub(r"\bT.INV.2T\(", "_xlfn.T.INV.2T(", formula)
1152 formula = re.sub(r"\bT.INV\(", "_xlfn.T.INV(", formula)
1153 formula = re.sub(r"\bT.TEST\(", "_xlfn.T.TEST(", formula)
1154 formula = re.sub(r"\bTEXTAFTER\(", "_xlfn.TEXTAFTER(", formula)
1155 formula = re.sub(r"\bTEXTBEFORE\(", "_xlfn.TEXTBEFORE(", formula)
1156 formula = re.sub(r"\bTEXTJOIN\(", "_xlfn.TEXTJOIN(", formula)
1157 formula = re.sub(r"\bUNICHAR\(", "_xlfn.UNICHAR(", formula)
1158 formula = re.sub(r"\bUNICODE\(", "_xlfn.UNICODE(", formula)
1159 formula = re.sub(r"\bVALUETOTEXT\(", "_xlfn.VALUETOTEXT(", formula)
1160 formula = re.sub(r"\bVAR.P\(", "_xlfn.VAR.P(", formula)
1161 formula = re.sub(r"\bVAR.S\(", "_xlfn.VAR.S(", formula)
1162 formula = re.sub(r"\bWEBSERVICE\(", "_xlfn.WEBSERVICE(", formula)
1163 formula = re.sub(r"\bWEIBULL.DIST\(", "_xlfn.WEIBULL.DIST(", formula)
1164 formula = re.sub(r"\bXMATCH\(", "_xlfn.XMATCH(", formula)
1165 formula = re.sub(r"\bXOR\(", "_xlfn.XOR(", formula)
1166 formula = re.sub(r"\bZ.TEST\(", "_xlfn.Z.TEST(", formula)
1168 return formula
1170 # Escape/expand table functions. This mainly involves converting Excel 2010
1171 # "@" table ref to 2007 "[#This Row],". We parse the string to avoid
1172 # replacements in string literals within the formula.
1173 @staticmethod
1174 def _prepare_table_formula(formula):
1175 if "@" not in formula:
1176 # No escaping required.
1177 return formula
1179 escaped_formula = []
1180 in_string_literal = False
1182 for char in formula:
1183 # Match the start/end of string literals to avoid escaping
1184 # references in strings.
1185 if char == '"':
1186 in_string_literal = not in_string_literal
1188 # Copy the string literal.
1189 if in_string_literal:
1190 escaped_formula.append(char)
1191 continue
1193 # Replace table reference.
1194 if char == "@":
1195 escaped_formula.append("[#This Row],")
1196 else:
1197 escaped_formula.append(char)
1199 return ("").join(escaped_formula)
1201 # Undecorated version of write_array_formula() and
1202 # write_dynamic_array_formula().
1203 def _write_array_formula(
1204 self,
1205 first_row,
1206 first_col,
1207 last_row,
1208 last_col,
1209 formula,
1210 cell_format=None,
1211 value=0,
1212 atype="static",
1213 ) -> Literal[0, -1]:
1214 # Swap last row/col with first row/col as necessary.
1215 if first_row > last_row:
1216 first_row, last_row = last_row, first_row
1217 if first_col > last_col:
1218 first_col, last_col = last_col, first_col
1220 # Check that row and col are valid and store max and min values.
1221 if self._check_dimensions(first_row, first_col):
1222 return -1
1223 if self._check_dimensions(last_row, last_col):
1224 return -1
1226 # Define array range
1227 if first_row == last_row and first_col == last_col:
1228 cell_range = xl_rowcol_to_cell(first_row, first_col)
1229 else:
1230 cell_range = (
1231 xl_rowcol_to_cell(first_row, first_col)
1232 + ":"
1233 + xl_rowcol_to_cell(last_row, last_col)
1234 )
1236 # Modify the formula string, as needed.
1237 formula = self._prepare_formula(formula)
1239 # Write previous row if in in-line string constant_memory mode.
1240 if self.constant_memory and first_row > self.previous_row:
1241 self._write_single_row(first_row)
1243 # Store the cell data in the worksheet data table.
1244 self.table[first_row][first_col] = CellArrayFormulaTuple(
1245 formula, cell_format, value, cell_range, atype
1246 )
1248 # Pad out the rest of the area with formatted zeroes.
1249 if not self.constant_memory:
1250 for row in range(first_row, last_row + 1):
1251 for col in range(first_col, last_col + 1):
1252 if row != first_row or col != first_col:
1253 self._write_number(row, col, 0, cell_format)
1255 return 0
1257 @convert_cell_args
1258 def write_datetime(
1259 self,
1260 row: int,
1261 col: int,
1262 date: datetime.datetime,
1263 cell_format: Optional[Format] = None,
1264 ) -> Literal[0, -1]:
1265 """
1266 Write a date or time to a worksheet cell.
1268 Args:
1269 row: The cell row (zero indexed).
1270 col: The cell column (zero indexed).
1271 date: Date and/or time as a datetime object.
1272 cell_format: A cell Format object.
1274 Returns:
1275 0: Success.
1276 -1: Row or column is out of worksheet bounds.
1278 """
1279 return self._write_datetime(row, col, date, cell_format)
1281 # Undecorated version of write_datetime().
1282 def _write_datetime(self, row: int, col: int, date, cell_format=None) -> int:
1283 # Check that row and col are valid and store max and min values.
1284 if self._check_dimensions(row, col):
1285 return -1
1287 # Write previous row if in in-line string constant_memory mode.
1288 if self.constant_memory and row > self.previous_row:
1289 self._write_single_row(row)
1291 # Convert datetime to an Excel date.
1292 number = self._convert_date_time(date)
1294 # Add the default date format.
1295 if cell_format is None:
1296 cell_format = self.default_date_format
1298 # Store the cell data in the worksheet data table.
1299 self.table[row][col] = CellDatetimeTuple(number, cell_format)
1301 return 0
1303 @convert_cell_args
1304 def write_boolean(
1305 self, row: int, col: int, boolean: bool, cell_format: Optional[Format] = None
1306 ):
1307 """
1308 Write a boolean value to a worksheet cell.
1310 Args:
1311 row: The cell row (zero indexed).
1312 col: The cell column (zero indexed).
1313 boolean: Cell data. bool type.
1314 cell_format: An optional cell Format object.
1316 Returns:
1317 0: Success.
1318 -1: Row or column is out of worksheet bounds.
1320 """
1321 return self._write_boolean(row, col, boolean, cell_format)
1323 # Undecorated version of write_boolean().
1324 def _write_boolean(self, row: int, col: int, boolean, cell_format=None) -> int:
1325 # Check that row and col are valid and store max and min values.
1326 if self._check_dimensions(row, col):
1327 return -1
1329 # Write previous row if in in-line string constant_memory mode.
1330 if self.constant_memory and row > self.previous_row:
1331 self._write_single_row(row)
1333 if boolean:
1334 value = 1
1335 else:
1336 value = 0
1338 # Store the cell data in the worksheet data table.
1339 self.table[row][col] = CellBooleanTuple(value, cell_format)
1341 return 0
1343 # Write a hyperlink. This is comprised of two elements: the displayed
1344 # string and the non-displayed link. The displayed string is the same as
1345 # the link unless an alternative string is specified. The display string
1346 # is written using the write_string() method. Therefore the max characters
1347 # string limit applies.
1348 #
1349 # The hyperlink can be to a http, ftp, mail, internal sheet, or external
1350 # directory urls.
1351 @convert_cell_args
1352 def write_url(
1353 self,
1354 row: int,
1355 col: int,
1356 url: str,
1357 cell_format: Optional[Format] = None,
1358 string: Optional[str] = None,
1359 tip: Optional[str] = None,
1360 ):
1361 """
1362 Write a hyperlink to a worksheet cell.
1364 Args:
1365 row: The cell row (zero indexed).
1366 col: The cell column (zero indexed).
1367 url: Hyperlink url.
1368 format: An optional cell Format object.
1369 string: An optional display string for the hyperlink.
1370 tip: An optional tooltip.
1371 Returns:
1372 0: Success.
1373 -1: Row or column is out of worksheet bounds.
1374 -2: String longer than 32767 characters.
1375 -3: URL longer than Excel limit of 255 characters.
1376 -4: Exceeds Excel limit of 65,530 urls per worksheet.
1377 """
1378 return self._write_url(row, col, url, cell_format, string, tip)
1380 # Undecorated version of write_url().
1381 def _write_url(
1382 self, row: int, col: int, url, cell_format=None, string=None, tip=None
1383 ) -> int:
1384 # Check that row and col are valid and store max and min values
1385 if self._check_dimensions(row, col):
1386 return -1
1388 # If the URL is a string convert it to a Url object.
1389 if not isinstance(url, Url):
1391 # For backwards compatibility check if the string URL exceeds the
1392 # Excel character limit for URLs and ignore it with a warning.
1393 max_url = self.max_url_length
1394 if "#" in url:
1395 url_str, anchor_str = url.split("#", 1)
1396 else:
1397 url_str = url
1398 anchor_str = ""
1400 if _utf16_len(url_str) > max_url or _utf16_len(anchor_str) > max_url:
1401 warn(
1402 f"Ignoring URL '{url}' with link or location/anchor > {max_url} "
1403 f"characters since it exceeds Excel's limit for URLs."
1404 )
1405 return -3
1407 url = Url(url)
1409 if string is not None:
1410 url._text = string
1412 if tip is not None:
1413 url.tip = tip
1415 # Check the limit of URLs per worksheet.
1416 self.hlink_count += 1
1418 if self.hlink_count > 65530:
1419 warn(
1420 f"Ignoring URL '{url._original_url}' since it exceeds Excel's limit of "
1421 f"65,530 URLs per worksheet."
1422 )
1423 return -4
1425 # Add the default URL format.
1426 if cell_format is None:
1427 cell_format = self.default_url_format
1429 if not self.ignore_write_string:
1430 # Write previous row if in in-line string constant_memory mode.
1431 if self.constant_memory and row > self.previous_row:
1432 self._write_single_row(row)
1434 # Write the hyperlink string.
1435 self._write_string(row, col, url.text, cell_format)
1437 # Store the hyperlink data in a separate structure.
1438 self.hyperlinks[row][col] = url
1440 return 0
1442 @convert_cell_args
1443 def write_rich_string(
1444 self, row: int, col: int, *args: Union[str, Format]
1445 ) -> Literal[0, -1, -2, -3, -4, -5]:
1446 """
1447 Write a "rich" string with multiple formats to a worksheet cell.
1449 Args:
1450 row: The cell row (zero indexed).
1451 col: The cell column (zero indexed).
1452 string_parts: String and format pairs.
1453 cell_format: Optional Format object.
1455 Returns:
1456 0: Success.
1457 -1: Row or column is out of worksheet bounds.
1458 -2: String truncated to 32k characters.
1459 -3: 2 consecutive formats used.
1460 -4: Empty string used.
1461 -5: Insufficient parameters.
1463 """
1465 return self._write_rich_string(row, col, *args)
1467 # Undecorated version of write_rich_string().
1468 def _write_rich_string(self, row: int, col: int, *args) -> int:
1469 tokens = list(args)
1470 cell_format = None
1471 string_index = 0
1472 raw_string = ""
1474 # Check that row and col are valid and store max and min values
1475 if self._check_dimensions(row, col):
1476 return -1
1478 # If the last arg is a format we use it as the cell format.
1479 if isinstance(tokens[-1], Format):
1480 cell_format = tokens.pop()
1482 # Create a temp XMLWriter object and use it to write the rich string
1483 # XML to a string.
1484 fh = StringIO()
1485 self.rstring = XMLwriter()
1486 self.rstring._set_filehandle(fh)
1488 # Create a temp format with the default font for unformatted fragments.
1489 default = Format()
1491 # Convert list of format, string tokens to pairs of (format, string)
1492 # except for the first string fragment which doesn't require a default
1493 # formatting run. Use the default for strings without a leading format.
1494 fragments = []
1495 previous = "format"
1496 if len(tokens) <= 2:
1497 warn(
1498 "You must specify more than 2 format/fragments for rich "
1499 "strings. Ignoring input in write_rich_string()."
1500 )
1501 return -5
1503 for pos, token in enumerate(tokens):
1504 if not isinstance(token, Format):
1505 # Token is a string.
1506 if previous != "format":
1507 # If previous token wasn't a format add one before string.
1508 fragments.append(default)
1509 fragments.append(token)
1510 else:
1511 # If previous token was a format just add the string.
1512 fragments.append(token)
1514 if token == "":
1515 warn(
1516 "Excel doesn't allow empty strings in rich strings. "
1517 "Ignoring input in write_rich_string()."
1518 )
1519 return -4
1521 # Keep track of unformatted string.
1522 raw_string += token
1523 previous = "string"
1524 else:
1525 # Can't allow 2 formats in a row.
1526 if previous == "format" and pos > 0:
1527 warn(
1528 "Excel doesn't allow 2 consecutive formats in rich "
1529 "strings. Ignoring input in write_rich_string()."
1530 )
1531 return -3
1533 # Token is a format object. Add it to the fragment list.
1534 fragments.append(token)
1535 previous = "format"
1537 # If the first token is a string start the <r> element.
1538 if not isinstance(fragments[0], Format):
1539 self.rstring._xml_start_tag("r")
1541 # Write the XML elements for the $format $string fragments.
1542 for token in fragments:
1543 if isinstance(token, Format):
1544 # Write the font run.
1545 self.rstring._xml_start_tag("r")
1546 self._write_font(token)
1547 else:
1548 # Write the string fragment part, with whitespace handling.
1549 attributes = []
1551 if _preserve_whitespace(token):
1552 attributes.append(("xml:space", "preserve"))
1554 self.rstring._xml_data_element("t", token, attributes)
1555 self.rstring._xml_end_tag("r")
1557 # Read the in-memory string.
1558 string = self.rstring.fh.getvalue()
1560 # Check that the string is < 32767 chars.
1561 if _utf16_len(raw_string) > self.xls_strmax:
1562 warn(
1563 "String length must be less than or equal to Excel's limit "
1564 "of 32,767 characters in write_rich_string()."
1565 )
1566 return -2
1568 # Write a shared string or an in-line string in constant_memory mode.
1569 if not self.constant_memory:
1570 string_index = self.str_table._get_shared_string_index(string)
1571 else:
1572 string_index = string
1574 # Write previous row if in in-line string constant_memory mode.
1575 if self.constant_memory and row > self.previous_row:
1576 self._write_single_row(row)
1578 # Store the cell data in the worksheet data table.
1579 self.table[row][col] = CellRichStringTuple(
1580 string_index, cell_format, raw_string
1581 )
1583 return 0
1585 def add_write_handler(self, user_type, user_function) -> None:
1586 """
1587 Add a callback function to the write() method to handle user defined
1588 types.
1590 Args:
1591 user_type: The user type() to match on.
1592 user_function: The user defined function to write the type data.
1593 Returns:
1594 Nothing.
1596 """
1598 self.write_handlers[user_type] = user_function
1600 @convert_cell_args
1601 def write_row(
1602 self, row: int, col: int, data, cell_format: Optional[Format] = None
1603 ) -> Union[Literal[0], Any]:
1604 """
1605 Write a row of data starting from (row, col).
1607 Args:
1608 row: The cell row (zero indexed).
1609 col: The cell column (zero indexed).
1610 data: A list of tokens to be written with write().
1611 format: An optional cell Format object.
1612 Returns:
1613 0: Success.
1614 other: Return value of write() method.
1616 """
1617 for token in data:
1618 error = self._write(row, col, token, cell_format)
1619 if error:
1620 return error
1621 col += 1
1623 return 0
1625 @convert_cell_args
1626 def write_column(
1627 self, row: int, col: int, data, cell_format: Optional[Format] = None
1628 ) -> Union[Literal[0], Any]:
1629 """
1630 Write a column of data starting from (row, col).
1632 Args:
1633 row: The cell row (zero indexed).
1634 col: The cell column (zero indexed).
1635 data: A list of tokens to be written with write().
1636 format: An optional cell Format object.
1637 Returns:
1638 0: Success.
1639 other: Return value of write() method.
1641 """
1642 for token in data:
1643 error = self._write(row, col, token, cell_format)
1644 if error:
1645 return error
1646 row += 1
1648 return 0
1650 @convert_cell_args
1651 def insert_image(
1652 self,
1653 row: int,
1654 col: int,
1655 source: Union[str, BytesIO, Image],
1656 options: Optional[Dict[str, Any]] = None,
1657 ) -> Literal[0, -1]:
1658 """
1659 Insert an image with its top-left corner in a worksheet cell.
1661 Args:
1662 row: The cell row (zero indexed).
1663 col: The cell column (zero indexed).
1664 source: Filename, BytesIO, or Image object.
1665 options: Position, scale, url and data stream of the image.
1667 Returns:
1668 0: Success.
1669 -1: Row or column is out of worksheet bounds.
1671 """
1672 # Check insert (row, col) without storing.
1673 if self._check_dimensions(row, col, True, True):
1674 warn(f"Cannot insert image at ({row}, {col}).")
1675 return -1
1677 # Convert the source to an Image object.
1678 image = self._image_from_source(source, options)
1680 image._row = row
1681 image._col = col
1682 image._set_user_options(options)
1684 self.images.append(image)
1686 return 0
1688 @convert_cell_args
1689 def embed_image(
1690 self,
1691 row: int,
1692 col: int,
1693 source: Union[str, BytesIO, Image],
1694 options: Optional[Dict[str, Any]] = None,
1695 ) -> Literal[0, -1]:
1696 """
1697 Embed an image in a worksheet cell.
1699 Args:
1700 row: The cell row (zero indexed).
1701 col: The cell column (zero indexed).
1702 source: Filename, BytesIO, or Image object.
1703 options: Url and data stream of the image.
1705 Returns:
1706 0: Success.
1707 -1: Row or column is out of worksheet bounds.
1709 """
1710 # Check insert (row, col) without storing.
1711 if self._check_dimensions(row, col):
1712 warn(f"Cannot embed image at ({row}, {col}).")
1713 return -1
1715 if options is None:
1716 options = {}
1718 # Convert the source to an Image object.
1719 image = self._image_from_source(source, options)
1720 image._set_user_options(options)
1722 cell_format = options.get("cell_format", None)
1724 if image.url:
1725 if cell_format is None:
1726 cell_format = self.default_url_format
1728 self.ignore_write_string = True
1729 self.write_url(row, col, image.url, cell_format)
1730 self.ignore_write_string = False
1732 image_index = self.embedded_images.get_image_index(image)
1734 # Store the cell error and image index in the worksheet data table.
1735 self.table[row][col] = CellErrorTuple("#VALUE!", cell_format, image_index)
1737 return 0
1739 @convert_cell_args
1740 def insert_textbox(
1741 self, row: int, col: int, text: str, options: Optional[Dict[str, Any]] = None
1742 ) -> Literal[0, -1]:
1743 """
1744 Insert an textbox with its top-left corner in a worksheet cell.
1746 Args:
1747 row: The cell row (zero indexed).
1748 col: The cell column (zero indexed).
1749 text: The text for the textbox.
1750 options: Textbox options.
1752 Returns:
1753 0: Success.
1754 -1: Row or column is out of worksheet bounds.
1756 """
1757 # Check insert (row, col) without storing.
1758 if self._check_dimensions(row, col, True, True):
1759 warn(f"Cannot insert textbox at ({row}, {col}).")
1760 return -1
1762 if text is None:
1763 text = ""
1765 if options is None:
1766 options = {}
1768 x_offset = options.get("x_offset", 0)
1769 y_offset = options.get("y_offset", 0)
1770 x_scale = options.get("x_scale", 1)
1771 y_scale = options.get("y_scale", 1)
1772 anchor = options.get("object_position", 1)
1773 description = options.get("description", None)
1774 decorative = options.get("decorative", False)
1776 self.shapes.append(
1777 [
1778 row,
1779 col,
1780 x_offset,
1781 y_offset,
1782 x_scale,
1783 y_scale,
1784 text,
1785 anchor,
1786 options,
1787 description,
1788 decorative,
1789 ]
1790 )
1791 return 0
1793 @convert_cell_args
1794 def insert_chart(
1795 self, row: int, col: int, chart: Chart, options: Optional[Dict[str, Any]] = None
1796 ) -> Literal[0, -1, -2]:
1797 """
1798 Insert an chart with its top-left corner in a worksheet cell.
1800 Args:
1801 row: The cell row (zero indexed).
1802 col: The cell column (zero indexed).
1803 chart: Chart object.
1804 options: Position and scale of the chart.
1806 Returns:
1807 0: Success.
1808 -1: Row or column is out of worksheet bounds.
1810 """
1811 # Check insert (row, col) without storing.
1812 if self._check_dimensions(row, col, True, True):
1813 warn(f"Cannot insert chart at ({row}, {col}).")
1814 return -1
1816 if options is None:
1817 options = {}
1819 # Ensure a chart isn't inserted more than once.
1820 if chart.already_inserted or chart.combined and chart.combined.already_inserted:
1821 warn("Chart cannot be inserted in a worksheet more than once.")
1822 return -2
1824 chart.already_inserted = True
1826 if chart.combined:
1827 chart.combined.already_inserted = True
1829 x_offset = options.get("x_offset", 0)
1830 y_offset = options.get("y_offset", 0)
1831 x_scale = options.get("x_scale", 1)
1832 y_scale = options.get("y_scale", 1)
1833 anchor = options.get("object_position", 1)
1834 description = options.get("description", None)
1835 decorative = options.get("decorative", False)
1837 # Allow Chart to override the scale and offset.
1838 if chart.x_scale != 1:
1839 x_scale = chart.x_scale
1841 if chart.y_scale != 1:
1842 y_scale = chart.y_scale
1844 if chart.x_offset:
1845 x_offset = chart.x_offset
1847 if chart.y_offset:
1848 y_offset = chart.y_offset
1850 self.charts.append(
1851 [
1852 row,
1853 col,
1854 chart,
1855 x_offset,
1856 y_offset,
1857 x_scale,
1858 y_scale,
1859 anchor,
1860 description,
1861 decorative,
1862 ]
1863 )
1864 return 0
1866 @convert_cell_args
1867 def write_comment(
1868 self, row: int, col: int, comment: str, options: Optional[Dict[str, Any]] = None
1869 ) -> Literal[0, -1, -2]:
1870 """
1871 Write a comment to a worksheet cell.
1873 Args:
1874 row: The cell row (zero indexed).
1875 col: The cell column (zero indexed).
1876 comment: Cell comment. Str.
1877 options: Comment formatting options.
1879 Returns:
1880 0: Success.
1881 -1: Row or column is out of worksheet bounds.
1882 -2: String longer than 32k characters.
1884 """
1885 # Check that row and col are valid and store max and min values
1886 if self._check_dimensions(row, col):
1887 return -1
1889 # Check that the comment string is < 32767 chars.
1890 if _utf16_len(comment) > self.xls_strmax:
1891 return -2
1893 self.has_vml = True
1894 self.has_comments = True
1896 # Store the options of the cell comment, to process on file close.
1897 comment = CommentType(row, col, comment, options)
1898 self.comments[row][col] = comment
1900 return 0
1902 def show_comments(self) -> None:
1903 """
1904 Make any comments in the worksheet visible.
1906 Args:
1907 None.
1909 Returns:
1910 Nothing.
1912 """
1913 self.comments_visible = True
1915 def set_background(
1916 self, source: Union[str, BytesIO, Image], is_byte_stream: bool = False
1917 ) -> Literal[0]:
1918 """
1919 Set a background image for a worksheet.
1921 Args:
1922 source: Filename, BytesIO, or Image object.
1923 is_byte_stream: Deprecated. Use a BytesIO object instead.
1925 Returns:
1926 0: Success.
1928 """
1929 # Convert the source to an Image object.
1930 image = self._image_from_source(source)
1932 self.background_image = image
1934 if is_byte_stream:
1935 warn(
1936 "The `is_byte_stream` parameter in `set_background()` is deprecated. "
1937 "This argument can be omitted if you are using a BytesIO object."
1938 )
1940 return 0
1942 def set_comments_author(self, author) -> None:
1943 """
1944 Set the default author of the cell comments.
1946 Args:
1947 author: Comment author name. String.
1949 Returns:
1950 Nothing.
1952 """
1953 self.comments_author = author
1955 def get_name(self):
1956 """
1957 Retrieve the worksheet name.
1959 Args:
1960 None.
1962 Returns:
1963 Nothing.
1965 """
1966 # There is no set_name() method. Name must be set in add_worksheet().
1967 return self.name
1969 def activate(self) -> None:
1970 """
1971 Set this worksheet as the active worksheet, i.e. the worksheet that is
1972 displayed when the workbook is opened. Also set it as selected.
1974 Note: An active worksheet cannot be hidden.
1976 Args:
1977 None.
1979 Returns:
1980 Nothing.
1982 """
1983 self.hidden = 0
1984 self.selected = 1
1985 self.worksheet_meta.activesheet = self.index
1987 def select(self) -> None:
1988 """
1989 Set current worksheet as a selected worksheet, i.e. the worksheet
1990 has its tab highlighted.
1992 Note: A selected worksheet cannot be hidden.
1994 Args:
1995 None.
1997 Returns:
1998 Nothing.
2000 """
2001 self.selected = 1
2002 self.hidden = 0
2004 def hide(self) -> None:
2005 """
2006 Hide the current worksheet.
2008 Args:
2009 None.
2011 Returns:
2012 Nothing.
2014 """
2015 self.hidden = 1
2017 # A hidden worksheet shouldn't be active or selected.
2018 self.selected = 0
2020 def very_hidden(self) -> None:
2021 """
2022 Hide the current worksheet. This can only be unhidden by VBA.
2024 Args:
2025 None.
2027 Returns:
2028 Nothing.
2030 """
2031 self.hidden = 2
2033 # A hidden worksheet shouldn't be active or selected.
2034 self.selected = 0
2036 def set_first_sheet(self) -> None:
2037 """
2038 Set current worksheet as the first visible sheet. This is necessary
2039 when there are a large number of worksheets and the activated
2040 worksheet is not visible on the screen.
2042 Note: A selected worksheet cannot be hidden.
2044 Args:
2045 None.
2047 Returns:
2048 Nothing.
2050 """
2051 self.hidden = 0 # Active worksheet can't be hidden.
2052 self.worksheet_meta.firstsheet = self.index
2054 @convert_column_args
2055 def set_column(
2056 self,
2057 first_col: int,
2058 last_col: int,
2059 width: Optional[float] = None,
2060 cell_format: Optional[Format] = None,
2061 options: Optional[Dict[str, Any]] = None,
2062 ) -> Literal[0, -1]:
2063 """
2064 Set the width, and other properties of a single column or a
2065 range of columns.
2067 Args:
2068 first_col: First column (zero-indexed).
2069 last_col: Last column (zero-indexed). Can be same as first_col.
2070 width: Column width. (optional).
2071 cell_format: Column cell_format. (optional).
2072 options: Dict of options such as hidden and level.
2074 Returns:
2075 0: Success.
2076 -1: Column number is out of worksheet bounds.
2078 """
2079 # Convert from Excel character width to pixels. The conversion is
2080 # different below 1 character widths.
2081 if width is None:
2082 width_pixels = None
2083 elif width == 0.0:
2084 width_pixels = 0
2085 elif width < 1.0:
2086 width_pixels = int(width * (self.max_digit_width + self.cell_padding) + 0.5)
2087 else:
2088 width_pixels = int(width * self.max_digit_width + 0.5) + self.cell_padding
2090 return self.set_column_pixels(
2091 first_col, last_col, width_pixels, cell_format, options
2092 )
2094 @convert_column_args
2095 def set_column_pixels(
2096 self,
2097 first_col: int,
2098 last_col: int,
2099 width: Optional[float] = None,
2100 cell_format: Optional[Format] = None,
2101 options: Optional[Dict[str, Any]] = None,
2102 ) -> Literal[0, -1]:
2103 """
2104 Set the width, and other properties of a single column or a
2105 range of columns, where column width is in pixels.
2107 Args:
2108 first_col: First column (zero-indexed).
2109 last_col: Last column (zero-indexed). Can be same as first_col.
2110 width: Column width in pixels. (optional).
2111 cell_format: Column cell_format. (optional).
2112 options: Dict of options such as hidden and level.
2114 Returns:
2115 0: Success.
2116 -1: Column number is out of worksheet bounds.
2118 """
2119 if options is None:
2120 options = {}
2122 # Ensure 2nd col is larger than first.
2123 if first_col > last_col:
2124 first_col, last_col = (last_col, first_col)
2126 # Don't modify the row dimensions when checking the columns.
2127 ignore_row = True
2129 # Set optional column values.
2130 hidden = options.get("hidden", False)
2131 collapsed = options.get("collapsed", False)
2132 level = options.get("level", 0)
2134 # Store the column dimension only in some conditions.
2135 if cell_format or (width and hidden):
2136 ignore_col = False
2137 else:
2138 ignore_col = True
2140 # Check that each column is valid and store the max and min values.
2141 if self._check_dimensions(0, last_col, ignore_row, ignore_col):
2142 return -1
2143 if self._check_dimensions(0, first_col, ignore_row, ignore_col):
2144 return -1
2146 # Set the limits for the outline levels (0 <= x <= 7).
2147 level = max(level, 0)
2148 level = min(level, 7)
2150 self.outline_col_level = max(self.outline_col_level, level)
2152 # Store the column data.
2153 for col in range(first_col, last_col + 1):
2154 self.col_info[col] = ColumnInfo(
2155 width=width,
2156 column_format=cell_format,
2157 hidden=hidden,
2158 level=level,
2159 collapsed=collapsed,
2160 )
2162 # Store the column change to allow optimizations.
2163 self.col_size_changed = True
2165 return 0
2167 def autofit(self, max_width: Optional[int] = None) -> None:
2168 """
2169 Simulate autofit based on the data, and datatypes in each column.
2171 Args:
2172 max_width (optional): max column width to autofit, in pixels.
2174 Returns:
2175 Nothing.
2177 """
2178 # pylint: disable=too-many-nested-blocks
2179 if self.constant_memory:
2180 warn("Autofit is not supported in constant_memory mode.")
2181 return
2183 # No data written to the target sheet; nothing to autofit
2184 if self.dim_rowmax is None:
2185 return
2187 # Store the max pixel width for each column.
2188 col_width_max = {}
2190 # Convert the autofit maximum pixel width to a column/character width,
2191 # but limit it to the Excel max limit.
2192 if max_width is None:
2193 max_width = self.max_col_width
2195 max_width = min(max_width, self.max_col_width)
2197 # Create a reverse lookup for the share strings table so we can convert
2198 # the string id back to the original string.
2199 strings = sorted(
2200 self.str_table.string_table, key=self.str_table.string_table.__getitem__
2201 )
2203 for row_num in range(self.dim_rowmin, self.dim_rowmax + 1):
2204 if not self.table.get(row_num):
2205 continue
2207 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
2208 if col_num in self.table[row_num]:
2209 cell = self.table[row_num][col_num]
2210 cell_type = cell.__class__.__name__
2211 length = 0
2213 if cell_type in ("String", "RichString"):
2214 # Handle strings and rich strings.
2215 #
2216 # For standard shared strings we do a reverse lookup
2217 # from the shared string id to the actual string. For
2218 # rich strings we use the unformatted string. We also
2219 # split multi-line strings and handle each part
2220 # separately.
2221 if cell_type == "String":
2222 string_id = cell.string
2223 string = strings[string_id]
2224 else:
2225 string = cell.raw_string
2227 if "\n" not in string:
2228 # Single line string.
2229 length = xl_pixel_width(string)
2230 else:
2231 # Handle multi-line strings.
2232 for segment in string.split("\n"):
2233 seg_length = xl_pixel_width(segment)
2234 length = max(length, seg_length)
2236 elif cell_type == "Number":
2237 # Handle numbers.
2238 #
2239 # We use a workaround/optimization for numbers since
2240 # digits all have a pixel width of 7. This gives a
2241 # slightly greater width for the decimal place and
2242 # minus sign but only by a few pixels and
2243 # over-estimation is okay.
2244 length = 7 * len(str(cell.number))
2246 elif cell_type == "Datetime":
2247 # Handle dates.
2248 #
2249 # The following uses the default width for mm/dd/yyyy
2250 # dates. It isn't feasible to parse the number format
2251 # to get the actual string width for all format types.
2252 length = self.default_date_width
2254 elif cell_type == "Boolean":
2255 # Handle boolean values.
2256 #
2257 # Use the Excel standard widths for TRUE and FALSE.
2258 if cell.boolean:
2259 length = 31
2260 else:
2261 length = 36
2263 elif cell_type in ("Formula", "ArrayFormula"):
2264 # Handle formulas.
2265 #
2266 # We only try to autofit a formula if it has a
2267 # non-zero value.
2268 if isinstance(cell.value, (float, int)):
2269 if cell.value > 0:
2270 length = 7 * len(str(cell.value))
2272 elif isinstance(cell.value, str):
2273 length = xl_pixel_width(cell.value)
2275 elif isinstance(cell.value, bool):
2276 if cell.value:
2277 length = 31
2278 else:
2279 length = 36
2281 # If the cell is in an autofilter header we add an
2282 # additional 16 pixels for the dropdown arrow.
2283 if self.filter_cells.get((row_num, col_num)) and length > 0:
2284 length += 16
2286 # Add the string length to the lookup table.
2287 width_max = col_width_max.get(col_num, 0)
2288 if length > width_max:
2289 col_width_max[col_num] = length
2291 # Apply the width to the column.
2292 for col_num, width in col_width_max.items():
2293 # Add a 7 pixels padding, like Excel.
2294 width += 7
2296 # Limit the width to the maximum user or Excel value.
2297 width = min(width, max_width)
2299 # Add the width to an existing col info structure or add a new one.
2300 if self.col_info.get(col_num):
2301 # We only update the width for an existing column if it is
2302 # greater than the user defined value. This allows the user
2303 # to pre-load a minimum col width.
2304 col_info = self.col_info.get(col_num)
2305 user_width = col_info.width
2306 autofit = col_info.autofit
2307 if user_width is not None and not autofit:
2308 # Col info is user defined.
2309 if width > user_width:
2310 self.col_info[col_num].width = width
2311 self.col_info[col_num].autofit = True
2312 else:
2313 self.col_info[col_num].width = width
2314 self.col_info[col_num].autofit = True
2315 else:
2316 self.col_info[col_num] = ColumnInfo(
2317 width=width,
2318 autofit=True,
2319 )
2321 def set_row(
2322 self,
2323 row: int,
2324 height: Optional[float] = None,
2325 cell_format: Optional[Format] = None,
2326 options: Optional[Dict[str, Any]] = None,
2327 ) -> Literal[0, -1]:
2328 """
2329 Set the width, and other properties of a row.
2331 Args:
2332 row: Row number (zero-indexed).
2333 height: Row height. (optional).
2334 cell_format: Row cell_format. (optional).
2335 options: Dict of options such as hidden, level and collapsed.
2337 Returns:
2338 0: Success.
2339 -1: Row number is out of worksheet bounds.
2341 """
2342 if height is not None:
2343 pixel_height = int(height * 4.0 / 3.0 + 0.5)
2344 else:
2345 pixel_height = None
2347 return self.set_row_pixels(row, pixel_height, cell_format, options)
2349 def set_row_pixels(
2350 self,
2351 row: int,
2352 height: Optional[float] = None,
2353 cell_format: Optional[Format] = None,
2354 options: Optional[Dict[str, Any]] = None,
2355 ) -> Literal[0, -1]:
2356 """
2357 Set the width (in pixels), and other properties of a row.
2359 Args:
2360 row: Row number (zero-indexed).
2361 height: Row height in pixels. (optional).
2362 cell_format: Row cell_format. (optional).
2363 options: Dict of options such as hidden, level and collapsed.
2365 Returns:
2366 0: Success.
2367 -1: Row number is out of worksheet bounds.
2369 """
2370 if options is None:
2371 options = {}
2373 # Use minimum col in _check_dimensions().
2374 if self.dim_colmin is not None:
2375 min_col = self.dim_colmin
2376 else:
2377 min_col = 0
2379 # Check that row is valid.
2380 if self._check_dimensions(row, min_col):
2381 return -1
2383 if height is None:
2384 height = self.default_row_height
2386 # Set optional row values.
2387 hidden = options.get("hidden", False)
2388 collapsed = options.get("collapsed", False)
2389 level = options.get("level", 0)
2391 # If the height is 0 the row is hidden and the height is the default.
2392 if height == 0:
2393 hidden = True
2394 height = self.default_row_height
2396 # Set the limits for the outline levels (0 <= x <= 7).
2397 level = max(level, 0)
2398 level = min(level, 7)
2400 self.outline_row_level = max(self.outline_row_level, level)
2402 # Store the row properties.
2403 self.row_info[row] = RowInfo(
2404 height=height,
2405 row_format=cell_format,
2406 hidden=hidden,
2407 level=level,
2408 collapsed=collapsed,
2409 )
2411 # Store the row sizes for use when calculating image vertices.
2412 self.row_sizes[row] = [height, hidden]
2414 return 0
2416 def set_default_row(
2417 self, height: Optional[float] = None, hide_unused_rows: bool = False
2418 ) -> None:
2419 """
2420 Set the default row properties.
2422 Args:
2423 height: Default height. Optional, defaults to 15.
2424 hide_unused_rows: Hide unused rows. Optional, defaults to False.
2426 Returns:
2427 Nothing.
2429 """
2430 if height is None:
2431 pixel_height = self.default_row_height
2432 else:
2433 pixel_height = int(height * 4.0 / 3.0 + 0.5)
2435 if pixel_height != self.original_row_height:
2436 self.default_row_height = pixel_height
2438 if hide_unused_rows:
2439 self.default_row_zeroed = 1
2441 @convert_range_args
2442 def merge_range(
2443 self,
2444 first_row: int,
2445 first_col: int,
2446 last_row: int,
2447 last_col: int,
2448 data: Any,
2449 cell_format: Optional[Format] = None,
2450 ) -> int:
2451 """
2452 Merge a range of cells.
2454 Args:
2455 first_row: The first row of the cell range. (zero indexed).
2456 first_col: The first column of the cell range.
2457 last_row: The last row of the cell range. (zero indexed).
2458 last_col: The last column of the cell range.
2459 data: Cell data.
2460 cell_format: Cell Format object.
2462 Returns:
2463 0: Success.
2464 -1: Row or column is out of worksheet bounds.
2465 other: Return value of write().
2467 """
2468 # Merge a range of cells. The first cell should contain the data and
2469 # the others should be blank. All cells should have the same format.
2471 # Excel doesn't allow a single cell to be merged
2472 if first_row == last_row and first_col == last_col:
2473 warn("Can't merge single cell")
2474 return -1
2476 # Swap last row/col with first row/col as necessary
2477 if first_row > last_row:
2478 first_row, last_row = (last_row, first_row)
2479 if first_col > last_col:
2480 first_col, last_col = (last_col, first_col)
2482 # Check that row and col are valid and store max and min values.
2483 if self._check_dimensions(first_row, first_col):
2484 return -1
2485 if self._check_dimensions(last_row, last_col):
2486 return -1
2488 # Check if the merge range overlaps a previous merged or table range.
2489 # This is a critical file corruption error in Excel.
2490 cell_range = xl_range(first_row, first_col, last_row, last_col)
2491 for row in range(first_row, last_row + 1):
2492 for col in range(first_col, last_col + 1):
2493 if self.merged_cells.get((row, col)):
2494 previous_range = self.merged_cells.get((row, col))
2495 raise OverlappingRange(
2496 f"Merge range '{cell_range}' overlaps previous merge "
2497 f"range '{previous_range}'."
2498 )
2500 if self.table_cells.get((row, col)):
2501 previous_range = self.table_cells.get((row, col))
2502 raise OverlappingRange(
2503 f"Merge range '{cell_range}' overlaps previous table "
2504 f"range '{previous_range}'."
2505 )
2507 self.merged_cells[(row, col)] = cell_range
2509 # Store the merge range.
2510 self.merge.append([first_row, first_col, last_row, last_col])
2512 # Write the first cell
2513 self._write(first_row, first_col, data, cell_format)
2515 # Pad out the rest of the area with formatted blank cells.
2516 for row in range(first_row, last_row + 1):
2517 for col in range(first_col, last_col + 1):
2518 if row == first_row and col == first_col:
2519 continue
2520 self._write_blank(row, col, "", cell_format)
2522 return 0
2524 @convert_range_args
2525 def autofilter(
2526 self, first_row: int, first_col: int, last_row: int, last_col: int
2527 ) -> None:
2528 """
2529 Set the autofilter area in the worksheet.
2531 Args:
2532 first_row: The first row of the cell range. (zero indexed).
2533 first_col: The first column of the cell range.
2534 last_row: The last row of the cell range. (zero indexed).
2535 last_col: The last column of the cell range.
2537 Returns:
2538 Nothing.
2540 """
2541 # Reverse max and min values if necessary.
2542 if last_row < first_row:
2543 first_row, last_row = (last_row, first_row)
2544 if last_col < first_col:
2545 first_col, last_col = (last_col, first_col)
2547 # Check that row and col are valid without storing the values.
2548 if self._check_dimensions(first_row, first_col, True, True):
2549 return -1
2550 if self._check_dimensions(last_row, last_col, True, True):
2551 return -1
2553 # Build up the autofilter area range "Sheet1!$A$1:$C$13".
2554 area = self._convert_name_area(first_row, first_col, last_row, last_col)
2555 ref = xl_range(first_row, first_col, last_row, last_col)
2557 self.autofilter_area = area
2558 self.autofilter_ref = ref
2559 self.filter_range = [first_col, last_col]
2561 # Store the filter cell positions for use in the autofit calculation.
2562 for col in range(first_col, last_col + 1):
2563 # Check that the autofilter doesn't overlap a table filter.
2564 if self.filter_cells.get((first_row, col)):
2565 filter_type, filter_range = self.filter_cells.get((first_row, col))
2566 if filter_type == "table":
2567 raise OverlappingRange(
2568 f"Worksheet autofilter range '{ref}' overlaps previous "
2569 f"Table autofilter range '{filter_range}'."
2570 )
2572 self.filter_cells[(first_row, col)] = ("worksheet", ref)
2574 return 0
2576 def filter_column(self, col: int, criteria: str) -> None:
2577 """
2578 Set the column filter criteria.
2580 Args:
2581 col: Filter column (zero-indexed).
2582 criteria: Filter criteria.
2584 Returns:
2585 Nothing.
2587 """
2588 if not self.autofilter_area:
2589 warn("Must call autofilter() before filter_column()")
2590 return
2592 # Check for a column reference in A1 notation and substitute.
2593 try:
2594 int(col)
2595 except ValueError:
2596 # Convert col ref to a cell ref and then to a col number.
2597 col_letter = col
2598 _, col = xl_cell_to_rowcol(col + "1")
2600 if col >= self.xls_colmax:
2601 warn(f"Invalid column '{col_letter}'")
2602 return
2604 col_first, col_last = self.filter_range
2606 # Reject column if it is outside filter range.
2607 if col < col_first or col > col_last:
2608 warn(
2609 f"Column '{col}' outside autofilter() column "
2610 f"range ({col_first}, {col_last})"
2611 )
2612 return
2614 tokens = self._extract_filter_tokens(criteria)
2616 if len(tokens) not in (3, 7):
2617 warn(f"Incorrect number of tokens in criteria '{criteria}'")
2619 tokens = self._parse_filter_expression(criteria, tokens)
2621 # Excel handles single or double custom filters as default filters.
2622 # We need to check for them and handle them accordingly.
2623 if len(tokens) == 2 and tokens[0] == 2:
2624 # Single equality.
2625 self.filter_column_list(col, [tokens[1]])
2626 elif len(tokens) == 5 and tokens[0] == 2 and tokens[2] == 1 and tokens[3] == 2:
2627 # Double equality with "or" operator.
2628 self.filter_column_list(col, [tokens[1], tokens[4]])
2629 else:
2630 # Non default custom filter.
2631 self.filter_cols[col] = tokens
2632 self.filter_type[col] = 0
2634 self.filter_on = 1
2636 def filter_column_list(self, col: int, filters: List[str]) -> None:
2637 """
2638 Set the column filter criteria in Excel 2007 list style.
2640 Args:
2641 col: Filter column (zero-indexed).
2642 filters: List of filter criteria to match.
2644 Returns:
2645 Nothing.
2647 """
2648 if not self.autofilter_area:
2649 warn("Must call autofilter() before filter_column()")
2650 return
2652 # Check for a column reference in A1 notation and substitute.
2653 try:
2654 int(col)
2655 except ValueError:
2656 # Convert col ref to a cell ref and then to a col number.
2657 col_letter = col
2658 _, col = xl_cell_to_rowcol(col + "1")
2660 if col >= self.xls_colmax:
2661 warn(f"Invalid column '{col_letter}'")
2662 return
2664 col_first, col_last = self.filter_range
2666 # Reject column if it is outside filter range.
2667 if col < col_first or col > col_last:
2668 warn(
2669 f"Column '{col}' outside autofilter() column range "
2670 f"({col_first},{col_last})"
2671 )
2672 return
2674 self.filter_cols[col] = filters
2675 self.filter_type[col] = 1
2676 self.filter_on = 1
2678 @convert_range_args
2679 def data_validation(
2680 self,
2681 first_row: int,
2682 first_col: int,
2683 last_row: int,
2684 last_col: int,
2685 options: Optional[Dict[str, Any]] = None,
2686 ) -> Literal[0, -1, -2]:
2687 """
2688 Add a data validation to a worksheet.
2690 Args:
2691 first_row: The first row of the cell range. (zero indexed).
2692 first_col: The first column of the cell range.
2693 last_row: The last row of the cell range. (zero indexed).
2694 last_col: The last column of the cell range.
2695 options: Data validation options.
2697 Returns:
2698 0: Success.
2699 -1: Row or column is out of worksheet bounds.
2700 -2: Incorrect parameter or option.
2701 """
2702 # Check that row and col are valid without storing the values.
2703 if self._check_dimensions(first_row, first_col, True, True):
2704 return -1
2705 if self._check_dimensions(last_row, last_col, True, True):
2706 return -1
2708 if options is None:
2709 options = {}
2710 else:
2711 # Copy the user defined options so they aren't modified.
2712 options = options.copy()
2714 # Valid input parameters.
2715 valid_parameters = {
2716 "validate",
2717 "criteria",
2718 "value",
2719 "source",
2720 "minimum",
2721 "maximum",
2722 "ignore_blank",
2723 "dropdown",
2724 "show_input",
2725 "input_title",
2726 "input_message",
2727 "show_error",
2728 "error_title",
2729 "error_message",
2730 "error_type",
2731 "other_cells",
2732 "multi_range",
2733 }
2735 # Check for valid input parameters.
2736 for param_key in options:
2737 if param_key not in valid_parameters:
2738 warn(f"Unknown parameter '{param_key}' in data_validation()")
2739 return -2
2741 # Map alternative parameter names 'source' or 'minimum' to 'value'.
2742 if "source" in options:
2743 options["value"] = options["source"]
2744 if "minimum" in options:
2745 options["value"] = options["minimum"]
2747 # 'validate' is a required parameter.
2748 if "validate" not in options:
2749 warn("Parameter 'validate' is required in data_validation()")
2750 return -2
2752 # List of valid validation types.
2753 valid_types = {
2754 "any": "none",
2755 "any value": "none",
2756 "whole number": "whole",
2757 "whole": "whole",
2758 "integer": "whole",
2759 "decimal": "decimal",
2760 "list": "list",
2761 "date": "date",
2762 "time": "time",
2763 "text length": "textLength",
2764 "length": "textLength",
2765 "custom": "custom",
2766 }
2768 # Check for valid validation types.
2769 if options["validate"] not in valid_types:
2770 warn(
2771 f"Unknown validation type '{options['validate']}' for parameter "
2772 f"'validate' in data_validation()"
2773 )
2774 return -2
2776 options["validate"] = valid_types[options["validate"]]
2778 # No action is required for validation type 'any' if there are no
2779 # input messages to display.
2780 if (
2781 options["validate"] == "none"
2782 and options.get("input_title") is None
2783 and options.get("input_message") is None
2784 ):
2785 return -2
2787 # The any, list and custom validations don't have a criteria so we use
2788 # a default of 'between'.
2789 if (
2790 options["validate"] == "none"
2791 or options["validate"] == "list"
2792 or options["validate"] == "custom"
2793 ):
2794 options["criteria"] = "between"
2795 options["maximum"] = None
2797 # 'criteria' is a required parameter.
2798 if "criteria" not in options:
2799 warn("Parameter 'criteria' is required in data_validation()")
2800 return -2
2802 # Valid criteria types.
2803 criteria_types = {
2804 "between": "between",
2805 "not between": "notBetween",
2806 "equal to": "equal",
2807 "=": "equal",
2808 "==": "equal",
2809 "not equal to": "notEqual",
2810 "!=": "notEqual",
2811 "<>": "notEqual",
2812 "greater than": "greaterThan",
2813 ">": "greaterThan",
2814 "less than": "lessThan",
2815 "<": "lessThan",
2816 "greater than or equal to": "greaterThanOrEqual",
2817 ">=": "greaterThanOrEqual",
2818 "less than or equal to": "lessThanOrEqual",
2819 "<=": "lessThanOrEqual",
2820 }
2822 # Check for valid criteria types.
2823 if options["criteria"] not in criteria_types:
2824 warn(
2825 f"Unknown criteria type '{options['criteria']}' for parameter "
2826 f"'criteria' in data_validation()"
2827 )
2828 return -2
2830 options["criteria"] = criteria_types[options["criteria"]]
2832 # 'Between' and 'Not between' criteria require 2 values.
2833 if options["criteria"] == "between" or options["criteria"] == "notBetween":
2834 if "maximum" not in options:
2835 warn(
2836 "Parameter 'maximum' is required in data_validation() "
2837 "when using 'between' or 'not between' criteria"
2838 )
2839 return -2
2840 else:
2841 options["maximum"] = None
2843 # Valid error dialog types.
2844 error_types = {
2845 "stop": 0,
2846 "warning": 1,
2847 "information": 2,
2848 }
2850 # Check for valid error dialog types.
2851 if "error_type" not in options:
2852 options["error_type"] = 0
2853 elif options["error_type"] not in error_types:
2854 warn(
2855 f"Unknown criteria type '{options['error_type']}' "
2856 f"for parameter 'error_type'."
2857 )
2858 return -2
2859 else:
2860 options["error_type"] = error_types[options["error_type"]]
2862 # Convert date/times value if required.
2863 if (
2864 options["validate"] in ("date", "time")
2865 and options["value"]
2866 and _supported_datetime(options["value"])
2867 ):
2868 date_time = self._convert_date_time(options["value"])
2869 # Format date number to the same precision as Excel.
2870 options["value"] = f"{date_time:.16g}"
2872 if options["maximum"] and _supported_datetime(options["maximum"]):
2873 date_time = self._convert_date_time(options["maximum"])
2874 options["maximum"] = f"{date_time:.16g}"
2876 # Check that the input title doesn't exceed the maximum length.
2877 if options.get("input_title") and _utf16_len(options["input_title"]) > 32:
2878 warn(
2879 f"Length of input title '{options['input_title']}' "
2880 f"exceeds Excel's limit of 32"
2881 )
2882 return -2
2884 # Check that the error title doesn't exceed the maximum length.
2885 if options.get("error_title") and _utf16_len(options["error_title"]) > 32:
2886 warn(
2887 f"Length of error title '{options['error_title']}' "
2888 f"exceeds Excel's limit of 32"
2889 )
2890 return -2
2892 # Check that the input message doesn't exceed the maximum length.
2893 if options.get("input_message") and _utf16_len(options["input_message"]) > 255:
2894 warn(
2895 f"Length of input message '{options['input_message']}' "
2896 f"exceeds Excel's limit of 255"
2897 )
2898 return -2
2900 # Check that the error message doesn't exceed the maximum length.
2901 if options.get("error_message") and _utf16_len(options["error_message"]) > 255:
2902 warn(
2903 f"Length of error message '{options['error_message']}' "
2904 f"exceeds Excel's limit of 255"
2905 )
2906 return -2
2908 # Check that the input list doesn't exceed the maximum length.
2909 if options["validate"] == "list" and isinstance(options["value"], list):
2910 formula = self._csv_join(*options["value"])
2911 if _utf16_len(formula) > 255:
2912 warn(
2913 f"Length of list items '{formula}' exceeds Excel's limit of "
2914 f"255, use a formula range instead"
2915 )
2916 return -2
2918 # Set some defaults if they haven't been defined by the user.
2919 if "ignore_blank" not in options:
2920 options["ignore_blank"] = 1
2921 if "dropdown" not in options:
2922 options["dropdown"] = 1
2923 if "show_input" not in options:
2924 options["show_input"] = 1
2925 if "show_error" not in options:
2926 options["show_error"] = 1
2928 # These are the cells to which the validation is applied.
2929 options["cells"] = [[first_row, first_col, last_row, last_col]]
2931 # A (for now) undocumented parameter to pass additional cell ranges.
2932 if "other_cells" in options:
2933 options["cells"].extend(options["other_cells"])
2935 # Override with user defined multiple range if provided.
2936 if "multi_range" in options:
2937 options["multi_range"] = options["multi_range"].replace("$", "")
2939 # Store the validation information until we close the worksheet.
2940 self.validations.append(options)
2942 return 0
2944 @convert_range_args
2945 def conditional_format(
2946 self,
2947 first_row: int,
2948 first_col: int,
2949 last_row: int,
2950 last_col: int,
2951 options: Optional[Dict[str, Any]] = None,
2952 ) -> Literal[0, -1, -2]:
2953 """
2954 Add a conditional format to a worksheet.
2956 Args:
2957 first_row: The first row of the cell range. (zero indexed).
2958 first_col: The first column of the cell range.
2959 last_row: The last row of the cell range. (zero indexed).
2960 last_col: The last column of the cell range.
2961 options: Conditional format options.
2963 Returns:
2964 0: Success.
2965 -1: Row or column is out of worksheet bounds.
2966 -2: Incorrect parameter or option.
2967 """
2968 # Check that row and col are valid without storing the values.
2969 if self._check_dimensions(first_row, first_col, True, True):
2970 return -1
2971 if self._check_dimensions(last_row, last_col, True, True):
2972 return -1
2974 if options is None:
2975 options = {}
2976 else:
2977 # Copy the user defined options so they aren't modified.
2978 options = options.copy()
2980 # Valid input parameters.
2981 valid_parameter = {
2982 "type",
2983 "format",
2984 "criteria",
2985 "value",
2986 "minimum",
2987 "maximum",
2988 "stop_if_true",
2989 "min_type",
2990 "mid_type",
2991 "max_type",
2992 "min_value",
2993 "mid_value",
2994 "max_value",
2995 "min_color",
2996 "mid_color",
2997 "max_color",
2998 "min_length",
2999 "max_length",
3000 "multi_range",
3001 "bar_color",
3002 "bar_negative_color",
3003 "bar_negative_color_same",
3004 "bar_solid",
3005 "bar_border_color",
3006 "bar_negative_border_color",
3007 "bar_negative_border_color_same",
3008 "bar_no_border",
3009 "bar_direction",
3010 "bar_axis_position",
3011 "bar_axis_color",
3012 "bar_only",
3013 "data_bar_2010",
3014 "icon_style",
3015 "reverse_icons",
3016 "icons_only",
3017 "icons",
3018 }
3020 # Check for valid input parameters.
3021 for param_key in options:
3022 if param_key not in valid_parameter:
3023 warn(f"Unknown parameter '{param_key}' in conditional_format()")
3024 return -2
3026 # 'type' is a required parameter.
3027 if "type" not in options:
3028 warn("Parameter 'type' is required in conditional_format()")
3029 return -2
3031 # Valid types.
3032 valid_type = {
3033 "cell": "cellIs",
3034 "date": "date",
3035 "time": "time",
3036 "average": "aboveAverage",
3037 "duplicate": "duplicateValues",
3038 "unique": "uniqueValues",
3039 "top": "top10",
3040 "bottom": "top10",
3041 "text": "text",
3042 "time_period": "timePeriod",
3043 "blanks": "containsBlanks",
3044 "no_blanks": "notContainsBlanks",
3045 "errors": "containsErrors",
3046 "no_errors": "notContainsErrors",
3047 "2_color_scale": "2_color_scale",
3048 "3_color_scale": "3_color_scale",
3049 "data_bar": "dataBar",
3050 "formula": "expression",
3051 "icon_set": "iconSet",
3052 }
3054 # Check for valid types.
3055 if options["type"] not in valid_type:
3056 warn(
3057 f"Unknown value '{options['type']}' for parameter 'type' "
3058 f"in conditional_format()"
3059 )
3060 return -2
3062 if options["type"] == "bottom":
3063 options["direction"] = "bottom"
3064 options["type"] = valid_type[options["type"]]
3066 # Valid criteria types.
3067 criteria_type = {
3068 "between": "between",
3069 "not between": "notBetween",
3070 "equal to": "equal",
3071 "=": "equal",
3072 "==": "equal",
3073 "not equal to": "notEqual",
3074 "!=": "notEqual",
3075 "<>": "notEqual",
3076 "greater than": "greaterThan",
3077 ">": "greaterThan",
3078 "less than": "lessThan",
3079 "<": "lessThan",
3080 "greater than or equal to": "greaterThanOrEqual",
3081 ">=": "greaterThanOrEqual",
3082 "less than or equal to": "lessThanOrEqual",
3083 "<=": "lessThanOrEqual",
3084 "containing": "containsText",
3085 "not containing": "notContains",
3086 "begins with": "beginsWith",
3087 "ends with": "endsWith",
3088 "yesterday": "yesterday",
3089 "today": "today",
3090 "last 7 days": "last7Days",
3091 "last week": "lastWeek",
3092 "this week": "thisWeek",
3093 "next week": "nextWeek",
3094 "last month": "lastMonth",
3095 "this month": "thisMonth",
3096 "next month": "nextMonth",
3097 # For legacy, but incorrect, support.
3098 "continue week": "nextWeek",
3099 "continue month": "nextMonth",
3100 }
3102 # Check for valid criteria types.
3103 if "criteria" in options and options["criteria"] in criteria_type:
3104 options["criteria"] = criteria_type[options["criteria"]]
3106 # Convert boolean values if required.
3107 if "value" in options and isinstance(options["value"], bool):
3108 options["value"] = str(options["value"]).upper()
3110 # Convert date/times value if required.
3111 if options["type"] in ("date", "time"):
3112 options["type"] = "cellIs"
3114 if "value" in options:
3115 if not _supported_datetime(options["value"]):
3116 warn("Conditional format 'value' must be a datetime object.")
3117 return -2
3119 date_time = self._convert_date_time(options["value"])
3120 # Format date number to the same precision as Excel.
3121 options["value"] = f"{date_time:.16g}"
3123 if "minimum" in options:
3124 if not _supported_datetime(options["minimum"]):
3125 warn("Conditional format 'minimum' must be a datetime object.")
3126 return -2
3128 date_time = self._convert_date_time(options["minimum"])
3129 options["minimum"] = f"{date_time:.16g}"
3131 if "maximum" in options:
3132 if not _supported_datetime(options["maximum"]):
3133 warn("Conditional format 'maximum' must be a datetime object.")
3134 return -2
3136 date_time = self._convert_date_time(options["maximum"])
3137 options["maximum"] = f"{date_time:.16g}"
3139 # Valid icon styles.
3140 valid_icons = {
3141 "3_arrows": "3Arrows", # 1
3142 "3_flags": "3Flags", # 2
3143 "3_traffic_lights_rimmed": "3TrafficLights2", # 3
3144 "3_symbols_circled": "3Symbols", # 4
3145 "4_arrows": "4Arrows", # 5
3146 "4_red_to_black": "4RedToBlack", # 6
3147 "4_traffic_lights": "4TrafficLights", # 7
3148 "5_arrows_gray": "5ArrowsGray", # 8
3149 "5_quarters": "5Quarters", # 9
3150 "3_arrows_gray": "3ArrowsGray", # 10
3151 "3_traffic_lights": "3TrafficLights", # 11
3152 "3_signs": "3Signs", # 12
3153 "3_symbols": "3Symbols2", # 13
3154 "4_arrows_gray": "4ArrowsGray", # 14
3155 "4_ratings": "4Rating", # 15
3156 "5_arrows": "5Arrows", # 16
3157 "5_ratings": "5Rating",
3158 } # 17
3160 # Set the icon set properties.
3161 if options["type"] == "iconSet":
3162 # An icon_set must have an icon style.
3163 if not options.get("icon_style"):
3164 warn(
3165 "The 'icon_style' parameter must be specified when "
3166 "'type' == 'icon_set' in conditional_format()."
3167 )
3168 return -3
3170 # Check for valid icon styles.
3171 if options["icon_style"] not in valid_icons:
3172 warn(
3173 f"Unknown icon_style '{options['icon_style']}' "
3174 f"in conditional_format()."
3175 )
3176 return -2
3178 options["icon_style"] = valid_icons[options["icon_style"]]
3180 # Set the number of icons for the icon style.
3181 options["total_icons"] = 3
3182 if options["icon_style"].startswith("4"):
3183 options["total_icons"] = 4
3184 elif options["icon_style"].startswith("5"):
3185 options["total_icons"] = 5
3187 options["icons"] = self._set_icon_props(
3188 options.get("total_icons"), options.get("icons")
3189 )
3191 # Swap last row/col for first row/col as necessary
3192 if first_row > last_row:
3193 first_row, last_row = last_row, first_row
3195 if first_col > last_col:
3196 first_col, last_col = last_col, first_col
3198 # Set the formatting range.
3199 cell_range = xl_range(first_row, first_col, last_row, last_col)
3200 start_cell = xl_rowcol_to_cell(first_row, first_col)
3202 # Override with user defined multiple range if provided.
3203 if "multi_range" in options:
3204 cell_range = options["multi_range"]
3205 cell_range = cell_range.replace("$", "")
3207 # Get the dxf format index.
3208 if options.get("format"):
3209 options["format"] = options["format"]._get_dxf_index()
3211 # Set the priority based on the order of adding.
3212 options["priority"] = self.dxf_priority
3213 self.dxf_priority += 1
3215 # Check for 2010 style data_bar parameters.
3216 # pylint: disable=too-many-boolean-expressions
3217 if (
3218 self.use_data_bars_2010
3219 or options.get("data_bar_2010")
3220 or options.get("bar_solid")
3221 or options.get("bar_border_color")
3222 or options.get("bar_negative_color")
3223 or options.get("bar_negative_color_same")
3224 or options.get("bar_negative_border_color")
3225 or options.get("bar_negative_border_color_same")
3226 or options.get("bar_no_border")
3227 or options.get("bar_axis_position")
3228 or options.get("bar_axis_color")
3229 or options.get("bar_direction")
3230 ):
3231 options["is_data_bar_2010"] = True
3233 # Special handling of text criteria.
3234 if options["type"] == "text":
3235 value = options["value"]
3236 criteria = options["criteria"]
3238 # Excel string lengths are in UTF-16 units.
3239 length = len(value.encode("utf-16-le")) // 2
3241 # Excel requires that double quotes in formula strings are doubled.
3242 value = value.replace('"', '""')
3244 if options["criteria"] == "containsText":
3245 options["type"] = "containsText"
3246 options["formula"] = f'NOT(ISERROR(SEARCH("{value}",{start_cell})))'
3247 elif options["criteria"] == "notContains":
3248 options["type"] = "notContainsText"
3249 options["formula"] = f'ISERROR(SEARCH("{value}",{start_cell}))'
3250 elif options["criteria"] == "beginsWith":
3251 options["type"] = "beginsWith"
3252 options["formula"] = f'LEFT({start_cell},{length})="{value}"'
3253 elif options["criteria"] == "endsWith":
3254 options["type"] = "endsWith"
3255 options["formula"] = f'RIGHT({start_cell},{length})="{value}"'
3256 else:
3257 warn(f"Invalid text criteria '{criteria}' in conditional_format()")
3259 # Special handling of time time_period criteria.
3260 if options["type"] == "timePeriod":
3261 if options["criteria"] == "yesterday":
3262 options["formula"] = f"FLOOR({start_cell},1)=TODAY()-1"
3264 elif options["criteria"] == "today":
3265 options["formula"] = f"FLOOR({start_cell},1)=TODAY()"
3267 elif options["criteria"] == "tomorrow":
3268 options["formula"] = f"FLOOR({start_cell},1)=TODAY()+1"
3270 # fmt: off
3271 elif options["criteria"] == "last7Days":
3272 options["formula"] = (
3273 f"AND(TODAY()-FLOOR({start_cell},1)<=6,"
3274 f"FLOOR({start_cell},1)<=TODAY())"
3275 )
3276 # fmt: on
3278 elif options["criteria"] == "lastWeek":
3279 options["formula"] = (
3280 f"AND(TODAY()-ROUNDDOWN({start_cell},0)>=(WEEKDAY(TODAY())),"
3281 f"TODAY()-ROUNDDOWN({start_cell},0)<(WEEKDAY(TODAY())+7))"
3282 )
3284 elif options["criteria"] == "thisWeek":
3285 options["formula"] = (
3286 f"AND(TODAY()-ROUNDDOWN({start_cell},0)<=WEEKDAY(TODAY())-1,"
3287 f"ROUNDDOWN({start_cell},0)-TODAY()<=7-WEEKDAY(TODAY()))"
3288 )
3290 elif options["criteria"] == "nextWeek":
3291 options["formula"] = (
3292 f"AND(ROUNDDOWN({start_cell},0)-TODAY()>(7-WEEKDAY(TODAY())),"
3293 f"ROUNDDOWN({start_cell},0)-TODAY()<(15-WEEKDAY(TODAY())))"
3294 )
3296 elif options["criteria"] == "lastMonth":
3297 options["formula"] = (
3298 f"AND(MONTH({start_cell})=MONTH(TODAY())-1,"
3299 f"OR(YEAR({start_cell})=YEAR("
3300 f"TODAY()),AND(MONTH({start_cell})=1,YEAR(A1)=YEAR(TODAY())-1)))"
3301 )
3303 # fmt: off
3304 elif options["criteria"] == "thisMonth":
3305 options["formula"] = (
3306 f"AND(MONTH({start_cell})=MONTH(TODAY()),"
3307 f"YEAR({start_cell})=YEAR(TODAY()))"
3308 )
3309 # fmt: on
3311 elif options["criteria"] == "nextMonth":
3312 options["formula"] = (
3313 f"AND(MONTH({start_cell})=MONTH(TODAY())+1,"
3314 f"OR(YEAR({start_cell})=YEAR("
3315 f"TODAY()),AND(MONTH({start_cell})=12,"
3316 f"YEAR({start_cell})=YEAR(TODAY())+1)))"
3317 )
3319 else:
3320 warn(
3321 f"Invalid time_period criteria '{options['criteria']}' "
3322 f"in conditional_format()"
3323 )
3325 # Special handling of blanks/error types.
3326 if options["type"] == "containsBlanks":
3327 options["formula"] = f"LEN(TRIM({start_cell}))=0"
3329 if options["type"] == "notContainsBlanks":
3330 options["formula"] = f"LEN(TRIM({start_cell}))>0"
3332 if options["type"] == "containsErrors":
3333 options["formula"] = f"ISERROR({start_cell})"
3335 if options["type"] == "notContainsErrors":
3336 options["formula"] = f"NOT(ISERROR({start_cell}))"
3338 # Special handling for 2 color scale.
3339 if options["type"] == "2_color_scale":
3340 options["type"] = "colorScale"
3342 # Color scales don't use any additional formatting.
3343 options["format"] = None
3345 # Turn off 3 color parameters.
3346 options["mid_type"] = None
3347 options["mid_color"] = None
3349 options.setdefault("min_type", "min")
3350 options.setdefault("max_type", "max")
3351 options.setdefault("min_value", 0)
3352 options.setdefault("max_value", 0)
3353 options.setdefault("min_color", Color("#FF7128"))
3354 options.setdefault("max_color", Color("#FFEF9C"))
3356 options["min_color"] = Color._from_value(options["min_color"])
3357 options["max_color"] = Color._from_value(options["max_color"])
3359 # Special handling for 3 color scale.
3360 if options["type"] == "3_color_scale":
3361 options["type"] = "colorScale"
3363 # Color scales don't use any additional formatting.
3364 options["format"] = None
3366 options.setdefault("min_type", "min")
3367 options.setdefault("mid_type", "percentile")
3368 options.setdefault("max_type", "max")
3369 options.setdefault("min_value", 0)
3370 options.setdefault("max_value", 0)
3371 options.setdefault("min_color", Color("#F8696B"))
3372 options.setdefault("mid_color", Color("#FFEB84"))
3373 options.setdefault("max_color", Color("#63BE7B"))
3375 options["min_color"] = Color._from_value(options["min_color"])
3376 options["mid_color"] = Color._from_value(options["mid_color"])
3377 options["max_color"] = Color._from_value(options["max_color"])
3379 # Set a default mid value.
3380 if "mid_value" not in options:
3381 options["mid_value"] = 50
3383 # Special handling for data bar.
3384 if options["type"] == "dataBar":
3385 # Color scales don't use any additional formatting.
3386 options["format"] = None
3388 if not options.get("min_type"):
3389 options["min_type"] = "min"
3390 options["x14_min_type"] = "autoMin"
3391 else:
3392 options["x14_min_type"] = options["min_type"]
3394 if not options.get("max_type"):
3395 options["max_type"] = "max"
3396 options["x14_max_type"] = "autoMax"
3397 else:
3398 options["x14_max_type"] = options["max_type"]
3400 options.setdefault("min_value", 0)
3401 options.setdefault("max_value", 0)
3402 options.setdefault("bar_color", Color("#638EC6"))
3403 options.setdefault("bar_border_color", options["bar_color"])
3404 options.setdefault("bar_only", False)
3405 options.setdefault("bar_no_border", False)
3406 options.setdefault("bar_solid", False)
3407 options.setdefault("bar_direction", "")
3408 options.setdefault("bar_negative_color", Color("#FF0000"))
3409 options.setdefault("bar_negative_border_color", Color("#FF0000"))
3410 options.setdefault("bar_negative_color_same", False)
3411 options.setdefault("bar_negative_border_color_same", False)
3412 options.setdefault("bar_axis_position", "")
3413 options.setdefault("bar_axis_color", Color("#000000"))
3415 options["bar_color"] = Color._from_value(options["bar_color"])
3416 options["bar_border_color"] = Color._from_value(options["bar_border_color"])
3417 options["bar_axis_color"] = Color._from_value(options["bar_axis_color"])
3418 options["bar_negative_color"] = Color._from_value(
3419 options["bar_negative_color"]
3420 )
3421 options["bar_negative_border_color"] = Color._from_value(
3422 options["bar_negative_border_color"]
3423 )
3425 # Adjust for 2010 style data_bar parameters.
3426 if options.get("is_data_bar_2010"):
3427 self.excel_version = 2010
3429 if options["min_type"] == "min" and options["min_value"] == 0:
3430 options["min_value"] = None
3432 if options["max_type"] == "max" and options["max_value"] == 0:
3433 options["max_value"] = None
3435 options["range"] = cell_range
3437 # Strip the leading = from formulas.
3438 try:
3439 options["min_value"] = options["min_value"].lstrip("=")
3440 except (KeyError, AttributeError):
3441 pass
3442 try:
3443 options["mid_value"] = options["mid_value"].lstrip("=")
3444 except (KeyError, AttributeError):
3445 pass
3446 try:
3447 options["max_value"] = options["max_value"].lstrip("=")
3448 except (KeyError, AttributeError):
3449 pass
3451 # Store the conditional format until we close the worksheet.
3452 if cell_range in self.cond_formats:
3453 self.cond_formats[cell_range].append(options)
3454 else:
3455 self.cond_formats[cell_range] = [options]
3457 return 0
3459 @convert_range_args
3460 def add_table(
3461 self,
3462 first_row: int,
3463 first_col: int,
3464 last_row: int,
3465 last_col: int,
3466 options: Optional[Dict[str, Any]] = None,
3467 ) -> Literal[0, -1, -2, -3]:
3468 """
3469 Add an Excel table to a worksheet.
3471 Args:
3472 first_row: The first row of the cell range. (zero indexed).
3473 first_col: The first column of the cell range.
3474 last_row: The last row of the cell range. (zero indexed).
3475 last_col: The last column of the cell range.
3476 options: Table format options. (Optional)
3478 Returns:
3479 0: Success.
3480 -1: Row or column is out of worksheet bounds.
3481 -2: Incorrect parameter or option.
3482 -3: Not supported in constant_memory mode.
3483 """
3484 table = {}
3485 col_formats = {}
3487 if options is None:
3488 options = {}
3489 else:
3490 # Copy the user defined options so they aren't modified.
3491 options = options.copy()
3493 if self.constant_memory:
3494 warn("add_table() isn't supported in 'constant_memory' mode")
3495 return -3
3497 # Check that row and col are valid without storing the values.
3498 if self._check_dimensions(first_row, first_col, True, True):
3499 return -1
3500 if self._check_dimensions(last_row, last_col, True, True):
3501 return -1
3503 # Swap last row/col for first row/col as necessary.
3504 if first_row > last_row:
3505 first_row, last_row = (last_row, first_row)
3506 if first_col > last_col:
3507 first_col, last_col = (last_col, first_col)
3509 # Check if the table range overlaps a previous merged or table range.
3510 # This is a critical file corruption error in Excel.
3511 cell_range = xl_range(first_row, first_col, last_row, last_col)
3512 for row in range(first_row, last_row + 1):
3513 for col in range(first_col, last_col + 1):
3514 if self.table_cells.get((row, col)):
3515 previous_range = self.table_cells.get((row, col))
3516 raise OverlappingRange(
3517 f"Table range '{cell_range}' overlaps previous "
3518 f"table range '{previous_range}'."
3519 )
3521 if self.merged_cells.get((row, col)):
3522 previous_range = self.merged_cells.get((row, col))
3523 raise OverlappingRange(
3524 f"Table range '{cell_range}' overlaps previous "
3525 f"merge range '{previous_range}'."
3526 )
3528 self.table_cells[(row, col)] = cell_range
3530 # Valid input parameters.
3531 valid_parameter = {
3532 "autofilter",
3533 "banded_columns",
3534 "banded_rows",
3535 "columns",
3536 "data",
3537 "first_column",
3538 "header_row",
3539 "last_column",
3540 "name",
3541 "style",
3542 "total_row",
3543 "description",
3544 "title",
3545 }
3547 # Check for valid input parameters.
3548 for param_key in options:
3549 if param_key not in valid_parameter:
3550 warn(f"Unknown parameter '{param_key}' in add_table()")
3551 return -2
3553 # Turn on Excel's defaults.
3554 options["banded_rows"] = options.get("banded_rows", True)
3555 options["header_row"] = options.get("header_row", True)
3556 options["autofilter"] = options.get("autofilter", True)
3558 # Check that there are enough rows.
3559 num_rows = last_row - first_row
3560 if options["header_row"]:
3561 num_rows -= 1
3563 if num_rows < 0:
3564 warn("Must have at least one data row in add_table()")
3565 return -2
3567 # Set the table options.
3568 table["show_first_col"] = options.get("first_column", False)
3569 table["show_last_col"] = options.get("last_column", False)
3570 table["show_row_stripes"] = options.get("banded_rows", False)
3571 table["show_col_stripes"] = options.get("banded_columns", False)
3572 table["header_row_count"] = options.get("header_row", 0)
3573 table["totals_row_shown"] = options.get("total_row", False)
3574 table["description"] = options.get("description")
3575 table["title"] = options.get("title")
3577 # Set the table name.
3578 if "name" in options:
3579 name = options["name"]
3580 table["name"] = name
3582 if " " in name:
3583 warn(f"Name '{name}' in add_table() cannot contain spaces")
3584 return -2
3586 # Warn if the name contains invalid chars as defined by Excel.
3587 if _has_invalid_name_chars(name):
3588 warn(f"Invalid Excel characters in add_table(): '{name}'")
3589 return -2
3591 # Warn if the name exceeds Excel's maximum length.
3592 if _utf16_len(name) > 255:
3593 warn(
3594 f"Name '{name}' in add_table() exceeds Excel's limit of 255 "
3595 f"characters"
3596 )
3597 return -2
3599 # Warn if the name looks like a cell name.
3600 if re.match(r"^[a-zA-Z][a-zA-Z]?[a-dA-D]?\d+$", name):
3601 warn(f"Name looks like a cell name in add_table(): '{name}'")
3602 return -2
3604 # Warn if the name looks like a R1C1 cell reference.
3605 if re.match(r"^[rcRC]$", name) or re.match(r"^[rcRC]\d+[rcRC]\d+$", name):
3606 warn(f"Invalid name '{name}' like a RC cell ref in add_table()")
3607 return -2
3609 # Set the table style.
3610 if "style" in options:
3611 table["style"] = options["style"]
3613 if table["style"] is None:
3614 table["style"] = ""
3616 # Remove whitespace from style name.
3617 table["style"] = table["style"].replace(" ", "")
3618 else:
3619 table["style"] = "TableStyleMedium9"
3621 # Set the data range rows (without the header and footer).
3622 first_data_row = first_row
3623 last_data_row = last_row
3625 if options.get("header_row"):
3626 first_data_row += 1
3628 if options.get("total_row"):
3629 last_data_row -= 1
3631 # Set the table and autofilter ranges.
3632 table["range"] = xl_range(first_row, first_col, last_row, last_col)
3634 table["a_range"] = xl_range(first_row, first_col, last_data_row, last_col)
3636 # If the header row if off the default is to turn autofilter off.
3637 if not options["header_row"]:
3638 options["autofilter"] = 0
3640 # Set the autofilter range.
3641 if options["autofilter"]:
3642 table["autofilter"] = table["a_range"]
3644 # Excel requires unique case insensitive header names. Check the user
3645 # defined and default names before any table data is written.
3646 seen_names = set()
3647 user_columns = options.get("columns", [])
3649 for col_id in range(1, last_col - first_col + 2):
3650 name = "Column" + str(col_id)
3652 if col_id <= len(user_columns):
3653 user_data = user_columns[col_id - 1]
3654 if user_data and user_data.get("header"):
3655 name = user_data["header"]
3657 name = name.lower()
3658 if name in seen_names:
3659 warn(f"Duplicate header name in add_table(): '{name}'")
3660 return -2
3662 seen_names.add(name)
3664 # Add the table columns.
3665 col_id = 1
3666 table["columns"] = []
3668 for col_num in range(first_col, last_col + 1):
3669 # Set up the default column data.
3670 col_data = {
3671 "id": col_id,
3672 "name": "Column" + str(col_id),
3673 "total_string": "",
3674 "total_function": "",
3675 "custom_total": "",
3676 "total_value": 0,
3677 "formula": "",
3678 "format": None,
3679 "name_format": None,
3680 }
3682 # Overwrite the defaults with any user defined values.
3683 if "columns" in options:
3684 # Check if there are user defined values for this column.
3685 if col_id <= len(options["columns"]):
3686 user_data = options["columns"][col_id - 1]
3687 else:
3688 user_data = None
3690 if user_data:
3691 # Get the column format.
3692 xformat = user_data.get("format", None)
3694 # Map user defined values to internal values.
3695 if user_data.get("header"):
3696 col_data["name"] = user_data["header"]
3698 col_data["name_format"] = user_data.get("header_format")
3700 # Handle the column formula.
3701 if user_data.get("formula"):
3702 formula = user_data["formula"]
3704 # Remove the formula '=' sign if it exists.
3705 if formula.startswith("="):
3706 formula = formula.lstrip("=")
3708 # Convert Excel 2010 "@" ref to 2007 "#This Row".
3709 formula = self._prepare_table_formula(formula)
3711 # Escape any future functions.
3712 formula = self._prepare_formula(formula, True)
3714 col_data["formula"] = formula
3715 # We write the formulas below after the table data.
3717 # Handle the function for the total row.
3718 if user_data.get("total_function"):
3719 function = user_data["total_function"]
3720 if function == "count_nums":
3721 function = "countNums"
3722 if function == "std_dev":
3723 function = "stdDev"
3725 subtotals = {
3726 "average",
3727 "countNums",
3728 "count",
3729 "max",
3730 "min",
3731 "stdDev",
3732 "sum",
3733 "var",
3734 }
3736 if function in subtotals:
3737 formula = self._table_function_to_formula(
3738 function, col_data["name"]
3739 )
3740 else:
3741 formula = self._prepare_formula(function, True)
3742 col_data["custom_total"] = formula
3743 function = "custom"
3745 col_data["total_function"] = function
3747 value = user_data.get("total_value", 0)
3749 self._write_formula(last_row, col_num, formula, xformat, value)
3751 elif user_data.get("total_string"):
3752 # Total label only (not a function).
3753 total_string = user_data["total_string"]
3754 col_data["total_string"] = total_string
3756 self._write_string(
3757 last_row, col_num, total_string, user_data.get("format")
3758 )
3760 # Get the dxf format index.
3761 if xformat is not None:
3762 col_data["format"] = xformat._get_dxf_index()
3764 # Store the column format for writing the cell data.
3765 # It doesn't matter if it is undefined.
3766 col_formats[col_id - 1] = xformat
3768 # Store the column data.
3769 table["columns"].append(col_data)
3771 # Write the column headers to the worksheet.
3772 if options["header_row"]:
3773 self._write_string(
3774 first_row, col_num, col_data["name"], col_data["name_format"]
3775 )
3777 col_id += 1
3779 # Write the cell data if supplied.
3780 if "data" in options:
3781 data = options["data"]
3783 # Index the row and col data with i and j.
3784 for i, row in enumerate(range(first_data_row, last_data_row + 1)):
3785 for j, col in enumerate(range(first_col, last_col + 1)):
3786 if i < len(data) and j < len(data[i]):
3787 token = data[i][j]
3788 if j in col_formats:
3789 self._write(row, col, token, col_formats[j])
3790 else:
3791 self._write(row, col, token, None)
3793 # Write any columns formulas after the user supplied table data to
3794 # overwrite it if required.
3795 for col_id, col_num in enumerate(range(first_col, last_col + 1)):
3796 column_data = table["columns"][col_id]
3797 if column_data and column_data["formula"]:
3798 formula_format = col_formats.get(col_id)
3799 formula = column_data["formula"]
3801 for row in range(first_data_row, last_data_row + 1):
3802 self._write_formula(row, col_num, formula, formula_format)
3804 # Store the table data.
3805 self.tables.append(table)
3807 # Store the filter cell positions for use in the autofit calculation.
3808 if options["autofilter"]:
3809 for col in range(first_col, last_col + 1):
3810 # Check that the table autofilter doesn't overlap a worksheet filter.
3811 if self.filter_cells.get((first_row, col)):
3812 filter_type, filter_range = self.filter_cells.get((first_row, col))
3813 if filter_type == "worksheet":
3814 raise OverlappingRange(
3815 f"Table autofilter range '{cell_range}' overlaps previous "
3816 f"Worksheet autofilter range '{filter_range}'."
3817 )
3819 self.filter_cells[(first_row, col)] = ("table", cell_range)
3821 return 0
3823 @convert_cell_args
3824 def add_sparkline(
3825 self, row: int, col: int, options: Optional[Dict[str, Any]] = None
3826 ) -> Literal[0, -1, -2]:
3827 """
3828 Add sparklines to the worksheet.
3830 Args:
3831 row: The cell row (zero indexed).
3832 col: The cell column (zero indexed).
3833 options: Sparkline formatting options.
3835 Returns:
3836 0: Success.
3837 -1: Row or column is out of worksheet bounds.
3838 -2: Incorrect parameter or option.
3840 """
3842 # Check that row and col are valid without storing the values.
3843 if self._check_dimensions(row, col, True, True):
3844 return -1
3846 sparkline = {"locations": [xl_rowcol_to_cell(row, col)]}
3848 if options is None:
3849 options = {}
3851 # Valid input parameters.
3852 valid_parameters = {
3853 "location",
3854 "range",
3855 "type",
3856 "high_point",
3857 "low_point",
3858 "negative_points",
3859 "first_point",
3860 "last_point",
3861 "markers",
3862 "style",
3863 "series_color",
3864 "negative_color",
3865 "markers_color",
3866 "first_color",
3867 "last_color",
3868 "high_color",
3869 "low_color",
3870 "max",
3871 "min",
3872 "axis",
3873 "reverse",
3874 "empty_cells",
3875 "show_hidden",
3876 "plot_hidden",
3877 "date_axis",
3878 "weight",
3879 }
3881 # Check for valid input parameters.
3882 for param_key in options:
3883 if param_key not in valid_parameters:
3884 warn(f"Unknown parameter '{param_key}' in add_sparkline()")
3885 return -1
3887 # 'range' is a required parameter.
3888 if "range" not in options:
3889 warn("Parameter 'range' is required in add_sparkline()")
3890 return -2
3892 # Handle the sparkline type.
3893 spark_type = options.get("type", "line")
3895 if spark_type not in ("line", "column", "win_loss"):
3896 warn(
3897 "Parameter 'type' must be 'line', 'column' "
3898 "or 'win_loss' in add_sparkline()"
3899 )
3900 return -2
3902 if spark_type == "win_loss":
3903 spark_type = "stacked"
3904 sparkline["type"] = spark_type
3906 # We handle single location/range values or list of values.
3907 if "location" in options:
3908 if isinstance(options["location"], list):
3909 sparkline["locations"] = options["location"]
3910 else:
3911 sparkline["locations"] = [options["location"]]
3913 if isinstance(options["range"], list):
3914 sparkline["ranges"] = options["range"]
3915 else:
3916 sparkline["ranges"] = [options["range"]]
3918 range_count = len(sparkline["ranges"])
3919 location_count = len(sparkline["locations"])
3921 # The ranges and locations must match.
3922 if range_count != location_count:
3923 warn(
3924 "Must have the same number of location and range "
3925 "parameters in add_sparkline()"
3926 )
3927 return -2
3929 # Store the count.
3930 sparkline["count"] = len(sparkline["locations"])
3932 # Get the worksheet name for the range conversion below.
3933 sheetname = quote_sheetname(self.name)
3935 # Cleanup the input ranges.
3936 new_ranges = []
3937 for spark_range in sparkline["ranges"]:
3938 # Remove the absolute reference $ symbols.
3939 spark_range = spark_range.replace("$", "")
3941 # Remove the = from formula.
3942 spark_range = spark_range.lstrip("=")
3944 # Convert a simple range into a full Sheet1!A1:D1 range.
3945 if "!" not in spark_range:
3946 spark_range = sheetname + "!" + spark_range
3948 new_ranges.append(spark_range)
3950 sparkline["ranges"] = new_ranges
3952 # Cleanup the input locations.
3953 new_locations = []
3954 for location in sparkline["locations"]:
3955 location = location.replace("$", "")
3956 new_locations.append(location)
3958 sparkline["locations"] = new_locations
3960 # Map options.
3961 sparkline["high"] = options.get("high_point")
3962 sparkline["low"] = options.get("low_point")
3963 sparkline["negative"] = options.get("negative_points")
3964 sparkline["first"] = options.get("first_point")
3965 sparkline["last"] = options.get("last_point")
3966 sparkline["markers"] = options.get("markers")
3967 sparkline["min"] = options.get("min")
3968 sparkline["max"] = options.get("max")
3969 sparkline["axis"] = options.get("axis")
3970 sparkline["reverse"] = options.get("reverse")
3971 sparkline["hidden"] = options.get("show_hidden")
3972 sparkline["weight"] = options.get("weight")
3974 # Map empty cells options.
3975 empty = options.get("empty_cells", "")
3977 if empty == "zero":
3978 sparkline["empty"] = 0
3979 elif empty == "connect":
3980 sparkline["empty"] = "span"
3981 else:
3982 sparkline["empty"] = "gap"
3984 # Map the date axis range.
3985 date_range = options.get("date_axis")
3987 if date_range and "!" not in date_range:
3988 date_range = sheetname + "!" + date_range
3990 sparkline["date_axis"] = date_range
3992 # Set the sparkline styles.
3993 style_id = options.get("style", 0)
3994 style = _get_sparkline_style(style_id)
3996 sparkline["series_color"] = style["series"]
3997 sparkline["negative_color"] = style["negative"]
3998 sparkline["markers_color"] = style["markers"]
3999 sparkline["first_color"] = style["first"]
4000 sparkline["last_color"] = style["last"]
4001 sparkline["high_color"] = style["high"]
4002 sparkline["low_color"] = style["low"]
4004 # Override the style colors with user defined colors.
4005 self._set_spark_color(sparkline, options, "series_color")
4006 self._set_spark_color(sparkline, options, "negative_color")
4007 self._set_spark_color(sparkline, options, "markers_color")
4008 self._set_spark_color(sparkline, options, "first_color")
4009 self._set_spark_color(sparkline, options, "last_color")
4010 self._set_spark_color(sparkline, options, "high_color")
4011 self._set_spark_color(sparkline, options, "low_color")
4013 self.sparklines.append(sparkline)
4015 return 0
4017 @convert_range_args
4018 def set_selection(
4019 self, first_row: int, first_col: int, last_row: int, last_col: int
4020 ) -> None:
4021 """
4022 Set the selected cell or cells in a worksheet
4024 Args:
4025 first_row: The first row of the cell range. (zero indexed).
4026 first_col: The first column of the cell range.
4027 last_row: The last row of the cell range. (zero indexed).
4028 last_col: The last column of the cell range.
4030 Returns:
4031 0: Nothing.
4032 """
4033 pane = None
4035 # Range selection. Do this before swapping max/min to allow the
4036 # selection direction to be reversed.
4037 active_cell = xl_rowcol_to_cell(first_row, first_col)
4039 # Swap last row/col for first row/col if necessary
4040 if first_row > last_row:
4041 first_row, last_row = (last_row, first_row)
4043 if first_col > last_col:
4044 first_col, last_col = (last_col, first_col)
4046 sqref = xl_range(first_row, first_col, last_row, last_col)
4048 # Selection isn't set for cell A1.
4049 if sqref == "A1":
4050 return
4052 self.selections = [[pane, active_cell, sqref]]
4054 @convert_cell_args
4055 def set_top_left_cell(self, row: int = 0, col: int = 0) -> None:
4056 """
4057 Set the first visible cell at the top left of a worksheet.
4059 Args:
4060 row: The cell row (zero indexed).
4061 col: The cell column (zero indexed).
4063 Returns:
4064 0: Nothing.
4065 """
4067 if row == 0 and col == 0:
4068 return
4070 self.top_left_cell = xl_rowcol_to_cell(row, col)
4072 def outline_settings(
4073 self,
4074 visible: bool = 1,
4075 symbols_below: bool = 1,
4076 symbols_right: bool = 1,
4077 auto_style: bool = 0,
4078 ) -> None:
4079 """
4080 Control outline settings.
4082 Args:
4083 visible: Outlines are visible. Optional, defaults to True.
4084 symbols_below: Show row outline symbols below the outline bar.
4085 Optional, defaults to True.
4086 symbols_right: Show column outline symbols to the right of the
4087 outline bar. Optional, defaults to True.
4088 auto_style: Use Automatic style. Optional, defaults to False.
4090 Returns:
4091 0: Nothing.
4092 """
4093 self.outline_on = visible
4094 self.outline_below = symbols_below
4095 self.outline_right = symbols_right
4096 self.outline_style = auto_style
4098 self.outline_changed = True
4100 @convert_cell_args
4101 def freeze_panes(
4102 self,
4103 row: int,
4104 col: int,
4105 top_row: Optional[int] = None,
4106 left_col: Optional[int] = None,
4107 pane_type: int = 0,
4108 ) -> None:
4109 """
4110 Create worksheet panes and mark them as frozen.
4112 Args:
4113 row: The cell row (zero indexed).
4114 col: The cell column (zero indexed).
4115 top_row: Topmost visible row in scrolling region of pane.
4116 left_col: Leftmost visible row in scrolling region of pane.
4118 Returns:
4119 0: Nothing.
4121 """
4122 if top_row is None:
4123 top_row = row
4125 if left_col is None:
4126 left_col = col
4128 self.panes = [row, col, top_row, left_col, pane_type]
4130 @convert_cell_args
4131 def split_panes(
4132 self,
4133 x: float,
4134 y: float,
4135 top_row: Optional[int] = None,
4136 left_col: Optional[int] = None,
4137 ) -> None:
4138 """
4139 Create worksheet panes and mark them as split.
4141 Args:
4142 x: The position for the vertical split.
4143 y: The position for the horizontal split.
4144 top_row: Topmost visible row in scrolling region of pane.
4145 left_col: Leftmost visible row in scrolling region of pane.
4147 Returns:
4148 0: Nothing.
4150 """
4151 # Same as freeze panes with a different pane type.
4152 self.freeze_panes(x, y, top_row, left_col, 2)
4154 def set_zoom(self, zoom: int = 100) -> None:
4155 """
4156 Set the worksheet zoom factor.
4158 Args:
4159 zoom: Scale factor: 10 <= zoom <= 400.
4161 Returns:
4162 Nothing.
4164 """
4165 # Ensure the zoom scale is in Excel's range.
4166 if zoom < 10 or zoom > 400:
4167 warn(f"Zoom factor '{zoom}' outside range: 10 <= zoom <= 400")
4168 zoom = 100
4170 self.zoom = int(zoom)
4172 def set_zoom_to_fit(self) -> None:
4173 """
4174 Set the worksheet zoom to selection/fit. Only works for chartsheets.
4176 Args:
4177 None.
4179 Returns:
4180 Nothing.
4182 """
4183 self.zoom_to_fit = True
4185 def right_to_left(self) -> None:
4186 """
4187 Display the worksheet right to left for some versions of Excel.
4189 Args:
4190 None.
4192 Returns:
4193 Nothing.
4195 """
4196 self.is_right_to_left = True
4198 def hide_zero(self) -> None:
4199 """
4200 Hide zero values in worksheet cells.
4202 Args:
4203 None.
4205 Returns:
4206 Nothing.
4208 """
4209 self.show_zeros = 0
4211 def set_tab_color(self, color: Union[str, Color]) -> None:
4212 """
4213 Set the color of the worksheet tab.
4215 Args:
4216 color: A #RGB color index.
4218 Returns:
4219 Nothing.
4221 """
4222 self.tab_color = Color._from_value(color)
4224 def protect(
4225 self, password: str = "", options: Optional[Dict[str, Any]] = None
4226 ) -> None:
4227 """
4228 Set the password and protection options of the worksheet.
4230 Args:
4231 password: An optional password string.
4232 options: A dictionary of worksheet objects to protect.
4234 Returns:
4235 Nothing.
4237 """
4238 if password != "":
4239 password = self._encode_password(password)
4241 if not options:
4242 options = {}
4244 # Default values for objects that can be protected.
4245 defaults = {
4246 "sheet": True,
4247 "content": False,
4248 "objects": False,
4249 "scenarios": False,
4250 "format_cells": False,
4251 "format_columns": False,
4252 "format_rows": False,
4253 "insert_columns": False,
4254 "insert_rows": False,
4255 "insert_hyperlinks": False,
4256 "delete_columns": False,
4257 "delete_rows": False,
4258 "select_locked_cells": True,
4259 "sort": False,
4260 "autofilter": False,
4261 "pivot_tables": False,
4262 "select_unlocked_cells": True,
4263 }
4265 # Overwrite the defaults with user specified values.
4266 for key in options:
4267 if key in defaults:
4268 defaults[key] = options[key]
4269 else:
4270 warn(f"Unknown protection object: '{key}'")
4272 # Set the password after the user defined values.
4273 defaults["password"] = password
4275 self.protect_options = defaults
4277 def unprotect_range(
4278 self,
4279 cell_range: str,
4280 range_name: Optional[str] = None,
4281 password: Optional[str] = None,
4282 ) -> int:
4283 """
4284 Unprotect ranges within a protected worksheet.
4286 Args:
4287 cell_range: The cell or cell range to unprotect.
4288 range_name: An optional name for the range.
4289 password: An optional password string. (undocumented)
4291 Returns:
4292 0: Success.
4293 -1: Parameter error.
4295 """
4296 if cell_range is None:
4297 warn("Cell range must be specified in unprotect_range()")
4298 return -1
4300 # Sanitize the cell range.
4301 cell_range = cell_range.lstrip("=")
4302 cell_range = cell_range.replace("$", "")
4304 self.num_protected_ranges += 1
4306 if range_name is None:
4307 range_name = "Range" + str(self.num_protected_ranges)
4309 if password:
4310 password = self._encode_password(password)
4312 self.protected_ranges.append((cell_range, range_name, password))
4314 return 0
4316 @convert_cell_args
4317 def insert_button(
4318 self, row: int, col: int, options: Optional[Dict[str, Any]] = None
4319 ) -> Literal[0, -1]:
4320 """
4321 Insert a button form object into the worksheet.
4323 Args:
4324 row: The cell row (zero indexed).
4325 col: The cell column (zero indexed).
4326 options: Button formatting options.
4328 Returns:
4329 0: Success.
4330 -1: Row or column is out of worksheet bounds.
4332 """
4333 # Check insert (row, col) without storing.
4334 if self._check_dimensions(row, col, True, True):
4335 warn(f"Cannot insert button at ({row}, {col}).")
4336 return -1
4338 if options is None:
4339 options = {}
4341 # Create a new button object.
4342 height = self.default_row_height
4343 width = self.default_col_width
4344 button_number = 1 + len(self.buttons_list)
4346 button = ButtonType(row, col, height, width, button_number, options)
4348 self.buttons_list.append(button)
4350 self.has_vml = True
4352 return 0
4354 @convert_cell_args
4355 def insert_checkbox(
4356 self, row: int, col: int, boolean: bool, cell_format: Optional[Format] = None
4357 ):
4358 """
4359 Insert a boolean checkbox in a worksheet cell.
4361 Args:
4362 row: The cell row (zero indexed).
4363 col: The cell column (zero indexed).
4364 boolean: The boolean value to display as a checkbox.
4365 cell_format: Cell Format object. (optional)
4367 Returns:
4368 0: Success.
4369 -1: Row or column is out of worksheet bounds.
4371 """
4372 # Ensure that the checkbox property is set in the user defined format.
4373 if cell_format and not cell_format.checkbox:
4374 # This needs to be fixed with a clone.
4375 cell_format.set_checkbox()
4377 # If no format is supplied create and/or use the default checkbox format.
4378 if not cell_format:
4379 if not self.default_checkbox_format:
4380 self.default_checkbox_format = self.workbook_add_format()
4381 self.default_checkbox_format.set_checkbox()
4383 cell_format = self.default_checkbox_format
4385 return self._write_boolean(row, col, boolean, cell_format)
4387 ###########################################################################
4388 #
4389 # Public API. Page Setup methods.
4390 #
4391 ###########################################################################
4392 def set_landscape(self) -> None:
4393 """
4394 Set the page orientation as landscape.
4396 Args:
4397 None.
4399 Returns:
4400 Nothing.
4402 """
4403 self.orientation = 0
4404 self.page_setup_changed = True
4406 def set_portrait(self) -> None:
4407 """
4408 Set the page orientation as portrait.
4410 Args:
4411 None.
4413 Returns:
4414 Nothing.
4416 """
4417 self.orientation = 1
4418 self.page_setup_changed = True
4420 def set_page_view(self, view: Literal[0, 1, 2] = 1) -> None:
4421 """
4422 Set the page view mode.
4424 Args:
4425 0: Normal view mode
4426 1: Page view mode (the default)
4427 2: Page break view mode
4429 Returns:
4430 Nothing.
4432 """
4433 self.page_view = view
4435 def set_pagebreak_view(self) -> None:
4436 """
4437 Set the page view mode.
4439 Args:
4440 None.
4442 Returns:
4443 Nothing.
4445 """
4446 self.page_view = 2
4448 def set_paper(self, paper_size: Union[Literal[1, 9], int]) -> None:
4449 """
4450 Set the paper type. US Letter = 1, A4 = 9.
4452 Args:
4453 paper_size: Paper index.
4455 Returns:
4456 Nothing.
4458 """
4459 if paper_size:
4460 self.paper_size = paper_size
4461 self.page_setup_changed = True
4463 def center_horizontally(self) -> None:
4464 """
4465 Center the page horizontally.
4467 Args:
4468 None.
4470 Returns:
4471 Nothing.
4473 """
4474 self.print_options_changed = True
4475 self.hcenter = 1
4477 def center_vertically(self) -> None:
4478 """
4479 Center the page vertically.
4481 Args:
4482 None.
4484 Returns:
4485 Nothing.
4487 """
4488 self.print_options_changed = True
4489 self.vcenter = 1
4491 def set_margins(
4492 self,
4493 left: float = 0.7,
4494 right: float = 0.7,
4495 top: float = 0.75,
4496 bottom: float = 0.75,
4497 ) -> None:
4498 """
4499 Set all the page margins in inches.
4501 Args:
4502 left: Left margin.
4503 right: Right margin.
4504 top: Top margin.
4505 bottom: Bottom margin.
4507 Returns:
4508 Nothing.
4510 """
4511 self.margin_left = left
4512 self.margin_right = right
4513 self.margin_top = top
4514 self.margin_bottom = bottom
4516 def set_header(
4517 self, header: str = "", options: Optional[Dict[str, Any]] = None, margin=None
4518 ) -> None:
4519 """
4520 Set the page header caption and optional margin.
4522 Args:
4523 header: Header string.
4524 margin: Header margin.
4525 options: Header options, mainly for images.
4527 Returns:
4528 Nothing.
4530 """
4531 header_orig = header
4532 header = header.replace("&[Picture]", "&G")
4534 if _utf16_len(header) > 255:
4535 warn("Header string cannot be longer than Excel's limit of 255 characters")
4536 return
4538 if options is not None:
4539 # For backward compatibility allow options to be the margin.
4540 if not isinstance(options, dict):
4541 options = {"margin": options}
4542 else:
4543 options = {}
4545 # Copy the user defined options so they aren't modified.
4546 options = options.copy()
4548 # For backward compatibility.
4549 if margin is not None:
4550 options["margin"] = margin
4552 # Reset the list in case the function is called more than once.
4553 self.header_images = []
4555 if options.get("image_left"):
4556 options["image_data"] = options.get("image_data_left")
4557 image = self._image_from_source(options.get("image_left"), options)
4558 image._header_position = "LH"
4559 self.header_images.append(image)
4561 if options.get("image_center"):
4562 options["image_data"] = options.get("image_data_center")
4563 image = self._image_from_source(options.get("image_center"), options)
4564 image._header_position = "CH"
4565 self.header_images.append(image)
4567 if options.get("image_right"):
4568 options["image_data"] = options.get("image_data_right")
4569 image = self._image_from_source(options.get("image_right"), options)
4570 image._header_position = "RH"
4571 self.header_images.append(image)
4573 placeholder_count = header.count("&G")
4574 image_count = len(self.header_images)
4576 if placeholder_count != image_count:
4577 warn(
4578 f"Number of footer images '{image_count}' doesn't match placeholder "
4579 f"count '{placeholder_count}' in string: {header_orig}"
4580 )
4581 self.header_images = []
4582 return
4584 if "align_with_margins" in options:
4585 self.header_footer_aligns = options["align_with_margins"]
4587 if "scale_with_doc" in options:
4588 self.header_footer_scales = options["scale_with_doc"]
4590 self.header = header
4591 self.margin_header = options.get("margin", 0.3)
4592 self.header_footer_changed = True
4594 if image_count:
4595 self.has_header_vml = True
4597 def set_footer(
4598 self, footer: str = "", options: Optional[Dict[str, Any]] = None, margin=None
4599 ) -> None:
4600 """
4601 Set the page footer caption and optional margin.
4603 Args:
4604 footer: Footer string.
4605 margin: Footer margin.
4606 options: Footer options, mainly for images.
4608 Returns:
4609 Nothing.
4611 """
4612 footer_orig = footer
4613 footer = footer.replace("&[Picture]", "&G")
4615 if _utf16_len(footer) > 255:
4616 warn("Footer string cannot be longer than Excel's limit of 255 characters")
4617 return
4619 if options is not None:
4620 # For backward compatibility allow options to be the margin.
4621 if not isinstance(options, dict):
4622 options = {"margin": options}
4623 else:
4624 options = {}
4626 # Copy the user defined options so they aren't modified.
4627 options = options.copy()
4629 # For backward compatibility.
4630 if margin is not None:
4631 options["margin"] = margin
4633 # Reset the list in case the function is called more than once.
4634 self.footer_images = []
4636 if options.get("image_left"):
4637 options["image_data"] = options.get("image_data_left")
4638 image = self._image_from_source(options.get("image_left"), options)
4639 image._header_position = "LF"
4640 self.footer_images.append(image)
4642 if options.get("image_center"):
4643 options["image_data"] = options.get("image_data_center")
4644 image = self._image_from_source(options.get("image_center"), options)
4645 image._header_position = "CF"
4646 self.footer_images.append(image)
4648 if options.get("image_right"):
4649 options["image_data"] = options.get("image_data_right")
4650 image = self._image_from_source(options.get("image_right"), options)
4651 image._header_position = "RF"
4652 self.footer_images.append(image)
4654 placeholder_count = footer.count("&G")
4655 image_count = len(self.footer_images)
4657 if placeholder_count != image_count:
4658 warn(
4659 f"Number of footer images '{image_count}' doesn't match placeholder "
4660 f"count '{placeholder_count}' in string: {footer_orig}"
4661 )
4662 self.footer_images = []
4663 return
4665 if "align_with_margins" in options:
4666 self.header_footer_aligns = options["align_with_margins"]
4668 if "scale_with_doc" in options:
4669 self.header_footer_scales = options["scale_with_doc"]
4671 self.footer = footer
4672 self.margin_footer = options.get("margin", 0.3)
4673 self.header_footer_changed = True
4675 if image_count:
4676 self.has_header_vml = True
4678 def repeat_rows(self, first_row: int, last_row: Optional[int] = None) -> None:
4679 """
4680 Set the rows to repeat at the top of each printed page.
4682 Args:
4683 first_row: Start row for range.
4684 last_row: End row for range.
4686 Returns:
4687 Nothing.
4689 """
4690 if last_row is None:
4691 last_row = first_row
4693 # Convert rows to 1 based.
4694 first_row += 1
4695 last_row += 1
4697 # Create the row range area like: $1:$2.
4698 area = f"${first_row}:${last_row}"
4700 # Build up the print titles area "Sheet1!$1:$2"
4701 sheetname = quote_sheetname(self.name)
4702 self.repeat_row_range = sheetname + "!" + area
4704 @convert_column_args
4705 def repeat_columns(self, first_col: int, last_col: Optional[int] = None) -> None:
4706 """
4707 Set the columns to repeat at the left hand side of each printed page.
4709 Args:
4710 first_col: Start column for range.
4711 last_col: End column for range.
4713 Returns:
4714 Nothing.
4716 """
4717 if last_col is None:
4718 last_col = first_col
4720 # Convert to A notation.
4721 first_col = xl_col_to_name(first_col, 1)
4722 last_col = xl_col_to_name(last_col, 1)
4724 # Create a column range like $C:$D.
4725 area = first_col + ":" + last_col
4727 # Build up the print area range "=Sheet2!$C:$D"
4728 sheetname = quote_sheetname(self.name)
4729 self.repeat_col_range = sheetname + "!" + area
4731 def hide_gridlines(self, option: Literal[0, 1, 2] = 1) -> None:
4732 """
4733 Set the option to hide gridlines on the screen and the printed page.
4735 Args:
4736 option: 0 : Don't hide gridlines
4737 1 : Hide printed gridlines only
4738 2 : Hide screen and printed gridlines
4740 Returns:
4741 Nothing.
4743 """
4744 if option == 0:
4745 self.print_gridlines = 1
4746 self.screen_gridlines = 1
4747 self.print_options_changed = True
4748 elif option == 1:
4749 self.print_gridlines = 0
4750 self.screen_gridlines = 1
4751 else:
4752 self.print_gridlines = 0
4753 self.screen_gridlines = 0
4755 def print_row_col_headers(self) -> None:
4756 """
4757 Set the option to print the row and column headers on the printed page.
4759 Args:
4760 None.
4762 Returns:
4763 Nothing.
4765 """
4766 self.print_headers = True
4767 self.print_options_changed = True
4769 def hide_row_col_headers(self) -> None:
4770 """
4771 Set the option to hide the row and column headers on the worksheet.
4773 Args:
4774 None.
4776 Returns:
4777 Nothing.
4779 """
4780 self.row_col_headers = True
4782 @convert_range_args
4783 def print_area(
4784 self, first_row: int, first_col: int, last_row: int, last_col: int
4785 ) -> Literal[0, -1]:
4786 """
4787 Set the print area in the current worksheet.
4789 Args:
4790 first_row: The first row of the cell range. (zero indexed).
4791 first_col: The first column of the cell range.
4792 last_row: The last row of the cell range. (zero indexed).
4793 last_col: The last column of the cell range.
4795 Returns:
4796 0: Success.
4797 -1: Row or column is out of worksheet bounds.
4799 """
4800 # Set the print area in the current worksheet.
4802 # Ignore max print area since it is the same as no area for Excel.
4803 if (
4804 first_row == 0
4805 and first_col == 0
4806 and last_row == self.xls_rowmax - 1
4807 and last_col == self.xls_colmax - 1
4808 ):
4809 return -1
4811 # Build up the print area range "Sheet1!$A$1:$C$13".
4812 area = self._convert_name_area(first_row, first_col, last_row, last_col)
4813 self.print_area_range = area
4815 return 0
4817 def print_across(self) -> None:
4818 """
4819 Set the order in which pages are printed.
4821 Args:
4822 None.
4824 Returns:
4825 Nothing.
4827 """
4828 self.page_order = 1
4829 self.page_setup_changed = True
4831 def fit_to_pages(self, width: int, height: int) -> None:
4832 """
4833 Fit the printed area to a specific number of pages both vertically and
4834 horizontally.
4836 Args:
4837 width: Number of pages horizontally.
4838 height: Number of pages vertically.
4840 Returns:
4841 Nothing.
4843 """
4844 self.fit_page = 1
4845 self.fit_width = width
4846 self.fit_height = height
4847 self.page_setup_changed = True
4849 def set_start_page(self, start_page: int) -> None:
4850 """
4851 Set the start page number when printing.
4853 Args:
4854 start_page: Start page number.
4856 Returns:
4857 Nothing.
4859 """
4860 self.page_start = start_page
4862 def set_print_scale(self, scale: int) -> None:
4863 """
4864 Set the scale factor for the printed page.
4866 Args:
4867 scale: Print scale. 10 <= scale <= 400.
4869 Returns:
4870 Nothing.
4872 """
4873 # Confine the scale to Excel's range.
4874 if scale < 10 or scale > 400:
4875 warn(f"Print scale '{scale}' outside range: 10 <= scale <= 400")
4876 return
4878 # Turn off "fit to page" option when print scale is on.
4879 self.fit_page = 0
4881 self.print_scale = int(scale)
4882 self.page_setup_changed = True
4884 def print_black_and_white(self) -> None:
4885 """
4886 Set the option to print the worksheet in black and white.
4888 Args:
4889 None.
4891 Returns:
4892 Nothing.
4894 """
4895 self.black_white = True
4896 self.page_setup_changed = True
4898 def set_h_pagebreaks(self, breaks: List[int]) -> None:
4899 """
4900 Set the horizontal page breaks on a worksheet.
4902 Args:
4903 breaks: List of rows where the page breaks should be added.
4905 Returns:
4906 Nothing.
4908 """
4909 self.hbreaks = breaks
4911 def set_v_pagebreaks(self, breaks: List[int]) -> None:
4912 """
4913 Set the horizontal page breaks on a worksheet.
4915 Args:
4916 breaks: List of columns where the page breaks should be added.
4918 Returns:
4919 Nothing.
4921 """
4922 self.vbreaks = breaks
4924 def set_vba_name(self, name: Optional[str] = None) -> None:
4925 """
4926 Set the VBA name for the worksheet. By default this is the
4927 same as the sheet name: i.e., Sheet1 etc.
4929 Args:
4930 name: The VBA name for the worksheet.
4932 Returns:
4933 Nothing.
4935 """
4936 if name is not None:
4937 self.vba_codename = name
4938 else:
4939 self.vba_codename = "Sheet" + str(self.index + 1)
4941 def ignore_errors(self, options: Optional[Dict[str, Any]] = None) -> Literal[0, -1]:
4942 """
4943 Ignore various Excel errors/warnings in a worksheet for user defined
4944 ranges.
4946 Args:
4947 options: A dict of ignore errors keys with cell range values.
4949 Returns:
4950 0: Success.
4951 -1: Incorrect parameter or option.
4953 """
4954 if options is None:
4955 return -1
4957 # Copy the user defined options so they aren't modified.
4958 options = options.copy()
4960 # Valid input parameters.
4961 valid_parameters = {
4962 "number_stored_as_text",
4963 "eval_error",
4964 "formula_differs",
4965 "formula_range",
4966 "formula_unlocked",
4967 "empty_cell_reference",
4968 "list_data_validation",
4969 "calculated_column",
4970 "two_digit_text_year",
4971 }
4973 # Check for valid input parameters.
4974 for param_key in options:
4975 if param_key not in valid_parameters:
4976 warn(f"Unknown parameter '{param_key}' in ignore_errors()")
4977 return -1
4979 self.ignored_errors = options
4981 return 0
4983 ###########################################################################
4984 #
4985 # Private API.
4986 #
4987 ###########################################################################
4988 def _initialize(self, init_data) -> None:
4989 self.name = init_data["name"]
4990 self.index = init_data["index"]
4991 self.str_table = init_data["str_table"]
4992 self.worksheet_meta = init_data["worksheet_meta"]
4993 self.constant_memory = init_data["constant_memory"]
4994 self.tmpdir = init_data["tmpdir"]
4995 self.date_1904 = init_data["date_1904"]
4996 self.strings_to_numbers = init_data["strings_to_numbers"]
4997 self.strings_to_formulas = init_data["strings_to_formulas"]
4998 self.strings_to_urls = init_data["strings_to_urls"]
4999 self.nan_inf_to_errors = init_data["nan_inf_to_errors"]
5000 self.default_date_format = init_data["default_date_format"]
5001 self.default_url_format = init_data["default_url_format"]
5002 self.workbook_add_format = init_data["workbook_add_format"]
5003 self.excel2003_style = init_data["excel2003_style"]
5004 self.remove_timezone = init_data["remove_timezone"]
5005 self.max_url_length = init_data["max_url_length"]
5006 self.use_future_functions = init_data["use_future_functions"]
5007 self.embedded_images = init_data["embedded_images"]
5008 self.default_row_height = init_data["default_row_height"]
5009 self.default_col_width = init_data["default_col_width"]
5010 self.max_digit_width = init_data["max_digit_width"]
5011 self.cell_padding = init_data["cell_padding"]
5012 self.max_col_width = init_data["max_col_width"]
5014 self.original_row_height = self.default_row_height
5016 if self.excel2003_style:
5017 self.original_row_height = 17
5018 self.default_row_height = 17
5019 self.margin_left = 0.75
5020 self.margin_right = 0.75
5021 self.margin_top = 1
5022 self.margin_bottom = 1
5023 self.margin_header = 0.5
5024 self.margin_footer = 0.5
5025 self.header_footer_aligns = False
5027 # Open a temp filehandle to store row data in constant_memory mode.
5028 if self.constant_memory:
5029 # This is sub-optimal but we need to create a temp file
5030 # with utf8 encoding in Python < 3.
5031 fd, filename = tempfile.mkstemp(dir=self.tmpdir)
5032 os.close(fd)
5033 self.row_data_filename = filename
5034 # pylint: disable=consider-using-with
5035 self.row_data_fh = open( # noqa: SIM115
5036 filename, mode="w+", encoding="utf-8"
5037 )
5039 # Set as the worksheet filehandle until the file is assembled.
5040 self.fh = self.row_data_fh
5042 def _assemble_xml_file(self) -> None:
5043 # Assemble and write the XML file.
5045 # Write the XML declaration.
5046 self._xml_declaration()
5048 # Write the root worksheet element.
5049 self._write_worksheet()
5051 # Write the worksheet properties.
5052 self._write_sheet_pr()
5054 # Write the worksheet dimensions.
5055 self._write_dimension()
5057 # Write the sheet view properties.
5058 self._write_sheet_views()
5060 # Write the sheet format properties.
5061 self._write_sheet_format_pr()
5063 # Write the sheet column info.
5064 self._write_cols()
5066 # Write the worksheet data such as rows columns and cells.
5067 if not self.constant_memory:
5068 self._write_sheet_data()
5069 else:
5070 self._write_optimized_sheet_data()
5072 # Write the sheetProtection element.
5073 self._write_sheet_protection()
5075 # Write the protectedRanges element.
5076 self._write_protected_ranges()
5078 # Write the phoneticPr element.
5079 if self.excel2003_style:
5080 self._write_phonetic_pr()
5082 # Write the autoFilter element.
5083 self._write_auto_filter()
5085 # Write the mergeCells element.
5086 self._write_merge_cells()
5088 # Write the conditional formats.
5089 self._write_conditional_formats()
5091 # Write the dataValidations element.
5092 self._write_data_validations()
5094 # Write the hyperlink element.
5095 self._write_hyperlinks()
5097 # Write the printOptions element.
5098 self._write_print_options()
5100 # Write the worksheet page_margins.
5101 self._write_page_margins()
5103 # Write the worksheet page setup.
5104 self._write_page_setup()
5106 # Write the headerFooter element.
5107 self._write_header_footer()
5109 # Write the rowBreaks element.
5110 self._write_row_breaks()
5112 # Write the colBreaks element.
5113 self._write_col_breaks()
5115 # Write the ignoredErrors element.
5116 self._write_ignored_errors()
5118 # Write the drawing element.
5119 self._write_drawings()
5121 # Write the legacyDrawing element.
5122 self._write_legacy_drawing()
5124 # Write the legacyDrawingHF element.
5125 self._write_legacy_drawing_hf()
5127 # Write the picture element, for the background.
5128 self._write_picture()
5130 # Write the tableParts element.
5131 self._write_table_parts()
5133 # Write the extLst elements.
5134 self._write_ext_list()
5136 # Close the worksheet tag.
5137 self._xml_end_tag("worksheet")
5139 # Close the file.
5140 self._xml_close()
5142 def _check_dimensions(
5143 self, row: int, col: int, ignore_row=False, ignore_col=False
5144 ) -> int:
5145 # Check that row and col are valid and store the max and min
5146 # values for use in other methods/elements. The ignore_row /
5147 # ignore_col flags is used to indicate that we wish to perform
5148 # the dimension check without storing the value. The ignore
5149 # flags are use by set_row() and data_validate.
5151 # Check that the row/col are within the worksheet bounds.
5152 if row < 0 or col < 0:
5153 return -1
5154 if row >= self.xls_rowmax or col >= self.xls_colmax:
5155 return -1
5157 # In constant_memory mode we don't change dimensions for rows
5158 # that are already written.
5159 if (
5160 not ignore_row
5161 and not ignore_col
5162 and self.constant_memory
5163 and row < self.previous_row
5164 ):
5165 return -2
5167 if not ignore_row:
5168 if self.dim_rowmin is None or row < self.dim_rowmin:
5169 self.dim_rowmin = row
5170 if self.dim_rowmax is None or row > self.dim_rowmax:
5171 self.dim_rowmax = row
5173 if not ignore_col:
5174 if self.dim_colmin is None or col < self.dim_colmin:
5175 self.dim_colmin = col
5176 if self.dim_colmax is None or col > self.dim_colmax:
5177 self.dim_colmax = col
5179 return 0
5181 def _convert_date_time(self, dt_obj):
5182 # Convert a datetime object to an Excel serial date and time.
5183 return _datetime_to_excel_datetime(dt_obj, self.date_1904, self.remove_timezone)
5185 def _convert_name_area(self, row_num_1, col_num_1, row_num_2, col_num_2):
5186 # Convert zero indexed rows and columns to the format required by
5187 # worksheet named ranges, eg, "Sheet1!$A$1:$C$13".
5189 range1 = ""
5190 range2 = ""
5191 area = ""
5192 row_col_only = 0
5194 # Convert to A1 notation.
5195 col_char_1 = xl_col_to_name(col_num_1, 1)
5196 col_char_2 = xl_col_to_name(col_num_2, 1)
5197 row_char_1 = "$" + str(row_num_1 + 1)
5198 row_char_2 = "$" + str(row_num_2 + 1)
5200 # We need to handle special cases that refer to rows or columns only.
5201 if row_num_1 == 0 and row_num_2 == self.xls_rowmax - 1:
5202 range1 = col_char_1
5203 range2 = col_char_2
5204 row_col_only = 1
5205 elif col_num_1 == 0 and col_num_2 == self.xls_colmax - 1:
5206 range1 = row_char_1
5207 range2 = row_char_2
5208 row_col_only = 1
5209 else:
5210 range1 = col_char_1 + row_char_1
5211 range2 = col_char_2 + row_char_2
5213 # A repeated range is only written once (if it isn't a special case).
5214 if range1 == range2 and not row_col_only:
5215 area = range1
5216 else:
5217 area = range1 + ":" + range2
5219 # Build up the print area range "Sheet1!$A$1:$C$13".
5220 sheetname = quote_sheetname(self.name)
5221 area = sheetname + "!" + area
5223 return area
5225 def _sort_pagebreaks(self, breaks):
5226 # This is an internal method used to filter elements of a list of
5227 # pagebreaks used in the _store_hbreak() and _store_vbreak() methods.
5228 # It:
5229 # 1. Removes duplicate entries from the list.
5230 # 2. Sorts the list.
5231 # 3. Removes 0 from the list if present.
5232 if not breaks:
5233 return []
5235 breaks_set = set(breaks)
5237 breaks_set.discard(0)
5239 breaks_list = list(breaks_set)
5240 breaks_list.sort()
5242 # The Excel 2007 specification says that the maximum number of page
5243 # breaks is 1026. However, in practice it is actually 1023.
5244 max_num_breaks = 1023
5245 if len(breaks_list) > max_num_breaks:
5246 breaks_list = breaks_list[:max_num_breaks]
5248 return breaks_list
5250 def _extract_filter_tokens(self, expression):
5251 # Extract the tokens from the filter expression. The tokens are mainly
5252 # non-whitespace groups. The only tricky part is to extract string
5253 # tokens that contain whitespace and/or quoted double quotes (Excel's
5254 # escaped quotes).
5255 #
5256 # Examples: 'x < 2000'
5257 # 'x > 2000 and x < 5000'
5258 # 'x = "foo"'
5259 # 'x = "foo bar"'
5260 # 'x = "foo "" bar"'
5261 #
5262 if not expression:
5263 return []
5265 token_re = re.compile(r'"(?:[^"]|"")*"|\S+')
5266 tokens = token_re.findall(expression)
5268 new_tokens = []
5269 # Remove single leading and trailing quotes and un-escape other quotes.
5270 for token in tokens:
5271 if token.startswith('"'):
5272 token = token[1:]
5274 if token.endswith('"'):
5275 token = token[:-1]
5277 token = token.replace('""', '"')
5279 new_tokens.append(token)
5281 return new_tokens
5283 def _parse_filter_expression(self, expression, tokens):
5284 # Converts the tokens of a possibly conditional expression into 1 or 2
5285 # sub expressions for further parsing.
5286 #
5287 # Examples:
5288 # ('x', '==', 2000) -> exp1
5289 # ('x', '>', 2000, 'and', 'x', '<', 5000) -> exp1 and exp2
5291 if len(tokens) == 7:
5292 # The number of tokens will be either 3 (for 1 expression)
5293 # or 7 (for 2 expressions).
5294 conditional = tokens[3]
5296 if re.match("(and|&&)", conditional):
5297 conditional = 0
5298 elif re.match(r"(or|\|\|)", conditional):
5299 conditional = 1
5300 else:
5301 warn(
5302 f"Token '{conditional}' is not a valid conditional "
5303 f"in filter expression '{expression}'"
5304 )
5306 expression_1 = self._parse_filter_tokens(expression, tokens[0:3])
5307 expression_2 = self._parse_filter_tokens(expression, tokens[4:7])
5308 return expression_1 + [conditional] + expression_2
5310 return self._parse_filter_tokens(expression, tokens)
5312 def _parse_filter_tokens(self, expression, tokens):
5313 # Parse the 3 tokens of a filter expression and return the operator
5314 # and token. The use of numbers instead of operators is a legacy of
5315 # Spreadsheet::WriteExcel.
5316 operators = {
5317 "==": 2,
5318 "=": 2,
5319 "=~": 2,
5320 "eq": 2,
5321 "!=": 5,
5322 "!~": 5,
5323 "ne": 5,
5324 "<>": 5,
5325 "<": 1,
5326 "<=": 3,
5327 ">": 4,
5328 ">=": 6,
5329 }
5331 operator = operators.get(tokens[1], None)
5332 token = tokens[2]
5334 # Special handling of "Top" filter expressions.
5335 if re.match("top|bottom", tokens[0].lower()):
5336 value = int(tokens[1])
5338 if value < 1 or value > 500:
5339 warn(
5340 f"The value '{token}' in expression '{expression}' "
5341 f"must be in the range 1 to 500"
5342 )
5344 token = token.lower()
5346 if token not in ("items", "%"):
5347 warn(
5348 f"The type '{token}' in expression '{expression}' "
5349 f"must be either 'items' or '%%'"
5350 )
5352 if tokens[0].lower() == "top":
5353 operator = 30
5354 else:
5355 operator = 32
5357 if tokens[2] == "%":
5358 operator += 1
5360 token = str(value)
5362 if not operator and tokens[0]:
5363 warn(
5364 f"Token '{token[0]}' is not a valid operator "
5365 f"in filter expression '{expression}'."
5366 )
5368 # Special handling for Blanks/NonBlanks.
5369 if re.match("blanks|nonblanks", token.lower()):
5370 # Only allow Equals or NotEqual in this context.
5371 if operator not in (2, 5):
5372 warn(
5373 f"The operator '{tokens[1]}' in expression '{expression}' "
5374 f"is not valid in relation to Blanks/NonBlanks'."
5375 )
5377 token = token.lower()
5379 # The operator should always be 2 (=) to flag a "simple" equality
5380 # in the binary record. Therefore we convert <> to =.
5381 if token == "blanks":
5382 if operator == 5:
5383 token = " "
5384 else:
5385 if operator == 5:
5386 operator = 2
5387 token = "blanks"
5388 else:
5389 operator = 5
5390 token = " "
5392 # if the string token contains an Excel match character then change the
5393 # operator type to indicate a non "simple" equality.
5394 if operator == 2 and re.search("[*?]", token):
5395 operator = 22
5397 return [operator, token]
5399 def _encode_password(self, password) -> str:
5400 # Hash a worksheet password. Based on the algorithm in
5401 # ECMA-376-4:2016, Office Open XML File Formats — Transitional
5402 # Migration Features, Additional attributes for workbookProtection
5403 # element (Part 1, §18.2.29).
5404 digest = 0x0000
5406 for char in password[::-1]:
5407 digest = ((digest >> 14) & 0x01) | ((digest << 1) & 0x7FFF)
5408 digest ^= ord(char)
5410 digest = ((digest >> 14) & 0x01) | ((digest << 1) & 0x7FFF)
5411 digest ^= len(password)
5412 digest ^= 0xCE4B
5414 return f"{digest:X}"
5416 def _image_from_source(self, source, options: Optional[Dict[str, Any]] = None):
5417 # Backward compatibility utility method to convert an input argument to
5418 # an Image object. The source can be a filename, BytesIO stream or
5419 # an existing Image object.
5420 if isinstance(source, Image):
5421 image = source
5422 elif options is not None and options.get("image_data"):
5423 image = Image(options["image_data"])
5424 image.image_name = source
5425 else:
5426 image = Image(source)
5428 return image
5430 def _prepare_image(
5431 self,
5432 image: Image,
5433 image_id: int,
5434 drawing_id: int,
5435 ) -> None:
5436 # Set up images/drawings.
5438 # Get the effective image width and height in pixels.
5439 width = image._width * image._x_scale
5440 height = image._height * image._y_scale
5442 # Scale by non 96dpi resolutions.
5443 width *= 96.0 / image._x_dpi
5444 height *= 96.0 / image._y_dpi
5446 dimensions = self._position_object_emus(
5447 image._col,
5448 image._row,
5449 image._x_offset,
5450 image._y_offset,
5451 width,
5452 height,
5453 image._anchor,
5454 )
5456 # Convert from pixels to emus.
5457 width = int(0.5 + (width * 9525))
5458 height = int(0.5 + (height * 9525))
5460 # Create a Drawing obj to use with worksheet unless one already exists.
5461 if not self.drawing:
5462 drawing = Drawing()
5463 drawing.embedded = 1
5464 self.drawing = drawing
5466 self.external_drawing_links.append(
5467 ["/drawing", "../drawings/drawing" + str(drawing_id) + ".xml", None]
5468 )
5469 else:
5470 drawing = self.drawing
5472 drawing_object = DrawingInfo()
5473 drawing_object._drawing_type = DrawingTypes.IMAGE
5474 drawing_object._dimensions = dimensions
5475 drawing_object._description = image.image_name
5476 drawing_object._width = width
5477 drawing_object._height = height
5478 drawing_object._shape = None
5479 drawing_object._anchor = image._anchor
5480 drawing_object._rel_index = 0
5481 drawing_object._decorative = image._decorative
5483 if image.description is not None:
5484 drawing_object._description = image.description
5486 if image._url:
5487 url = image._url
5488 target = url._target()
5489 target_mode = url._target_mode()
5491 if not self.drawing_rels.get(url._link):
5492 self.drawing_links.append(["/hyperlink", target, target_mode])
5494 url._rel_index = self._get_drawing_rel_index(url._link)
5495 drawing_object._url = url
5497 if not self.drawing_rels.get(image._digest):
5498 self.drawing_links.append(
5499 [
5500 "/image",
5501 "../media/image" + str(image_id) + "." + image._image_extension,
5502 ]
5503 )
5505 drawing_object._rel_index = self._get_drawing_rel_index(image._digest)
5506 drawing._add_drawing_object(drawing_object)
5508 def _prepare_shape(self, index, drawing_id) -> None:
5509 # Set up shapes/drawings.
5510 (
5511 row,
5512 col,
5513 x_offset,
5514 y_offset,
5515 x_scale,
5516 y_scale,
5517 text,
5518 anchor,
5519 options,
5520 description,
5521 decorative,
5522 ) = self.shapes[index]
5524 width = options.get("width", self.default_col_width * 3)
5525 height = options.get("height", self.default_row_height * 6)
5527 width *= x_scale
5528 height *= y_scale
5530 dimensions = self._position_object_emus(
5531 col, row, x_offset, y_offset, width, height, anchor
5532 )
5534 # Convert from pixels to emus.
5535 width = int(0.5 + (width * 9525))
5536 height = int(0.5 + (height * 9525))
5538 # Create a Drawing obj to use with worksheet unless one already exists.
5539 if not self.drawing:
5540 drawing = Drawing()
5541 drawing.embedded = 1
5542 self.drawing = drawing
5544 self.external_drawing_links.append(
5545 ["/drawing", "../drawings/drawing" + str(drawing_id) + ".xml", None]
5546 )
5547 else:
5548 drawing = self.drawing
5550 shape = Shape("rect", "TextBox", options)
5551 shape.text = text
5553 drawing_object = DrawingInfo()
5554 drawing_object._drawing_type = DrawingTypes.SHAPE
5555 drawing_object._dimensions = dimensions
5556 drawing_object._width = width
5557 drawing_object._height = height
5558 drawing_object._description = description
5559 drawing_object._shape = shape
5560 drawing_object._anchor = anchor
5561 drawing_object._rel_index = 0
5562 drawing_object._decorative = decorative
5564 url = Url.from_options(options)
5565 if url:
5566 target = url._target()
5567 target_mode = url._target_mode()
5569 if not self.drawing_rels.get(url._link):
5570 self.drawing_links.append(["/hyperlink", target, target_mode])
5572 url._rel_index = self._get_drawing_rel_index(url._link)
5573 drawing_object._url = url
5575 drawing._add_drawing_object(drawing_object)
5577 def _prepare_header_image(self, image_id, image) -> None:
5578 # Set up an image without a drawing object for header/footer images.
5580 # Strip the extension from the filename.
5581 image.image_name = re.sub(r"\..*$", "", image.image_name)
5583 if not self.vml_drawing_rels.get(image._digest):
5584 self.vml_drawing_links.append(
5585 [
5586 "/image",
5587 "../media/image" + str(image_id) + "." + image._image_extension,
5588 ]
5589 )
5591 image._ref_id = self._get_vml_drawing_rel_index(image._digest)
5593 self.header_images_list.append(image)
5595 def _prepare_background(self, image_id, image_extension) -> None:
5596 # Set up an image without a drawing object for backgrounds.
5597 self.external_background_links.append(
5598 ["/image", "../media/image" + str(image_id) + "." + image_extension]
5599 )
5601 def _prepare_chart(self, index, chart_id, drawing_id) -> None:
5602 # Set up chart/drawings.
5603 (
5604 row,
5605 col,
5606 chart,
5607 x_offset,
5608 y_offset,
5609 x_scale,
5610 y_scale,
5611 anchor,
5612 description,
5613 decorative,
5614 ) = self.charts[index]
5616 chart.id = chart_id - 1
5618 # Use user specified dimensions, if any.
5619 width = int(0.5 + (chart.width * x_scale))
5620 height = int(0.5 + (chart.height * y_scale))
5622 dimensions = self._position_object_emus(
5623 col, row, x_offset, y_offset, width, height, anchor
5624 )
5626 # Set the chart name for the embedded object if it has been specified.
5627 name = chart.chart_name
5629 # Create a Drawing obj to use with worksheet unless one already exists.
5630 if not self.drawing:
5631 drawing = Drawing()
5632 drawing.embedded = 1
5633 self.drawing = drawing
5635 self.external_drawing_links.append(
5636 ["/drawing", "../drawings/drawing" + str(drawing_id) + ".xml"]
5637 )
5638 else:
5639 drawing = self.drawing
5641 drawing_object = DrawingInfo()
5642 drawing_object._drawing_type = DrawingTypes.CHART
5643 drawing_object._dimensions = dimensions
5644 drawing_object._width = width
5645 drawing_object._height = height
5646 drawing_object._name = name
5647 drawing_object._shape = None
5648 drawing_object._anchor = anchor
5649 drawing_object._rel_index = self._get_drawing_rel_index()
5650 drawing_object._description = description
5651 drawing_object._decorative = decorative
5653 drawing._add_drawing_object(drawing_object)
5655 self.drawing_links.append(
5656 ["/chart", "../charts/chart" + str(chart_id) + ".xml"]
5657 )
5659 def _position_object_emus(
5660 self, col_start, row_start, x1, y1, width, height, anchor
5661 ):
5662 # Calculate the vertices that define the position of a graphical
5663 # object within the worksheet in EMUs.
5664 #
5665 # The vertices are expressed as English Metric Units (EMUs). There are
5666 # 12,700 EMUs per point. Therefore, 12,700 * 3 /4 = 9,525 EMUs per
5667 # pixel
5668 (
5669 col_start,
5670 row_start,
5671 x1,
5672 y1,
5673 col_end,
5674 row_end,
5675 x2,
5676 y2,
5677 x_abs,
5678 y_abs,
5679 ) = self._position_object_pixels(
5680 col_start, row_start, x1, y1, width, height, anchor
5681 )
5683 # Convert the pixel values to EMUs. See above.
5684 x1 = int(0.5 + 9525 * x1)
5685 y1 = int(0.5 + 9525 * y1)
5686 x2 = int(0.5 + 9525 * x2)
5687 y2 = int(0.5 + 9525 * y2)
5688 x_abs = int(0.5 + 9525 * x_abs)
5689 y_abs = int(0.5 + 9525 * y_abs)
5691 return (col_start, row_start, x1, y1, col_end, row_end, x2, y2, x_abs, y_abs)
5693 # Calculate the vertices that define the position of a graphical object
5694 # within the worksheet in pixels.
5695 #
5696 # +------------+------------+
5697 # | A | B |
5698 # +-----+------------+------------+
5699 # | |(x1,y1) | |
5700 # | 1 |(A1)._______|______ |
5701 # | | | | |
5702 # | | | | |
5703 # +-----+----| OBJECT |-----+
5704 # | | | | |
5705 # | 2 | |______________. |
5706 # | | | (B2)|
5707 # | | | (x2,y2)|
5708 # +---- +------------+------------+
5709 #
5710 # Example of an object that covers some of the area from cell A1 to B2.
5711 #
5712 # Based on the width and height of the object we need to calculate 8 vars:
5713 #
5714 # col_start, row_start, col_end, row_end, x1, y1, x2, y2.
5715 #
5716 # We also calculate the absolute x and y position of the top left vertex of
5717 # the object. This is required for images.
5718 #
5719 # The width and height of the cells that the object occupies can be
5720 # variable and have to be taken into account.
5721 #
5722 # The values of col_start and row_start are passed in from the calling
5723 # function. The values of col_end and row_end are calculated by
5724 # subtracting the width and height of the object from the width and
5725 # height of the underlying cells.
5726 #
5727 def _position_object_pixels(
5728 self, col_start, row_start, x1, y1, width, height, anchor
5729 ):
5730 # col_start # Col containing upper left corner of object.
5731 # x1 # Distance to left side of object.
5732 #
5733 # row_start # Row containing top left corner of object.
5734 # y1 # Distance to top of object.
5735 #
5736 # col_end # Col containing lower right corner of object.
5737 # x2 # Distance to right side of object.
5738 #
5739 # row_end # Row containing bottom right corner of object.
5740 # y2 # Distance to bottom of object.
5741 #
5742 # width # Width of object frame.
5743 # height # Height of object frame.
5744 #
5745 # x_abs # Absolute distance to left side of object.
5746 # y_abs # Absolute distance to top side of object.
5747 x_abs = 0
5748 y_abs = 0
5750 # Adjust start column for negative offsets.
5751 # pylint: disable=chained-comparison
5752 while x1 < 0 and col_start > 0:
5753 x1 += self._size_col(col_start - 1)
5754 col_start -= 1
5756 # Adjust start row for negative offsets.
5757 while y1 < 0 and row_start > 0:
5758 y1 += self._size_row(row_start - 1)
5759 row_start -= 1
5761 # Ensure that the image isn't shifted off the page at top left.
5762 x1 = max(0, x1)
5763 y1 = max(0, y1)
5765 # Calculate the absolute x offset of the top-left vertex.
5766 if self.col_size_changed:
5767 x_abs += self._col_offset(col_start)
5768 else:
5769 # Optimization for when the column widths haven't changed.
5770 x_abs += self.default_col_width * col_start
5772 x_abs += x1
5774 # Calculate the absolute y offset of the top-left vertex.
5775 if self.row_sizes:
5776 y_abs += self._row_offset(row_start)
5777 else:
5778 # Optimization for when no individual row heights have changed.
5779 y_abs += self.default_row_height * row_start
5781 y_abs += y1
5783 # Adjust start column for offsets that are greater than the col width.
5784 while x1 >= self._size_col(col_start, anchor):
5785 x1 -= self._size_col(col_start)
5786 col_start += 1
5788 # Adjust start row for offsets that are greater than the row height.
5789 while y1 >= self._size_row(row_start, anchor):
5790 y1 -= self._size_row(row_start)
5791 row_start += 1
5793 # Initialize end cell to the same as the start cell.
5794 col_end = col_start
5795 row_end = row_start
5797 # Don't offset the image in the cell if the row/col is hidden.
5798 if self._size_col(col_start, anchor) > 0:
5799 width = width + x1
5800 if self._size_row(row_start, anchor) > 0:
5801 height = height + y1
5803 # Subtract the underlying cell widths to find end cell of the object.
5804 while width >= self._size_col(col_end, anchor):
5805 width -= self._size_col(col_end, anchor)
5806 col_end += 1
5808 # Subtract the underlying cell heights to find end cell of the object.
5809 while height >= self._size_row(row_end, anchor):
5810 height -= self._size_row(row_end, anchor)
5811 row_end += 1
5813 # The end vertices are whatever is left from the width and height.
5814 x2 = width
5815 y2 = height
5817 return [col_start, row_start, x1, y1, col_end, row_end, x2, y2, x_abs, y_abs]
5819 def _size_col(self, col: int, anchor=0):
5820 # Look up the cell value to see if it has been changed.
5821 if col in self.col_info:
5822 width = self.col_info[col].width
5823 hidden = self.col_info[col].hidden
5825 if width is None:
5826 width = self.default_col_width
5828 if hidden and anchor != 4:
5829 width = 0
5831 return width
5833 return self.default_col_width
5835 def _size_row(self, row: int, anchor=0):
5836 # Look up the cell value to see if it has been changed
5837 if row in self.row_sizes:
5838 height = self.row_sizes[row][0]
5839 hidden = self.row_sizes[row][1]
5841 if hidden and anchor != 4:
5842 height = 0
5844 return height
5846 return self.default_row_height
5848 def _col_offset(self, col: int):
5849 # Get the absolute pixel offset of a column by summing the widths of
5850 # the columns before it. Objects are generally positioned in row/col
5851 # order so, to avoid re-summing from the first column for each object,
5852 # the sum restarts from the previously calculated column and offset.
5853 prev_col, offset = self.prev_col_offset
5855 if col < prev_col:
5856 prev_col, offset = 0, 0
5858 for col_id in range(prev_col, col):
5859 offset += self._size_col(col_id)
5861 self.prev_col_offset = (col, offset)
5863 return offset
5865 def _row_offset(self, row: int):
5866 # Get the absolute pixel offset of a row by summing the heights of the
5867 # rows before it. See _col_offset() above.
5868 prev_row, offset = self.prev_row_offset
5870 if row < prev_row:
5871 prev_row, offset = 0, 0
5873 for row_id in range(prev_row, row):
5874 offset += self._size_row(row_id)
5876 self.prev_row_offset = (row, offset)
5878 return offset
5880 def _pixels_to_height(self, pixels):
5881 # Convert the height of a cell from pixels to character units.
5882 return 0.75 * pixels
5884 def _comment_vertices(self, comment: CommentType):
5885 # Calculate the positions of the comment object.
5886 anchor = 0
5887 vertices = self._position_object_pixels(
5888 comment.start_col,
5889 comment.start_row,
5890 comment.x_offset,
5891 comment.y_offset,
5892 comment.width,
5893 comment.height,
5894 anchor,
5895 )
5897 # Add the width and height for VML.
5898 vertices.append(comment.width)
5899 vertices.append(comment.height)
5901 return vertices
5903 def _button_vertices(self, button: ButtonType):
5904 # Calculate the positions of the button object.
5905 anchor = 0
5906 vertices = self._position_object_pixels(
5907 button.col,
5908 button.row,
5909 button.x_offset,
5910 button.y_offset,
5911 button.width,
5912 button.height,
5913 anchor,
5914 )
5916 # Add the width and height for VML.
5917 vertices.append(button.width)
5918 vertices.append(button.height)
5920 return vertices
5922 def _prepare_vml_objects(
5923 self, vml_data_id, vml_shape_id, vml_drawing_id, comment_id
5924 ):
5925 comments = []
5926 # Sort the comments into row/column order for easier comparison
5927 # testing and set the external links for comments and buttons.
5928 row_nums = sorted(self.comments.keys())
5930 for row in row_nums:
5931 col_nums = sorted(self.comments[row].keys())
5933 for col in col_nums:
5934 comment = self.comments[row][col]
5935 comment.vertices = self._comment_vertices(comment)
5937 # Set comment visibility if required and not user defined.
5938 if comment.is_visible is None:
5939 comment.is_visible = self.comments_visible
5941 # Set comment author if not already user defined.
5942 if comment.author is None:
5943 comment.author = self.comments_author
5945 comments.append(comment)
5947 for button in self.buttons_list:
5948 button.vertices = self._button_vertices(button)
5950 self.external_vml_links.append(
5951 ["/vmlDrawing", "../drawings/vmlDrawing" + str(vml_drawing_id) + ".vml"]
5952 )
5954 if self.has_comments:
5955 self.comments_list = comments
5957 self.external_comment_links.append(
5958 ["/comments", "../comments" + str(comment_id) + ".xml"]
5959 )
5961 count = len(comments)
5962 start_data_id = vml_data_id
5964 # The VML o:idmap data id contains a comma separated range when there
5965 # is more than one 1024 block of comments, like this: data="1,2".
5966 for i in range(int(count / 1024)):
5967 data_id = start_data_id + i + 1
5968 vml_data_id = f"{vml_data_id},{data_id}"
5970 self.vml_data_id = vml_data_id
5971 self.vml_shape_id = vml_shape_id
5973 return count
5975 def _prepare_header_vml_objects(self, vml_header_id, vml_drawing_id) -> None:
5976 # Set up external linkage for VML header/footer images.
5978 self.vml_header_id = vml_header_id
5980 self.external_vml_links.append(
5981 ["/vmlDrawing", "../drawings/vmlDrawing" + str(vml_drawing_id) + ".vml"]
5982 )
5984 def _prepare_tables(self, table_id, seen) -> None:
5985 # Set the table ids for the worksheet tables.
5986 for table in self.tables:
5987 table["id"] = table_id
5989 if table.get("name") is None:
5990 # Set a default name.
5991 table["name"] = "Table" + str(table_id)
5993 # Check for duplicate table names.
5994 name = table["name"].lower()
5996 if name in seen:
5997 raise DuplicateTableName(
5998 f"Duplicate name '{table['name']}' used in worksheet.add_table()."
5999 )
6001 seen[name] = True
6003 # Store the link used for the rels file.
6004 self.external_table_links.append(
6005 ["/table", "../tables/table" + str(table_id) + ".xml"]
6006 )
6007 table_id += 1
6009 def _table_function_to_formula(self, function, col_name):
6010 # Convert a table total function to a worksheet formula.
6011 formula = ""
6013 # Escape special characters, as required by Excel.
6014 col_name = col_name.replace("'", "''")
6015 col_name = col_name.replace("#", "'#")
6016 col_name = col_name.replace("]", "']")
6017 col_name = col_name.replace("[", "'[")
6019 subtotals = {
6020 "average": 101,
6021 "countNums": 102,
6022 "count": 103,
6023 "max": 104,
6024 "min": 105,
6025 "stdDev": 107,
6026 "sum": 109,
6027 "var": 110,
6028 }
6030 if function in subtotals:
6031 func_num = subtotals[function]
6032 formula = f"SUBTOTAL({func_num},[{col_name}])"
6033 else:
6034 warn(f"Unsupported function '{function}' in add_table()")
6036 return formula
6038 def _set_spark_color(self, sparkline, options, user_color) -> None:
6039 # Set the sparkline color.
6040 if user_color not in options:
6041 return
6043 sparkline[user_color] = Color._from_value(options[user_color])
6045 def _get_range_data(self, row_start, col_start, row_end, col_end):
6046 # Returns a range of data from the worksheet _table to be used in
6047 # chart cached data. Strings are returned as SST ids and decoded
6048 # in the workbook. Return None for data that doesn't exist since
6049 # Excel can chart have series with data missing.
6051 if self.constant_memory:
6052 return ()
6054 data = []
6056 # Iterate through the table data.
6057 for row_num in range(row_start, row_end + 1):
6058 # Store None if row doesn't exist.
6059 if row_num not in self.table:
6060 data.append(None)
6061 continue
6063 for col_num in range(col_start, col_end + 1):
6064 if col_num in self.table[row_num]:
6065 cell = self.table[row_num][col_num]
6067 cell_type = cell.__class__.__name__
6069 if cell_type in ("Number", "Datetime"):
6070 # Return a number with Excel's precision.
6071 data.append(f"{cell.number:.16g}")
6073 elif cell_type == "String":
6074 # Return a string from it's shared string index.
6075 index = cell.string
6076 string = self.str_table._get_shared_string(index)
6078 data.append(string)
6080 elif cell_type in ("Formula", "ArrayFormula"):
6081 # Return the formula value.
6082 value = cell.value
6084 if value is None:
6085 value = 0
6087 data.append(value)
6089 elif cell_type == "Blank":
6090 # Return a empty cell.
6091 data.append("")
6092 else:
6093 # Store None if column doesn't exist.
6094 data.append(None)
6096 return data
6098 def _csv_join(self, *items):
6099 # Create a csv string for use with data validation formulas and lists.
6101 # Convert non string types to string.
6102 items = [str(item) if not isinstance(item, str) else item for item in items]
6104 return ",".join(items)
6106 def _escape_url(self, url):
6107 # Don't escape URL if it looks already escaped.
6108 if re.search("%[0-9a-fA-F]{2}", url):
6109 return url
6111 # Can't use url.quote() here because it doesn't match Excel.
6112 url = url.replace("%", "%25")
6113 url = url.replace('"', "%22")
6114 url = url.replace(" ", "%20")
6115 url = url.replace("<", "%3c")
6116 url = url.replace(">", "%3e")
6117 url = url.replace("[", "%5b")
6118 url = url.replace("]", "%5d")
6119 url = url.replace("^", "%5e")
6120 url = url.replace("`", "%60")
6121 url = url.replace("{", "%7b")
6122 url = url.replace("}", "%7d")
6124 return url
6126 def _get_drawing_rel_index(self, target=None):
6127 # Get the index used to address a drawing rel link.
6128 if target is None:
6129 self.drawing_rels_id += 1
6130 return self.drawing_rels_id
6132 if self.drawing_rels.get(target):
6133 return self.drawing_rels[target]
6135 self.drawing_rels_id += 1
6136 self.drawing_rels[target] = self.drawing_rels_id
6137 return self.drawing_rels_id
6139 def _get_vml_drawing_rel_index(self, target=None):
6140 # Get the index used to address a vml drawing rel link.
6141 if self.vml_drawing_rels.get(target):
6142 return self.vml_drawing_rels[target]
6144 self.vml_drawing_rels_id += 1
6145 self.vml_drawing_rels[target] = self.vml_drawing_rels_id
6146 return self.vml_drawing_rels_id
6148 ###########################################################################
6149 #
6150 # The following font methods are mainly duplicated from the Styles class
6151 # with appropriate changes for rich string styles.
6152 #
6153 ###########################################################################
6154 def _write_font(self, xf_format) -> None:
6155 # Write the <font> element.
6156 xml_writer = self.rstring
6158 xml_writer._xml_start_tag("rPr")
6160 # Handle the main font properties.
6161 if xf_format.bold:
6162 xml_writer._xml_empty_tag("b")
6163 if xf_format.italic:
6164 xml_writer._xml_empty_tag("i")
6165 if xf_format.font_strikeout:
6166 xml_writer._xml_empty_tag("strike")
6167 if xf_format.font_outline:
6168 xml_writer._xml_empty_tag("outline")
6169 if xf_format.font_shadow:
6170 xml_writer._xml_empty_tag("shadow")
6172 # Handle the underline variants.
6173 if xf_format.underline:
6174 self._write_underline(xf_format.underline)
6176 # Handle super/subscript.
6177 if xf_format.font_script == 1:
6178 self._write_vert_align("superscript")
6179 if xf_format.font_script == 2:
6180 self._write_vert_align("subscript")
6182 # Write the font size
6183 xml_writer._xml_empty_tag("sz", [("val", xf_format.font_size)])
6185 # Handle colors.
6186 if xf_format.theme == -1:
6187 # Ignore for excel2003_style.
6188 pass
6189 elif xf_format.theme:
6190 self._write_rstring_color("color", [("theme", xf_format.theme)])
6191 elif xf_format.color_indexed:
6192 self._write_rstring_color("color", [("indexed", xf_format.color_indexed)])
6193 elif xf_format.font_color:
6194 color = xf_format.font_color
6195 if not color._is_automatic:
6196 self._write_rstring_color("color", color._attributes())
6197 else:
6198 self._write_rstring_color("color", [("theme", 1)])
6200 # Write some other font properties related to font families.
6201 xml_writer._xml_empty_tag("rFont", [("val", xf_format.font_name)])
6202 xml_writer._xml_empty_tag("family", [("val", xf_format.font_family)])
6204 if xf_format.font_name == "Calibri" and not xf_format.hyperlink:
6205 xml_writer._xml_empty_tag("scheme", [("val", xf_format.font_scheme)])
6207 xml_writer._xml_end_tag("rPr")
6209 def _write_underline(self, underline) -> None:
6210 # Write the underline font element.
6211 attributes = []
6213 # Handle the underline variants.
6214 if underline == 2:
6215 attributes = [("val", "double")]
6216 elif underline == 33:
6217 attributes = [("val", "singleAccounting")]
6218 elif underline == 34:
6219 attributes = [("val", "doubleAccounting")]
6221 self.rstring._xml_empty_tag("u", attributes)
6223 def _write_vert_align(self, val) -> None:
6224 # Write the <vertAlign> font sub-element.
6225 attributes = [("val", val)]
6227 self.rstring._xml_empty_tag("vertAlign", attributes)
6229 def _write_rstring_color(self, name, attributes) -> None:
6230 # Write the <color> element.
6231 self.rstring._xml_empty_tag(name, attributes)
6233 def _opt_close(self) -> None:
6234 # Close the row data filehandle in constant_memory mode.
6235 if not self.row_data_fh_closed:
6236 self.row_data_fh.close()
6237 self.row_data_fh_closed = True
6239 def _opt_reopen(self) -> None:
6240 # Reopen the row data filehandle in constant_memory mode.
6241 if self.row_data_fh_closed:
6242 filename = self.row_data_filename
6243 # pylint: disable=consider-using-with
6244 self.row_data_fh = open( # noqa: SIM115
6245 filename, mode="a+", encoding="utf-8"
6246 )
6247 self.row_data_fh_closed = False
6248 self.fh = self.row_data_fh
6250 def _set_icon_props(self, total_icons, user_props=None):
6251 # Set the sub-properties for icons.
6252 props = []
6254 # Set the defaults.
6255 for _ in range(total_icons):
6256 props.append({"criteria": False, "value": 0, "type": "percent"})
6258 # Set the default icon values based on the number of icons.
6259 if total_icons == 3:
6260 props[0]["value"] = 67
6261 props[1]["value"] = 33
6263 if total_icons == 4:
6264 props[0]["value"] = 75
6265 props[1]["value"] = 50
6266 props[2]["value"] = 25
6268 if total_icons == 5:
6269 props[0]["value"] = 80
6270 props[1]["value"] = 60
6271 props[2]["value"] = 40
6272 props[3]["value"] = 20
6274 # Overwrite default properties with user defined properties.
6275 if user_props:
6276 # Ensure we don't set user properties for lowest icon.
6277 max_data = len(user_props)
6278 if max_data >= total_icons:
6279 max_data = total_icons - 1
6281 for i in range(max_data):
6282 # Set the user defined 'value' property.
6283 if user_props[i].get("value") is not None:
6284 props[i]["value"] = user_props[i]["value"]
6286 # Remove the formula '=' sign if it exists.
6287 tmp = props[i]["value"]
6288 if isinstance(tmp, str) and tmp.startswith("="):
6289 props[i]["value"] = tmp.lstrip("=")
6291 # Set the user defined 'type' property.
6292 if user_props[i].get("type"):
6293 valid_types = ("percent", "percentile", "number", "formula")
6295 if user_props[i]["type"] not in valid_types:
6296 warn(
6297 f"Unknown icon property type '{user_props[i]['type']}' "
6298 f"for sub-property 'type' in conditional_format()."
6299 )
6300 else:
6301 props[i]["type"] = user_props[i]["type"]
6303 if props[i]["type"] == "number":
6304 props[i]["type"] = "num"
6306 # Set the user defined 'criteria' property.
6307 criteria = user_props[i].get("criteria")
6308 if criteria and criteria == ">":
6309 props[i]["criteria"] = True
6311 return props
6313 ###########################################################################
6314 #
6315 # XML methods.
6316 #
6317 ###########################################################################
6319 def _write_worksheet(self) -> None:
6320 # Write the <worksheet> element. This is the root element.
6322 schema = "http://schemas.openxmlformats.org/"
6323 xmlns = schema + "spreadsheetml/2006/main"
6324 xmlns_r = schema + "officeDocument/2006/relationships"
6325 xmlns_mc = schema + "markup-compatibility/2006"
6326 ms_schema = "http://schemas.microsoft.com/"
6327 xmlns_x14ac = ms_schema + "office/spreadsheetml/2009/9/ac"
6329 attributes = [("xmlns", xmlns), ("xmlns:r", xmlns_r)]
6331 # Add some extra attributes for Excel 2010. Mainly for sparklines.
6332 if self.excel_version == 2010:
6333 attributes.append(("xmlns:mc", xmlns_mc))
6334 attributes.append(("xmlns:x14ac", xmlns_x14ac))
6335 attributes.append(("mc:Ignorable", "x14ac"))
6337 self._xml_start_tag("worksheet", attributes)
6339 def _write_dimension(self) -> None:
6340 # Write the <dimension> element. This specifies the range of
6341 # cells in the worksheet. As a special case, empty
6342 # spreadsheets use 'A1' as a range.
6344 if self.dim_rowmin is None and self.dim_colmin is None:
6345 # If the min dimensions are not defined then no dimensions
6346 # have been set and we use the default 'A1'.
6347 ref = "A1"
6349 elif self.dim_rowmin is None and self.dim_colmin is not None:
6350 # If the row dimensions aren't set but the column
6351 # dimensions are set then they have been changed via
6352 # set_column().
6354 if self.dim_colmin == self.dim_colmax:
6355 # The dimensions are a single cell and not a range.
6356 ref = xl_rowcol_to_cell(0, self.dim_colmin)
6357 else:
6358 # The dimensions are a cell range.
6359 cell_1 = xl_rowcol_to_cell(0, self.dim_colmin)
6360 cell_2 = xl_rowcol_to_cell(0, self.dim_colmax)
6361 ref = cell_1 + ":" + cell_2
6363 elif self.dim_rowmin == self.dim_rowmax and self.dim_colmin == self.dim_colmax:
6364 # The dimensions are a single cell and not a range.
6365 ref = xl_rowcol_to_cell(self.dim_rowmin, self.dim_colmin)
6366 else:
6367 # The dimensions are a cell range.
6368 cell_1 = xl_rowcol_to_cell(self.dim_rowmin, self.dim_colmin)
6369 cell_2 = xl_rowcol_to_cell(self.dim_rowmax, self.dim_colmax)
6370 ref = cell_1 + ":" + cell_2
6372 self._xml_empty_tag("dimension", [("ref", ref)])
6374 def _write_sheet_views(self) -> None:
6375 # Write the <sheetViews> element.
6376 self._xml_start_tag("sheetViews")
6378 # Write the sheetView element.
6379 self._write_sheet_view()
6381 self._xml_end_tag("sheetViews")
6383 def _write_sheet_view(self) -> None:
6384 # Write the <sheetViews> element.
6385 attributes = []
6387 # Hide screen gridlines if required.
6388 if not self.screen_gridlines:
6389 attributes.append(("showGridLines", 0))
6391 # Hide screen row/column headers.
6392 if self.row_col_headers:
6393 attributes.append(("showRowColHeaders", 0))
6395 # Hide zeroes in cells.
6396 if not self.show_zeros:
6397 attributes.append(("showZeros", 0))
6399 # Display worksheet right to left for Hebrew, Arabic and others.
6400 if self.is_right_to_left:
6401 attributes.append(("rightToLeft", 1))
6403 # Show that the sheet tab is selected.
6404 if self.selected:
6405 attributes.append(("tabSelected", 1))
6407 # Turn outlines off. Also required in the outlinePr element.
6408 if not self.outline_on:
6409 attributes.append(("showOutlineSymbols", 0))
6411 # Set the page view/layout mode if required.
6412 if self.page_view == 1:
6413 attributes.append(("view", "pageLayout"))
6414 elif self.page_view == 2:
6415 attributes.append(("view", "pageBreakPreview"))
6417 # Set the first visible cell.
6418 if self.top_left_cell != "":
6419 attributes.append(("topLeftCell", self.top_left_cell))
6421 # Set the zoom level.
6422 if self.zoom != 100:
6423 attributes.append(("zoomScale", self.zoom))
6425 if self.page_view == 0 and self.zoom_scale_normal:
6426 attributes.append(("zoomScaleNormal", self.zoom))
6427 if self.page_view == 1:
6428 attributes.append(("zoomScalePageLayoutView", self.zoom))
6429 if self.page_view == 2:
6430 attributes.append(("zoomScaleSheetLayoutView", self.zoom))
6432 attributes.append(("workbookViewId", 0))
6434 if self.is_chartsheet and self.zoom_to_fit:
6435 attributes.append(("zoomToFit", 1))
6437 if self.panes or self.selections:
6438 self._xml_start_tag("sheetView", attributes)
6439 self._write_panes()
6440 self._write_selections()
6441 self._xml_end_tag("sheetView")
6442 else:
6443 self._xml_empty_tag("sheetView", attributes)
6445 def _write_sheet_format_pr(self) -> None:
6446 # Write the <sheetFormatPr> element.
6447 height_in_chars = self.default_row_height * 0.75
6448 row_level = self.outline_row_level
6449 col_level = self.outline_col_level
6451 attributes = [("defaultRowHeight", f"{height_in_chars:.16g}")]
6453 if self.default_row_height != self.original_row_height:
6454 attributes.append(("customHeight", 1))
6456 if self.default_row_zeroed:
6457 attributes.append(("zeroHeight", 1))
6459 if row_level:
6460 attributes.append(("outlineLevelRow", row_level))
6461 if col_level:
6462 attributes.append(("outlineLevelCol", col_level))
6464 if self.excel_version == 2010:
6465 attributes.append(("x14ac:dyDescent", "0.25"))
6467 self._xml_empty_tag("sheetFormatPr", attributes)
6469 def _write_cols(self) -> None:
6470 # Write the <cols> element and <col> sub elements.
6472 # Exit unless some column have been formatted.
6473 if not self.col_info:
6474 return
6476 self._xml_start_tag("cols")
6478 # Use the first element of the column information structures to set
6479 # the initial/previous properties.
6480 first_col = min(self.col_info.keys())
6481 last_col = first_col
6482 prev_col_options = self.col_info[first_col]
6483 del self.col_info[first_col]
6484 deleted_col = first_col
6485 deleted_col_options = prev_col_options
6487 for col in sorted(self.col_info.keys()):
6488 col_options = self.col_info[col]
6489 # Check if the column number is contiguous with the previous
6490 # column and if the properties are the same.
6491 if col == last_col + 1 and col_options == prev_col_options:
6492 last_col = col
6493 else:
6494 # If not contiguous/equal then we write out the current range
6495 # of columns and start again.
6496 self._write_col_info(first_col, last_col, prev_col_options)
6497 first_col = col
6498 last_col = first_col
6499 prev_col_options = col_options
6501 # We will exit the previous loop with one unhandled column range.
6502 self._write_col_info(first_col, last_col, prev_col_options)
6504 # Put back the deleted first column information structure.
6505 self.col_info[deleted_col] = deleted_col_options
6507 self._xml_end_tag("cols")
6509 def _write_col_info(self, col_min: int, col_max: int, col_info: ColumnInfo) -> None:
6510 # Write the <col> element.
6511 width = col_info.width
6512 has_custom_width = True
6513 xf_index = 0
6515 # Get the cell_format index.
6516 if col_info.column_format:
6517 xf_index = col_info.column_format._get_xf_index()
6519 # Set the Excel default column width.
6520 if width is None:
6521 if not col_info.hidden:
6522 width = self.default_col_width
6523 has_custom_width = False
6524 else:
6525 width = 0
6526 elif width == self.default_col_width:
6527 # Width is defined but same as default.
6528 has_custom_width = False
6530 # Convert column width from pixels to character width.
6531 char_width = (width * 256 // self.max_digit_width) / 256.0
6533 attributes = [
6534 ("min", col_min + 1),
6535 ("max", col_max + 1),
6536 ("width", f"{char_width:.16g}"),
6537 ]
6539 if xf_index:
6540 attributes.append(("style", xf_index))
6541 if col_info.hidden:
6542 attributes.append(("hidden", "1"))
6543 if col_info.autofit:
6544 attributes.append(("bestFit", "1"))
6545 if has_custom_width:
6546 attributes.append(("customWidth", "1"))
6547 if col_info.level:
6548 attributes.append(("outlineLevel", col_info.level))
6549 if col_info.collapsed:
6550 attributes.append(("collapsed", "1"))
6552 self._xml_empty_tag("col", attributes)
6554 def _write_sheet_data(self) -> None:
6555 # Write the <sheetData> element.
6556 if self.dim_rowmin is None:
6557 # If the dimensions aren't defined there is no data to write.
6558 self._xml_empty_tag("sheetData")
6559 else:
6560 self._xml_start_tag("sheetData")
6561 self._write_rows()
6562 self._xml_end_tag("sheetData")
6564 def _write_optimized_sheet_data(self) -> None:
6565 # Write the <sheetData> element when constant_memory is on. In this
6566 # case we read the data stored in the temp file and rewrite it to the
6567 # XML sheet file.
6568 if self.dim_rowmin is None:
6569 # If the dimensions aren't defined then there is no data to write.
6570 self._xml_empty_tag("sheetData")
6571 else:
6572 self._xml_start_tag("sheetData")
6574 # Rewind the filehandle that was used for temp row data.
6575 buff_size = 65536
6576 self.row_data_fh.seek(0)
6577 data = self.row_data_fh.read(buff_size)
6579 while data:
6580 self.fh.write(data)
6581 data = self.row_data_fh.read(buff_size)
6583 self.row_data_fh.close()
6584 os.unlink(self.row_data_filename)
6586 self._xml_end_tag("sheetData")
6588 def _write_page_margins(self) -> None:
6589 # Write the <pageMargins> element.
6590 attributes = [
6591 ("left", self.margin_left),
6592 ("right", self.margin_right),
6593 ("top", self.margin_top),
6594 ("bottom", self.margin_bottom),
6595 ("header", self.margin_header),
6596 ("footer", self.margin_footer),
6597 ]
6599 self._xml_empty_tag("pageMargins", attributes)
6601 def _write_page_setup(self) -> None:
6602 # Write the <pageSetup> element.
6603 #
6604 # The following is an example taken from Excel.
6605 #
6606 # <pageSetup
6607 # paperSize="9"
6608 # scale="110"
6609 # fitToWidth="2"
6610 # fitToHeight="2"
6611 # pageOrder="overThenDown"
6612 # orientation="portrait"
6613 # blackAndWhite="1"
6614 # draft="1"
6615 # horizontalDpi="200"
6616 # verticalDpi="200"
6617 # r:id="rId1"
6618 # />
6619 #
6620 attributes = []
6622 # Skip this element if no page setup has changed.
6623 if not self.page_setup_changed:
6624 return
6626 # Set paper size.
6627 if self.paper_size:
6628 attributes.append(("paperSize", self.paper_size))
6630 # Set the print_scale.
6631 if self.print_scale != 100:
6632 attributes.append(("scale", self.print_scale))
6634 # Set the "Fit to page" properties.
6635 if self.fit_page and self.fit_width != 1:
6636 attributes.append(("fitToWidth", self.fit_width))
6638 if self.fit_page and self.fit_height != 1:
6639 attributes.append(("fitToHeight", self.fit_height))
6641 # Set the page print direction.
6642 if self.page_order:
6643 attributes.append(("pageOrder", "overThenDown"))
6645 # Set start page for printing.
6646 if self.page_start > 1:
6647 attributes.append(("firstPageNumber", self.page_start))
6649 # Set page orientation.
6650 if self.orientation:
6651 attributes.append(("orientation", "portrait"))
6652 else:
6653 attributes.append(("orientation", "landscape"))
6655 # Set the print in black and white option.
6656 if self.black_white:
6657 attributes.append(("blackAndWhite", "1"))
6659 # Set start page for printing.
6660 if self.page_start != 0:
6661 attributes.append(("useFirstPageNumber", "1"))
6663 # Set the DPI. Mainly only for testing.
6664 if self.is_chartsheet:
6665 if self.horizontal_dpi:
6666 attributes.append(("horizontalDpi", self.horizontal_dpi))
6668 if self.vertical_dpi:
6669 attributes.append(("verticalDpi", self.vertical_dpi))
6670 else:
6671 if self.vertical_dpi:
6672 attributes.append(("verticalDpi", self.vertical_dpi))
6674 if self.horizontal_dpi:
6675 attributes.append(("horizontalDpi", self.horizontal_dpi))
6677 self._xml_empty_tag("pageSetup", attributes)
6679 def _write_print_options(self) -> None:
6680 # Write the <printOptions> element.
6681 attributes = []
6683 if not self.print_options_changed:
6684 return
6686 # Set horizontal centering.
6687 if self.hcenter:
6688 attributes.append(("horizontalCentered", 1))
6690 # Set vertical centering.
6691 if self.vcenter:
6692 attributes.append(("verticalCentered", 1))
6694 # Enable row and column headers.
6695 if self.print_headers:
6696 attributes.append(("headings", 1))
6698 # Set printed gridlines.
6699 if self.print_gridlines:
6700 attributes.append(("gridLines", 1))
6702 self._xml_empty_tag("printOptions", attributes)
6704 def _write_header_footer(self) -> None:
6705 # Write the <headerFooter> element.
6706 attributes = []
6708 if not self.header_footer_scales:
6709 attributes.append(("scaleWithDoc", 0))
6711 if not self.header_footer_aligns:
6712 attributes.append(("alignWithMargins", 0))
6714 if self.header_footer_changed:
6715 self._xml_start_tag("headerFooter", attributes)
6716 if self.header:
6717 self._write_odd_header()
6718 if self.footer:
6719 self._write_odd_footer()
6720 self._xml_end_tag("headerFooter")
6721 elif self.excel2003_style:
6722 self._xml_empty_tag("headerFooter", attributes)
6724 def _write_odd_header(self) -> None:
6725 # Write the <headerFooter> element.
6726 self._xml_data_element("oddHeader", self.header)
6728 def _write_odd_footer(self) -> None:
6729 # Write the <headerFooter> element.
6730 self._xml_data_element("oddFooter", self.footer)
6732 def _write_rows(self) -> None:
6733 # Write out the worksheet data as a series of rows and cells.
6734 self._calculate_spans()
6736 for row_num in range(self.dim_rowmin, self.dim_rowmax + 1):
6737 if (
6738 row_num in self.row_info
6739 or row_num in self.comments
6740 or self.table[row_num]
6741 ):
6742 # Only process rows with formatting, cell data and/or comments.
6744 span_index = int(row_num / 16)
6746 if span_index in self.row_spans:
6747 span = self.row_spans[span_index]
6748 else:
6749 span = None
6751 if self.table[row_num]:
6752 # Write the cells if the row contains data.
6753 if row_num not in self.row_info:
6754 self._write_row(row_num, span)
6755 else:
6756 self._write_row(row_num, span, self.row_info[row_num])
6758 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6759 if col_num in self.table[row_num]:
6760 col_ref = self.table[row_num][col_num]
6761 self._write_cell(row_num, col_num, col_ref)
6763 self._xml_end_tag("row")
6765 elif row_num in self.comments:
6766 # Row with comments in cells.
6767 if row_num not in self.row_info:
6768 self._write_empty_row(row_num, span, None)
6769 else:
6770 self._write_empty_row(row_num, span, self.row_info[row_num])
6771 else:
6772 # Blank row with attributes only.
6773 if row_num not in self.row_info:
6774 self._write_empty_row(row_num, span, None)
6775 else:
6776 self._write_empty_row(row_num, span, self.row_info[row_num])
6778 def _write_single_row(self, current_row_num=0) -> None:
6779 # Write out the worksheet data as a single row with cells.
6780 # This method is used when constant_memory is on. A single
6781 # row is written and the data table is reset. That way only
6782 # one row of data is kept in memory at any one time. We don't
6783 # write span data in the optimized case since it is optional.
6785 # Set the new previous row as the current row.
6786 row_num = self.previous_row
6787 self.previous_row = current_row_num
6789 if row_num in self.row_info or row_num in self.comments or self.table[row_num]:
6790 # Only process rows with formatting, cell data and/or comments.
6792 # No span data in optimized mode.
6793 span = None
6795 if self.table[row_num]:
6796 # Write the cells if the row contains data.
6797 if row_num not in self.row_info:
6798 self._write_row(row_num, span)
6799 else:
6800 self._write_row(row_num, span, self.row_info[row_num])
6802 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6803 if col_num in self.table[row_num]:
6804 col_ref = self.table[row_num][col_num]
6805 self._write_cell(row_num, col_num, col_ref)
6807 self._xml_end_tag("row")
6808 else:
6809 # Row attributes or comments only.
6810 self._write_empty_row(row_num, span, self.row_info[row_num])
6812 # Reset table.
6813 self.table.clear()
6815 def _calculate_spans(self) -> None:
6816 # Calculate the "spans" attribute of the <row> tag. This is an
6817 # XLSX optimization and isn't strictly required. However, it
6818 # makes comparing files easier. The span is the same for each
6819 # block of 16 rows.
6820 spans = {}
6821 span_min = None
6822 span_max = None
6824 for row_num in range(self.dim_rowmin, self.dim_rowmax + 1):
6825 if row_num in self.table:
6826 # Calculate spans for cell data.
6827 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6828 if col_num in self.table[row_num]:
6829 if span_min is None:
6830 span_min = col_num
6831 span_max = col_num
6832 else:
6833 span_min = min(span_min, col_num)
6834 span_max = max(span_max, col_num)
6836 if row_num in self.comments:
6837 # Calculate spans for comments.
6838 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6839 if row_num in self.comments and col_num in self.comments[row_num]:
6840 if span_min is None:
6841 span_min = col_num
6842 span_max = col_num
6843 else:
6844 span_min = min(span_min, col_num)
6845 span_max = max(span_max, col_num)
6847 if ((row_num + 1) % 16 == 0) or row_num == self.dim_rowmax:
6848 span_index = int(row_num / 16)
6850 if span_min is not None:
6851 span_min += 1
6852 span_max += 1
6853 spans[span_index] = f"{span_min}:{span_max}"
6854 span_min = None
6856 self.row_spans = spans
6858 def _write_row(
6859 self,
6860 row: int,
6861 spans: Optional[str],
6862 row_info: Optional[RowInfo] = None,
6863 empty_row: bool = False,
6864 ) -> None:
6865 # Write the <row> element.
6866 xf_index = 0
6868 if row_info:
6869 height = row_info.height
6870 row_format = row_info.row_format
6871 hidden = row_info.hidden
6872 level = row_info.level
6873 collapsed = row_info.collapsed
6874 else:
6875 height = None
6876 row_format = None
6877 hidden = 0
6878 level = 0
6879 collapsed = 0
6881 if height is None:
6882 height = self.default_row_height
6884 attributes = [("r", row + 1)]
6886 # Get the cell_format index.
6887 if row_format:
6888 xf_index = row_format._get_xf_index()
6890 # Add row attributes where applicable.
6891 if spans:
6892 attributes.append(("spans", spans))
6894 if xf_index:
6895 attributes.append(("s", xf_index))
6897 if row_format:
6898 attributes.append(("customFormat", 1))
6900 if height != self.original_row_height or (
6901 height == self.original_row_height and height != self.default_row_height
6902 ):
6903 height_in_chars = height * 0.75
6904 attributes.append(("ht", f"{height_in_chars:.16g}"))
6906 if hidden:
6907 attributes.append(("hidden", 1))
6909 if height != self.original_row_height or (
6910 height == self.original_row_height and height != self.default_row_height
6911 ):
6912 attributes.append(("customHeight", 1))
6914 if level:
6915 attributes.append(("outlineLevel", level))
6917 if collapsed:
6918 attributes.append(("collapsed", 1))
6920 if self.excel_version == 2010:
6921 attributes.append(("x14ac:dyDescent", "0.25"))
6923 if empty_row:
6924 self._xml_empty_tag_unencoded("row", attributes)
6925 else:
6926 self._xml_start_tag_unencoded("row", attributes)
6928 def _write_empty_row(
6929 self, row: int, spans: Optional[str], row_info: Optional[RowInfo] = None
6930 ) -> None:
6931 # Write and empty <row> element.
6932 self._write_row(row, spans, row_info, empty_row=True)
6934 def _write_cell(self, row: int, col: int, cell) -> None:
6935 # Write the <cell> element.
6936 # Note. This is the innermost loop so efficiency is important.
6938 cell_range = xl_rowcol_to_cell_fast(row, col)
6939 attributes = [("r", cell_range)]
6941 if cell.format:
6942 # Add the cell format index.
6943 xf_index = cell.format._get_xf_index()
6944 attributes.append(("s", xf_index))
6945 elif row in self.row_info and self.row_info[row].row_format:
6946 # Add the row format.
6947 row_format = self.row_info[row].row_format
6948 attributes.append(("s", row_format._get_xf_index()))
6949 elif col in self.col_info:
6950 # Add the column format.
6951 column_format = self.col_info[col].column_format
6952 if column_format is not None:
6953 attributes.append(("s", column_format._get_xf_index()))
6955 type_cell_name = cell.__class__.__name__
6957 # Write the various cell types.
6958 if type_cell_name in ("Number", "Datetime"):
6959 # Write a number.
6960 self._xml_number_element(cell.number, attributes)
6962 elif type_cell_name in ("String", "RichString"):
6963 # Write a string.
6964 string = cell.string
6966 if not self.constant_memory:
6967 # Write a shared string.
6968 self._xml_string_element(string, attributes)
6969 else:
6970 # Write an optimized in-line string.
6972 # Convert control character to a _xHHHH_ escape.
6973 string = self._escape_control_characters(string)
6975 # Write any rich strings without further tags.
6976 if string.startswith("<r>") and string.endswith("</r>"):
6977 self._xml_rich_inline_string(string, attributes)
6978 else:
6979 # Add attribute to preserve leading or trailing whitespace.
6980 preserve = _preserve_whitespace(string)
6981 self._xml_inline_string(string, preserve, attributes)
6983 elif type_cell_name == "Formula":
6984 # Write a formula. First check the formula value type.
6985 value = cell.value
6986 if isinstance(cell.value, bool):
6987 attributes.append(("t", "b"))
6988 if cell.value:
6989 value = 1
6990 else:
6991 value = 0
6993 elif isinstance(cell.value, str):
6994 error_codes = (
6995 "#DIV/0!",
6996 "#N/A",
6997 "#NAME?",
6998 "#NULL!",
6999 "#NUM!",
7000 "#REF!",
7001 "#VALUE!",
7002 )
7004 if cell.value == "":
7005 # Allow blank to force recalc in some third party apps.
7006 pass
7007 elif cell.value in error_codes:
7008 attributes.append(("t", "e"))
7009 else:
7010 attributes.append(("t", "str"))
7012 self._xml_formula_element(cell.formula, value, attributes)
7014 elif type_cell_name == "ArrayFormula":
7015 # Write a array formula.
7017 if cell.atype == "dynamic":
7018 attributes.append(("cm", 1))
7020 # First check if the formula value is a string.
7021 try:
7022 float(cell.value)
7023 except ValueError:
7024 attributes.append(("t", "str"))
7026 # Write an array formula.
7027 self._xml_start_tag("c", attributes)
7029 self._write_cell_array_formula(cell.formula, cell.range)
7030 self._write_cell_value(cell.value)
7031 self._xml_end_tag("c")
7033 elif type_cell_name == "Blank":
7034 # Write a empty cell.
7035 self._xml_empty_tag("c", attributes)
7037 elif type_cell_name == "Boolean":
7038 # Write a boolean cell.
7039 attributes.append(("t", "b"))
7040 self._xml_start_tag("c", attributes)
7041 self._write_cell_value(cell.boolean)
7042 self._xml_end_tag("c")
7044 elif type_cell_name == "Error":
7045 # Write a boolean cell.
7046 attributes.append(("t", "e"))
7047 attributes.append(("vm", cell.value))
7048 self._xml_start_tag("c", attributes)
7049 self._write_cell_value(cell.error)
7050 self._xml_end_tag("c")
7052 def _write_cell_value(self, value) -> None:
7053 # Write the cell value <v> element.
7054 if value is None:
7055 value = ""
7057 self._xml_data_element("v", value)
7059 def _write_cell_array_formula(self, formula, cell_range) -> None:
7060 # Write the cell array formula <f> element.
7061 attributes = [("t", "array"), ("ref", cell_range)]
7063 self._xml_data_element("f", formula, attributes)
7065 def _write_sheet_pr(self) -> None:
7066 # Write the <sheetPr> element for Sheet level properties.
7067 attributes = []
7069 if (
7070 not self.fit_page
7071 and not self.filter_on
7072 and not self.tab_color
7073 and not self.outline_changed
7074 and not self.vba_codename
7075 ):
7076 return
7078 if self.vba_codename:
7079 attributes.append(("codeName", self.vba_codename))
7081 if self.filter_on:
7082 attributes.append(("filterMode", 1))
7084 if self.fit_page or self.tab_color or self.outline_changed:
7085 self._xml_start_tag("sheetPr", attributes)
7086 self._write_tab_color()
7087 self._write_outline_pr()
7088 self._write_page_set_up_pr()
7089 self._xml_end_tag("sheetPr")
7090 else:
7091 self._xml_empty_tag("sheetPr", attributes)
7093 def _write_page_set_up_pr(self) -> None:
7094 # Write the <pageSetUpPr> element.
7095 if not self.fit_page:
7096 return
7098 attributes = [("fitToPage", 1)]
7099 self._xml_empty_tag("pageSetUpPr", attributes)
7101 def _write_tab_color(self) -> None:
7102 # Write the <tabColor> element.
7103 color = self.tab_color
7105 if not color:
7106 return
7108 self._write_color("tabColor", color._attributes())
7110 def _write_outline_pr(self) -> None:
7111 # Write the <outlinePr> element.
7112 attributes = []
7114 if not self.outline_changed:
7115 return
7117 if self.outline_style:
7118 attributes.append(("applyStyles", 1))
7119 if not self.outline_below:
7120 attributes.append(("summaryBelow", 0))
7121 if not self.outline_right:
7122 attributes.append(("summaryRight", 0))
7123 if not self.outline_on:
7124 attributes.append(("showOutlineSymbols", 0))
7126 self._xml_empty_tag("outlinePr", attributes)
7128 def _write_row_breaks(self) -> None:
7129 # Write the <rowBreaks> element.
7130 page_breaks = self._sort_pagebreaks(self.hbreaks)
7132 if not page_breaks:
7133 return
7135 count = len(page_breaks)
7137 attributes = [
7138 ("count", count),
7139 ("manualBreakCount", count),
7140 ]
7142 self._xml_start_tag("rowBreaks", attributes)
7144 for row_num in page_breaks:
7145 self._write_brk(row_num, 16383)
7147 self._xml_end_tag("rowBreaks")
7149 def _write_col_breaks(self) -> None:
7150 # Write the <colBreaks> element.
7151 page_breaks = self._sort_pagebreaks(self.vbreaks)
7153 if not page_breaks:
7154 return
7156 count = len(page_breaks)
7158 attributes = [
7159 ("count", count),
7160 ("manualBreakCount", count),
7161 ]
7163 self._xml_start_tag("colBreaks", attributes)
7165 for col_num in page_breaks:
7166 self._write_brk(col_num, 1048575)
7168 self._xml_end_tag("colBreaks")
7170 def _write_brk(self, brk_id, brk_max) -> None:
7171 # Write the <brk> element.
7172 attributes = [("id", brk_id), ("max", brk_max), ("man", 1)]
7174 self._xml_empty_tag("brk", attributes)
7176 def _write_merge_cells(self) -> None:
7177 # Write the <mergeCells> element.
7178 merged_cells = self.merge
7179 count = len(merged_cells)
7181 if not count:
7182 return
7184 attributes = [("count", count)]
7186 self._xml_start_tag("mergeCells", attributes)
7188 for merged_range in merged_cells:
7189 # Write the mergeCell element.
7190 self._write_merge_cell(merged_range)
7192 self._xml_end_tag("mergeCells")
7194 def _write_merge_cell(self, merged_range) -> None:
7195 # Write the <mergeCell> element.
7196 row_min, col_min, row_max, col_max = merged_range
7198 # Convert the merge dimensions to a cell range.
7199 cell_1 = xl_rowcol_to_cell(row_min, col_min)
7200 cell_2 = xl_rowcol_to_cell(row_max, col_max)
7201 ref = cell_1 + ":" + cell_2
7203 attributes = [("ref", ref)]
7205 self._xml_empty_tag("mergeCell", attributes)
7207 def _write_hyperlinks(self) -> None:
7208 # Process any stored hyperlinks in row/col order and write the
7209 # <hyperlinks> element. The attributes are different for internal
7210 # and external links.
7212 # Sort the hyperlinks into row order.
7213 row_nums = sorted(self.hyperlinks.keys())
7215 # Exit if there are no hyperlinks to process.
7216 if not row_nums:
7217 return
7219 # Write the hyperlink elements.
7220 self._xml_start_tag("hyperlinks")
7222 # Iterate over the rows.
7223 for row_num in row_nums:
7224 # Sort the hyperlinks into column order.
7225 col_nums = sorted(self.hyperlinks[row_num].keys())
7227 # Iterate over the columns.
7228 for col_num in col_nums:
7229 # Get the link data for this cell.
7230 url = self.hyperlinks[row_num][col_num]
7232 # If the cell was overwritten by the user and isn't a string
7233 # then we have to add the url as the string to display.
7234 if self.table and self.table[row_num] and self.table[row_num][col_num]:
7235 cell = self.table[row_num][col_num]
7236 if cell.__class__.__name__ != "String":
7237 url._is_object_link = True
7239 if url._link_type in (UrlTypes.URL, UrlTypes.EXTERNAL):
7240 # External link with rel file relationship.
7241 self.rel_count += 1
7243 self._write_hyperlink_external(
7244 row_num, col_num, self.rel_count, url
7245 )
7247 # Links for use by the packager.
7248 self.external_hyper_links.append(
7249 ["/hyperlink", url._target(), "External"]
7250 )
7251 else:
7252 # Internal link with rel file relationship.
7253 self._write_hyperlink_internal(row_num, col_num, url)
7255 self._xml_end_tag("hyperlinks")
7257 def _write_hyperlink_external(
7258 self, row: int, col: int, id_num: int, url: Url
7259 ) -> None:
7260 # Write the <hyperlink> element for external links.
7261 ref = xl_rowcol_to_cell(row, col)
7262 r_id = "rId" + str(id_num)
7264 attributes = [("ref", ref), ("r:id", r_id)]
7266 if url._anchor:
7267 attributes.append(("location", url._anchor))
7269 if url._is_object_link:
7270 attributes.append(("display", url._text))
7272 if url._tip:
7273 attributes.append(("tooltip", url._tip))
7275 self._xml_empty_tag("hyperlink", attributes)
7277 def _write_hyperlink_internal(self, row: int, col: int, url: Url) -> None:
7278 # Write the <hyperlink> element for internal links.
7279 ref = xl_rowcol_to_cell(row, col)
7281 attributes = [("ref", ref), ("location", url._link)]
7283 if url._tip:
7284 attributes.append(("tooltip", url._tip))
7286 attributes.append(("display", url._text))
7288 self._xml_empty_tag("hyperlink", attributes)
7290 def _write_auto_filter(self) -> None:
7291 # Write the <autoFilter> element.
7292 if not self.autofilter_ref:
7293 return
7295 attributes = [("ref", self.autofilter_ref)]
7297 if self.filter_on:
7298 # Autofilter defined active filters.
7299 self._xml_start_tag("autoFilter", attributes)
7300 self._write_autofilters()
7301 self._xml_end_tag("autoFilter")
7303 else:
7304 # Autofilter defined without active filters.
7305 self._xml_empty_tag("autoFilter", attributes)
7307 def _write_autofilters(self) -> None:
7308 # Function to iterate through the columns that form part of an
7309 # autofilter range and write the appropriate filters.
7310 col1, col2 = self.filter_range
7312 for col in range(col1, col2 + 1):
7313 # Skip if column doesn't have an active filter.
7314 if col not in self.filter_cols:
7315 continue
7317 # Retrieve the filter tokens and write the autofilter records.
7318 tokens = self.filter_cols[col]
7319 filter_type = self.filter_type[col]
7321 # Filters are relative to first column in the autofilter.
7322 self._write_filter_column(col - col1, filter_type, tokens)
7324 def _write_filter_column(self, col_id, filter_type, filters) -> None:
7325 # Write the <filterColumn> element.
7326 attributes = [("colId", col_id)]
7328 self._xml_start_tag("filterColumn", attributes)
7330 if filter_type == 1:
7331 # Type == 1 is the new XLSX style filter.
7332 self._write_filters(filters)
7333 else:
7334 # Type == 0 is the classic "custom" filter.
7335 self._write_custom_filters(filters)
7337 self._xml_end_tag("filterColumn")
7339 def _write_filters(self, filters) -> None:
7340 # Write the <filters> element.
7341 non_blanks = [filter for filter in filters if str(filter).lower() != "blanks"]
7342 attributes = []
7344 if len(filters) != len(non_blanks):
7345 attributes = [("blank", 1)]
7347 if len(filters) == 1 and len(non_blanks) == 0:
7348 # Special case for blank cells only.
7349 self._xml_empty_tag("filters", attributes)
7350 else:
7351 # General case.
7352 self._xml_start_tag("filters", attributes)
7354 for autofilter in sorted(non_blanks):
7355 self._write_filter(autofilter)
7357 self._xml_end_tag("filters")
7359 def _write_filter(self, val) -> None:
7360 # Write the <filter> element.
7361 attributes = [("val", val)]
7363 self._xml_empty_tag("filter", attributes)
7365 def _write_custom_filters(self, tokens) -> None:
7366 # Write the <customFilters> element.
7367 if len(tokens) == 2:
7368 # One filter expression only.
7369 self._xml_start_tag("customFilters")
7370 self._write_custom_filter(*tokens)
7371 self._xml_end_tag("customFilters")
7372 else:
7373 # Two filter expressions.
7374 attributes = []
7376 # Check if the "join" operand is "and" or "or".
7377 if tokens[2] == 0:
7378 attributes = [("and", 1)]
7379 else:
7380 attributes = [("and", 0)]
7382 # Write the two custom filters.
7383 self._xml_start_tag("customFilters", attributes)
7384 self._write_custom_filter(tokens[0], tokens[1])
7385 self._write_custom_filter(tokens[3], tokens[4])
7386 self._xml_end_tag("customFilters")
7388 def _write_custom_filter(self, operator, val) -> None:
7389 # Write the <customFilter> element.
7390 attributes = []
7392 operators = {
7393 1: "lessThan",
7394 2: "equal",
7395 3: "lessThanOrEqual",
7396 4: "greaterThan",
7397 5: "notEqual",
7398 6: "greaterThanOrEqual",
7399 22: "equal",
7400 }
7402 # Convert the operator from a number to a descriptive string.
7403 if operators[operator] is not None:
7404 operator = operators[operator]
7405 else:
7406 warn(f"Unknown operator = {operator}")
7408 # The 'equal' operator is the default attribute and isn't stored.
7409 if operator != "equal":
7410 attributes.append(("operator", operator))
7411 attributes.append(("val", val))
7413 self._xml_empty_tag("customFilter", attributes)
7415 def _write_sheet_protection(self) -> None:
7416 # Write the <sheetProtection> element.
7417 attributes = []
7419 if not self.protect_options:
7420 return
7422 options = self.protect_options
7424 if options["password"]:
7425 attributes.append(("password", options["password"]))
7426 if options["sheet"]:
7427 attributes.append(("sheet", 1))
7428 if options["content"]:
7429 attributes.append(("content", 1))
7430 if not options["objects"]:
7431 attributes.append(("objects", 1))
7432 if not options["scenarios"]:
7433 attributes.append(("scenarios", 1))
7434 if options["format_cells"]:
7435 attributes.append(("formatCells", 0))
7436 if options["format_columns"]:
7437 attributes.append(("formatColumns", 0))
7438 if options["format_rows"]:
7439 attributes.append(("formatRows", 0))
7440 if options["insert_columns"]:
7441 attributes.append(("insertColumns", 0))
7442 if options["insert_rows"]:
7443 attributes.append(("insertRows", 0))
7444 if options["insert_hyperlinks"]:
7445 attributes.append(("insertHyperlinks", 0))
7446 if options["delete_columns"]:
7447 attributes.append(("deleteColumns", 0))
7448 if options["delete_rows"]:
7449 attributes.append(("deleteRows", 0))
7450 if not options["select_locked_cells"]:
7451 attributes.append(("selectLockedCells", 1))
7452 if options["sort"]:
7453 attributes.append(("sort", 0))
7454 if options["autofilter"]:
7455 attributes.append(("autoFilter", 0))
7456 if options["pivot_tables"]:
7457 attributes.append(("pivotTables", 0))
7458 if not options["select_unlocked_cells"]:
7459 attributes.append(("selectUnlockedCells", 1))
7461 self._xml_empty_tag("sheetProtection", attributes)
7463 def _write_protected_ranges(self) -> None:
7464 # Write the <protectedRanges> element.
7465 if self.num_protected_ranges == 0:
7466 return
7468 self._xml_start_tag("protectedRanges")
7470 for cell_range, range_name, password in self.protected_ranges:
7471 self._write_protected_range(cell_range, range_name, password)
7473 self._xml_end_tag("protectedRanges")
7475 def _write_protected_range(self, cell_range, range_name, password) -> None:
7476 # Write the <protectedRange> element.
7477 attributes = []
7479 if password:
7480 attributes.append(("password", password))
7482 attributes.append(("sqref", cell_range))
7483 attributes.append(("name", range_name))
7485 self._xml_empty_tag("protectedRange", attributes)
7487 def _write_drawings(self) -> None:
7488 # Write the <drawing> elements.
7489 if not self.drawing:
7490 return
7492 self.rel_count += 1
7493 self._write_drawing(self.rel_count)
7495 def _write_drawing(self, drawing_id) -> None:
7496 # Write the <drawing> element.
7497 r_id = "rId" + str(drawing_id)
7499 attributes = [("r:id", r_id)]
7501 self._xml_empty_tag("drawing", attributes)
7503 def _write_legacy_drawing(self) -> None:
7504 # Write the <legacyDrawing> element.
7505 if not self.has_vml:
7506 return
7508 # Increment the relationship id for any drawings or comments.
7509 self.rel_count += 1
7510 r_id = "rId" + str(self.rel_count)
7512 attributes = [("r:id", r_id)]
7514 self._xml_empty_tag("legacyDrawing", attributes)
7516 def _write_legacy_drawing_hf(self) -> None:
7517 # Write the <legacyDrawingHF> element.
7518 if not self.has_header_vml:
7519 return
7521 # Increment the relationship id for any drawings or comments.
7522 self.rel_count += 1
7523 r_id = "rId" + str(self.rel_count)
7525 attributes = [("r:id", r_id)]
7527 self._xml_empty_tag("legacyDrawingHF", attributes)
7529 def _write_picture(self) -> None:
7530 # Write the <picture> element.
7531 if not self.background_image:
7532 return
7534 # Increment the relationship id.
7535 self.rel_count += 1
7536 r_id = "rId" + str(self.rel_count)
7538 attributes = [("r:id", r_id)]
7540 self._xml_empty_tag("picture", attributes)
7542 def _write_data_validations(self) -> None:
7543 # Write the <dataValidations> element.
7544 validations = self.validations
7545 count = len(validations)
7547 if not count:
7548 return
7550 attributes = [("count", count)]
7552 self._xml_start_tag("dataValidations", attributes)
7554 for validation in validations:
7555 # Write the dataValidation element.
7556 self._write_data_validation(validation)
7558 self._xml_end_tag("dataValidations")
7560 def _write_data_validation(self, options) -> None:
7561 # Write the <dataValidation> element.
7562 sqref = ""
7563 attributes = []
7565 # Set the cell range(s) for the data validation.
7566 for cells in options["cells"]:
7567 # Add a space between multiple cell ranges.
7568 if sqref != "":
7569 sqref += " "
7571 row_first, col_first, row_last, col_last = cells
7573 # Swap last row/col for first row/col as necessary
7574 if row_first > row_last:
7575 row_first, row_last = (row_last, row_first)
7577 if col_first > col_last:
7578 col_first, col_last = (col_last, col_first)
7580 sqref += xl_range(row_first, col_first, row_last, col_last)
7582 if options.get("multi_range"):
7583 sqref = options["multi_range"]
7585 if options["validate"] != "none":
7586 attributes.append(("type", options["validate"]))
7588 if options["criteria"] != "between":
7589 attributes.append(("operator", options["criteria"]))
7591 if "error_type" in options:
7592 if options["error_type"] == 1:
7593 attributes.append(("errorStyle", "warning"))
7594 if options["error_type"] == 2:
7595 attributes.append(("errorStyle", "information"))
7597 if options["ignore_blank"]:
7598 attributes.append(("allowBlank", 1))
7600 if not options["dropdown"]:
7601 attributes.append(("showDropDown", 1))
7603 if options["show_input"]:
7604 attributes.append(("showInputMessage", 1))
7606 if options["show_error"]:
7607 attributes.append(("showErrorMessage", 1))
7609 if "error_title" in options:
7610 attributes.append(("errorTitle", options["error_title"]))
7612 if "error_message" in options:
7613 attributes.append(("error", options["error_message"]))
7615 if "input_title" in options:
7616 attributes.append(("promptTitle", options["input_title"]))
7618 if "input_message" in options:
7619 attributes.append(("prompt", options["input_message"]))
7621 attributes.append(("sqref", sqref))
7623 if options["validate"] == "none":
7624 self._xml_empty_tag("dataValidation", attributes)
7625 else:
7626 self._xml_start_tag("dataValidation", attributes)
7628 # Write the formula1 element.
7629 self._write_formula_1(options["value"])
7631 # Write the formula2 element.
7632 if options["maximum"] is not None:
7633 self._write_formula_2(options["maximum"])
7635 self._xml_end_tag("dataValidation")
7637 def _write_formula_1(self, formula) -> None:
7638 # Write the <formula1> element.
7640 if isinstance(formula, list):
7641 formula = self._csv_join(*formula)
7642 formula = f'"{formula}"'
7643 else:
7644 # Check if the formula is a number.
7645 try:
7646 float(formula)
7647 except ValueError:
7648 # Not a number, treat as a formula.
7649 formula = self._prepare_formula(formula, True)
7651 self._xml_data_element("formula1", formula)
7653 def _write_formula_2(self, formula) -> None:
7654 # Write the <formula2> element.
7656 # Check if the formula is a number.
7657 try:
7658 float(formula)
7659 except ValueError:
7660 # Not a number, treat as a formula.
7661 formula = self._prepare_formula(formula, True)
7663 self._xml_data_element("formula2", formula)
7665 def _write_conditional_formats(self) -> None:
7666 # Write the Worksheet conditional formats, in the order that the ranges
7667 # were added in.
7668 for cond_range, params in self.cond_formats.items():
7669 self._write_conditional_formatting(cond_range, params)
7671 def _write_conditional_formatting(self, cond_range, params) -> None:
7672 # Write the <conditionalFormatting> element.
7673 attributes = [("sqref", cond_range)]
7674 self._xml_start_tag("conditionalFormatting", attributes)
7675 for param in params:
7676 # Write the cfRule element.
7677 self._write_cf_rule(param)
7678 self._xml_end_tag("conditionalFormatting")
7680 def _write_cf_rule(self, params) -> None:
7681 # Write the <cfRule> element.
7682 attributes = [("type", params["type"])]
7684 if "format" in params and params["format"] is not None:
7685 attributes.append(("dxfId", params["format"]))
7687 attributes.append(("priority", params["priority"]))
7689 if params.get("stop_if_true"):
7690 attributes.append(("stopIfTrue", 1))
7692 if params["type"] == "cellIs":
7693 attributes.append(("operator", params["criteria"]))
7695 self._xml_start_tag("cfRule", attributes)
7697 if "minimum" in params and "maximum" in params:
7698 self._write_formula_element(params["minimum"])
7699 self._write_formula_element(params["maximum"])
7700 else:
7701 self._write_formula_element(params["value"])
7703 self._xml_end_tag("cfRule")
7705 elif params["type"] == "aboveAverage":
7706 if re.search("below", params["criteria"]):
7707 attributes.append(("aboveAverage", 0))
7709 if re.search("equal", params["criteria"]):
7710 attributes.append(("equalAverage", 1))
7712 if re.search("[123] std dev", params["criteria"]):
7713 match = re.search("([123]) std dev", params["criteria"])
7714 attributes.append(("stdDev", match.group(1)))
7716 self._xml_empty_tag("cfRule", attributes)
7718 elif params["type"] == "top10":
7719 if "criteria" in params and params["criteria"] == "%":
7720 attributes.append(("percent", 1))
7722 if "direction" in params:
7723 attributes.append(("bottom", 1))
7725 rank = params["value"] or 10
7726 attributes.append(("rank", rank))
7728 self._xml_empty_tag("cfRule", attributes)
7730 elif params["type"] in ("duplicateValues", "uniqueValues"):
7731 self._xml_empty_tag("cfRule", attributes)
7733 elif (
7734 params["type"] == "containsText"
7735 or params["type"] == "notContainsText"
7736 or params["type"] == "beginsWith"
7737 or params["type"] == "endsWith"
7738 ):
7739 attributes.append(("operator", params["criteria"]))
7740 attributes.append(("text", params["value"]))
7741 self._xml_start_tag("cfRule", attributes)
7742 self._write_formula_element(params["formula"])
7743 self._xml_end_tag("cfRule")
7745 elif params["type"] == "timePeriod":
7746 attributes.append(("timePeriod", params["criteria"]))
7747 self._xml_start_tag("cfRule", attributes)
7748 self._write_formula_element(params["formula"])
7749 self._xml_end_tag("cfRule")
7751 elif (
7752 params["type"] == "containsBlanks"
7753 or params["type"] == "notContainsBlanks"
7754 or params["type"] == "containsErrors"
7755 or params["type"] == "notContainsErrors"
7756 ):
7757 self._xml_start_tag("cfRule", attributes)
7758 self._write_formula_element(params["formula"])
7759 self._xml_end_tag("cfRule")
7761 elif params["type"] == "colorScale":
7762 self._xml_start_tag("cfRule", attributes)
7763 self._write_color_scale(params)
7764 self._xml_end_tag("cfRule")
7766 elif params["type"] == "dataBar":
7767 self._xml_start_tag("cfRule", attributes)
7768 self._write_data_bar(params)
7770 if params.get("is_data_bar_2010"):
7771 self._write_data_bar_ext(params)
7773 self._xml_end_tag("cfRule")
7775 elif params["type"] == "expression":
7776 self._xml_start_tag("cfRule", attributes)
7777 self._write_formula_element(params["criteria"])
7778 self._xml_end_tag("cfRule")
7780 elif params["type"] == "iconSet":
7781 self._xml_start_tag("cfRule", attributes)
7782 self._write_icon_set(params)
7783 self._xml_end_tag("cfRule")
7785 def _write_formula_element(self, formula) -> None:
7786 # Write the <formula> element.
7788 # Check if the formula is a number.
7789 try:
7790 float(formula)
7791 except ValueError:
7792 # Not a number, treat as a formula.
7793 formula = self._prepare_formula(formula, True)
7795 self._xml_data_element("formula", formula)
7797 def _write_color_scale(self, param) -> None:
7798 # Write the <colorScale> element.
7800 self._xml_start_tag("colorScale")
7802 self._write_cfvo(param["min_type"], param["min_value"])
7804 if param["mid_type"] is not None:
7805 self._write_cfvo(param["mid_type"], param["mid_value"])
7807 self._write_cfvo(param["max_type"], param["max_value"])
7809 self._write_color("color", param["min_color"]._attributes())
7811 if param["mid_color"] is not None:
7812 self._write_color("color", param["mid_color"]._attributes())
7814 self._write_color("color", param["max_color"]._attributes())
7816 self._xml_end_tag("colorScale")
7818 def _write_data_bar(self, param) -> None:
7819 # Write the <dataBar> element.
7820 attributes = []
7822 # Min and max bar lengths in in the spec but not supported directly by
7823 # Excel.
7824 if "min_length" in param:
7825 attributes.append(("minLength", param["min_length"]))
7827 if "max_length" in param:
7828 attributes.append(("maxLength", param["max_length"]))
7830 if param.get("bar_only"):
7831 attributes.append(("showValue", 0))
7833 self._xml_start_tag("dataBar", attributes)
7835 self._write_cfvo(param["min_type"], param["min_value"])
7836 self._write_cfvo(param["max_type"], param["max_value"])
7837 self._write_color("color", param["bar_color"]._attributes())
7839 self._xml_end_tag("dataBar")
7841 def _write_data_bar_ext(self, param) -> None:
7842 # Write the <extLst> dataBar extension element.
7844 # Create a pseudo GUID for each unique Excel 2010 data bar.
7845 worksheet_count = self.index + 1
7846 data_bar_count = len(self.data_bars_2010) + 1
7847 guid = f"{{DA7ABA51-AAAA-BBBB-{worksheet_count:04X}-{data_bar_count:012X}}}"
7849 # Store the 2010 data bar parameters to write the extLst elements.
7850 param["guid"] = guid
7851 self.data_bars_2010.append(param)
7853 self._xml_start_tag("extLst")
7854 self._write_ext("{B025F937-C7B1-47D3-B67F-A62EFF666E3E}")
7855 self._xml_data_element("x14:id", guid)
7856 self._xml_end_tag("ext")
7857 self._xml_end_tag("extLst")
7859 def _write_icon_set(self, param) -> None:
7860 # Write the <iconSet> element.
7861 attributes = []
7863 # Don't set attribute for default style.
7864 if param["icon_style"] != "3TrafficLights":
7865 attributes = [("iconSet", param["icon_style"])]
7867 if param.get("icons_only"):
7868 attributes.append(("showValue", 0))
7870 if param.get("reverse_icons"):
7871 attributes.append(("reverse", 1))
7873 self._xml_start_tag("iconSet", attributes)
7875 # Write the properties for different icon styles.
7876 for icon in reversed(param["icons"]):
7877 self._write_cfvo(icon["type"], icon["value"], icon["criteria"])
7879 self._xml_end_tag("iconSet")
7881 def _write_cfvo(self, cf_type, val, criteria=None) -> None:
7882 # Write the <cfvo> element.
7883 attributes = [("type", cf_type)]
7885 if val is not None:
7886 attributes.append(("val", val))
7888 if criteria:
7889 attributes.append(("gte", 0))
7891 self._xml_empty_tag("cfvo", attributes)
7893 def _write_color(self, name, attributes) -> None:
7894 # Write the <color> element.
7895 self._xml_empty_tag(name, attributes)
7897 def _write_selections(self) -> None:
7898 # Write the <selection> elements.
7899 for selection in self.selections:
7900 self._write_selection(*selection)
7902 def _write_selection(self, pane, active_cell, sqref) -> None:
7903 # Write the <selection> element.
7904 attributes = []
7906 if pane:
7907 attributes.append(("pane", pane))
7909 if active_cell:
7910 attributes.append(("activeCell", active_cell))
7912 if sqref:
7913 attributes.append(("sqref", sqref))
7915 self._xml_empty_tag("selection", attributes)
7917 def _write_panes(self) -> None:
7918 # Write the frozen or split <pane> elements.
7919 panes = self.panes
7921 if not panes:
7922 return
7924 if panes[4] == 2:
7925 self._write_split_panes(*panes)
7926 else:
7927 self._write_freeze_panes(*panes)
7929 def _write_freeze_panes(
7930 self, row: int, col: int, top_row, left_col, pane_type
7931 ) -> None:
7932 # Write the <pane> element for freeze panes.
7933 attributes = []
7935 y_split = row
7936 x_split = col
7937 top_left_cell = xl_rowcol_to_cell(top_row, left_col)
7938 active_pane = ""
7939 state = ""
7940 active_cell = ""
7941 sqref = ""
7943 # Move user cell selection to the panes.
7944 if self.selections:
7945 _, active_cell, sqref = self.selections[0]
7946 self.selections = []
7948 # Set the active pane.
7949 if row and col:
7950 active_pane = "bottomRight"
7952 row_cell = xl_rowcol_to_cell(row, 0)
7953 col_cell = xl_rowcol_to_cell(0, col)
7955 self.selections.append(["topRight", col_cell, col_cell])
7956 self.selections.append(["bottomLeft", row_cell, row_cell])
7957 self.selections.append(["bottomRight", active_cell, sqref])
7959 elif col:
7960 active_pane = "topRight"
7961 self.selections.append(["topRight", active_cell, sqref])
7963 else:
7964 active_pane = "bottomLeft"
7965 self.selections.append(["bottomLeft", active_cell, sqref])
7967 # Set the pane type.
7968 if pane_type == 0:
7969 state = "frozen"
7970 elif pane_type == 1:
7971 state = "frozenSplit"
7972 else:
7973 state = "split"
7975 if x_split:
7976 attributes.append(("xSplit", x_split))
7978 if y_split:
7979 attributes.append(("ySplit", y_split))
7981 attributes.append(("topLeftCell", top_left_cell))
7982 attributes.append(("activePane", active_pane))
7983 attributes.append(("state", state))
7985 self._xml_empty_tag("pane", attributes)
7987 def _write_split_panes(self, row: int, col: int, top_row, left_col, _) -> None:
7988 # Write the <pane> element for split panes.
7989 attributes = []
7990 has_selection = False
7991 active_pane = ""
7992 active_cell = ""
7993 sqref = ""
7995 y_split = row
7996 x_split = col
7998 # Move user cell selection to the panes.
7999 if self.selections:
8000 _, active_cell, sqref = self.selections[0]
8001 self.selections = []
8002 has_selection = True
8004 # Convert the row and col to 1/20 twip units with padding.
8005 if y_split:
8006 y_split = int(20 * y_split + 300)
8008 if x_split:
8009 x_split = self._calculate_x_split_width(x_split)
8011 # For non-explicit topLeft definitions, estimate the cell offset based
8012 # on the pixels dimensions. This is only a workaround and doesn't take
8013 # adjusted cell dimensions into account.
8014 if top_row == row and left_col == col:
8015 top_row = int(0.5 + (y_split - 300) / 20 / 15)
8016 left_col = int(0.5 + (x_split - 390) / 20 / 3 * 4 / 64)
8018 top_left_cell = xl_rowcol_to_cell(top_row, left_col)
8020 # If there is no selection set the active cell to the top left cell.
8021 if not has_selection:
8022 active_cell = top_left_cell
8023 sqref = top_left_cell
8025 # Set the Cell selections.
8026 if row and col:
8027 active_pane = "bottomRight"
8029 row_cell = xl_rowcol_to_cell(top_row, 0)
8030 col_cell = xl_rowcol_to_cell(0, left_col)
8032 self.selections.append(["topRight", col_cell, col_cell])
8033 self.selections.append(["bottomLeft", row_cell, row_cell])
8034 self.selections.append(["bottomRight", active_cell, sqref])
8036 elif col:
8037 active_pane = "topRight"
8038 self.selections.append(["topRight", active_cell, sqref])
8040 else:
8041 active_pane = "bottomLeft"
8042 self.selections.append(["bottomLeft", active_cell, sqref])
8044 # Format splits to the same precision as Excel.
8045 if x_split:
8046 attributes.append(("xSplit", f"{x_split:.16g}"))
8048 if y_split:
8049 attributes.append(("ySplit", f"{y_split:.16g}"))
8051 attributes.append(("topLeftCell", top_left_cell))
8053 if has_selection:
8054 attributes.append(("activePane", active_pane))
8056 self._xml_empty_tag("pane", attributes)
8058 def _calculate_x_split_width(self, width):
8059 # Convert column width from user units to pane split width.
8061 max_digit_width = 7 # For Calabri 11.
8062 padding = 5
8064 # Convert to pixels.
8065 if width < 1:
8066 pixels = int(width * (max_digit_width + padding) + 0.5)
8067 else:
8068 pixels = int(width * max_digit_width + 0.5) + padding
8070 # Convert to points.
8071 points = pixels * 3 / 4
8073 # Convert to twips (twentieths of a point).
8074 twips = points * 20
8076 # Add offset/padding.
8077 width = twips + 390
8079 return width
8081 def _write_table_parts(self) -> None:
8082 # Write the <tableParts> element.
8083 tables = self.tables
8084 count = len(tables)
8086 # Return if worksheet doesn't contain any tables.
8087 if not count:
8088 return
8090 attributes = [
8091 (
8092 "count",
8093 count,
8094 )
8095 ]
8097 self._xml_start_tag("tableParts", attributes)
8099 for _ in tables:
8100 # Write the tablePart element.
8101 self.rel_count += 1
8102 self._write_table_part(self.rel_count)
8104 self._xml_end_tag("tableParts")
8106 def _write_table_part(self, r_id) -> None:
8107 # Write the <tablePart> element.
8109 r_id = "rId" + str(r_id)
8111 attributes = [
8112 (
8113 "r:id",
8114 r_id,
8115 )
8116 ]
8118 self._xml_empty_tag("tablePart", attributes)
8120 def _write_ext_list(self) -> None:
8121 # Write the <extLst> element for data bars and sparklines.
8122 has_data_bars = len(self.data_bars_2010)
8123 has_sparklines = len(self.sparklines)
8125 if not has_data_bars and not has_sparklines:
8126 return
8128 # Write the extLst element.
8129 self._xml_start_tag("extLst")
8131 if has_data_bars:
8132 self._write_ext_list_data_bars()
8134 if has_sparklines:
8135 self._write_ext_list_sparklines()
8137 self._xml_end_tag("extLst")
8139 def _write_ext_list_data_bars(self) -> None:
8140 # Write the Excel 2010 data_bar subelements.
8141 self._write_ext("{78C0D931-6437-407d-A8EE-F0AAD7539E65}")
8143 self._xml_start_tag("x14:conditionalFormattings")
8145 # Write the Excel 2010 conditional formatting data bar elements.
8146 for data_bar in self.data_bars_2010:
8147 # Write the x14:conditionalFormatting element.
8148 self._write_conditional_formatting_2010(data_bar)
8150 self._xml_end_tag("x14:conditionalFormattings")
8151 self._xml_end_tag("ext")
8153 def _write_conditional_formatting_2010(self, data_bar) -> None:
8154 # Write the <x14:conditionalFormatting> element.
8155 xmlns_xm = "http://schemas.microsoft.com/office/excel/2006/main"
8157 attributes = [("xmlns:xm", xmlns_xm)]
8159 self._xml_start_tag("x14:conditionalFormatting", attributes)
8161 # Write the x14:cfRule element.
8162 self._write_x14_cf_rule(data_bar)
8164 # Write the x14:dataBar element.
8165 self._write_x14_data_bar(data_bar)
8167 # Write the x14 max and min data bars.
8168 self._write_x14_cfvo(data_bar["x14_min_type"], data_bar["min_value"])
8169 self._write_x14_cfvo(data_bar["x14_max_type"], data_bar["max_value"])
8171 if not data_bar["bar_no_border"]:
8172 # Write the x14:borderColor element.
8173 self._write_x14_border_color(data_bar["bar_border_color"])
8175 # Write the x14:negativeFillColor element.
8176 if not data_bar["bar_negative_color_same"]:
8177 self._write_x14_negative_fill_color(data_bar["bar_negative_color"])
8179 # Write the x14:negativeBorderColor element.
8180 if (
8181 not data_bar["bar_no_border"]
8182 and not data_bar["bar_negative_border_color_same"]
8183 ):
8184 self._write_x14_negative_border_color(data_bar["bar_negative_border_color"])
8186 # Write the x14:axisColor element.
8187 if data_bar["bar_axis_position"] != "none":
8188 self._write_x14_axis_color(data_bar["bar_axis_color"])
8190 self._xml_end_tag("x14:dataBar")
8191 self._xml_end_tag("x14:cfRule")
8193 # Write the xm:sqref element.
8194 self._xml_data_element("xm:sqref", data_bar["range"])
8196 self._xml_end_tag("x14:conditionalFormatting")
8198 def _write_x14_cf_rule(self, data_bar) -> None:
8199 # Write the <x14:cfRule> element.
8200 rule_type = "dataBar"
8201 guid = data_bar["guid"]
8202 attributes = [("type", rule_type), ("id", guid)]
8204 self._xml_start_tag("x14:cfRule", attributes)
8206 def _write_x14_data_bar(self, data_bar) -> None:
8207 # Write the <x14:dataBar> element.
8208 min_length = 0
8209 max_length = 100
8211 attributes = [
8212 ("minLength", min_length),
8213 ("maxLength", max_length),
8214 ]
8216 if not data_bar["bar_no_border"]:
8217 attributes.append(("border", 1))
8219 if data_bar["bar_solid"]:
8220 attributes.append(("gradient", 0))
8222 if data_bar["bar_direction"] == "left":
8223 attributes.append(("direction", "leftToRight"))
8225 if data_bar["bar_direction"] == "right":
8226 attributes.append(("direction", "rightToLeft"))
8228 if data_bar["bar_negative_color_same"]:
8229 attributes.append(("negativeBarColorSameAsPositive", 1))
8231 if (
8232 not data_bar["bar_no_border"]
8233 and not data_bar["bar_negative_border_color_same"]
8234 ):
8235 attributes.append(("negativeBarBorderColorSameAsPositive", 0))
8237 if data_bar["bar_axis_position"] == "middle":
8238 attributes.append(("axisPosition", "middle"))
8240 if data_bar["bar_axis_position"] == "none":
8241 attributes.append(("axisPosition", "none"))
8243 self._xml_start_tag("x14:dataBar", attributes)
8245 def _write_x14_cfvo(self, rule_type, value) -> None:
8246 # Write the <x14:cfvo> element.
8247 attributes = [("type", rule_type)]
8249 if rule_type in ("min", "max", "autoMin", "autoMax"):
8250 self._xml_empty_tag("x14:cfvo", attributes)
8251 else:
8252 self._xml_start_tag("x14:cfvo", attributes)
8253 self._xml_data_element("xm:f", value)
8254 self._xml_end_tag("x14:cfvo")
8256 def _write_x14_border_color(self, color) -> None:
8257 # Write the <x14:borderColor> element.
8258 self._write_color("x14:borderColor", color._attributes())
8260 def _write_x14_negative_fill_color(self, color) -> None:
8261 # Write the <x14:negativeFillColor> element.
8262 self._xml_empty_tag("x14:negativeFillColor", color._attributes())
8264 def _write_x14_negative_border_color(self, color) -> None:
8265 # Write the <x14:negativeBorderColor> element.
8266 self._xml_empty_tag("x14:negativeBorderColor", color._attributes())
8268 def _write_x14_axis_color(self, color) -> None:
8269 # Write the <x14:axisColor> element.
8270 self._xml_empty_tag("x14:axisColor", color._attributes())
8272 def _write_ext_list_sparklines(self) -> None:
8273 # Write the sparkline extension sub-elements.
8274 self._write_ext("{05C60535-1F16-4fd2-B633-F4F36F0B64E0}")
8276 # Write the x14:sparklineGroups element.
8277 self._write_sparkline_groups()
8279 # Write the sparkline elements.
8280 for sparkline in reversed(self.sparklines):
8281 # Write the x14:sparklineGroup element.
8282 self._write_sparkline_group(sparkline)
8284 # Write the x14:colorSeries element.
8285 self._write_color_series(sparkline["series_color"])
8287 # Write the x14:colorNegative element.
8288 self._write_color_negative(sparkline["negative_color"])
8290 # Write the x14:colorAxis element.
8291 self._write_color_axis()
8293 # Write the x14:colorMarkers element.
8294 self._write_color_markers(sparkline["markers_color"])
8296 # Write the x14:colorFirst element.
8297 self._write_color_first(sparkline["first_color"])
8299 # Write the x14:colorLast element.
8300 self._write_color_last(sparkline["last_color"])
8302 # Write the x14:colorHigh element.
8303 self._write_color_high(sparkline["high_color"])
8305 # Write the x14:colorLow element.
8306 self._write_color_low(sparkline["low_color"])
8308 if sparkline["date_axis"]:
8309 self._xml_data_element("xm:f", sparkline["date_axis"])
8311 self._write_sparklines(sparkline)
8313 self._xml_end_tag("x14:sparklineGroup")
8315 self._xml_end_tag("x14:sparklineGroups")
8316 self._xml_end_tag("ext")
8318 def _write_sparklines(self, sparkline) -> None:
8319 # Write the <x14:sparklines> element and <x14:sparkline> sub-elements.
8321 # Write the sparkline elements.
8322 self._xml_start_tag("x14:sparklines")
8324 for i in range(sparkline["count"]):
8325 spark_range = sparkline["ranges"][i]
8326 location = sparkline["locations"][i]
8328 self._xml_start_tag("x14:sparkline")
8329 self._xml_data_element("xm:f", spark_range)
8330 self._xml_data_element("xm:sqref", location)
8331 self._xml_end_tag("x14:sparkline")
8333 self._xml_end_tag("x14:sparklines")
8335 def _write_ext(self, uri) -> None:
8336 # Write the <ext> element.
8337 schema = "http://schemas.microsoft.com/office/"
8338 xmlns_x14 = schema + "spreadsheetml/2009/9/main"
8340 attributes = [
8341 ("xmlns:x14", xmlns_x14),
8342 ("uri", uri),
8343 ]
8345 self._xml_start_tag("ext", attributes)
8347 def _write_sparkline_groups(self) -> None:
8348 # Write the <x14:sparklineGroups> element.
8349 xmlns_xm = "http://schemas.microsoft.com/office/excel/2006/main"
8351 attributes = [("xmlns:xm", xmlns_xm)]
8353 self._xml_start_tag("x14:sparklineGroups", attributes)
8355 def _write_sparkline_group(self, options) -> None:
8356 # Write the <x14:sparklineGroup> element.
8357 #
8358 # Example for order.
8359 #
8360 # <x14:sparklineGroup
8361 # manualMax="0"
8362 # manualMin="0"
8363 # lineWeight="2.25"
8364 # type="column"
8365 # dateAxis="1"
8366 # displayEmptyCellsAs="span"
8367 # markers="1"
8368 # high="1"
8369 # low="1"
8370 # first="1"
8371 # last="1"
8372 # negative="1"
8373 # displayXAxis="1"
8374 # displayHidden="1"
8375 # minAxisType="custom"
8376 # maxAxisType="custom"
8377 # rightToLeft="1">
8378 #
8379 empty = options.get("empty")
8380 attributes = []
8382 if options.get("max") is not None:
8383 if options["max"] == "group":
8384 options["cust_max"] = "group"
8385 else:
8386 attributes.append(("manualMax", options["max"]))
8387 options["cust_max"] = "custom"
8389 if options.get("min") is not None:
8390 if options["min"] == "group":
8391 options["cust_min"] = "group"
8392 else:
8393 attributes.append(("manualMin", options["min"]))
8394 options["cust_min"] = "custom"
8396 # Ignore the default type attribute (line).
8397 if options["type"] != "line":
8398 attributes.append(("type", options["type"]))
8400 if options.get("weight"):
8401 attributes.append(("lineWeight", options["weight"]))
8403 if options.get("date_axis"):
8404 attributes.append(("dateAxis", 1))
8406 if empty:
8407 attributes.append(("displayEmptyCellsAs", empty))
8409 if options.get("markers"):
8410 attributes.append(("markers", 1))
8412 if options.get("high"):
8413 attributes.append(("high", 1))
8415 if options.get("low"):
8416 attributes.append(("low", 1))
8418 if options.get("first"):
8419 attributes.append(("first", 1))
8421 if options.get("last"):
8422 attributes.append(("last", 1))
8424 if options.get("negative"):
8425 attributes.append(("negative", 1))
8427 if options.get("axis"):
8428 attributes.append(("displayXAxis", 1))
8430 if options.get("hidden"):
8431 attributes.append(("displayHidden", 1))
8433 if options.get("cust_min"):
8434 attributes.append(("minAxisType", options["cust_min"]))
8436 if options.get("cust_max"):
8437 attributes.append(("maxAxisType", options["cust_max"]))
8439 if options.get("reverse"):
8440 attributes.append(("rightToLeft", 1))
8442 self._xml_start_tag("x14:sparklineGroup", attributes)
8444 def _write_spark_color(self, tag, color) -> None:
8445 # Helper function for the sparkline color functions below.
8446 if color:
8447 self._write_color(tag, color._attributes())
8449 def _write_color_series(self, color) -> None:
8450 # Write the <x14:colorSeries> element.
8451 self._write_spark_color("x14:colorSeries", color)
8453 def _write_color_negative(self, color) -> None:
8454 # Write the <x14:colorNegative> element.
8455 self._write_spark_color("x14:colorNegative", color)
8457 def _write_color_axis(self) -> None:
8458 # Write the <x14:colorAxis> element.
8459 self._write_spark_color("x14:colorAxis", Color("#000000"))
8461 def _write_color_markers(self, color) -> None:
8462 # Write the <x14:colorMarkers> element.
8463 self._write_spark_color("x14:colorMarkers", color)
8465 def _write_color_first(self, color) -> None:
8466 # Write the <x14:colorFirst> element.
8467 self._write_spark_color("x14:colorFirst", color)
8469 def _write_color_last(self, color) -> None:
8470 # Write the <x14:colorLast> element.
8471 self._write_spark_color("x14:colorLast", color)
8473 def _write_color_high(self, color) -> None:
8474 # Write the <x14:colorHigh> element.
8475 self._write_spark_color("x14:colorHigh", color)
8477 def _write_color_low(self, color) -> None:
8478 # Write the <x14:colorLow> element.
8479 self._write_spark_color("x14:colorLow", color)
8481 def _write_phonetic_pr(self) -> None:
8482 # Write the <phoneticPr> element.
8483 attributes = [
8484 ("fontId", "0"),
8485 ("type", "noConversion"),
8486 ]
8488 self._xml_empty_tag("phoneticPr", attributes)
8490 def _write_ignored_errors(self) -> None:
8491 # Write the <ignoredErrors> element.
8492 if not self.ignored_errors:
8493 return
8495 self._xml_start_tag("ignoredErrors")
8497 if self.ignored_errors.get("number_stored_as_text"):
8498 ignored_range = self.ignored_errors["number_stored_as_text"]
8499 self._write_ignored_error("numberStoredAsText", ignored_range)
8501 if self.ignored_errors.get("eval_error"):
8502 ignored_range = self.ignored_errors["eval_error"]
8503 self._write_ignored_error("evalError", ignored_range)
8505 if self.ignored_errors.get("formula_differs"):
8506 ignored_range = self.ignored_errors["formula_differs"]
8507 self._write_ignored_error("formula", ignored_range)
8509 if self.ignored_errors.get("formula_range"):
8510 ignored_range = self.ignored_errors["formula_range"]
8511 self._write_ignored_error("formulaRange", ignored_range)
8513 if self.ignored_errors.get("formula_unlocked"):
8514 ignored_range = self.ignored_errors["formula_unlocked"]
8515 self._write_ignored_error("unlockedFormula", ignored_range)
8517 if self.ignored_errors.get("empty_cell_reference"):
8518 ignored_range = self.ignored_errors["empty_cell_reference"]
8519 self._write_ignored_error("emptyCellReference", ignored_range)
8521 if self.ignored_errors.get("list_data_validation"):
8522 ignored_range = self.ignored_errors["list_data_validation"]
8523 self._write_ignored_error("listDataValidation", ignored_range)
8525 if self.ignored_errors.get("calculated_column"):
8526 ignored_range = self.ignored_errors["calculated_column"]
8527 self._write_ignored_error("calculatedColumn", ignored_range)
8529 if self.ignored_errors.get("two_digit_text_year"):
8530 ignored_range = self.ignored_errors["two_digit_text_year"]
8531 self._write_ignored_error("twoDigitTextYear", ignored_range)
8533 self._xml_end_tag("ignoredErrors")
8535 def _write_ignored_error(self, error_type, ignored_range) -> None:
8536 # Write the <ignoredError> element.
8537 attributes = [
8538 ("sqref", ignored_range),
8539 (error_type, 1),
8540 ]
8542 self._xml_empty_tag("ignoredError", attributes)