Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.11/site-packages/xlsxwriter/workbook.py: 52%
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# Workbook - A class for writing the Excel XLSX Workbook file.
4#
5# SPDX-License-Identifier: BSD-2-Clause
6#
7# Copyright (c) 2013-2025, John McNamara, jmcnamara@cpan.org
8#
10# Standard packages.
11import operator
12import os
13import re
14import time
15from datetime import datetime, timezone
16from decimal import Decimal
17from fractions import Fraction
18from warnings import warn
19from zipfile import ZIP_DEFLATED, LargeZipFile, ZipFile, ZipInfo
21from xlsxwriter.image import Image
23# Package imports.
24from . import xmlwriter
25from .chart_area import ChartArea
26from .chart_bar import ChartBar
27from .chart_column import ChartColumn
28from .chart_doughnut import ChartDoughnut
29from .chart_line import ChartLine
30from .chart_pie import ChartPie
31from .chart_radar import ChartRadar
32from .chart_scatter import ChartScatter
33from .chart_stock import ChartStock
34from .chartsheet import Chartsheet
35from .exceptions import (
36 DuplicateWorksheetName,
37 FileCreateError,
38 FileSizeError,
39 InvalidWorksheetName,
40)
41from .format import Format
42from .packager import Packager
43from .sharedstrings import SharedStringTable
44from .utility import xl_cell_to_rowcol
45from .worksheet import Worksheet
48class Workbook(xmlwriter.XMLwriter):
49 """
50 A class for writing the Excel XLSX Workbook file.
53 """
55 ###########################################################################
56 #
57 # Public API.
58 #
59 ###########################################################################
60 chartsheet_class = Chartsheet
61 worksheet_class = Worksheet
63 def __init__(self, filename=None, options=None):
64 """
65 Constructor.
67 """
68 if options is None:
69 options = {}
71 super().__init__()
73 self.filename = filename
75 self.tmpdir = options.get("tmpdir", None)
76 self.date_1904 = options.get("date_1904", False)
77 self.strings_to_numbers = options.get("strings_to_numbers", False)
78 self.strings_to_formulas = options.get("strings_to_formulas", True)
79 self.strings_to_urls = options.get("strings_to_urls", True)
80 self.nan_inf_to_errors = options.get("nan_inf_to_errors", False)
81 self.default_date_format = options.get("default_date_format", None)
82 self.constant_memory = options.get("constant_memory", False)
83 self.in_memory = options.get("in_memory", False)
84 self.excel2003_style = options.get("excel2003_style", False)
85 self.remove_timezone = options.get("remove_timezone", False)
86 self.use_future_functions = options.get("use_future_functions", False)
87 self.default_format_properties = options.get("default_format_properties", {})
89 self.max_url_length = options.get("max_url_length", 2079)
90 if self.max_url_length < 255:
91 self.max_url_length = 2079
93 if options.get("use_zip64"):
94 self.allow_zip64 = True
95 else:
96 self.allow_zip64 = False
98 self.worksheet_meta = WorksheetMeta()
99 self.selected = 0
100 self.fileclosed = 0
101 self.filehandle = None
102 self.internal_fh = 0
103 self.sheet_name = "Sheet"
104 self.chart_name = "Chart"
105 self.sheetname_count = 0
106 self.chartname_count = 0
107 self.worksheets_objs = []
108 self.charts = []
109 self.drawings = []
110 self.sheetnames = {}
111 self.formats = []
112 self.xf_formats = []
113 self.xf_format_indices = {}
114 self.dxf_formats = []
115 self.dxf_format_indices = {}
116 self.palette = []
117 self.font_count = 0
118 self.num_formats = []
119 self.defined_names = []
120 self.named_ranges = []
121 self.custom_colors = []
122 self.doc_properties = {}
123 self.custom_properties = []
124 self.createtime = datetime.now(timezone.utc)
125 self.num_vml_files = 0
126 self.num_comment_files = 0
127 self.x_window = 240
128 self.y_window = 15
129 self.window_width = 16095
130 self.window_height = 9660
131 self.tab_ratio = 600
132 self.str_table = SharedStringTable()
133 self.vba_project = None
134 self.vba_project_is_stream = False
135 self.vba_project_signature = None
136 self.vba_project_signature_is_stream = False
137 self.vba_codename = None
138 self.image_types = {}
139 self.images = []
140 self.border_count = 0
141 self.fill_count = 0
142 self.drawing_count = 0
143 self.calc_mode = "auto"
144 self.calc_on_load = True
145 self.calc_id = 124519
146 self.has_comments = False
147 self.read_only = 0
148 self.has_metadata = False
149 self.has_embedded_images = False
150 self.has_dynamic_functions = False
151 self.has_embedded_descriptions = False
152 self.embedded_images = EmbeddedImages()
153 self.feature_property_bags = set()
155 # We can't do 'constant_memory' mode while doing 'in_memory' mode.
156 if self.in_memory:
157 self.constant_memory = False
159 # Add the default cell format.
160 if self.excel2003_style:
161 self.add_format({"xf_index": 0, "font_family": 0})
162 else:
163 self.add_format({"xf_index": 0})
165 # Add a default URL format.
166 self.default_url_format = self.add_format({"hyperlink": True})
168 # Add the default date format.
169 if self.default_date_format is not None:
170 self.default_date_format = self.add_format(
171 {"num_format": self.default_date_format}
172 )
174 def __enter__(self):
175 """Return self object to use with "with" statement."""
176 return self
178 def __exit__(self, type, value, traceback):
179 # pylint: disable=redefined-builtin
180 """Close workbook when exiting "with" statement."""
181 self.close()
183 def add_worksheet(self, name=None, worksheet_class=None):
184 """
185 Add a new worksheet to the Excel workbook.
187 Args:
188 name: The worksheet name. Defaults to 'Sheet1', etc.
190 Returns:
191 Reference to a worksheet object.
193 """
194 if worksheet_class is None:
195 worksheet_class = self.worksheet_class
197 return self._add_sheet(name, worksheet_class=worksheet_class)
199 def add_chartsheet(self, name=None, chartsheet_class=None):
200 """
201 Add a new chartsheet to the Excel workbook.
203 Args:
204 name: The chartsheet name. Defaults to 'Sheet1', etc.
206 Returns:
207 Reference to a chartsheet object.
209 """
210 if chartsheet_class is None:
211 chartsheet_class = self.chartsheet_class
213 return self._add_sheet(name, worksheet_class=chartsheet_class)
215 def add_format(self, properties=None):
216 """
217 Add a new Format to the Excel Workbook.
219 Args:
220 properties: The format properties.
222 Returns:
223 Reference to a Format object.
225 """
226 format_properties = self.default_format_properties.copy()
228 if self.excel2003_style:
229 format_properties = {"font_name": "Arial", "font_size": 10, "theme": 1 * -1}
231 if properties:
232 format_properties.update(properties)
234 xf_format = Format(
235 format_properties, self.xf_format_indices, self.dxf_format_indices
236 )
238 # Store the format reference.
239 self.formats.append(xf_format)
241 return xf_format
243 def add_chart(self, options):
244 """
245 Create a chart object.
247 Args:
248 options: The chart type and subtype options.
250 Returns:
251 Reference to a Chart object.
253 """
255 # Type must be specified so we can create the required chart instance.
256 chart_type = options.get("type")
257 if chart_type is None:
258 warn("Chart type must be defined in add_chart()")
259 return None
261 if chart_type == "area":
262 chart = ChartArea(options)
263 elif chart_type == "bar":
264 chart = ChartBar(options)
265 elif chart_type == "column":
266 chart = ChartColumn(options)
267 elif chart_type == "doughnut":
268 chart = ChartDoughnut()
269 elif chart_type == "line":
270 chart = ChartLine(options)
271 elif chart_type == "pie":
272 chart = ChartPie()
273 elif chart_type == "radar":
274 chart = ChartRadar(options)
275 elif chart_type == "scatter":
276 chart = ChartScatter(options)
277 elif chart_type == "stock":
278 chart = ChartStock()
279 else:
280 warn(f"Unknown chart type '{chart_type}' in add_chart()")
281 return None
283 # Set the embedded chart name if present.
284 if "name" in options:
285 chart.chart_name = options["name"]
287 chart.embedded = True
288 chart.date_1904 = self.date_1904
289 chart.remove_timezone = self.remove_timezone
291 self.charts.append(chart)
293 return chart
295 def add_vba_project(self, vba_project, is_stream=False):
296 """
297 Add a vbaProject binary to the Excel workbook.
299 Args:
300 vba_project: The vbaProject binary file name.
301 is_stream: vba_project is an in memory byte stream.
303 Returns:
304 0 on success.
306 """
307 if not is_stream and not os.path.exists(vba_project):
308 warn(f"VBA project binary file '{vba_project}' not found.")
309 return -1
311 if self.vba_codename is None:
312 self.vba_codename = "ThisWorkbook"
314 self.vba_project = vba_project
315 self.vba_project_is_stream = is_stream
317 return 0
319 def add_signed_vba_project(
320 self, vba_project, signature, project_is_stream=False, signature_is_stream=False
321 ):
322 """
323 Add a vbaProject binary and a vbaProjectSignature binary to the
324 Excel workbook.
326 Args:
327 vba_project: The vbaProject binary file name.
328 signature: The vbaProjectSignature binary file name.
329 project_is_stream: vba_project is an in memory byte stream.
330 signature_is_stream: signature is an in memory byte stream.
332 Returns:
333 0 on success.
335 """
336 if self.add_vba_project(vba_project, project_is_stream) == -1:
337 return -1
339 if not signature_is_stream and not os.path.exists(signature):
340 warn(f"VBA project signature binary file '{signature}' not found.")
341 return -1
343 self.vba_project_signature = signature
344 self.vba_project_signature_is_stream = signature_is_stream
346 return 0
348 def close(self):
349 """
350 Call finalization code and close file.
352 Args:
353 None.
355 Returns:
356 Nothing.
358 """
359 # pylint: disable=raise-missing-from
360 if not self.fileclosed:
361 try:
362 self._store_workbook()
363 except IOError as e:
364 raise FileCreateError(e)
365 except LargeZipFile:
366 raise FileSizeError(
367 "Filesize would require ZIP64 extensions. "
368 "Use workbook.use_zip64()."
369 )
371 self.fileclosed = True
373 # Ensure all constant_memory temp files are closed.
374 if self.constant_memory:
375 for worksheet in self.worksheets():
376 worksheet._opt_close()
378 else:
379 warn("Calling close() on already closed file.")
381 def set_size(self, width, height):
382 """
383 Set the size of a workbook window.
385 Args:
386 width: Width of the window in pixels.
387 height: Height of the window in pixels.
389 Returns:
390 Nothing.
392 """
393 # Convert the width/height to twips at 96 dpi.
394 if width:
395 self.window_width = int(width * 1440 / 96)
396 else:
397 self.window_width = 16095
399 if height:
400 self.window_height = int(height * 1440 / 96)
401 else:
402 self.window_height = 9660
404 def set_tab_ratio(self, tab_ratio=None):
405 """
406 Set the ratio between worksheet tabs and the horizontal slider.
408 Args:
409 tab_ratio: The tab ratio, 0 <= tab_ratio <= 100
411 Returns:
412 Nothing.
414 """
415 if tab_ratio is None:
416 return
418 if tab_ratio < 0 or tab_ratio > 100:
419 warn(f"Tab ratio '{tab_ratio}' outside: 0 <= tab_ratio <= 100")
420 else:
421 self.tab_ratio = int(tab_ratio * 10)
423 def set_properties(self, properties):
424 """
425 Set the document properties such as Title, Author etc.
427 Args:
428 properties: Dictionary of document properties.
430 Returns:
431 Nothing.
433 """
434 self.doc_properties = properties
436 def set_custom_property(self, name, value, property_type=None):
437 """
438 Set a custom document property.
440 Args:
441 name: The name of the custom property.
442 value: The value of the custom property.
443 property_type: The type of the custom property. Optional.
445 Returns:
446 0 on success.
448 """
449 if name is None or value is None:
450 warn(
451 "The name and value parameters must be non-None in "
452 "set_custom_property()"
453 )
454 return -1
456 if property_type is None:
457 # Determine the property type from the Python type.
458 if isinstance(value, bool):
459 property_type = "bool"
460 elif isinstance(value, datetime):
461 property_type = "date"
462 elif isinstance(value, int):
463 property_type = "number_int"
464 elif isinstance(value, (float, int, Decimal, Fraction)):
465 property_type = "number"
466 else:
467 property_type = "text"
469 # Convert non-string values to strings to have a single data type.
470 if property_type == "bool":
471 value = str(value).lower()
473 if property_type == "date":
474 value = value.strftime("%Y-%m-%dT%H:%M:%SZ")
476 if property_type in ("number", "number_int"):
477 value = str(value)
479 if property_type == "text" and len(value) > 255:
480 warn(
481 f"Length of 'value' parameter exceeds Excel's limit of 255 "
482 f"characters in set_custom_property(): '{value}'"
483 )
485 if len(name) > 255:
486 warn(
487 f"Length of 'name' parameter exceeds Excel's limit of 255 "
488 f"characters in set_custom_property(): '{name}'"
489 )
491 self.custom_properties.append((name, value, property_type))
493 return 0
495 def set_calc_mode(self, mode, calc_id=None):
496 """
497 Set the Excel calculation mode for the workbook.
499 Args:
500 mode: String containing one of:
501 * manual
502 * auto_except_tables
503 * auto
505 Returns:
506 Nothing.
508 """
509 self.calc_mode = mode
511 if mode == "manual":
512 self.calc_on_load = False
513 elif mode == "auto_except_tables":
514 self.calc_mode = "autoNoTable"
516 # Leave undocumented for now. Rarely required.
517 if calc_id:
518 self.calc_id = calc_id
520 def define_name(self, name, formula):
521 # Create a defined name in Excel. We handle global/workbook level
522 # names and local/worksheet names.
523 """
524 Create a defined name in the workbook.
526 Args:
527 name: The defined name.
528 formula: The cell or range that the defined name refers to.
530 Returns:
531 0 on success.
533 """
534 sheet_index = None
535 sheetname = ""
537 # Remove the = sign from the formula if it exists.
538 if formula.startswith("="):
539 formula = formula.lstrip("=")
541 # Local defined names are formatted like "Sheet1!name".
542 sheet_parts = re.compile(r"^([^!]+)!([^!]+)$")
543 match = sheet_parts.match(name)
545 if match:
546 sheetname = match.group(1)
547 name = match.group(2)
548 sheet_index = self._get_sheet_index(sheetname)
550 # Warn if the sheet index wasn't found.
551 if sheet_index is None:
552 warn(f"Unknown sheet name '{sheetname}' in defined_name()")
553 return -1
554 else:
555 # Use -1 to indicate global names.
556 sheet_index = -1
558 # Warn if the defined name contains invalid chars as defined by Excel.
559 if not re.match(r"^[\w\\][\w\\.]*$", name, re.UNICODE) or re.match(
560 r"^\d", name
561 ):
562 warn(f"Invalid Excel characters in defined_name(): '{name}'")
563 return -1
565 # Warn if the defined name looks like a cell name.
566 if re.match(r"^[a-zA-Z][a-zA-Z]?[a-dA-D]?\d+$", name):
567 warn(f"Name looks like a cell name in defined_name(): '{name}'")
568 return -1
570 # Warn if the name looks like a R1C1 cell reference.
571 if re.match(r"^[rcRC]$", name) or re.match(r"^[rcRC]\d+[rcRC]\d+$", name):
572 warn(f"Invalid name '{name}' like a RC cell ref in defined_name()")
573 return -1
575 self.defined_names.append([name, sheet_index, formula, False])
577 return 0
579 def worksheets(self):
580 """
581 Return a list of the worksheet objects in the workbook.
583 Args:
584 None.
586 Returns:
587 A list of worksheet objects.
589 """
590 return self.worksheets_objs
592 def get_worksheet_by_name(self, name):
593 """
594 Return a worksheet object in the workbook using the sheetname.
596 Args:
597 name: The name of the worksheet.
599 Returns:
600 A worksheet object or None.
602 """
603 return self.sheetnames.get(name)
605 def get_default_url_format(self):
606 """
607 Get the default url format used when a user defined format isn't
608 specified with write_url(). The format is the hyperlink style defined
609 by Excel for the default theme.
611 Args:
612 None.
614 Returns:
615 A format object.
617 """
618 return self.default_url_format
620 def use_zip64(self):
621 """
622 Allow ZIP64 extensions when writing xlsx file zip container.
624 Args:
625 None.
627 Returns:
628 Nothing.
630 """
631 self.allow_zip64 = True
633 def set_vba_name(self, name=None):
634 """
635 Set the VBA name for the workbook. By default the workbook is referred
636 to as ThisWorkbook in VBA.
638 Args:
639 name: The VBA name for the workbook.
641 Returns:
642 Nothing.
644 """
645 if name is not None:
646 self.vba_codename = name
647 else:
648 self.vba_codename = "ThisWorkbook"
650 def read_only_recommended(self):
651 """
652 Set the Excel "Read-only recommended" option when saving a file.
654 Args:
655 None.
657 Returns:
658 Nothing.
660 """
661 self.read_only = 2
663 ###########################################################################
664 #
665 # Private API.
666 #
667 ###########################################################################
669 def _assemble_xml_file(self):
670 # Assemble and write the XML file.
672 # Prepare format object for passing to Style.pm.
673 self._prepare_format_properties()
675 # Write the XML declaration.
676 self._xml_declaration()
678 # Write the workbook element.
679 self._write_workbook()
681 # Write the fileVersion element.
682 self._write_file_version()
684 # Write the fileSharing element.
685 self._write_file_sharing()
687 # Write the workbookPr element.
688 self._write_workbook_pr()
690 # Write the bookViews element.
691 self._write_book_views()
693 # Write the sheets element.
694 self._write_sheets()
696 # Write the workbook defined names.
697 self._write_defined_names()
699 # Write the calcPr element.
700 self._write_calc_pr()
702 # Close the workbook tag.
703 self._xml_end_tag("workbook")
705 # Close the file.
706 self._xml_close()
708 def _store_workbook(self):
709 # pylint: disable=consider-using-with
710 # Create the xlsx/zip file.
711 try:
712 xlsx_file = ZipFile(
713 self.filename,
714 "w",
715 compression=ZIP_DEFLATED,
716 allowZip64=self.allow_zip64,
717 )
718 except IOError as e:
719 raise e
721 # Assemble worksheets into a workbook.
722 packager = self._get_packager()
724 # Add a default worksheet if non have been added.
725 if not self.worksheets():
726 self.add_worksheet()
728 # Ensure that at least one worksheet has been selected.
729 if self.worksheet_meta.activesheet == 0:
730 self.worksheets_objs[0].selected = 1
731 self.worksheets_objs[0].hidden = 0
733 # Set the active sheet.
734 for sheet in self.worksheets():
735 if sheet.index == self.worksheet_meta.activesheet:
736 sheet.active = 1
738 # Set the sheet vba_codename the workbook has a vbaProject binary.
739 if self.vba_project:
740 for sheet in self.worksheets():
741 if sheet.vba_codename is None:
742 sheet.set_vba_name()
744 # Convert the SST strings data structure.
745 self._prepare_sst_string_data()
747 # Prepare the worksheet VML elements such as comments and buttons.
748 self._prepare_vml()
750 # Set the defined names for the worksheets such as Print Titles.
751 self._prepare_defined_names()
753 # Prepare the drawings, charts and images.
754 self._prepare_drawings()
756 # Add cached data to charts.
757 self._add_chart_data()
759 # Prepare the worksheet tables.
760 self._prepare_tables()
762 # Prepare the metadata file links.
763 self._prepare_metadata()
765 # Package the workbook.
766 packager._add_workbook(self)
767 packager._set_tmpdir(self.tmpdir)
768 packager._set_in_memory(self.in_memory)
769 xml_files = packager._create_package()
771 # Free up the Packager object.
772 packager = None
774 # Add XML sub-files to the Zip file with their Excel filename.
775 for file_id, file_data in enumerate(xml_files):
776 os_filename, xml_filename, is_binary = file_data
778 if self.in_memory:
779 # Set sub-file timestamp to Excel's timestamp of 1/1/1980.
780 zipinfo = ZipInfo(xml_filename, (1980, 1, 1, 0, 0, 0))
782 # Copy compression type from parent ZipFile.
783 zipinfo.compress_type = xlsx_file.compression
785 if is_binary:
786 xlsx_file.writestr(zipinfo, os_filename.getvalue())
787 else:
788 xlsx_file.writestr(zipinfo, os_filename.getvalue().encode("utf-8"))
789 else:
790 # The sub-files are tempfiles on disk, i.e, not in memory.
792 # Set sub-file timestamp to 31/1/1980 due to portability
793 # issues setting it to Excel's timestamp of 1/1/1980.
794 timestamp = time.mktime((1980, 1, 31, 0, 0, 0, 0, 0, -1))
795 os.utime(os_filename, (timestamp, timestamp))
797 try:
798 xlsx_file.write(os_filename, xml_filename)
799 os.remove(os_filename)
800 except LargeZipFile as e:
801 # Close open temp files on zipfile.LargeZipFile exception.
802 for i in range(file_id, len(xml_files) - 1):
803 os.remove(xml_files[i][0])
804 raise e
806 xlsx_file.close()
808 def _add_sheet(self, name, worksheet_class=None):
809 # Utility for shared code in add_worksheet() and add_chartsheet().
811 if worksheet_class:
812 worksheet = worksheet_class()
813 else:
814 worksheet = self.worksheet_class()
816 sheet_index = len(self.worksheets_objs)
817 name = self._check_sheetname(name, isinstance(worksheet, Chartsheet))
819 # Initialization data to pass to the worksheet.
820 init_data = {
821 "name": name,
822 "index": sheet_index,
823 "str_table": self.str_table,
824 "worksheet_meta": self.worksheet_meta,
825 "constant_memory": self.constant_memory,
826 "tmpdir": self.tmpdir,
827 "date_1904": self.date_1904,
828 "strings_to_numbers": self.strings_to_numbers,
829 "strings_to_formulas": self.strings_to_formulas,
830 "strings_to_urls": self.strings_to_urls,
831 "nan_inf_to_errors": self.nan_inf_to_errors,
832 "default_date_format": self.default_date_format,
833 "default_url_format": self.default_url_format,
834 "workbook_add_format": self.add_format,
835 "excel2003_style": self.excel2003_style,
836 "remove_timezone": self.remove_timezone,
837 "max_url_length": self.max_url_length,
838 "use_future_functions": self.use_future_functions,
839 "embedded_images": self.embedded_images,
840 }
842 worksheet._initialize(init_data)
844 self.worksheets_objs.append(worksheet)
845 self.sheetnames[name] = worksheet
847 return worksheet
849 def _check_sheetname(self, sheetname, is_chartsheet=False):
850 # Check for valid worksheet names. We check the length, if it contains
851 # any invalid chars and if the sheetname is unique in the workbook.
852 invalid_char = re.compile(r"[\[\]:*?/\\]")
854 # Increment the Sheet/Chart number used for default sheet names below.
855 if is_chartsheet:
856 self.chartname_count += 1
857 else:
858 self.sheetname_count += 1
860 # Supply default Sheet/Chart sheetname if none has been defined.
861 if sheetname is None or sheetname == "":
862 if is_chartsheet:
863 sheetname = self.chart_name + str(self.chartname_count)
864 else:
865 sheetname = self.sheet_name + str(self.sheetname_count)
867 # Check that sheet sheetname is <= 31. Excel limit.
868 if len(sheetname) > 31:
869 raise InvalidWorksheetName(
870 f"Excel worksheet name '{sheetname}' must be <= 31 chars."
871 )
873 # Check that sheetname doesn't contain any invalid characters.
874 if invalid_char.search(sheetname):
875 raise InvalidWorksheetName(
876 f"Invalid Excel character '[]:*?/\\' in sheetname '{sheetname}'."
877 )
879 # Check that sheetname doesn't start or end with an apostrophe.
880 if sheetname.startswith("'") or sheetname.endswith("'"):
881 raise InvalidWorksheetName(
882 f'Sheet name cannot start or end with an apostrophe "{sheetname}".'
883 )
885 # Check that the worksheet name doesn't already exist since this is a
886 # fatal Excel error. The check must be case insensitive like Excel.
887 for worksheet in self.worksheets():
888 if sheetname.lower() == worksheet.name.lower():
889 raise DuplicateWorksheetName(
890 f"Sheetname '{sheetname}', with case ignored, is already in use."
891 )
893 return sheetname
895 def _prepare_format_properties(self):
896 # Prepare all Format properties prior to passing them to styles.py.
898 # Separate format objects into XF and DXF formats.
899 self._prepare_formats()
901 # Set the font index for the format objects.
902 self._prepare_fonts()
904 # Set the number format index for the format objects.
905 self._prepare_num_formats()
907 # Set the border index for the format objects.
908 self._prepare_borders()
910 # Set the fill index for the format objects.
911 self._prepare_fills()
913 def _prepare_formats(self):
914 # Iterate through the XF Format objects and separate them into
915 # XF and DXF formats. The XF and DF formats then need to be sorted
916 # back into index order rather than creation order.
917 xf_formats = []
918 dxf_formats = []
920 # Sort into XF and DXF formats.
921 for xf_format in self.formats:
922 if xf_format.xf_index is not None:
923 xf_formats.append(xf_format)
925 if xf_format.dxf_index is not None:
926 dxf_formats.append(xf_format)
928 # Pre-extend the format lists.
929 self.xf_formats = [None] * len(xf_formats)
930 self.dxf_formats = [None] * len(dxf_formats)
932 # Rearrange formats into index order.
933 for xf_format in xf_formats:
934 index = xf_format.xf_index
935 self.xf_formats[index] = xf_format
937 for dxf_format in dxf_formats:
938 index = dxf_format.dxf_index
939 self.dxf_formats[index] = dxf_format
941 def _set_default_xf_indices(self):
942 # Set the default index for each format. Only used for testing.
944 formats = list(self.formats)
946 # Delete the default url format.
947 del formats[1]
949 # Skip the default date format if set.
950 if self.default_date_format is not None:
951 del formats[1]
953 # Set the remaining formats.
954 for xf_format in formats:
955 xf_format._get_xf_index()
957 def _prepare_fonts(self):
958 # Iterate through the XF Format objects and give them an index to
959 # non-default font elements.
960 fonts = {}
961 index = 0
963 for xf_format in self.xf_formats:
964 key = xf_format._get_font_key()
965 if key in fonts:
966 # Font has already been used.
967 xf_format.font_index = fonts[key]
968 xf_format.has_font = False
969 else:
970 # This is a new font.
971 fonts[key] = index
972 xf_format.font_index = index
973 xf_format.has_font = True
974 index += 1
976 self.font_count = index
978 # For DXF formats we only need to check if the properties have changed.
979 for xf_format in self.dxf_formats:
980 # The only font properties that can change for a DXF format are:
981 # color, bold, italic, underline and strikethrough.
982 if (
983 xf_format.font_color
984 or xf_format.bold
985 or xf_format.italic
986 or xf_format.underline
987 or xf_format.font_strikeout
988 ):
989 xf_format.has_dxf_font = True
991 def _prepare_num_formats(self):
992 # User defined records in Excel start from index 0xA4.
993 unique_num_formats = {}
994 num_formats = []
995 index = 164
997 for xf_format in self.xf_formats + self.dxf_formats:
998 num_format = xf_format.num_format
1000 # Check if num_format is an index to a built-in number format.
1001 if not isinstance(num_format, str):
1002 num_format = int(num_format)
1004 # Number format '0' is indexed as 1 in Excel.
1005 if num_format == 0:
1006 num_format = 1
1008 xf_format.num_format_index = num_format
1009 continue
1011 if num_format == "0":
1012 # Number format '0' is indexed as 1 in Excel.
1013 xf_format.num_format_index = 1
1014 continue
1016 if num_format == "General":
1017 # The 'General' format has an number format index of 0.
1018 xf_format.num_format_index = 0
1019 continue
1021 if num_format in unique_num_formats:
1022 # Number xf_format has already been used.
1023 xf_format.num_format_index = unique_num_formats[num_format]
1024 else:
1025 # Add a new number xf_format.
1026 unique_num_formats[num_format] = index
1027 xf_format.num_format_index = index
1028 index += 1
1030 # Only increase font count for XF formats (not DXF formats).
1031 if xf_format.xf_index:
1032 num_formats.append(num_format)
1034 self.num_formats = num_formats
1036 def _prepare_borders(self):
1037 # Iterate through the XF Format objects and give them an index to
1038 # non-default border elements.
1039 borders = {}
1040 index = 0
1042 for xf_format in self.xf_formats:
1043 key = xf_format._get_border_key()
1045 if key in borders:
1046 # Border has already been used.
1047 xf_format.border_index = borders[key]
1048 xf_format.has_border = False
1049 else:
1050 # This is a new border.
1051 borders[key] = index
1052 xf_format.border_index = index
1053 xf_format.has_border = True
1054 index += 1
1056 self.border_count = index
1058 # For DXF formats we only need to check if the properties have changed.
1059 has_border = re.compile(r"[^0None:]")
1061 for xf_format in self.dxf_formats:
1062 key = xf_format._get_border_key()
1064 if has_border.search(key):
1065 xf_format.has_dxf_border = True
1067 def _prepare_fills(self):
1068 # Iterate through the XF Format objects and give them an index to
1069 # non-default fill elements.
1070 # The user defined fill properties start from 2 since there are 2
1071 # default fills: patternType="none" and patternType="gray125".
1072 fills = {}
1073 index = 2 # Start from 2. See above.
1075 # Add the default fills.
1076 fills["0:None:None"] = 0
1077 fills["17:None:None"] = 1
1079 # Store the DXF colors separately since them may be reversed below.
1080 for xf_format in self.dxf_formats:
1081 if xf_format.pattern or xf_format.bg_color or xf_format.fg_color:
1082 xf_format.has_dxf_fill = True
1083 xf_format.dxf_bg_color = xf_format.bg_color
1084 xf_format.dxf_fg_color = xf_format.fg_color
1086 for xf_format in self.xf_formats:
1087 # The following logical statements jointly take care of special
1088 # cases in relation to cell colors and patterns:
1089 # 1. For a solid fill (_pattern == 1) Excel reverses the role of
1090 # foreground and background colors, and
1091 # 2. If the user specifies a foreground or background color
1092 # without a pattern they probably wanted a solid fill, so we fill
1093 # in the defaults.
1094 if xf_format.pattern == 1 and xf_format.bg_color and xf_format.fg_color:
1095 tmp = xf_format.fg_color
1096 xf_format.fg_color = xf_format.bg_color
1097 xf_format.bg_color = tmp
1099 if xf_format.pattern <= 1 and xf_format.bg_color and not xf_format.fg_color:
1100 xf_format.fg_color = xf_format.bg_color
1101 xf_format.bg_color = None
1102 xf_format.pattern = 1
1104 if xf_format.pattern <= 1 and not xf_format.bg_color and xf_format.fg_color:
1105 xf_format.pattern = 1
1107 key = xf_format._get_fill_key()
1109 if key in fills:
1110 # Fill has already been used.
1111 xf_format.fill_index = fills[key]
1112 xf_format.has_fill = False
1113 else:
1114 # This is a new fill.
1115 fills[key] = index
1116 xf_format.fill_index = index
1117 xf_format.has_fill = True
1118 index += 1
1120 self.fill_count = index
1122 def _has_feature_property_bags(self):
1123 # Check for any format properties that require a feature bag. Currently
1124 # this only applies to checkboxes.
1125 if not self.feature_property_bags:
1126 for xf_format in self.formats:
1127 if xf_format.checkbox:
1128 self.feature_property_bags.add("XFComplements")
1130 if xf_format.dxf_index is not None and xf_format.checkbox:
1131 self.feature_property_bags.add("DXFComplements")
1133 return self.feature_property_bags
1135 def _prepare_defined_names(self):
1136 # Iterate through the worksheets and store any defined names in
1137 # addition to any user defined names. Stores the defined names
1138 # for the Workbook.xml and the named ranges for App.xml.
1139 defined_names = self.defined_names
1141 for sheet in self.worksheets():
1142 # Check for Print Area settings.
1143 if sheet.autofilter_area:
1144 hidden = 1
1145 sheet_range = sheet.autofilter_area
1146 # Store the defined names.
1147 defined_names.append(
1148 ["_xlnm._FilterDatabase", sheet.index, sheet_range, hidden]
1149 )
1151 # Check for Print Area settings.
1152 if sheet.print_area_range:
1153 hidden = 0
1154 sheet_range = sheet.print_area_range
1155 # Store the defined names.
1156 defined_names.append(
1157 ["_xlnm.Print_Area", sheet.index, sheet_range, hidden]
1158 )
1160 # Check for repeat rows/cols referred to as Print Titles.
1161 if sheet.repeat_col_range or sheet.repeat_row_range:
1162 hidden = 0
1163 sheet_range = ""
1164 if sheet.repeat_col_range and sheet.repeat_row_range:
1165 sheet_range = sheet.repeat_col_range + "," + sheet.repeat_row_range
1166 else:
1167 sheet_range = sheet.repeat_col_range + sheet.repeat_row_range
1168 # Store the defined names.
1169 defined_names.append(
1170 ["_xlnm.Print_Titles", sheet.index, sheet_range, hidden]
1171 )
1173 defined_names = self._sort_defined_names(defined_names)
1174 self.defined_names = defined_names
1175 self.named_ranges = self._extract_named_ranges(defined_names)
1177 def _sort_defined_names(self, names):
1178 # Sort the list of list of internal and user defined names in
1179 # the same order as used by Excel.
1181 # Add a normalize name string to each list for sorting.
1182 for name_list in names:
1183 (defined_name, _, sheet_name, _) = name_list
1185 # Normalize the defined name by removing any leading '_xmln.'
1186 # from internal names and lowercasing the string.
1187 defined_name = defined_name.replace("_xlnm.", "").lower()
1189 # Normalize the sheetname by removing the leading quote and
1190 # lowercasing the string.
1191 sheet_name = sheet_name.lstrip("'").lower()
1193 name_list.append(defined_name + "::" + sheet_name)
1195 # Sort based on the normalized key.
1196 names.sort(key=operator.itemgetter(4))
1198 # Remove the extra key used for sorting.
1199 for name_list in names:
1200 name_list.pop()
1202 return names
1204 def _prepare_drawings(self):
1205 # Iterate through the worksheets and set up chart and image drawings.
1206 chart_ref_id = 0
1207 ref_id = 0
1208 drawing_id = 0
1209 image_ids = {}
1210 header_image_ids = {}
1211 background_ids = {}
1213 # Store the image types for any embedded images.
1214 for image in self.embedded_images.images:
1215 image_extension = image._image_extension
1216 self.image_types[image_extension] = True
1218 if image.description is not None:
1219 self.has_embedded_descriptions = True
1221 image_ref_id = len(self.embedded_images.images)
1223 for sheet in self.worksheets():
1224 chart_count = len(sheet.charts)
1225 image_count = len(sheet.images)
1226 shape_count = len(sheet.shapes)
1228 header_image_count = len(sheet.header_images)
1229 footer_image_count = len(sheet.footer_images)
1230 has_background = sheet.background_image
1231 has_drawing = False
1233 if not (
1234 chart_count
1235 or image_count
1236 or shape_count
1237 or header_image_count
1238 or footer_image_count
1239 or has_background
1240 ):
1241 continue
1243 # Don't increase the drawing_id header/footer images.
1244 if chart_count or image_count or shape_count:
1245 drawing_id += 1
1246 has_drawing = True
1248 # Prepare the background images.
1249 if sheet.background_image:
1250 image = sheet.background_image
1251 image_extension = image._image_extension
1252 image_digest = image._digest
1254 self.image_types[image_extension] = True
1256 if image_digest in background_ids:
1257 ref_id = background_ids[image_digest]
1258 else:
1259 image_ref_id += 1
1260 ref_id = image_ref_id
1261 background_ids[image_digest] = image_ref_id
1262 self.images.append(image)
1264 sheet._prepare_background(ref_id, image_extension)
1266 # Prepare the worksheet images.
1267 for index in range(image_count):
1268 image = sheet.images[index]
1269 image_extension = image._image_extension
1270 image_digest = image._digest
1272 self.image_types[image_extension] = True
1274 if image_digest in image_ids:
1275 ref_id = image_ids[image_digest]
1276 else:
1277 image_ref_id += 1
1278 ref_id = image_ref_id
1279 image_ids[image_digest] = image_ref_id
1280 self.images.append(image)
1282 sheet._prepare_image(
1283 image,
1284 ref_id,
1285 drawing_id,
1286 )
1288 # Prepare the worksheet charts.
1289 for index in range(chart_count):
1290 chart_ref_id += 1
1291 sheet._prepare_chart(index, chart_ref_id, drawing_id)
1293 # Prepare the worksheet shapes.
1294 for index in range(shape_count):
1295 sheet._prepare_shape(index, drawing_id)
1297 # Prepare the header images.
1298 for image in sheet.header_images:
1299 image_extension = image._image_extension
1300 image_digest = image._digest
1302 self.image_types[image_extension] = True
1304 if image_digest in header_image_ids:
1305 ref_id = header_image_ids[image_digest]
1306 else:
1307 image_ref_id += 1
1308 ref_id = image_ref_id
1309 header_image_ids[image_digest] = image_ref_id
1310 self.images.append(image)
1312 sheet._prepare_header_image(ref_id, image)
1314 # Prepare the footer images.
1315 for image in sheet.footer_images:
1316 image_extension = image._image_extension
1317 image_digest = image._digest
1319 self.image_types[image_extension] = True
1321 if image_digest in header_image_ids:
1322 ref_id = header_image_ids[image_digest]
1323 else:
1324 image_ref_id += 1
1325 ref_id = image_ref_id
1326 header_image_ids[image_digest] = image_ref_id
1327 self.images.append(image)
1329 sheet._prepare_header_image(ref_id, image)
1331 if has_drawing:
1332 drawing = sheet.drawing
1333 self.drawings.append(drawing)
1335 # Remove charts that were created but not inserted into worksheets.
1336 for chart in self.charts[:]:
1337 if chart.id == -1:
1338 self.charts.remove(chart)
1340 # Sort the workbook charts references into the order that the were
1341 # written to the worksheets above.
1342 self.charts = sorted(self.charts, key=lambda chart: chart.id)
1344 self.drawing_count = drawing_id
1346 def _extract_named_ranges(self, defined_names):
1347 # Extract the named ranges from the sorted list of defined names.
1348 # These are used in the App.xml file.
1349 named_ranges = []
1351 for defined_name in defined_names:
1352 name = defined_name[0]
1353 index = defined_name[1]
1354 sheet_range = defined_name[2]
1356 # Skip autoFilter ranges.
1357 if name == "_xlnm._FilterDatabase":
1358 continue
1360 # We are only interested in defined names with ranges.
1361 if "!" in sheet_range:
1362 sheet_name, _ = sheet_range.split("!", 1)
1364 # Match Print_Area and Print_Titles xlnm types.
1365 if name.startswith("_xlnm."):
1366 xlnm_type = name.replace("_xlnm.", "")
1367 name = sheet_name + "!" + xlnm_type
1368 elif index != -1:
1369 name = sheet_name + "!" + name
1371 named_ranges.append(name)
1373 return named_ranges
1375 def _get_sheet_index(self, sheetname):
1376 # Convert a sheet name to its index. Return None otherwise.
1377 sheetname = sheetname.strip("'")
1379 if sheetname in self.sheetnames:
1380 return self.sheetnames[sheetname].index
1382 return None
1384 def _prepare_vml(self):
1385 # Iterate through the worksheets and set up the VML objects.
1386 comment_id = 0
1387 vml_drawing_id = 0
1388 vml_data_id = 1
1389 vml_header_id = 0
1390 vml_shape_id = 1024
1391 vml_files = 0
1392 comment_files = 0
1394 for sheet in self.worksheets():
1395 if not sheet.has_vml and not sheet.has_header_vml:
1396 continue
1398 vml_files += 1
1400 if sheet.has_vml:
1401 if sheet.has_comments:
1402 comment_files += 1
1403 comment_id += 1
1404 self.has_comments = True
1406 vml_drawing_id += 1
1408 count = sheet._prepare_vml_objects(
1409 vml_data_id, vml_shape_id, vml_drawing_id, comment_id
1410 )
1412 # Each VML should start with a shape id incremented by 1024.
1413 vml_data_id += 1 * int((1024 + count) / 1024)
1414 vml_shape_id += 1024 * int((1024 + count) / 1024)
1416 if sheet.has_header_vml:
1417 vml_header_id += 1
1418 vml_drawing_id += 1
1419 sheet._prepare_header_vml_objects(vml_header_id, vml_drawing_id)
1421 self.num_vml_files = vml_files
1422 self.num_comment_files = comment_files
1424 def _prepare_tables(self):
1425 # Set the table ids for the worksheet tables.
1426 table_id = 0
1427 seen = {}
1429 for sheet in self.worksheets():
1430 table_count = len(sheet.tables)
1432 if not table_count:
1433 continue
1435 sheet._prepare_tables(table_id + 1, seen)
1436 table_id += table_count
1438 def _prepare_metadata(self):
1439 # Set the metadata rel link.
1440 self.has_embedded_images = self.embedded_images.has_images()
1441 self.has_metadata = self.has_embedded_images
1443 for sheet in self.worksheets():
1444 if sheet.has_dynamic_arrays:
1445 self.has_metadata = True
1446 self.has_dynamic_functions = True
1448 def _add_chart_data(self):
1449 # Add "cached" data to charts to provide the numCache and strCache
1450 # data for series and title/axis ranges.
1451 worksheets = {}
1452 seen_ranges = {}
1453 charts = []
1455 # Map worksheet names to worksheet objects.
1456 for worksheet in self.worksheets():
1457 worksheets[worksheet.name] = worksheet
1459 # Build a list of the worksheet charts including any combined charts.
1460 for chart in self.charts:
1461 charts.append(chart)
1462 if chart.combined:
1463 charts.append(chart.combined)
1465 for chart in charts:
1466 for c_range in chart.formula_ids.keys():
1467 r_id = chart.formula_ids[c_range]
1469 # Skip if the series has user defined data.
1470 if chart.formula_data[r_id] is not None:
1471 if c_range not in seen_ranges or seen_ranges[c_range] is None:
1472 data = chart.formula_data[r_id]
1473 seen_ranges[c_range] = data
1474 continue
1476 # Check to see if the data is already cached locally.
1477 if c_range in seen_ranges:
1478 chart.formula_data[r_id] = seen_ranges[c_range]
1479 continue
1481 # Convert the range formula to a sheet name and cell range.
1482 (sheetname, cells) = self._get_chart_range(c_range)
1484 # Skip if we couldn't parse the formula.
1485 if sheetname is None:
1486 continue
1488 # Handle non-contiguous ranges like:
1489 # (Sheet1!$A$1:$A$2,Sheet1!$A$4:$A$5).
1490 # We don't try to parse them. We just return an empty list.
1491 if sheetname.startswith("("):
1492 chart.formula_data[r_id] = []
1493 seen_ranges[c_range] = []
1494 continue
1496 # Warn if the name is unknown since it indicates a user error
1497 # in a chart series formula.
1498 if sheetname not in worksheets:
1499 warn(
1500 f"Unknown worksheet reference '{sheetname}' in range "
1501 f"'{c_range}' passed to add_series()"
1502 )
1503 chart.formula_data[r_id] = []
1504 seen_ranges[c_range] = []
1505 continue
1507 # Find the worksheet object based on the sheet name.
1508 worksheet = worksheets[sheetname]
1510 # Get the data from the worksheet table.
1511 data = worksheet._get_range_data(*cells)
1513 # Add the data to the chart.
1514 chart.formula_data[r_id] = data
1516 # Store range data locally to avoid lookup if seen again.
1517 seen_ranges[c_range] = data
1519 def _get_chart_range(self, c_range):
1520 # Convert a range formula such as Sheet1!$B$1:$B$5 into a sheet name
1521 # and cell range such as ( 'Sheet1', 0, 1, 4, 1 ).
1523 # Split the range formula into sheetname and cells at the last '!'.
1524 pos = c_range.rfind("!")
1525 if pos > 0:
1526 sheetname = c_range[:pos]
1527 cells = c_range[pos + 1 :]
1528 else:
1529 return None, None
1531 # Split the cell range into 2 cells or else use single cell for both.
1532 if cells.find(":") > 0:
1533 (cell_1, cell_2) = cells.split(":", 1)
1534 else:
1535 (cell_1, cell_2) = (cells, cells)
1537 # Remove leading/trailing quotes and convert escaped quotes to single.
1538 sheetname = sheetname.strip("'")
1539 sheetname = sheetname.replace("''", "'")
1541 try:
1542 # Get the row, col values from the Excel ranges. We do this in a
1543 # try block for ranges that can't be parsed such as defined names.
1544 (row_start, col_start) = xl_cell_to_rowcol(cell_1)
1545 (row_end, col_end) = xl_cell_to_rowcol(cell_2)
1546 except AttributeError:
1547 return None, None
1549 # We only handle 1D ranges.
1550 if row_start != row_end and col_start != col_end:
1551 return None, None
1553 return sheetname, [row_start, col_start, row_end, col_end]
1555 def _prepare_sst_string_data(self):
1556 # Convert the SST string data from a dict to a list.
1557 self.str_table._sort_string_data()
1559 def _get_packager(self):
1560 # Get and instance of the Packager class to create the xlsx package.
1561 # This allows the default packager to be over-ridden.
1562 return Packager()
1564 ###########################################################################
1565 #
1566 # XML methods.
1567 #
1568 ###########################################################################
1570 def _write_workbook(self):
1571 # Write <workbook> element.
1573 schema = "http://schemas.openxmlformats.org"
1574 xmlns = schema + "/spreadsheetml/2006/main"
1575 xmlns_r = schema + "/officeDocument/2006/relationships"
1577 attributes = [
1578 ("xmlns", xmlns),
1579 ("xmlns:r", xmlns_r),
1580 ]
1582 self._xml_start_tag("workbook", attributes)
1584 def _write_file_version(self):
1585 # Write the <fileVersion> element.
1587 app_name = "xl"
1588 last_edited = 4
1589 lowest_edited = 4
1590 rup_build = 4505
1592 attributes = [
1593 ("appName", app_name),
1594 ("lastEdited", last_edited),
1595 ("lowestEdited", lowest_edited),
1596 ("rupBuild", rup_build),
1597 ]
1599 if self.vba_project:
1600 attributes.append(("codeName", "{37E998C4-C9E5-D4B9-71C8-EB1FF731991C}"))
1602 self._xml_empty_tag("fileVersion", attributes)
1604 def _write_file_sharing(self):
1605 # Write the <fileSharing> element.
1606 if self.read_only == 0:
1607 return
1609 attributes = [("readOnlyRecommended", 1)]
1611 self._xml_empty_tag("fileSharing", attributes)
1613 def _write_workbook_pr(self):
1614 # Write <workbookPr> element.
1615 default_theme_version = 124226
1616 attributes = []
1618 if self.vba_codename:
1619 attributes.append(("codeName", self.vba_codename))
1620 if self.date_1904:
1621 attributes.append(("date1904", 1))
1623 attributes.append(("defaultThemeVersion", default_theme_version))
1625 self._xml_empty_tag("workbookPr", attributes)
1627 def _write_book_views(self):
1628 # Write <bookViews> element.
1629 self._xml_start_tag("bookViews")
1630 self._write_workbook_view()
1631 self._xml_end_tag("bookViews")
1633 def _write_workbook_view(self):
1634 # Write <workbookView> element.
1635 attributes = [
1636 ("xWindow", self.x_window),
1637 ("yWindow", self.y_window),
1638 ("windowWidth", self.window_width),
1639 ("windowHeight", self.window_height),
1640 ]
1642 # Store the tabRatio attribute when it isn't the default.
1643 if self.tab_ratio != 600:
1644 attributes.append(("tabRatio", self.tab_ratio))
1646 # Store the firstSheet attribute when it isn't the default.
1647 if self.worksheet_meta.firstsheet > 0:
1648 firstsheet = self.worksheet_meta.firstsheet + 1
1649 attributes.append(("firstSheet", firstsheet))
1651 # Store the activeTab attribute when it isn't the first sheet.
1652 if self.worksheet_meta.activesheet > 0:
1653 attributes.append(("activeTab", self.worksheet_meta.activesheet))
1655 self._xml_empty_tag("workbookView", attributes)
1657 def _write_sheets(self):
1658 # Write <sheets> element.
1659 self._xml_start_tag("sheets")
1661 id_num = 1
1662 for worksheet in self.worksheets():
1663 self._write_sheet(worksheet.name, id_num, worksheet.hidden)
1664 id_num += 1
1666 self._xml_end_tag("sheets")
1668 def _write_sheet(self, name, sheet_id, hidden):
1669 # Write <sheet> element.
1670 attributes = [
1671 ("name", name),
1672 ("sheetId", sheet_id),
1673 ]
1675 if hidden == 1:
1676 attributes.append(("state", "hidden"))
1677 elif hidden == 2:
1678 attributes.append(("state", "veryHidden"))
1680 attributes.append(("r:id", "rId" + str(sheet_id)))
1682 self._xml_empty_tag("sheet", attributes)
1684 def _write_calc_pr(self):
1685 # Write the <calcPr> element.
1686 attributes = [("calcId", self.calc_id)]
1688 if self.calc_mode == "manual":
1689 attributes.append(("calcMode", self.calc_mode))
1690 attributes.append(("calcOnSave", "0"))
1691 elif self.calc_mode == "autoNoTable":
1692 attributes.append(("calcMode", self.calc_mode))
1694 if self.calc_on_load:
1695 attributes.append(("fullCalcOnLoad", "1"))
1697 self._xml_empty_tag("calcPr", attributes)
1699 def _write_defined_names(self):
1700 # Write the <definedNames> element.
1701 if not self.defined_names:
1702 return
1704 self._xml_start_tag("definedNames")
1706 for defined_name in self.defined_names:
1707 self._write_defined_name(defined_name)
1709 self._xml_end_tag("definedNames")
1711 def _write_defined_name(self, defined_name):
1712 # Write the <definedName> element.
1713 name = defined_name[0]
1714 sheet_id = defined_name[1]
1715 sheet_range = defined_name[2]
1716 hidden = defined_name[3]
1718 attributes = [("name", name)]
1720 if sheet_id != -1:
1721 attributes.append(("localSheetId", sheet_id))
1722 if hidden:
1723 attributes.append(("hidden", 1))
1725 self._xml_data_element("definedName", sheet_range, attributes)
1728# A metadata class to share data between worksheets.
1729class WorksheetMeta:
1730 """
1731 A class to track worksheets data such as the active sheet and the
1732 first sheet.
1734 """
1736 def __init__(self):
1737 self.activesheet = 0
1738 self.firstsheet = 0
1741# A helper class to share embedded images between worksheets.
1742class EmbeddedImages:
1743 """
1744 A class to track duplicate embedded images between worksheets.
1746 """
1748 def __init__(self):
1749 self.images = []
1750 self.image_indexes = {}
1752 def get_image_index(self, image: Image):
1753 """
1754 Get the index of an embedded image.
1756 Args:
1757 image: The image to lookup.
1759 Returns:
1760 The image index.
1762 """
1763 image_index = self.image_indexes.get(image._digest)
1765 if image_index is None:
1766 self.images.append(image)
1767 image_index = len(self.images)
1768 self.image_indexes[image._digest] = image_index
1770 return image_index
1772 def has_images(self):
1773 """
1774 Check if the worksheet has embedded images.
1776 Args:
1777 None.
1779 Returns:
1780 Boolean.
1782 """
1783 return len(self.images) > 0