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 # Check that row and col are valid without storing the values.
2549 if self._check_dimensions(first_row, first_col, True, True):
2550 return -1
2551 if self._check_dimensions(last_row, last_col, True, True):
2552 return -1
2554 # Build up the autofilter area range "Sheet1!$A$1:$C$13".
2555 area = self._convert_name_area(first_row, first_col, last_row, last_col)
2556 ref = xl_range(first_row, first_col, last_row, last_col)
2558 self.autofilter_area = area
2559 self.autofilter_ref = ref
2560 self.filter_range = [first_col, last_col]
2562 # Store the filter cell positions for use in the autofit calculation.
2563 for col in range(first_col, last_col + 1):
2564 # Check that the autofilter doesn't overlap a table filter.
2565 if self.filter_cells.get((first_row, col)):
2566 filter_type, filter_range = self.filter_cells.get((first_row, col))
2567 if filter_type == "table":
2568 raise OverlappingRange(
2569 f"Worksheet autofilter range '{ref}' overlaps previous "
2570 f"Table autofilter range '{filter_range}'."
2571 )
2573 self.filter_cells[(first_row, col)] = ("worksheet", ref)
2575 return 0
2577 def filter_column(self, col: int, criteria: str) -> None:
2578 """
2579 Set the column filter criteria.
2581 Args:
2582 col: Filter column (zero-indexed).
2583 criteria: Filter criteria.
2585 Returns:
2586 Nothing.
2588 """
2589 if not self.autofilter_area:
2590 warn("Must call autofilter() before filter_column()")
2591 return
2593 # Check for a column reference in A1 notation and substitute.
2594 try:
2595 int(col)
2596 except ValueError:
2597 # Convert col ref to a cell ref and then to a col number.
2598 col_letter = col
2599 _, col = xl_cell_to_rowcol(col + "1")
2601 if col >= self.xls_colmax:
2602 warn(f"Invalid column '{col_letter}'")
2603 return
2605 col_first, col_last = self.filter_range
2607 # Reject column if it is outside filter range.
2608 if col < col_first or col > col_last:
2609 warn(
2610 f"Column '{col}' outside autofilter() column "
2611 f"range ({col_first}, {col_last})"
2612 )
2613 return
2615 tokens = self._extract_filter_tokens(criteria)
2617 if len(tokens) not in (3, 7):
2618 warn(f"Incorrect number of tokens in criteria '{criteria}'")
2620 tokens = self._parse_filter_expression(criteria, tokens)
2622 # Excel handles single or double custom filters as default filters.
2623 # We need to check for them and handle them accordingly.
2624 if len(tokens) == 2 and tokens[0] == 2:
2625 # Single equality.
2626 self.filter_column_list(col, [tokens[1]])
2627 elif len(tokens) == 5 and tokens[0] == 2 and tokens[2] == 1 and tokens[3] == 2:
2628 # Double equality with "or" operator.
2629 self.filter_column_list(col, [tokens[1], tokens[4]])
2630 else:
2631 # Non default custom filter.
2632 self.filter_cols[col] = tokens
2633 self.filter_type[col] = 0
2635 self.filter_on = 1
2637 def filter_column_list(self, col: int, filters: List[str]) -> None:
2638 """
2639 Set the column filter criteria in Excel 2007 list style.
2641 Args:
2642 col: Filter column (zero-indexed).
2643 filters: List of filter criteria to match.
2645 Returns:
2646 Nothing.
2648 """
2649 if not self.autofilter_area:
2650 warn("Must call autofilter() before filter_column()")
2651 return
2653 # Check for a column reference in A1 notation and substitute.
2654 try:
2655 int(col)
2656 except ValueError:
2657 # Convert col ref to a cell ref and then to a col number.
2658 col_letter = col
2659 _, col = xl_cell_to_rowcol(col + "1")
2661 if col >= self.xls_colmax:
2662 warn(f"Invalid column '{col_letter}'")
2663 return
2665 col_first, col_last = self.filter_range
2667 # Reject column if it is outside filter range.
2668 if col < col_first or col > col_last:
2669 warn(
2670 f"Column '{col}' outside autofilter() column range "
2671 f"({col_first},{col_last})"
2672 )
2673 return
2675 self.filter_cols[col] = filters
2676 self.filter_type[col] = 1
2677 self.filter_on = 1
2679 @convert_range_args
2680 def data_validation(
2681 self,
2682 first_row: int,
2683 first_col: int,
2684 last_row: int,
2685 last_col: int,
2686 options: Optional[Dict[str, Any]] = None,
2687 ) -> Literal[0, -1, -2]:
2688 """
2689 Add a data validation to a worksheet.
2691 Args:
2692 first_row: The first row of the cell range. (zero indexed).
2693 first_col: The first column of the cell range.
2694 last_row: The last row of the cell range. (zero indexed).
2695 last_col: The last column of the cell range.
2696 options: Data validation options.
2698 Returns:
2699 0: Success.
2700 -1: Row or column is out of worksheet bounds.
2701 -2: Incorrect parameter or option.
2702 """
2703 # Check that row and col are valid without storing the values.
2704 if self._check_dimensions(first_row, first_col, True, True):
2705 return -1
2706 if self._check_dimensions(last_row, last_col, True, True):
2707 return -1
2709 if options is None:
2710 options = {}
2711 else:
2712 # Copy the user defined options so they aren't modified.
2713 options = options.copy()
2715 # Valid input parameters.
2716 valid_parameters = {
2717 "validate",
2718 "criteria",
2719 "value",
2720 "source",
2721 "minimum",
2722 "maximum",
2723 "ignore_blank",
2724 "dropdown",
2725 "show_input",
2726 "input_title",
2727 "input_message",
2728 "show_error",
2729 "error_title",
2730 "error_message",
2731 "error_type",
2732 "other_cells",
2733 "multi_range",
2734 }
2736 # Check for valid input parameters.
2737 for param_key in options.keys():
2738 if param_key not in valid_parameters:
2739 warn(f"Unknown parameter '{param_key}' in data_validation()")
2740 return -2
2742 # Map alternative parameter names 'source' or 'minimum' to 'value'.
2743 if "source" in options:
2744 options["value"] = options["source"]
2745 if "minimum" in options:
2746 options["value"] = options["minimum"]
2748 # 'validate' is a required parameter.
2749 if "validate" not in options:
2750 warn("Parameter 'validate' is required in data_validation()")
2751 return -2
2753 # List of valid validation types.
2754 valid_types = {
2755 "any": "none",
2756 "any value": "none",
2757 "whole number": "whole",
2758 "whole": "whole",
2759 "integer": "whole",
2760 "decimal": "decimal",
2761 "list": "list",
2762 "date": "date",
2763 "time": "time",
2764 "text length": "textLength",
2765 "length": "textLength",
2766 "custom": "custom",
2767 }
2769 # Check for valid validation types.
2770 if options["validate"] not in valid_types:
2771 warn(
2772 f"Unknown validation type '{options['validate']}' for parameter "
2773 f"'validate' in data_validation()"
2774 )
2775 return -2
2777 options["validate"] = valid_types[options["validate"]]
2779 # No action is required for validation type 'any' if there are no
2780 # input messages to display.
2781 if (
2782 options["validate"] == "none"
2783 and options.get("input_title") is None
2784 and options.get("input_message") is None
2785 ):
2786 return -2
2788 # The any, list and custom validations don't have a criteria so we use
2789 # a default of 'between'.
2790 if (
2791 options["validate"] == "none"
2792 or options["validate"] == "list"
2793 or options["validate"] == "custom"
2794 ):
2795 options["criteria"] = "between"
2796 options["maximum"] = None
2798 # 'criteria' is a required parameter.
2799 if "criteria" not in options:
2800 warn("Parameter 'criteria' is required in data_validation()")
2801 return -2
2803 # Valid criteria types.
2804 criteria_types = {
2805 "between": "between",
2806 "not between": "notBetween",
2807 "equal to": "equal",
2808 "=": "equal",
2809 "==": "equal",
2810 "not equal to": "notEqual",
2811 "!=": "notEqual",
2812 "<>": "notEqual",
2813 "greater than": "greaterThan",
2814 ">": "greaterThan",
2815 "less than": "lessThan",
2816 "<": "lessThan",
2817 "greater than or equal to": "greaterThanOrEqual",
2818 ">=": "greaterThanOrEqual",
2819 "less than or equal to": "lessThanOrEqual",
2820 "<=": "lessThanOrEqual",
2821 }
2823 # Check for valid criteria types.
2824 if options["criteria"] not in criteria_types:
2825 warn(
2826 f"Unknown criteria type '{options['criteria']}' for parameter "
2827 f"'criteria' in data_validation()"
2828 )
2829 return -2
2831 options["criteria"] = criteria_types[options["criteria"]]
2833 # 'Between' and 'Not between' criteria require 2 values.
2834 if options["criteria"] == "between" or options["criteria"] == "notBetween":
2835 if "maximum" not in options:
2836 warn(
2837 "Parameter 'maximum' is required in data_validation() "
2838 "when using 'between' or 'not between' criteria"
2839 )
2840 return -2
2841 else:
2842 options["maximum"] = None
2844 # Valid error dialog types.
2845 error_types = {
2846 "stop": 0,
2847 "warning": 1,
2848 "information": 2,
2849 }
2851 # Check for valid error dialog types.
2852 if "error_type" not in options:
2853 options["error_type"] = 0
2854 elif options["error_type"] not in error_types:
2855 warn(
2856 f"Unknown criteria type '{options['error_type']}' "
2857 f"for parameter 'error_type'."
2858 )
2859 return -2
2860 else:
2861 options["error_type"] = error_types[options["error_type"]]
2863 # Convert date/times value if required.
2864 if (
2865 options["validate"] in ("date", "time")
2866 and options["value"]
2867 and _supported_datetime(options["value"])
2868 ):
2869 date_time = self._convert_date_time(options["value"])
2870 # Format date number to the same precision as Excel.
2871 options["value"] = f"{date_time:.16g}"
2873 if options["maximum"] and _supported_datetime(options["maximum"]):
2874 date_time = self._convert_date_time(options["maximum"])
2875 options["maximum"] = f"{date_time:.16g}"
2877 # Check that the input title doesn't exceed the maximum length.
2878 if options.get("input_title") and len(options["input_title"]) > 32:
2879 warn(
2880 f"Length of input title '{options['input_title']}' "
2881 f"exceeds Excel's limit of 32"
2882 )
2883 return -2
2885 # Check that the error title doesn't exceed the maximum length.
2886 if options.get("error_title") and len(options["error_title"]) > 32:
2887 warn(
2888 f"Length of error title '{options['error_title']}' "
2889 f"exceeds Excel's limit of 32"
2890 )
2891 return -2
2893 # Check that the input message doesn't exceed the maximum length.
2894 if options.get("input_message") and len(options["input_message"]) > 255:
2895 warn(
2896 f"Length of input message '{options['input_message']}' "
2897 f"exceeds Excel's limit of 255"
2898 )
2899 return -2
2901 # Check that the error message doesn't exceed the maximum length.
2902 if options.get("error_message") and len(options["error_message"]) > 255:
2903 warn(
2904 f"Length of error message '{options['error_message']}' "
2905 f"exceeds Excel's limit of 255"
2906 )
2907 return -2
2909 # Check that the input list doesn't exceed the maximum length.
2910 if options["validate"] == "list" and isinstance(options["value"], list):
2911 formula = self._csv_join(*options["value"])
2912 if len(formula) > 255:
2913 warn(
2914 f"Length of list items '{formula}' exceeds Excel's limit of "
2915 f"255, use a formula range instead"
2916 )
2917 return -2
2919 # Set some defaults if they haven't been defined by the user.
2920 if "ignore_blank" not in options:
2921 options["ignore_blank"] = 1
2922 if "dropdown" not in options:
2923 options["dropdown"] = 1
2924 if "show_input" not in options:
2925 options["show_input"] = 1
2926 if "show_error" not in options:
2927 options["show_error"] = 1
2929 # These are the cells to which the validation is applied.
2930 options["cells"] = [[first_row, first_col, last_row, last_col]]
2932 # A (for now) undocumented parameter to pass additional cell ranges.
2933 if "other_cells" in options:
2934 options["cells"].extend(options["other_cells"])
2936 # Override with user defined multiple range if provided.
2937 if "multi_range" in options:
2938 options["multi_range"] = options["multi_range"].replace("$", "")
2940 # Store the validation information until we close the worksheet.
2941 self.validations.append(options)
2943 return 0
2945 @convert_range_args
2946 def conditional_format(
2947 self,
2948 first_row: int,
2949 first_col: int,
2950 last_row: int,
2951 last_col: int,
2952 options: Optional[Dict[str, Any]] = None,
2953 ) -> Literal[0, -1, -2]:
2954 """
2955 Add a conditional format to a worksheet.
2957 Args:
2958 first_row: The first row of the cell range. (zero indexed).
2959 first_col: The first column of the cell range.
2960 last_row: The last row of the cell range. (zero indexed).
2961 last_col: The last column of the cell range.
2962 options: Conditional format options.
2964 Returns:
2965 0: Success.
2966 -1: Row or column is out of worksheet bounds.
2967 -2: Incorrect parameter or option.
2968 """
2969 # Check that row and col are valid without storing the values.
2970 if self._check_dimensions(first_row, first_col, True, True):
2971 return -1
2972 if self._check_dimensions(last_row, last_col, True, True):
2973 return -1
2975 if options is None:
2976 options = {}
2977 else:
2978 # Copy the user defined options so they aren't modified.
2979 options = options.copy()
2981 # Valid input parameters.
2982 valid_parameter = {
2983 "type",
2984 "format",
2985 "criteria",
2986 "value",
2987 "minimum",
2988 "maximum",
2989 "stop_if_true",
2990 "min_type",
2991 "mid_type",
2992 "max_type",
2993 "min_value",
2994 "mid_value",
2995 "max_value",
2996 "min_color",
2997 "mid_color",
2998 "max_color",
2999 "min_length",
3000 "max_length",
3001 "multi_range",
3002 "bar_color",
3003 "bar_negative_color",
3004 "bar_negative_color_same",
3005 "bar_solid",
3006 "bar_border_color",
3007 "bar_negative_border_color",
3008 "bar_negative_border_color_same",
3009 "bar_no_border",
3010 "bar_direction",
3011 "bar_axis_position",
3012 "bar_axis_color",
3013 "bar_only",
3014 "data_bar_2010",
3015 "icon_style",
3016 "reverse_icons",
3017 "icons_only",
3018 "icons",
3019 }
3021 # Check for valid input parameters.
3022 for param_key in options.keys():
3023 if param_key not in valid_parameter:
3024 warn(f"Unknown parameter '{param_key}' in conditional_format()")
3025 return -2
3027 # 'type' is a required parameter.
3028 if "type" not in options:
3029 warn("Parameter 'type' is required in conditional_format()")
3030 return -2
3032 # Valid types.
3033 valid_type = {
3034 "cell": "cellIs",
3035 "date": "date",
3036 "time": "time",
3037 "average": "aboveAverage",
3038 "duplicate": "duplicateValues",
3039 "unique": "uniqueValues",
3040 "top": "top10",
3041 "bottom": "top10",
3042 "text": "text",
3043 "time_period": "timePeriod",
3044 "blanks": "containsBlanks",
3045 "no_blanks": "notContainsBlanks",
3046 "errors": "containsErrors",
3047 "no_errors": "notContainsErrors",
3048 "2_color_scale": "2_color_scale",
3049 "3_color_scale": "3_color_scale",
3050 "data_bar": "dataBar",
3051 "formula": "expression",
3052 "icon_set": "iconSet",
3053 }
3055 # Check for valid types.
3056 if options["type"] not in valid_type:
3057 warn(
3058 f"Unknown value '{options['type']}' for parameter 'type' "
3059 f"in conditional_format()"
3060 )
3061 return -2
3063 if options["type"] == "bottom":
3064 options["direction"] = "bottom"
3065 options["type"] = valid_type[options["type"]]
3067 # Valid criteria types.
3068 criteria_type = {
3069 "between": "between",
3070 "not between": "notBetween",
3071 "equal to": "equal",
3072 "=": "equal",
3073 "==": "equal",
3074 "not equal to": "notEqual",
3075 "!=": "notEqual",
3076 "<>": "notEqual",
3077 "greater than": "greaterThan",
3078 ">": "greaterThan",
3079 "less than": "lessThan",
3080 "<": "lessThan",
3081 "greater than or equal to": "greaterThanOrEqual",
3082 ">=": "greaterThanOrEqual",
3083 "less than or equal to": "lessThanOrEqual",
3084 "<=": "lessThanOrEqual",
3085 "containing": "containsText",
3086 "not containing": "notContains",
3087 "begins with": "beginsWith",
3088 "ends with": "endsWith",
3089 "yesterday": "yesterday",
3090 "today": "today",
3091 "last 7 days": "last7Days",
3092 "last week": "lastWeek",
3093 "this week": "thisWeek",
3094 "next week": "nextWeek",
3095 "last month": "lastMonth",
3096 "this month": "thisMonth",
3097 "next month": "nextMonth",
3098 # For legacy, but incorrect, support.
3099 "continue week": "nextWeek",
3100 "continue month": "nextMonth",
3101 }
3103 # Check for valid criteria types.
3104 if "criteria" in options and options["criteria"] in criteria_type:
3105 options["criteria"] = criteria_type[options["criteria"]]
3107 # Convert boolean values if required.
3108 if "value" in options and isinstance(options["value"], bool):
3109 options["value"] = str(options["value"]).upper()
3111 # Convert date/times value if required.
3112 if options["type"] in ("date", "time"):
3113 options["type"] = "cellIs"
3115 if "value" in options:
3116 if not _supported_datetime(options["value"]):
3117 warn("Conditional format 'value' must be a datetime object.")
3118 return -2
3120 date_time = self._convert_date_time(options["value"])
3121 # Format date number to the same precision as Excel.
3122 options["value"] = f"{date_time:.16g}"
3124 if "minimum" in options:
3125 if not _supported_datetime(options["minimum"]):
3126 warn("Conditional format 'minimum' must be a datetime object.")
3127 return -2
3129 date_time = self._convert_date_time(options["minimum"])
3130 options["minimum"] = f"{date_time:.16g}"
3132 if "maximum" in options:
3133 if not _supported_datetime(options["maximum"]):
3134 warn("Conditional format 'maximum' must be a datetime object.")
3135 return -2
3137 date_time = self._convert_date_time(options["maximum"])
3138 options["maximum"] = f"{date_time:.16g}"
3140 # Valid icon styles.
3141 valid_icons = {
3142 "3_arrows": "3Arrows", # 1
3143 "3_flags": "3Flags", # 2
3144 "3_traffic_lights_rimmed": "3TrafficLights2", # 3
3145 "3_symbols_circled": "3Symbols", # 4
3146 "4_arrows": "4Arrows", # 5
3147 "4_red_to_black": "4RedToBlack", # 6
3148 "4_traffic_lights": "4TrafficLights", # 7
3149 "5_arrows_gray": "5ArrowsGray", # 8
3150 "5_quarters": "5Quarters", # 9
3151 "3_arrows_gray": "3ArrowsGray", # 10
3152 "3_traffic_lights": "3TrafficLights", # 11
3153 "3_signs": "3Signs", # 12
3154 "3_symbols": "3Symbols2", # 13
3155 "4_arrows_gray": "4ArrowsGray", # 14
3156 "4_ratings": "4Rating", # 15
3157 "5_arrows": "5Arrows", # 16
3158 "5_ratings": "5Rating",
3159 } # 17
3161 # Set the icon set properties.
3162 if options["type"] == "iconSet":
3163 # An icon_set must have an icon style.
3164 if not options.get("icon_style"):
3165 warn(
3166 "The 'icon_style' parameter must be specified when "
3167 "'type' == 'icon_set' in conditional_format()."
3168 )
3169 return -3
3171 # Check for valid icon styles.
3172 if options["icon_style"] not in valid_icons:
3173 warn(
3174 f"Unknown icon_style '{options['icon_style']}' "
3175 f"in conditional_format()."
3176 )
3177 return -2
3179 options["icon_style"] = valid_icons[options["icon_style"]]
3181 # Set the number of icons for the icon style.
3182 options["total_icons"] = 3
3183 if options["icon_style"].startswith("4"):
3184 options["total_icons"] = 4
3185 elif options["icon_style"].startswith("5"):
3186 options["total_icons"] = 5
3188 options["icons"] = self._set_icon_props(
3189 options.get("total_icons"), options.get("icons")
3190 )
3192 # Swap last row/col for first row/col as necessary
3193 if first_row > last_row:
3194 first_row, last_row = last_row, first_row
3196 if first_col > last_col:
3197 first_col, last_col = last_col, first_col
3199 # Set the formatting range.
3200 cell_range = xl_range(first_row, first_col, last_row, last_col)
3201 start_cell = xl_rowcol_to_cell(first_row, first_col)
3203 # Override with user defined multiple range if provided.
3204 if "multi_range" in options:
3205 cell_range = options["multi_range"]
3206 cell_range = cell_range.replace("$", "")
3208 # Get the dxf format index.
3209 if "format" in options and options["format"]:
3210 options["format"] = options["format"]._get_dxf_index()
3212 # Set the priority based on the order of adding.
3213 options["priority"] = self.dxf_priority
3214 self.dxf_priority += 1
3216 # Check for 2010 style data_bar parameters.
3217 # pylint: disable=too-many-boolean-expressions
3218 if (
3219 self.use_data_bars_2010
3220 or options.get("data_bar_2010")
3221 or options.get("bar_solid")
3222 or options.get("bar_border_color")
3223 or options.get("bar_negative_color")
3224 or options.get("bar_negative_color_same")
3225 or options.get("bar_negative_border_color")
3226 or options.get("bar_negative_border_color_same")
3227 or options.get("bar_no_border")
3228 or options.get("bar_axis_position")
3229 or options.get("bar_axis_color")
3230 or options.get("bar_direction")
3231 ):
3232 options["is_data_bar_2010"] = True
3234 # Special handling of text criteria.
3235 if options["type"] == "text":
3236 value = options["value"]
3237 length = len(value)
3238 criteria = options["criteria"]
3240 if options["criteria"] == "containsText":
3241 options["type"] = "containsText"
3242 options["formula"] = f'NOT(ISERROR(SEARCH("{value}",{start_cell})))'
3243 elif options["criteria"] == "notContains":
3244 options["type"] = "notContainsText"
3245 options["formula"] = f'ISERROR(SEARCH("{value}",{start_cell}))'
3246 elif options["criteria"] == "beginsWith":
3247 options["type"] = "beginsWith"
3248 options["formula"] = f'LEFT({start_cell},{length})="{value}"'
3249 elif options["criteria"] == "endsWith":
3250 options["type"] = "endsWith"
3251 options["formula"] = f'RIGHT({start_cell},{length})="{value}"'
3252 else:
3253 warn(f"Invalid text criteria '{criteria}' in conditional_format()")
3255 # Special handling of time time_period criteria.
3256 if options["type"] == "timePeriod":
3257 if options["criteria"] == "yesterday":
3258 options["formula"] = f"FLOOR({start_cell},1)=TODAY()-1"
3260 elif options["criteria"] == "today":
3261 options["formula"] = f"FLOOR({start_cell},1)=TODAY()"
3263 elif options["criteria"] == "tomorrow":
3264 options["formula"] = f"FLOOR({start_cell},1)=TODAY()+1"
3266 # fmt: off
3267 elif options["criteria"] == "last7Days":
3268 options["formula"] = (
3269 f"AND(TODAY()-FLOOR({start_cell},1)<=6,"
3270 f"FLOOR({start_cell},1)<=TODAY())"
3271 )
3272 # fmt: on
3274 elif options["criteria"] == "lastWeek":
3275 options["formula"] = (
3276 f"AND(TODAY()-ROUNDDOWN({start_cell},0)>=(WEEKDAY(TODAY())),"
3277 f"TODAY()-ROUNDDOWN({start_cell},0)<(WEEKDAY(TODAY())+7))"
3278 )
3280 elif options["criteria"] == "thisWeek":
3281 options["formula"] = (
3282 f"AND(TODAY()-ROUNDDOWN({start_cell},0)<=WEEKDAY(TODAY())-1,"
3283 f"ROUNDDOWN({start_cell},0)-TODAY()<=7-WEEKDAY(TODAY()))"
3284 )
3286 elif options["criteria"] == "nextWeek":
3287 options["formula"] = (
3288 f"AND(ROUNDDOWN({start_cell},0)-TODAY()>(7-WEEKDAY(TODAY())),"
3289 f"ROUNDDOWN({start_cell},0)-TODAY()<(15-WEEKDAY(TODAY())))"
3290 )
3292 elif options["criteria"] == "lastMonth":
3293 options["formula"] = (
3294 f"AND(MONTH({start_cell})=MONTH(TODAY())-1,"
3295 f"OR(YEAR({start_cell})=YEAR("
3296 f"TODAY()),AND(MONTH({start_cell})=1,YEAR(A1)=YEAR(TODAY())-1)))"
3297 )
3299 # fmt: off
3300 elif options["criteria"] == "thisMonth":
3301 options["formula"] = (
3302 f"AND(MONTH({start_cell})=MONTH(TODAY()),"
3303 f"YEAR({start_cell})=YEAR(TODAY()))"
3304 )
3305 # fmt: on
3307 elif options["criteria"] == "nextMonth":
3308 options["formula"] = (
3309 f"AND(MONTH({start_cell})=MONTH(TODAY())+1,"
3310 f"OR(YEAR({start_cell})=YEAR("
3311 f"TODAY()),AND(MONTH({start_cell})=12,"
3312 f"YEAR({start_cell})=YEAR(TODAY())+1)))"
3313 )
3315 else:
3316 warn(
3317 f"Invalid time_period criteria '{options['criteria']}' "
3318 f"in conditional_format()"
3319 )
3321 # Special handling of blanks/error types.
3322 if options["type"] == "containsBlanks":
3323 options["formula"] = f"LEN(TRIM({start_cell}))=0"
3325 if options["type"] == "notContainsBlanks":
3326 options["formula"] = f"LEN(TRIM({start_cell}))>0"
3328 if options["type"] == "containsErrors":
3329 options["formula"] = f"ISERROR({start_cell})"
3331 if options["type"] == "notContainsErrors":
3332 options["formula"] = f"NOT(ISERROR({start_cell}))"
3334 # Special handling for 2 color scale.
3335 if options["type"] == "2_color_scale":
3336 options["type"] = "colorScale"
3338 # Color scales don't use any additional formatting.
3339 options["format"] = None
3341 # Turn off 3 color parameters.
3342 options["mid_type"] = None
3343 options["mid_color"] = None
3345 options.setdefault("min_type", "min")
3346 options.setdefault("max_type", "max")
3347 options.setdefault("min_value", 0)
3348 options.setdefault("max_value", 0)
3349 options.setdefault("min_color", Color("#FF7128"))
3350 options.setdefault("max_color", Color("#FFEF9C"))
3352 options["min_color"] = Color._from_value(options["min_color"])
3353 options["max_color"] = Color._from_value(options["max_color"])
3355 # Special handling for 3 color scale.
3356 if options["type"] == "3_color_scale":
3357 options["type"] = "colorScale"
3359 # Color scales don't use any additional formatting.
3360 options["format"] = None
3362 options.setdefault("min_type", "min")
3363 options.setdefault("mid_type", "percentile")
3364 options.setdefault("max_type", "max")
3365 options.setdefault("min_value", 0)
3366 options.setdefault("max_value", 0)
3367 options.setdefault("min_color", Color("#F8696B"))
3368 options.setdefault("mid_color", Color("#FFEB84"))
3369 options.setdefault("max_color", Color("#63BE7B"))
3371 options["min_color"] = Color._from_value(options["min_color"])
3372 options["mid_color"] = Color._from_value(options["mid_color"])
3373 options["max_color"] = Color._from_value(options["max_color"])
3375 # Set a default mid value.
3376 if "mid_value" not in options:
3377 options["mid_value"] = 50
3379 # Special handling for data bar.
3380 if options["type"] == "dataBar":
3381 # Color scales don't use any additional formatting.
3382 options["format"] = None
3384 if not options.get("min_type"):
3385 options["min_type"] = "min"
3386 options["x14_min_type"] = "autoMin"
3387 else:
3388 options["x14_min_type"] = options["min_type"]
3390 if not options.get("max_type"):
3391 options["max_type"] = "max"
3392 options["x14_max_type"] = "autoMax"
3393 else:
3394 options["x14_max_type"] = options["max_type"]
3396 options.setdefault("min_value", 0)
3397 options.setdefault("max_value", 0)
3398 options.setdefault("bar_color", Color("#638EC6"))
3399 options.setdefault("bar_border_color", options["bar_color"])
3400 options.setdefault("bar_only", False)
3401 options.setdefault("bar_no_border", False)
3402 options.setdefault("bar_solid", False)
3403 options.setdefault("bar_direction", "")
3404 options.setdefault("bar_negative_color", Color("#FF0000"))
3405 options.setdefault("bar_negative_border_color", Color("#FF0000"))
3406 options.setdefault("bar_negative_color_same", False)
3407 options.setdefault("bar_negative_border_color_same", False)
3408 options.setdefault("bar_axis_position", "")
3409 options.setdefault("bar_axis_color", Color("#000000"))
3411 options["bar_color"] = Color._from_value(options["bar_color"])
3412 options["bar_border_color"] = Color._from_value(options["bar_border_color"])
3413 options["bar_axis_color"] = Color._from_value(options["bar_axis_color"])
3414 options["bar_negative_color"] = Color._from_value(
3415 options["bar_negative_color"]
3416 )
3417 options["bar_negative_border_color"] = Color._from_value(
3418 options["bar_negative_border_color"]
3419 )
3421 # Adjust for 2010 style data_bar parameters.
3422 if options.get("is_data_bar_2010"):
3423 self.excel_version = 2010
3425 if options["min_type"] == "min" and options["min_value"] == 0:
3426 options["min_value"] = None
3428 if options["max_type"] == "max" and options["max_value"] == 0:
3429 options["max_value"] = None
3431 options["range"] = cell_range
3433 # Strip the leading = from formulas.
3434 try:
3435 options["min_value"] = options["min_value"].lstrip("=")
3436 except (KeyError, AttributeError):
3437 pass
3438 try:
3439 options["mid_value"] = options["mid_value"].lstrip("=")
3440 except (KeyError, AttributeError):
3441 pass
3442 try:
3443 options["max_value"] = options["max_value"].lstrip("=")
3444 except (KeyError, AttributeError):
3445 pass
3447 # Store the conditional format until we close the worksheet.
3448 if cell_range in self.cond_formats:
3449 self.cond_formats[cell_range].append(options)
3450 else:
3451 self.cond_formats[cell_range] = [options]
3453 return 0
3455 @convert_range_args
3456 def add_table(
3457 self,
3458 first_row: int,
3459 first_col: int,
3460 last_row: int,
3461 last_col: int,
3462 options: Optional[Dict[str, Any]] = None,
3463 ) -> Literal[0, -1, -2, -3]:
3464 """
3465 Add an Excel table to a worksheet.
3467 Args:
3468 first_row: The first row of the cell range. (zero indexed).
3469 first_col: The first column of the cell range.
3470 last_row: The last row of the cell range. (zero indexed).
3471 last_col: The last column of the cell range.
3472 options: Table format options. (Optional)
3474 Returns:
3475 0: Success.
3476 -1: Row or column is out of worksheet bounds.
3477 -2: Incorrect parameter or option.
3478 -3: Not supported in constant_memory mode.
3479 """
3480 table = {}
3481 col_formats = {}
3483 if options is None:
3484 options = {}
3485 else:
3486 # Copy the user defined options so they aren't modified.
3487 options = options.copy()
3489 if self.constant_memory:
3490 warn("add_table() isn't supported in 'constant_memory' mode")
3491 return -3
3493 # Check that row and col are valid without storing the values.
3494 if self._check_dimensions(first_row, first_col, True, True):
3495 return -1
3496 if self._check_dimensions(last_row, last_col, True, True):
3497 return -1
3499 # Swap last row/col for first row/col as necessary.
3500 if first_row > last_row:
3501 first_row, last_row = (last_row, first_row)
3502 if first_col > last_col:
3503 first_col, last_col = (last_col, first_col)
3505 # Check if the table range overlaps a previous merged or table range.
3506 # This is a critical file corruption error in Excel.
3507 cell_range = xl_range(first_row, first_col, last_row, last_col)
3508 for row in range(first_row, last_row + 1):
3509 for col in range(first_col, last_col + 1):
3510 if self.table_cells.get((row, col)):
3511 previous_range = self.table_cells.get((row, col))
3512 raise OverlappingRange(
3513 f"Table range '{cell_range}' overlaps previous "
3514 f"table range '{previous_range}'."
3515 )
3517 if self.merged_cells.get((row, col)):
3518 previous_range = self.merged_cells.get((row, col))
3519 raise OverlappingRange(
3520 f"Table range '{cell_range}' overlaps previous "
3521 f"merge range '{previous_range}'."
3522 )
3524 self.table_cells[(row, col)] = cell_range
3526 # Valid input parameters.
3527 valid_parameter = {
3528 "autofilter",
3529 "banded_columns",
3530 "banded_rows",
3531 "columns",
3532 "data",
3533 "first_column",
3534 "header_row",
3535 "last_column",
3536 "name",
3537 "style",
3538 "total_row",
3539 "description",
3540 "title",
3541 }
3543 # Check for valid input parameters.
3544 for param_key in options.keys():
3545 if param_key not in valid_parameter:
3546 warn(f"Unknown parameter '{param_key}' in add_table()")
3547 return -2
3549 # Turn on Excel's defaults.
3550 options["banded_rows"] = options.get("banded_rows", True)
3551 options["header_row"] = options.get("header_row", True)
3552 options["autofilter"] = options.get("autofilter", True)
3554 # Check that there are enough rows.
3555 num_rows = last_row - first_row
3556 if options["header_row"]:
3557 num_rows -= 1
3559 if num_rows < 0:
3560 warn("Must have at least one data row in in add_table()")
3561 return -2
3563 # Set the table options.
3564 table["show_first_col"] = options.get("first_column", False)
3565 table["show_last_col"] = options.get("last_column", False)
3566 table["show_row_stripes"] = options.get("banded_rows", False)
3567 table["show_col_stripes"] = options.get("banded_columns", False)
3568 table["header_row_count"] = options.get("header_row", 0)
3569 table["totals_row_shown"] = options.get("total_row", False)
3570 table["description"] = options.get("description")
3571 table["title"] = options.get("title")
3573 # Set the table name.
3574 if "name" in options:
3575 name = options["name"]
3576 table["name"] = name
3578 if " " in name:
3579 warn(f"Name '{name}' in add_table() cannot contain spaces")
3580 return -2
3582 # Warn if the name contains invalid chars as defined by Excel.
3583 if not re.match(r"^[\w\\][\w\\.]*$", name, re.UNICODE) or re.match(
3584 r"^\d", name
3585 ):
3586 warn(f"Invalid Excel characters in add_table(): '{name}'")
3587 return -2
3589 # Warn if the name looks like a cell name.
3590 if re.match(r"^[a-zA-Z][a-zA-Z]?[a-dA-D]?\d+$", name):
3591 warn(f"Name looks like a cell name in add_table(): '{name}'")
3592 return -2
3594 # Warn if the name looks like a R1C1 cell reference.
3595 if re.match(r"^[rcRC]$", name) or re.match(r"^[rcRC]\d+[rcRC]\d+$", name):
3596 warn(f"Invalid name '{name}' like a RC cell ref in add_table()")
3597 return -2
3599 # Set the table style.
3600 if "style" in options:
3601 table["style"] = options["style"]
3603 if table["style"] is None:
3604 table["style"] = ""
3606 # Remove whitespace from style name.
3607 table["style"] = table["style"].replace(" ", "")
3608 else:
3609 table["style"] = "TableStyleMedium9"
3611 # Set the data range rows (without the header and footer).
3612 first_data_row = first_row
3613 last_data_row = last_row
3615 if options.get("header_row"):
3616 first_data_row += 1
3618 if options.get("total_row"):
3619 last_data_row -= 1
3621 # Set the table and autofilter ranges.
3622 table["range"] = xl_range(first_row, first_col, last_row, last_col)
3624 table["a_range"] = xl_range(first_row, first_col, last_data_row, last_col)
3626 # If the header row if off the default is to turn autofilter off.
3627 if not options["header_row"]:
3628 options["autofilter"] = 0
3630 # Set the autofilter range.
3631 if options["autofilter"]:
3632 table["autofilter"] = table["a_range"]
3634 # Add the table columns.
3635 col_id = 1
3636 table["columns"] = []
3637 seen_names = {}
3639 for col_num in range(first_col, last_col + 1):
3640 # Set up the default column data.
3641 col_data = {
3642 "id": col_id,
3643 "name": "Column" + str(col_id),
3644 "total_string": "",
3645 "total_function": "",
3646 "custom_total": "",
3647 "total_value": 0,
3648 "formula": "",
3649 "format": None,
3650 "name_format": None,
3651 }
3653 # Overwrite the defaults with any user defined values.
3654 if "columns" in options:
3655 # Check if there are user defined values for this column.
3656 if col_id <= len(options["columns"]):
3657 user_data = options["columns"][col_id - 1]
3658 else:
3659 user_data = None
3661 if user_data:
3662 # Get the column format.
3663 xformat = user_data.get("format", None)
3665 # Map user defined values to internal values.
3666 if user_data.get("header"):
3667 col_data["name"] = user_data["header"]
3669 # Excel requires unique case insensitive header names.
3670 header_name = col_data["name"]
3671 name = header_name.lower()
3672 if name in seen_names:
3673 warn(f"Duplicate header name in add_table(): '{name}'")
3674 return -2
3676 seen_names[name] = True
3678 col_data["name_format"] = user_data.get("header_format")
3680 # Handle the column formula.
3681 if "formula" in user_data and user_data["formula"]:
3682 formula = user_data["formula"]
3684 # Remove the formula '=' sign if it exists.
3685 if formula.startswith("="):
3686 formula = formula.lstrip("=")
3688 # Convert Excel 2010 "@" ref to 2007 "#This Row".
3689 formula = self._prepare_table_formula(formula)
3691 # Escape any future functions.
3692 formula = self._prepare_formula(formula, True)
3694 col_data["formula"] = formula
3695 # We write the formulas below after the table data.
3697 # Handle the function for the total row.
3698 if user_data.get("total_function"):
3699 function = user_data["total_function"]
3700 if function == "count_nums":
3701 function = "countNums"
3702 if function == "std_dev":
3703 function = "stdDev"
3705 subtotals = set(
3706 [
3707 "average",
3708 "countNums",
3709 "count",
3710 "max",
3711 "min",
3712 "stdDev",
3713 "sum",
3714 "var",
3715 ]
3716 )
3718 if function in subtotals:
3719 formula = self._table_function_to_formula(
3720 function, col_data["name"]
3721 )
3722 else:
3723 formula = self._prepare_formula(function, True)
3724 col_data["custom_total"] = formula
3725 function = "custom"
3727 col_data["total_function"] = function
3729 value = user_data.get("total_value", 0)
3731 self._write_formula(last_row, col_num, formula, xformat, value)
3733 elif user_data.get("total_string"):
3734 # Total label only (not a function).
3735 total_string = user_data["total_string"]
3736 col_data["total_string"] = total_string
3738 self._write_string(
3739 last_row, col_num, total_string, user_data.get("format")
3740 )
3742 # Get the dxf format index.
3743 if xformat is not None:
3744 col_data["format"] = xformat._get_dxf_index()
3746 # Store the column format for writing the cell data.
3747 # It doesn't matter if it is undefined.
3748 col_formats[col_id - 1] = xformat
3750 # Store the column data.
3751 table["columns"].append(col_data)
3753 # Write the column headers to the worksheet.
3754 if options["header_row"]:
3755 self._write_string(
3756 first_row, col_num, col_data["name"], col_data["name_format"]
3757 )
3759 col_id += 1
3761 # Write the cell data if supplied.
3762 if "data" in options:
3763 data = options["data"]
3765 i = 0 # For indexing the row data.
3766 for row in range(first_data_row, last_data_row + 1):
3767 j = 0 # For indexing the col data.
3768 for col in range(first_col, last_col + 1):
3769 if i < len(data) and j < len(data[i]):
3770 token = data[i][j]
3771 if j in col_formats:
3772 self._write(row, col, token, col_formats[j])
3773 else:
3774 self._write(row, col, token, None)
3775 j += 1
3776 i += 1
3778 # Write any columns formulas after the user supplied table data to
3779 # overwrite it if required.
3780 for col_id, col_num in enumerate(range(first_col, last_col + 1)):
3781 column_data = table["columns"][col_id]
3782 if column_data and column_data["formula"]:
3783 formula_format = col_formats.get(col_id)
3784 formula = column_data["formula"]
3786 for row in range(first_data_row, last_data_row + 1):
3787 self._write_formula(row, col_num, formula, formula_format)
3789 # Store the table data.
3790 self.tables.append(table)
3792 # Store the filter cell positions for use in the autofit calculation.
3793 if options["autofilter"]:
3794 for col in range(first_col, last_col + 1):
3795 # Check that the table autofilter doesn't overlap a worksheet filter.
3796 if self.filter_cells.get((first_row, col)):
3797 filter_type, filter_range = self.filter_cells.get((first_row, col))
3798 if filter_type == "worksheet":
3799 raise OverlappingRange(
3800 f"Table autofilter range '{cell_range}' overlaps previous "
3801 f"Worksheet autofilter range '{filter_range}'."
3802 )
3804 self.filter_cells[(first_row, col)] = ("table", cell_range)
3806 return 0
3808 @convert_cell_args
3809 def add_sparkline(
3810 self, row: int, col: int, options: Optional[Dict[str, Any]] = None
3811 ) -> Literal[0, -1, -2]:
3812 """
3813 Add sparklines to the worksheet.
3815 Args:
3816 row: The cell row (zero indexed).
3817 col: The cell column (zero indexed).
3818 options: Sparkline formatting options.
3820 Returns:
3821 0: Success.
3822 -1: Row or column is out of worksheet bounds.
3823 -2: Incorrect parameter or option.
3825 """
3827 # Check that row and col are valid without storing the values.
3828 if self._check_dimensions(row, col, True, True):
3829 return -1
3831 sparkline = {"locations": [xl_rowcol_to_cell(row, col)]}
3833 if options is None:
3834 options = {}
3836 # Valid input parameters.
3837 valid_parameters = {
3838 "location",
3839 "range",
3840 "type",
3841 "high_point",
3842 "low_point",
3843 "negative_points",
3844 "first_point",
3845 "last_point",
3846 "markers",
3847 "style",
3848 "series_color",
3849 "negative_color",
3850 "markers_color",
3851 "first_color",
3852 "last_color",
3853 "high_color",
3854 "low_color",
3855 "max",
3856 "min",
3857 "axis",
3858 "reverse",
3859 "empty_cells",
3860 "show_hidden",
3861 "plot_hidden",
3862 "date_axis",
3863 "weight",
3864 }
3866 # Check for valid input parameters.
3867 for param_key in options.keys():
3868 if param_key not in valid_parameters:
3869 warn(f"Unknown parameter '{param_key}' in add_sparkline()")
3870 return -1
3872 # 'range' is a required parameter.
3873 if "range" not in options:
3874 warn("Parameter 'range' is required in add_sparkline()")
3875 return -2
3877 # Handle the sparkline type.
3878 spark_type = options.get("type", "line")
3880 if spark_type not in ("line", "column", "win_loss"):
3881 warn(
3882 "Parameter 'type' must be 'line', 'column' "
3883 "or 'win_loss' in add_sparkline()"
3884 )
3885 return -2
3887 if spark_type == "win_loss":
3888 spark_type = "stacked"
3889 sparkline["type"] = spark_type
3891 # We handle single location/range values or list of values.
3892 if "location" in options:
3893 if isinstance(options["location"], list):
3894 sparkline["locations"] = options["location"]
3895 else:
3896 sparkline["locations"] = [options["location"]]
3898 if isinstance(options["range"], list):
3899 sparkline["ranges"] = options["range"]
3900 else:
3901 sparkline["ranges"] = [options["range"]]
3903 range_count = len(sparkline["ranges"])
3904 location_count = len(sparkline["locations"])
3906 # The ranges and locations must match.
3907 if range_count != location_count:
3908 warn(
3909 "Must have the same number of location and range "
3910 "parameters in add_sparkline()"
3911 )
3912 return -2
3914 # Store the count.
3915 sparkline["count"] = len(sparkline["locations"])
3917 # Get the worksheet name for the range conversion below.
3918 sheetname = quote_sheetname(self.name)
3920 # Cleanup the input ranges.
3921 new_ranges = []
3922 for spark_range in sparkline["ranges"]:
3923 # Remove the absolute reference $ symbols.
3924 spark_range = spark_range.replace("$", "")
3926 # Remove the = from formula.
3927 spark_range = spark_range.lstrip("=")
3929 # Convert a simple range into a full Sheet1!A1:D1 range.
3930 if "!" not in spark_range:
3931 spark_range = sheetname + "!" + spark_range
3933 new_ranges.append(spark_range)
3935 sparkline["ranges"] = new_ranges
3937 # Cleanup the input locations.
3938 new_locations = []
3939 for location in sparkline["locations"]:
3940 location = location.replace("$", "")
3941 new_locations.append(location)
3943 sparkline["locations"] = new_locations
3945 # Map options.
3946 sparkline["high"] = options.get("high_point")
3947 sparkline["low"] = options.get("low_point")
3948 sparkline["negative"] = options.get("negative_points")
3949 sparkline["first"] = options.get("first_point")
3950 sparkline["last"] = options.get("last_point")
3951 sparkline["markers"] = options.get("markers")
3952 sparkline["min"] = options.get("min")
3953 sparkline["max"] = options.get("max")
3954 sparkline["axis"] = options.get("axis")
3955 sparkline["reverse"] = options.get("reverse")
3956 sparkline["hidden"] = options.get("show_hidden")
3957 sparkline["weight"] = options.get("weight")
3959 # Map empty cells options.
3960 empty = options.get("empty_cells", "")
3962 if empty == "zero":
3963 sparkline["empty"] = 0
3964 elif empty == "connect":
3965 sparkline["empty"] = "span"
3966 else:
3967 sparkline["empty"] = "gap"
3969 # Map the date axis range.
3970 date_range = options.get("date_axis")
3972 if date_range and "!" not in date_range:
3973 date_range = sheetname + "!" + date_range
3975 sparkline["date_axis"] = date_range
3977 # Set the sparkline styles.
3978 style_id = options.get("style", 0)
3979 style = _get_sparkline_style(style_id)
3981 sparkline["series_color"] = style["series"]
3982 sparkline["negative_color"] = style["negative"]
3983 sparkline["markers_color"] = style["markers"]
3984 sparkline["first_color"] = style["first"]
3985 sparkline["last_color"] = style["last"]
3986 sparkline["high_color"] = style["high"]
3987 sparkline["low_color"] = style["low"]
3989 # Override the style colors with user defined colors.
3990 self._set_spark_color(sparkline, options, "series_color")
3991 self._set_spark_color(sparkline, options, "negative_color")
3992 self._set_spark_color(sparkline, options, "markers_color")
3993 self._set_spark_color(sparkline, options, "first_color")
3994 self._set_spark_color(sparkline, options, "last_color")
3995 self._set_spark_color(sparkline, options, "high_color")
3996 self._set_spark_color(sparkline, options, "low_color")
3998 self.sparklines.append(sparkline)
4000 return 0
4002 @convert_range_args
4003 def set_selection(
4004 self, first_row: int, first_col: int, last_row: int, last_col: int
4005 ) -> None:
4006 """
4007 Set the selected cell or cells in a worksheet
4009 Args:
4010 first_row: The first row of the cell range. (zero indexed).
4011 first_col: The first column of the cell range.
4012 last_row: The last row of the cell range. (zero indexed).
4013 last_col: The last column of the cell range.
4015 Returns:
4016 0: Nothing.
4017 """
4018 pane = None
4020 # Range selection. Do this before swapping max/min to allow the
4021 # selection direction to be reversed.
4022 active_cell = xl_rowcol_to_cell(first_row, first_col)
4024 # Swap last row/col for first row/col if necessary
4025 if first_row > last_row:
4026 first_row, last_row = (last_row, first_row)
4028 if first_col > last_col:
4029 first_col, last_col = (last_col, first_col)
4031 sqref = xl_range(first_row, first_col, last_row, last_col)
4033 # Selection isn't set for cell A1.
4034 if sqref == "A1":
4035 return
4037 self.selections = [[pane, active_cell, sqref]]
4039 @convert_cell_args
4040 def set_top_left_cell(self, row: int = 0, col: int = 0) -> None:
4041 """
4042 Set the first visible cell at the top left of a worksheet.
4044 Args:
4045 row: The cell row (zero indexed).
4046 col: The cell column (zero indexed).
4048 Returns:
4049 0: Nothing.
4050 """
4052 if row == 0 and col == 0:
4053 return
4055 self.top_left_cell = xl_rowcol_to_cell(row, col)
4057 def outline_settings(
4058 self,
4059 visible: bool = 1,
4060 symbols_below: bool = 1,
4061 symbols_right: bool = 1,
4062 auto_style: bool = 0,
4063 ) -> None:
4064 """
4065 Control outline settings.
4067 Args:
4068 visible: Outlines are visible. Optional, defaults to True.
4069 symbols_below: Show row outline symbols below the outline bar.
4070 Optional, defaults to True.
4071 symbols_right: Show column outline symbols to the right of the
4072 outline bar. Optional, defaults to True.
4073 auto_style: Use Automatic style. Optional, defaults to False.
4075 Returns:
4076 0: Nothing.
4077 """
4078 self.outline_on = visible
4079 self.outline_below = symbols_below
4080 self.outline_right = symbols_right
4081 self.outline_style = auto_style
4083 self.outline_changed = True
4085 @convert_cell_args
4086 def freeze_panes(
4087 self,
4088 row: int,
4089 col: int,
4090 top_row: Optional[int] = None,
4091 left_col: Optional[int] = None,
4092 pane_type: int = 0,
4093 ) -> None:
4094 """
4095 Create worksheet panes and mark them as frozen.
4097 Args:
4098 row: The cell row (zero indexed).
4099 col: The cell column (zero indexed).
4100 top_row: Topmost visible row in scrolling region of pane.
4101 left_col: Leftmost visible row in scrolling region of pane.
4103 Returns:
4104 0: Nothing.
4106 """
4107 if top_row is None:
4108 top_row = row
4110 if left_col is None:
4111 left_col = col
4113 self.panes = [row, col, top_row, left_col, pane_type]
4115 @convert_cell_args
4116 def split_panes(
4117 self,
4118 x: float,
4119 y: float,
4120 top_row: Optional[int] = None,
4121 left_col: Optional[int] = None,
4122 ) -> None:
4123 """
4124 Create worksheet panes and mark them as split.
4126 Args:
4127 x: The position for the vertical split.
4128 y: The position for the horizontal split.
4129 top_row: Topmost visible row in scrolling region of pane.
4130 left_col: Leftmost visible row in scrolling region of pane.
4132 Returns:
4133 0: Nothing.
4135 """
4136 # Same as freeze panes with a different pane type.
4137 self.freeze_panes(x, y, top_row, left_col, 2)
4139 def set_zoom(self, zoom: int = 100) -> None:
4140 """
4141 Set the worksheet zoom factor.
4143 Args:
4144 zoom: Scale factor: 10 <= zoom <= 400.
4146 Returns:
4147 Nothing.
4149 """
4150 # Ensure the zoom scale is in Excel's range.
4151 if zoom < 10 or zoom > 400:
4152 warn(f"Zoom factor '{zoom}' outside range: 10 <= zoom <= 400")
4153 zoom = 100
4155 self.zoom = int(zoom)
4157 def set_zoom_to_fit(self) -> None:
4158 """
4159 Set the worksheet zoom to selection/fit. Only works for chartsheets.
4161 Args:
4162 None.
4164 Returns:
4165 Nothing.
4167 """
4168 self.zoom_to_fit = True
4170 def right_to_left(self) -> None:
4171 """
4172 Display the worksheet right to left for some versions of Excel.
4174 Args:
4175 None.
4177 Returns:
4178 Nothing.
4180 """
4181 self.is_right_to_left = True
4183 def hide_zero(self) -> None:
4184 """
4185 Hide zero values in worksheet cells.
4187 Args:
4188 None.
4190 Returns:
4191 Nothing.
4193 """
4194 self.show_zeros = 0
4196 def set_tab_color(self, color: Union[str, Color]) -> None:
4197 """
4198 Set the color of the worksheet tab.
4200 Args:
4201 color: A #RGB color index.
4203 Returns:
4204 Nothing.
4206 """
4207 self.tab_color = Color._from_value(color)
4209 def protect(
4210 self, password: str = "", options: Optional[Dict[str, Any]] = None
4211 ) -> None:
4212 """
4213 Set the password and protection options of the worksheet.
4215 Args:
4216 password: An optional password string.
4217 options: A dictionary of worksheet objects to protect.
4219 Returns:
4220 Nothing.
4222 """
4223 if password != "":
4224 password = self._encode_password(password)
4226 if not options:
4227 options = {}
4229 # Default values for objects that can be protected.
4230 defaults = {
4231 "sheet": True,
4232 "content": False,
4233 "objects": False,
4234 "scenarios": False,
4235 "format_cells": False,
4236 "format_columns": False,
4237 "format_rows": False,
4238 "insert_columns": False,
4239 "insert_rows": False,
4240 "insert_hyperlinks": False,
4241 "delete_columns": False,
4242 "delete_rows": False,
4243 "select_locked_cells": True,
4244 "sort": False,
4245 "autofilter": False,
4246 "pivot_tables": False,
4247 "select_unlocked_cells": True,
4248 }
4250 # Overwrite the defaults with user specified values.
4251 for key in options.keys():
4252 if key in defaults:
4253 defaults[key] = options[key]
4254 else:
4255 warn(f"Unknown protection object: '{key}'")
4257 # Set the password after the user defined values.
4258 defaults["password"] = password
4260 self.protect_options = defaults
4262 def unprotect_range(
4263 self,
4264 cell_range: str,
4265 range_name: Optional[str] = None,
4266 password: Optional[str] = None,
4267 ) -> int:
4268 """
4269 Unprotect ranges within a protected worksheet.
4271 Args:
4272 cell_range: The cell or cell range to unprotect.
4273 range_name: An optional name for the range.
4274 password: An optional password string. (undocumented)
4276 Returns:
4277 0: Success.
4278 -1: Parameter error.
4280 """
4281 if cell_range is None:
4282 warn("Cell range must be specified in unprotect_range()")
4283 return -1
4285 # Sanitize the cell range.
4286 cell_range = cell_range.lstrip("=")
4287 cell_range = cell_range.replace("$", "")
4289 self.num_protected_ranges += 1
4291 if range_name is None:
4292 range_name = "Range" + str(self.num_protected_ranges)
4294 if password:
4295 password = self._encode_password(password)
4297 self.protected_ranges.append((cell_range, range_name, password))
4299 return 0
4301 @convert_cell_args
4302 def insert_button(
4303 self, row: int, col: int, options: Optional[Dict[str, Any]] = None
4304 ) -> Literal[0, -1]:
4305 """
4306 Insert a button form object into the worksheet.
4308 Args:
4309 row: The cell row (zero indexed).
4310 col: The cell column (zero indexed).
4311 options: Button formatting options.
4313 Returns:
4314 0: Success.
4315 -1: Row or column is out of worksheet bounds.
4317 """
4318 # Check insert (row, col) without storing.
4319 if self._check_dimensions(row, col, True, True):
4320 warn(f"Cannot insert button at ({row}, {col}).")
4321 return -1
4323 if options is None:
4324 options = {}
4326 # Create a new button object.
4327 height = self.default_row_height
4328 width = self.default_col_width
4329 button_number = 1 + len(self.buttons_list)
4331 button = ButtonType(row, col, height, width, button_number, options)
4333 self.buttons_list.append(button)
4335 self.has_vml = True
4337 return 0
4339 @convert_cell_args
4340 def insert_checkbox(
4341 self, row: int, col: int, boolean: bool, cell_format: Optional[Format] = None
4342 ):
4343 """
4344 Insert a boolean checkbox in a worksheet cell.
4346 Args:
4347 row: The cell row (zero indexed).
4348 col: The cell column (zero indexed).
4349 boolean: The boolean value to display as a checkbox.
4350 cell_format: Cell Format object. (optional)
4352 Returns:
4353 0: Success.
4354 -1: Row or column is out of worksheet bounds.
4356 """
4357 # Ensure that the checkbox property is set in the user defined format.
4358 if cell_format and not cell_format.checkbox:
4359 # This needs to be fixed with a clone.
4360 cell_format.set_checkbox()
4362 # If no format is supplied create and/or use the default checkbox format.
4363 if not cell_format:
4364 if not self.default_checkbox_format:
4365 self.default_checkbox_format = self.workbook_add_format()
4366 self.default_checkbox_format.set_checkbox()
4368 cell_format = self.default_checkbox_format
4370 return self._write_boolean(row, col, boolean, cell_format)
4372 ###########################################################################
4373 #
4374 # Public API. Page Setup methods.
4375 #
4376 ###########################################################################
4377 def set_landscape(self) -> None:
4378 """
4379 Set the page orientation as landscape.
4381 Args:
4382 None.
4384 Returns:
4385 Nothing.
4387 """
4388 self.orientation = 0
4389 self.page_setup_changed = True
4391 def set_portrait(self) -> None:
4392 """
4393 Set the page orientation as portrait.
4395 Args:
4396 None.
4398 Returns:
4399 Nothing.
4401 """
4402 self.orientation = 1
4403 self.page_setup_changed = True
4405 def set_page_view(self, view: Literal[0, 1, 2] = 1) -> None:
4406 """
4407 Set the page view mode.
4409 Args:
4410 0: Normal view mode
4411 1: Page view mode (the default)
4412 2: Page break view mode
4414 Returns:
4415 Nothing.
4417 """
4418 self.page_view = view
4420 def set_pagebreak_view(self) -> None:
4421 """
4422 Set the page view mode.
4424 Args:
4425 None.
4427 Returns:
4428 Nothing.
4430 """
4431 self.page_view = 2
4433 def set_paper(self, paper_size: Union[Literal[1, 9], int]) -> None:
4434 """
4435 Set the paper type. US Letter = 1, A4 = 9.
4437 Args:
4438 paper_size: Paper index.
4440 Returns:
4441 Nothing.
4443 """
4444 if paper_size:
4445 self.paper_size = paper_size
4446 self.page_setup_changed = True
4448 def center_horizontally(self) -> None:
4449 """
4450 Center the page horizontally.
4452 Args:
4453 None.
4455 Returns:
4456 Nothing.
4458 """
4459 self.print_options_changed = True
4460 self.hcenter = 1
4462 def center_vertically(self) -> None:
4463 """
4464 Center the page vertically.
4466 Args:
4467 None.
4469 Returns:
4470 Nothing.
4472 """
4473 self.print_options_changed = True
4474 self.vcenter = 1
4476 def set_margins(
4477 self,
4478 left: float = 0.7,
4479 right: float = 0.7,
4480 top: float = 0.75,
4481 bottom: float = 0.75,
4482 ) -> None:
4483 """
4484 Set all the page margins in inches.
4486 Args:
4487 left: Left margin.
4488 right: Right margin.
4489 top: Top margin.
4490 bottom: Bottom margin.
4492 Returns:
4493 Nothing.
4495 """
4496 self.margin_left = left
4497 self.margin_right = right
4498 self.margin_top = top
4499 self.margin_bottom = bottom
4501 def set_header(
4502 self, header: str = "", options: Optional[Dict[str, Any]] = None, margin=None
4503 ) -> None:
4504 """
4505 Set the page header caption and optional margin.
4507 Args:
4508 header: Header string.
4509 margin: Header margin.
4510 options: Header options, mainly for images.
4512 Returns:
4513 Nothing.
4515 """
4516 header_orig = header
4517 header = header.replace("&[Picture]", "&G")
4519 if len(header) > 255:
4520 warn("Header string cannot be longer than Excel's limit of 255 characters")
4521 return
4523 if options is not None:
4524 # For backward compatibility allow options to be the margin.
4525 if not isinstance(options, dict):
4526 options = {"margin": options}
4527 else:
4528 options = {}
4530 # Copy the user defined options so they aren't modified.
4531 options = options.copy()
4533 # For backward compatibility.
4534 if margin is not None:
4535 options["margin"] = margin
4537 # Reset the list in case the function is called more than once.
4538 self.header_images = []
4540 if options.get("image_left"):
4541 options["image_data"] = options.get("image_data_left")
4542 image = self._image_from_source(options.get("image_left"), options)
4543 image._header_position = "LH"
4544 self.header_images.append(image)
4546 if options.get("image_center"):
4547 options["image_data"] = options.get("image_data_center")
4548 image = self._image_from_source(options.get("image_center"), options)
4549 image._header_position = "CH"
4550 self.header_images.append(image)
4552 if options.get("image_right"):
4553 options["image_data"] = options.get("image_data_right")
4554 image = self._image_from_source(options.get("image_right"), options)
4555 image._header_position = "RH"
4556 self.header_images.append(image)
4558 placeholder_count = header.count("&G")
4559 image_count = len(self.header_images)
4561 if placeholder_count != image_count:
4562 warn(
4563 f"Number of footer images '{image_count}' doesn't match placeholder "
4564 f"count '{placeholder_count}' in string: {header_orig}"
4565 )
4566 self.header_images = []
4567 return
4569 if "align_with_margins" in options:
4570 self.header_footer_aligns = options["align_with_margins"]
4572 if "scale_with_doc" in options:
4573 self.header_footer_scales = options["scale_with_doc"]
4575 self.header = header
4576 self.margin_header = options.get("margin", 0.3)
4577 self.header_footer_changed = True
4579 if image_count:
4580 self.has_header_vml = True
4582 def set_footer(
4583 self, footer: str = "", options: Optional[Dict[str, Any]] = None, margin=None
4584 ) -> None:
4585 """
4586 Set the page footer caption and optional margin.
4588 Args:
4589 footer: Footer string.
4590 margin: Footer margin.
4591 options: Footer options, mainly for images.
4593 Returns:
4594 Nothing.
4596 """
4597 footer_orig = footer
4598 footer = footer.replace("&[Picture]", "&G")
4600 if len(footer) > 255:
4601 warn("Footer string cannot be longer than Excel's limit of 255 characters")
4602 return
4604 if options is not None:
4605 # For backward compatibility allow options to be the margin.
4606 if not isinstance(options, dict):
4607 options = {"margin": options}
4608 else:
4609 options = {}
4611 # Copy the user defined options so they aren't modified.
4612 options = options.copy()
4614 # For backward compatibility.
4615 if margin is not None:
4616 options["margin"] = margin
4618 # Reset the list in case the function is called more than once.
4619 self.footer_images = []
4621 if options.get("image_left"):
4622 options["image_data"] = options.get("image_data_left")
4623 image = self._image_from_source(options.get("image_left"), options)
4624 image._header_position = "LF"
4625 self.footer_images.append(image)
4627 if options.get("image_center"):
4628 options["image_data"] = options.get("image_data_center")
4629 image = self._image_from_source(options.get("image_center"), options)
4630 image._header_position = "CF"
4631 self.footer_images.append(image)
4633 if options.get("image_right"):
4634 options["image_data"] = options.get("image_data_right")
4635 image = self._image_from_source(options.get("image_right"), options)
4636 image._header_position = "RF"
4637 self.footer_images.append(image)
4639 placeholder_count = footer.count("&G")
4640 image_count = len(self.footer_images)
4642 if placeholder_count != image_count:
4643 warn(
4644 f"Number of footer images '{image_count}' doesn't match placeholder "
4645 f"count '{placeholder_count}' in string: {footer_orig}"
4646 )
4647 self.footer_images = []
4648 return
4650 if "align_with_margins" in options:
4651 self.header_footer_aligns = options["align_with_margins"]
4653 if "scale_with_doc" in options:
4654 self.header_footer_scales = options["scale_with_doc"]
4656 self.footer = footer
4657 self.margin_footer = options.get("margin", 0.3)
4658 self.header_footer_changed = True
4660 if image_count:
4661 self.has_header_vml = True
4663 def repeat_rows(self, first_row: int, last_row: Optional[int] = None) -> None:
4664 """
4665 Set the rows to repeat at the top of each printed page.
4667 Args:
4668 first_row: Start row for range.
4669 last_row: End row for range.
4671 Returns:
4672 Nothing.
4674 """
4675 if last_row is None:
4676 last_row = first_row
4678 # Convert rows to 1 based.
4679 first_row += 1
4680 last_row += 1
4682 # Create the row range area like: $1:$2.
4683 area = f"${first_row}:${last_row}"
4685 # Build up the print titles area "Sheet1!$1:$2"
4686 sheetname = quote_sheetname(self.name)
4687 self.repeat_row_range = sheetname + "!" + area
4689 @convert_column_args
4690 def repeat_columns(self, first_col: int, last_col: Optional[int] = None) -> None:
4691 """
4692 Set the columns to repeat at the left hand side of each printed page.
4694 Args:
4695 first_col: Start column for range.
4696 last_col: End column for range.
4698 Returns:
4699 Nothing.
4701 """
4702 if last_col is None:
4703 last_col = first_col
4705 # Convert to A notation.
4706 first_col = xl_col_to_name(first_col, 1)
4707 last_col = xl_col_to_name(last_col, 1)
4709 # Create a column range like $C:$D.
4710 area = first_col + ":" + last_col
4712 # Build up the print area range "=Sheet2!$C:$D"
4713 sheetname = quote_sheetname(self.name)
4714 self.repeat_col_range = sheetname + "!" + area
4716 def hide_gridlines(self, option: Literal[0, 1, 2] = 1) -> None:
4717 """
4718 Set the option to hide gridlines on the screen and the printed page.
4720 Args:
4721 option: 0 : Don't hide gridlines
4722 1 : Hide printed gridlines only
4723 2 : Hide screen and printed gridlines
4725 Returns:
4726 Nothing.
4728 """
4729 if option == 0:
4730 self.print_gridlines = 1
4731 self.screen_gridlines = 1
4732 self.print_options_changed = True
4733 elif option == 1:
4734 self.print_gridlines = 0
4735 self.screen_gridlines = 1
4736 else:
4737 self.print_gridlines = 0
4738 self.screen_gridlines = 0
4740 def print_row_col_headers(self) -> None:
4741 """
4742 Set the option to print the row and column headers on the printed page.
4744 Args:
4745 None.
4747 Returns:
4748 Nothing.
4750 """
4751 self.print_headers = True
4752 self.print_options_changed = True
4754 def hide_row_col_headers(self) -> None:
4755 """
4756 Set the option to hide the row and column headers on the worksheet.
4758 Args:
4759 None.
4761 Returns:
4762 Nothing.
4764 """
4765 self.row_col_headers = True
4767 @convert_range_args
4768 def print_area(
4769 self, first_row: int, first_col: int, last_row: int, last_col: int
4770 ) -> Literal[0, -1]:
4771 """
4772 Set the print area in the current worksheet.
4774 Args:
4775 first_row: The first row of the cell range. (zero indexed).
4776 first_col: The first column of the cell range.
4777 last_row: The last row of the cell range. (zero indexed).
4778 last_col: The last column of the cell range.
4780 Returns:
4781 0: Success.
4782 -1: Row or column is out of worksheet bounds.
4784 """
4785 # Set the print area in the current worksheet.
4787 # Ignore max print area since it is the same as no area for Excel.
4788 if (
4789 first_row == 0
4790 and first_col == 0
4791 and last_row == self.xls_rowmax - 1
4792 and last_col == self.xls_colmax - 1
4793 ):
4794 return -1
4796 # Build up the print area range "Sheet1!$A$1:$C$13".
4797 area = self._convert_name_area(first_row, first_col, last_row, last_col)
4798 self.print_area_range = area
4800 return 0
4802 def print_across(self) -> None:
4803 """
4804 Set the order in which pages are printed.
4806 Args:
4807 None.
4809 Returns:
4810 Nothing.
4812 """
4813 self.page_order = 1
4814 self.page_setup_changed = True
4816 def fit_to_pages(self, width: int, height: int) -> None:
4817 """
4818 Fit the printed area to a specific number of pages both vertically and
4819 horizontally.
4821 Args:
4822 width: Number of pages horizontally.
4823 height: Number of pages vertically.
4825 Returns:
4826 Nothing.
4828 """
4829 self.fit_page = 1
4830 self.fit_width = width
4831 self.fit_height = height
4832 self.page_setup_changed = True
4834 def set_start_page(self, start_page: int) -> None:
4835 """
4836 Set the start page number when printing.
4838 Args:
4839 start_page: Start page number.
4841 Returns:
4842 Nothing.
4844 """
4845 self.page_start = start_page
4847 def set_print_scale(self, scale: int) -> None:
4848 """
4849 Set the scale factor for the printed page.
4851 Args:
4852 scale: Print scale. 10 <= scale <= 400.
4854 Returns:
4855 Nothing.
4857 """
4858 # Confine the scale to Excel's range.
4859 if scale < 10 or scale > 400:
4860 warn(f"Print scale '{scale}' outside range: 10 <= scale <= 400")
4861 return
4863 # Turn off "fit to page" option when print scale is on.
4864 self.fit_page = 0
4866 self.print_scale = int(scale)
4867 self.page_setup_changed = True
4869 def print_black_and_white(self) -> None:
4870 """
4871 Set the option to print the worksheet in black and white.
4873 Args:
4874 None.
4876 Returns:
4877 Nothing.
4879 """
4880 self.black_white = True
4881 self.page_setup_changed = True
4883 def set_h_pagebreaks(self, breaks: List[int]) -> None:
4884 """
4885 Set the horizontal page breaks on a worksheet.
4887 Args:
4888 breaks: List of rows where the page breaks should be added.
4890 Returns:
4891 Nothing.
4893 """
4894 self.hbreaks = breaks
4896 def set_v_pagebreaks(self, breaks: List[int]) -> None:
4897 """
4898 Set the horizontal page breaks on a worksheet.
4900 Args:
4901 breaks: List of columns where the page breaks should be added.
4903 Returns:
4904 Nothing.
4906 """
4907 self.vbreaks = breaks
4909 def set_vba_name(self, name: Optional[str] = None) -> None:
4910 """
4911 Set the VBA name for the worksheet. By default this is the
4912 same as the sheet name: i.e., Sheet1 etc.
4914 Args:
4915 name: The VBA name for the worksheet.
4917 Returns:
4918 Nothing.
4920 """
4921 if name is not None:
4922 self.vba_codename = name
4923 else:
4924 self.vba_codename = "Sheet" + str(self.index + 1)
4926 def ignore_errors(self, options: Optional[Dict[str, Any]] = None) -> Literal[0, -1]:
4927 """
4928 Ignore various Excel errors/warnings in a worksheet for user defined
4929 ranges.
4931 Args:
4932 options: A dict of ignore errors keys with cell range values.
4934 Returns:
4935 0: Success.
4936 -1: Incorrect parameter or option.
4938 """
4939 if options is None:
4940 return -1
4942 # Copy the user defined options so they aren't modified.
4943 options = options.copy()
4945 # Valid input parameters.
4946 valid_parameters = {
4947 "number_stored_as_text",
4948 "eval_error",
4949 "formula_differs",
4950 "formula_range",
4951 "formula_unlocked",
4952 "empty_cell_reference",
4953 "list_data_validation",
4954 "calculated_column",
4955 "two_digit_text_year",
4956 }
4958 # Check for valid input parameters.
4959 for param_key in options.keys():
4960 if param_key not in valid_parameters:
4961 warn(f"Unknown parameter '{param_key}' in ignore_errors()")
4962 return -1
4964 self.ignored_errors = options
4966 return 0
4968 ###########################################################################
4969 #
4970 # Private API.
4971 #
4972 ###########################################################################
4973 def _initialize(self, init_data) -> None:
4974 self.name = init_data["name"]
4975 self.index = init_data["index"]
4976 self.str_table = init_data["str_table"]
4977 self.worksheet_meta = init_data["worksheet_meta"]
4978 self.constant_memory = init_data["constant_memory"]
4979 self.tmpdir = init_data["tmpdir"]
4980 self.date_1904 = init_data["date_1904"]
4981 self.strings_to_numbers = init_data["strings_to_numbers"]
4982 self.strings_to_formulas = init_data["strings_to_formulas"]
4983 self.strings_to_urls = init_data["strings_to_urls"]
4984 self.nan_inf_to_errors = init_data["nan_inf_to_errors"]
4985 self.default_date_format = init_data["default_date_format"]
4986 self.default_url_format = init_data["default_url_format"]
4987 self.workbook_add_format = init_data["workbook_add_format"]
4988 self.excel2003_style = init_data["excel2003_style"]
4989 self.remove_timezone = init_data["remove_timezone"]
4990 self.max_url_length = init_data["max_url_length"]
4991 self.use_future_functions = init_data["use_future_functions"]
4992 self.embedded_images = init_data["embedded_images"]
4993 self.default_row_height = init_data["default_row_height"]
4994 self.default_col_width = init_data["default_col_width"]
4995 self.max_digit_width = init_data["max_digit_width"]
4996 self.cell_padding = init_data["cell_padding"]
4997 self.max_col_width = init_data["max_col_width"]
4999 self.original_row_height = self.default_row_height
5001 if self.excel2003_style:
5002 self.original_row_height = 17
5003 self.default_row_height = 17
5004 self.margin_left = 0.75
5005 self.margin_right = 0.75
5006 self.margin_top = 1
5007 self.margin_bottom = 1
5008 self.margin_header = 0.5
5009 self.margin_footer = 0.5
5010 self.header_footer_aligns = False
5012 # Open a temp filehandle to store row data in constant_memory mode.
5013 if self.constant_memory:
5014 # This is sub-optimal but we need to create a temp file
5015 # with utf8 encoding in Python < 3.
5016 fd, filename = tempfile.mkstemp(dir=self.tmpdir)
5017 os.close(fd)
5018 self.row_data_filename = filename
5019 # pylint: disable=consider-using-with
5020 self.row_data_fh = open(filename, mode="w+", encoding="utf-8")
5022 # Set as the worksheet filehandle until the file is assembled.
5023 self.fh = self.row_data_fh
5025 def _assemble_xml_file(self) -> None:
5026 # Assemble and write the XML file.
5028 # Write the XML declaration.
5029 self._xml_declaration()
5031 # Write the root worksheet element.
5032 self._write_worksheet()
5034 # Write the worksheet properties.
5035 self._write_sheet_pr()
5037 # Write the worksheet dimensions.
5038 self._write_dimension()
5040 # Write the sheet view properties.
5041 self._write_sheet_views()
5043 # Write the sheet format properties.
5044 self._write_sheet_format_pr()
5046 # Write the sheet column info.
5047 self._write_cols()
5049 # Write the worksheet data such as rows columns and cells.
5050 if not self.constant_memory:
5051 self._write_sheet_data()
5052 else:
5053 self._write_optimized_sheet_data()
5055 # Write the sheetProtection element.
5056 self._write_sheet_protection()
5058 # Write the protectedRanges element.
5059 self._write_protected_ranges()
5061 # Write the phoneticPr element.
5062 if self.excel2003_style:
5063 self._write_phonetic_pr()
5065 # Write the autoFilter element.
5066 self._write_auto_filter()
5068 # Write the mergeCells element.
5069 self._write_merge_cells()
5071 # Write the conditional formats.
5072 self._write_conditional_formats()
5074 # Write the dataValidations element.
5075 self._write_data_validations()
5077 # Write the hyperlink element.
5078 self._write_hyperlinks()
5080 # Write the printOptions element.
5081 self._write_print_options()
5083 # Write the worksheet page_margins.
5084 self._write_page_margins()
5086 # Write the worksheet page setup.
5087 self._write_page_setup()
5089 # Write the headerFooter element.
5090 self._write_header_footer()
5092 # Write the rowBreaks element.
5093 self._write_row_breaks()
5095 # Write the colBreaks element.
5096 self._write_col_breaks()
5098 # Write the ignoredErrors element.
5099 self._write_ignored_errors()
5101 # Write the drawing element.
5102 self._write_drawings()
5104 # Write the legacyDrawing element.
5105 self._write_legacy_drawing()
5107 # Write the legacyDrawingHF element.
5108 self._write_legacy_drawing_hf()
5110 # Write the picture element, for the background.
5111 self._write_picture()
5113 # Write the tableParts element.
5114 self._write_table_parts()
5116 # Write the extLst elements.
5117 self._write_ext_list()
5119 # Close the worksheet tag.
5120 self._xml_end_tag("worksheet")
5122 # Close the file.
5123 self._xml_close()
5125 def _check_dimensions(
5126 self, row: int, col: int, ignore_row=False, ignore_col=False
5127 ) -> int:
5128 # Check that row and col are valid and store the max and min
5129 # values for use in other methods/elements. The ignore_row /
5130 # ignore_col flags is used to indicate that we wish to perform
5131 # the dimension check without storing the value. The ignore
5132 # flags are use by set_row() and data_validate.
5134 # Check that the row/col are within the worksheet bounds.
5135 if row < 0 or col < 0:
5136 return -1
5137 if row >= self.xls_rowmax or col >= self.xls_colmax:
5138 return -1
5140 # In constant_memory mode we don't change dimensions for rows
5141 # that are already written.
5142 if not ignore_row and not ignore_col and self.constant_memory:
5143 if row < self.previous_row:
5144 return -2
5146 if not ignore_row:
5147 if self.dim_rowmin is None or row < self.dim_rowmin:
5148 self.dim_rowmin = row
5149 if self.dim_rowmax is None or row > self.dim_rowmax:
5150 self.dim_rowmax = row
5152 if not ignore_col:
5153 if self.dim_colmin is None or col < self.dim_colmin:
5154 self.dim_colmin = col
5155 if self.dim_colmax is None or col > self.dim_colmax:
5156 self.dim_colmax = col
5158 return 0
5160 def _convert_date_time(self, dt_obj):
5161 # Convert a datetime object to an Excel serial date and time.
5162 return _datetime_to_excel_datetime(dt_obj, self.date_1904, self.remove_timezone)
5164 def _convert_name_area(self, row_num_1, col_num_1, row_num_2, col_num_2):
5165 # Convert zero indexed rows and columns to the format required by
5166 # worksheet named ranges, eg, "Sheet1!$A$1:$C$13".
5168 range1 = ""
5169 range2 = ""
5170 area = ""
5171 row_col_only = 0
5173 # Convert to A1 notation.
5174 col_char_1 = xl_col_to_name(col_num_1, 1)
5175 col_char_2 = xl_col_to_name(col_num_2, 1)
5176 row_char_1 = "$" + str(row_num_1 + 1)
5177 row_char_2 = "$" + str(row_num_2 + 1)
5179 # We need to handle special cases that refer to rows or columns only.
5180 if row_num_1 == 0 and row_num_2 == self.xls_rowmax - 1:
5181 range1 = col_char_1
5182 range2 = col_char_2
5183 row_col_only = 1
5184 elif col_num_1 == 0 and col_num_2 == self.xls_colmax - 1:
5185 range1 = row_char_1
5186 range2 = row_char_2
5187 row_col_only = 1
5188 else:
5189 range1 = col_char_1 + row_char_1
5190 range2 = col_char_2 + row_char_2
5192 # A repeated range is only written once (if it isn't a special case).
5193 if range1 == range2 and not row_col_only:
5194 area = range1
5195 else:
5196 area = range1 + ":" + range2
5198 # Build up the print area range "Sheet1!$A$1:$C$13".
5199 sheetname = quote_sheetname(self.name)
5200 area = sheetname + "!" + area
5202 return area
5204 def _sort_pagebreaks(self, breaks):
5205 # This is an internal method used to filter elements of a list of
5206 # pagebreaks used in the _store_hbreak() and _store_vbreak() methods.
5207 # It:
5208 # 1. Removes duplicate entries from the list.
5209 # 2. Sorts the list.
5210 # 3. Removes 0 from the list if present.
5211 if not breaks:
5212 return []
5214 breaks_set = set(breaks)
5216 if 0 in breaks_set:
5217 breaks_set.remove(0)
5219 breaks_list = list(breaks_set)
5220 breaks_list.sort()
5222 # The Excel 2007 specification says that the maximum number of page
5223 # breaks is 1026. However, in practice it is actually 1023.
5224 max_num_breaks = 1023
5225 if len(breaks_list) > max_num_breaks:
5226 breaks_list = breaks_list[:max_num_breaks]
5228 return breaks_list
5230 def _extract_filter_tokens(self, expression):
5231 # Extract the tokens from the filter expression. The tokens are mainly
5232 # non-whitespace groups. The only tricky part is to extract string
5233 # tokens that contain whitespace and/or quoted double quotes (Excel's
5234 # escaped quotes).
5235 #
5236 # Examples: 'x < 2000'
5237 # 'x > 2000 and x < 5000'
5238 # 'x = "foo"'
5239 # 'x = "foo bar"'
5240 # 'x = "foo "" bar"'
5241 #
5242 if not expression:
5243 return []
5245 token_re = re.compile(r'"(?:[^"]|"")*"|\S+')
5246 tokens = token_re.findall(expression)
5248 new_tokens = []
5249 # Remove single leading and trailing quotes and un-escape other quotes.
5250 for token in tokens:
5251 if token.startswith('"'):
5252 token = token[1:]
5254 if token.endswith('"'):
5255 token = token[:-1]
5257 token = token.replace('""', '"')
5259 new_tokens.append(token)
5261 return new_tokens
5263 def _parse_filter_expression(self, expression, tokens):
5264 # Converts the tokens of a possibly conditional expression into 1 or 2
5265 # sub expressions for further parsing.
5266 #
5267 # Examples:
5268 # ('x', '==', 2000) -> exp1
5269 # ('x', '>', 2000, 'and', 'x', '<', 5000) -> exp1 and exp2
5271 if len(tokens) == 7:
5272 # The number of tokens will be either 3 (for 1 expression)
5273 # or 7 (for 2 expressions).
5274 conditional = tokens[3]
5276 if re.match("(and|&&)", conditional):
5277 conditional = 0
5278 elif re.match(r"(or|\|\|)", conditional):
5279 conditional = 1
5280 else:
5281 warn(
5282 f"Token '{conditional}' is not a valid conditional "
5283 f"in filter expression '{expression}'"
5284 )
5286 expression_1 = self._parse_filter_tokens(expression, tokens[0:3])
5287 expression_2 = self._parse_filter_tokens(expression, tokens[4:7])
5288 return expression_1 + [conditional] + expression_2
5290 return self._parse_filter_tokens(expression, tokens)
5292 def _parse_filter_tokens(self, expression, tokens):
5293 # Parse the 3 tokens of a filter expression and return the operator
5294 # and token. The use of numbers instead of operators is a legacy of
5295 # Spreadsheet::WriteExcel.
5296 operators = {
5297 "==": 2,
5298 "=": 2,
5299 "=~": 2,
5300 "eq": 2,
5301 "!=": 5,
5302 "!~": 5,
5303 "ne": 5,
5304 "<>": 5,
5305 "<": 1,
5306 "<=": 3,
5307 ">": 4,
5308 ">=": 6,
5309 }
5311 operator = operators.get(tokens[1], None)
5312 token = tokens[2]
5314 # Special handling of "Top" filter expressions.
5315 if re.match("top|bottom", tokens[0].lower()):
5316 value = int(tokens[1])
5318 if value < 1 or value > 500:
5319 warn(
5320 f"The value '{token}' in expression '{expression}' "
5321 f"must be in the range 1 to 500"
5322 )
5324 token = token.lower()
5326 if token not in ("items", "%"):
5327 warn(
5328 f"The type '{token}' in expression '{expression}' "
5329 f"must be either 'items' or '%%'"
5330 )
5332 if tokens[0].lower() == "top":
5333 operator = 30
5334 else:
5335 operator = 32
5337 if tokens[2] == "%":
5338 operator += 1
5340 token = str(value)
5342 if not operator and tokens[0]:
5343 warn(
5344 f"Token '{token[0]}' is not a valid operator "
5345 f"in filter expression '{expression}'."
5346 )
5348 # Special handling for Blanks/NonBlanks.
5349 if re.match("blanks|nonblanks", token.lower()):
5350 # Only allow Equals or NotEqual in this context.
5351 if operator not in (2, 5):
5352 warn(
5353 f"The operator '{tokens[1]}' in expression '{expression}' "
5354 f"is not valid in relation to Blanks/NonBlanks'."
5355 )
5357 token = token.lower()
5359 # The operator should always be 2 (=) to flag a "simple" equality
5360 # in the binary record. Therefore we convert <> to =.
5361 if token == "blanks":
5362 if operator == 5:
5363 token = " "
5364 else:
5365 if operator == 5:
5366 operator = 2
5367 token = "blanks"
5368 else:
5369 operator = 5
5370 token = " "
5372 # if the string token contains an Excel match character then change the
5373 # operator type to indicate a non "simple" equality.
5374 if operator == 2 and re.search("[*?]", token):
5375 operator = 22
5377 return [operator, token]
5379 def _encode_password(self, password) -> str:
5380 # Hash a worksheet password. Based on the algorithm in
5381 # ECMA-376-4:2016, Office Open XML File Formats — Transitional
5382 # Migration Features, Additional attributes for workbookProtection
5383 # element (Part 1, §18.2.29).
5384 digest = 0x0000
5386 for char in password[::-1]:
5387 digest = ((digest >> 14) & 0x01) | ((digest << 1) & 0x7FFF)
5388 digest ^= ord(char)
5390 digest = ((digest >> 14) & 0x01) | ((digest << 1) & 0x7FFF)
5391 digest ^= len(password)
5392 digest ^= 0xCE4B
5394 return f"{digest:X}"
5396 def _image_from_source(self, source, options: Optional[Dict[str, Any]] = None):
5397 # Backward compatibility utility method to convert an input argument to
5398 # an Image object. The source can be a filename, BytesIO stream or
5399 # an existing Image object.
5400 if isinstance(source, Image):
5401 image = source
5402 elif options is not None and options.get("image_data"):
5403 image = Image(options["image_data"])
5404 image.image_name = source
5405 else:
5406 image = Image(source)
5408 return image
5410 def _prepare_image(
5411 self,
5412 image: Image,
5413 image_id: int,
5414 drawing_id: int,
5415 ) -> None:
5416 # Set up images/drawings.
5418 # Get the effective image width and height in pixels.
5419 width = image._width * image._x_scale
5420 height = image._height * image._y_scale
5422 # Scale by non 96dpi resolutions.
5423 width *= 96.0 / image._x_dpi
5424 height *= 96.0 / image._y_dpi
5426 dimensions = self._position_object_emus(
5427 image._col,
5428 image._row,
5429 image._x_offset,
5430 image._y_offset,
5431 width,
5432 height,
5433 image._anchor,
5434 )
5436 # Convert from pixels to emus.
5437 width = int(0.5 + (width * 9525))
5438 height = int(0.5 + (height * 9525))
5440 # Create a Drawing obj to use with worksheet unless one already exists.
5441 if not self.drawing:
5442 drawing = Drawing()
5443 drawing.embedded = 1
5444 self.drawing = drawing
5446 self.external_drawing_links.append(
5447 ["/drawing", "../drawings/drawing" + str(drawing_id) + ".xml", None]
5448 )
5449 else:
5450 drawing = self.drawing
5452 drawing_object = DrawingInfo()
5453 drawing_object._drawing_type = DrawingTypes.IMAGE
5454 drawing_object._dimensions = dimensions
5455 drawing_object._description = image.image_name
5456 drawing_object._width = width
5457 drawing_object._height = height
5458 drawing_object._shape = None
5459 drawing_object._anchor = image._anchor
5460 drawing_object._rel_index = 0
5461 drawing_object._decorative = image._decorative
5463 if image.description is not None:
5464 drawing_object._description = image.description
5466 if image._url:
5467 url = image._url
5468 target = url._target()
5469 target_mode = url._target_mode()
5471 if not self.drawing_rels.get(url._link):
5472 self.drawing_links.append(["/hyperlink", target, target_mode])
5474 url._rel_index = self._get_drawing_rel_index(url._link)
5475 drawing_object._url = url
5477 if not self.drawing_rels.get(image._digest):
5478 self.drawing_links.append(
5479 [
5480 "/image",
5481 "../media/image" + str(image_id) + "." + image._image_extension,
5482 ]
5483 )
5485 drawing_object._rel_index = self._get_drawing_rel_index(image._digest)
5486 drawing._add_drawing_object(drawing_object)
5488 def _prepare_shape(self, index, drawing_id) -> None:
5489 # Set up shapes/drawings.
5490 (
5491 row,
5492 col,
5493 x_offset,
5494 y_offset,
5495 x_scale,
5496 y_scale,
5497 text,
5498 anchor,
5499 options,
5500 description,
5501 decorative,
5502 ) = self.shapes[index]
5504 width = options.get("width", self.default_col_width * 3)
5505 height = options.get("height", self.default_row_height * 6)
5507 width *= x_scale
5508 height *= y_scale
5510 dimensions = self._position_object_emus(
5511 col, row, x_offset, y_offset, width, height, anchor
5512 )
5514 # Convert from pixels to emus.
5515 width = int(0.5 + (width * 9525))
5516 height = int(0.5 + (height * 9525))
5518 # Create a Drawing obj to use with worksheet unless one already exists.
5519 if not self.drawing:
5520 drawing = Drawing()
5521 drawing.embedded = 1
5522 self.drawing = drawing
5524 self.external_drawing_links.append(
5525 ["/drawing", "../drawings/drawing" + str(drawing_id) + ".xml", None]
5526 )
5527 else:
5528 drawing = self.drawing
5530 shape = Shape("rect", "TextBox", options)
5531 shape.text = text
5533 drawing_object = DrawingInfo()
5534 drawing_object._drawing_type = DrawingTypes.SHAPE
5535 drawing_object._dimensions = dimensions
5536 drawing_object._width = width
5537 drawing_object._height = height
5538 drawing_object._description = description
5539 drawing_object._shape = shape
5540 drawing_object._anchor = anchor
5541 drawing_object._rel_index = 0
5542 drawing_object._decorative = decorative
5544 url = Url.from_options(options)
5545 if url:
5546 target = url._target()
5547 target_mode = url._target_mode()
5549 if not self.drawing_rels.get(url._link):
5550 self.drawing_links.append(["/hyperlink", target, target_mode])
5552 url._rel_index = self._get_drawing_rel_index(url._link)
5553 drawing_object._url = url
5555 drawing._add_drawing_object(drawing_object)
5557 def _prepare_header_image(self, image_id, image) -> None:
5558 # Set up an image without a drawing object for header/footer images.
5560 # Strip the extension from the filename.
5561 image.image_name = re.sub(r"\..*$", "", image.image_name)
5563 if not self.vml_drawing_rels.get(image._digest):
5564 self.vml_drawing_links.append(
5565 [
5566 "/image",
5567 "../media/image" + str(image_id) + "." + image._image_extension,
5568 ]
5569 )
5571 image._ref_id = self._get_vml_drawing_rel_index(image._digest)
5573 self.header_images_list.append(image)
5575 def _prepare_background(self, image_id, image_extension) -> None:
5576 # Set up an image without a drawing object for backgrounds.
5577 self.external_background_links.append(
5578 ["/image", "../media/image" + str(image_id) + "." + image_extension]
5579 )
5581 def _prepare_chart(self, index, chart_id, drawing_id) -> None:
5582 # Set up chart/drawings.
5583 (
5584 row,
5585 col,
5586 chart,
5587 x_offset,
5588 y_offset,
5589 x_scale,
5590 y_scale,
5591 anchor,
5592 description,
5593 decorative,
5594 ) = self.charts[index]
5596 chart.id = chart_id - 1
5598 # Use user specified dimensions, if any.
5599 width = int(0.5 + (chart.width * x_scale))
5600 height = int(0.5 + (chart.height * y_scale))
5602 dimensions = self._position_object_emus(
5603 col, row, x_offset, y_offset, width, height, anchor
5604 )
5606 # Set the chart name for the embedded object if it has been specified.
5607 name = chart.chart_name
5609 # Create a Drawing obj to use with worksheet unless one already exists.
5610 if not self.drawing:
5611 drawing = Drawing()
5612 drawing.embedded = 1
5613 self.drawing = drawing
5615 self.external_drawing_links.append(
5616 ["/drawing", "../drawings/drawing" + str(drawing_id) + ".xml"]
5617 )
5618 else:
5619 drawing = self.drawing
5621 drawing_object = DrawingInfo()
5622 drawing_object._drawing_type = DrawingTypes.CHART
5623 drawing_object._dimensions = dimensions
5624 drawing_object._width = width
5625 drawing_object._height = height
5626 drawing_object._name = name
5627 drawing_object._shape = None
5628 drawing_object._anchor = anchor
5629 drawing_object._rel_index = self._get_drawing_rel_index()
5630 drawing_object._description = description
5631 drawing_object._decorative = decorative
5633 drawing._add_drawing_object(drawing_object)
5635 self.drawing_links.append(
5636 ["/chart", "../charts/chart" + str(chart_id) + ".xml"]
5637 )
5639 def _position_object_emus(
5640 self, col_start, row_start, x1, y1, width, height, anchor
5641 ):
5642 # Calculate the vertices that define the position of a graphical
5643 # object within the worksheet in EMUs.
5644 #
5645 # The vertices are expressed as English Metric Units (EMUs). There are
5646 # 12,700 EMUs per point. Therefore, 12,700 * 3 /4 = 9,525 EMUs per
5647 # pixel
5648 (
5649 col_start,
5650 row_start,
5651 x1,
5652 y1,
5653 col_end,
5654 row_end,
5655 x2,
5656 y2,
5657 x_abs,
5658 y_abs,
5659 ) = self._position_object_pixels(
5660 col_start, row_start, x1, y1, width, height, anchor
5661 )
5663 # Convert the pixel values to EMUs. See above.
5664 x1 = int(0.5 + 9525 * x1)
5665 y1 = int(0.5 + 9525 * y1)
5666 x2 = int(0.5 + 9525 * x2)
5667 y2 = int(0.5 + 9525 * y2)
5668 x_abs = int(0.5 + 9525 * x_abs)
5669 y_abs = int(0.5 + 9525 * y_abs)
5671 return (col_start, row_start, x1, y1, col_end, row_end, x2, y2, x_abs, y_abs)
5673 # Calculate the vertices that define the position of a graphical object
5674 # within the worksheet in pixels.
5675 #
5676 # +------------+------------+
5677 # | A | B |
5678 # +-----+------------+------------+
5679 # | |(x1,y1) | |
5680 # | 1 |(A1)._______|______ |
5681 # | | | | |
5682 # | | | | |
5683 # +-----+----| OBJECT |-----+
5684 # | | | | |
5685 # | 2 | |______________. |
5686 # | | | (B2)|
5687 # | | | (x2,y2)|
5688 # +---- +------------+------------+
5689 #
5690 # Example of an object that covers some of the area from cell A1 to B2.
5691 #
5692 # Based on the width and height of the object we need to calculate 8 vars:
5693 #
5694 # col_start, row_start, col_end, row_end, x1, y1, x2, y2.
5695 #
5696 # We also calculate the absolute x and y position of the top left vertex of
5697 # the object. This is required for images.
5698 #
5699 # The width and height of the cells that the object occupies can be
5700 # variable and have to be taken into account.
5701 #
5702 # The values of col_start and row_start are passed in from the calling
5703 # function. The values of col_end and row_end are calculated by
5704 # subtracting the width and height of the object from the width and
5705 # height of the underlying cells.
5706 #
5707 def _position_object_pixels(
5708 self, col_start, row_start, x1, y1, width, height, anchor
5709 ):
5710 # col_start # Col containing upper left corner of object.
5711 # x1 # Distance to left side of object.
5712 #
5713 # row_start # Row containing top left corner of object.
5714 # y1 # Distance to top of object.
5715 #
5716 # col_end # Col containing lower right corner of object.
5717 # x2 # Distance to right side of object.
5718 #
5719 # row_end # Row containing bottom right corner of object.
5720 # y2 # Distance to bottom of object.
5721 #
5722 # width # Width of object frame.
5723 # height # Height of object frame.
5724 #
5725 # x_abs # Absolute distance to left side of object.
5726 # y_abs # Absolute distance to top side of object.
5727 x_abs = 0
5728 y_abs = 0
5730 # Adjust start column for negative offsets.
5731 # pylint: disable=chained-comparison
5732 while x1 < 0 and col_start > 0:
5733 x1 += self._size_col(col_start - 1)
5734 col_start -= 1
5736 # Adjust start row for negative offsets.
5737 while y1 < 0 and row_start > 0:
5738 y1 += self._size_row(row_start - 1)
5739 row_start -= 1
5741 # Ensure that the image isn't shifted off the page at top left.
5742 x1 = max(0, x1)
5743 y1 = max(0, y1)
5745 # Calculate the absolute x offset of the top-left vertex.
5746 if self.col_size_changed:
5747 for col_id in range(col_start):
5748 x_abs += self._size_col(col_id)
5749 else:
5750 # Optimization for when the column widths haven't changed.
5751 x_abs += self.default_col_width * col_start
5753 x_abs += x1
5755 # Calculate the absolute y offset of the top-left vertex.
5756 if self.row_size_changed:
5757 for row_id in range(row_start):
5758 y_abs += self._size_row(row_id)
5759 else:
5760 # Optimization for when the row heights haven't changed.
5761 y_abs += self.default_row_height * row_start
5763 y_abs += y1
5765 # Adjust start column for offsets that are greater than the col width.
5766 while x1 >= self._size_col(col_start, anchor):
5767 x1 -= self._size_col(col_start)
5768 col_start += 1
5770 # Adjust start row for offsets that are greater than the row height.
5771 while y1 >= self._size_row(row_start, anchor):
5772 y1 -= self._size_row(row_start)
5773 row_start += 1
5775 # Initialize end cell to the same as the start cell.
5776 col_end = col_start
5777 row_end = row_start
5779 # Don't offset the image in the cell if the row/col is hidden.
5780 if self._size_col(col_start, anchor) > 0:
5781 width = width + x1
5782 if self._size_row(row_start, anchor) > 0:
5783 height = height + y1
5785 # Subtract the underlying cell widths to find end cell of the object.
5786 while width >= self._size_col(col_end, anchor):
5787 width -= self._size_col(col_end, anchor)
5788 col_end += 1
5790 # Subtract the underlying cell heights to find end cell of the object.
5791 while height >= self._size_row(row_end, anchor):
5792 height -= self._size_row(row_end, anchor)
5793 row_end += 1
5795 # The end vertices are whatever is left from the width and height.
5796 x2 = width
5797 y2 = height
5799 return [col_start, row_start, x1, y1, col_end, row_end, x2, y2, x_abs, y_abs]
5801 def _size_col(self, col: int, anchor=0):
5802 # Look up the cell value to see if it has been changed.
5803 if col in self.col_info:
5804 width = self.col_info[col].width
5805 hidden = self.col_info[col].hidden
5807 if width is None:
5808 width = self.default_col_width
5810 if hidden and anchor != 4:
5811 width = 0
5813 return width
5815 return self.default_col_width
5817 def _size_row(self, row: int, anchor=0):
5818 # Look up the cell value to see if it has been changed
5819 if row in self.row_sizes:
5820 height = self.row_sizes[row][0]
5821 hidden = self.row_sizes[row][1]
5823 if hidden and anchor != 4:
5824 height = 0
5826 return height
5828 return self.default_row_height
5830 def _pixels_to_height(self, pixels):
5831 # Convert the height of a cell from pixels to character units.
5832 return 0.75 * pixels
5834 def _comment_vertices(self, comment: CommentType):
5835 # Calculate the positions of the comment object.
5836 anchor = 0
5837 vertices = self._position_object_pixels(
5838 comment.start_col,
5839 comment.start_row,
5840 comment.x_offset,
5841 comment.y_offset,
5842 comment.width,
5843 comment.height,
5844 anchor,
5845 )
5847 # Add the width and height for VML.
5848 vertices.append(comment.width)
5849 vertices.append(comment.height)
5851 return vertices
5853 def _button_vertices(self, button: ButtonType):
5854 # Calculate the positions of the button object.
5855 anchor = 0
5856 vertices = self._position_object_pixels(
5857 button.col,
5858 button.row,
5859 button.x_offset,
5860 button.y_offset,
5861 button.width,
5862 button.height,
5863 anchor,
5864 )
5866 # Add the width and height for VML.
5867 vertices.append(button.width)
5868 vertices.append(button.height)
5870 return vertices
5872 def _prepare_vml_objects(
5873 self, vml_data_id, vml_shape_id, vml_drawing_id, comment_id
5874 ):
5875 comments = []
5876 # Sort the comments into row/column order for easier comparison
5877 # testing and set the external links for comments and buttons.
5878 row_nums = sorted(self.comments.keys())
5880 for row in row_nums:
5881 col_nums = sorted(self.comments[row].keys())
5883 for col in col_nums:
5884 comment = self.comments[row][col]
5885 comment.vertices = self._comment_vertices(comment)
5887 # Set comment visibility if required and not user defined.
5888 if comment.is_visible is None:
5889 comment.is_visible = self.comments_visible
5891 # Set comment author if not already user defined.
5892 if comment.author is None:
5893 comment.author = self.comments_author
5895 comments.append(comment)
5897 for button in self.buttons_list:
5898 button.vertices = self._button_vertices(button)
5900 self.external_vml_links.append(
5901 ["/vmlDrawing", "../drawings/vmlDrawing" + str(vml_drawing_id) + ".vml"]
5902 )
5904 if self.has_comments:
5905 self.comments_list = comments
5907 self.external_comment_links.append(
5908 ["/comments", "../comments" + str(comment_id) + ".xml"]
5909 )
5911 count = len(comments)
5912 start_data_id = vml_data_id
5914 # The VML o:idmap data id contains a comma separated range when there
5915 # is more than one 1024 block of comments, like this: data="1,2".
5916 for i in range(int(count / 1024)):
5917 data_id = start_data_id + i + 1
5918 vml_data_id = f"{vml_data_id},{data_id}"
5920 self.vml_data_id = vml_data_id
5921 self.vml_shape_id = vml_shape_id
5923 return count
5925 def _prepare_header_vml_objects(self, vml_header_id, vml_drawing_id) -> None:
5926 # Set up external linkage for VML header/footer images.
5928 self.vml_header_id = vml_header_id
5930 self.external_vml_links.append(
5931 ["/vmlDrawing", "../drawings/vmlDrawing" + str(vml_drawing_id) + ".vml"]
5932 )
5934 def _prepare_tables(self, table_id, seen) -> None:
5935 # Set the table ids for the worksheet tables.
5936 for table in self.tables:
5937 table["id"] = table_id
5939 if table.get("name") is None:
5940 # Set a default name.
5941 table["name"] = "Table" + str(table_id)
5943 # Check for duplicate table names.
5944 name = table["name"].lower()
5946 if name in seen:
5947 raise DuplicateTableName(
5948 f"Duplicate name '{table['name']}' used in worksheet.add_table()."
5949 )
5951 seen[name] = True
5953 # Store the link used for the rels file.
5954 self.external_table_links.append(
5955 ["/table", "../tables/table" + str(table_id) + ".xml"]
5956 )
5957 table_id += 1
5959 def _table_function_to_formula(self, function, col_name):
5960 # Convert a table total function to a worksheet formula.
5961 formula = ""
5963 # Escape special characters, as required by Excel.
5964 col_name = col_name.replace("'", "''")
5965 col_name = col_name.replace("#", "'#")
5966 col_name = col_name.replace("]", "']")
5967 col_name = col_name.replace("[", "'[")
5969 subtotals = {
5970 "average": 101,
5971 "countNums": 102,
5972 "count": 103,
5973 "max": 104,
5974 "min": 105,
5975 "stdDev": 107,
5976 "sum": 109,
5977 "var": 110,
5978 }
5980 if function in subtotals:
5981 func_num = subtotals[function]
5982 formula = f"SUBTOTAL({func_num},[{col_name}])"
5983 else:
5984 warn(f"Unsupported function '{function}' in add_table()")
5986 return formula
5988 def _set_spark_color(self, sparkline, options, user_color) -> None:
5989 # Set the sparkline color.
5990 if user_color not in options:
5991 return
5993 sparkline[user_color] = Color._from_value(options[user_color])
5995 def _get_range_data(self, row_start, col_start, row_end, col_end):
5996 # Returns a range of data from the worksheet _table to be used in
5997 # chart cached data. Strings are returned as SST ids and decoded
5998 # in the workbook. Return None for data that doesn't exist since
5999 # Excel can chart have series with data missing.
6001 if self.constant_memory:
6002 return ()
6004 data = []
6006 # Iterate through the table data.
6007 for row_num in range(row_start, row_end + 1):
6008 # Store None if row doesn't exist.
6009 if row_num not in self.table:
6010 data.append(None)
6011 continue
6013 for col_num in range(col_start, col_end + 1):
6014 if col_num in self.table[row_num]:
6015 cell = self.table[row_num][col_num]
6017 cell_type = cell.__class__.__name__
6019 if cell_type in ("Number", "Datetime"):
6020 # Return a number with Excel's precision.
6021 data.append(f"{cell.number:.16g}")
6023 elif cell_type == "String":
6024 # Return a string from it's shared string index.
6025 index = cell.string
6026 string = self.str_table._get_shared_string(index)
6028 data.append(string)
6030 elif cell_type in ("Formula", "ArrayFormula"):
6031 # Return the formula value.
6032 value = cell.value
6034 if value is None:
6035 value = 0
6037 data.append(value)
6039 elif cell_type == "Blank":
6040 # Return a empty cell.
6041 data.append("")
6042 else:
6043 # Store None if column doesn't exist.
6044 data.append(None)
6046 return data
6048 def _csv_join(self, *items):
6049 # Create a csv string for use with data validation formulas and lists.
6051 # Convert non string types to string.
6052 items = [str(item) if not isinstance(item, str) else item for item in items]
6054 return ",".join(items)
6056 def _escape_url(self, url):
6057 # Don't escape URL if it looks already escaped.
6058 if re.search("%[0-9a-fA-F]{2}", url):
6059 return url
6061 # Can't use url.quote() here because it doesn't match Excel.
6062 url = url.replace("%", "%25")
6063 url = url.replace('"', "%22")
6064 url = url.replace(" ", "%20")
6065 url = url.replace("<", "%3c")
6066 url = url.replace(">", "%3e")
6067 url = url.replace("[", "%5b")
6068 url = url.replace("]", "%5d")
6069 url = url.replace("^", "%5e")
6070 url = url.replace("`", "%60")
6071 url = url.replace("{", "%7b")
6072 url = url.replace("}", "%7d")
6074 return url
6076 def _get_drawing_rel_index(self, target=None):
6077 # Get the index used to address a drawing rel link.
6078 if target is None:
6079 self.drawing_rels_id += 1
6080 return self.drawing_rels_id
6082 if self.drawing_rels.get(target):
6083 return self.drawing_rels[target]
6085 self.drawing_rels_id += 1
6086 self.drawing_rels[target] = self.drawing_rels_id
6087 return self.drawing_rels_id
6089 def _get_vml_drawing_rel_index(self, target=None):
6090 # Get the index used to address a vml drawing rel link.
6091 if self.vml_drawing_rels.get(target):
6092 return self.vml_drawing_rels[target]
6094 self.vml_drawing_rels_id += 1
6095 self.vml_drawing_rels[target] = self.vml_drawing_rels_id
6096 return self.vml_drawing_rels_id
6098 ###########################################################################
6099 #
6100 # The following font methods are mainly duplicated from the Styles class
6101 # with appropriate changes for rich string styles.
6102 #
6103 ###########################################################################
6104 def _write_font(self, xf_format) -> None:
6105 # Write the <font> element.
6106 xml_writer = self.rstring
6108 xml_writer._xml_start_tag("rPr")
6110 # Handle the main font properties.
6111 if xf_format.bold:
6112 xml_writer._xml_empty_tag("b")
6113 if xf_format.italic:
6114 xml_writer._xml_empty_tag("i")
6115 if xf_format.font_strikeout:
6116 xml_writer._xml_empty_tag("strike")
6117 if xf_format.font_outline:
6118 xml_writer._xml_empty_tag("outline")
6119 if xf_format.font_shadow:
6120 xml_writer._xml_empty_tag("shadow")
6122 # Handle the underline variants.
6123 if xf_format.underline:
6124 self._write_underline(xf_format.underline)
6126 # Handle super/subscript.
6127 if xf_format.font_script == 1:
6128 self._write_vert_align("superscript")
6129 if xf_format.font_script == 2:
6130 self._write_vert_align("subscript")
6132 # Write the font size
6133 xml_writer._xml_empty_tag("sz", [("val", xf_format.font_size)])
6135 # Handle colors.
6136 if xf_format.theme == -1:
6137 # Ignore for excel2003_style.
6138 pass
6139 elif xf_format.theme:
6140 self._write_rstring_color("color", [("theme", xf_format.theme)])
6141 elif xf_format.color_indexed:
6142 self._write_rstring_color("color", [("indexed", xf_format.color_indexed)])
6143 elif xf_format.font_color:
6144 color = xf_format.font_color
6145 if not color._is_automatic:
6146 self._write_rstring_color("color", color._attributes())
6147 else:
6148 self._write_rstring_color("color", [("theme", 1)])
6150 # Write some other font properties related to font families.
6151 xml_writer._xml_empty_tag("rFont", [("val", xf_format.font_name)])
6152 xml_writer._xml_empty_tag("family", [("val", xf_format.font_family)])
6154 if xf_format.font_name == "Calibri" and not xf_format.hyperlink:
6155 xml_writer._xml_empty_tag("scheme", [("val", xf_format.font_scheme)])
6157 xml_writer._xml_end_tag("rPr")
6159 def _write_underline(self, underline) -> None:
6160 # Write the underline font element.
6161 attributes = []
6163 # Handle the underline variants.
6164 if underline == 2:
6165 attributes = [("val", "double")]
6166 elif underline == 33:
6167 attributes = [("val", "singleAccounting")]
6168 elif underline == 34:
6169 attributes = [("val", "doubleAccounting")]
6171 self.rstring._xml_empty_tag("u", attributes)
6173 def _write_vert_align(self, val) -> None:
6174 # Write the <vertAlign> font sub-element.
6175 attributes = [("val", val)]
6177 self.rstring._xml_empty_tag("vertAlign", attributes)
6179 def _write_rstring_color(self, name, attributes) -> None:
6180 # Write the <color> element.
6181 self.rstring._xml_empty_tag(name, attributes)
6183 def _opt_close(self) -> None:
6184 # Close the row data filehandle in constant_memory mode.
6185 if not self.row_data_fh_closed:
6186 self.row_data_fh.close()
6187 self.row_data_fh_closed = True
6189 def _opt_reopen(self) -> None:
6190 # Reopen the row data filehandle in constant_memory mode.
6191 if self.row_data_fh_closed:
6192 filename = self.row_data_filename
6193 # pylint: disable=consider-using-with
6194 self.row_data_fh = open(filename, mode="a+", encoding="utf-8")
6195 self.row_data_fh_closed = False
6196 self.fh = self.row_data_fh
6198 def _set_icon_props(self, total_icons, user_props=None):
6199 # Set the sub-properties for icons.
6200 props = []
6202 # Set the defaults.
6203 for _ in range(total_icons):
6204 props.append({"criteria": False, "value": 0, "type": "percent"})
6206 # Set the default icon values based on the number of icons.
6207 if total_icons == 3:
6208 props[0]["value"] = 67
6209 props[1]["value"] = 33
6211 if total_icons == 4:
6212 props[0]["value"] = 75
6213 props[1]["value"] = 50
6214 props[2]["value"] = 25
6216 if total_icons == 5:
6217 props[0]["value"] = 80
6218 props[1]["value"] = 60
6219 props[2]["value"] = 40
6220 props[3]["value"] = 20
6222 # Overwrite default properties with user defined properties.
6223 if user_props:
6224 # Ensure we don't set user properties for lowest icon.
6225 max_data = len(user_props)
6226 if max_data >= total_icons:
6227 max_data = total_icons - 1
6229 for i in range(max_data):
6230 # Set the user defined 'value' property.
6231 if user_props[i].get("value") is not None:
6232 props[i]["value"] = user_props[i]["value"]
6234 # Remove the formula '=' sign if it exists.
6235 tmp = props[i]["value"]
6236 if isinstance(tmp, str) and tmp.startswith("="):
6237 props[i]["value"] = tmp.lstrip("=")
6239 # Set the user defined 'type' property.
6240 if user_props[i].get("type"):
6241 valid_types = ("percent", "percentile", "number", "formula")
6243 if user_props[i]["type"] not in valid_types:
6244 warn(
6245 f"Unknown icon property type '{user_props[i]['type']}' "
6246 f"for sub-property 'type' in conditional_format()."
6247 )
6248 else:
6249 props[i]["type"] = user_props[i]["type"]
6251 if props[i]["type"] == "number":
6252 props[i]["type"] = "num"
6254 # Set the user defined 'criteria' property.
6255 criteria = user_props[i].get("criteria")
6256 if criteria and criteria == ">":
6257 props[i]["criteria"] = True
6259 return props
6261 ###########################################################################
6262 #
6263 # XML methods.
6264 #
6265 ###########################################################################
6267 def _write_worksheet(self) -> None:
6268 # Write the <worksheet> element. This is the root element.
6270 schema = "http://schemas.openxmlformats.org/"
6271 xmlns = schema + "spreadsheetml/2006/main"
6272 xmlns_r = schema + "officeDocument/2006/relationships"
6273 xmlns_mc = schema + "markup-compatibility/2006"
6274 ms_schema = "http://schemas.microsoft.com/"
6275 xmlns_x14ac = ms_schema + "office/spreadsheetml/2009/9/ac"
6277 attributes = [("xmlns", xmlns), ("xmlns:r", xmlns_r)]
6279 # Add some extra attributes for Excel 2010. Mainly for sparklines.
6280 if self.excel_version == 2010:
6281 attributes.append(("xmlns:mc", xmlns_mc))
6282 attributes.append(("xmlns:x14ac", xmlns_x14ac))
6283 attributes.append(("mc:Ignorable", "x14ac"))
6285 self._xml_start_tag("worksheet", attributes)
6287 def _write_dimension(self) -> None:
6288 # Write the <dimension> element. This specifies the range of
6289 # cells in the worksheet. As a special case, empty
6290 # spreadsheets use 'A1' as a range.
6292 if self.dim_rowmin is None and self.dim_colmin is None:
6293 # If the min dimensions are not defined then no dimensions
6294 # have been set and we use the default 'A1'.
6295 ref = "A1"
6297 elif self.dim_rowmin is None and self.dim_colmin is not None:
6298 # If the row dimensions aren't set but the column
6299 # dimensions are set then they have been changed via
6300 # set_column().
6302 if self.dim_colmin == self.dim_colmax:
6303 # The dimensions are a single cell and not a range.
6304 ref = xl_rowcol_to_cell(0, self.dim_colmin)
6305 else:
6306 # The dimensions are a cell range.
6307 cell_1 = xl_rowcol_to_cell(0, self.dim_colmin)
6308 cell_2 = xl_rowcol_to_cell(0, self.dim_colmax)
6309 ref = cell_1 + ":" + cell_2
6311 elif self.dim_rowmin == self.dim_rowmax and self.dim_colmin == self.dim_colmax:
6312 # The dimensions are a single cell and not a range.
6313 ref = xl_rowcol_to_cell(self.dim_rowmin, self.dim_colmin)
6314 else:
6315 # The dimensions are a cell range.
6316 cell_1 = xl_rowcol_to_cell(self.dim_rowmin, self.dim_colmin)
6317 cell_2 = xl_rowcol_to_cell(self.dim_rowmax, self.dim_colmax)
6318 ref = cell_1 + ":" + cell_2
6320 self._xml_empty_tag("dimension", [("ref", ref)])
6322 def _write_sheet_views(self) -> None:
6323 # Write the <sheetViews> element.
6324 self._xml_start_tag("sheetViews")
6326 # Write the sheetView element.
6327 self._write_sheet_view()
6329 self._xml_end_tag("sheetViews")
6331 def _write_sheet_view(self) -> None:
6332 # Write the <sheetViews> element.
6333 attributes = []
6335 # Hide screen gridlines if required.
6336 if not self.screen_gridlines:
6337 attributes.append(("showGridLines", 0))
6339 # Hide screen row/column headers.
6340 if self.row_col_headers:
6341 attributes.append(("showRowColHeaders", 0))
6343 # Hide zeroes in cells.
6344 if not self.show_zeros:
6345 attributes.append(("showZeros", 0))
6347 # Display worksheet right to left for Hebrew, Arabic and others.
6348 if self.is_right_to_left:
6349 attributes.append(("rightToLeft", 1))
6351 # Show that the sheet tab is selected.
6352 if self.selected:
6353 attributes.append(("tabSelected", 1))
6355 # Turn outlines off. Also required in the outlinePr element.
6356 if not self.outline_on:
6357 attributes.append(("showOutlineSymbols", 0))
6359 # Set the page view/layout mode if required.
6360 if self.page_view == 1:
6361 attributes.append(("view", "pageLayout"))
6362 elif self.page_view == 2:
6363 attributes.append(("view", "pageBreakPreview"))
6365 # Set the first visible cell.
6366 if self.top_left_cell != "":
6367 attributes.append(("topLeftCell", self.top_left_cell))
6369 # Set the zoom level.
6370 if self.zoom != 100:
6371 attributes.append(("zoomScale", self.zoom))
6373 if self.page_view == 0 and self.zoom_scale_normal:
6374 attributes.append(("zoomScaleNormal", self.zoom))
6375 if self.page_view == 1:
6376 attributes.append(("zoomScalePageLayoutView", self.zoom))
6377 if self.page_view == 2:
6378 attributes.append(("zoomScaleSheetLayoutView", self.zoom))
6380 attributes.append(("workbookViewId", 0))
6382 if self.is_chartsheet and self.zoom_to_fit:
6383 attributes.append(("zoomToFit", 1))
6385 if self.panes or self.selections:
6386 self._xml_start_tag("sheetView", attributes)
6387 self._write_panes()
6388 self._write_selections()
6389 self._xml_end_tag("sheetView")
6390 else:
6391 self._xml_empty_tag("sheetView", attributes)
6393 def _write_sheet_format_pr(self) -> None:
6394 # Write the <sheetFormatPr> element.
6395 height_in_chars = self.default_row_height * 0.75
6396 row_level = self.outline_row_level
6397 col_level = self.outline_col_level
6399 attributes = [("defaultRowHeight", f"{height_in_chars:.16g}")]
6401 if self.default_row_height != self.original_row_height:
6402 attributes.append(("customHeight", 1))
6404 if self.default_row_zeroed:
6405 attributes.append(("zeroHeight", 1))
6407 if row_level:
6408 attributes.append(("outlineLevelRow", row_level))
6409 if col_level:
6410 attributes.append(("outlineLevelCol", col_level))
6412 if self.excel_version == 2010:
6413 attributes.append(("x14ac:dyDescent", "0.25"))
6415 self._xml_empty_tag("sheetFormatPr", attributes)
6417 def _write_cols(self) -> None:
6418 # Write the <cols> element and <col> sub elements.
6420 # Exit unless some column have been formatted.
6421 if not self.col_info:
6422 return
6424 self._xml_start_tag("cols")
6426 # Use the first element of the column information structures to set
6427 # the initial/previous properties.
6428 first_col = (sorted(self.col_info.keys()))[0]
6429 last_col = first_col
6430 prev_col_options = self.col_info[first_col]
6431 del self.col_info[first_col]
6432 deleted_col = first_col
6433 deleted_col_options = prev_col_options
6435 for col in sorted(self.col_info.keys()):
6436 col_options = self.col_info[col]
6437 # Check if the column number is contiguous with the previous
6438 # column and if the properties are the same.
6439 if col == last_col + 1 and col_options == prev_col_options:
6440 last_col = col
6441 else:
6442 # If not contiguous/equal then we write out the current range
6443 # of columns and start again.
6444 self._write_col_info(first_col, last_col, prev_col_options)
6445 first_col = col
6446 last_col = first_col
6447 prev_col_options = col_options
6449 # We will exit the previous loop with one unhandled column range.
6450 self._write_col_info(first_col, last_col, prev_col_options)
6452 # Put back the deleted first column information structure.
6453 self.col_info[deleted_col] = deleted_col_options
6455 self._xml_end_tag("cols")
6457 def _write_col_info(self, col_min: int, col_max: int, col_info: ColumnInfo) -> None:
6458 # Write the <col> element.
6459 width = col_info.width
6460 has_custom_width = True
6461 xf_index = 0
6463 # Get the cell_format index.
6464 if col_info.column_format:
6465 xf_index = col_info.column_format._get_xf_index()
6467 # Set the Excel default column width.
6468 if width is None:
6469 if not col_info.hidden:
6470 width = self.default_col_width
6471 has_custom_width = False
6472 else:
6473 width = 0
6474 elif width == self.default_col_width:
6475 # Width is defined but same as default.
6476 has_custom_width = False
6478 # Convert column width from pixels to character width.
6479 char_width = (width * 256 // self.max_digit_width) / 256.0
6481 attributes = [
6482 ("min", col_min + 1),
6483 ("max", col_max + 1),
6484 ("width", f"{char_width:.16g}"),
6485 ]
6487 if xf_index:
6488 attributes.append(("style", xf_index))
6489 if col_info.hidden:
6490 attributes.append(("hidden", "1"))
6491 if col_info.autofit:
6492 attributes.append(("bestFit", "1"))
6493 if has_custom_width:
6494 attributes.append(("customWidth", "1"))
6495 if col_info.level:
6496 attributes.append(("outlineLevel", col_info.level))
6497 if col_info.collapsed:
6498 attributes.append(("collapsed", "1"))
6500 self._xml_empty_tag("col", attributes)
6502 def _write_sheet_data(self) -> None:
6503 # Write the <sheetData> element.
6504 if self.dim_rowmin is None:
6505 # If the dimensions aren't defined there is no data to write.
6506 self._xml_empty_tag("sheetData")
6507 else:
6508 self._xml_start_tag("sheetData")
6509 self._write_rows()
6510 self._xml_end_tag("sheetData")
6512 def _write_optimized_sheet_data(self) -> None:
6513 # Write the <sheetData> element when constant_memory is on. In this
6514 # case we read the data stored in the temp file and rewrite it to the
6515 # XML sheet file.
6516 if self.dim_rowmin is None:
6517 # If the dimensions aren't defined then there is no data to write.
6518 self._xml_empty_tag("sheetData")
6519 else:
6520 self._xml_start_tag("sheetData")
6522 # Rewind the filehandle that was used for temp row data.
6523 buff_size = 65536
6524 self.row_data_fh.seek(0)
6525 data = self.row_data_fh.read(buff_size)
6527 while data:
6528 self.fh.write(data)
6529 data = self.row_data_fh.read(buff_size)
6531 self.row_data_fh.close()
6532 os.unlink(self.row_data_filename)
6534 self._xml_end_tag("sheetData")
6536 def _write_page_margins(self) -> None:
6537 # Write the <pageMargins> element.
6538 attributes = [
6539 ("left", self.margin_left),
6540 ("right", self.margin_right),
6541 ("top", self.margin_top),
6542 ("bottom", self.margin_bottom),
6543 ("header", self.margin_header),
6544 ("footer", self.margin_footer),
6545 ]
6547 self._xml_empty_tag("pageMargins", attributes)
6549 def _write_page_setup(self) -> None:
6550 # Write the <pageSetup> element.
6551 #
6552 # The following is an example taken from Excel.
6553 #
6554 # <pageSetup
6555 # paperSize="9"
6556 # scale="110"
6557 # fitToWidth="2"
6558 # fitToHeight="2"
6559 # pageOrder="overThenDown"
6560 # orientation="portrait"
6561 # blackAndWhite="1"
6562 # draft="1"
6563 # horizontalDpi="200"
6564 # verticalDpi="200"
6565 # r:id="rId1"
6566 # />
6567 #
6568 attributes = []
6570 # Skip this element if no page setup has changed.
6571 if not self.page_setup_changed:
6572 return
6574 # Set paper size.
6575 if self.paper_size:
6576 attributes.append(("paperSize", self.paper_size))
6578 # Set the print_scale.
6579 if self.print_scale != 100:
6580 attributes.append(("scale", self.print_scale))
6582 # Set the "Fit to page" properties.
6583 if self.fit_page and self.fit_width != 1:
6584 attributes.append(("fitToWidth", self.fit_width))
6586 if self.fit_page and self.fit_height != 1:
6587 attributes.append(("fitToHeight", self.fit_height))
6589 # Set the page print direction.
6590 if self.page_order:
6591 attributes.append(("pageOrder", "overThenDown"))
6593 # Set start page for printing.
6594 if self.page_start > 1:
6595 attributes.append(("firstPageNumber", self.page_start))
6597 # Set page orientation.
6598 if self.orientation:
6599 attributes.append(("orientation", "portrait"))
6600 else:
6601 attributes.append(("orientation", "landscape"))
6603 # Set the print in black and white option.
6604 if self.black_white:
6605 attributes.append(("blackAndWhite", "1"))
6607 # Set start page for printing.
6608 if self.page_start != 0:
6609 attributes.append(("useFirstPageNumber", "1"))
6611 # Set the DPI. Mainly only for testing.
6612 if self.is_chartsheet:
6613 if self.horizontal_dpi:
6614 attributes.append(("horizontalDpi", self.horizontal_dpi))
6616 if self.vertical_dpi:
6617 attributes.append(("verticalDpi", self.vertical_dpi))
6618 else:
6619 if self.vertical_dpi:
6620 attributes.append(("verticalDpi", self.vertical_dpi))
6622 if self.horizontal_dpi:
6623 attributes.append(("horizontalDpi", self.horizontal_dpi))
6625 self._xml_empty_tag("pageSetup", attributes)
6627 def _write_print_options(self) -> None:
6628 # Write the <printOptions> element.
6629 attributes = []
6631 if not self.print_options_changed:
6632 return
6634 # Set horizontal centering.
6635 if self.hcenter:
6636 attributes.append(("horizontalCentered", 1))
6638 # Set vertical centering.
6639 if self.vcenter:
6640 attributes.append(("verticalCentered", 1))
6642 # Enable row and column headers.
6643 if self.print_headers:
6644 attributes.append(("headings", 1))
6646 # Set printed gridlines.
6647 if self.print_gridlines:
6648 attributes.append(("gridLines", 1))
6650 self._xml_empty_tag("printOptions", attributes)
6652 def _write_header_footer(self) -> None:
6653 # Write the <headerFooter> element.
6654 attributes = []
6656 if not self.header_footer_scales:
6657 attributes.append(("scaleWithDoc", 0))
6659 if not self.header_footer_aligns:
6660 attributes.append(("alignWithMargins", 0))
6662 if self.header_footer_changed:
6663 self._xml_start_tag("headerFooter", attributes)
6664 if self.header:
6665 self._write_odd_header()
6666 if self.footer:
6667 self._write_odd_footer()
6668 self._xml_end_tag("headerFooter")
6669 elif self.excel2003_style:
6670 self._xml_empty_tag("headerFooter", attributes)
6672 def _write_odd_header(self) -> None:
6673 # Write the <headerFooter> element.
6674 self._xml_data_element("oddHeader", self.header)
6676 def _write_odd_footer(self) -> None:
6677 # Write the <headerFooter> element.
6678 self._xml_data_element("oddFooter", self.footer)
6680 def _write_rows(self) -> None:
6681 # Write out the worksheet data as a series of rows and cells.
6682 self._calculate_spans()
6684 for row_num in range(self.dim_rowmin, self.dim_rowmax + 1):
6685 if (
6686 row_num in self.row_info
6687 or row_num in self.comments
6688 or self.table[row_num]
6689 ):
6690 # Only process rows with formatting, cell data and/or comments.
6692 span_index = int(row_num / 16)
6694 if span_index in self.row_spans:
6695 span = self.row_spans[span_index]
6696 else:
6697 span = None
6699 if self.table[row_num]:
6700 # Write the cells if the row contains data.
6701 if row_num not in self.row_info:
6702 self._write_row(row_num, span)
6703 else:
6704 self._write_row(row_num, span, self.row_info[row_num])
6706 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6707 if col_num in self.table[row_num]:
6708 col_ref = self.table[row_num][col_num]
6709 self._write_cell(row_num, col_num, col_ref)
6711 self._xml_end_tag("row")
6713 elif row_num in self.comments:
6714 # Row with comments in cells.
6715 if row_num not in self.row_info:
6716 self._write_empty_row(row_num, span, None)
6717 else:
6718 self._write_empty_row(row_num, span, self.row_info[row_num])
6719 else:
6720 # Blank row with attributes only.
6721 if row_num not in self.row_info:
6722 self._write_empty_row(row_num, span, None)
6723 else:
6724 self._write_empty_row(row_num, span, self.row_info[row_num])
6726 def _write_single_row(self, current_row_num=0) -> None:
6727 # Write out the worksheet data as a single row with cells.
6728 # This method is used when constant_memory is on. A single
6729 # row is written and the data table is reset. That way only
6730 # one row of data is kept in memory at any one time. We don't
6731 # write span data in the optimized case since it is optional.
6733 # Set the new previous row as the current row.
6734 row_num = self.previous_row
6735 self.previous_row = current_row_num
6737 if row_num in self.row_info or row_num in self.comments or self.table[row_num]:
6738 # Only process rows with formatting, cell data and/or comments.
6740 # No span data in optimized mode.
6741 span = None
6743 if self.table[row_num]:
6744 # Write the cells if the row contains data.
6745 if row_num not in self.row_info:
6746 self._write_row(row_num, span)
6747 else:
6748 self._write_row(row_num, span, self.row_info[row_num])
6750 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6751 if col_num in self.table[row_num]:
6752 col_ref = self.table[row_num][col_num]
6753 self._write_cell(row_num, col_num, col_ref)
6755 self._xml_end_tag("row")
6756 else:
6757 # Row attributes or comments only.
6758 self._write_empty_row(row_num, span, self.row_info[row_num])
6760 # Reset table.
6761 self.table.clear()
6763 def _calculate_spans(self) -> None:
6764 # Calculate the "spans" attribute of the <row> tag. This is an
6765 # XLSX optimization and isn't strictly required. However, it
6766 # makes comparing files easier. The span is the same for each
6767 # block of 16 rows.
6768 spans = {}
6769 span_min = None
6770 span_max = None
6772 for row_num in range(self.dim_rowmin, self.dim_rowmax + 1):
6773 if row_num in self.table:
6774 # Calculate spans for cell data.
6775 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6776 if col_num in self.table[row_num]:
6777 if span_min is None:
6778 span_min = col_num
6779 span_max = col_num
6780 else:
6781 span_min = min(span_min, col_num)
6782 span_max = max(span_max, col_num)
6784 if row_num in self.comments:
6785 # Calculate spans for comments.
6786 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6787 if row_num in self.comments and col_num in self.comments[row_num]:
6788 if span_min is None:
6789 span_min = col_num
6790 span_max = col_num
6791 else:
6792 span_min = min(span_min, col_num)
6793 span_max = max(span_max, col_num)
6795 if ((row_num + 1) % 16 == 0) or row_num == self.dim_rowmax:
6796 span_index = int(row_num / 16)
6798 if span_min is not None:
6799 span_min += 1
6800 span_max += 1
6801 spans[span_index] = f"{span_min}:{span_max}"
6802 span_min = None
6804 self.row_spans = spans
6806 def _write_row(
6807 self,
6808 row: int,
6809 spans: Optional[str],
6810 row_info: Optional[RowInfo] = None,
6811 empty_row: bool = False,
6812 ) -> None:
6813 # Write the <row> element.
6814 xf_index = 0
6816 if row_info:
6817 height = row_info.height
6818 row_format = row_info.row_format
6819 hidden = row_info.hidden
6820 level = row_info.level
6821 collapsed = row_info.collapsed
6822 else:
6823 height = None
6824 row_format = None
6825 hidden = 0
6826 level = 0
6827 collapsed = 0
6829 if height is None:
6830 height = self.default_row_height
6832 attributes = [("r", row + 1)]
6834 # Get the cell_format index.
6835 if row_format:
6836 xf_index = row_format._get_xf_index()
6838 # Add row attributes where applicable.
6839 if spans:
6840 attributes.append(("spans", spans))
6842 if xf_index:
6843 attributes.append(("s", xf_index))
6845 if row_format:
6846 attributes.append(("customFormat", 1))
6848 if height != self.original_row_height or (
6849 height == self.original_row_height and height != self.default_row_height
6850 ):
6851 height_in_chars = height * 0.75
6852 attributes.append(("ht", f"{height_in_chars:.16g}"))
6854 if hidden:
6855 attributes.append(("hidden", 1))
6857 if height != self.original_row_height or (
6858 height == self.original_row_height and height != self.default_row_height
6859 ):
6860 attributes.append(("customHeight", 1))
6862 if level:
6863 attributes.append(("outlineLevel", level))
6865 if collapsed:
6866 attributes.append(("collapsed", 1))
6868 if self.excel_version == 2010:
6869 attributes.append(("x14ac:dyDescent", "0.25"))
6871 if empty_row:
6872 self._xml_empty_tag_unencoded("row", attributes)
6873 else:
6874 self._xml_start_tag_unencoded("row", attributes)
6876 def _write_empty_row(
6877 self, row: int, spans: Optional[str], row_info: Optional[RowInfo] = None
6878 ) -> None:
6879 # Write and empty <row> element.
6880 self._write_row(row, spans, row_info, empty_row=True)
6882 def _write_cell(self, row: int, col: int, cell) -> None:
6883 # Write the <cell> element.
6884 # Note. This is the innermost loop so efficiency is important.
6886 cell_range = xl_rowcol_to_cell_fast(row, col)
6887 attributes = [("r", cell_range)]
6889 if cell.format:
6890 # Add the cell format index.
6891 xf_index = cell.format._get_xf_index()
6892 attributes.append(("s", xf_index))
6893 elif row in self.row_info and self.row_info[row].row_format:
6894 # Add the row format.
6895 row_format = self.row_info[row].row_format
6896 attributes.append(("s", row_format._get_xf_index()))
6897 elif col in self.col_info:
6898 # Add the column format.
6899 column_format = self.col_info[col].column_format
6900 if column_format is not None:
6901 attributes.append(("s", column_format._get_xf_index()))
6903 type_cell_name = cell.__class__.__name__
6905 # Write the various cell types.
6906 if type_cell_name in ("Number", "Datetime"):
6907 # Write a number.
6908 self._xml_number_element(cell.number, attributes)
6910 elif type_cell_name in ("String", "RichString"):
6911 # Write a string.
6912 string = cell.string
6914 if not self.constant_memory:
6915 # Write a shared string.
6916 self._xml_string_element(string, attributes)
6917 else:
6918 # Write an optimized in-line string.
6920 # Convert control character to a _xHHHH_ escape.
6921 string = self._escape_control_characters(string)
6923 # Write any rich strings without further tags.
6924 if string.startswith("<r>") and string.endswith("</r>"):
6925 self._xml_rich_inline_string(string, attributes)
6926 else:
6927 # Add attribute to preserve leading or trailing whitespace.
6928 preserve = _preserve_whitespace(string)
6929 self._xml_inline_string(string, preserve, attributes)
6931 elif type_cell_name == "Formula":
6932 # Write a formula. First check the formula value type.
6933 value = cell.value
6934 if isinstance(cell.value, bool):
6935 attributes.append(("t", "b"))
6936 if cell.value:
6937 value = 1
6938 else:
6939 value = 0
6941 elif isinstance(cell.value, str):
6942 error_codes = (
6943 "#DIV/0!",
6944 "#N/A",
6945 "#NAME?",
6946 "#NULL!",
6947 "#NUM!",
6948 "#REF!",
6949 "#VALUE!",
6950 )
6952 if cell.value == "":
6953 # Allow blank to force recalc in some third party apps.
6954 pass
6955 elif cell.value in error_codes:
6956 attributes.append(("t", "e"))
6957 else:
6958 attributes.append(("t", "str"))
6960 self._xml_formula_element(cell.formula, value, attributes)
6962 elif type_cell_name == "ArrayFormula":
6963 # Write a array formula.
6965 if cell.atype == "dynamic":
6966 attributes.append(("cm", 1))
6968 # First check if the formula value is a string.
6969 try:
6970 float(cell.value)
6971 except ValueError:
6972 attributes.append(("t", "str"))
6974 # Write an array formula.
6975 self._xml_start_tag("c", attributes)
6977 self._write_cell_array_formula(cell.formula, cell.range)
6978 self._write_cell_value(cell.value)
6979 self._xml_end_tag("c")
6981 elif type_cell_name == "Blank":
6982 # Write a empty cell.
6983 self._xml_empty_tag("c", attributes)
6985 elif type_cell_name == "Boolean":
6986 # Write a boolean cell.
6987 attributes.append(("t", "b"))
6988 self._xml_start_tag("c", attributes)
6989 self._write_cell_value(cell.boolean)
6990 self._xml_end_tag("c")
6992 elif type_cell_name == "Error":
6993 # Write a boolean cell.
6994 attributes.append(("t", "e"))
6995 attributes.append(("vm", cell.value))
6996 self._xml_start_tag("c", attributes)
6997 self._write_cell_value(cell.error)
6998 self._xml_end_tag("c")
7000 def _write_cell_value(self, value) -> None:
7001 # Write the cell value <v> element.
7002 if value is None:
7003 value = ""
7005 self._xml_data_element("v", value)
7007 def _write_cell_array_formula(self, formula, cell_range) -> None:
7008 # Write the cell array formula <f> element.
7009 attributes = [("t", "array"), ("ref", cell_range)]
7011 self._xml_data_element("f", formula, attributes)
7013 def _write_sheet_pr(self) -> None:
7014 # Write the <sheetPr> element for Sheet level properties.
7015 attributes = []
7017 if (
7018 not self.fit_page
7019 and not self.filter_on
7020 and not self.tab_color
7021 and not self.outline_changed
7022 and not self.vba_codename
7023 ):
7024 return
7026 if self.vba_codename:
7027 attributes.append(("codeName", self.vba_codename))
7029 if self.filter_on:
7030 attributes.append(("filterMode", 1))
7032 if self.fit_page or self.tab_color or self.outline_changed:
7033 self._xml_start_tag("sheetPr", attributes)
7034 self._write_tab_color()
7035 self._write_outline_pr()
7036 self._write_page_set_up_pr()
7037 self._xml_end_tag("sheetPr")
7038 else:
7039 self._xml_empty_tag("sheetPr", attributes)
7041 def _write_page_set_up_pr(self) -> None:
7042 # Write the <pageSetUpPr> element.
7043 if not self.fit_page:
7044 return
7046 attributes = [("fitToPage", 1)]
7047 self._xml_empty_tag("pageSetUpPr", attributes)
7049 def _write_tab_color(self) -> None:
7050 # Write the <tabColor> element.
7051 color = self.tab_color
7053 if not color:
7054 return
7056 self._write_color("tabColor", color._attributes())
7058 def _write_outline_pr(self) -> None:
7059 # Write the <outlinePr> element.
7060 attributes = []
7062 if not self.outline_changed:
7063 return
7065 if self.outline_style:
7066 attributes.append(("applyStyles", 1))
7067 if not self.outline_below:
7068 attributes.append(("summaryBelow", 0))
7069 if not self.outline_right:
7070 attributes.append(("summaryRight", 0))
7071 if not self.outline_on:
7072 attributes.append(("showOutlineSymbols", 0))
7074 self._xml_empty_tag("outlinePr", attributes)
7076 def _write_row_breaks(self) -> None:
7077 # Write the <rowBreaks> element.
7078 page_breaks = self._sort_pagebreaks(self.hbreaks)
7080 if not page_breaks:
7081 return
7083 count = len(page_breaks)
7085 attributes = [
7086 ("count", count),
7087 ("manualBreakCount", count),
7088 ]
7090 self._xml_start_tag("rowBreaks", attributes)
7092 for row_num in page_breaks:
7093 self._write_brk(row_num, 16383)
7095 self._xml_end_tag("rowBreaks")
7097 def _write_col_breaks(self) -> None:
7098 # Write the <colBreaks> element.
7099 page_breaks = self._sort_pagebreaks(self.vbreaks)
7101 if not page_breaks:
7102 return
7104 count = len(page_breaks)
7106 attributes = [
7107 ("count", count),
7108 ("manualBreakCount", count),
7109 ]
7111 self._xml_start_tag("colBreaks", attributes)
7113 for col_num in page_breaks:
7114 self._write_brk(col_num, 1048575)
7116 self._xml_end_tag("colBreaks")
7118 def _write_brk(self, brk_id, brk_max) -> None:
7119 # Write the <brk> element.
7120 attributes = [("id", brk_id), ("max", brk_max), ("man", 1)]
7122 self._xml_empty_tag("brk", attributes)
7124 def _write_merge_cells(self) -> None:
7125 # Write the <mergeCells> element.
7126 merged_cells = self.merge
7127 count = len(merged_cells)
7129 if not count:
7130 return
7132 attributes = [("count", count)]
7134 self._xml_start_tag("mergeCells", attributes)
7136 for merged_range in merged_cells:
7137 # Write the mergeCell element.
7138 self._write_merge_cell(merged_range)
7140 self._xml_end_tag("mergeCells")
7142 def _write_merge_cell(self, merged_range) -> None:
7143 # Write the <mergeCell> element.
7144 row_min, col_min, row_max, col_max = merged_range
7146 # Convert the merge dimensions to a cell range.
7147 cell_1 = xl_rowcol_to_cell(row_min, col_min)
7148 cell_2 = xl_rowcol_to_cell(row_max, col_max)
7149 ref = cell_1 + ":" + cell_2
7151 attributes = [("ref", ref)]
7153 self._xml_empty_tag("mergeCell", attributes)
7155 def _write_hyperlinks(self) -> None:
7156 # Process any stored hyperlinks in row/col order and write the
7157 # <hyperlinks> element. The attributes are different for internal
7158 # and external links.
7160 # Sort the hyperlinks into row order.
7161 row_nums = sorted(self.hyperlinks.keys())
7163 # Exit if there are no hyperlinks to process.
7164 if not row_nums:
7165 return
7167 # Write the hyperlink elements.
7168 self._xml_start_tag("hyperlinks")
7170 # Iterate over the rows.
7171 for row_num in row_nums:
7172 # Sort the hyperlinks into column order.
7173 col_nums = sorted(self.hyperlinks[row_num].keys())
7175 # Iterate over the columns.
7176 for col_num in col_nums:
7177 # Get the link data for this cell.
7178 url = self.hyperlinks[row_num][col_num]
7180 # If the cell was overwritten by the user and isn't a string
7181 # then we have to add the url as the string to display.
7182 if self.table and self.table[row_num] and self.table[row_num][col_num]:
7183 cell = self.table[row_num][col_num]
7184 if cell.__class__.__name__ != "String":
7185 url._is_object_link = True
7187 if url._link_type in (UrlTypes.URL, UrlTypes.EXTERNAL):
7188 # External link with rel file relationship.
7189 self.rel_count += 1
7191 self._write_hyperlink_external(
7192 row_num, col_num, self.rel_count, url
7193 )
7195 # Links for use by the packager.
7196 self.external_hyper_links.append(
7197 ["/hyperlink", url._target(), "External"]
7198 )
7199 else:
7200 # Internal link with rel file relationship.
7201 self._write_hyperlink_internal(row_num, col_num, url)
7203 self._xml_end_tag("hyperlinks")
7205 def _write_hyperlink_external(
7206 self, row: int, col: int, id_num: int, url: Url
7207 ) -> None:
7208 # Write the <hyperlink> element for external links.
7209 ref = xl_rowcol_to_cell(row, col)
7210 r_id = "rId" + str(id_num)
7212 attributes = [("ref", ref), ("r:id", r_id)]
7214 if url._anchor:
7215 attributes.append(("location", url._anchor))
7217 if url._is_object_link:
7218 attributes.append(("display", url._text))
7220 if url._tip:
7221 attributes.append(("tooltip", url._tip))
7223 self._xml_empty_tag("hyperlink", attributes)
7225 def _write_hyperlink_internal(self, row: int, col: int, url: Url) -> None:
7226 # Write the <hyperlink> element for internal links.
7227 ref = xl_rowcol_to_cell(row, col)
7229 attributes = [("ref", ref), ("location", url._link)]
7231 if url._tip:
7232 attributes.append(("tooltip", url._tip))
7234 attributes.append(("display", url._text))
7236 self._xml_empty_tag("hyperlink", attributes)
7238 def _write_auto_filter(self) -> None:
7239 # Write the <autoFilter> element.
7240 if not self.autofilter_ref:
7241 return
7243 attributes = [("ref", self.autofilter_ref)]
7245 if self.filter_on:
7246 # Autofilter defined active filters.
7247 self._xml_start_tag("autoFilter", attributes)
7248 self._write_autofilters()
7249 self._xml_end_tag("autoFilter")
7251 else:
7252 # Autofilter defined without active filters.
7253 self._xml_empty_tag("autoFilter", attributes)
7255 def _write_autofilters(self) -> None:
7256 # Function to iterate through the columns that form part of an
7257 # autofilter range and write the appropriate filters.
7258 col1, col2 = self.filter_range
7260 for col in range(col1, col2 + 1):
7261 # Skip if column doesn't have an active filter.
7262 if col not in self.filter_cols:
7263 continue
7265 # Retrieve the filter tokens and write the autofilter records.
7266 tokens = self.filter_cols[col]
7267 filter_type = self.filter_type[col]
7269 # Filters are relative to first column in the autofilter.
7270 self._write_filter_column(col - col1, filter_type, tokens)
7272 def _write_filter_column(self, col_id, filter_type, filters) -> None:
7273 # Write the <filterColumn> element.
7274 attributes = [("colId", col_id)]
7276 self._xml_start_tag("filterColumn", attributes)
7278 if filter_type == 1:
7279 # Type == 1 is the new XLSX style filter.
7280 self._write_filters(filters)
7281 else:
7282 # Type == 0 is the classic "custom" filter.
7283 self._write_custom_filters(filters)
7285 self._xml_end_tag("filterColumn")
7287 def _write_filters(self, filters) -> None:
7288 # Write the <filters> element.
7289 non_blanks = [filter for filter in filters if str(filter).lower() != "blanks"]
7290 attributes = []
7292 if len(filters) != len(non_blanks):
7293 attributes = [("blank", 1)]
7295 if len(filters) == 1 and len(non_blanks) == 0:
7296 # Special case for blank cells only.
7297 self._xml_empty_tag("filters", attributes)
7298 else:
7299 # General case.
7300 self._xml_start_tag("filters", attributes)
7302 for autofilter in sorted(non_blanks):
7303 self._write_filter(autofilter)
7305 self._xml_end_tag("filters")
7307 def _write_filter(self, val) -> None:
7308 # Write the <filter> element.
7309 attributes = [("val", val)]
7311 self._xml_empty_tag("filter", attributes)
7313 def _write_custom_filters(self, tokens) -> None:
7314 # Write the <customFilters> element.
7315 if len(tokens) == 2:
7316 # One filter expression only.
7317 self._xml_start_tag("customFilters")
7318 self._write_custom_filter(*tokens)
7319 self._xml_end_tag("customFilters")
7320 else:
7321 # Two filter expressions.
7322 attributes = []
7324 # Check if the "join" operand is "and" or "or".
7325 if tokens[2] == 0:
7326 attributes = [("and", 1)]
7327 else:
7328 attributes = [("and", 0)]
7330 # Write the two custom filters.
7331 self._xml_start_tag("customFilters", attributes)
7332 self._write_custom_filter(tokens[0], tokens[1])
7333 self._write_custom_filter(tokens[3], tokens[4])
7334 self._xml_end_tag("customFilters")
7336 def _write_custom_filter(self, operator, val) -> None:
7337 # Write the <customFilter> element.
7338 attributes = []
7340 operators = {
7341 1: "lessThan",
7342 2: "equal",
7343 3: "lessThanOrEqual",
7344 4: "greaterThan",
7345 5: "notEqual",
7346 6: "greaterThanOrEqual",
7347 22: "equal",
7348 }
7350 # Convert the operator from a number to a descriptive string.
7351 if operators[operator] is not None:
7352 operator = operators[operator]
7353 else:
7354 warn(f"Unknown operator = {operator}")
7356 # The 'equal' operator is the default attribute and isn't stored.
7357 if operator != "equal":
7358 attributes.append(("operator", operator))
7359 attributes.append(("val", val))
7361 self._xml_empty_tag("customFilter", attributes)
7363 def _write_sheet_protection(self) -> None:
7364 # Write the <sheetProtection> element.
7365 attributes = []
7367 if not self.protect_options:
7368 return
7370 options = self.protect_options
7372 if options["password"]:
7373 attributes.append(("password", options["password"]))
7374 if options["sheet"]:
7375 attributes.append(("sheet", 1))
7376 if options["content"]:
7377 attributes.append(("content", 1))
7378 if not options["objects"]:
7379 attributes.append(("objects", 1))
7380 if not options["scenarios"]:
7381 attributes.append(("scenarios", 1))
7382 if options["format_cells"]:
7383 attributes.append(("formatCells", 0))
7384 if options["format_columns"]:
7385 attributes.append(("formatColumns", 0))
7386 if options["format_rows"]:
7387 attributes.append(("formatRows", 0))
7388 if options["insert_columns"]:
7389 attributes.append(("insertColumns", 0))
7390 if options["insert_rows"]:
7391 attributes.append(("insertRows", 0))
7392 if options["insert_hyperlinks"]:
7393 attributes.append(("insertHyperlinks", 0))
7394 if options["delete_columns"]:
7395 attributes.append(("deleteColumns", 0))
7396 if options["delete_rows"]:
7397 attributes.append(("deleteRows", 0))
7398 if not options["select_locked_cells"]:
7399 attributes.append(("selectLockedCells", 1))
7400 if options["sort"]:
7401 attributes.append(("sort", 0))
7402 if options["autofilter"]:
7403 attributes.append(("autoFilter", 0))
7404 if options["pivot_tables"]:
7405 attributes.append(("pivotTables", 0))
7406 if not options["select_unlocked_cells"]:
7407 attributes.append(("selectUnlockedCells", 1))
7409 self._xml_empty_tag("sheetProtection", attributes)
7411 def _write_protected_ranges(self) -> None:
7412 # Write the <protectedRanges> element.
7413 if self.num_protected_ranges == 0:
7414 return
7416 self._xml_start_tag("protectedRanges")
7418 for cell_range, range_name, password in self.protected_ranges:
7419 self._write_protected_range(cell_range, range_name, password)
7421 self._xml_end_tag("protectedRanges")
7423 def _write_protected_range(self, cell_range, range_name, password) -> None:
7424 # Write the <protectedRange> element.
7425 attributes = []
7427 if password:
7428 attributes.append(("password", password))
7430 attributes.append(("sqref", cell_range))
7431 attributes.append(("name", range_name))
7433 self._xml_empty_tag("protectedRange", attributes)
7435 def _write_drawings(self) -> None:
7436 # Write the <drawing> elements.
7437 if not self.drawing:
7438 return
7440 self.rel_count += 1
7441 self._write_drawing(self.rel_count)
7443 def _write_drawing(self, drawing_id) -> None:
7444 # Write the <drawing> element.
7445 r_id = "rId" + str(drawing_id)
7447 attributes = [("r:id", r_id)]
7449 self._xml_empty_tag("drawing", attributes)
7451 def _write_legacy_drawing(self) -> None:
7452 # Write the <legacyDrawing> element.
7453 if not self.has_vml:
7454 return
7456 # Increment the relationship id for any drawings or comments.
7457 self.rel_count += 1
7458 r_id = "rId" + str(self.rel_count)
7460 attributes = [("r:id", r_id)]
7462 self._xml_empty_tag("legacyDrawing", attributes)
7464 def _write_legacy_drawing_hf(self) -> None:
7465 # Write the <legacyDrawingHF> element.
7466 if not self.has_header_vml:
7467 return
7469 # Increment the relationship id for any drawings or comments.
7470 self.rel_count += 1
7471 r_id = "rId" + str(self.rel_count)
7473 attributes = [("r:id", r_id)]
7475 self._xml_empty_tag("legacyDrawingHF", attributes)
7477 def _write_picture(self) -> None:
7478 # Write the <picture> element.
7479 if not self.background_image:
7480 return
7482 # Increment the relationship id.
7483 self.rel_count += 1
7484 r_id = "rId" + str(self.rel_count)
7486 attributes = [("r:id", r_id)]
7488 self._xml_empty_tag("picture", attributes)
7490 def _write_data_validations(self) -> None:
7491 # Write the <dataValidations> element.
7492 validations = self.validations
7493 count = len(validations)
7495 if not count:
7496 return
7498 attributes = [("count", count)]
7500 self._xml_start_tag("dataValidations", attributes)
7502 for validation in validations:
7503 # Write the dataValidation element.
7504 self._write_data_validation(validation)
7506 self._xml_end_tag("dataValidations")
7508 def _write_data_validation(self, options) -> None:
7509 # Write the <dataValidation> element.
7510 sqref = ""
7511 attributes = []
7513 # Set the cell range(s) for the data validation.
7514 for cells in options["cells"]:
7515 # Add a space between multiple cell ranges.
7516 if sqref != "":
7517 sqref += " "
7519 row_first, col_first, row_last, col_last = cells
7521 # Swap last row/col for first row/col as necessary
7522 if row_first > row_last:
7523 row_first, row_last = (row_last, row_first)
7525 if col_first > col_last:
7526 col_first, col_last = (col_last, col_first)
7528 sqref += xl_range(row_first, col_first, row_last, col_last)
7530 if options.get("multi_range"):
7531 sqref = options["multi_range"]
7533 if options["validate"] != "none":
7534 attributes.append(("type", options["validate"]))
7536 if options["criteria"] != "between":
7537 attributes.append(("operator", options["criteria"]))
7539 if "error_type" in options:
7540 if options["error_type"] == 1:
7541 attributes.append(("errorStyle", "warning"))
7542 if options["error_type"] == 2:
7543 attributes.append(("errorStyle", "information"))
7545 if options["ignore_blank"]:
7546 attributes.append(("allowBlank", 1))
7548 if not options["dropdown"]:
7549 attributes.append(("showDropDown", 1))
7551 if options["show_input"]:
7552 attributes.append(("showInputMessage", 1))
7554 if options["show_error"]:
7555 attributes.append(("showErrorMessage", 1))
7557 if "error_title" in options:
7558 attributes.append(("errorTitle", options["error_title"]))
7560 if "error_message" in options:
7561 attributes.append(("error", options["error_message"]))
7563 if "input_title" in options:
7564 attributes.append(("promptTitle", options["input_title"]))
7566 if "input_message" in options:
7567 attributes.append(("prompt", options["input_message"]))
7569 attributes.append(("sqref", sqref))
7571 if options["validate"] == "none":
7572 self._xml_empty_tag("dataValidation", attributes)
7573 else:
7574 self._xml_start_tag("dataValidation", attributes)
7576 # Write the formula1 element.
7577 self._write_formula_1(options["value"])
7579 # Write the formula2 element.
7580 if options["maximum"] is not None:
7581 self._write_formula_2(options["maximum"])
7583 self._xml_end_tag("dataValidation")
7585 def _write_formula_1(self, formula) -> None:
7586 # Write the <formula1> element.
7588 if isinstance(formula, list):
7589 formula = self._csv_join(*formula)
7590 formula = f'"{formula}"'
7591 else:
7592 # Check if the formula is a number.
7593 try:
7594 float(formula)
7595 except ValueError:
7596 # Not a number. Remove the formula '=' sign if it exists.
7597 if formula.startswith("="):
7598 formula = formula.lstrip("=")
7600 self._xml_data_element("formula1", formula)
7602 def _write_formula_2(self, formula) -> None:
7603 # Write the <formula2> element.
7605 # Check if the formula is a number.
7606 try:
7607 float(formula)
7608 except ValueError:
7609 # Not a number. Remove the formula '=' sign if it exists.
7610 if formula.startswith("="):
7611 formula = formula.lstrip("=")
7613 self._xml_data_element("formula2", formula)
7615 def _write_conditional_formats(self) -> None:
7616 # Write the Worksheet conditional formats.
7617 ranges = sorted(self.cond_formats.keys())
7619 if not ranges:
7620 return
7622 for cond_range in ranges:
7623 self._write_conditional_formatting(
7624 cond_range, self.cond_formats[cond_range]
7625 )
7627 def _write_conditional_formatting(self, cond_range, params) -> None:
7628 # Write the <conditionalFormatting> element.
7629 attributes = [("sqref", cond_range)]
7630 self._xml_start_tag("conditionalFormatting", attributes)
7631 for param in params:
7632 # Write the cfRule element.
7633 self._write_cf_rule(param)
7634 self._xml_end_tag("conditionalFormatting")
7636 def _write_cf_rule(self, params) -> None:
7637 # Write the <cfRule> element.
7638 attributes = [("type", params["type"])]
7640 if "format" in params and params["format"] is not None:
7641 attributes.append(("dxfId", params["format"]))
7643 attributes.append(("priority", params["priority"]))
7645 if params.get("stop_if_true"):
7646 attributes.append(("stopIfTrue", 1))
7648 if params["type"] == "cellIs":
7649 attributes.append(("operator", params["criteria"]))
7651 self._xml_start_tag("cfRule", attributes)
7653 if "minimum" in params and "maximum" in params:
7654 self._write_formula_element(params["minimum"])
7655 self._write_formula_element(params["maximum"])
7656 else:
7657 self._write_formula_element(params["value"])
7659 self._xml_end_tag("cfRule")
7661 elif params["type"] == "aboveAverage":
7662 if re.search("below", params["criteria"]):
7663 attributes.append(("aboveAverage", 0))
7665 if re.search("equal", params["criteria"]):
7666 attributes.append(("equalAverage", 1))
7668 if re.search("[123] std dev", params["criteria"]):
7669 match = re.search("([123]) std dev", params["criteria"])
7670 attributes.append(("stdDev", match.group(1)))
7672 self._xml_empty_tag("cfRule", attributes)
7674 elif params["type"] == "top10":
7675 if "criteria" in params and params["criteria"] == "%":
7676 attributes.append(("percent", 1))
7678 if "direction" in params:
7679 attributes.append(("bottom", 1))
7681 rank = params["value"] or 10
7682 attributes.append(("rank", rank))
7684 self._xml_empty_tag("cfRule", attributes)
7686 elif params["type"] == "duplicateValues":
7687 self._xml_empty_tag("cfRule", attributes)
7689 elif params["type"] == "uniqueValues":
7690 self._xml_empty_tag("cfRule", attributes)
7692 elif (
7693 params["type"] == "containsText"
7694 or params["type"] == "notContainsText"
7695 or params["type"] == "beginsWith"
7696 or params["type"] == "endsWith"
7697 ):
7698 attributes.append(("operator", params["criteria"]))
7699 attributes.append(("text", params["value"]))
7700 self._xml_start_tag("cfRule", attributes)
7701 self._write_formula_element(params["formula"])
7702 self._xml_end_tag("cfRule")
7704 elif params["type"] == "timePeriod":
7705 attributes.append(("timePeriod", params["criteria"]))
7706 self._xml_start_tag("cfRule", attributes)
7707 self._write_formula_element(params["formula"])
7708 self._xml_end_tag("cfRule")
7710 elif (
7711 params["type"] == "containsBlanks"
7712 or params["type"] == "notContainsBlanks"
7713 or params["type"] == "containsErrors"
7714 or params["type"] == "notContainsErrors"
7715 ):
7716 self._xml_start_tag("cfRule", attributes)
7717 self._write_formula_element(params["formula"])
7718 self._xml_end_tag("cfRule")
7720 elif params["type"] == "colorScale":
7721 self._xml_start_tag("cfRule", attributes)
7722 self._write_color_scale(params)
7723 self._xml_end_tag("cfRule")
7725 elif params["type"] == "dataBar":
7726 self._xml_start_tag("cfRule", attributes)
7727 self._write_data_bar(params)
7729 if params.get("is_data_bar_2010"):
7730 self._write_data_bar_ext(params)
7732 self._xml_end_tag("cfRule")
7734 elif params["type"] == "expression":
7735 self._xml_start_tag("cfRule", attributes)
7736 self._write_formula_element(params["criteria"])
7737 self._xml_end_tag("cfRule")
7739 elif params["type"] == "iconSet":
7740 self._xml_start_tag("cfRule", attributes)
7741 self._write_icon_set(params)
7742 self._xml_end_tag("cfRule")
7744 def _write_formula_element(self, formula) -> None:
7745 # Write the <formula> element.
7747 # Check if the formula is a number.
7748 try:
7749 float(formula)
7750 except ValueError:
7751 # Not a number. Remove the formula '=' sign if it exists.
7752 if formula.startswith("="):
7753 formula = formula.lstrip("=")
7755 self._xml_data_element("formula", formula)
7757 def _write_color_scale(self, param) -> None:
7758 # Write the <colorScale> element.
7760 self._xml_start_tag("colorScale")
7762 self._write_cfvo(param["min_type"], param["min_value"])
7764 if param["mid_type"] is not None:
7765 self._write_cfvo(param["mid_type"], param["mid_value"])
7767 self._write_cfvo(param["max_type"], param["max_value"])
7769 self._write_color("color", param["min_color"]._attributes())
7771 if param["mid_color"] is not None:
7772 self._write_color("color", param["mid_color"]._attributes())
7774 self._write_color("color", param["max_color"]._attributes())
7776 self._xml_end_tag("colorScale")
7778 def _write_data_bar(self, param) -> None:
7779 # Write the <dataBar> element.
7780 attributes = []
7782 # Min and max bar lengths in in the spec but not supported directly by
7783 # Excel.
7784 if "min_length" in param:
7785 attributes.append(("minLength", param["min_length"]))
7787 if "max_length" in param:
7788 attributes.append(("maxLength", param["max_length"]))
7790 if param.get("bar_only"):
7791 attributes.append(("showValue", 0))
7793 self._xml_start_tag("dataBar", attributes)
7795 self._write_cfvo(param["min_type"], param["min_value"])
7796 self._write_cfvo(param["max_type"], param["max_value"])
7797 self._write_color("color", param["bar_color"]._attributes())
7799 self._xml_end_tag("dataBar")
7801 def _write_data_bar_ext(self, param) -> None:
7802 # Write the <extLst> dataBar extension element.
7804 # Create a pseudo GUID for each unique Excel 2010 data bar.
7805 worksheet_count = self.index + 1
7806 data_bar_count = len(self.data_bars_2010) + 1
7807 guid = "{DA7ABA51-AAAA-BBBB-%04X-%012X}" % (worksheet_count, data_bar_count)
7809 # Store the 2010 data bar parameters to write the extLst elements.
7810 param["guid"] = guid
7811 self.data_bars_2010.append(param)
7813 self._xml_start_tag("extLst")
7814 self._write_ext("{B025F937-C7B1-47D3-B67F-A62EFF666E3E}")
7815 self._xml_data_element("x14:id", guid)
7816 self._xml_end_tag("ext")
7817 self._xml_end_tag("extLst")
7819 def _write_icon_set(self, param) -> None:
7820 # Write the <iconSet> element.
7821 attributes = []
7823 # Don't set attribute for default style.
7824 if param["icon_style"] != "3TrafficLights":
7825 attributes = [("iconSet", param["icon_style"])]
7827 if param.get("icons_only"):
7828 attributes.append(("showValue", 0))
7830 if param.get("reverse_icons"):
7831 attributes.append(("reverse", 1))
7833 self._xml_start_tag("iconSet", attributes)
7835 # Write the properties for different icon styles.
7836 for icon in reversed(param["icons"]):
7837 self._write_cfvo(icon["type"], icon["value"], icon["criteria"])
7839 self._xml_end_tag("iconSet")
7841 def _write_cfvo(self, cf_type, val, criteria=None) -> None:
7842 # Write the <cfvo> element.
7843 attributes = [("type", cf_type)]
7845 if val is not None:
7846 attributes.append(("val", val))
7848 if criteria:
7849 attributes.append(("gte", 0))
7851 self._xml_empty_tag("cfvo", attributes)
7853 def _write_color(self, name, attributes) -> None:
7854 # Write the <color> element.
7855 self._xml_empty_tag(name, attributes)
7857 def _write_selections(self) -> None:
7858 # Write the <selection> elements.
7859 for selection in self.selections:
7860 self._write_selection(*selection)
7862 def _write_selection(self, pane, active_cell, sqref) -> None:
7863 # Write the <selection> element.
7864 attributes = []
7866 if pane:
7867 attributes.append(("pane", pane))
7869 if active_cell:
7870 attributes.append(("activeCell", active_cell))
7872 if sqref:
7873 attributes.append(("sqref", sqref))
7875 self._xml_empty_tag("selection", attributes)
7877 def _write_panes(self) -> None:
7878 # Write the frozen or split <pane> elements.
7879 panes = self.panes
7881 if not panes:
7882 return
7884 if panes[4] == 2:
7885 self._write_split_panes(*panes)
7886 else:
7887 self._write_freeze_panes(*panes)
7889 def _write_freeze_panes(
7890 self, row: int, col: int, top_row, left_col, pane_type
7891 ) -> None:
7892 # Write the <pane> element for freeze panes.
7893 attributes = []
7895 y_split = row
7896 x_split = col
7897 top_left_cell = xl_rowcol_to_cell(top_row, left_col)
7898 active_pane = ""
7899 state = ""
7900 active_cell = ""
7901 sqref = ""
7903 # Move user cell selection to the panes.
7904 if self.selections:
7905 _, active_cell, sqref = self.selections[0]
7906 self.selections = []
7908 # Set the active pane.
7909 if row and col:
7910 active_pane = "bottomRight"
7912 row_cell = xl_rowcol_to_cell(row, 0)
7913 col_cell = xl_rowcol_to_cell(0, col)
7915 self.selections.append(["topRight", col_cell, col_cell])
7916 self.selections.append(["bottomLeft", row_cell, row_cell])
7917 self.selections.append(["bottomRight", active_cell, sqref])
7919 elif col:
7920 active_pane = "topRight"
7921 self.selections.append(["topRight", active_cell, sqref])
7923 else:
7924 active_pane = "bottomLeft"
7925 self.selections.append(["bottomLeft", active_cell, sqref])
7927 # Set the pane type.
7928 if pane_type == 0:
7929 state = "frozen"
7930 elif pane_type == 1:
7931 state = "frozenSplit"
7932 else:
7933 state = "split"
7935 if x_split:
7936 attributes.append(("xSplit", x_split))
7938 if y_split:
7939 attributes.append(("ySplit", y_split))
7941 attributes.append(("topLeftCell", top_left_cell))
7942 attributes.append(("activePane", active_pane))
7943 attributes.append(("state", state))
7945 self._xml_empty_tag("pane", attributes)
7947 def _write_split_panes(self, row: int, col: int, top_row, left_col, _) -> None:
7948 # Write the <pane> element for split panes.
7949 attributes = []
7950 has_selection = False
7951 active_pane = ""
7952 active_cell = ""
7953 sqref = ""
7955 y_split = row
7956 x_split = col
7958 # Move user cell selection to the panes.
7959 if self.selections:
7960 _, active_cell, sqref = self.selections[0]
7961 self.selections = []
7962 has_selection = True
7964 # Convert the row and col to 1/20 twip units with padding.
7965 if y_split:
7966 y_split = int(20 * y_split + 300)
7968 if x_split:
7969 x_split = self._calculate_x_split_width(x_split)
7971 # For non-explicit topLeft definitions, estimate the cell offset based
7972 # on the pixels dimensions. This is only a workaround and doesn't take
7973 # adjusted cell dimensions into account.
7974 if top_row == row and left_col == col:
7975 top_row = int(0.5 + (y_split - 300) / 20 / 15)
7976 left_col = int(0.5 + (x_split - 390) / 20 / 3 * 4 / 64)
7978 top_left_cell = xl_rowcol_to_cell(top_row, left_col)
7980 # If there is no selection set the active cell to the top left cell.
7981 if not has_selection:
7982 active_cell = top_left_cell
7983 sqref = top_left_cell
7985 # Set the Cell selections.
7986 if row and col:
7987 active_pane = "bottomRight"
7989 row_cell = xl_rowcol_to_cell(top_row, 0)
7990 col_cell = xl_rowcol_to_cell(0, left_col)
7992 self.selections.append(["topRight", col_cell, col_cell])
7993 self.selections.append(["bottomLeft", row_cell, row_cell])
7994 self.selections.append(["bottomRight", active_cell, sqref])
7996 elif col:
7997 active_pane = "topRight"
7998 self.selections.append(["topRight", active_cell, sqref])
8000 else:
8001 active_pane = "bottomLeft"
8002 self.selections.append(["bottomLeft", active_cell, sqref])
8004 # Format splits to the same precision as Excel.
8005 if x_split:
8006 attributes.append(("xSplit", f"{x_split:.16g}"))
8008 if y_split:
8009 attributes.append(("ySplit", f"{y_split:.16g}"))
8011 attributes.append(("topLeftCell", top_left_cell))
8013 if has_selection:
8014 attributes.append(("activePane", active_pane))
8016 self._xml_empty_tag("pane", attributes)
8018 def _calculate_x_split_width(self, width):
8019 # Convert column width from user units to pane split width.
8021 max_digit_width = 7 # For Calabri 11.
8022 padding = 5
8024 # Convert to pixels.
8025 if width < 1:
8026 pixels = int(width * (max_digit_width + padding) + 0.5)
8027 else:
8028 pixels = int(width * max_digit_width + 0.5) + padding
8030 # Convert to points.
8031 points = pixels * 3 / 4
8033 # Convert to twips (twentieths of a point).
8034 twips = points * 20
8036 # Add offset/padding.
8037 width = twips + 390
8039 return width
8041 def _write_table_parts(self) -> None:
8042 # Write the <tableParts> element.
8043 tables = self.tables
8044 count = len(tables)
8046 # Return if worksheet doesn't contain any tables.
8047 if not count:
8048 return
8050 attributes = [
8051 (
8052 "count",
8053 count,
8054 )
8055 ]
8057 self._xml_start_tag("tableParts", attributes)
8059 for _ in tables:
8060 # Write the tablePart element.
8061 self.rel_count += 1
8062 self._write_table_part(self.rel_count)
8064 self._xml_end_tag("tableParts")
8066 def _write_table_part(self, r_id) -> None:
8067 # Write the <tablePart> element.
8069 r_id = "rId" + str(r_id)
8071 attributes = [
8072 (
8073 "r:id",
8074 r_id,
8075 )
8076 ]
8078 self._xml_empty_tag("tablePart", attributes)
8080 def _write_ext_list(self) -> None:
8081 # Write the <extLst> element for data bars and sparklines.
8082 has_data_bars = len(self.data_bars_2010)
8083 has_sparklines = len(self.sparklines)
8085 if not has_data_bars and not has_sparklines:
8086 return
8088 # Write the extLst element.
8089 self._xml_start_tag("extLst")
8091 if has_data_bars:
8092 self._write_ext_list_data_bars()
8094 if has_sparklines:
8095 self._write_ext_list_sparklines()
8097 self._xml_end_tag("extLst")
8099 def _write_ext_list_data_bars(self) -> None:
8100 # Write the Excel 2010 data_bar subelements.
8101 self._write_ext("{78C0D931-6437-407d-A8EE-F0AAD7539E65}")
8103 self._xml_start_tag("x14:conditionalFormattings")
8105 # Write the Excel 2010 conditional formatting data bar elements.
8106 for data_bar in self.data_bars_2010:
8107 # Write the x14:conditionalFormatting element.
8108 self._write_conditional_formatting_2010(data_bar)
8110 self._xml_end_tag("x14:conditionalFormattings")
8111 self._xml_end_tag("ext")
8113 def _write_conditional_formatting_2010(self, data_bar) -> None:
8114 # Write the <x14:conditionalFormatting> element.
8115 xmlns_xm = "http://schemas.microsoft.com/office/excel/2006/main"
8117 attributes = [("xmlns:xm", xmlns_xm)]
8119 self._xml_start_tag("x14:conditionalFormatting", attributes)
8121 # Write the x14:cfRule element.
8122 self._write_x14_cf_rule(data_bar)
8124 # Write the x14:dataBar element.
8125 self._write_x14_data_bar(data_bar)
8127 # Write the x14 max and min data bars.
8128 self._write_x14_cfvo(data_bar["x14_min_type"], data_bar["min_value"])
8129 self._write_x14_cfvo(data_bar["x14_max_type"], data_bar["max_value"])
8131 if not data_bar["bar_no_border"]:
8132 # Write the x14:borderColor element.
8133 self._write_x14_border_color(data_bar["bar_border_color"])
8135 # Write the x14:negativeFillColor element.
8136 if not data_bar["bar_negative_color_same"]:
8137 self._write_x14_negative_fill_color(data_bar["bar_negative_color"])
8139 # Write the x14:negativeBorderColor element.
8140 if (
8141 not data_bar["bar_no_border"]
8142 and not data_bar["bar_negative_border_color_same"]
8143 ):
8144 self._write_x14_negative_border_color(data_bar["bar_negative_border_color"])
8146 # Write the x14:axisColor element.
8147 if data_bar["bar_axis_position"] != "none":
8148 self._write_x14_axis_color(data_bar["bar_axis_color"])
8150 self._xml_end_tag("x14:dataBar")
8151 self._xml_end_tag("x14:cfRule")
8153 # Write the xm:sqref element.
8154 self._xml_data_element("xm:sqref", data_bar["range"])
8156 self._xml_end_tag("x14:conditionalFormatting")
8158 def _write_x14_cf_rule(self, data_bar) -> None:
8159 # Write the <x14:cfRule> element.
8160 rule_type = "dataBar"
8161 guid = data_bar["guid"]
8162 attributes = [("type", rule_type), ("id", guid)]
8164 self._xml_start_tag("x14:cfRule", attributes)
8166 def _write_x14_data_bar(self, data_bar) -> None:
8167 # Write the <x14:dataBar> element.
8168 min_length = 0
8169 max_length = 100
8171 attributes = [
8172 ("minLength", min_length),
8173 ("maxLength", max_length),
8174 ]
8176 if not data_bar["bar_no_border"]:
8177 attributes.append(("border", 1))
8179 if data_bar["bar_solid"]:
8180 attributes.append(("gradient", 0))
8182 if data_bar["bar_direction"] == "left":
8183 attributes.append(("direction", "leftToRight"))
8185 if data_bar["bar_direction"] == "right":
8186 attributes.append(("direction", "rightToLeft"))
8188 if data_bar["bar_negative_color_same"]:
8189 attributes.append(("negativeBarColorSameAsPositive", 1))
8191 if (
8192 not data_bar["bar_no_border"]
8193 and not data_bar["bar_negative_border_color_same"]
8194 ):
8195 attributes.append(("negativeBarBorderColorSameAsPositive", 0))
8197 if data_bar["bar_axis_position"] == "middle":
8198 attributes.append(("axisPosition", "middle"))
8200 if data_bar["bar_axis_position"] == "none":
8201 attributes.append(("axisPosition", "none"))
8203 self._xml_start_tag("x14:dataBar", attributes)
8205 def _write_x14_cfvo(self, rule_type, value) -> None:
8206 # Write the <x14:cfvo> element.
8207 attributes = [("type", rule_type)]
8209 if rule_type in ("min", "max", "autoMin", "autoMax"):
8210 self._xml_empty_tag("x14:cfvo", attributes)
8211 else:
8212 self._xml_start_tag("x14:cfvo", attributes)
8213 self._xml_data_element("xm:f", value)
8214 self._xml_end_tag("x14:cfvo")
8216 def _write_x14_border_color(self, color) -> None:
8217 # Write the <x14:borderColor> element.
8218 self._write_color("x14:borderColor", color._attributes())
8220 def _write_x14_negative_fill_color(self, color) -> None:
8221 # Write the <x14:negativeFillColor> element.
8222 self._xml_empty_tag("x14:negativeFillColor", color._attributes())
8224 def _write_x14_negative_border_color(self, color) -> None:
8225 # Write the <x14:negativeBorderColor> element.
8226 self._xml_empty_tag("x14:negativeBorderColor", color._attributes())
8228 def _write_x14_axis_color(self, color) -> None:
8229 # Write the <x14:axisColor> element.
8230 self._xml_empty_tag("x14:axisColor", color._attributes())
8232 def _write_ext_list_sparklines(self) -> None:
8233 # Write the sparkline extension sub-elements.
8234 self._write_ext("{05C60535-1F16-4fd2-B633-F4F36F0B64E0}")
8236 # Write the x14:sparklineGroups element.
8237 self._write_sparkline_groups()
8239 # Write the sparkline elements.
8240 for sparkline in reversed(self.sparklines):
8241 # Write the x14:sparklineGroup element.
8242 self._write_sparkline_group(sparkline)
8244 # Write the x14:colorSeries element.
8245 self._write_color_series(sparkline["series_color"])
8247 # Write the x14:colorNegative element.
8248 self._write_color_negative(sparkline["negative_color"])
8250 # Write the x14:colorAxis element.
8251 self._write_color_axis()
8253 # Write the x14:colorMarkers element.
8254 self._write_color_markers(sparkline["markers_color"])
8256 # Write the x14:colorFirst element.
8257 self._write_color_first(sparkline["first_color"])
8259 # Write the x14:colorLast element.
8260 self._write_color_last(sparkline["last_color"])
8262 # Write the x14:colorHigh element.
8263 self._write_color_high(sparkline["high_color"])
8265 # Write the x14:colorLow element.
8266 self._write_color_low(sparkline["low_color"])
8268 if sparkline["date_axis"]:
8269 self._xml_data_element("xm:f", sparkline["date_axis"])
8271 self._write_sparklines(sparkline)
8273 self._xml_end_tag("x14:sparklineGroup")
8275 self._xml_end_tag("x14:sparklineGroups")
8276 self._xml_end_tag("ext")
8278 def _write_sparklines(self, sparkline) -> None:
8279 # Write the <x14:sparklines> element and <x14:sparkline> sub-elements.
8281 # Write the sparkline elements.
8282 self._xml_start_tag("x14:sparklines")
8284 for i in range(sparkline["count"]):
8285 spark_range = sparkline["ranges"][i]
8286 location = sparkline["locations"][i]
8288 self._xml_start_tag("x14:sparkline")
8289 self._xml_data_element("xm:f", spark_range)
8290 self._xml_data_element("xm:sqref", location)
8291 self._xml_end_tag("x14:sparkline")
8293 self._xml_end_tag("x14:sparklines")
8295 def _write_ext(self, uri) -> None:
8296 # Write the <ext> element.
8297 schema = "http://schemas.microsoft.com/office/"
8298 xmlns_x14 = schema + "spreadsheetml/2009/9/main"
8300 attributes = [
8301 ("xmlns:x14", xmlns_x14),
8302 ("uri", uri),
8303 ]
8305 self._xml_start_tag("ext", attributes)
8307 def _write_sparkline_groups(self) -> None:
8308 # Write the <x14:sparklineGroups> element.
8309 xmlns_xm = "http://schemas.microsoft.com/office/excel/2006/main"
8311 attributes = [("xmlns:xm", xmlns_xm)]
8313 self._xml_start_tag("x14:sparklineGroups", attributes)
8315 def _write_sparkline_group(self, options) -> None:
8316 # Write the <x14:sparklineGroup> element.
8317 #
8318 # Example for order.
8319 #
8320 # <x14:sparklineGroup
8321 # manualMax="0"
8322 # manualMin="0"
8323 # lineWeight="2.25"
8324 # type="column"
8325 # dateAxis="1"
8326 # displayEmptyCellsAs="span"
8327 # markers="1"
8328 # high="1"
8329 # low="1"
8330 # first="1"
8331 # last="1"
8332 # negative="1"
8333 # displayXAxis="1"
8334 # displayHidden="1"
8335 # minAxisType="custom"
8336 # maxAxisType="custom"
8337 # rightToLeft="1">
8338 #
8339 empty = options.get("empty")
8340 attributes = []
8342 if options.get("max") is not None:
8343 if options["max"] == "group":
8344 options["cust_max"] = "group"
8345 else:
8346 attributes.append(("manualMax", options["max"]))
8347 options["cust_max"] = "custom"
8349 if options.get("min") is not None:
8350 if options["min"] == "group":
8351 options["cust_min"] = "group"
8352 else:
8353 attributes.append(("manualMin", options["min"]))
8354 options["cust_min"] = "custom"
8356 # Ignore the default type attribute (line).
8357 if options["type"] != "line":
8358 attributes.append(("type", options["type"]))
8360 if options.get("weight"):
8361 attributes.append(("lineWeight", options["weight"]))
8363 if options.get("date_axis"):
8364 attributes.append(("dateAxis", 1))
8366 if empty:
8367 attributes.append(("displayEmptyCellsAs", empty))
8369 if options.get("markers"):
8370 attributes.append(("markers", 1))
8372 if options.get("high"):
8373 attributes.append(("high", 1))
8375 if options.get("low"):
8376 attributes.append(("low", 1))
8378 if options.get("first"):
8379 attributes.append(("first", 1))
8381 if options.get("last"):
8382 attributes.append(("last", 1))
8384 if options.get("negative"):
8385 attributes.append(("negative", 1))
8387 if options.get("axis"):
8388 attributes.append(("displayXAxis", 1))
8390 if options.get("hidden"):
8391 attributes.append(("displayHidden", 1))
8393 if options.get("cust_min"):
8394 attributes.append(("minAxisType", options["cust_min"]))
8396 if options.get("cust_max"):
8397 attributes.append(("maxAxisType", options["cust_max"]))
8399 if options.get("reverse"):
8400 attributes.append(("rightToLeft", 1))
8402 self._xml_start_tag("x14:sparklineGroup", attributes)
8404 def _write_spark_color(self, tag, color) -> None:
8405 # Helper function for the sparkline color functions below.
8406 if color:
8407 self._write_color(tag, color._attributes())
8409 def _write_color_series(self, color) -> None:
8410 # Write the <x14:colorSeries> element.
8411 self._write_spark_color("x14:colorSeries", color)
8413 def _write_color_negative(self, color) -> None:
8414 # Write the <x14:colorNegative> element.
8415 self._write_spark_color("x14:colorNegative", color)
8417 def _write_color_axis(self) -> None:
8418 # Write the <x14:colorAxis> element.
8419 self._write_spark_color("x14:colorAxis", Color("#000000"))
8421 def _write_color_markers(self, color) -> None:
8422 # Write the <x14:colorMarkers> element.
8423 self._write_spark_color("x14:colorMarkers", color)
8425 def _write_color_first(self, color) -> None:
8426 # Write the <x14:colorFirst> element.
8427 self._write_spark_color("x14:colorFirst", color)
8429 def _write_color_last(self, color) -> None:
8430 # Write the <x14:colorLast> element.
8431 self._write_spark_color("x14:colorLast", color)
8433 def _write_color_high(self, color) -> None:
8434 # Write the <x14:colorHigh> element.
8435 self._write_spark_color("x14:colorHigh", color)
8437 def _write_color_low(self, color) -> None:
8438 # Write the <x14:colorLow> element.
8439 self._write_spark_color("x14:colorLow", color)
8441 def _write_phonetic_pr(self) -> None:
8442 # Write the <phoneticPr> element.
8443 attributes = [
8444 ("fontId", "0"),
8445 ("type", "noConversion"),
8446 ]
8448 self._xml_empty_tag("phoneticPr", attributes)
8450 def _write_ignored_errors(self) -> None:
8451 # Write the <ignoredErrors> element.
8452 if not self.ignored_errors:
8453 return
8455 self._xml_start_tag("ignoredErrors")
8457 if self.ignored_errors.get("number_stored_as_text"):
8458 ignored_range = self.ignored_errors["number_stored_as_text"]
8459 self._write_ignored_error("numberStoredAsText", ignored_range)
8461 if self.ignored_errors.get("eval_error"):
8462 ignored_range = self.ignored_errors["eval_error"]
8463 self._write_ignored_error("evalError", ignored_range)
8465 if self.ignored_errors.get("formula_differs"):
8466 ignored_range = self.ignored_errors["formula_differs"]
8467 self._write_ignored_error("formula", ignored_range)
8469 if self.ignored_errors.get("formula_range"):
8470 ignored_range = self.ignored_errors["formula_range"]
8471 self._write_ignored_error("formulaRange", ignored_range)
8473 if self.ignored_errors.get("formula_unlocked"):
8474 ignored_range = self.ignored_errors["formula_unlocked"]
8475 self._write_ignored_error("unlockedFormula", ignored_range)
8477 if self.ignored_errors.get("empty_cell_reference"):
8478 ignored_range = self.ignored_errors["empty_cell_reference"]
8479 self._write_ignored_error("emptyCellReference", ignored_range)
8481 if self.ignored_errors.get("list_data_validation"):
8482 ignored_range = self.ignored_errors["list_data_validation"]
8483 self._write_ignored_error("listDataValidation", ignored_range)
8485 if self.ignored_errors.get("calculated_column"):
8486 ignored_range = self.ignored_errors["calculated_column"]
8487 self._write_ignored_error("calculatedColumn", ignored_range)
8489 if self.ignored_errors.get("two_digit_text_year"):
8490 ignored_range = self.ignored_errors["two_digit_text_year"]
8491 self._write_ignored_error("twoDigitTextYear", ignored_range)
8493 self._xml_end_tag("ignoredErrors")
8495 def _write_ignored_error(self, error_type, ignored_range) -> None:
8496 # Write the <ignoredError> element.
8497 attributes = [
8498 ("sqref", ignored_range),
8499 (error_type, 1),
8500 ]
8502 self._xml_empty_tag("ignoredError", attributes)