Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.11/site-packages/xlsxwriter/worksheet.py: 23%
Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1###############################################################################
2#
3# Worksheet - A class for writing the Excel XLSX Worksheet file.
4#
5# SPDX-License-Identifier: BSD-2-Clause
6#
7# Copyright (c) 2013-2025, John McNamara, jmcnamara@cpan.org
8#
10# pylint: disable=too-many-return-statements
12# Standard packages.
13import datetime
14import math
15import os
16import re
17import tempfile
18from collections import defaultdict, namedtuple
19from decimal import Decimal
20from fractions import Fraction
21from functools import wraps
22from io import BytesIO, StringIO
23from math import isinf, isnan
24from typing import Any, Dict, List, Literal, Optional, Union
25from warnings import warn
27from xlsxwriter.chart import Chart
28from xlsxwriter.color import Color
29from xlsxwriter.comments import CommentType
30from xlsxwriter.image import Image
31from xlsxwriter.url import Url, UrlTypes
32from xlsxwriter.vml import ButtonType
34# Package imports.
35from . import xmlwriter
36from .drawing import Drawing, DrawingInfo, DrawingTypes
37from .exceptions import DuplicateTableName, OverlappingRange
38from .format import Format
39from .shape import Shape
40from .utility import (
41 _datetime_to_excel_datetime,
42 _get_sparkline_style,
43 _preserve_whitespace,
44 _supported_datetime,
45 quote_sheetname,
46 xl_cell_to_rowcol,
47 xl_col_to_name,
48 xl_pixel_width,
49 xl_range,
50 xl_rowcol_to_cell,
51 xl_rowcol_to_cell_fast,
52)
53from .xmlwriter import XMLwriter
55re_dynamic_function = re.compile(
56 r"""
57 \bANCHORARRAY\( |
58 \bBYCOL\( |
59 \bBYROW\( |
60 \bCHOOSECOLS\( |
61 \bCHOOSEROWS\( |
62 \bDROP\( |
63 \bEXPAND\( |
64 \bFILTER\( |
65 \bHSTACK\( |
66 \bLAMBDA\( |
67 \bMAKEARRAY\( |
68 \bMAP\( |
69 \bRANDARRAY\( |
70 \bREDUCE\( |
71 \bSCAN\( |
72 \bSEQUENCE\( |
73 \bSINGLE\( |
74 \bSORT\( |
75 \bSORTBY\( |
76 \bSWITCH\( |
77 \bTAKE\( |
78 \bTEXTSPLIT\( |
79 \bTOCOL\( |
80 \bTOROW\( |
81 \bUNIQUE\( |
82 \bVSTACK\( |
83 \bWRAPCOLS\( |
84 \bWRAPROWS\( |
85 \bXLOOKUP\(""",
86 re.VERBOSE,
87)
90###############################################################################
91#
92# Decorator functions.
93#
94###############################################################################
95def convert_cell_args(method):
96 """
97 Decorator function to convert A1 notation in cell method calls
98 to the default row/col notation.
100 """
102 @wraps(method)
103 def cell_wrapper(self, *args, **kwargs):
104 try:
105 # First arg is an int, default to row/col notation.
106 if args:
107 first_arg = args[0]
108 int(first_arg)
109 except ValueError:
110 # First arg isn't an int, convert to A1 notation.
111 new_args = xl_cell_to_rowcol(first_arg)
112 args = new_args + args[1:]
114 return method(self, *args, **kwargs)
116 return cell_wrapper
119def convert_range_args(method):
120 """
121 Decorator function to convert A1 notation in range method calls
122 to the default row/col notation.
124 """
126 @wraps(method)
127 def cell_wrapper(self, *args, **kwargs):
128 try:
129 # First arg is an int, default to row/col notation.
130 if args:
131 int(args[0])
132 except ValueError:
133 # First arg isn't an int, convert to A1 notation.
134 if ":" in args[0]:
135 cell_1, cell_2 = args[0].split(":")
136 row_1, col_1 = xl_cell_to_rowcol(cell_1)
137 row_2, col_2 = xl_cell_to_rowcol(cell_2)
138 else:
139 row_1, col_1 = xl_cell_to_rowcol(args[0])
140 row_2, col_2 = row_1, col_1
142 new_args = [row_1, col_1, row_2, col_2]
143 new_args.extend(args[1:])
144 args = new_args
146 return method(self, *args, **kwargs)
148 return cell_wrapper
151def convert_column_args(method):
152 """
153 Decorator function to convert A1 notation in columns method calls
154 to the default row/col notation.
156 """
158 @wraps(method)
159 def column_wrapper(self, *args, **kwargs):
160 try:
161 # First arg is an int, default to row/col notation.
162 if args:
163 int(args[0])
164 except ValueError:
165 # First arg isn't an int, convert to A1 notation.
166 cell_1, cell_2 = [col + "1" for col in args[0].split(":")]
167 _, col_1 = xl_cell_to_rowcol(cell_1)
168 _, col_2 = xl_cell_to_rowcol(cell_2)
169 new_args = [col_1, col_2]
170 new_args.extend(args[1:])
171 args = new_args
173 return method(self, *args, **kwargs)
175 return column_wrapper
178###############################################################################
179#
180# Named tuples used for cell types.
181#
182###############################################################################
183CellBlankTuple = namedtuple("Blank", "format")
184CellErrorTuple = namedtuple("Error", "error, format, value")
185CellNumberTuple = namedtuple("Number", "number, format")
186CellStringTuple = namedtuple("String", "string, format")
187CellBooleanTuple = namedtuple("Boolean", "boolean, format")
188CellFormulaTuple = namedtuple("Formula", "formula, format, value")
189CellDatetimeTuple = namedtuple("Datetime", "number, format")
190CellRichStringTuple = namedtuple("RichString", "string, format, raw_string")
191CellArrayFormulaTuple = namedtuple(
192 "ArrayFormula", "formula, format, value, range, atype"
193)
196###############################################################################
197#
198# Worksheet Class definition.
199#
200###############################################################################
201class Worksheet(xmlwriter.XMLwriter):
202 """
203 A class for writing the Excel XLSX Worksheet file.
205 """
207 ###########################################################################
208 #
209 # Public API.
210 #
211 ###########################################################################
213 def __init__(self) -> None:
214 """
215 Constructor.
217 """
219 super().__init__()
221 self.name = None
222 self.index = None
223 self.str_table = None
224 self.palette = None
225 self.constant_memory = 0
226 self.tmpdir = None
227 self.is_chartsheet = False
229 self.ext_sheets = []
230 self.fileclosed = 0
231 self.excel_version = 2007
232 self.excel2003_style = False
234 self.xls_rowmax = 1048576
235 self.xls_colmax = 16384
236 self.xls_strmax = 32767
237 self.dim_rowmin = None
238 self.dim_rowmax = None
239 self.dim_colmin = None
240 self.dim_colmax = None
242 self.col_info = {}
243 self.selections = []
244 self.hidden = 0
245 self.active = 0
246 self.tab_color = 0
247 self.top_left_cell = ""
249 self.panes = []
250 self.active_pane = 3
251 self.selected = 0
253 self.page_setup_changed = False
254 self.paper_size = 0
255 self.orientation = 1
257 self.print_options_changed = False
258 self.hcenter = False
259 self.vcenter = False
260 self.print_gridlines = False
261 self.screen_gridlines = True
262 self.print_headers = False
263 self.row_col_headers = False
265 self.header_footer_changed = False
266 self.header = ""
267 self.footer = ""
268 self.header_footer_aligns = True
269 self.header_footer_scales = True
270 self.header_images = []
271 self.footer_images = []
272 self.header_images_list = []
274 self.margin_left = 0.7
275 self.margin_right = 0.7
276 self.margin_top = 0.75
277 self.margin_bottom = 0.75
278 self.margin_header = 0.3
279 self.margin_footer = 0.3
281 self.repeat_row_range = ""
282 self.repeat_col_range = ""
283 self.print_area_range = ""
285 self.page_order = 0
286 self.black_white = 0
287 self.draft_quality = 0
288 self.print_comments = 0
289 self.page_start = 0
291 self.fit_page = 0
292 self.fit_width = 0
293 self.fit_height = 0
295 self.hbreaks = []
296 self.vbreaks = []
298 self.protect_options = {}
299 self.protected_ranges = []
300 self.num_protected_ranges = 0
301 self.set_cols = {}
302 self.set_rows = defaultdict(dict)
304 self.zoom = 100
305 self.zoom_scale_normal = 1
306 self.print_scale = 100
307 self.is_right_to_left = False
308 self.show_zeros = 1
309 self.leading_zeros = 0
311 self.outline_row_level = 0
312 self.outline_col_level = 0
313 self.outline_style = 0
314 self.outline_below = 1
315 self.outline_right = 1
316 self.outline_on = 1
317 self.outline_changed = False
319 self.original_row_height = 15
320 self.default_row_height = 15
321 self.default_row_pixels = 20
322 self.default_col_width = 8.43
323 self.default_col_pixels = 64
324 self.default_date_pixels = 68
325 self.default_row_zeroed = 0
327 self.names = {}
328 self.write_match = []
329 self.table = defaultdict(dict)
330 self.merge = []
331 self.merged_cells = {}
332 self.table_cells = {}
333 self.row_spans = {}
335 self.has_vml = False
336 self.has_header_vml = False
337 self.has_comments = False
338 self.comments = defaultdict(dict)
339 self.comments_list = []
340 self.comments_author = ""
341 self.comments_visible = False
342 self.vml_shape_id = 1024
343 self.buttons_list = []
344 self.vml_header_id = 0
346 self.autofilter_area = ""
347 self.autofilter_ref = None
348 self.filter_range = [0, 9]
349 self.filter_on = 0
350 self.filter_cols = {}
351 self.filter_type = {}
352 self.filter_cells = {}
354 self.row_sizes = {}
355 self.col_size_changed = False
356 self.row_size_changed = False
358 self.last_shape_id = 1
359 self.rel_count = 0
360 self.hlink_count = 0
361 self.hlink_refs = []
362 self.external_hyper_links = []
363 self.external_drawing_links = []
364 self.external_comment_links = []
365 self.external_vml_links = []
366 self.external_table_links = []
367 self.external_background_links = []
368 self.drawing_links = []
369 self.vml_drawing_links = []
370 self.charts = []
371 self.images = []
372 self.tables = []
373 self.sparklines = []
374 self.shapes = []
375 self.shape_hash = {}
376 self.drawing = 0
377 self.drawing_rels = {}
378 self.drawing_rels_id = 0
379 self.vml_drawing_rels = {}
380 self.vml_drawing_rels_id = 0
381 self.background_image = None
383 self.rstring = ""
384 self.previous_row = 0
386 self.validations = []
387 self.cond_formats = {}
388 self.data_bars_2010 = []
389 self.use_data_bars_2010 = False
390 self.dxf_priority = 1
391 self.page_view = 0
393 self.vba_codename = None
395 self.date_1904 = False
396 self.hyperlinks = defaultdict(dict)
398 self.strings_to_numbers = False
399 self.strings_to_urls = True
400 self.nan_inf_to_errors = False
401 self.strings_to_formulas = True
403 self.default_date_format = None
404 self.default_url_format = None
405 self.default_checkbox_format = None
406 self.workbook_add_format = None
407 self.remove_timezone = False
408 self.max_url_length = 2079
410 self.row_data_filename = None
411 self.row_data_fh = None
412 self.worksheet_meta = None
413 self.vml_data_id = None
414 self.vml_shape_id = None
416 self.row_data_filename = None
417 self.row_data_fh = None
418 self.row_data_fh_closed = False
420 self.vertical_dpi = 0
421 self.horizontal_dpi = 0
423 self.write_handlers = {}
425 self.ignored_errors = None
427 self.has_dynamic_arrays = False
428 self.use_future_functions = False
429 self.ignore_write_string = False
430 self.embedded_images = None
432 # Utility function for writing different types of strings.
433 def _write_token_as_string(self, token, row: int, col: int, *args):
434 # Map the data to the appropriate write_*() method.
435 if token == "":
436 return self._write_blank(row, col, *args)
438 if self.strings_to_formulas and token.startswith("="):
439 return self._write_formula(row, col, *args)
441 if token.startswith("{=") and token.endswith("}"):
442 return self._write_formula(row, col, *args)
444 # pylint: disable=too-many-boolean-expressions
445 if (
446 ":" in token
447 and self.strings_to_urls
448 and (
449 re.match("(ftp|http)s?://", token)
450 or re.match("mailto:", token)
451 or re.match("(in|ex)ternal:", token)
452 or re.match("file://", token)
453 )
454 ):
455 return self._write_url(row, col, *args)
457 if self.strings_to_numbers:
458 try:
459 f = float(token)
460 if self.nan_inf_to_errors or (not isnan(f) and not isinf(f)):
461 return self._write_number(row, col, f, *args[1:])
462 except ValueError:
463 # Not a number, write as a string.
464 pass
466 return self._write_string(row, col, *args)
468 # We have a plain string.
469 return self._write_string(row, col, *args)
471 @convert_cell_args
472 def write(self, row: int, col: int, *args) -> Union[Literal[0, -1], Any]:
473 """
474 Write data to a worksheet cell by calling the appropriate write_*()
475 method based on the type of data being passed.
477 Args:
478 row: The cell row (zero indexed).
479 col: The cell column (zero indexed).
480 *args: Args to pass to sub functions.
482 Returns:
483 0: Success.
484 -1: Row or column is out of worksheet bounds.
485 other: Return value of called method.
487 """
488 return self._write(row, col, *args)
490 # Undecorated version of write().
491 def _write(self, row: int, col: int, *args):
492 # pylint: disable=raise-missing-from
493 # Check the number of args passed.
494 if not args:
495 raise TypeError("write() takes at least 4 arguments (3 given)")
497 # The first arg should be the token for all write calls.
498 token = args[0]
500 # Avoid isinstance() for better performance.
501 token_type = token.__class__
503 # Check for any user defined type handlers with callback functions.
504 if token_type in self.write_handlers:
505 write_handler = self.write_handlers[token_type]
506 function_return = write_handler(self, row, col, *args)
508 # If the return value is None then the callback has returned
509 # control to this function and we should continue as
510 # normal. Otherwise we return the value to the caller and exit.
511 if function_return is None:
512 pass
513 else:
514 return function_return
516 # Write None as a blank cell.
517 if token is None:
518 return self._write_blank(row, col, *args)
520 # Check for standard Python types.
521 if token_type is bool:
522 return self._write_boolean(row, col, *args)
524 if token_type in (float, int, Decimal, Fraction):
525 return self._write_number(row, col, *args)
527 if token_type is str:
528 return self._write_token_as_string(token, row, col, *args)
530 if token_type in (
531 datetime.datetime,
532 datetime.date,
533 datetime.time,
534 datetime.timedelta,
535 ):
536 return self._write_datetime(row, col, *args)
538 # Resort to isinstance() for subclassed primitives.
540 # Write number types.
541 if isinstance(token, (float, int, Decimal, Fraction)):
542 return self._write_number(row, col, *args)
544 # Write string types.
545 if isinstance(token, str):
546 return self._write_token_as_string(token, row, col, *args)
548 # Write boolean types.
549 if isinstance(token, bool):
550 return self._write_boolean(row, col, *args)
552 # Write datetime objects.
553 if _supported_datetime(token):
554 return self._write_datetime(row, col, *args)
556 # Write Url type.
557 if isinstance(token, Url):
558 return self._write_url(row, col, *args)
560 # We haven't matched a supported type. Try float.
561 try:
562 f = float(token)
563 return self._write_number(row, col, f, *args[1:])
564 except ValueError:
565 pass
566 except TypeError:
567 raise TypeError(f"Unsupported type {type(token)} in write()")
569 # Finally try string.
570 try:
571 str(token)
572 return self._write_string(row, col, *args)
573 except ValueError:
574 raise TypeError(f"Unsupported type {type(token)} in write()")
576 @convert_cell_args
577 def write_string(
578 self, row: int, col: int, string: str, cell_format: Optional[Format] = None
579 ) -> Literal[0, -1, -2]:
580 """
581 Write a string to a worksheet cell.
583 Args:
584 row: The cell row (zero indexed).
585 col: The cell column (zero indexed).
586 string: Cell data. Str.
587 format: An optional cell Format object.
589 Returns:
590 0: Success.
591 -1: Row or column is out of worksheet bounds.
592 -2: String truncated to 32k characters.
594 """
595 return self._write_string(row, col, string, cell_format)
597 # Undecorated version of write_string().
598 def _write_string(
599 self, row: int, col: int, string: str, cell_format: Optional[Format] = None
600 ) -> Literal[0, -1, -2]:
601 str_error = 0
603 # Check that row and col are valid and store max and min values.
604 if self._check_dimensions(row, col):
605 return -1
607 # Check that the string is < 32767 chars.
608 if len(string) > self.xls_strmax:
609 string = string[: self.xls_strmax]
610 str_error = -2
612 # Write a shared string or an in-line string in constant_memory mode.
613 if not self.constant_memory:
614 string_index = self.str_table._get_shared_string_index(string)
615 else:
616 string_index = string
618 # Write previous row if in in-line string constant_memory mode.
619 if self.constant_memory and row > self.previous_row:
620 self._write_single_row(row)
622 # Store the cell data in the worksheet data table.
623 self.table[row][col] = CellStringTuple(string_index, cell_format)
625 return str_error
627 @convert_cell_args
628 def write_number(
629 self,
630 row: int,
631 col: int,
632 number: Union[int, float, Fraction],
633 cell_format: Optional[Format] = None,
634 ) -> Literal[0, -1]:
635 """
636 Write a number to a worksheet cell.
638 Args:
639 row: The cell row (zero indexed).
640 col: The cell column (zero indexed).
641 number: Cell data. Int or float.
642 cell_format: An optional cell Format object.
644 Returns:
645 0: Success.
646 -1: Row or column is out of worksheet bounds.
648 """
649 return self._write_number(row, col, number, cell_format)
651 # Undecorated version of write_number().
652 def _write_number(
653 self,
654 row: int,
655 col: int,
656 number: Union[int, float, Fraction],
657 cell_format: Optional[Format] = None,
658 ) -> Literal[0, -1]:
659 if isnan(number) or isinf(number):
660 if self.nan_inf_to_errors:
661 if isnan(number):
662 return self._write_formula(row, col, "#NUM!", cell_format, "#NUM!")
664 if number == math.inf:
665 return self._write_formula(row, col, "1/0", cell_format, "#DIV/0!")
667 if number == -math.inf:
668 return self._write_formula(row, col, "-1/0", cell_format, "#DIV/0!")
669 else:
670 raise TypeError(
671 "NAN/INF not supported in write_number() "
672 "without 'nan_inf_to_errors' Workbook() option"
673 )
675 if number.__class__ is Fraction:
676 number = float(number)
678 # Check that row and col are valid and store max and min values.
679 if self._check_dimensions(row, col):
680 return -1
682 # Write previous row if in in-line string constant_memory mode.
683 if self.constant_memory and row > self.previous_row:
684 self._write_single_row(row)
686 # Store the cell data in the worksheet data table.
687 self.table[row][col] = CellNumberTuple(number, cell_format)
689 return 0
691 @convert_cell_args
692 def write_blank(
693 self, row: int, col: int, blank: Any, cell_format: Optional[Format] = None
694 ):
695 """
696 Write a blank cell with formatting to a worksheet cell. The blank
697 token is ignored and the format only is written to the cell.
699 Args:
700 row: The cell row (zero indexed).
701 col: The cell column (zero indexed).
702 blank: Any value. It is ignored.
703 cell_format: An optional cell Format object.
705 Returns:
706 0: Success.
707 -1: Row or column is out of worksheet bounds.
709 """
710 return self._write_blank(row, col, blank, cell_format)
712 # Undecorated version of write_blank().
713 def _write_blank(
714 self, row: int, col: int, _, cell_format: Optional[Format] = None
715 ) -> Literal[0, -1]:
716 # Don't write a blank cell unless it has a format.
717 if cell_format is None:
718 return 0
720 # Check that row and col are valid and store max and min values.
721 if self._check_dimensions(row, col):
722 return -1
724 # Write previous row if in in-line string constant_memory mode.
725 if self.constant_memory and row > self.previous_row:
726 self._write_single_row(row)
728 # Store the cell data in the worksheet data table.
729 self.table[row][col] = CellBlankTuple(cell_format)
731 return 0
733 @convert_cell_args
734 def write_formula(
735 self,
736 row: int,
737 col: int,
738 formula: str,
739 cell_format: Optional[Format] = None,
740 value=0,
741 ) -> Literal[0, -1, -2]:
742 """
743 Write a formula to a worksheet cell.
745 Args:
746 row: The cell row (zero indexed).
747 col: The cell column (zero indexed).
748 formula: Cell formula.
749 cell_format: An optional cell Format object.
750 value: An optional value for the formula. Default is 0.
752 Returns:
753 0: Success.
754 -1: Row or column is out of worksheet bounds.
755 -2: Formula can't be None or empty.
757 """
758 # Check that row and col are valid and store max and min values.
759 return self._write_formula(row, col, formula, cell_format, value)
761 # Undecorated version of write_formula().
762 def _write_formula(
763 self,
764 row: int,
765 col: int,
766 formula: str,
767 cell_format: Optional[Format] = None,
768 value=0,
769 ) -> Literal[0, -1, -2]:
770 if self._check_dimensions(row, col):
771 return -1
773 if formula is None or formula == "":
774 warn("Formula can't be None or empty")
775 return -1
777 # Check for dynamic array functions.
778 if re_dynamic_function.search(formula):
779 return self.write_dynamic_array_formula(
780 row, col, row, col, formula, cell_format, value
781 )
783 # Hand off array formulas.
784 if formula.startswith("{") and formula.endswith("}"):
785 return self._write_array_formula(
786 row, col, row, col, formula, cell_format, value
787 )
789 # Modify the formula string, as needed.
790 formula = self._prepare_formula(formula)
792 # Write previous row if in in-line string constant_memory mode.
793 if self.constant_memory and row > self.previous_row:
794 self._write_single_row(row)
796 # Store the cell data in the worksheet data table.
797 self.table[row][col] = CellFormulaTuple(formula, cell_format, value)
799 return 0
801 @convert_range_args
802 def write_array_formula(
803 self,
804 first_row: int,
805 first_col: int,
806 last_row: int,
807 last_col: int,
808 formula: str,
809 cell_format: Optional[Format] = None,
810 value=0,
811 ) -> Literal[0, -1]:
812 """
813 Write a formula to a worksheet cell/range.
815 Args:
816 first_row: The first row of the cell range. (zero indexed).
817 first_col: The first column of the cell range.
818 last_row: The last row of the cell range. (zero indexed).
819 last_col: The last column of the cell range.
820 formula: Cell formula.
821 cell_format: An optional cell Format object.
822 value: An optional value for the formula. Default is 0.
824 Returns:
825 0: Success.
826 -1: Row or column is out of worksheet bounds.
828 """
829 # Check for dynamic array functions.
830 if re_dynamic_function.search(formula):
831 return self.write_dynamic_array_formula(
832 first_row, first_col, last_row, last_col, formula, cell_format, value
833 )
835 return self._write_array_formula(
836 first_row,
837 first_col,
838 last_row,
839 last_col,
840 formula,
841 cell_format,
842 value,
843 "static",
844 )
846 @convert_range_args
847 def write_dynamic_array_formula(
848 self,
849 first_row: int,
850 first_col: int,
851 last_row: int,
852 last_col: int,
853 formula: str,
854 cell_format: Optional[Format] = None,
855 value=0,
856 ) -> Literal[0, -1]:
857 """
858 Write a dynamic array formula to a worksheet cell/range.
860 Args:
861 first_row: The first row of the cell range. (zero indexed).
862 first_col: The first column of the cell range.
863 last_row: The last row of the cell range. (zero indexed).
864 last_col: The last column of the cell range.
865 formula: Cell formula.
866 cell_format: An optional cell Format object.
867 value: An optional value for the formula. Default is 0.
869 Returns:
870 0: Success.
871 -1: Row or column is out of worksheet bounds.
873 """
874 error = self._write_array_formula(
875 first_row,
876 first_col,
877 last_row,
878 last_col,
879 formula,
880 cell_format,
881 value,
882 "dynamic",
883 )
885 if error == 0:
886 self.has_dynamic_arrays = True
888 return error
890 # Utility method to strip equal sign and array braces from a formula and
891 # also expand out future and dynamic array formulas.
892 def _prepare_formula(self, formula, expand_future_functions=False):
893 # Remove array formula braces and the leading =.
894 if formula.startswith("{"):
895 formula = formula[1:]
896 if formula.startswith("="):
897 formula = formula[1:]
898 if formula.endswith("}"):
899 formula = formula[:-1]
901 # Check if formula is already expanded by the user.
902 if "_xlfn." in formula:
903 return formula
905 # Expand dynamic formulas.
906 formula = re.sub(r"\bANCHORARRAY\(", "_xlfn.ANCHORARRAY(", formula)
907 formula = re.sub(r"\bBYCOL\(", "_xlfn.BYCOL(", formula)
908 formula = re.sub(r"\bBYROW\(", "_xlfn.BYROW(", formula)
909 formula = re.sub(r"\bCHOOSECOLS\(", "_xlfn.CHOOSECOLS(", formula)
910 formula = re.sub(r"\bCHOOSEROWS\(", "_xlfn.CHOOSEROWS(", formula)
911 formula = re.sub(r"\bDROP\(", "_xlfn.DROP(", formula)
912 formula = re.sub(r"\bEXPAND\(", "_xlfn.EXPAND(", formula)
913 formula = re.sub(r"\bFILTER\(", "_xlfn._xlws.FILTER(", formula)
914 formula = re.sub(r"\bHSTACK\(", "_xlfn.HSTACK(", formula)
915 formula = re.sub(r"\bLAMBDA\(", "_xlfn.LAMBDA(", formula)
916 formula = re.sub(r"\bMAKEARRAY\(", "_xlfn.MAKEARRAY(", formula)
917 formula = re.sub(r"\bMAP\(", "_xlfn.MAP(", formula)
918 formula = re.sub(r"\bRANDARRAY\(", "_xlfn.RANDARRAY(", formula)
919 formula = re.sub(r"\bREDUCE\(", "_xlfn.REDUCE(", formula)
920 formula = re.sub(r"\bSCAN\(", "_xlfn.SCAN(", formula)
921 formula = re.sub(r"\SINGLE\(", "_xlfn.SINGLE(", formula)
922 formula = re.sub(r"\bSEQUENCE\(", "_xlfn.SEQUENCE(", formula)
923 formula = re.sub(r"\bSORT\(", "_xlfn._xlws.SORT(", formula)
924 formula = re.sub(r"\bSORTBY\(", "_xlfn.SORTBY(", formula)
925 formula = re.sub(r"\bSWITCH\(", "_xlfn.SWITCH(", formula)
926 formula = re.sub(r"\bTAKE\(", "_xlfn.TAKE(", formula)
927 formula = re.sub(r"\bTEXTSPLIT\(", "_xlfn.TEXTSPLIT(", formula)
928 formula = re.sub(r"\bTOCOL\(", "_xlfn.TOCOL(", formula)
929 formula = re.sub(r"\bTOROW\(", "_xlfn.TOROW(", formula)
930 formula = re.sub(r"\bUNIQUE\(", "_xlfn.UNIQUE(", formula)
931 formula = re.sub(r"\bVSTACK\(", "_xlfn.VSTACK(", formula)
932 formula = re.sub(r"\bWRAPCOLS\(", "_xlfn.WRAPCOLS(", formula)
933 formula = re.sub(r"\bWRAPROWS\(", "_xlfn.WRAPROWS(", formula)
934 formula = re.sub(r"\bXLOOKUP\(", "_xlfn.XLOOKUP(", formula)
936 if not self.use_future_functions and not expand_future_functions:
937 return formula
939 formula = re.sub(r"\bACOTH\(", "_xlfn.ACOTH(", formula)
940 formula = re.sub(r"\bACOT\(", "_xlfn.ACOT(", formula)
941 formula = re.sub(r"\bAGGREGATE\(", "_xlfn.AGGREGATE(", formula)
942 formula = re.sub(r"\bARABIC\(", "_xlfn.ARABIC(", formula)
943 formula = re.sub(r"\bARRAYTOTEXT\(", "_xlfn.ARRAYTOTEXT(", formula)
944 formula = re.sub(r"\bBASE\(", "_xlfn.BASE(", formula)
945 formula = re.sub(r"\bBETA.DIST\(", "_xlfn.BETA.DIST(", formula)
946 formula = re.sub(r"\bBETA.INV\(", "_xlfn.BETA.INV(", formula)
947 formula = re.sub(r"\bBINOM.DIST.RANGE\(", "_xlfn.BINOM.DIST.RANGE(", formula)
948 formula = re.sub(r"\bBINOM.DIST\(", "_xlfn.BINOM.DIST(", formula)
949 formula = re.sub(r"\bBINOM.INV\(", "_xlfn.BINOM.INV(", formula)
950 formula = re.sub(r"\bBITAND\(", "_xlfn.BITAND(", formula)
951 formula = re.sub(r"\bBITLSHIFT\(", "_xlfn.BITLSHIFT(", formula)
952 formula = re.sub(r"\bBITOR\(", "_xlfn.BITOR(", formula)
953 formula = re.sub(r"\bBITRSHIFT\(", "_xlfn.BITRSHIFT(", formula)
954 formula = re.sub(r"\bBITXOR\(", "_xlfn.BITXOR(", formula)
955 formula = re.sub(r"\bCEILING.MATH\(", "_xlfn.CEILING.MATH(", formula)
956 formula = re.sub(r"\bCEILING.PRECISE\(", "_xlfn.CEILING.PRECISE(", formula)
957 formula = re.sub(r"\bCHISQ.DIST.RT\(", "_xlfn.CHISQ.DIST.RT(", formula)
958 formula = re.sub(r"\bCHISQ.DIST\(", "_xlfn.CHISQ.DIST(", formula)
959 formula = re.sub(r"\bCHISQ.INV.RT\(", "_xlfn.CHISQ.INV.RT(", formula)
960 formula = re.sub(r"\bCHISQ.INV\(", "_xlfn.CHISQ.INV(", formula)
961 formula = re.sub(r"\bCHISQ.TEST\(", "_xlfn.CHISQ.TEST(", formula)
962 formula = re.sub(r"\bCOMBINA\(", "_xlfn.COMBINA(", formula)
963 formula = re.sub(r"\bCONCAT\(", "_xlfn.CONCAT(", formula)
964 formula = re.sub(r"\bCONFIDENCE.NORM\(", "_xlfn.CONFIDENCE.NORM(", formula)
965 formula = re.sub(r"\bCONFIDENCE.T\(", "_xlfn.CONFIDENCE.T(", formula)
966 formula = re.sub(r"\bCOTH\(", "_xlfn.COTH(", formula)
967 formula = re.sub(r"\bCOT\(", "_xlfn.COT(", formula)
968 formula = re.sub(r"\bCOVARIANCE.P\(", "_xlfn.COVARIANCE.P(", formula)
969 formula = re.sub(r"\bCOVARIANCE.S\(", "_xlfn.COVARIANCE.S(", formula)
970 formula = re.sub(r"\bCSCH\(", "_xlfn.CSCH(", formula)
971 formula = re.sub(r"\bCSC\(", "_xlfn.CSC(", formula)
972 formula = re.sub(r"\bDAYS\(", "_xlfn.DAYS(", formula)
973 formula = re.sub(r"\bDECIMAL\(", "_xlfn.DECIMAL(", formula)
974 formula = re.sub(r"\bERF.PRECISE\(", "_xlfn.ERF.PRECISE(", formula)
975 formula = re.sub(r"\bERFC.PRECISE\(", "_xlfn.ERFC.PRECISE(", formula)
976 formula = re.sub(r"\bEXPON.DIST\(", "_xlfn.EXPON.DIST(", formula)
977 formula = re.sub(r"\bF.DIST.RT\(", "_xlfn.F.DIST.RT(", formula)
978 formula = re.sub(r"\bF.DIST\(", "_xlfn.F.DIST(", formula)
979 formula = re.sub(r"\bF.INV.RT\(", "_xlfn.F.INV.RT(", formula)
980 formula = re.sub(r"\bF.INV\(", "_xlfn.F.INV(", formula)
981 formula = re.sub(r"\bF.TEST\(", "_xlfn.F.TEST(", formula)
982 formula = re.sub(r"\bFILTERXML\(", "_xlfn.FILTERXML(", formula)
983 formula = re.sub(r"\bFLOOR.MATH\(", "_xlfn.FLOOR.MATH(", formula)
984 formula = re.sub(r"\bFLOOR.PRECISE\(", "_xlfn.FLOOR.PRECISE(", formula)
985 formula = re.sub(
986 r"\bFORECAST.ETS.CONFINT\(", "_xlfn.FORECAST.ETS.CONFINT(", formula
987 )
988 formula = re.sub(
989 r"\bFORECAST.ETS.SEASONALITY\(", "_xlfn.FORECAST.ETS.SEASONALITY(", formula
990 )
991 formula = re.sub(r"\bFORECAST.ETS.STAT\(", "_xlfn.FORECAST.ETS.STAT(", formula)
992 formula = re.sub(r"\bFORECAST.ETS\(", "_xlfn.FORECAST.ETS(", formula)
993 formula = re.sub(r"\bFORECAST.LINEAR\(", "_xlfn.FORECAST.LINEAR(", formula)
994 formula = re.sub(r"\bFORMULATEXT\(", "_xlfn.FORMULATEXT(", formula)
995 formula = re.sub(r"\bGAMMA.DIST\(", "_xlfn.GAMMA.DIST(", formula)
996 formula = re.sub(r"\bGAMMA.INV\(", "_xlfn.GAMMA.INV(", formula)
997 formula = re.sub(r"\bGAMMALN.PRECISE\(", "_xlfn.GAMMALN.PRECISE(", formula)
998 formula = re.sub(r"\bGAMMA\(", "_xlfn.GAMMA(", formula)
999 formula = re.sub(r"\bGAUSS\(", "_xlfn.GAUSS(", formula)
1000 formula = re.sub(r"\bHYPGEOM.DIST\(", "_xlfn.HYPGEOM.DIST(", formula)
1001 formula = re.sub(r"\bIFNA\(", "_xlfn.IFNA(", formula)
1002 formula = re.sub(r"\bIFS\(", "_xlfn.IFS(", formula)
1003 formula = re.sub(r"\bIMAGE\(", "_xlfn.IMAGE(", formula)
1004 formula = re.sub(r"\bIMCOSH\(", "_xlfn.IMCOSH(", formula)
1005 formula = re.sub(r"\bIMCOT\(", "_xlfn.IMCOT(", formula)
1006 formula = re.sub(r"\bIMCSCH\(", "_xlfn.IMCSCH(", formula)
1007 formula = re.sub(r"\bIMCSC\(", "_xlfn.IMCSC(", formula)
1008 formula = re.sub(r"\bIMSECH\(", "_xlfn.IMSECH(", formula)
1009 formula = re.sub(r"\bIMSEC\(", "_xlfn.IMSEC(", formula)
1010 formula = re.sub(r"\bIMSINH\(", "_xlfn.IMSINH(", formula)
1011 formula = re.sub(r"\bIMTAN\(", "_xlfn.IMTAN(", formula)
1012 formula = re.sub(r"\bISFORMULA\(", "_xlfn.ISFORMULA(", formula)
1013 formula = re.sub(r"\bISOMITTED\(", "_xlfn.ISOMITTED(", formula)
1014 formula = re.sub(r"\bISOWEEKNUM\(", "_xlfn.ISOWEEKNUM(", formula)
1015 formula = re.sub(r"\bLET\(", "_xlfn.LET(", formula)
1016 formula = re.sub(r"\bLOGNORM.DIST\(", "_xlfn.LOGNORM.DIST(", formula)
1017 formula = re.sub(r"\bLOGNORM.INV\(", "_xlfn.LOGNORM.INV(", formula)
1018 formula = re.sub(r"\bMAXIFS\(", "_xlfn.MAXIFS(", formula)
1019 formula = re.sub(r"\bMINIFS\(", "_xlfn.MINIFS(", formula)
1020 formula = re.sub(r"\bMODE.MULT\(", "_xlfn.MODE.MULT(", formula)
1021 formula = re.sub(r"\bMODE.SNGL\(", "_xlfn.MODE.SNGL(", formula)
1022 formula = re.sub(r"\bMUNIT\(", "_xlfn.MUNIT(", formula)
1023 formula = re.sub(r"\bNEGBINOM.DIST\(", "_xlfn.NEGBINOM.DIST(", formula)
1024 formula = re.sub(r"\bNORM.DIST\(", "_xlfn.NORM.DIST(", formula)
1025 formula = re.sub(r"\bNORM.INV\(", "_xlfn.NORM.INV(", formula)
1026 formula = re.sub(r"\bNORM.S.DIST\(", "_xlfn.NORM.S.DIST(", formula)
1027 formula = re.sub(r"\bNORM.S.INV\(", "_xlfn.NORM.S.INV(", formula)
1028 formula = re.sub(r"\bNUMBERVALUE\(", "_xlfn.NUMBERVALUE(", formula)
1029 formula = re.sub(r"\bPDURATION\(", "_xlfn.PDURATION(", formula)
1030 formula = re.sub(r"\bPERCENTILE.EXC\(", "_xlfn.PERCENTILE.EXC(", formula)
1031 formula = re.sub(r"\bPERCENTILE.INC\(", "_xlfn.PERCENTILE.INC(", formula)
1032 formula = re.sub(r"\bPERCENTRANK.EXC\(", "_xlfn.PERCENTRANK.EXC(", formula)
1033 formula = re.sub(r"\bPERCENTRANK.INC\(", "_xlfn.PERCENTRANK.INC(", formula)
1034 formula = re.sub(r"\bPERMUTATIONA\(", "_xlfn.PERMUTATIONA(", formula)
1035 formula = re.sub(r"\bPHI\(", "_xlfn.PHI(", formula)
1036 formula = re.sub(r"\bPOISSON.DIST\(", "_xlfn.POISSON.DIST(", formula)
1037 formula = re.sub(r"\bQUARTILE.EXC\(", "_xlfn.QUARTILE.EXC(", formula)
1038 formula = re.sub(r"\bQUARTILE.INC\(", "_xlfn.QUARTILE.INC(", formula)
1039 formula = re.sub(r"\bQUERYSTRING\(", "_xlfn.QUERYSTRING(", formula)
1040 formula = re.sub(r"\bRANK.AVG\(", "_xlfn.RANK.AVG(", formula)
1041 formula = re.sub(r"\bRANK.EQ\(", "_xlfn.RANK.EQ(", formula)
1042 formula = re.sub(r"\bRRI\(", "_xlfn.RRI(", formula)
1043 formula = re.sub(r"\bSECH\(", "_xlfn.SECH(", formula)
1044 formula = re.sub(r"\bSEC\(", "_xlfn.SEC(", formula)
1045 formula = re.sub(r"\bSHEETS\(", "_xlfn.SHEETS(", formula)
1046 formula = re.sub(r"\bSHEET\(", "_xlfn.SHEET(", formula)
1047 formula = re.sub(r"\bSKEW.P\(", "_xlfn.SKEW.P(", formula)
1048 formula = re.sub(r"\bSTDEV.P\(", "_xlfn.STDEV.P(", formula)
1049 formula = re.sub(r"\bSTDEV.S\(", "_xlfn.STDEV.S(", formula)
1050 formula = re.sub(r"\bT.DIST.2T\(", "_xlfn.T.DIST.2T(", formula)
1051 formula = re.sub(r"\bT.DIST.RT\(", "_xlfn.T.DIST.RT(", formula)
1052 formula = re.sub(r"\bT.DIST\(", "_xlfn.T.DIST(", formula)
1053 formula = re.sub(r"\bT.INV.2T\(", "_xlfn.T.INV.2T(", formula)
1054 formula = re.sub(r"\bT.INV\(", "_xlfn.T.INV(", formula)
1055 formula = re.sub(r"\bT.TEST\(", "_xlfn.T.TEST(", formula)
1056 formula = re.sub(r"\bTEXTAFTER\(", "_xlfn.TEXTAFTER(", formula)
1057 formula = re.sub(r"\bTEXTBEFORE\(", "_xlfn.TEXTBEFORE(", formula)
1058 formula = re.sub(r"\bTEXTJOIN\(", "_xlfn.TEXTJOIN(", formula)
1059 formula = re.sub(r"\bUNICHAR\(", "_xlfn.UNICHAR(", formula)
1060 formula = re.sub(r"\bUNICODE\(", "_xlfn.UNICODE(", formula)
1061 formula = re.sub(r"\bVALUETOTEXT\(", "_xlfn.VALUETOTEXT(", formula)
1062 formula = re.sub(r"\bVAR.P\(", "_xlfn.VAR.P(", formula)
1063 formula = re.sub(r"\bVAR.S\(", "_xlfn.VAR.S(", formula)
1064 formula = re.sub(r"\bWEBSERVICE\(", "_xlfn.WEBSERVICE(", formula)
1065 formula = re.sub(r"\bWEIBULL.DIST\(", "_xlfn.WEIBULL.DIST(", formula)
1066 formula = re.sub(r"\bXMATCH\(", "_xlfn.XMATCH(", formula)
1067 formula = re.sub(r"\bXOR\(", "_xlfn.XOR(", formula)
1068 formula = re.sub(r"\bZ.TEST\(", "_xlfn.Z.TEST(", formula)
1070 return formula
1072 # Escape/expand table functions. This mainly involves converting Excel 2010
1073 # "@" table ref to 2007 "[#This Row],". We parse the string to avoid
1074 # replacements in string literals within the formula.
1075 @staticmethod
1076 def _prepare_table_formula(formula):
1077 if "@" not in formula:
1078 # No escaping required.
1079 return formula
1081 escaped_formula = []
1082 in_string_literal = False
1084 for char in formula:
1085 # Match the start/end of string literals to avoid escaping
1086 # references in strings.
1087 if char == '"':
1088 in_string_literal = not in_string_literal
1090 # Copy the string literal.
1091 if in_string_literal:
1092 escaped_formula.append(char)
1093 continue
1095 # Replace table reference.
1096 if char == "@":
1097 escaped_formula.append("[#This Row],")
1098 else:
1099 escaped_formula.append(char)
1101 return ("").join(escaped_formula)
1103 # Undecorated version of write_array_formula() and
1104 # write_dynamic_array_formula().
1105 def _write_array_formula(
1106 self,
1107 first_row,
1108 first_col,
1109 last_row,
1110 last_col,
1111 formula,
1112 cell_format=None,
1113 value=0,
1114 atype="static",
1115 ) -> Literal[0, -1]:
1116 # Swap last row/col with first row/col as necessary.
1117 if first_row > last_row:
1118 first_row, last_row = last_row, first_row
1119 if first_col > last_col:
1120 first_col, last_col = last_col, first_col
1122 # Check that row and col are valid and store max and min values.
1123 if self._check_dimensions(first_row, first_col):
1124 return -1
1125 if self._check_dimensions(last_row, last_col):
1126 return -1
1128 # Define array range
1129 if first_row == last_row and first_col == last_col:
1130 cell_range = xl_rowcol_to_cell(first_row, first_col)
1131 else:
1132 cell_range = (
1133 xl_rowcol_to_cell(first_row, first_col)
1134 + ":"
1135 + xl_rowcol_to_cell(last_row, last_col)
1136 )
1138 # Modify the formula string, as needed.
1139 formula = self._prepare_formula(formula)
1141 # Write previous row if in in-line string constant_memory mode.
1142 if self.constant_memory and first_row > self.previous_row:
1143 self._write_single_row(first_row)
1145 # Store the cell data in the worksheet data table.
1146 self.table[first_row][first_col] = CellArrayFormulaTuple(
1147 formula, cell_format, value, cell_range, atype
1148 )
1150 # Pad out the rest of the area with formatted zeroes.
1151 if not self.constant_memory:
1152 for row in range(first_row, last_row + 1):
1153 for col in range(first_col, last_col + 1):
1154 if row != first_row or col != first_col:
1155 self._write_number(row, col, 0, cell_format)
1157 return 0
1159 @convert_cell_args
1160 def write_datetime(
1161 self,
1162 row: int,
1163 col: int,
1164 date: datetime.datetime,
1165 cell_format: Optional[Format] = None,
1166 ) -> Literal[0, -1]:
1167 """
1168 Write a date or time to a worksheet cell.
1170 Args:
1171 row: The cell row (zero indexed).
1172 col: The cell column (zero indexed).
1173 date: Date and/or time as a datetime object.
1174 cell_format: A cell Format object.
1176 Returns:
1177 0: Success.
1178 -1: Row or column is out of worksheet bounds.
1180 """
1181 return self._write_datetime(row, col, date, cell_format)
1183 # Undecorated version of write_datetime().
1184 def _write_datetime(self, row: int, col: int, date, cell_format=None) -> int:
1185 # Check that row and col are valid and store max and min values.
1186 if self._check_dimensions(row, col):
1187 return -1
1189 # Write previous row if in in-line string constant_memory mode.
1190 if self.constant_memory and row > self.previous_row:
1191 self._write_single_row(row)
1193 # Convert datetime to an Excel date.
1194 number = self._convert_date_time(date)
1196 # Add the default date format.
1197 if cell_format is None:
1198 cell_format = self.default_date_format
1200 # Store the cell data in the worksheet data table.
1201 self.table[row][col] = CellDatetimeTuple(number, cell_format)
1203 return 0
1205 @convert_cell_args
1206 def write_boolean(
1207 self, row: int, col: int, boolean: bool, cell_format: Optional[Format] = None
1208 ):
1209 """
1210 Write a boolean value to a worksheet cell.
1212 Args:
1213 row: The cell row (zero indexed).
1214 col: The cell column (zero indexed).
1215 boolean: Cell data. bool type.
1216 cell_format: An optional cell Format object.
1218 Returns:
1219 0: Success.
1220 -1: Row or column is out of worksheet bounds.
1222 """
1223 return self._write_boolean(row, col, boolean, cell_format)
1225 # Undecorated version of write_boolean().
1226 def _write_boolean(self, row: int, col: int, boolean, cell_format=None) -> int:
1227 # Check that row and col are valid and store max and min values.
1228 if self._check_dimensions(row, col):
1229 return -1
1231 # Write previous row if in in-line string constant_memory mode.
1232 if self.constant_memory and row > self.previous_row:
1233 self._write_single_row(row)
1235 if boolean:
1236 value = 1
1237 else:
1238 value = 0
1240 # Store the cell data in the worksheet data table.
1241 self.table[row][col] = CellBooleanTuple(value, cell_format)
1243 return 0
1245 # Write a hyperlink. This is comprised of two elements: the displayed
1246 # string and the non-displayed link. The displayed string is the same as
1247 # the link unless an alternative string is specified. The display string
1248 # is written using the write_string() method. Therefore the max characters
1249 # string limit applies.
1250 #
1251 # The hyperlink can be to a http, ftp, mail, internal sheet, or external
1252 # directory urls.
1253 @convert_cell_args
1254 def write_url(
1255 self,
1256 row: int,
1257 col: int,
1258 url: str,
1259 cell_format: Optional[Format] = None,
1260 string: Optional[str] = None,
1261 tip: Optional[str] = None,
1262 ):
1263 """
1264 Write a hyperlink to a worksheet cell.
1266 Args:
1267 row: The cell row (zero indexed).
1268 col: The cell column (zero indexed).
1269 url: Hyperlink url.
1270 format: An optional cell Format object.
1271 string: An optional display string for the hyperlink.
1272 tip: An optional tooltip.
1273 Returns:
1274 0: Success.
1275 -1: Row or column is out of worksheet bounds.
1276 -2: String longer than 32767 characters.
1277 -3: URL longer than Excel limit of 255 characters.
1278 -4: Exceeds Excel limit of 65,530 urls per worksheet.
1279 """
1280 return self._write_url(row, col, url, cell_format, string, tip)
1282 # Undecorated version of write_url().
1283 def _write_url(
1284 self, row: int, col: int, url, cell_format=None, string=None, tip=None
1285 ) -> int:
1286 # Check that row and col are valid and store max and min values
1287 if self._check_dimensions(row, col):
1288 return -1
1290 # If the URL is a string convert it to a Url object.
1291 if not isinstance(url, Url):
1293 # For backwards compatibility check if the string URL exceeds the
1294 # Excel character limit for URLs and ignore it with a warning.
1295 max_url = self.max_url_length
1296 if "#" in url:
1297 url_str, anchor_str = url.split("#", 1)
1298 else:
1299 url_str = url
1300 anchor_str = ""
1302 if len(url_str) > max_url or len(anchor_str) > max_url:
1303 warn(
1304 f"Ignoring URL '{url}' with link or location/anchor > {max_url} "
1305 f"characters since it exceeds Excel's limit for URLs."
1306 )
1307 return -3
1309 url = Url(url)
1311 if string is not None:
1312 url._text = string
1314 if tip is not None:
1315 url._tip = tip
1317 # Check the limit of URLs per worksheet.
1318 self.hlink_count += 1
1320 if self.hlink_count > 65530:
1321 warn(
1322 f"Ignoring URL '{url._original_url}' since it exceeds Excel's limit of "
1323 f"65,530 URLs per worksheet."
1324 )
1325 return -4
1327 # Add the default URL format.
1328 if cell_format is None:
1329 cell_format = self.default_url_format
1331 if not self.ignore_write_string:
1332 # Write previous row if in in-line string constant_memory mode.
1333 if self.constant_memory and row > self.previous_row:
1334 self._write_single_row(row)
1336 # Write the hyperlink string.
1337 self._write_string(row, col, url.text, cell_format)
1339 # Store the hyperlink data in a separate structure.
1340 self.hyperlinks[row][col] = url
1342 return 0
1344 @convert_cell_args
1345 def write_rich_string(
1346 self, row: int, col: int, *args: Union[str, Format]
1347 ) -> Literal[0, -1, -2, -3, -4, -5]:
1348 """
1349 Write a "rich" string with multiple formats to a worksheet cell.
1351 Args:
1352 row: The cell row (zero indexed).
1353 col: The cell column (zero indexed).
1354 string_parts: String and format pairs.
1355 cell_format: Optional Format object.
1357 Returns:
1358 0: Success.
1359 -1: Row or column is out of worksheet bounds.
1360 -2: String truncated to 32k characters.
1361 -3: 2 consecutive formats used.
1362 -4: Empty string used.
1363 -5: Insufficient parameters.
1365 """
1367 return self._write_rich_string(row, col, *args)
1369 # Undecorated version of write_rich_string().
1370 def _write_rich_string(self, row: int, col: int, *args) -> int:
1371 tokens = list(args)
1372 cell_format = None
1373 string_index = 0
1374 raw_string = ""
1376 # Check that row and col are valid and store max and min values
1377 if self._check_dimensions(row, col):
1378 return -1
1380 # If the last arg is a format we use it as the cell format.
1381 if isinstance(tokens[-1], Format):
1382 cell_format = tokens.pop()
1384 # Create a temp XMLWriter object and use it to write the rich string
1385 # XML to a string.
1386 fh = StringIO()
1387 self.rstring = XMLwriter()
1388 self.rstring._set_filehandle(fh)
1390 # Create a temp format with the default font for unformatted fragments.
1391 default = Format()
1393 # Convert list of format, string tokens to pairs of (format, string)
1394 # except for the first string fragment which doesn't require a default
1395 # formatting run. Use the default for strings without a leading format.
1396 fragments = []
1397 previous = "format"
1398 pos = 0
1400 if len(tokens) <= 2:
1401 warn(
1402 "You must specify more than 2 format/fragments for rich "
1403 "strings. Ignoring input in write_rich_string()."
1404 )
1405 return -5
1407 for token in tokens:
1408 if not isinstance(token, Format):
1409 # Token is a string.
1410 if previous != "format":
1411 # If previous token wasn't a format add one before string.
1412 fragments.append(default)
1413 fragments.append(token)
1414 else:
1415 # If previous token was a format just add the string.
1416 fragments.append(token)
1418 if token == "":
1419 warn(
1420 "Excel doesn't allow empty strings in rich strings. "
1421 "Ignoring input in write_rich_string()."
1422 )
1423 return -4
1425 # Keep track of unformatted string.
1426 raw_string += token
1427 previous = "string"
1428 else:
1429 # Can't allow 2 formats in a row.
1430 if previous == "format" and pos > 0:
1431 warn(
1432 "Excel doesn't allow 2 consecutive formats in rich "
1433 "strings. Ignoring input in write_rich_string()."
1434 )
1435 return -3
1437 # Token is a format object. Add it to the fragment list.
1438 fragments.append(token)
1439 previous = "format"
1441 pos += 1
1443 # If the first token is a string start the <r> element.
1444 if not isinstance(fragments[0], Format):
1445 self.rstring._xml_start_tag("r")
1447 # Write the XML elements for the $format $string fragments.
1448 for token in fragments:
1449 if isinstance(token, Format):
1450 # Write the font run.
1451 self.rstring._xml_start_tag("r")
1452 self._write_font(token)
1453 else:
1454 # Write the string fragment part, with whitespace handling.
1455 attributes = []
1457 if _preserve_whitespace(token):
1458 attributes.append(("xml:space", "preserve"))
1460 self.rstring._xml_data_element("t", token, attributes)
1461 self.rstring._xml_end_tag("r")
1463 # Read the in-memory string.
1464 string = self.rstring.fh.getvalue()
1466 # Check that the string is < 32767 chars.
1467 if len(raw_string) > self.xls_strmax:
1468 warn(
1469 "String length must be less than or equal to Excel's limit "
1470 "of 32,767 characters in write_rich_string()."
1471 )
1472 return -2
1474 # Write a shared string or an in-line string in constant_memory mode.
1475 if not self.constant_memory:
1476 string_index = self.str_table._get_shared_string_index(string)
1477 else:
1478 string_index = string
1480 # Write previous row if in in-line string constant_memory mode.
1481 if self.constant_memory and row > self.previous_row:
1482 self._write_single_row(row)
1484 # Store the cell data in the worksheet data table.
1485 self.table[row][col] = CellRichStringTuple(
1486 string_index, cell_format, raw_string
1487 )
1489 return 0
1491 def add_write_handler(self, user_type, user_function) -> None:
1492 """
1493 Add a callback function to the write() method to handle user defined
1494 types.
1496 Args:
1497 user_type: The user type() to match on.
1498 user_function: The user defined function to write the type data.
1499 Returns:
1500 Nothing.
1502 """
1504 self.write_handlers[user_type] = user_function
1506 @convert_cell_args
1507 def write_row(
1508 self, row: int, col: int, data, cell_format: Optional[Format] = None
1509 ) -> Union[Literal[0], Any]:
1510 """
1511 Write a row of data starting from (row, col).
1513 Args:
1514 row: The cell row (zero indexed).
1515 col: The cell column (zero indexed).
1516 data: A list of tokens to be written with write().
1517 format: An optional cell Format object.
1518 Returns:
1519 0: Success.
1520 other: Return value of write() method.
1522 """
1523 for token in data:
1524 error = self._write(row, col, token, cell_format)
1525 if error:
1526 return error
1527 col += 1
1529 return 0
1531 @convert_cell_args
1532 def write_column(
1533 self, row: int, col: int, data, cell_format: Optional[Format] = None
1534 ) -> Union[Literal[0], Any]:
1535 """
1536 Write a column of data starting from (row, col).
1538 Args:
1539 row: The cell row (zero indexed).
1540 col: The cell column (zero indexed).
1541 data: A list of tokens to be written with write().
1542 format: An optional cell Format object.
1543 Returns:
1544 0: Success.
1545 other: Return value of write() method.
1547 """
1548 for token in data:
1549 error = self._write(row, col, token, cell_format)
1550 if error:
1551 return error
1552 row += 1
1554 return 0
1556 @convert_cell_args
1557 def insert_image(
1558 self,
1559 row: int,
1560 col: int,
1561 source: Union[str, BytesIO, Image],
1562 options: Optional[Dict[str, Any]] = None,
1563 ) -> Literal[0, -1]:
1564 """
1565 Insert an image with its top-left corner in a worksheet cell.
1567 Args:
1568 row: The cell row (zero indexed).
1569 col: The cell column (zero indexed).
1570 source: Filename, BytesIO, or Image object.
1571 options: Position, scale, url and data stream of the image.
1573 Returns:
1574 0: Success.
1575 -1: Row or column is out of worksheet bounds.
1577 """
1578 # Check insert (row, col) without storing.
1579 if self._check_dimensions(row, col, True, True):
1580 warn(f"Cannot insert image at ({row}, {col}).")
1581 return -1
1583 # Convert the source to an Image object.
1584 image = self._image_from_source(source, options)
1586 image._row = row
1587 image._col = col
1588 image._set_user_options(options)
1590 self.images.append(image)
1592 return 0
1594 @convert_cell_args
1595 def embed_image(
1596 self,
1597 row: int,
1598 col: int,
1599 source: Union[str, BytesIO, Image],
1600 options: Optional[Dict[str, Any]] = None,
1601 ) -> Literal[0, -1]:
1602 """
1603 Embed an image in a worksheet cell.
1605 Args:
1606 row: The cell row (zero indexed).
1607 col: The cell column (zero indexed).
1608 source: Filename, BytesIO, or Image object.
1609 options: Url and data stream of the image.
1611 Returns:
1612 0: Success.
1613 -1: Row or column is out of worksheet bounds.
1615 """
1616 # Check insert (row, col) without storing.
1617 if self._check_dimensions(row, col):
1618 warn(f"Cannot embed image at ({row}, {col}).")
1619 return -1
1621 if options is None:
1622 options = {}
1624 # Convert the source to an Image object.
1625 image = self._image_from_source(source, options)
1626 image._set_user_options(options)
1628 cell_format = options.get("cell_format", None)
1630 if image.url:
1631 if cell_format is None:
1632 cell_format = self.default_url_format
1634 self.ignore_write_string = True
1635 self.write_url(row, col, image.url, cell_format)
1636 self.ignore_write_string = False
1638 image_index = self.embedded_images.get_image_index(image)
1640 # Store the cell error and image index in the worksheet data table.
1641 self.table[row][col] = CellErrorTuple("#VALUE!", cell_format, image_index)
1643 return 0
1645 @convert_cell_args
1646 def insert_textbox(
1647 self, row: int, col: int, text: str, options: Optional[Dict[str, Any]] = None
1648 ) -> Literal[0, -1]:
1649 """
1650 Insert an textbox with its top-left corner in a worksheet cell.
1652 Args:
1653 row: The cell row (zero indexed).
1654 col: The cell column (zero indexed).
1655 text: The text for the textbox.
1656 options: Textbox options.
1658 Returns:
1659 0: Success.
1660 -1: Row or column is out of worksheet bounds.
1662 """
1663 # Check insert (row, col) without storing.
1664 if self._check_dimensions(row, col, True, True):
1665 warn(f"Cannot insert textbox at ({row}, {col}).")
1666 return -1
1668 if text is None:
1669 text = ""
1671 if options is None:
1672 options = {}
1674 x_offset = options.get("x_offset", 0)
1675 y_offset = options.get("y_offset", 0)
1676 x_scale = options.get("x_scale", 1)
1677 y_scale = options.get("y_scale", 1)
1678 anchor = options.get("object_position", 1)
1679 description = options.get("description", None)
1680 decorative = options.get("decorative", False)
1682 self.shapes.append(
1683 [
1684 row,
1685 col,
1686 x_offset,
1687 y_offset,
1688 x_scale,
1689 y_scale,
1690 text,
1691 anchor,
1692 options,
1693 description,
1694 decorative,
1695 ]
1696 )
1697 return 0
1699 @convert_cell_args
1700 def insert_chart(
1701 self, row: int, col: int, chart: Chart, options: Optional[Dict[str, Any]] = None
1702 ) -> Literal[0, -1, -2]:
1703 """
1704 Insert an chart with its top-left corner in a worksheet cell.
1706 Args:
1707 row: The cell row (zero indexed).
1708 col: The cell column (zero indexed).
1709 chart: Chart object.
1710 options: Position and scale of the chart.
1712 Returns:
1713 0: Success.
1714 -1: Row or column is out of worksheet bounds.
1716 """
1717 # Check insert (row, col) without storing.
1718 if self._check_dimensions(row, col, True, True):
1719 warn(f"Cannot insert chart at ({row}, {col}).")
1720 return -1
1722 if options is None:
1723 options = {}
1725 # Ensure a chart isn't inserted more than once.
1726 if chart.already_inserted or chart.combined and chart.combined.already_inserted:
1727 warn("Chart cannot be inserted in a worksheet more than once.")
1728 return -2
1730 chart.already_inserted = True
1732 if chart.combined:
1733 chart.combined.already_inserted = True
1735 x_offset = options.get("x_offset", 0)
1736 y_offset = options.get("y_offset", 0)
1737 x_scale = options.get("x_scale", 1)
1738 y_scale = options.get("y_scale", 1)
1739 anchor = options.get("object_position", 1)
1740 description = options.get("description", None)
1741 decorative = options.get("decorative", False)
1743 # Allow Chart to override the scale and offset.
1744 if chart.x_scale != 1:
1745 x_scale = chart.x_scale
1747 if chart.y_scale != 1:
1748 y_scale = chart.y_scale
1750 if chart.x_offset:
1751 x_offset = chart.x_offset
1753 if chart.y_offset:
1754 y_offset = chart.y_offset
1756 self.charts.append(
1757 [
1758 row,
1759 col,
1760 chart,
1761 x_offset,
1762 y_offset,
1763 x_scale,
1764 y_scale,
1765 anchor,
1766 description,
1767 decorative,
1768 ]
1769 )
1770 return 0
1772 @convert_cell_args
1773 def write_comment(
1774 self, row: int, col: int, comment: str, options: Optional[Dict[str, Any]] = None
1775 ) -> Literal[0, -1, -2]:
1776 """
1777 Write a comment to a worksheet cell.
1779 Args:
1780 row: The cell row (zero indexed).
1781 col: The cell column (zero indexed).
1782 comment: Cell comment. Str.
1783 options: Comment formatting options.
1785 Returns:
1786 0: Success.
1787 -1: Row or column is out of worksheet bounds.
1788 -2: String longer than 32k characters.
1790 """
1791 # Check that row and col are valid and store max and min values
1792 if self._check_dimensions(row, col):
1793 return -1
1795 # Check that the comment string is < 32767 chars.
1796 if len(comment) > self.xls_strmax:
1797 return -2
1799 self.has_vml = True
1800 self.has_comments = True
1802 # Store the options of the cell comment, to process on file close.
1803 comment = CommentType(row, col, comment, options)
1804 self.comments[row][col] = comment
1806 return 0
1808 def show_comments(self) -> None:
1809 """
1810 Make any comments in the worksheet visible.
1812 Args:
1813 None.
1815 Returns:
1816 Nothing.
1818 """
1819 self.comments_visible = True
1821 def set_background(
1822 self, source: Union[str, BytesIO, Image], is_byte_stream: bool = False
1823 ) -> Literal[0]:
1824 """
1825 Set a background image for a worksheet.
1827 Args:
1828 source: Filename, BytesIO, or Image object.
1829 is_byte_stream: Deprecated. Use a BytesIO object instead.
1831 Returns:
1832 0: Success.
1834 """
1835 # Convert the source to an Image object.
1836 image = self._image_from_source(source)
1838 self.background_image = image
1840 if is_byte_stream:
1841 warn(
1842 "The `is_byte_stream` parameter in `set_background()` is deprecated. "
1843 "This argument can be omitted if you are using a BytesIO object."
1844 )
1846 return 0
1848 def set_comments_author(self, author) -> None:
1849 """
1850 Set the default author of the cell comments.
1852 Args:
1853 author: Comment author name. String.
1855 Returns:
1856 Nothing.
1858 """
1859 self.comments_author = author
1861 def get_name(self):
1862 """
1863 Retrieve the worksheet name.
1865 Args:
1866 None.
1868 Returns:
1869 Nothing.
1871 """
1872 # There is no set_name() method. Name must be set in add_worksheet().
1873 return self.name
1875 def activate(self) -> None:
1876 """
1877 Set this worksheet as the active worksheet, i.e. the worksheet that is
1878 displayed when the workbook is opened. Also set it as selected.
1880 Note: An active worksheet cannot be hidden.
1882 Args:
1883 None.
1885 Returns:
1886 Nothing.
1888 """
1889 self.hidden = 0
1890 self.selected = 1
1891 self.worksheet_meta.activesheet = self.index
1893 def select(self) -> None:
1894 """
1895 Set current worksheet as a selected worksheet, i.e. the worksheet
1896 has its tab highlighted.
1898 Note: A selected worksheet cannot be hidden.
1900 Args:
1901 None.
1903 Returns:
1904 Nothing.
1906 """
1907 self.selected = 1
1908 self.hidden = 0
1910 def hide(self) -> None:
1911 """
1912 Hide the current worksheet.
1914 Args:
1915 None.
1917 Returns:
1918 Nothing.
1920 """
1921 self.hidden = 1
1923 # A hidden worksheet shouldn't be active or selected.
1924 self.selected = 0
1926 def very_hidden(self) -> None:
1927 """
1928 Hide the current worksheet. This can only be unhidden by VBA.
1930 Args:
1931 None.
1933 Returns:
1934 Nothing.
1936 """
1937 self.hidden = 2
1939 # A hidden worksheet shouldn't be active or selected.
1940 self.selected = 0
1942 def set_first_sheet(self) -> None:
1943 """
1944 Set current worksheet as the first visible sheet. This is necessary
1945 when there are a large number of worksheets and the activated
1946 worksheet is not visible on the screen.
1948 Note: A selected worksheet cannot be hidden.
1950 Args:
1951 None.
1953 Returns:
1954 Nothing.
1956 """
1957 self.hidden = 0 # Active worksheet can't be hidden.
1958 self.worksheet_meta.firstsheet = self.index
1960 @convert_column_args
1961 def set_column(
1962 self,
1963 first_col: int,
1964 last_col: int,
1965 width: Optional[float] = None,
1966 cell_format: Optional[Format] = None,
1967 options: Optional[Dict[str, Any]] = None,
1968 ) -> Literal[0, -1]:
1969 """
1970 Set the width, and other properties of a single column or a
1971 range of columns.
1973 Args:
1974 first_col: First column (zero-indexed).
1975 last_col: Last column (zero-indexed). Can be same as first_col.
1976 width: Column width. (optional).
1977 cell_format: Column cell_format. (optional).
1978 options: Dict of options such as hidden and level.
1980 Returns:
1981 0: Success.
1982 -1: Column number is out of worksheet bounds.
1984 """
1985 if options is None:
1986 options = {}
1988 # Ensure 2nd col is larger than first.
1989 if first_col > last_col:
1990 (first_col, last_col) = (last_col, first_col)
1992 # Don't modify the row dimensions when checking the columns.
1993 ignore_row = True
1995 # Set optional column values.
1996 hidden = options.get("hidden", False)
1997 collapsed = options.get("collapsed", False)
1998 level = options.get("level", 0)
2000 # Store the column dimension only in some conditions.
2001 if cell_format or (width and hidden):
2002 ignore_col = False
2003 else:
2004 ignore_col = True
2006 # Check that each column is valid and store the max and min values.
2007 if self._check_dimensions(0, last_col, ignore_row, ignore_col):
2008 return -1
2009 if self._check_dimensions(0, first_col, ignore_row, ignore_col):
2010 return -1
2012 # Set the limits for the outline levels (0 <= x <= 7).
2013 level = max(level, 0)
2014 level = min(level, 7)
2016 self.outline_col_level = max(self.outline_col_level, level)
2018 # Store the column data.
2019 for col in range(first_col, last_col + 1):
2020 self.col_info[col] = [width, cell_format, hidden, level, collapsed, False]
2022 # Store the column change to allow optimizations.
2023 self.col_size_changed = True
2025 return 0
2027 @convert_column_args
2028 def set_column_pixels(
2029 self,
2030 first_col: int,
2031 last_col: int,
2032 width: Optional[float] = None,
2033 cell_format: Optional[Format] = None,
2034 options: Optional[Dict[str, Any]] = None,
2035 ) -> Literal[0, -1]:
2036 """
2037 Set the width, and other properties of a single column or a
2038 range of columns, where column width is in pixels.
2040 Args:
2041 first_col: First column (zero-indexed).
2042 last_col: Last column (zero-indexed). Can be same as first_col.
2043 width: Column width in pixels. (optional).
2044 cell_format: Column cell_format. (optional).
2045 options: Dict of options such as hidden and level.
2047 Returns:
2048 0: Success.
2049 -1: Column number is out of worksheet bounds.
2051 """
2052 if width is not None:
2053 width = self._pixels_to_width(width)
2055 return self.set_column(first_col, last_col, width, cell_format, options)
2057 def autofit(self, max_width: int = 1790) -> None:
2058 """
2059 Simulate autofit based on the data, and datatypes in each column.
2061 Args:
2062 max_width (optional): max column width to autofit, in pixels.
2064 Returns:
2065 Nothing.
2067 """
2068 # pylint: disable=too-many-nested-blocks
2069 if self.constant_memory:
2070 warn("Autofit is not supported in constant_memory mode.")
2071 return
2073 # No data written to the target sheet; nothing to autofit
2074 if self.dim_rowmax is None:
2075 return
2077 # Store the max pixel width for each column.
2078 col_width_max = {}
2080 # Convert the autofit maximum pixel width to a column/character width,
2081 # but limit it to the Excel max limit.
2082 max_width = min(self._pixels_to_width(max_width), 255.0)
2084 # Create a reverse lookup for the share strings table so we can convert
2085 # the string id back to the original string.
2086 strings = sorted(
2087 self.str_table.string_table, key=self.str_table.string_table.__getitem__
2088 )
2090 for row_num in range(self.dim_rowmin, self.dim_rowmax + 1):
2091 if not self.table.get(row_num):
2092 continue
2094 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
2095 if col_num in self.table[row_num]:
2096 cell = self.table[row_num][col_num]
2097 cell_type = cell.__class__.__name__
2098 length = 0
2100 if cell_type in ("String", "RichString"):
2101 # Handle strings and rich strings.
2102 #
2103 # For standard shared strings we do a reverse lookup
2104 # from the shared string id to the actual string. For
2105 # rich strings we use the unformatted string. We also
2106 # split multi-line strings and handle each part
2107 # separately.
2108 if cell_type == "String":
2109 string_id = cell.string
2110 string = strings[string_id]
2111 else:
2112 string = cell.raw_string
2114 if "\n" not in string:
2115 # Single line string.
2116 length = xl_pixel_width(string)
2117 else:
2118 # Handle multi-line strings.
2119 for string in string.split("\n"):
2120 seg_length = xl_pixel_width(string)
2121 length = max(length, seg_length)
2123 elif cell_type == "Number":
2124 # Handle numbers.
2125 #
2126 # We use a workaround/optimization for numbers since
2127 # digits all have a pixel width of 7. This gives a
2128 # slightly greater width for the decimal place and
2129 # minus sign but only by a few pixels and
2130 # over-estimation is okay.
2131 length = 7 * len(str(cell.number))
2133 elif cell_type == "Datetime":
2134 # Handle dates.
2135 #
2136 # The following uses the default width for mm/dd/yyyy
2137 # dates. It isn't feasible to parse the number format
2138 # to get the actual string width for all format types.
2139 length = self.default_date_pixels
2141 elif cell_type == "Boolean":
2142 # Handle boolean values.
2143 #
2144 # Use the Excel standard widths for TRUE and FALSE.
2145 if cell.boolean:
2146 length = 31
2147 else:
2148 length = 36
2150 elif cell_type in ("Formula", "ArrayFormula"):
2151 # Handle formulas.
2152 #
2153 # We only try to autofit a formula if it has a
2154 # non-zero value.
2155 if isinstance(cell.value, (float, int)):
2156 if cell.value > 0:
2157 length = 7 * len(str(cell.value))
2159 elif isinstance(cell.value, str):
2160 length = xl_pixel_width(cell.value)
2162 elif isinstance(cell.value, bool):
2163 if cell.value:
2164 length = 31
2165 else:
2166 length = 36
2168 # If the cell is in an autofilter header we add an
2169 # additional 16 pixels for the dropdown arrow.
2170 if self.filter_cells.get((row_num, col_num)) and length > 0:
2171 length += 16
2173 # Add the string length to the lookup table.
2174 width_max = col_width_max.get(col_num, 0)
2175 if length > width_max:
2176 col_width_max[col_num] = length
2178 # Apply the width to the column.
2179 for col_num, pixel_width in col_width_max.items():
2180 # Convert the string pixel width to a character width using an
2181 # additional padding of 7 pixels, like Excel.
2182 width = self._pixels_to_width(pixel_width + 7)
2184 # Limit the width to the maximum user or Excel value.
2185 width = min(width, max_width)
2187 # Add the width to an existing col info structure or add a new one.
2188 if self.col_info.get(col_num):
2189 # We only update the width for an existing column if it is
2190 # greater than the user defined value. This allows the user
2191 # to pre-load a minimum col width.
2192 col_info = self.col_info.get(col_num)
2193 user_width = col_info[0]
2194 hidden = col_info[5]
2195 if user_width is not None and not hidden:
2196 # Col info is user defined.
2197 if width > user_width:
2198 self.col_info[col_num][0] = width
2199 self.col_info[col_num][5] = True
2200 else:
2201 self.col_info[col_num][0] = width
2202 self.col_info[col_num][5] = True
2203 else:
2204 self.col_info[col_num] = [width, None, False, 0, False, True]
2206 def set_row(
2207 self,
2208 row: int,
2209 height: Optional[float] = None,
2210 cell_format: Optional[Format] = None,
2211 options: Optional[Dict[str, Any]] = None,
2212 ) -> Literal[0, -1]:
2213 """
2214 Set the width, and other properties of a row.
2216 Args:
2217 row: Row number (zero-indexed).
2218 height: Row height. (optional).
2219 cell_format: Row cell_format. (optional).
2220 options: Dict of options such as hidden, level and collapsed.
2222 Returns:
2223 0: Success.
2224 -1: Row number is out of worksheet bounds.
2226 """
2227 if options is None:
2228 options = {}
2230 # Use minimum col in _check_dimensions().
2231 if self.dim_colmin is not None:
2232 min_col = self.dim_colmin
2233 else:
2234 min_col = 0
2236 # Check that row is valid.
2237 if self._check_dimensions(row, min_col):
2238 return -1
2240 if height is None:
2241 height = self.default_row_height
2243 # Set optional row values.
2244 hidden = options.get("hidden", False)
2245 collapsed = options.get("collapsed", False)
2246 level = options.get("level", 0)
2248 # If the height is 0 the row is hidden and the height is the default.
2249 if height == 0:
2250 hidden = 1
2251 height = self.default_row_height
2253 # Set the limits for the outline levels (0 <= x <= 7).
2254 level = max(level, 0)
2255 level = min(level, 7)
2257 self.outline_row_level = max(self.outline_row_level, level)
2259 # Store the row properties.
2260 self.set_rows[row] = [height, cell_format, hidden, level, collapsed]
2262 # Store the row change to allow optimizations.
2263 self.row_size_changed = True
2265 # Store the row sizes for use when calculating image vertices.
2266 self.row_sizes[row] = [height, hidden]
2268 return 0
2270 def set_row_pixels(
2271 self,
2272 row: int,
2273 height: Optional[float] = None,
2274 cell_format: Optional[Format] = None,
2275 options: Optional[Dict[str, Any]] = None,
2276 ) -> Literal[0, -1]:
2277 """
2278 Set the width (in pixels), and other properties of a row.
2280 Args:
2281 row: Row number (zero-indexed).
2282 height: Row height in pixels. (optional).
2283 cell_format: Row cell_format. (optional).
2284 options: Dict of options such as hidden, level and collapsed.
2286 Returns:
2287 0: Success.
2288 -1: Row number is out of worksheet bounds.
2290 """
2291 if height is not None:
2292 height = self._pixels_to_height(height)
2294 return self.set_row(row, height, cell_format, options)
2296 def set_default_row(
2297 self, height: Optional[float] = None, hide_unused_rows: bool = False
2298 ) -> None:
2299 """
2300 Set the default row properties.
2302 Args:
2303 height: Default height. Optional, defaults to 15.
2304 hide_unused_rows: Hide unused rows. Optional, defaults to False.
2306 Returns:
2307 Nothing.
2309 """
2310 if height is None:
2311 height = self.default_row_height
2313 if height != self.original_row_height:
2314 # Store the row change to allow optimizations.
2315 self.row_size_changed = True
2316 self.default_row_height = height
2318 if hide_unused_rows:
2319 self.default_row_zeroed = 1
2321 @convert_range_args
2322 def merge_range(
2323 self,
2324 first_row: int,
2325 first_col: int,
2326 last_row: int,
2327 last_col: int,
2328 data: Any,
2329 cell_format: Optional[Format] = None,
2330 ) -> int:
2331 """
2332 Merge a range of cells.
2334 Args:
2335 first_row: The first row of the cell range. (zero indexed).
2336 first_col: The first column of the cell range.
2337 last_row: The last row of the cell range. (zero indexed).
2338 last_col: The last column of the cell range.
2339 data: Cell data.
2340 cell_format: Cell Format object.
2342 Returns:
2343 0: Success.
2344 -1: Row or column is out of worksheet bounds.
2345 other: Return value of write().
2347 """
2348 # Merge a range of cells. The first cell should contain the data and
2349 # the others should be blank. All cells should have the same format.
2351 # Excel doesn't allow a single cell to be merged
2352 if first_row == last_row and first_col == last_col:
2353 warn("Can't merge single cell")
2354 return -1
2356 # Swap last row/col with first row/col as necessary
2357 if first_row > last_row:
2358 (first_row, last_row) = (last_row, first_row)
2359 if first_col > last_col:
2360 (first_col, last_col) = (last_col, first_col)
2362 # Check that row and col are valid and store max and min values.
2363 if self._check_dimensions(first_row, first_col):
2364 return -1
2365 if self._check_dimensions(last_row, last_col):
2366 return -1
2368 # Check if the merge range overlaps a previous merged or table range.
2369 # This is a critical file corruption error in Excel.
2370 cell_range = xl_range(first_row, first_col, last_row, last_col)
2371 for row in range(first_row, last_row + 1):
2372 for col in range(first_col, last_col + 1):
2373 if self.merged_cells.get((row, col)):
2374 previous_range = self.merged_cells.get((row, col))
2375 raise OverlappingRange(
2376 f"Merge range '{cell_range}' overlaps previous merge "
2377 f"range '{previous_range}'."
2378 )
2380 if self.table_cells.get((row, col)):
2381 previous_range = self.table_cells.get((row, col))
2382 raise OverlappingRange(
2383 f"Merge range '{cell_range}' overlaps previous table "
2384 f"range '{previous_range}'."
2385 )
2387 self.merged_cells[(row, col)] = cell_range
2389 # Store the merge range.
2390 self.merge.append([first_row, first_col, last_row, last_col])
2392 # Write the first cell
2393 self._write(first_row, first_col, data, cell_format)
2395 # Pad out the rest of the area with formatted blank cells.
2396 for row in range(first_row, last_row + 1):
2397 for col in range(first_col, last_col + 1):
2398 if row == first_row and col == first_col:
2399 continue
2400 self._write_blank(row, col, "", cell_format)
2402 return 0
2404 @convert_range_args
2405 def autofilter(
2406 self, first_row: int, first_col: int, last_row: int, last_col: int
2407 ) -> None:
2408 """
2409 Set the autofilter area in the worksheet.
2411 Args:
2412 first_row: The first row of the cell range. (zero indexed).
2413 first_col: The first column of the cell range.
2414 last_row: The last row of the cell range. (zero indexed).
2415 last_col: The last column of the cell range.
2417 Returns:
2418 Nothing.
2420 """
2421 # Reverse max and min values if necessary.
2422 if last_row < first_row:
2423 (first_row, last_row) = (last_row, first_row)
2424 if last_col < first_col:
2425 (first_col, last_col) = (last_col, first_col)
2427 # Build up the autofilter area range "Sheet1!$A$1:$C$13".
2428 area = self._convert_name_area(first_row, first_col, last_row, last_col)
2429 ref = xl_range(first_row, first_col, last_row, last_col)
2431 self.autofilter_area = area
2432 self.autofilter_ref = ref
2433 self.filter_range = [first_col, last_col]
2435 # Store the filter cell positions for use in the autofit calculation.
2436 for col in range(first_col, last_col + 1):
2437 # Check that the autofilter doesn't overlap a table filter.
2438 if self.filter_cells.get((first_row, col)):
2439 filter_type, filter_range = self.filter_cells.get((first_row, col))
2440 if filter_type == "table":
2441 raise OverlappingRange(
2442 f"Worksheet autofilter range '{ref}' overlaps previous "
2443 f"Table autofilter range '{filter_range}'."
2444 )
2446 self.filter_cells[(first_row, col)] = ("worksheet", ref)
2448 def filter_column(self, col: int, criteria: str) -> None:
2449 """
2450 Set the column filter criteria.
2452 Args:
2453 col: Filter column (zero-indexed).
2454 criteria: Filter criteria.
2456 Returns:
2457 Nothing.
2459 """
2460 if not self.autofilter_area:
2461 warn("Must call autofilter() before filter_column()")
2462 return
2464 # Check for a column reference in A1 notation and substitute.
2465 try:
2466 int(col)
2467 except ValueError:
2468 # Convert col ref to a cell ref and then to a col number.
2469 col_letter = col
2470 (_, col) = xl_cell_to_rowcol(col + "1")
2472 if col >= self.xls_colmax:
2473 warn(f"Invalid column '{col_letter}'")
2474 return
2476 (col_first, col_last) = self.filter_range
2478 # Reject column if it is outside filter range.
2479 if col < col_first or col > col_last:
2480 warn(
2481 f"Column '{col}' outside autofilter() column "
2482 f"range ({col_first}, {col_last})"
2483 )
2484 return
2486 tokens = self._extract_filter_tokens(criteria)
2488 if len(tokens) not in (3, 7):
2489 warn(f"Incorrect number of tokens in criteria '{criteria}'")
2491 tokens = self._parse_filter_expression(criteria, tokens)
2493 # Excel handles single or double custom filters as default filters.
2494 # We need to check for them and handle them accordingly.
2495 if len(tokens) == 2 and tokens[0] == 2:
2496 # Single equality.
2497 self.filter_column_list(col, [tokens[1]])
2498 elif len(tokens) == 5 and tokens[0] == 2 and tokens[2] == 1 and tokens[3] == 2:
2499 # Double equality with "or" operator.
2500 self.filter_column_list(col, [tokens[1], tokens[4]])
2501 else:
2502 # Non default custom filter.
2503 self.filter_cols[col] = tokens
2504 self.filter_type[col] = 0
2506 self.filter_on = 1
2508 def filter_column_list(self, col: int, filters: List[str]) -> None:
2509 """
2510 Set the column filter criteria in Excel 2007 list style.
2512 Args:
2513 col: Filter column (zero-indexed).
2514 filters: List of filter criteria to match.
2516 Returns:
2517 Nothing.
2519 """
2520 if not self.autofilter_area:
2521 warn("Must call autofilter() before filter_column()")
2522 return
2524 # Check for a column reference in A1 notation and substitute.
2525 try:
2526 int(col)
2527 except ValueError:
2528 # Convert col ref to a cell ref and then to a col number.
2529 col_letter = col
2530 (_, col) = xl_cell_to_rowcol(col + "1")
2532 if col >= self.xls_colmax:
2533 warn(f"Invalid column '{col_letter}'")
2534 return
2536 (col_first, col_last) = self.filter_range
2538 # Reject column if it is outside filter range.
2539 if col < col_first or col > col_last:
2540 warn(
2541 f"Column '{col}' outside autofilter() column range "
2542 f"({col_first},{col_last})"
2543 )
2544 return
2546 self.filter_cols[col] = filters
2547 self.filter_type[col] = 1
2548 self.filter_on = 1
2550 @convert_range_args
2551 def data_validation(
2552 self,
2553 first_row: int,
2554 first_col: int,
2555 last_row: int,
2556 last_col: int,
2557 options: Optional[Dict[str, Any]] = None,
2558 ) -> Literal[0, -1, -2]:
2559 """
2560 Add a data validation to a worksheet.
2562 Args:
2563 first_row: The first row of the cell range. (zero indexed).
2564 first_col: The first column of the cell range.
2565 last_row: The last row of the cell range. (zero indexed).
2566 last_col: The last column of the cell range.
2567 options: Data validation options.
2569 Returns:
2570 0: Success.
2571 -1: Row or column is out of worksheet bounds.
2572 -2: Incorrect parameter or option.
2573 """
2574 # Check that row and col are valid without storing the values.
2575 if self._check_dimensions(first_row, first_col, True, True):
2576 return -1
2577 if self._check_dimensions(last_row, last_col, True, True):
2578 return -1
2580 if options is None:
2581 options = {}
2582 else:
2583 # Copy the user defined options so they aren't modified.
2584 options = options.copy()
2586 # Valid input parameters.
2587 valid_parameters = {
2588 "validate",
2589 "criteria",
2590 "value",
2591 "source",
2592 "minimum",
2593 "maximum",
2594 "ignore_blank",
2595 "dropdown",
2596 "show_input",
2597 "input_title",
2598 "input_message",
2599 "show_error",
2600 "error_title",
2601 "error_message",
2602 "error_type",
2603 "other_cells",
2604 "multi_range",
2605 }
2607 # Check for valid input parameters.
2608 for param_key in options.keys():
2609 if param_key not in valid_parameters:
2610 warn(f"Unknown parameter '{param_key}' in data_validation()")
2611 return -2
2613 # Map alternative parameter names 'source' or 'minimum' to 'value'.
2614 if "source" in options:
2615 options["value"] = options["source"]
2616 if "minimum" in options:
2617 options["value"] = options["minimum"]
2619 # 'validate' is a required parameter.
2620 if "validate" not in options:
2621 warn("Parameter 'validate' is required in data_validation()")
2622 return -2
2624 # List of valid validation types.
2625 valid_types = {
2626 "any": "none",
2627 "any value": "none",
2628 "whole number": "whole",
2629 "whole": "whole",
2630 "integer": "whole",
2631 "decimal": "decimal",
2632 "list": "list",
2633 "date": "date",
2634 "time": "time",
2635 "text length": "textLength",
2636 "length": "textLength",
2637 "custom": "custom",
2638 }
2640 # Check for valid validation types.
2641 if options["validate"] not in valid_types:
2642 warn(
2643 f"Unknown validation type '{options['validate']}' for parameter "
2644 f"'validate' in data_validation()"
2645 )
2646 return -2
2648 options["validate"] = valid_types[options["validate"]]
2650 # No action is required for validation type 'any' if there are no
2651 # input messages to display.
2652 if (
2653 options["validate"] == "none"
2654 and options.get("input_title") is None
2655 and options.get("input_message") is None
2656 ):
2657 return -2
2659 # The any, list and custom validations don't have a criteria so we use
2660 # a default of 'between'.
2661 if (
2662 options["validate"] == "none"
2663 or options["validate"] == "list"
2664 or options["validate"] == "custom"
2665 ):
2666 options["criteria"] = "between"
2667 options["maximum"] = None
2669 # 'criteria' is a required parameter.
2670 if "criteria" not in options:
2671 warn("Parameter 'criteria' is required in data_validation()")
2672 return -2
2674 # Valid criteria types.
2675 criteria_types = {
2676 "between": "between",
2677 "not between": "notBetween",
2678 "equal to": "equal",
2679 "=": "equal",
2680 "==": "equal",
2681 "not equal to": "notEqual",
2682 "!=": "notEqual",
2683 "<>": "notEqual",
2684 "greater than": "greaterThan",
2685 ">": "greaterThan",
2686 "less than": "lessThan",
2687 "<": "lessThan",
2688 "greater than or equal to": "greaterThanOrEqual",
2689 ">=": "greaterThanOrEqual",
2690 "less than or equal to": "lessThanOrEqual",
2691 "<=": "lessThanOrEqual",
2692 }
2694 # Check for valid criteria types.
2695 if options["criteria"] not in criteria_types:
2696 warn(
2697 f"Unknown criteria type '{options['criteria']}' for parameter "
2698 f"'criteria' in data_validation()"
2699 )
2700 return -2
2702 options["criteria"] = criteria_types[options["criteria"]]
2704 # 'Between' and 'Not between' criteria require 2 values.
2705 if options["criteria"] == "between" or options["criteria"] == "notBetween":
2706 if "maximum" not in options:
2707 warn(
2708 "Parameter 'maximum' is required in data_validation() "
2709 "when using 'between' or 'not between' criteria"
2710 )
2711 return -2
2712 else:
2713 options["maximum"] = None
2715 # Valid error dialog types.
2716 error_types = {
2717 "stop": 0,
2718 "warning": 1,
2719 "information": 2,
2720 }
2722 # Check for valid error dialog types.
2723 if "error_type" not in options:
2724 options["error_type"] = 0
2725 elif options["error_type"] not in error_types:
2726 warn(
2727 f"Unknown criteria type '{options['error_type']}' "
2728 f"for parameter 'error_type'."
2729 )
2730 return -2
2731 else:
2732 options["error_type"] = error_types[options["error_type"]]
2734 # Convert date/times value if required.
2735 if (
2736 options["validate"] in ("date", "time")
2737 and options["value"]
2738 and _supported_datetime(options["value"])
2739 ):
2740 date_time = self._convert_date_time(options["value"])
2741 # Format date number to the same precision as Excel.
2742 options["value"] = f"{date_time:.16g}"
2744 if options["maximum"] and _supported_datetime(options["maximum"]):
2745 date_time = self._convert_date_time(options["maximum"])
2746 options["maximum"] = f"{date_time:.16g}"
2748 # Check that the input title doesn't exceed the maximum length.
2749 if options.get("input_title") and len(options["input_title"]) > 32:
2750 warn(
2751 f"Length of input title '{options['input_title']}' "
2752 f"exceeds Excel's limit of 32"
2753 )
2754 return -2
2756 # Check that the error title doesn't exceed the maximum length.
2757 if options.get("error_title") and len(options["error_title"]) > 32:
2758 warn(
2759 f"Length of error title '{options['error_title']}' "
2760 f"exceeds Excel's limit of 32"
2761 )
2762 return -2
2764 # Check that the input message doesn't exceed the maximum length.
2765 if options.get("input_message") and len(options["input_message"]) > 255:
2766 warn(
2767 f"Length of input message '{options['input_message']}' "
2768 f"exceeds Excel's limit of 255"
2769 )
2770 return -2
2772 # Check that the error message doesn't exceed the maximum length.
2773 if options.get("error_message") and len(options["error_message"]) > 255:
2774 warn(
2775 f"Length of error message '{options['error_message']}' "
2776 f"exceeds Excel's limit of 255"
2777 )
2778 return -2
2780 # Check that the input list doesn't exceed the maximum length.
2781 if options["validate"] == "list" and isinstance(options["value"], list):
2782 formula = self._csv_join(*options["value"])
2783 if len(formula) > 255:
2784 warn(
2785 f"Length of list items '{formula}' exceeds Excel's limit of "
2786 f"255, use a formula range instead"
2787 )
2788 return -2
2790 # Set some defaults if they haven't been defined by the user.
2791 if "ignore_blank" not in options:
2792 options["ignore_blank"] = 1
2793 if "dropdown" not in options:
2794 options["dropdown"] = 1
2795 if "show_input" not in options:
2796 options["show_input"] = 1
2797 if "show_error" not in options:
2798 options["show_error"] = 1
2800 # These are the cells to which the validation is applied.
2801 options["cells"] = [[first_row, first_col, last_row, last_col]]
2803 # A (for now) undocumented parameter to pass additional cell ranges.
2804 if "other_cells" in options:
2805 options["cells"].extend(options["other_cells"])
2807 # Override with user defined multiple range if provided.
2808 if "multi_range" in options:
2809 options["multi_range"] = options["multi_range"].replace("$", "")
2811 # Store the validation information until we close the worksheet.
2812 self.validations.append(options)
2814 return 0
2816 @convert_range_args
2817 def conditional_format(
2818 self,
2819 first_row: int,
2820 first_col: int,
2821 last_row: int,
2822 last_col: int,
2823 options: Optional[Dict[str, Any]] = None,
2824 ) -> Literal[0, -1, -2]:
2825 """
2826 Add a conditional format to a worksheet.
2828 Args:
2829 first_row: The first row of the cell range. (zero indexed).
2830 first_col: The first column of the cell range.
2831 last_row: The last row of the cell range. (zero indexed).
2832 last_col: The last column of the cell range.
2833 options: Conditional format options.
2835 Returns:
2836 0: Success.
2837 -1: Row or column is out of worksheet bounds.
2838 -2: Incorrect parameter or option.
2839 """
2840 # Check that row and col are valid without storing the values.
2841 if self._check_dimensions(first_row, first_col, True, True):
2842 return -1
2843 if self._check_dimensions(last_row, last_col, True, True):
2844 return -1
2846 if options is None:
2847 options = {}
2848 else:
2849 # Copy the user defined options so they aren't modified.
2850 options = options.copy()
2852 # Valid input parameters.
2853 valid_parameter = {
2854 "type",
2855 "format",
2856 "criteria",
2857 "value",
2858 "minimum",
2859 "maximum",
2860 "stop_if_true",
2861 "min_type",
2862 "mid_type",
2863 "max_type",
2864 "min_value",
2865 "mid_value",
2866 "max_value",
2867 "min_color",
2868 "mid_color",
2869 "max_color",
2870 "min_length",
2871 "max_length",
2872 "multi_range",
2873 "bar_color",
2874 "bar_negative_color",
2875 "bar_negative_color_same",
2876 "bar_solid",
2877 "bar_border_color",
2878 "bar_negative_border_color",
2879 "bar_negative_border_color_same",
2880 "bar_no_border",
2881 "bar_direction",
2882 "bar_axis_position",
2883 "bar_axis_color",
2884 "bar_only",
2885 "data_bar_2010",
2886 "icon_style",
2887 "reverse_icons",
2888 "icons_only",
2889 "icons",
2890 }
2892 # Check for valid input parameters.
2893 for param_key in options.keys():
2894 if param_key not in valid_parameter:
2895 warn(f"Unknown parameter '{param_key}' in conditional_format()")
2896 return -2
2898 # 'type' is a required parameter.
2899 if "type" not in options:
2900 warn("Parameter 'type' is required in conditional_format()")
2901 return -2
2903 # Valid types.
2904 valid_type = {
2905 "cell": "cellIs",
2906 "date": "date",
2907 "time": "time",
2908 "average": "aboveAverage",
2909 "duplicate": "duplicateValues",
2910 "unique": "uniqueValues",
2911 "top": "top10",
2912 "bottom": "top10",
2913 "text": "text",
2914 "time_period": "timePeriod",
2915 "blanks": "containsBlanks",
2916 "no_blanks": "notContainsBlanks",
2917 "errors": "containsErrors",
2918 "no_errors": "notContainsErrors",
2919 "2_color_scale": "2_color_scale",
2920 "3_color_scale": "3_color_scale",
2921 "data_bar": "dataBar",
2922 "formula": "expression",
2923 "icon_set": "iconSet",
2924 }
2926 # Check for valid types.
2927 if options["type"] not in valid_type:
2928 warn(
2929 f"Unknown value '{options['type']}' for parameter 'type' "
2930 f"in conditional_format()"
2931 )
2932 return -2
2934 if options["type"] == "bottom":
2935 options["direction"] = "bottom"
2936 options["type"] = valid_type[options["type"]]
2938 # Valid criteria types.
2939 criteria_type = {
2940 "between": "between",
2941 "not between": "notBetween",
2942 "equal to": "equal",
2943 "=": "equal",
2944 "==": "equal",
2945 "not equal to": "notEqual",
2946 "!=": "notEqual",
2947 "<>": "notEqual",
2948 "greater than": "greaterThan",
2949 ">": "greaterThan",
2950 "less than": "lessThan",
2951 "<": "lessThan",
2952 "greater than or equal to": "greaterThanOrEqual",
2953 ">=": "greaterThanOrEqual",
2954 "less than or equal to": "lessThanOrEqual",
2955 "<=": "lessThanOrEqual",
2956 "containing": "containsText",
2957 "not containing": "notContains",
2958 "begins with": "beginsWith",
2959 "ends with": "endsWith",
2960 "yesterday": "yesterday",
2961 "today": "today",
2962 "last 7 days": "last7Days",
2963 "last week": "lastWeek",
2964 "this week": "thisWeek",
2965 "next week": "nextWeek",
2966 "last month": "lastMonth",
2967 "this month": "thisMonth",
2968 "next month": "nextMonth",
2969 # For legacy, but incorrect, support.
2970 "continue week": "nextWeek",
2971 "continue month": "nextMonth",
2972 }
2974 # Check for valid criteria types.
2975 if "criteria" in options and options["criteria"] in criteria_type:
2976 options["criteria"] = criteria_type[options["criteria"]]
2978 # Convert boolean values if required.
2979 if "value" in options and isinstance(options["value"], bool):
2980 options["value"] = str(options["value"]).upper()
2982 # Convert date/times value if required.
2983 if options["type"] in ("date", "time"):
2984 options["type"] = "cellIs"
2986 if "value" in options:
2987 if not _supported_datetime(options["value"]):
2988 warn("Conditional format 'value' must be a datetime object.")
2989 return -2
2991 date_time = self._convert_date_time(options["value"])
2992 # Format date number to the same precision as Excel.
2993 options["value"] = f"{date_time:.16g}"
2995 if "minimum" in options:
2996 if not _supported_datetime(options["minimum"]):
2997 warn("Conditional format 'minimum' must be a datetime object.")
2998 return -2
3000 date_time = self._convert_date_time(options["minimum"])
3001 options["minimum"] = f"{date_time:.16g}"
3003 if "maximum" in options:
3004 if not _supported_datetime(options["maximum"]):
3005 warn("Conditional format 'maximum' must be a datetime object.")
3006 return -2
3008 date_time = self._convert_date_time(options["maximum"])
3009 options["maximum"] = f"{date_time:.16g}"
3011 # Valid icon styles.
3012 valid_icons = {
3013 "3_arrows": "3Arrows", # 1
3014 "3_flags": "3Flags", # 2
3015 "3_traffic_lights_rimmed": "3TrafficLights2", # 3
3016 "3_symbols_circled": "3Symbols", # 4
3017 "4_arrows": "4Arrows", # 5
3018 "4_red_to_black": "4RedToBlack", # 6
3019 "4_traffic_lights": "4TrafficLights", # 7
3020 "5_arrows_gray": "5ArrowsGray", # 8
3021 "5_quarters": "5Quarters", # 9
3022 "3_arrows_gray": "3ArrowsGray", # 10
3023 "3_traffic_lights": "3TrafficLights", # 11
3024 "3_signs": "3Signs", # 12
3025 "3_symbols": "3Symbols2", # 13
3026 "4_arrows_gray": "4ArrowsGray", # 14
3027 "4_ratings": "4Rating", # 15
3028 "5_arrows": "5Arrows", # 16
3029 "5_ratings": "5Rating",
3030 } # 17
3032 # Set the icon set properties.
3033 if options["type"] == "iconSet":
3034 # An icon_set must have an icon style.
3035 if not options.get("icon_style"):
3036 warn(
3037 "The 'icon_style' parameter must be specified when "
3038 "'type' == 'icon_set' in conditional_format()."
3039 )
3040 return -3
3042 # Check for valid icon styles.
3043 if options["icon_style"] not in valid_icons:
3044 warn(
3045 f"Unknown icon_style '{options['icon_style']}' "
3046 f"in conditional_format()."
3047 )
3048 return -2
3050 options["icon_style"] = valid_icons[options["icon_style"]]
3052 # Set the number of icons for the icon style.
3053 options["total_icons"] = 3
3054 if options["icon_style"].startswith("4"):
3055 options["total_icons"] = 4
3056 elif options["icon_style"].startswith("5"):
3057 options["total_icons"] = 5
3059 options["icons"] = self._set_icon_props(
3060 options.get("total_icons"), options.get("icons")
3061 )
3063 # Swap last row/col for first row/col as necessary
3064 if first_row > last_row:
3065 first_row, last_row = last_row, first_row
3067 if first_col > last_col:
3068 first_col, last_col = last_col, first_col
3070 # Set the formatting range.
3071 cell_range = xl_range(first_row, first_col, last_row, last_col)
3072 start_cell = xl_rowcol_to_cell(first_row, first_col)
3074 # Override with user defined multiple range if provided.
3075 if "multi_range" in options:
3076 cell_range = options["multi_range"]
3077 cell_range = cell_range.replace("$", "")
3079 # Get the dxf format index.
3080 if "format" in options and options["format"]:
3081 options["format"] = options["format"]._get_dxf_index()
3083 # Set the priority based on the order of adding.
3084 options["priority"] = self.dxf_priority
3085 self.dxf_priority += 1
3087 # Check for 2010 style data_bar parameters.
3088 # pylint: disable=too-many-boolean-expressions
3089 if (
3090 self.use_data_bars_2010
3091 or options.get("data_bar_2010")
3092 or options.get("bar_solid")
3093 or options.get("bar_border_color")
3094 or options.get("bar_negative_color")
3095 or options.get("bar_negative_color_same")
3096 or options.get("bar_negative_border_color")
3097 or options.get("bar_negative_border_color_same")
3098 or options.get("bar_no_border")
3099 or options.get("bar_axis_position")
3100 or options.get("bar_axis_color")
3101 or options.get("bar_direction")
3102 ):
3103 options["is_data_bar_2010"] = True
3105 # Special handling of text criteria.
3106 if options["type"] == "text":
3107 value = options["value"]
3108 length = len(value)
3109 criteria = options["criteria"]
3111 if options["criteria"] == "containsText":
3112 options["type"] = "containsText"
3113 options["formula"] = f'NOT(ISERROR(SEARCH("{value}",{start_cell})))'
3114 elif options["criteria"] == "notContains":
3115 options["type"] = "notContainsText"
3116 options["formula"] = f'ISERROR(SEARCH("{value}",{start_cell}))'
3117 elif options["criteria"] == "beginsWith":
3118 options["type"] = "beginsWith"
3119 options["formula"] = f'LEFT({start_cell},{length})="{value}"'
3120 elif options["criteria"] == "endsWith":
3121 options["type"] = "endsWith"
3122 options["formula"] = f'RIGHT({start_cell},{length})="{value}"'
3123 else:
3124 warn(f"Invalid text criteria '{criteria}' in conditional_format()")
3126 # Special handling of time time_period criteria.
3127 if options["type"] == "timePeriod":
3128 if options["criteria"] == "yesterday":
3129 options["formula"] = f"FLOOR({start_cell},1)=TODAY()-1"
3131 elif options["criteria"] == "today":
3132 options["formula"] = f"FLOOR({start_cell},1)=TODAY()"
3134 elif options["criteria"] == "tomorrow":
3135 options["formula"] = f"FLOOR({start_cell},1)=TODAY()+1"
3137 # fmt: off
3138 elif options["criteria"] == "last7Days":
3139 options["formula"] = (
3140 f"AND(TODAY()-FLOOR({start_cell},1)<=6,"
3141 f"FLOOR({start_cell},1)<=TODAY())"
3142 )
3143 # fmt: on
3145 elif options["criteria"] == "lastWeek":
3146 options["formula"] = (
3147 f"AND(TODAY()-ROUNDDOWN({start_cell},0)>=(WEEKDAY(TODAY())),"
3148 f"TODAY()-ROUNDDOWN({start_cell},0)<(WEEKDAY(TODAY())+7))"
3149 )
3151 elif options["criteria"] == "thisWeek":
3152 options["formula"] = (
3153 f"AND(TODAY()-ROUNDDOWN({start_cell},0)<=WEEKDAY(TODAY())-1,"
3154 f"ROUNDDOWN({start_cell},0)-TODAY()<=7-WEEKDAY(TODAY()))"
3155 )
3157 elif options["criteria"] == "nextWeek":
3158 options["formula"] = (
3159 f"AND(ROUNDDOWN({start_cell},0)-TODAY()>(7-WEEKDAY(TODAY())),"
3160 f"ROUNDDOWN({start_cell},0)-TODAY()<(15-WEEKDAY(TODAY())))"
3161 )
3163 elif options["criteria"] == "lastMonth":
3164 options["formula"] = (
3165 f"AND(MONTH({start_cell})=MONTH(TODAY())-1,"
3166 f"OR(YEAR({start_cell})=YEAR("
3167 f"TODAY()),AND(MONTH({start_cell})=1,YEAR(A1)=YEAR(TODAY())-1)))"
3168 )
3170 # fmt: off
3171 elif options["criteria"] == "thisMonth":
3172 options["formula"] = (
3173 f"AND(MONTH({start_cell})=MONTH(TODAY()),"
3174 f"YEAR({start_cell})=YEAR(TODAY()))"
3175 )
3176 # fmt: on
3178 elif options["criteria"] == "nextMonth":
3179 options["formula"] = (
3180 f"AND(MONTH({start_cell})=MONTH(TODAY())+1,"
3181 f"OR(YEAR({start_cell})=YEAR("
3182 f"TODAY()),AND(MONTH({start_cell})=12,"
3183 f"YEAR({start_cell})=YEAR(TODAY())+1)))"
3184 )
3186 else:
3187 warn(
3188 f"Invalid time_period criteria '{options['criteria']}' "
3189 f"in conditional_format()"
3190 )
3192 # Special handling of blanks/error types.
3193 if options["type"] == "containsBlanks":
3194 options["formula"] = f"LEN(TRIM({start_cell}))=0"
3196 if options["type"] == "notContainsBlanks":
3197 options["formula"] = f"LEN(TRIM({start_cell}))>0"
3199 if options["type"] == "containsErrors":
3200 options["formula"] = f"ISERROR({start_cell})"
3202 if options["type"] == "notContainsErrors":
3203 options["formula"] = f"NOT(ISERROR({start_cell}))"
3205 # Special handling for 2 color scale.
3206 if options["type"] == "2_color_scale":
3207 options["type"] = "colorScale"
3209 # Color scales don't use any additional formatting.
3210 options["format"] = None
3212 # Turn off 3 color parameters.
3213 options["mid_type"] = None
3214 options["mid_color"] = None
3216 options.setdefault("min_type", "min")
3217 options.setdefault("max_type", "max")
3218 options.setdefault("min_value", 0)
3219 options.setdefault("max_value", 0)
3220 options.setdefault("min_color", Color("#FF7128"))
3221 options.setdefault("max_color", Color("#FFEF9C"))
3223 options["min_color"] = Color._from_value(options["min_color"])
3224 options["max_color"] = Color._from_value(options["max_color"])
3226 # Special handling for 3 color scale.
3227 if options["type"] == "3_color_scale":
3228 options["type"] = "colorScale"
3230 # Color scales don't use any additional formatting.
3231 options["format"] = None
3233 options.setdefault("min_type", "min")
3234 options.setdefault("mid_type", "percentile")
3235 options.setdefault("max_type", "max")
3236 options.setdefault("min_value", 0)
3237 options.setdefault("max_value", 0)
3238 options.setdefault("min_color", Color("#F8696B"))
3239 options.setdefault("mid_color", Color("#FFEB84"))
3240 options.setdefault("max_color", Color("#63BE7B"))
3242 options["min_color"] = Color._from_value(options["min_color"])
3243 options["mid_color"] = Color._from_value(options["mid_color"])
3244 options["max_color"] = Color._from_value(options["max_color"])
3246 # Set a default mid value.
3247 if "mid_value" not in options:
3248 options["mid_value"] = 50
3250 # Special handling for data bar.
3251 if options["type"] == "dataBar":
3252 # Color scales don't use any additional formatting.
3253 options["format"] = None
3255 if not options.get("min_type"):
3256 options["min_type"] = "min"
3257 options["x14_min_type"] = "autoMin"
3258 else:
3259 options["x14_min_type"] = options["min_type"]
3261 if not options.get("max_type"):
3262 options["max_type"] = "max"
3263 options["x14_max_type"] = "autoMax"
3264 else:
3265 options["x14_max_type"] = options["max_type"]
3267 options.setdefault("min_value", 0)
3268 options.setdefault("max_value", 0)
3269 options.setdefault("bar_color", Color("#638EC6"))
3270 options.setdefault("bar_border_color", options["bar_color"])
3271 options.setdefault("bar_only", False)
3272 options.setdefault("bar_no_border", False)
3273 options.setdefault("bar_solid", False)
3274 options.setdefault("bar_direction", "")
3275 options.setdefault("bar_negative_color", Color("#FF0000"))
3276 options.setdefault("bar_negative_border_color", Color("#FF0000"))
3277 options.setdefault("bar_negative_color_same", False)
3278 options.setdefault("bar_negative_border_color_same", False)
3279 options.setdefault("bar_axis_position", "")
3280 options.setdefault("bar_axis_color", Color("#000000"))
3282 options["bar_color"] = Color._from_value(options["bar_color"])
3283 options["bar_border_color"] = Color._from_value(options["bar_border_color"])
3284 options["bar_axis_color"] = Color._from_value(options["bar_axis_color"])
3285 options["bar_negative_color"] = Color._from_value(
3286 options["bar_negative_color"]
3287 )
3288 options["bar_negative_border_color"] = Color._from_value(
3289 options["bar_negative_border_color"]
3290 )
3292 # Adjust for 2010 style data_bar parameters.
3293 if options.get("is_data_bar_2010"):
3294 self.excel_version = 2010
3296 if options["min_type"] == "min" and options["min_value"] == 0:
3297 options["min_value"] = None
3299 if options["max_type"] == "max" and options["max_value"] == 0:
3300 options["max_value"] = None
3302 options["range"] = cell_range
3304 # Strip the leading = from formulas.
3305 try:
3306 options["min_value"] = options["min_value"].lstrip("=")
3307 except (KeyError, AttributeError):
3308 pass
3309 try:
3310 options["mid_value"] = options["mid_value"].lstrip("=")
3311 except (KeyError, AttributeError):
3312 pass
3313 try:
3314 options["max_value"] = options["max_value"].lstrip("=")
3315 except (KeyError, AttributeError):
3316 pass
3318 # Store the conditional format until we close the worksheet.
3319 if cell_range in self.cond_formats:
3320 self.cond_formats[cell_range].append(options)
3321 else:
3322 self.cond_formats[cell_range] = [options]
3324 return 0
3326 @convert_range_args
3327 def add_table(
3328 self,
3329 first_row: int,
3330 first_col: int,
3331 last_row: int,
3332 last_col: int,
3333 options: Optional[Dict[str, Any]] = None,
3334 ) -> Literal[0, -1, -2, -3]:
3335 """
3336 Add an Excel table to a worksheet.
3338 Args:
3339 first_row: The first row of the cell range. (zero indexed).
3340 first_col: The first column of the cell range.
3341 last_row: The last row of the cell range. (zero indexed).
3342 last_col: The last column of the cell range.
3343 options: Table format options. (Optional)
3345 Returns:
3346 0: Success.
3347 -1: Row or column is out of worksheet bounds.
3348 -2: Incorrect parameter or option.
3349 -3: Not supported in constant_memory mode.
3350 """
3351 table = {}
3352 col_formats = {}
3354 if options is None:
3355 options = {}
3356 else:
3357 # Copy the user defined options so they aren't modified.
3358 options = options.copy()
3360 if self.constant_memory:
3361 warn("add_table() isn't supported in 'constant_memory' mode")
3362 return -3
3364 # Check that row and col are valid without storing the values.
3365 if self._check_dimensions(first_row, first_col, True, True):
3366 return -1
3367 if self._check_dimensions(last_row, last_col, True, True):
3368 return -1
3370 # Swap last row/col for first row/col as necessary.
3371 if first_row > last_row:
3372 (first_row, last_row) = (last_row, first_row)
3373 if first_col > last_col:
3374 (first_col, last_col) = (last_col, first_col)
3376 # Check if the table range overlaps a previous merged or table range.
3377 # This is a critical file corruption error in Excel.
3378 cell_range = xl_range(first_row, first_col, last_row, last_col)
3379 for row in range(first_row, last_row + 1):
3380 for col in range(first_col, last_col + 1):
3381 if self.table_cells.get((row, col)):
3382 previous_range = self.table_cells.get((row, col))
3383 raise OverlappingRange(
3384 f"Table range '{cell_range}' overlaps previous "
3385 f"table range '{previous_range}'."
3386 )
3388 if self.merged_cells.get((row, col)):
3389 previous_range = self.merged_cells.get((row, col))
3390 raise OverlappingRange(
3391 f"Table range '{cell_range}' overlaps previous "
3392 f"merge range '{previous_range}'."
3393 )
3395 self.table_cells[(row, col)] = cell_range
3397 # Valid input parameters.
3398 valid_parameter = {
3399 "autofilter",
3400 "banded_columns",
3401 "banded_rows",
3402 "columns",
3403 "data",
3404 "first_column",
3405 "header_row",
3406 "last_column",
3407 "name",
3408 "style",
3409 "total_row",
3410 }
3412 # Check for valid input parameters.
3413 for param_key in options.keys():
3414 if param_key not in valid_parameter:
3415 warn(f"Unknown parameter '{param_key}' in add_table()")
3416 return -2
3418 # Turn on Excel's defaults.
3419 options["banded_rows"] = options.get("banded_rows", True)
3420 options["header_row"] = options.get("header_row", True)
3421 options["autofilter"] = options.get("autofilter", True)
3423 # Check that there are enough rows.
3424 num_rows = last_row - first_row
3425 if options["header_row"]:
3426 num_rows -= 1
3428 if num_rows < 0:
3429 warn("Must have at least one data row in in add_table()")
3430 return -2
3432 # Set the table options.
3433 table["show_first_col"] = options.get("first_column", False)
3434 table["show_last_col"] = options.get("last_column", False)
3435 table["show_row_stripes"] = options.get("banded_rows", False)
3436 table["show_col_stripes"] = options.get("banded_columns", False)
3437 table["header_row_count"] = options.get("header_row", 0)
3438 table["totals_row_shown"] = options.get("total_row", False)
3440 # Set the table name.
3441 if "name" in options:
3442 name = options["name"]
3443 table["name"] = name
3445 if " " in name:
3446 warn(f"Name '{name}' in add_table() cannot contain spaces")
3447 return -2
3449 # Warn if the name contains invalid chars as defined by Excel.
3450 if not re.match(r"^[\w\\][\w\\.]*$", name, re.UNICODE) or re.match(
3451 r"^\d", name
3452 ):
3453 warn(f"Invalid Excel characters in add_table(): '{name}'")
3454 return -2
3456 # Warn if the name looks like a cell name.
3457 if re.match(r"^[a-zA-Z][a-zA-Z]?[a-dA-D]?\d+$", name):
3458 warn(f"Name looks like a cell name in add_table(): '{name}'")
3459 return -2
3461 # Warn if the name looks like a R1C1 cell reference.
3462 if re.match(r"^[rcRC]$", name) or re.match(r"^[rcRC]\d+[rcRC]\d+$", name):
3463 warn(f"Invalid name '{name}' like a RC cell ref in add_table()")
3464 return -2
3466 # Set the table style.
3467 if "style" in options:
3468 table["style"] = options["style"]
3470 if table["style"] is None:
3471 table["style"] = ""
3473 # Remove whitespace from style name.
3474 table["style"] = table["style"].replace(" ", "")
3475 else:
3476 table["style"] = "TableStyleMedium9"
3478 # Set the data range rows (without the header and footer).
3479 first_data_row = first_row
3480 last_data_row = last_row
3482 if options.get("header_row"):
3483 first_data_row += 1
3485 if options.get("total_row"):
3486 last_data_row -= 1
3488 # Set the table and autofilter ranges.
3489 table["range"] = xl_range(first_row, first_col, last_row, last_col)
3491 table["a_range"] = xl_range(first_row, first_col, last_data_row, last_col)
3493 # If the header row if off the default is to turn autofilter off.
3494 if not options["header_row"]:
3495 options["autofilter"] = 0
3497 # Set the autofilter range.
3498 if options["autofilter"]:
3499 table["autofilter"] = table["a_range"]
3501 # Add the table columns.
3502 col_id = 1
3503 table["columns"] = []
3504 seen_names = {}
3506 for col_num in range(first_col, last_col + 1):
3507 # Set up the default column data.
3508 col_data = {
3509 "id": col_id,
3510 "name": "Column" + str(col_id),
3511 "total_string": "",
3512 "total_function": "",
3513 "custom_total": "",
3514 "total_value": 0,
3515 "formula": "",
3516 "format": None,
3517 "name_format": None,
3518 }
3520 # Overwrite the defaults with any user defined values.
3521 if "columns" in options:
3522 # Check if there are user defined values for this column.
3523 if col_id <= len(options["columns"]):
3524 user_data = options["columns"][col_id - 1]
3525 else:
3526 user_data = None
3528 if user_data:
3529 # Get the column format.
3530 xformat = user_data.get("format", None)
3532 # Map user defined values to internal values.
3533 if user_data.get("header"):
3534 col_data["name"] = user_data["header"]
3536 # Excel requires unique case insensitive header names.
3537 header_name = col_data["name"]
3538 name = header_name.lower()
3539 if name in seen_names:
3540 warn(f"Duplicate header name in add_table(): '{name}'")
3541 return -2
3543 seen_names[name] = True
3545 col_data["name_format"] = user_data.get("header_format")
3547 # Handle the column formula.
3548 if "formula" in user_data and user_data["formula"]:
3549 formula = user_data["formula"]
3551 # Remove the formula '=' sign if it exists.
3552 if formula.startswith("="):
3553 formula = formula.lstrip("=")
3555 # Convert Excel 2010 "@" ref to 2007 "#This Row".
3556 formula = self._prepare_table_formula(formula)
3558 # Escape any future functions.
3559 formula = self._prepare_formula(formula, True)
3561 col_data["formula"] = formula
3562 # We write the formulas below after the table data.
3564 # Handle the function for the total row.
3565 if user_data.get("total_function"):
3566 function = user_data["total_function"]
3567 if function == "count_nums":
3568 function = "countNums"
3569 if function == "std_dev":
3570 function = "stdDev"
3572 subtotals = set(
3573 [
3574 "average",
3575 "countNums",
3576 "count",
3577 "max",
3578 "min",
3579 "stdDev",
3580 "sum",
3581 "var",
3582 ]
3583 )
3585 if function in subtotals:
3586 formula = self._table_function_to_formula(
3587 function, col_data["name"]
3588 )
3589 else:
3590 formula = self._prepare_formula(function, True)
3591 col_data["custom_total"] = formula
3592 function = "custom"
3594 col_data["total_function"] = function
3596 value = user_data.get("total_value", 0)
3598 self._write_formula(last_row, col_num, formula, xformat, value)
3600 elif user_data.get("total_string"):
3601 # Total label only (not a function).
3602 total_string = user_data["total_string"]
3603 col_data["total_string"] = total_string
3605 self._write_string(
3606 last_row, col_num, total_string, user_data.get("format")
3607 )
3609 # Get the dxf format index.
3610 if xformat is not None:
3611 col_data["format"] = xformat._get_dxf_index()
3613 # Store the column format for writing the cell data.
3614 # It doesn't matter if it is undefined.
3615 col_formats[col_id - 1] = xformat
3617 # Store the column data.
3618 table["columns"].append(col_data)
3620 # Write the column headers to the worksheet.
3621 if options["header_row"]:
3622 self._write_string(
3623 first_row, col_num, col_data["name"], col_data["name_format"]
3624 )
3626 col_id += 1
3628 # Write the cell data if supplied.
3629 if "data" in options:
3630 data = options["data"]
3632 i = 0 # For indexing the row data.
3633 for row in range(first_data_row, last_data_row + 1):
3634 j = 0 # For indexing the col data.
3635 for col in range(first_col, last_col + 1):
3636 if i < len(data) and j < len(data[i]):
3637 token = data[i][j]
3638 if j in col_formats:
3639 self._write(row, col, token, col_formats[j])
3640 else:
3641 self._write(row, col, token, None)
3642 j += 1
3643 i += 1
3645 # Write any columns formulas after the user supplied table data to
3646 # overwrite it if required.
3647 for col_id, col_num in enumerate(range(first_col, last_col + 1)):
3648 column_data = table["columns"][col_id]
3649 if column_data and column_data["formula"]:
3650 formula_format = col_formats.get(col_id)
3651 formula = column_data["formula"]
3653 for row in range(first_data_row, last_data_row + 1):
3654 self._write_formula(row, col_num, formula, formula_format)
3656 # Store the table data.
3657 self.tables.append(table)
3659 # Store the filter cell positions for use in the autofit calculation.
3660 if options["autofilter"]:
3661 for col in range(first_col, last_col + 1):
3662 # Check that the table autofilter doesn't overlap a worksheet filter.
3663 if self.filter_cells.get((first_row, col)):
3664 filter_type, filter_range = self.filter_cells.get((first_row, col))
3665 if filter_type == "worksheet":
3666 raise OverlappingRange(
3667 f"Table autofilter range '{cell_range}' overlaps previous "
3668 f"Worksheet autofilter range '{filter_range}'."
3669 )
3671 self.filter_cells[(first_row, col)] = ("table", cell_range)
3673 return 0
3675 @convert_cell_args
3676 def add_sparkline(
3677 self, row: int, col: int, options: Optional[Dict[str, Any]] = None
3678 ) -> Literal[0, -1, -2]:
3679 """
3680 Add sparklines to the worksheet.
3682 Args:
3683 row: The cell row (zero indexed).
3684 col: The cell column (zero indexed).
3685 options: Sparkline formatting options.
3687 Returns:
3688 0: Success.
3689 -1: Row or column is out of worksheet bounds.
3690 -2: Incorrect parameter or option.
3692 """
3694 # Check that row and col are valid without storing the values.
3695 if self._check_dimensions(row, col, True, True):
3696 return -1
3698 sparkline = {"locations": [xl_rowcol_to_cell(row, col)]}
3700 if options is None:
3701 options = {}
3703 # Valid input parameters.
3704 valid_parameters = {
3705 "location",
3706 "range",
3707 "type",
3708 "high_point",
3709 "low_point",
3710 "negative_points",
3711 "first_point",
3712 "last_point",
3713 "markers",
3714 "style",
3715 "series_color",
3716 "negative_color",
3717 "markers_color",
3718 "first_color",
3719 "last_color",
3720 "high_color",
3721 "low_color",
3722 "max",
3723 "min",
3724 "axis",
3725 "reverse",
3726 "empty_cells",
3727 "show_hidden",
3728 "plot_hidden",
3729 "date_axis",
3730 "weight",
3731 }
3733 # Check for valid input parameters.
3734 for param_key in options.keys():
3735 if param_key not in valid_parameters:
3736 warn(f"Unknown parameter '{param_key}' in add_sparkline()")
3737 return -1
3739 # 'range' is a required parameter.
3740 if "range" not in options:
3741 warn("Parameter 'range' is required in add_sparkline()")
3742 return -2
3744 # Handle the sparkline type.
3745 spark_type = options.get("type", "line")
3747 if spark_type not in ("line", "column", "win_loss"):
3748 warn(
3749 "Parameter 'type' must be 'line', 'column' "
3750 "or 'win_loss' in add_sparkline()"
3751 )
3752 return -2
3754 if spark_type == "win_loss":
3755 spark_type = "stacked"
3756 sparkline["type"] = spark_type
3758 # We handle single location/range values or list of values.
3759 if "location" in options:
3760 if isinstance(options["location"], list):
3761 sparkline["locations"] = options["location"]
3762 else:
3763 sparkline["locations"] = [options["location"]]
3765 if isinstance(options["range"], list):
3766 sparkline["ranges"] = options["range"]
3767 else:
3768 sparkline["ranges"] = [options["range"]]
3770 range_count = len(sparkline["ranges"])
3771 location_count = len(sparkline["locations"])
3773 # The ranges and locations must match.
3774 if range_count != location_count:
3775 warn(
3776 "Must have the same number of location and range "
3777 "parameters in add_sparkline()"
3778 )
3779 return -2
3781 # Store the count.
3782 sparkline["count"] = len(sparkline["locations"])
3784 # Get the worksheet name for the range conversion below.
3785 sheetname = quote_sheetname(self.name)
3787 # Cleanup the input ranges.
3788 new_ranges = []
3789 for spark_range in sparkline["ranges"]:
3790 # Remove the absolute reference $ symbols.
3791 spark_range = spark_range.replace("$", "")
3793 # Remove the = from formula.
3794 spark_range = spark_range.lstrip("=")
3796 # Convert a simple range into a full Sheet1!A1:D1 range.
3797 if "!" not in spark_range:
3798 spark_range = sheetname + "!" + spark_range
3800 new_ranges.append(spark_range)
3802 sparkline["ranges"] = new_ranges
3804 # Cleanup the input locations.
3805 new_locations = []
3806 for location in sparkline["locations"]:
3807 location = location.replace("$", "")
3808 new_locations.append(location)
3810 sparkline["locations"] = new_locations
3812 # Map options.
3813 sparkline["high"] = options.get("high_point")
3814 sparkline["low"] = options.get("low_point")
3815 sparkline["negative"] = options.get("negative_points")
3816 sparkline["first"] = options.get("first_point")
3817 sparkline["last"] = options.get("last_point")
3818 sparkline["markers"] = options.get("markers")
3819 sparkline["min"] = options.get("min")
3820 sparkline["max"] = options.get("max")
3821 sparkline["axis"] = options.get("axis")
3822 sparkline["reverse"] = options.get("reverse")
3823 sparkline["hidden"] = options.get("show_hidden")
3824 sparkline["weight"] = options.get("weight")
3826 # Map empty cells options.
3827 empty = options.get("empty_cells", "")
3829 if empty == "zero":
3830 sparkline["empty"] = 0
3831 elif empty == "connect":
3832 sparkline["empty"] = "span"
3833 else:
3834 sparkline["empty"] = "gap"
3836 # Map the date axis range.
3837 date_range = options.get("date_axis")
3839 if date_range and "!" not in date_range:
3840 date_range = sheetname + "!" + date_range
3842 sparkline["date_axis"] = date_range
3844 # Set the sparkline styles.
3845 style_id = options.get("style", 0)
3846 style = _get_sparkline_style(style_id)
3848 sparkline["series_color"] = style["series"]
3849 sparkline["negative_color"] = style["negative"]
3850 sparkline["markers_color"] = style["markers"]
3851 sparkline["first_color"] = style["first"]
3852 sparkline["last_color"] = style["last"]
3853 sparkline["high_color"] = style["high"]
3854 sparkline["low_color"] = style["low"]
3856 # Override the style colors with user defined colors.
3857 self._set_spark_color(sparkline, options, "series_color")
3858 self._set_spark_color(sparkline, options, "negative_color")
3859 self._set_spark_color(sparkline, options, "markers_color")
3860 self._set_spark_color(sparkline, options, "first_color")
3861 self._set_spark_color(sparkline, options, "last_color")
3862 self._set_spark_color(sparkline, options, "high_color")
3863 self._set_spark_color(sparkline, options, "low_color")
3865 self.sparklines.append(sparkline)
3867 return 0
3869 @convert_range_args
3870 def set_selection(
3871 self, first_row: int, first_col: int, last_row: int, last_col: int
3872 ) -> None:
3873 """
3874 Set the selected cell or cells in a worksheet
3876 Args:
3877 first_row: The first row of the cell range. (zero indexed).
3878 first_col: The first column of the cell range.
3879 last_row: The last row of the cell range. (zero indexed).
3880 last_col: The last column of the cell range.
3882 Returns:
3883 0: Nothing.
3884 """
3885 pane = None
3887 # Range selection. Do this before swapping max/min to allow the
3888 # selection direction to be reversed.
3889 active_cell = xl_rowcol_to_cell(first_row, first_col)
3891 # Swap last row/col for first row/col if necessary
3892 if first_row > last_row:
3893 (first_row, last_row) = (last_row, first_row)
3895 if first_col > last_col:
3896 (first_col, last_col) = (last_col, first_col)
3898 sqref = xl_range(first_row, first_col, last_row, last_col)
3900 # Selection isn't set for cell A1.
3901 if sqref == "A1":
3902 return
3904 self.selections = [[pane, active_cell, sqref]]
3906 @convert_cell_args
3907 def set_top_left_cell(self, row: int = 0, col: int = 0) -> None:
3908 """
3909 Set the first visible cell at the top left of a worksheet.
3911 Args:
3912 row: The cell row (zero indexed).
3913 col: The cell column (zero indexed).
3915 Returns:
3916 0: Nothing.
3917 """
3919 if row == 0 and col == 0:
3920 return
3922 self.top_left_cell = xl_rowcol_to_cell(row, col)
3924 def outline_settings(
3925 self,
3926 visible: bool = 1,
3927 symbols_below: bool = 1,
3928 symbols_right: bool = 1,
3929 auto_style: bool = 0,
3930 ) -> None:
3931 """
3932 Control outline settings.
3934 Args:
3935 visible: Outlines are visible. Optional, defaults to True.
3936 symbols_below: Show row outline symbols below the outline bar.
3937 Optional, defaults to True.
3938 symbols_right: Show column outline symbols to the right of the
3939 outline bar. Optional, defaults to True.
3940 auto_style: Use Automatic style. Optional, defaults to False.
3942 Returns:
3943 0: Nothing.
3944 """
3945 self.outline_on = visible
3946 self.outline_below = symbols_below
3947 self.outline_right = symbols_right
3948 self.outline_style = auto_style
3950 self.outline_changed = True
3952 @convert_cell_args
3953 def freeze_panes(
3954 self,
3955 row: int,
3956 col: int,
3957 top_row: Optional[int] = None,
3958 left_col: Optional[int] = None,
3959 pane_type: int = 0,
3960 ) -> None:
3961 """
3962 Create worksheet panes and mark them as frozen.
3964 Args:
3965 row: The cell row (zero indexed).
3966 col: The cell column (zero indexed).
3967 top_row: Topmost visible row in scrolling region of pane.
3968 left_col: Leftmost visible row in scrolling region of pane.
3970 Returns:
3971 0: Nothing.
3973 """
3974 if top_row is None:
3975 top_row = row
3977 if left_col is None:
3978 left_col = col
3980 self.panes = [row, col, top_row, left_col, pane_type]
3982 @convert_cell_args
3983 def split_panes(
3984 self,
3985 x: float,
3986 y: float,
3987 top_row: Optional[int] = None,
3988 left_col: Optional[int] = None,
3989 ) -> None:
3990 """
3991 Create worksheet panes and mark them as split.
3993 Args:
3994 x: The position for the vertical split.
3995 y: The position for the horizontal split.
3996 top_row: Topmost visible row in scrolling region of pane.
3997 left_col: Leftmost visible row in scrolling region of pane.
3999 Returns:
4000 0: Nothing.
4002 """
4003 # Same as freeze panes with a different pane type.
4004 self.freeze_panes(x, y, top_row, left_col, 2)
4006 def set_zoom(self, zoom: int = 100) -> None:
4007 """
4008 Set the worksheet zoom factor.
4010 Args:
4011 zoom: Scale factor: 10 <= zoom <= 400.
4013 Returns:
4014 Nothing.
4016 """
4017 # Ensure the zoom scale is in Excel's range.
4018 if zoom < 10 or zoom > 400:
4019 warn(f"Zoom factor '{zoom}' outside range: 10 <= zoom <= 400")
4020 zoom = 100
4022 self.zoom = int(zoom)
4024 def right_to_left(self) -> None:
4025 """
4026 Display the worksheet right to left for some versions of Excel.
4028 Args:
4029 None.
4031 Returns:
4032 Nothing.
4034 """
4035 self.is_right_to_left = True
4037 def hide_zero(self) -> None:
4038 """
4039 Hide zero values in worksheet cells.
4041 Args:
4042 None.
4044 Returns:
4045 Nothing.
4047 """
4048 self.show_zeros = 0
4050 def set_tab_color(self, color: Union[str, Color]) -> None:
4051 """
4052 Set the color of the worksheet tab.
4054 Args:
4055 color: A #RGB color index.
4057 Returns:
4058 Nothing.
4060 """
4061 self.tab_color = Color._from_value(color)
4063 def protect(
4064 self, password: str = "", options: Optional[Dict[str, Any]] = None
4065 ) -> None:
4066 """
4067 Set the password and protection options of the worksheet.
4069 Args:
4070 password: An optional password string.
4071 options: A dictionary of worksheet objects to protect.
4073 Returns:
4074 Nothing.
4076 """
4077 if password != "":
4078 password = self._encode_password(password)
4080 if not options:
4081 options = {}
4083 # Default values for objects that can be protected.
4084 defaults = {
4085 "sheet": True,
4086 "content": False,
4087 "objects": False,
4088 "scenarios": False,
4089 "format_cells": False,
4090 "format_columns": False,
4091 "format_rows": False,
4092 "insert_columns": False,
4093 "insert_rows": False,
4094 "insert_hyperlinks": False,
4095 "delete_columns": False,
4096 "delete_rows": False,
4097 "select_locked_cells": True,
4098 "sort": False,
4099 "autofilter": False,
4100 "pivot_tables": False,
4101 "select_unlocked_cells": True,
4102 }
4104 # Overwrite the defaults with user specified values.
4105 for key in options.keys():
4106 if key in defaults:
4107 defaults[key] = options[key]
4108 else:
4109 warn(f"Unknown protection object: '{key}'")
4111 # Set the password after the user defined values.
4112 defaults["password"] = password
4114 self.protect_options = defaults
4116 def unprotect_range(
4117 self,
4118 cell_range: str,
4119 range_name: Optional[str] = None,
4120 password: Optional[str] = None,
4121 ) -> int:
4122 """
4123 Unprotect ranges within a protected worksheet.
4125 Args:
4126 cell_range: The cell or cell range to unprotect.
4127 range_name: An optional name for the range.
4128 password: An optional password string. (undocumented)
4130 Returns:
4131 0: Success.
4132 -1: Parameter error.
4134 """
4135 if cell_range is None:
4136 warn("Cell range must be specified in unprotect_range()")
4137 return -1
4139 # Sanitize the cell range.
4140 cell_range = cell_range.lstrip("=")
4141 cell_range = cell_range.replace("$", "")
4143 self.num_protected_ranges += 1
4145 if range_name is None:
4146 range_name = "Range" + str(self.num_protected_ranges)
4148 if password:
4149 password = self._encode_password(password)
4151 self.protected_ranges.append((cell_range, range_name, password))
4153 return 0
4155 @convert_cell_args
4156 def insert_button(
4157 self, row: int, col: int, options: Optional[Dict[str, Any]] = None
4158 ) -> Literal[0, -1]:
4159 """
4160 Insert a button form object into the worksheet.
4162 Args:
4163 row: The cell row (zero indexed).
4164 col: The cell column (zero indexed).
4165 options: Button formatting options.
4167 Returns:
4168 0: Success.
4169 -1: Row or column is out of worksheet bounds.
4171 """
4172 # Check insert (row, col) without storing.
4173 if self._check_dimensions(row, col, True, True):
4174 warn(f"Cannot insert button at ({row}, {col}).")
4175 return -1
4177 if options is None:
4178 options = {}
4180 # Create a new button object.
4181 height = self.default_row_pixels
4182 width = self.default_col_pixels
4183 button_number = 1 + len(self.buttons_list)
4185 button = ButtonType(row, col, height, width, button_number, options)
4187 self.buttons_list.append(button)
4189 self.has_vml = True
4191 return 0
4193 @convert_cell_args
4194 def insert_checkbox(
4195 self, row: int, col: int, boolean: bool, cell_format: Optional[Format] = None
4196 ):
4197 """
4198 Insert a boolean checkbox in a worksheet cell.
4200 Args:
4201 row: The cell row (zero indexed).
4202 col: The cell column (zero indexed).
4203 boolean: The boolean value to display as a checkbox.
4204 cell_format: Cell Format object. (optional)
4206 Returns:
4207 0: Success.
4208 -1: Row or column is out of worksheet bounds.
4210 """
4211 # Ensure that the checkbox property is set in the user defined format.
4212 if cell_format and not cell_format.checkbox:
4213 # This needs to be fixed with a clone.
4214 cell_format.set_checkbox()
4216 # If no format is supplied create and/or use the default checkbox format.
4217 if not cell_format:
4218 if not self.default_checkbox_format:
4219 self.default_checkbox_format = self.workbook_add_format()
4220 self.default_checkbox_format.set_checkbox()
4222 cell_format = self.default_checkbox_format
4224 return self._write_boolean(row, col, boolean, cell_format)
4226 ###########################################################################
4227 #
4228 # Public API. Page Setup methods.
4229 #
4230 ###########################################################################
4231 def set_landscape(self) -> None:
4232 """
4233 Set the page orientation as landscape.
4235 Args:
4236 None.
4238 Returns:
4239 Nothing.
4241 """
4242 self.orientation = 0
4243 self.page_setup_changed = True
4245 def set_portrait(self) -> None:
4246 """
4247 Set the page orientation as portrait.
4249 Args:
4250 None.
4252 Returns:
4253 Nothing.
4255 """
4256 self.orientation = 1
4257 self.page_setup_changed = True
4259 def set_page_view(self, view: Literal[0, 1, 2] = 1) -> None:
4260 """
4261 Set the page view mode.
4263 Args:
4264 0: Normal view mode
4265 1: Page view mode (the default)
4266 2: Page break view mode
4268 Returns:
4269 Nothing.
4271 """
4272 self.page_view = view
4274 def set_pagebreak_view(self) -> None:
4275 """
4276 Set the page view mode.
4278 Args:
4279 None.
4281 Returns:
4282 Nothing.
4284 """
4285 self.page_view = 2
4287 def set_paper(self, paper_size: Union[Literal[1, 9], int]) -> None:
4288 """
4289 Set the paper type. US Letter = 1, A4 = 9.
4291 Args:
4292 paper_size: Paper index.
4294 Returns:
4295 Nothing.
4297 """
4298 if paper_size:
4299 self.paper_size = paper_size
4300 self.page_setup_changed = True
4302 def center_horizontally(self) -> None:
4303 """
4304 Center the page horizontally.
4306 Args:
4307 None.
4309 Returns:
4310 Nothing.
4312 """
4313 self.print_options_changed = True
4314 self.hcenter = 1
4316 def center_vertically(self) -> None:
4317 """
4318 Center the page vertically.
4320 Args:
4321 None.
4323 Returns:
4324 Nothing.
4326 """
4327 self.print_options_changed = True
4328 self.vcenter = 1
4330 def set_margins(
4331 self,
4332 left: float = 0.7,
4333 right: float = 0.7,
4334 top: float = 0.75,
4335 bottom: float = 0.75,
4336 ) -> None:
4337 """
4338 Set all the page margins in inches.
4340 Args:
4341 left: Left margin.
4342 right: Right margin.
4343 top: Top margin.
4344 bottom: Bottom margin.
4346 Returns:
4347 Nothing.
4349 """
4350 self.margin_left = left
4351 self.margin_right = right
4352 self.margin_top = top
4353 self.margin_bottom = bottom
4355 def set_header(
4356 self, header: str = "", options: Optional[Dict[str, Any]] = None, margin=None
4357 ) -> None:
4358 """
4359 Set the page header caption and optional margin.
4361 Args:
4362 header: Header string.
4363 margin: Header margin.
4364 options: Header options, mainly for images.
4366 Returns:
4367 Nothing.
4369 """
4370 header_orig = header
4371 header = header.replace("&[Picture]", "&G")
4373 if len(header) > 255:
4374 warn("Header string cannot be longer than Excel's limit of 255 characters")
4375 return
4377 if options is not None:
4378 # For backward compatibility allow options to be the margin.
4379 if not isinstance(options, dict):
4380 options = {"margin": options}
4381 else:
4382 options = {}
4384 # Copy the user defined options so they aren't modified.
4385 options = options.copy()
4387 # For backward compatibility.
4388 if margin is not None:
4389 options["margin"] = margin
4391 # Reset the list in case the function is called more than once.
4392 self.header_images = []
4394 if options.get("image_left"):
4395 options["image_data"] = options.get("image_data_left")
4396 image = self._image_from_source(options.get("image_left"), options)
4397 image._header_position = "LH"
4398 self.header_images.append(image)
4400 if options.get("image_center"):
4401 options["image_data"] = options.get("image_data_center")
4402 image = self._image_from_source(options.get("image_center"), options)
4403 image._header_position = "CH"
4404 self.header_images.append(image)
4406 if options.get("image_right"):
4407 options["image_data"] = options.get("image_data_right")
4408 image = self._image_from_source(options.get("image_right"), options)
4409 image._header_position = "RH"
4410 self.header_images.append(image)
4412 placeholder_count = header.count("&G")
4413 image_count = len(self.header_images)
4415 if placeholder_count != image_count:
4416 warn(
4417 f"Number of footer images '{image_count}' doesn't match placeholder "
4418 f"count '{placeholder_count}' in string: {header_orig}"
4419 )
4420 self.header_images = []
4421 return
4423 if "align_with_margins" in options:
4424 self.header_footer_aligns = options["align_with_margins"]
4426 if "scale_with_doc" in options:
4427 self.header_footer_scales = options["scale_with_doc"]
4429 self.header = header
4430 self.margin_header = options.get("margin", 0.3)
4431 self.header_footer_changed = True
4433 if image_count:
4434 self.has_header_vml = True
4436 def set_footer(
4437 self, footer: str = "", options: Optional[Dict[str, Any]] = None, margin=None
4438 ) -> None:
4439 """
4440 Set the page footer caption and optional margin.
4442 Args:
4443 footer: Footer string.
4444 margin: Footer margin.
4445 options: Footer options, mainly for images.
4447 Returns:
4448 Nothing.
4450 """
4451 footer_orig = footer
4452 footer = footer.replace("&[Picture]", "&G")
4454 if len(footer) > 255:
4455 warn("Footer string cannot be longer than Excel's limit of 255 characters")
4456 return
4458 if options is not None:
4459 # For backward compatibility allow options to be the margin.
4460 if not isinstance(options, dict):
4461 options = {"margin": options}
4462 else:
4463 options = {}
4465 # Copy the user defined options so they aren't modified.
4466 options = options.copy()
4468 # For backward compatibility.
4469 if margin is not None:
4470 options["margin"] = margin
4472 # Reset the list in case the function is called more than once.
4473 self.footer_images = []
4475 if options.get("image_left"):
4476 options["image_data"] = options.get("image_data_left")
4477 image = self._image_from_source(options.get("image_left"), options)
4478 image._header_position = "LF"
4479 self.footer_images.append(image)
4481 if options.get("image_center"):
4482 options["image_data"] = options.get("image_data_center")
4483 image = self._image_from_source(options.get("image_center"), options)
4484 image._header_position = "CF"
4485 self.footer_images.append(image)
4487 if options.get("image_right"):
4488 options["image_data"] = options.get("image_data_right")
4489 image = self._image_from_source(options.get("image_right"), options)
4490 image._header_position = "RF"
4491 self.footer_images.append(image)
4493 placeholder_count = footer.count("&G")
4494 image_count = len(self.footer_images)
4496 if placeholder_count != image_count:
4497 warn(
4498 f"Number of footer images '{image_count}' doesn't match placeholder "
4499 f"count '{placeholder_count}' in string: {footer_orig}"
4500 )
4501 self.footer_images = []
4502 return
4504 if "align_with_margins" in options:
4505 self.header_footer_aligns = options["align_with_margins"]
4507 if "scale_with_doc" in options:
4508 self.header_footer_scales = options["scale_with_doc"]
4510 self.footer = footer
4511 self.margin_footer = options.get("margin", 0.3)
4512 self.header_footer_changed = True
4514 if image_count:
4515 self.has_header_vml = True
4517 def repeat_rows(self, first_row: int, last_row: Optional[int] = None) -> None:
4518 """
4519 Set the rows to repeat at the top of each printed page.
4521 Args:
4522 first_row: Start row for range.
4523 last_row: End row for range.
4525 Returns:
4526 Nothing.
4528 """
4529 if last_row is None:
4530 last_row = first_row
4532 # Convert rows to 1 based.
4533 first_row += 1
4534 last_row += 1
4536 # Create the row range area like: $1:$2.
4537 area = f"${first_row}:${last_row}"
4539 # Build up the print titles area "Sheet1!$1:$2"
4540 sheetname = quote_sheetname(self.name)
4541 self.repeat_row_range = sheetname + "!" + area
4543 @convert_column_args
4544 def repeat_columns(self, first_col: int, last_col: Optional[int] = None) -> None:
4545 """
4546 Set the columns to repeat at the left hand side of each printed page.
4548 Args:
4549 first_col: Start column for range.
4550 last_col: End column for range.
4552 Returns:
4553 Nothing.
4555 """
4556 if last_col is None:
4557 last_col = first_col
4559 # Convert to A notation.
4560 first_col = xl_col_to_name(first_col, 1)
4561 last_col = xl_col_to_name(last_col, 1)
4563 # Create a column range like $C:$D.
4564 area = first_col + ":" + last_col
4566 # Build up the print area range "=Sheet2!$C:$D"
4567 sheetname = quote_sheetname(self.name)
4568 self.repeat_col_range = sheetname + "!" + area
4570 def hide_gridlines(self, option: Literal[0, 1, 2] = 1) -> None:
4571 """
4572 Set the option to hide gridlines on the screen and the printed page.
4574 Args:
4575 option: 0 : Don't hide gridlines
4576 1 : Hide printed gridlines only
4577 2 : Hide screen and printed gridlines
4579 Returns:
4580 Nothing.
4582 """
4583 if option == 0:
4584 self.print_gridlines = 1
4585 self.screen_gridlines = 1
4586 self.print_options_changed = True
4587 elif option == 1:
4588 self.print_gridlines = 0
4589 self.screen_gridlines = 1
4590 else:
4591 self.print_gridlines = 0
4592 self.screen_gridlines = 0
4594 def print_row_col_headers(self) -> None:
4595 """
4596 Set the option to print the row and column headers on the printed page.
4598 Args:
4599 None.
4601 Returns:
4602 Nothing.
4604 """
4605 self.print_headers = True
4606 self.print_options_changed = True
4608 def hide_row_col_headers(self) -> None:
4609 """
4610 Set the option to hide the row and column headers on the worksheet.
4612 Args:
4613 None.
4615 Returns:
4616 Nothing.
4618 """
4619 self.row_col_headers = True
4621 @convert_range_args
4622 def print_area(
4623 self, first_row: int, first_col: int, last_row: int, last_col: int
4624 ) -> Literal[0, -1]:
4625 """
4626 Set the print area in the current worksheet.
4628 Args:
4629 first_row: The first row of the cell range. (zero indexed).
4630 first_col: The first column of the cell range.
4631 last_row: The last row of the cell range. (zero indexed).
4632 last_col: The last column of the cell range.
4634 Returns:
4635 0: Success.
4636 -1: Row or column is out of worksheet bounds.
4638 """
4639 # Set the print area in the current worksheet.
4641 # Ignore max print area since it is the same as no area for Excel.
4642 if (
4643 first_row == 0
4644 and first_col == 0
4645 and last_row == self.xls_rowmax - 1
4646 and last_col == self.xls_colmax - 1
4647 ):
4648 return -1
4650 # Build up the print area range "Sheet1!$A$1:$C$13".
4651 area = self._convert_name_area(first_row, first_col, last_row, last_col)
4652 self.print_area_range = area
4654 return 0
4656 def print_across(self) -> None:
4657 """
4658 Set the order in which pages are printed.
4660 Args:
4661 None.
4663 Returns:
4664 Nothing.
4666 """
4667 self.page_order = 1
4668 self.page_setup_changed = True
4670 def fit_to_pages(self, width: int, height: int) -> None:
4671 """
4672 Fit the printed area to a specific number of pages both vertically and
4673 horizontally.
4675 Args:
4676 width: Number of pages horizontally.
4677 height: Number of pages vertically.
4679 Returns:
4680 Nothing.
4682 """
4683 self.fit_page = 1
4684 self.fit_width = width
4685 self.fit_height = height
4686 self.page_setup_changed = True
4688 def set_start_page(self, start_page: int) -> None:
4689 """
4690 Set the start page number when printing.
4692 Args:
4693 start_page: Start page number.
4695 Returns:
4696 Nothing.
4698 """
4699 self.page_start = start_page
4701 def set_print_scale(self, scale: int) -> None:
4702 """
4703 Set the scale factor for the printed page.
4705 Args:
4706 scale: Print scale. 10 <= scale <= 400.
4708 Returns:
4709 Nothing.
4711 """
4712 # Confine the scale to Excel's range.
4713 if scale < 10 or scale > 400:
4714 warn(f"Print scale '{scale}' outside range: 10 <= scale <= 400")
4715 return
4717 # Turn off "fit to page" option when print scale is on.
4718 self.fit_page = 0
4720 self.print_scale = int(scale)
4721 self.page_setup_changed = True
4723 def print_black_and_white(self) -> None:
4724 """
4725 Set the option to print the worksheet in black and white.
4727 Args:
4728 None.
4730 Returns:
4731 Nothing.
4733 """
4734 self.black_white = True
4735 self.page_setup_changed = True
4737 def set_h_pagebreaks(self, breaks: List[int]) -> None:
4738 """
4739 Set the horizontal page breaks on a worksheet.
4741 Args:
4742 breaks: List of rows where the page breaks should be added.
4744 Returns:
4745 Nothing.
4747 """
4748 self.hbreaks = breaks
4750 def set_v_pagebreaks(self, breaks: List[int]) -> None:
4751 """
4752 Set the horizontal page breaks on a worksheet.
4754 Args:
4755 breaks: List of columns where the page breaks should be added.
4757 Returns:
4758 Nothing.
4760 """
4761 self.vbreaks = breaks
4763 def set_vba_name(self, name: Optional[str] = None) -> None:
4764 """
4765 Set the VBA name for the worksheet. By default this is the
4766 same as the sheet name: i.e., Sheet1 etc.
4768 Args:
4769 name: The VBA name for the worksheet.
4771 Returns:
4772 Nothing.
4774 """
4775 if name is not None:
4776 self.vba_codename = name
4777 else:
4778 self.vba_codename = "Sheet" + str(self.index + 1)
4780 def ignore_errors(self, options: Optional[Dict[str, Any]] = None) -> Literal[0, -1]:
4781 """
4782 Ignore various Excel errors/warnings in a worksheet for user defined
4783 ranges.
4785 Args:
4786 options: A dict of ignore errors keys with cell range values.
4788 Returns:
4789 0: Success.
4790 -1: Incorrect parameter or option.
4792 """
4793 if options is None:
4794 return -1
4796 # Copy the user defined options so they aren't modified.
4797 options = options.copy()
4799 # Valid input parameters.
4800 valid_parameters = {
4801 "number_stored_as_text",
4802 "eval_error",
4803 "formula_differs",
4804 "formula_range",
4805 "formula_unlocked",
4806 "empty_cell_reference",
4807 "list_data_validation",
4808 "calculated_column",
4809 "two_digit_text_year",
4810 }
4812 # Check for valid input parameters.
4813 for param_key in options.keys():
4814 if param_key not in valid_parameters:
4815 warn(f"Unknown parameter '{param_key}' in ignore_errors()")
4816 return -1
4818 self.ignored_errors = options
4820 return 0
4822 ###########################################################################
4823 #
4824 # Private API.
4825 #
4826 ###########################################################################
4827 def _initialize(self, init_data) -> None:
4828 self.name = init_data["name"]
4829 self.index = init_data["index"]
4830 self.str_table = init_data["str_table"]
4831 self.worksheet_meta = init_data["worksheet_meta"]
4832 self.constant_memory = init_data["constant_memory"]
4833 self.tmpdir = init_data["tmpdir"]
4834 self.date_1904 = init_data["date_1904"]
4835 self.strings_to_numbers = init_data["strings_to_numbers"]
4836 self.strings_to_formulas = init_data["strings_to_formulas"]
4837 self.strings_to_urls = init_data["strings_to_urls"]
4838 self.nan_inf_to_errors = init_data["nan_inf_to_errors"]
4839 self.default_date_format = init_data["default_date_format"]
4840 self.default_url_format = init_data["default_url_format"]
4841 self.workbook_add_format = init_data["workbook_add_format"]
4842 self.excel2003_style = init_data["excel2003_style"]
4843 self.remove_timezone = init_data["remove_timezone"]
4844 self.max_url_length = init_data["max_url_length"]
4845 self.use_future_functions = init_data["use_future_functions"]
4846 self.embedded_images = init_data["embedded_images"]
4848 if self.excel2003_style:
4849 self.original_row_height = 12.75
4850 self.default_row_height = 12.75
4851 self.default_row_pixels = 17
4852 self.margin_left = 0.75
4853 self.margin_right = 0.75
4854 self.margin_top = 1
4855 self.margin_bottom = 1
4856 self.margin_header = 0.5
4857 self.margin_footer = 0.5
4858 self.header_footer_aligns = False
4860 # Open a temp filehandle to store row data in constant_memory mode.
4861 if self.constant_memory:
4862 # This is sub-optimal but we need to create a temp file
4863 # with utf8 encoding in Python < 3.
4864 (fd, filename) = tempfile.mkstemp(dir=self.tmpdir)
4865 os.close(fd)
4866 self.row_data_filename = filename
4867 # pylint: disable=consider-using-with
4868 self.row_data_fh = open(filename, mode="w+", encoding="utf-8")
4870 # Set as the worksheet filehandle until the file is assembled.
4871 self.fh = self.row_data_fh
4873 def _assemble_xml_file(self) -> None:
4874 # Assemble and write the XML file.
4876 # Write the XML declaration.
4877 self._xml_declaration()
4879 # Write the root worksheet element.
4880 self._write_worksheet()
4882 # Write the worksheet properties.
4883 self._write_sheet_pr()
4885 # Write the worksheet dimensions.
4886 self._write_dimension()
4888 # Write the sheet view properties.
4889 self._write_sheet_views()
4891 # Write the sheet format properties.
4892 self._write_sheet_format_pr()
4894 # Write the sheet column info.
4895 self._write_cols()
4897 # Write the worksheet data such as rows columns and cells.
4898 if not self.constant_memory:
4899 self._write_sheet_data()
4900 else:
4901 self._write_optimized_sheet_data()
4903 # Write the sheetProtection element.
4904 self._write_sheet_protection()
4906 # Write the protectedRanges element.
4907 self._write_protected_ranges()
4909 # Write the phoneticPr element.
4910 if self.excel2003_style:
4911 self._write_phonetic_pr()
4913 # Write the autoFilter element.
4914 self._write_auto_filter()
4916 # Write the mergeCells element.
4917 self._write_merge_cells()
4919 # Write the conditional formats.
4920 self._write_conditional_formats()
4922 # Write the dataValidations element.
4923 self._write_data_validations()
4925 # Write the hyperlink element.
4926 self._write_hyperlinks()
4928 # Write the printOptions element.
4929 self._write_print_options()
4931 # Write the worksheet page_margins.
4932 self._write_page_margins()
4934 # Write the worksheet page setup.
4935 self._write_page_setup()
4937 # Write the headerFooter element.
4938 self._write_header_footer()
4940 # Write the rowBreaks element.
4941 self._write_row_breaks()
4943 # Write the colBreaks element.
4944 self._write_col_breaks()
4946 # Write the ignoredErrors element.
4947 self._write_ignored_errors()
4949 # Write the drawing element.
4950 self._write_drawings()
4952 # Write the legacyDrawing element.
4953 self._write_legacy_drawing()
4955 # Write the legacyDrawingHF element.
4956 self._write_legacy_drawing_hf()
4958 # Write the picture element, for the background.
4959 self._write_picture()
4961 # Write the tableParts element.
4962 self._write_table_parts()
4964 # Write the extLst elements.
4965 self._write_ext_list()
4967 # Close the worksheet tag.
4968 self._xml_end_tag("worksheet")
4970 # Close the file.
4971 self._xml_close()
4973 def _check_dimensions(
4974 self, row: int, col: int, ignore_row=False, ignore_col=False
4975 ) -> int:
4976 # Check that row and col are valid and store the max and min
4977 # values for use in other methods/elements. The ignore_row /
4978 # ignore_col flags is used to indicate that we wish to perform
4979 # the dimension check without storing the value. The ignore
4980 # flags are use by set_row() and data_validate.
4982 # Check that the row/col are within the worksheet bounds.
4983 if row < 0 or col < 0:
4984 return -1
4985 if row >= self.xls_rowmax or col >= self.xls_colmax:
4986 return -1
4988 # In constant_memory mode we don't change dimensions for rows
4989 # that are already written.
4990 if not ignore_row and not ignore_col and self.constant_memory:
4991 if row < self.previous_row:
4992 return -2
4994 if not ignore_row:
4995 if self.dim_rowmin is None or row < self.dim_rowmin:
4996 self.dim_rowmin = row
4997 if self.dim_rowmax is None or row > self.dim_rowmax:
4998 self.dim_rowmax = row
5000 if not ignore_col:
5001 if self.dim_colmin is None or col < self.dim_colmin:
5002 self.dim_colmin = col
5003 if self.dim_colmax is None or col > self.dim_colmax:
5004 self.dim_colmax = col
5006 return 0
5008 def _convert_date_time(self, dt_obj):
5009 # Convert a datetime object to an Excel serial date and time.
5010 return _datetime_to_excel_datetime(dt_obj, self.date_1904, self.remove_timezone)
5012 def _convert_name_area(self, row_num_1, col_num_1, row_num_2, col_num_2):
5013 # Convert zero indexed rows and columns to the format required by
5014 # worksheet named ranges, eg, "Sheet1!$A$1:$C$13".
5016 range1 = ""
5017 range2 = ""
5018 area = ""
5019 row_col_only = 0
5021 # Convert to A1 notation.
5022 col_char_1 = xl_col_to_name(col_num_1, 1)
5023 col_char_2 = xl_col_to_name(col_num_2, 1)
5024 row_char_1 = "$" + str(row_num_1 + 1)
5025 row_char_2 = "$" + str(row_num_2 + 1)
5027 # We need to handle special cases that refer to rows or columns only.
5028 if row_num_1 == 0 and row_num_2 == self.xls_rowmax - 1:
5029 range1 = col_char_1
5030 range2 = col_char_2
5031 row_col_only = 1
5032 elif col_num_1 == 0 and col_num_2 == self.xls_colmax - 1:
5033 range1 = row_char_1
5034 range2 = row_char_2
5035 row_col_only = 1
5036 else:
5037 range1 = col_char_1 + row_char_1
5038 range2 = col_char_2 + row_char_2
5040 # A repeated range is only written once (if it isn't a special case).
5041 if range1 == range2 and not row_col_only:
5042 area = range1
5043 else:
5044 area = range1 + ":" + range2
5046 # Build up the print area range "Sheet1!$A$1:$C$13".
5047 sheetname = quote_sheetname(self.name)
5048 area = sheetname + "!" + area
5050 return area
5052 def _sort_pagebreaks(self, breaks):
5053 # This is an internal method used to filter elements of a list of
5054 # pagebreaks used in the _store_hbreak() and _store_vbreak() methods.
5055 # It:
5056 # 1. Removes duplicate entries from the list.
5057 # 2. Sorts the list.
5058 # 3. Removes 0 from the list if present.
5059 if not breaks:
5060 return []
5062 breaks_set = set(breaks)
5064 if 0 in breaks_set:
5065 breaks_set.remove(0)
5067 breaks_list = list(breaks_set)
5068 breaks_list.sort()
5070 # The Excel 2007 specification says that the maximum number of page
5071 # breaks is 1026. However, in practice it is actually 1023.
5072 max_num_breaks = 1023
5073 if len(breaks_list) > max_num_breaks:
5074 breaks_list = breaks_list[:max_num_breaks]
5076 return breaks_list
5078 def _extract_filter_tokens(self, expression):
5079 # Extract the tokens from the filter expression. The tokens are mainly
5080 # non-whitespace groups. The only tricky part is to extract string
5081 # tokens that contain whitespace and/or quoted double quotes (Excel's
5082 # escaped quotes).
5083 #
5084 # Examples: 'x < 2000'
5085 # 'x > 2000 and x < 5000'
5086 # 'x = "foo"'
5087 # 'x = "foo bar"'
5088 # 'x = "foo "" bar"'
5089 #
5090 if not expression:
5091 return []
5093 token_re = re.compile(r'"(?:[^"]|"")*"|\S+')
5094 tokens = token_re.findall(expression)
5096 new_tokens = []
5097 # Remove single leading and trailing quotes and un-escape other quotes.
5098 for token in tokens:
5099 if token.startswith('"'):
5100 token = token[1:]
5102 if token.endswith('"'):
5103 token = token[:-1]
5105 token = token.replace('""', '"')
5107 new_tokens.append(token)
5109 return new_tokens
5111 def _parse_filter_expression(self, expression, tokens):
5112 # Converts the tokens of a possibly conditional expression into 1 or 2
5113 # sub expressions for further parsing.
5114 #
5115 # Examples:
5116 # ('x', '==', 2000) -> exp1
5117 # ('x', '>', 2000, 'and', 'x', '<', 5000) -> exp1 and exp2
5119 if len(tokens) == 7:
5120 # The number of tokens will be either 3 (for 1 expression)
5121 # or 7 (for 2 expressions).
5122 conditional = tokens[3]
5124 if re.match("(and|&&)", conditional):
5125 conditional = 0
5126 elif re.match(r"(or|\|\|)", conditional):
5127 conditional = 1
5128 else:
5129 warn(
5130 f"Token '{conditional}' is not a valid conditional "
5131 f"in filter expression '{expression}'"
5132 )
5134 expression_1 = self._parse_filter_tokens(expression, tokens[0:3])
5135 expression_2 = self._parse_filter_tokens(expression, tokens[4:7])
5136 return expression_1 + [conditional] + expression_2
5138 return self._parse_filter_tokens(expression, tokens)
5140 def _parse_filter_tokens(self, expression, tokens):
5141 # Parse the 3 tokens of a filter expression and return the operator
5142 # and token. The use of numbers instead of operators is a legacy of
5143 # Spreadsheet::WriteExcel.
5144 operators = {
5145 "==": 2,
5146 "=": 2,
5147 "=~": 2,
5148 "eq": 2,
5149 "!=": 5,
5150 "!~": 5,
5151 "ne": 5,
5152 "<>": 5,
5153 "<": 1,
5154 "<=": 3,
5155 ">": 4,
5156 ">=": 6,
5157 }
5159 operator = operators.get(tokens[1], None)
5160 token = tokens[2]
5162 # Special handling of "Top" filter expressions.
5163 if re.match("top|bottom", tokens[0].lower()):
5164 value = int(tokens[1])
5166 if value < 1 or value > 500:
5167 warn(
5168 f"The value '{token}' in expression '{expression}' "
5169 f"must be in the range 1 to 500"
5170 )
5172 token = token.lower()
5174 if token not in ("items", "%"):
5175 warn(
5176 f"The type '{token}' in expression '{expression}' "
5177 f"must be either 'items' or '%%'"
5178 )
5180 if tokens[0].lower() == "top":
5181 operator = 30
5182 else:
5183 operator = 32
5185 if tokens[2] == "%":
5186 operator += 1
5188 token = str(value)
5190 if not operator and tokens[0]:
5191 warn(
5192 f"Token '{token[0]}' is not a valid operator "
5193 f"in filter expression '{expression}'."
5194 )
5196 # Special handling for Blanks/NonBlanks.
5197 if re.match("blanks|nonblanks", token.lower()):
5198 # Only allow Equals or NotEqual in this context.
5199 if operator not in (2, 5):
5200 warn(
5201 f"The operator '{tokens[1]}' in expression '{expression}' "
5202 f"is not valid in relation to Blanks/NonBlanks'."
5203 )
5205 token = token.lower()
5207 # The operator should always be 2 (=) to flag a "simple" equality
5208 # in the binary record. Therefore we convert <> to =.
5209 if token == "blanks":
5210 if operator == 5:
5211 token = " "
5212 else:
5213 if operator == 5:
5214 operator = 2
5215 token = "blanks"
5216 else:
5217 operator = 5
5218 token = " "
5220 # if the string token contains an Excel match character then change the
5221 # operator type to indicate a non "simple" equality.
5222 if operator == 2 and re.search("[*?]", token):
5223 operator = 22
5225 return [operator, token]
5227 def _encode_password(self, password) -> str:
5228 # Hash a worksheet password. Based on the algorithm in
5229 # ECMA-376-4:2016, Office Open XML File Formats — Transitional
5230 # Migration Features, Additional attributes for workbookProtection
5231 # element (Part 1, §18.2.29).
5232 digest = 0x0000
5234 for char in password[::-1]:
5235 digest = ((digest >> 14) & 0x01) | ((digest << 1) & 0x7FFF)
5236 digest ^= ord(char)
5238 digest = ((digest >> 14) & 0x01) | ((digest << 1) & 0x7FFF)
5239 digest ^= len(password)
5240 digest ^= 0xCE4B
5242 return f"{digest:X}"
5244 def _image_from_source(self, source, options: Optional[Dict[str, Any]] = None):
5245 # Backward compatibility utility method to convert an input argument to
5246 # an Image object. The source can be a filename, BytesIO stream or
5247 # an existing Image object.
5248 if isinstance(source, Image):
5249 image = source
5250 elif options is not None and options.get("image_data"):
5251 image = Image(options["image_data"])
5252 image.image_name = source
5253 else:
5254 image = Image(source)
5256 return image
5258 def _prepare_image(
5259 self,
5260 image: Image,
5261 image_id: int,
5262 drawing_id: int,
5263 ) -> None:
5264 # Set up images/drawings.
5266 # Get the effective image width and height in pixels.
5267 width = image._width * image._x_scale
5268 height = image._height * image._y_scale
5270 # Scale by non 96dpi resolutions.
5271 width *= 96.0 / image._x_dpi
5272 height *= 96.0 / image._y_dpi
5274 dimensions = self._position_object_emus(
5275 image._col,
5276 image._row,
5277 image._x_offset,
5278 image._y_offset,
5279 width,
5280 height,
5281 image._anchor,
5282 )
5284 # Convert from pixels to emus.
5285 width = int(0.5 + (width * 9525))
5286 height = int(0.5 + (height * 9525))
5288 # Create a Drawing obj to use with worksheet unless one already exists.
5289 if not self.drawing:
5290 drawing = Drawing()
5291 drawing.embedded = 1
5292 self.drawing = drawing
5294 self.external_drawing_links.append(
5295 ["/drawing", "../drawings/drawing" + str(drawing_id) + ".xml", None]
5296 )
5297 else:
5298 drawing = self.drawing
5300 drawing_object = DrawingInfo()
5301 drawing_object._drawing_type = DrawingTypes.IMAGE
5302 drawing_object._dimensions = dimensions
5303 drawing_object._description = image.image_name
5304 drawing_object._width = width
5305 drawing_object._height = height
5306 drawing_object._shape = None
5307 drawing_object._anchor = image._anchor
5308 drawing_object._rel_index = 0
5309 drawing_object._decorative = image._decorative
5311 if image.description is not None:
5312 drawing_object._description = image.description
5314 if image._url:
5315 url = image._url
5316 target = url._target()
5317 target_mode = url._target_mode()
5319 if not self.drawing_rels.get(url._link):
5320 self.drawing_links.append(["/hyperlink", target, target_mode])
5322 url._rel_index = self._get_drawing_rel_index(url._link)
5323 drawing_object._url = url
5325 if not self.drawing_rels.get(image._digest):
5326 self.drawing_links.append(
5327 [
5328 "/image",
5329 "../media/image" + str(image_id) + "." + image._image_extension,
5330 ]
5331 )
5333 drawing_object._rel_index = self._get_drawing_rel_index(image._digest)
5334 drawing._add_drawing_object(drawing_object)
5336 def _prepare_shape(self, index, drawing_id) -> None:
5337 # Set up shapes/drawings.
5338 (
5339 row,
5340 col,
5341 x_offset,
5342 y_offset,
5343 x_scale,
5344 y_scale,
5345 text,
5346 anchor,
5347 options,
5348 description,
5349 decorative,
5350 ) = self.shapes[index]
5352 width = options.get("width", self.default_col_pixels * 3)
5353 height = options.get("height", self.default_row_pixels * 6)
5355 width *= x_scale
5356 height *= y_scale
5358 dimensions = self._position_object_emus(
5359 col, row, x_offset, y_offset, width, height, anchor
5360 )
5362 # Convert from pixels to emus.
5363 width = int(0.5 + (width * 9525))
5364 height = int(0.5 + (height * 9525))
5366 # Create a Drawing obj to use with worksheet unless one already exists.
5367 if not self.drawing:
5368 drawing = Drawing()
5369 drawing.embedded = 1
5370 self.drawing = drawing
5372 self.external_drawing_links.append(
5373 ["/drawing", "../drawings/drawing" + str(drawing_id) + ".xml", None]
5374 )
5375 else:
5376 drawing = self.drawing
5378 shape = Shape("rect", "TextBox", options)
5379 shape.text = text
5381 drawing_object = DrawingInfo()
5382 drawing_object._drawing_type = DrawingTypes.SHAPE
5383 drawing_object._dimensions = dimensions
5384 drawing_object._width = width
5385 drawing_object._height = height
5386 drawing_object._description = description
5387 drawing_object._shape = shape
5388 drawing_object._anchor = anchor
5389 drawing_object._rel_index = 0
5390 drawing_object._decorative = decorative
5392 url = Url.from_options(options)
5393 if url:
5394 target = url._target()
5395 target_mode = url._target_mode()
5397 if not self.drawing_rels.get(url._link):
5398 self.drawing_links.append(["/hyperlink", target, target_mode])
5400 url._rel_index = self._get_drawing_rel_index(url._link)
5401 drawing_object._url = url
5403 drawing._add_drawing_object(drawing_object)
5405 def _prepare_header_image(self, image_id, image) -> None:
5406 # Set up an image without a drawing object for header/footer images.
5408 # Strip the extension from the filename.
5409 image.image_name = re.sub(r"\..*$", "", image.image_name)
5411 if not self.vml_drawing_rels.get(image._digest):
5412 self.vml_drawing_links.append(
5413 [
5414 "/image",
5415 "../media/image" + str(image_id) + "." + image._image_extension,
5416 ]
5417 )
5419 image._ref_id = self._get_vml_drawing_rel_index(image._digest)
5421 self.header_images_list.append(image)
5423 def _prepare_background(self, image_id, image_extension) -> None:
5424 # Set up an image without a drawing object for backgrounds.
5425 self.external_background_links.append(
5426 ["/image", "../media/image" + str(image_id) + "." + image_extension]
5427 )
5429 def _prepare_chart(self, index, chart_id, drawing_id) -> None:
5430 # Set up chart/drawings.
5431 (
5432 row,
5433 col,
5434 chart,
5435 x_offset,
5436 y_offset,
5437 x_scale,
5438 y_scale,
5439 anchor,
5440 description,
5441 decorative,
5442 ) = self.charts[index]
5444 chart.id = chart_id - 1
5446 # Use user specified dimensions, if any.
5447 width = int(0.5 + (chart.width * x_scale))
5448 height = int(0.5 + (chart.height * y_scale))
5450 dimensions = self._position_object_emus(
5451 col, row, x_offset, y_offset, width, height, anchor
5452 )
5454 # Set the chart name for the embedded object if it has been specified.
5455 name = chart.chart_name
5457 # Create a Drawing obj to use with worksheet unless one already exists.
5458 if not self.drawing:
5459 drawing = Drawing()
5460 drawing.embedded = 1
5461 self.drawing = drawing
5463 self.external_drawing_links.append(
5464 ["/drawing", "../drawings/drawing" + str(drawing_id) + ".xml"]
5465 )
5466 else:
5467 drawing = self.drawing
5469 drawing_object = DrawingInfo()
5470 drawing_object._drawing_type = DrawingTypes.CHART
5471 drawing_object._dimensions = dimensions
5472 drawing_object._width = width
5473 drawing_object._height = height
5474 drawing_object._name = name
5475 drawing_object._shape = None
5476 drawing_object._anchor = anchor
5477 drawing_object._rel_index = self._get_drawing_rel_index()
5478 drawing_object._description = description
5479 drawing_object._decorative = decorative
5481 drawing._add_drawing_object(drawing_object)
5483 self.drawing_links.append(
5484 ["/chart", "../charts/chart" + str(chart_id) + ".xml"]
5485 )
5487 def _position_object_emus(
5488 self, col_start, row_start, x1, y1, width, height, anchor
5489 ):
5490 # Calculate the vertices that define the position of a graphical
5491 # object within the worksheet in EMUs.
5492 #
5493 # The vertices are expressed as English Metric Units (EMUs). There are
5494 # 12,700 EMUs per point. Therefore, 12,700 * 3 /4 = 9,525 EMUs per
5495 # pixel
5496 (
5497 col_start,
5498 row_start,
5499 x1,
5500 y1,
5501 col_end,
5502 row_end,
5503 x2,
5504 y2,
5505 x_abs,
5506 y_abs,
5507 ) = self._position_object_pixels(
5508 col_start, row_start, x1, y1, width, height, anchor
5509 )
5511 # Convert the pixel values to EMUs. See above.
5512 x1 = int(0.5 + 9525 * x1)
5513 y1 = int(0.5 + 9525 * y1)
5514 x2 = int(0.5 + 9525 * x2)
5515 y2 = int(0.5 + 9525 * y2)
5516 x_abs = int(0.5 + 9525 * x_abs)
5517 y_abs = int(0.5 + 9525 * y_abs)
5519 return (col_start, row_start, x1, y1, col_end, row_end, x2, y2, x_abs, y_abs)
5521 # Calculate the vertices that define the position of a graphical object
5522 # within the worksheet in pixels.
5523 #
5524 # +------------+------------+
5525 # | A | B |
5526 # +-----+------------+------------+
5527 # | |(x1,y1) | |
5528 # | 1 |(A1)._______|______ |
5529 # | | | | |
5530 # | | | | |
5531 # +-----+----| OBJECT |-----+
5532 # | | | | |
5533 # | 2 | |______________. |
5534 # | | | (B2)|
5535 # | | | (x2,y2)|
5536 # +---- +------------+------------+
5537 #
5538 # Example of an object that covers some of the area from cell A1 to B2.
5539 #
5540 # Based on the width and height of the object we need to calculate 8 vars:
5541 #
5542 # col_start, row_start, col_end, row_end, x1, y1, x2, y2.
5543 #
5544 # We also calculate the absolute x and y position of the top left vertex of
5545 # the object. This is required for images.
5546 #
5547 # The width and height of the cells that the object occupies can be
5548 # variable and have to be taken into account.
5549 #
5550 # The values of col_start and row_start are passed in from the calling
5551 # function. The values of col_end and row_end are calculated by
5552 # subtracting the width and height of the object from the width and
5553 # height of the underlying cells.
5554 #
5555 def _position_object_pixels(
5556 self, col_start, row_start, x1, y1, width, height, anchor
5557 ):
5558 # col_start # Col containing upper left corner of object.
5559 # x1 # Distance to left side of object.
5560 #
5561 # row_start # Row containing top left corner of object.
5562 # y1 # Distance to top of object.
5563 #
5564 # col_end # Col containing lower right corner of object.
5565 # x2 # Distance to right side of object.
5566 #
5567 # row_end # Row containing bottom right corner of object.
5568 # y2 # Distance to bottom of object.
5569 #
5570 # width # Width of object frame.
5571 # height # Height of object frame.
5572 #
5573 # x_abs # Absolute distance to left side of object.
5574 # y_abs # Absolute distance to top side of object.
5575 x_abs = 0
5576 y_abs = 0
5578 # Adjust start column for negative offsets.
5579 # pylint: disable=chained-comparison
5580 while x1 < 0 and col_start > 0:
5581 x1 += self._size_col(col_start - 1)
5582 col_start -= 1
5584 # Adjust start row for negative offsets.
5585 while y1 < 0 and row_start > 0:
5586 y1 += self._size_row(row_start - 1)
5587 row_start -= 1
5589 # Ensure that the image isn't shifted off the page at top left.
5590 x1 = max(0, x1)
5591 y1 = max(0, y1)
5593 # Calculate the absolute x offset of the top-left vertex.
5594 if self.col_size_changed:
5595 for col_id in range(col_start):
5596 x_abs += self._size_col(col_id)
5597 else:
5598 # Optimization for when the column widths haven't changed.
5599 x_abs += self.default_col_pixels * col_start
5601 x_abs += x1
5603 # Calculate the absolute y offset of the top-left vertex.
5604 if self.row_size_changed:
5605 for row_id in range(row_start):
5606 y_abs += self._size_row(row_id)
5607 else:
5608 # Optimization for when the row heights haven't changed.
5609 y_abs += self.default_row_pixels * row_start
5611 y_abs += y1
5613 # Adjust start column for offsets that are greater than the col width.
5614 while x1 >= self._size_col(col_start, anchor):
5615 x1 -= self._size_col(col_start)
5616 col_start += 1
5618 # Adjust start row for offsets that are greater than the row height.
5619 while y1 >= self._size_row(row_start, anchor):
5620 y1 -= self._size_row(row_start)
5621 row_start += 1
5623 # Initialize end cell to the same as the start cell.
5624 col_end = col_start
5625 row_end = row_start
5627 # Don't offset the image in the cell if the row/col is hidden.
5628 if self._size_col(col_start, anchor) > 0:
5629 width = width + x1
5630 if self._size_row(row_start, anchor) > 0:
5631 height = height + y1
5633 # Subtract the underlying cell widths to find end cell of the object.
5634 while width >= self._size_col(col_end, anchor):
5635 width -= self._size_col(col_end, anchor)
5636 col_end += 1
5638 # Subtract the underlying cell heights to find end cell of the object.
5639 while height >= self._size_row(row_end, anchor):
5640 height -= self._size_row(row_end, anchor)
5641 row_end += 1
5643 # The end vertices are whatever is left from the width and height.
5644 x2 = width
5645 y2 = height
5647 return [col_start, row_start, x1, y1, col_end, row_end, x2, y2, x_abs, y_abs]
5649 def _size_col(self, col: int, anchor=0):
5650 # Convert the width of a cell from character units to pixels. Excel
5651 # rounds the column width to the nearest pixel. If the width hasn't
5652 # been set by the user we use the default value. A hidden column is
5653 # treated as having a width of zero unless it has the special
5654 # "object_position" of 4 (size with cells).
5655 max_digit_width = 7 # For Calibri 11.
5656 padding = 5
5657 pixels = 0
5659 # Look up the cell value to see if it has been changed.
5660 if col in self.col_info:
5661 width = self.col_info[col][0]
5662 hidden = self.col_info[col][2]
5664 if width is None:
5665 width = self.default_col_width
5667 # Convert to pixels.
5668 if hidden and anchor != 4:
5669 pixels = 0
5670 elif width < 1:
5671 pixels = int(width * (max_digit_width + padding) + 0.5)
5672 else:
5673 pixels = int(width * max_digit_width + 0.5) + padding
5674 else:
5675 pixels = self.default_col_pixels
5677 return pixels
5679 def _size_row(self, row: int, anchor=0):
5680 # Convert the height of a cell from character units to pixels. If the
5681 # height hasn't been set by the user we use the default value. A
5682 # hidden row is treated as having a height of zero unless it has the
5683 # special "object_position" of 4 (size with cells).
5684 pixels = 0
5686 # Look up the cell value to see if it has been changed
5687 if row in self.row_sizes:
5688 height = self.row_sizes[row][0]
5689 hidden = self.row_sizes[row][1]
5691 if hidden and anchor != 4:
5692 pixels = 0
5693 else:
5694 pixels = int(4.0 / 3.0 * height)
5695 else:
5696 pixels = int(4.0 / 3.0 * self.default_row_height)
5698 return pixels
5700 def _pixels_to_width(self, pixels):
5701 # Convert the width of a cell from pixels to character units.
5702 max_digit_width = 7.0 # For Calabri 11.
5703 padding = 5.0
5705 if pixels <= 12:
5706 width = pixels / (max_digit_width + padding)
5707 else:
5708 width = (pixels - padding) / max_digit_width
5710 return width
5712 def _pixels_to_height(self, pixels):
5713 # Convert the height of a cell from pixels to character units.
5714 return 0.75 * pixels
5716 def _comment_vertices(self, comment: CommentType):
5717 # Calculate the positions of the comment object.
5718 anchor = 0
5719 vertices = self._position_object_pixels(
5720 comment.start_col,
5721 comment.start_row,
5722 comment.x_offset,
5723 comment.y_offset,
5724 comment.width,
5725 comment.height,
5726 anchor,
5727 )
5729 # Add the width and height for VML.
5730 vertices.append(comment.width)
5731 vertices.append(comment.height)
5733 return vertices
5735 def _button_vertices(self, button: ButtonType):
5736 # Calculate the positions of the button object.
5737 anchor = 0
5738 vertices = self._position_object_pixels(
5739 button.col,
5740 button.row,
5741 button.x_offset,
5742 button.y_offset,
5743 button.width,
5744 button.height,
5745 anchor,
5746 )
5748 # Add the width and height for VML.
5749 vertices.append(button.width)
5750 vertices.append(button.height)
5752 return vertices
5754 def _prepare_vml_objects(
5755 self, vml_data_id, vml_shape_id, vml_drawing_id, comment_id
5756 ):
5757 comments = []
5758 # Sort the comments into row/column order for easier comparison
5759 # testing and set the external links for comments and buttons.
5760 row_nums = sorted(self.comments.keys())
5762 for row in row_nums:
5763 col_nums = sorted(self.comments[row].keys())
5765 for col in col_nums:
5766 comment = self.comments[row][col]
5767 comment.vertices = self._comment_vertices(comment)
5769 # Set comment visibility if required and not user defined.
5770 if comment.is_visible is None:
5771 comment.is_visible = self.comments_visible
5773 # Set comment author if not already user defined.
5774 if comment.author is None:
5775 comment.author = self.comments_author
5777 comments.append(comment)
5779 for button in self.buttons_list:
5780 button.vertices = self._button_vertices(button)
5782 self.external_vml_links.append(
5783 ["/vmlDrawing", "../drawings/vmlDrawing" + str(vml_drawing_id) + ".vml"]
5784 )
5786 if self.has_comments:
5787 self.comments_list = comments
5789 self.external_comment_links.append(
5790 ["/comments", "../comments" + str(comment_id) + ".xml"]
5791 )
5793 count = len(comments)
5794 start_data_id = vml_data_id
5796 # The VML o:idmap data id contains a comma separated range when there
5797 # is more than one 1024 block of comments, like this: data="1,2".
5798 for i in range(int(count / 1024)):
5799 data_id = start_data_id + i + 1
5800 vml_data_id = f"{vml_data_id},{data_id}"
5802 self.vml_data_id = vml_data_id
5803 self.vml_shape_id = vml_shape_id
5805 return count
5807 def _prepare_header_vml_objects(self, vml_header_id, vml_drawing_id) -> None:
5808 # Set up external linkage for VML header/footer images.
5810 self.vml_header_id = vml_header_id
5812 self.external_vml_links.append(
5813 ["/vmlDrawing", "../drawings/vmlDrawing" + str(vml_drawing_id) + ".vml"]
5814 )
5816 def _prepare_tables(self, table_id, seen) -> None:
5817 # Set the table ids for the worksheet tables.
5818 for table in self.tables:
5819 table["id"] = table_id
5821 if table.get("name") is None:
5822 # Set a default name.
5823 table["name"] = "Table" + str(table_id)
5825 # Check for duplicate table names.
5826 name = table["name"].lower()
5828 if name in seen:
5829 raise DuplicateTableName(
5830 f"Duplicate name '{table['name']}' used in worksheet.add_table()."
5831 )
5833 seen[name] = True
5835 # Store the link used for the rels file.
5836 self.external_table_links.append(
5837 ["/table", "../tables/table" + str(table_id) + ".xml"]
5838 )
5839 table_id += 1
5841 def _table_function_to_formula(self, function, col_name):
5842 # Convert a table total function to a worksheet formula.
5843 formula = ""
5845 # Escape special characters, as required by Excel.
5846 col_name = col_name.replace("'", "''")
5847 col_name = col_name.replace("#", "'#")
5848 col_name = col_name.replace("]", "']")
5849 col_name = col_name.replace("[", "'[")
5851 subtotals = {
5852 "average": 101,
5853 "countNums": 102,
5854 "count": 103,
5855 "max": 104,
5856 "min": 105,
5857 "stdDev": 107,
5858 "sum": 109,
5859 "var": 110,
5860 }
5862 if function in subtotals:
5863 func_num = subtotals[function]
5864 formula = f"SUBTOTAL({func_num},[{col_name}])"
5865 else:
5866 warn(f"Unsupported function '{function}' in add_table()")
5868 return formula
5870 def _set_spark_color(self, sparkline, options, user_color) -> None:
5871 # Set the sparkline color.
5872 if user_color not in options:
5873 return
5875 sparkline[user_color] = Color._from_value(options[user_color])
5877 def _get_range_data(self, row_start, col_start, row_end, col_end):
5878 # Returns a range of data from the worksheet _table to be used in
5879 # chart cached data. Strings are returned as SST ids and decoded
5880 # in the workbook. Return None for data that doesn't exist since
5881 # Excel can chart series with data missing.
5883 if self.constant_memory:
5884 return ()
5886 data = []
5888 # Iterate through the table data.
5889 for row_num in range(row_start, row_end + 1):
5890 # Store None if row doesn't exist.
5891 if row_num not in self.table:
5892 data.append(None)
5893 continue
5895 for col_num in range(col_start, col_end + 1):
5896 if col_num in self.table[row_num]:
5897 cell = self.table[row_num][col_num]
5899 cell_type = cell.__class__.__name__
5901 if cell_type in ("Number", "Datetime"):
5902 # Return a number with Excel's precision.
5903 data.append(f"{cell.number:.16g}")
5905 elif cell_type == "String":
5906 # Return a string from it's shared string index.
5907 index = cell.string
5908 string = self.str_table._get_shared_string(index)
5910 data.append(string)
5912 elif cell_type in ("Formula", "ArrayFormula"):
5913 # Return the formula value.
5914 value = cell.value
5916 if value is None:
5917 value = 0
5919 data.append(value)
5921 elif cell_type == "Blank":
5922 # Return a empty cell.
5923 data.append("")
5924 else:
5925 # Store None if column doesn't exist.
5926 data.append(None)
5928 return data
5930 def _csv_join(self, *items):
5931 # Create a csv string for use with data validation formulas and lists.
5933 # Convert non string types to string.
5934 items = [str(item) if not isinstance(item, str) else item for item in items]
5936 return ",".join(items)
5938 def _escape_url(self, url):
5939 # Don't escape URL if it looks already escaped.
5940 if re.search("%[0-9a-fA-F]{2}", url):
5941 return url
5943 # Can't use url.quote() here because it doesn't match Excel.
5944 url = url.replace("%", "%25")
5945 url = url.replace('"', "%22")
5946 url = url.replace(" ", "%20")
5947 url = url.replace("<", "%3c")
5948 url = url.replace(">", "%3e")
5949 url = url.replace("[", "%5b")
5950 url = url.replace("]", "%5d")
5951 url = url.replace("^", "%5e")
5952 url = url.replace("`", "%60")
5953 url = url.replace("{", "%7b")
5954 url = url.replace("}", "%7d")
5956 return url
5958 def _get_drawing_rel_index(self, target=None):
5959 # Get the index used to address a drawing rel link.
5960 if target is None:
5961 self.drawing_rels_id += 1
5962 return self.drawing_rels_id
5964 if self.drawing_rels.get(target):
5965 return self.drawing_rels[target]
5967 self.drawing_rels_id += 1
5968 self.drawing_rels[target] = self.drawing_rels_id
5969 return self.drawing_rels_id
5971 def _get_vml_drawing_rel_index(self, target=None):
5972 # Get the index used to address a vml drawing rel link.
5973 if self.vml_drawing_rels.get(target):
5974 return self.vml_drawing_rels[target]
5976 self.vml_drawing_rels_id += 1
5977 self.vml_drawing_rels[target] = self.vml_drawing_rels_id
5978 return self.vml_drawing_rels_id
5980 ###########################################################################
5981 #
5982 # The following font methods are mainly duplicated from the Styles class
5983 # with appropriate changes for rich string styles.
5984 #
5985 ###########################################################################
5986 def _write_font(self, xf_format) -> None:
5987 # Write the <font> element.
5988 xml_writer = self.rstring
5990 xml_writer._xml_start_tag("rPr")
5992 # Handle the main font properties.
5993 if xf_format.bold:
5994 xml_writer._xml_empty_tag("b")
5995 if xf_format.italic:
5996 xml_writer._xml_empty_tag("i")
5997 if xf_format.font_strikeout:
5998 xml_writer._xml_empty_tag("strike")
5999 if xf_format.font_outline:
6000 xml_writer._xml_empty_tag("outline")
6001 if xf_format.font_shadow:
6002 xml_writer._xml_empty_tag("shadow")
6004 # Handle the underline variants.
6005 if xf_format.underline:
6006 self._write_underline(xf_format.underline)
6008 # Handle super/subscript.
6009 if xf_format.font_script == 1:
6010 self._write_vert_align("superscript")
6011 if xf_format.font_script == 2:
6012 self._write_vert_align("subscript")
6014 # Write the font size
6015 xml_writer._xml_empty_tag("sz", [("val", xf_format.font_size)])
6017 # Handle colors.
6018 if xf_format.theme == -1:
6019 # Ignore for excel2003_style.
6020 pass
6021 elif xf_format.theme:
6022 self._write_rstring_color("color", [("theme", xf_format.theme)])
6023 elif xf_format.color_indexed:
6024 self._write_rstring_color("color", [("indexed", xf_format.color_indexed)])
6025 elif xf_format.font_color:
6026 color = xf_format.font_color
6027 if not color._is_automatic:
6028 self._write_rstring_color("color", color._attributes())
6029 else:
6030 self._write_rstring_color("color", [("theme", 1)])
6032 # Write some other font properties related to font families.
6033 xml_writer._xml_empty_tag("rFont", [("val", xf_format.font_name)])
6034 xml_writer._xml_empty_tag("family", [("val", xf_format.font_family)])
6036 if xf_format.font_name == "Calibri" and not xf_format.hyperlink:
6037 xml_writer._xml_empty_tag("scheme", [("val", xf_format.font_scheme)])
6039 xml_writer._xml_end_tag("rPr")
6041 def _write_underline(self, underline) -> None:
6042 # Write the underline font element.
6043 attributes = []
6045 # Handle the underline variants.
6046 if underline == 2:
6047 attributes = [("val", "double")]
6048 elif underline == 33:
6049 attributes = [("val", "singleAccounting")]
6050 elif underline == 34:
6051 attributes = [("val", "doubleAccounting")]
6053 self.rstring._xml_empty_tag("u", attributes)
6055 def _write_vert_align(self, val) -> None:
6056 # Write the <vertAlign> font sub-element.
6057 attributes = [("val", val)]
6059 self.rstring._xml_empty_tag("vertAlign", attributes)
6061 def _write_rstring_color(self, name, attributes) -> None:
6062 # Write the <color> element.
6063 self.rstring._xml_empty_tag(name, attributes)
6065 def _opt_close(self) -> None:
6066 # Close the row data filehandle in constant_memory mode.
6067 if not self.row_data_fh_closed:
6068 self.row_data_fh.close()
6069 self.row_data_fh_closed = True
6071 def _opt_reopen(self) -> None:
6072 # Reopen the row data filehandle in constant_memory mode.
6073 if self.row_data_fh_closed:
6074 filename = self.row_data_filename
6075 # pylint: disable=consider-using-with
6076 self.row_data_fh = open(filename, mode="a+", encoding="utf-8")
6077 self.row_data_fh_closed = False
6078 self.fh = self.row_data_fh
6080 def _set_icon_props(self, total_icons, user_props=None):
6081 # Set the sub-properties for icons.
6082 props = []
6084 # Set the defaults.
6085 for _ in range(total_icons):
6086 props.append({"criteria": False, "value": 0, "type": "percent"})
6088 # Set the default icon values based on the number of icons.
6089 if total_icons == 3:
6090 props[0]["value"] = 67
6091 props[1]["value"] = 33
6093 if total_icons == 4:
6094 props[0]["value"] = 75
6095 props[1]["value"] = 50
6096 props[2]["value"] = 25
6098 if total_icons == 5:
6099 props[0]["value"] = 80
6100 props[1]["value"] = 60
6101 props[2]["value"] = 40
6102 props[3]["value"] = 20
6104 # Overwrite default properties with user defined properties.
6105 if user_props:
6106 # Ensure we don't set user properties for lowest icon.
6107 max_data = len(user_props)
6108 if max_data >= total_icons:
6109 max_data = total_icons - 1
6111 for i in range(max_data):
6112 # Set the user defined 'value' property.
6113 if user_props[i].get("value") is not None:
6114 props[i]["value"] = user_props[i]["value"]
6116 # Remove the formula '=' sign if it exists.
6117 tmp = props[i]["value"]
6118 if isinstance(tmp, str) and tmp.startswith("="):
6119 props[i]["value"] = tmp.lstrip("=")
6121 # Set the user defined 'type' property.
6122 if user_props[i].get("type"):
6123 valid_types = ("percent", "percentile", "number", "formula")
6125 if user_props[i]["type"] not in valid_types:
6126 warn(
6127 f"Unknown icon property type '{user_props[i]['type']}' "
6128 f"for sub-property 'type' in conditional_format()."
6129 )
6130 else:
6131 props[i]["type"] = user_props[i]["type"]
6133 if props[i]["type"] == "number":
6134 props[i]["type"] = "num"
6136 # Set the user defined 'criteria' property.
6137 criteria = user_props[i].get("criteria")
6138 if criteria and criteria == ">":
6139 props[i]["criteria"] = True
6141 return props
6143 ###########################################################################
6144 #
6145 # XML methods.
6146 #
6147 ###########################################################################
6149 def _write_worksheet(self) -> None:
6150 # Write the <worksheet> element. This is the root element.
6152 schema = "http://schemas.openxmlformats.org/"
6153 xmlns = schema + "spreadsheetml/2006/main"
6154 xmlns_r = schema + "officeDocument/2006/relationships"
6155 xmlns_mc = schema + "markup-compatibility/2006"
6156 ms_schema = "http://schemas.microsoft.com/"
6157 xmlns_x14ac = ms_schema + "office/spreadsheetml/2009/9/ac"
6159 attributes = [("xmlns", xmlns), ("xmlns:r", xmlns_r)]
6161 # Add some extra attributes for Excel 2010. Mainly for sparklines.
6162 if self.excel_version == 2010:
6163 attributes.append(("xmlns:mc", xmlns_mc))
6164 attributes.append(("xmlns:x14ac", xmlns_x14ac))
6165 attributes.append(("mc:Ignorable", "x14ac"))
6167 self._xml_start_tag("worksheet", attributes)
6169 def _write_dimension(self) -> None:
6170 # Write the <dimension> element. This specifies the range of
6171 # cells in the worksheet. As a special case, empty
6172 # spreadsheets use 'A1' as a range.
6174 if self.dim_rowmin is None and self.dim_colmin is None:
6175 # If the min dimensions are not defined then no dimensions
6176 # have been set and we use the default 'A1'.
6177 ref = "A1"
6179 elif self.dim_rowmin is None and self.dim_colmin is not None:
6180 # If the row dimensions aren't set but the column
6181 # dimensions are set then they have been changed via
6182 # set_column().
6184 if self.dim_colmin == self.dim_colmax:
6185 # The dimensions are a single cell and not a range.
6186 ref = xl_rowcol_to_cell(0, self.dim_colmin)
6187 else:
6188 # The dimensions are a cell range.
6189 cell_1 = xl_rowcol_to_cell(0, self.dim_colmin)
6190 cell_2 = xl_rowcol_to_cell(0, self.dim_colmax)
6191 ref = cell_1 + ":" + cell_2
6193 elif self.dim_rowmin == self.dim_rowmax and self.dim_colmin == self.dim_colmax:
6194 # The dimensions are a single cell and not a range.
6195 ref = xl_rowcol_to_cell(self.dim_rowmin, self.dim_colmin)
6196 else:
6197 # The dimensions are a cell range.
6198 cell_1 = xl_rowcol_to_cell(self.dim_rowmin, self.dim_colmin)
6199 cell_2 = xl_rowcol_to_cell(self.dim_rowmax, self.dim_colmax)
6200 ref = cell_1 + ":" + cell_2
6202 self._xml_empty_tag("dimension", [("ref", ref)])
6204 def _write_sheet_views(self) -> None:
6205 # Write the <sheetViews> element.
6206 self._xml_start_tag("sheetViews")
6208 # Write the sheetView element.
6209 self._write_sheet_view()
6211 self._xml_end_tag("sheetViews")
6213 def _write_sheet_view(self) -> None:
6214 # Write the <sheetViews> element.
6215 attributes = []
6217 # Hide screen gridlines if required.
6218 if not self.screen_gridlines:
6219 attributes.append(("showGridLines", 0))
6221 # Hide screen row/column headers.
6222 if self.row_col_headers:
6223 attributes.append(("showRowColHeaders", 0))
6225 # Hide zeroes in cells.
6226 if not self.show_zeros:
6227 attributes.append(("showZeros", 0))
6229 # Display worksheet right to left for Hebrew, Arabic and others.
6230 if self.is_right_to_left:
6231 attributes.append(("rightToLeft", 1))
6233 # Show that the sheet tab is selected.
6234 if self.selected:
6235 attributes.append(("tabSelected", 1))
6237 # Turn outlines off. Also required in the outlinePr element.
6238 if not self.outline_on:
6239 attributes.append(("showOutlineSymbols", 0))
6241 # Set the page view/layout mode if required.
6242 if self.page_view == 1:
6243 attributes.append(("view", "pageLayout"))
6244 elif self.page_view == 2:
6245 attributes.append(("view", "pageBreakPreview"))
6247 # Set the first visible cell.
6248 if self.top_left_cell != "":
6249 attributes.append(("topLeftCell", self.top_left_cell))
6251 # Set the zoom level.
6252 if self.zoom != 100:
6253 attributes.append(("zoomScale", self.zoom))
6255 if self.page_view == 0 and self.zoom_scale_normal:
6256 attributes.append(("zoomScaleNormal", self.zoom))
6257 if self.page_view == 1:
6258 attributes.append(("zoomScalePageLayoutView", self.zoom))
6259 if self.page_view == 2:
6260 attributes.append(("zoomScaleSheetLayoutView", self.zoom))
6262 attributes.append(("workbookViewId", 0))
6264 if self.panes or self.selections:
6265 self._xml_start_tag("sheetView", attributes)
6266 self._write_panes()
6267 self._write_selections()
6268 self._xml_end_tag("sheetView")
6269 else:
6270 self._xml_empty_tag("sheetView", attributes)
6272 def _write_sheet_format_pr(self) -> None:
6273 # Write the <sheetFormatPr> element.
6274 default_row_height = self.default_row_height
6275 row_level = self.outline_row_level
6276 col_level = self.outline_col_level
6278 attributes = [("defaultRowHeight", default_row_height)]
6280 if self.default_row_height != self.original_row_height:
6281 attributes.append(("customHeight", 1))
6283 if self.default_row_zeroed:
6284 attributes.append(("zeroHeight", 1))
6286 if row_level:
6287 attributes.append(("outlineLevelRow", row_level))
6288 if col_level:
6289 attributes.append(("outlineLevelCol", col_level))
6291 if self.excel_version == 2010:
6292 attributes.append(("x14ac:dyDescent", "0.25"))
6294 self._xml_empty_tag("sheetFormatPr", attributes)
6296 def _write_cols(self) -> None:
6297 # Write the <cols> element and <col> sub elements.
6299 # Exit unless some column have been formatted.
6300 if not self.col_info:
6301 return
6303 self._xml_start_tag("cols")
6305 # Use the first element of the column information structures to set
6306 # the initial/previous properties.
6307 first_col = (sorted(self.col_info.keys()))[0]
6308 last_col = first_col
6309 prev_col_options = self.col_info[first_col]
6310 del self.col_info[first_col]
6311 deleted_col = first_col
6312 deleted_col_options = prev_col_options
6314 for col in sorted(self.col_info.keys()):
6315 col_options = self.col_info[col]
6316 # Check if the column number is contiguous with the previous
6317 # column and if the properties are the same.
6318 if col == last_col + 1 and col_options == prev_col_options:
6319 last_col = col
6320 else:
6321 # If not contiguous/equal then we write out the current range
6322 # of columns and start again.
6323 self._write_col_info(first_col, last_col, prev_col_options)
6324 first_col = col
6325 last_col = first_col
6326 prev_col_options = col_options
6328 # We will exit the previous loop with one unhandled column range.
6329 self._write_col_info(first_col, last_col, prev_col_options)
6331 # Put back the deleted first column information structure.
6332 self.col_info[deleted_col] = deleted_col_options
6334 self._xml_end_tag("cols")
6336 def _write_col_info(self, col_min, col_max, col_info) -> None:
6337 # Write the <col> element.
6338 (width, cell_format, hidden, level, collapsed, autofit) = col_info
6340 custom_width = 1
6341 xf_index = 0
6343 # Get the cell_format index.
6344 if cell_format:
6345 xf_index = cell_format._get_xf_index()
6347 # Set the Excel default column width.
6348 if width is None:
6349 if not hidden:
6350 width = 8.43
6351 custom_width = 0
6352 else:
6353 width = 0
6354 elif width == 8.43:
6355 # Width is defined but same as default.
6356 custom_width = 0
6358 # Convert column width from user units to character width.
6359 if width > 0:
6360 # For Calabri 11.
6361 max_digit_width = 7
6362 padding = 5
6364 if width < 1:
6365 width = (
6366 int(
6367 (int(width * (max_digit_width + padding) + 0.5))
6368 / float(max_digit_width)
6369 * 256.0
6370 )
6371 / 256.0
6372 )
6373 else:
6374 width = (
6375 int(
6376 (int(width * max_digit_width + 0.5) + padding)
6377 / float(max_digit_width)
6378 * 256.0
6379 )
6380 / 256.0
6381 )
6383 attributes = [
6384 ("min", col_min + 1),
6385 ("max", col_max + 1),
6386 ("width", f"{width:.16g}"),
6387 ]
6389 if xf_index:
6390 attributes.append(("style", xf_index))
6391 if hidden:
6392 attributes.append(("hidden", "1"))
6393 if autofit:
6394 attributes.append(("bestFit", "1"))
6395 if custom_width:
6396 attributes.append(("customWidth", "1"))
6397 if level:
6398 attributes.append(("outlineLevel", level))
6399 if collapsed:
6400 attributes.append(("collapsed", "1"))
6402 self._xml_empty_tag("col", attributes)
6404 def _write_sheet_data(self) -> None:
6405 # Write the <sheetData> element.
6406 if self.dim_rowmin is None:
6407 # If the dimensions aren't defined there is no data to write.
6408 self._xml_empty_tag("sheetData")
6409 else:
6410 self._xml_start_tag("sheetData")
6411 self._write_rows()
6412 self._xml_end_tag("sheetData")
6414 def _write_optimized_sheet_data(self) -> None:
6415 # Write the <sheetData> element when constant_memory is on. In this
6416 # case we read the data stored in the temp file and rewrite it to the
6417 # XML sheet file.
6418 if self.dim_rowmin is None:
6419 # If the dimensions aren't defined then there is no data to write.
6420 self._xml_empty_tag("sheetData")
6421 else:
6422 self._xml_start_tag("sheetData")
6424 # Rewind the filehandle that was used for temp row data.
6425 buff_size = 65536
6426 self.row_data_fh.seek(0)
6427 data = self.row_data_fh.read(buff_size)
6429 while data:
6430 self.fh.write(data)
6431 data = self.row_data_fh.read(buff_size)
6433 self.row_data_fh.close()
6434 os.unlink(self.row_data_filename)
6436 self._xml_end_tag("sheetData")
6438 def _write_page_margins(self) -> None:
6439 # Write the <pageMargins> element.
6440 attributes = [
6441 ("left", self.margin_left),
6442 ("right", self.margin_right),
6443 ("top", self.margin_top),
6444 ("bottom", self.margin_bottom),
6445 ("header", self.margin_header),
6446 ("footer", self.margin_footer),
6447 ]
6449 self._xml_empty_tag("pageMargins", attributes)
6451 def _write_page_setup(self) -> None:
6452 # Write the <pageSetup> element.
6453 #
6454 # The following is an example taken from Excel.
6455 #
6456 # <pageSetup
6457 # paperSize="9"
6458 # scale="110"
6459 # fitToWidth="2"
6460 # fitToHeight="2"
6461 # pageOrder="overThenDown"
6462 # orientation="portrait"
6463 # blackAndWhite="1"
6464 # draft="1"
6465 # horizontalDpi="200"
6466 # verticalDpi="200"
6467 # r:id="rId1"
6468 # />
6469 #
6470 attributes = []
6472 # Skip this element if no page setup has changed.
6473 if not self.page_setup_changed:
6474 return
6476 # Set paper size.
6477 if self.paper_size:
6478 attributes.append(("paperSize", self.paper_size))
6480 # Set the print_scale.
6481 if self.print_scale != 100:
6482 attributes.append(("scale", self.print_scale))
6484 # Set the "Fit to page" properties.
6485 if self.fit_page and self.fit_width != 1:
6486 attributes.append(("fitToWidth", self.fit_width))
6488 if self.fit_page and self.fit_height != 1:
6489 attributes.append(("fitToHeight", self.fit_height))
6491 # Set the page print direction.
6492 if self.page_order:
6493 attributes.append(("pageOrder", "overThenDown"))
6495 # Set start page for printing.
6496 if self.page_start > 1:
6497 attributes.append(("firstPageNumber", self.page_start))
6499 # Set page orientation.
6500 if self.orientation:
6501 attributes.append(("orientation", "portrait"))
6502 else:
6503 attributes.append(("orientation", "landscape"))
6505 # Set the print in black and white option.
6506 if self.black_white:
6507 attributes.append(("blackAndWhite", "1"))
6509 # Set start page for printing.
6510 if self.page_start != 0:
6511 attributes.append(("useFirstPageNumber", "1"))
6513 # Set the DPI. Mainly only for testing.
6514 if self.is_chartsheet:
6515 if self.horizontal_dpi:
6516 attributes.append(("horizontalDpi", self.horizontal_dpi))
6518 if self.vertical_dpi:
6519 attributes.append(("verticalDpi", self.vertical_dpi))
6520 else:
6521 if self.vertical_dpi:
6522 attributes.append(("verticalDpi", self.vertical_dpi))
6524 if self.horizontal_dpi:
6525 attributes.append(("horizontalDpi", self.horizontal_dpi))
6527 self._xml_empty_tag("pageSetup", attributes)
6529 def _write_print_options(self) -> None:
6530 # Write the <printOptions> element.
6531 attributes = []
6533 if not self.print_options_changed:
6534 return
6536 # Set horizontal centering.
6537 if self.hcenter:
6538 attributes.append(("horizontalCentered", 1))
6540 # Set vertical centering.
6541 if self.vcenter:
6542 attributes.append(("verticalCentered", 1))
6544 # Enable row and column headers.
6545 if self.print_headers:
6546 attributes.append(("headings", 1))
6548 # Set printed gridlines.
6549 if self.print_gridlines:
6550 attributes.append(("gridLines", 1))
6552 self._xml_empty_tag("printOptions", attributes)
6554 def _write_header_footer(self) -> None:
6555 # Write the <headerFooter> element.
6556 attributes = []
6558 if not self.header_footer_scales:
6559 attributes.append(("scaleWithDoc", 0))
6561 if not self.header_footer_aligns:
6562 attributes.append(("alignWithMargins", 0))
6564 if self.header_footer_changed:
6565 self._xml_start_tag("headerFooter", attributes)
6566 if self.header:
6567 self._write_odd_header()
6568 if self.footer:
6569 self._write_odd_footer()
6570 self._xml_end_tag("headerFooter")
6571 elif self.excel2003_style:
6572 self._xml_empty_tag("headerFooter", attributes)
6574 def _write_odd_header(self) -> None:
6575 # Write the <headerFooter> element.
6576 self._xml_data_element("oddHeader", self.header)
6578 def _write_odd_footer(self) -> None:
6579 # Write the <headerFooter> element.
6580 self._xml_data_element("oddFooter", self.footer)
6582 def _write_rows(self) -> None:
6583 # Write out the worksheet data as a series of rows and cells.
6584 self._calculate_spans()
6586 for row_num in range(self.dim_rowmin, self.dim_rowmax + 1):
6587 if (
6588 row_num in self.set_rows
6589 or row_num in self.comments
6590 or self.table[row_num]
6591 ):
6592 # Only process rows with formatting, cell data and/or comments.
6594 span_index = int(row_num / 16)
6596 if span_index in self.row_spans:
6597 span = self.row_spans[span_index]
6598 else:
6599 span = None
6601 if self.table[row_num]:
6602 # Write the cells if the row contains data.
6603 if row_num not in self.set_rows:
6604 self._write_row(row_num, span)
6605 else:
6606 self._write_row(row_num, span, self.set_rows[row_num])
6608 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6609 if col_num in self.table[row_num]:
6610 col_ref = self.table[row_num][col_num]
6611 self._write_cell(row_num, col_num, col_ref)
6613 self._xml_end_tag("row")
6615 elif row_num in self.comments:
6616 # Row with comments in cells.
6617 self._write_empty_row(row_num, span, self.set_rows[row_num])
6618 else:
6619 # Blank row with attributes only.
6620 self._write_empty_row(row_num, span, self.set_rows[row_num])
6622 def _write_single_row(self, current_row_num=0) -> None:
6623 # Write out the worksheet data as a single row with cells.
6624 # This method is used when constant_memory is on. A single
6625 # row is written and the data table is reset. That way only
6626 # one row of data is kept in memory at any one time. We don't
6627 # write span data in the optimized case since it is optional.
6629 # Set the new previous row as the current row.
6630 row_num = self.previous_row
6631 self.previous_row = current_row_num
6633 if row_num in self.set_rows or row_num in self.comments or self.table[row_num]:
6634 # Only process rows with formatting, cell data and/or comments.
6636 # No span data in optimized mode.
6637 span = None
6639 if self.table[row_num]:
6640 # Write the cells if the row contains data.
6641 if row_num not in self.set_rows:
6642 self._write_row(row_num, span)
6643 else:
6644 self._write_row(row_num, span, self.set_rows[row_num])
6646 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6647 if col_num in self.table[row_num]:
6648 col_ref = self.table[row_num][col_num]
6649 self._write_cell(row_num, col_num, col_ref)
6651 self._xml_end_tag("row")
6652 else:
6653 # Row attributes or comments only.
6654 self._write_empty_row(row_num, span, self.set_rows[row_num])
6656 # Reset table.
6657 self.table.clear()
6659 def _calculate_spans(self) -> None:
6660 # Calculate the "spans" attribute of the <row> tag. This is an
6661 # XLSX optimization and isn't strictly required. However, it
6662 # makes comparing files easier. The span is the same for each
6663 # block of 16 rows.
6664 spans = {}
6665 span_min = None
6666 span_max = None
6668 for row_num in range(self.dim_rowmin, self.dim_rowmax + 1):
6669 if row_num in self.table:
6670 # Calculate spans for cell data.
6671 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6672 if col_num in self.table[row_num]:
6673 if span_min is None:
6674 span_min = col_num
6675 span_max = col_num
6676 else:
6677 span_min = min(span_min, col_num)
6678 span_max = max(span_max, col_num)
6680 if row_num in self.comments:
6681 # Calculate spans for comments.
6682 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6683 if row_num in self.comments and col_num in self.comments[row_num]:
6684 if span_min is None:
6685 span_min = col_num
6686 span_max = col_num
6687 else:
6688 span_min = min(span_min, col_num)
6689 span_max = max(span_max, col_num)
6691 if ((row_num + 1) % 16 == 0) or row_num == self.dim_rowmax:
6692 span_index = int(row_num / 16)
6694 if span_min is not None:
6695 span_min += 1
6696 span_max += 1
6697 spans[span_index] = f"{span_min}:{span_max}"
6698 span_min = None
6700 self.row_spans = spans
6702 def _write_row(self, row: int, spans, properties=None, empty_row=False) -> None:
6703 # Write the <row> element.
6704 xf_index = 0
6706 if properties:
6707 height, cell_format, hidden, level, collapsed = properties
6708 else:
6709 height, cell_format, hidden, level, collapsed = None, None, 0, 0, 0
6711 if height is None:
6712 height = self.default_row_height
6714 attributes = [("r", row + 1)]
6716 # Get the cell_format index.
6717 if cell_format:
6718 xf_index = cell_format._get_xf_index()
6720 # Add row attributes where applicable.
6721 if spans:
6722 attributes.append(("spans", spans))
6724 if xf_index:
6725 attributes.append(("s", xf_index))
6727 if cell_format:
6728 attributes.append(("customFormat", 1))
6730 if height != self.original_row_height or (
6731 height == self.original_row_height and height != self.default_row_height
6732 ):
6733 attributes.append(("ht", f"{height:g}"))
6735 if hidden:
6736 attributes.append(("hidden", 1))
6738 if height != self.original_row_height or (
6739 height == self.original_row_height and height != self.default_row_height
6740 ):
6741 attributes.append(("customHeight", 1))
6743 if level:
6744 attributes.append(("outlineLevel", level))
6746 if collapsed:
6747 attributes.append(("collapsed", 1))
6749 if self.excel_version == 2010:
6750 attributes.append(("x14ac:dyDescent", "0.25"))
6752 if empty_row:
6753 self._xml_empty_tag_unencoded("row", attributes)
6754 else:
6755 self._xml_start_tag_unencoded("row", attributes)
6757 def _write_empty_row(self, row: int, spans, properties=None) -> None:
6758 # Write and empty <row> element.
6759 self._write_row(row, spans, properties, empty_row=True)
6761 def _write_cell(self, row: int, col: int, cell) -> None:
6762 # Write the <cell> element.
6763 # Note. This is the innermost loop so efficiency is important.
6765 cell_range = xl_rowcol_to_cell_fast(row, col)
6766 attributes = [("r", cell_range)]
6768 if cell.format:
6769 # Add the cell format index.
6770 xf_index = cell.format._get_xf_index()
6771 attributes.append(("s", xf_index))
6772 elif row in self.set_rows and self.set_rows[row][1]:
6773 # Add the row format.
6774 row_xf = self.set_rows[row][1]
6775 attributes.append(("s", row_xf._get_xf_index()))
6776 elif col in self.col_info:
6777 # Add the column format.
6778 col_xf = self.col_info[col][1]
6779 if col_xf is not None:
6780 attributes.append(("s", col_xf._get_xf_index()))
6782 type_cell_name = cell.__class__.__name__
6784 # Write the various cell types.
6785 if type_cell_name in ("Number", "Datetime"):
6786 # Write a number.
6787 self._xml_number_element(cell.number, attributes)
6789 elif type_cell_name in ("String", "RichString"):
6790 # Write a string.
6791 string = cell.string
6793 if not self.constant_memory:
6794 # Write a shared string.
6795 self._xml_string_element(string, attributes)
6796 else:
6797 # Write an optimized in-line string.
6799 # Convert control character to a _xHHHH_ escape.
6800 string = self._escape_control_characters(string)
6802 # Write any rich strings without further tags.
6803 if string.startswith("<r>") and string.endswith("</r>"):
6804 self._xml_rich_inline_string(string, attributes)
6805 else:
6806 # Add attribute to preserve leading or trailing whitespace.
6807 preserve = _preserve_whitespace(string)
6808 self._xml_inline_string(string, preserve, attributes)
6810 elif type_cell_name == "Formula":
6811 # Write a formula. First check the formula value type.
6812 value = cell.value
6813 if isinstance(cell.value, bool):
6814 attributes.append(("t", "b"))
6815 if cell.value:
6816 value = 1
6817 else:
6818 value = 0
6820 elif isinstance(cell.value, str):
6821 error_codes = (
6822 "#DIV/0!",
6823 "#N/A",
6824 "#NAME?",
6825 "#NULL!",
6826 "#NUM!",
6827 "#REF!",
6828 "#VALUE!",
6829 )
6831 if cell.value == "":
6832 # Allow blank to force recalc in some third party apps.
6833 pass
6834 elif cell.value in error_codes:
6835 attributes.append(("t", "e"))
6836 else:
6837 attributes.append(("t", "str"))
6839 self._xml_formula_element(cell.formula, value, attributes)
6841 elif type_cell_name == "ArrayFormula":
6842 # Write a array formula.
6844 if cell.atype == "dynamic":
6845 attributes.append(("cm", 1))
6847 # First check if the formula value is a string.
6848 try:
6849 float(cell.value)
6850 except ValueError:
6851 attributes.append(("t", "str"))
6853 # Write an array formula.
6854 self._xml_start_tag("c", attributes)
6856 self._write_cell_array_formula(cell.formula, cell.range)
6857 self._write_cell_value(cell.value)
6858 self._xml_end_tag("c")
6860 elif type_cell_name == "Blank":
6861 # Write a empty cell.
6862 self._xml_empty_tag("c", attributes)
6864 elif type_cell_name == "Boolean":
6865 # Write a boolean cell.
6866 attributes.append(("t", "b"))
6867 self._xml_start_tag("c", attributes)
6868 self._write_cell_value(cell.boolean)
6869 self._xml_end_tag("c")
6871 elif type_cell_name == "Error":
6872 # Write a boolean cell.
6873 attributes.append(("t", "e"))
6874 attributes.append(("vm", cell.value))
6875 self._xml_start_tag("c", attributes)
6876 self._write_cell_value(cell.error)
6877 self._xml_end_tag("c")
6879 def _write_cell_value(self, value) -> None:
6880 # Write the cell value <v> element.
6881 if value is None:
6882 value = ""
6884 self._xml_data_element("v", value)
6886 def _write_cell_array_formula(self, formula, cell_range) -> None:
6887 # Write the cell array formula <f> element.
6888 attributes = [("t", "array"), ("ref", cell_range)]
6890 self._xml_data_element("f", formula, attributes)
6892 def _write_sheet_pr(self) -> None:
6893 # Write the <sheetPr> element for Sheet level properties.
6894 attributes = []
6896 if (
6897 not self.fit_page
6898 and not self.filter_on
6899 and not self.tab_color
6900 and not self.outline_changed
6901 and not self.vba_codename
6902 ):
6903 return
6905 if self.vba_codename:
6906 attributes.append(("codeName", self.vba_codename))
6908 if self.filter_on:
6909 attributes.append(("filterMode", 1))
6911 if self.fit_page or self.tab_color or self.outline_changed:
6912 self._xml_start_tag("sheetPr", attributes)
6913 self._write_tab_color()
6914 self._write_outline_pr()
6915 self._write_page_set_up_pr()
6916 self._xml_end_tag("sheetPr")
6917 else:
6918 self._xml_empty_tag("sheetPr", attributes)
6920 def _write_page_set_up_pr(self) -> None:
6921 # Write the <pageSetUpPr> element.
6922 if not self.fit_page:
6923 return
6925 attributes = [("fitToPage", 1)]
6926 self._xml_empty_tag("pageSetUpPr", attributes)
6928 def _write_tab_color(self) -> None:
6929 # Write the <tabColor> element.
6930 color = self.tab_color
6932 if not color:
6933 return
6935 self._write_color("tabColor", color._attributes())
6937 def _write_outline_pr(self) -> None:
6938 # Write the <outlinePr> element.
6939 attributes = []
6941 if not self.outline_changed:
6942 return
6944 if self.outline_style:
6945 attributes.append(("applyStyles", 1))
6946 if not self.outline_below:
6947 attributes.append(("summaryBelow", 0))
6948 if not self.outline_right:
6949 attributes.append(("summaryRight", 0))
6950 if not self.outline_on:
6951 attributes.append(("showOutlineSymbols", 0))
6953 self._xml_empty_tag("outlinePr", attributes)
6955 def _write_row_breaks(self) -> None:
6956 # Write the <rowBreaks> element.
6957 page_breaks = self._sort_pagebreaks(self.hbreaks)
6959 if not page_breaks:
6960 return
6962 count = len(page_breaks)
6964 attributes = [
6965 ("count", count),
6966 ("manualBreakCount", count),
6967 ]
6969 self._xml_start_tag("rowBreaks", attributes)
6971 for row_num in page_breaks:
6972 self._write_brk(row_num, 16383)
6974 self._xml_end_tag("rowBreaks")
6976 def _write_col_breaks(self) -> None:
6977 # Write the <colBreaks> element.
6978 page_breaks = self._sort_pagebreaks(self.vbreaks)
6980 if not page_breaks:
6981 return
6983 count = len(page_breaks)
6985 attributes = [
6986 ("count", count),
6987 ("manualBreakCount", count),
6988 ]
6990 self._xml_start_tag("colBreaks", attributes)
6992 for col_num in page_breaks:
6993 self._write_brk(col_num, 1048575)
6995 self._xml_end_tag("colBreaks")
6997 def _write_brk(self, brk_id, brk_max) -> None:
6998 # Write the <brk> element.
6999 attributes = [("id", brk_id), ("max", brk_max), ("man", 1)]
7001 self._xml_empty_tag("brk", attributes)
7003 def _write_merge_cells(self) -> None:
7004 # Write the <mergeCells> element.
7005 merged_cells = self.merge
7006 count = len(merged_cells)
7008 if not count:
7009 return
7011 attributes = [("count", count)]
7013 self._xml_start_tag("mergeCells", attributes)
7015 for merged_range in merged_cells:
7016 # Write the mergeCell element.
7017 self._write_merge_cell(merged_range)
7019 self._xml_end_tag("mergeCells")
7021 def _write_merge_cell(self, merged_range) -> None:
7022 # Write the <mergeCell> element.
7023 (row_min, col_min, row_max, col_max) = merged_range
7025 # Convert the merge dimensions to a cell range.
7026 cell_1 = xl_rowcol_to_cell(row_min, col_min)
7027 cell_2 = xl_rowcol_to_cell(row_max, col_max)
7028 ref = cell_1 + ":" + cell_2
7030 attributes = [("ref", ref)]
7032 self._xml_empty_tag("mergeCell", attributes)
7034 def _write_hyperlinks(self) -> None:
7035 # Process any stored hyperlinks in row/col order and write the
7036 # <hyperlinks> element. The attributes are different for internal
7037 # and external links.
7039 # Sort the hyperlinks into row order.
7040 row_nums = sorted(self.hyperlinks.keys())
7042 # Exit if there are no hyperlinks to process.
7043 if not row_nums:
7044 return
7046 # Write the hyperlink elements.
7047 self._xml_start_tag("hyperlinks")
7049 # Iterate over the rows.
7050 for row_num in row_nums:
7051 # Sort the hyperlinks into column order.
7052 col_nums = sorted(self.hyperlinks[row_num].keys())
7054 # Iterate over the columns.
7055 for col_num in col_nums:
7056 # Get the link data for this cell.
7057 url = self.hyperlinks[row_num][col_num]
7059 # If the cell was overwritten by the user and isn't a string
7060 # then we have to add the url as the string to display.
7061 if self.table and self.table[row_num] and self.table[row_num][col_num]:
7062 cell = self.table[row_num][col_num]
7063 if cell.__class__.__name__ != "String":
7064 url._is_object_link = True
7066 if url._link_type in (UrlTypes.URL, UrlTypes.EXTERNAL):
7067 # External link with rel file relationship.
7068 self.rel_count += 1
7070 self._write_hyperlink_external(
7071 row_num, col_num, self.rel_count, url
7072 )
7074 # Links for use by the packager.
7075 self.external_hyper_links.append(
7076 ["/hyperlink", url._target(), "External"]
7077 )
7078 else:
7079 # Internal link with rel file relationship.
7080 self._write_hyperlink_internal(row_num, col_num, url)
7082 self._xml_end_tag("hyperlinks")
7084 def _write_hyperlink_external(
7085 self, row: int, col: int, id_num: int, url: Url
7086 ) -> None:
7087 # Write the <hyperlink> element for external links.
7088 ref = xl_rowcol_to_cell(row, col)
7089 r_id = "rId" + str(id_num)
7091 attributes = [("ref", ref), ("r:id", r_id)]
7093 if url._anchor:
7094 attributes.append(("location", url._anchor))
7096 if url._is_object_link:
7097 attributes.append(("display", url._text))
7099 if url._tip:
7100 attributes.append(("tooltip", url._tip))
7102 self._xml_empty_tag("hyperlink", attributes)
7104 def _write_hyperlink_internal(self, row: int, col: int, url: Url) -> None:
7105 # Write the <hyperlink> element for internal links.
7106 ref = xl_rowcol_to_cell(row, col)
7108 attributes = [("ref", ref), ("location", url._link)]
7110 if url._tip:
7111 attributes.append(("tooltip", url._tip))
7113 attributes.append(("display", url._text))
7115 self._xml_empty_tag("hyperlink", attributes)
7117 def _write_auto_filter(self) -> None:
7118 # Write the <autoFilter> element.
7119 if not self.autofilter_ref:
7120 return
7122 attributes = [("ref", self.autofilter_ref)]
7124 if self.filter_on:
7125 # Autofilter defined active filters.
7126 self._xml_start_tag("autoFilter", attributes)
7127 self._write_autofilters()
7128 self._xml_end_tag("autoFilter")
7130 else:
7131 # Autofilter defined without active filters.
7132 self._xml_empty_tag("autoFilter", attributes)
7134 def _write_autofilters(self) -> None:
7135 # Function to iterate through the columns that form part of an
7136 # autofilter range and write the appropriate filters.
7137 (col1, col2) = self.filter_range
7139 for col in range(col1, col2 + 1):
7140 # Skip if column doesn't have an active filter.
7141 if col not in self.filter_cols:
7142 continue
7144 # Retrieve the filter tokens and write the autofilter records.
7145 tokens = self.filter_cols[col]
7146 filter_type = self.filter_type[col]
7148 # Filters are relative to first column in the autofilter.
7149 self._write_filter_column(col - col1, filter_type, tokens)
7151 def _write_filter_column(self, col_id, filter_type, filters) -> None:
7152 # Write the <filterColumn> element.
7153 attributes = [("colId", col_id)]
7155 self._xml_start_tag("filterColumn", attributes)
7157 if filter_type == 1:
7158 # Type == 1 is the new XLSX style filter.
7159 self._write_filters(filters)
7160 else:
7161 # Type == 0 is the classic "custom" filter.
7162 self._write_custom_filters(filters)
7164 self._xml_end_tag("filterColumn")
7166 def _write_filters(self, filters) -> None:
7167 # Write the <filters> element.
7168 non_blanks = [filter for filter in filters if str(filter).lower() != "blanks"]
7169 attributes = []
7171 if len(filters) != len(non_blanks):
7172 attributes = [("blank", 1)]
7174 if len(filters) == 1 and len(non_blanks) == 0:
7175 # Special case for blank cells only.
7176 self._xml_empty_tag("filters", attributes)
7177 else:
7178 # General case.
7179 self._xml_start_tag("filters", attributes)
7181 for autofilter in sorted(non_blanks):
7182 self._write_filter(autofilter)
7184 self._xml_end_tag("filters")
7186 def _write_filter(self, val) -> None:
7187 # Write the <filter> element.
7188 attributes = [("val", val)]
7190 self._xml_empty_tag("filter", attributes)
7192 def _write_custom_filters(self, tokens) -> None:
7193 # Write the <customFilters> element.
7194 if len(tokens) == 2:
7195 # One filter expression only.
7196 self._xml_start_tag("customFilters")
7197 self._write_custom_filter(*tokens)
7198 self._xml_end_tag("customFilters")
7199 else:
7200 # Two filter expressions.
7201 attributes = []
7203 # Check if the "join" operand is "and" or "or".
7204 if tokens[2] == 0:
7205 attributes = [("and", 1)]
7206 else:
7207 attributes = [("and", 0)]
7209 # Write the two custom filters.
7210 self._xml_start_tag("customFilters", attributes)
7211 self._write_custom_filter(tokens[0], tokens[1])
7212 self._write_custom_filter(tokens[3], tokens[4])
7213 self._xml_end_tag("customFilters")
7215 def _write_custom_filter(self, operator, val) -> None:
7216 # Write the <customFilter> element.
7217 attributes = []
7219 operators = {
7220 1: "lessThan",
7221 2: "equal",
7222 3: "lessThanOrEqual",
7223 4: "greaterThan",
7224 5: "notEqual",
7225 6: "greaterThanOrEqual",
7226 22: "equal",
7227 }
7229 # Convert the operator from a number to a descriptive string.
7230 if operators[operator] is not None:
7231 operator = operators[operator]
7232 else:
7233 warn(f"Unknown operator = {operator}")
7235 # The 'equal' operator is the default attribute and isn't stored.
7236 if operator != "equal":
7237 attributes.append(("operator", operator))
7238 attributes.append(("val", val))
7240 self._xml_empty_tag("customFilter", attributes)
7242 def _write_sheet_protection(self) -> None:
7243 # Write the <sheetProtection> element.
7244 attributes = []
7246 if not self.protect_options:
7247 return
7249 options = self.protect_options
7251 if options["password"]:
7252 attributes.append(("password", options["password"]))
7253 if options["sheet"]:
7254 attributes.append(("sheet", 1))
7255 if options["content"]:
7256 attributes.append(("content", 1))
7257 if not options["objects"]:
7258 attributes.append(("objects", 1))
7259 if not options["scenarios"]:
7260 attributes.append(("scenarios", 1))
7261 if options["format_cells"]:
7262 attributes.append(("formatCells", 0))
7263 if options["format_columns"]:
7264 attributes.append(("formatColumns", 0))
7265 if options["format_rows"]:
7266 attributes.append(("formatRows", 0))
7267 if options["insert_columns"]:
7268 attributes.append(("insertColumns", 0))
7269 if options["insert_rows"]:
7270 attributes.append(("insertRows", 0))
7271 if options["insert_hyperlinks"]:
7272 attributes.append(("insertHyperlinks", 0))
7273 if options["delete_columns"]:
7274 attributes.append(("deleteColumns", 0))
7275 if options["delete_rows"]:
7276 attributes.append(("deleteRows", 0))
7277 if not options["select_locked_cells"]:
7278 attributes.append(("selectLockedCells", 1))
7279 if options["sort"]:
7280 attributes.append(("sort", 0))
7281 if options["autofilter"]:
7282 attributes.append(("autoFilter", 0))
7283 if options["pivot_tables"]:
7284 attributes.append(("pivotTables", 0))
7285 if not options["select_unlocked_cells"]:
7286 attributes.append(("selectUnlockedCells", 1))
7288 self._xml_empty_tag("sheetProtection", attributes)
7290 def _write_protected_ranges(self) -> None:
7291 # Write the <protectedRanges> element.
7292 if self.num_protected_ranges == 0:
7293 return
7295 self._xml_start_tag("protectedRanges")
7297 for cell_range, range_name, password in self.protected_ranges:
7298 self._write_protected_range(cell_range, range_name, password)
7300 self._xml_end_tag("protectedRanges")
7302 def _write_protected_range(self, cell_range, range_name, password) -> None:
7303 # Write the <protectedRange> element.
7304 attributes = []
7306 if password:
7307 attributes.append(("password", password))
7309 attributes.append(("sqref", cell_range))
7310 attributes.append(("name", range_name))
7312 self._xml_empty_tag("protectedRange", attributes)
7314 def _write_drawings(self) -> None:
7315 # Write the <drawing> elements.
7316 if not self.drawing:
7317 return
7319 self.rel_count += 1
7320 self._write_drawing(self.rel_count)
7322 def _write_drawing(self, drawing_id) -> None:
7323 # Write the <drawing> element.
7324 r_id = "rId" + str(drawing_id)
7326 attributes = [("r:id", r_id)]
7328 self._xml_empty_tag("drawing", attributes)
7330 def _write_legacy_drawing(self) -> None:
7331 # Write the <legacyDrawing> element.
7332 if not self.has_vml:
7333 return
7335 # Increment the relationship id for any drawings or comments.
7336 self.rel_count += 1
7337 r_id = "rId" + str(self.rel_count)
7339 attributes = [("r:id", r_id)]
7341 self._xml_empty_tag("legacyDrawing", attributes)
7343 def _write_legacy_drawing_hf(self) -> None:
7344 # Write the <legacyDrawingHF> element.
7345 if not self.has_header_vml:
7346 return
7348 # Increment the relationship id for any drawings or comments.
7349 self.rel_count += 1
7350 r_id = "rId" + str(self.rel_count)
7352 attributes = [("r:id", r_id)]
7354 self._xml_empty_tag("legacyDrawingHF", attributes)
7356 def _write_picture(self) -> None:
7357 # Write the <picture> element.
7358 if not self.background_image:
7359 return
7361 # Increment the relationship id.
7362 self.rel_count += 1
7363 r_id = "rId" + str(self.rel_count)
7365 attributes = [("r:id", r_id)]
7367 self._xml_empty_tag("picture", attributes)
7369 def _write_data_validations(self) -> None:
7370 # Write the <dataValidations> element.
7371 validations = self.validations
7372 count = len(validations)
7374 if not count:
7375 return
7377 attributes = [("count", count)]
7379 self._xml_start_tag("dataValidations", attributes)
7381 for validation in validations:
7382 # Write the dataValidation element.
7383 self._write_data_validation(validation)
7385 self._xml_end_tag("dataValidations")
7387 def _write_data_validation(self, options) -> None:
7388 # Write the <dataValidation> element.
7389 sqref = ""
7390 attributes = []
7392 # Set the cell range(s) for the data validation.
7393 for cells in options["cells"]:
7394 # Add a space between multiple cell ranges.
7395 if sqref != "":
7396 sqref += " "
7398 (row_first, col_first, row_last, col_last) = cells
7400 # Swap last row/col for first row/col as necessary
7401 if row_first > row_last:
7402 (row_first, row_last) = (row_last, row_first)
7404 if col_first > col_last:
7405 (col_first, col_last) = (col_last, col_first)
7407 sqref += xl_range(row_first, col_first, row_last, col_last)
7409 if options.get("multi_range"):
7410 sqref = options["multi_range"]
7412 if options["validate"] != "none":
7413 attributes.append(("type", options["validate"]))
7415 if options["criteria"] != "between":
7416 attributes.append(("operator", options["criteria"]))
7418 if "error_type" in options:
7419 if options["error_type"] == 1:
7420 attributes.append(("errorStyle", "warning"))
7421 if options["error_type"] == 2:
7422 attributes.append(("errorStyle", "information"))
7424 if options["ignore_blank"]:
7425 attributes.append(("allowBlank", 1))
7427 if not options["dropdown"]:
7428 attributes.append(("showDropDown", 1))
7430 if options["show_input"]:
7431 attributes.append(("showInputMessage", 1))
7433 if options["show_error"]:
7434 attributes.append(("showErrorMessage", 1))
7436 if "error_title" in options:
7437 attributes.append(("errorTitle", options["error_title"]))
7439 if "error_message" in options:
7440 attributes.append(("error", options["error_message"]))
7442 if "input_title" in options:
7443 attributes.append(("promptTitle", options["input_title"]))
7445 if "input_message" in options:
7446 attributes.append(("prompt", options["input_message"]))
7448 attributes.append(("sqref", sqref))
7450 if options["validate"] == "none":
7451 self._xml_empty_tag("dataValidation", attributes)
7452 else:
7453 self._xml_start_tag("dataValidation", attributes)
7455 # Write the formula1 element.
7456 self._write_formula_1(options["value"])
7458 # Write the formula2 element.
7459 if options["maximum"] is not None:
7460 self._write_formula_2(options["maximum"])
7462 self._xml_end_tag("dataValidation")
7464 def _write_formula_1(self, formula) -> None:
7465 # Write the <formula1> element.
7467 if isinstance(formula, list):
7468 formula = self._csv_join(*formula)
7469 formula = f'"{formula}"'
7470 else:
7471 # Check if the formula is a number.
7472 try:
7473 float(formula)
7474 except ValueError:
7475 # Not a number. Remove the formula '=' sign if it exists.
7476 if formula.startswith("="):
7477 formula = formula.lstrip("=")
7479 self._xml_data_element("formula1", formula)
7481 def _write_formula_2(self, formula) -> None:
7482 # Write the <formula2> element.
7484 # Check if the formula is a number.
7485 try:
7486 float(formula)
7487 except ValueError:
7488 # Not a number. Remove the formula '=' sign if it exists.
7489 if formula.startswith("="):
7490 formula = formula.lstrip("=")
7492 self._xml_data_element("formula2", formula)
7494 def _write_conditional_formats(self) -> None:
7495 # Write the Worksheet conditional formats.
7496 ranges = sorted(self.cond_formats.keys())
7498 if not ranges:
7499 return
7501 for cond_range in ranges:
7502 self._write_conditional_formatting(
7503 cond_range, self.cond_formats[cond_range]
7504 )
7506 def _write_conditional_formatting(self, cond_range, params) -> None:
7507 # Write the <conditionalFormatting> element.
7508 attributes = [("sqref", cond_range)]
7509 self._xml_start_tag("conditionalFormatting", attributes)
7510 for param in params:
7511 # Write the cfRule element.
7512 self._write_cf_rule(param)
7513 self._xml_end_tag("conditionalFormatting")
7515 def _write_cf_rule(self, params) -> None:
7516 # Write the <cfRule> element.
7517 attributes = [("type", params["type"])]
7519 if "format" in params and params["format"] is not None:
7520 attributes.append(("dxfId", params["format"]))
7522 attributes.append(("priority", params["priority"]))
7524 if params.get("stop_if_true"):
7525 attributes.append(("stopIfTrue", 1))
7527 if params["type"] == "cellIs":
7528 attributes.append(("operator", params["criteria"]))
7530 self._xml_start_tag("cfRule", attributes)
7532 if "minimum" in params and "maximum" in params:
7533 self._write_formula_element(params["minimum"])
7534 self._write_formula_element(params["maximum"])
7535 else:
7536 self._write_formula_element(params["value"])
7538 self._xml_end_tag("cfRule")
7540 elif params["type"] == "aboveAverage":
7541 if re.search("below", params["criteria"]):
7542 attributes.append(("aboveAverage", 0))
7544 if re.search("equal", params["criteria"]):
7545 attributes.append(("equalAverage", 1))
7547 if re.search("[123] std dev", params["criteria"]):
7548 match = re.search("([123]) std dev", params["criteria"])
7549 attributes.append(("stdDev", match.group(1)))
7551 self._xml_empty_tag("cfRule", attributes)
7553 elif params["type"] == "top10":
7554 if "criteria" in params and params["criteria"] == "%":
7555 attributes.append(("percent", 1))
7557 if "direction" in params:
7558 attributes.append(("bottom", 1))
7560 rank = params["value"] or 10
7561 attributes.append(("rank", rank))
7563 self._xml_empty_tag("cfRule", attributes)
7565 elif params["type"] == "duplicateValues":
7566 self._xml_empty_tag("cfRule", attributes)
7568 elif params["type"] == "uniqueValues":
7569 self._xml_empty_tag("cfRule", attributes)
7571 elif (
7572 params["type"] == "containsText"
7573 or params["type"] == "notContainsText"
7574 or params["type"] == "beginsWith"
7575 or params["type"] == "endsWith"
7576 ):
7577 attributes.append(("operator", params["criteria"]))
7578 attributes.append(("text", params["value"]))
7579 self._xml_start_tag("cfRule", attributes)
7580 self._write_formula_element(params["formula"])
7581 self._xml_end_tag("cfRule")
7583 elif params["type"] == "timePeriod":
7584 attributes.append(("timePeriod", params["criteria"]))
7585 self._xml_start_tag("cfRule", attributes)
7586 self._write_formula_element(params["formula"])
7587 self._xml_end_tag("cfRule")
7589 elif (
7590 params["type"] == "containsBlanks"
7591 or params["type"] == "notContainsBlanks"
7592 or params["type"] == "containsErrors"
7593 or params["type"] == "notContainsErrors"
7594 ):
7595 self._xml_start_tag("cfRule", attributes)
7596 self._write_formula_element(params["formula"])
7597 self._xml_end_tag("cfRule")
7599 elif params["type"] == "colorScale":
7600 self._xml_start_tag("cfRule", attributes)
7601 self._write_color_scale(params)
7602 self._xml_end_tag("cfRule")
7604 elif params["type"] == "dataBar":
7605 self._xml_start_tag("cfRule", attributes)
7606 self._write_data_bar(params)
7608 if params.get("is_data_bar_2010"):
7609 self._write_data_bar_ext(params)
7611 self._xml_end_tag("cfRule")
7613 elif params["type"] == "expression":
7614 self._xml_start_tag("cfRule", attributes)
7615 self._write_formula_element(params["criteria"])
7616 self._xml_end_tag("cfRule")
7618 elif params["type"] == "iconSet":
7619 self._xml_start_tag("cfRule", attributes)
7620 self._write_icon_set(params)
7621 self._xml_end_tag("cfRule")
7623 def _write_formula_element(self, formula) -> None:
7624 # Write the <formula> element.
7626 # Check if the formula is a number.
7627 try:
7628 float(formula)
7629 except ValueError:
7630 # Not a number. Remove the formula '=' sign if it exists.
7631 if formula.startswith("="):
7632 formula = formula.lstrip("=")
7634 self._xml_data_element("formula", formula)
7636 def _write_color_scale(self, param) -> None:
7637 # Write the <colorScale> element.
7639 self._xml_start_tag("colorScale")
7641 self._write_cfvo(param["min_type"], param["min_value"])
7643 if param["mid_type"] is not None:
7644 self._write_cfvo(param["mid_type"], param["mid_value"])
7646 self._write_cfvo(param["max_type"], param["max_value"])
7648 self._write_color("color", param["min_color"]._attributes())
7650 if param["mid_color"] is not None:
7651 self._write_color("color", param["mid_color"]._attributes())
7653 self._write_color("color", param["max_color"]._attributes())
7655 self._xml_end_tag("colorScale")
7657 def _write_data_bar(self, param) -> None:
7658 # Write the <dataBar> element.
7659 attributes = []
7661 # Min and max bar lengths in in the spec but not supported directly by
7662 # Excel.
7663 if "min_length" in param:
7664 attributes.append(("minLength", param["min_length"]))
7666 if "max_length" in param:
7667 attributes.append(("maxLength", param["max_length"]))
7669 if param.get("bar_only"):
7670 attributes.append(("showValue", 0))
7672 self._xml_start_tag("dataBar", attributes)
7674 self._write_cfvo(param["min_type"], param["min_value"])
7675 self._write_cfvo(param["max_type"], param["max_value"])
7676 self._write_color("color", param["bar_color"]._attributes())
7678 self._xml_end_tag("dataBar")
7680 def _write_data_bar_ext(self, param) -> None:
7681 # Write the <extLst> dataBar extension element.
7683 # Create a pseudo GUID for each unique Excel 2010 data bar.
7684 worksheet_count = self.index + 1
7685 data_bar_count = len(self.data_bars_2010) + 1
7686 guid = "{DA7ABA51-AAAA-BBBB-%04X-%012X}" % (worksheet_count, data_bar_count)
7688 # Store the 2010 data bar parameters to write the extLst elements.
7689 param["guid"] = guid
7690 self.data_bars_2010.append(param)
7692 self._xml_start_tag("extLst")
7693 self._write_ext("{B025F937-C7B1-47D3-B67F-A62EFF666E3E}")
7694 self._xml_data_element("x14:id", guid)
7695 self._xml_end_tag("ext")
7696 self._xml_end_tag("extLst")
7698 def _write_icon_set(self, param) -> None:
7699 # Write the <iconSet> element.
7700 attributes = []
7702 # Don't set attribute for default style.
7703 if param["icon_style"] != "3TrafficLights":
7704 attributes = [("iconSet", param["icon_style"])]
7706 if param.get("icons_only"):
7707 attributes.append(("showValue", 0))
7709 if param.get("reverse_icons"):
7710 attributes.append(("reverse", 1))
7712 self._xml_start_tag("iconSet", attributes)
7714 # Write the properties for different icon styles.
7715 for icon in reversed(param["icons"]):
7716 self._write_cfvo(icon["type"], icon["value"], icon["criteria"])
7718 self._xml_end_tag("iconSet")
7720 def _write_cfvo(self, cf_type, val, criteria=None) -> None:
7721 # Write the <cfvo> element.
7722 attributes = [("type", cf_type)]
7724 if val is not None:
7725 attributes.append(("val", val))
7727 if criteria:
7728 attributes.append(("gte", 0))
7730 self._xml_empty_tag("cfvo", attributes)
7732 def _write_color(self, name, attributes) -> None:
7733 # Write the <color> element.
7734 self._xml_empty_tag(name, attributes)
7736 def _write_selections(self) -> None:
7737 # Write the <selection> elements.
7738 for selection in self.selections:
7739 self._write_selection(*selection)
7741 def _write_selection(self, pane, active_cell, sqref) -> None:
7742 # Write the <selection> element.
7743 attributes = []
7745 if pane:
7746 attributes.append(("pane", pane))
7748 if active_cell:
7749 attributes.append(("activeCell", active_cell))
7751 if sqref:
7752 attributes.append(("sqref", sqref))
7754 self._xml_empty_tag("selection", attributes)
7756 def _write_panes(self) -> None:
7757 # Write the frozen or split <pane> elements.
7758 panes = self.panes
7760 if not panes:
7761 return
7763 if panes[4] == 2:
7764 self._write_split_panes(*panes)
7765 else:
7766 self._write_freeze_panes(*panes)
7768 def _write_freeze_panes(
7769 self, row: int, col: int, top_row, left_col, pane_type
7770 ) -> None:
7771 # Write the <pane> element for freeze panes.
7772 attributes = []
7774 y_split = row
7775 x_split = col
7776 top_left_cell = xl_rowcol_to_cell(top_row, left_col)
7777 active_pane = ""
7778 state = ""
7779 active_cell = ""
7780 sqref = ""
7782 # Move user cell selection to the panes.
7783 if self.selections:
7784 (_, active_cell, sqref) = self.selections[0]
7785 self.selections = []
7787 # Set the active pane.
7788 if row and col:
7789 active_pane = "bottomRight"
7791 row_cell = xl_rowcol_to_cell(row, 0)
7792 col_cell = xl_rowcol_to_cell(0, col)
7794 self.selections.append(["topRight", col_cell, col_cell])
7795 self.selections.append(["bottomLeft", row_cell, row_cell])
7796 self.selections.append(["bottomRight", active_cell, sqref])
7798 elif col:
7799 active_pane = "topRight"
7800 self.selections.append(["topRight", active_cell, sqref])
7802 else:
7803 active_pane = "bottomLeft"
7804 self.selections.append(["bottomLeft", active_cell, sqref])
7806 # Set the pane type.
7807 if pane_type == 0:
7808 state = "frozen"
7809 elif pane_type == 1:
7810 state = "frozenSplit"
7811 else:
7812 state = "split"
7814 if x_split:
7815 attributes.append(("xSplit", x_split))
7817 if y_split:
7818 attributes.append(("ySplit", y_split))
7820 attributes.append(("topLeftCell", top_left_cell))
7821 attributes.append(("activePane", active_pane))
7822 attributes.append(("state", state))
7824 self._xml_empty_tag("pane", attributes)
7826 def _write_split_panes(self, row: int, col: int, top_row, left_col, _) -> None:
7827 # Write the <pane> element for split panes.
7828 attributes = []
7829 has_selection = False
7830 active_pane = ""
7831 active_cell = ""
7832 sqref = ""
7834 y_split = row
7835 x_split = col
7837 # Move user cell selection to the panes.
7838 if self.selections:
7839 (_, active_cell, sqref) = self.selections[0]
7840 self.selections = []
7841 has_selection = True
7843 # Convert the row and col to 1/20 twip units with padding.
7844 if y_split:
7845 y_split = int(20 * y_split + 300)
7847 if x_split:
7848 x_split = self._calculate_x_split_width(x_split)
7850 # For non-explicit topLeft definitions, estimate the cell offset based
7851 # on the pixels dimensions. This is only a workaround and doesn't take
7852 # adjusted cell dimensions into account.
7853 if top_row == row and left_col == col:
7854 top_row = int(0.5 + (y_split - 300) / 20 / 15)
7855 left_col = int(0.5 + (x_split - 390) / 20 / 3 * 4 / 64)
7857 top_left_cell = xl_rowcol_to_cell(top_row, left_col)
7859 # If there is no selection set the active cell to the top left cell.
7860 if not has_selection:
7861 active_cell = top_left_cell
7862 sqref = top_left_cell
7864 # Set the Cell selections.
7865 if row and col:
7866 active_pane = "bottomRight"
7868 row_cell = xl_rowcol_to_cell(top_row, 0)
7869 col_cell = xl_rowcol_to_cell(0, left_col)
7871 self.selections.append(["topRight", col_cell, col_cell])
7872 self.selections.append(["bottomLeft", row_cell, row_cell])
7873 self.selections.append(["bottomRight", active_cell, sqref])
7875 elif col:
7876 active_pane = "topRight"
7877 self.selections.append(["topRight", active_cell, sqref])
7879 else:
7880 active_pane = "bottomLeft"
7881 self.selections.append(["bottomLeft", active_cell, sqref])
7883 # Format splits to the same precision as Excel.
7884 if x_split:
7885 attributes.append(("xSplit", f"{x_split:.16g}"))
7887 if y_split:
7888 attributes.append(("ySplit", f"{y_split:.16g}"))
7890 attributes.append(("topLeftCell", top_left_cell))
7892 if has_selection:
7893 attributes.append(("activePane", active_pane))
7895 self._xml_empty_tag("pane", attributes)
7897 def _calculate_x_split_width(self, width):
7898 # Convert column width from user units to pane split width.
7900 max_digit_width = 7 # For Calabri 11.
7901 padding = 5
7903 # Convert to pixels.
7904 if width < 1:
7905 pixels = int(width * (max_digit_width + padding) + 0.5)
7906 else:
7907 pixels = int(width * max_digit_width + 0.5) + padding
7909 # Convert to points.
7910 points = pixels * 3 / 4
7912 # Convert to twips (twentieths of a point).
7913 twips = points * 20
7915 # Add offset/padding.
7916 width = twips + 390
7918 return width
7920 def _write_table_parts(self) -> None:
7921 # Write the <tableParts> element.
7922 tables = self.tables
7923 count = len(tables)
7925 # Return if worksheet doesn't contain any tables.
7926 if not count:
7927 return
7929 attributes = [
7930 (
7931 "count",
7932 count,
7933 )
7934 ]
7936 self._xml_start_tag("tableParts", attributes)
7938 for _ in tables:
7939 # Write the tablePart element.
7940 self.rel_count += 1
7941 self._write_table_part(self.rel_count)
7943 self._xml_end_tag("tableParts")
7945 def _write_table_part(self, r_id) -> None:
7946 # Write the <tablePart> element.
7948 r_id = "rId" + str(r_id)
7950 attributes = [
7951 (
7952 "r:id",
7953 r_id,
7954 )
7955 ]
7957 self._xml_empty_tag("tablePart", attributes)
7959 def _write_ext_list(self) -> None:
7960 # Write the <extLst> element for data bars and sparklines.
7961 has_data_bars = len(self.data_bars_2010)
7962 has_sparklines = len(self.sparklines)
7964 if not has_data_bars and not has_sparklines:
7965 return
7967 # Write the extLst element.
7968 self._xml_start_tag("extLst")
7970 if has_data_bars:
7971 self._write_ext_list_data_bars()
7973 if has_sparklines:
7974 self._write_ext_list_sparklines()
7976 self._xml_end_tag("extLst")
7978 def _write_ext_list_data_bars(self) -> None:
7979 # Write the Excel 2010 data_bar subelements.
7980 self._write_ext("{78C0D931-6437-407d-A8EE-F0AAD7539E65}")
7982 self._xml_start_tag("x14:conditionalFormattings")
7984 # Write the Excel 2010 conditional formatting data bar elements.
7985 for data_bar in self.data_bars_2010:
7986 # Write the x14:conditionalFormatting element.
7987 self._write_conditional_formatting_2010(data_bar)
7989 self._xml_end_tag("x14:conditionalFormattings")
7990 self._xml_end_tag("ext")
7992 def _write_conditional_formatting_2010(self, data_bar) -> None:
7993 # Write the <x14:conditionalFormatting> element.
7994 xmlns_xm = "http://schemas.microsoft.com/office/excel/2006/main"
7996 attributes = [("xmlns:xm", xmlns_xm)]
7998 self._xml_start_tag("x14:conditionalFormatting", attributes)
8000 # Write the x14:cfRule element.
8001 self._write_x14_cf_rule(data_bar)
8003 # Write the x14:dataBar element.
8004 self._write_x14_data_bar(data_bar)
8006 # Write the x14 max and min data bars.
8007 self._write_x14_cfvo(data_bar["x14_min_type"], data_bar["min_value"])
8008 self._write_x14_cfvo(data_bar["x14_max_type"], data_bar["max_value"])
8010 if not data_bar["bar_no_border"]:
8011 # Write the x14:borderColor element.
8012 self._write_x14_border_color(data_bar["bar_border_color"])
8014 # Write the x14:negativeFillColor element.
8015 if not data_bar["bar_negative_color_same"]:
8016 self._write_x14_negative_fill_color(data_bar["bar_negative_color"])
8018 # Write the x14:negativeBorderColor element.
8019 if (
8020 not data_bar["bar_no_border"]
8021 and not data_bar["bar_negative_border_color_same"]
8022 ):
8023 self._write_x14_negative_border_color(data_bar["bar_negative_border_color"])
8025 # Write the x14:axisColor element.
8026 if data_bar["bar_axis_position"] != "none":
8027 self._write_x14_axis_color(data_bar["bar_axis_color"])
8029 self._xml_end_tag("x14:dataBar")
8030 self._xml_end_tag("x14:cfRule")
8032 # Write the xm:sqref element.
8033 self._xml_data_element("xm:sqref", data_bar["range"])
8035 self._xml_end_tag("x14:conditionalFormatting")
8037 def _write_x14_cf_rule(self, data_bar) -> None:
8038 # Write the <x14:cfRule> element.
8039 rule_type = "dataBar"
8040 guid = data_bar["guid"]
8041 attributes = [("type", rule_type), ("id", guid)]
8043 self._xml_start_tag("x14:cfRule", attributes)
8045 def _write_x14_data_bar(self, data_bar) -> None:
8046 # Write the <x14:dataBar> element.
8047 min_length = 0
8048 max_length = 100
8050 attributes = [
8051 ("minLength", min_length),
8052 ("maxLength", max_length),
8053 ]
8055 if not data_bar["bar_no_border"]:
8056 attributes.append(("border", 1))
8058 if data_bar["bar_solid"]:
8059 attributes.append(("gradient", 0))
8061 if data_bar["bar_direction"] == "left":
8062 attributes.append(("direction", "leftToRight"))
8064 if data_bar["bar_direction"] == "right":
8065 attributes.append(("direction", "rightToLeft"))
8067 if data_bar["bar_negative_color_same"]:
8068 attributes.append(("negativeBarColorSameAsPositive", 1))
8070 if (
8071 not data_bar["bar_no_border"]
8072 and not data_bar["bar_negative_border_color_same"]
8073 ):
8074 attributes.append(("negativeBarBorderColorSameAsPositive", 0))
8076 if data_bar["bar_axis_position"] == "middle":
8077 attributes.append(("axisPosition", "middle"))
8079 if data_bar["bar_axis_position"] == "none":
8080 attributes.append(("axisPosition", "none"))
8082 self._xml_start_tag("x14:dataBar", attributes)
8084 def _write_x14_cfvo(self, rule_type, value) -> None:
8085 # Write the <x14:cfvo> element.
8086 attributes = [("type", rule_type)]
8088 if rule_type in ("min", "max", "autoMin", "autoMax"):
8089 self._xml_empty_tag("x14:cfvo", attributes)
8090 else:
8091 self._xml_start_tag("x14:cfvo", attributes)
8092 self._xml_data_element("xm:f", value)
8093 self._xml_end_tag("x14:cfvo")
8095 def _write_x14_border_color(self, color) -> None:
8096 # Write the <x14:borderColor> element.
8097 self._write_color("x14:borderColor", color._attributes())
8099 def _write_x14_negative_fill_color(self, color) -> None:
8100 # Write the <x14:negativeFillColor> element.
8101 self._xml_empty_tag("x14:negativeFillColor", color._attributes())
8103 def _write_x14_negative_border_color(self, color) -> None:
8104 # Write the <x14:negativeBorderColor> element.
8105 self._xml_empty_tag("x14:negativeBorderColor", color._attributes())
8107 def _write_x14_axis_color(self, color) -> None:
8108 # Write the <x14:axisColor> element.
8109 self._xml_empty_tag("x14:axisColor", color._attributes())
8111 def _write_ext_list_sparklines(self) -> None:
8112 # Write the sparkline extension sub-elements.
8113 self._write_ext("{05C60535-1F16-4fd2-B633-F4F36F0B64E0}")
8115 # Write the x14:sparklineGroups element.
8116 self._write_sparkline_groups()
8118 # Write the sparkline elements.
8119 for sparkline in reversed(self.sparklines):
8120 # Write the x14:sparklineGroup element.
8121 self._write_sparkline_group(sparkline)
8123 # Write the x14:colorSeries element.
8124 self._write_color_series(sparkline["series_color"])
8126 # Write the x14:colorNegative element.
8127 self._write_color_negative(sparkline["negative_color"])
8129 # Write the x14:colorAxis element.
8130 self._write_color_axis()
8132 # Write the x14:colorMarkers element.
8133 self._write_color_markers(sparkline["markers_color"])
8135 # Write the x14:colorFirst element.
8136 self._write_color_first(sparkline["first_color"])
8138 # Write the x14:colorLast element.
8139 self._write_color_last(sparkline["last_color"])
8141 # Write the x14:colorHigh element.
8142 self._write_color_high(sparkline["high_color"])
8144 # Write the x14:colorLow element.
8145 self._write_color_low(sparkline["low_color"])
8147 if sparkline["date_axis"]:
8148 self._xml_data_element("xm:f", sparkline["date_axis"])
8150 self._write_sparklines(sparkline)
8152 self._xml_end_tag("x14:sparklineGroup")
8154 self._xml_end_tag("x14:sparklineGroups")
8155 self._xml_end_tag("ext")
8157 def _write_sparklines(self, sparkline) -> None:
8158 # Write the <x14:sparklines> element and <x14:sparkline> sub-elements.
8160 # Write the sparkline elements.
8161 self._xml_start_tag("x14:sparklines")
8163 for i in range(sparkline["count"]):
8164 spark_range = sparkline["ranges"][i]
8165 location = sparkline["locations"][i]
8167 self._xml_start_tag("x14:sparkline")
8168 self._xml_data_element("xm:f", spark_range)
8169 self._xml_data_element("xm:sqref", location)
8170 self._xml_end_tag("x14:sparkline")
8172 self._xml_end_tag("x14:sparklines")
8174 def _write_ext(self, uri) -> None:
8175 # Write the <ext> element.
8176 schema = "http://schemas.microsoft.com/office/"
8177 xmlns_x14 = schema + "spreadsheetml/2009/9/main"
8179 attributes = [
8180 ("xmlns:x14", xmlns_x14),
8181 ("uri", uri),
8182 ]
8184 self._xml_start_tag("ext", attributes)
8186 def _write_sparkline_groups(self) -> None:
8187 # Write the <x14:sparklineGroups> element.
8188 xmlns_xm = "http://schemas.microsoft.com/office/excel/2006/main"
8190 attributes = [("xmlns:xm", xmlns_xm)]
8192 self._xml_start_tag("x14:sparklineGroups", attributes)
8194 def _write_sparkline_group(self, options) -> None:
8195 # Write the <x14:sparklineGroup> element.
8196 #
8197 # Example for order.
8198 #
8199 # <x14:sparklineGroup
8200 # manualMax="0"
8201 # manualMin="0"
8202 # lineWeight="2.25"
8203 # type="column"
8204 # dateAxis="1"
8205 # displayEmptyCellsAs="span"
8206 # markers="1"
8207 # high="1"
8208 # low="1"
8209 # first="1"
8210 # last="1"
8211 # negative="1"
8212 # displayXAxis="1"
8213 # displayHidden="1"
8214 # minAxisType="custom"
8215 # maxAxisType="custom"
8216 # rightToLeft="1">
8217 #
8218 empty = options.get("empty")
8219 attributes = []
8221 if options.get("max") is not None:
8222 if options["max"] == "group":
8223 options["cust_max"] = "group"
8224 else:
8225 attributes.append(("manualMax", options["max"]))
8226 options["cust_max"] = "custom"
8228 if options.get("min") is not None:
8229 if options["min"] == "group":
8230 options["cust_min"] = "group"
8231 else:
8232 attributes.append(("manualMin", options["min"]))
8233 options["cust_min"] = "custom"
8235 # Ignore the default type attribute (line).
8236 if options["type"] != "line":
8237 attributes.append(("type", options["type"]))
8239 if options.get("weight"):
8240 attributes.append(("lineWeight", options["weight"]))
8242 if options.get("date_axis"):
8243 attributes.append(("dateAxis", 1))
8245 if empty:
8246 attributes.append(("displayEmptyCellsAs", empty))
8248 if options.get("markers"):
8249 attributes.append(("markers", 1))
8251 if options.get("high"):
8252 attributes.append(("high", 1))
8254 if options.get("low"):
8255 attributes.append(("low", 1))
8257 if options.get("first"):
8258 attributes.append(("first", 1))
8260 if options.get("last"):
8261 attributes.append(("last", 1))
8263 if options.get("negative"):
8264 attributes.append(("negative", 1))
8266 if options.get("axis"):
8267 attributes.append(("displayXAxis", 1))
8269 if options.get("hidden"):
8270 attributes.append(("displayHidden", 1))
8272 if options.get("cust_min"):
8273 attributes.append(("minAxisType", options["cust_min"]))
8275 if options.get("cust_max"):
8276 attributes.append(("maxAxisType", options["cust_max"]))
8278 if options.get("reverse"):
8279 attributes.append(("rightToLeft", 1))
8281 self._xml_start_tag("x14:sparklineGroup", attributes)
8283 def _write_spark_color(self, tag, color) -> None:
8284 # Helper function for the sparkline color functions below.
8285 if color:
8286 self._write_color(tag, color._attributes())
8288 def _write_color_series(self, color) -> None:
8289 # Write the <x14:colorSeries> element.
8290 self._write_spark_color("x14:colorSeries", color)
8292 def _write_color_negative(self, color) -> None:
8293 # Write the <x14:colorNegative> element.
8294 self._write_spark_color("x14:colorNegative", color)
8296 def _write_color_axis(self) -> None:
8297 # Write the <x14:colorAxis> element.
8298 self._write_spark_color("x14:colorAxis", Color("#000000"))
8300 def _write_color_markers(self, color) -> None:
8301 # Write the <x14:colorMarkers> element.
8302 self._write_spark_color("x14:colorMarkers", color)
8304 def _write_color_first(self, color) -> None:
8305 # Write the <x14:colorFirst> element.
8306 self._write_spark_color("x14:colorFirst", color)
8308 def _write_color_last(self, color) -> None:
8309 # Write the <x14:colorLast> element.
8310 self._write_spark_color("x14:colorLast", color)
8312 def _write_color_high(self, color) -> None:
8313 # Write the <x14:colorHigh> element.
8314 self._write_spark_color("x14:colorHigh", color)
8316 def _write_color_low(self, color) -> None:
8317 # Write the <x14:colorLow> element.
8318 self._write_spark_color("x14:colorLow", color)
8320 def _write_phonetic_pr(self) -> None:
8321 # Write the <phoneticPr> element.
8322 attributes = [
8323 ("fontId", "0"),
8324 ("type", "noConversion"),
8325 ]
8327 self._xml_empty_tag("phoneticPr", attributes)
8329 def _write_ignored_errors(self) -> None:
8330 # Write the <ignoredErrors> element.
8331 if not self.ignored_errors:
8332 return
8334 self._xml_start_tag("ignoredErrors")
8336 if self.ignored_errors.get("number_stored_as_text"):
8337 ignored_range = self.ignored_errors["number_stored_as_text"]
8338 self._write_ignored_error("numberStoredAsText", ignored_range)
8340 if self.ignored_errors.get("eval_error"):
8341 ignored_range = self.ignored_errors["eval_error"]
8342 self._write_ignored_error("evalError", ignored_range)
8344 if self.ignored_errors.get("formula_differs"):
8345 ignored_range = self.ignored_errors["formula_differs"]
8346 self._write_ignored_error("formula", ignored_range)
8348 if self.ignored_errors.get("formula_range"):
8349 ignored_range = self.ignored_errors["formula_range"]
8350 self._write_ignored_error("formulaRange", ignored_range)
8352 if self.ignored_errors.get("formula_unlocked"):
8353 ignored_range = self.ignored_errors["formula_unlocked"]
8354 self._write_ignored_error("unlockedFormula", ignored_range)
8356 if self.ignored_errors.get("empty_cell_reference"):
8357 ignored_range = self.ignored_errors["empty_cell_reference"]
8358 self._write_ignored_error("emptyCellReference", ignored_range)
8360 if self.ignored_errors.get("list_data_validation"):
8361 ignored_range = self.ignored_errors["list_data_validation"]
8362 self._write_ignored_error("listDataValidation", ignored_range)
8364 if self.ignored_errors.get("calculated_column"):
8365 ignored_range = self.ignored_errors["calculated_column"]
8366 self._write_ignored_error("calculatedColumn", ignored_range)
8368 if self.ignored_errors.get("two_digit_text_year"):
8369 ignored_range = self.ignored_errors["two_digit_text_year"]
8370 self._write_ignored_error("twoDigitTextYear", ignored_range)
8372 self._xml_end_tag("ignoredErrors")
8374 def _write_ignored_error(self, error_type, ignored_range) -> None:
8375 # Write the <ignoredError> element.
8376 attributes = [
8377 ("sqref", ignored_range),
8378 (error_type, 1),
8379 ]
8381 self._xml_empty_tag("ignoredError", attributes)