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-2025, 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 decimal import Decimal
20from fractions import Fraction
21from functools import wraps
22from io import BytesIO, StringIO
23from math import isinf, isnan
24from typing import (
25 TYPE_CHECKING,
26 Any,
27 Callable,
28 Dict,
29 List,
30 Literal,
31 Optional,
32 TypeVar,
33 Union,
34)
35from warnings import warn
37from xlsxwriter.chart import Chart
38from xlsxwriter.color import Color
39from xlsxwriter.comments import CommentType
40from xlsxwriter.image import Image
41from xlsxwriter.url import Url, UrlTypes
42from xlsxwriter.vml import ButtonType
44# Package imports.
45from . import xmlwriter
46from .drawing import Drawing, DrawingInfo, DrawingTypes
47from .exceptions import DuplicateTableName, OverlappingRange
48from .format import Format
49from .shape import Shape
50from .utility import (
51 _datetime_to_excel_datetime,
52 _get_sparkline_style,
53 _preserve_whitespace,
54 _supported_datetime,
55 quote_sheetname,
56 xl_cell_to_rowcol,
57 xl_col_to_name,
58 xl_pixel_width,
59 xl_range,
60 xl_rowcol_to_cell,
61 xl_rowcol_to_cell_fast,
62)
63from .xmlwriter import XMLwriter
65if TYPE_CHECKING:
66 from typing_extensions import Concatenate, ParamSpec, Protocol, overload
68 ReturnTypeT_co = TypeVar("ReturnTypeT_co", covariant=True)
69 P = ParamSpec("P")
71 class CellMethod(Protocol[P, ReturnTypeT_co]):
72 """Overloads to support cell notation."""
74 @overload
75 def __call__(
76 self, row: int, col: int, /, *args: P.args, **kwargs: P.kwargs
77 ) -> ReturnTypeT_co: ...
79 @overload
80 def __call__(
81 self, cell: str, /, *args: P.args, **kwargs: P.kwargs
82 ) -> ReturnTypeT_co: ...
84 class RangeMethod(Protocol[P, ReturnTypeT_co]):
85 """Overloads to support range notation."""
87 @overload
88 def __call__(
89 self,
90 first_row: int,
91 first_col: int,
92 last_row: int,
93 last_col: int,
94 /,
95 *args: P.args,
96 **kwargs: P.kwargs,
97 ) -> ReturnTypeT_co: ...
99 @overload
100 def __call__(
101 self, cell_range: str, /, *args: P.args, **kwargs: P.kwargs
102 ) -> ReturnTypeT_co: ...
104 class ColumnMethod(Protocol[P, ReturnTypeT_co]):
105 """Overloads to support column range notation."""
107 @overload
108 def __call__(
109 self, first_col: int, last_col: int, /, *args: P.args, **kwargs: P.kwargs
110 ) -> ReturnTypeT_co: ...
112 @overload
113 def __call__(
114 self, col_range: str, /, *args: P.args, **kwargs: P.kwargs
115 ) -> ReturnTypeT_co: ...
118re_dynamic_function = re.compile(
119 r"""
120 \bANCHORARRAY\( |
121 \bBYCOL\( |
122 \bBYROW\( |
123 \bCHOOSECOLS\( |
124 \bCHOOSEROWS\( |
125 \bDROP\( |
126 \bEXPAND\( |
127 \bFILTER\( |
128 \bHSTACK\( |
129 \bLAMBDA\( |
130 \bMAKEARRAY\( |
131 \bMAP\( |
132 \bRANDARRAY\( |
133 \bREDUCE\( |
134 \bSCAN\( |
135 \bSEQUENCE\( |
136 \bSINGLE\( |
137 \bSORT\( |
138 \bSORTBY\( |
139 \bSWITCH\( |
140 \bTAKE\( |
141 \bTEXTSPLIT\( |
142 \bTOCOL\( |
143 \bTOROW\( |
144 \bUNIQUE\( |
145 \bVSTACK\( |
146 \bWRAPCOLS\( |
147 \bWRAPROWS\( |
148 \bXLOOKUP\(""",
149 re.VERBOSE,
150)
153###############################################################################
154#
155# Decorator functions.
156#
157###############################################################################
158def convert_cell_args(
159 method: "Callable[Concatenate[Any, int, int, P], ReturnTypeT_co]",
160) -> "CellMethod[P, ReturnTypeT_co]":
161 """
162 Decorator function to convert A1 notation in cell method calls
163 to the default row/col notation.
165 """
167 @wraps(method)
168 def cell_wrapper(self, *args, **kwargs):
169 try:
170 # First arg is an int, default to row/col notation.
171 if args:
172 first_arg = args[0]
173 int(first_arg)
174 except ValueError:
175 # First arg isn't an int, convert to A1 notation.
176 new_args = xl_cell_to_rowcol(first_arg)
177 args = new_args + args[1:]
179 return method(self, *args, **kwargs)
181 return cell_wrapper
184def convert_range_args(
185 method: "Callable[Concatenate[Any, int, int, int, int, P], ReturnTypeT_co]",
186) -> "RangeMethod[P, ReturnTypeT_co]":
187 """
188 Decorator function to convert A1 notation in range method calls
189 to the default row/col notation.
191 """
193 @wraps(method)
194 def cell_wrapper(self, *args, **kwargs):
195 try:
196 # First arg is an int, default to row/col notation.
197 if args:
198 int(args[0])
199 except ValueError:
200 # First arg isn't an int, convert to A1 notation.
201 if ":" in args[0]:
202 cell_1, cell_2 = args[0].split(":")
203 row_1, col_1 = xl_cell_to_rowcol(cell_1)
204 row_2, col_2 = xl_cell_to_rowcol(cell_2)
205 else:
206 row_1, col_1 = xl_cell_to_rowcol(args[0])
207 row_2, col_2 = row_1, col_1
209 new_args = [row_1, col_1, row_2, col_2]
210 new_args.extend(args[1:])
211 args = new_args
213 return method(self, *args, **kwargs)
215 return cell_wrapper
218def convert_column_args(
219 method: "Callable[Concatenate[Any, int, int, P], ReturnTypeT_co]",
220) -> "ColumnMethod[P, ReturnTypeT_co]":
221 """
222 Decorator function to convert A1 notation in columns method calls
223 to the default row/col notation.
225 """
227 @wraps(method)
228 def column_wrapper(self, *args, **kwargs):
229 try:
230 # First arg is an int, default to row/col notation.
231 if args:
232 int(args[0])
233 except ValueError:
234 # First arg isn't an int, convert to A1 notation.
235 cell_1, cell_2 = [col + "1" for col in args[0].split(":")]
236 _, col_1 = xl_cell_to_rowcol(cell_1)
237 _, col_2 = xl_cell_to_rowcol(cell_2)
238 new_args = [col_1, col_2]
239 new_args.extend(args[1:])
240 args = new_args
242 return method(self, *args, **kwargs)
244 return column_wrapper
247###############################################################################
248#
249# Named tuples used for cell types.
250#
251###############################################################################
252CellBlankTuple = namedtuple("Blank", "format")
253CellErrorTuple = namedtuple("Error", "error, format, value")
254CellNumberTuple = namedtuple("Number", "number, format")
255CellStringTuple = namedtuple("String", "string, format")
256CellBooleanTuple = namedtuple("Boolean", "boolean, format")
257CellFormulaTuple = namedtuple("Formula", "formula, format, value")
258CellDatetimeTuple = namedtuple("Datetime", "number, format")
259CellRichStringTuple = namedtuple("RichString", "string, format, raw_string")
260CellArrayFormulaTuple = namedtuple(
261 "ArrayFormula", "formula, format, value, range, atype"
262)
265###############################################################################
266#
267# Worksheet Class definition.
268#
269###############################################################################
270class Worksheet(xmlwriter.XMLwriter):
271 """
272 A class for writing the Excel XLSX Worksheet file.
274 """
276 ###########################################################################
277 #
278 # Public API.
279 #
280 ###########################################################################
282 def __init__(self) -> None:
283 """
284 Constructor.
286 """
288 super().__init__()
290 self.name = None
291 self.index = None
292 self.str_table = None
293 self.palette = None
294 self.constant_memory = 0
295 self.tmpdir = None
296 self.is_chartsheet = False
298 self.ext_sheets = []
299 self.fileclosed = 0
300 self.excel_version = 2007
301 self.excel2003_style = False
303 self.xls_rowmax = 1048576
304 self.xls_colmax = 16384
305 self.xls_strmax = 32767
306 self.dim_rowmin = None
307 self.dim_rowmax = None
308 self.dim_colmin = None
309 self.dim_colmax = None
311 self.col_info = {}
312 self.selections = []
313 self.hidden = 0
314 self.active = 0
315 self.tab_color = 0
316 self.top_left_cell = ""
318 self.panes = []
319 self.active_pane = 3
320 self.selected = 0
322 self.page_setup_changed = False
323 self.paper_size = 0
324 self.orientation = 1
326 self.print_options_changed = False
327 self.hcenter = False
328 self.vcenter = False
329 self.print_gridlines = False
330 self.screen_gridlines = True
331 self.print_headers = False
332 self.row_col_headers = False
334 self.header_footer_changed = False
335 self.header = ""
336 self.footer = ""
337 self.header_footer_aligns = True
338 self.header_footer_scales = True
339 self.header_images = []
340 self.footer_images = []
341 self.header_images_list = []
343 self.margin_left = 0.7
344 self.margin_right = 0.7
345 self.margin_top = 0.75
346 self.margin_bottom = 0.75
347 self.margin_header = 0.3
348 self.margin_footer = 0.3
350 self.repeat_row_range = ""
351 self.repeat_col_range = ""
352 self.print_area_range = ""
354 self.page_order = 0
355 self.black_white = 0
356 self.draft_quality = 0
357 self.print_comments = 0
358 self.page_start = 0
360 self.fit_page = 0
361 self.fit_width = 0
362 self.fit_height = 0
364 self.hbreaks = []
365 self.vbreaks = []
367 self.protect_options = {}
368 self.protected_ranges = []
369 self.num_protected_ranges = 0
370 self.set_cols = {}
371 self.set_rows = defaultdict(dict)
373 self.zoom = 100
374 self.zoom_scale_normal = True
375 self.zoom_to_fit = False
376 self.print_scale = 100
377 self.is_right_to_left = False
378 self.show_zeros = 1
379 self.leading_zeros = 0
381 self.outline_row_level = 0
382 self.outline_col_level = 0
383 self.outline_style = 0
384 self.outline_below = 1
385 self.outline_right = 1
386 self.outline_on = 1
387 self.outline_changed = False
389 self.original_row_height = 15
390 self.default_row_height = 15
391 self.default_row_pixels = 20
392 self.default_col_width = 8.43
393 self.default_col_pixels = 64
394 self.default_date_pixels = 68
395 self.default_row_zeroed = 0
397 self.names = {}
398 self.write_match = []
399 self.table = defaultdict(dict)
400 self.merge = []
401 self.merged_cells = {}
402 self.table_cells = {}
403 self.row_spans = {}
405 self.has_vml = False
406 self.has_header_vml = False
407 self.has_comments = False
408 self.comments = defaultdict(dict)
409 self.comments_list = []
410 self.comments_author = ""
411 self.comments_visible = False
412 self.vml_shape_id = 1024
413 self.buttons_list = []
414 self.vml_header_id = 0
416 self.autofilter_area = ""
417 self.autofilter_ref = None
418 self.filter_range = [0, 9]
419 self.filter_on = 0
420 self.filter_cols = {}
421 self.filter_type = {}
422 self.filter_cells = {}
424 self.row_sizes = {}
425 self.col_size_changed = False
426 self.row_size_changed = False
428 self.last_shape_id = 1
429 self.rel_count = 0
430 self.hlink_count = 0
431 self.hlink_refs = []
432 self.external_hyper_links = []
433 self.external_drawing_links = []
434 self.external_comment_links = []
435 self.external_vml_links = []
436 self.external_table_links = []
437 self.external_background_links = []
438 self.drawing_links = []
439 self.vml_drawing_links = []
440 self.charts = []
441 self.images = []
442 self.tables = []
443 self.sparklines = []
444 self.shapes = []
445 self.shape_hash = {}
446 self.drawing = 0
447 self.drawing_rels = {}
448 self.drawing_rels_id = 0
449 self.vml_drawing_rels = {}
450 self.vml_drawing_rels_id = 0
451 self.background_image = None
453 self.rstring = ""
454 self.previous_row = 0
456 self.validations = []
457 self.cond_formats = {}
458 self.data_bars_2010 = []
459 self.use_data_bars_2010 = False
460 self.dxf_priority = 1
461 self.page_view = 0
463 self.vba_codename = None
465 self.date_1904 = False
466 self.hyperlinks = defaultdict(dict)
468 self.strings_to_numbers = False
469 self.strings_to_urls = True
470 self.nan_inf_to_errors = False
471 self.strings_to_formulas = True
473 self.default_date_format = None
474 self.default_url_format = None
475 self.default_checkbox_format = None
476 self.workbook_add_format = None
477 self.remove_timezone = False
478 self.max_url_length = 2079
480 self.row_data_filename = None
481 self.row_data_fh = None
482 self.worksheet_meta = None
483 self.vml_data_id = None
484 self.vml_shape_id = None
486 self.row_data_filename = None
487 self.row_data_fh = None
488 self.row_data_fh_closed = False
490 self.vertical_dpi = 0
491 self.horizontal_dpi = 0
493 self.write_handlers = {}
495 self.ignored_errors = None
497 self.has_dynamic_arrays = False
498 self.use_future_functions = False
499 self.ignore_write_string = False
500 self.embedded_images = None
502 # Utility function for writing different types of strings.
503 def _write_token_as_string(self, token, row: int, col: int, *args):
504 # Map the data to the appropriate write_*() method.
505 if token == "":
506 return self._write_blank(row, col, *args)
508 if self.strings_to_formulas and token.startswith("="):
509 return self._write_formula(row, col, *args)
511 if token.startswith("{=") and token.endswith("}"):
512 return self._write_formula(row, col, *args)
514 # pylint: disable=too-many-boolean-expressions
515 if (
516 ":" in token
517 and self.strings_to_urls
518 and (
519 re.match("(ftp|http)s?://", token)
520 or re.match("mailto:", token)
521 or re.match("(in|ex)ternal:", token)
522 or re.match("file://", token)
523 )
524 ):
525 return self._write_url(row, col, *args)
527 if self.strings_to_numbers:
528 try:
529 f = float(token)
530 if self.nan_inf_to_errors or (not isnan(f) and not isinf(f)):
531 return self._write_number(row, col, f, *args[1:])
532 except ValueError:
533 # Not a number, write as a string.
534 pass
536 return self._write_string(row, col, *args)
538 # We have a plain string.
539 return self._write_string(row, col, *args)
541 @convert_cell_args
542 def write(self, row: int, col: int, *args) -> Union[Literal[0, -1], Any]:
543 """
544 Write data to a worksheet cell by calling the appropriate write_*()
545 method based on the type of data being passed.
547 Args:
548 row: The cell row (zero indexed).
549 col: The cell column (zero indexed).
550 *args: Args to pass to sub functions.
552 Returns:
553 0: Success.
554 -1: Row or column is out of worksheet bounds.
555 other: Return value of called method.
557 """
558 return self._write(row, col, *args)
560 # Undecorated version of write().
561 def _write(self, row: int, col: int, *args):
562 # pylint: disable=raise-missing-from
563 # Check the number of args passed.
564 if not args:
565 raise TypeError("write() takes at least 4 arguments (3 given)")
567 # The first arg should be the token for all write calls.
568 token = args[0]
570 # Avoid isinstance() for better performance.
571 token_type = token.__class__
573 # Check for any user defined type handlers with callback functions.
574 if token_type in self.write_handlers:
575 write_handler = self.write_handlers[token_type]
576 function_return = write_handler(self, row, col, *args)
578 # If the return value is None then the callback has returned
579 # control to this function and we should continue as
580 # normal. Otherwise we return the value to the caller and exit.
581 if function_return is None:
582 pass
583 else:
584 return function_return
586 # Write None as a blank cell.
587 if token is None:
588 return self._write_blank(row, col, *args)
590 # Check for standard Python types.
591 if token_type is bool:
592 return self._write_boolean(row, col, *args)
594 if token_type in (float, int, Decimal, Fraction):
595 return self._write_number(row, col, *args)
597 if token_type is str:
598 return self._write_token_as_string(token, row, col, *args)
600 if token_type in (
601 datetime.datetime,
602 datetime.date,
603 datetime.time,
604 datetime.timedelta,
605 ):
606 return self._write_datetime(row, col, *args)
608 # Resort to isinstance() for subclassed primitives.
610 # Write number types.
611 if isinstance(token, (float, int, Decimal, Fraction)):
612 return self._write_number(row, col, *args)
614 # Write string types.
615 if isinstance(token, str):
616 return self._write_token_as_string(token, row, col, *args)
618 # Write boolean types.
619 if isinstance(token, bool):
620 return self._write_boolean(row, col, *args)
622 # Write datetime objects.
623 if _supported_datetime(token):
624 return self._write_datetime(row, col, *args)
626 # Write Url type.
627 if isinstance(token, Url):
628 return self._write_url(row, col, *args)
630 # We haven't matched a supported type. Try float.
631 try:
632 f = float(token)
633 return self._write_number(row, col, f, *args[1:])
634 except ValueError:
635 pass
636 except TypeError:
637 raise TypeError(f"Unsupported type {type(token)} in write()")
639 # Finally try string.
640 try:
641 str(token)
642 return self._write_string(row, col, *args)
643 except ValueError:
644 raise TypeError(f"Unsupported type {type(token)} in write()")
646 @convert_cell_args
647 def write_string(
648 self, row: int, col: int, string: str, cell_format: Optional[Format] = None
649 ) -> Literal[0, -1, -2]:
650 """
651 Write a string to a worksheet cell.
653 Args:
654 row: The cell row (zero indexed).
655 col: The cell column (zero indexed).
656 string: Cell data. Str.
657 format: An optional cell Format object.
659 Returns:
660 0: Success.
661 -1: Row or column is out of worksheet bounds.
662 -2: String truncated to 32k characters.
664 """
665 return self._write_string(row, col, string, cell_format)
667 # Undecorated version of write_string().
668 def _write_string(
669 self, row: int, col: int, string: str, cell_format: Optional[Format] = None
670 ) -> Literal[0, -1, -2]:
671 str_error = 0
673 # Check that row and col are valid and store max and min values.
674 if self._check_dimensions(row, col):
675 return -1
677 # Check that the string is < 32767 chars.
678 if len(string) > self.xls_strmax:
679 string = string[: self.xls_strmax]
680 str_error = -2
682 # Write a shared string or an in-line string in constant_memory mode.
683 if not self.constant_memory:
684 string_index = self.str_table._get_shared_string_index(string)
685 else:
686 string_index = string
688 # Write previous row if in in-line string constant_memory mode.
689 if self.constant_memory and row > self.previous_row:
690 self._write_single_row(row)
692 # Store the cell data in the worksheet data table.
693 self.table[row][col] = CellStringTuple(string_index, cell_format)
695 return str_error
697 @convert_cell_args
698 def write_number(
699 self,
700 row: int,
701 col: int,
702 number: Union[int, float, Fraction],
703 cell_format: Optional[Format] = None,
704 ) -> Literal[0, -1]:
705 """
706 Write a number to a worksheet cell.
708 Args:
709 row: The cell row (zero indexed).
710 col: The cell column (zero indexed).
711 number: Cell data. Int or float.
712 cell_format: An optional cell Format object.
714 Returns:
715 0: Success.
716 -1: Row or column is out of worksheet bounds.
718 """
719 return self._write_number(row, col, number, cell_format)
721 # Undecorated version of write_number().
722 def _write_number(
723 self,
724 row: int,
725 col: int,
726 number: Union[int, float, Fraction],
727 cell_format: Optional[Format] = None,
728 ) -> Literal[0, -1]:
729 if isnan(number) or isinf(number):
730 if self.nan_inf_to_errors:
731 if isnan(number):
732 return self._write_formula(row, col, "#NUM!", cell_format, "#NUM!")
734 if number == math.inf:
735 return self._write_formula(row, col, "1/0", cell_format, "#DIV/0!")
737 if number == -math.inf:
738 return self._write_formula(row, col, "-1/0", cell_format, "#DIV/0!")
739 else:
740 raise TypeError(
741 "NAN/INF not supported in write_number() "
742 "without 'nan_inf_to_errors' Workbook() option"
743 )
745 if number.__class__ is Fraction:
746 number = float(number)
748 # Check that row and col are valid and store max and min values.
749 if self._check_dimensions(row, col):
750 return -1
752 # Write previous row if in in-line string constant_memory mode.
753 if self.constant_memory and row > self.previous_row:
754 self._write_single_row(row)
756 # Store the cell data in the worksheet data table.
757 self.table[row][col] = CellNumberTuple(number, cell_format)
759 return 0
761 @convert_cell_args
762 def write_blank(
763 self, row: int, col: int, blank: Any, cell_format: Optional[Format] = None
764 ):
765 """
766 Write a blank cell with formatting to a worksheet cell. The blank
767 token is ignored and the format only is written to the cell.
769 Args:
770 row: The cell row (zero indexed).
771 col: The cell column (zero indexed).
772 blank: Any value. It is ignored.
773 cell_format: An optional cell Format object.
775 Returns:
776 0: Success.
777 -1: Row or column is out of worksheet bounds.
779 """
780 return self._write_blank(row, col, blank, cell_format)
782 # Undecorated version of write_blank().
783 def _write_blank(
784 self, row: int, col: int, _, cell_format: Optional[Format] = None
785 ) -> Literal[0, -1]:
786 # Don't write a blank cell unless it has a format.
787 if cell_format is None:
788 return 0
790 # Check that row and col are valid and store max and min values.
791 if self._check_dimensions(row, col):
792 return -1
794 # Write previous row if in in-line string constant_memory mode.
795 if self.constant_memory and row > self.previous_row:
796 self._write_single_row(row)
798 # Store the cell data in the worksheet data table.
799 self.table[row][col] = CellBlankTuple(cell_format)
801 return 0
803 @convert_cell_args
804 def write_formula(
805 self,
806 row: int,
807 col: int,
808 formula: str,
809 cell_format: Optional[Format] = None,
810 value=0,
811 ) -> Literal[0, -1, -2]:
812 """
813 Write a formula to a worksheet cell.
815 Args:
816 row: The cell row (zero indexed).
817 col: The cell column (zero indexed).
818 formula: Cell formula.
819 cell_format: An optional cell Format object.
820 value: An optional value for the formula. Default is 0.
822 Returns:
823 0: Success.
824 -1: Row or column is out of worksheet bounds.
825 -2: Formula can't be None or empty.
827 """
828 # Check that row and col are valid and store max and min values.
829 return self._write_formula(row, col, formula, cell_format, value)
831 # Undecorated version of write_formula().
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 if self._check_dimensions(row, col):
841 return -1
843 if formula is None or formula == "":
844 warn("Formula can't be None or empty")
845 return -1
847 # Check for dynamic array functions.
848 if re_dynamic_function.search(formula):
849 return self.write_dynamic_array_formula(
850 row, col, row, col, formula, cell_format, value
851 )
853 # Hand off array formulas.
854 if formula.startswith("{") and formula.endswith("}"):
855 return self._write_array_formula(
856 row, col, row, col, formula, cell_format, value
857 )
859 # Modify the formula string, as needed.
860 formula = self._prepare_formula(formula)
862 # Write previous row if in in-line string constant_memory mode.
863 if self.constant_memory and row > self.previous_row:
864 self._write_single_row(row)
866 # Store the cell data in the worksheet data table.
867 self.table[row][col] = CellFormulaTuple(formula, cell_format, value)
869 return 0
871 @convert_range_args
872 def write_array_formula(
873 self,
874 first_row: int,
875 first_col: int,
876 last_row: int,
877 last_col: int,
878 formula: str,
879 cell_format: Optional[Format] = None,
880 value=0,
881 ) -> Literal[0, -1]:
882 """
883 Write a formula to a worksheet cell/range.
885 Args:
886 first_row: The first row of the cell range. (zero indexed).
887 first_col: The first column of the cell range.
888 last_row: The last row of the cell range. (zero indexed).
889 last_col: The last column of the cell range.
890 formula: Cell formula.
891 cell_format: An optional cell Format object.
892 value: An optional value for the formula. Default is 0.
894 Returns:
895 0: Success.
896 -1: Row or column is out of worksheet bounds.
898 """
899 # Check for dynamic array functions.
900 if re_dynamic_function.search(formula):
901 return self.write_dynamic_array_formula(
902 first_row, first_col, last_row, last_col, formula, cell_format, value
903 )
905 return self._write_array_formula(
906 first_row,
907 first_col,
908 last_row,
909 last_col,
910 formula,
911 cell_format,
912 value,
913 "static",
914 )
916 @convert_range_args
917 def write_dynamic_array_formula(
918 self,
919 first_row: int,
920 first_col: int,
921 last_row: int,
922 last_col: int,
923 formula: str,
924 cell_format: Optional[Format] = None,
925 value=0,
926 ) -> Literal[0, -1]:
927 """
928 Write a dynamic array formula to a worksheet cell/range.
930 Args:
931 first_row: The first row of the cell range. (zero indexed).
932 first_col: The first column of the cell range.
933 last_row: The last row of the cell range. (zero indexed).
934 last_col: The last column of the cell range.
935 formula: Cell formula.
936 cell_format: An optional cell Format object.
937 value: An optional value for the formula. Default is 0.
939 Returns:
940 0: Success.
941 -1: Row or column is out of worksheet bounds.
943 """
944 error = self._write_array_formula(
945 first_row,
946 first_col,
947 last_row,
948 last_col,
949 formula,
950 cell_format,
951 value,
952 "dynamic",
953 )
955 if error == 0:
956 self.has_dynamic_arrays = True
958 return error
960 # Utility method to strip equal sign and array braces from a formula and
961 # also expand out future and dynamic array formulas.
962 def _prepare_formula(self, formula, expand_future_functions=False):
963 # Remove array formula braces and the leading =.
964 if formula.startswith("{"):
965 formula = formula[1:]
966 if formula.startswith("="):
967 formula = formula[1:]
968 if formula.endswith("}"):
969 formula = formula[:-1]
971 # Check if formula is already expanded by the user.
972 if "_xlfn." in formula:
973 return formula
975 # Expand dynamic formulas.
976 formula = re.sub(r"\bANCHORARRAY\(", "_xlfn.ANCHORARRAY(", formula)
977 formula = re.sub(r"\bBYCOL\(", "_xlfn.BYCOL(", formula)
978 formula = re.sub(r"\bBYROW\(", "_xlfn.BYROW(", formula)
979 formula = re.sub(r"\bCHOOSECOLS\(", "_xlfn.CHOOSECOLS(", formula)
980 formula = re.sub(r"\bCHOOSEROWS\(", "_xlfn.CHOOSEROWS(", formula)
981 formula = re.sub(r"\bDROP\(", "_xlfn.DROP(", formula)
982 formula = re.sub(r"\bEXPAND\(", "_xlfn.EXPAND(", formula)
983 formula = re.sub(r"\bFILTER\(", "_xlfn._xlws.FILTER(", formula)
984 formula = re.sub(r"\bHSTACK\(", "_xlfn.HSTACK(", formula)
985 formula = re.sub(r"\bLAMBDA\(", "_xlfn.LAMBDA(", formula)
986 formula = re.sub(r"\bMAKEARRAY\(", "_xlfn.MAKEARRAY(", formula)
987 formula = re.sub(r"\bMAP\(", "_xlfn.MAP(", formula)
988 formula = re.sub(r"\bRANDARRAY\(", "_xlfn.RANDARRAY(", formula)
989 formula = re.sub(r"\bREDUCE\(", "_xlfn.REDUCE(", formula)
990 formula = re.sub(r"\bSCAN\(", "_xlfn.SCAN(", formula)
991 formula = re.sub(r"\SINGLE\(", "_xlfn.SINGLE(", formula)
992 formula = re.sub(r"\bSEQUENCE\(", "_xlfn.SEQUENCE(", formula)
993 formula = re.sub(r"\bSORT\(", "_xlfn._xlws.SORT(", formula)
994 formula = re.sub(r"\bSORTBY\(", "_xlfn.SORTBY(", formula)
995 formula = re.sub(r"\bSWITCH\(", "_xlfn.SWITCH(", formula)
996 formula = re.sub(r"\bTAKE\(", "_xlfn.TAKE(", formula)
997 formula = re.sub(r"\bTEXTSPLIT\(", "_xlfn.TEXTSPLIT(", formula)
998 formula = re.sub(r"\bTOCOL\(", "_xlfn.TOCOL(", formula)
999 formula = re.sub(r"\bTOROW\(", "_xlfn.TOROW(", formula)
1000 formula = re.sub(r"\bUNIQUE\(", "_xlfn.UNIQUE(", formula)
1001 formula = re.sub(r"\bVSTACK\(", "_xlfn.VSTACK(", formula)
1002 formula = re.sub(r"\bWRAPCOLS\(", "_xlfn.WRAPCOLS(", formula)
1003 formula = re.sub(r"\bWRAPROWS\(", "_xlfn.WRAPROWS(", formula)
1004 formula = re.sub(r"\bXLOOKUP\(", "_xlfn.XLOOKUP(", formula)
1006 if not self.use_future_functions and not expand_future_functions:
1007 return formula
1009 formula = re.sub(r"\bACOTH\(", "_xlfn.ACOTH(", formula)
1010 formula = re.sub(r"\bACOT\(", "_xlfn.ACOT(", formula)
1011 formula = re.sub(r"\bAGGREGATE\(", "_xlfn.AGGREGATE(", formula)
1012 formula = re.sub(r"\bARABIC\(", "_xlfn.ARABIC(", formula)
1013 formula = re.sub(r"\bARRAYTOTEXT\(", "_xlfn.ARRAYTOTEXT(", formula)
1014 formula = re.sub(r"\bBASE\(", "_xlfn.BASE(", formula)
1015 formula = re.sub(r"\bBETA.DIST\(", "_xlfn.BETA.DIST(", formula)
1016 formula = re.sub(r"\bBETA.INV\(", "_xlfn.BETA.INV(", formula)
1017 formula = re.sub(r"\bBINOM.DIST.RANGE\(", "_xlfn.BINOM.DIST.RANGE(", formula)
1018 formula = re.sub(r"\bBINOM.DIST\(", "_xlfn.BINOM.DIST(", formula)
1019 formula = re.sub(r"\bBINOM.INV\(", "_xlfn.BINOM.INV(", formula)
1020 formula = re.sub(r"\bBITAND\(", "_xlfn.BITAND(", formula)
1021 formula = re.sub(r"\bBITLSHIFT\(", "_xlfn.BITLSHIFT(", formula)
1022 formula = re.sub(r"\bBITOR\(", "_xlfn.BITOR(", formula)
1023 formula = re.sub(r"\bBITRSHIFT\(", "_xlfn.BITRSHIFT(", formula)
1024 formula = re.sub(r"\bBITXOR\(", "_xlfn.BITXOR(", formula)
1025 formula = re.sub(r"\bCEILING.MATH\(", "_xlfn.CEILING.MATH(", formula)
1026 formula = re.sub(r"\bCEILING.PRECISE\(", "_xlfn.CEILING.PRECISE(", formula)
1027 formula = re.sub(r"\bCHISQ.DIST.RT\(", "_xlfn.CHISQ.DIST.RT(", formula)
1028 formula = re.sub(r"\bCHISQ.DIST\(", "_xlfn.CHISQ.DIST(", formula)
1029 formula = re.sub(r"\bCHISQ.INV.RT\(", "_xlfn.CHISQ.INV.RT(", formula)
1030 formula = re.sub(r"\bCHISQ.INV\(", "_xlfn.CHISQ.INV(", formula)
1031 formula = re.sub(r"\bCHISQ.TEST\(", "_xlfn.CHISQ.TEST(", formula)
1032 formula = re.sub(r"\bCOMBINA\(", "_xlfn.COMBINA(", formula)
1033 formula = re.sub(r"\bCONCAT\(", "_xlfn.CONCAT(", formula)
1034 formula = re.sub(r"\bCONFIDENCE.NORM\(", "_xlfn.CONFIDENCE.NORM(", formula)
1035 formula = re.sub(r"\bCONFIDENCE.T\(", "_xlfn.CONFIDENCE.T(", formula)
1036 formula = re.sub(r"\bCOTH\(", "_xlfn.COTH(", formula)
1037 formula = re.sub(r"\bCOT\(", "_xlfn.COT(", formula)
1038 formula = re.sub(r"\bCOVARIANCE.P\(", "_xlfn.COVARIANCE.P(", formula)
1039 formula = re.sub(r"\bCOVARIANCE.S\(", "_xlfn.COVARIANCE.S(", formula)
1040 formula = re.sub(r"\bCSCH\(", "_xlfn.CSCH(", formula)
1041 formula = re.sub(r"\bCSC\(", "_xlfn.CSC(", formula)
1042 formula = re.sub(r"\bDAYS\(", "_xlfn.DAYS(", formula)
1043 formula = re.sub(r"\bDECIMAL\(", "_xlfn.DECIMAL(", formula)
1044 formula = re.sub(r"\bERF.PRECISE\(", "_xlfn.ERF.PRECISE(", formula)
1045 formula = re.sub(r"\bERFC.PRECISE\(", "_xlfn.ERFC.PRECISE(", formula)
1046 formula = re.sub(r"\bEXPON.DIST\(", "_xlfn.EXPON.DIST(", formula)
1047 formula = re.sub(r"\bF.DIST.RT\(", "_xlfn.F.DIST.RT(", formula)
1048 formula = re.sub(r"\bF.DIST\(", "_xlfn.F.DIST(", formula)
1049 formula = re.sub(r"\bF.INV.RT\(", "_xlfn.F.INV.RT(", formula)
1050 formula = re.sub(r"\bF.INV\(", "_xlfn.F.INV(", formula)
1051 formula = re.sub(r"\bF.TEST\(", "_xlfn.F.TEST(", formula)
1052 formula = re.sub(r"\bFILTERXML\(", "_xlfn.FILTERXML(", formula)
1053 formula = re.sub(r"\bFLOOR.MATH\(", "_xlfn.FLOOR.MATH(", formula)
1054 formula = re.sub(r"\bFLOOR.PRECISE\(", "_xlfn.FLOOR.PRECISE(", formula)
1055 formula = re.sub(
1056 r"\bFORECAST.ETS.CONFINT\(", "_xlfn.FORECAST.ETS.CONFINT(", formula
1057 )
1058 formula = re.sub(
1059 r"\bFORECAST.ETS.SEASONALITY\(", "_xlfn.FORECAST.ETS.SEASONALITY(", formula
1060 )
1061 formula = re.sub(r"\bFORECAST.ETS.STAT\(", "_xlfn.FORECAST.ETS.STAT(", formula)
1062 formula = re.sub(r"\bFORECAST.ETS\(", "_xlfn.FORECAST.ETS(", formula)
1063 formula = re.sub(r"\bFORECAST.LINEAR\(", "_xlfn.FORECAST.LINEAR(", formula)
1064 formula = re.sub(r"\bFORMULATEXT\(", "_xlfn.FORMULATEXT(", formula)
1065 formula = re.sub(r"\bGAMMA.DIST\(", "_xlfn.GAMMA.DIST(", formula)
1066 formula = re.sub(r"\bGAMMA.INV\(", "_xlfn.GAMMA.INV(", formula)
1067 formula = re.sub(r"\bGAMMALN.PRECISE\(", "_xlfn.GAMMALN.PRECISE(", formula)
1068 formula = re.sub(r"\bGAMMA\(", "_xlfn.GAMMA(", formula)
1069 formula = re.sub(r"\bGAUSS\(", "_xlfn.GAUSS(", formula)
1070 formula = re.sub(r"\bHYPGEOM.DIST\(", "_xlfn.HYPGEOM.DIST(", formula)
1071 formula = re.sub(r"\bIFNA\(", "_xlfn.IFNA(", formula)
1072 formula = re.sub(r"\bIFS\(", "_xlfn.IFS(", formula)
1073 formula = re.sub(r"\bIMAGE\(", "_xlfn.IMAGE(", formula)
1074 formula = re.sub(r"\bIMCOSH\(", "_xlfn.IMCOSH(", formula)
1075 formula = re.sub(r"\bIMCOT\(", "_xlfn.IMCOT(", formula)
1076 formula = re.sub(r"\bIMCSCH\(", "_xlfn.IMCSCH(", formula)
1077 formula = re.sub(r"\bIMCSC\(", "_xlfn.IMCSC(", formula)
1078 formula = re.sub(r"\bIMSECH\(", "_xlfn.IMSECH(", formula)
1079 formula = re.sub(r"\bIMSEC\(", "_xlfn.IMSEC(", formula)
1080 formula = re.sub(r"\bIMSINH\(", "_xlfn.IMSINH(", formula)
1081 formula = re.sub(r"\bIMTAN\(", "_xlfn.IMTAN(", formula)
1082 formula = re.sub(r"\bISFORMULA\(", "_xlfn.ISFORMULA(", formula)
1083 formula = re.sub(r"\bISOMITTED\(", "_xlfn.ISOMITTED(", formula)
1084 formula = re.sub(r"\bISOWEEKNUM\(", "_xlfn.ISOWEEKNUM(", formula)
1085 formula = re.sub(r"\bLET\(", "_xlfn.LET(", formula)
1086 formula = re.sub(r"\bLOGNORM.DIST\(", "_xlfn.LOGNORM.DIST(", formula)
1087 formula = re.sub(r"\bLOGNORM.INV\(", "_xlfn.LOGNORM.INV(", formula)
1088 formula = re.sub(r"\bMAXIFS\(", "_xlfn.MAXIFS(", formula)
1089 formula = re.sub(r"\bMINIFS\(", "_xlfn.MINIFS(", formula)
1090 formula = re.sub(r"\bMODE.MULT\(", "_xlfn.MODE.MULT(", formula)
1091 formula = re.sub(r"\bMODE.SNGL\(", "_xlfn.MODE.SNGL(", formula)
1092 formula = re.sub(r"\bMUNIT\(", "_xlfn.MUNIT(", formula)
1093 formula = re.sub(r"\bNEGBINOM.DIST\(", "_xlfn.NEGBINOM.DIST(", formula)
1094 formula = re.sub(r"\bNORM.DIST\(", "_xlfn.NORM.DIST(", formula)
1095 formula = re.sub(r"\bNORM.INV\(", "_xlfn.NORM.INV(", formula)
1096 formula = re.sub(r"\bNORM.S.DIST\(", "_xlfn.NORM.S.DIST(", formula)
1097 formula = re.sub(r"\bNORM.S.INV\(", "_xlfn.NORM.S.INV(", formula)
1098 formula = re.sub(r"\bNUMBERVALUE\(", "_xlfn.NUMBERVALUE(", formula)
1099 formula = re.sub(r"\bPDURATION\(", "_xlfn.PDURATION(", formula)
1100 formula = re.sub(r"\bPERCENTILE.EXC\(", "_xlfn.PERCENTILE.EXC(", formula)
1101 formula = re.sub(r"\bPERCENTILE.INC\(", "_xlfn.PERCENTILE.INC(", formula)
1102 formula = re.sub(r"\bPERCENTRANK.EXC\(", "_xlfn.PERCENTRANK.EXC(", formula)
1103 formula = re.sub(r"\bPERCENTRANK.INC\(", "_xlfn.PERCENTRANK.INC(", formula)
1104 formula = re.sub(r"\bPERMUTATIONA\(", "_xlfn.PERMUTATIONA(", formula)
1105 formula = re.sub(r"\bPHI\(", "_xlfn.PHI(", formula)
1106 formula = re.sub(r"\bPOISSON.DIST\(", "_xlfn.POISSON.DIST(", formula)
1107 formula = re.sub(r"\bQUARTILE.EXC\(", "_xlfn.QUARTILE.EXC(", formula)
1108 formula = re.sub(r"\bQUARTILE.INC\(", "_xlfn.QUARTILE.INC(", formula)
1109 formula = re.sub(r"\bQUERYSTRING\(", "_xlfn.QUERYSTRING(", formula)
1110 formula = re.sub(r"\bRANK.AVG\(", "_xlfn.RANK.AVG(", formula)
1111 formula = re.sub(r"\bRANK.EQ\(", "_xlfn.RANK.EQ(", formula)
1112 formula = re.sub(r"\bRRI\(", "_xlfn.RRI(", formula)
1113 formula = re.sub(r"\bSECH\(", "_xlfn.SECH(", formula)
1114 formula = re.sub(r"\bSEC\(", "_xlfn.SEC(", formula)
1115 formula = re.sub(r"\bSHEETS\(", "_xlfn.SHEETS(", formula)
1116 formula = re.sub(r"\bSHEET\(", "_xlfn.SHEET(", formula)
1117 formula = re.sub(r"\bSKEW.P\(", "_xlfn.SKEW.P(", formula)
1118 formula = re.sub(r"\bSTDEV.P\(", "_xlfn.STDEV.P(", formula)
1119 formula = re.sub(r"\bSTDEV.S\(", "_xlfn.STDEV.S(", formula)
1120 formula = re.sub(r"\bT.DIST.2T\(", "_xlfn.T.DIST.2T(", formula)
1121 formula = re.sub(r"\bT.DIST.RT\(", "_xlfn.T.DIST.RT(", formula)
1122 formula = re.sub(r"\bT.DIST\(", "_xlfn.T.DIST(", formula)
1123 formula = re.sub(r"\bT.INV.2T\(", "_xlfn.T.INV.2T(", formula)
1124 formula = re.sub(r"\bT.INV\(", "_xlfn.T.INV(", formula)
1125 formula = re.sub(r"\bT.TEST\(", "_xlfn.T.TEST(", formula)
1126 formula = re.sub(r"\bTEXTAFTER\(", "_xlfn.TEXTAFTER(", formula)
1127 formula = re.sub(r"\bTEXTBEFORE\(", "_xlfn.TEXTBEFORE(", formula)
1128 formula = re.sub(r"\bTEXTJOIN\(", "_xlfn.TEXTJOIN(", formula)
1129 formula = re.sub(r"\bUNICHAR\(", "_xlfn.UNICHAR(", formula)
1130 formula = re.sub(r"\bUNICODE\(", "_xlfn.UNICODE(", formula)
1131 formula = re.sub(r"\bVALUETOTEXT\(", "_xlfn.VALUETOTEXT(", formula)
1132 formula = re.sub(r"\bVAR.P\(", "_xlfn.VAR.P(", formula)
1133 formula = re.sub(r"\bVAR.S\(", "_xlfn.VAR.S(", formula)
1134 formula = re.sub(r"\bWEBSERVICE\(", "_xlfn.WEBSERVICE(", formula)
1135 formula = re.sub(r"\bWEIBULL.DIST\(", "_xlfn.WEIBULL.DIST(", formula)
1136 formula = re.sub(r"\bXMATCH\(", "_xlfn.XMATCH(", formula)
1137 formula = re.sub(r"\bXOR\(", "_xlfn.XOR(", formula)
1138 formula = re.sub(r"\bZ.TEST\(", "_xlfn.Z.TEST(", formula)
1140 return formula
1142 # Escape/expand table functions. This mainly involves converting Excel 2010
1143 # "@" table ref to 2007 "[#This Row],". We parse the string to avoid
1144 # replacements in string literals within the formula.
1145 @staticmethod
1146 def _prepare_table_formula(formula):
1147 if "@" not in formula:
1148 # No escaping required.
1149 return formula
1151 escaped_formula = []
1152 in_string_literal = False
1154 for char in formula:
1155 # Match the start/end of string literals to avoid escaping
1156 # references in strings.
1157 if char == '"':
1158 in_string_literal = not in_string_literal
1160 # Copy the string literal.
1161 if in_string_literal:
1162 escaped_formula.append(char)
1163 continue
1165 # Replace table reference.
1166 if char == "@":
1167 escaped_formula.append("[#This Row],")
1168 else:
1169 escaped_formula.append(char)
1171 return ("").join(escaped_formula)
1173 # Undecorated version of write_array_formula() and
1174 # write_dynamic_array_formula().
1175 def _write_array_formula(
1176 self,
1177 first_row,
1178 first_col,
1179 last_row,
1180 last_col,
1181 formula,
1182 cell_format=None,
1183 value=0,
1184 atype="static",
1185 ) -> Literal[0, -1]:
1186 # Swap last row/col with first row/col as necessary.
1187 if first_row > last_row:
1188 first_row, last_row = last_row, first_row
1189 if first_col > last_col:
1190 first_col, last_col = last_col, first_col
1192 # Check that row and col are valid and store max and min values.
1193 if self._check_dimensions(first_row, first_col):
1194 return -1
1195 if self._check_dimensions(last_row, last_col):
1196 return -1
1198 # Define array range
1199 if first_row == last_row and first_col == last_col:
1200 cell_range = xl_rowcol_to_cell(first_row, first_col)
1201 else:
1202 cell_range = (
1203 xl_rowcol_to_cell(first_row, first_col)
1204 + ":"
1205 + xl_rowcol_to_cell(last_row, last_col)
1206 )
1208 # Modify the formula string, as needed.
1209 formula = self._prepare_formula(formula)
1211 # Write previous row if in in-line string constant_memory mode.
1212 if self.constant_memory and first_row > self.previous_row:
1213 self._write_single_row(first_row)
1215 # Store the cell data in the worksheet data table.
1216 self.table[first_row][first_col] = CellArrayFormulaTuple(
1217 formula, cell_format, value, cell_range, atype
1218 )
1220 # Pad out the rest of the area with formatted zeroes.
1221 if not self.constant_memory:
1222 for row in range(first_row, last_row + 1):
1223 for col in range(first_col, last_col + 1):
1224 if row != first_row or col != first_col:
1225 self._write_number(row, col, 0, cell_format)
1227 return 0
1229 @convert_cell_args
1230 def write_datetime(
1231 self,
1232 row: int,
1233 col: int,
1234 date: datetime.datetime,
1235 cell_format: Optional[Format] = None,
1236 ) -> Literal[0, -1]:
1237 """
1238 Write a date or time to a worksheet cell.
1240 Args:
1241 row: The cell row (zero indexed).
1242 col: The cell column (zero indexed).
1243 date: Date and/or time as a datetime object.
1244 cell_format: A cell Format object.
1246 Returns:
1247 0: Success.
1248 -1: Row or column is out of worksheet bounds.
1250 """
1251 return self._write_datetime(row, col, date, cell_format)
1253 # Undecorated version of write_datetime().
1254 def _write_datetime(self, row: int, col: int, date, cell_format=None) -> int:
1255 # Check that row and col are valid and store max and min values.
1256 if self._check_dimensions(row, col):
1257 return -1
1259 # Write previous row if in in-line string constant_memory mode.
1260 if self.constant_memory and row > self.previous_row:
1261 self._write_single_row(row)
1263 # Convert datetime to an Excel date.
1264 number = self._convert_date_time(date)
1266 # Add the default date format.
1267 if cell_format is None:
1268 cell_format = self.default_date_format
1270 # Store the cell data in the worksheet data table.
1271 self.table[row][col] = CellDatetimeTuple(number, cell_format)
1273 return 0
1275 @convert_cell_args
1276 def write_boolean(
1277 self, row: int, col: int, boolean: bool, cell_format: Optional[Format] = None
1278 ):
1279 """
1280 Write a boolean value to a worksheet cell.
1282 Args:
1283 row: The cell row (zero indexed).
1284 col: The cell column (zero indexed).
1285 boolean: Cell data. bool type.
1286 cell_format: An optional cell Format object.
1288 Returns:
1289 0: Success.
1290 -1: Row or column is out of worksheet bounds.
1292 """
1293 return self._write_boolean(row, col, boolean, cell_format)
1295 # Undecorated version of write_boolean().
1296 def _write_boolean(self, row: int, col: int, boolean, cell_format=None) -> int:
1297 # Check that row and col are valid and store max and min values.
1298 if self._check_dimensions(row, col):
1299 return -1
1301 # Write previous row if in in-line string constant_memory mode.
1302 if self.constant_memory and row > self.previous_row:
1303 self._write_single_row(row)
1305 if boolean:
1306 value = 1
1307 else:
1308 value = 0
1310 # Store the cell data in the worksheet data table.
1311 self.table[row][col] = CellBooleanTuple(value, cell_format)
1313 return 0
1315 # Write a hyperlink. This is comprised of two elements: the displayed
1316 # string and the non-displayed link. The displayed string is the same as
1317 # the link unless an alternative string is specified. The display string
1318 # is written using the write_string() method. Therefore the max characters
1319 # string limit applies.
1320 #
1321 # The hyperlink can be to a http, ftp, mail, internal sheet, or external
1322 # directory urls.
1323 @convert_cell_args
1324 def write_url(
1325 self,
1326 row: int,
1327 col: int,
1328 url: str,
1329 cell_format: Optional[Format] = None,
1330 string: Optional[str] = None,
1331 tip: Optional[str] = None,
1332 ):
1333 """
1334 Write a hyperlink to a worksheet cell.
1336 Args:
1337 row: The cell row (zero indexed).
1338 col: The cell column (zero indexed).
1339 url: Hyperlink url.
1340 format: An optional cell Format object.
1341 string: An optional display string for the hyperlink.
1342 tip: An optional tooltip.
1343 Returns:
1344 0: Success.
1345 -1: Row or column is out of worksheet bounds.
1346 -2: String longer than 32767 characters.
1347 -3: URL longer than Excel limit of 255 characters.
1348 -4: Exceeds Excel limit of 65,530 urls per worksheet.
1349 """
1350 return self._write_url(row, col, url, cell_format, string, tip)
1352 # Undecorated version of write_url().
1353 def _write_url(
1354 self, row: int, col: int, url, cell_format=None, string=None, tip=None
1355 ) -> int:
1356 # Check that row and col are valid and store max and min values
1357 if self._check_dimensions(row, col):
1358 return -1
1360 # If the URL is a string convert it to a Url object.
1361 if not isinstance(url, Url):
1363 # For backwards compatibility check if the string URL exceeds the
1364 # Excel character limit for URLs and ignore it with a warning.
1365 max_url = self.max_url_length
1366 if "#" in url:
1367 url_str, anchor_str = url.split("#", 1)
1368 else:
1369 url_str = url
1370 anchor_str = ""
1372 if len(url_str) > max_url or len(anchor_str) > max_url:
1373 warn(
1374 f"Ignoring URL '{url}' with link or location/anchor > {max_url} "
1375 f"characters since it exceeds Excel's limit for URLs."
1376 )
1377 return -3
1379 url = Url(url)
1381 if string is not None:
1382 url._text = string
1384 if tip is not None:
1385 url._tip = tip
1387 # Check the limit of URLs per worksheet.
1388 self.hlink_count += 1
1390 if self.hlink_count > 65530:
1391 warn(
1392 f"Ignoring URL '{url._original_url}' since it exceeds Excel's limit of "
1393 f"65,530 URLs per worksheet."
1394 )
1395 return -4
1397 # Add the default URL format.
1398 if cell_format is None:
1399 cell_format = self.default_url_format
1401 if not self.ignore_write_string:
1402 # Write previous row if in in-line string constant_memory mode.
1403 if self.constant_memory and row > self.previous_row:
1404 self._write_single_row(row)
1406 # Write the hyperlink string.
1407 self._write_string(row, col, url.text, cell_format)
1409 # Store the hyperlink data in a separate structure.
1410 self.hyperlinks[row][col] = url
1412 return 0
1414 @convert_cell_args
1415 def write_rich_string(
1416 self, row: int, col: int, *args: Union[str, Format]
1417 ) -> Literal[0, -1, -2, -3, -4, -5]:
1418 """
1419 Write a "rich" string with multiple formats to a worksheet cell.
1421 Args:
1422 row: The cell row (zero indexed).
1423 col: The cell column (zero indexed).
1424 string_parts: String and format pairs.
1425 cell_format: Optional Format object.
1427 Returns:
1428 0: Success.
1429 -1: Row or column is out of worksheet bounds.
1430 -2: String truncated to 32k characters.
1431 -3: 2 consecutive formats used.
1432 -4: Empty string used.
1433 -5: Insufficient parameters.
1435 """
1437 return self._write_rich_string(row, col, *args)
1439 # Undecorated version of write_rich_string().
1440 def _write_rich_string(self, row: int, col: int, *args) -> int:
1441 tokens = list(args)
1442 cell_format = None
1443 string_index = 0
1444 raw_string = ""
1446 # Check that row and col are valid and store max and min values
1447 if self._check_dimensions(row, col):
1448 return -1
1450 # If the last arg is a format we use it as the cell format.
1451 if isinstance(tokens[-1], Format):
1452 cell_format = tokens.pop()
1454 # Create a temp XMLWriter object and use it to write the rich string
1455 # XML to a string.
1456 fh = StringIO()
1457 self.rstring = XMLwriter()
1458 self.rstring._set_filehandle(fh)
1460 # Create a temp format with the default font for unformatted fragments.
1461 default = Format()
1463 # Convert list of format, string tokens to pairs of (format, string)
1464 # except for the first string fragment which doesn't require a default
1465 # formatting run. Use the default for strings without a leading format.
1466 fragments = []
1467 previous = "format"
1468 pos = 0
1470 if len(tokens) <= 2:
1471 warn(
1472 "You must specify more than 2 format/fragments for rich "
1473 "strings. Ignoring input in write_rich_string()."
1474 )
1475 return -5
1477 for token in tokens:
1478 if not isinstance(token, Format):
1479 # Token is a string.
1480 if previous != "format":
1481 # If previous token wasn't a format add one before string.
1482 fragments.append(default)
1483 fragments.append(token)
1484 else:
1485 # If previous token was a format just add the string.
1486 fragments.append(token)
1488 if token == "":
1489 warn(
1490 "Excel doesn't allow empty strings in rich strings. "
1491 "Ignoring input in write_rich_string()."
1492 )
1493 return -4
1495 # Keep track of unformatted string.
1496 raw_string += token
1497 previous = "string"
1498 else:
1499 # Can't allow 2 formats in a row.
1500 if previous == "format" and pos > 0:
1501 warn(
1502 "Excel doesn't allow 2 consecutive formats in rich "
1503 "strings. Ignoring input in write_rich_string()."
1504 )
1505 return -3
1507 # Token is a format object. Add it to the fragment list.
1508 fragments.append(token)
1509 previous = "format"
1511 pos += 1
1513 # If the first token is a string start the <r> element.
1514 if not isinstance(fragments[0], Format):
1515 self.rstring._xml_start_tag("r")
1517 # Write the XML elements for the $format $string fragments.
1518 for token in fragments:
1519 if isinstance(token, Format):
1520 # Write the font run.
1521 self.rstring._xml_start_tag("r")
1522 self._write_font(token)
1523 else:
1524 # Write the string fragment part, with whitespace handling.
1525 attributes = []
1527 if _preserve_whitespace(token):
1528 attributes.append(("xml:space", "preserve"))
1530 self.rstring._xml_data_element("t", token, attributes)
1531 self.rstring._xml_end_tag("r")
1533 # Read the in-memory string.
1534 string = self.rstring.fh.getvalue()
1536 # Check that the string is < 32767 chars.
1537 if len(raw_string) > self.xls_strmax:
1538 warn(
1539 "String length must be less than or equal to Excel's limit "
1540 "of 32,767 characters in write_rich_string()."
1541 )
1542 return -2
1544 # Write a shared string or an in-line string in constant_memory mode.
1545 if not self.constant_memory:
1546 string_index = self.str_table._get_shared_string_index(string)
1547 else:
1548 string_index = string
1550 # Write previous row if in in-line string constant_memory mode.
1551 if self.constant_memory and row > self.previous_row:
1552 self._write_single_row(row)
1554 # Store the cell data in the worksheet data table.
1555 self.table[row][col] = CellRichStringTuple(
1556 string_index, cell_format, raw_string
1557 )
1559 return 0
1561 def add_write_handler(self, user_type, user_function) -> None:
1562 """
1563 Add a callback function to the write() method to handle user defined
1564 types.
1566 Args:
1567 user_type: The user type() to match on.
1568 user_function: The user defined function to write the type data.
1569 Returns:
1570 Nothing.
1572 """
1574 self.write_handlers[user_type] = user_function
1576 @convert_cell_args
1577 def write_row(
1578 self, row: int, col: int, data, cell_format: Optional[Format] = None
1579 ) -> Union[Literal[0], Any]:
1580 """
1581 Write a row of data starting from (row, col).
1583 Args:
1584 row: The cell row (zero indexed).
1585 col: The cell column (zero indexed).
1586 data: A list of tokens to be written with write().
1587 format: An optional cell Format object.
1588 Returns:
1589 0: Success.
1590 other: Return value of write() method.
1592 """
1593 for token in data:
1594 error = self._write(row, col, token, cell_format)
1595 if error:
1596 return error
1597 col += 1
1599 return 0
1601 @convert_cell_args
1602 def write_column(
1603 self, row: int, col: int, data, cell_format: Optional[Format] = None
1604 ) -> Union[Literal[0], Any]:
1605 """
1606 Write a column of data starting from (row, col).
1608 Args:
1609 row: The cell row (zero indexed).
1610 col: The cell column (zero indexed).
1611 data: A list of tokens to be written with write().
1612 format: An optional cell Format object.
1613 Returns:
1614 0: Success.
1615 other: Return value of write() method.
1617 """
1618 for token in data:
1619 error = self._write(row, col, token, cell_format)
1620 if error:
1621 return error
1622 row += 1
1624 return 0
1626 @convert_cell_args
1627 def insert_image(
1628 self,
1629 row: int,
1630 col: int,
1631 source: Union[str, BytesIO, Image],
1632 options: Optional[Dict[str, Any]] = None,
1633 ) -> Literal[0, -1]:
1634 """
1635 Insert an image with its top-left corner in a worksheet cell.
1637 Args:
1638 row: The cell row (zero indexed).
1639 col: The cell column (zero indexed).
1640 source: Filename, BytesIO, or Image object.
1641 options: Position, scale, url and data stream of the image.
1643 Returns:
1644 0: Success.
1645 -1: Row or column is out of worksheet bounds.
1647 """
1648 # Check insert (row, col) without storing.
1649 if self._check_dimensions(row, col, True, True):
1650 warn(f"Cannot insert image at ({row}, {col}).")
1651 return -1
1653 # Convert the source to an Image object.
1654 image = self._image_from_source(source, options)
1656 image._row = row
1657 image._col = col
1658 image._set_user_options(options)
1660 self.images.append(image)
1662 return 0
1664 @convert_cell_args
1665 def embed_image(
1666 self,
1667 row: int,
1668 col: int,
1669 source: Union[str, BytesIO, Image],
1670 options: Optional[Dict[str, Any]] = None,
1671 ) -> Literal[0, -1]:
1672 """
1673 Embed an image in a worksheet cell.
1675 Args:
1676 row: The cell row (zero indexed).
1677 col: The cell column (zero indexed).
1678 source: Filename, BytesIO, or Image object.
1679 options: Url and data stream of the image.
1681 Returns:
1682 0: Success.
1683 -1: Row or column is out of worksheet bounds.
1685 """
1686 # Check insert (row, col) without storing.
1687 if self._check_dimensions(row, col):
1688 warn(f"Cannot embed image at ({row}, {col}).")
1689 return -1
1691 if options is None:
1692 options = {}
1694 # Convert the source to an Image object.
1695 image = self._image_from_source(source, options)
1696 image._set_user_options(options)
1698 cell_format = options.get("cell_format", None)
1700 if image.url:
1701 if cell_format is None:
1702 cell_format = self.default_url_format
1704 self.ignore_write_string = True
1705 self.write_url(row, col, image.url, cell_format)
1706 self.ignore_write_string = False
1708 image_index = self.embedded_images.get_image_index(image)
1710 # Store the cell error and image index in the worksheet data table.
1711 self.table[row][col] = CellErrorTuple("#VALUE!", cell_format, image_index)
1713 return 0
1715 @convert_cell_args
1716 def insert_textbox(
1717 self, row: int, col: int, text: str, options: Optional[Dict[str, Any]] = None
1718 ) -> Literal[0, -1]:
1719 """
1720 Insert an textbox with its top-left corner in a worksheet cell.
1722 Args:
1723 row: The cell row (zero indexed).
1724 col: The cell column (zero indexed).
1725 text: The text for the textbox.
1726 options: Textbox options.
1728 Returns:
1729 0: Success.
1730 -1: Row or column is out of worksheet bounds.
1732 """
1733 # Check insert (row, col) without storing.
1734 if self._check_dimensions(row, col, True, True):
1735 warn(f"Cannot insert textbox at ({row}, {col}).")
1736 return -1
1738 if text is None:
1739 text = ""
1741 if options is None:
1742 options = {}
1744 x_offset = options.get("x_offset", 0)
1745 y_offset = options.get("y_offset", 0)
1746 x_scale = options.get("x_scale", 1)
1747 y_scale = options.get("y_scale", 1)
1748 anchor = options.get("object_position", 1)
1749 description = options.get("description", None)
1750 decorative = options.get("decorative", False)
1752 self.shapes.append(
1753 [
1754 row,
1755 col,
1756 x_offset,
1757 y_offset,
1758 x_scale,
1759 y_scale,
1760 text,
1761 anchor,
1762 options,
1763 description,
1764 decorative,
1765 ]
1766 )
1767 return 0
1769 @convert_cell_args
1770 def insert_chart(
1771 self, row: int, col: int, chart: Chart, options: Optional[Dict[str, Any]] = None
1772 ) -> Literal[0, -1, -2]:
1773 """
1774 Insert an chart with its top-left corner in a worksheet cell.
1776 Args:
1777 row: The cell row (zero indexed).
1778 col: The cell column (zero indexed).
1779 chart: Chart object.
1780 options: Position and scale of the chart.
1782 Returns:
1783 0: Success.
1784 -1: Row or column is out of worksheet bounds.
1786 """
1787 # Check insert (row, col) without storing.
1788 if self._check_dimensions(row, col, True, True):
1789 warn(f"Cannot insert chart at ({row}, {col}).")
1790 return -1
1792 if options is None:
1793 options = {}
1795 # Ensure a chart isn't inserted more than once.
1796 if chart.already_inserted or chart.combined and chart.combined.already_inserted:
1797 warn("Chart cannot be inserted in a worksheet more than once.")
1798 return -2
1800 chart.already_inserted = True
1802 if chart.combined:
1803 chart.combined.already_inserted = True
1805 x_offset = options.get("x_offset", 0)
1806 y_offset = options.get("y_offset", 0)
1807 x_scale = options.get("x_scale", 1)
1808 y_scale = options.get("y_scale", 1)
1809 anchor = options.get("object_position", 1)
1810 description = options.get("description", None)
1811 decorative = options.get("decorative", False)
1813 # Allow Chart to override the scale and offset.
1814 if chart.x_scale != 1:
1815 x_scale = chart.x_scale
1817 if chart.y_scale != 1:
1818 y_scale = chart.y_scale
1820 if chart.x_offset:
1821 x_offset = chart.x_offset
1823 if chart.y_offset:
1824 y_offset = chart.y_offset
1826 self.charts.append(
1827 [
1828 row,
1829 col,
1830 chart,
1831 x_offset,
1832 y_offset,
1833 x_scale,
1834 y_scale,
1835 anchor,
1836 description,
1837 decorative,
1838 ]
1839 )
1840 return 0
1842 @convert_cell_args
1843 def write_comment(
1844 self, row: int, col: int, comment: str, options: Optional[Dict[str, Any]] = None
1845 ) -> Literal[0, -1, -2]:
1846 """
1847 Write a comment to a worksheet cell.
1849 Args:
1850 row: The cell row (zero indexed).
1851 col: The cell column (zero indexed).
1852 comment: Cell comment. Str.
1853 options: Comment formatting options.
1855 Returns:
1856 0: Success.
1857 -1: Row or column is out of worksheet bounds.
1858 -2: String longer than 32k characters.
1860 """
1861 # Check that row and col are valid and store max and min values
1862 if self._check_dimensions(row, col):
1863 return -1
1865 # Check that the comment string is < 32767 chars.
1866 if len(comment) > self.xls_strmax:
1867 return -2
1869 self.has_vml = True
1870 self.has_comments = True
1872 # Store the options of the cell comment, to process on file close.
1873 comment = CommentType(row, col, comment, options)
1874 self.comments[row][col] = comment
1876 return 0
1878 def show_comments(self) -> None:
1879 """
1880 Make any comments in the worksheet visible.
1882 Args:
1883 None.
1885 Returns:
1886 Nothing.
1888 """
1889 self.comments_visible = True
1891 def set_background(
1892 self, source: Union[str, BytesIO, Image], is_byte_stream: bool = False
1893 ) -> Literal[0]:
1894 """
1895 Set a background image for a worksheet.
1897 Args:
1898 source: Filename, BytesIO, or Image object.
1899 is_byte_stream: Deprecated. Use a BytesIO object instead.
1901 Returns:
1902 0: Success.
1904 """
1905 # Convert the source to an Image object.
1906 image = self._image_from_source(source)
1908 self.background_image = image
1910 if is_byte_stream:
1911 warn(
1912 "The `is_byte_stream` parameter in `set_background()` is deprecated. "
1913 "This argument can be omitted if you are using a BytesIO object."
1914 )
1916 return 0
1918 def set_comments_author(self, author) -> None:
1919 """
1920 Set the default author of the cell comments.
1922 Args:
1923 author: Comment author name. String.
1925 Returns:
1926 Nothing.
1928 """
1929 self.comments_author = author
1931 def get_name(self):
1932 """
1933 Retrieve the worksheet name.
1935 Args:
1936 None.
1938 Returns:
1939 Nothing.
1941 """
1942 # There is no set_name() method. Name must be set in add_worksheet().
1943 return self.name
1945 def activate(self) -> None:
1946 """
1947 Set this worksheet as the active worksheet, i.e. the worksheet that is
1948 displayed when the workbook is opened. Also set it as selected.
1950 Note: An active worksheet cannot be hidden.
1952 Args:
1953 None.
1955 Returns:
1956 Nothing.
1958 """
1959 self.hidden = 0
1960 self.selected = 1
1961 self.worksheet_meta.activesheet = self.index
1963 def select(self) -> None:
1964 """
1965 Set current worksheet as a selected worksheet, i.e. the worksheet
1966 has its tab highlighted.
1968 Note: A selected worksheet cannot be hidden.
1970 Args:
1971 None.
1973 Returns:
1974 Nothing.
1976 """
1977 self.selected = 1
1978 self.hidden = 0
1980 def hide(self) -> None:
1981 """
1982 Hide the current worksheet.
1984 Args:
1985 None.
1987 Returns:
1988 Nothing.
1990 """
1991 self.hidden = 1
1993 # A hidden worksheet shouldn't be active or selected.
1994 self.selected = 0
1996 def very_hidden(self) -> None:
1997 """
1998 Hide the current worksheet. This can only be unhidden by VBA.
2000 Args:
2001 None.
2003 Returns:
2004 Nothing.
2006 """
2007 self.hidden = 2
2009 # A hidden worksheet shouldn't be active or selected.
2010 self.selected = 0
2012 def set_first_sheet(self) -> None:
2013 """
2014 Set current worksheet as the first visible sheet. This is necessary
2015 when there are a large number of worksheets and the activated
2016 worksheet is not visible on the screen.
2018 Note: A selected worksheet cannot be hidden.
2020 Args:
2021 None.
2023 Returns:
2024 Nothing.
2026 """
2027 self.hidden = 0 # Active worksheet can't be hidden.
2028 self.worksheet_meta.firstsheet = self.index
2030 @convert_column_args
2031 def set_column(
2032 self,
2033 first_col: int,
2034 last_col: int,
2035 width: Optional[float] = None,
2036 cell_format: Optional[Format] = None,
2037 options: Optional[Dict[str, Any]] = None,
2038 ) -> Literal[0, -1]:
2039 """
2040 Set the width, and other properties of a single column or a
2041 range of columns.
2043 Args:
2044 first_col: First column (zero-indexed).
2045 last_col: Last column (zero-indexed). Can be same as first_col.
2046 width: Column width. (optional).
2047 cell_format: Column cell_format. (optional).
2048 options: Dict of options such as hidden and level.
2050 Returns:
2051 0: Success.
2052 -1: Column number is out of worksheet bounds.
2054 """
2055 if options is None:
2056 options = {}
2058 # Ensure 2nd col is larger than first.
2059 if first_col > last_col:
2060 (first_col, last_col) = (last_col, first_col)
2062 # Don't modify the row dimensions when checking the columns.
2063 ignore_row = True
2065 # Set optional column values.
2066 hidden = options.get("hidden", False)
2067 collapsed = options.get("collapsed", False)
2068 level = options.get("level", 0)
2070 # Store the column dimension only in some conditions.
2071 if cell_format or (width and hidden):
2072 ignore_col = False
2073 else:
2074 ignore_col = True
2076 # Check that each column is valid and store the max and min values.
2077 if self._check_dimensions(0, last_col, ignore_row, ignore_col):
2078 return -1
2079 if self._check_dimensions(0, first_col, ignore_row, ignore_col):
2080 return -1
2082 # Set the limits for the outline levels (0 <= x <= 7).
2083 level = max(level, 0)
2084 level = min(level, 7)
2086 self.outline_col_level = max(self.outline_col_level, level)
2088 # Store the column data.
2089 for col in range(first_col, last_col + 1):
2090 self.col_info[col] = [width, cell_format, hidden, level, collapsed, False]
2092 # Store the column change to allow optimizations.
2093 self.col_size_changed = True
2095 return 0
2097 @convert_column_args
2098 def set_column_pixels(
2099 self,
2100 first_col: int,
2101 last_col: int,
2102 width: Optional[float] = None,
2103 cell_format: Optional[Format] = None,
2104 options: Optional[Dict[str, Any]] = None,
2105 ) -> Literal[0, -1]:
2106 """
2107 Set the width, and other properties of a single column or a
2108 range of columns, where column width is in pixels.
2110 Args:
2111 first_col: First column (zero-indexed).
2112 last_col: Last column (zero-indexed). Can be same as first_col.
2113 width: Column width in pixels. (optional).
2114 cell_format: Column cell_format. (optional).
2115 options: Dict of options such as hidden and level.
2117 Returns:
2118 0: Success.
2119 -1: Column number is out of worksheet bounds.
2121 """
2122 if width is not None:
2123 width = self._pixels_to_width(width)
2125 return self.set_column(first_col, last_col, width, cell_format, options)
2127 def autofit(self, max_width: int = 1790) -> None:
2128 """
2129 Simulate autofit based on the data, and datatypes in each column.
2131 Args:
2132 max_width (optional): max column width to autofit, in pixels.
2134 Returns:
2135 Nothing.
2137 """
2138 # pylint: disable=too-many-nested-blocks
2139 if self.constant_memory:
2140 warn("Autofit is not supported in constant_memory mode.")
2141 return
2143 # No data written to the target sheet; nothing to autofit
2144 if self.dim_rowmax is None:
2145 return
2147 # Store the max pixel width for each column.
2148 col_width_max = {}
2150 # Convert the autofit maximum pixel width to a column/character width,
2151 # but limit it to the Excel max limit.
2152 max_width = min(self._pixels_to_width(max_width), 255.0)
2154 # Create a reverse lookup for the share strings table so we can convert
2155 # the string id back to the original string.
2156 strings = sorted(
2157 self.str_table.string_table, key=self.str_table.string_table.__getitem__
2158 )
2160 for row_num in range(self.dim_rowmin, self.dim_rowmax + 1):
2161 if not self.table.get(row_num):
2162 continue
2164 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
2165 if col_num in self.table[row_num]:
2166 cell = self.table[row_num][col_num]
2167 cell_type = cell.__class__.__name__
2168 length = 0
2170 if cell_type in ("String", "RichString"):
2171 # Handle strings and rich strings.
2172 #
2173 # For standard shared strings we do a reverse lookup
2174 # from the shared string id to the actual string. For
2175 # rich strings we use the unformatted string. We also
2176 # split multi-line strings and handle each part
2177 # separately.
2178 if cell_type == "String":
2179 string_id = cell.string
2180 string = strings[string_id]
2181 else:
2182 string = cell.raw_string
2184 if "\n" not in string:
2185 # Single line string.
2186 length = xl_pixel_width(string)
2187 else:
2188 # Handle multi-line strings.
2189 for string in string.split("\n"):
2190 seg_length = xl_pixel_width(string)
2191 length = max(length, seg_length)
2193 elif cell_type == "Number":
2194 # Handle numbers.
2195 #
2196 # We use a workaround/optimization for numbers since
2197 # digits all have a pixel width of 7. This gives a
2198 # slightly greater width for the decimal place and
2199 # minus sign but only by a few pixels and
2200 # over-estimation is okay.
2201 length = 7 * len(str(cell.number))
2203 elif cell_type == "Datetime":
2204 # Handle dates.
2205 #
2206 # The following uses the default width for mm/dd/yyyy
2207 # dates. It isn't feasible to parse the number format
2208 # to get the actual string width for all format types.
2209 length = self.default_date_pixels
2211 elif cell_type == "Boolean":
2212 # Handle boolean values.
2213 #
2214 # Use the Excel standard widths for TRUE and FALSE.
2215 if cell.boolean:
2216 length = 31
2217 else:
2218 length = 36
2220 elif cell_type in ("Formula", "ArrayFormula"):
2221 # Handle formulas.
2222 #
2223 # We only try to autofit a formula if it has a
2224 # non-zero value.
2225 if isinstance(cell.value, (float, int)):
2226 if cell.value > 0:
2227 length = 7 * len(str(cell.value))
2229 elif isinstance(cell.value, str):
2230 length = xl_pixel_width(cell.value)
2232 elif isinstance(cell.value, bool):
2233 if cell.value:
2234 length = 31
2235 else:
2236 length = 36
2238 # If the cell is in an autofilter header we add an
2239 # additional 16 pixels for the dropdown arrow.
2240 if self.filter_cells.get((row_num, col_num)) and length > 0:
2241 length += 16
2243 # Add the string length to the lookup table.
2244 width_max = col_width_max.get(col_num, 0)
2245 if length > width_max:
2246 col_width_max[col_num] = length
2248 # Apply the width to the column.
2249 for col_num, pixel_width in col_width_max.items():
2250 # Convert the string pixel width to a character width using an
2251 # additional padding of 7 pixels, like Excel.
2252 width = self._pixels_to_width(pixel_width + 7)
2254 # Limit the width to the maximum user or Excel value.
2255 width = min(width, max_width)
2257 # Add the width to an existing col info structure or add a new one.
2258 if self.col_info.get(col_num):
2259 # We only update the width for an existing column if it is
2260 # greater than the user defined value. This allows the user
2261 # to pre-load a minimum col width.
2262 col_info = self.col_info.get(col_num)
2263 user_width = col_info[0]
2264 hidden = col_info[5]
2265 if user_width is not None and not hidden:
2266 # Col info is user defined.
2267 if width > user_width:
2268 self.col_info[col_num][0] = width
2269 self.col_info[col_num][5] = True
2270 else:
2271 self.col_info[col_num][0] = width
2272 self.col_info[col_num][5] = True
2273 else:
2274 self.col_info[col_num] = [width, None, False, 0, False, True]
2276 def set_row(
2277 self,
2278 row: int,
2279 height: Optional[float] = None,
2280 cell_format: Optional[Format] = None,
2281 options: Optional[Dict[str, Any]] = None,
2282 ) -> Literal[0, -1]:
2283 """
2284 Set the width, and other properties of a row.
2286 Args:
2287 row: Row number (zero-indexed).
2288 height: Row height. (optional).
2289 cell_format: Row cell_format. (optional).
2290 options: Dict of options such as hidden, level and collapsed.
2292 Returns:
2293 0: Success.
2294 -1: Row number is out of worksheet bounds.
2296 """
2297 if options is None:
2298 options = {}
2300 # Use minimum col in _check_dimensions().
2301 if self.dim_colmin is not None:
2302 min_col = self.dim_colmin
2303 else:
2304 min_col = 0
2306 # Check that row is valid.
2307 if self._check_dimensions(row, min_col):
2308 return -1
2310 if height is None:
2311 height = self.default_row_height
2313 # Set optional row values.
2314 hidden = options.get("hidden", False)
2315 collapsed = options.get("collapsed", False)
2316 level = options.get("level", 0)
2318 # If the height is 0 the row is hidden and the height is the default.
2319 if height == 0:
2320 hidden = 1
2321 height = self.default_row_height
2323 # Set the limits for the outline levels (0 <= x <= 7).
2324 level = max(level, 0)
2325 level = min(level, 7)
2327 self.outline_row_level = max(self.outline_row_level, level)
2329 # Store the row properties.
2330 self.set_rows[row] = [height, cell_format, hidden, level, collapsed]
2332 # Store the row change to allow optimizations.
2333 self.row_size_changed = True
2335 # Store the row sizes for use when calculating image vertices.
2336 self.row_sizes[row] = [height, hidden]
2338 return 0
2340 def set_row_pixels(
2341 self,
2342 row: int,
2343 height: Optional[float] = None,
2344 cell_format: Optional[Format] = None,
2345 options: Optional[Dict[str, Any]] = None,
2346 ) -> Literal[0, -1]:
2347 """
2348 Set the width (in pixels), and other properties of a row.
2350 Args:
2351 row: Row number (zero-indexed).
2352 height: Row height in pixels. (optional).
2353 cell_format: Row cell_format. (optional).
2354 options: Dict of options such as hidden, level and collapsed.
2356 Returns:
2357 0: Success.
2358 -1: Row number is out of worksheet bounds.
2360 """
2361 if height is not None:
2362 height = self._pixels_to_height(height)
2364 return self.set_row(row, height, cell_format, options)
2366 def set_default_row(
2367 self, height: Optional[float] = None, hide_unused_rows: bool = False
2368 ) -> None:
2369 """
2370 Set the default row properties.
2372 Args:
2373 height: Default height. Optional, defaults to 15.
2374 hide_unused_rows: Hide unused rows. Optional, defaults to False.
2376 Returns:
2377 Nothing.
2379 """
2380 if height is None:
2381 height = self.default_row_height
2383 if height != self.original_row_height:
2384 # Store the row change to allow optimizations.
2385 self.row_size_changed = True
2386 self.default_row_height = height
2388 if hide_unused_rows:
2389 self.default_row_zeroed = 1
2391 @convert_range_args
2392 def merge_range(
2393 self,
2394 first_row: int,
2395 first_col: int,
2396 last_row: int,
2397 last_col: int,
2398 data: Any,
2399 cell_format: Optional[Format] = None,
2400 ) -> int:
2401 """
2402 Merge a range of cells.
2404 Args:
2405 first_row: The first row of the cell range. (zero indexed).
2406 first_col: The first column of the cell range.
2407 last_row: The last row of the cell range. (zero indexed).
2408 last_col: The last column of the cell range.
2409 data: Cell data.
2410 cell_format: Cell Format object.
2412 Returns:
2413 0: Success.
2414 -1: Row or column is out of worksheet bounds.
2415 other: Return value of write().
2417 """
2418 # Merge a range of cells. The first cell should contain the data and
2419 # the others should be blank. All cells should have the same format.
2421 # Excel doesn't allow a single cell to be merged
2422 if first_row == last_row and first_col == last_col:
2423 warn("Can't merge single cell")
2424 return -1
2426 # Swap last row/col with first row/col as necessary
2427 if first_row > last_row:
2428 (first_row, last_row) = (last_row, first_row)
2429 if first_col > last_col:
2430 (first_col, last_col) = (last_col, first_col)
2432 # Check that row and col are valid and store max and min values.
2433 if self._check_dimensions(first_row, first_col):
2434 return -1
2435 if self._check_dimensions(last_row, last_col):
2436 return -1
2438 # Check if the merge range overlaps a previous merged or table range.
2439 # This is a critical file corruption error in Excel.
2440 cell_range = xl_range(first_row, first_col, last_row, last_col)
2441 for row in range(first_row, last_row + 1):
2442 for col in range(first_col, last_col + 1):
2443 if self.merged_cells.get((row, col)):
2444 previous_range = self.merged_cells.get((row, col))
2445 raise OverlappingRange(
2446 f"Merge range '{cell_range}' overlaps previous merge "
2447 f"range '{previous_range}'."
2448 )
2450 if self.table_cells.get((row, col)):
2451 previous_range = self.table_cells.get((row, col))
2452 raise OverlappingRange(
2453 f"Merge range '{cell_range}' overlaps previous table "
2454 f"range '{previous_range}'."
2455 )
2457 self.merged_cells[(row, col)] = cell_range
2459 # Store the merge range.
2460 self.merge.append([first_row, first_col, last_row, last_col])
2462 # Write the first cell
2463 self._write(first_row, first_col, data, cell_format)
2465 # Pad out the rest of the area with formatted blank cells.
2466 for row in range(first_row, last_row + 1):
2467 for col in range(first_col, last_col + 1):
2468 if row == first_row and col == first_col:
2469 continue
2470 self._write_blank(row, col, "", cell_format)
2472 return 0
2474 @convert_range_args
2475 def autofilter(
2476 self, first_row: int, first_col: int, last_row: int, last_col: int
2477 ) -> None:
2478 """
2479 Set the autofilter area in the worksheet.
2481 Args:
2482 first_row: The first row of the cell range. (zero indexed).
2483 first_col: The first column of the cell range.
2484 last_row: The last row of the cell range. (zero indexed).
2485 last_col: The last column of the cell range.
2487 Returns:
2488 Nothing.
2490 """
2491 # Reverse max and min values if necessary.
2492 if last_row < first_row:
2493 (first_row, last_row) = (last_row, first_row)
2494 if last_col < first_col:
2495 (first_col, last_col) = (last_col, first_col)
2497 # Build up the autofilter area range "Sheet1!$A$1:$C$13".
2498 area = self._convert_name_area(first_row, first_col, last_row, last_col)
2499 ref = xl_range(first_row, first_col, last_row, last_col)
2501 self.autofilter_area = area
2502 self.autofilter_ref = ref
2503 self.filter_range = [first_col, last_col]
2505 # Store the filter cell positions for use in the autofit calculation.
2506 for col in range(first_col, last_col + 1):
2507 # Check that the autofilter doesn't overlap a table filter.
2508 if self.filter_cells.get((first_row, col)):
2509 filter_type, filter_range = self.filter_cells.get((first_row, col))
2510 if filter_type == "table":
2511 raise OverlappingRange(
2512 f"Worksheet autofilter range '{ref}' overlaps previous "
2513 f"Table autofilter range '{filter_range}'."
2514 )
2516 self.filter_cells[(first_row, col)] = ("worksheet", ref)
2518 def filter_column(self, col: int, criteria: str) -> None:
2519 """
2520 Set the column filter criteria.
2522 Args:
2523 col: Filter column (zero-indexed).
2524 criteria: Filter criteria.
2526 Returns:
2527 Nothing.
2529 """
2530 if not self.autofilter_area:
2531 warn("Must call autofilter() before filter_column()")
2532 return
2534 # Check for a column reference in A1 notation and substitute.
2535 try:
2536 int(col)
2537 except ValueError:
2538 # Convert col ref to a cell ref and then to a col number.
2539 col_letter = col
2540 (_, col) = xl_cell_to_rowcol(col + "1")
2542 if col >= self.xls_colmax:
2543 warn(f"Invalid column '{col_letter}'")
2544 return
2546 (col_first, col_last) = self.filter_range
2548 # Reject column if it is outside filter range.
2549 if col < col_first or col > col_last:
2550 warn(
2551 f"Column '{col}' outside autofilter() column "
2552 f"range ({col_first}, {col_last})"
2553 )
2554 return
2556 tokens = self._extract_filter_tokens(criteria)
2558 if len(tokens) not in (3, 7):
2559 warn(f"Incorrect number of tokens in criteria '{criteria}'")
2561 tokens = self._parse_filter_expression(criteria, tokens)
2563 # Excel handles single or double custom filters as default filters.
2564 # We need to check for them and handle them accordingly.
2565 if len(tokens) == 2 and tokens[0] == 2:
2566 # Single equality.
2567 self.filter_column_list(col, [tokens[1]])
2568 elif len(tokens) == 5 and tokens[0] == 2 and tokens[2] == 1 and tokens[3] == 2:
2569 # Double equality with "or" operator.
2570 self.filter_column_list(col, [tokens[1], tokens[4]])
2571 else:
2572 # Non default custom filter.
2573 self.filter_cols[col] = tokens
2574 self.filter_type[col] = 0
2576 self.filter_on = 1
2578 def filter_column_list(self, col: int, filters: List[str]) -> None:
2579 """
2580 Set the column filter criteria in Excel 2007 list style.
2582 Args:
2583 col: Filter column (zero-indexed).
2584 filters: List of filter criteria to match.
2586 Returns:
2587 Nothing.
2589 """
2590 if not self.autofilter_area:
2591 warn("Must call autofilter() before filter_column()")
2592 return
2594 # Check for a column reference in A1 notation and substitute.
2595 try:
2596 int(col)
2597 except ValueError:
2598 # Convert col ref to a cell ref and then to a col number.
2599 col_letter = col
2600 (_, col) = xl_cell_to_rowcol(col + "1")
2602 if col >= self.xls_colmax:
2603 warn(f"Invalid column '{col_letter}'")
2604 return
2606 (col_first, col_last) = self.filter_range
2608 # Reject column if it is outside filter range.
2609 if col < col_first or col > col_last:
2610 warn(
2611 f"Column '{col}' outside autofilter() column range "
2612 f"({col_first},{col_last})"
2613 )
2614 return
2616 self.filter_cols[col] = filters
2617 self.filter_type[col] = 1
2618 self.filter_on = 1
2620 @convert_range_args
2621 def data_validation(
2622 self,
2623 first_row: int,
2624 first_col: int,
2625 last_row: int,
2626 last_col: int,
2627 options: Optional[Dict[str, Any]] = None,
2628 ) -> Literal[0, -1, -2]:
2629 """
2630 Add a data validation to a worksheet.
2632 Args:
2633 first_row: The first row of the cell range. (zero indexed).
2634 first_col: The first column of the cell range.
2635 last_row: The last row of the cell range. (zero indexed).
2636 last_col: The last column of the cell range.
2637 options: Data validation options.
2639 Returns:
2640 0: Success.
2641 -1: Row or column is out of worksheet bounds.
2642 -2: Incorrect parameter or option.
2643 """
2644 # Check that row and col are valid without storing the values.
2645 if self._check_dimensions(first_row, first_col, True, True):
2646 return -1
2647 if self._check_dimensions(last_row, last_col, True, True):
2648 return -1
2650 if options is None:
2651 options = {}
2652 else:
2653 # Copy the user defined options so they aren't modified.
2654 options = options.copy()
2656 # Valid input parameters.
2657 valid_parameters = {
2658 "validate",
2659 "criteria",
2660 "value",
2661 "source",
2662 "minimum",
2663 "maximum",
2664 "ignore_blank",
2665 "dropdown",
2666 "show_input",
2667 "input_title",
2668 "input_message",
2669 "show_error",
2670 "error_title",
2671 "error_message",
2672 "error_type",
2673 "other_cells",
2674 "multi_range",
2675 }
2677 # Check for valid input parameters.
2678 for param_key in options.keys():
2679 if param_key not in valid_parameters:
2680 warn(f"Unknown parameter '{param_key}' in data_validation()")
2681 return -2
2683 # Map alternative parameter names 'source' or 'minimum' to 'value'.
2684 if "source" in options:
2685 options["value"] = options["source"]
2686 if "minimum" in options:
2687 options["value"] = options["minimum"]
2689 # 'validate' is a required parameter.
2690 if "validate" not in options:
2691 warn("Parameter 'validate' is required in data_validation()")
2692 return -2
2694 # List of valid validation types.
2695 valid_types = {
2696 "any": "none",
2697 "any value": "none",
2698 "whole number": "whole",
2699 "whole": "whole",
2700 "integer": "whole",
2701 "decimal": "decimal",
2702 "list": "list",
2703 "date": "date",
2704 "time": "time",
2705 "text length": "textLength",
2706 "length": "textLength",
2707 "custom": "custom",
2708 }
2710 # Check for valid validation types.
2711 if options["validate"] not in valid_types:
2712 warn(
2713 f"Unknown validation type '{options['validate']}' for parameter "
2714 f"'validate' in data_validation()"
2715 )
2716 return -2
2718 options["validate"] = valid_types[options["validate"]]
2720 # No action is required for validation type 'any' if there are no
2721 # input messages to display.
2722 if (
2723 options["validate"] == "none"
2724 and options.get("input_title") is None
2725 and options.get("input_message") is None
2726 ):
2727 return -2
2729 # The any, list and custom validations don't have a criteria so we use
2730 # a default of 'between'.
2731 if (
2732 options["validate"] == "none"
2733 or options["validate"] == "list"
2734 or options["validate"] == "custom"
2735 ):
2736 options["criteria"] = "between"
2737 options["maximum"] = None
2739 # 'criteria' is a required parameter.
2740 if "criteria" not in options:
2741 warn("Parameter 'criteria' is required in data_validation()")
2742 return -2
2744 # Valid criteria types.
2745 criteria_types = {
2746 "between": "between",
2747 "not between": "notBetween",
2748 "equal to": "equal",
2749 "=": "equal",
2750 "==": "equal",
2751 "not equal to": "notEqual",
2752 "!=": "notEqual",
2753 "<>": "notEqual",
2754 "greater than": "greaterThan",
2755 ">": "greaterThan",
2756 "less than": "lessThan",
2757 "<": "lessThan",
2758 "greater than or equal to": "greaterThanOrEqual",
2759 ">=": "greaterThanOrEqual",
2760 "less than or equal to": "lessThanOrEqual",
2761 "<=": "lessThanOrEqual",
2762 }
2764 # Check for valid criteria types.
2765 if options["criteria"] not in criteria_types:
2766 warn(
2767 f"Unknown criteria type '{options['criteria']}' for parameter "
2768 f"'criteria' in data_validation()"
2769 )
2770 return -2
2772 options["criteria"] = criteria_types[options["criteria"]]
2774 # 'Between' and 'Not between' criteria require 2 values.
2775 if options["criteria"] == "between" or options["criteria"] == "notBetween":
2776 if "maximum" not in options:
2777 warn(
2778 "Parameter 'maximum' is required in data_validation() "
2779 "when using 'between' or 'not between' criteria"
2780 )
2781 return -2
2782 else:
2783 options["maximum"] = None
2785 # Valid error dialog types.
2786 error_types = {
2787 "stop": 0,
2788 "warning": 1,
2789 "information": 2,
2790 }
2792 # Check for valid error dialog types.
2793 if "error_type" not in options:
2794 options["error_type"] = 0
2795 elif options["error_type"] not in error_types:
2796 warn(
2797 f"Unknown criteria type '{options['error_type']}' "
2798 f"for parameter 'error_type'."
2799 )
2800 return -2
2801 else:
2802 options["error_type"] = error_types[options["error_type"]]
2804 # Convert date/times value if required.
2805 if (
2806 options["validate"] in ("date", "time")
2807 and options["value"]
2808 and _supported_datetime(options["value"])
2809 ):
2810 date_time = self._convert_date_time(options["value"])
2811 # Format date number to the same precision as Excel.
2812 options["value"] = f"{date_time:.16g}"
2814 if options["maximum"] and _supported_datetime(options["maximum"]):
2815 date_time = self._convert_date_time(options["maximum"])
2816 options["maximum"] = f"{date_time:.16g}"
2818 # Check that the input title doesn't exceed the maximum length.
2819 if options.get("input_title") and len(options["input_title"]) > 32:
2820 warn(
2821 f"Length of input title '{options['input_title']}' "
2822 f"exceeds Excel's limit of 32"
2823 )
2824 return -2
2826 # Check that the error title doesn't exceed the maximum length.
2827 if options.get("error_title") and len(options["error_title"]) > 32:
2828 warn(
2829 f"Length of error title '{options['error_title']}' "
2830 f"exceeds Excel's limit of 32"
2831 )
2832 return -2
2834 # Check that the input message doesn't exceed the maximum length.
2835 if options.get("input_message") and len(options["input_message"]) > 255:
2836 warn(
2837 f"Length of input message '{options['input_message']}' "
2838 f"exceeds Excel's limit of 255"
2839 )
2840 return -2
2842 # Check that the error message doesn't exceed the maximum length.
2843 if options.get("error_message") and len(options["error_message"]) > 255:
2844 warn(
2845 f"Length of error message '{options['error_message']}' "
2846 f"exceeds Excel's limit of 255"
2847 )
2848 return -2
2850 # Check that the input list doesn't exceed the maximum length.
2851 if options["validate"] == "list" and isinstance(options["value"], list):
2852 formula = self._csv_join(*options["value"])
2853 if len(formula) > 255:
2854 warn(
2855 f"Length of list items '{formula}' exceeds Excel's limit of "
2856 f"255, use a formula range instead"
2857 )
2858 return -2
2860 # Set some defaults if they haven't been defined by the user.
2861 if "ignore_blank" not in options:
2862 options["ignore_blank"] = 1
2863 if "dropdown" not in options:
2864 options["dropdown"] = 1
2865 if "show_input" not in options:
2866 options["show_input"] = 1
2867 if "show_error" not in options:
2868 options["show_error"] = 1
2870 # These are the cells to which the validation is applied.
2871 options["cells"] = [[first_row, first_col, last_row, last_col]]
2873 # A (for now) undocumented parameter to pass additional cell ranges.
2874 if "other_cells" in options:
2875 options["cells"].extend(options["other_cells"])
2877 # Override with user defined multiple range if provided.
2878 if "multi_range" in options:
2879 options["multi_range"] = options["multi_range"].replace("$", "")
2881 # Store the validation information until we close the worksheet.
2882 self.validations.append(options)
2884 return 0
2886 @convert_range_args
2887 def conditional_format(
2888 self,
2889 first_row: int,
2890 first_col: int,
2891 last_row: int,
2892 last_col: int,
2893 options: Optional[Dict[str, Any]] = None,
2894 ) -> Literal[0, -1, -2]:
2895 """
2896 Add a conditional format to a worksheet.
2898 Args:
2899 first_row: The first row of the cell range. (zero indexed).
2900 first_col: The first column of the cell range.
2901 last_row: The last row of the cell range. (zero indexed).
2902 last_col: The last column of the cell range.
2903 options: Conditional format options.
2905 Returns:
2906 0: Success.
2907 -1: Row or column is out of worksheet bounds.
2908 -2: Incorrect parameter or option.
2909 """
2910 # Check that row and col are valid without storing the values.
2911 if self._check_dimensions(first_row, first_col, True, True):
2912 return -1
2913 if self._check_dimensions(last_row, last_col, True, True):
2914 return -1
2916 if options is None:
2917 options = {}
2918 else:
2919 # Copy the user defined options so they aren't modified.
2920 options = options.copy()
2922 # Valid input parameters.
2923 valid_parameter = {
2924 "type",
2925 "format",
2926 "criteria",
2927 "value",
2928 "minimum",
2929 "maximum",
2930 "stop_if_true",
2931 "min_type",
2932 "mid_type",
2933 "max_type",
2934 "min_value",
2935 "mid_value",
2936 "max_value",
2937 "min_color",
2938 "mid_color",
2939 "max_color",
2940 "min_length",
2941 "max_length",
2942 "multi_range",
2943 "bar_color",
2944 "bar_negative_color",
2945 "bar_negative_color_same",
2946 "bar_solid",
2947 "bar_border_color",
2948 "bar_negative_border_color",
2949 "bar_negative_border_color_same",
2950 "bar_no_border",
2951 "bar_direction",
2952 "bar_axis_position",
2953 "bar_axis_color",
2954 "bar_only",
2955 "data_bar_2010",
2956 "icon_style",
2957 "reverse_icons",
2958 "icons_only",
2959 "icons",
2960 }
2962 # Check for valid input parameters.
2963 for param_key in options.keys():
2964 if param_key not in valid_parameter:
2965 warn(f"Unknown parameter '{param_key}' in conditional_format()")
2966 return -2
2968 # 'type' is a required parameter.
2969 if "type" not in options:
2970 warn("Parameter 'type' is required in conditional_format()")
2971 return -2
2973 # Valid types.
2974 valid_type = {
2975 "cell": "cellIs",
2976 "date": "date",
2977 "time": "time",
2978 "average": "aboveAverage",
2979 "duplicate": "duplicateValues",
2980 "unique": "uniqueValues",
2981 "top": "top10",
2982 "bottom": "top10",
2983 "text": "text",
2984 "time_period": "timePeriod",
2985 "blanks": "containsBlanks",
2986 "no_blanks": "notContainsBlanks",
2987 "errors": "containsErrors",
2988 "no_errors": "notContainsErrors",
2989 "2_color_scale": "2_color_scale",
2990 "3_color_scale": "3_color_scale",
2991 "data_bar": "dataBar",
2992 "formula": "expression",
2993 "icon_set": "iconSet",
2994 }
2996 # Check for valid types.
2997 if options["type"] not in valid_type:
2998 warn(
2999 f"Unknown value '{options['type']}' for parameter 'type' "
3000 f"in conditional_format()"
3001 )
3002 return -2
3004 if options["type"] == "bottom":
3005 options["direction"] = "bottom"
3006 options["type"] = valid_type[options["type"]]
3008 # Valid criteria types.
3009 criteria_type = {
3010 "between": "between",
3011 "not between": "notBetween",
3012 "equal to": "equal",
3013 "=": "equal",
3014 "==": "equal",
3015 "not equal to": "notEqual",
3016 "!=": "notEqual",
3017 "<>": "notEqual",
3018 "greater than": "greaterThan",
3019 ">": "greaterThan",
3020 "less than": "lessThan",
3021 "<": "lessThan",
3022 "greater than or equal to": "greaterThanOrEqual",
3023 ">=": "greaterThanOrEqual",
3024 "less than or equal to": "lessThanOrEqual",
3025 "<=": "lessThanOrEqual",
3026 "containing": "containsText",
3027 "not containing": "notContains",
3028 "begins with": "beginsWith",
3029 "ends with": "endsWith",
3030 "yesterday": "yesterday",
3031 "today": "today",
3032 "last 7 days": "last7Days",
3033 "last week": "lastWeek",
3034 "this week": "thisWeek",
3035 "next week": "nextWeek",
3036 "last month": "lastMonth",
3037 "this month": "thisMonth",
3038 "next month": "nextMonth",
3039 # For legacy, but incorrect, support.
3040 "continue week": "nextWeek",
3041 "continue month": "nextMonth",
3042 }
3044 # Check for valid criteria types.
3045 if "criteria" in options and options["criteria"] in criteria_type:
3046 options["criteria"] = criteria_type[options["criteria"]]
3048 # Convert boolean values if required.
3049 if "value" in options and isinstance(options["value"], bool):
3050 options["value"] = str(options["value"]).upper()
3052 # Convert date/times value if required.
3053 if options["type"] in ("date", "time"):
3054 options["type"] = "cellIs"
3056 if "value" in options:
3057 if not _supported_datetime(options["value"]):
3058 warn("Conditional format 'value' must be a datetime object.")
3059 return -2
3061 date_time = self._convert_date_time(options["value"])
3062 # Format date number to the same precision as Excel.
3063 options["value"] = f"{date_time:.16g}"
3065 if "minimum" in options:
3066 if not _supported_datetime(options["minimum"]):
3067 warn("Conditional format 'minimum' must be a datetime object.")
3068 return -2
3070 date_time = self._convert_date_time(options["minimum"])
3071 options["minimum"] = f"{date_time:.16g}"
3073 if "maximum" in options:
3074 if not _supported_datetime(options["maximum"]):
3075 warn("Conditional format 'maximum' must be a datetime object.")
3076 return -2
3078 date_time = self._convert_date_time(options["maximum"])
3079 options["maximum"] = f"{date_time:.16g}"
3081 # Valid icon styles.
3082 valid_icons = {
3083 "3_arrows": "3Arrows", # 1
3084 "3_flags": "3Flags", # 2
3085 "3_traffic_lights_rimmed": "3TrafficLights2", # 3
3086 "3_symbols_circled": "3Symbols", # 4
3087 "4_arrows": "4Arrows", # 5
3088 "4_red_to_black": "4RedToBlack", # 6
3089 "4_traffic_lights": "4TrafficLights", # 7
3090 "5_arrows_gray": "5ArrowsGray", # 8
3091 "5_quarters": "5Quarters", # 9
3092 "3_arrows_gray": "3ArrowsGray", # 10
3093 "3_traffic_lights": "3TrafficLights", # 11
3094 "3_signs": "3Signs", # 12
3095 "3_symbols": "3Symbols2", # 13
3096 "4_arrows_gray": "4ArrowsGray", # 14
3097 "4_ratings": "4Rating", # 15
3098 "5_arrows": "5Arrows", # 16
3099 "5_ratings": "5Rating",
3100 } # 17
3102 # Set the icon set properties.
3103 if options["type"] == "iconSet":
3104 # An icon_set must have an icon style.
3105 if not options.get("icon_style"):
3106 warn(
3107 "The 'icon_style' parameter must be specified when "
3108 "'type' == 'icon_set' in conditional_format()."
3109 )
3110 return -3
3112 # Check for valid icon styles.
3113 if options["icon_style"] not in valid_icons:
3114 warn(
3115 f"Unknown icon_style '{options['icon_style']}' "
3116 f"in conditional_format()."
3117 )
3118 return -2
3120 options["icon_style"] = valid_icons[options["icon_style"]]
3122 # Set the number of icons for the icon style.
3123 options["total_icons"] = 3
3124 if options["icon_style"].startswith("4"):
3125 options["total_icons"] = 4
3126 elif options["icon_style"].startswith("5"):
3127 options["total_icons"] = 5
3129 options["icons"] = self._set_icon_props(
3130 options.get("total_icons"), options.get("icons")
3131 )
3133 # Swap last row/col for first row/col as necessary
3134 if first_row > last_row:
3135 first_row, last_row = last_row, first_row
3137 if first_col > last_col:
3138 first_col, last_col = last_col, first_col
3140 # Set the formatting range.
3141 cell_range = xl_range(first_row, first_col, last_row, last_col)
3142 start_cell = xl_rowcol_to_cell(first_row, first_col)
3144 # Override with user defined multiple range if provided.
3145 if "multi_range" in options:
3146 cell_range = options["multi_range"]
3147 cell_range = cell_range.replace("$", "")
3149 # Get the dxf format index.
3150 if "format" in options and options["format"]:
3151 options["format"] = options["format"]._get_dxf_index()
3153 # Set the priority based on the order of adding.
3154 options["priority"] = self.dxf_priority
3155 self.dxf_priority += 1
3157 # Check for 2010 style data_bar parameters.
3158 # pylint: disable=too-many-boolean-expressions
3159 if (
3160 self.use_data_bars_2010
3161 or options.get("data_bar_2010")
3162 or options.get("bar_solid")
3163 or options.get("bar_border_color")
3164 or options.get("bar_negative_color")
3165 or options.get("bar_negative_color_same")
3166 or options.get("bar_negative_border_color")
3167 or options.get("bar_negative_border_color_same")
3168 or options.get("bar_no_border")
3169 or options.get("bar_axis_position")
3170 or options.get("bar_axis_color")
3171 or options.get("bar_direction")
3172 ):
3173 options["is_data_bar_2010"] = True
3175 # Special handling of text criteria.
3176 if options["type"] == "text":
3177 value = options["value"]
3178 length = len(value)
3179 criteria = options["criteria"]
3181 if options["criteria"] == "containsText":
3182 options["type"] = "containsText"
3183 options["formula"] = f'NOT(ISERROR(SEARCH("{value}",{start_cell})))'
3184 elif options["criteria"] == "notContains":
3185 options["type"] = "notContainsText"
3186 options["formula"] = f'ISERROR(SEARCH("{value}",{start_cell}))'
3187 elif options["criteria"] == "beginsWith":
3188 options["type"] = "beginsWith"
3189 options["formula"] = f'LEFT({start_cell},{length})="{value}"'
3190 elif options["criteria"] == "endsWith":
3191 options["type"] = "endsWith"
3192 options["formula"] = f'RIGHT({start_cell},{length})="{value}"'
3193 else:
3194 warn(f"Invalid text criteria '{criteria}' in conditional_format()")
3196 # Special handling of time time_period criteria.
3197 if options["type"] == "timePeriod":
3198 if options["criteria"] == "yesterday":
3199 options["formula"] = f"FLOOR({start_cell},1)=TODAY()-1"
3201 elif options["criteria"] == "today":
3202 options["formula"] = f"FLOOR({start_cell},1)=TODAY()"
3204 elif options["criteria"] == "tomorrow":
3205 options["formula"] = f"FLOOR({start_cell},1)=TODAY()+1"
3207 # fmt: off
3208 elif options["criteria"] == "last7Days":
3209 options["formula"] = (
3210 f"AND(TODAY()-FLOOR({start_cell},1)<=6,"
3211 f"FLOOR({start_cell},1)<=TODAY())"
3212 )
3213 # fmt: on
3215 elif options["criteria"] == "lastWeek":
3216 options["formula"] = (
3217 f"AND(TODAY()-ROUNDDOWN({start_cell},0)>=(WEEKDAY(TODAY())),"
3218 f"TODAY()-ROUNDDOWN({start_cell},0)<(WEEKDAY(TODAY())+7))"
3219 )
3221 elif options["criteria"] == "thisWeek":
3222 options["formula"] = (
3223 f"AND(TODAY()-ROUNDDOWN({start_cell},0)<=WEEKDAY(TODAY())-1,"
3224 f"ROUNDDOWN({start_cell},0)-TODAY()<=7-WEEKDAY(TODAY()))"
3225 )
3227 elif options["criteria"] == "nextWeek":
3228 options["formula"] = (
3229 f"AND(ROUNDDOWN({start_cell},0)-TODAY()>(7-WEEKDAY(TODAY())),"
3230 f"ROUNDDOWN({start_cell},0)-TODAY()<(15-WEEKDAY(TODAY())))"
3231 )
3233 elif options["criteria"] == "lastMonth":
3234 options["formula"] = (
3235 f"AND(MONTH({start_cell})=MONTH(TODAY())-1,"
3236 f"OR(YEAR({start_cell})=YEAR("
3237 f"TODAY()),AND(MONTH({start_cell})=1,YEAR(A1)=YEAR(TODAY())-1)))"
3238 )
3240 # fmt: off
3241 elif options["criteria"] == "thisMonth":
3242 options["formula"] = (
3243 f"AND(MONTH({start_cell})=MONTH(TODAY()),"
3244 f"YEAR({start_cell})=YEAR(TODAY()))"
3245 )
3246 # fmt: on
3248 elif options["criteria"] == "nextMonth":
3249 options["formula"] = (
3250 f"AND(MONTH({start_cell})=MONTH(TODAY())+1,"
3251 f"OR(YEAR({start_cell})=YEAR("
3252 f"TODAY()),AND(MONTH({start_cell})=12,"
3253 f"YEAR({start_cell})=YEAR(TODAY())+1)))"
3254 )
3256 else:
3257 warn(
3258 f"Invalid time_period criteria '{options['criteria']}' "
3259 f"in conditional_format()"
3260 )
3262 # Special handling of blanks/error types.
3263 if options["type"] == "containsBlanks":
3264 options["formula"] = f"LEN(TRIM({start_cell}))=0"
3266 if options["type"] == "notContainsBlanks":
3267 options["formula"] = f"LEN(TRIM({start_cell}))>0"
3269 if options["type"] == "containsErrors":
3270 options["formula"] = f"ISERROR({start_cell})"
3272 if options["type"] == "notContainsErrors":
3273 options["formula"] = f"NOT(ISERROR({start_cell}))"
3275 # Special handling for 2 color scale.
3276 if options["type"] == "2_color_scale":
3277 options["type"] = "colorScale"
3279 # Color scales don't use any additional formatting.
3280 options["format"] = None
3282 # Turn off 3 color parameters.
3283 options["mid_type"] = None
3284 options["mid_color"] = None
3286 options.setdefault("min_type", "min")
3287 options.setdefault("max_type", "max")
3288 options.setdefault("min_value", 0)
3289 options.setdefault("max_value", 0)
3290 options.setdefault("min_color", Color("#FF7128"))
3291 options.setdefault("max_color", Color("#FFEF9C"))
3293 options["min_color"] = Color._from_value(options["min_color"])
3294 options["max_color"] = Color._from_value(options["max_color"])
3296 # Special handling for 3 color scale.
3297 if options["type"] == "3_color_scale":
3298 options["type"] = "colorScale"
3300 # Color scales don't use any additional formatting.
3301 options["format"] = None
3303 options.setdefault("min_type", "min")
3304 options.setdefault("mid_type", "percentile")
3305 options.setdefault("max_type", "max")
3306 options.setdefault("min_value", 0)
3307 options.setdefault("max_value", 0)
3308 options.setdefault("min_color", Color("#F8696B"))
3309 options.setdefault("mid_color", Color("#FFEB84"))
3310 options.setdefault("max_color", Color("#63BE7B"))
3312 options["min_color"] = Color._from_value(options["min_color"])
3313 options["mid_color"] = Color._from_value(options["mid_color"])
3314 options["max_color"] = Color._from_value(options["max_color"])
3316 # Set a default mid value.
3317 if "mid_value" not in options:
3318 options["mid_value"] = 50
3320 # Special handling for data bar.
3321 if options["type"] == "dataBar":
3322 # Color scales don't use any additional formatting.
3323 options["format"] = None
3325 if not options.get("min_type"):
3326 options["min_type"] = "min"
3327 options["x14_min_type"] = "autoMin"
3328 else:
3329 options["x14_min_type"] = options["min_type"]
3331 if not options.get("max_type"):
3332 options["max_type"] = "max"
3333 options["x14_max_type"] = "autoMax"
3334 else:
3335 options["x14_max_type"] = options["max_type"]
3337 options.setdefault("min_value", 0)
3338 options.setdefault("max_value", 0)
3339 options.setdefault("bar_color", Color("#638EC6"))
3340 options.setdefault("bar_border_color", options["bar_color"])
3341 options.setdefault("bar_only", False)
3342 options.setdefault("bar_no_border", False)
3343 options.setdefault("bar_solid", False)
3344 options.setdefault("bar_direction", "")
3345 options.setdefault("bar_negative_color", Color("#FF0000"))
3346 options.setdefault("bar_negative_border_color", Color("#FF0000"))
3347 options.setdefault("bar_negative_color_same", False)
3348 options.setdefault("bar_negative_border_color_same", False)
3349 options.setdefault("bar_axis_position", "")
3350 options.setdefault("bar_axis_color", Color("#000000"))
3352 options["bar_color"] = Color._from_value(options["bar_color"])
3353 options["bar_border_color"] = Color._from_value(options["bar_border_color"])
3354 options["bar_axis_color"] = Color._from_value(options["bar_axis_color"])
3355 options["bar_negative_color"] = Color._from_value(
3356 options["bar_negative_color"]
3357 )
3358 options["bar_negative_border_color"] = Color._from_value(
3359 options["bar_negative_border_color"]
3360 )
3362 # Adjust for 2010 style data_bar parameters.
3363 if options.get("is_data_bar_2010"):
3364 self.excel_version = 2010
3366 if options["min_type"] == "min" and options["min_value"] == 0:
3367 options["min_value"] = None
3369 if options["max_type"] == "max" and options["max_value"] == 0:
3370 options["max_value"] = None
3372 options["range"] = cell_range
3374 # Strip the leading = from formulas.
3375 try:
3376 options["min_value"] = options["min_value"].lstrip("=")
3377 except (KeyError, AttributeError):
3378 pass
3379 try:
3380 options["mid_value"] = options["mid_value"].lstrip("=")
3381 except (KeyError, AttributeError):
3382 pass
3383 try:
3384 options["max_value"] = options["max_value"].lstrip("=")
3385 except (KeyError, AttributeError):
3386 pass
3388 # Store the conditional format until we close the worksheet.
3389 if cell_range in self.cond_formats:
3390 self.cond_formats[cell_range].append(options)
3391 else:
3392 self.cond_formats[cell_range] = [options]
3394 return 0
3396 @convert_range_args
3397 def add_table(
3398 self,
3399 first_row: int,
3400 first_col: int,
3401 last_row: int,
3402 last_col: int,
3403 options: Optional[Dict[str, Any]] = None,
3404 ) -> Literal[0, -1, -2, -3]:
3405 """
3406 Add an Excel table to a worksheet.
3408 Args:
3409 first_row: The first row of the cell range. (zero indexed).
3410 first_col: The first column of the cell range.
3411 last_row: The last row of the cell range. (zero indexed).
3412 last_col: The last column of the cell range.
3413 options: Table format options. (Optional)
3415 Returns:
3416 0: Success.
3417 -1: Row or column is out of worksheet bounds.
3418 -2: Incorrect parameter or option.
3419 -3: Not supported in constant_memory mode.
3420 """
3421 table = {}
3422 col_formats = {}
3424 if options is None:
3425 options = {}
3426 else:
3427 # Copy the user defined options so they aren't modified.
3428 options = options.copy()
3430 if self.constant_memory:
3431 warn("add_table() isn't supported in 'constant_memory' mode")
3432 return -3
3434 # Check that row and col are valid without storing the values.
3435 if self._check_dimensions(first_row, first_col, True, True):
3436 return -1
3437 if self._check_dimensions(last_row, last_col, True, True):
3438 return -1
3440 # Swap last row/col for first row/col as necessary.
3441 if first_row > last_row:
3442 (first_row, last_row) = (last_row, first_row)
3443 if first_col > last_col:
3444 (first_col, last_col) = (last_col, first_col)
3446 # Check if the table range overlaps a previous merged or table range.
3447 # This is a critical file corruption error in Excel.
3448 cell_range = xl_range(first_row, first_col, last_row, last_col)
3449 for row in range(first_row, last_row + 1):
3450 for col in range(first_col, last_col + 1):
3451 if self.table_cells.get((row, col)):
3452 previous_range = self.table_cells.get((row, col))
3453 raise OverlappingRange(
3454 f"Table range '{cell_range}' overlaps previous "
3455 f"table range '{previous_range}'."
3456 )
3458 if self.merged_cells.get((row, col)):
3459 previous_range = self.merged_cells.get((row, col))
3460 raise OverlappingRange(
3461 f"Table range '{cell_range}' overlaps previous "
3462 f"merge range '{previous_range}'."
3463 )
3465 self.table_cells[(row, col)] = cell_range
3467 # Valid input parameters.
3468 valid_parameter = {
3469 "autofilter",
3470 "banded_columns",
3471 "banded_rows",
3472 "columns",
3473 "data",
3474 "first_column",
3475 "header_row",
3476 "last_column",
3477 "name",
3478 "style",
3479 "total_row",
3480 "description",
3481 "title",
3482 }
3484 # Check for valid input parameters.
3485 for param_key in options.keys():
3486 if param_key not in valid_parameter:
3487 warn(f"Unknown parameter '{param_key}' in add_table()")
3488 return -2
3490 # Turn on Excel's defaults.
3491 options["banded_rows"] = options.get("banded_rows", True)
3492 options["header_row"] = options.get("header_row", True)
3493 options["autofilter"] = options.get("autofilter", True)
3495 # Check that there are enough rows.
3496 num_rows = last_row - first_row
3497 if options["header_row"]:
3498 num_rows -= 1
3500 if num_rows < 0:
3501 warn("Must have at least one data row in in add_table()")
3502 return -2
3504 # Set the table options.
3505 table["show_first_col"] = options.get("first_column", False)
3506 table["show_last_col"] = options.get("last_column", False)
3507 table["show_row_stripes"] = options.get("banded_rows", False)
3508 table["show_col_stripes"] = options.get("banded_columns", False)
3509 table["header_row_count"] = options.get("header_row", 0)
3510 table["totals_row_shown"] = options.get("total_row", False)
3511 table["description"] = options.get("description")
3512 table["title"] = options.get("title")
3514 # Set the table name.
3515 if "name" in options:
3516 name = options["name"]
3517 table["name"] = name
3519 if " " in name:
3520 warn(f"Name '{name}' in add_table() cannot contain spaces")
3521 return -2
3523 # Warn if the name contains invalid chars as defined by Excel.
3524 if not re.match(r"^[\w\\][\w\\.]*$", name, re.UNICODE) or re.match(
3525 r"^\d", name
3526 ):
3527 warn(f"Invalid Excel characters in add_table(): '{name}'")
3528 return -2
3530 # Warn if the name looks like a cell name.
3531 if re.match(r"^[a-zA-Z][a-zA-Z]?[a-dA-D]?\d+$", name):
3532 warn(f"Name looks like a cell name in add_table(): '{name}'")
3533 return -2
3535 # Warn if the name looks like a R1C1 cell reference.
3536 if re.match(r"^[rcRC]$", name) or re.match(r"^[rcRC]\d+[rcRC]\d+$", name):
3537 warn(f"Invalid name '{name}' like a RC cell ref in add_table()")
3538 return -2
3540 # Set the table style.
3541 if "style" in options:
3542 table["style"] = options["style"]
3544 if table["style"] is None:
3545 table["style"] = ""
3547 # Remove whitespace from style name.
3548 table["style"] = table["style"].replace(" ", "")
3549 else:
3550 table["style"] = "TableStyleMedium9"
3552 # Set the data range rows (without the header and footer).
3553 first_data_row = first_row
3554 last_data_row = last_row
3556 if options.get("header_row"):
3557 first_data_row += 1
3559 if options.get("total_row"):
3560 last_data_row -= 1
3562 # Set the table and autofilter ranges.
3563 table["range"] = xl_range(first_row, first_col, last_row, last_col)
3565 table["a_range"] = xl_range(first_row, first_col, last_data_row, last_col)
3567 # If the header row if off the default is to turn autofilter off.
3568 if not options["header_row"]:
3569 options["autofilter"] = 0
3571 # Set the autofilter range.
3572 if options["autofilter"]:
3573 table["autofilter"] = table["a_range"]
3575 # Add the table columns.
3576 col_id = 1
3577 table["columns"] = []
3578 seen_names = {}
3580 for col_num in range(first_col, last_col + 1):
3581 # Set up the default column data.
3582 col_data = {
3583 "id": col_id,
3584 "name": "Column" + str(col_id),
3585 "total_string": "",
3586 "total_function": "",
3587 "custom_total": "",
3588 "total_value": 0,
3589 "formula": "",
3590 "format": None,
3591 "name_format": None,
3592 }
3594 # Overwrite the defaults with any user defined values.
3595 if "columns" in options:
3596 # Check if there are user defined values for this column.
3597 if col_id <= len(options["columns"]):
3598 user_data = options["columns"][col_id - 1]
3599 else:
3600 user_data = None
3602 if user_data:
3603 # Get the column format.
3604 xformat = user_data.get("format", None)
3606 # Map user defined values to internal values.
3607 if user_data.get("header"):
3608 col_data["name"] = user_data["header"]
3610 # Excel requires unique case insensitive header names.
3611 header_name = col_data["name"]
3612 name = header_name.lower()
3613 if name in seen_names:
3614 warn(f"Duplicate header name in add_table(): '{name}'")
3615 return -2
3617 seen_names[name] = True
3619 col_data["name_format"] = user_data.get("header_format")
3621 # Handle the column formula.
3622 if "formula" in user_data and user_data["formula"]:
3623 formula = user_data["formula"]
3625 # Remove the formula '=' sign if it exists.
3626 if formula.startswith("="):
3627 formula = formula.lstrip("=")
3629 # Convert Excel 2010 "@" ref to 2007 "#This Row".
3630 formula = self._prepare_table_formula(formula)
3632 # Escape any future functions.
3633 formula = self._prepare_formula(formula, True)
3635 col_data["formula"] = formula
3636 # We write the formulas below after the table data.
3638 # Handle the function for the total row.
3639 if user_data.get("total_function"):
3640 function = user_data["total_function"]
3641 if function == "count_nums":
3642 function = "countNums"
3643 if function == "std_dev":
3644 function = "stdDev"
3646 subtotals = set(
3647 [
3648 "average",
3649 "countNums",
3650 "count",
3651 "max",
3652 "min",
3653 "stdDev",
3654 "sum",
3655 "var",
3656 ]
3657 )
3659 if function in subtotals:
3660 formula = self._table_function_to_formula(
3661 function, col_data["name"]
3662 )
3663 else:
3664 formula = self._prepare_formula(function, True)
3665 col_data["custom_total"] = formula
3666 function = "custom"
3668 col_data["total_function"] = function
3670 value = user_data.get("total_value", 0)
3672 self._write_formula(last_row, col_num, formula, xformat, value)
3674 elif user_data.get("total_string"):
3675 # Total label only (not a function).
3676 total_string = user_data["total_string"]
3677 col_data["total_string"] = total_string
3679 self._write_string(
3680 last_row, col_num, total_string, user_data.get("format")
3681 )
3683 # Get the dxf format index.
3684 if xformat is not None:
3685 col_data["format"] = xformat._get_dxf_index()
3687 # Store the column format for writing the cell data.
3688 # It doesn't matter if it is undefined.
3689 col_formats[col_id - 1] = xformat
3691 # Store the column data.
3692 table["columns"].append(col_data)
3694 # Write the column headers to the worksheet.
3695 if options["header_row"]:
3696 self._write_string(
3697 first_row, col_num, col_data["name"], col_data["name_format"]
3698 )
3700 col_id += 1
3702 # Write the cell data if supplied.
3703 if "data" in options:
3704 data = options["data"]
3706 i = 0 # For indexing the row data.
3707 for row in range(first_data_row, last_data_row + 1):
3708 j = 0 # For indexing the col data.
3709 for col in range(first_col, last_col + 1):
3710 if i < len(data) and j < len(data[i]):
3711 token = data[i][j]
3712 if j in col_formats:
3713 self._write(row, col, token, col_formats[j])
3714 else:
3715 self._write(row, col, token, None)
3716 j += 1
3717 i += 1
3719 # Write any columns formulas after the user supplied table data to
3720 # overwrite it if required.
3721 for col_id, col_num in enumerate(range(first_col, last_col + 1)):
3722 column_data = table["columns"][col_id]
3723 if column_data and column_data["formula"]:
3724 formula_format = col_formats.get(col_id)
3725 formula = column_data["formula"]
3727 for row in range(first_data_row, last_data_row + 1):
3728 self._write_formula(row, col_num, formula, formula_format)
3730 # Store the table data.
3731 self.tables.append(table)
3733 # Store the filter cell positions for use in the autofit calculation.
3734 if options["autofilter"]:
3735 for col in range(first_col, last_col + 1):
3736 # Check that the table autofilter doesn't overlap a worksheet filter.
3737 if self.filter_cells.get((first_row, col)):
3738 filter_type, filter_range = self.filter_cells.get((first_row, col))
3739 if filter_type == "worksheet":
3740 raise OverlappingRange(
3741 f"Table autofilter range '{cell_range}' overlaps previous "
3742 f"Worksheet autofilter range '{filter_range}'."
3743 )
3745 self.filter_cells[(first_row, col)] = ("table", cell_range)
3747 return 0
3749 @convert_cell_args
3750 def add_sparkline(
3751 self, row: int, col: int, options: Optional[Dict[str, Any]] = None
3752 ) -> Literal[0, -1, -2]:
3753 """
3754 Add sparklines to the worksheet.
3756 Args:
3757 row: The cell row (zero indexed).
3758 col: The cell column (zero indexed).
3759 options: Sparkline formatting options.
3761 Returns:
3762 0: Success.
3763 -1: Row or column is out of worksheet bounds.
3764 -2: Incorrect parameter or option.
3766 """
3768 # Check that row and col are valid without storing the values.
3769 if self._check_dimensions(row, col, True, True):
3770 return -1
3772 sparkline = {"locations": [xl_rowcol_to_cell(row, col)]}
3774 if options is None:
3775 options = {}
3777 # Valid input parameters.
3778 valid_parameters = {
3779 "location",
3780 "range",
3781 "type",
3782 "high_point",
3783 "low_point",
3784 "negative_points",
3785 "first_point",
3786 "last_point",
3787 "markers",
3788 "style",
3789 "series_color",
3790 "negative_color",
3791 "markers_color",
3792 "first_color",
3793 "last_color",
3794 "high_color",
3795 "low_color",
3796 "max",
3797 "min",
3798 "axis",
3799 "reverse",
3800 "empty_cells",
3801 "show_hidden",
3802 "plot_hidden",
3803 "date_axis",
3804 "weight",
3805 }
3807 # Check for valid input parameters.
3808 for param_key in options.keys():
3809 if param_key not in valid_parameters:
3810 warn(f"Unknown parameter '{param_key}' in add_sparkline()")
3811 return -1
3813 # 'range' is a required parameter.
3814 if "range" not in options:
3815 warn("Parameter 'range' is required in add_sparkline()")
3816 return -2
3818 # Handle the sparkline type.
3819 spark_type = options.get("type", "line")
3821 if spark_type not in ("line", "column", "win_loss"):
3822 warn(
3823 "Parameter 'type' must be 'line', 'column' "
3824 "or 'win_loss' in add_sparkline()"
3825 )
3826 return -2
3828 if spark_type == "win_loss":
3829 spark_type = "stacked"
3830 sparkline["type"] = spark_type
3832 # We handle single location/range values or list of values.
3833 if "location" in options:
3834 if isinstance(options["location"], list):
3835 sparkline["locations"] = options["location"]
3836 else:
3837 sparkline["locations"] = [options["location"]]
3839 if isinstance(options["range"], list):
3840 sparkline["ranges"] = options["range"]
3841 else:
3842 sparkline["ranges"] = [options["range"]]
3844 range_count = len(sparkline["ranges"])
3845 location_count = len(sparkline["locations"])
3847 # The ranges and locations must match.
3848 if range_count != location_count:
3849 warn(
3850 "Must have the same number of location and range "
3851 "parameters in add_sparkline()"
3852 )
3853 return -2
3855 # Store the count.
3856 sparkline["count"] = len(sparkline["locations"])
3858 # Get the worksheet name for the range conversion below.
3859 sheetname = quote_sheetname(self.name)
3861 # Cleanup the input ranges.
3862 new_ranges = []
3863 for spark_range in sparkline["ranges"]:
3864 # Remove the absolute reference $ symbols.
3865 spark_range = spark_range.replace("$", "")
3867 # Remove the = from formula.
3868 spark_range = spark_range.lstrip("=")
3870 # Convert a simple range into a full Sheet1!A1:D1 range.
3871 if "!" not in spark_range:
3872 spark_range = sheetname + "!" + spark_range
3874 new_ranges.append(spark_range)
3876 sparkline["ranges"] = new_ranges
3878 # Cleanup the input locations.
3879 new_locations = []
3880 for location in sparkline["locations"]:
3881 location = location.replace("$", "")
3882 new_locations.append(location)
3884 sparkline["locations"] = new_locations
3886 # Map options.
3887 sparkline["high"] = options.get("high_point")
3888 sparkline["low"] = options.get("low_point")
3889 sparkline["negative"] = options.get("negative_points")
3890 sparkline["first"] = options.get("first_point")
3891 sparkline["last"] = options.get("last_point")
3892 sparkline["markers"] = options.get("markers")
3893 sparkline["min"] = options.get("min")
3894 sparkline["max"] = options.get("max")
3895 sparkline["axis"] = options.get("axis")
3896 sparkline["reverse"] = options.get("reverse")
3897 sparkline["hidden"] = options.get("show_hidden")
3898 sparkline["weight"] = options.get("weight")
3900 # Map empty cells options.
3901 empty = options.get("empty_cells", "")
3903 if empty == "zero":
3904 sparkline["empty"] = 0
3905 elif empty == "connect":
3906 sparkline["empty"] = "span"
3907 else:
3908 sparkline["empty"] = "gap"
3910 # Map the date axis range.
3911 date_range = options.get("date_axis")
3913 if date_range and "!" not in date_range:
3914 date_range = sheetname + "!" + date_range
3916 sparkline["date_axis"] = date_range
3918 # Set the sparkline styles.
3919 style_id = options.get("style", 0)
3920 style = _get_sparkline_style(style_id)
3922 sparkline["series_color"] = style["series"]
3923 sparkline["negative_color"] = style["negative"]
3924 sparkline["markers_color"] = style["markers"]
3925 sparkline["first_color"] = style["first"]
3926 sparkline["last_color"] = style["last"]
3927 sparkline["high_color"] = style["high"]
3928 sparkline["low_color"] = style["low"]
3930 # Override the style colors with user defined colors.
3931 self._set_spark_color(sparkline, options, "series_color")
3932 self._set_spark_color(sparkline, options, "negative_color")
3933 self._set_spark_color(sparkline, options, "markers_color")
3934 self._set_spark_color(sparkline, options, "first_color")
3935 self._set_spark_color(sparkline, options, "last_color")
3936 self._set_spark_color(sparkline, options, "high_color")
3937 self._set_spark_color(sparkline, options, "low_color")
3939 self.sparklines.append(sparkline)
3941 return 0
3943 @convert_range_args
3944 def set_selection(
3945 self, first_row: int, first_col: int, last_row: int, last_col: int
3946 ) -> None:
3947 """
3948 Set the selected cell or cells in a worksheet
3950 Args:
3951 first_row: The first row of the cell range. (zero indexed).
3952 first_col: The first column of the cell range.
3953 last_row: The last row of the cell range. (zero indexed).
3954 last_col: The last column of the cell range.
3956 Returns:
3957 0: Nothing.
3958 """
3959 pane = None
3961 # Range selection. Do this before swapping max/min to allow the
3962 # selection direction to be reversed.
3963 active_cell = xl_rowcol_to_cell(first_row, first_col)
3965 # Swap last row/col for first row/col if necessary
3966 if first_row > last_row:
3967 (first_row, last_row) = (last_row, first_row)
3969 if first_col > last_col:
3970 (first_col, last_col) = (last_col, first_col)
3972 sqref = xl_range(first_row, first_col, last_row, last_col)
3974 # Selection isn't set for cell A1.
3975 if sqref == "A1":
3976 return
3978 self.selections = [[pane, active_cell, sqref]]
3980 @convert_cell_args
3981 def set_top_left_cell(self, row: int = 0, col: int = 0) -> None:
3982 """
3983 Set the first visible cell at the top left of a worksheet.
3985 Args:
3986 row: The cell row (zero indexed).
3987 col: The cell column (zero indexed).
3989 Returns:
3990 0: Nothing.
3991 """
3993 if row == 0 and col == 0:
3994 return
3996 self.top_left_cell = xl_rowcol_to_cell(row, col)
3998 def outline_settings(
3999 self,
4000 visible: bool = 1,
4001 symbols_below: bool = 1,
4002 symbols_right: bool = 1,
4003 auto_style: bool = 0,
4004 ) -> None:
4005 """
4006 Control outline settings.
4008 Args:
4009 visible: Outlines are visible. Optional, defaults to True.
4010 symbols_below: Show row outline symbols below the outline bar.
4011 Optional, defaults to True.
4012 symbols_right: Show column outline symbols to the right of the
4013 outline bar. Optional, defaults to True.
4014 auto_style: Use Automatic style. Optional, defaults to False.
4016 Returns:
4017 0: Nothing.
4018 """
4019 self.outline_on = visible
4020 self.outline_below = symbols_below
4021 self.outline_right = symbols_right
4022 self.outline_style = auto_style
4024 self.outline_changed = True
4026 @convert_cell_args
4027 def freeze_panes(
4028 self,
4029 row: int,
4030 col: int,
4031 top_row: Optional[int] = None,
4032 left_col: Optional[int] = None,
4033 pane_type: int = 0,
4034 ) -> None:
4035 """
4036 Create worksheet panes and mark them as frozen.
4038 Args:
4039 row: The cell row (zero indexed).
4040 col: The cell column (zero indexed).
4041 top_row: Topmost visible row in scrolling region of pane.
4042 left_col: Leftmost visible row in scrolling region of pane.
4044 Returns:
4045 0: Nothing.
4047 """
4048 if top_row is None:
4049 top_row = row
4051 if left_col is None:
4052 left_col = col
4054 self.panes = [row, col, top_row, left_col, pane_type]
4056 @convert_cell_args
4057 def split_panes(
4058 self,
4059 x: float,
4060 y: float,
4061 top_row: Optional[int] = None,
4062 left_col: Optional[int] = None,
4063 ) -> None:
4064 """
4065 Create worksheet panes and mark them as split.
4067 Args:
4068 x: The position for the vertical split.
4069 y: The position for the horizontal split.
4070 top_row: Topmost visible row in scrolling region of pane.
4071 left_col: Leftmost visible row in scrolling region of pane.
4073 Returns:
4074 0: Nothing.
4076 """
4077 # Same as freeze panes with a different pane type.
4078 self.freeze_panes(x, y, top_row, left_col, 2)
4080 def set_zoom(self, zoom: int = 100) -> None:
4081 """
4082 Set the worksheet zoom factor.
4084 Args:
4085 zoom: Scale factor: 10 <= zoom <= 400.
4087 Returns:
4088 Nothing.
4090 """
4091 # Ensure the zoom scale is in Excel's range.
4092 if zoom < 10 or zoom > 400:
4093 warn(f"Zoom factor '{zoom}' outside range: 10 <= zoom <= 400")
4094 zoom = 100
4096 self.zoom = int(zoom)
4098 def set_zoom_to_fit(self) -> None:
4099 """
4100 Set the worksheet zoom to selection/fit. Only works for chartsheets.
4102 Args:
4103 None.
4105 Returns:
4106 Nothing.
4108 """
4109 self.zoom_to_fit = True
4111 def right_to_left(self) -> None:
4112 """
4113 Display the worksheet right to left for some versions of Excel.
4115 Args:
4116 None.
4118 Returns:
4119 Nothing.
4121 """
4122 self.is_right_to_left = True
4124 def hide_zero(self) -> None:
4125 """
4126 Hide zero values in worksheet cells.
4128 Args:
4129 None.
4131 Returns:
4132 Nothing.
4134 """
4135 self.show_zeros = 0
4137 def set_tab_color(self, color: Union[str, Color]) -> None:
4138 """
4139 Set the color of the worksheet tab.
4141 Args:
4142 color: A #RGB color index.
4144 Returns:
4145 Nothing.
4147 """
4148 self.tab_color = Color._from_value(color)
4150 def protect(
4151 self, password: str = "", options: Optional[Dict[str, Any]] = None
4152 ) -> None:
4153 """
4154 Set the password and protection options of the worksheet.
4156 Args:
4157 password: An optional password string.
4158 options: A dictionary of worksheet objects to protect.
4160 Returns:
4161 Nothing.
4163 """
4164 if password != "":
4165 password = self._encode_password(password)
4167 if not options:
4168 options = {}
4170 # Default values for objects that can be protected.
4171 defaults = {
4172 "sheet": True,
4173 "content": False,
4174 "objects": False,
4175 "scenarios": False,
4176 "format_cells": False,
4177 "format_columns": False,
4178 "format_rows": False,
4179 "insert_columns": False,
4180 "insert_rows": False,
4181 "insert_hyperlinks": False,
4182 "delete_columns": False,
4183 "delete_rows": False,
4184 "select_locked_cells": True,
4185 "sort": False,
4186 "autofilter": False,
4187 "pivot_tables": False,
4188 "select_unlocked_cells": True,
4189 }
4191 # Overwrite the defaults with user specified values.
4192 for key in options.keys():
4193 if key in defaults:
4194 defaults[key] = options[key]
4195 else:
4196 warn(f"Unknown protection object: '{key}'")
4198 # Set the password after the user defined values.
4199 defaults["password"] = password
4201 self.protect_options = defaults
4203 def unprotect_range(
4204 self,
4205 cell_range: str,
4206 range_name: Optional[str] = None,
4207 password: Optional[str] = None,
4208 ) -> int:
4209 """
4210 Unprotect ranges within a protected worksheet.
4212 Args:
4213 cell_range: The cell or cell range to unprotect.
4214 range_name: An optional name for the range.
4215 password: An optional password string. (undocumented)
4217 Returns:
4218 0: Success.
4219 -1: Parameter error.
4221 """
4222 if cell_range is None:
4223 warn("Cell range must be specified in unprotect_range()")
4224 return -1
4226 # Sanitize the cell range.
4227 cell_range = cell_range.lstrip("=")
4228 cell_range = cell_range.replace("$", "")
4230 self.num_protected_ranges += 1
4232 if range_name is None:
4233 range_name = "Range" + str(self.num_protected_ranges)
4235 if password:
4236 password = self._encode_password(password)
4238 self.protected_ranges.append((cell_range, range_name, password))
4240 return 0
4242 @convert_cell_args
4243 def insert_button(
4244 self, row: int, col: int, options: Optional[Dict[str, Any]] = None
4245 ) -> Literal[0, -1]:
4246 """
4247 Insert a button form object into the worksheet.
4249 Args:
4250 row: The cell row (zero indexed).
4251 col: The cell column (zero indexed).
4252 options: Button formatting options.
4254 Returns:
4255 0: Success.
4256 -1: Row or column is out of worksheet bounds.
4258 """
4259 # Check insert (row, col) without storing.
4260 if self._check_dimensions(row, col, True, True):
4261 warn(f"Cannot insert button at ({row}, {col}).")
4262 return -1
4264 if options is None:
4265 options = {}
4267 # Create a new button object.
4268 height = self.default_row_pixels
4269 width = self.default_col_pixels
4270 button_number = 1 + len(self.buttons_list)
4272 button = ButtonType(row, col, height, width, button_number, options)
4274 self.buttons_list.append(button)
4276 self.has_vml = True
4278 return 0
4280 @convert_cell_args
4281 def insert_checkbox(
4282 self, row: int, col: int, boolean: bool, cell_format: Optional[Format] = None
4283 ):
4284 """
4285 Insert a boolean checkbox in a worksheet cell.
4287 Args:
4288 row: The cell row (zero indexed).
4289 col: The cell column (zero indexed).
4290 boolean: The boolean value to display as a checkbox.
4291 cell_format: Cell Format object. (optional)
4293 Returns:
4294 0: Success.
4295 -1: Row or column is out of worksheet bounds.
4297 """
4298 # Ensure that the checkbox property is set in the user defined format.
4299 if cell_format and not cell_format.checkbox:
4300 # This needs to be fixed with a clone.
4301 cell_format.set_checkbox()
4303 # If no format is supplied create and/or use the default checkbox format.
4304 if not cell_format:
4305 if not self.default_checkbox_format:
4306 self.default_checkbox_format = self.workbook_add_format()
4307 self.default_checkbox_format.set_checkbox()
4309 cell_format = self.default_checkbox_format
4311 return self._write_boolean(row, col, boolean, cell_format)
4313 ###########################################################################
4314 #
4315 # Public API. Page Setup methods.
4316 #
4317 ###########################################################################
4318 def set_landscape(self) -> None:
4319 """
4320 Set the page orientation as landscape.
4322 Args:
4323 None.
4325 Returns:
4326 Nothing.
4328 """
4329 self.orientation = 0
4330 self.page_setup_changed = True
4332 def set_portrait(self) -> None:
4333 """
4334 Set the page orientation as portrait.
4336 Args:
4337 None.
4339 Returns:
4340 Nothing.
4342 """
4343 self.orientation = 1
4344 self.page_setup_changed = True
4346 def set_page_view(self, view: Literal[0, 1, 2] = 1) -> None:
4347 """
4348 Set the page view mode.
4350 Args:
4351 0: Normal view mode
4352 1: Page view mode (the default)
4353 2: Page break view mode
4355 Returns:
4356 Nothing.
4358 """
4359 self.page_view = view
4361 def set_pagebreak_view(self) -> None:
4362 """
4363 Set the page view mode.
4365 Args:
4366 None.
4368 Returns:
4369 Nothing.
4371 """
4372 self.page_view = 2
4374 def set_paper(self, paper_size: Union[Literal[1, 9], int]) -> None:
4375 """
4376 Set the paper type. US Letter = 1, A4 = 9.
4378 Args:
4379 paper_size: Paper index.
4381 Returns:
4382 Nothing.
4384 """
4385 if paper_size:
4386 self.paper_size = paper_size
4387 self.page_setup_changed = True
4389 def center_horizontally(self) -> None:
4390 """
4391 Center the page horizontally.
4393 Args:
4394 None.
4396 Returns:
4397 Nothing.
4399 """
4400 self.print_options_changed = True
4401 self.hcenter = 1
4403 def center_vertically(self) -> None:
4404 """
4405 Center the page vertically.
4407 Args:
4408 None.
4410 Returns:
4411 Nothing.
4413 """
4414 self.print_options_changed = True
4415 self.vcenter = 1
4417 def set_margins(
4418 self,
4419 left: float = 0.7,
4420 right: float = 0.7,
4421 top: float = 0.75,
4422 bottom: float = 0.75,
4423 ) -> None:
4424 """
4425 Set all the page margins in inches.
4427 Args:
4428 left: Left margin.
4429 right: Right margin.
4430 top: Top margin.
4431 bottom: Bottom margin.
4433 Returns:
4434 Nothing.
4436 """
4437 self.margin_left = left
4438 self.margin_right = right
4439 self.margin_top = top
4440 self.margin_bottom = bottom
4442 def set_header(
4443 self, header: str = "", options: Optional[Dict[str, Any]] = None, margin=None
4444 ) -> None:
4445 """
4446 Set the page header caption and optional margin.
4448 Args:
4449 header: Header string.
4450 margin: Header margin.
4451 options: Header options, mainly for images.
4453 Returns:
4454 Nothing.
4456 """
4457 header_orig = header
4458 header = header.replace("&[Picture]", "&G")
4460 if len(header) > 255:
4461 warn("Header string cannot be longer than Excel's limit of 255 characters")
4462 return
4464 if options is not None:
4465 # For backward compatibility allow options to be the margin.
4466 if not isinstance(options, dict):
4467 options = {"margin": options}
4468 else:
4469 options = {}
4471 # Copy the user defined options so they aren't modified.
4472 options = options.copy()
4474 # For backward compatibility.
4475 if margin is not None:
4476 options["margin"] = margin
4478 # Reset the list in case the function is called more than once.
4479 self.header_images = []
4481 if options.get("image_left"):
4482 options["image_data"] = options.get("image_data_left")
4483 image = self._image_from_source(options.get("image_left"), options)
4484 image._header_position = "LH"
4485 self.header_images.append(image)
4487 if options.get("image_center"):
4488 options["image_data"] = options.get("image_data_center")
4489 image = self._image_from_source(options.get("image_center"), options)
4490 image._header_position = "CH"
4491 self.header_images.append(image)
4493 if options.get("image_right"):
4494 options["image_data"] = options.get("image_data_right")
4495 image = self._image_from_source(options.get("image_right"), options)
4496 image._header_position = "RH"
4497 self.header_images.append(image)
4499 placeholder_count = header.count("&G")
4500 image_count = len(self.header_images)
4502 if placeholder_count != image_count:
4503 warn(
4504 f"Number of footer images '{image_count}' doesn't match placeholder "
4505 f"count '{placeholder_count}' in string: {header_orig}"
4506 )
4507 self.header_images = []
4508 return
4510 if "align_with_margins" in options:
4511 self.header_footer_aligns = options["align_with_margins"]
4513 if "scale_with_doc" in options:
4514 self.header_footer_scales = options["scale_with_doc"]
4516 self.header = header
4517 self.margin_header = options.get("margin", 0.3)
4518 self.header_footer_changed = True
4520 if image_count:
4521 self.has_header_vml = True
4523 def set_footer(
4524 self, footer: str = "", options: Optional[Dict[str, Any]] = None, margin=None
4525 ) -> None:
4526 """
4527 Set the page footer caption and optional margin.
4529 Args:
4530 footer: Footer string.
4531 margin: Footer margin.
4532 options: Footer options, mainly for images.
4534 Returns:
4535 Nothing.
4537 """
4538 footer_orig = footer
4539 footer = footer.replace("&[Picture]", "&G")
4541 if len(footer) > 255:
4542 warn("Footer string cannot be longer than Excel's limit of 255 characters")
4543 return
4545 if options is not None:
4546 # For backward compatibility allow options to be the margin.
4547 if not isinstance(options, dict):
4548 options = {"margin": options}
4549 else:
4550 options = {}
4552 # Copy the user defined options so they aren't modified.
4553 options = options.copy()
4555 # For backward compatibility.
4556 if margin is not None:
4557 options["margin"] = margin
4559 # Reset the list in case the function is called more than once.
4560 self.footer_images = []
4562 if options.get("image_left"):
4563 options["image_data"] = options.get("image_data_left")
4564 image = self._image_from_source(options.get("image_left"), options)
4565 image._header_position = "LF"
4566 self.footer_images.append(image)
4568 if options.get("image_center"):
4569 options["image_data"] = options.get("image_data_center")
4570 image = self._image_from_source(options.get("image_center"), options)
4571 image._header_position = "CF"
4572 self.footer_images.append(image)
4574 if options.get("image_right"):
4575 options["image_data"] = options.get("image_data_right")
4576 image = self._image_from_source(options.get("image_right"), options)
4577 image._header_position = "RF"
4578 self.footer_images.append(image)
4580 placeholder_count = footer.count("&G")
4581 image_count = len(self.footer_images)
4583 if placeholder_count != image_count:
4584 warn(
4585 f"Number of footer images '{image_count}' doesn't match placeholder "
4586 f"count '{placeholder_count}' in string: {footer_orig}"
4587 )
4588 self.footer_images = []
4589 return
4591 if "align_with_margins" in options:
4592 self.header_footer_aligns = options["align_with_margins"]
4594 if "scale_with_doc" in options:
4595 self.header_footer_scales = options["scale_with_doc"]
4597 self.footer = footer
4598 self.margin_footer = options.get("margin", 0.3)
4599 self.header_footer_changed = True
4601 if image_count:
4602 self.has_header_vml = True
4604 def repeat_rows(self, first_row: int, last_row: Optional[int] = None) -> None:
4605 """
4606 Set the rows to repeat at the top of each printed page.
4608 Args:
4609 first_row: Start row for range.
4610 last_row: End row for range.
4612 Returns:
4613 Nothing.
4615 """
4616 if last_row is None:
4617 last_row = first_row
4619 # Convert rows to 1 based.
4620 first_row += 1
4621 last_row += 1
4623 # Create the row range area like: $1:$2.
4624 area = f"${first_row}:${last_row}"
4626 # Build up the print titles area "Sheet1!$1:$2"
4627 sheetname = quote_sheetname(self.name)
4628 self.repeat_row_range = sheetname + "!" + area
4630 @convert_column_args
4631 def repeat_columns(self, first_col: int, last_col: Optional[int] = None) -> None:
4632 """
4633 Set the columns to repeat at the left hand side of each printed page.
4635 Args:
4636 first_col: Start column for range.
4637 last_col: End column for range.
4639 Returns:
4640 Nothing.
4642 """
4643 if last_col is None:
4644 last_col = first_col
4646 # Convert to A notation.
4647 first_col = xl_col_to_name(first_col, 1)
4648 last_col = xl_col_to_name(last_col, 1)
4650 # Create a column range like $C:$D.
4651 area = first_col + ":" + last_col
4653 # Build up the print area range "=Sheet2!$C:$D"
4654 sheetname = quote_sheetname(self.name)
4655 self.repeat_col_range = sheetname + "!" + area
4657 def hide_gridlines(self, option: Literal[0, 1, 2] = 1) -> None:
4658 """
4659 Set the option to hide gridlines on the screen and the printed page.
4661 Args:
4662 option: 0 : Don't hide gridlines
4663 1 : Hide printed gridlines only
4664 2 : Hide screen and printed gridlines
4666 Returns:
4667 Nothing.
4669 """
4670 if option == 0:
4671 self.print_gridlines = 1
4672 self.screen_gridlines = 1
4673 self.print_options_changed = True
4674 elif option == 1:
4675 self.print_gridlines = 0
4676 self.screen_gridlines = 1
4677 else:
4678 self.print_gridlines = 0
4679 self.screen_gridlines = 0
4681 def print_row_col_headers(self) -> None:
4682 """
4683 Set the option to print the row and column headers on the printed page.
4685 Args:
4686 None.
4688 Returns:
4689 Nothing.
4691 """
4692 self.print_headers = True
4693 self.print_options_changed = True
4695 def hide_row_col_headers(self) -> None:
4696 """
4697 Set the option to hide the row and column headers on the worksheet.
4699 Args:
4700 None.
4702 Returns:
4703 Nothing.
4705 """
4706 self.row_col_headers = True
4708 @convert_range_args
4709 def print_area(
4710 self, first_row: int, first_col: int, last_row: int, last_col: int
4711 ) -> Literal[0, -1]:
4712 """
4713 Set the print area in the current worksheet.
4715 Args:
4716 first_row: The first row of the cell range. (zero indexed).
4717 first_col: The first column of the cell range.
4718 last_row: The last row of the cell range. (zero indexed).
4719 last_col: The last column of the cell range.
4721 Returns:
4722 0: Success.
4723 -1: Row or column is out of worksheet bounds.
4725 """
4726 # Set the print area in the current worksheet.
4728 # Ignore max print area since it is the same as no area for Excel.
4729 if (
4730 first_row == 0
4731 and first_col == 0
4732 and last_row == self.xls_rowmax - 1
4733 and last_col == self.xls_colmax - 1
4734 ):
4735 return -1
4737 # Build up the print area range "Sheet1!$A$1:$C$13".
4738 area = self._convert_name_area(first_row, first_col, last_row, last_col)
4739 self.print_area_range = area
4741 return 0
4743 def print_across(self) -> None:
4744 """
4745 Set the order in which pages are printed.
4747 Args:
4748 None.
4750 Returns:
4751 Nothing.
4753 """
4754 self.page_order = 1
4755 self.page_setup_changed = True
4757 def fit_to_pages(self, width: int, height: int) -> None:
4758 """
4759 Fit the printed area to a specific number of pages both vertically and
4760 horizontally.
4762 Args:
4763 width: Number of pages horizontally.
4764 height: Number of pages vertically.
4766 Returns:
4767 Nothing.
4769 """
4770 self.fit_page = 1
4771 self.fit_width = width
4772 self.fit_height = height
4773 self.page_setup_changed = True
4775 def set_start_page(self, start_page: int) -> None:
4776 """
4777 Set the start page number when printing.
4779 Args:
4780 start_page: Start page number.
4782 Returns:
4783 Nothing.
4785 """
4786 self.page_start = start_page
4788 def set_print_scale(self, scale: int) -> None:
4789 """
4790 Set the scale factor for the printed page.
4792 Args:
4793 scale: Print scale. 10 <= scale <= 400.
4795 Returns:
4796 Nothing.
4798 """
4799 # Confine the scale to Excel's range.
4800 if scale < 10 or scale > 400:
4801 warn(f"Print scale '{scale}' outside range: 10 <= scale <= 400")
4802 return
4804 # Turn off "fit to page" option when print scale is on.
4805 self.fit_page = 0
4807 self.print_scale = int(scale)
4808 self.page_setup_changed = True
4810 def print_black_and_white(self) -> None:
4811 """
4812 Set the option to print the worksheet in black and white.
4814 Args:
4815 None.
4817 Returns:
4818 Nothing.
4820 """
4821 self.black_white = True
4822 self.page_setup_changed = True
4824 def set_h_pagebreaks(self, breaks: List[int]) -> None:
4825 """
4826 Set the horizontal page breaks on a worksheet.
4828 Args:
4829 breaks: List of rows where the page breaks should be added.
4831 Returns:
4832 Nothing.
4834 """
4835 self.hbreaks = breaks
4837 def set_v_pagebreaks(self, breaks: List[int]) -> None:
4838 """
4839 Set the horizontal page breaks on a worksheet.
4841 Args:
4842 breaks: List of columns where the page breaks should be added.
4844 Returns:
4845 Nothing.
4847 """
4848 self.vbreaks = breaks
4850 def set_vba_name(self, name: Optional[str] = None) -> None:
4851 """
4852 Set the VBA name for the worksheet. By default this is the
4853 same as the sheet name: i.e., Sheet1 etc.
4855 Args:
4856 name: The VBA name for the worksheet.
4858 Returns:
4859 Nothing.
4861 """
4862 if name is not None:
4863 self.vba_codename = name
4864 else:
4865 self.vba_codename = "Sheet" + str(self.index + 1)
4867 def ignore_errors(self, options: Optional[Dict[str, Any]] = None) -> Literal[0, -1]:
4868 """
4869 Ignore various Excel errors/warnings in a worksheet for user defined
4870 ranges.
4872 Args:
4873 options: A dict of ignore errors keys with cell range values.
4875 Returns:
4876 0: Success.
4877 -1: Incorrect parameter or option.
4879 """
4880 if options is None:
4881 return -1
4883 # Copy the user defined options so they aren't modified.
4884 options = options.copy()
4886 # Valid input parameters.
4887 valid_parameters = {
4888 "number_stored_as_text",
4889 "eval_error",
4890 "formula_differs",
4891 "formula_range",
4892 "formula_unlocked",
4893 "empty_cell_reference",
4894 "list_data_validation",
4895 "calculated_column",
4896 "two_digit_text_year",
4897 }
4899 # Check for valid input parameters.
4900 for param_key in options.keys():
4901 if param_key not in valid_parameters:
4902 warn(f"Unknown parameter '{param_key}' in ignore_errors()")
4903 return -1
4905 self.ignored_errors = options
4907 return 0
4909 ###########################################################################
4910 #
4911 # Private API.
4912 #
4913 ###########################################################################
4914 def _initialize(self, init_data) -> None:
4915 self.name = init_data["name"]
4916 self.index = init_data["index"]
4917 self.str_table = init_data["str_table"]
4918 self.worksheet_meta = init_data["worksheet_meta"]
4919 self.constant_memory = init_data["constant_memory"]
4920 self.tmpdir = init_data["tmpdir"]
4921 self.date_1904 = init_data["date_1904"]
4922 self.strings_to_numbers = init_data["strings_to_numbers"]
4923 self.strings_to_formulas = init_data["strings_to_formulas"]
4924 self.strings_to_urls = init_data["strings_to_urls"]
4925 self.nan_inf_to_errors = init_data["nan_inf_to_errors"]
4926 self.default_date_format = init_data["default_date_format"]
4927 self.default_url_format = init_data["default_url_format"]
4928 self.workbook_add_format = init_data["workbook_add_format"]
4929 self.excel2003_style = init_data["excel2003_style"]
4930 self.remove_timezone = init_data["remove_timezone"]
4931 self.max_url_length = init_data["max_url_length"]
4932 self.use_future_functions = init_data["use_future_functions"]
4933 self.embedded_images = init_data["embedded_images"]
4935 if self.excel2003_style:
4936 self.original_row_height = 12.75
4937 self.default_row_height = 12.75
4938 self.default_row_pixels = 17
4939 self.margin_left = 0.75
4940 self.margin_right = 0.75
4941 self.margin_top = 1
4942 self.margin_bottom = 1
4943 self.margin_header = 0.5
4944 self.margin_footer = 0.5
4945 self.header_footer_aligns = False
4947 # Open a temp filehandle to store row data in constant_memory mode.
4948 if self.constant_memory:
4949 # This is sub-optimal but we need to create a temp file
4950 # with utf8 encoding in Python < 3.
4951 (fd, filename) = tempfile.mkstemp(dir=self.tmpdir)
4952 os.close(fd)
4953 self.row_data_filename = filename
4954 # pylint: disable=consider-using-with
4955 self.row_data_fh = open(filename, mode="w+", encoding="utf-8")
4957 # Set as the worksheet filehandle until the file is assembled.
4958 self.fh = self.row_data_fh
4960 def _assemble_xml_file(self) -> None:
4961 # Assemble and write the XML file.
4963 # Write the XML declaration.
4964 self._xml_declaration()
4966 # Write the root worksheet element.
4967 self._write_worksheet()
4969 # Write the worksheet properties.
4970 self._write_sheet_pr()
4972 # Write the worksheet dimensions.
4973 self._write_dimension()
4975 # Write the sheet view properties.
4976 self._write_sheet_views()
4978 # Write the sheet format properties.
4979 self._write_sheet_format_pr()
4981 # Write the sheet column info.
4982 self._write_cols()
4984 # Write the worksheet data such as rows columns and cells.
4985 if not self.constant_memory:
4986 self._write_sheet_data()
4987 else:
4988 self._write_optimized_sheet_data()
4990 # Write the sheetProtection element.
4991 self._write_sheet_protection()
4993 # Write the protectedRanges element.
4994 self._write_protected_ranges()
4996 # Write the phoneticPr element.
4997 if self.excel2003_style:
4998 self._write_phonetic_pr()
5000 # Write the autoFilter element.
5001 self._write_auto_filter()
5003 # Write the mergeCells element.
5004 self._write_merge_cells()
5006 # Write the conditional formats.
5007 self._write_conditional_formats()
5009 # Write the dataValidations element.
5010 self._write_data_validations()
5012 # Write the hyperlink element.
5013 self._write_hyperlinks()
5015 # Write the printOptions element.
5016 self._write_print_options()
5018 # Write the worksheet page_margins.
5019 self._write_page_margins()
5021 # Write the worksheet page setup.
5022 self._write_page_setup()
5024 # Write the headerFooter element.
5025 self._write_header_footer()
5027 # Write the rowBreaks element.
5028 self._write_row_breaks()
5030 # Write the colBreaks element.
5031 self._write_col_breaks()
5033 # Write the ignoredErrors element.
5034 self._write_ignored_errors()
5036 # Write the drawing element.
5037 self._write_drawings()
5039 # Write the legacyDrawing element.
5040 self._write_legacy_drawing()
5042 # Write the legacyDrawingHF element.
5043 self._write_legacy_drawing_hf()
5045 # Write the picture element, for the background.
5046 self._write_picture()
5048 # Write the tableParts element.
5049 self._write_table_parts()
5051 # Write the extLst elements.
5052 self._write_ext_list()
5054 # Close the worksheet tag.
5055 self._xml_end_tag("worksheet")
5057 # Close the file.
5058 self._xml_close()
5060 def _check_dimensions(
5061 self, row: int, col: int, ignore_row=False, ignore_col=False
5062 ) -> int:
5063 # Check that row and col are valid and store the max and min
5064 # values for use in other methods/elements. The ignore_row /
5065 # ignore_col flags is used to indicate that we wish to perform
5066 # the dimension check without storing the value. The ignore
5067 # flags are use by set_row() and data_validate.
5069 # Check that the row/col are within the worksheet bounds.
5070 if row < 0 or col < 0:
5071 return -1
5072 if row >= self.xls_rowmax or col >= self.xls_colmax:
5073 return -1
5075 # In constant_memory mode we don't change dimensions for rows
5076 # that are already written.
5077 if not ignore_row and not ignore_col and self.constant_memory:
5078 if row < self.previous_row:
5079 return -2
5081 if not ignore_row:
5082 if self.dim_rowmin is None or row < self.dim_rowmin:
5083 self.dim_rowmin = row
5084 if self.dim_rowmax is None or row > self.dim_rowmax:
5085 self.dim_rowmax = row
5087 if not ignore_col:
5088 if self.dim_colmin is None or col < self.dim_colmin:
5089 self.dim_colmin = col
5090 if self.dim_colmax is None or col > self.dim_colmax:
5091 self.dim_colmax = col
5093 return 0
5095 def _convert_date_time(self, dt_obj):
5096 # Convert a datetime object to an Excel serial date and time.
5097 return _datetime_to_excel_datetime(dt_obj, self.date_1904, self.remove_timezone)
5099 def _convert_name_area(self, row_num_1, col_num_1, row_num_2, col_num_2):
5100 # Convert zero indexed rows and columns to the format required by
5101 # worksheet named ranges, eg, "Sheet1!$A$1:$C$13".
5103 range1 = ""
5104 range2 = ""
5105 area = ""
5106 row_col_only = 0
5108 # Convert to A1 notation.
5109 col_char_1 = xl_col_to_name(col_num_1, 1)
5110 col_char_2 = xl_col_to_name(col_num_2, 1)
5111 row_char_1 = "$" + str(row_num_1 + 1)
5112 row_char_2 = "$" + str(row_num_2 + 1)
5114 # We need to handle special cases that refer to rows or columns only.
5115 if row_num_1 == 0 and row_num_2 == self.xls_rowmax - 1:
5116 range1 = col_char_1
5117 range2 = col_char_2
5118 row_col_only = 1
5119 elif col_num_1 == 0 and col_num_2 == self.xls_colmax - 1:
5120 range1 = row_char_1
5121 range2 = row_char_2
5122 row_col_only = 1
5123 else:
5124 range1 = col_char_1 + row_char_1
5125 range2 = col_char_2 + row_char_2
5127 # A repeated range is only written once (if it isn't a special case).
5128 if range1 == range2 and not row_col_only:
5129 area = range1
5130 else:
5131 area = range1 + ":" + range2
5133 # Build up the print area range "Sheet1!$A$1:$C$13".
5134 sheetname = quote_sheetname(self.name)
5135 area = sheetname + "!" + area
5137 return area
5139 def _sort_pagebreaks(self, breaks):
5140 # This is an internal method used to filter elements of a list of
5141 # pagebreaks used in the _store_hbreak() and _store_vbreak() methods.
5142 # It:
5143 # 1. Removes duplicate entries from the list.
5144 # 2. Sorts the list.
5145 # 3. Removes 0 from the list if present.
5146 if not breaks:
5147 return []
5149 breaks_set = set(breaks)
5151 if 0 in breaks_set:
5152 breaks_set.remove(0)
5154 breaks_list = list(breaks_set)
5155 breaks_list.sort()
5157 # The Excel 2007 specification says that the maximum number of page
5158 # breaks is 1026. However, in practice it is actually 1023.
5159 max_num_breaks = 1023
5160 if len(breaks_list) > max_num_breaks:
5161 breaks_list = breaks_list[:max_num_breaks]
5163 return breaks_list
5165 def _extract_filter_tokens(self, expression):
5166 # Extract the tokens from the filter expression. The tokens are mainly
5167 # non-whitespace groups. The only tricky part is to extract string
5168 # tokens that contain whitespace and/or quoted double quotes (Excel's
5169 # escaped quotes).
5170 #
5171 # Examples: 'x < 2000'
5172 # 'x > 2000 and x < 5000'
5173 # 'x = "foo"'
5174 # 'x = "foo bar"'
5175 # 'x = "foo "" bar"'
5176 #
5177 if not expression:
5178 return []
5180 token_re = re.compile(r'"(?:[^"]|"")*"|\S+')
5181 tokens = token_re.findall(expression)
5183 new_tokens = []
5184 # Remove single leading and trailing quotes and un-escape other quotes.
5185 for token in tokens:
5186 if token.startswith('"'):
5187 token = token[1:]
5189 if token.endswith('"'):
5190 token = token[:-1]
5192 token = token.replace('""', '"')
5194 new_tokens.append(token)
5196 return new_tokens
5198 def _parse_filter_expression(self, expression, tokens):
5199 # Converts the tokens of a possibly conditional expression into 1 or 2
5200 # sub expressions for further parsing.
5201 #
5202 # Examples:
5203 # ('x', '==', 2000) -> exp1
5204 # ('x', '>', 2000, 'and', 'x', '<', 5000) -> exp1 and exp2
5206 if len(tokens) == 7:
5207 # The number of tokens will be either 3 (for 1 expression)
5208 # or 7 (for 2 expressions).
5209 conditional = tokens[3]
5211 if re.match("(and|&&)", conditional):
5212 conditional = 0
5213 elif re.match(r"(or|\|\|)", conditional):
5214 conditional = 1
5215 else:
5216 warn(
5217 f"Token '{conditional}' is not a valid conditional "
5218 f"in filter expression '{expression}'"
5219 )
5221 expression_1 = self._parse_filter_tokens(expression, tokens[0:3])
5222 expression_2 = self._parse_filter_tokens(expression, tokens[4:7])
5223 return expression_1 + [conditional] + expression_2
5225 return self._parse_filter_tokens(expression, tokens)
5227 def _parse_filter_tokens(self, expression, tokens):
5228 # Parse the 3 tokens of a filter expression and return the operator
5229 # and token. The use of numbers instead of operators is a legacy of
5230 # Spreadsheet::WriteExcel.
5231 operators = {
5232 "==": 2,
5233 "=": 2,
5234 "=~": 2,
5235 "eq": 2,
5236 "!=": 5,
5237 "!~": 5,
5238 "ne": 5,
5239 "<>": 5,
5240 "<": 1,
5241 "<=": 3,
5242 ">": 4,
5243 ">=": 6,
5244 }
5246 operator = operators.get(tokens[1], None)
5247 token = tokens[2]
5249 # Special handling of "Top" filter expressions.
5250 if re.match("top|bottom", tokens[0].lower()):
5251 value = int(tokens[1])
5253 if value < 1 or value > 500:
5254 warn(
5255 f"The value '{token}' in expression '{expression}' "
5256 f"must be in the range 1 to 500"
5257 )
5259 token = token.lower()
5261 if token not in ("items", "%"):
5262 warn(
5263 f"The type '{token}' in expression '{expression}' "
5264 f"must be either 'items' or '%%'"
5265 )
5267 if tokens[0].lower() == "top":
5268 operator = 30
5269 else:
5270 operator = 32
5272 if tokens[2] == "%":
5273 operator += 1
5275 token = str(value)
5277 if not operator and tokens[0]:
5278 warn(
5279 f"Token '{token[0]}' is not a valid operator "
5280 f"in filter expression '{expression}'."
5281 )
5283 # Special handling for Blanks/NonBlanks.
5284 if re.match("blanks|nonblanks", token.lower()):
5285 # Only allow Equals or NotEqual in this context.
5286 if operator not in (2, 5):
5287 warn(
5288 f"The operator '{tokens[1]}' in expression '{expression}' "
5289 f"is not valid in relation to Blanks/NonBlanks'."
5290 )
5292 token = token.lower()
5294 # The operator should always be 2 (=) to flag a "simple" equality
5295 # in the binary record. Therefore we convert <> to =.
5296 if token == "blanks":
5297 if operator == 5:
5298 token = " "
5299 else:
5300 if operator == 5:
5301 operator = 2
5302 token = "blanks"
5303 else:
5304 operator = 5
5305 token = " "
5307 # if the string token contains an Excel match character then change the
5308 # operator type to indicate a non "simple" equality.
5309 if operator == 2 and re.search("[*?]", token):
5310 operator = 22
5312 return [operator, token]
5314 def _encode_password(self, password) -> str:
5315 # Hash a worksheet password. Based on the algorithm in
5316 # ECMA-376-4:2016, Office Open XML File Formats — Transitional
5317 # Migration Features, Additional attributes for workbookProtection
5318 # element (Part 1, §18.2.29).
5319 digest = 0x0000
5321 for char in password[::-1]:
5322 digest = ((digest >> 14) & 0x01) | ((digest << 1) & 0x7FFF)
5323 digest ^= ord(char)
5325 digest = ((digest >> 14) & 0x01) | ((digest << 1) & 0x7FFF)
5326 digest ^= len(password)
5327 digest ^= 0xCE4B
5329 return f"{digest:X}"
5331 def _image_from_source(self, source, options: Optional[Dict[str, Any]] = None):
5332 # Backward compatibility utility method to convert an input argument to
5333 # an Image object. The source can be a filename, BytesIO stream or
5334 # an existing Image object.
5335 if isinstance(source, Image):
5336 image = source
5337 elif options is not None and options.get("image_data"):
5338 image = Image(options["image_data"])
5339 image.image_name = source
5340 else:
5341 image = Image(source)
5343 return image
5345 def _prepare_image(
5346 self,
5347 image: Image,
5348 image_id: int,
5349 drawing_id: int,
5350 ) -> None:
5351 # Set up images/drawings.
5353 # Get the effective image width and height in pixels.
5354 width = image._width * image._x_scale
5355 height = image._height * image._y_scale
5357 # Scale by non 96dpi resolutions.
5358 width *= 96.0 / image._x_dpi
5359 height *= 96.0 / image._y_dpi
5361 dimensions = self._position_object_emus(
5362 image._col,
5363 image._row,
5364 image._x_offset,
5365 image._y_offset,
5366 width,
5367 height,
5368 image._anchor,
5369 )
5371 # Convert from pixels to emus.
5372 width = int(0.5 + (width * 9525))
5373 height = int(0.5 + (height * 9525))
5375 # Create a Drawing obj to use with worksheet unless one already exists.
5376 if not self.drawing:
5377 drawing = Drawing()
5378 drawing.embedded = 1
5379 self.drawing = drawing
5381 self.external_drawing_links.append(
5382 ["/drawing", "../drawings/drawing" + str(drawing_id) + ".xml", None]
5383 )
5384 else:
5385 drawing = self.drawing
5387 drawing_object = DrawingInfo()
5388 drawing_object._drawing_type = DrawingTypes.IMAGE
5389 drawing_object._dimensions = dimensions
5390 drawing_object._description = image.image_name
5391 drawing_object._width = width
5392 drawing_object._height = height
5393 drawing_object._shape = None
5394 drawing_object._anchor = image._anchor
5395 drawing_object._rel_index = 0
5396 drawing_object._decorative = image._decorative
5398 if image.description is not None:
5399 drawing_object._description = image.description
5401 if image._url:
5402 url = image._url
5403 target = url._target()
5404 target_mode = url._target_mode()
5406 if not self.drawing_rels.get(url._link):
5407 self.drawing_links.append(["/hyperlink", target, target_mode])
5409 url._rel_index = self._get_drawing_rel_index(url._link)
5410 drawing_object._url = url
5412 if not self.drawing_rels.get(image._digest):
5413 self.drawing_links.append(
5414 [
5415 "/image",
5416 "../media/image" + str(image_id) + "." + image._image_extension,
5417 ]
5418 )
5420 drawing_object._rel_index = self._get_drawing_rel_index(image._digest)
5421 drawing._add_drawing_object(drawing_object)
5423 def _prepare_shape(self, index, drawing_id) -> None:
5424 # Set up shapes/drawings.
5425 (
5426 row,
5427 col,
5428 x_offset,
5429 y_offset,
5430 x_scale,
5431 y_scale,
5432 text,
5433 anchor,
5434 options,
5435 description,
5436 decorative,
5437 ) = self.shapes[index]
5439 width = options.get("width", self.default_col_pixels * 3)
5440 height = options.get("height", self.default_row_pixels * 6)
5442 width *= x_scale
5443 height *= y_scale
5445 dimensions = self._position_object_emus(
5446 col, row, x_offset, y_offset, width, height, anchor
5447 )
5449 # Convert from pixels to emus.
5450 width = int(0.5 + (width * 9525))
5451 height = int(0.5 + (height * 9525))
5453 # Create a Drawing obj to use with worksheet unless one already exists.
5454 if not self.drawing:
5455 drawing = Drawing()
5456 drawing.embedded = 1
5457 self.drawing = drawing
5459 self.external_drawing_links.append(
5460 ["/drawing", "../drawings/drawing" + str(drawing_id) + ".xml", None]
5461 )
5462 else:
5463 drawing = self.drawing
5465 shape = Shape("rect", "TextBox", options)
5466 shape.text = text
5468 drawing_object = DrawingInfo()
5469 drawing_object._drawing_type = DrawingTypes.SHAPE
5470 drawing_object._dimensions = dimensions
5471 drawing_object._width = width
5472 drawing_object._height = height
5473 drawing_object._description = description
5474 drawing_object._shape = shape
5475 drawing_object._anchor = anchor
5476 drawing_object._rel_index = 0
5477 drawing_object._decorative = decorative
5479 url = Url.from_options(options)
5480 if url:
5481 target = url._target()
5482 target_mode = url._target_mode()
5484 if not self.drawing_rels.get(url._link):
5485 self.drawing_links.append(["/hyperlink", target, target_mode])
5487 url._rel_index = self._get_drawing_rel_index(url._link)
5488 drawing_object._url = url
5490 drawing._add_drawing_object(drawing_object)
5492 def _prepare_header_image(self, image_id, image) -> None:
5493 # Set up an image without a drawing object for header/footer images.
5495 # Strip the extension from the filename.
5496 image.image_name = re.sub(r"\..*$", "", image.image_name)
5498 if not self.vml_drawing_rels.get(image._digest):
5499 self.vml_drawing_links.append(
5500 [
5501 "/image",
5502 "../media/image" + str(image_id) + "." + image._image_extension,
5503 ]
5504 )
5506 image._ref_id = self._get_vml_drawing_rel_index(image._digest)
5508 self.header_images_list.append(image)
5510 def _prepare_background(self, image_id, image_extension) -> None:
5511 # Set up an image without a drawing object for backgrounds.
5512 self.external_background_links.append(
5513 ["/image", "../media/image" + str(image_id) + "." + image_extension]
5514 )
5516 def _prepare_chart(self, index, chart_id, drawing_id) -> None:
5517 # Set up chart/drawings.
5518 (
5519 row,
5520 col,
5521 chart,
5522 x_offset,
5523 y_offset,
5524 x_scale,
5525 y_scale,
5526 anchor,
5527 description,
5528 decorative,
5529 ) = self.charts[index]
5531 chart.id = chart_id - 1
5533 # Use user specified dimensions, if any.
5534 width = int(0.5 + (chart.width * x_scale))
5535 height = int(0.5 + (chart.height * y_scale))
5537 dimensions = self._position_object_emus(
5538 col, row, x_offset, y_offset, width, height, anchor
5539 )
5541 # Set the chart name for the embedded object if it has been specified.
5542 name = chart.chart_name
5544 # Create a Drawing obj to use with worksheet unless one already exists.
5545 if not self.drawing:
5546 drawing = Drawing()
5547 drawing.embedded = 1
5548 self.drawing = drawing
5550 self.external_drawing_links.append(
5551 ["/drawing", "../drawings/drawing" + str(drawing_id) + ".xml"]
5552 )
5553 else:
5554 drawing = self.drawing
5556 drawing_object = DrawingInfo()
5557 drawing_object._drawing_type = DrawingTypes.CHART
5558 drawing_object._dimensions = dimensions
5559 drawing_object._width = width
5560 drawing_object._height = height
5561 drawing_object._name = name
5562 drawing_object._shape = None
5563 drawing_object._anchor = anchor
5564 drawing_object._rel_index = self._get_drawing_rel_index()
5565 drawing_object._description = description
5566 drawing_object._decorative = decorative
5568 drawing._add_drawing_object(drawing_object)
5570 self.drawing_links.append(
5571 ["/chart", "../charts/chart" + str(chart_id) + ".xml"]
5572 )
5574 def _position_object_emus(
5575 self, col_start, row_start, x1, y1, width, height, anchor
5576 ):
5577 # Calculate the vertices that define the position of a graphical
5578 # object within the worksheet in EMUs.
5579 #
5580 # The vertices are expressed as English Metric Units (EMUs). There are
5581 # 12,700 EMUs per point. Therefore, 12,700 * 3 /4 = 9,525 EMUs per
5582 # pixel
5583 (
5584 col_start,
5585 row_start,
5586 x1,
5587 y1,
5588 col_end,
5589 row_end,
5590 x2,
5591 y2,
5592 x_abs,
5593 y_abs,
5594 ) = self._position_object_pixels(
5595 col_start, row_start, x1, y1, width, height, anchor
5596 )
5598 # Convert the pixel values to EMUs. See above.
5599 x1 = int(0.5 + 9525 * x1)
5600 y1 = int(0.5 + 9525 * y1)
5601 x2 = int(0.5 + 9525 * x2)
5602 y2 = int(0.5 + 9525 * y2)
5603 x_abs = int(0.5 + 9525 * x_abs)
5604 y_abs = int(0.5 + 9525 * y_abs)
5606 return (col_start, row_start, x1, y1, col_end, row_end, x2, y2, x_abs, y_abs)
5608 # Calculate the vertices that define the position of a graphical object
5609 # within the worksheet in pixels.
5610 #
5611 # +------------+------------+
5612 # | A | B |
5613 # +-----+------------+------------+
5614 # | |(x1,y1) | |
5615 # | 1 |(A1)._______|______ |
5616 # | | | | |
5617 # | | | | |
5618 # +-----+----| OBJECT |-----+
5619 # | | | | |
5620 # | 2 | |______________. |
5621 # | | | (B2)|
5622 # | | | (x2,y2)|
5623 # +---- +------------+------------+
5624 #
5625 # Example of an object that covers some of the area from cell A1 to B2.
5626 #
5627 # Based on the width and height of the object we need to calculate 8 vars:
5628 #
5629 # col_start, row_start, col_end, row_end, x1, y1, x2, y2.
5630 #
5631 # We also calculate the absolute x and y position of the top left vertex of
5632 # the object. This is required for images.
5633 #
5634 # The width and height of the cells that the object occupies can be
5635 # variable and have to be taken into account.
5636 #
5637 # The values of col_start and row_start are passed in from the calling
5638 # function. The values of col_end and row_end are calculated by
5639 # subtracting the width and height of the object from the width and
5640 # height of the underlying cells.
5641 #
5642 def _position_object_pixels(
5643 self, col_start, row_start, x1, y1, width, height, anchor
5644 ):
5645 # col_start # Col containing upper left corner of object.
5646 # x1 # Distance to left side of object.
5647 #
5648 # row_start # Row containing top left corner of object.
5649 # y1 # Distance to top of object.
5650 #
5651 # col_end # Col containing lower right corner of object.
5652 # x2 # Distance to right side of object.
5653 #
5654 # row_end # Row containing bottom right corner of object.
5655 # y2 # Distance to bottom of object.
5656 #
5657 # width # Width of object frame.
5658 # height # Height of object frame.
5659 #
5660 # x_abs # Absolute distance to left side of object.
5661 # y_abs # Absolute distance to top side of object.
5662 x_abs = 0
5663 y_abs = 0
5665 # Adjust start column for negative offsets.
5666 # pylint: disable=chained-comparison
5667 while x1 < 0 and col_start > 0:
5668 x1 += self._size_col(col_start - 1)
5669 col_start -= 1
5671 # Adjust start row for negative offsets.
5672 while y1 < 0 and row_start > 0:
5673 y1 += self._size_row(row_start - 1)
5674 row_start -= 1
5676 # Ensure that the image isn't shifted off the page at top left.
5677 x1 = max(0, x1)
5678 y1 = max(0, y1)
5680 # Calculate the absolute x offset of the top-left vertex.
5681 if self.col_size_changed:
5682 for col_id in range(col_start):
5683 x_abs += self._size_col(col_id)
5684 else:
5685 # Optimization for when the column widths haven't changed.
5686 x_abs += self.default_col_pixels * col_start
5688 x_abs += x1
5690 # Calculate the absolute y offset of the top-left vertex.
5691 if self.row_size_changed:
5692 for row_id in range(row_start):
5693 y_abs += self._size_row(row_id)
5694 else:
5695 # Optimization for when the row heights haven't changed.
5696 y_abs += self.default_row_pixels * row_start
5698 y_abs += y1
5700 # Adjust start column for offsets that are greater than the col width.
5701 while x1 >= self._size_col(col_start, anchor):
5702 x1 -= self._size_col(col_start)
5703 col_start += 1
5705 # Adjust start row for offsets that are greater than the row height.
5706 while y1 >= self._size_row(row_start, anchor):
5707 y1 -= self._size_row(row_start)
5708 row_start += 1
5710 # Initialize end cell to the same as the start cell.
5711 col_end = col_start
5712 row_end = row_start
5714 # Don't offset the image in the cell if the row/col is hidden.
5715 if self._size_col(col_start, anchor) > 0:
5716 width = width + x1
5717 if self._size_row(row_start, anchor) > 0:
5718 height = height + y1
5720 # Subtract the underlying cell widths to find end cell of the object.
5721 while width >= self._size_col(col_end, anchor):
5722 width -= self._size_col(col_end, anchor)
5723 col_end += 1
5725 # Subtract the underlying cell heights to find end cell of the object.
5726 while height >= self._size_row(row_end, anchor):
5727 height -= self._size_row(row_end, anchor)
5728 row_end += 1
5730 # The end vertices are whatever is left from the width and height.
5731 x2 = width
5732 y2 = height
5734 return [col_start, row_start, x1, y1, col_end, row_end, x2, y2, x_abs, y_abs]
5736 def _size_col(self, col: int, anchor=0):
5737 # Convert the width of a cell from character units to pixels. Excel
5738 # rounds the column width to the nearest pixel. If the width hasn't
5739 # been set by the user we use the default value. A hidden column is
5740 # treated as having a width of zero unless it has the special
5741 # "object_position" of 4 (size with cells).
5742 max_digit_width = 7 # For Calibri 11.
5743 padding = 5
5744 pixels = 0
5746 # Look up the cell value to see if it has been changed.
5747 if col in self.col_info:
5748 width = self.col_info[col][0]
5749 hidden = self.col_info[col][2]
5751 if width is None:
5752 width = self.default_col_width
5754 # Convert to pixels.
5755 if hidden and anchor != 4:
5756 pixels = 0
5757 elif width < 1:
5758 pixels = int(width * (max_digit_width + padding) + 0.5)
5759 else:
5760 pixels = int(width * max_digit_width + 0.5) + padding
5761 else:
5762 pixels = self.default_col_pixels
5764 return pixels
5766 def _size_row(self, row: int, anchor=0):
5767 # Convert the height of a cell from character units to pixels. If the
5768 # height hasn't been set by the user we use the default value. A
5769 # hidden row is treated as having a height of zero unless it has the
5770 # special "object_position" of 4 (size with cells).
5771 pixels = 0
5773 # Look up the cell value to see if it has been changed
5774 if row in self.row_sizes:
5775 height = self.row_sizes[row][0]
5776 hidden = self.row_sizes[row][1]
5778 if hidden and anchor != 4:
5779 pixels = 0
5780 else:
5781 pixels = int(4.0 / 3.0 * height)
5782 else:
5783 pixels = int(4.0 / 3.0 * self.default_row_height)
5785 return pixels
5787 def _pixels_to_width(self, pixels):
5788 # Convert the width of a cell from pixels to character units.
5789 max_digit_width = 7.0 # For Calabri 11.
5790 padding = 5.0
5792 if pixels <= 12:
5793 width = pixels / (max_digit_width + padding)
5794 else:
5795 width = (pixels - padding) / max_digit_width
5797 return width
5799 def _pixels_to_height(self, pixels):
5800 # Convert the height of a cell from pixels to character units.
5801 return 0.75 * pixels
5803 def _comment_vertices(self, comment: CommentType):
5804 # Calculate the positions of the comment object.
5805 anchor = 0
5806 vertices = self._position_object_pixels(
5807 comment.start_col,
5808 comment.start_row,
5809 comment.x_offset,
5810 comment.y_offset,
5811 comment.width,
5812 comment.height,
5813 anchor,
5814 )
5816 # Add the width and height for VML.
5817 vertices.append(comment.width)
5818 vertices.append(comment.height)
5820 return vertices
5822 def _button_vertices(self, button: ButtonType):
5823 # Calculate the positions of the button object.
5824 anchor = 0
5825 vertices = self._position_object_pixels(
5826 button.col,
5827 button.row,
5828 button.x_offset,
5829 button.y_offset,
5830 button.width,
5831 button.height,
5832 anchor,
5833 )
5835 # Add the width and height for VML.
5836 vertices.append(button.width)
5837 vertices.append(button.height)
5839 return vertices
5841 def _prepare_vml_objects(
5842 self, vml_data_id, vml_shape_id, vml_drawing_id, comment_id
5843 ):
5844 comments = []
5845 # Sort the comments into row/column order for easier comparison
5846 # testing and set the external links for comments and buttons.
5847 row_nums = sorted(self.comments.keys())
5849 for row in row_nums:
5850 col_nums = sorted(self.comments[row].keys())
5852 for col in col_nums:
5853 comment = self.comments[row][col]
5854 comment.vertices = self._comment_vertices(comment)
5856 # Set comment visibility if required and not user defined.
5857 if comment.is_visible is None:
5858 comment.is_visible = self.comments_visible
5860 # Set comment author if not already user defined.
5861 if comment.author is None:
5862 comment.author = self.comments_author
5864 comments.append(comment)
5866 for button in self.buttons_list:
5867 button.vertices = self._button_vertices(button)
5869 self.external_vml_links.append(
5870 ["/vmlDrawing", "../drawings/vmlDrawing" + str(vml_drawing_id) + ".vml"]
5871 )
5873 if self.has_comments:
5874 self.comments_list = comments
5876 self.external_comment_links.append(
5877 ["/comments", "../comments" + str(comment_id) + ".xml"]
5878 )
5880 count = len(comments)
5881 start_data_id = vml_data_id
5883 # The VML o:idmap data id contains a comma separated range when there
5884 # is more than one 1024 block of comments, like this: data="1,2".
5885 for i in range(int(count / 1024)):
5886 data_id = start_data_id + i + 1
5887 vml_data_id = f"{vml_data_id},{data_id}"
5889 self.vml_data_id = vml_data_id
5890 self.vml_shape_id = vml_shape_id
5892 return count
5894 def _prepare_header_vml_objects(self, vml_header_id, vml_drawing_id) -> None:
5895 # Set up external linkage for VML header/footer images.
5897 self.vml_header_id = vml_header_id
5899 self.external_vml_links.append(
5900 ["/vmlDrawing", "../drawings/vmlDrawing" + str(vml_drawing_id) + ".vml"]
5901 )
5903 def _prepare_tables(self, table_id, seen) -> None:
5904 # Set the table ids for the worksheet tables.
5905 for table in self.tables:
5906 table["id"] = table_id
5908 if table.get("name") is None:
5909 # Set a default name.
5910 table["name"] = "Table" + str(table_id)
5912 # Check for duplicate table names.
5913 name = table["name"].lower()
5915 if name in seen:
5916 raise DuplicateTableName(
5917 f"Duplicate name '{table['name']}' used in worksheet.add_table()."
5918 )
5920 seen[name] = True
5922 # Store the link used for the rels file.
5923 self.external_table_links.append(
5924 ["/table", "../tables/table" + str(table_id) + ".xml"]
5925 )
5926 table_id += 1
5928 def _table_function_to_formula(self, function, col_name):
5929 # Convert a table total function to a worksheet formula.
5930 formula = ""
5932 # Escape special characters, as required by Excel.
5933 col_name = col_name.replace("'", "''")
5934 col_name = col_name.replace("#", "'#")
5935 col_name = col_name.replace("]", "']")
5936 col_name = col_name.replace("[", "'[")
5938 subtotals = {
5939 "average": 101,
5940 "countNums": 102,
5941 "count": 103,
5942 "max": 104,
5943 "min": 105,
5944 "stdDev": 107,
5945 "sum": 109,
5946 "var": 110,
5947 }
5949 if function in subtotals:
5950 func_num = subtotals[function]
5951 formula = f"SUBTOTAL({func_num},[{col_name}])"
5952 else:
5953 warn(f"Unsupported function '{function}' in add_table()")
5955 return formula
5957 def _set_spark_color(self, sparkline, options, user_color) -> None:
5958 # Set the sparkline color.
5959 if user_color not in options:
5960 return
5962 sparkline[user_color] = Color._from_value(options[user_color])
5964 def _get_range_data(self, row_start, col_start, row_end, col_end):
5965 # Returns a range of data from the worksheet _table to be used in
5966 # chart cached data. Strings are returned as SST ids and decoded
5967 # in the workbook. Return None for data that doesn't exist since
5968 # Excel can chart have series with data missing.
5970 if self.constant_memory:
5971 return ()
5973 data = []
5975 # Iterate through the table data.
5976 for row_num in range(row_start, row_end + 1):
5977 # Store None if row doesn't exist.
5978 if row_num not in self.table:
5979 data.append(None)
5980 continue
5982 for col_num in range(col_start, col_end + 1):
5983 if col_num in self.table[row_num]:
5984 cell = self.table[row_num][col_num]
5986 cell_type = cell.__class__.__name__
5988 if cell_type in ("Number", "Datetime"):
5989 # Return a number with Excel's precision.
5990 data.append(f"{cell.number:.16g}")
5992 elif cell_type == "String":
5993 # Return a string from it's shared string index.
5994 index = cell.string
5995 string = self.str_table._get_shared_string(index)
5997 data.append(string)
5999 elif cell_type in ("Formula", "ArrayFormula"):
6000 # Return the formula value.
6001 value = cell.value
6003 if value is None:
6004 value = 0
6006 data.append(value)
6008 elif cell_type == "Blank":
6009 # Return a empty cell.
6010 data.append("")
6011 else:
6012 # Store None if column doesn't exist.
6013 data.append(None)
6015 return data
6017 def _csv_join(self, *items):
6018 # Create a csv string for use with data validation formulas and lists.
6020 # Convert non string types to string.
6021 items = [str(item) if not isinstance(item, str) else item for item in items]
6023 return ",".join(items)
6025 def _escape_url(self, url):
6026 # Don't escape URL if it looks already escaped.
6027 if re.search("%[0-9a-fA-F]{2}", url):
6028 return url
6030 # Can't use url.quote() here because it doesn't match Excel.
6031 url = url.replace("%", "%25")
6032 url = url.replace('"', "%22")
6033 url = url.replace(" ", "%20")
6034 url = url.replace("<", "%3c")
6035 url = url.replace(">", "%3e")
6036 url = url.replace("[", "%5b")
6037 url = url.replace("]", "%5d")
6038 url = url.replace("^", "%5e")
6039 url = url.replace("`", "%60")
6040 url = url.replace("{", "%7b")
6041 url = url.replace("}", "%7d")
6043 return url
6045 def _get_drawing_rel_index(self, target=None):
6046 # Get the index used to address a drawing rel link.
6047 if target is None:
6048 self.drawing_rels_id += 1
6049 return self.drawing_rels_id
6051 if self.drawing_rels.get(target):
6052 return self.drawing_rels[target]
6054 self.drawing_rels_id += 1
6055 self.drawing_rels[target] = self.drawing_rels_id
6056 return self.drawing_rels_id
6058 def _get_vml_drawing_rel_index(self, target=None):
6059 # Get the index used to address a vml drawing rel link.
6060 if self.vml_drawing_rels.get(target):
6061 return self.vml_drawing_rels[target]
6063 self.vml_drawing_rels_id += 1
6064 self.vml_drawing_rels[target] = self.vml_drawing_rels_id
6065 return self.vml_drawing_rels_id
6067 ###########################################################################
6068 #
6069 # The following font methods are mainly duplicated from the Styles class
6070 # with appropriate changes for rich string styles.
6071 #
6072 ###########################################################################
6073 def _write_font(self, xf_format) -> None:
6074 # Write the <font> element.
6075 xml_writer = self.rstring
6077 xml_writer._xml_start_tag("rPr")
6079 # Handle the main font properties.
6080 if xf_format.bold:
6081 xml_writer._xml_empty_tag("b")
6082 if xf_format.italic:
6083 xml_writer._xml_empty_tag("i")
6084 if xf_format.font_strikeout:
6085 xml_writer._xml_empty_tag("strike")
6086 if xf_format.font_outline:
6087 xml_writer._xml_empty_tag("outline")
6088 if xf_format.font_shadow:
6089 xml_writer._xml_empty_tag("shadow")
6091 # Handle the underline variants.
6092 if xf_format.underline:
6093 self._write_underline(xf_format.underline)
6095 # Handle super/subscript.
6096 if xf_format.font_script == 1:
6097 self._write_vert_align("superscript")
6098 if xf_format.font_script == 2:
6099 self._write_vert_align("subscript")
6101 # Write the font size
6102 xml_writer._xml_empty_tag("sz", [("val", xf_format.font_size)])
6104 # Handle colors.
6105 if xf_format.theme == -1:
6106 # Ignore for excel2003_style.
6107 pass
6108 elif xf_format.theme:
6109 self._write_rstring_color("color", [("theme", xf_format.theme)])
6110 elif xf_format.color_indexed:
6111 self._write_rstring_color("color", [("indexed", xf_format.color_indexed)])
6112 elif xf_format.font_color:
6113 color = xf_format.font_color
6114 if not color._is_automatic:
6115 self._write_rstring_color("color", color._attributes())
6116 else:
6117 self._write_rstring_color("color", [("theme", 1)])
6119 # Write some other font properties related to font families.
6120 xml_writer._xml_empty_tag("rFont", [("val", xf_format.font_name)])
6121 xml_writer._xml_empty_tag("family", [("val", xf_format.font_family)])
6123 if xf_format.font_name == "Calibri" and not xf_format.hyperlink:
6124 xml_writer._xml_empty_tag("scheme", [("val", xf_format.font_scheme)])
6126 xml_writer._xml_end_tag("rPr")
6128 def _write_underline(self, underline) -> None:
6129 # Write the underline font element.
6130 attributes = []
6132 # Handle the underline variants.
6133 if underline == 2:
6134 attributes = [("val", "double")]
6135 elif underline == 33:
6136 attributes = [("val", "singleAccounting")]
6137 elif underline == 34:
6138 attributes = [("val", "doubleAccounting")]
6140 self.rstring._xml_empty_tag("u", attributes)
6142 def _write_vert_align(self, val) -> None:
6143 # Write the <vertAlign> font sub-element.
6144 attributes = [("val", val)]
6146 self.rstring._xml_empty_tag("vertAlign", attributes)
6148 def _write_rstring_color(self, name, attributes) -> None:
6149 # Write the <color> element.
6150 self.rstring._xml_empty_tag(name, attributes)
6152 def _opt_close(self) -> None:
6153 # Close the row data filehandle in constant_memory mode.
6154 if not self.row_data_fh_closed:
6155 self.row_data_fh.close()
6156 self.row_data_fh_closed = True
6158 def _opt_reopen(self) -> None:
6159 # Reopen the row data filehandle in constant_memory mode.
6160 if self.row_data_fh_closed:
6161 filename = self.row_data_filename
6162 # pylint: disable=consider-using-with
6163 self.row_data_fh = open(filename, mode="a+", encoding="utf-8")
6164 self.row_data_fh_closed = False
6165 self.fh = self.row_data_fh
6167 def _set_icon_props(self, total_icons, user_props=None):
6168 # Set the sub-properties for icons.
6169 props = []
6171 # Set the defaults.
6172 for _ in range(total_icons):
6173 props.append({"criteria": False, "value": 0, "type": "percent"})
6175 # Set the default icon values based on the number of icons.
6176 if total_icons == 3:
6177 props[0]["value"] = 67
6178 props[1]["value"] = 33
6180 if total_icons == 4:
6181 props[0]["value"] = 75
6182 props[1]["value"] = 50
6183 props[2]["value"] = 25
6185 if total_icons == 5:
6186 props[0]["value"] = 80
6187 props[1]["value"] = 60
6188 props[2]["value"] = 40
6189 props[3]["value"] = 20
6191 # Overwrite default properties with user defined properties.
6192 if user_props:
6193 # Ensure we don't set user properties for lowest icon.
6194 max_data = len(user_props)
6195 if max_data >= total_icons:
6196 max_data = total_icons - 1
6198 for i in range(max_data):
6199 # Set the user defined 'value' property.
6200 if user_props[i].get("value") is not None:
6201 props[i]["value"] = user_props[i]["value"]
6203 # Remove the formula '=' sign if it exists.
6204 tmp = props[i]["value"]
6205 if isinstance(tmp, str) and tmp.startswith("="):
6206 props[i]["value"] = tmp.lstrip("=")
6208 # Set the user defined 'type' property.
6209 if user_props[i].get("type"):
6210 valid_types = ("percent", "percentile", "number", "formula")
6212 if user_props[i]["type"] not in valid_types:
6213 warn(
6214 f"Unknown icon property type '{user_props[i]['type']}' "
6215 f"for sub-property 'type' in conditional_format()."
6216 )
6217 else:
6218 props[i]["type"] = user_props[i]["type"]
6220 if props[i]["type"] == "number":
6221 props[i]["type"] = "num"
6223 # Set the user defined 'criteria' property.
6224 criteria = user_props[i].get("criteria")
6225 if criteria and criteria == ">":
6226 props[i]["criteria"] = True
6228 return props
6230 ###########################################################################
6231 #
6232 # XML methods.
6233 #
6234 ###########################################################################
6236 def _write_worksheet(self) -> None:
6237 # Write the <worksheet> element. This is the root element.
6239 schema = "http://schemas.openxmlformats.org/"
6240 xmlns = schema + "spreadsheetml/2006/main"
6241 xmlns_r = schema + "officeDocument/2006/relationships"
6242 xmlns_mc = schema + "markup-compatibility/2006"
6243 ms_schema = "http://schemas.microsoft.com/"
6244 xmlns_x14ac = ms_schema + "office/spreadsheetml/2009/9/ac"
6246 attributes = [("xmlns", xmlns), ("xmlns:r", xmlns_r)]
6248 # Add some extra attributes for Excel 2010. Mainly for sparklines.
6249 if self.excel_version == 2010:
6250 attributes.append(("xmlns:mc", xmlns_mc))
6251 attributes.append(("xmlns:x14ac", xmlns_x14ac))
6252 attributes.append(("mc:Ignorable", "x14ac"))
6254 self._xml_start_tag("worksheet", attributes)
6256 def _write_dimension(self) -> None:
6257 # Write the <dimension> element. This specifies the range of
6258 # cells in the worksheet. As a special case, empty
6259 # spreadsheets use 'A1' as a range.
6261 if self.dim_rowmin is None and self.dim_colmin is None:
6262 # If the min dimensions are not defined then no dimensions
6263 # have been set and we use the default 'A1'.
6264 ref = "A1"
6266 elif self.dim_rowmin is None and self.dim_colmin is not None:
6267 # If the row dimensions aren't set but the column
6268 # dimensions are set then they have been changed via
6269 # set_column().
6271 if self.dim_colmin == self.dim_colmax:
6272 # The dimensions are a single cell and not a range.
6273 ref = xl_rowcol_to_cell(0, self.dim_colmin)
6274 else:
6275 # The dimensions are a cell range.
6276 cell_1 = xl_rowcol_to_cell(0, self.dim_colmin)
6277 cell_2 = xl_rowcol_to_cell(0, self.dim_colmax)
6278 ref = cell_1 + ":" + cell_2
6280 elif self.dim_rowmin == self.dim_rowmax and self.dim_colmin == self.dim_colmax:
6281 # The dimensions are a single cell and not a range.
6282 ref = xl_rowcol_to_cell(self.dim_rowmin, self.dim_colmin)
6283 else:
6284 # The dimensions are a cell range.
6285 cell_1 = xl_rowcol_to_cell(self.dim_rowmin, self.dim_colmin)
6286 cell_2 = xl_rowcol_to_cell(self.dim_rowmax, self.dim_colmax)
6287 ref = cell_1 + ":" + cell_2
6289 self._xml_empty_tag("dimension", [("ref", ref)])
6291 def _write_sheet_views(self) -> None:
6292 # Write the <sheetViews> element.
6293 self._xml_start_tag("sheetViews")
6295 # Write the sheetView element.
6296 self._write_sheet_view()
6298 self._xml_end_tag("sheetViews")
6300 def _write_sheet_view(self) -> None:
6301 # Write the <sheetViews> element.
6302 attributes = []
6304 # Hide screen gridlines if required.
6305 if not self.screen_gridlines:
6306 attributes.append(("showGridLines", 0))
6308 # Hide screen row/column headers.
6309 if self.row_col_headers:
6310 attributes.append(("showRowColHeaders", 0))
6312 # Hide zeroes in cells.
6313 if not self.show_zeros:
6314 attributes.append(("showZeros", 0))
6316 # Display worksheet right to left for Hebrew, Arabic and others.
6317 if self.is_right_to_left:
6318 attributes.append(("rightToLeft", 1))
6320 # Show that the sheet tab is selected.
6321 if self.selected:
6322 attributes.append(("tabSelected", 1))
6324 # Turn outlines off. Also required in the outlinePr element.
6325 if not self.outline_on:
6326 attributes.append(("showOutlineSymbols", 0))
6328 # Set the page view/layout mode if required.
6329 if self.page_view == 1:
6330 attributes.append(("view", "pageLayout"))
6331 elif self.page_view == 2:
6332 attributes.append(("view", "pageBreakPreview"))
6334 # Set the first visible cell.
6335 if self.top_left_cell != "":
6336 attributes.append(("topLeftCell", self.top_left_cell))
6338 # Set the zoom level.
6339 if self.zoom != 100:
6340 attributes.append(("zoomScale", self.zoom))
6342 if self.page_view == 0 and self.zoom_scale_normal:
6343 attributes.append(("zoomScaleNormal", self.zoom))
6344 if self.page_view == 1:
6345 attributes.append(("zoomScalePageLayoutView", self.zoom))
6346 if self.page_view == 2:
6347 attributes.append(("zoomScaleSheetLayoutView", self.zoom))
6349 attributes.append(("workbookViewId", 0))
6351 if self.is_chartsheet and self.zoom_to_fit:
6352 attributes.append(("zoomToFit", 1))
6354 if self.panes or self.selections:
6355 self._xml_start_tag("sheetView", attributes)
6356 self._write_panes()
6357 self._write_selections()
6358 self._xml_end_tag("sheetView")
6359 else:
6360 self._xml_empty_tag("sheetView", attributes)
6362 def _write_sheet_format_pr(self) -> None:
6363 # Write the <sheetFormatPr> element.
6364 default_row_height = self.default_row_height
6365 row_level = self.outline_row_level
6366 col_level = self.outline_col_level
6368 attributes = [("defaultRowHeight", default_row_height)]
6370 if self.default_row_height != self.original_row_height:
6371 attributes.append(("customHeight", 1))
6373 if self.default_row_zeroed:
6374 attributes.append(("zeroHeight", 1))
6376 if row_level:
6377 attributes.append(("outlineLevelRow", row_level))
6378 if col_level:
6379 attributes.append(("outlineLevelCol", col_level))
6381 if self.excel_version == 2010:
6382 attributes.append(("x14ac:dyDescent", "0.25"))
6384 self._xml_empty_tag("sheetFormatPr", attributes)
6386 def _write_cols(self) -> None:
6387 # Write the <cols> element and <col> sub elements.
6389 # Exit unless some column have been formatted.
6390 if not self.col_info:
6391 return
6393 self._xml_start_tag("cols")
6395 # Use the first element of the column information structures to set
6396 # the initial/previous properties.
6397 first_col = (sorted(self.col_info.keys()))[0]
6398 last_col = first_col
6399 prev_col_options = self.col_info[first_col]
6400 del self.col_info[first_col]
6401 deleted_col = first_col
6402 deleted_col_options = prev_col_options
6404 for col in sorted(self.col_info.keys()):
6405 col_options = self.col_info[col]
6406 # Check if the column number is contiguous with the previous
6407 # column and if the properties are the same.
6408 if col == last_col + 1 and col_options == prev_col_options:
6409 last_col = col
6410 else:
6411 # If not contiguous/equal then we write out the current range
6412 # of columns and start again.
6413 self._write_col_info(first_col, last_col, prev_col_options)
6414 first_col = col
6415 last_col = first_col
6416 prev_col_options = col_options
6418 # We will exit the previous loop with one unhandled column range.
6419 self._write_col_info(first_col, last_col, prev_col_options)
6421 # Put back the deleted first column information structure.
6422 self.col_info[deleted_col] = deleted_col_options
6424 self._xml_end_tag("cols")
6426 def _write_col_info(self, col_min, col_max, col_info) -> None:
6427 # Write the <col> element.
6428 (width, cell_format, hidden, level, collapsed, autofit) = col_info
6430 custom_width = 1
6431 xf_index = 0
6433 # Get the cell_format index.
6434 if cell_format:
6435 xf_index = cell_format._get_xf_index()
6437 # Set the Excel default column width.
6438 if width is None:
6439 if not hidden:
6440 width = 8.43
6441 custom_width = 0
6442 else:
6443 width = 0
6444 elif width == 8.43:
6445 # Width is defined but same as default.
6446 custom_width = 0
6448 # Convert column width from user units to character width.
6449 if width > 0:
6450 # For Calabri 11.
6451 max_digit_width = 7
6452 padding = 5
6454 if width < 1:
6455 width = (
6456 int(
6457 (int(width * (max_digit_width + padding) + 0.5))
6458 / float(max_digit_width)
6459 * 256.0
6460 )
6461 / 256.0
6462 )
6463 else:
6464 width = (
6465 int(
6466 (int(width * max_digit_width + 0.5) + padding)
6467 / float(max_digit_width)
6468 * 256.0
6469 )
6470 / 256.0
6471 )
6473 attributes = [
6474 ("min", col_min + 1),
6475 ("max", col_max + 1),
6476 ("width", f"{width:.16g}"),
6477 ]
6479 if xf_index:
6480 attributes.append(("style", xf_index))
6481 if hidden:
6482 attributes.append(("hidden", "1"))
6483 if autofit:
6484 attributes.append(("bestFit", "1"))
6485 if custom_width:
6486 attributes.append(("customWidth", "1"))
6487 if level:
6488 attributes.append(("outlineLevel", level))
6489 if collapsed:
6490 attributes.append(("collapsed", "1"))
6492 self._xml_empty_tag("col", attributes)
6494 def _write_sheet_data(self) -> None:
6495 # Write the <sheetData> element.
6496 if self.dim_rowmin is None:
6497 # If the dimensions aren't defined there is no data to write.
6498 self._xml_empty_tag("sheetData")
6499 else:
6500 self._xml_start_tag("sheetData")
6501 self._write_rows()
6502 self._xml_end_tag("sheetData")
6504 def _write_optimized_sheet_data(self) -> None:
6505 # Write the <sheetData> element when constant_memory is on. In this
6506 # case we read the data stored in the temp file and rewrite it to the
6507 # XML sheet file.
6508 if self.dim_rowmin is None:
6509 # If the dimensions aren't defined then there is no data to write.
6510 self._xml_empty_tag("sheetData")
6511 else:
6512 self._xml_start_tag("sheetData")
6514 # Rewind the filehandle that was used for temp row data.
6515 buff_size = 65536
6516 self.row_data_fh.seek(0)
6517 data = self.row_data_fh.read(buff_size)
6519 while data:
6520 self.fh.write(data)
6521 data = self.row_data_fh.read(buff_size)
6523 self.row_data_fh.close()
6524 os.unlink(self.row_data_filename)
6526 self._xml_end_tag("sheetData")
6528 def _write_page_margins(self) -> None:
6529 # Write the <pageMargins> element.
6530 attributes = [
6531 ("left", self.margin_left),
6532 ("right", self.margin_right),
6533 ("top", self.margin_top),
6534 ("bottom", self.margin_bottom),
6535 ("header", self.margin_header),
6536 ("footer", self.margin_footer),
6537 ]
6539 self._xml_empty_tag("pageMargins", attributes)
6541 def _write_page_setup(self) -> None:
6542 # Write the <pageSetup> element.
6543 #
6544 # The following is an example taken from Excel.
6545 #
6546 # <pageSetup
6547 # paperSize="9"
6548 # scale="110"
6549 # fitToWidth="2"
6550 # fitToHeight="2"
6551 # pageOrder="overThenDown"
6552 # orientation="portrait"
6553 # blackAndWhite="1"
6554 # draft="1"
6555 # horizontalDpi="200"
6556 # verticalDpi="200"
6557 # r:id="rId1"
6558 # />
6559 #
6560 attributes = []
6562 # Skip this element if no page setup has changed.
6563 if not self.page_setup_changed:
6564 return
6566 # Set paper size.
6567 if self.paper_size:
6568 attributes.append(("paperSize", self.paper_size))
6570 # Set the print_scale.
6571 if self.print_scale != 100:
6572 attributes.append(("scale", self.print_scale))
6574 # Set the "Fit to page" properties.
6575 if self.fit_page and self.fit_width != 1:
6576 attributes.append(("fitToWidth", self.fit_width))
6578 if self.fit_page and self.fit_height != 1:
6579 attributes.append(("fitToHeight", self.fit_height))
6581 # Set the page print direction.
6582 if self.page_order:
6583 attributes.append(("pageOrder", "overThenDown"))
6585 # Set start page for printing.
6586 if self.page_start > 1:
6587 attributes.append(("firstPageNumber", self.page_start))
6589 # Set page orientation.
6590 if self.orientation:
6591 attributes.append(("orientation", "portrait"))
6592 else:
6593 attributes.append(("orientation", "landscape"))
6595 # Set the print in black and white option.
6596 if self.black_white:
6597 attributes.append(("blackAndWhite", "1"))
6599 # Set start page for printing.
6600 if self.page_start != 0:
6601 attributes.append(("useFirstPageNumber", "1"))
6603 # Set the DPI. Mainly only for testing.
6604 if self.is_chartsheet:
6605 if self.horizontal_dpi:
6606 attributes.append(("horizontalDpi", self.horizontal_dpi))
6608 if self.vertical_dpi:
6609 attributes.append(("verticalDpi", self.vertical_dpi))
6610 else:
6611 if self.vertical_dpi:
6612 attributes.append(("verticalDpi", self.vertical_dpi))
6614 if self.horizontal_dpi:
6615 attributes.append(("horizontalDpi", self.horizontal_dpi))
6617 self._xml_empty_tag("pageSetup", attributes)
6619 def _write_print_options(self) -> None:
6620 # Write the <printOptions> element.
6621 attributes = []
6623 if not self.print_options_changed:
6624 return
6626 # Set horizontal centering.
6627 if self.hcenter:
6628 attributes.append(("horizontalCentered", 1))
6630 # Set vertical centering.
6631 if self.vcenter:
6632 attributes.append(("verticalCentered", 1))
6634 # Enable row and column headers.
6635 if self.print_headers:
6636 attributes.append(("headings", 1))
6638 # Set printed gridlines.
6639 if self.print_gridlines:
6640 attributes.append(("gridLines", 1))
6642 self._xml_empty_tag("printOptions", attributes)
6644 def _write_header_footer(self) -> None:
6645 # Write the <headerFooter> element.
6646 attributes = []
6648 if not self.header_footer_scales:
6649 attributes.append(("scaleWithDoc", 0))
6651 if not self.header_footer_aligns:
6652 attributes.append(("alignWithMargins", 0))
6654 if self.header_footer_changed:
6655 self._xml_start_tag("headerFooter", attributes)
6656 if self.header:
6657 self._write_odd_header()
6658 if self.footer:
6659 self._write_odd_footer()
6660 self._xml_end_tag("headerFooter")
6661 elif self.excel2003_style:
6662 self._xml_empty_tag("headerFooter", attributes)
6664 def _write_odd_header(self) -> None:
6665 # Write the <headerFooter> element.
6666 self._xml_data_element("oddHeader", self.header)
6668 def _write_odd_footer(self) -> None:
6669 # Write the <headerFooter> element.
6670 self._xml_data_element("oddFooter", self.footer)
6672 def _write_rows(self) -> None:
6673 # Write out the worksheet data as a series of rows and cells.
6674 self._calculate_spans()
6676 for row_num in range(self.dim_rowmin, self.dim_rowmax + 1):
6677 if (
6678 row_num in self.set_rows
6679 or row_num in self.comments
6680 or self.table[row_num]
6681 ):
6682 # Only process rows with formatting, cell data and/or comments.
6684 span_index = int(row_num / 16)
6686 if span_index in self.row_spans:
6687 span = self.row_spans[span_index]
6688 else:
6689 span = None
6691 if self.table[row_num]:
6692 # Write the cells if the row contains data.
6693 if row_num not in self.set_rows:
6694 self._write_row(row_num, span)
6695 else:
6696 self._write_row(row_num, span, self.set_rows[row_num])
6698 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6699 if col_num in self.table[row_num]:
6700 col_ref = self.table[row_num][col_num]
6701 self._write_cell(row_num, col_num, col_ref)
6703 self._xml_end_tag("row")
6705 elif row_num in self.comments:
6706 # Row with comments in cells.
6707 self._write_empty_row(row_num, span, self.set_rows[row_num])
6708 else:
6709 # Blank row with attributes only.
6710 self._write_empty_row(row_num, span, self.set_rows[row_num])
6712 def _write_single_row(self, current_row_num=0) -> None:
6713 # Write out the worksheet data as a single row with cells.
6714 # This method is used when constant_memory is on. A single
6715 # row is written and the data table is reset. That way only
6716 # one row of data is kept in memory at any one time. We don't
6717 # write span data in the optimized case since it is optional.
6719 # Set the new previous row as the current row.
6720 row_num = self.previous_row
6721 self.previous_row = current_row_num
6723 if row_num in self.set_rows or row_num in self.comments or self.table[row_num]:
6724 # Only process rows with formatting, cell data and/or comments.
6726 # No span data in optimized mode.
6727 span = None
6729 if self.table[row_num]:
6730 # Write the cells if the row contains data.
6731 if row_num not in self.set_rows:
6732 self._write_row(row_num, span)
6733 else:
6734 self._write_row(row_num, span, self.set_rows[row_num])
6736 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6737 if col_num in self.table[row_num]:
6738 col_ref = self.table[row_num][col_num]
6739 self._write_cell(row_num, col_num, col_ref)
6741 self._xml_end_tag("row")
6742 else:
6743 # Row attributes or comments only.
6744 self._write_empty_row(row_num, span, self.set_rows[row_num])
6746 # Reset table.
6747 self.table.clear()
6749 def _calculate_spans(self) -> None:
6750 # Calculate the "spans" attribute of the <row> tag. This is an
6751 # XLSX optimization and isn't strictly required. However, it
6752 # makes comparing files easier. The span is the same for each
6753 # block of 16 rows.
6754 spans = {}
6755 span_min = None
6756 span_max = None
6758 for row_num in range(self.dim_rowmin, self.dim_rowmax + 1):
6759 if row_num in self.table:
6760 # Calculate spans for cell data.
6761 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6762 if col_num in self.table[row_num]:
6763 if span_min is None:
6764 span_min = col_num
6765 span_max = col_num
6766 else:
6767 span_min = min(span_min, col_num)
6768 span_max = max(span_max, col_num)
6770 if row_num in self.comments:
6771 # Calculate spans for comments.
6772 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6773 if row_num in self.comments and col_num in self.comments[row_num]:
6774 if span_min is None:
6775 span_min = col_num
6776 span_max = col_num
6777 else:
6778 span_min = min(span_min, col_num)
6779 span_max = max(span_max, col_num)
6781 if ((row_num + 1) % 16 == 0) or row_num == self.dim_rowmax:
6782 span_index = int(row_num / 16)
6784 if span_min is not None:
6785 span_min += 1
6786 span_max += 1
6787 spans[span_index] = f"{span_min}:{span_max}"
6788 span_min = None
6790 self.row_spans = spans
6792 def _write_row(self, row: int, spans, properties=None, empty_row=False) -> None:
6793 # Write the <row> element.
6794 xf_index = 0
6796 if properties:
6797 height, cell_format, hidden, level, collapsed = properties
6798 else:
6799 height, cell_format, hidden, level, collapsed = None, None, 0, 0, 0
6801 if height is None:
6802 height = self.default_row_height
6804 attributes = [("r", row + 1)]
6806 # Get the cell_format index.
6807 if cell_format:
6808 xf_index = cell_format._get_xf_index()
6810 # Add row attributes where applicable.
6811 if spans:
6812 attributes.append(("spans", spans))
6814 if xf_index:
6815 attributes.append(("s", xf_index))
6817 if cell_format:
6818 attributes.append(("customFormat", 1))
6820 if height != self.original_row_height or (
6821 height == self.original_row_height and height != self.default_row_height
6822 ):
6823 attributes.append(("ht", f"{height:g}"))
6825 if hidden:
6826 attributes.append(("hidden", 1))
6828 if height != self.original_row_height or (
6829 height == self.original_row_height and height != self.default_row_height
6830 ):
6831 attributes.append(("customHeight", 1))
6833 if level:
6834 attributes.append(("outlineLevel", level))
6836 if collapsed:
6837 attributes.append(("collapsed", 1))
6839 if self.excel_version == 2010:
6840 attributes.append(("x14ac:dyDescent", "0.25"))
6842 if empty_row:
6843 self._xml_empty_tag_unencoded("row", attributes)
6844 else:
6845 self._xml_start_tag_unencoded("row", attributes)
6847 def _write_empty_row(self, row: int, spans, properties=None) -> None:
6848 # Write and empty <row> element.
6849 self._write_row(row, spans, properties, empty_row=True)
6851 def _write_cell(self, row: int, col: int, cell) -> None:
6852 # Write the <cell> element.
6853 # Note. This is the innermost loop so efficiency is important.
6855 cell_range = xl_rowcol_to_cell_fast(row, col)
6856 attributes = [("r", cell_range)]
6858 if cell.format:
6859 # Add the cell format index.
6860 xf_index = cell.format._get_xf_index()
6861 attributes.append(("s", xf_index))
6862 elif row in self.set_rows and self.set_rows[row][1]:
6863 # Add the row format.
6864 row_xf = self.set_rows[row][1]
6865 attributes.append(("s", row_xf._get_xf_index()))
6866 elif col in self.col_info:
6867 # Add the column format.
6868 col_xf = self.col_info[col][1]
6869 if col_xf is not None:
6870 attributes.append(("s", col_xf._get_xf_index()))
6872 type_cell_name = cell.__class__.__name__
6874 # Write the various cell types.
6875 if type_cell_name in ("Number", "Datetime"):
6876 # Write a number.
6877 self._xml_number_element(cell.number, attributes)
6879 elif type_cell_name in ("String", "RichString"):
6880 # Write a string.
6881 string = cell.string
6883 if not self.constant_memory:
6884 # Write a shared string.
6885 self._xml_string_element(string, attributes)
6886 else:
6887 # Write an optimized in-line string.
6889 # Convert control character to a _xHHHH_ escape.
6890 string = self._escape_control_characters(string)
6892 # Write any rich strings without further tags.
6893 if string.startswith("<r>") and string.endswith("</r>"):
6894 self._xml_rich_inline_string(string, attributes)
6895 else:
6896 # Add attribute to preserve leading or trailing whitespace.
6897 preserve = _preserve_whitespace(string)
6898 self._xml_inline_string(string, preserve, attributes)
6900 elif type_cell_name == "Formula":
6901 # Write a formula. First check the formula value type.
6902 value = cell.value
6903 if isinstance(cell.value, bool):
6904 attributes.append(("t", "b"))
6905 if cell.value:
6906 value = 1
6907 else:
6908 value = 0
6910 elif isinstance(cell.value, str):
6911 error_codes = (
6912 "#DIV/0!",
6913 "#N/A",
6914 "#NAME?",
6915 "#NULL!",
6916 "#NUM!",
6917 "#REF!",
6918 "#VALUE!",
6919 )
6921 if cell.value == "":
6922 # Allow blank to force recalc in some third party apps.
6923 pass
6924 elif cell.value in error_codes:
6925 attributes.append(("t", "e"))
6926 else:
6927 attributes.append(("t", "str"))
6929 self._xml_formula_element(cell.formula, value, attributes)
6931 elif type_cell_name == "ArrayFormula":
6932 # Write a array formula.
6934 if cell.atype == "dynamic":
6935 attributes.append(("cm", 1))
6937 # First check if the formula value is a string.
6938 try:
6939 float(cell.value)
6940 except ValueError:
6941 attributes.append(("t", "str"))
6943 # Write an array formula.
6944 self._xml_start_tag("c", attributes)
6946 self._write_cell_array_formula(cell.formula, cell.range)
6947 self._write_cell_value(cell.value)
6948 self._xml_end_tag("c")
6950 elif type_cell_name == "Blank":
6951 # Write a empty cell.
6952 self._xml_empty_tag("c", attributes)
6954 elif type_cell_name == "Boolean":
6955 # Write a boolean cell.
6956 attributes.append(("t", "b"))
6957 self._xml_start_tag("c", attributes)
6958 self._write_cell_value(cell.boolean)
6959 self._xml_end_tag("c")
6961 elif type_cell_name == "Error":
6962 # Write a boolean cell.
6963 attributes.append(("t", "e"))
6964 attributes.append(("vm", cell.value))
6965 self._xml_start_tag("c", attributes)
6966 self._write_cell_value(cell.error)
6967 self._xml_end_tag("c")
6969 def _write_cell_value(self, value) -> None:
6970 # Write the cell value <v> element.
6971 if value is None:
6972 value = ""
6974 self._xml_data_element("v", value)
6976 def _write_cell_array_formula(self, formula, cell_range) -> None:
6977 # Write the cell array formula <f> element.
6978 attributes = [("t", "array"), ("ref", cell_range)]
6980 self._xml_data_element("f", formula, attributes)
6982 def _write_sheet_pr(self) -> None:
6983 # Write the <sheetPr> element for Sheet level properties.
6984 attributes = []
6986 if (
6987 not self.fit_page
6988 and not self.filter_on
6989 and not self.tab_color
6990 and not self.outline_changed
6991 and not self.vba_codename
6992 ):
6993 return
6995 if self.vba_codename:
6996 attributes.append(("codeName", self.vba_codename))
6998 if self.filter_on:
6999 attributes.append(("filterMode", 1))
7001 if self.fit_page or self.tab_color or self.outline_changed:
7002 self._xml_start_tag("sheetPr", attributes)
7003 self._write_tab_color()
7004 self._write_outline_pr()
7005 self._write_page_set_up_pr()
7006 self._xml_end_tag("sheetPr")
7007 else:
7008 self._xml_empty_tag("sheetPr", attributes)
7010 def _write_page_set_up_pr(self) -> None:
7011 # Write the <pageSetUpPr> element.
7012 if not self.fit_page:
7013 return
7015 attributes = [("fitToPage", 1)]
7016 self._xml_empty_tag("pageSetUpPr", attributes)
7018 def _write_tab_color(self) -> None:
7019 # Write the <tabColor> element.
7020 color = self.tab_color
7022 if not color:
7023 return
7025 self._write_color("tabColor", color._attributes())
7027 def _write_outline_pr(self) -> None:
7028 # Write the <outlinePr> element.
7029 attributes = []
7031 if not self.outline_changed:
7032 return
7034 if self.outline_style:
7035 attributes.append(("applyStyles", 1))
7036 if not self.outline_below:
7037 attributes.append(("summaryBelow", 0))
7038 if not self.outline_right:
7039 attributes.append(("summaryRight", 0))
7040 if not self.outline_on:
7041 attributes.append(("showOutlineSymbols", 0))
7043 self._xml_empty_tag("outlinePr", attributes)
7045 def _write_row_breaks(self) -> None:
7046 # Write the <rowBreaks> element.
7047 page_breaks = self._sort_pagebreaks(self.hbreaks)
7049 if not page_breaks:
7050 return
7052 count = len(page_breaks)
7054 attributes = [
7055 ("count", count),
7056 ("manualBreakCount", count),
7057 ]
7059 self._xml_start_tag("rowBreaks", attributes)
7061 for row_num in page_breaks:
7062 self._write_brk(row_num, 16383)
7064 self._xml_end_tag("rowBreaks")
7066 def _write_col_breaks(self) -> None:
7067 # Write the <colBreaks> element.
7068 page_breaks = self._sort_pagebreaks(self.vbreaks)
7070 if not page_breaks:
7071 return
7073 count = len(page_breaks)
7075 attributes = [
7076 ("count", count),
7077 ("manualBreakCount", count),
7078 ]
7080 self._xml_start_tag("colBreaks", attributes)
7082 for col_num in page_breaks:
7083 self._write_brk(col_num, 1048575)
7085 self._xml_end_tag("colBreaks")
7087 def _write_brk(self, brk_id, brk_max) -> None:
7088 # Write the <brk> element.
7089 attributes = [("id", brk_id), ("max", brk_max), ("man", 1)]
7091 self._xml_empty_tag("brk", attributes)
7093 def _write_merge_cells(self) -> None:
7094 # Write the <mergeCells> element.
7095 merged_cells = self.merge
7096 count = len(merged_cells)
7098 if not count:
7099 return
7101 attributes = [("count", count)]
7103 self._xml_start_tag("mergeCells", attributes)
7105 for merged_range in merged_cells:
7106 # Write the mergeCell element.
7107 self._write_merge_cell(merged_range)
7109 self._xml_end_tag("mergeCells")
7111 def _write_merge_cell(self, merged_range) -> None:
7112 # Write the <mergeCell> element.
7113 (row_min, col_min, row_max, col_max) = merged_range
7115 # Convert the merge dimensions to a cell range.
7116 cell_1 = xl_rowcol_to_cell(row_min, col_min)
7117 cell_2 = xl_rowcol_to_cell(row_max, col_max)
7118 ref = cell_1 + ":" + cell_2
7120 attributes = [("ref", ref)]
7122 self._xml_empty_tag("mergeCell", attributes)
7124 def _write_hyperlinks(self) -> None:
7125 # Process any stored hyperlinks in row/col order and write the
7126 # <hyperlinks> element. The attributes are different for internal
7127 # and external links.
7129 # Sort the hyperlinks into row order.
7130 row_nums = sorted(self.hyperlinks.keys())
7132 # Exit if there are no hyperlinks to process.
7133 if not row_nums:
7134 return
7136 # Write the hyperlink elements.
7137 self._xml_start_tag("hyperlinks")
7139 # Iterate over the rows.
7140 for row_num in row_nums:
7141 # Sort the hyperlinks into column order.
7142 col_nums = sorted(self.hyperlinks[row_num].keys())
7144 # Iterate over the columns.
7145 for col_num in col_nums:
7146 # Get the link data for this cell.
7147 url = self.hyperlinks[row_num][col_num]
7149 # If the cell was overwritten by the user and isn't a string
7150 # then we have to add the url as the string to display.
7151 if self.table and self.table[row_num] and self.table[row_num][col_num]:
7152 cell = self.table[row_num][col_num]
7153 if cell.__class__.__name__ != "String":
7154 url._is_object_link = True
7156 if url._link_type in (UrlTypes.URL, UrlTypes.EXTERNAL):
7157 # External link with rel file relationship.
7158 self.rel_count += 1
7160 self._write_hyperlink_external(
7161 row_num, col_num, self.rel_count, url
7162 )
7164 # Links for use by the packager.
7165 self.external_hyper_links.append(
7166 ["/hyperlink", url._target(), "External"]
7167 )
7168 else:
7169 # Internal link with rel file relationship.
7170 self._write_hyperlink_internal(row_num, col_num, url)
7172 self._xml_end_tag("hyperlinks")
7174 def _write_hyperlink_external(
7175 self, row: int, col: int, id_num: int, url: Url
7176 ) -> None:
7177 # Write the <hyperlink> element for external links.
7178 ref = xl_rowcol_to_cell(row, col)
7179 r_id = "rId" + str(id_num)
7181 attributes = [("ref", ref), ("r:id", r_id)]
7183 if url._anchor:
7184 attributes.append(("location", url._anchor))
7186 if url._is_object_link:
7187 attributes.append(("display", url._text))
7189 if url._tip:
7190 attributes.append(("tooltip", url._tip))
7192 self._xml_empty_tag("hyperlink", attributes)
7194 def _write_hyperlink_internal(self, row: int, col: int, url: Url) -> None:
7195 # Write the <hyperlink> element for internal links.
7196 ref = xl_rowcol_to_cell(row, col)
7198 attributes = [("ref", ref), ("location", url._link)]
7200 if url._tip:
7201 attributes.append(("tooltip", url._tip))
7203 attributes.append(("display", url._text))
7205 self._xml_empty_tag("hyperlink", attributes)
7207 def _write_auto_filter(self) -> None:
7208 # Write the <autoFilter> element.
7209 if not self.autofilter_ref:
7210 return
7212 attributes = [("ref", self.autofilter_ref)]
7214 if self.filter_on:
7215 # Autofilter defined active filters.
7216 self._xml_start_tag("autoFilter", attributes)
7217 self._write_autofilters()
7218 self._xml_end_tag("autoFilter")
7220 else:
7221 # Autofilter defined without active filters.
7222 self._xml_empty_tag("autoFilter", attributes)
7224 def _write_autofilters(self) -> None:
7225 # Function to iterate through the columns that form part of an
7226 # autofilter range and write the appropriate filters.
7227 (col1, col2) = self.filter_range
7229 for col in range(col1, col2 + 1):
7230 # Skip if column doesn't have an active filter.
7231 if col not in self.filter_cols:
7232 continue
7234 # Retrieve the filter tokens and write the autofilter records.
7235 tokens = self.filter_cols[col]
7236 filter_type = self.filter_type[col]
7238 # Filters are relative to first column in the autofilter.
7239 self._write_filter_column(col - col1, filter_type, tokens)
7241 def _write_filter_column(self, col_id, filter_type, filters) -> None:
7242 # Write the <filterColumn> element.
7243 attributes = [("colId", col_id)]
7245 self._xml_start_tag("filterColumn", attributes)
7247 if filter_type == 1:
7248 # Type == 1 is the new XLSX style filter.
7249 self._write_filters(filters)
7250 else:
7251 # Type == 0 is the classic "custom" filter.
7252 self._write_custom_filters(filters)
7254 self._xml_end_tag("filterColumn")
7256 def _write_filters(self, filters) -> None:
7257 # Write the <filters> element.
7258 non_blanks = [filter for filter in filters if str(filter).lower() != "blanks"]
7259 attributes = []
7261 if len(filters) != len(non_blanks):
7262 attributes = [("blank", 1)]
7264 if len(filters) == 1 and len(non_blanks) == 0:
7265 # Special case for blank cells only.
7266 self._xml_empty_tag("filters", attributes)
7267 else:
7268 # General case.
7269 self._xml_start_tag("filters", attributes)
7271 for autofilter in sorted(non_blanks):
7272 self._write_filter(autofilter)
7274 self._xml_end_tag("filters")
7276 def _write_filter(self, val) -> None:
7277 # Write the <filter> element.
7278 attributes = [("val", val)]
7280 self._xml_empty_tag("filter", attributes)
7282 def _write_custom_filters(self, tokens) -> None:
7283 # Write the <customFilters> element.
7284 if len(tokens) == 2:
7285 # One filter expression only.
7286 self._xml_start_tag("customFilters")
7287 self._write_custom_filter(*tokens)
7288 self._xml_end_tag("customFilters")
7289 else:
7290 # Two filter expressions.
7291 attributes = []
7293 # Check if the "join" operand is "and" or "or".
7294 if tokens[2] == 0:
7295 attributes = [("and", 1)]
7296 else:
7297 attributes = [("and", 0)]
7299 # Write the two custom filters.
7300 self._xml_start_tag("customFilters", attributes)
7301 self._write_custom_filter(tokens[0], tokens[1])
7302 self._write_custom_filter(tokens[3], tokens[4])
7303 self._xml_end_tag("customFilters")
7305 def _write_custom_filter(self, operator, val) -> None:
7306 # Write the <customFilter> element.
7307 attributes = []
7309 operators = {
7310 1: "lessThan",
7311 2: "equal",
7312 3: "lessThanOrEqual",
7313 4: "greaterThan",
7314 5: "notEqual",
7315 6: "greaterThanOrEqual",
7316 22: "equal",
7317 }
7319 # Convert the operator from a number to a descriptive string.
7320 if operators[operator] is not None:
7321 operator = operators[operator]
7322 else:
7323 warn(f"Unknown operator = {operator}")
7325 # The 'equal' operator is the default attribute and isn't stored.
7326 if operator != "equal":
7327 attributes.append(("operator", operator))
7328 attributes.append(("val", val))
7330 self._xml_empty_tag("customFilter", attributes)
7332 def _write_sheet_protection(self) -> None:
7333 # Write the <sheetProtection> element.
7334 attributes = []
7336 if not self.protect_options:
7337 return
7339 options = self.protect_options
7341 if options["password"]:
7342 attributes.append(("password", options["password"]))
7343 if options["sheet"]:
7344 attributes.append(("sheet", 1))
7345 if options["content"]:
7346 attributes.append(("content", 1))
7347 if not options["objects"]:
7348 attributes.append(("objects", 1))
7349 if not options["scenarios"]:
7350 attributes.append(("scenarios", 1))
7351 if options["format_cells"]:
7352 attributes.append(("formatCells", 0))
7353 if options["format_columns"]:
7354 attributes.append(("formatColumns", 0))
7355 if options["format_rows"]:
7356 attributes.append(("formatRows", 0))
7357 if options["insert_columns"]:
7358 attributes.append(("insertColumns", 0))
7359 if options["insert_rows"]:
7360 attributes.append(("insertRows", 0))
7361 if options["insert_hyperlinks"]:
7362 attributes.append(("insertHyperlinks", 0))
7363 if options["delete_columns"]:
7364 attributes.append(("deleteColumns", 0))
7365 if options["delete_rows"]:
7366 attributes.append(("deleteRows", 0))
7367 if not options["select_locked_cells"]:
7368 attributes.append(("selectLockedCells", 1))
7369 if options["sort"]:
7370 attributes.append(("sort", 0))
7371 if options["autofilter"]:
7372 attributes.append(("autoFilter", 0))
7373 if options["pivot_tables"]:
7374 attributes.append(("pivotTables", 0))
7375 if not options["select_unlocked_cells"]:
7376 attributes.append(("selectUnlockedCells", 1))
7378 self._xml_empty_tag("sheetProtection", attributes)
7380 def _write_protected_ranges(self) -> None:
7381 # Write the <protectedRanges> element.
7382 if self.num_protected_ranges == 0:
7383 return
7385 self._xml_start_tag("protectedRanges")
7387 for cell_range, range_name, password in self.protected_ranges:
7388 self._write_protected_range(cell_range, range_name, password)
7390 self._xml_end_tag("protectedRanges")
7392 def _write_protected_range(self, cell_range, range_name, password) -> None:
7393 # Write the <protectedRange> element.
7394 attributes = []
7396 if password:
7397 attributes.append(("password", password))
7399 attributes.append(("sqref", cell_range))
7400 attributes.append(("name", range_name))
7402 self._xml_empty_tag("protectedRange", attributes)
7404 def _write_drawings(self) -> None:
7405 # Write the <drawing> elements.
7406 if not self.drawing:
7407 return
7409 self.rel_count += 1
7410 self._write_drawing(self.rel_count)
7412 def _write_drawing(self, drawing_id) -> None:
7413 # Write the <drawing> element.
7414 r_id = "rId" + str(drawing_id)
7416 attributes = [("r:id", r_id)]
7418 self._xml_empty_tag("drawing", attributes)
7420 def _write_legacy_drawing(self) -> None:
7421 # Write the <legacyDrawing> element.
7422 if not self.has_vml:
7423 return
7425 # Increment the relationship id for any drawings or comments.
7426 self.rel_count += 1
7427 r_id = "rId" + str(self.rel_count)
7429 attributes = [("r:id", r_id)]
7431 self._xml_empty_tag("legacyDrawing", attributes)
7433 def _write_legacy_drawing_hf(self) -> None:
7434 # Write the <legacyDrawingHF> element.
7435 if not self.has_header_vml:
7436 return
7438 # Increment the relationship id for any drawings or comments.
7439 self.rel_count += 1
7440 r_id = "rId" + str(self.rel_count)
7442 attributes = [("r:id", r_id)]
7444 self._xml_empty_tag("legacyDrawingHF", attributes)
7446 def _write_picture(self) -> None:
7447 # Write the <picture> element.
7448 if not self.background_image:
7449 return
7451 # Increment the relationship id.
7452 self.rel_count += 1
7453 r_id = "rId" + str(self.rel_count)
7455 attributes = [("r:id", r_id)]
7457 self._xml_empty_tag("picture", attributes)
7459 def _write_data_validations(self) -> None:
7460 # Write the <dataValidations> element.
7461 validations = self.validations
7462 count = len(validations)
7464 if not count:
7465 return
7467 attributes = [("count", count)]
7469 self._xml_start_tag("dataValidations", attributes)
7471 for validation in validations:
7472 # Write the dataValidation element.
7473 self._write_data_validation(validation)
7475 self._xml_end_tag("dataValidations")
7477 def _write_data_validation(self, options) -> None:
7478 # Write the <dataValidation> element.
7479 sqref = ""
7480 attributes = []
7482 # Set the cell range(s) for the data validation.
7483 for cells in options["cells"]:
7484 # Add a space between multiple cell ranges.
7485 if sqref != "":
7486 sqref += " "
7488 (row_first, col_first, row_last, col_last) = cells
7490 # Swap last row/col for first row/col as necessary
7491 if row_first > row_last:
7492 (row_first, row_last) = (row_last, row_first)
7494 if col_first > col_last:
7495 (col_first, col_last) = (col_last, col_first)
7497 sqref += xl_range(row_first, col_first, row_last, col_last)
7499 if options.get("multi_range"):
7500 sqref = options["multi_range"]
7502 if options["validate"] != "none":
7503 attributes.append(("type", options["validate"]))
7505 if options["criteria"] != "between":
7506 attributes.append(("operator", options["criteria"]))
7508 if "error_type" in options:
7509 if options["error_type"] == 1:
7510 attributes.append(("errorStyle", "warning"))
7511 if options["error_type"] == 2:
7512 attributes.append(("errorStyle", "information"))
7514 if options["ignore_blank"]:
7515 attributes.append(("allowBlank", 1))
7517 if not options["dropdown"]:
7518 attributes.append(("showDropDown", 1))
7520 if options["show_input"]:
7521 attributes.append(("showInputMessage", 1))
7523 if options["show_error"]:
7524 attributes.append(("showErrorMessage", 1))
7526 if "error_title" in options:
7527 attributes.append(("errorTitle", options["error_title"]))
7529 if "error_message" in options:
7530 attributes.append(("error", options["error_message"]))
7532 if "input_title" in options:
7533 attributes.append(("promptTitle", options["input_title"]))
7535 if "input_message" in options:
7536 attributes.append(("prompt", options["input_message"]))
7538 attributes.append(("sqref", sqref))
7540 if options["validate"] == "none":
7541 self._xml_empty_tag("dataValidation", attributes)
7542 else:
7543 self._xml_start_tag("dataValidation", attributes)
7545 # Write the formula1 element.
7546 self._write_formula_1(options["value"])
7548 # Write the formula2 element.
7549 if options["maximum"] is not None:
7550 self._write_formula_2(options["maximum"])
7552 self._xml_end_tag("dataValidation")
7554 def _write_formula_1(self, formula) -> None:
7555 # Write the <formula1> element.
7557 if isinstance(formula, list):
7558 formula = self._csv_join(*formula)
7559 formula = f'"{formula}"'
7560 else:
7561 # Check if the formula is a number.
7562 try:
7563 float(formula)
7564 except ValueError:
7565 # Not a number. Remove the formula '=' sign if it exists.
7566 if formula.startswith("="):
7567 formula = formula.lstrip("=")
7569 self._xml_data_element("formula1", formula)
7571 def _write_formula_2(self, formula) -> None:
7572 # Write the <formula2> element.
7574 # Check if the formula is a number.
7575 try:
7576 float(formula)
7577 except ValueError:
7578 # Not a number. Remove the formula '=' sign if it exists.
7579 if formula.startswith("="):
7580 formula = formula.lstrip("=")
7582 self._xml_data_element("formula2", formula)
7584 def _write_conditional_formats(self) -> None:
7585 # Write the Worksheet conditional formats.
7586 ranges = sorted(self.cond_formats.keys())
7588 if not ranges:
7589 return
7591 for cond_range in ranges:
7592 self._write_conditional_formatting(
7593 cond_range, self.cond_formats[cond_range]
7594 )
7596 def _write_conditional_formatting(self, cond_range, params) -> None:
7597 # Write the <conditionalFormatting> element.
7598 attributes = [("sqref", cond_range)]
7599 self._xml_start_tag("conditionalFormatting", attributes)
7600 for param in params:
7601 # Write the cfRule element.
7602 self._write_cf_rule(param)
7603 self._xml_end_tag("conditionalFormatting")
7605 def _write_cf_rule(self, params) -> None:
7606 # Write the <cfRule> element.
7607 attributes = [("type", params["type"])]
7609 if "format" in params and params["format"] is not None:
7610 attributes.append(("dxfId", params["format"]))
7612 attributes.append(("priority", params["priority"]))
7614 if params.get("stop_if_true"):
7615 attributes.append(("stopIfTrue", 1))
7617 if params["type"] == "cellIs":
7618 attributes.append(("operator", params["criteria"]))
7620 self._xml_start_tag("cfRule", attributes)
7622 if "minimum" in params and "maximum" in params:
7623 self._write_formula_element(params["minimum"])
7624 self._write_formula_element(params["maximum"])
7625 else:
7626 self._write_formula_element(params["value"])
7628 self._xml_end_tag("cfRule")
7630 elif params["type"] == "aboveAverage":
7631 if re.search("below", params["criteria"]):
7632 attributes.append(("aboveAverage", 0))
7634 if re.search("equal", params["criteria"]):
7635 attributes.append(("equalAverage", 1))
7637 if re.search("[123] std dev", params["criteria"]):
7638 match = re.search("([123]) std dev", params["criteria"])
7639 attributes.append(("stdDev", match.group(1)))
7641 self._xml_empty_tag("cfRule", attributes)
7643 elif params["type"] == "top10":
7644 if "criteria" in params and params["criteria"] == "%":
7645 attributes.append(("percent", 1))
7647 if "direction" in params:
7648 attributes.append(("bottom", 1))
7650 rank = params["value"] or 10
7651 attributes.append(("rank", rank))
7653 self._xml_empty_tag("cfRule", attributes)
7655 elif params["type"] == "duplicateValues":
7656 self._xml_empty_tag("cfRule", attributes)
7658 elif params["type"] == "uniqueValues":
7659 self._xml_empty_tag("cfRule", attributes)
7661 elif (
7662 params["type"] == "containsText"
7663 or params["type"] == "notContainsText"
7664 or params["type"] == "beginsWith"
7665 or params["type"] == "endsWith"
7666 ):
7667 attributes.append(("operator", params["criteria"]))
7668 attributes.append(("text", params["value"]))
7669 self._xml_start_tag("cfRule", attributes)
7670 self._write_formula_element(params["formula"])
7671 self._xml_end_tag("cfRule")
7673 elif params["type"] == "timePeriod":
7674 attributes.append(("timePeriod", params["criteria"]))
7675 self._xml_start_tag("cfRule", attributes)
7676 self._write_formula_element(params["formula"])
7677 self._xml_end_tag("cfRule")
7679 elif (
7680 params["type"] == "containsBlanks"
7681 or params["type"] == "notContainsBlanks"
7682 or params["type"] == "containsErrors"
7683 or params["type"] == "notContainsErrors"
7684 ):
7685 self._xml_start_tag("cfRule", attributes)
7686 self._write_formula_element(params["formula"])
7687 self._xml_end_tag("cfRule")
7689 elif params["type"] == "colorScale":
7690 self._xml_start_tag("cfRule", attributes)
7691 self._write_color_scale(params)
7692 self._xml_end_tag("cfRule")
7694 elif params["type"] == "dataBar":
7695 self._xml_start_tag("cfRule", attributes)
7696 self._write_data_bar(params)
7698 if params.get("is_data_bar_2010"):
7699 self._write_data_bar_ext(params)
7701 self._xml_end_tag("cfRule")
7703 elif params["type"] == "expression":
7704 self._xml_start_tag("cfRule", attributes)
7705 self._write_formula_element(params["criteria"])
7706 self._xml_end_tag("cfRule")
7708 elif params["type"] == "iconSet":
7709 self._xml_start_tag("cfRule", attributes)
7710 self._write_icon_set(params)
7711 self._xml_end_tag("cfRule")
7713 def _write_formula_element(self, formula) -> None:
7714 # Write the <formula> element.
7716 # Check if the formula is a number.
7717 try:
7718 float(formula)
7719 except ValueError:
7720 # Not a number. Remove the formula '=' sign if it exists.
7721 if formula.startswith("="):
7722 formula = formula.lstrip("=")
7724 self._xml_data_element("formula", formula)
7726 def _write_color_scale(self, param) -> None:
7727 # Write the <colorScale> element.
7729 self._xml_start_tag("colorScale")
7731 self._write_cfvo(param["min_type"], param["min_value"])
7733 if param["mid_type"] is not None:
7734 self._write_cfvo(param["mid_type"], param["mid_value"])
7736 self._write_cfvo(param["max_type"], param["max_value"])
7738 self._write_color("color", param["min_color"]._attributes())
7740 if param["mid_color"] is not None:
7741 self._write_color("color", param["mid_color"]._attributes())
7743 self._write_color("color", param["max_color"]._attributes())
7745 self._xml_end_tag("colorScale")
7747 def _write_data_bar(self, param) -> None:
7748 # Write the <dataBar> element.
7749 attributes = []
7751 # Min and max bar lengths in in the spec but not supported directly by
7752 # Excel.
7753 if "min_length" in param:
7754 attributes.append(("minLength", param["min_length"]))
7756 if "max_length" in param:
7757 attributes.append(("maxLength", param["max_length"]))
7759 if param.get("bar_only"):
7760 attributes.append(("showValue", 0))
7762 self._xml_start_tag("dataBar", attributes)
7764 self._write_cfvo(param["min_type"], param["min_value"])
7765 self._write_cfvo(param["max_type"], param["max_value"])
7766 self._write_color("color", param["bar_color"]._attributes())
7768 self._xml_end_tag("dataBar")
7770 def _write_data_bar_ext(self, param) -> None:
7771 # Write the <extLst> dataBar extension element.
7773 # Create a pseudo GUID for each unique Excel 2010 data bar.
7774 worksheet_count = self.index + 1
7775 data_bar_count = len(self.data_bars_2010) + 1
7776 guid = "{DA7ABA51-AAAA-BBBB-%04X-%012X}" % (worksheet_count, data_bar_count)
7778 # Store the 2010 data bar parameters to write the extLst elements.
7779 param["guid"] = guid
7780 self.data_bars_2010.append(param)
7782 self._xml_start_tag("extLst")
7783 self._write_ext("{B025F937-C7B1-47D3-B67F-A62EFF666E3E}")
7784 self._xml_data_element("x14:id", guid)
7785 self._xml_end_tag("ext")
7786 self._xml_end_tag("extLst")
7788 def _write_icon_set(self, param) -> None:
7789 # Write the <iconSet> element.
7790 attributes = []
7792 # Don't set attribute for default style.
7793 if param["icon_style"] != "3TrafficLights":
7794 attributes = [("iconSet", param["icon_style"])]
7796 if param.get("icons_only"):
7797 attributes.append(("showValue", 0))
7799 if param.get("reverse_icons"):
7800 attributes.append(("reverse", 1))
7802 self._xml_start_tag("iconSet", attributes)
7804 # Write the properties for different icon styles.
7805 for icon in reversed(param["icons"]):
7806 self._write_cfvo(icon["type"], icon["value"], icon["criteria"])
7808 self._xml_end_tag("iconSet")
7810 def _write_cfvo(self, cf_type, val, criteria=None) -> None:
7811 # Write the <cfvo> element.
7812 attributes = [("type", cf_type)]
7814 if val is not None:
7815 attributes.append(("val", val))
7817 if criteria:
7818 attributes.append(("gte", 0))
7820 self._xml_empty_tag("cfvo", attributes)
7822 def _write_color(self, name, attributes) -> None:
7823 # Write the <color> element.
7824 self._xml_empty_tag(name, attributes)
7826 def _write_selections(self) -> None:
7827 # Write the <selection> elements.
7828 for selection in self.selections:
7829 self._write_selection(*selection)
7831 def _write_selection(self, pane, active_cell, sqref) -> None:
7832 # Write the <selection> element.
7833 attributes = []
7835 if pane:
7836 attributes.append(("pane", pane))
7838 if active_cell:
7839 attributes.append(("activeCell", active_cell))
7841 if sqref:
7842 attributes.append(("sqref", sqref))
7844 self._xml_empty_tag("selection", attributes)
7846 def _write_panes(self) -> None:
7847 # Write the frozen or split <pane> elements.
7848 panes = self.panes
7850 if not panes:
7851 return
7853 if panes[4] == 2:
7854 self._write_split_panes(*panes)
7855 else:
7856 self._write_freeze_panes(*panes)
7858 def _write_freeze_panes(
7859 self, row: int, col: int, top_row, left_col, pane_type
7860 ) -> None:
7861 # Write the <pane> element for freeze panes.
7862 attributes = []
7864 y_split = row
7865 x_split = col
7866 top_left_cell = xl_rowcol_to_cell(top_row, left_col)
7867 active_pane = ""
7868 state = ""
7869 active_cell = ""
7870 sqref = ""
7872 # Move user cell selection to the panes.
7873 if self.selections:
7874 (_, active_cell, sqref) = self.selections[0]
7875 self.selections = []
7877 # Set the active pane.
7878 if row and col:
7879 active_pane = "bottomRight"
7881 row_cell = xl_rowcol_to_cell(row, 0)
7882 col_cell = xl_rowcol_to_cell(0, col)
7884 self.selections.append(["topRight", col_cell, col_cell])
7885 self.selections.append(["bottomLeft", row_cell, row_cell])
7886 self.selections.append(["bottomRight", active_cell, sqref])
7888 elif col:
7889 active_pane = "topRight"
7890 self.selections.append(["topRight", active_cell, sqref])
7892 else:
7893 active_pane = "bottomLeft"
7894 self.selections.append(["bottomLeft", active_cell, sqref])
7896 # Set the pane type.
7897 if pane_type == 0:
7898 state = "frozen"
7899 elif pane_type == 1:
7900 state = "frozenSplit"
7901 else:
7902 state = "split"
7904 if x_split:
7905 attributes.append(("xSplit", x_split))
7907 if y_split:
7908 attributes.append(("ySplit", y_split))
7910 attributes.append(("topLeftCell", top_left_cell))
7911 attributes.append(("activePane", active_pane))
7912 attributes.append(("state", state))
7914 self._xml_empty_tag("pane", attributes)
7916 def _write_split_panes(self, row: int, col: int, top_row, left_col, _) -> None:
7917 # Write the <pane> element for split panes.
7918 attributes = []
7919 has_selection = False
7920 active_pane = ""
7921 active_cell = ""
7922 sqref = ""
7924 y_split = row
7925 x_split = col
7927 # Move user cell selection to the panes.
7928 if self.selections:
7929 (_, active_cell, sqref) = self.selections[0]
7930 self.selections = []
7931 has_selection = True
7933 # Convert the row and col to 1/20 twip units with padding.
7934 if y_split:
7935 y_split = int(20 * y_split + 300)
7937 if x_split:
7938 x_split = self._calculate_x_split_width(x_split)
7940 # For non-explicit topLeft definitions, estimate the cell offset based
7941 # on the pixels dimensions. This is only a workaround and doesn't take
7942 # adjusted cell dimensions into account.
7943 if top_row == row and left_col == col:
7944 top_row = int(0.5 + (y_split - 300) / 20 / 15)
7945 left_col = int(0.5 + (x_split - 390) / 20 / 3 * 4 / 64)
7947 top_left_cell = xl_rowcol_to_cell(top_row, left_col)
7949 # If there is no selection set the active cell to the top left cell.
7950 if not has_selection:
7951 active_cell = top_left_cell
7952 sqref = top_left_cell
7954 # Set the Cell selections.
7955 if row and col:
7956 active_pane = "bottomRight"
7958 row_cell = xl_rowcol_to_cell(top_row, 0)
7959 col_cell = xl_rowcol_to_cell(0, left_col)
7961 self.selections.append(["topRight", col_cell, col_cell])
7962 self.selections.append(["bottomLeft", row_cell, row_cell])
7963 self.selections.append(["bottomRight", active_cell, sqref])
7965 elif col:
7966 active_pane = "topRight"
7967 self.selections.append(["topRight", active_cell, sqref])
7969 else:
7970 active_pane = "bottomLeft"
7971 self.selections.append(["bottomLeft", active_cell, sqref])
7973 # Format splits to the same precision as Excel.
7974 if x_split:
7975 attributes.append(("xSplit", f"{x_split:.16g}"))
7977 if y_split:
7978 attributes.append(("ySplit", f"{y_split:.16g}"))
7980 attributes.append(("topLeftCell", top_left_cell))
7982 if has_selection:
7983 attributes.append(("activePane", active_pane))
7985 self._xml_empty_tag("pane", attributes)
7987 def _calculate_x_split_width(self, width):
7988 # Convert column width from user units to pane split width.
7990 max_digit_width = 7 # For Calabri 11.
7991 padding = 5
7993 # Convert to pixels.
7994 if width < 1:
7995 pixels = int(width * (max_digit_width + padding) + 0.5)
7996 else:
7997 pixels = int(width * max_digit_width + 0.5) + padding
7999 # Convert to points.
8000 points = pixels * 3 / 4
8002 # Convert to twips (twentieths of a point).
8003 twips = points * 20
8005 # Add offset/padding.
8006 width = twips + 390
8008 return width
8010 def _write_table_parts(self) -> None:
8011 # Write the <tableParts> element.
8012 tables = self.tables
8013 count = len(tables)
8015 # Return if worksheet doesn't contain any tables.
8016 if not count:
8017 return
8019 attributes = [
8020 (
8021 "count",
8022 count,
8023 )
8024 ]
8026 self._xml_start_tag("tableParts", attributes)
8028 for _ in tables:
8029 # Write the tablePart element.
8030 self.rel_count += 1
8031 self._write_table_part(self.rel_count)
8033 self._xml_end_tag("tableParts")
8035 def _write_table_part(self, r_id) -> None:
8036 # Write the <tablePart> element.
8038 r_id = "rId" + str(r_id)
8040 attributes = [
8041 (
8042 "r:id",
8043 r_id,
8044 )
8045 ]
8047 self._xml_empty_tag("tablePart", attributes)
8049 def _write_ext_list(self) -> None:
8050 # Write the <extLst> element for data bars and sparklines.
8051 has_data_bars = len(self.data_bars_2010)
8052 has_sparklines = len(self.sparklines)
8054 if not has_data_bars and not has_sparklines:
8055 return
8057 # Write the extLst element.
8058 self._xml_start_tag("extLst")
8060 if has_data_bars:
8061 self._write_ext_list_data_bars()
8063 if has_sparklines:
8064 self._write_ext_list_sparklines()
8066 self._xml_end_tag("extLst")
8068 def _write_ext_list_data_bars(self) -> None:
8069 # Write the Excel 2010 data_bar subelements.
8070 self._write_ext("{78C0D931-6437-407d-A8EE-F0AAD7539E65}")
8072 self._xml_start_tag("x14:conditionalFormattings")
8074 # Write the Excel 2010 conditional formatting data bar elements.
8075 for data_bar in self.data_bars_2010:
8076 # Write the x14:conditionalFormatting element.
8077 self._write_conditional_formatting_2010(data_bar)
8079 self._xml_end_tag("x14:conditionalFormattings")
8080 self._xml_end_tag("ext")
8082 def _write_conditional_formatting_2010(self, data_bar) -> None:
8083 # Write the <x14:conditionalFormatting> element.
8084 xmlns_xm = "http://schemas.microsoft.com/office/excel/2006/main"
8086 attributes = [("xmlns:xm", xmlns_xm)]
8088 self._xml_start_tag("x14:conditionalFormatting", attributes)
8090 # Write the x14:cfRule element.
8091 self._write_x14_cf_rule(data_bar)
8093 # Write the x14:dataBar element.
8094 self._write_x14_data_bar(data_bar)
8096 # Write the x14 max and min data bars.
8097 self._write_x14_cfvo(data_bar["x14_min_type"], data_bar["min_value"])
8098 self._write_x14_cfvo(data_bar["x14_max_type"], data_bar["max_value"])
8100 if not data_bar["bar_no_border"]:
8101 # Write the x14:borderColor element.
8102 self._write_x14_border_color(data_bar["bar_border_color"])
8104 # Write the x14:negativeFillColor element.
8105 if not data_bar["bar_negative_color_same"]:
8106 self._write_x14_negative_fill_color(data_bar["bar_negative_color"])
8108 # Write the x14:negativeBorderColor element.
8109 if (
8110 not data_bar["bar_no_border"]
8111 and not data_bar["bar_negative_border_color_same"]
8112 ):
8113 self._write_x14_negative_border_color(data_bar["bar_negative_border_color"])
8115 # Write the x14:axisColor element.
8116 if data_bar["bar_axis_position"] != "none":
8117 self._write_x14_axis_color(data_bar["bar_axis_color"])
8119 self._xml_end_tag("x14:dataBar")
8120 self._xml_end_tag("x14:cfRule")
8122 # Write the xm:sqref element.
8123 self._xml_data_element("xm:sqref", data_bar["range"])
8125 self._xml_end_tag("x14:conditionalFormatting")
8127 def _write_x14_cf_rule(self, data_bar) -> None:
8128 # Write the <x14:cfRule> element.
8129 rule_type = "dataBar"
8130 guid = data_bar["guid"]
8131 attributes = [("type", rule_type), ("id", guid)]
8133 self._xml_start_tag("x14:cfRule", attributes)
8135 def _write_x14_data_bar(self, data_bar) -> None:
8136 # Write the <x14:dataBar> element.
8137 min_length = 0
8138 max_length = 100
8140 attributes = [
8141 ("minLength", min_length),
8142 ("maxLength", max_length),
8143 ]
8145 if not data_bar["bar_no_border"]:
8146 attributes.append(("border", 1))
8148 if data_bar["bar_solid"]:
8149 attributes.append(("gradient", 0))
8151 if data_bar["bar_direction"] == "left":
8152 attributes.append(("direction", "leftToRight"))
8154 if data_bar["bar_direction"] == "right":
8155 attributes.append(("direction", "rightToLeft"))
8157 if data_bar["bar_negative_color_same"]:
8158 attributes.append(("negativeBarColorSameAsPositive", 1))
8160 if (
8161 not data_bar["bar_no_border"]
8162 and not data_bar["bar_negative_border_color_same"]
8163 ):
8164 attributes.append(("negativeBarBorderColorSameAsPositive", 0))
8166 if data_bar["bar_axis_position"] == "middle":
8167 attributes.append(("axisPosition", "middle"))
8169 if data_bar["bar_axis_position"] == "none":
8170 attributes.append(("axisPosition", "none"))
8172 self._xml_start_tag("x14:dataBar", attributes)
8174 def _write_x14_cfvo(self, rule_type, value) -> None:
8175 # Write the <x14:cfvo> element.
8176 attributes = [("type", rule_type)]
8178 if rule_type in ("min", "max", "autoMin", "autoMax"):
8179 self._xml_empty_tag("x14:cfvo", attributes)
8180 else:
8181 self._xml_start_tag("x14:cfvo", attributes)
8182 self._xml_data_element("xm:f", value)
8183 self._xml_end_tag("x14:cfvo")
8185 def _write_x14_border_color(self, color) -> None:
8186 # Write the <x14:borderColor> element.
8187 self._write_color("x14:borderColor", color._attributes())
8189 def _write_x14_negative_fill_color(self, color) -> None:
8190 # Write the <x14:negativeFillColor> element.
8191 self._xml_empty_tag("x14:negativeFillColor", color._attributes())
8193 def _write_x14_negative_border_color(self, color) -> None:
8194 # Write the <x14:negativeBorderColor> element.
8195 self._xml_empty_tag("x14:negativeBorderColor", color._attributes())
8197 def _write_x14_axis_color(self, color) -> None:
8198 # Write the <x14:axisColor> element.
8199 self._xml_empty_tag("x14:axisColor", color._attributes())
8201 def _write_ext_list_sparklines(self) -> None:
8202 # Write the sparkline extension sub-elements.
8203 self._write_ext("{05C60535-1F16-4fd2-B633-F4F36F0B64E0}")
8205 # Write the x14:sparklineGroups element.
8206 self._write_sparkline_groups()
8208 # Write the sparkline elements.
8209 for sparkline in reversed(self.sparklines):
8210 # Write the x14:sparklineGroup element.
8211 self._write_sparkline_group(sparkline)
8213 # Write the x14:colorSeries element.
8214 self._write_color_series(sparkline["series_color"])
8216 # Write the x14:colorNegative element.
8217 self._write_color_negative(sparkline["negative_color"])
8219 # Write the x14:colorAxis element.
8220 self._write_color_axis()
8222 # Write the x14:colorMarkers element.
8223 self._write_color_markers(sparkline["markers_color"])
8225 # Write the x14:colorFirst element.
8226 self._write_color_first(sparkline["first_color"])
8228 # Write the x14:colorLast element.
8229 self._write_color_last(sparkline["last_color"])
8231 # Write the x14:colorHigh element.
8232 self._write_color_high(sparkline["high_color"])
8234 # Write the x14:colorLow element.
8235 self._write_color_low(sparkline["low_color"])
8237 if sparkline["date_axis"]:
8238 self._xml_data_element("xm:f", sparkline["date_axis"])
8240 self._write_sparklines(sparkline)
8242 self._xml_end_tag("x14:sparklineGroup")
8244 self._xml_end_tag("x14:sparklineGroups")
8245 self._xml_end_tag("ext")
8247 def _write_sparklines(self, sparkline) -> None:
8248 # Write the <x14:sparklines> element and <x14:sparkline> sub-elements.
8250 # Write the sparkline elements.
8251 self._xml_start_tag("x14:sparklines")
8253 for i in range(sparkline["count"]):
8254 spark_range = sparkline["ranges"][i]
8255 location = sparkline["locations"][i]
8257 self._xml_start_tag("x14:sparkline")
8258 self._xml_data_element("xm:f", spark_range)
8259 self._xml_data_element("xm:sqref", location)
8260 self._xml_end_tag("x14:sparkline")
8262 self._xml_end_tag("x14:sparklines")
8264 def _write_ext(self, uri) -> None:
8265 # Write the <ext> element.
8266 schema = "http://schemas.microsoft.com/office/"
8267 xmlns_x14 = schema + "spreadsheetml/2009/9/main"
8269 attributes = [
8270 ("xmlns:x14", xmlns_x14),
8271 ("uri", uri),
8272 ]
8274 self._xml_start_tag("ext", attributes)
8276 def _write_sparkline_groups(self) -> None:
8277 # Write the <x14:sparklineGroups> element.
8278 xmlns_xm = "http://schemas.microsoft.com/office/excel/2006/main"
8280 attributes = [("xmlns:xm", xmlns_xm)]
8282 self._xml_start_tag("x14:sparklineGroups", attributes)
8284 def _write_sparkline_group(self, options) -> None:
8285 # Write the <x14:sparklineGroup> element.
8286 #
8287 # Example for order.
8288 #
8289 # <x14:sparklineGroup
8290 # manualMax="0"
8291 # manualMin="0"
8292 # lineWeight="2.25"
8293 # type="column"
8294 # dateAxis="1"
8295 # displayEmptyCellsAs="span"
8296 # markers="1"
8297 # high="1"
8298 # low="1"
8299 # first="1"
8300 # last="1"
8301 # negative="1"
8302 # displayXAxis="1"
8303 # displayHidden="1"
8304 # minAxisType="custom"
8305 # maxAxisType="custom"
8306 # rightToLeft="1">
8307 #
8308 empty = options.get("empty")
8309 attributes = []
8311 if options.get("max") is not None:
8312 if options["max"] == "group":
8313 options["cust_max"] = "group"
8314 else:
8315 attributes.append(("manualMax", options["max"]))
8316 options["cust_max"] = "custom"
8318 if options.get("min") is not None:
8319 if options["min"] == "group":
8320 options["cust_min"] = "group"
8321 else:
8322 attributes.append(("manualMin", options["min"]))
8323 options["cust_min"] = "custom"
8325 # Ignore the default type attribute (line).
8326 if options["type"] != "line":
8327 attributes.append(("type", options["type"]))
8329 if options.get("weight"):
8330 attributes.append(("lineWeight", options["weight"]))
8332 if options.get("date_axis"):
8333 attributes.append(("dateAxis", 1))
8335 if empty:
8336 attributes.append(("displayEmptyCellsAs", empty))
8338 if options.get("markers"):
8339 attributes.append(("markers", 1))
8341 if options.get("high"):
8342 attributes.append(("high", 1))
8344 if options.get("low"):
8345 attributes.append(("low", 1))
8347 if options.get("first"):
8348 attributes.append(("first", 1))
8350 if options.get("last"):
8351 attributes.append(("last", 1))
8353 if options.get("negative"):
8354 attributes.append(("negative", 1))
8356 if options.get("axis"):
8357 attributes.append(("displayXAxis", 1))
8359 if options.get("hidden"):
8360 attributes.append(("displayHidden", 1))
8362 if options.get("cust_min"):
8363 attributes.append(("minAxisType", options["cust_min"]))
8365 if options.get("cust_max"):
8366 attributes.append(("maxAxisType", options["cust_max"]))
8368 if options.get("reverse"):
8369 attributes.append(("rightToLeft", 1))
8371 self._xml_start_tag("x14:sparklineGroup", attributes)
8373 def _write_spark_color(self, tag, color) -> None:
8374 # Helper function for the sparkline color functions below.
8375 if color:
8376 self._write_color(tag, color._attributes())
8378 def _write_color_series(self, color) -> None:
8379 # Write the <x14:colorSeries> element.
8380 self._write_spark_color("x14:colorSeries", color)
8382 def _write_color_negative(self, color) -> None:
8383 # Write the <x14:colorNegative> element.
8384 self._write_spark_color("x14:colorNegative", color)
8386 def _write_color_axis(self) -> None:
8387 # Write the <x14:colorAxis> element.
8388 self._write_spark_color("x14:colorAxis", Color("#000000"))
8390 def _write_color_markers(self, color) -> None:
8391 # Write the <x14:colorMarkers> element.
8392 self._write_spark_color("x14:colorMarkers", color)
8394 def _write_color_first(self, color) -> None:
8395 # Write the <x14:colorFirst> element.
8396 self._write_spark_color("x14:colorFirst", color)
8398 def _write_color_last(self, color) -> None:
8399 # Write the <x14:colorLast> element.
8400 self._write_spark_color("x14:colorLast", color)
8402 def _write_color_high(self, color) -> None:
8403 # Write the <x14:colorHigh> element.
8404 self._write_spark_color("x14:colorHigh", color)
8406 def _write_color_low(self, color) -> None:
8407 # Write the <x14:colorLow> element.
8408 self._write_spark_color("x14:colorLow", color)
8410 def _write_phonetic_pr(self) -> None:
8411 # Write the <phoneticPr> element.
8412 attributes = [
8413 ("fontId", "0"),
8414 ("type", "noConversion"),
8415 ]
8417 self._xml_empty_tag("phoneticPr", attributes)
8419 def _write_ignored_errors(self) -> None:
8420 # Write the <ignoredErrors> element.
8421 if not self.ignored_errors:
8422 return
8424 self._xml_start_tag("ignoredErrors")
8426 if self.ignored_errors.get("number_stored_as_text"):
8427 ignored_range = self.ignored_errors["number_stored_as_text"]
8428 self._write_ignored_error("numberStoredAsText", ignored_range)
8430 if self.ignored_errors.get("eval_error"):
8431 ignored_range = self.ignored_errors["eval_error"]
8432 self._write_ignored_error("evalError", ignored_range)
8434 if self.ignored_errors.get("formula_differs"):
8435 ignored_range = self.ignored_errors["formula_differs"]
8436 self._write_ignored_error("formula", ignored_range)
8438 if self.ignored_errors.get("formula_range"):
8439 ignored_range = self.ignored_errors["formula_range"]
8440 self._write_ignored_error("formulaRange", ignored_range)
8442 if self.ignored_errors.get("formula_unlocked"):
8443 ignored_range = self.ignored_errors["formula_unlocked"]
8444 self._write_ignored_error("unlockedFormula", ignored_range)
8446 if self.ignored_errors.get("empty_cell_reference"):
8447 ignored_range = self.ignored_errors["empty_cell_reference"]
8448 self._write_ignored_error("emptyCellReference", ignored_range)
8450 if self.ignored_errors.get("list_data_validation"):
8451 ignored_range = self.ignored_errors["list_data_validation"]
8452 self._write_ignored_error("listDataValidation", ignored_range)
8454 if self.ignored_errors.get("calculated_column"):
8455 ignored_range = self.ignored_errors["calculated_column"]
8456 self._write_ignored_error("calculatedColumn", ignored_range)
8458 if self.ignored_errors.get("two_digit_text_year"):
8459 ignored_range = self.ignored_errors["two_digit_text_year"]
8460 self._write_ignored_error("twoDigitTextYear", ignored_range)
8462 self._xml_end_tag("ignoredErrors")
8464 def _write_ignored_error(self, error_type, ignored_range) -> None:
8465 # Write the <ignoredError> element.
8466 attributes = [
8467 ("sqref", ignored_range),
8468 (error_type, 1),
8469 ]
8471 self._xml_empty_tag("ignoredError", attributes)