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 StringIO
23from math import isinf, isnan
24from warnings import warn
26from xlsxwriter.color import Color
27from xlsxwriter.comments import CommentType
28from xlsxwriter.image import Image
29from xlsxwriter.url import Url, UrlTypes
30from xlsxwriter.vml import ButtonType
32# Package imports.
33from . import xmlwriter
34from .drawing import Drawing, DrawingInfo, DrawingTypes
35from .exceptions import DuplicateTableName, OverlappingRange
36from .format import Format
37from .shape import Shape
38from .utility import (
39 _datetime_to_excel_datetime,
40 _get_sparkline_style,
41 _preserve_whitespace,
42 _supported_datetime,
43 quote_sheetname,
44 xl_cell_to_rowcol,
45 xl_col_to_name,
46 xl_pixel_width,
47 xl_range,
48 xl_rowcol_to_cell,
49 xl_rowcol_to_cell_fast,
50)
51from .xmlwriter import XMLwriter
53re_dynamic_function = re.compile(
54 r"""
55 \bANCHORARRAY\( |
56 \bBYCOL\( |
57 \bBYROW\( |
58 \bCHOOSECOLS\( |
59 \bCHOOSEROWS\( |
60 \bDROP\( |
61 \bEXPAND\( |
62 \bFILTER\( |
63 \bHSTACK\( |
64 \bLAMBDA\( |
65 \bMAKEARRAY\( |
66 \bMAP\( |
67 \bRANDARRAY\( |
68 \bREDUCE\( |
69 \bSCAN\( |
70 \bSEQUENCE\( |
71 \bSINGLE\( |
72 \bSORT\( |
73 \bSORTBY\( |
74 \bSWITCH\( |
75 \bTAKE\( |
76 \bTEXTSPLIT\( |
77 \bTOCOL\( |
78 \bTOROW\( |
79 \bUNIQUE\( |
80 \bVSTACK\( |
81 \bWRAPCOLS\( |
82 \bWRAPROWS\( |
83 \bXLOOKUP\(""",
84 re.VERBOSE,
85)
88###############################################################################
89#
90# Decorator functions.
91#
92###############################################################################
93def convert_cell_args(method):
94 """
95 Decorator function to convert A1 notation in cell method calls
96 to the default row/col notation.
98 """
100 @wraps(method)
101 def cell_wrapper(self, *args, **kwargs):
102 try:
103 # First arg is an int, default to row/col notation.
104 if args:
105 first_arg = args[0]
106 int(first_arg)
107 except ValueError:
108 # First arg isn't an int, convert to A1 notation.
109 new_args = xl_cell_to_rowcol(first_arg)
110 args = new_args + args[1:]
112 return method(self, *args, **kwargs)
114 return cell_wrapper
117def convert_range_args(method):
118 """
119 Decorator function to convert A1 notation in range method calls
120 to the default row/col notation.
122 """
124 @wraps(method)
125 def cell_wrapper(self, *args, **kwargs):
126 try:
127 # First arg is an int, default to row/col notation.
128 if args:
129 int(args[0])
130 except ValueError:
131 # First arg isn't an int, convert to A1 notation.
132 if ":" in args[0]:
133 cell_1, cell_2 = args[0].split(":")
134 row_1, col_1 = xl_cell_to_rowcol(cell_1)
135 row_2, col_2 = xl_cell_to_rowcol(cell_2)
136 else:
137 row_1, col_1 = xl_cell_to_rowcol(args[0])
138 row_2, col_2 = row_1, col_1
140 new_args = [row_1, col_1, row_2, col_2]
141 new_args.extend(args[1:])
142 args = new_args
144 return method(self, *args, **kwargs)
146 return cell_wrapper
149def convert_column_args(method):
150 """
151 Decorator function to convert A1 notation in columns method calls
152 to the default row/col notation.
154 """
156 @wraps(method)
157 def column_wrapper(self, *args, **kwargs):
158 try:
159 # First arg is an int, default to row/col notation.
160 if args:
161 int(args[0])
162 except ValueError:
163 # First arg isn't an int, convert to A1 notation.
164 cell_1, cell_2 = [col + "1" for col in args[0].split(":")]
165 _, col_1 = xl_cell_to_rowcol(cell_1)
166 _, col_2 = xl_cell_to_rowcol(cell_2)
167 new_args = [col_1, col_2]
168 new_args.extend(args[1:])
169 args = new_args
171 return method(self, *args, **kwargs)
173 return column_wrapper
176###############################################################################
177#
178# Named tuples used for cell types.
179#
180###############################################################################
181CellBlankTuple = namedtuple("Blank", "format")
182CellErrorTuple = namedtuple("Error", "error, format, value")
183CellNumberTuple = namedtuple("Number", "number, format")
184CellStringTuple = namedtuple("String", "string, format")
185CellBooleanTuple = namedtuple("Boolean", "boolean, format")
186CellFormulaTuple = namedtuple("Formula", "formula, format, value")
187CellDatetimeTuple = namedtuple("Datetime", "number, format")
188CellRichStringTuple = namedtuple("RichString", "string, format, raw_string")
189CellArrayFormulaTuple = namedtuple(
190 "ArrayFormula", "formula, format, value, range, atype"
191)
194###############################################################################
195#
196# Worksheet Class definition.
197#
198###############################################################################
199class Worksheet(xmlwriter.XMLwriter):
200 """
201 A class for writing the Excel XLSX Worksheet file.
203 """
205 ###########################################################################
206 #
207 # Public API.
208 #
209 ###########################################################################
211 def __init__(self):
212 """
213 Constructor.
215 """
217 super().__init__()
219 self.name = None
220 self.index = None
221 self.str_table = None
222 self.palette = None
223 self.constant_memory = 0
224 self.tmpdir = None
225 self.is_chartsheet = False
227 self.ext_sheets = []
228 self.fileclosed = 0
229 self.excel_version = 2007
230 self.excel2003_style = False
232 self.xls_rowmax = 1048576
233 self.xls_colmax = 16384
234 self.xls_strmax = 32767
235 self.dim_rowmin = None
236 self.dim_rowmax = None
237 self.dim_colmin = None
238 self.dim_colmax = None
240 self.col_info = {}
241 self.selections = []
242 self.hidden = 0
243 self.active = 0
244 self.tab_color = 0
245 self.top_left_cell = ""
247 self.panes = []
248 self.active_pane = 3
249 self.selected = 0
251 self.page_setup_changed = False
252 self.paper_size = 0
253 self.orientation = 1
255 self.print_options_changed = False
256 self.hcenter = False
257 self.vcenter = False
258 self.print_gridlines = False
259 self.screen_gridlines = True
260 self.print_headers = False
261 self.row_col_headers = False
263 self.header_footer_changed = False
264 self.header = ""
265 self.footer = ""
266 self.header_footer_aligns = True
267 self.header_footer_scales = True
268 self.header_images = []
269 self.footer_images = []
270 self.header_images_list = []
272 self.margin_left = 0.7
273 self.margin_right = 0.7
274 self.margin_top = 0.75
275 self.margin_bottom = 0.75
276 self.margin_header = 0.3
277 self.margin_footer = 0.3
279 self.repeat_row_range = ""
280 self.repeat_col_range = ""
281 self.print_area_range = ""
283 self.page_order = 0
284 self.black_white = 0
285 self.draft_quality = 0
286 self.print_comments = 0
287 self.page_start = 0
289 self.fit_page = 0
290 self.fit_width = 0
291 self.fit_height = 0
293 self.hbreaks = []
294 self.vbreaks = []
296 self.protect_options = {}
297 self.protected_ranges = []
298 self.num_protected_ranges = 0
299 self.set_cols = {}
300 self.set_rows = defaultdict(dict)
302 self.zoom = 100
303 self.zoom_scale_normal = 1
304 self.print_scale = 100
305 self.is_right_to_left = False
306 self.show_zeros = 1
307 self.leading_zeros = 0
309 self.outline_row_level = 0
310 self.outline_col_level = 0
311 self.outline_style = 0
312 self.outline_below = 1
313 self.outline_right = 1
314 self.outline_on = 1
315 self.outline_changed = False
317 self.original_row_height = 15
318 self.default_row_height = 15
319 self.default_row_pixels = 20
320 self.default_col_width = 8.43
321 self.default_col_pixels = 64
322 self.default_date_pixels = 68
323 self.default_row_zeroed = 0
325 self.names = {}
326 self.write_match = []
327 self.table = defaultdict(dict)
328 self.merge = []
329 self.merged_cells = {}
330 self.table_cells = {}
331 self.row_spans = {}
333 self.has_vml = False
334 self.has_header_vml = False
335 self.has_comments = False
336 self.comments = defaultdict(dict)
337 self.comments_list = []
338 self.comments_author = ""
339 self.comments_visible = False
340 self.vml_shape_id = 1024
341 self.buttons_list = []
342 self.vml_header_id = 0
344 self.autofilter_area = ""
345 self.autofilter_ref = None
346 self.filter_range = [0, 9]
347 self.filter_on = 0
348 self.filter_cols = {}
349 self.filter_type = {}
350 self.filter_cells = {}
352 self.row_sizes = {}
353 self.col_size_changed = False
354 self.row_size_changed = False
356 self.last_shape_id = 1
357 self.rel_count = 0
358 self.hlink_count = 0
359 self.hlink_refs = []
360 self.external_hyper_links = []
361 self.external_drawing_links = []
362 self.external_comment_links = []
363 self.external_vml_links = []
364 self.external_table_links = []
365 self.external_background_links = []
366 self.drawing_links = []
367 self.vml_drawing_links = []
368 self.charts = []
369 self.images = []
370 self.tables = []
371 self.sparklines = []
372 self.shapes = []
373 self.shape_hash = {}
374 self.drawing = 0
375 self.drawing_rels = {}
376 self.drawing_rels_id = 0
377 self.vml_drawing_rels = {}
378 self.vml_drawing_rels_id = 0
379 self.background_image = None
381 self.rstring = ""
382 self.previous_row = 0
384 self.validations = []
385 self.cond_formats = {}
386 self.data_bars_2010 = []
387 self.use_data_bars_2010 = False
388 self.dxf_priority = 1
389 self.page_view = 0
391 self.vba_codename = None
393 self.date_1904 = False
394 self.hyperlinks = defaultdict(dict)
396 self.strings_to_numbers = False
397 self.strings_to_urls = True
398 self.nan_inf_to_errors = False
399 self.strings_to_formulas = True
401 self.default_date_format = None
402 self.default_url_format = None
403 self.default_checkbox_format = None
404 self.workbook_add_format = None
405 self.remove_timezone = False
406 self.max_url_length = 2079
408 self.row_data_filename = None
409 self.row_data_fh = None
410 self.worksheet_meta = None
411 self.vml_data_id = None
412 self.vml_shape_id = None
414 self.row_data_filename = None
415 self.row_data_fh = None
416 self.row_data_fh_closed = False
418 self.vertical_dpi = 0
419 self.horizontal_dpi = 0
421 self.write_handlers = {}
423 self.ignored_errors = None
425 self.has_dynamic_arrays = False
426 self.use_future_functions = False
427 self.ignore_write_string = False
428 self.embedded_images = None
430 # Utility function for writing different types of strings.
431 def _write_token_as_string(self, token, row, col, *args):
432 # Map the data to the appropriate write_*() method.
433 if token == "":
434 return self._write_blank(row, col, *args)
436 if self.strings_to_formulas and token.startswith("="):
437 return self._write_formula(row, col, *args)
439 if token.startswith("{=") and token.endswith("}"):
440 return self._write_formula(row, col, *args)
442 # pylint: disable=too-many-boolean-expressions
443 if (
444 ":" in token
445 and self.strings_to_urls
446 and (
447 re.match("(ftp|http)s?://", token)
448 or re.match("mailto:", token)
449 or re.match("(in|ex)ternal:", token)
450 or re.match("file://", token)
451 )
452 ):
453 return self._write_url(row, col, *args)
455 if self.strings_to_numbers:
456 try:
457 f = float(token)
458 if self.nan_inf_to_errors or (not isnan(f) and not isinf(f)):
459 return self._write_number(row, col, f, *args[1:])
460 except ValueError:
461 # Not a number, write as a string.
462 pass
464 return self._write_string(row, col, *args)
466 # We have a plain string.
467 return self._write_string(row, col, *args)
469 @convert_cell_args
470 def write(self, row, col, *args):
471 """
472 Write data to a worksheet cell by calling the appropriate write_*()
473 method based on the type of data being passed.
475 Args:
476 row: The cell row (zero indexed).
477 col: The cell column (zero indexed).
478 *args: Args to pass to sub functions.
480 Returns:
481 0: Success.
482 -1: Row or column is out of worksheet bounds.
483 other: Return value of called method.
485 """
486 return self._write(row, col, *args)
488 # Undecorated version of write().
489 def _write(self, row, col, *args):
490 # pylint: disable=raise-missing-from
491 # Check the number of args passed.
492 if not args:
493 raise TypeError("write() takes at least 4 arguments (3 given)")
495 # The first arg should be the token for all write calls.
496 token = args[0]
498 # Avoid isinstance() for better performance.
499 token_type = token.__class__
501 # Check for any user defined type handlers with callback functions.
502 if token_type in self.write_handlers:
503 write_handler = self.write_handlers[token_type]
504 function_return = write_handler(self, row, col, *args)
506 # If the return value is None then the callback has returned
507 # control to this function and we should continue as
508 # normal. Otherwise we return the value to the caller and exit.
509 if function_return is None:
510 pass
511 else:
512 return function_return
514 # Write None as a blank cell.
515 if token is None:
516 return self._write_blank(row, col, *args)
518 # Check for standard Python types.
519 if token_type is bool:
520 return self._write_boolean(row, col, *args)
522 if token_type in (float, int, Decimal, Fraction):
523 return self._write_number(row, col, *args)
525 if token_type is str:
526 return self._write_token_as_string(token, row, col, *args)
528 if token_type in (
529 datetime.datetime,
530 datetime.date,
531 datetime.time,
532 datetime.timedelta,
533 ):
534 return self._write_datetime(row, col, *args)
536 # Resort to isinstance() for subclassed primitives.
538 # Write number types.
539 if isinstance(token, (float, int, Decimal, Fraction)):
540 return self._write_number(row, col, *args)
542 # Write string types.
543 if isinstance(token, str):
544 return self._write_token_as_string(token, row, col, *args)
546 # Write boolean types.
547 if isinstance(token, bool):
548 return self._write_boolean(row, col, *args)
550 # Write datetime objects.
551 if _supported_datetime(token):
552 return self._write_datetime(row, col, *args)
554 # Write Url type.
555 if isinstance(token, Url):
556 return self._write_url(row, col, *args)
558 # We haven't matched a supported type. Try float.
559 try:
560 f = float(token)
561 return self._write_number(row, col, f, *args[1:])
562 except ValueError:
563 pass
564 except TypeError:
565 raise TypeError(f"Unsupported type {type(token)} in write()")
567 # Finally try string.
568 try:
569 str(token)
570 return self._write_string(row, col, *args)
571 except ValueError:
572 raise TypeError(f"Unsupported type {type(token)} in write()")
574 @convert_cell_args
575 def write_string(self, row, col, string, cell_format=None):
576 """
577 Write a string to a worksheet cell.
579 Args:
580 row: The cell row (zero indexed).
581 col: The cell column (zero indexed).
582 string: Cell data. Str.
583 format: An optional cell Format object.
585 Returns:
586 0: Success.
587 -1: Row or column is out of worksheet bounds.
588 -2: String truncated to 32k characters.
590 """
591 return self._write_string(row, col, string, cell_format)
593 # Undecorated version of write_string().
594 def _write_string(self, row, col, string, cell_format=None):
595 str_error = 0
597 # Check that row and col are valid and store max and min values.
598 if self._check_dimensions(row, col):
599 return -1
601 # Check that the string is < 32767 chars.
602 if len(string) > self.xls_strmax:
603 string = string[: self.xls_strmax]
604 str_error = -2
606 # Write a shared string or an in-line string in constant_memory mode.
607 if not self.constant_memory:
608 string_index = self.str_table._get_shared_string_index(string)
609 else:
610 string_index = string
612 # Write previous row if in in-line string constant_memory mode.
613 if self.constant_memory and row > self.previous_row:
614 self._write_single_row(row)
616 # Store the cell data in the worksheet data table.
617 self.table[row][col] = CellStringTuple(string_index, cell_format)
619 return str_error
621 @convert_cell_args
622 def write_number(self, row, col, number, cell_format=None):
623 """
624 Write a number to a worksheet cell.
626 Args:
627 row: The cell row (zero indexed).
628 col: The cell column (zero indexed).
629 number: Cell data. Int or float.
630 cell_format: An optional cell Format object.
632 Returns:
633 0: Success.
634 -1: Row or column is out of worksheet bounds.
636 """
637 return self._write_number(row, col, number, cell_format)
639 # Undecorated version of write_number().
640 def _write_number(self, row, col, number, cell_format=None):
641 if isnan(number) or isinf(number):
642 if self.nan_inf_to_errors:
643 if isnan(number):
644 return self._write_formula(row, col, "#NUM!", cell_format, "#NUM!")
646 if number == math.inf:
647 return self._write_formula(row, col, "1/0", cell_format, "#DIV/0!")
649 if number == -math.inf:
650 return self._write_formula(row, col, "-1/0", cell_format, "#DIV/0!")
651 else:
652 raise TypeError(
653 "NAN/INF not supported in write_number() "
654 "without 'nan_inf_to_errors' Workbook() option"
655 )
657 if number.__class__ is Fraction:
658 number = float(number)
660 # Check that row and col are valid and store max and min values.
661 if self._check_dimensions(row, col):
662 return -1
664 # Write previous row if in in-line string constant_memory mode.
665 if self.constant_memory and row > self.previous_row:
666 self._write_single_row(row)
668 # Store the cell data in the worksheet data table.
669 self.table[row][col] = CellNumberTuple(number, cell_format)
671 return 0
673 @convert_cell_args
674 def write_blank(self, row, col, blank, cell_format=None):
675 """
676 Write a blank cell with formatting to a worksheet cell. The blank
677 token is ignored and the format only is written to the cell.
679 Args:
680 row: The cell row (zero indexed).
681 col: The cell column (zero indexed).
682 blank: Any value. It is ignored.
683 cell_format: An optional cell Format object.
685 Returns:
686 0: Success.
687 -1: Row or column is out of worksheet bounds.
689 """
690 return self._write_blank(row, col, blank, cell_format)
692 # Undecorated version of write_blank().
693 def _write_blank(self, row, col, _, cell_format=None):
694 # Don't write a blank cell unless it has a format.
695 if cell_format is None:
696 return 0
698 # Check that row and col are valid and store max and min values.
699 if self._check_dimensions(row, col):
700 return -1
702 # Write previous row if in in-line string constant_memory mode.
703 if self.constant_memory and row > self.previous_row:
704 self._write_single_row(row)
706 # Store the cell data in the worksheet data table.
707 self.table[row][col] = CellBlankTuple(cell_format)
709 return 0
711 @convert_cell_args
712 def write_formula(self, row, col, formula, cell_format=None, value=0):
713 """
714 Write a formula to a worksheet cell.
716 Args:
717 row: The cell row (zero indexed).
718 col: The cell column (zero indexed).
719 formula: Cell formula.
720 cell_format: An optional cell Format object.
721 value: An optional value for the formula. Default is 0.
723 Returns:
724 0: Success.
725 -1: Row or column is out of worksheet bounds.
726 -2: Formula can't be None or empty.
728 """
729 # Check that row and col are valid and store max and min values.
730 return self._write_formula(row, col, formula, cell_format, value)
732 # Undecorated version of write_formula().
733 def _write_formula(self, row, col, formula, cell_format=None, value=0):
734 if self._check_dimensions(row, col):
735 return -1
737 if formula is None or formula == "":
738 warn("Formula can't be None or empty")
739 return -1
741 # Check for dynamic array functions.
742 if re_dynamic_function.search(formula):
743 return self.write_dynamic_array_formula(
744 row, col, row, col, formula, cell_format, value
745 )
747 # Hand off array formulas.
748 if formula.startswith("{") and formula.endswith("}"):
749 return self._write_array_formula(
750 row, col, row, col, formula, cell_format, value
751 )
753 # Modify the formula string, as needed.
754 formula = self._prepare_formula(formula)
756 # Write previous row if in in-line string constant_memory mode.
757 if self.constant_memory and row > self.previous_row:
758 self._write_single_row(row)
760 # Store the cell data in the worksheet data table.
761 self.table[row][col] = CellFormulaTuple(formula, cell_format, value)
763 return 0
765 @convert_range_args
766 def write_array_formula(
767 self,
768 first_row,
769 first_col,
770 last_row,
771 last_col,
772 formula,
773 cell_format=None,
774 value=0,
775 ):
776 """
777 Write a formula to a worksheet cell/range.
779 Args:
780 first_row: The first row of the cell range. (zero indexed).
781 first_col: The first column of the cell range.
782 last_row: The last row of the cell range. (zero indexed).
783 last_col: The last column of the cell range.
784 formula: Cell formula.
785 cell_format: An optional cell Format object.
786 value: An optional value for the formula. Default is 0.
788 Returns:
789 0: Success.
790 -1: Row or column is out of worksheet bounds.
792 """
793 # Check for dynamic array functions.
794 if re_dynamic_function.search(formula):
795 return self.write_dynamic_array_formula(
796 first_row, first_col, last_row, last_col, formula, cell_format, value
797 )
799 return self._write_array_formula(
800 first_row,
801 first_col,
802 last_row,
803 last_col,
804 formula,
805 cell_format,
806 value,
807 "static",
808 )
810 @convert_range_args
811 def write_dynamic_array_formula(
812 self,
813 first_row,
814 first_col,
815 last_row,
816 last_col,
817 formula,
818 cell_format=None,
819 value=0,
820 ):
821 """
822 Write a dynamic array formula to a worksheet cell/range.
824 Args:
825 first_row: The first row of the cell range. (zero indexed).
826 first_col: The first column of the cell range.
827 last_row: The last row of the cell range. (zero indexed).
828 last_col: The last column of the cell range.
829 formula: Cell formula.
830 cell_format: An optional cell Format object.
831 value: An optional value for the formula. Default is 0.
833 Returns:
834 0: Success.
835 -1: Row or column is out of worksheet bounds.
837 """
838 error = self._write_array_formula(
839 first_row,
840 first_col,
841 last_row,
842 last_col,
843 formula,
844 cell_format,
845 value,
846 "dynamic",
847 )
849 if error == 0:
850 self.has_dynamic_arrays = True
852 return error
854 # Utility method to strip equal sign and array braces from a formula and
855 # also expand out future and dynamic array formulas.
856 def _prepare_formula(self, formula, expand_future_functions=False):
857 # Remove array formula braces and the leading =.
858 if formula.startswith("{"):
859 formula = formula[1:]
860 if formula.startswith("="):
861 formula = formula[1:]
862 if formula.endswith("}"):
863 formula = formula[:-1]
865 # Check if formula is already expanded by the user.
866 if "_xlfn." in formula:
867 return formula
869 # Expand dynamic formulas.
870 formula = re.sub(r"\bANCHORARRAY\(", "_xlfn.ANCHORARRAY(", formula)
871 formula = re.sub(r"\bBYCOL\(", "_xlfn.BYCOL(", formula)
872 formula = re.sub(r"\bBYROW\(", "_xlfn.BYROW(", formula)
873 formula = re.sub(r"\bCHOOSECOLS\(", "_xlfn.CHOOSECOLS(", formula)
874 formula = re.sub(r"\bCHOOSEROWS\(", "_xlfn.CHOOSEROWS(", formula)
875 formula = re.sub(r"\bDROP\(", "_xlfn.DROP(", formula)
876 formula = re.sub(r"\bEXPAND\(", "_xlfn.EXPAND(", formula)
877 formula = re.sub(r"\bFILTER\(", "_xlfn._xlws.FILTER(", formula)
878 formula = re.sub(r"\bHSTACK\(", "_xlfn.HSTACK(", formula)
879 formula = re.sub(r"\bLAMBDA\(", "_xlfn.LAMBDA(", formula)
880 formula = re.sub(r"\bMAKEARRAY\(", "_xlfn.MAKEARRAY(", formula)
881 formula = re.sub(r"\bMAP\(", "_xlfn.MAP(", formula)
882 formula = re.sub(r"\bRANDARRAY\(", "_xlfn.RANDARRAY(", formula)
883 formula = re.sub(r"\bREDUCE\(", "_xlfn.REDUCE(", formula)
884 formula = re.sub(r"\bSCAN\(", "_xlfn.SCAN(", formula)
885 formula = re.sub(r"\SINGLE\(", "_xlfn.SINGLE(", formula)
886 formula = re.sub(r"\bSEQUENCE\(", "_xlfn.SEQUENCE(", formula)
887 formula = re.sub(r"\bSORT\(", "_xlfn._xlws.SORT(", formula)
888 formula = re.sub(r"\bSORTBY\(", "_xlfn.SORTBY(", formula)
889 formula = re.sub(r"\bSWITCH\(", "_xlfn.SWITCH(", formula)
890 formula = re.sub(r"\bTAKE\(", "_xlfn.TAKE(", formula)
891 formula = re.sub(r"\bTEXTSPLIT\(", "_xlfn.TEXTSPLIT(", formula)
892 formula = re.sub(r"\bTOCOL\(", "_xlfn.TOCOL(", formula)
893 formula = re.sub(r"\bTOROW\(", "_xlfn.TOROW(", formula)
894 formula = re.sub(r"\bUNIQUE\(", "_xlfn.UNIQUE(", formula)
895 formula = re.sub(r"\bVSTACK\(", "_xlfn.VSTACK(", formula)
896 formula = re.sub(r"\bWRAPCOLS\(", "_xlfn.WRAPCOLS(", formula)
897 formula = re.sub(r"\bWRAPROWS\(", "_xlfn.WRAPROWS(", formula)
898 formula = re.sub(r"\bXLOOKUP\(", "_xlfn.XLOOKUP(", formula)
900 if not self.use_future_functions and not expand_future_functions:
901 return formula
903 formula = re.sub(r"\bACOTH\(", "_xlfn.ACOTH(", formula)
904 formula = re.sub(r"\bACOT\(", "_xlfn.ACOT(", formula)
905 formula = re.sub(r"\bAGGREGATE\(", "_xlfn.AGGREGATE(", formula)
906 formula = re.sub(r"\bARABIC\(", "_xlfn.ARABIC(", formula)
907 formula = re.sub(r"\bARRAYTOTEXT\(", "_xlfn.ARRAYTOTEXT(", formula)
908 formula = re.sub(r"\bBASE\(", "_xlfn.BASE(", formula)
909 formula = re.sub(r"\bBETA.DIST\(", "_xlfn.BETA.DIST(", formula)
910 formula = re.sub(r"\bBETA.INV\(", "_xlfn.BETA.INV(", formula)
911 formula = re.sub(r"\bBINOM.DIST.RANGE\(", "_xlfn.BINOM.DIST.RANGE(", formula)
912 formula = re.sub(r"\bBINOM.DIST\(", "_xlfn.BINOM.DIST(", formula)
913 formula = re.sub(r"\bBINOM.INV\(", "_xlfn.BINOM.INV(", formula)
914 formula = re.sub(r"\bBITAND\(", "_xlfn.BITAND(", formula)
915 formula = re.sub(r"\bBITLSHIFT\(", "_xlfn.BITLSHIFT(", formula)
916 formula = re.sub(r"\bBITOR\(", "_xlfn.BITOR(", formula)
917 formula = re.sub(r"\bBITRSHIFT\(", "_xlfn.BITRSHIFT(", formula)
918 formula = re.sub(r"\bBITXOR\(", "_xlfn.BITXOR(", formula)
919 formula = re.sub(r"\bCEILING.MATH\(", "_xlfn.CEILING.MATH(", formula)
920 formula = re.sub(r"\bCEILING.PRECISE\(", "_xlfn.CEILING.PRECISE(", formula)
921 formula = re.sub(r"\bCHISQ.DIST.RT\(", "_xlfn.CHISQ.DIST.RT(", formula)
922 formula = re.sub(r"\bCHISQ.DIST\(", "_xlfn.CHISQ.DIST(", formula)
923 formula = re.sub(r"\bCHISQ.INV.RT\(", "_xlfn.CHISQ.INV.RT(", formula)
924 formula = re.sub(r"\bCHISQ.INV\(", "_xlfn.CHISQ.INV(", formula)
925 formula = re.sub(r"\bCHISQ.TEST\(", "_xlfn.CHISQ.TEST(", formula)
926 formula = re.sub(r"\bCOMBINA\(", "_xlfn.COMBINA(", formula)
927 formula = re.sub(r"\bCONCAT\(", "_xlfn.CONCAT(", formula)
928 formula = re.sub(r"\bCONFIDENCE.NORM\(", "_xlfn.CONFIDENCE.NORM(", formula)
929 formula = re.sub(r"\bCONFIDENCE.T\(", "_xlfn.CONFIDENCE.T(", formula)
930 formula = re.sub(r"\bCOTH\(", "_xlfn.COTH(", formula)
931 formula = re.sub(r"\bCOT\(", "_xlfn.COT(", formula)
932 formula = re.sub(r"\bCOVARIANCE.P\(", "_xlfn.COVARIANCE.P(", formula)
933 formula = re.sub(r"\bCOVARIANCE.S\(", "_xlfn.COVARIANCE.S(", formula)
934 formula = re.sub(r"\bCSCH\(", "_xlfn.CSCH(", formula)
935 formula = re.sub(r"\bCSC\(", "_xlfn.CSC(", formula)
936 formula = re.sub(r"\bDAYS\(", "_xlfn.DAYS(", formula)
937 formula = re.sub(r"\bDECIMAL\(", "_xlfn.DECIMAL(", formula)
938 formula = re.sub(r"\bERF.PRECISE\(", "_xlfn.ERF.PRECISE(", formula)
939 formula = re.sub(r"\bERFC.PRECISE\(", "_xlfn.ERFC.PRECISE(", formula)
940 formula = re.sub(r"\bEXPON.DIST\(", "_xlfn.EXPON.DIST(", formula)
941 formula = re.sub(r"\bF.DIST.RT\(", "_xlfn.F.DIST.RT(", formula)
942 formula = re.sub(r"\bF.DIST\(", "_xlfn.F.DIST(", formula)
943 formula = re.sub(r"\bF.INV.RT\(", "_xlfn.F.INV.RT(", formula)
944 formula = re.sub(r"\bF.INV\(", "_xlfn.F.INV(", formula)
945 formula = re.sub(r"\bF.TEST\(", "_xlfn.F.TEST(", formula)
946 formula = re.sub(r"\bFILTERXML\(", "_xlfn.FILTERXML(", formula)
947 formula = re.sub(r"\bFLOOR.MATH\(", "_xlfn.FLOOR.MATH(", formula)
948 formula = re.sub(r"\bFLOOR.PRECISE\(", "_xlfn.FLOOR.PRECISE(", formula)
949 formula = re.sub(
950 r"\bFORECAST.ETS.CONFINT\(", "_xlfn.FORECAST.ETS.CONFINT(", formula
951 )
952 formula = re.sub(
953 r"\bFORECAST.ETS.SEASONALITY\(", "_xlfn.FORECAST.ETS.SEASONALITY(", formula
954 )
955 formula = re.sub(r"\bFORECAST.ETS.STAT\(", "_xlfn.FORECAST.ETS.STAT(", formula)
956 formula = re.sub(r"\bFORECAST.ETS\(", "_xlfn.FORECAST.ETS(", formula)
957 formula = re.sub(r"\bFORECAST.LINEAR\(", "_xlfn.FORECAST.LINEAR(", formula)
958 formula = re.sub(r"\bFORMULATEXT\(", "_xlfn.FORMULATEXT(", formula)
959 formula = re.sub(r"\bGAMMA.DIST\(", "_xlfn.GAMMA.DIST(", formula)
960 formula = re.sub(r"\bGAMMA.INV\(", "_xlfn.GAMMA.INV(", formula)
961 formula = re.sub(r"\bGAMMALN.PRECISE\(", "_xlfn.GAMMALN.PRECISE(", formula)
962 formula = re.sub(r"\bGAMMA\(", "_xlfn.GAMMA(", formula)
963 formula = re.sub(r"\bGAUSS\(", "_xlfn.GAUSS(", formula)
964 formula = re.sub(r"\bHYPGEOM.DIST\(", "_xlfn.HYPGEOM.DIST(", formula)
965 formula = re.sub(r"\bIFNA\(", "_xlfn.IFNA(", formula)
966 formula = re.sub(r"\bIFS\(", "_xlfn.IFS(", formula)
967 formula = re.sub(r"\bIMAGE\(", "_xlfn.IMAGE(", formula)
968 formula = re.sub(r"\bIMCOSH\(", "_xlfn.IMCOSH(", formula)
969 formula = re.sub(r"\bIMCOT\(", "_xlfn.IMCOT(", formula)
970 formula = re.sub(r"\bIMCSCH\(", "_xlfn.IMCSCH(", formula)
971 formula = re.sub(r"\bIMCSC\(", "_xlfn.IMCSC(", formula)
972 formula = re.sub(r"\bIMSECH\(", "_xlfn.IMSECH(", formula)
973 formula = re.sub(r"\bIMSEC\(", "_xlfn.IMSEC(", formula)
974 formula = re.sub(r"\bIMSINH\(", "_xlfn.IMSINH(", formula)
975 formula = re.sub(r"\bIMTAN\(", "_xlfn.IMTAN(", formula)
976 formula = re.sub(r"\bISFORMULA\(", "_xlfn.ISFORMULA(", formula)
977 formula = re.sub(r"\bISOMITTED\(", "_xlfn.ISOMITTED(", formula)
978 formula = re.sub(r"\bISOWEEKNUM\(", "_xlfn.ISOWEEKNUM(", formula)
979 formula = re.sub(r"\bLET\(", "_xlfn.LET(", formula)
980 formula = re.sub(r"\bLOGNORM.DIST\(", "_xlfn.LOGNORM.DIST(", formula)
981 formula = re.sub(r"\bLOGNORM.INV\(", "_xlfn.LOGNORM.INV(", formula)
982 formula = re.sub(r"\bMAXIFS\(", "_xlfn.MAXIFS(", formula)
983 formula = re.sub(r"\bMINIFS\(", "_xlfn.MINIFS(", formula)
984 formula = re.sub(r"\bMODE.MULT\(", "_xlfn.MODE.MULT(", formula)
985 formula = re.sub(r"\bMODE.SNGL\(", "_xlfn.MODE.SNGL(", formula)
986 formula = re.sub(r"\bMUNIT\(", "_xlfn.MUNIT(", formula)
987 formula = re.sub(r"\bNEGBINOM.DIST\(", "_xlfn.NEGBINOM.DIST(", formula)
988 formula = re.sub(r"\bNORM.DIST\(", "_xlfn.NORM.DIST(", formula)
989 formula = re.sub(r"\bNORM.INV\(", "_xlfn.NORM.INV(", formula)
990 formula = re.sub(r"\bNORM.S.DIST\(", "_xlfn.NORM.S.DIST(", formula)
991 formula = re.sub(r"\bNORM.S.INV\(", "_xlfn.NORM.S.INV(", formula)
992 formula = re.sub(r"\bNUMBERVALUE\(", "_xlfn.NUMBERVALUE(", formula)
993 formula = re.sub(r"\bPDURATION\(", "_xlfn.PDURATION(", formula)
994 formula = re.sub(r"\bPERCENTILE.EXC\(", "_xlfn.PERCENTILE.EXC(", formula)
995 formula = re.sub(r"\bPERCENTILE.INC\(", "_xlfn.PERCENTILE.INC(", formula)
996 formula = re.sub(r"\bPERCENTRANK.EXC\(", "_xlfn.PERCENTRANK.EXC(", formula)
997 formula = re.sub(r"\bPERCENTRANK.INC\(", "_xlfn.PERCENTRANK.INC(", formula)
998 formula = re.sub(r"\bPERMUTATIONA\(", "_xlfn.PERMUTATIONA(", formula)
999 formula = re.sub(r"\bPHI\(", "_xlfn.PHI(", formula)
1000 formula = re.sub(r"\bPOISSON.DIST\(", "_xlfn.POISSON.DIST(", formula)
1001 formula = re.sub(r"\bQUARTILE.EXC\(", "_xlfn.QUARTILE.EXC(", formula)
1002 formula = re.sub(r"\bQUARTILE.INC\(", "_xlfn.QUARTILE.INC(", formula)
1003 formula = re.sub(r"\bQUERYSTRING\(", "_xlfn.QUERYSTRING(", formula)
1004 formula = re.sub(r"\bRANK.AVG\(", "_xlfn.RANK.AVG(", formula)
1005 formula = re.sub(r"\bRANK.EQ\(", "_xlfn.RANK.EQ(", formula)
1006 formula = re.sub(r"\bRRI\(", "_xlfn.RRI(", formula)
1007 formula = re.sub(r"\bSECH\(", "_xlfn.SECH(", formula)
1008 formula = re.sub(r"\bSEC\(", "_xlfn.SEC(", formula)
1009 formula = re.sub(r"\bSHEETS\(", "_xlfn.SHEETS(", formula)
1010 formula = re.sub(r"\bSHEET\(", "_xlfn.SHEET(", formula)
1011 formula = re.sub(r"\bSKEW.P\(", "_xlfn.SKEW.P(", formula)
1012 formula = re.sub(r"\bSTDEV.P\(", "_xlfn.STDEV.P(", formula)
1013 formula = re.sub(r"\bSTDEV.S\(", "_xlfn.STDEV.S(", formula)
1014 formula = re.sub(r"\bT.DIST.2T\(", "_xlfn.T.DIST.2T(", formula)
1015 formula = re.sub(r"\bT.DIST.RT\(", "_xlfn.T.DIST.RT(", formula)
1016 formula = re.sub(r"\bT.DIST\(", "_xlfn.T.DIST(", formula)
1017 formula = re.sub(r"\bT.INV.2T\(", "_xlfn.T.INV.2T(", formula)
1018 formula = re.sub(r"\bT.INV\(", "_xlfn.T.INV(", formula)
1019 formula = re.sub(r"\bT.TEST\(", "_xlfn.T.TEST(", formula)
1020 formula = re.sub(r"\bTEXTAFTER\(", "_xlfn.TEXTAFTER(", formula)
1021 formula = re.sub(r"\bTEXTBEFORE\(", "_xlfn.TEXTBEFORE(", formula)
1022 formula = re.sub(r"\bTEXTJOIN\(", "_xlfn.TEXTJOIN(", formula)
1023 formula = re.sub(r"\bUNICHAR\(", "_xlfn.UNICHAR(", formula)
1024 formula = re.sub(r"\bUNICODE\(", "_xlfn.UNICODE(", formula)
1025 formula = re.sub(r"\bVALUETOTEXT\(", "_xlfn.VALUETOTEXT(", formula)
1026 formula = re.sub(r"\bVAR.P\(", "_xlfn.VAR.P(", formula)
1027 formula = re.sub(r"\bVAR.S\(", "_xlfn.VAR.S(", formula)
1028 formula = re.sub(r"\bWEBSERVICE\(", "_xlfn.WEBSERVICE(", formula)
1029 formula = re.sub(r"\bWEIBULL.DIST\(", "_xlfn.WEIBULL.DIST(", formula)
1030 formula = re.sub(r"\bXMATCH\(", "_xlfn.XMATCH(", formula)
1031 formula = re.sub(r"\bXOR\(", "_xlfn.XOR(", formula)
1032 formula = re.sub(r"\bZ.TEST\(", "_xlfn.Z.TEST(", formula)
1034 return formula
1036 # Escape/expand table functions. This mainly involves converting Excel 2010
1037 # "@" table ref to 2007 "[#This Row],". We parse the string to avoid
1038 # replacements in string literals within the formula.
1039 @staticmethod
1040 def _prepare_table_formula(formula):
1041 if "@" not in formula:
1042 # No escaping required.
1043 return formula
1045 escaped_formula = []
1046 in_string_literal = False
1048 for char in formula:
1049 # Match the start/end of string literals to avoid escaping
1050 # references in strings.
1051 if char == '"':
1052 in_string_literal = not in_string_literal
1054 # Copy the string literal.
1055 if in_string_literal:
1056 escaped_formula.append(char)
1057 continue
1059 # Replace table reference.
1060 if char == "@":
1061 escaped_formula.append("[#This Row],")
1062 else:
1063 escaped_formula.append(char)
1065 return ("").join(escaped_formula)
1067 # Undecorated version of write_array_formula() and
1068 # write_dynamic_array_formula().
1069 def _write_array_formula(
1070 self,
1071 first_row,
1072 first_col,
1073 last_row,
1074 last_col,
1075 formula,
1076 cell_format=None,
1077 value=0,
1078 atype="static",
1079 ):
1080 # Swap last row/col with first row/col as necessary.
1081 if first_row > last_row:
1082 first_row, last_row = last_row, first_row
1083 if first_col > last_col:
1084 first_col, last_col = last_col, first_col
1086 # Check that row and col are valid and store max and min values.
1087 if self._check_dimensions(first_row, first_col):
1088 return -1
1089 if self._check_dimensions(last_row, last_col):
1090 return -1
1092 # Define array range
1093 if first_row == last_row and first_col == last_col:
1094 cell_range = xl_rowcol_to_cell(first_row, first_col)
1095 else:
1096 cell_range = (
1097 xl_rowcol_to_cell(first_row, first_col)
1098 + ":"
1099 + xl_rowcol_to_cell(last_row, last_col)
1100 )
1102 # Modify the formula string, as needed.
1103 formula = self._prepare_formula(formula)
1105 # Write previous row if in in-line string constant_memory mode.
1106 if self.constant_memory and first_row > self.previous_row:
1107 self._write_single_row(first_row)
1109 # Store the cell data in the worksheet data table.
1110 self.table[first_row][first_col] = CellArrayFormulaTuple(
1111 formula, cell_format, value, cell_range, atype
1112 )
1114 # Pad out the rest of the area with formatted zeroes.
1115 if not self.constant_memory:
1116 for row in range(first_row, last_row + 1):
1117 for col in range(first_col, last_col + 1):
1118 if row != first_row or col != first_col:
1119 self._write_number(row, col, 0, cell_format)
1121 return 0
1123 @convert_cell_args
1124 def write_datetime(self, row, col, date, cell_format=None):
1125 """
1126 Write a date or time to a worksheet cell.
1128 Args:
1129 row: The cell row (zero indexed).
1130 col: The cell column (zero indexed).
1131 date: Date and/or time as a datetime object.
1132 cell_format: A cell Format object.
1134 Returns:
1135 0: Success.
1136 -1: Row or column is out of worksheet bounds.
1138 """
1139 return self._write_datetime(row, col, date, cell_format)
1141 # Undecorated version of write_datetime().
1142 def _write_datetime(self, row, col, date, cell_format=None):
1143 # Check that row and col are valid and store max and min values.
1144 if self._check_dimensions(row, col):
1145 return -1
1147 # Write previous row if in in-line string constant_memory mode.
1148 if self.constant_memory and row > self.previous_row:
1149 self._write_single_row(row)
1151 # Convert datetime to an Excel date.
1152 number = self._convert_date_time(date)
1154 # Add the default date format.
1155 if cell_format is None:
1156 cell_format = self.default_date_format
1158 # Store the cell data in the worksheet data table.
1159 self.table[row][col] = CellDatetimeTuple(number, cell_format)
1161 return 0
1163 @convert_cell_args
1164 def write_boolean(self, row, col, boolean, cell_format=None):
1165 """
1166 Write a boolean value to a worksheet cell.
1168 Args:
1169 row: The cell row (zero indexed).
1170 col: The cell column (zero indexed).
1171 boolean: Cell data. bool type.
1172 cell_format: An optional cell Format object.
1174 Returns:
1175 0: Success.
1176 -1: Row or column is out of worksheet bounds.
1178 """
1179 return self._write_boolean(row, col, boolean, cell_format)
1181 # Undecorated version of write_boolean().
1182 def _write_boolean(self, row, col, boolean, cell_format=None):
1183 # Check that row and col are valid and store max and min values.
1184 if self._check_dimensions(row, col):
1185 return -1
1187 # Write previous row if in in-line string constant_memory mode.
1188 if self.constant_memory and row > self.previous_row:
1189 self._write_single_row(row)
1191 if boolean:
1192 value = 1
1193 else:
1194 value = 0
1196 # Store the cell data in the worksheet data table.
1197 self.table[row][col] = CellBooleanTuple(value, cell_format)
1199 return 0
1201 # Write a hyperlink. This is comprised of two elements: the displayed
1202 # string and the non-displayed link. The displayed string is the same as
1203 # the link unless an alternative string is specified. The display string
1204 # is written using the write_string() method. Therefore the max characters
1205 # string limit applies.
1206 #
1207 # The hyperlink can be to a http, ftp, mail, internal sheet, or external
1208 # directory urls.
1209 @convert_cell_args
1210 def write_url(self, row, col, url, cell_format=None, string=None, tip=None):
1211 """
1212 Write a hyperlink to a worksheet cell.
1214 Args:
1215 row: The cell row (zero indexed).
1216 col: The cell column (zero indexed).
1217 url: Hyperlink url.
1218 format: An optional cell Format object.
1219 string: An optional display string for the hyperlink.
1220 tip: An optional tooltip.
1221 Returns:
1222 0: Success.
1223 -1: Row or column is out of worksheet bounds.
1224 -2: String longer than 32767 characters.
1225 -3: URL longer than Excel limit of 255 characters.
1226 -4: Exceeds Excel limit of 65,530 urls per worksheet.
1227 """
1228 return self._write_url(row, col, url, cell_format, string, tip)
1230 # Undecorated version of write_url().
1231 def _write_url(self, row, col, url, cell_format=None, string=None, tip=None):
1232 # Check that row and col are valid and store max and min values
1233 if self._check_dimensions(row, col):
1234 return -1
1236 # If the URL is a string convert it to a Url object.
1237 if not isinstance(url, Url):
1239 # For backwards compatibility check if the string URL exceeds the
1240 # Excel character limit for URLs and ignore it with a warning.
1241 max_url = self.max_url_length
1242 if "#" in url:
1243 url_str, anchor_str = url.split("#", 1)
1244 else:
1245 url_str = url
1246 anchor_str = ""
1248 if len(url_str) > max_url or len(anchor_str) > max_url:
1249 warn(
1250 f"Ignoring URL '{url}' with link or location/anchor > {max_url} "
1251 f"characters since it exceeds Excel's limit for URLs."
1252 )
1253 return -3
1255 url = Url(url)
1257 if string is not None:
1258 url._text = string
1260 if tip is not None:
1261 url._tip = tip
1263 # Check the limit of URLs per worksheet.
1264 self.hlink_count += 1
1266 if self.hlink_count > 65530:
1267 warn(
1268 f"Ignoring URL '{url._original_url}' since it exceeds Excel's limit of "
1269 f"65,530 URLs per worksheet."
1270 )
1271 return -4
1273 # Add the default URL format.
1274 if cell_format is None:
1275 cell_format = self.default_url_format
1277 if not self.ignore_write_string:
1278 # Write previous row if in in-line string constant_memory mode.
1279 if self.constant_memory and row > self.previous_row:
1280 self._write_single_row(row)
1282 # Write the hyperlink string.
1283 self._write_string(row, col, url.text, cell_format)
1285 # Store the hyperlink data in a separate structure.
1286 self.hyperlinks[row][col] = url
1288 return 0
1290 @convert_cell_args
1291 def write_rich_string(self, row, col, *args):
1292 """
1293 Write a "rich" string with multiple formats to a worksheet cell.
1295 Args:
1296 row: The cell row (zero indexed).
1297 col: The cell column (zero indexed).
1298 string_parts: String and format pairs.
1299 cell_format: Optional Format object.
1301 Returns:
1302 0: Success.
1303 -1: Row or column is out of worksheet bounds.
1304 -2: String truncated to 32k characters.
1305 -3: 2 consecutive formats used.
1306 -4: Empty string used.
1307 -5: Insufficient parameters.
1309 """
1311 return self._write_rich_string(row, col, *args)
1313 # Undecorated version of write_rich_string().
1314 def _write_rich_string(self, row, col, *args):
1315 tokens = list(args)
1316 cell_format = None
1317 string_index = 0
1318 raw_string = ""
1320 # Check that row and col are valid and store max and min values
1321 if self._check_dimensions(row, col):
1322 return -1
1324 # If the last arg is a format we use it as the cell format.
1325 if isinstance(tokens[-1], Format):
1326 cell_format = tokens.pop()
1328 # Create a temp XMLWriter object and use it to write the rich string
1329 # XML to a string.
1330 fh = StringIO()
1331 self.rstring = XMLwriter()
1332 self.rstring._set_filehandle(fh)
1334 # Create a temp format with the default font for unformatted fragments.
1335 default = Format()
1337 # Convert list of format, string tokens to pairs of (format, string)
1338 # except for the first string fragment which doesn't require a default
1339 # formatting run. Use the default for strings without a leading format.
1340 fragments = []
1341 previous = "format"
1342 pos = 0
1344 if len(tokens) <= 2:
1345 warn(
1346 "You must specify more than 2 format/fragments for rich "
1347 "strings. Ignoring input in write_rich_string()."
1348 )
1349 return -5
1351 for token in tokens:
1352 if not isinstance(token, Format):
1353 # Token is a string.
1354 if previous != "format":
1355 # If previous token wasn't a format add one before string.
1356 fragments.append(default)
1357 fragments.append(token)
1358 else:
1359 # If previous token was a format just add the string.
1360 fragments.append(token)
1362 if token == "":
1363 warn(
1364 "Excel doesn't allow empty strings in rich strings. "
1365 "Ignoring input in write_rich_string()."
1366 )
1367 return -4
1369 # Keep track of unformatted string.
1370 raw_string += token
1371 previous = "string"
1372 else:
1373 # Can't allow 2 formats in a row.
1374 if previous == "format" and pos > 0:
1375 warn(
1376 "Excel doesn't allow 2 consecutive formats in rich "
1377 "strings. Ignoring input in write_rich_string()."
1378 )
1379 return -3
1381 # Token is a format object. Add it to the fragment list.
1382 fragments.append(token)
1383 previous = "format"
1385 pos += 1
1387 # If the first token is a string start the <r> element.
1388 if not isinstance(fragments[0], Format):
1389 self.rstring._xml_start_tag("r")
1391 # Write the XML elements for the $format $string fragments.
1392 for token in fragments:
1393 if isinstance(token, Format):
1394 # Write the font run.
1395 self.rstring._xml_start_tag("r")
1396 self._write_font(token)
1397 else:
1398 # Write the string fragment part, with whitespace handling.
1399 attributes = []
1401 if _preserve_whitespace(token):
1402 attributes.append(("xml:space", "preserve"))
1404 self.rstring._xml_data_element("t", token, attributes)
1405 self.rstring._xml_end_tag("r")
1407 # Read the in-memory string.
1408 string = self.rstring.fh.getvalue()
1410 # Check that the string is < 32767 chars.
1411 if len(raw_string) > self.xls_strmax:
1412 warn(
1413 "String length must be less than or equal to Excel's limit "
1414 "of 32,767 characters in write_rich_string()."
1415 )
1416 return -2
1418 # Write a shared string or an in-line string in constant_memory mode.
1419 if not self.constant_memory:
1420 string_index = self.str_table._get_shared_string_index(string)
1421 else:
1422 string_index = string
1424 # Write previous row if in in-line string constant_memory mode.
1425 if self.constant_memory and row > self.previous_row:
1426 self._write_single_row(row)
1428 # Store the cell data in the worksheet data table.
1429 self.table[row][col] = CellRichStringTuple(
1430 string_index, cell_format, raw_string
1431 )
1433 return 0
1435 def add_write_handler(self, user_type, user_function):
1436 """
1437 Add a callback function to the write() method to handle user defined
1438 types.
1440 Args:
1441 user_type: The user type() to match on.
1442 user_function: The user defined function to write the type data.
1443 Returns:
1444 Nothing.
1446 """
1448 self.write_handlers[user_type] = user_function
1450 @convert_cell_args
1451 def write_row(self, row, col, data, cell_format=None):
1452 """
1453 Write a row of data starting from (row, col).
1455 Args:
1456 row: The cell row (zero indexed).
1457 col: The cell column (zero indexed).
1458 data: A list of tokens to be written with write().
1459 format: An optional cell Format object.
1460 Returns:
1461 0: Success.
1462 other: Return value of write() method.
1464 """
1465 for token in data:
1466 error = self._write(row, col, token, cell_format)
1467 if error:
1468 return error
1469 col += 1
1471 return 0
1473 @convert_cell_args
1474 def write_column(self, row, col, data, cell_format=None):
1475 """
1476 Write a column of data starting from (row, col).
1478 Args:
1479 row: The cell row (zero indexed).
1480 col: The cell column (zero indexed).
1481 data: A list of tokens to be written with write().
1482 format: An optional cell Format object.
1483 Returns:
1484 0: Success.
1485 other: Return value of write() method.
1487 """
1488 for token in data:
1489 error = self._write(row, col, token, cell_format)
1490 if error:
1491 return error
1492 row += 1
1494 return 0
1496 @convert_cell_args
1497 def insert_image(self, row, col, source, options=None):
1498 """
1499 Insert an image with its top-left corner in a worksheet cell.
1501 Args:
1502 row: The cell row (zero indexed).
1503 col: The cell column (zero indexed).
1504 source: Filename, BytesIO, or Image object.
1505 options: Position, scale, url and data stream of the image.
1507 Returns:
1508 0: Success.
1509 -1: Row or column is out of worksheet bounds.
1511 """
1512 # Check insert (row, col) without storing.
1513 if self._check_dimensions(row, col, True, True):
1514 warn(f"Cannot insert image at ({row}, {col}).")
1515 return -1
1517 # Convert the source to an Image object.
1518 image = self._image_from_source(source, options)
1520 image._row = row
1521 image._col = col
1522 image._set_user_options(options)
1524 self.images.append(image)
1526 return 0
1528 @convert_cell_args
1529 def embed_image(self, row, col, source, options=None):
1530 """
1531 Embed an image in a worksheet cell.
1533 Args:
1534 row: The cell row (zero indexed).
1535 col: The cell column (zero indexed).
1536 source: Filename, BytesIO, or Image object.
1537 options: Url and data stream of the image.
1539 Returns:
1540 0: Success.
1541 -1: Row or column is out of worksheet bounds.
1543 """
1544 # Check insert (row, col) without storing.
1545 if self._check_dimensions(row, col):
1546 warn(f"Cannot embed image at ({row}, {col}).")
1547 return -1
1549 if options is None:
1550 options = {}
1552 # Convert the source to an Image object.
1553 image = self._image_from_source(source, options)
1554 image._set_user_options(options)
1556 cell_format = options.get("cell_format", None)
1558 if image.url:
1559 if cell_format is None:
1560 cell_format = self.default_url_format
1562 self.ignore_write_string = True
1563 self.write_url(row, col, image.url, cell_format)
1564 self.ignore_write_string = False
1566 image_index = self.embedded_images.get_image_index(image)
1568 # Store the cell error and image index in the worksheet data table.
1569 self.table[row][col] = CellErrorTuple("#VALUE!", cell_format, image_index)
1571 return 0
1573 @convert_cell_args
1574 def insert_textbox(self, row, col, text, options=None):
1575 """
1576 Insert an textbox with its top-left corner in a worksheet cell.
1578 Args:
1579 row: The cell row (zero indexed).
1580 col: The cell column (zero indexed).
1581 text: The text for the textbox.
1582 options: Textbox options.
1584 Returns:
1585 0: Success.
1586 -1: Row or column is out of worksheet bounds.
1588 """
1589 # Check insert (row, col) without storing.
1590 if self._check_dimensions(row, col, True, True):
1591 warn(f"Cannot insert textbox at ({row}, {col}).")
1592 return -1
1594 if text is None:
1595 text = ""
1597 if options is None:
1598 options = {}
1600 x_offset = options.get("x_offset", 0)
1601 y_offset = options.get("y_offset", 0)
1602 x_scale = options.get("x_scale", 1)
1603 y_scale = options.get("y_scale", 1)
1604 anchor = options.get("object_position", 1)
1605 description = options.get("description", None)
1606 decorative = options.get("decorative", False)
1608 self.shapes.append(
1609 [
1610 row,
1611 col,
1612 x_offset,
1613 y_offset,
1614 x_scale,
1615 y_scale,
1616 text,
1617 anchor,
1618 options,
1619 description,
1620 decorative,
1621 ]
1622 )
1623 return 0
1625 @convert_cell_args
1626 def insert_chart(self, row, col, chart, options=None):
1627 """
1628 Insert an chart with its top-left corner in a worksheet cell.
1630 Args:
1631 row: The cell row (zero indexed).
1632 col: The cell column (zero indexed).
1633 chart: Chart object.
1634 options: Position and scale of the chart.
1636 Returns:
1637 0: Success.
1638 -1: Row or column is out of worksheet bounds.
1640 """
1641 # Check insert (row, col) without storing.
1642 if self._check_dimensions(row, col, True, True):
1643 warn(f"Cannot insert chart at ({row}, {col}).")
1644 return -1
1646 if options is None:
1647 options = {}
1649 # Ensure a chart isn't inserted more than once.
1650 if chart.already_inserted or chart.combined and chart.combined.already_inserted:
1651 warn("Chart cannot be inserted in a worksheet more than once.")
1652 return -2
1654 chart.already_inserted = True
1656 if chart.combined:
1657 chart.combined.already_inserted = True
1659 x_offset = options.get("x_offset", 0)
1660 y_offset = options.get("y_offset", 0)
1661 x_scale = options.get("x_scale", 1)
1662 y_scale = options.get("y_scale", 1)
1663 anchor = options.get("object_position", 1)
1664 description = options.get("description", None)
1665 decorative = options.get("decorative", False)
1667 # Allow Chart to override the scale and offset.
1668 if chart.x_scale != 1:
1669 x_scale = chart.x_scale
1671 if chart.y_scale != 1:
1672 y_scale = chart.y_scale
1674 if chart.x_offset:
1675 x_offset = chart.x_offset
1677 if chart.y_offset:
1678 y_offset = chart.y_offset
1680 self.charts.append(
1681 [
1682 row,
1683 col,
1684 chart,
1685 x_offset,
1686 y_offset,
1687 x_scale,
1688 y_scale,
1689 anchor,
1690 description,
1691 decorative,
1692 ]
1693 )
1694 return 0
1696 @convert_cell_args
1697 def write_comment(self, row, col, comment, options=None):
1698 """
1699 Write a comment to a worksheet cell.
1701 Args:
1702 row: The cell row (zero indexed).
1703 col: The cell column (zero indexed).
1704 comment: Cell comment. Str.
1705 options: Comment formatting options.
1707 Returns:
1708 0: Success.
1709 -1: Row or column is out of worksheet bounds.
1710 -2: String longer than 32k characters.
1712 """
1713 # Check that row and col are valid and store max and min values
1714 if self._check_dimensions(row, col):
1715 return -1
1717 # Check that the comment string is < 32767 chars.
1718 if len(comment) > self.xls_strmax:
1719 return -2
1721 self.has_vml = True
1722 self.has_comments = True
1724 # Store the options of the cell comment, to process on file close.
1725 comment = CommentType(row, col, comment, options)
1726 self.comments[row][col] = comment
1728 return 0
1730 def show_comments(self):
1731 """
1732 Make any comments in the worksheet visible.
1734 Args:
1735 None.
1737 Returns:
1738 Nothing.
1740 """
1741 self.comments_visible = True
1743 def set_background(self, source, is_byte_stream=False):
1744 """
1745 Set a background image for a worksheet.
1747 Args:
1748 source: Filename, BytesIO, or Image object.
1749 is_byte_stream: Deprecated. Use a BytesIO object instead.
1751 Returns:
1752 0: Success.
1754 """
1755 # Convert the source to an Image object.
1756 image = self._image_from_source(source)
1758 self.background_image = image
1760 if is_byte_stream:
1761 warn(
1762 "The `is_byte_stream` parameter in `set_background()` is deprecated. "
1763 "This argument can be omitted if you are using a BytesIO object."
1764 )
1766 return 0
1768 def set_comments_author(self, author):
1769 """
1770 Set the default author of the cell comments.
1772 Args:
1773 author: Comment author name. String.
1775 Returns:
1776 Nothing.
1778 """
1779 self.comments_author = author
1781 def get_name(self):
1782 """
1783 Retrieve the worksheet name.
1785 Args:
1786 None.
1788 Returns:
1789 Nothing.
1791 """
1792 # There is no set_name() method. Name must be set in add_worksheet().
1793 return self.name
1795 def activate(self):
1796 """
1797 Set this worksheet as the active worksheet, i.e. the worksheet that is
1798 displayed when the workbook is opened. Also set it as selected.
1800 Note: An active worksheet cannot be hidden.
1802 Args:
1803 None.
1805 Returns:
1806 Nothing.
1808 """
1809 self.hidden = 0
1810 self.selected = 1
1811 self.worksheet_meta.activesheet = self.index
1813 def select(self):
1814 """
1815 Set current worksheet as a selected worksheet, i.e. the worksheet
1816 has its tab highlighted.
1818 Note: A selected worksheet cannot be hidden.
1820 Args:
1821 None.
1823 Returns:
1824 Nothing.
1826 """
1827 self.selected = 1
1828 self.hidden = 0
1830 def hide(self):
1831 """
1832 Hide the current worksheet.
1834 Args:
1835 None.
1837 Returns:
1838 Nothing.
1840 """
1841 self.hidden = 1
1843 # A hidden worksheet shouldn't be active or selected.
1844 self.selected = 0
1846 def very_hidden(self):
1847 """
1848 Hide the current worksheet. This can only be unhidden by VBA.
1850 Args:
1851 None.
1853 Returns:
1854 Nothing.
1856 """
1857 self.hidden = 2
1859 # A hidden worksheet shouldn't be active or selected.
1860 self.selected = 0
1862 def set_first_sheet(self):
1863 """
1864 Set current worksheet as the first visible sheet. This is necessary
1865 when there are a large number of worksheets and the activated
1866 worksheet is not visible on the screen.
1868 Note: A selected worksheet cannot be hidden.
1870 Args:
1871 None.
1873 Returns:
1874 Nothing.
1876 """
1877 self.hidden = 0 # Active worksheet can't be hidden.
1878 self.worksheet_meta.firstsheet = self.index
1880 @convert_column_args
1881 def set_column(
1882 self, first_col, last_col, width=None, cell_format=None, options=None
1883 ):
1884 """
1885 Set the width, and other properties of a single column or a
1886 range of columns.
1888 Args:
1889 first_col: First column (zero-indexed).
1890 last_col: Last column (zero-indexed). Can be same as first_col.
1891 width: Column width. (optional).
1892 cell_format: Column cell_format. (optional).
1893 options: Dict of options such as hidden and level.
1895 Returns:
1896 0: Success.
1897 -1: Column number is out of worksheet bounds.
1899 """
1900 if options is None:
1901 options = {}
1903 # Ensure 2nd col is larger than first.
1904 if first_col > last_col:
1905 (first_col, last_col) = (last_col, first_col)
1907 # Don't modify the row dimensions when checking the columns.
1908 ignore_row = True
1910 # Set optional column values.
1911 hidden = options.get("hidden", False)
1912 collapsed = options.get("collapsed", False)
1913 level = options.get("level", 0)
1915 # Store the column dimension only in some conditions.
1916 if cell_format or (width and hidden):
1917 ignore_col = False
1918 else:
1919 ignore_col = True
1921 # Check that each column is valid and store the max and min values.
1922 if self._check_dimensions(0, last_col, ignore_row, ignore_col):
1923 return -1
1924 if self._check_dimensions(0, first_col, ignore_row, ignore_col):
1925 return -1
1927 # Set the limits for the outline levels (0 <= x <= 7).
1928 level = max(level, 0)
1929 level = min(level, 7)
1931 self.outline_col_level = max(self.outline_col_level, level)
1933 # Store the column data.
1934 for col in range(first_col, last_col + 1):
1935 self.col_info[col] = [width, cell_format, hidden, level, collapsed, False]
1937 # Store the column change to allow optimizations.
1938 self.col_size_changed = True
1940 return 0
1942 @convert_column_args
1943 def set_column_pixels(
1944 self, first_col, last_col, width=None, cell_format=None, options=None
1945 ):
1946 """
1947 Set the width, and other properties of a single column or a
1948 range of columns, where column width is in pixels.
1950 Args:
1951 first_col: First column (zero-indexed).
1952 last_col: Last column (zero-indexed). Can be same as first_col.
1953 width: Column width in pixels. (optional).
1954 cell_format: Column cell_format. (optional).
1955 options: Dict of options such as hidden and level.
1957 Returns:
1958 0: Success.
1959 -1: Column number is out of worksheet bounds.
1961 """
1962 if width is not None:
1963 width = self._pixels_to_width(width)
1965 return self.set_column(first_col, last_col, width, cell_format, options)
1967 def autofit(self, max_width=1790):
1968 """
1969 Simulate autofit based on the data, and datatypes in each column.
1971 Args:
1972 max_width (optional): max column width to autofit, in pixels.
1974 Returns:
1975 Nothing.
1977 """
1978 # pylint: disable=too-many-nested-blocks
1979 if self.constant_memory:
1980 warn("Autofit is not supported in constant_memory mode.")
1981 return
1983 # No data written to the target sheet; nothing to autofit
1984 if self.dim_rowmax is None:
1985 return
1987 # Store the max pixel width for each column.
1988 col_width_max = {}
1990 # Convert the autofit maximum pixel width to a column/character width,
1991 # but limit it to the Excel max limit.
1992 max_width = min(self._pixels_to_width(max_width), 255.0)
1994 # Create a reverse lookup for the share strings table so we can convert
1995 # the string id back to the original string.
1996 strings = sorted(
1997 self.str_table.string_table, key=self.str_table.string_table.__getitem__
1998 )
2000 for row_num in range(self.dim_rowmin, self.dim_rowmax + 1):
2001 if not self.table.get(row_num):
2002 continue
2004 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
2005 if col_num in self.table[row_num]:
2006 cell = self.table[row_num][col_num]
2007 cell_type = cell.__class__.__name__
2008 length = 0
2010 if cell_type in ("String", "RichString"):
2011 # Handle strings and rich strings.
2012 #
2013 # For standard shared strings we do a reverse lookup
2014 # from the shared string id to the actual string. For
2015 # rich strings we use the unformatted string. We also
2016 # split multi-line strings and handle each part
2017 # separately.
2018 if cell_type == "String":
2019 string_id = cell.string
2020 string = strings[string_id]
2021 else:
2022 string = cell.raw_string
2024 if "\n" not in string:
2025 # Single line string.
2026 length = xl_pixel_width(string)
2027 else:
2028 # Handle multi-line strings.
2029 for string in string.split("\n"):
2030 seg_length = xl_pixel_width(string)
2031 length = max(length, seg_length)
2033 elif cell_type == "Number":
2034 # Handle numbers.
2035 #
2036 # We use a workaround/optimization for numbers since
2037 # digits all have a pixel width of 7. This gives a
2038 # slightly greater width for the decimal place and
2039 # minus sign but only by a few pixels and
2040 # over-estimation is okay.
2041 length = 7 * len(str(cell.number))
2043 elif cell_type == "Datetime":
2044 # Handle dates.
2045 #
2046 # The following uses the default width for mm/dd/yyyy
2047 # dates. It isn't feasible to parse the number format
2048 # to get the actual string width for all format types.
2049 length = self.default_date_pixels
2051 elif cell_type == "Boolean":
2052 # Handle boolean values.
2053 #
2054 # Use the Excel standard widths for TRUE and FALSE.
2055 if cell.boolean:
2056 length = 31
2057 else:
2058 length = 36
2060 elif cell_type in ("Formula", "ArrayFormula"):
2061 # Handle formulas.
2062 #
2063 # We only try to autofit a formula if it has a
2064 # non-zero value.
2065 if isinstance(cell.value, (float, int)):
2066 if cell.value > 0:
2067 length = 7 * len(str(cell.value))
2069 elif isinstance(cell.value, str):
2070 length = xl_pixel_width(cell.value)
2072 elif isinstance(cell.value, bool):
2073 if cell.value:
2074 length = 31
2075 else:
2076 length = 36
2078 # If the cell is in an autofilter header we add an
2079 # additional 16 pixels for the dropdown arrow.
2080 if self.filter_cells.get((row_num, col_num)) and length > 0:
2081 length += 16
2083 # Add the string length to the lookup table.
2084 width_max = col_width_max.get(col_num, 0)
2085 if length > width_max:
2086 col_width_max[col_num] = length
2088 # Apply the width to the column.
2089 for col_num, pixel_width in col_width_max.items():
2090 # Convert the string pixel width to a character width using an
2091 # additional padding of 7 pixels, like Excel.
2092 width = self._pixels_to_width(pixel_width + 7)
2094 # Limit the width to the maximum user or Excel value.
2095 width = min(width, max_width)
2097 # Add the width to an existing col info structure or add a new one.
2098 if self.col_info.get(col_num):
2099 # We only update the width for an existing column if it is
2100 # greater than the user defined value. This allows the user
2101 # to pre-load a minimum col width.
2102 col_info = self.col_info.get(col_num)
2103 user_width = col_info[0]
2104 hidden = col_info[5]
2105 if user_width is not None and not hidden:
2106 # Col info is user defined.
2107 if width > user_width:
2108 self.col_info[col_num][0] = width
2109 self.col_info[col_num][5] = True
2110 else:
2111 self.col_info[col_num][0] = width
2112 self.col_info[col_num][5] = True
2113 else:
2114 self.col_info[col_num] = [width, None, False, 0, False, True]
2116 def set_row(self, row, height=None, cell_format=None, options=None):
2117 """
2118 Set the width, and other properties of a row.
2120 Args:
2121 row: Row number (zero-indexed).
2122 height: Row height. (optional).
2123 cell_format: Row cell_format. (optional).
2124 options: Dict of options such as hidden, level and collapsed.
2126 Returns:
2127 0: Success.
2128 -1: Row number is out of worksheet bounds.
2130 """
2131 if options is None:
2132 options = {}
2134 # Use minimum col in _check_dimensions().
2135 if self.dim_colmin is not None:
2136 min_col = self.dim_colmin
2137 else:
2138 min_col = 0
2140 # Check that row is valid.
2141 if self._check_dimensions(row, min_col):
2142 return -1
2144 if height is None:
2145 height = self.default_row_height
2147 # Set optional row values.
2148 hidden = options.get("hidden", False)
2149 collapsed = options.get("collapsed", False)
2150 level = options.get("level", 0)
2152 # If the height is 0 the row is hidden and the height is the default.
2153 if height == 0:
2154 hidden = 1
2155 height = self.default_row_height
2157 # Set the limits for the outline levels (0 <= x <= 7).
2158 level = max(level, 0)
2159 level = min(level, 7)
2161 self.outline_row_level = max(self.outline_row_level, level)
2163 # Store the row properties.
2164 self.set_rows[row] = [height, cell_format, hidden, level, collapsed]
2166 # Store the row change to allow optimizations.
2167 self.row_size_changed = True
2169 # Store the row sizes for use when calculating image vertices.
2170 self.row_sizes[row] = [height, hidden]
2172 return 0
2174 def set_row_pixels(self, row, height=None, cell_format=None, options=None):
2175 """
2176 Set the width (in pixels), and other properties of a row.
2178 Args:
2179 row: Row number (zero-indexed).
2180 height: Row height in pixels. (optional).
2181 cell_format: Row cell_format. (optional).
2182 options: Dict of options such as hidden, level and collapsed.
2184 Returns:
2185 0: Success.
2186 -1: Row number is out of worksheet bounds.
2188 """
2189 if height is not None:
2190 height = self._pixels_to_height(height)
2192 return self.set_row(row, height, cell_format, options)
2194 def set_default_row(self, height=None, hide_unused_rows=False):
2195 """
2196 Set the default row properties.
2198 Args:
2199 height: Default height. Optional, defaults to 15.
2200 hide_unused_rows: Hide unused rows. Optional, defaults to False.
2202 Returns:
2203 Nothing.
2205 """
2206 if height is None:
2207 height = self.default_row_height
2209 if height != self.original_row_height:
2210 # Store the row change to allow optimizations.
2211 self.row_size_changed = True
2212 self.default_row_height = height
2214 if hide_unused_rows:
2215 self.default_row_zeroed = 1
2217 @convert_range_args
2218 def merge_range(
2219 self, first_row, first_col, last_row, last_col, data, cell_format=None
2220 ):
2221 """
2222 Merge a range of cells.
2224 Args:
2225 first_row: The first row of the cell range. (zero indexed).
2226 first_col: The first column of the cell range.
2227 last_row: The last row of the cell range. (zero indexed).
2228 last_col: The last column of the cell range.
2229 data: Cell data.
2230 cell_format: Cell Format object.
2232 Returns:
2233 0: Success.
2234 -1: Row or column is out of worksheet bounds.
2235 other: Return value of write().
2237 """
2238 # Merge a range of cells. The first cell should contain the data and
2239 # the others should be blank. All cells should have the same format.
2241 # Excel doesn't allow a single cell to be merged
2242 if first_row == last_row and first_col == last_col:
2243 warn("Can't merge single cell")
2244 return -1
2246 # Swap last row/col with first row/col as necessary
2247 if first_row > last_row:
2248 (first_row, last_row) = (last_row, first_row)
2249 if first_col > last_col:
2250 (first_col, last_col) = (last_col, first_col)
2252 # Check that row and col are valid and store max and min values.
2253 if self._check_dimensions(first_row, first_col):
2254 return -1
2255 if self._check_dimensions(last_row, last_col):
2256 return -1
2258 # Check if the merge range overlaps a previous merged or table range.
2259 # This is a critical file corruption error in Excel.
2260 cell_range = xl_range(first_row, first_col, last_row, last_col)
2261 for row in range(first_row, last_row + 1):
2262 for col in range(first_col, last_col + 1):
2263 if self.merged_cells.get((row, col)):
2264 previous_range = self.merged_cells.get((row, col))
2265 raise OverlappingRange(
2266 f"Merge range '{cell_range}' overlaps previous merge "
2267 f"range '{previous_range}'."
2268 )
2270 if self.table_cells.get((row, col)):
2271 previous_range = self.table_cells.get((row, col))
2272 raise OverlappingRange(
2273 f"Merge range '{cell_range}' overlaps previous table "
2274 f"range '{previous_range}'."
2275 )
2277 self.merged_cells[(row, col)] = cell_range
2279 # Store the merge range.
2280 self.merge.append([first_row, first_col, last_row, last_col])
2282 # Write the first cell
2283 self._write(first_row, first_col, data, cell_format)
2285 # Pad out the rest of the area with formatted blank cells.
2286 for row in range(first_row, last_row + 1):
2287 for col in range(first_col, last_col + 1):
2288 if row == first_row and col == first_col:
2289 continue
2290 self._write_blank(row, col, "", cell_format)
2292 return 0
2294 @convert_range_args
2295 def autofilter(self, first_row, first_col, last_row, last_col):
2296 """
2297 Set the autofilter area in the worksheet.
2299 Args:
2300 first_row: The first row of the cell range. (zero indexed).
2301 first_col: The first column of the cell range.
2302 last_row: The last row of the cell range. (zero indexed).
2303 last_col: The last column of the cell range.
2305 Returns:
2306 Nothing.
2308 """
2309 # Reverse max and min values if necessary.
2310 if last_row < first_row:
2311 (first_row, last_row) = (last_row, first_row)
2312 if last_col < first_col:
2313 (first_col, last_col) = (last_col, first_col)
2315 # Build up the autofilter area range "Sheet1!$A$1:$C$13".
2316 area = self._convert_name_area(first_row, first_col, last_row, last_col)
2317 ref = xl_range(first_row, first_col, last_row, last_col)
2319 self.autofilter_area = area
2320 self.autofilter_ref = ref
2321 self.filter_range = [first_col, last_col]
2323 # Store the filter cell positions for use in the autofit calculation.
2324 for col in range(first_col, last_col + 1):
2325 # Check that the autofilter doesn't overlap a table filter.
2326 if self.filter_cells.get((first_row, col)):
2327 filter_type, filter_range = self.filter_cells.get((first_row, col))
2328 if filter_type == "table":
2329 raise OverlappingRange(
2330 f"Worksheet autofilter range '{ref}' overlaps previous "
2331 f"Table autofilter range '{filter_range}'."
2332 )
2334 self.filter_cells[(first_row, col)] = ("worksheet", ref)
2336 def filter_column(self, col, criteria):
2337 """
2338 Set the column filter criteria.
2340 Args:
2341 col: Filter column (zero-indexed).
2342 criteria: Filter criteria.
2344 Returns:
2345 Nothing.
2347 """
2348 if not self.autofilter_area:
2349 warn("Must call autofilter() before filter_column()")
2350 return
2352 # Check for a column reference in A1 notation and substitute.
2353 try:
2354 int(col)
2355 except ValueError:
2356 # Convert col ref to a cell ref and then to a col number.
2357 col_letter = col
2358 (_, col) = xl_cell_to_rowcol(col + "1")
2360 if col >= self.xls_colmax:
2361 warn(f"Invalid column '{col_letter}'")
2362 return
2364 (col_first, col_last) = self.filter_range
2366 # Reject column if it is outside filter range.
2367 if col < col_first or col > col_last:
2368 warn(
2369 f"Column '{col}' outside autofilter() column "
2370 f"range ({col_first}, {col_last})"
2371 )
2372 return
2374 tokens = self._extract_filter_tokens(criteria)
2376 if len(tokens) not in (3, 7):
2377 warn(f"Incorrect number of tokens in criteria '{criteria}'")
2379 tokens = self._parse_filter_expression(criteria, tokens)
2381 # Excel handles single or double custom filters as default filters.
2382 # We need to check for them and handle them accordingly.
2383 if len(tokens) == 2 and tokens[0] == 2:
2384 # Single equality.
2385 self.filter_column_list(col, [tokens[1]])
2386 elif len(tokens) == 5 and tokens[0] == 2 and tokens[2] == 1 and tokens[3] == 2:
2387 # Double equality with "or" operator.
2388 self.filter_column_list(col, [tokens[1], tokens[4]])
2389 else:
2390 # Non default custom filter.
2391 self.filter_cols[col] = tokens
2392 self.filter_type[col] = 0
2394 self.filter_on = 1
2396 def filter_column_list(self, col, filters):
2397 """
2398 Set the column filter criteria in Excel 2007 list style.
2400 Args:
2401 col: Filter column (zero-indexed).
2402 filters: List of filter criteria to match.
2404 Returns:
2405 Nothing.
2407 """
2408 if not self.autofilter_area:
2409 warn("Must call autofilter() before filter_column()")
2410 return
2412 # Check for a column reference in A1 notation and substitute.
2413 try:
2414 int(col)
2415 except ValueError:
2416 # Convert col ref to a cell ref and then to a col number.
2417 col_letter = col
2418 (_, col) = xl_cell_to_rowcol(col + "1")
2420 if col >= self.xls_colmax:
2421 warn(f"Invalid column '{col_letter}'")
2422 return
2424 (col_first, col_last) = self.filter_range
2426 # Reject column if it is outside filter range.
2427 if col < col_first or col > col_last:
2428 warn(
2429 f"Column '{col}' outside autofilter() column range "
2430 f"({col_first},{col_last})"
2431 )
2432 return
2434 self.filter_cols[col] = filters
2435 self.filter_type[col] = 1
2436 self.filter_on = 1
2438 @convert_range_args
2439 def data_validation(self, first_row, first_col, last_row, last_col, options=None):
2440 """
2441 Add a data validation to a worksheet.
2443 Args:
2444 first_row: The first row of the cell range. (zero indexed).
2445 first_col: The first column of the cell range.
2446 last_row: The last row of the cell range. (zero indexed).
2447 last_col: The last column of the cell range.
2448 options: Data validation options.
2450 Returns:
2451 0: Success.
2452 -1: Row or column is out of worksheet bounds.
2453 -2: Incorrect parameter or option.
2454 """
2455 # Check that row and col are valid without storing the values.
2456 if self._check_dimensions(first_row, first_col, True, True):
2457 return -1
2458 if self._check_dimensions(last_row, last_col, True, True):
2459 return -1
2461 if options is None:
2462 options = {}
2463 else:
2464 # Copy the user defined options so they aren't modified.
2465 options = options.copy()
2467 # Valid input parameters.
2468 valid_parameters = {
2469 "validate",
2470 "criteria",
2471 "value",
2472 "source",
2473 "minimum",
2474 "maximum",
2475 "ignore_blank",
2476 "dropdown",
2477 "show_input",
2478 "input_title",
2479 "input_message",
2480 "show_error",
2481 "error_title",
2482 "error_message",
2483 "error_type",
2484 "other_cells",
2485 "multi_range",
2486 }
2488 # Check for valid input parameters.
2489 for param_key in options.keys():
2490 if param_key not in valid_parameters:
2491 warn(f"Unknown parameter '{param_key}' in data_validation()")
2492 return -2
2494 # Map alternative parameter names 'source' or 'minimum' to 'value'.
2495 if "source" in options:
2496 options["value"] = options["source"]
2497 if "minimum" in options:
2498 options["value"] = options["minimum"]
2500 # 'validate' is a required parameter.
2501 if "validate" not in options:
2502 warn("Parameter 'validate' is required in data_validation()")
2503 return -2
2505 # List of valid validation types.
2506 valid_types = {
2507 "any": "none",
2508 "any value": "none",
2509 "whole number": "whole",
2510 "whole": "whole",
2511 "integer": "whole",
2512 "decimal": "decimal",
2513 "list": "list",
2514 "date": "date",
2515 "time": "time",
2516 "text length": "textLength",
2517 "length": "textLength",
2518 "custom": "custom",
2519 }
2521 # Check for valid validation types.
2522 if options["validate"] not in valid_types:
2523 warn(
2524 f"Unknown validation type '{options['validate']}' for parameter "
2525 f"'validate' in data_validation()"
2526 )
2527 return -2
2529 options["validate"] = valid_types[options["validate"]]
2531 # No action is required for validation type 'any' if there are no
2532 # input messages to display.
2533 if (
2534 options["validate"] == "none"
2535 and options.get("input_title") is None
2536 and options.get("input_message") is None
2537 ):
2538 return -2
2540 # The any, list and custom validations don't have a criteria so we use
2541 # a default of 'between'.
2542 if (
2543 options["validate"] == "none"
2544 or options["validate"] == "list"
2545 or options["validate"] == "custom"
2546 ):
2547 options["criteria"] = "between"
2548 options["maximum"] = None
2550 # 'criteria' is a required parameter.
2551 if "criteria" not in options:
2552 warn("Parameter 'criteria' is required in data_validation()")
2553 return -2
2555 # Valid criteria types.
2556 criteria_types = {
2557 "between": "between",
2558 "not between": "notBetween",
2559 "equal to": "equal",
2560 "=": "equal",
2561 "==": "equal",
2562 "not equal to": "notEqual",
2563 "!=": "notEqual",
2564 "<>": "notEqual",
2565 "greater than": "greaterThan",
2566 ">": "greaterThan",
2567 "less than": "lessThan",
2568 "<": "lessThan",
2569 "greater than or equal to": "greaterThanOrEqual",
2570 ">=": "greaterThanOrEqual",
2571 "less than or equal to": "lessThanOrEqual",
2572 "<=": "lessThanOrEqual",
2573 }
2575 # Check for valid criteria types.
2576 if options["criteria"] not in criteria_types:
2577 warn(
2578 f"Unknown criteria type '{options['criteria']}' for parameter "
2579 f"'criteria' in data_validation()"
2580 )
2581 return -2
2583 options["criteria"] = criteria_types[options["criteria"]]
2585 # 'Between' and 'Not between' criteria require 2 values.
2586 if options["criteria"] == "between" or options["criteria"] == "notBetween":
2587 if "maximum" not in options:
2588 warn(
2589 "Parameter 'maximum' is required in data_validation() "
2590 "when using 'between' or 'not between' criteria"
2591 )
2592 return -2
2593 else:
2594 options["maximum"] = None
2596 # Valid error dialog types.
2597 error_types = {
2598 "stop": 0,
2599 "warning": 1,
2600 "information": 2,
2601 }
2603 # Check for valid error dialog types.
2604 if "error_type" not in options:
2605 options["error_type"] = 0
2606 elif options["error_type"] not in error_types:
2607 warn(
2608 f"Unknown criteria type '{options['error_type']}' "
2609 f"for parameter 'error_type'."
2610 )
2611 return -2
2612 else:
2613 options["error_type"] = error_types[options["error_type"]]
2615 # Convert date/times value if required.
2616 if (
2617 options["validate"] in ("date", "time")
2618 and options["value"]
2619 and _supported_datetime(options["value"])
2620 ):
2621 date_time = self._convert_date_time(options["value"])
2622 # Format date number to the same precision as Excel.
2623 options["value"] = f"{date_time:.16g}"
2625 if options["maximum"] and _supported_datetime(options["maximum"]):
2626 date_time = self._convert_date_time(options["maximum"])
2627 options["maximum"] = f"{date_time:.16g}"
2629 # Check that the input title doesn't exceed the maximum length.
2630 if options.get("input_title") and len(options["input_title"]) > 32:
2631 warn(
2632 f"Length of input title '{options['input_title']}' "
2633 f"exceeds Excel's limit of 32"
2634 )
2635 return -2
2637 # Check that the error title doesn't exceed the maximum length.
2638 if options.get("error_title") and len(options["error_title"]) > 32:
2639 warn(
2640 f"Length of error title '{options['error_title']}' "
2641 f"exceeds Excel's limit of 32"
2642 )
2643 return -2
2645 # Check that the input message doesn't exceed the maximum length.
2646 if options.get("input_message") and len(options["input_message"]) > 255:
2647 warn(
2648 f"Length of input message '{options['input_message']}' "
2649 f"exceeds Excel's limit of 255"
2650 )
2651 return -2
2653 # Check that the error message doesn't exceed the maximum length.
2654 if options.get("error_message") and len(options["error_message"]) > 255:
2655 warn(
2656 f"Length of error message '{options['error_message']}' "
2657 f"exceeds Excel's limit of 255"
2658 )
2659 return -2
2661 # Check that the input list doesn't exceed the maximum length.
2662 if options["validate"] == "list" and isinstance(options["value"], list):
2663 formula = self._csv_join(*options["value"])
2664 if len(formula) > 255:
2665 warn(
2666 f"Length of list items '{formula}' exceeds Excel's limit of "
2667 f"255, use a formula range instead"
2668 )
2669 return -2
2671 # Set some defaults if they haven't been defined by the user.
2672 if "ignore_blank" not in options:
2673 options["ignore_blank"] = 1
2674 if "dropdown" not in options:
2675 options["dropdown"] = 1
2676 if "show_input" not in options:
2677 options["show_input"] = 1
2678 if "show_error" not in options:
2679 options["show_error"] = 1
2681 # These are the cells to which the validation is applied.
2682 options["cells"] = [[first_row, first_col, last_row, last_col]]
2684 # A (for now) undocumented parameter to pass additional cell ranges.
2685 if "other_cells" in options:
2686 options["cells"].extend(options["other_cells"])
2688 # Override with user defined multiple range if provided.
2689 if "multi_range" in options:
2690 options["multi_range"] = options["multi_range"].replace("$", "")
2692 # Store the validation information until we close the worksheet.
2693 self.validations.append(options)
2695 return 0
2697 @convert_range_args
2698 def conditional_format(
2699 self, first_row, first_col, last_row, last_col, options=None
2700 ):
2701 """
2702 Add a conditional format to a worksheet.
2704 Args:
2705 first_row: The first row of the cell range. (zero indexed).
2706 first_col: The first column of the cell range.
2707 last_row: The last row of the cell range. (zero indexed).
2708 last_col: The last column of the cell range.
2709 options: Conditional format options.
2711 Returns:
2712 0: Success.
2713 -1: Row or column is out of worksheet bounds.
2714 -2: Incorrect parameter or option.
2715 """
2716 # Check that row and col are valid without storing the values.
2717 if self._check_dimensions(first_row, first_col, True, True):
2718 return -1
2719 if self._check_dimensions(last_row, last_col, True, True):
2720 return -1
2722 if options is None:
2723 options = {}
2724 else:
2725 # Copy the user defined options so they aren't modified.
2726 options = options.copy()
2728 # Valid input parameters.
2729 valid_parameter = {
2730 "type",
2731 "format",
2732 "criteria",
2733 "value",
2734 "minimum",
2735 "maximum",
2736 "stop_if_true",
2737 "min_type",
2738 "mid_type",
2739 "max_type",
2740 "min_value",
2741 "mid_value",
2742 "max_value",
2743 "min_color",
2744 "mid_color",
2745 "max_color",
2746 "min_length",
2747 "max_length",
2748 "multi_range",
2749 "bar_color",
2750 "bar_negative_color",
2751 "bar_negative_color_same",
2752 "bar_solid",
2753 "bar_border_color",
2754 "bar_negative_border_color",
2755 "bar_negative_border_color_same",
2756 "bar_no_border",
2757 "bar_direction",
2758 "bar_axis_position",
2759 "bar_axis_color",
2760 "bar_only",
2761 "data_bar_2010",
2762 "icon_style",
2763 "reverse_icons",
2764 "icons_only",
2765 "icons",
2766 }
2768 # Check for valid input parameters.
2769 for param_key in options.keys():
2770 if param_key not in valid_parameter:
2771 warn(f"Unknown parameter '{param_key}' in conditional_format()")
2772 return -2
2774 # 'type' is a required parameter.
2775 if "type" not in options:
2776 warn("Parameter 'type' is required in conditional_format()")
2777 return -2
2779 # Valid types.
2780 valid_type = {
2781 "cell": "cellIs",
2782 "date": "date",
2783 "time": "time",
2784 "average": "aboveAverage",
2785 "duplicate": "duplicateValues",
2786 "unique": "uniqueValues",
2787 "top": "top10",
2788 "bottom": "top10",
2789 "text": "text",
2790 "time_period": "timePeriod",
2791 "blanks": "containsBlanks",
2792 "no_blanks": "notContainsBlanks",
2793 "errors": "containsErrors",
2794 "no_errors": "notContainsErrors",
2795 "2_color_scale": "2_color_scale",
2796 "3_color_scale": "3_color_scale",
2797 "data_bar": "dataBar",
2798 "formula": "expression",
2799 "icon_set": "iconSet",
2800 }
2802 # Check for valid types.
2803 if options["type"] not in valid_type:
2804 warn(
2805 f"Unknown value '{options['type']}' for parameter 'type' "
2806 f"in conditional_format()"
2807 )
2808 return -2
2810 if options["type"] == "bottom":
2811 options["direction"] = "bottom"
2812 options["type"] = valid_type[options["type"]]
2814 # Valid criteria types.
2815 criteria_type = {
2816 "between": "between",
2817 "not between": "notBetween",
2818 "equal to": "equal",
2819 "=": "equal",
2820 "==": "equal",
2821 "not equal to": "notEqual",
2822 "!=": "notEqual",
2823 "<>": "notEqual",
2824 "greater than": "greaterThan",
2825 ">": "greaterThan",
2826 "less than": "lessThan",
2827 "<": "lessThan",
2828 "greater than or equal to": "greaterThanOrEqual",
2829 ">=": "greaterThanOrEqual",
2830 "less than or equal to": "lessThanOrEqual",
2831 "<=": "lessThanOrEqual",
2832 "containing": "containsText",
2833 "not containing": "notContains",
2834 "begins with": "beginsWith",
2835 "ends with": "endsWith",
2836 "yesterday": "yesterday",
2837 "today": "today",
2838 "last 7 days": "last7Days",
2839 "last week": "lastWeek",
2840 "this week": "thisWeek",
2841 "next week": "nextWeek",
2842 "last month": "lastMonth",
2843 "this month": "thisMonth",
2844 "next month": "nextMonth",
2845 # For legacy, but incorrect, support.
2846 "continue week": "nextWeek",
2847 "continue month": "nextMonth",
2848 }
2850 # Check for valid criteria types.
2851 if "criteria" in options and options["criteria"] in criteria_type:
2852 options["criteria"] = criteria_type[options["criteria"]]
2854 # Convert boolean values if required.
2855 if "value" in options and isinstance(options["value"], bool):
2856 options["value"] = str(options["value"]).upper()
2858 # Convert date/times value if required.
2859 if options["type"] in ("date", "time"):
2860 options["type"] = "cellIs"
2862 if "value" in options:
2863 if not _supported_datetime(options["value"]):
2864 warn("Conditional format 'value' must be a datetime object.")
2865 return -2
2867 date_time = self._convert_date_time(options["value"])
2868 # Format date number to the same precision as Excel.
2869 options["value"] = f"{date_time:.16g}"
2871 if "minimum" in options:
2872 if not _supported_datetime(options["minimum"]):
2873 warn("Conditional format 'minimum' must be a datetime object.")
2874 return -2
2876 date_time = self._convert_date_time(options["minimum"])
2877 options["minimum"] = f"{date_time:.16g}"
2879 if "maximum" in options:
2880 if not _supported_datetime(options["maximum"]):
2881 warn("Conditional format 'maximum' must be a datetime object.")
2882 return -2
2884 date_time = self._convert_date_time(options["maximum"])
2885 options["maximum"] = f"{date_time:.16g}"
2887 # Valid icon styles.
2888 valid_icons = {
2889 "3_arrows": "3Arrows", # 1
2890 "3_flags": "3Flags", # 2
2891 "3_traffic_lights_rimmed": "3TrafficLights2", # 3
2892 "3_symbols_circled": "3Symbols", # 4
2893 "4_arrows": "4Arrows", # 5
2894 "4_red_to_black": "4RedToBlack", # 6
2895 "4_traffic_lights": "4TrafficLights", # 7
2896 "5_arrows_gray": "5ArrowsGray", # 8
2897 "5_quarters": "5Quarters", # 9
2898 "3_arrows_gray": "3ArrowsGray", # 10
2899 "3_traffic_lights": "3TrafficLights", # 11
2900 "3_signs": "3Signs", # 12
2901 "3_symbols": "3Symbols2", # 13
2902 "4_arrows_gray": "4ArrowsGray", # 14
2903 "4_ratings": "4Rating", # 15
2904 "5_arrows": "5Arrows", # 16
2905 "5_ratings": "5Rating",
2906 } # 17
2908 # Set the icon set properties.
2909 if options["type"] == "iconSet":
2910 # An icon_set must have an icon style.
2911 if not options.get("icon_style"):
2912 warn(
2913 "The 'icon_style' parameter must be specified when "
2914 "'type' == 'icon_set' in conditional_format()."
2915 )
2916 return -3
2918 # Check for valid icon styles.
2919 if options["icon_style"] not in valid_icons:
2920 warn(
2921 f"Unknown icon_style '{options['icon_style']}' "
2922 f"in conditional_format()."
2923 )
2924 return -2
2926 options["icon_style"] = valid_icons[options["icon_style"]]
2928 # Set the number of icons for the icon style.
2929 options["total_icons"] = 3
2930 if options["icon_style"].startswith("4"):
2931 options["total_icons"] = 4
2932 elif options["icon_style"].startswith("5"):
2933 options["total_icons"] = 5
2935 options["icons"] = self._set_icon_props(
2936 options.get("total_icons"), options.get("icons")
2937 )
2939 # Swap last row/col for first row/col as necessary
2940 if first_row > last_row:
2941 first_row, last_row = last_row, first_row
2943 if first_col > last_col:
2944 first_col, last_col = last_col, first_col
2946 # Set the formatting range.
2947 cell_range = xl_range(first_row, first_col, last_row, last_col)
2948 start_cell = xl_rowcol_to_cell(first_row, first_col)
2950 # Override with user defined multiple range if provided.
2951 if "multi_range" in options:
2952 cell_range = options["multi_range"]
2953 cell_range = cell_range.replace("$", "")
2955 # Get the dxf format index.
2956 if "format" in options and options["format"]:
2957 options["format"] = options["format"]._get_dxf_index()
2959 # Set the priority based on the order of adding.
2960 options["priority"] = self.dxf_priority
2961 self.dxf_priority += 1
2963 # Check for 2010 style data_bar parameters.
2964 # pylint: disable=too-many-boolean-expressions
2965 if (
2966 self.use_data_bars_2010
2967 or options.get("data_bar_2010")
2968 or options.get("bar_solid")
2969 or options.get("bar_border_color")
2970 or options.get("bar_negative_color")
2971 or options.get("bar_negative_color_same")
2972 or options.get("bar_negative_border_color")
2973 or options.get("bar_negative_border_color_same")
2974 or options.get("bar_no_border")
2975 or options.get("bar_axis_position")
2976 or options.get("bar_axis_color")
2977 or options.get("bar_direction")
2978 ):
2979 options["is_data_bar_2010"] = True
2981 # Special handling of text criteria.
2982 if options["type"] == "text":
2983 value = options["value"]
2984 length = len(value)
2985 criteria = options["criteria"]
2987 if options["criteria"] == "containsText":
2988 options["type"] = "containsText"
2989 options["formula"] = f'NOT(ISERROR(SEARCH("{value}",{start_cell})))'
2990 elif options["criteria"] == "notContains":
2991 options["type"] = "notContainsText"
2992 options["formula"] = f'ISERROR(SEARCH("{value}",{start_cell}))'
2993 elif options["criteria"] == "beginsWith":
2994 options["type"] = "beginsWith"
2995 options["formula"] = f'LEFT({start_cell},{length})="{value}"'
2996 elif options["criteria"] == "endsWith":
2997 options["type"] = "endsWith"
2998 options["formula"] = f'RIGHT({start_cell},{length})="{value}"'
2999 else:
3000 warn(f"Invalid text criteria '{criteria}' in conditional_format()")
3002 # Special handling of time time_period criteria.
3003 if options["type"] == "timePeriod":
3004 if options["criteria"] == "yesterday":
3005 options["formula"] = f"FLOOR({start_cell},1)=TODAY()-1"
3007 elif options["criteria"] == "today":
3008 options["formula"] = f"FLOOR({start_cell},1)=TODAY()"
3010 elif options["criteria"] == "tomorrow":
3011 options["formula"] = f"FLOOR({start_cell},1)=TODAY()+1"
3013 # fmt: off
3014 elif options["criteria"] == "last7Days":
3015 options["formula"] = (
3016 f"AND(TODAY()-FLOOR({start_cell},1)<=6,"
3017 f"FLOOR({start_cell},1)<=TODAY())"
3018 )
3019 # fmt: on
3021 elif options["criteria"] == "lastWeek":
3022 options["formula"] = (
3023 f"AND(TODAY()-ROUNDDOWN({start_cell},0)>=(WEEKDAY(TODAY())),"
3024 f"TODAY()-ROUNDDOWN({start_cell},0)<(WEEKDAY(TODAY())+7))"
3025 )
3027 elif options["criteria"] == "thisWeek":
3028 options["formula"] = (
3029 f"AND(TODAY()-ROUNDDOWN({start_cell},0)<=WEEKDAY(TODAY())-1,"
3030 f"ROUNDDOWN({start_cell},0)-TODAY()<=7-WEEKDAY(TODAY()))"
3031 )
3033 elif options["criteria"] == "nextWeek":
3034 options["formula"] = (
3035 f"AND(ROUNDDOWN({start_cell},0)-TODAY()>(7-WEEKDAY(TODAY())),"
3036 f"ROUNDDOWN({start_cell},0)-TODAY()<(15-WEEKDAY(TODAY())))"
3037 )
3039 elif options["criteria"] == "lastMonth":
3040 options["formula"] = (
3041 f"AND(MONTH({start_cell})=MONTH(TODAY())-1,"
3042 f"OR(YEAR({start_cell})=YEAR("
3043 f"TODAY()),AND(MONTH({start_cell})=1,YEAR(A1)=YEAR(TODAY())-1)))"
3044 )
3046 # fmt: off
3047 elif options["criteria"] == "thisMonth":
3048 options["formula"] = (
3049 f"AND(MONTH({start_cell})=MONTH(TODAY()),"
3050 f"YEAR({start_cell})=YEAR(TODAY()))"
3051 )
3052 # fmt: on
3054 elif options["criteria"] == "nextMonth":
3055 options["formula"] = (
3056 f"AND(MONTH({start_cell})=MONTH(TODAY())+1,"
3057 f"OR(YEAR({start_cell})=YEAR("
3058 f"TODAY()),AND(MONTH({start_cell})=12,"
3059 f"YEAR({start_cell})=YEAR(TODAY())+1)))"
3060 )
3062 else:
3063 warn(
3064 f"Invalid time_period criteria '{options['criteria']}' "
3065 f"in conditional_format()"
3066 )
3068 # Special handling of blanks/error types.
3069 if options["type"] == "containsBlanks":
3070 options["formula"] = f"LEN(TRIM({start_cell}))=0"
3072 if options["type"] == "notContainsBlanks":
3073 options["formula"] = f"LEN(TRIM({start_cell}))>0"
3075 if options["type"] == "containsErrors":
3076 options["formula"] = f"ISERROR({start_cell})"
3078 if options["type"] == "notContainsErrors":
3079 options["formula"] = f"NOT(ISERROR({start_cell}))"
3081 # Special handling for 2 color scale.
3082 if options["type"] == "2_color_scale":
3083 options["type"] = "colorScale"
3085 # Color scales don't use any additional formatting.
3086 options["format"] = None
3088 # Turn off 3 color parameters.
3089 options["mid_type"] = None
3090 options["mid_color"] = None
3092 options.setdefault("min_type", "min")
3093 options.setdefault("max_type", "max")
3094 options.setdefault("min_value", 0)
3095 options.setdefault("max_value", 0)
3096 options.setdefault("min_color", Color("#FF7128"))
3097 options.setdefault("max_color", Color("#FFEF9C"))
3099 options["min_color"] = Color._from_value(options["min_color"])
3100 options["max_color"] = Color._from_value(options["max_color"])
3102 # Special handling for 3 color scale.
3103 if options["type"] == "3_color_scale":
3104 options["type"] = "colorScale"
3106 # Color scales don't use any additional formatting.
3107 options["format"] = None
3109 options.setdefault("min_type", "min")
3110 options.setdefault("mid_type", "percentile")
3111 options.setdefault("max_type", "max")
3112 options.setdefault("min_value", 0)
3113 options.setdefault("max_value", 0)
3114 options.setdefault("min_color", Color("#F8696B"))
3115 options.setdefault("mid_color", Color("#FFEB84"))
3116 options.setdefault("max_color", Color("#63BE7B"))
3118 options["min_color"] = Color._from_value(options["min_color"])
3119 options["mid_color"] = Color._from_value(options["mid_color"])
3120 options["max_color"] = Color._from_value(options["max_color"])
3122 # Set a default mid value.
3123 if "mid_value" not in options:
3124 options["mid_value"] = 50
3126 # Special handling for data bar.
3127 if options["type"] == "dataBar":
3128 # Color scales don't use any additional formatting.
3129 options["format"] = None
3131 if not options.get("min_type"):
3132 options["min_type"] = "min"
3133 options["x14_min_type"] = "autoMin"
3134 else:
3135 options["x14_min_type"] = options["min_type"]
3137 if not options.get("max_type"):
3138 options["max_type"] = "max"
3139 options["x14_max_type"] = "autoMax"
3140 else:
3141 options["x14_max_type"] = options["max_type"]
3143 options.setdefault("min_value", 0)
3144 options.setdefault("max_value", 0)
3145 options.setdefault("bar_color", Color("#638EC6"))
3146 options.setdefault("bar_border_color", options["bar_color"])
3147 options.setdefault("bar_only", False)
3148 options.setdefault("bar_no_border", False)
3149 options.setdefault("bar_solid", False)
3150 options.setdefault("bar_direction", "")
3151 options.setdefault("bar_negative_color", Color("#FF0000"))
3152 options.setdefault("bar_negative_border_color", Color("#FF0000"))
3153 options.setdefault("bar_negative_color_same", False)
3154 options.setdefault("bar_negative_border_color_same", False)
3155 options.setdefault("bar_axis_position", "")
3156 options.setdefault("bar_axis_color", Color("#000000"))
3158 options["bar_color"] = Color._from_value(options["bar_color"])
3159 options["bar_border_color"] = Color._from_value(options["bar_border_color"])
3160 options["bar_axis_color"] = Color._from_value(options["bar_axis_color"])
3161 options["bar_negative_color"] = Color._from_value(
3162 options["bar_negative_color"]
3163 )
3164 options["bar_negative_border_color"] = Color._from_value(
3165 options["bar_negative_border_color"]
3166 )
3168 # Adjust for 2010 style data_bar parameters.
3169 if options.get("is_data_bar_2010"):
3170 self.excel_version = 2010
3172 if options["min_type"] == "min" and options["min_value"] == 0:
3173 options["min_value"] = None
3175 if options["max_type"] == "max" and options["max_value"] == 0:
3176 options["max_value"] = None
3178 options["range"] = cell_range
3180 # Strip the leading = from formulas.
3181 try:
3182 options["min_value"] = options["min_value"].lstrip("=")
3183 except (KeyError, AttributeError):
3184 pass
3185 try:
3186 options["mid_value"] = options["mid_value"].lstrip("=")
3187 except (KeyError, AttributeError):
3188 pass
3189 try:
3190 options["max_value"] = options["max_value"].lstrip("=")
3191 except (KeyError, AttributeError):
3192 pass
3194 # Store the conditional format until we close the worksheet.
3195 if cell_range in self.cond_formats:
3196 self.cond_formats[cell_range].append(options)
3197 else:
3198 self.cond_formats[cell_range] = [options]
3200 return 0
3202 @convert_range_args
3203 def add_table(self, first_row, first_col, last_row, last_col, options=None):
3204 """
3205 Add an Excel table to a worksheet.
3207 Args:
3208 first_row: The first row of the cell range. (zero indexed).
3209 first_col: The first column of the cell range.
3210 last_row: The last row of the cell range. (zero indexed).
3211 last_col: The last column of the cell range.
3212 options: Table format options. (Optional)
3214 Returns:
3215 0: Success.
3216 -1: Row or column is out of worksheet bounds.
3217 -2: Incorrect parameter or option.
3218 -3: Not supported in constant_memory mode.
3219 """
3220 table = {}
3221 col_formats = {}
3223 if options is None:
3224 options = {}
3225 else:
3226 # Copy the user defined options so they aren't modified.
3227 options = options.copy()
3229 if self.constant_memory:
3230 warn("add_table() isn't supported in 'constant_memory' mode")
3231 return -3
3233 # Check that row and col are valid without storing the values.
3234 if self._check_dimensions(first_row, first_col, True, True):
3235 return -1
3236 if self._check_dimensions(last_row, last_col, True, True):
3237 return -1
3239 # Swap last row/col for first row/col as necessary.
3240 if first_row > last_row:
3241 (first_row, last_row) = (last_row, first_row)
3242 if first_col > last_col:
3243 (first_col, last_col) = (last_col, first_col)
3245 # Check if the table range overlaps a previous merged or table range.
3246 # This is a critical file corruption error in Excel.
3247 cell_range = xl_range(first_row, first_col, last_row, last_col)
3248 for row in range(first_row, last_row + 1):
3249 for col in range(first_col, last_col + 1):
3250 if self.table_cells.get((row, col)):
3251 previous_range = self.table_cells.get((row, col))
3252 raise OverlappingRange(
3253 f"Table range '{cell_range}' overlaps previous "
3254 f"table range '{previous_range}'."
3255 )
3257 if self.merged_cells.get((row, col)):
3258 previous_range = self.merged_cells.get((row, col))
3259 raise OverlappingRange(
3260 f"Table range '{cell_range}' overlaps previous "
3261 f"merge range '{previous_range}'."
3262 )
3264 self.table_cells[(row, col)] = cell_range
3266 # Valid input parameters.
3267 valid_parameter = {
3268 "autofilter",
3269 "banded_columns",
3270 "banded_rows",
3271 "columns",
3272 "data",
3273 "first_column",
3274 "header_row",
3275 "last_column",
3276 "name",
3277 "style",
3278 "total_row",
3279 }
3281 # Check for valid input parameters.
3282 for param_key in options.keys():
3283 if param_key not in valid_parameter:
3284 warn(f"Unknown parameter '{param_key}' in add_table()")
3285 return -2
3287 # Turn on Excel's defaults.
3288 options["banded_rows"] = options.get("banded_rows", True)
3289 options["header_row"] = options.get("header_row", True)
3290 options["autofilter"] = options.get("autofilter", True)
3292 # Check that there are enough rows.
3293 num_rows = last_row - first_row
3294 if options["header_row"]:
3295 num_rows -= 1
3297 if num_rows < 0:
3298 warn("Must have at least one data row in in add_table()")
3299 return -2
3301 # Set the table options.
3302 table["show_first_col"] = options.get("first_column", False)
3303 table["show_last_col"] = options.get("last_column", False)
3304 table["show_row_stripes"] = options.get("banded_rows", False)
3305 table["show_col_stripes"] = options.get("banded_columns", False)
3306 table["header_row_count"] = options.get("header_row", 0)
3307 table["totals_row_shown"] = options.get("total_row", False)
3309 # Set the table name.
3310 if "name" in options:
3311 name = options["name"]
3312 table["name"] = name
3314 if " " in name:
3315 warn(f"Name '{name}' in add_table() cannot contain spaces")
3316 return -2
3318 # Warn if the name contains invalid chars as defined by Excel.
3319 if not re.match(r"^[\w\\][\w\\.]*$", name, re.UNICODE) or re.match(
3320 r"^\d", name
3321 ):
3322 warn(f"Invalid Excel characters in add_table(): '{name}'")
3323 return -2
3325 # Warn if the name looks like a cell name.
3326 if re.match(r"^[a-zA-Z][a-zA-Z]?[a-dA-D]?\d+$", name):
3327 warn(f"Name looks like a cell name in add_table(): '{name}'")
3328 return -2
3330 # Warn if the name looks like a R1C1 cell reference.
3331 if re.match(r"^[rcRC]$", name) or re.match(r"^[rcRC]\d+[rcRC]\d+$", name):
3332 warn(f"Invalid name '{name}' like a RC cell ref in add_table()")
3333 return -2
3335 # Set the table style.
3336 if "style" in options:
3337 table["style"] = options["style"]
3339 if table["style"] is None:
3340 table["style"] = ""
3342 # Remove whitespace from style name.
3343 table["style"] = table["style"].replace(" ", "")
3344 else:
3345 table["style"] = "TableStyleMedium9"
3347 # Set the data range rows (without the header and footer).
3348 first_data_row = first_row
3349 last_data_row = last_row
3351 if options.get("header_row"):
3352 first_data_row += 1
3354 if options.get("total_row"):
3355 last_data_row -= 1
3357 # Set the table and autofilter ranges.
3358 table["range"] = xl_range(first_row, first_col, last_row, last_col)
3360 table["a_range"] = xl_range(first_row, first_col, last_data_row, last_col)
3362 # If the header row if off the default is to turn autofilter off.
3363 if not options["header_row"]:
3364 options["autofilter"] = 0
3366 # Set the autofilter range.
3367 if options["autofilter"]:
3368 table["autofilter"] = table["a_range"]
3370 # Add the table columns.
3371 col_id = 1
3372 table["columns"] = []
3373 seen_names = {}
3375 for col_num in range(first_col, last_col + 1):
3376 # Set up the default column data.
3377 col_data = {
3378 "id": col_id,
3379 "name": "Column" + str(col_id),
3380 "total_string": "",
3381 "total_function": "",
3382 "custom_total": "",
3383 "total_value": 0,
3384 "formula": "",
3385 "format": None,
3386 "name_format": None,
3387 }
3389 # Overwrite the defaults with any user defined values.
3390 if "columns" in options:
3391 # Check if there are user defined values for this column.
3392 if col_id <= len(options["columns"]):
3393 user_data = options["columns"][col_id - 1]
3394 else:
3395 user_data = None
3397 if user_data:
3398 # Get the column format.
3399 xformat = user_data.get("format", None)
3401 # Map user defined values to internal values.
3402 if user_data.get("header"):
3403 col_data["name"] = user_data["header"]
3405 # Excel requires unique case insensitive header names.
3406 header_name = col_data["name"]
3407 name = header_name.lower()
3408 if name in seen_names:
3409 warn(f"Duplicate header name in add_table(): '{name}'")
3410 return -2
3412 seen_names[name] = True
3414 col_data["name_format"] = user_data.get("header_format")
3416 # Handle the column formula.
3417 if "formula" in user_data and user_data["formula"]:
3418 formula = user_data["formula"]
3420 # Remove the formula '=' sign if it exists.
3421 if formula.startswith("="):
3422 formula = formula.lstrip("=")
3424 # Convert Excel 2010 "@" ref to 2007 "#This Row".
3425 formula = self._prepare_table_formula(formula)
3427 # Escape any future functions.
3428 formula = self._prepare_formula(formula, True)
3430 col_data["formula"] = formula
3431 # We write the formulas below after the table data.
3433 # Handle the function for the total row.
3434 if user_data.get("total_function"):
3435 function = user_data["total_function"]
3436 if function == "count_nums":
3437 function = "countNums"
3438 if function == "std_dev":
3439 function = "stdDev"
3441 subtotals = set(
3442 [
3443 "average",
3444 "countNums",
3445 "count",
3446 "max",
3447 "min",
3448 "stdDev",
3449 "sum",
3450 "var",
3451 ]
3452 )
3454 if function in subtotals:
3455 formula = self._table_function_to_formula(
3456 function, col_data["name"]
3457 )
3458 else:
3459 formula = self._prepare_formula(function, True)
3460 col_data["custom_total"] = formula
3461 function = "custom"
3463 col_data["total_function"] = function
3465 value = user_data.get("total_value", 0)
3467 self._write_formula(last_row, col_num, formula, xformat, value)
3469 elif user_data.get("total_string"):
3470 # Total label only (not a function).
3471 total_string = user_data["total_string"]
3472 col_data["total_string"] = total_string
3474 self._write_string(
3475 last_row, col_num, total_string, user_data.get("format")
3476 )
3478 # Get the dxf format index.
3479 if xformat is not None:
3480 col_data["format"] = xformat._get_dxf_index()
3482 # Store the column format for writing the cell data.
3483 # It doesn't matter if it is undefined.
3484 col_formats[col_id - 1] = xformat
3486 # Store the column data.
3487 table["columns"].append(col_data)
3489 # Write the column headers to the worksheet.
3490 if options["header_row"]:
3491 self._write_string(
3492 first_row, col_num, col_data["name"], col_data["name_format"]
3493 )
3495 col_id += 1
3497 # Write the cell data if supplied.
3498 if "data" in options:
3499 data = options["data"]
3501 i = 0 # For indexing the row data.
3502 for row in range(first_data_row, last_data_row + 1):
3503 j = 0 # For indexing the col data.
3504 for col in range(first_col, last_col + 1):
3505 if i < len(data) and j < len(data[i]):
3506 token = data[i][j]
3507 if j in col_formats:
3508 self._write(row, col, token, col_formats[j])
3509 else:
3510 self._write(row, col, token, None)
3511 j += 1
3512 i += 1
3514 # Write any columns formulas after the user supplied table data to
3515 # overwrite it if required.
3516 for col_id, col_num in enumerate(range(first_col, last_col + 1)):
3517 column_data = table["columns"][col_id]
3518 if column_data and column_data["formula"]:
3519 formula_format = col_formats.get(col_id)
3520 formula = column_data["formula"]
3522 for row in range(first_data_row, last_data_row + 1):
3523 self._write_formula(row, col_num, formula, formula_format)
3525 # Store the table data.
3526 self.tables.append(table)
3528 # Store the filter cell positions for use in the autofit calculation.
3529 if options["autofilter"]:
3530 for col in range(first_col, last_col + 1):
3531 # Check that the table autofilter doesn't overlap a worksheet filter.
3532 if self.filter_cells.get((first_row, col)):
3533 filter_type, filter_range = self.filter_cells.get((first_row, col))
3534 if filter_type == "worksheet":
3535 raise OverlappingRange(
3536 f"Table autofilter range '{cell_range}' overlaps previous "
3537 f"Worksheet autofilter range '{filter_range}'."
3538 )
3540 self.filter_cells[(first_row, col)] = ("table", cell_range)
3542 return 0
3544 @convert_cell_args
3545 def add_sparkline(self, row, col, options=None):
3546 """
3547 Add sparklines to the worksheet.
3549 Args:
3550 row: The cell row (zero indexed).
3551 col: The cell column (zero indexed).
3552 options: Sparkline formatting options.
3554 Returns:
3555 0: Success.
3556 -1: Row or column is out of worksheet bounds.
3557 -2: Incorrect parameter or option.
3559 """
3561 # Check that row and col are valid without storing the values.
3562 if self._check_dimensions(row, col, True, True):
3563 return -1
3565 sparkline = {"locations": [xl_rowcol_to_cell(row, col)]}
3567 if options is None:
3568 options = {}
3570 # Valid input parameters.
3571 valid_parameters = {
3572 "location",
3573 "range",
3574 "type",
3575 "high_point",
3576 "low_point",
3577 "negative_points",
3578 "first_point",
3579 "last_point",
3580 "markers",
3581 "style",
3582 "series_color",
3583 "negative_color",
3584 "markers_color",
3585 "first_color",
3586 "last_color",
3587 "high_color",
3588 "low_color",
3589 "max",
3590 "min",
3591 "axis",
3592 "reverse",
3593 "empty_cells",
3594 "show_hidden",
3595 "plot_hidden",
3596 "date_axis",
3597 "weight",
3598 }
3600 # Check for valid input parameters.
3601 for param_key in options.keys():
3602 if param_key not in valid_parameters:
3603 warn(f"Unknown parameter '{param_key}' in add_sparkline()")
3604 return -1
3606 # 'range' is a required parameter.
3607 if "range" not in options:
3608 warn("Parameter 'range' is required in add_sparkline()")
3609 return -2
3611 # Handle the sparkline type.
3612 spark_type = options.get("type", "line")
3614 if spark_type not in ("line", "column", "win_loss"):
3615 warn(
3616 "Parameter 'type' must be 'line', 'column' "
3617 "or 'win_loss' in add_sparkline()"
3618 )
3619 return -2
3621 if spark_type == "win_loss":
3622 spark_type = "stacked"
3623 sparkline["type"] = spark_type
3625 # We handle single location/range values or list of values.
3626 if "location" in options:
3627 if isinstance(options["location"], list):
3628 sparkline["locations"] = options["location"]
3629 else:
3630 sparkline["locations"] = [options["location"]]
3632 if isinstance(options["range"], list):
3633 sparkline["ranges"] = options["range"]
3634 else:
3635 sparkline["ranges"] = [options["range"]]
3637 range_count = len(sparkline["ranges"])
3638 location_count = len(sparkline["locations"])
3640 # The ranges and locations must match.
3641 if range_count != location_count:
3642 warn(
3643 "Must have the same number of location and range "
3644 "parameters in add_sparkline()"
3645 )
3646 return -2
3648 # Store the count.
3649 sparkline["count"] = len(sparkline["locations"])
3651 # Get the worksheet name for the range conversion below.
3652 sheetname = quote_sheetname(self.name)
3654 # Cleanup the input ranges.
3655 new_ranges = []
3656 for spark_range in sparkline["ranges"]:
3657 # Remove the absolute reference $ symbols.
3658 spark_range = spark_range.replace("$", "")
3660 # Remove the = from formula.
3661 spark_range = spark_range.lstrip("=")
3663 # Convert a simple range into a full Sheet1!A1:D1 range.
3664 if "!" not in spark_range:
3665 spark_range = sheetname + "!" + spark_range
3667 new_ranges.append(spark_range)
3669 sparkline["ranges"] = new_ranges
3671 # Cleanup the input locations.
3672 new_locations = []
3673 for location in sparkline["locations"]:
3674 location = location.replace("$", "")
3675 new_locations.append(location)
3677 sparkline["locations"] = new_locations
3679 # Map options.
3680 sparkline["high"] = options.get("high_point")
3681 sparkline["low"] = options.get("low_point")
3682 sparkline["negative"] = options.get("negative_points")
3683 sparkline["first"] = options.get("first_point")
3684 sparkline["last"] = options.get("last_point")
3685 sparkline["markers"] = options.get("markers")
3686 sparkline["min"] = options.get("min")
3687 sparkline["max"] = options.get("max")
3688 sparkline["axis"] = options.get("axis")
3689 sparkline["reverse"] = options.get("reverse")
3690 sparkline["hidden"] = options.get("show_hidden")
3691 sparkline["weight"] = options.get("weight")
3693 # Map empty cells options.
3694 empty = options.get("empty_cells", "")
3696 if empty == "zero":
3697 sparkline["empty"] = 0
3698 elif empty == "connect":
3699 sparkline["empty"] = "span"
3700 else:
3701 sparkline["empty"] = "gap"
3703 # Map the date axis range.
3704 date_range = options.get("date_axis")
3706 if date_range and "!" not in date_range:
3707 date_range = sheetname + "!" + date_range
3709 sparkline["date_axis"] = date_range
3711 # Set the sparkline styles.
3712 style_id = options.get("style", 0)
3713 style = _get_sparkline_style(style_id)
3715 sparkline["series_color"] = style["series"]
3716 sparkline["negative_color"] = style["negative"]
3717 sparkline["markers_color"] = style["markers"]
3718 sparkline["first_color"] = style["first"]
3719 sparkline["last_color"] = style["last"]
3720 sparkline["high_color"] = style["high"]
3721 sparkline["low_color"] = style["low"]
3723 # Override the style colors with user defined colors.
3724 self._set_spark_color(sparkline, options, "series_color")
3725 self._set_spark_color(sparkline, options, "negative_color")
3726 self._set_spark_color(sparkline, options, "markers_color")
3727 self._set_spark_color(sparkline, options, "first_color")
3728 self._set_spark_color(sparkline, options, "last_color")
3729 self._set_spark_color(sparkline, options, "high_color")
3730 self._set_spark_color(sparkline, options, "low_color")
3732 self.sparklines.append(sparkline)
3734 return 0
3736 @convert_range_args
3737 def set_selection(self, first_row, first_col, last_row, last_col):
3738 """
3739 Set the selected cell or cells in a worksheet
3741 Args:
3742 first_row: The first row of the cell range. (zero indexed).
3743 first_col: The first column of the cell range.
3744 last_row: The last row of the cell range. (zero indexed).
3745 last_col: The last column of the cell range.
3747 Returns:
3748 0: Nothing.
3749 """
3750 pane = None
3752 # Range selection. Do this before swapping max/min to allow the
3753 # selection direction to be reversed.
3754 active_cell = xl_rowcol_to_cell(first_row, first_col)
3756 # Swap last row/col for first row/col if necessary
3757 if first_row > last_row:
3758 (first_row, last_row) = (last_row, first_row)
3760 if first_col > last_col:
3761 (first_col, last_col) = (last_col, first_col)
3763 sqref = xl_range(first_row, first_col, last_row, last_col)
3765 # Selection isn't set for cell A1.
3766 if sqref == "A1":
3767 return
3769 self.selections = [[pane, active_cell, sqref]]
3771 @convert_cell_args
3772 def set_top_left_cell(self, row=0, col=0):
3773 """
3774 Set the first visible cell at the top left of a worksheet.
3776 Args:
3777 row: The cell row (zero indexed).
3778 col: The cell column (zero indexed).
3780 Returns:
3781 0: Nothing.
3782 """
3784 if row == 0 and col == 0:
3785 return
3787 self.top_left_cell = xl_rowcol_to_cell(row, col)
3789 def outline_settings(
3790 self, visible=1, symbols_below=1, symbols_right=1, auto_style=0
3791 ):
3792 """
3793 Control outline settings.
3795 Args:
3796 visible: Outlines are visible. Optional, defaults to True.
3797 symbols_below: Show row outline symbols below the outline bar.
3798 Optional, defaults to True.
3799 symbols_right: Show column outline symbols to the right of the
3800 outline bar. Optional, defaults to True.
3801 auto_style: Use Automatic style. Optional, defaults to False.
3803 Returns:
3804 0: Nothing.
3805 """
3806 self.outline_on = visible
3807 self.outline_below = symbols_below
3808 self.outline_right = symbols_right
3809 self.outline_style = auto_style
3811 self.outline_changed = True
3813 @convert_cell_args
3814 def freeze_panes(self, row, col, top_row=None, left_col=None, pane_type=0):
3815 """
3816 Create worksheet panes and mark them as frozen.
3818 Args:
3819 row: The cell row (zero indexed).
3820 col: The cell column (zero indexed).
3821 top_row: Topmost visible row in scrolling region of pane.
3822 left_col: Leftmost visible row in scrolling region of pane.
3824 Returns:
3825 0: Nothing.
3827 """
3828 if top_row is None:
3829 top_row = row
3831 if left_col is None:
3832 left_col = col
3834 self.panes = [row, col, top_row, left_col, pane_type]
3836 @convert_cell_args
3837 def split_panes(self, x, y, top_row=None, left_col=None):
3838 """
3839 Create worksheet panes and mark them as split.
3841 Args:
3842 x: The position for the vertical split.
3843 y: The position for the horizontal split.
3844 top_row: Topmost visible row in scrolling region of pane.
3845 left_col: Leftmost visible row in scrolling region of pane.
3847 Returns:
3848 0: Nothing.
3850 """
3851 # Same as freeze panes with a different pane type.
3852 self.freeze_panes(x, y, top_row, left_col, 2)
3854 def set_zoom(self, zoom=100):
3855 """
3856 Set the worksheet zoom factor.
3858 Args:
3859 zoom: Scale factor: 10 <= zoom <= 400.
3861 Returns:
3862 Nothing.
3864 """
3865 # Ensure the zoom scale is in Excel's range.
3866 if zoom < 10 or zoom > 400:
3867 warn(f"Zoom factor '{zoom}' outside range: 10 <= zoom <= 400")
3868 zoom = 100
3870 self.zoom = int(zoom)
3872 def right_to_left(self):
3873 """
3874 Display the worksheet right to left for some versions of Excel.
3876 Args:
3877 None.
3879 Returns:
3880 Nothing.
3882 """
3883 self.is_right_to_left = True
3885 def hide_zero(self):
3886 """
3887 Hide zero values in worksheet cells.
3889 Args:
3890 None.
3892 Returns:
3893 Nothing.
3895 """
3896 self.show_zeros = 0
3898 def set_tab_color(self, color):
3899 """
3900 Set the color of the worksheet tab.
3902 Args:
3903 color: A #RGB color index.
3905 Returns:
3906 Nothing.
3908 """
3909 self.tab_color = Color._from_value(color)
3911 def protect(self, password="", options=None):
3912 """
3913 Set the password and protection options of the worksheet.
3915 Args:
3916 password: An optional password string.
3917 options: A dictionary of worksheet objects to protect.
3919 Returns:
3920 Nothing.
3922 """
3923 if password != "":
3924 password = self._encode_password(password)
3926 if not options:
3927 options = {}
3929 # Default values for objects that can be protected.
3930 defaults = {
3931 "sheet": True,
3932 "content": False,
3933 "objects": False,
3934 "scenarios": False,
3935 "format_cells": False,
3936 "format_columns": False,
3937 "format_rows": False,
3938 "insert_columns": False,
3939 "insert_rows": False,
3940 "insert_hyperlinks": False,
3941 "delete_columns": False,
3942 "delete_rows": False,
3943 "select_locked_cells": True,
3944 "sort": False,
3945 "autofilter": False,
3946 "pivot_tables": False,
3947 "select_unlocked_cells": True,
3948 }
3950 # Overwrite the defaults with user specified values.
3951 for key in options.keys():
3952 if key in defaults:
3953 defaults[key] = options[key]
3954 else:
3955 warn(f"Unknown protection object: '{key}'")
3957 # Set the password after the user defined values.
3958 defaults["password"] = password
3960 self.protect_options = defaults
3962 def unprotect_range(self, cell_range, range_name=None, password=None):
3963 """
3964 Unprotect ranges within a protected worksheet.
3966 Args:
3967 cell_range: The cell or cell range to unprotect.
3968 range_name: An optional name for the range.
3969 password: An optional password string. (undocumented)
3971 Returns:
3972 0: Success.
3973 -1: Parameter error.
3975 """
3976 if cell_range is None:
3977 warn("Cell range must be specified in unprotect_range()")
3978 return -1
3980 # Sanitize the cell range.
3981 cell_range = cell_range.lstrip("=")
3982 cell_range = cell_range.replace("$", "")
3984 self.num_protected_ranges += 1
3986 if range_name is None:
3987 range_name = "Range" + str(self.num_protected_ranges)
3989 if password:
3990 password = self._encode_password(password)
3992 self.protected_ranges.append((cell_range, range_name, password))
3994 return 0
3996 @convert_cell_args
3997 def insert_button(self, row, col, options=None):
3998 """
3999 Insert a button form object into the worksheet.
4001 Args:
4002 row: The cell row (zero indexed).
4003 col: The cell column (zero indexed).
4004 options: Button formatting options.
4006 Returns:
4007 0: Success.
4008 -1: Row or column is out of worksheet bounds.
4010 """
4011 # Check insert (row, col) without storing.
4012 if self._check_dimensions(row, col, True, True):
4013 warn(f"Cannot insert button at ({row}, {col}).")
4014 return -1
4016 if options is None:
4017 options = {}
4019 # Create a new button object.
4020 height = self.default_row_pixels
4021 width = self.default_col_pixels
4022 button_number = 1 + len(self.buttons_list)
4024 button = ButtonType(row, col, height, width, button_number, options)
4026 self.buttons_list.append(button)
4028 self.has_vml = True
4030 return 0
4032 @convert_cell_args
4033 def insert_checkbox(self, row, col, boolean, cell_format=None):
4034 """
4035 Insert a boolean checkbox in a worksheet cell.
4037 Args:
4038 row: The cell row (zero indexed).
4039 col: The cell column (zero indexed).
4040 boolean: The boolean value to display as a checkbox.
4041 cell_format: Cell Format object. (optional)
4043 Returns:
4044 0: Success.
4045 -1: Row or column is out of worksheet bounds.
4047 """
4048 # Ensure that the checkbox property is set in the user defined format.
4049 if cell_format and not cell_format.checkbox:
4050 # This needs to be fixed with a clone.
4051 cell_format.set_checkbox()
4053 # If no format is supplied create and/or use the default checkbox format.
4054 if not cell_format:
4055 if not self.default_checkbox_format:
4056 self.default_checkbox_format = self.workbook_add_format()
4057 self.default_checkbox_format.set_checkbox()
4059 cell_format = self.default_checkbox_format
4061 return self._write_boolean(row, col, boolean, cell_format)
4063 ###########################################################################
4064 #
4065 # Public API. Page Setup methods.
4066 #
4067 ###########################################################################
4068 def set_landscape(self):
4069 """
4070 Set the page orientation as landscape.
4072 Args:
4073 None.
4075 Returns:
4076 Nothing.
4078 """
4079 self.orientation = 0
4080 self.page_setup_changed = True
4082 def set_portrait(self):
4083 """
4084 Set the page orientation as portrait.
4086 Args:
4087 None.
4089 Returns:
4090 Nothing.
4092 """
4093 self.orientation = 1
4094 self.page_setup_changed = True
4096 def set_page_view(self, view=1):
4097 """
4098 Set the page view mode.
4100 Args:
4101 0: Normal view mode
4102 1: Page view mode (the default)
4103 2: Page break view mode
4105 Returns:
4106 Nothing.
4108 """
4109 self.page_view = view
4111 def set_pagebreak_view(self):
4112 """
4113 Set the page view mode.
4115 Args:
4116 None.
4118 Returns:
4119 Nothing.
4121 """
4122 self.page_view = 2
4124 def set_paper(self, paper_size):
4125 """
4126 Set the paper type. US Letter = 1, A4 = 9.
4128 Args:
4129 paper_size: Paper index.
4131 Returns:
4132 Nothing.
4134 """
4135 if paper_size:
4136 self.paper_size = paper_size
4137 self.page_setup_changed = True
4139 def center_horizontally(self):
4140 """
4141 Center the page horizontally.
4143 Args:
4144 None.
4146 Returns:
4147 Nothing.
4149 """
4150 self.print_options_changed = True
4151 self.hcenter = 1
4153 def center_vertically(self):
4154 """
4155 Center the page vertically.
4157 Args:
4158 None.
4160 Returns:
4161 Nothing.
4163 """
4164 self.print_options_changed = True
4165 self.vcenter = 1
4167 def set_margins(self, left=0.7, right=0.7, top=0.75, bottom=0.75):
4168 """
4169 Set all the page margins in inches.
4171 Args:
4172 left: Left margin.
4173 right: Right margin.
4174 top: Top margin.
4175 bottom: Bottom margin.
4177 Returns:
4178 Nothing.
4180 """
4181 self.margin_left = left
4182 self.margin_right = right
4183 self.margin_top = top
4184 self.margin_bottom = bottom
4186 def set_header(self, header="", options=None, margin=None):
4187 """
4188 Set the page header caption and optional margin.
4190 Args:
4191 header: Header string.
4192 margin: Header margin.
4193 options: Header options, mainly for images.
4195 Returns:
4196 Nothing.
4198 """
4199 header_orig = header
4200 header = header.replace("&[Picture]", "&G")
4202 if len(header) > 255:
4203 warn("Header string cannot be longer than Excel's limit of 255 characters")
4204 return
4206 if options is not None:
4207 # For backward compatibility allow options to be the margin.
4208 if not isinstance(options, dict):
4209 options = {"margin": options}
4210 else:
4211 options = {}
4213 # Copy the user defined options so they aren't modified.
4214 options = options.copy()
4216 # For backward compatibility.
4217 if margin is not None:
4218 options["margin"] = margin
4220 # Reset the list in case the function is called more than once.
4221 self.header_images = []
4223 if options.get("image_left"):
4224 options["image_data"] = options.get("image_data_left")
4225 image = self._image_from_source(options.get("image_left"), options)
4226 image._header_position = "LH"
4227 self.header_images.append(image)
4229 if options.get("image_center"):
4230 options["image_data"] = options.get("image_data_center")
4231 image = self._image_from_source(options.get("image_center"), options)
4232 image._header_position = "CH"
4233 self.header_images.append(image)
4235 if options.get("image_right"):
4236 options["image_data"] = options.get("image_data_right")
4237 image = self._image_from_source(options.get("image_right"), options)
4238 image._header_position = "RH"
4239 self.header_images.append(image)
4241 placeholder_count = header.count("&G")
4242 image_count = len(self.header_images)
4244 if placeholder_count != image_count:
4245 warn(
4246 f"Number of footer images '{image_count}' doesn't match placeholder "
4247 f"count '{placeholder_count}' in string: {header_orig}"
4248 )
4249 self.header_images = []
4250 return
4252 if "align_with_margins" in options:
4253 self.header_footer_aligns = options["align_with_margins"]
4255 if "scale_with_doc" in options:
4256 self.header_footer_scales = options["scale_with_doc"]
4258 self.header = header
4259 self.margin_header = options.get("margin", 0.3)
4260 self.header_footer_changed = True
4262 if image_count:
4263 self.has_header_vml = True
4265 def set_footer(self, footer="", options=None, margin=None):
4266 """
4267 Set the page footer caption and optional margin.
4269 Args:
4270 footer: Footer string.
4271 margin: Footer margin.
4272 options: Footer options, mainly for images.
4274 Returns:
4275 Nothing.
4277 """
4278 footer_orig = footer
4279 footer = footer.replace("&[Picture]", "&G")
4281 if len(footer) > 255:
4282 warn("Footer string cannot be longer than Excel's limit of 255 characters")
4283 return
4285 if options is not None:
4286 # For backward compatibility allow options to be the margin.
4287 if not isinstance(options, dict):
4288 options = {"margin": options}
4289 else:
4290 options = {}
4292 # Copy the user defined options so they aren't modified.
4293 options = options.copy()
4295 # For backward compatibility.
4296 if margin is not None:
4297 options["margin"] = margin
4299 # Reset the list in case the function is called more than once.
4300 self.footer_images = []
4302 if options.get("image_left"):
4303 options["image_data"] = options.get("image_data_left")
4304 image = self._image_from_source(options.get("image_left"), options)
4305 image._header_position = "LF"
4306 self.footer_images.append(image)
4308 if options.get("image_center"):
4309 options["image_data"] = options.get("image_data_center")
4310 image = self._image_from_source(options.get("image_center"), options)
4311 image._header_position = "CF"
4312 self.footer_images.append(image)
4314 if options.get("image_right"):
4315 options["image_data"] = options.get("image_data_right")
4316 image = self._image_from_source(options.get("image_right"), options)
4317 image._header_position = "RF"
4318 self.footer_images.append(image)
4320 placeholder_count = footer.count("&G")
4321 image_count = len(self.footer_images)
4323 if placeholder_count != image_count:
4324 warn(
4325 f"Number of footer images '{image_count}' doesn't match placeholder "
4326 f"count '{placeholder_count}' in string: {footer_orig}"
4327 )
4328 self.footer_images = []
4329 return
4331 if "align_with_margins" in options:
4332 self.header_footer_aligns = options["align_with_margins"]
4334 if "scale_with_doc" in options:
4335 self.header_footer_scales = options["scale_with_doc"]
4337 self.footer = footer
4338 self.margin_footer = options.get("margin", 0.3)
4339 self.header_footer_changed = True
4341 if image_count:
4342 self.has_header_vml = True
4344 def repeat_rows(self, first_row, last_row=None):
4345 """
4346 Set the rows to repeat at the top of each printed page.
4348 Args:
4349 first_row: Start row for range.
4350 last_row: End row for range.
4352 Returns:
4353 Nothing.
4355 """
4356 if last_row is None:
4357 last_row = first_row
4359 # Convert rows to 1 based.
4360 first_row += 1
4361 last_row += 1
4363 # Create the row range area like: $1:$2.
4364 area = f"${first_row}:${last_row}"
4366 # Build up the print titles area "Sheet1!$1:$2"
4367 sheetname = quote_sheetname(self.name)
4368 self.repeat_row_range = sheetname + "!" + area
4370 @convert_column_args
4371 def repeat_columns(self, first_col, last_col=None):
4372 """
4373 Set the columns to repeat at the left hand side of each printed page.
4375 Args:
4376 first_col: Start column for range.
4377 last_col: End column for range.
4379 Returns:
4380 Nothing.
4382 """
4383 if last_col is None:
4384 last_col = first_col
4386 # Convert to A notation.
4387 first_col = xl_col_to_name(first_col, 1)
4388 last_col = xl_col_to_name(last_col, 1)
4390 # Create a column range like $C:$D.
4391 area = first_col + ":" + last_col
4393 # Build up the print area range "=Sheet2!$C:$D"
4394 sheetname = quote_sheetname(self.name)
4395 self.repeat_col_range = sheetname + "!" + area
4397 def hide_gridlines(self, option=1):
4398 """
4399 Set the option to hide gridlines on the screen and the printed page.
4401 Args:
4402 option: 0 : Don't hide gridlines
4403 1 : Hide printed gridlines only
4404 2 : Hide screen and printed gridlines
4406 Returns:
4407 Nothing.
4409 """
4410 if option == 0:
4411 self.print_gridlines = 1
4412 self.screen_gridlines = 1
4413 self.print_options_changed = True
4414 elif option == 1:
4415 self.print_gridlines = 0
4416 self.screen_gridlines = 1
4417 else:
4418 self.print_gridlines = 0
4419 self.screen_gridlines = 0
4421 def print_row_col_headers(self):
4422 """
4423 Set the option to print the row and column headers on the printed page.
4425 Args:
4426 None.
4428 Returns:
4429 Nothing.
4431 """
4432 self.print_headers = True
4433 self.print_options_changed = True
4435 def hide_row_col_headers(self):
4436 """
4437 Set the option to hide the row and column headers on the worksheet.
4439 Args:
4440 None.
4442 Returns:
4443 Nothing.
4445 """
4446 self.row_col_headers = True
4448 @convert_range_args
4449 def print_area(self, first_row, first_col, last_row, last_col):
4450 """
4451 Set the print area in the current worksheet.
4453 Args:
4454 first_row: The first row of the cell range. (zero indexed).
4455 first_col: The first column of the cell range.
4456 last_row: The last row of the cell range. (zero indexed).
4457 last_col: The last column of the cell range.
4459 Returns:
4460 0: Success.
4461 -1: Row or column is out of worksheet bounds.
4463 """
4464 # Set the print area in the current worksheet.
4466 # Ignore max print area since it is the same as no area for Excel.
4467 if (
4468 first_row == 0
4469 and first_col == 0
4470 and last_row == self.xls_rowmax - 1
4471 and last_col == self.xls_colmax - 1
4472 ):
4473 return -1
4475 # Build up the print area range "Sheet1!$A$1:$C$13".
4476 area = self._convert_name_area(first_row, first_col, last_row, last_col)
4477 self.print_area_range = area
4479 return 0
4481 def print_across(self):
4482 """
4483 Set the order in which pages are printed.
4485 Args:
4486 None.
4488 Returns:
4489 Nothing.
4491 """
4492 self.page_order = 1
4493 self.page_setup_changed = True
4495 def fit_to_pages(self, width, height):
4496 """
4497 Fit the printed area to a specific number of pages both vertically and
4498 horizontally.
4500 Args:
4501 width: Number of pages horizontally.
4502 height: Number of pages vertically.
4504 Returns:
4505 Nothing.
4507 """
4508 self.fit_page = 1
4509 self.fit_width = width
4510 self.fit_height = height
4511 self.page_setup_changed = True
4513 def set_start_page(self, start_page):
4514 """
4515 Set the start page number when printing.
4517 Args:
4518 start_page: Start page number.
4520 Returns:
4521 Nothing.
4523 """
4524 self.page_start = start_page
4526 def set_print_scale(self, scale):
4527 """
4528 Set the scale factor for the printed page.
4530 Args:
4531 scale: Print scale. 10 <= scale <= 400.
4533 Returns:
4534 Nothing.
4536 """
4537 # Confine the scale to Excel's range.
4538 if scale < 10 or scale > 400:
4539 warn(f"Print scale '{scale}' outside range: 10 <= scale <= 400")
4540 return
4542 # Turn off "fit to page" option when print scale is on.
4543 self.fit_page = 0
4545 self.print_scale = int(scale)
4546 self.page_setup_changed = True
4548 def print_black_and_white(self):
4549 """
4550 Set the option to print the worksheet in black and white.
4552 Args:
4553 None.
4555 Returns:
4556 Nothing.
4558 """
4559 self.black_white = True
4560 self.page_setup_changed = True
4562 def set_h_pagebreaks(self, breaks):
4563 """
4564 Set the horizontal page breaks on a worksheet.
4566 Args:
4567 breaks: List of rows where the page breaks should be added.
4569 Returns:
4570 Nothing.
4572 """
4573 self.hbreaks = breaks
4575 def set_v_pagebreaks(self, breaks):
4576 """
4577 Set the horizontal page breaks on a worksheet.
4579 Args:
4580 breaks: List of columns where the page breaks should be added.
4582 Returns:
4583 Nothing.
4585 """
4586 self.vbreaks = breaks
4588 def set_vba_name(self, name=None):
4589 """
4590 Set the VBA name for the worksheet. By default this is the
4591 same as the sheet name: i.e., Sheet1 etc.
4593 Args:
4594 name: The VBA name for the worksheet.
4596 Returns:
4597 Nothing.
4599 """
4600 if name is not None:
4601 self.vba_codename = name
4602 else:
4603 self.vba_codename = "Sheet" + str(self.index + 1)
4605 def ignore_errors(self, options=None):
4606 """
4607 Ignore various Excel errors/warnings in a worksheet for user defined
4608 ranges.
4610 Args:
4611 options: A dict of ignore errors keys with cell range values.
4613 Returns:
4614 0: Success.
4615 -1: Incorrect parameter or option.
4617 """
4618 if options is None:
4619 return -1
4621 # Copy the user defined options so they aren't modified.
4622 options = options.copy()
4624 # Valid input parameters.
4625 valid_parameters = {
4626 "number_stored_as_text",
4627 "eval_error",
4628 "formula_differs",
4629 "formula_range",
4630 "formula_unlocked",
4631 "empty_cell_reference",
4632 "list_data_validation",
4633 "calculated_column",
4634 "two_digit_text_year",
4635 }
4637 # Check for valid input parameters.
4638 for param_key in options.keys():
4639 if param_key not in valid_parameters:
4640 warn(f"Unknown parameter '{param_key}' in ignore_errors()")
4641 return -1
4643 self.ignored_errors = options
4645 return 0
4647 ###########################################################################
4648 #
4649 # Private API.
4650 #
4651 ###########################################################################
4652 def _initialize(self, init_data):
4653 self.name = init_data["name"]
4654 self.index = init_data["index"]
4655 self.str_table = init_data["str_table"]
4656 self.worksheet_meta = init_data["worksheet_meta"]
4657 self.constant_memory = init_data["constant_memory"]
4658 self.tmpdir = init_data["tmpdir"]
4659 self.date_1904 = init_data["date_1904"]
4660 self.strings_to_numbers = init_data["strings_to_numbers"]
4661 self.strings_to_formulas = init_data["strings_to_formulas"]
4662 self.strings_to_urls = init_data["strings_to_urls"]
4663 self.nan_inf_to_errors = init_data["nan_inf_to_errors"]
4664 self.default_date_format = init_data["default_date_format"]
4665 self.default_url_format = init_data["default_url_format"]
4666 self.workbook_add_format = init_data["workbook_add_format"]
4667 self.excel2003_style = init_data["excel2003_style"]
4668 self.remove_timezone = init_data["remove_timezone"]
4669 self.max_url_length = init_data["max_url_length"]
4670 self.use_future_functions = init_data["use_future_functions"]
4671 self.embedded_images = init_data["embedded_images"]
4673 if self.excel2003_style:
4674 self.original_row_height = 12.75
4675 self.default_row_height = 12.75
4676 self.default_row_pixels = 17
4677 self.margin_left = 0.75
4678 self.margin_right = 0.75
4679 self.margin_top = 1
4680 self.margin_bottom = 1
4681 self.margin_header = 0.5
4682 self.margin_footer = 0.5
4683 self.header_footer_aligns = False
4685 # Open a temp filehandle to store row data in constant_memory mode.
4686 if self.constant_memory:
4687 # This is sub-optimal but we need to create a temp file
4688 # with utf8 encoding in Python < 3.
4689 (fd, filename) = tempfile.mkstemp(dir=self.tmpdir)
4690 os.close(fd)
4691 self.row_data_filename = filename
4692 # pylint: disable=consider-using-with
4693 self.row_data_fh = open(filename, mode="w+", encoding="utf-8")
4695 # Set as the worksheet filehandle until the file is assembled.
4696 self.fh = self.row_data_fh
4698 def _assemble_xml_file(self):
4699 # Assemble and write the XML file.
4701 # Write the XML declaration.
4702 self._xml_declaration()
4704 # Write the root worksheet element.
4705 self._write_worksheet()
4707 # Write the worksheet properties.
4708 self._write_sheet_pr()
4710 # Write the worksheet dimensions.
4711 self._write_dimension()
4713 # Write the sheet view properties.
4714 self._write_sheet_views()
4716 # Write the sheet format properties.
4717 self._write_sheet_format_pr()
4719 # Write the sheet column info.
4720 self._write_cols()
4722 # Write the worksheet data such as rows columns and cells.
4723 if not self.constant_memory:
4724 self._write_sheet_data()
4725 else:
4726 self._write_optimized_sheet_data()
4728 # Write the sheetProtection element.
4729 self._write_sheet_protection()
4731 # Write the protectedRanges element.
4732 self._write_protected_ranges()
4734 # Write the phoneticPr element.
4735 if self.excel2003_style:
4736 self._write_phonetic_pr()
4738 # Write the autoFilter element.
4739 self._write_auto_filter()
4741 # Write the mergeCells element.
4742 self._write_merge_cells()
4744 # Write the conditional formats.
4745 self._write_conditional_formats()
4747 # Write the dataValidations element.
4748 self._write_data_validations()
4750 # Write the hyperlink element.
4751 self._write_hyperlinks()
4753 # Write the printOptions element.
4754 self._write_print_options()
4756 # Write the worksheet page_margins.
4757 self._write_page_margins()
4759 # Write the worksheet page setup.
4760 self._write_page_setup()
4762 # Write the headerFooter element.
4763 self._write_header_footer()
4765 # Write the rowBreaks element.
4766 self._write_row_breaks()
4768 # Write the colBreaks element.
4769 self._write_col_breaks()
4771 # Write the ignoredErrors element.
4772 self._write_ignored_errors()
4774 # Write the drawing element.
4775 self._write_drawings()
4777 # Write the legacyDrawing element.
4778 self._write_legacy_drawing()
4780 # Write the legacyDrawingHF element.
4781 self._write_legacy_drawing_hf()
4783 # Write the picture element, for the background.
4784 self._write_picture()
4786 # Write the tableParts element.
4787 self._write_table_parts()
4789 # Write the extLst elements.
4790 self._write_ext_list()
4792 # Close the worksheet tag.
4793 self._xml_end_tag("worksheet")
4795 # Close the file.
4796 self._xml_close()
4798 def _check_dimensions(self, row, col, ignore_row=False, ignore_col=False):
4799 # Check that row and col are valid and store the max and min
4800 # values for use in other methods/elements. The ignore_row /
4801 # ignore_col flags is used to indicate that we wish to perform
4802 # the dimension check without storing the value. The ignore
4803 # flags are use by set_row() and data_validate.
4805 # Check that the row/col are within the worksheet bounds.
4806 if row < 0 or col < 0:
4807 return -1
4808 if row >= self.xls_rowmax or col >= self.xls_colmax:
4809 return -1
4811 # In constant_memory mode we don't change dimensions for rows
4812 # that are already written.
4813 if not ignore_row and not ignore_col and self.constant_memory:
4814 if row < self.previous_row:
4815 return -2
4817 if not ignore_row:
4818 if self.dim_rowmin is None or row < self.dim_rowmin:
4819 self.dim_rowmin = row
4820 if self.dim_rowmax is None or row > self.dim_rowmax:
4821 self.dim_rowmax = row
4823 if not ignore_col:
4824 if self.dim_colmin is None or col < self.dim_colmin:
4825 self.dim_colmin = col
4826 if self.dim_colmax is None or col > self.dim_colmax:
4827 self.dim_colmax = col
4829 return 0
4831 def _convert_date_time(self, dt_obj):
4832 # Convert a datetime object to an Excel serial date and time.
4833 return _datetime_to_excel_datetime(dt_obj, self.date_1904, self.remove_timezone)
4835 def _convert_name_area(self, row_num_1, col_num_1, row_num_2, col_num_2):
4836 # Convert zero indexed rows and columns to the format required by
4837 # worksheet named ranges, eg, "Sheet1!$A$1:$C$13".
4839 range1 = ""
4840 range2 = ""
4841 area = ""
4842 row_col_only = 0
4844 # Convert to A1 notation.
4845 col_char_1 = xl_col_to_name(col_num_1, 1)
4846 col_char_2 = xl_col_to_name(col_num_2, 1)
4847 row_char_1 = "$" + str(row_num_1 + 1)
4848 row_char_2 = "$" + str(row_num_2 + 1)
4850 # We need to handle special cases that refer to rows or columns only.
4851 if row_num_1 == 0 and row_num_2 == self.xls_rowmax - 1:
4852 range1 = col_char_1
4853 range2 = col_char_2
4854 row_col_only = 1
4855 elif col_num_1 == 0 and col_num_2 == self.xls_colmax - 1:
4856 range1 = row_char_1
4857 range2 = row_char_2
4858 row_col_only = 1
4859 else:
4860 range1 = col_char_1 + row_char_1
4861 range2 = col_char_2 + row_char_2
4863 # A repeated range is only written once (if it isn't a special case).
4864 if range1 == range2 and not row_col_only:
4865 area = range1
4866 else:
4867 area = range1 + ":" + range2
4869 # Build up the print area range "Sheet1!$A$1:$C$13".
4870 sheetname = quote_sheetname(self.name)
4871 area = sheetname + "!" + area
4873 return area
4875 def _sort_pagebreaks(self, breaks):
4876 # This is an internal method used to filter elements of a list of
4877 # pagebreaks used in the _store_hbreak() and _store_vbreak() methods.
4878 # It:
4879 # 1. Removes duplicate entries from the list.
4880 # 2. Sorts the list.
4881 # 3. Removes 0 from the list if present.
4882 if not breaks:
4883 return []
4885 breaks_set = set(breaks)
4887 if 0 in breaks_set:
4888 breaks_set.remove(0)
4890 breaks_list = list(breaks_set)
4891 breaks_list.sort()
4893 # The Excel 2007 specification says that the maximum number of page
4894 # breaks is 1026. However, in practice it is actually 1023.
4895 max_num_breaks = 1023
4896 if len(breaks_list) > max_num_breaks:
4897 breaks_list = breaks_list[:max_num_breaks]
4899 return breaks_list
4901 def _extract_filter_tokens(self, expression):
4902 # Extract the tokens from the filter expression. The tokens are mainly
4903 # non-whitespace groups. The only tricky part is to extract string
4904 # tokens that contain whitespace and/or quoted double quotes (Excel's
4905 # escaped quotes).
4906 #
4907 # Examples: 'x < 2000'
4908 # 'x > 2000 and x < 5000'
4909 # 'x = "foo"'
4910 # 'x = "foo bar"'
4911 # 'x = "foo "" bar"'
4912 #
4913 if not expression:
4914 return []
4916 token_re = re.compile(r'"(?:[^"]|"")*"|\S+')
4917 tokens = token_re.findall(expression)
4919 new_tokens = []
4920 # Remove single leading and trailing quotes and un-escape other quotes.
4921 for token in tokens:
4922 if token.startswith('"'):
4923 token = token[1:]
4925 if token.endswith('"'):
4926 token = token[:-1]
4928 token = token.replace('""', '"')
4930 new_tokens.append(token)
4932 return new_tokens
4934 def _parse_filter_expression(self, expression, tokens):
4935 # Converts the tokens of a possibly conditional expression into 1 or 2
4936 # sub expressions for further parsing.
4937 #
4938 # Examples:
4939 # ('x', '==', 2000) -> exp1
4940 # ('x', '>', 2000, 'and', 'x', '<', 5000) -> exp1 and exp2
4942 if len(tokens) == 7:
4943 # The number of tokens will be either 3 (for 1 expression)
4944 # or 7 (for 2 expressions).
4945 conditional = tokens[3]
4947 if re.match("(and|&&)", conditional):
4948 conditional = 0
4949 elif re.match(r"(or|\|\|)", conditional):
4950 conditional = 1
4951 else:
4952 warn(
4953 f"Token '{conditional}' is not a valid conditional "
4954 f"in filter expression '{expression}'"
4955 )
4957 expression_1 = self._parse_filter_tokens(expression, tokens[0:3])
4958 expression_2 = self._parse_filter_tokens(expression, tokens[4:7])
4959 return expression_1 + [conditional] + expression_2
4961 return self._parse_filter_tokens(expression, tokens)
4963 def _parse_filter_tokens(self, expression, tokens):
4964 # Parse the 3 tokens of a filter expression and return the operator
4965 # and token. The use of numbers instead of operators is a legacy of
4966 # Spreadsheet::WriteExcel.
4967 operators = {
4968 "==": 2,
4969 "=": 2,
4970 "=~": 2,
4971 "eq": 2,
4972 "!=": 5,
4973 "!~": 5,
4974 "ne": 5,
4975 "<>": 5,
4976 "<": 1,
4977 "<=": 3,
4978 ">": 4,
4979 ">=": 6,
4980 }
4982 operator = operators.get(tokens[1], None)
4983 token = tokens[2]
4985 # Special handling of "Top" filter expressions.
4986 if re.match("top|bottom", tokens[0].lower()):
4987 value = int(tokens[1])
4989 if value < 1 or value > 500:
4990 warn(
4991 f"The value '{token}' in expression '{expression}' "
4992 f"must be in the range 1 to 500"
4993 )
4995 token = token.lower()
4997 if token not in ("items", "%"):
4998 warn(
4999 f"The type '{token}' in expression '{expression}' "
5000 f"must be either 'items' or '%%'"
5001 )
5003 if tokens[0].lower() == "top":
5004 operator = 30
5005 else:
5006 operator = 32
5008 if tokens[2] == "%":
5009 operator += 1
5011 token = str(value)
5013 if not operator and tokens[0]:
5014 warn(
5015 f"Token '{token[0]}' is not a valid operator "
5016 f"in filter expression '{expression}'."
5017 )
5019 # Special handling for Blanks/NonBlanks.
5020 if re.match("blanks|nonblanks", token.lower()):
5021 # Only allow Equals or NotEqual in this context.
5022 if operator not in (2, 5):
5023 warn(
5024 f"The operator '{tokens[1]}' in expression '{expression}' "
5025 f"is not valid in relation to Blanks/NonBlanks'."
5026 )
5028 token = token.lower()
5030 # The operator should always be 2 (=) to flag a "simple" equality
5031 # in the binary record. Therefore we convert <> to =.
5032 if token == "blanks":
5033 if operator == 5:
5034 token = " "
5035 else:
5036 if operator == 5:
5037 operator = 2
5038 token = "blanks"
5039 else:
5040 operator = 5
5041 token = " "
5043 # if the string token contains an Excel match character then change the
5044 # operator type to indicate a non "simple" equality.
5045 if operator == 2 and re.search("[*?]", token):
5046 operator = 22
5048 return [operator, token]
5050 def _encode_password(self, password):
5051 # Hash a worksheet password. Based on the algorithm in
5052 # ECMA-376-4:2016, Office Open XML File Formats — Transitional
5053 # Migration Features, Additional attributes for workbookProtection
5054 # element (Part 1, §18.2.29).
5055 digest = 0x0000
5057 for char in password[::-1]:
5058 digest = ((digest >> 14) & 0x01) | ((digest << 1) & 0x7FFF)
5059 digest ^= ord(char)
5061 digest = ((digest >> 14) & 0x01) | ((digest << 1) & 0x7FFF)
5062 digest ^= len(password)
5063 digest ^= 0xCE4B
5065 return f"{digest:X}"
5067 def _image_from_source(self, source, options=None):
5068 # Backward compatibility utility method to convert an input argument to
5069 # an Image object. The source can be a filename, BytesIO stream or
5070 # an existing Image object.
5071 if isinstance(source, Image):
5072 image = source
5073 elif options is not None and options.get("image_data"):
5074 image = Image(options["image_data"])
5075 image.image_name = source
5076 else:
5077 image = Image(source)
5079 return image
5081 def _prepare_image(
5082 self,
5083 image: Image,
5084 image_id: int,
5085 drawing_id: int,
5086 ):
5087 # Set up images/drawings.
5089 # Get the effective image width and height in pixels.
5090 width = image._width * image._x_scale
5091 height = image._height * image._y_scale
5093 # Scale by non 96dpi resolutions.
5094 width *= 96.0 / image._x_dpi
5095 height *= 96.0 / image._y_dpi
5097 dimensions = self._position_object_emus(
5098 image._col,
5099 image._row,
5100 image._x_offset,
5101 image._y_offset,
5102 width,
5103 height,
5104 image._anchor,
5105 )
5107 # Convert from pixels to emus.
5108 width = int(0.5 + (width * 9525))
5109 height = int(0.5 + (height * 9525))
5111 # Create a Drawing obj to use with worksheet unless one already exists.
5112 if not self.drawing:
5113 drawing = Drawing()
5114 drawing.embedded = 1
5115 self.drawing = drawing
5117 self.external_drawing_links.append(
5118 ["/drawing", "../drawings/drawing" + str(drawing_id) + ".xml", None]
5119 )
5120 else:
5121 drawing = self.drawing
5123 drawing_object = DrawingInfo()
5124 drawing_object._drawing_type = DrawingTypes.IMAGE
5125 drawing_object._dimensions = dimensions
5126 drawing_object._description = image.image_name
5127 drawing_object._width = width
5128 drawing_object._height = height
5129 drawing_object._shape = None
5130 drawing_object._anchor = image._anchor
5131 drawing_object._rel_index = 0
5132 drawing_object._decorative = image._decorative
5134 if image.description is not None:
5135 drawing_object._description = image.description
5137 if image._url:
5138 url = image._url
5139 target = url._target()
5140 target_mode = url._target_mode()
5142 if not self.drawing_rels.get(url._link):
5143 self.drawing_links.append(["/hyperlink", target, target_mode])
5145 url._rel_index = self._get_drawing_rel_index(url._link)
5146 drawing_object._url = url
5148 if not self.drawing_rels.get(image._digest):
5149 self.drawing_links.append(
5150 [
5151 "/image",
5152 "../media/image" + str(image_id) + "." + image._image_extension,
5153 ]
5154 )
5156 drawing_object._rel_index = self._get_drawing_rel_index(image._digest)
5157 drawing._add_drawing_object(drawing_object)
5159 def _prepare_shape(self, index, drawing_id):
5160 # Set up shapes/drawings.
5161 (
5162 row,
5163 col,
5164 x_offset,
5165 y_offset,
5166 x_scale,
5167 y_scale,
5168 text,
5169 anchor,
5170 options,
5171 description,
5172 decorative,
5173 ) = self.shapes[index]
5175 width = options.get("width", self.default_col_pixels * 3)
5176 height = options.get("height", self.default_row_pixels * 6)
5178 width *= x_scale
5179 height *= y_scale
5181 dimensions = self._position_object_emus(
5182 col, row, x_offset, y_offset, width, height, anchor
5183 )
5185 # Convert from pixels to emus.
5186 width = int(0.5 + (width * 9525))
5187 height = int(0.5 + (height * 9525))
5189 # Create a Drawing obj to use with worksheet unless one already exists.
5190 if not self.drawing:
5191 drawing = Drawing()
5192 drawing.embedded = 1
5193 self.drawing = drawing
5195 self.external_drawing_links.append(
5196 ["/drawing", "../drawings/drawing" + str(drawing_id) + ".xml", None]
5197 )
5198 else:
5199 drawing = self.drawing
5201 shape = Shape("rect", "TextBox", options)
5202 shape.text = text
5204 drawing_object = DrawingInfo()
5205 drawing_object._drawing_type = DrawingTypes.SHAPE
5206 drawing_object._dimensions = dimensions
5207 drawing_object._width = width
5208 drawing_object._height = height
5209 drawing_object._description = description
5210 drawing_object._shape = shape
5211 drawing_object._anchor = anchor
5212 drawing_object._rel_index = 0
5213 drawing_object._decorative = decorative
5215 url = Url.from_options(options)
5216 if url:
5217 target = url._target()
5218 target_mode = url._target_mode()
5220 if not self.drawing_rels.get(url._link):
5221 self.drawing_links.append(["/hyperlink", target, target_mode])
5223 url._rel_index = self._get_drawing_rel_index(url._link)
5224 drawing_object._url = url
5226 drawing._add_drawing_object(drawing_object)
5228 def _prepare_header_image(self, image_id, image):
5229 # Set up an image without a drawing object for header/footer images.
5231 # Strip the extension from the filename.
5232 image.image_name = re.sub(r"\..*$", "", image.image_name)
5234 if not self.vml_drawing_rels.get(image._digest):
5235 self.vml_drawing_links.append(
5236 [
5237 "/image",
5238 "../media/image" + str(image_id) + "." + image._image_extension,
5239 ]
5240 )
5242 image._ref_id = self._get_vml_drawing_rel_index(image._digest)
5244 self.header_images_list.append(image)
5246 def _prepare_background(self, image_id, image_extension):
5247 # Set up an image without a drawing object for backgrounds.
5248 self.external_background_links.append(
5249 ["/image", "../media/image" + str(image_id) + "." + image_extension]
5250 )
5252 def _prepare_chart(self, index, chart_id, drawing_id):
5253 # Set up chart/drawings.
5254 (
5255 row,
5256 col,
5257 chart,
5258 x_offset,
5259 y_offset,
5260 x_scale,
5261 y_scale,
5262 anchor,
5263 description,
5264 decorative,
5265 ) = self.charts[index]
5267 chart.id = chart_id - 1
5269 # Use user specified dimensions, if any.
5270 width = int(0.5 + (chart.width * x_scale))
5271 height = int(0.5 + (chart.height * y_scale))
5273 dimensions = self._position_object_emus(
5274 col, row, x_offset, y_offset, width, height, anchor
5275 )
5277 # Set the chart name for the embedded object if it has been specified.
5278 name = chart.chart_name
5280 # Create a Drawing obj to use with worksheet unless one already exists.
5281 if not self.drawing:
5282 drawing = Drawing()
5283 drawing.embedded = 1
5284 self.drawing = drawing
5286 self.external_drawing_links.append(
5287 ["/drawing", "../drawings/drawing" + str(drawing_id) + ".xml"]
5288 )
5289 else:
5290 drawing = self.drawing
5292 drawing_object = DrawingInfo()
5293 drawing_object._drawing_type = DrawingTypes.CHART
5294 drawing_object._dimensions = dimensions
5295 drawing_object._width = width
5296 drawing_object._height = height
5297 drawing_object._name = name
5298 drawing_object._shape = None
5299 drawing_object._anchor = anchor
5300 drawing_object._rel_index = self._get_drawing_rel_index()
5301 drawing_object._description = description
5302 drawing_object._decorative = decorative
5304 drawing._add_drawing_object(drawing_object)
5306 self.drawing_links.append(
5307 ["/chart", "../charts/chart" + str(chart_id) + ".xml"]
5308 )
5310 def _position_object_emus(
5311 self, col_start, row_start, x1, y1, width, height, anchor
5312 ):
5313 # Calculate the vertices that define the position of a graphical
5314 # object within the worksheet in EMUs.
5315 #
5316 # The vertices are expressed as English Metric Units (EMUs). There are
5317 # 12,700 EMUs per point. Therefore, 12,700 * 3 /4 = 9,525 EMUs per
5318 # pixel
5319 (
5320 col_start,
5321 row_start,
5322 x1,
5323 y1,
5324 col_end,
5325 row_end,
5326 x2,
5327 y2,
5328 x_abs,
5329 y_abs,
5330 ) = self._position_object_pixels(
5331 col_start, row_start, x1, y1, width, height, anchor
5332 )
5334 # Convert the pixel values to EMUs. See above.
5335 x1 = int(0.5 + 9525 * x1)
5336 y1 = int(0.5 + 9525 * y1)
5337 x2 = int(0.5 + 9525 * x2)
5338 y2 = int(0.5 + 9525 * y2)
5339 x_abs = int(0.5 + 9525 * x_abs)
5340 y_abs = int(0.5 + 9525 * y_abs)
5342 return (col_start, row_start, x1, y1, col_end, row_end, x2, y2, x_abs, y_abs)
5344 # Calculate the vertices that define the position of a graphical object
5345 # within the worksheet in pixels.
5346 #
5347 # +------------+------------+
5348 # | A | B |
5349 # +-----+------------+------------+
5350 # | |(x1,y1) | |
5351 # | 1 |(A1)._______|______ |
5352 # | | | | |
5353 # | | | | |
5354 # +-----+----| OBJECT |-----+
5355 # | | | | |
5356 # | 2 | |______________. |
5357 # | | | (B2)|
5358 # | | | (x2,y2)|
5359 # +---- +------------+------------+
5360 #
5361 # Example of an object that covers some of the area from cell A1 to B2.
5362 #
5363 # Based on the width and height of the object we need to calculate 8 vars:
5364 #
5365 # col_start, row_start, col_end, row_end, x1, y1, x2, y2.
5366 #
5367 # We also calculate the absolute x and y position of the top left vertex of
5368 # the object. This is required for images.
5369 #
5370 # The width and height of the cells that the object occupies can be
5371 # variable and have to be taken into account.
5372 #
5373 # The values of col_start and row_start are passed in from the calling
5374 # function. The values of col_end and row_end are calculated by
5375 # subtracting the width and height of the object from the width and
5376 # height of the underlying cells.
5377 #
5378 def _position_object_pixels(
5379 self, col_start, row_start, x1, y1, width, height, anchor
5380 ):
5381 # col_start # Col containing upper left corner of object.
5382 # x1 # Distance to left side of object.
5383 #
5384 # row_start # Row containing top left corner of object.
5385 # y1 # Distance to top of object.
5386 #
5387 # col_end # Col containing lower right corner of object.
5388 # x2 # Distance to right side of object.
5389 #
5390 # row_end # Row containing bottom right corner of object.
5391 # y2 # Distance to bottom of object.
5392 #
5393 # width # Width of object frame.
5394 # height # Height of object frame.
5395 #
5396 # x_abs # Absolute distance to left side of object.
5397 # y_abs # Absolute distance to top side of object.
5398 x_abs = 0
5399 y_abs = 0
5401 # Adjust start column for negative offsets.
5402 # pylint: disable=chained-comparison
5403 while x1 < 0 and col_start > 0:
5404 x1 += self._size_col(col_start - 1)
5405 col_start -= 1
5407 # Adjust start row for negative offsets.
5408 while y1 < 0 and row_start > 0:
5409 y1 += self._size_row(row_start - 1)
5410 row_start -= 1
5412 # Ensure that the image isn't shifted off the page at top left.
5413 x1 = max(0, x1)
5414 y1 = max(0, y1)
5416 # Calculate the absolute x offset of the top-left vertex.
5417 if self.col_size_changed:
5418 for col_id in range(col_start):
5419 x_abs += self._size_col(col_id)
5420 else:
5421 # Optimization for when the column widths haven't changed.
5422 x_abs += self.default_col_pixels * col_start
5424 x_abs += x1
5426 # Calculate the absolute y offset of the top-left vertex.
5427 if self.row_size_changed:
5428 for row_id in range(row_start):
5429 y_abs += self._size_row(row_id)
5430 else:
5431 # Optimization for when the row heights haven't changed.
5432 y_abs += self.default_row_pixels * row_start
5434 y_abs += y1
5436 # Adjust start column for offsets that are greater than the col width.
5437 while x1 >= self._size_col(col_start, anchor):
5438 x1 -= self._size_col(col_start)
5439 col_start += 1
5441 # Adjust start row for offsets that are greater than the row height.
5442 while y1 >= self._size_row(row_start, anchor):
5443 y1 -= self._size_row(row_start)
5444 row_start += 1
5446 # Initialize end cell to the same as the start cell.
5447 col_end = col_start
5448 row_end = row_start
5450 # Don't offset the image in the cell if the row/col is hidden.
5451 if self._size_col(col_start, anchor) > 0:
5452 width = width + x1
5453 if self._size_row(row_start, anchor) > 0:
5454 height = height + y1
5456 # Subtract the underlying cell widths to find end cell of the object.
5457 while width >= self._size_col(col_end, anchor):
5458 width -= self._size_col(col_end, anchor)
5459 col_end += 1
5461 # Subtract the underlying cell heights to find end cell of the object.
5462 while height >= self._size_row(row_end, anchor):
5463 height -= self._size_row(row_end, anchor)
5464 row_end += 1
5466 # The end vertices are whatever is left from the width and height.
5467 x2 = width
5468 y2 = height
5470 return [col_start, row_start, x1, y1, col_end, row_end, x2, y2, x_abs, y_abs]
5472 def _size_col(self, col, anchor=0):
5473 # Convert the width of a cell from character units to pixels. Excel
5474 # rounds the column width to the nearest pixel. If the width hasn't
5475 # been set by the user we use the default value. A hidden column is
5476 # treated as having a width of zero unless it has the special
5477 # "object_position" of 4 (size with cells).
5478 max_digit_width = 7 # For Calibri 11.
5479 padding = 5
5480 pixels = 0
5482 # Look up the cell value to see if it has been changed.
5483 if col in self.col_info:
5484 width = self.col_info[col][0]
5485 hidden = self.col_info[col][2]
5487 if width is None:
5488 width = self.default_col_width
5490 # Convert to pixels.
5491 if hidden and anchor != 4:
5492 pixels = 0
5493 elif width < 1:
5494 pixels = int(width * (max_digit_width + padding) + 0.5)
5495 else:
5496 pixels = int(width * max_digit_width + 0.5) + padding
5497 else:
5498 pixels = self.default_col_pixels
5500 return pixels
5502 def _size_row(self, row, anchor=0):
5503 # Convert the height of a cell from character units to pixels. If the
5504 # height hasn't been set by the user we use the default value. A
5505 # hidden row is treated as having a height of zero unless it has the
5506 # special "object_position" of 4 (size with cells).
5507 pixels = 0
5509 # Look up the cell value to see if it has been changed
5510 if row in self.row_sizes:
5511 height = self.row_sizes[row][0]
5512 hidden = self.row_sizes[row][1]
5514 if hidden and anchor != 4:
5515 pixels = 0
5516 else:
5517 pixels = int(4.0 / 3.0 * height)
5518 else:
5519 pixels = int(4.0 / 3.0 * self.default_row_height)
5521 return pixels
5523 def _pixels_to_width(self, pixels):
5524 # Convert the width of a cell from pixels to character units.
5525 max_digit_width = 7.0 # For Calabri 11.
5526 padding = 5.0
5528 if pixels <= 12:
5529 width = pixels / (max_digit_width + padding)
5530 else:
5531 width = (pixels - padding) / max_digit_width
5533 return width
5535 def _pixels_to_height(self, pixels):
5536 # Convert the height of a cell from pixels to character units.
5537 return 0.75 * pixels
5539 def _comment_vertices(self, comment: CommentType):
5540 # Calculate the positions of the comment object.
5541 anchor = 0
5542 vertices = self._position_object_pixels(
5543 comment.start_col,
5544 comment.start_row,
5545 comment.x_offset,
5546 comment.y_offset,
5547 comment.width,
5548 comment.height,
5549 anchor,
5550 )
5552 # Add the width and height for VML.
5553 vertices.append(comment.width)
5554 vertices.append(comment.height)
5556 return vertices
5558 def _button_vertices(self, button: ButtonType):
5559 # Calculate the positions of the button object.
5560 anchor = 0
5561 vertices = self._position_object_pixels(
5562 button.col,
5563 button.row,
5564 button.x_offset,
5565 button.y_offset,
5566 button.width,
5567 button.height,
5568 anchor,
5569 )
5571 # Add the width and height for VML.
5572 vertices.append(button.width)
5573 vertices.append(button.height)
5575 return vertices
5577 def _prepare_vml_objects(
5578 self, vml_data_id, vml_shape_id, vml_drawing_id, comment_id
5579 ):
5580 comments = []
5581 # Sort the comments into row/column order for easier comparison
5582 # testing and set the external links for comments and buttons.
5583 row_nums = sorted(self.comments.keys())
5585 for row in row_nums:
5586 col_nums = sorted(self.comments[row].keys())
5588 for col in col_nums:
5589 comment = self.comments[row][col]
5590 comment.vertices = self._comment_vertices(comment)
5592 # Set comment visibility if required and not user defined.
5593 if comment.is_visible is None:
5594 comment.is_visible = self.comments_visible
5596 # Set comment author if not already user defined.
5597 if comment.author is None:
5598 comment.author = self.comments_author
5600 comments.append(comment)
5602 for button in self.buttons_list:
5603 button.vertices = self._button_vertices(button)
5605 self.external_vml_links.append(
5606 ["/vmlDrawing", "../drawings/vmlDrawing" + str(vml_drawing_id) + ".vml"]
5607 )
5609 if self.has_comments:
5610 self.comments_list = comments
5612 self.external_comment_links.append(
5613 ["/comments", "../comments" + str(comment_id) + ".xml"]
5614 )
5616 count = len(comments)
5617 start_data_id = vml_data_id
5619 # The VML o:idmap data id contains a comma separated range when there
5620 # is more than one 1024 block of comments, like this: data="1,2".
5621 for i in range(int(count / 1024)):
5622 data_id = start_data_id + i + 1
5623 vml_data_id = f"{vml_data_id},{data_id}"
5625 self.vml_data_id = vml_data_id
5626 self.vml_shape_id = vml_shape_id
5628 return count
5630 def _prepare_header_vml_objects(self, vml_header_id, vml_drawing_id):
5631 # Set up external linkage for VML header/footer images.
5633 self.vml_header_id = vml_header_id
5635 self.external_vml_links.append(
5636 ["/vmlDrawing", "../drawings/vmlDrawing" + str(vml_drawing_id) + ".vml"]
5637 )
5639 def _prepare_tables(self, table_id, seen):
5640 # Set the table ids for the worksheet tables.
5641 for table in self.tables:
5642 table["id"] = table_id
5644 if table.get("name") is None:
5645 # Set a default name.
5646 table["name"] = "Table" + str(table_id)
5648 # Check for duplicate table names.
5649 name = table["name"].lower()
5651 if name in seen:
5652 raise DuplicateTableName(
5653 f"Duplicate name '{table['name']}' used in worksheet.add_table()."
5654 )
5656 seen[name] = True
5658 # Store the link used for the rels file.
5659 self.external_table_links.append(
5660 ["/table", "../tables/table" + str(table_id) + ".xml"]
5661 )
5662 table_id += 1
5664 def _table_function_to_formula(self, function, col_name):
5665 # Convert a table total function to a worksheet formula.
5666 formula = ""
5668 # Escape special characters, as required by Excel.
5669 col_name = col_name.replace("'", "''")
5670 col_name = col_name.replace("#", "'#")
5671 col_name = col_name.replace("]", "']")
5672 col_name = col_name.replace("[", "'[")
5674 subtotals = {
5675 "average": 101,
5676 "countNums": 102,
5677 "count": 103,
5678 "max": 104,
5679 "min": 105,
5680 "stdDev": 107,
5681 "sum": 109,
5682 "var": 110,
5683 }
5685 if function in subtotals:
5686 func_num = subtotals[function]
5687 formula = f"SUBTOTAL({func_num},[{col_name}])"
5688 else:
5689 warn(f"Unsupported function '{function}' in add_table()")
5691 return formula
5693 def _set_spark_color(self, sparkline, options, user_color):
5694 # Set the sparkline color.
5695 if user_color not in options:
5696 return
5698 sparkline[user_color] = Color._from_value(options[user_color])
5700 def _get_range_data(self, row_start, col_start, row_end, col_end):
5701 # Returns a range of data from the worksheet _table to be used in
5702 # chart cached data. Strings are returned as SST ids and decoded
5703 # in the workbook. Return None for data that doesn't exist since
5704 # Excel can chart series with data missing.
5706 if self.constant_memory:
5707 return ()
5709 data = []
5711 # Iterate through the table data.
5712 for row_num in range(row_start, row_end + 1):
5713 # Store None if row doesn't exist.
5714 if row_num not in self.table:
5715 data.append(None)
5716 continue
5718 for col_num in range(col_start, col_end + 1):
5719 if col_num in self.table[row_num]:
5720 cell = self.table[row_num][col_num]
5722 cell_type = cell.__class__.__name__
5724 if cell_type in ("Number", "Datetime"):
5725 # Return a number with Excel's precision.
5726 data.append(f"{cell.number:.16g}")
5728 elif cell_type == "String":
5729 # Return a string from it's shared string index.
5730 index = cell.string
5731 string = self.str_table._get_shared_string(index)
5733 data.append(string)
5735 elif cell_type in ("Formula", "ArrayFormula"):
5736 # Return the formula value.
5737 value = cell.value
5739 if value is None:
5740 value = 0
5742 data.append(value)
5744 elif cell_type == "Blank":
5745 # Return a empty cell.
5746 data.append("")
5747 else:
5748 # Store None if column doesn't exist.
5749 data.append(None)
5751 return data
5753 def _csv_join(self, *items):
5754 # Create a csv string for use with data validation formulas and lists.
5756 # Convert non string types to string.
5757 items = [str(item) if not isinstance(item, str) else item for item in items]
5759 return ",".join(items)
5761 def _escape_url(self, url):
5762 # Don't escape URL if it looks already escaped.
5763 if re.search("%[0-9a-fA-F]{2}", url):
5764 return url
5766 # Can't use url.quote() here because it doesn't match Excel.
5767 url = url.replace("%", "%25")
5768 url = url.replace('"', "%22")
5769 url = url.replace(" ", "%20")
5770 url = url.replace("<", "%3c")
5771 url = url.replace(">", "%3e")
5772 url = url.replace("[", "%5b")
5773 url = url.replace("]", "%5d")
5774 url = url.replace("^", "%5e")
5775 url = url.replace("`", "%60")
5776 url = url.replace("{", "%7b")
5777 url = url.replace("}", "%7d")
5779 return url
5781 def _get_drawing_rel_index(self, target=None):
5782 # Get the index used to address a drawing rel link.
5783 if target is None:
5784 self.drawing_rels_id += 1
5785 return self.drawing_rels_id
5787 if self.drawing_rels.get(target):
5788 return self.drawing_rels[target]
5790 self.drawing_rels_id += 1
5791 self.drawing_rels[target] = self.drawing_rels_id
5792 return self.drawing_rels_id
5794 def _get_vml_drawing_rel_index(self, target=None):
5795 # Get the index used to address a vml drawing rel link.
5796 if self.vml_drawing_rels.get(target):
5797 return self.vml_drawing_rels[target]
5799 self.vml_drawing_rels_id += 1
5800 self.vml_drawing_rels[target] = self.vml_drawing_rels_id
5801 return self.vml_drawing_rels_id
5803 ###########################################################################
5804 #
5805 # The following font methods are mainly duplicated from the Styles class
5806 # with appropriate changes for rich string styles.
5807 #
5808 ###########################################################################
5809 def _write_font(self, xf_format):
5810 # Write the <font> element.
5811 xml_writer = self.rstring
5813 xml_writer._xml_start_tag("rPr")
5815 # Handle the main font properties.
5816 if xf_format.bold:
5817 xml_writer._xml_empty_tag("b")
5818 if xf_format.italic:
5819 xml_writer._xml_empty_tag("i")
5820 if xf_format.font_strikeout:
5821 xml_writer._xml_empty_tag("strike")
5822 if xf_format.font_outline:
5823 xml_writer._xml_empty_tag("outline")
5824 if xf_format.font_shadow:
5825 xml_writer._xml_empty_tag("shadow")
5827 # Handle the underline variants.
5828 if xf_format.underline:
5829 self._write_underline(xf_format.underline)
5831 # Handle super/subscript.
5832 if xf_format.font_script == 1:
5833 self._write_vert_align("superscript")
5834 if xf_format.font_script == 2:
5835 self._write_vert_align("subscript")
5837 # Write the font size
5838 xml_writer._xml_empty_tag("sz", [("val", xf_format.font_size)])
5840 # Handle colors.
5841 if xf_format.theme == -1:
5842 # Ignore for excel2003_style.
5843 pass
5844 elif xf_format.theme:
5845 self._write_rstring_color("color", [("theme", xf_format.theme)])
5846 elif xf_format.color_indexed:
5847 self._write_rstring_color("color", [("indexed", xf_format.color_indexed)])
5848 elif xf_format.font_color:
5849 color = xf_format.font_color
5850 if not color._is_automatic:
5851 self._write_rstring_color("color", color._attributes())
5852 else:
5853 self._write_rstring_color("color", [("theme", 1)])
5855 # Write some other font properties related to font families.
5856 xml_writer._xml_empty_tag("rFont", [("val", xf_format.font_name)])
5857 xml_writer._xml_empty_tag("family", [("val", xf_format.font_family)])
5859 if xf_format.font_name == "Calibri" and not xf_format.hyperlink:
5860 xml_writer._xml_empty_tag("scheme", [("val", xf_format.font_scheme)])
5862 xml_writer._xml_end_tag("rPr")
5864 def _write_underline(self, underline):
5865 # Write the underline font element.
5866 attributes = []
5868 # Handle the underline variants.
5869 if underline == 2:
5870 attributes = [("val", "double")]
5871 elif underline == 33:
5872 attributes = [("val", "singleAccounting")]
5873 elif underline == 34:
5874 attributes = [("val", "doubleAccounting")]
5876 self.rstring._xml_empty_tag("u", attributes)
5878 def _write_vert_align(self, val):
5879 # Write the <vertAlign> font sub-element.
5880 attributes = [("val", val)]
5882 self.rstring._xml_empty_tag("vertAlign", attributes)
5884 def _write_rstring_color(self, name, attributes):
5885 # Write the <color> element.
5886 self.rstring._xml_empty_tag(name, attributes)
5888 def _opt_close(self):
5889 # Close the row data filehandle in constant_memory mode.
5890 if not self.row_data_fh_closed:
5891 self.row_data_fh.close()
5892 self.row_data_fh_closed = True
5894 def _opt_reopen(self):
5895 # Reopen the row data filehandle in constant_memory mode.
5896 if self.row_data_fh_closed:
5897 filename = self.row_data_filename
5898 # pylint: disable=consider-using-with
5899 self.row_data_fh = open(filename, mode="a+", encoding="utf-8")
5900 self.row_data_fh_closed = False
5901 self.fh = self.row_data_fh
5903 def _set_icon_props(self, total_icons, user_props=None):
5904 # Set the sub-properties for icons.
5905 props = []
5907 # Set the defaults.
5908 for _ in range(total_icons):
5909 props.append({"criteria": False, "value": 0, "type": "percent"})
5911 # Set the default icon values based on the number of icons.
5912 if total_icons == 3:
5913 props[0]["value"] = 67
5914 props[1]["value"] = 33
5916 if total_icons == 4:
5917 props[0]["value"] = 75
5918 props[1]["value"] = 50
5919 props[2]["value"] = 25
5921 if total_icons == 5:
5922 props[0]["value"] = 80
5923 props[1]["value"] = 60
5924 props[2]["value"] = 40
5925 props[3]["value"] = 20
5927 # Overwrite default properties with user defined properties.
5928 if user_props:
5929 # Ensure we don't set user properties for lowest icon.
5930 max_data = len(user_props)
5931 if max_data >= total_icons:
5932 max_data = total_icons - 1
5934 for i in range(max_data):
5935 # Set the user defined 'value' property.
5936 if user_props[i].get("value") is not None:
5937 props[i]["value"] = user_props[i]["value"]
5939 # Remove the formula '=' sign if it exists.
5940 tmp = props[i]["value"]
5941 if isinstance(tmp, str) and tmp.startswith("="):
5942 props[i]["value"] = tmp.lstrip("=")
5944 # Set the user defined 'type' property.
5945 if user_props[i].get("type"):
5946 valid_types = ("percent", "percentile", "number", "formula")
5948 if user_props[i]["type"] not in valid_types:
5949 warn(
5950 f"Unknown icon property type '{user_props[i]['type']}' "
5951 f"for sub-property 'type' in conditional_format()."
5952 )
5953 else:
5954 props[i]["type"] = user_props[i]["type"]
5956 if props[i]["type"] == "number":
5957 props[i]["type"] = "num"
5959 # Set the user defined 'criteria' property.
5960 criteria = user_props[i].get("criteria")
5961 if criteria and criteria == ">":
5962 props[i]["criteria"] = True
5964 return props
5966 ###########################################################################
5967 #
5968 # XML methods.
5969 #
5970 ###########################################################################
5972 def _write_worksheet(self):
5973 # Write the <worksheet> element. This is the root element.
5975 schema = "http://schemas.openxmlformats.org/"
5976 xmlns = schema + "spreadsheetml/2006/main"
5977 xmlns_r = schema + "officeDocument/2006/relationships"
5978 xmlns_mc = schema + "markup-compatibility/2006"
5979 ms_schema = "http://schemas.microsoft.com/"
5980 xmlns_x14ac = ms_schema + "office/spreadsheetml/2009/9/ac"
5982 attributes = [("xmlns", xmlns), ("xmlns:r", xmlns_r)]
5984 # Add some extra attributes for Excel 2010. Mainly for sparklines.
5985 if self.excel_version == 2010:
5986 attributes.append(("xmlns:mc", xmlns_mc))
5987 attributes.append(("xmlns:x14ac", xmlns_x14ac))
5988 attributes.append(("mc:Ignorable", "x14ac"))
5990 self._xml_start_tag("worksheet", attributes)
5992 def _write_dimension(self):
5993 # Write the <dimension> element. This specifies the range of
5994 # cells in the worksheet. As a special case, empty
5995 # spreadsheets use 'A1' as a range.
5997 if self.dim_rowmin is None and self.dim_colmin is None:
5998 # If the min dimensions are not defined then no dimensions
5999 # have been set and we use the default 'A1'.
6000 ref = "A1"
6002 elif self.dim_rowmin is None and self.dim_colmin is not None:
6003 # If the row dimensions aren't set but the column
6004 # dimensions are set then they have been changed via
6005 # set_column().
6007 if self.dim_colmin == self.dim_colmax:
6008 # The dimensions are a single cell and not a range.
6009 ref = xl_rowcol_to_cell(0, self.dim_colmin)
6010 else:
6011 # The dimensions are a cell range.
6012 cell_1 = xl_rowcol_to_cell(0, self.dim_colmin)
6013 cell_2 = xl_rowcol_to_cell(0, self.dim_colmax)
6014 ref = cell_1 + ":" + cell_2
6016 elif self.dim_rowmin == self.dim_rowmax and self.dim_colmin == self.dim_colmax:
6017 # The dimensions are a single cell and not a range.
6018 ref = xl_rowcol_to_cell(self.dim_rowmin, self.dim_colmin)
6019 else:
6020 # The dimensions are a cell range.
6021 cell_1 = xl_rowcol_to_cell(self.dim_rowmin, self.dim_colmin)
6022 cell_2 = xl_rowcol_to_cell(self.dim_rowmax, self.dim_colmax)
6023 ref = cell_1 + ":" + cell_2
6025 self._xml_empty_tag("dimension", [("ref", ref)])
6027 def _write_sheet_views(self):
6028 # Write the <sheetViews> element.
6029 self._xml_start_tag("sheetViews")
6031 # Write the sheetView element.
6032 self._write_sheet_view()
6034 self._xml_end_tag("sheetViews")
6036 def _write_sheet_view(self):
6037 # Write the <sheetViews> element.
6038 attributes = []
6040 # Hide screen gridlines if required.
6041 if not self.screen_gridlines:
6042 attributes.append(("showGridLines", 0))
6044 # Hide screen row/column headers.
6045 if self.row_col_headers:
6046 attributes.append(("showRowColHeaders", 0))
6048 # Hide zeroes in cells.
6049 if not self.show_zeros:
6050 attributes.append(("showZeros", 0))
6052 # Display worksheet right to left for Hebrew, Arabic and others.
6053 if self.is_right_to_left:
6054 attributes.append(("rightToLeft", 1))
6056 # Show that the sheet tab is selected.
6057 if self.selected:
6058 attributes.append(("tabSelected", 1))
6060 # Turn outlines off. Also required in the outlinePr element.
6061 if not self.outline_on:
6062 attributes.append(("showOutlineSymbols", 0))
6064 # Set the page view/layout mode if required.
6065 if self.page_view == 1:
6066 attributes.append(("view", "pageLayout"))
6067 elif self.page_view == 2:
6068 attributes.append(("view", "pageBreakPreview"))
6070 # Set the first visible cell.
6071 if self.top_left_cell != "":
6072 attributes.append(("topLeftCell", self.top_left_cell))
6074 # Set the zoom level.
6075 if self.zoom != 100:
6076 attributes.append(("zoomScale", self.zoom))
6078 if self.page_view == 0 and self.zoom_scale_normal:
6079 attributes.append(("zoomScaleNormal", self.zoom))
6080 if self.page_view == 1:
6081 attributes.append(("zoomScalePageLayoutView", self.zoom))
6082 if self.page_view == 2:
6083 attributes.append(("zoomScaleSheetLayoutView", self.zoom))
6085 attributes.append(("workbookViewId", 0))
6087 if self.panes or self.selections:
6088 self._xml_start_tag("sheetView", attributes)
6089 self._write_panes()
6090 self._write_selections()
6091 self._xml_end_tag("sheetView")
6092 else:
6093 self._xml_empty_tag("sheetView", attributes)
6095 def _write_sheet_format_pr(self):
6096 # Write the <sheetFormatPr> element.
6097 default_row_height = self.default_row_height
6098 row_level = self.outline_row_level
6099 col_level = self.outline_col_level
6101 attributes = [("defaultRowHeight", default_row_height)]
6103 if self.default_row_height != self.original_row_height:
6104 attributes.append(("customHeight", 1))
6106 if self.default_row_zeroed:
6107 attributes.append(("zeroHeight", 1))
6109 if row_level:
6110 attributes.append(("outlineLevelRow", row_level))
6111 if col_level:
6112 attributes.append(("outlineLevelCol", col_level))
6114 if self.excel_version == 2010:
6115 attributes.append(("x14ac:dyDescent", "0.25"))
6117 self._xml_empty_tag("sheetFormatPr", attributes)
6119 def _write_cols(self):
6120 # Write the <cols> element and <col> sub elements.
6122 # Exit unless some column have been formatted.
6123 if not self.col_info:
6124 return
6126 self._xml_start_tag("cols")
6128 # Use the first element of the column information structures to set
6129 # the initial/previous properties.
6130 first_col = (sorted(self.col_info.keys()))[0]
6131 last_col = first_col
6132 prev_col_options = self.col_info[first_col]
6133 del self.col_info[first_col]
6134 deleted_col = first_col
6135 deleted_col_options = prev_col_options
6137 for col in sorted(self.col_info.keys()):
6138 col_options = self.col_info[col]
6139 # Check if the column number is contiguous with the previous
6140 # column and if the properties are the same.
6141 if col == last_col + 1 and col_options == prev_col_options:
6142 last_col = col
6143 else:
6144 # If not contiguous/equal then we write out the current range
6145 # of columns and start again.
6146 self._write_col_info(first_col, last_col, prev_col_options)
6147 first_col = col
6148 last_col = first_col
6149 prev_col_options = col_options
6151 # We will exit the previous loop with one unhandled column range.
6152 self._write_col_info(first_col, last_col, prev_col_options)
6154 # Put back the deleted first column information structure.
6155 self.col_info[deleted_col] = deleted_col_options
6157 self._xml_end_tag("cols")
6159 def _write_col_info(self, col_min, col_max, col_info):
6160 # Write the <col> element.
6161 (width, cell_format, hidden, level, collapsed, autofit) = col_info
6163 custom_width = 1
6164 xf_index = 0
6166 # Get the cell_format index.
6167 if cell_format:
6168 xf_index = cell_format._get_xf_index()
6170 # Set the Excel default column width.
6171 if width is None:
6172 if not hidden:
6173 width = 8.43
6174 custom_width = 0
6175 else:
6176 width = 0
6177 elif width == 8.43:
6178 # Width is defined but same as default.
6179 custom_width = 0
6181 # Convert column width from user units to character width.
6182 if width > 0:
6183 # For Calabri 11.
6184 max_digit_width = 7
6185 padding = 5
6187 if width < 1:
6188 width = (
6189 int(
6190 (int(width * (max_digit_width + padding) + 0.5))
6191 / float(max_digit_width)
6192 * 256.0
6193 )
6194 / 256.0
6195 )
6196 else:
6197 width = (
6198 int(
6199 (int(width * max_digit_width + 0.5) + padding)
6200 / float(max_digit_width)
6201 * 256.0
6202 )
6203 / 256.0
6204 )
6206 attributes = [
6207 ("min", col_min + 1),
6208 ("max", col_max + 1),
6209 ("width", f"{width:.16g}"),
6210 ]
6212 if xf_index:
6213 attributes.append(("style", xf_index))
6214 if hidden:
6215 attributes.append(("hidden", "1"))
6216 if autofit:
6217 attributes.append(("bestFit", "1"))
6218 if custom_width:
6219 attributes.append(("customWidth", "1"))
6220 if level:
6221 attributes.append(("outlineLevel", level))
6222 if collapsed:
6223 attributes.append(("collapsed", "1"))
6225 self._xml_empty_tag("col", attributes)
6227 def _write_sheet_data(self):
6228 # Write the <sheetData> element.
6229 if self.dim_rowmin is None:
6230 # If the dimensions aren't defined there is no data to write.
6231 self._xml_empty_tag("sheetData")
6232 else:
6233 self._xml_start_tag("sheetData")
6234 self._write_rows()
6235 self._xml_end_tag("sheetData")
6237 def _write_optimized_sheet_data(self):
6238 # Write the <sheetData> element when constant_memory is on. In this
6239 # case we read the data stored in the temp file and rewrite it to the
6240 # XML sheet file.
6241 if self.dim_rowmin is None:
6242 # If the dimensions aren't defined then there is no data to write.
6243 self._xml_empty_tag("sheetData")
6244 else:
6245 self._xml_start_tag("sheetData")
6247 # Rewind the filehandle that was used for temp row data.
6248 buff_size = 65536
6249 self.row_data_fh.seek(0)
6250 data = self.row_data_fh.read(buff_size)
6252 while data:
6253 self.fh.write(data)
6254 data = self.row_data_fh.read(buff_size)
6256 self.row_data_fh.close()
6257 os.unlink(self.row_data_filename)
6259 self._xml_end_tag("sheetData")
6261 def _write_page_margins(self):
6262 # Write the <pageMargins> element.
6263 attributes = [
6264 ("left", self.margin_left),
6265 ("right", self.margin_right),
6266 ("top", self.margin_top),
6267 ("bottom", self.margin_bottom),
6268 ("header", self.margin_header),
6269 ("footer", self.margin_footer),
6270 ]
6272 self._xml_empty_tag("pageMargins", attributes)
6274 def _write_page_setup(self):
6275 # Write the <pageSetup> element.
6276 #
6277 # The following is an example taken from Excel.
6278 #
6279 # <pageSetup
6280 # paperSize="9"
6281 # scale="110"
6282 # fitToWidth="2"
6283 # fitToHeight="2"
6284 # pageOrder="overThenDown"
6285 # orientation="portrait"
6286 # blackAndWhite="1"
6287 # draft="1"
6288 # horizontalDpi="200"
6289 # verticalDpi="200"
6290 # r:id="rId1"
6291 # />
6292 #
6293 attributes = []
6295 # Skip this element if no page setup has changed.
6296 if not self.page_setup_changed:
6297 return
6299 # Set paper size.
6300 if self.paper_size:
6301 attributes.append(("paperSize", self.paper_size))
6303 # Set the print_scale.
6304 if self.print_scale != 100:
6305 attributes.append(("scale", self.print_scale))
6307 # Set the "Fit to page" properties.
6308 if self.fit_page and self.fit_width != 1:
6309 attributes.append(("fitToWidth", self.fit_width))
6311 if self.fit_page and self.fit_height != 1:
6312 attributes.append(("fitToHeight", self.fit_height))
6314 # Set the page print direction.
6315 if self.page_order:
6316 attributes.append(("pageOrder", "overThenDown"))
6318 # Set start page for printing.
6319 if self.page_start > 1:
6320 attributes.append(("firstPageNumber", self.page_start))
6322 # Set page orientation.
6323 if self.orientation:
6324 attributes.append(("orientation", "portrait"))
6325 else:
6326 attributes.append(("orientation", "landscape"))
6328 # Set the print in black and white option.
6329 if self.black_white:
6330 attributes.append(("blackAndWhite", "1"))
6332 # Set start page for printing.
6333 if self.page_start != 0:
6334 attributes.append(("useFirstPageNumber", "1"))
6336 # Set the DPI. Mainly only for testing.
6337 if self.is_chartsheet:
6338 if self.horizontal_dpi:
6339 attributes.append(("horizontalDpi", self.horizontal_dpi))
6341 if self.vertical_dpi:
6342 attributes.append(("verticalDpi", self.vertical_dpi))
6343 else:
6344 if self.vertical_dpi:
6345 attributes.append(("verticalDpi", self.vertical_dpi))
6347 if self.horizontal_dpi:
6348 attributes.append(("horizontalDpi", self.horizontal_dpi))
6350 self._xml_empty_tag("pageSetup", attributes)
6352 def _write_print_options(self):
6353 # Write the <printOptions> element.
6354 attributes = []
6356 if not self.print_options_changed:
6357 return
6359 # Set horizontal centering.
6360 if self.hcenter:
6361 attributes.append(("horizontalCentered", 1))
6363 # Set vertical centering.
6364 if self.vcenter:
6365 attributes.append(("verticalCentered", 1))
6367 # Enable row and column headers.
6368 if self.print_headers:
6369 attributes.append(("headings", 1))
6371 # Set printed gridlines.
6372 if self.print_gridlines:
6373 attributes.append(("gridLines", 1))
6375 self._xml_empty_tag("printOptions", attributes)
6377 def _write_header_footer(self):
6378 # Write the <headerFooter> element.
6379 attributes = []
6381 if not self.header_footer_scales:
6382 attributes.append(("scaleWithDoc", 0))
6384 if not self.header_footer_aligns:
6385 attributes.append(("alignWithMargins", 0))
6387 if self.header_footer_changed:
6388 self._xml_start_tag("headerFooter", attributes)
6389 if self.header:
6390 self._write_odd_header()
6391 if self.footer:
6392 self._write_odd_footer()
6393 self._xml_end_tag("headerFooter")
6394 elif self.excel2003_style:
6395 self._xml_empty_tag("headerFooter", attributes)
6397 def _write_odd_header(self):
6398 # Write the <headerFooter> element.
6399 self._xml_data_element("oddHeader", self.header)
6401 def _write_odd_footer(self):
6402 # Write the <headerFooter> element.
6403 self._xml_data_element("oddFooter", self.footer)
6405 def _write_rows(self):
6406 # Write out the worksheet data as a series of rows and cells.
6407 self._calculate_spans()
6409 for row_num in range(self.dim_rowmin, self.dim_rowmax + 1):
6410 if (
6411 row_num in self.set_rows
6412 or row_num in self.comments
6413 or self.table[row_num]
6414 ):
6415 # Only process rows with formatting, cell data and/or comments.
6417 span_index = int(row_num / 16)
6419 if span_index in self.row_spans:
6420 span = self.row_spans[span_index]
6421 else:
6422 span = None
6424 if self.table[row_num]:
6425 # Write the cells if the row contains data.
6426 if row_num not in self.set_rows:
6427 self._write_row(row_num, span)
6428 else:
6429 self._write_row(row_num, span, self.set_rows[row_num])
6431 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6432 if col_num in self.table[row_num]:
6433 col_ref = self.table[row_num][col_num]
6434 self._write_cell(row_num, col_num, col_ref)
6436 self._xml_end_tag("row")
6438 elif row_num in self.comments:
6439 # Row with comments in cells.
6440 self._write_empty_row(row_num, span, self.set_rows[row_num])
6441 else:
6442 # Blank row with attributes only.
6443 self._write_empty_row(row_num, span, self.set_rows[row_num])
6445 def _write_single_row(self, current_row_num=0):
6446 # Write out the worksheet data as a single row with cells.
6447 # This method is used when constant_memory is on. A single
6448 # row is written and the data table is reset. That way only
6449 # one row of data is kept in memory at any one time. We don't
6450 # write span data in the optimized case since it is optional.
6452 # Set the new previous row as the current row.
6453 row_num = self.previous_row
6454 self.previous_row = current_row_num
6456 if row_num in self.set_rows or row_num in self.comments or self.table[row_num]:
6457 # Only process rows with formatting, cell data and/or comments.
6459 # No span data in optimized mode.
6460 span = None
6462 if self.table[row_num]:
6463 # Write the cells if the row contains data.
6464 if row_num not in self.set_rows:
6465 self._write_row(row_num, span)
6466 else:
6467 self._write_row(row_num, span, self.set_rows[row_num])
6469 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6470 if col_num in self.table[row_num]:
6471 col_ref = self.table[row_num][col_num]
6472 self._write_cell(row_num, col_num, col_ref)
6474 self._xml_end_tag("row")
6475 else:
6476 # Row attributes or comments only.
6477 self._write_empty_row(row_num, span, self.set_rows[row_num])
6479 # Reset table.
6480 self.table.clear()
6482 def _calculate_spans(self):
6483 # Calculate the "spans" attribute of the <row> tag. This is an
6484 # XLSX optimization and isn't strictly required. However, it
6485 # makes comparing files easier. The span is the same for each
6486 # block of 16 rows.
6487 spans = {}
6488 span_min = None
6489 span_max = None
6491 for row_num in range(self.dim_rowmin, self.dim_rowmax + 1):
6492 if row_num in self.table:
6493 # Calculate spans for cell data.
6494 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6495 if col_num in self.table[row_num]:
6496 if span_min is None:
6497 span_min = col_num
6498 span_max = col_num
6499 else:
6500 span_min = min(span_min, col_num)
6501 span_max = max(span_max, col_num)
6503 if row_num in self.comments:
6504 # Calculate spans for comments.
6505 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6506 if row_num in self.comments and col_num in self.comments[row_num]:
6507 if span_min is None:
6508 span_min = col_num
6509 span_max = col_num
6510 else:
6511 span_min = min(span_min, col_num)
6512 span_max = max(span_max, col_num)
6514 if ((row_num + 1) % 16 == 0) or row_num == self.dim_rowmax:
6515 span_index = int(row_num / 16)
6517 if span_min is not None:
6518 span_min += 1
6519 span_max += 1
6520 spans[span_index] = f"{span_min}:{span_max}"
6521 span_min = None
6523 self.row_spans = spans
6525 def _write_row(self, row, spans, properties=None, empty_row=False):
6526 # Write the <row> element.
6527 xf_index = 0
6529 if properties:
6530 height, cell_format, hidden, level, collapsed = properties
6531 else:
6532 height, cell_format, hidden, level, collapsed = None, None, 0, 0, 0
6534 if height is None:
6535 height = self.default_row_height
6537 attributes = [("r", row + 1)]
6539 # Get the cell_format index.
6540 if cell_format:
6541 xf_index = cell_format._get_xf_index()
6543 # Add row attributes where applicable.
6544 if spans:
6545 attributes.append(("spans", spans))
6547 if xf_index:
6548 attributes.append(("s", xf_index))
6550 if cell_format:
6551 attributes.append(("customFormat", 1))
6553 if height != self.original_row_height or (
6554 height == self.original_row_height and height != self.default_row_height
6555 ):
6556 attributes.append(("ht", f"{height:g}"))
6558 if hidden:
6559 attributes.append(("hidden", 1))
6561 if height != self.original_row_height or (
6562 height == self.original_row_height and height != self.default_row_height
6563 ):
6564 attributes.append(("customHeight", 1))
6566 if level:
6567 attributes.append(("outlineLevel", level))
6569 if collapsed:
6570 attributes.append(("collapsed", 1))
6572 if self.excel_version == 2010:
6573 attributes.append(("x14ac:dyDescent", "0.25"))
6575 if empty_row:
6576 self._xml_empty_tag_unencoded("row", attributes)
6577 else:
6578 self._xml_start_tag_unencoded("row", attributes)
6580 def _write_empty_row(self, row, spans, properties=None):
6581 # Write and empty <row> element.
6582 self._write_row(row, spans, properties, empty_row=True)
6584 def _write_cell(self, row, col, cell):
6585 # Write the <cell> element.
6586 # Note. This is the innermost loop so efficiency is important.
6588 cell_range = xl_rowcol_to_cell_fast(row, col)
6589 attributes = [("r", cell_range)]
6591 if cell.format:
6592 # Add the cell format index.
6593 xf_index = cell.format._get_xf_index()
6594 attributes.append(("s", xf_index))
6595 elif row in self.set_rows and self.set_rows[row][1]:
6596 # Add the row format.
6597 row_xf = self.set_rows[row][1]
6598 attributes.append(("s", row_xf._get_xf_index()))
6599 elif col in self.col_info:
6600 # Add the column format.
6601 col_xf = self.col_info[col][1]
6602 if col_xf is not None:
6603 attributes.append(("s", col_xf._get_xf_index()))
6605 type_cell_name = cell.__class__.__name__
6607 # Write the various cell types.
6608 if type_cell_name in ("Number", "Datetime"):
6609 # Write a number.
6610 self._xml_number_element(cell.number, attributes)
6612 elif type_cell_name in ("String", "RichString"):
6613 # Write a string.
6614 string = cell.string
6616 if not self.constant_memory:
6617 # Write a shared string.
6618 self._xml_string_element(string, attributes)
6619 else:
6620 # Write an optimized in-line string.
6622 # Convert control character to a _xHHHH_ escape.
6623 string = self._escape_control_characters(string)
6625 # Write any rich strings without further tags.
6626 if string.startswith("<r>") and string.endswith("</r>"):
6627 self._xml_rich_inline_string(string, attributes)
6628 else:
6629 # Add attribute to preserve leading or trailing whitespace.
6630 preserve = _preserve_whitespace(string)
6631 self._xml_inline_string(string, preserve, attributes)
6633 elif type_cell_name == "Formula":
6634 # Write a formula. First check the formula value type.
6635 value = cell.value
6636 if isinstance(cell.value, bool):
6637 attributes.append(("t", "b"))
6638 if cell.value:
6639 value = 1
6640 else:
6641 value = 0
6643 elif isinstance(cell.value, str):
6644 error_codes = (
6645 "#DIV/0!",
6646 "#N/A",
6647 "#NAME?",
6648 "#NULL!",
6649 "#NUM!",
6650 "#REF!",
6651 "#VALUE!",
6652 )
6654 if cell.value == "":
6655 # Allow blank to force recalc in some third party apps.
6656 pass
6657 elif cell.value in error_codes:
6658 attributes.append(("t", "e"))
6659 else:
6660 attributes.append(("t", "str"))
6662 self._xml_formula_element(cell.formula, value, attributes)
6664 elif type_cell_name == "ArrayFormula":
6665 # Write a array formula.
6667 if cell.atype == "dynamic":
6668 attributes.append(("cm", 1))
6670 # First check if the formula value is a string.
6671 try:
6672 float(cell.value)
6673 except ValueError:
6674 attributes.append(("t", "str"))
6676 # Write an array formula.
6677 self._xml_start_tag("c", attributes)
6679 self._write_cell_array_formula(cell.formula, cell.range)
6680 self._write_cell_value(cell.value)
6681 self._xml_end_tag("c")
6683 elif type_cell_name == "Blank":
6684 # Write a empty cell.
6685 self._xml_empty_tag("c", attributes)
6687 elif type_cell_name == "Boolean":
6688 # Write a boolean cell.
6689 attributes.append(("t", "b"))
6690 self._xml_start_tag("c", attributes)
6691 self._write_cell_value(cell.boolean)
6692 self._xml_end_tag("c")
6694 elif type_cell_name == "Error":
6695 # Write a boolean cell.
6696 attributes.append(("t", "e"))
6697 attributes.append(("vm", cell.value))
6698 self._xml_start_tag("c", attributes)
6699 self._write_cell_value(cell.error)
6700 self._xml_end_tag("c")
6702 def _write_cell_value(self, value):
6703 # Write the cell value <v> element.
6704 if value is None:
6705 value = ""
6707 self._xml_data_element("v", value)
6709 def _write_cell_array_formula(self, formula, cell_range):
6710 # Write the cell array formula <f> element.
6711 attributes = [("t", "array"), ("ref", cell_range)]
6713 self._xml_data_element("f", formula, attributes)
6715 def _write_sheet_pr(self):
6716 # Write the <sheetPr> element for Sheet level properties.
6717 attributes = []
6719 if (
6720 not self.fit_page
6721 and not self.filter_on
6722 and not self.tab_color
6723 and not self.outline_changed
6724 and not self.vba_codename
6725 ):
6726 return
6728 if self.vba_codename:
6729 attributes.append(("codeName", self.vba_codename))
6731 if self.filter_on:
6732 attributes.append(("filterMode", 1))
6734 if self.fit_page or self.tab_color or self.outline_changed:
6735 self._xml_start_tag("sheetPr", attributes)
6736 self._write_tab_color()
6737 self._write_outline_pr()
6738 self._write_page_set_up_pr()
6739 self._xml_end_tag("sheetPr")
6740 else:
6741 self._xml_empty_tag("sheetPr", attributes)
6743 def _write_page_set_up_pr(self):
6744 # Write the <pageSetUpPr> element.
6745 if not self.fit_page:
6746 return
6748 attributes = [("fitToPage", 1)]
6749 self._xml_empty_tag("pageSetUpPr", attributes)
6751 def _write_tab_color(self):
6752 # Write the <tabColor> element.
6753 color = self.tab_color
6755 if not color:
6756 return
6758 self._write_color("tabColor", color._attributes())
6760 def _write_outline_pr(self):
6761 # Write the <outlinePr> element.
6762 attributes = []
6764 if not self.outline_changed:
6765 return
6767 if self.outline_style:
6768 attributes.append(("applyStyles", 1))
6769 if not self.outline_below:
6770 attributes.append(("summaryBelow", 0))
6771 if not self.outline_right:
6772 attributes.append(("summaryRight", 0))
6773 if not self.outline_on:
6774 attributes.append(("showOutlineSymbols", 0))
6776 self._xml_empty_tag("outlinePr", attributes)
6778 def _write_row_breaks(self):
6779 # Write the <rowBreaks> element.
6780 page_breaks = self._sort_pagebreaks(self.hbreaks)
6782 if not page_breaks:
6783 return
6785 count = len(page_breaks)
6787 attributes = [
6788 ("count", count),
6789 ("manualBreakCount", count),
6790 ]
6792 self._xml_start_tag("rowBreaks", attributes)
6794 for row_num in page_breaks:
6795 self._write_brk(row_num, 16383)
6797 self._xml_end_tag("rowBreaks")
6799 def _write_col_breaks(self):
6800 # Write the <colBreaks> element.
6801 page_breaks = self._sort_pagebreaks(self.vbreaks)
6803 if not page_breaks:
6804 return
6806 count = len(page_breaks)
6808 attributes = [
6809 ("count", count),
6810 ("manualBreakCount", count),
6811 ]
6813 self._xml_start_tag("colBreaks", attributes)
6815 for col_num in page_breaks:
6816 self._write_brk(col_num, 1048575)
6818 self._xml_end_tag("colBreaks")
6820 def _write_brk(self, brk_id, brk_max):
6821 # Write the <brk> element.
6822 attributes = [("id", brk_id), ("max", brk_max), ("man", 1)]
6824 self._xml_empty_tag("brk", attributes)
6826 def _write_merge_cells(self):
6827 # Write the <mergeCells> element.
6828 merged_cells = self.merge
6829 count = len(merged_cells)
6831 if not count:
6832 return
6834 attributes = [("count", count)]
6836 self._xml_start_tag("mergeCells", attributes)
6838 for merged_range in merged_cells:
6839 # Write the mergeCell element.
6840 self._write_merge_cell(merged_range)
6842 self._xml_end_tag("mergeCells")
6844 def _write_merge_cell(self, merged_range):
6845 # Write the <mergeCell> element.
6846 (row_min, col_min, row_max, col_max) = merged_range
6848 # Convert the merge dimensions to a cell range.
6849 cell_1 = xl_rowcol_to_cell(row_min, col_min)
6850 cell_2 = xl_rowcol_to_cell(row_max, col_max)
6851 ref = cell_1 + ":" + cell_2
6853 attributes = [("ref", ref)]
6855 self._xml_empty_tag("mergeCell", attributes)
6857 def _write_hyperlinks(self):
6858 # Process any stored hyperlinks in row/col order and write the
6859 # <hyperlinks> element. The attributes are different for internal
6860 # and external links.
6862 # Sort the hyperlinks into row order.
6863 row_nums = sorted(self.hyperlinks.keys())
6865 # Exit if there are no hyperlinks to process.
6866 if not row_nums:
6867 return
6869 # Write the hyperlink elements.
6870 self._xml_start_tag("hyperlinks")
6872 # Iterate over the rows.
6873 for row_num in row_nums:
6874 # Sort the hyperlinks into column order.
6875 col_nums = sorted(self.hyperlinks[row_num].keys())
6877 # Iterate over the columns.
6878 for col_num in col_nums:
6879 # Get the link data for this cell.
6880 url = self.hyperlinks[row_num][col_num]
6882 # If the cell was overwritten by the user and isn't a string
6883 # then we have to add the url as the string to display.
6884 if self.table and self.table[row_num] and self.table[row_num][col_num]:
6885 cell = self.table[row_num][col_num]
6886 if cell.__class__.__name__ != "String":
6887 url._is_object_link = True
6889 if url._link_type in (UrlTypes.URL, UrlTypes.EXTERNAL):
6890 # External link with rel file relationship.
6891 self.rel_count += 1
6893 self._write_hyperlink_external(
6894 row_num, col_num, self.rel_count, url
6895 )
6897 # Links for use by the packager.
6898 self.external_hyper_links.append(
6899 ["/hyperlink", url._target(), "External"]
6900 )
6901 else:
6902 # Internal link with rel file relationship.
6903 self._write_hyperlink_internal(row_num, col_num, url)
6905 self._xml_end_tag("hyperlinks")
6907 def _write_hyperlink_external(self, row: int, col: int, id_num: int, url: Url):
6908 # Write the <hyperlink> element for external links.
6909 ref = xl_rowcol_to_cell(row, col)
6910 r_id = "rId" + str(id_num)
6912 attributes = [("ref", ref), ("r:id", r_id)]
6914 if url._anchor:
6915 attributes.append(("location", url._anchor))
6917 if url._is_object_link:
6918 attributes.append(("display", url._text))
6920 if url._tip:
6921 attributes.append(("tooltip", url._tip))
6923 self._xml_empty_tag("hyperlink", attributes)
6925 def _write_hyperlink_internal(self, row: int, col: int, url: Url):
6926 # Write the <hyperlink> element for internal links.
6927 ref = xl_rowcol_to_cell(row, col)
6929 attributes = [("ref", ref), ("location", url._link)]
6931 if url._tip:
6932 attributes.append(("tooltip", url._tip))
6934 attributes.append(("display", url._text))
6936 self._xml_empty_tag("hyperlink", attributes)
6938 def _write_auto_filter(self):
6939 # Write the <autoFilter> element.
6940 if not self.autofilter_ref:
6941 return
6943 attributes = [("ref", self.autofilter_ref)]
6945 if self.filter_on:
6946 # Autofilter defined active filters.
6947 self._xml_start_tag("autoFilter", attributes)
6948 self._write_autofilters()
6949 self._xml_end_tag("autoFilter")
6951 else:
6952 # Autofilter defined without active filters.
6953 self._xml_empty_tag("autoFilter", attributes)
6955 def _write_autofilters(self):
6956 # Function to iterate through the columns that form part of an
6957 # autofilter range and write the appropriate filters.
6958 (col1, col2) = self.filter_range
6960 for col in range(col1, col2 + 1):
6961 # Skip if column doesn't have an active filter.
6962 if col not in self.filter_cols:
6963 continue
6965 # Retrieve the filter tokens and write the autofilter records.
6966 tokens = self.filter_cols[col]
6967 filter_type = self.filter_type[col]
6969 # Filters are relative to first column in the autofilter.
6970 self._write_filter_column(col - col1, filter_type, tokens)
6972 def _write_filter_column(self, col_id, filter_type, filters):
6973 # Write the <filterColumn> element.
6974 attributes = [("colId", col_id)]
6976 self._xml_start_tag("filterColumn", attributes)
6978 if filter_type == 1:
6979 # Type == 1 is the new XLSX style filter.
6980 self._write_filters(filters)
6981 else:
6982 # Type == 0 is the classic "custom" filter.
6983 self._write_custom_filters(filters)
6985 self._xml_end_tag("filterColumn")
6987 def _write_filters(self, filters):
6988 # Write the <filters> element.
6989 non_blanks = [filter for filter in filters if str(filter).lower() != "blanks"]
6990 attributes = []
6992 if len(filters) != len(non_blanks):
6993 attributes = [("blank", 1)]
6995 if len(filters) == 1 and len(non_blanks) == 0:
6996 # Special case for blank cells only.
6997 self._xml_empty_tag("filters", attributes)
6998 else:
6999 # General case.
7000 self._xml_start_tag("filters", attributes)
7002 for autofilter in sorted(non_blanks):
7003 self._write_filter(autofilter)
7005 self._xml_end_tag("filters")
7007 def _write_filter(self, val):
7008 # Write the <filter> element.
7009 attributes = [("val", val)]
7011 self._xml_empty_tag("filter", attributes)
7013 def _write_custom_filters(self, tokens):
7014 # Write the <customFilters> element.
7015 if len(tokens) == 2:
7016 # One filter expression only.
7017 self._xml_start_tag("customFilters")
7018 self._write_custom_filter(*tokens)
7019 self._xml_end_tag("customFilters")
7020 else:
7021 # Two filter expressions.
7022 attributes = []
7024 # Check if the "join" operand is "and" or "or".
7025 if tokens[2] == 0:
7026 attributes = [("and", 1)]
7027 else:
7028 attributes = [("and", 0)]
7030 # Write the two custom filters.
7031 self._xml_start_tag("customFilters", attributes)
7032 self._write_custom_filter(tokens[0], tokens[1])
7033 self._write_custom_filter(tokens[3], tokens[4])
7034 self._xml_end_tag("customFilters")
7036 def _write_custom_filter(self, operator, val):
7037 # Write the <customFilter> element.
7038 attributes = []
7040 operators = {
7041 1: "lessThan",
7042 2: "equal",
7043 3: "lessThanOrEqual",
7044 4: "greaterThan",
7045 5: "notEqual",
7046 6: "greaterThanOrEqual",
7047 22: "equal",
7048 }
7050 # Convert the operator from a number to a descriptive string.
7051 if operators[operator] is not None:
7052 operator = operators[operator]
7053 else:
7054 warn(f"Unknown operator = {operator}")
7056 # The 'equal' operator is the default attribute and isn't stored.
7057 if operator != "equal":
7058 attributes.append(("operator", operator))
7059 attributes.append(("val", val))
7061 self._xml_empty_tag("customFilter", attributes)
7063 def _write_sheet_protection(self):
7064 # Write the <sheetProtection> element.
7065 attributes = []
7067 if not self.protect_options:
7068 return
7070 options = self.protect_options
7072 if options["password"]:
7073 attributes.append(("password", options["password"]))
7074 if options["sheet"]:
7075 attributes.append(("sheet", 1))
7076 if options["content"]:
7077 attributes.append(("content", 1))
7078 if not options["objects"]:
7079 attributes.append(("objects", 1))
7080 if not options["scenarios"]:
7081 attributes.append(("scenarios", 1))
7082 if options["format_cells"]:
7083 attributes.append(("formatCells", 0))
7084 if options["format_columns"]:
7085 attributes.append(("formatColumns", 0))
7086 if options["format_rows"]:
7087 attributes.append(("formatRows", 0))
7088 if options["insert_columns"]:
7089 attributes.append(("insertColumns", 0))
7090 if options["insert_rows"]:
7091 attributes.append(("insertRows", 0))
7092 if options["insert_hyperlinks"]:
7093 attributes.append(("insertHyperlinks", 0))
7094 if options["delete_columns"]:
7095 attributes.append(("deleteColumns", 0))
7096 if options["delete_rows"]:
7097 attributes.append(("deleteRows", 0))
7098 if not options["select_locked_cells"]:
7099 attributes.append(("selectLockedCells", 1))
7100 if options["sort"]:
7101 attributes.append(("sort", 0))
7102 if options["autofilter"]:
7103 attributes.append(("autoFilter", 0))
7104 if options["pivot_tables"]:
7105 attributes.append(("pivotTables", 0))
7106 if not options["select_unlocked_cells"]:
7107 attributes.append(("selectUnlockedCells", 1))
7109 self._xml_empty_tag("sheetProtection", attributes)
7111 def _write_protected_ranges(self):
7112 # Write the <protectedRanges> element.
7113 if self.num_protected_ranges == 0:
7114 return
7116 self._xml_start_tag("protectedRanges")
7118 for cell_range, range_name, password in self.protected_ranges:
7119 self._write_protected_range(cell_range, range_name, password)
7121 self._xml_end_tag("protectedRanges")
7123 def _write_protected_range(self, cell_range, range_name, password):
7124 # Write the <protectedRange> element.
7125 attributes = []
7127 if password:
7128 attributes.append(("password", password))
7130 attributes.append(("sqref", cell_range))
7131 attributes.append(("name", range_name))
7133 self._xml_empty_tag("protectedRange", attributes)
7135 def _write_drawings(self):
7136 # Write the <drawing> elements.
7137 if not self.drawing:
7138 return
7140 self.rel_count += 1
7141 self._write_drawing(self.rel_count)
7143 def _write_drawing(self, drawing_id):
7144 # Write the <drawing> element.
7145 r_id = "rId" + str(drawing_id)
7147 attributes = [("r:id", r_id)]
7149 self._xml_empty_tag("drawing", attributes)
7151 def _write_legacy_drawing(self):
7152 # Write the <legacyDrawing> element.
7153 if not self.has_vml:
7154 return
7156 # Increment the relationship id for any drawings or comments.
7157 self.rel_count += 1
7158 r_id = "rId" + str(self.rel_count)
7160 attributes = [("r:id", r_id)]
7162 self._xml_empty_tag("legacyDrawing", attributes)
7164 def _write_legacy_drawing_hf(self):
7165 # Write the <legacyDrawingHF> element.
7166 if not self.has_header_vml:
7167 return
7169 # Increment the relationship id for any drawings or comments.
7170 self.rel_count += 1
7171 r_id = "rId" + str(self.rel_count)
7173 attributes = [("r:id", r_id)]
7175 self._xml_empty_tag("legacyDrawingHF", attributes)
7177 def _write_picture(self):
7178 # Write the <picture> element.
7179 if not self.background_image:
7180 return
7182 # Increment the relationship id.
7183 self.rel_count += 1
7184 r_id = "rId" + str(self.rel_count)
7186 attributes = [("r:id", r_id)]
7188 self._xml_empty_tag("picture", attributes)
7190 def _write_data_validations(self):
7191 # Write the <dataValidations> element.
7192 validations = self.validations
7193 count = len(validations)
7195 if not count:
7196 return
7198 attributes = [("count", count)]
7200 self._xml_start_tag("dataValidations", attributes)
7202 for validation in validations:
7203 # Write the dataValidation element.
7204 self._write_data_validation(validation)
7206 self._xml_end_tag("dataValidations")
7208 def _write_data_validation(self, options):
7209 # Write the <dataValidation> element.
7210 sqref = ""
7211 attributes = []
7213 # Set the cell range(s) for the data validation.
7214 for cells in options["cells"]:
7215 # Add a space between multiple cell ranges.
7216 if sqref != "":
7217 sqref += " "
7219 (row_first, col_first, row_last, col_last) = cells
7221 # Swap last row/col for first row/col as necessary
7222 if row_first > row_last:
7223 (row_first, row_last) = (row_last, row_first)
7225 if col_first > col_last:
7226 (col_first, col_last) = (col_last, col_first)
7228 sqref += xl_range(row_first, col_first, row_last, col_last)
7230 if options.get("multi_range"):
7231 sqref = options["multi_range"]
7233 if options["validate"] != "none":
7234 attributes.append(("type", options["validate"]))
7236 if options["criteria"] != "between":
7237 attributes.append(("operator", options["criteria"]))
7239 if "error_type" in options:
7240 if options["error_type"] == 1:
7241 attributes.append(("errorStyle", "warning"))
7242 if options["error_type"] == 2:
7243 attributes.append(("errorStyle", "information"))
7245 if options["ignore_blank"]:
7246 attributes.append(("allowBlank", 1))
7248 if not options["dropdown"]:
7249 attributes.append(("showDropDown", 1))
7251 if options["show_input"]:
7252 attributes.append(("showInputMessage", 1))
7254 if options["show_error"]:
7255 attributes.append(("showErrorMessage", 1))
7257 if "error_title" in options:
7258 attributes.append(("errorTitle", options["error_title"]))
7260 if "error_message" in options:
7261 attributes.append(("error", options["error_message"]))
7263 if "input_title" in options:
7264 attributes.append(("promptTitle", options["input_title"]))
7266 if "input_message" in options:
7267 attributes.append(("prompt", options["input_message"]))
7269 attributes.append(("sqref", sqref))
7271 if options["validate"] == "none":
7272 self._xml_empty_tag("dataValidation", attributes)
7273 else:
7274 self._xml_start_tag("dataValidation", attributes)
7276 # Write the formula1 element.
7277 self._write_formula_1(options["value"])
7279 # Write the formula2 element.
7280 if options["maximum"] is not None:
7281 self._write_formula_2(options["maximum"])
7283 self._xml_end_tag("dataValidation")
7285 def _write_formula_1(self, formula):
7286 # Write the <formula1> element.
7288 if isinstance(formula, list):
7289 formula = self._csv_join(*formula)
7290 formula = f'"{formula}"'
7291 else:
7292 # Check if the formula is a number.
7293 try:
7294 float(formula)
7295 except ValueError:
7296 # Not a number. Remove the formula '=' sign if it exists.
7297 if formula.startswith("="):
7298 formula = formula.lstrip("=")
7300 self._xml_data_element("formula1", formula)
7302 def _write_formula_2(self, formula):
7303 # Write the <formula2> element.
7305 # Check if the formula is a number.
7306 try:
7307 float(formula)
7308 except ValueError:
7309 # Not a number. Remove the formula '=' sign if it exists.
7310 if formula.startswith("="):
7311 formula = formula.lstrip("=")
7313 self._xml_data_element("formula2", formula)
7315 def _write_conditional_formats(self):
7316 # Write the Worksheet conditional formats.
7317 ranges = sorted(self.cond_formats.keys())
7319 if not ranges:
7320 return
7322 for cond_range in ranges:
7323 self._write_conditional_formatting(
7324 cond_range, self.cond_formats[cond_range]
7325 )
7327 def _write_conditional_formatting(self, cond_range, params):
7328 # Write the <conditionalFormatting> element.
7329 attributes = [("sqref", cond_range)]
7330 self._xml_start_tag("conditionalFormatting", attributes)
7331 for param in params:
7332 # Write the cfRule element.
7333 self._write_cf_rule(param)
7334 self._xml_end_tag("conditionalFormatting")
7336 def _write_cf_rule(self, params):
7337 # Write the <cfRule> element.
7338 attributes = [("type", params["type"])]
7340 if "format" in params and params["format"] is not None:
7341 attributes.append(("dxfId", params["format"]))
7343 attributes.append(("priority", params["priority"]))
7345 if params.get("stop_if_true"):
7346 attributes.append(("stopIfTrue", 1))
7348 if params["type"] == "cellIs":
7349 attributes.append(("operator", params["criteria"]))
7351 self._xml_start_tag("cfRule", attributes)
7353 if "minimum" in params and "maximum" in params:
7354 self._write_formula_element(params["minimum"])
7355 self._write_formula_element(params["maximum"])
7356 else:
7357 self._write_formula_element(params["value"])
7359 self._xml_end_tag("cfRule")
7361 elif params["type"] == "aboveAverage":
7362 if re.search("below", params["criteria"]):
7363 attributes.append(("aboveAverage", 0))
7365 if re.search("equal", params["criteria"]):
7366 attributes.append(("equalAverage", 1))
7368 if re.search("[123] std dev", params["criteria"]):
7369 match = re.search("([123]) std dev", params["criteria"])
7370 attributes.append(("stdDev", match.group(1)))
7372 self._xml_empty_tag("cfRule", attributes)
7374 elif params["type"] == "top10":
7375 if "criteria" in params and params["criteria"] == "%":
7376 attributes.append(("percent", 1))
7378 if "direction" in params:
7379 attributes.append(("bottom", 1))
7381 rank = params["value"] or 10
7382 attributes.append(("rank", rank))
7384 self._xml_empty_tag("cfRule", attributes)
7386 elif params["type"] == "duplicateValues":
7387 self._xml_empty_tag("cfRule", attributes)
7389 elif params["type"] == "uniqueValues":
7390 self._xml_empty_tag("cfRule", attributes)
7392 elif (
7393 params["type"] == "containsText"
7394 or params["type"] == "notContainsText"
7395 or params["type"] == "beginsWith"
7396 or params["type"] == "endsWith"
7397 ):
7398 attributes.append(("operator", params["criteria"]))
7399 attributes.append(("text", params["value"]))
7400 self._xml_start_tag("cfRule", attributes)
7401 self._write_formula_element(params["formula"])
7402 self._xml_end_tag("cfRule")
7404 elif params["type"] == "timePeriod":
7405 attributes.append(("timePeriod", params["criteria"]))
7406 self._xml_start_tag("cfRule", attributes)
7407 self._write_formula_element(params["formula"])
7408 self._xml_end_tag("cfRule")
7410 elif (
7411 params["type"] == "containsBlanks"
7412 or params["type"] == "notContainsBlanks"
7413 or params["type"] == "containsErrors"
7414 or params["type"] == "notContainsErrors"
7415 ):
7416 self._xml_start_tag("cfRule", attributes)
7417 self._write_formula_element(params["formula"])
7418 self._xml_end_tag("cfRule")
7420 elif params["type"] == "colorScale":
7421 self._xml_start_tag("cfRule", attributes)
7422 self._write_color_scale(params)
7423 self._xml_end_tag("cfRule")
7425 elif params["type"] == "dataBar":
7426 self._xml_start_tag("cfRule", attributes)
7427 self._write_data_bar(params)
7429 if params.get("is_data_bar_2010"):
7430 self._write_data_bar_ext(params)
7432 self._xml_end_tag("cfRule")
7434 elif params["type"] == "expression":
7435 self._xml_start_tag("cfRule", attributes)
7436 self._write_formula_element(params["criteria"])
7437 self._xml_end_tag("cfRule")
7439 elif params["type"] == "iconSet":
7440 self._xml_start_tag("cfRule", attributes)
7441 self._write_icon_set(params)
7442 self._xml_end_tag("cfRule")
7444 def _write_formula_element(self, formula):
7445 # Write the <formula> element.
7447 # Check if the formula is a number.
7448 try:
7449 float(formula)
7450 except ValueError:
7451 # Not a number. Remove the formula '=' sign if it exists.
7452 if formula.startswith("="):
7453 formula = formula.lstrip("=")
7455 self._xml_data_element("formula", formula)
7457 def _write_color_scale(self, param):
7458 # Write the <colorScale> element.
7460 self._xml_start_tag("colorScale")
7462 self._write_cfvo(param["min_type"], param["min_value"])
7464 if param["mid_type"] is not None:
7465 self._write_cfvo(param["mid_type"], param["mid_value"])
7467 self._write_cfvo(param["max_type"], param["max_value"])
7469 self._write_color("color", param["min_color"]._attributes())
7471 if param["mid_color"] is not None:
7472 self._write_color("color", param["mid_color"]._attributes())
7474 self._write_color("color", param["max_color"]._attributes())
7476 self._xml_end_tag("colorScale")
7478 def _write_data_bar(self, param):
7479 # Write the <dataBar> element.
7480 attributes = []
7482 # Min and max bar lengths in in the spec but not supported directly by
7483 # Excel.
7484 if "min_length" in param:
7485 attributes.append(("minLength", param["min_length"]))
7487 if "max_length" in param:
7488 attributes.append(("maxLength", param["max_length"]))
7490 if param.get("bar_only"):
7491 attributes.append(("showValue", 0))
7493 self._xml_start_tag("dataBar", attributes)
7495 self._write_cfvo(param["min_type"], param["min_value"])
7496 self._write_cfvo(param["max_type"], param["max_value"])
7497 self._write_color("color", param["bar_color"]._attributes())
7499 self._xml_end_tag("dataBar")
7501 def _write_data_bar_ext(self, param):
7502 # Write the <extLst> dataBar extension element.
7504 # Create a pseudo GUID for each unique Excel 2010 data bar.
7505 worksheet_count = self.index + 1
7506 data_bar_count = len(self.data_bars_2010) + 1
7507 guid = "{DA7ABA51-AAAA-BBBB-%04X-%012X}" % (worksheet_count, data_bar_count)
7509 # Store the 2010 data bar parameters to write the extLst elements.
7510 param["guid"] = guid
7511 self.data_bars_2010.append(param)
7513 self._xml_start_tag("extLst")
7514 self._write_ext("{B025F937-C7B1-47D3-B67F-A62EFF666E3E}")
7515 self._xml_data_element("x14:id", guid)
7516 self._xml_end_tag("ext")
7517 self._xml_end_tag("extLst")
7519 def _write_icon_set(self, param):
7520 # Write the <iconSet> element.
7521 attributes = []
7523 # Don't set attribute for default style.
7524 if param["icon_style"] != "3TrafficLights":
7525 attributes = [("iconSet", param["icon_style"])]
7527 if param.get("icons_only"):
7528 attributes.append(("showValue", 0))
7530 if param.get("reverse_icons"):
7531 attributes.append(("reverse", 1))
7533 self._xml_start_tag("iconSet", attributes)
7535 # Write the properties for different icon styles.
7536 for icon in reversed(param["icons"]):
7537 self._write_cfvo(icon["type"], icon["value"], icon["criteria"])
7539 self._xml_end_tag("iconSet")
7541 def _write_cfvo(self, cf_type, val, criteria=None):
7542 # Write the <cfvo> element.
7543 attributes = [("type", cf_type)]
7545 if val is not None:
7546 attributes.append(("val", val))
7548 if criteria:
7549 attributes.append(("gte", 0))
7551 self._xml_empty_tag("cfvo", attributes)
7553 def _write_color(self, name, attributes):
7554 # Write the <color> element.
7555 self._xml_empty_tag(name, attributes)
7557 def _write_selections(self):
7558 # Write the <selection> elements.
7559 for selection in self.selections:
7560 self._write_selection(*selection)
7562 def _write_selection(self, pane, active_cell, sqref):
7563 # Write the <selection> element.
7564 attributes = []
7566 if pane:
7567 attributes.append(("pane", pane))
7569 if active_cell:
7570 attributes.append(("activeCell", active_cell))
7572 if sqref:
7573 attributes.append(("sqref", sqref))
7575 self._xml_empty_tag("selection", attributes)
7577 def _write_panes(self):
7578 # Write the frozen or split <pane> elements.
7579 panes = self.panes
7581 if not panes:
7582 return
7584 if panes[4] == 2:
7585 self._write_split_panes(*panes)
7586 else:
7587 self._write_freeze_panes(*panes)
7589 def _write_freeze_panes(self, row, col, top_row, left_col, pane_type):
7590 # Write the <pane> element for freeze panes.
7591 attributes = []
7593 y_split = row
7594 x_split = col
7595 top_left_cell = xl_rowcol_to_cell(top_row, left_col)
7596 active_pane = ""
7597 state = ""
7598 active_cell = ""
7599 sqref = ""
7601 # Move user cell selection to the panes.
7602 if self.selections:
7603 (_, active_cell, sqref) = self.selections[0]
7604 self.selections = []
7606 # Set the active pane.
7607 if row and col:
7608 active_pane = "bottomRight"
7610 row_cell = xl_rowcol_to_cell(row, 0)
7611 col_cell = xl_rowcol_to_cell(0, col)
7613 self.selections.append(["topRight", col_cell, col_cell])
7614 self.selections.append(["bottomLeft", row_cell, row_cell])
7615 self.selections.append(["bottomRight", active_cell, sqref])
7617 elif col:
7618 active_pane = "topRight"
7619 self.selections.append(["topRight", active_cell, sqref])
7621 else:
7622 active_pane = "bottomLeft"
7623 self.selections.append(["bottomLeft", active_cell, sqref])
7625 # Set the pane type.
7626 if pane_type == 0:
7627 state = "frozen"
7628 elif pane_type == 1:
7629 state = "frozenSplit"
7630 else:
7631 state = "split"
7633 if x_split:
7634 attributes.append(("xSplit", x_split))
7636 if y_split:
7637 attributes.append(("ySplit", y_split))
7639 attributes.append(("topLeftCell", top_left_cell))
7640 attributes.append(("activePane", active_pane))
7641 attributes.append(("state", state))
7643 self._xml_empty_tag("pane", attributes)
7645 def _write_split_panes(self, row, col, top_row, left_col, _):
7646 # Write the <pane> element for split panes.
7647 attributes = []
7648 has_selection = False
7649 active_pane = ""
7650 active_cell = ""
7651 sqref = ""
7653 y_split = row
7654 x_split = col
7656 # Move user cell selection to the panes.
7657 if self.selections:
7658 (_, active_cell, sqref) = self.selections[0]
7659 self.selections = []
7660 has_selection = True
7662 # Convert the row and col to 1/20 twip units with padding.
7663 if y_split:
7664 y_split = int(20 * y_split + 300)
7666 if x_split:
7667 x_split = self._calculate_x_split_width(x_split)
7669 # For non-explicit topLeft definitions, estimate the cell offset based
7670 # on the pixels dimensions. This is only a workaround and doesn't take
7671 # adjusted cell dimensions into account.
7672 if top_row == row and left_col == col:
7673 top_row = int(0.5 + (y_split - 300) / 20 / 15)
7674 left_col = int(0.5 + (x_split - 390) / 20 / 3 * 4 / 64)
7676 top_left_cell = xl_rowcol_to_cell(top_row, left_col)
7678 # If there is no selection set the active cell to the top left cell.
7679 if not has_selection:
7680 active_cell = top_left_cell
7681 sqref = top_left_cell
7683 # Set the Cell selections.
7684 if row and col:
7685 active_pane = "bottomRight"
7687 row_cell = xl_rowcol_to_cell(top_row, 0)
7688 col_cell = xl_rowcol_to_cell(0, left_col)
7690 self.selections.append(["topRight", col_cell, col_cell])
7691 self.selections.append(["bottomLeft", row_cell, row_cell])
7692 self.selections.append(["bottomRight", active_cell, sqref])
7694 elif col:
7695 active_pane = "topRight"
7696 self.selections.append(["topRight", active_cell, sqref])
7698 else:
7699 active_pane = "bottomLeft"
7700 self.selections.append(["bottomLeft", active_cell, sqref])
7702 # Format splits to the same precision as Excel.
7703 if x_split:
7704 attributes.append(("xSplit", f"{x_split:.16g}"))
7706 if y_split:
7707 attributes.append(("ySplit", f"{y_split:.16g}"))
7709 attributes.append(("topLeftCell", top_left_cell))
7711 if has_selection:
7712 attributes.append(("activePane", active_pane))
7714 self._xml_empty_tag("pane", attributes)
7716 def _calculate_x_split_width(self, width):
7717 # Convert column width from user units to pane split width.
7719 max_digit_width = 7 # For Calabri 11.
7720 padding = 5
7722 # Convert to pixels.
7723 if width < 1:
7724 pixels = int(width * (max_digit_width + padding) + 0.5)
7725 else:
7726 pixels = int(width * max_digit_width + 0.5) + padding
7728 # Convert to points.
7729 points = pixels * 3 / 4
7731 # Convert to twips (twentieths of a point).
7732 twips = points * 20
7734 # Add offset/padding.
7735 width = twips + 390
7737 return width
7739 def _write_table_parts(self):
7740 # Write the <tableParts> element.
7741 tables = self.tables
7742 count = len(tables)
7744 # Return if worksheet doesn't contain any tables.
7745 if not count:
7746 return
7748 attributes = [
7749 (
7750 "count",
7751 count,
7752 )
7753 ]
7755 self._xml_start_tag("tableParts", attributes)
7757 for _ in tables:
7758 # Write the tablePart element.
7759 self.rel_count += 1
7760 self._write_table_part(self.rel_count)
7762 self._xml_end_tag("tableParts")
7764 def _write_table_part(self, r_id):
7765 # Write the <tablePart> element.
7767 r_id = "rId" + str(r_id)
7769 attributes = [
7770 (
7771 "r:id",
7772 r_id,
7773 )
7774 ]
7776 self._xml_empty_tag("tablePart", attributes)
7778 def _write_ext_list(self):
7779 # Write the <extLst> element for data bars and sparklines.
7780 has_data_bars = len(self.data_bars_2010)
7781 has_sparklines = len(self.sparklines)
7783 if not has_data_bars and not has_sparklines:
7784 return
7786 # Write the extLst element.
7787 self._xml_start_tag("extLst")
7789 if has_data_bars:
7790 self._write_ext_list_data_bars()
7792 if has_sparklines:
7793 self._write_ext_list_sparklines()
7795 self._xml_end_tag("extLst")
7797 def _write_ext_list_data_bars(self):
7798 # Write the Excel 2010 data_bar subelements.
7799 self._write_ext("{78C0D931-6437-407d-A8EE-F0AAD7539E65}")
7801 self._xml_start_tag("x14:conditionalFormattings")
7803 # Write the Excel 2010 conditional formatting data bar elements.
7804 for data_bar in self.data_bars_2010:
7805 # Write the x14:conditionalFormatting element.
7806 self._write_conditional_formatting_2010(data_bar)
7808 self._xml_end_tag("x14:conditionalFormattings")
7809 self._xml_end_tag("ext")
7811 def _write_conditional_formatting_2010(self, data_bar):
7812 # Write the <x14:conditionalFormatting> element.
7813 xmlns_xm = "http://schemas.microsoft.com/office/excel/2006/main"
7815 attributes = [("xmlns:xm", xmlns_xm)]
7817 self._xml_start_tag("x14:conditionalFormatting", attributes)
7819 # Write the x14:cfRule element.
7820 self._write_x14_cf_rule(data_bar)
7822 # Write the x14:dataBar element.
7823 self._write_x14_data_bar(data_bar)
7825 # Write the x14 max and min data bars.
7826 self._write_x14_cfvo(data_bar["x14_min_type"], data_bar["min_value"])
7827 self._write_x14_cfvo(data_bar["x14_max_type"], data_bar["max_value"])
7829 if not data_bar["bar_no_border"]:
7830 # Write the x14:borderColor element.
7831 self._write_x14_border_color(data_bar["bar_border_color"])
7833 # Write the x14:negativeFillColor element.
7834 if not data_bar["bar_negative_color_same"]:
7835 self._write_x14_negative_fill_color(data_bar["bar_negative_color"])
7837 # Write the x14:negativeBorderColor element.
7838 if (
7839 not data_bar["bar_no_border"]
7840 and not data_bar["bar_negative_border_color_same"]
7841 ):
7842 self._write_x14_negative_border_color(data_bar["bar_negative_border_color"])
7844 # Write the x14:axisColor element.
7845 if data_bar["bar_axis_position"] != "none":
7846 self._write_x14_axis_color(data_bar["bar_axis_color"])
7848 self._xml_end_tag("x14:dataBar")
7849 self._xml_end_tag("x14:cfRule")
7851 # Write the xm:sqref element.
7852 self._xml_data_element("xm:sqref", data_bar["range"])
7854 self._xml_end_tag("x14:conditionalFormatting")
7856 def _write_x14_cf_rule(self, data_bar):
7857 # Write the <x14:cfRule> element.
7858 rule_type = "dataBar"
7859 guid = data_bar["guid"]
7860 attributes = [("type", rule_type), ("id", guid)]
7862 self._xml_start_tag("x14:cfRule", attributes)
7864 def _write_x14_data_bar(self, data_bar):
7865 # Write the <x14:dataBar> element.
7866 min_length = 0
7867 max_length = 100
7869 attributes = [
7870 ("minLength", min_length),
7871 ("maxLength", max_length),
7872 ]
7874 if not data_bar["bar_no_border"]:
7875 attributes.append(("border", 1))
7877 if data_bar["bar_solid"]:
7878 attributes.append(("gradient", 0))
7880 if data_bar["bar_direction"] == "left":
7881 attributes.append(("direction", "leftToRight"))
7883 if data_bar["bar_direction"] == "right":
7884 attributes.append(("direction", "rightToLeft"))
7886 if data_bar["bar_negative_color_same"]:
7887 attributes.append(("negativeBarColorSameAsPositive", 1))
7889 if (
7890 not data_bar["bar_no_border"]
7891 and not data_bar["bar_negative_border_color_same"]
7892 ):
7893 attributes.append(("negativeBarBorderColorSameAsPositive", 0))
7895 if data_bar["bar_axis_position"] == "middle":
7896 attributes.append(("axisPosition", "middle"))
7898 if data_bar["bar_axis_position"] == "none":
7899 attributes.append(("axisPosition", "none"))
7901 self._xml_start_tag("x14:dataBar", attributes)
7903 def _write_x14_cfvo(self, rule_type, value):
7904 # Write the <x14:cfvo> element.
7905 attributes = [("type", rule_type)]
7907 if rule_type in ("min", "max", "autoMin", "autoMax"):
7908 self._xml_empty_tag("x14:cfvo", attributes)
7909 else:
7910 self._xml_start_tag("x14:cfvo", attributes)
7911 self._xml_data_element("xm:f", value)
7912 self._xml_end_tag("x14:cfvo")
7914 def _write_x14_border_color(self, color):
7915 # Write the <x14:borderColor> element.
7916 self._write_color("x14:borderColor", color._attributes())
7918 def _write_x14_negative_fill_color(self, color):
7919 # Write the <x14:negativeFillColor> element.
7920 self._xml_empty_tag("x14:negativeFillColor", color._attributes())
7922 def _write_x14_negative_border_color(self, color):
7923 # Write the <x14:negativeBorderColor> element.
7924 self._xml_empty_tag("x14:negativeBorderColor", color._attributes())
7926 def _write_x14_axis_color(self, color):
7927 # Write the <x14:axisColor> element.
7928 self._xml_empty_tag("x14:axisColor", color._attributes())
7930 def _write_ext_list_sparklines(self):
7931 # Write the sparkline extension sub-elements.
7932 self._write_ext("{05C60535-1F16-4fd2-B633-F4F36F0B64E0}")
7934 # Write the x14:sparklineGroups element.
7935 self._write_sparkline_groups()
7937 # Write the sparkline elements.
7938 for sparkline in reversed(self.sparklines):
7939 # Write the x14:sparklineGroup element.
7940 self._write_sparkline_group(sparkline)
7942 # Write the x14:colorSeries element.
7943 self._write_color_series(sparkline["series_color"])
7945 # Write the x14:colorNegative element.
7946 self._write_color_negative(sparkline["negative_color"])
7948 # Write the x14:colorAxis element.
7949 self._write_color_axis()
7951 # Write the x14:colorMarkers element.
7952 self._write_color_markers(sparkline["markers_color"])
7954 # Write the x14:colorFirst element.
7955 self._write_color_first(sparkline["first_color"])
7957 # Write the x14:colorLast element.
7958 self._write_color_last(sparkline["last_color"])
7960 # Write the x14:colorHigh element.
7961 self._write_color_high(sparkline["high_color"])
7963 # Write the x14:colorLow element.
7964 self._write_color_low(sparkline["low_color"])
7966 if sparkline["date_axis"]:
7967 self._xml_data_element("xm:f", sparkline["date_axis"])
7969 self._write_sparklines(sparkline)
7971 self._xml_end_tag("x14:sparklineGroup")
7973 self._xml_end_tag("x14:sparklineGroups")
7974 self._xml_end_tag("ext")
7976 def _write_sparklines(self, sparkline):
7977 # Write the <x14:sparklines> element and <x14:sparkline> sub-elements.
7979 # Write the sparkline elements.
7980 self._xml_start_tag("x14:sparklines")
7982 for i in range(sparkline["count"]):
7983 spark_range = sparkline["ranges"][i]
7984 location = sparkline["locations"][i]
7986 self._xml_start_tag("x14:sparkline")
7987 self._xml_data_element("xm:f", spark_range)
7988 self._xml_data_element("xm:sqref", location)
7989 self._xml_end_tag("x14:sparkline")
7991 self._xml_end_tag("x14:sparklines")
7993 def _write_ext(self, uri):
7994 # Write the <ext> element.
7995 schema = "http://schemas.microsoft.com/office/"
7996 xmlns_x14 = schema + "spreadsheetml/2009/9/main"
7998 attributes = [
7999 ("xmlns:x14", xmlns_x14),
8000 ("uri", uri),
8001 ]
8003 self._xml_start_tag("ext", attributes)
8005 def _write_sparkline_groups(self):
8006 # Write the <x14:sparklineGroups> element.
8007 xmlns_xm = "http://schemas.microsoft.com/office/excel/2006/main"
8009 attributes = [("xmlns:xm", xmlns_xm)]
8011 self._xml_start_tag("x14:sparklineGroups", attributes)
8013 def _write_sparkline_group(self, options):
8014 # Write the <x14:sparklineGroup> element.
8015 #
8016 # Example for order.
8017 #
8018 # <x14:sparklineGroup
8019 # manualMax="0"
8020 # manualMin="0"
8021 # lineWeight="2.25"
8022 # type="column"
8023 # dateAxis="1"
8024 # displayEmptyCellsAs="span"
8025 # markers="1"
8026 # high="1"
8027 # low="1"
8028 # first="1"
8029 # last="1"
8030 # negative="1"
8031 # displayXAxis="1"
8032 # displayHidden="1"
8033 # minAxisType="custom"
8034 # maxAxisType="custom"
8035 # rightToLeft="1">
8036 #
8037 empty = options.get("empty")
8038 attributes = []
8040 if options.get("max") is not None:
8041 if options["max"] == "group":
8042 options["cust_max"] = "group"
8043 else:
8044 attributes.append(("manualMax", options["max"]))
8045 options["cust_max"] = "custom"
8047 if options.get("min") is not None:
8048 if options["min"] == "group":
8049 options["cust_min"] = "group"
8050 else:
8051 attributes.append(("manualMin", options["min"]))
8052 options["cust_min"] = "custom"
8054 # Ignore the default type attribute (line).
8055 if options["type"] != "line":
8056 attributes.append(("type", options["type"]))
8058 if options.get("weight"):
8059 attributes.append(("lineWeight", options["weight"]))
8061 if options.get("date_axis"):
8062 attributes.append(("dateAxis", 1))
8064 if empty:
8065 attributes.append(("displayEmptyCellsAs", empty))
8067 if options.get("markers"):
8068 attributes.append(("markers", 1))
8070 if options.get("high"):
8071 attributes.append(("high", 1))
8073 if options.get("low"):
8074 attributes.append(("low", 1))
8076 if options.get("first"):
8077 attributes.append(("first", 1))
8079 if options.get("last"):
8080 attributes.append(("last", 1))
8082 if options.get("negative"):
8083 attributes.append(("negative", 1))
8085 if options.get("axis"):
8086 attributes.append(("displayXAxis", 1))
8088 if options.get("hidden"):
8089 attributes.append(("displayHidden", 1))
8091 if options.get("cust_min"):
8092 attributes.append(("minAxisType", options["cust_min"]))
8094 if options.get("cust_max"):
8095 attributes.append(("maxAxisType", options["cust_max"]))
8097 if options.get("reverse"):
8098 attributes.append(("rightToLeft", 1))
8100 self._xml_start_tag("x14:sparklineGroup", attributes)
8102 def _write_spark_color(self, tag, color):
8103 # Helper function for the sparkline color functions below.
8104 if color:
8105 self._write_color(tag, color._attributes())
8107 def _write_color_series(self, color):
8108 # Write the <x14:colorSeries> element.
8109 self._write_spark_color("x14:colorSeries", color)
8111 def _write_color_negative(self, color):
8112 # Write the <x14:colorNegative> element.
8113 self._write_spark_color("x14:colorNegative", color)
8115 def _write_color_axis(self):
8116 # Write the <x14:colorAxis> element.
8117 self._write_spark_color("x14:colorAxis", Color("#000000"))
8119 def _write_color_markers(self, color):
8120 # Write the <x14:colorMarkers> element.
8121 self._write_spark_color("x14:colorMarkers", color)
8123 def _write_color_first(self, color):
8124 # Write the <x14:colorFirst> element.
8125 self._write_spark_color("x14:colorFirst", color)
8127 def _write_color_last(self, color):
8128 # Write the <x14:colorLast> element.
8129 self._write_spark_color("x14:colorLast", color)
8131 def _write_color_high(self, color):
8132 # Write the <x14:colorHigh> element.
8133 self._write_spark_color("x14:colorHigh", color)
8135 def _write_color_low(self, color):
8136 # Write the <x14:colorLow> element.
8137 self._write_spark_color("x14:colorLow", color)
8139 def _write_phonetic_pr(self):
8140 # Write the <phoneticPr> element.
8141 attributes = [
8142 ("fontId", "0"),
8143 ("type", "noConversion"),
8144 ]
8146 self._xml_empty_tag("phoneticPr", attributes)
8148 def _write_ignored_errors(self):
8149 # Write the <ignoredErrors> element.
8150 if not self.ignored_errors:
8151 return
8153 self._xml_start_tag("ignoredErrors")
8155 if self.ignored_errors.get("number_stored_as_text"):
8156 ignored_range = self.ignored_errors["number_stored_as_text"]
8157 self._write_ignored_error("numberStoredAsText", ignored_range)
8159 if self.ignored_errors.get("eval_error"):
8160 ignored_range = self.ignored_errors["eval_error"]
8161 self._write_ignored_error("evalError", ignored_range)
8163 if self.ignored_errors.get("formula_differs"):
8164 ignored_range = self.ignored_errors["formula_differs"]
8165 self._write_ignored_error("formula", ignored_range)
8167 if self.ignored_errors.get("formula_range"):
8168 ignored_range = self.ignored_errors["formula_range"]
8169 self._write_ignored_error("formulaRange", ignored_range)
8171 if self.ignored_errors.get("formula_unlocked"):
8172 ignored_range = self.ignored_errors["formula_unlocked"]
8173 self._write_ignored_error("unlockedFormula", ignored_range)
8175 if self.ignored_errors.get("empty_cell_reference"):
8176 ignored_range = self.ignored_errors["empty_cell_reference"]
8177 self._write_ignored_error("emptyCellReference", ignored_range)
8179 if self.ignored_errors.get("list_data_validation"):
8180 ignored_range = self.ignored_errors["list_data_validation"]
8181 self._write_ignored_error("listDataValidation", ignored_range)
8183 if self.ignored_errors.get("calculated_column"):
8184 ignored_range = self.ignored_errors["calculated_column"]
8185 self._write_ignored_error("calculatedColumn", ignored_range)
8187 if self.ignored_errors.get("two_digit_text_year"):
8188 ignored_range = self.ignored_errors["two_digit_text_year"]
8189 self._write_ignored_error("twoDigitTextYear", ignored_range)
8191 self._xml_end_tag("ignoredErrors")
8193 def _write_ignored_error(self, error_type, ignored_range):
8194 # Write the <ignoredError> element.
8195 attributes = [
8196 ("sqref", ignored_range),
8197 (error_type, 1),
8198 ]
8200 self._xml_empty_tag("ignoredError", attributes)