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-2026, John McNamara, jmcnamara@cpan.org
8#
10# Standard packages.
11import operator
12import os
13import re
14import time
15import warnings
16import zipfile
17from datetime import datetime
18from decimal import Decimal
19from fractions import Fraction
20from io import StringIO
21from typing import IO, Any, AnyStr, Dict, List, Literal, Optional, Tuple, Union
22from warnings import warn
23from zipfile import ZIP_DEFLATED, LargeZipFile, ZipFile, ZipInfo
25# Package imports.
26from xlsxwriter import xmlwriter
27from xlsxwriter.chart_area import ChartArea
28from xlsxwriter.chart_bar import ChartBar
29from xlsxwriter.chart_column import ChartColumn
30from xlsxwriter.chart_doughnut import ChartDoughnut
31from xlsxwriter.chart_line import ChartLine
32from xlsxwriter.chart_pie import ChartPie
33from xlsxwriter.chart_radar import ChartRadar
34from xlsxwriter.chart_scatter import ChartScatter
35from xlsxwriter.chart_stock import ChartStock
36from xlsxwriter.chartsheet import Chartsheet
37from xlsxwriter.exceptions import (
38 DuplicateWorksheetName,
39 FileCreateError,
40 FileSizeError,
41 InvalidWorksheetName,
42 ThemeFileError,
43)
44from xlsxwriter.format import Format
45from xlsxwriter.image import Image
46from xlsxwriter.packager import Packager
47from xlsxwriter.sharedstrings import SharedStringTable
48from xlsxwriter.theme import THEME_XML_2007, THEME_XML_2023
49from xlsxwriter.utility import _has_invalid_name_chars, _utf16_len, xl_cell_to_rowcol
50from xlsxwriter.worksheet import Worksheet
53class Workbook(xmlwriter.XMLwriter):
54 """
55 A class for writing the Excel XLSX Workbook file.
58 """
60 ###########################################################################
61 #
62 # Public API.
63 #
64 ###########################################################################
65 chartsheet_class = Chartsheet
66 worksheet_class = Worksheet
68 def __init__(
69 self,
70 filename: Optional[Union[str, IO[AnyStr], os.PathLike]] = None,
71 options: Optional[Dict[str, Any]] = None,
72 ) -> None:
73 """
74 Constructor.
76 """
77 if options is None:
78 options = {}
80 super().__init__()
82 self.filename = filename
84 self.tmpdir = options.get("tmpdir", None)
85 self.date_1904 = options.get("date_1904", False)
86 self.strings_to_numbers = options.get("strings_to_numbers", False)
87 self.strings_to_formulas = options.get("strings_to_formulas", True)
88 self.strings_to_urls = options.get("strings_to_urls", True)
89 self.nan_inf_to_errors = options.get("nan_inf_to_errors", False)
90 self.default_date_format = options.get("default_date_format", None)
91 self.constant_memory = options.get("constant_memory", False)
92 self.in_memory = options.get("in_memory", False)
93 self.excel2003_style = options.get("excel2003_style", False)
94 self.excel_2023_theme = options.get("excel_2023_theme", False)
95 self.remove_timezone = options.get("remove_timezone", False)
96 self.use_future_functions = options.get("use_future_functions", False)
97 self.default_row_height = options.get("default_row_height", 20)
98 self.default_col_width = options.get("default_column_width", 64)
99 self.default_format_properties = options.get(
100 "default_format_properties", {"font_name": "Calibri", "font_size": 11}
101 )
103 self.max_url_length = options.get("max_url_length", 2079)
104 if self.max_url_length < 255:
105 self.max_url_length = 2079
107 if options.get("use_zip64"):
108 self.allow_zip64 = True
109 else:
110 self.allow_zip64 = False
112 self.worksheet_meta = WorksheetMeta()
113 self.selected = 0
114 self.fileclosed = 0
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.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]]) -> None:
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 TypeError: If the theme parameter type is not supported.
437 """
438 theme_xml = ""
440 if isinstance(theme, (str, os.PathLike)):
441 theme_xml = self._read_theme_from_file(theme)
443 elif isinstance(theme, StringIO):
444 theme_xml = theme.getvalue()
446 else:
447 raise TypeError(
448 "Theme must be a file path (string or PathLike), or StringIO object."
449 )
451 # Simple check to see if the file is text/XML.
452 if not theme_xml.startswith("<?xml") or "<a:theme" not in theme_xml:
453 raise ThemeFileError(f"Invalid XML theme file: '{theme}'.")
455 # Check for Excel 2007 theme files that contain images as fills. These
456 # aren't currently supported.
457 if "<a:blipFill>" in theme_xml:
458 raise ThemeFileError(
459 "Theme file contains image fills which aren't currently "
460 f"supported: '{theme}'."
461 )
463 self.theme_xml = theme_xml
464 self.default_theme_version = ""
466 def close(self) -> None:
467 """
468 Call finalization code and close file.
470 Args:
471 None.
473 Returns:
474 Nothing.
476 """
477 # pylint: disable=raise-missing-from
478 if not self.fileclosed:
479 try:
480 self._store_workbook()
481 except OSError as e:
482 raise FileCreateError(e)
483 except LargeZipFile:
484 raise FileSizeError(
485 "Filesize would require ZIP64 extensions. "
486 "Use workbook.use_zip64()."
487 )
489 self.fileclosed = True
491 # Ensure all constant_memory temp files are closed.
492 if self.constant_memory:
493 for worksheet in self.worksheets():
494 worksheet._opt_close()
496 else:
497 warn("Calling close() on already closed file.")
499 def set_size(self, width: int, height: int) -> None:
500 """
501 Set the size of a workbook window.
503 Args:
504 width: Width of the window in pixels.
505 height: Height of the window in pixels.
507 Returns:
508 Nothing.
510 """
511 # Convert the width/height to twips at 96 dpi.
512 if width:
513 self.window_width = int(width * 1440 / 96)
514 else:
515 self.window_width = 16095
517 if height:
518 self.window_height = int(height * 1440 / 96)
519 else:
520 self.window_height = 9660
522 def set_tab_ratio(self, tab_ratio: Optional[Union[int, float]] = None) -> None:
523 """
524 Set the ratio between worksheet tabs and the horizontal slider.
526 Args:
527 tab_ratio: The tab ratio, 0 <= tab_ratio <= 100
529 Returns:
530 Nothing.
532 """
533 if tab_ratio is None:
534 return
536 if tab_ratio < 0 or tab_ratio > 100:
537 warn(f"Tab ratio '{tab_ratio}' outside: 0 <= tab_ratio <= 100")
538 else:
539 self.tab_ratio = int(tab_ratio * 10)
541 def set_properties(self, properties) -> None:
542 """
543 Set the document properties such as Title, Author etc.
545 Args:
546 properties: Dictionary of document properties.
548 Returns:
549 Nothing.
551 """
552 self.doc_properties = properties
554 def set_custom_property(
555 self,
556 name: str,
557 value: Union[bool, datetime, float, Decimal, Fraction, Any],
558 property_type: Optional[
559 Literal["bool", "date", "number", "number_int", "text"]
560 ] = None,
561 ) -> Literal[0, -1]:
562 """
563 Set a custom document property.
565 Args:
566 name: The name of the custom property.
567 value: The value of the custom property.
568 property_type: The type of the custom property. Optional.
570 Returns:
571 0 on success.
573 """
574 if name is None or value is None:
575 warn(
576 "The name and value parameters must be non-None in "
577 "set_custom_property()"
578 )
579 return -1
581 if property_type is None:
582 # Determine the property type from the Python type.
583 if isinstance(value, bool):
584 property_type = "bool"
585 elif isinstance(value, datetime):
586 property_type = "date"
587 elif isinstance(value, int):
588 property_type = "number_int"
589 elif isinstance(value, (float, int, Decimal, Fraction)):
590 property_type = "number"
591 else:
592 property_type = "text"
594 # Convert non-string values to strings to have a single data type.
595 if property_type == "bool":
596 value = str(value).lower()
598 if property_type == "date":
599 value = value.strftime("%Y-%m-%dT%H:%M:%SZ")
601 if property_type in ("number", "number_int"):
602 value = str(value)
604 if property_type == "text" and _utf16_len(value) > 255:
605 warn(
606 f"Length of 'value' parameter exceeds Excel's limit of 255 "
607 f"characters in set_custom_property(): '{value}'"
608 )
610 if _utf16_len(name) > 255:
611 warn(
612 f"Length of 'name' parameter exceeds Excel's limit of 255 "
613 f"characters in set_custom_property(): '{name}'"
614 )
616 self.custom_properties.append((name, value, property_type))
618 return 0
620 def set_calc_mode(
621 self, mode: Literal["manual", "auto_except_tables", "auto"], calc_id=None
622 ) -> None:
623 """
624 Set the Excel calculation mode for the workbook.
626 Args:
627 mode: String containing one of:
628 * manual
629 * auto_except_tables
630 * auto
632 Returns:
633 Nothing.
635 """
636 self.calc_mode = mode
638 if mode == "manual":
639 self.calc_on_load = False
640 elif mode == "auto_except_tables":
641 self.calc_mode = "autoNoTable"
643 # Leave undocumented for now. Rarely required.
644 if calc_id:
645 self.calc_id = calc_id
647 def define_name(self, name: str, formula: str) -> Literal[0, -1]:
648 # Create a defined name in Excel. We handle global/workbook level
649 # names and local/worksheet names.
650 """
651 Create a defined name in the workbook.
653 Args:
654 name: The defined name.
655 formula: The cell or range that the defined name refers to.
657 Returns:
658 0 on success.
660 """
661 sheet_index = None
662 sheetname = ""
664 # Remove the = sign from the formula if it exists.
665 if formula.startswith("="):
666 formula = formula.lstrip("=")
668 # Local defined names are formatted like "Sheet1!name".
669 sheet_parts = re.compile(r"^([^!]+)!([^!]+)$")
670 match = sheet_parts.match(name)
672 if match:
673 sheetname = match.group(1)
674 name = match.group(2)
675 sheet_index = self._get_sheet_index(sheetname)
677 # Warn if the sheet index wasn't found.
678 if sheet_index is None:
679 warn(f"Unknown sheet name '{sheetname}' in defined_name()")
680 return -1
681 else:
682 # Use -1 to indicate global names.
683 sheet_index = -1
685 # Warn if the defined name contains invalid chars as defined by Excel.
686 if _has_invalid_name_chars(name):
687 warn(f"Invalid Excel characters in defined_name(): '{name}'")
688 return -1
690 # Warn if the defined name exceeds Excel's maximum length.
691 if _utf16_len(name) > 255:
692 warn(
693 f"Name '{name}' in define_name() exceeds Excel's limit of 255 "
694 f"characters"
695 )
696 return -1
698 # Warn if the defined name looks like a cell name.
699 if re.match(r"^[a-zA-Z][a-zA-Z]?[a-dA-D]?\d+$", name):
700 warn(f"Name looks like a cell name in defined_name(): '{name}'")
701 return -1
703 # Warn if the name looks like a R1C1 cell reference.
704 if re.match(r"^[rcRC]$", name) or re.match(r"^[rcRC]\d+[rcRC]\d+$", name):
705 warn(f"Invalid name '{name}' like a RC cell ref in defined_name()")
706 return -1
708 self.defined_names.append([name, sheet_index, formula, False])
710 return 0
712 def worksheets(self) -> List[Worksheet]:
713 """
714 Return a list of the worksheet objects in the workbook.
716 Args:
717 None.
719 Returns:
720 A list of worksheet objects.
722 """
723 return self.worksheets_objs
725 def get_worksheet_by_name(self, name: str) -> Optional[Worksheet]:
726 """
727 Return a worksheet object in the workbook using the sheetname.
729 Args:
730 name: The name of the worksheet.
732 Returns:
733 A worksheet object or None.
735 """
736 return self.sheetnames.get(name)
738 def get_default_url_format(self) -> Format:
739 """
740 Get the default url format used when a user defined format isn't
741 specified with write_url(). The format is the hyperlink style defined
742 by Excel for the default theme.
744 Args:
745 None.
747 Returns:
748 A format object.
750 """
751 return self.default_url_format
753 def use_zip64(self) -> None:
754 """
755 Allow ZIP64 extensions when writing xlsx file zip container.
757 Args:
758 None.
760 Returns:
761 Nothing.
763 """
764 self.allow_zip64 = True
766 def set_vba_name(self, name: Optional[str] = None) -> None:
767 """
768 Set the VBA name for the workbook. By default the workbook is referred
769 to as ThisWorkbook in VBA.
771 Args:
772 name: The VBA name for the workbook.
774 Returns:
775 Nothing.
777 """
778 if name is not None:
779 self.vba_codename = name
780 else:
781 self.vba_codename = "ThisWorkbook"
783 def read_only_recommended(self) -> None:
784 """
785 Set the Excel "Read-only recommended" option when saving a file.
787 Args:
788 None.
790 Returns:
791 Nothing.
793 """
794 self.read_only = 2
796 ###########################################################################
797 #
798 # Private API.
799 #
800 ###########################################################################
802 def _assemble_xml_file(self) -> None:
803 # Assemble and write the XML file.
805 # Prepare format object for passing to Style.pm.
806 self._prepare_format_properties()
808 # Write the XML declaration.
809 self._xml_declaration()
811 # Write the workbook element.
812 self._write_workbook()
814 # Write the fileVersion element.
815 self._write_file_version()
817 # Write the fileSharing element.
818 self._write_file_sharing()
820 # Write the workbookPr element.
821 self._write_workbook_pr()
823 # Write the bookViews element.
824 self._write_book_views()
826 # Write the sheets element.
827 self._write_sheets()
829 # Write the workbook defined names.
830 self._write_defined_names()
832 # Write the calcPr element.
833 self._write_calc_pr()
835 # Close the workbook tag.
836 self._xml_end_tag("workbook")
838 # Close the file.
839 self._xml_close()
841 def _store_workbook(self) -> None:
842 # pylint: disable=consider-using-with
843 # Create the xlsx/zip file.
844 xlsx_file = ZipFile(
845 self.filename,
846 "w",
847 compression=ZIP_DEFLATED,
848 allowZip64=self.allow_zip64,
849 )
851 # Assemble worksheets into a workbook.
852 packager = self._get_packager()
854 # Add a default worksheet if non have been added.
855 if not self.worksheets():
856 self.add_worksheet()
858 # Ensure that at least one worksheet has been selected.
859 if self.worksheet_meta.activesheet == 0:
860 self.worksheets_objs[0].selected = 1
861 self.worksheets_objs[0].hidden = 0
863 # Set the active sheet.
864 for sheet in self.worksheets():
865 if sheet.index == self.worksheet_meta.activesheet:
866 sheet.active = 1
868 # Set the sheet vba_codename the workbook has a vbaProject binary.
869 if self.vba_project:
870 for sheet in self.worksheets():
871 if sheet.vba_codename is None:
872 sheet.set_vba_name()
874 # Convert the SST strings data structure.
875 self._prepare_sst_string_data()
877 # Prepare the worksheet VML elements such as comments and buttons.
878 self._prepare_vml()
880 # Set the defined names for the worksheets such as Print Titles.
881 self._prepare_defined_names()
883 # Prepare the drawings, charts and images.
884 self._prepare_drawings()
886 # Add cached data to charts.
887 self._add_chart_data()
889 # Prepare the worksheet tables.
890 self._prepare_tables()
892 # Prepare the metadata file links.
893 self._prepare_metadata()
895 # Package the workbook.
896 packager._add_workbook(self)
897 packager._set_tmpdir(self.tmpdir)
898 packager._set_in_memory(self.in_memory)
899 xml_files = packager._create_package()
901 # Free up the Packager object.
902 packager = None
904 # Add XML sub-files to the Zip file with their Excel filename.
905 for file_id, file_data in enumerate(xml_files):
906 os_filename, xml_filename, is_binary = file_data
908 if self.in_memory:
909 # Set sub-file timestamp to Excel's timestamp of 1/1/1980.
910 zipinfo = ZipInfo(xml_filename, (1980, 1, 1, 0, 0, 0))
912 # Copy compression type from parent ZipFile.
913 zipinfo.compress_type = xlsx_file.compression
915 if is_binary:
916 xlsx_file.writestr(zipinfo, os_filename.getvalue())
917 else:
918 xlsx_file.writestr(zipinfo, os_filename.getvalue().encode("utf-8"))
919 else:
920 # The sub-files are tempfiles on disk, i.e, not in memory.
922 # Set sub-file timestamp to 31/1/1980 due to portability
923 # issues setting it to Excel's timestamp of 1/1/1980.
924 timestamp = time.mktime((1980, 1, 31, 0, 0, 0, 0, 0, -1))
925 os.utime(os_filename, (timestamp, timestamp))
927 try:
928 xlsx_file.write(os_filename, xml_filename)
929 os.remove(os_filename)
930 except LargeZipFile:
931 # Close open temp files on zipfile.LargeZipFile exception.
932 for i in range(file_id, len(xml_files) - 1):
933 os.remove(xml_files[i][0])
934 raise
936 xlsx_file.close()
938 def _add_sheet(self, name, worksheet_class=None):
939 # Utility for shared code in add_worksheet() and add_chartsheet().
941 if worksheet_class:
942 worksheet = worksheet_class()
943 else:
944 worksheet = self.worksheet_class()
946 sheet_index = len(self.worksheets_objs)
947 name = self._check_sheetname(name, isinstance(worksheet, Chartsheet))
949 # Initialization data to pass to the worksheet.
950 init_data = {
951 "name": name,
952 "index": sheet_index,
953 "str_table": self.str_table,
954 "worksheet_meta": self.worksheet_meta,
955 "constant_memory": self.constant_memory,
956 "tmpdir": self.tmpdir,
957 "date_1904": self.date_1904,
958 "strings_to_numbers": self.strings_to_numbers,
959 "strings_to_formulas": self.strings_to_formulas,
960 "strings_to_urls": self.strings_to_urls,
961 "nan_inf_to_errors": self.nan_inf_to_errors,
962 "default_date_format": self.default_date_format,
963 "default_url_format": self.default_url_format,
964 "workbook_add_format": self.add_format,
965 "excel2003_style": self.excel2003_style,
966 "remove_timezone": self.remove_timezone,
967 "max_url_length": self.max_url_length,
968 "use_future_functions": self.use_future_functions,
969 "embedded_images": self.embedded_images,
970 "default_row_height": self.default_row_height,
971 "default_col_width": self.default_col_width,
972 "max_digit_width": self.max_digit_width,
973 "cell_padding": self.cell_padding,
974 "max_col_width": self.max_col_width,
975 }
977 worksheet._initialize(init_data)
979 self.worksheets_objs.append(worksheet)
980 self.sheetnames[name] = worksheet
982 return worksheet
984 def _check_sheetname(self, sheetname, is_chartsheet=False):
985 # Check for valid worksheet names. We check the length, if it contains
986 # any invalid chars and if the sheetname is unique in the workbook.
987 invalid_char = re.compile(r"[\[\]:*?/\\]")
989 # Increment the Sheet/Chart number used for default sheet names below.
990 if is_chartsheet:
991 self.chartname_count += 1
992 else:
993 self.sheetname_count += 1
995 # Supply default Sheet/Chart sheetname if none has been defined.
996 if sheetname is None or sheetname == "":
997 if is_chartsheet:
998 sheetname = self.chart_name + str(self.chartname_count)
999 else:
1000 sheetname = self.sheet_name + str(self.sheetname_count)
1002 # Check that sheet sheetname is <= 31. Excel limit.
1003 if _utf16_len(sheetname) > 31:
1004 raise InvalidWorksheetName(
1005 f"Excel worksheet name '{sheetname}' must be <= 31 chars."
1006 )
1008 # Check that sheetname doesn't contain any invalid characters.
1009 if invalid_char.search(sheetname):
1010 raise InvalidWorksheetName(
1011 f"Invalid Excel character '[]:*?/\\' in sheetname '{sheetname}'."
1012 )
1014 # Check that sheetname doesn't start or end with an apostrophe.
1015 if sheetname.startswith("'") or sheetname.endswith("'"):
1016 raise InvalidWorksheetName(
1017 f'Sheet name cannot start or end with an apostrophe "{sheetname}".'
1018 )
1020 # Check that the worksheet name doesn't already exist since this is a
1021 # fatal Excel error. The check must be case insensitive like Excel.
1022 for worksheet in self.worksheets():
1023 if sheetname.lower() == worksheet.name.lower():
1024 raise DuplicateWorksheetName(
1025 f"Sheetname '{sheetname}', with case ignored, is already in use."
1026 )
1028 return sheetname
1030 def _prepare_format_properties(self) -> None:
1031 # Prepare all Format properties prior to passing them to styles.py.
1033 # Separate format objects into XF and DXF formats.
1034 self._prepare_formats()
1036 # Set the font index for the format objects.
1037 self._prepare_fonts()
1039 # Set the number format index for the format objects.
1040 self._prepare_num_formats()
1042 # Set the border index for the format objects.
1043 self._prepare_borders()
1045 # Set the fill index for the format objects.
1046 self._prepare_fills()
1048 def _prepare_formats(self) -> None:
1049 # Iterate through the XF Format objects and separate them into
1050 # XF and DXF formats. The XF and DF formats then need to be sorted
1051 # back into index order rather than creation order.
1052 xf_formats = []
1053 dxf_formats = []
1055 # Sort into XF and DXF formats.
1056 for xf_format in self.formats:
1057 if xf_format.xf_index is not None:
1058 xf_formats.append(xf_format)
1060 if xf_format.dxf_index is not None:
1061 dxf_formats.append(xf_format)
1063 # Pre-extend the format lists.
1064 self.xf_formats = [None] * len(xf_formats)
1065 self.dxf_formats = [None] * len(dxf_formats)
1067 # Rearrange formats into index order.
1068 for xf_format in xf_formats:
1069 index = xf_format.xf_index
1070 self.xf_formats[index] = xf_format
1072 for dxf_format in dxf_formats:
1073 index = dxf_format.dxf_index
1074 self.dxf_formats[index] = dxf_format
1076 def _set_default_xf_indices(self) -> None:
1077 # Set the default index for each format. Only used for testing.
1079 formats = list(self.formats)
1081 # Delete the default url format.
1082 del formats[1]
1084 # Skip the default date format if set.
1085 if self.default_date_format is not None:
1086 del formats[1]
1088 # Set the remaining formats.
1089 for xf_format in formats:
1090 xf_format._get_xf_index()
1092 def _prepare_fonts(self) -> None:
1093 # Iterate through the XF Format objects and give them an index to
1094 # non-default font elements.
1095 fonts = {}
1096 index = 0
1098 for xf_format in self.xf_formats:
1099 key = xf_format._get_font_key()
1100 if key in fonts:
1101 # Font has already been used.
1102 xf_format.font_index = fonts[key]
1103 xf_format.has_font = False
1104 else:
1105 # This is a new font.
1106 fonts[key] = index
1107 xf_format.font_index = index
1108 xf_format.has_font = True
1109 index += 1
1111 self.font_count = index
1113 # For DXF formats we only need to check if the properties have changed.
1114 for xf_format in self.dxf_formats:
1115 # The only font properties that can change for a DXF format are:
1116 # color, bold, italic, underline and strikethrough.
1117 if (
1118 xf_format.font_color
1119 or xf_format.bold
1120 or xf_format.italic
1121 or xf_format.underline
1122 or xf_format.font_strikeout
1123 ):
1124 xf_format.has_dxf_font = True
1126 def _prepare_num_formats(self) -> None:
1127 # User defined records in Excel start from index 0xA4.
1128 unique_num_formats = {}
1129 num_formats = []
1130 index = 164
1132 for xf_format in self.xf_formats + self.dxf_formats:
1133 num_format = xf_format.num_format
1135 # Check if num_format is an index to a built-in number format.
1136 if not isinstance(num_format, str):
1137 num_format = int(num_format)
1139 # Number format '0' is indexed as 1 in Excel.
1140 if num_format == 0:
1141 num_format = 1
1143 xf_format.num_format_index = num_format
1144 continue
1146 if num_format == "0":
1147 # Number format '0' is indexed as 1 in Excel.
1148 xf_format.num_format_index = 1
1149 continue
1151 if num_format == "General":
1152 # The 'General' format has an number format index of 0.
1153 xf_format.num_format_index = 0
1154 continue
1156 if num_format in unique_num_formats:
1157 # Number xf_format has already been used.
1158 xf_format.num_format_index = unique_num_formats[num_format]
1159 else:
1160 # Add a new number xf_format.
1161 unique_num_formats[num_format] = index
1162 xf_format.num_format_index = index
1163 index += 1
1165 # Only increase font count for XF formats (not DXF formats).
1166 if xf_format.xf_index:
1167 num_formats.append(num_format)
1169 self.num_formats = num_formats
1171 def _prepare_borders(self) -> None:
1172 # Iterate through the XF Format objects and give them an index to
1173 # non-default border elements.
1174 borders = {}
1175 index = 0
1177 for xf_format in self.xf_formats:
1178 key = xf_format._get_border_key()
1180 if key in borders:
1181 # Border has already been used.
1182 xf_format.border_index = borders[key]
1183 xf_format.has_border = False
1184 else:
1185 # This is a new border.
1186 borders[key] = index
1187 xf_format.border_index = index
1188 xf_format.has_border = True
1189 index += 1
1191 self.border_count = index
1193 # For DXF formats we only need to check if the properties have changed.
1194 has_border = re.compile(r"[^0None:]")
1196 for xf_format in self.dxf_formats:
1197 key = xf_format._get_border_key()
1199 if has_border.search(key):
1200 xf_format.has_dxf_border = True
1202 def _prepare_fills(self) -> None:
1203 # Iterate through the XF Format objects and give them an index to
1204 # non-default fill elements.
1205 # The user defined fill properties start from 2 since there are 2
1206 # default fills: patternType="none" and patternType="gray125".
1207 fills = {}
1208 index = 2 # Start from 2. See above.
1210 # Add the default fills.
1211 fills["0:None:None"] = 0
1212 fills["17:None:None"] = 1
1214 # Store the DXF colors separately since them may be reversed below.
1215 for xf_format in self.dxf_formats:
1216 if xf_format.pattern or xf_format.bg_color or xf_format.fg_color:
1217 xf_format.has_dxf_fill = True
1218 xf_format.dxf_bg_color = xf_format.bg_color
1219 xf_format.dxf_fg_color = xf_format.fg_color
1221 for xf_format in self.xf_formats:
1222 # The following logical statements jointly take care of special
1223 # cases in relation to cell colors and patterns:
1224 # 1. For a solid fill (_pattern == 1) Excel reverses the role of
1225 # foreground and background colors, and
1226 # 2. If the user specifies a foreground or background color
1227 # without a pattern they probably wanted a solid fill, so we fill
1228 # in the defaults.
1229 if xf_format.pattern == 1 and xf_format.bg_color and xf_format.fg_color:
1230 tmp = xf_format.fg_color
1231 xf_format.fg_color = xf_format.bg_color
1232 xf_format.bg_color = tmp
1234 if xf_format.pattern <= 1 and xf_format.bg_color and not xf_format.fg_color:
1235 xf_format.fg_color = xf_format.bg_color
1236 xf_format.bg_color = None
1237 xf_format.pattern = 1
1239 if xf_format.pattern <= 1 and not xf_format.bg_color and xf_format.fg_color:
1240 xf_format.pattern = 1
1242 key = xf_format._get_fill_key()
1244 if key in fills:
1245 # Fill has already been used.
1246 xf_format.fill_index = fills[key]
1247 xf_format.has_fill = False
1248 else:
1249 # This is a new fill.
1250 fills[key] = index
1251 xf_format.fill_index = index
1252 xf_format.has_fill = True
1253 index += 1
1255 self.fill_count = index
1257 def _has_feature_property_bags(self):
1258 # Check for any format properties that require a feature bag. Currently
1259 # this only applies to checkboxes.
1260 if not self.feature_property_bags:
1261 for xf_format in self.formats:
1262 if xf_format.checkbox:
1263 self.feature_property_bags.add("XFComplements")
1265 if xf_format.dxf_index is not None and xf_format.checkbox:
1266 self.feature_property_bags.add("DXFComplements")
1268 return self.feature_property_bags
1270 def _prepare_defined_names(self) -> None:
1271 # Iterate through the worksheets and store any defined names in
1272 # addition to any user defined names. Stores the defined names
1273 # for the Workbook.xml and the named ranges for App.xml.
1274 defined_names = self.defined_names
1276 for sheet in self.worksheets():
1277 # Check for Print Area settings.
1278 if sheet.autofilter_area:
1279 hidden = 1
1280 sheet_range = sheet.autofilter_area
1281 # Store the defined names.
1282 defined_names.append(
1283 ["_xlnm._FilterDatabase", sheet.index, sheet_range, hidden]
1284 )
1286 # Check for Print Area settings.
1287 if sheet.print_area_range:
1288 hidden = 0
1289 sheet_range = sheet.print_area_range
1290 # Store the defined names.
1291 defined_names.append(
1292 ["_xlnm.Print_Area", sheet.index, sheet_range, hidden]
1293 )
1295 # Check for repeat rows/cols referred to as Print Titles.
1296 if sheet.repeat_col_range or sheet.repeat_row_range:
1297 hidden = 0
1298 sheet_range = ""
1299 if sheet.repeat_col_range and sheet.repeat_row_range:
1300 sheet_range = sheet.repeat_col_range + "," + sheet.repeat_row_range
1301 else:
1302 sheet_range = sheet.repeat_col_range + sheet.repeat_row_range
1303 # Store the defined names.
1304 defined_names.append(
1305 ["_xlnm.Print_Titles", sheet.index, sheet_range, hidden]
1306 )
1308 defined_names = self._sort_defined_names(defined_names)
1309 self.defined_names = defined_names
1310 self.named_ranges = self._extract_named_ranges(defined_names)
1312 def _sort_defined_names(self, names):
1313 # Sort the list of list of internal and user defined names in
1314 # the same order as used by Excel.
1316 # Add a normalize name string to each list for sorting.
1317 for name_list in names:
1318 defined_name, _, sheet_name, _ = name_list
1320 # Normalize the defined name by removing any leading '_xmln.'
1321 # from internal names and lowercasing the string.
1322 defined_name = defined_name.replace("_xlnm.", "").lower()
1324 # Normalize the sheetname by removing the leading quote and
1325 # lowercasing the string.
1326 sheet_name = sheet_name.lstrip("'").lower()
1328 name_list.append(defined_name + "::" + sheet_name)
1330 # Sort based on the normalized key.
1331 names.sort(key=operator.itemgetter(4))
1333 # Remove the extra key used for sorting.
1334 for name_list in names:
1335 name_list.pop()
1337 return names
1339 def _prepare_drawings(self) -> None:
1340 # Iterate through the worksheets and set up chart and image drawings.
1341 chart_ref_id = 0
1342 ref_id = 0
1343 drawing_id = 0
1344 image_ids = {}
1345 header_image_ids = {}
1346 background_ids = {}
1348 # Store the image types for any embedded images.
1349 for image in self.embedded_images.images:
1350 image_extension = image._image_extension
1351 self.image_types[image_extension] = True
1353 if image.description is not None:
1354 self.has_embedded_descriptions = True
1356 image_ref_id = len(self.embedded_images.images)
1358 for sheet in self.worksheets():
1359 chart_count = len(sheet.charts)
1360 image_count = len(sheet.images)
1361 shape_count = len(sheet.shapes)
1363 header_image_count = len(sheet.header_images)
1364 footer_image_count = len(sheet.footer_images)
1365 has_background = sheet.background_image
1366 has_drawing = False
1368 if not (
1369 chart_count
1370 or image_count
1371 or shape_count
1372 or header_image_count
1373 or footer_image_count
1374 or has_background
1375 ):
1376 continue
1378 # Don't increase the drawing_id header/footer images.
1379 if chart_count or image_count or shape_count:
1380 drawing_id += 1
1381 has_drawing = True
1383 # Prepare the background images.
1384 if sheet.background_image:
1385 image = sheet.background_image
1386 image_extension = image._image_extension
1387 image_digest = image._digest
1389 self.image_types[image_extension] = True
1391 if image_digest in background_ids:
1392 ref_id = background_ids[image_digest]
1393 else:
1394 image_ref_id += 1
1395 ref_id = image_ref_id
1396 background_ids[image_digest] = image_ref_id
1397 self.images.append(image)
1399 sheet._prepare_background(ref_id, image_extension)
1401 # Prepare the worksheet images.
1402 for index in range(image_count):
1403 image = sheet.images[index]
1404 image_extension = image._image_extension
1405 image_digest = image._digest
1407 self.image_types[image_extension] = True
1409 if image_digest in image_ids:
1410 ref_id = image_ids[image_digest]
1411 else:
1412 image_ref_id += 1
1413 ref_id = image_ref_id
1414 image_ids[image_digest] = image_ref_id
1415 self.images.append(image)
1417 sheet._prepare_image(
1418 image,
1419 ref_id,
1420 drawing_id,
1421 )
1423 # Prepare the worksheet charts.
1424 for index in range(chart_count):
1425 chart_ref_id += 1
1426 sheet._prepare_chart(index, chart_ref_id, drawing_id)
1428 # Prepare the worksheet shapes.
1429 for index in range(shape_count):
1430 sheet._prepare_shape(index, drawing_id)
1432 # Prepare the header images.
1433 for image in sheet.header_images:
1434 image_extension = image._image_extension
1435 image_digest = image._digest
1437 self.image_types[image_extension] = True
1439 if image_digest in header_image_ids:
1440 ref_id = header_image_ids[image_digest]
1441 else:
1442 image_ref_id += 1
1443 ref_id = image_ref_id
1444 header_image_ids[image_digest] = image_ref_id
1445 self.images.append(image)
1447 sheet._prepare_header_image(ref_id, image)
1449 # Prepare the footer images.
1450 for image in sheet.footer_images:
1451 image_extension = image._image_extension
1452 image_digest = image._digest
1454 self.image_types[image_extension] = True
1456 if image_digest in header_image_ids:
1457 ref_id = header_image_ids[image_digest]
1458 else:
1459 image_ref_id += 1
1460 ref_id = image_ref_id
1461 header_image_ids[image_digest] = image_ref_id
1462 self.images.append(image)
1464 sheet._prepare_header_image(ref_id, image)
1466 if has_drawing:
1467 drawing = sheet.drawing
1468 self.drawings.append(drawing)
1470 # Remove charts that were created but not inserted into worksheets.
1471 for chart in self.charts[:]:
1472 if chart.id == -1:
1473 self.charts.remove(chart)
1475 # Sort the workbook charts references into the order that the were
1476 # written to the worksheets above.
1477 self.charts = sorted(self.charts, key=lambda chart: chart.id)
1479 self.drawing_count = drawing_id
1481 def _extract_named_ranges(self, defined_names):
1482 # Extract the named ranges from the sorted list of defined names.
1483 # These are used in the App.xml file.
1484 named_ranges = []
1486 for defined_name in defined_names:
1487 name = defined_name[0]
1488 index = defined_name[1]
1489 sheet_range = defined_name[2]
1491 # Skip autoFilter ranges.
1492 if name == "_xlnm._FilterDatabase":
1493 continue
1495 # We are only interested in defined names with ranges.
1496 if "!" in sheet_range:
1497 sheet_name, _ = sheet_range.split("!", 1)
1499 # Match Print_Area and Print_Titles xlnm types.
1500 if name.startswith("_xlnm."):
1501 xlnm_type = name.replace("_xlnm.", "")
1502 name = sheet_name + "!" + xlnm_type
1503 elif index != -1:
1504 name = sheet_name + "!" + name
1506 named_ranges.append(name)
1508 return named_ranges
1510 def _get_sheet_index(self, sheetname):
1511 # Convert a sheet name to its index. Return None otherwise.
1512 sheetname = sheetname.strip("'")
1514 if sheetname in self.sheetnames:
1515 return self.sheetnames[sheetname].index
1517 return None
1519 def _prepare_vml(self) -> None:
1520 # Iterate through the worksheets and set up the VML objects.
1521 comment_id = 0
1522 vml_drawing_id = 0
1523 vml_data_id = 1
1524 vml_header_id = 0
1525 vml_shape_id = 1024
1526 vml_files = 0
1527 comment_files = 0
1529 for sheet in self.worksheets():
1530 if not sheet.has_vml and not sheet.has_header_vml:
1531 continue
1533 vml_files += 1
1535 if sheet.has_vml:
1536 if sheet.has_comments:
1537 comment_files += 1
1538 comment_id += 1
1539 self.has_comments = True
1541 vml_drawing_id += 1
1543 count = sheet._prepare_vml_objects(
1544 vml_data_id, vml_shape_id, vml_drawing_id, comment_id
1545 )
1547 # Each VML should start with a shape id incremented by 1024.
1548 vml_data_id += 1 * int((1024 + count) / 1024)
1549 vml_shape_id += 1024 * int((1024 + count) / 1024)
1551 if sheet.has_header_vml:
1552 vml_header_id += 1
1553 vml_drawing_id += 1
1554 sheet._prepare_header_vml_objects(vml_header_id, vml_drawing_id)
1556 self.num_vml_files = vml_files
1557 self.num_comment_files = comment_files
1559 def _prepare_tables(self) -> None:
1560 # Set the table ids for the worksheet tables.
1561 table_id = 0
1562 seen = {}
1564 for sheet in self.worksheets():
1565 table_count = len(sheet.tables)
1567 if not table_count:
1568 continue
1570 sheet._prepare_tables(table_id + 1, seen)
1571 table_id += table_count
1573 def _prepare_metadata(self) -> None:
1574 # Set the metadata rel link.
1575 self.has_embedded_images = self.embedded_images.has_images()
1576 self.has_metadata = self.has_embedded_images
1578 for sheet in self.worksheets():
1579 if sheet.has_dynamic_arrays:
1580 self.has_metadata = True
1581 self.has_dynamic_functions = True
1583 def _add_chart_data(self) -> None:
1584 # Add "cached" data to charts to provide the numCache and strCache
1585 # data for series and title/axis ranges.
1586 worksheets = {}
1587 seen_ranges = {}
1588 charts = []
1590 # Map worksheet names to worksheet objects.
1591 for worksheet in self.worksheets():
1592 worksheets[worksheet.name] = worksheet
1594 # Build a list of the worksheet charts including any combined charts.
1595 for chart in self.charts:
1596 charts.append(chart)
1597 if chart.combined:
1598 charts.append(chart.combined)
1600 for chart in charts:
1601 for c_range in chart.formula_ids:
1602 r_id = chart.formula_ids[c_range]
1604 # Skip if the series has user defined data.
1605 if chart.formula_data[r_id] is not None:
1606 if c_range not in seen_ranges or seen_ranges[c_range] is None:
1607 data = chart.formula_data[r_id]
1608 seen_ranges[c_range] = data
1609 continue
1611 # Check to see if the data is already cached locally.
1612 if c_range in seen_ranges:
1613 chart.formula_data[r_id] = seen_ranges[c_range]
1614 continue
1616 # Convert the range formula to a sheet name and cell range.
1617 with warnings.catch_warnings():
1618 # Ignore warning for non-cell ranges like defined names.
1619 warnings.simplefilter("ignore")
1620 sheetname, cells = self._get_chart_range(c_range)
1621 # Skip if we couldn't parse the formula.
1622 if sheetname is None:
1623 continue
1625 # Handle non-contiguous ranges like:
1626 # (Sheet1!$A$1:$A$2,Sheet1!$A$4:$A$5).
1627 # We don't try to parse them. We just return an empty list.
1628 if sheetname.startswith("("):
1629 chart.formula_data[r_id] = []
1630 seen_ranges[c_range] = []
1631 continue
1633 # Warn if the name is unknown since it indicates a user error
1634 # in a chart series formula.
1635 if sheetname not in worksheets:
1636 warn(
1637 f"Unknown worksheet reference '{sheetname}' in range "
1638 f"'{c_range}' passed to add_series()"
1639 )
1640 chart.formula_data[r_id] = []
1641 seen_ranges[c_range] = []
1642 continue
1644 # Find the worksheet object based on the sheet name.
1645 worksheet = worksheets[sheetname]
1647 # Get the data from the worksheet table.
1648 data = worksheet._get_range_data(*cells)
1650 # Add the data to the chart.
1651 chart.formula_data[r_id] = data
1653 # Store range data locally to avoid lookup if seen again.
1654 seen_ranges[c_range] = data
1656 def _get_chart_range(self, c_range):
1657 # Convert a range formula such as Sheet1!$B$1:$B$5 into a sheet name
1658 # and cell range such as ( 'Sheet1', 0, 1, 4, 1 ).
1660 # Split the range formula into sheetname and cells at the last '!'.
1661 pos = c_range.rfind("!")
1662 if pos > 0:
1663 sheetname = c_range[:pos]
1664 cells = c_range[pos + 1 :]
1665 else:
1666 return None, None
1668 # Split the cell range into 2 cells or else use single cell for both.
1669 if cells.find(":") > 0:
1670 cell_1, cell_2 = cells.split(":", 1)
1671 else:
1672 cell_1, cell_2 = (cells, cells)
1674 # Remove leading/trailing quotes and convert escaped quotes to single.
1675 sheetname = sheetname.strip("'")
1676 sheetname = sheetname.replace("''", "'")
1678 try:
1679 # Get the row, col values from the Excel ranges. We do this in a
1680 # try block for ranges that can't be parsed such as defined names.
1681 row_start, col_start = xl_cell_to_rowcol(cell_1)
1682 row_end, col_end = xl_cell_to_rowcol(cell_2)
1683 except AttributeError:
1684 return None, None
1686 # We only handle 1D ranges.
1687 if row_start != row_end and col_start != col_end:
1688 return None, None
1690 return sheetname, [row_start, col_start, row_end, col_end]
1692 def _prepare_sst_string_data(self) -> None:
1693 # Convert the SST string data from a dict to a list.
1694 self.str_table._sort_string_data()
1696 def _get_packager(self):
1697 # Get and instance of the Packager class to create the xlsx package.
1698 # This allows the default packager to be over-ridden.
1699 return Packager()
1701 def _default_column_metrics(self, width: int) -> Tuple[int, int, int]:
1702 # Get default font metrics for a default column width.
1703 #
1704 # This function returns the font metrics (max_digit_width, padding,
1705 # max_col_width) based on the column pixel width for a default font.
1706 #
1707 # To add support for additional fonts and sizes please open a GitHub request
1708 # with an empty sample workbook with one worksheet.
1709 if width == 56:
1710 metrics = (6, 5, 1533)
1711 elif width == 64:
1712 metrics = (7, 5, 1790)
1713 elif width == 72:
1714 metrics = (8, 5, 2043)
1715 elif width == 80:
1716 metrics = (9, 7, 2300)
1717 elif width == 96:
1718 metrics = (11, 7, 2810)
1719 elif width == 104:
1720 metrics = (12, 7, 3065)
1721 elif width == 120:
1722 metrics = (13, 9, 3323)
1723 else:
1724 warn(f"Unsupported default_column_width '{width}'. Using 64 pixels.")
1725 metrics = (7, 5, 1790)
1727 return metrics
1729 def _read_theme_from_file(self, path: Union[str, os.PathLike]) -> str:
1730 # Read theme XML from either a zip file (thmx/xlsx) or a text file.
1731 try:
1732 # Try to read as a thmx/xlsx zip file first.
1733 with zipfile.ZipFile(path, "r") as archive:
1734 possible_paths = [
1735 "theme/theme/theme1.xml", # thmx file.
1736 "xl/theme/theme1.xml", # xlsx file.
1737 ]
1739 for theme_path in possible_paths:
1740 try:
1741 with archive.open(theme_path) as theme_file:
1742 theme_xml = theme_file.read().decode("utf-8")
1743 return theme_xml
1744 except KeyError:
1745 continue
1747 raise ThemeFileError(f"No theme1.xml found in file: '{path}'.")
1749 except zipfile.BadZipFile:
1750 try:
1751 # Try reading as a text file if zipfile failed.
1752 with open(path, "r", encoding="utf-8") as f:
1753 return f.read()
1754 except OSError as e:
1755 raise OSError(f"Could not read file '{path}': {e}.") from e
1757 ###########################################################################
1758 #
1759 # XML methods.
1760 #
1761 ###########################################################################
1763 def _write_workbook(self) -> None:
1764 # Write <workbook> element.
1766 schema = "http://schemas.openxmlformats.org"
1767 xmlns = schema + "/spreadsheetml/2006/main"
1768 xmlns_r = schema + "/officeDocument/2006/relationships"
1770 attributes = [
1771 ("xmlns", xmlns),
1772 ("xmlns:r", xmlns_r),
1773 ]
1775 self._xml_start_tag("workbook", attributes)
1777 def _write_file_version(self) -> None:
1778 # Write the <fileVersion> element.
1780 app_name = "xl"
1781 last_edited = 4
1782 lowest_edited = 4
1783 rup_build = 4505
1785 attributes = [
1786 ("appName", app_name),
1787 ("lastEdited", last_edited),
1788 ("lowestEdited", lowest_edited),
1789 ("rupBuild", rup_build),
1790 ]
1792 if self.vba_project:
1793 attributes.append(("codeName", "{37E998C4-C9E5-D4B9-71C8-EB1FF731991C}"))
1795 self._xml_empty_tag("fileVersion", attributes)
1797 def _write_file_sharing(self) -> None:
1798 # Write the <fileSharing> element.
1799 if self.read_only == 0:
1800 return
1802 attributes = [("readOnlyRecommended", 1)]
1804 self._xml_empty_tag("fileSharing", attributes)
1806 def _write_workbook_pr(self) -> None:
1807 # Write <workbookPr> element.
1808 attributes = []
1810 if self.vba_codename:
1811 attributes.append(("codeName", self.vba_codename))
1812 if self.date_1904:
1813 attributes.append(("date1904", 1))
1815 if self.default_theme_version:
1816 attributes.append(("defaultThemeVersion", self.default_theme_version))
1818 self._xml_empty_tag("workbookPr", attributes)
1820 def _write_book_views(self) -> None:
1821 # Write <bookViews> element.
1822 self._xml_start_tag("bookViews")
1823 self._write_workbook_view()
1824 self._xml_end_tag("bookViews")
1826 def _write_workbook_view(self) -> None:
1827 # Write <workbookView> element.
1828 attributes = [
1829 ("xWindow", self.x_window),
1830 ("yWindow", self.y_window),
1831 ("windowWidth", self.window_width),
1832 ("windowHeight", self.window_height),
1833 ]
1835 # Store the tabRatio attribute when it isn't the default.
1836 if self.tab_ratio != 600:
1837 attributes.append(("tabRatio", self.tab_ratio))
1839 # Store the firstSheet attribute when it isn't the default.
1840 if self.worksheet_meta.firstsheet > 0:
1841 attributes.append(("firstSheet", self.worksheet_meta.firstsheet))
1843 # Store the activeTab attribute when it isn't the first sheet.
1844 if self.worksheet_meta.activesheet > 0:
1845 attributes.append(("activeTab", self.worksheet_meta.activesheet))
1847 self._xml_empty_tag("workbookView", attributes)
1849 def _write_sheets(self) -> None:
1850 # Write <sheets> element.
1851 self._xml_start_tag("sheets")
1853 id_num = 1
1854 for worksheet in self.worksheets():
1855 self._write_sheet(worksheet.name, id_num, worksheet.hidden)
1856 id_num += 1
1858 self._xml_end_tag("sheets")
1860 def _write_sheet(self, name, sheet_id, hidden) -> None:
1861 # Write <sheet> element.
1862 attributes = [
1863 ("name", name),
1864 ("sheetId", sheet_id),
1865 ]
1867 if hidden == 1:
1868 attributes.append(("state", "hidden"))
1869 elif hidden == 2:
1870 attributes.append(("state", "veryHidden"))
1872 attributes.append(("r:id", "rId" + str(sheet_id)))
1874 self._xml_empty_tag("sheet", attributes)
1876 def _write_calc_pr(self) -> None:
1877 # Write the <calcPr> element.
1878 attributes = [("calcId", self.calc_id)]
1880 if self.calc_mode == "manual":
1881 attributes.append(("calcMode", self.calc_mode))
1882 attributes.append(("calcOnSave", "0"))
1883 elif self.calc_mode == "autoNoTable":
1884 attributes.append(("calcMode", self.calc_mode))
1886 if self.calc_on_load:
1887 attributes.append(("fullCalcOnLoad", "1"))
1889 self._xml_empty_tag("calcPr", attributes)
1891 def _write_defined_names(self) -> None:
1892 # Write the <definedNames> element.
1893 if not self.defined_names:
1894 return
1896 self._xml_start_tag("definedNames")
1898 for defined_name in self.defined_names:
1899 self._write_defined_name(defined_name)
1901 self._xml_end_tag("definedNames")
1903 def _write_defined_name(self, defined_name) -> None:
1904 # Write the <definedName> element.
1905 name = defined_name[0]
1906 sheet_id = defined_name[1]
1907 sheet_range = defined_name[2]
1908 hidden = defined_name[3]
1910 attributes = [("name", name)]
1912 if sheet_id != -1:
1913 attributes.append(("localSheetId", sheet_id))
1914 if hidden:
1915 attributes.append(("hidden", 1))
1917 self._xml_data_element("definedName", sheet_range, attributes)
1920# A metadata class to share data between worksheets.
1921class WorksheetMeta:
1922 """
1923 A class to track worksheets data such as the active sheet and the
1924 first sheet.
1926 """
1928 def __init__(self) -> None:
1929 self.activesheet = 0
1930 self.firstsheet = 0
1933# A helper class to share embedded images between worksheets.
1934class EmbeddedImages:
1935 """
1936 A class to track duplicate embedded images between worksheets.
1938 """
1940 def __init__(self) -> None:
1941 self.images = []
1942 self.image_indexes = {}
1944 def get_image_index(self, image: Image):
1945 """
1946 Get the index of an embedded image.
1948 Args:
1949 image: The image to lookup.
1951 Returns:
1952 The image index.
1954 """
1955 image_index = self.image_indexes.get(image._digest)
1957 if image_index is None:
1958 self.images.append(image)
1959 image_index = len(self.images)
1960 self.image_indexes[image._digest] = image_index
1962 return image_index
1964 def has_images(self):
1965 """
1966 Check if the worksheet has embedded images.
1968 Args:
1969 None.
1971 Returns:
1972 Boolean.
1974 """
1975 return len(self.images) > 0