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 typing import IO, Any, AnyStr, Dict, List, Literal, Optional, Union
19from warnings import warn
20from zipfile import ZIP_DEFLATED, LargeZipFile, ZipFile, ZipInfo
22from xlsxwriter.image import Image
24# Package imports.
25from . import xmlwriter
26from .chart_area import ChartArea
27from .chart_bar import ChartBar
28from .chart_column import ChartColumn
29from .chart_doughnut import ChartDoughnut
30from .chart_line import ChartLine
31from .chart_pie import ChartPie
32from .chart_radar import ChartRadar
33from .chart_scatter import ChartScatter
34from .chart_stock import ChartStock
35from .chartsheet import Chartsheet
36from .exceptions import (
37 DuplicateWorksheetName,
38 FileCreateError,
39 FileSizeError,
40 InvalidWorksheetName,
41)
42from .format import Format
43from .packager import Packager
44from .sharedstrings import SharedStringTable
45from .utility import xl_cell_to_rowcol
46from .worksheet import Worksheet
49class Workbook(xmlwriter.XMLwriter):
50 """
51 A class for writing the Excel XLSX Workbook file.
54 """
56 ###########################################################################
57 #
58 # Public API.
59 #
60 ###########################################################################
61 chartsheet_class = Chartsheet
62 worksheet_class = Worksheet
64 def __init__(
65 self,
66 filename: Optional[Union[str, IO[AnyStr], os.PathLike]] = None,
67 options: Optional[Dict[str, Any]] = None,
68 ) -> None:
69 """
70 Constructor.
72 """
73 if options is None:
74 options = {}
76 super().__init__()
78 self.filename = filename
80 self.tmpdir = options.get("tmpdir", None)
81 self.date_1904 = options.get("date_1904", False)
82 self.strings_to_numbers = options.get("strings_to_numbers", False)
83 self.strings_to_formulas = options.get("strings_to_formulas", True)
84 self.strings_to_urls = options.get("strings_to_urls", True)
85 self.nan_inf_to_errors = options.get("nan_inf_to_errors", False)
86 self.default_date_format = options.get("default_date_format", None)
87 self.constant_memory = options.get("constant_memory", False)
88 self.in_memory = options.get("in_memory", False)
89 self.excel2003_style = options.get("excel2003_style", False)
90 self.remove_timezone = options.get("remove_timezone", False)
91 self.use_future_functions = options.get("use_future_functions", False)
92 self.default_format_properties = options.get("default_format_properties", {})
94 self.max_url_length = options.get("max_url_length", 2079)
95 if self.max_url_length < 255:
96 self.max_url_length = 2079
98 if options.get("use_zip64"):
99 self.allow_zip64 = True
100 else:
101 self.allow_zip64 = False
103 self.worksheet_meta = WorksheetMeta()
104 self.selected = 0
105 self.fileclosed = 0
106 self.filehandle = None
107 self.internal_fh = 0
108 self.sheet_name = "Sheet"
109 self.chart_name = "Chart"
110 self.sheetname_count = 0
111 self.chartname_count = 0
112 self.worksheets_objs = []
113 self.charts = []
114 self.drawings = []
115 self.sheetnames = {}
116 self.formats = []
117 self.xf_formats = []
118 self.xf_format_indices = {}
119 self.dxf_formats = []
120 self.dxf_format_indices = {}
121 self.palette = []
122 self.font_count = 0
123 self.num_formats = []
124 self.defined_names = []
125 self.named_ranges = []
126 self.custom_colors = []
127 self.doc_properties = {}
128 self.custom_properties = []
129 self.createtime = datetime.now(timezone.utc)
130 self.num_vml_files = 0
131 self.num_comment_files = 0
132 self.x_window = 240
133 self.y_window = 15
134 self.window_width = 16095
135 self.window_height = 9660
136 self.tab_ratio = 600
137 self.str_table = SharedStringTable()
138 self.vba_project = None
139 self.vba_project_is_stream = False
140 self.vba_project_signature = None
141 self.vba_project_signature_is_stream = False
142 self.vba_codename = None
143 self.image_types = {}
144 self.images = []
145 self.border_count = 0
146 self.fill_count = 0
147 self.drawing_count = 0
148 self.calc_mode = "auto"
149 self.calc_on_load = True
150 self.calc_id = 124519
151 self.has_comments = False
152 self.read_only = 0
153 self.has_metadata = False
154 self.has_embedded_images = False
155 self.has_dynamic_functions = False
156 self.has_embedded_descriptions = False
157 self.embedded_images = EmbeddedImages()
158 self.feature_property_bags = set()
160 # We can't do 'constant_memory' mode while doing 'in_memory' mode.
161 if self.in_memory:
162 self.constant_memory = False
164 # Add the default cell format.
165 if self.excel2003_style:
166 self.add_format({"xf_index": 0, "font_family": 0})
167 else:
168 self.add_format({"xf_index": 0})
170 # Add a default URL format.
171 self.default_url_format = self.add_format({"hyperlink": True})
173 # Add the default date format.
174 if self.default_date_format is not None:
175 self.default_date_format = self.add_format(
176 {"num_format": self.default_date_format}
177 )
179 def __enter__(self):
180 """Return self object to use with "with" statement."""
181 return self
183 def __exit__(self, type, value, traceback) -> None:
184 # pylint: disable=redefined-builtin
185 """Close workbook when exiting "with" statement."""
186 self.close()
188 def add_worksheet(
189 self, name: Optional[str] = None, worksheet_class=None
190 ) -> Worksheet:
191 """
192 Add a new worksheet to the Excel workbook.
194 Args:
195 name: The worksheet name. Defaults to 'Sheet1', etc.
197 Returns:
198 Reference to a worksheet object.
200 """
201 if worksheet_class is None:
202 worksheet_class = self.worksheet_class
204 return self._add_sheet(name, worksheet_class=worksheet_class)
206 def add_chartsheet(
207 self, name: Optional[str] = None, chartsheet_class=None
208 ) -> Chartsheet:
209 """
210 Add a new chartsheet to the Excel workbook.
212 Args:
213 name: The chartsheet name. Defaults to 'Sheet1', etc.
215 Returns:
216 Reference to a chartsheet object.
218 """
219 if chartsheet_class is None:
220 chartsheet_class = self.chartsheet_class
222 return self._add_sheet(name, worksheet_class=chartsheet_class)
224 def add_format(self, properties=None) -> Format:
225 """
226 Add a new Format to the Excel Workbook.
228 Args:
229 properties: The format properties.
231 Returns:
232 Reference to a Format object.
234 """
235 format_properties = self.default_format_properties.copy()
237 if self.excel2003_style:
238 format_properties = {"font_name": "Arial", "font_size": 10, "theme": 1 * -1}
240 if properties:
241 format_properties.update(properties)
243 xf_format = Format(
244 format_properties, self.xf_format_indices, self.dxf_format_indices
245 )
247 # Store the format reference.
248 self.formats.append(xf_format)
250 return xf_format
252 def add_chart(self, options: Dict[str, Any]) -> Optional[
253 Union[
254 ChartArea,
255 ChartBar,
256 ChartColumn,
257 ChartDoughnut,
258 ChartLine,
259 ChartPie,
260 ChartRadar,
261 ChartScatter,
262 ChartStock,
263 ]
264 ]:
265 """
266 Create a chart object.
268 Args:
269 options: The chart type and subtype options.
271 Returns:
272 Reference to a Chart object.
274 """
276 # Type must be specified so we can create the required chart instance.
277 chart_type = options.get("type")
278 if chart_type is None:
279 warn("Chart type must be defined in add_chart()")
280 return None
282 if chart_type == "area":
283 chart = ChartArea(options)
284 elif chart_type == "bar":
285 chart = ChartBar(options)
286 elif chart_type == "column":
287 chart = ChartColumn(options)
288 elif chart_type == "doughnut":
289 chart = ChartDoughnut()
290 elif chart_type == "line":
291 chart = ChartLine(options)
292 elif chart_type == "pie":
293 chart = ChartPie()
294 elif chart_type == "radar":
295 chart = ChartRadar(options)
296 elif chart_type == "scatter":
297 chart = ChartScatter(options)
298 elif chart_type == "stock":
299 chart = ChartStock()
300 else:
301 warn(f"Unknown chart type '{chart_type}' in add_chart()")
302 return None
304 # Set the embedded chart name if present.
305 if "name" in options:
306 chart.chart_name = options["name"]
308 chart.embedded = True
309 chart.date_1904 = self.date_1904
310 chart.remove_timezone = self.remove_timezone
312 self.charts.append(chart)
314 return chart
316 def add_vba_project(self, vba_project: str, is_stream: bool = False) -> int:
317 """
318 Add a vbaProject binary to the Excel workbook.
320 Args:
321 vba_project: The vbaProject binary file name.
322 is_stream: vba_project is an in memory byte stream.
324 Returns:
325 0 on success.
327 """
328 if not is_stream and not os.path.exists(vba_project):
329 warn(f"VBA project binary file '{vba_project}' not found.")
330 return -1
332 if self.vba_codename is None:
333 self.vba_codename = "ThisWorkbook"
335 self.vba_project = vba_project
336 self.vba_project_is_stream = is_stream
338 return 0
340 def add_signed_vba_project(
341 self,
342 vba_project: str,
343 signature: str,
344 project_is_stream: bool = False,
345 signature_is_stream: bool = False,
346 ) -> Literal[0, -1]:
347 """
348 Add a vbaProject binary and a vbaProjectSignature binary to the
349 Excel workbook.
351 Args:
352 vba_project: The vbaProject binary file name.
353 signature: The vbaProjectSignature binary file name.
354 project_is_stream: vba_project is an in memory byte stream.
355 signature_is_stream: signature is an in memory byte stream.
357 Returns:
358 0 on success.
360 """
361 if self.add_vba_project(vba_project, project_is_stream) == -1:
362 return -1
364 if not signature_is_stream and not os.path.exists(signature):
365 warn(f"VBA project signature binary file '{signature}' not found.")
366 return -1
368 self.vba_project_signature = signature
369 self.vba_project_signature_is_stream = signature_is_stream
371 return 0
373 def close(self) -> None:
374 """
375 Call finalization code and close file.
377 Args:
378 None.
380 Returns:
381 Nothing.
383 """
384 # pylint: disable=raise-missing-from
385 if not self.fileclosed:
386 try:
387 self._store_workbook()
388 except IOError as e:
389 raise FileCreateError(e)
390 except LargeZipFile:
391 raise FileSizeError(
392 "Filesize would require ZIP64 extensions. "
393 "Use workbook.use_zip64()."
394 )
396 self.fileclosed = True
398 # Ensure all constant_memory temp files are closed.
399 if self.constant_memory:
400 for worksheet in self.worksheets():
401 worksheet._opt_close()
403 else:
404 warn("Calling close() on already closed file.")
406 def set_size(self, width: int, height: int) -> None:
407 """
408 Set the size of a workbook window.
410 Args:
411 width: Width of the window in pixels.
412 height: Height of the window in pixels.
414 Returns:
415 Nothing.
417 """
418 # Convert the width/height to twips at 96 dpi.
419 if width:
420 self.window_width = int(width * 1440 / 96)
421 else:
422 self.window_width = 16095
424 if height:
425 self.window_height = int(height * 1440 / 96)
426 else:
427 self.window_height = 9660
429 def set_tab_ratio(self, tab_ratio: Optional[Union[int, float]] = None) -> None:
430 """
431 Set the ratio between worksheet tabs and the horizontal slider.
433 Args:
434 tab_ratio: The tab ratio, 0 <= tab_ratio <= 100
436 Returns:
437 Nothing.
439 """
440 if tab_ratio is None:
441 return
443 if tab_ratio < 0 or tab_ratio > 100:
444 warn(f"Tab ratio '{tab_ratio}' outside: 0 <= tab_ratio <= 100")
445 else:
446 self.tab_ratio = int(tab_ratio * 10)
448 def set_properties(self, properties) -> None:
449 """
450 Set the document properties such as Title, Author etc.
452 Args:
453 properties: Dictionary of document properties.
455 Returns:
456 Nothing.
458 """
459 self.doc_properties = properties
461 def set_custom_property(
462 self,
463 name: str,
464 value: Union[bool, datetime, int, float, Decimal, Fraction, Any],
465 property_type: Optional[
466 Literal["bool", "date", "number", "number_int", "text"]
467 ] = None,
468 ) -> Literal[0, -1]:
469 """
470 Set a custom document property.
472 Args:
473 name: The name of the custom property.
474 value: The value of the custom property.
475 property_type: The type of the custom property. Optional.
477 Returns:
478 0 on success.
480 """
481 if name is None or value is None:
482 warn(
483 "The name and value parameters must be non-None in "
484 "set_custom_property()"
485 )
486 return -1
488 if property_type is None:
489 # Determine the property type from the Python type.
490 if isinstance(value, bool):
491 property_type = "bool"
492 elif isinstance(value, datetime):
493 property_type = "date"
494 elif isinstance(value, int):
495 property_type = "number_int"
496 elif isinstance(value, (float, int, Decimal, Fraction)):
497 property_type = "number"
498 else:
499 property_type = "text"
501 # Convert non-string values to strings to have a single data type.
502 if property_type == "bool":
503 value = str(value).lower()
505 if property_type == "date":
506 value = value.strftime("%Y-%m-%dT%H:%M:%SZ")
508 if property_type in ("number", "number_int"):
509 value = str(value)
511 if property_type == "text" and len(value) > 255:
512 warn(
513 f"Length of 'value' parameter exceeds Excel's limit of 255 "
514 f"characters in set_custom_property(): '{value}'"
515 )
517 if len(name) > 255:
518 warn(
519 f"Length of 'name' parameter exceeds Excel's limit of 255 "
520 f"characters in set_custom_property(): '{name}'"
521 )
523 self.custom_properties.append((name, value, property_type))
525 return 0
527 def set_calc_mode(
528 self, mode: Literal["manual", "auto_except_tables", "auto"], calc_id=None
529 ) -> None:
530 """
531 Set the Excel calculation mode for the workbook.
533 Args:
534 mode: String containing one of:
535 * manual
536 * auto_except_tables
537 * auto
539 Returns:
540 Nothing.
542 """
543 self.calc_mode = mode
545 if mode == "manual":
546 self.calc_on_load = False
547 elif mode == "auto_except_tables":
548 self.calc_mode = "autoNoTable"
550 # Leave undocumented for now. Rarely required.
551 if calc_id:
552 self.calc_id = calc_id
554 def define_name(self, name: str, formula: str) -> Literal[0, -1]:
555 # Create a defined name in Excel. We handle global/workbook level
556 # names and local/worksheet names.
557 """
558 Create a defined name in the workbook.
560 Args:
561 name: The defined name.
562 formula: The cell or range that the defined name refers to.
564 Returns:
565 0 on success.
567 """
568 sheet_index = None
569 sheetname = ""
571 # Remove the = sign from the formula if it exists.
572 if formula.startswith("="):
573 formula = formula.lstrip("=")
575 # Local defined names are formatted like "Sheet1!name".
576 sheet_parts = re.compile(r"^([^!]+)!([^!]+)$")
577 match = sheet_parts.match(name)
579 if match:
580 sheetname = match.group(1)
581 name = match.group(2)
582 sheet_index = self._get_sheet_index(sheetname)
584 # Warn if the sheet index wasn't found.
585 if sheet_index is None:
586 warn(f"Unknown sheet name '{sheetname}' in defined_name()")
587 return -1
588 else:
589 # Use -1 to indicate global names.
590 sheet_index = -1
592 # Warn if the defined name contains invalid chars as defined by Excel.
593 if not re.match(r"^[\w\\][\w\\.]*$", name, re.UNICODE) or re.match(
594 r"^\d", name
595 ):
596 warn(f"Invalid Excel characters in defined_name(): '{name}'")
597 return -1
599 # Warn if the defined name looks like a cell name.
600 if re.match(r"^[a-zA-Z][a-zA-Z]?[a-dA-D]?\d+$", name):
601 warn(f"Name looks like a cell name in defined_name(): '{name}'")
602 return -1
604 # Warn if the name looks like a R1C1 cell reference.
605 if re.match(r"^[rcRC]$", name) or re.match(r"^[rcRC]\d+[rcRC]\d+$", name):
606 warn(f"Invalid name '{name}' like a RC cell ref in defined_name()")
607 return -1
609 self.defined_names.append([name, sheet_index, formula, False])
611 return 0
613 def worksheets(self) -> List[Worksheet]:
614 """
615 Return a list of the worksheet objects in the workbook.
617 Args:
618 None.
620 Returns:
621 A list of worksheet objects.
623 """
624 return self.worksheets_objs
626 def get_worksheet_by_name(self, name: str) -> Optional[Worksheet]:
627 """
628 Return a worksheet object in the workbook using the sheetname.
630 Args:
631 name: The name of the worksheet.
633 Returns:
634 A worksheet object or None.
636 """
637 return self.sheetnames.get(name)
639 def get_default_url_format(self) -> Format:
640 """
641 Get the default url format used when a user defined format isn't
642 specified with write_url(). The format is the hyperlink style defined
643 by Excel for the default theme.
645 Args:
646 None.
648 Returns:
649 A format object.
651 """
652 return self.default_url_format
654 def use_zip64(self) -> None:
655 """
656 Allow ZIP64 extensions when writing xlsx file zip container.
658 Args:
659 None.
661 Returns:
662 Nothing.
664 """
665 self.allow_zip64 = True
667 def set_vba_name(self, name: Optional[str] = None) -> None:
668 """
669 Set the VBA name for the workbook. By default the workbook is referred
670 to as ThisWorkbook in VBA.
672 Args:
673 name: The VBA name for the workbook.
675 Returns:
676 Nothing.
678 """
679 if name is not None:
680 self.vba_codename = name
681 else:
682 self.vba_codename = "ThisWorkbook"
684 def read_only_recommended(self) -> None:
685 """
686 Set the Excel "Read-only recommended" option when saving a file.
688 Args:
689 None.
691 Returns:
692 Nothing.
694 """
695 self.read_only = 2
697 ###########################################################################
698 #
699 # Private API.
700 #
701 ###########################################################################
703 def _assemble_xml_file(self) -> None:
704 # Assemble and write the XML file.
706 # Prepare format object for passing to Style.pm.
707 self._prepare_format_properties()
709 # Write the XML declaration.
710 self._xml_declaration()
712 # Write the workbook element.
713 self._write_workbook()
715 # Write the fileVersion element.
716 self._write_file_version()
718 # Write the fileSharing element.
719 self._write_file_sharing()
721 # Write the workbookPr element.
722 self._write_workbook_pr()
724 # Write the bookViews element.
725 self._write_book_views()
727 # Write the sheets element.
728 self._write_sheets()
730 # Write the workbook defined names.
731 self._write_defined_names()
733 # Write the calcPr element.
734 self._write_calc_pr()
736 # Close the workbook tag.
737 self._xml_end_tag("workbook")
739 # Close the file.
740 self._xml_close()
742 def _store_workbook(self) -> None:
743 # pylint: disable=consider-using-with
744 # Create the xlsx/zip file.
745 try:
746 xlsx_file = ZipFile(
747 self.filename,
748 "w",
749 compression=ZIP_DEFLATED,
750 allowZip64=self.allow_zip64,
751 )
752 except IOError as e:
753 raise e
755 # Assemble worksheets into a workbook.
756 packager = self._get_packager()
758 # Add a default worksheet if non have been added.
759 if not self.worksheets():
760 self.add_worksheet()
762 # Ensure that at least one worksheet has been selected.
763 if self.worksheet_meta.activesheet == 0:
764 self.worksheets_objs[0].selected = 1
765 self.worksheets_objs[0].hidden = 0
767 # Set the active sheet.
768 for sheet in self.worksheets():
769 if sheet.index == self.worksheet_meta.activesheet:
770 sheet.active = 1
772 # Set the sheet vba_codename the workbook has a vbaProject binary.
773 if self.vba_project:
774 for sheet in self.worksheets():
775 if sheet.vba_codename is None:
776 sheet.set_vba_name()
778 # Convert the SST strings data structure.
779 self._prepare_sst_string_data()
781 # Prepare the worksheet VML elements such as comments and buttons.
782 self._prepare_vml()
784 # Set the defined names for the worksheets such as Print Titles.
785 self._prepare_defined_names()
787 # Prepare the drawings, charts and images.
788 self._prepare_drawings()
790 # Add cached data to charts.
791 self._add_chart_data()
793 # Prepare the worksheet tables.
794 self._prepare_tables()
796 # Prepare the metadata file links.
797 self._prepare_metadata()
799 # Package the workbook.
800 packager._add_workbook(self)
801 packager._set_tmpdir(self.tmpdir)
802 packager._set_in_memory(self.in_memory)
803 xml_files = packager._create_package()
805 # Free up the Packager object.
806 packager = None
808 # Add XML sub-files to the Zip file with their Excel filename.
809 for file_id, file_data in enumerate(xml_files):
810 os_filename, xml_filename, is_binary = file_data
812 if self.in_memory:
813 # Set sub-file timestamp to Excel's timestamp of 1/1/1980.
814 zipinfo = ZipInfo(xml_filename, (1980, 1, 1, 0, 0, 0))
816 # Copy compression type from parent ZipFile.
817 zipinfo.compress_type = xlsx_file.compression
819 if is_binary:
820 xlsx_file.writestr(zipinfo, os_filename.getvalue())
821 else:
822 xlsx_file.writestr(zipinfo, os_filename.getvalue().encode("utf-8"))
823 else:
824 # The sub-files are tempfiles on disk, i.e, not in memory.
826 # Set sub-file timestamp to 31/1/1980 due to portability
827 # issues setting it to Excel's timestamp of 1/1/1980.
828 timestamp = time.mktime((1980, 1, 31, 0, 0, 0, 0, 0, -1))
829 os.utime(os_filename, (timestamp, timestamp))
831 try:
832 xlsx_file.write(os_filename, xml_filename)
833 os.remove(os_filename)
834 except LargeZipFile as e:
835 # Close open temp files on zipfile.LargeZipFile exception.
836 for i in range(file_id, len(xml_files) - 1):
837 os.remove(xml_files[i][0])
838 raise e
840 xlsx_file.close()
842 def _add_sheet(self, name, worksheet_class=None):
843 # Utility for shared code in add_worksheet() and add_chartsheet().
845 if worksheet_class:
846 worksheet = worksheet_class()
847 else:
848 worksheet = self.worksheet_class()
850 sheet_index = len(self.worksheets_objs)
851 name = self._check_sheetname(name, isinstance(worksheet, Chartsheet))
853 # Initialization data to pass to the worksheet.
854 init_data = {
855 "name": name,
856 "index": sheet_index,
857 "str_table": self.str_table,
858 "worksheet_meta": self.worksheet_meta,
859 "constant_memory": self.constant_memory,
860 "tmpdir": self.tmpdir,
861 "date_1904": self.date_1904,
862 "strings_to_numbers": self.strings_to_numbers,
863 "strings_to_formulas": self.strings_to_formulas,
864 "strings_to_urls": self.strings_to_urls,
865 "nan_inf_to_errors": self.nan_inf_to_errors,
866 "default_date_format": self.default_date_format,
867 "default_url_format": self.default_url_format,
868 "workbook_add_format": self.add_format,
869 "excel2003_style": self.excel2003_style,
870 "remove_timezone": self.remove_timezone,
871 "max_url_length": self.max_url_length,
872 "use_future_functions": self.use_future_functions,
873 "embedded_images": self.embedded_images,
874 }
876 worksheet._initialize(init_data)
878 self.worksheets_objs.append(worksheet)
879 self.sheetnames[name] = worksheet
881 return worksheet
883 def _check_sheetname(self, sheetname, is_chartsheet=False):
884 # Check for valid worksheet names. We check the length, if it contains
885 # any invalid chars and if the sheetname is unique in the workbook.
886 invalid_char = re.compile(r"[\[\]:*?/\\]")
888 # Increment the Sheet/Chart number used for default sheet names below.
889 if is_chartsheet:
890 self.chartname_count += 1
891 else:
892 self.sheetname_count += 1
894 # Supply default Sheet/Chart sheetname if none has been defined.
895 if sheetname is None or sheetname == "":
896 if is_chartsheet:
897 sheetname = self.chart_name + str(self.chartname_count)
898 else:
899 sheetname = self.sheet_name + str(self.sheetname_count)
901 # Check that sheet sheetname is <= 31. Excel limit.
902 if len(sheetname) > 31:
903 raise InvalidWorksheetName(
904 f"Excel worksheet name '{sheetname}' must be <= 31 chars."
905 )
907 # Check that sheetname doesn't contain any invalid characters.
908 if invalid_char.search(sheetname):
909 raise InvalidWorksheetName(
910 f"Invalid Excel character '[]:*?/\\' in sheetname '{sheetname}'."
911 )
913 # Check that sheetname doesn't start or end with an apostrophe.
914 if sheetname.startswith("'") or sheetname.endswith("'"):
915 raise InvalidWorksheetName(
916 f'Sheet name cannot start or end with an apostrophe "{sheetname}".'
917 )
919 # Check that the worksheet name doesn't already exist since this is a
920 # fatal Excel error. The check must be case insensitive like Excel.
921 for worksheet in self.worksheets():
922 if sheetname.lower() == worksheet.name.lower():
923 raise DuplicateWorksheetName(
924 f"Sheetname '{sheetname}', with case ignored, is already in use."
925 )
927 return sheetname
929 def _prepare_format_properties(self) -> None:
930 # Prepare all Format properties prior to passing them to styles.py.
932 # Separate format objects into XF and DXF formats.
933 self._prepare_formats()
935 # Set the font index for the format objects.
936 self._prepare_fonts()
938 # Set the number format index for the format objects.
939 self._prepare_num_formats()
941 # Set the border index for the format objects.
942 self._prepare_borders()
944 # Set the fill index for the format objects.
945 self._prepare_fills()
947 def _prepare_formats(self) -> None:
948 # Iterate through the XF Format objects and separate them into
949 # XF and DXF formats. The XF and DF formats then need to be sorted
950 # back into index order rather than creation order.
951 xf_formats = []
952 dxf_formats = []
954 # Sort into XF and DXF formats.
955 for xf_format in self.formats:
956 if xf_format.xf_index is not None:
957 xf_formats.append(xf_format)
959 if xf_format.dxf_index is not None:
960 dxf_formats.append(xf_format)
962 # Pre-extend the format lists.
963 self.xf_formats = [None] * len(xf_formats)
964 self.dxf_formats = [None] * len(dxf_formats)
966 # Rearrange formats into index order.
967 for xf_format in xf_formats:
968 index = xf_format.xf_index
969 self.xf_formats[index] = xf_format
971 for dxf_format in dxf_formats:
972 index = dxf_format.dxf_index
973 self.dxf_formats[index] = dxf_format
975 def _set_default_xf_indices(self) -> None:
976 # Set the default index for each format. Only used for testing.
978 formats = list(self.formats)
980 # Delete the default url format.
981 del formats[1]
983 # Skip the default date format if set.
984 if self.default_date_format is not None:
985 del formats[1]
987 # Set the remaining formats.
988 for xf_format in formats:
989 xf_format._get_xf_index()
991 def _prepare_fonts(self) -> None:
992 # Iterate through the XF Format objects and give them an index to
993 # non-default font elements.
994 fonts = {}
995 index = 0
997 for xf_format in self.xf_formats:
998 key = xf_format._get_font_key()
999 if key in fonts:
1000 # Font has already been used.
1001 xf_format.font_index = fonts[key]
1002 xf_format.has_font = False
1003 else:
1004 # This is a new font.
1005 fonts[key] = index
1006 xf_format.font_index = index
1007 xf_format.has_font = True
1008 index += 1
1010 self.font_count = index
1012 # For DXF formats we only need to check if the properties have changed.
1013 for xf_format in self.dxf_formats:
1014 # The only font properties that can change for a DXF format are:
1015 # color, bold, italic, underline and strikethrough.
1016 if (
1017 xf_format.font_color
1018 or xf_format.bold
1019 or xf_format.italic
1020 or xf_format.underline
1021 or xf_format.font_strikeout
1022 ):
1023 xf_format.has_dxf_font = True
1025 def _prepare_num_formats(self) -> None:
1026 # User defined records in Excel start from index 0xA4.
1027 unique_num_formats = {}
1028 num_formats = []
1029 index = 164
1031 for xf_format in self.xf_formats + self.dxf_formats:
1032 num_format = xf_format.num_format
1034 # Check if num_format is an index to a built-in number format.
1035 if not isinstance(num_format, str):
1036 num_format = int(num_format)
1038 # Number format '0' is indexed as 1 in Excel.
1039 if num_format == 0:
1040 num_format = 1
1042 xf_format.num_format_index = num_format
1043 continue
1045 if num_format == "0":
1046 # Number format '0' is indexed as 1 in Excel.
1047 xf_format.num_format_index = 1
1048 continue
1050 if num_format == "General":
1051 # The 'General' format has an number format index of 0.
1052 xf_format.num_format_index = 0
1053 continue
1055 if num_format in unique_num_formats:
1056 # Number xf_format has already been used.
1057 xf_format.num_format_index = unique_num_formats[num_format]
1058 else:
1059 # Add a new number xf_format.
1060 unique_num_formats[num_format] = index
1061 xf_format.num_format_index = index
1062 index += 1
1064 # Only increase font count for XF formats (not DXF formats).
1065 if xf_format.xf_index:
1066 num_formats.append(num_format)
1068 self.num_formats = num_formats
1070 def _prepare_borders(self) -> None:
1071 # Iterate through the XF Format objects and give them an index to
1072 # non-default border elements.
1073 borders = {}
1074 index = 0
1076 for xf_format in self.xf_formats:
1077 key = xf_format._get_border_key()
1079 if key in borders:
1080 # Border has already been used.
1081 xf_format.border_index = borders[key]
1082 xf_format.has_border = False
1083 else:
1084 # This is a new border.
1085 borders[key] = index
1086 xf_format.border_index = index
1087 xf_format.has_border = True
1088 index += 1
1090 self.border_count = index
1092 # For DXF formats we only need to check if the properties have changed.
1093 has_border = re.compile(r"[^0None:]")
1095 for xf_format in self.dxf_formats:
1096 key = xf_format._get_border_key()
1098 if has_border.search(key):
1099 xf_format.has_dxf_border = True
1101 def _prepare_fills(self) -> None:
1102 # Iterate through the XF Format objects and give them an index to
1103 # non-default fill elements.
1104 # The user defined fill properties start from 2 since there are 2
1105 # default fills: patternType="none" and patternType="gray125".
1106 fills = {}
1107 index = 2 # Start from 2. See above.
1109 # Add the default fills.
1110 fills["0:None:None"] = 0
1111 fills["17:None:None"] = 1
1113 # Store the DXF colors separately since them may be reversed below.
1114 for xf_format in self.dxf_formats:
1115 if xf_format.pattern or xf_format.bg_color or xf_format.fg_color:
1116 xf_format.has_dxf_fill = True
1117 xf_format.dxf_bg_color = xf_format.bg_color
1118 xf_format.dxf_fg_color = xf_format.fg_color
1120 for xf_format in self.xf_formats:
1121 # The following logical statements jointly take care of special
1122 # cases in relation to cell colors and patterns:
1123 # 1. For a solid fill (_pattern == 1) Excel reverses the role of
1124 # foreground and background colors, and
1125 # 2. If the user specifies a foreground or background color
1126 # without a pattern they probably wanted a solid fill, so we fill
1127 # in the defaults.
1128 if xf_format.pattern == 1 and xf_format.bg_color and xf_format.fg_color:
1129 tmp = xf_format.fg_color
1130 xf_format.fg_color = xf_format.bg_color
1131 xf_format.bg_color = tmp
1133 if xf_format.pattern <= 1 and xf_format.bg_color and not xf_format.fg_color:
1134 xf_format.fg_color = xf_format.bg_color
1135 xf_format.bg_color = None
1136 xf_format.pattern = 1
1138 if xf_format.pattern <= 1 and not xf_format.bg_color and xf_format.fg_color:
1139 xf_format.pattern = 1
1141 key = xf_format._get_fill_key()
1143 if key in fills:
1144 # Fill has already been used.
1145 xf_format.fill_index = fills[key]
1146 xf_format.has_fill = False
1147 else:
1148 # This is a new fill.
1149 fills[key] = index
1150 xf_format.fill_index = index
1151 xf_format.has_fill = True
1152 index += 1
1154 self.fill_count = index
1156 def _has_feature_property_bags(self):
1157 # Check for any format properties that require a feature bag. Currently
1158 # this only applies to checkboxes.
1159 if not self.feature_property_bags:
1160 for xf_format in self.formats:
1161 if xf_format.checkbox:
1162 self.feature_property_bags.add("XFComplements")
1164 if xf_format.dxf_index is not None and xf_format.checkbox:
1165 self.feature_property_bags.add("DXFComplements")
1167 return self.feature_property_bags
1169 def _prepare_defined_names(self) -> None:
1170 # Iterate through the worksheets and store any defined names in
1171 # addition to any user defined names. Stores the defined names
1172 # for the Workbook.xml and the named ranges for App.xml.
1173 defined_names = self.defined_names
1175 for sheet in self.worksheets():
1176 # Check for Print Area settings.
1177 if sheet.autofilter_area:
1178 hidden = 1
1179 sheet_range = sheet.autofilter_area
1180 # Store the defined names.
1181 defined_names.append(
1182 ["_xlnm._FilterDatabase", sheet.index, sheet_range, hidden]
1183 )
1185 # Check for Print Area settings.
1186 if sheet.print_area_range:
1187 hidden = 0
1188 sheet_range = sheet.print_area_range
1189 # Store the defined names.
1190 defined_names.append(
1191 ["_xlnm.Print_Area", sheet.index, sheet_range, hidden]
1192 )
1194 # Check for repeat rows/cols referred to as Print Titles.
1195 if sheet.repeat_col_range or sheet.repeat_row_range:
1196 hidden = 0
1197 sheet_range = ""
1198 if sheet.repeat_col_range and sheet.repeat_row_range:
1199 sheet_range = sheet.repeat_col_range + "," + sheet.repeat_row_range
1200 else:
1201 sheet_range = sheet.repeat_col_range + sheet.repeat_row_range
1202 # Store the defined names.
1203 defined_names.append(
1204 ["_xlnm.Print_Titles", sheet.index, sheet_range, hidden]
1205 )
1207 defined_names = self._sort_defined_names(defined_names)
1208 self.defined_names = defined_names
1209 self.named_ranges = self._extract_named_ranges(defined_names)
1211 def _sort_defined_names(self, names):
1212 # Sort the list of list of internal and user defined names in
1213 # the same order as used by Excel.
1215 # Add a normalize name string to each list for sorting.
1216 for name_list in names:
1217 (defined_name, _, sheet_name, _) = name_list
1219 # Normalize the defined name by removing any leading '_xmln.'
1220 # from internal names and lowercasing the string.
1221 defined_name = defined_name.replace("_xlnm.", "").lower()
1223 # Normalize the sheetname by removing the leading quote and
1224 # lowercasing the string.
1225 sheet_name = sheet_name.lstrip("'").lower()
1227 name_list.append(defined_name + "::" + sheet_name)
1229 # Sort based on the normalized key.
1230 names.sort(key=operator.itemgetter(4))
1232 # Remove the extra key used for sorting.
1233 for name_list in names:
1234 name_list.pop()
1236 return names
1238 def _prepare_drawings(self) -> None:
1239 # Iterate through the worksheets and set up chart and image drawings.
1240 chart_ref_id = 0
1241 ref_id = 0
1242 drawing_id = 0
1243 image_ids = {}
1244 header_image_ids = {}
1245 background_ids = {}
1247 # Store the image types for any embedded images.
1248 for image in self.embedded_images.images:
1249 image_extension = image._image_extension
1250 self.image_types[image_extension] = True
1252 if image.description is not None:
1253 self.has_embedded_descriptions = True
1255 image_ref_id = len(self.embedded_images.images)
1257 for sheet in self.worksheets():
1258 chart_count = len(sheet.charts)
1259 image_count = len(sheet.images)
1260 shape_count = len(sheet.shapes)
1262 header_image_count = len(sheet.header_images)
1263 footer_image_count = len(sheet.footer_images)
1264 has_background = sheet.background_image
1265 has_drawing = False
1267 if not (
1268 chart_count
1269 or image_count
1270 or shape_count
1271 or header_image_count
1272 or footer_image_count
1273 or has_background
1274 ):
1275 continue
1277 # Don't increase the drawing_id header/footer images.
1278 if chart_count or image_count or shape_count:
1279 drawing_id += 1
1280 has_drawing = True
1282 # Prepare the background images.
1283 if sheet.background_image:
1284 image = sheet.background_image
1285 image_extension = image._image_extension
1286 image_digest = image._digest
1288 self.image_types[image_extension] = True
1290 if image_digest in background_ids:
1291 ref_id = background_ids[image_digest]
1292 else:
1293 image_ref_id += 1
1294 ref_id = image_ref_id
1295 background_ids[image_digest] = image_ref_id
1296 self.images.append(image)
1298 sheet._prepare_background(ref_id, image_extension)
1300 # Prepare the worksheet images.
1301 for index in range(image_count):
1302 image = sheet.images[index]
1303 image_extension = image._image_extension
1304 image_digest = image._digest
1306 self.image_types[image_extension] = True
1308 if image_digest in image_ids:
1309 ref_id = image_ids[image_digest]
1310 else:
1311 image_ref_id += 1
1312 ref_id = image_ref_id
1313 image_ids[image_digest] = image_ref_id
1314 self.images.append(image)
1316 sheet._prepare_image(
1317 image,
1318 ref_id,
1319 drawing_id,
1320 )
1322 # Prepare the worksheet charts.
1323 for index in range(chart_count):
1324 chart_ref_id += 1
1325 sheet._prepare_chart(index, chart_ref_id, drawing_id)
1327 # Prepare the worksheet shapes.
1328 for index in range(shape_count):
1329 sheet._prepare_shape(index, drawing_id)
1331 # Prepare the header images.
1332 for image in sheet.header_images:
1333 image_extension = image._image_extension
1334 image_digest = image._digest
1336 self.image_types[image_extension] = True
1338 if image_digest in header_image_ids:
1339 ref_id = header_image_ids[image_digest]
1340 else:
1341 image_ref_id += 1
1342 ref_id = image_ref_id
1343 header_image_ids[image_digest] = image_ref_id
1344 self.images.append(image)
1346 sheet._prepare_header_image(ref_id, image)
1348 # Prepare the footer images.
1349 for image in sheet.footer_images:
1350 image_extension = image._image_extension
1351 image_digest = image._digest
1353 self.image_types[image_extension] = True
1355 if image_digest in header_image_ids:
1356 ref_id = header_image_ids[image_digest]
1357 else:
1358 image_ref_id += 1
1359 ref_id = image_ref_id
1360 header_image_ids[image_digest] = image_ref_id
1361 self.images.append(image)
1363 sheet._prepare_header_image(ref_id, image)
1365 if has_drawing:
1366 drawing = sheet.drawing
1367 self.drawings.append(drawing)
1369 # Remove charts that were created but not inserted into worksheets.
1370 for chart in self.charts[:]:
1371 if chart.id == -1:
1372 self.charts.remove(chart)
1374 # Sort the workbook charts references into the order that the were
1375 # written to the worksheets above.
1376 self.charts = sorted(self.charts, key=lambda chart: chart.id)
1378 self.drawing_count = drawing_id
1380 def _extract_named_ranges(self, defined_names):
1381 # Extract the named ranges from the sorted list of defined names.
1382 # These are used in the App.xml file.
1383 named_ranges = []
1385 for defined_name in defined_names:
1386 name = defined_name[0]
1387 index = defined_name[1]
1388 sheet_range = defined_name[2]
1390 # Skip autoFilter ranges.
1391 if name == "_xlnm._FilterDatabase":
1392 continue
1394 # We are only interested in defined names with ranges.
1395 if "!" in sheet_range:
1396 sheet_name, _ = sheet_range.split("!", 1)
1398 # Match Print_Area and Print_Titles xlnm types.
1399 if name.startswith("_xlnm."):
1400 xlnm_type = name.replace("_xlnm.", "")
1401 name = sheet_name + "!" + xlnm_type
1402 elif index != -1:
1403 name = sheet_name + "!" + name
1405 named_ranges.append(name)
1407 return named_ranges
1409 def _get_sheet_index(self, sheetname):
1410 # Convert a sheet name to its index. Return None otherwise.
1411 sheetname = sheetname.strip("'")
1413 if sheetname in self.sheetnames:
1414 return self.sheetnames[sheetname].index
1416 return None
1418 def _prepare_vml(self) -> None:
1419 # Iterate through the worksheets and set up the VML objects.
1420 comment_id = 0
1421 vml_drawing_id = 0
1422 vml_data_id = 1
1423 vml_header_id = 0
1424 vml_shape_id = 1024
1425 vml_files = 0
1426 comment_files = 0
1428 for sheet in self.worksheets():
1429 if not sheet.has_vml and not sheet.has_header_vml:
1430 continue
1432 vml_files += 1
1434 if sheet.has_vml:
1435 if sheet.has_comments:
1436 comment_files += 1
1437 comment_id += 1
1438 self.has_comments = True
1440 vml_drawing_id += 1
1442 count = sheet._prepare_vml_objects(
1443 vml_data_id, vml_shape_id, vml_drawing_id, comment_id
1444 )
1446 # Each VML should start with a shape id incremented by 1024.
1447 vml_data_id += 1 * int((1024 + count) / 1024)
1448 vml_shape_id += 1024 * int((1024 + count) / 1024)
1450 if sheet.has_header_vml:
1451 vml_header_id += 1
1452 vml_drawing_id += 1
1453 sheet._prepare_header_vml_objects(vml_header_id, vml_drawing_id)
1455 self.num_vml_files = vml_files
1456 self.num_comment_files = comment_files
1458 def _prepare_tables(self) -> None:
1459 # Set the table ids for the worksheet tables.
1460 table_id = 0
1461 seen = {}
1463 for sheet in self.worksheets():
1464 table_count = len(sheet.tables)
1466 if not table_count:
1467 continue
1469 sheet._prepare_tables(table_id + 1, seen)
1470 table_id += table_count
1472 def _prepare_metadata(self) -> None:
1473 # Set the metadata rel link.
1474 self.has_embedded_images = self.embedded_images.has_images()
1475 self.has_metadata = self.has_embedded_images
1477 for sheet in self.worksheets():
1478 if sheet.has_dynamic_arrays:
1479 self.has_metadata = True
1480 self.has_dynamic_functions = True
1482 def _add_chart_data(self) -> None:
1483 # Add "cached" data to charts to provide the numCache and strCache
1484 # data for series and title/axis ranges.
1485 worksheets = {}
1486 seen_ranges = {}
1487 charts = []
1489 # Map worksheet names to worksheet objects.
1490 for worksheet in self.worksheets():
1491 worksheets[worksheet.name] = worksheet
1493 # Build a list of the worksheet charts including any combined charts.
1494 for chart in self.charts:
1495 charts.append(chart)
1496 if chart.combined:
1497 charts.append(chart.combined)
1499 for chart in charts:
1500 for c_range in chart.formula_ids.keys():
1501 r_id = chart.formula_ids[c_range]
1503 # Skip if the series has user defined data.
1504 if chart.formula_data[r_id] is not None:
1505 if c_range not in seen_ranges or seen_ranges[c_range] is None:
1506 data = chart.formula_data[r_id]
1507 seen_ranges[c_range] = data
1508 continue
1510 # Check to see if the data is already cached locally.
1511 if c_range in seen_ranges:
1512 chart.formula_data[r_id] = seen_ranges[c_range]
1513 continue
1515 # Convert the range formula to a sheet name and cell range.
1516 (sheetname, cells) = self._get_chart_range(c_range)
1518 # Skip if we couldn't parse the formula.
1519 if sheetname is None:
1520 continue
1522 # Handle non-contiguous ranges like:
1523 # (Sheet1!$A$1:$A$2,Sheet1!$A$4:$A$5).
1524 # We don't try to parse them. We just return an empty list.
1525 if sheetname.startswith("("):
1526 chart.formula_data[r_id] = []
1527 seen_ranges[c_range] = []
1528 continue
1530 # Warn if the name is unknown since it indicates a user error
1531 # in a chart series formula.
1532 if sheetname not in worksheets:
1533 warn(
1534 f"Unknown worksheet reference '{sheetname}' in range "
1535 f"'{c_range}' passed to add_series()"
1536 )
1537 chart.formula_data[r_id] = []
1538 seen_ranges[c_range] = []
1539 continue
1541 # Find the worksheet object based on the sheet name.
1542 worksheet = worksheets[sheetname]
1544 # Get the data from the worksheet table.
1545 data = worksheet._get_range_data(*cells)
1547 # Add the data to the chart.
1548 chart.formula_data[r_id] = data
1550 # Store range data locally to avoid lookup if seen again.
1551 seen_ranges[c_range] = data
1553 def _get_chart_range(self, c_range):
1554 # Convert a range formula such as Sheet1!$B$1:$B$5 into a sheet name
1555 # and cell range such as ( 'Sheet1', 0, 1, 4, 1 ).
1557 # Split the range formula into sheetname and cells at the last '!'.
1558 pos = c_range.rfind("!")
1559 if pos > 0:
1560 sheetname = c_range[:pos]
1561 cells = c_range[pos + 1 :]
1562 else:
1563 return None, None
1565 # Split the cell range into 2 cells or else use single cell for both.
1566 if cells.find(":") > 0:
1567 (cell_1, cell_2) = cells.split(":", 1)
1568 else:
1569 (cell_1, cell_2) = (cells, cells)
1571 # Remove leading/trailing quotes and convert escaped quotes to single.
1572 sheetname = sheetname.strip("'")
1573 sheetname = sheetname.replace("''", "'")
1575 try:
1576 # Get the row, col values from the Excel ranges. We do this in a
1577 # try block for ranges that can't be parsed such as defined names.
1578 (row_start, col_start) = xl_cell_to_rowcol(cell_1)
1579 (row_end, col_end) = xl_cell_to_rowcol(cell_2)
1580 except AttributeError:
1581 return None, None
1583 # We only handle 1D ranges.
1584 if row_start != row_end and col_start != col_end:
1585 return None, None
1587 return sheetname, [row_start, col_start, row_end, col_end]
1589 def _prepare_sst_string_data(self) -> None:
1590 # Convert the SST string data from a dict to a list.
1591 self.str_table._sort_string_data()
1593 def _get_packager(self):
1594 # Get and instance of the Packager class to create the xlsx package.
1595 # This allows the default packager to be over-ridden.
1596 return Packager()
1598 ###########################################################################
1599 #
1600 # XML methods.
1601 #
1602 ###########################################################################
1604 def _write_workbook(self) -> None:
1605 # Write <workbook> element.
1607 schema = "http://schemas.openxmlformats.org"
1608 xmlns = schema + "/spreadsheetml/2006/main"
1609 xmlns_r = schema + "/officeDocument/2006/relationships"
1611 attributes = [
1612 ("xmlns", xmlns),
1613 ("xmlns:r", xmlns_r),
1614 ]
1616 self._xml_start_tag("workbook", attributes)
1618 def _write_file_version(self) -> None:
1619 # Write the <fileVersion> element.
1621 app_name = "xl"
1622 last_edited = 4
1623 lowest_edited = 4
1624 rup_build = 4505
1626 attributes = [
1627 ("appName", app_name),
1628 ("lastEdited", last_edited),
1629 ("lowestEdited", lowest_edited),
1630 ("rupBuild", rup_build),
1631 ]
1633 if self.vba_project:
1634 attributes.append(("codeName", "{37E998C4-C9E5-D4B9-71C8-EB1FF731991C}"))
1636 self._xml_empty_tag("fileVersion", attributes)
1638 def _write_file_sharing(self) -> None:
1639 # Write the <fileSharing> element.
1640 if self.read_only == 0:
1641 return
1643 attributes = [("readOnlyRecommended", 1)]
1645 self._xml_empty_tag("fileSharing", attributes)
1647 def _write_workbook_pr(self) -> None:
1648 # Write <workbookPr> element.
1649 default_theme_version = 124226
1650 attributes = []
1652 if self.vba_codename:
1653 attributes.append(("codeName", self.vba_codename))
1654 if self.date_1904:
1655 attributes.append(("date1904", 1))
1657 attributes.append(("defaultThemeVersion", default_theme_version))
1659 self._xml_empty_tag("workbookPr", attributes)
1661 def _write_book_views(self) -> None:
1662 # Write <bookViews> element.
1663 self._xml_start_tag("bookViews")
1664 self._write_workbook_view()
1665 self._xml_end_tag("bookViews")
1667 def _write_workbook_view(self) -> None:
1668 # Write <workbookView> element.
1669 attributes = [
1670 ("xWindow", self.x_window),
1671 ("yWindow", self.y_window),
1672 ("windowWidth", self.window_width),
1673 ("windowHeight", self.window_height),
1674 ]
1676 # Store the tabRatio attribute when it isn't the default.
1677 if self.tab_ratio != 600:
1678 attributes.append(("tabRatio", self.tab_ratio))
1680 # Store the firstSheet attribute when it isn't the default.
1681 if self.worksheet_meta.firstsheet > 0:
1682 firstsheet = self.worksheet_meta.firstsheet + 1
1683 attributes.append(("firstSheet", firstsheet))
1685 # Store the activeTab attribute when it isn't the first sheet.
1686 if self.worksheet_meta.activesheet > 0:
1687 attributes.append(("activeTab", self.worksheet_meta.activesheet))
1689 self._xml_empty_tag("workbookView", attributes)
1691 def _write_sheets(self) -> None:
1692 # Write <sheets> element.
1693 self._xml_start_tag("sheets")
1695 id_num = 1
1696 for worksheet in self.worksheets():
1697 self._write_sheet(worksheet.name, id_num, worksheet.hidden)
1698 id_num += 1
1700 self._xml_end_tag("sheets")
1702 def _write_sheet(self, name, sheet_id, hidden) -> None:
1703 # Write <sheet> element.
1704 attributes = [
1705 ("name", name),
1706 ("sheetId", sheet_id),
1707 ]
1709 if hidden == 1:
1710 attributes.append(("state", "hidden"))
1711 elif hidden == 2:
1712 attributes.append(("state", "veryHidden"))
1714 attributes.append(("r:id", "rId" + str(sheet_id)))
1716 self._xml_empty_tag("sheet", attributes)
1718 def _write_calc_pr(self) -> None:
1719 # Write the <calcPr> element.
1720 attributes = [("calcId", self.calc_id)]
1722 if self.calc_mode == "manual":
1723 attributes.append(("calcMode", self.calc_mode))
1724 attributes.append(("calcOnSave", "0"))
1725 elif self.calc_mode == "autoNoTable":
1726 attributes.append(("calcMode", self.calc_mode))
1728 if self.calc_on_load:
1729 attributes.append(("fullCalcOnLoad", "1"))
1731 self._xml_empty_tag("calcPr", attributes)
1733 def _write_defined_names(self) -> None:
1734 # Write the <definedNames> element.
1735 if not self.defined_names:
1736 return
1738 self._xml_start_tag("definedNames")
1740 for defined_name in self.defined_names:
1741 self._write_defined_name(defined_name)
1743 self._xml_end_tag("definedNames")
1745 def _write_defined_name(self, defined_name) -> None:
1746 # Write the <definedName> element.
1747 name = defined_name[0]
1748 sheet_id = defined_name[1]
1749 sheet_range = defined_name[2]
1750 hidden = defined_name[3]
1752 attributes = [("name", name)]
1754 if sheet_id != -1:
1755 attributes.append(("localSheetId", sheet_id))
1756 if hidden:
1757 attributes.append(("hidden", 1))
1759 self._xml_data_element("definedName", sheet_range, attributes)
1762# A metadata class to share data between worksheets.
1763class WorksheetMeta:
1764 """
1765 A class to track worksheets data such as the active sheet and the
1766 first sheet.
1768 """
1770 def __init__(self) -> None:
1771 self.activesheet = 0
1772 self.firstsheet = 0
1775# A helper class to share embedded images between worksheets.
1776class EmbeddedImages:
1777 """
1778 A class to track duplicate embedded images between worksheets.
1780 """
1782 def __init__(self) -> None:
1783 self.images = []
1784 self.image_indexes = {}
1786 def get_image_index(self, image: Image):
1787 """
1788 Get the index of an embedded image.
1790 Args:
1791 image: The image to lookup.
1793 Returns:
1794 The image index.
1796 """
1797 image_index = self.image_indexes.get(image._digest)
1799 if image_index is None:
1800 self.images.append(image)
1801 image_index = len(self.images)
1802 self.image_indexes[image._digest] = image_index
1804 return image_index
1806 def has_images(self):
1807 """
1808 Check if the worksheet has embedded images.
1810 Args:
1811 None.
1813 Returns:
1814 Boolean.
1816 """
1817 return len(self.images) > 0