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 = True
306 self.zoom_to_fit = False
307 self.print_scale = 100
308 self.is_right_to_left = False
309 self.show_zeros = 1
310 self.leading_zeros = 0
312 self.outline_row_level = 0
313 self.outline_col_level = 0
314 self.outline_style = 0
315 self.outline_below = 1
316 self.outline_right = 1
317 self.outline_on = 1
318 self.outline_changed = False
320 self.original_row_height = 15
321 self.default_row_height = 15
322 self.default_row_pixels = 20
323 self.default_col_width = 8.43
324 self.default_col_pixels = 64
325 self.default_date_pixels = 68
326 self.default_row_zeroed = 0
328 self.names = {}
329 self.write_match = []
330 self.table = defaultdict(dict)
331 self.merge = []
332 self.merged_cells = {}
333 self.table_cells = {}
334 self.row_spans = {}
336 self.has_vml = False
337 self.has_header_vml = False
338 self.has_comments = False
339 self.comments = defaultdict(dict)
340 self.comments_list = []
341 self.comments_author = ""
342 self.comments_visible = False
343 self.vml_shape_id = 1024
344 self.buttons_list = []
345 self.vml_header_id = 0
347 self.autofilter_area = ""
348 self.autofilter_ref = None
349 self.filter_range = [0, 9]
350 self.filter_on = 0
351 self.filter_cols = {}
352 self.filter_type = {}
353 self.filter_cells = {}
355 self.row_sizes = {}
356 self.col_size_changed = False
357 self.row_size_changed = False
359 self.last_shape_id = 1
360 self.rel_count = 0
361 self.hlink_count = 0
362 self.hlink_refs = []
363 self.external_hyper_links = []
364 self.external_drawing_links = []
365 self.external_comment_links = []
366 self.external_vml_links = []
367 self.external_table_links = []
368 self.external_background_links = []
369 self.drawing_links = []
370 self.vml_drawing_links = []
371 self.charts = []
372 self.images = []
373 self.tables = []
374 self.sparklines = []
375 self.shapes = []
376 self.shape_hash = {}
377 self.drawing = 0
378 self.drawing_rels = {}
379 self.drawing_rels_id = 0
380 self.vml_drawing_rels = {}
381 self.vml_drawing_rels_id = 0
382 self.background_image = None
384 self.rstring = ""
385 self.previous_row = 0
387 self.validations = []
388 self.cond_formats = {}
389 self.data_bars_2010 = []
390 self.use_data_bars_2010 = False
391 self.dxf_priority = 1
392 self.page_view = 0
394 self.vba_codename = None
396 self.date_1904 = False
397 self.hyperlinks = defaultdict(dict)
399 self.strings_to_numbers = False
400 self.strings_to_urls = True
401 self.nan_inf_to_errors = False
402 self.strings_to_formulas = True
404 self.default_date_format = None
405 self.default_url_format = None
406 self.default_checkbox_format = None
407 self.workbook_add_format = None
408 self.remove_timezone = False
409 self.max_url_length = 2079
411 self.row_data_filename = None
412 self.row_data_fh = None
413 self.worksheet_meta = None
414 self.vml_data_id = None
415 self.vml_shape_id = None
417 self.row_data_filename = None
418 self.row_data_fh = None
419 self.row_data_fh_closed = False
421 self.vertical_dpi = 0
422 self.horizontal_dpi = 0
424 self.write_handlers = {}
426 self.ignored_errors = None
428 self.has_dynamic_arrays = False
429 self.use_future_functions = False
430 self.ignore_write_string = False
431 self.embedded_images = None
433 # Utility function for writing different types of strings.
434 def _write_token_as_string(self, token, row: int, col: int, *args):
435 # Map the data to the appropriate write_*() method.
436 if token == "":
437 return self._write_blank(row, col, *args)
439 if self.strings_to_formulas and token.startswith("="):
440 return self._write_formula(row, col, *args)
442 if token.startswith("{=") and token.endswith("}"):
443 return self._write_formula(row, col, *args)
445 # pylint: disable=too-many-boolean-expressions
446 if (
447 ":" in token
448 and self.strings_to_urls
449 and (
450 re.match("(ftp|http)s?://", token)
451 or re.match("mailto:", token)
452 or re.match("(in|ex)ternal:", token)
453 or re.match("file://", token)
454 )
455 ):
456 return self._write_url(row, col, *args)
458 if self.strings_to_numbers:
459 try:
460 f = float(token)
461 if self.nan_inf_to_errors or (not isnan(f) and not isinf(f)):
462 return self._write_number(row, col, f, *args[1:])
463 except ValueError:
464 # Not a number, write as a string.
465 pass
467 return self._write_string(row, col, *args)
469 # We have a plain string.
470 return self._write_string(row, col, *args)
472 @convert_cell_args
473 def write(self, row: int, col: int, *args) -> Union[Literal[0, -1], Any]:
474 """
475 Write data to a worksheet cell by calling the appropriate write_*()
476 method based on the type of data being passed.
478 Args:
479 row: The cell row (zero indexed).
480 col: The cell column (zero indexed).
481 *args: Args to pass to sub functions.
483 Returns:
484 0: Success.
485 -1: Row or column is out of worksheet bounds.
486 other: Return value of called method.
488 """
489 return self._write(row, col, *args)
491 # Undecorated version of write().
492 def _write(self, row: int, col: int, *args):
493 # pylint: disable=raise-missing-from
494 # Check the number of args passed.
495 if not args:
496 raise TypeError("write() takes at least 4 arguments (3 given)")
498 # The first arg should be the token for all write calls.
499 token = args[0]
501 # Avoid isinstance() for better performance.
502 token_type = token.__class__
504 # Check for any user defined type handlers with callback functions.
505 if token_type in self.write_handlers:
506 write_handler = self.write_handlers[token_type]
507 function_return = write_handler(self, row, col, *args)
509 # If the return value is None then the callback has returned
510 # control to this function and we should continue as
511 # normal. Otherwise we return the value to the caller and exit.
512 if function_return is None:
513 pass
514 else:
515 return function_return
517 # Write None as a blank cell.
518 if token is None:
519 return self._write_blank(row, col, *args)
521 # Check for standard Python types.
522 if token_type is bool:
523 return self._write_boolean(row, col, *args)
525 if token_type in (float, int, Decimal, Fraction):
526 return self._write_number(row, col, *args)
528 if token_type is str:
529 return self._write_token_as_string(token, row, col, *args)
531 if token_type in (
532 datetime.datetime,
533 datetime.date,
534 datetime.time,
535 datetime.timedelta,
536 ):
537 return self._write_datetime(row, col, *args)
539 # Resort to isinstance() for subclassed primitives.
541 # Write number types.
542 if isinstance(token, (float, int, Decimal, Fraction)):
543 return self._write_number(row, col, *args)
545 # Write string types.
546 if isinstance(token, str):
547 return self._write_token_as_string(token, row, col, *args)
549 # Write boolean types.
550 if isinstance(token, bool):
551 return self._write_boolean(row, col, *args)
553 # Write datetime objects.
554 if _supported_datetime(token):
555 return self._write_datetime(row, col, *args)
557 # Write Url type.
558 if isinstance(token, Url):
559 return self._write_url(row, col, *args)
561 # We haven't matched a supported type. Try float.
562 try:
563 f = float(token)
564 return self._write_number(row, col, f, *args[1:])
565 except ValueError:
566 pass
567 except TypeError:
568 raise TypeError(f"Unsupported type {type(token)} in write()")
570 # Finally try string.
571 try:
572 str(token)
573 return self._write_string(row, col, *args)
574 except ValueError:
575 raise TypeError(f"Unsupported type {type(token)} in write()")
577 @convert_cell_args
578 def write_string(
579 self, row: int, col: int, string: str, cell_format: Optional[Format] = None
580 ) -> Literal[0, -1, -2]:
581 """
582 Write a string to a worksheet cell.
584 Args:
585 row: The cell row (zero indexed).
586 col: The cell column (zero indexed).
587 string: Cell data. Str.
588 format: An optional cell Format object.
590 Returns:
591 0: Success.
592 -1: Row or column is out of worksheet bounds.
593 -2: String truncated to 32k characters.
595 """
596 return self._write_string(row, col, string, cell_format)
598 # Undecorated version of write_string().
599 def _write_string(
600 self, row: int, col: int, string: str, cell_format: Optional[Format] = None
601 ) -> Literal[0, -1, -2]:
602 str_error = 0
604 # Check that row and col are valid and store max and min values.
605 if self._check_dimensions(row, col):
606 return -1
608 # Check that the string is < 32767 chars.
609 if len(string) > self.xls_strmax:
610 string = string[: self.xls_strmax]
611 str_error = -2
613 # Write a shared string or an in-line string in constant_memory mode.
614 if not self.constant_memory:
615 string_index = self.str_table._get_shared_string_index(string)
616 else:
617 string_index = string
619 # Write previous row if in in-line string constant_memory mode.
620 if self.constant_memory and row > self.previous_row:
621 self._write_single_row(row)
623 # Store the cell data in the worksheet data table.
624 self.table[row][col] = CellStringTuple(string_index, cell_format)
626 return str_error
628 @convert_cell_args
629 def write_number(
630 self,
631 row: int,
632 col: int,
633 number: Union[int, float, Fraction],
634 cell_format: Optional[Format] = None,
635 ) -> Literal[0, -1]:
636 """
637 Write a number to a worksheet cell.
639 Args:
640 row: The cell row (zero indexed).
641 col: The cell column (zero indexed).
642 number: Cell data. Int or float.
643 cell_format: An optional cell Format object.
645 Returns:
646 0: Success.
647 -1: Row or column is out of worksheet bounds.
649 """
650 return self._write_number(row, col, number, cell_format)
652 # Undecorated version of write_number().
653 def _write_number(
654 self,
655 row: int,
656 col: int,
657 number: Union[int, float, Fraction],
658 cell_format: Optional[Format] = None,
659 ) -> Literal[0, -1]:
660 if isnan(number) or isinf(number):
661 if self.nan_inf_to_errors:
662 if isnan(number):
663 return self._write_formula(row, col, "#NUM!", cell_format, "#NUM!")
665 if number == math.inf:
666 return self._write_formula(row, col, "1/0", cell_format, "#DIV/0!")
668 if number == -math.inf:
669 return self._write_formula(row, col, "-1/0", cell_format, "#DIV/0!")
670 else:
671 raise TypeError(
672 "NAN/INF not supported in write_number() "
673 "without 'nan_inf_to_errors' Workbook() option"
674 )
676 if number.__class__ is Fraction:
677 number = float(number)
679 # Check that row and col are valid and store max and min values.
680 if self._check_dimensions(row, col):
681 return -1
683 # Write previous row if in in-line string constant_memory mode.
684 if self.constant_memory and row > self.previous_row:
685 self._write_single_row(row)
687 # Store the cell data in the worksheet data table.
688 self.table[row][col] = CellNumberTuple(number, cell_format)
690 return 0
692 @convert_cell_args
693 def write_blank(
694 self, row: int, col: int, blank: Any, cell_format: Optional[Format] = None
695 ):
696 """
697 Write a blank cell with formatting to a worksheet cell. The blank
698 token is ignored and the format only is written to the cell.
700 Args:
701 row: The cell row (zero indexed).
702 col: The cell column (zero indexed).
703 blank: Any value. It is ignored.
704 cell_format: An optional cell Format object.
706 Returns:
707 0: Success.
708 -1: Row or column is out of worksheet bounds.
710 """
711 return self._write_blank(row, col, blank, cell_format)
713 # Undecorated version of write_blank().
714 def _write_blank(
715 self, row: int, col: int, _, cell_format: Optional[Format] = None
716 ) -> Literal[0, -1]:
717 # Don't write a blank cell unless it has a format.
718 if cell_format is None:
719 return 0
721 # Check that row and col are valid and store max and min values.
722 if self._check_dimensions(row, col):
723 return -1
725 # Write previous row if in in-line string constant_memory mode.
726 if self.constant_memory and row > self.previous_row:
727 self._write_single_row(row)
729 # Store the cell data in the worksheet data table.
730 self.table[row][col] = CellBlankTuple(cell_format)
732 return 0
734 @convert_cell_args
735 def write_formula(
736 self,
737 row: int,
738 col: int,
739 formula: str,
740 cell_format: Optional[Format] = None,
741 value=0,
742 ) -> Literal[0, -1, -2]:
743 """
744 Write a formula to a worksheet cell.
746 Args:
747 row: The cell row (zero indexed).
748 col: The cell column (zero indexed).
749 formula: Cell formula.
750 cell_format: An optional cell Format object.
751 value: An optional value for the formula. Default is 0.
753 Returns:
754 0: Success.
755 -1: Row or column is out of worksheet bounds.
756 -2: Formula can't be None or empty.
758 """
759 # Check that row and col are valid and store max and min values.
760 return self._write_formula(row, col, formula, cell_format, value)
762 # Undecorated version of write_formula().
763 def _write_formula(
764 self,
765 row: int,
766 col: int,
767 formula: str,
768 cell_format: Optional[Format] = None,
769 value=0,
770 ) -> Literal[0, -1, -2]:
771 if self._check_dimensions(row, col):
772 return -1
774 if formula is None or formula == "":
775 warn("Formula can't be None or empty")
776 return -1
778 # Check for dynamic array functions.
779 if re_dynamic_function.search(formula):
780 return self.write_dynamic_array_formula(
781 row, col, row, col, formula, cell_format, value
782 )
784 # Hand off array formulas.
785 if formula.startswith("{") and formula.endswith("}"):
786 return self._write_array_formula(
787 row, col, row, col, formula, cell_format, value
788 )
790 # Modify the formula string, as needed.
791 formula = self._prepare_formula(formula)
793 # Write previous row if in in-line string constant_memory mode.
794 if self.constant_memory and row > self.previous_row:
795 self._write_single_row(row)
797 # Store the cell data in the worksheet data table.
798 self.table[row][col] = CellFormulaTuple(formula, cell_format, value)
800 return 0
802 @convert_range_args
803 def write_array_formula(
804 self,
805 first_row: int,
806 first_col: int,
807 last_row: int,
808 last_col: int,
809 formula: str,
810 cell_format: Optional[Format] = None,
811 value=0,
812 ) -> Literal[0, -1]:
813 """
814 Write a formula to a worksheet cell/range.
816 Args:
817 first_row: The first row of the cell range. (zero indexed).
818 first_col: The first column of the cell range.
819 last_row: The last row of the cell range. (zero indexed).
820 last_col: The last column of the cell range.
821 formula: Cell formula.
822 cell_format: An optional cell Format object.
823 value: An optional value for the formula. Default is 0.
825 Returns:
826 0: Success.
827 -1: Row or column is out of worksheet bounds.
829 """
830 # Check for dynamic array functions.
831 if re_dynamic_function.search(formula):
832 return self.write_dynamic_array_formula(
833 first_row, first_col, last_row, last_col, formula, cell_format, value
834 )
836 return self._write_array_formula(
837 first_row,
838 first_col,
839 last_row,
840 last_col,
841 formula,
842 cell_format,
843 value,
844 "static",
845 )
847 @convert_range_args
848 def write_dynamic_array_formula(
849 self,
850 first_row: int,
851 first_col: int,
852 last_row: int,
853 last_col: int,
854 formula: str,
855 cell_format: Optional[Format] = None,
856 value=0,
857 ) -> Literal[0, -1]:
858 """
859 Write a dynamic array formula to a worksheet cell/range.
861 Args:
862 first_row: The first row of the cell range. (zero indexed).
863 first_col: The first column of the cell range.
864 last_row: The last row of the cell range. (zero indexed).
865 last_col: The last column of the cell range.
866 formula: Cell formula.
867 cell_format: An optional cell Format object.
868 value: An optional value for the formula. Default is 0.
870 Returns:
871 0: Success.
872 -1: Row or column is out of worksheet bounds.
874 """
875 error = self._write_array_formula(
876 first_row,
877 first_col,
878 last_row,
879 last_col,
880 formula,
881 cell_format,
882 value,
883 "dynamic",
884 )
886 if error == 0:
887 self.has_dynamic_arrays = True
889 return error
891 # Utility method to strip equal sign and array braces from a formula and
892 # also expand out future and dynamic array formulas.
893 def _prepare_formula(self, formula, expand_future_functions=False):
894 # Remove array formula braces and the leading =.
895 if formula.startswith("{"):
896 formula = formula[1:]
897 if formula.startswith("="):
898 formula = formula[1:]
899 if formula.endswith("}"):
900 formula = formula[:-1]
902 # Check if formula is already expanded by the user.
903 if "_xlfn." in formula:
904 return formula
906 # Expand dynamic formulas.
907 formula = re.sub(r"\bANCHORARRAY\(", "_xlfn.ANCHORARRAY(", formula)
908 formula = re.sub(r"\bBYCOL\(", "_xlfn.BYCOL(", formula)
909 formula = re.sub(r"\bBYROW\(", "_xlfn.BYROW(", formula)
910 formula = re.sub(r"\bCHOOSECOLS\(", "_xlfn.CHOOSECOLS(", formula)
911 formula = re.sub(r"\bCHOOSEROWS\(", "_xlfn.CHOOSEROWS(", formula)
912 formula = re.sub(r"\bDROP\(", "_xlfn.DROP(", formula)
913 formula = re.sub(r"\bEXPAND\(", "_xlfn.EXPAND(", formula)
914 formula = re.sub(r"\bFILTER\(", "_xlfn._xlws.FILTER(", formula)
915 formula = re.sub(r"\bHSTACK\(", "_xlfn.HSTACK(", formula)
916 formula = re.sub(r"\bLAMBDA\(", "_xlfn.LAMBDA(", formula)
917 formula = re.sub(r"\bMAKEARRAY\(", "_xlfn.MAKEARRAY(", formula)
918 formula = re.sub(r"\bMAP\(", "_xlfn.MAP(", formula)
919 formula = re.sub(r"\bRANDARRAY\(", "_xlfn.RANDARRAY(", formula)
920 formula = re.sub(r"\bREDUCE\(", "_xlfn.REDUCE(", formula)
921 formula = re.sub(r"\bSCAN\(", "_xlfn.SCAN(", formula)
922 formula = re.sub(r"\SINGLE\(", "_xlfn.SINGLE(", formula)
923 formula = re.sub(r"\bSEQUENCE\(", "_xlfn.SEQUENCE(", formula)
924 formula = re.sub(r"\bSORT\(", "_xlfn._xlws.SORT(", formula)
925 formula = re.sub(r"\bSORTBY\(", "_xlfn.SORTBY(", formula)
926 formula = re.sub(r"\bSWITCH\(", "_xlfn.SWITCH(", formula)
927 formula = re.sub(r"\bTAKE\(", "_xlfn.TAKE(", formula)
928 formula = re.sub(r"\bTEXTSPLIT\(", "_xlfn.TEXTSPLIT(", formula)
929 formula = re.sub(r"\bTOCOL\(", "_xlfn.TOCOL(", formula)
930 formula = re.sub(r"\bTOROW\(", "_xlfn.TOROW(", formula)
931 formula = re.sub(r"\bUNIQUE\(", "_xlfn.UNIQUE(", formula)
932 formula = re.sub(r"\bVSTACK\(", "_xlfn.VSTACK(", formula)
933 formula = re.sub(r"\bWRAPCOLS\(", "_xlfn.WRAPCOLS(", formula)
934 formula = re.sub(r"\bWRAPROWS\(", "_xlfn.WRAPROWS(", formula)
935 formula = re.sub(r"\bXLOOKUP\(", "_xlfn.XLOOKUP(", formula)
937 if not self.use_future_functions and not expand_future_functions:
938 return formula
940 formula = re.sub(r"\bACOTH\(", "_xlfn.ACOTH(", formula)
941 formula = re.sub(r"\bACOT\(", "_xlfn.ACOT(", formula)
942 formula = re.sub(r"\bAGGREGATE\(", "_xlfn.AGGREGATE(", formula)
943 formula = re.sub(r"\bARABIC\(", "_xlfn.ARABIC(", formula)
944 formula = re.sub(r"\bARRAYTOTEXT\(", "_xlfn.ARRAYTOTEXT(", formula)
945 formula = re.sub(r"\bBASE\(", "_xlfn.BASE(", formula)
946 formula = re.sub(r"\bBETA.DIST\(", "_xlfn.BETA.DIST(", formula)
947 formula = re.sub(r"\bBETA.INV\(", "_xlfn.BETA.INV(", formula)
948 formula = re.sub(r"\bBINOM.DIST.RANGE\(", "_xlfn.BINOM.DIST.RANGE(", formula)
949 formula = re.sub(r"\bBINOM.DIST\(", "_xlfn.BINOM.DIST(", formula)
950 formula = re.sub(r"\bBINOM.INV\(", "_xlfn.BINOM.INV(", formula)
951 formula = re.sub(r"\bBITAND\(", "_xlfn.BITAND(", formula)
952 formula = re.sub(r"\bBITLSHIFT\(", "_xlfn.BITLSHIFT(", formula)
953 formula = re.sub(r"\bBITOR\(", "_xlfn.BITOR(", formula)
954 formula = re.sub(r"\bBITRSHIFT\(", "_xlfn.BITRSHIFT(", formula)
955 formula = re.sub(r"\bBITXOR\(", "_xlfn.BITXOR(", formula)
956 formula = re.sub(r"\bCEILING.MATH\(", "_xlfn.CEILING.MATH(", formula)
957 formula = re.sub(r"\bCEILING.PRECISE\(", "_xlfn.CEILING.PRECISE(", formula)
958 formula = re.sub(r"\bCHISQ.DIST.RT\(", "_xlfn.CHISQ.DIST.RT(", formula)
959 formula = re.sub(r"\bCHISQ.DIST\(", "_xlfn.CHISQ.DIST(", formula)
960 formula = re.sub(r"\bCHISQ.INV.RT\(", "_xlfn.CHISQ.INV.RT(", formula)
961 formula = re.sub(r"\bCHISQ.INV\(", "_xlfn.CHISQ.INV(", formula)
962 formula = re.sub(r"\bCHISQ.TEST\(", "_xlfn.CHISQ.TEST(", formula)
963 formula = re.sub(r"\bCOMBINA\(", "_xlfn.COMBINA(", formula)
964 formula = re.sub(r"\bCONCAT\(", "_xlfn.CONCAT(", formula)
965 formula = re.sub(r"\bCONFIDENCE.NORM\(", "_xlfn.CONFIDENCE.NORM(", formula)
966 formula = re.sub(r"\bCONFIDENCE.T\(", "_xlfn.CONFIDENCE.T(", formula)
967 formula = re.sub(r"\bCOTH\(", "_xlfn.COTH(", formula)
968 formula = re.sub(r"\bCOT\(", "_xlfn.COT(", formula)
969 formula = re.sub(r"\bCOVARIANCE.P\(", "_xlfn.COVARIANCE.P(", formula)
970 formula = re.sub(r"\bCOVARIANCE.S\(", "_xlfn.COVARIANCE.S(", formula)
971 formula = re.sub(r"\bCSCH\(", "_xlfn.CSCH(", formula)
972 formula = re.sub(r"\bCSC\(", "_xlfn.CSC(", formula)
973 formula = re.sub(r"\bDAYS\(", "_xlfn.DAYS(", formula)
974 formula = re.sub(r"\bDECIMAL\(", "_xlfn.DECIMAL(", formula)
975 formula = re.sub(r"\bERF.PRECISE\(", "_xlfn.ERF.PRECISE(", formula)
976 formula = re.sub(r"\bERFC.PRECISE\(", "_xlfn.ERFC.PRECISE(", formula)
977 formula = re.sub(r"\bEXPON.DIST\(", "_xlfn.EXPON.DIST(", formula)
978 formula = re.sub(r"\bF.DIST.RT\(", "_xlfn.F.DIST.RT(", formula)
979 formula = re.sub(r"\bF.DIST\(", "_xlfn.F.DIST(", formula)
980 formula = re.sub(r"\bF.INV.RT\(", "_xlfn.F.INV.RT(", formula)
981 formula = re.sub(r"\bF.INV\(", "_xlfn.F.INV(", formula)
982 formula = re.sub(r"\bF.TEST\(", "_xlfn.F.TEST(", formula)
983 formula = re.sub(r"\bFILTERXML\(", "_xlfn.FILTERXML(", formula)
984 formula = re.sub(r"\bFLOOR.MATH\(", "_xlfn.FLOOR.MATH(", formula)
985 formula = re.sub(r"\bFLOOR.PRECISE\(", "_xlfn.FLOOR.PRECISE(", formula)
986 formula = re.sub(
987 r"\bFORECAST.ETS.CONFINT\(", "_xlfn.FORECAST.ETS.CONFINT(", formula
988 )
989 formula = re.sub(
990 r"\bFORECAST.ETS.SEASONALITY\(", "_xlfn.FORECAST.ETS.SEASONALITY(", formula
991 )
992 formula = re.sub(r"\bFORECAST.ETS.STAT\(", "_xlfn.FORECAST.ETS.STAT(", formula)
993 formula = re.sub(r"\bFORECAST.ETS\(", "_xlfn.FORECAST.ETS(", formula)
994 formula = re.sub(r"\bFORECAST.LINEAR\(", "_xlfn.FORECAST.LINEAR(", formula)
995 formula = re.sub(r"\bFORMULATEXT\(", "_xlfn.FORMULATEXT(", formula)
996 formula = re.sub(r"\bGAMMA.DIST\(", "_xlfn.GAMMA.DIST(", formula)
997 formula = re.sub(r"\bGAMMA.INV\(", "_xlfn.GAMMA.INV(", formula)
998 formula = re.sub(r"\bGAMMALN.PRECISE\(", "_xlfn.GAMMALN.PRECISE(", formula)
999 formula = re.sub(r"\bGAMMA\(", "_xlfn.GAMMA(", formula)
1000 formula = re.sub(r"\bGAUSS\(", "_xlfn.GAUSS(", formula)
1001 formula = re.sub(r"\bHYPGEOM.DIST\(", "_xlfn.HYPGEOM.DIST(", formula)
1002 formula = re.sub(r"\bIFNA\(", "_xlfn.IFNA(", formula)
1003 formula = re.sub(r"\bIFS\(", "_xlfn.IFS(", formula)
1004 formula = re.sub(r"\bIMAGE\(", "_xlfn.IMAGE(", formula)
1005 formula = re.sub(r"\bIMCOSH\(", "_xlfn.IMCOSH(", formula)
1006 formula = re.sub(r"\bIMCOT\(", "_xlfn.IMCOT(", formula)
1007 formula = re.sub(r"\bIMCSCH\(", "_xlfn.IMCSCH(", formula)
1008 formula = re.sub(r"\bIMCSC\(", "_xlfn.IMCSC(", formula)
1009 formula = re.sub(r"\bIMSECH\(", "_xlfn.IMSECH(", formula)
1010 formula = re.sub(r"\bIMSEC\(", "_xlfn.IMSEC(", formula)
1011 formula = re.sub(r"\bIMSINH\(", "_xlfn.IMSINH(", formula)
1012 formula = re.sub(r"\bIMTAN\(", "_xlfn.IMTAN(", formula)
1013 formula = re.sub(r"\bISFORMULA\(", "_xlfn.ISFORMULA(", formula)
1014 formula = re.sub(r"\bISOMITTED\(", "_xlfn.ISOMITTED(", formula)
1015 formula = re.sub(r"\bISOWEEKNUM\(", "_xlfn.ISOWEEKNUM(", formula)
1016 formula = re.sub(r"\bLET\(", "_xlfn.LET(", formula)
1017 formula = re.sub(r"\bLOGNORM.DIST\(", "_xlfn.LOGNORM.DIST(", formula)
1018 formula = re.sub(r"\bLOGNORM.INV\(", "_xlfn.LOGNORM.INV(", formula)
1019 formula = re.sub(r"\bMAXIFS\(", "_xlfn.MAXIFS(", formula)
1020 formula = re.sub(r"\bMINIFS\(", "_xlfn.MINIFS(", formula)
1021 formula = re.sub(r"\bMODE.MULT\(", "_xlfn.MODE.MULT(", formula)
1022 formula = re.sub(r"\bMODE.SNGL\(", "_xlfn.MODE.SNGL(", formula)
1023 formula = re.sub(r"\bMUNIT\(", "_xlfn.MUNIT(", formula)
1024 formula = re.sub(r"\bNEGBINOM.DIST\(", "_xlfn.NEGBINOM.DIST(", formula)
1025 formula = re.sub(r"\bNORM.DIST\(", "_xlfn.NORM.DIST(", formula)
1026 formula = re.sub(r"\bNORM.INV\(", "_xlfn.NORM.INV(", formula)
1027 formula = re.sub(r"\bNORM.S.DIST\(", "_xlfn.NORM.S.DIST(", formula)
1028 formula = re.sub(r"\bNORM.S.INV\(", "_xlfn.NORM.S.INV(", formula)
1029 formula = re.sub(r"\bNUMBERVALUE\(", "_xlfn.NUMBERVALUE(", formula)
1030 formula = re.sub(r"\bPDURATION\(", "_xlfn.PDURATION(", formula)
1031 formula = re.sub(r"\bPERCENTILE.EXC\(", "_xlfn.PERCENTILE.EXC(", formula)
1032 formula = re.sub(r"\bPERCENTILE.INC\(", "_xlfn.PERCENTILE.INC(", formula)
1033 formula = re.sub(r"\bPERCENTRANK.EXC\(", "_xlfn.PERCENTRANK.EXC(", formula)
1034 formula = re.sub(r"\bPERCENTRANK.INC\(", "_xlfn.PERCENTRANK.INC(", formula)
1035 formula = re.sub(r"\bPERMUTATIONA\(", "_xlfn.PERMUTATIONA(", formula)
1036 formula = re.sub(r"\bPHI\(", "_xlfn.PHI(", formula)
1037 formula = re.sub(r"\bPOISSON.DIST\(", "_xlfn.POISSON.DIST(", formula)
1038 formula = re.sub(r"\bQUARTILE.EXC\(", "_xlfn.QUARTILE.EXC(", formula)
1039 formula = re.sub(r"\bQUARTILE.INC\(", "_xlfn.QUARTILE.INC(", formula)
1040 formula = re.sub(r"\bQUERYSTRING\(", "_xlfn.QUERYSTRING(", formula)
1041 formula = re.sub(r"\bRANK.AVG\(", "_xlfn.RANK.AVG(", formula)
1042 formula = re.sub(r"\bRANK.EQ\(", "_xlfn.RANK.EQ(", formula)
1043 formula = re.sub(r"\bRRI\(", "_xlfn.RRI(", formula)
1044 formula = re.sub(r"\bSECH\(", "_xlfn.SECH(", formula)
1045 formula = re.sub(r"\bSEC\(", "_xlfn.SEC(", formula)
1046 formula = re.sub(r"\bSHEETS\(", "_xlfn.SHEETS(", formula)
1047 formula = re.sub(r"\bSHEET\(", "_xlfn.SHEET(", formula)
1048 formula = re.sub(r"\bSKEW.P\(", "_xlfn.SKEW.P(", formula)
1049 formula = re.sub(r"\bSTDEV.P\(", "_xlfn.STDEV.P(", formula)
1050 formula = re.sub(r"\bSTDEV.S\(", "_xlfn.STDEV.S(", formula)
1051 formula = re.sub(r"\bT.DIST.2T\(", "_xlfn.T.DIST.2T(", formula)
1052 formula = re.sub(r"\bT.DIST.RT\(", "_xlfn.T.DIST.RT(", formula)
1053 formula = re.sub(r"\bT.DIST\(", "_xlfn.T.DIST(", formula)
1054 formula = re.sub(r"\bT.INV.2T\(", "_xlfn.T.INV.2T(", formula)
1055 formula = re.sub(r"\bT.INV\(", "_xlfn.T.INV(", formula)
1056 formula = re.sub(r"\bT.TEST\(", "_xlfn.T.TEST(", formula)
1057 formula = re.sub(r"\bTEXTAFTER\(", "_xlfn.TEXTAFTER(", formula)
1058 formula = re.sub(r"\bTEXTBEFORE\(", "_xlfn.TEXTBEFORE(", formula)
1059 formula = re.sub(r"\bTEXTJOIN\(", "_xlfn.TEXTJOIN(", formula)
1060 formula = re.sub(r"\bUNICHAR\(", "_xlfn.UNICHAR(", formula)
1061 formula = re.sub(r"\bUNICODE\(", "_xlfn.UNICODE(", formula)
1062 formula = re.sub(r"\bVALUETOTEXT\(", "_xlfn.VALUETOTEXT(", formula)
1063 formula = re.sub(r"\bVAR.P\(", "_xlfn.VAR.P(", formula)
1064 formula = re.sub(r"\bVAR.S\(", "_xlfn.VAR.S(", formula)
1065 formula = re.sub(r"\bWEBSERVICE\(", "_xlfn.WEBSERVICE(", formula)
1066 formula = re.sub(r"\bWEIBULL.DIST\(", "_xlfn.WEIBULL.DIST(", formula)
1067 formula = re.sub(r"\bXMATCH\(", "_xlfn.XMATCH(", formula)
1068 formula = re.sub(r"\bXOR\(", "_xlfn.XOR(", formula)
1069 formula = re.sub(r"\bZ.TEST\(", "_xlfn.Z.TEST(", formula)
1071 return formula
1073 # Escape/expand table functions. This mainly involves converting Excel 2010
1074 # "@" table ref to 2007 "[#This Row],". We parse the string to avoid
1075 # replacements in string literals within the formula.
1076 @staticmethod
1077 def _prepare_table_formula(formula):
1078 if "@" not in formula:
1079 # No escaping required.
1080 return formula
1082 escaped_formula = []
1083 in_string_literal = False
1085 for char in formula:
1086 # Match the start/end of string literals to avoid escaping
1087 # references in strings.
1088 if char == '"':
1089 in_string_literal = not in_string_literal
1091 # Copy the string literal.
1092 if in_string_literal:
1093 escaped_formula.append(char)
1094 continue
1096 # Replace table reference.
1097 if char == "@":
1098 escaped_formula.append("[#This Row],")
1099 else:
1100 escaped_formula.append(char)
1102 return ("").join(escaped_formula)
1104 # Undecorated version of write_array_formula() and
1105 # write_dynamic_array_formula().
1106 def _write_array_formula(
1107 self,
1108 first_row,
1109 first_col,
1110 last_row,
1111 last_col,
1112 formula,
1113 cell_format=None,
1114 value=0,
1115 atype="static",
1116 ) -> Literal[0, -1]:
1117 # Swap last row/col with first row/col as necessary.
1118 if first_row > last_row:
1119 first_row, last_row = last_row, first_row
1120 if first_col > last_col:
1121 first_col, last_col = last_col, first_col
1123 # Check that row and col are valid and store max and min values.
1124 if self._check_dimensions(first_row, first_col):
1125 return -1
1126 if self._check_dimensions(last_row, last_col):
1127 return -1
1129 # Define array range
1130 if first_row == last_row and first_col == last_col:
1131 cell_range = xl_rowcol_to_cell(first_row, first_col)
1132 else:
1133 cell_range = (
1134 xl_rowcol_to_cell(first_row, first_col)
1135 + ":"
1136 + xl_rowcol_to_cell(last_row, last_col)
1137 )
1139 # Modify the formula string, as needed.
1140 formula = self._prepare_formula(formula)
1142 # Write previous row if in in-line string constant_memory mode.
1143 if self.constant_memory and first_row > self.previous_row:
1144 self._write_single_row(first_row)
1146 # Store the cell data in the worksheet data table.
1147 self.table[first_row][first_col] = CellArrayFormulaTuple(
1148 formula, cell_format, value, cell_range, atype
1149 )
1151 # Pad out the rest of the area with formatted zeroes.
1152 if not self.constant_memory:
1153 for row in range(first_row, last_row + 1):
1154 for col in range(first_col, last_col + 1):
1155 if row != first_row or col != first_col:
1156 self._write_number(row, col, 0, cell_format)
1158 return 0
1160 @convert_cell_args
1161 def write_datetime(
1162 self,
1163 row: int,
1164 col: int,
1165 date: datetime.datetime,
1166 cell_format: Optional[Format] = None,
1167 ) -> Literal[0, -1]:
1168 """
1169 Write a date or time to a worksheet cell.
1171 Args:
1172 row: The cell row (zero indexed).
1173 col: The cell column (zero indexed).
1174 date: Date and/or time as a datetime object.
1175 cell_format: A cell Format object.
1177 Returns:
1178 0: Success.
1179 -1: Row or column is out of worksheet bounds.
1181 """
1182 return self._write_datetime(row, col, date, cell_format)
1184 # Undecorated version of write_datetime().
1185 def _write_datetime(self, row: int, col: int, date, cell_format=None) -> int:
1186 # Check that row and col are valid and store max and min values.
1187 if self._check_dimensions(row, col):
1188 return -1
1190 # Write previous row if in in-line string constant_memory mode.
1191 if self.constant_memory and row > self.previous_row:
1192 self._write_single_row(row)
1194 # Convert datetime to an Excel date.
1195 number = self._convert_date_time(date)
1197 # Add the default date format.
1198 if cell_format is None:
1199 cell_format = self.default_date_format
1201 # Store the cell data in the worksheet data table.
1202 self.table[row][col] = CellDatetimeTuple(number, cell_format)
1204 return 0
1206 @convert_cell_args
1207 def write_boolean(
1208 self, row: int, col: int, boolean: bool, cell_format: Optional[Format] = None
1209 ):
1210 """
1211 Write a boolean value to a worksheet cell.
1213 Args:
1214 row: The cell row (zero indexed).
1215 col: The cell column (zero indexed).
1216 boolean: Cell data. bool type.
1217 cell_format: An optional cell Format object.
1219 Returns:
1220 0: Success.
1221 -1: Row or column is out of worksheet bounds.
1223 """
1224 return self._write_boolean(row, col, boolean, cell_format)
1226 # Undecorated version of write_boolean().
1227 def _write_boolean(self, row: int, col: int, boolean, cell_format=None) -> int:
1228 # Check that row and col are valid and store max and min values.
1229 if self._check_dimensions(row, col):
1230 return -1
1232 # Write previous row if in in-line string constant_memory mode.
1233 if self.constant_memory and row > self.previous_row:
1234 self._write_single_row(row)
1236 if boolean:
1237 value = 1
1238 else:
1239 value = 0
1241 # Store the cell data in the worksheet data table.
1242 self.table[row][col] = CellBooleanTuple(value, cell_format)
1244 return 0
1246 # Write a hyperlink. This is comprised of two elements: the displayed
1247 # string and the non-displayed link. The displayed string is the same as
1248 # the link unless an alternative string is specified. The display string
1249 # is written using the write_string() method. Therefore the max characters
1250 # string limit applies.
1251 #
1252 # The hyperlink can be to a http, ftp, mail, internal sheet, or external
1253 # directory urls.
1254 @convert_cell_args
1255 def write_url(
1256 self,
1257 row: int,
1258 col: int,
1259 url: str,
1260 cell_format: Optional[Format] = None,
1261 string: Optional[str] = None,
1262 tip: Optional[str] = None,
1263 ):
1264 """
1265 Write a hyperlink to a worksheet cell.
1267 Args:
1268 row: The cell row (zero indexed).
1269 col: The cell column (zero indexed).
1270 url: Hyperlink url.
1271 format: An optional cell Format object.
1272 string: An optional display string for the hyperlink.
1273 tip: An optional tooltip.
1274 Returns:
1275 0: Success.
1276 -1: Row or column is out of worksheet bounds.
1277 -2: String longer than 32767 characters.
1278 -3: URL longer than Excel limit of 255 characters.
1279 -4: Exceeds Excel limit of 65,530 urls per worksheet.
1280 """
1281 return self._write_url(row, col, url, cell_format, string, tip)
1283 # Undecorated version of write_url().
1284 def _write_url(
1285 self, row: int, col: int, url, cell_format=None, string=None, tip=None
1286 ) -> int:
1287 # Check that row and col are valid and store max and min values
1288 if self._check_dimensions(row, col):
1289 return -1
1291 # If the URL is a string convert it to a Url object.
1292 if not isinstance(url, Url):
1294 # For backwards compatibility check if the string URL exceeds the
1295 # Excel character limit for URLs and ignore it with a warning.
1296 max_url = self.max_url_length
1297 if "#" in url:
1298 url_str, anchor_str = url.split("#", 1)
1299 else:
1300 url_str = url
1301 anchor_str = ""
1303 if len(url_str) > max_url or len(anchor_str) > max_url:
1304 warn(
1305 f"Ignoring URL '{url}' with link or location/anchor > {max_url} "
1306 f"characters since it exceeds Excel's limit for URLs."
1307 )
1308 return -3
1310 url = Url(url)
1312 if string is not None:
1313 url._text = string
1315 if tip is not None:
1316 url._tip = tip
1318 # Check the limit of URLs per worksheet.
1319 self.hlink_count += 1
1321 if self.hlink_count > 65530:
1322 warn(
1323 f"Ignoring URL '{url._original_url}' since it exceeds Excel's limit of "
1324 f"65,530 URLs per worksheet."
1325 )
1326 return -4
1328 # Add the default URL format.
1329 if cell_format is None:
1330 cell_format = self.default_url_format
1332 if not self.ignore_write_string:
1333 # Write previous row if in in-line string constant_memory mode.
1334 if self.constant_memory and row > self.previous_row:
1335 self._write_single_row(row)
1337 # Write the hyperlink string.
1338 self._write_string(row, col, url.text, cell_format)
1340 # Store the hyperlink data in a separate structure.
1341 self.hyperlinks[row][col] = url
1343 return 0
1345 @convert_cell_args
1346 def write_rich_string(
1347 self, row: int, col: int, *args: Union[str, Format]
1348 ) -> Literal[0, -1, -2, -3, -4, -5]:
1349 """
1350 Write a "rich" string with multiple formats to a worksheet cell.
1352 Args:
1353 row: The cell row (zero indexed).
1354 col: The cell column (zero indexed).
1355 string_parts: String and format pairs.
1356 cell_format: Optional Format object.
1358 Returns:
1359 0: Success.
1360 -1: Row or column is out of worksheet bounds.
1361 -2: String truncated to 32k characters.
1362 -3: 2 consecutive formats used.
1363 -4: Empty string used.
1364 -5: Insufficient parameters.
1366 """
1368 return self._write_rich_string(row, col, *args)
1370 # Undecorated version of write_rich_string().
1371 def _write_rich_string(self, row: int, col: int, *args) -> int:
1372 tokens = list(args)
1373 cell_format = None
1374 string_index = 0
1375 raw_string = ""
1377 # Check that row and col are valid and store max and min values
1378 if self._check_dimensions(row, col):
1379 return -1
1381 # If the last arg is a format we use it as the cell format.
1382 if isinstance(tokens[-1], Format):
1383 cell_format = tokens.pop()
1385 # Create a temp XMLWriter object and use it to write the rich string
1386 # XML to a string.
1387 fh = StringIO()
1388 self.rstring = XMLwriter()
1389 self.rstring._set_filehandle(fh)
1391 # Create a temp format with the default font for unformatted fragments.
1392 default = Format()
1394 # Convert list of format, string tokens to pairs of (format, string)
1395 # except for the first string fragment which doesn't require a default
1396 # formatting run. Use the default for strings without a leading format.
1397 fragments = []
1398 previous = "format"
1399 pos = 0
1401 if len(tokens) <= 2:
1402 warn(
1403 "You must specify more than 2 format/fragments for rich "
1404 "strings. Ignoring input in write_rich_string()."
1405 )
1406 return -5
1408 for token in tokens:
1409 if not isinstance(token, Format):
1410 # Token is a string.
1411 if previous != "format":
1412 # If previous token wasn't a format add one before string.
1413 fragments.append(default)
1414 fragments.append(token)
1415 else:
1416 # If previous token was a format just add the string.
1417 fragments.append(token)
1419 if token == "":
1420 warn(
1421 "Excel doesn't allow empty strings in rich strings. "
1422 "Ignoring input in write_rich_string()."
1423 )
1424 return -4
1426 # Keep track of unformatted string.
1427 raw_string += token
1428 previous = "string"
1429 else:
1430 # Can't allow 2 formats in a row.
1431 if previous == "format" and pos > 0:
1432 warn(
1433 "Excel doesn't allow 2 consecutive formats in rich "
1434 "strings. Ignoring input in write_rich_string()."
1435 )
1436 return -3
1438 # Token is a format object. Add it to the fragment list.
1439 fragments.append(token)
1440 previous = "format"
1442 pos += 1
1444 # If the first token is a string start the <r> element.
1445 if not isinstance(fragments[0], Format):
1446 self.rstring._xml_start_tag("r")
1448 # Write the XML elements for the $format $string fragments.
1449 for token in fragments:
1450 if isinstance(token, Format):
1451 # Write the font run.
1452 self.rstring._xml_start_tag("r")
1453 self._write_font(token)
1454 else:
1455 # Write the string fragment part, with whitespace handling.
1456 attributes = []
1458 if _preserve_whitespace(token):
1459 attributes.append(("xml:space", "preserve"))
1461 self.rstring._xml_data_element("t", token, attributes)
1462 self.rstring._xml_end_tag("r")
1464 # Read the in-memory string.
1465 string = self.rstring.fh.getvalue()
1467 # Check that the string is < 32767 chars.
1468 if len(raw_string) > self.xls_strmax:
1469 warn(
1470 "String length must be less than or equal to Excel's limit "
1471 "of 32,767 characters in write_rich_string()."
1472 )
1473 return -2
1475 # Write a shared string or an in-line string in constant_memory mode.
1476 if not self.constant_memory:
1477 string_index = self.str_table._get_shared_string_index(string)
1478 else:
1479 string_index = string
1481 # Write previous row if in in-line string constant_memory mode.
1482 if self.constant_memory and row > self.previous_row:
1483 self._write_single_row(row)
1485 # Store the cell data in the worksheet data table.
1486 self.table[row][col] = CellRichStringTuple(
1487 string_index, cell_format, raw_string
1488 )
1490 return 0
1492 def add_write_handler(self, user_type, user_function) -> None:
1493 """
1494 Add a callback function to the write() method to handle user defined
1495 types.
1497 Args:
1498 user_type: The user type() to match on.
1499 user_function: The user defined function to write the type data.
1500 Returns:
1501 Nothing.
1503 """
1505 self.write_handlers[user_type] = user_function
1507 @convert_cell_args
1508 def write_row(
1509 self, row: int, col: int, data, cell_format: Optional[Format] = None
1510 ) -> Union[Literal[0], Any]:
1511 """
1512 Write a row of data starting from (row, col).
1514 Args:
1515 row: The cell row (zero indexed).
1516 col: The cell column (zero indexed).
1517 data: A list of tokens to be written with write().
1518 format: An optional cell Format object.
1519 Returns:
1520 0: Success.
1521 other: Return value of write() method.
1523 """
1524 for token in data:
1525 error = self._write(row, col, token, cell_format)
1526 if error:
1527 return error
1528 col += 1
1530 return 0
1532 @convert_cell_args
1533 def write_column(
1534 self, row: int, col: int, data, cell_format: Optional[Format] = None
1535 ) -> Union[Literal[0], Any]:
1536 """
1537 Write a column of data starting from (row, col).
1539 Args:
1540 row: The cell row (zero indexed).
1541 col: The cell column (zero indexed).
1542 data: A list of tokens to be written with write().
1543 format: An optional cell Format object.
1544 Returns:
1545 0: Success.
1546 other: Return value of write() method.
1548 """
1549 for token in data:
1550 error = self._write(row, col, token, cell_format)
1551 if error:
1552 return error
1553 row += 1
1555 return 0
1557 @convert_cell_args
1558 def insert_image(
1559 self,
1560 row: int,
1561 col: int,
1562 source: Union[str, BytesIO, Image],
1563 options: Optional[Dict[str, Any]] = None,
1564 ) -> Literal[0, -1]:
1565 """
1566 Insert an image with its top-left corner in a worksheet cell.
1568 Args:
1569 row: The cell row (zero indexed).
1570 col: The cell column (zero indexed).
1571 source: Filename, BytesIO, or Image object.
1572 options: Position, scale, url and data stream of the image.
1574 Returns:
1575 0: Success.
1576 -1: Row or column is out of worksheet bounds.
1578 """
1579 # Check insert (row, col) without storing.
1580 if self._check_dimensions(row, col, True, True):
1581 warn(f"Cannot insert image at ({row}, {col}).")
1582 return -1
1584 # Convert the source to an Image object.
1585 image = self._image_from_source(source, options)
1587 image._row = row
1588 image._col = col
1589 image._set_user_options(options)
1591 self.images.append(image)
1593 return 0
1595 @convert_cell_args
1596 def embed_image(
1597 self,
1598 row: int,
1599 col: int,
1600 source: Union[str, BytesIO, Image],
1601 options: Optional[Dict[str, Any]] = None,
1602 ) -> Literal[0, -1]:
1603 """
1604 Embed an image in a worksheet cell.
1606 Args:
1607 row: The cell row (zero indexed).
1608 col: The cell column (zero indexed).
1609 source: Filename, BytesIO, or Image object.
1610 options: Url and data stream of the image.
1612 Returns:
1613 0: Success.
1614 -1: Row or column is out of worksheet bounds.
1616 """
1617 # Check insert (row, col) without storing.
1618 if self._check_dimensions(row, col):
1619 warn(f"Cannot embed image at ({row}, {col}).")
1620 return -1
1622 if options is None:
1623 options = {}
1625 # Convert the source to an Image object.
1626 image = self._image_from_source(source, options)
1627 image._set_user_options(options)
1629 cell_format = options.get("cell_format", None)
1631 if image.url:
1632 if cell_format is None:
1633 cell_format = self.default_url_format
1635 self.ignore_write_string = True
1636 self.write_url(row, col, image.url, cell_format)
1637 self.ignore_write_string = False
1639 image_index = self.embedded_images.get_image_index(image)
1641 # Store the cell error and image index in the worksheet data table.
1642 self.table[row][col] = CellErrorTuple("#VALUE!", cell_format, image_index)
1644 return 0
1646 @convert_cell_args
1647 def insert_textbox(
1648 self, row: int, col: int, text: str, options: Optional[Dict[str, Any]] = None
1649 ) -> Literal[0, -1]:
1650 """
1651 Insert an textbox with its top-left corner in a worksheet cell.
1653 Args:
1654 row: The cell row (zero indexed).
1655 col: The cell column (zero indexed).
1656 text: The text for the textbox.
1657 options: Textbox options.
1659 Returns:
1660 0: Success.
1661 -1: Row or column is out of worksheet bounds.
1663 """
1664 # Check insert (row, col) without storing.
1665 if self._check_dimensions(row, col, True, True):
1666 warn(f"Cannot insert textbox at ({row}, {col}).")
1667 return -1
1669 if text is None:
1670 text = ""
1672 if options is None:
1673 options = {}
1675 x_offset = options.get("x_offset", 0)
1676 y_offset = options.get("y_offset", 0)
1677 x_scale = options.get("x_scale", 1)
1678 y_scale = options.get("y_scale", 1)
1679 anchor = options.get("object_position", 1)
1680 description = options.get("description", None)
1681 decorative = options.get("decorative", False)
1683 self.shapes.append(
1684 [
1685 row,
1686 col,
1687 x_offset,
1688 y_offset,
1689 x_scale,
1690 y_scale,
1691 text,
1692 anchor,
1693 options,
1694 description,
1695 decorative,
1696 ]
1697 )
1698 return 0
1700 @convert_cell_args
1701 def insert_chart(
1702 self, row: int, col: int, chart: Chart, options: Optional[Dict[str, Any]] = None
1703 ) -> Literal[0, -1, -2]:
1704 """
1705 Insert an chart with its top-left corner in a worksheet cell.
1707 Args:
1708 row: The cell row (zero indexed).
1709 col: The cell column (zero indexed).
1710 chart: Chart object.
1711 options: Position and scale of the chart.
1713 Returns:
1714 0: Success.
1715 -1: Row or column is out of worksheet bounds.
1717 """
1718 # Check insert (row, col) without storing.
1719 if self._check_dimensions(row, col, True, True):
1720 warn(f"Cannot insert chart at ({row}, {col}).")
1721 return -1
1723 if options is None:
1724 options = {}
1726 # Ensure a chart isn't inserted more than once.
1727 if chart.already_inserted or chart.combined and chart.combined.already_inserted:
1728 warn("Chart cannot be inserted in a worksheet more than once.")
1729 return -2
1731 chart.already_inserted = True
1733 if chart.combined:
1734 chart.combined.already_inserted = True
1736 x_offset = options.get("x_offset", 0)
1737 y_offset = options.get("y_offset", 0)
1738 x_scale = options.get("x_scale", 1)
1739 y_scale = options.get("y_scale", 1)
1740 anchor = options.get("object_position", 1)
1741 description = options.get("description", None)
1742 decorative = options.get("decorative", False)
1744 # Allow Chart to override the scale and offset.
1745 if chart.x_scale != 1:
1746 x_scale = chart.x_scale
1748 if chart.y_scale != 1:
1749 y_scale = chart.y_scale
1751 if chart.x_offset:
1752 x_offset = chart.x_offset
1754 if chart.y_offset:
1755 y_offset = chart.y_offset
1757 self.charts.append(
1758 [
1759 row,
1760 col,
1761 chart,
1762 x_offset,
1763 y_offset,
1764 x_scale,
1765 y_scale,
1766 anchor,
1767 description,
1768 decorative,
1769 ]
1770 )
1771 return 0
1773 @convert_cell_args
1774 def write_comment(
1775 self, row: int, col: int, comment: str, options: Optional[Dict[str, Any]] = None
1776 ) -> Literal[0, -1, -2]:
1777 """
1778 Write a comment to a worksheet cell.
1780 Args:
1781 row: The cell row (zero indexed).
1782 col: The cell column (zero indexed).
1783 comment: Cell comment. Str.
1784 options: Comment formatting options.
1786 Returns:
1787 0: Success.
1788 -1: Row or column is out of worksheet bounds.
1789 -2: String longer than 32k characters.
1791 """
1792 # Check that row and col are valid and store max and min values
1793 if self._check_dimensions(row, col):
1794 return -1
1796 # Check that the comment string is < 32767 chars.
1797 if len(comment) > self.xls_strmax:
1798 return -2
1800 self.has_vml = True
1801 self.has_comments = True
1803 # Store the options of the cell comment, to process on file close.
1804 comment = CommentType(row, col, comment, options)
1805 self.comments[row][col] = comment
1807 return 0
1809 def show_comments(self) -> None:
1810 """
1811 Make any comments in the worksheet visible.
1813 Args:
1814 None.
1816 Returns:
1817 Nothing.
1819 """
1820 self.comments_visible = True
1822 def set_background(
1823 self, source: Union[str, BytesIO, Image], is_byte_stream: bool = False
1824 ) -> Literal[0]:
1825 """
1826 Set a background image for a worksheet.
1828 Args:
1829 source: Filename, BytesIO, or Image object.
1830 is_byte_stream: Deprecated. Use a BytesIO object instead.
1832 Returns:
1833 0: Success.
1835 """
1836 # Convert the source to an Image object.
1837 image = self._image_from_source(source)
1839 self.background_image = image
1841 if is_byte_stream:
1842 warn(
1843 "The `is_byte_stream` parameter in `set_background()` is deprecated. "
1844 "This argument can be omitted if you are using a BytesIO object."
1845 )
1847 return 0
1849 def set_comments_author(self, author) -> None:
1850 """
1851 Set the default author of the cell comments.
1853 Args:
1854 author: Comment author name. String.
1856 Returns:
1857 Nothing.
1859 """
1860 self.comments_author = author
1862 def get_name(self):
1863 """
1864 Retrieve the worksheet name.
1866 Args:
1867 None.
1869 Returns:
1870 Nothing.
1872 """
1873 # There is no set_name() method. Name must be set in add_worksheet().
1874 return self.name
1876 def activate(self) -> None:
1877 """
1878 Set this worksheet as the active worksheet, i.e. the worksheet that is
1879 displayed when the workbook is opened. Also set it as selected.
1881 Note: An active worksheet cannot be hidden.
1883 Args:
1884 None.
1886 Returns:
1887 Nothing.
1889 """
1890 self.hidden = 0
1891 self.selected = 1
1892 self.worksheet_meta.activesheet = self.index
1894 def select(self) -> None:
1895 """
1896 Set current worksheet as a selected worksheet, i.e. the worksheet
1897 has its tab highlighted.
1899 Note: A selected worksheet cannot be hidden.
1901 Args:
1902 None.
1904 Returns:
1905 Nothing.
1907 """
1908 self.selected = 1
1909 self.hidden = 0
1911 def hide(self) -> None:
1912 """
1913 Hide the current worksheet.
1915 Args:
1916 None.
1918 Returns:
1919 Nothing.
1921 """
1922 self.hidden = 1
1924 # A hidden worksheet shouldn't be active or selected.
1925 self.selected = 0
1927 def very_hidden(self) -> None:
1928 """
1929 Hide the current worksheet. This can only be unhidden by VBA.
1931 Args:
1932 None.
1934 Returns:
1935 Nothing.
1937 """
1938 self.hidden = 2
1940 # A hidden worksheet shouldn't be active or selected.
1941 self.selected = 0
1943 def set_first_sheet(self) -> None:
1944 """
1945 Set current worksheet as the first visible sheet. This is necessary
1946 when there are a large number of worksheets and the activated
1947 worksheet is not visible on the screen.
1949 Note: A selected worksheet cannot be hidden.
1951 Args:
1952 None.
1954 Returns:
1955 Nothing.
1957 """
1958 self.hidden = 0 # Active worksheet can't be hidden.
1959 self.worksheet_meta.firstsheet = self.index
1961 @convert_column_args
1962 def set_column(
1963 self,
1964 first_col: int,
1965 last_col: int,
1966 width: Optional[float] = None,
1967 cell_format: Optional[Format] = None,
1968 options: Optional[Dict[str, Any]] = None,
1969 ) -> Literal[0, -1]:
1970 """
1971 Set the width, and other properties of a single column or a
1972 range of columns.
1974 Args:
1975 first_col: First column (zero-indexed).
1976 last_col: Last column (zero-indexed). Can be same as first_col.
1977 width: Column width. (optional).
1978 cell_format: Column cell_format. (optional).
1979 options: Dict of options such as hidden and level.
1981 Returns:
1982 0: Success.
1983 -1: Column number is out of worksheet bounds.
1985 """
1986 if options is None:
1987 options = {}
1989 # Ensure 2nd col is larger than first.
1990 if first_col > last_col:
1991 (first_col, last_col) = (last_col, first_col)
1993 # Don't modify the row dimensions when checking the columns.
1994 ignore_row = True
1996 # Set optional column values.
1997 hidden = options.get("hidden", False)
1998 collapsed = options.get("collapsed", False)
1999 level = options.get("level", 0)
2001 # Store the column dimension only in some conditions.
2002 if cell_format or (width and hidden):
2003 ignore_col = False
2004 else:
2005 ignore_col = True
2007 # Check that each column is valid and store the max and min values.
2008 if self._check_dimensions(0, last_col, ignore_row, ignore_col):
2009 return -1
2010 if self._check_dimensions(0, first_col, ignore_row, ignore_col):
2011 return -1
2013 # Set the limits for the outline levels (0 <= x <= 7).
2014 level = max(level, 0)
2015 level = min(level, 7)
2017 self.outline_col_level = max(self.outline_col_level, level)
2019 # Store the column data.
2020 for col in range(first_col, last_col + 1):
2021 self.col_info[col] = [width, cell_format, hidden, level, collapsed, False]
2023 # Store the column change to allow optimizations.
2024 self.col_size_changed = True
2026 return 0
2028 @convert_column_args
2029 def set_column_pixels(
2030 self,
2031 first_col: int,
2032 last_col: int,
2033 width: Optional[float] = None,
2034 cell_format: Optional[Format] = None,
2035 options: Optional[Dict[str, Any]] = None,
2036 ) -> Literal[0, -1]:
2037 """
2038 Set the width, and other properties of a single column or a
2039 range of columns, where column width is in pixels.
2041 Args:
2042 first_col: First column (zero-indexed).
2043 last_col: Last column (zero-indexed). Can be same as first_col.
2044 width: Column width in pixels. (optional).
2045 cell_format: Column cell_format. (optional).
2046 options: Dict of options such as hidden and level.
2048 Returns:
2049 0: Success.
2050 -1: Column number is out of worksheet bounds.
2052 """
2053 if width is not None:
2054 width = self._pixels_to_width(width)
2056 return self.set_column(first_col, last_col, width, cell_format, options)
2058 def autofit(self, max_width: int = 1790) -> None:
2059 """
2060 Simulate autofit based on the data, and datatypes in each column.
2062 Args:
2063 max_width (optional): max column width to autofit, in pixels.
2065 Returns:
2066 Nothing.
2068 """
2069 # pylint: disable=too-many-nested-blocks
2070 if self.constant_memory:
2071 warn("Autofit is not supported in constant_memory mode.")
2072 return
2074 # No data written to the target sheet; nothing to autofit
2075 if self.dim_rowmax is None:
2076 return
2078 # Store the max pixel width for each column.
2079 col_width_max = {}
2081 # Convert the autofit maximum pixel width to a column/character width,
2082 # but limit it to the Excel max limit.
2083 max_width = min(self._pixels_to_width(max_width), 255.0)
2085 # Create a reverse lookup for the share strings table so we can convert
2086 # the string id back to the original string.
2087 strings = sorted(
2088 self.str_table.string_table, key=self.str_table.string_table.__getitem__
2089 )
2091 for row_num in range(self.dim_rowmin, self.dim_rowmax + 1):
2092 if not self.table.get(row_num):
2093 continue
2095 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
2096 if col_num in self.table[row_num]:
2097 cell = self.table[row_num][col_num]
2098 cell_type = cell.__class__.__name__
2099 length = 0
2101 if cell_type in ("String", "RichString"):
2102 # Handle strings and rich strings.
2103 #
2104 # For standard shared strings we do a reverse lookup
2105 # from the shared string id to the actual string. For
2106 # rich strings we use the unformatted string. We also
2107 # split multi-line strings and handle each part
2108 # separately.
2109 if cell_type == "String":
2110 string_id = cell.string
2111 string = strings[string_id]
2112 else:
2113 string = cell.raw_string
2115 if "\n" not in string:
2116 # Single line string.
2117 length = xl_pixel_width(string)
2118 else:
2119 # Handle multi-line strings.
2120 for string in string.split("\n"):
2121 seg_length = xl_pixel_width(string)
2122 length = max(length, seg_length)
2124 elif cell_type == "Number":
2125 # Handle numbers.
2126 #
2127 # We use a workaround/optimization for numbers since
2128 # digits all have a pixel width of 7. This gives a
2129 # slightly greater width for the decimal place and
2130 # minus sign but only by a few pixels and
2131 # over-estimation is okay.
2132 length = 7 * len(str(cell.number))
2134 elif cell_type == "Datetime":
2135 # Handle dates.
2136 #
2137 # The following uses the default width for mm/dd/yyyy
2138 # dates. It isn't feasible to parse the number format
2139 # to get the actual string width for all format types.
2140 length = self.default_date_pixels
2142 elif cell_type == "Boolean":
2143 # Handle boolean values.
2144 #
2145 # Use the Excel standard widths for TRUE and FALSE.
2146 if cell.boolean:
2147 length = 31
2148 else:
2149 length = 36
2151 elif cell_type in ("Formula", "ArrayFormula"):
2152 # Handle formulas.
2153 #
2154 # We only try to autofit a formula if it has a
2155 # non-zero value.
2156 if isinstance(cell.value, (float, int)):
2157 if cell.value > 0:
2158 length = 7 * len(str(cell.value))
2160 elif isinstance(cell.value, str):
2161 length = xl_pixel_width(cell.value)
2163 elif isinstance(cell.value, bool):
2164 if cell.value:
2165 length = 31
2166 else:
2167 length = 36
2169 # If the cell is in an autofilter header we add an
2170 # additional 16 pixels for the dropdown arrow.
2171 if self.filter_cells.get((row_num, col_num)) and length > 0:
2172 length += 16
2174 # Add the string length to the lookup table.
2175 width_max = col_width_max.get(col_num, 0)
2176 if length > width_max:
2177 col_width_max[col_num] = length
2179 # Apply the width to the column.
2180 for col_num, pixel_width in col_width_max.items():
2181 # Convert the string pixel width to a character width using an
2182 # additional padding of 7 pixels, like Excel.
2183 width = self._pixels_to_width(pixel_width + 7)
2185 # Limit the width to the maximum user or Excel value.
2186 width = min(width, max_width)
2188 # Add the width to an existing col info structure or add a new one.
2189 if self.col_info.get(col_num):
2190 # We only update the width for an existing column if it is
2191 # greater than the user defined value. This allows the user
2192 # to pre-load a minimum col width.
2193 col_info = self.col_info.get(col_num)
2194 user_width = col_info[0]
2195 hidden = col_info[5]
2196 if user_width is not None and not hidden:
2197 # Col info is user defined.
2198 if width > user_width:
2199 self.col_info[col_num][0] = width
2200 self.col_info[col_num][5] = True
2201 else:
2202 self.col_info[col_num][0] = width
2203 self.col_info[col_num][5] = True
2204 else:
2205 self.col_info[col_num] = [width, None, False, 0, False, True]
2207 def set_row(
2208 self,
2209 row: int,
2210 height: Optional[float] = None,
2211 cell_format: Optional[Format] = None,
2212 options: Optional[Dict[str, Any]] = None,
2213 ) -> Literal[0, -1]:
2214 """
2215 Set the width, and other properties of a row.
2217 Args:
2218 row: Row number (zero-indexed).
2219 height: Row height. (optional).
2220 cell_format: Row cell_format. (optional).
2221 options: Dict of options such as hidden, level and collapsed.
2223 Returns:
2224 0: Success.
2225 -1: Row number is out of worksheet bounds.
2227 """
2228 if options is None:
2229 options = {}
2231 # Use minimum col in _check_dimensions().
2232 if self.dim_colmin is not None:
2233 min_col = self.dim_colmin
2234 else:
2235 min_col = 0
2237 # Check that row is valid.
2238 if self._check_dimensions(row, min_col):
2239 return -1
2241 if height is None:
2242 height = self.default_row_height
2244 # Set optional row values.
2245 hidden = options.get("hidden", False)
2246 collapsed = options.get("collapsed", False)
2247 level = options.get("level", 0)
2249 # If the height is 0 the row is hidden and the height is the default.
2250 if height == 0:
2251 hidden = 1
2252 height = self.default_row_height
2254 # Set the limits for the outline levels (0 <= x <= 7).
2255 level = max(level, 0)
2256 level = min(level, 7)
2258 self.outline_row_level = max(self.outline_row_level, level)
2260 # Store the row properties.
2261 self.set_rows[row] = [height, cell_format, hidden, level, collapsed]
2263 # Store the row change to allow optimizations.
2264 self.row_size_changed = True
2266 # Store the row sizes for use when calculating image vertices.
2267 self.row_sizes[row] = [height, hidden]
2269 return 0
2271 def set_row_pixels(
2272 self,
2273 row: int,
2274 height: Optional[float] = None,
2275 cell_format: Optional[Format] = None,
2276 options: Optional[Dict[str, Any]] = None,
2277 ) -> Literal[0, -1]:
2278 """
2279 Set the width (in pixels), and other properties of a row.
2281 Args:
2282 row: Row number (zero-indexed).
2283 height: Row height in pixels. (optional).
2284 cell_format: Row cell_format. (optional).
2285 options: Dict of options such as hidden, level and collapsed.
2287 Returns:
2288 0: Success.
2289 -1: Row number is out of worksheet bounds.
2291 """
2292 if height is not None:
2293 height = self._pixels_to_height(height)
2295 return self.set_row(row, height, cell_format, options)
2297 def set_default_row(
2298 self, height: Optional[float] = None, hide_unused_rows: bool = False
2299 ) -> None:
2300 """
2301 Set the default row properties.
2303 Args:
2304 height: Default height. Optional, defaults to 15.
2305 hide_unused_rows: Hide unused rows. Optional, defaults to False.
2307 Returns:
2308 Nothing.
2310 """
2311 if height is None:
2312 height = self.default_row_height
2314 if height != self.original_row_height:
2315 # Store the row change to allow optimizations.
2316 self.row_size_changed = True
2317 self.default_row_height = height
2319 if hide_unused_rows:
2320 self.default_row_zeroed = 1
2322 @convert_range_args
2323 def merge_range(
2324 self,
2325 first_row: int,
2326 first_col: int,
2327 last_row: int,
2328 last_col: int,
2329 data: Any,
2330 cell_format: Optional[Format] = None,
2331 ) -> int:
2332 """
2333 Merge a range of cells.
2335 Args:
2336 first_row: The first row of the cell range. (zero indexed).
2337 first_col: The first column of the cell range.
2338 last_row: The last row of the cell range. (zero indexed).
2339 last_col: The last column of the cell range.
2340 data: Cell data.
2341 cell_format: Cell Format object.
2343 Returns:
2344 0: Success.
2345 -1: Row or column is out of worksheet bounds.
2346 other: Return value of write().
2348 """
2349 # Merge a range of cells. The first cell should contain the data and
2350 # the others should be blank. All cells should have the same format.
2352 # Excel doesn't allow a single cell to be merged
2353 if first_row == last_row and first_col == last_col:
2354 warn("Can't merge single cell")
2355 return -1
2357 # Swap last row/col with first row/col as necessary
2358 if first_row > last_row:
2359 (first_row, last_row) = (last_row, first_row)
2360 if first_col > last_col:
2361 (first_col, last_col) = (last_col, first_col)
2363 # Check that row and col are valid and store max and min values.
2364 if self._check_dimensions(first_row, first_col):
2365 return -1
2366 if self._check_dimensions(last_row, last_col):
2367 return -1
2369 # Check if the merge range overlaps a previous merged or table range.
2370 # This is a critical file corruption error in Excel.
2371 cell_range = xl_range(first_row, first_col, last_row, last_col)
2372 for row in range(first_row, last_row + 1):
2373 for col in range(first_col, last_col + 1):
2374 if self.merged_cells.get((row, col)):
2375 previous_range = self.merged_cells.get((row, col))
2376 raise OverlappingRange(
2377 f"Merge range '{cell_range}' overlaps previous merge "
2378 f"range '{previous_range}'."
2379 )
2381 if self.table_cells.get((row, col)):
2382 previous_range = self.table_cells.get((row, col))
2383 raise OverlappingRange(
2384 f"Merge range '{cell_range}' overlaps previous table "
2385 f"range '{previous_range}'."
2386 )
2388 self.merged_cells[(row, col)] = cell_range
2390 # Store the merge range.
2391 self.merge.append([first_row, first_col, last_row, last_col])
2393 # Write the first cell
2394 self._write(first_row, first_col, data, cell_format)
2396 # Pad out the rest of the area with formatted blank cells.
2397 for row in range(first_row, last_row + 1):
2398 for col in range(first_col, last_col + 1):
2399 if row == first_row and col == first_col:
2400 continue
2401 self._write_blank(row, col, "", cell_format)
2403 return 0
2405 @convert_range_args
2406 def autofilter(
2407 self, first_row: int, first_col: int, last_row: int, last_col: int
2408 ) -> None:
2409 """
2410 Set the autofilter area in the worksheet.
2412 Args:
2413 first_row: The first row of the cell range. (zero indexed).
2414 first_col: The first column of the cell range.
2415 last_row: The last row of the cell range. (zero indexed).
2416 last_col: The last column of the cell range.
2418 Returns:
2419 Nothing.
2421 """
2422 # Reverse max and min values if necessary.
2423 if last_row < first_row:
2424 (first_row, last_row) = (last_row, first_row)
2425 if last_col < first_col:
2426 (first_col, last_col) = (last_col, first_col)
2428 # Build up the autofilter area range "Sheet1!$A$1:$C$13".
2429 area = self._convert_name_area(first_row, first_col, last_row, last_col)
2430 ref = xl_range(first_row, first_col, last_row, last_col)
2432 self.autofilter_area = area
2433 self.autofilter_ref = ref
2434 self.filter_range = [first_col, last_col]
2436 # Store the filter cell positions for use in the autofit calculation.
2437 for col in range(first_col, last_col + 1):
2438 # Check that the autofilter doesn't overlap a table filter.
2439 if self.filter_cells.get((first_row, col)):
2440 filter_type, filter_range = self.filter_cells.get((first_row, col))
2441 if filter_type == "table":
2442 raise OverlappingRange(
2443 f"Worksheet autofilter range '{ref}' overlaps previous "
2444 f"Table autofilter range '{filter_range}'."
2445 )
2447 self.filter_cells[(first_row, col)] = ("worksheet", ref)
2449 def filter_column(self, col: int, criteria: str) -> None:
2450 """
2451 Set the column filter criteria.
2453 Args:
2454 col: Filter column (zero-indexed).
2455 criteria: Filter criteria.
2457 Returns:
2458 Nothing.
2460 """
2461 if not self.autofilter_area:
2462 warn("Must call autofilter() before filter_column()")
2463 return
2465 # Check for a column reference in A1 notation and substitute.
2466 try:
2467 int(col)
2468 except ValueError:
2469 # Convert col ref to a cell ref and then to a col number.
2470 col_letter = col
2471 (_, col) = xl_cell_to_rowcol(col + "1")
2473 if col >= self.xls_colmax:
2474 warn(f"Invalid column '{col_letter}'")
2475 return
2477 (col_first, col_last) = self.filter_range
2479 # Reject column if it is outside filter range.
2480 if col < col_first or col > col_last:
2481 warn(
2482 f"Column '{col}' outside autofilter() column "
2483 f"range ({col_first}, {col_last})"
2484 )
2485 return
2487 tokens = self._extract_filter_tokens(criteria)
2489 if len(tokens) not in (3, 7):
2490 warn(f"Incorrect number of tokens in criteria '{criteria}'")
2492 tokens = self._parse_filter_expression(criteria, tokens)
2494 # Excel handles single or double custom filters as default filters.
2495 # We need to check for them and handle them accordingly.
2496 if len(tokens) == 2 and tokens[0] == 2:
2497 # Single equality.
2498 self.filter_column_list(col, [tokens[1]])
2499 elif len(tokens) == 5 and tokens[0] == 2 and tokens[2] == 1 and tokens[3] == 2:
2500 # Double equality with "or" operator.
2501 self.filter_column_list(col, [tokens[1], tokens[4]])
2502 else:
2503 # Non default custom filter.
2504 self.filter_cols[col] = tokens
2505 self.filter_type[col] = 0
2507 self.filter_on = 1
2509 def filter_column_list(self, col: int, filters: List[str]) -> None:
2510 """
2511 Set the column filter criteria in Excel 2007 list style.
2513 Args:
2514 col: Filter column (zero-indexed).
2515 filters: List of filter criteria to match.
2517 Returns:
2518 Nothing.
2520 """
2521 if not self.autofilter_area:
2522 warn("Must call autofilter() before filter_column()")
2523 return
2525 # Check for a column reference in A1 notation and substitute.
2526 try:
2527 int(col)
2528 except ValueError:
2529 # Convert col ref to a cell ref and then to a col number.
2530 col_letter = col
2531 (_, col) = xl_cell_to_rowcol(col + "1")
2533 if col >= self.xls_colmax:
2534 warn(f"Invalid column '{col_letter}'")
2535 return
2537 (col_first, col_last) = self.filter_range
2539 # Reject column if it is outside filter range.
2540 if col < col_first or col > col_last:
2541 warn(
2542 f"Column '{col}' outside autofilter() column range "
2543 f"({col_first},{col_last})"
2544 )
2545 return
2547 self.filter_cols[col] = filters
2548 self.filter_type[col] = 1
2549 self.filter_on = 1
2551 @convert_range_args
2552 def data_validation(
2553 self,
2554 first_row: int,
2555 first_col: int,
2556 last_row: int,
2557 last_col: int,
2558 options: Optional[Dict[str, Any]] = None,
2559 ) -> Literal[0, -1, -2]:
2560 """
2561 Add a data validation to a worksheet.
2563 Args:
2564 first_row: The first row of the cell range. (zero indexed).
2565 first_col: The first column of the cell range.
2566 last_row: The last row of the cell range. (zero indexed).
2567 last_col: The last column of the cell range.
2568 options: Data validation options.
2570 Returns:
2571 0: Success.
2572 -1: Row or column is out of worksheet bounds.
2573 -2: Incorrect parameter or option.
2574 """
2575 # Check that row and col are valid without storing the values.
2576 if self._check_dimensions(first_row, first_col, True, True):
2577 return -1
2578 if self._check_dimensions(last_row, last_col, True, True):
2579 return -1
2581 if options is None:
2582 options = {}
2583 else:
2584 # Copy the user defined options so they aren't modified.
2585 options = options.copy()
2587 # Valid input parameters.
2588 valid_parameters = {
2589 "validate",
2590 "criteria",
2591 "value",
2592 "source",
2593 "minimum",
2594 "maximum",
2595 "ignore_blank",
2596 "dropdown",
2597 "show_input",
2598 "input_title",
2599 "input_message",
2600 "show_error",
2601 "error_title",
2602 "error_message",
2603 "error_type",
2604 "other_cells",
2605 "multi_range",
2606 }
2608 # Check for valid input parameters.
2609 for param_key in options.keys():
2610 if param_key not in valid_parameters:
2611 warn(f"Unknown parameter '{param_key}' in data_validation()")
2612 return -2
2614 # Map alternative parameter names 'source' or 'minimum' to 'value'.
2615 if "source" in options:
2616 options["value"] = options["source"]
2617 if "minimum" in options:
2618 options["value"] = options["minimum"]
2620 # 'validate' is a required parameter.
2621 if "validate" not in options:
2622 warn("Parameter 'validate' is required in data_validation()")
2623 return -2
2625 # List of valid validation types.
2626 valid_types = {
2627 "any": "none",
2628 "any value": "none",
2629 "whole number": "whole",
2630 "whole": "whole",
2631 "integer": "whole",
2632 "decimal": "decimal",
2633 "list": "list",
2634 "date": "date",
2635 "time": "time",
2636 "text length": "textLength",
2637 "length": "textLength",
2638 "custom": "custom",
2639 }
2641 # Check for valid validation types.
2642 if options["validate"] not in valid_types:
2643 warn(
2644 f"Unknown validation type '{options['validate']}' for parameter "
2645 f"'validate' in data_validation()"
2646 )
2647 return -2
2649 options["validate"] = valid_types[options["validate"]]
2651 # No action is required for validation type 'any' if there are no
2652 # input messages to display.
2653 if (
2654 options["validate"] == "none"
2655 and options.get("input_title") is None
2656 and options.get("input_message") is None
2657 ):
2658 return -2
2660 # The any, list and custom validations don't have a criteria so we use
2661 # a default of 'between'.
2662 if (
2663 options["validate"] == "none"
2664 or options["validate"] == "list"
2665 or options["validate"] == "custom"
2666 ):
2667 options["criteria"] = "between"
2668 options["maximum"] = None
2670 # 'criteria' is a required parameter.
2671 if "criteria" not in options:
2672 warn("Parameter 'criteria' is required in data_validation()")
2673 return -2
2675 # Valid criteria types.
2676 criteria_types = {
2677 "between": "between",
2678 "not between": "notBetween",
2679 "equal to": "equal",
2680 "=": "equal",
2681 "==": "equal",
2682 "not equal to": "notEqual",
2683 "!=": "notEqual",
2684 "<>": "notEqual",
2685 "greater than": "greaterThan",
2686 ">": "greaterThan",
2687 "less than": "lessThan",
2688 "<": "lessThan",
2689 "greater than or equal to": "greaterThanOrEqual",
2690 ">=": "greaterThanOrEqual",
2691 "less than or equal to": "lessThanOrEqual",
2692 "<=": "lessThanOrEqual",
2693 }
2695 # Check for valid criteria types.
2696 if options["criteria"] not in criteria_types:
2697 warn(
2698 f"Unknown criteria type '{options['criteria']}' for parameter "
2699 f"'criteria' in data_validation()"
2700 )
2701 return -2
2703 options["criteria"] = criteria_types[options["criteria"]]
2705 # 'Between' and 'Not between' criteria require 2 values.
2706 if options["criteria"] == "between" or options["criteria"] == "notBetween":
2707 if "maximum" not in options:
2708 warn(
2709 "Parameter 'maximum' is required in data_validation() "
2710 "when using 'between' or 'not between' criteria"
2711 )
2712 return -2
2713 else:
2714 options["maximum"] = None
2716 # Valid error dialog types.
2717 error_types = {
2718 "stop": 0,
2719 "warning": 1,
2720 "information": 2,
2721 }
2723 # Check for valid error dialog types.
2724 if "error_type" not in options:
2725 options["error_type"] = 0
2726 elif options["error_type"] not in error_types:
2727 warn(
2728 f"Unknown criteria type '{options['error_type']}' "
2729 f"for parameter 'error_type'."
2730 )
2731 return -2
2732 else:
2733 options["error_type"] = error_types[options["error_type"]]
2735 # Convert date/times value if required.
2736 if (
2737 options["validate"] in ("date", "time")
2738 and options["value"]
2739 and _supported_datetime(options["value"])
2740 ):
2741 date_time = self._convert_date_time(options["value"])
2742 # Format date number to the same precision as Excel.
2743 options["value"] = f"{date_time:.16g}"
2745 if options["maximum"] and _supported_datetime(options["maximum"]):
2746 date_time = self._convert_date_time(options["maximum"])
2747 options["maximum"] = f"{date_time:.16g}"
2749 # Check that the input title doesn't exceed the maximum length.
2750 if options.get("input_title") and len(options["input_title"]) > 32:
2751 warn(
2752 f"Length of input title '{options['input_title']}' "
2753 f"exceeds Excel's limit of 32"
2754 )
2755 return -2
2757 # Check that the error title doesn't exceed the maximum length.
2758 if options.get("error_title") and len(options["error_title"]) > 32:
2759 warn(
2760 f"Length of error title '{options['error_title']}' "
2761 f"exceeds Excel's limit of 32"
2762 )
2763 return -2
2765 # Check that the input message doesn't exceed the maximum length.
2766 if options.get("input_message") and len(options["input_message"]) > 255:
2767 warn(
2768 f"Length of input message '{options['input_message']}' "
2769 f"exceeds Excel's limit of 255"
2770 )
2771 return -2
2773 # Check that the error message doesn't exceed the maximum length.
2774 if options.get("error_message") and len(options["error_message"]) > 255:
2775 warn(
2776 f"Length of error message '{options['error_message']}' "
2777 f"exceeds Excel's limit of 255"
2778 )
2779 return -2
2781 # Check that the input list doesn't exceed the maximum length.
2782 if options["validate"] == "list" and isinstance(options["value"], list):
2783 formula = self._csv_join(*options["value"])
2784 if len(formula) > 255:
2785 warn(
2786 f"Length of list items '{formula}' exceeds Excel's limit of "
2787 f"255, use a formula range instead"
2788 )
2789 return -2
2791 # Set some defaults if they haven't been defined by the user.
2792 if "ignore_blank" not in options:
2793 options["ignore_blank"] = 1
2794 if "dropdown" not in options:
2795 options["dropdown"] = 1
2796 if "show_input" not in options:
2797 options["show_input"] = 1
2798 if "show_error" not in options:
2799 options["show_error"] = 1
2801 # These are the cells to which the validation is applied.
2802 options["cells"] = [[first_row, first_col, last_row, last_col]]
2804 # A (for now) undocumented parameter to pass additional cell ranges.
2805 if "other_cells" in options:
2806 options["cells"].extend(options["other_cells"])
2808 # Override with user defined multiple range if provided.
2809 if "multi_range" in options:
2810 options["multi_range"] = options["multi_range"].replace("$", "")
2812 # Store the validation information until we close the worksheet.
2813 self.validations.append(options)
2815 return 0
2817 @convert_range_args
2818 def conditional_format(
2819 self,
2820 first_row: int,
2821 first_col: int,
2822 last_row: int,
2823 last_col: int,
2824 options: Optional[Dict[str, Any]] = None,
2825 ) -> Literal[0, -1, -2]:
2826 """
2827 Add a conditional format to a worksheet.
2829 Args:
2830 first_row: The first row of the cell range. (zero indexed).
2831 first_col: The first column of the cell range.
2832 last_row: The last row of the cell range. (zero indexed).
2833 last_col: The last column of the cell range.
2834 options: Conditional format options.
2836 Returns:
2837 0: Success.
2838 -1: Row or column is out of worksheet bounds.
2839 -2: Incorrect parameter or option.
2840 """
2841 # Check that row and col are valid without storing the values.
2842 if self._check_dimensions(first_row, first_col, True, True):
2843 return -1
2844 if self._check_dimensions(last_row, last_col, True, True):
2845 return -1
2847 if options is None:
2848 options = {}
2849 else:
2850 # Copy the user defined options so they aren't modified.
2851 options = options.copy()
2853 # Valid input parameters.
2854 valid_parameter = {
2855 "type",
2856 "format",
2857 "criteria",
2858 "value",
2859 "minimum",
2860 "maximum",
2861 "stop_if_true",
2862 "min_type",
2863 "mid_type",
2864 "max_type",
2865 "min_value",
2866 "mid_value",
2867 "max_value",
2868 "min_color",
2869 "mid_color",
2870 "max_color",
2871 "min_length",
2872 "max_length",
2873 "multi_range",
2874 "bar_color",
2875 "bar_negative_color",
2876 "bar_negative_color_same",
2877 "bar_solid",
2878 "bar_border_color",
2879 "bar_negative_border_color",
2880 "bar_negative_border_color_same",
2881 "bar_no_border",
2882 "bar_direction",
2883 "bar_axis_position",
2884 "bar_axis_color",
2885 "bar_only",
2886 "data_bar_2010",
2887 "icon_style",
2888 "reverse_icons",
2889 "icons_only",
2890 "icons",
2891 }
2893 # Check for valid input parameters.
2894 for param_key in options.keys():
2895 if param_key not in valid_parameter:
2896 warn(f"Unknown parameter '{param_key}' in conditional_format()")
2897 return -2
2899 # 'type' is a required parameter.
2900 if "type" not in options:
2901 warn("Parameter 'type' is required in conditional_format()")
2902 return -2
2904 # Valid types.
2905 valid_type = {
2906 "cell": "cellIs",
2907 "date": "date",
2908 "time": "time",
2909 "average": "aboveAverage",
2910 "duplicate": "duplicateValues",
2911 "unique": "uniqueValues",
2912 "top": "top10",
2913 "bottom": "top10",
2914 "text": "text",
2915 "time_period": "timePeriod",
2916 "blanks": "containsBlanks",
2917 "no_blanks": "notContainsBlanks",
2918 "errors": "containsErrors",
2919 "no_errors": "notContainsErrors",
2920 "2_color_scale": "2_color_scale",
2921 "3_color_scale": "3_color_scale",
2922 "data_bar": "dataBar",
2923 "formula": "expression",
2924 "icon_set": "iconSet",
2925 }
2927 # Check for valid types.
2928 if options["type"] not in valid_type:
2929 warn(
2930 f"Unknown value '{options['type']}' for parameter 'type' "
2931 f"in conditional_format()"
2932 )
2933 return -2
2935 if options["type"] == "bottom":
2936 options["direction"] = "bottom"
2937 options["type"] = valid_type[options["type"]]
2939 # Valid criteria types.
2940 criteria_type = {
2941 "between": "between",
2942 "not between": "notBetween",
2943 "equal to": "equal",
2944 "=": "equal",
2945 "==": "equal",
2946 "not equal to": "notEqual",
2947 "!=": "notEqual",
2948 "<>": "notEqual",
2949 "greater than": "greaterThan",
2950 ">": "greaterThan",
2951 "less than": "lessThan",
2952 "<": "lessThan",
2953 "greater than or equal to": "greaterThanOrEqual",
2954 ">=": "greaterThanOrEqual",
2955 "less than or equal to": "lessThanOrEqual",
2956 "<=": "lessThanOrEqual",
2957 "containing": "containsText",
2958 "not containing": "notContains",
2959 "begins with": "beginsWith",
2960 "ends with": "endsWith",
2961 "yesterday": "yesterday",
2962 "today": "today",
2963 "last 7 days": "last7Days",
2964 "last week": "lastWeek",
2965 "this week": "thisWeek",
2966 "next week": "nextWeek",
2967 "last month": "lastMonth",
2968 "this month": "thisMonth",
2969 "next month": "nextMonth",
2970 # For legacy, but incorrect, support.
2971 "continue week": "nextWeek",
2972 "continue month": "nextMonth",
2973 }
2975 # Check for valid criteria types.
2976 if "criteria" in options and options["criteria"] in criteria_type:
2977 options["criteria"] = criteria_type[options["criteria"]]
2979 # Convert boolean values if required.
2980 if "value" in options and isinstance(options["value"], bool):
2981 options["value"] = str(options["value"]).upper()
2983 # Convert date/times value if required.
2984 if options["type"] in ("date", "time"):
2985 options["type"] = "cellIs"
2987 if "value" in options:
2988 if not _supported_datetime(options["value"]):
2989 warn("Conditional format 'value' must be a datetime object.")
2990 return -2
2992 date_time = self._convert_date_time(options["value"])
2993 # Format date number to the same precision as Excel.
2994 options["value"] = f"{date_time:.16g}"
2996 if "minimum" in options:
2997 if not _supported_datetime(options["minimum"]):
2998 warn("Conditional format 'minimum' must be a datetime object.")
2999 return -2
3001 date_time = self._convert_date_time(options["minimum"])
3002 options["minimum"] = f"{date_time:.16g}"
3004 if "maximum" in options:
3005 if not _supported_datetime(options["maximum"]):
3006 warn("Conditional format 'maximum' must be a datetime object.")
3007 return -2
3009 date_time = self._convert_date_time(options["maximum"])
3010 options["maximum"] = f"{date_time:.16g}"
3012 # Valid icon styles.
3013 valid_icons = {
3014 "3_arrows": "3Arrows", # 1
3015 "3_flags": "3Flags", # 2
3016 "3_traffic_lights_rimmed": "3TrafficLights2", # 3
3017 "3_symbols_circled": "3Symbols", # 4
3018 "4_arrows": "4Arrows", # 5
3019 "4_red_to_black": "4RedToBlack", # 6
3020 "4_traffic_lights": "4TrafficLights", # 7
3021 "5_arrows_gray": "5ArrowsGray", # 8
3022 "5_quarters": "5Quarters", # 9
3023 "3_arrows_gray": "3ArrowsGray", # 10
3024 "3_traffic_lights": "3TrafficLights", # 11
3025 "3_signs": "3Signs", # 12
3026 "3_symbols": "3Symbols2", # 13
3027 "4_arrows_gray": "4ArrowsGray", # 14
3028 "4_ratings": "4Rating", # 15
3029 "5_arrows": "5Arrows", # 16
3030 "5_ratings": "5Rating",
3031 } # 17
3033 # Set the icon set properties.
3034 if options["type"] == "iconSet":
3035 # An icon_set must have an icon style.
3036 if not options.get("icon_style"):
3037 warn(
3038 "The 'icon_style' parameter must be specified when "
3039 "'type' == 'icon_set' in conditional_format()."
3040 )
3041 return -3
3043 # Check for valid icon styles.
3044 if options["icon_style"] not in valid_icons:
3045 warn(
3046 f"Unknown icon_style '{options['icon_style']}' "
3047 f"in conditional_format()."
3048 )
3049 return -2
3051 options["icon_style"] = valid_icons[options["icon_style"]]
3053 # Set the number of icons for the icon style.
3054 options["total_icons"] = 3
3055 if options["icon_style"].startswith("4"):
3056 options["total_icons"] = 4
3057 elif options["icon_style"].startswith("5"):
3058 options["total_icons"] = 5
3060 options["icons"] = self._set_icon_props(
3061 options.get("total_icons"), options.get("icons")
3062 )
3064 # Swap last row/col for first row/col as necessary
3065 if first_row > last_row:
3066 first_row, last_row = last_row, first_row
3068 if first_col > last_col:
3069 first_col, last_col = last_col, first_col
3071 # Set the formatting range.
3072 cell_range = xl_range(first_row, first_col, last_row, last_col)
3073 start_cell = xl_rowcol_to_cell(first_row, first_col)
3075 # Override with user defined multiple range if provided.
3076 if "multi_range" in options:
3077 cell_range = options["multi_range"]
3078 cell_range = cell_range.replace("$", "")
3080 # Get the dxf format index.
3081 if "format" in options and options["format"]:
3082 options["format"] = options["format"]._get_dxf_index()
3084 # Set the priority based on the order of adding.
3085 options["priority"] = self.dxf_priority
3086 self.dxf_priority += 1
3088 # Check for 2010 style data_bar parameters.
3089 # pylint: disable=too-many-boolean-expressions
3090 if (
3091 self.use_data_bars_2010
3092 or options.get("data_bar_2010")
3093 or options.get("bar_solid")
3094 or options.get("bar_border_color")
3095 or options.get("bar_negative_color")
3096 or options.get("bar_negative_color_same")
3097 or options.get("bar_negative_border_color")
3098 or options.get("bar_negative_border_color_same")
3099 or options.get("bar_no_border")
3100 or options.get("bar_axis_position")
3101 or options.get("bar_axis_color")
3102 or options.get("bar_direction")
3103 ):
3104 options["is_data_bar_2010"] = True
3106 # Special handling of text criteria.
3107 if options["type"] == "text":
3108 value = options["value"]
3109 length = len(value)
3110 criteria = options["criteria"]
3112 if options["criteria"] == "containsText":
3113 options["type"] = "containsText"
3114 options["formula"] = f'NOT(ISERROR(SEARCH("{value}",{start_cell})))'
3115 elif options["criteria"] == "notContains":
3116 options["type"] = "notContainsText"
3117 options["formula"] = f'ISERROR(SEARCH("{value}",{start_cell}))'
3118 elif options["criteria"] == "beginsWith":
3119 options["type"] = "beginsWith"
3120 options["formula"] = f'LEFT({start_cell},{length})="{value}"'
3121 elif options["criteria"] == "endsWith":
3122 options["type"] = "endsWith"
3123 options["formula"] = f'RIGHT({start_cell},{length})="{value}"'
3124 else:
3125 warn(f"Invalid text criteria '{criteria}' in conditional_format()")
3127 # Special handling of time time_period criteria.
3128 if options["type"] == "timePeriod":
3129 if options["criteria"] == "yesterday":
3130 options["formula"] = f"FLOOR({start_cell},1)=TODAY()-1"
3132 elif options["criteria"] == "today":
3133 options["formula"] = f"FLOOR({start_cell},1)=TODAY()"
3135 elif options["criteria"] == "tomorrow":
3136 options["formula"] = f"FLOOR({start_cell},1)=TODAY()+1"
3138 # fmt: off
3139 elif options["criteria"] == "last7Days":
3140 options["formula"] = (
3141 f"AND(TODAY()-FLOOR({start_cell},1)<=6,"
3142 f"FLOOR({start_cell},1)<=TODAY())"
3143 )
3144 # fmt: on
3146 elif options["criteria"] == "lastWeek":
3147 options["formula"] = (
3148 f"AND(TODAY()-ROUNDDOWN({start_cell},0)>=(WEEKDAY(TODAY())),"
3149 f"TODAY()-ROUNDDOWN({start_cell},0)<(WEEKDAY(TODAY())+7))"
3150 )
3152 elif options["criteria"] == "thisWeek":
3153 options["formula"] = (
3154 f"AND(TODAY()-ROUNDDOWN({start_cell},0)<=WEEKDAY(TODAY())-1,"
3155 f"ROUNDDOWN({start_cell},0)-TODAY()<=7-WEEKDAY(TODAY()))"
3156 )
3158 elif options["criteria"] == "nextWeek":
3159 options["formula"] = (
3160 f"AND(ROUNDDOWN({start_cell},0)-TODAY()>(7-WEEKDAY(TODAY())),"
3161 f"ROUNDDOWN({start_cell},0)-TODAY()<(15-WEEKDAY(TODAY())))"
3162 )
3164 elif options["criteria"] == "lastMonth":
3165 options["formula"] = (
3166 f"AND(MONTH({start_cell})=MONTH(TODAY())-1,"
3167 f"OR(YEAR({start_cell})=YEAR("
3168 f"TODAY()),AND(MONTH({start_cell})=1,YEAR(A1)=YEAR(TODAY())-1)))"
3169 )
3171 # fmt: off
3172 elif options["criteria"] == "thisMonth":
3173 options["formula"] = (
3174 f"AND(MONTH({start_cell})=MONTH(TODAY()),"
3175 f"YEAR({start_cell})=YEAR(TODAY()))"
3176 )
3177 # fmt: on
3179 elif options["criteria"] == "nextMonth":
3180 options["formula"] = (
3181 f"AND(MONTH({start_cell})=MONTH(TODAY())+1,"
3182 f"OR(YEAR({start_cell})=YEAR("
3183 f"TODAY()),AND(MONTH({start_cell})=12,"
3184 f"YEAR({start_cell})=YEAR(TODAY())+1)))"
3185 )
3187 else:
3188 warn(
3189 f"Invalid time_period criteria '{options['criteria']}' "
3190 f"in conditional_format()"
3191 )
3193 # Special handling of blanks/error types.
3194 if options["type"] == "containsBlanks":
3195 options["formula"] = f"LEN(TRIM({start_cell}))=0"
3197 if options["type"] == "notContainsBlanks":
3198 options["formula"] = f"LEN(TRIM({start_cell}))>0"
3200 if options["type"] == "containsErrors":
3201 options["formula"] = f"ISERROR({start_cell})"
3203 if options["type"] == "notContainsErrors":
3204 options["formula"] = f"NOT(ISERROR({start_cell}))"
3206 # Special handling for 2 color scale.
3207 if options["type"] == "2_color_scale":
3208 options["type"] = "colorScale"
3210 # Color scales don't use any additional formatting.
3211 options["format"] = None
3213 # Turn off 3 color parameters.
3214 options["mid_type"] = None
3215 options["mid_color"] = None
3217 options.setdefault("min_type", "min")
3218 options.setdefault("max_type", "max")
3219 options.setdefault("min_value", 0)
3220 options.setdefault("max_value", 0)
3221 options.setdefault("min_color", Color("#FF7128"))
3222 options.setdefault("max_color", Color("#FFEF9C"))
3224 options["min_color"] = Color._from_value(options["min_color"])
3225 options["max_color"] = Color._from_value(options["max_color"])
3227 # Special handling for 3 color scale.
3228 if options["type"] == "3_color_scale":
3229 options["type"] = "colorScale"
3231 # Color scales don't use any additional formatting.
3232 options["format"] = None
3234 options.setdefault("min_type", "min")
3235 options.setdefault("mid_type", "percentile")
3236 options.setdefault("max_type", "max")
3237 options.setdefault("min_value", 0)
3238 options.setdefault("max_value", 0)
3239 options.setdefault("min_color", Color("#F8696B"))
3240 options.setdefault("mid_color", Color("#FFEB84"))
3241 options.setdefault("max_color", Color("#63BE7B"))
3243 options["min_color"] = Color._from_value(options["min_color"])
3244 options["mid_color"] = Color._from_value(options["mid_color"])
3245 options["max_color"] = Color._from_value(options["max_color"])
3247 # Set a default mid value.
3248 if "mid_value" not in options:
3249 options["mid_value"] = 50
3251 # Special handling for data bar.
3252 if options["type"] == "dataBar":
3253 # Color scales don't use any additional formatting.
3254 options["format"] = None
3256 if not options.get("min_type"):
3257 options["min_type"] = "min"
3258 options["x14_min_type"] = "autoMin"
3259 else:
3260 options["x14_min_type"] = options["min_type"]
3262 if not options.get("max_type"):
3263 options["max_type"] = "max"
3264 options["x14_max_type"] = "autoMax"
3265 else:
3266 options["x14_max_type"] = options["max_type"]
3268 options.setdefault("min_value", 0)
3269 options.setdefault("max_value", 0)
3270 options.setdefault("bar_color", Color("#638EC6"))
3271 options.setdefault("bar_border_color", options["bar_color"])
3272 options.setdefault("bar_only", False)
3273 options.setdefault("bar_no_border", False)
3274 options.setdefault("bar_solid", False)
3275 options.setdefault("bar_direction", "")
3276 options.setdefault("bar_negative_color", Color("#FF0000"))
3277 options.setdefault("bar_negative_border_color", Color("#FF0000"))
3278 options.setdefault("bar_negative_color_same", False)
3279 options.setdefault("bar_negative_border_color_same", False)
3280 options.setdefault("bar_axis_position", "")
3281 options.setdefault("bar_axis_color", Color("#000000"))
3283 options["bar_color"] = Color._from_value(options["bar_color"])
3284 options["bar_border_color"] = Color._from_value(options["bar_border_color"])
3285 options["bar_axis_color"] = Color._from_value(options["bar_axis_color"])
3286 options["bar_negative_color"] = Color._from_value(
3287 options["bar_negative_color"]
3288 )
3289 options["bar_negative_border_color"] = Color._from_value(
3290 options["bar_negative_border_color"]
3291 )
3293 # Adjust for 2010 style data_bar parameters.
3294 if options.get("is_data_bar_2010"):
3295 self.excel_version = 2010
3297 if options["min_type"] == "min" and options["min_value"] == 0:
3298 options["min_value"] = None
3300 if options["max_type"] == "max" and options["max_value"] == 0:
3301 options["max_value"] = None
3303 options["range"] = cell_range
3305 # Strip the leading = from formulas.
3306 try:
3307 options["min_value"] = options["min_value"].lstrip("=")
3308 except (KeyError, AttributeError):
3309 pass
3310 try:
3311 options["mid_value"] = options["mid_value"].lstrip("=")
3312 except (KeyError, AttributeError):
3313 pass
3314 try:
3315 options["max_value"] = options["max_value"].lstrip("=")
3316 except (KeyError, AttributeError):
3317 pass
3319 # Store the conditional format until we close the worksheet.
3320 if cell_range in self.cond_formats:
3321 self.cond_formats[cell_range].append(options)
3322 else:
3323 self.cond_formats[cell_range] = [options]
3325 return 0
3327 @convert_range_args
3328 def add_table(
3329 self,
3330 first_row: int,
3331 first_col: int,
3332 last_row: int,
3333 last_col: int,
3334 options: Optional[Dict[str, Any]] = None,
3335 ) -> Literal[0, -1, -2, -3]:
3336 """
3337 Add an Excel table to a worksheet.
3339 Args:
3340 first_row: The first row of the cell range. (zero indexed).
3341 first_col: The first column of the cell range.
3342 last_row: The last row of the cell range. (zero indexed).
3343 last_col: The last column of the cell range.
3344 options: Table format options. (Optional)
3346 Returns:
3347 0: Success.
3348 -1: Row or column is out of worksheet bounds.
3349 -2: Incorrect parameter or option.
3350 -3: Not supported in constant_memory mode.
3351 """
3352 table = {}
3353 col_formats = {}
3355 if options is None:
3356 options = {}
3357 else:
3358 # Copy the user defined options so they aren't modified.
3359 options = options.copy()
3361 if self.constant_memory:
3362 warn("add_table() isn't supported in 'constant_memory' mode")
3363 return -3
3365 # Check that row and col are valid without storing the values.
3366 if self._check_dimensions(first_row, first_col, True, True):
3367 return -1
3368 if self._check_dimensions(last_row, last_col, True, True):
3369 return -1
3371 # Swap last row/col for first row/col as necessary.
3372 if first_row > last_row:
3373 (first_row, last_row) = (last_row, first_row)
3374 if first_col > last_col:
3375 (first_col, last_col) = (last_col, first_col)
3377 # Check if the table range overlaps a previous merged or table range.
3378 # This is a critical file corruption error in Excel.
3379 cell_range = xl_range(first_row, first_col, last_row, last_col)
3380 for row in range(first_row, last_row + 1):
3381 for col in range(first_col, last_col + 1):
3382 if self.table_cells.get((row, col)):
3383 previous_range = self.table_cells.get((row, col))
3384 raise OverlappingRange(
3385 f"Table range '{cell_range}' overlaps previous "
3386 f"table range '{previous_range}'."
3387 )
3389 if self.merged_cells.get((row, col)):
3390 previous_range = self.merged_cells.get((row, col))
3391 raise OverlappingRange(
3392 f"Table range '{cell_range}' overlaps previous "
3393 f"merge range '{previous_range}'."
3394 )
3396 self.table_cells[(row, col)] = cell_range
3398 # Valid input parameters.
3399 valid_parameter = {
3400 "autofilter",
3401 "banded_columns",
3402 "banded_rows",
3403 "columns",
3404 "data",
3405 "first_column",
3406 "header_row",
3407 "last_column",
3408 "name",
3409 "style",
3410 "total_row",
3411 }
3413 # Check for valid input parameters.
3414 for param_key in options.keys():
3415 if param_key not in valid_parameter:
3416 warn(f"Unknown parameter '{param_key}' in add_table()")
3417 return -2
3419 # Turn on Excel's defaults.
3420 options["banded_rows"] = options.get("banded_rows", True)
3421 options["header_row"] = options.get("header_row", True)
3422 options["autofilter"] = options.get("autofilter", True)
3424 # Check that there are enough rows.
3425 num_rows = last_row - first_row
3426 if options["header_row"]:
3427 num_rows -= 1
3429 if num_rows < 0:
3430 warn("Must have at least one data row in in add_table()")
3431 return -2
3433 # Set the table options.
3434 table["show_first_col"] = options.get("first_column", False)
3435 table["show_last_col"] = options.get("last_column", False)
3436 table["show_row_stripes"] = options.get("banded_rows", False)
3437 table["show_col_stripes"] = options.get("banded_columns", False)
3438 table["header_row_count"] = options.get("header_row", 0)
3439 table["totals_row_shown"] = options.get("total_row", False)
3441 # Set the table name.
3442 if "name" in options:
3443 name = options["name"]
3444 table["name"] = name
3446 if " " in name:
3447 warn(f"Name '{name}' in add_table() cannot contain spaces")
3448 return -2
3450 # Warn if the name contains invalid chars as defined by Excel.
3451 if not re.match(r"^[\w\\][\w\\.]*$", name, re.UNICODE) or re.match(
3452 r"^\d", name
3453 ):
3454 warn(f"Invalid Excel characters in add_table(): '{name}'")
3455 return -2
3457 # Warn if the name looks like a cell name.
3458 if re.match(r"^[a-zA-Z][a-zA-Z]?[a-dA-D]?\d+$", name):
3459 warn(f"Name looks like a cell name in add_table(): '{name}'")
3460 return -2
3462 # Warn if the name looks like a R1C1 cell reference.
3463 if re.match(r"^[rcRC]$", name) or re.match(r"^[rcRC]\d+[rcRC]\d+$", name):
3464 warn(f"Invalid name '{name}' like a RC cell ref in add_table()")
3465 return -2
3467 # Set the table style.
3468 if "style" in options:
3469 table["style"] = options["style"]
3471 if table["style"] is None:
3472 table["style"] = ""
3474 # Remove whitespace from style name.
3475 table["style"] = table["style"].replace(" ", "")
3476 else:
3477 table["style"] = "TableStyleMedium9"
3479 # Set the data range rows (without the header and footer).
3480 first_data_row = first_row
3481 last_data_row = last_row
3483 if options.get("header_row"):
3484 first_data_row += 1
3486 if options.get("total_row"):
3487 last_data_row -= 1
3489 # Set the table and autofilter ranges.
3490 table["range"] = xl_range(first_row, first_col, last_row, last_col)
3492 table["a_range"] = xl_range(first_row, first_col, last_data_row, last_col)
3494 # If the header row if off the default is to turn autofilter off.
3495 if not options["header_row"]:
3496 options["autofilter"] = 0
3498 # Set the autofilter range.
3499 if options["autofilter"]:
3500 table["autofilter"] = table["a_range"]
3502 # Add the table columns.
3503 col_id = 1
3504 table["columns"] = []
3505 seen_names = {}
3507 for col_num in range(first_col, last_col + 1):
3508 # Set up the default column data.
3509 col_data = {
3510 "id": col_id,
3511 "name": "Column" + str(col_id),
3512 "total_string": "",
3513 "total_function": "",
3514 "custom_total": "",
3515 "total_value": 0,
3516 "formula": "",
3517 "format": None,
3518 "name_format": None,
3519 }
3521 # Overwrite the defaults with any user defined values.
3522 if "columns" in options:
3523 # Check if there are user defined values for this column.
3524 if col_id <= len(options["columns"]):
3525 user_data = options["columns"][col_id - 1]
3526 else:
3527 user_data = None
3529 if user_data:
3530 # Get the column format.
3531 xformat = user_data.get("format", None)
3533 # Map user defined values to internal values.
3534 if user_data.get("header"):
3535 col_data["name"] = user_data["header"]
3537 # Excel requires unique case insensitive header names.
3538 header_name = col_data["name"]
3539 name = header_name.lower()
3540 if name in seen_names:
3541 warn(f"Duplicate header name in add_table(): '{name}'")
3542 return -2
3544 seen_names[name] = True
3546 col_data["name_format"] = user_data.get("header_format")
3548 # Handle the column formula.
3549 if "formula" in user_data and user_data["formula"]:
3550 formula = user_data["formula"]
3552 # Remove the formula '=' sign if it exists.
3553 if formula.startswith("="):
3554 formula = formula.lstrip("=")
3556 # Convert Excel 2010 "@" ref to 2007 "#This Row".
3557 formula = self._prepare_table_formula(formula)
3559 # Escape any future functions.
3560 formula = self._prepare_formula(formula, True)
3562 col_data["formula"] = formula
3563 # We write the formulas below after the table data.
3565 # Handle the function for the total row.
3566 if user_data.get("total_function"):
3567 function = user_data["total_function"]
3568 if function == "count_nums":
3569 function = "countNums"
3570 if function == "std_dev":
3571 function = "stdDev"
3573 subtotals = set(
3574 [
3575 "average",
3576 "countNums",
3577 "count",
3578 "max",
3579 "min",
3580 "stdDev",
3581 "sum",
3582 "var",
3583 ]
3584 )
3586 if function in subtotals:
3587 formula = self._table_function_to_formula(
3588 function, col_data["name"]
3589 )
3590 else:
3591 formula = self._prepare_formula(function, True)
3592 col_data["custom_total"] = formula
3593 function = "custom"
3595 col_data["total_function"] = function
3597 value = user_data.get("total_value", 0)
3599 self._write_formula(last_row, col_num, formula, xformat, value)
3601 elif user_data.get("total_string"):
3602 # Total label only (not a function).
3603 total_string = user_data["total_string"]
3604 col_data["total_string"] = total_string
3606 self._write_string(
3607 last_row, col_num, total_string, user_data.get("format")
3608 )
3610 # Get the dxf format index.
3611 if xformat is not None:
3612 col_data["format"] = xformat._get_dxf_index()
3614 # Store the column format for writing the cell data.
3615 # It doesn't matter if it is undefined.
3616 col_formats[col_id - 1] = xformat
3618 # Store the column data.
3619 table["columns"].append(col_data)
3621 # Write the column headers to the worksheet.
3622 if options["header_row"]:
3623 self._write_string(
3624 first_row, col_num, col_data["name"], col_data["name_format"]
3625 )
3627 col_id += 1
3629 # Write the cell data if supplied.
3630 if "data" in options:
3631 data = options["data"]
3633 i = 0 # For indexing the row data.
3634 for row in range(first_data_row, last_data_row + 1):
3635 j = 0 # For indexing the col data.
3636 for col in range(first_col, last_col + 1):
3637 if i < len(data) and j < len(data[i]):
3638 token = data[i][j]
3639 if j in col_formats:
3640 self._write(row, col, token, col_formats[j])
3641 else:
3642 self._write(row, col, token, None)
3643 j += 1
3644 i += 1
3646 # Write any columns formulas after the user supplied table data to
3647 # overwrite it if required.
3648 for col_id, col_num in enumerate(range(first_col, last_col + 1)):
3649 column_data = table["columns"][col_id]
3650 if column_data and column_data["formula"]:
3651 formula_format = col_formats.get(col_id)
3652 formula = column_data["formula"]
3654 for row in range(first_data_row, last_data_row + 1):
3655 self._write_formula(row, col_num, formula, formula_format)
3657 # Store the table data.
3658 self.tables.append(table)
3660 # Store the filter cell positions for use in the autofit calculation.
3661 if options["autofilter"]:
3662 for col in range(first_col, last_col + 1):
3663 # Check that the table autofilter doesn't overlap a worksheet filter.
3664 if self.filter_cells.get((first_row, col)):
3665 filter_type, filter_range = self.filter_cells.get((first_row, col))
3666 if filter_type == "worksheet":
3667 raise OverlappingRange(
3668 f"Table autofilter range '{cell_range}' overlaps previous "
3669 f"Worksheet autofilter range '{filter_range}'."
3670 )
3672 self.filter_cells[(first_row, col)] = ("table", cell_range)
3674 return 0
3676 @convert_cell_args
3677 def add_sparkline(
3678 self, row: int, col: int, options: Optional[Dict[str, Any]] = None
3679 ) -> Literal[0, -1, -2]:
3680 """
3681 Add sparklines to the worksheet.
3683 Args:
3684 row: The cell row (zero indexed).
3685 col: The cell column (zero indexed).
3686 options: Sparkline formatting options.
3688 Returns:
3689 0: Success.
3690 -1: Row or column is out of worksheet bounds.
3691 -2: Incorrect parameter or option.
3693 """
3695 # Check that row and col are valid without storing the values.
3696 if self._check_dimensions(row, col, True, True):
3697 return -1
3699 sparkline = {"locations": [xl_rowcol_to_cell(row, col)]}
3701 if options is None:
3702 options = {}
3704 # Valid input parameters.
3705 valid_parameters = {
3706 "location",
3707 "range",
3708 "type",
3709 "high_point",
3710 "low_point",
3711 "negative_points",
3712 "first_point",
3713 "last_point",
3714 "markers",
3715 "style",
3716 "series_color",
3717 "negative_color",
3718 "markers_color",
3719 "first_color",
3720 "last_color",
3721 "high_color",
3722 "low_color",
3723 "max",
3724 "min",
3725 "axis",
3726 "reverse",
3727 "empty_cells",
3728 "show_hidden",
3729 "plot_hidden",
3730 "date_axis",
3731 "weight",
3732 }
3734 # Check for valid input parameters.
3735 for param_key in options.keys():
3736 if param_key not in valid_parameters:
3737 warn(f"Unknown parameter '{param_key}' in add_sparkline()")
3738 return -1
3740 # 'range' is a required parameter.
3741 if "range" not in options:
3742 warn("Parameter 'range' is required in add_sparkline()")
3743 return -2
3745 # Handle the sparkline type.
3746 spark_type = options.get("type", "line")
3748 if spark_type not in ("line", "column", "win_loss"):
3749 warn(
3750 "Parameter 'type' must be 'line', 'column' "
3751 "or 'win_loss' in add_sparkline()"
3752 )
3753 return -2
3755 if spark_type == "win_loss":
3756 spark_type = "stacked"
3757 sparkline["type"] = spark_type
3759 # We handle single location/range values or list of values.
3760 if "location" in options:
3761 if isinstance(options["location"], list):
3762 sparkline["locations"] = options["location"]
3763 else:
3764 sparkline["locations"] = [options["location"]]
3766 if isinstance(options["range"], list):
3767 sparkline["ranges"] = options["range"]
3768 else:
3769 sparkline["ranges"] = [options["range"]]
3771 range_count = len(sparkline["ranges"])
3772 location_count = len(sparkline["locations"])
3774 # The ranges and locations must match.
3775 if range_count != location_count:
3776 warn(
3777 "Must have the same number of location and range "
3778 "parameters in add_sparkline()"
3779 )
3780 return -2
3782 # Store the count.
3783 sparkline["count"] = len(sparkline["locations"])
3785 # Get the worksheet name for the range conversion below.
3786 sheetname = quote_sheetname(self.name)
3788 # Cleanup the input ranges.
3789 new_ranges = []
3790 for spark_range in sparkline["ranges"]:
3791 # Remove the absolute reference $ symbols.
3792 spark_range = spark_range.replace("$", "")
3794 # Remove the = from formula.
3795 spark_range = spark_range.lstrip("=")
3797 # Convert a simple range into a full Sheet1!A1:D1 range.
3798 if "!" not in spark_range:
3799 spark_range = sheetname + "!" + spark_range
3801 new_ranges.append(spark_range)
3803 sparkline["ranges"] = new_ranges
3805 # Cleanup the input locations.
3806 new_locations = []
3807 for location in sparkline["locations"]:
3808 location = location.replace("$", "")
3809 new_locations.append(location)
3811 sparkline["locations"] = new_locations
3813 # Map options.
3814 sparkline["high"] = options.get("high_point")
3815 sparkline["low"] = options.get("low_point")
3816 sparkline["negative"] = options.get("negative_points")
3817 sparkline["first"] = options.get("first_point")
3818 sparkline["last"] = options.get("last_point")
3819 sparkline["markers"] = options.get("markers")
3820 sparkline["min"] = options.get("min")
3821 sparkline["max"] = options.get("max")
3822 sparkline["axis"] = options.get("axis")
3823 sparkline["reverse"] = options.get("reverse")
3824 sparkline["hidden"] = options.get("show_hidden")
3825 sparkline["weight"] = options.get("weight")
3827 # Map empty cells options.
3828 empty = options.get("empty_cells", "")
3830 if empty == "zero":
3831 sparkline["empty"] = 0
3832 elif empty == "connect":
3833 sparkline["empty"] = "span"
3834 else:
3835 sparkline["empty"] = "gap"
3837 # Map the date axis range.
3838 date_range = options.get("date_axis")
3840 if date_range and "!" not in date_range:
3841 date_range = sheetname + "!" + date_range
3843 sparkline["date_axis"] = date_range
3845 # Set the sparkline styles.
3846 style_id = options.get("style", 0)
3847 style = _get_sparkline_style(style_id)
3849 sparkline["series_color"] = style["series"]
3850 sparkline["negative_color"] = style["negative"]
3851 sparkline["markers_color"] = style["markers"]
3852 sparkline["first_color"] = style["first"]
3853 sparkline["last_color"] = style["last"]
3854 sparkline["high_color"] = style["high"]
3855 sparkline["low_color"] = style["low"]
3857 # Override the style colors with user defined colors.
3858 self._set_spark_color(sparkline, options, "series_color")
3859 self._set_spark_color(sparkline, options, "negative_color")
3860 self._set_spark_color(sparkline, options, "markers_color")
3861 self._set_spark_color(sparkline, options, "first_color")
3862 self._set_spark_color(sparkline, options, "last_color")
3863 self._set_spark_color(sparkline, options, "high_color")
3864 self._set_spark_color(sparkline, options, "low_color")
3866 self.sparklines.append(sparkline)
3868 return 0
3870 @convert_range_args
3871 def set_selection(
3872 self, first_row: int, first_col: int, last_row: int, last_col: int
3873 ) -> None:
3874 """
3875 Set the selected cell or cells in a worksheet
3877 Args:
3878 first_row: The first row of the cell range. (zero indexed).
3879 first_col: The first column of the cell range.
3880 last_row: The last row of the cell range. (zero indexed).
3881 last_col: The last column of the cell range.
3883 Returns:
3884 0: Nothing.
3885 """
3886 pane = None
3888 # Range selection. Do this before swapping max/min to allow the
3889 # selection direction to be reversed.
3890 active_cell = xl_rowcol_to_cell(first_row, first_col)
3892 # Swap last row/col for first row/col if necessary
3893 if first_row > last_row:
3894 (first_row, last_row) = (last_row, first_row)
3896 if first_col > last_col:
3897 (first_col, last_col) = (last_col, first_col)
3899 sqref = xl_range(first_row, first_col, last_row, last_col)
3901 # Selection isn't set for cell A1.
3902 if sqref == "A1":
3903 return
3905 self.selections = [[pane, active_cell, sqref]]
3907 @convert_cell_args
3908 def set_top_left_cell(self, row: int = 0, col: int = 0) -> None:
3909 """
3910 Set the first visible cell at the top left of a worksheet.
3912 Args:
3913 row: The cell row (zero indexed).
3914 col: The cell column (zero indexed).
3916 Returns:
3917 0: Nothing.
3918 """
3920 if row == 0 and col == 0:
3921 return
3923 self.top_left_cell = xl_rowcol_to_cell(row, col)
3925 def outline_settings(
3926 self,
3927 visible: bool = 1,
3928 symbols_below: bool = 1,
3929 symbols_right: bool = 1,
3930 auto_style: bool = 0,
3931 ) -> None:
3932 """
3933 Control outline settings.
3935 Args:
3936 visible: Outlines are visible. Optional, defaults to True.
3937 symbols_below: Show row outline symbols below the outline bar.
3938 Optional, defaults to True.
3939 symbols_right: Show column outline symbols to the right of the
3940 outline bar. Optional, defaults to True.
3941 auto_style: Use Automatic style. Optional, defaults to False.
3943 Returns:
3944 0: Nothing.
3945 """
3946 self.outline_on = visible
3947 self.outline_below = symbols_below
3948 self.outline_right = symbols_right
3949 self.outline_style = auto_style
3951 self.outline_changed = True
3953 @convert_cell_args
3954 def freeze_panes(
3955 self,
3956 row: int,
3957 col: int,
3958 top_row: Optional[int] = None,
3959 left_col: Optional[int] = None,
3960 pane_type: int = 0,
3961 ) -> None:
3962 """
3963 Create worksheet panes and mark them as frozen.
3965 Args:
3966 row: The cell row (zero indexed).
3967 col: The cell column (zero indexed).
3968 top_row: Topmost visible row in scrolling region of pane.
3969 left_col: Leftmost visible row in scrolling region of pane.
3971 Returns:
3972 0: Nothing.
3974 """
3975 if top_row is None:
3976 top_row = row
3978 if left_col is None:
3979 left_col = col
3981 self.panes = [row, col, top_row, left_col, pane_type]
3983 @convert_cell_args
3984 def split_panes(
3985 self,
3986 x: float,
3987 y: float,
3988 top_row: Optional[int] = None,
3989 left_col: Optional[int] = None,
3990 ) -> None:
3991 """
3992 Create worksheet panes and mark them as split.
3994 Args:
3995 x: The position for the vertical split.
3996 y: The position for the horizontal split.
3997 top_row: Topmost visible row in scrolling region of pane.
3998 left_col: Leftmost visible row in scrolling region of pane.
4000 Returns:
4001 0: Nothing.
4003 """
4004 # Same as freeze panes with a different pane type.
4005 self.freeze_panes(x, y, top_row, left_col, 2)
4007 def set_zoom(self, zoom: int = 100) -> None:
4008 """
4009 Set the worksheet zoom factor.
4011 Args:
4012 zoom: Scale factor: 10 <= zoom <= 400.
4014 Returns:
4015 Nothing.
4017 """
4018 # Ensure the zoom scale is in Excel's range.
4019 if zoom < 10 or zoom > 400:
4020 warn(f"Zoom factor '{zoom}' outside range: 10 <= zoom <= 400")
4021 zoom = 100
4023 self.zoom = int(zoom)
4025 def set_zoom_to_fit(self) -> None:
4026 """
4027 Set the worksheet zoom to selection/fit. Only works for chartsheets.
4029 Args:
4030 None.
4032 Returns:
4033 Nothing.
4035 """
4036 self.zoom_to_fit = True
4038 def right_to_left(self) -> None:
4039 """
4040 Display the worksheet right to left for some versions of Excel.
4042 Args:
4043 None.
4045 Returns:
4046 Nothing.
4048 """
4049 self.is_right_to_left = True
4051 def hide_zero(self) -> None:
4052 """
4053 Hide zero values in worksheet cells.
4055 Args:
4056 None.
4058 Returns:
4059 Nothing.
4061 """
4062 self.show_zeros = 0
4064 def set_tab_color(self, color: Union[str, Color]) -> None:
4065 """
4066 Set the color of the worksheet tab.
4068 Args:
4069 color: A #RGB color index.
4071 Returns:
4072 Nothing.
4074 """
4075 self.tab_color = Color._from_value(color)
4077 def protect(
4078 self, password: str = "", options: Optional[Dict[str, Any]] = None
4079 ) -> None:
4080 """
4081 Set the password and protection options of the worksheet.
4083 Args:
4084 password: An optional password string.
4085 options: A dictionary of worksheet objects to protect.
4087 Returns:
4088 Nothing.
4090 """
4091 if password != "":
4092 password = self._encode_password(password)
4094 if not options:
4095 options = {}
4097 # Default values for objects that can be protected.
4098 defaults = {
4099 "sheet": True,
4100 "content": False,
4101 "objects": False,
4102 "scenarios": False,
4103 "format_cells": False,
4104 "format_columns": False,
4105 "format_rows": False,
4106 "insert_columns": False,
4107 "insert_rows": False,
4108 "insert_hyperlinks": False,
4109 "delete_columns": False,
4110 "delete_rows": False,
4111 "select_locked_cells": True,
4112 "sort": False,
4113 "autofilter": False,
4114 "pivot_tables": False,
4115 "select_unlocked_cells": True,
4116 }
4118 # Overwrite the defaults with user specified values.
4119 for key in options.keys():
4120 if key in defaults:
4121 defaults[key] = options[key]
4122 else:
4123 warn(f"Unknown protection object: '{key}'")
4125 # Set the password after the user defined values.
4126 defaults["password"] = password
4128 self.protect_options = defaults
4130 def unprotect_range(
4131 self,
4132 cell_range: str,
4133 range_name: Optional[str] = None,
4134 password: Optional[str] = None,
4135 ) -> int:
4136 """
4137 Unprotect ranges within a protected worksheet.
4139 Args:
4140 cell_range: The cell or cell range to unprotect.
4141 range_name: An optional name for the range.
4142 password: An optional password string. (undocumented)
4144 Returns:
4145 0: Success.
4146 -1: Parameter error.
4148 """
4149 if cell_range is None:
4150 warn("Cell range must be specified in unprotect_range()")
4151 return -1
4153 # Sanitize the cell range.
4154 cell_range = cell_range.lstrip("=")
4155 cell_range = cell_range.replace("$", "")
4157 self.num_protected_ranges += 1
4159 if range_name is None:
4160 range_name = "Range" + str(self.num_protected_ranges)
4162 if password:
4163 password = self._encode_password(password)
4165 self.protected_ranges.append((cell_range, range_name, password))
4167 return 0
4169 @convert_cell_args
4170 def insert_button(
4171 self, row: int, col: int, options: Optional[Dict[str, Any]] = None
4172 ) -> Literal[0, -1]:
4173 """
4174 Insert a button form object into the worksheet.
4176 Args:
4177 row: The cell row (zero indexed).
4178 col: The cell column (zero indexed).
4179 options: Button formatting options.
4181 Returns:
4182 0: Success.
4183 -1: Row or column is out of worksheet bounds.
4185 """
4186 # Check insert (row, col) without storing.
4187 if self._check_dimensions(row, col, True, True):
4188 warn(f"Cannot insert button at ({row}, {col}).")
4189 return -1
4191 if options is None:
4192 options = {}
4194 # Create a new button object.
4195 height = self.default_row_pixels
4196 width = self.default_col_pixels
4197 button_number = 1 + len(self.buttons_list)
4199 button = ButtonType(row, col, height, width, button_number, options)
4201 self.buttons_list.append(button)
4203 self.has_vml = True
4205 return 0
4207 @convert_cell_args
4208 def insert_checkbox(
4209 self, row: int, col: int, boolean: bool, cell_format: Optional[Format] = None
4210 ):
4211 """
4212 Insert a boolean checkbox in a worksheet cell.
4214 Args:
4215 row: The cell row (zero indexed).
4216 col: The cell column (zero indexed).
4217 boolean: The boolean value to display as a checkbox.
4218 cell_format: Cell Format object. (optional)
4220 Returns:
4221 0: Success.
4222 -1: Row or column is out of worksheet bounds.
4224 """
4225 # Ensure that the checkbox property is set in the user defined format.
4226 if cell_format and not cell_format.checkbox:
4227 # This needs to be fixed with a clone.
4228 cell_format.set_checkbox()
4230 # If no format is supplied create and/or use the default checkbox format.
4231 if not cell_format:
4232 if not self.default_checkbox_format:
4233 self.default_checkbox_format = self.workbook_add_format()
4234 self.default_checkbox_format.set_checkbox()
4236 cell_format = self.default_checkbox_format
4238 return self._write_boolean(row, col, boolean, cell_format)
4240 ###########################################################################
4241 #
4242 # Public API. Page Setup methods.
4243 #
4244 ###########################################################################
4245 def set_landscape(self) -> None:
4246 """
4247 Set the page orientation as landscape.
4249 Args:
4250 None.
4252 Returns:
4253 Nothing.
4255 """
4256 self.orientation = 0
4257 self.page_setup_changed = True
4259 def set_portrait(self) -> None:
4260 """
4261 Set the page orientation as portrait.
4263 Args:
4264 None.
4266 Returns:
4267 Nothing.
4269 """
4270 self.orientation = 1
4271 self.page_setup_changed = True
4273 def set_page_view(self, view: Literal[0, 1, 2] = 1) -> None:
4274 """
4275 Set the page view mode.
4277 Args:
4278 0: Normal view mode
4279 1: Page view mode (the default)
4280 2: Page break view mode
4282 Returns:
4283 Nothing.
4285 """
4286 self.page_view = view
4288 def set_pagebreak_view(self) -> None:
4289 """
4290 Set the page view mode.
4292 Args:
4293 None.
4295 Returns:
4296 Nothing.
4298 """
4299 self.page_view = 2
4301 def set_paper(self, paper_size: Union[Literal[1, 9], int]) -> None:
4302 """
4303 Set the paper type. US Letter = 1, A4 = 9.
4305 Args:
4306 paper_size: Paper index.
4308 Returns:
4309 Nothing.
4311 """
4312 if paper_size:
4313 self.paper_size = paper_size
4314 self.page_setup_changed = True
4316 def center_horizontally(self) -> None:
4317 """
4318 Center the page horizontally.
4320 Args:
4321 None.
4323 Returns:
4324 Nothing.
4326 """
4327 self.print_options_changed = True
4328 self.hcenter = 1
4330 def center_vertically(self) -> None:
4331 """
4332 Center the page vertically.
4334 Args:
4335 None.
4337 Returns:
4338 Nothing.
4340 """
4341 self.print_options_changed = True
4342 self.vcenter = 1
4344 def set_margins(
4345 self,
4346 left: float = 0.7,
4347 right: float = 0.7,
4348 top: float = 0.75,
4349 bottom: float = 0.75,
4350 ) -> None:
4351 """
4352 Set all the page margins in inches.
4354 Args:
4355 left: Left margin.
4356 right: Right margin.
4357 top: Top margin.
4358 bottom: Bottom margin.
4360 Returns:
4361 Nothing.
4363 """
4364 self.margin_left = left
4365 self.margin_right = right
4366 self.margin_top = top
4367 self.margin_bottom = bottom
4369 def set_header(
4370 self, header: str = "", options: Optional[Dict[str, Any]] = None, margin=None
4371 ) -> None:
4372 """
4373 Set the page header caption and optional margin.
4375 Args:
4376 header: Header string.
4377 margin: Header margin.
4378 options: Header options, mainly for images.
4380 Returns:
4381 Nothing.
4383 """
4384 header_orig = header
4385 header = header.replace("&[Picture]", "&G")
4387 if len(header) > 255:
4388 warn("Header string cannot be longer than Excel's limit of 255 characters")
4389 return
4391 if options is not None:
4392 # For backward compatibility allow options to be the margin.
4393 if not isinstance(options, dict):
4394 options = {"margin": options}
4395 else:
4396 options = {}
4398 # Copy the user defined options so they aren't modified.
4399 options = options.copy()
4401 # For backward compatibility.
4402 if margin is not None:
4403 options["margin"] = margin
4405 # Reset the list in case the function is called more than once.
4406 self.header_images = []
4408 if options.get("image_left"):
4409 options["image_data"] = options.get("image_data_left")
4410 image = self._image_from_source(options.get("image_left"), options)
4411 image._header_position = "LH"
4412 self.header_images.append(image)
4414 if options.get("image_center"):
4415 options["image_data"] = options.get("image_data_center")
4416 image = self._image_from_source(options.get("image_center"), options)
4417 image._header_position = "CH"
4418 self.header_images.append(image)
4420 if options.get("image_right"):
4421 options["image_data"] = options.get("image_data_right")
4422 image = self._image_from_source(options.get("image_right"), options)
4423 image._header_position = "RH"
4424 self.header_images.append(image)
4426 placeholder_count = header.count("&G")
4427 image_count = len(self.header_images)
4429 if placeholder_count != image_count:
4430 warn(
4431 f"Number of footer images '{image_count}' doesn't match placeholder "
4432 f"count '{placeholder_count}' in string: {header_orig}"
4433 )
4434 self.header_images = []
4435 return
4437 if "align_with_margins" in options:
4438 self.header_footer_aligns = options["align_with_margins"]
4440 if "scale_with_doc" in options:
4441 self.header_footer_scales = options["scale_with_doc"]
4443 self.header = header
4444 self.margin_header = options.get("margin", 0.3)
4445 self.header_footer_changed = True
4447 if image_count:
4448 self.has_header_vml = True
4450 def set_footer(
4451 self, footer: str = "", options: Optional[Dict[str, Any]] = None, margin=None
4452 ) -> None:
4453 """
4454 Set the page footer caption and optional margin.
4456 Args:
4457 footer: Footer string.
4458 margin: Footer margin.
4459 options: Footer options, mainly for images.
4461 Returns:
4462 Nothing.
4464 """
4465 footer_orig = footer
4466 footer = footer.replace("&[Picture]", "&G")
4468 if len(footer) > 255:
4469 warn("Footer string cannot be longer than Excel's limit of 255 characters")
4470 return
4472 if options is not None:
4473 # For backward compatibility allow options to be the margin.
4474 if not isinstance(options, dict):
4475 options = {"margin": options}
4476 else:
4477 options = {}
4479 # Copy the user defined options so they aren't modified.
4480 options = options.copy()
4482 # For backward compatibility.
4483 if margin is not None:
4484 options["margin"] = margin
4486 # Reset the list in case the function is called more than once.
4487 self.footer_images = []
4489 if options.get("image_left"):
4490 options["image_data"] = options.get("image_data_left")
4491 image = self._image_from_source(options.get("image_left"), options)
4492 image._header_position = "LF"
4493 self.footer_images.append(image)
4495 if options.get("image_center"):
4496 options["image_data"] = options.get("image_data_center")
4497 image = self._image_from_source(options.get("image_center"), options)
4498 image._header_position = "CF"
4499 self.footer_images.append(image)
4501 if options.get("image_right"):
4502 options["image_data"] = options.get("image_data_right")
4503 image = self._image_from_source(options.get("image_right"), options)
4504 image._header_position = "RF"
4505 self.footer_images.append(image)
4507 placeholder_count = footer.count("&G")
4508 image_count = len(self.footer_images)
4510 if placeholder_count != image_count:
4511 warn(
4512 f"Number of footer images '{image_count}' doesn't match placeholder "
4513 f"count '{placeholder_count}' in string: {footer_orig}"
4514 )
4515 self.footer_images = []
4516 return
4518 if "align_with_margins" in options:
4519 self.header_footer_aligns = options["align_with_margins"]
4521 if "scale_with_doc" in options:
4522 self.header_footer_scales = options["scale_with_doc"]
4524 self.footer = footer
4525 self.margin_footer = options.get("margin", 0.3)
4526 self.header_footer_changed = True
4528 if image_count:
4529 self.has_header_vml = True
4531 def repeat_rows(self, first_row: int, last_row: Optional[int] = None) -> None:
4532 """
4533 Set the rows to repeat at the top of each printed page.
4535 Args:
4536 first_row: Start row for range.
4537 last_row: End row for range.
4539 Returns:
4540 Nothing.
4542 """
4543 if last_row is None:
4544 last_row = first_row
4546 # Convert rows to 1 based.
4547 first_row += 1
4548 last_row += 1
4550 # Create the row range area like: $1:$2.
4551 area = f"${first_row}:${last_row}"
4553 # Build up the print titles area "Sheet1!$1:$2"
4554 sheetname = quote_sheetname(self.name)
4555 self.repeat_row_range = sheetname + "!" + area
4557 @convert_column_args
4558 def repeat_columns(self, first_col: int, last_col: Optional[int] = None) -> None:
4559 """
4560 Set the columns to repeat at the left hand side of each printed page.
4562 Args:
4563 first_col: Start column for range.
4564 last_col: End column for range.
4566 Returns:
4567 Nothing.
4569 """
4570 if last_col is None:
4571 last_col = first_col
4573 # Convert to A notation.
4574 first_col = xl_col_to_name(first_col, 1)
4575 last_col = xl_col_to_name(last_col, 1)
4577 # Create a column range like $C:$D.
4578 area = first_col + ":" + last_col
4580 # Build up the print area range "=Sheet2!$C:$D"
4581 sheetname = quote_sheetname(self.name)
4582 self.repeat_col_range = sheetname + "!" + area
4584 def hide_gridlines(self, option: Literal[0, 1, 2] = 1) -> None:
4585 """
4586 Set the option to hide gridlines on the screen and the printed page.
4588 Args:
4589 option: 0 : Don't hide gridlines
4590 1 : Hide printed gridlines only
4591 2 : Hide screen and printed gridlines
4593 Returns:
4594 Nothing.
4596 """
4597 if option == 0:
4598 self.print_gridlines = 1
4599 self.screen_gridlines = 1
4600 self.print_options_changed = True
4601 elif option == 1:
4602 self.print_gridlines = 0
4603 self.screen_gridlines = 1
4604 else:
4605 self.print_gridlines = 0
4606 self.screen_gridlines = 0
4608 def print_row_col_headers(self) -> None:
4609 """
4610 Set the option to print the row and column headers on the printed page.
4612 Args:
4613 None.
4615 Returns:
4616 Nothing.
4618 """
4619 self.print_headers = True
4620 self.print_options_changed = True
4622 def hide_row_col_headers(self) -> None:
4623 """
4624 Set the option to hide the row and column headers on the worksheet.
4626 Args:
4627 None.
4629 Returns:
4630 Nothing.
4632 """
4633 self.row_col_headers = True
4635 @convert_range_args
4636 def print_area(
4637 self, first_row: int, first_col: int, last_row: int, last_col: int
4638 ) -> Literal[0, -1]:
4639 """
4640 Set the print area in the current worksheet.
4642 Args:
4643 first_row: The first row of the cell range. (zero indexed).
4644 first_col: The first column of the cell range.
4645 last_row: The last row of the cell range. (zero indexed).
4646 last_col: The last column of the cell range.
4648 Returns:
4649 0: Success.
4650 -1: Row or column is out of worksheet bounds.
4652 """
4653 # Set the print area in the current worksheet.
4655 # Ignore max print area since it is the same as no area for Excel.
4656 if (
4657 first_row == 0
4658 and first_col == 0
4659 and last_row == self.xls_rowmax - 1
4660 and last_col == self.xls_colmax - 1
4661 ):
4662 return -1
4664 # Build up the print area range "Sheet1!$A$1:$C$13".
4665 area = self._convert_name_area(first_row, first_col, last_row, last_col)
4666 self.print_area_range = area
4668 return 0
4670 def print_across(self) -> None:
4671 """
4672 Set the order in which pages are printed.
4674 Args:
4675 None.
4677 Returns:
4678 Nothing.
4680 """
4681 self.page_order = 1
4682 self.page_setup_changed = True
4684 def fit_to_pages(self, width: int, height: int) -> None:
4685 """
4686 Fit the printed area to a specific number of pages both vertically and
4687 horizontally.
4689 Args:
4690 width: Number of pages horizontally.
4691 height: Number of pages vertically.
4693 Returns:
4694 Nothing.
4696 """
4697 self.fit_page = 1
4698 self.fit_width = width
4699 self.fit_height = height
4700 self.page_setup_changed = True
4702 def set_start_page(self, start_page: int) -> None:
4703 """
4704 Set the start page number when printing.
4706 Args:
4707 start_page: Start page number.
4709 Returns:
4710 Nothing.
4712 """
4713 self.page_start = start_page
4715 def set_print_scale(self, scale: int) -> None:
4716 """
4717 Set the scale factor for the printed page.
4719 Args:
4720 scale: Print scale. 10 <= scale <= 400.
4722 Returns:
4723 Nothing.
4725 """
4726 # Confine the scale to Excel's range.
4727 if scale < 10 or scale > 400:
4728 warn(f"Print scale '{scale}' outside range: 10 <= scale <= 400")
4729 return
4731 # Turn off "fit to page" option when print scale is on.
4732 self.fit_page = 0
4734 self.print_scale = int(scale)
4735 self.page_setup_changed = True
4737 def print_black_and_white(self) -> None:
4738 """
4739 Set the option to print the worksheet in black and white.
4741 Args:
4742 None.
4744 Returns:
4745 Nothing.
4747 """
4748 self.black_white = True
4749 self.page_setup_changed = True
4751 def set_h_pagebreaks(self, breaks: List[int]) -> None:
4752 """
4753 Set the horizontal page breaks on a worksheet.
4755 Args:
4756 breaks: List of rows where the page breaks should be added.
4758 Returns:
4759 Nothing.
4761 """
4762 self.hbreaks = breaks
4764 def set_v_pagebreaks(self, breaks: List[int]) -> None:
4765 """
4766 Set the horizontal page breaks on a worksheet.
4768 Args:
4769 breaks: List of columns where the page breaks should be added.
4771 Returns:
4772 Nothing.
4774 """
4775 self.vbreaks = breaks
4777 def set_vba_name(self, name: Optional[str] = None) -> None:
4778 """
4779 Set the VBA name for the worksheet. By default this is the
4780 same as the sheet name: i.e., Sheet1 etc.
4782 Args:
4783 name: The VBA name for the worksheet.
4785 Returns:
4786 Nothing.
4788 """
4789 if name is not None:
4790 self.vba_codename = name
4791 else:
4792 self.vba_codename = "Sheet" + str(self.index + 1)
4794 def ignore_errors(self, options: Optional[Dict[str, Any]] = None) -> Literal[0, -1]:
4795 """
4796 Ignore various Excel errors/warnings in a worksheet for user defined
4797 ranges.
4799 Args:
4800 options: A dict of ignore errors keys with cell range values.
4802 Returns:
4803 0: Success.
4804 -1: Incorrect parameter or option.
4806 """
4807 if options is None:
4808 return -1
4810 # Copy the user defined options so they aren't modified.
4811 options = options.copy()
4813 # Valid input parameters.
4814 valid_parameters = {
4815 "number_stored_as_text",
4816 "eval_error",
4817 "formula_differs",
4818 "formula_range",
4819 "formula_unlocked",
4820 "empty_cell_reference",
4821 "list_data_validation",
4822 "calculated_column",
4823 "two_digit_text_year",
4824 }
4826 # Check for valid input parameters.
4827 for param_key in options.keys():
4828 if param_key not in valid_parameters:
4829 warn(f"Unknown parameter '{param_key}' in ignore_errors()")
4830 return -1
4832 self.ignored_errors = options
4834 return 0
4836 ###########################################################################
4837 #
4838 # Private API.
4839 #
4840 ###########################################################################
4841 def _initialize(self, init_data) -> None:
4842 self.name = init_data["name"]
4843 self.index = init_data["index"]
4844 self.str_table = init_data["str_table"]
4845 self.worksheet_meta = init_data["worksheet_meta"]
4846 self.constant_memory = init_data["constant_memory"]
4847 self.tmpdir = init_data["tmpdir"]
4848 self.date_1904 = init_data["date_1904"]
4849 self.strings_to_numbers = init_data["strings_to_numbers"]
4850 self.strings_to_formulas = init_data["strings_to_formulas"]
4851 self.strings_to_urls = init_data["strings_to_urls"]
4852 self.nan_inf_to_errors = init_data["nan_inf_to_errors"]
4853 self.default_date_format = init_data["default_date_format"]
4854 self.default_url_format = init_data["default_url_format"]
4855 self.workbook_add_format = init_data["workbook_add_format"]
4856 self.excel2003_style = init_data["excel2003_style"]
4857 self.remove_timezone = init_data["remove_timezone"]
4858 self.max_url_length = init_data["max_url_length"]
4859 self.use_future_functions = init_data["use_future_functions"]
4860 self.embedded_images = init_data["embedded_images"]
4862 if self.excel2003_style:
4863 self.original_row_height = 12.75
4864 self.default_row_height = 12.75
4865 self.default_row_pixels = 17
4866 self.margin_left = 0.75
4867 self.margin_right = 0.75
4868 self.margin_top = 1
4869 self.margin_bottom = 1
4870 self.margin_header = 0.5
4871 self.margin_footer = 0.5
4872 self.header_footer_aligns = False
4874 # Open a temp filehandle to store row data in constant_memory mode.
4875 if self.constant_memory:
4876 # This is sub-optimal but we need to create a temp file
4877 # with utf8 encoding in Python < 3.
4878 (fd, filename) = tempfile.mkstemp(dir=self.tmpdir)
4879 os.close(fd)
4880 self.row_data_filename = filename
4881 # pylint: disable=consider-using-with
4882 self.row_data_fh = open(filename, mode="w+", encoding="utf-8")
4884 # Set as the worksheet filehandle until the file is assembled.
4885 self.fh = self.row_data_fh
4887 def _assemble_xml_file(self) -> None:
4888 # Assemble and write the XML file.
4890 # Write the XML declaration.
4891 self._xml_declaration()
4893 # Write the root worksheet element.
4894 self._write_worksheet()
4896 # Write the worksheet properties.
4897 self._write_sheet_pr()
4899 # Write the worksheet dimensions.
4900 self._write_dimension()
4902 # Write the sheet view properties.
4903 self._write_sheet_views()
4905 # Write the sheet format properties.
4906 self._write_sheet_format_pr()
4908 # Write the sheet column info.
4909 self._write_cols()
4911 # Write the worksheet data such as rows columns and cells.
4912 if not self.constant_memory:
4913 self._write_sheet_data()
4914 else:
4915 self._write_optimized_sheet_data()
4917 # Write the sheetProtection element.
4918 self._write_sheet_protection()
4920 # Write the protectedRanges element.
4921 self._write_protected_ranges()
4923 # Write the phoneticPr element.
4924 if self.excel2003_style:
4925 self._write_phonetic_pr()
4927 # Write the autoFilter element.
4928 self._write_auto_filter()
4930 # Write the mergeCells element.
4931 self._write_merge_cells()
4933 # Write the conditional formats.
4934 self._write_conditional_formats()
4936 # Write the dataValidations element.
4937 self._write_data_validations()
4939 # Write the hyperlink element.
4940 self._write_hyperlinks()
4942 # Write the printOptions element.
4943 self._write_print_options()
4945 # Write the worksheet page_margins.
4946 self._write_page_margins()
4948 # Write the worksheet page setup.
4949 self._write_page_setup()
4951 # Write the headerFooter element.
4952 self._write_header_footer()
4954 # Write the rowBreaks element.
4955 self._write_row_breaks()
4957 # Write the colBreaks element.
4958 self._write_col_breaks()
4960 # Write the ignoredErrors element.
4961 self._write_ignored_errors()
4963 # Write the drawing element.
4964 self._write_drawings()
4966 # Write the legacyDrawing element.
4967 self._write_legacy_drawing()
4969 # Write the legacyDrawingHF element.
4970 self._write_legacy_drawing_hf()
4972 # Write the picture element, for the background.
4973 self._write_picture()
4975 # Write the tableParts element.
4976 self._write_table_parts()
4978 # Write the extLst elements.
4979 self._write_ext_list()
4981 # Close the worksheet tag.
4982 self._xml_end_tag("worksheet")
4984 # Close the file.
4985 self._xml_close()
4987 def _check_dimensions(
4988 self, row: int, col: int, ignore_row=False, ignore_col=False
4989 ) -> int:
4990 # Check that row and col are valid and store the max and min
4991 # values for use in other methods/elements. The ignore_row /
4992 # ignore_col flags is used to indicate that we wish to perform
4993 # the dimension check without storing the value. The ignore
4994 # flags are use by set_row() and data_validate.
4996 # Check that the row/col are within the worksheet bounds.
4997 if row < 0 or col < 0:
4998 return -1
4999 if row >= self.xls_rowmax or col >= self.xls_colmax:
5000 return -1
5002 # In constant_memory mode we don't change dimensions for rows
5003 # that are already written.
5004 if not ignore_row and not ignore_col and self.constant_memory:
5005 if row < self.previous_row:
5006 return -2
5008 if not ignore_row:
5009 if self.dim_rowmin is None or row < self.dim_rowmin:
5010 self.dim_rowmin = row
5011 if self.dim_rowmax is None or row > self.dim_rowmax:
5012 self.dim_rowmax = row
5014 if not ignore_col:
5015 if self.dim_colmin is None or col < self.dim_colmin:
5016 self.dim_colmin = col
5017 if self.dim_colmax is None or col > self.dim_colmax:
5018 self.dim_colmax = col
5020 return 0
5022 def _convert_date_time(self, dt_obj):
5023 # Convert a datetime object to an Excel serial date and time.
5024 return _datetime_to_excel_datetime(dt_obj, self.date_1904, self.remove_timezone)
5026 def _convert_name_area(self, row_num_1, col_num_1, row_num_2, col_num_2):
5027 # Convert zero indexed rows and columns to the format required by
5028 # worksheet named ranges, eg, "Sheet1!$A$1:$C$13".
5030 range1 = ""
5031 range2 = ""
5032 area = ""
5033 row_col_only = 0
5035 # Convert to A1 notation.
5036 col_char_1 = xl_col_to_name(col_num_1, 1)
5037 col_char_2 = xl_col_to_name(col_num_2, 1)
5038 row_char_1 = "$" + str(row_num_1 + 1)
5039 row_char_2 = "$" + str(row_num_2 + 1)
5041 # We need to handle special cases that refer to rows or columns only.
5042 if row_num_1 == 0 and row_num_2 == self.xls_rowmax - 1:
5043 range1 = col_char_1
5044 range2 = col_char_2
5045 row_col_only = 1
5046 elif col_num_1 == 0 and col_num_2 == self.xls_colmax - 1:
5047 range1 = row_char_1
5048 range2 = row_char_2
5049 row_col_only = 1
5050 else:
5051 range1 = col_char_1 + row_char_1
5052 range2 = col_char_2 + row_char_2
5054 # A repeated range is only written once (if it isn't a special case).
5055 if range1 == range2 and not row_col_only:
5056 area = range1
5057 else:
5058 area = range1 + ":" + range2
5060 # Build up the print area range "Sheet1!$A$1:$C$13".
5061 sheetname = quote_sheetname(self.name)
5062 area = sheetname + "!" + area
5064 return area
5066 def _sort_pagebreaks(self, breaks):
5067 # This is an internal method used to filter elements of a list of
5068 # pagebreaks used in the _store_hbreak() and _store_vbreak() methods.
5069 # It:
5070 # 1. Removes duplicate entries from the list.
5071 # 2. Sorts the list.
5072 # 3. Removes 0 from the list if present.
5073 if not breaks:
5074 return []
5076 breaks_set = set(breaks)
5078 if 0 in breaks_set:
5079 breaks_set.remove(0)
5081 breaks_list = list(breaks_set)
5082 breaks_list.sort()
5084 # The Excel 2007 specification says that the maximum number of page
5085 # breaks is 1026. However, in practice it is actually 1023.
5086 max_num_breaks = 1023
5087 if len(breaks_list) > max_num_breaks:
5088 breaks_list = breaks_list[:max_num_breaks]
5090 return breaks_list
5092 def _extract_filter_tokens(self, expression):
5093 # Extract the tokens from the filter expression. The tokens are mainly
5094 # non-whitespace groups. The only tricky part is to extract string
5095 # tokens that contain whitespace and/or quoted double quotes (Excel's
5096 # escaped quotes).
5097 #
5098 # Examples: 'x < 2000'
5099 # 'x > 2000 and x < 5000'
5100 # 'x = "foo"'
5101 # 'x = "foo bar"'
5102 # 'x = "foo "" bar"'
5103 #
5104 if not expression:
5105 return []
5107 token_re = re.compile(r'"(?:[^"]|"")*"|\S+')
5108 tokens = token_re.findall(expression)
5110 new_tokens = []
5111 # Remove single leading and trailing quotes and un-escape other quotes.
5112 for token in tokens:
5113 if token.startswith('"'):
5114 token = token[1:]
5116 if token.endswith('"'):
5117 token = token[:-1]
5119 token = token.replace('""', '"')
5121 new_tokens.append(token)
5123 return new_tokens
5125 def _parse_filter_expression(self, expression, tokens):
5126 # Converts the tokens of a possibly conditional expression into 1 or 2
5127 # sub expressions for further parsing.
5128 #
5129 # Examples:
5130 # ('x', '==', 2000) -> exp1
5131 # ('x', '>', 2000, 'and', 'x', '<', 5000) -> exp1 and exp2
5133 if len(tokens) == 7:
5134 # The number of tokens will be either 3 (for 1 expression)
5135 # or 7 (for 2 expressions).
5136 conditional = tokens[3]
5138 if re.match("(and|&&)", conditional):
5139 conditional = 0
5140 elif re.match(r"(or|\|\|)", conditional):
5141 conditional = 1
5142 else:
5143 warn(
5144 f"Token '{conditional}' is not a valid conditional "
5145 f"in filter expression '{expression}'"
5146 )
5148 expression_1 = self._parse_filter_tokens(expression, tokens[0:3])
5149 expression_2 = self._parse_filter_tokens(expression, tokens[4:7])
5150 return expression_1 + [conditional] + expression_2
5152 return self._parse_filter_tokens(expression, tokens)
5154 def _parse_filter_tokens(self, expression, tokens):
5155 # Parse the 3 tokens of a filter expression and return the operator
5156 # and token. The use of numbers instead of operators is a legacy of
5157 # Spreadsheet::WriteExcel.
5158 operators = {
5159 "==": 2,
5160 "=": 2,
5161 "=~": 2,
5162 "eq": 2,
5163 "!=": 5,
5164 "!~": 5,
5165 "ne": 5,
5166 "<>": 5,
5167 "<": 1,
5168 "<=": 3,
5169 ">": 4,
5170 ">=": 6,
5171 }
5173 operator = operators.get(tokens[1], None)
5174 token = tokens[2]
5176 # Special handling of "Top" filter expressions.
5177 if re.match("top|bottom", tokens[0].lower()):
5178 value = int(tokens[1])
5180 if value < 1 or value > 500:
5181 warn(
5182 f"The value '{token}' in expression '{expression}' "
5183 f"must be in the range 1 to 500"
5184 )
5186 token = token.lower()
5188 if token not in ("items", "%"):
5189 warn(
5190 f"The type '{token}' in expression '{expression}' "
5191 f"must be either 'items' or '%%'"
5192 )
5194 if tokens[0].lower() == "top":
5195 operator = 30
5196 else:
5197 operator = 32
5199 if tokens[2] == "%":
5200 operator += 1
5202 token = str(value)
5204 if not operator and tokens[0]:
5205 warn(
5206 f"Token '{token[0]}' is not a valid operator "
5207 f"in filter expression '{expression}'."
5208 )
5210 # Special handling for Blanks/NonBlanks.
5211 if re.match("blanks|nonblanks", token.lower()):
5212 # Only allow Equals or NotEqual in this context.
5213 if operator not in (2, 5):
5214 warn(
5215 f"The operator '{tokens[1]}' in expression '{expression}' "
5216 f"is not valid in relation to Blanks/NonBlanks'."
5217 )
5219 token = token.lower()
5221 # The operator should always be 2 (=) to flag a "simple" equality
5222 # in the binary record. Therefore we convert <> to =.
5223 if token == "blanks":
5224 if operator == 5:
5225 token = " "
5226 else:
5227 if operator == 5:
5228 operator = 2
5229 token = "blanks"
5230 else:
5231 operator = 5
5232 token = " "
5234 # if the string token contains an Excel match character then change the
5235 # operator type to indicate a non "simple" equality.
5236 if operator == 2 and re.search("[*?]", token):
5237 operator = 22
5239 return [operator, token]
5241 def _encode_password(self, password) -> str:
5242 # Hash a worksheet password. Based on the algorithm in
5243 # ECMA-376-4:2016, Office Open XML File Formats — Transitional
5244 # Migration Features, Additional attributes for workbookProtection
5245 # element (Part 1, §18.2.29).
5246 digest = 0x0000
5248 for char in password[::-1]:
5249 digest = ((digest >> 14) & 0x01) | ((digest << 1) & 0x7FFF)
5250 digest ^= ord(char)
5252 digest = ((digest >> 14) & 0x01) | ((digest << 1) & 0x7FFF)
5253 digest ^= len(password)
5254 digest ^= 0xCE4B
5256 return f"{digest:X}"
5258 def _image_from_source(self, source, options: Optional[Dict[str, Any]] = None):
5259 # Backward compatibility utility method to convert an input argument to
5260 # an Image object. The source can be a filename, BytesIO stream or
5261 # an existing Image object.
5262 if isinstance(source, Image):
5263 image = source
5264 elif options is not None and options.get("image_data"):
5265 image = Image(options["image_data"])
5266 image.image_name = source
5267 else:
5268 image = Image(source)
5270 return image
5272 def _prepare_image(
5273 self,
5274 image: Image,
5275 image_id: int,
5276 drawing_id: int,
5277 ) -> None:
5278 # Set up images/drawings.
5280 # Get the effective image width and height in pixels.
5281 width = image._width * image._x_scale
5282 height = image._height * image._y_scale
5284 # Scale by non 96dpi resolutions.
5285 width *= 96.0 / image._x_dpi
5286 height *= 96.0 / image._y_dpi
5288 dimensions = self._position_object_emus(
5289 image._col,
5290 image._row,
5291 image._x_offset,
5292 image._y_offset,
5293 width,
5294 height,
5295 image._anchor,
5296 )
5298 # Convert from pixels to emus.
5299 width = int(0.5 + (width * 9525))
5300 height = int(0.5 + (height * 9525))
5302 # Create a Drawing obj to use with worksheet unless one already exists.
5303 if not self.drawing:
5304 drawing = Drawing()
5305 drawing.embedded = 1
5306 self.drawing = drawing
5308 self.external_drawing_links.append(
5309 ["/drawing", "../drawings/drawing" + str(drawing_id) + ".xml", None]
5310 )
5311 else:
5312 drawing = self.drawing
5314 drawing_object = DrawingInfo()
5315 drawing_object._drawing_type = DrawingTypes.IMAGE
5316 drawing_object._dimensions = dimensions
5317 drawing_object._description = image.image_name
5318 drawing_object._width = width
5319 drawing_object._height = height
5320 drawing_object._shape = None
5321 drawing_object._anchor = image._anchor
5322 drawing_object._rel_index = 0
5323 drawing_object._decorative = image._decorative
5325 if image.description is not None:
5326 drawing_object._description = image.description
5328 if image._url:
5329 url = image._url
5330 target = url._target()
5331 target_mode = url._target_mode()
5333 if not self.drawing_rels.get(url._link):
5334 self.drawing_links.append(["/hyperlink", target, target_mode])
5336 url._rel_index = self._get_drawing_rel_index(url._link)
5337 drawing_object._url = url
5339 if not self.drawing_rels.get(image._digest):
5340 self.drawing_links.append(
5341 [
5342 "/image",
5343 "../media/image" + str(image_id) + "." + image._image_extension,
5344 ]
5345 )
5347 drawing_object._rel_index = self._get_drawing_rel_index(image._digest)
5348 drawing._add_drawing_object(drawing_object)
5350 def _prepare_shape(self, index, drawing_id) -> None:
5351 # Set up shapes/drawings.
5352 (
5353 row,
5354 col,
5355 x_offset,
5356 y_offset,
5357 x_scale,
5358 y_scale,
5359 text,
5360 anchor,
5361 options,
5362 description,
5363 decorative,
5364 ) = self.shapes[index]
5366 width = options.get("width", self.default_col_pixels * 3)
5367 height = options.get("height", self.default_row_pixels * 6)
5369 width *= x_scale
5370 height *= y_scale
5372 dimensions = self._position_object_emus(
5373 col, row, x_offset, y_offset, width, height, anchor
5374 )
5376 # Convert from pixels to emus.
5377 width = int(0.5 + (width * 9525))
5378 height = int(0.5 + (height * 9525))
5380 # Create a Drawing obj to use with worksheet unless one already exists.
5381 if not self.drawing:
5382 drawing = Drawing()
5383 drawing.embedded = 1
5384 self.drawing = drawing
5386 self.external_drawing_links.append(
5387 ["/drawing", "../drawings/drawing" + str(drawing_id) + ".xml", None]
5388 )
5389 else:
5390 drawing = self.drawing
5392 shape = Shape("rect", "TextBox", options)
5393 shape.text = text
5395 drawing_object = DrawingInfo()
5396 drawing_object._drawing_type = DrawingTypes.SHAPE
5397 drawing_object._dimensions = dimensions
5398 drawing_object._width = width
5399 drawing_object._height = height
5400 drawing_object._description = description
5401 drawing_object._shape = shape
5402 drawing_object._anchor = anchor
5403 drawing_object._rel_index = 0
5404 drawing_object._decorative = decorative
5406 url = Url.from_options(options)
5407 if url:
5408 target = url._target()
5409 target_mode = url._target_mode()
5411 if not self.drawing_rels.get(url._link):
5412 self.drawing_links.append(["/hyperlink", target, target_mode])
5414 url._rel_index = self._get_drawing_rel_index(url._link)
5415 drawing_object._url = url
5417 drawing._add_drawing_object(drawing_object)
5419 def _prepare_header_image(self, image_id, image) -> None:
5420 # Set up an image without a drawing object for header/footer images.
5422 # Strip the extension from the filename.
5423 image.image_name = re.sub(r"\..*$", "", image.image_name)
5425 if not self.vml_drawing_rels.get(image._digest):
5426 self.vml_drawing_links.append(
5427 [
5428 "/image",
5429 "../media/image" + str(image_id) + "." + image._image_extension,
5430 ]
5431 )
5433 image._ref_id = self._get_vml_drawing_rel_index(image._digest)
5435 self.header_images_list.append(image)
5437 def _prepare_background(self, image_id, image_extension) -> None:
5438 # Set up an image without a drawing object for backgrounds.
5439 self.external_background_links.append(
5440 ["/image", "../media/image" + str(image_id) + "." + image_extension]
5441 )
5443 def _prepare_chart(self, index, chart_id, drawing_id) -> None:
5444 # Set up chart/drawings.
5445 (
5446 row,
5447 col,
5448 chart,
5449 x_offset,
5450 y_offset,
5451 x_scale,
5452 y_scale,
5453 anchor,
5454 description,
5455 decorative,
5456 ) = self.charts[index]
5458 chart.id = chart_id - 1
5460 # Use user specified dimensions, if any.
5461 width = int(0.5 + (chart.width * x_scale))
5462 height = int(0.5 + (chart.height * y_scale))
5464 dimensions = self._position_object_emus(
5465 col, row, x_offset, y_offset, width, height, anchor
5466 )
5468 # Set the chart name for the embedded object if it has been specified.
5469 name = chart.chart_name
5471 # Create a Drawing obj to use with worksheet unless one already exists.
5472 if not self.drawing:
5473 drawing = Drawing()
5474 drawing.embedded = 1
5475 self.drawing = drawing
5477 self.external_drawing_links.append(
5478 ["/drawing", "../drawings/drawing" + str(drawing_id) + ".xml"]
5479 )
5480 else:
5481 drawing = self.drawing
5483 drawing_object = DrawingInfo()
5484 drawing_object._drawing_type = DrawingTypes.CHART
5485 drawing_object._dimensions = dimensions
5486 drawing_object._width = width
5487 drawing_object._height = height
5488 drawing_object._name = name
5489 drawing_object._shape = None
5490 drawing_object._anchor = anchor
5491 drawing_object._rel_index = self._get_drawing_rel_index()
5492 drawing_object._description = description
5493 drawing_object._decorative = decorative
5495 drawing._add_drawing_object(drawing_object)
5497 self.drawing_links.append(
5498 ["/chart", "../charts/chart" + str(chart_id) + ".xml"]
5499 )
5501 def _position_object_emus(
5502 self, col_start, row_start, x1, y1, width, height, anchor
5503 ):
5504 # Calculate the vertices that define the position of a graphical
5505 # object within the worksheet in EMUs.
5506 #
5507 # The vertices are expressed as English Metric Units (EMUs). There are
5508 # 12,700 EMUs per point. Therefore, 12,700 * 3 /4 = 9,525 EMUs per
5509 # pixel
5510 (
5511 col_start,
5512 row_start,
5513 x1,
5514 y1,
5515 col_end,
5516 row_end,
5517 x2,
5518 y2,
5519 x_abs,
5520 y_abs,
5521 ) = self._position_object_pixels(
5522 col_start, row_start, x1, y1, width, height, anchor
5523 )
5525 # Convert the pixel values to EMUs. See above.
5526 x1 = int(0.5 + 9525 * x1)
5527 y1 = int(0.5 + 9525 * y1)
5528 x2 = int(0.5 + 9525 * x2)
5529 y2 = int(0.5 + 9525 * y2)
5530 x_abs = int(0.5 + 9525 * x_abs)
5531 y_abs = int(0.5 + 9525 * y_abs)
5533 return (col_start, row_start, x1, y1, col_end, row_end, x2, y2, x_abs, y_abs)
5535 # Calculate the vertices that define the position of a graphical object
5536 # within the worksheet in pixels.
5537 #
5538 # +------------+------------+
5539 # | A | B |
5540 # +-----+------------+------------+
5541 # | |(x1,y1) | |
5542 # | 1 |(A1)._______|______ |
5543 # | | | | |
5544 # | | | | |
5545 # +-----+----| OBJECT |-----+
5546 # | | | | |
5547 # | 2 | |______________. |
5548 # | | | (B2)|
5549 # | | | (x2,y2)|
5550 # +---- +------------+------------+
5551 #
5552 # Example of an object that covers some of the area from cell A1 to B2.
5553 #
5554 # Based on the width and height of the object we need to calculate 8 vars:
5555 #
5556 # col_start, row_start, col_end, row_end, x1, y1, x2, y2.
5557 #
5558 # We also calculate the absolute x and y position of the top left vertex of
5559 # the object. This is required for images.
5560 #
5561 # The width and height of the cells that the object occupies can be
5562 # variable and have to be taken into account.
5563 #
5564 # The values of col_start and row_start are passed in from the calling
5565 # function. The values of col_end and row_end are calculated by
5566 # subtracting the width and height of the object from the width and
5567 # height of the underlying cells.
5568 #
5569 def _position_object_pixels(
5570 self, col_start, row_start, x1, y1, width, height, anchor
5571 ):
5572 # col_start # Col containing upper left corner of object.
5573 # x1 # Distance to left side of object.
5574 #
5575 # row_start # Row containing top left corner of object.
5576 # y1 # Distance to top of object.
5577 #
5578 # col_end # Col containing lower right corner of object.
5579 # x2 # Distance to right side of object.
5580 #
5581 # row_end # Row containing bottom right corner of object.
5582 # y2 # Distance to bottom of object.
5583 #
5584 # width # Width of object frame.
5585 # height # Height of object frame.
5586 #
5587 # x_abs # Absolute distance to left side of object.
5588 # y_abs # Absolute distance to top side of object.
5589 x_abs = 0
5590 y_abs = 0
5592 # Adjust start column for negative offsets.
5593 # pylint: disable=chained-comparison
5594 while x1 < 0 and col_start > 0:
5595 x1 += self._size_col(col_start - 1)
5596 col_start -= 1
5598 # Adjust start row for negative offsets.
5599 while y1 < 0 and row_start > 0:
5600 y1 += self._size_row(row_start - 1)
5601 row_start -= 1
5603 # Ensure that the image isn't shifted off the page at top left.
5604 x1 = max(0, x1)
5605 y1 = max(0, y1)
5607 # Calculate the absolute x offset of the top-left vertex.
5608 if self.col_size_changed:
5609 for col_id in range(col_start):
5610 x_abs += self._size_col(col_id)
5611 else:
5612 # Optimization for when the column widths haven't changed.
5613 x_abs += self.default_col_pixels * col_start
5615 x_abs += x1
5617 # Calculate the absolute y offset of the top-left vertex.
5618 if self.row_size_changed:
5619 for row_id in range(row_start):
5620 y_abs += self._size_row(row_id)
5621 else:
5622 # Optimization for when the row heights haven't changed.
5623 y_abs += self.default_row_pixels * row_start
5625 y_abs += y1
5627 # Adjust start column for offsets that are greater than the col width.
5628 while x1 >= self._size_col(col_start, anchor):
5629 x1 -= self._size_col(col_start)
5630 col_start += 1
5632 # Adjust start row for offsets that are greater than the row height.
5633 while y1 >= self._size_row(row_start, anchor):
5634 y1 -= self._size_row(row_start)
5635 row_start += 1
5637 # Initialize end cell to the same as the start cell.
5638 col_end = col_start
5639 row_end = row_start
5641 # Don't offset the image in the cell if the row/col is hidden.
5642 if self._size_col(col_start, anchor) > 0:
5643 width = width + x1
5644 if self._size_row(row_start, anchor) > 0:
5645 height = height + y1
5647 # Subtract the underlying cell widths to find end cell of the object.
5648 while width >= self._size_col(col_end, anchor):
5649 width -= self._size_col(col_end, anchor)
5650 col_end += 1
5652 # Subtract the underlying cell heights to find end cell of the object.
5653 while height >= self._size_row(row_end, anchor):
5654 height -= self._size_row(row_end, anchor)
5655 row_end += 1
5657 # The end vertices are whatever is left from the width and height.
5658 x2 = width
5659 y2 = height
5661 return [col_start, row_start, x1, y1, col_end, row_end, x2, y2, x_abs, y_abs]
5663 def _size_col(self, col: int, anchor=0):
5664 # Convert the width of a cell from character units to pixels. Excel
5665 # rounds the column width to the nearest pixel. If the width hasn't
5666 # been set by the user we use the default value. A hidden column is
5667 # treated as having a width of zero unless it has the special
5668 # "object_position" of 4 (size with cells).
5669 max_digit_width = 7 # For Calibri 11.
5670 padding = 5
5671 pixels = 0
5673 # Look up the cell value to see if it has been changed.
5674 if col in self.col_info:
5675 width = self.col_info[col][0]
5676 hidden = self.col_info[col][2]
5678 if width is None:
5679 width = self.default_col_width
5681 # Convert to pixels.
5682 if hidden and anchor != 4:
5683 pixels = 0
5684 elif width < 1:
5685 pixels = int(width * (max_digit_width + padding) + 0.5)
5686 else:
5687 pixels = int(width * max_digit_width + 0.5) + padding
5688 else:
5689 pixels = self.default_col_pixels
5691 return pixels
5693 def _size_row(self, row: int, anchor=0):
5694 # Convert the height of a cell from character units to pixels. If the
5695 # height hasn't been set by the user we use the default value. A
5696 # hidden row is treated as having a height of zero unless it has the
5697 # special "object_position" of 4 (size with cells).
5698 pixels = 0
5700 # Look up the cell value to see if it has been changed
5701 if row in self.row_sizes:
5702 height = self.row_sizes[row][0]
5703 hidden = self.row_sizes[row][1]
5705 if hidden and anchor != 4:
5706 pixels = 0
5707 else:
5708 pixels = int(4.0 / 3.0 * height)
5709 else:
5710 pixels = int(4.0 / 3.0 * self.default_row_height)
5712 return pixels
5714 def _pixels_to_width(self, pixels):
5715 # Convert the width of a cell from pixels to character units.
5716 max_digit_width = 7.0 # For Calabri 11.
5717 padding = 5.0
5719 if pixels <= 12:
5720 width = pixels / (max_digit_width + padding)
5721 else:
5722 width = (pixels - padding) / max_digit_width
5724 return width
5726 def _pixels_to_height(self, pixels):
5727 # Convert the height of a cell from pixels to character units.
5728 return 0.75 * pixels
5730 def _comment_vertices(self, comment: CommentType):
5731 # Calculate the positions of the comment object.
5732 anchor = 0
5733 vertices = self._position_object_pixels(
5734 comment.start_col,
5735 comment.start_row,
5736 comment.x_offset,
5737 comment.y_offset,
5738 comment.width,
5739 comment.height,
5740 anchor,
5741 )
5743 # Add the width and height for VML.
5744 vertices.append(comment.width)
5745 vertices.append(comment.height)
5747 return vertices
5749 def _button_vertices(self, button: ButtonType):
5750 # Calculate the positions of the button object.
5751 anchor = 0
5752 vertices = self._position_object_pixels(
5753 button.col,
5754 button.row,
5755 button.x_offset,
5756 button.y_offset,
5757 button.width,
5758 button.height,
5759 anchor,
5760 )
5762 # Add the width and height for VML.
5763 vertices.append(button.width)
5764 vertices.append(button.height)
5766 return vertices
5768 def _prepare_vml_objects(
5769 self, vml_data_id, vml_shape_id, vml_drawing_id, comment_id
5770 ):
5771 comments = []
5772 # Sort the comments into row/column order for easier comparison
5773 # testing and set the external links for comments and buttons.
5774 row_nums = sorted(self.comments.keys())
5776 for row in row_nums:
5777 col_nums = sorted(self.comments[row].keys())
5779 for col in col_nums:
5780 comment = self.comments[row][col]
5781 comment.vertices = self._comment_vertices(comment)
5783 # Set comment visibility if required and not user defined.
5784 if comment.is_visible is None:
5785 comment.is_visible = self.comments_visible
5787 # Set comment author if not already user defined.
5788 if comment.author is None:
5789 comment.author = self.comments_author
5791 comments.append(comment)
5793 for button in self.buttons_list:
5794 button.vertices = self._button_vertices(button)
5796 self.external_vml_links.append(
5797 ["/vmlDrawing", "../drawings/vmlDrawing" + str(vml_drawing_id) + ".vml"]
5798 )
5800 if self.has_comments:
5801 self.comments_list = comments
5803 self.external_comment_links.append(
5804 ["/comments", "../comments" + str(comment_id) + ".xml"]
5805 )
5807 count = len(comments)
5808 start_data_id = vml_data_id
5810 # The VML o:idmap data id contains a comma separated range when there
5811 # is more than one 1024 block of comments, like this: data="1,2".
5812 for i in range(int(count / 1024)):
5813 data_id = start_data_id + i + 1
5814 vml_data_id = f"{vml_data_id},{data_id}"
5816 self.vml_data_id = vml_data_id
5817 self.vml_shape_id = vml_shape_id
5819 return count
5821 def _prepare_header_vml_objects(self, vml_header_id, vml_drawing_id) -> None:
5822 # Set up external linkage for VML header/footer images.
5824 self.vml_header_id = vml_header_id
5826 self.external_vml_links.append(
5827 ["/vmlDrawing", "../drawings/vmlDrawing" + str(vml_drawing_id) + ".vml"]
5828 )
5830 def _prepare_tables(self, table_id, seen) -> None:
5831 # Set the table ids for the worksheet tables.
5832 for table in self.tables:
5833 table["id"] = table_id
5835 if table.get("name") is None:
5836 # Set a default name.
5837 table["name"] = "Table" + str(table_id)
5839 # Check for duplicate table names.
5840 name = table["name"].lower()
5842 if name in seen:
5843 raise DuplicateTableName(
5844 f"Duplicate name '{table['name']}' used in worksheet.add_table()."
5845 )
5847 seen[name] = True
5849 # Store the link used for the rels file.
5850 self.external_table_links.append(
5851 ["/table", "../tables/table" + str(table_id) + ".xml"]
5852 )
5853 table_id += 1
5855 def _table_function_to_formula(self, function, col_name):
5856 # Convert a table total function to a worksheet formula.
5857 formula = ""
5859 # Escape special characters, as required by Excel.
5860 col_name = col_name.replace("'", "''")
5861 col_name = col_name.replace("#", "'#")
5862 col_name = col_name.replace("]", "']")
5863 col_name = col_name.replace("[", "'[")
5865 subtotals = {
5866 "average": 101,
5867 "countNums": 102,
5868 "count": 103,
5869 "max": 104,
5870 "min": 105,
5871 "stdDev": 107,
5872 "sum": 109,
5873 "var": 110,
5874 }
5876 if function in subtotals:
5877 func_num = subtotals[function]
5878 formula = f"SUBTOTAL({func_num},[{col_name}])"
5879 else:
5880 warn(f"Unsupported function '{function}' in add_table()")
5882 return formula
5884 def _set_spark_color(self, sparkline, options, user_color) -> None:
5885 # Set the sparkline color.
5886 if user_color not in options:
5887 return
5889 sparkline[user_color] = Color._from_value(options[user_color])
5891 def _get_range_data(self, row_start, col_start, row_end, col_end):
5892 # Returns a range of data from the worksheet _table to be used in
5893 # chart cached data. Strings are returned as SST ids and decoded
5894 # in the workbook. Return None for data that doesn't exist since
5895 # Excel can chart series with data missing.
5897 if self.constant_memory:
5898 return ()
5900 data = []
5902 # Iterate through the table data.
5903 for row_num in range(row_start, row_end + 1):
5904 # Store None if row doesn't exist.
5905 if row_num not in self.table:
5906 data.append(None)
5907 continue
5909 for col_num in range(col_start, col_end + 1):
5910 if col_num in self.table[row_num]:
5911 cell = self.table[row_num][col_num]
5913 cell_type = cell.__class__.__name__
5915 if cell_type in ("Number", "Datetime"):
5916 # Return a number with Excel's precision.
5917 data.append(f"{cell.number:.16g}")
5919 elif cell_type == "String":
5920 # Return a string from it's shared string index.
5921 index = cell.string
5922 string = self.str_table._get_shared_string(index)
5924 data.append(string)
5926 elif cell_type in ("Formula", "ArrayFormula"):
5927 # Return the formula value.
5928 value = cell.value
5930 if value is None:
5931 value = 0
5933 data.append(value)
5935 elif cell_type == "Blank":
5936 # Return a empty cell.
5937 data.append("")
5938 else:
5939 # Store None if column doesn't exist.
5940 data.append(None)
5942 return data
5944 def _csv_join(self, *items):
5945 # Create a csv string for use with data validation formulas and lists.
5947 # Convert non string types to string.
5948 items = [str(item) if not isinstance(item, str) else item for item in items]
5950 return ",".join(items)
5952 def _escape_url(self, url):
5953 # Don't escape URL if it looks already escaped.
5954 if re.search("%[0-9a-fA-F]{2}", url):
5955 return url
5957 # Can't use url.quote() here because it doesn't match Excel.
5958 url = url.replace("%", "%25")
5959 url = url.replace('"', "%22")
5960 url = url.replace(" ", "%20")
5961 url = url.replace("<", "%3c")
5962 url = url.replace(">", "%3e")
5963 url = url.replace("[", "%5b")
5964 url = url.replace("]", "%5d")
5965 url = url.replace("^", "%5e")
5966 url = url.replace("`", "%60")
5967 url = url.replace("{", "%7b")
5968 url = url.replace("}", "%7d")
5970 return url
5972 def _get_drawing_rel_index(self, target=None):
5973 # Get the index used to address a drawing rel link.
5974 if target is None:
5975 self.drawing_rels_id += 1
5976 return self.drawing_rels_id
5978 if self.drawing_rels.get(target):
5979 return self.drawing_rels[target]
5981 self.drawing_rels_id += 1
5982 self.drawing_rels[target] = self.drawing_rels_id
5983 return self.drawing_rels_id
5985 def _get_vml_drawing_rel_index(self, target=None):
5986 # Get the index used to address a vml drawing rel link.
5987 if self.vml_drawing_rels.get(target):
5988 return self.vml_drawing_rels[target]
5990 self.vml_drawing_rels_id += 1
5991 self.vml_drawing_rels[target] = self.vml_drawing_rels_id
5992 return self.vml_drawing_rels_id
5994 ###########################################################################
5995 #
5996 # The following font methods are mainly duplicated from the Styles class
5997 # with appropriate changes for rich string styles.
5998 #
5999 ###########################################################################
6000 def _write_font(self, xf_format) -> None:
6001 # Write the <font> element.
6002 xml_writer = self.rstring
6004 xml_writer._xml_start_tag("rPr")
6006 # Handle the main font properties.
6007 if xf_format.bold:
6008 xml_writer._xml_empty_tag("b")
6009 if xf_format.italic:
6010 xml_writer._xml_empty_tag("i")
6011 if xf_format.font_strikeout:
6012 xml_writer._xml_empty_tag("strike")
6013 if xf_format.font_outline:
6014 xml_writer._xml_empty_tag("outline")
6015 if xf_format.font_shadow:
6016 xml_writer._xml_empty_tag("shadow")
6018 # Handle the underline variants.
6019 if xf_format.underline:
6020 self._write_underline(xf_format.underline)
6022 # Handle super/subscript.
6023 if xf_format.font_script == 1:
6024 self._write_vert_align("superscript")
6025 if xf_format.font_script == 2:
6026 self._write_vert_align("subscript")
6028 # Write the font size
6029 xml_writer._xml_empty_tag("sz", [("val", xf_format.font_size)])
6031 # Handle colors.
6032 if xf_format.theme == -1:
6033 # Ignore for excel2003_style.
6034 pass
6035 elif xf_format.theme:
6036 self._write_rstring_color("color", [("theme", xf_format.theme)])
6037 elif xf_format.color_indexed:
6038 self._write_rstring_color("color", [("indexed", xf_format.color_indexed)])
6039 elif xf_format.font_color:
6040 color = xf_format.font_color
6041 if not color._is_automatic:
6042 self._write_rstring_color("color", color._attributes())
6043 else:
6044 self._write_rstring_color("color", [("theme", 1)])
6046 # Write some other font properties related to font families.
6047 xml_writer._xml_empty_tag("rFont", [("val", xf_format.font_name)])
6048 xml_writer._xml_empty_tag("family", [("val", xf_format.font_family)])
6050 if xf_format.font_name == "Calibri" and not xf_format.hyperlink:
6051 xml_writer._xml_empty_tag("scheme", [("val", xf_format.font_scheme)])
6053 xml_writer._xml_end_tag("rPr")
6055 def _write_underline(self, underline) -> None:
6056 # Write the underline font element.
6057 attributes = []
6059 # Handle the underline variants.
6060 if underline == 2:
6061 attributes = [("val", "double")]
6062 elif underline == 33:
6063 attributes = [("val", "singleAccounting")]
6064 elif underline == 34:
6065 attributes = [("val", "doubleAccounting")]
6067 self.rstring._xml_empty_tag("u", attributes)
6069 def _write_vert_align(self, val) -> None:
6070 # Write the <vertAlign> font sub-element.
6071 attributes = [("val", val)]
6073 self.rstring._xml_empty_tag("vertAlign", attributes)
6075 def _write_rstring_color(self, name, attributes) -> None:
6076 # Write the <color> element.
6077 self.rstring._xml_empty_tag(name, attributes)
6079 def _opt_close(self) -> None:
6080 # Close the row data filehandle in constant_memory mode.
6081 if not self.row_data_fh_closed:
6082 self.row_data_fh.close()
6083 self.row_data_fh_closed = True
6085 def _opt_reopen(self) -> None:
6086 # Reopen the row data filehandle in constant_memory mode.
6087 if self.row_data_fh_closed:
6088 filename = self.row_data_filename
6089 # pylint: disable=consider-using-with
6090 self.row_data_fh = open(filename, mode="a+", encoding="utf-8")
6091 self.row_data_fh_closed = False
6092 self.fh = self.row_data_fh
6094 def _set_icon_props(self, total_icons, user_props=None):
6095 # Set the sub-properties for icons.
6096 props = []
6098 # Set the defaults.
6099 for _ in range(total_icons):
6100 props.append({"criteria": False, "value": 0, "type": "percent"})
6102 # Set the default icon values based on the number of icons.
6103 if total_icons == 3:
6104 props[0]["value"] = 67
6105 props[1]["value"] = 33
6107 if total_icons == 4:
6108 props[0]["value"] = 75
6109 props[1]["value"] = 50
6110 props[2]["value"] = 25
6112 if total_icons == 5:
6113 props[0]["value"] = 80
6114 props[1]["value"] = 60
6115 props[2]["value"] = 40
6116 props[3]["value"] = 20
6118 # Overwrite default properties with user defined properties.
6119 if user_props:
6120 # Ensure we don't set user properties for lowest icon.
6121 max_data = len(user_props)
6122 if max_data >= total_icons:
6123 max_data = total_icons - 1
6125 for i in range(max_data):
6126 # Set the user defined 'value' property.
6127 if user_props[i].get("value") is not None:
6128 props[i]["value"] = user_props[i]["value"]
6130 # Remove the formula '=' sign if it exists.
6131 tmp = props[i]["value"]
6132 if isinstance(tmp, str) and tmp.startswith("="):
6133 props[i]["value"] = tmp.lstrip("=")
6135 # Set the user defined 'type' property.
6136 if user_props[i].get("type"):
6137 valid_types = ("percent", "percentile", "number", "formula")
6139 if user_props[i]["type"] not in valid_types:
6140 warn(
6141 f"Unknown icon property type '{user_props[i]['type']}' "
6142 f"for sub-property 'type' in conditional_format()."
6143 )
6144 else:
6145 props[i]["type"] = user_props[i]["type"]
6147 if props[i]["type"] == "number":
6148 props[i]["type"] = "num"
6150 # Set the user defined 'criteria' property.
6151 criteria = user_props[i].get("criteria")
6152 if criteria and criteria == ">":
6153 props[i]["criteria"] = True
6155 return props
6157 ###########################################################################
6158 #
6159 # XML methods.
6160 #
6161 ###########################################################################
6163 def _write_worksheet(self) -> None:
6164 # Write the <worksheet> element. This is the root element.
6166 schema = "http://schemas.openxmlformats.org/"
6167 xmlns = schema + "spreadsheetml/2006/main"
6168 xmlns_r = schema + "officeDocument/2006/relationships"
6169 xmlns_mc = schema + "markup-compatibility/2006"
6170 ms_schema = "http://schemas.microsoft.com/"
6171 xmlns_x14ac = ms_schema + "office/spreadsheetml/2009/9/ac"
6173 attributes = [("xmlns", xmlns), ("xmlns:r", xmlns_r)]
6175 # Add some extra attributes for Excel 2010. Mainly for sparklines.
6176 if self.excel_version == 2010:
6177 attributes.append(("xmlns:mc", xmlns_mc))
6178 attributes.append(("xmlns:x14ac", xmlns_x14ac))
6179 attributes.append(("mc:Ignorable", "x14ac"))
6181 self._xml_start_tag("worksheet", attributes)
6183 def _write_dimension(self) -> None:
6184 # Write the <dimension> element. This specifies the range of
6185 # cells in the worksheet. As a special case, empty
6186 # spreadsheets use 'A1' as a range.
6188 if self.dim_rowmin is None and self.dim_colmin is None:
6189 # If the min dimensions are not defined then no dimensions
6190 # have been set and we use the default 'A1'.
6191 ref = "A1"
6193 elif self.dim_rowmin is None and self.dim_colmin is not None:
6194 # If the row dimensions aren't set but the column
6195 # dimensions are set then they have been changed via
6196 # set_column().
6198 if self.dim_colmin == self.dim_colmax:
6199 # The dimensions are a single cell and not a range.
6200 ref = xl_rowcol_to_cell(0, self.dim_colmin)
6201 else:
6202 # The dimensions are a cell range.
6203 cell_1 = xl_rowcol_to_cell(0, self.dim_colmin)
6204 cell_2 = xl_rowcol_to_cell(0, self.dim_colmax)
6205 ref = cell_1 + ":" + cell_2
6207 elif self.dim_rowmin == self.dim_rowmax and self.dim_colmin == self.dim_colmax:
6208 # The dimensions are a single cell and not a range.
6209 ref = xl_rowcol_to_cell(self.dim_rowmin, self.dim_colmin)
6210 else:
6211 # The dimensions are a cell range.
6212 cell_1 = xl_rowcol_to_cell(self.dim_rowmin, self.dim_colmin)
6213 cell_2 = xl_rowcol_to_cell(self.dim_rowmax, self.dim_colmax)
6214 ref = cell_1 + ":" + cell_2
6216 self._xml_empty_tag("dimension", [("ref", ref)])
6218 def _write_sheet_views(self) -> None:
6219 # Write the <sheetViews> element.
6220 self._xml_start_tag("sheetViews")
6222 # Write the sheetView element.
6223 self._write_sheet_view()
6225 self._xml_end_tag("sheetViews")
6227 def _write_sheet_view(self) -> None:
6228 # Write the <sheetViews> element.
6229 attributes = []
6231 # Hide screen gridlines if required.
6232 if not self.screen_gridlines:
6233 attributes.append(("showGridLines", 0))
6235 # Hide screen row/column headers.
6236 if self.row_col_headers:
6237 attributes.append(("showRowColHeaders", 0))
6239 # Hide zeroes in cells.
6240 if not self.show_zeros:
6241 attributes.append(("showZeros", 0))
6243 # Display worksheet right to left for Hebrew, Arabic and others.
6244 if self.is_right_to_left:
6245 attributes.append(("rightToLeft", 1))
6247 # Show that the sheet tab is selected.
6248 if self.selected:
6249 attributes.append(("tabSelected", 1))
6251 # Turn outlines off. Also required in the outlinePr element.
6252 if not self.outline_on:
6253 attributes.append(("showOutlineSymbols", 0))
6255 # Set the page view/layout mode if required.
6256 if self.page_view == 1:
6257 attributes.append(("view", "pageLayout"))
6258 elif self.page_view == 2:
6259 attributes.append(("view", "pageBreakPreview"))
6261 # Set the first visible cell.
6262 if self.top_left_cell != "":
6263 attributes.append(("topLeftCell", self.top_left_cell))
6265 # Set the zoom level.
6266 if self.zoom != 100:
6267 attributes.append(("zoomScale", self.zoom))
6269 if self.page_view == 0 and self.zoom_scale_normal:
6270 attributes.append(("zoomScaleNormal", self.zoom))
6271 if self.page_view == 1:
6272 attributes.append(("zoomScalePageLayoutView", self.zoom))
6273 if self.page_view == 2:
6274 attributes.append(("zoomScaleSheetLayoutView", self.zoom))
6276 attributes.append(("workbookViewId", 0))
6278 if self.is_chartsheet and self.zoom_to_fit:
6279 attributes.append(("zoomToFit", 1))
6281 if self.panes or self.selections:
6282 self._xml_start_tag("sheetView", attributes)
6283 self._write_panes()
6284 self._write_selections()
6285 self._xml_end_tag("sheetView")
6286 else:
6287 self._xml_empty_tag("sheetView", attributes)
6289 def _write_sheet_format_pr(self) -> None:
6290 # Write the <sheetFormatPr> element.
6291 default_row_height = self.default_row_height
6292 row_level = self.outline_row_level
6293 col_level = self.outline_col_level
6295 attributes = [("defaultRowHeight", default_row_height)]
6297 if self.default_row_height != self.original_row_height:
6298 attributes.append(("customHeight", 1))
6300 if self.default_row_zeroed:
6301 attributes.append(("zeroHeight", 1))
6303 if row_level:
6304 attributes.append(("outlineLevelRow", row_level))
6305 if col_level:
6306 attributes.append(("outlineLevelCol", col_level))
6308 if self.excel_version == 2010:
6309 attributes.append(("x14ac:dyDescent", "0.25"))
6311 self._xml_empty_tag("sheetFormatPr", attributes)
6313 def _write_cols(self) -> None:
6314 # Write the <cols> element and <col> sub elements.
6316 # Exit unless some column have been formatted.
6317 if not self.col_info:
6318 return
6320 self._xml_start_tag("cols")
6322 # Use the first element of the column information structures to set
6323 # the initial/previous properties.
6324 first_col = (sorted(self.col_info.keys()))[0]
6325 last_col = first_col
6326 prev_col_options = self.col_info[first_col]
6327 del self.col_info[first_col]
6328 deleted_col = first_col
6329 deleted_col_options = prev_col_options
6331 for col in sorted(self.col_info.keys()):
6332 col_options = self.col_info[col]
6333 # Check if the column number is contiguous with the previous
6334 # column and if the properties are the same.
6335 if col == last_col + 1 and col_options == prev_col_options:
6336 last_col = col
6337 else:
6338 # If not contiguous/equal then we write out the current range
6339 # of columns and start again.
6340 self._write_col_info(first_col, last_col, prev_col_options)
6341 first_col = col
6342 last_col = first_col
6343 prev_col_options = col_options
6345 # We will exit the previous loop with one unhandled column range.
6346 self._write_col_info(first_col, last_col, prev_col_options)
6348 # Put back the deleted first column information structure.
6349 self.col_info[deleted_col] = deleted_col_options
6351 self._xml_end_tag("cols")
6353 def _write_col_info(self, col_min, col_max, col_info) -> None:
6354 # Write the <col> element.
6355 (width, cell_format, hidden, level, collapsed, autofit) = col_info
6357 custom_width = 1
6358 xf_index = 0
6360 # Get the cell_format index.
6361 if cell_format:
6362 xf_index = cell_format._get_xf_index()
6364 # Set the Excel default column width.
6365 if width is None:
6366 if not hidden:
6367 width = 8.43
6368 custom_width = 0
6369 else:
6370 width = 0
6371 elif width == 8.43:
6372 # Width is defined but same as default.
6373 custom_width = 0
6375 # Convert column width from user units to character width.
6376 if width > 0:
6377 # For Calabri 11.
6378 max_digit_width = 7
6379 padding = 5
6381 if width < 1:
6382 width = (
6383 int(
6384 (int(width * (max_digit_width + padding) + 0.5))
6385 / float(max_digit_width)
6386 * 256.0
6387 )
6388 / 256.0
6389 )
6390 else:
6391 width = (
6392 int(
6393 (int(width * max_digit_width + 0.5) + padding)
6394 / float(max_digit_width)
6395 * 256.0
6396 )
6397 / 256.0
6398 )
6400 attributes = [
6401 ("min", col_min + 1),
6402 ("max", col_max + 1),
6403 ("width", f"{width:.16g}"),
6404 ]
6406 if xf_index:
6407 attributes.append(("style", xf_index))
6408 if hidden:
6409 attributes.append(("hidden", "1"))
6410 if autofit:
6411 attributes.append(("bestFit", "1"))
6412 if custom_width:
6413 attributes.append(("customWidth", "1"))
6414 if level:
6415 attributes.append(("outlineLevel", level))
6416 if collapsed:
6417 attributes.append(("collapsed", "1"))
6419 self._xml_empty_tag("col", attributes)
6421 def _write_sheet_data(self) -> None:
6422 # Write the <sheetData> element.
6423 if self.dim_rowmin is None:
6424 # If the dimensions aren't defined there is no data to write.
6425 self._xml_empty_tag("sheetData")
6426 else:
6427 self._xml_start_tag("sheetData")
6428 self._write_rows()
6429 self._xml_end_tag("sheetData")
6431 def _write_optimized_sheet_data(self) -> None:
6432 # Write the <sheetData> element when constant_memory is on. In this
6433 # case we read the data stored in the temp file and rewrite it to the
6434 # XML sheet file.
6435 if self.dim_rowmin is None:
6436 # If the dimensions aren't defined then there is no data to write.
6437 self._xml_empty_tag("sheetData")
6438 else:
6439 self._xml_start_tag("sheetData")
6441 # Rewind the filehandle that was used for temp row data.
6442 buff_size = 65536
6443 self.row_data_fh.seek(0)
6444 data = self.row_data_fh.read(buff_size)
6446 while data:
6447 self.fh.write(data)
6448 data = self.row_data_fh.read(buff_size)
6450 self.row_data_fh.close()
6451 os.unlink(self.row_data_filename)
6453 self._xml_end_tag("sheetData")
6455 def _write_page_margins(self) -> None:
6456 # Write the <pageMargins> element.
6457 attributes = [
6458 ("left", self.margin_left),
6459 ("right", self.margin_right),
6460 ("top", self.margin_top),
6461 ("bottom", self.margin_bottom),
6462 ("header", self.margin_header),
6463 ("footer", self.margin_footer),
6464 ]
6466 self._xml_empty_tag("pageMargins", attributes)
6468 def _write_page_setup(self) -> None:
6469 # Write the <pageSetup> element.
6470 #
6471 # The following is an example taken from Excel.
6472 #
6473 # <pageSetup
6474 # paperSize="9"
6475 # scale="110"
6476 # fitToWidth="2"
6477 # fitToHeight="2"
6478 # pageOrder="overThenDown"
6479 # orientation="portrait"
6480 # blackAndWhite="1"
6481 # draft="1"
6482 # horizontalDpi="200"
6483 # verticalDpi="200"
6484 # r:id="rId1"
6485 # />
6486 #
6487 attributes = []
6489 # Skip this element if no page setup has changed.
6490 if not self.page_setup_changed:
6491 return
6493 # Set paper size.
6494 if self.paper_size:
6495 attributes.append(("paperSize", self.paper_size))
6497 # Set the print_scale.
6498 if self.print_scale != 100:
6499 attributes.append(("scale", self.print_scale))
6501 # Set the "Fit to page" properties.
6502 if self.fit_page and self.fit_width != 1:
6503 attributes.append(("fitToWidth", self.fit_width))
6505 if self.fit_page and self.fit_height != 1:
6506 attributes.append(("fitToHeight", self.fit_height))
6508 # Set the page print direction.
6509 if self.page_order:
6510 attributes.append(("pageOrder", "overThenDown"))
6512 # Set start page for printing.
6513 if self.page_start > 1:
6514 attributes.append(("firstPageNumber", self.page_start))
6516 # Set page orientation.
6517 if self.orientation:
6518 attributes.append(("orientation", "portrait"))
6519 else:
6520 attributes.append(("orientation", "landscape"))
6522 # Set the print in black and white option.
6523 if self.black_white:
6524 attributes.append(("blackAndWhite", "1"))
6526 # Set start page for printing.
6527 if self.page_start != 0:
6528 attributes.append(("useFirstPageNumber", "1"))
6530 # Set the DPI. Mainly only for testing.
6531 if self.is_chartsheet:
6532 if self.horizontal_dpi:
6533 attributes.append(("horizontalDpi", self.horizontal_dpi))
6535 if self.vertical_dpi:
6536 attributes.append(("verticalDpi", self.vertical_dpi))
6537 else:
6538 if self.vertical_dpi:
6539 attributes.append(("verticalDpi", self.vertical_dpi))
6541 if self.horizontal_dpi:
6542 attributes.append(("horizontalDpi", self.horizontal_dpi))
6544 self._xml_empty_tag("pageSetup", attributes)
6546 def _write_print_options(self) -> None:
6547 # Write the <printOptions> element.
6548 attributes = []
6550 if not self.print_options_changed:
6551 return
6553 # Set horizontal centering.
6554 if self.hcenter:
6555 attributes.append(("horizontalCentered", 1))
6557 # Set vertical centering.
6558 if self.vcenter:
6559 attributes.append(("verticalCentered", 1))
6561 # Enable row and column headers.
6562 if self.print_headers:
6563 attributes.append(("headings", 1))
6565 # Set printed gridlines.
6566 if self.print_gridlines:
6567 attributes.append(("gridLines", 1))
6569 self._xml_empty_tag("printOptions", attributes)
6571 def _write_header_footer(self) -> None:
6572 # Write the <headerFooter> element.
6573 attributes = []
6575 if not self.header_footer_scales:
6576 attributes.append(("scaleWithDoc", 0))
6578 if not self.header_footer_aligns:
6579 attributes.append(("alignWithMargins", 0))
6581 if self.header_footer_changed:
6582 self._xml_start_tag("headerFooter", attributes)
6583 if self.header:
6584 self._write_odd_header()
6585 if self.footer:
6586 self._write_odd_footer()
6587 self._xml_end_tag("headerFooter")
6588 elif self.excel2003_style:
6589 self._xml_empty_tag("headerFooter", attributes)
6591 def _write_odd_header(self) -> None:
6592 # Write the <headerFooter> element.
6593 self._xml_data_element("oddHeader", self.header)
6595 def _write_odd_footer(self) -> None:
6596 # Write the <headerFooter> element.
6597 self._xml_data_element("oddFooter", self.footer)
6599 def _write_rows(self) -> None:
6600 # Write out the worksheet data as a series of rows and cells.
6601 self._calculate_spans()
6603 for row_num in range(self.dim_rowmin, self.dim_rowmax + 1):
6604 if (
6605 row_num in self.set_rows
6606 or row_num in self.comments
6607 or self.table[row_num]
6608 ):
6609 # Only process rows with formatting, cell data and/or comments.
6611 span_index = int(row_num / 16)
6613 if span_index in self.row_spans:
6614 span = self.row_spans[span_index]
6615 else:
6616 span = None
6618 if self.table[row_num]:
6619 # Write the cells if the row contains data.
6620 if row_num not in self.set_rows:
6621 self._write_row(row_num, span)
6622 else:
6623 self._write_row(row_num, span, self.set_rows[row_num])
6625 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6626 if col_num in self.table[row_num]:
6627 col_ref = self.table[row_num][col_num]
6628 self._write_cell(row_num, col_num, col_ref)
6630 self._xml_end_tag("row")
6632 elif row_num in self.comments:
6633 # Row with comments in cells.
6634 self._write_empty_row(row_num, span, self.set_rows[row_num])
6635 else:
6636 # Blank row with attributes only.
6637 self._write_empty_row(row_num, span, self.set_rows[row_num])
6639 def _write_single_row(self, current_row_num=0) -> None:
6640 # Write out the worksheet data as a single row with cells.
6641 # This method is used when constant_memory is on. A single
6642 # row is written and the data table is reset. That way only
6643 # one row of data is kept in memory at any one time. We don't
6644 # write span data in the optimized case since it is optional.
6646 # Set the new previous row as the current row.
6647 row_num = self.previous_row
6648 self.previous_row = current_row_num
6650 if row_num in self.set_rows or row_num in self.comments or self.table[row_num]:
6651 # Only process rows with formatting, cell data and/or comments.
6653 # No span data in optimized mode.
6654 span = None
6656 if self.table[row_num]:
6657 # Write the cells if the row contains data.
6658 if row_num not in self.set_rows:
6659 self._write_row(row_num, span)
6660 else:
6661 self._write_row(row_num, span, self.set_rows[row_num])
6663 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6664 if col_num in self.table[row_num]:
6665 col_ref = self.table[row_num][col_num]
6666 self._write_cell(row_num, col_num, col_ref)
6668 self._xml_end_tag("row")
6669 else:
6670 # Row attributes or comments only.
6671 self._write_empty_row(row_num, span, self.set_rows[row_num])
6673 # Reset table.
6674 self.table.clear()
6676 def _calculate_spans(self) -> None:
6677 # Calculate the "spans" attribute of the <row> tag. This is an
6678 # XLSX optimization and isn't strictly required. However, it
6679 # makes comparing files easier. The span is the same for each
6680 # block of 16 rows.
6681 spans = {}
6682 span_min = None
6683 span_max = None
6685 for row_num in range(self.dim_rowmin, self.dim_rowmax + 1):
6686 if row_num in self.table:
6687 # Calculate spans for cell data.
6688 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6689 if col_num in self.table[row_num]:
6690 if span_min is None:
6691 span_min = col_num
6692 span_max = col_num
6693 else:
6694 span_min = min(span_min, col_num)
6695 span_max = max(span_max, col_num)
6697 if row_num in self.comments:
6698 # Calculate spans for comments.
6699 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6700 if row_num in self.comments and col_num in self.comments[row_num]:
6701 if span_min is None:
6702 span_min = col_num
6703 span_max = col_num
6704 else:
6705 span_min = min(span_min, col_num)
6706 span_max = max(span_max, col_num)
6708 if ((row_num + 1) % 16 == 0) or row_num == self.dim_rowmax:
6709 span_index = int(row_num / 16)
6711 if span_min is not None:
6712 span_min += 1
6713 span_max += 1
6714 spans[span_index] = f"{span_min}:{span_max}"
6715 span_min = None
6717 self.row_spans = spans
6719 def _write_row(self, row: int, spans, properties=None, empty_row=False) -> None:
6720 # Write the <row> element.
6721 xf_index = 0
6723 if properties:
6724 height, cell_format, hidden, level, collapsed = properties
6725 else:
6726 height, cell_format, hidden, level, collapsed = None, None, 0, 0, 0
6728 if height is None:
6729 height = self.default_row_height
6731 attributes = [("r", row + 1)]
6733 # Get the cell_format index.
6734 if cell_format:
6735 xf_index = cell_format._get_xf_index()
6737 # Add row attributes where applicable.
6738 if spans:
6739 attributes.append(("spans", spans))
6741 if xf_index:
6742 attributes.append(("s", xf_index))
6744 if cell_format:
6745 attributes.append(("customFormat", 1))
6747 if height != self.original_row_height or (
6748 height == self.original_row_height and height != self.default_row_height
6749 ):
6750 attributes.append(("ht", f"{height:g}"))
6752 if hidden:
6753 attributes.append(("hidden", 1))
6755 if height != self.original_row_height or (
6756 height == self.original_row_height and height != self.default_row_height
6757 ):
6758 attributes.append(("customHeight", 1))
6760 if level:
6761 attributes.append(("outlineLevel", level))
6763 if collapsed:
6764 attributes.append(("collapsed", 1))
6766 if self.excel_version == 2010:
6767 attributes.append(("x14ac:dyDescent", "0.25"))
6769 if empty_row:
6770 self._xml_empty_tag_unencoded("row", attributes)
6771 else:
6772 self._xml_start_tag_unencoded("row", attributes)
6774 def _write_empty_row(self, row: int, spans, properties=None) -> None:
6775 # Write and empty <row> element.
6776 self._write_row(row, spans, properties, empty_row=True)
6778 def _write_cell(self, row: int, col: int, cell) -> None:
6779 # Write the <cell> element.
6780 # Note. This is the innermost loop so efficiency is important.
6782 cell_range = xl_rowcol_to_cell_fast(row, col)
6783 attributes = [("r", cell_range)]
6785 if cell.format:
6786 # Add the cell format index.
6787 xf_index = cell.format._get_xf_index()
6788 attributes.append(("s", xf_index))
6789 elif row in self.set_rows and self.set_rows[row][1]:
6790 # Add the row format.
6791 row_xf = self.set_rows[row][1]
6792 attributes.append(("s", row_xf._get_xf_index()))
6793 elif col in self.col_info:
6794 # Add the column format.
6795 col_xf = self.col_info[col][1]
6796 if col_xf is not None:
6797 attributes.append(("s", col_xf._get_xf_index()))
6799 type_cell_name = cell.__class__.__name__
6801 # Write the various cell types.
6802 if type_cell_name in ("Number", "Datetime"):
6803 # Write a number.
6804 self._xml_number_element(cell.number, attributes)
6806 elif type_cell_name in ("String", "RichString"):
6807 # Write a string.
6808 string = cell.string
6810 if not self.constant_memory:
6811 # Write a shared string.
6812 self._xml_string_element(string, attributes)
6813 else:
6814 # Write an optimized in-line string.
6816 # Convert control character to a _xHHHH_ escape.
6817 string = self._escape_control_characters(string)
6819 # Write any rich strings without further tags.
6820 if string.startswith("<r>") and string.endswith("</r>"):
6821 self._xml_rich_inline_string(string, attributes)
6822 else:
6823 # Add attribute to preserve leading or trailing whitespace.
6824 preserve = _preserve_whitespace(string)
6825 self._xml_inline_string(string, preserve, attributes)
6827 elif type_cell_name == "Formula":
6828 # Write a formula. First check the formula value type.
6829 value = cell.value
6830 if isinstance(cell.value, bool):
6831 attributes.append(("t", "b"))
6832 if cell.value:
6833 value = 1
6834 else:
6835 value = 0
6837 elif isinstance(cell.value, str):
6838 error_codes = (
6839 "#DIV/0!",
6840 "#N/A",
6841 "#NAME?",
6842 "#NULL!",
6843 "#NUM!",
6844 "#REF!",
6845 "#VALUE!",
6846 )
6848 if cell.value == "":
6849 # Allow blank to force recalc in some third party apps.
6850 pass
6851 elif cell.value in error_codes:
6852 attributes.append(("t", "e"))
6853 else:
6854 attributes.append(("t", "str"))
6856 self._xml_formula_element(cell.formula, value, attributes)
6858 elif type_cell_name == "ArrayFormula":
6859 # Write a array formula.
6861 if cell.atype == "dynamic":
6862 attributes.append(("cm", 1))
6864 # First check if the formula value is a string.
6865 try:
6866 float(cell.value)
6867 except ValueError:
6868 attributes.append(("t", "str"))
6870 # Write an array formula.
6871 self._xml_start_tag("c", attributes)
6873 self._write_cell_array_formula(cell.formula, cell.range)
6874 self._write_cell_value(cell.value)
6875 self._xml_end_tag("c")
6877 elif type_cell_name == "Blank":
6878 # Write a empty cell.
6879 self._xml_empty_tag("c", attributes)
6881 elif type_cell_name == "Boolean":
6882 # Write a boolean cell.
6883 attributes.append(("t", "b"))
6884 self._xml_start_tag("c", attributes)
6885 self._write_cell_value(cell.boolean)
6886 self._xml_end_tag("c")
6888 elif type_cell_name == "Error":
6889 # Write a boolean cell.
6890 attributes.append(("t", "e"))
6891 attributes.append(("vm", cell.value))
6892 self._xml_start_tag("c", attributes)
6893 self._write_cell_value(cell.error)
6894 self._xml_end_tag("c")
6896 def _write_cell_value(self, value) -> None:
6897 # Write the cell value <v> element.
6898 if value is None:
6899 value = ""
6901 self._xml_data_element("v", value)
6903 def _write_cell_array_formula(self, formula, cell_range) -> None:
6904 # Write the cell array formula <f> element.
6905 attributes = [("t", "array"), ("ref", cell_range)]
6907 self._xml_data_element("f", formula, attributes)
6909 def _write_sheet_pr(self) -> None:
6910 # Write the <sheetPr> element for Sheet level properties.
6911 attributes = []
6913 if (
6914 not self.fit_page
6915 and not self.filter_on
6916 and not self.tab_color
6917 and not self.outline_changed
6918 and not self.vba_codename
6919 ):
6920 return
6922 if self.vba_codename:
6923 attributes.append(("codeName", self.vba_codename))
6925 if self.filter_on:
6926 attributes.append(("filterMode", 1))
6928 if self.fit_page or self.tab_color or self.outline_changed:
6929 self._xml_start_tag("sheetPr", attributes)
6930 self._write_tab_color()
6931 self._write_outline_pr()
6932 self._write_page_set_up_pr()
6933 self._xml_end_tag("sheetPr")
6934 else:
6935 self._xml_empty_tag("sheetPr", attributes)
6937 def _write_page_set_up_pr(self) -> None:
6938 # Write the <pageSetUpPr> element.
6939 if not self.fit_page:
6940 return
6942 attributes = [("fitToPage", 1)]
6943 self._xml_empty_tag("pageSetUpPr", attributes)
6945 def _write_tab_color(self) -> None:
6946 # Write the <tabColor> element.
6947 color = self.tab_color
6949 if not color:
6950 return
6952 self._write_color("tabColor", color._attributes())
6954 def _write_outline_pr(self) -> None:
6955 # Write the <outlinePr> element.
6956 attributes = []
6958 if not self.outline_changed:
6959 return
6961 if self.outline_style:
6962 attributes.append(("applyStyles", 1))
6963 if not self.outline_below:
6964 attributes.append(("summaryBelow", 0))
6965 if not self.outline_right:
6966 attributes.append(("summaryRight", 0))
6967 if not self.outline_on:
6968 attributes.append(("showOutlineSymbols", 0))
6970 self._xml_empty_tag("outlinePr", attributes)
6972 def _write_row_breaks(self) -> None:
6973 # Write the <rowBreaks> element.
6974 page_breaks = self._sort_pagebreaks(self.hbreaks)
6976 if not page_breaks:
6977 return
6979 count = len(page_breaks)
6981 attributes = [
6982 ("count", count),
6983 ("manualBreakCount", count),
6984 ]
6986 self._xml_start_tag("rowBreaks", attributes)
6988 for row_num in page_breaks:
6989 self._write_brk(row_num, 16383)
6991 self._xml_end_tag("rowBreaks")
6993 def _write_col_breaks(self) -> None:
6994 # Write the <colBreaks> element.
6995 page_breaks = self._sort_pagebreaks(self.vbreaks)
6997 if not page_breaks:
6998 return
7000 count = len(page_breaks)
7002 attributes = [
7003 ("count", count),
7004 ("manualBreakCount", count),
7005 ]
7007 self._xml_start_tag("colBreaks", attributes)
7009 for col_num in page_breaks:
7010 self._write_brk(col_num, 1048575)
7012 self._xml_end_tag("colBreaks")
7014 def _write_brk(self, brk_id, brk_max) -> None:
7015 # Write the <brk> element.
7016 attributes = [("id", brk_id), ("max", brk_max), ("man", 1)]
7018 self._xml_empty_tag("brk", attributes)
7020 def _write_merge_cells(self) -> None:
7021 # Write the <mergeCells> element.
7022 merged_cells = self.merge
7023 count = len(merged_cells)
7025 if not count:
7026 return
7028 attributes = [("count", count)]
7030 self._xml_start_tag("mergeCells", attributes)
7032 for merged_range in merged_cells:
7033 # Write the mergeCell element.
7034 self._write_merge_cell(merged_range)
7036 self._xml_end_tag("mergeCells")
7038 def _write_merge_cell(self, merged_range) -> None:
7039 # Write the <mergeCell> element.
7040 (row_min, col_min, row_max, col_max) = merged_range
7042 # Convert the merge dimensions to a cell range.
7043 cell_1 = xl_rowcol_to_cell(row_min, col_min)
7044 cell_2 = xl_rowcol_to_cell(row_max, col_max)
7045 ref = cell_1 + ":" + cell_2
7047 attributes = [("ref", ref)]
7049 self._xml_empty_tag("mergeCell", attributes)
7051 def _write_hyperlinks(self) -> None:
7052 # Process any stored hyperlinks in row/col order and write the
7053 # <hyperlinks> element. The attributes are different for internal
7054 # and external links.
7056 # Sort the hyperlinks into row order.
7057 row_nums = sorted(self.hyperlinks.keys())
7059 # Exit if there are no hyperlinks to process.
7060 if not row_nums:
7061 return
7063 # Write the hyperlink elements.
7064 self._xml_start_tag("hyperlinks")
7066 # Iterate over the rows.
7067 for row_num in row_nums:
7068 # Sort the hyperlinks into column order.
7069 col_nums = sorted(self.hyperlinks[row_num].keys())
7071 # Iterate over the columns.
7072 for col_num in col_nums:
7073 # Get the link data for this cell.
7074 url = self.hyperlinks[row_num][col_num]
7076 # If the cell was overwritten by the user and isn't a string
7077 # then we have to add the url as the string to display.
7078 if self.table and self.table[row_num] and self.table[row_num][col_num]:
7079 cell = self.table[row_num][col_num]
7080 if cell.__class__.__name__ != "String":
7081 url._is_object_link = True
7083 if url._link_type in (UrlTypes.URL, UrlTypes.EXTERNAL):
7084 # External link with rel file relationship.
7085 self.rel_count += 1
7087 self._write_hyperlink_external(
7088 row_num, col_num, self.rel_count, url
7089 )
7091 # Links for use by the packager.
7092 self.external_hyper_links.append(
7093 ["/hyperlink", url._target(), "External"]
7094 )
7095 else:
7096 # Internal link with rel file relationship.
7097 self._write_hyperlink_internal(row_num, col_num, url)
7099 self._xml_end_tag("hyperlinks")
7101 def _write_hyperlink_external(
7102 self, row: int, col: int, id_num: int, url: Url
7103 ) -> None:
7104 # Write the <hyperlink> element for external links.
7105 ref = xl_rowcol_to_cell(row, col)
7106 r_id = "rId" + str(id_num)
7108 attributes = [("ref", ref), ("r:id", r_id)]
7110 if url._anchor:
7111 attributes.append(("location", url._anchor))
7113 if url._is_object_link:
7114 attributes.append(("display", url._text))
7116 if url._tip:
7117 attributes.append(("tooltip", url._tip))
7119 self._xml_empty_tag("hyperlink", attributes)
7121 def _write_hyperlink_internal(self, row: int, col: int, url: Url) -> None:
7122 # Write the <hyperlink> element for internal links.
7123 ref = xl_rowcol_to_cell(row, col)
7125 attributes = [("ref", ref), ("location", url._link)]
7127 if url._tip:
7128 attributes.append(("tooltip", url._tip))
7130 attributes.append(("display", url._text))
7132 self._xml_empty_tag("hyperlink", attributes)
7134 def _write_auto_filter(self) -> None:
7135 # Write the <autoFilter> element.
7136 if not self.autofilter_ref:
7137 return
7139 attributes = [("ref", self.autofilter_ref)]
7141 if self.filter_on:
7142 # Autofilter defined active filters.
7143 self._xml_start_tag("autoFilter", attributes)
7144 self._write_autofilters()
7145 self._xml_end_tag("autoFilter")
7147 else:
7148 # Autofilter defined without active filters.
7149 self._xml_empty_tag("autoFilter", attributes)
7151 def _write_autofilters(self) -> None:
7152 # Function to iterate through the columns that form part of an
7153 # autofilter range and write the appropriate filters.
7154 (col1, col2) = self.filter_range
7156 for col in range(col1, col2 + 1):
7157 # Skip if column doesn't have an active filter.
7158 if col not in self.filter_cols:
7159 continue
7161 # Retrieve the filter tokens and write the autofilter records.
7162 tokens = self.filter_cols[col]
7163 filter_type = self.filter_type[col]
7165 # Filters are relative to first column in the autofilter.
7166 self._write_filter_column(col - col1, filter_type, tokens)
7168 def _write_filter_column(self, col_id, filter_type, filters) -> None:
7169 # Write the <filterColumn> element.
7170 attributes = [("colId", col_id)]
7172 self._xml_start_tag("filterColumn", attributes)
7174 if filter_type == 1:
7175 # Type == 1 is the new XLSX style filter.
7176 self._write_filters(filters)
7177 else:
7178 # Type == 0 is the classic "custom" filter.
7179 self._write_custom_filters(filters)
7181 self._xml_end_tag("filterColumn")
7183 def _write_filters(self, filters) -> None:
7184 # Write the <filters> element.
7185 non_blanks = [filter for filter in filters if str(filter).lower() != "blanks"]
7186 attributes = []
7188 if len(filters) != len(non_blanks):
7189 attributes = [("blank", 1)]
7191 if len(filters) == 1 and len(non_blanks) == 0:
7192 # Special case for blank cells only.
7193 self._xml_empty_tag("filters", attributes)
7194 else:
7195 # General case.
7196 self._xml_start_tag("filters", attributes)
7198 for autofilter in sorted(non_blanks):
7199 self._write_filter(autofilter)
7201 self._xml_end_tag("filters")
7203 def _write_filter(self, val) -> None:
7204 # Write the <filter> element.
7205 attributes = [("val", val)]
7207 self._xml_empty_tag("filter", attributes)
7209 def _write_custom_filters(self, tokens) -> None:
7210 # Write the <customFilters> element.
7211 if len(tokens) == 2:
7212 # One filter expression only.
7213 self._xml_start_tag("customFilters")
7214 self._write_custom_filter(*tokens)
7215 self._xml_end_tag("customFilters")
7216 else:
7217 # Two filter expressions.
7218 attributes = []
7220 # Check if the "join" operand is "and" or "or".
7221 if tokens[2] == 0:
7222 attributes = [("and", 1)]
7223 else:
7224 attributes = [("and", 0)]
7226 # Write the two custom filters.
7227 self._xml_start_tag("customFilters", attributes)
7228 self._write_custom_filter(tokens[0], tokens[1])
7229 self._write_custom_filter(tokens[3], tokens[4])
7230 self._xml_end_tag("customFilters")
7232 def _write_custom_filter(self, operator, val) -> None:
7233 # Write the <customFilter> element.
7234 attributes = []
7236 operators = {
7237 1: "lessThan",
7238 2: "equal",
7239 3: "lessThanOrEqual",
7240 4: "greaterThan",
7241 5: "notEqual",
7242 6: "greaterThanOrEqual",
7243 22: "equal",
7244 }
7246 # Convert the operator from a number to a descriptive string.
7247 if operators[operator] is not None:
7248 operator = operators[operator]
7249 else:
7250 warn(f"Unknown operator = {operator}")
7252 # The 'equal' operator is the default attribute and isn't stored.
7253 if operator != "equal":
7254 attributes.append(("operator", operator))
7255 attributes.append(("val", val))
7257 self._xml_empty_tag("customFilter", attributes)
7259 def _write_sheet_protection(self) -> None:
7260 # Write the <sheetProtection> element.
7261 attributes = []
7263 if not self.protect_options:
7264 return
7266 options = self.protect_options
7268 if options["password"]:
7269 attributes.append(("password", options["password"]))
7270 if options["sheet"]:
7271 attributes.append(("sheet", 1))
7272 if options["content"]:
7273 attributes.append(("content", 1))
7274 if not options["objects"]:
7275 attributes.append(("objects", 1))
7276 if not options["scenarios"]:
7277 attributes.append(("scenarios", 1))
7278 if options["format_cells"]:
7279 attributes.append(("formatCells", 0))
7280 if options["format_columns"]:
7281 attributes.append(("formatColumns", 0))
7282 if options["format_rows"]:
7283 attributes.append(("formatRows", 0))
7284 if options["insert_columns"]:
7285 attributes.append(("insertColumns", 0))
7286 if options["insert_rows"]:
7287 attributes.append(("insertRows", 0))
7288 if options["insert_hyperlinks"]:
7289 attributes.append(("insertHyperlinks", 0))
7290 if options["delete_columns"]:
7291 attributes.append(("deleteColumns", 0))
7292 if options["delete_rows"]:
7293 attributes.append(("deleteRows", 0))
7294 if not options["select_locked_cells"]:
7295 attributes.append(("selectLockedCells", 1))
7296 if options["sort"]:
7297 attributes.append(("sort", 0))
7298 if options["autofilter"]:
7299 attributes.append(("autoFilter", 0))
7300 if options["pivot_tables"]:
7301 attributes.append(("pivotTables", 0))
7302 if not options["select_unlocked_cells"]:
7303 attributes.append(("selectUnlockedCells", 1))
7305 self._xml_empty_tag("sheetProtection", attributes)
7307 def _write_protected_ranges(self) -> None:
7308 # Write the <protectedRanges> element.
7309 if self.num_protected_ranges == 0:
7310 return
7312 self._xml_start_tag("protectedRanges")
7314 for cell_range, range_name, password in self.protected_ranges:
7315 self._write_protected_range(cell_range, range_name, password)
7317 self._xml_end_tag("protectedRanges")
7319 def _write_protected_range(self, cell_range, range_name, password) -> None:
7320 # Write the <protectedRange> element.
7321 attributes = []
7323 if password:
7324 attributes.append(("password", password))
7326 attributes.append(("sqref", cell_range))
7327 attributes.append(("name", range_name))
7329 self._xml_empty_tag("protectedRange", attributes)
7331 def _write_drawings(self) -> None:
7332 # Write the <drawing> elements.
7333 if not self.drawing:
7334 return
7336 self.rel_count += 1
7337 self._write_drawing(self.rel_count)
7339 def _write_drawing(self, drawing_id) -> None:
7340 # Write the <drawing> element.
7341 r_id = "rId" + str(drawing_id)
7343 attributes = [("r:id", r_id)]
7345 self._xml_empty_tag("drawing", attributes)
7347 def _write_legacy_drawing(self) -> None:
7348 # Write the <legacyDrawing> element.
7349 if not self.has_vml:
7350 return
7352 # Increment the relationship id for any drawings or comments.
7353 self.rel_count += 1
7354 r_id = "rId" + str(self.rel_count)
7356 attributes = [("r:id", r_id)]
7358 self._xml_empty_tag("legacyDrawing", attributes)
7360 def _write_legacy_drawing_hf(self) -> None:
7361 # Write the <legacyDrawingHF> element.
7362 if not self.has_header_vml:
7363 return
7365 # Increment the relationship id for any drawings or comments.
7366 self.rel_count += 1
7367 r_id = "rId" + str(self.rel_count)
7369 attributes = [("r:id", r_id)]
7371 self._xml_empty_tag("legacyDrawingHF", attributes)
7373 def _write_picture(self) -> None:
7374 # Write the <picture> element.
7375 if not self.background_image:
7376 return
7378 # Increment the relationship id.
7379 self.rel_count += 1
7380 r_id = "rId" + str(self.rel_count)
7382 attributes = [("r:id", r_id)]
7384 self._xml_empty_tag("picture", attributes)
7386 def _write_data_validations(self) -> None:
7387 # Write the <dataValidations> element.
7388 validations = self.validations
7389 count = len(validations)
7391 if not count:
7392 return
7394 attributes = [("count", count)]
7396 self._xml_start_tag("dataValidations", attributes)
7398 for validation in validations:
7399 # Write the dataValidation element.
7400 self._write_data_validation(validation)
7402 self._xml_end_tag("dataValidations")
7404 def _write_data_validation(self, options) -> None:
7405 # Write the <dataValidation> element.
7406 sqref = ""
7407 attributes = []
7409 # Set the cell range(s) for the data validation.
7410 for cells in options["cells"]:
7411 # Add a space between multiple cell ranges.
7412 if sqref != "":
7413 sqref += " "
7415 (row_first, col_first, row_last, col_last) = cells
7417 # Swap last row/col for first row/col as necessary
7418 if row_first > row_last:
7419 (row_first, row_last) = (row_last, row_first)
7421 if col_first > col_last:
7422 (col_first, col_last) = (col_last, col_first)
7424 sqref += xl_range(row_first, col_first, row_last, col_last)
7426 if options.get("multi_range"):
7427 sqref = options["multi_range"]
7429 if options["validate"] != "none":
7430 attributes.append(("type", options["validate"]))
7432 if options["criteria"] != "between":
7433 attributes.append(("operator", options["criteria"]))
7435 if "error_type" in options:
7436 if options["error_type"] == 1:
7437 attributes.append(("errorStyle", "warning"))
7438 if options["error_type"] == 2:
7439 attributes.append(("errorStyle", "information"))
7441 if options["ignore_blank"]:
7442 attributes.append(("allowBlank", 1))
7444 if not options["dropdown"]:
7445 attributes.append(("showDropDown", 1))
7447 if options["show_input"]:
7448 attributes.append(("showInputMessage", 1))
7450 if options["show_error"]:
7451 attributes.append(("showErrorMessage", 1))
7453 if "error_title" in options:
7454 attributes.append(("errorTitle", options["error_title"]))
7456 if "error_message" in options:
7457 attributes.append(("error", options["error_message"]))
7459 if "input_title" in options:
7460 attributes.append(("promptTitle", options["input_title"]))
7462 if "input_message" in options:
7463 attributes.append(("prompt", options["input_message"]))
7465 attributes.append(("sqref", sqref))
7467 if options["validate"] == "none":
7468 self._xml_empty_tag("dataValidation", attributes)
7469 else:
7470 self._xml_start_tag("dataValidation", attributes)
7472 # Write the formula1 element.
7473 self._write_formula_1(options["value"])
7475 # Write the formula2 element.
7476 if options["maximum"] is not None:
7477 self._write_formula_2(options["maximum"])
7479 self._xml_end_tag("dataValidation")
7481 def _write_formula_1(self, formula) -> None:
7482 # Write the <formula1> element.
7484 if isinstance(formula, list):
7485 formula = self._csv_join(*formula)
7486 formula = f'"{formula}"'
7487 else:
7488 # Check if the formula is a number.
7489 try:
7490 float(formula)
7491 except ValueError:
7492 # Not a number. Remove the formula '=' sign if it exists.
7493 if formula.startswith("="):
7494 formula = formula.lstrip("=")
7496 self._xml_data_element("formula1", formula)
7498 def _write_formula_2(self, formula) -> None:
7499 # Write the <formula2> element.
7501 # Check if the formula is a number.
7502 try:
7503 float(formula)
7504 except ValueError:
7505 # Not a number. Remove the formula '=' sign if it exists.
7506 if formula.startswith("="):
7507 formula = formula.lstrip("=")
7509 self._xml_data_element("formula2", formula)
7511 def _write_conditional_formats(self) -> None:
7512 # Write the Worksheet conditional formats.
7513 ranges = sorted(self.cond_formats.keys())
7515 if not ranges:
7516 return
7518 for cond_range in ranges:
7519 self._write_conditional_formatting(
7520 cond_range, self.cond_formats[cond_range]
7521 )
7523 def _write_conditional_formatting(self, cond_range, params) -> None:
7524 # Write the <conditionalFormatting> element.
7525 attributes = [("sqref", cond_range)]
7526 self._xml_start_tag("conditionalFormatting", attributes)
7527 for param in params:
7528 # Write the cfRule element.
7529 self._write_cf_rule(param)
7530 self._xml_end_tag("conditionalFormatting")
7532 def _write_cf_rule(self, params) -> None:
7533 # Write the <cfRule> element.
7534 attributes = [("type", params["type"])]
7536 if "format" in params and params["format"] is not None:
7537 attributes.append(("dxfId", params["format"]))
7539 attributes.append(("priority", params["priority"]))
7541 if params.get("stop_if_true"):
7542 attributes.append(("stopIfTrue", 1))
7544 if params["type"] == "cellIs":
7545 attributes.append(("operator", params["criteria"]))
7547 self._xml_start_tag("cfRule", attributes)
7549 if "minimum" in params and "maximum" in params:
7550 self._write_formula_element(params["minimum"])
7551 self._write_formula_element(params["maximum"])
7552 else:
7553 self._write_formula_element(params["value"])
7555 self._xml_end_tag("cfRule")
7557 elif params["type"] == "aboveAverage":
7558 if re.search("below", params["criteria"]):
7559 attributes.append(("aboveAverage", 0))
7561 if re.search("equal", params["criteria"]):
7562 attributes.append(("equalAverage", 1))
7564 if re.search("[123] std dev", params["criteria"]):
7565 match = re.search("([123]) std dev", params["criteria"])
7566 attributes.append(("stdDev", match.group(1)))
7568 self._xml_empty_tag("cfRule", attributes)
7570 elif params["type"] == "top10":
7571 if "criteria" in params and params["criteria"] == "%":
7572 attributes.append(("percent", 1))
7574 if "direction" in params:
7575 attributes.append(("bottom", 1))
7577 rank = params["value"] or 10
7578 attributes.append(("rank", rank))
7580 self._xml_empty_tag("cfRule", attributes)
7582 elif params["type"] == "duplicateValues":
7583 self._xml_empty_tag("cfRule", attributes)
7585 elif params["type"] == "uniqueValues":
7586 self._xml_empty_tag("cfRule", attributes)
7588 elif (
7589 params["type"] == "containsText"
7590 or params["type"] == "notContainsText"
7591 or params["type"] == "beginsWith"
7592 or params["type"] == "endsWith"
7593 ):
7594 attributes.append(("operator", params["criteria"]))
7595 attributes.append(("text", params["value"]))
7596 self._xml_start_tag("cfRule", attributes)
7597 self._write_formula_element(params["formula"])
7598 self._xml_end_tag("cfRule")
7600 elif params["type"] == "timePeriod":
7601 attributes.append(("timePeriod", params["criteria"]))
7602 self._xml_start_tag("cfRule", attributes)
7603 self._write_formula_element(params["formula"])
7604 self._xml_end_tag("cfRule")
7606 elif (
7607 params["type"] == "containsBlanks"
7608 or params["type"] == "notContainsBlanks"
7609 or params["type"] == "containsErrors"
7610 or params["type"] == "notContainsErrors"
7611 ):
7612 self._xml_start_tag("cfRule", attributes)
7613 self._write_formula_element(params["formula"])
7614 self._xml_end_tag("cfRule")
7616 elif params["type"] == "colorScale":
7617 self._xml_start_tag("cfRule", attributes)
7618 self._write_color_scale(params)
7619 self._xml_end_tag("cfRule")
7621 elif params["type"] == "dataBar":
7622 self._xml_start_tag("cfRule", attributes)
7623 self._write_data_bar(params)
7625 if params.get("is_data_bar_2010"):
7626 self._write_data_bar_ext(params)
7628 self._xml_end_tag("cfRule")
7630 elif params["type"] == "expression":
7631 self._xml_start_tag("cfRule", attributes)
7632 self._write_formula_element(params["criteria"])
7633 self._xml_end_tag("cfRule")
7635 elif params["type"] == "iconSet":
7636 self._xml_start_tag("cfRule", attributes)
7637 self._write_icon_set(params)
7638 self._xml_end_tag("cfRule")
7640 def _write_formula_element(self, formula) -> None:
7641 # Write the <formula> element.
7643 # Check if the formula is a number.
7644 try:
7645 float(formula)
7646 except ValueError:
7647 # Not a number. Remove the formula '=' sign if it exists.
7648 if formula.startswith("="):
7649 formula = formula.lstrip("=")
7651 self._xml_data_element("formula", formula)
7653 def _write_color_scale(self, param) -> None:
7654 # Write the <colorScale> element.
7656 self._xml_start_tag("colorScale")
7658 self._write_cfvo(param["min_type"], param["min_value"])
7660 if param["mid_type"] is not None:
7661 self._write_cfvo(param["mid_type"], param["mid_value"])
7663 self._write_cfvo(param["max_type"], param["max_value"])
7665 self._write_color("color", param["min_color"]._attributes())
7667 if param["mid_color"] is not None:
7668 self._write_color("color", param["mid_color"]._attributes())
7670 self._write_color("color", param["max_color"]._attributes())
7672 self._xml_end_tag("colorScale")
7674 def _write_data_bar(self, param) -> None:
7675 # Write the <dataBar> element.
7676 attributes = []
7678 # Min and max bar lengths in in the spec but not supported directly by
7679 # Excel.
7680 if "min_length" in param:
7681 attributes.append(("minLength", param["min_length"]))
7683 if "max_length" in param:
7684 attributes.append(("maxLength", param["max_length"]))
7686 if param.get("bar_only"):
7687 attributes.append(("showValue", 0))
7689 self._xml_start_tag("dataBar", attributes)
7691 self._write_cfvo(param["min_type"], param["min_value"])
7692 self._write_cfvo(param["max_type"], param["max_value"])
7693 self._write_color("color", param["bar_color"]._attributes())
7695 self._xml_end_tag("dataBar")
7697 def _write_data_bar_ext(self, param) -> None:
7698 # Write the <extLst> dataBar extension element.
7700 # Create a pseudo GUID for each unique Excel 2010 data bar.
7701 worksheet_count = self.index + 1
7702 data_bar_count = len(self.data_bars_2010) + 1
7703 guid = "{DA7ABA51-AAAA-BBBB-%04X-%012X}" % (worksheet_count, data_bar_count)
7705 # Store the 2010 data bar parameters to write the extLst elements.
7706 param["guid"] = guid
7707 self.data_bars_2010.append(param)
7709 self._xml_start_tag("extLst")
7710 self._write_ext("{B025F937-C7B1-47D3-B67F-A62EFF666E3E}")
7711 self._xml_data_element("x14:id", guid)
7712 self._xml_end_tag("ext")
7713 self._xml_end_tag("extLst")
7715 def _write_icon_set(self, param) -> None:
7716 # Write the <iconSet> element.
7717 attributes = []
7719 # Don't set attribute for default style.
7720 if param["icon_style"] != "3TrafficLights":
7721 attributes = [("iconSet", param["icon_style"])]
7723 if param.get("icons_only"):
7724 attributes.append(("showValue", 0))
7726 if param.get("reverse_icons"):
7727 attributes.append(("reverse", 1))
7729 self._xml_start_tag("iconSet", attributes)
7731 # Write the properties for different icon styles.
7732 for icon in reversed(param["icons"]):
7733 self._write_cfvo(icon["type"], icon["value"], icon["criteria"])
7735 self._xml_end_tag("iconSet")
7737 def _write_cfvo(self, cf_type, val, criteria=None) -> None:
7738 # Write the <cfvo> element.
7739 attributes = [("type", cf_type)]
7741 if val is not None:
7742 attributes.append(("val", val))
7744 if criteria:
7745 attributes.append(("gte", 0))
7747 self._xml_empty_tag("cfvo", attributes)
7749 def _write_color(self, name, attributes) -> None:
7750 # Write the <color> element.
7751 self._xml_empty_tag(name, attributes)
7753 def _write_selections(self) -> None:
7754 # Write the <selection> elements.
7755 for selection in self.selections:
7756 self._write_selection(*selection)
7758 def _write_selection(self, pane, active_cell, sqref) -> None:
7759 # Write the <selection> element.
7760 attributes = []
7762 if pane:
7763 attributes.append(("pane", pane))
7765 if active_cell:
7766 attributes.append(("activeCell", active_cell))
7768 if sqref:
7769 attributes.append(("sqref", sqref))
7771 self._xml_empty_tag("selection", attributes)
7773 def _write_panes(self) -> None:
7774 # Write the frozen or split <pane> elements.
7775 panes = self.panes
7777 if not panes:
7778 return
7780 if panes[4] == 2:
7781 self._write_split_panes(*panes)
7782 else:
7783 self._write_freeze_panes(*panes)
7785 def _write_freeze_panes(
7786 self, row: int, col: int, top_row, left_col, pane_type
7787 ) -> None:
7788 # Write the <pane> element for freeze panes.
7789 attributes = []
7791 y_split = row
7792 x_split = col
7793 top_left_cell = xl_rowcol_to_cell(top_row, left_col)
7794 active_pane = ""
7795 state = ""
7796 active_cell = ""
7797 sqref = ""
7799 # Move user cell selection to the panes.
7800 if self.selections:
7801 (_, active_cell, sqref) = self.selections[0]
7802 self.selections = []
7804 # Set the active pane.
7805 if row and col:
7806 active_pane = "bottomRight"
7808 row_cell = xl_rowcol_to_cell(row, 0)
7809 col_cell = xl_rowcol_to_cell(0, col)
7811 self.selections.append(["topRight", col_cell, col_cell])
7812 self.selections.append(["bottomLeft", row_cell, row_cell])
7813 self.selections.append(["bottomRight", active_cell, sqref])
7815 elif col:
7816 active_pane = "topRight"
7817 self.selections.append(["topRight", active_cell, sqref])
7819 else:
7820 active_pane = "bottomLeft"
7821 self.selections.append(["bottomLeft", active_cell, sqref])
7823 # Set the pane type.
7824 if pane_type == 0:
7825 state = "frozen"
7826 elif pane_type == 1:
7827 state = "frozenSplit"
7828 else:
7829 state = "split"
7831 if x_split:
7832 attributes.append(("xSplit", x_split))
7834 if y_split:
7835 attributes.append(("ySplit", y_split))
7837 attributes.append(("topLeftCell", top_left_cell))
7838 attributes.append(("activePane", active_pane))
7839 attributes.append(("state", state))
7841 self._xml_empty_tag("pane", attributes)
7843 def _write_split_panes(self, row: int, col: int, top_row, left_col, _) -> None:
7844 # Write the <pane> element for split panes.
7845 attributes = []
7846 has_selection = False
7847 active_pane = ""
7848 active_cell = ""
7849 sqref = ""
7851 y_split = row
7852 x_split = col
7854 # Move user cell selection to the panes.
7855 if self.selections:
7856 (_, active_cell, sqref) = self.selections[0]
7857 self.selections = []
7858 has_selection = True
7860 # Convert the row and col to 1/20 twip units with padding.
7861 if y_split:
7862 y_split = int(20 * y_split + 300)
7864 if x_split:
7865 x_split = self._calculate_x_split_width(x_split)
7867 # For non-explicit topLeft definitions, estimate the cell offset based
7868 # on the pixels dimensions. This is only a workaround and doesn't take
7869 # adjusted cell dimensions into account.
7870 if top_row == row and left_col == col:
7871 top_row = int(0.5 + (y_split - 300) / 20 / 15)
7872 left_col = int(0.5 + (x_split - 390) / 20 / 3 * 4 / 64)
7874 top_left_cell = xl_rowcol_to_cell(top_row, left_col)
7876 # If there is no selection set the active cell to the top left cell.
7877 if not has_selection:
7878 active_cell = top_left_cell
7879 sqref = top_left_cell
7881 # Set the Cell selections.
7882 if row and col:
7883 active_pane = "bottomRight"
7885 row_cell = xl_rowcol_to_cell(top_row, 0)
7886 col_cell = xl_rowcol_to_cell(0, left_col)
7888 self.selections.append(["topRight", col_cell, col_cell])
7889 self.selections.append(["bottomLeft", row_cell, row_cell])
7890 self.selections.append(["bottomRight", active_cell, sqref])
7892 elif col:
7893 active_pane = "topRight"
7894 self.selections.append(["topRight", active_cell, sqref])
7896 else:
7897 active_pane = "bottomLeft"
7898 self.selections.append(["bottomLeft", active_cell, sqref])
7900 # Format splits to the same precision as Excel.
7901 if x_split:
7902 attributes.append(("xSplit", f"{x_split:.16g}"))
7904 if y_split:
7905 attributes.append(("ySplit", f"{y_split:.16g}"))
7907 attributes.append(("topLeftCell", top_left_cell))
7909 if has_selection:
7910 attributes.append(("activePane", active_pane))
7912 self._xml_empty_tag("pane", attributes)
7914 def _calculate_x_split_width(self, width):
7915 # Convert column width from user units to pane split width.
7917 max_digit_width = 7 # For Calabri 11.
7918 padding = 5
7920 # Convert to pixels.
7921 if width < 1:
7922 pixels = int(width * (max_digit_width + padding) + 0.5)
7923 else:
7924 pixels = int(width * max_digit_width + 0.5) + padding
7926 # Convert to points.
7927 points = pixels * 3 / 4
7929 # Convert to twips (twentieths of a point).
7930 twips = points * 20
7932 # Add offset/padding.
7933 width = twips + 390
7935 return width
7937 def _write_table_parts(self) -> None:
7938 # Write the <tableParts> element.
7939 tables = self.tables
7940 count = len(tables)
7942 # Return if worksheet doesn't contain any tables.
7943 if not count:
7944 return
7946 attributes = [
7947 (
7948 "count",
7949 count,
7950 )
7951 ]
7953 self._xml_start_tag("tableParts", attributes)
7955 for _ in tables:
7956 # Write the tablePart element.
7957 self.rel_count += 1
7958 self._write_table_part(self.rel_count)
7960 self._xml_end_tag("tableParts")
7962 def _write_table_part(self, r_id) -> None:
7963 # Write the <tablePart> element.
7965 r_id = "rId" + str(r_id)
7967 attributes = [
7968 (
7969 "r:id",
7970 r_id,
7971 )
7972 ]
7974 self._xml_empty_tag("tablePart", attributes)
7976 def _write_ext_list(self) -> None:
7977 # Write the <extLst> element for data bars and sparklines.
7978 has_data_bars = len(self.data_bars_2010)
7979 has_sparklines = len(self.sparklines)
7981 if not has_data_bars and not has_sparklines:
7982 return
7984 # Write the extLst element.
7985 self._xml_start_tag("extLst")
7987 if has_data_bars:
7988 self._write_ext_list_data_bars()
7990 if has_sparklines:
7991 self._write_ext_list_sparklines()
7993 self._xml_end_tag("extLst")
7995 def _write_ext_list_data_bars(self) -> None:
7996 # Write the Excel 2010 data_bar subelements.
7997 self._write_ext("{78C0D931-6437-407d-A8EE-F0AAD7539E65}")
7999 self._xml_start_tag("x14:conditionalFormattings")
8001 # Write the Excel 2010 conditional formatting data bar elements.
8002 for data_bar in self.data_bars_2010:
8003 # Write the x14:conditionalFormatting element.
8004 self._write_conditional_formatting_2010(data_bar)
8006 self._xml_end_tag("x14:conditionalFormattings")
8007 self._xml_end_tag("ext")
8009 def _write_conditional_formatting_2010(self, data_bar) -> None:
8010 # Write the <x14:conditionalFormatting> element.
8011 xmlns_xm = "http://schemas.microsoft.com/office/excel/2006/main"
8013 attributes = [("xmlns:xm", xmlns_xm)]
8015 self._xml_start_tag("x14:conditionalFormatting", attributes)
8017 # Write the x14:cfRule element.
8018 self._write_x14_cf_rule(data_bar)
8020 # Write the x14:dataBar element.
8021 self._write_x14_data_bar(data_bar)
8023 # Write the x14 max and min data bars.
8024 self._write_x14_cfvo(data_bar["x14_min_type"], data_bar["min_value"])
8025 self._write_x14_cfvo(data_bar["x14_max_type"], data_bar["max_value"])
8027 if not data_bar["bar_no_border"]:
8028 # Write the x14:borderColor element.
8029 self._write_x14_border_color(data_bar["bar_border_color"])
8031 # Write the x14:negativeFillColor element.
8032 if not data_bar["bar_negative_color_same"]:
8033 self._write_x14_negative_fill_color(data_bar["bar_negative_color"])
8035 # Write the x14:negativeBorderColor element.
8036 if (
8037 not data_bar["bar_no_border"]
8038 and not data_bar["bar_negative_border_color_same"]
8039 ):
8040 self._write_x14_negative_border_color(data_bar["bar_negative_border_color"])
8042 # Write the x14:axisColor element.
8043 if data_bar["bar_axis_position"] != "none":
8044 self._write_x14_axis_color(data_bar["bar_axis_color"])
8046 self._xml_end_tag("x14:dataBar")
8047 self._xml_end_tag("x14:cfRule")
8049 # Write the xm:sqref element.
8050 self._xml_data_element("xm:sqref", data_bar["range"])
8052 self._xml_end_tag("x14:conditionalFormatting")
8054 def _write_x14_cf_rule(self, data_bar) -> None:
8055 # Write the <x14:cfRule> element.
8056 rule_type = "dataBar"
8057 guid = data_bar["guid"]
8058 attributes = [("type", rule_type), ("id", guid)]
8060 self._xml_start_tag("x14:cfRule", attributes)
8062 def _write_x14_data_bar(self, data_bar) -> None:
8063 # Write the <x14:dataBar> element.
8064 min_length = 0
8065 max_length = 100
8067 attributes = [
8068 ("minLength", min_length),
8069 ("maxLength", max_length),
8070 ]
8072 if not data_bar["bar_no_border"]:
8073 attributes.append(("border", 1))
8075 if data_bar["bar_solid"]:
8076 attributes.append(("gradient", 0))
8078 if data_bar["bar_direction"] == "left":
8079 attributes.append(("direction", "leftToRight"))
8081 if data_bar["bar_direction"] == "right":
8082 attributes.append(("direction", "rightToLeft"))
8084 if data_bar["bar_negative_color_same"]:
8085 attributes.append(("negativeBarColorSameAsPositive", 1))
8087 if (
8088 not data_bar["bar_no_border"]
8089 and not data_bar["bar_negative_border_color_same"]
8090 ):
8091 attributes.append(("negativeBarBorderColorSameAsPositive", 0))
8093 if data_bar["bar_axis_position"] == "middle":
8094 attributes.append(("axisPosition", "middle"))
8096 if data_bar["bar_axis_position"] == "none":
8097 attributes.append(("axisPosition", "none"))
8099 self._xml_start_tag("x14:dataBar", attributes)
8101 def _write_x14_cfvo(self, rule_type, value) -> None:
8102 # Write the <x14:cfvo> element.
8103 attributes = [("type", rule_type)]
8105 if rule_type in ("min", "max", "autoMin", "autoMax"):
8106 self._xml_empty_tag("x14:cfvo", attributes)
8107 else:
8108 self._xml_start_tag("x14:cfvo", attributes)
8109 self._xml_data_element("xm:f", value)
8110 self._xml_end_tag("x14:cfvo")
8112 def _write_x14_border_color(self, color) -> None:
8113 # Write the <x14:borderColor> element.
8114 self._write_color("x14:borderColor", color._attributes())
8116 def _write_x14_negative_fill_color(self, color) -> None:
8117 # Write the <x14:negativeFillColor> element.
8118 self._xml_empty_tag("x14:negativeFillColor", color._attributes())
8120 def _write_x14_negative_border_color(self, color) -> None:
8121 # Write the <x14:negativeBorderColor> element.
8122 self._xml_empty_tag("x14:negativeBorderColor", color._attributes())
8124 def _write_x14_axis_color(self, color) -> None:
8125 # Write the <x14:axisColor> element.
8126 self._xml_empty_tag("x14:axisColor", color._attributes())
8128 def _write_ext_list_sparklines(self) -> None:
8129 # Write the sparkline extension sub-elements.
8130 self._write_ext("{05C60535-1F16-4fd2-B633-F4F36F0B64E0}")
8132 # Write the x14:sparklineGroups element.
8133 self._write_sparkline_groups()
8135 # Write the sparkline elements.
8136 for sparkline in reversed(self.sparklines):
8137 # Write the x14:sparklineGroup element.
8138 self._write_sparkline_group(sparkline)
8140 # Write the x14:colorSeries element.
8141 self._write_color_series(sparkline["series_color"])
8143 # Write the x14:colorNegative element.
8144 self._write_color_negative(sparkline["negative_color"])
8146 # Write the x14:colorAxis element.
8147 self._write_color_axis()
8149 # Write the x14:colorMarkers element.
8150 self._write_color_markers(sparkline["markers_color"])
8152 # Write the x14:colorFirst element.
8153 self._write_color_first(sparkline["first_color"])
8155 # Write the x14:colorLast element.
8156 self._write_color_last(sparkline["last_color"])
8158 # Write the x14:colorHigh element.
8159 self._write_color_high(sparkline["high_color"])
8161 # Write the x14:colorLow element.
8162 self._write_color_low(sparkline["low_color"])
8164 if sparkline["date_axis"]:
8165 self._xml_data_element("xm:f", sparkline["date_axis"])
8167 self._write_sparklines(sparkline)
8169 self._xml_end_tag("x14:sparklineGroup")
8171 self._xml_end_tag("x14:sparklineGroups")
8172 self._xml_end_tag("ext")
8174 def _write_sparklines(self, sparkline) -> None:
8175 # Write the <x14:sparklines> element and <x14:sparkline> sub-elements.
8177 # Write the sparkline elements.
8178 self._xml_start_tag("x14:sparklines")
8180 for i in range(sparkline["count"]):
8181 spark_range = sparkline["ranges"][i]
8182 location = sparkline["locations"][i]
8184 self._xml_start_tag("x14:sparkline")
8185 self._xml_data_element("xm:f", spark_range)
8186 self._xml_data_element("xm:sqref", location)
8187 self._xml_end_tag("x14:sparkline")
8189 self._xml_end_tag("x14:sparklines")
8191 def _write_ext(self, uri) -> None:
8192 # Write the <ext> element.
8193 schema = "http://schemas.microsoft.com/office/"
8194 xmlns_x14 = schema + "spreadsheetml/2009/9/main"
8196 attributes = [
8197 ("xmlns:x14", xmlns_x14),
8198 ("uri", uri),
8199 ]
8201 self._xml_start_tag("ext", attributes)
8203 def _write_sparkline_groups(self) -> None:
8204 # Write the <x14:sparklineGroups> element.
8205 xmlns_xm = "http://schemas.microsoft.com/office/excel/2006/main"
8207 attributes = [("xmlns:xm", xmlns_xm)]
8209 self._xml_start_tag("x14:sparklineGroups", attributes)
8211 def _write_sparkline_group(self, options) -> None:
8212 # Write the <x14:sparklineGroup> element.
8213 #
8214 # Example for order.
8215 #
8216 # <x14:sparklineGroup
8217 # manualMax="0"
8218 # manualMin="0"
8219 # lineWeight="2.25"
8220 # type="column"
8221 # dateAxis="1"
8222 # displayEmptyCellsAs="span"
8223 # markers="1"
8224 # high="1"
8225 # low="1"
8226 # first="1"
8227 # last="1"
8228 # negative="1"
8229 # displayXAxis="1"
8230 # displayHidden="1"
8231 # minAxisType="custom"
8232 # maxAxisType="custom"
8233 # rightToLeft="1">
8234 #
8235 empty = options.get("empty")
8236 attributes = []
8238 if options.get("max") is not None:
8239 if options["max"] == "group":
8240 options["cust_max"] = "group"
8241 else:
8242 attributes.append(("manualMax", options["max"]))
8243 options["cust_max"] = "custom"
8245 if options.get("min") is not None:
8246 if options["min"] == "group":
8247 options["cust_min"] = "group"
8248 else:
8249 attributes.append(("manualMin", options["min"]))
8250 options["cust_min"] = "custom"
8252 # Ignore the default type attribute (line).
8253 if options["type"] != "line":
8254 attributes.append(("type", options["type"]))
8256 if options.get("weight"):
8257 attributes.append(("lineWeight", options["weight"]))
8259 if options.get("date_axis"):
8260 attributes.append(("dateAxis", 1))
8262 if empty:
8263 attributes.append(("displayEmptyCellsAs", empty))
8265 if options.get("markers"):
8266 attributes.append(("markers", 1))
8268 if options.get("high"):
8269 attributes.append(("high", 1))
8271 if options.get("low"):
8272 attributes.append(("low", 1))
8274 if options.get("first"):
8275 attributes.append(("first", 1))
8277 if options.get("last"):
8278 attributes.append(("last", 1))
8280 if options.get("negative"):
8281 attributes.append(("negative", 1))
8283 if options.get("axis"):
8284 attributes.append(("displayXAxis", 1))
8286 if options.get("hidden"):
8287 attributes.append(("displayHidden", 1))
8289 if options.get("cust_min"):
8290 attributes.append(("minAxisType", options["cust_min"]))
8292 if options.get("cust_max"):
8293 attributes.append(("maxAxisType", options["cust_max"]))
8295 if options.get("reverse"):
8296 attributes.append(("rightToLeft", 1))
8298 self._xml_start_tag("x14:sparklineGroup", attributes)
8300 def _write_spark_color(self, tag, color) -> None:
8301 # Helper function for the sparkline color functions below.
8302 if color:
8303 self._write_color(tag, color._attributes())
8305 def _write_color_series(self, color) -> None:
8306 # Write the <x14:colorSeries> element.
8307 self._write_spark_color("x14:colorSeries", color)
8309 def _write_color_negative(self, color) -> None:
8310 # Write the <x14:colorNegative> element.
8311 self._write_spark_color("x14:colorNegative", color)
8313 def _write_color_axis(self) -> None:
8314 # Write the <x14:colorAxis> element.
8315 self._write_spark_color("x14:colorAxis", Color("#000000"))
8317 def _write_color_markers(self, color) -> None:
8318 # Write the <x14:colorMarkers> element.
8319 self._write_spark_color("x14:colorMarkers", color)
8321 def _write_color_first(self, color) -> None:
8322 # Write the <x14:colorFirst> element.
8323 self._write_spark_color("x14:colorFirst", color)
8325 def _write_color_last(self, color) -> None:
8326 # Write the <x14:colorLast> element.
8327 self._write_spark_color("x14:colorLast", color)
8329 def _write_color_high(self, color) -> None:
8330 # Write the <x14:colorHigh> element.
8331 self._write_spark_color("x14:colorHigh", color)
8333 def _write_color_low(self, color) -> None:
8334 # Write the <x14:colorLow> element.
8335 self._write_spark_color("x14:colorLow", color)
8337 def _write_phonetic_pr(self) -> None:
8338 # Write the <phoneticPr> element.
8339 attributes = [
8340 ("fontId", "0"),
8341 ("type", "noConversion"),
8342 ]
8344 self._xml_empty_tag("phoneticPr", attributes)
8346 def _write_ignored_errors(self) -> None:
8347 # Write the <ignoredErrors> element.
8348 if not self.ignored_errors:
8349 return
8351 self._xml_start_tag("ignoredErrors")
8353 if self.ignored_errors.get("number_stored_as_text"):
8354 ignored_range = self.ignored_errors["number_stored_as_text"]
8355 self._write_ignored_error("numberStoredAsText", ignored_range)
8357 if self.ignored_errors.get("eval_error"):
8358 ignored_range = self.ignored_errors["eval_error"]
8359 self._write_ignored_error("evalError", ignored_range)
8361 if self.ignored_errors.get("formula_differs"):
8362 ignored_range = self.ignored_errors["formula_differs"]
8363 self._write_ignored_error("formula", ignored_range)
8365 if self.ignored_errors.get("formula_range"):
8366 ignored_range = self.ignored_errors["formula_range"]
8367 self._write_ignored_error("formulaRange", ignored_range)
8369 if self.ignored_errors.get("formula_unlocked"):
8370 ignored_range = self.ignored_errors["formula_unlocked"]
8371 self._write_ignored_error("unlockedFormula", ignored_range)
8373 if self.ignored_errors.get("empty_cell_reference"):
8374 ignored_range = self.ignored_errors["empty_cell_reference"]
8375 self._write_ignored_error("emptyCellReference", ignored_range)
8377 if self.ignored_errors.get("list_data_validation"):
8378 ignored_range = self.ignored_errors["list_data_validation"]
8379 self._write_ignored_error("listDataValidation", ignored_range)
8381 if self.ignored_errors.get("calculated_column"):
8382 ignored_range = self.ignored_errors["calculated_column"]
8383 self._write_ignored_error("calculatedColumn", ignored_range)
8385 if self.ignored_errors.get("two_digit_text_year"):
8386 ignored_range = self.ignored_errors["two_digit_text_year"]
8387 self._write_ignored_error("twoDigitTextYear", ignored_range)
8389 self._xml_end_tag("ignoredErrors")
8391 def _write_ignored_error(self, error_type, ignored_range) -> None:
8392 # Write the <ignoredError> element.
8393 attributes = [
8394 ("sqref", ignored_range),
8395 (error_type, 1),
8396 ]
8398 self._xml_empty_tag("ignoredError", attributes)