Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.11/site-packages/xlsxwriter/workbook.py: 50%
Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1###############################################################################
2#
3# Workbook - A class for writing the Excel XLSX Workbook file.
4#
5# SPDX-License-Identifier: BSD-2-Clause
6#
7# Copyright (c) 2013-2025, John McNamara, jmcnamara@cpan.org
8#
10# Standard packages.
11import operator
12import os
13import re
14import time
15import 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 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 ValueError: 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 ValueError(
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 IOError 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, int, 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 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 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 not re.match(r"^[\w\\][\w\\.]*$", name, re.UNICODE) or re.match(
687 r"^\d", name
688 ):
689 warn(f"Invalid Excel characters in defined_name(): '{name}'")
690 return -1
692 # Warn if the defined name looks like a cell name.
693 if re.match(r"^[a-zA-Z][a-zA-Z]?[a-dA-D]?\d+$", name):
694 warn(f"Name looks like a cell name in defined_name(): '{name}'")
695 return -1
697 # Warn if the name looks like a R1C1 cell reference.
698 if re.match(r"^[rcRC]$", name) or re.match(r"^[rcRC]\d+[rcRC]\d+$", name):
699 warn(f"Invalid name '{name}' like a RC cell ref in defined_name()")
700 return -1
702 self.defined_names.append([name, sheet_index, formula, False])
704 return 0
706 def worksheets(self) -> List[Worksheet]:
707 """
708 Return a list of the worksheet objects in the workbook.
710 Args:
711 None.
713 Returns:
714 A list of worksheet objects.
716 """
717 return self.worksheets_objs
719 def get_worksheet_by_name(self, name: str) -> Optional[Worksheet]:
720 """
721 Return a worksheet object in the workbook using the sheetname.
723 Args:
724 name: The name of the worksheet.
726 Returns:
727 A worksheet object or None.
729 """
730 return self.sheetnames.get(name)
732 def get_default_url_format(self) -> Format:
733 """
734 Get the default url format used when a user defined format isn't
735 specified with write_url(). The format is the hyperlink style defined
736 by Excel for the default theme.
738 Args:
739 None.
741 Returns:
742 A format object.
744 """
745 return self.default_url_format
747 def use_zip64(self) -> None:
748 """
749 Allow ZIP64 extensions when writing xlsx file zip container.
751 Args:
752 None.
754 Returns:
755 Nothing.
757 """
758 self.allow_zip64 = True
760 def set_vba_name(self, name: Optional[str] = None) -> None:
761 """
762 Set the VBA name for the workbook. By default the workbook is referred
763 to as ThisWorkbook in VBA.
765 Args:
766 name: The VBA name for the workbook.
768 Returns:
769 Nothing.
771 """
772 if name is not None:
773 self.vba_codename = name
774 else:
775 self.vba_codename = "ThisWorkbook"
777 def read_only_recommended(self) -> None:
778 """
779 Set the Excel "Read-only recommended" option when saving a file.
781 Args:
782 None.
784 Returns:
785 Nothing.
787 """
788 self.read_only = 2
790 ###########################################################################
791 #
792 # Private API.
793 #
794 ###########################################################################
796 def _assemble_xml_file(self) -> None:
797 # Assemble and write the XML file.
799 # Prepare format object for passing to Style.pm.
800 self._prepare_format_properties()
802 # Write the XML declaration.
803 self._xml_declaration()
805 # Write the workbook element.
806 self._write_workbook()
808 # Write the fileVersion element.
809 self._write_file_version()
811 # Write the fileSharing element.
812 self._write_file_sharing()
814 # Write the workbookPr element.
815 self._write_workbook_pr()
817 # Write the bookViews element.
818 self._write_book_views()
820 # Write the sheets element.
821 self._write_sheets()
823 # Write the workbook defined names.
824 self._write_defined_names()
826 # Write the calcPr element.
827 self._write_calc_pr()
829 # Close the workbook tag.
830 self._xml_end_tag("workbook")
832 # Close the file.
833 self._xml_close()
835 def _store_workbook(self) -> None:
836 # pylint: disable=consider-using-with
837 # Create the xlsx/zip file.
838 try:
839 xlsx_file = ZipFile(
840 self.filename,
841 "w",
842 compression=ZIP_DEFLATED,
843 allowZip64=self.allow_zip64,
844 )
845 except IOError as e:
846 raise e
848 # Assemble worksheets into a workbook.
849 packager = self._get_packager()
851 # Add a default worksheet if non have been added.
852 if not self.worksheets():
853 self.add_worksheet()
855 # Ensure that at least one worksheet has been selected.
856 if self.worksheet_meta.activesheet == 0:
857 self.worksheets_objs[0].selected = 1
858 self.worksheets_objs[0].hidden = 0
860 # Set the active sheet.
861 for sheet in self.worksheets():
862 if sheet.index == self.worksheet_meta.activesheet:
863 sheet.active = 1
865 # Set the sheet vba_codename the workbook has a vbaProject binary.
866 if self.vba_project:
867 for sheet in self.worksheets():
868 if sheet.vba_codename is None:
869 sheet.set_vba_name()
871 # Convert the SST strings data structure.
872 self._prepare_sst_string_data()
874 # Prepare the worksheet VML elements such as comments and buttons.
875 self._prepare_vml()
877 # Set the defined names for the worksheets such as Print Titles.
878 self._prepare_defined_names()
880 # Prepare the drawings, charts and images.
881 self._prepare_drawings()
883 # Add cached data to charts.
884 self._add_chart_data()
886 # Prepare the worksheet tables.
887 self._prepare_tables()
889 # Prepare the metadata file links.
890 self._prepare_metadata()
892 # Package the workbook.
893 packager._add_workbook(self)
894 packager._set_tmpdir(self.tmpdir)
895 packager._set_in_memory(self.in_memory)
896 xml_files = packager._create_package()
898 # Free up the Packager object.
899 packager = None
901 # Add XML sub-files to the Zip file with their Excel filename.
902 for file_id, file_data in enumerate(xml_files):
903 os_filename, xml_filename, is_binary = file_data
905 if self.in_memory:
906 # Set sub-file timestamp to Excel's timestamp of 1/1/1980.
907 zipinfo = ZipInfo(xml_filename, (1980, 1, 1, 0, 0, 0))
909 # Copy compression type from parent ZipFile.
910 zipinfo.compress_type = xlsx_file.compression
912 if is_binary:
913 xlsx_file.writestr(zipinfo, os_filename.getvalue())
914 else:
915 xlsx_file.writestr(zipinfo, os_filename.getvalue().encode("utf-8"))
916 else:
917 # The sub-files are tempfiles on disk, i.e, not in memory.
919 # Set sub-file timestamp to 31/1/1980 due to portability
920 # issues setting it to Excel's timestamp of 1/1/1980.
921 timestamp = time.mktime((1980, 1, 31, 0, 0, 0, 0, 0, -1))
922 os.utime(os_filename, (timestamp, timestamp))
924 try:
925 xlsx_file.write(os_filename, xml_filename)
926 os.remove(os_filename)
927 except LargeZipFile as e:
928 # Close open temp files on zipfile.LargeZipFile exception.
929 for i in range(file_id, len(xml_files) - 1):
930 os.remove(xml_files[i][0])
931 raise e
933 xlsx_file.close()
935 def _add_sheet(self, name, worksheet_class=None):
936 # Utility for shared code in add_worksheet() and add_chartsheet().
938 if worksheet_class:
939 worksheet = worksheet_class()
940 else:
941 worksheet = self.worksheet_class()
943 sheet_index = len(self.worksheets_objs)
944 name = self._check_sheetname(name, isinstance(worksheet, Chartsheet))
946 # Initialization data to pass to the worksheet.
947 init_data = {
948 "name": name,
949 "index": sheet_index,
950 "str_table": self.str_table,
951 "worksheet_meta": self.worksheet_meta,
952 "constant_memory": self.constant_memory,
953 "tmpdir": self.tmpdir,
954 "date_1904": self.date_1904,
955 "strings_to_numbers": self.strings_to_numbers,
956 "strings_to_formulas": self.strings_to_formulas,
957 "strings_to_urls": self.strings_to_urls,
958 "nan_inf_to_errors": self.nan_inf_to_errors,
959 "default_date_format": self.default_date_format,
960 "default_url_format": self.default_url_format,
961 "workbook_add_format": self.add_format,
962 "excel2003_style": self.excel2003_style,
963 "remove_timezone": self.remove_timezone,
964 "max_url_length": self.max_url_length,
965 "use_future_functions": self.use_future_functions,
966 "embedded_images": self.embedded_images,
967 "default_row_height": self.default_row_height,
968 "default_col_width": self.default_col_width,
969 "max_digit_width": self.max_digit_width,
970 "cell_padding": self.cell_padding,
971 "max_col_width": self.max_col_width,
972 }
974 worksheet._initialize(init_data)
976 self.worksheets_objs.append(worksheet)
977 self.sheetnames[name] = worksheet
979 return worksheet
981 def _check_sheetname(self, sheetname, is_chartsheet=False):
982 # Check for valid worksheet names. We check the length, if it contains
983 # any invalid chars and if the sheetname is unique in the workbook.
984 invalid_char = re.compile(r"[\[\]:*?/\\]")
986 # Increment the Sheet/Chart number used for default sheet names below.
987 if is_chartsheet:
988 self.chartname_count += 1
989 else:
990 self.sheetname_count += 1
992 # Supply default Sheet/Chart sheetname if none has been defined.
993 if sheetname is None or sheetname == "":
994 if is_chartsheet:
995 sheetname = self.chart_name + str(self.chartname_count)
996 else:
997 sheetname = self.sheet_name + str(self.sheetname_count)
999 # Check that sheet sheetname is <= 31. Excel limit.
1000 if len(sheetname) > 31:
1001 raise InvalidWorksheetName(
1002 f"Excel worksheet name '{sheetname}' must be <= 31 chars."
1003 )
1005 # Check that sheetname doesn't contain any invalid characters.
1006 if invalid_char.search(sheetname):
1007 raise InvalidWorksheetName(
1008 f"Invalid Excel character '[]:*?/\\' in sheetname '{sheetname}'."
1009 )
1011 # Check that sheetname doesn't start or end with an apostrophe.
1012 if sheetname.startswith("'") or sheetname.endswith("'"):
1013 raise InvalidWorksheetName(
1014 f'Sheet name cannot start or end with an apostrophe "{sheetname}".'
1015 )
1017 # Check that the worksheet name doesn't already exist since this is a
1018 # fatal Excel error. The check must be case insensitive like Excel.
1019 for worksheet in self.worksheets():
1020 if sheetname.lower() == worksheet.name.lower():
1021 raise DuplicateWorksheetName(
1022 f"Sheetname '{sheetname}', with case ignored, is already in use."
1023 )
1025 return sheetname
1027 def _prepare_format_properties(self) -> None:
1028 # Prepare all Format properties prior to passing them to styles.py.
1030 # Separate format objects into XF and DXF formats.
1031 self._prepare_formats()
1033 # Set the font index for the format objects.
1034 self._prepare_fonts()
1036 # Set the number format index for the format objects.
1037 self._prepare_num_formats()
1039 # Set the border index for the format objects.
1040 self._prepare_borders()
1042 # Set the fill index for the format objects.
1043 self._prepare_fills()
1045 def _prepare_formats(self) -> None:
1046 # Iterate through the XF Format objects and separate them into
1047 # XF and DXF formats. The XF and DF formats then need to be sorted
1048 # back into index order rather than creation order.
1049 xf_formats = []
1050 dxf_formats = []
1052 # Sort into XF and DXF formats.
1053 for xf_format in self.formats:
1054 if xf_format.xf_index is not None:
1055 xf_formats.append(xf_format)
1057 if xf_format.dxf_index is not None:
1058 dxf_formats.append(xf_format)
1060 # Pre-extend the format lists.
1061 self.xf_formats = [None] * len(xf_formats)
1062 self.dxf_formats = [None] * len(dxf_formats)
1064 # Rearrange formats into index order.
1065 for xf_format in xf_formats:
1066 index = xf_format.xf_index
1067 self.xf_formats[index] = xf_format
1069 for dxf_format in dxf_formats:
1070 index = dxf_format.dxf_index
1071 self.dxf_formats[index] = dxf_format
1073 def _set_default_xf_indices(self) -> None:
1074 # Set the default index for each format. Only used for testing.
1076 formats = list(self.formats)
1078 # Delete the default url format.
1079 del formats[1]
1081 # Skip the default date format if set.
1082 if self.default_date_format is not None:
1083 del formats[1]
1085 # Set the remaining formats.
1086 for xf_format in formats:
1087 xf_format._get_xf_index()
1089 def _prepare_fonts(self) -> None:
1090 # Iterate through the XF Format objects and give them an index to
1091 # non-default font elements.
1092 fonts = {}
1093 index = 0
1095 for xf_format in self.xf_formats:
1096 key = xf_format._get_font_key()
1097 if key in fonts:
1098 # Font has already been used.
1099 xf_format.font_index = fonts[key]
1100 xf_format.has_font = False
1101 else:
1102 # This is a new font.
1103 fonts[key] = index
1104 xf_format.font_index = index
1105 xf_format.has_font = True
1106 index += 1
1108 self.font_count = index
1110 # For DXF formats we only need to check if the properties have changed.
1111 for xf_format in self.dxf_formats:
1112 # The only font properties that can change for a DXF format are:
1113 # color, bold, italic, underline and strikethrough.
1114 if (
1115 xf_format.font_color
1116 or xf_format.bold
1117 or xf_format.italic
1118 or xf_format.underline
1119 or xf_format.font_strikeout
1120 ):
1121 xf_format.has_dxf_font = True
1123 def _prepare_num_formats(self) -> None:
1124 # User defined records in Excel start from index 0xA4.
1125 unique_num_formats = {}
1126 num_formats = []
1127 index = 164
1129 for xf_format in self.xf_formats + self.dxf_formats:
1130 num_format = xf_format.num_format
1132 # Check if num_format is an index to a built-in number format.
1133 if not isinstance(num_format, str):
1134 num_format = int(num_format)
1136 # Number format '0' is indexed as 1 in Excel.
1137 if num_format == 0:
1138 num_format = 1
1140 xf_format.num_format_index = num_format
1141 continue
1143 if num_format == "0":
1144 # Number format '0' is indexed as 1 in Excel.
1145 xf_format.num_format_index = 1
1146 continue
1148 if num_format == "General":
1149 # The 'General' format has an number format index of 0.
1150 xf_format.num_format_index = 0
1151 continue
1153 if num_format in unique_num_formats:
1154 # Number xf_format has already been used.
1155 xf_format.num_format_index = unique_num_formats[num_format]
1156 else:
1157 # Add a new number xf_format.
1158 unique_num_formats[num_format] = index
1159 xf_format.num_format_index = index
1160 index += 1
1162 # Only increase font count for XF formats (not DXF formats).
1163 if xf_format.xf_index:
1164 num_formats.append(num_format)
1166 self.num_formats = num_formats
1168 def _prepare_borders(self) -> None:
1169 # Iterate through the XF Format objects and give them an index to
1170 # non-default border elements.
1171 borders = {}
1172 index = 0
1174 for xf_format in self.xf_formats:
1175 key = xf_format._get_border_key()
1177 if key in borders:
1178 # Border has already been used.
1179 xf_format.border_index = borders[key]
1180 xf_format.has_border = False
1181 else:
1182 # This is a new border.
1183 borders[key] = index
1184 xf_format.border_index = index
1185 xf_format.has_border = True
1186 index += 1
1188 self.border_count = index
1190 # For DXF formats we only need to check if the properties have changed.
1191 has_border = re.compile(r"[^0None:]")
1193 for xf_format in self.dxf_formats:
1194 key = xf_format._get_border_key()
1196 if has_border.search(key):
1197 xf_format.has_dxf_border = True
1199 def _prepare_fills(self) -> None:
1200 # Iterate through the XF Format objects and give them an index to
1201 # non-default fill elements.
1202 # The user defined fill properties start from 2 since there are 2
1203 # default fills: patternType="none" and patternType="gray125".
1204 fills = {}
1205 index = 2 # Start from 2. See above.
1207 # Add the default fills.
1208 fills["0:None:None"] = 0
1209 fills["17:None:None"] = 1
1211 # Store the DXF colors separately since them may be reversed below.
1212 for xf_format in self.dxf_formats:
1213 if xf_format.pattern or xf_format.bg_color or xf_format.fg_color:
1214 xf_format.has_dxf_fill = True
1215 xf_format.dxf_bg_color = xf_format.bg_color
1216 xf_format.dxf_fg_color = xf_format.fg_color
1218 for xf_format in self.xf_formats:
1219 # The following logical statements jointly take care of special
1220 # cases in relation to cell colors and patterns:
1221 # 1. For a solid fill (_pattern == 1) Excel reverses the role of
1222 # foreground and background colors, and
1223 # 2. If the user specifies a foreground or background color
1224 # without a pattern they probably wanted a solid fill, so we fill
1225 # in the defaults.
1226 if xf_format.pattern == 1 and xf_format.bg_color and xf_format.fg_color:
1227 tmp = xf_format.fg_color
1228 xf_format.fg_color = xf_format.bg_color
1229 xf_format.bg_color = tmp
1231 if xf_format.pattern <= 1 and xf_format.bg_color and not xf_format.fg_color:
1232 xf_format.fg_color = xf_format.bg_color
1233 xf_format.bg_color = None
1234 xf_format.pattern = 1
1236 if xf_format.pattern <= 1 and not xf_format.bg_color and xf_format.fg_color:
1237 xf_format.pattern = 1
1239 key = xf_format._get_fill_key()
1241 if key in fills:
1242 # Fill has already been used.
1243 xf_format.fill_index = fills[key]
1244 xf_format.has_fill = False
1245 else:
1246 # This is a new fill.
1247 fills[key] = index
1248 xf_format.fill_index = index
1249 xf_format.has_fill = True
1250 index += 1
1252 self.fill_count = index
1254 def _has_feature_property_bags(self):
1255 # Check for any format properties that require a feature bag. Currently
1256 # this only applies to checkboxes.
1257 if not self.feature_property_bags:
1258 for xf_format in self.formats:
1259 if xf_format.checkbox:
1260 self.feature_property_bags.add("XFComplements")
1262 if xf_format.dxf_index is not None and xf_format.checkbox:
1263 self.feature_property_bags.add("DXFComplements")
1265 return self.feature_property_bags
1267 def _prepare_defined_names(self) -> None:
1268 # Iterate through the worksheets and store any defined names in
1269 # addition to any user defined names. Stores the defined names
1270 # for the Workbook.xml and the named ranges for App.xml.
1271 defined_names = self.defined_names
1273 for sheet in self.worksheets():
1274 # Check for Print Area settings.
1275 if sheet.autofilter_area:
1276 hidden = 1
1277 sheet_range = sheet.autofilter_area
1278 # Store the defined names.
1279 defined_names.append(
1280 ["_xlnm._FilterDatabase", sheet.index, sheet_range, hidden]
1281 )
1283 # Check for Print Area settings.
1284 if sheet.print_area_range:
1285 hidden = 0
1286 sheet_range = sheet.print_area_range
1287 # Store the defined names.
1288 defined_names.append(
1289 ["_xlnm.Print_Area", sheet.index, sheet_range, hidden]
1290 )
1292 # Check for repeat rows/cols referred to as Print Titles.
1293 if sheet.repeat_col_range or sheet.repeat_row_range:
1294 hidden = 0
1295 sheet_range = ""
1296 if sheet.repeat_col_range and sheet.repeat_row_range:
1297 sheet_range = sheet.repeat_col_range + "," + sheet.repeat_row_range
1298 else:
1299 sheet_range = sheet.repeat_col_range + sheet.repeat_row_range
1300 # Store the defined names.
1301 defined_names.append(
1302 ["_xlnm.Print_Titles", sheet.index, sheet_range, hidden]
1303 )
1305 defined_names = self._sort_defined_names(defined_names)
1306 self.defined_names = defined_names
1307 self.named_ranges = self._extract_named_ranges(defined_names)
1309 def _sort_defined_names(self, names):
1310 # Sort the list of list of internal and user defined names in
1311 # the same order as used by Excel.
1313 # Add a normalize name string to each list for sorting.
1314 for name_list in names:
1315 defined_name, _, sheet_name, _ = name_list
1317 # Normalize the defined name by removing any leading '_xmln.'
1318 # from internal names and lowercasing the string.
1319 defined_name = defined_name.replace("_xlnm.", "").lower()
1321 # Normalize the sheetname by removing the leading quote and
1322 # lowercasing the string.
1323 sheet_name = sheet_name.lstrip("'").lower()
1325 name_list.append(defined_name + "::" + sheet_name)
1327 # Sort based on the normalized key.
1328 names.sort(key=operator.itemgetter(4))
1330 # Remove the extra key used for sorting.
1331 for name_list in names:
1332 name_list.pop()
1334 return names
1336 def _prepare_drawings(self) -> None:
1337 # Iterate through the worksheets and set up chart and image drawings.
1338 chart_ref_id = 0
1339 ref_id = 0
1340 drawing_id = 0
1341 image_ids = {}
1342 header_image_ids = {}
1343 background_ids = {}
1345 # Store the image types for any embedded images.
1346 for image in self.embedded_images.images:
1347 image_extension = image._image_extension
1348 self.image_types[image_extension] = True
1350 if image.description is not None:
1351 self.has_embedded_descriptions = True
1353 image_ref_id = len(self.embedded_images.images)
1355 for sheet in self.worksheets():
1356 chart_count = len(sheet.charts)
1357 image_count = len(sheet.images)
1358 shape_count = len(sheet.shapes)
1360 header_image_count = len(sheet.header_images)
1361 footer_image_count = len(sheet.footer_images)
1362 has_background = sheet.background_image
1363 has_drawing = False
1365 if not (
1366 chart_count
1367 or image_count
1368 or shape_count
1369 or header_image_count
1370 or footer_image_count
1371 or has_background
1372 ):
1373 continue
1375 # Don't increase the drawing_id header/footer images.
1376 if chart_count or image_count or shape_count:
1377 drawing_id += 1
1378 has_drawing = True
1380 # Prepare the background images.
1381 if sheet.background_image:
1382 image = sheet.background_image
1383 image_extension = image._image_extension
1384 image_digest = image._digest
1386 self.image_types[image_extension] = True
1388 if image_digest in background_ids:
1389 ref_id = background_ids[image_digest]
1390 else:
1391 image_ref_id += 1
1392 ref_id = image_ref_id
1393 background_ids[image_digest] = image_ref_id
1394 self.images.append(image)
1396 sheet._prepare_background(ref_id, image_extension)
1398 # Prepare the worksheet images.
1399 for index in range(image_count):
1400 image = sheet.images[index]
1401 image_extension = image._image_extension
1402 image_digest = image._digest
1404 self.image_types[image_extension] = True
1406 if image_digest in image_ids:
1407 ref_id = image_ids[image_digest]
1408 else:
1409 image_ref_id += 1
1410 ref_id = image_ref_id
1411 image_ids[image_digest] = image_ref_id
1412 self.images.append(image)
1414 sheet._prepare_image(
1415 image,
1416 ref_id,
1417 drawing_id,
1418 )
1420 # Prepare the worksheet charts.
1421 for index in range(chart_count):
1422 chart_ref_id += 1
1423 sheet._prepare_chart(index, chart_ref_id, drawing_id)
1425 # Prepare the worksheet shapes.
1426 for index in range(shape_count):
1427 sheet._prepare_shape(index, drawing_id)
1429 # Prepare the header images.
1430 for image in sheet.header_images:
1431 image_extension = image._image_extension
1432 image_digest = image._digest
1434 self.image_types[image_extension] = True
1436 if image_digest in header_image_ids:
1437 ref_id = header_image_ids[image_digest]
1438 else:
1439 image_ref_id += 1
1440 ref_id = image_ref_id
1441 header_image_ids[image_digest] = image_ref_id
1442 self.images.append(image)
1444 sheet._prepare_header_image(ref_id, image)
1446 # Prepare the footer images.
1447 for image in sheet.footer_images:
1448 image_extension = image._image_extension
1449 image_digest = image._digest
1451 self.image_types[image_extension] = True
1453 if image_digest in header_image_ids:
1454 ref_id = header_image_ids[image_digest]
1455 else:
1456 image_ref_id += 1
1457 ref_id = image_ref_id
1458 header_image_ids[image_digest] = image_ref_id
1459 self.images.append(image)
1461 sheet._prepare_header_image(ref_id, image)
1463 if has_drawing:
1464 drawing = sheet.drawing
1465 self.drawings.append(drawing)
1467 # Remove charts that were created but not inserted into worksheets.
1468 for chart in self.charts[:]:
1469 if chart.id == -1:
1470 self.charts.remove(chart)
1472 # Sort the workbook charts references into the order that the were
1473 # written to the worksheets above.
1474 self.charts = sorted(self.charts, key=lambda chart: chart.id)
1476 self.drawing_count = drawing_id
1478 def _extract_named_ranges(self, defined_names):
1479 # Extract the named ranges from the sorted list of defined names.
1480 # These are used in the App.xml file.
1481 named_ranges = []
1483 for defined_name in defined_names:
1484 name = defined_name[0]
1485 index = defined_name[1]
1486 sheet_range = defined_name[2]
1488 # Skip autoFilter ranges.
1489 if name == "_xlnm._FilterDatabase":
1490 continue
1492 # We are only interested in defined names with ranges.
1493 if "!" in sheet_range:
1494 sheet_name, _ = sheet_range.split("!", 1)
1496 # Match Print_Area and Print_Titles xlnm types.
1497 if name.startswith("_xlnm."):
1498 xlnm_type = name.replace("_xlnm.", "")
1499 name = sheet_name + "!" + xlnm_type
1500 elif index != -1:
1501 name = sheet_name + "!" + name
1503 named_ranges.append(name)
1505 return named_ranges
1507 def _get_sheet_index(self, sheetname):
1508 # Convert a sheet name to its index. Return None otherwise.
1509 sheetname = sheetname.strip("'")
1511 if sheetname in self.sheetnames:
1512 return self.sheetnames[sheetname].index
1514 return None
1516 def _prepare_vml(self) -> None:
1517 # Iterate through the worksheets and set up the VML objects.
1518 comment_id = 0
1519 vml_drawing_id = 0
1520 vml_data_id = 1
1521 vml_header_id = 0
1522 vml_shape_id = 1024
1523 vml_files = 0
1524 comment_files = 0
1526 for sheet in self.worksheets():
1527 if not sheet.has_vml and not sheet.has_header_vml:
1528 continue
1530 vml_files += 1
1532 if sheet.has_vml:
1533 if sheet.has_comments:
1534 comment_files += 1
1535 comment_id += 1
1536 self.has_comments = True
1538 vml_drawing_id += 1
1540 count = sheet._prepare_vml_objects(
1541 vml_data_id, vml_shape_id, vml_drawing_id, comment_id
1542 )
1544 # Each VML should start with a shape id incremented by 1024.
1545 vml_data_id += 1 * int((1024 + count) / 1024)
1546 vml_shape_id += 1024 * int((1024 + count) / 1024)
1548 if sheet.has_header_vml:
1549 vml_header_id += 1
1550 vml_drawing_id += 1
1551 sheet._prepare_header_vml_objects(vml_header_id, vml_drawing_id)
1553 self.num_vml_files = vml_files
1554 self.num_comment_files = comment_files
1556 def _prepare_tables(self) -> None:
1557 # Set the table ids for the worksheet tables.
1558 table_id = 0
1559 seen = {}
1561 for sheet in self.worksheets():
1562 table_count = len(sheet.tables)
1564 if not table_count:
1565 continue
1567 sheet._prepare_tables(table_id + 1, seen)
1568 table_id += table_count
1570 def _prepare_metadata(self) -> None:
1571 # Set the metadata rel link.
1572 self.has_embedded_images = self.embedded_images.has_images()
1573 self.has_metadata = self.has_embedded_images
1575 for sheet in self.worksheets():
1576 if sheet.has_dynamic_arrays:
1577 self.has_metadata = True
1578 self.has_dynamic_functions = True
1580 def _add_chart_data(self) -> None:
1581 # Add "cached" data to charts to provide the numCache and strCache
1582 # data for series and title/axis ranges.
1583 worksheets = {}
1584 seen_ranges = {}
1585 charts = []
1587 # Map worksheet names to worksheet objects.
1588 for worksheet in self.worksheets():
1589 worksheets[worksheet.name] = worksheet
1591 # Build a list of the worksheet charts including any combined charts.
1592 for chart in self.charts:
1593 charts.append(chart)
1594 if chart.combined:
1595 charts.append(chart.combined)
1597 for chart in charts:
1598 for c_range in chart.formula_ids.keys():
1599 r_id = chart.formula_ids[c_range]
1601 # Skip if the series has user defined data.
1602 if chart.formula_data[r_id] is not None:
1603 if c_range not in seen_ranges or seen_ranges[c_range] is None:
1604 data = chart.formula_data[r_id]
1605 seen_ranges[c_range] = data
1606 continue
1608 # Check to see if the data is already cached locally.
1609 if c_range in seen_ranges:
1610 chart.formula_data[r_id] = seen_ranges[c_range]
1611 continue
1613 # Convert the range formula to a sheet name and cell range.
1614 with warnings.catch_warnings():
1615 # Ignore warning for non-cell ranges like defined names.
1616 warnings.simplefilter("ignore")
1617 sheetname, cells = self._get_chart_range(c_range)
1618 # Skip if we couldn't parse the formula.
1619 if sheetname is None:
1620 continue
1622 # Handle non-contiguous ranges like:
1623 # (Sheet1!$A$1:$A$2,Sheet1!$A$4:$A$5).
1624 # We don't try to parse them. We just return an empty list.
1625 if sheetname.startswith("("):
1626 chart.formula_data[r_id] = []
1627 seen_ranges[c_range] = []
1628 continue
1630 # Warn if the name is unknown since it indicates a user error
1631 # in a chart series formula.
1632 if sheetname not in worksheets:
1633 warn(
1634 f"Unknown worksheet reference '{sheetname}' in range "
1635 f"'{c_range}' passed to add_series()"
1636 )
1637 chart.formula_data[r_id] = []
1638 seen_ranges[c_range] = []
1639 continue
1641 # Find the worksheet object based on the sheet name.
1642 worksheet = worksheets[sheetname]
1644 # Get the data from the worksheet table.
1645 data = worksheet._get_range_data(*cells)
1647 # Add the data to the chart.
1648 chart.formula_data[r_id] = data
1650 # Store range data locally to avoid lookup if seen again.
1651 seen_ranges[c_range] = data
1653 def _get_chart_range(self, c_range):
1654 # Convert a range formula such as Sheet1!$B$1:$B$5 into a sheet name
1655 # and cell range such as ( 'Sheet1', 0, 1, 4, 1 ).
1657 # Split the range formula into sheetname and cells at the last '!'.
1658 pos = c_range.rfind("!")
1659 if pos > 0:
1660 sheetname = c_range[:pos]
1661 cells = c_range[pos + 1 :]
1662 else:
1663 return None, None
1665 # Split the cell range into 2 cells or else use single cell for both.
1666 if cells.find(":") > 0:
1667 cell_1, cell_2 = cells.split(":", 1)
1668 else:
1669 cell_1, cell_2 = (cells, cells)
1671 # Remove leading/trailing quotes and convert escaped quotes to single.
1672 sheetname = sheetname.strip("'")
1673 sheetname = sheetname.replace("''", "'")
1675 try:
1676 # Get the row, col values from the Excel ranges. We do this in a
1677 # try block for ranges that can't be parsed such as defined names.
1678 row_start, col_start = xl_cell_to_rowcol(cell_1)
1679 row_end, col_end = xl_cell_to_rowcol(cell_2)
1680 except AttributeError:
1681 return None, None
1683 # We only handle 1D ranges.
1684 if row_start != row_end and col_start != col_end:
1685 return None, None
1687 return sheetname, [row_start, col_start, row_end, col_end]
1689 def _prepare_sst_string_data(self) -> None:
1690 # Convert the SST string data from a dict to a list.
1691 self.str_table._sort_string_data()
1693 def _get_packager(self):
1694 # Get and instance of the Packager class to create the xlsx package.
1695 # This allows the default packager to be over-ridden.
1696 return Packager()
1698 def _default_column_metrics(self, width: int) -> Tuple[int, int, int]:
1699 # Get default font metrics for a default column width.
1700 #
1701 # This function returns the font metrics (max_digit_width, padding,
1702 # max_col_width) based on the column pixel width for a default font.
1703 #
1704 # To add support for additional fonts and sizes please open a GitHub request
1705 # with an empty sample workbook with one worksheet.
1706 if width == 56:
1707 metrics = (6, 5, 1533)
1708 elif width == 64:
1709 metrics = (7, 5, 1790)
1710 elif width == 72:
1711 metrics = (8, 5, 2043)
1712 elif width == 80:
1713 metrics = (9, 7, 2300)
1714 elif width == 96:
1715 metrics = (11, 7, 2810)
1716 elif width == 104:
1717 metrics = (12, 7, 3065)
1718 elif width == 120:
1719 metrics = (13, 9, 3323)
1720 else:
1721 warn(f"Unsupported default_column_width '{width}'. Using 64 pixels.")
1722 metrics = (7, 5, 1790)
1724 return metrics
1726 def _read_theme_from_file(self, path: Union[str, os.PathLike]) -> str:
1727 # Read theme XML from either a zip file (thmx/xlsx) or a text file.
1728 try:
1729 # Try to read as a thmx/xlsx zip file first.
1730 with zipfile.ZipFile(path, "r") as archive:
1731 possible_paths = [
1732 "theme/theme/theme1.xml", # thmx file.
1733 "xl/theme/theme1.xml", # xlsx file.
1734 ]
1736 for theme_path in possible_paths:
1737 try:
1738 with archive.open(theme_path) as theme_file:
1739 theme_xml = theme_file.read().decode("utf-8")
1740 return theme_xml
1741 except KeyError:
1742 continue
1744 raise ThemeFileError(f"No theme1.xml found in file: '{path}'.")
1746 except zipfile.BadZipFile:
1747 try:
1748 # Try reading as a text file if zipfile failed.
1749 with open(path, "r", encoding="utf-8") as f:
1750 return f.read()
1751 except IOError as e:
1752 raise IOError(f"Could not read file '{path}': {e}.") from e
1754 ###########################################################################
1755 #
1756 # XML methods.
1757 #
1758 ###########################################################################
1760 def _write_workbook(self) -> None:
1761 # Write <workbook> element.
1763 schema = "http://schemas.openxmlformats.org"
1764 xmlns = schema + "/spreadsheetml/2006/main"
1765 xmlns_r = schema + "/officeDocument/2006/relationships"
1767 attributes = [
1768 ("xmlns", xmlns),
1769 ("xmlns:r", xmlns_r),
1770 ]
1772 self._xml_start_tag("workbook", attributes)
1774 def _write_file_version(self) -> None:
1775 # Write the <fileVersion> element.
1777 app_name = "xl"
1778 last_edited = 4
1779 lowest_edited = 4
1780 rup_build = 4505
1782 attributes = [
1783 ("appName", app_name),
1784 ("lastEdited", last_edited),
1785 ("lowestEdited", lowest_edited),
1786 ("rupBuild", rup_build),
1787 ]
1789 if self.vba_project:
1790 attributes.append(("codeName", "{37E998C4-C9E5-D4B9-71C8-EB1FF731991C}"))
1792 self._xml_empty_tag("fileVersion", attributes)
1794 def _write_file_sharing(self) -> None:
1795 # Write the <fileSharing> element.
1796 if self.read_only == 0:
1797 return
1799 attributes = [("readOnlyRecommended", 1)]
1801 self._xml_empty_tag("fileSharing", attributes)
1803 def _write_workbook_pr(self) -> None:
1804 # Write <workbookPr> element.
1805 attributes = []
1807 if self.vba_codename:
1808 attributes.append(("codeName", self.vba_codename))
1809 if self.date_1904:
1810 attributes.append(("date1904", 1))
1812 if self.default_theme_version:
1813 attributes.append(("defaultThemeVersion", self.default_theme_version))
1815 self._xml_empty_tag("workbookPr", attributes)
1817 def _write_book_views(self) -> None:
1818 # Write <bookViews> element.
1819 self._xml_start_tag("bookViews")
1820 self._write_workbook_view()
1821 self._xml_end_tag("bookViews")
1823 def _write_workbook_view(self) -> None:
1824 # Write <workbookView> element.
1825 attributes = [
1826 ("xWindow", self.x_window),
1827 ("yWindow", self.y_window),
1828 ("windowWidth", self.window_width),
1829 ("windowHeight", self.window_height),
1830 ]
1832 # Store the tabRatio attribute when it isn't the default.
1833 if self.tab_ratio != 600:
1834 attributes.append(("tabRatio", self.tab_ratio))
1836 # Store the firstSheet attribute when it isn't the default.
1837 if self.worksheet_meta.firstsheet > 0:
1838 firstsheet = self.worksheet_meta.firstsheet + 1
1839 attributes.append(("firstSheet", firstsheet))
1841 # Store the activeTab attribute when it isn't the first sheet.
1842 if self.worksheet_meta.activesheet > 0:
1843 attributes.append(("activeTab", self.worksheet_meta.activesheet))
1845 self._xml_empty_tag("workbookView", attributes)
1847 def _write_sheets(self) -> None:
1848 # Write <sheets> element.
1849 self._xml_start_tag("sheets")
1851 id_num = 1
1852 for worksheet in self.worksheets():
1853 self._write_sheet(worksheet.name, id_num, worksheet.hidden)
1854 id_num += 1
1856 self._xml_end_tag("sheets")
1858 def _write_sheet(self, name, sheet_id, hidden) -> None:
1859 # Write <sheet> element.
1860 attributes = [
1861 ("name", name),
1862 ("sheetId", sheet_id),
1863 ]
1865 if hidden == 1:
1866 attributes.append(("state", "hidden"))
1867 elif hidden == 2:
1868 attributes.append(("state", "veryHidden"))
1870 attributes.append(("r:id", "rId" + str(sheet_id)))
1872 self._xml_empty_tag("sheet", attributes)
1874 def _write_calc_pr(self) -> None:
1875 # Write the <calcPr> element.
1876 attributes = [("calcId", self.calc_id)]
1878 if self.calc_mode == "manual":
1879 attributes.append(("calcMode", self.calc_mode))
1880 attributes.append(("calcOnSave", "0"))
1881 elif self.calc_mode == "autoNoTable":
1882 attributes.append(("calcMode", self.calc_mode))
1884 if self.calc_on_load:
1885 attributes.append(("fullCalcOnLoad", "1"))
1887 self._xml_empty_tag("calcPr", attributes)
1889 def _write_defined_names(self) -> None:
1890 # Write the <definedNames> element.
1891 if not self.defined_names:
1892 return
1894 self._xml_start_tag("definedNames")
1896 for defined_name in self.defined_names:
1897 self._write_defined_name(defined_name)
1899 self._xml_end_tag("definedNames")
1901 def _write_defined_name(self, defined_name) -> None:
1902 # Write the <definedName> element.
1903 name = defined_name[0]
1904 sheet_id = defined_name[1]
1905 sheet_range = defined_name[2]
1906 hidden = defined_name[3]
1908 attributes = [("name", name)]
1910 if sheet_id != -1:
1911 attributes.append(("localSheetId", sheet_id))
1912 if hidden:
1913 attributes.append(("hidden", 1))
1915 self._xml_data_element("definedName", sheet_range, attributes)
1918# A metadata class to share data between worksheets.
1919class WorksheetMeta:
1920 """
1921 A class to track worksheets data such as the active sheet and the
1922 first sheet.
1924 """
1926 def __init__(self) -> None:
1927 self.activesheet = 0
1928 self.firstsheet = 0
1931# A helper class to share embedded images between worksheets.
1932class EmbeddedImages:
1933 """
1934 A class to track duplicate embedded images between worksheets.
1936 """
1938 def __init__(self) -> None:
1939 self.images = []
1940 self.image_indexes = {}
1942 def get_image_index(self, image: Image):
1943 """
1944 Get the index of an embedded image.
1946 Args:
1947 image: The image to lookup.
1949 Returns:
1950 The image index.
1952 """
1953 image_index = self.image_indexes.get(image._digest)
1955 if image_index is None:
1956 self.images.append(image)
1957 image_index = len(self.images)
1958 self.image_indexes[image._digest] = image_index
1960 return image_index
1962 def has_images(self):
1963 """
1964 Check if the worksheet has embedded images.
1966 Args:
1967 None.
1969 Returns:
1970 Boolean.
1972 """
1973 return len(self.images) > 0