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 dataclasses import dataclass
20from decimal import Decimal
21from fractions import Fraction
22from functools import wraps
23from io import BytesIO, StringIO
24from math import isinf, isnan
25from typing import (
26 TYPE_CHECKING,
27 Any,
28 Callable,
29 Dict,
30 List,
31 Literal,
32 Optional,
33 TypeVar,
34 Union,
35)
36from warnings import warn
38# Package imports.
39from xlsxwriter import xmlwriter
40from xlsxwriter.chart import Chart
41from xlsxwriter.color import Color
42from xlsxwriter.comments import CommentType
43from xlsxwriter.drawing import Drawing, DrawingInfo, DrawingTypes
44from xlsxwriter.exceptions import DuplicateTableName, OverlappingRange
45from xlsxwriter.format import Format
46from xlsxwriter.image import Image
47from xlsxwriter.shape import Shape
48from xlsxwriter.url import Url, UrlTypes
49from xlsxwriter.utility import (
50 _datetime_to_excel_datetime,
51 _get_sparkline_style,
52 _preserve_whitespace,
53 _supported_datetime,
54 quote_sheetname,
55 xl_cell_to_rowcol,
56 xl_col_to_name,
57 xl_pixel_width,
58 xl_range,
59 xl_rowcol_to_cell,
60 xl_rowcol_to_cell_fast,
61)
62from xlsxwriter.vml import ButtonType
63from xlsxwriter.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)
264###############################################################################
265#
266# Helper classes and types.
267#
268###############################################################################
271@dataclass
272class ColumnInfo:
273 """Type to hold user modified properties for a column."""
275 width: Optional[int] = None
276 column_format: Optional["Format"] = None
277 hidden: bool = False
278 level: int = 0
279 collapsed: bool = False
280 autofit: bool = False
283@dataclass
284class RowInfo:
285 """Type to hold user modified properties for a row."""
287 height: Optional[int] = None
288 row_format: Optional["Format"] = None
289 hidden: bool = False
290 level: int = 0
291 collapsed: bool = False
294###############################################################################
295#
296# Worksheet Class definition.
297#
298###############################################################################
299class Worksheet(xmlwriter.XMLwriter):
300 """
301 A class for writing the Excel XLSX Worksheet file.
303 """
305 ###########################################################################
306 #
307 # Public API.
308 #
309 ###########################################################################
311 def __init__(self) -> None:
312 """
313 Constructor.
315 """
317 super().__init__()
319 self.name = None
320 self.index = None
321 self.str_table = None
322 self.palette = None
323 self.constant_memory = 0
324 self.tmpdir = None
325 self.is_chartsheet = False
327 self.fileclosed = 0
328 self.excel_version = 2007
329 self.excel2003_style = False
331 self.xls_rowmax = 1048576
332 self.xls_colmax = 16384
333 self.xls_strmax = 32767
334 self.dim_rowmin = None
335 self.dim_rowmax = None
336 self.dim_colmin = None
337 self.dim_colmax = None
339 self.col_info: Dict[int, ColumnInfo] = {}
340 self.row_info: Dict[int, RowInfo] = {}
341 self.default_row_height: int = 20
342 self.default_col_width: int = 64
343 self.cell_padding: int = 5
344 self.original_row_height: int = 20
345 self.max_digit_width: int = 7
346 self.max_col_width: int = 1790
347 self.default_date_width = 68
348 self.default_row_zeroed = 0
350 self.selections = []
351 self.hidden = 0
352 self.active = 0
353 self.tab_color = 0
354 self.top_left_cell = ""
356 self.panes = []
357 self.selected = 0
359 self.page_setup_changed = False
360 self.paper_size = 0
361 self.orientation = 1
363 self.print_options_changed = False
364 self.hcenter = False
365 self.vcenter = False
366 self.print_gridlines = False
367 self.screen_gridlines = True
368 self.print_headers = False
369 self.row_col_headers = False
371 self.header_footer_changed = False
372 self.header = ""
373 self.footer = ""
374 self.header_footer_aligns = True
375 self.header_footer_scales = True
376 self.header_images = []
377 self.footer_images = []
378 self.header_images_list = []
380 self.margin_left = 0.7
381 self.margin_right = 0.7
382 self.margin_top = 0.75
383 self.margin_bottom = 0.75
384 self.margin_header = 0.3
385 self.margin_footer = 0.3
387 self.repeat_row_range = ""
388 self.repeat_col_range = ""
389 self.print_area_range = ""
391 self.page_order = 0
392 self.black_white = 0
393 self.page_start = 0
395 self.fit_page = 0
396 self.fit_width = 0
397 self.fit_height = 0
399 self.hbreaks = []
400 self.vbreaks = []
402 self.protect_options = {}
403 self.protected_ranges = []
404 self.num_protected_ranges = 0
406 self.zoom = 100
407 self.zoom_scale_normal = True
408 self.zoom_to_fit = False
409 self.print_scale = 100
410 self.is_right_to_left = False
411 self.show_zeros = 1
413 self.outline_row_level = 0
414 self.outline_col_level = 0
415 self.outline_style = 0
416 self.outline_below = 1
417 self.outline_right = 1
418 self.outline_on = 1
419 self.outline_changed = False
421 self.table = defaultdict(dict)
422 self.merge = []
423 self.merged_cells = {}
424 self.table_cells = {}
425 self.row_spans = {}
427 self.has_vml = False
428 self.has_header_vml = False
429 self.has_comments = False
430 self.comments = defaultdict(dict)
431 self.comments_list = []
432 self.comments_author = ""
433 self.comments_visible = False
434 self.vml_shape_id = 1024
435 self.buttons_list = []
436 self.vml_header_id = 0
438 self.autofilter_area = ""
439 self.autofilter_ref = None
440 self.filter_range = [0, 9]
441 self.filter_on = 0
442 self.filter_cols = {}
443 self.filter_type = {}
444 self.filter_cells = {}
446 self.row_sizes = {}
447 self.col_size_changed = False
448 self.row_size_changed = False
450 self.rel_count = 0
451 self.hlink_count = 0
452 self.external_hyper_links = []
453 self.external_drawing_links = []
454 self.external_comment_links = []
455 self.external_vml_links = []
456 self.external_table_links = []
457 self.external_background_links = []
458 self.drawing_links = []
459 self.vml_drawing_links = []
460 self.charts = []
461 self.images = []
462 self.tables = []
463 self.sparklines = []
464 self.shapes = []
465 self.shape_hash = {}
466 self.drawing = 0
467 self.drawing_rels = {}
468 self.drawing_rels_id = 0
469 self.vml_drawing_rels = {}
470 self.vml_drawing_rels_id = 0
471 self.background_image = None
473 self.rstring = ""
474 self.previous_row = 0
476 self.validations = []
477 self.cond_formats = {}
478 self.data_bars_2010 = []
479 self.use_data_bars_2010 = False
480 self.dxf_priority = 1
481 self.page_view = 0
483 self.vba_codename = None
485 self.date_1904 = False
486 self.hyperlinks = defaultdict(dict)
488 self.strings_to_numbers = False
489 self.strings_to_urls = True
490 self.nan_inf_to_errors = False
491 self.strings_to_formulas = True
493 self.default_date_format = None
494 self.default_url_format = None
495 self.default_checkbox_format = None
496 self.workbook_add_format = None
497 self.remove_timezone = False
498 self.max_url_length = 2079
500 self.row_data_filename = None
501 self.row_data_fh = None
502 self.worksheet_meta = None
503 self.vml_data_id = None
504 self.vml_shape_id = None
506 self.row_data_filename = None
507 self.row_data_fh = None
508 self.row_data_fh_closed = False
510 self.vertical_dpi = 0
511 self.horizontal_dpi = 0
513 self.write_handlers = {}
515 self.ignored_errors = None
517 self.has_dynamic_arrays = False
518 self.use_future_functions = False
519 self.ignore_write_string = False
520 self.embedded_images = None
522 # Utility function for writing different types of strings.
523 def _write_token_as_string(self, token, row: int, col: int, *args):
524 # Map the data to the appropriate write_*() method.
525 if token == "":
526 return self._write_blank(row, col, *args)
528 if self.strings_to_formulas and token.startswith("="):
529 return self._write_formula(row, col, *args)
531 if token.startswith("{=") and token.endswith("}"):
532 return self._write_formula(row, col, *args)
534 # pylint: disable=too-many-boolean-expressions
535 if (
536 ":" in token
537 and self.strings_to_urls
538 and (
539 re.match("(ftp|http)s?://", token)
540 or re.match("mailto:", token)
541 or re.match("(in|ex)ternal:", token)
542 or re.match("file://", token)
543 )
544 ):
545 return self._write_url(row, col, *args)
547 if self.strings_to_numbers:
548 try:
549 f = float(token)
550 if self.nan_inf_to_errors or (not isnan(f) and not isinf(f)):
551 return self._write_number(row, col, f, *args[1:])
552 except ValueError:
553 # Not a number, write as a string.
554 pass
556 return self._write_string(row, col, *args)
558 # We have a plain string.
559 return self._write_string(row, col, *args)
561 @convert_cell_args
562 def write(self, row: int, col: int, *args) -> Union[Literal[0, -1], Any]:
563 """
564 Write data to a worksheet cell by calling the appropriate write_*()
565 method based on the type of data being passed.
567 Args:
568 row: The cell row (zero indexed).
569 col: The cell column (zero indexed).
570 *args: Args to pass to sub functions.
572 Returns:
573 0: Success.
574 -1: Row or column is out of worksheet bounds.
575 other: Return value of called method.
577 """
578 return self._write(row, col, *args)
580 # Undecorated version of write().
581 def _write(self, row: int, col: int, *args):
582 # pylint: disable=raise-missing-from
583 # Check the number of args passed.
584 if not args:
585 raise TypeError("write() takes at least 4 arguments (3 given)")
587 # The first arg should be the token for all write calls.
588 token = args[0]
590 # Avoid isinstance() for better performance.
591 token_type = token.__class__
593 # Check for any user defined type handlers with callback functions.
594 if token_type in self.write_handlers:
595 write_handler = self.write_handlers[token_type]
596 function_return = write_handler(self, row, col, *args)
598 # If the return value is None then the callback has returned
599 # control to this function and we should continue as
600 # normal. Otherwise we return the value to the caller and exit.
601 if function_return is None:
602 pass
603 else:
604 return function_return
606 # Write None as a blank cell.
607 if token is None:
608 return self._write_blank(row, col, *args)
610 # Check for standard Python types.
611 if token_type is bool:
612 return self._write_boolean(row, col, *args)
614 if token_type in (float, int, Decimal, Fraction):
615 return self._write_number(row, col, *args)
617 if token_type is str:
618 return self._write_token_as_string(token, row, col, *args)
620 if token_type in (
621 datetime.datetime,
622 datetime.date,
623 datetime.time,
624 datetime.timedelta,
625 ):
626 return self._write_datetime(row, col, *args)
628 # Resort to isinstance() for subclassed primitives.
630 # Write number types.
631 if isinstance(token, (float, int, Decimal, Fraction)):
632 return self._write_number(row, col, *args)
634 # Write string types.
635 if isinstance(token, str):
636 return self._write_token_as_string(token, row, col, *args)
638 # Write boolean types.
639 if isinstance(token, bool):
640 return self._write_boolean(row, col, *args)
642 # Write datetime objects.
643 if _supported_datetime(token):
644 return self._write_datetime(row, col, *args)
646 # Write Url type.
647 if isinstance(token, Url):
648 return self._write_url(row, col, *args)
650 # We haven't matched a supported type. Try float.
651 try:
652 f = float(token)
653 return self._write_number(row, col, f, *args[1:])
654 except ValueError:
655 pass
656 except TypeError:
657 raise TypeError(f"Unsupported type {type(token)} in write()")
659 # Finally try string.
660 try:
661 str(token)
662 return self._write_string(row, col, *args)
663 except ValueError:
664 raise TypeError(f"Unsupported type {type(token)} in write()")
666 @convert_cell_args
667 def write_string(
668 self, row: int, col: int, string: str, cell_format: Optional[Format] = None
669 ) -> Literal[0, -1, -2]:
670 """
671 Write a string to a worksheet cell.
673 Args:
674 row: The cell row (zero indexed).
675 col: The cell column (zero indexed).
676 string: Cell data. Str.
677 format: An optional cell Format object.
679 Returns:
680 0: Success.
681 -1: Row or column is out of worksheet bounds.
682 -2: String truncated to 32k characters.
684 """
685 return self._write_string(row, col, string, cell_format)
687 # Undecorated version of write_string().
688 def _write_string(
689 self, row: int, col: int, string: str, cell_format: Optional[Format] = None
690 ) -> Literal[0, -1, -2]:
691 str_error = 0
693 # Check that row and col are valid and store max and min values.
694 if self._check_dimensions(row, col):
695 return -1
697 # Check that the string is < 32767 chars.
698 if len(string) > self.xls_strmax:
699 string = string[: self.xls_strmax]
700 str_error = -2
702 # Write a shared string or an in-line string in constant_memory mode.
703 if not self.constant_memory:
704 string_index = self.str_table._get_shared_string_index(string)
705 else:
706 string_index = string
708 # Write previous row if in in-line string constant_memory mode.
709 if self.constant_memory and row > self.previous_row:
710 self._write_single_row(row)
712 # Store the cell data in the worksheet data table.
713 self.table[row][col] = CellStringTuple(string_index, cell_format)
715 return str_error
717 @convert_cell_args
718 def write_number(
719 self,
720 row: int,
721 col: int,
722 number: Union[int, float, Fraction],
723 cell_format: Optional[Format] = None,
724 ) -> Literal[0, -1]:
725 """
726 Write a number to a worksheet cell.
728 Args:
729 row: The cell row (zero indexed).
730 col: The cell column (zero indexed).
731 number: Cell data. Int or float.
732 cell_format: An optional cell Format object.
734 Returns:
735 0: Success.
736 -1: Row or column is out of worksheet bounds.
738 """
739 return self._write_number(row, col, number, cell_format)
741 # Undecorated version of write_number().
742 def _write_number(
743 self,
744 row: int,
745 col: int,
746 number: Union[int, float, Fraction],
747 cell_format: Optional[Format] = None,
748 ) -> Literal[0, -1]:
749 if isnan(number) or isinf(number):
750 if self.nan_inf_to_errors:
751 if isnan(number):
752 return self._write_formula(row, col, "#NUM!", cell_format, "#NUM!")
754 if number == math.inf:
755 return self._write_formula(row, col, "1/0", cell_format, "#DIV/0!")
757 if number == -math.inf:
758 return self._write_formula(row, col, "-1/0", cell_format, "#DIV/0!")
759 else:
760 raise TypeError(
761 "NAN/INF not supported in write_number() "
762 "without 'nan_inf_to_errors' Workbook() option"
763 )
765 if number.__class__ is Fraction:
766 number = float(number)
768 # Check that row and col are valid and store max and min values.
769 if self._check_dimensions(row, col):
770 return -1
772 # Write previous row if in in-line string constant_memory mode.
773 if self.constant_memory and row > self.previous_row:
774 self._write_single_row(row)
776 # Store the cell data in the worksheet data table.
777 self.table[row][col] = CellNumberTuple(number, cell_format)
779 return 0
781 @convert_cell_args
782 def write_blank(
783 self, row: int, col: int, blank: Any, cell_format: Optional[Format] = None
784 ):
785 """
786 Write a blank cell with formatting to a worksheet cell. The blank
787 token is ignored and the format only is written to the cell.
789 Args:
790 row: The cell row (zero indexed).
791 col: The cell column (zero indexed).
792 blank: Any value. It is ignored.
793 cell_format: An optional cell Format object.
795 Returns:
796 0: Success.
797 -1: Row or column is out of worksheet bounds.
799 """
800 return self._write_blank(row, col, blank, cell_format)
802 # Undecorated version of write_blank().
803 def _write_blank(
804 self, row: int, col: int, _, cell_format: Optional[Format] = None
805 ) -> Literal[0, -1]:
806 # Don't write a blank cell unless it has a format.
807 if cell_format is None:
808 return 0
810 # Check that row and col are valid and store max and min values.
811 if self._check_dimensions(row, col):
812 return -1
814 # Write previous row if in in-line string constant_memory mode.
815 if self.constant_memory and row > self.previous_row:
816 self._write_single_row(row)
818 # Store the cell data in the worksheet data table.
819 self.table[row][col] = CellBlankTuple(cell_format)
821 return 0
823 @convert_cell_args
824 def write_formula(
825 self,
826 row: int,
827 col: int,
828 formula: str,
829 cell_format: Optional[Format] = None,
830 value=0,
831 ) -> Literal[0, -1, -2]:
832 """
833 Write a formula to a worksheet cell.
835 Args:
836 row: The cell row (zero indexed).
837 col: The cell column (zero indexed).
838 formula: Cell formula.
839 cell_format: An optional cell Format object.
840 value: An optional value for the formula. Default is 0.
842 Returns:
843 0: Success.
844 -1: Row or column is out of worksheet bounds.
845 -2: Formula can't be None or empty.
847 """
848 # Check that row and col are valid and store max and min values.
849 return self._write_formula(row, col, formula, cell_format, value)
851 # Undecorated version of write_formula().
852 def _write_formula(
853 self,
854 row: int,
855 col: int,
856 formula: str,
857 cell_format: Optional[Format] = None,
858 value=0,
859 ) -> Literal[0, -1, -2]:
860 if self._check_dimensions(row, col):
861 return -1
863 if formula is None or formula == "":
864 warn("Formula can't be None or empty")
865 return -1
867 # Check for dynamic array functions.
868 if re_dynamic_function.search(formula):
869 return self.write_dynamic_array_formula(
870 row, col, row, col, formula, cell_format, value
871 )
873 # Hand off array formulas.
874 if formula.startswith("{") and formula.endswith("}"):
875 return self._write_array_formula(
876 row, col, row, col, formula, cell_format, value
877 )
879 # Modify the formula string, as needed.
880 formula = self._prepare_formula(formula)
882 # Write previous row if in in-line string constant_memory mode.
883 if self.constant_memory and row > self.previous_row:
884 self._write_single_row(row)
886 # Store the cell data in the worksheet data table.
887 self.table[row][col] = CellFormulaTuple(formula, cell_format, value)
889 return 0
891 @convert_range_args
892 def write_array_formula(
893 self,
894 first_row: int,
895 first_col: int,
896 last_row: int,
897 last_col: int,
898 formula: str,
899 cell_format: Optional[Format] = None,
900 value=0,
901 ) -> Literal[0, -1]:
902 """
903 Write a formula to a worksheet cell/range.
905 Args:
906 first_row: The first row of the cell range. (zero indexed).
907 first_col: The first column of the cell range.
908 last_row: The last row of the cell range. (zero indexed).
909 last_col: The last column of the cell range.
910 formula: Cell formula.
911 cell_format: An optional cell Format object.
912 value: An optional value for the formula. Default is 0.
914 Returns:
915 0: Success.
916 -1: Row or column is out of worksheet bounds.
918 """
919 # Check for dynamic array functions.
920 if re_dynamic_function.search(formula):
921 return self.write_dynamic_array_formula(
922 first_row, first_col, last_row, last_col, formula, cell_format, value
923 )
925 return self._write_array_formula(
926 first_row,
927 first_col,
928 last_row,
929 last_col,
930 formula,
931 cell_format,
932 value,
933 "static",
934 )
936 @convert_range_args
937 def write_dynamic_array_formula(
938 self,
939 first_row: int,
940 first_col: int,
941 last_row: int,
942 last_col: int,
943 formula: str,
944 cell_format: Optional[Format] = None,
945 value=0,
946 ) -> Literal[0, -1]:
947 """
948 Write a dynamic array formula to a worksheet cell/range.
950 Args:
951 first_row: The first row of the cell range. (zero indexed).
952 first_col: The first column of the cell range.
953 last_row: The last row of the cell range. (zero indexed).
954 last_col: The last column of the cell range.
955 formula: Cell formula.
956 cell_format: An optional cell Format object.
957 value: An optional value for the formula. Default is 0.
959 Returns:
960 0: Success.
961 -1: Row or column is out of worksheet bounds.
963 """
964 error = self._write_array_formula(
965 first_row,
966 first_col,
967 last_row,
968 last_col,
969 formula,
970 cell_format,
971 value,
972 "dynamic",
973 )
975 if error == 0:
976 self.has_dynamic_arrays = True
978 return error
980 # Utility method to strip equal sign and array braces from a formula and
981 # also expand out future and dynamic array formulas.
982 def _prepare_formula(self, formula, expand_future_functions=False):
983 # Remove array formula braces and the leading =.
984 if formula.startswith("{"):
985 formula = formula[1:]
986 if formula.startswith("="):
987 formula = formula[1:]
988 if formula.endswith("}"):
989 formula = formula[:-1]
991 # Check if formula is already expanded by the user.
992 if "_xlfn." in formula:
993 return formula
995 # Expand dynamic formulas.
996 formula = re.sub(r"\bANCHORARRAY\(", "_xlfn.ANCHORARRAY(", formula)
997 formula = re.sub(r"\bBYCOL\(", "_xlfn.BYCOL(", formula)
998 formula = re.sub(r"\bBYROW\(", "_xlfn.BYROW(", formula)
999 formula = re.sub(r"\bCHOOSECOLS\(", "_xlfn.CHOOSECOLS(", formula)
1000 formula = re.sub(r"\bCHOOSEROWS\(", "_xlfn.CHOOSEROWS(", formula)
1001 formula = re.sub(r"\bDROP\(", "_xlfn.DROP(", formula)
1002 formula = re.sub(r"\bEXPAND\(", "_xlfn.EXPAND(", formula)
1003 formula = re.sub(r"\bFILTER\(", "_xlfn._xlws.FILTER(", formula)
1004 formula = re.sub(r"\bHSTACK\(", "_xlfn.HSTACK(", formula)
1005 formula = re.sub(r"\bLAMBDA\(", "_xlfn.LAMBDA(", formula)
1006 formula = re.sub(r"\bMAKEARRAY\(", "_xlfn.MAKEARRAY(", formula)
1007 formula = re.sub(r"\bMAP\(", "_xlfn.MAP(", formula)
1008 formula = re.sub(r"\bRANDARRAY\(", "_xlfn.RANDARRAY(", formula)
1009 formula = re.sub(r"\bREDUCE\(", "_xlfn.REDUCE(", formula)
1010 formula = re.sub(r"\bSCAN\(", "_xlfn.SCAN(", formula)
1011 formula = re.sub(r"\SINGLE\(", "_xlfn.SINGLE(", formula)
1012 formula = re.sub(r"\bSEQUENCE\(", "_xlfn.SEQUENCE(", formula)
1013 formula = re.sub(r"\bSORT\(", "_xlfn._xlws.SORT(", formula)
1014 formula = re.sub(r"\bSORTBY\(", "_xlfn.SORTBY(", formula)
1015 formula = re.sub(r"\bSWITCH\(", "_xlfn.SWITCH(", formula)
1016 formula = re.sub(r"\bTAKE\(", "_xlfn.TAKE(", formula)
1017 formula = re.sub(r"\bTEXTSPLIT\(", "_xlfn.TEXTSPLIT(", formula)
1018 formula = re.sub(r"\bTOCOL\(", "_xlfn.TOCOL(", formula)
1019 formula = re.sub(r"\bTOROW\(", "_xlfn.TOROW(", formula)
1020 formula = re.sub(r"\bUNIQUE\(", "_xlfn.UNIQUE(", formula)
1021 formula = re.sub(r"\bVSTACK\(", "_xlfn.VSTACK(", formula)
1022 formula = re.sub(r"\bWRAPCOLS\(", "_xlfn.WRAPCOLS(", formula)
1023 formula = re.sub(r"\bWRAPROWS\(", "_xlfn.WRAPROWS(", formula)
1024 formula = re.sub(r"\bXLOOKUP\(", "_xlfn.XLOOKUP(", formula)
1026 if not self.use_future_functions and not expand_future_functions:
1027 return formula
1029 formula = re.sub(r"\bACOTH\(", "_xlfn.ACOTH(", formula)
1030 formula = re.sub(r"\bACOT\(", "_xlfn.ACOT(", formula)
1031 formula = re.sub(r"\bAGGREGATE\(", "_xlfn.AGGREGATE(", formula)
1032 formula = re.sub(r"\bARABIC\(", "_xlfn.ARABIC(", formula)
1033 formula = re.sub(r"\bARRAYTOTEXT\(", "_xlfn.ARRAYTOTEXT(", formula)
1034 formula = re.sub(r"\bBASE\(", "_xlfn.BASE(", formula)
1035 formula = re.sub(r"\bBETA.DIST\(", "_xlfn.BETA.DIST(", formula)
1036 formula = re.sub(r"\bBETA.INV\(", "_xlfn.BETA.INV(", formula)
1037 formula = re.sub(r"\bBINOM.DIST.RANGE\(", "_xlfn.BINOM.DIST.RANGE(", formula)
1038 formula = re.sub(r"\bBINOM.DIST\(", "_xlfn.BINOM.DIST(", formula)
1039 formula = re.sub(r"\bBINOM.INV\(", "_xlfn.BINOM.INV(", formula)
1040 formula = re.sub(r"\bBITAND\(", "_xlfn.BITAND(", formula)
1041 formula = re.sub(r"\bBITLSHIFT\(", "_xlfn.BITLSHIFT(", formula)
1042 formula = re.sub(r"\bBITOR\(", "_xlfn.BITOR(", formula)
1043 formula = re.sub(r"\bBITRSHIFT\(", "_xlfn.BITRSHIFT(", formula)
1044 formula = re.sub(r"\bBITXOR\(", "_xlfn.BITXOR(", formula)
1045 formula = re.sub(r"\bCEILING.MATH\(", "_xlfn.CEILING.MATH(", formula)
1046 formula = re.sub(r"\bCEILING.PRECISE\(", "_xlfn.CEILING.PRECISE(", formula)
1047 formula = re.sub(r"\bCHISQ.DIST.RT\(", "_xlfn.CHISQ.DIST.RT(", formula)
1048 formula = re.sub(r"\bCHISQ.DIST\(", "_xlfn.CHISQ.DIST(", formula)
1049 formula = re.sub(r"\bCHISQ.INV.RT\(", "_xlfn.CHISQ.INV.RT(", formula)
1050 formula = re.sub(r"\bCHISQ.INV\(", "_xlfn.CHISQ.INV(", formula)
1051 formula = re.sub(r"\bCHISQ.TEST\(", "_xlfn.CHISQ.TEST(", formula)
1052 formula = re.sub(r"\bCOMBINA\(", "_xlfn.COMBINA(", formula)
1053 formula = re.sub(r"\bCONCAT\(", "_xlfn.CONCAT(", formula)
1054 formula = re.sub(r"\bCONFIDENCE.NORM\(", "_xlfn.CONFIDENCE.NORM(", formula)
1055 formula = re.sub(r"\bCONFIDENCE.T\(", "_xlfn.CONFIDENCE.T(", formula)
1056 formula = re.sub(r"\bCOTH\(", "_xlfn.COTH(", formula)
1057 formula = re.sub(r"\bCOT\(", "_xlfn.COT(", formula)
1058 formula = re.sub(r"\bCOVARIANCE.P\(", "_xlfn.COVARIANCE.P(", formula)
1059 formula = re.sub(r"\bCOVARIANCE.S\(", "_xlfn.COVARIANCE.S(", formula)
1060 formula = re.sub(r"\bCSCH\(", "_xlfn.CSCH(", formula)
1061 formula = re.sub(r"\bCSC\(", "_xlfn.CSC(", formula)
1062 formula = re.sub(r"\bDAYS\(", "_xlfn.DAYS(", formula)
1063 formula = re.sub(r"\bDECIMAL\(", "_xlfn.DECIMAL(", formula)
1064 formula = re.sub(r"\bERF.PRECISE\(", "_xlfn.ERF.PRECISE(", formula)
1065 formula = re.sub(r"\bERFC.PRECISE\(", "_xlfn.ERFC.PRECISE(", formula)
1066 formula = re.sub(r"\bEXPON.DIST\(", "_xlfn.EXPON.DIST(", formula)
1067 formula = re.sub(r"\bF.DIST.RT\(", "_xlfn.F.DIST.RT(", formula)
1068 formula = re.sub(r"\bF.DIST\(", "_xlfn.F.DIST(", formula)
1069 formula = re.sub(r"\bF.INV.RT\(", "_xlfn.F.INV.RT(", formula)
1070 formula = re.sub(r"\bF.INV\(", "_xlfn.F.INV(", formula)
1071 formula = re.sub(r"\bF.TEST\(", "_xlfn.F.TEST(", formula)
1072 formula = re.sub(r"\bFILTERXML\(", "_xlfn.FILTERXML(", formula)
1073 formula = re.sub(r"\bFLOOR.MATH\(", "_xlfn.FLOOR.MATH(", formula)
1074 formula = re.sub(r"\bFLOOR.PRECISE\(", "_xlfn.FLOOR.PRECISE(", formula)
1075 formula = re.sub(
1076 r"\bFORECAST.ETS.CONFINT\(", "_xlfn.FORECAST.ETS.CONFINT(", formula
1077 )
1078 formula = re.sub(
1079 r"\bFORECAST.ETS.SEASONALITY\(", "_xlfn.FORECAST.ETS.SEASONALITY(", formula
1080 )
1081 formula = re.sub(r"\bFORECAST.ETS.STAT\(", "_xlfn.FORECAST.ETS.STAT(", formula)
1082 formula = re.sub(r"\bFORECAST.ETS\(", "_xlfn.FORECAST.ETS(", formula)
1083 formula = re.sub(r"\bFORECAST.LINEAR\(", "_xlfn.FORECAST.LINEAR(", formula)
1084 formula = re.sub(r"\bFORMULATEXT\(", "_xlfn.FORMULATEXT(", formula)
1085 formula = re.sub(r"\bGAMMA.DIST\(", "_xlfn.GAMMA.DIST(", formula)
1086 formula = re.sub(r"\bGAMMA.INV\(", "_xlfn.GAMMA.INV(", formula)
1087 formula = re.sub(r"\bGAMMALN.PRECISE\(", "_xlfn.GAMMALN.PRECISE(", formula)
1088 formula = re.sub(r"\bGAMMA\(", "_xlfn.GAMMA(", formula)
1089 formula = re.sub(r"\bGAUSS\(", "_xlfn.GAUSS(", formula)
1090 formula = re.sub(r"\bHYPGEOM.DIST\(", "_xlfn.HYPGEOM.DIST(", formula)
1091 formula = re.sub(r"\bIFNA\(", "_xlfn.IFNA(", formula)
1092 formula = re.sub(r"\bIFS\(", "_xlfn.IFS(", formula)
1093 formula = re.sub(r"\bIMAGE\(", "_xlfn.IMAGE(", formula)
1094 formula = re.sub(r"\bIMCOSH\(", "_xlfn.IMCOSH(", formula)
1095 formula = re.sub(r"\bIMCOT\(", "_xlfn.IMCOT(", formula)
1096 formula = re.sub(r"\bIMCSCH\(", "_xlfn.IMCSCH(", formula)
1097 formula = re.sub(r"\bIMCSC\(", "_xlfn.IMCSC(", formula)
1098 formula = re.sub(r"\bIMSECH\(", "_xlfn.IMSECH(", formula)
1099 formula = re.sub(r"\bIMSEC\(", "_xlfn.IMSEC(", formula)
1100 formula = re.sub(r"\bIMSINH\(", "_xlfn.IMSINH(", formula)
1101 formula = re.sub(r"\bIMTAN\(", "_xlfn.IMTAN(", formula)
1102 formula = re.sub(r"\bISFORMULA\(", "_xlfn.ISFORMULA(", formula)
1103 formula = re.sub(r"\bISOMITTED\(", "_xlfn.ISOMITTED(", formula)
1104 formula = re.sub(r"\bISOWEEKNUM\(", "_xlfn.ISOWEEKNUM(", formula)
1105 formula = re.sub(r"\bLET\(", "_xlfn.LET(", formula)
1106 formula = re.sub(r"\bLOGNORM.DIST\(", "_xlfn.LOGNORM.DIST(", formula)
1107 formula = re.sub(r"\bLOGNORM.INV\(", "_xlfn.LOGNORM.INV(", formula)
1108 formula = re.sub(r"\bMAXIFS\(", "_xlfn.MAXIFS(", formula)
1109 formula = re.sub(r"\bMINIFS\(", "_xlfn.MINIFS(", formula)
1110 formula = re.sub(r"\bMODE.MULT\(", "_xlfn.MODE.MULT(", formula)
1111 formula = re.sub(r"\bMODE.SNGL\(", "_xlfn.MODE.SNGL(", formula)
1112 formula = re.sub(r"\bMUNIT\(", "_xlfn.MUNIT(", formula)
1113 formula = re.sub(r"\bNEGBINOM.DIST\(", "_xlfn.NEGBINOM.DIST(", formula)
1114 formula = re.sub(r"\bNORM.DIST\(", "_xlfn.NORM.DIST(", formula)
1115 formula = re.sub(r"\bNORM.INV\(", "_xlfn.NORM.INV(", formula)
1116 formula = re.sub(r"\bNORM.S.DIST\(", "_xlfn.NORM.S.DIST(", formula)
1117 formula = re.sub(r"\bNORM.S.INV\(", "_xlfn.NORM.S.INV(", formula)
1118 formula = re.sub(r"\bNUMBERVALUE\(", "_xlfn.NUMBERVALUE(", formula)
1119 formula = re.sub(r"\bPDURATION\(", "_xlfn.PDURATION(", formula)
1120 formula = re.sub(r"\bPERCENTILE.EXC\(", "_xlfn.PERCENTILE.EXC(", formula)
1121 formula = re.sub(r"\bPERCENTILE.INC\(", "_xlfn.PERCENTILE.INC(", formula)
1122 formula = re.sub(r"\bPERCENTRANK.EXC\(", "_xlfn.PERCENTRANK.EXC(", formula)
1123 formula = re.sub(r"\bPERCENTRANK.INC\(", "_xlfn.PERCENTRANK.INC(", formula)
1124 formula = re.sub(r"\bPERMUTATIONA\(", "_xlfn.PERMUTATIONA(", formula)
1125 formula = re.sub(r"\bPHI\(", "_xlfn.PHI(", formula)
1126 formula = re.sub(r"\bPOISSON.DIST\(", "_xlfn.POISSON.DIST(", formula)
1127 formula = re.sub(r"\bQUARTILE.EXC\(", "_xlfn.QUARTILE.EXC(", formula)
1128 formula = re.sub(r"\bQUARTILE.INC\(", "_xlfn.QUARTILE.INC(", formula)
1129 formula = re.sub(r"\bQUERYSTRING\(", "_xlfn.QUERYSTRING(", formula)
1130 formula = re.sub(r"\bRANK.AVG\(", "_xlfn.RANK.AVG(", formula)
1131 formula = re.sub(r"\bRANK.EQ\(", "_xlfn.RANK.EQ(", formula)
1132 formula = re.sub(r"\bRRI\(", "_xlfn.RRI(", formula)
1133 formula = re.sub(r"\bSECH\(", "_xlfn.SECH(", formula)
1134 formula = re.sub(r"\bSEC\(", "_xlfn.SEC(", formula)
1135 formula = re.sub(r"\bSHEETS\(", "_xlfn.SHEETS(", formula)
1136 formula = re.sub(r"\bSHEET\(", "_xlfn.SHEET(", formula)
1137 formula = re.sub(r"\bSKEW.P\(", "_xlfn.SKEW.P(", formula)
1138 formula = re.sub(r"\bSTDEV.P\(", "_xlfn.STDEV.P(", formula)
1139 formula = re.sub(r"\bSTDEV.S\(", "_xlfn.STDEV.S(", formula)
1140 formula = re.sub(r"\bT.DIST.2T\(", "_xlfn.T.DIST.2T(", formula)
1141 formula = re.sub(r"\bT.DIST.RT\(", "_xlfn.T.DIST.RT(", formula)
1142 formula = re.sub(r"\bT.DIST\(", "_xlfn.T.DIST(", formula)
1143 formula = re.sub(r"\bT.INV.2T\(", "_xlfn.T.INV.2T(", formula)
1144 formula = re.sub(r"\bT.INV\(", "_xlfn.T.INV(", formula)
1145 formula = re.sub(r"\bT.TEST\(", "_xlfn.T.TEST(", formula)
1146 formula = re.sub(r"\bTEXTAFTER\(", "_xlfn.TEXTAFTER(", formula)
1147 formula = re.sub(r"\bTEXTBEFORE\(", "_xlfn.TEXTBEFORE(", formula)
1148 formula = re.sub(r"\bTEXTJOIN\(", "_xlfn.TEXTJOIN(", formula)
1149 formula = re.sub(r"\bUNICHAR\(", "_xlfn.UNICHAR(", formula)
1150 formula = re.sub(r"\bUNICODE\(", "_xlfn.UNICODE(", formula)
1151 formula = re.sub(r"\bVALUETOTEXT\(", "_xlfn.VALUETOTEXT(", formula)
1152 formula = re.sub(r"\bVAR.P\(", "_xlfn.VAR.P(", formula)
1153 formula = re.sub(r"\bVAR.S\(", "_xlfn.VAR.S(", formula)
1154 formula = re.sub(r"\bWEBSERVICE\(", "_xlfn.WEBSERVICE(", formula)
1155 formula = re.sub(r"\bWEIBULL.DIST\(", "_xlfn.WEIBULL.DIST(", formula)
1156 formula = re.sub(r"\bXMATCH\(", "_xlfn.XMATCH(", formula)
1157 formula = re.sub(r"\bXOR\(", "_xlfn.XOR(", formula)
1158 formula = re.sub(r"\bZ.TEST\(", "_xlfn.Z.TEST(", formula)
1160 return formula
1162 # Escape/expand table functions. This mainly involves converting Excel 2010
1163 # "@" table ref to 2007 "[#This Row],". We parse the string to avoid
1164 # replacements in string literals within the formula.
1165 @staticmethod
1166 def _prepare_table_formula(formula):
1167 if "@" not in formula:
1168 # No escaping required.
1169 return formula
1171 escaped_formula = []
1172 in_string_literal = False
1174 for char in formula:
1175 # Match the start/end of string literals to avoid escaping
1176 # references in strings.
1177 if char == '"':
1178 in_string_literal = not in_string_literal
1180 # Copy the string literal.
1181 if in_string_literal:
1182 escaped_formula.append(char)
1183 continue
1185 # Replace table reference.
1186 if char == "@":
1187 escaped_formula.append("[#This Row],")
1188 else:
1189 escaped_formula.append(char)
1191 return ("").join(escaped_formula)
1193 # Undecorated version of write_array_formula() and
1194 # write_dynamic_array_formula().
1195 def _write_array_formula(
1196 self,
1197 first_row,
1198 first_col,
1199 last_row,
1200 last_col,
1201 formula,
1202 cell_format=None,
1203 value=0,
1204 atype="static",
1205 ) -> Literal[0, -1]:
1206 # Swap last row/col with first row/col as necessary.
1207 if first_row > last_row:
1208 first_row, last_row = last_row, first_row
1209 if first_col > last_col:
1210 first_col, last_col = last_col, first_col
1212 # Check that row and col are valid and store max and min values.
1213 if self._check_dimensions(first_row, first_col):
1214 return -1
1215 if self._check_dimensions(last_row, last_col):
1216 return -1
1218 # Define array range
1219 if first_row == last_row and first_col == last_col:
1220 cell_range = xl_rowcol_to_cell(first_row, first_col)
1221 else:
1222 cell_range = (
1223 xl_rowcol_to_cell(first_row, first_col)
1224 + ":"
1225 + xl_rowcol_to_cell(last_row, last_col)
1226 )
1228 # Modify the formula string, as needed.
1229 formula = self._prepare_formula(formula)
1231 # Write previous row if in in-line string constant_memory mode.
1232 if self.constant_memory and first_row > self.previous_row:
1233 self._write_single_row(first_row)
1235 # Store the cell data in the worksheet data table.
1236 self.table[first_row][first_col] = CellArrayFormulaTuple(
1237 formula, cell_format, value, cell_range, atype
1238 )
1240 # Pad out the rest of the area with formatted zeroes.
1241 if not self.constant_memory:
1242 for row in range(first_row, last_row + 1):
1243 for col in range(first_col, last_col + 1):
1244 if row != first_row or col != first_col:
1245 self._write_number(row, col, 0, cell_format)
1247 return 0
1249 @convert_cell_args
1250 def write_datetime(
1251 self,
1252 row: int,
1253 col: int,
1254 date: datetime.datetime,
1255 cell_format: Optional[Format] = None,
1256 ) -> Literal[0, -1]:
1257 """
1258 Write a date or time to a worksheet cell.
1260 Args:
1261 row: The cell row (zero indexed).
1262 col: The cell column (zero indexed).
1263 date: Date and/or time as a datetime object.
1264 cell_format: A cell Format object.
1266 Returns:
1267 0: Success.
1268 -1: Row or column is out of worksheet bounds.
1270 """
1271 return self._write_datetime(row, col, date, cell_format)
1273 # Undecorated version of write_datetime().
1274 def _write_datetime(self, row: int, col: int, date, cell_format=None) -> int:
1275 # Check that row and col are valid and store max and min values.
1276 if self._check_dimensions(row, col):
1277 return -1
1279 # Write previous row if in in-line string constant_memory mode.
1280 if self.constant_memory and row > self.previous_row:
1281 self._write_single_row(row)
1283 # Convert datetime to an Excel date.
1284 number = self._convert_date_time(date)
1286 # Add the default date format.
1287 if cell_format is None:
1288 cell_format = self.default_date_format
1290 # Store the cell data in the worksheet data table.
1291 self.table[row][col] = CellDatetimeTuple(number, cell_format)
1293 return 0
1295 @convert_cell_args
1296 def write_boolean(
1297 self, row: int, col: int, boolean: bool, cell_format: Optional[Format] = None
1298 ):
1299 """
1300 Write a boolean value to a worksheet cell.
1302 Args:
1303 row: The cell row (zero indexed).
1304 col: The cell column (zero indexed).
1305 boolean: Cell data. bool type.
1306 cell_format: An optional cell Format object.
1308 Returns:
1309 0: Success.
1310 -1: Row or column is out of worksheet bounds.
1312 """
1313 return self._write_boolean(row, col, boolean, cell_format)
1315 # Undecorated version of write_boolean().
1316 def _write_boolean(self, row: int, col: int, boolean, cell_format=None) -> int:
1317 # Check that row and col are valid and store max and min values.
1318 if self._check_dimensions(row, col):
1319 return -1
1321 # Write previous row if in in-line string constant_memory mode.
1322 if self.constant_memory and row > self.previous_row:
1323 self._write_single_row(row)
1325 if boolean:
1326 value = 1
1327 else:
1328 value = 0
1330 # Store the cell data in the worksheet data table.
1331 self.table[row][col] = CellBooleanTuple(value, cell_format)
1333 return 0
1335 # Write a hyperlink. This is comprised of two elements: the displayed
1336 # string and the non-displayed link. The displayed string is the same as
1337 # the link unless an alternative string is specified. The display string
1338 # is written using the write_string() method. Therefore the max characters
1339 # string limit applies.
1340 #
1341 # The hyperlink can be to a http, ftp, mail, internal sheet, or external
1342 # directory urls.
1343 @convert_cell_args
1344 def write_url(
1345 self,
1346 row: int,
1347 col: int,
1348 url: str,
1349 cell_format: Optional[Format] = None,
1350 string: Optional[str] = None,
1351 tip: Optional[str] = None,
1352 ):
1353 """
1354 Write a hyperlink to a worksheet cell.
1356 Args:
1357 row: The cell row (zero indexed).
1358 col: The cell column (zero indexed).
1359 url: Hyperlink url.
1360 format: An optional cell Format object.
1361 string: An optional display string for the hyperlink.
1362 tip: An optional tooltip.
1363 Returns:
1364 0: Success.
1365 -1: Row or column is out of worksheet bounds.
1366 -2: String longer than 32767 characters.
1367 -3: URL longer than Excel limit of 255 characters.
1368 -4: Exceeds Excel limit of 65,530 urls per worksheet.
1369 """
1370 return self._write_url(row, col, url, cell_format, string, tip)
1372 # Undecorated version of write_url().
1373 def _write_url(
1374 self, row: int, col: int, url, cell_format=None, string=None, tip=None
1375 ) -> int:
1376 # Check that row and col are valid and store max and min values
1377 if self._check_dimensions(row, col):
1378 return -1
1380 # If the URL is a string convert it to a Url object.
1381 if not isinstance(url, Url):
1383 # For backwards compatibility check if the string URL exceeds the
1384 # Excel character limit for URLs and ignore it with a warning.
1385 max_url = self.max_url_length
1386 if "#" in url:
1387 url_str, anchor_str = url.split("#", 1)
1388 else:
1389 url_str = url
1390 anchor_str = ""
1392 if len(url_str) > max_url or len(anchor_str) > max_url:
1393 warn(
1394 f"Ignoring URL '{url}' with link or location/anchor > {max_url} "
1395 f"characters since it exceeds Excel's limit for URLs."
1396 )
1397 return -3
1399 url = Url(url)
1401 if string is not None:
1402 url._text = string
1404 if tip is not None:
1405 url._tip = tip
1407 # Check the limit of URLs per worksheet.
1408 self.hlink_count += 1
1410 if self.hlink_count > 65530:
1411 warn(
1412 f"Ignoring URL '{url._original_url}' since it exceeds Excel's limit of "
1413 f"65,530 URLs per worksheet."
1414 )
1415 return -4
1417 # Add the default URL format.
1418 if cell_format is None:
1419 cell_format = self.default_url_format
1421 if not self.ignore_write_string:
1422 # Write previous row if in in-line string constant_memory mode.
1423 if self.constant_memory and row > self.previous_row:
1424 self._write_single_row(row)
1426 # Write the hyperlink string.
1427 self._write_string(row, col, url.text, cell_format)
1429 # Store the hyperlink data in a separate structure.
1430 self.hyperlinks[row][col] = url
1432 return 0
1434 @convert_cell_args
1435 def write_rich_string(
1436 self, row: int, col: int, *args: Union[str, Format]
1437 ) -> Literal[0, -1, -2, -3, -4, -5]:
1438 """
1439 Write a "rich" string with multiple formats to a worksheet cell.
1441 Args:
1442 row: The cell row (zero indexed).
1443 col: The cell column (zero indexed).
1444 string_parts: String and format pairs.
1445 cell_format: Optional Format object.
1447 Returns:
1448 0: Success.
1449 -1: Row or column is out of worksheet bounds.
1450 -2: String truncated to 32k characters.
1451 -3: 2 consecutive formats used.
1452 -4: Empty string used.
1453 -5: Insufficient parameters.
1455 """
1457 return self._write_rich_string(row, col, *args)
1459 # Undecorated version of write_rich_string().
1460 def _write_rich_string(self, row: int, col: int, *args) -> int:
1461 tokens = list(args)
1462 cell_format = None
1463 string_index = 0
1464 raw_string = ""
1466 # Check that row and col are valid and store max and min values
1467 if self._check_dimensions(row, col):
1468 return -1
1470 # If the last arg is a format we use it as the cell format.
1471 if isinstance(tokens[-1], Format):
1472 cell_format = tokens.pop()
1474 # Create a temp XMLWriter object and use it to write the rich string
1475 # XML to a string.
1476 fh = StringIO()
1477 self.rstring = XMLwriter()
1478 self.rstring._set_filehandle(fh)
1480 # Create a temp format with the default font for unformatted fragments.
1481 default = Format()
1483 # Convert list of format, string tokens to pairs of (format, string)
1484 # except for the first string fragment which doesn't require a default
1485 # formatting run. Use the default for strings without a leading format.
1486 fragments = []
1487 previous = "format"
1488 pos = 0
1490 if len(tokens) <= 2:
1491 warn(
1492 "You must specify more than 2 format/fragments for rich "
1493 "strings. Ignoring input in write_rich_string()."
1494 )
1495 return -5
1497 for token in tokens:
1498 if not isinstance(token, Format):
1499 # Token is a string.
1500 if previous != "format":
1501 # If previous token wasn't a format add one before string.
1502 fragments.append(default)
1503 fragments.append(token)
1504 else:
1505 # If previous token was a format just add the string.
1506 fragments.append(token)
1508 if token == "":
1509 warn(
1510 "Excel doesn't allow empty strings in rich strings. "
1511 "Ignoring input in write_rich_string()."
1512 )
1513 return -4
1515 # Keep track of unformatted string.
1516 raw_string += token
1517 previous = "string"
1518 else:
1519 # Can't allow 2 formats in a row.
1520 if previous == "format" and pos > 0:
1521 warn(
1522 "Excel doesn't allow 2 consecutive formats in rich "
1523 "strings. Ignoring input in write_rich_string()."
1524 )
1525 return -3
1527 # Token is a format object. Add it to the fragment list.
1528 fragments.append(token)
1529 previous = "format"
1531 pos += 1
1533 # If the first token is a string start the <r> element.
1534 if not isinstance(fragments[0], Format):
1535 self.rstring._xml_start_tag("r")
1537 # Write the XML elements for the $format $string fragments.
1538 for token in fragments:
1539 if isinstance(token, Format):
1540 # Write the font run.
1541 self.rstring._xml_start_tag("r")
1542 self._write_font(token)
1543 else:
1544 # Write the string fragment part, with whitespace handling.
1545 attributes = []
1547 if _preserve_whitespace(token):
1548 attributes.append(("xml:space", "preserve"))
1550 self.rstring._xml_data_element("t", token, attributes)
1551 self.rstring._xml_end_tag("r")
1553 # Read the in-memory string.
1554 string = self.rstring.fh.getvalue()
1556 # Check that the string is < 32767 chars.
1557 if len(raw_string) > self.xls_strmax:
1558 warn(
1559 "String length must be less than or equal to Excel's limit "
1560 "of 32,767 characters in write_rich_string()."
1561 )
1562 return -2
1564 # Write a shared string or an in-line string in constant_memory mode.
1565 if not self.constant_memory:
1566 string_index = self.str_table._get_shared_string_index(string)
1567 else:
1568 string_index = string
1570 # Write previous row if in in-line string constant_memory mode.
1571 if self.constant_memory and row > self.previous_row:
1572 self._write_single_row(row)
1574 # Store the cell data in the worksheet data table.
1575 self.table[row][col] = CellRichStringTuple(
1576 string_index, cell_format, raw_string
1577 )
1579 return 0
1581 def add_write_handler(self, user_type, user_function) -> None:
1582 """
1583 Add a callback function to the write() method to handle user defined
1584 types.
1586 Args:
1587 user_type: The user type() to match on.
1588 user_function: The user defined function to write the type data.
1589 Returns:
1590 Nothing.
1592 """
1594 self.write_handlers[user_type] = user_function
1596 @convert_cell_args
1597 def write_row(
1598 self, row: int, col: int, data, cell_format: Optional[Format] = None
1599 ) -> Union[Literal[0], Any]:
1600 """
1601 Write a row of data starting from (row, col).
1603 Args:
1604 row: The cell row (zero indexed).
1605 col: The cell column (zero indexed).
1606 data: A list of tokens to be written with write().
1607 format: An optional cell Format object.
1608 Returns:
1609 0: Success.
1610 other: Return value of write() method.
1612 """
1613 for token in data:
1614 error = self._write(row, col, token, cell_format)
1615 if error:
1616 return error
1617 col += 1
1619 return 0
1621 @convert_cell_args
1622 def write_column(
1623 self, row: int, col: int, data, cell_format: Optional[Format] = None
1624 ) -> Union[Literal[0], Any]:
1625 """
1626 Write a column of data starting from (row, col).
1628 Args:
1629 row: The cell row (zero indexed).
1630 col: The cell column (zero indexed).
1631 data: A list of tokens to be written with write().
1632 format: An optional cell Format object.
1633 Returns:
1634 0: Success.
1635 other: Return value of write() method.
1637 """
1638 for token in data:
1639 error = self._write(row, col, token, cell_format)
1640 if error:
1641 return error
1642 row += 1
1644 return 0
1646 @convert_cell_args
1647 def insert_image(
1648 self,
1649 row: int,
1650 col: int,
1651 source: Union[str, BytesIO, Image],
1652 options: Optional[Dict[str, Any]] = None,
1653 ) -> Literal[0, -1]:
1654 """
1655 Insert an image with its top-left corner in a worksheet cell.
1657 Args:
1658 row: The cell row (zero indexed).
1659 col: The cell column (zero indexed).
1660 source: Filename, BytesIO, or Image object.
1661 options: Position, scale, url and data stream of the image.
1663 Returns:
1664 0: Success.
1665 -1: Row or column is out of worksheet bounds.
1667 """
1668 # Check insert (row, col) without storing.
1669 if self._check_dimensions(row, col, True, True):
1670 warn(f"Cannot insert image at ({row}, {col}).")
1671 return -1
1673 # Convert the source to an Image object.
1674 image = self._image_from_source(source, options)
1676 image._row = row
1677 image._col = col
1678 image._set_user_options(options)
1680 self.images.append(image)
1682 return 0
1684 @convert_cell_args
1685 def embed_image(
1686 self,
1687 row: int,
1688 col: int,
1689 source: Union[str, BytesIO, Image],
1690 options: Optional[Dict[str, Any]] = None,
1691 ) -> Literal[0, -1]:
1692 """
1693 Embed an image in a worksheet cell.
1695 Args:
1696 row: The cell row (zero indexed).
1697 col: The cell column (zero indexed).
1698 source: Filename, BytesIO, or Image object.
1699 options: Url and data stream of the image.
1701 Returns:
1702 0: Success.
1703 -1: Row or column is out of worksheet bounds.
1705 """
1706 # Check insert (row, col) without storing.
1707 if self._check_dimensions(row, col):
1708 warn(f"Cannot embed image at ({row}, {col}).")
1709 return -1
1711 if options is None:
1712 options = {}
1714 # Convert the source to an Image object.
1715 image = self._image_from_source(source, options)
1716 image._set_user_options(options)
1718 cell_format = options.get("cell_format", None)
1720 if image.url:
1721 if cell_format is None:
1722 cell_format = self.default_url_format
1724 self.ignore_write_string = True
1725 self.write_url(row, col, image.url, cell_format)
1726 self.ignore_write_string = False
1728 image_index = self.embedded_images.get_image_index(image)
1730 # Store the cell error and image index in the worksheet data table.
1731 self.table[row][col] = CellErrorTuple("#VALUE!", cell_format, image_index)
1733 return 0
1735 @convert_cell_args
1736 def insert_textbox(
1737 self, row: int, col: int, text: str, options: Optional[Dict[str, Any]] = None
1738 ) -> Literal[0, -1]:
1739 """
1740 Insert an textbox with its top-left corner in a worksheet cell.
1742 Args:
1743 row: The cell row (zero indexed).
1744 col: The cell column (zero indexed).
1745 text: The text for the textbox.
1746 options: Textbox options.
1748 Returns:
1749 0: Success.
1750 -1: Row or column is out of worksheet bounds.
1752 """
1753 # Check insert (row, col) without storing.
1754 if self._check_dimensions(row, col, True, True):
1755 warn(f"Cannot insert textbox at ({row}, {col}).")
1756 return -1
1758 if text is None:
1759 text = ""
1761 if options is None:
1762 options = {}
1764 x_offset = options.get("x_offset", 0)
1765 y_offset = options.get("y_offset", 0)
1766 x_scale = options.get("x_scale", 1)
1767 y_scale = options.get("y_scale", 1)
1768 anchor = options.get("object_position", 1)
1769 description = options.get("description", None)
1770 decorative = options.get("decorative", False)
1772 self.shapes.append(
1773 [
1774 row,
1775 col,
1776 x_offset,
1777 y_offset,
1778 x_scale,
1779 y_scale,
1780 text,
1781 anchor,
1782 options,
1783 description,
1784 decorative,
1785 ]
1786 )
1787 return 0
1789 @convert_cell_args
1790 def insert_chart(
1791 self, row: int, col: int, chart: Chart, options: Optional[Dict[str, Any]] = None
1792 ) -> Literal[0, -1, -2]:
1793 """
1794 Insert an chart with its top-left corner in a worksheet cell.
1796 Args:
1797 row: The cell row (zero indexed).
1798 col: The cell column (zero indexed).
1799 chart: Chart object.
1800 options: Position and scale of the chart.
1802 Returns:
1803 0: Success.
1804 -1: Row or column is out of worksheet bounds.
1806 """
1807 # Check insert (row, col) without storing.
1808 if self._check_dimensions(row, col, True, True):
1809 warn(f"Cannot insert chart at ({row}, {col}).")
1810 return -1
1812 if options is None:
1813 options = {}
1815 # Ensure a chart isn't inserted more than once.
1816 if chart.already_inserted or chart.combined and chart.combined.already_inserted:
1817 warn("Chart cannot be inserted in a worksheet more than once.")
1818 return -2
1820 chart.already_inserted = True
1822 if chart.combined:
1823 chart.combined.already_inserted = True
1825 x_offset = options.get("x_offset", 0)
1826 y_offset = options.get("y_offset", 0)
1827 x_scale = options.get("x_scale", 1)
1828 y_scale = options.get("y_scale", 1)
1829 anchor = options.get("object_position", 1)
1830 description = options.get("description", None)
1831 decorative = options.get("decorative", False)
1833 # Allow Chart to override the scale and offset.
1834 if chart.x_scale != 1:
1835 x_scale = chart.x_scale
1837 if chart.y_scale != 1:
1838 y_scale = chart.y_scale
1840 if chart.x_offset:
1841 x_offset = chart.x_offset
1843 if chart.y_offset:
1844 y_offset = chart.y_offset
1846 self.charts.append(
1847 [
1848 row,
1849 col,
1850 chart,
1851 x_offset,
1852 y_offset,
1853 x_scale,
1854 y_scale,
1855 anchor,
1856 description,
1857 decorative,
1858 ]
1859 )
1860 return 0
1862 @convert_cell_args
1863 def write_comment(
1864 self, row: int, col: int, comment: str, options: Optional[Dict[str, Any]] = None
1865 ) -> Literal[0, -1, -2]:
1866 """
1867 Write a comment to a worksheet cell.
1869 Args:
1870 row: The cell row (zero indexed).
1871 col: The cell column (zero indexed).
1872 comment: Cell comment. Str.
1873 options: Comment formatting options.
1875 Returns:
1876 0: Success.
1877 -1: Row or column is out of worksheet bounds.
1878 -2: String longer than 32k characters.
1880 """
1881 # Check that row and col are valid and store max and min values
1882 if self._check_dimensions(row, col):
1883 return -1
1885 # Check that the comment string is < 32767 chars.
1886 if len(comment) > self.xls_strmax:
1887 return -2
1889 self.has_vml = True
1890 self.has_comments = True
1892 # Store the options of the cell comment, to process on file close.
1893 comment = CommentType(row, col, comment, options)
1894 self.comments[row][col] = comment
1896 return 0
1898 def show_comments(self) -> None:
1899 """
1900 Make any comments in the worksheet visible.
1902 Args:
1903 None.
1905 Returns:
1906 Nothing.
1908 """
1909 self.comments_visible = True
1911 def set_background(
1912 self, source: Union[str, BytesIO, Image], is_byte_stream: bool = False
1913 ) -> Literal[0]:
1914 """
1915 Set a background image for a worksheet.
1917 Args:
1918 source: Filename, BytesIO, or Image object.
1919 is_byte_stream: Deprecated. Use a BytesIO object instead.
1921 Returns:
1922 0: Success.
1924 """
1925 # Convert the source to an Image object.
1926 image = self._image_from_source(source)
1928 self.background_image = image
1930 if is_byte_stream:
1931 warn(
1932 "The `is_byte_stream` parameter in `set_background()` is deprecated. "
1933 "This argument can be omitted if you are using a BytesIO object."
1934 )
1936 return 0
1938 def set_comments_author(self, author) -> None:
1939 """
1940 Set the default author of the cell comments.
1942 Args:
1943 author: Comment author name. String.
1945 Returns:
1946 Nothing.
1948 """
1949 self.comments_author = author
1951 def get_name(self):
1952 """
1953 Retrieve the worksheet name.
1955 Args:
1956 None.
1958 Returns:
1959 Nothing.
1961 """
1962 # There is no set_name() method. Name must be set in add_worksheet().
1963 return self.name
1965 def activate(self) -> None:
1966 """
1967 Set this worksheet as the active worksheet, i.e. the worksheet that is
1968 displayed when the workbook is opened. Also set it as selected.
1970 Note: An active worksheet cannot be hidden.
1972 Args:
1973 None.
1975 Returns:
1976 Nothing.
1978 """
1979 self.hidden = 0
1980 self.selected = 1
1981 self.worksheet_meta.activesheet = self.index
1983 def select(self) -> None:
1984 """
1985 Set current worksheet as a selected worksheet, i.e. the worksheet
1986 has its tab highlighted.
1988 Note: A selected worksheet cannot be hidden.
1990 Args:
1991 None.
1993 Returns:
1994 Nothing.
1996 """
1997 self.selected = 1
1998 self.hidden = 0
2000 def hide(self) -> None:
2001 """
2002 Hide the current worksheet.
2004 Args:
2005 None.
2007 Returns:
2008 Nothing.
2010 """
2011 self.hidden = 1
2013 # A hidden worksheet shouldn't be active or selected.
2014 self.selected = 0
2016 def very_hidden(self) -> None:
2017 """
2018 Hide the current worksheet. This can only be unhidden by VBA.
2020 Args:
2021 None.
2023 Returns:
2024 Nothing.
2026 """
2027 self.hidden = 2
2029 # A hidden worksheet shouldn't be active or selected.
2030 self.selected = 0
2032 def set_first_sheet(self) -> None:
2033 """
2034 Set current worksheet as the first visible sheet. This is necessary
2035 when there are a large number of worksheets and the activated
2036 worksheet is not visible on the screen.
2038 Note: A selected worksheet cannot be hidden.
2040 Args:
2041 None.
2043 Returns:
2044 Nothing.
2046 """
2047 self.hidden = 0 # Active worksheet can't be hidden.
2048 self.worksheet_meta.firstsheet = self.index
2050 @convert_column_args
2051 def set_column(
2052 self,
2053 first_col: int,
2054 last_col: int,
2055 width: Optional[float] = None,
2056 cell_format: Optional[Format] = None,
2057 options: Optional[Dict[str, Any]] = None,
2058 ) -> Literal[0, -1]:
2059 """
2060 Set the width, and other properties of a single column or a
2061 range of columns.
2063 Args:
2064 first_col: First column (zero-indexed).
2065 last_col: Last column (zero-indexed). Can be same as first_col.
2066 width: Column width. (optional).
2067 cell_format: Column cell_format. (optional).
2068 options: Dict of options such as hidden and level.
2070 Returns:
2071 0: Success.
2072 -1: Column number is out of worksheet bounds.
2074 """
2075 # Convert from Excel character width to pixels. The conversion is
2076 # different below 1 character widths.
2077 if width is None:
2078 width_pixels = None
2079 elif width == 0.0:
2080 width_pixels = 0
2081 elif width < 1.0:
2082 width_pixels = round(width * (self.max_digit_width + self.cell_padding))
2083 else:
2084 width_pixels = round(width * self.max_digit_width) + self.cell_padding
2086 return self.set_column_pixels(
2087 first_col, last_col, width_pixels, cell_format, options
2088 )
2090 @convert_column_args
2091 def set_column_pixels(
2092 self,
2093 first_col: int,
2094 last_col: int,
2095 width: Optional[float] = None,
2096 cell_format: Optional[Format] = None,
2097 options: Optional[Dict[str, Any]] = None,
2098 ) -> Literal[0, -1]:
2099 """
2100 Set the width, and other properties of a single column or a
2101 range of columns, where column width is in pixels.
2103 Args:
2104 first_col: First column (zero-indexed).
2105 last_col: Last column (zero-indexed). Can be same as first_col.
2106 width: Column width in pixels. (optional).
2107 cell_format: Column cell_format. (optional).
2108 options: Dict of options such as hidden and level.
2110 Returns:
2111 0: Success.
2112 -1: Column number is out of worksheet bounds.
2114 """
2115 if options is None:
2116 options = {}
2118 # Ensure 2nd col is larger than first.
2119 if first_col > last_col:
2120 first_col, last_col = (last_col, first_col)
2122 # Don't modify the row dimensions when checking the columns.
2123 ignore_row = True
2125 # Set optional column values.
2126 hidden = options.get("hidden", False)
2127 collapsed = options.get("collapsed", False)
2128 level = options.get("level", 0)
2130 # Store the column dimension only in some conditions.
2131 if cell_format or (width and hidden):
2132 ignore_col = False
2133 else:
2134 ignore_col = True
2136 # Check that each column is valid and store the max and min values.
2137 if self._check_dimensions(0, last_col, ignore_row, ignore_col):
2138 return -1
2139 if self._check_dimensions(0, first_col, ignore_row, ignore_col):
2140 return -1
2142 # Set the limits for the outline levels (0 <= x <= 7).
2143 level = max(level, 0)
2144 level = min(level, 7)
2146 self.outline_col_level = max(self.outline_col_level, level)
2148 # Store the column data.
2149 for col in range(first_col, last_col + 1):
2150 self.col_info[col] = ColumnInfo(
2151 width=width,
2152 column_format=cell_format,
2153 hidden=hidden,
2154 level=level,
2155 collapsed=collapsed,
2156 )
2158 # Store the column change to allow optimizations.
2159 self.col_size_changed = True
2161 return 0
2163 def autofit(self, max_width: int = None) -> None:
2164 """
2165 Simulate autofit based on the data, and datatypes in each column.
2167 Args:
2168 max_width (optional): max column width to autofit, in pixels.
2170 Returns:
2171 Nothing.
2173 """
2174 # pylint: disable=too-many-nested-blocks
2175 if self.constant_memory:
2176 warn("Autofit is not supported in constant_memory mode.")
2177 return
2179 # No data written to the target sheet; nothing to autofit
2180 if self.dim_rowmax is None:
2181 return
2183 # Store the max pixel width for each column.
2184 col_width_max = {}
2186 # Convert the autofit maximum pixel width to a column/character width,
2187 # but limit it to the Excel max limit.
2188 if max_width is None:
2189 max_width = self.max_col_width
2191 max_width = min(max_width, self.max_col_width)
2193 # Create a reverse lookup for the share strings table so we can convert
2194 # the string id back to the original string.
2195 strings = sorted(
2196 self.str_table.string_table, key=self.str_table.string_table.__getitem__
2197 )
2199 for row_num in range(self.dim_rowmin, self.dim_rowmax + 1):
2200 if not self.table.get(row_num):
2201 continue
2203 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
2204 if col_num in self.table[row_num]:
2205 cell = self.table[row_num][col_num]
2206 cell_type = cell.__class__.__name__
2207 length = 0
2209 if cell_type in ("String", "RichString"):
2210 # Handle strings and rich strings.
2211 #
2212 # For standard shared strings we do a reverse lookup
2213 # from the shared string id to the actual string. For
2214 # rich strings we use the unformatted string. We also
2215 # split multi-line strings and handle each part
2216 # separately.
2217 if cell_type == "String":
2218 string_id = cell.string
2219 string = strings[string_id]
2220 else:
2221 string = cell.raw_string
2223 if "\n" not in string:
2224 # Single line string.
2225 length = xl_pixel_width(string)
2226 else:
2227 # Handle multi-line strings.
2228 for string in string.split("\n"):
2229 seg_length = xl_pixel_width(string)
2230 length = max(length, seg_length)
2232 elif cell_type == "Number":
2233 # Handle numbers.
2234 #
2235 # We use a workaround/optimization for numbers since
2236 # digits all have a pixel width of 7. This gives a
2237 # slightly greater width for the decimal place and
2238 # minus sign but only by a few pixels and
2239 # over-estimation is okay.
2240 length = 7 * len(str(cell.number))
2242 elif cell_type == "Datetime":
2243 # Handle dates.
2244 #
2245 # The following uses the default width for mm/dd/yyyy
2246 # dates. It isn't feasible to parse the number format
2247 # to get the actual string width for all format types.
2248 length = self.default_date_width
2250 elif cell_type == "Boolean":
2251 # Handle boolean values.
2252 #
2253 # Use the Excel standard widths for TRUE and FALSE.
2254 if cell.boolean:
2255 length = 31
2256 else:
2257 length = 36
2259 elif cell_type in ("Formula", "ArrayFormula"):
2260 # Handle formulas.
2261 #
2262 # We only try to autofit a formula if it has a
2263 # non-zero value.
2264 if isinstance(cell.value, (float, int)):
2265 if cell.value > 0:
2266 length = 7 * len(str(cell.value))
2268 elif isinstance(cell.value, str):
2269 length = xl_pixel_width(cell.value)
2271 elif isinstance(cell.value, bool):
2272 if cell.value:
2273 length = 31
2274 else:
2275 length = 36
2277 # If the cell is in an autofilter header we add an
2278 # additional 16 pixels for the dropdown arrow.
2279 if self.filter_cells.get((row_num, col_num)) and length > 0:
2280 length += 16
2282 # Add the string length to the lookup table.
2283 width_max = col_width_max.get(col_num, 0)
2284 if length > width_max:
2285 col_width_max[col_num] = length
2287 # Apply the width to the column.
2288 for col_num, width in col_width_max.items():
2289 # Add a 7 pixels padding, like Excel.
2290 width += 7
2292 # Limit the width to the maximum user or Excel value.
2293 width = min(width, max_width)
2295 # Add the width to an existing col info structure or add a new one.
2296 if self.col_info.get(col_num):
2297 # We only update the width for an existing column if it is
2298 # greater than the user defined value. This allows the user
2299 # to pre-load a minimum col width.
2300 col_info = self.col_info.get(col_num)
2301 user_width = col_info.width
2302 hidden = col_info.hidden
2303 if user_width is not None and not hidden:
2304 # Col info is user defined.
2305 if width > user_width:
2306 self.col_info[col_num].width = width
2307 self.col_info[col_num].hidden = True
2308 else:
2309 self.col_info[col_num].width = width
2310 self.col_info[col_num].hidden = True
2311 else:
2312 self.col_info[col_num] = ColumnInfo(
2313 width=width,
2314 autofit=True,
2315 )
2317 def set_row(
2318 self,
2319 row: int,
2320 height: Optional[float] = None,
2321 cell_format: Optional[Format] = None,
2322 options: Optional[Dict[str, Any]] = None,
2323 ) -> Literal[0, -1]:
2324 """
2325 Set the width, and other properties of a row.
2327 Args:
2328 row: Row number (zero-indexed).
2329 height: Row height. (optional).
2330 cell_format: Row cell_format. (optional).
2331 options: Dict of options such as hidden, level and collapsed.
2333 Returns:
2334 0: Success.
2335 -1: Row number is out of worksheet bounds.
2337 """
2338 if height is not None:
2339 pixel_height = round(height * 4.0 / 3.0)
2340 else:
2341 pixel_height = None
2343 return self.set_row_pixels(row, pixel_height, cell_format, options)
2345 def set_row_pixels(
2346 self,
2347 row: int,
2348 height: Optional[float] = None,
2349 cell_format: Optional[Format] = None,
2350 options: Optional[Dict[str, Any]] = None,
2351 ) -> Literal[0, -1]:
2352 """
2353 Set the width (in pixels), and other properties of a row.
2355 Args:
2356 row: Row number (zero-indexed).
2357 height: Row height in pixels. (optional).
2358 cell_format: Row cell_format. (optional).
2359 options: Dict of options such as hidden, level and collapsed.
2361 Returns:
2362 0: Success.
2363 -1: Row number is out of worksheet bounds.
2365 """
2366 if options is None:
2367 options = {}
2369 # Use minimum col in _check_dimensions().
2370 if self.dim_colmin is not None:
2371 min_col = self.dim_colmin
2372 else:
2373 min_col = 0
2375 # Check that row is valid.
2376 if self._check_dimensions(row, min_col):
2377 return -1
2379 if height is None:
2380 height = self.default_row_height
2382 # Set optional row values.
2383 hidden = options.get("hidden", False)
2384 collapsed = options.get("collapsed", False)
2385 level = options.get("level", 0)
2387 # If the height is 0 the row is hidden and the height is the default.
2388 if height == 0:
2389 hidden = True
2390 height = self.default_row_height
2392 # Set the limits for the outline levels (0 <= x <= 7).
2393 level = max(level, 0)
2394 level = min(level, 7)
2396 self.outline_row_level = max(self.outline_row_level, level)
2398 # Store the row properties.
2399 self.row_info[row] = RowInfo(
2400 height=height,
2401 row_format=cell_format,
2402 hidden=hidden,
2403 level=level,
2404 collapsed=collapsed,
2405 )
2407 # Store the row change to allow optimizations.
2408 self.row_size_changed = True
2410 # Store the row sizes for use when calculating image vertices.
2411 self.row_sizes[row] = [height, hidden]
2413 return 0
2415 def set_default_row(
2416 self, height: Optional[float] = None, hide_unused_rows: bool = False
2417 ) -> None:
2418 """
2419 Set the default row properties.
2421 Args:
2422 height: Default height. Optional, defaults to 15.
2423 hide_unused_rows: Hide unused rows. Optional, defaults to False.
2425 Returns:
2426 Nothing.
2428 """
2429 if height is None:
2430 pixel_height = self.default_row_height
2431 else:
2432 pixel_height = int(round(height * 4.0 / 3.0))
2434 if pixel_height != self.original_row_height:
2435 # Store the row change to allow optimizations.
2436 self.row_size_changed = True
2437 self.default_row_height = pixel_height
2439 if hide_unused_rows:
2440 self.default_row_zeroed = 1
2442 @convert_range_args
2443 def merge_range(
2444 self,
2445 first_row: int,
2446 first_col: int,
2447 last_row: int,
2448 last_col: int,
2449 data: Any,
2450 cell_format: Optional[Format] = None,
2451 ) -> int:
2452 """
2453 Merge a range of cells.
2455 Args:
2456 first_row: The first row of the cell range. (zero indexed).
2457 first_col: The first column of the cell range.
2458 last_row: The last row of the cell range. (zero indexed).
2459 last_col: The last column of the cell range.
2460 data: Cell data.
2461 cell_format: Cell Format object.
2463 Returns:
2464 0: Success.
2465 -1: Row or column is out of worksheet bounds.
2466 other: Return value of write().
2468 """
2469 # Merge a range of cells. The first cell should contain the data and
2470 # the others should be blank. All cells should have the same format.
2472 # Excel doesn't allow a single cell to be merged
2473 if first_row == last_row and first_col == last_col:
2474 warn("Can't merge single cell")
2475 return -1
2477 # Swap last row/col with first row/col as necessary
2478 if first_row > last_row:
2479 first_row, last_row = (last_row, first_row)
2480 if first_col > last_col:
2481 first_col, last_col = (last_col, first_col)
2483 # Check that row and col are valid and store max and min values.
2484 if self._check_dimensions(first_row, first_col):
2485 return -1
2486 if self._check_dimensions(last_row, last_col):
2487 return -1
2489 # Check if the merge range overlaps a previous merged or table range.
2490 # This is a critical file corruption error in Excel.
2491 cell_range = xl_range(first_row, first_col, last_row, last_col)
2492 for row in range(first_row, last_row + 1):
2493 for col in range(first_col, last_col + 1):
2494 if self.merged_cells.get((row, col)):
2495 previous_range = self.merged_cells.get((row, col))
2496 raise OverlappingRange(
2497 f"Merge range '{cell_range}' overlaps previous merge "
2498 f"range '{previous_range}'."
2499 )
2501 if self.table_cells.get((row, col)):
2502 previous_range = self.table_cells.get((row, col))
2503 raise OverlappingRange(
2504 f"Merge range '{cell_range}' overlaps previous table "
2505 f"range '{previous_range}'."
2506 )
2508 self.merged_cells[(row, col)] = cell_range
2510 # Store the merge range.
2511 self.merge.append([first_row, first_col, last_row, last_col])
2513 # Write the first cell
2514 self._write(first_row, first_col, data, cell_format)
2516 # Pad out the rest of the area with formatted blank cells.
2517 for row in range(first_row, last_row + 1):
2518 for col in range(first_col, last_col + 1):
2519 if row == first_row and col == first_col:
2520 continue
2521 self._write_blank(row, col, "", cell_format)
2523 return 0
2525 @convert_range_args
2526 def autofilter(
2527 self, first_row: int, first_col: int, last_row: int, last_col: int
2528 ) -> None:
2529 """
2530 Set the autofilter area in the worksheet.
2532 Args:
2533 first_row: The first row of the cell range. (zero indexed).
2534 first_col: The first column of the cell range.
2535 last_row: The last row of the cell range. (zero indexed).
2536 last_col: The last column of the cell range.
2538 Returns:
2539 Nothing.
2541 """
2542 # Reverse max and min values if necessary.
2543 if last_row < first_row:
2544 first_row, last_row = (last_row, first_row)
2545 if last_col < first_col:
2546 first_col, last_col = (last_col, first_col)
2548 # Build up the autofilter area range "Sheet1!$A$1:$C$13".
2549 area = self._convert_name_area(first_row, first_col, last_row, last_col)
2550 ref = xl_range(first_row, first_col, last_row, last_col)
2552 self.autofilter_area = area
2553 self.autofilter_ref = ref
2554 self.filter_range = [first_col, last_col]
2556 # Store the filter cell positions for use in the autofit calculation.
2557 for col in range(first_col, last_col + 1):
2558 # Check that the autofilter doesn't overlap a table filter.
2559 if self.filter_cells.get((first_row, col)):
2560 filter_type, filter_range = self.filter_cells.get((first_row, col))
2561 if filter_type == "table":
2562 raise OverlappingRange(
2563 f"Worksheet autofilter range '{ref}' overlaps previous "
2564 f"Table autofilter range '{filter_range}'."
2565 )
2567 self.filter_cells[(first_row, col)] = ("worksheet", ref)
2569 def filter_column(self, col: int, criteria: str) -> None:
2570 """
2571 Set the column filter criteria.
2573 Args:
2574 col: Filter column (zero-indexed).
2575 criteria: Filter criteria.
2577 Returns:
2578 Nothing.
2580 """
2581 if not self.autofilter_area:
2582 warn("Must call autofilter() before filter_column()")
2583 return
2585 # Check for a column reference in A1 notation and substitute.
2586 try:
2587 int(col)
2588 except ValueError:
2589 # Convert col ref to a cell ref and then to a col number.
2590 col_letter = col
2591 _, col = xl_cell_to_rowcol(col + "1")
2593 if col >= self.xls_colmax:
2594 warn(f"Invalid column '{col_letter}'")
2595 return
2597 col_first, col_last = self.filter_range
2599 # Reject column if it is outside filter range.
2600 if col < col_first or col > col_last:
2601 warn(
2602 f"Column '{col}' outside autofilter() column "
2603 f"range ({col_first}, {col_last})"
2604 )
2605 return
2607 tokens = self._extract_filter_tokens(criteria)
2609 if len(tokens) not in (3, 7):
2610 warn(f"Incorrect number of tokens in criteria '{criteria}'")
2612 tokens = self._parse_filter_expression(criteria, tokens)
2614 # Excel handles single or double custom filters as default filters.
2615 # We need to check for them and handle them accordingly.
2616 if len(tokens) == 2 and tokens[0] == 2:
2617 # Single equality.
2618 self.filter_column_list(col, [tokens[1]])
2619 elif len(tokens) == 5 and tokens[0] == 2 and tokens[2] == 1 and tokens[3] == 2:
2620 # Double equality with "or" operator.
2621 self.filter_column_list(col, [tokens[1], tokens[4]])
2622 else:
2623 # Non default custom filter.
2624 self.filter_cols[col] = tokens
2625 self.filter_type[col] = 0
2627 self.filter_on = 1
2629 def filter_column_list(self, col: int, filters: List[str]) -> None:
2630 """
2631 Set the column filter criteria in Excel 2007 list style.
2633 Args:
2634 col: Filter column (zero-indexed).
2635 filters: List of filter criteria to match.
2637 Returns:
2638 Nothing.
2640 """
2641 if not self.autofilter_area:
2642 warn("Must call autofilter() before filter_column()")
2643 return
2645 # Check for a column reference in A1 notation and substitute.
2646 try:
2647 int(col)
2648 except ValueError:
2649 # Convert col ref to a cell ref and then to a col number.
2650 col_letter = col
2651 _, col = xl_cell_to_rowcol(col + "1")
2653 if col >= self.xls_colmax:
2654 warn(f"Invalid column '{col_letter}'")
2655 return
2657 col_first, col_last = self.filter_range
2659 # Reject column if it is outside filter range.
2660 if col < col_first or col > col_last:
2661 warn(
2662 f"Column '{col}' outside autofilter() column range "
2663 f"({col_first},{col_last})"
2664 )
2665 return
2667 self.filter_cols[col] = filters
2668 self.filter_type[col] = 1
2669 self.filter_on = 1
2671 @convert_range_args
2672 def data_validation(
2673 self,
2674 first_row: int,
2675 first_col: int,
2676 last_row: int,
2677 last_col: int,
2678 options: Optional[Dict[str, Any]] = None,
2679 ) -> Literal[0, -1, -2]:
2680 """
2681 Add a data validation to a worksheet.
2683 Args:
2684 first_row: The first row of the cell range. (zero indexed).
2685 first_col: The first column of the cell range.
2686 last_row: The last row of the cell range. (zero indexed).
2687 last_col: The last column of the cell range.
2688 options: Data validation options.
2690 Returns:
2691 0: Success.
2692 -1: Row or column is out of worksheet bounds.
2693 -2: Incorrect parameter or option.
2694 """
2695 # Check that row and col are valid without storing the values.
2696 if self._check_dimensions(first_row, first_col, True, True):
2697 return -1
2698 if self._check_dimensions(last_row, last_col, True, True):
2699 return -1
2701 if options is None:
2702 options = {}
2703 else:
2704 # Copy the user defined options so they aren't modified.
2705 options = options.copy()
2707 # Valid input parameters.
2708 valid_parameters = {
2709 "validate",
2710 "criteria",
2711 "value",
2712 "source",
2713 "minimum",
2714 "maximum",
2715 "ignore_blank",
2716 "dropdown",
2717 "show_input",
2718 "input_title",
2719 "input_message",
2720 "show_error",
2721 "error_title",
2722 "error_message",
2723 "error_type",
2724 "other_cells",
2725 "multi_range",
2726 }
2728 # Check for valid input parameters.
2729 for param_key in options.keys():
2730 if param_key not in valid_parameters:
2731 warn(f"Unknown parameter '{param_key}' in data_validation()")
2732 return -2
2734 # Map alternative parameter names 'source' or 'minimum' to 'value'.
2735 if "source" in options:
2736 options["value"] = options["source"]
2737 if "minimum" in options:
2738 options["value"] = options["minimum"]
2740 # 'validate' is a required parameter.
2741 if "validate" not in options:
2742 warn("Parameter 'validate' is required in data_validation()")
2743 return -2
2745 # List of valid validation types.
2746 valid_types = {
2747 "any": "none",
2748 "any value": "none",
2749 "whole number": "whole",
2750 "whole": "whole",
2751 "integer": "whole",
2752 "decimal": "decimal",
2753 "list": "list",
2754 "date": "date",
2755 "time": "time",
2756 "text length": "textLength",
2757 "length": "textLength",
2758 "custom": "custom",
2759 }
2761 # Check for valid validation types.
2762 if options["validate"] not in valid_types:
2763 warn(
2764 f"Unknown validation type '{options['validate']}' for parameter "
2765 f"'validate' in data_validation()"
2766 )
2767 return -2
2769 options["validate"] = valid_types[options["validate"]]
2771 # No action is required for validation type 'any' if there are no
2772 # input messages to display.
2773 if (
2774 options["validate"] == "none"
2775 and options.get("input_title") is None
2776 and options.get("input_message") is None
2777 ):
2778 return -2
2780 # The any, list and custom validations don't have a criteria so we use
2781 # a default of 'between'.
2782 if (
2783 options["validate"] == "none"
2784 or options["validate"] == "list"
2785 or options["validate"] == "custom"
2786 ):
2787 options["criteria"] = "between"
2788 options["maximum"] = None
2790 # 'criteria' is a required parameter.
2791 if "criteria" not in options:
2792 warn("Parameter 'criteria' is required in data_validation()")
2793 return -2
2795 # Valid criteria types.
2796 criteria_types = {
2797 "between": "between",
2798 "not between": "notBetween",
2799 "equal to": "equal",
2800 "=": "equal",
2801 "==": "equal",
2802 "not equal to": "notEqual",
2803 "!=": "notEqual",
2804 "<>": "notEqual",
2805 "greater than": "greaterThan",
2806 ">": "greaterThan",
2807 "less than": "lessThan",
2808 "<": "lessThan",
2809 "greater than or equal to": "greaterThanOrEqual",
2810 ">=": "greaterThanOrEqual",
2811 "less than or equal to": "lessThanOrEqual",
2812 "<=": "lessThanOrEqual",
2813 }
2815 # Check for valid criteria types.
2816 if options["criteria"] not in criteria_types:
2817 warn(
2818 f"Unknown criteria type '{options['criteria']}' for parameter "
2819 f"'criteria' in data_validation()"
2820 )
2821 return -2
2823 options["criteria"] = criteria_types[options["criteria"]]
2825 # 'Between' and 'Not between' criteria require 2 values.
2826 if options["criteria"] == "between" or options["criteria"] == "notBetween":
2827 if "maximum" not in options:
2828 warn(
2829 "Parameter 'maximum' is required in data_validation() "
2830 "when using 'between' or 'not between' criteria"
2831 )
2832 return -2
2833 else:
2834 options["maximum"] = None
2836 # Valid error dialog types.
2837 error_types = {
2838 "stop": 0,
2839 "warning": 1,
2840 "information": 2,
2841 }
2843 # Check for valid error dialog types.
2844 if "error_type" not in options:
2845 options["error_type"] = 0
2846 elif options["error_type"] not in error_types:
2847 warn(
2848 f"Unknown criteria type '{options['error_type']}' "
2849 f"for parameter 'error_type'."
2850 )
2851 return -2
2852 else:
2853 options["error_type"] = error_types[options["error_type"]]
2855 # Convert date/times value if required.
2856 if (
2857 options["validate"] in ("date", "time")
2858 and options["value"]
2859 and _supported_datetime(options["value"])
2860 ):
2861 date_time = self._convert_date_time(options["value"])
2862 # Format date number to the same precision as Excel.
2863 options["value"] = f"{date_time:.16g}"
2865 if options["maximum"] and _supported_datetime(options["maximum"]):
2866 date_time = self._convert_date_time(options["maximum"])
2867 options["maximum"] = f"{date_time:.16g}"
2869 # Check that the input title doesn't exceed the maximum length.
2870 if options.get("input_title") and len(options["input_title"]) > 32:
2871 warn(
2872 f"Length of input title '{options['input_title']}' "
2873 f"exceeds Excel's limit of 32"
2874 )
2875 return -2
2877 # Check that the error title doesn't exceed the maximum length.
2878 if options.get("error_title") and len(options["error_title"]) > 32:
2879 warn(
2880 f"Length of error title '{options['error_title']}' "
2881 f"exceeds Excel's limit of 32"
2882 )
2883 return -2
2885 # Check that the input message doesn't exceed the maximum length.
2886 if options.get("input_message") and len(options["input_message"]) > 255:
2887 warn(
2888 f"Length of input message '{options['input_message']}' "
2889 f"exceeds Excel's limit of 255"
2890 )
2891 return -2
2893 # Check that the error message doesn't exceed the maximum length.
2894 if options.get("error_message") and len(options["error_message"]) > 255:
2895 warn(
2896 f"Length of error message '{options['error_message']}' "
2897 f"exceeds Excel's limit of 255"
2898 )
2899 return -2
2901 # Check that the input list doesn't exceed the maximum length.
2902 if options["validate"] == "list" and isinstance(options["value"], list):
2903 formula = self._csv_join(*options["value"])
2904 if len(formula) > 255:
2905 warn(
2906 f"Length of list items '{formula}' exceeds Excel's limit of "
2907 f"255, use a formula range instead"
2908 )
2909 return -2
2911 # Set some defaults if they haven't been defined by the user.
2912 if "ignore_blank" not in options:
2913 options["ignore_blank"] = 1
2914 if "dropdown" not in options:
2915 options["dropdown"] = 1
2916 if "show_input" not in options:
2917 options["show_input"] = 1
2918 if "show_error" not in options:
2919 options["show_error"] = 1
2921 # These are the cells to which the validation is applied.
2922 options["cells"] = [[first_row, first_col, last_row, last_col]]
2924 # A (for now) undocumented parameter to pass additional cell ranges.
2925 if "other_cells" in options:
2926 options["cells"].extend(options["other_cells"])
2928 # Override with user defined multiple range if provided.
2929 if "multi_range" in options:
2930 options["multi_range"] = options["multi_range"].replace("$", "")
2932 # Store the validation information until we close the worksheet.
2933 self.validations.append(options)
2935 return 0
2937 @convert_range_args
2938 def conditional_format(
2939 self,
2940 first_row: int,
2941 first_col: int,
2942 last_row: int,
2943 last_col: int,
2944 options: Optional[Dict[str, Any]] = None,
2945 ) -> Literal[0, -1, -2]:
2946 """
2947 Add a conditional format to a worksheet.
2949 Args:
2950 first_row: The first row of the cell range. (zero indexed).
2951 first_col: The first column of the cell range.
2952 last_row: The last row of the cell range. (zero indexed).
2953 last_col: The last column of the cell range.
2954 options: Conditional format options.
2956 Returns:
2957 0: Success.
2958 -1: Row or column is out of worksheet bounds.
2959 -2: Incorrect parameter or option.
2960 """
2961 # Check that row and col are valid without storing the values.
2962 if self._check_dimensions(first_row, first_col, True, True):
2963 return -1
2964 if self._check_dimensions(last_row, last_col, True, True):
2965 return -1
2967 if options is None:
2968 options = {}
2969 else:
2970 # Copy the user defined options so they aren't modified.
2971 options = options.copy()
2973 # Valid input parameters.
2974 valid_parameter = {
2975 "type",
2976 "format",
2977 "criteria",
2978 "value",
2979 "minimum",
2980 "maximum",
2981 "stop_if_true",
2982 "min_type",
2983 "mid_type",
2984 "max_type",
2985 "min_value",
2986 "mid_value",
2987 "max_value",
2988 "min_color",
2989 "mid_color",
2990 "max_color",
2991 "min_length",
2992 "max_length",
2993 "multi_range",
2994 "bar_color",
2995 "bar_negative_color",
2996 "bar_negative_color_same",
2997 "bar_solid",
2998 "bar_border_color",
2999 "bar_negative_border_color",
3000 "bar_negative_border_color_same",
3001 "bar_no_border",
3002 "bar_direction",
3003 "bar_axis_position",
3004 "bar_axis_color",
3005 "bar_only",
3006 "data_bar_2010",
3007 "icon_style",
3008 "reverse_icons",
3009 "icons_only",
3010 "icons",
3011 }
3013 # Check for valid input parameters.
3014 for param_key in options.keys():
3015 if param_key not in valid_parameter:
3016 warn(f"Unknown parameter '{param_key}' in conditional_format()")
3017 return -2
3019 # 'type' is a required parameter.
3020 if "type" not in options:
3021 warn("Parameter 'type' is required in conditional_format()")
3022 return -2
3024 # Valid types.
3025 valid_type = {
3026 "cell": "cellIs",
3027 "date": "date",
3028 "time": "time",
3029 "average": "aboveAverage",
3030 "duplicate": "duplicateValues",
3031 "unique": "uniqueValues",
3032 "top": "top10",
3033 "bottom": "top10",
3034 "text": "text",
3035 "time_period": "timePeriod",
3036 "blanks": "containsBlanks",
3037 "no_blanks": "notContainsBlanks",
3038 "errors": "containsErrors",
3039 "no_errors": "notContainsErrors",
3040 "2_color_scale": "2_color_scale",
3041 "3_color_scale": "3_color_scale",
3042 "data_bar": "dataBar",
3043 "formula": "expression",
3044 "icon_set": "iconSet",
3045 }
3047 # Check for valid types.
3048 if options["type"] not in valid_type:
3049 warn(
3050 f"Unknown value '{options['type']}' for parameter 'type' "
3051 f"in conditional_format()"
3052 )
3053 return -2
3055 if options["type"] == "bottom":
3056 options["direction"] = "bottom"
3057 options["type"] = valid_type[options["type"]]
3059 # Valid criteria types.
3060 criteria_type = {
3061 "between": "between",
3062 "not between": "notBetween",
3063 "equal to": "equal",
3064 "=": "equal",
3065 "==": "equal",
3066 "not equal to": "notEqual",
3067 "!=": "notEqual",
3068 "<>": "notEqual",
3069 "greater than": "greaterThan",
3070 ">": "greaterThan",
3071 "less than": "lessThan",
3072 "<": "lessThan",
3073 "greater than or equal to": "greaterThanOrEqual",
3074 ">=": "greaterThanOrEqual",
3075 "less than or equal to": "lessThanOrEqual",
3076 "<=": "lessThanOrEqual",
3077 "containing": "containsText",
3078 "not containing": "notContains",
3079 "begins with": "beginsWith",
3080 "ends with": "endsWith",
3081 "yesterday": "yesterday",
3082 "today": "today",
3083 "last 7 days": "last7Days",
3084 "last week": "lastWeek",
3085 "this week": "thisWeek",
3086 "next week": "nextWeek",
3087 "last month": "lastMonth",
3088 "this month": "thisMonth",
3089 "next month": "nextMonth",
3090 # For legacy, but incorrect, support.
3091 "continue week": "nextWeek",
3092 "continue month": "nextMonth",
3093 }
3095 # Check for valid criteria types.
3096 if "criteria" in options and options["criteria"] in criteria_type:
3097 options["criteria"] = criteria_type[options["criteria"]]
3099 # Convert boolean values if required.
3100 if "value" in options and isinstance(options["value"], bool):
3101 options["value"] = str(options["value"]).upper()
3103 # Convert date/times value if required.
3104 if options["type"] in ("date", "time"):
3105 options["type"] = "cellIs"
3107 if "value" in options:
3108 if not _supported_datetime(options["value"]):
3109 warn("Conditional format 'value' must be a datetime object.")
3110 return -2
3112 date_time = self._convert_date_time(options["value"])
3113 # Format date number to the same precision as Excel.
3114 options["value"] = f"{date_time:.16g}"
3116 if "minimum" in options:
3117 if not _supported_datetime(options["minimum"]):
3118 warn("Conditional format 'minimum' must be a datetime object.")
3119 return -2
3121 date_time = self._convert_date_time(options["minimum"])
3122 options["minimum"] = f"{date_time:.16g}"
3124 if "maximum" in options:
3125 if not _supported_datetime(options["maximum"]):
3126 warn("Conditional format 'maximum' must be a datetime object.")
3127 return -2
3129 date_time = self._convert_date_time(options["maximum"])
3130 options["maximum"] = f"{date_time:.16g}"
3132 # Valid icon styles.
3133 valid_icons = {
3134 "3_arrows": "3Arrows", # 1
3135 "3_flags": "3Flags", # 2
3136 "3_traffic_lights_rimmed": "3TrafficLights2", # 3
3137 "3_symbols_circled": "3Symbols", # 4
3138 "4_arrows": "4Arrows", # 5
3139 "4_red_to_black": "4RedToBlack", # 6
3140 "4_traffic_lights": "4TrafficLights", # 7
3141 "5_arrows_gray": "5ArrowsGray", # 8
3142 "5_quarters": "5Quarters", # 9
3143 "3_arrows_gray": "3ArrowsGray", # 10
3144 "3_traffic_lights": "3TrafficLights", # 11
3145 "3_signs": "3Signs", # 12
3146 "3_symbols": "3Symbols2", # 13
3147 "4_arrows_gray": "4ArrowsGray", # 14
3148 "4_ratings": "4Rating", # 15
3149 "5_arrows": "5Arrows", # 16
3150 "5_ratings": "5Rating",
3151 } # 17
3153 # Set the icon set properties.
3154 if options["type"] == "iconSet":
3155 # An icon_set must have an icon style.
3156 if not options.get("icon_style"):
3157 warn(
3158 "The 'icon_style' parameter must be specified when "
3159 "'type' == 'icon_set' in conditional_format()."
3160 )
3161 return -3
3163 # Check for valid icon styles.
3164 if options["icon_style"] not in valid_icons:
3165 warn(
3166 f"Unknown icon_style '{options['icon_style']}' "
3167 f"in conditional_format()."
3168 )
3169 return -2
3171 options["icon_style"] = valid_icons[options["icon_style"]]
3173 # Set the number of icons for the icon style.
3174 options["total_icons"] = 3
3175 if options["icon_style"].startswith("4"):
3176 options["total_icons"] = 4
3177 elif options["icon_style"].startswith("5"):
3178 options["total_icons"] = 5
3180 options["icons"] = self._set_icon_props(
3181 options.get("total_icons"), options.get("icons")
3182 )
3184 # Swap last row/col for first row/col as necessary
3185 if first_row > last_row:
3186 first_row, last_row = last_row, first_row
3188 if first_col > last_col:
3189 first_col, last_col = last_col, first_col
3191 # Set the formatting range.
3192 cell_range = xl_range(first_row, first_col, last_row, last_col)
3193 start_cell = xl_rowcol_to_cell(first_row, first_col)
3195 # Override with user defined multiple range if provided.
3196 if "multi_range" in options:
3197 cell_range = options["multi_range"]
3198 cell_range = cell_range.replace("$", "")
3200 # Get the dxf format index.
3201 if "format" in options and options["format"]:
3202 options["format"] = options["format"]._get_dxf_index()
3204 # Set the priority based on the order of adding.
3205 options["priority"] = self.dxf_priority
3206 self.dxf_priority += 1
3208 # Check for 2010 style data_bar parameters.
3209 # pylint: disable=too-many-boolean-expressions
3210 if (
3211 self.use_data_bars_2010
3212 or options.get("data_bar_2010")
3213 or options.get("bar_solid")
3214 or options.get("bar_border_color")
3215 or options.get("bar_negative_color")
3216 or options.get("bar_negative_color_same")
3217 or options.get("bar_negative_border_color")
3218 or options.get("bar_negative_border_color_same")
3219 or options.get("bar_no_border")
3220 or options.get("bar_axis_position")
3221 or options.get("bar_axis_color")
3222 or options.get("bar_direction")
3223 ):
3224 options["is_data_bar_2010"] = True
3226 # Special handling of text criteria.
3227 if options["type"] == "text":
3228 value = options["value"]
3229 length = len(value)
3230 criteria = options["criteria"]
3232 if options["criteria"] == "containsText":
3233 options["type"] = "containsText"
3234 options["formula"] = f'NOT(ISERROR(SEARCH("{value}",{start_cell})))'
3235 elif options["criteria"] == "notContains":
3236 options["type"] = "notContainsText"
3237 options["formula"] = f'ISERROR(SEARCH("{value}",{start_cell}))'
3238 elif options["criteria"] == "beginsWith":
3239 options["type"] = "beginsWith"
3240 options["formula"] = f'LEFT({start_cell},{length})="{value}"'
3241 elif options["criteria"] == "endsWith":
3242 options["type"] = "endsWith"
3243 options["formula"] = f'RIGHT({start_cell},{length})="{value}"'
3244 else:
3245 warn(f"Invalid text criteria '{criteria}' in conditional_format()")
3247 # Special handling of time time_period criteria.
3248 if options["type"] == "timePeriod":
3249 if options["criteria"] == "yesterday":
3250 options["formula"] = f"FLOOR({start_cell},1)=TODAY()-1"
3252 elif options["criteria"] == "today":
3253 options["formula"] = f"FLOOR({start_cell},1)=TODAY()"
3255 elif options["criteria"] == "tomorrow":
3256 options["formula"] = f"FLOOR({start_cell},1)=TODAY()+1"
3258 # fmt: off
3259 elif options["criteria"] == "last7Days":
3260 options["formula"] = (
3261 f"AND(TODAY()-FLOOR({start_cell},1)<=6,"
3262 f"FLOOR({start_cell},1)<=TODAY())"
3263 )
3264 # fmt: on
3266 elif options["criteria"] == "lastWeek":
3267 options["formula"] = (
3268 f"AND(TODAY()-ROUNDDOWN({start_cell},0)>=(WEEKDAY(TODAY())),"
3269 f"TODAY()-ROUNDDOWN({start_cell},0)<(WEEKDAY(TODAY())+7))"
3270 )
3272 elif options["criteria"] == "thisWeek":
3273 options["formula"] = (
3274 f"AND(TODAY()-ROUNDDOWN({start_cell},0)<=WEEKDAY(TODAY())-1,"
3275 f"ROUNDDOWN({start_cell},0)-TODAY()<=7-WEEKDAY(TODAY()))"
3276 )
3278 elif options["criteria"] == "nextWeek":
3279 options["formula"] = (
3280 f"AND(ROUNDDOWN({start_cell},0)-TODAY()>(7-WEEKDAY(TODAY())),"
3281 f"ROUNDDOWN({start_cell},0)-TODAY()<(15-WEEKDAY(TODAY())))"
3282 )
3284 elif options["criteria"] == "lastMonth":
3285 options["formula"] = (
3286 f"AND(MONTH({start_cell})=MONTH(TODAY())-1,"
3287 f"OR(YEAR({start_cell})=YEAR("
3288 f"TODAY()),AND(MONTH({start_cell})=1,YEAR(A1)=YEAR(TODAY())-1)))"
3289 )
3291 # fmt: off
3292 elif options["criteria"] == "thisMonth":
3293 options["formula"] = (
3294 f"AND(MONTH({start_cell})=MONTH(TODAY()),"
3295 f"YEAR({start_cell})=YEAR(TODAY()))"
3296 )
3297 # fmt: on
3299 elif options["criteria"] == "nextMonth":
3300 options["formula"] = (
3301 f"AND(MONTH({start_cell})=MONTH(TODAY())+1,"
3302 f"OR(YEAR({start_cell})=YEAR("
3303 f"TODAY()),AND(MONTH({start_cell})=12,"
3304 f"YEAR({start_cell})=YEAR(TODAY())+1)))"
3305 )
3307 else:
3308 warn(
3309 f"Invalid time_period criteria '{options['criteria']}' "
3310 f"in conditional_format()"
3311 )
3313 # Special handling of blanks/error types.
3314 if options["type"] == "containsBlanks":
3315 options["formula"] = f"LEN(TRIM({start_cell}))=0"
3317 if options["type"] == "notContainsBlanks":
3318 options["formula"] = f"LEN(TRIM({start_cell}))>0"
3320 if options["type"] == "containsErrors":
3321 options["formula"] = f"ISERROR({start_cell})"
3323 if options["type"] == "notContainsErrors":
3324 options["formula"] = f"NOT(ISERROR({start_cell}))"
3326 # Special handling for 2 color scale.
3327 if options["type"] == "2_color_scale":
3328 options["type"] = "colorScale"
3330 # Color scales don't use any additional formatting.
3331 options["format"] = None
3333 # Turn off 3 color parameters.
3334 options["mid_type"] = None
3335 options["mid_color"] = None
3337 options.setdefault("min_type", "min")
3338 options.setdefault("max_type", "max")
3339 options.setdefault("min_value", 0)
3340 options.setdefault("max_value", 0)
3341 options.setdefault("min_color", Color("#FF7128"))
3342 options.setdefault("max_color", Color("#FFEF9C"))
3344 options["min_color"] = Color._from_value(options["min_color"])
3345 options["max_color"] = Color._from_value(options["max_color"])
3347 # Special handling for 3 color scale.
3348 if options["type"] == "3_color_scale":
3349 options["type"] = "colorScale"
3351 # Color scales don't use any additional formatting.
3352 options["format"] = None
3354 options.setdefault("min_type", "min")
3355 options.setdefault("mid_type", "percentile")
3356 options.setdefault("max_type", "max")
3357 options.setdefault("min_value", 0)
3358 options.setdefault("max_value", 0)
3359 options.setdefault("min_color", Color("#F8696B"))
3360 options.setdefault("mid_color", Color("#FFEB84"))
3361 options.setdefault("max_color", Color("#63BE7B"))
3363 options["min_color"] = Color._from_value(options["min_color"])
3364 options["mid_color"] = Color._from_value(options["mid_color"])
3365 options["max_color"] = Color._from_value(options["max_color"])
3367 # Set a default mid value.
3368 if "mid_value" not in options:
3369 options["mid_value"] = 50
3371 # Special handling for data bar.
3372 if options["type"] == "dataBar":
3373 # Color scales don't use any additional formatting.
3374 options["format"] = None
3376 if not options.get("min_type"):
3377 options["min_type"] = "min"
3378 options["x14_min_type"] = "autoMin"
3379 else:
3380 options["x14_min_type"] = options["min_type"]
3382 if not options.get("max_type"):
3383 options["max_type"] = "max"
3384 options["x14_max_type"] = "autoMax"
3385 else:
3386 options["x14_max_type"] = options["max_type"]
3388 options.setdefault("min_value", 0)
3389 options.setdefault("max_value", 0)
3390 options.setdefault("bar_color", Color("#638EC6"))
3391 options.setdefault("bar_border_color", options["bar_color"])
3392 options.setdefault("bar_only", False)
3393 options.setdefault("bar_no_border", False)
3394 options.setdefault("bar_solid", False)
3395 options.setdefault("bar_direction", "")
3396 options.setdefault("bar_negative_color", Color("#FF0000"))
3397 options.setdefault("bar_negative_border_color", Color("#FF0000"))
3398 options.setdefault("bar_negative_color_same", False)
3399 options.setdefault("bar_negative_border_color_same", False)
3400 options.setdefault("bar_axis_position", "")
3401 options.setdefault("bar_axis_color", Color("#000000"))
3403 options["bar_color"] = Color._from_value(options["bar_color"])
3404 options["bar_border_color"] = Color._from_value(options["bar_border_color"])
3405 options["bar_axis_color"] = Color._from_value(options["bar_axis_color"])
3406 options["bar_negative_color"] = Color._from_value(
3407 options["bar_negative_color"]
3408 )
3409 options["bar_negative_border_color"] = Color._from_value(
3410 options["bar_negative_border_color"]
3411 )
3413 # Adjust for 2010 style data_bar parameters.
3414 if options.get("is_data_bar_2010"):
3415 self.excel_version = 2010
3417 if options["min_type"] == "min" and options["min_value"] == 0:
3418 options["min_value"] = None
3420 if options["max_type"] == "max" and options["max_value"] == 0:
3421 options["max_value"] = None
3423 options["range"] = cell_range
3425 # Strip the leading = from formulas.
3426 try:
3427 options["min_value"] = options["min_value"].lstrip("=")
3428 except (KeyError, AttributeError):
3429 pass
3430 try:
3431 options["mid_value"] = options["mid_value"].lstrip("=")
3432 except (KeyError, AttributeError):
3433 pass
3434 try:
3435 options["max_value"] = options["max_value"].lstrip("=")
3436 except (KeyError, AttributeError):
3437 pass
3439 # Store the conditional format until we close the worksheet.
3440 if cell_range in self.cond_formats:
3441 self.cond_formats[cell_range].append(options)
3442 else:
3443 self.cond_formats[cell_range] = [options]
3445 return 0
3447 @convert_range_args
3448 def add_table(
3449 self,
3450 first_row: int,
3451 first_col: int,
3452 last_row: int,
3453 last_col: int,
3454 options: Optional[Dict[str, Any]] = None,
3455 ) -> Literal[0, -1, -2, -3]:
3456 """
3457 Add an Excel table to a worksheet.
3459 Args:
3460 first_row: The first row of the cell range. (zero indexed).
3461 first_col: The first column of the cell range.
3462 last_row: The last row of the cell range. (zero indexed).
3463 last_col: The last column of the cell range.
3464 options: Table format options. (Optional)
3466 Returns:
3467 0: Success.
3468 -1: Row or column is out of worksheet bounds.
3469 -2: Incorrect parameter or option.
3470 -3: Not supported in constant_memory mode.
3471 """
3472 table = {}
3473 col_formats = {}
3475 if options is None:
3476 options = {}
3477 else:
3478 # Copy the user defined options so they aren't modified.
3479 options = options.copy()
3481 if self.constant_memory:
3482 warn("add_table() isn't supported in 'constant_memory' mode")
3483 return -3
3485 # Check that row and col are valid without storing the values.
3486 if self._check_dimensions(first_row, first_col, True, True):
3487 return -1
3488 if self._check_dimensions(last_row, last_col, True, True):
3489 return -1
3491 # Swap last row/col for first row/col as necessary.
3492 if first_row > last_row:
3493 first_row, last_row = (last_row, first_row)
3494 if first_col > last_col:
3495 first_col, last_col = (last_col, first_col)
3497 # Check if the table range overlaps a previous merged or table range.
3498 # This is a critical file corruption error in Excel.
3499 cell_range = xl_range(first_row, first_col, last_row, last_col)
3500 for row in range(first_row, last_row + 1):
3501 for col in range(first_col, last_col + 1):
3502 if self.table_cells.get((row, col)):
3503 previous_range = self.table_cells.get((row, col))
3504 raise OverlappingRange(
3505 f"Table range '{cell_range}' overlaps previous "
3506 f"table range '{previous_range}'."
3507 )
3509 if self.merged_cells.get((row, col)):
3510 previous_range = self.merged_cells.get((row, col))
3511 raise OverlappingRange(
3512 f"Table range '{cell_range}' overlaps previous "
3513 f"merge range '{previous_range}'."
3514 )
3516 self.table_cells[(row, col)] = cell_range
3518 # Valid input parameters.
3519 valid_parameter = {
3520 "autofilter",
3521 "banded_columns",
3522 "banded_rows",
3523 "columns",
3524 "data",
3525 "first_column",
3526 "header_row",
3527 "last_column",
3528 "name",
3529 "style",
3530 "total_row",
3531 "description",
3532 "title",
3533 }
3535 # Check for valid input parameters.
3536 for param_key in options.keys():
3537 if param_key not in valid_parameter:
3538 warn(f"Unknown parameter '{param_key}' in add_table()")
3539 return -2
3541 # Turn on Excel's defaults.
3542 options["banded_rows"] = options.get("banded_rows", True)
3543 options["header_row"] = options.get("header_row", True)
3544 options["autofilter"] = options.get("autofilter", True)
3546 # Check that there are enough rows.
3547 num_rows = last_row - first_row
3548 if options["header_row"]:
3549 num_rows -= 1
3551 if num_rows < 0:
3552 warn("Must have at least one data row in in add_table()")
3553 return -2
3555 # Set the table options.
3556 table["show_first_col"] = options.get("first_column", False)
3557 table["show_last_col"] = options.get("last_column", False)
3558 table["show_row_stripes"] = options.get("banded_rows", False)
3559 table["show_col_stripes"] = options.get("banded_columns", False)
3560 table["header_row_count"] = options.get("header_row", 0)
3561 table["totals_row_shown"] = options.get("total_row", False)
3562 table["description"] = options.get("description")
3563 table["title"] = options.get("title")
3565 # Set the table name.
3566 if "name" in options:
3567 name = options["name"]
3568 table["name"] = name
3570 if " " in name:
3571 warn(f"Name '{name}' in add_table() cannot contain spaces")
3572 return -2
3574 # Warn if the name contains invalid chars as defined by Excel.
3575 if not re.match(r"^[\w\\][\w\\.]*$", name, re.UNICODE) or re.match(
3576 r"^\d", name
3577 ):
3578 warn(f"Invalid Excel characters in add_table(): '{name}'")
3579 return -2
3581 # Warn if the name looks like a cell name.
3582 if re.match(r"^[a-zA-Z][a-zA-Z]?[a-dA-D]?\d+$", name):
3583 warn(f"Name looks like a cell name in add_table(): '{name}'")
3584 return -2
3586 # Warn if the name looks like a R1C1 cell reference.
3587 if re.match(r"^[rcRC]$", name) or re.match(r"^[rcRC]\d+[rcRC]\d+$", name):
3588 warn(f"Invalid name '{name}' like a RC cell ref in add_table()")
3589 return -2
3591 # Set the table style.
3592 if "style" in options:
3593 table["style"] = options["style"]
3595 if table["style"] is None:
3596 table["style"] = ""
3598 # Remove whitespace from style name.
3599 table["style"] = table["style"].replace(" ", "")
3600 else:
3601 table["style"] = "TableStyleMedium9"
3603 # Set the data range rows (without the header and footer).
3604 first_data_row = first_row
3605 last_data_row = last_row
3607 if options.get("header_row"):
3608 first_data_row += 1
3610 if options.get("total_row"):
3611 last_data_row -= 1
3613 # Set the table and autofilter ranges.
3614 table["range"] = xl_range(first_row, first_col, last_row, last_col)
3616 table["a_range"] = xl_range(first_row, first_col, last_data_row, last_col)
3618 # If the header row if off the default is to turn autofilter off.
3619 if not options["header_row"]:
3620 options["autofilter"] = 0
3622 # Set the autofilter range.
3623 if options["autofilter"]:
3624 table["autofilter"] = table["a_range"]
3626 # Add the table columns.
3627 col_id = 1
3628 table["columns"] = []
3629 seen_names = {}
3631 for col_num in range(first_col, last_col + 1):
3632 # Set up the default column data.
3633 col_data = {
3634 "id": col_id,
3635 "name": "Column" + str(col_id),
3636 "total_string": "",
3637 "total_function": "",
3638 "custom_total": "",
3639 "total_value": 0,
3640 "formula": "",
3641 "format": None,
3642 "name_format": None,
3643 }
3645 # Overwrite the defaults with any user defined values.
3646 if "columns" in options:
3647 # Check if there are user defined values for this column.
3648 if col_id <= len(options["columns"]):
3649 user_data = options["columns"][col_id - 1]
3650 else:
3651 user_data = None
3653 if user_data:
3654 # Get the column format.
3655 xformat = user_data.get("format", None)
3657 # Map user defined values to internal values.
3658 if user_data.get("header"):
3659 col_data["name"] = user_data["header"]
3661 # Excel requires unique case insensitive header names.
3662 header_name = col_data["name"]
3663 name = header_name.lower()
3664 if name in seen_names:
3665 warn(f"Duplicate header name in add_table(): '{name}'")
3666 return -2
3668 seen_names[name] = True
3670 col_data["name_format"] = user_data.get("header_format")
3672 # Handle the column formula.
3673 if "formula" in user_data and user_data["formula"]:
3674 formula = user_data["formula"]
3676 # Remove the formula '=' sign if it exists.
3677 if formula.startswith("="):
3678 formula = formula.lstrip("=")
3680 # Convert Excel 2010 "@" ref to 2007 "#This Row".
3681 formula = self._prepare_table_formula(formula)
3683 # Escape any future functions.
3684 formula = self._prepare_formula(formula, True)
3686 col_data["formula"] = formula
3687 # We write the formulas below after the table data.
3689 # Handle the function for the total row.
3690 if user_data.get("total_function"):
3691 function = user_data["total_function"]
3692 if function == "count_nums":
3693 function = "countNums"
3694 if function == "std_dev":
3695 function = "stdDev"
3697 subtotals = set(
3698 [
3699 "average",
3700 "countNums",
3701 "count",
3702 "max",
3703 "min",
3704 "stdDev",
3705 "sum",
3706 "var",
3707 ]
3708 )
3710 if function in subtotals:
3711 formula = self._table_function_to_formula(
3712 function, col_data["name"]
3713 )
3714 else:
3715 formula = self._prepare_formula(function, True)
3716 col_data["custom_total"] = formula
3717 function = "custom"
3719 col_data["total_function"] = function
3721 value = user_data.get("total_value", 0)
3723 self._write_formula(last_row, col_num, formula, xformat, value)
3725 elif user_data.get("total_string"):
3726 # Total label only (not a function).
3727 total_string = user_data["total_string"]
3728 col_data["total_string"] = total_string
3730 self._write_string(
3731 last_row, col_num, total_string, user_data.get("format")
3732 )
3734 # Get the dxf format index.
3735 if xformat is not None:
3736 col_data["format"] = xformat._get_dxf_index()
3738 # Store the column format for writing the cell data.
3739 # It doesn't matter if it is undefined.
3740 col_formats[col_id - 1] = xformat
3742 # Store the column data.
3743 table["columns"].append(col_data)
3745 # Write the column headers to the worksheet.
3746 if options["header_row"]:
3747 self._write_string(
3748 first_row, col_num, col_data["name"], col_data["name_format"]
3749 )
3751 col_id += 1
3753 # Write the cell data if supplied.
3754 if "data" in options:
3755 data = options["data"]
3757 i = 0 # For indexing the row data.
3758 for row in range(first_data_row, last_data_row + 1):
3759 j = 0 # For indexing the col data.
3760 for col in range(first_col, last_col + 1):
3761 if i < len(data) and j < len(data[i]):
3762 token = data[i][j]
3763 if j in col_formats:
3764 self._write(row, col, token, col_formats[j])
3765 else:
3766 self._write(row, col, token, None)
3767 j += 1
3768 i += 1
3770 # Write any columns formulas after the user supplied table data to
3771 # overwrite it if required.
3772 for col_id, col_num in enumerate(range(first_col, last_col + 1)):
3773 column_data = table["columns"][col_id]
3774 if column_data and column_data["formula"]:
3775 formula_format = col_formats.get(col_id)
3776 formula = column_data["formula"]
3778 for row in range(first_data_row, last_data_row + 1):
3779 self._write_formula(row, col_num, formula, formula_format)
3781 # Store the table data.
3782 self.tables.append(table)
3784 # Store the filter cell positions for use in the autofit calculation.
3785 if options["autofilter"]:
3786 for col in range(first_col, last_col + 1):
3787 # Check that the table autofilter doesn't overlap a worksheet filter.
3788 if self.filter_cells.get((first_row, col)):
3789 filter_type, filter_range = self.filter_cells.get((first_row, col))
3790 if filter_type == "worksheet":
3791 raise OverlappingRange(
3792 f"Table autofilter range '{cell_range}' overlaps previous "
3793 f"Worksheet autofilter range '{filter_range}'."
3794 )
3796 self.filter_cells[(first_row, col)] = ("table", cell_range)
3798 return 0
3800 @convert_cell_args
3801 def add_sparkline(
3802 self, row: int, col: int, options: Optional[Dict[str, Any]] = None
3803 ) -> Literal[0, -1, -2]:
3804 """
3805 Add sparklines to the worksheet.
3807 Args:
3808 row: The cell row (zero indexed).
3809 col: The cell column (zero indexed).
3810 options: Sparkline formatting options.
3812 Returns:
3813 0: Success.
3814 -1: Row or column is out of worksheet bounds.
3815 -2: Incorrect parameter or option.
3817 """
3819 # Check that row and col are valid without storing the values.
3820 if self._check_dimensions(row, col, True, True):
3821 return -1
3823 sparkline = {"locations": [xl_rowcol_to_cell(row, col)]}
3825 if options is None:
3826 options = {}
3828 # Valid input parameters.
3829 valid_parameters = {
3830 "location",
3831 "range",
3832 "type",
3833 "high_point",
3834 "low_point",
3835 "negative_points",
3836 "first_point",
3837 "last_point",
3838 "markers",
3839 "style",
3840 "series_color",
3841 "negative_color",
3842 "markers_color",
3843 "first_color",
3844 "last_color",
3845 "high_color",
3846 "low_color",
3847 "max",
3848 "min",
3849 "axis",
3850 "reverse",
3851 "empty_cells",
3852 "show_hidden",
3853 "plot_hidden",
3854 "date_axis",
3855 "weight",
3856 }
3858 # Check for valid input parameters.
3859 for param_key in options.keys():
3860 if param_key not in valid_parameters:
3861 warn(f"Unknown parameter '{param_key}' in add_sparkline()")
3862 return -1
3864 # 'range' is a required parameter.
3865 if "range" not in options:
3866 warn("Parameter 'range' is required in add_sparkline()")
3867 return -2
3869 # Handle the sparkline type.
3870 spark_type = options.get("type", "line")
3872 if spark_type not in ("line", "column", "win_loss"):
3873 warn(
3874 "Parameter 'type' must be 'line', 'column' "
3875 "or 'win_loss' in add_sparkline()"
3876 )
3877 return -2
3879 if spark_type == "win_loss":
3880 spark_type = "stacked"
3881 sparkline["type"] = spark_type
3883 # We handle single location/range values or list of values.
3884 if "location" in options:
3885 if isinstance(options["location"], list):
3886 sparkline["locations"] = options["location"]
3887 else:
3888 sparkline["locations"] = [options["location"]]
3890 if isinstance(options["range"], list):
3891 sparkline["ranges"] = options["range"]
3892 else:
3893 sparkline["ranges"] = [options["range"]]
3895 range_count = len(sparkline["ranges"])
3896 location_count = len(sparkline["locations"])
3898 # The ranges and locations must match.
3899 if range_count != location_count:
3900 warn(
3901 "Must have the same number of location and range "
3902 "parameters in add_sparkline()"
3903 )
3904 return -2
3906 # Store the count.
3907 sparkline["count"] = len(sparkline["locations"])
3909 # Get the worksheet name for the range conversion below.
3910 sheetname = quote_sheetname(self.name)
3912 # Cleanup the input ranges.
3913 new_ranges = []
3914 for spark_range in sparkline["ranges"]:
3915 # Remove the absolute reference $ symbols.
3916 spark_range = spark_range.replace("$", "")
3918 # Remove the = from formula.
3919 spark_range = spark_range.lstrip("=")
3921 # Convert a simple range into a full Sheet1!A1:D1 range.
3922 if "!" not in spark_range:
3923 spark_range = sheetname + "!" + spark_range
3925 new_ranges.append(spark_range)
3927 sparkline["ranges"] = new_ranges
3929 # Cleanup the input locations.
3930 new_locations = []
3931 for location in sparkline["locations"]:
3932 location = location.replace("$", "")
3933 new_locations.append(location)
3935 sparkline["locations"] = new_locations
3937 # Map options.
3938 sparkline["high"] = options.get("high_point")
3939 sparkline["low"] = options.get("low_point")
3940 sparkline["negative"] = options.get("negative_points")
3941 sparkline["first"] = options.get("first_point")
3942 sparkline["last"] = options.get("last_point")
3943 sparkline["markers"] = options.get("markers")
3944 sparkline["min"] = options.get("min")
3945 sparkline["max"] = options.get("max")
3946 sparkline["axis"] = options.get("axis")
3947 sparkline["reverse"] = options.get("reverse")
3948 sparkline["hidden"] = options.get("show_hidden")
3949 sparkline["weight"] = options.get("weight")
3951 # Map empty cells options.
3952 empty = options.get("empty_cells", "")
3954 if empty == "zero":
3955 sparkline["empty"] = 0
3956 elif empty == "connect":
3957 sparkline["empty"] = "span"
3958 else:
3959 sparkline["empty"] = "gap"
3961 # Map the date axis range.
3962 date_range = options.get("date_axis")
3964 if date_range and "!" not in date_range:
3965 date_range = sheetname + "!" + date_range
3967 sparkline["date_axis"] = date_range
3969 # Set the sparkline styles.
3970 style_id = options.get("style", 0)
3971 style = _get_sparkline_style(style_id)
3973 sparkline["series_color"] = style["series"]
3974 sparkline["negative_color"] = style["negative"]
3975 sparkline["markers_color"] = style["markers"]
3976 sparkline["first_color"] = style["first"]
3977 sparkline["last_color"] = style["last"]
3978 sparkline["high_color"] = style["high"]
3979 sparkline["low_color"] = style["low"]
3981 # Override the style colors with user defined colors.
3982 self._set_spark_color(sparkline, options, "series_color")
3983 self._set_spark_color(sparkline, options, "negative_color")
3984 self._set_spark_color(sparkline, options, "markers_color")
3985 self._set_spark_color(sparkline, options, "first_color")
3986 self._set_spark_color(sparkline, options, "last_color")
3987 self._set_spark_color(sparkline, options, "high_color")
3988 self._set_spark_color(sparkline, options, "low_color")
3990 self.sparklines.append(sparkline)
3992 return 0
3994 @convert_range_args
3995 def set_selection(
3996 self, first_row: int, first_col: int, last_row: int, last_col: int
3997 ) -> None:
3998 """
3999 Set the selected cell or cells in a worksheet
4001 Args:
4002 first_row: The first row of the cell range. (zero indexed).
4003 first_col: The first column of the cell range.
4004 last_row: The last row of the cell range. (zero indexed).
4005 last_col: The last column of the cell range.
4007 Returns:
4008 0: Nothing.
4009 """
4010 pane = None
4012 # Range selection. Do this before swapping max/min to allow the
4013 # selection direction to be reversed.
4014 active_cell = xl_rowcol_to_cell(first_row, first_col)
4016 # Swap last row/col for first row/col if necessary
4017 if first_row > last_row:
4018 first_row, last_row = (last_row, first_row)
4020 if first_col > last_col:
4021 first_col, last_col = (last_col, first_col)
4023 sqref = xl_range(first_row, first_col, last_row, last_col)
4025 # Selection isn't set for cell A1.
4026 if sqref == "A1":
4027 return
4029 self.selections = [[pane, active_cell, sqref]]
4031 @convert_cell_args
4032 def set_top_left_cell(self, row: int = 0, col: int = 0) -> None:
4033 """
4034 Set the first visible cell at the top left of a worksheet.
4036 Args:
4037 row: The cell row (zero indexed).
4038 col: The cell column (zero indexed).
4040 Returns:
4041 0: Nothing.
4042 """
4044 if row == 0 and col == 0:
4045 return
4047 self.top_left_cell = xl_rowcol_to_cell(row, col)
4049 def outline_settings(
4050 self,
4051 visible: bool = 1,
4052 symbols_below: bool = 1,
4053 symbols_right: bool = 1,
4054 auto_style: bool = 0,
4055 ) -> None:
4056 """
4057 Control outline settings.
4059 Args:
4060 visible: Outlines are visible. Optional, defaults to True.
4061 symbols_below: Show row outline symbols below the outline bar.
4062 Optional, defaults to True.
4063 symbols_right: Show column outline symbols to the right of the
4064 outline bar. Optional, defaults to True.
4065 auto_style: Use Automatic style. Optional, defaults to False.
4067 Returns:
4068 0: Nothing.
4069 """
4070 self.outline_on = visible
4071 self.outline_below = symbols_below
4072 self.outline_right = symbols_right
4073 self.outline_style = auto_style
4075 self.outline_changed = True
4077 @convert_cell_args
4078 def freeze_panes(
4079 self,
4080 row: int,
4081 col: int,
4082 top_row: Optional[int] = None,
4083 left_col: Optional[int] = None,
4084 pane_type: int = 0,
4085 ) -> None:
4086 """
4087 Create worksheet panes and mark them as frozen.
4089 Args:
4090 row: The cell row (zero indexed).
4091 col: The cell column (zero indexed).
4092 top_row: Topmost visible row in scrolling region of pane.
4093 left_col: Leftmost visible row in scrolling region of pane.
4095 Returns:
4096 0: Nothing.
4098 """
4099 if top_row is None:
4100 top_row = row
4102 if left_col is None:
4103 left_col = col
4105 self.panes = [row, col, top_row, left_col, pane_type]
4107 @convert_cell_args
4108 def split_panes(
4109 self,
4110 x: float,
4111 y: float,
4112 top_row: Optional[int] = None,
4113 left_col: Optional[int] = None,
4114 ) -> None:
4115 """
4116 Create worksheet panes and mark them as split.
4118 Args:
4119 x: The position for the vertical split.
4120 y: The position for the horizontal split.
4121 top_row: Topmost visible row in scrolling region of pane.
4122 left_col: Leftmost visible row in scrolling region of pane.
4124 Returns:
4125 0: Nothing.
4127 """
4128 # Same as freeze panes with a different pane type.
4129 self.freeze_panes(x, y, top_row, left_col, 2)
4131 def set_zoom(self, zoom: int = 100) -> None:
4132 """
4133 Set the worksheet zoom factor.
4135 Args:
4136 zoom: Scale factor: 10 <= zoom <= 400.
4138 Returns:
4139 Nothing.
4141 """
4142 # Ensure the zoom scale is in Excel's range.
4143 if zoom < 10 or zoom > 400:
4144 warn(f"Zoom factor '{zoom}' outside range: 10 <= zoom <= 400")
4145 zoom = 100
4147 self.zoom = int(zoom)
4149 def set_zoom_to_fit(self) -> None:
4150 """
4151 Set the worksheet zoom to selection/fit. Only works for chartsheets.
4153 Args:
4154 None.
4156 Returns:
4157 Nothing.
4159 """
4160 self.zoom_to_fit = True
4162 def right_to_left(self) -> None:
4163 """
4164 Display the worksheet right to left for some versions of Excel.
4166 Args:
4167 None.
4169 Returns:
4170 Nothing.
4172 """
4173 self.is_right_to_left = True
4175 def hide_zero(self) -> None:
4176 """
4177 Hide zero values in worksheet cells.
4179 Args:
4180 None.
4182 Returns:
4183 Nothing.
4185 """
4186 self.show_zeros = 0
4188 def set_tab_color(self, color: Union[str, Color]) -> None:
4189 """
4190 Set the color of the worksheet tab.
4192 Args:
4193 color: A #RGB color index.
4195 Returns:
4196 Nothing.
4198 """
4199 self.tab_color = Color._from_value(color)
4201 def protect(
4202 self, password: str = "", options: Optional[Dict[str, Any]] = None
4203 ) -> None:
4204 """
4205 Set the password and protection options of the worksheet.
4207 Args:
4208 password: An optional password string.
4209 options: A dictionary of worksheet objects to protect.
4211 Returns:
4212 Nothing.
4214 """
4215 if password != "":
4216 password = self._encode_password(password)
4218 if not options:
4219 options = {}
4221 # Default values for objects that can be protected.
4222 defaults = {
4223 "sheet": True,
4224 "content": False,
4225 "objects": False,
4226 "scenarios": False,
4227 "format_cells": False,
4228 "format_columns": False,
4229 "format_rows": False,
4230 "insert_columns": False,
4231 "insert_rows": False,
4232 "insert_hyperlinks": False,
4233 "delete_columns": False,
4234 "delete_rows": False,
4235 "select_locked_cells": True,
4236 "sort": False,
4237 "autofilter": False,
4238 "pivot_tables": False,
4239 "select_unlocked_cells": True,
4240 }
4242 # Overwrite the defaults with user specified values.
4243 for key in options.keys():
4244 if key in defaults:
4245 defaults[key] = options[key]
4246 else:
4247 warn(f"Unknown protection object: '{key}'")
4249 # Set the password after the user defined values.
4250 defaults["password"] = password
4252 self.protect_options = defaults
4254 def unprotect_range(
4255 self,
4256 cell_range: str,
4257 range_name: Optional[str] = None,
4258 password: Optional[str] = None,
4259 ) -> int:
4260 """
4261 Unprotect ranges within a protected worksheet.
4263 Args:
4264 cell_range: The cell or cell range to unprotect.
4265 range_name: An optional name for the range.
4266 password: An optional password string. (undocumented)
4268 Returns:
4269 0: Success.
4270 -1: Parameter error.
4272 """
4273 if cell_range is None:
4274 warn("Cell range must be specified in unprotect_range()")
4275 return -1
4277 # Sanitize the cell range.
4278 cell_range = cell_range.lstrip("=")
4279 cell_range = cell_range.replace("$", "")
4281 self.num_protected_ranges += 1
4283 if range_name is None:
4284 range_name = "Range" + str(self.num_protected_ranges)
4286 if password:
4287 password = self._encode_password(password)
4289 self.protected_ranges.append((cell_range, range_name, password))
4291 return 0
4293 @convert_cell_args
4294 def insert_button(
4295 self, row: int, col: int, options: Optional[Dict[str, Any]] = None
4296 ) -> Literal[0, -1]:
4297 """
4298 Insert a button form object into the worksheet.
4300 Args:
4301 row: The cell row (zero indexed).
4302 col: The cell column (zero indexed).
4303 options: Button formatting options.
4305 Returns:
4306 0: Success.
4307 -1: Row or column is out of worksheet bounds.
4309 """
4310 # Check insert (row, col) without storing.
4311 if self._check_dimensions(row, col, True, True):
4312 warn(f"Cannot insert button at ({row}, {col}).")
4313 return -1
4315 if options is None:
4316 options = {}
4318 # Create a new button object.
4319 height = self.default_row_height
4320 width = self.default_col_width
4321 button_number = 1 + len(self.buttons_list)
4323 button = ButtonType(row, col, height, width, button_number, options)
4325 self.buttons_list.append(button)
4327 self.has_vml = True
4329 return 0
4331 @convert_cell_args
4332 def insert_checkbox(
4333 self, row: int, col: int, boolean: bool, cell_format: Optional[Format] = None
4334 ):
4335 """
4336 Insert a boolean checkbox in a worksheet cell.
4338 Args:
4339 row: The cell row (zero indexed).
4340 col: The cell column (zero indexed).
4341 boolean: The boolean value to display as a checkbox.
4342 cell_format: Cell Format object. (optional)
4344 Returns:
4345 0: Success.
4346 -1: Row or column is out of worksheet bounds.
4348 """
4349 # Ensure that the checkbox property is set in the user defined format.
4350 if cell_format and not cell_format.checkbox:
4351 # This needs to be fixed with a clone.
4352 cell_format.set_checkbox()
4354 # If no format is supplied create and/or use the default checkbox format.
4355 if not cell_format:
4356 if not self.default_checkbox_format:
4357 self.default_checkbox_format = self.workbook_add_format()
4358 self.default_checkbox_format.set_checkbox()
4360 cell_format = self.default_checkbox_format
4362 return self._write_boolean(row, col, boolean, cell_format)
4364 ###########################################################################
4365 #
4366 # Public API. Page Setup methods.
4367 #
4368 ###########################################################################
4369 def set_landscape(self) -> None:
4370 """
4371 Set the page orientation as landscape.
4373 Args:
4374 None.
4376 Returns:
4377 Nothing.
4379 """
4380 self.orientation = 0
4381 self.page_setup_changed = True
4383 def set_portrait(self) -> None:
4384 """
4385 Set the page orientation as portrait.
4387 Args:
4388 None.
4390 Returns:
4391 Nothing.
4393 """
4394 self.orientation = 1
4395 self.page_setup_changed = True
4397 def set_page_view(self, view: Literal[0, 1, 2] = 1) -> None:
4398 """
4399 Set the page view mode.
4401 Args:
4402 0: Normal view mode
4403 1: Page view mode (the default)
4404 2: Page break view mode
4406 Returns:
4407 Nothing.
4409 """
4410 self.page_view = view
4412 def set_pagebreak_view(self) -> None:
4413 """
4414 Set the page view mode.
4416 Args:
4417 None.
4419 Returns:
4420 Nothing.
4422 """
4423 self.page_view = 2
4425 def set_paper(self, paper_size: Union[Literal[1, 9], int]) -> None:
4426 """
4427 Set the paper type. US Letter = 1, A4 = 9.
4429 Args:
4430 paper_size: Paper index.
4432 Returns:
4433 Nothing.
4435 """
4436 if paper_size:
4437 self.paper_size = paper_size
4438 self.page_setup_changed = True
4440 def center_horizontally(self) -> None:
4441 """
4442 Center the page horizontally.
4444 Args:
4445 None.
4447 Returns:
4448 Nothing.
4450 """
4451 self.print_options_changed = True
4452 self.hcenter = 1
4454 def center_vertically(self) -> None:
4455 """
4456 Center the page vertically.
4458 Args:
4459 None.
4461 Returns:
4462 Nothing.
4464 """
4465 self.print_options_changed = True
4466 self.vcenter = 1
4468 def set_margins(
4469 self,
4470 left: float = 0.7,
4471 right: float = 0.7,
4472 top: float = 0.75,
4473 bottom: float = 0.75,
4474 ) -> None:
4475 """
4476 Set all the page margins in inches.
4478 Args:
4479 left: Left margin.
4480 right: Right margin.
4481 top: Top margin.
4482 bottom: Bottom margin.
4484 Returns:
4485 Nothing.
4487 """
4488 self.margin_left = left
4489 self.margin_right = right
4490 self.margin_top = top
4491 self.margin_bottom = bottom
4493 def set_header(
4494 self, header: str = "", options: Optional[Dict[str, Any]] = None, margin=None
4495 ) -> None:
4496 """
4497 Set the page header caption and optional margin.
4499 Args:
4500 header: Header string.
4501 margin: Header margin.
4502 options: Header options, mainly for images.
4504 Returns:
4505 Nothing.
4507 """
4508 header_orig = header
4509 header = header.replace("&[Picture]", "&G")
4511 if len(header) > 255:
4512 warn("Header string cannot be longer than Excel's limit of 255 characters")
4513 return
4515 if options is not None:
4516 # For backward compatibility allow options to be the margin.
4517 if not isinstance(options, dict):
4518 options = {"margin": options}
4519 else:
4520 options = {}
4522 # Copy the user defined options so they aren't modified.
4523 options = options.copy()
4525 # For backward compatibility.
4526 if margin is not None:
4527 options["margin"] = margin
4529 # Reset the list in case the function is called more than once.
4530 self.header_images = []
4532 if options.get("image_left"):
4533 options["image_data"] = options.get("image_data_left")
4534 image = self._image_from_source(options.get("image_left"), options)
4535 image._header_position = "LH"
4536 self.header_images.append(image)
4538 if options.get("image_center"):
4539 options["image_data"] = options.get("image_data_center")
4540 image = self._image_from_source(options.get("image_center"), options)
4541 image._header_position = "CH"
4542 self.header_images.append(image)
4544 if options.get("image_right"):
4545 options["image_data"] = options.get("image_data_right")
4546 image = self._image_from_source(options.get("image_right"), options)
4547 image._header_position = "RH"
4548 self.header_images.append(image)
4550 placeholder_count = header.count("&G")
4551 image_count = len(self.header_images)
4553 if placeholder_count != image_count:
4554 warn(
4555 f"Number of footer images '{image_count}' doesn't match placeholder "
4556 f"count '{placeholder_count}' in string: {header_orig}"
4557 )
4558 self.header_images = []
4559 return
4561 if "align_with_margins" in options:
4562 self.header_footer_aligns = options["align_with_margins"]
4564 if "scale_with_doc" in options:
4565 self.header_footer_scales = options["scale_with_doc"]
4567 self.header = header
4568 self.margin_header = options.get("margin", 0.3)
4569 self.header_footer_changed = True
4571 if image_count:
4572 self.has_header_vml = True
4574 def set_footer(
4575 self, footer: str = "", options: Optional[Dict[str, Any]] = None, margin=None
4576 ) -> None:
4577 """
4578 Set the page footer caption and optional margin.
4580 Args:
4581 footer: Footer string.
4582 margin: Footer margin.
4583 options: Footer options, mainly for images.
4585 Returns:
4586 Nothing.
4588 """
4589 footer_orig = footer
4590 footer = footer.replace("&[Picture]", "&G")
4592 if len(footer) > 255:
4593 warn("Footer string cannot be longer than Excel's limit of 255 characters")
4594 return
4596 if options is not None:
4597 # For backward compatibility allow options to be the margin.
4598 if not isinstance(options, dict):
4599 options = {"margin": options}
4600 else:
4601 options = {}
4603 # Copy the user defined options so they aren't modified.
4604 options = options.copy()
4606 # For backward compatibility.
4607 if margin is not None:
4608 options["margin"] = margin
4610 # Reset the list in case the function is called more than once.
4611 self.footer_images = []
4613 if options.get("image_left"):
4614 options["image_data"] = options.get("image_data_left")
4615 image = self._image_from_source(options.get("image_left"), options)
4616 image._header_position = "LF"
4617 self.footer_images.append(image)
4619 if options.get("image_center"):
4620 options["image_data"] = options.get("image_data_center")
4621 image = self._image_from_source(options.get("image_center"), options)
4622 image._header_position = "CF"
4623 self.footer_images.append(image)
4625 if options.get("image_right"):
4626 options["image_data"] = options.get("image_data_right")
4627 image = self._image_from_source(options.get("image_right"), options)
4628 image._header_position = "RF"
4629 self.footer_images.append(image)
4631 placeholder_count = footer.count("&G")
4632 image_count = len(self.footer_images)
4634 if placeholder_count != image_count:
4635 warn(
4636 f"Number of footer images '{image_count}' doesn't match placeholder "
4637 f"count '{placeholder_count}' in string: {footer_orig}"
4638 )
4639 self.footer_images = []
4640 return
4642 if "align_with_margins" in options:
4643 self.header_footer_aligns = options["align_with_margins"]
4645 if "scale_with_doc" in options:
4646 self.header_footer_scales = options["scale_with_doc"]
4648 self.footer = footer
4649 self.margin_footer = options.get("margin", 0.3)
4650 self.header_footer_changed = True
4652 if image_count:
4653 self.has_header_vml = True
4655 def repeat_rows(self, first_row: int, last_row: Optional[int] = None) -> None:
4656 """
4657 Set the rows to repeat at the top of each printed page.
4659 Args:
4660 first_row: Start row for range.
4661 last_row: End row for range.
4663 Returns:
4664 Nothing.
4666 """
4667 if last_row is None:
4668 last_row = first_row
4670 # Convert rows to 1 based.
4671 first_row += 1
4672 last_row += 1
4674 # Create the row range area like: $1:$2.
4675 area = f"${first_row}:${last_row}"
4677 # Build up the print titles area "Sheet1!$1:$2"
4678 sheetname = quote_sheetname(self.name)
4679 self.repeat_row_range = sheetname + "!" + area
4681 @convert_column_args
4682 def repeat_columns(self, first_col: int, last_col: Optional[int] = None) -> None:
4683 """
4684 Set the columns to repeat at the left hand side of each printed page.
4686 Args:
4687 first_col: Start column for range.
4688 last_col: End column for range.
4690 Returns:
4691 Nothing.
4693 """
4694 if last_col is None:
4695 last_col = first_col
4697 # Convert to A notation.
4698 first_col = xl_col_to_name(first_col, 1)
4699 last_col = xl_col_to_name(last_col, 1)
4701 # Create a column range like $C:$D.
4702 area = first_col + ":" + last_col
4704 # Build up the print area range "=Sheet2!$C:$D"
4705 sheetname = quote_sheetname(self.name)
4706 self.repeat_col_range = sheetname + "!" + area
4708 def hide_gridlines(self, option: Literal[0, 1, 2] = 1) -> None:
4709 """
4710 Set the option to hide gridlines on the screen and the printed page.
4712 Args:
4713 option: 0 : Don't hide gridlines
4714 1 : Hide printed gridlines only
4715 2 : Hide screen and printed gridlines
4717 Returns:
4718 Nothing.
4720 """
4721 if option == 0:
4722 self.print_gridlines = 1
4723 self.screen_gridlines = 1
4724 self.print_options_changed = True
4725 elif option == 1:
4726 self.print_gridlines = 0
4727 self.screen_gridlines = 1
4728 else:
4729 self.print_gridlines = 0
4730 self.screen_gridlines = 0
4732 def print_row_col_headers(self) -> None:
4733 """
4734 Set the option to print the row and column headers on the printed page.
4736 Args:
4737 None.
4739 Returns:
4740 Nothing.
4742 """
4743 self.print_headers = True
4744 self.print_options_changed = True
4746 def hide_row_col_headers(self) -> None:
4747 """
4748 Set the option to hide the row and column headers on the worksheet.
4750 Args:
4751 None.
4753 Returns:
4754 Nothing.
4756 """
4757 self.row_col_headers = True
4759 @convert_range_args
4760 def print_area(
4761 self, first_row: int, first_col: int, last_row: int, last_col: int
4762 ) -> Literal[0, -1]:
4763 """
4764 Set the print area in the current worksheet.
4766 Args:
4767 first_row: The first row of the cell range. (zero indexed).
4768 first_col: The first column of the cell range.
4769 last_row: The last row of the cell range. (zero indexed).
4770 last_col: The last column of the cell range.
4772 Returns:
4773 0: Success.
4774 -1: Row or column is out of worksheet bounds.
4776 """
4777 # Set the print area in the current worksheet.
4779 # Ignore max print area since it is the same as no area for Excel.
4780 if (
4781 first_row == 0
4782 and first_col == 0
4783 and last_row == self.xls_rowmax - 1
4784 and last_col == self.xls_colmax - 1
4785 ):
4786 return -1
4788 # Build up the print area range "Sheet1!$A$1:$C$13".
4789 area = self._convert_name_area(first_row, first_col, last_row, last_col)
4790 self.print_area_range = area
4792 return 0
4794 def print_across(self) -> None:
4795 """
4796 Set the order in which pages are printed.
4798 Args:
4799 None.
4801 Returns:
4802 Nothing.
4804 """
4805 self.page_order = 1
4806 self.page_setup_changed = True
4808 def fit_to_pages(self, width: int, height: int) -> None:
4809 """
4810 Fit the printed area to a specific number of pages both vertically and
4811 horizontally.
4813 Args:
4814 width: Number of pages horizontally.
4815 height: Number of pages vertically.
4817 Returns:
4818 Nothing.
4820 """
4821 self.fit_page = 1
4822 self.fit_width = width
4823 self.fit_height = height
4824 self.page_setup_changed = True
4826 def set_start_page(self, start_page: int) -> None:
4827 """
4828 Set the start page number when printing.
4830 Args:
4831 start_page: Start page number.
4833 Returns:
4834 Nothing.
4836 """
4837 self.page_start = start_page
4839 def set_print_scale(self, scale: int) -> None:
4840 """
4841 Set the scale factor for the printed page.
4843 Args:
4844 scale: Print scale. 10 <= scale <= 400.
4846 Returns:
4847 Nothing.
4849 """
4850 # Confine the scale to Excel's range.
4851 if scale < 10 or scale > 400:
4852 warn(f"Print scale '{scale}' outside range: 10 <= scale <= 400")
4853 return
4855 # Turn off "fit to page" option when print scale is on.
4856 self.fit_page = 0
4858 self.print_scale = int(scale)
4859 self.page_setup_changed = True
4861 def print_black_and_white(self) -> None:
4862 """
4863 Set the option to print the worksheet in black and white.
4865 Args:
4866 None.
4868 Returns:
4869 Nothing.
4871 """
4872 self.black_white = True
4873 self.page_setup_changed = True
4875 def set_h_pagebreaks(self, breaks: List[int]) -> None:
4876 """
4877 Set the horizontal page breaks on a worksheet.
4879 Args:
4880 breaks: List of rows where the page breaks should be added.
4882 Returns:
4883 Nothing.
4885 """
4886 self.hbreaks = breaks
4888 def set_v_pagebreaks(self, breaks: List[int]) -> None:
4889 """
4890 Set the horizontal page breaks on a worksheet.
4892 Args:
4893 breaks: List of columns where the page breaks should be added.
4895 Returns:
4896 Nothing.
4898 """
4899 self.vbreaks = breaks
4901 def set_vba_name(self, name: Optional[str] = None) -> None:
4902 """
4903 Set the VBA name for the worksheet. By default this is the
4904 same as the sheet name: i.e., Sheet1 etc.
4906 Args:
4907 name: The VBA name for the worksheet.
4909 Returns:
4910 Nothing.
4912 """
4913 if name is not None:
4914 self.vba_codename = name
4915 else:
4916 self.vba_codename = "Sheet" + str(self.index + 1)
4918 def ignore_errors(self, options: Optional[Dict[str, Any]] = None) -> Literal[0, -1]:
4919 """
4920 Ignore various Excel errors/warnings in a worksheet for user defined
4921 ranges.
4923 Args:
4924 options: A dict of ignore errors keys with cell range values.
4926 Returns:
4927 0: Success.
4928 -1: Incorrect parameter or option.
4930 """
4931 if options is None:
4932 return -1
4934 # Copy the user defined options so they aren't modified.
4935 options = options.copy()
4937 # Valid input parameters.
4938 valid_parameters = {
4939 "number_stored_as_text",
4940 "eval_error",
4941 "formula_differs",
4942 "formula_range",
4943 "formula_unlocked",
4944 "empty_cell_reference",
4945 "list_data_validation",
4946 "calculated_column",
4947 "two_digit_text_year",
4948 }
4950 # Check for valid input parameters.
4951 for param_key in options.keys():
4952 if param_key not in valid_parameters:
4953 warn(f"Unknown parameter '{param_key}' in ignore_errors()")
4954 return -1
4956 self.ignored_errors = options
4958 return 0
4960 ###########################################################################
4961 #
4962 # Private API.
4963 #
4964 ###########################################################################
4965 def _initialize(self, init_data) -> None:
4966 self.name = init_data["name"]
4967 self.index = init_data["index"]
4968 self.str_table = init_data["str_table"]
4969 self.worksheet_meta = init_data["worksheet_meta"]
4970 self.constant_memory = init_data["constant_memory"]
4971 self.tmpdir = init_data["tmpdir"]
4972 self.date_1904 = init_data["date_1904"]
4973 self.strings_to_numbers = init_data["strings_to_numbers"]
4974 self.strings_to_formulas = init_data["strings_to_formulas"]
4975 self.strings_to_urls = init_data["strings_to_urls"]
4976 self.nan_inf_to_errors = init_data["nan_inf_to_errors"]
4977 self.default_date_format = init_data["default_date_format"]
4978 self.default_url_format = init_data["default_url_format"]
4979 self.workbook_add_format = init_data["workbook_add_format"]
4980 self.excel2003_style = init_data["excel2003_style"]
4981 self.remove_timezone = init_data["remove_timezone"]
4982 self.max_url_length = init_data["max_url_length"]
4983 self.use_future_functions = init_data["use_future_functions"]
4984 self.embedded_images = init_data["embedded_images"]
4985 self.default_row_height = init_data["default_row_height"]
4986 self.default_col_width = init_data["default_col_width"]
4987 self.max_digit_width = init_data["max_digit_width"]
4988 self.cell_padding = init_data["cell_padding"]
4989 self.max_col_width = init_data["max_col_width"]
4991 self.original_row_height = self.default_row_height
4993 if self.excel2003_style:
4994 self.original_row_height = 17
4995 self.default_row_height = 17
4996 self.margin_left = 0.75
4997 self.margin_right = 0.75
4998 self.margin_top = 1
4999 self.margin_bottom = 1
5000 self.margin_header = 0.5
5001 self.margin_footer = 0.5
5002 self.header_footer_aligns = False
5004 # Open a temp filehandle to store row data in constant_memory mode.
5005 if self.constant_memory:
5006 # This is sub-optimal but we need to create a temp file
5007 # with utf8 encoding in Python < 3.
5008 fd, filename = tempfile.mkstemp(dir=self.tmpdir)
5009 os.close(fd)
5010 self.row_data_filename = filename
5011 # pylint: disable=consider-using-with
5012 self.row_data_fh = open(filename, mode="w+", encoding="utf-8")
5014 # Set as the worksheet filehandle until the file is assembled.
5015 self.fh = self.row_data_fh
5017 def _assemble_xml_file(self) -> None:
5018 # Assemble and write the XML file.
5020 # Write the XML declaration.
5021 self._xml_declaration()
5023 # Write the root worksheet element.
5024 self._write_worksheet()
5026 # Write the worksheet properties.
5027 self._write_sheet_pr()
5029 # Write the worksheet dimensions.
5030 self._write_dimension()
5032 # Write the sheet view properties.
5033 self._write_sheet_views()
5035 # Write the sheet format properties.
5036 self._write_sheet_format_pr()
5038 # Write the sheet column info.
5039 self._write_cols()
5041 # Write the worksheet data such as rows columns and cells.
5042 if not self.constant_memory:
5043 self._write_sheet_data()
5044 else:
5045 self._write_optimized_sheet_data()
5047 # Write the sheetProtection element.
5048 self._write_sheet_protection()
5050 # Write the protectedRanges element.
5051 self._write_protected_ranges()
5053 # Write the phoneticPr element.
5054 if self.excel2003_style:
5055 self._write_phonetic_pr()
5057 # Write the autoFilter element.
5058 self._write_auto_filter()
5060 # Write the mergeCells element.
5061 self._write_merge_cells()
5063 # Write the conditional formats.
5064 self._write_conditional_formats()
5066 # Write the dataValidations element.
5067 self._write_data_validations()
5069 # Write the hyperlink element.
5070 self._write_hyperlinks()
5072 # Write the printOptions element.
5073 self._write_print_options()
5075 # Write the worksheet page_margins.
5076 self._write_page_margins()
5078 # Write the worksheet page setup.
5079 self._write_page_setup()
5081 # Write the headerFooter element.
5082 self._write_header_footer()
5084 # Write the rowBreaks element.
5085 self._write_row_breaks()
5087 # Write the colBreaks element.
5088 self._write_col_breaks()
5090 # Write the ignoredErrors element.
5091 self._write_ignored_errors()
5093 # Write the drawing element.
5094 self._write_drawings()
5096 # Write the legacyDrawing element.
5097 self._write_legacy_drawing()
5099 # Write the legacyDrawingHF element.
5100 self._write_legacy_drawing_hf()
5102 # Write the picture element, for the background.
5103 self._write_picture()
5105 # Write the tableParts element.
5106 self._write_table_parts()
5108 # Write the extLst elements.
5109 self._write_ext_list()
5111 # Close the worksheet tag.
5112 self._xml_end_tag("worksheet")
5114 # Close the file.
5115 self._xml_close()
5117 def _check_dimensions(
5118 self, row: int, col: int, ignore_row=False, ignore_col=False
5119 ) -> int:
5120 # Check that row and col are valid and store the max and min
5121 # values for use in other methods/elements. The ignore_row /
5122 # ignore_col flags is used to indicate that we wish to perform
5123 # the dimension check without storing the value. The ignore
5124 # flags are use by set_row() and data_validate.
5126 # Check that the row/col are within the worksheet bounds.
5127 if row < 0 or col < 0:
5128 return -1
5129 if row >= self.xls_rowmax or col >= self.xls_colmax:
5130 return -1
5132 # In constant_memory mode we don't change dimensions for rows
5133 # that are already written.
5134 if not ignore_row and not ignore_col and self.constant_memory:
5135 if row < self.previous_row:
5136 return -2
5138 if not ignore_row:
5139 if self.dim_rowmin is None or row < self.dim_rowmin:
5140 self.dim_rowmin = row
5141 if self.dim_rowmax is None or row > self.dim_rowmax:
5142 self.dim_rowmax = row
5144 if not ignore_col:
5145 if self.dim_colmin is None or col < self.dim_colmin:
5146 self.dim_colmin = col
5147 if self.dim_colmax is None or col > self.dim_colmax:
5148 self.dim_colmax = col
5150 return 0
5152 def _convert_date_time(self, dt_obj):
5153 # Convert a datetime object to an Excel serial date and time.
5154 return _datetime_to_excel_datetime(dt_obj, self.date_1904, self.remove_timezone)
5156 def _convert_name_area(self, row_num_1, col_num_1, row_num_2, col_num_2):
5157 # Convert zero indexed rows and columns to the format required by
5158 # worksheet named ranges, eg, "Sheet1!$A$1:$C$13".
5160 range1 = ""
5161 range2 = ""
5162 area = ""
5163 row_col_only = 0
5165 # Convert to A1 notation.
5166 col_char_1 = xl_col_to_name(col_num_1, 1)
5167 col_char_2 = xl_col_to_name(col_num_2, 1)
5168 row_char_1 = "$" + str(row_num_1 + 1)
5169 row_char_2 = "$" + str(row_num_2 + 1)
5171 # We need to handle special cases that refer to rows or columns only.
5172 if row_num_1 == 0 and row_num_2 == self.xls_rowmax - 1:
5173 range1 = col_char_1
5174 range2 = col_char_2
5175 row_col_only = 1
5176 elif col_num_1 == 0 and col_num_2 == self.xls_colmax - 1:
5177 range1 = row_char_1
5178 range2 = row_char_2
5179 row_col_only = 1
5180 else:
5181 range1 = col_char_1 + row_char_1
5182 range2 = col_char_2 + row_char_2
5184 # A repeated range is only written once (if it isn't a special case).
5185 if range1 == range2 and not row_col_only:
5186 area = range1
5187 else:
5188 area = range1 + ":" + range2
5190 # Build up the print area range "Sheet1!$A$1:$C$13".
5191 sheetname = quote_sheetname(self.name)
5192 area = sheetname + "!" + area
5194 return area
5196 def _sort_pagebreaks(self, breaks):
5197 # This is an internal method used to filter elements of a list of
5198 # pagebreaks used in the _store_hbreak() and _store_vbreak() methods.
5199 # It:
5200 # 1. Removes duplicate entries from the list.
5201 # 2. Sorts the list.
5202 # 3. Removes 0 from the list if present.
5203 if not breaks:
5204 return []
5206 breaks_set = set(breaks)
5208 if 0 in breaks_set:
5209 breaks_set.remove(0)
5211 breaks_list = list(breaks_set)
5212 breaks_list.sort()
5214 # The Excel 2007 specification says that the maximum number of page
5215 # breaks is 1026. However, in practice it is actually 1023.
5216 max_num_breaks = 1023
5217 if len(breaks_list) > max_num_breaks:
5218 breaks_list = breaks_list[:max_num_breaks]
5220 return breaks_list
5222 def _extract_filter_tokens(self, expression):
5223 # Extract the tokens from the filter expression. The tokens are mainly
5224 # non-whitespace groups. The only tricky part is to extract string
5225 # tokens that contain whitespace and/or quoted double quotes (Excel's
5226 # escaped quotes).
5227 #
5228 # Examples: 'x < 2000'
5229 # 'x > 2000 and x < 5000'
5230 # 'x = "foo"'
5231 # 'x = "foo bar"'
5232 # 'x = "foo "" bar"'
5233 #
5234 if not expression:
5235 return []
5237 token_re = re.compile(r'"(?:[^"]|"")*"|\S+')
5238 tokens = token_re.findall(expression)
5240 new_tokens = []
5241 # Remove single leading and trailing quotes and un-escape other quotes.
5242 for token in tokens:
5243 if token.startswith('"'):
5244 token = token[1:]
5246 if token.endswith('"'):
5247 token = token[:-1]
5249 token = token.replace('""', '"')
5251 new_tokens.append(token)
5253 return new_tokens
5255 def _parse_filter_expression(self, expression, tokens):
5256 # Converts the tokens of a possibly conditional expression into 1 or 2
5257 # sub expressions for further parsing.
5258 #
5259 # Examples:
5260 # ('x', '==', 2000) -> exp1
5261 # ('x', '>', 2000, 'and', 'x', '<', 5000) -> exp1 and exp2
5263 if len(tokens) == 7:
5264 # The number of tokens will be either 3 (for 1 expression)
5265 # or 7 (for 2 expressions).
5266 conditional = tokens[3]
5268 if re.match("(and|&&)", conditional):
5269 conditional = 0
5270 elif re.match(r"(or|\|\|)", conditional):
5271 conditional = 1
5272 else:
5273 warn(
5274 f"Token '{conditional}' is not a valid conditional "
5275 f"in filter expression '{expression}'"
5276 )
5278 expression_1 = self._parse_filter_tokens(expression, tokens[0:3])
5279 expression_2 = self._parse_filter_tokens(expression, tokens[4:7])
5280 return expression_1 + [conditional] + expression_2
5282 return self._parse_filter_tokens(expression, tokens)
5284 def _parse_filter_tokens(self, expression, tokens):
5285 # Parse the 3 tokens of a filter expression and return the operator
5286 # and token. The use of numbers instead of operators is a legacy of
5287 # Spreadsheet::WriteExcel.
5288 operators = {
5289 "==": 2,
5290 "=": 2,
5291 "=~": 2,
5292 "eq": 2,
5293 "!=": 5,
5294 "!~": 5,
5295 "ne": 5,
5296 "<>": 5,
5297 "<": 1,
5298 "<=": 3,
5299 ">": 4,
5300 ">=": 6,
5301 }
5303 operator = operators.get(tokens[1], None)
5304 token = tokens[2]
5306 # Special handling of "Top" filter expressions.
5307 if re.match("top|bottom", tokens[0].lower()):
5308 value = int(tokens[1])
5310 if value < 1 or value > 500:
5311 warn(
5312 f"The value '{token}' in expression '{expression}' "
5313 f"must be in the range 1 to 500"
5314 )
5316 token = token.lower()
5318 if token not in ("items", "%"):
5319 warn(
5320 f"The type '{token}' in expression '{expression}' "
5321 f"must be either 'items' or '%%'"
5322 )
5324 if tokens[0].lower() == "top":
5325 operator = 30
5326 else:
5327 operator = 32
5329 if tokens[2] == "%":
5330 operator += 1
5332 token = str(value)
5334 if not operator and tokens[0]:
5335 warn(
5336 f"Token '{token[0]}' is not a valid operator "
5337 f"in filter expression '{expression}'."
5338 )
5340 # Special handling for Blanks/NonBlanks.
5341 if re.match("blanks|nonblanks", token.lower()):
5342 # Only allow Equals or NotEqual in this context.
5343 if operator not in (2, 5):
5344 warn(
5345 f"The operator '{tokens[1]}' in expression '{expression}' "
5346 f"is not valid in relation to Blanks/NonBlanks'."
5347 )
5349 token = token.lower()
5351 # The operator should always be 2 (=) to flag a "simple" equality
5352 # in the binary record. Therefore we convert <> to =.
5353 if token == "blanks":
5354 if operator == 5:
5355 token = " "
5356 else:
5357 if operator == 5:
5358 operator = 2
5359 token = "blanks"
5360 else:
5361 operator = 5
5362 token = " "
5364 # if the string token contains an Excel match character then change the
5365 # operator type to indicate a non "simple" equality.
5366 if operator == 2 and re.search("[*?]", token):
5367 operator = 22
5369 return [operator, token]
5371 def _encode_password(self, password) -> str:
5372 # Hash a worksheet password. Based on the algorithm in
5373 # ECMA-376-4:2016, Office Open XML File Formats — Transitional
5374 # Migration Features, Additional attributes for workbookProtection
5375 # element (Part 1, §18.2.29).
5376 digest = 0x0000
5378 for char in password[::-1]:
5379 digest = ((digest >> 14) & 0x01) | ((digest << 1) & 0x7FFF)
5380 digest ^= ord(char)
5382 digest = ((digest >> 14) & 0x01) | ((digest << 1) & 0x7FFF)
5383 digest ^= len(password)
5384 digest ^= 0xCE4B
5386 return f"{digest:X}"
5388 def _image_from_source(self, source, options: Optional[Dict[str, Any]] = None):
5389 # Backward compatibility utility method to convert an input argument to
5390 # an Image object. The source can be a filename, BytesIO stream or
5391 # an existing Image object.
5392 if isinstance(source, Image):
5393 image = source
5394 elif options is not None and options.get("image_data"):
5395 image = Image(options["image_data"])
5396 image.image_name = source
5397 else:
5398 image = Image(source)
5400 return image
5402 def _prepare_image(
5403 self,
5404 image: Image,
5405 image_id: int,
5406 drawing_id: int,
5407 ) -> None:
5408 # Set up images/drawings.
5410 # Get the effective image width and height in pixels.
5411 width = image._width * image._x_scale
5412 height = image._height * image._y_scale
5414 # Scale by non 96dpi resolutions.
5415 width *= 96.0 / image._x_dpi
5416 height *= 96.0 / image._y_dpi
5418 dimensions = self._position_object_emus(
5419 image._col,
5420 image._row,
5421 image._x_offset,
5422 image._y_offset,
5423 width,
5424 height,
5425 image._anchor,
5426 )
5428 # Convert from pixels to emus.
5429 width = int(0.5 + (width * 9525))
5430 height = int(0.5 + (height * 9525))
5432 # Create a Drawing obj to use with worksheet unless one already exists.
5433 if not self.drawing:
5434 drawing = Drawing()
5435 drawing.embedded = 1
5436 self.drawing = drawing
5438 self.external_drawing_links.append(
5439 ["/drawing", "../drawings/drawing" + str(drawing_id) + ".xml", None]
5440 )
5441 else:
5442 drawing = self.drawing
5444 drawing_object = DrawingInfo()
5445 drawing_object._drawing_type = DrawingTypes.IMAGE
5446 drawing_object._dimensions = dimensions
5447 drawing_object._description = image.image_name
5448 drawing_object._width = width
5449 drawing_object._height = height
5450 drawing_object._shape = None
5451 drawing_object._anchor = image._anchor
5452 drawing_object._rel_index = 0
5453 drawing_object._decorative = image._decorative
5455 if image.description is not None:
5456 drawing_object._description = image.description
5458 if image._url:
5459 url = image._url
5460 target = url._target()
5461 target_mode = url._target_mode()
5463 if not self.drawing_rels.get(url._link):
5464 self.drawing_links.append(["/hyperlink", target, target_mode])
5466 url._rel_index = self._get_drawing_rel_index(url._link)
5467 drawing_object._url = url
5469 if not self.drawing_rels.get(image._digest):
5470 self.drawing_links.append(
5471 [
5472 "/image",
5473 "../media/image" + str(image_id) + "." + image._image_extension,
5474 ]
5475 )
5477 drawing_object._rel_index = self._get_drawing_rel_index(image._digest)
5478 drawing._add_drawing_object(drawing_object)
5480 def _prepare_shape(self, index, drawing_id) -> None:
5481 # Set up shapes/drawings.
5482 (
5483 row,
5484 col,
5485 x_offset,
5486 y_offset,
5487 x_scale,
5488 y_scale,
5489 text,
5490 anchor,
5491 options,
5492 description,
5493 decorative,
5494 ) = self.shapes[index]
5496 width = options.get("width", self.default_col_width * 3)
5497 height = options.get("height", self.default_row_height * 6)
5499 width *= x_scale
5500 height *= y_scale
5502 dimensions = self._position_object_emus(
5503 col, row, x_offset, y_offset, width, height, anchor
5504 )
5506 # Convert from pixels to emus.
5507 width = int(0.5 + (width * 9525))
5508 height = int(0.5 + (height * 9525))
5510 # Create a Drawing obj to use with worksheet unless one already exists.
5511 if not self.drawing:
5512 drawing = Drawing()
5513 drawing.embedded = 1
5514 self.drawing = drawing
5516 self.external_drawing_links.append(
5517 ["/drawing", "../drawings/drawing" + str(drawing_id) + ".xml", None]
5518 )
5519 else:
5520 drawing = self.drawing
5522 shape = Shape("rect", "TextBox", options)
5523 shape.text = text
5525 drawing_object = DrawingInfo()
5526 drawing_object._drawing_type = DrawingTypes.SHAPE
5527 drawing_object._dimensions = dimensions
5528 drawing_object._width = width
5529 drawing_object._height = height
5530 drawing_object._description = description
5531 drawing_object._shape = shape
5532 drawing_object._anchor = anchor
5533 drawing_object._rel_index = 0
5534 drawing_object._decorative = decorative
5536 url = Url.from_options(options)
5537 if url:
5538 target = url._target()
5539 target_mode = url._target_mode()
5541 if not self.drawing_rels.get(url._link):
5542 self.drawing_links.append(["/hyperlink", target, target_mode])
5544 url._rel_index = self._get_drawing_rel_index(url._link)
5545 drawing_object._url = url
5547 drawing._add_drawing_object(drawing_object)
5549 def _prepare_header_image(self, image_id, image) -> None:
5550 # Set up an image without a drawing object for header/footer images.
5552 # Strip the extension from the filename.
5553 image.image_name = re.sub(r"\..*$", "", image.image_name)
5555 if not self.vml_drawing_rels.get(image._digest):
5556 self.vml_drawing_links.append(
5557 [
5558 "/image",
5559 "../media/image" + str(image_id) + "." + image._image_extension,
5560 ]
5561 )
5563 image._ref_id = self._get_vml_drawing_rel_index(image._digest)
5565 self.header_images_list.append(image)
5567 def _prepare_background(self, image_id, image_extension) -> None:
5568 # Set up an image without a drawing object for backgrounds.
5569 self.external_background_links.append(
5570 ["/image", "../media/image" + str(image_id) + "." + image_extension]
5571 )
5573 def _prepare_chart(self, index, chart_id, drawing_id) -> None:
5574 # Set up chart/drawings.
5575 (
5576 row,
5577 col,
5578 chart,
5579 x_offset,
5580 y_offset,
5581 x_scale,
5582 y_scale,
5583 anchor,
5584 description,
5585 decorative,
5586 ) = self.charts[index]
5588 chart.id = chart_id - 1
5590 # Use user specified dimensions, if any.
5591 width = int(0.5 + (chart.width * x_scale))
5592 height = int(0.5 + (chart.height * y_scale))
5594 dimensions = self._position_object_emus(
5595 col, row, x_offset, y_offset, width, height, anchor
5596 )
5598 # Set the chart name for the embedded object if it has been specified.
5599 name = chart.chart_name
5601 # Create a Drawing obj to use with worksheet unless one already exists.
5602 if not self.drawing:
5603 drawing = Drawing()
5604 drawing.embedded = 1
5605 self.drawing = drawing
5607 self.external_drawing_links.append(
5608 ["/drawing", "../drawings/drawing" + str(drawing_id) + ".xml"]
5609 )
5610 else:
5611 drawing = self.drawing
5613 drawing_object = DrawingInfo()
5614 drawing_object._drawing_type = DrawingTypes.CHART
5615 drawing_object._dimensions = dimensions
5616 drawing_object._width = width
5617 drawing_object._height = height
5618 drawing_object._name = name
5619 drawing_object._shape = None
5620 drawing_object._anchor = anchor
5621 drawing_object._rel_index = self._get_drawing_rel_index()
5622 drawing_object._description = description
5623 drawing_object._decorative = decorative
5625 drawing._add_drawing_object(drawing_object)
5627 self.drawing_links.append(
5628 ["/chart", "../charts/chart" + str(chart_id) + ".xml"]
5629 )
5631 def _position_object_emus(
5632 self, col_start, row_start, x1, y1, width, height, anchor
5633 ):
5634 # Calculate the vertices that define the position of a graphical
5635 # object within the worksheet in EMUs.
5636 #
5637 # The vertices are expressed as English Metric Units (EMUs). There are
5638 # 12,700 EMUs per point. Therefore, 12,700 * 3 /4 = 9,525 EMUs per
5639 # pixel
5640 (
5641 col_start,
5642 row_start,
5643 x1,
5644 y1,
5645 col_end,
5646 row_end,
5647 x2,
5648 y2,
5649 x_abs,
5650 y_abs,
5651 ) = self._position_object_pixels(
5652 col_start, row_start, x1, y1, width, height, anchor
5653 )
5655 # Convert the pixel values to EMUs. See above.
5656 x1 = int(0.5 + 9525 * x1)
5657 y1 = int(0.5 + 9525 * y1)
5658 x2 = int(0.5 + 9525 * x2)
5659 y2 = int(0.5 + 9525 * y2)
5660 x_abs = int(0.5 + 9525 * x_abs)
5661 y_abs = int(0.5 + 9525 * y_abs)
5663 return (col_start, row_start, x1, y1, col_end, row_end, x2, y2, x_abs, y_abs)
5665 # Calculate the vertices that define the position of a graphical object
5666 # within the worksheet in pixels.
5667 #
5668 # +------------+------------+
5669 # | A | B |
5670 # +-----+------------+------------+
5671 # | |(x1,y1) | |
5672 # | 1 |(A1)._______|______ |
5673 # | | | | |
5674 # | | | | |
5675 # +-----+----| OBJECT |-----+
5676 # | | | | |
5677 # | 2 | |______________. |
5678 # | | | (B2)|
5679 # | | | (x2,y2)|
5680 # +---- +------------+------------+
5681 #
5682 # Example of an object that covers some of the area from cell A1 to B2.
5683 #
5684 # Based on the width and height of the object we need to calculate 8 vars:
5685 #
5686 # col_start, row_start, col_end, row_end, x1, y1, x2, y2.
5687 #
5688 # We also calculate the absolute x and y position of the top left vertex of
5689 # the object. This is required for images.
5690 #
5691 # The width and height of the cells that the object occupies can be
5692 # variable and have to be taken into account.
5693 #
5694 # The values of col_start and row_start are passed in from the calling
5695 # function. The values of col_end and row_end are calculated by
5696 # subtracting the width and height of the object from the width and
5697 # height of the underlying cells.
5698 #
5699 def _position_object_pixels(
5700 self, col_start, row_start, x1, y1, width, height, anchor
5701 ):
5702 # col_start # Col containing upper left corner of object.
5703 # x1 # Distance to left side of object.
5704 #
5705 # row_start # Row containing top left corner of object.
5706 # y1 # Distance to top of object.
5707 #
5708 # col_end # Col containing lower right corner of object.
5709 # x2 # Distance to right side of object.
5710 #
5711 # row_end # Row containing bottom right corner of object.
5712 # y2 # Distance to bottom of object.
5713 #
5714 # width # Width of object frame.
5715 # height # Height of object frame.
5716 #
5717 # x_abs # Absolute distance to left side of object.
5718 # y_abs # Absolute distance to top side of object.
5719 x_abs = 0
5720 y_abs = 0
5722 # Adjust start column for negative offsets.
5723 # pylint: disable=chained-comparison
5724 while x1 < 0 and col_start > 0:
5725 x1 += self._size_col(col_start - 1)
5726 col_start -= 1
5728 # Adjust start row for negative offsets.
5729 while y1 < 0 and row_start > 0:
5730 y1 += self._size_row(row_start - 1)
5731 row_start -= 1
5733 # Ensure that the image isn't shifted off the page at top left.
5734 x1 = max(0, x1)
5735 y1 = max(0, y1)
5737 # Calculate the absolute x offset of the top-left vertex.
5738 if self.col_size_changed:
5739 for col_id in range(col_start):
5740 x_abs += self._size_col(col_id)
5741 else:
5742 # Optimization for when the column widths haven't changed.
5743 x_abs += self.default_col_width * col_start
5745 x_abs += x1
5747 # Calculate the absolute y offset of the top-left vertex.
5748 if self.row_size_changed:
5749 for row_id in range(row_start):
5750 y_abs += self._size_row(row_id)
5751 else:
5752 # Optimization for when the row heights haven't changed.
5753 y_abs += self.default_row_height * row_start
5755 y_abs += y1
5757 # Adjust start column for offsets that are greater than the col width.
5758 while x1 >= self._size_col(col_start, anchor):
5759 x1 -= self._size_col(col_start)
5760 col_start += 1
5762 # Adjust start row for offsets that are greater than the row height.
5763 while y1 >= self._size_row(row_start, anchor):
5764 y1 -= self._size_row(row_start)
5765 row_start += 1
5767 # Initialize end cell to the same as the start cell.
5768 col_end = col_start
5769 row_end = row_start
5771 # Don't offset the image in the cell if the row/col is hidden.
5772 if self._size_col(col_start, anchor) > 0:
5773 width = width + x1
5774 if self._size_row(row_start, anchor) > 0:
5775 height = height + y1
5777 # Subtract the underlying cell widths to find end cell of the object.
5778 while width >= self._size_col(col_end, anchor):
5779 width -= self._size_col(col_end, anchor)
5780 col_end += 1
5782 # Subtract the underlying cell heights to find end cell of the object.
5783 while height >= self._size_row(row_end, anchor):
5784 height -= self._size_row(row_end, anchor)
5785 row_end += 1
5787 # The end vertices are whatever is left from the width and height.
5788 x2 = width
5789 y2 = height
5791 return [col_start, row_start, x1, y1, col_end, row_end, x2, y2, x_abs, y_abs]
5793 def _size_col(self, col: int, anchor=0):
5794 # Look up the cell value to see if it has been changed.
5795 if col in self.col_info:
5796 width = self.col_info[col].width
5797 hidden = self.col_info[col].hidden
5799 if width is None:
5800 width = self.default_col_width
5802 if hidden and anchor != 4:
5803 width = 0
5805 return width
5807 return self.default_col_width
5809 def _size_row(self, row: int, anchor=0):
5810 # Look up the cell value to see if it has been changed
5811 if row in self.row_sizes:
5812 height = self.row_sizes[row][0]
5813 hidden = self.row_sizes[row][1]
5815 if hidden and anchor != 4:
5816 height = 0
5818 return height
5820 return self.default_row_height
5822 def _pixels_to_height(self, pixels):
5823 # Convert the height of a cell from pixels to character units.
5824 return 0.75 * pixels
5826 def _comment_vertices(self, comment: CommentType):
5827 # Calculate the positions of the comment object.
5828 anchor = 0
5829 vertices = self._position_object_pixels(
5830 comment.start_col,
5831 comment.start_row,
5832 comment.x_offset,
5833 comment.y_offset,
5834 comment.width,
5835 comment.height,
5836 anchor,
5837 )
5839 # Add the width and height for VML.
5840 vertices.append(comment.width)
5841 vertices.append(comment.height)
5843 return vertices
5845 def _button_vertices(self, button: ButtonType):
5846 # Calculate the positions of the button object.
5847 anchor = 0
5848 vertices = self._position_object_pixels(
5849 button.col,
5850 button.row,
5851 button.x_offset,
5852 button.y_offset,
5853 button.width,
5854 button.height,
5855 anchor,
5856 )
5858 # Add the width and height for VML.
5859 vertices.append(button.width)
5860 vertices.append(button.height)
5862 return vertices
5864 def _prepare_vml_objects(
5865 self, vml_data_id, vml_shape_id, vml_drawing_id, comment_id
5866 ):
5867 comments = []
5868 # Sort the comments into row/column order for easier comparison
5869 # testing and set the external links for comments and buttons.
5870 row_nums = sorted(self.comments.keys())
5872 for row in row_nums:
5873 col_nums = sorted(self.comments[row].keys())
5875 for col in col_nums:
5876 comment = self.comments[row][col]
5877 comment.vertices = self._comment_vertices(comment)
5879 # Set comment visibility if required and not user defined.
5880 if comment.is_visible is None:
5881 comment.is_visible = self.comments_visible
5883 # Set comment author if not already user defined.
5884 if comment.author is None:
5885 comment.author = self.comments_author
5887 comments.append(comment)
5889 for button in self.buttons_list:
5890 button.vertices = self._button_vertices(button)
5892 self.external_vml_links.append(
5893 ["/vmlDrawing", "../drawings/vmlDrawing" + str(vml_drawing_id) + ".vml"]
5894 )
5896 if self.has_comments:
5897 self.comments_list = comments
5899 self.external_comment_links.append(
5900 ["/comments", "../comments" + str(comment_id) + ".xml"]
5901 )
5903 count = len(comments)
5904 start_data_id = vml_data_id
5906 # The VML o:idmap data id contains a comma separated range when there
5907 # is more than one 1024 block of comments, like this: data="1,2".
5908 for i in range(int(count / 1024)):
5909 data_id = start_data_id + i + 1
5910 vml_data_id = f"{vml_data_id},{data_id}"
5912 self.vml_data_id = vml_data_id
5913 self.vml_shape_id = vml_shape_id
5915 return count
5917 def _prepare_header_vml_objects(self, vml_header_id, vml_drawing_id) -> None:
5918 # Set up external linkage for VML header/footer images.
5920 self.vml_header_id = vml_header_id
5922 self.external_vml_links.append(
5923 ["/vmlDrawing", "../drawings/vmlDrawing" + str(vml_drawing_id) + ".vml"]
5924 )
5926 def _prepare_tables(self, table_id, seen) -> None:
5927 # Set the table ids for the worksheet tables.
5928 for table in self.tables:
5929 table["id"] = table_id
5931 if table.get("name") is None:
5932 # Set a default name.
5933 table["name"] = "Table" + str(table_id)
5935 # Check for duplicate table names.
5936 name = table["name"].lower()
5938 if name in seen:
5939 raise DuplicateTableName(
5940 f"Duplicate name '{table['name']}' used in worksheet.add_table()."
5941 )
5943 seen[name] = True
5945 # Store the link used for the rels file.
5946 self.external_table_links.append(
5947 ["/table", "../tables/table" + str(table_id) + ".xml"]
5948 )
5949 table_id += 1
5951 def _table_function_to_formula(self, function, col_name):
5952 # Convert a table total function to a worksheet formula.
5953 formula = ""
5955 # Escape special characters, as required by Excel.
5956 col_name = col_name.replace("'", "''")
5957 col_name = col_name.replace("#", "'#")
5958 col_name = col_name.replace("]", "']")
5959 col_name = col_name.replace("[", "'[")
5961 subtotals = {
5962 "average": 101,
5963 "countNums": 102,
5964 "count": 103,
5965 "max": 104,
5966 "min": 105,
5967 "stdDev": 107,
5968 "sum": 109,
5969 "var": 110,
5970 }
5972 if function in subtotals:
5973 func_num = subtotals[function]
5974 formula = f"SUBTOTAL({func_num},[{col_name}])"
5975 else:
5976 warn(f"Unsupported function '{function}' in add_table()")
5978 return formula
5980 def _set_spark_color(self, sparkline, options, user_color) -> None:
5981 # Set the sparkline color.
5982 if user_color not in options:
5983 return
5985 sparkline[user_color] = Color._from_value(options[user_color])
5987 def _get_range_data(self, row_start, col_start, row_end, col_end):
5988 # Returns a range of data from the worksheet _table to be used in
5989 # chart cached data. Strings are returned as SST ids and decoded
5990 # in the workbook. Return None for data that doesn't exist since
5991 # Excel can chart have series with data missing.
5993 if self.constant_memory:
5994 return ()
5996 data = []
5998 # Iterate through the table data.
5999 for row_num in range(row_start, row_end + 1):
6000 # Store None if row doesn't exist.
6001 if row_num not in self.table:
6002 data.append(None)
6003 continue
6005 for col_num in range(col_start, col_end + 1):
6006 if col_num in self.table[row_num]:
6007 cell = self.table[row_num][col_num]
6009 cell_type = cell.__class__.__name__
6011 if cell_type in ("Number", "Datetime"):
6012 # Return a number with Excel's precision.
6013 data.append(f"{cell.number:.16g}")
6015 elif cell_type == "String":
6016 # Return a string from it's shared string index.
6017 index = cell.string
6018 string = self.str_table._get_shared_string(index)
6020 data.append(string)
6022 elif cell_type in ("Formula", "ArrayFormula"):
6023 # Return the formula value.
6024 value = cell.value
6026 if value is None:
6027 value = 0
6029 data.append(value)
6031 elif cell_type == "Blank":
6032 # Return a empty cell.
6033 data.append("")
6034 else:
6035 # Store None if column doesn't exist.
6036 data.append(None)
6038 return data
6040 def _csv_join(self, *items):
6041 # Create a csv string for use with data validation formulas and lists.
6043 # Convert non string types to string.
6044 items = [str(item) if not isinstance(item, str) else item for item in items]
6046 return ",".join(items)
6048 def _escape_url(self, url):
6049 # Don't escape URL if it looks already escaped.
6050 if re.search("%[0-9a-fA-F]{2}", url):
6051 return url
6053 # Can't use url.quote() here because it doesn't match Excel.
6054 url = url.replace("%", "%25")
6055 url = url.replace('"', "%22")
6056 url = url.replace(" ", "%20")
6057 url = url.replace("<", "%3c")
6058 url = url.replace(">", "%3e")
6059 url = url.replace("[", "%5b")
6060 url = url.replace("]", "%5d")
6061 url = url.replace("^", "%5e")
6062 url = url.replace("`", "%60")
6063 url = url.replace("{", "%7b")
6064 url = url.replace("}", "%7d")
6066 return url
6068 def _get_drawing_rel_index(self, target=None):
6069 # Get the index used to address a drawing rel link.
6070 if target is None:
6071 self.drawing_rels_id += 1
6072 return self.drawing_rels_id
6074 if self.drawing_rels.get(target):
6075 return self.drawing_rels[target]
6077 self.drawing_rels_id += 1
6078 self.drawing_rels[target] = self.drawing_rels_id
6079 return self.drawing_rels_id
6081 def _get_vml_drawing_rel_index(self, target=None):
6082 # Get the index used to address a vml drawing rel link.
6083 if self.vml_drawing_rels.get(target):
6084 return self.vml_drawing_rels[target]
6086 self.vml_drawing_rels_id += 1
6087 self.vml_drawing_rels[target] = self.vml_drawing_rels_id
6088 return self.vml_drawing_rels_id
6090 ###########################################################################
6091 #
6092 # The following font methods are mainly duplicated from the Styles class
6093 # with appropriate changes for rich string styles.
6094 #
6095 ###########################################################################
6096 def _write_font(self, xf_format) -> None:
6097 # Write the <font> element.
6098 xml_writer = self.rstring
6100 xml_writer._xml_start_tag("rPr")
6102 # Handle the main font properties.
6103 if xf_format.bold:
6104 xml_writer._xml_empty_tag("b")
6105 if xf_format.italic:
6106 xml_writer._xml_empty_tag("i")
6107 if xf_format.font_strikeout:
6108 xml_writer._xml_empty_tag("strike")
6109 if xf_format.font_outline:
6110 xml_writer._xml_empty_tag("outline")
6111 if xf_format.font_shadow:
6112 xml_writer._xml_empty_tag("shadow")
6114 # Handle the underline variants.
6115 if xf_format.underline:
6116 self._write_underline(xf_format.underline)
6118 # Handle super/subscript.
6119 if xf_format.font_script == 1:
6120 self._write_vert_align("superscript")
6121 if xf_format.font_script == 2:
6122 self._write_vert_align("subscript")
6124 # Write the font size
6125 xml_writer._xml_empty_tag("sz", [("val", xf_format.font_size)])
6127 # Handle colors.
6128 if xf_format.theme == -1:
6129 # Ignore for excel2003_style.
6130 pass
6131 elif xf_format.theme:
6132 self._write_rstring_color("color", [("theme", xf_format.theme)])
6133 elif xf_format.color_indexed:
6134 self._write_rstring_color("color", [("indexed", xf_format.color_indexed)])
6135 elif xf_format.font_color:
6136 color = xf_format.font_color
6137 if not color._is_automatic:
6138 self._write_rstring_color("color", color._attributes())
6139 else:
6140 self._write_rstring_color("color", [("theme", 1)])
6142 # Write some other font properties related to font families.
6143 xml_writer._xml_empty_tag("rFont", [("val", xf_format.font_name)])
6144 xml_writer._xml_empty_tag("family", [("val", xf_format.font_family)])
6146 if xf_format.font_name == "Calibri" and not xf_format.hyperlink:
6147 xml_writer._xml_empty_tag("scheme", [("val", xf_format.font_scheme)])
6149 xml_writer._xml_end_tag("rPr")
6151 def _write_underline(self, underline) -> None:
6152 # Write the underline font element.
6153 attributes = []
6155 # Handle the underline variants.
6156 if underline == 2:
6157 attributes = [("val", "double")]
6158 elif underline == 33:
6159 attributes = [("val", "singleAccounting")]
6160 elif underline == 34:
6161 attributes = [("val", "doubleAccounting")]
6163 self.rstring._xml_empty_tag("u", attributes)
6165 def _write_vert_align(self, val) -> None:
6166 # Write the <vertAlign> font sub-element.
6167 attributes = [("val", val)]
6169 self.rstring._xml_empty_tag("vertAlign", attributes)
6171 def _write_rstring_color(self, name, attributes) -> None:
6172 # Write the <color> element.
6173 self.rstring._xml_empty_tag(name, attributes)
6175 def _opt_close(self) -> None:
6176 # Close the row data filehandle in constant_memory mode.
6177 if not self.row_data_fh_closed:
6178 self.row_data_fh.close()
6179 self.row_data_fh_closed = True
6181 def _opt_reopen(self) -> None:
6182 # Reopen the row data filehandle in constant_memory mode.
6183 if self.row_data_fh_closed:
6184 filename = self.row_data_filename
6185 # pylint: disable=consider-using-with
6186 self.row_data_fh = open(filename, mode="a+", encoding="utf-8")
6187 self.row_data_fh_closed = False
6188 self.fh = self.row_data_fh
6190 def _set_icon_props(self, total_icons, user_props=None):
6191 # Set the sub-properties for icons.
6192 props = []
6194 # Set the defaults.
6195 for _ in range(total_icons):
6196 props.append({"criteria": False, "value": 0, "type": "percent"})
6198 # Set the default icon values based on the number of icons.
6199 if total_icons == 3:
6200 props[0]["value"] = 67
6201 props[1]["value"] = 33
6203 if total_icons == 4:
6204 props[0]["value"] = 75
6205 props[1]["value"] = 50
6206 props[2]["value"] = 25
6208 if total_icons == 5:
6209 props[0]["value"] = 80
6210 props[1]["value"] = 60
6211 props[2]["value"] = 40
6212 props[3]["value"] = 20
6214 # Overwrite default properties with user defined properties.
6215 if user_props:
6216 # Ensure we don't set user properties for lowest icon.
6217 max_data = len(user_props)
6218 if max_data >= total_icons:
6219 max_data = total_icons - 1
6221 for i in range(max_data):
6222 # Set the user defined 'value' property.
6223 if user_props[i].get("value") is not None:
6224 props[i]["value"] = user_props[i]["value"]
6226 # Remove the formula '=' sign if it exists.
6227 tmp = props[i]["value"]
6228 if isinstance(tmp, str) and tmp.startswith("="):
6229 props[i]["value"] = tmp.lstrip("=")
6231 # Set the user defined 'type' property.
6232 if user_props[i].get("type"):
6233 valid_types = ("percent", "percentile", "number", "formula")
6235 if user_props[i]["type"] not in valid_types:
6236 warn(
6237 f"Unknown icon property type '{user_props[i]['type']}' "
6238 f"for sub-property 'type' in conditional_format()."
6239 )
6240 else:
6241 props[i]["type"] = user_props[i]["type"]
6243 if props[i]["type"] == "number":
6244 props[i]["type"] = "num"
6246 # Set the user defined 'criteria' property.
6247 criteria = user_props[i].get("criteria")
6248 if criteria and criteria == ">":
6249 props[i]["criteria"] = True
6251 return props
6253 ###########################################################################
6254 #
6255 # XML methods.
6256 #
6257 ###########################################################################
6259 def _write_worksheet(self) -> None:
6260 # Write the <worksheet> element. This is the root element.
6262 schema = "http://schemas.openxmlformats.org/"
6263 xmlns = schema + "spreadsheetml/2006/main"
6264 xmlns_r = schema + "officeDocument/2006/relationships"
6265 xmlns_mc = schema + "markup-compatibility/2006"
6266 ms_schema = "http://schemas.microsoft.com/"
6267 xmlns_x14ac = ms_schema + "office/spreadsheetml/2009/9/ac"
6269 attributes = [("xmlns", xmlns), ("xmlns:r", xmlns_r)]
6271 # Add some extra attributes for Excel 2010. Mainly for sparklines.
6272 if self.excel_version == 2010:
6273 attributes.append(("xmlns:mc", xmlns_mc))
6274 attributes.append(("xmlns:x14ac", xmlns_x14ac))
6275 attributes.append(("mc:Ignorable", "x14ac"))
6277 self._xml_start_tag("worksheet", attributes)
6279 def _write_dimension(self) -> None:
6280 # Write the <dimension> element. This specifies the range of
6281 # cells in the worksheet. As a special case, empty
6282 # spreadsheets use 'A1' as a range.
6284 if self.dim_rowmin is None and self.dim_colmin is None:
6285 # If the min dimensions are not defined then no dimensions
6286 # have been set and we use the default 'A1'.
6287 ref = "A1"
6289 elif self.dim_rowmin is None and self.dim_colmin is not None:
6290 # If the row dimensions aren't set but the column
6291 # dimensions are set then they have been changed via
6292 # set_column().
6294 if self.dim_colmin == self.dim_colmax:
6295 # The dimensions are a single cell and not a range.
6296 ref = xl_rowcol_to_cell(0, self.dim_colmin)
6297 else:
6298 # The dimensions are a cell range.
6299 cell_1 = xl_rowcol_to_cell(0, self.dim_colmin)
6300 cell_2 = xl_rowcol_to_cell(0, self.dim_colmax)
6301 ref = cell_1 + ":" + cell_2
6303 elif self.dim_rowmin == self.dim_rowmax and self.dim_colmin == self.dim_colmax:
6304 # The dimensions are a single cell and not a range.
6305 ref = xl_rowcol_to_cell(self.dim_rowmin, self.dim_colmin)
6306 else:
6307 # The dimensions are a cell range.
6308 cell_1 = xl_rowcol_to_cell(self.dim_rowmin, self.dim_colmin)
6309 cell_2 = xl_rowcol_to_cell(self.dim_rowmax, self.dim_colmax)
6310 ref = cell_1 + ":" + cell_2
6312 self._xml_empty_tag("dimension", [("ref", ref)])
6314 def _write_sheet_views(self) -> None:
6315 # Write the <sheetViews> element.
6316 self._xml_start_tag("sheetViews")
6318 # Write the sheetView element.
6319 self._write_sheet_view()
6321 self._xml_end_tag("sheetViews")
6323 def _write_sheet_view(self) -> None:
6324 # Write the <sheetViews> element.
6325 attributes = []
6327 # Hide screen gridlines if required.
6328 if not self.screen_gridlines:
6329 attributes.append(("showGridLines", 0))
6331 # Hide screen row/column headers.
6332 if self.row_col_headers:
6333 attributes.append(("showRowColHeaders", 0))
6335 # Hide zeroes in cells.
6336 if not self.show_zeros:
6337 attributes.append(("showZeros", 0))
6339 # Display worksheet right to left for Hebrew, Arabic and others.
6340 if self.is_right_to_left:
6341 attributes.append(("rightToLeft", 1))
6343 # Show that the sheet tab is selected.
6344 if self.selected:
6345 attributes.append(("tabSelected", 1))
6347 # Turn outlines off. Also required in the outlinePr element.
6348 if not self.outline_on:
6349 attributes.append(("showOutlineSymbols", 0))
6351 # Set the page view/layout mode if required.
6352 if self.page_view == 1:
6353 attributes.append(("view", "pageLayout"))
6354 elif self.page_view == 2:
6355 attributes.append(("view", "pageBreakPreview"))
6357 # Set the first visible cell.
6358 if self.top_left_cell != "":
6359 attributes.append(("topLeftCell", self.top_left_cell))
6361 # Set the zoom level.
6362 if self.zoom != 100:
6363 attributes.append(("zoomScale", self.zoom))
6365 if self.page_view == 0 and self.zoom_scale_normal:
6366 attributes.append(("zoomScaleNormal", self.zoom))
6367 if self.page_view == 1:
6368 attributes.append(("zoomScalePageLayoutView", self.zoom))
6369 if self.page_view == 2:
6370 attributes.append(("zoomScaleSheetLayoutView", self.zoom))
6372 attributes.append(("workbookViewId", 0))
6374 if self.is_chartsheet and self.zoom_to_fit:
6375 attributes.append(("zoomToFit", 1))
6377 if self.panes or self.selections:
6378 self._xml_start_tag("sheetView", attributes)
6379 self._write_panes()
6380 self._write_selections()
6381 self._xml_end_tag("sheetView")
6382 else:
6383 self._xml_empty_tag("sheetView", attributes)
6385 def _write_sheet_format_pr(self) -> None:
6386 # Write the <sheetFormatPr> element.
6387 height_in_chars = self.default_row_height * 0.75
6388 row_level = self.outline_row_level
6389 col_level = self.outline_col_level
6391 attributes = [("defaultRowHeight", f"{height_in_chars:.16g}")]
6393 if self.default_row_height != self.original_row_height:
6394 attributes.append(("customHeight", 1))
6396 if self.default_row_zeroed:
6397 attributes.append(("zeroHeight", 1))
6399 if row_level:
6400 attributes.append(("outlineLevelRow", row_level))
6401 if col_level:
6402 attributes.append(("outlineLevelCol", col_level))
6404 if self.excel_version == 2010:
6405 attributes.append(("x14ac:dyDescent", "0.25"))
6407 self._xml_empty_tag("sheetFormatPr", attributes)
6409 def _write_cols(self) -> None:
6410 # Write the <cols> element and <col> sub elements.
6412 # Exit unless some column have been formatted.
6413 if not self.col_info:
6414 return
6416 self._xml_start_tag("cols")
6418 # Use the first element of the column information structures to set
6419 # the initial/previous properties.
6420 first_col = (sorted(self.col_info.keys()))[0]
6421 last_col = first_col
6422 prev_col_options = self.col_info[first_col]
6423 del self.col_info[first_col]
6424 deleted_col = first_col
6425 deleted_col_options = prev_col_options
6427 for col in sorted(self.col_info.keys()):
6428 col_options = self.col_info[col]
6429 # Check if the column number is contiguous with the previous
6430 # column and if the properties are the same.
6431 if col == last_col + 1 and col_options == prev_col_options:
6432 last_col = col
6433 else:
6434 # If not contiguous/equal then we write out the current range
6435 # of columns and start again.
6436 self._write_col_info(first_col, last_col, prev_col_options)
6437 first_col = col
6438 last_col = first_col
6439 prev_col_options = col_options
6441 # We will exit the previous loop with one unhandled column range.
6442 self._write_col_info(first_col, last_col, prev_col_options)
6444 # Put back the deleted first column information structure.
6445 self.col_info[deleted_col] = deleted_col_options
6447 self._xml_end_tag("cols")
6449 def _write_col_info(self, col_min: int, col_max: int, col_info: ColumnInfo) -> None:
6450 # Write the <col> element.
6451 width = col_info.width
6452 has_custom_width = True
6453 xf_index = 0
6455 # Get the cell_format index.
6456 if col_info.column_format:
6457 xf_index = col_info.column_format._get_xf_index()
6459 # Set the Excel default column width.
6460 if width is None:
6461 if not col_info.hidden:
6462 width = self.default_col_width
6463 has_custom_width = False
6464 else:
6465 width = 0
6466 elif width == self.default_col_width:
6467 # Width is defined but same as default.
6468 has_custom_width = False
6470 # Convert column width from pixels to character width.
6471 char_width = (width * 256 // self.max_digit_width) / 256.0
6473 attributes = [
6474 ("min", col_min + 1),
6475 ("max", col_max + 1),
6476 ("width", f"{char_width:.16g}"),
6477 ]
6479 if xf_index:
6480 attributes.append(("style", xf_index))
6481 if col_info.hidden:
6482 attributes.append(("hidden", "1"))
6483 if col_info.autofit:
6484 attributes.append(("bestFit", "1"))
6485 if has_custom_width:
6486 attributes.append(("customWidth", "1"))
6487 if col_info.level:
6488 attributes.append(("outlineLevel", col_info.level))
6489 if col_info.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.row_info
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.row_info:
6694 self._write_row(row_num, span)
6695 else:
6696 self._write_row(row_num, span, self.row_info[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 if row_num not in self.row_info:
6708 self._write_empty_row(row_num, span, None)
6709 else:
6710 self._write_empty_row(row_num, span, self.row_info[row_num])
6711 else:
6712 # Blank row with attributes only.
6713 if row_num not in self.row_info:
6714 self._write_empty_row(row_num, span, None)
6715 else:
6716 self._write_empty_row(row_num, span, self.row_info[row_num])
6718 def _write_single_row(self, current_row_num=0) -> None:
6719 # Write out the worksheet data as a single row with cells.
6720 # This method is used when constant_memory is on. A single
6721 # row is written and the data table is reset. That way only
6722 # one row of data is kept in memory at any one time. We don't
6723 # write span data in the optimized case since it is optional.
6725 # Set the new previous row as the current row.
6726 row_num = self.previous_row
6727 self.previous_row = current_row_num
6729 if row_num in self.row_info or row_num in self.comments or self.table[row_num]:
6730 # Only process rows with formatting, cell data and/or comments.
6732 # No span data in optimized mode.
6733 span = None
6735 if self.table[row_num]:
6736 # Write the cells if the row contains data.
6737 if row_num not in self.row_info:
6738 self._write_row(row_num, span)
6739 else:
6740 self._write_row(row_num, span, self.row_info[row_num])
6742 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6743 if col_num in self.table[row_num]:
6744 col_ref = self.table[row_num][col_num]
6745 self._write_cell(row_num, col_num, col_ref)
6747 self._xml_end_tag("row")
6748 else:
6749 # Row attributes or comments only.
6750 self._write_empty_row(row_num, span, self.row_info[row_num])
6752 # Reset table.
6753 self.table.clear()
6755 def _calculate_spans(self) -> None:
6756 # Calculate the "spans" attribute of the <row> tag. This is an
6757 # XLSX optimization and isn't strictly required. However, it
6758 # makes comparing files easier. The span is the same for each
6759 # block of 16 rows.
6760 spans = {}
6761 span_min = None
6762 span_max = None
6764 for row_num in range(self.dim_rowmin, self.dim_rowmax + 1):
6765 if row_num in self.table:
6766 # Calculate spans for cell data.
6767 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6768 if col_num in self.table[row_num]:
6769 if span_min is None:
6770 span_min = col_num
6771 span_max = col_num
6772 else:
6773 span_min = min(span_min, col_num)
6774 span_max = max(span_max, col_num)
6776 if row_num in self.comments:
6777 # Calculate spans for comments.
6778 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6779 if row_num in self.comments and col_num in self.comments[row_num]:
6780 if span_min is None:
6781 span_min = col_num
6782 span_max = col_num
6783 else:
6784 span_min = min(span_min, col_num)
6785 span_max = max(span_max, col_num)
6787 if ((row_num + 1) % 16 == 0) or row_num == self.dim_rowmax:
6788 span_index = int(row_num / 16)
6790 if span_min is not None:
6791 span_min += 1
6792 span_max += 1
6793 spans[span_index] = f"{span_min}:{span_max}"
6794 span_min = None
6796 self.row_spans = spans
6798 def _write_row(
6799 self,
6800 row: int,
6801 spans: Optional[str],
6802 row_info: Optional[RowInfo] = None,
6803 empty_row: bool = False,
6804 ) -> None:
6805 # Write the <row> element.
6806 xf_index = 0
6808 if row_info:
6809 height = row_info.height
6810 row_format = row_info.row_format
6811 hidden = row_info.hidden
6812 level = row_info.level
6813 collapsed = row_info.collapsed
6814 else:
6815 height = None
6816 row_format = None
6817 hidden = 0
6818 level = 0
6819 collapsed = 0
6821 if height is None:
6822 height = self.default_row_height
6824 attributes = [("r", row + 1)]
6826 # Get the cell_format index.
6827 if row_format:
6828 xf_index = row_format._get_xf_index()
6830 # Add row attributes where applicable.
6831 if spans:
6832 attributes.append(("spans", spans))
6834 if xf_index:
6835 attributes.append(("s", xf_index))
6837 if row_format:
6838 attributes.append(("customFormat", 1))
6840 if height != self.original_row_height or (
6841 height == self.original_row_height and height != self.default_row_height
6842 ):
6843 height_in_chars = height * 0.75
6844 attributes.append(("ht", f"{height_in_chars:.16g}"))
6846 if hidden:
6847 attributes.append(("hidden", 1))
6849 if height != self.original_row_height or (
6850 height == self.original_row_height and height != self.default_row_height
6851 ):
6852 attributes.append(("customHeight", 1))
6854 if level:
6855 attributes.append(("outlineLevel", level))
6857 if collapsed:
6858 attributes.append(("collapsed", 1))
6860 if self.excel_version == 2010:
6861 attributes.append(("x14ac:dyDescent", "0.25"))
6863 if empty_row:
6864 self._xml_empty_tag_unencoded("row", attributes)
6865 else:
6866 self._xml_start_tag_unencoded("row", attributes)
6868 def _write_empty_row(
6869 self, row: int, spans: Optional[str], row_info: Optional[RowInfo] = None
6870 ) -> None:
6871 # Write and empty <row> element.
6872 self._write_row(row, spans, row_info, empty_row=True)
6874 def _write_cell(self, row: int, col: int, cell) -> None:
6875 # Write the <cell> element.
6876 # Note. This is the innermost loop so efficiency is important.
6878 cell_range = xl_rowcol_to_cell_fast(row, col)
6879 attributes = [("r", cell_range)]
6881 if cell.format:
6882 # Add the cell format index.
6883 xf_index = cell.format._get_xf_index()
6884 attributes.append(("s", xf_index))
6885 elif row in self.row_info and self.row_info[row].row_format:
6886 # Add the row format.
6887 row_format = self.row_info[row].row_format
6888 attributes.append(("s", row_format._get_xf_index()))
6889 elif col in self.col_info:
6890 # Add the column format.
6891 column_format = self.col_info[col].column_format
6892 if column_format is not None:
6893 attributes.append(("s", column_format._get_xf_index()))
6895 type_cell_name = cell.__class__.__name__
6897 # Write the various cell types.
6898 if type_cell_name in ("Number", "Datetime"):
6899 # Write a number.
6900 self._xml_number_element(cell.number, attributes)
6902 elif type_cell_name in ("String", "RichString"):
6903 # Write a string.
6904 string = cell.string
6906 if not self.constant_memory:
6907 # Write a shared string.
6908 self._xml_string_element(string, attributes)
6909 else:
6910 # Write an optimized in-line string.
6912 # Convert control character to a _xHHHH_ escape.
6913 string = self._escape_control_characters(string)
6915 # Write any rich strings without further tags.
6916 if string.startswith("<r>") and string.endswith("</r>"):
6917 self._xml_rich_inline_string(string, attributes)
6918 else:
6919 # Add attribute to preserve leading or trailing whitespace.
6920 preserve = _preserve_whitespace(string)
6921 self._xml_inline_string(string, preserve, attributes)
6923 elif type_cell_name == "Formula":
6924 # Write a formula. First check the formula value type.
6925 value = cell.value
6926 if isinstance(cell.value, bool):
6927 attributes.append(("t", "b"))
6928 if cell.value:
6929 value = 1
6930 else:
6931 value = 0
6933 elif isinstance(cell.value, str):
6934 error_codes = (
6935 "#DIV/0!",
6936 "#N/A",
6937 "#NAME?",
6938 "#NULL!",
6939 "#NUM!",
6940 "#REF!",
6941 "#VALUE!",
6942 )
6944 if cell.value == "":
6945 # Allow blank to force recalc in some third party apps.
6946 pass
6947 elif cell.value in error_codes:
6948 attributes.append(("t", "e"))
6949 else:
6950 attributes.append(("t", "str"))
6952 self._xml_formula_element(cell.formula, value, attributes)
6954 elif type_cell_name == "ArrayFormula":
6955 # Write a array formula.
6957 if cell.atype == "dynamic":
6958 attributes.append(("cm", 1))
6960 # First check if the formula value is a string.
6961 try:
6962 float(cell.value)
6963 except ValueError:
6964 attributes.append(("t", "str"))
6966 # Write an array formula.
6967 self._xml_start_tag("c", attributes)
6969 self._write_cell_array_formula(cell.formula, cell.range)
6970 self._write_cell_value(cell.value)
6971 self._xml_end_tag("c")
6973 elif type_cell_name == "Blank":
6974 # Write a empty cell.
6975 self._xml_empty_tag("c", attributes)
6977 elif type_cell_name == "Boolean":
6978 # Write a boolean cell.
6979 attributes.append(("t", "b"))
6980 self._xml_start_tag("c", attributes)
6981 self._write_cell_value(cell.boolean)
6982 self._xml_end_tag("c")
6984 elif type_cell_name == "Error":
6985 # Write a boolean cell.
6986 attributes.append(("t", "e"))
6987 attributes.append(("vm", cell.value))
6988 self._xml_start_tag("c", attributes)
6989 self._write_cell_value(cell.error)
6990 self._xml_end_tag("c")
6992 def _write_cell_value(self, value) -> None:
6993 # Write the cell value <v> element.
6994 if value is None:
6995 value = ""
6997 self._xml_data_element("v", value)
6999 def _write_cell_array_formula(self, formula, cell_range) -> None:
7000 # Write the cell array formula <f> element.
7001 attributes = [("t", "array"), ("ref", cell_range)]
7003 self._xml_data_element("f", formula, attributes)
7005 def _write_sheet_pr(self) -> None:
7006 # Write the <sheetPr> element for Sheet level properties.
7007 attributes = []
7009 if (
7010 not self.fit_page
7011 and not self.filter_on
7012 and not self.tab_color
7013 and not self.outline_changed
7014 and not self.vba_codename
7015 ):
7016 return
7018 if self.vba_codename:
7019 attributes.append(("codeName", self.vba_codename))
7021 if self.filter_on:
7022 attributes.append(("filterMode", 1))
7024 if self.fit_page or self.tab_color or self.outline_changed:
7025 self._xml_start_tag("sheetPr", attributes)
7026 self._write_tab_color()
7027 self._write_outline_pr()
7028 self._write_page_set_up_pr()
7029 self._xml_end_tag("sheetPr")
7030 else:
7031 self._xml_empty_tag("sheetPr", attributes)
7033 def _write_page_set_up_pr(self) -> None:
7034 # Write the <pageSetUpPr> element.
7035 if not self.fit_page:
7036 return
7038 attributes = [("fitToPage", 1)]
7039 self._xml_empty_tag("pageSetUpPr", attributes)
7041 def _write_tab_color(self) -> None:
7042 # Write the <tabColor> element.
7043 color = self.tab_color
7045 if not color:
7046 return
7048 self._write_color("tabColor", color._attributes())
7050 def _write_outline_pr(self) -> None:
7051 # Write the <outlinePr> element.
7052 attributes = []
7054 if not self.outline_changed:
7055 return
7057 if self.outline_style:
7058 attributes.append(("applyStyles", 1))
7059 if not self.outline_below:
7060 attributes.append(("summaryBelow", 0))
7061 if not self.outline_right:
7062 attributes.append(("summaryRight", 0))
7063 if not self.outline_on:
7064 attributes.append(("showOutlineSymbols", 0))
7066 self._xml_empty_tag("outlinePr", attributes)
7068 def _write_row_breaks(self) -> None:
7069 # Write the <rowBreaks> element.
7070 page_breaks = self._sort_pagebreaks(self.hbreaks)
7072 if not page_breaks:
7073 return
7075 count = len(page_breaks)
7077 attributes = [
7078 ("count", count),
7079 ("manualBreakCount", count),
7080 ]
7082 self._xml_start_tag("rowBreaks", attributes)
7084 for row_num in page_breaks:
7085 self._write_brk(row_num, 16383)
7087 self._xml_end_tag("rowBreaks")
7089 def _write_col_breaks(self) -> None:
7090 # Write the <colBreaks> element.
7091 page_breaks = self._sort_pagebreaks(self.vbreaks)
7093 if not page_breaks:
7094 return
7096 count = len(page_breaks)
7098 attributes = [
7099 ("count", count),
7100 ("manualBreakCount", count),
7101 ]
7103 self._xml_start_tag("colBreaks", attributes)
7105 for col_num in page_breaks:
7106 self._write_brk(col_num, 1048575)
7108 self._xml_end_tag("colBreaks")
7110 def _write_brk(self, brk_id, brk_max) -> None:
7111 # Write the <brk> element.
7112 attributes = [("id", brk_id), ("max", brk_max), ("man", 1)]
7114 self._xml_empty_tag("brk", attributes)
7116 def _write_merge_cells(self) -> None:
7117 # Write the <mergeCells> element.
7118 merged_cells = self.merge
7119 count = len(merged_cells)
7121 if not count:
7122 return
7124 attributes = [("count", count)]
7126 self._xml_start_tag("mergeCells", attributes)
7128 for merged_range in merged_cells:
7129 # Write the mergeCell element.
7130 self._write_merge_cell(merged_range)
7132 self._xml_end_tag("mergeCells")
7134 def _write_merge_cell(self, merged_range) -> None:
7135 # Write the <mergeCell> element.
7136 row_min, col_min, row_max, col_max = merged_range
7138 # Convert the merge dimensions to a cell range.
7139 cell_1 = xl_rowcol_to_cell(row_min, col_min)
7140 cell_2 = xl_rowcol_to_cell(row_max, col_max)
7141 ref = cell_1 + ":" + cell_2
7143 attributes = [("ref", ref)]
7145 self._xml_empty_tag("mergeCell", attributes)
7147 def _write_hyperlinks(self) -> None:
7148 # Process any stored hyperlinks in row/col order and write the
7149 # <hyperlinks> element. The attributes are different for internal
7150 # and external links.
7152 # Sort the hyperlinks into row order.
7153 row_nums = sorted(self.hyperlinks.keys())
7155 # Exit if there are no hyperlinks to process.
7156 if not row_nums:
7157 return
7159 # Write the hyperlink elements.
7160 self._xml_start_tag("hyperlinks")
7162 # Iterate over the rows.
7163 for row_num in row_nums:
7164 # Sort the hyperlinks into column order.
7165 col_nums = sorted(self.hyperlinks[row_num].keys())
7167 # Iterate over the columns.
7168 for col_num in col_nums:
7169 # Get the link data for this cell.
7170 url = self.hyperlinks[row_num][col_num]
7172 # If the cell was overwritten by the user and isn't a string
7173 # then we have to add the url as the string to display.
7174 if self.table and self.table[row_num] and self.table[row_num][col_num]:
7175 cell = self.table[row_num][col_num]
7176 if cell.__class__.__name__ != "String":
7177 url._is_object_link = True
7179 if url._link_type in (UrlTypes.URL, UrlTypes.EXTERNAL):
7180 # External link with rel file relationship.
7181 self.rel_count += 1
7183 self._write_hyperlink_external(
7184 row_num, col_num, self.rel_count, url
7185 )
7187 # Links for use by the packager.
7188 self.external_hyper_links.append(
7189 ["/hyperlink", url._target(), "External"]
7190 )
7191 else:
7192 # Internal link with rel file relationship.
7193 self._write_hyperlink_internal(row_num, col_num, url)
7195 self._xml_end_tag("hyperlinks")
7197 def _write_hyperlink_external(
7198 self, row: int, col: int, id_num: int, url: Url
7199 ) -> None:
7200 # Write the <hyperlink> element for external links.
7201 ref = xl_rowcol_to_cell(row, col)
7202 r_id = "rId" + str(id_num)
7204 attributes = [("ref", ref), ("r:id", r_id)]
7206 if url._anchor:
7207 attributes.append(("location", url._anchor))
7209 if url._is_object_link:
7210 attributes.append(("display", url._text))
7212 if url._tip:
7213 attributes.append(("tooltip", url._tip))
7215 self._xml_empty_tag("hyperlink", attributes)
7217 def _write_hyperlink_internal(self, row: int, col: int, url: Url) -> None:
7218 # Write the <hyperlink> element for internal links.
7219 ref = xl_rowcol_to_cell(row, col)
7221 attributes = [("ref", ref), ("location", url._link)]
7223 if url._tip:
7224 attributes.append(("tooltip", url._tip))
7226 attributes.append(("display", url._text))
7228 self._xml_empty_tag("hyperlink", attributes)
7230 def _write_auto_filter(self) -> None:
7231 # Write the <autoFilter> element.
7232 if not self.autofilter_ref:
7233 return
7235 attributes = [("ref", self.autofilter_ref)]
7237 if self.filter_on:
7238 # Autofilter defined active filters.
7239 self._xml_start_tag("autoFilter", attributes)
7240 self._write_autofilters()
7241 self._xml_end_tag("autoFilter")
7243 else:
7244 # Autofilter defined without active filters.
7245 self._xml_empty_tag("autoFilter", attributes)
7247 def _write_autofilters(self) -> None:
7248 # Function to iterate through the columns that form part of an
7249 # autofilter range and write the appropriate filters.
7250 col1, col2 = self.filter_range
7252 for col in range(col1, col2 + 1):
7253 # Skip if column doesn't have an active filter.
7254 if col not in self.filter_cols:
7255 continue
7257 # Retrieve the filter tokens and write the autofilter records.
7258 tokens = self.filter_cols[col]
7259 filter_type = self.filter_type[col]
7261 # Filters are relative to first column in the autofilter.
7262 self._write_filter_column(col - col1, filter_type, tokens)
7264 def _write_filter_column(self, col_id, filter_type, filters) -> None:
7265 # Write the <filterColumn> element.
7266 attributes = [("colId", col_id)]
7268 self._xml_start_tag("filterColumn", attributes)
7270 if filter_type == 1:
7271 # Type == 1 is the new XLSX style filter.
7272 self._write_filters(filters)
7273 else:
7274 # Type == 0 is the classic "custom" filter.
7275 self._write_custom_filters(filters)
7277 self._xml_end_tag("filterColumn")
7279 def _write_filters(self, filters) -> None:
7280 # Write the <filters> element.
7281 non_blanks = [filter for filter in filters if str(filter).lower() != "blanks"]
7282 attributes = []
7284 if len(filters) != len(non_blanks):
7285 attributes = [("blank", 1)]
7287 if len(filters) == 1 and len(non_blanks) == 0:
7288 # Special case for blank cells only.
7289 self._xml_empty_tag("filters", attributes)
7290 else:
7291 # General case.
7292 self._xml_start_tag("filters", attributes)
7294 for autofilter in sorted(non_blanks):
7295 self._write_filter(autofilter)
7297 self._xml_end_tag("filters")
7299 def _write_filter(self, val) -> None:
7300 # Write the <filter> element.
7301 attributes = [("val", val)]
7303 self._xml_empty_tag("filter", attributes)
7305 def _write_custom_filters(self, tokens) -> None:
7306 # Write the <customFilters> element.
7307 if len(tokens) == 2:
7308 # One filter expression only.
7309 self._xml_start_tag("customFilters")
7310 self._write_custom_filter(*tokens)
7311 self._xml_end_tag("customFilters")
7312 else:
7313 # Two filter expressions.
7314 attributes = []
7316 # Check if the "join" operand is "and" or "or".
7317 if tokens[2] == 0:
7318 attributes = [("and", 1)]
7319 else:
7320 attributes = [("and", 0)]
7322 # Write the two custom filters.
7323 self._xml_start_tag("customFilters", attributes)
7324 self._write_custom_filter(tokens[0], tokens[1])
7325 self._write_custom_filter(tokens[3], tokens[4])
7326 self._xml_end_tag("customFilters")
7328 def _write_custom_filter(self, operator, val) -> None:
7329 # Write the <customFilter> element.
7330 attributes = []
7332 operators = {
7333 1: "lessThan",
7334 2: "equal",
7335 3: "lessThanOrEqual",
7336 4: "greaterThan",
7337 5: "notEqual",
7338 6: "greaterThanOrEqual",
7339 22: "equal",
7340 }
7342 # Convert the operator from a number to a descriptive string.
7343 if operators[operator] is not None:
7344 operator = operators[operator]
7345 else:
7346 warn(f"Unknown operator = {operator}")
7348 # The 'equal' operator is the default attribute and isn't stored.
7349 if operator != "equal":
7350 attributes.append(("operator", operator))
7351 attributes.append(("val", val))
7353 self._xml_empty_tag("customFilter", attributes)
7355 def _write_sheet_protection(self) -> None:
7356 # Write the <sheetProtection> element.
7357 attributes = []
7359 if not self.protect_options:
7360 return
7362 options = self.protect_options
7364 if options["password"]:
7365 attributes.append(("password", options["password"]))
7366 if options["sheet"]:
7367 attributes.append(("sheet", 1))
7368 if options["content"]:
7369 attributes.append(("content", 1))
7370 if not options["objects"]:
7371 attributes.append(("objects", 1))
7372 if not options["scenarios"]:
7373 attributes.append(("scenarios", 1))
7374 if options["format_cells"]:
7375 attributes.append(("formatCells", 0))
7376 if options["format_columns"]:
7377 attributes.append(("formatColumns", 0))
7378 if options["format_rows"]:
7379 attributes.append(("formatRows", 0))
7380 if options["insert_columns"]:
7381 attributes.append(("insertColumns", 0))
7382 if options["insert_rows"]:
7383 attributes.append(("insertRows", 0))
7384 if options["insert_hyperlinks"]:
7385 attributes.append(("insertHyperlinks", 0))
7386 if options["delete_columns"]:
7387 attributes.append(("deleteColumns", 0))
7388 if options["delete_rows"]:
7389 attributes.append(("deleteRows", 0))
7390 if not options["select_locked_cells"]:
7391 attributes.append(("selectLockedCells", 1))
7392 if options["sort"]:
7393 attributes.append(("sort", 0))
7394 if options["autofilter"]:
7395 attributes.append(("autoFilter", 0))
7396 if options["pivot_tables"]:
7397 attributes.append(("pivotTables", 0))
7398 if not options["select_unlocked_cells"]:
7399 attributes.append(("selectUnlockedCells", 1))
7401 self._xml_empty_tag("sheetProtection", attributes)
7403 def _write_protected_ranges(self) -> None:
7404 # Write the <protectedRanges> element.
7405 if self.num_protected_ranges == 0:
7406 return
7408 self._xml_start_tag("protectedRanges")
7410 for cell_range, range_name, password in self.protected_ranges:
7411 self._write_protected_range(cell_range, range_name, password)
7413 self._xml_end_tag("protectedRanges")
7415 def _write_protected_range(self, cell_range, range_name, password) -> None:
7416 # Write the <protectedRange> element.
7417 attributes = []
7419 if password:
7420 attributes.append(("password", password))
7422 attributes.append(("sqref", cell_range))
7423 attributes.append(("name", range_name))
7425 self._xml_empty_tag("protectedRange", attributes)
7427 def _write_drawings(self) -> None:
7428 # Write the <drawing> elements.
7429 if not self.drawing:
7430 return
7432 self.rel_count += 1
7433 self._write_drawing(self.rel_count)
7435 def _write_drawing(self, drawing_id) -> None:
7436 # Write the <drawing> element.
7437 r_id = "rId" + str(drawing_id)
7439 attributes = [("r:id", r_id)]
7441 self._xml_empty_tag("drawing", attributes)
7443 def _write_legacy_drawing(self) -> None:
7444 # Write the <legacyDrawing> element.
7445 if not self.has_vml:
7446 return
7448 # Increment the relationship id for any drawings or comments.
7449 self.rel_count += 1
7450 r_id = "rId" + str(self.rel_count)
7452 attributes = [("r:id", r_id)]
7454 self._xml_empty_tag("legacyDrawing", attributes)
7456 def _write_legacy_drawing_hf(self) -> None:
7457 # Write the <legacyDrawingHF> element.
7458 if not self.has_header_vml:
7459 return
7461 # Increment the relationship id for any drawings or comments.
7462 self.rel_count += 1
7463 r_id = "rId" + str(self.rel_count)
7465 attributes = [("r:id", r_id)]
7467 self._xml_empty_tag("legacyDrawingHF", attributes)
7469 def _write_picture(self) -> None:
7470 # Write the <picture> element.
7471 if not self.background_image:
7472 return
7474 # Increment the relationship id.
7475 self.rel_count += 1
7476 r_id = "rId" + str(self.rel_count)
7478 attributes = [("r:id", r_id)]
7480 self._xml_empty_tag("picture", attributes)
7482 def _write_data_validations(self) -> None:
7483 # Write the <dataValidations> element.
7484 validations = self.validations
7485 count = len(validations)
7487 if not count:
7488 return
7490 attributes = [("count", count)]
7492 self._xml_start_tag("dataValidations", attributes)
7494 for validation in validations:
7495 # Write the dataValidation element.
7496 self._write_data_validation(validation)
7498 self._xml_end_tag("dataValidations")
7500 def _write_data_validation(self, options) -> None:
7501 # Write the <dataValidation> element.
7502 sqref = ""
7503 attributes = []
7505 # Set the cell range(s) for the data validation.
7506 for cells in options["cells"]:
7507 # Add a space between multiple cell ranges.
7508 if sqref != "":
7509 sqref += " "
7511 row_first, col_first, row_last, col_last = cells
7513 # Swap last row/col for first row/col as necessary
7514 if row_first > row_last:
7515 row_first, row_last = (row_last, row_first)
7517 if col_first > col_last:
7518 col_first, col_last = (col_last, col_first)
7520 sqref += xl_range(row_first, col_first, row_last, col_last)
7522 if options.get("multi_range"):
7523 sqref = options["multi_range"]
7525 if options["validate"] != "none":
7526 attributes.append(("type", options["validate"]))
7528 if options["criteria"] != "between":
7529 attributes.append(("operator", options["criteria"]))
7531 if "error_type" in options:
7532 if options["error_type"] == 1:
7533 attributes.append(("errorStyle", "warning"))
7534 if options["error_type"] == 2:
7535 attributes.append(("errorStyle", "information"))
7537 if options["ignore_blank"]:
7538 attributes.append(("allowBlank", 1))
7540 if not options["dropdown"]:
7541 attributes.append(("showDropDown", 1))
7543 if options["show_input"]:
7544 attributes.append(("showInputMessage", 1))
7546 if options["show_error"]:
7547 attributes.append(("showErrorMessage", 1))
7549 if "error_title" in options:
7550 attributes.append(("errorTitle", options["error_title"]))
7552 if "error_message" in options:
7553 attributes.append(("error", options["error_message"]))
7555 if "input_title" in options:
7556 attributes.append(("promptTitle", options["input_title"]))
7558 if "input_message" in options:
7559 attributes.append(("prompt", options["input_message"]))
7561 attributes.append(("sqref", sqref))
7563 if options["validate"] == "none":
7564 self._xml_empty_tag("dataValidation", attributes)
7565 else:
7566 self._xml_start_tag("dataValidation", attributes)
7568 # Write the formula1 element.
7569 self._write_formula_1(options["value"])
7571 # Write the formula2 element.
7572 if options["maximum"] is not None:
7573 self._write_formula_2(options["maximum"])
7575 self._xml_end_tag("dataValidation")
7577 def _write_formula_1(self, formula) -> None:
7578 # Write the <formula1> element.
7580 if isinstance(formula, list):
7581 formula = self._csv_join(*formula)
7582 formula = f'"{formula}"'
7583 else:
7584 # Check if the formula is a number.
7585 try:
7586 float(formula)
7587 except ValueError:
7588 # Not a number. Remove the formula '=' sign if it exists.
7589 if formula.startswith("="):
7590 formula = formula.lstrip("=")
7592 self._xml_data_element("formula1", formula)
7594 def _write_formula_2(self, formula) -> None:
7595 # Write the <formula2> element.
7597 # Check if the formula is a number.
7598 try:
7599 float(formula)
7600 except ValueError:
7601 # Not a number. Remove the formula '=' sign if it exists.
7602 if formula.startswith("="):
7603 formula = formula.lstrip("=")
7605 self._xml_data_element("formula2", formula)
7607 def _write_conditional_formats(self) -> None:
7608 # Write the Worksheet conditional formats.
7609 ranges = sorted(self.cond_formats.keys())
7611 if not ranges:
7612 return
7614 for cond_range in ranges:
7615 self._write_conditional_formatting(
7616 cond_range, self.cond_formats[cond_range]
7617 )
7619 def _write_conditional_formatting(self, cond_range, params) -> None:
7620 # Write the <conditionalFormatting> element.
7621 attributes = [("sqref", cond_range)]
7622 self._xml_start_tag("conditionalFormatting", attributes)
7623 for param in params:
7624 # Write the cfRule element.
7625 self._write_cf_rule(param)
7626 self._xml_end_tag("conditionalFormatting")
7628 def _write_cf_rule(self, params) -> None:
7629 # Write the <cfRule> element.
7630 attributes = [("type", params["type"])]
7632 if "format" in params and params["format"] is not None:
7633 attributes.append(("dxfId", params["format"]))
7635 attributes.append(("priority", params["priority"]))
7637 if params.get("stop_if_true"):
7638 attributes.append(("stopIfTrue", 1))
7640 if params["type"] == "cellIs":
7641 attributes.append(("operator", params["criteria"]))
7643 self._xml_start_tag("cfRule", attributes)
7645 if "minimum" in params and "maximum" in params:
7646 self._write_formula_element(params["minimum"])
7647 self._write_formula_element(params["maximum"])
7648 else:
7649 self._write_formula_element(params["value"])
7651 self._xml_end_tag("cfRule")
7653 elif params["type"] == "aboveAverage":
7654 if re.search("below", params["criteria"]):
7655 attributes.append(("aboveAverage", 0))
7657 if re.search("equal", params["criteria"]):
7658 attributes.append(("equalAverage", 1))
7660 if re.search("[123] std dev", params["criteria"]):
7661 match = re.search("([123]) std dev", params["criteria"])
7662 attributes.append(("stdDev", match.group(1)))
7664 self._xml_empty_tag("cfRule", attributes)
7666 elif params["type"] == "top10":
7667 if "criteria" in params and params["criteria"] == "%":
7668 attributes.append(("percent", 1))
7670 if "direction" in params:
7671 attributes.append(("bottom", 1))
7673 rank = params["value"] or 10
7674 attributes.append(("rank", rank))
7676 self._xml_empty_tag("cfRule", attributes)
7678 elif params["type"] == "duplicateValues":
7679 self._xml_empty_tag("cfRule", attributes)
7681 elif params["type"] == "uniqueValues":
7682 self._xml_empty_tag("cfRule", attributes)
7684 elif (
7685 params["type"] == "containsText"
7686 or params["type"] == "notContainsText"
7687 or params["type"] == "beginsWith"
7688 or params["type"] == "endsWith"
7689 ):
7690 attributes.append(("operator", params["criteria"]))
7691 attributes.append(("text", params["value"]))
7692 self._xml_start_tag("cfRule", attributes)
7693 self._write_formula_element(params["formula"])
7694 self._xml_end_tag("cfRule")
7696 elif params["type"] == "timePeriod":
7697 attributes.append(("timePeriod", params["criteria"]))
7698 self._xml_start_tag("cfRule", attributes)
7699 self._write_formula_element(params["formula"])
7700 self._xml_end_tag("cfRule")
7702 elif (
7703 params["type"] == "containsBlanks"
7704 or params["type"] == "notContainsBlanks"
7705 or params["type"] == "containsErrors"
7706 or params["type"] == "notContainsErrors"
7707 ):
7708 self._xml_start_tag("cfRule", attributes)
7709 self._write_formula_element(params["formula"])
7710 self._xml_end_tag("cfRule")
7712 elif params["type"] == "colorScale":
7713 self._xml_start_tag("cfRule", attributes)
7714 self._write_color_scale(params)
7715 self._xml_end_tag("cfRule")
7717 elif params["type"] == "dataBar":
7718 self._xml_start_tag("cfRule", attributes)
7719 self._write_data_bar(params)
7721 if params.get("is_data_bar_2010"):
7722 self._write_data_bar_ext(params)
7724 self._xml_end_tag("cfRule")
7726 elif params["type"] == "expression":
7727 self._xml_start_tag("cfRule", attributes)
7728 self._write_formula_element(params["criteria"])
7729 self._xml_end_tag("cfRule")
7731 elif params["type"] == "iconSet":
7732 self._xml_start_tag("cfRule", attributes)
7733 self._write_icon_set(params)
7734 self._xml_end_tag("cfRule")
7736 def _write_formula_element(self, formula) -> None:
7737 # Write the <formula> element.
7739 # Check if the formula is a number.
7740 try:
7741 float(formula)
7742 except ValueError:
7743 # Not a number. Remove the formula '=' sign if it exists.
7744 if formula.startswith("="):
7745 formula = formula.lstrip("=")
7747 self._xml_data_element("formula", formula)
7749 def _write_color_scale(self, param) -> None:
7750 # Write the <colorScale> element.
7752 self._xml_start_tag("colorScale")
7754 self._write_cfvo(param["min_type"], param["min_value"])
7756 if param["mid_type"] is not None:
7757 self._write_cfvo(param["mid_type"], param["mid_value"])
7759 self._write_cfvo(param["max_type"], param["max_value"])
7761 self._write_color("color", param["min_color"]._attributes())
7763 if param["mid_color"] is not None:
7764 self._write_color("color", param["mid_color"]._attributes())
7766 self._write_color("color", param["max_color"]._attributes())
7768 self._xml_end_tag("colorScale")
7770 def _write_data_bar(self, param) -> None:
7771 # Write the <dataBar> element.
7772 attributes = []
7774 # Min and max bar lengths in in the spec but not supported directly by
7775 # Excel.
7776 if "min_length" in param:
7777 attributes.append(("minLength", param["min_length"]))
7779 if "max_length" in param:
7780 attributes.append(("maxLength", param["max_length"]))
7782 if param.get("bar_only"):
7783 attributes.append(("showValue", 0))
7785 self._xml_start_tag("dataBar", attributes)
7787 self._write_cfvo(param["min_type"], param["min_value"])
7788 self._write_cfvo(param["max_type"], param["max_value"])
7789 self._write_color("color", param["bar_color"]._attributes())
7791 self._xml_end_tag("dataBar")
7793 def _write_data_bar_ext(self, param) -> None:
7794 # Write the <extLst> dataBar extension element.
7796 # Create a pseudo GUID for each unique Excel 2010 data bar.
7797 worksheet_count = self.index + 1
7798 data_bar_count = len(self.data_bars_2010) + 1
7799 guid = "{DA7ABA51-AAAA-BBBB-%04X-%012X}" % (worksheet_count, data_bar_count)
7801 # Store the 2010 data bar parameters to write the extLst elements.
7802 param["guid"] = guid
7803 self.data_bars_2010.append(param)
7805 self._xml_start_tag("extLst")
7806 self._write_ext("{B025F937-C7B1-47D3-B67F-A62EFF666E3E}")
7807 self._xml_data_element("x14:id", guid)
7808 self._xml_end_tag("ext")
7809 self._xml_end_tag("extLst")
7811 def _write_icon_set(self, param) -> None:
7812 # Write the <iconSet> element.
7813 attributes = []
7815 # Don't set attribute for default style.
7816 if param["icon_style"] != "3TrafficLights":
7817 attributes = [("iconSet", param["icon_style"])]
7819 if param.get("icons_only"):
7820 attributes.append(("showValue", 0))
7822 if param.get("reverse_icons"):
7823 attributes.append(("reverse", 1))
7825 self._xml_start_tag("iconSet", attributes)
7827 # Write the properties for different icon styles.
7828 for icon in reversed(param["icons"]):
7829 self._write_cfvo(icon["type"], icon["value"], icon["criteria"])
7831 self._xml_end_tag("iconSet")
7833 def _write_cfvo(self, cf_type, val, criteria=None) -> None:
7834 # Write the <cfvo> element.
7835 attributes = [("type", cf_type)]
7837 if val is not None:
7838 attributes.append(("val", val))
7840 if criteria:
7841 attributes.append(("gte", 0))
7843 self._xml_empty_tag("cfvo", attributes)
7845 def _write_color(self, name, attributes) -> None:
7846 # Write the <color> element.
7847 self._xml_empty_tag(name, attributes)
7849 def _write_selections(self) -> None:
7850 # Write the <selection> elements.
7851 for selection in self.selections:
7852 self._write_selection(*selection)
7854 def _write_selection(self, pane, active_cell, sqref) -> None:
7855 # Write the <selection> element.
7856 attributes = []
7858 if pane:
7859 attributes.append(("pane", pane))
7861 if active_cell:
7862 attributes.append(("activeCell", active_cell))
7864 if sqref:
7865 attributes.append(("sqref", sqref))
7867 self._xml_empty_tag("selection", attributes)
7869 def _write_panes(self) -> None:
7870 # Write the frozen or split <pane> elements.
7871 panes = self.panes
7873 if not panes:
7874 return
7876 if panes[4] == 2:
7877 self._write_split_panes(*panes)
7878 else:
7879 self._write_freeze_panes(*panes)
7881 def _write_freeze_panes(
7882 self, row: int, col: int, top_row, left_col, pane_type
7883 ) -> None:
7884 # Write the <pane> element for freeze panes.
7885 attributes = []
7887 y_split = row
7888 x_split = col
7889 top_left_cell = xl_rowcol_to_cell(top_row, left_col)
7890 active_pane = ""
7891 state = ""
7892 active_cell = ""
7893 sqref = ""
7895 # Move user cell selection to the panes.
7896 if self.selections:
7897 _, active_cell, sqref = self.selections[0]
7898 self.selections = []
7900 # Set the active pane.
7901 if row and col:
7902 active_pane = "bottomRight"
7904 row_cell = xl_rowcol_to_cell(row, 0)
7905 col_cell = xl_rowcol_to_cell(0, col)
7907 self.selections.append(["topRight", col_cell, col_cell])
7908 self.selections.append(["bottomLeft", row_cell, row_cell])
7909 self.selections.append(["bottomRight", active_cell, sqref])
7911 elif col:
7912 active_pane = "topRight"
7913 self.selections.append(["topRight", active_cell, sqref])
7915 else:
7916 active_pane = "bottomLeft"
7917 self.selections.append(["bottomLeft", active_cell, sqref])
7919 # Set the pane type.
7920 if pane_type == 0:
7921 state = "frozen"
7922 elif pane_type == 1:
7923 state = "frozenSplit"
7924 else:
7925 state = "split"
7927 if x_split:
7928 attributes.append(("xSplit", x_split))
7930 if y_split:
7931 attributes.append(("ySplit", y_split))
7933 attributes.append(("topLeftCell", top_left_cell))
7934 attributes.append(("activePane", active_pane))
7935 attributes.append(("state", state))
7937 self._xml_empty_tag("pane", attributes)
7939 def _write_split_panes(self, row: int, col: int, top_row, left_col, _) -> None:
7940 # Write the <pane> element for split panes.
7941 attributes = []
7942 has_selection = False
7943 active_pane = ""
7944 active_cell = ""
7945 sqref = ""
7947 y_split = row
7948 x_split = col
7950 # Move user cell selection to the panes.
7951 if self.selections:
7952 _, active_cell, sqref = self.selections[0]
7953 self.selections = []
7954 has_selection = True
7956 # Convert the row and col to 1/20 twip units with padding.
7957 if y_split:
7958 y_split = int(20 * y_split + 300)
7960 if x_split:
7961 x_split = self._calculate_x_split_width(x_split)
7963 # For non-explicit topLeft definitions, estimate the cell offset based
7964 # on the pixels dimensions. This is only a workaround and doesn't take
7965 # adjusted cell dimensions into account.
7966 if top_row == row and left_col == col:
7967 top_row = int(0.5 + (y_split - 300) / 20 / 15)
7968 left_col = int(0.5 + (x_split - 390) / 20 / 3 * 4 / 64)
7970 top_left_cell = xl_rowcol_to_cell(top_row, left_col)
7972 # If there is no selection set the active cell to the top left cell.
7973 if not has_selection:
7974 active_cell = top_left_cell
7975 sqref = top_left_cell
7977 # Set the Cell selections.
7978 if row and col:
7979 active_pane = "bottomRight"
7981 row_cell = xl_rowcol_to_cell(top_row, 0)
7982 col_cell = xl_rowcol_to_cell(0, left_col)
7984 self.selections.append(["topRight", col_cell, col_cell])
7985 self.selections.append(["bottomLeft", row_cell, row_cell])
7986 self.selections.append(["bottomRight", active_cell, sqref])
7988 elif col:
7989 active_pane = "topRight"
7990 self.selections.append(["topRight", active_cell, sqref])
7992 else:
7993 active_pane = "bottomLeft"
7994 self.selections.append(["bottomLeft", active_cell, sqref])
7996 # Format splits to the same precision as Excel.
7997 if x_split:
7998 attributes.append(("xSplit", f"{x_split:.16g}"))
8000 if y_split:
8001 attributes.append(("ySplit", f"{y_split:.16g}"))
8003 attributes.append(("topLeftCell", top_left_cell))
8005 if has_selection:
8006 attributes.append(("activePane", active_pane))
8008 self._xml_empty_tag("pane", attributes)
8010 def _calculate_x_split_width(self, width):
8011 # Convert column width from user units to pane split width.
8013 max_digit_width = 7 # For Calabri 11.
8014 padding = 5
8016 # Convert to pixels.
8017 if width < 1:
8018 pixels = int(width * (max_digit_width + padding) + 0.5)
8019 else:
8020 pixels = int(width * max_digit_width + 0.5) + padding
8022 # Convert to points.
8023 points = pixels * 3 / 4
8025 # Convert to twips (twentieths of a point).
8026 twips = points * 20
8028 # Add offset/padding.
8029 width = twips + 390
8031 return width
8033 def _write_table_parts(self) -> None:
8034 # Write the <tableParts> element.
8035 tables = self.tables
8036 count = len(tables)
8038 # Return if worksheet doesn't contain any tables.
8039 if not count:
8040 return
8042 attributes = [
8043 (
8044 "count",
8045 count,
8046 )
8047 ]
8049 self._xml_start_tag("tableParts", attributes)
8051 for _ in tables:
8052 # Write the tablePart element.
8053 self.rel_count += 1
8054 self._write_table_part(self.rel_count)
8056 self._xml_end_tag("tableParts")
8058 def _write_table_part(self, r_id) -> None:
8059 # Write the <tablePart> element.
8061 r_id = "rId" + str(r_id)
8063 attributes = [
8064 (
8065 "r:id",
8066 r_id,
8067 )
8068 ]
8070 self._xml_empty_tag("tablePart", attributes)
8072 def _write_ext_list(self) -> None:
8073 # Write the <extLst> element for data bars and sparklines.
8074 has_data_bars = len(self.data_bars_2010)
8075 has_sparklines = len(self.sparklines)
8077 if not has_data_bars and not has_sparklines:
8078 return
8080 # Write the extLst element.
8081 self._xml_start_tag("extLst")
8083 if has_data_bars:
8084 self._write_ext_list_data_bars()
8086 if has_sparklines:
8087 self._write_ext_list_sparklines()
8089 self._xml_end_tag("extLst")
8091 def _write_ext_list_data_bars(self) -> None:
8092 # Write the Excel 2010 data_bar subelements.
8093 self._write_ext("{78C0D931-6437-407d-A8EE-F0AAD7539E65}")
8095 self._xml_start_tag("x14:conditionalFormattings")
8097 # Write the Excel 2010 conditional formatting data bar elements.
8098 for data_bar in self.data_bars_2010:
8099 # Write the x14:conditionalFormatting element.
8100 self._write_conditional_formatting_2010(data_bar)
8102 self._xml_end_tag("x14:conditionalFormattings")
8103 self._xml_end_tag("ext")
8105 def _write_conditional_formatting_2010(self, data_bar) -> None:
8106 # Write the <x14:conditionalFormatting> element.
8107 xmlns_xm = "http://schemas.microsoft.com/office/excel/2006/main"
8109 attributes = [("xmlns:xm", xmlns_xm)]
8111 self._xml_start_tag("x14:conditionalFormatting", attributes)
8113 # Write the x14:cfRule element.
8114 self._write_x14_cf_rule(data_bar)
8116 # Write the x14:dataBar element.
8117 self._write_x14_data_bar(data_bar)
8119 # Write the x14 max and min data bars.
8120 self._write_x14_cfvo(data_bar["x14_min_type"], data_bar["min_value"])
8121 self._write_x14_cfvo(data_bar["x14_max_type"], data_bar["max_value"])
8123 if not data_bar["bar_no_border"]:
8124 # Write the x14:borderColor element.
8125 self._write_x14_border_color(data_bar["bar_border_color"])
8127 # Write the x14:negativeFillColor element.
8128 if not data_bar["bar_negative_color_same"]:
8129 self._write_x14_negative_fill_color(data_bar["bar_negative_color"])
8131 # Write the x14:negativeBorderColor element.
8132 if (
8133 not data_bar["bar_no_border"]
8134 and not data_bar["bar_negative_border_color_same"]
8135 ):
8136 self._write_x14_negative_border_color(data_bar["bar_negative_border_color"])
8138 # Write the x14:axisColor element.
8139 if data_bar["bar_axis_position"] != "none":
8140 self._write_x14_axis_color(data_bar["bar_axis_color"])
8142 self._xml_end_tag("x14:dataBar")
8143 self._xml_end_tag("x14:cfRule")
8145 # Write the xm:sqref element.
8146 self._xml_data_element("xm:sqref", data_bar["range"])
8148 self._xml_end_tag("x14:conditionalFormatting")
8150 def _write_x14_cf_rule(self, data_bar) -> None:
8151 # Write the <x14:cfRule> element.
8152 rule_type = "dataBar"
8153 guid = data_bar["guid"]
8154 attributes = [("type", rule_type), ("id", guid)]
8156 self._xml_start_tag("x14:cfRule", attributes)
8158 def _write_x14_data_bar(self, data_bar) -> None:
8159 # Write the <x14:dataBar> element.
8160 min_length = 0
8161 max_length = 100
8163 attributes = [
8164 ("minLength", min_length),
8165 ("maxLength", max_length),
8166 ]
8168 if not data_bar["bar_no_border"]:
8169 attributes.append(("border", 1))
8171 if data_bar["bar_solid"]:
8172 attributes.append(("gradient", 0))
8174 if data_bar["bar_direction"] == "left":
8175 attributes.append(("direction", "leftToRight"))
8177 if data_bar["bar_direction"] == "right":
8178 attributes.append(("direction", "rightToLeft"))
8180 if data_bar["bar_negative_color_same"]:
8181 attributes.append(("negativeBarColorSameAsPositive", 1))
8183 if (
8184 not data_bar["bar_no_border"]
8185 and not data_bar["bar_negative_border_color_same"]
8186 ):
8187 attributes.append(("negativeBarBorderColorSameAsPositive", 0))
8189 if data_bar["bar_axis_position"] == "middle":
8190 attributes.append(("axisPosition", "middle"))
8192 if data_bar["bar_axis_position"] == "none":
8193 attributes.append(("axisPosition", "none"))
8195 self._xml_start_tag("x14:dataBar", attributes)
8197 def _write_x14_cfvo(self, rule_type, value) -> None:
8198 # Write the <x14:cfvo> element.
8199 attributes = [("type", rule_type)]
8201 if rule_type in ("min", "max", "autoMin", "autoMax"):
8202 self._xml_empty_tag("x14:cfvo", attributes)
8203 else:
8204 self._xml_start_tag("x14:cfvo", attributes)
8205 self._xml_data_element("xm:f", value)
8206 self._xml_end_tag("x14:cfvo")
8208 def _write_x14_border_color(self, color) -> None:
8209 # Write the <x14:borderColor> element.
8210 self._write_color("x14:borderColor", color._attributes())
8212 def _write_x14_negative_fill_color(self, color) -> None:
8213 # Write the <x14:negativeFillColor> element.
8214 self._xml_empty_tag("x14:negativeFillColor", color._attributes())
8216 def _write_x14_negative_border_color(self, color) -> None:
8217 # Write the <x14:negativeBorderColor> element.
8218 self._xml_empty_tag("x14:negativeBorderColor", color._attributes())
8220 def _write_x14_axis_color(self, color) -> None:
8221 # Write the <x14:axisColor> element.
8222 self._xml_empty_tag("x14:axisColor", color._attributes())
8224 def _write_ext_list_sparklines(self) -> None:
8225 # Write the sparkline extension sub-elements.
8226 self._write_ext("{05C60535-1F16-4fd2-B633-F4F36F0B64E0}")
8228 # Write the x14:sparklineGroups element.
8229 self._write_sparkline_groups()
8231 # Write the sparkline elements.
8232 for sparkline in reversed(self.sparklines):
8233 # Write the x14:sparklineGroup element.
8234 self._write_sparkline_group(sparkline)
8236 # Write the x14:colorSeries element.
8237 self._write_color_series(sparkline["series_color"])
8239 # Write the x14:colorNegative element.
8240 self._write_color_negative(sparkline["negative_color"])
8242 # Write the x14:colorAxis element.
8243 self._write_color_axis()
8245 # Write the x14:colorMarkers element.
8246 self._write_color_markers(sparkline["markers_color"])
8248 # Write the x14:colorFirst element.
8249 self._write_color_first(sparkline["first_color"])
8251 # Write the x14:colorLast element.
8252 self._write_color_last(sparkline["last_color"])
8254 # Write the x14:colorHigh element.
8255 self._write_color_high(sparkline["high_color"])
8257 # Write the x14:colorLow element.
8258 self._write_color_low(sparkline["low_color"])
8260 if sparkline["date_axis"]:
8261 self._xml_data_element("xm:f", sparkline["date_axis"])
8263 self._write_sparklines(sparkline)
8265 self._xml_end_tag("x14:sparklineGroup")
8267 self._xml_end_tag("x14:sparklineGroups")
8268 self._xml_end_tag("ext")
8270 def _write_sparklines(self, sparkline) -> None:
8271 # Write the <x14:sparklines> element and <x14:sparkline> sub-elements.
8273 # Write the sparkline elements.
8274 self._xml_start_tag("x14:sparklines")
8276 for i in range(sparkline["count"]):
8277 spark_range = sparkline["ranges"][i]
8278 location = sparkline["locations"][i]
8280 self._xml_start_tag("x14:sparkline")
8281 self._xml_data_element("xm:f", spark_range)
8282 self._xml_data_element("xm:sqref", location)
8283 self._xml_end_tag("x14:sparkline")
8285 self._xml_end_tag("x14:sparklines")
8287 def _write_ext(self, uri) -> None:
8288 # Write the <ext> element.
8289 schema = "http://schemas.microsoft.com/office/"
8290 xmlns_x14 = schema + "spreadsheetml/2009/9/main"
8292 attributes = [
8293 ("xmlns:x14", xmlns_x14),
8294 ("uri", uri),
8295 ]
8297 self._xml_start_tag("ext", attributes)
8299 def _write_sparkline_groups(self) -> None:
8300 # Write the <x14:sparklineGroups> element.
8301 xmlns_xm = "http://schemas.microsoft.com/office/excel/2006/main"
8303 attributes = [("xmlns:xm", xmlns_xm)]
8305 self._xml_start_tag("x14:sparklineGroups", attributes)
8307 def _write_sparkline_group(self, options) -> None:
8308 # Write the <x14:sparklineGroup> element.
8309 #
8310 # Example for order.
8311 #
8312 # <x14:sparklineGroup
8313 # manualMax="0"
8314 # manualMin="0"
8315 # lineWeight="2.25"
8316 # type="column"
8317 # dateAxis="1"
8318 # displayEmptyCellsAs="span"
8319 # markers="1"
8320 # high="1"
8321 # low="1"
8322 # first="1"
8323 # last="1"
8324 # negative="1"
8325 # displayXAxis="1"
8326 # displayHidden="1"
8327 # minAxisType="custom"
8328 # maxAxisType="custom"
8329 # rightToLeft="1">
8330 #
8331 empty = options.get("empty")
8332 attributes = []
8334 if options.get("max") is not None:
8335 if options["max"] == "group":
8336 options["cust_max"] = "group"
8337 else:
8338 attributes.append(("manualMax", options["max"]))
8339 options["cust_max"] = "custom"
8341 if options.get("min") is not None:
8342 if options["min"] == "group":
8343 options["cust_min"] = "group"
8344 else:
8345 attributes.append(("manualMin", options["min"]))
8346 options["cust_min"] = "custom"
8348 # Ignore the default type attribute (line).
8349 if options["type"] != "line":
8350 attributes.append(("type", options["type"]))
8352 if options.get("weight"):
8353 attributes.append(("lineWeight", options["weight"]))
8355 if options.get("date_axis"):
8356 attributes.append(("dateAxis", 1))
8358 if empty:
8359 attributes.append(("displayEmptyCellsAs", empty))
8361 if options.get("markers"):
8362 attributes.append(("markers", 1))
8364 if options.get("high"):
8365 attributes.append(("high", 1))
8367 if options.get("low"):
8368 attributes.append(("low", 1))
8370 if options.get("first"):
8371 attributes.append(("first", 1))
8373 if options.get("last"):
8374 attributes.append(("last", 1))
8376 if options.get("negative"):
8377 attributes.append(("negative", 1))
8379 if options.get("axis"):
8380 attributes.append(("displayXAxis", 1))
8382 if options.get("hidden"):
8383 attributes.append(("displayHidden", 1))
8385 if options.get("cust_min"):
8386 attributes.append(("minAxisType", options["cust_min"]))
8388 if options.get("cust_max"):
8389 attributes.append(("maxAxisType", options["cust_max"]))
8391 if options.get("reverse"):
8392 attributes.append(("rightToLeft", 1))
8394 self._xml_start_tag("x14:sparklineGroup", attributes)
8396 def _write_spark_color(self, tag, color) -> None:
8397 # Helper function for the sparkline color functions below.
8398 if color:
8399 self._write_color(tag, color._attributes())
8401 def _write_color_series(self, color) -> None:
8402 # Write the <x14:colorSeries> element.
8403 self._write_spark_color("x14:colorSeries", color)
8405 def _write_color_negative(self, color) -> None:
8406 # Write the <x14:colorNegative> element.
8407 self._write_spark_color("x14:colorNegative", color)
8409 def _write_color_axis(self) -> None:
8410 # Write the <x14:colorAxis> element.
8411 self._write_spark_color("x14:colorAxis", Color("#000000"))
8413 def _write_color_markers(self, color) -> None:
8414 # Write the <x14:colorMarkers> element.
8415 self._write_spark_color("x14:colorMarkers", color)
8417 def _write_color_first(self, color) -> None:
8418 # Write the <x14:colorFirst> element.
8419 self._write_spark_color("x14:colorFirst", color)
8421 def _write_color_last(self, color) -> None:
8422 # Write the <x14:colorLast> element.
8423 self._write_spark_color("x14:colorLast", color)
8425 def _write_color_high(self, color) -> None:
8426 # Write the <x14:colorHigh> element.
8427 self._write_spark_color("x14:colorHigh", color)
8429 def _write_color_low(self, color) -> None:
8430 # Write the <x14:colorLow> element.
8431 self._write_spark_color("x14:colorLow", color)
8433 def _write_phonetic_pr(self) -> None:
8434 # Write the <phoneticPr> element.
8435 attributes = [
8436 ("fontId", "0"),
8437 ("type", "noConversion"),
8438 ]
8440 self._xml_empty_tag("phoneticPr", attributes)
8442 def _write_ignored_errors(self) -> None:
8443 # Write the <ignoredErrors> element.
8444 if not self.ignored_errors:
8445 return
8447 self._xml_start_tag("ignoredErrors")
8449 if self.ignored_errors.get("number_stored_as_text"):
8450 ignored_range = self.ignored_errors["number_stored_as_text"]
8451 self._write_ignored_error("numberStoredAsText", ignored_range)
8453 if self.ignored_errors.get("eval_error"):
8454 ignored_range = self.ignored_errors["eval_error"]
8455 self._write_ignored_error("evalError", ignored_range)
8457 if self.ignored_errors.get("formula_differs"):
8458 ignored_range = self.ignored_errors["formula_differs"]
8459 self._write_ignored_error("formula", ignored_range)
8461 if self.ignored_errors.get("formula_range"):
8462 ignored_range = self.ignored_errors["formula_range"]
8463 self._write_ignored_error("formulaRange", ignored_range)
8465 if self.ignored_errors.get("formula_unlocked"):
8466 ignored_range = self.ignored_errors["formula_unlocked"]
8467 self._write_ignored_error("unlockedFormula", ignored_range)
8469 if self.ignored_errors.get("empty_cell_reference"):
8470 ignored_range = self.ignored_errors["empty_cell_reference"]
8471 self._write_ignored_error("emptyCellReference", ignored_range)
8473 if self.ignored_errors.get("list_data_validation"):
8474 ignored_range = self.ignored_errors["list_data_validation"]
8475 self._write_ignored_error("listDataValidation", ignored_range)
8477 if self.ignored_errors.get("calculated_column"):
8478 ignored_range = self.ignored_errors["calculated_column"]
8479 self._write_ignored_error("calculatedColumn", ignored_range)
8481 if self.ignored_errors.get("two_digit_text_year"):
8482 ignored_range = self.ignored_errors["two_digit_text_year"]
8483 self._write_ignored_error("twoDigitTextYear", ignored_range)
8485 self._xml_end_tag("ignoredErrors")
8487 def _write_ignored_error(self, error_type, ignored_range) -> None:
8488 # Write the <ignoredError> element.
8489 attributes = [
8490 ("sqref", ignored_range),
8491 (error_type, 1),
8492 ]
8494 self._xml_empty_tag("ignoredError", attributes)