Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.11/site-packages/xlsxwriter/workbook.py: 50%
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
15import zipfile
16from datetime import datetime, timezone
17from decimal import Decimal
18from fractions import Fraction
19from io import StringIO
20from typing import IO, Any, AnyStr, Dict, List, Literal, Optional, Tuple, Union
21from warnings import warn
22from zipfile import ZIP_DEFLATED, LargeZipFile, ZipFile, ZipInfo
24# Package imports.
25from xlsxwriter import xmlwriter
26from xlsxwriter.chart_area import ChartArea
27from xlsxwriter.chart_bar import ChartBar
28from xlsxwriter.chart_column import ChartColumn
29from xlsxwriter.chart_doughnut import ChartDoughnut
30from xlsxwriter.chart_line import ChartLine
31from xlsxwriter.chart_pie import ChartPie
32from xlsxwriter.chart_radar import ChartRadar
33from xlsxwriter.chart_scatter import ChartScatter
34from xlsxwriter.chart_stock import ChartStock
35from xlsxwriter.chartsheet import Chartsheet
36from xlsxwriter.exceptions import (
37 DuplicateWorksheetName,
38 FileCreateError,
39 FileSizeError,
40 InvalidWorksheetName,
41 ThemeFileError,
42)
43from xlsxwriter.format import Format
44from xlsxwriter.image import Image
45from xlsxwriter.packager import Packager
46from xlsxwriter.sharedstrings import SharedStringTable
47from xlsxwriter.theme import THEME_XML_2007, THEME_XML_2023
48from xlsxwriter.utility import xl_cell_to_rowcol
49from xlsxwriter.worksheet import Worksheet
52class Workbook(xmlwriter.XMLwriter):
53 """
54 A class for writing the Excel XLSX Workbook file.
57 """
59 ###########################################################################
60 #
61 # Public API.
62 #
63 ###########################################################################
64 chartsheet_class = Chartsheet
65 worksheet_class = Worksheet
67 def __init__(
68 self,
69 filename: Optional[Union[str, IO[AnyStr], os.PathLike]] = None,
70 options: Optional[Dict[str, Any]] = None,
71 ) -> None:
72 """
73 Constructor.
75 """
76 if options is None:
77 options = {}
79 super().__init__()
81 self.filename = filename
83 self.tmpdir = options.get("tmpdir", None)
84 self.date_1904 = options.get("date_1904", False)
85 self.strings_to_numbers = options.get("strings_to_numbers", False)
86 self.strings_to_formulas = options.get("strings_to_formulas", True)
87 self.strings_to_urls = options.get("strings_to_urls", True)
88 self.nan_inf_to_errors = options.get("nan_inf_to_errors", False)
89 self.default_date_format = options.get("default_date_format", None)
90 self.constant_memory = options.get("constant_memory", False)
91 self.in_memory = options.get("in_memory", False)
92 self.excel2003_style = options.get("excel2003_style", False)
93 self.excel_2023_theme = options.get("excel_2023_theme", False)
94 self.remove_timezone = options.get("remove_timezone", False)
95 self.use_future_functions = options.get("use_future_functions", False)
96 self.default_row_height = options.get("default_row_height", 20)
97 self.default_col_width = options.get("default_column_width", 64)
98 self.default_format_properties = options.get(
99 "default_format_properties", {"font_name": "Calibri", "font_size": 11}
100 )
102 self.max_url_length = options.get("max_url_length", 2079)
103 if self.max_url_length < 255:
104 self.max_url_length = 2079
106 if options.get("use_zip64"):
107 self.allow_zip64 = True
108 else:
109 self.allow_zip64 = False
111 self.worksheet_meta = WorksheetMeta()
112 self.selected = 0
113 self.fileclosed = 0
114 self.filehandle = None
115 self.internal_fh = 0
116 self.sheet_name = "Sheet"
117 self.chart_name = "Chart"
118 self.sheetname_count = 0
119 self.chartname_count = 0
120 self.worksheets_objs = []
121 self.charts = []
122 self.drawings = []
123 self.sheetnames = {}
124 self.formats = []
125 self.xf_formats = []
126 self.xf_format_indices = {}
127 self.dxf_formats = []
128 self.dxf_format_indices = {}
129 self.palette = []
130 self.font_count = 0
131 self.num_formats = []
132 self.defined_names = []
133 self.named_ranges = []
134 self.custom_colors = []
135 self.doc_properties = {}
136 self.custom_properties = []
137 self.createtime = datetime.now(timezone.utc)
138 self.num_vml_files = 0
139 self.num_comment_files = 0
140 self.x_window = 240
141 self.y_window = 15
142 self.window_width = 16095
143 self.window_height = 9660
144 self.tab_ratio = 600
145 self.str_table = SharedStringTable()
146 self.vba_project = None
147 self.vba_project_is_stream = False
148 self.vba_project_signature = None
149 self.vba_project_signature_is_stream = False
150 self.vba_codename = None
151 self.image_types = {}
152 self.images = []
153 self.border_count = 0
154 self.fill_count = 0
155 self.drawing_count = 0
156 self.calc_mode = "auto"
157 self.calc_on_load = True
158 self.calc_id = 124519
159 self.has_comments = False
160 self.read_only = 0
161 self.has_metadata = False
162 self.has_embedded_images = False
163 self.has_dynamic_functions = False
164 self.has_embedded_descriptions = False
165 self.embedded_images = EmbeddedImages()
166 self.feature_property_bags = set()
167 self.default_theme_version: str = "124226"
168 self.theme_xml: str = THEME_XML_2007
170 # We can't do 'constant_memory' mode while doing 'in_memory' mode.
171 if self.in_memory:
172 self.constant_memory = False
174 # Add the default cell format.
175 if self.excel_2023_theme:
176 format_properties = {
177 "font_name": "Aptos Narrow",
178 "font_size": 11,
179 "font_scheme": "minor",
180 }
181 self.default_format_properties = format_properties.copy()
182 self.default_col_width = 64
183 self.default_row_height = 20
184 self.default_theme_version = "202300"
185 self.theme_xml: str = THEME_XML_2023
187 format_properties["xf_index"] = 0
188 self.add_format(format_properties)
190 elif self.excel2003_style:
191 # This is a deprecated workaround for Excel 2003 style default format.
192 format_properties = {
193 "font_name": "Arial",
194 "font_size": 10,
195 "font_family": 0,
196 "font_scheme": False,
197 "theme": -1,
198 }
199 format_properties["xf_index"] = 0
200 self.add_format(format_properties)
202 self.default_format_properties = {
203 "font_name": "Arial",
204 "font_size": 10,
205 "font_scheme": False,
206 "theme": -1,
207 }
208 else:
209 format_properties = self.default_format_properties.copy()
210 format_properties["xf_index"] = 0
211 self.add_format(format_properties)
213 # Store the theme font name to cell format properties.
214 self.default_format_properties["theme_font_name"] = (
215 self.default_format_properties.get("font_name", "Calibri")
216 )
218 # Add a default URL format.
219 format_properties = self.default_format_properties.copy()
220 format_properties["hyperlink"] = True
221 format_properties["font_scheme"] = "none"
222 self.default_url_format = self.add_format(format_properties)
224 # Add the default date format.
225 if self.default_date_format is not None:
226 self.default_date_format = self.add_format(
227 {"num_format": self.default_date_format}
228 )
230 (self.max_digit_width, self.cell_padding, self.max_col_width) = (
231 self._default_column_metrics(self.default_col_width)
232 )
234 def __enter__(self):
235 """Return self object to use with "with" statement."""
236 return self
238 def __exit__(self, type, value, traceback) -> None:
239 # pylint: disable=redefined-builtin
240 """Close workbook when exiting "with" statement."""
241 self.close()
243 def add_worksheet(
244 self, name: Optional[str] = None, worksheet_class=None
245 ) -> Worksheet:
246 """
247 Add a new worksheet to the Excel workbook.
249 Args:
250 name: The worksheet name. Defaults to 'Sheet1', etc.
252 Returns:
253 Reference to a worksheet object.
255 """
256 if worksheet_class is None:
257 worksheet_class = self.worksheet_class
259 return self._add_sheet(name, worksheet_class=worksheet_class)
261 def add_chartsheet(
262 self, name: Optional[str] = None, chartsheet_class=None
263 ) -> Chartsheet:
264 """
265 Add a new chartsheet to the Excel workbook.
267 Args:
268 name: The chartsheet name. Defaults to 'Sheet1', etc.
270 Returns:
271 Reference to a chartsheet object.
273 """
274 if chartsheet_class is None:
275 chartsheet_class = self.chartsheet_class
277 return self._add_sheet(name, worksheet_class=chartsheet_class)
279 def add_format(self, properties=None) -> Format:
280 """
281 Add a new Format to the Excel Workbook.
283 Args:
284 properties: The format properties.
286 Returns:
287 Reference to a Format object.
289 """
290 format_properties = self.default_format_properties.copy()
292 if properties:
293 format_properties.update(properties)
295 xf_format = Format(
296 format_properties, self.xf_format_indices, self.dxf_format_indices
297 )
299 # Store the format reference.
300 self.formats.append(xf_format)
302 return xf_format
304 def add_chart(self, options: Dict[str, Any]) -> Optional[
305 Union[
306 ChartArea,
307 ChartBar,
308 ChartColumn,
309 ChartDoughnut,
310 ChartLine,
311 ChartPie,
312 ChartRadar,
313 ChartScatter,
314 ChartStock,
315 ]
316 ]:
317 """
318 Create a chart object.
320 Args:
321 options: The chart type and subtype options.
323 Returns:
324 Reference to a Chart object.
326 """
328 # Type must be specified so we can create the required chart instance.
329 chart_type = options.get("type")
330 if chart_type is None:
331 warn("Chart type must be defined in add_chart()")
332 return None
334 if chart_type == "area":
335 chart = ChartArea(options)
336 elif chart_type == "bar":
337 chart = ChartBar(options)
338 elif chart_type == "column":
339 chart = ChartColumn(options)
340 elif chart_type == "doughnut":
341 chart = ChartDoughnut()
342 elif chart_type == "line":
343 chart = ChartLine(options)
344 elif chart_type == "pie":
345 chart = ChartPie()
346 elif chart_type == "radar":
347 chart = ChartRadar(options)
348 elif chart_type == "scatter":
349 chart = ChartScatter(options)
350 elif chart_type == "stock":
351 chart = ChartStock()
352 else:
353 warn(f"Unknown chart type '{chart_type}' in add_chart()")
354 return None
356 # Set the embedded chart name if present.
357 if "name" in options:
358 chart.chart_name = options["name"]
360 chart.embedded = True
361 chart.date_1904 = self.date_1904
362 chart.remove_timezone = self.remove_timezone
364 self.charts.append(chart)
366 return chart
368 def add_vba_project(self, vba_project: str, is_stream: bool = False) -> int:
369 """
370 Add a vbaProject binary to the Excel workbook.
372 Args:
373 vba_project: The vbaProject binary file name.
374 is_stream: vba_project is an in memory byte stream.
376 Returns:
377 0 on success.
379 """
380 if not is_stream and not os.path.exists(vba_project):
381 warn(f"VBA project binary file '{vba_project}' not found.")
382 return -1
384 if self.vba_codename is None:
385 self.vba_codename = "ThisWorkbook"
387 self.vba_project = vba_project
388 self.vba_project_is_stream = is_stream
390 return 0
392 def add_signed_vba_project(
393 self,
394 vba_project: str,
395 signature: str,
396 project_is_stream: bool = False,
397 signature_is_stream: bool = False,
398 ) -> Literal[0, -1]:
399 """
400 Add a vbaProject binary and a vbaProjectSignature binary to the
401 Excel workbook.
403 Args:
404 vba_project: The vbaProject binary file name.
405 signature: The vbaProjectSignature binary file name.
406 project_is_stream: vba_project is an in memory byte stream.
407 signature_is_stream: signature is an in memory byte stream.
409 Returns:
410 0 on success.
412 """
413 if self.add_vba_project(vba_project, project_is_stream) == -1:
414 return -1
416 if not signature_is_stream and not os.path.exists(signature):
417 warn(f"VBA project signature binary file '{signature}' not found.")
418 return -1
420 self.vba_project_signature = signature
421 self.vba_project_signature_is_stream = signature_is_stream
423 return 0
425 def use_custom_theme(self, theme: Union[str, os.PathLike, IO[AnyStr]]) -> None:
426 """
427 Add a custom theme to the Excel workbook.
429 Args:
430 theme: The custom theme as a file path (string or PathLike),
431 or in-memory string as a StringIO object.
433 Raises:
434 IOError: If the file cannot be read.
435 ThemeFileError: If the theme file is invalid or unsupported.
436 ValueError: If the theme parameter type is not supported.
438 """
439 theme_xml = ""
441 if isinstance(theme, (str, os.PathLike)):
442 theme_xml = self._read_theme_from_file(theme)
444 elif isinstance(theme, StringIO):
445 theme_xml = theme.getvalue()
447 else:
448 raise ValueError(
449 "Theme must be a file path (string or PathLike), or StringIO object."
450 )
452 # Simple check to see if the file is text/XML.
453 if not theme_xml.startswith("<?xml") or "<a:theme" not in theme_xml:
454 raise ThemeFileError(f"Invalid XML theme file: '{theme}'.")
456 # Check for Excel 2007 theme files that contain images as fills. These
457 # aren't currently supported.
458 if "<a:blipFill>" in theme_xml:
459 raise ThemeFileError(
460 "Theme file contains image fills which aren't currently "
461 f"supported: '{theme}'."
462 )
464 self.theme_xml = theme_xml
465 self.default_theme_version = ""
467 def close(self) -> None:
468 """
469 Call finalization code and close file.
471 Args:
472 None.
474 Returns:
475 Nothing.
477 """
478 # pylint: disable=raise-missing-from
479 if not self.fileclosed:
480 try:
481 self._store_workbook()
482 except IOError as e:
483 raise FileCreateError(e)
484 except LargeZipFile:
485 raise FileSizeError(
486 "Filesize would require ZIP64 extensions. "
487 "Use workbook.use_zip64()."
488 )
490 self.fileclosed = True
492 # Ensure all constant_memory temp files are closed.
493 if self.constant_memory:
494 for worksheet in self.worksheets():
495 worksheet._opt_close()
497 else:
498 warn("Calling close() on already closed file.")
500 def set_size(self, width: int, height: int) -> None:
501 """
502 Set the size of a workbook window.
504 Args:
505 width: Width of the window in pixels.
506 height: Height of the window in pixels.
508 Returns:
509 Nothing.
511 """
512 # Convert the width/height to twips at 96 dpi.
513 if width:
514 self.window_width = int(width * 1440 / 96)
515 else:
516 self.window_width = 16095
518 if height:
519 self.window_height = int(height * 1440 / 96)
520 else:
521 self.window_height = 9660
523 def set_tab_ratio(self, tab_ratio: Optional[Union[int, float]] = None) -> None:
524 """
525 Set the ratio between worksheet tabs and the horizontal slider.
527 Args:
528 tab_ratio: The tab ratio, 0 <= tab_ratio <= 100
530 Returns:
531 Nothing.
533 """
534 if tab_ratio is None:
535 return
537 if tab_ratio < 0 or tab_ratio > 100:
538 warn(f"Tab ratio '{tab_ratio}' outside: 0 <= tab_ratio <= 100")
539 else:
540 self.tab_ratio = int(tab_ratio * 10)
542 def set_properties(self, properties) -> None:
543 """
544 Set the document properties such as Title, Author etc.
546 Args:
547 properties: Dictionary of document properties.
549 Returns:
550 Nothing.
552 """
553 self.doc_properties = properties
555 def set_custom_property(
556 self,
557 name: str,
558 value: Union[bool, datetime, int, float, Decimal, Fraction, Any],
559 property_type: Optional[
560 Literal["bool", "date", "number", "number_int", "text"]
561 ] = None,
562 ) -> Literal[0, -1]:
563 """
564 Set a custom document property.
566 Args:
567 name: The name of the custom property.
568 value: The value of the custom property.
569 property_type: The type of the custom property. Optional.
571 Returns:
572 0 on success.
574 """
575 if name is None or value is None:
576 warn(
577 "The name and value parameters must be non-None in "
578 "set_custom_property()"
579 )
580 return -1
582 if property_type is None:
583 # Determine the property type from the Python type.
584 if isinstance(value, bool):
585 property_type = "bool"
586 elif isinstance(value, datetime):
587 property_type = "date"
588 elif isinstance(value, int):
589 property_type = "number_int"
590 elif isinstance(value, (float, int, Decimal, Fraction)):
591 property_type = "number"
592 else:
593 property_type = "text"
595 # Convert non-string values to strings to have a single data type.
596 if property_type == "bool":
597 value = str(value).lower()
599 if property_type == "date":
600 value = value.strftime("%Y-%m-%dT%H:%M:%SZ")
602 if property_type in ("number", "number_int"):
603 value = str(value)
605 if property_type == "text" and len(value) > 255:
606 warn(
607 f"Length of 'value' parameter exceeds Excel's limit of 255 "
608 f"characters in set_custom_property(): '{value}'"
609 )
611 if len(name) > 255:
612 warn(
613 f"Length of 'name' parameter exceeds Excel's limit of 255 "
614 f"characters in set_custom_property(): '{name}'"
615 )
617 self.custom_properties.append((name, value, property_type))
619 return 0
621 def set_calc_mode(
622 self, mode: Literal["manual", "auto_except_tables", "auto"], calc_id=None
623 ) -> None:
624 """
625 Set the Excel calculation mode for the workbook.
627 Args:
628 mode: String containing one of:
629 * manual
630 * auto_except_tables
631 * auto
633 Returns:
634 Nothing.
636 """
637 self.calc_mode = mode
639 if mode == "manual":
640 self.calc_on_load = False
641 elif mode == "auto_except_tables":
642 self.calc_mode = "autoNoTable"
644 # Leave undocumented for now. Rarely required.
645 if calc_id:
646 self.calc_id = calc_id
648 def define_name(self, name: str, formula: str) -> Literal[0, -1]:
649 # Create a defined name in Excel. We handle global/workbook level
650 # names and local/worksheet names.
651 """
652 Create a defined name in the workbook.
654 Args:
655 name: The defined name.
656 formula: The cell or range that the defined name refers to.
658 Returns:
659 0 on success.
661 """
662 sheet_index = None
663 sheetname = ""
665 # Remove the = sign from the formula if it exists.
666 if formula.startswith("="):
667 formula = formula.lstrip("=")
669 # Local defined names are formatted like "Sheet1!name".
670 sheet_parts = re.compile(r"^([^!]+)!([^!]+)$")
671 match = sheet_parts.match(name)
673 if match:
674 sheetname = match.group(1)
675 name = match.group(2)
676 sheet_index = self._get_sheet_index(sheetname)
678 # Warn if the sheet index wasn't found.
679 if sheet_index is None:
680 warn(f"Unknown sheet name '{sheetname}' in defined_name()")
681 return -1
682 else:
683 # Use -1 to indicate global names.
684 sheet_index = -1
686 # Warn if the defined name contains invalid chars as defined by Excel.
687 if not re.match(r"^[\w\\][\w\\.]*$", name, re.UNICODE) or re.match(
688 r"^\d", name
689 ):
690 warn(f"Invalid Excel characters in defined_name(): '{name}'")
691 return -1
693 # Warn if the defined name looks like a cell name.
694 if re.match(r"^[a-zA-Z][a-zA-Z]?[a-dA-D]?\d+$", name):
695 warn(f"Name looks like a cell name in defined_name(): '{name}'")
696 return -1
698 # Warn if the name looks like a R1C1 cell reference.
699 if re.match(r"^[rcRC]$", name) or re.match(r"^[rcRC]\d+[rcRC]\d+$", name):
700 warn(f"Invalid name '{name}' like a RC cell ref in defined_name()")
701 return -1
703 self.defined_names.append([name, sheet_index, formula, False])
705 return 0
707 def worksheets(self) -> List[Worksheet]:
708 """
709 Return a list of the worksheet objects in the workbook.
711 Args:
712 None.
714 Returns:
715 A list of worksheet objects.
717 """
718 return self.worksheets_objs
720 def get_worksheet_by_name(self, name: str) -> Optional[Worksheet]:
721 """
722 Return a worksheet object in the workbook using the sheetname.
724 Args:
725 name: The name of the worksheet.
727 Returns:
728 A worksheet object or None.
730 """
731 return self.sheetnames.get(name)
733 def get_default_url_format(self) -> Format:
734 """
735 Get the default url format used when a user defined format isn't
736 specified with write_url(). The format is the hyperlink style defined
737 by Excel for the default theme.
739 Args:
740 None.
742 Returns:
743 A format object.
745 """
746 return self.default_url_format
748 def use_zip64(self) -> None:
749 """
750 Allow ZIP64 extensions when writing xlsx file zip container.
752 Args:
753 None.
755 Returns:
756 Nothing.
758 """
759 self.allow_zip64 = True
761 def set_vba_name(self, name: Optional[str] = None) -> None:
762 """
763 Set the VBA name for the workbook. By default the workbook is referred
764 to as ThisWorkbook in VBA.
766 Args:
767 name: The VBA name for the workbook.
769 Returns:
770 Nothing.
772 """
773 if name is not None:
774 self.vba_codename = name
775 else:
776 self.vba_codename = "ThisWorkbook"
778 def read_only_recommended(self) -> None:
779 """
780 Set the Excel "Read-only recommended" option when saving a file.
782 Args:
783 None.
785 Returns:
786 Nothing.
788 """
789 self.read_only = 2
791 ###########################################################################
792 #
793 # Private API.
794 #
795 ###########################################################################
797 def _assemble_xml_file(self) -> None:
798 # Assemble and write the XML file.
800 # Prepare format object for passing to Style.pm.
801 self._prepare_format_properties()
803 # Write the XML declaration.
804 self._xml_declaration()
806 # Write the workbook element.
807 self._write_workbook()
809 # Write the fileVersion element.
810 self._write_file_version()
812 # Write the fileSharing element.
813 self._write_file_sharing()
815 # Write the workbookPr element.
816 self._write_workbook_pr()
818 # Write the bookViews element.
819 self._write_book_views()
821 # Write the sheets element.
822 self._write_sheets()
824 # Write the workbook defined names.
825 self._write_defined_names()
827 # Write the calcPr element.
828 self._write_calc_pr()
830 # Close the workbook tag.
831 self._xml_end_tag("workbook")
833 # Close the file.
834 self._xml_close()
836 def _store_workbook(self) -> None:
837 # pylint: disable=consider-using-with
838 # Create the xlsx/zip file.
839 try:
840 xlsx_file = ZipFile(
841 self.filename,
842 "w",
843 compression=ZIP_DEFLATED,
844 allowZip64=self.allow_zip64,
845 )
846 except IOError as e:
847 raise e
849 # Assemble worksheets into a workbook.
850 packager = self._get_packager()
852 # Add a default worksheet if non have been added.
853 if not self.worksheets():
854 self.add_worksheet()
856 # Ensure that at least one worksheet has been selected.
857 if self.worksheet_meta.activesheet == 0:
858 self.worksheets_objs[0].selected = 1
859 self.worksheets_objs[0].hidden = 0
861 # Set the active sheet.
862 for sheet in self.worksheets():
863 if sheet.index == self.worksheet_meta.activesheet:
864 sheet.active = 1
866 # Set the sheet vba_codename the workbook has a vbaProject binary.
867 if self.vba_project:
868 for sheet in self.worksheets():
869 if sheet.vba_codename is None:
870 sheet.set_vba_name()
872 # Convert the SST strings data structure.
873 self._prepare_sst_string_data()
875 # Prepare the worksheet VML elements such as comments and buttons.
876 self._prepare_vml()
878 # Set the defined names for the worksheets such as Print Titles.
879 self._prepare_defined_names()
881 # Prepare the drawings, charts and images.
882 self._prepare_drawings()
884 # Add cached data to charts.
885 self._add_chart_data()
887 # Prepare the worksheet tables.
888 self._prepare_tables()
890 # Prepare the metadata file links.
891 self._prepare_metadata()
893 # Package the workbook.
894 packager._add_workbook(self)
895 packager._set_tmpdir(self.tmpdir)
896 packager._set_in_memory(self.in_memory)
897 xml_files = packager._create_package()
899 # Free up the Packager object.
900 packager = None
902 # Add XML sub-files to the Zip file with their Excel filename.
903 for file_id, file_data in enumerate(xml_files):
904 os_filename, xml_filename, is_binary = file_data
906 if self.in_memory:
907 # Set sub-file timestamp to Excel's timestamp of 1/1/1980.
908 zipinfo = ZipInfo(xml_filename, (1980, 1, 1, 0, 0, 0))
910 # Copy compression type from parent ZipFile.
911 zipinfo.compress_type = xlsx_file.compression
913 if is_binary:
914 xlsx_file.writestr(zipinfo, os_filename.getvalue())
915 else:
916 xlsx_file.writestr(zipinfo, os_filename.getvalue().encode("utf-8"))
917 else:
918 # The sub-files are tempfiles on disk, i.e, not in memory.
920 # Set sub-file timestamp to 31/1/1980 due to portability
921 # issues setting it to Excel's timestamp of 1/1/1980.
922 timestamp = time.mktime((1980, 1, 31, 0, 0, 0, 0, 0, -1))
923 os.utime(os_filename, (timestamp, timestamp))
925 try:
926 xlsx_file.write(os_filename, xml_filename)
927 os.remove(os_filename)
928 except LargeZipFile as e:
929 # Close open temp files on zipfile.LargeZipFile exception.
930 for i in range(file_id, len(xml_files) - 1):
931 os.remove(xml_files[i][0])
932 raise e
934 xlsx_file.close()
936 def _add_sheet(self, name, worksheet_class=None):
937 # Utility for shared code in add_worksheet() and add_chartsheet().
939 if worksheet_class:
940 worksheet = worksheet_class()
941 else:
942 worksheet = self.worksheet_class()
944 sheet_index = len(self.worksheets_objs)
945 name = self._check_sheetname(name, isinstance(worksheet, Chartsheet))
947 # Initialization data to pass to the worksheet.
948 init_data = {
949 "name": name,
950 "index": sheet_index,
951 "str_table": self.str_table,
952 "worksheet_meta": self.worksheet_meta,
953 "constant_memory": self.constant_memory,
954 "tmpdir": self.tmpdir,
955 "date_1904": self.date_1904,
956 "strings_to_numbers": self.strings_to_numbers,
957 "strings_to_formulas": self.strings_to_formulas,
958 "strings_to_urls": self.strings_to_urls,
959 "nan_inf_to_errors": self.nan_inf_to_errors,
960 "default_date_format": self.default_date_format,
961 "default_url_format": self.default_url_format,
962 "workbook_add_format": self.add_format,
963 "excel2003_style": self.excel2003_style,
964 "remove_timezone": self.remove_timezone,
965 "max_url_length": self.max_url_length,
966 "use_future_functions": self.use_future_functions,
967 "embedded_images": self.embedded_images,
968 "default_row_height": self.default_row_height,
969 "default_col_width": self.default_col_width,
970 "max_digit_width": self.max_digit_width,
971 "cell_padding": self.cell_padding,
972 "max_col_width": self.max_col_width,
973 }
975 worksheet._initialize(init_data)
977 self.worksheets_objs.append(worksheet)
978 self.sheetnames[name] = worksheet
980 return worksheet
982 def _check_sheetname(self, sheetname, is_chartsheet=False):
983 # Check for valid worksheet names. We check the length, if it contains
984 # any invalid chars and if the sheetname is unique in the workbook.
985 invalid_char = re.compile(r"[\[\]:*?/\\]")
987 # Increment the Sheet/Chart number used for default sheet names below.
988 if is_chartsheet:
989 self.chartname_count += 1
990 else:
991 self.sheetname_count += 1
993 # Supply default Sheet/Chart sheetname if none has been defined.
994 if sheetname is None or sheetname == "":
995 if is_chartsheet:
996 sheetname = self.chart_name + str(self.chartname_count)
997 else:
998 sheetname = self.sheet_name + str(self.sheetname_count)
1000 # Check that sheet sheetname is <= 31. Excel limit.
1001 if len(sheetname) > 31:
1002 raise InvalidWorksheetName(
1003 f"Excel worksheet name '{sheetname}' must be <= 31 chars."
1004 )
1006 # Check that sheetname doesn't contain any invalid characters.
1007 if invalid_char.search(sheetname):
1008 raise InvalidWorksheetName(
1009 f"Invalid Excel character '[]:*?/\\' in sheetname '{sheetname}'."
1010 )
1012 # Check that sheetname doesn't start or end with an apostrophe.
1013 if sheetname.startswith("'") or sheetname.endswith("'"):
1014 raise InvalidWorksheetName(
1015 f'Sheet name cannot start or end with an apostrophe "{sheetname}".'
1016 )
1018 # Check that the worksheet name doesn't already exist since this is a
1019 # fatal Excel error. The check must be case insensitive like Excel.
1020 for worksheet in self.worksheets():
1021 if sheetname.lower() == worksheet.name.lower():
1022 raise DuplicateWorksheetName(
1023 f"Sheetname '{sheetname}', with case ignored, is already in use."
1024 )
1026 return sheetname
1028 def _prepare_format_properties(self) -> None:
1029 # Prepare all Format properties prior to passing them to styles.py.
1031 # Separate format objects into XF and DXF formats.
1032 self._prepare_formats()
1034 # Set the font index for the format objects.
1035 self._prepare_fonts()
1037 # Set the number format index for the format objects.
1038 self._prepare_num_formats()
1040 # Set the border index for the format objects.
1041 self._prepare_borders()
1043 # Set the fill index for the format objects.
1044 self._prepare_fills()
1046 def _prepare_formats(self) -> None:
1047 # Iterate through the XF Format objects and separate them into
1048 # XF and DXF formats. The XF and DF formats then need to be sorted
1049 # back into index order rather than creation order.
1050 xf_formats = []
1051 dxf_formats = []
1053 # Sort into XF and DXF formats.
1054 for xf_format in self.formats:
1055 if xf_format.xf_index is not None:
1056 xf_formats.append(xf_format)
1058 if xf_format.dxf_index is not None:
1059 dxf_formats.append(xf_format)
1061 # Pre-extend the format lists.
1062 self.xf_formats = [None] * len(xf_formats)
1063 self.dxf_formats = [None] * len(dxf_formats)
1065 # Rearrange formats into index order.
1066 for xf_format in xf_formats:
1067 index = xf_format.xf_index
1068 self.xf_formats[index] = xf_format
1070 for dxf_format in dxf_formats:
1071 index = dxf_format.dxf_index
1072 self.dxf_formats[index] = dxf_format
1074 def _set_default_xf_indices(self) -> None:
1075 # Set the default index for each format. Only used for testing.
1077 formats = list(self.formats)
1079 # Delete the default url format.
1080 del formats[1]
1082 # Skip the default date format if set.
1083 if self.default_date_format is not None:
1084 del formats[1]
1086 # Set the remaining formats.
1087 for xf_format in formats:
1088 xf_format._get_xf_index()
1090 def _prepare_fonts(self) -> None:
1091 # Iterate through the XF Format objects and give them an index to
1092 # non-default font elements.
1093 fonts = {}
1094 index = 0
1096 for xf_format in self.xf_formats:
1097 key = xf_format._get_font_key()
1098 if key in fonts:
1099 # Font has already been used.
1100 xf_format.font_index = fonts[key]
1101 xf_format.has_font = False
1102 else:
1103 # This is a new font.
1104 fonts[key] = index
1105 xf_format.font_index = index
1106 xf_format.has_font = True
1107 index += 1
1109 self.font_count = index
1111 # For DXF formats we only need to check if the properties have changed.
1112 for xf_format in self.dxf_formats:
1113 # The only font properties that can change for a DXF format are:
1114 # color, bold, italic, underline and strikethrough.
1115 if (
1116 xf_format.font_color
1117 or xf_format.bold
1118 or xf_format.italic
1119 or xf_format.underline
1120 or xf_format.font_strikeout
1121 ):
1122 xf_format.has_dxf_font = True
1124 def _prepare_num_formats(self) -> None:
1125 # User defined records in Excel start from index 0xA4.
1126 unique_num_formats = {}
1127 num_formats = []
1128 index = 164
1130 for xf_format in self.xf_formats + self.dxf_formats:
1131 num_format = xf_format.num_format
1133 # Check if num_format is an index to a built-in number format.
1134 if not isinstance(num_format, str):
1135 num_format = int(num_format)
1137 # Number format '0' is indexed as 1 in Excel.
1138 if num_format == 0:
1139 num_format = 1
1141 xf_format.num_format_index = num_format
1142 continue
1144 if num_format == "0":
1145 # Number format '0' is indexed as 1 in Excel.
1146 xf_format.num_format_index = 1
1147 continue
1149 if num_format == "General":
1150 # The 'General' format has an number format index of 0.
1151 xf_format.num_format_index = 0
1152 continue
1154 if num_format in unique_num_formats:
1155 # Number xf_format has already been used.
1156 xf_format.num_format_index = unique_num_formats[num_format]
1157 else:
1158 # Add a new number xf_format.
1159 unique_num_formats[num_format] = index
1160 xf_format.num_format_index = index
1161 index += 1
1163 # Only increase font count for XF formats (not DXF formats).
1164 if xf_format.xf_index:
1165 num_formats.append(num_format)
1167 self.num_formats = num_formats
1169 def _prepare_borders(self) -> None:
1170 # Iterate through the XF Format objects and give them an index to
1171 # non-default border elements.
1172 borders = {}
1173 index = 0
1175 for xf_format in self.xf_formats:
1176 key = xf_format._get_border_key()
1178 if key in borders:
1179 # Border has already been used.
1180 xf_format.border_index = borders[key]
1181 xf_format.has_border = False
1182 else:
1183 # This is a new border.
1184 borders[key] = index
1185 xf_format.border_index = index
1186 xf_format.has_border = True
1187 index += 1
1189 self.border_count = index
1191 # For DXF formats we only need to check if the properties have changed.
1192 has_border = re.compile(r"[^0None:]")
1194 for xf_format in self.dxf_formats:
1195 key = xf_format._get_border_key()
1197 if has_border.search(key):
1198 xf_format.has_dxf_border = True
1200 def _prepare_fills(self) -> None:
1201 # Iterate through the XF Format objects and give them an index to
1202 # non-default fill elements.
1203 # The user defined fill properties start from 2 since there are 2
1204 # default fills: patternType="none" and patternType="gray125".
1205 fills = {}
1206 index = 2 # Start from 2. See above.
1208 # Add the default fills.
1209 fills["0:None:None"] = 0
1210 fills["17:None:None"] = 1
1212 # Store the DXF colors separately since them may be reversed below.
1213 for xf_format in self.dxf_formats:
1214 if xf_format.pattern or xf_format.bg_color or xf_format.fg_color:
1215 xf_format.has_dxf_fill = True
1216 xf_format.dxf_bg_color = xf_format.bg_color
1217 xf_format.dxf_fg_color = xf_format.fg_color
1219 for xf_format in self.xf_formats:
1220 # The following logical statements jointly take care of special
1221 # cases in relation to cell colors and patterns:
1222 # 1. For a solid fill (_pattern == 1) Excel reverses the role of
1223 # foreground and background colors, and
1224 # 2. If the user specifies a foreground or background color
1225 # without a pattern they probably wanted a solid fill, so we fill
1226 # in the defaults.
1227 if xf_format.pattern == 1 and xf_format.bg_color and xf_format.fg_color:
1228 tmp = xf_format.fg_color
1229 xf_format.fg_color = xf_format.bg_color
1230 xf_format.bg_color = tmp
1232 if xf_format.pattern <= 1 and xf_format.bg_color and not xf_format.fg_color:
1233 xf_format.fg_color = xf_format.bg_color
1234 xf_format.bg_color = None
1235 xf_format.pattern = 1
1237 if xf_format.pattern <= 1 and not xf_format.bg_color and xf_format.fg_color:
1238 xf_format.pattern = 1
1240 key = xf_format._get_fill_key()
1242 if key in fills:
1243 # Fill has already been used.
1244 xf_format.fill_index = fills[key]
1245 xf_format.has_fill = False
1246 else:
1247 # This is a new fill.
1248 fills[key] = index
1249 xf_format.fill_index = index
1250 xf_format.has_fill = True
1251 index += 1
1253 self.fill_count = index
1255 def _has_feature_property_bags(self):
1256 # Check for any format properties that require a feature bag. Currently
1257 # this only applies to checkboxes.
1258 if not self.feature_property_bags:
1259 for xf_format in self.formats:
1260 if xf_format.checkbox:
1261 self.feature_property_bags.add("XFComplements")
1263 if xf_format.dxf_index is not None and xf_format.checkbox:
1264 self.feature_property_bags.add("DXFComplements")
1266 return self.feature_property_bags
1268 def _prepare_defined_names(self) -> None:
1269 # Iterate through the worksheets and store any defined names in
1270 # addition to any user defined names. Stores the defined names
1271 # for the Workbook.xml and the named ranges for App.xml.
1272 defined_names = self.defined_names
1274 for sheet in self.worksheets():
1275 # Check for Print Area settings.
1276 if sheet.autofilter_area:
1277 hidden = 1
1278 sheet_range = sheet.autofilter_area
1279 # Store the defined names.
1280 defined_names.append(
1281 ["_xlnm._FilterDatabase", sheet.index, sheet_range, hidden]
1282 )
1284 # Check for Print Area settings.
1285 if sheet.print_area_range:
1286 hidden = 0
1287 sheet_range = sheet.print_area_range
1288 # Store the defined names.
1289 defined_names.append(
1290 ["_xlnm.Print_Area", sheet.index, sheet_range, hidden]
1291 )
1293 # Check for repeat rows/cols referred to as Print Titles.
1294 if sheet.repeat_col_range or sheet.repeat_row_range:
1295 hidden = 0
1296 sheet_range = ""
1297 if sheet.repeat_col_range and sheet.repeat_row_range:
1298 sheet_range = sheet.repeat_col_range + "," + sheet.repeat_row_range
1299 else:
1300 sheet_range = sheet.repeat_col_range + sheet.repeat_row_range
1301 # Store the defined names.
1302 defined_names.append(
1303 ["_xlnm.Print_Titles", sheet.index, sheet_range, hidden]
1304 )
1306 defined_names = self._sort_defined_names(defined_names)
1307 self.defined_names = defined_names
1308 self.named_ranges = self._extract_named_ranges(defined_names)
1310 def _sort_defined_names(self, names):
1311 # Sort the list of list of internal and user defined names in
1312 # the same order as used by Excel.
1314 # Add a normalize name string to each list for sorting.
1315 for name_list in names:
1316 (defined_name, _, sheet_name, _) = name_list
1318 # Normalize the defined name by removing any leading '_xmln.'
1319 # from internal names and lowercasing the string.
1320 defined_name = defined_name.replace("_xlnm.", "").lower()
1322 # Normalize the sheetname by removing the leading quote and
1323 # lowercasing the string.
1324 sheet_name = sheet_name.lstrip("'").lower()
1326 name_list.append(defined_name + "::" + sheet_name)
1328 # Sort based on the normalized key.
1329 names.sort(key=operator.itemgetter(4))
1331 # Remove the extra key used for sorting.
1332 for name_list in names:
1333 name_list.pop()
1335 return names
1337 def _prepare_drawings(self) -> None:
1338 # Iterate through the worksheets and set up chart and image drawings.
1339 chart_ref_id = 0
1340 ref_id = 0
1341 drawing_id = 0
1342 image_ids = {}
1343 header_image_ids = {}
1344 background_ids = {}
1346 # Store the image types for any embedded images.
1347 for image in self.embedded_images.images:
1348 image_extension = image._image_extension
1349 self.image_types[image_extension] = True
1351 if image.description is not None:
1352 self.has_embedded_descriptions = True
1354 image_ref_id = len(self.embedded_images.images)
1356 for sheet in self.worksheets():
1357 chart_count = len(sheet.charts)
1358 image_count = len(sheet.images)
1359 shape_count = len(sheet.shapes)
1361 header_image_count = len(sheet.header_images)
1362 footer_image_count = len(sheet.footer_images)
1363 has_background = sheet.background_image
1364 has_drawing = False
1366 if not (
1367 chart_count
1368 or image_count
1369 or shape_count
1370 or header_image_count
1371 or footer_image_count
1372 or has_background
1373 ):
1374 continue
1376 # Don't increase the drawing_id header/footer images.
1377 if chart_count or image_count or shape_count:
1378 drawing_id += 1
1379 has_drawing = True
1381 # Prepare the background images.
1382 if sheet.background_image:
1383 image = sheet.background_image
1384 image_extension = image._image_extension
1385 image_digest = image._digest
1387 self.image_types[image_extension] = True
1389 if image_digest in background_ids:
1390 ref_id = background_ids[image_digest]
1391 else:
1392 image_ref_id += 1
1393 ref_id = image_ref_id
1394 background_ids[image_digest] = image_ref_id
1395 self.images.append(image)
1397 sheet._prepare_background(ref_id, image_extension)
1399 # Prepare the worksheet images.
1400 for index in range(image_count):
1401 image = sheet.images[index]
1402 image_extension = image._image_extension
1403 image_digest = image._digest
1405 self.image_types[image_extension] = True
1407 if image_digest in image_ids:
1408 ref_id = image_ids[image_digest]
1409 else:
1410 image_ref_id += 1
1411 ref_id = image_ref_id
1412 image_ids[image_digest] = image_ref_id
1413 self.images.append(image)
1415 sheet._prepare_image(
1416 image,
1417 ref_id,
1418 drawing_id,
1419 )
1421 # Prepare the worksheet charts.
1422 for index in range(chart_count):
1423 chart_ref_id += 1
1424 sheet._prepare_chart(index, chart_ref_id, drawing_id)
1426 # Prepare the worksheet shapes.
1427 for index in range(shape_count):
1428 sheet._prepare_shape(index, drawing_id)
1430 # Prepare the header images.
1431 for image in sheet.header_images:
1432 image_extension = image._image_extension
1433 image_digest = image._digest
1435 self.image_types[image_extension] = True
1437 if image_digest in header_image_ids:
1438 ref_id = header_image_ids[image_digest]
1439 else:
1440 image_ref_id += 1
1441 ref_id = image_ref_id
1442 header_image_ids[image_digest] = image_ref_id
1443 self.images.append(image)
1445 sheet._prepare_header_image(ref_id, image)
1447 # Prepare the footer images.
1448 for image in sheet.footer_images:
1449 image_extension = image._image_extension
1450 image_digest = image._digest
1452 self.image_types[image_extension] = True
1454 if image_digest in header_image_ids:
1455 ref_id = header_image_ids[image_digest]
1456 else:
1457 image_ref_id += 1
1458 ref_id = image_ref_id
1459 header_image_ids[image_digest] = image_ref_id
1460 self.images.append(image)
1462 sheet._prepare_header_image(ref_id, image)
1464 if has_drawing:
1465 drawing = sheet.drawing
1466 self.drawings.append(drawing)
1468 # Remove charts that were created but not inserted into worksheets.
1469 for chart in self.charts[:]:
1470 if chart.id == -1:
1471 self.charts.remove(chart)
1473 # Sort the workbook charts references into the order that the were
1474 # written to the worksheets above.
1475 self.charts = sorted(self.charts, key=lambda chart: chart.id)
1477 self.drawing_count = drawing_id
1479 def _extract_named_ranges(self, defined_names):
1480 # Extract the named ranges from the sorted list of defined names.
1481 # These are used in the App.xml file.
1482 named_ranges = []
1484 for defined_name in defined_names:
1485 name = defined_name[0]
1486 index = defined_name[1]
1487 sheet_range = defined_name[2]
1489 # Skip autoFilter ranges.
1490 if name == "_xlnm._FilterDatabase":
1491 continue
1493 # We are only interested in defined names with ranges.
1494 if "!" in sheet_range:
1495 sheet_name, _ = sheet_range.split("!", 1)
1497 # Match Print_Area and Print_Titles xlnm types.
1498 if name.startswith("_xlnm."):
1499 xlnm_type = name.replace("_xlnm.", "")
1500 name = sheet_name + "!" + xlnm_type
1501 elif index != -1:
1502 name = sheet_name + "!" + name
1504 named_ranges.append(name)
1506 return named_ranges
1508 def _get_sheet_index(self, sheetname):
1509 # Convert a sheet name to its index. Return None otherwise.
1510 sheetname = sheetname.strip("'")
1512 if sheetname in self.sheetnames:
1513 return self.sheetnames[sheetname].index
1515 return None
1517 def _prepare_vml(self) -> None:
1518 # Iterate through the worksheets and set up the VML objects.
1519 comment_id = 0
1520 vml_drawing_id = 0
1521 vml_data_id = 1
1522 vml_header_id = 0
1523 vml_shape_id = 1024
1524 vml_files = 0
1525 comment_files = 0
1527 for sheet in self.worksheets():
1528 if not sheet.has_vml and not sheet.has_header_vml:
1529 continue
1531 vml_files += 1
1533 if sheet.has_vml:
1534 if sheet.has_comments:
1535 comment_files += 1
1536 comment_id += 1
1537 self.has_comments = True
1539 vml_drawing_id += 1
1541 count = sheet._prepare_vml_objects(
1542 vml_data_id, vml_shape_id, vml_drawing_id, comment_id
1543 )
1545 # Each VML should start with a shape id incremented by 1024.
1546 vml_data_id += 1 * int((1024 + count) / 1024)
1547 vml_shape_id += 1024 * int((1024 + count) / 1024)
1549 if sheet.has_header_vml:
1550 vml_header_id += 1
1551 vml_drawing_id += 1
1552 sheet._prepare_header_vml_objects(vml_header_id, vml_drawing_id)
1554 self.num_vml_files = vml_files
1555 self.num_comment_files = comment_files
1557 def _prepare_tables(self) -> None:
1558 # Set the table ids for the worksheet tables.
1559 table_id = 0
1560 seen = {}
1562 for sheet in self.worksheets():
1563 table_count = len(sheet.tables)
1565 if not table_count:
1566 continue
1568 sheet._prepare_tables(table_id + 1, seen)
1569 table_id += table_count
1571 def _prepare_metadata(self) -> None:
1572 # Set the metadata rel link.
1573 self.has_embedded_images = self.embedded_images.has_images()
1574 self.has_metadata = self.has_embedded_images
1576 for sheet in self.worksheets():
1577 if sheet.has_dynamic_arrays:
1578 self.has_metadata = True
1579 self.has_dynamic_functions = True
1581 def _add_chart_data(self) -> None:
1582 # Add "cached" data to charts to provide the numCache and strCache
1583 # data for series and title/axis ranges.
1584 worksheets = {}
1585 seen_ranges = {}
1586 charts = []
1588 # Map worksheet names to worksheet objects.
1589 for worksheet in self.worksheets():
1590 worksheets[worksheet.name] = worksheet
1592 # Build a list of the worksheet charts including any combined charts.
1593 for chart in self.charts:
1594 charts.append(chart)
1595 if chart.combined:
1596 charts.append(chart.combined)
1598 for chart in charts:
1599 for c_range in chart.formula_ids.keys():
1600 r_id = chart.formula_ids[c_range]
1602 # Skip if the series has user defined data.
1603 if chart.formula_data[r_id] is not None:
1604 if c_range not in seen_ranges or seen_ranges[c_range] is None:
1605 data = chart.formula_data[r_id]
1606 seen_ranges[c_range] = data
1607 continue
1609 # Check to see if the data is already cached locally.
1610 if c_range in seen_ranges:
1611 chart.formula_data[r_id] = seen_ranges[c_range]
1612 continue
1614 # Convert the range formula to a sheet name and cell range.
1615 (sheetname, cells) = self._get_chart_range(c_range)
1617 # Skip if we couldn't parse the formula.
1618 if sheetname is None:
1619 continue
1621 # Handle non-contiguous ranges like:
1622 # (Sheet1!$A$1:$A$2,Sheet1!$A$4:$A$5).
1623 # We don't try to parse them. We just return an empty list.
1624 if sheetname.startswith("("):
1625 chart.formula_data[r_id] = []
1626 seen_ranges[c_range] = []
1627 continue
1629 # Warn if the name is unknown since it indicates a user error
1630 # in a chart series formula.
1631 if sheetname not in worksheets:
1632 warn(
1633 f"Unknown worksheet reference '{sheetname}' in range "
1634 f"'{c_range}' passed to add_series()"
1635 )
1636 chart.formula_data[r_id] = []
1637 seen_ranges[c_range] = []
1638 continue
1640 # Find the worksheet object based on the sheet name.
1641 worksheet = worksheets[sheetname]
1643 # Get the data from the worksheet table.
1644 data = worksheet._get_range_data(*cells)
1646 # Add the data to the chart.
1647 chart.formula_data[r_id] = data
1649 # Store range data locally to avoid lookup if seen again.
1650 seen_ranges[c_range] = data
1652 def _get_chart_range(self, c_range):
1653 # Convert a range formula such as Sheet1!$B$1:$B$5 into a sheet name
1654 # and cell range such as ( 'Sheet1', 0, 1, 4, 1 ).
1656 # Split the range formula into sheetname and cells at the last '!'.
1657 pos = c_range.rfind("!")
1658 if pos > 0:
1659 sheetname = c_range[:pos]
1660 cells = c_range[pos + 1 :]
1661 else:
1662 return None, None
1664 # Split the cell range into 2 cells or else use single cell for both.
1665 if cells.find(":") > 0:
1666 (cell_1, cell_2) = cells.split(":", 1)
1667 else:
1668 (cell_1, cell_2) = (cells, cells)
1670 # Remove leading/trailing quotes and convert escaped quotes to single.
1671 sheetname = sheetname.strip("'")
1672 sheetname = sheetname.replace("''", "'")
1674 try:
1675 # Get the row, col values from the Excel ranges. We do this in a
1676 # try block for ranges that can't be parsed such as defined names.
1677 (row_start, col_start) = xl_cell_to_rowcol(cell_1)
1678 (row_end, col_end) = xl_cell_to_rowcol(cell_2)
1679 except AttributeError:
1680 return None, None
1682 # We only handle 1D ranges.
1683 if row_start != row_end and col_start != col_end:
1684 return None, None
1686 return sheetname, [row_start, col_start, row_end, col_end]
1688 def _prepare_sst_string_data(self) -> None:
1689 # Convert the SST string data from a dict to a list.
1690 self.str_table._sort_string_data()
1692 def _get_packager(self):
1693 # Get and instance of the Packager class to create the xlsx package.
1694 # This allows the default packager to be over-ridden.
1695 return Packager()
1697 def _default_column_metrics(self, width: int) -> Tuple[int, int, int]:
1698 # Get default font metrics for a default column width.
1699 #
1700 # This function returns the font metrics (max_digit_width, padding,
1701 # max_col_width) based on the column pixel width for a default font.
1702 #
1703 # To add support for additional fonts and sizes please open a GitHub request
1704 # with an empty sample workbook with one worksheet.
1705 if width == 56:
1706 metrics = (6, 5, 1533)
1707 elif width == 64:
1708 metrics = (7, 5, 1790)
1709 elif width == 72:
1710 metrics = (8, 5, 2043)
1711 elif width == 80:
1712 metrics = (9, 7, 2300)
1713 elif width == 96:
1714 metrics = (11, 7, 2810)
1715 elif width == 104:
1716 metrics = (12, 7, 3065)
1717 elif width == 120:
1718 metrics = (13, 9, 3323)
1719 else:
1720 warn(f"Unsupported default_column_width '{width}'. Using 64 pixels.")
1721 metrics = (7, 5, 1790)
1723 return metrics
1725 def _read_theme_from_file(self, path: Union[str, os.PathLike]) -> str:
1726 # Read theme XML from either a zip file (thmx/xlsx) or a text file.
1727 try:
1728 # Try to read as a thmx/xlsx zip file first.
1729 with zipfile.ZipFile(path, "r") as archive:
1730 possible_paths = [
1731 "theme/theme/theme1.xml", # thmx file.
1732 "xl/theme/theme1.xml", # xlsx file.
1733 ]
1735 for theme_path in possible_paths:
1736 try:
1737 with archive.open(theme_path) as theme_file:
1738 theme_xml = theme_file.read().decode("utf-8")
1739 return theme_xml
1740 except KeyError:
1741 continue
1743 raise ThemeFileError(f"No theme1.xml found in file: '{path}'.")
1745 except zipfile.BadZipFile:
1746 try:
1747 # Try reading as a text file if zipfile failed.
1748 with open(path, "r", encoding="utf-8") as f:
1749 return f.read()
1750 except IOError as e:
1751 raise IOError(f"Could not read file '{path}': {e}.") from e
1753 ###########################################################################
1754 #
1755 # XML methods.
1756 #
1757 ###########################################################################
1759 def _write_workbook(self) -> None:
1760 # Write <workbook> element.
1762 schema = "http://schemas.openxmlformats.org"
1763 xmlns = schema + "/spreadsheetml/2006/main"
1764 xmlns_r = schema + "/officeDocument/2006/relationships"
1766 attributes = [
1767 ("xmlns", xmlns),
1768 ("xmlns:r", xmlns_r),
1769 ]
1771 self._xml_start_tag("workbook", attributes)
1773 def _write_file_version(self) -> None:
1774 # Write the <fileVersion> element.
1776 app_name = "xl"
1777 last_edited = 4
1778 lowest_edited = 4
1779 rup_build = 4505
1781 attributes = [
1782 ("appName", app_name),
1783 ("lastEdited", last_edited),
1784 ("lowestEdited", lowest_edited),
1785 ("rupBuild", rup_build),
1786 ]
1788 if self.vba_project:
1789 attributes.append(("codeName", "{37E998C4-C9E5-D4B9-71C8-EB1FF731991C}"))
1791 self._xml_empty_tag("fileVersion", attributes)
1793 def _write_file_sharing(self) -> None:
1794 # Write the <fileSharing> element.
1795 if self.read_only == 0:
1796 return
1798 attributes = [("readOnlyRecommended", 1)]
1800 self._xml_empty_tag("fileSharing", attributes)
1802 def _write_workbook_pr(self) -> None:
1803 # Write <workbookPr> element.
1804 attributes = []
1806 if self.vba_codename:
1807 attributes.append(("codeName", self.vba_codename))
1808 if self.date_1904:
1809 attributes.append(("date1904", 1))
1811 if self.default_theme_version:
1812 attributes.append(("defaultThemeVersion", self.default_theme_version))
1814 self._xml_empty_tag("workbookPr", attributes)
1816 def _write_book_views(self) -> None:
1817 # Write <bookViews> element.
1818 self._xml_start_tag("bookViews")
1819 self._write_workbook_view()
1820 self._xml_end_tag("bookViews")
1822 def _write_workbook_view(self) -> None:
1823 # Write <workbookView> element.
1824 attributes = [
1825 ("xWindow", self.x_window),
1826 ("yWindow", self.y_window),
1827 ("windowWidth", self.window_width),
1828 ("windowHeight", self.window_height),
1829 ]
1831 # Store the tabRatio attribute when it isn't the default.
1832 if self.tab_ratio != 600:
1833 attributes.append(("tabRatio", self.tab_ratio))
1835 # Store the firstSheet attribute when it isn't the default.
1836 if self.worksheet_meta.firstsheet > 0:
1837 firstsheet = self.worksheet_meta.firstsheet + 1
1838 attributes.append(("firstSheet", firstsheet))
1840 # Store the activeTab attribute when it isn't the first sheet.
1841 if self.worksheet_meta.activesheet > 0:
1842 attributes.append(("activeTab", self.worksheet_meta.activesheet))
1844 self._xml_empty_tag("workbookView", attributes)
1846 def _write_sheets(self) -> None:
1847 # Write <sheets> element.
1848 self._xml_start_tag("sheets")
1850 id_num = 1
1851 for worksheet in self.worksheets():
1852 self._write_sheet(worksheet.name, id_num, worksheet.hidden)
1853 id_num += 1
1855 self._xml_end_tag("sheets")
1857 def _write_sheet(self, name, sheet_id, hidden) -> None:
1858 # Write <sheet> element.
1859 attributes = [
1860 ("name", name),
1861 ("sheetId", sheet_id),
1862 ]
1864 if hidden == 1:
1865 attributes.append(("state", "hidden"))
1866 elif hidden == 2:
1867 attributes.append(("state", "veryHidden"))
1869 attributes.append(("r:id", "rId" + str(sheet_id)))
1871 self._xml_empty_tag("sheet", attributes)
1873 def _write_calc_pr(self) -> None:
1874 # Write the <calcPr> element.
1875 attributes = [("calcId", self.calc_id)]
1877 if self.calc_mode == "manual":
1878 attributes.append(("calcMode", self.calc_mode))
1879 attributes.append(("calcOnSave", "0"))
1880 elif self.calc_mode == "autoNoTable":
1881 attributes.append(("calcMode", self.calc_mode))
1883 if self.calc_on_load:
1884 attributes.append(("fullCalcOnLoad", "1"))
1886 self._xml_empty_tag("calcPr", attributes)
1888 def _write_defined_names(self) -> None:
1889 # Write the <definedNames> element.
1890 if not self.defined_names:
1891 return
1893 self._xml_start_tag("definedNames")
1895 for defined_name in self.defined_names:
1896 self._write_defined_name(defined_name)
1898 self._xml_end_tag("definedNames")
1900 def _write_defined_name(self, defined_name) -> None:
1901 # Write the <definedName> element.
1902 name = defined_name[0]
1903 sheet_id = defined_name[1]
1904 sheet_range = defined_name[2]
1905 hidden = defined_name[3]
1907 attributes = [("name", name)]
1909 if sheet_id != -1:
1910 attributes.append(("localSheetId", sheet_id))
1911 if hidden:
1912 attributes.append(("hidden", 1))
1914 self._xml_data_element("definedName", sheet_range, attributes)
1917# A metadata class to share data between worksheets.
1918class WorksheetMeta:
1919 """
1920 A class to track worksheets data such as the active sheet and the
1921 first sheet.
1923 """
1925 def __init__(self) -> None:
1926 self.activesheet = 0
1927 self.firstsheet = 0
1930# A helper class to share embedded images between worksheets.
1931class EmbeddedImages:
1932 """
1933 A class to track duplicate embedded images between worksheets.
1935 """
1937 def __init__(self) -> None:
1938 self.images = []
1939 self.image_indexes = {}
1941 def get_image_index(self, image: Image):
1942 """
1943 Get the index of an embedded image.
1945 Args:
1946 image: The image to lookup.
1948 Returns:
1949 The image index.
1951 """
1952 image_index = self.image_indexes.get(image._digest)
1954 if image_index is None:
1955 self.images.append(image)
1956 image_index = len(self.images)
1957 self.image_indexes[image._digest] = image_index
1959 return image_index
1961 def has_images(self):
1962 """
1963 Check if the worksheet has embedded images.
1965 Args:
1966 None.
1968 Returns:
1969 Boolean.
1971 """
1972 return len(self.images) > 0