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 Any, 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, filename: Optional[str] = None, options: Optional[Dict[str, Any]] = None
66 ) -> None:
67 """
68 Constructor.
70 """
71 if options is None:
72 options = {}
74 super().__init__()
76 self.filename = filename
78 self.tmpdir = options.get("tmpdir", None)
79 self.date_1904 = options.get("date_1904", False)
80 self.strings_to_numbers = options.get("strings_to_numbers", False)
81 self.strings_to_formulas = options.get("strings_to_formulas", True)
82 self.strings_to_urls = options.get("strings_to_urls", True)
83 self.nan_inf_to_errors = options.get("nan_inf_to_errors", False)
84 self.default_date_format = options.get("default_date_format", None)
85 self.constant_memory = options.get("constant_memory", False)
86 self.in_memory = options.get("in_memory", False)
87 self.excel2003_style = options.get("excel2003_style", False)
88 self.remove_timezone = options.get("remove_timezone", False)
89 self.use_future_functions = options.get("use_future_functions", False)
90 self.default_format_properties = options.get("default_format_properties", {})
92 self.max_url_length = options.get("max_url_length", 2079)
93 if self.max_url_length < 255:
94 self.max_url_length = 2079
96 if options.get("use_zip64"):
97 self.allow_zip64 = True
98 else:
99 self.allow_zip64 = False
101 self.worksheet_meta = WorksheetMeta()
102 self.selected = 0
103 self.fileclosed = 0
104 self.filehandle = None
105 self.internal_fh = 0
106 self.sheet_name = "Sheet"
107 self.chart_name = "Chart"
108 self.sheetname_count = 0
109 self.chartname_count = 0
110 self.worksheets_objs = []
111 self.charts = []
112 self.drawings = []
113 self.sheetnames = {}
114 self.formats = []
115 self.xf_formats = []
116 self.xf_format_indices = {}
117 self.dxf_formats = []
118 self.dxf_format_indices = {}
119 self.palette = []
120 self.font_count = 0
121 self.num_formats = []
122 self.defined_names = []
123 self.named_ranges = []
124 self.custom_colors = []
125 self.doc_properties = {}
126 self.custom_properties = []
127 self.createtime = datetime.now(timezone.utc)
128 self.num_vml_files = 0
129 self.num_comment_files = 0
130 self.x_window = 240
131 self.y_window = 15
132 self.window_width = 16095
133 self.window_height = 9660
134 self.tab_ratio = 600
135 self.str_table = SharedStringTable()
136 self.vba_project = None
137 self.vba_project_is_stream = False
138 self.vba_project_signature = None
139 self.vba_project_signature_is_stream = False
140 self.vba_codename = None
141 self.image_types = {}
142 self.images = []
143 self.border_count = 0
144 self.fill_count = 0
145 self.drawing_count = 0
146 self.calc_mode = "auto"
147 self.calc_on_load = True
148 self.calc_id = 124519
149 self.has_comments = False
150 self.read_only = 0
151 self.has_metadata = False
152 self.has_embedded_images = False
153 self.has_dynamic_functions = False
154 self.has_embedded_descriptions = False
155 self.embedded_images = EmbeddedImages()
156 self.feature_property_bags = set()
158 # We can't do 'constant_memory' mode while doing 'in_memory' mode.
159 if self.in_memory:
160 self.constant_memory = False
162 # Add the default cell format.
163 if self.excel2003_style:
164 self.add_format({"xf_index": 0, "font_family": 0})
165 else:
166 self.add_format({"xf_index": 0})
168 # Add a default URL format.
169 self.default_url_format = self.add_format({"hyperlink": True})
171 # Add the default date format.
172 if self.default_date_format is not None:
173 self.default_date_format = self.add_format(
174 {"num_format": self.default_date_format}
175 )
177 def __enter__(self):
178 """Return self object to use with "with" statement."""
179 return self
181 def __exit__(self, type, value, traceback) -> None:
182 # pylint: disable=redefined-builtin
183 """Close workbook when exiting "with" statement."""
184 self.close()
186 def add_worksheet(
187 self, name: Optional[str] = None, worksheet_class=None
188 ) -> Worksheet:
189 """
190 Add a new worksheet to the Excel workbook.
192 Args:
193 name: The worksheet name. Defaults to 'Sheet1', etc.
195 Returns:
196 Reference to a worksheet object.
198 """
199 if worksheet_class is None:
200 worksheet_class = self.worksheet_class
202 return self._add_sheet(name, worksheet_class=worksheet_class)
204 def add_chartsheet(
205 self, name: Optional[str] = None, chartsheet_class=None
206 ) -> Chartsheet:
207 """
208 Add a new chartsheet to the Excel workbook.
210 Args:
211 name: The chartsheet name. Defaults to 'Sheet1', etc.
213 Returns:
214 Reference to a chartsheet object.
216 """
217 if chartsheet_class is None:
218 chartsheet_class = self.chartsheet_class
220 return self._add_sheet(name, worksheet_class=chartsheet_class)
222 def add_format(self, properties=None) -> Format:
223 """
224 Add a new Format to the Excel Workbook.
226 Args:
227 properties: The format properties.
229 Returns:
230 Reference to a Format object.
232 """
233 format_properties = self.default_format_properties.copy()
235 if self.excel2003_style:
236 format_properties = {"font_name": "Arial", "font_size": 10, "theme": 1 * -1}
238 if properties:
239 format_properties.update(properties)
241 xf_format = Format(
242 format_properties, self.xf_format_indices, self.dxf_format_indices
243 )
245 # Store the format reference.
246 self.formats.append(xf_format)
248 return xf_format
250 def add_chart(self, options: Dict[str, Any]) -> Optional[
251 Union[
252 ChartArea,
253 ChartBar,
254 ChartColumn,
255 ChartDoughnut,
256 ChartLine,
257 ChartPie,
258 ChartRadar,
259 ChartScatter,
260 ChartStock,
261 ]
262 ]:
263 """
264 Create a chart object.
266 Args:
267 options: The chart type and subtype options.
269 Returns:
270 Reference to a Chart object.
272 """
274 # Type must be specified so we can create the required chart instance.
275 chart_type = options.get("type")
276 if chart_type is None:
277 warn("Chart type must be defined in add_chart()")
278 return None
280 if chart_type == "area":
281 chart = ChartArea(options)
282 elif chart_type == "bar":
283 chart = ChartBar(options)
284 elif chart_type == "column":
285 chart = ChartColumn(options)
286 elif chart_type == "doughnut":
287 chart = ChartDoughnut()
288 elif chart_type == "line":
289 chart = ChartLine(options)
290 elif chart_type == "pie":
291 chart = ChartPie()
292 elif chart_type == "radar":
293 chart = ChartRadar(options)
294 elif chart_type == "scatter":
295 chart = ChartScatter(options)
296 elif chart_type == "stock":
297 chart = ChartStock()
298 else:
299 warn(f"Unknown chart type '{chart_type}' in add_chart()")
300 return None
302 # Set the embedded chart name if present.
303 if "name" in options:
304 chart.chart_name = options["name"]
306 chart.embedded = True
307 chart.date_1904 = self.date_1904
308 chart.remove_timezone = self.remove_timezone
310 self.charts.append(chart)
312 return chart
314 def add_vba_project(self, vba_project: str, is_stream: bool = False) -> int:
315 """
316 Add a vbaProject binary to the Excel workbook.
318 Args:
319 vba_project: The vbaProject binary file name.
320 is_stream: vba_project is an in memory byte stream.
322 Returns:
323 0 on success.
325 """
326 if not is_stream and not os.path.exists(vba_project):
327 warn(f"VBA project binary file '{vba_project}' not found.")
328 return -1
330 if self.vba_codename is None:
331 self.vba_codename = "ThisWorkbook"
333 self.vba_project = vba_project
334 self.vba_project_is_stream = is_stream
336 return 0
338 def add_signed_vba_project(
339 self,
340 vba_project: str,
341 signature: str,
342 project_is_stream: bool = False,
343 signature_is_stream: bool = False,
344 ) -> Literal[0, -1]:
345 """
346 Add a vbaProject binary and a vbaProjectSignature binary to the
347 Excel workbook.
349 Args:
350 vba_project: The vbaProject binary file name.
351 signature: The vbaProjectSignature binary file name.
352 project_is_stream: vba_project is an in memory byte stream.
353 signature_is_stream: signature is an in memory byte stream.
355 Returns:
356 0 on success.
358 """
359 if self.add_vba_project(vba_project, project_is_stream) == -1:
360 return -1
362 if not signature_is_stream and not os.path.exists(signature):
363 warn(f"VBA project signature binary file '{signature}' not found.")
364 return -1
366 self.vba_project_signature = signature
367 self.vba_project_signature_is_stream = signature_is_stream
369 return 0
371 def close(self) -> None:
372 """
373 Call finalization code and close file.
375 Args:
376 None.
378 Returns:
379 Nothing.
381 """
382 # pylint: disable=raise-missing-from
383 if not self.fileclosed:
384 try:
385 self._store_workbook()
386 except IOError as e:
387 raise FileCreateError(e)
388 except LargeZipFile:
389 raise FileSizeError(
390 "Filesize would require ZIP64 extensions. "
391 "Use workbook.use_zip64()."
392 )
394 self.fileclosed = True
396 # Ensure all constant_memory temp files are closed.
397 if self.constant_memory:
398 for worksheet in self.worksheets():
399 worksheet._opt_close()
401 else:
402 warn("Calling close() on already closed file.")
404 def set_size(self, width: int, height: int) -> None:
405 """
406 Set the size of a workbook window.
408 Args:
409 width: Width of the window in pixels.
410 height: Height of the window in pixels.
412 Returns:
413 Nothing.
415 """
416 # Convert the width/height to twips at 96 dpi.
417 if width:
418 self.window_width = int(width * 1440 / 96)
419 else:
420 self.window_width = 16095
422 if height:
423 self.window_height = int(height * 1440 / 96)
424 else:
425 self.window_height = 9660
427 def set_tab_ratio(self, tab_ratio: Optional[Union[int, float]] = None) -> None:
428 """
429 Set the ratio between worksheet tabs and the horizontal slider.
431 Args:
432 tab_ratio: The tab ratio, 0 <= tab_ratio <= 100
434 Returns:
435 Nothing.
437 """
438 if tab_ratio is None:
439 return
441 if tab_ratio < 0 or tab_ratio > 100:
442 warn(f"Tab ratio '{tab_ratio}' outside: 0 <= tab_ratio <= 100")
443 else:
444 self.tab_ratio = int(tab_ratio * 10)
446 def set_properties(self, properties) -> None:
447 """
448 Set the document properties such as Title, Author etc.
450 Args:
451 properties: Dictionary of document properties.
453 Returns:
454 Nothing.
456 """
457 self.doc_properties = properties
459 def set_custom_property(
460 self,
461 name: str,
462 value: Union[bool, datetime, int, float, Decimal, Fraction, Any],
463 property_type: Optional[
464 Literal["bool", "date", "number", "number_int", "text"]
465 ] = None,
466 ) -> Literal[0, -1]:
467 """
468 Set a custom document property.
470 Args:
471 name: The name of the custom property.
472 value: The value of the custom property.
473 property_type: The type of the custom property. Optional.
475 Returns:
476 0 on success.
478 """
479 if name is None or value is None:
480 warn(
481 "The name and value parameters must be non-None in "
482 "set_custom_property()"
483 )
484 return -1
486 if property_type is None:
487 # Determine the property type from the Python type.
488 if isinstance(value, bool):
489 property_type = "bool"
490 elif isinstance(value, datetime):
491 property_type = "date"
492 elif isinstance(value, int):
493 property_type = "number_int"
494 elif isinstance(value, (float, int, Decimal, Fraction)):
495 property_type = "number"
496 else:
497 property_type = "text"
499 # Convert non-string values to strings to have a single data type.
500 if property_type == "bool":
501 value = str(value).lower()
503 if property_type == "date":
504 value = value.strftime("%Y-%m-%dT%H:%M:%SZ")
506 if property_type in ("number", "number_int"):
507 value = str(value)
509 if property_type == "text" and len(value) > 255:
510 warn(
511 f"Length of 'value' parameter exceeds Excel's limit of 255 "
512 f"characters in set_custom_property(): '{value}'"
513 )
515 if len(name) > 255:
516 warn(
517 f"Length of 'name' parameter exceeds Excel's limit of 255 "
518 f"characters in set_custom_property(): '{name}'"
519 )
521 self.custom_properties.append((name, value, property_type))
523 return 0
525 def set_calc_mode(
526 self, mode: Literal["manual", "auto_except_tables", "auto"], calc_id=None
527 ) -> None:
528 """
529 Set the Excel calculation mode for the workbook.
531 Args:
532 mode: String containing one of:
533 * manual
534 * auto_except_tables
535 * auto
537 Returns:
538 Nothing.
540 """
541 self.calc_mode = mode
543 if mode == "manual":
544 self.calc_on_load = False
545 elif mode == "auto_except_tables":
546 self.calc_mode = "autoNoTable"
548 # Leave undocumented for now. Rarely required.
549 if calc_id:
550 self.calc_id = calc_id
552 def define_name(self, name: str, formula: str) -> Literal[0, -1]:
553 # Create a defined name in Excel. We handle global/workbook level
554 # names and local/worksheet names.
555 """
556 Create a defined name in the workbook.
558 Args:
559 name: The defined name.
560 formula: The cell or range that the defined name refers to.
562 Returns:
563 0 on success.
565 """
566 sheet_index = None
567 sheetname = ""
569 # Remove the = sign from the formula if it exists.
570 if formula.startswith("="):
571 formula = formula.lstrip("=")
573 # Local defined names are formatted like "Sheet1!name".
574 sheet_parts = re.compile(r"^([^!]+)!([^!]+)$")
575 match = sheet_parts.match(name)
577 if match:
578 sheetname = match.group(1)
579 name = match.group(2)
580 sheet_index = self._get_sheet_index(sheetname)
582 # Warn if the sheet index wasn't found.
583 if sheet_index is None:
584 warn(f"Unknown sheet name '{sheetname}' in defined_name()")
585 return -1
586 else:
587 # Use -1 to indicate global names.
588 sheet_index = -1
590 # Warn if the defined name contains invalid chars as defined by Excel.
591 if not re.match(r"^[\w\\][\w\\.]*$", name, re.UNICODE) or re.match(
592 r"^\d", name
593 ):
594 warn(f"Invalid Excel characters in defined_name(): '{name}'")
595 return -1
597 # Warn if the defined name looks like a cell name.
598 if re.match(r"^[a-zA-Z][a-zA-Z]?[a-dA-D]?\d+$", name):
599 warn(f"Name looks like a cell name in defined_name(): '{name}'")
600 return -1
602 # Warn if the name looks like a R1C1 cell reference.
603 if re.match(r"^[rcRC]$", name) or re.match(r"^[rcRC]\d+[rcRC]\d+$", name):
604 warn(f"Invalid name '{name}' like a RC cell ref in defined_name()")
605 return -1
607 self.defined_names.append([name, sheet_index, formula, False])
609 return 0
611 def worksheets(self) -> List[Worksheet]:
612 """
613 Return a list of the worksheet objects in the workbook.
615 Args:
616 None.
618 Returns:
619 A list of worksheet objects.
621 """
622 return self.worksheets_objs
624 def get_worksheet_by_name(self, name: str) -> Optional[Worksheet]:
625 """
626 Return a worksheet object in the workbook using the sheetname.
628 Args:
629 name: The name of the worksheet.
631 Returns:
632 A worksheet object or None.
634 """
635 return self.sheetnames.get(name)
637 def get_default_url_format(self) -> Format:
638 """
639 Get the default url format used when a user defined format isn't
640 specified with write_url(). The format is the hyperlink style defined
641 by Excel for the default theme.
643 Args:
644 None.
646 Returns:
647 A format object.
649 """
650 return self.default_url_format
652 def use_zip64(self) -> None:
653 """
654 Allow ZIP64 extensions when writing xlsx file zip container.
656 Args:
657 None.
659 Returns:
660 Nothing.
662 """
663 self.allow_zip64 = True
665 def set_vba_name(self, name: Optional[str] = None) -> None:
666 """
667 Set the VBA name for the workbook. By default the workbook is referred
668 to as ThisWorkbook in VBA.
670 Args:
671 name: The VBA name for the workbook.
673 Returns:
674 Nothing.
676 """
677 if name is not None:
678 self.vba_codename = name
679 else:
680 self.vba_codename = "ThisWorkbook"
682 def read_only_recommended(self) -> None:
683 """
684 Set the Excel "Read-only recommended" option when saving a file.
686 Args:
687 None.
689 Returns:
690 Nothing.
692 """
693 self.read_only = 2
695 ###########################################################################
696 #
697 # Private API.
698 #
699 ###########################################################################
701 def _assemble_xml_file(self) -> None:
702 # Assemble and write the XML file.
704 # Prepare format object for passing to Style.pm.
705 self._prepare_format_properties()
707 # Write the XML declaration.
708 self._xml_declaration()
710 # Write the workbook element.
711 self._write_workbook()
713 # Write the fileVersion element.
714 self._write_file_version()
716 # Write the fileSharing element.
717 self._write_file_sharing()
719 # Write the workbookPr element.
720 self._write_workbook_pr()
722 # Write the bookViews element.
723 self._write_book_views()
725 # Write the sheets element.
726 self._write_sheets()
728 # Write the workbook defined names.
729 self._write_defined_names()
731 # Write the calcPr element.
732 self._write_calc_pr()
734 # Close the workbook tag.
735 self._xml_end_tag("workbook")
737 # Close the file.
738 self._xml_close()
740 def _store_workbook(self) -> None:
741 # pylint: disable=consider-using-with
742 # Create the xlsx/zip file.
743 try:
744 xlsx_file = ZipFile(
745 self.filename,
746 "w",
747 compression=ZIP_DEFLATED,
748 allowZip64=self.allow_zip64,
749 )
750 except IOError as e:
751 raise e
753 # Assemble worksheets into a workbook.
754 packager = self._get_packager()
756 # Add a default worksheet if non have been added.
757 if not self.worksheets():
758 self.add_worksheet()
760 # Ensure that at least one worksheet has been selected.
761 if self.worksheet_meta.activesheet == 0:
762 self.worksheets_objs[0].selected = 1
763 self.worksheets_objs[0].hidden = 0
765 # Set the active sheet.
766 for sheet in self.worksheets():
767 if sheet.index == self.worksheet_meta.activesheet:
768 sheet.active = 1
770 # Set the sheet vba_codename the workbook has a vbaProject binary.
771 if self.vba_project:
772 for sheet in self.worksheets():
773 if sheet.vba_codename is None:
774 sheet.set_vba_name()
776 # Convert the SST strings data structure.
777 self._prepare_sst_string_data()
779 # Prepare the worksheet VML elements such as comments and buttons.
780 self._prepare_vml()
782 # Set the defined names for the worksheets such as Print Titles.
783 self._prepare_defined_names()
785 # Prepare the drawings, charts and images.
786 self._prepare_drawings()
788 # Add cached data to charts.
789 self._add_chart_data()
791 # Prepare the worksheet tables.
792 self._prepare_tables()
794 # Prepare the metadata file links.
795 self._prepare_metadata()
797 # Package the workbook.
798 packager._add_workbook(self)
799 packager._set_tmpdir(self.tmpdir)
800 packager._set_in_memory(self.in_memory)
801 xml_files = packager._create_package()
803 # Free up the Packager object.
804 packager = None
806 # Add XML sub-files to the Zip file with their Excel filename.
807 for file_id, file_data in enumerate(xml_files):
808 os_filename, xml_filename, is_binary = file_data
810 if self.in_memory:
811 # Set sub-file timestamp to Excel's timestamp of 1/1/1980.
812 zipinfo = ZipInfo(xml_filename, (1980, 1, 1, 0, 0, 0))
814 # Copy compression type from parent ZipFile.
815 zipinfo.compress_type = xlsx_file.compression
817 if is_binary:
818 xlsx_file.writestr(zipinfo, os_filename.getvalue())
819 else:
820 xlsx_file.writestr(zipinfo, os_filename.getvalue().encode("utf-8"))
821 else:
822 # The sub-files are tempfiles on disk, i.e, not in memory.
824 # Set sub-file timestamp to 31/1/1980 due to portability
825 # issues setting it to Excel's timestamp of 1/1/1980.
826 timestamp = time.mktime((1980, 1, 31, 0, 0, 0, 0, 0, -1))
827 os.utime(os_filename, (timestamp, timestamp))
829 try:
830 xlsx_file.write(os_filename, xml_filename)
831 os.remove(os_filename)
832 except LargeZipFile as e:
833 # Close open temp files on zipfile.LargeZipFile exception.
834 for i in range(file_id, len(xml_files) - 1):
835 os.remove(xml_files[i][0])
836 raise e
838 xlsx_file.close()
840 def _add_sheet(self, name, worksheet_class=None):
841 # Utility for shared code in add_worksheet() and add_chartsheet().
843 if worksheet_class:
844 worksheet = worksheet_class()
845 else:
846 worksheet = self.worksheet_class()
848 sheet_index = len(self.worksheets_objs)
849 name = self._check_sheetname(name, isinstance(worksheet, Chartsheet))
851 # Initialization data to pass to the worksheet.
852 init_data = {
853 "name": name,
854 "index": sheet_index,
855 "str_table": self.str_table,
856 "worksheet_meta": self.worksheet_meta,
857 "constant_memory": self.constant_memory,
858 "tmpdir": self.tmpdir,
859 "date_1904": self.date_1904,
860 "strings_to_numbers": self.strings_to_numbers,
861 "strings_to_formulas": self.strings_to_formulas,
862 "strings_to_urls": self.strings_to_urls,
863 "nan_inf_to_errors": self.nan_inf_to_errors,
864 "default_date_format": self.default_date_format,
865 "default_url_format": self.default_url_format,
866 "workbook_add_format": self.add_format,
867 "excel2003_style": self.excel2003_style,
868 "remove_timezone": self.remove_timezone,
869 "max_url_length": self.max_url_length,
870 "use_future_functions": self.use_future_functions,
871 "embedded_images": self.embedded_images,
872 }
874 worksheet._initialize(init_data)
876 self.worksheets_objs.append(worksheet)
877 self.sheetnames[name] = worksheet
879 return worksheet
881 def _check_sheetname(self, sheetname, is_chartsheet=False):
882 # Check for valid worksheet names. We check the length, if it contains
883 # any invalid chars and if the sheetname is unique in the workbook.
884 invalid_char = re.compile(r"[\[\]:*?/\\]")
886 # Increment the Sheet/Chart number used for default sheet names below.
887 if is_chartsheet:
888 self.chartname_count += 1
889 else:
890 self.sheetname_count += 1
892 # Supply default Sheet/Chart sheetname if none has been defined.
893 if sheetname is None or sheetname == "":
894 if is_chartsheet:
895 sheetname = self.chart_name + str(self.chartname_count)
896 else:
897 sheetname = self.sheet_name + str(self.sheetname_count)
899 # Check that sheet sheetname is <= 31. Excel limit.
900 if len(sheetname) > 31:
901 raise InvalidWorksheetName(
902 f"Excel worksheet name '{sheetname}' must be <= 31 chars."
903 )
905 # Check that sheetname doesn't contain any invalid characters.
906 if invalid_char.search(sheetname):
907 raise InvalidWorksheetName(
908 f"Invalid Excel character '[]:*?/\\' in sheetname '{sheetname}'."
909 )
911 # Check that sheetname doesn't start or end with an apostrophe.
912 if sheetname.startswith("'") or sheetname.endswith("'"):
913 raise InvalidWorksheetName(
914 f'Sheet name cannot start or end with an apostrophe "{sheetname}".'
915 )
917 # Check that the worksheet name doesn't already exist since this is a
918 # fatal Excel error. The check must be case insensitive like Excel.
919 for worksheet in self.worksheets():
920 if sheetname.lower() == worksheet.name.lower():
921 raise DuplicateWorksheetName(
922 f"Sheetname '{sheetname}', with case ignored, is already in use."
923 )
925 return sheetname
927 def _prepare_format_properties(self) -> None:
928 # Prepare all Format properties prior to passing them to styles.py.
930 # Separate format objects into XF and DXF formats.
931 self._prepare_formats()
933 # Set the font index for the format objects.
934 self._prepare_fonts()
936 # Set the number format index for the format objects.
937 self._prepare_num_formats()
939 # Set the border index for the format objects.
940 self._prepare_borders()
942 # Set the fill index for the format objects.
943 self._prepare_fills()
945 def _prepare_formats(self) -> None:
946 # Iterate through the XF Format objects and separate them into
947 # XF and DXF formats. The XF and DF formats then need to be sorted
948 # back into index order rather than creation order.
949 xf_formats = []
950 dxf_formats = []
952 # Sort into XF and DXF formats.
953 for xf_format in self.formats:
954 if xf_format.xf_index is not None:
955 xf_formats.append(xf_format)
957 if xf_format.dxf_index is not None:
958 dxf_formats.append(xf_format)
960 # Pre-extend the format lists.
961 self.xf_formats = [None] * len(xf_formats)
962 self.dxf_formats = [None] * len(dxf_formats)
964 # Rearrange formats into index order.
965 for xf_format in xf_formats:
966 index = xf_format.xf_index
967 self.xf_formats[index] = xf_format
969 for dxf_format in dxf_formats:
970 index = dxf_format.dxf_index
971 self.dxf_formats[index] = dxf_format
973 def _set_default_xf_indices(self) -> None:
974 # Set the default index for each format. Only used for testing.
976 formats = list(self.formats)
978 # Delete the default url format.
979 del formats[1]
981 # Skip the default date format if set.
982 if self.default_date_format is not None:
983 del formats[1]
985 # Set the remaining formats.
986 for xf_format in formats:
987 xf_format._get_xf_index()
989 def _prepare_fonts(self) -> None:
990 # Iterate through the XF Format objects and give them an index to
991 # non-default font elements.
992 fonts = {}
993 index = 0
995 for xf_format in self.xf_formats:
996 key = xf_format._get_font_key()
997 if key in fonts:
998 # Font has already been used.
999 xf_format.font_index = fonts[key]
1000 xf_format.has_font = False
1001 else:
1002 # This is a new font.
1003 fonts[key] = index
1004 xf_format.font_index = index
1005 xf_format.has_font = True
1006 index += 1
1008 self.font_count = index
1010 # For DXF formats we only need to check if the properties have changed.
1011 for xf_format in self.dxf_formats:
1012 # The only font properties that can change for a DXF format are:
1013 # color, bold, italic, underline and strikethrough.
1014 if (
1015 xf_format.font_color
1016 or xf_format.bold
1017 or xf_format.italic
1018 or xf_format.underline
1019 or xf_format.font_strikeout
1020 ):
1021 xf_format.has_dxf_font = True
1023 def _prepare_num_formats(self) -> None:
1024 # User defined records in Excel start from index 0xA4.
1025 unique_num_formats = {}
1026 num_formats = []
1027 index = 164
1029 for xf_format in self.xf_formats + self.dxf_formats:
1030 num_format = xf_format.num_format
1032 # Check if num_format is an index to a built-in number format.
1033 if not isinstance(num_format, str):
1034 num_format = int(num_format)
1036 # Number format '0' is indexed as 1 in Excel.
1037 if num_format == 0:
1038 num_format = 1
1040 xf_format.num_format_index = num_format
1041 continue
1043 if num_format == "0":
1044 # Number format '0' is indexed as 1 in Excel.
1045 xf_format.num_format_index = 1
1046 continue
1048 if num_format == "General":
1049 # The 'General' format has an number format index of 0.
1050 xf_format.num_format_index = 0
1051 continue
1053 if num_format in unique_num_formats:
1054 # Number xf_format has already been used.
1055 xf_format.num_format_index = unique_num_formats[num_format]
1056 else:
1057 # Add a new number xf_format.
1058 unique_num_formats[num_format] = index
1059 xf_format.num_format_index = index
1060 index += 1
1062 # Only increase font count for XF formats (not DXF formats).
1063 if xf_format.xf_index:
1064 num_formats.append(num_format)
1066 self.num_formats = num_formats
1068 def _prepare_borders(self) -> None:
1069 # Iterate through the XF Format objects and give them an index to
1070 # non-default border elements.
1071 borders = {}
1072 index = 0
1074 for xf_format in self.xf_formats:
1075 key = xf_format._get_border_key()
1077 if key in borders:
1078 # Border has already been used.
1079 xf_format.border_index = borders[key]
1080 xf_format.has_border = False
1081 else:
1082 # This is a new border.
1083 borders[key] = index
1084 xf_format.border_index = index
1085 xf_format.has_border = True
1086 index += 1
1088 self.border_count = index
1090 # For DXF formats we only need to check if the properties have changed.
1091 has_border = re.compile(r"[^0None:]")
1093 for xf_format in self.dxf_formats:
1094 key = xf_format._get_border_key()
1096 if has_border.search(key):
1097 xf_format.has_dxf_border = True
1099 def _prepare_fills(self) -> None:
1100 # Iterate through the XF Format objects and give them an index to
1101 # non-default fill elements.
1102 # The user defined fill properties start from 2 since there are 2
1103 # default fills: patternType="none" and patternType="gray125".
1104 fills = {}
1105 index = 2 # Start from 2. See above.
1107 # Add the default fills.
1108 fills["0:None:None"] = 0
1109 fills["17:None:None"] = 1
1111 # Store the DXF colors separately since them may be reversed below.
1112 for xf_format in self.dxf_formats:
1113 if xf_format.pattern or xf_format.bg_color or xf_format.fg_color:
1114 xf_format.has_dxf_fill = True
1115 xf_format.dxf_bg_color = xf_format.bg_color
1116 xf_format.dxf_fg_color = xf_format.fg_color
1118 for xf_format in self.xf_formats:
1119 # The following logical statements jointly take care of special
1120 # cases in relation to cell colors and patterns:
1121 # 1. For a solid fill (_pattern == 1) Excel reverses the role of
1122 # foreground and background colors, and
1123 # 2. If the user specifies a foreground or background color
1124 # without a pattern they probably wanted a solid fill, so we fill
1125 # in the defaults.
1126 if xf_format.pattern == 1 and xf_format.bg_color and xf_format.fg_color:
1127 tmp = xf_format.fg_color
1128 xf_format.fg_color = xf_format.bg_color
1129 xf_format.bg_color = tmp
1131 if xf_format.pattern <= 1 and xf_format.bg_color and not xf_format.fg_color:
1132 xf_format.fg_color = xf_format.bg_color
1133 xf_format.bg_color = None
1134 xf_format.pattern = 1
1136 if xf_format.pattern <= 1 and not xf_format.bg_color and xf_format.fg_color:
1137 xf_format.pattern = 1
1139 key = xf_format._get_fill_key()
1141 if key in fills:
1142 # Fill has already been used.
1143 xf_format.fill_index = fills[key]
1144 xf_format.has_fill = False
1145 else:
1146 # This is a new fill.
1147 fills[key] = index
1148 xf_format.fill_index = index
1149 xf_format.has_fill = True
1150 index += 1
1152 self.fill_count = index
1154 def _has_feature_property_bags(self):
1155 # Check for any format properties that require a feature bag. Currently
1156 # this only applies to checkboxes.
1157 if not self.feature_property_bags:
1158 for xf_format in self.formats:
1159 if xf_format.checkbox:
1160 self.feature_property_bags.add("XFComplements")
1162 if xf_format.dxf_index is not None and xf_format.checkbox:
1163 self.feature_property_bags.add("DXFComplements")
1165 return self.feature_property_bags
1167 def _prepare_defined_names(self) -> None:
1168 # Iterate through the worksheets and store any defined names in
1169 # addition to any user defined names. Stores the defined names
1170 # for the Workbook.xml and the named ranges for App.xml.
1171 defined_names = self.defined_names
1173 for sheet in self.worksheets():
1174 # Check for Print Area settings.
1175 if sheet.autofilter_area:
1176 hidden = 1
1177 sheet_range = sheet.autofilter_area
1178 # Store the defined names.
1179 defined_names.append(
1180 ["_xlnm._FilterDatabase", sheet.index, sheet_range, hidden]
1181 )
1183 # Check for Print Area settings.
1184 if sheet.print_area_range:
1185 hidden = 0
1186 sheet_range = sheet.print_area_range
1187 # Store the defined names.
1188 defined_names.append(
1189 ["_xlnm.Print_Area", sheet.index, sheet_range, hidden]
1190 )
1192 # Check for repeat rows/cols referred to as Print Titles.
1193 if sheet.repeat_col_range or sheet.repeat_row_range:
1194 hidden = 0
1195 sheet_range = ""
1196 if sheet.repeat_col_range and sheet.repeat_row_range:
1197 sheet_range = sheet.repeat_col_range + "," + sheet.repeat_row_range
1198 else:
1199 sheet_range = sheet.repeat_col_range + sheet.repeat_row_range
1200 # Store the defined names.
1201 defined_names.append(
1202 ["_xlnm.Print_Titles", sheet.index, sheet_range, hidden]
1203 )
1205 defined_names = self._sort_defined_names(defined_names)
1206 self.defined_names = defined_names
1207 self.named_ranges = self._extract_named_ranges(defined_names)
1209 def _sort_defined_names(self, names):
1210 # Sort the list of list of internal and user defined names in
1211 # the same order as used by Excel.
1213 # Add a normalize name string to each list for sorting.
1214 for name_list in names:
1215 (defined_name, _, sheet_name, _) = name_list
1217 # Normalize the defined name by removing any leading '_xmln.'
1218 # from internal names and lowercasing the string.
1219 defined_name = defined_name.replace("_xlnm.", "").lower()
1221 # Normalize the sheetname by removing the leading quote and
1222 # lowercasing the string.
1223 sheet_name = sheet_name.lstrip("'").lower()
1225 name_list.append(defined_name + "::" + sheet_name)
1227 # Sort based on the normalized key.
1228 names.sort(key=operator.itemgetter(4))
1230 # Remove the extra key used for sorting.
1231 for name_list in names:
1232 name_list.pop()
1234 return names
1236 def _prepare_drawings(self) -> None:
1237 # Iterate through the worksheets and set up chart and image drawings.
1238 chart_ref_id = 0
1239 ref_id = 0
1240 drawing_id = 0
1241 image_ids = {}
1242 header_image_ids = {}
1243 background_ids = {}
1245 # Store the image types for any embedded images.
1246 for image in self.embedded_images.images:
1247 image_extension = image._image_extension
1248 self.image_types[image_extension] = True
1250 if image.description is not None:
1251 self.has_embedded_descriptions = True
1253 image_ref_id = len(self.embedded_images.images)
1255 for sheet in self.worksheets():
1256 chart_count = len(sheet.charts)
1257 image_count = len(sheet.images)
1258 shape_count = len(sheet.shapes)
1260 header_image_count = len(sheet.header_images)
1261 footer_image_count = len(sheet.footer_images)
1262 has_background = sheet.background_image
1263 has_drawing = False
1265 if not (
1266 chart_count
1267 or image_count
1268 or shape_count
1269 or header_image_count
1270 or footer_image_count
1271 or has_background
1272 ):
1273 continue
1275 # Don't increase the drawing_id header/footer images.
1276 if chart_count or image_count or shape_count:
1277 drawing_id += 1
1278 has_drawing = True
1280 # Prepare the background images.
1281 if sheet.background_image:
1282 image = sheet.background_image
1283 image_extension = image._image_extension
1284 image_digest = image._digest
1286 self.image_types[image_extension] = True
1288 if image_digest in background_ids:
1289 ref_id = background_ids[image_digest]
1290 else:
1291 image_ref_id += 1
1292 ref_id = image_ref_id
1293 background_ids[image_digest] = image_ref_id
1294 self.images.append(image)
1296 sheet._prepare_background(ref_id, image_extension)
1298 # Prepare the worksheet images.
1299 for index in range(image_count):
1300 image = sheet.images[index]
1301 image_extension = image._image_extension
1302 image_digest = image._digest
1304 self.image_types[image_extension] = True
1306 if image_digest in image_ids:
1307 ref_id = image_ids[image_digest]
1308 else:
1309 image_ref_id += 1
1310 ref_id = image_ref_id
1311 image_ids[image_digest] = image_ref_id
1312 self.images.append(image)
1314 sheet._prepare_image(
1315 image,
1316 ref_id,
1317 drawing_id,
1318 )
1320 # Prepare the worksheet charts.
1321 for index in range(chart_count):
1322 chart_ref_id += 1
1323 sheet._prepare_chart(index, chart_ref_id, drawing_id)
1325 # Prepare the worksheet shapes.
1326 for index in range(shape_count):
1327 sheet._prepare_shape(index, drawing_id)
1329 # Prepare the header images.
1330 for image in sheet.header_images:
1331 image_extension = image._image_extension
1332 image_digest = image._digest
1334 self.image_types[image_extension] = True
1336 if image_digest in header_image_ids:
1337 ref_id = header_image_ids[image_digest]
1338 else:
1339 image_ref_id += 1
1340 ref_id = image_ref_id
1341 header_image_ids[image_digest] = image_ref_id
1342 self.images.append(image)
1344 sheet._prepare_header_image(ref_id, image)
1346 # Prepare the footer images.
1347 for image in sheet.footer_images:
1348 image_extension = image._image_extension
1349 image_digest = image._digest
1351 self.image_types[image_extension] = True
1353 if image_digest in header_image_ids:
1354 ref_id = header_image_ids[image_digest]
1355 else:
1356 image_ref_id += 1
1357 ref_id = image_ref_id
1358 header_image_ids[image_digest] = image_ref_id
1359 self.images.append(image)
1361 sheet._prepare_header_image(ref_id, image)
1363 if has_drawing:
1364 drawing = sheet.drawing
1365 self.drawings.append(drawing)
1367 # Remove charts that were created but not inserted into worksheets.
1368 for chart in self.charts[:]:
1369 if chart.id == -1:
1370 self.charts.remove(chart)
1372 # Sort the workbook charts references into the order that the were
1373 # written to the worksheets above.
1374 self.charts = sorted(self.charts, key=lambda chart: chart.id)
1376 self.drawing_count = drawing_id
1378 def _extract_named_ranges(self, defined_names):
1379 # Extract the named ranges from the sorted list of defined names.
1380 # These are used in the App.xml file.
1381 named_ranges = []
1383 for defined_name in defined_names:
1384 name = defined_name[0]
1385 index = defined_name[1]
1386 sheet_range = defined_name[2]
1388 # Skip autoFilter ranges.
1389 if name == "_xlnm._FilterDatabase":
1390 continue
1392 # We are only interested in defined names with ranges.
1393 if "!" in sheet_range:
1394 sheet_name, _ = sheet_range.split("!", 1)
1396 # Match Print_Area and Print_Titles xlnm types.
1397 if name.startswith("_xlnm."):
1398 xlnm_type = name.replace("_xlnm.", "")
1399 name = sheet_name + "!" + xlnm_type
1400 elif index != -1:
1401 name = sheet_name + "!" + name
1403 named_ranges.append(name)
1405 return named_ranges
1407 def _get_sheet_index(self, sheetname):
1408 # Convert a sheet name to its index. Return None otherwise.
1409 sheetname = sheetname.strip("'")
1411 if sheetname in self.sheetnames:
1412 return self.sheetnames[sheetname].index
1414 return None
1416 def _prepare_vml(self) -> None:
1417 # Iterate through the worksheets and set up the VML objects.
1418 comment_id = 0
1419 vml_drawing_id = 0
1420 vml_data_id = 1
1421 vml_header_id = 0
1422 vml_shape_id = 1024
1423 vml_files = 0
1424 comment_files = 0
1426 for sheet in self.worksheets():
1427 if not sheet.has_vml and not sheet.has_header_vml:
1428 continue
1430 vml_files += 1
1432 if sheet.has_vml:
1433 if sheet.has_comments:
1434 comment_files += 1
1435 comment_id += 1
1436 self.has_comments = True
1438 vml_drawing_id += 1
1440 count = sheet._prepare_vml_objects(
1441 vml_data_id, vml_shape_id, vml_drawing_id, comment_id
1442 )
1444 # Each VML should start with a shape id incremented by 1024.
1445 vml_data_id += 1 * int((1024 + count) / 1024)
1446 vml_shape_id += 1024 * int((1024 + count) / 1024)
1448 if sheet.has_header_vml:
1449 vml_header_id += 1
1450 vml_drawing_id += 1
1451 sheet._prepare_header_vml_objects(vml_header_id, vml_drawing_id)
1453 self.num_vml_files = vml_files
1454 self.num_comment_files = comment_files
1456 def _prepare_tables(self) -> None:
1457 # Set the table ids for the worksheet tables.
1458 table_id = 0
1459 seen = {}
1461 for sheet in self.worksheets():
1462 table_count = len(sheet.tables)
1464 if not table_count:
1465 continue
1467 sheet._prepare_tables(table_id + 1, seen)
1468 table_id += table_count
1470 def _prepare_metadata(self) -> None:
1471 # Set the metadata rel link.
1472 self.has_embedded_images = self.embedded_images.has_images()
1473 self.has_metadata = self.has_embedded_images
1475 for sheet in self.worksheets():
1476 if sheet.has_dynamic_arrays:
1477 self.has_metadata = True
1478 self.has_dynamic_functions = True
1480 def _add_chart_data(self) -> None:
1481 # Add "cached" data to charts to provide the numCache and strCache
1482 # data for series and title/axis ranges.
1483 worksheets = {}
1484 seen_ranges = {}
1485 charts = []
1487 # Map worksheet names to worksheet objects.
1488 for worksheet in self.worksheets():
1489 worksheets[worksheet.name] = worksheet
1491 # Build a list of the worksheet charts including any combined charts.
1492 for chart in self.charts:
1493 charts.append(chart)
1494 if chart.combined:
1495 charts.append(chart.combined)
1497 for chart in charts:
1498 for c_range in chart.formula_ids.keys():
1499 r_id = chart.formula_ids[c_range]
1501 # Skip if the series has user defined data.
1502 if chart.formula_data[r_id] is not None:
1503 if c_range not in seen_ranges or seen_ranges[c_range] is None:
1504 data = chart.formula_data[r_id]
1505 seen_ranges[c_range] = data
1506 continue
1508 # Check to see if the data is already cached locally.
1509 if c_range in seen_ranges:
1510 chart.formula_data[r_id] = seen_ranges[c_range]
1511 continue
1513 # Convert the range formula to a sheet name and cell range.
1514 (sheetname, cells) = self._get_chart_range(c_range)
1516 # Skip if we couldn't parse the formula.
1517 if sheetname is None:
1518 continue
1520 # Handle non-contiguous ranges like:
1521 # (Sheet1!$A$1:$A$2,Sheet1!$A$4:$A$5).
1522 # We don't try to parse them. We just return an empty list.
1523 if sheetname.startswith("("):
1524 chart.formula_data[r_id] = []
1525 seen_ranges[c_range] = []
1526 continue
1528 # Warn if the name is unknown since it indicates a user error
1529 # in a chart series formula.
1530 if sheetname not in worksheets:
1531 warn(
1532 f"Unknown worksheet reference '{sheetname}' in range "
1533 f"'{c_range}' passed to add_series()"
1534 )
1535 chart.formula_data[r_id] = []
1536 seen_ranges[c_range] = []
1537 continue
1539 # Find the worksheet object based on the sheet name.
1540 worksheet = worksheets[sheetname]
1542 # Get the data from the worksheet table.
1543 data = worksheet._get_range_data(*cells)
1545 # Add the data to the chart.
1546 chart.formula_data[r_id] = data
1548 # Store range data locally to avoid lookup if seen again.
1549 seen_ranges[c_range] = data
1551 def _get_chart_range(self, c_range):
1552 # Convert a range formula such as Sheet1!$B$1:$B$5 into a sheet name
1553 # and cell range such as ( 'Sheet1', 0, 1, 4, 1 ).
1555 # Split the range formula into sheetname and cells at the last '!'.
1556 pos = c_range.rfind("!")
1557 if pos > 0:
1558 sheetname = c_range[:pos]
1559 cells = c_range[pos + 1 :]
1560 else:
1561 return None, None
1563 # Split the cell range into 2 cells or else use single cell for both.
1564 if cells.find(":") > 0:
1565 (cell_1, cell_2) = cells.split(":", 1)
1566 else:
1567 (cell_1, cell_2) = (cells, cells)
1569 # Remove leading/trailing quotes and convert escaped quotes to single.
1570 sheetname = sheetname.strip("'")
1571 sheetname = sheetname.replace("''", "'")
1573 try:
1574 # Get the row, col values from the Excel ranges. We do this in a
1575 # try block for ranges that can't be parsed such as defined names.
1576 (row_start, col_start) = xl_cell_to_rowcol(cell_1)
1577 (row_end, col_end) = xl_cell_to_rowcol(cell_2)
1578 except AttributeError:
1579 return None, None
1581 # We only handle 1D ranges.
1582 if row_start != row_end and col_start != col_end:
1583 return None, None
1585 return sheetname, [row_start, col_start, row_end, col_end]
1587 def _prepare_sst_string_data(self) -> None:
1588 # Convert the SST string data from a dict to a list.
1589 self.str_table._sort_string_data()
1591 def _get_packager(self):
1592 # Get and instance of the Packager class to create the xlsx package.
1593 # This allows the default packager to be over-ridden.
1594 return Packager()
1596 ###########################################################################
1597 #
1598 # XML methods.
1599 #
1600 ###########################################################################
1602 def _write_workbook(self) -> None:
1603 # Write <workbook> element.
1605 schema = "http://schemas.openxmlformats.org"
1606 xmlns = schema + "/spreadsheetml/2006/main"
1607 xmlns_r = schema + "/officeDocument/2006/relationships"
1609 attributes = [
1610 ("xmlns", xmlns),
1611 ("xmlns:r", xmlns_r),
1612 ]
1614 self._xml_start_tag("workbook", attributes)
1616 def _write_file_version(self) -> None:
1617 # Write the <fileVersion> element.
1619 app_name = "xl"
1620 last_edited = 4
1621 lowest_edited = 4
1622 rup_build = 4505
1624 attributes = [
1625 ("appName", app_name),
1626 ("lastEdited", last_edited),
1627 ("lowestEdited", lowest_edited),
1628 ("rupBuild", rup_build),
1629 ]
1631 if self.vba_project:
1632 attributes.append(("codeName", "{37E998C4-C9E5-D4B9-71C8-EB1FF731991C}"))
1634 self._xml_empty_tag("fileVersion", attributes)
1636 def _write_file_sharing(self) -> None:
1637 # Write the <fileSharing> element.
1638 if self.read_only == 0:
1639 return
1641 attributes = [("readOnlyRecommended", 1)]
1643 self._xml_empty_tag("fileSharing", attributes)
1645 def _write_workbook_pr(self) -> None:
1646 # Write <workbookPr> element.
1647 default_theme_version = 124226
1648 attributes = []
1650 if self.vba_codename:
1651 attributes.append(("codeName", self.vba_codename))
1652 if self.date_1904:
1653 attributes.append(("date1904", 1))
1655 attributes.append(("defaultThemeVersion", default_theme_version))
1657 self._xml_empty_tag("workbookPr", attributes)
1659 def _write_book_views(self) -> None:
1660 # Write <bookViews> element.
1661 self._xml_start_tag("bookViews")
1662 self._write_workbook_view()
1663 self._xml_end_tag("bookViews")
1665 def _write_workbook_view(self) -> None:
1666 # Write <workbookView> element.
1667 attributes = [
1668 ("xWindow", self.x_window),
1669 ("yWindow", self.y_window),
1670 ("windowWidth", self.window_width),
1671 ("windowHeight", self.window_height),
1672 ]
1674 # Store the tabRatio attribute when it isn't the default.
1675 if self.tab_ratio != 600:
1676 attributes.append(("tabRatio", self.tab_ratio))
1678 # Store the firstSheet attribute when it isn't the default.
1679 if self.worksheet_meta.firstsheet > 0:
1680 firstsheet = self.worksheet_meta.firstsheet + 1
1681 attributes.append(("firstSheet", firstsheet))
1683 # Store the activeTab attribute when it isn't the first sheet.
1684 if self.worksheet_meta.activesheet > 0:
1685 attributes.append(("activeTab", self.worksheet_meta.activesheet))
1687 self._xml_empty_tag("workbookView", attributes)
1689 def _write_sheets(self) -> None:
1690 # Write <sheets> element.
1691 self._xml_start_tag("sheets")
1693 id_num = 1
1694 for worksheet in self.worksheets():
1695 self._write_sheet(worksheet.name, id_num, worksheet.hidden)
1696 id_num += 1
1698 self._xml_end_tag("sheets")
1700 def _write_sheet(self, name, sheet_id, hidden) -> None:
1701 # Write <sheet> element.
1702 attributes = [
1703 ("name", name),
1704 ("sheetId", sheet_id),
1705 ]
1707 if hidden == 1:
1708 attributes.append(("state", "hidden"))
1709 elif hidden == 2:
1710 attributes.append(("state", "veryHidden"))
1712 attributes.append(("r:id", "rId" + str(sheet_id)))
1714 self._xml_empty_tag("sheet", attributes)
1716 def _write_calc_pr(self) -> None:
1717 # Write the <calcPr> element.
1718 attributes = [("calcId", self.calc_id)]
1720 if self.calc_mode == "manual":
1721 attributes.append(("calcMode", self.calc_mode))
1722 attributes.append(("calcOnSave", "0"))
1723 elif self.calc_mode == "autoNoTable":
1724 attributes.append(("calcMode", self.calc_mode))
1726 if self.calc_on_load:
1727 attributes.append(("fullCalcOnLoad", "1"))
1729 self._xml_empty_tag("calcPr", attributes)
1731 def _write_defined_names(self) -> None:
1732 # Write the <definedNames> element.
1733 if not self.defined_names:
1734 return
1736 self._xml_start_tag("definedNames")
1738 for defined_name in self.defined_names:
1739 self._write_defined_name(defined_name)
1741 self._xml_end_tag("definedNames")
1743 def _write_defined_name(self, defined_name) -> None:
1744 # Write the <definedName> element.
1745 name = defined_name[0]
1746 sheet_id = defined_name[1]
1747 sheet_range = defined_name[2]
1748 hidden = defined_name[3]
1750 attributes = [("name", name)]
1752 if sheet_id != -1:
1753 attributes.append(("localSheetId", sheet_id))
1754 if hidden:
1755 attributes.append(("hidden", 1))
1757 self._xml_data_element("definedName", sheet_range, attributes)
1760# A metadata class to share data between worksheets.
1761class WorksheetMeta:
1762 """
1763 A class to track worksheets data such as the active sheet and the
1764 first sheet.
1766 """
1768 def __init__(self) -> None:
1769 self.activesheet = 0
1770 self.firstsheet = 0
1773# A helper class to share embedded images between worksheets.
1774class EmbeddedImages:
1775 """
1776 A class to track duplicate embedded images between worksheets.
1778 """
1780 def __init__(self) -> None:
1781 self.images = []
1782 self.image_indexes = {}
1784 def get_image_index(self, image: Image):
1785 """
1786 Get the index of an embedded image.
1788 Args:
1789 image: The image to lookup.
1791 Returns:
1792 The image index.
1794 """
1795 image_index = self.image_indexes.get(image._digest)
1797 if image_index is None:
1798 self.images.append(image)
1799 image_index = len(self.images)
1800 self.image_indexes[image._digest] = image_index
1802 return image_index
1804 def has_images(self):
1805 """
1806 Check if the worksheet has embedded images.
1808 Args:
1809 None.
1811 Returns:
1812 Boolean.
1814 """
1815 return len(self.images) > 0