Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.11/site-packages/xlsxwriter/workbook.py: 51%
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 io import StringIO
19from typing import IO, Any, AnyStr, Dict, List, Literal, Optional, Tuple, Union
20from warnings import warn
21from zipfile import ZIP_DEFLATED, LargeZipFile, ZipFile, ZipInfo
23# Package imports.
24from xlsxwriter import xmlwriter
25from xlsxwriter.chart_area import ChartArea
26from xlsxwriter.chart_bar import ChartBar
27from xlsxwriter.chart_column import ChartColumn
28from xlsxwriter.chart_doughnut import ChartDoughnut
29from xlsxwriter.chart_line import ChartLine
30from xlsxwriter.chart_pie import ChartPie
31from xlsxwriter.chart_radar import ChartRadar
32from xlsxwriter.chart_scatter import ChartScatter
33from xlsxwriter.chart_stock import ChartStock
34from xlsxwriter.chartsheet import Chartsheet
35from xlsxwriter.exceptions import (
36 DuplicateWorksheetName,
37 FileCreateError,
38 FileSizeError,
39 InvalidWorksheetName,
40 ThemeFileError,
41)
42from xlsxwriter.format import Format
43from xlsxwriter.image import Image
44from xlsxwriter.packager import Packager
45from xlsxwriter.sharedstrings import SharedStringTable
46from xlsxwriter.theme import THEME_XML_2007, THEME_XML_2023
47from xlsxwriter.utility import xl_cell_to_rowcol
48from xlsxwriter.worksheet import Worksheet
51class Workbook(xmlwriter.XMLwriter):
52 """
53 A class for writing the Excel XLSX Workbook file.
56 """
58 ###########################################################################
59 #
60 # Public API.
61 #
62 ###########################################################################
63 chartsheet_class = Chartsheet
64 worksheet_class = Worksheet
66 def __init__(
67 self,
68 filename: Optional[Union[str, IO[AnyStr], os.PathLike]] = None,
69 options: Optional[Dict[str, Any]] = None,
70 ) -> None:
71 """
72 Constructor.
74 """
75 if options is None:
76 options = {}
78 super().__init__()
80 self.filename = filename
82 self.tmpdir = options.get("tmpdir", None)
83 self.date_1904 = options.get("date_1904", False)
84 self.strings_to_numbers = options.get("strings_to_numbers", False)
85 self.strings_to_formulas = options.get("strings_to_formulas", True)
86 self.strings_to_urls = options.get("strings_to_urls", True)
87 self.nan_inf_to_errors = options.get("nan_inf_to_errors", False)
88 self.default_date_format = options.get("default_date_format", None)
89 self.constant_memory = options.get("constant_memory", False)
90 self.in_memory = options.get("in_memory", False)
91 self.excel2003_style = options.get("excel2003_style", False)
92 self.excel_2023_theme = options.get("excel_2023_theme", False)
93 self.remove_timezone = options.get("remove_timezone", False)
94 self.use_future_functions = options.get("use_future_functions", False)
95 self.default_row_height = options.get("default_row_height", 20)
96 self.default_col_width = options.get("default_column_width", 64)
97 self.default_format_properties = options.get(
98 "default_format_properties", {"font_name": "Calibri", "font_size": 11}
99 )
101 self.max_url_length = options.get("max_url_length", 2079)
102 if self.max_url_length < 255:
103 self.max_url_length = 2079
105 if options.get("use_zip64"):
106 self.allow_zip64 = True
107 else:
108 self.allow_zip64 = False
110 self.worksheet_meta = WorksheetMeta()
111 self.selected = 0
112 self.fileclosed = 0
113 self.filehandle = None
114 self.internal_fh = 0
115 self.sheet_name = "Sheet"
116 self.chart_name = "Chart"
117 self.sheetname_count = 0
118 self.chartname_count = 0
119 self.worksheets_objs = []
120 self.charts = []
121 self.drawings = []
122 self.sheetnames = {}
123 self.formats = []
124 self.xf_formats = []
125 self.xf_format_indices = {}
126 self.dxf_formats = []
127 self.dxf_format_indices = {}
128 self.palette = []
129 self.font_count = 0
130 self.num_formats = []
131 self.defined_names = []
132 self.named_ranges = []
133 self.custom_colors = []
134 self.doc_properties = {}
135 self.custom_properties = []
136 self.createtime = datetime.now(timezone.utc)
137 self.num_vml_files = 0
138 self.num_comment_files = 0
139 self.x_window = 240
140 self.y_window = 15
141 self.window_width = 16095
142 self.window_height = 9660
143 self.tab_ratio = 600
144 self.str_table = SharedStringTable()
145 self.vba_project = None
146 self.vba_project_is_stream = False
147 self.vba_project_signature = None
148 self.vba_project_signature_is_stream = False
149 self.vba_codename = None
150 self.image_types = {}
151 self.images = []
152 self.border_count = 0
153 self.fill_count = 0
154 self.drawing_count = 0
155 self.calc_mode = "auto"
156 self.calc_on_load = True
157 self.calc_id = 124519
158 self.has_comments = False
159 self.read_only = 0
160 self.has_metadata = False
161 self.has_embedded_images = False
162 self.has_dynamic_functions = False
163 self.has_embedded_descriptions = False
164 self.embedded_images = EmbeddedImages()
165 self.feature_property_bags = set()
166 self.default_theme_version: str = "124226"
167 self.theme_xml: str = THEME_XML_2007
169 # We can't do 'constant_memory' mode while doing 'in_memory' mode.
170 if self.in_memory:
171 self.constant_memory = False
173 # Add the default cell format.
174 if self.excel_2023_theme:
175 format_properties = {
176 "font_name": "Aptos Narrow",
177 "font_size": 11,
178 "font_scheme": "minor",
179 }
180 self.default_format_properties = format_properties.copy()
181 self.default_col_width = 64
182 self.default_row_height = 20
183 self.default_theme_version = "202300"
184 self.theme_xml: str = THEME_XML_2023
186 format_properties["xf_index"] = 0
187 self.add_format(format_properties)
189 elif self.excel2003_style:
190 # This is a deprecated workaround for Excel 2003 style default format.
191 format_properties = {
192 "font_name": "Arial",
193 "font_size": 10,
194 "font_family": 0,
195 "font_scheme": False,
196 "theme": -1,
197 }
198 format_properties["xf_index"] = 0
199 self.add_format(format_properties)
201 self.default_format_properties = {
202 "font_name": "Arial",
203 "font_size": 10,
204 "font_scheme": False,
205 "theme": -1,
206 }
207 else:
208 format_properties = self.default_format_properties.copy()
209 format_properties["xf_index"] = 0
210 self.add_format(format_properties)
212 # Store the theme font name to cell format properties.
213 self.default_format_properties["theme_font_name"] = (
214 self.default_format_properties.get("font_name", "Calibri")
215 )
217 # Add a default URL format.
218 format_properties = self.default_format_properties.copy()
219 format_properties["hyperlink"] = True
220 format_properties["font_scheme"] = "none"
221 self.default_url_format = self.add_format(format_properties)
223 # Add the default date format.
224 if self.default_date_format is not None:
225 self.default_date_format = self.add_format(
226 {"num_format": self.default_date_format}
227 )
229 (self.max_digit_width, self.cell_padding, self.max_col_width) = (
230 self._default_column_metrics(self.default_col_width)
231 )
233 def __enter__(self):
234 """Return self object to use with "with" statement."""
235 return self
237 def __exit__(self, type, value, traceback) -> None:
238 # pylint: disable=redefined-builtin
239 """Close workbook when exiting "with" statement."""
240 self.close()
242 def add_worksheet(
243 self, name: Optional[str] = None, worksheet_class=None
244 ) -> Worksheet:
245 """
246 Add a new worksheet to the Excel workbook.
248 Args:
249 name: The worksheet name. Defaults to 'Sheet1', etc.
251 Returns:
252 Reference to a worksheet object.
254 """
255 if worksheet_class is None:
256 worksheet_class = self.worksheet_class
258 return self._add_sheet(name, worksheet_class=worksheet_class)
260 def add_chartsheet(
261 self, name: Optional[str] = None, chartsheet_class=None
262 ) -> Chartsheet:
263 """
264 Add a new chartsheet to the Excel workbook.
266 Args:
267 name: The chartsheet name. Defaults to 'Sheet1', etc.
269 Returns:
270 Reference to a chartsheet object.
272 """
273 if chartsheet_class is None:
274 chartsheet_class = self.chartsheet_class
276 return self._add_sheet(name, worksheet_class=chartsheet_class)
278 def add_format(self, properties=None) -> Format:
279 """
280 Add a new Format to the Excel Workbook.
282 Args:
283 properties: The format properties.
285 Returns:
286 Reference to a Format object.
288 """
289 format_properties = self.default_format_properties.copy()
291 if properties:
292 format_properties.update(properties)
294 xf_format = Format(
295 format_properties, self.xf_format_indices, self.dxf_format_indices
296 )
298 # Store the format reference.
299 self.formats.append(xf_format)
301 return xf_format
303 def add_chart(self, options: Dict[str, Any]) -> Optional[
304 Union[
305 ChartArea,
306 ChartBar,
307 ChartColumn,
308 ChartDoughnut,
309 ChartLine,
310 ChartPie,
311 ChartRadar,
312 ChartScatter,
313 ChartStock,
314 ]
315 ]:
316 """
317 Create a chart object.
319 Args:
320 options: The chart type and subtype options.
322 Returns:
323 Reference to a Chart object.
325 """
327 # Type must be specified so we can create the required chart instance.
328 chart_type = options.get("type")
329 if chart_type is None:
330 warn("Chart type must be defined in add_chart()")
331 return None
333 if chart_type == "area":
334 chart = ChartArea(options)
335 elif chart_type == "bar":
336 chart = ChartBar(options)
337 elif chart_type == "column":
338 chart = ChartColumn(options)
339 elif chart_type == "doughnut":
340 chart = ChartDoughnut()
341 elif chart_type == "line":
342 chart = ChartLine(options)
343 elif chart_type == "pie":
344 chart = ChartPie()
345 elif chart_type == "radar":
346 chart = ChartRadar(options)
347 elif chart_type == "scatter":
348 chart = ChartScatter(options)
349 elif chart_type == "stock":
350 chart = ChartStock()
351 else:
352 warn(f"Unknown chart type '{chart_type}' in add_chart()")
353 return None
355 # Set the embedded chart name if present.
356 if "name" in options:
357 chart.chart_name = options["name"]
359 chart.embedded = True
360 chart.date_1904 = self.date_1904
361 chart.remove_timezone = self.remove_timezone
363 self.charts.append(chart)
365 return chart
367 def add_vba_project(self, vba_project: str, is_stream: bool = False) -> int:
368 """
369 Add a vbaProject binary to the Excel workbook.
371 Args:
372 vba_project: The vbaProject binary file name.
373 is_stream: vba_project is an in memory byte stream.
375 Returns:
376 0 on success.
378 """
379 if not is_stream and not os.path.exists(vba_project):
380 warn(f"VBA project binary file '{vba_project}' not found.")
381 return -1
383 if self.vba_codename is None:
384 self.vba_codename = "ThisWorkbook"
386 self.vba_project = vba_project
387 self.vba_project_is_stream = is_stream
389 return 0
391 def add_signed_vba_project(
392 self,
393 vba_project: str,
394 signature: str,
395 project_is_stream: bool = False,
396 signature_is_stream: bool = False,
397 ) -> Literal[0, -1]:
398 """
399 Add a vbaProject binary and a vbaProjectSignature binary to the
400 Excel workbook.
402 Args:
403 vba_project: The vbaProject binary file name.
404 signature: The vbaProjectSignature binary file name.
405 project_is_stream: vba_project is an in memory byte stream.
406 signature_is_stream: signature is an in memory byte stream.
408 Returns:
409 0 on success.
411 """
412 if self.add_vba_project(vba_project, project_is_stream) == -1:
413 return -1
415 if not signature_is_stream and not os.path.exists(signature):
416 warn(f"VBA project signature binary file '{signature}' not found.")
417 return -1
419 self.vba_project_signature = signature
420 self.vba_project_signature_is_stream = signature_is_stream
422 return 0
424 def use_custom_theme(self, theme: Union[str, os.PathLike, IO[AnyStr]]) -> int:
425 """
426 Add a custom theme to the Excel workbook.
428 Args:
429 theme: The custom theme as a file path (string or PathLike),
430 or in-memory string as a StringIO object.
432 Raises:
433 IOError: If the file cannot be read.
434 ThemeFileError: If the theme file is invalid or unsupported.
435 ValueError: If the theme parameter type is not supported.
437 """
438 theme_xml = ""
440 if isinstance(theme, (str, os.PathLike)):
441 with open(theme, "r", encoding="utf-8") as file:
442 theme_xml = file.read()
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 BytesIO object"
450 )
452 # Simple check to see if the file is text/XML.
453 if not theme_xml.startswith("<?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 ###########################################################################
1726 #
1727 # XML methods.
1728 #
1729 ###########################################################################
1731 def _write_workbook(self) -> None:
1732 # Write <workbook> element.
1734 schema = "http://schemas.openxmlformats.org"
1735 xmlns = schema + "/spreadsheetml/2006/main"
1736 xmlns_r = schema + "/officeDocument/2006/relationships"
1738 attributes = [
1739 ("xmlns", xmlns),
1740 ("xmlns:r", xmlns_r),
1741 ]
1743 self._xml_start_tag("workbook", attributes)
1745 def _write_file_version(self) -> None:
1746 # Write the <fileVersion> element.
1748 app_name = "xl"
1749 last_edited = 4
1750 lowest_edited = 4
1751 rup_build = 4505
1753 attributes = [
1754 ("appName", app_name),
1755 ("lastEdited", last_edited),
1756 ("lowestEdited", lowest_edited),
1757 ("rupBuild", rup_build),
1758 ]
1760 if self.vba_project:
1761 attributes.append(("codeName", "{37E998C4-C9E5-D4B9-71C8-EB1FF731991C}"))
1763 self._xml_empty_tag("fileVersion", attributes)
1765 def _write_file_sharing(self) -> None:
1766 # Write the <fileSharing> element.
1767 if self.read_only == 0:
1768 return
1770 attributes = [("readOnlyRecommended", 1)]
1772 self._xml_empty_tag("fileSharing", attributes)
1774 def _write_workbook_pr(self) -> None:
1775 # Write <workbookPr> element.
1776 attributes = []
1778 if self.vba_codename:
1779 attributes.append(("codeName", self.vba_codename))
1780 if self.date_1904:
1781 attributes.append(("date1904", 1))
1783 if self.default_theme_version:
1784 attributes.append(("defaultThemeVersion", self.default_theme_version))
1786 self._xml_empty_tag("workbookPr", attributes)
1788 def _write_book_views(self) -> None:
1789 # Write <bookViews> element.
1790 self._xml_start_tag("bookViews")
1791 self._write_workbook_view()
1792 self._xml_end_tag("bookViews")
1794 def _write_workbook_view(self) -> None:
1795 # Write <workbookView> element.
1796 attributes = [
1797 ("xWindow", self.x_window),
1798 ("yWindow", self.y_window),
1799 ("windowWidth", self.window_width),
1800 ("windowHeight", self.window_height),
1801 ]
1803 # Store the tabRatio attribute when it isn't the default.
1804 if self.tab_ratio != 600:
1805 attributes.append(("tabRatio", self.tab_ratio))
1807 # Store the firstSheet attribute when it isn't the default.
1808 if self.worksheet_meta.firstsheet > 0:
1809 firstsheet = self.worksheet_meta.firstsheet + 1
1810 attributes.append(("firstSheet", firstsheet))
1812 # Store the activeTab attribute when it isn't the first sheet.
1813 if self.worksheet_meta.activesheet > 0:
1814 attributes.append(("activeTab", self.worksheet_meta.activesheet))
1816 self._xml_empty_tag("workbookView", attributes)
1818 def _write_sheets(self) -> None:
1819 # Write <sheets> element.
1820 self._xml_start_tag("sheets")
1822 id_num = 1
1823 for worksheet in self.worksheets():
1824 self._write_sheet(worksheet.name, id_num, worksheet.hidden)
1825 id_num += 1
1827 self._xml_end_tag("sheets")
1829 def _write_sheet(self, name, sheet_id, hidden) -> None:
1830 # Write <sheet> element.
1831 attributes = [
1832 ("name", name),
1833 ("sheetId", sheet_id),
1834 ]
1836 if hidden == 1:
1837 attributes.append(("state", "hidden"))
1838 elif hidden == 2:
1839 attributes.append(("state", "veryHidden"))
1841 attributes.append(("r:id", "rId" + str(sheet_id)))
1843 self._xml_empty_tag("sheet", attributes)
1845 def _write_calc_pr(self) -> None:
1846 # Write the <calcPr> element.
1847 attributes = [("calcId", self.calc_id)]
1849 if self.calc_mode == "manual":
1850 attributes.append(("calcMode", self.calc_mode))
1851 attributes.append(("calcOnSave", "0"))
1852 elif self.calc_mode == "autoNoTable":
1853 attributes.append(("calcMode", self.calc_mode))
1855 if self.calc_on_load:
1856 attributes.append(("fullCalcOnLoad", "1"))
1858 self._xml_empty_tag("calcPr", attributes)
1860 def _write_defined_names(self) -> None:
1861 # Write the <definedNames> element.
1862 if not self.defined_names:
1863 return
1865 self._xml_start_tag("definedNames")
1867 for defined_name in self.defined_names:
1868 self._write_defined_name(defined_name)
1870 self._xml_end_tag("definedNames")
1872 def _write_defined_name(self, defined_name) -> None:
1873 # Write the <definedName> element.
1874 name = defined_name[0]
1875 sheet_id = defined_name[1]
1876 sheet_range = defined_name[2]
1877 hidden = defined_name[3]
1879 attributes = [("name", name)]
1881 if sheet_id != -1:
1882 attributes.append(("localSheetId", sheet_id))
1883 if hidden:
1884 attributes.append(("hidden", 1))
1886 self._xml_data_element("definedName", sheet_range, attributes)
1889# A metadata class to share data between worksheets.
1890class WorksheetMeta:
1891 """
1892 A class to track worksheets data such as the active sheet and the
1893 first sheet.
1895 """
1897 def __init__(self) -> None:
1898 self.activesheet = 0
1899 self.firstsheet = 0
1902# A helper class to share embedded images between worksheets.
1903class EmbeddedImages:
1904 """
1905 A class to track duplicate embedded images between worksheets.
1907 """
1909 def __init__(self) -> None:
1910 self.images = []
1911 self.image_indexes = {}
1913 def get_image_index(self, image: Image):
1914 """
1915 Get the index of an embedded image.
1917 Args:
1918 image: The image to lookup.
1920 Returns:
1921 The image index.
1923 """
1924 image_index = self.image_indexes.get(image._digest)
1926 if image_index is None:
1927 self.images.append(image)
1928 image_index = len(self.images)
1929 self.image_indexes[image._digest] = image_index
1931 return image_index
1933 def has_images(self):
1934 """
1935 Check if the worksheet has embedded images.
1937 Args:
1938 None.
1940 Returns:
1941 Boolean.
1943 """
1944 return len(self.images) > 0