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.ext_sheets = []
328 self.fileclosed = 0
329 self.excel_version = 2007
330 self.excel2003_style = False
332 self.xls_rowmax = 1048576
333 self.xls_colmax = 16384
334 self.xls_strmax = 32767
335 self.dim_rowmin = None
336 self.dim_rowmax = None
337 self.dim_colmin = None
338 self.dim_colmax = None
340 self.col_info: Dict[int, ColumnInfo] = {}
341 self.row_info: Dict[int, RowInfo] = {}
342 self.default_row_height: int = 20
343 self.default_col_width: int = 64
344 self.cell_padding: int = 5
345 self.original_row_height: int = 20
346 self.max_digit_width: int = 7
347 self.max_col_width: int = 1790
348 self.default_date_width = 68
349 self.default_row_zeroed = 0
351 self.selections = []
352 self.hidden = 0
353 self.active = 0
354 self.tab_color = 0
355 self.top_left_cell = ""
357 self.panes = []
358 self.active_pane = 3
359 self.selected = 0
361 self.page_setup_changed = False
362 self.paper_size = 0
363 self.orientation = 1
365 self.print_options_changed = False
366 self.hcenter = False
367 self.vcenter = False
368 self.print_gridlines = False
369 self.screen_gridlines = True
370 self.print_headers = False
371 self.row_col_headers = False
373 self.header_footer_changed = False
374 self.header = ""
375 self.footer = ""
376 self.header_footer_aligns = True
377 self.header_footer_scales = True
378 self.header_images = []
379 self.footer_images = []
380 self.header_images_list = []
382 self.margin_left = 0.7
383 self.margin_right = 0.7
384 self.margin_top = 0.75
385 self.margin_bottom = 0.75
386 self.margin_header = 0.3
387 self.margin_footer = 0.3
389 self.repeat_row_range = ""
390 self.repeat_col_range = ""
391 self.print_area_range = ""
393 self.page_order = 0
394 self.black_white = 0
395 self.draft_quality = 0
396 self.print_comments = 0
397 self.page_start = 0
399 self.fit_page = 0
400 self.fit_width = 0
401 self.fit_height = 0
403 self.hbreaks = []
404 self.vbreaks = []
406 self.protect_options = {}
407 self.protected_ranges = []
408 self.num_protected_ranges = 0
410 self.zoom = 100
411 self.zoom_scale_normal = True
412 self.zoom_to_fit = False
413 self.print_scale = 100
414 self.is_right_to_left = False
415 self.show_zeros = 1
416 self.leading_zeros = 0
418 self.outline_row_level = 0
419 self.outline_col_level = 0
420 self.outline_style = 0
421 self.outline_below = 1
422 self.outline_right = 1
423 self.outline_on = 1
424 self.outline_changed = False
426 self.names = {}
427 self.write_match = []
428 self.table = defaultdict(dict)
429 self.merge = []
430 self.merged_cells = {}
431 self.table_cells = {}
432 self.row_spans = {}
434 self.has_vml = False
435 self.has_header_vml = False
436 self.has_comments = False
437 self.comments = defaultdict(dict)
438 self.comments_list = []
439 self.comments_author = ""
440 self.comments_visible = False
441 self.vml_shape_id = 1024
442 self.buttons_list = []
443 self.vml_header_id = 0
445 self.autofilter_area = ""
446 self.autofilter_ref = None
447 self.filter_range = [0, 9]
448 self.filter_on = 0
449 self.filter_cols = {}
450 self.filter_type = {}
451 self.filter_cells = {}
453 self.row_sizes = {}
454 self.col_size_changed = False
455 self.row_size_changed = False
457 self.last_shape_id = 1
458 self.rel_count = 0
459 self.hlink_count = 0
460 self.hlink_refs = []
461 self.external_hyper_links = []
462 self.external_drawing_links = []
463 self.external_comment_links = []
464 self.external_vml_links = []
465 self.external_table_links = []
466 self.external_background_links = []
467 self.drawing_links = []
468 self.vml_drawing_links = []
469 self.charts = []
470 self.images = []
471 self.tables = []
472 self.sparklines = []
473 self.shapes = []
474 self.shape_hash = {}
475 self.drawing = 0
476 self.drawing_rels = {}
477 self.drawing_rels_id = 0
478 self.vml_drawing_rels = {}
479 self.vml_drawing_rels_id = 0
480 self.background_image = None
482 self.rstring = ""
483 self.previous_row = 0
485 self.validations = []
486 self.cond_formats = {}
487 self.data_bars_2010 = []
488 self.use_data_bars_2010 = False
489 self.dxf_priority = 1
490 self.page_view = 0
492 self.vba_codename = None
494 self.date_1904 = False
495 self.hyperlinks = defaultdict(dict)
497 self.strings_to_numbers = False
498 self.strings_to_urls = True
499 self.nan_inf_to_errors = False
500 self.strings_to_formulas = True
502 self.default_date_format = None
503 self.default_url_format = None
504 self.default_checkbox_format = None
505 self.workbook_add_format = None
506 self.remove_timezone = False
507 self.max_url_length = 2079
509 self.row_data_filename = None
510 self.row_data_fh = None
511 self.worksheet_meta = None
512 self.vml_data_id = None
513 self.vml_shape_id = None
515 self.row_data_filename = None
516 self.row_data_fh = None
517 self.row_data_fh_closed = False
519 self.vertical_dpi = 0
520 self.horizontal_dpi = 0
522 self.write_handlers = {}
524 self.ignored_errors = None
526 self.has_dynamic_arrays = False
527 self.use_future_functions = False
528 self.ignore_write_string = False
529 self.embedded_images = None
531 # Utility function for writing different types of strings.
532 def _write_token_as_string(self, token, row: int, col: int, *args):
533 # Map the data to the appropriate write_*() method.
534 if token == "":
535 return self._write_blank(row, col, *args)
537 if self.strings_to_formulas and token.startswith("="):
538 return self._write_formula(row, col, *args)
540 if token.startswith("{=") and token.endswith("}"):
541 return self._write_formula(row, col, *args)
543 # pylint: disable=too-many-boolean-expressions
544 if (
545 ":" in token
546 and self.strings_to_urls
547 and (
548 re.match("(ftp|http)s?://", token)
549 or re.match("mailto:", token)
550 or re.match("(in|ex)ternal:", token)
551 or re.match("file://", token)
552 )
553 ):
554 return self._write_url(row, col, *args)
556 if self.strings_to_numbers:
557 try:
558 f = float(token)
559 if self.nan_inf_to_errors or (not isnan(f) and not isinf(f)):
560 return self._write_number(row, col, f, *args[1:])
561 except ValueError:
562 # Not a number, write as a string.
563 pass
565 return self._write_string(row, col, *args)
567 # We have a plain string.
568 return self._write_string(row, col, *args)
570 @convert_cell_args
571 def write(self, row: int, col: int, *args) -> Union[Literal[0, -1], Any]:
572 """
573 Write data to a worksheet cell by calling the appropriate write_*()
574 method based on the type of data being passed.
576 Args:
577 row: The cell row (zero indexed).
578 col: The cell column (zero indexed).
579 *args: Args to pass to sub functions.
581 Returns:
582 0: Success.
583 -1: Row or column is out of worksheet bounds.
584 other: Return value of called method.
586 """
587 return self._write(row, col, *args)
589 # Undecorated version of write().
590 def _write(self, row: int, col: int, *args):
591 # pylint: disable=raise-missing-from
592 # Check the number of args passed.
593 if not args:
594 raise TypeError("write() takes at least 4 arguments (3 given)")
596 # The first arg should be the token for all write calls.
597 token = args[0]
599 # Avoid isinstance() for better performance.
600 token_type = token.__class__
602 # Check for any user defined type handlers with callback functions.
603 if token_type in self.write_handlers:
604 write_handler = self.write_handlers[token_type]
605 function_return = write_handler(self, row, col, *args)
607 # If the return value is None then the callback has returned
608 # control to this function and we should continue as
609 # normal. Otherwise we return the value to the caller and exit.
610 if function_return is None:
611 pass
612 else:
613 return function_return
615 # Write None as a blank cell.
616 if token is None:
617 return self._write_blank(row, col, *args)
619 # Check for standard Python types.
620 if token_type is bool:
621 return self._write_boolean(row, col, *args)
623 if token_type in (float, int, Decimal, Fraction):
624 return self._write_number(row, col, *args)
626 if token_type is str:
627 return self._write_token_as_string(token, row, col, *args)
629 if token_type in (
630 datetime.datetime,
631 datetime.date,
632 datetime.time,
633 datetime.timedelta,
634 ):
635 return self._write_datetime(row, col, *args)
637 # Resort to isinstance() for subclassed primitives.
639 # Write number types.
640 if isinstance(token, (float, int, Decimal, Fraction)):
641 return self._write_number(row, col, *args)
643 # Write string types.
644 if isinstance(token, str):
645 return self._write_token_as_string(token, row, col, *args)
647 # Write boolean types.
648 if isinstance(token, bool):
649 return self._write_boolean(row, col, *args)
651 # Write datetime objects.
652 if _supported_datetime(token):
653 return self._write_datetime(row, col, *args)
655 # Write Url type.
656 if isinstance(token, Url):
657 return self._write_url(row, col, *args)
659 # We haven't matched a supported type. Try float.
660 try:
661 f = float(token)
662 return self._write_number(row, col, f, *args[1:])
663 except ValueError:
664 pass
665 except TypeError:
666 raise TypeError(f"Unsupported type {type(token)} in write()")
668 # Finally try string.
669 try:
670 str(token)
671 return self._write_string(row, col, *args)
672 except ValueError:
673 raise TypeError(f"Unsupported type {type(token)} in write()")
675 @convert_cell_args
676 def write_string(
677 self, row: int, col: int, string: str, cell_format: Optional[Format] = None
678 ) -> Literal[0, -1, -2]:
679 """
680 Write a string to a worksheet cell.
682 Args:
683 row: The cell row (zero indexed).
684 col: The cell column (zero indexed).
685 string: Cell data. Str.
686 format: An optional cell Format object.
688 Returns:
689 0: Success.
690 -1: Row or column is out of worksheet bounds.
691 -2: String truncated to 32k characters.
693 """
694 return self._write_string(row, col, string, cell_format)
696 # Undecorated version of write_string().
697 def _write_string(
698 self, row: int, col: int, string: str, cell_format: Optional[Format] = None
699 ) -> Literal[0, -1, -2]:
700 str_error = 0
702 # Check that row and col are valid and store max and min values.
703 if self._check_dimensions(row, col):
704 return -1
706 # Check that the string is < 32767 chars.
707 if len(string) > self.xls_strmax:
708 string = string[: self.xls_strmax]
709 str_error = -2
711 # Write a shared string or an in-line string in constant_memory mode.
712 if not self.constant_memory:
713 string_index = self.str_table._get_shared_string_index(string)
714 else:
715 string_index = string
717 # Write previous row if in in-line string constant_memory mode.
718 if self.constant_memory and row > self.previous_row:
719 self._write_single_row(row)
721 # Store the cell data in the worksheet data table.
722 self.table[row][col] = CellStringTuple(string_index, cell_format)
724 return str_error
726 @convert_cell_args
727 def write_number(
728 self,
729 row: int,
730 col: int,
731 number: Union[int, float, Fraction],
732 cell_format: Optional[Format] = None,
733 ) -> Literal[0, -1]:
734 """
735 Write a number to a worksheet cell.
737 Args:
738 row: The cell row (zero indexed).
739 col: The cell column (zero indexed).
740 number: Cell data. Int or float.
741 cell_format: An optional cell Format object.
743 Returns:
744 0: Success.
745 -1: Row or column is out of worksheet bounds.
747 """
748 return self._write_number(row, col, number, cell_format)
750 # Undecorated version of write_number().
751 def _write_number(
752 self,
753 row: int,
754 col: int,
755 number: Union[int, float, Fraction],
756 cell_format: Optional[Format] = None,
757 ) -> Literal[0, -1]:
758 if isnan(number) or isinf(number):
759 if self.nan_inf_to_errors:
760 if isnan(number):
761 return self._write_formula(row, col, "#NUM!", cell_format, "#NUM!")
763 if number == math.inf:
764 return self._write_formula(row, col, "1/0", cell_format, "#DIV/0!")
766 if number == -math.inf:
767 return self._write_formula(row, col, "-1/0", cell_format, "#DIV/0!")
768 else:
769 raise TypeError(
770 "NAN/INF not supported in write_number() "
771 "without 'nan_inf_to_errors' Workbook() option"
772 )
774 if number.__class__ is Fraction:
775 number = float(number)
777 # Check that row and col are valid and store max and min values.
778 if self._check_dimensions(row, col):
779 return -1
781 # Write previous row if in in-line string constant_memory mode.
782 if self.constant_memory and row > self.previous_row:
783 self._write_single_row(row)
785 # Store the cell data in the worksheet data table.
786 self.table[row][col] = CellNumberTuple(number, cell_format)
788 return 0
790 @convert_cell_args
791 def write_blank(
792 self, row: int, col: int, blank: Any, cell_format: Optional[Format] = None
793 ):
794 """
795 Write a blank cell with formatting to a worksheet cell. The blank
796 token is ignored and the format only is written to the cell.
798 Args:
799 row: The cell row (zero indexed).
800 col: The cell column (zero indexed).
801 blank: Any value. It is ignored.
802 cell_format: An optional cell Format object.
804 Returns:
805 0: Success.
806 -1: Row or column is out of worksheet bounds.
808 """
809 return self._write_blank(row, col, blank, cell_format)
811 # Undecorated version of write_blank().
812 def _write_blank(
813 self, row: int, col: int, _, cell_format: Optional[Format] = None
814 ) -> Literal[0, -1]:
815 # Don't write a blank cell unless it has a format.
816 if cell_format is None:
817 return 0
819 # Check that row and col are valid and store max and min values.
820 if self._check_dimensions(row, col):
821 return -1
823 # Write previous row if in in-line string constant_memory mode.
824 if self.constant_memory and row > self.previous_row:
825 self._write_single_row(row)
827 # Store the cell data in the worksheet data table.
828 self.table[row][col] = CellBlankTuple(cell_format)
830 return 0
832 @convert_cell_args
833 def write_formula(
834 self,
835 row: int,
836 col: int,
837 formula: str,
838 cell_format: Optional[Format] = None,
839 value=0,
840 ) -> Literal[0, -1, -2]:
841 """
842 Write a formula to a worksheet cell.
844 Args:
845 row: The cell row (zero indexed).
846 col: The cell column (zero indexed).
847 formula: Cell formula.
848 cell_format: An optional cell Format object.
849 value: An optional value for the formula. Default is 0.
851 Returns:
852 0: Success.
853 -1: Row or column is out of worksheet bounds.
854 -2: Formula can't be None or empty.
856 """
857 # Check that row and col are valid and store max and min values.
858 return self._write_formula(row, col, formula, cell_format, value)
860 # Undecorated version of write_formula().
861 def _write_formula(
862 self,
863 row: int,
864 col: int,
865 formula: str,
866 cell_format: Optional[Format] = None,
867 value=0,
868 ) -> Literal[0, -1, -2]:
869 if self._check_dimensions(row, col):
870 return -1
872 if formula is None or formula == "":
873 warn("Formula can't be None or empty")
874 return -1
876 # Check for dynamic array functions.
877 if re_dynamic_function.search(formula):
878 return self.write_dynamic_array_formula(
879 row, col, row, col, formula, cell_format, value
880 )
882 # Hand off array formulas.
883 if formula.startswith("{") and formula.endswith("}"):
884 return self._write_array_formula(
885 row, col, row, col, formula, cell_format, value
886 )
888 # Modify the formula string, as needed.
889 formula = self._prepare_formula(formula)
891 # Write previous row if in in-line string constant_memory mode.
892 if self.constant_memory and row > self.previous_row:
893 self._write_single_row(row)
895 # Store the cell data in the worksheet data table.
896 self.table[row][col] = CellFormulaTuple(formula, cell_format, value)
898 return 0
900 @convert_range_args
901 def write_array_formula(
902 self,
903 first_row: int,
904 first_col: int,
905 last_row: int,
906 last_col: int,
907 formula: str,
908 cell_format: Optional[Format] = None,
909 value=0,
910 ) -> Literal[0, -1]:
911 """
912 Write a formula to a worksheet cell/range.
914 Args:
915 first_row: The first row of the cell range. (zero indexed).
916 first_col: The first column of the cell range.
917 last_row: The last row of the cell range. (zero indexed).
918 last_col: The last column of the cell range.
919 formula: Cell formula.
920 cell_format: An optional cell Format object.
921 value: An optional value for the formula. Default is 0.
923 Returns:
924 0: Success.
925 -1: Row or column is out of worksheet bounds.
927 """
928 # Check for dynamic array functions.
929 if re_dynamic_function.search(formula):
930 return self.write_dynamic_array_formula(
931 first_row, first_col, last_row, last_col, formula, cell_format, value
932 )
934 return self._write_array_formula(
935 first_row,
936 first_col,
937 last_row,
938 last_col,
939 formula,
940 cell_format,
941 value,
942 "static",
943 )
945 @convert_range_args
946 def write_dynamic_array_formula(
947 self,
948 first_row: int,
949 first_col: int,
950 last_row: int,
951 last_col: int,
952 formula: str,
953 cell_format: Optional[Format] = None,
954 value=0,
955 ) -> Literal[0, -1]:
956 """
957 Write a dynamic array formula to a worksheet cell/range.
959 Args:
960 first_row: The first row of the cell range. (zero indexed).
961 first_col: The first column of the cell range.
962 last_row: The last row of the cell range. (zero indexed).
963 last_col: The last column of the cell range.
964 formula: Cell formula.
965 cell_format: An optional cell Format object.
966 value: An optional value for the formula. Default is 0.
968 Returns:
969 0: Success.
970 -1: Row or column is out of worksheet bounds.
972 """
973 error = self._write_array_formula(
974 first_row,
975 first_col,
976 last_row,
977 last_col,
978 formula,
979 cell_format,
980 value,
981 "dynamic",
982 )
984 if error == 0:
985 self.has_dynamic_arrays = True
987 return error
989 # Utility method to strip equal sign and array braces from a formula and
990 # also expand out future and dynamic array formulas.
991 def _prepare_formula(self, formula, expand_future_functions=False):
992 # Remove array formula braces and the leading =.
993 if formula.startswith("{"):
994 formula = formula[1:]
995 if formula.startswith("="):
996 formula = formula[1:]
997 if formula.endswith("}"):
998 formula = formula[:-1]
1000 # Check if formula is already expanded by the user.
1001 if "_xlfn." in formula:
1002 return formula
1004 # Expand dynamic formulas.
1005 formula = re.sub(r"\bANCHORARRAY\(", "_xlfn.ANCHORARRAY(", formula)
1006 formula = re.sub(r"\bBYCOL\(", "_xlfn.BYCOL(", formula)
1007 formula = re.sub(r"\bBYROW\(", "_xlfn.BYROW(", formula)
1008 formula = re.sub(r"\bCHOOSECOLS\(", "_xlfn.CHOOSECOLS(", formula)
1009 formula = re.sub(r"\bCHOOSEROWS\(", "_xlfn.CHOOSEROWS(", formula)
1010 formula = re.sub(r"\bDROP\(", "_xlfn.DROP(", formula)
1011 formula = re.sub(r"\bEXPAND\(", "_xlfn.EXPAND(", formula)
1012 formula = re.sub(r"\bFILTER\(", "_xlfn._xlws.FILTER(", formula)
1013 formula = re.sub(r"\bHSTACK\(", "_xlfn.HSTACK(", formula)
1014 formula = re.sub(r"\bLAMBDA\(", "_xlfn.LAMBDA(", formula)
1015 formula = re.sub(r"\bMAKEARRAY\(", "_xlfn.MAKEARRAY(", formula)
1016 formula = re.sub(r"\bMAP\(", "_xlfn.MAP(", formula)
1017 formula = re.sub(r"\bRANDARRAY\(", "_xlfn.RANDARRAY(", formula)
1018 formula = re.sub(r"\bREDUCE\(", "_xlfn.REDUCE(", formula)
1019 formula = re.sub(r"\bSCAN\(", "_xlfn.SCAN(", formula)
1020 formula = re.sub(r"\SINGLE\(", "_xlfn.SINGLE(", formula)
1021 formula = re.sub(r"\bSEQUENCE\(", "_xlfn.SEQUENCE(", formula)
1022 formula = re.sub(r"\bSORT\(", "_xlfn._xlws.SORT(", formula)
1023 formula = re.sub(r"\bSORTBY\(", "_xlfn.SORTBY(", formula)
1024 formula = re.sub(r"\bSWITCH\(", "_xlfn.SWITCH(", formula)
1025 formula = re.sub(r"\bTAKE\(", "_xlfn.TAKE(", formula)
1026 formula = re.sub(r"\bTEXTSPLIT\(", "_xlfn.TEXTSPLIT(", formula)
1027 formula = re.sub(r"\bTOCOL\(", "_xlfn.TOCOL(", formula)
1028 formula = re.sub(r"\bTOROW\(", "_xlfn.TOROW(", formula)
1029 formula = re.sub(r"\bUNIQUE\(", "_xlfn.UNIQUE(", formula)
1030 formula = re.sub(r"\bVSTACK\(", "_xlfn.VSTACK(", formula)
1031 formula = re.sub(r"\bWRAPCOLS\(", "_xlfn.WRAPCOLS(", formula)
1032 formula = re.sub(r"\bWRAPROWS\(", "_xlfn.WRAPROWS(", formula)
1033 formula = re.sub(r"\bXLOOKUP\(", "_xlfn.XLOOKUP(", formula)
1035 if not self.use_future_functions and not expand_future_functions:
1036 return formula
1038 formula = re.sub(r"\bACOTH\(", "_xlfn.ACOTH(", formula)
1039 formula = re.sub(r"\bACOT\(", "_xlfn.ACOT(", formula)
1040 formula = re.sub(r"\bAGGREGATE\(", "_xlfn.AGGREGATE(", formula)
1041 formula = re.sub(r"\bARABIC\(", "_xlfn.ARABIC(", formula)
1042 formula = re.sub(r"\bARRAYTOTEXT\(", "_xlfn.ARRAYTOTEXT(", formula)
1043 formula = re.sub(r"\bBASE\(", "_xlfn.BASE(", formula)
1044 formula = re.sub(r"\bBETA.DIST\(", "_xlfn.BETA.DIST(", formula)
1045 formula = re.sub(r"\bBETA.INV\(", "_xlfn.BETA.INV(", formula)
1046 formula = re.sub(r"\bBINOM.DIST.RANGE\(", "_xlfn.BINOM.DIST.RANGE(", formula)
1047 formula = re.sub(r"\bBINOM.DIST\(", "_xlfn.BINOM.DIST(", formula)
1048 formula = re.sub(r"\bBINOM.INV\(", "_xlfn.BINOM.INV(", formula)
1049 formula = re.sub(r"\bBITAND\(", "_xlfn.BITAND(", formula)
1050 formula = re.sub(r"\bBITLSHIFT\(", "_xlfn.BITLSHIFT(", formula)
1051 formula = re.sub(r"\bBITOR\(", "_xlfn.BITOR(", formula)
1052 formula = re.sub(r"\bBITRSHIFT\(", "_xlfn.BITRSHIFT(", formula)
1053 formula = re.sub(r"\bBITXOR\(", "_xlfn.BITXOR(", formula)
1054 formula = re.sub(r"\bCEILING.MATH\(", "_xlfn.CEILING.MATH(", formula)
1055 formula = re.sub(r"\bCEILING.PRECISE\(", "_xlfn.CEILING.PRECISE(", formula)
1056 formula = re.sub(r"\bCHISQ.DIST.RT\(", "_xlfn.CHISQ.DIST.RT(", formula)
1057 formula = re.sub(r"\bCHISQ.DIST\(", "_xlfn.CHISQ.DIST(", formula)
1058 formula = re.sub(r"\bCHISQ.INV.RT\(", "_xlfn.CHISQ.INV.RT(", formula)
1059 formula = re.sub(r"\bCHISQ.INV\(", "_xlfn.CHISQ.INV(", formula)
1060 formula = re.sub(r"\bCHISQ.TEST\(", "_xlfn.CHISQ.TEST(", formula)
1061 formula = re.sub(r"\bCOMBINA\(", "_xlfn.COMBINA(", formula)
1062 formula = re.sub(r"\bCONCAT\(", "_xlfn.CONCAT(", formula)
1063 formula = re.sub(r"\bCONFIDENCE.NORM\(", "_xlfn.CONFIDENCE.NORM(", formula)
1064 formula = re.sub(r"\bCONFIDENCE.T\(", "_xlfn.CONFIDENCE.T(", formula)
1065 formula = re.sub(r"\bCOTH\(", "_xlfn.COTH(", formula)
1066 formula = re.sub(r"\bCOT\(", "_xlfn.COT(", formula)
1067 formula = re.sub(r"\bCOVARIANCE.P\(", "_xlfn.COVARIANCE.P(", formula)
1068 formula = re.sub(r"\bCOVARIANCE.S\(", "_xlfn.COVARIANCE.S(", formula)
1069 formula = re.sub(r"\bCSCH\(", "_xlfn.CSCH(", formula)
1070 formula = re.sub(r"\bCSC\(", "_xlfn.CSC(", formula)
1071 formula = re.sub(r"\bDAYS\(", "_xlfn.DAYS(", formula)
1072 formula = re.sub(r"\bDECIMAL\(", "_xlfn.DECIMAL(", formula)
1073 formula = re.sub(r"\bERF.PRECISE\(", "_xlfn.ERF.PRECISE(", formula)
1074 formula = re.sub(r"\bERFC.PRECISE\(", "_xlfn.ERFC.PRECISE(", formula)
1075 formula = re.sub(r"\bEXPON.DIST\(", "_xlfn.EXPON.DIST(", formula)
1076 formula = re.sub(r"\bF.DIST.RT\(", "_xlfn.F.DIST.RT(", formula)
1077 formula = re.sub(r"\bF.DIST\(", "_xlfn.F.DIST(", formula)
1078 formula = re.sub(r"\bF.INV.RT\(", "_xlfn.F.INV.RT(", formula)
1079 formula = re.sub(r"\bF.INV\(", "_xlfn.F.INV(", formula)
1080 formula = re.sub(r"\bF.TEST\(", "_xlfn.F.TEST(", formula)
1081 formula = re.sub(r"\bFILTERXML\(", "_xlfn.FILTERXML(", formula)
1082 formula = re.sub(r"\bFLOOR.MATH\(", "_xlfn.FLOOR.MATH(", formula)
1083 formula = re.sub(r"\bFLOOR.PRECISE\(", "_xlfn.FLOOR.PRECISE(", formula)
1084 formula = re.sub(
1085 r"\bFORECAST.ETS.CONFINT\(", "_xlfn.FORECAST.ETS.CONFINT(", formula
1086 )
1087 formula = re.sub(
1088 r"\bFORECAST.ETS.SEASONALITY\(", "_xlfn.FORECAST.ETS.SEASONALITY(", formula
1089 )
1090 formula = re.sub(r"\bFORECAST.ETS.STAT\(", "_xlfn.FORECAST.ETS.STAT(", formula)
1091 formula = re.sub(r"\bFORECAST.ETS\(", "_xlfn.FORECAST.ETS(", formula)
1092 formula = re.sub(r"\bFORECAST.LINEAR\(", "_xlfn.FORECAST.LINEAR(", formula)
1093 formula = re.sub(r"\bFORMULATEXT\(", "_xlfn.FORMULATEXT(", formula)
1094 formula = re.sub(r"\bGAMMA.DIST\(", "_xlfn.GAMMA.DIST(", formula)
1095 formula = re.sub(r"\bGAMMA.INV\(", "_xlfn.GAMMA.INV(", formula)
1096 formula = re.sub(r"\bGAMMALN.PRECISE\(", "_xlfn.GAMMALN.PRECISE(", formula)
1097 formula = re.sub(r"\bGAMMA\(", "_xlfn.GAMMA(", formula)
1098 formula = re.sub(r"\bGAUSS\(", "_xlfn.GAUSS(", formula)
1099 formula = re.sub(r"\bHYPGEOM.DIST\(", "_xlfn.HYPGEOM.DIST(", formula)
1100 formula = re.sub(r"\bIFNA\(", "_xlfn.IFNA(", formula)
1101 formula = re.sub(r"\bIFS\(", "_xlfn.IFS(", formula)
1102 formula = re.sub(r"\bIMAGE\(", "_xlfn.IMAGE(", formula)
1103 formula = re.sub(r"\bIMCOSH\(", "_xlfn.IMCOSH(", formula)
1104 formula = re.sub(r"\bIMCOT\(", "_xlfn.IMCOT(", formula)
1105 formula = re.sub(r"\bIMCSCH\(", "_xlfn.IMCSCH(", formula)
1106 formula = re.sub(r"\bIMCSC\(", "_xlfn.IMCSC(", formula)
1107 formula = re.sub(r"\bIMSECH\(", "_xlfn.IMSECH(", formula)
1108 formula = re.sub(r"\bIMSEC\(", "_xlfn.IMSEC(", formula)
1109 formula = re.sub(r"\bIMSINH\(", "_xlfn.IMSINH(", formula)
1110 formula = re.sub(r"\bIMTAN\(", "_xlfn.IMTAN(", formula)
1111 formula = re.sub(r"\bISFORMULA\(", "_xlfn.ISFORMULA(", formula)
1112 formula = re.sub(r"\bISOMITTED\(", "_xlfn.ISOMITTED(", formula)
1113 formula = re.sub(r"\bISOWEEKNUM\(", "_xlfn.ISOWEEKNUM(", formula)
1114 formula = re.sub(r"\bLET\(", "_xlfn.LET(", formula)
1115 formula = re.sub(r"\bLOGNORM.DIST\(", "_xlfn.LOGNORM.DIST(", formula)
1116 formula = re.sub(r"\bLOGNORM.INV\(", "_xlfn.LOGNORM.INV(", formula)
1117 formula = re.sub(r"\bMAXIFS\(", "_xlfn.MAXIFS(", formula)
1118 formula = re.sub(r"\bMINIFS\(", "_xlfn.MINIFS(", formula)
1119 formula = re.sub(r"\bMODE.MULT\(", "_xlfn.MODE.MULT(", formula)
1120 formula = re.sub(r"\bMODE.SNGL\(", "_xlfn.MODE.SNGL(", formula)
1121 formula = re.sub(r"\bMUNIT\(", "_xlfn.MUNIT(", formula)
1122 formula = re.sub(r"\bNEGBINOM.DIST\(", "_xlfn.NEGBINOM.DIST(", formula)
1123 formula = re.sub(r"\bNORM.DIST\(", "_xlfn.NORM.DIST(", formula)
1124 formula = re.sub(r"\bNORM.INV\(", "_xlfn.NORM.INV(", formula)
1125 formula = re.sub(r"\bNORM.S.DIST\(", "_xlfn.NORM.S.DIST(", formula)
1126 formula = re.sub(r"\bNORM.S.INV\(", "_xlfn.NORM.S.INV(", formula)
1127 formula = re.sub(r"\bNUMBERVALUE\(", "_xlfn.NUMBERVALUE(", formula)
1128 formula = re.sub(r"\bPDURATION\(", "_xlfn.PDURATION(", formula)
1129 formula = re.sub(r"\bPERCENTILE.EXC\(", "_xlfn.PERCENTILE.EXC(", formula)
1130 formula = re.sub(r"\bPERCENTILE.INC\(", "_xlfn.PERCENTILE.INC(", formula)
1131 formula = re.sub(r"\bPERCENTRANK.EXC\(", "_xlfn.PERCENTRANK.EXC(", formula)
1132 formula = re.sub(r"\bPERCENTRANK.INC\(", "_xlfn.PERCENTRANK.INC(", formula)
1133 formula = re.sub(r"\bPERMUTATIONA\(", "_xlfn.PERMUTATIONA(", formula)
1134 formula = re.sub(r"\bPHI\(", "_xlfn.PHI(", formula)
1135 formula = re.sub(r"\bPOISSON.DIST\(", "_xlfn.POISSON.DIST(", formula)
1136 formula = re.sub(r"\bQUARTILE.EXC\(", "_xlfn.QUARTILE.EXC(", formula)
1137 formula = re.sub(r"\bQUARTILE.INC\(", "_xlfn.QUARTILE.INC(", formula)
1138 formula = re.sub(r"\bQUERYSTRING\(", "_xlfn.QUERYSTRING(", formula)
1139 formula = re.sub(r"\bRANK.AVG\(", "_xlfn.RANK.AVG(", formula)
1140 formula = re.sub(r"\bRANK.EQ\(", "_xlfn.RANK.EQ(", formula)
1141 formula = re.sub(r"\bRRI\(", "_xlfn.RRI(", formula)
1142 formula = re.sub(r"\bSECH\(", "_xlfn.SECH(", formula)
1143 formula = re.sub(r"\bSEC\(", "_xlfn.SEC(", formula)
1144 formula = re.sub(r"\bSHEETS\(", "_xlfn.SHEETS(", formula)
1145 formula = re.sub(r"\bSHEET\(", "_xlfn.SHEET(", formula)
1146 formula = re.sub(r"\bSKEW.P\(", "_xlfn.SKEW.P(", formula)
1147 formula = re.sub(r"\bSTDEV.P\(", "_xlfn.STDEV.P(", formula)
1148 formula = re.sub(r"\bSTDEV.S\(", "_xlfn.STDEV.S(", formula)
1149 formula = re.sub(r"\bT.DIST.2T\(", "_xlfn.T.DIST.2T(", formula)
1150 formula = re.sub(r"\bT.DIST.RT\(", "_xlfn.T.DIST.RT(", formula)
1151 formula = re.sub(r"\bT.DIST\(", "_xlfn.T.DIST(", formula)
1152 formula = re.sub(r"\bT.INV.2T\(", "_xlfn.T.INV.2T(", formula)
1153 formula = re.sub(r"\bT.INV\(", "_xlfn.T.INV(", formula)
1154 formula = re.sub(r"\bT.TEST\(", "_xlfn.T.TEST(", formula)
1155 formula = re.sub(r"\bTEXTAFTER\(", "_xlfn.TEXTAFTER(", formula)
1156 formula = re.sub(r"\bTEXTBEFORE\(", "_xlfn.TEXTBEFORE(", formula)
1157 formula = re.sub(r"\bTEXTJOIN\(", "_xlfn.TEXTJOIN(", formula)
1158 formula = re.sub(r"\bUNICHAR\(", "_xlfn.UNICHAR(", formula)
1159 formula = re.sub(r"\bUNICODE\(", "_xlfn.UNICODE(", formula)
1160 formula = re.sub(r"\bVALUETOTEXT\(", "_xlfn.VALUETOTEXT(", formula)
1161 formula = re.sub(r"\bVAR.P\(", "_xlfn.VAR.P(", formula)
1162 formula = re.sub(r"\bVAR.S\(", "_xlfn.VAR.S(", formula)
1163 formula = re.sub(r"\bWEBSERVICE\(", "_xlfn.WEBSERVICE(", formula)
1164 formula = re.sub(r"\bWEIBULL.DIST\(", "_xlfn.WEIBULL.DIST(", formula)
1165 formula = re.sub(r"\bXMATCH\(", "_xlfn.XMATCH(", formula)
1166 formula = re.sub(r"\bXOR\(", "_xlfn.XOR(", formula)
1167 formula = re.sub(r"\bZ.TEST\(", "_xlfn.Z.TEST(", formula)
1169 return formula
1171 # Escape/expand table functions. This mainly involves converting Excel 2010
1172 # "@" table ref to 2007 "[#This Row],". We parse the string to avoid
1173 # replacements in string literals within the formula.
1174 @staticmethod
1175 def _prepare_table_formula(formula):
1176 if "@" not in formula:
1177 # No escaping required.
1178 return formula
1180 escaped_formula = []
1181 in_string_literal = False
1183 for char in formula:
1184 # Match the start/end of string literals to avoid escaping
1185 # references in strings.
1186 if char == '"':
1187 in_string_literal = not in_string_literal
1189 # Copy the string literal.
1190 if in_string_literal:
1191 escaped_formula.append(char)
1192 continue
1194 # Replace table reference.
1195 if char == "@":
1196 escaped_formula.append("[#This Row],")
1197 else:
1198 escaped_formula.append(char)
1200 return ("").join(escaped_formula)
1202 # Undecorated version of write_array_formula() and
1203 # write_dynamic_array_formula().
1204 def _write_array_formula(
1205 self,
1206 first_row,
1207 first_col,
1208 last_row,
1209 last_col,
1210 formula,
1211 cell_format=None,
1212 value=0,
1213 atype="static",
1214 ) -> Literal[0, -1]:
1215 # Swap last row/col with first row/col as necessary.
1216 if first_row > last_row:
1217 first_row, last_row = last_row, first_row
1218 if first_col > last_col:
1219 first_col, last_col = last_col, first_col
1221 # Check that row and col are valid and store max and min values.
1222 if self._check_dimensions(first_row, first_col):
1223 return -1
1224 if self._check_dimensions(last_row, last_col):
1225 return -1
1227 # Define array range
1228 if first_row == last_row and first_col == last_col:
1229 cell_range = xl_rowcol_to_cell(first_row, first_col)
1230 else:
1231 cell_range = (
1232 xl_rowcol_to_cell(first_row, first_col)
1233 + ":"
1234 + xl_rowcol_to_cell(last_row, last_col)
1235 )
1237 # Modify the formula string, as needed.
1238 formula = self._prepare_formula(formula)
1240 # Write previous row if in in-line string constant_memory mode.
1241 if self.constant_memory and first_row > self.previous_row:
1242 self._write_single_row(first_row)
1244 # Store the cell data in the worksheet data table.
1245 self.table[first_row][first_col] = CellArrayFormulaTuple(
1246 formula, cell_format, value, cell_range, atype
1247 )
1249 # Pad out the rest of the area with formatted zeroes.
1250 if not self.constant_memory:
1251 for row in range(first_row, last_row + 1):
1252 for col in range(first_col, last_col + 1):
1253 if row != first_row or col != first_col:
1254 self._write_number(row, col, 0, cell_format)
1256 return 0
1258 @convert_cell_args
1259 def write_datetime(
1260 self,
1261 row: int,
1262 col: int,
1263 date: datetime.datetime,
1264 cell_format: Optional[Format] = None,
1265 ) -> Literal[0, -1]:
1266 """
1267 Write a date or time to a worksheet cell.
1269 Args:
1270 row: The cell row (zero indexed).
1271 col: The cell column (zero indexed).
1272 date: Date and/or time as a datetime object.
1273 cell_format: A cell Format object.
1275 Returns:
1276 0: Success.
1277 -1: Row or column is out of worksheet bounds.
1279 """
1280 return self._write_datetime(row, col, date, cell_format)
1282 # Undecorated version of write_datetime().
1283 def _write_datetime(self, row: int, col: int, date, cell_format=None) -> int:
1284 # Check that row and col are valid and store max and min values.
1285 if self._check_dimensions(row, col):
1286 return -1
1288 # Write previous row if in in-line string constant_memory mode.
1289 if self.constant_memory and row > self.previous_row:
1290 self._write_single_row(row)
1292 # Convert datetime to an Excel date.
1293 number = self._convert_date_time(date)
1295 # Add the default date format.
1296 if cell_format is None:
1297 cell_format = self.default_date_format
1299 # Store the cell data in the worksheet data table.
1300 self.table[row][col] = CellDatetimeTuple(number, cell_format)
1302 return 0
1304 @convert_cell_args
1305 def write_boolean(
1306 self, row: int, col: int, boolean: bool, cell_format: Optional[Format] = None
1307 ):
1308 """
1309 Write a boolean value to a worksheet cell.
1311 Args:
1312 row: The cell row (zero indexed).
1313 col: The cell column (zero indexed).
1314 boolean: Cell data. bool type.
1315 cell_format: An optional cell Format object.
1317 Returns:
1318 0: Success.
1319 -1: Row or column is out of worksheet bounds.
1321 """
1322 return self._write_boolean(row, col, boolean, cell_format)
1324 # Undecorated version of write_boolean().
1325 def _write_boolean(self, row: int, col: int, boolean, cell_format=None) -> int:
1326 # Check that row and col are valid and store max and min values.
1327 if self._check_dimensions(row, col):
1328 return -1
1330 # Write previous row if in in-line string constant_memory mode.
1331 if self.constant_memory and row > self.previous_row:
1332 self._write_single_row(row)
1334 if boolean:
1335 value = 1
1336 else:
1337 value = 0
1339 # Store the cell data in the worksheet data table.
1340 self.table[row][col] = CellBooleanTuple(value, cell_format)
1342 return 0
1344 # Write a hyperlink. This is comprised of two elements: the displayed
1345 # string and the non-displayed link. The displayed string is the same as
1346 # the link unless an alternative string is specified. The display string
1347 # is written using the write_string() method. Therefore the max characters
1348 # string limit applies.
1349 #
1350 # The hyperlink can be to a http, ftp, mail, internal sheet, or external
1351 # directory urls.
1352 @convert_cell_args
1353 def write_url(
1354 self,
1355 row: int,
1356 col: int,
1357 url: str,
1358 cell_format: Optional[Format] = None,
1359 string: Optional[str] = None,
1360 tip: Optional[str] = None,
1361 ):
1362 """
1363 Write a hyperlink to a worksheet cell.
1365 Args:
1366 row: The cell row (zero indexed).
1367 col: The cell column (zero indexed).
1368 url: Hyperlink url.
1369 format: An optional cell Format object.
1370 string: An optional display string for the hyperlink.
1371 tip: An optional tooltip.
1372 Returns:
1373 0: Success.
1374 -1: Row or column is out of worksheet bounds.
1375 -2: String longer than 32767 characters.
1376 -3: URL longer than Excel limit of 255 characters.
1377 -4: Exceeds Excel limit of 65,530 urls per worksheet.
1378 """
1379 return self._write_url(row, col, url, cell_format, string, tip)
1381 # Undecorated version of write_url().
1382 def _write_url(
1383 self, row: int, col: int, url, cell_format=None, string=None, tip=None
1384 ) -> int:
1385 # Check that row and col are valid and store max and min values
1386 if self._check_dimensions(row, col):
1387 return -1
1389 # If the URL is a string convert it to a Url object.
1390 if not isinstance(url, Url):
1392 # For backwards compatibility check if the string URL exceeds the
1393 # Excel character limit for URLs and ignore it with a warning.
1394 max_url = self.max_url_length
1395 if "#" in url:
1396 url_str, anchor_str = url.split("#", 1)
1397 else:
1398 url_str = url
1399 anchor_str = ""
1401 if len(url_str) > max_url or len(anchor_str) > max_url:
1402 warn(
1403 f"Ignoring URL '{url}' with link or location/anchor > {max_url} "
1404 f"characters since it exceeds Excel's limit for URLs."
1405 )
1406 return -3
1408 url = Url(url)
1410 if string is not None:
1411 url._text = string
1413 if tip is not None:
1414 url._tip = tip
1416 # Check the limit of URLs per worksheet.
1417 self.hlink_count += 1
1419 if self.hlink_count > 65530:
1420 warn(
1421 f"Ignoring URL '{url._original_url}' since it exceeds Excel's limit of "
1422 f"65,530 URLs per worksheet."
1423 )
1424 return -4
1426 # Add the default URL format.
1427 if cell_format is None:
1428 cell_format = self.default_url_format
1430 if not self.ignore_write_string:
1431 # Write previous row if in in-line string constant_memory mode.
1432 if self.constant_memory and row > self.previous_row:
1433 self._write_single_row(row)
1435 # Write the hyperlink string.
1436 self._write_string(row, col, url.text, cell_format)
1438 # Store the hyperlink data in a separate structure.
1439 self.hyperlinks[row][col] = url
1441 return 0
1443 @convert_cell_args
1444 def write_rich_string(
1445 self, row: int, col: int, *args: Union[str, Format]
1446 ) -> Literal[0, -1, -2, -3, -4, -5]:
1447 """
1448 Write a "rich" string with multiple formats to a worksheet cell.
1450 Args:
1451 row: The cell row (zero indexed).
1452 col: The cell column (zero indexed).
1453 string_parts: String and format pairs.
1454 cell_format: Optional Format object.
1456 Returns:
1457 0: Success.
1458 -1: Row or column is out of worksheet bounds.
1459 -2: String truncated to 32k characters.
1460 -3: 2 consecutive formats used.
1461 -4: Empty string used.
1462 -5: Insufficient parameters.
1464 """
1466 return self._write_rich_string(row, col, *args)
1468 # Undecorated version of write_rich_string().
1469 def _write_rich_string(self, row: int, col: int, *args) -> int:
1470 tokens = list(args)
1471 cell_format = None
1472 string_index = 0
1473 raw_string = ""
1475 # Check that row and col are valid and store max and min values
1476 if self._check_dimensions(row, col):
1477 return -1
1479 # If the last arg is a format we use it as the cell format.
1480 if isinstance(tokens[-1], Format):
1481 cell_format = tokens.pop()
1483 # Create a temp XMLWriter object and use it to write the rich string
1484 # XML to a string.
1485 fh = StringIO()
1486 self.rstring = XMLwriter()
1487 self.rstring._set_filehandle(fh)
1489 # Create a temp format with the default font for unformatted fragments.
1490 default = Format()
1492 # Convert list of format, string tokens to pairs of (format, string)
1493 # except for the first string fragment which doesn't require a default
1494 # formatting run. Use the default for strings without a leading format.
1495 fragments = []
1496 previous = "format"
1497 pos = 0
1499 if len(tokens) <= 2:
1500 warn(
1501 "You must specify more than 2 format/fragments for rich "
1502 "strings. Ignoring input in write_rich_string()."
1503 )
1504 return -5
1506 for token in tokens:
1507 if not isinstance(token, Format):
1508 # Token is a string.
1509 if previous != "format":
1510 # If previous token wasn't a format add one before string.
1511 fragments.append(default)
1512 fragments.append(token)
1513 else:
1514 # If previous token was a format just add the string.
1515 fragments.append(token)
1517 if token == "":
1518 warn(
1519 "Excel doesn't allow empty strings in rich strings. "
1520 "Ignoring input in write_rich_string()."
1521 )
1522 return -4
1524 # Keep track of unformatted string.
1525 raw_string += token
1526 previous = "string"
1527 else:
1528 # Can't allow 2 formats in a row.
1529 if previous == "format" and pos > 0:
1530 warn(
1531 "Excel doesn't allow 2 consecutive formats in rich "
1532 "strings. Ignoring input in write_rich_string()."
1533 )
1534 return -3
1536 # Token is a format object. Add it to the fragment list.
1537 fragments.append(token)
1538 previous = "format"
1540 pos += 1
1542 # If the first token is a string start the <r> element.
1543 if not isinstance(fragments[0], Format):
1544 self.rstring._xml_start_tag("r")
1546 # Write the XML elements for the $format $string fragments.
1547 for token in fragments:
1548 if isinstance(token, Format):
1549 # Write the font run.
1550 self.rstring._xml_start_tag("r")
1551 self._write_font(token)
1552 else:
1553 # Write the string fragment part, with whitespace handling.
1554 attributes = []
1556 if _preserve_whitespace(token):
1557 attributes.append(("xml:space", "preserve"))
1559 self.rstring._xml_data_element("t", token, attributes)
1560 self.rstring._xml_end_tag("r")
1562 # Read the in-memory string.
1563 string = self.rstring.fh.getvalue()
1565 # Check that the string is < 32767 chars.
1566 if len(raw_string) > self.xls_strmax:
1567 warn(
1568 "String length must be less than or equal to Excel's limit "
1569 "of 32,767 characters in write_rich_string()."
1570 )
1571 return -2
1573 # Write a shared string or an in-line string in constant_memory mode.
1574 if not self.constant_memory:
1575 string_index = self.str_table._get_shared_string_index(string)
1576 else:
1577 string_index = string
1579 # Write previous row if in in-line string constant_memory mode.
1580 if self.constant_memory and row > self.previous_row:
1581 self._write_single_row(row)
1583 # Store the cell data in the worksheet data table.
1584 self.table[row][col] = CellRichStringTuple(
1585 string_index, cell_format, raw_string
1586 )
1588 return 0
1590 def add_write_handler(self, user_type, user_function) -> None:
1591 """
1592 Add a callback function to the write() method to handle user defined
1593 types.
1595 Args:
1596 user_type: The user type() to match on.
1597 user_function: The user defined function to write the type data.
1598 Returns:
1599 Nothing.
1601 """
1603 self.write_handlers[user_type] = user_function
1605 @convert_cell_args
1606 def write_row(
1607 self, row: int, col: int, data, cell_format: Optional[Format] = None
1608 ) -> Union[Literal[0], Any]:
1609 """
1610 Write a row of data starting from (row, col).
1612 Args:
1613 row: The cell row (zero indexed).
1614 col: The cell column (zero indexed).
1615 data: A list of tokens to be written with write().
1616 format: An optional cell Format object.
1617 Returns:
1618 0: Success.
1619 other: Return value of write() method.
1621 """
1622 for token in data:
1623 error = self._write(row, col, token, cell_format)
1624 if error:
1625 return error
1626 col += 1
1628 return 0
1630 @convert_cell_args
1631 def write_column(
1632 self, row: int, col: int, data, cell_format: Optional[Format] = None
1633 ) -> Union[Literal[0], Any]:
1634 """
1635 Write a column of data starting from (row, col).
1637 Args:
1638 row: The cell row (zero indexed).
1639 col: The cell column (zero indexed).
1640 data: A list of tokens to be written with write().
1641 format: An optional cell Format object.
1642 Returns:
1643 0: Success.
1644 other: Return value of write() method.
1646 """
1647 for token in data:
1648 error = self._write(row, col, token, cell_format)
1649 if error:
1650 return error
1651 row += 1
1653 return 0
1655 @convert_cell_args
1656 def insert_image(
1657 self,
1658 row: int,
1659 col: int,
1660 source: Union[str, BytesIO, Image],
1661 options: Optional[Dict[str, Any]] = None,
1662 ) -> Literal[0, -1]:
1663 """
1664 Insert an image with its top-left corner in a worksheet cell.
1666 Args:
1667 row: The cell row (zero indexed).
1668 col: The cell column (zero indexed).
1669 source: Filename, BytesIO, or Image object.
1670 options: Position, scale, url and data stream of the image.
1672 Returns:
1673 0: Success.
1674 -1: Row or column is out of worksheet bounds.
1676 """
1677 # Check insert (row, col) without storing.
1678 if self._check_dimensions(row, col, True, True):
1679 warn(f"Cannot insert image at ({row}, {col}).")
1680 return -1
1682 # Convert the source to an Image object.
1683 image = self._image_from_source(source, options)
1685 image._row = row
1686 image._col = col
1687 image._set_user_options(options)
1689 self.images.append(image)
1691 return 0
1693 @convert_cell_args
1694 def embed_image(
1695 self,
1696 row: int,
1697 col: int,
1698 source: Union[str, BytesIO, Image],
1699 options: Optional[Dict[str, Any]] = None,
1700 ) -> Literal[0, -1]:
1701 """
1702 Embed an image in a worksheet cell.
1704 Args:
1705 row: The cell row (zero indexed).
1706 col: The cell column (zero indexed).
1707 source: Filename, BytesIO, or Image object.
1708 options: Url and data stream of the image.
1710 Returns:
1711 0: Success.
1712 -1: Row or column is out of worksheet bounds.
1714 """
1715 # Check insert (row, col) without storing.
1716 if self._check_dimensions(row, col):
1717 warn(f"Cannot embed image at ({row}, {col}).")
1718 return -1
1720 if options is None:
1721 options = {}
1723 # Convert the source to an Image object.
1724 image = self._image_from_source(source, options)
1725 image._set_user_options(options)
1727 cell_format = options.get("cell_format", None)
1729 if image.url:
1730 if cell_format is None:
1731 cell_format = self.default_url_format
1733 self.ignore_write_string = True
1734 self.write_url(row, col, image.url, cell_format)
1735 self.ignore_write_string = False
1737 image_index = self.embedded_images.get_image_index(image)
1739 # Store the cell error and image index in the worksheet data table.
1740 self.table[row][col] = CellErrorTuple("#VALUE!", cell_format, image_index)
1742 return 0
1744 @convert_cell_args
1745 def insert_textbox(
1746 self, row: int, col: int, text: str, options: Optional[Dict[str, Any]] = None
1747 ) -> Literal[0, -1]:
1748 """
1749 Insert an textbox with its top-left corner in a worksheet cell.
1751 Args:
1752 row: The cell row (zero indexed).
1753 col: The cell column (zero indexed).
1754 text: The text for the textbox.
1755 options: Textbox options.
1757 Returns:
1758 0: Success.
1759 -1: Row or column is out of worksheet bounds.
1761 """
1762 # Check insert (row, col) without storing.
1763 if self._check_dimensions(row, col, True, True):
1764 warn(f"Cannot insert textbox at ({row}, {col}).")
1765 return -1
1767 if text is None:
1768 text = ""
1770 if options is None:
1771 options = {}
1773 x_offset = options.get("x_offset", 0)
1774 y_offset = options.get("y_offset", 0)
1775 x_scale = options.get("x_scale", 1)
1776 y_scale = options.get("y_scale", 1)
1777 anchor = options.get("object_position", 1)
1778 description = options.get("description", None)
1779 decorative = options.get("decorative", False)
1781 self.shapes.append(
1782 [
1783 row,
1784 col,
1785 x_offset,
1786 y_offset,
1787 x_scale,
1788 y_scale,
1789 text,
1790 anchor,
1791 options,
1792 description,
1793 decorative,
1794 ]
1795 )
1796 return 0
1798 @convert_cell_args
1799 def insert_chart(
1800 self, row: int, col: int, chart: Chart, options: Optional[Dict[str, Any]] = None
1801 ) -> Literal[0, -1, -2]:
1802 """
1803 Insert an chart with its top-left corner in a worksheet cell.
1805 Args:
1806 row: The cell row (zero indexed).
1807 col: The cell column (zero indexed).
1808 chart: Chart object.
1809 options: Position and scale of the chart.
1811 Returns:
1812 0: Success.
1813 -1: Row or column is out of worksheet bounds.
1815 """
1816 # Check insert (row, col) without storing.
1817 if self._check_dimensions(row, col, True, True):
1818 warn(f"Cannot insert chart at ({row}, {col}).")
1819 return -1
1821 if options is None:
1822 options = {}
1824 # Ensure a chart isn't inserted more than once.
1825 if chart.already_inserted or chart.combined and chart.combined.already_inserted:
1826 warn("Chart cannot be inserted in a worksheet more than once.")
1827 return -2
1829 chart.already_inserted = True
1831 if chart.combined:
1832 chart.combined.already_inserted = True
1834 x_offset = options.get("x_offset", 0)
1835 y_offset = options.get("y_offset", 0)
1836 x_scale = options.get("x_scale", 1)
1837 y_scale = options.get("y_scale", 1)
1838 anchor = options.get("object_position", 1)
1839 description = options.get("description", None)
1840 decorative = options.get("decorative", False)
1842 # Allow Chart to override the scale and offset.
1843 if chart.x_scale != 1:
1844 x_scale = chart.x_scale
1846 if chart.y_scale != 1:
1847 y_scale = chart.y_scale
1849 if chart.x_offset:
1850 x_offset = chart.x_offset
1852 if chart.y_offset:
1853 y_offset = chart.y_offset
1855 self.charts.append(
1856 [
1857 row,
1858 col,
1859 chart,
1860 x_offset,
1861 y_offset,
1862 x_scale,
1863 y_scale,
1864 anchor,
1865 description,
1866 decorative,
1867 ]
1868 )
1869 return 0
1871 @convert_cell_args
1872 def write_comment(
1873 self, row: int, col: int, comment: str, options: Optional[Dict[str, Any]] = None
1874 ) -> Literal[0, -1, -2]:
1875 """
1876 Write a comment to a worksheet cell.
1878 Args:
1879 row: The cell row (zero indexed).
1880 col: The cell column (zero indexed).
1881 comment: Cell comment. Str.
1882 options: Comment formatting options.
1884 Returns:
1885 0: Success.
1886 -1: Row or column is out of worksheet bounds.
1887 -2: String longer than 32k characters.
1889 """
1890 # Check that row and col are valid and store max and min values
1891 if self._check_dimensions(row, col):
1892 return -1
1894 # Check that the comment string is < 32767 chars.
1895 if len(comment) > self.xls_strmax:
1896 return -2
1898 self.has_vml = True
1899 self.has_comments = True
1901 # Store the options of the cell comment, to process on file close.
1902 comment = CommentType(row, col, comment, options)
1903 self.comments[row][col] = comment
1905 return 0
1907 def show_comments(self) -> None:
1908 """
1909 Make any comments in the worksheet visible.
1911 Args:
1912 None.
1914 Returns:
1915 Nothing.
1917 """
1918 self.comments_visible = True
1920 def set_background(
1921 self, source: Union[str, BytesIO, Image], is_byte_stream: bool = False
1922 ) -> Literal[0]:
1923 """
1924 Set a background image for a worksheet.
1926 Args:
1927 source: Filename, BytesIO, or Image object.
1928 is_byte_stream: Deprecated. Use a BytesIO object instead.
1930 Returns:
1931 0: Success.
1933 """
1934 # Convert the source to an Image object.
1935 image = self._image_from_source(source)
1937 self.background_image = image
1939 if is_byte_stream:
1940 warn(
1941 "The `is_byte_stream` parameter in `set_background()` is deprecated. "
1942 "This argument can be omitted if you are using a BytesIO object."
1943 )
1945 return 0
1947 def set_comments_author(self, author) -> None:
1948 """
1949 Set the default author of the cell comments.
1951 Args:
1952 author: Comment author name. String.
1954 Returns:
1955 Nothing.
1957 """
1958 self.comments_author = author
1960 def get_name(self):
1961 """
1962 Retrieve the worksheet name.
1964 Args:
1965 None.
1967 Returns:
1968 Nothing.
1970 """
1971 # There is no set_name() method. Name must be set in add_worksheet().
1972 return self.name
1974 def activate(self) -> None:
1975 """
1976 Set this worksheet as the active worksheet, i.e. the worksheet that is
1977 displayed when the workbook is opened. Also set it as selected.
1979 Note: An active worksheet cannot be hidden.
1981 Args:
1982 None.
1984 Returns:
1985 Nothing.
1987 """
1988 self.hidden = 0
1989 self.selected = 1
1990 self.worksheet_meta.activesheet = self.index
1992 def select(self) -> None:
1993 """
1994 Set current worksheet as a selected worksheet, i.e. the worksheet
1995 has its tab highlighted.
1997 Note: A selected worksheet cannot be hidden.
1999 Args:
2000 None.
2002 Returns:
2003 Nothing.
2005 """
2006 self.selected = 1
2007 self.hidden = 0
2009 def hide(self) -> None:
2010 """
2011 Hide the current worksheet.
2013 Args:
2014 None.
2016 Returns:
2017 Nothing.
2019 """
2020 self.hidden = 1
2022 # A hidden worksheet shouldn't be active or selected.
2023 self.selected = 0
2025 def very_hidden(self) -> None:
2026 """
2027 Hide the current worksheet. This can only be unhidden by VBA.
2029 Args:
2030 None.
2032 Returns:
2033 Nothing.
2035 """
2036 self.hidden = 2
2038 # A hidden worksheet shouldn't be active or selected.
2039 self.selected = 0
2041 def set_first_sheet(self) -> None:
2042 """
2043 Set current worksheet as the first visible sheet. This is necessary
2044 when there are a large number of worksheets and the activated
2045 worksheet is not visible on the screen.
2047 Note: A selected worksheet cannot be hidden.
2049 Args:
2050 None.
2052 Returns:
2053 Nothing.
2055 """
2056 self.hidden = 0 # Active worksheet can't be hidden.
2057 self.worksheet_meta.firstsheet = self.index
2059 @convert_column_args
2060 def set_column(
2061 self,
2062 first_col: int,
2063 last_col: int,
2064 width: Optional[float] = None,
2065 cell_format: Optional[Format] = None,
2066 options: Optional[Dict[str, Any]] = None,
2067 ) -> Literal[0, -1]:
2068 """
2069 Set the width, and other properties of a single column or a
2070 range of columns.
2072 Args:
2073 first_col: First column (zero-indexed).
2074 last_col: Last column (zero-indexed). Can be same as first_col.
2075 width: Column width. (optional).
2076 cell_format: Column cell_format. (optional).
2077 options: Dict of options such as hidden and level.
2079 Returns:
2080 0: Success.
2081 -1: Column number is out of worksheet bounds.
2083 """
2084 # Convert from Excel character width to pixels. The conversion is
2085 # different below 1 character widths.
2086 if width is None:
2087 width_pixels = None
2088 elif width == 0.0:
2089 width_pixels = 0
2090 elif width < 1.0:
2091 width_pixels = round(width * (self.max_digit_width + self.cell_padding))
2092 else:
2093 width_pixels = round(width * self.max_digit_width) + self.cell_padding
2095 return self.set_column_pixels(
2096 first_col, last_col, width_pixels, cell_format, options
2097 )
2099 @convert_column_args
2100 def set_column_pixels(
2101 self,
2102 first_col: int,
2103 last_col: int,
2104 width: Optional[float] = None,
2105 cell_format: Optional[Format] = None,
2106 options: Optional[Dict[str, Any]] = None,
2107 ) -> Literal[0, -1]:
2108 """
2109 Set the width, and other properties of a single column or a
2110 range of columns, where column width is in pixels.
2112 Args:
2113 first_col: First column (zero-indexed).
2114 last_col: Last column (zero-indexed). Can be same as first_col.
2115 width: Column width in pixels. (optional).
2116 cell_format: Column cell_format. (optional).
2117 options: Dict of options such as hidden and level.
2119 Returns:
2120 0: Success.
2121 -1: Column number is out of worksheet bounds.
2123 """
2124 if options is None:
2125 options = {}
2127 # Ensure 2nd col is larger than first.
2128 if first_col > last_col:
2129 (first_col, last_col) = (last_col, first_col)
2131 # Don't modify the row dimensions when checking the columns.
2132 ignore_row = True
2134 # Set optional column values.
2135 hidden = options.get("hidden", False)
2136 collapsed = options.get("collapsed", False)
2137 level = options.get("level", 0)
2139 # Store the column dimension only in some conditions.
2140 if cell_format or (width and hidden):
2141 ignore_col = False
2142 else:
2143 ignore_col = True
2145 # Check that each column is valid and store the max and min values.
2146 if self._check_dimensions(0, last_col, ignore_row, ignore_col):
2147 return -1
2148 if self._check_dimensions(0, first_col, ignore_row, ignore_col):
2149 return -1
2151 # Set the limits for the outline levels (0 <= x <= 7).
2152 level = max(level, 0)
2153 level = min(level, 7)
2155 self.outline_col_level = max(self.outline_col_level, level)
2157 # Store the column data.
2158 for col in range(first_col, last_col + 1):
2159 self.col_info[col] = ColumnInfo(
2160 width=width,
2161 column_format=cell_format,
2162 hidden=hidden,
2163 level=level,
2164 collapsed=collapsed,
2165 )
2167 # Store the column change to allow optimizations.
2168 self.col_size_changed = True
2170 return 0
2172 def autofit(self, max_width: int = None) -> None:
2173 """
2174 Simulate autofit based on the data, and datatypes in each column.
2176 Args:
2177 max_width (optional): max column width to autofit, in pixels.
2179 Returns:
2180 Nothing.
2182 """
2183 # pylint: disable=too-many-nested-blocks
2184 if self.constant_memory:
2185 warn("Autofit is not supported in constant_memory mode.")
2186 return
2188 # No data written to the target sheet; nothing to autofit
2189 if self.dim_rowmax is None:
2190 return
2192 # Store the max pixel width for each column.
2193 col_width_max = {}
2195 # Convert the autofit maximum pixel width to a column/character width,
2196 # but limit it to the Excel max limit.
2197 if max_width is None:
2198 max_width = self.max_col_width
2200 max_width = min(max_width, self.max_col_width)
2202 # Create a reverse lookup for the share strings table so we can convert
2203 # the string id back to the original string.
2204 strings = sorted(
2205 self.str_table.string_table, key=self.str_table.string_table.__getitem__
2206 )
2208 for row_num in range(self.dim_rowmin, self.dim_rowmax + 1):
2209 if not self.table.get(row_num):
2210 continue
2212 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
2213 if col_num in self.table[row_num]:
2214 cell = self.table[row_num][col_num]
2215 cell_type = cell.__class__.__name__
2216 length = 0
2218 if cell_type in ("String", "RichString"):
2219 # Handle strings and rich strings.
2220 #
2221 # For standard shared strings we do a reverse lookup
2222 # from the shared string id to the actual string. For
2223 # rich strings we use the unformatted string. We also
2224 # split multi-line strings and handle each part
2225 # separately.
2226 if cell_type == "String":
2227 string_id = cell.string
2228 string = strings[string_id]
2229 else:
2230 string = cell.raw_string
2232 if "\n" not in string:
2233 # Single line string.
2234 length = xl_pixel_width(string)
2235 else:
2236 # Handle multi-line strings.
2237 for string in string.split("\n"):
2238 seg_length = xl_pixel_width(string)
2239 length = max(length, seg_length)
2241 elif cell_type == "Number":
2242 # Handle numbers.
2243 #
2244 # We use a workaround/optimization for numbers since
2245 # digits all have a pixel width of 7. This gives a
2246 # slightly greater width for the decimal place and
2247 # minus sign but only by a few pixels and
2248 # over-estimation is okay.
2249 length = 7 * len(str(cell.number))
2251 elif cell_type == "Datetime":
2252 # Handle dates.
2253 #
2254 # The following uses the default width for mm/dd/yyyy
2255 # dates. It isn't feasible to parse the number format
2256 # to get the actual string width for all format types.
2257 length = self.default_date_width
2259 elif cell_type == "Boolean":
2260 # Handle boolean values.
2261 #
2262 # Use the Excel standard widths for TRUE and FALSE.
2263 if cell.boolean:
2264 length = 31
2265 else:
2266 length = 36
2268 elif cell_type in ("Formula", "ArrayFormula"):
2269 # Handle formulas.
2270 #
2271 # We only try to autofit a formula if it has a
2272 # non-zero value.
2273 if isinstance(cell.value, (float, int)):
2274 if cell.value > 0:
2275 length = 7 * len(str(cell.value))
2277 elif isinstance(cell.value, str):
2278 length = xl_pixel_width(cell.value)
2280 elif isinstance(cell.value, bool):
2281 if cell.value:
2282 length = 31
2283 else:
2284 length = 36
2286 # If the cell is in an autofilter header we add an
2287 # additional 16 pixels for the dropdown arrow.
2288 if self.filter_cells.get((row_num, col_num)) and length > 0:
2289 length += 16
2291 # Add the string length to the lookup table.
2292 width_max = col_width_max.get(col_num, 0)
2293 if length > width_max:
2294 col_width_max[col_num] = length
2296 # Apply the width to the column.
2297 for col_num, width in col_width_max.items():
2298 # Add a 7 pixels padding, like Excel.
2299 width += 7
2301 # Limit the width to the maximum user or Excel value.
2302 width = min(width, max_width)
2304 # Add the width to an existing col info structure or add a new one.
2305 if self.col_info.get(col_num):
2306 # We only update the width for an existing column if it is
2307 # greater than the user defined value. This allows the user
2308 # to pre-load a minimum col width.
2309 col_info = self.col_info.get(col_num)
2310 user_width = col_info.width
2311 hidden = col_info.hidden
2312 if user_width is not None and not hidden:
2313 # Col info is user defined.
2314 if width > user_width:
2315 self.col_info[col_num].width = width
2316 self.col_info[col_num].hidden = True
2317 else:
2318 self.col_info[col_num].width = width
2319 self.col_info[col_num].hidden = True
2320 else:
2321 self.col_info[col_num] = ColumnInfo(
2322 width=width,
2323 autofit=True,
2324 )
2326 def set_row(
2327 self,
2328 row: int,
2329 height: Optional[float] = None,
2330 cell_format: Optional[Format] = None,
2331 options: Optional[Dict[str, Any]] = None,
2332 ) -> Literal[0, -1]:
2333 """
2334 Set the width, and other properties of a row.
2336 Args:
2337 row: Row number (zero-indexed).
2338 height: Row height. (optional).
2339 cell_format: Row cell_format. (optional).
2340 options: Dict of options such as hidden, level and collapsed.
2342 Returns:
2343 0: Success.
2344 -1: Row number is out of worksheet bounds.
2346 """
2347 if height is not None:
2348 pixel_height = round(height * 4.0 / 3.0)
2349 else:
2350 pixel_height = None
2352 return self.set_row_pixels(row, pixel_height, cell_format, options)
2354 def set_row_pixels(
2355 self,
2356 row: int,
2357 height: Optional[float] = None,
2358 cell_format: Optional[Format] = None,
2359 options: Optional[Dict[str, Any]] = None,
2360 ) -> Literal[0, -1]:
2361 """
2362 Set the width (in pixels), and other properties of a row.
2364 Args:
2365 row: Row number (zero-indexed).
2366 height: Row height in pixels. (optional).
2367 cell_format: Row cell_format. (optional).
2368 options: Dict of options such as hidden, level and collapsed.
2370 Returns:
2371 0: Success.
2372 -1: Row number is out of worksheet bounds.
2374 """
2375 if options is None:
2376 options = {}
2378 # Use minimum col in _check_dimensions().
2379 if self.dim_colmin is not None:
2380 min_col = self.dim_colmin
2381 else:
2382 min_col = 0
2384 # Check that row is valid.
2385 if self._check_dimensions(row, min_col):
2386 return -1
2388 if height is None:
2389 height = self.default_row_height
2391 # Set optional row values.
2392 hidden = options.get("hidden", False)
2393 collapsed = options.get("collapsed", False)
2394 level = options.get("level", 0)
2396 # If the height is 0 the row is hidden and the height is the default.
2397 if height == 0:
2398 hidden = True
2399 height = self.default_row_height
2401 # Set the limits for the outline levels (0 <= x <= 7).
2402 level = max(level, 0)
2403 level = min(level, 7)
2405 self.outline_row_level = max(self.outline_row_level, level)
2407 # Store the row properties.
2408 self.row_info[row] = RowInfo(
2409 height=height,
2410 row_format=cell_format,
2411 hidden=hidden,
2412 level=level,
2413 collapsed=collapsed,
2414 )
2416 # Store the row change to allow optimizations.
2417 self.row_size_changed = True
2419 # Store the row sizes for use when calculating image vertices.
2420 self.row_sizes[row] = [height, hidden]
2422 return 0
2424 def set_default_row(
2425 self, height: Optional[float] = None, hide_unused_rows: bool = False
2426 ) -> None:
2427 """
2428 Set the default row properties.
2430 Args:
2431 height: Default height. Optional, defaults to 15.
2432 hide_unused_rows: Hide unused rows. Optional, defaults to False.
2434 Returns:
2435 Nothing.
2437 """
2438 if height is None:
2439 pixel_height = self.default_row_height
2440 else:
2441 pixel_height = int(round(height * 4.0 / 3.0))
2443 if pixel_height != self.original_row_height:
2444 # Store the row change to allow optimizations.
2445 self.row_size_changed = True
2446 self.default_row_height = pixel_height
2448 if hide_unused_rows:
2449 self.default_row_zeroed = 1
2451 @convert_range_args
2452 def merge_range(
2453 self,
2454 first_row: int,
2455 first_col: int,
2456 last_row: int,
2457 last_col: int,
2458 data: Any,
2459 cell_format: Optional[Format] = None,
2460 ) -> int:
2461 """
2462 Merge a range of cells.
2464 Args:
2465 first_row: The first row of the cell range. (zero indexed).
2466 first_col: The first column of the cell range.
2467 last_row: The last row of the cell range. (zero indexed).
2468 last_col: The last column of the cell range.
2469 data: Cell data.
2470 cell_format: Cell Format object.
2472 Returns:
2473 0: Success.
2474 -1: Row or column is out of worksheet bounds.
2475 other: Return value of write().
2477 """
2478 # Merge a range of cells. The first cell should contain the data and
2479 # the others should be blank. All cells should have the same format.
2481 # Excel doesn't allow a single cell to be merged
2482 if first_row == last_row and first_col == last_col:
2483 warn("Can't merge single cell")
2484 return -1
2486 # Swap last row/col with first row/col as necessary
2487 if first_row > last_row:
2488 (first_row, last_row) = (last_row, first_row)
2489 if first_col > last_col:
2490 (first_col, last_col) = (last_col, first_col)
2492 # Check that row and col are valid and store max and min values.
2493 if self._check_dimensions(first_row, first_col):
2494 return -1
2495 if self._check_dimensions(last_row, last_col):
2496 return -1
2498 # Check if the merge range overlaps a previous merged or table range.
2499 # This is a critical file corruption error in Excel.
2500 cell_range = xl_range(first_row, first_col, last_row, last_col)
2501 for row in range(first_row, last_row + 1):
2502 for col in range(first_col, last_col + 1):
2503 if self.merged_cells.get((row, col)):
2504 previous_range = self.merged_cells.get((row, col))
2505 raise OverlappingRange(
2506 f"Merge range '{cell_range}' overlaps previous merge "
2507 f"range '{previous_range}'."
2508 )
2510 if self.table_cells.get((row, col)):
2511 previous_range = self.table_cells.get((row, col))
2512 raise OverlappingRange(
2513 f"Merge range '{cell_range}' overlaps previous table "
2514 f"range '{previous_range}'."
2515 )
2517 self.merged_cells[(row, col)] = cell_range
2519 # Store the merge range.
2520 self.merge.append([first_row, first_col, last_row, last_col])
2522 # Write the first cell
2523 self._write(first_row, first_col, data, cell_format)
2525 # Pad out the rest of the area with formatted blank cells.
2526 for row in range(first_row, last_row + 1):
2527 for col in range(first_col, last_col + 1):
2528 if row == first_row and col == first_col:
2529 continue
2530 self._write_blank(row, col, "", cell_format)
2532 return 0
2534 @convert_range_args
2535 def autofilter(
2536 self, first_row: int, first_col: int, last_row: int, last_col: int
2537 ) -> None:
2538 """
2539 Set the autofilter area in the worksheet.
2541 Args:
2542 first_row: The first row of the cell range. (zero indexed).
2543 first_col: The first column of the cell range.
2544 last_row: The last row of the cell range. (zero indexed).
2545 last_col: The last column of the cell range.
2547 Returns:
2548 Nothing.
2550 """
2551 # Reverse max and min values if necessary.
2552 if last_row < first_row:
2553 (first_row, last_row) = (last_row, first_row)
2554 if last_col < first_col:
2555 (first_col, last_col) = (last_col, first_col)
2557 # Build up the autofilter area range "Sheet1!$A$1:$C$13".
2558 area = self._convert_name_area(first_row, first_col, last_row, last_col)
2559 ref = xl_range(first_row, first_col, last_row, last_col)
2561 self.autofilter_area = area
2562 self.autofilter_ref = ref
2563 self.filter_range = [first_col, last_col]
2565 # Store the filter cell positions for use in the autofit calculation.
2566 for col in range(first_col, last_col + 1):
2567 # Check that the autofilter doesn't overlap a table filter.
2568 if self.filter_cells.get((first_row, col)):
2569 filter_type, filter_range = self.filter_cells.get((first_row, col))
2570 if filter_type == "table":
2571 raise OverlappingRange(
2572 f"Worksheet autofilter range '{ref}' overlaps previous "
2573 f"Table autofilter range '{filter_range}'."
2574 )
2576 self.filter_cells[(first_row, col)] = ("worksheet", ref)
2578 def filter_column(self, col: int, criteria: str) -> None:
2579 """
2580 Set the column filter criteria.
2582 Args:
2583 col: Filter column (zero-indexed).
2584 criteria: Filter criteria.
2586 Returns:
2587 Nothing.
2589 """
2590 if not self.autofilter_area:
2591 warn("Must call autofilter() before filter_column()")
2592 return
2594 # Check for a column reference in A1 notation and substitute.
2595 try:
2596 int(col)
2597 except ValueError:
2598 # Convert col ref to a cell ref and then to a col number.
2599 col_letter = col
2600 (_, col) = xl_cell_to_rowcol(col + "1")
2602 if col >= self.xls_colmax:
2603 warn(f"Invalid column '{col_letter}'")
2604 return
2606 (col_first, col_last) = self.filter_range
2608 # Reject column if it is outside filter range.
2609 if col < col_first or col > col_last:
2610 warn(
2611 f"Column '{col}' outside autofilter() column "
2612 f"range ({col_first}, {col_last})"
2613 )
2614 return
2616 tokens = self._extract_filter_tokens(criteria)
2618 if len(tokens) not in (3, 7):
2619 warn(f"Incorrect number of tokens in criteria '{criteria}'")
2621 tokens = self._parse_filter_expression(criteria, tokens)
2623 # Excel handles single or double custom filters as default filters.
2624 # We need to check for them and handle them accordingly.
2625 if len(tokens) == 2 and tokens[0] == 2:
2626 # Single equality.
2627 self.filter_column_list(col, [tokens[1]])
2628 elif len(tokens) == 5 and tokens[0] == 2 and tokens[2] == 1 and tokens[3] == 2:
2629 # Double equality with "or" operator.
2630 self.filter_column_list(col, [tokens[1], tokens[4]])
2631 else:
2632 # Non default custom filter.
2633 self.filter_cols[col] = tokens
2634 self.filter_type[col] = 0
2636 self.filter_on = 1
2638 def filter_column_list(self, col: int, filters: List[str]) -> None:
2639 """
2640 Set the column filter criteria in Excel 2007 list style.
2642 Args:
2643 col: Filter column (zero-indexed).
2644 filters: List of filter criteria to match.
2646 Returns:
2647 Nothing.
2649 """
2650 if not self.autofilter_area:
2651 warn("Must call autofilter() before filter_column()")
2652 return
2654 # Check for a column reference in A1 notation and substitute.
2655 try:
2656 int(col)
2657 except ValueError:
2658 # Convert col ref to a cell ref and then to a col number.
2659 col_letter = col
2660 (_, col) = xl_cell_to_rowcol(col + "1")
2662 if col >= self.xls_colmax:
2663 warn(f"Invalid column '{col_letter}'")
2664 return
2666 (col_first, col_last) = self.filter_range
2668 # Reject column if it is outside filter range.
2669 if col < col_first or col > col_last:
2670 warn(
2671 f"Column '{col}' outside autofilter() column range "
2672 f"({col_first},{col_last})"
2673 )
2674 return
2676 self.filter_cols[col] = filters
2677 self.filter_type[col] = 1
2678 self.filter_on = 1
2680 @convert_range_args
2681 def data_validation(
2682 self,
2683 first_row: int,
2684 first_col: int,
2685 last_row: int,
2686 last_col: int,
2687 options: Optional[Dict[str, Any]] = None,
2688 ) -> Literal[0, -1, -2]:
2689 """
2690 Add a data validation to a worksheet.
2692 Args:
2693 first_row: The first row of the cell range. (zero indexed).
2694 first_col: The first column of the cell range.
2695 last_row: The last row of the cell range. (zero indexed).
2696 last_col: The last column of the cell range.
2697 options: Data validation options.
2699 Returns:
2700 0: Success.
2701 -1: Row or column is out of worksheet bounds.
2702 -2: Incorrect parameter or option.
2703 """
2704 # Check that row and col are valid without storing the values.
2705 if self._check_dimensions(first_row, first_col, True, True):
2706 return -1
2707 if self._check_dimensions(last_row, last_col, True, True):
2708 return -1
2710 if options is None:
2711 options = {}
2712 else:
2713 # Copy the user defined options so they aren't modified.
2714 options = options.copy()
2716 # Valid input parameters.
2717 valid_parameters = {
2718 "validate",
2719 "criteria",
2720 "value",
2721 "source",
2722 "minimum",
2723 "maximum",
2724 "ignore_blank",
2725 "dropdown",
2726 "show_input",
2727 "input_title",
2728 "input_message",
2729 "show_error",
2730 "error_title",
2731 "error_message",
2732 "error_type",
2733 "other_cells",
2734 "multi_range",
2735 }
2737 # Check for valid input parameters.
2738 for param_key in options.keys():
2739 if param_key not in valid_parameters:
2740 warn(f"Unknown parameter '{param_key}' in data_validation()")
2741 return -2
2743 # Map alternative parameter names 'source' or 'minimum' to 'value'.
2744 if "source" in options:
2745 options["value"] = options["source"]
2746 if "minimum" in options:
2747 options["value"] = options["minimum"]
2749 # 'validate' is a required parameter.
2750 if "validate" not in options:
2751 warn("Parameter 'validate' is required in data_validation()")
2752 return -2
2754 # List of valid validation types.
2755 valid_types = {
2756 "any": "none",
2757 "any value": "none",
2758 "whole number": "whole",
2759 "whole": "whole",
2760 "integer": "whole",
2761 "decimal": "decimal",
2762 "list": "list",
2763 "date": "date",
2764 "time": "time",
2765 "text length": "textLength",
2766 "length": "textLength",
2767 "custom": "custom",
2768 }
2770 # Check for valid validation types.
2771 if options["validate"] not in valid_types:
2772 warn(
2773 f"Unknown validation type '{options['validate']}' for parameter "
2774 f"'validate' in data_validation()"
2775 )
2776 return -2
2778 options["validate"] = valid_types[options["validate"]]
2780 # No action is required for validation type 'any' if there are no
2781 # input messages to display.
2782 if (
2783 options["validate"] == "none"
2784 and options.get("input_title") is None
2785 and options.get("input_message") is None
2786 ):
2787 return -2
2789 # The any, list and custom validations don't have a criteria so we use
2790 # a default of 'between'.
2791 if (
2792 options["validate"] == "none"
2793 or options["validate"] == "list"
2794 or options["validate"] == "custom"
2795 ):
2796 options["criteria"] = "between"
2797 options["maximum"] = None
2799 # 'criteria' is a required parameter.
2800 if "criteria" not in options:
2801 warn("Parameter 'criteria' is required in data_validation()")
2802 return -2
2804 # Valid criteria types.
2805 criteria_types = {
2806 "between": "between",
2807 "not between": "notBetween",
2808 "equal to": "equal",
2809 "=": "equal",
2810 "==": "equal",
2811 "not equal to": "notEqual",
2812 "!=": "notEqual",
2813 "<>": "notEqual",
2814 "greater than": "greaterThan",
2815 ">": "greaterThan",
2816 "less than": "lessThan",
2817 "<": "lessThan",
2818 "greater than or equal to": "greaterThanOrEqual",
2819 ">=": "greaterThanOrEqual",
2820 "less than or equal to": "lessThanOrEqual",
2821 "<=": "lessThanOrEqual",
2822 }
2824 # Check for valid criteria types.
2825 if options["criteria"] not in criteria_types:
2826 warn(
2827 f"Unknown criteria type '{options['criteria']}' for parameter "
2828 f"'criteria' in data_validation()"
2829 )
2830 return -2
2832 options["criteria"] = criteria_types[options["criteria"]]
2834 # 'Between' and 'Not between' criteria require 2 values.
2835 if options["criteria"] == "between" or options["criteria"] == "notBetween":
2836 if "maximum" not in options:
2837 warn(
2838 "Parameter 'maximum' is required in data_validation() "
2839 "when using 'between' or 'not between' criteria"
2840 )
2841 return -2
2842 else:
2843 options["maximum"] = None
2845 # Valid error dialog types.
2846 error_types = {
2847 "stop": 0,
2848 "warning": 1,
2849 "information": 2,
2850 }
2852 # Check for valid error dialog types.
2853 if "error_type" not in options:
2854 options["error_type"] = 0
2855 elif options["error_type"] not in error_types:
2856 warn(
2857 f"Unknown criteria type '{options['error_type']}' "
2858 f"for parameter 'error_type'."
2859 )
2860 return -2
2861 else:
2862 options["error_type"] = error_types[options["error_type"]]
2864 # Convert date/times value if required.
2865 if (
2866 options["validate"] in ("date", "time")
2867 and options["value"]
2868 and _supported_datetime(options["value"])
2869 ):
2870 date_time = self._convert_date_time(options["value"])
2871 # Format date number to the same precision as Excel.
2872 options["value"] = f"{date_time:.16g}"
2874 if options["maximum"] and _supported_datetime(options["maximum"]):
2875 date_time = self._convert_date_time(options["maximum"])
2876 options["maximum"] = f"{date_time:.16g}"
2878 # Check that the input title doesn't exceed the maximum length.
2879 if options.get("input_title") and len(options["input_title"]) > 32:
2880 warn(
2881 f"Length of input title '{options['input_title']}' "
2882 f"exceeds Excel's limit of 32"
2883 )
2884 return -2
2886 # Check that the error title doesn't exceed the maximum length.
2887 if options.get("error_title") and len(options["error_title"]) > 32:
2888 warn(
2889 f"Length of error title '{options['error_title']}' "
2890 f"exceeds Excel's limit of 32"
2891 )
2892 return -2
2894 # Check that the input message doesn't exceed the maximum length.
2895 if options.get("input_message") and len(options["input_message"]) > 255:
2896 warn(
2897 f"Length of input message '{options['input_message']}' "
2898 f"exceeds Excel's limit of 255"
2899 )
2900 return -2
2902 # Check that the error message doesn't exceed the maximum length.
2903 if options.get("error_message") and len(options["error_message"]) > 255:
2904 warn(
2905 f"Length of error message '{options['error_message']}' "
2906 f"exceeds Excel's limit of 255"
2907 )
2908 return -2
2910 # Check that the input list doesn't exceed the maximum length.
2911 if options["validate"] == "list" and isinstance(options["value"], list):
2912 formula = self._csv_join(*options["value"])
2913 if len(formula) > 255:
2914 warn(
2915 f"Length of list items '{formula}' exceeds Excel's limit of "
2916 f"255, use a formula range instead"
2917 )
2918 return -2
2920 # Set some defaults if they haven't been defined by the user.
2921 if "ignore_blank" not in options:
2922 options["ignore_blank"] = 1
2923 if "dropdown" not in options:
2924 options["dropdown"] = 1
2925 if "show_input" not in options:
2926 options["show_input"] = 1
2927 if "show_error" not in options:
2928 options["show_error"] = 1
2930 # These are the cells to which the validation is applied.
2931 options["cells"] = [[first_row, first_col, last_row, last_col]]
2933 # A (for now) undocumented parameter to pass additional cell ranges.
2934 if "other_cells" in options:
2935 options["cells"].extend(options["other_cells"])
2937 # Override with user defined multiple range if provided.
2938 if "multi_range" in options:
2939 options["multi_range"] = options["multi_range"].replace("$", "")
2941 # Store the validation information until we close the worksheet.
2942 self.validations.append(options)
2944 return 0
2946 @convert_range_args
2947 def conditional_format(
2948 self,
2949 first_row: int,
2950 first_col: int,
2951 last_row: int,
2952 last_col: int,
2953 options: Optional[Dict[str, Any]] = None,
2954 ) -> Literal[0, -1, -2]:
2955 """
2956 Add a conditional format to a worksheet.
2958 Args:
2959 first_row: The first row of the cell range. (zero indexed).
2960 first_col: The first column of the cell range.
2961 last_row: The last row of the cell range. (zero indexed).
2962 last_col: The last column of the cell range.
2963 options: Conditional format options.
2965 Returns:
2966 0: Success.
2967 -1: Row or column is out of worksheet bounds.
2968 -2: Incorrect parameter or option.
2969 """
2970 # Check that row and col are valid without storing the values.
2971 if self._check_dimensions(first_row, first_col, True, True):
2972 return -1
2973 if self._check_dimensions(last_row, last_col, True, True):
2974 return -1
2976 if options is None:
2977 options = {}
2978 else:
2979 # Copy the user defined options so they aren't modified.
2980 options = options.copy()
2982 # Valid input parameters.
2983 valid_parameter = {
2984 "type",
2985 "format",
2986 "criteria",
2987 "value",
2988 "minimum",
2989 "maximum",
2990 "stop_if_true",
2991 "min_type",
2992 "mid_type",
2993 "max_type",
2994 "min_value",
2995 "mid_value",
2996 "max_value",
2997 "min_color",
2998 "mid_color",
2999 "max_color",
3000 "min_length",
3001 "max_length",
3002 "multi_range",
3003 "bar_color",
3004 "bar_negative_color",
3005 "bar_negative_color_same",
3006 "bar_solid",
3007 "bar_border_color",
3008 "bar_negative_border_color",
3009 "bar_negative_border_color_same",
3010 "bar_no_border",
3011 "bar_direction",
3012 "bar_axis_position",
3013 "bar_axis_color",
3014 "bar_only",
3015 "data_bar_2010",
3016 "icon_style",
3017 "reverse_icons",
3018 "icons_only",
3019 "icons",
3020 }
3022 # Check for valid input parameters.
3023 for param_key in options.keys():
3024 if param_key not in valid_parameter:
3025 warn(f"Unknown parameter '{param_key}' in conditional_format()")
3026 return -2
3028 # 'type' is a required parameter.
3029 if "type" not in options:
3030 warn("Parameter 'type' is required in conditional_format()")
3031 return -2
3033 # Valid types.
3034 valid_type = {
3035 "cell": "cellIs",
3036 "date": "date",
3037 "time": "time",
3038 "average": "aboveAverage",
3039 "duplicate": "duplicateValues",
3040 "unique": "uniqueValues",
3041 "top": "top10",
3042 "bottom": "top10",
3043 "text": "text",
3044 "time_period": "timePeriod",
3045 "blanks": "containsBlanks",
3046 "no_blanks": "notContainsBlanks",
3047 "errors": "containsErrors",
3048 "no_errors": "notContainsErrors",
3049 "2_color_scale": "2_color_scale",
3050 "3_color_scale": "3_color_scale",
3051 "data_bar": "dataBar",
3052 "formula": "expression",
3053 "icon_set": "iconSet",
3054 }
3056 # Check for valid types.
3057 if options["type"] not in valid_type:
3058 warn(
3059 f"Unknown value '{options['type']}' for parameter 'type' "
3060 f"in conditional_format()"
3061 )
3062 return -2
3064 if options["type"] == "bottom":
3065 options["direction"] = "bottom"
3066 options["type"] = valid_type[options["type"]]
3068 # Valid criteria types.
3069 criteria_type = {
3070 "between": "between",
3071 "not between": "notBetween",
3072 "equal to": "equal",
3073 "=": "equal",
3074 "==": "equal",
3075 "not equal to": "notEqual",
3076 "!=": "notEqual",
3077 "<>": "notEqual",
3078 "greater than": "greaterThan",
3079 ">": "greaterThan",
3080 "less than": "lessThan",
3081 "<": "lessThan",
3082 "greater than or equal to": "greaterThanOrEqual",
3083 ">=": "greaterThanOrEqual",
3084 "less than or equal to": "lessThanOrEqual",
3085 "<=": "lessThanOrEqual",
3086 "containing": "containsText",
3087 "not containing": "notContains",
3088 "begins with": "beginsWith",
3089 "ends with": "endsWith",
3090 "yesterday": "yesterday",
3091 "today": "today",
3092 "last 7 days": "last7Days",
3093 "last week": "lastWeek",
3094 "this week": "thisWeek",
3095 "next week": "nextWeek",
3096 "last month": "lastMonth",
3097 "this month": "thisMonth",
3098 "next month": "nextMonth",
3099 # For legacy, but incorrect, support.
3100 "continue week": "nextWeek",
3101 "continue month": "nextMonth",
3102 }
3104 # Check for valid criteria types.
3105 if "criteria" in options and options["criteria"] in criteria_type:
3106 options["criteria"] = criteria_type[options["criteria"]]
3108 # Convert boolean values if required.
3109 if "value" in options and isinstance(options["value"], bool):
3110 options["value"] = str(options["value"]).upper()
3112 # Convert date/times value if required.
3113 if options["type"] in ("date", "time"):
3114 options["type"] = "cellIs"
3116 if "value" in options:
3117 if not _supported_datetime(options["value"]):
3118 warn("Conditional format 'value' must be a datetime object.")
3119 return -2
3121 date_time = self._convert_date_time(options["value"])
3122 # Format date number to the same precision as Excel.
3123 options["value"] = f"{date_time:.16g}"
3125 if "minimum" in options:
3126 if not _supported_datetime(options["minimum"]):
3127 warn("Conditional format 'minimum' must be a datetime object.")
3128 return -2
3130 date_time = self._convert_date_time(options["minimum"])
3131 options["minimum"] = f"{date_time:.16g}"
3133 if "maximum" in options:
3134 if not _supported_datetime(options["maximum"]):
3135 warn("Conditional format 'maximum' must be a datetime object.")
3136 return -2
3138 date_time = self._convert_date_time(options["maximum"])
3139 options["maximum"] = f"{date_time:.16g}"
3141 # Valid icon styles.
3142 valid_icons = {
3143 "3_arrows": "3Arrows", # 1
3144 "3_flags": "3Flags", # 2
3145 "3_traffic_lights_rimmed": "3TrafficLights2", # 3
3146 "3_symbols_circled": "3Symbols", # 4
3147 "4_arrows": "4Arrows", # 5
3148 "4_red_to_black": "4RedToBlack", # 6
3149 "4_traffic_lights": "4TrafficLights", # 7
3150 "5_arrows_gray": "5ArrowsGray", # 8
3151 "5_quarters": "5Quarters", # 9
3152 "3_arrows_gray": "3ArrowsGray", # 10
3153 "3_traffic_lights": "3TrafficLights", # 11
3154 "3_signs": "3Signs", # 12
3155 "3_symbols": "3Symbols2", # 13
3156 "4_arrows_gray": "4ArrowsGray", # 14
3157 "4_ratings": "4Rating", # 15
3158 "5_arrows": "5Arrows", # 16
3159 "5_ratings": "5Rating",
3160 } # 17
3162 # Set the icon set properties.
3163 if options["type"] == "iconSet":
3164 # An icon_set must have an icon style.
3165 if not options.get("icon_style"):
3166 warn(
3167 "The 'icon_style' parameter must be specified when "
3168 "'type' == 'icon_set' in conditional_format()."
3169 )
3170 return -3
3172 # Check for valid icon styles.
3173 if options["icon_style"] not in valid_icons:
3174 warn(
3175 f"Unknown icon_style '{options['icon_style']}' "
3176 f"in conditional_format()."
3177 )
3178 return -2
3180 options["icon_style"] = valid_icons[options["icon_style"]]
3182 # Set the number of icons for the icon style.
3183 options["total_icons"] = 3
3184 if options["icon_style"].startswith("4"):
3185 options["total_icons"] = 4
3186 elif options["icon_style"].startswith("5"):
3187 options["total_icons"] = 5
3189 options["icons"] = self._set_icon_props(
3190 options.get("total_icons"), options.get("icons")
3191 )
3193 # Swap last row/col for first row/col as necessary
3194 if first_row > last_row:
3195 first_row, last_row = last_row, first_row
3197 if first_col > last_col:
3198 first_col, last_col = last_col, first_col
3200 # Set the formatting range.
3201 cell_range = xl_range(first_row, first_col, last_row, last_col)
3202 start_cell = xl_rowcol_to_cell(first_row, first_col)
3204 # Override with user defined multiple range if provided.
3205 if "multi_range" in options:
3206 cell_range = options["multi_range"]
3207 cell_range = cell_range.replace("$", "")
3209 # Get the dxf format index.
3210 if "format" in options and options["format"]:
3211 options["format"] = options["format"]._get_dxf_index()
3213 # Set the priority based on the order of adding.
3214 options["priority"] = self.dxf_priority
3215 self.dxf_priority += 1
3217 # Check for 2010 style data_bar parameters.
3218 # pylint: disable=too-many-boolean-expressions
3219 if (
3220 self.use_data_bars_2010
3221 or options.get("data_bar_2010")
3222 or options.get("bar_solid")
3223 or options.get("bar_border_color")
3224 or options.get("bar_negative_color")
3225 or options.get("bar_negative_color_same")
3226 or options.get("bar_negative_border_color")
3227 or options.get("bar_negative_border_color_same")
3228 or options.get("bar_no_border")
3229 or options.get("bar_axis_position")
3230 or options.get("bar_axis_color")
3231 or options.get("bar_direction")
3232 ):
3233 options["is_data_bar_2010"] = True
3235 # Special handling of text criteria.
3236 if options["type"] == "text":
3237 value = options["value"]
3238 length = len(value)
3239 criteria = options["criteria"]
3241 if options["criteria"] == "containsText":
3242 options["type"] = "containsText"
3243 options["formula"] = f'NOT(ISERROR(SEARCH("{value}",{start_cell})))'
3244 elif options["criteria"] == "notContains":
3245 options["type"] = "notContainsText"
3246 options["formula"] = f'ISERROR(SEARCH("{value}",{start_cell}))'
3247 elif options["criteria"] == "beginsWith":
3248 options["type"] = "beginsWith"
3249 options["formula"] = f'LEFT({start_cell},{length})="{value}"'
3250 elif options["criteria"] == "endsWith":
3251 options["type"] = "endsWith"
3252 options["formula"] = f'RIGHT({start_cell},{length})="{value}"'
3253 else:
3254 warn(f"Invalid text criteria '{criteria}' in conditional_format()")
3256 # Special handling of time time_period criteria.
3257 if options["type"] == "timePeriod":
3258 if options["criteria"] == "yesterday":
3259 options["formula"] = f"FLOOR({start_cell},1)=TODAY()-1"
3261 elif options["criteria"] == "today":
3262 options["formula"] = f"FLOOR({start_cell},1)=TODAY()"
3264 elif options["criteria"] == "tomorrow":
3265 options["formula"] = f"FLOOR({start_cell},1)=TODAY()+1"
3267 # fmt: off
3268 elif options["criteria"] == "last7Days":
3269 options["formula"] = (
3270 f"AND(TODAY()-FLOOR({start_cell},1)<=6,"
3271 f"FLOOR({start_cell},1)<=TODAY())"
3272 )
3273 # fmt: on
3275 elif options["criteria"] == "lastWeek":
3276 options["formula"] = (
3277 f"AND(TODAY()-ROUNDDOWN({start_cell},0)>=(WEEKDAY(TODAY())),"
3278 f"TODAY()-ROUNDDOWN({start_cell},0)<(WEEKDAY(TODAY())+7))"
3279 )
3281 elif options["criteria"] == "thisWeek":
3282 options["formula"] = (
3283 f"AND(TODAY()-ROUNDDOWN({start_cell},0)<=WEEKDAY(TODAY())-1,"
3284 f"ROUNDDOWN({start_cell},0)-TODAY()<=7-WEEKDAY(TODAY()))"
3285 )
3287 elif options["criteria"] == "nextWeek":
3288 options["formula"] = (
3289 f"AND(ROUNDDOWN({start_cell},0)-TODAY()>(7-WEEKDAY(TODAY())),"
3290 f"ROUNDDOWN({start_cell},0)-TODAY()<(15-WEEKDAY(TODAY())))"
3291 )
3293 elif options["criteria"] == "lastMonth":
3294 options["formula"] = (
3295 f"AND(MONTH({start_cell})=MONTH(TODAY())-1,"
3296 f"OR(YEAR({start_cell})=YEAR("
3297 f"TODAY()),AND(MONTH({start_cell})=1,YEAR(A1)=YEAR(TODAY())-1)))"
3298 )
3300 # fmt: off
3301 elif options["criteria"] == "thisMonth":
3302 options["formula"] = (
3303 f"AND(MONTH({start_cell})=MONTH(TODAY()),"
3304 f"YEAR({start_cell})=YEAR(TODAY()))"
3305 )
3306 # fmt: on
3308 elif options["criteria"] == "nextMonth":
3309 options["formula"] = (
3310 f"AND(MONTH({start_cell})=MONTH(TODAY())+1,"
3311 f"OR(YEAR({start_cell})=YEAR("
3312 f"TODAY()),AND(MONTH({start_cell})=12,"
3313 f"YEAR({start_cell})=YEAR(TODAY())+1)))"
3314 )
3316 else:
3317 warn(
3318 f"Invalid time_period criteria '{options['criteria']}' "
3319 f"in conditional_format()"
3320 )
3322 # Special handling of blanks/error types.
3323 if options["type"] == "containsBlanks":
3324 options["formula"] = f"LEN(TRIM({start_cell}))=0"
3326 if options["type"] == "notContainsBlanks":
3327 options["formula"] = f"LEN(TRIM({start_cell}))>0"
3329 if options["type"] == "containsErrors":
3330 options["formula"] = f"ISERROR({start_cell})"
3332 if options["type"] == "notContainsErrors":
3333 options["formula"] = f"NOT(ISERROR({start_cell}))"
3335 # Special handling for 2 color scale.
3336 if options["type"] == "2_color_scale":
3337 options["type"] = "colorScale"
3339 # Color scales don't use any additional formatting.
3340 options["format"] = None
3342 # Turn off 3 color parameters.
3343 options["mid_type"] = None
3344 options["mid_color"] = None
3346 options.setdefault("min_type", "min")
3347 options.setdefault("max_type", "max")
3348 options.setdefault("min_value", 0)
3349 options.setdefault("max_value", 0)
3350 options.setdefault("min_color", Color("#FF7128"))
3351 options.setdefault("max_color", Color("#FFEF9C"))
3353 options["min_color"] = Color._from_value(options["min_color"])
3354 options["max_color"] = Color._from_value(options["max_color"])
3356 # Special handling for 3 color scale.
3357 if options["type"] == "3_color_scale":
3358 options["type"] = "colorScale"
3360 # Color scales don't use any additional formatting.
3361 options["format"] = None
3363 options.setdefault("min_type", "min")
3364 options.setdefault("mid_type", "percentile")
3365 options.setdefault("max_type", "max")
3366 options.setdefault("min_value", 0)
3367 options.setdefault("max_value", 0)
3368 options.setdefault("min_color", Color("#F8696B"))
3369 options.setdefault("mid_color", Color("#FFEB84"))
3370 options.setdefault("max_color", Color("#63BE7B"))
3372 options["min_color"] = Color._from_value(options["min_color"])
3373 options["mid_color"] = Color._from_value(options["mid_color"])
3374 options["max_color"] = Color._from_value(options["max_color"])
3376 # Set a default mid value.
3377 if "mid_value" not in options:
3378 options["mid_value"] = 50
3380 # Special handling for data bar.
3381 if options["type"] == "dataBar":
3382 # Color scales don't use any additional formatting.
3383 options["format"] = None
3385 if not options.get("min_type"):
3386 options["min_type"] = "min"
3387 options["x14_min_type"] = "autoMin"
3388 else:
3389 options["x14_min_type"] = options["min_type"]
3391 if not options.get("max_type"):
3392 options["max_type"] = "max"
3393 options["x14_max_type"] = "autoMax"
3394 else:
3395 options["x14_max_type"] = options["max_type"]
3397 options.setdefault("min_value", 0)
3398 options.setdefault("max_value", 0)
3399 options.setdefault("bar_color", Color("#638EC6"))
3400 options.setdefault("bar_border_color", options["bar_color"])
3401 options.setdefault("bar_only", False)
3402 options.setdefault("bar_no_border", False)
3403 options.setdefault("bar_solid", False)
3404 options.setdefault("bar_direction", "")
3405 options.setdefault("bar_negative_color", Color("#FF0000"))
3406 options.setdefault("bar_negative_border_color", Color("#FF0000"))
3407 options.setdefault("bar_negative_color_same", False)
3408 options.setdefault("bar_negative_border_color_same", False)
3409 options.setdefault("bar_axis_position", "")
3410 options.setdefault("bar_axis_color", Color("#000000"))
3412 options["bar_color"] = Color._from_value(options["bar_color"])
3413 options["bar_border_color"] = Color._from_value(options["bar_border_color"])
3414 options["bar_axis_color"] = Color._from_value(options["bar_axis_color"])
3415 options["bar_negative_color"] = Color._from_value(
3416 options["bar_negative_color"]
3417 )
3418 options["bar_negative_border_color"] = Color._from_value(
3419 options["bar_negative_border_color"]
3420 )
3422 # Adjust for 2010 style data_bar parameters.
3423 if options.get("is_data_bar_2010"):
3424 self.excel_version = 2010
3426 if options["min_type"] == "min" and options["min_value"] == 0:
3427 options["min_value"] = None
3429 if options["max_type"] == "max" and options["max_value"] == 0:
3430 options["max_value"] = None
3432 options["range"] = cell_range
3434 # Strip the leading = from formulas.
3435 try:
3436 options["min_value"] = options["min_value"].lstrip("=")
3437 except (KeyError, AttributeError):
3438 pass
3439 try:
3440 options["mid_value"] = options["mid_value"].lstrip("=")
3441 except (KeyError, AttributeError):
3442 pass
3443 try:
3444 options["max_value"] = options["max_value"].lstrip("=")
3445 except (KeyError, AttributeError):
3446 pass
3448 # Store the conditional format until we close the worksheet.
3449 if cell_range in self.cond_formats:
3450 self.cond_formats[cell_range].append(options)
3451 else:
3452 self.cond_formats[cell_range] = [options]
3454 return 0
3456 @convert_range_args
3457 def add_table(
3458 self,
3459 first_row: int,
3460 first_col: int,
3461 last_row: int,
3462 last_col: int,
3463 options: Optional[Dict[str, Any]] = None,
3464 ) -> Literal[0, -1, -2, -3]:
3465 """
3466 Add an Excel table to a worksheet.
3468 Args:
3469 first_row: The first row of the cell range. (zero indexed).
3470 first_col: The first column of the cell range.
3471 last_row: The last row of the cell range. (zero indexed).
3472 last_col: The last column of the cell range.
3473 options: Table format options. (Optional)
3475 Returns:
3476 0: Success.
3477 -1: Row or column is out of worksheet bounds.
3478 -2: Incorrect parameter or option.
3479 -3: Not supported in constant_memory mode.
3480 """
3481 table = {}
3482 col_formats = {}
3484 if options is None:
3485 options = {}
3486 else:
3487 # Copy the user defined options so they aren't modified.
3488 options = options.copy()
3490 if self.constant_memory:
3491 warn("add_table() isn't supported in 'constant_memory' mode")
3492 return -3
3494 # Check that row and col are valid without storing the values.
3495 if self._check_dimensions(first_row, first_col, True, True):
3496 return -1
3497 if self._check_dimensions(last_row, last_col, True, True):
3498 return -1
3500 # Swap last row/col for first row/col as necessary.
3501 if first_row > last_row:
3502 (first_row, last_row) = (last_row, first_row)
3503 if first_col > last_col:
3504 (first_col, last_col) = (last_col, first_col)
3506 # Check if the table range overlaps a previous merged or table range.
3507 # This is a critical file corruption error in Excel.
3508 cell_range = xl_range(first_row, first_col, last_row, last_col)
3509 for row in range(first_row, last_row + 1):
3510 for col in range(first_col, last_col + 1):
3511 if self.table_cells.get((row, col)):
3512 previous_range = self.table_cells.get((row, col))
3513 raise OverlappingRange(
3514 f"Table range '{cell_range}' overlaps previous "
3515 f"table range '{previous_range}'."
3516 )
3518 if self.merged_cells.get((row, col)):
3519 previous_range = self.merged_cells.get((row, col))
3520 raise OverlappingRange(
3521 f"Table range '{cell_range}' overlaps previous "
3522 f"merge range '{previous_range}'."
3523 )
3525 self.table_cells[(row, col)] = cell_range
3527 # Valid input parameters.
3528 valid_parameter = {
3529 "autofilter",
3530 "banded_columns",
3531 "banded_rows",
3532 "columns",
3533 "data",
3534 "first_column",
3535 "header_row",
3536 "last_column",
3537 "name",
3538 "style",
3539 "total_row",
3540 "description",
3541 "title",
3542 }
3544 # Check for valid input parameters.
3545 for param_key in options.keys():
3546 if param_key not in valid_parameter:
3547 warn(f"Unknown parameter '{param_key}' in add_table()")
3548 return -2
3550 # Turn on Excel's defaults.
3551 options["banded_rows"] = options.get("banded_rows", True)
3552 options["header_row"] = options.get("header_row", True)
3553 options["autofilter"] = options.get("autofilter", True)
3555 # Check that there are enough rows.
3556 num_rows = last_row - first_row
3557 if options["header_row"]:
3558 num_rows -= 1
3560 if num_rows < 0:
3561 warn("Must have at least one data row in in add_table()")
3562 return -2
3564 # Set the table options.
3565 table["show_first_col"] = options.get("first_column", False)
3566 table["show_last_col"] = options.get("last_column", False)
3567 table["show_row_stripes"] = options.get("banded_rows", False)
3568 table["show_col_stripes"] = options.get("banded_columns", False)
3569 table["header_row_count"] = options.get("header_row", 0)
3570 table["totals_row_shown"] = options.get("total_row", False)
3571 table["description"] = options.get("description")
3572 table["title"] = options.get("title")
3574 # Set the table name.
3575 if "name" in options:
3576 name = options["name"]
3577 table["name"] = name
3579 if " " in name:
3580 warn(f"Name '{name}' in add_table() cannot contain spaces")
3581 return -2
3583 # Warn if the name contains invalid chars as defined by Excel.
3584 if not re.match(r"^[\w\\][\w\\.]*$", name, re.UNICODE) or re.match(
3585 r"^\d", name
3586 ):
3587 warn(f"Invalid Excel characters in add_table(): '{name}'")
3588 return -2
3590 # Warn if the name looks like a cell name.
3591 if re.match(r"^[a-zA-Z][a-zA-Z]?[a-dA-D]?\d+$", name):
3592 warn(f"Name looks like a cell name in add_table(): '{name}'")
3593 return -2
3595 # Warn if the name looks like a R1C1 cell reference.
3596 if re.match(r"^[rcRC]$", name) or re.match(r"^[rcRC]\d+[rcRC]\d+$", name):
3597 warn(f"Invalid name '{name}' like a RC cell ref in add_table()")
3598 return -2
3600 # Set the table style.
3601 if "style" in options:
3602 table["style"] = options["style"]
3604 if table["style"] is None:
3605 table["style"] = ""
3607 # Remove whitespace from style name.
3608 table["style"] = table["style"].replace(" ", "")
3609 else:
3610 table["style"] = "TableStyleMedium9"
3612 # Set the data range rows (without the header and footer).
3613 first_data_row = first_row
3614 last_data_row = last_row
3616 if options.get("header_row"):
3617 first_data_row += 1
3619 if options.get("total_row"):
3620 last_data_row -= 1
3622 # Set the table and autofilter ranges.
3623 table["range"] = xl_range(first_row, first_col, last_row, last_col)
3625 table["a_range"] = xl_range(first_row, first_col, last_data_row, last_col)
3627 # If the header row if off the default is to turn autofilter off.
3628 if not options["header_row"]:
3629 options["autofilter"] = 0
3631 # Set the autofilter range.
3632 if options["autofilter"]:
3633 table["autofilter"] = table["a_range"]
3635 # Add the table columns.
3636 col_id = 1
3637 table["columns"] = []
3638 seen_names = {}
3640 for col_num in range(first_col, last_col + 1):
3641 # Set up the default column data.
3642 col_data = {
3643 "id": col_id,
3644 "name": "Column" + str(col_id),
3645 "total_string": "",
3646 "total_function": "",
3647 "custom_total": "",
3648 "total_value": 0,
3649 "formula": "",
3650 "format": None,
3651 "name_format": None,
3652 }
3654 # Overwrite the defaults with any user defined values.
3655 if "columns" in options:
3656 # Check if there are user defined values for this column.
3657 if col_id <= len(options["columns"]):
3658 user_data = options["columns"][col_id - 1]
3659 else:
3660 user_data = None
3662 if user_data:
3663 # Get the column format.
3664 xformat = user_data.get("format", None)
3666 # Map user defined values to internal values.
3667 if user_data.get("header"):
3668 col_data["name"] = user_data["header"]
3670 # Excel requires unique case insensitive header names.
3671 header_name = col_data["name"]
3672 name = header_name.lower()
3673 if name in seen_names:
3674 warn(f"Duplicate header name in add_table(): '{name}'")
3675 return -2
3677 seen_names[name] = True
3679 col_data["name_format"] = user_data.get("header_format")
3681 # Handle the column formula.
3682 if "formula" in user_data and user_data["formula"]:
3683 formula = user_data["formula"]
3685 # Remove the formula '=' sign if it exists.
3686 if formula.startswith("="):
3687 formula = formula.lstrip("=")
3689 # Convert Excel 2010 "@" ref to 2007 "#This Row".
3690 formula = self._prepare_table_formula(formula)
3692 # Escape any future functions.
3693 formula = self._prepare_formula(formula, True)
3695 col_data["formula"] = formula
3696 # We write the formulas below after the table data.
3698 # Handle the function for the total row.
3699 if user_data.get("total_function"):
3700 function = user_data["total_function"]
3701 if function == "count_nums":
3702 function = "countNums"
3703 if function == "std_dev":
3704 function = "stdDev"
3706 subtotals = set(
3707 [
3708 "average",
3709 "countNums",
3710 "count",
3711 "max",
3712 "min",
3713 "stdDev",
3714 "sum",
3715 "var",
3716 ]
3717 )
3719 if function in subtotals:
3720 formula = self._table_function_to_formula(
3721 function, col_data["name"]
3722 )
3723 else:
3724 formula = self._prepare_formula(function, True)
3725 col_data["custom_total"] = formula
3726 function = "custom"
3728 col_data["total_function"] = function
3730 value = user_data.get("total_value", 0)
3732 self._write_formula(last_row, col_num, formula, xformat, value)
3734 elif user_data.get("total_string"):
3735 # Total label only (not a function).
3736 total_string = user_data["total_string"]
3737 col_data["total_string"] = total_string
3739 self._write_string(
3740 last_row, col_num, total_string, user_data.get("format")
3741 )
3743 # Get the dxf format index.
3744 if xformat is not None:
3745 col_data["format"] = xformat._get_dxf_index()
3747 # Store the column format for writing the cell data.
3748 # It doesn't matter if it is undefined.
3749 col_formats[col_id - 1] = xformat
3751 # Store the column data.
3752 table["columns"].append(col_data)
3754 # Write the column headers to the worksheet.
3755 if options["header_row"]:
3756 self._write_string(
3757 first_row, col_num, col_data["name"], col_data["name_format"]
3758 )
3760 col_id += 1
3762 # Write the cell data if supplied.
3763 if "data" in options:
3764 data = options["data"]
3766 i = 0 # For indexing the row data.
3767 for row in range(first_data_row, last_data_row + 1):
3768 j = 0 # For indexing the col data.
3769 for col in range(first_col, last_col + 1):
3770 if i < len(data) and j < len(data[i]):
3771 token = data[i][j]
3772 if j in col_formats:
3773 self._write(row, col, token, col_formats[j])
3774 else:
3775 self._write(row, col, token, None)
3776 j += 1
3777 i += 1
3779 # Write any columns formulas after the user supplied table data to
3780 # overwrite it if required.
3781 for col_id, col_num in enumerate(range(first_col, last_col + 1)):
3782 column_data = table["columns"][col_id]
3783 if column_data and column_data["formula"]:
3784 formula_format = col_formats.get(col_id)
3785 formula = column_data["formula"]
3787 for row in range(first_data_row, last_data_row + 1):
3788 self._write_formula(row, col_num, formula, formula_format)
3790 # Store the table data.
3791 self.tables.append(table)
3793 # Store the filter cell positions for use in the autofit calculation.
3794 if options["autofilter"]:
3795 for col in range(first_col, last_col + 1):
3796 # Check that the table autofilter doesn't overlap a worksheet filter.
3797 if self.filter_cells.get((first_row, col)):
3798 filter_type, filter_range = self.filter_cells.get((first_row, col))
3799 if filter_type == "worksheet":
3800 raise OverlappingRange(
3801 f"Table autofilter range '{cell_range}' overlaps previous "
3802 f"Worksheet autofilter range '{filter_range}'."
3803 )
3805 self.filter_cells[(first_row, col)] = ("table", cell_range)
3807 return 0
3809 @convert_cell_args
3810 def add_sparkline(
3811 self, row: int, col: int, options: Optional[Dict[str, Any]] = None
3812 ) -> Literal[0, -1, -2]:
3813 """
3814 Add sparklines to the worksheet.
3816 Args:
3817 row: The cell row (zero indexed).
3818 col: The cell column (zero indexed).
3819 options: Sparkline formatting options.
3821 Returns:
3822 0: Success.
3823 -1: Row or column is out of worksheet bounds.
3824 -2: Incorrect parameter or option.
3826 """
3828 # Check that row and col are valid without storing the values.
3829 if self._check_dimensions(row, col, True, True):
3830 return -1
3832 sparkline = {"locations": [xl_rowcol_to_cell(row, col)]}
3834 if options is None:
3835 options = {}
3837 # Valid input parameters.
3838 valid_parameters = {
3839 "location",
3840 "range",
3841 "type",
3842 "high_point",
3843 "low_point",
3844 "negative_points",
3845 "first_point",
3846 "last_point",
3847 "markers",
3848 "style",
3849 "series_color",
3850 "negative_color",
3851 "markers_color",
3852 "first_color",
3853 "last_color",
3854 "high_color",
3855 "low_color",
3856 "max",
3857 "min",
3858 "axis",
3859 "reverse",
3860 "empty_cells",
3861 "show_hidden",
3862 "plot_hidden",
3863 "date_axis",
3864 "weight",
3865 }
3867 # Check for valid input parameters.
3868 for param_key in options.keys():
3869 if param_key not in valid_parameters:
3870 warn(f"Unknown parameter '{param_key}' in add_sparkline()")
3871 return -1
3873 # 'range' is a required parameter.
3874 if "range" not in options:
3875 warn("Parameter 'range' is required in add_sparkline()")
3876 return -2
3878 # Handle the sparkline type.
3879 spark_type = options.get("type", "line")
3881 if spark_type not in ("line", "column", "win_loss"):
3882 warn(
3883 "Parameter 'type' must be 'line', 'column' "
3884 "or 'win_loss' in add_sparkline()"
3885 )
3886 return -2
3888 if spark_type == "win_loss":
3889 spark_type = "stacked"
3890 sparkline["type"] = spark_type
3892 # We handle single location/range values or list of values.
3893 if "location" in options:
3894 if isinstance(options["location"], list):
3895 sparkline["locations"] = options["location"]
3896 else:
3897 sparkline["locations"] = [options["location"]]
3899 if isinstance(options["range"], list):
3900 sparkline["ranges"] = options["range"]
3901 else:
3902 sparkline["ranges"] = [options["range"]]
3904 range_count = len(sparkline["ranges"])
3905 location_count = len(sparkline["locations"])
3907 # The ranges and locations must match.
3908 if range_count != location_count:
3909 warn(
3910 "Must have the same number of location and range "
3911 "parameters in add_sparkline()"
3912 )
3913 return -2
3915 # Store the count.
3916 sparkline["count"] = len(sparkline["locations"])
3918 # Get the worksheet name for the range conversion below.
3919 sheetname = quote_sheetname(self.name)
3921 # Cleanup the input ranges.
3922 new_ranges = []
3923 for spark_range in sparkline["ranges"]:
3924 # Remove the absolute reference $ symbols.
3925 spark_range = spark_range.replace("$", "")
3927 # Remove the = from formula.
3928 spark_range = spark_range.lstrip("=")
3930 # Convert a simple range into a full Sheet1!A1:D1 range.
3931 if "!" not in spark_range:
3932 spark_range = sheetname + "!" + spark_range
3934 new_ranges.append(spark_range)
3936 sparkline["ranges"] = new_ranges
3938 # Cleanup the input locations.
3939 new_locations = []
3940 for location in sparkline["locations"]:
3941 location = location.replace("$", "")
3942 new_locations.append(location)
3944 sparkline["locations"] = new_locations
3946 # Map options.
3947 sparkline["high"] = options.get("high_point")
3948 sparkline["low"] = options.get("low_point")
3949 sparkline["negative"] = options.get("negative_points")
3950 sparkline["first"] = options.get("first_point")
3951 sparkline["last"] = options.get("last_point")
3952 sparkline["markers"] = options.get("markers")
3953 sparkline["min"] = options.get("min")
3954 sparkline["max"] = options.get("max")
3955 sparkline["axis"] = options.get("axis")
3956 sparkline["reverse"] = options.get("reverse")
3957 sparkline["hidden"] = options.get("show_hidden")
3958 sparkline["weight"] = options.get("weight")
3960 # Map empty cells options.
3961 empty = options.get("empty_cells", "")
3963 if empty == "zero":
3964 sparkline["empty"] = 0
3965 elif empty == "connect":
3966 sparkline["empty"] = "span"
3967 else:
3968 sparkline["empty"] = "gap"
3970 # Map the date axis range.
3971 date_range = options.get("date_axis")
3973 if date_range and "!" not in date_range:
3974 date_range = sheetname + "!" + date_range
3976 sparkline["date_axis"] = date_range
3978 # Set the sparkline styles.
3979 style_id = options.get("style", 0)
3980 style = _get_sparkline_style(style_id)
3982 sparkline["series_color"] = style["series"]
3983 sparkline["negative_color"] = style["negative"]
3984 sparkline["markers_color"] = style["markers"]
3985 sparkline["first_color"] = style["first"]
3986 sparkline["last_color"] = style["last"]
3987 sparkline["high_color"] = style["high"]
3988 sparkline["low_color"] = style["low"]
3990 # Override the style colors with user defined colors.
3991 self._set_spark_color(sparkline, options, "series_color")
3992 self._set_spark_color(sparkline, options, "negative_color")
3993 self._set_spark_color(sparkline, options, "markers_color")
3994 self._set_spark_color(sparkline, options, "first_color")
3995 self._set_spark_color(sparkline, options, "last_color")
3996 self._set_spark_color(sparkline, options, "high_color")
3997 self._set_spark_color(sparkline, options, "low_color")
3999 self.sparklines.append(sparkline)
4001 return 0
4003 @convert_range_args
4004 def set_selection(
4005 self, first_row: int, first_col: int, last_row: int, last_col: int
4006 ) -> None:
4007 """
4008 Set the selected cell or cells in a worksheet
4010 Args:
4011 first_row: The first row of the cell range. (zero indexed).
4012 first_col: The first column of the cell range.
4013 last_row: The last row of the cell range. (zero indexed).
4014 last_col: The last column of the cell range.
4016 Returns:
4017 0: Nothing.
4018 """
4019 pane = None
4021 # Range selection. Do this before swapping max/min to allow the
4022 # selection direction to be reversed.
4023 active_cell = xl_rowcol_to_cell(first_row, first_col)
4025 # Swap last row/col for first row/col if necessary
4026 if first_row > last_row:
4027 (first_row, last_row) = (last_row, first_row)
4029 if first_col > last_col:
4030 (first_col, last_col) = (last_col, first_col)
4032 sqref = xl_range(first_row, first_col, last_row, last_col)
4034 # Selection isn't set for cell A1.
4035 if sqref == "A1":
4036 return
4038 self.selections = [[pane, active_cell, sqref]]
4040 @convert_cell_args
4041 def set_top_left_cell(self, row: int = 0, col: int = 0) -> None:
4042 """
4043 Set the first visible cell at the top left of a worksheet.
4045 Args:
4046 row: The cell row (zero indexed).
4047 col: The cell column (zero indexed).
4049 Returns:
4050 0: Nothing.
4051 """
4053 if row == 0 and col == 0:
4054 return
4056 self.top_left_cell = xl_rowcol_to_cell(row, col)
4058 def outline_settings(
4059 self,
4060 visible: bool = 1,
4061 symbols_below: bool = 1,
4062 symbols_right: bool = 1,
4063 auto_style: bool = 0,
4064 ) -> None:
4065 """
4066 Control outline settings.
4068 Args:
4069 visible: Outlines are visible. Optional, defaults to True.
4070 symbols_below: Show row outline symbols below the outline bar.
4071 Optional, defaults to True.
4072 symbols_right: Show column outline symbols to the right of the
4073 outline bar. Optional, defaults to True.
4074 auto_style: Use Automatic style. Optional, defaults to False.
4076 Returns:
4077 0: Nothing.
4078 """
4079 self.outline_on = visible
4080 self.outline_below = symbols_below
4081 self.outline_right = symbols_right
4082 self.outline_style = auto_style
4084 self.outline_changed = True
4086 @convert_cell_args
4087 def freeze_panes(
4088 self,
4089 row: int,
4090 col: int,
4091 top_row: Optional[int] = None,
4092 left_col: Optional[int] = None,
4093 pane_type: int = 0,
4094 ) -> None:
4095 """
4096 Create worksheet panes and mark them as frozen.
4098 Args:
4099 row: The cell row (zero indexed).
4100 col: The cell column (zero indexed).
4101 top_row: Topmost visible row in scrolling region of pane.
4102 left_col: Leftmost visible row in scrolling region of pane.
4104 Returns:
4105 0: Nothing.
4107 """
4108 if top_row is None:
4109 top_row = row
4111 if left_col is None:
4112 left_col = col
4114 self.panes = [row, col, top_row, left_col, pane_type]
4116 @convert_cell_args
4117 def split_panes(
4118 self,
4119 x: float,
4120 y: float,
4121 top_row: Optional[int] = None,
4122 left_col: Optional[int] = None,
4123 ) -> None:
4124 """
4125 Create worksheet panes and mark them as split.
4127 Args:
4128 x: The position for the vertical split.
4129 y: The position for the horizontal split.
4130 top_row: Topmost visible row in scrolling region of pane.
4131 left_col: Leftmost visible row in scrolling region of pane.
4133 Returns:
4134 0: Nothing.
4136 """
4137 # Same as freeze panes with a different pane type.
4138 self.freeze_panes(x, y, top_row, left_col, 2)
4140 def set_zoom(self, zoom: int = 100) -> None:
4141 """
4142 Set the worksheet zoom factor.
4144 Args:
4145 zoom: Scale factor: 10 <= zoom <= 400.
4147 Returns:
4148 Nothing.
4150 """
4151 # Ensure the zoom scale is in Excel's range.
4152 if zoom < 10 or zoom > 400:
4153 warn(f"Zoom factor '{zoom}' outside range: 10 <= zoom <= 400")
4154 zoom = 100
4156 self.zoom = int(zoom)
4158 def set_zoom_to_fit(self) -> None:
4159 """
4160 Set the worksheet zoom to selection/fit. Only works for chartsheets.
4162 Args:
4163 None.
4165 Returns:
4166 Nothing.
4168 """
4169 self.zoom_to_fit = True
4171 def right_to_left(self) -> None:
4172 """
4173 Display the worksheet right to left for some versions of Excel.
4175 Args:
4176 None.
4178 Returns:
4179 Nothing.
4181 """
4182 self.is_right_to_left = True
4184 def hide_zero(self) -> None:
4185 """
4186 Hide zero values in worksheet cells.
4188 Args:
4189 None.
4191 Returns:
4192 Nothing.
4194 """
4195 self.show_zeros = 0
4197 def set_tab_color(self, color: Union[str, Color]) -> None:
4198 """
4199 Set the color of the worksheet tab.
4201 Args:
4202 color: A #RGB color index.
4204 Returns:
4205 Nothing.
4207 """
4208 self.tab_color = Color._from_value(color)
4210 def protect(
4211 self, password: str = "", options: Optional[Dict[str, Any]] = None
4212 ) -> None:
4213 """
4214 Set the password and protection options of the worksheet.
4216 Args:
4217 password: An optional password string.
4218 options: A dictionary of worksheet objects to protect.
4220 Returns:
4221 Nothing.
4223 """
4224 if password != "":
4225 password = self._encode_password(password)
4227 if not options:
4228 options = {}
4230 # Default values for objects that can be protected.
4231 defaults = {
4232 "sheet": True,
4233 "content": False,
4234 "objects": False,
4235 "scenarios": False,
4236 "format_cells": False,
4237 "format_columns": False,
4238 "format_rows": False,
4239 "insert_columns": False,
4240 "insert_rows": False,
4241 "insert_hyperlinks": False,
4242 "delete_columns": False,
4243 "delete_rows": False,
4244 "select_locked_cells": True,
4245 "sort": False,
4246 "autofilter": False,
4247 "pivot_tables": False,
4248 "select_unlocked_cells": True,
4249 }
4251 # Overwrite the defaults with user specified values.
4252 for key in options.keys():
4253 if key in defaults:
4254 defaults[key] = options[key]
4255 else:
4256 warn(f"Unknown protection object: '{key}'")
4258 # Set the password after the user defined values.
4259 defaults["password"] = password
4261 self.protect_options = defaults
4263 def unprotect_range(
4264 self,
4265 cell_range: str,
4266 range_name: Optional[str] = None,
4267 password: Optional[str] = None,
4268 ) -> int:
4269 """
4270 Unprotect ranges within a protected worksheet.
4272 Args:
4273 cell_range: The cell or cell range to unprotect.
4274 range_name: An optional name for the range.
4275 password: An optional password string. (undocumented)
4277 Returns:
4278 0: Success.
4279 -1: Parameter error.
4281 """
4282 if cell_range is None:
4283 warn("Cell range must be specified in unprotect_range()")
4284 return -1
4286 # Sanitize the cell range.
4287 cell_range = cell_range.lstrip("=")
4288 cell_range = cell_range.replace("$", "")
4290 self.num_protected_ranges += 1
4292 if range_name is None:
4293 range_name = "Range" + str(self.num_protected_ranges)
4295 if password:
4296 password = self._encode_password(password)
4298 self.protected_ranges.append((cell_range, range_name, password))
4300 return 0
4302 @convert_cell_args
4303 def insert_button(
4304 self, row: int, col: int, options: Optional[Dict[str, Any]] = None
4305 ) -> Literal[0, -1]:
4306 """
4307 Insert a button form object into the worksheet.
4309 Args:
4310 row: The cell row (zero indexed).
4311 col: The cell column (zero indexed).
4312 options: Button formatting options.
4314 Returns:
4315 0: Success.
4316 -1: Row or column is out of worksheet bounds.
4318 """
4319 # Check insert (row, col) without storing.
4320 if self._check_dimensions(row, col, True, True):
4321 warn(f"Cannot insert button at ({row}, {col}).")
4322 return -1
4324 if options is None:
4325 options = {}
4327 # Create a new button object.
4328 height = self.default_row_height
4329 width = self.default_col_width
4330 button_number = 1 + len(self.buttons_list)
4332 button = ButtonType(row, col, height, width, button_number, options)
4334 self.buttons_list.append(button)
4336 self.has_vml = True
4338 return 0
4340 @convert_cell_args
4341 def insert_checkbox(
4342 self, row: int, col: int, boolean: bool, cell_format: Optional[Format] = None
4343 ):
4344 """
4345 Insert a boolean checkbox in a worksheet cell.
4347 Args:
4348 row: The cell row (zero indexed).
4349 col: The cell column (zero indexed).
4350 boolean: The boolean value to display as a checkbox.
4351 cell_format: Cell Format object. (optional)
4353 Returns:
4354 0: Success.
4355 -1: Row or column is out of worksheet bounds.
4357 """
4358 # Ensure that the checkbox property is set in the user defined format.
4359 if cell_format and not cell_format.checkbox:
4360 # This needs to be fixed with a clone.
4361 cell_format.set_checkbox()
4363 # If no format is supplied create and/or use the default checkbox format.
4364 if not cell_format:
4365 if not self.default_checkbox_format:
4366 self.default_checkbox_format = self.workbook_add_format()
4367 self.default_checkbox_format.set_checkbox()
4369 cell_format = self.default_checkbox_format
4371 return self._write_boolean(row, col, boolean, cell_format)
4373 ###########################################################################
4374 #
4375 # Public API. Page Setup methods.
4376 #
4377 ###########################################################################
4378 def set_landscape(self) -> None:
4379 """
4380 Set the page orientation as landscape.
4382 Args:
4383 None.
4385 Returns:
4386 Nothing.
4388 """
4389 self.orientation = 0
4390 self.page_setup_changed = True
4392 def set_portrait(self) -> None:
4393 """
4394 Set the page orientation as portrait.
4396 Args:
4397 None.
4399 Returns:
4400 Nothing.
4402 """
4403 self.orientation = 1
4404 self.page_setup_changed = True
4406 def set_page_view(self, view: Literal[0, 1, 2] = 1) -> None:
4407 """
4408 Set the page view mode.
4410 Args:
4411 0: Normal view mode
4412 1: Page view mode (the default)
4413 2: Page break view mode
4415 Returns:
4416 Nothing.
4418 """
4419 self.page_view = view
4421 def set_pagebreak_view(self) -> None:
4422 """
4423 Set the page view mode.
4425 Args:
4426 None.
4428 Returns:
4429 Nothing.
4431 """
4432 self.page_view = 2
4434 def set_paper(self, paper_size: Union[Literal[1, 9], int]) -> None:
4435 """
4436 Set the paper type. US Letter = 1, A4 = 9.
4438 Args:
4439 paper_size: Paper index.
4441 Returns:
4442 Nothing.
4444 """
4445 if paper_size:
4446 self.paper_size = paper_size
4447 self.page_setup_changed = True
4449 def center_horizontally(self) -> None:
4450 """
4451 Center the page horizontally.
4453 Args:
4454 None.
4456 Returns:
4457 Nothing.
4459 """
4460 self.print_options_changed = True
4461 self.hcenter = 1
4463 def center_vertically(self) -> None:
4464 """
4465 Center the page vertically.
4467 Args:
4468 None.
4470 Returns:
4471 Nothing.
4473 """
4474 self.print_options_changed = True
4475 self.vcenter = 1
4477 def set_margins(
4478 self,
4479 left: float = 0.7,
4480 right: float = 0.7,
4481 top: float = 0.75,
4482 bottom: float = 0.75,
4483 ) -> None:
4484 """
4485 Set all the page margins in inches.
4487 Args:
4488 left: Left margin.
4489 right: Right margin.
4490 top: Top margin.
4491 bottom: Bottom margin.
4493 Returns:
4494 Nothing.
4496 """
4497 self.margin_left = left
4498 self.margin_right = right
4499 self.margin_top = top
4500 self.margin_bottom = bottom
4502 def set_header(
4503 self, header: str = "", options: Optional[Dict[str, Any]] = None, margin=None
4504 ) -> None:
4505 """
4506 Set the page header caption and optional margin.
4508 Args:
4509 header: Header string.
4510 margin: Header margin.
4511 options: Header options, mainly for images.
4513 Returns:
4514 Nothing.
4516 """
4517 header_orig = header
4518 header = header.replace("&[Picture]", "&G")
4520 if len(header) > 255:
4521 warn("Header string cannot be longer than Excel's limit of 255 characters")
4522 return
4524 if options is not None:
4525 # For backward compatibility allow options to be the margin.
4526 if not isinstance(options, dict):
4527 options = {"margin": options}
4528 else:
4529 options = {}
4531 # Copy the user defined options so they aren't modified.
4532 options = options.copy()
4534 # For backward compatibility.
4535 if margin is not None:
4536 options["margin"] = margin
4538 # Reset the list in case the function is called more than once.
4539 self.header_images = []
4541 if options.get("image_left"):
4542 options["image_data"] = options.get("image_data_left")
4543 image = self._image_from_source(options.get("image_left"), options)
4544 image._header_position = "LH"
4545 self.header_images.append(image)
4547 if options.get("image_center"):
4548 options["image_data"] = options.get("image_data_center")
4549 image = self._image_from_source(options.get("image_center"), options)
4550 image._header_position = "CH"
4551 self.header_images.append(image)
4553 if options.get("image_right"):
4554 options["image_data"] = options.get("image_data_right")
4555 image = self._image_from_source(options.get("image_right"), options)
4556 image._header_position = "RH"
4557 self.header_images.append(image)
4559 placeholder_count = header.count("&G")
4560 image_count = len(self.header_images)
4562 if placeholder_count != image_count:
4563 warn(
4564 f"Number of footer images '{image_count}' doesn't match placeholder "
4565 f"count '{placeholder_count}' in string: {header_orig}"
4566 )
4567 self.header_images = []
4568 return
4570 if "align_with_margins" in options:
4571 self.header_footer_aligns = options["align_with_margins"]
4573 if "scale_with_doc" in options:
4574 self.header_footer_scales = options["scale_with_doc"]
4576 self.header = header
4577 self.margin_header = options.get("margin", 0.3)
4578 self.header_footer_changed = True
4580 if image_count:
4581 self.has_header_vml = True
4583 def set_footer(
4584 self, footer: str = "", options: Optional[Dict[str, Any]] = None, margin=None
4585 ) -> None:
4586 """
4587 Set the page footer caption and optional margin.
4589 Args:
4590 footer: Footer string.
4591 margin: Footer margin.
4592 options: Footer options, mainly for images.
4594 Returns:
4595 Nothing.
4597 """
4598 footer_orig = footer
4599 footer = footer.replace("&[Picture]", "&G")
4601 if len(footer) > 255:
4602 warn("Footer string cannot be longer than Excel's limit of 255 characters")
4603 return
4605 if options is not None:
4606 # For backward compatibility allow options to be the margin.
4607 if not isinstance(options, dict):
4608 options = {"margin": options}
4609 else:
4610 options = {}
4612 # Copy the user defined options so they aren't modified.
4613 options = options.copy()
4615 # For backward compatibility.
4616 if margin is not None:
4617 options["margin"] = margin
4619 # Reset the list in case the function is called more than once.
4620 self.footer_images = []
4622 if options.get("image_left"):
4623 options["image_data"] = options.get("image_data_left")
4624 image = self._image_from_source(options.get("image_left"), options)
4625 image._header_position = "LF"
4626 self.footer_images.append(image)
4628 if options.get("image_center"):
4629 options["image_data"] = options.get("image_data_center")
4630 image = self._image_from_source(options.get("image_center"), options)
4631 image._header_position = "CF"
4632 self.footer_images.append(image)
4634 if options.get("image_right"):
4635 options["image_data"] = options.get("image_data_right")
4636 image = self._image_from_source(options.get("image_right"), options)
4637 image._header_position = "RF"
4638 self.footer_images.append(image)
4640 placeholder_count = footer.count("&G")
4641 image_count = len(self.footer_images)
4643 if placeholder_count != image_count:
4644 warn(
4645 f"Number of footer images '{image_count}' doesn't match placeholder "
4646 f"count '{placeholder_count}' in string: {footer_orig}"
4647 )
4648 self.footer_images = []
4649 return
4651 if "align_with_margins" in options:
4652 self.header_footer_aligns = options["align_with_margins"]
4654 if "scale_with_doc" in options:
4655 self.header_footer_scales = options["scale_with_doc"]
4657 self.footer = footer
4658 self.margin_footer = options.get("margin", 0.3)
4659 self.header_footer_changed = True
4661 if image_count:
4662 self.has_header_vml = True
4664 def repeat_rows(self, first_row: int, last_row: Optional[int] = None) -> None:
4665 """
4666 Set the rows to repeat at the top of each printed page.
4668 Args:
4669 first_row: Start row for range.
4670 last_row: End row for range.
4672 Returns:
4673 Nothing.
4675 """
4676 if last_row is None:
4677 last_row = first_row
4679 # Convert rows to 1 based.
4680 first_row += 1
4681 last_row += 1
4683 # Create the row range area like: $1:$2.
4684 area = f"${first_row}:${last_row}"
4686 # Build up the print titles area "Sheet1!$1:$2"
4687 sheetname = quote_sheetname(self.name)
4688 self.repeat_row_range = sheetname + "!" + area
4690 @convert_column_args
4691 def repeat_columns(self, first_col: int, last_col: Optional[int] = None) -> None:
4692 """
4693 Set the columns to repeat at the left hand side of each printed page.
4695 Args:
4696 first_col: Start column for range.
4697 last_col: End column for range.
4699 Returns:
4700 Nothing.
4702 """
4703 if last_col is None:
4704 last_col = first_col
4706 # Convert to A notation.
4707 first_col = xl_col_to_name(first_col, 1)
4708 last_col = xl_col_to_name(last_col, 1)
4710 # Create a column range like $C:$D.
4711 area = first_col + ":" + last_col
4713 # Build up the print area range "=Sheet2!$C:$D"
4714 sheetname = quote_sheetname(self.name)
4715 self.repeat_col_range = sheetname + "!" + area
4717 def hide_gridlines(self, option: Literal[0, 1, 2] = 1) -> None:
4718 """
4719 Set the option to hide gridlines on the screen and the printed page.
4721 Args:
4722 option: 0 : Don't hide gridlines
4723 1 : Hide printed gridlines only
4724 2 : Hide screen and printed gridlines
4726 Returns:
4727 Nothing.
4729 """
4730 if option == 0:
4731 self.print_gridlines = 1
4732 self.screen_gridlines = 1
4733 self.print_options_changed = True
4734 elif option == 1:
4735 self.print_gridlines = 0
4736 self.screen_gridlines = 1
4737 else:
4738 self.print_gridlines = 0
4739 self.screen_gridlines = 0
4741 def print_row_col_headers(self) -> None:
4742 """
4743 Set the option to print the row and column headers on the printed page.
4745 Args:
4746 None.
4748 Returns:
4749 Nothing.
4751 """
4752 self.print_headers = True
4753 self.print_options_changed = True
4755 def hide_row_col_headers(self) -> None:
4756 """
4757 Set the option to hide the row and column headers on the worksheet.
4759 Args:
4760 None.
4762 Returns:
4763 Nothing.
4765 """
4766 self.row_col_headers = True
4768 @convert_range_args
4769 def print_area(
4770 self, first_row: int, first_col: int, last_row: int, last_col: int
4771 ) -> Literal[0, -1]:
4772 """
4773 Set the print area in the current worksheet.
4775 Args:
4776 first_row: The first row of the cell range. (zero indexed).
4777 first_col: The first column of the cell range.
4778 last_row: The last row of the cell range. (zero indexed).
4779 last_col: The last column of the cell range.
4781 Returns:
4782 0: Success.
4783 -1: Row or column is out of worksheet bounds.
4785 """
4786 # Set the print area in the current worksheet.
4788 # Ignore max print area since it is the same as no area for Excel.
4789 if (
4790 first_row == 0
4791 and first_col == 0
4792 and last_row == self.xls_rowmax - 1
4793 and last_col == self.xls_colmax - 1
4794 ):
4795 return -1
4797 # Build up the print area range "Sheet1!$A$1:$C$13".
4798 area = self._convert_name_area(first_row, first_col, last_row, last_col)
4799 self.print_area_range = area
4801 return 0
4803 def print_across(self) -> None:
4804 """
4805 Set the order in which pages are printed.
4807 Args:
4808 None.
4810 Returns:
4811 Nothing.
4813 """
4814 self.page_order = 1
4815 self.page_setup_changed = True
4817 def fit_to_pages(self, width: int, height: int) -> None:
4818 """
4819 Fit the printed area to a specific number of pages both vertically and
4820 horizontally.
4822 Args:
4823 width: Number of pages horizontally.
4824 height: Number of pages vertically.
4826 Returns:
4827 Nothing.
4829 """
4830 self.fit_page = 1
4831 self.fit_width = width
4832 self.fit_height = height
4833 self.page_setup_changed = True
4835 def set_start_page(self, start_page: int) -> None:
4836 """
4837 Set the start page number when printing.
4839 Args:
4840 start_page: Start page number.
4842 Returns:
4843 Nothing.
4845 """
4846 self.page_start = start_page
4848 def set_print_scale(self, scale: int) -> None:
4849 """
4850 Set the scale factor for the printed page.
4852 Args:
4853 scale: Print scale. 10 <= scale <= 400.
4855 Returns:
4856 Nothing.
4858 """
4859 # Confine the scale to Excel's range.
4860 if scale < 10 or scale > 400:
4861 warn(f"Print scale '{scale}' outside range: 10 <= scale <= 400")
4862 return
4864 # Turn off "fit to page" option when print scale is on.
4865 self.fit_page = 0
4867 self.print_scale = int(scale)
4868 self.page_setup_changed = True
4870 def print_black_and_white(self) -> None:
4871 """
4872 Set the option to print the worksheet in black and white.
4874 Args:
4875 None.
4877 Returns:
4878 Nothing.
4880 """
4881 self.black_white = True
4882 self.page_setup_changed = True
4884 def set_h_pagebreaks(self, breaks: List[int]) -> None:
4885 """
4886 Set the horizontal page breaks on a worksheet.
4888 Args:
4889 breaks: List of rows where the page breaks should be added.
4891 Returns:
4892 Nothing.
4894 """
4895 self.hbreaks = breaks
4897 def set_v_pagebreaks(self, breaks: List[int]) -> None:
4898 """
4899 Set the horizontal page breaks on a worksheet.
4901 Args:
4902 breaks: List of columns where the page breaks should be added.
4904 Returns:
4905 Nothing.
4907 """
4908 self.vbreaks = breaks
4910 def set_vba_name(self, name: Optional[str] = None) -> None:
4911 """
4912 Set the VBA name for the worksheet. By default this is the
4913 same as the sheet name: i.e., Sheet1 etc.
4915 Args:
4916 name: The VBA name for the worksheet.
4918 Returns:
4919 Nothing.
4921 """
4922 if name is not None:
4923 self.vba_codename = name
4924 else:
4925 self.vba_codename = "Sheet" + str(self.index + 1)
4927 def ignore_errors(self, options: Optional[Dict[str, Any]] = None) -> Literal[0, -1]:
4928 """
4929 Ignore various Excel errors/warnings in a worksheet for user defined
4930 ranges.
4932 Args:
4933 options: A dict of ignore errors keys with cell range values.
4935 Returns:
4936 0: Success.
4937 -1: Incorrect parameter or option.
4939 """
4940 if options is None:
4941 return -1
4943 # Copy the user defined options so they aren't modified.
4944 options = options.copy()
4946 # Valid input parameters.
4947 valid_parameters = {
4948 "number_stored_as_text",
4949 "eval_error",
4950 "formula_differs",
4951 "formula_range",
4952 "formula_unlocked",
4953 "empty_cell_reference",
4954 "list_data_validation",
4955 "calculated_column",
4956 "two_digit_text_year",
4957 }
4959 # Check for valid input parameters.
4960 for param_key in options.keys():
4961 if param_key not in valid_parameters:
4962 warn(f"Unknown parameter '{param_key}' in ignore_errors()")
4963 return -1
4965 self.ignored_errors = options
4967 return 0
4969 ###########################################################################
4970 #
4971 # Private API.
4972 #
4973 ###########################################################################
4974 def _initialize(self, init_data) -> None:
4975 self.name = init_data["name"]
4976 self.index = init_data["index"]
4977 self.str_table = init_data["str_table"]
4978 self.worksheet_meta = init_data["worksheet_meta"]
4979 self.constant_memory = init_data["constant_memory"]
4980 self.tmpdir = init_data["tmpdir"]
4981 self.date_1904 = init_data["date_1904"]
4982 self.strings_to_numbers = init_data["strings_to_numbers"]
4983 self.strings_to_formulas = init_data["strings_to_formulas"]
4984 self.strings_to_urls = init_data["strings_to_urls"]
4985 self.nan_inf_to_errors = init_data["nan_inf_to_errors"]
4986 self.default_date_format = init_data["default_date_format"]
4987 self.default_url_format = init_data["default_url_format"]
4988 self.workbook_add_format = init_data["workbook_add_format"]
4989 self.excel2003_style = init_data["excel2003_style"]
4990 self.remove_timezone = init_data["remove_timezone"]
4991 self.max_url_length = init_data["max_url_length"]
4992 self.use_future_functions = init_data["use_future_functions"]
4993 self.embedded_images = init_data["embedded_images"]
4994 self.default_row_height = init_data["default_row_height"]
4995 self.default_col_width = init_data["default_col_width"]
4996 self.max_digit_width = init_data["max_digit_width"]
4997 self.cell_padding = init_data["cell_padding"]
4998 self.max_col_width = init_data["max_col_width"]
5000 self.original_row_height = self.default_row_height
5002 if self.excel2003_style:
5003 self.original_row_height = 17
5004 self.default_row_height = 17
5005 self.margin_left = 0.75
5006 self.margin_right = 0.75
5007 self.margin_top = 1
5008 self.margin_bottom = 1
5009 self.margin_header = 0.5
5010 self.margin_footer = 0.5
5011 self.header_footer_aligns = False
5013 # Open a temp filehandle to store row data in constant_memory mode.
5014 if self.constant_memory:
5015 # This is sub-optimal but we need to create a temp file
5016 # with utf8 encoding in Python < 3.
5017 (fd, filename) = tempfile.mkstemp(dir=self.tmpdir)
5018 os.close(fd)
5019 self.row_data_filename = filename
5020 # pylint: disable=consider-using-with
5021 self.row_data_fh = open(filename, mode="w+", encoding="utf-8")
5023 # Set as the worksheet filehandle until the file is assembled.
5024 self.fh = self.row_data_fh
5026 def _assemble_xml_file(self) -> None:
5027 # Assemble and write the XML file.
5029 # Write the XML declaration.
5030 self._xml_declaration()
5032 # Write the root worksheet element.
5033 self._write_worksheet()
5035 # Write the worksheet properties.
5036 self._write_sheet_pr()
5038 # Write the worksheet dimensions.
5039 self._write_dimension()
5041 # Write the sheet view properties.
5042 self._write_sheet_views()
5044 # Write the sheet format properties.
5045 self._write_sheet_format_pr()
5047 # Write the sheet column info.
5048 self._write_cols()
5050 # Write the worksheet data such as rows columns and cells.
5051 if not self.constant_memory:
5052 self._write_sheet_data()
5053 else:
5054 self._write_optimized_sheet_data()
5056 # Write the sheetProtection element.
5057 self._write_sheet_protection()
5059 # Write the protectedRanges element.
5060 self._write_protected_ranges()
5062 # Write the phoneticPr element.
5063 if self.excel2003_style:
5064 self._write_phonetic_pr()
5066 # Write the autoFilter element.
5067 self._write_auto_filter()
5069 # Write the mergeCells element.
5070 self._write_merge_cells()
5072 # Write the conditional formats.
5073 self._write_conditional_formats()
5075 # Write the dataValidations element.
5076 self._write_data_validations()
5078 # Write the hyperlink element.
5079 self._write_hyperlinks()
5081 # Write the printOptions element.
5082 self._write_print_options()
5084 # Write the worksheet page_margins.
5085 self._write_page_margins()
5087 # Write the worksheet page setup.
5088 self._write_page_setup()
5090 # Write the headerFooter element.
5091 self._write_header_footer()
5093 # Write the rowBreaks element.
5094 self._write_row_breaks()
5096 # Write the colBreaks element.
5097 self._write_col_breaks()
5099 # Write the ignoredErrors element.
5100 self._write_ignored_errors()
5102 # Write the drawing element.
5103 self._write_drawings()
5105 # Write the legacyDrawing element.
5106 self._write_legacy_drawing()
5108 # Write the legacyDrawingHF element.
5109 self._write_legacy_drawing_hf()
5111 # Write the picture element, for the background.
5112 self._write_picture()
5114 # Write the tableParts element.
5115 self._write_table_parts()
5117 # Write the extLst elements.
5118 self._write_ext_list()
5120 # Close the worksheet tag.
5121 self._xml_end_tag("worksheet")
5123 # Close the file.
5124 self._xml_close()
5126 def _check_dimensions(
5127 self, row: int, col: int, ignore_row=False, ignore_col=False
5128 ) -> int:
5129 # Check that row and col are valid and store the max and min
5130 # values for use in other methods/elements. The ignore_row /
5131 # ignore_col flags is used to indicate that we wish to perform
5132 # the dimension check without storing the value. The ignore
5133 # flags are use by set_row() and data_validate.
5135 # Check that the row/col are within the worksheet bounds.
5136 if row < 0 or col < 0:
5137 return -1
5138 if row >= self.xls_rowmax or col >= self.xls_colmax:
5139 return -1
5141 # In constant_memory mode we don't change dimensions for rows
5142 # that are already written.
5143 if not ignore_row and not ignore_col and self.constant_memory:
5144 if row < self.previous_row:
5145 return -2
5147 if not ignore_row:
5148 if self.dim_rowmin is None or row < self.dim_rowmin:
5149 self.dim_rowmin = row
5150 if self.dim_rowmax is None or row > self.dim_rowmax:
5151 self.dim_rowmax = row
5153 if not ignore_col:
5154 if self.dim_colmin is None or col < self.dim_colmin:
5155 self.dim_colmin = col
5156 if self.dim_colmax is None or col > self.dim_colmax:
5157 self.dim_colmax = col
5159 return 0
5161 def _convert_date_time(self, dt_obj):
5162 # Convert a datetime object to an Excel serial date and time.
5163 return _datetime_to_excel_datetime(dt_obj, self.date_1904, self.remove_timezone)
5165 def _convert_name_area(self, row_num_1, col_num_1, row_num_2, col_num_2):
5166 # Convert zero indexed rows and columns to the format required by
5167 # worksheet named ranges, eg, "Sheet1!$A$1:$C$13".
5169 range1 = ""
5170 range2 = ""
5171 area = ""
5172 row_col_only = 0
5174 # Convert to A1 notation.
5175 col_char_1 = xl_col_to_name(col_num_1, 1)
5176 col_char_2 = xl_col_to_name(col_num_2, 1)
5177 row_char_1 = "$" + str(row_num_1 + 1)
5178 row_char_2 = "$" + str(row_num_2 + 1)
5180 # We need to handle special cases that refer to rows or columns only.
5181 if row_num_1 == 0 and row_num_2 == self.xls_rowmax - 1:
5182 range1 = col_char_1
5183 range2 = col_char_2
5184 row_col_only = 1
5185 elif col_num_1 == 0 and col_num_2 == self.xls_colmax - 1:
5186 range1 = row_char_1
5187 range2 = row_char_2
5188 row_col_only = 1
5189 else:
5190 range1 = col_char_1 + row_char_1
5191 range2 = col_char_2 + row_char_2
5193 # A repeated range is only written once (if it isn't a special case).
5194 if range1 == range2 and not row_col_only:
5195 area = range1
5196 else:
5197 area = range1 + ":" + range2
5199 # Build up the print area range "Sheet1!$A$1:$C$13".
5200 sheetname = quote_sheetname(self.name)
5201 area = sheetname + "!" + area
5203 return area
5205 def _sort_pagebreaks(self, breaks):
5206 # This is an internal method used to filter elements of a list of
5207 # pagebreaks used in the _store_hbreak() and _store_vbreak() methods.
5208 # It:
5209 # 1. Removes duplicate entries from the list.
5210 # 2. Sorts the list.
5211 # 3. Removes 0 from the list if present.
5212 if not breaks:
5213 return []
5215 breaks_set = set(breaks)
5217 if 0 in breaks_set:
5218 breaks_set.remove(0)
5220 breaks_list = list(breaks_set)
5221 breaks_list.sort()
5223 # The Excel 2007 specification says that the maximum number of page
5224 # breaks is 1026. However, in practice it is actually 1023.
5225 max_num_breaks = 1023
5226 if len(breaks_list) > max_num_breaks:
5227 breaks_list = breaks_list[:max_num_breaks]
5229 return breaks_list
5231 def _extract_filter_tokens(self, expression):
5232 # Extract the tokens from the filter expression. The tokens are mainly
5233 # non-whitespace groups. The only tricky part is to extract string
5234 # tokens that contain whitespace and/or quoted double quotes (Excel's
5235 # escaped quotes).
5236 #
5237 # Examples: 'x < 2000'
5238 # 'x > 2000 and x < 5000'
5239 # 'x = "foo"'
5240 # 'x = "foo bar"'
5241 # 'x = "foo "" bar"'
5242 #
5243 if not expression:
5244 return []
5246 token_re = re.compile(r'"(?:[^"]|"")*"|\S+')
5247 tokens = token_re.findall(expression)
5249 new_tokens = []
5250 # Remove single leading and trailing quotes and un-escape other quotes.
5251 for token in tokens:
5252 if token.startswith('"'):
5253 token = token[1:]
5255 if token.endswith('"'):
5256 token = token[:-1]
5258 token = token.replace('""', '"')
5260 new_tokens.append(token)
5262 return new_tokens
5264 def _parse_filter_expression(self, expression, tokens):
5265 # Converts the tokens of a possibly conditional expression into 1 or 2
5266 # sub expressions for further parsing.
5267 #
5268 # Examples:
5269 # ('x', '==', 2000) -> exp1
5270 # ('x', '>', 2000, 'and', 'x', '<', 5000) -> exp1 and exp2
5272 if len(tokens) == 7:
5273 # The number of tokens will be either 3 (for 1 expression)
5274 # or 7 (for 2 expressions).
5275 conditional = tokens[3]
5277 if re.match("(and|&&)", conditional):
5278 conditional = 0
5279 elif re.match(r"(or|\|\|)", conditional):
5280 conditional = 1
5281 else:
5282 warn(
5283 f"Token '{conditional}' is not a valid conditional "
5284 f"in filter expression '{expression}'"
5285 )
5287 expression_1 = self._parse_filter_tokens(expression, tokens[0:3])
5288 expression_2 = self._parse_filter_tokens(expression, tokens[4:7])
5289 return expression_1 + [conditional] + expression_2
5291 return self._parse_filter_tokens(expression, tokens)
5293 def _parse_filter_tokens(self, expression, tokens):
5294 # Parse the 3 tokens of a filter expression and return the operator
5295 # and token. The use of numbers instead of operators is a legacy of
5296 # Spreadsheet::WriteExcel.
5297 operators = {
5298 "==": 2,
5299 "=": 2,
5300 "=~": 2,
5301 "eq": 2,
5302 "!=": 5,
5303 "!~": 5,
5304 "ne": 5,
5305 "<>": 5,
5306 "<": 1,
5307 "<=": 3,
5308 ">": 4,
5309 ">=": 6,
5310 }
5312 operator = operators.get(tokens[1], None)
5313 token = tokens[2]
5315 # Special handling of "Top" filter expressions.
5316 if re.match("top|bottom", tokens[0].lower()):
5317 value = int(tokens[1])
5319 if value < 1 or value > 500:
5320 warn(
5321 f"The value '{token}' in expression '{expression}' "
5322 f"must be in the range 1 to 500"
5323 )
5325 token = token.lower()
5327 if token not in ("items", "%"):
5328 warn(
5329 f"The type '{token}' in expression '{expression}' "
5330 f"must be either 'items' or '%%'"
5331 )
5333 if tokens[0].lower() == "top":
5334 operator = 30
5335 else:
5336 operator = 32
5338 if tokens[2] == "%":
5339 operator += 1
5341 token = str(value)
5343 if not operator and tokens[0]:
5344 warn(
5345 f"Token '{token[0]}' is not a valid operator "
5346 f"in filter expression '{expression}'."
5347 )
5349 # Special handling for Blanks/NonBlanks.
5350 if re.match("blanks|nonblanks", token.lower()):
5351 # Only allow Equals or NotEqual in this context.
5352 if operator not in (2, 5):
5353 warn(
5354 f"The operator '{tokens[1]}' in expression '{expression}' "
5355 f"is not valid in relation to Blanks/NonBlanks'."
5356 )
5358 token = token.lower()
5360 # The operator should always be 2 (=) to flag a "simple" equality
5361 # in the binary record. Therefore we convert <> to =.
5362 if token == "blanks":
5363 if operator == 5:
5364 token = " "
5365 else:
5366 if operator == 5:
5367 operator = 2
5368 token = "blanks"
5369 else:
5370 operator = 5
5371 token = " "
5373 # if the string token contains an Excel match character then change the
5374 # operator type to indicate a non "simple" equality.
5375 if operator == 2 and re.search("[*?]", token):
5376 operator = 22
5378 return [operator, token]
5380 def _encode_password(self, password) -> str:
5381 # Hash a worksheet password. Based on the algorithm in
5382 # ECMA-376-4:2016, Office Open XML File Formats — Transitional
5383 # Migration Features, Additional attributes for workbookProtection
5384 # element (Part 1, §18.2.29).
5385 digest = 0x0000
5387 for char in password[::-1]:
5388 digest = ((digest >> 14) & 0x01) | ((digest << 1) & 0x7FFF)
5389 digest ^= ord(char)
5391 digest = ((digest >> 14) & 0x01) | ((digest << 1) & 0x7FFF)
5392 digest ^= len(password)
5393 digest ^= 0xCE4B
5395 return f"{digest:X}"
5397 def _image_from_source(self, source, options: Optional[Dict[str, Any]] = None):
5398 # Backward compatibility utility method to convert an input argument to
5399 # an Image object. The source can be a filename, BytesIO stream or
5400 # an existing Image object.
5401 if isinstance(source, Image):
5402 image = source
5403 elif options is not None and options.get("image_data"):
5404 image = Image(options["image_data"])
5405 image.image_name = source
5406 else:
5407 image = Image(source)
5409 return image
5411 def _prepare_image(
5412 self,
5413 image: Image,
5414 image_id: int,
5415 drawing_id: int,
5416 ) -> None:
5417 # Set up images/drawings.
5419 # Get the effective image width and height in pixels.
5420 width = image._width * image._x_scale
5421 height = image._height * image._y_scale
5423 # Scale by non 96dpi resolutions.
5424 width *= 96.0 / image._x_dpi
5425 height *= 96.0 / image._y_dpi
5427 dimensions = self._position_object_emus(
5428 image._col,
5429 image._row,
5430 image._x_offset,
5431 image._y_offset,
5432 width,
5433 height,
5434 image._anchor,
5435 )
5437 # Convert from pixels to emus.
5438 width = int(0.5 + (width * 9525))
5439 height = int(0.5 + (height * 9525))
5441 # Create a Drawing obj to use with worksheet unless one already exists.
5442 if not self.drawing:
5443 drawing = Drawing()
5444 drawing.embedded = 1
5445 self.drawing = drawing
5447 self.external_drawing_links.append(
5448 ["/drawing", "../drawings/drawing" + str(drawing_id) + ".xml", None]
5449 )
5450 else:
5451 drawing = self.drawing
5453 drawing_object = DrawingInfo()
5454 drawing_object._drawing_type = DrawingTypes.IMAGE
5455 drawing_object._dimensions = dimensions
5456 drawing_object._description = image.image_name
5457 drawing_object._width = width
5458 drawing_object._height = height
5459 drawing_object._shape = None
5460 drawing_object._anchor = image._anchor
5461 drawing_object._rel_index = 0
5462 drawing_object._decorative = image._decorative
5464 if image.description is not None:
5465 drawing_object._description = image.description
5467 if image._url:
5468 url = image._url
5469 target = url._target()
5470 target_mode = url._target_mode()
5472 if not self.drawing_rels.get(url._link):
5473 self.drawing_links.append(["/hyperlink", target, target_mode])
5475 url._rel_index = self._get_drawing_rel_index(url._link)
5476 drawing_object._url = url
5478 if not self.drawing_rels.get(image._digest):
5479 self.drawing_links.append(
5480 [
5481 "/image",
5482 "../media/image" + str(image_id) + "." + image._image_extension,
5483 ]
5484 )
5486 drawing_object._rel_index = self._get_drawing_rel_index(image._digest)
5487 drawing._add_drawing_object(drawing_object)
5489 def _prepare_shape(self, index, drawing_id) -> None:
5490 # Set up shapes/drawings.
5491 (
5492 row,
5493 col,
5494 x_offset,
5495 y_offset,
5496 x_scale,
5497 y_scale,
5498 text,
5499 anchor,
5500 options,
5501 description,
5502 decorative,
5503 ) = self.shapes[index]
5505 width = options.get("width", self.default_col_width * 3)
5506 height = options.get("height", self.default_row_height * 6)
5508 width *= x_scale
5509 height *= y_scale
5511 dimensions = self._position_object_emus(
5512 col, row, x_offset, y_offset, width, height, anchor
5513 )
5515 # Convert from pixels to emus.
5516 width = int(0.5 + (width * 9525))
5517 height = int(0.5 + (height * 9525))
5519 # Create a Drawing obj to use with worksheet unless one already exists.
5520 if not self.drawing:
5521 drawing = Drawing()
5522 drawing.embedded = 1
5523 self.drawing = drawing
5525 self.external_drawing_links.append(
5526 ["/drawing", "../drawings/drawing" + str(drawing_id) + ".xml", None]
5527 )
5528 else:
5529 drawing = self.drawing
5531 shape = Shape("rect", "TextBox", options)
5532 shape.text = text
5534 drawing_object = DrawingInfo()
5535 drawing_object._drawing_type = DrawingTypes.SHAPE
5536 drawing_object._dimensions = dimensions
5537 drawing_object._width = width
5538 drawing_object._height = height
5539 drawing_object._description = description
5540 drawing_object._shape = shape
5541 drawing_object._anchor = anchor
5542 drawing_object._rel_index = 0
5543 drawing_object._decorative = decorative
5545 url = Url.from_options(options)
5546 if url:
5547 target = url._target()
5548 target_mode = url._target_mode()
5550 if not self.drawing_rels.get(url._link):
5551 self.drawing_links.append(["/hyperlink", target, target_mode])
5553 url._rel_index = self._get_drawing_rel_index(url._link)
5554 drawing_object._url = url
5556 drawing._add_drawing_object(drawing_object)
5558 def _prepare_header_image(self, image_id, image) -> None:
5559 # Set up an image without a drawing object for header/footer images.
5561 # Strip the extension from the filename.
5562 image.image_name = re.sub(r"\..*$", "", image.image_name)
5564 if not self.vml_drawing_rels.get(image._digest):
5565 self.vml_drawing_links.append(
5566 [
5567 "/image",
5568 "../media/image" + str(image_id) + "." + image._image_extension,
5569 ]
5570 )
5572 image._ref_id = self._get_vml_drawing_rel_index(image._digest)
5574 self.header_images_list.append(image)
5576 def _prepare_background(self, image_id, image_extension) -> None:
5577 # Set up an image without a drawing object for backgrounds.
5578 self.external_background_links.append(
5579 ["/image", "../media/image" + str(image_id) + "." + image_extension]
5580 )
5582 def _prepare_chart(self, index, chart_id, drawing_id) -> None:
5583 # Set up chart/drawings.
5584 (
5585 row,
5586 col,
5587 chart,
5588 x_offset,
5589 y_offset,
5590 x_scale,
5591 y_scale,
5592 anchor,
5593 description,
5594 decorative,
5595 ) = self.charts[index]
5597 chart.id = chart_id - 1
5599 # Use user specified dimensions, if any.
5600 width = int(0.5 + (chart.width * x_scale))
5601 height = int(0.5 + (chart.height * y_scale))
5603 dimensions = self._position_object_emus(
5604 col, row, x_offset, y_offset, width, height, anchor
5605 )
5607 # Set the chart name for the embedded object if it has been specified.
5608 name = chart.chart_name
5610 # Create a Drawing obj to use with worksheet unless one already exists.
5611 if not self.drawing:
5612 drawing = Drawing()
5613 drawing.embedded = 1
5614 self.drawing = drawing
5616 self.external_drawing_links.append(
5617 ["/drawing", "../drawings/drawing" + str(drawing_id) + ".xml"]
5618 )
5619 else:
5620 drawing = self.drawing
5622 drawing_object = DrawingInfo()
5623 drawing_object._drawing_type = DrawingTypes.CHART
5624 drawing_object._dimensions = dimensions
5625 drawing_object._width = width
5626 drawing_object._height = height
5627 drawing_object._name = name
5628 drawing_object._shape = None
5629 drawing_object._anchor = anchor
5630 drawing_object._rel_index = self._get_drawing_rel_index()
5631 drawing_object._description = description
5632 drawing_object._decorative = decorative
5634 drawing._add_drawing_object(drawing_object)
5636 self.drawing_links.append(
5637 ["/chart", "../charts/chart" + str(chart_id) + ".xml"]
5638 )
5640 def _position_object_emus(
5641 self, col_start, row_start, x1, y1, width, height, anchor
5642 ):
5643 # Calculate the vertices that define the position of a graphical
5644 # object within the worksheet in EMUs.
5645 #
5646 # The vertices are expressed as English Metric Units (EMUs). There are
5647 # 12,700 EMUs per point. Therefore, 12,700 * 3 /4 = 9,525 EMUs per
5648 # pixel
5649 (
5650 col_start,
5651 row_start,
5652 x1,
5653 y1,
5654 col_end,
5655 row_end,
5656 x2,
5657 y2,
5658 x_abs,
5659 y_abs,
5660 ) = self._position_object_pixels(
5661 col_start, row_start, x1, y1, width, height, anchor
5662 )
5664 # Convert the pixel values to EMUs. See above.
5665 x1 = int(0.5 + 9525 * x1)
5666 y1 = int(0.5 + 9525 * y1)
5667 x2 = int(0.5 + 9525 * x2)
5668 y2 = int(0.5 + 9525 * y2)
5669 x_abs = int(0.5 + 9525 * x_abs)
5670 y_abs = int(0.5 + 9525 * y_abs)
5672 return (col_start, row_start, x1, y1, col_end, row_end, x2, y2, x_abs, y_abs)
5674 # Calculate the vertices that define the position of a graphical object
5675 # within the worksheet in pixels.
5676 #
5677 # +------------+------------+
5678 # | A | B |
5679 # +-----+------------+------------+
5680 # | |(x1,y1) | |
5681 # | 1 |(A1)._______|______ |
5682 # | | | | |
5683 # | | | | |
5684 # +-----+----| OBJECT |-----+
5685 # | | | | |
5686 # | 2 | |______________. |
5687 # | | | (B2)|
5688 # | | | (x2,y2)|
5689 # +---- +------------+------------+
5690 #
5691 # Example of an object that covers some of the area from cell A1 to B2.
5692 #
5693 # Based on the width and height of the object we need to calculate 8 vars:
5694 #
5695 # col_start, row_start, col_end, row_end, x1, y1, x2, y2.
5696 #
5697 # We also calculate the absolute x and y position of the top left vertex of
5698 # the object. This is required for images.
5699 #
5700 # The width and height of the cells that the object occupies can be
5701 # variable and have to be taken into account.
5702 #
5703 # The values of col_start and row_start are passed in from the calling
5704 # function. The values of col_end and row_end are calculated by
5705 # subtracting the width and height of the object from the width and
5706 # height of the underlying cells.
5707 #
5708 def _position_object_pixels(
5709 self, col_start, row_start, x1, y1, width, height, anchor
5710 ):
5711 # col_start # Col containing upper left corner of object.
5712 # x1 # Distance to left side of object.
5713 #
5714 # row_start # Row containing top left corner of object.
5715 # y1 # Distance to top of object.
5716 #
5717 # col_end # Col containing lower right corner of object.
5718 # x2 # Distance to right side of object.
5719 #
5720 # row_end # Row containing bottom right corner of object.
5721 # y2 # Distance to bottom of object.
5722 #
5723 # width # Width of object frame.
5724 # height # Height of object frame.
5725 #
5726 # x_abs # Absolute distance to left side of object.
5727 # y_abs # Absolute distance to top side of object.
5728 x_abs = 0
5729 y_abs = 0
5731 # Adjust start column for negative offsets.
5732 # pylint: disable=chained-comparison
5733 while x1 < 0 and col_start > 0:
5734 x1 += self._size_col(col_start - 1)
5735 col_start -= 1
5737 # Adjust start row for negative offsets.
5738 while y1 < 0 and row_start > 0:
5739 y1 += self._size_row(row_start - 1)
5740 row_start -= 1
5742 # Ensure that the image isn't shifted off the page at top left.
5743 x1 = max(0, x1)
5744 y1 = max(0, y1)
5746 # Calculate the absolute x offset of the top-left vertex.
5747 if self.col_size_changed:
5748 for col_id in range(col_start):
5749 x_abs += self._size_col(col_id)
5750 else:
5751 # Optimization for when the column widths haven't changed.
5752 x_abs += self.default_col_width * col_start
5754 x_abs += x1
5756 # Calculate the absolute y offset of the top-left vertex.
5757 if self.row_size_changed:
5758 for row_id in range(row_start):
5759 y_abs += self._size_row(row_id)
5760 else:
5761 # Optimization for when the row heights haven't changed.
5762 y_abs += self.default_row_height * row_start
5764 y_abs += y1
5766 # Adjust start column for offsets that are greater than the col width.
5767 while x1 >= self._size_col(col_start, anchor):
5768 x1 -= self._size_col(col_start)
5769 col_start += 1
5771 # Adjust start row for offsets that are greater than the row height.
5772 while y1 >= self._size_row(row_start, anchor):
5773 y1 -= self._size_row(row_start)
5774 row_start += 1
5776 # Initialize end cell to the same as the start cell.
5777 col_end = col_start
5778 row_end = row_start
5780 # Don't offset the image in the cell if the row/col is hidden.
5781 if self._size_col(col_start, anchor) > 0:
5782 width = width + x1
5783 if self._size_row(row_start, anchor) > 0:
5784 height = height + y1
5786 # Subtract the underlying cell widths to find end cell of the object.
5787 while width >= self._size_col(col_end, anchor):
5788 width -= self._size_col(col_end, anchor)
5789 col_end += 1
5791 # Subtract the underlying cell heights to find end cell of the object.
5792 while height >= self._size_row(row_end, anchor):
5793 height -= self._size_row(row_end, anchor)
5794 row_end += 1
5796 # The end vertices are whatever is left from the width and height.
5797 x2 = width
5798 y2 = height
5800 return [col_start, row_start, x1, y1, col_end, row_end, x2, y2, x_abs, y_abs]
5802 def _size_col(self, col: int, anchor=0):
5803 # Look up the cell value to see if it has been changed.
5804 if col in self.col_info:
5805 width = self.col_info[col].width
5806 hidden = self.col_info[col].hidden
5808 if width is None:
5809 width = self.default_col_width
5811 if hidden and anchor != 4:
5812 width = 0
5814 return width
5816 return self.default_col_width
5818 def _size_row(self, row: int, anchor=0):
5819 # Look up the cell value to see if it has been changed
5820 if row in self.row_sizes:
5821 height = self.row_sizes[row][0]
5822 hidden = self.row_sizes[row][1]
5824 if hidden and anchor != 4:
5825 height = 0
5827 return height
5829 return self.default_row_height
5831 def _pixels_to_height(self, pixels):
5832 # Convert the height of a cell from pixels to character units.
5833 return 0.75 * pixels
5835 def _comment_vertices(self, comment: CommentType):
5836 # Calculate the positions of the comment object.
5837 anchor = 0
5838 vertices = self._position_object_pixels(
5839 comment.start_col,
5840 comment.start_row,
5841 comment.x_offset,
5842 comment.y_offset,
5843 comment.width,
5844 comment.height,
5845 anchor,
5846 )
5848 # Add the width and height for VML.
5849 vertices.append(comment.width)
5850 vertices.append(comment.height)
5852 return vertices
5854 def _button_vertices(self, button: ButtonType):
5855 # Calculate the positions of the button object.
5856 anchor = 0
5857 vertices = self._position_object_pixels(
5858 button.col,
5859 button.row,
5860 button.x_offset,
5861 button.y_offset,
5862 button.width,
5863 button.height,
5864 anchor,
5865 )
5867 # Add the width and height for VML.
5868 vertices.append(button.width)
5869 vertices.append(button.height)
5871 return vertices
5873 def _prepare_vml_objects(
5874 self, vml_data_id, vml_shape_id, vml_drawing_id, comment_id
5875 ):
5876 comments = []
5877 # Sort the comments into row/column order for easier comparison
5878 # testing and set the external links for comments and buttons.
5879 row_nums = sorted(self.comments.keys())
5881 for row in row_nums:
5882 col_nums = sorted(self.comments[row].keys())
5884 for col in col_nums:
5885 comment = self.comments[row][col]
5886 comment.vertices = self._comment_vertices(comment)
5888 # Set comment visibility if required and not user defined.
5889 if comment.is_visible is None:
5890 comment.is_visible = self.comments_visible
5892 # Set comment author if not already user defined.
5893 if comment.author is None:
5894 comment.author = self.comments_author
5896 comments.append(comment)
5898 for button in self.buttons_list:
5899 button.vertices = self._button_vertices(button)
5901 self.external_vml_links.append(
5902 ["/vmlDrawing", "../drawings/vmlDrawing" + str(vml_drawing_id) + ".vml"]
5903 )
5905 if self.has_comments:
5906 self.comments_list = comments
5908 self.external_comment_links.append(
5909 ["/comments", "../comments" + str(comment_id) + ".xml"]
5910 )
5912 count = len(comments)
5913 start_data_id = vml_data_id
5915 # The VML o:idmap data id contains a comma separated range when there
5916 # is more than one 1024 block of comments, like this: data="1,2".
5917 for i in range(int(count / 1024)):
5918 data_id = start_data_id + i + 1
5919 vml_data_id = f"{vml_data_id},{data_id}"
5921 self.vml_data_id = vml_data_id
5922 self.vml_shape_id = vml_shape_id
5924 return count
5926 def _prepare_header_vml_objects(self, vml_header_id, vml_drawing_id) -> None:
5927 # Set up external linkage for VML header/footer images.
5929 self.vml_header_id = vml_header_id
5931 self.external_vml_links.append(
5932 ["/vmlDrawing", "../drawings/vmlDrawing" + str(vml_drawing_id) + ".vml"]
5933 )
5935 def _prepare_tables(self, table_id, seen) -> None:
5936 # Set the table ids for the worksheet tables.
5937 for table in self.tables:
5938 table["id"] = table_id
5940 if table.get("name") is None:
5941 # Set a default name.
5942 table["name"] = "Table" + str(table_id)
5944 # Check for duplicate table names.
5945 name = table["name"].lower()
5947 if name in seen:
5948 raise DuplicateTableName(
5949 f"Duplicate name '{table['name']}' used in worksheet.add_table()."
5950 )
5952 seen[name] = True
5954 # Store the link used for the rels file.
5955 self.external_table_links.append(
5956 ["/table", "../tables/table" + str(table_id) + ".xml"]
5957 )
5958 table_id += 1
5960 def _table_function_to_formula(self, function, col_name):
5961 # Convert a table total function to a worksheet formula.
5962 formula = ""
5964 # Escape special characters, as required by Excel.
5965 col_name = col_name.replace("'", "''")
5966 col_name = col_name.replace("#", "'#")
5967 col_name = col_name.replace("]", "']")
5968 col_name = col_name.replace("[", "'[")
5970 subtotals = {
5971 "average": 101,
5972 "countNums": 102,
5973 "count": 103,
5974 "max": 104,
5975 "min": 105,
5976 "stdDev": 107,
5977 "sum": 109,
5978 "var": 110,
5979 }
5981 if function in subtotals:
5982 func_num = subtotals[function]
5983 formula = f"SUBTOTAL({func_num},[{col_name}])"
5984 else:
5985 warn(f"Unsupported function '{function}' in add_table()")
5987 return formula
5989 def _set_spark_color(self, sparkline, options, user_color) -> None:
5990 # Set the sparkline color.
5991 if user_color not in options:
5992 return
5994 sparkline[user_color] = Color._from_value(options[user_color])
5996 def _get_range_data(self, row_start, col_start, row_end, col_end):
5997 # Returns a range of data from the worksheet _table to be used in
5998 # chart cached data. Strings are returned as SST ids and decoded
5999 # in the workbook. Return None for data that doesn't exist since
6000 # Excel can chart have series with data missing.
6002 if self.constant_memory:
6003 return ()
6005 data = []
6007 # Iterate through the table data.
6008 for row_num in range(row_start, row_end + 1):
6009 # Store None if row doesn't exist.
6010 if row_num not in self.table:
6011 data.append(None)
6012 continue
6014 for col_num in range(col_start, col_end + 1):
6015 if col_num in self.table[row_num]:
6016 cell = self.table[row_num][col_num]
6018 cell_type = cell.__class__.__name__
6020 if cell_type in ("Number", "Datetime"):
6021 # Return a number with Excel's precision.
6022 data.append(f"{cell.number:.16g}")
6024 elif cell_type == "String":
6025 # Return a string from it's shared string index.
6026 index = cell.string
6027 string = self.str_table._get_shared_string(index)
6029 data.append(string)
6031 elif cell_type in ("Formula", "ArrayFormula"):
6032 # Return the formula value.
6033 value = cell.value
6035 if value is None:
6036 value = 0
6038 data.append(value)
6040 elif cell_type == "Blank":
6041 # Return a empty cell.
6042 data.append("")
6043 else:
6044 # Store None if column doesn't exist.
6045 data.append(None)
6047 return data
6049 def _csv_join(self, *items):
6050 # Create a csv string for use with data validation formulas and lists.
6052 # Convert non string types to string.
6053 items = [str(item) if not isinstance(item, str) else item for item in items]
6055 return ",".join(items)
6057 def _escape_url(self, url):
6058 # Don't escape URL if it looks already escaped.
6059 if re.search("%[0-9a-fA-F]{2}", url):
6060 return url
6062 # Can't use url.quote() here because it doesn't match Excel.
6063 url = url.replace("%", "%25")
6064 url = url.replace('"', "%22")
6065 url = url.replace(" ", "%20")
6066 url = url.replace("<", "%3c")
6067 url = url.replace(">", "%3e")
6068 url = url.replace("[", "%5b")
6069 url = url.replace("]", "%5d")
6070 url = url.replace("^", "%5e")
6071 url = url.replace("`", "%60")
6072 url = url.replace("{", "%7b")
6073 url = url.replace("}", "%7d")
6075 return url
6077 def _get_drawing_rel_index(self, target=None):
6078 # Get the index used to address a drawing rel link.
6079 if target is None:
6080 self.drawing_rels_id += 1
6081 return self.drawing_rels_id
6083 if self.drawing_rels.get(target):
6084 return self.drawing_rels[target]
6086 self.drawing_rels_id += 1
6087 self.drawing_rels[target] = self.drawing_rels_id
6088 return self.drawing_rels_id
6090 def _get_vml_drawing_rel_index(self, target=None):
6091 # Get the index used to address a vml drawing rel link.
6092 if self.vml_drawing_rels.get(target):
6093 return self.vml_drawing_rels[target]
6095 self.vml_drawing_rels_id += 1
6096 self.vml_drawing_rels[target] = self.vml_drawing_rels_id
6097 return self.vml_drawing_rels_id
6099 ###########################################################################
6100 #
6101 # The following font methods are mainly duplicated from the Styles class
6102 # with appropriate changes for rich string styles.
6103 #
6104 ###########################################################################
6105 def _write_font(self, xf_format) -> None:
6106 # Write the <font> element.
6107 xml_writer = self.rstring
6109 xml_writer._xml_start_tag("rPr")
6111 # Handle the main font properties.
6112 if xf_format.bold:
6113 xml_writer._xml_empty_tag("b")
6114 if xf_format.italic:
6115 xml_writer._xml_empty_tag("i")
6116 if xf_format.font_strikeout:
6117 xml_writer._xml_empty_tag("strike")
6118 if xf_format.font_outline:
6119 xml_writer._xml_empty_tag("outline")
6120 if xf_format.font_shadow:
6121 xml_writer._xml_empty_tag("shadow")
6123 # Handle the underline variants.
6124 if xf_format.underline:
6125 self._write_underline(xf_format.underline)
6127 # Handle super/subscript.
6128 if xf_format.font_script == 1:
6129 self._write_vert_align("superscript")
6130 if xf_format.font_script == 2:
6131 self._write_vert_align("subscript")
6133 # Write the font size
6134 xml_writer._xml_empty_tag("sz", [("val", xf_format.font_size)])
6136 # Handle colors.
6137 if xf_format.theme == -1:
6138 # Ignore for excel2003_style.
6139 pass
6140 elif xf_format.theme:
6141 self._write_rstring_color("color", [("theme", xf_format.theme)])
6142 elif xf_format.color_indexed:
6143 self._write_rstring_color("color", [("indexed", xf_format.color_indexed)])
6144 elif xf_format.font_color:
6145 color = xf_format.font_color
6146 if not color._is_automatic:
6147 self._write_rstring_color("color", color._attributes())
6148 else:
6149 self._write_rstring_color("color", [("theme", 1)])
6151 # Write some other font properties related to font families.
6152 xml_writer._xml_empty_tag("rFont", [("val", xf_format.font_name)])
6153 xml_writer._xml_empty_tag("family", [("val", xf_format.font_family)])
6155 if xf_format.font_name == "Calibri" and not xf_format.hyperlink:
6156 xml_writer._xml_empty_tag("scheme", [("val", xf_format.font_scheme)])
6158 xml_writer._xml_end_tag("rPr")
6160 def _write_underline(self, underline) -> None:
6161 # Write the underline font element.
6162 attributes = []
6164 # Handle the underline variants.
6165 if underline == 2:
6166 attributes = [("val", "double")]
6167 elif underline == 33:
6168 attributes = [("val", "singleAccounting")]
6169 elif underline == 34:
6170 attributes = [("val", "doubleAccounting")]
6172 self.rstring._xml_empty_tag("u", attributes)
6174 def _write_vert_align(self, val) -> None:
6175 # Write the <vertAlign> font sub-element.
6176 attributes = [("val", val)]
6178 self.rstring._xml_empty_tag("vertAlign", attributes)
6180 def _write_rstring_color(self, name, attributes) -> None:
6181 # Write the <color> element.
6182 self.rstring._xml_empty_tag(name, attributes)
6184 def _opt_close(self) -> None:
6185 # Close the row data filehandle in constant_memory mode.
6186 if not self.row_data_fh_closed:
6187 self.row_data_fh.close()
6188 self.row_data_fh_closed = True
6190 def _opt_reopen(self) -> None:
6191 # Reopen the row data filehandle in constant_memory mode.
6192 if self.row_data_fh_closed:
6193 filename = self.row_data_filename
6194 # pylint: disable=consider-using-with
6195 self.row_data_fh = open(filename, mode="a+", encoding="utf-8")
6196 self.row_data_fh_closed = False
6197 self.fh = self.row_data_fh
6199 def _set_icon_props(self, total_icons, user_props=None):
6200 # Set the sub-properties for icons.
6201 props = []
6203 # Set the defaults.
6204 for _ in range(total_icons):
6205 props.append({"criteria": False, "value": 0, "type": "percent"})
6207 # Set the default icon values based on the number of icons.
6208 if total_icons == 3:
6209 props[0]["value"] = 67
6210 props[1]["value"] = 33
6212 if total_icons == 4:
6213 props[0]["value"] = 75
6214 props[1]["value"] = 50
6215 props[2]["value"] = 25
6217 if total_icons == 5:
6218 props[0]["value"] = 80
6219 props[1]["value"] = 60
6220 props[2]["value"] = 40
6221 props[3]["value"] = 20
6223 # Overwrite default properties with user defined properties.
6224 if user_props:
6225 # Ensure we don't set user properties for lowest icon.
6226 max_data = len(user_props)
6227 if max_data >= total_icons:
6228 max_data = total_icons - 1
6230 for i in range(max_data):
6231 # Set the user defined 'value' property.
6232 if user_props[i].get("value") is not None:
6233 props[i]["value"] = user_props[i]["value"]
6235 # Remove the formula '=' sign if it exists.
6236 tmp = props[i]["value"]
6237 if isinstance(tmp, str) and tmp.startswith("="):
6238 props[i]["value"] = tmp.lstrip("=")
6240 # Set the user defined 'type' property.
6241 if user_props[i].get("type"):
6242 valid_types = ("percent", "percentile", "number", "formula")
6244 if user_props[i]["type"] not in valid_types:
6245 warn(
6246 f"Unknown icon property type '{user_props[i]['type']}' "
6247 f"for sub-property 'type' in conditional_format()."
6248 )
6249 else:
6250 props[i]["type"] = user_props[i]["type"]
6252 if props[i]["type"] == "number":
6253 props[i]["type"] = "num"
6255 # Set the user defined 'criteria' property.
6256 criteria = user_props[i].get("criteria")
6257 if criteria and criteria == ">":
6258 props[i]["criteria"] = True
6260 return props
6262 ###########################################################################
6263 #
6264 # XML methods.
6265 #
6266 ###########################################################################
6268 def _write_worksheet(self) -> None:
6269 # Write the <worksheet> element. This is the root element.
6271 schema = "http://schemas.openxmlformats.org/"
6272 xmlns = schema + "spreadsheetml/2006/main"
6273 xmlns_r = schema + "officeDocument/2006/relationships"
6274 xmlns_mc = schema + "markup-compatibility/2006"
6275 ms_schema = "http://schemas.microsoft.com/"
6276 xmlns_x14ac = ms_schema + "office/spreadsheetml/2009/9/ac"
6278 attributes = [("xmlns", xmlns), ("xmlns:r", xmlns_r)]
6280 # Add some extra attributes for Excel 2010. Mainly for sparklines.
6281 if self.excel_version == 2010:
6282 attributes.append(("xmlns:mc", xmlns_mc))
6283 attributes.append(("xmlns:x14ac", xmlns_x14ac))
6284 attributes.append(("mc:Ignorable", "x14ac"))
6286 self._xml_start_tag("worksheet", attributes)
6288 def _write_dimension(self) -> None:
6289 # Write the <dimension> element. This specifies the range of
6290 # cells in the worksheet. As a special case, empty
6291 # spreadsheets use 'A1' as a range.
6293 if self.dim_rowmin is None and self.dim_colmin is None:
6294 # If the min dimensions are not defined then no dimensions
6295 # have been set and we use the default 'A1'.
6296 ref = "A1"
6298 elif self.dim_rowmin is None and self.dim_colmin is not None:
6299 # If the row dimensions aren't set but the column
6300 # dimensions are set then they have been changed via
6301 # set_column().
6303 if self.dim_colmin == self.dim_colmax:
6304 # The dimensions are a single cell and not a range.
6305 ref = xl_rowcol_to_cell(0, self.dim_colmin)
6306 else:
6307 # The dimensions are a cell range.
6308 cell_1 = xl_rowcol_to_cell(0, self.dim_colmin)
6309 cell_2 = xl_rowcol_to_cell(0, self.dim_colmax)
6310 ref = cell_1 + ":" + cell_2
6312 elif self.dim_rowmin == self.dim_rowmax and self.dim_colmin == self.dim_colmax:
6313 # The dimensions are a single cell and not a range.
6314 ref = xl_rowcol_to_cell(self.dim_rowmin, self.dim_colmin)
6315 else:
6316 # The dimensions are a cell range.
6317 cell_1 = xl_rowcol_to_cell(self.dim_rowmin, self.dim_colmin)
6318 cell_2 = xl_rowcol_to_cell(self.dim_rowmax, self.dim_colmax)
6319 ref = cell_1 + ":" + cell_2
6321 self._xml_empty_tag("dimension", [("ref", ref)])
6323 def _write_sheet_views(self) -> None:
6324 # Write the <sheetViews> element.
6325 self._xml_start_tag("sheetViews")
6327 # Write the sheetView element.
6328 self._write_sheet_view()
6330 self._xml_end_tag("sheetViews")
6332 def _write_sheet_view(self) -> None:
6333 # Write the <sheetViews> element.
6334 attributes = []
6336 # Hide screen gridlines if required.
6337 if not self.screen_gridlines:
6338 attributes.append(("showGridLines", 0))
6340 # Hide screen row/column headers.
6341 if self.row_col_headers:
6342 attributes.append(("showRowColHeaders", 0))
6344 # Hide zeroes in cells.
6345 if not self.show_zeros:
6346 attributes.append(("showZeros", 0))
6348 # Display worksheet right to left for Hebrew, Arabic and others.
6349 if self.is_right_to_left:
6350 attributes.append(("rightToLeft", 1))
6352 # Show that the sheet tab is selected.
6353 if self.selected:
6354 attributes.append(("tabSelected", 1))
6356 # Turn outlines off. Also required in the outlinePr element.
6357 if not self.outline_on:
6358 attributes.append(("showOutlineSymbols", 0))
6360 # Set the page view/layout mode if required.
6361 if self.page_view == 1:
6362 attributes.append(("view", "pageLayout"))
6363 elif self.page_view == 2:
6364 attributes.append(("view", "pageBreakPreview"))
6366 # Set the first visible cell.
6367 if self.top_left_cell != "":
6368 attributes.append(("topLeftCell", self.top_left_cell))
6370 # Set the zoom level.
6371 if self.zoom != 100:
6372 attributes.append(("zoomScale", self.zoom))
6374 if self.page_view == 0 and self.zoom_scale_normal:
6375 attributes.append(("zoomScaleNormal", self.zoom))
6376 if self.page_view == 1:
6377 attributes.append(("zoomScalePageLayoutView", self.zoom))
6378 if self.page_view == 2:
6379 attributes.append(("zoomScaleSheetLayoutView", self.zoom))
6381 attributes.append(("workbookViewId", 0))
6383 if self.is_chartsheet and self.zoom_to_fit:
6384 attributes.append(("zoomToFit", 1))
6386 if self.panes or self.selections:
6387 self._xml_start_tag("sheetView", attributes)
6388 self._write_panes()
6389 self._write_selections()
6390 self._xml_end_tag("sheetView")
6391 else:
6392 self._xml_empty_tag("sheetView", attributes)
6394 def _write_sheet_format_pr(self) -> None:
6395 # Write the <sheetFormatPr> element.
6396 height_in_chars = self.default_row_height * 0.75
6397 row_level = self.outline_row_level
6398 col_level = self.outline_col_level
6400 attributes = [("defaultRowHeight", f"{height_in_chars:.16g}")]
6402 if self.default_row_height != self.original_row_height:
6403 attributes.append(("customHeight", 1))
6405 if self.default_row_zeroed:
6406 attributes.append(("zeroHeight", 1))
6408 if row_level:
6409 attributes.append(("outlineLevelRow", row_level))
6410 if col_level:
6411 attributes.append(("outlineLevelCol", col_level))
6413 if self.excel_version == 2010:
6414 attributes.append(("x14ac:dyDescent", "0.25"))
6416 self._xml_empty_tag("sheetFormatPr", attributes)
6418 def _write_cols(self) -> None:
6419 # Write the <cols> element and <col> sub elements.
6421 # Exit unless some column have been formatted.
6422 if not self.col_info:
6423 return
6425 self._xml_start_tag("cols")
6427 # Use the first element of the column information structures to set
6428 # the initial/previous properties.
6429 first_col = (sorted(self.col_info.keys()))[0]
6430 last_col = first_col
6431 prev_col_options = self.col_info[first_col]
6432 del self.col_info[first_col]
6433 deleted_col = first_col
6434 deleted_col_options = prev_col_options
6436 for col in sorted(self.col_info.keys()):
6437 col_options = self.col_info[col]
6438 # Check if the column number is contiguous with the previous
6439 # column and if the properties are the same.
6440 if col == last_col + 1 and col_options == prev_col_options:
6441 last_col = col
6442 else:
6443 # If not contiguous/equal then we write out the current range
6444 # of columns and start again.
6445 self._write_col_info(first_col, last_col, prev_col_options)
6446 first_col = col
6447 last_col = first_col
6448 prev_col_options = col_options
6450 # We will exit the previous loop with one unhandled column range.
6451 self._write_col_info(first_col, last_col, prev_col_options)
6453 # Put back the deleted first column information structure.
6454 self.col_info[deleted_col] = deleted_col_options
6456 self._xml_end_tag("cols")
6458 def _write_col_info(self, col_min: int, col_max: int, col_info: ColumnInfo) -> None:
6459 # Write the <col> element.
6460 width = col_info.width
6461 has_custom_width = True
6462 xf_index = 0
6464 # Get the cell_format index.
6465 if col_info.column_format:
6466 xf_index = col_info.column_format._get_xf_index()
6468 # Set the Excel default column width.
6469 if width is None:
6470 if not col_info.hidden:
6471 width = self.default_col_width
6472 has_custom_width = False
6473 else:
6474 width = 0
6475 elif width == self.default_col_width:
6476 # Width is defined but same as default.
6477 has_custom_width = False
6479 # Convert column width from pixels to character width.
6480 char_width = (width * 256 // self.max_digit_width) / 256.0
6482 attributes = [
6483 ("min", col_min + 1),
6484 ("max", col_max + 1),
6485 ("width", f"{char_width:.16g}"),
6486 ]
6488 if xf_index:
6489 attributes.append(("style", xf_index))
6490 if col_info.hidden:
6491 attributes.append(("hidden", "1"))
6492 if col_info.autofit:
6493 attributes.append(("bestFit", "1"))
6494 if has_custom_width:
6495 attributes.append(("customWidth", "1"))
6496 if col_info.level:
6497 attributes.append(("outlineLevel", col_info.level))
6498 if col_info.collapsed:
6499 attributes.append(("collapsed", "1"))
6501 self._xml_empty_tag("col", attributes)
6503 def _write_sheet_data(self) -> None:
6504 # Write the <sheetData> element.
6505 if self.dim_rowmin is None:
6506 # If the dimensions aren't defined there is no data to write.
6507 self._xml_empty_tag("sheetData")
6508 else:
6509 self._xml_start_tag("sheetData")
6510 self._write_rows()
6511 self._xml_end_tag("sheetData")
6513 def _write_optimized_sheet_data(self) -> None:
6514 # Write the <sheetData> element when constant_memory is on. In this
6515 # case we read the data stored in the temp file and rewrite it to the
6516 # XML sheet file.
6517 if self.dim_rowmin is None:
6518 # If the dimensions aren't defined then there is no data to write.
6519 self._xml_empty_tag("sheetData")
6520 else:
6521 self._xml_start_tag("sheetData")
6523 # Rewind the filehandle that was used for temp row data.
6524 buff_size = 65536
6525 self.row_data_fh.seek(0)
6526 data = self.row_data_fh.read(buff_size)
6528 while data:
6529 self.fh.write(data)
6530 data = self.row_data_fh.read(buff_size)
6532 self.row_data_fh.close()
6533 os.unlink(self.row_data_filename)
6535 self._xml_end_tag("sheetData")
6537 def _write_page_margins(self) -> None:
6538 # Write the <pageMargins> element.
6539 attributes = [
6540 ("left", self.margin_left),
6541 ("right", self.margin_right),
6542 ("top", self.margin_top),
6543 ("bottom", self.margin_bottom),
6544 ("header", self.margin_header),
6545 ("footer", self.margin_footer),
6546 ]
6548 self._xml_empty_tag("pageMargins", attributes)
6550 def _write_page_setup(self) -> None:
6551 # Write the <pageSetup> element.
6552 #
6553 # The following is an example taken from Excel.
6554 #
6555 # <pageSetup
6556 # paperSize="9"
6557 # scale="110"
6558 # fitToWidth="2"
6559 # fitToHeight="2"
6560 # pageOrder="overThenDown"
6561 # orientation="portrait"
6562 # blackAndWhite="1"
6563 # draft="1"
6564 # horizontalDpi="200"
6565 # verticalDpi="200"
6566 # r:id="rId1"
6567 # />
6568 #
6569 attributes = []
6571 # Skip this element if no page setup has changed.
6572 if not self.page_setup_changed:
6573 return
6575 # Set paper size.
6576 if self.paper_size:
6577 attributes.append(("paperSize", self.paper_size))
6579 # Set the print_scale.
6580 if self.print_scale != 100:
6581 attributes.append(("scale", self.print_scale))
6583 # Set the "Fit to page" properties.
6584 if self.fit_page and self.fit_width != 1:
6585 attributes.append(("fitToWidth", self.fit_width))
6587 if self.fit_page and self.fit_height != 1:
6588 attributes.append(("fitToHeight", self.fit_height))
6590 # Set the page print direction.
6591 if self.page_order:
6592 attributes.append(("pageOrder", "overThenDown"))
6594 # Set start page for printing.
6595 if self.page_start > 1:
6596 attributes.append(("firstPageNumber", self.page_start))
6598 # Set page orientation.
6599 if self.orientation:
6600 attributes.append(("orientation", "portrait"))
6601 else:
6602 attributes.append(("orientation", "landscape"))
6604 # Set the print in black and white option.
6605 if self.black_white:
6606 attributes.append(("blackAndWhite", "1"))
6608 # Set start page for printing.
6609 if self.page_start != 0:
6610 attributes.append(("useFirstPageNumber", "1"))
6612 # Set the DPI. Mainly only for testing.
6613 if self.is_chartsheet:
6614 if self.horizontal_dpi:
6615 attributes.append(("horizontalDpi", self.horizontal_dpi))
6617 if self.vertical_dpi:
6618 attributes.append(("verticalDpi", self.vertical_dpi))
6619 else:
6620 if self.vertical_dpi:
6621 attributes.append(("verticalDpi", self.vertical_dpi))
6623 if self.horizontal_dpi:
6624 attributes.append(("horizontalDpi", self.horizontal_dpi))
6626 self._xml_empty_tag("pageSetup", attributes)
6628 def _write_print_options(self) -> None:
6629 # Write the <printOptions> element.
6630 attributes = []
6632 if not self.print_options_changed:
6633 return
6635 # Set horizontal centering.
6636 if self.hcenter:
6637 attributes.append(("horizontalCentered", 1))
6639 # Set vertical centering.
6640 if self.vcenter:
6641 attributes.append(("verticalCentered", 1))
6643 # Enable row and column headers.
6644 if self.print_headers:
6645 attributes.append(("headings", 1))
6647 # Set printed gridlines.
6648 if self.print_gridlines:
6649 attributes.append(("gridLines", 1))
6651 self._xml_empty_tag("printOptions", attributes)
6653 def _write_header_footer(self) -> None:
6654 # Write the <headerFooter> element.
6655 attributes = []
6657 if not self.header_footer_scales:
6658 attributes.append(("scaleWithDoc", 0))
6660 if not self.header_footer_aligns:
6661 attributes.append(("alignWithMargins", 0))
6663 if self.header_footer_changed:
6664 self._xml_start_tag("headerFooter", attributes)
6665 if self.header:
6666 self._write_odd_header()
6667 if self.footer:
6668 self._write_odd_footer()
6669 self._xml_end_tag("headerFooter")
6670 elif self.excel2003_style:
6671 self._xml_empty_tag("headerFooter", attributes)
6673 def _write_odd_header(self) -> None:
6674 # Write the <headerFooter> element.
6675 self._xml_data_element("oddHeader", self.header)
6677 def _write_odd_footer(self) -> None:
6678 # Write the <headerFooter> element.
6679 self._xml_data_element("oddFooter", self.footer)
6681 def _write_rows(self) -> None:
6682 # Write out the worksheet data as a series of rows and cells.
6683 self._calculate_spans()
6685 for row_num in range(self.dim_rowmin, self.dim_rowmax + 1):
6686 if (
6687 row_num in self.row_info
6688 or row_num in self.comments
6689 or self.table[row_num]
6690 ):
6691 # Only process rows with formatting, cell data and/or comments.
6693 span_index = int(row_num / 16)
6695 if span_index in self.row_spans:
6696 span = self.row_spans[span_index]
6697 else:
6698 span = None
6700 if self.table[row_num]:
6701 # Write the cells if the row contains data.
6702 if row_num not in self.row_info:
6703 self._write_row(row_num, span)
6704 else:
6705 self._write_row(row_num, span, self.row_info[row_num])
6707 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6708 if col_num in self.table[row_num]:
6709 col_ref = self.table[row_num][col_num]
6710 self._write_cell(row_num, col_num, col_ref)
6712 self._xml_end_tag("row")
6714 elif row_num in self.comments:
6715 # Row with comments in cells.
6716 if row_num not in self.row_info:
6717 self._write_empty_row(row_num, span, None)
6718 else:
6719 self._write_empty_row(row_num, span, self.row_info[row_num])
6720 else:
6721 # Blank row with attributes only.
6722 if row_num not in self.row_info:
6723 self._write_empty_row(row_num, span, None)
6724 else:
6725 self._write_empty_row(row_num, span, self.row_info[row_num])
6727 def _write_single_row(self, current_row_num=0) -> None:
6728 # Write out the worksheet data as a single row with cells.
6729 # This method is used when constant_memory is on. A single
6730 # row is written and the data table is reset. That way only
6731 # one row of data is kept in memory at any one time. We don't
6732 # write span data in the optimized case since it is optional.
6734 # Set the new previous row as the current row.
6735 row_num = self.previous_row
6736 self.previous_row = current_row_num
6738 if row_num in self.row_info or row_num in self.comments or self.table[row_num]:
6739 # Only process rows with formatting, cell data and/or comments.
6741 # No span data in optimized mode.
6742 span = None
6744 if self.table[row_num]:
6745 # Write the cells if the row contains data.
6746 if row_num not in self.row_info:
6747 self._write_row(row_num, span)
6748 else:
6749 self._write_row(row_num, span, self.row_info[row_num])
6751 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6752 if col_num in self.table[row_num]:
6753 col_ref = self.table[row_num][col_num]
6754 self._write_cell(row_num, col_num, col_ref)
6756 self._xml_end_tag("row")
6757 else:
6758 # Row attributes or comments only.
6759 self._write_empty_row(row_num, span, self.row_info[row_num])
6761 # Reset table.
6762 self.table.clear()
6764 def _calculate_spans(self) -> None:
6765 # Calculate the "spans" attribute of the <row> tag. This is an
6766 # XLSX optimization and isn't strictly required. However, it
6767 # makes comparing files easier. The span is the same for each
6768 # block of 16 rows.
6769 spans = {}
6770 span_min = None
6771 span_max = None
6773 for row_num in range(self.dim_rowmin, self.dim_rowmax + 1):
6774 if row_num in self.table:
6775 # Calculate spans for cell data.
6776 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6777 if col_num in self.table[row_num]:
6778 if span_min is None:
6779 span_min = col_num
6780 span_max = col_num
6781 else:
6782 span_min = min(span_min, col_num)
6783 span_max = max(span_max, col_num)
6785 if row_num in self.comments:
6786 # Calculate spans for comments.
6787 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6788 if row_num in self.comments and col_num in self.comments[row_num]:
6789 if span_min is None:
6790 span_min = col_num
6791 span_max = col_num
6792 else:
6793 span_min = min(span_min, col_num)
6794 span_max = max(span_max, col_num)
6796 if ((row_num + 1) % 16 == 0) or row_num == self.dim_rowmax:
6797 span_index = int(row_num / 16)
6799 if span_min is not None:
6800 span_min += 1
6801 span_max += 1
6802 spans[span_index] = f"{span_min}:{span_max}"
6803 span_min = None
6805 self.row_spans = spans
6807 def _write_row(
6808 self,
6809 row: int,
6810 spans: Optional[str],
6811 row_info: Optional[RowInfo] = None,
6812 empty_row: bool = False,
6813 ) -> None:
6814 # Write the <row> element.
6815 xf_index = 0
6817 if row_info:
6818 height = row_info.height
6819 row_format = row_info.row_format
6820 hidden = row_info.hidden
6821 level = row_info.level
6822 collapsed = row_info.collapsed
6823 else:
6824 height = None
6825 row_format = None
6826 hidden = 0
6827 level = 0
6828 collapsed = 0
6830 if height is None:
6831 height = self.default_row_height
6833 attributes = [("r", row + 1)]
6835 # Get the cell_format index.
6836 if row_format:
6837 xf_index = row_format._get_xf_index()
6839 # Add row attributes where applicable.
6840 if spans:
6841 attributes.append(("spans", spans))
6843 if xf_index:
6844 attributes.append(("s", xf_index))
6846 if row_format:
6847 attributes.append(("customFormat", 1))
6849 if height != self.original_row_height or (
6850 height == self.original_row_height and height != self.default_row_height
6851 ):
6852 height_in_chars = height * 0.75
6853 attributes.append(("ht", f"{height_in_chars:.16g}"))
6855 if hidden:
6856 attributes.append(("hidden", 1))
6858 if height != self.original_row_height or (
6859 height == self.original_row_height and height != self.default_row_height
6860 ):
6861 attributes.append(("customHeight", 1))
6863 if level:
6864 attributes.append(("outlineLevel", level))
6866 if collapsed:
6867 attributes.append(("collapsed", 1))
6869 if self.excel_version == 2010:
6870 attributes.append(("x14ac:dyDescent", "0.25"))
6872 if empty_row:
6873 self._xml_empty_tag_unencoded("row", attributes)
6874 else:
6875 self._xml_start_tag_unencoded("row", attributes)
6877 def _write_empty_row(
6878 self, row: int, spans: Optional[str], row_info: Optional[RowInfo] = None
6879 ) -> None:
6880 # Write and empty <row> element.
6881 self._write_row(row, spans, row_info, empty_row=True)
6883 def _write_cell(self, row: int, col: int, cell) -> None:
6884 # Write the <cell> element.
6885 # Note. This is the innermost loop so efficiency is important.
6887 cell_range = xl_rowcol_to_cell_fast(row, col)
6888 attributes = [("r", cell_range)]
6890 if cell.format:
6891 # Add the cell format index.
6892 xf_index = cell.format._get_xf_index()
6893 attributes.append(("s", xf_index))
6894 elif row in self.row_info and self.row_info[row].row_format:
6895 # Add the row format.
6896 row_format = self.row_info[row].row_format
6897 attributes.append(("s", row_format._get_xf_index()))
6898 elif col in self.col_info:
6899 # Add the column format.
6900 column_format = self.col_info[col].column_format
6901 if column_format is not None:
6902 attributes.append(("s", column_format._get_xf_index()))
6904 type_cell_name = cell.__class__.__name__
6906 # Write the various cell types.
6907 if type_cell_name in ("Number", "Datetime"):
6908 # Write a number.
6909 self._xml_number_element(cell.number, attributes)
6911 elif type_cell_name in ("String", "RichString"):
6912 # Write a string.
6913 string = cell.string
6915 if not self.constant_memory:
6916 # Write a shared string.
6917 self._xml_string_element(string, attributes)
6918 else:
6919 # Write an optimized in-line string.
6921 # Convert control character to a _xHHHH_ escape.
6922 string = self._escape_control_characters(string)
6924 # Write any rich strings without further tags.
6925 if string.startswith("<r>") and string.endswith("</r>"):
6926 self._xml_rich_inline_string(string, attributes)
6927 else:
6928 # Add attribute to preserve leading or trailing whitespace.
6929 preserve = _preserve_whitespace(string)
6930 self._xml_inline_string(string, preserve, attributes)
6932 elif type_cell_name == "Formula":
6933 # Write a formula. First check the formula value type.
6934 value = cell.value
6935 if isinstance(cell.value, bool):
6936 attributes.append(("t", "b"))
6937 if cell.value:
6938 value = 1
6939 else:
6940 value = 0
6942 elif isinstance(cell.value, str):
6943 error_codes = (
6944 "#DIV/0!",
6945 "#N/A",
6946 "#NAME?",
6947 "#NULL!",
6948 "#NUM!",
6949 "#REF!",
6950 "#VALUE!",
6951 )
6953 if cell.value == "":
6954 # Allow blank to force recalc in some third party apps.
6955 pass
6956 elif cell.value in error_codes:
6957 attributes.append(("t", "e"))
6958 else:
6959 attributes.append(("t", "str"))
6961 self._xml_formula_element(cell.formula, value, attributes)
6963 elif type_cell_name == "ArrayFormula":
6964 # Write a array formula.
6966 if cell.atype == "dynamic":
6967 attributes.append(("cm", 1))
6969 # First check if the formula value is a string.
6970 try:
6971 float(cell.value)
6972 except ValueError:
6973 attributes.append(("t", "str"))
6975 # Write an array formula.
6976 self._xml_start_tag("c", attributes)
6978 self._write_cell_array_formula(cell.formula, cell.range)
6979 self._write_cell_value(cell.value)
6980 self._xml_end_tag("c")
6982 elif type_cell_name == "Blank":
6983 # Write a empty cell.
6984 self._xml_empty_tag("c", attributes)
6986 elif type_cell_name == "Boolean":
6987 # Write a boolean cell.
6988 attributes.append(("t", "b"))
6989 self._xml_start_tag("c", attributes)
6990 self._write_cell_value(cell.boolean)
6991 self._xml_end_tag("c")
6993 elif type_cell_name == "Error":
6994 # Write a boolean cell.
6995 attributes.append(("t", "e"))
6996 attributes.append(("vm", cell.value))
6997 self._xml_start_tag("c", attributes)
6998 self._write_cell_value(cell.error)
6999 self._xml_end_tag("c")
7001 def _write_cell_value(self, value) -> None:
7002 # Write the cell value <v> element.
7003 if value is None:
7004 value = ""
7006 self._xml_data_element("v", value)
7008 def _write_cell_array_formula(self, formula, cell_range) -> None:
7009 # Write the cell array formula <f> element.
7010 attributes = [("t", "array"), ("ref", cell_range)]
7012 self._xml_data_element("f", formula, attributes)
7014 def _write_sheet_pr(self) -> None:
7015 # Write the <sheetPr> element for Sheet level properties.
7016 attributes = []
7018 if (
7019 not self.fit_page
7020 and not self.filter_on
7021 and not self.tab_color
7022 and not self.outline_changed
7023 and not self.vba_codename
7024 ):
7025 return
7027 if self.vba_codename:
7028 attributes.append(("codeName", self.vba_codename))
7030 if self.filter_on:
7031 attributes.append(("filterMode", 1))
7033 if self.fit_page or self.tab_color or self.outline_changed:
7034 self._xml_start_tag("sheetPr", attributes)
7035 self._write_tab_color()
7036 self._write_outline_pr()
7037 self._write_page_set_up_pr()
7038 self._xml_end_tag("sheetPr")
7039 else:
7040 self._xml_empty_tag("sheetPr", attributes)
7042 def _write_page_set_up_pr(self) -> None:
7043 # Write the <pageSetUpPr> element.
7044 if not self.fit_page:
7045 return
7047 attributes = [("fitToPage", 1)]
7048 self._xml_empty_tag("pageSetUpPr", attributes)
7050 def _write_tab_color(self) -> None:
7051 # Write the <tabColor> element.
7052 color = self.tab_color
7054 if not color:
7055 return
7057 self._write_color("tabColor", color._attributes())
7059 def _write_outline_pr(self) -> None:
7060 # Write the <outlinePr> element.
7061 attributes = []
7063 if not self.outline_changed:
7064 return
7066 if self.outline_style:
7067 attributes.append(("applyStyles", 1))
7068 if not self.outline_below:
7069 attributes.append(("summaryBelow", 0))
7070 if not self.outline_right:
7071 attributes.append(("summaryRight", 0))
7072 if not self.outline_on:
7073 attributes.append(("showOutlineSymbols", 0))
7075 self._xml_empty_tag("outlinePr", attributes)
7077 def _write_row_breaks(self) -> None:
7078 # Write the <rowBreaks> element.
7079 page_breaks = self._sort_pagebreaks(self.hbreaks)
7081 if not page_breaks:
7082 return
7084 count = len(page_breaks)
7086 attributes = [
7087 ("count", count),
7088 ("manualBreakCount", count),
7089 ]
7091 self._xml_start_tag("rowBreaks", attributes)
7093 for row_num in page_breaks:
7094 self._write_brk(row_num, 16383)
7096 self._xml_end_tag("rowBreaks")
7098 def _write_col_breaks(self) -> None:
7099 # Write the <colBreaks> element.
7100 page_breaks = self._sort_pagebreaks(self.vbreaks)
7102 if not page_breaks:
7103 return
7105 count = len(page_breaks)
7107 attributes = [
7108 ("count", count),
7109 ("manualBreakCount", count),
7110 ]
7112 self._xml_start_tag("colBreaks", attributes)
7114 for col_num in page_breaks:
7115 self._write_brk(col_num, 1048575)
7117 self._xml_end_tag("colBreaks")
7119 def _write_brk(self, brk_id, brk_max) -> None:
7120 # Write the <brk> element.
7121 attributes = [("id", brk_id), ("max", brk_max), ("man", 1)]
7123 self._xml_empty_tag("brk", attributes)
7125 def _write_merge_cells(self) -> None:
7126 # Write the <mergeCells> element.
7127 merged_cells = self.merge
7128 count = len(merged_cells)
7130 if not count:
7131 return
7133 attributes = [("count", count)]
7135 self._xml_start_tag("mergeCells", attributes)
7137 for merged_range in merged_cells:
7138 # Write the mergeCell element.
7139 self._write_merge_cell(merged_range)
7141 self._xml_end_tag("mergeCells")
7143 def _write_merge_cell(self, merged_range) -> None:
7144 # Write the <mergeCell> element.
7145 (row_min, col_min, row_max, col_max) = merged_range
7147 # Convert the merge dimensions to a cell range.
7148 cell_1 = xl_rowcol_to_cell(row_min, col_min)
7149 cell_2 = xl_rowcol_to_cell(row_max, col_max)
7150 ref = cell_1 + ":" + cell_2
7152 attributes = [("ref", ref)]
7154 self._xml_empty_tag("mergeCell", attributes)
7156 def _write_hyperlinks(self) -> None:
7157 # Process any stored hyperlinks in row/col order and write the
7158 # <hyperlinks> element. The attributes are different for internal
7159 # and external links.
7161 # Sort the hyperlinks into row order.
7162 row_nums = sorted(self.hyperlinks.keys())
7164 # Exit if there are no hyperlinks to process.
7165 if not row_nums:
7166 return
7168 # Write the hyperlink elements.
7169 self._xml_start_tag("hyperlinks")
7171 # Iterate over the rows.
7172 for row_num in row_nums:
7173 # Sort the hyperlinks into column order.
7174 col_nums = sorted(self.hyperlinks[row_num].keys())
7176 # Iterate over the columns.
7177 for col_num in col_nums:
7178 # Get the link data for this cell.
7179 url = self.hyperlinks[row_num][col_num]
7181 # If the cell was overwritten by the user and isn't a string
7182 # then we have to add the url as the string to display.
7183 if self.table and self.table[row_num] and self.table[row_num][col_num]:
7184 cell = self.table[row_num][col_num]
7185 if cell.__class__.__name__ != "String":
7186 url._is_object_link = True
7188 if url._link_type in (UrlTypes.URL, UrlTypes.EXTERNAL):
7189 # External link with rel file relationship.
7190 self.rel_count += 1
7192 self._write_hyperlink_external(
7193 row_num, col_num, self.rel_count, url
7194 )
7196 # Links for use by the packager.
7197 self.external_hyper_links.append(
7198 ["/hyperlink", url._target(), "External"]
7199 )
7200 else:
7201 # Internal link with rel file relationship.
7202 self._write_hyperlink_internal(row_num, col_num, url)
7204 self._xml_end_tag("hyperlinks")
7206 def _write_hyperlink_external(
7207 self, row: int, col: int, id_num: int, url: Url
7208 ) -> None:
7209 # Write the <hyperlink> element for external links.
7210 ref = xl_rowcol_to_cell(row, col)
7211 r_id = "rId" + str(id_num)
7213 attributes = [("ref", ref), ("r:id", r_id)]
7215 if url._anchor:
7216 attributes.append(("location", url._anchor))
7218 if url._is_object_link:
7219 attributes.append(("display", url._text))
7221 if url._tip:
7222 attributes.append(("tooltip", url._tip))
7224 self._xml_empty_tag("hyperlink", attributes)
7226 def _write_hyperlink_internal(self, row: int, col: int, url: Url) -> None:
7227 # Write the <hyperlink> element for internal links.
7228 ref = xl_rowcol_to_cell(row, col)
7230 attributes = [("ref", ref), ("location", url._link)]
7232 if url._tip:
7233 attributes.append(("tooltip", url._tip))
7235 attributes.append(("display", url._text))
7237 self._xml_empty_tag("hyperlink", attributes)
7239 def _write_auto_filter(self) -> None:
7240 # Write the <autoFilter> element.
7241 if not self.autofilter_ref:
7242 return
7244 attributes = [("ref", self.autofilter_ref)]
7246 if self.filter_on:
7247 # Autofilter defined active filters.
7248 self._xml_start_tag("autoFilter", attributes)
7249 self._write_autofilters()
7250 self._xml_end_tag("autoFilter")
7252 else:
7253 # Autofilter defined without active filters.
7254 self._xml_empty_tag("autoFilter", attributes)
7256 def _write_autofilters(self) -> None:
7257 # Function to iterate through the columns that form part of an
7258 # autofilter range and write the appropriate filters.
7259 (col1, col2) = self.filter_range
7261 for col in range(col1, col2 + 1):
7262 # Skip if column doesn't have an active filter.
7263 if col not in self.filter_cols:
7264 continue
7266 # Retrieve the filter tokens and write the autofilter records.
7267 tokens = self.filter_cols[col]
7268 filter_type = self.filter_type[col]
7270 # Filters are relative to first column in the autofilter.
7271 self._write_filter_column(col - col1, filter_type, tokens)
7273 def _write_filter_column(self, col_id, filter_type, filters) -> None:
7274 # Write the <filterColumn> element.
7275 attributes = [("colId", col_id)]
7277 self._xml_start_tag("filterColumn", attributes)
7279 if filter_type == 1:
7280 # Type == 1 is the new XLSX style filter.
7281 self._write_filters(filters)
7282 else:
7283 # Type == 0 is the classic "custom" filter.
7284 self._write_custom_filters(filters)
7286 self._xml_end_tag("filterColumn")
7288 def _write_filters(self, filters) -> None:
7289 # Write the <filters> element.
7290 non_blanks = [filter for filter in filters if str(filter).lower() != "blanks"]
7291 attributes = []
7293 if len(filters) != len(non_blanks):
7294 attributes = [("blank", 1)]
7296 if len(filters) == 1 and len(non_blanks) == 0:
7297 # Special case for blank cells only.
7298 self._xml_empty_tag("filters", attributes)
7299 else:
7300 # General case.
7301 self._xml_start_tag("filters", attributes)
7303 for autofilter in sorted(non_blanks):
7304 self._write_filter(autofilter)
7306 self._xml_end_tag("filters")
7308 def _write_filter(self, val) -> None:
7309 # Write the <filter> element.
7310 attributes = [("val", val)]
7312 self._xml_empty_tag("filter", attributes)
7314 def _write_custom_filters(self, tokens) -> None:
7315 # Write the <customFilters> element.
7316 if len(tokens) == 2:
7317 # One filter expression only.
7318 self._xml_start_tag("customFilters")
7319 self._write_custom_filter(*tokens)
7320 self._xml_end_tag("customFilters")
7321 else:
7322 # Two filter expressions.
7323 attributes = []
7325 # Check if the "join" operand is "and" or "or".
7326 if tokens[2] == 0:
7327 attributes = [("and", 1)]
7328 else:
7329 attributes = [("and", 0)]
7331 # Write the two custom filters.
7332 self._xml_start_tag("customFilters", attributes)
7333 self._write_custom_filter(tokens[0], tokens[1])
7334 self._write_custom_filter(tokens[3], tokens[4])
7335 self._xml_end_tag("customFilters")
7337 def _write_custom_filter(self, operator, val) -> None:
7338 # Write the <customFilter> element.
7339 attributes = []
7341 operators = {
7342 1: "lessThan",
7343 2: "equal",
7344 3: "lessThanOrEqual",
7345 4: "greaterThan",
7346 5: "notEqual",
7347 6: "greaterThanOrEqual",
7348 22: "equal",
7349 }
7351 # Convert the operator from a number to a descriptive string.
7352 if operators[operator] is not None:
7353 operator = operators[operator]
7354 else:
7355 warn(f"Unknown operator = {operator}")
7357 # The 'equal' operator is the default attribute and isn't stored.
7358 if operator != "equal":
7359 attributes.append(("operator", operator))
7360 attributes.append(("val", val))
7362 self._xml_empty_tag("customFilter", attributes)
7364 def _write_sheet_protection(self) -> None:
7365 # Write the <sheetProtection> element.
7366 attributes = []
7368 if not self.protect_options:
7369 return
7371 options = self.protect_options
7373 if options["password"]:
7374 attributes.append(("password", options["password"]))
7375 if options["sheet"]:
7376 attributes.append(("sheet", 1))
7377 if options["content"]:
7378 attributes.append(("content", 1))
7379 if not options["objects"]:
7380 attributes.append(("objects", 1))
7381 if not options["scenarios"]:
7382 attributes.append(("scenarios", 1))
7383 if options["format_cells"]:
7384 attributes.append(("formatCells", 0))
7385 if options["format_columns"]:
7386 attributes.append(("formatColumns", 0))
7387 if options["format_rows"]:
7388 attributes.append(("formatRows", 0))
7389 if options["insert_columns"]:
7390 attributes.append(("insertColumns", 0))
7391 if options["insert_rows"]:
7392 attributes.append(("insertRows", 0))
7393 if options["insert_hyperlinks"]:
7394 attributes.append(("insertHyperlinks", 0))
7395 if options["delete_columns"]:
7396 attributes.append(("deleteColumns", 0))
7397 if options["delete_rows"]:
7398 attributes.append(("deleteRows", 0))
7399 if not options["select_locked_cells"]:
7400 attributes.append(("selectLockedCells", 1))
7401 if options["sort"]:
7402 attributes.append(("sort", 0))
7403 if options["autofilter"]:
7404 attributes.append(("autoFilter", 0))
7405 if options["pivot_tables"]:
7406 attributes.append(("pivotTables", 0))
7407 if not options["select_unlocked_cells"]:
7408 attributes.append(("selectUnlockedCells", 1))
7410 self._xml_empty_tag("sheetProtection", attributes)
7412 def _write_protected_ranges(self) -> None:
7413 # Write the <protectedRanges> element.
7414 if self.num_protected_ranges == 0:
7415 return
7417 self._xml_start_tag("protectedRanges")
7419 for cell_range, range_name, password in self.protected_ranges:
7420 self._write_protected_range(cell_range, range_name, password)
7422 self._xml_end_tag("protectedRanges")
7424 def _write_protected_range(self, cell_range, range_name, password) -> None:
7425 # Write the <protectedRange> element.
7426 attributes = []
7428 if password:
7429 attributes.append(("password", password))
7431 attributes.append(("sqref", cell_range))
7432 attributes.append(("name", range_name))
7434 self._xml_empty_tag("protectedRange", attributes)
7436 def _write_drawings(self) -> None:
7437 # Write the <drawing> elements.
7438 if not self.drawing:
7439 return
7441 self.rel_count += 1
7442 self._write_drawing(self.rel_count)
7444 def _write_drawing(self, drawing_id) -> None:
7445 # Write the <drawing> element.
7446 r_id = "rId" + str(drawing_id)
7448 attributes = [("r:id", r_id)]
7450 self._xml_empty_tag("drawing", attributes)
7452 def _write_legacy_drawing(self) -> None:
7453 # Write the <legacyDrawing> element.
7454 if not self.has_vml:
7455 return
7457 # Increment the relationship id for any drawings or comments.
7458 self.rel_count += 1
7459 r_id = "rId" + str(self.rel_count)
7461 attributes = [("r:id", r_id)]
7463 self._xml_empty_tag("legacyDrawing", attributes)
7465 def _write_legacy_drawing_hf(self) -> None:
7466 # Write the <legacyDrawingHF> element.
7467 if not self.has_header_vml:
7468 return
7470 # Increment the relationship id for any drawings or comments.
7471 self.rel_count += 1
7472 r_id = "rId" + str(self.rel_count)
7474 attributes = [("r:id", r_id)]
7476 self._xml_empty_tag("legacyDrawingHF", attributes)
7478 def _write_picture(self) -> None:
7479 # Write the <picture> element.
7480 if not self.background_image:
7481 return
7483 # Increment the relationship id.
7484 self.rel_count += 1
7485 r_id = "rId" + str(self.rel_count)
7487 attributes = [("r:id", r_id)]
7489 self._xml_empty_tag("picture", attributes)
7491 def _write_data_validations(self) -> None:
7492 # Write the <dataValidations> element.
7493 validations = self.validations
7494 count = len(validations)
7496 if not count:
7497 return
7499 attributes = [("count", count)]
7501 self._xml_start_tag("dataValidations", attributes)
7503 for validation in validations:
7504 # Write the dataValidation element.
7505 self._write_data_validation(validation)
7507 self._xml_end_tag("dataValidations")
7509 def _write_data_validation(self, options) -> None:
7510 # Write the <dataValidation> element.
7511 sqref = ""
7512 attributes = []
7514 # Set the cell range(s) for the data validation.
7515 for cells in options["cells"]:
7516 # Add a space between multiple cell ranges.
7517 if sqref != "":
7518 sqref += " "
7520 (row_first, col_first, row_last, col_last) = cells
7522 # Swap last row/col for first row/col as necessary
7523 if row_first > row_last:
7524 (row_first, row_last) = (row_last, row_first)
7526 if col_first > col_last:
7527 (col_first, col_last) = (col_last, col_first)
7529 sqref += xl_range(row_first, col_first, row_last, col_last)
7531 if options.get("multi_range"):
7532 sqref = options["multi_range"]
7534 if options["validate"] != "none":
7535 attributes.append(("type", options["validate"]))
7537 if options["criteria"] != "between":
7538 attributes.append(("operator", options["criteria"]))
7540 if "error_type" in options:
7541 if options["error_type"] == 1:
7542 attributes.append(("errorStyle", "warning"))
7543 if options["error_type"] == 2:
7544 attributes.append(("errorStyle", "information"))
7546 if options["ignore_blank"]:
7547 attributes.append(("allowBlank", 1))
7549 if not options["dropdown"]:
7550 attributes.append(("showDropDown", 1))
7552 if options["show_input"]:
7553 attributes.append(("showInputMessage", 1))
7555 if options["show_error"]:
7556 attributes.append(("showErrorMessage", 1))
7558 if "error_title" in options:
7559 attributes.append(("errorTitle", options["error_title"]))
7561 if "error_message" in options:
7562 attributes.append(("error", options["error_message"]))
7564 if "input_title" in options:
7565 attributes.append(("promptTitle", options["input_title"]))
7567 if "input_message" in options:
7568 attributes.append(("prompt", options["input_message"]))
7570 attributes.append(("sqref", sqref))
7572 if options["validate"] == "none":
7573 self._xml_empty_tag("dataValidation", attributes)
7574 else:
7575 self._xml_start_tag("dataValidation", attributes)
7577 # Write the formula1 element.
7578 self._write_formula_1(options["value"])
7580 # Write the formula2 element.
7581 if options["maximum"] is not None:
7582 self._write_formula_2(options["maximum"])
7584 self._xml_end_tag("dataValidation")
7586 def _write_formula_1(self, formula) -> None:
7587 # Write the <formula1> element.
7589 if isinstance(formula, list):
7590 formula = self._csv_join(*formula)
7591 formula = f'"{formula}"'
7592 else:
7593 # Check if the formula is a number.
7594 try:
7595 float(formula)
7596 except ValueError:
7597 # Not a number. Remove the formula '=' sign if it exists.
7598 if formula.startswith("="):
7599 formula = formula.lstrip("=")
7601 self._xml_data_element("formula1", formula)
7603 def _write_formula_2(self, formula) -> None:
7604 # Write the <formula2> element.
7606 # Check if the formula is a number.
7607 try:
7608 float(formula)
7609 except ValueError:
7610 # Not a number. Remove the formula '=' sign if it exists.
7611 if formula.startswith("="):
7612 formula = formula.lstrip("=")
7614 self._xml_data_element("formula2", formula)
7616 def _write_conditional_formats(self) -> None:
7617 # Write the Worksheet conditional formats.
7618 ranges = sorted(self.cond_formats.keys())
7620 if not ranges:
7621 return
7623 for cond_range in ranges:
7624 self._write_conditional_formatting(
7625 cond_range, self.cond_formats[cond_range]
7626 )
7628 def _write_conditional_formatting(self, cond_range, params) -> None:
7629 # Write the <conditionalFormatting> element.
7630 attributes = [("sqref", cond_range)]
7631 self._xml_start_tag("conditionalFormatting", attributes)
7632 for param in params:
7633 # Write the cfRule element.
7634 self._write_cf_rule(param)
7635 self._xml_end_tag("conditionalFormatting")
7637 def _write_cf_rule(self, params) -> None:
7638 # Write the <cfRule> element.
7639 attributes = [("type", params["type"])]
7641 if "format" in params and params["format"] is not None:
7642 attributes.append(("dxfId", params["format"]))
7644 attributes.append(("priority", params["priority"]))
7646 if params.get("stop_if_true"):
7647 attributes.append(("stopIfTrue", 1))
7649 if params["type"] == "cellIs":
7650 attributes.append(("operator", params["criteria"]))
7652 self._xml_start_tag("cfRule", attributes)
7654 if "minimum" in params and "maximum" in params:
7655 self._write_formula_element(params["minimum"])
7656 self._write_formula_element(params["maximum"])
7657 else:
7658 self._write_formula_element(params["value"])
7660 self._xml_end_tag("cfRule")
7662 elif params["type"] == "aboveAverage":
7663 if re.search("below", params["criteria"]):
7664 attributes.append(("aboveAverage", 0))
7666 if re.search("equal", params["criteria"]):
7667 attributes.append(("equalAverage", 1))
7669 if re.search("[123] std dev", params["criteria"]):
7670 match = re.search("([123]) std dev", params["criteria"])
7671 attributes.append(("stdDev", match.group(1)))
7673 self._xml_empty_tag("cfRule", attributes)
7675 elif params["type"] == "top10":
7676 if "criteria" in params and params["criteria"] == "%":
7677 attributes.append(("percent", 1))
7679 if "direction" in params:
7680 attributes.append(("bottom", 1))
7682 rank = params["value"] or 10
7683 attributes.append(("rank", rank))
7685 self._xml_empty_tag("cfRule", attributes)
7687 elif params["type"] == "duplicateValues":
7688 self._xml_empty_tag("cfRule", attributes)
7690 elif params["type"] == "uniqueValues":
7691 self._xml_empty_tag("cfRule", attributes)
7693 elif (
7694 params["type"] == "containsText"
7695 or params["type"] == "notContainsText"
7696 or params["type"] == "beginsWith"
7697 or params["type"] == "endsWith"
7698 ):
7699 attributes.append(("operator", params["criteria"]))
7700 attributes.append(("text", params["value"]))
7701 self._xml_start_tag("cfRule", attributes)
7702 self._write_formula_element(params["formula"])
7703 self._xml_end_tag("cfRule")
7705 elif params["type"] == "timePeriod":
7706 attributes.append(("timePeriod", params["criteria"]))
7707 self._xml_start_tag("cfRule", attributes)
7708 self._write_formula_element(params["formula"])
7709 self._xml_end_tag("cfRule")
7711 elif (
7712 params["type"] == "containsBlanks"
7713 or params["type"] == "notContainsBlanks"
7714 or params["type"] == "containsErrors"
7715 or params["type"] == "notContainsErrors"
7716 ):
7717 self._xml_start_tag("cfRule", attributes)
7718 self._write_formula_element(params["formula"])
7719 self._xml_end_tag("cfRule")
7721 elif params["type"] == "colorScale":
7722 self._xml_start_tag("cfRule", attributes)
7723 self._write_color_scale(params)
7724 self._xml_end_tag("cfRule")
7726 elif params["type"] == "dataBar":
7727 self._xml_start_tag("cfRule", attributes)
7728 self._write_data_bar(params)
7730 if params.get("is_data_bar_2010"):
7731 self._write_data_bar_ext(params)
7733 self._xml_end_tag("cfRule")
7735 elif params["type"] == "expression":
7736 self._xml_start_tag("cfRule", attributes)
7737 self._write_formula_element(params["criteria"])
7738 self._xml_end_tag("cfRule")
7740 elif params["type"] == "iconSet":
7741 self._xml_start_tag("cfRule", attributes)
7742 self._write_icon_set(params)
7743 self._xml_end_tag("cfRule")
7745 def _write_formula_element(self, formula) -> None:
7746 # Write the <formula> element.
7748 # Check if the formula is a number.
7749 try:
7750 float(formula)
7751 except ValueError:
7752 # Not a number. Remove the formula '=' sign if it exists.
7753 if formula.startswith("="):
7754 formula = formula.lstrip("=")
7756 self._xml_data_element("formula", formula)
7758 def _write_color_scale(self, param) -> None:
7759 # Write the <colorScale> element.
7761 self._xml_start_tag("colorScale")
7763 self._write_cfvo(param["min_type"], param["min_value"])
7765 if param["mid_type"] is not None:
7766 self._write_cfvo(param["mid_type"], param["mid_value"])
7768 self._write_cfvo(param["max_type"], param["max_value"])
7770 self._write_color("color", param["min_color"]._attributes())
7772 if param["mid_color"] is not None:
7773 self._write_color("color", param["mid_color"]._attributes())
7775 self._write_color("color", param["max_color"]._attributes())
7777 self._xml_end_tag("colorScale")
7779 def _write_data_bar(self, param) -> None:
7780 # Write the <dataBar> element.
7781 attributes = []
7783 # Min and max bar lengths in in the spec but not supported directly by
7784 # Excel.
7785 if "min_length" in param:
7786 attributes.append(("minLength", param["min_length"]))
7788 if "max_length" in param:
7789 attributes.append(("maxLength", param["max_length"]))
7791 if param.get("bar_only"):
7792 attributes.append(("showValue", 0))
7794 self._xml_start_tag("dataBar", attributes)
7796 self._write_cfvo(param["min_type"], param["min_value"])
7797 self._write_cfvo(param["max_type"], param["max_value"])
7798 self._write_color("color", param["bar_color"]._attributes())
7800 self._xml_end_tag("dataBar")
7802 def _write_data_bar_ext(self, param) -> None:
7803 # Write the <extLst> dataBar extension element.
7805 # Create a pseudo GUID for each unique Excel 2010 data bar.
7806 worksheet_count = self.index + 1
7807 data_bar_count = len(self.data_bars_2010) + 1
7808 guid = "{DA7ABA51-AAAA-BBBB-%04X-%012X}" % (worksheet_count, data_bar_count)
7810 # Store the 2010 data bar parameters to write the extLst elements.
7811 param["guid"] = guid
7812 self.data_bars_2010.append(param)
7814 self._xml_start_tag("extLst")
7815 self._write_ext("{B025F937-C7B1-47D3-B67F-A62EFF666E3E}")
7816 self._xml_data_element("x14:id", guid)
7817 self._xml_end_tag("ext")
7818 self._xml_end_tag("extLst")
7820 def _write_icon_set(self, param) -> None:
7821 # Write the <iconSet> element.
7822 attributes = []
7824 # Don't set attribute for default style.
7825 if param["icon_style"] != "3TrafficLights":
7826 attributes = [("iconSet", param["icon_style"])]
7828 if param.get("icons_only"):
7829 attributes.append(("showValue", 0))
7831 if param.get("reverse_icons"):
7832 attributes.append(("reverse", 1))
7834 self._xml_start_tag("iconSet", attributes)
7836 # Write the properties for different icon styles.
7837 for icon in reversed(param["icons"]):
7838 self._write_cfvo(icon["type"], icon["value"], icon["criteria"])
7840 self._xml_end_tag("iconSet")
7842 def _write_cfvo(self, cf_type, val, criteria=None) -> None:
7843 # Write the <cfvo> element.
7844 attributes = [("type", cf_type)]
7846 if val is not None:
7847 attributes.append(("val", val))
7849 if criteria:
7850 attributes.append(("gte", 0))
7852 self._xml_empty_tag("cfvo", attributes)
7854 def _write_color(self, name, attributes) -> None:
7855 # Write the <color> element.
7856 self._xml_empty_tag(name, attributes)
7858 def _write_selections(self) -> None:
7859 # Write the <selection> elements.
7860 for selection in self.selections:
7861 self._write_selection(*selection)
7863 def _write_selection(self, pane, active_cell, sqref) -> None:
7864 # Write the <selection> element.
7865 attributes = []
7867 if pane:
7868 attributes.append(("pane", pane))
7870 if active_cell:
7871 attributes.append(("activeCell", active_cell))
7873 if sqref:
7874 attributes.append(("sqref", sqref))
7876 self._xml_empty_tag("selection", attributes)
7878 def _write_panes(self) -> None:
7879 # Write the frozen or split <pane> elements.
7880 panes = self.panes
7882 if not panes:
7883 return
7885 if panes[4] == 2:
7886 self._write_split_panes(*panes)
7887 else:
7888 self._write_freeze_panes(*panes)
7890 def _write_freeze_panes(
7891 self, row: int, col: int, top_row, left_col, pane_type
7892 ) -> None:
7893 # Write the <pane> element for freeze panes.
7894 attributes = []
7896 y_split = row
7897 x_split = col
7898 top_left_cell = xl_rowcol_to_cell(top_row, left_col)
7899 active_pane = ""
7900 state = ""
7901 active_cell = ""
7902 sqref = ""
7904 # Move user cell selection to the panes.
7905 if self.selections:
7906 (_, active_cell, sqref) = self.selections[0]
7907 self.selections = []
7909 # Set the active pane.
7910 if row and col:
7911 active_pane = "bottomRight"
7913 row_cell = xl_rowcol_to_cell(row, 0)
7914 col_cell = xl_rowcol_to_cell(0, col)
7916 self.selections.append(["topRight", col_cell, col_cell])
7917 self.selections.append(["bottomLeft", row_cell, row_cell])
7918 self.selections.append(["bottomRight", active_cell, sqref])
7920 elif col:
7921 active_pane = "topRight"
7922 self.selections.append(["topRight", active_cell, sqref])
7924 else:
7925 active_pane = "bottomLeft"
7926 self.selections.append(["bottomLeft", active_cell, sqref])
7928 # Set the pane type.
7929 if pane_type == 0:
7930 state = "frozen"
7931 elif pane_type == 1:
7932 state = "frozenSplit"
7933 else:
7934 state = "split"
7936 if x_split:
7937 attributes.append(("xSplit", x_split))
7939 if y_split:
7940 attributes.append(("ySplit", y_split))
7942 attributes.append(("topLeftCell", top_left_cell))
7943 attributes.append(("activePane", active_pane))
7944 attributes.append(("state", state))
7946 self._xml_empty_tag("pane", attributes)
7948 def _write_split_panes(self, row: int, col: int, top_row, left_col, _) -> None:
7949 # Write the <pane> element for split panes.
7950 attributes = []
7951 has_selection = False
7952 active_pane = ""
7953 active_cell = ""
7954 sqref = ""
7956 y_split = row
7957 x_split = col
7959 # Move user cell selection to the panes.
7960 if self.selections:
7961 (_, active_cell, sqref) = self.selections[0]
7962 self.selections = []
7963 has_selection = True
7965 # Convert the row and col to 1/20 twip units with padding.
7966 if y_split:
7967 y_split = int(20 * y_split + 300)
7969 if x_split:
7970 x_split = self._calculate_x_split_width(x_split)
7972 # For non-explicit topLeft definitions, estimate the cell offset based
7973 # on the pixels dimensions. This is only a workaround and doesn't take
7974 # adjusted cell dimensions into account.
7975 if top_row == row and left_col == col:
7976 top_row = int(0.5 + (y_split - 300) / 20 / 15)
7977 left_col = int(0.5 + (x_split - 390) / 20 / 3 * 4 / 64)
7979 top_left_cell = xl_rowcol_to_cell(top_row, left_col)
7981 # If there is no selection set the active cell to the top left cell.
7982 if not has_selection:
7983 active_cell = top_left_cell
7984 sqref = top_left_cell
7986 # Set the Cell selections.
7987 if row and col:
7988 active_pane = "bottomRight"
7990 row_cell = xl_rowcol_to_cell(top_row, 0)
7991 col_cell = xl_rowcol_to_cell(0, left_col)
7993 self.selections.append(["topRight", col_cell, col_cell])
7994 self.selections.append(["bottomLeft", row_cell, row_cell])
7995 self.selections.append(["bottomRight", active_cell, sqref])
7997 elif col:
7998 active_pane = "topRight"
7999 self.selections.append(["topRight", active_cell, sqref])
8001 else:
8002 active_pane = "bottomLeft"
8003 self.selections.append(["bottomLeft", active_cell, sqref])
8005 # Format splits to the same precision as Excel.
8006 if x_split:
8007 attributes.append(("xSplit", f"{x_split:.16g}"))
8009 if y_split:
8010 attributes.append(("ySplit", f"{y_split:.16g}"))
8012 attributes.append(("topLeftCell", top_left_cell))
8014 if has_selection:
8015 attributes.append(("activePane", active_pane))
8017 self._xml_empty_tag("pane", attributes)
8019 def _calculate_x_split_width(self, width):
8020 # Convert column width from user units to pane split width.
8022 max_digit_width = 7 # For Calabri 11.
8023 padding = 5
8025 # Convert to pixels.
8026 if width < 1:
8027 pixels = int(width * (max_digit_width + padding) + 0.5)
8028 else:
8029 pixels = int(width * max_digit_width + 0.5) + padding
8031 # Convert to points.
8032 points = pixels * 3 / 4
8034 # Convert to twips (twentieths of a point).
8035 twips = points * 20
8037 # Add offset/padding.
8038 width = twips + 390
8040 return width
8042 def _write_table_parts(self) -> None:
8043 # Write the <tableParts> element.
8044 tables = self.tables
8045 count = len(tables)
8047 # Return if worksheet doesn't contain any tables.
8048 if not count:
8049 return
8051 attributes = [
8052 (
8053 "count",
8054 count,
8055 )
8056 ]
8058 self._xml_start_tag("tableParts", attributes)
8060 for _ in tables:
8061 # Write the tablePart element.
8062 self.rel_count += 1
8063 self._write_table_part(self.rel_count)
8065 self._xml_end_tag("tableParts")
8067 def _write_table_part(self, r_id) -> None:
8068 # Write the <tablePart> element.
8070 r_id = "rId" + str(r_id)
8072 attributes = [
8073 (
8074 "r:id",
8075 r_id,
8076 )
8077 ]
8079 self._xml_empty_tag("tablePart", attributes)
8081 def _write_ext_list(self) -> None:
8082 # Write the <extLst> element for data bars and sparklines.
8083 has_data_bars = len(self.data_bars_2010)
8084 has_sparklines = len(self.sparklines)
8086 if not has_data_bars and not has_sparklines:
8087 return
8089 # Write the extLst element.
8090 self._xml_start_tag("extLst")
8092 if has_data_bars:
8093 self._write_ext_list_data_bars()
8095 if has_sparklines:
8096 self._write_ext_list_sparklines()
8098 self._xml_end_tag("extLst")
8100 def _write_ext_list_data_bars(self) -> None:
8101 # Write the Excel 2010 data_bar subelements.
8102 self._write_ext("{78C0D931-6437-407d-A8EE-F0AAD7539E65}")
8104 self._xml_start_tag("x14:conditionalFormattings")
8106 # Write the Excel 2010 conditional formatting data bar elements.
8107 for data_bar in self.data_bars_2010:
8108 # Write the x14:conditionalFormatting element.
8109 self._write_conditional_formatting_2010(data_bar)
8111 self._xml_end_tag("x14:conditionalFormattings")
8112 self._xml_end_tag("ext")
8114 def _write_conditional_formatting_2010(self, data_bar) -> None:
8115 # Write the <x14:conditionalFormatting> element.
8116 xmlns_xm = "http://schemas.microsoft.com/office/excel/2006/main"
8118 attributes = [("xmlns:xm", xmlns_xm)]
8120 self._xml_start_tag("x14:conditionalFormatting", attributes)
8122 # Write the x14:cfRule element.
8123 self._write_x14_cf_rule(data_bar)
8125 # Write the x14:dataBar element.
8126 self._write_x14_data_bar(data_bar)
8128 # Write the x14 max and min data bars.
8129 self._write_x14_cfvo(data_bar["x14_min_type"], data_bar["min_value"])
8130 self._write_x14_cfvo(data_bar["x14_max_type"], data_bar["max_value"])
8132 if not data_bar["bar_no_border"]:
8133 # Write the x14:borderColor element.
8134 self._write_x14_border_color(data_bar["bar_border_color"])
8136 # Write the x14:negativeFillColor element.
8137 if not data_bar["bar_negative_color_same"]:
8138 self._write_x14_negative_fill_color(data_bar["bar_negative_color"])
8140 # Write the x14:negativeBorderColor element.
8141 if (
8142 not data_bar["bar_no_border"]
8143 and not data_bar["bar_negative_border_color_same"]
8144 ):
8145 self._write_x14_negative_border_color(data_bar["bar_negative_border_color"])
8147 # Write the x14:axisColor element.
8148 if data_bar["bar_axis_position"] != "none":
8149 self._write_x14_axis_color(data_bar["bar_axis_color"])
8151 self._xml_end_tag("x14:dataBar")
8152 self._xml_end_tag("x14:cfRule")
8154 # Write the xm:sqref element.
8155 self._xml_data_element("xm:sqref", data_bar["range"])
8157 self._xml_end_tag("x14:conditionalFormatting")
8159 def _write_x14_cf_rule(self, data_bar) -> None:
8160 # Write the <x14:cfRule> element.
8161 rule_type = "dataBar"
8162 guid = data_bar["guid"]
8163 attributes = [("type", rule_type), ("id", guid)]
8165 self._xml_start_tag("x14:cfRule", attributes)
8167 def _write_x14_data_bar(self, data_bar) -> None:
8168 # Write the <x14:dataBar> element.
8169 min_length = 0
8170 max_length = 100
8172 attributes = [
8173 ("minLength", min_length),
8174 ("maxLength", max_length),
8175 ]
8177 if not data_bar["bar_no_border"]:
8178 attributes.append(("border", 1))
8180 if data_bar["bar_solid"]:
8181 attributes.append(("gradient", 0))
8183 if data_bar["bar_direction"] == "left":
8184 attributes.append(("direction", "leftToRight"))
8186 if data_bar["bar_direction"] == "right":
8187 attributes.append(("direction", "rightToLeft"))
8189 if data_bar["bar_negative_color_same"]:
8190 attributes.append(("negativeBarColorSameAsPositive", 1))
8192 if (
8193 not data_bar["bar_no_border"]
8194 and not data_bar["bar_negative_border_color_same"]
8195 ):
8196 attributes.append(("negativeBarBorderColorSameAsPositive", 0))
8198 if data_bar["bar_axis_position"] == "middle":
8199 attributes.append(("axisPosition", "middle"))
8201 if data_bar["bar_axis_position"] == "none":
8202 attributes.append(("axisPosition", "none"))
8204 self._xml_start_tag("x14:dataBar", attributes)
8206 def _write_x14_cfvo(self, rule_type, value) -> None:
8207 # Write the <x14:cfvo> element.
8208 attributes = [("type", rule_type)]
8210 if rule_type in ("min", "max", "autoMin", "autoMax"):
8211 self._xml_empty_tag("x14:cfvo", attributes)
8212 else:
8213 self._xml_start_tag("x14:cfvo", attributes)
8214 self._xml_data_element("xm:f", value)
8215 self._xml_end_tag("x14:cfvo")
8217 def _write_x14_border_color(self, color) -> None:
8218 # Write the <x14:borderColor> element.
8219 self._write_color("x14:borderColor", color._attributes())
8221 def _write_x14_negative_fill_color(self, color) -> None:
8222 # Write the <x14:negativeFillColor> element.
8223 self._xml_empty_tag("x14:negativeFillColor", color._attributes())
8225 def _write_x14_negative_border_color(self, color) -> None:
8226 # Write the <x14:negativeBorderColor> element.
8227 self._xml_empty_tag("x14:negativeBorderColor", color._attributes())
8229 def _write_x14_axis_color(self, color) -> None:
8230 # Write the <x14:axisColor> element.
8231 self._xml_empty_tag("x14:axisColor", color._attributes())
8233 def _write_ext_list_sparklines(self) -> None:
8234 # Write the sparkline extension sub-elements.
8235 self._write_ext("{05C60535-1F16-4fd2-B633-F4F36F0B64E0}")
8237 # Write the x14:sparklineGroups element.
8238 self._write_sparkline_groups()
8240 # Write the sparkline elements.
8241 for sparkline in reversed(self.sparklines):
8242 # Write the x14:sparklineGroup element.
8243 self._write_sparkline_group(sparkline)
8245 # Write the x14:colorSeries element.
8246 self._write_color_series(sparkline["series_color"])
8248 # Write the x14:colorNegative element.
8249 self._write_color_negative(sparkline["negative_color"])
8251 # Write the x14:colorAxis element.
8252 self._write_color_axis()
8254 # Write the x14:colorMarkers element.
8255 self._write_color_markers(sparkline["markers_color"])
8257 # Write the x14:colorFirst element.
8258 self._write_color_first(sparkline["first_color"])
8260 # Write the x14:colorLast element.
8261 self._write_color_last(sparkline["last_color"])
8263 # Write the x14:colorHigh element.
8264 self._write_color_high(sparkline["high_color"])
8266 # Write the x14:colorLow element.
8267 self._write_color_low(sparkline["low_color"])
8269 if sparkline["date_axis"]:
8270 self._xml_data_element("xm:f", sparkline["date_axis"])
8272 self._write_sparklines(sparkline)
8274 self._xml_end_tag("x14:sparklineGroup")
8276 self._xml_end_tag("x14:sparklineGroups")
8277 self._xml_end_tag("ext")
8279 def _write_sparklines(self, sparkline) -> None:
8280 # Write the <x14:sparklines> element and <x14:sparkline> sub-elements.
8282 # Write the sparkline elements.
8283 self._xml_start_tag("x14:sparklines")
8285 for i in range(sparkline["count"]):
8286 spark_range = sparkline["ranges"][i]
8287 location = sparkline["locations"][i]
8289 self._xml_start_tag("x14:sparkline")
8290 self._xml_data_element("xm:f", spark_range)
8291 self._xml_data_element("xm:sqref", location)
8292 self._xml_end_tag("x14:sparkline")
8294 self._xml_end_tag("x14:sparklines")
8296 def _write_ext(self, uri) -> None:
8297 # Write the <ext> element.
8298 schema = "http://schemas.microsoft.com/office/"
8299 xmlns_x14 = schema + "spreadsheetml/2009/9/main"
8301 attributes = [
8302 ("xmlns:x14", xmlns_x14),
8303 ("uri", uri),
8304 ]
8306 self._xml_start_tag("ext", attributes)
8308 def _write_sparkline_groups(self) -> None:
8309 # Write the <x14:sparklineGroups> element.
8310 xmlns_xm = "http://schemas.microsoft.com/office/excel/2006/main"
8312 attributes = [("xmlns:xm", xmlns_xm)]
8314 self._xml_start_tag("x14:sparklineGroups", attributes)
8316 def _write_sparkline_group(self, options) -> None:
8317 # Write the <x14:sparklineGroup> element.
8318 #
8319 # Example for order.
8320 #
8321 # <x14:sparklineGroup
8322 # manualMax="0"
8323 # manualMin="0"
8324 # lineWeight="2.25"
8325 # type="column"
8326 # dateAxis="1"
8327 # displayEmptyCellsAs="span"
8328 # markers="1"
8329 # high="1"
8330 # low="1"
8331 # first="1"
8332 # last="1"
8333 # negative="1"
8334 # displayXAxis="1"
8335 # displayHidden="1"
8336 # minAxisType="custom"
8337 # maxAxisType="custom"
8338 # rightToLeft="1">
8339 #
8340 empty = options.get("empty")
8341 attributes = []
8343 if options.get("max") is not None:
8344 if options["max"] == "group":
8345 options["cust_max"] = "group"
8346 else:
8347 attributes.append(("manualMax", options["max"]))
8348 options["cust_max"] = "custom"
8350 if options.get("min") is not None:
8351 if options["min"] == "group":
8352 options["cust_min"] = "group"
8353 else:
8354 attributes.append(("manualMin", options["min"]))
8355 options["cust_min"] = "custom"
8357 # Ignore the default type attribute (line).
8358 if options["type"] != "line":
8359 attributes.append(("type", options["type"]))
8361 if options.get("weight"):
8362 attributes.append(("lineWeight", options["weight"]))
8364 if options.get("date_axis"):
8365 attributes.append(("dateAxis", 1))
8367 if empty:
8368 attributes.append(("displayEmptyCellsAs", empty))
8370 if options.get("markers"):
8371 attributes.append(("markers", 1))
8373 if options.get("high"):
8374 attributes.append(("high", 1))
8376 if options.get("low"):
8377 attributes.append(("low", 1))
8379 if options.get("first"):
8380 attributes.append(("first", 1))
8382 if options.get("last"):
8383 attributes.append(("last", 1))
8385 if options.get("negative"):
8386 attributes.append(("negative", 1))
8388 if options.get("axis"):
8389 attributes.append(("displayXAxis", 1))
8391 if options.get("hidden"):
8392 attributes.append(("displayHidden", 1))
8394 if options.get("cust_min"):
8395 attributes.append(("minAxisType", options["cust_min"]))
8397 if options.get("cust_max"):
8398 attributes.append(("maxAxisType", options["cust_max"]))
8400 if options.get("reverse"):
8401 attributes.append(("rightToLeft", 1))
8403 self._xml_start_tag("x14:sparklineGroup", attributes)
8405 def _write_spark_color(self, tag, color) -> None:
8406 # Helper function for the sparkline color functions below.
8407 if color:
8408 self._write_color(tag, color._attributes())
8410 def _write_color_series(self, color) -> None:
8411 # Write the <x14:colorSeries> element.
8412 self._write_spark_color("x14:colorSeries", color)
8414 def _write_color_negative(self, color) -> None:
8415 # Write the <x14:colorNegative> element.
8416 self._write_spark_color("x14:colorNegative", color)
8418 def _write_color_axis(self) -> None:
8419 # Write the <x14:colorAxis> element.
8420 self._write_spark_color("x14:colorAxis", Color("#000000"))
8422 def _write_color_markers(self, color) -> None:
8423 # Write the <x14:colorMarkers> element.
8424 self._write_spark_color("x14:colorMarkers", color)
8426 def _write_color_first(self, color) -> None:
8427 # Write the <x14:colorFirst> element.
8428 self._write_spark_color("x14:colorFirst", color)
8430 def _write_color_last(self, color) -> None:
8431 # Write the <x14:colorLast> element.
8432 self._write_spark_color("x14:colorLast", color)
8434 def _write_color_high(self, color) -> None:
8435 # Write the <x14:colorHigh> element.
8436 self._write_spark_color("x14:colorHigh", color)
8438 def _write_color_low(self, color) -> None:
8439 # Write the <x14:colorLow> element.
8440 self._write_spark_color("x14:colorLow", color)
8442 def _write_phonetic_pr(self) -> None:
8443 # Write the <phoneticPr> element.
8444 attributes = [
8445 ("fontId", "0"),
8446 ("type", "noConversion"),
8447 ]
8449 self._xml_empty_tag("phoneticPr", attributes)
8451 def _write_ignored_errors(self) -> None:
8452 # Write the <ignoredErrors> element.
8453 if not self.ignored_errors:
8454 return
8456 self._xml_start_tag("ignoredErrors")
8458 if self.ignored_errors.get("number_stored_as_text"):
8459 ignored_range = self.ignored_errors["number_stored_as_text"]
8460 self._write_ignored_error("numberStoredAsText", ignored_range)
8462 if self.ignored_errors.get("eval_error"):
8463 ignored_range = self.ignored_errors["eval_error"]
8464 self._write_ignored_error("evalError", ignored_range)
8466 if self.ignored_errors.get("formula_differs"):
8467 ignored_range = self.ignored_errors["formula_differs"]
8468 self._write_ignored_error("formula", ignored_range)
8470 if self.ignored_errors.get("formula_range"):
8471 ignored_range = self.ignored_errors["formula_range"]
8472 self._write_ignored_error("formulaRange", ignored_range)
8474 if self.ignored_errors.get("formula_unlocked"):
8475 ignored_range = self.ignored_errors["formula_unlocked"]
8476 self._write_ignored_error("unlockedFormula", ignored_range)
8478 if self.ignored_errors.get("empty_cell_reference"):
8479 ignored_range = self.ignored_errors["empty_cell_reference"]
8480 self._write_ignored_error("emptyCellReference", ignored_range)
8482 if self.ignored_errors.get("list_data_validation"):
8483 ignored_range = self.ignored_errors["list_data_validation"]
8484 self._write_ignored_error("listDataValidation", ignored_range)
8486 if self.ignored_errors.get("calculated_column"):
8487 ignored_range = self.ignored_errors["calculated_column"]
8488 self._write_ignored_error("calculatedColumn", ignored_range)
8490 if self.ignored_errors.get("two_digit_text_year"):
8491 ignored_range = self.ignored_errors["two_digit_text_year"]
8492 self._write_ignored_error("twoDigitTextYear", ignored_range)
8494 self._xml_end_tag("ignoredErrors")
8496 def _write_ignored_error(self, error_type, ignored_range) -> None:
8497 # Write the <ignoredError> element.
8498 attributes = [
8499 ("sqref", ignored_range),
8500 (error_type, 1),
8501 ]
8503 self._xml_empty_tag("ignoredError", attributes)