Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.8/site-packages/xlsxwriter/worksheet.py: 22%
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# Copyright 2013-2024, John McNamara, jmcnamara@cpan.org
7#
9# Standard packages.
10import datetime
11import math
12import os
13import re
14import tempfile
16from collections import defaultdict
17from collections import namedtuple
18from decimal import Decimal
19from fractions import Fraction
20from functools import wraps
21from io import StringIO
22from math import isinf
23from math import isnan
24from warnings import warn
26# Package imports.
27from . import xmlwriter
28from .format import Format
29from .drawing import Drawing
30from .shape import Shape
31from .xmlwriter import XMLwriter
32from .utility import xl_rowcol_to_cell
33from .utility import xl_rowcol_to_cell_fast
34from .utility import xl_cell_to_rowcol
35from .utility import xl_col_to_name
36from .utility import xl_range
37from .utility import xl_color
38from .utility import xl_pixel_width
39from .utility import get_sparkline_style
40from .utility import supported_datetime
41from .utility import datetime_to_excel_datetime
42from .utility import get_image_properties
43from .utility import preserve_whitespace
44from .utility import quote_sheetname
45from .exceptions import DuplicateTableName
46from .exceptions import OverlappingRange
48re_dynamic_function = re.compile(
49 r"""
50 \bANCHORARRAY\( |
51 \bBYCOL\( |
52 \bBYROW\( |
53 \bCHOOSECOLS\( |
54 \bCHOOSEROWS\( |
55 \bDROP\( |
56 \bEXPAND\( |
57 \bFILTER\( |
58 \bHSTACK\( |
59 \bLAMBDA\( |
60 \bMAKEARRAY\( |
61 \bMAP\( |
62 \bRANDARRAY\( |
63 \bREDUCE\( |
64 \bSCAN\( |
65 \bSEQUENCE\( |
66 \bSINGLE\( |
67 \bSORT\( |
68 \bSORTBY\( |
69 \bSWITCH\( |
70 \bTAKE\( |
71 \bTEXTSPLIT\( |
72 \bTOCOL\( |
73 \bTOROW\( |
74 \bUNIQUE\( |
75 \bVSTACK\( |
76 \bWRAPCOLS\( |
77 \bWRAPROWS\( |
78 \bXLOOKUP\(""",
79 re.VERBOSE,
80)
83###############################################################################
84#
85# Decorator functions.
86#
87###############################################################################
88def convert_cell_args(method):
89 """
90 Decorator function to convert A1 notation in cell method calls
91 to the default row/col notation.
93 """
95 @wraps(method)
96 def cell_wrapper(self, *args, **kwargs):
97 try:
98 # First arg is an int, default to row/col notation.
99 if args:
100 first_arg = args[0]
101 int(first_arg)
102 except ValueError:
103 # First arg isn't an int, convert to A1 notation.
104 new_args = xl_cell_to_rowcol(first_arg)
105 args = new_args + args[1:]
107 return method(self, *args, **kwargs)
109 return cell_wrapper
112def convert_range_args(method):
113 """
114 Decorator function to convert A1 notation in range method calls
115 to the default row/col notation.
117 """
119 @wraps(method)
120 def cell_wrapper(self, *args, **kwargs):
121 try:
122 # First arg is an int, default to row/col notation.
123 if args:
124 int(args[0])
125 except ValueError:
126 # First arg isn't an int, convert to A1 notation.
127 if ":" in args[0]:
128 cell_1, cell_2 = args[0].split(":")
129 row_1, col_1 = xl_cell_to_rowcol(cell_1)
130 row_2, col_2 = xl_cell_to_rowcol(cell_2)
131 else:
132 row_1, col_1 = xl_cell_to_rowcol(args[0])
133 row_2, col_2 = row_1, col_1
135 new_args = [row_1, col_1, row_2, col_2]
136 new_args.extend(args[1:])
137 args = new_args
139 return method(self, *args, **kwargs)
141 return cell_wrapper
144def convert_column_args(method):
145 """
146 Decorator function to convert A1 notation in columns method calls
147 to the default row/col notation.
149 """
151 @wraps(method)
152 def column_wrapper(self, *args, **kwargs):
153 try:
154 # First arg is an int, default to row/col notation.
155 if args:
156 int(args[0])
157 except ValueError:
158 # First arg isn't an int, convert to A1 notation.
159 cell_1, cell_2 = [col + "1" for col in args[0].split(":")]
160 _, col_1 = xl_cell_to_rowcol(cell_1)
161 _, col_2 = xl_cell_to_rowcol(cell_2)
162 new_args = [col_1, col_2]
163 new_args.extend(args[1:])
164 args = new_args
166 return method(self, *args, **kwargs)
168 return column_wrapper
171###############################################################################
172#
173# Named tuples used for cell types.
174#
175###############################################################################
176cell_string_tuple = namedtuple("String", "string, format")
177cell_number_tuple = namedtuple("Number", "number, format")
178cell_blank_tuple = namedtuple("Blank", "format")
179cell_boolean_tuple = namedtuple("Boolean", "boolean, format")
180cell_formula_tuple = namedtuple("Formula", "formula, format, value")
181cell_datetime_tuple = namedtuple("Datetime", "number, format")
182cell_arformula_tuple = namedtuple(
183 "ArrayFormula", "formula, format, value, range, atype"
184)
185cell_rich_string_tuple = namedtuple("RichString", "string, format, raw_string")
186cell_error_tuple = namedtuple("Error", "error, format, value")
189###############################################################################
190#
191# Worksheet Class definition.
192#
193###############################################################################
194class Worksheet(xmlwriter.XMLwriter):
195 """
196 A class for writing the Excel XLSX Worksheet file.
198 """
200 ###########################################################################
201 #
202 # Public API.
203 #
204 ###########################################################################
206 def __init__(self):
207 """
208 Constructor.
210 """
212 super(Worksheet, self).__init__()
214 self.name = None
215 self.index = None
216 self.str_table = None
217 self.palette = None
218 self.constant_memory = 0
219 self.tmpdir = None
220 self.is_chartsheet = False
222 self.ext_sheets = []
223 self.fileclosed = 0
224 self.excel_version = 2007
225 self.excel2003_style = False
227 self.xls_rowmax = 1048576
228 self.xls_colmax = 16384
229 self.xls_strmax = 32767
230 self.dim_rowmin = None
231 self.dim_rowmax = None
232 self.dim_colmin = None
233 self.dim_colmax = None
235 self.col_info = {}
236 self.selections = []
237 self.hidden = 0
238 self.active = 0
239 self.tab_color = 0
240 self.top_left_cell = ""
242 self.panes = []
243 self.active_pane = 3
244 self.selected = 0
246 self.page_setup_changed = False
247 self.paper_size = 0
248 self.orientation = 1
250 self.print_options_changed = False
251 self.hcenter = False
252 self.vcenter = False
253 self.print_gridlines = False
254 self.screen_gridlines = True
255 self.print_headers = False
256 self.row_col_headers = False
258 self.header_footer_changed = False
259 self.header = ""
260 self.footer = ""
261 self.header_footer_aligns = True
262 self.header_footer_scales = True
263 self.header_images = []
264 self.footer_images = []
265 self.header_images_list = []
267 self.margin_left = 0.7
268 self.margin_right = 0.7
269 self.margin_top = 0.75
270 self.margin_bottom = 0.75
271 self.margin_header = 0.3
272 self.margin_footer = 0.3
274 self.repeat_row_range = ""
275 self.repeat_col_range = ""
276 self.print_area_range = ""
278 self.page_order = 0
279 self.black_white = 0
280 self.draft_quality = 0
281 self.print_comments = 0
282 self.page_start = 0
284 self.fit_page = 0
285 self.fit_width = 0
286 self.fit_height = 0
288 self.hbreaks = []
289 self.vbreaks = []
291 self.protect_options = {}
292 self.protected_ranges = []
293 self.num_protected_ranges = 0
294 self.set_cols = {}
295 self.set_rows = defaultdict(dict)
297 self.zoom = 100
298 self.zoom_scale_normal = 1
299 self.print_scale = 100
300 self.is_right_to_left = 0
301 self.show_zeros = 1
302 self.leading_zeros = 0
304 self.outline_row_level = 0
305 self.outline_col_level = 0
306 self.outline_style = 0
307 self.outline_below = 1
308 self.outline_right = 1
309 self.outline_on = 1
310 self.outline_changed = False
312 self.original_row_height = 15
313 self.default_row_height = 15
314 self.default_row_pixels = 20
315 self.default_col_width = 8.43
316 self.default_col_pixels = 64
317 self.default_date_pixels = 68
318 self.default_row_zeroed = 0
320 self.names = {}
321 self.write_match = []
322 self.table = defaultdict(dict)
323 self.merge = []
324 self.merged_cells = {}
325 self.table_cells = {}
326 self.row_spans = {}
328 self.has_vml = False
329 self.has_header_vml = False
330 self.has_comments = False
331 self.comments = defaultdict(dict)
332 self.comments_list = []
333 self.comments_author = ""
334 self.comments_visible = 0
335 self.vml_shape_id = 1024
336 self.buttons_list = []
337 self.vml_header_id = 0
339 self.autofilter_area = ""
340 self.autofilter_ref = None
341 self.filter_range = []
342 self.filter_on = 0
343 self.filter_cols = {}
344 self.filter_type = {}
345 self.filter_cells = {}
347 self.row_sizes = {}
348 self.col_size_changed = False
349 self.row_size_changed = False
351 self.last_shape_id = 1
352 self.rel_count = 0
353 self.hlink_count = 0
354 self.hlink_refs = []
355 self.external_hyper_links = []
356 self.external_drawing_links = []
357 self.external_comment_links = []
358 self.external_vml_links = []
359 self.external_table_links = []
360 self.external_background_links = []
361 self.drawing_links = []
362 self.vml_drawing_links = []
363 self.charts = []
364 self.images = []
365 self.tables = []
366 self.sparklines = []
367 self.shapes = []
368 self.shape_hash = {}
369 self.drawing = 0
370 self.drawing_rels = {}
371 self.drawing_rels_id = 0
372 self.vml_drawing_rels = {}
373 self.vml_drawing_rels_id = 0
374 self.background_image = None
375 self.background_bytes = False
377 self.rstring = ""
378 self.previous_row = 0
380 self.validations = []
381 self.cond_formats = {}
382 self.data_bars_2010 = []
383 self.use_data_bars_2010 = False
384 self.dxf_priority = 1
385 self.page_view = 0
387 self.vba_codename = None
389 self.date_1904 = False
390 self.hyperlinks = defaultdict(dict)
392 self.strings_to_numbers = False
393 self.strings_to_urls = True
394 self.nan_inf_to_errors = False
395 self.strings_to_formulas = True
397 self.default_date_format = None
398 self.default_url_format = None
399 self.remove_timezone = False
400 self.max_url_length = 2079
402 self.row_data_filename = None
403 self.row_data_fh = None
404 self.worksheet_meta = None
405 self.vml_data_id = None
406 self.vml_shape_id = None
408 self.row_data_filename = None
409 self.row_data_fh = None
410 self.row_data_fh_closed = False
412 self.vertical_dpi = 0
413 self.horizontal_dpi = 0
415 self.write_handlers = {}
417 self.ignored_errors = None
419 self.has_dynamic_arrays = False
420 self.use_future_functions = False
421 self.ignore_write_string = False
423 # Utility function for writing different types of strings.
424 def _write_token_as_string(self, token, row, col, *args):
425 # Map the data to the appropriate write_*() method.
426 if token == "":
427 return self._write_blank(row, col, *args)
429 if self.strings_to_formulas and token.startswith("="):
430 return self._write_formula(row, col, *args)
432 if token.startswith("{=") and token.endswith("}"):
433 return self._write_formula(row, col, *args)
435 if (
436 ":" in token
437 and self.strings_to_urls
438 and (
439 re.match("(ftp|http)s?://", token)
440 or re.match("mailto:", token)
441 or re.match("(in|ex)ternal:", token)
442 )
443 ):
444 return self._write_url(row, col, *args)
446 if self.strings_to_numbers:
447 try:
448 f = float(token)
449 if self.nan_inf_to_errors or (not isnan(f) and not isinf(f)):
450 return self._write_number(row, col, f, *args[1:])
451 except ValueError:
452 # Not a number, write as a string.
453 pass
455 return self._write_string(row, col, *args)
457 else:
458 # We have a plain string.
459 return self._write_string(row, col, *args)
461 @convert_cell_args
462 def write(self, row, col, *args):
463 """
464 Write data to a worksheet cell by calling the appropriate write_*()
465 method based on the type of data being passed.
467 Args:
468 row: The cell row (zero indexed).
469 col: The cell column (zero indexed).
470 *args: Args to pass to sub functions.
472 Returns:
473 0: Success.
474 -1: Row or column is out of worksheet bounds.
475 other: Return value of called method.
477 """
478 return self._write(row, col, *args)
480 # Undecorated version of write().
481 def _write(self, row, col, *args):
482 # Check the number of args passed.
483 if not args:
484 raise TypeError("write() takes at least 4 arguments (3 given)")
486 # The first arg should be the token for all write calls.
487 token = args[0]
489 # Avoid isinstance() for better performance.
490 token_type = token.__class__
492 # Check for any user defined type handlers with callback functions.
493 if token_type in self.write_handlers:
494 write_handler = self.write_handlers[token_type]
495 function_return = write_handler(self, row, col, *args)
497 # If the return value is None then the callback has returned
498 # control to this function and we should continue as
499 # normal. Otherwise we return the value to the caller and exit.
500 if function_return is None:
501 pass
502 else:
503 return function_return
505 # Write None as a blank cell.
506 if token is None:
507 return self._write_blank(row, col, *args)
509 # Check for standard Python types.
510 if token_type is bool:
511 return self._write_boolean(row, col, *args)
513 if token_type in (float, int, Decimal, Fraction):
514 return self._write_number(row, col, *args)
516 if token_type is str:
517 return self._write_token_as_string(token, row, col, *args)
519 if token_type in (
520 datetime.datetime,
521 datetime.date,
522 datetime.time,
523 datetime.timedelta,
524 ):
525 return self._write_datetime(row, col, *args)
527 # Resort to isinstance() for subclassed primitives.
529 # Write number types.
530 if isinstance(token, (float, int, Decimal, Fraction)):
531 return self._write_number(row, col, *args)
533 # Write string types.
534 if isinstance(token, str):
535 return self._write_token_as_string(token, row, col, *args)
537 # Write boolean types.
538 if isinstance(token, bool):
539 return self._write_boolean(row, col, *args)
541 # Write datetime objects.
542 if supported_datetime(token):
543 return self._write_datetime(row, col, *args)
545 # We haven't matched a supported type. Try float.
546 try:
547 f = float(token)
548 return self._write_number(row, col, f, *args[1:])
549 except ValueError:
550 pass
551 except TypeError:
552 raise TypeError("Unsupported type %s in write()" % type(token))
554 # Finally try string.
555 try:
556 str(token)
557 return self._write_string(row, col, *args)
558 except ValueError:
559 raise TypeError("Unsupported type %s in write()" % type(token))
561 @convert_cell_args
562 def write_string(self, row, col, string, cell_format=None):
563 """
564 Write a string to a worksheet cell.
566 Args:
567 row: The cell row (zero indexed).
568 col: The cell column (zero indexed).
569 string: Cell data. Str.
570 format: An optional cell Format object.
572 Returns:
573 0: Success.
574 -1: Row or column is out of worksheet bounds.
575 -2: String truncated to 32k characters.
577 """
578 return self._write_string(row, col, string, cell_format)
580 # Undecorated version of write_string().
581 def _write_string(self, row, col, string, cell_format=None):
582 str_error = 0
584 # Check that row and col are valid and store max and min values.
585 if self._check_dimensions(row, col):
586 return -1
588 # Check that the string is < 32767 chars.
589 if len(string) > self.xls_strmax:
590 string = string[: self.xls_strmax]
591 str_error = -2
593 # Write a shared string or an in-line string in constant_memory mode.
594 if not self.constant_memory:
595 string_index = self.str_table._get_shared_string_index(string)
596 else:
597 string_index = string
599 # Write previous row if in in-line string constant_memory mode.
600 if self.constant_memory and row > self.previous_row:
601 self._write_single_row(row)
603 # Store the cell data in the worksheet data table.
604 self.table[row][col] = cell_string_tuple(string_index, cell_format)
606 return str_error
608 @convert_cell_args
609 def write_number(self, row, col, number, cell_format=None):
610 """
611 Write a number to a worksheet cell.
613 Args:
614 row: The cell row (zero indexed).
615 col: The cell column (zero indexed).
616 number: Cell data. Int or float.
617 cell_format: An optional cell Format object.
619 Returns:
620 0: Success.
621 -1: Row or column is out of worksheet bounds.
623 """
624 return self._write_number(row, col, number, cell_format)
626 # Undecorated version of write_number().
627 def _write_number(self, row, col, number, cell_format=None):
628 if isnan(number) or isinf(number):
629 if self.nan_inf_to_errors:
630 if isnan(number):
631 return self._write_formula(row, col, "#NUM!", cell_format, "#NUM!")
632 elif number == math.inf:
633 return self._write_formula(row, col, "1/0", cell_format, "#DIV/0!")
634 elif number == -math.inf:
635 return self._write_formula(row, col, "-1/0", cell_format, "#DIV/0!")
636 else:
637 raise TypeError(
638 "NAN/INF not supported in write_number() "
639 "without 'nan_inf_to_errors' Workbook() option"
640 )
642 # Check that row and col are valid and store max and min values.
643 if self._check_dimensions(row, col):
644 return -1
646 # Write previous row if in in-line string constant_memory mode.
647 if self.constant_memory and row > self.previous_row:
648 self._write_single_row(row)
650 # Store the cell data in the worksheet data table.
651 self.table[row][col] = cell_number_tuple(number, cell_format)
653 return 0
655 @convert_cell_args
656 def write_blank(self, row, col, blank, cell_format=None):
657 """
658 Write a blank cell with formatting to a worksheet cell. The blank
659 token is ignored and the format only is written to the cell.
661 Args:
662 row: The cell row (zero indexed).
663 col: The cell column (zero indexed).
664 blank: Any value. It is ignored.
665 cell_format: An optional cell Format object.
667 Returns:
668 0: Success.
669 -1: Row or column is out of worksheet bounds.
671 """
672 return self._write_blank(row, col, blank, cell_format)
674 # Undecorated version of write_blank().
675 def _write_blank(self, row, col, blank, cell_format=None):
676 # Don't write a blank cell unless it has a format.
677 if cell_format is None:
678 return 0
680 # Check that row and col are valid and store max and min values.
681 if self._check_dimensions(row, col):
682 return -1
684 # Write previous row if in in-line string constant_memory mode.
685 if self.constant_memory and row > self.previous_row:
686 self._write_single_row(row)
688 # Store the cell data in the worksheet data table.
689 self.table[row][col] = cell_blank_tuple(cell_format)
691 return 0
693 @convert_cell_args
694 def write_formula(self, row, col, formula, cell_format=None, value=0):
695 """
696 Write a formula to a worksheet cell.
698 Args:
699 row: The cell row (zero indexed).
700 col: The cell column (zero indexed).
701 formula: Cell formula.
702 cell_format: An optional cell Format object.
703 value: An optional value for the formula. Default is 0.
705 Returns:
706 0: Success.
707 -1: Row or column is out of worksheet bounds.
708 -2: Formula can't be None or empty.
710 """
711 # Check that row and col are valid and store max and min values.
712 return self._write_formula(row, col, formula, cell_format, value)
714 # Undecorated version of write_formula().
715 def _write_formula(self, row, col, formula, cell_format=None, value=0):
716 if self._check_dimensions(row, col):
717 return -1
719 if formula is None or formula == "":
720 warn("Formula can't be None or empty")
721 return -1
723 # Check for dynamic array functions.
724 if re_dynamic_function.search(formula):
725 return self.write_dynamic_array_formula(
726 row, col, row, col, formula, cell_format, value
727 )
729 # Hand off array formulas.
730 if formula.startswith("{") and formula.endswith("}"):
731 return self._write_array_formula(
732 row, col, row, col, formula, cell_format, value
733 )
735 # Modify the formula string, as needed.
736 formula = self._prepare_formula(formula)
738 # Write previous row if in in-line string constant_memory mode.
739 if self.constant_memory and row > self.previous_row:
740 self._write_single_row(row)
742 # Store the cell data in the worksheet data table.
743 self.table[row][col] = cell_formula_tuple(formula, cell_format, value)
745 return 0
747 @convert_range_args
748 def write_array_formula(
749 self,
750 first_row,
751 first_col,
752 last_row,
753 last_col,
754 formula,
755 cell_format=None,
756 value=0,
757 ):
758 """
759 Write a formula to a worksheet cell/range.
761 Args:
762 first_row: The first row of the cell range. (zero indexed).
763 first_col: The first column of the cell range.
764 last_row: The last row of the cell range. (zero indexed).
765 last_col: The last column of the cell range.
766 formula: Cell formula.
767 cell_format: An optional cell Format object.
768 value: An optional value for the formula. Default is 0.
770 Returns:
771 0: Success.
772 -1: Row or column is out of worksheet bounds.
774 """
775 # Check for dynamic array functions.
776 if re_dynamic_function.search(formula):
777 return self.write_dynamic_array_formula(
778 first_row, first_col, last_row, last_col, formula, cell_format, value
779 )
781 return self._write_array_formula(
782 first_row,
783 first_col,
784 last_row,
785 last_col,
786 formula,
787 cell_format,
788 value,
789 "static",
790 )
792 @convert_range_args
793 def write_dynamic_array_formula(
794 self,
795 first_row,
796 first_col,
797 last_row,
798 last_col,
799 formula,
800 cell_format=None,
801 value=0,
802 ):
803 """
804 Write a dynamic array formula to a worksheet cell/range.
806 Args:
807 first_row: The first row of the cell range. (zero indexed).
808 first_col: The first column of the cell range.
809 last_row: The last row of the cell range. (zero indexed).
810 last_col: The last column of the cell range.
811 formula: Cell formula.
812 cell_format: An optional cell Format object.
813 value: An optional value for the formula. Default is 0.
815 Returns:
816 0: Success.
817 -1: Row or column is out of worksheet bounds.
819 """
820 error = self._write_array_formula(
821 first_row,
822 first_col,
823 last_row,
824 last_col,
825 formula,
826 cell_format,
827 value,
828 "dynamic",
829 )
831 if error == 0:
832 self.has_dynamic_arrays = True
834 return error
836 # Utility method to strip equal sign and array braces from a formula and
837 # also expand out future and dynamic array formulas.
838 def _prepare_formula(self, formula, expand_future_functions=False):
839 # Remove array formula braces and the leading =.
840 if formula.startswith("{"):
841 formula = formula[1:]
842 if formula.startswith("="):
843 formula = formula[1:]
844 if formula.endswith("}"):
845 formula = formula[:-1]
847 # Check if formula is already expanded by the user.
848 if "_xlfn." in formula:
849 return formula
851 # Expand dynamic formulas.
852 formula = re.sub(r"\bANCHORARRAY\(", "_xlfn.ANCHORARRAY(", formula)
853 formula = re.sub(r"\bBYCOL\(", "_xlfn.BYCOL(", formula)
854 formula = re.sub(r"\bBYROW\(", "_xlfn.BYROW(", formula)
855 formula = re.sub(r"\bCHOOSECOLS\(", "_xlfn.CHOOSECOLS(", formula)
856 formula = re.sub(r"\bCHOOSEROWS\(", "_xlfn.CHOOSEROWS(", formula)
857 formula = re.sub(r"\bDROP\(", "_xlfn.DROP(", formula)
858 formula = re.sub(r"\bEXPAND\(", "_xlfn.EXPAND(", formula)
859 formula = re.sub(r"\bFILTER\(", "_xlfn._xlws.FILTER(", formula)
860 formula = re.sub(r"\bHSTACK\(", "_xlfn.HSTACK(", formula)
861 formula = re.sub(r"\bLAMBDA\(", "_xlfn.LAMBDA(", formula)
862 formula = re.sub(r"\bMAKEARRAY\(", "_xlfn.MAKEARRAY(", formula)
863 formula = re.sub(r"\bMAP\(", "_xlfn.MAP(", formula)
864 formula = re.sub(r"\bRANDARRAY\(", "_xlfn.RANDARRAY(", formula)
865 formula = re.sub(r"\bREDUCE\(", "_xlfn.REDUCE(", formula)
866 formula = re.sub(r"\bSCAN\(", "_xlfn.SCAN(", formula)
867 formula = re.sub(r"\SINGLE\(", "_xlfn.SINGLE(", formula)
868 formula = re.sub(r"\bSEQUENCE\(", "_xlfn.SEQUENCE(", formula)
869 formula = re.sub(r"\bSORT\(", "_xlfn._xlws.SORT(", formula)
870 formula = re.sub(r"\bSORTBY\(", "_xlfn.SORTBY(", formula)
871 formula = re.sub(r"\bSWITCH\(", "_xlfn.SWITCH(", formula)
872 formula = re.sub(r"\bTAKE\(", "_xlfn.TAKE(", formula)
873 formula = re.sub(r"\bTEXTSPLIT\(", "_xlfn.TEXTSPLIT(", formula)
874 formula = re.sub(r"\bTOCOL\(", "_xlfn.TOCOL(", formula)
875 formula = re.sub(r"\bTOROW\(", "_xlfn.TOROW(", formula)
876 formula = re.sub(r"\bUNIQUE\(", "_xlfn.UNIQUE(", formula)
877 formula = re.sub(r"\bVSTACK\(", "_xlfn.VSTACK(", formula)
878 formula = re.sub(r"\bWRAPCOLS\(", "_xlfn.WRAPCOLS(", formula)
879 formula = re.sub(r"\bWRAPROWS\(", "_xlfn.WRAPROWS(", formula)
880 formula = re.sub(r"\bXLOOKUP\(", "_xlfn.XLOOKUP(", formula)
882 if not self.use_future_functions and not expand_future_functions:
883 return formula
885 formula = re.sub(r"\bACOTH\(", "_xlfn.ACOTH(", formula)
886 formula = re.sub(r"\bACOT\(", "_xlfn.ACOT(", formula)
887 formula = re.sub(r"\bAGGREGATE\(", "_xlfn.AGGREGATE(", formula)
888 formula = re.sub(r"\bARABIC\(", "_xlfn.ARABIC(", formula)
889 formula = re.sub(r"\bARRAYTOTEXT\(", "_xlfn.ARRAYTOTEXT(", formula)
890 formula = re.sub(r"\bBASE\(", "_xlfn.BASE(", formula)
891 formula = re.sub(r"\bBETA.DIST\(", "_xlfn.BETA.DIST(", formula)
892 formula = re.sub(r"\bBETA.INV\(", "_xlfn.BETA.INV(", formula)
893 formula = re.sub(r"\bBINOM.DIST.RANGE\(", "_xlfn.BINOM.DIST.RANGE(", formula)
894 formula = re.sub(r"\bBINOM.DIST\(", "_xlfn.BINOM.DIST(", formula)
895 formula = re.sub(r"\bBINOM.INV\(", "_xlfn.BINOM.INV(", formula)
896 formula = re.sub(r"\bBITAND\(", "_xlfn.BITAND(", formula)
897 formula = re.sub(r"\bBITLSHIFT\(", "_xlfn.BITLSHIFT(", formula)
898 formula = re.sub(r"\bBITOR\(", "_xlfn.BITOR(", formula)
899 formula = re.sub(r"\bBITRSHIFT\(", "_xlfn.BITRSHIFT(", formula)
900 formula = re.sub(r"\bBITXOR\(", "_xlfn.BITXOR(", formula)
901 formula = re.sub(r"\bCEILING.MATH\(", "_xlfn.CEILING.MATH(", formula)
902 formula = re.sub(r"\bCEILING.PRECISE\(", "_xlfn.CEILING.PRECISE(", formula)
903 formula = re.sub(r"\bCHISQ.DIST.RT\(", "_xlfn.CHISQ.DIST.RT(", formula)
904 formula = re.sub(r"\bCHISQ.DIST\(", "_xlfn.CHISQ.DIST(", formula)
905 formula = re.sub(r"\bCHISQ.INV.RT\(", "_xlfn.CHISQ.INV.RT(", formula)
906 formula = re.sub(r"\bCHISQ.INV\(", "_xlfn.CHISQ.INV(", formula)
907 formula = re.sub(r"\bCHISQ.TEST\(", "_xlfn.CHISQ.TEST(", formula)
908 formula = re.sub(r"\bCOMBINA\(", "_xlfn.COMBINA(", formula)
909 formula = re.sub(r"\bCONCAT\(", "_xlfn.CONCAT(", formula)
910 formula = re.sub(r"\bCONFIDENCE.NORM\(", "_xlfn.CONFIDENCE.NORM(", formula)
911 formula = re.sub(r"\bCONFIDENCE.T\(", "_xlfn.CONFIDENCE.T(", formula)
912 formula = re.sub(r"\bCOTH\(", "_xlfn.COTH(", formula)
913 formula = re.sub(r"\bCOT\(", "_xlfn.COT(", formula)
914 formula = re.sub(r"\bCOVARIANCE.P\(", "_xlfn.COVARIANCE.P(", formula)
915 formula = re.sub(r"\bCOVARIANCE.S\(", "_xlfn.COVARIANCE.S(", formula)
916 formula = re.sub(r"\bCSCH\(", "_xlfn.CSCH(", formula)
917 formula = re.sub(r"\bCSC\(", "_xlfn.CSC(", formula)
918 formula = re.sub(r"\bDAYS\(", "_xlfn.DAYS(", formula)
919 formula = re.sub(r"\bDECIMAL\(", "_xlfn.DECIMAL(", formula)
920 formula = re.sub(r"\bERF.PRECISE\(", "_xlfn.ERF.PRECISE(", formula)
921 formula = re.sub(r"\bERFC.PRECISE\(", "_xlfn.ERFC.PRECISE(", formula)
922 formula = re.sub(r"\bEXPON.DIST\(", "_xlfn.EXPON.DIST(", formula)
923 formula = re.sub(r"\bF.DIST.RT\(", "_xlfn.F.DIST.RT(", formula)
924 formula = re.sub(r"\bF.DIST\(", "_xlfn.F.DIST(", formula)
925 formula = re.sub(r"\bF.INV.RT\(", "_xlfn.F.INV.RT(", formula)
926 formula = re.sub(r"\bF.INV\(", "_xlfn.F.INV(", formula)
927 formula = re.sub(r"\bF.TEST\(", "_xlfn.F.TEST(", formula)
928 formula = re.sub(r"\bFILTERXML\(", "_xlfn.FILTERXML(", formula)
929 formula = re.sub(r"\bFLOOR.MATH\(", "_xlfn.FLOOR.MATH(", formula)
930 formula = re.sub(r"\bFLOOR.PRECISE\(", "_xlfn.FLOOR.PRECISE(", formula)
931 formula = re.sub(
932 r"\bFORECAST.ETS.CONFINT\(", "_xlfn.FORECAST.ETS.CONFINT(", formula
933 )
934 formula = re.sub(
935 r"\bFORECAST.ETS.SEASONALITY\(", "_xlfn.FORECAST.ETS.SEASONALITY(", formula
936 )
937 formula = re.sub(r"\bFORECAST.ETS.STAT\(", "_xlfn.FORECAST.ETS.STAT(", formula)
938 formula = re.sub(r"\bFORECAST.ETS\(", "_xlfn.FORECAST.ETS(", formula)
939 formula = re.sub(r"\bFORECAST.LINEAR\(", "_xlfn.FORECAST.LINEAR(", formula)
940 formula = re.sub(r"\bFORMULATEXT\(", "_xlfn.FORMULATEXT(", formula)
941 formula = re.sub(r"\bGAMMA.DIST\(", "_xlfn.GAMMA.DIST(", formula)
942 formula = re.sub(r"\bGAMMA.INV\(", "_xlfn.GAMMA.INV(", formula)
943 formula = re.sub(r"\bGAMMALN.PRECISE\(", "_xlfn.GAMMALN.PRECISE(", formula)
944 formula = re.sub(r"\bGAMMA\(", "_xlfn.GAMMA(", formula)
945 formula = re.sub(r"\bGAUSS\(", "_xlfn.GAUSS(", formula)
946 formula = re.sub(r"\bHYPGEOM.DIST\(", "_xlfn.HYPGEOM.DIST(", formula)
947 formula = re.sub(r"\bIFNA\(", "_xlfn.IFNA(", formula)
948 formula = re.sub(r"\bIFS\(", "_xlfn.IFS(", formula)
949 formula = re.sub(r"\bIMAGE\(", "_xlfn.IMAGE(", formula)
950 formula = re.sub(r"\bIMCOSH\(", "_xlfn.IMCOSH(", formula)
951 formula = re.sub(r"\bIMCOT\(", "_xlfn.IMCOT(", formula)
952 formula = re.sub(r"\bIMCSCH\(", "_xlfn.IMCSCH(", formula)
953 formula = re.sub(r"\bIMCSC\(", "_xlfn.IMCSC(", formula)
954 formula = re.sub(r"\bIMSECH\(", "_xlfn.IMSECH(", formula)
955 formula = re.sub(r"\bIMSEC\(", "_xlfn.IMSEC(", formula)
956 formula = re.sub(r"\bIMSINH\(", "_xlfn.IMSINH(", formula)
957 formula = re.sub(r"\bIMTAN\(", "_xlfn.IMTAN(", formula)
958 formula = re.sub(r"\bISFORMULA\(", "_xlfn.ISFORMULA(", formula)
959 formula = re.sub(r"\bISOMITTED\(", "_xlfn.ISOMITTED(", formula)
960 formula = re.sub(r"\bISOWEEKNUM\(", "_xlfn.ISOWEEKNUM(", formula)
961 formula = re.sub(r"\bLET\(", "_xlfn.LET(", formula)
962 formula = re.sub(r"\bLOGNORM.DIST\(", "_xlfn.LOGNORM.DIST(", formula)
963 formula = re.sub(r"\bLOGNORM.INV\(", "_xlfn.LOGNORM.INV(", formula)
964 formula = re.sub(r"\bMAXIFS\(", "_xlfn.MAXIFS(", formula)
965 formula = re.sub(r"\bMINIFS\(", "_xlfn.MINIFS(", formula)
966 formula = re.sub(r"\bMODE.MULT\(", "_xlfn.MODE.MULT(", formula)
967 formula = re.sub(r"\bMODE.SNGL\(", "_xlfn.MODE.SNGL(", formula)
968 formula = re.sub(r"\bMUNIT\(", "_xlfn.MUNIT(", formula)
969 formula = re.sub(r"\bNEGBINOM.DIST\(", "_xlfn.NEGBINOM.DIST(", formula)
970 formula = re.sub(r"\bNORM.DIST\(", "_xlfn.NORM.DIST(", formula)
971 formula = re.sub(r"\bNORM.INV\(", "_xlfn.NORM.INV(", formula)
972 formula = re.sub(r"\bNORM.S.DIST\(", "_xlfn.NORM.S.DIST(", formula)
973 formula = re.sub(r"\bNORM.S.INV\(", "_xlfn.NORM.S.INV(", formula)
974 formula = re.sub(r"\bNUMBERVALUE\(", "_xlfn.NUMBERVALUE(", formula)
975 formula = re.sub(r"\bPDURATION\(", "_xlfn.PDURATION(", formula)
976 formula = re.sub(r"\bPERCENTILE.EXC\(", "_xlfn.PERCENTILE.EXC(", formula)
977 formula = re.sub(r"\bPERCENTILE.INC\(", "_xlfn.PERCENTILE.INC(", formula)
978 formula = re.sub(r"\bPERCENTRANK.EXC\(", "_xlfn.PERCENTRANK.EXC(", formula)
979 formula = re.sub(r"\bPERCENTRANK.INC\(", "_xlfn.PERCENTRANK.INC(", formula)
980 formula = re.sub(r"\bPERMUTATIONA\(", "_xlfn.PERMUTATIONA(", formula)
981 formula = re.sub(r"\bPHI\(", "_xlfn.PHI(", formula)
982 formula = re.sub(r"\bPOISSON.DIST\(", "_xlfn.POISSON.DIST(", formula)
983 formula = re.sub(r"\bQUARTILE.EXC\(", "_xlfn.QUARTILE.EXC(", formula)
984 formula = re.sub(r"\bQUARTILE.INC\(", "_xlfn.QUARTILE.INC(", formula)
985 formula = re.sub(r"\bQUERYSTRING\(", "_xlfn.QUERYSTRING(", formula)
986 formula = re.sub(r"\bRANK.AVG\(", "_xlfn.RANK.AVG(", formula)
987 formula = re.sub(r"\bRANK.EQ\(", "_xlfn.RANK.EQ(", formula)
988 formula = re.sub(r"\bRRI\(", "_xlfn.RRI(", formula)
989 formula = re.sub(r"\bSECH\(", "_xlfn.SECH(", formula)
990 formula = re.sub(r"\bSEC\(", "_xlfn.SEC(", formula)
991 formula = re.sub(r"\bSHEETS\(", "_xlfn.SHEETS(", formula)
992 formula = re.sub(r"\bSHEET\(", "_xlfn.SHEET(", formula)
993 formula = re.sub(r"\bSKEW.P\(", "_xlfn.SKEW.P(", formula)
994 formula = re.sub(r"\bSTDEV.P\(", "_xlfn.STDEV.P(", formula)
995 formula = re.sub(r"\bSTDEV.S\(", "_xlfn.STDEV.S(", formula)
996 formula = re.sub(r"\bT.DIST.2T\(", "_xlfn.T.DIST.2T(", formula)
997 formula = re.sub(r"\bT.DIST.RT\(", "_xlfn.T.DIST.RT(", formula)
998 formula = re.sub(r"\bT.DIST\(", "_xlfn.T.DIST(", formula)
999 formula = re.sub(r"\bT.INV.2T\(", "_xlfn.T.INV.2T(", formula)
1000 formula = re.sub(r"\bT.INV\(", "_xlfn.T.INV(", formula)
1001 formula = re.sub(r"\bT.TEST\(", "_xlfn.T.TEST(", formula)
1002 formula = re.sub(r"\bTEXTAFTER\(", "_xlfn.TEXTAFTER(", formula)
1003 formula = re.sub(r"\bTEXTBEFORE\(", "_xlfn.TEXTBEFORE(", formula)
1004 formula = re.sub(r"\bTEXTJOIN\(", "_xlfn.TEXTJOIN(", formula)
1005 formula = re.sub(r"\bUNICHAR\(", "_xlfn.UNICHAR(", formula)
1006 formula = re.sub(r"\bUNICODE\(", "_xlfn.UNICODE(", formula)
1007 formula = re.sub(r"\bVALUETOTEXT\(", "_xlfn.VALUETOTEXT(", formula)
1008 formula = re.sub(r"\bVAR.P\(", "_xlfn.VAR.P(", formula)
1009 formula = re.sub(r"\bVAR.S\(", "_xlfn.VAR.S(", formula)
1010 formula = re.sub(r"\bWEBSERVICE\(", "_xlfn.WEBSERVICE(", formula)
1011 formula = re.sub(r"\bWEIBULL.DIST\(", "_xlfn.WEIBULL.DIST(", formula)
1012 formula = re.sub(r"\bXMATCH\(", "_xlfn.XMATCH(", formula)
1013 formula = re.sub(r"\bXOR\(", "_xlfn.XOR(", formula)
1014 formula = re.sub(r"\bZ.TEST\(", "_xlfn.Z.TEST(", formula)
1016 return formula
1018 # Escape/expand table functions. This mainly involves converting Excel 2010
1019 # "@" table ref to 2007 "[#This Row],". We parse the string to avoid
1020 # replacements in string literals within the formula.
1021 @staticmethod
1022 def _prepare_table_formula(formula):
1023 if "@" not in formula:
1024 # No escaping required.
1025 return formula
1027 escaped_formula = []
1028 in_string_literal = False
1030 for char in formula:
1031 # Match the start/end of string literals to avoid escaping
1032 # references in strings.
1033 if char == '"':
1034 in_string_literal = not in_string_literal
1036 # Copy the string literal.
1037 if in_string_literal:
1038 escaped_formula.append(char)
1039 continue
1041 # Replace table reference.
1042 if char == "@":
1043 escaped_formula.append("[#This Row],")
1044 else:
1045 escaped_formula.append(char)
1047 return ("").join(escaped_formula)
1049 # Undecorated version of write_array_formula() and
1050 # write_dynamic_array_formula().
1051 def _write_array_formula(
1052 self,
1053 first_row,
1054 first_col,
1055 last_row,
1056 last_col,
1057 formula,
1058 cell_format=None,
1059 value=0,
1060 atype="static",
1061 ):
1062 # Swap last row/col with first row/col as necessary.
1063 if first_row > last_row:
1064 first_row, last_row = last_row, first_row
1065 if first_col > last_col:
1066 first_col, last_col = last_col, first_col
1068 # Check that row and col are valid and store max and min values.
1069 if self._check_dimensions(first_row, first_col):
1070 return -1
1071 if self._check_dimensions(last_row, last_col):
1072 return -1
1074 # Define array range
1075 if first_row == last_row and first_col == last_col:
1076 cell_range = xl_rowcol_to_cell(first_row, first_col)
1077 else:
1078 cell_range = (
1079 xl_rowcol_to_cell(first_row, first_col)
1080 + ":"
1081 + xl_rowcol_to_cell(last_row, last_col)
1082 )
1084 # Modify the formula string, as needed.
1085 formula = self._prepare_formula(formula)
1087 # Write previous row if in in-line string constant_memory mode.
1088 if self.constant_memory and first_row > self.previous_row:
1089 self._write_single_row(first_row)
1091 # Store the cell data in the worksheet data table.
1092 self.table[first_row][first_col] = cell_arformula_tuple(
1093 formula, cell_format, value, cell_range, atype
1094 )
1096 # Pad out the rest of the area with formatted zeroes.
1097 if not self.constant_memory:
1098 for row in range(first_row, last_row + 1):
1099 for col in range(first_col, last_col + 1):
1100 if row != first_row or col != first_col:
1101 self._write_number(row, col, 0, cell_format)
1103 return 0
1105 @convert_cell_args
1106 def write_datetime(self, row, col, date, cell_format=None):
1107 """
1108 Write a date or time to a worksheet cell.
1110 Args:
1111 row: The cell row (zero indexed).
1112 col: The cell column (zero indexed).
1113 date: Date and/or time as a datetime object.
1114 cell_format: A cell Format object.
1116 Returns:
1117 0: Success.
1118 -1: Row or column is out of worksheet bounds.
1120 """
1121 return self._write_datetime(row, col, date, cell_format)
1123 # Undecorated version of write_datetime().
1124 def _write_datetime(self, row, col, date, cell_format=None):
1125 # Check that row and col are valid and store max and min values.
1126 if self._check_dimensions(row, col):
1127 return -1
1129 # Write previous row if in in-line string constant_memory mode.
1130 if self.constant_memory and row > self.previous_row:
1131 self._write_single_row(row)
1133 # Convert datetime to an Excel date.
1134 number = self._convert_date_time(date)
1136 # Add the default date format.
1137 if cell_format is None:
1138 cell_format = self.default_date_format
1140 # Store the cell data in the worksheet data table.
1141 self.table[row][col] = cell_datetime_tuple(number, cell_format)
1143 return 0
1145 @convert_cell_args
1146 def write_boolean(self, row, col, boolean, cell_format=None):
1147 """
1148 Write a boolean value to a worksheet cell.
1150 Args:
1151 row: The cell row (zero indexed).
1152 col: The cell column (zero indexed).
1153 boolean: Cell data. bool type.
1154 cell_format: An optional cell Format object.
1156 Returns:
1157 0: Success.
1158 -1: Row or column is out of worksheet bounds.
1160 """
1161 return self._write_boolean(row, col, boolean, cell_format)
1163 # Undecorated version of write_boolean().
1164 def _write_boolean(self, row, col, boolean, cell_format=None):
1165 # Check that row and col are valid and store max and min values.
1166 if self._check_dimensions(row, col):
1167 return -1
1169 # Write previous row if in in-line string constant_memory mode.
1170 if self.constant_memory and row > self.previous_row:
1171 self._write_single_row(row)
1173 if boolean:
1174 value = 1
1175 else:
1176 value = 0
1178 # Store the cell data in the worksheet data table.
1179 self.table[row][col] = cell_boolean_tuple(value, cell_format)
1181 return 0
1183 # Write a hyperlink. This is comprised of two elements: the displayed
1184 # string and the non-displayed link. The displayed string is the same as
1185 # the link unless an alternative string is specified. The display string
1186 # is written using the write_string() method. Therefore the max characters
1187 # string limit applies.
1188 #
1189 # The hyperlink can be to a http, ftp, mail, internal sheet, or external
1190 # directory urls.
1191 @convert_cell_args
1192 def write_url(self, row, col, url, cell_format=None, string=None, tip=None):
1193 """
1194 Write a hyperlink to a worksheet cell.
1196 Args:
1197 row: The cell row (zero indexed).
1198 col: The cell column (zero indexed).
1199 url: Hyperlink url.
1200 format: An optional cell Format object.
1201 string: An optional display string for the hyperlink.
1202 tip: An optional tooltip.
1203 Returns:
1204 0: Success.
1205 -1: Row or column is out of worksheet bounds.
1206 -2: String longer than 32767 characters.
1207 -3: URL longer than Excel limit of 255 characters.
1208 -4: Exceeds Excel limit of 65,530 urls per worksheet.
1209 """
1210 return self._write_url(row, col, url, cell_format, string, tip)
1212 # Undecorated version of write_url().
1213 def _write_url(self, row, col, url, cell_format=None, string=None, tip=None):
1214 # Check that row and col are valid and store max and min values
1215 if self._check_dimensions(row, col):
1216 return -1
1218 # Set the displayed string to the URL unless defined by the user.
1219 if string is None:
1220 string = url
1222 # Default to external link type such as 'http://' or 'external:'.
1223 link_type = 1
1225 # Remove the URI scheme from internal links.
1226 if url.startswith("internal:"):
1227 url = url.replace("internal:", "")
1228 string = string.replace("internal:", "")
1229 link_type = 2
1231 # Remove the URI scheme from external links and change the directory
1232 # separator from Unix to Dos.
1233 external = False
1234 if url.startswith("external:"):
1235 url = url.replace("external:", "")
1236 url = url.replace("/", "\\")
1237 string = string.replace("external:", "")
1238 string = string.replace("/", "\\")
1239 external = True
1241 # Strip the mailto header.
1242 string = string.replace("mailto:", "")
1244 # Check that the string is < 32767 chars
1245 str_error = 0
1246 if len(string) > self.xls_strmax:
1247 warn(
1248 "Ignoring URL since it exceeds Excel's string limit of "
1249 "32767 characters"
1250 )
1251 return -2
1253 # Copy string for use in hyperlink elements.
1254 url_str = string
1256 # External links to URLs and to other Excel workbooks have slightly
1257 # different characteristics that we have to account for.
1258 if link_type == 1:
1259 # Split url into the link and optional anchor/location.
1260 if "#" in url:
1261 url, url_str = url.split("#", 1)
1262 else:
1263 url_str = None
1265 url = self._escape_url(url)
1267 if url_str is not None and not external:
1268 url_str = self._escape_url(url_str)
1270 # Add the file:/// URI to the url for Windows style "C:/" link and
1271 # Network shares.
1272 if re.match(r"\w:", url) or re.match(r"\\", url):
1273 url = "file:///" + url
1275 # Convert a .\dir\file.xlsx link to dir\file.xlsx.
1276 url = re.sub(r"^\.\\", "", url)
1278 # Excel limits the escaped URL and location/anchor to 255 characters.
1279 tmp_url_str = url_str or ""
1280 max_url = self.max_url_length
1281 if len(url) > max_url or len(tmp_url_str) > max_url:
1282 warn(
1283 "Ignoring URL '%s' with link or location/anchor > %d "
1284 "characters since it exceeds Excel's limit for URLS" % (url, max_url)
1285 )
1286 return -3
1288 # Check the limit of URLS per worksheet.
1289 self.hlink_count += 1
1291 if self.hlink_count > 65530:
1292 warn(
1293 "Ignoring URL '%s' since it exceeds Excel's limit of "
1294 "65,530 URLS per worksheet." % url
1295 )
1296 return -4
1298 # Add the default URL format.
1299 if cell_format is None:
1300 cell_format = self.default_url_format
1302 if not self.ignore_write_string:
1303 # Write previous row if in in-line string constant_memory mode.
1304 if self.constant_memory and row > self.previous_row:
1305 self._write_single_row(row)
1307 # Write the hyperlink string.
1308 self._write_string(row, col, string, cell_format)
1310 # Store the hyperlink data in a separate structure.
1311 self.hyperlinks[row][col] = {
1312 "link_type": link_type,
1313 "url": url,
1314 "str": url_str,
1315 "tip": tip,
1316 }
1318 return str_error
1320 @convert_cell_args
1321 def write_rich_string(self, row, col, *args):
1322 """
1323 Write a "rich" string with multiple formats to a worksheet cell.
1325 Args:
1326 row: The cell row (zero indexed).
1327 col: The cell column (zero indexed).
1328 string_parts: String and format pairs.
1329 cell_format: Optional Format object.
1331 Returns:
1332 0: Success.
1333 -1: Row or column is out of worksheet bounds.
1334 -2: String truncated to 32k characters.
1335 -3: 2 consecutive formats used.
1336 -4: Empty string used.
1337 -5: Insufficient parameters.
1339 """
1341 return self._write_rich_string(row, col, *args)
1343 # Undecorated version of write_rich_string().
1344 def _write_rich_string(self, row, col, *args):
1345 tokens = list(args)
1346 cell_format = None
1347 string_index = 0
1348 raw_string = ""
1350 # Check that row and col are valid and store max and min values
1351 if self._check_dimensions(row, col):
1352 return -1
1354 # If the last arg is a format we use it as the cell format.
1355 if isinstance(tokens[-1], Format):
1356 cell_format = tokens.pop()
1358 # Create a temp XMLWriter object and use it to write the rich string
1359 # XML to a string.
1360 fh = StringIO()
1361 self.rstring = XMLwriter()
1362 self.rstring._set_filehandle(fh)
1364 # Create a temp format with the default font for unformatted fragments.
1365 default = Format()
1367 # Convert list of format, string tokens to pairs of (format, string)
1368 # except for the first string fragment which doesn't require a default
1369 # formatting run. Use the default for strings without a leading format.
1370 fragments = []
1371 previous = "format"
1372 pos = 0
1374 if len(tokens) <= 2:
1375 warn(
1376 "You must specify more than 2 format/fragments for rich "
1377 "strings. Ignoring input in write_rich_string()."
1378 )
1379 return -5
1381 for token in tokens:
1382 if not isinstance(token, Format):
1383 # Token is a string.
1384 if previous != "format":
1385 # If previous token wasn't a format add one before string.
1386 fragments.append(default)
1387 fragments.append(token)
1388 else:
1389 # If previous token was a format just add the string.
1390 fragments.append(token)
1392 if token == "":
1393 warn(
1394 "Excel doesn't allow empty strings in rich strings. "
1395 "Ignoring input in write_rich_string()."
1396 )
1397 return -4
1399 # Keep track of unformatted string.
1400 raw_string += token
1401 previous = "string"
1402 else:
1403 # Can't allow 2 formats in a row.
1404 if previous == "format" and pos > 0:
1405 warn(
1406 "Excel doesn't allow 2 consecutive formats in rich "
1407 "strings. Ignoring input in write_rich_string()."
1408 )
1409 return -3
1411 # Token is a format object. Add it to the fragment list.
1412 fragments.append(token)
1413 previous = "format"
1415 pos += 1
1417 # If the first token is a string start the <r> element.
1418 if not isinstance(fragments[0], Format):
1419 self.rstring._xml_start_tag("r")
1421 # Write the XML elements for the $format $string fragments.
1422 for token in fragments:
1423 if isinstance(token, Format):
1424 # Write the font run.
1425 self.rstring._xml_start_tag("r")
1426 self._write_font(token)
1427 else:
1428 # Write the string fragment part, with whitespace handling.
1429 attributes = []
1431 if preserve_whitespace(token):
1432 attributes.append(("xml:space", "preserve"))
1434 self.rstring._xml_data_element("t", token, attributes)
1435 self.rstring._xml_end_tag("r")
1437 # Read the in-memory string.
1438 string = self.rstring.fh.getvalue()
1440 # Check that the string is < 32767 chars.
1441 if len(raw_string) > self.xls_strmax:
1442 warn(
1443 "String length must be less than or equal to Excel's limit "
1444 "of 32,767 characters in write_rich_string()."
1445 )
1446 return -2
1448 # Write a shared string or an in-line string in constant_memory mode.
1449 if not self.constant_memory:
1450 string_index = self.str_table._get_shared_string_index(string)
1451 else:
1452 string_index = string
1454 # Write previous row if in in-line string constant_memory mode.
1455 if self.constant_memory and row > self.previous_row:
1456 self._write_single_row(row)
1458 # Store the cell data in the worksheet data table.
1459 self.table[row][col] = cell_rich_string_tuple(
1460 string_index, cell_format, raw_string
1461 )
1463 return 0
1465 def add_write_handler(self, user_type, user_function):
1466 """
1467 Add a callback function to the write() method to handle user defined
1468 types.
1470 Args:
1471 user_type: The user type() to match on.
1472 user_function: The user defined function to write the type data.
1473 Returns:
1474 Nothing.
1476 """
1478 self.write_handlers[user_type] = user_function
1480 @convert_cell_args
1481 def write_row(self, row, col, data, cell_format=None):
1482 """
1483 Write a row of data starting from (row, col).
1485 Args:
1486 row: The cell row (zero indexed).
1487 col: The cell column (zero indexed).
1488 data: A list of tokens to be written with write().
1489 format: An optional cell Format object.
1490 Returns:
1491 0: Success.
1492 other: Return value of write() method.
1494 """
1495 for token in data:
1496 error = self._write(row, col, token, cell_format)
1497 if error:
1498 return error
1499 col += 1
1501 return 0
1503 @convert_cell_args
1504 def write_column(self, row, col, data, cell_format=None):
1505 """
1506 Write a column of data starting from (row, col).
1508 Args:
1509 row: The cell row (zero indexed).
1510 col: The cell column (zero indexed).
1511 data: A list of tokens to be written with write().
1512 format: An optional cell Format object.
1513 Returns:
1514 0: Success.
1515 other: Return value of write() method.
1517 """
1518 for token in data:
1519 error = self._write(row, col, token, cell_format)
1520 if error:
1521 return error
1522 row += 1
1524 return 0
1526 @convert_cell_args
1527 def insert_image(self, row, col, filename, options=None):
1528 """
1529 Insert an image with its top-left corner in a worksheet cell.
1531 Args:
1532 row: The cell row (zero indexed).
1533 col: The cell column (zero indexed).
1534 filename: Path and filename for in supported formats.
1535 options: Position, scale, url and data stream of the image.
1537 Returns:
1538 0: Success.
1539 -1: Row or column is out of worksheet bounds.
1541 """
1542 # Check insert (row, col) without storing.
1543 if self._check_dimensions(row, col, True, True):
1544 warn("Cannot insert image at (%d, %d)." % (row, col))
1545 return -1
1547 if options is None:
1548 options = {}
1550 x_offset = options.get("x_offset", 0)
1551 y_offset = options.get("y_offset", 0)
1552 x_scale = options.get("x_scale", 1)
1553 y_scale = options.get("y_scale", 1)
1554 url = options.get("url", None)
1555 tip = options.get("tip", None)
1556 anchor = options.get("object_position", 2)
1557 image_data = options.get("image_data", None)
1558 description = options.get("description", None)
1559 decorative = options.get("decorative", False)
1561 # For backward compatibility with older parameter name.
1562 anchor = options.get("positioning", anchor)
1564 if not image_data and not os.path.exists(filename):
1565 warn("Image file '%s' not found." % filename)
1566 return -1
1568 self.images.append(
1569 [
1570 row,
1571 col,
1572 filename,
1573 x_offset,
1574 y_offset,
1575 x_scale,
1576 y_scale,
1577 url,
1578 tip,
1579 anchor,
1580 image_data,
1581 description,
1582 decorative,
1583 ]
1584 )
1585 return 0
1587 @convert_cell_args
1588 def embed_image(self, row, col, filename, options=None):
1589 """
1590 Embed an image in a worksheet cell.
1592 Args:
1593 row: The cell row (zero indexed).
1594 col: The cell column (zero indexed).
1595 filename: Path and filename for in supported formats.
1596 options: Url and data stream of the image.
1598 Returns:
1599 0: Success.
1600 -1: Row or column is out of worksheet bounds.
1602 """
1603 # Check insert (row, col) without storing.
1604 if self._check_dimensions(row, col):
1605 warn("Cannot embed image at (%d, %d)." % (row, col))
1606 return -1
1608 if options is None:
1609 options = {}
1611 url = options.get("url", None)
1612 tip = options.get("tip", None)
1613 cell_format = options.get("cell_format", None)
1614 image_data = options.get("image_data", None)
1615 description = options.get("description", None)
1616 decorative = options.get("decorative", False)
1618 if not image_data and not os.path.exists(filename):
1619 warn("Image file '%s' not found." % filename)
1620 return -1
1622 if url:
1623 if cell_format is None:
1624 cell_format = self.default_url_format
1626 self.ignore_write_string = True
1627 self.write_url(row, col, url, cell_format, None, tip)
1628 self.ignore_write_string = False
1630 # Get the image properties, for the type and checksum.
1631 (
1632 image_type,
1633 _,
1634 _,
1635 _,
1636 _,
1637 _,
1638 digest,
1639 ) = get_image_properties(filename, image_data)
1641 image = [filename, image_type, image_data, description, decorative]
1642 image_index = self.embedded_images.get_image_index(image, digest)
1644 # Store the cell error and image index in the worksheet data table.
1645 self.table[row][col] = cell_error_tuple("#VALUE!", cell_format, image_index)
1647 return 0
1649 @convert_cell_args
1650 def insert_textbox(self, row, col, text, options=None):
1651 """
1652 Insert an textbox with its top-left corner in a worksheet cell.
1654 Args:
1655 row: The cell row (zero indexed).
1656 col: The cell column (zero indexed).
1657 text: The text for the textbox.
1658 options: Textbox options.
1660 Returns:
1661 0: Success.
1662 -1: Row or column is out of worksheet bounds.
1664 """
1665 # Check insert (row, col) without storing.
1666 if self._check_dimensions(row, col, True, True):
1667 warn("Cannot insert textbox at (%d, %d)." % (row, col))
1668 return -1
1670 if text is None:
1671 text = ""
1673 if options is None:
1674 options = {}
1676 x_offset = options.get("x_offset", 0)
1677 y_offset = options.get("y_offset", 0)
1678 x_scale = options.get("x_scale", 1)
1679 y_scale = options.get("y_scale", 1)
1680 anchor = options.get("object_position", 1)
1681 description = options.get("description", None)
1682 decorative = options.get("decorative", False)
1684 self.shapes.append(
1685 [
1686 row,
1687 col,
1688 x_offset,
1689 y_offset,
1690 x_scale,
1691 y_scale,
1692 text,
1693 anchor,
1694 options,
1695 description,
1696 decorative,
1697 ]
1698 )
1699 return 0
1701 @convert_cell_args
1702 def insert_chart(self, row, col, chart, options=None):
1703 """
1704 Insert an chart with its top-left corner in a worksheet cell.
1706 Args:
1707 row: The cell row (zero indexed).
1708 col: The cell column (zero indexed).
1709 chart: Chart object.
1710 options: Position and scale of the chart.
1712 Returns:
1713 0: Success.
1714 -1: Row or column is out of worksheet bounds.
1716 """
1717 # Check insert (row, col) without storing.
1718 if self._check_dimensions(row, col, True, True):
1719 warn("Cannot insert chart at (%d, %d)." % (row, col))
1720 return -1
1722 if options is None:
1723 options = {}
1725 # Ensure a chart isn't inserted more than once.
1726 if chart.already_inserted or chart.combined and chart.combined.already_inserted:
1727 warn("Chart cannot be inserted in a worksheet more than once.")
1728 return
1729 else:
1730 chart.already_inserted = True
1732 if chart.combined:
1733 chart.combined.already_inserted = True
1735 x_offset = options.get("x_offset", 0)
1736 y_offset = options.get("y_offset", 0)
1737 x_scale = options.get("x_scale", 1)
1738 y_scale = options.get("y_scale", 1)
1739 anchor = options.get("object_position", 1)
1740 description = options.get("description", None)
1741 decorative = options.get("decorative", False)
1743 # Allow Chart to override the scale and offset.
1744 if chart.x_scale != 1:
1745 x_scale = chart.x_scale
1747 if chart.y_scale != 1:
1748 y_scale = chart.y_scale
1750 if chart.x_offset:
1751 x_offset = chart.x_offset
1753 if chart.y_offset:
1754 y_offset = chart.y_offset
1756 self.charts.append(
1757 [
1758 row,
1759 col,
1760 chart,
1761 x_offset,
1762 y_offset,
1763 x_scale,
1764 y_scale,
1765 anchor,
1766 description,
1767 decorative,
1768 ]
1769 )
1770 return 0
1772 @convert_cell_args
1773 def write_comment(self, row, col, comment, options=None):
1774 """
1775 Write a comment to a worksheet cell.
1777 Args:
1778 row: The cell row (zero indexed).
1779 col: The cell column (zero indexed).
1780 comment: Cell comment. Str.
1781 options: Comment formatting options.
1783 Returns:
1784 0: Success.
1785 -1: Row or column is out of worksheet bounds.
1786 -2: String longer than 32k characters.
1788 """
1789 if options is None:
1790 options = {}
1792 # Check that row and col are valid and store max and min values
1793 if self._check_dimensions(row, col):
1794 return -1
1796 # Check that the comment string is < 32767 chars.
1797 if len(comment) > self.xls_strmax:
1798 return -2
1800 self.has_vml = 1
1801 self.has_comments = 1
1803 # Store the options of the cell comment, to process on file close.
1804 self.comments[row][col] = [row, col, comment, options]
1806 return 0
1808 def show_comments(self):
1809 """
1810 Make any comments in the worksheet visible.
1812 Args:
1813 None.
1815 Returns:
1816 Nothing.
1818 """
1819 self.comments_visible = 1
1821 def set_background(self, filename, is_byte_stream=False):
1822 """
1823 Set a background image for a worksheet.
1825 Args:
1826 filename: Path and filename for in supported formats.
1827 is_byte_stream: File is a stream of bytes.
1828 Returns:
1829 Nothing.
1831 """
1833 if not is_byte_stream and not os.path.exists(filename):
1834 warn("Image file '%s' not found." % filename)
1835 return -1
1837 self.background_bytes = is_byte_stream
1838 self.background_image = filename
1840 def set_comments_author(self, author):
1841 """
1842 Set the default author of the cell comments.
1844 Args:
1845 author: Comment author name. String.
1847 Returns:
1848 Nothing.
1850 """
1851 self.comments_author = author
1853 def get_name(self):
1854 """
1855 Retrieve the worksheet name.
1857 Args:
1858 None.
1860 Returns:
1861 Nothing.
1863 """
1864 # There is no set_name() method. Name must be set in add_worksheet().
1865 return self.name
1867 def activate(self):
1868 """
1869 Set this worksheet as the active worksheet, i.e. the worksheet that is
1870 displayed when the workbook is opened. Also set it as selected.
1872 Note: An active worksheet cannot be hidden.
1874 Args:
1875 None.
1877 Returns:
1878 Nothing.
1880 """
1881 self.hidden = 0
1882 self.selected = 1
1883 self.worksheet_meta.activesheet = self.index
1885 def select(self):
1886 """
1887 Set current worksheet as a selected worksheet, i.e. the worksheet
1888 has its tab highlighted.
1890 Note: A selected worksheet cannot be hidden.
1892 Args:
1893 None.
1895 Returns:
1896 Nothing.
1898 """
1899 self.selected = 1
1900 self.hidden = 0
1902 def hide(self):
1903 """
1904 Hide the current worksheet.
1906 Args:
1907 None.
1909 Returns:
1910 Nothing.
1912 """
1913 self.hidden = 1
1915 # A hidden worksheet shouldn't be active or selected.
1916 self.selected = 0
1918 def very_hidden(self):
1919 """
1920 Hide the current worksheet. This can only be unhidden by VBA.
1922 Args:
1923 None.
1925 Returns:
1926 Nothing.
1928 """
1929 self.hidden = 2
1931 # A hidden worksheet shouldn't be active or selected.
1932 self.selected = 0
1934 def set_first_sheet(self):
1935 """
1936 Set current worksheet as the first visible sheet. This is necessary
1937 when there are a large number of worksheets and the activated
1938 worksheet is not visible on the screen.
1940 Note: A selected worksheet cannot be hidden.
1942 Args:
1943 None.
1945 Returns:
1946 Nothing.
1948 """
1949 self.hidden = 0 # Active worksheet can't be hidden.
1950 self.worksheet_meta.firstsheet = self.index
1952 @convert_column_args
1953 def set_column(
1954 self, first_col, last_col, width=None, cell_format=None, options=None
1955 ):
1956 """
1957 Set the width, and other properties of a single column or a
1958 range of columns.
1960 Args:
1961 first_col: First column (zero-indexed).
1962 last_col: Last column (zero-indexed). Can be same as first_col.
1963 width: Column width. (optional).
1964 cell_format: Column cell_format. (optional).
1965 options: Dict of options such as hidden and level.
1967 Returns:
1968 0: Success.
1969 -1: Column number is out of worksheet bounds.
1971 """
1972 if options is None:
1973 options = {}
1975 # Ensure 2nd col is larger than first.
1976 if first_col > last_col:
1977 (first_col, last_col) = (last_col, first_col)
1979 # Don't modify the row dimensions when checking the columns.
1980 ignore_row = True
1982 # Set optional column values.
1983 hidden = options.get("hidden", False)
1984 collapsed = options.get("collapsed", False)
1985 level = options.get("level", 0)
1987 # Store the column dimension only in some conditions.
1988 if cell_format or (width and hidden):
1989 ignore_col = False
1990 else:
1991 ignore_col = True
1993 # Check that each column is valid and store the max and min values.
1994 if self._check_dimensions(0, last_col, ignore_row, ignore_col):
1995 return -1
1996 if self._check_dimensions(0, first_col, ignore_row, ignore_col):
1997 return -1
1999 # Set the limits for the outline levels (0 <= x <= 7).
2000 if level < 0:
2001 level = 0
2002 if level > 7:
2003 level = 7
2005 if level > self.outline_col_level:
2006 self.outline_col_level = level
2008 # Store the column data.
2009 for col in range(first_col, last_col + 1):
2010 self.col_info[col] = [width, cell_format, hidden, level, collapsed, False]
2012 # Store the column change to allow optimizations.
2013 self.col_size_changed = True
2015 return 0
2017 @convert_column_args
2018 def set_column_pixels(
2019 self, first_col, last_col, width=None, cell_format=None, options=None
2020 ):
2021 """
2022 Set the width, and other properties of a single column or a
2023 range of columns, where column width is in pixels.
2025 Args:
2026 first_col: First column (zero-indexed).
2027 last_col: Last column (zero-indexed). Can be same as first_col.
2028 width: Column width in pixels. (optional).
2029 cell_format: Column cell_format. (optional).
2030 options: Dict of options such as hidden and level.
2032 Returns:
2033 0: Success.
2034 -1: Column number is out of worksheet bounds.
2036 """
2037 if width is not None:
2038 width = self._pixels_to_width(width)
2040 return self.set_column(first_col, last_col, width, cell_format, options)
2042 def autofit(self):
2043 """
2044 Simulate autofit based on the data, and datatypes in each column.
2046 Args:
2047 None.
2049 Returns:
2050 Nothing.
2052 """
2053 if self.constant_memory:
2054 warn("Autofit is not supported in constant_memory mode.")
2055 return
2057 # No data written to the target sheet; nothing to autofit
2058 if self.dim_rowmax is None:
2059 return
2061 # Store the max pixel width for each column.
2062 col_width_max = {}
2064 # Create a reverse lookup for the share strings table so we can convert
2065 # the string id back to the original string.
2066 strings = sorted(
2067 self.str_table.string_table, key=self.str_table.string_table.__getitem__
2068 )
2070 for row_num in range(self.dim_rowmin, self.dim_rowmax + 1):
2071 if not self.table.get(row_num):
2072 continue
2074 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
2075 if col_num in self.table[row_num]:
2076 cell = self.table[row_num][col_num]
2077 cell_type = cell.__class__.__name__
2078 length = 0
2080 if cell_type in ("String", "RichString"):
2081 # Handle strings and rich strings.
2082 #
2083 # For standard shared strings we do a reverse lookup
2084 # from the shared string id to the actual string. For
2085 # rich strings we use the unformatted string. We also
2086 # split multi-line strings and handle each part
2087 # separately.
2088 if cell_type == "String":
2089 string_id = cell.string
2090 string = strings[string_id]
2091 else:
2092 string = cell.raw_string
2094 if "\n" not in string:
2095 # Single line string.
2096 length = xl_pixel_width(string)
2097 else:
2098 # Handle multi-line strings.
2099 for string in string.split("\n"):
2100 seg_length = xl_pixel_width(string)
2101 if seg_length > length:
2102 length = seg_length
2104 elif cell_type == "Number":
2105 # Handle numbers.
2106 #
2107 # We use a workaround/optimization for numbers since
2108 # digits all have a pixel width of 7. This gives a
2109 # slightly greater width for the decimal place and
2110 # minus sign but only by a few pixels and
2111 # over-estimation is okay.
2112 length = 7 * len(str(cell.number))
2114 elif cell_type == "Datetime":
2115 # Handle dates.
2116 #
2117 # The following uses the default width for mm/dd/yyyy
2118 # dates. It isn't feasible to parse the number format
2119 # to get the actual string width for all format types.
2120 length = self.default_date_pixels
2122 elif cell_type == "Boolean":
2123 # Handle boolean values.
2124 #
2125 # Use the Excel standard widths for TRUE and FALSE.
2126 if cell.boolean:
2127 length = 31
2128 else:
2129 length = 36
2131 elif cell_type == "Formula" or cell_type == "ArrayFormula":
2132 # Handle formulas.
2133 #
2134 # We only try to autofit a formula if it has a
2135 # non-zero value.
2136 if isinstance(cell.value, (float, int)):
2137 if cell.value > 0:
2138 length = 7 * len(str(cell.value))
2140 elif isinstance(cell.value, str):
2141 length = xl_pixel_width(cell.value)
2143 elif isinstance(cell.value, bool):
2144 if cell.value:
2145 length = 31
2146 else:
2147 length = 36
2149 # If the cell is in an autofilter header we add an
2150 # additional 16 pixels for the dropdown arrow.
2151 if self.filter_cells.get((row_num, col_num)) and length > 0:
2152 length += 16
2154 # Add the string length to the lookup table.
2155 width_max = col_width_max.get(col_num, 0)
2156 if length > width_max:
2157 col_width_max[col_num] = length
2159 # Apply the width to the column.
2160 for col_num, pixel_width in col_width_max.items():
2161 # Convert the string pixel width to a character width using an
2162 # additional padding of 7 pixels, like Excel.
2163 width = self._pixels_to_width(pixel_width + 7)
2165 # The max column character width in Excel is 255.
2166 if width > 255.0:
2167 width = 255.0
2169 # Add the width to an existing col info structure or add a new one.
2170 if self.col_info.get(col_num):
2171 # We only update the width for an existing column if it is
2172 # greater than the user defined value. This allows the user
2173 # to pre-load a minimum col width.
2174 col_info = self.col_info.get(col_num)
2175 user_width = col_info[0]
2176 hidden = col_info[5]
2177 if user_width is not None and not hidden:
2178 # Col info is user defined.
2179 if width > user_width:
2180 self.col_info[col_num][0] = width
2181 self.col_info[col_num][5] = True
2182 else:
2183 self.col_info[col_num][0] = width
2184 self.col_info[col_num][5] = True
2185 else:
2186 self.col_info[col_num] = [width, None, False, 0, False, True]
2188 def set_row(self, row, height=None, cell_format=None, options=None):
2189 """
2190 Set the width, and other properties of a row.
2192 Args:
2193 row: Row number (zero-indexed).
2194 height: Row height. (optional).
2195 cell_format: Row cell_format. (optional).
2196 options: Dict of options such as hidden, level and collapsed.
2198 Returns:
2199 0: Success.
2200 -1: Row number is out of worksheet bounds.
2202 """
2203 if options is None:
2204 options = {}
2206 # Use minimum col in _check_dimensions().
2207 if self.dim_colmin is not None:
2208 min_col = self.dim_colmin
2209 else:
2210 min_col = 0
2212 # Check that row is valid.
2213 if self._check_dimensions(row, min_col):
2214 return -1
2216 if height is None:
2217 height = self.default_row_height
2219 # Set optional row values.
2220 hidden = options.get("hidden", False)
2221 collapsed = options.get("collapsed", False)
2222 level = options.get("level", 0)
2224 # If the height is 0 the row is hidden and the height is the default.
2225 if height == 0:
2226 hidden = 1
2227 height = self.default_row_height
2229 # Set the limits for the outline levels (0 <= x <= 7).
2230 if level < 0:
2231 level = 0
2232 if level > 7:
2233 level = 7
2235 if level > self.outline_row_level:
2236 self.outline_row_level = level
2238 # Store the row properties.
2239 self.set_rows[row] = [height, cell_format, hidden, level, collapsed]
2241 # Store the row change to allow optimizations.
2242 self.row_size_changed = True
2244 # Store the row sizes for use when calculating image vertices.
2245 self.row_sizes[row] = [height, hidden]
2247 return 0
2249 def set_row_pixels(self, row, height=None, cell_format=None, options=None):
2250 """
2251 Set the width (in pixels), and other properties of a row.
2253 Args:
2254 row: Row number (zero-indexed).
2255 height: Row height in pixels. (optional).
2256 cell_format: Row cell_format. (optional).
2257 options: Dict of options such as hidden, level and collapsed.
2259 Returns:
2260 0: Success.
2261 -1: Row number is out of worksheet bounds.
2263 """
2264 if height is not None:
2265 height = self._pixels_to_height(height)
2267 return self.set_row(row, height, cell_format, options)
2269 def set_default_row(self, height=None, hide_unused_rows=False):
2270 """
2271 Set the default row properties.
2273 Args:
2274 height: Default height. Optional, defaults to 15.
2275 hide_unused_rows: Hide unused rows. Optional, defaults to False.
2277 Returns:
2278 Nothing.
2280 """
2281 if height is None:
2282 height = self.default_row_height
2284 if height != self.original_row_height:
2285 # Store the row change to allow optimizations.
2286 self.row_size_changed = True
2287 self.default_row_height = height
2289 if hide_unused_rows:
2290 self.default_row_zeroed = 1
2292 @convert_range_args
2293 def merge_range(
2294 self, first_row, first_col, last_row, last_col, data, cell_format=None
2295 ):
2296 """
2297 Merge a range of cells.
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.
2304 data: Cell data.
2305 cell_format: Cell Format object.
2307 Returns:
2308 0: Success.
2309 -1: Row or column is out of worksheet bounds.
2310 other: Return value of write().
2312 """
2313 # Merge a range of cells. The first cell should contain the data and
2314 # the others should be blank. All cells should have the same format.
2316 # Excel doesn't allow a single cell to be merged
2317 if first_row == last_row and first_col == last_col:
2318 warn("Can't merge single cell")
2319 return
2321 # Swap last row/col with first row/col as necessary
2322 if first_row > last_row:
2323 (first_row, last_row) = (last_row, first_row)
2324 if first_col > last_col:
2325 (first_col, last_col) = (last_col, first_col)
2327 # Check that row and col are valid and store max and min values.
2328 if self._check_dimensions(first_row, first_col):
2329 return -1
2330 if self._check_dimensions(last_row, last_col):
2331 return -1
2333 # Check if the merge range overlaps a previous merged or table range.
2334 # This is a critical file corruption error in Excel.
2335 cell_range = xl_range(first_row, first_col, last_row, last_col)
2336 for row in range(first_row, last_row + 1):
2337 for col in range(first_col, last_col + 1):
2338 if self.merged_cells.get((row, col)):
2339 previous_range = self.merged_cells.get((row, col))
2340 raise OverlappingRange(
2341 "Merge range '%s' overlaps previous merge range '%s'."
2342 % (cell_range, previous_range)
2343 )
2344 elif self.table_cells.get((row, col)):
2345 previous_range = self.table_cells.get((row, col))
2346 raise OverlappingRange(
2347 "Merge range '%s' overlaps previous table range '%s'."
2348 % (cell_range, previous_range)
2349 )
2350 else:
2351 self.merged_cells[(row, col)] = cell_range
2353 # Store the merge range.
2354 self.merge.append([first_row, first_col, last_row, last_col])
2356 # Write the first cell
2357 self._write(first_row, first_col, data, cell_format)
2359 # Pad out the rest of the area with formatted blank cells.
2360 for row in range(first_row, last_row + 1):
2361 for col in range(first_col, last_col + 1):
2362 if row == first_row and col == first_col:
2363 continue
2364 self._write_blank(row, col, "", cell_format)
2366 return 0
2368 @convert_range_args
2369 def autofilter(self, first_row, first_col, last_row, last_col):
2370 """
2371 Set the autofilter area in the worksheet.
2373 Args:
2374 first_row: The first row of the cell range. (zero indexed).
2375 first_col: The first column of the cell range.
2376 last_row: The last row of the cell range. (zero indexed).
2377 last_col: The last column of the cell range.
2379 Returns:
2380 Nothing.
2382 """
2383 # Reverse max and min values if necessary.
2384 if last_row < first_row:
2385 (first_row, last_row) = (last_row, first_row)
2386 if last_col < first_col:
2387 (first_col, last_col) = (last_col, first_col)
2389 # Build up the print area range "Sheet1!$A$1:$C$13".
2390 area = self._convert_name_area(first_row, first_col, last_row, last_col)
2391 ref = xl_range(first_row, first_col, last_row, last_col)
2393 self.autofilter_area = area
2394 self.autofilter_ref = ref
2395 self.filter_range = [first_col, last_col]
2397 # Store the filter cell positions for use in the autofit calculation.
2398 for col in range(first_col, last_col + 1):
2399 self.filter_cells[(first_row, col)] = True
2401 def filter_column(self, col, criteria):
2402 """
2403 Set the column filter criteria.
2405 Args:
2406 col: Filter column (zero-indexed).
2407 criteria: Filter criteria.
2409 Returns:
2410 Nothing.
2412 """
2413 if not self.autofilter_area:
2414 warn("Must call autofilter() before filter_column()")
2415 return
2417 # Check for a column reference in A1 notation and substitute.
2418 try:
2419 int(col)
2420 except ValueError:
2421 # Convert col ref to a cell ref and then to a col number.
2422 col_letter = col
2423 (_, col) = xl_cell_to_rowcol(col + "1")
2425 if col >= self.xls_colmax:
2426 warn("Invalid column '%s'" % col_letter)
2427 return
2429 (col_first, col_last) = self.filter_range
2431 # Reject column if it is outside filter range.
2432 if col < col_first or col > col_last:
2433 warn(
2434 "Column '%d' outside autofilter() column range (%d, %d)"
2435 % (col, col_first, col_last)
2436 )
2437 return
2439 tokens = self._extract_filter_tokens(criteria)
2441 if len(tokens) not in (3, 7):
2442 warn("Incorrect number of tokens in criteria '%s'" % criteria)
2444 tokens = self._parse_filter_expression(criteria, tokens)
2446 # Excel handles single or double custom filters as default filters.
2447 # We need to check for them and handle them accordingly.
2448 if len(tokens) == 2 and tokens[0] == 2:
2449 # Single equality.
2450 self.filter_column_list(col, [tokens[1]])
2451 elif len(tokens) == 5 and tokens[0] == 2 and tokens[2] == 1 and tokens[3] == 2:
2452 # Double equality with "or" operator.
2453 self.filter_column_list(col, [tokens[1], tokens[4]])
2454 else:
2455 # Non default custom filter.
2456 self.filter_cols[col] = tokens
2457 self.filter_type[col] = 0
2459 self.filter_on = 1
2461 def filter_column_list(self, col, filters):
2462 """
2463 Set the column filter criteria in Excel 2007 list style.
2465 Args:
2466 col: Filter column (zero-indexed).
2467 filters: List of filter criteria to match.
2469 Returns:
2470 Nothing.
2472 """
2473 if not self.autofilter_area:
2474 warn("Must call autofilter() before filter_column()")
2475 return
2477 # Check for a column reference in A1 notation and substitute.
2478 try:
2479 int(col)
2480 except ValueError:
2481 # Convert col ref to a cell ref and then to a col number.
2482 col_letter = col
2483 (_, col) = xl_cell_to_rowcol(col + "1")
2485 if col >= self.xls_colmax:
2486 warn("Invalid column '%s'" % col_letter)
2487 return
2489 (col_first, col_last) = self.filter_range
2491 # Reject column if it is outside filter range.
2492 if col < col_first or col > col_last:
2493 warn(
2494 "Column '%d' outside autofilter() column range "
2495 "(%d,%d)" % (col, col_first, col_last)
2496 )
2497 return
2499 self.filter_cols[col] = filters
2500 self.filter_type[col] = 1
2501 self.filter_on = 1
2503 @convert_range_args
2504 def data_validation(self, first_row, first_col, last_row, last_col, options=None):
2505 """
2506 Add a data validation to a worksheet.
2508 Args:
2509 first_row: The first row of the cell range. (zero indexed).
2510 first_col: The first column of the cell range.
2511 last_row: The last row of the cell range. (zero indexed).
2512 last_col: The last column of the cell range.
2513 options: Data validation options.
2515 Returns:
2516 0: Success.
2517 -1: Row or column is out of worksheet bounds.
2518 -2: Incorrect parameter or option.
2519 """
2520 # Check that row and col are valid without storing the values.
2521 if self._check_dimensions(first_row, first_col, True, True):
2522 return -1
2523 if self._check_dimensions(last_row, last_col, True, True):
2524 return -1
2526 if options is None:
2527 options = {}
2528 else:
2529 # Copy the user defined options so they aren't modified.
2530 options = options.copy()
2532 # Valid input parameters.
2533 valid_parameters = {
2534 "validate",
2535 "criteria",
2536 "value",
2537 "source",
2538 "minimum",
2539 "maximum",
2540 "ignore_blank",
2541 "dropdown",
2542 "show_input",
2543 "input_title",
2544 "input_message",
2545 "show_error",
2546 "error_title",
2547 "error_message",
2548 "error_type",
2549 "other_cells",
2550 "multi_range",
2551 }
2553 # Check for valid input parameters.
2554 for param_key in options.keys():
2555 if param_key not in valid_parameters:
2556 warn("Unknown parameter '%s' in data_validation()" % param_key)
2557 return -2
2559 # Map alternative parameter names 'source' or 'minimum' to 'value'.
2560 if "source" in options:
2561 options["value"] = options["source"]
2562 if "minimum" in options:
2563 options["value"] = options["minimum"]
2565 # 'validate' is a required parameter.
2566 if "validate" not in options:
2567 warn("Parameter 'validate' is required in data_validation()")
2568 return -2
2570 # List of valid validation types.
2571 valid_types = {
2572 "any": "none",
2573 "any value": "none",
2574 "whole number": "whole",
2575 "whole": "whole",
2576 "integer": "whole",
2577 "decimal": "decimal",
2578 "list": "list",
2579 "date": "date",
2580 "time": "time",
2581 "text length": "textLength",
2582 "length": "textLength",
2583 "custom": "custom",
2584 }
2586 # Check for valid validation types.
2587 if options["validate"] not in valid_types:
2588 warn(
2589 "Unknown validation type '%s' for parameter "
2590 "'validate' in data_validation()" % options["validate"]
2591 )
2592 return -2
2593 else:
2594 options["validate"] = valid_types[options["validate"]]
2596 # No action is required for validation type 'any' if there are no
2597 # input messages to display.
2598 if (
2599 options["validate"] == "none"
2600 and options.get("input_title") is None
2601 and options.get("input_message") is None
2602 ):
2603 return -2
2605 # The any, list and custom validations don't have a criteria so we use
2606 # a default of 'between'.
2607 if (
2608 options["validate"] == "none"
2609 or options["validate"] == "list"
2610 or options["validate"] == "custom"
2611 ):
2612 options["criteria"] = "between"
2613 options["maximum"] = None
2615 # 'criteria' is a required parameter.
2616 if "criteria" not in options:
2617 warn("Parameter 'criteria' is required in data_validation()")
2618 return -2
2620 # Valid criteria types.
2621 criteria_types = {
2622 "between": "between",
2623 "not between": "notBetween",
2624 "equal to": "equal",
2625 "=": "equal",
2626 "==": "equal",
2627 "not equal to": "notEqual",
2628 "!=": "notEqual",
2629 "<>": "notEqual",
2630 "greater than": "greaterThan",
2631 ">": "greaterThan",
2632 "less than": "lessThan",
2633 "<": "lessThan",
2634 "greater than or equal to": "greaterThanOrEqual",
2635 ">=": "greaterThanOrEqual",
2636 "less than or equal to": "lessThanOrEqual",
2637 "<=": "lessThanOrEqual",
2638 }
2640 # Check for valid criteria types.
2641 if options["criteria"] not in criteria_types:
2642 warn(
2643 "Unknown criteria type '%s' for parameter "
2644 "'criteria' in data_validation()" % options["criteria"]
2645 )
2646 return -2
2647 else:
2648 options["criteria"] = criteria_types[options["criteria"]]
2650 # 'Between' and 'Not between' criteria require 2 values.
2651 if options["criteria"] == "between" or options["criteria"] == "notBetween":
2652 if "maximum" not in options:
2653 warn(
2654 "Parameter 'maximum' is required in data_validation() "
2655 "when using 'between' or 'not between' criteria"
2656 )
2657 return -2
2658 else:
2659 options["maximum"] = None
2661 # Valid error dialog types.
2662 error_types = {
2663 "stop": 0,
2664 "warning": 1,
2665 "information": 2,
2666 }
2668 # Check for valid error dialog types.
2669 if "error_type" not in options:
2670 options["error_type"] = 0
2671 elif options["error_type"] not in error_types:
2672 warn(
2673 "Unknown criteria type '%s' for parameter 'error_type' "
2674 "in data_validation()" % options["error_type"]
2675 )
2676 return -2
2677 else:
2678 options["error_type"] = error_types[options["error_type"]]
2680 # Convert date/times value if required.
2681 if (
2682 options["validate"] in ("date", "time")
2683 and options["value"]
2684 and supported_datetime(options["value"])
2685 ):
2686 date_time = self._convert_date_time(options["value"])
2687 # Format date number to the same precision as Excel.
2688 options["value"] = "%.16g" % date_time
2690 if options["maximum"] and supported_datetime(options["maximum"]):
2691 date_time = self._convert_date_time(options["maximum"])
2692 options["maximum"] = "%.16g" % date_time
2694 # Check that the input title doesn't exceed the maximum length.
2695 if options.get("input_title") and len(options["input_title"]) > 32:
2696 warn(
2697 "Length of input title '%s' exceeds Excel's limit of 32"
2698 % options["input_title"]
2699 )
2700 return -2
2702 # Check that the error title doesn't exceed the maximum length.
2703 if options.get("error_title") and len(options["error_title"]) > 32:
2704 warn(
2705 "Length of error title '%s' exceeds Excel's limit of 32"
2706 % options["error_title"]
2707 )
2708 return -2
2710 # Check that the input message doesn't exceed the maximum length.
2711 if options.get("input_message") and len(options["input_message"]) > 255:
2712 warn(
2713 "Length of input message '%s' exceeds Excel's limit of 255"
2714 % options["input_message"]
2715 )
2716 return -2
2718 # Check that the error message doesn't exceed the maximum length.
2719 if options.get("error_message") and len(options["error_message"]) > 255:
2720 warn(
2721 "Length of error message '%s' exceeds Excel's limit of 255"
2722 % options["error_message"]
2723 )
2724 return -2
2726 # Check that the input list doesn't exceed the maximum length.
2727 if options["validate"] == "list" and isinstance(options["value"], list):
2728 formula = self._csv_join(*options["value"])
2729 if len(formula) > 255:
2730 warn(
2731 "Length of list items '%s' exceeds Excel's limit of "
2732 "255, use a formula range instead" % formula
2733 )
2734 return -2
2736 # Set some defaults if they haven't been defined by the user.
2737 if "ignore_blank" not in options:
2738 options["ignore_blank"] = 1
2739 if "dropdown" not in options:
2740 options["dropdown"] = 1
2741 if "show_input" not in options:
2742 options["show_input"] = 1
2743 if "show_error" not in options:
2744 options["show_error"] = 1
2746 # These are the cells to which the validation is applied.
2747 options["cells"] = [[first_row, first_col, last_row, last_col]]
2749 # A (for now) undocumented parameter to pass additional cell ranges.
2750 if "other_cells" in options:
2751 options["cells"].extend(options["other_cells"])
2753 # Override with user defined multiple range if provided.
2754 if "multi_range" in options:
2755 options["multi_range"] = options["multi_range"].replace("$", "")
2757 # Store the validation information until we close the worksheet.
2758 self.validations.append(options)
2760 return 0
2762 @convert_range_args
2763 def conditional_format(
2764 self, first_row, first_col, last_row, last_col, options=None
2765 ):
2766 """
2767 Add a conditional format to a worksheet.
2769 Args:
2770 first_row: The first row of the cell range. (zero indexed).
2771 first_col: The first column of the cell range.
2772 last_row: The last row of the cell range. (zero indexed).
2773 last_col: The last column of the cell range.
2774 options: Conditional format options.
2776 Returns:
2777 0: Success.
2778 -1: Row or column is out of worksheet bounds.
2779 -2: Incorrect parameter or option.
2780 """
2781 # Check that row and col are valid without storing the values.
2782 if self._check_dimensions(first_row, first_col, True, True):
2783 return -1
2784 if self._check_dimensions(last_row, last_col, True, True):
2785 return -1
2787 if options is None:
2788 options = {}
2789 else:
2790 # Copy the user defined options so they aren't modified.
2791 options = options.copy()
2793 # Valid input parameters.
2794 valid_parameter = {
2795 "type",
2796 "format",
2797 "criteria",
2798 "value",
2799 "minimum",
2800 "maximum",
2801 "stop_if_true",
2802 "min_type",
2803 "mid_type",
2804 "max_type",
2805 "min_value",
2806 "mid_value",
2807 "max_value",
2808 "min_color",
2809 "mid_color",
2810 "max_color",
2811 "min_length",
2812 "max_length",
2813 "multi_range",
2814 "bar_color",
2815 "bar_negative_color",
2816 "bar_negative_color_same",
2817 "bar_solid",
2818 "bar_border_color",
2819 "bar_negative_border_color",
2820 "bar_negative_border_color_same",
2821 "bar_no_border",
2822 "bar_direction",
2823 "bar_axis_position",
2824 "bar_axis_color",
2825 "bar_only",
2826 "data_bar_2010",
2827 "icon_style",
2828 "reverse_icons",
2829 "icons_only",
2830 "icons",
2831 }
2833 # Check for valid input parameters.
2834 for param_key in options.keys():
2835 if param_key not in valid_parameter:
2836 warn("Unknown parameter '%s' in conditional_format()" % param_key)
2837 return -2
2839 # 'type' is a required parameter.
2840 if "type" not in options:
2841 warn("Parameter 'type' is required in conditional_format()")
2842 return -2
2844 # Valid types.
2845 valid_type = {
2846 "cell": "cellIs",
2847 "date": "date",
2848 "time": "time",
2849 "average": "aboveAverage",
2850 "duplicate": "duplicateValues",
2851 "unique": "uniqueValues",
2852 "top": "top10",
2853 "bottom": "top10",
2854 "text": "text",
2855 "time_period": "timePeriod",
2856 "blanks": "containsBlanks",
2857 "no_blanks": "notContainsBlanks",
2858 "errors": "containsErrors",
2859 "no_errors": "notContainsErrors",
2860 "2_color_scale": "2_color_scale",
2861 "3_color_scale": "3_color_scale",
2862 "data_bar": "dataBar",
2863 "formula": "expression",
2864 "icon_set": "iconSet",
2865 }
2867 # Check for valid types.
2868 if options["type"] not in valid_type:
2869 warn(
2870 "Unknown value '%s' for parameter 'type' "
2871 "in conditional_format()" % options["type"]
2872 )
2873 return -2
2874 else:
2875 if options["type"] == "bottom":
2876 options["direction"] = "bottom"
2877 options["type"] = valid_type[options["type"]]
2879 # Valid criteria types.
2880 criteria_type = {
2881 "between": "between",
2882 "not between": "notBetween",
2883 "equal to": "equal",
2884 "=": "equal",
2885 "==": "equal",
2886 "not equal to": "notEqual",
2887 "!=": "notEqual",
2888 "<>": "notEqual",
2889 "greater than": "greaterThan",
2890 ">": "greaterThan",
2891 "less than": "lessThan",
2892 "<": "lessThan",
2893 "greater than or equal to": "greaterThanOrEqual",
2894 ">=": "greaterThanOrEqual",
2895 "less than or equal to": "lessThanOrEqual",
2896 "<=": "lessThanOrEqual",
2897 "containing": "containsText",
2898 "not containing": "notContains",
2899 "begins with": "beginsWith",
2900 "ends with": "endsWith",
2901 "yesterday": "yesterday",
2902 "today": "today",
2903 "last 7 days": "last7Days",
2904 "last week": "lastWeek",
2905 "this week": "thisWeek",
2906 "next week": "nextWeek",
2907 "last month": "lastMonth",
2908 "this month": "thisMonth",
2909 "next month": "nextMonth",
2910 # For legacy, but incorrect, support.
2911 "continue week": "nextWeek",
2912 "continue month": "nextMonth",
2913 }
2915 # Check for valid criteria types.
2916 if "criteria" in options and options["criteria"] in criteria_type:
2917 options["criteria"] = criteria_type[options["criteria"]]
2919 # Convert date/times value if required.
2920 if options["type"] in ("date", "time"):
2921 options["type"] = "cellIs"
2923 if "value" in options:
2924 if not supported_datetime(options["value"]):
2925 warn("Conditional format 'value' must be a datetime object.")
2926 return -2
2927 else:
2928 date_time = self._convert_date_time(options["value"])
2929 # Format date number to the same precision as Excel.
2930 options["value"] = "%.16g" % date_time
2932 if "minimum" in options:
2933 if not supported_datetime(options["minimum"]):
2934 warn("Conditional format 'minimum' must be a datetime object.")
2935 return -2
2936 else:
2937 date_time = self._convert_date_time(options["minimum"])
2938 options["minimum"] = "%.16g" % date_time
2940 if "maximum" in options:
2941 if not supported_datetime(options["maximum"]):
2942 warn("Conditional format 'maximum' must be a datetime object.")
2943 return -2
2944 else:
2945 date_time = self._convert_date_time(options["maximum"])
2946 options["maximum"] = "%.16g" % date_time
2948 # Valid icon styles.
2949 valid_icons = {
2950 "3_arrows": "3Arrows", # 1
2951 "3_flags": "3Flags", # 2
2952 "3_traffic_lights_rimmed": "3TrafficLights2", # 3
2953 "3_symbols_circled": "3Symbols", # 4
2954 "4_arrows": "4Arrows", # 5
2955 "4_red_to_black": "4RedToBlack", # 6
2956 "4_traffic_lights": "4TrafficLights", # 7
2957 "5_arrows_gray": "5ArrowsGray", # 8
2958 "5_quarters": "5Quarters", # 9
2959 "3_arrows_gray": "3ArrowsGray", # 10
2960 "3_traffic_lights": "3TrafficLights", # 11
2961 "3_signs": "3Signs", # 12
2962 "3_symbols": "3Symbols2", # 13
2963 "4_arrows_gray": "4ArrowsGray", # 14
2964 "4_ratings": "4Rating", # 15
2965 "5_arrows": "5Arrows", # 16
2966 "5_ratings": "5Rating",
2967 } # 17
2969 # Set the icon set properties.
2970 if options["type"] == "iconSet":
2971 # An icon_set must have an icon style.
2972 if not options.get("icon_style"):
2973 warn(
2974 "The 'icon_style' parameter must be specified when "
2975 "'type' == 'icon_set' in conditional_format()"
2976 )
2977 return -3
2979 # Check for valid icon styles.
2980 if options["icon_style"] not in valid_icons:
2981 warn(
2982 "Unknown icon_style '%s' in conditional_format()"
2983 % options["icon_style"]
2984 )
2985 return -2
2986 else:
2987 options["icon_style"] = valid_icons[options["icon_style"]]
2989 # Set the number of icons for the icon style.
2990 options["total_icons"] = 3
2991 if options["icon_style"].startswith("4"):
2992 options["total_icons"] = 4
2993 elif options["icon_style"].startswith("5"):
2994 options["total_icons"] = 5
2996 options["icons"] = self._set_icon_props(
2997 options.get("total_icons"), options.get("icons")
2998 )
3000 # Swap last row/col for first row/col as necessary
3001 if first_row > last_row:
3002 first_row, last_row = last_row, first_row
3004 if first_col > last_col:
3005 first_col, last_col = last_col, first_col
3007 # Set the formatting range.
3008 cell_range = xl_range(first_row, first_col, last_row, last_col)
3009 start_cell = xl_rowcol_to_cell(first_row, first_col)
3011 # Override with user defined multiple range if provided.
3012 if "multi_range" in options:
3013 cell_range = options["multi_range"]
3014 cell_range = cell_range.replace("$", "")
3016 # Get the dxf format index.
3017 if "format" in options and options["format"]:
3018 options["format"] = options["format"]._get_dxf_index()
3020 # Set the priority based on the order of adding.
3021 options["priority"] = self.dxf_priority
3022 self.dxf_priority += 1
3024 # Check for 2010 style data_bar parameters.
3025 if (
3026 self.use_data_bars_2010
3027 or options.get("data_bar_2010")
3028 or options.get("bar_solid")
3029 or options.get("bar_border_color")
3030 or options.get("bar_negative_color")
3031 or options.get("bar_negative_color_same")
3032 or options.get("bar_negative_border_color")
3033 or options.get("bar_negative_border_color_same")
3034 or options.get("bar_no_border")
3035 or options.get("bar_axis_position")
3036 or options.get("bar_axis_color")
3037 or options.get("bar_direction")
3038 ):
3039 options["is_data_bar_2010"] = True
3041 # Special handling of text criteria.
3042 if options["type"] == "text":
3043 if options["criteria"] == "containsText":
3044 options["type"] = "containsText"
3045 options["formula"] = 'NOT(ISERROR(SEARCH("%s",%s)))' % (
3046 options["value"],
3047 start_cell,
3048 )
3049 elif options["criteria"] == "notContains":
3050 options["type"] = "notContainsText"
3051 options["formula"] = 'ISERROR(SEARCH("%s",%s))' % (
3052 options["value"],
3053 start_cell,
3054 )
3055 elif options["criteria"] == "beginsWith":
3056 options["type"] = "beginsWith"
3057 options["formula"] = 'LEFT(%s,%d)="%s"' % (
3058 start_cell,
3059 len(options["value"]),
3060 options["value"],
3061 )
3062 elif options["criteria"] == "endsWith":
3063 options["type"] = "endsWith"
3064 options["formula"] = 'RIGHT(%s,%d)="%s"' % (
3065 start_cell,
3066 len(options["value"]),
3067 options["value"],
3068 )
3069 else:
3070 warn(
3071 "Invalid text criteria '%s' "
3072 "in conditional_format()" % options["criteria"]
3073 )
3075 # Special handling of time time_period criteria.
3076 if options["type"] == "timePeriod":
3077 if options["criteria"] == "yesterday":
3078 options["formula"] = "FLOOR(%s,1)=TODAY()-1" % start_cell
3080 elif options["criteria"] == "today":
3081 options["formula"] = "FLOOR(%s,1)=TODAY()" % start_cell
3083 elif options["criteria"] == "tomorrow":
3084 options["formula"] = "FLOOR(%s,1)=TODAY()+1" % start_cell
3086 # fmt: off
3087 elif options["criteria"] == "last7Days":
3088 options["formula"] = (
3089 "AND(TODAY()-FLOOR(%s,1)<=6,FLOOR(%s,1)<=TODAY())"
3090 % (start_cell, start_cell)
3091 )
3092 # fmt: on
3094 elif options["criteria"] == "lastWeek":
3095 options["formula"] = (
3096 "AND(TODAY()-ROUNDDOWN(%s,0)>=(WEEKDAY(TODAY())),"
3097 "TODAY()-ROUNDDOWN(%s,0)<(WEEKDAY(TODAY())+7))"
3098 % (start_cell, start_cell)
3099 )
3101 elif options["criteria"] == "thisWeek":
3102 options["formula"] = (
3103 "AND(TODAY()-ROUNDDOWN(%s,0)<=WEEKDAY(TODAY())-1,"
3104 "ROUNDDOWN(%s,0)-TODAY()<=7-WEEKDAY(TODAY()))"
3105 % (start_cell, start_cell)
3106 )
3108 elif options["criteria"] == "nextWeek":
3109 options["formula"] = (
3110 "AND(ROUNDDOWN(%s,0)-TODAY()>(7-WEEKDAY(TODAY())),"
3111 "ROUNDDOWN(%s,0)-TODAY()<(15-WEEKDAY(TODAY())))"
3112 % (start_cell, start_cell)
3113 )
3115 elif options["criteria"] == "lastMonth":
3116 options["formula"] = (
3117 "AND(MONTH(%s)=MONTH(TODAY())-1,OR(YEAR(%s)=YEAR("
3118 "TODAY()),AND(MONTH(%s)=1,YEAR(A1)=YEAR(TODAY())-1)))"
3119 % (start_cell, start_cell, start_cell)
3120 )
3122 # fmt: off
3123 elif options["criteria"] == "thisMonth":
3124 options["formula"] = (
3125 "AND(MONTH(%s)=MONTH(TODAY()),YEAR(%s)=YEAR(TODAY()))"
3126 % (start_cell, start_cell)
3127 )
3128 # fmt: on
3130 elif options["criteria"] == "nextMonth":
3131 options["formula"] = (
3132 "AND(MONTH(%s)=MONTH(TODAY())+1,OR(YEAR(%s)=YEAR("
3133 "TODAY()),AND(MONTH(%s)=12,YEAR(%s)=YEAR(TODAY())+1)))"
3134 % (start_cell, start_cell, start_cell, start_cell)
3135 )
3137 else:
3138 warn(
3139 "Invalid time_period criteria '%s' "
3140 "in conditional_format()" % options["criteria"]
3141 )
3143 # Special handling of blanks/error types.
3144 if options["type"] == "containsBlanks":
3145 options["formula"] = "LEN(TRIM(%s))=0" % start_cell
3147 if options["type"] == "notContainsBlanks":
3148 options["formula"] = "LEN(TRIM(%s))>0" % start_cell
3150 if options["type"] == "containsErrors":
3151 options["formula"] = "ISERROR(%s)" % start_cell
3153 if options["type"] == "notContainsErrors":
3154 options["formula"] = "NOT(ISERROR(%s))" % start_cell
3156 # Special handling for 2 color scale.
3157 if options["type"] == "2_color_scale":
3158 options["type"] = "colorScale"
3160 # Color scales don't use any additional formatting.
3161 options["format"] = None
3163 # Turn off 3 color parameters.
3164 options["mid_type"] = None
3165 options["mid_color"] = None
3167 options.setdefault("min_type", "min")
3168 options.setdefault("max_type", "max")
3169 options.setdefault("min_value", 0)
3170 options.setdefault("max_value", 0)
3171 options.setdefault("min_color", "#FF7128")
3172 options.setdefault("max_color", "#FFEF9C")
3174 options["min_color"] = xl_color(options["min_color"])
3175 options["max_color"] = xl_color(options["max_color"])
3177 # Special handling for 3 color scale.
3178 if options["type"] == "3_color_scale":
3179 options["type"] = "colorScale"
3181 # Color scales don't use any additional formatting.
3182 options["format"] = None
3184 options.setdefault("min_type", "min")
3185 options.setdefault("mid_type", "percentile")
3186 options.setdefault("max_type", "max")
3187 options.setdefault("min_value", 0)
3188 options.setdefault("max_value", 0)
3189 options.setdefault("min_color", "#F8696B")
3190 options.setdefault("mid_color", "#FFEB84")
3191 options.setdefault("max_color", "#63BE7B")
3193 options["min_color"] = xl_color(options["min_color"])
3194 options["mid_color"] = xl_color(options["mid_color"])
3195 options["max_color"] = xl_color(options["max_color"])
3197 # Set a default mid value.
3198 if "mid_value" not in options:
3199 options["mid_value"] = 50
3201 # Special handling for data bar.
3202 if options["type"] == "dataBar":
3203 # Color scales don't use any additional formatting.
3204 options["format"] = None
3206 if not options.get("min_type"):
3207 options["min_type"] = "min"
3208 options["x14_min_type"] = "autoMin"
3209 else:
3210 options["x14_min_type"] = options["min_type"]
3212 if not options.get("max_type"):
3213 options["max_type"] = "max"
3214 options["x14_max_type"] = "autoMax"
3215 else:
3216 options["x14_max_type"] = options["max_type"]
3218 options.setdefault("min_value", 0)
3219 options.setdefault("max_value", 0)
3220 options.setdefault("bar_color", "#638EC6")
3221 options.setdefault("bar_border_color", options["bar_color"])
3222 options.setdefault("bar_only", False)
3223 options.setdefault("bar_no_border", False)
3224 options.setdefault("bar_solid", False)
3225 options.setdefault("bar_direction", "")
3226 options.setdefault("bar_negative_color", "#FF0000")
3227 options.setdefault("bar_negative_border_color", "#FF0000")
3228 options.setdefault("bar_negative_color_same", False)
3229 options.setdefault("bar_negative_border_color_same", False)
3230 options.setdefault("bar_axis_position", "")
3231 options.setdefault("bar_axis_color", "#000000")
3233 options["bar_color"] = xl_color(options["bar_color"])
3234 options["bar_border_color"] = xl_color(options["bar_border_color"])
3235 options["bar_axis_color"] = xl_color(options["bar_axis_color"])
3236 options["bar_negative_color"] = xl_color(options["bar_negative_color"])
3237 options["bar_negative_border_color"] = xl_color(
3238 options["bar_negative_border_color"]
3239 )
3241 # Adjust for 2010 style data_bar parameters.
3242 if options.get("is_data_bar_2010"):
3243 self.excel_version = 2010
3245 if options["min_type"] == "min" and options["min_value"] == 0:
3246 options["min_value"] = None
3248 if options["max_type"] == "max" and options["max_value"] == 0:
3249 options["max_value"] = None
3251 options["range"] = cell_range
3253 # Strip the leading = from formulas.
3254 try:
3255 options["min_value"] = options["min_value"].lstrip("=")
3256 except (KeyError, AttributeError):
3257 pass
3258 try:
3259 options["mid_value"] = options["mid_value"].lstrip("=")
3260 except (KeyError, AttributeError):
3261 pass
3262 try:
3263 options["max_value"] = options["max_value"].lstrip("=")
3264 except (KeyError, AttributeError):
3265 pass
3267 # Store the conditional format until we close the worksheet.
3268 if cell_range in self.cond_formats:
3269 self.cond_formats[cell_range].append(options)
3270 else:
3271 self.cond_formats[cell_range] = [options]
3273 return 0
3275 @convert_range_args
3276 def add_table(self, first_row, first_col, last_row, last_col, options=None):
3277 """
3278 Add an Excel table to a worksheet.
3280 Args:
3281 first_row: The first row of the cell range. (zero indexed).
3282 first_col: The first column of the cell range.
3283 last_row: The last row of the cell range. (zero indexed).
3284 last_col: The last column of the cell range.
3285 options: Table format options. (Optional)
3287 Returns:
3288 0: Success.
3289 -1: Row or column is out of worksheet bounds.
3290 -2: Incorrect parameter or option.
3291 -3: Not supported in constant_memory mode.
3292 """
3293 table = {}
3294 col_formats = {}
3296 if options is None:
3297 options = {}
3298 else:
3299 # Copy the user defined options so they aren't modified.
3300 options = options.copy()
3302 if self.constant_memory:
3303 warn("add_table() isn't supported in 'constant_memory' mode")
3304 return -3
3306 # Check that row and col are valid without storing the values.
3307 if self._check_dimensions(first_row, first_col, True, True):
3308 return -1
3309 if self._check_dimensions(last_row, last_col, True, True):
3310 return -1
3312 # Swap last row/col for first row/col as necessary.
3313 if first_row > last_row:
3314 (first_row, last_row) = (last_row, first_row)
3315 if first_col > last_col:
3316 (first_col, last_col) = (last_col, first_col)
3318 # Check if the table range overlaps a previous merged or table range.
3319 # This is a critical file corruption error in Excel.
3320 cell_range = xl_range(first_row, first_col, last_row, last_col)
3321 for row in range(first_row, last_row + 1):
3322 for col in range(first_col, last_col + 1):
3323 if self.table_cells.get((row, col)):
3324 previous_range = self.table_cells.get((row, col))
3325 raise OverlappingRange(
3326 "Table range '%s' overlaps previous table range '%s'."
3327 % (cell_range, previous_range)
3328 )
3329 elif self.merged_cells.get((row, col)):
3330 previous_range = self.merged_cells.get((row, col))
3331 raise OverlappingRange(
3332 "Table range '%s' overlaps previous merge range '%s'."
3333 % (cell_range, previous_range)
3334 )
3335 else:
3336 self.table_cells[(row, col)] = cell_range
3338 # Valid input parameters.
3339 valid_parameter = {
3340 "autofilter",
3341 "banded_columns",
3342 "banded_rows",
3343 "columns",
3344 "data",
3345 "first_column",
3346 "header_row",
3347 "last_column",
3348 "name",
3349 "style",
3350 "total_row",
3351 }
3353 # Check for valid input parameters.
3354 for param_key in options.keys():
3355 if param_key not in valid_parameter:
3356 warn("Unknown parameter '%s' in add_table()" % param_key)
3357 return -2
3359 # Turn on Excel's defaults.
3360 options["banded_rows"] = options.get("banded_rows", True)
3361 options["header_row"] = options.get("header_row", True)
3362 options["autofilter"] = options.get("autofilter", True)
3364 # Check that there are enough rows.
3365 num_rows = last_row - first_row
3366 if options["header_row"]:
3367 num_rows -= 1
3369 if num_rows < 0:
3370 warn("Must have at least one data row in in add_table()")
3371 return -2
3373 # Set the table options.
3374 table["show_first_col"] = options.get("first_column", False)
3375 table["show_last_col"] = options.get("last_column", False)
3376 table["show_row_stripes"] = options.get("banded_rows", False)
3377 table["show_col_stripes"] = options.get("banded_columns", False)
3378 table["header_row_count"] = options.get("header_row", 0)
3379 table["totals_row_shown"] = options.get("total_row", False)
3381 # Set the table name.
3382 if "name" in options:
3383 name = options["name"]
3384 table["name"] = name
3386 if " " in name:
3387 warn("Name '%s' in add_table() cannot contain spaces" % name)
3388 return -2
3390 # Warn if the name contains invalid chars as defined by Excel.
3391 if not re.match(r"^[\w\\][\w\\.]*$", name, re.UNICODE) or re.match(
3392 r"^\d", name
3393 ):
3394 warn("Invalid Excel characters in add_table(): '%s'" % name)
3395 return -2
3397 # Warn if the name looks like a cell name.
3398 if re.match(r"^[a-zA-Z][a-zA-Z]?[a-dA-D]?\d+$", name):
3399 warn("Name looks like a cell name in add_table(): '%s'" % name)
3400 return -2
3402 # Warn if the name looks like a R1C1 cell reference.
3403 if re.match(r"^[rcRC]$", name) or re.match(r"^[rcRC]\d+[rcRC]\d+$", name):
3404 warn("Invalid name '%s' like a RC cell ref in add_table()" % name)
3405 return -2
3407 # Set the table style.
3408 if "style" in options:
3409 table["style"] = options["style"]
3411 if table["style"] is None:
3412 table["style"] = ""
3414 # Remove whitespace from style name.
3415 table["style"] = table["style"].replace(" ", "")
3416 else:
3417 table["style"] = "TableStyleMedium9"
3419 # Set the data range rows (without the header and footer).
3420 first_data_row = first_row
3421 last_data_row = last_row
3423 if options.get("header_row"):
3424 first_data_row += 1
3426 if options.get("total_row"):
3427 last_data_row -= 1
3429 # Set the table and autofilter ranges.
3430 table["range"] = xl_range(first_row, first_col, last_row, last_col)
3432 table["a_range"] = xl_range(first_row, first_col, last_data_row, last_col)
3434 # If the header row if off the default is to turn autofilter off.
3435 if not options["header_row"]:
3436 options["autofilter"] = 0
3438 # Set the autofilter range.
3439 if options["autofilter"]:
3440 table["autofilter"] = table["a_range"]
3442 # Add the table columns.
3443 col_id = 1
3444 table["columns"] = []
3445 seen_names = {}
3447 for col_num in range(first_col, last_col + 1):
3448 # Set up the default column data.
3449 col_data = {
3450 "id": col_id,
3451 "name": "Column" + str(col_id),
3452 "total_string": "",
3453 "total_function": "",
3454 "custom_total": "",
3455 "total_value": 0,
3456 "formula": "",
3457 "format": None,
3458 "name_format": None,
3459 }
3461 # Overwrite the defaults with any user defined values.
3462 if "columns" in options:
3463 # Check if there are user defined values for this column.
3464 if col_id <= len(options["columns"]):
3465 user_data = options["columns"][col_id - 1]
3466 else:
3467 user_data = None
3469 if user_data:
3470 # Get the column format.
3471 xformat = user_data.get("format", None)
3473 # Map user defined values to internal values.
3474 if user_data.get("header"):
3475 col_data["name"] = user_data["header"]
3477 # Excel requires unique case insensitive header names.
3478 header_name = col_data["name"]
3479 name = header_name.lower()
3480 if name in seen_names:
3481 warn("Duplicate header name in add_table(): '%s'" % name)
3482 return -2
3483 else:
3484 seen_names[name] = True
3486 col_data["name_format"] = user_data.get("header_format")
3488 # Handle the column formula.
3489 if "formula" in user_data and user_data["formula"]:
3490 formula = user_data["formula"]
3492 # Remove the formula '=' sign if it exists.
3493 if formula.startswith("="):
3494 formula = formula.lstrip("=")
3496 # Convert Excel 2010 "@" ref to 2007 "#This Row".
3497 formula = self._prepare_table_formula(formula)
3499 # Escape any future functions.
3500 formula = self._prepare_formula(formula, True)
3502 col_data["formula"] = formula
3503 # We write the formulas below after the table data.
3505 # Handle the function for the total row.
3506 if user_data.get("total_function"):
3507 function = user_data["total_function"]
3508 if function == "count_nums":
3509 function = "countNums"
3510 if function == "std_dev":
3511 function = "stdDev"
3513 subtotals = set(
3514 [
3515 "average",
3516 "countNums",
3517 "count",
3518 "max",
3519 "min",
3520 "stdDev",
3521 "sum",
3522 "var",
3523 ]
3524 )
3526 if function in subtotals:
3527 formula = self._table_function_to_formula(
3528 function, col_data["name"]
3529 )
3530 else:
3531 formula = self._prepare_formula(function, True)
3532 col_data["custom_total"] = formula
3533 function = "custom"
3535 col_data["total_function"] = function
3537 value = user_data.get("total_value", 0)
3539 self._write_formula(last_row, col_num, formula, xformat, value)
3541 elif user_data.get("total_string"):
3542 # Total label only (not a function).
3543 total_string = user_data["total_string"]
3544 col_data["total_string"] = total_string
3546 self._write_string(
3547 last_row, col_num, total_string, user_data.get("format")
3548 )
3550 # Get the dxf format index.
3551 if xformat is not None:
3552 col_data["format"] = xformat._get_dxf_index()
3554 # Store the column format for writing the cell data.
3555 # It doesn't matter if it is undefined.
3556 col_formats[col_id - 1] = xformat
3558 # Store the column data.
3559 table["columns"].append(col_data)
3561 # Write the column headers to the worksheet.
3562 if options["header_row"]:
3563 self._write_string(
3564 first_row, col_num, col_data["name"], col_data["name_format"]
3565 )
3567 col_id += 1
3569 # Write the cell data if supplied.
3570 if "data" in options:
3571 data = options["data"]
3573 i = 0 # For indexing the row data.
3574 for row in range(first_data_row, last_data_row + 1):
3575 j = 0 # For indexing the col data.
3576 for col in range(first_col, last_col + 1):
3577 if i < len(data) and j < len(data[i]):
3578 token = data[i][j]
3579 if j in col_formats:
3580 self._write(row, col, token, col_formats[j])
3581 else:
3582 self._write(row, col, token, None)
3583 j += 1
3584 i += 1
3586 # Write any columns formulas after the user supplied table data to
3587 # overwrite it if required.
3588 for col_id, col_num in enumerate(range(first_col, last_col + 1)):
3589 column_data = table["columns"][col_id]
3590 if column_data and column_data["formula"]:
3591 formula_format = col_formats.get(col_id)
3592 formula = column_data["formula"]
3594 for row in range(first_data_row, last_data_row + 1):
3595 self._write_formula(row, col_num, formula, formula_format)
3597 # Store the table data.
3598 self.tables.append(table)
3600 # Store the filter cell positions for use in the autofit calculation.
3601 if options["autofilter"]:
3602 for col in range(first_col, last_col + 1):
3603 self.filter_cells[(first_row, col)] = True
3605 return 0
3607 @convert_cell_args
3608 def add_sparkline(self, row, col, options=None):
3609 """
3610 Add sparklines to the worksheet.
3612 Args:
3613 row: The cell row (zero indexed).
3614 col: The cell column (zero indexed).
3615 options: Sparkline formatting options.
3617 Returns:
3618 0: Success.
3619 -1: Row or column is out of worksheet bounds.
3620 -2: Incorrect parameter or option.
3622 """
3624 # Check that row and col are valid without storing the values.
3625 if self._check_dimensions(row, col, True, True):
3626 return -1
3628 sparkline = {"locations": [xl_rowcol_to_cell(row, col)]}
3630 if options is None:
3631 options = {}
3633 # Valid input parameters.
3634 valid_parameters = {
3635 "location",
3636 "range",
3637 "type",
3638 "high_point",
3639 "low_point",
3640 "negative_points",
3641 "first_point",
3642 "last_point",
3643 "markers",
3644 "style",
3645 "series_color",
3646 "negative_color",
3647 "markers_color",
3648 "first_color",
3649 "last_color",
3650 "high_color",
3651 "low_color",
3652 "max",
3653 "min",
3654 "axis",
3655 "reverse",
3656 "empty_cells",
3657 "show_hidden",
3658 "plot_hidden",
3659 "date_axis",
3660 "weight",
3661 }
3663 # Check for valid input parameters.
3664 for param_key in options.keys():
3665 if param_key not in valid_parameters:
3666 warn("Unknown parameter '%s' in add_sparkline()" % param_key)
3667 return -1
3669 # 'range' is a required parameter.
3670 if "range" not in options:
3671 warn("Parameter 'range' is required in add_sparkline()")
3672 return -2
3674 # Handle the sparkline type.
3675 spark_type = options.get("type", "line")
3677 if spark_type not in ("line", "column", "win_loss"):
3678 warn(
3679 "Parameter 'type' must be 'line', 'column' "
3680 "or 'win_loss' in add_sparkline()"
3681 )
3682 return -2
3684 if spark_type == "win_loss":
3685 spark_type = "stacked"
3686 sparkline["type"] = spark_type
3688 # We handle single location/range values or list of values.
3689 if "location" in options:
3690 if isinstance(options["location"], list):
3691 sparkline["locations"] = options["location"]
3692 else:
3693 sparkline["locations"] = [options["location"]]
3695 if isinstance(options["range"], list):
3696 sparkline["ranges"] = options["range"]
3697 else:
3698 sparkline["ranges"] = [options["range"]]
3700 range_count = len(sparkline["ranges"])
3701 location_count = len(sparkline["locations"])
3703 # The ranges and locations must match.
3704 if range_count != location_count:
3705 warn(
3706 "Must have the same number of location and range "
3707 "parameters in add_sparkline()"
3708 )
3709 return -2
3711 # Store the count.
3712 sparkline["count"] = len(sparkline["locations"])
3714 # Get the worksheet name for the range conversion below.
3715 sheetname = quote_sheetname(self.name)
3717 # Cleanup the input ranges.
3718 new_ranges = []
3719 for spark_range in sparkline["ranges"]:
3720 # Remove the absolute reference $ symbols.
3721 spark_range = spark_range.replace("$", "")
3723 # Remove the = from formula.
3724 spark_range = spark_range.lstrip("=")
3726 # Convert a simple range into a full Sheet1!A1:D1 range.
3727 if "!" not in spark_range:
3728 spark_range = sheetname + "!" + spark_range
3730 new_ranges.append(spark_range)
3732 sparkline["ranges"] = new_ranges
3734 # Cleanup the input locations.
3735 new_locations = []
3736 for location in sparkline["locations"]:
3737 location = location.replace("$", "")
3738 new_locations.append(location)
3740 sparkline["locations"] = new_locations
3742 # Map options.
3743 sparkline["high"] = options.get("high_point")
3744 sparkline["low"] = options.get("low_point")
3745 sparkline["negative"] = options.get("negative_points")
3746 sparkline["first"] = options.get("first_point")
3747 sparkline["last"] = options.get("last_point")
3748 sparkline["markers"] = options.get("markers")
3749 sparkline["min"] = options.get("min")
3750 sparkline["max"] = options.get("max")
3751 sparkline["axis"] = options.get("axis")
3752 sparkline["reverse"] = options.get("reverse")
3753 sparkline["hidden"] = options.get("show_hidden")
3754 sparkline["weight"] = options.get("weight")
3756 # Map empty cells options.
3757 empty = options.get("empty_cells", "")
3759 if empty == "zero":
3760 sparkline["empty"] = 0
3761 elif empty == "connect":
3762 sparkline["empty"] = "span"
3763 else:
3764 sparkline["empty"] = "gap"
3766 # Map the date axis range.
3767 date_range = options.get("date_axis")
3769 if date_range and "!" not in date_range:
3770 date_range = sheetname + "!" + date_range
3772 sparkline["date_axis"] = date_range
3774 # Set the sparkline styles.
3775 style_id = options.get("style", 0)
3776 style = get_sparkline_style(style_id)
3778 sparkline["series_color"] = style["series"]
3779 sparkline["negative_color"] = style["negative"]
3780 sparkline["markers_color"] = style["markers"]
3781 sparkline["first_color"] = style["first"]
3782 sparkline["last_color"] = style["last"]
3783 sparkline["high_color"] = style["high"]
3784 sparkline["low_color"] = style["low"]
3786 # Override the style colors with user defined colors.
3787 self._set_spark_color(sparkline, options, "series_color")
3788 self._set_spark_color(sparkline, options, "negative_color")
3789 self._set_spark_color(sparkline, options, "markers_color")
3790 self._set_spark_color(sparkline, options, "first_color")
3791 self._set_spark_color(sparkline, options, "last_color")
3792 self._set_spark_color(sparkline, options, "high_color")
3793 self._set_spark_color(sparkline, options, "low_color")
3795 self.sparklines.append(sparkline)
3797 return 0
3799 @convert_range_args
3800 def set_selection(self, first_row, first_col, last_row, last_col):
3801 """
3802 Set the selected cell or cells in a worksheet
3804 Args:
3805 first_row: The first row of the cell range. (zero indexed).
3806 first_col: The first column of the cell range.
3807 last_row: The last row of the cell range. (zero indexed).
3808 last_col: The last column of the cell range.
3810 Returns:
3811 0: Nothing.
3812 """
3813 pane = None
3815 # Range selection. Do this before swapping max/min to allow the
3816 # selection direction to be reversed.
3817 active_cell = xl_rowcol_to_cell(first_row, first_col)
3819 # Swap last row/col for first row/col if necessary
3820 if first_row > last_row:
3821 (first_row, last_row) = (last_row, first_row)
3823 if first_col > last_col:
3824 (first_col, last_col) = (last_col, first_col)
3826 sqref = xl_range(first_row, first_col, last_row, last_col)
3828 # Selection isn't set for cell A1.
3829 if sqref == "A1":
3830 return
3832 self.selections = [[pane, active_cell, sqref]]
3834 @convert_cell_args
3835 def set_top_left_cell(self, row=0, col=0):
3836 """
3837 Set the first visible cell at the top left of a worksheet.
3839 Args:
3840 row: The cell row (zero indexed).
3841 col: The cell column (zero indexed).
3843 Returns:
3844 0: Nothing.
3845 """
3847 if row == 0 and col == 0:
3848 return
3850 self.top_left_cell = xl_rowcol_to_cell(row, col)
3852 def outline_settings(
3853 self, visible=1, symbols_below=1, symbols_right=1, auto_style=0
3854 ):
3855 """
3856 Control outline settings.
3858 Args:
3859 visible: Outlines are visible. Optional, defaults to True.
3860 symbols_below: Show row outline symbols below the outline bar.
3861 Optional, defaults to True.
3862 symbols_right: Show column outline symbols to the right of the
3863 outline bar. Optional, defaults to True.
3864 auto_style: Use Automatic style. Optional, defaults to False.
3866 Returns:
3867 0: Nothing.
3868 """
3869 self.outline_on = visible
3870 self.outline_below = symbols_below
3871 self.outline_right = symbols_right
3872 self.outline_style = auto_style
3874 self.outline_changed = True
3876 @convert_cell_args
3877 def freeze_panes(self, row, col, top_row=None, left_col=None, pane_type=0):
3878 """
3879 Create worksheet panes and mark them as frozen.
3881 Args:
3882 row: The cell row (zero indexed).
3883 col: The cell column (zero indexed).
3884 top_row: Topmost visible row in scrolling region of pane.
3885 left_col: Leftmost visible row in scrolling region of pane.
3887 Returns:
3888 0: Nothing.
3890 """
3891 if top_row is None:
3892 top_row = row
3894 if left_col is None:
3895 left_col = col
3897 self.panes = [row, col, top_row, left_col, pane_type]
3899 @convert_cell_args
3900 def split_panes(self, x, y, top_row=None, left_col=None):
3901 """
3902 Create worksheet panes and mark them as split.
3904 Args:
3905 x: The position for the vertical split.
3906 y: The position for the horizontal split.
3907 top_row: Topmost visible row in scrolling region of pane.
3908 left_col: Leftmost visible row in scrolling region of pane.
3910 Returns:
3911 0: Nothing.
3913 """
3914 # Same as freeze panes with a different pane type.
3915 self.freeze_panes(x, y, top_row, left_col, 2)
3917 def set_zoom(self, zoom=100):
3918 """
3919 Set the worksheet zoom factor.
3921 Args:
3922 zoom: Scale factor: 10 <= zoom <= 400.
3924 Returns:
3925 Nothing.
3927 """
3928 # Ensure the zoom scale is in Excel's range.
3929 if zoom < 10 or zoom > 400:
3930 warn("Zoom factor %d outside range: 10 <= zoom <= 400" % zoom)
3931 zoom = 100
3933 self.zoom = int(zoom)
3935 def right_to_left(self):
3936 """
3937 Display the worksheet right to left for some versions of Excel.
3939 Args:
3940 None.
3942 Returns:
3943 Nothing.
3945 """
3946 self.is_right_to_left = 1
3948 def hide_zero(self):
3949 """
3950 Hide zero values in worksheet cells.
3952 Args:
3953 None.
3955 Returns:
3956 Nothing.
3958 """
3959 self.show_zeros = 0
3961 def set_tab_color(self, color):
3962 """
3963 Set the color of the worksheet tab.
3965 Args:
3966 color: A #RGB color index.
3968 Returns:
3969 Nothing.
3971 """
3972 self.tab_color = xl_color(color)
3974 def protect(self, password="", options=None):
3975 """
3976 Set the password and protection options of the worksheet.
3978 Args:
3979 password: An optional password string.
3980 options: A dictionary of worksheet objects to protect.
3982 Returns:
3983 Nothing.
3985 """
3986 if password != "":
3987 password = self._encode_password(password)
3989 if not options:
3990 options = {}
3992 # Default values for objects that can be protected.
3993 defaults = {
3994 "sheet": True,
3995 "content": False,
3996 "objects": False,
3997 "scenarios": False,
3998 "format_cells": False,
3999 "format_columns": False,
4000 "format_rows": False,
4001 "insert_columns": False,
4002 "insert_rows": False,
4003 "insert_hyperlinks": False,
4004 "delete_columns": False,
4005 "delete_rows": False,
4006 "select_locked_cells": True,
4007 "sort": False,
4008 "autofilter": False,
4009 "pivot_tables": False,
4010 "select_unlocked_cells": True,
4011 }
4013 # Overwrite the defaults with user specified values.
4014 for key in options.keys():
4015 if key in defaults:
4016 defaults[key] = options[key]
4017 else:
4018 warn("Unknown protection object: '%s'" % key)
4020 # Set the password after the user defined values.
4021 defaults["password"] = password
4023 self.protect_options = defaults
4025 def unprotect_range(self, cell_range, range_name=None, password=None):
4026 """
4027 Unprotect ranges within a protected worksheet.
4029 Args:
4030 cell_range: The cell or cell range to unprotect.
4031 range_name: An optional name for the range.
4032 password: An optional password string. (undocumented)
4034 Returns:
4035 Nothing.
4037 """
4038 if cell_range is None:
4039 warn("Cell range must be specified in unprotect_range()")
4040 return -1
4042 # Sanitize the cell range.
4043 cell_range = cell_range.lstrip("=")
4044 cell_range = cell_range.replace("$", "")
4046 self.num_protected_ranges += 1
4048 if range_name is None:
4049 range_name = "Range" + str(self.num_protected_ranges)
4051 if password:
4052 password = self._encode_password(password)
4054 self.protected_ranges.append((cell_range, range_name, password))
4056 @convert_cell_args
4057 def insert_button(self, row, col, options=None):
4058 """
4059 Insert a button form object into the worksheet.
4061 Args:
4062 row: The cell row (zero indexed).
4063 col: The cell column (zero indexed).
4064 options: Button formatting options.
4066 Returns:
4067 0: Success.
4068 -1: Row or column is out of worksheet bounds.
4070 """
4071 # Check insert (row, col) without storing.
4072 if self._check_dimensions(row, col, True, True):
4073 warn("Cannot insert button at (%d, %d)." % (row, col))
4074 return -1
4076 if options is None:
4077 options = {}
4079 button = self._button_params(row, col, options)
4081 self.buttons_list.append(button)
4083 self.has_vml = 1
4085 return 0
4087 ###########################################################################
4088 #
4089 # Public API. Page Setup methods.
4090 #
4091 ###########################################################################
4092 def set_landscape(self):
4093 """
4094 Set the page orientation as landscape.
4096 Args:
4097 None.
4099 Returns:
4100 Nothing.
4102 """
4103 self.orientation = 0
4104 self.page_setup_changed = True
4106 def set_portrait(self):
4107 """
4108 Set the page orientation as portrait.
4110 Args:
4111 None.
4113 Returns:
4114 Nothing.
4116 """
4117 self.orientation = 1
4118 self.page_setup_changed = True
4120 def set_page_view(self, view=1):
4121 """
4122 Set the page view mode.
4124 Args:
4125 0: Normal view mode
4126 1: Page view mode (the default)
4127 2: Page break view mode
4129 Returns:
4130 Nothing.
4132 """
4133 self.page_view = view
4135 def set_pagebreak_view(self, view=1):
4136 """
4137 Set the page view mode.
4139 Args:
4140 None.
4142 Returns:
4143 Nothing.
4145 """
4146 self.page_view = 2
4148 def set_paper(self, paper_size):
4149 """
4150 Set the paper type. US Letter = 1, A4 = 9.
4152 Args:
4153 paper_size: Paper index.
4155 Returns:
4156 Nothing.
4158 """
4159 if paper_size:
4160 self.paper_size = paper_size
4161 self.page_setup_changed = True
4163 def center_horizontally(self):
4164 """
4165 Center the page horizontally.
4167 Args:
4168 None.
4170 Returns:
4171 Nothing.
4173 """
4174 self.print_options_changed = True
4175 self.hcenter = 1
4177 def center_vertically(self):
4178 """
4179 Center the page vertically.
4181 Args:
4182 None.
4184 Returns:
4185 Nothing.
4187 """
4188 self.print_options_changed = True
4189 self.vcenter = 1
4191 def set_margins(self, left=0.7, right=0.7, top=0.75, bottom=0.75):
4192 """
4193 Set all the page margins in inches.
4195 Args:
4196 left: Left margin.
4197 right: Right margin.
4198 top: Top margin.
4199 bottom: Bottom margin.
4201 Returns:
4202 Nothing.
4204 """
4205 self.margin_left = left
4206 self.margin_right = right
4207 self.margin_top = top
4208 self.margin_bottom = bottom
4210 def set_header(self, header="", options=None, margin=None):
4211 """
4212 Set the page header caption and optional margin.
4214 Args:
4215 header: Header string.
4216 margin: Header margin.
4217 options: Header options, mainly for images.
4219 Returns:
4220 Nothing.
4222 """
4223 header_orig = header
4224 header = header.replace("&[Picture]", "&G")
4226 if len(header) > 255:
4227 warn("Header string cannot be longer than Excel's limit of 255 characters")
4228 return
4230 if options is not None:
4231 # For backward compatibility allow options to be the margin.
4232 if not isinstance(options, dict):
4233 options = {"margin": options}
4234 else:
4235 options = {}
4237 # Copy the user defined options so they aren't modified.
4238 options = options.copy()
4240 # For backward compatibility.
4241 if margin is not None:
4242 options["margin"] = margin
4244 # Reset the list in case the function is called more than once.
4245 self.header_images = []
4247 if options.get("image_left"):
4248 self.header_images.append(
4249 [options.get("image_left"), options.get("image_data_left"), "LH"]
4250 )
4252 if options.get("image_center"):
4253 self.header_images.append(
4254 [options.get("image_center"), options.get("image_data_center"), "CH"]
4255 )
4257 if options.get("image_right"):
4258 self.header_images.append(
4259 [options.get("image_right"), options.get("image_data_right"), "RH"]
4260 )
4262 placeholder_count = header.count("&G")
4263 image_count = len(self.header_images)
4265 if placeholder_count != image_count:
4266 warn(
4267 "Number of header images (%s) doesn't match placeholder "
4268 "count (%s) in string: %s"
4269 % (image_count, placeholder_count, header_orig)
4270 )
4271 self.header_images = []
4272 return
4274 if "align_with_margins" in options:
4275 self.header_footer_aligns = options["align_with_margins"]
4277 if "scale_with_doc" in options:
4278 self.header_footer_scales = options["scale_with_doc"]
4280 self.header = header
4281 self.margin_header = options.get("margin", 0.3)
4282 self.header_footer_changed = True
4284 if image_count:
4285 self.has_header_vml = True
4287 def set_footer(self, footer="", options=None, margin=None):
4288 """
4289 Set the page footer caption and optional margin.
4291 Args:
4292 footer: Footer string.
4293 margin: Footer margin.
4294 options: Footer options, mainly for images.
4296 Returns:
4297 Nothing.
4299 """
4300 footer_orig = footer
4301 footer = footer.replace("&[Picture]", "&G")
4303 if len(footer) > 255:
4304 warn("Footer string cannot be longer than Excel's limit of 255 characters")
4305 return
4307 if options is not None:
4308 # For backward compatibility allow options to be the margin.
4309 if not isinstance(options, dict):
4310 options = {"margin": options}
4311 else:
4312 options = {}
4314 # Copy the user defined options so they aren't modified.
4315 options = options.copy()
4317 # For backward compatibility.
4318 if margin is not None:
4319 options["margin"] = margin
4321 # Reset the list in case the function is called more than once.
4322 self.footer_images = []
4324 if options.get("image_left"):
4325 self.footer_images.append(
4326 [options.get("image_left"), options.get("image_data_left"), "LF"]
4327 )
4329 if options.get("image_center"):
4330 self.footer_images.append(
4331 [options.get("image_center"), options.get("image_data_center"), "CF"]
4332 )
4334 if options.get("image_right"):
4335 self.footer_images.append(
4336 [options.get("image_right"), options.get("image_data_right"), "RF"]
4337 )
4339 placeholder_count = footer.count("&G")
4340 image_count = len(self.footer_images)
4342 if placeholder_count != image_count:
4343 warn(
4344 "Number of footer images (%s) doesn't match placeholder "
4345 "count (%s) in string: %s"
4346 % (image_count, placeholder_count, footer_orig)
4347 )
4348 self.footer_images = []
4349 return
4351 if "align_with_margins" in options:
4352 self.header_footer_aligns = options["align_with_margins"]
4354 if "scale_with_doc" in options:
4355 self.header_footer_scales = options["scale_with_doc"]
4357 self.footer = footer
4358 self.margin_footer = options.get("margin", 0.3)
4359 self.header_footer_changed = True
4361 if image_count:
4362 self.has_header_vml = True
4364 def repeat_rows(self, first_row, last_row=None):
4365 """
4366 Set the rows to repeat at the top of each printed page.
4368 Args:
4369 first_row: Start row for range.
4370 last_row: End row for range.
4372 Returns:
4373 Nothing.
4375 """
4376 if last_row is None:
4377 last_row = first_row
4379 # Convert rows to 1 based.
4380 first_row += 1
4381 last_row += 1
4383 # Create the row range area like: $1:$2.
4384 area = "$%d:$%d" % (first_row, last_row)
4386 # Build up the print titles area "Sheet1!$1:$2"
4387 sheetname = quote_sheetname(self.name)
4388 self.repeat_row_range = sheetname + "!" + area
4390 @convert_column_args
4391 def repeat_columns(self, first_col, last_col=None):
4392 """
4393 Set the columns to repeat at the left hand side of each printed page.
4395 Args:
4396 first_col: Start column for range.
4397 last_col: End column for range.
4399 Returns:
4400 Nothing.
4402 """
4403 if last_col is None:
4404 last_col = first_col
4406 # Convert to A notation.
4407 first_col = xl_col_to_name(first_col, 1)
4408 last_col = xl_col_to_name(last_col, 1)
4410 # Create a column range like $C:$D.
4411 area = first_col + ":" + last_col
4413 # Build up the print area range "=Sheet2!$C:$D"
4414 sheetname = quote_sheetname(self.name)
4415 self.repeat_col_range = sheetname + "!" + area
4417 def hide_gridlines(self, option=1):
4418 """
4419 Set the option to hide gridlines on the screen and the printed page.
4421 Args:
4422 option: 0 : Don't hide gridlines
4423 1 : Hide printed gridlines only
4424 2 : Hide screen and printed gridlines
4426 Returns:
4427 Nothing.
4429 """
4430 if option == 0:
4431 self.print_gridlines = 1
4432 self.screen_gridlines = 1
4433 self.print_options_changed = True
4434 elif option == 1:
4435 self.print_gridlines = 0
4436 self.screen_gridlines = 1
4437 else:
4438 self.print_gridlines = 0
4439 self.screen_gridlines = 0
4441 def print_row_col_headers(self):
4442 """
4443 Set the option to print the row and column headers on the printed page.
4445 Args:
4446 None.
4448 Returns:
4449 Nothing.
4451 """
4452 self.print_headers = True
4453 self.print_options_changed = True
4455 def hide_row_col_headers(self):
4456 """
4457 Set the option to hide the row and column headers on the worksheet.
4459 Args:
4460 None.
4462 Returns:
4463 Nothing.
4465 """
4466 self.row_col_headers = True
4468 @convert_range_args
4469 def print_area(self, first_row, first_col, last_row, last_col):
4470 """
4471 Set the print area in the current worksheet.
4473 Args:
4474 first_row: The first row of the cell range. (zero indexed).
4475 first_col: The first column of the cell range.
4476 last_row: The last row of the cell range. (zero indexed).
4477 last_col: The last column of the cell range.
4479 Returns:
4480 0: Success.
4481 -1: Row or column is out of worksheet bounds.
4483 """
4484 # Set the print area in the current worksheet.
4486 # Ignore max print area since it is the same as no area for Excel.
4487 if (
4488 first_row == 0
4489 and first_col == 0
4490 and last_row == self.xls_rowmax - 1
4491 and last_col == self.xls_colmax - 1
4492 ):
4493 return
4495 # Build up the print area range "Sheet1!$A$1:$C$13".
4496 area = self._convert_name_area(first_row, first_col, last_row, last_col)
4497 self.print_area_range = area
4499 return 0
4501 def print_across(self):
4502 """
4503 Set the order in which pages are printed.
4505 Args:
4506 None.
4508 Returns:
4509 Nothing.
4511 """
4512 self.page_order = 1
4513 self.page_setup_changed = True
4515 def fit_to_pages(self, width, height):
4516 """
4517 Fit the printed area to a specific number of pages both vertically and
4518 horizontally.
4520 Args:
4521 width: Number of pages horizontally.
4522 height: Number of pages vertically.
4524 Returns:
4525 Nothing.
4527 """
4528 self.fit_page = 1
4529 self.fit_width = width
4530 self.fit_height = height
4531 self.page_setup_changed = True
4533 def set_start_page(self, start_page):
4534 """
4535 Set the start page number when printing.
4537 Args:
4538 start_page: Start page number.
4540 Returns:
4541 Nothing.
4543 """
4544 self.page_start = start_page
4546 def set_print_scale(self, scale):
4547 """
4548 Set the scale factor for the printed page.
4550 Args:
4551 scale: Print scale. 10 <= scale <= 400.
4553 Returns:
4554 Nothing.
4556 """
4557 # Confine the scale to Excel's range.
4558 if scale < 10 or scale > 400:
4559 warn("Print scale '%d' outside range: 10 <= scale <= 400" % scale)
4560 return
4562 # Turn off "fit to page" option when print scale is on.
4563 self.fit_page = 0
4565 self.print_scale = int(scale)
4566 self.page_setup_changed = True
4568 def print_black_and_white(self):
4569 """
4570 Set the option to print the worksheet in black and white.
4572 Args:
4573 None.
4575 Returns:
4576 Nothing.
4578 """
4579 self.black_white = True
4580 self.page_setup_changed = True
4582 def set_h_pagebreaks(self, breaks):
4583 """
4584 Set the horizontal page breaks on a worksheet.
4586 Args:
4587 breaks: List of rows where the page breaks should be added.
4589 Returns:
4590 Nothing.
4592 """
4593 self.hbreaks = breaks
4595 def set_v_pagebreaks(self, breaks):
4596 """
4597 Set the horizontal page breaks on a worksheet.
4599 Args:
4600 breaks: List of columns where the page breaks should be added.
4602 Returns:
4603 Nothing.
4605 """
4606 self.vbreaks = breaks
4608 def set_vba_name(self, name=None):
4609 """
4610 Set the VBA name for the worksheet. By default this is the
4611 same as the sheet name: i.e., Sheet1 etc.
4613 Args:
4614 name: The VBA name for the worksheet.
4616 Returns:
4617 Nothing.
4619 """
4620 if name is not None:
4621 self.vba_codename = name
4622 else:
4623 self.vba_codename = "Sheet" + str(self.index + 1)
4625 def ignore_errors(self, options=None):
4626 """
4627 Ignore various Excel errors/warnings in a worksheet for user defined
4628 ranges.
4630 Args:
4631 options: A dict of ignore errors keys with cell range values.
4633 Returns:
4634 0: Success.
4635 -1: Incorrect parameter or option.
4637 """
4638 if options is None:
4639 return -1
4640 else:
4641 # Copy the user defined options so they aren't modified.
4642 options = options.copy()
4644 # Valid input parameters.
4645 valid_parameters = {
4646 "number_stored_as_text",
4647 "eval_error",
4648 "formula_differs",
4649 "formula_range",
4650 "formula_unlocked",
4651 "empty_cell_reference",
4652 "list_data_validation",
4653 "calculated_column",
4654 "two_digit_text_year",
4655 }
4657 # Check for valid input parameters.
4658 for param_key in options.keys():
4659 if param_key not in valid_parameters:
4660 warn("Unknown parameter '%s' in ignore_errors()" % param_key)
4661 return -1
4663 self.ignored_errors = options
4665 return 0
4667 ###########################################################################
4668 #
4669 # Private API.
4670 #
4671 ###########################################################################
4672 def _initialize(self, init_data):
4673 self.name = init_data["name"]
4674 self.index = init_data["index"]
4675 self.str_table = init_data["str_table"]
4676 self.worksheet_meta = init_data["worksheet_meta"]
4677 self.constant_memory = init_data["constant_memory"]
4678 self.tmpdir = init_data["tmpdir"]
4679 self.date_1904 = init_data["date_1904"]
4680 self.strings_to_numbers = init_data["strings_to_numbers"]
4681 self.strings_to_formulas = init_data["strings_to_formulas"]
4682 self.strings_to_urls = init_data["strings_to_urls"]
4683 self.nan_inf_to_errors = init_data["nan_inf_to_errors"]
4684 self.default_date_format = init_data["default_date_format"]
4685 self.default_url_format = init_data["default_url_format"]
4686 self.excel2003_style = init_data["excel2003_style"]
4687 self.remove_timezone = init_data["remove_timezone"]
4688 self.max_url_length = init_data["max_url_length"]
4689 self.use_future_functions = init_data["use_future_functions"]
4690 self.embedded_images = init_data["embedded_images"]
4692 if self.excel2003_style:
4693 self.original_row_height = 12.75
4694 self.default_row_height = 12.75
4695 self.default_row_pixels = 17
4696 self.margin_left = 0.75
4697 self.margin_right = 0.75
4698 self.margin_top = 1
4699 self.margin_bottom = 1
4700 self.margin_header = 0.5
4701 self.margin_footer = 0.5
4702 self.header_footer_aligns = False
4704 # Open a temp filehandle to store row data in constant_memory mode.
4705 if self.constant_memory:
4706 # This is sub-optimal but we need to create a temp file
4707 # with utf8 encoding in Python < 3.
4708 (fd, filename) = tempfile.mkstemp(dir=self.tmpdir)
4709 os.close(fd)
4710 self.row_data_filename = filename
4711 self.row_data_fh = open(filename, mode="w+", encoding="utf-8")
4713 # Set as the worksheet filehandle until the file is assembled.
4714 self.fh = self.row_data_fh
4716 def _assemble_xml_file(self):
4717 # Assemble and write the XML file.
4719 # Write the XML declaration.
4720 self._xml_declaration()
4722 # Write the root worksheet element.
4723 self._write_worksheet()
4725 # Write the worksheet properties.
4726 self._write_sheet_pr()
4728 # Write the worksheet dimensions.
4729 self._write_dimension()
4731 # Write the sheet view properties.
4732 self._write_sheet_views()
4734 # Write the sheet format properties.
4735 self._write_sheet_format_pr()
4737 # Write the sheet column info.
4738 self._write_cols()
4740 # Write the worksheet data such as rows columns and cells.
4741 if not self.constant_memory:
4742 self._write_sheet_data()
4743 else:
4744 self._write_optimized_sheet_data()
4746 # Write the sheetProtection element.
4747 self._write_sheet_protection()
4749 # Write the protectedRanges element.
4750 self._write_protected_ranges()
4752 # Write the phoneticPr element.
4753 if self.excel2003_style:
4754 self._write_phonetic_pr()
4756 # Write the autoFilter element.
4757 self._write_auto_filter()
4759 # Write the mergeCells element.
4760 self._write_merge_cells()
4762 # Write the conditional formats.
4763 self._write_conditional_formats()
4765 # Write the dataValidations element.
4766 self._write_data_validations()
4768 # Write the hyperlink element.
4769 self._write_hyperlinks()
4771 # Write the printOptions element.
4772 self._write_print_options()
4774 # Write the worksheet page_margins.
4775 self._write_page_margins()
4777 # Write the worksheet page setup.
4778 self._write_page_setup()
4780 # Write the headerFooter element.
4781 self._write_header_footer()
4783 # Write the rowBreaks element.
4784 self._write_row_breaks()
4786 # Write the colBreaks element.
4787 self._write_col_breaks()
4789 # Write the ignoredErrors element.
4790 self._write_ignored_errors()
4792 # Write the drawing element.
4793 self._write_drawings()
4795 # Write the legacyDrawing element.
4796 self._write_legacy_drawing()
4798 # Write the legacyDrawingHF element.
4799 self._write_legacy_drawing_hf()
4801 # Write the picture element, for the background.
4802 self._write_picture()
4804 # Write the tableParts element.
4805 self._write_table_parts()
4807 # Write the extLst elements.
4808 self._write_ext_list()
4810 # Close the worksheet tag.
4811 self._xml_end_tag("worksheet")
4813 # Close the file.
4814 self._xml_close()
4816 def _check_dimensions(self, row, col, ignore_row=False, ignore_col=False):
4817 # Check that row and col are valid and store the max and min
4818 # values for use in other methods/elements. The ignore_row /
4819 # ignore_col flags is used to indicate that we wish to perform
4820 # the dimension check without storing the value. The ignore
4821 # flags are use by set_row() and data_validate.
4823 # Check that the row/col are within the worksheet bounds.
4824 if row < 0 or col < 0:
4825 return -1
4826 if row >= self.xls_rowmax or col >= self.xls_colmax:
4827 return -1
4829 # In constant_memory mode we don't change dimensions for rows
4830 # that are already written.
4831 if not ignore_row and not ignore_col and self.constant_memory:
4832 if row < self.previous_row:
4833 return -2
4835 if not ignore_row:
4836 if self.dim_rowmin is None or row < self.dim_rowmin:
4837 self.dim_rowmin = row
4838 if self.dim_rowmax is None or row > self.dim_rowmax:
4839 self.dim_rowmax = row
4841 if not ignore_col:
4842 if self.dim_colmin is None or col < self.dim_colmin:
4843 self.dim_colmin = col
4844 if self.dim_colmax is None or col > self.dim_colmax:
4845 self.dim_colmax = col
4847 return 0
4849 def _convert_date_time(self, dt_obj):
4850 # Convert a datetime object to an Excel serial date and time.
4851 return datetime_to_excel_datetime(dt_obj, self.date_1904, self.remove_timezone)
4853 def _convert_name_area(self, row_num_1, col_num_1, row_num_2, col_num_2):
4854 # Convert zero indexed rows and columns to the format required by
4855 # worksheet named ranges, eg, "Sheet1!$A$1:$C$13".
4857 range1 = ""
4858 range2 = ""
4859 area = ""
4860 row_col_only = 0
4862 # Convert to A1 notation.
4863 col_char_1 = xl_col_to_name(col_num_1, 1)
4864 col_char_2 = xl_col_to_name(col_num_2, 1)
4865 row_char_1 = "$" + str(row_num_1 + 1)
4866 row_char_2 = "$" + str(row_num_2 + 1)
4868 # We need to handle special cases that refer to rows or columns only.
4869 if row_num_1 == 0 and row_num_2 == self.xls_rowmax - 1:
4870 range1 = col_char_1
4871 range2 = col_char_2
4872 row_col_only = 1
4873 elif col_num_1 == 0 and col_num_2 == self.xls_colmax - 1:
4874 range1 = row_char_1
4875 range2 = row_char_2
4876 row_col_only = 1
4877 else:
4878 range1 = col_char_1 + row_char_1
4879 range2 = col_char_2 + row_char_2
4881 # A repeated range is only written once (if it isn't a special case).
4882 if range1 == range2 and not row_col_only:
4883 area = range1
4884 else:
4885 area = range1 + ":" + range2
4887 # Build up the print area range "Sheet1!$A$1:$C$13".
4888 sheetname = quote_sheetname(self.name)
4889 area = sheetname + "!" + area
4891 return area
4893 def _sort_pagebreaks(self, breaks):
4894 # This is an internal method used to filter elements of a list of
4895 # pagebreaks used in the _store_hbreak() and _store_vbreak() methods.
4896 # It:
4897 # 1. Removes duplicate entries from the list.
4898 # 2. Sorts the list.
4899 # 3. Removes 0 from the list if present.
4900 if not breaks:
4901 return
4903 breaks_set = set(breaks)
4905 if 0 in breaks_set:
4906 breaks_set.remove(0)
4908 breaks_list = list(breaks_set)
4909 breaks_list.sort()
4911 # The Excel 2007 specification says that the maximum number of page
4912 # breaks is 1026. However, in practice it is actually 1023.
4913 max_num_breaks = 1023
4914 if len(breaks_list) > max_num_breaks:
4915 breaks_list = breaks_list[:max_num_breaks]
4917 return breaks_list
4919 def _extract_filter_tokens(self, expression):
4920 # Extract the tokens from the filter expression. The tokens are mainly
4921 # non-whitespace groups. The only tricky part is to extract string
4922 # tokens that contain whitespace and/or quoted double quotes (Excel's
4923 # escaped quotes).
4924 #
4925 # Examples: 'x < 2000'
4926 # 'x > 2000 and x < 5000'
4927 # 'x = "foo"'
4928 # 'x = "foo bar"'
4929 # 'x = "foo "" bar"'
4930 #
4931 if not expression:
4932 return []
4934 token_re = re.compile(r'"(?:[^"]|"")*"|\S+')
4935 tokens = token_re.findall(expression)
4937 new_tokens = []
4938 # Remove single leading and trailing quotes and un-escape other quotes.
4939 for token in tokens:
4940 if token.startswith('"'):
4941 token = token[1:]
4943 if token.endswith('"'):
4944 token = token[:-1]
4946 token = token.replace('""', '"')
4948 new_tokens.append(token)
4950 return new_tokens
4952 def _parse_filter_expression(self, expression, tokens):
4953 # Converts the tokens of a possibly conditional expression into 1 or 2
4954 # sub expressions for further parsing.
4955 #
4956 # Examples:
4957 # ('x', '==', 2000) -> exp1
4958 # ('x', '>', 2000, 'and', 'x', '<', 5000) -> exp1 and exp2
4960 if len(tokens) == 7:
4961 # The number of tokens will be either 3 (for 1 expression)
4962 # or 7 (for 2 expressions).
4963 conditional = tokens[3]
4965 if re.match("(and|&&)", conditional):
4966 conditional = 0
4967 elif re.match(r"(or|\|\|)", conditional):
4968 conditional = 1
4969 else:
4970 warn(
4971 "Token '%s' is not a valid conditional "
4972 "in filter expression '%s'" % (conditional, expression)
4973 )
4975 expression_1 = self._parse_filter_tokens(expression, tokens[0:3])
4976 expression_2 = self._parse_filter_tokens(expression, tokens[4:7])
4978 return expression_1 + [conditional] + expression_2
4979 else:
4980 return self._parse_filter_tokens(expression, tokens)
4982 def _parse_filter_tokens(self, expression, tokens):
4983 # Parse the 3 tokens of a filter expression and return the operator
4984 # and token. The use of numbers instead of operators is a legacy of
4985 # Spreadsheet::WriteExcel.
4986 operators = {
4987 "==": 2,
4988 "=": 2,
4989 "=~": 2,
4990 "eq": 2,
4991 "!=": 5,
4992 "!~": 5,
4993 "ne": 5,
4994 "<>": 5,
4995 "<": 1,
4996 "<=": 3,
4997 ">": 4,
4998 ">=": 6,
4999 }
5001 operator = operators.get(tokens[1], None)
5002 token = tokens[2]
5004 # Special handling of "Top" filter expressions.
5005 if re.match("top|bottom", tokens[0].lower()):
5006 value = int(tokens[1])
5008 if value < 1 or value > 500:
5009 warn(
5010 "The value '%d' in expression '%s' "
5011 "must be in the range 1 to 500" % (value, expression)
5012 )
5014 token = token.lower()
5016 if token != "items" and token != "%":
5017 warn(
5018 "The type '%s' in expression '%s' "
5019 "must be either 'items' or '%%'" % (token, expression)
5020 )
5022 if tokens[0].lower() == "top":
5023 operator = 30
5024 else:
5025 operator = 32
5027 if tokens[2] == "%":
5028 operator += 1
5030 token = str(value)
5032 if not operator and tokens[0]:
5033 warn(
5034 "Token '%s' is not a valid operator "
5035 "in filter expression '%s'" % (token[0], expression)
5036 )
5038 # Special handling for Blanks/NonBlanks.
5039 if re.match("blanks|nonblanks", token.lower()):
5040 # Only allow Equals or NotEqual in this context.
5041 if operator != 2 and operator != 5:
5042 warn(
5043 "The operator '%s' in expression '%s' "
5044 "is not valid in relation to Blanks/NonBlanks'"
5045 % (tokens[1], expression)
5046 )
5048 token = token.lower()
5050 # The operator should always be 2 (=) to flag a "simple" equality
5051 # in the binary record. Therefore we convert <> to =.
5052 if token == "blanks":
5053 if operator == 5:
5054 token = " "
5055 else:
5056 if operator == 5:
5057 operator = 2
5058 token = "blanks"
5059 else:
5060 operator = 5
5061 token = " "
5063 # if the string token contains an Excel match character then change the
5064 # operator type to indicate a non "simple" equality.
5065 if operator == 2 and re.search("[*?]", token):
5066 operator = 22
5068 return [operator, token]
5070 def _encode_password(self, password):
5071 # Hash a worksheet password. Based on the algorithm in
5072 # ECMA-376-4:2016, Office Open XML File Formats — Transitional
5073 # Migration Features, Additional attributes for workbookProtection
5074 # element (Part 1, §18.2.29).
5075 hash = 0x0000
5077 for char in password[::-1]:
5078 hash = ((hash >> 14) & 0x01) | ((hash << 1) & 0x7FFF)
5079 hash ^= ord(char)
5081 hash = ((hash >> 14) & 0x01) | ((hash << 1) & 0x7FFF)
5082 hash ^= len(password)
5083 hash ^= 0xCE4B
5085 return "%X" % hash
5087 def _prepare_image(
5088 self,
5089 index,
5090 image_id,
5091 drawing_id,
5092 width,
5093 height,
5094 name,
5095 image_type,
5096 x_dpi,
5097 y_dpi,
5098 digest,
5099 ):
5100 # Set up images/drawings.
5101 drawing_type = 2
5102 (
5103 row,
5104 col,
5105 _,
5106 x_offset,
5107 y_offset,
5108 x_scale,
5109 y_scale,
5110 url,
5111 tip,
5112 anchor,
5113 _,
5114 description,
5115 decorative,
5116 ) = self.images[index]
5118 width *= x_scale
5119 height *= y_scale
5121 # Scale by non 96dpi resolutions.
5122 width *= 96.0 / x_dpi
5123 height *= 96.0 / y_dpi
5125 dimensions = self._position_object_emus(
5126 col, row, x_offset, y_offset, width, height, anchor
5127 )
5128 # Convert from pixels to emus.
5129 width = int(0.5 + (width * 9525))
5130 height = int(0.5 + (height * 9525))
5132 # Create a Drawing obj to use with worksheet unless one already exists.
5133 if not self.drawing:
5134 drawing = Drawing()
5135 drawing.embedded = 1
5136 self.drawing = drawing
5138 self.external_drawing_links.append(
5139 ["/drawing", "../drawings/drawing" + str(drawing_id) + ".xml", None]
5140 )
5141 else:
5142 drawing = self.drawing
5144 drawing_object = drawing._add_drawing_object()
5145 drawing_object["type"] = drawing_type
5146 drawing_object["dimensions"] = dimensions
5147 drawing_object["width"] = width
5148 drawing_object["height"] = height
5149 drawing_object["description"] = name
5150 drawing_object["shape"] = None
5151 drawing_object["anchor"] = anchor
5152 drawing_object["rel_index"] = 0
5153 drawing_object["url_rel_index"] = 0
5154 drawing_object["tip"] = tip
5155 drawing_object["decorative"] = decorative
5157 if description is not None:
5158 drawing_object["description"] = description
5160 if url:
5161 target = None
5162 rel_type = "/hyperlink"
5163 target_mode = "External"
5165 if re.match("(ftp|http)s?://", url):
5166 target = self._escape_url(url)
5168 if re.match("^mailto:", url):
5169 target = self._escape_url(url)
5171 if re.match("external:", url):
5172 target = url.replace("external:", "")
5173 target = self._escape_url(target)
5174 # Additional escape not required in worksheet hyperlinks.
5175 target = target.replace("#", "%23")
5177 if re.match(r"\w:", target) or re.match(r"\\", target):
5178 target = "file:///" + target
5179 else:
5180 target = target.replace("\\", "/")
5182 if re.match("internal:", url):
5183 target = url.replace("internal:", "#")
5184 target_mode = None
5186 if target is not None:
5187 if len(target) > self.max_url_length:
5188 warn(
5189 "Ignoring URL '%s' with link and/or anchor > %d "
5190 "characters since it exceeds Excel's limit for URLS"
5191 % (url, self.max_url_length)
5192 )
5193 else:
5194 if not self.drawing_rels.get(url):
5195 self.drawing_links.append([rel_type, target, target_mode])
5197 drawing_object["url_rel_index"] = self._get_drawing_rel_index(url)
5199 if not self.drawing_rels.get(digest):
5200 self.drawing_links.append(
5201 ["/image", "../media/image" + str(image_id) + "." + image_type]
5202 )
5204 drawing_object["rel_index"] = self._get_drawing_rel_index(digest)
5206 def _prepare_shape(self, index, drawing_id):
5207 # Set up shapes/drawings.
5208 drawing_type = 3
5210 (
5211 row,
5212 col,
5213 x_offset,
5214 y_offset,
5215 x_scale,
5216 y_scale,
5217 text,
5218 anchor,
5219 options,
5220 description,
5221 decorative,
5222 ) = self.shapes[index]
5224 width = options.get("width", self.default_col_pixels * 3)
5225 height = options.get("height", self.default_row_pixels * 6)
5227 width *= x_scale
5228 height *= y_scale
5230 dimensions = self._position_object_emus(
5231 col, row, x_offset, y_offset, width, height, anchor
5232 )
5234 # Convert from pixels to emus.
5235 width = int(0.5 + (width * 9525))
5236 height = int(0.5 + (height * 9525))
5238 # Create a Drawing obj to use with worksheet unless one already exists.
5239 if not self.drawing:
5240 drawing = Drawing()
5241 drawing.embedded = 1
5242 self.drawing = drawing
5244 self.external_drawing_links.append(
5245 ["/drawing", "../drawings/drawing" + str(drawing_id) + ".xml", None]
5246 )
5247 else:
5248 drawing = self.drawing
5250 shape = Shape("rect", "TextBox", options)
5251 shape.text = text
5253 drawing_object = drawing._add_drawing_object()
5254 drawing_object["type"] = drawing_type
5255 drawing_object["dimensions"] = dimensions
5256 drawing_object["width"] = width
5257 drawing_object["height"] = height
5258 drawing_object["description"] = description
5259 drawing_object["shape"] = shape
5260 drawing_object["anchor"] = anchor
5261 drawing_object["rel_index"] = 0
5262 drawing_object["url_rel_index"] = 0
5263 drawing_object["tip"] = options.get("tip")
5264 drawing_object["decorative"] = decorative
5266 url = options.get("url", None)
5267 if url:
5268 target = None
5269 rel_type = "/hyperlink"
5270 target_mode = "External"
5272 if re.match("(ftp|http)s?://", url):
5273 target = self._escape_url(url)
5275 if re.match("^mailto:", url):
5276 target = self._escape_url(url)
5278 if re.match("external:", url):
5279 target = url.replace("external:", "file:///")
5280 target = self._escape_url(target)
5281 # Additional escape not required in worksheet hyperlinks.
5282 target = target.replace("#", "%23")
5284 if re.match("internal:", url):
5285 target = url.replace("internal:", "#")
5286 target_mode = None
5288 if target is not None:
5289 if len(target) > self.max_url_length:
5290 warn(
5291 "Ignoring URL '%s' with link and/or anchor > %d "
5292 "characters since it exceeds Excel's limit for URLS"
5293 % (url, self.max_url_length)
5294 )
5295 else:
5296 if not self.drawing_rels.get(url):
5297 self.drawing_links.append([rel_type, target, target_mode])
5299 drawing_object["url_rel_index"] = self._get_drawing_rel_index(url)
5301 def _prepare_header_image(
5302 self, image_id, width, height, name, image_type, position, x_dpi, y_dpi, digest
5303 ):
5304 # Set up an image without a drawing object for header/footer images.
5306 # Strip the extension from the filename.
5307 name = re.sub(r"\..*$", "", name)
5309 if not self.vml_drawing_rels.get(digest):
5310 self.vml_drawing_links.append(
5311 ["/image", "../media/image" + str(image_id) + "." + image_type]
5312 )
5314 ref_id = self._get_vml_drawing_rel_index(digest)
5316 self.header_images_list.append(
5317 [width, height, name, position, x_dpi, y_dpi, ref_id]
5318 )
5320 def _prepare_background(self, image_id, image_type):
5321 # Set up an image without a drawing object for backgrounds.
5322 self.external_background_links.append(
5323 ["/image", "../media/image" + str(image_id) + "." + image_type]
5324 )
5326 def _prepare_chart(self, index, chart_id, drawing_id):
5327 # Set up chart/drawings.
5328 drawing_type = 1
5330 (
5331 row,
5332 col,
5333 chart,
5334 x_offset,
5335 y_offset,
5336 x_scale,
5337 y_scale,
5338 anchor,
5339 description,
5340 decorative,
5341 ) = self.charts[index]
5343 chart.id = chart_id - 1
5345 # Use user specified dimensions, if any.
5346 width = int(0.5 + (chart.width * x_scale))
5347 height = int(0.5 + (chart.height * y_scale))
5349 dimensions = self._position_object_emus(
5350 col, row, x_offset, y_offset, width, height, anchor
5351 )
5353 # Set the chart name for the embedded object if it has been specified.
5354 name = chart.chart_name
5356 # Create a Drawing obj to use with worksheet unless one already exists.
5357 if not self.drawing:
5358 drawing = Drawing()
5359 drawing.embedded = 1
5360 self.drawing = drawing
5362 self.external_drawing_links.append(
5363 ["/drawing", "../drawings/drawing" + str(drawing_id) + ".xml"]
5364 )
5365 else:
5366 drawing = self.drawing
5368 drawing_object = drawing._add_drawing_object()
5369 drawing_object["type"] = drawing_type
5370 drawing_object["dimensions"] = dimensions
5371 drawing_object["width"] = width
5372 drawing_object["height"] = height
5373 drawing_object["name"] = name
5374 drawing_object["shape"] = None
5375 drawing_object["anchor"] = anchor
5376 drawing_object["rel_index"] = self._get_drawing_rel_index()
5377 drawing_object["url_rel_index"] = 0
5378 drawing_object["tip"] = None
5379 drawing_object["description"] = description
5380 drawing_object["decorative"] = decorative
5382 self.drawing_links.append(
5383 ["/chart", "../charts/chart" + str(chart_id) + ".xml"]
5384 )
5386 def _position_object_emus(
5387 self, col_start, row_start, x1, y1, width, height, anchor
5388 ):
5389 # Calculate the vertices that define the position of a graphical
5390 # object within the worksheet in EMUs.
5391 #
5392 # The vertices are expressed as English Metric Units (EMUs). There are
5393 # 12,700 EMUs per point. Therefore, 12,700 * 3 /4 = 9,525 EMUs per
5394 # pixel
5395 (
5396 col_start,
5397 row_start,
5398 x1,
5399 y1,
5400 col_end,
5401 row_end,
5402 x2,
5403 y2,
5404 x_abs,
5405 y_abs,
5406 ) = self._position_object_pixels(
5407 col_start, row_start, x1, y1, width, height, anchor
5408 )
5410 # Convert the pixel values to EMUs. See above.
5411 x1 = int(0.5 + 9525 * x1)
5412 y1 = int(0.5 + 9525 * y1)
5413 x2 = int(0.5 + 9525 * x2)
5414 y2 = int(0.5 + 9525 * y2)
5415 x_abs = int(0.5 + 9525 * x_abs)
5416 y_abs = int(0.5 + 9525 * y_abs)
5418 return (col_start, row_start, x1, y1, col_end, row_end, x2, y2, x_abs, y_abs)
5420 # Calculate the vertices that define the position of a graphical object
5421 # within the worksheet in pixels.
5422 #
5423 # +------------+------------+
5424 # | A | B |
5425 # +-----+------------+------------+
5426 # | |(x1,y1) | |
5427 # | 1 |(A1)._______|______ |
5428 # | | | | |
5429 # | | | | |
5430 # +-----+----| OBJECT |-----+
5431 # | | | | |
5432 # | 2 | |______________. |
5433 # | | | (B2)|
5434 # | | | (x2,y2)|
5435 # +---- +------------+------------+
5436 #
5437 # Example of an object that covers some of the area from cell A1 to B2.
5438 #
5439 # Based on the width and height of the object we need to calculate 8 vars:
5440 #
5441 # col_start, row_start, col_end, row_end, x1, y1, x2, y2.
5442 #
5443 # We also calculate the absolute x and y position of the top left vertex of
5444 # the object. This is required for images.
5445 #
5446 # The width and height of the cells that the object occupies can be
5447 # variable and have to be taken into account.
5448 #
5449 # The values of col_start and row_start are passed in from the calling
5450 # function. The values of col_end and row_end are calculated by
5451 # subtracting the width and height of the object from the width and
5452 # height of the underlying cells.
5453 #
5454 def _position_object_pixels(
5455 self, col_start, row_start, x1, y1, width, height, anchor
5456 ):
5457 # col_start # Col containing upper left corner of object.
5458 # x1 # Distance to left side of object.
5459 #
5460 # row_start # Row containing top left corner of object.
5461 # y1 # Distance to top of object.
5462 #
5463 # col_end # Col containing lower right corner of object.
5464 # x2 # Distance to right side of object.
5465 #
5466 # row_end # Row containing bottom right corner of object.
5467 # y2 # Distance to bottom of object.
5468 #
5469 # width # Width of object frame.
5470 # height # Height of object frame.
5471 #
5472 # x_abs # Absolute distance to left side of object.
5473 # y_abs # Absolute distance to top side of object.
5474 x_abs = 0
5475 y_abs = 0
5477 # Adjust start column for negative offsets.
5478 while x1 < 0 and col_start > 0:
5479 x1 += self._size_col(col_start - 1)
5480 col_start -= 1
5482 # Adjust start row for negative offsets.
5483 while y1 < 0 and row_start > 0:
5484 y1 += self._size_row(row_start - 1)
5485 row_start -= 1
5487 # Ensure that the image isn't shifted off the page at top left.
5488 if x1 < 0:
5489 x1 = 0
5491 if y1 < 0:
5492 y1 = 0
5494 # Calculate the absolute x offset of the top-left vertex.
5495 if self.col_size_changed:
5496 for col_id in range(col_start):
5497 x_abs += self._size_col(col_id)
5498 else:
5499 # Optimization for when the column widths haven't changed.
5500 x_abs += self.default_col_pixels * col_start
5502 x_abs += x1
5504 # Calculate the absolute y offset of the top-left vertex.
5505 if self.row_size_changed:
5506 for row_id in range(row_start):
5507 y_abs += self._size_row(row_id)
5508 else:
5509 # Optimization for when the row heights haven't changed.
5510 y_abs += self.default_row_pixels * row_start
5512 y_abs += y1
5514 # Adjust start column for offsets that are greater than the col width.
5515 while x1 >= self._size_col(col_start, anchor):
5516 x1 -= self._size_col(col_start)
5517 col_start += 1
5519 # Adjust start row for offsets that are greater than the row height.
5520 while y1 >= self._size_row(row_start, anchor):
5521 y1 -= self._size_row(row_start)
5522 row_start += 1
5524 # Initialize end cell to the same as the start cell.
5525 col_end = col_start
5526 row_end = row_start
5528 # Don't offset the image in the cell if the row/col is hidden.
5529 if self._size_col(col_start, anchor) > 0:
5530 width = width + x1
5531 if self._size_row(row_start, anchor) > 0:
5532 height = height + y1
5534 # Subtract the underlying cell widths to find end cell of the object.
5535 while width >= self._size_col(col_end, anchor):
5536 width -= self._size_col(col_end, anchor)
5537 col_end += 1
5539 # Subtract the underlying cell heights to find end cell of the object.
5540 while height >= self._size_row(row_end, anchor):
5541 height -= self._size_row(row_end, anchor)
5542 row_end += 1
5544 # The end vertices are whatever is left from the width and height.
5545 x2 = width
5546 y2 = height
5548 return [col_start, row_start, x1, y1, col_end, row_end, x2, y2, x_abs, y_abs]
5550 def _size_col(self, col, anchor=0):
5551 # Convert the width of a cell from character units to pixels. Excel
5552 # rounds the column width to the nearest pixel. If the width hasn't
5553 # been set by the user we use the default value. A hidden column is
5554 # treated as having a width of zero unless it has the special
5555 # "object_position" of 4 (size with cells).
5556 max_digit_width = 7 # For Calibri 11.
5557 padding = 5
5558 pixels = 0
5560 # Look up the cell value to see if it has been changed.
5561 if col in self.col_info:
5562 width = self.col_info[col][0]
5563 hidden = self.col_info[col][2]
5565 if width is None:
5566 width = self.default_col_width
5568 # Convert to pixels.
5569 if hidden and anchor != 4:
5570 pixels = 0
5571 elif width < 1:
5572 pixels = int(width * (max_digit_width + padding) + 0.5)
5573 else:
5574 pixels = int(width * max_digit_width + 0.5) + padding
5575 else:
5576 pixels = self.default_col_pixels
5578 return pixels
5580 def _size_row(self, row, anchor=0):
5581 # Convert the height of a cell from character units to pixels. If the
5582 # height hasn't been set by the user we use the default value. A
5583 # hidden row is treated as having a height of zero unless it has the
5584 # special "object_position" of 4 (size with cells).
5585 pixels = 0
5587 # Look up the cell value to see if it has been changed
5588 if row in self.row_sizes:
5589 height = self.row_sizes[row][0]
5590 hidden = self.row_sizes[row][1]
5592 if hidden and anchor != 4:
5593 pixels = 0
5594 else:
5595 pixels = int(4.0 / 3.0 * height)
5596 else:
5597 pixels = int(4.0 / 3.0 * self.default_row_height)
5599 return pixels
5601 def _pixels_to_width(self, pixels):
5602 # Convert the width of a cell from pixels to character units.
5603 max_digit_width = 7.0 # For Calabri 11.
5604 padding = 5.0
5606 if pixels <= 12:
5607 width = pixels / (max_digit_width + padding)
5608 else:
5609 width = (pixels - padding) / max_digit_width
5611 return width
5613 def _pixels_to_height(self, pixels):
5614 # Convert the height of a cell from pixels to character units.
5615 return 0.75 * pixels
5617 def _comment_params(self, row, col, string, options):
5618 # This method handles the additional optional parameters to
5619 # write_comment() as well as calculating the comment object
5620 # position and vertices.
5621 default_width = 128
5622 default_height = 74
5623 anchor = 0
5625 params = {
5626 "author": None,
5627 "color": "#ffffe1",
5628 "start_cell": None,
5629 "start_col": None,
5630 "start_row": None,
5631 "visible": None,
5632 "width": default_width,
5633 "height": default_height,
5634 "x_offset": None,
5635 "x_scale": 1,
5636 "y_offset": None,
5637 "y_scale": 1,
5638 "font_name": "Tahoma",
5639 "font_size": 8,
5640 "font_family": 2,
5641 }
5643 # Overwrite the defaults with any user supplied values. Incorrect or
5644 # misspelled parameters are silently ignored.
5645 for key in options.keys():
5646 params[key] = options[key]
5648 # Ensure that a width and height have been set.
5649 if not params["width"]:
5650 params["width"] = default_width
5651 if not params["height"]:
5652 params["height"] = default_height
5654 # Set the comment background color.
5655 params["color"] = xl_color(params["color"]).lower()
5657 # Convert from Excel XML style color to XML html style color.
5658 params["color"] = params["color"].replace("ff", "#", 1)
5660 # Convert a cell reference to a row and column.
5661 if params["start_cell"] is not None:
5662 (start_row, start_col) = xl_cell_to_rowcol(params["start_cell"])
5663 params["start_row"] = start_row
5664 params["start_col"] = start_col
5666 # Set the default start cell and offsets for the comment. These are
5667 # generally fixed in relation to the parent cell. However there are
5668 # some edge cases for cells at the, er, edges.
5669 row_max = self.xls_rowmax
5670 col_max = self.xls_colmax
5672 if params["start_row"] is None:
5673 if row == 0:
5674 params["start_row"] = 0
5675 elif row == row_max - 3:
5676 params["start_row"] = row_max - 7
5677 elif row == row_max - 2:
5678 params["start_row"] = row_max - 6
5679 elif row == row_max - 1:
5680 params["start_row"] = row_max - 5
5681 else:
5682 params["start_row"] = row - 1
5684 if params["y_offset"] is None:
5685 if row == 0:
5686 params["y_offset"] = 2
5687 elif row == row_max - 3:
5688 params["y_offset"] = 16
5689 elif row == row_max - 2:
5690 params["y_offset"] = 16
5691 elif row == row_max - 1:
5692 params["y_offset"] = 14
5693 else:
5694 params["y_offset"] = 10
5696 if params["start_col"] is None:
5697 if col == col_max - 3:
5698 params["start_col"] = col_max - 6
5699 elif col == col_max - 2:
5700 params["start_col"] = col_max - 5
5701 elif col == col_max - 1:
5702 params["start_col"] = col_max - 4
5703 else:
5704 params["start_col"] = col + 1
5706 if params["x_offset"] is None:
5707 if col == col_max - 3:
5708 params["x_offset"] = 49
5709 elif col == col_max - 2:
5710 params["x_offset"] = 49
5711 elif col == col_max - 1:
5712 params["x_offset"] = 49
5713 else:
5714 params["x_offset"] = 15
5716 # Scale the size of the comment box if required.
5717 if params["x_scale"]:
5718 params["width"] = params["width"] * params["x_scale"]
5720 if params["y_scale"]:
5721 params["height"] = params["height"] * params["y_scale"]
5723 # Round the dimensions to the nearest pixel.
5724 params["width"] = int(0.5 + params["width"])
5725 params["height"] = int(0.5 + params["height"])
5727 # Calculate the positions of the comment object.
5728 vertices = self._position_object_pixels(
5729 params["start_col"],
5730 params["start_row"],
5731 params["x_offset"],
5732 params["y_offset"],
5733 params["width"],
5734 params["height"],
5735 anchor,
5736 )
5738 # Add the width and height for VML.
5739 vertices.append(params["width"])
5740 vertices.append(params["height"])
5742 return [
5743 row,
5744 col,
5745 string,
5746 params["author"],
5747 params["visible"],
5748 params["color"],
5749 params["font_name"],
5750 params["font_size"],
5751 params["font_family"],
5752 ] + [vertices]
5754 def _button_params(self, row, col, options):
5755 # This method handles the parameters passed to insert_button() as well
5756 # as calculating the button object position and vertices.
5758 default_height = self.default_row_pixels
5759 default_width = self.default_col_pixels
5760 anchor = 0
5762 button_number = 1 + len(self.buttons_list)
5763 button = {"row": row, "col": col, "font": {}}
5764 params = {}
5766 # Overwrite the defaults with any user supplied values. Incorrect or
5767 # misspelled parameters are silently ignored.
5768 for key in options.keys():
5769 params[key] = options[key]
5771 # Set the button caption.
5772 caption = params.get("caption")
5774 # Set a default caption if none was specified by user.
5775 if caption is None:
5776 caption = "Button %d" % button_number
5778 button["font"]["caption"] = caption
5780 # Set the macro name.
5781 if params.get("macro"):
5782 button["macro"] = "[0]!" + params["macro"]
5783 else:
5784 button["macro"] = "[0]!Button%d_Click" % button_number
5786 # Set the alt text for the button.
5787 button["description"] = params.get("description")
5789 # Ensure that a width and height have been set.
5790 params["width"] = params.get("width", default_width)
5791 params["height"] = params.get("height", default_height)
5793 # Set the x/y offsets.
5794 params["x_offset"] = params.get("x_offset", 0)
5795 params["y_offset"] = params.get("y_offset", 0)
5797 # Scale the size of the button if required.
5798 params["width"] = params["width"] * params.get("x_scale", 1)
5799 params["height"] = params["height"] * params.get("y_scale", 1)
5801 # Round the dimensions to the nearest pixel.
5802 params["width"] = int(0.5 + params["width"])
5803 params["height"] = int(0.5 + params["height"])
5805 params["start_row"] = row
5806 params["start_col"] = col
5808 # Calculate the positions of the button object.
5809 vertices = self._position_object_pixels(
5810 params["start_col"],
5811 params["start_row"],
5812 params["x_offset"],
5813 params["y_offset"],
5814 params["width"],
5815 params["height"],
5816 anchor,
5817 )
5819 # Add the width and height for VML.
5820 vertices.append(params["width"])
5821 vertices.append(params["height"])
5823 button["vertices"] = vertices
5825 return button
5827 def _prepare_vml_objects(
5828 self, vml_data_id, vml_shape_id, vml_drawing_id, comment_id
5829 ):
5830 comments = []
5831 # Sort the comments into row/column order for easier comparison
5832 # testing and set the external links for comments and buttons.
5833 row_nums = sorted(self.comments.keys())
5835 for row in row_nums:
5836 col_nums = sorted(self.comments[row].keys())
5838 for col in col_nums:
5839 user_options = self.comments[row][col]
5840 params = self._comment_params(*user_options)
5841 self.comments[row][col] = params
5843 # Set comment visibility if required and not user defined.
5844 if self.comments_visible:
5845 if self.comments[row][col][4] is None:
5846 self.comments[row][col][4] = 1
5848 # Set comment author if not already user defined.
5849 if self.comments[row][col][3] is None:
5850 self.comments[row][col][3] = self.comments_author
5852 comments.append(self.comments[row][col])
5854 self.external_vml_links.append(
5855 ["/vmlDrawing", "../drawings/vmlDrawing" + str(vml_drawing_id) + ".vml"]
5856 )
5858 if self.has_comments:
5859 self.comments_list = comments
5861 self.external_comment_links.append(
5862 ["/comments", "../comments" + str(comment_id) + ".xml"]
5863 )
5865 count = len(comments)
5866 start_data_id = vml_data_id
5868 # The VML o:idmap data id contains a comma separated range when there
5869 # is more than one 1024 block of comments, like this: data="1,2".
5870 for i in range(int(count / 1024)):
5871 vml_data_id = "%s,%d" % (vml_data_id, start_data_id + i + 1)
5873 self.vml_data_id = vml_data_id
5874 self.vml_shape_id = vml_shape_id
5876 return count
5878 def _prepare_header_vml_objects(self, vml_header_id, vml_drawing_id):
5879 # Set up external linkage for VML header/footer images.
5881 self.vml_header_id = vml_header_id
5883 self.external_vml_links.append(
5884 ["/vmlDrawing", "../drawings/vmlDrawing" + str(vml_drawing_id) + ".vml"]
5885 )
5887 def _prepare_tables(self, table_id, seen):
5888 # Set the table ids for the worksheet tables.
5889 for table in self.tables:
5890 table["id"] = table_id
5892 if table.get("name") is None:
5893 # Set a default name.
5894 table["name"] = "Table" + str(table_id)
5896 # Check for duplicate table names.
5897 name = table["name"].lower()
5899 if name in seen:
5900 raise DuplicateTableName(
5901 "Duplicate name '%s' used in worksheet.add_table()." % table["name"]
5902 )
5903 else:
5904 seen[name] = True
5906 # Store the link used for the rels file.
5907 self.external_table_links.append(
5908 ["/table", "../tables/table" + str(table_id) + ".xml"]
5909 )
5910 table_id += 1
5912 def _table_function_to_formula(self, function, col_name):
5913 # Convert a table total function to a worksheet formula.
5914 formula = ""
5916 # Escape special characters, as required by Excel.
5917 col_name = col_name.replace("'", "''")
5918 col_name = col_name.replace("#", "'#")
5919 col_name = col_name.replace("]", "']")
5920 col_name = col_name.replace("[", "'[")
5922 subtotals = {
5923 "average": 101,
5924 "countNums": 102,
5925 "count": 103,
5926 "max": 104,
5927 "min": 105,
5928 "stdDev": 107,
5929 "sum": 109,
5930 "var": 110,
5931 }
5933 if function in subtotals:
5934 func_num = subtotals[function]
5935 formula = "SUBTOTAL(%s,[%s])" % (func_num, col_name)
5936 else:
5937 warn("Unsupported function '%s' in add_table()" % function)
5939 return formula
5941 def _set_spark_color(self, sparkline, options, user_color):
5942 # Set the sparkline color.
5943 if user_color not in options:
5944 return
5946 sparkline[user_color] = {"rgb": xl_color(options[user_color])}
5948 def _get_range_data(self, row_start, col_start, row_end, col_end):
5949 # Returns a range of data from the worksheet _table to be used in
5950 # chart cached data. Strings are returned as SST ids and decoded
5951 # in the workbook. Return None for data that doesn't exist since
5952 # Excel can chart series with data missing.
5954 if self.constant_memory:
5955 return ()
5957 data = []
5959 # Iterate through the table data.
5960 for row_num in range(row_start, row_end + 1):
5961 # Store None if row doesn't exist.
5962 if row_num not in self.table:
5963 data.append(None)
5964 continue
5966 for col_num in range(col_start, col_end + 1):
5967 if col_num in self.table[row_num]:
5968 cell = self.table[row_num][col_num]
5970 cell_type = cell.__class__.__name__
5972 if cell_type in ("Number", "Datetime"):
5973 # Return a number with Excel's precision.
5974 data.append("%.16g" % cell.number)
5976 elif cell_type == "String":
5977 # Return a string from it's shared string index.
5978 index = cell.string
5979 string = self.str_table._get_shared_string(index)
5981 data.append(string)
5983 elif cell_type in ("Formula", "ArrayFormula"):
5984 # Return the formula value.
5985 value = cell.value
5987 if value is None:
5988 value = 0
5990 data.append(value)
5992 elif cell_type == "Blank":
5993 # Return a empty cell.
5994 data.append("")
5995 else:
5996 # Store None if column doesn't exist.
5997 data.append(None)
5999 return data
6001 def _csv_join(self, *items):
6002 # Create a csv string for use with data validation formulas and lists.
6004 # Convert non string types to string.
6005 items = [str(item) if not isinstance(item, str) else item for item in items]
6007 return ",".join(items)
6009 def _escape_url(self, url):
6010 # Don't escape URL if it looks already escaped.
6011 if re.search("%[0-9a-fA-F]{2}", url):
6012 return url
6014 # Can't use url.quote() here because it doesn't match Excel.
6015 url = url.replace("%", "%25")
6016 url = url.replace('"', "%22")
6017 url = url.replace(" ", "%20")
6018 url = url.replace("<", "%3c")
6019 url = url.replace(">", "%3e")
6020 url = url.replace("[", "%5b")
6021 url = url.replace("]", "%5d")
6022 url = url.replace("^", "%5e")
6023 url = url.replace("`", "%60")
6024 url = url.replace("{", "%7b")
6025 url = url.replace("}", "%7d")
6027 return url
6029 def _get_drawing_rel_index(self, target=None):
6030 # Get the index used to address a drawing rel link.
6031 if target is None:
6032 self.drawing_rels_id += 1
6033 return self.drawing_rels_id
6034 elif self.drawing_rels.get(target):
6035 return self.drawing_rels[target]
6036 else:
6037 self.drawing_rels_id += 1
6038 self.drawing_rels[target] = self.drawing_rels_id
6039 return self.drawing_rels_id
6041 def _get_vml_drawing_rel_index(self, target=None):
6042 # Get the index used to address a vml drawing rel link.
6043 if self.vml_drawing_rels.get(target):
6044 return self.vml_drawing_rels[target]
6045 else:
6046 self.vml_drawing_rels_id += 1
6047 self.vml_drawing_rels[target] = self.vml_drawing_rels_id
6048 return self.vml_drawing_rels_id
6050 ###########################################################################
6051 #
6052 # The following font methods are, more or less, duplicated from the
6053 # Styles class. Not the cleanest version of reuse but works for now.
6054 #
6055 ###########################################################################
6056 def _write_font(self, xf_format):
6057 # Write the <font> element.
6058 xml_writer = self.rstring
6060 xml_writer._xml_start_tag("rPr")
6062 # Handle the main font properties.
6063 if xf_format.bold:
6064 xml_writer._xml_empty_tag("b")
6065 if xf_format.italic:
6066 xml_writer._xml_empty_tag("i")
6067 if xf_format.font_strikeout:
6068 xml_writer._xml_empty_tag("strike")
6069 if xf_format.font_outline:
6070 xml_writer._xml_empty_tag("outline")
6071 if xf_format.font_shadow:
6072 xml_writer._xml_empty_tag("shadow")
6074 # Handle the underline variants.
6075 if xf_format.underline:
6076 self._write_underline(xf_format.underline)
6078 # Handle super/subscript.
6079 if xf_format.font_script == 1:
6080 self._write_vert_align("superscript")
6081 if xf_format.font_script == 2:
6082 self._write_vert_align("subscript")
6084 # Write the font size
6085 xml_writer._xml_empty_tag("sz", [("val", xf_format.font_size)])
6087 # Handle colors.
6088 if xf_format.theme == -1:
6089 # Ignore for excel2003_style.
6090 pass
6091 elif xf_format.theme:
6092 self._write_color("theme", xf_format.theme)
6093 elif xf_format.color_indexed:
6094 self._write_color("indexed", xf_format.color_indexed)
6095 elif xf_format.font_color:
6096 color = self._get_palette_color(xf_format.font_color)
6097 self._write_rstring_color("rgb", color)
6098 else:
6099 self._write_rstring_color("theme", 1)
6101 # Write some other font properties related to font families.
6102 xml_writer._xml_empty_tag("rFont", [("val", xf_format.font_name)])
6103 xml_writer._xml_empty_tag("family", [("val", xf_format.font_family)])
6105 if xf_format.font_name == "Calibri" and not xf_format.hyperlink:
6106 xml_writer._xml_empty_tag("scheme", [("val", xf_format.font_scheme)])
6108 xml_writer._xml_end_tag("rPr")
6110 def _write_underline(self, underline):
6111 # Write the underline font element.
6112 attributes = []
6114 # Handle the underline variants.
6115 if underline == 2:
6116 attributes = [("val", "double")]
6117 elif underline == 33:
6118 attributes = [("val", "singleAccounting")]
6119 elif underline == 34:
6120 attributes = [("val", "doubleAccounting")]
6122 self.rstring._xml_empty_tag("u", attributes)
6124 def _write_vert_align(self, val):
6125 # Write the <vertAlign> font sub-element.
6126 attributes = [("val", val)]
6128 self.rstring._xml_empty_tag("vertAlign", attributes)
6130 def _write_rstring_color(self, name, value):
6131 # Write the <color> element.
6132 attributes = [(name, value)]
6134 self.rstring._xml_empty_tag("color", attributes)
6136 def _get_palette_color(self, color):
6137 # Convert the RGB color.
6138 if color[0] == "#":
6139 color = color[1:]
6141 return "FF" + color.upper()
6143 def _opt_close(self):
6144 # Close the row data filehandle in constant_memory mode.
6145 if not self.row_data_fh_closed:
6146 self.row_data_fh.close()
6147 self.row_data_fh_closed = True
6149 def _opt_reopen(self):
6150 # Reopen the row data filehandle in constant_memory mode.
6151 if self.row_data_fh_closed:
6152 filename = self.row_data_filename
6153 self.row_data_fh = open(filename, mode="a+", encoding="utf-8")
6154 self.row_data_fh_closed = False
6155 self.fh = self.row_data_fh
6157 def _set_icon_props(self, total_icons, user_props=None):
6158 # Set the sub-properties for icons.
6159 props = []
6161 # Set the defaults.
6162 for _ in range(total_icons):
6163 props.append({"criteria": False, "value": 0, "type": "percent"})
6165 # Set the default icon values based on the number of icons.
6166 if total_icons == 3:
6167 props[0]["value"] = 67
6168 props[1]["value"] = 33
6170 if total_icons == 4:
6171 props[0]["value"] = 75
6172 props[1]["value"] = 50
6173 props[2]["value"] = 25
6175 if total_icons == 5:
6176 props[0]["value"] = 80
6177 props[1]["value"] = 60
6178 props[2]["value"] = 40
6179 props[3]["value"] = 20
6181 # Overwrite default properties with user defined properties.
6182 if user_props:
6183 # Ensure we don't set user properties for lowest icon.
6184 max_data = len(user_props)
6185 if max_data >= total_icons:
6186 max_data = total_icons - 1
6188 for i in range(max_data):
6189 # Set the user defined 'value' property.
6190 if user_props[i].get("value") is not None:
6191 props[i]["value"] = user_props[i]["value"]
6193 # Remove the formula '=' sign if it exists.
6194 tmp = props[i]["value"]
6195 if isinstance(tmp, str) and tmp.startswith("="):
6196 props[i]["value"] = tmp.lstrip("=")
6198 # Set the user defined 'type' property.
6199 if user_props[i].get("type"):
6200 valid_types = ("percent", "percentile", "number", "formula")
6202 if user_props[i]["type"] not in valid_types:
6203 warn(
6204 "Unknown icon property type '%s' for sub-"
6205 "property 'type' in conditional_format()"
6206 % user_props[i]["type"]
6207 )
6208 else:
6209 props[i]["type"] = user_props[i]["type"]
6211 if props[i]["type"] == "number":
6212 props[i]["type"] = "num"
6214 # Set the user defined 'criteria' property.
6215 criteria = user_props[i].get("criteria")
6216 if criteria and criteria == ">":
6217 props[i]["criteria"] = True
6219 return props
6221 ###########################################################################
6222 #
6223 # XML methods.
6224 #
6225 ###########################################################################
6227 def _write_worksheet(self):
6228 # Write the <worksheet> element. This is the root element.
6230 schema = "http://schemas.openxmlformats.org/"
6231 xmlns = schema + "spreadsheetml/2006/main"
6232 xmlns_r = schema + "officeDocument/2006/relationships"
6233 xmlns_mc = schema + "markup-compatibility/2006"
6234 ms_schema = "http://schemas.microsoft.com/"
6235 xmlns_x14ac = ms_schema + "office/spreadsheetml/2009/9/ac"
6237 attributes = [("xmlns", xmlns), ("xmlns:r", xmlns_r)]
6239 # Add some extra attributes for Excel 2010. Mainly for sparklines.
6240 if self.excel_version == 2010:
6241 attributes.append(("xmlns:mc", xmlns_mc))
6242 attributes.append(("xmlns:x14ac", xmlns_x14ac))
6243 attributes.append(("mc:Ignorable", "x14ac"))
6245 self._xml_start_tag("worksheet", attributes)
6247 def _write_dimension(self):
6248 # Write the <dimension> element. This specifies the range of
6249 # cells in the worksheet. As a special case, empty
6250 # spreadsheets use 'A1' as a range.
6252 if self.dim_rowmin is None and self.dim_colmin is None:
6253 # If the min dimensions are not defined then no dimensions
6254 # have been set and we use the default 'A1'.
6255 ref = "A1"
6257 elif self.dim_rowmin is None and self.dim_colmin is not None:
6258 # If the row dimensions aren't set but the column
6259 # dimensions are set then they have been changed via
6260 # set_column().
6262 if self.dim_colmin == self.dim_colmax:
6263 # The dimensions are a single cell and not a range.
6264 ref = xl_rowcol_to_cell(0, self.dim_colmin)
6265 else:
6266 # The dimensions are a cell range.
6267 cell_1 = xl_rowcol_to_cell(0, self.dim_colmin)
6268 cell_2 = xl_rowcol_to_cell(0, self.dim_colmax)
6269 ref = cell_1 + ":" + cell_2
6271 elif self.dim_rowmin == self.dim_rowmax and self.dim_colmin == self.dim_colmax:
6272 # The dimensions are a single cell and not a range.
6273 ref = xl_rowcol_to_cell(self.dim_rowmin, self.dim_colmin)
6274 else:
6275 # The dimensions are a cell range.
6276 cell_1 = xl_rowcol_to_cell(self.dim_rowmin, self.dim_colmin)
6277 cell_2 = xl_rowcol_to_cell(self.dim_rowmax, self.dim_colmax)
6278 ref = cell_1 + ":" + cell_2
6280 self._xml_empty_tag("dimension", [("ref", ref)])
6282 def _write_sheet_views(self):
6283 # Write the <sheetViews> element.
6284 self._xml_start_tag("sheetViews")
6286 # Write the sheetView element.
6287 self._write_sheet_view()
6289 self._xml_end_tag("sheetViews")
6291 def _write_sheet_view(self):
6292 # Write the <sheetViews> element.
6293 attributes = []
6295 # Hide screen gridlines if required.
6296 if not self.screen_gridlines:
6297 attributes.append(("showGridLines", 0))
6299 # Hide screen row/column headers.
6300 if self.row_col_headers:
6301 attributes.append(("showRowColHeaders", 0))
6303 # Hide zeroes in cells.
6304 if not self.show_zeros:
6305 attributes.append(("showZeros", 0))
6307 # Display worksheet right to left for Hebrew, Arabic and others.
6308 if self.is_right_to_left:
6309 attributes.append(("rightToLeft", 1))
6311 # Show that the sheet tab is selected.
6312 if self.selected:
6313 attributes.append(("tabSelected", 1))
6315 # Turn outlines off. Also required in the outlinePr element.
6316 if not self.outline_on:
6317 attributes.append(("showOutlineSymbols", 0))
6319 # Set the page view/layout mode if required.
6320 if self.page_view == 1:
6321 attributes.append(("view", "pageLayout"))
6322 elif self.page_view == 2:
6323 attributes.append(("view", "pageBreakPreview"))
6325 # Set the first visible cell.
6326 if self.top_left_cell != "":
6327 attributes.append(("topLeftCell", self.top_left_cell))
6329 # Set the zoom level.
6330 if self.zoom != 100:
6331 attributes.append(("zoomScale", self.zoom))
6333 if self.page_view == 0 and self.zoom_scale_normal:
6334 attributes.append(("zoomScaleNormal", self.zoom))
6335 if self.page_view == 1:
6336 attributes.append(("zoomScalePageLayoutView", self.zoom))
6337 if self.page_view == 2:
6338 attributes.append(("zoomScaleSheetLayoutView", self.zoom))
6340 attributes.append(("workbookViewId", 0))
6342 if self.panes or len(self.selections):
6343 self._xml_start_tag("sheetView", attributes)
6344 self._write_panes()
6345 self._write_selections()
6346 self._xml_end_tag("sheetView")
6347 else:
6348 self._xml_empty_tag("sheetView", attributes)
6350 def _write_sheet_format_pr(self):
6351 # Write the <sheetFormatPr> element.
6352 default_row_height = self.default_row_height
6353 row_level = self.outline_row_level
6354 col_level = self.outline_col_level
6356 attributes = [("defaultRowHeight", default_row_height)]
6358 if self.default_row_height != self.original_row_height:
6359 attributes.append(("customHeight", 1))
6361 if self.default_row_zeroed:
6362 attributes.append(("zeroHeight", 1))
6364 if row_level:
6365 attributes.append(("outlineLevelRow", row_level))
6366 if col_level:
6367 attributes.append(("outlineLevelCol", col_level))
6369 if self.excel_version == 2010:
6370 attributes.append(("x14ac:dyDescent", "0.25"))
6372 self._xml_empty_tag("sheetFormatPr", attributes)
6374 def _write_cols(self):
6375 # Write the <cols> element and <col> sub elements.
6377 # Exit unless some column have been formatted.
6378 if not self.col_info:
6379 return
6381 self._xml_start_tag("cols")
6383 # Use the first element of the column information structures to set
6384 # the initial/previous properties.
6385 first_col = (sorted(self.col_info.keys()))[0]
6386 last_col = first_col
6387 prev_col_options = self.col_info[first_col]
6388 del self.col_info[first_col]
6389 deleted_col = first_col
6390 deleted_col_options = prev_col_options
6392 for col in sorted(self.col_info.keys()):
6393 col_options = self.col_info[col]
6394 # Check if the column number is contiguous with the previous
6395 # column and if the properties are the same.
6396 if col == last_col + 1 and col_options == prev_col_options:
6397 last_col = col
6398 else:
6399 # If not contiguous/equal then we write out the current range
6400 # of columns and start again.
6401 self._write_col_info(first_col, last_col, prev_col_options)
6402 first_col = col
6403 last_col = first_col
6404 prev_col_options = col_options
6406 # We will exit the previous loop with one unhandled column range.
6407 self._write_col_info(first_col, last_col, prev_col_options)
6409 # Put back the deleted first column information structure.
6410 self.col_info[deleted_col] = deleted_col_options
6412 self._xml_end_tag("cols")
6414 def _write_col_info(self, col_min, col_max, col_info):
6415 # Write the <col> element.
6416 (width, cell_format, hidden, level, collapsed, autofit) = col_info
6418 custom_width = 1
6419 xf_index = 0
6421 # Get the cell_format index.
6422 if cell_format:
6423 xf_index = cell_format._get_xf_index()
6425 # Set the Excel default column width.
6426 if width is None:
6427 if not hidden:
6428 width = 8.43
6429 custom_width = 0
6430 else:
6431 width = 0
6432 elif width == 8.43:
6433 # Width is defined but same as default.
6434 custom_width = 0
6436 # Convert column width from user units to character width.
6437 if width > 0:
6438 # For Calabri 11.
6439 max_digit_width = 7
6440 padding = 5
6442 if width < 1:
6443 width = (
6444 int(
6445 (int(width * (max_digit_width + padding) + 0.5))
6446 / float(max_digit_width)
6447 * 256.0
6448 )
6449 / 256.0
6450 )
6451 else:
6452 width = (
6453 int(
6454 (int(width * max_digit_width + 0.5) + padding)
6455 / float(max_digit_width)
6456 * 256.0
6457 )
6458 / 256.0
6459 )
6461 attributes = [
6462 ("min", col_min + 1),
6463 ("max", col_max + 1),
6464 ("width", "%.16g" % width),
6465 ]
6467 if xf_index:
6468 attributes.append(("style", xf_index))
6469 if hidden:
6470 attributes.append(("hidden", "1"))
6471 if autofit:
6472 attributes.append(("bestFit", "1"))
6473 if custom_width:
6474 attributes.append(("customWidth", "1"))
6475 if level:
6476 attributes.append(("outlineLevel", level))
6477 if collapsed:
6478 attributes.append(("collapsed", "1"))
6480 self._xml_empty_tag("col", attributes)
6482 def _write_sheet_data(self):
6483 # Write the <sheetData> element.
6484 if self.dim_rowmin is None:
6485 # If the dimensions aren't defined there is no data to write.
6486 self._xml_empty_tag("sheetData")
6487 else:
6488 self._xml_start_tag("sheetData")
6489 self._write_rows()
6490 self._xml_end_tag("sheetData")
6492 def _write_optimized_sheet_data(self):
6493 # Write the <sheetData> element when constant_memory is on. In this
6494 # case we read the data stored in the temp file and rewrite it to the
6495 # XML sheet file.
6496 if self.dim_rowmin is None:
6497 # If the dimensions aren't defined then there is no data to write.
6498 self._xml_empty_tag("sheetData")
6499 else:
6500 self._xml_start_tag("sheetData")
6502 # Rewind the filehandle that was used for temp row data.
6503 buff_size = 65536
6504 self.row_data_fh.seek(0)
6505 data = self.row_data_fh.read(buff_size)
6507 while data:
6508 self.fh.write(data)
6509 data = self.row_data_fh.read(buff_size)
6511 self.row_data_fh.close()
6512 os.unlink(self.row_data_filename)
6514 self._xml_end_tag("sheetData")
6516 def _write_page_margins(self):
6517 # Write the <pageMargins> element.
6518 attributes = [
6519 ("left", self.margin_left),
6520 ("right", self.margin_right),
6521 ("top", self.margin_top),
6522 ("bottom", self.margin_bottom),
6523 ("header", self.margin_header),
6524 ("footer", self.margin_footer),
6525 ]
6527 self._xml_empty_tag("pageMargins", attributes)
6529 def _write_page_setup(self):
6530 # Write the <pageSetup> element.
6531 #
6532 # The following is an example taken from Excel.
6533 #
6534 # <pageSetup
6535 # paperSize="9"
6536 # scale="110"
6537 # fitToWidth="2"
6538 # fitToHeight="2"
6539 # pageOrder="overThenDown"
6540 # orientation="portrait"
6541 # blackAndWhite="1"
6542 # draft="1"
6543 # horizontalDpi="200"
6544 # verticalDpi="200"
6545 # r:id="rId1"
6546 # />
6547 #
6548 attributes = []
6550 # Skip this element if no page setup has changed.
6551 if not self.page_setup_changed:
6552 return
6554 # Set paper size.
6555 if self.paper_size:
6556 attributes.append(("paperSize", self.paper_size))
6558 # Set the print_scale.
6559 if self.print_scale != 100:
6560 attributes.append(("scale", self.print_scale))
6562 # Set the "Fit to page" properties.
6563 if self.fit_page and self.fit_width != 1:
6564 attributes.append(("fitToWidth", self.fit_width))
6566 if self.fit_page and self.fit_height != 1:
6567 attributes.append(("fitToHeight", self.fit_height))
6569 # Set the page print direction.
6570 if self.page_order:
6571 attributes.append(("pageOrder", "overThenDown"))
6573 # Set start page for printing.
6574 if self.page_start > 1:
6575 attributes.append(("firstPageNumber", self.page_start))
6577 # Set page orientation.
6578 if self.orientation:
6579 attributes.append(("orientation", "portrait"))
6580 else:
6581 attributes.append(("orientation", "landscape"))
6583 # Set the print in black and white option.
6584 if self.black_white:
6585 attributes.append(("blackAndWhite", "1"))
6587 # Set start page for printing.
6588 if self.page_start != 0:
6589 attributes.append(("useFirstPageNumber", "1"))
6591 # Set the DPI. Mainly only for testing.
6592 if self.is_chartsheet:
6593 if self.horizontal_dpi:
6594 attributes.append(("horizontalDpi", self.horizontal_dpi))
6596 if self.vertical_dpi:
6597 attributes.append(("verticalDpi", self.vertical_dpi))
6598 else:
6599 if self.vertical_dpi:
6600 attributes.append(("verticalDpi", self.vertical_dpi))
6602 if self.horizontal_dpi:
6603 attributes.append(("horizontalDpi", self.horizontal_dpi))
6605 self._xml_empty_tag("pageSetup", attributes)
6607 def _write_print_options(self):
6608 # Write the <printOptions> element.
6609 attributes = []
6611 if not self.print_options_changed:
6612 return
6614 # Set horizontal centering.
6615 if self.hcenter:
6616 attributes.append(("horizontalCentered", 1))
6618 # Set vertical centering.
6619 if self.vcenter:
6620 attributes.append(("verticalCentered", 1))
6622 # Enable row and column headers.
6623 if self.print_headers:
6624 attributes.append(("headings", 1))
6626 # Set printed gridlines.
6627 if self.print_gridlines:
6628 attributes.append(("gridLines", 1))
6630 self._xml_empty_tag("printOptions", attributes)
6632 def _write_header_footer(self):
6633 # Write the <headerFooter> element.
6634 attributes = []
6636 if not self.header_footer_scales:
6637 attributes.append(("scaleWithDoc", 0))
6639 if not self.header_footer_aligns:
6640 attributes.append(("alignWithMargins", 0))
6642 if self.header_footer_changed:
6643 self._xml_start_tag("headerFooter", attributes)
6644 if self.header:
6645 self._write_odd_header()
6646 if self.footer:
6647 self._write_odd_footer()
6648 self._xml_end_tag("headerFooter")
6649 elif self.excel2003_style:
6650 self._xml_empty_tag("headerFooter", attributes)
6652 def _write_odd_header(self):
6653 # Write the <headerFooter> element.
6654 self._xml_data_element("oddHeader", self.header)
6656 def _write_odd_footer(self):
6657 # Write the <headerFooter> element.
6658 self._xml_data_element("oddFooter", self.footer)
6660 def _write_rows(self):
6661 # Write out the worksheet data as a series of rows and cells.
6662 self._calculate_spans()
6664 for row_num in range(self.dim_rowmin, self.dim_rowmax + 1):
6665 if (
6666 row_num in self.set_rows
6667 or row_num in self.comments
6668 or self.table[row_num]
6669 ):
6670 # Only process rows with formatting, cell data and/or comments.
6672 span_index = int(row_num / 16)
6674 if span_index in self.row_spans:
6675 span = self.row_spans[span_index]
6676 else:
6677 span = None
6679 if self.table[row_num]:
6680 # Write the cells if the row contains data.
6681 if row_num not in self.set_rows:
6682 self._write_row(row_num, span)
6683 else:
6684 self._write_row(row_num, span, self.set_rows[row_num])
6686 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6687 if col_num in self.table[row_num]:
6688 col_ref = self.table[row_num][col_num]
6689 self._write_cell(row_num, col_num, col_ref)
6691 self._xml_end_tag("row")
6693 elif row_num in self.comments:
6694 # Row with comments in cells.
6695 self._write_empty_row(row_num, span, self.set_rows[row_num])
6696 else:
6697 # Blank row with attributes only.
6698 self._write_empty_row(row_num, span, self.set_rows[row_num])
6700 def _write_single_row(self, current_row_num=0):
6701 # Write out the worksheet data as a single row with cells.
6702 # This method is used when constant_memory is on. A single
6703 # row is written and the data table is reset. That way only
6704 # one row of data is kept in memory at any one time. We don't
6705 # write span data in the optimized case since it is optional.
6707 # Set the new previous row as the current row.
6708 row_num = self.previous_row
6709 self.previous_row = current_row_num
6711 if row_num in self.set_rows or row_num in self.comments or self.table[row_num]:
6712 # Only process rows with formatting, cell data and/or comments.
6714 # No span data in optimized mode.
6715 span = None
6717 if self.table[row_num]:
6718 # Write the cells if the row contains data.
6719 if row_num not in self.set_rows:
6720 self._write_row(row_num, span)
6721 else:
6722 self._write_row(row_num, span, self.set_rows[row_num])
6724 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6725 if col_num in self.table[row_num]:
6726 col_ref = self.table[row_num][col_num]
6727 self._write_cell(row_num, col_num, col_ref)
6729 self._xml_end_tag("row")
6730 else:
6731 # Row attributes or comments only.
6732 self._write_empty_row(row_num, span, self.set_rows[row_num])
6734 # Reset table.
6735 self.table.clear()
6737 def _calculate_spans(self):
6738 # Calculate the "spans" attribute of the <row> tag. This is an
6739 # XLSX optimization and isn't strictly required. However, it
6740 # makes comparing files easier. The span is the same for each
6741 # block of 16 rows.
6742 spans = {}
6743 span_min = None
6744 span_max = None
6746 for row_num in range(self.dim_rowmin, self.dim_rowmax + 1):
6747 if row_num in self.table:
6748 # Calculate spans for cell data.
6749 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6750 if col_num in self.table[row_num]:
6751 if span_min is None:
6752 span_min = col_num
6753 span_max = col_num
6754 else:
6755 if col_num < span_min:
6756 span_min = col_num
6757 if col_num > span_max:
6758 span_max = col_num
6760 if row_num in self.comments:
6761 # Calculate spans for comments.
6762 for col_num in range(self.dim_colmin, self.dim_colmax + 1):
6763 if row_num in self.comments and col_num in self.comments[row_num]:
6764 if span_min is None:
6765 span_min = col_num
6766 span_max = col_num
6767 else:
6768 if col_num < span_min:
6769 span_min = col_num
6770 if col_num > span_max:
6771 span_max = col_num
6773 if ((row_num + 1) % 16 == 0) or row_num == self.dim_rowmax:
6774 span_index = int(row_num / 16)
6776 if span_min is not None:
6777 span_min += 1
6778 span_max += 1
6779 spans[span_index] = "%s:%s" % (span_min, span_max)
6780 span_min = None
6782 self.row_spans = spans
6784 def _write_row(self, row, spans, properties=None, empty_row=False):
6785 # Write the <row> element.
6786 xf_index = 0
6788 if properties:
6789 height, cell_format, hidden, level, collapsed = properties
6790 else:
6791 height, cell_format, hidden, level, collapsed = None, None, 0, 0, 0
6793 if height is None:
6794 height = self.default_row_height
6796 attributes = [("r", row + 1)]
6798 # Get the cell_format index.
6799 if cell_format:
6800 xf_index = cell_format._get_xf_index()
6802 # Add row attributes where applicable.
6803 if spans:
6804 attributes.append(("spans", spans))
6806 if xf_index:
6807 attributes.append(("s", xf_index))
6809 if cell_format:
6810 attributes.append(("customFormat", 1))
6812 if height != self.original_row_height:
6813 attributes.append(("ht", "%g" % height))
6815 if hidden:
6816 attributes.append(("hidden", 1))
6818 if height != self.original_row_height:
6819 attributes.append(("customHeight", 1))
6821 if level:
6822 attributes.append(("outlineLevel", level))
6824 if collapsed:
6825 attributes.append(("collapsed", 1))
6827 if self.excel_version == 2010:
6828 attributes.append(("x14ac:dyDescent", "0.25"))
6830 if empty_row:
6831 self._xml_empty_tag_unencoded("row", attributes)
6832 else:
6833 self._xml_start_tag_unencoded("row", attributes)
6835 def _write_empty_row(self, row, spans, properties=None):
6836 # Write and empty <row> element.
6837 self._write_row(row, spans, properties, empty_row=True)
6839 def _write_cell(self, row, col, cell):
6840 # Write the <cell> element.
6841 # Note. This is the innermost loop so efficiency is important.
6843 cell_range = xl_rowcol_to_cell_fast(row, col)
6844 attributes = [("r", cell_range)]
6846 if cell.format:
6847 # Add the cell format index.
6848 xf_index = cell.format._get_xf_index()
6849 attributes.append(("s", xf_index))
6850 elif row in self.set_rows and self.set_rows[row][1]:
6851 # Add the row format.
6852 row_xf = self.set_rows[row][1]
6853 attributes.append(("s", row_xf._get_xf_index()))
6854 elif col in self.col_info:
6855 # Add the column format.
6856 col_xf = self.col_info[col][1]
6857 if col_xf is not None:
6858 attributes.append(("s", col_xf._get_xf_index()))
6860 type_cell_name = cell.__class__.__name__
6862 # Write the various cell types.
6863 if type_cell_name in ("Number", "Datetime"):
6864 # Write a number.
6865 self._xml_number_element(cell.number, attributes)
6867 elif type_cell_name in ("String", "RichString"):
6868 # Write a string.
6869 string = cell.string
6871 if not self.constant_memory:
6872 # Write a shared string.
6873 self._xml_string_element(string, attributes)
6874 else:
6875 # Write an optimized in-line string.
6877 # Convert control character to a _xHHHH_ escape.
6878 string = self._escape_control_characters(string)
6880 # Write any rich strings without further tags.
6881 if string.startswith("<r>") and string.endswith("</r>"):
6882 self._xml_rich_inline_string(string, attributes)
6883 else:
6884 # Add attribute to preserve leading or trailing whitespace.
6885 preserve = preserve_whitespace(string)
6886 self._xml_inline_string(string, preserve, attributes)
6888 elif type_cell_name == "Formula":
6889 # Write a formula. First check the formula value type.
6890 value = cell.value
6891 if isinstance(cell.value, bool):
6892 attributes.append(("t", "b"))
6893 if cell.value:
6894 value = 1
6895 else:
6896 value = 0
6898 elif isinstance(cell.value, str):
6899 error_codes = (
6900 "#DIV/0!",
6901 "#N/A",
6902 "#NAME?",
6903 "#NULL!",
6904 "#NUM!",
6905 "#REF!",
6906 "#VALUE!",
6907 )
6909 if cell.value == "":
6910 # Allow blank to force recalc in some third party apps.
6911 pass
6912 elif cell.value in error_codes:
6913 attributes.append(("t", "e"))
6914 else:
6915 attributes.append(("t", "str"))
6917 self._xml_formula_element(cell.formula, value, attributes)
6919 elif type_cell_name == "ArrayFormula":
6920 # Write a array formula.
6922 if cell.atype == "dynamic":
6923 attributes.append(("cm", 1))
6925 # First check if the formula value is a string.
6926 try:
6927 float(cell.value)
6928 except ValueError:
6929 attributes.append(("t", "str"))
6931 # Write an array formula.
6932 self._xml_start_tag("c", attributes)
6934 self._write_cell_array_formula(cell.formula, cell.range)
6935 self._write_cell_value(cell.value)
6936 self._xml_end_tag("c")
6938 elif type_cell_name == "Blank":
6939 # Write a empty cell.
6940 self._xml_empty_tag("c", attributes)
6942 elif type_cell_name == "Boolean":
6943 # Write a boolean cell.
6944 attributes.append(("t", "b"))
6945 self._xml_start_tag("c", attributes)
6946 self._write_cell_value(cell.boolean)
6947 self._xml_end_tag("c")
6949 elif type_cell_name == "Error":
6950 # Write a boolean cell.
6951 attributes.append(("t", "e"))
6952 attributes.append(("vm", cell.value))
6953 self._xml_start_tag("c", attributes)
6954 self._write_cell_value(cell.error)
6955 self._xml_end_tag("c")
6957 def _write_cell_value(self, value):
6958 # Write the cell value <v> element.
6959 if value is None:
6960 value = ""
6962 self._xml_data_element("v", value)
6964 def _write_cell_array_formula(self, formula, cell_range):
6965 # Write the cell array formula <f> element.
6966 attributes = [("t", "array"), ("ref", cell_range)]
6968 self._xml_data_element("f", formula, attributes)
6970 def _write_sheet_pr(self):
6971 # Write the <sheetPr> element for Sheet level properties.
6972 attributes = []
6974 if (
6975 not self.fit_page
6976 and not self.filter_on
6977 and not self.tab_color
6978 and not self.outline_changed
6979 and not self.vba_codename
6980 ):
6981 return
6983 if self.vba_codename:
6984 attributes.append(("codeName", self.vba_codename))
6986 if self.filter_on:
6987 attributes.append(("filterMode", 1))
6989 if self.fit_page or self.tab_color or self.outline_changed:
6990 self._xml_start_tag("sheetPr", attributes)
6991 self._write_tab_color()
6992 self._write_outline_pr()
6993 self._write_page_set_up_pr()
6994 self._xml_end_tag("sheetPr")
6995 else:
6996 self._xml_empty_tag("sheetPr", attributes)
6998 def _write_page_set_up_pr(self):
6999 # Write the <pageSetUpPr> element.
7000 if not self.fit_page:
7001 return
7003 attributes = [("fitToPage", 1)]
7004 self._xml_empty_tag("pageSetUpPr", attributes)
7006 def _write_tab_color(self):
7007 # Write the <tabColor> element.
7008 color = self.tab_color
7010 if not color:
7011 return
7013 attributes = [("rgb", color)]
7015 self._xml_empty_tag("tabColor", attributes)
7017 def _write_outline_pr(self):
7018 # Write the <outlinePr> element.
7019 attributes = []
7021 if not self.outline_changed:
7022 return
7024 if self.outline_style:
7025 attributes.append(("applyStyles", 1))
7026 if not self.outline_below:
7027 attributes.append(("summaryBelow", 0))
7028 if not self.outline_right:
7029 attributes.append(("summaryRight", 0))
7030 if not self.outline_on:
7031 attributes.append(("showOutlineSymbols", 0))
7033 self._xml_empty_tag("outlinePr", attributes)
7035 def _write_row_breaks(self):
7036 # Write the <rowBreaks> element.
7037 page_breaks = self._sort_pagebreaks(self.hbreaks)
7039 if not page_breaks:
7040 return
7042 count = len(page_breaks)
7044 attributes = [
7045 ("count", count),
7046 ("manualBreakCount", count),
7047 ]
7049 self._xml_start_tag("rowBreaks", attributes)
7051 for row_num in page_breaks:
7052 self._write_brk(row_num, 16383)
7054 self._xml_end_tag("rowBreaks")
7056 def _write_col_breaks(self):
7057 # Write the <colBreaks> element.
7058 page_breaks = self._sort_pagebreaks(self.vbreaks)
7060 if not page_breaks:
7061 return
7063 count = len(page_breaks)
7065 attributes = [
7066 ("count", count),
7067 ("manualBreakCount", count),
7068 ]
7070 self._xml_start_tag("colBreaks", attributes)
7072 for col_num in page_breaks:
7073 self._write_brk(col_num, 1048575)
7075 self._xml_end_tag("colBreaks")
7077 def _write_brk(self, brk_id, brk_max):
7078 # Write the <brk> element.
7079 attributes = [("id", brk_id), ("max", brk_max), ("man", 1)]
7081 self._xml_empty_tag("brk", attributes)
7083 def _write_merge_cells(self):
7084 # Write the <mergeCells> element.
7085 merged_cells = self.merge
7086 count = len(merged_cells)
7088 if not count:
7089 return
7091 attributes = [("count", count)]
7093 self._xml_start_tag("mergeCells", attributes)
7095 for merged_range in merged_cells:
7096 # Write the mergeCell element.
7097 self._write_merge_cell(merged_range)
7099 self._xml_end_tag("mergeCells")
7101 def _write_merge_cell(self, merged_range):
7102 # Write the <mergeCell> element.
7103 (row_min, col_min, row_max, col_max) = merged_range
7105 # Convert the merge dimensions to a cell range.
7106 cell_1 = xl_rowcol_to_cell(row_min, col_min)
7107 cell_2 = xl_rowcol_to_cell(row_max, col_max)
7108 ref = cell_1 + ":" + cell_2
7110 attributes = [("ref", ref)]
7112 self._xml_empty_tag("mergeCell", attributes)
7114 def _write_hyperlinks(self):
7115 # Process any stored hyperlinks in row/col order and write the
7116 # <hyperlinks> element. The attributes are different for internal
7117 # and external links.
7118 hlink_refs = []
7119 display = None
7121 # Sort the hyperlinks into row order.
7122 row_nums = sorted(self.hyperlinks.keys())
7124 # Exit if there are no hyperlinks to process.
7125 if not row_nums:
7126 return
7128 # Iterate over the rows.
7129 for row_num in row_nums:
7130 # Sort the hyperlinks into column order.
7131 col_nums = sorted(self.hyperlinks[row_num].keys())
7133 # Iterate over the columns.
7134 for col_num in col_nums:
7135 # Get the link data for this cell.
7136 link = self.hyperlinks[row_num][col_num]
7137 link_type = link["link_type"]
7139 # If the cell isn't a string then we have to add the url as
7140 # the string to display.
7141 if self.table and self.table[row_num] and self.table[row_num][col_num]:
7142 cell = self.table[row_num][col_num]
7143 if cell.__class__.__name__ != "String":
7144 display = link["url"]
7146 if link_type == 1:
7147 # External link with rel file relationship.
7148 self.rel_count += 1
7150 hlink_refs.append(
7151 [
7152 link_type,
7153 row_num,
7154 col_num,
7155 self.rel_count,
7156 link["str"],
7157 display,
7158 link["tip"],
7159 ]
7160 )
7162 # Links for use by the packager.
7163 self.external_hyper_links.append(
7164 ["/hyperlink", link["url"], "External"]
7165 )
7166 else:
7167 # Internal link with rel file relationship.
7168 hlink_refs.append(
7169 [
7170 link_type,
7171 row_num,
7172 col_num,
7173 link["url"],
7174 link["str"],
7175 link["tip"],
7176 ]
7177 )
7179 # Write the hyperlink elements.
7180 self._xml_start_tag("hyperlinks")
7182 for args in hlink_refs:
7183 link_type = args.pop(0)
7185 if link_type == 1:
7186 self._write_hyperlink_external(*args)
7187 elif link_type == 2:
7188 self._write_hyperlink_internal(*args)
7190 self._xml_end_tag("hyperlinks")
7192 def _write_hyperlink_external(
7193 self, row, col, id_num, location=None, display=None, tooltip=None
7194 ):
7195 # Write the <hyperlink> element for external links.
7196 ref = xl_rowcol_to_cell(row, col)
7197 r_id = "rId" + str(id_num)
7199 attributes = [("ref", ref), ("r:id", r_id)]
7201 if location is not None:
7202 attributes.append(("location", location))
7203 if display is not None:
7204 attributes.append(("display", display))
7205 if tooltip is not None:
7206 attributes.append(("tooltip", tooltip))
7208 self._xml_empty_tag("hyperlink", attributes)
7210 def _write_hyperlink_internal(
7211 self, row, col, location=None, display=None, tooltip=None
7212 ):
7213 # Write the <hyperlink> element for internal links.
7214 ref = xl_rowcol_to_cell(row, col)
7216 attributes = [("ref", ref), ("location", location)]
7218 if tooltip is not None:
7219 attributes.append(("tooltip", tooltip))
7220 attributes.append(("display", display))
7222 self._xml_empty_tag("hyperlink", attributes)
7224 def _write_auto_filter(self):
7225 # Write the <autoFilter> element.
7226 if not self.autofilter_ref:
7227 return
7229 attributes = [("ref", self.autofilter_ref)]
7231 if self.filter_on:
7232 # Autofilter defined active filters.
7233 self._xml_start_tag("autoFilter", attributes)
7234 self._write_autofilters()
7235 self._xml_end_tag("autoFilter")
7237 else:
7238 # Autofilter defined without active filters.
7239 self._xml_empty_tag("autoFilter", attributes)
7241 def _write_autofilters(self):
7242 # Function to iterate through the columns that form part of an
7243 # autofilter range and write the appropriate filters.
7244 (col1, col2) = self.filter_range
7246 for col in range(col1, col2 + 1):
7247 # Skip if column doesn't have an active filter.
7248 if col not in self.filter_cols:
7249 continue
7251 # Retrieve the filter tokens and write the autofilter records.
7252 tokens = self.filter_cols[col]
7253 filter_type = self.filter_type[col]
7255 # Filters are relative to first column in the autofilter.
7256 self._write_filter_column(col - col1, filter_type, tokens)
7258 def _write_filter_column(self, col_id, filter_type, filters):
7259 # Write the <filterColumn> element.
7260 attributes = [("colId", col_id)]
7262 self._xml_start_tag("filterColumn", attributes)
7264 if filter_type == 1:
7265 # Type == 1 is the new XLSX style filter.
7266 self._write_filters(filters)
7267 else:
7268 # Type == 0 is the classic "custom" filter.
7269 self._write_custom_filters(filters)
7271 self._xml_end_tag("filterColumn")
7273 def _write_filters(self, filters):
7274 # Write the <filters> element.
7275 non_blanks = [filter for filter in filters if str(filter).lower() != "blanks"]
7276 attributes = []
7278 if len(filters) != len(non_blanks):
7279 attributes = [("blank", 1)]
7281 if len(filters) == 1 and len(non_blanks) == 0:
7282 # Special case for blank cells only.
7283 self._xml_empty_tag("filters", attributes)
7284 else:
7285 # General case.
7286 self._xml_start_tag("filters", attributes)
7288 for autofilter in sorted(non_blanks):
7289 self._write_filter(autofilter)
7291 self._xml_end_tag("filters")
7293 def _write_filter(self, val):
7294 # Write the <filter> element.
7295 attributes = [("val", val)]
7297 self._xml_empty_tag("filter", attributes)
7299 def _write_custom_filters(self, tokens):
7300 # Write the <customFilters> element.
7301 if len(tokens) == 2:
7302 # One filter expression only.
7303 self._xml_start_tag("customFilters")
7304 self._write_custom_filter(*tokens)
7305 self._xml_end_tag("customFilters")
7306 else:
7307 # Two filter expressions.
7308 attributes = []
7310 # Check if the "join" operand is "and" or "or".
7311 if tokens[2] == 0:
7312 attributes = [("and", 1)]
7313 else:
7314 attributes = [("and", 0)]
7316 # Write the two custom filters.
7317 self._xml_start_tag("customFilters", attributes)
7318 self._write_custom_filter(tokens[0], tokens[1])
7319 self._write_custom_filter(tokens[3], tokens[4])
7320 self._xml_end_tag("customFilters")
7322 def _write_custom_filter(self, operator, val):
7323 # Write the <customFilter> element.
7324 attributes = []
7326 operators = {
7327 1: "lessThan",
7328 2: "equal",
7329 3: "lessThanOrEqual",
7330 4: "greaterThan",
7331 5: "notEqual",
7332 6: "greaterThanOrEqual",
7333 22: "equal",
7334 }
7336 # Convert the operator from a number to a descriptive string.
7337 if operators[operator] is not None:
7338 operator = operators[operator]
7339 else:
7340 warn("Unknown operator = %s" % operator)
7342 # The 'equal' operator is the default attribute and isn't stored.
7343 if operator != "equal":
7344 attributes.append(("operator", operator))
7345 attributes.append(("val", val))
7347 self._xml_empty_tag("customFilter", attributes)
7349 def _write_sheet_protection(self):
7350 # Write the <sheetProtection> element.
7351 attributes = []
7353 if not self.protect_options:
7354 return
7356 options = self.protect_options
7358 if options["password"]:
7359 attributes.append(("password", options["password"]))
7360 if options["sheet"]:
7361 attributes.append(("sheet", 1))
7362 if options["content"]:
7363 attributes.append(("content", 1))
7364 if not options["objects"]:
7365 attributes.append(("objects", 1))
7366 if not options["scenarios"]:
7367 attributes.append(("scenarios", 1))
7368 if options["format_cells"]:
7369 attributes.append(("formatCells", 0))
7370 if options["format_columns"]:
7371 attributes.append(("formatColumns", 0))
7372 if options["format_rows"]:
7373 attributes.append(("formatRows", 0))
7374 if options["insert_columns"]:
7375 attributes.append(("insertColumns", 0))
7376 if options["insert_rows"]:
7377 attributes.append(("insertRows", 0))
7378 if options["insert_hyperlinks"]:
7379 attributes.append(("insertHyperlinks", 0))
7380 if options["delete_columns"]:
7381 attributes.append(("deleteColumns", 0))
7382 if options["delete_rows"]:
7383 attributes.append(("deleteRows", 0))
7384 if not options["select_locked_cells"]:
7385 attributes.append(("selectLockedCells", 1))
7386 if options["sort"]:
7387 attributes.append(("sort", 0))
7388 if options["autofilter"]:
7389 attributes.append(("autoFilter", 0))
7390 if options["pivot_tables"]:
7391 attributes.append(("pivotTables", 0))
7392 if not options["select_unlocked_cells"]:
7393 attributes.append(("selectUnlockedCells", 1))
7395 self._xml_empty_tag("sheetProtection", attributes)
7397 def _write_protected_ranges(self):
7398 # Write the <protectedRanges> element.
7399 if self.num_protected_ranges == 0:
7400 return
7402 self._xml_start_tag("protectedRanges")
7404 for cell_range, range_name, password in self.protected_ranges:
7405 self._write_protected_range(cell_range, range_name, password)
7407 self._xml_end_tag("protectedRanges")
7409 def _write_protected_range(self, cell_range, range_name, password):
7410 # Write the <protectedRange> element.
7411 attributes = []
7413 if password:
7414 attributes.append(("password", password))
7416 attributes.append(("sqref", cell_range))
7417 attributes.append(("name", range_name))
7419 self._xml_empty_tag("protectedRange", attributes)
7421 def _write_drawings(self):
7422 # Write the <drawing> elements.
7423 if not self.drawing:
7424 return
7426 self.rel_count += 1
7427 self._write_drawing(self.rel_count)
7429 def _write_drawing(self, drawing_id):
7430 # Write the <drawing> element.
7431 r_id = "rId" + str(drawing_id)
7433 attributes = [("r:id", r_id)]
7435 self._xml_empty_tag("drawing", attributes)
7437 def _write_legacy_drawing(self):
7438 # Write the <legacyDrawing> element.
7439 if not self.has_vml:
7440 return
7442 # Increment the relationship id for any drawings or comments.
7443 self.rel_count += 1
7444 r_id = "rId" + str(self.rel_count)
7446 attributes = [("r:id", r_id)]
7448 self._xml_empty_tag("legacyDrawing", attributes)
7450 def _write_legacy_drawing_hf(self):
7451 # Write the <legacyDrawingHF> element.
7452 if not self.has_header_vml:
7453 return
7455 # Increment the relationship id for any drawings or comments.
7456 self.rel_count += 1
7457 r_id = "rId" + str(self.rel_count)
7459 attributes = [("r:id", r_id)]
7461 self._xml_empty_tag("legacyDrawingHF", attributes)
7463 def _write_picture(self):
7464 # Write the <picture> element.
7465 if not self.background_image:
7466 return
7468 # Increment the relationship id.
7469 self.rel_count += 1
7470 r_id = "rId" + str(self.rel_count)
7472 attributes = [("r:id", r_id)]
7474 self._xml_empty_tag("picture", attributes)
7476 def _write_data_validations(self):
7477 # Write the <dataValidations> element.
7478 validations = self.validations
7479 count = len(validations)
7481 if not count:
7482 return
7484 attributes = [("count", count)]
7486 self._xml_start_tag("dataValidations", attributes)
7488 for validation in validations:
7489 # Write the dataValidation element.
7490 self._write_data_validation(validation)
7492 self._xml_end_tag("dataValidations")
7494 def _write_data_validation(self, options):
7495 # Write the <dataValidation> element.
7496 sqref = ""
7497 attributes = []
7499 # Set the cell range(s) for the data validation.
7500 for cells in options["cells"]:
7501 # Add a space between multiple cell ranges.
7502 if sqref != "":
7503 sqref += " "
7505 (row_first, col_first, row_last, col_last) = cells
7507 # Swap last row/col for first row/col as necessary
7508 if row_first > row_last:
7509 (row_first, row_last) = (row_last, row_first)
7511 if col_first > col_last:
7512 (col_first, col_last) = (col_last, col_first)
7514 sqref += xl_range(row_first, col_first, row_last, col_last)
7516 if options.get("multi_range"):
7517 sqref = options["multi_range"]
7519 if options["validate"] != "none":
7520 attributes.append(("type", options["validate"]))
7522 if options["criteria"] != "between":
7523 attributes.append(("operator", options["criteria"]))
7525 if "error_type" in options:
7526 if options["error_type"] == 1:
7527 attributes.append(("errorStyle", "warning"))
7528 if options["error_type"] == 2:
7529 attributes.append(("errorStyle", "information"))
7531 if options["ignore_blank"]:
7532 attributes.append(("allowBlank", 1))
7534 if not options["dropdown"]:
7535 attributes.append(("showDropDown", 1))
7537 if options["show_input"]:
7538 attributes.append(("showInputMessage", 1))
7540 if options["show_error"]:
7541 attributes.append(("showErrorMessage", 1))
7543 if "error_title" in options:
7544 attributes.append(("errorTitle", options["error_title"]))
7546 if "error_message" in options:
7547 attributes.append(("error", options["error_message"]))
7549 if "input_title" in options:
7550 attributes.append(("promptTitle", options["input_title"]))
7552 if "input_message" in options:
7553 attributes.append(("prompt", options["input_message"]))
7555 attributes.append(("sqref", sqref))
7557 if options["validate"] == "none":
7558 self._xml_empty_tag("dataValidation", attributes)
7559 else:
7560 self._xml_start_tag("dataValidation", attributes)
7562 # Write the formula1 element.
7563 self._write_formula_1(options["value"])
7565 # Write the formula2 element.
7566 if options["maximum"] is not None:
7567 self._write_formula_2(options["maximum"])
7569 self._xml_end_tag("dataValidation")
7571 def _write_formula_1(self, formula):
7572 # Write the <formula1> element.
7574 if isinstance(formula, list):
7575 formula = self._csv_join(*formula)
7576 formula = '"%s"' % formula
7577 else:
7578 # Check if the formula is a number.
7579 try:
7580 float(formula)
7581 except ValueError:
7582 # Not a number. Remove the formula '=' sign if it exists.
7583 if formula.startswith("="):
7584 formula = formula.lstrip("=")
7586 self._xml_data_element("formula1", formula)
7588 def _write_formula_2(self, formula):
7589 # Write the <formula2> element.
7591 # Check if the formula is a number.
7592 try:
7593 float(formula)
7594 except ValueError:
7595 # Not a number. Remove the formula '=' sign if it exists.
7596 if formula.startswith("="):
7597 formula = formula.lstrip("=")
7599 self._xml_data_element("formula2", formula)
7601 def _write_conditional_formats(self):
7602 # Write the Worksheet conditional formats.
7603 ranges = sorted(self.cond_formats.keys())
7605 if not ranges:
7606 return
7608 for cond_range in ranges:
7609 self._write_conditional_formatting(
7610 cond_range, self.cond_formats[cond_range]
7611 )
7613 def _write_conditional_formatting(self, cond_range, params):
7614 # Write the <conditionalFormatting> element.
7615 attributes = [("sqref", cond_range)]
7616 self._xml_start_tag("conditionalFormatting", attributes)
7617 for param in params:
7618 # Write the cfRule element.
7619 self._write_cf_rule(param)
7620 self._xml_end_tag("conditionalFormatting")
7622 def _write_cf_rule(self, params):
7623 # Write the <cfRule> element.
7624 attributes = [("type", params["type"])]
7626 if "format" in params and params["format"] is not None:
7627 attributes.append(("dxfId", params["format"]))
7629 attributes.append(("priority", params["priority"]))
7631 if params.get("stop_if_true"):
7632 attributes.append(("stopIfTrue", 1))
7634 if params["type"] == "cellIs":
7635 attributes.append(("operator", params["criteria"]))
7637 self._xml_start_tag("cfRule", attributes)
7639 if "minimum" in params and "maximum" in params:
7640 self._write_formula_element(params["minimum"])
7641 self._write_formula_element(params["maximum"])
7642 else:
7643 self._write_formula_element(params["value"])
7645 self._xml_end_tag("cfRule")
7647 elif params["type"] == "aboveAverage":
7648 if re.search("below", params["criteria"]):
7649 attributes.append(("aboveAverage", 0))
7651 if re.search("equal", params["criteria"]):
7652 attributes.append(("equalAverage", 1))
7654 if re.search("[123] std dev", params["criteria"]):
7655 match = re.search("([123]) std dev", params["criteria"])
7656 attributes.append(("stdDev", match.group(1)))
7658 self._xml_empty_tag("cfRule", attributes)
7660 elif params["type"] == "top10":
7661 if "criteria" in params and params["criteria"] == "%":
7662 attributes.append(("percent", 1))
7664 if "direction" in params:
7665 attributes.append(("bottom", 1))
7667 rank = params["value"] or 10
7668 attributes.append(("rank", rank))
7670 self._xml_empty_tag("cfRule", attributes)
7672 elif params["type"] == "duplicateValues":
7673 self._xml_empty_tag("cfRule", attributes)
7675 elif params["type"] == "uniqueValues":
7676 self._xml_empty_tag("cfRule", attributes)
7678 elif (
7679 params["type"] == "containsText"
7680 or params["type"] == "notContainsText"
7681 or params["type"] == "beginsWith"
7682 or params["type"] == "endsWith"
7683 ):
7684 attributes.append(("operator", params["criteria"]))
7685 attributes.append(("text", params["value"]))
7686 self._xml_start_tag("cfRule", attributes)
7687 self._write_formula_element(params["formula"])
7688 self._xml_end_tag("cfRule")
7690 elif params["type"] == "timePeriod":
7691 attributes.append(("timePeriod", params["criteria"]))
7692 self._xml_start_tag("cfRule", attributes)
7693 self._write_formula_element(params["formula"])
7694 self._xml_end_tag("cfRule")
7696 elif (
7697 params["type"] == "containsBlanks"
7698 or params["type"] == "notContainsBlanks"
7699 or params["type"] == "containsErrors"
7700 or params["type"] == "notContainsErrors"
7701 ):
7702 self._xml_start_tag("cfRule", attributes)
7703 self._write_formula_element(params["formula"])
7704 self._xml_end_tag("cfRule")
7706 elif params["type"] == "colorScale":
7707 self._xml_start_tag("cfRule", attributes)
7708 self._write_color_scale(params)
7709 self._xml_end_tag("cfRule")
7711 elif params["type"] == "dataBar":
7712 self._xml_start_tag("cfRule", attributes)
7713 self._write_data_bar(params)
7715 if params.get("is_data_bar_2010"):
7716 self._write_data_bar_ext(params)
7718 self._xml_end_tag("cfRule")
7720 elif params["type"] == "expression":
7721 self._xml_start_tag("cfRule", attributes)
7722 self._write_formula_element(params["criteria"])
7723 self._xml_end_tag("cfRule")
7725 elif params["type"] == "iconSet":
7726 self._xml_start_tag("cfRule", attributes)
7727 self._write_icon_set(params)
7728 self._xml_end_tag("cfRule")
7730 def _write_formula_element(self, formula):
7731 # Write the <formula> element.
7733 # Check if the formula is a number.
7734 try:
7735 float(formula)
7736 except ValueError:
7737 # Not a number. Remove the formula '=' sign if it exists.
7738 if formula.startswith("="):
7739 formula = formula.lstrip("=")
7741 self._xml_data_element("formula", formula)
7743 def _write_color_scale(self, param):
7744 # Write the <colorScale> element.
7746 self._xml_start_tag("colorScale")
7748 self._write_cfvo(param["min_type"], param["min_value"])
7750 if param["mid_type"] is not None:
7751 self._write_cfvo(param["mid_type"], param["mid_value"])
7753 self._write_cfvo(param["max_type"], param["max_value"])
7755 self._write_color("rgb", param["min_color"])
7757 if param["mid_color"] is not None:
7758 self._write_color("rgb", param["mid_color"])
7760 self._write_color("rgb", param["max_color"])
7762 self._xml_end_tag("colorScale")
7764 def _write_data_bar(self, param):
7765 # Write the <dataBar> element.
7766 attributes = []
7768 # Min and max bar lengths in in the spec but not supported directly by
7769 # Excel.
7770 if param.get("min_length"):
7771 attributes.append(("minLength", param["min_length"]))
7773 if param.get("max_length"):
7774 attributes.append(("maxLength", param["max_length"]))
7776 if param.get("bar_only"):
7777 attributes.append(("showValue", 0))
7779 self._xml_start_tag("dataBar", attributes)
7781 self._write_cfvo(param["min_type"], param["min_value"])
7782 self._write_cfvo(param["max_type"], param["max_value"])
7783 self._write_color("rgb", param["bar_color"])
7785 self._xml_end_tag("dataBar")
7787 def _write_data_bar_ext(self, param):
7788 # Write the <extLst> dataBar extension element.
7790 # Create a pseudo GUID for each unique Excel 2010 data bar.
7791 worksheet_count = self.index + 1
7792 data_bar_count = len(self.data_bars_2010) + 1
7793 guid = "{DA7ABA51-AAAA-BBBB-%04X-%012X}" % (worksheet_count, data_bar_count)
7795 # Store the 2010 data bar parameters to write the extLst elements.
7796 param["guid"] = guid
7797 self.data_bars_2010.append(param)
7799 self._xml_start_tag("extLst")
7800 self._write_ext("{B025F937-C7B1-47D3-B67F-A62EFF666E3E}")
7801 self._xml_data_element("x14:id", guid)
7802 self._xml_end_tag("ext")
7803 self._xml_end_tag("extLst")
7805 def _write_icon_set(self, param):
7806 # Write the <iconSet> element.
7807 attributes = []
7809 # Don't set attribute for default style.
7810 if param["icon_style"] != "3TrafficLights":
7811 attributes = [("iconSet", param["icon_style"])]
7813 if param.get("icons_only"):
7814 attributes.append(("showValue", 0))
7816 if param.get("reverse_icons"):
7817 attributes.append(("reverse", 1))
7819 self._xml_start_tag("iconSet", attributes)
7821 # Write the properties for different icon styles.
7822 for icon in reversed(param["icons"]):
7823 self._write_cfvo(icon["type"], icon["value"], icon["criteria"])
7825 self._xml_end_tag("iconSet")
7827 def _write_cfvo(self, cf_type, val, criteria=None):
7828 # Write the <cfvo> element.
7829 attributes = [("type", cf_type)]
7831 if val is not None:
7832 attributes.append(("val", val))
7834 if criteria:
7835 attributes.append(("gte", 0))
7837 self._xml_empty_tag("cfvo", attributes)
7839 def _write_color(self, name, value):
7840 # Write the <color> element.
7841 attributes = [(name, value)]
7843 self._xml_empty_tag("color", attributes)
7845 def _write_selections(self):
7846 # Write the <selection> elements.
7847 for selection in self.selections:
7848 self._write_selection(*selection)
7850 def _write_selection(self, pane, active_cell, sqref):
7851 # Write the <selection> element.
7852 attributes = []
7854 if pane:
7855 attributes.append(("pane", pane))
7857 if active_cell:
7858 attributes.append(("activeCell", active_cell))
7860 if sqref:
7861 attributes.append(("sqref", sqref))
7863 self._xml_empty_tag("selection", attributes)
7865 def _write_panes(self):
7866 # Write the frozen or split <pane> elements.
7867 panes = self.panes
7869 if not len(panes):
7870 return
7872 if panes[4] == 2:
7873 self._write_split_panes(*panes)
7874 else:
7875 self._write_freeze_panes(*panes)
7877 def _write_freeze_panes(self, row, col, top_row, left_col, pane_type):
7878 # Write the <pane> element for freeze panes.
7879 attributes = []
7881 y_split = row
7882 x_split = col
7883 top_left_cell = xl_rowcol_to_cell(top_row, left_col)
7884 active_pane = ""
7885 state = ""
7886 active_cell = ""
7887 sqref = ""
7889 # Move user cell selection to the panes.
7890 if self.selections:
7891 (_, active_cell, sqref) = self.selections[0]
7892 self.selections = []
7894 # Set the active pane.
7895 if row and col:
7896 active_pane = "bottomRight"
7898 row_cell = xl_rowcol_to_cell(row, 0)
7899 col_cell = xl_rowcol_to_cell(0, col)
7901 self.selections.append(["topRight", col_cell, col_cell])
7902 self.selections.append(["bottomLeft", row_cell, row_cell])
7903 self.selections.append(["bottomRight", active_cell, sqref])
7905 elif col:
7906 active_pane = "topRight"
7907 self.selections.append(["topRight", active_cell, sqref])
7909 else:
7910 active_pane = "bottomLeft"
7911 self.selections.append(["bottomLeft", active_cell, sqref])
7913 # Set the pane type.
7914 if pane_type == 0:
7915 state = "frozen"
7916 elif pane_type == 1:
7917 state = "frozenSplit"
7918 else:
7919 state = "split"
7921 if x_split:
7922 attributes.append(("xSplit", x_split))
7924 if y_split:
7925 attributes.append(("ySplit", y_split))
7927 attributes.append(("topLeftCell", top_left_cell))
7928 attributes.append(("activePane", active_pane))
7929 attributes.append(("state", state))
7931 self._xml_empty_tag("pane", attributes)
7933 def _write_split_panes(self, row, col, top_row, left_col, pane_type):
7934 # Write the <pane> element for split panes.
7935 attributes = []
7936 has_selection = 0
7937 active_pane = ""
7938 active_cell = ""
7939 sqref = ""
7941 y_split = row
7942 x_split = col
7944 # Move user cell selection to the panes.
7945 if self.selections:
7946 (_, active_cell, sqref) = self.selections[0]
7947 self.selections = []
7948 has_selection = 1
7950 # Convert the row and col to 1/20 twip units with padding.
7951 if y_split:
7952 y_split = int(20 * y_split + 300)
7954 if x_split:
7955 x_split = self._calculate_x_split_width(x_split)
7957 # For non-explicit topLeft definitions, estimate the cell offset based
7958 # on the pixels dimensions. This is only a workaround and doesn't take
7959 # adjusted cell dimensions into account.
7960 if top_row == row and left_col == col:
7961 top_row = int(0.5 + (y_split - 300) / 20 / 15)
7962 left_col = int(0.5 + (x_split - 390) / 20 / 3 * 4 / 64)
7964 top_left_cell = xl_rowcol_to_cell(top_row, left_col)
7966 # If there is no selection set the active cell to the top left cell.
7967 if not has_selection:
7968 active_cell = top_left_cell
7969 sqref = top_left_cell
7971 # Set the Cell selections.
7972 if row and col:
7973 active_pane = "bottomRight"
7975 row_cell = xl_rowcol_to_cell(top_row, 0)
7976 col_cell = xl_rowcol_to_cell(0, left_col)
7978 self.selections.append(["topRight", col_cell, col_cell])
7979 self.selections.append(["bottomLeft", row_cell, row_cell])
7980 self.selections.append(["bottomRight", active_cell, sqref])
7982 elif col:
7983 active_pane = "topRight"
7984 self.selections.append(["topRight", active_cell, sqref])
7986 else:
7987 active_pane = "bottomLeft"
7988 self.selections.append(["bottomLeft", active_cell, sqref])
7990 # Format splits to the same precision as Excel.
7991 if x_split:
7992 attributes.append(("xSplit", "%.16g" % x_split))
7994 if y_split:
7995 attributes.append(("ySplit", "%.16g" % y_split))
7997 attributes.append(("topLeftCell", top_left_cell))
7999 if has_selection:
8000 attributes.append(("activePane", active_pane))
8002 self._xml_empty_tag("pane", attributes)
8004 def _calculate_x_split_width(self, width):
8005 # Convert column width from user units to pane split width.
8007 max_digit_width = 7 # For Calabri 11.
8008 padding = 5
8010 # Convert to pixels.
8011 if width < 1:
8012 pixels = int(width * (max_digit_width + padding) + 0.5)
8013 else:
8014 pixels = int(width * max_digit_width + 0.5) + padding
8016 # Convert to points.
8017 points = pixels * 3 / 4
8019 # Convert to twips (twentieths of a point).
8020 twips = points * 20
8022 # Add offset/padding.
8023 width = twips + 390
8025 return width
8027 def _write_table_parts(self):
8028 # Write the <tableParts> element.
8029 tables = self.tables
8030 count = len(tables)
8032 # Return if worksheet doesn't contain any tables.
8033 if not count:
8034 return
8036 attributes = [
8037 (
8038 "count",
8039 count,
8040 )
8041 ]
8043 self._xml_start_tag("tableParts", attributes)
8045 for _ in tables:
8046 # Write the tablePart element.
8047 self.rel_count += 1
8048 self._write_table_part(self.rel_count)
8050 self._xml_end_tag("tableParts")
8052 def _write_table_part(self, r_id):
8053 # Write the <tablePart> element.
8055 r_id = "rId" + str(r_id)
8057 attributes = [
8058 (
8059 "r:id",
8060 r_id,
8061 )
8062 ]
8064 self._xml_empty_tag("tablePart", attributes)
8066 def _write_ext_list(self):
8067 # Write the <extLst> element for data bars and sparklines.
8068 has_data_bars = len(self.data_bars_2010)
8069 has_sparklines = len(self.sparklines)
8071 if not has_data_bars and not has_sparklines:
8072 return
8074 # Write the extLst element.
8075 self._xml_start_tag("extLst")
8077 if has_data_bars:
8078 self._write_ext_list_data_bars()
8080 if has_sparklines:
8081 self._write_ext_list_sparklines()
8083 self._xml_end_tag("extLst")
8085 def _write_ext_list_data_bars(self):
8086 # Write the Excel 2010 data_bar subelements.
8087 self._write_ext("{78C0D931-6437-407d-A8EE-F0AAD7539E65}")
8089 self._xml_start_tag("x14:conditionalFormattings")
8091 # Write the Excel 2010 conditional formatting data bar elements.
8092 for data_bar in self.data_bars_2010:
8093 # Write the x14:conditionalFormatting element.
8094 self._write_conditional_formatting_2010(data_bar)
8096 self._xml_end_tag("x14:conditionalFormattings")
8097 self._xml_end_tag("ext")
8099 def _write_conditional_formatting_2010(self, data_bar):
8100 # Write the <x14:conditionalFormatting> element.
8101 xmlns_xm = "http://schemas.microsoft.com/office/excel/2006/main"
8103 attributes = [("xmlns:xm", xmlns_xm)]
8105 self._xml_start_tag("x14:conditionalFormatting", attributes)
8107 # Write the x14:cfRule element.
8108 self._write_x14_cf_rule(data_bar)
8110 # Write the x14:dataBar element.
8111 self._write_x14_data_bar(data_bar)
8113 # Write the x14 max and min data bars.
8114 self._write_x14_cfvo(data_bar["x14_min_type"], data_bar["min_value"])
8115 self._write_x14_cfvo(data_bar["x14_max_type"], data_bar["max_value"])
8117 if not data_bar["bar_no_border"]:
8118 # Write the x14:borderColor element.
8119 self._write_x14_border_color(data_bar["bar_border_color"])
8121 # Write the x14:negativeFillColor element.
8122 if not data_bar["bar_negative_color_same"]:
8123 self._write_x14_negative_fill_color(data_bar["bar_negative_color"])
8125 # Write the x14:negativeBorderColor element.
8126 if (
8127 not data_bar["bar_no_border"]
8128 and not data_bar["bar_negative_border_color_same"]
8129 ):
8130 self._write_x14_negative_border_color(data_bar["bar_negative_border_color"])
8132 # Write the x14:axisColor element.
8133 if data_bar["bar_axis_position"] != "none":
8134 self._write_x14_axis_color(data_bar["bar_axis_color"])
8136 self._xml_end_tag("x14:dataBar")
8137 self._xml_end_tag("x14:cfRule")
8139 # Write the xm:sqref element.
8140 self._xml_data_element("xm:sqref", data_bar["range"])
8142 self._xml_end_tag("x14:conditionalFormatting")
8144 def _write_x14_cf_rule(self, data_bar):
8145 # Write the <x14:cfRule> element.
8146 rule_type = "dataBar"
8147 guid = data_bar["guid"]
8148 attributes = [("type", rule_type), ("id", guid)]
8150 self._xml_start_tag("x14:cfRule", attributes)
8152 def _write_x14_data_bar(self, data_bar):
8153 # Write the <x14:dataBar> element.
8154 min_length = 0
8155 max_length = 100
8157 attributes = [
8158 ("minLength", min_length),
8159 ("maxLength", max_length),
8160 ]
8162 if not data_bar["bar_no_border"]:
8163 attributes.append(("border", 1))
8165 if data_bar["bar_solid"]:
8166 attributes.append(("gradient", 0))
8168 if data_bar["bar_direction"] == "left":
8169 attributes.append(("direction", "leftToRight"))
8171 if data_bar["bar_direction"] == "right":
8172 attributes.append(("direction", "rightToLeft"))
8174 if data_bar["bar_negative_color_same"]:
8175 attributes.append(("negativeBarColorSameAsPositive", 1))
8177 if (
8178 not data_bar["bar_no_border"]
8179 and not data_bar["bar_negative_border_color_same"]
8180 ):
8181 attributes.append(("negativeBarBorderColorSameAsPositive", 0))
8183 if data_bar["bar_axis_position"] == "middle":
8184 attributes.append(("axisPosition", "middle"))
8186 if data_bar["bar_axis_position"] == "none":
8187 attributes.append(("axisPosition", "none"))
8189 self._xml_start_tag("x14:dataBar", attributes)
8191 def _write_x14_cfvo(self, rule_type, value):
8192 # Write the <x14:cfvo> element.
8193 attributes = [("type", rule_type)]
8195 if rule_type in ("min", "max", "autoMin", "autoMax"):
8196 self._xml_empty_tag("x14:cfvo", attributes)
8197 else:
8198 self._xml_start_tag("x14:cfvo", attributes)
8199 self._xml_data_element("xm:f", value)
8200 self._xml_end_tag("x14:cfvo")
8202 def _write_x14_border_color(self, rgb):
8203 # Write the <x14:borderColor> element.
8204 attributes = [("rgb", rgb)]
8205 self._xml_empty_tag("x14:borderColor", attributes)
8207 def _write_x14_negative_fill_color(self, rgb):
8208 # Write the <x14:negativeFillColor> element.
8209 attributes = [("rgb", rgb)]
8210 self._xml_empty_tag("x14:negativeFillColor", attributes)
8212 def _write_x14_negative_border_color(self, rgb):
8213 # Write the <x14:negativeBorderColor> element.
8214 attributes = [("rgb", rgb)]
8215 self._xml_empty_tag("x14:negativeBorderColor", attributes)
8217 def _write_x14_axis_color(self, rgb):
8218 # Write the <x14:axisColor> element.
8219 attributes = [("rgb", rgb)]
8220 self._xml_empty_tag("x14:axisColor", attributes)
8222 def _write_ext_list_sparklines(self):
8223 # Write the sparkline extension sub-elements.
8224 self._write_ext("{05C60535-1F16-4fd2-B633-F4F36F0B64E0}")
8226 # Write the x14:sparklineGroups element.
8227 self._write_sparkline_groups()
8229 # Write the sparkline elements.
8230 for sparkline in reversed(self.sparklines):
8231 # Write the x14:sparklineGroup element.
8232 self._write_sparkline_group(sparkline)
8234 # Write the x14:colorSeries element.
8235 self._write_color_series(sparkline["series_color"])
8237 # Write the x14:colorNegative element.
8238 self._write_color_negative(sparkline["negative_color"])
8240 # Write the x14:colorAxis element.
8241 self._write_color_axis()
8243 # Write the x14:colorMarkers element.
8244 self._write_color_markers(sparkline["markers_color"])
8246 # Write the x14:colorFirst element.
8247 self._write_color_first(sparkline["first_color"])
8249 # Write the x14:colorLast element.
8250 self._write_color_last(sparkline["last_color"])
8252 # Write the x14:colorHigh element.
8253 self._write_color_high(sparkline["high_color"])
8255 # Write the x14:colorLow element.
8256 self._write_color_low(sparkline["low_color"])
8258 if sparkline["date_axis"]:
8259 self._xml_data_element("xm:f", sparkline["date_axis"])
8261 self._write_sparklines(sparkline)
8263 self._xml_end_tag("x14:sparklineGroup")
8265 self._xml_end_tag("x14:sparklineGroups")
8266 self._xml_end_tag("ext")
8268 def _write_sparklines(self, sparkline):
8269 # Write the <x14:sparklines> element and <x14:sparkline> sub-elements.
8271 # Write the sparkline elements.
8272 self._xml_start_tag("x14:sparklines")
8274 for i in range(sparkline["count"]):
8275 spark_range = sparkline["ranges"][i]
8276 location = sparkline["locations"][i]
8278 self._xml_start_tag("x14:sparkline")
8279 self._xml_data_element("xm:f", spark_range)
8280 self._xml_data_element("xm:sqref", location)
8281 self._xml_end_tag("x14:sparkline")
8283 self._xml_end_tag("x14:sparklines")
8285 def _write_ext(self, uri):
8286 # Write the <ext> element.
8287 schema = "http://schemas.microsoft.com/office/"
8288 xmlns_x14 = schema + "spreadsheetml/2009/9/main"
8290 attributes = [
8291 ("xmlns:x14", xmlns_x14),
8292 ("uri", uri),
8293 ]
8295 self._xml_start_tag("ext", attributes)
8297 def _write_sparkline_groups(self):
8298 # Write the <x14:sparklineGroups> element.
8299 xmlns_xm = "http://schemas.microsoft.com/office/excel/2006/main"
8301 attributes = [("xmlns:xm", xmlns_xm)]
8303 self._xml_start_tag("x14:sparklineGroups", attributes)
8305 def _write_sparkline_group(self, options):
8306 # Write the <x14:sparklineGroup> element.
8307 #
8308 # Example for order.
8309 #
8310 # <x14:sparklineGroup
8311 # manualMax="0"
8312 # manualMin="0"
8313 # lineWeight="2.25"
8314 # type="column"
8315 # dateAxis="1"
8316 # displayEmptyCellsAs="span"
8317 # markers="1"
8318 # high="1"
8319 # low="1"
8320 # first="1"
8321 # last="1"
8322 # negative="1"
8323 # displayXAxis="1"
8324 # displayHidden="1"
8325 # minAxisType="custom"
8326 # maxAxisType="custom"
8327 # rightToLeft="1">
8328 #
8329 empty = options.get("empty")
8330 attributes = []
8332 if options.get("max") is not None:
8333 if options["max"] == "group":
8334 options["cust_max"] = "group"
8335 else:
8336 attributes.append(("manualMax", options["max"]))
8337 options["cust_max"] = "custom"
8339 if options.get("min") is not None:
8340 if options["min"] == "group":
8341 options["cust_min"] = "group"
8342 else:
8343 attributes.append(("manualMin", options["min"]))
8344 options["cust_min"] = "custom"
8346 # Ignore the default type attribute (line).
8347 if options["type"] != "line":
8348 attributes.append(("type", options["type"]))
8350 if options.get("weight"):
8351 attributes.append(("lineWeight", options["weight"]))
8353 if options.get("date_axis"):
8354 attributes.append(("dateAxis", 1))
8356 if empty:
8357 attributes.append(("displayEmptyCellsAs", empty))
8359 if options.get("markers"):
8360 attributes.append(("markers", 1))
8362 if options.get("high"):
8363 attributes.append(("high", 1))
8365 if options.get("low"):
8366 attributes.append(("low", 1))
8368 if options.get("first"):
8369 attributes.append(("first", 1))
8371 if options.get("last"):
8372 attributes.append(("last", 1))
8374 if options.get("negative"):
8375 attributes.append(("negative", 1))
8377 if options.get("axis"):
8378 attributes.append(("displayXAxis", 1))
8380 if options.get("hidden"):
8381 attributes.append(("displayHidden", 1))
8383 if options.get("cust_min"):
8384 attributes.append(("minAxisType", options["cust_min"]))
8386 if options.get("cust_max"):
8387 attributes.append(("maxAxisType", options["cust_max"]))
8389 if options.get("reverse"):
8390 attributes.append(("rightToLeft", 1))
8392 self._xml_start_tag("x14:sparklineGroup", attributes)
8394 def _write_spark_color(self, element, color):
8395 # Helper function for the sparkline color functions below.
8396 attributes = []
8398 if color.get("rgb"):
8399 attributes.append(("rgb", color["rgb"]))
8401 if color.get("theme"):
8402 attributes.append(("theme", color["theme"]))
8404 if color.get("tint"):
8405 attributes.append(("tint", color["tint"]))
8407 self._xml_empty_tag(element, attributes)
8409 def _write_color_series(self, color):
8410 # Write the <x14:colorSeries> element.
8411 self._write_spark_color("x14:colorSeries", color)
8413 def _write_color_negative(self, color):
8414 # Write the <x14:colorNegative> element.
8415 self._write_spark_color("x14:colorNegative", color)
8417 def _write_color_axis(self):
8418 # Write the <x14:colorAxis> element.
8419 self._write_spark_color("x14:colorAxis", {"rgb": "FF000000"})
8421 def _write_color_markers(self, color):
8422 # Write the <x14:colorMarkers> element.
8423 self._write_spark_color("x14:colorMarkers", color)
8425 def _write_color_first(self, color):
8426 # Write the <x14:colorFirst> element.
8427 self._write_spark_color("x14:colorFirst", color)
8429 def _write_color_last(self, color):
8430 # Write the <x14:colorLast> element.
8431 self._write_spark_color("x14:colorLast", color)
8433 def _write_color_high(self, color):
8434 # Write the <x14:colorHigh> element.
8435 self._write_spark_color("x14:colorHigh", color)
8437 def _write_color_low(self, color):
8438 # Write the <x14:colorLow> element.
8439 self._write_spark_color("x14:colorLow", color)
8441 def _write_phonetic_pr(self):
8442 # Write the <phoneticPr> element.
8443 attributes = [
8444 ("fontId", "0"),
8445 ("type", "noConversion"),
8446 ]
8448 self._xml_empty_tag("phoneticPr", attributes)
8450 def _write_ignored_errors(self):
8451 # Write the <ignoredErrors> element.
8452 if not self.ignored_errors:
8453 return
8455 self._xml_start_tag("ignoredErrors")
8457 if self.ignored_errors.get("number_stored_as_text"):
8458 ignored_range = self.ignored_errors["number_stored_as_text"]
8459 self._write_ignored_error("numberStoredAsText", ignored_range)
8461 if self.ignored_errors.get("eval_error"):
8462 ignored_range = self.ignored_errors["eval_error"]
8463 self._write_ignored_error("evalError", ignored_range)
8465 if self.ignored_errors.get("formula_differs"):
8466 ignored_range = self.ignored_errors["formula_differs"]
8467 self._write_ignored_error("formula", ignored_range)
8469 if self.ignored_errors.get("formula_range"):
8470 ignored_range = self.ignored_errors["formula_range"]
8471 self._write_ignored_error("formulaRange", ignored_range)
8473 if self.ignored_errors.get("formula_unlocked"):
8474 ignored_range = self.ignored_errors["formula_unlocked"]
8475 self._write_ignored_error("unlockedFormula", ignored_range)
8477 if self.ignored_errors.get("empty_cell_reference"):
8478 ignored_range = self.ignored_errors["empty_cell_reference"]
8479 self._write_ignored_error("emptyCellReference", ignored_range)
8481 if self.ignored_errors.get("list_data_validation"):
8482 ignored_range = self.ignored_errors["list_data_validation"]
8483 self._write_ignored_error("listDataValidation", ignored_range)
8485 if self.ignored_errors.get("calculated_column"):
8486 ignored_range = self.ignored_errors["calculated_column"]
8487 self._write_ignored_error("calculatedColumn", ignored_range)
8489 if self.ignored_errors.get("two_digit_text_year"):
8490 ignored_range = self.ignored_errors["two_digit_text_year"]
8491 self._write_ignored_error("twoDigitTextYear", ignored_range)
8493 self._xml_end_tag("ignoredErrors")
8495 def _write_ignored_error(self, type, ignored_range):
8496 # Write the <ignoredError> element.
8497 attributes = [
8498 ("sqref", ignored_range),
8499 (type, 1),
8500 ]
8502 self._xml_empty_tag("ignoredError", attributes)