Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.8/site-packages/xlsxwriter/workbook.py: 52%
Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1###############################################################################
2#
3# Workbook - A class for writing the Excel XLSX Workbook file.
4#
5# SPDX-License-Identifier: BSD-2-Clause
6# Copyright 2013-2024, John McNamara, jmcnamara@cpan.org
7#
9# Standard packages.
10import operator
11import os
12import re
13import time
14from datetime import datetime, timezone
15from decimal import Decimal
16from fractions import Fraction
17from warnings import warn
18from zipfile import ZipFile, ZipInfo, ZIP_DEFLATED, LargeZipFile
21# Package imports.
22from . import xmlwriter
23from .worksheet import Worksheet
24from .chartsheet import Chartsheet
25from .sharedstrings import SharedStringTable
26from .format import Format
27from .packager import Packager
28from .utility import xl_cell_to_rowcol
29from .chart_area import ChartArea
30from .chart_bar import ChartBar
31from .chart_column import ChartColumn
32from .chart_doughnut import ChartDoughnut
33from .chart_line import ChartLine
34from .chart_pie import ChartPie
35from .chart_radar import ChartRadar
36from .chart_scatter import ChartScatter
37from .chart_stock import ChartStock
38from .exceptions import InvalidWorksheetName
39from .exceptions import DuplicateWorksheetName
40from .exceptions import FileCreateError
41from .exceptions import FileSizeError
42from .utility import get_image_properties
45class Workbook(xmlwriter.XMLwriter):
46 """
47 A class for writing the Excel XLSX Workbook file.
50 """
52 ###########################################################################
53 #
54 # Public API.
55 #
56 ###########################################################################
57 chartsheet_class = Chartsheet
58 worksheet_class = Worksheet
60 def __init__(self, filename=None, options=None):
61 """
62 Constructor.
64 """
65 if options is None:
66 options = {}
68 super(Workbook, self).__init__()
70 self.filename = filename
72 self.tmpdir = options.get("tmpdir", None)
73 self.date_1904 = options.get("date_1904", False)
74 self.strings_to_numbers = options.get("strings_to_numbers", False)
75 self.strings_to_formulas = options.get("strings_to_formulas", True)
76 self.strings_to_urls = options.get("strings_to_urls", True)
77 self.nan_inf_to_errors = options.get("nan_inf_to_errors", False)
78 self.default_date_format = options.get("default_date_format", None)
79 self.constant_memory = options.get("constant_memory", False)
80 self.in_memory = options.get("in_memory", False)
81 self.excel2003_style = options.get("excel2003_style", False)
82 self.remove_timezone = options.get("remove_timezone", False)
83 self.use_future_functions = options.get("use_future_functions", False)
84 self.default_format_properties = options.get("default_format_properties", {})
86 self.max_url_length = options.get("max_url_length", 2079)
87 if self.max_url_length < 255:
88 self.max_url_length = 2079
90 if options.get("use_zip64"):
91 self.allow_zip64 = True
92 else:
93 self.allow_zip64 = False
95 self.worksheet_meta = WorksheetMeta()
96 self.selected = 0
97 self.fileclosed = 0
98 self.filehandle = None
99 self.internal_fh = 0
100 self.sheet_name = "Sheet"
101 self.chart_name = "Chart"
102 self.sheetname_count = 0
103 self.chartname_count = 0
104 self.worksheets_objs = []
105 self.charts = []
106 self.drawings = []
107 self.sheetnames = {}
108 self.formats = []
109 self.xf_formats = []
110 self.xf_format_indices = {}
111 self.dxf_formats = []
112 self.dxf_format_indices = {}
113 self.palette = []
114 self.font_count = 0
115 self.num_formats = []
116 self.defined_names = []
117 self.named_ranges = []
118 self.custom_colors = []
119 self.doc_properties = {}
120 self.custom_properties = []
121 self.createtime = datetime.now(timezone.utc)
122 self.num_vml_files = 0
123 self.num_comment_files = 0
124 self.x_window = 240
125 self.y_window = 15
126 self.window_width = 16095
127 self.window_height = 9660
128 self.tab_ratio = 600
129 self.str_table = SharedStringTable()
130 self.vba_project = None
131 self.vba_project_is_stream = False
132 self.vba_project_signature = None
133 self.vba_project_signature_is_stream = False
134 self.vba_codename = None
135 self.image_types = {}
136 self.images = []
137 self.border_count = 0
138 self.fill_count = 0
139 self.drawing_count = 0
140 self.calc_mode = "auto"
141 self.calc_on_load = True
142 self.calc_id = 124519
143 self.has_comments = False
144 self.read_only = 0
145 self.has_metadata = False
146 self.has_embedded_images = False
147 self.has_dynamic_functions = False
148 self.has_embedded_descriptions = False
149 self.embedded_images = EmbeddedImages()
151 # We can't do 'constant_memory' mode while doing 'in_memory' mode.
152 if self.in_memory:
153 self.constant_memory = False
155 # Add the default cell format.
156 if self.excel2003_style:
157 self.add_format({"xf_index": 0, "font_family": 0})
158 else:
159 self.add_format({"xf_index": 0})
161 # Add a default URL format.
162 self.default_url_format = self.add_format({"hyperlink": True})
164 # Add the default date format.
165 if self.default_date_format is not None:
166 self.default_date_format = self.add_format(
167 {"num_format": self.default_date_format}
168 )
170 def __enter__(self):
171 """Return self object to use with "with" statement."""
172 return self
174 def __exit__(self, type, value, traceback):
175 """Close workbook when exiting "with" statement."""
176 self.close()
178 def add_worksheet(self, name=None, worksheet_class=None):
179 """
180 Add a new worksheet to the Excel workbook.
182 Args:
183 name: The worksheet name. Defaults to 'Sheet1', etc.
185 Returns:
186 Reference to a worksheet object.
188 """
189 if worksheet_class is None:
190 worksheet_class = self.worksheet_class
192 return self._add_sheet(name, worksheet_class=worksheet_class)
194 def add_chartsheet(self, name=None, chartsheet_class=None):
195 """
196 Add a new chartsheet to the Excel workbook.
198 Args:
199 name: The chartsheet name. Defaults to 'Sheet1', etc.
201 Returns:
202 Reference to a chartsheet object.
204 """
205 if chartsheet_class is None:
206 chartsheet_class = self.chartsheet_class
208 return self._add_sheet(name, worksheet_class=chartsheet_class)
210 def add_format(self, properties=None):
211 """
212 Add a new Format to the Excel Workbook.
214 Args:
215 properties: The format properties.
217 Returns:
218 Reference to a Format object.
220 """
221 format_properties = self.default_format_properties.copy()
223 if self.excel2003_style:
224 format_properties = {"font_name": "Arial", "font_size": 10, "theme": 1 * -1}
226 if properties:
227 format_properties.update(properties)
229 xf_format = Format(
230 format_properties, self.xf_format_indices, self.dxf_format_indices
231 )
233 # Store the format reference.
234 self.formats.append(xf_format)
236 return xf_format
238 def add_chart(self, options):
239 """
240 Create a chart object.
242 Args:
243 options: The chart type and subtype options.
245 Returns:
246 Reference to a Chart object.
248 """
250 # Type must be specified so we can create the required chart instance.
251 chart_type = options.get("type")
252 if chart_type is None:
253 warn("Chart type must be defined in add_chart()")
254 return
256 if chart_type == "area":
257 chart = ChartArea(options)
258 elif chart_type == "bar":
259 chart = ChartBar(options)
260 elif chart_type == "column":
261 chart = ChartColumn(options)
262 elif chart_type == "doughnut":
263 chart = ChartDoughnut(options)
264 elif chart_type == "line":
265 chart = ChartLine(options)
266 elif chart_type == "pie":
267 chart = ChartPie(options)
268 elif chart_type == "radar":
269 chart = ChartRadar(options)
270 elif chart_type == "scatter":
271 chart = ChartScatter(options)
272 elif chart_type == "stock":
273 chart = ChartStock(options)
274 else:
275 warn("Unknown chart type '%s' in add_chart()" % chart_type)
276 return
278 # Set the embedded chart name if present.
279 if "name" in options:
280 chart.chart_name = options["name"]
282 chart.embedded = True
283 chart.date_1904 = self.date_1904
284 chart.remove_timezone = self.remove_timezone
286 self.charts.append(chart)
288 return chart
290 def add_vba_project(self, vba_project, is_stream=False):
291 """
292 Add a vbaProject binary to the Excel workbook.
294 Args:
295 vba_project: The vbaProject binary file name.
296 is_stream: vba_project is an in memory byte stream.
298 Returns:
299 Nothing.
301 """
302 if not is_stream and not os.path.exists(vba_project):
303 warn("VBA project binary file '%s' not found." % vba_project)
304 return -1
306 if self.vba_codename is None:
307 self.vba_codename = "ThisWorkbook"
309 self.vba_project = vba_project
310 self.vba_project_is_stream = is_stream
312 def add_signed_vba_project(
313 self, vba_project, signature, project_is_stream=False, signature_is_stream=False
314 ):
315 """
316 Add a vbaProject binary and a vbaProjectSignature binary to the
317 Excel workbook.
319 Args:
320 vba_project: The vbaProject binary file name.
321 signature: The vbaProjectSignature binary file name.
322 project_is_stream: vba_project is an in memory byte stream.
323 signature_is_stream: signature is an in memory byte stream.
325 Returns:
326 Nothing.
328 """
329 if self.add_vba_project(vba_project, project_is_stream) == -1:
330 return -1
332 if not signature_is_stream and not os.path.exists(signature):
333 warn("VBA project signature binary file '%s' not found." % signature)
334 return -1
336 self.vba_project_signature = signature
337 self.vba_project_signature_is_stream = signature_is_stream
339 def close(self):
340 """
341 Call finalization code and close file.
343 Args:
344 None.
346 Returns:
347 Nothing.
349 """
350 if not self.fileclosed:
351 try:
352 self._store_workbook()
353 except IOError as e:
354 raise FileCreateError(e)
355 except LargeZipFile:
356 raise FileSizeError(
357 "Filesize would require ZIP64 extensions. "
358 "Use workbook.use_zip64()."
359 )
361 self.fileclosed = True
363 # Ensure all constant_memory temp files are closed.
364 if self.constant_memory:
365 for worksheet in self.worksheets():
366 worksheet._opt_close()
368 else:
369 warn("Calling close() on already closed file.")
371 def set_size(self, width, height):
372 """
373 Set the size of a workbook window.
375 Args:
376 width: Width of the window in pixels.
377 height: Height of the window in pixels.
379 Returns:
380 Nothing.
382 """
383 # Convert the width/height to twips at 96 dpi.
384 if width:
385 self.window_width = int(width * 1440 / 96)
386 else:
387 self.window_width = 16095
389 if height:
390 self.window_height = int(height * 1440 / 96)
391 else:
392 self.window_height = 9660
394 def set_tab_ratio(self, tab_ratio=None):
395 """
396 Set the ratio between worksheet tabs and the horizontal slider.
398 Args:
399 tab_ratio: The tab ratio, 0 <= tab_ratio <= 100
401 Returns:
402 Nothing.
404 """
405 if tab_ratio is None:
406 return
408 if tab_ratio < 0 or tab_ratio > 100:
409 warn("Tab ratio '%d' outside: 0 <= tab_ratio <= 100" % tab_ratio)
410 else:
411 self.tab_ratio = int(tab_ratio * 10)
413 def set_properties(self, properties):
414 """
415 Set the document properties such as Title, Author etc.
417 Args:
418 properties: Dictionary of document properties.
420 Returns:
421 Nothing.
423 """
424 self.doc_properties = properties
426 def set_custom_property(self, name, value, property_type=None):
427 """
428 Set a custom document property.
430 Args:
431 name: The name of the custom property.
432 value: The value of the custom property.
433 property_type: The type of the custom property. Optional.
435 Returns:
436 Nothing.
438 """
439 if name is None or value is None:
440 warn(
441 "The name and value parameters must be non-None in "
442 "set_custom_property()"
443 )
444 return -1
446 if property_type is None:
447 # Determine the property type from the Python type.
448 if isinstance(value, bool):
449 property_type = "bool"
450 elif isinstance(value, datetime):
451 property_type = "date"
452 elif isinstance(value, int):
453 property_type = "number_int"
454 elif isinstance(value, (float, int, Decimal, Fraction)):
455 property_type = "number"
456 else:
457 property_type = "text"
459 if property_type == "date":
460 value = value.strftime("%Y-%m-%dT%H:%M:%SZ")
462 if property_type == "text" and len(value) > 255:
463 warn(
464 "Length of 'value' parameter exceeds Excel's limit of 255 "
465 "characters in set_custom_property(): '%s'" % value
466 )
468 if len(name) > 255:
469 warn(
470 "Length of 'name' parameter exceeds Excel's limit of 255 "
471 "characters in set_custom_property(): '%s'" % name
472 )
474 self.custom_properties.append((name, value, property_type))
476 def set_calc_mode(self, mode, calc_id=None):
477 """
478 Set the Excel calculation mode for the workbook.
480 Args:
481 mode: String containing one of:
482 * manual
483 * auto_except_tables
484 * auto
486 Returns:
487 Nothing.
489 """
490 self.calc_mode = mode
492 if mode == "manual":
493 self.calc_on_load = False
494 elif mode == "auto_except_tables":
495 self.calc_mode = "autoNoTable"
497 # Leave undocumented for now. Rarely required.
498 if calc_id:
499 self.calc_id = calc_id
501 def define_name(self, name, formula):
502 # Create a defined name in Excel. We handle global/workbook level
503 # names and local/worksheet names.
504 """
505 Create a defined name in the workbook.
507 Args:
508 name: The defined name.
509 formula: The cell or range that the defined name refers to.
511 Returns:
512 Nothing.
514 """
515 sheet_index = None
516 sheetname = ""
518 # Remove the = sign from the formula if it exists.
519 if formula.startswith("="):
520 formula = formula.lstrip("=")
522 # Local defined names are formatted like "Sheet1!name".
523 sheet_parts = re.compile(r"^([^!]+)!([^!]+)$")
524 match = sheet_parts.match(name)
526 if match:
527 sheetname = match.group(1)
528 name = match.group(2)
529 sheet_index = self._get_sheet_index(sheetname)
531 # Warn if the sheet index wasn't found.
532 if sheet_index is None:
533 warn("Unknown sheet name '%s' in defined_name()" % sheetname)
534 return -1
535 else:
536 # Use -1 to indicate global names.
537 sheet_index = -1
539 # Warn if the defined name contains invalid chars as defined by Excel.
540 if not re.match(r"^[\w\\][\w\\.]*$", name, re.UNICODE) or re.match(
541 r"^\d", name
542 ):
543 warn("Invalid Excel characters in defined_name(): '%s'" % name)
544 return -1
546 # Warn if the defined name looks like a cell name.
547 if re.match(r"^[a-zA-Z][a-zA-Z]?[a-dA-D]?\d+$", name):
548 warn("Name looks like a cell name in defined_name(): '%s'" % name)
549 return -1
551 # Warn if the name looks like a R1C1 cell reference.
552 if re.match(r"^[rcRC]$", name) or re.match(r"^[rcRC]\d+[rcRC]\d+$", name):
553 warn("Invalid name '%s' like a RC cell ref in defined_name()" % name)
554 return -1
556 self.defined_names.append([name, sheet_index, formula, False])
558 def worksheets(self):
559 """
560 Return a list of the worksheet objects in the workbook.
562 Args:
563 None.
565 Returns:
566 A list of worksheet objects.
568 """
569 return self.worksheets_objs
571 def get_worksheet_by_name(self, name):
572 """
573 Return a worksheet object in the workbook using the sheetname.
575 Args:
576 name: The name of the worksheet.
578 Returns:
579 A worksheet object or None.
581 """
582 return self.sheetnames.get(name)
584 def get_default_url_format(self):
585 """
586 Get the default url format used when a user defined format isn't
587 specified with write_url(). The format is the hyperlink style defined
588 by Excel for the default theme.
590 Args:
591 None.
593 Returns:
594 A format object.
596 """
597 return self.default_url_format
599 def use_zip64(self):
600 """
601 Allow ZIP64 extensions when writing xlsx file zip container.
603 Args:
604 None.
606 Returns:
607 Nothing.
609 """
610 self.allow_zip64 = True
612 def set_vba_name(self, name=None):
613 """
614 Set the VBA name for the workbook. By default the workbook is referred
615 to as ThisWorkbook in VBA.
617 Args:
618 name: The VBA name for the workbook.
620 Returns:
621 Nothing.
623 """
624 if name is not None:
625 self.vba_codename = name
626 else:
627 self.vba_codename = "ThisWorkbook"
629 def read_only_recommended(self):
630 """
631 Set the Excel "Read-only recommended" option when saving a file.
633 Args:
634 None.
636 Returns:
637 Nothing.
639 """
640 self.read_only = 2
642 ###########################################################################
643 #
644 # Private API.
645 #
646 ###########################################################################
648 def _assemble_xml_file(self):
649 # Assemble and write the XML file.
651 # Prepare format object for passing to Style.pm.
652 self._prepare_format_properties()
654 # Write the XML declaration.
655 self._xml_declaration()
657 # Write the workbook element.
658 self._write_workbook()
660 # Write the fileVersion element.
661 self._write_file_version()
663 # Write the fileSharing element.
664 self._write_file_sharing()
666 # Write the workbookPr element.
667 self._write_workbook_pr()
669 # Write the bookViews element.
670 self._write_book_views()
672 # Write the sheets element.
673 self._write_sheets()
675 # Write the workbook defined names.
676 self._write_defined_names()
678 # Write the calcPr element.
679 self._write_calc_pr()
681 # Close the workbook tag.
682 self._xml_end_tag("workbook")
684 # Close the file.
685 self._xml_close()
687 def _store_workbook(self):
688 # Create the xlsx/zip file.
689 try:
690 xlsx_file = ZipFile(
691 self.filename,
692 "w",
693 compression=ZIP_DEFLATED,
694 allowZip64=self.allow_zip64,
695 )
696 except IOError as e:
697 raise e
699 # Assemble worksheets into a workbook.
700 packager = self._get_packager()
702 # Add a default worksheet if non have been added.
703 if not self.worksheets():
704 self.add_worksheet()
706 # Ensure that at least one worksheet has been selected.
707 if self.worksheet_meta.activesheet == 0:
708 self.worksheets_objs[0].selected = 1
709 self.worksheets_objs[0].hidden = 0
711 # Set the active sheet.
712 for sheet in self.worksheets():
713 if sheet.index == self.worksheet_meta.activesheet:
714 sheet.active = 1
716 # Set the sheet vba_codename the workbook has a vbaProject binary.
717 if self.vba_project:
718 for sheet in self.worksheets():
719 if sheet.vba_codename is None:
720 sheet.set_vba_name()
722 # Convert the SST strings data structure.
723 self._prepare_sst_string_data()
725 # Prepare the worksheet VML elements such as comments and buttons.
726 self._prepare_vml()
728 # Set the defined names for the worksheets such as Print Titles.
729 self._prepare_defined_names()
731 # Prepare the drawings, charts and images.
732 self._prepare_drawings()
734 # Add cached data to charts.
735 self._add_chart_data()
737 # Prepare the worksheet tables.
738 self._prepare_tables()
740 # Prepare the metadata file links.
741 self._prepare_metadata()
743 # Package the workbook.
744 packager._add_workbook(self)
745 packager._set_tmpdir(self.tmpdir)
746 packager._set_in_memory(self.in_memory)
747 xml_files = packager._create_package()
749 # Free up the Packager object.
750 packager = None
752 # Add XML sub-files to the Zip file with their Excel filename.
753 for file_id, file_data in enumerate(xml_files):
754 os_filename, xml_filename, is_binary = file_data
756 if self.in_memory:
757 # Set sub-file timestamp to Excel's timestamp of 1/1/1980.
758 zipinfo = ZipInfo(xml_filename, (1980, 1, 1, 0, 0, 0))
760 # Copy compression type from parent ZipFile.
761 zipinfo.compress_type = xlsx_file.compression
763 if is_binary:
764 xlsx_file.writestr(zipinfo, os_filename.getvalue())
765 else:
766 xlsx_file.writestr(zipinfo, os_filename.getvalue().encode("utf-8"))
767 else:
768 # The sub-files are tempfiles on disk, i.e, not in memory.
770 # Set sub-file timestamp to 31/1/1980 due to portability
771 # issues setting it to Excel's timestamp of 1/1/1980.
772 timestamp = time.mktime((1980, 1, 31, 0, 0, 0, 0, 0, -1))
773 os.utime(os_filename, (timestamp, timestamp))
775 try:
776 xlsx_file.write(os_filename, xml_filename)
777 os.remove(os_filename)
778 except LargeZipFile as e:
779 # Close open temp files on zipfile.LargeZipFile exception.
780 for i in range(file_id, len(xml_files) - 1):
781 os.remove(xml_files[i][0])
782 raise e
784 xlsx_file.close()
786 def _add_sheet(self, name, worksheet_class=None):
787 # Utility for shared code in add_worksheet() and add_chartsheet().
789 if worksheet_class:
790 worksheet = worksheet_class()
791 else:
792 worksheet = self.worksheet_class()
794 sheet_index = len(self.worksheets_objs)
795 name = self._check_sheetname(name, isinstance(worksheet, Chartsheet))
797 # Initialization data to pass to the worksheet.
798 init_data = {
799 "name": name,
800 "index": sheet_index,
801 "str_table": self.str_table,
802 "worksheet_meta": self.worksheet_meta,
803 "constant_memory": self.constant_memory,
804 "tmpdir": self.tmpdir,
805 "date_1904": self.date_1904,
806 "strings_to_numbers": self.strings_to_numbers,
807 "strings_to_formulas": self.strings_to_formulas,
808 "strings_to_urls": self.strings_to_urls,
809 "nan_inf_to_errors": self.nan_inf_to_errors,
810 "default_date_format": self.default_date_format,
811 "default_url_format": self.default_url_format,
812 "excel2003_style": self.excel2003_style,
813 "remove_timezone": self.remove_timezone,
814 "max_url_length": self.max_url_length,
815 "use_future_functions": self.use_future_functions,
816 "embedded_images": self.embedded_images,
817 }
819 worksheet._initialize(init_data)
821 self.worksheets_objs.append(worksheet)
822 self.sheetnames[name] = worksheet
824 return worksheet
826 def _check_sheetname(self, sheetname, is_chartsheet=False):
827 # Check for valid worksheet names. We check the length, if it contains
828 # any invalid chars and if the sheetname is unique in the workbook.
829 invalid_char = re.compile(r"[\[\]:*?/\\]")
831 # Increment the Sheet/Chart number used for default sheet names below.
832 if is_chartsheet:
833 self.chartname_count += 1
834 else:
835 self.sheetname_count += 1
837 # Supply default Sheet/Chart sheetname if none has been defined.
838 if sheetname is None or sheetname == "":
839 if is_chartsheet:
840 sheetname = self.chart_name + str(self.chartname_count)
841 else:
842 sheetname = self.sheet_name + str(self.sheetname_count)
844 # Check that sheet sheetname is <= 31. Excel limit.
845 if len(sheetname) > 31:
846 raise InvalidWorksheetName(
847 "Excel worksheet name '%s' must be <= 31 chars." % sheetname
848 )
850 # Check that sheetname doesn't contain any invalid characters.
851 if invalid_char.search(sheetname):
852 raise InvalidWorksheetName(
853 "Invalid Excel character '[]:*?/\\' in sheetname '%s'." % sheetname
854 )
856 # Check that sheetname doesn't start or end with an apostrophe.
857 if sheetname.startswith("'") or sheetname.endswith("'"):
858 raise InvalidWorksheetName(
859 'Sheet name cannot start or end with an apostrophe "%s".' % sheetname
860 )
862 # Check that the worksheet name doesn't already exist since this is a
863 # fatal Excel error. The check must be case insensitive like Excel.
864 for worksheet in self.worksheets():
865 if sheetname.lower() == worksheet.name.lower():
866 raise DuplicateWorksheetName(
867 "Sheetname '%s', with case ignored, is already in use." % sheetname
868 )
870 return sheetname
872 def _prepare_format_properties(self):
873 # Prepare all Format properties prior to passing them to styles.py.
875 # Separate format objects into XF and DXF formats.
876 self._prepare_formats()
878 # Set the font index for the format objects.
879 self._prepare_fonts()
881 # Set the number format index for the format objects.
882 self._prepare_num_formats()
884 # Set the border index for the format objects.
885 self._prepare_borders()
887 # Set the fill index for the format objects.
888 self._prepare_fills()
890 def _prepare_formats(self):
891 # Iterate through the XF Format objects and separate them into
892 # XF and DXF formats. The XF and DF formats then need to be sorted
893 # back into index order rather than creation order.
894 xf_formats = []
895 dxf_formats = []
897 # Sort into XF and DXF formats.
898 for xf_format in self.formats:
899 if xf_format.xf_index is not None:
900 xf_formats.append(xf_format)
902 if xf_format.dxf_index is not None:
903 dxf_formats.append(xf_format)
905 # Pre-extend the format lists.
906 self.xf_formats = [None] * len(xf_formats)
907 self.dxf_formats = [None] * len(dxf_formats)
909 # Rearrange formats into index order.
910 for xf_format in xf_formats:
911 index = xf_format.xf_index
912 self.xf_formats[index] = xf_format
914 for dxf_format in dxf_formats:
915 index = dxf_format.dxf_index
916 self.dxf_formats[index] = dxf_format
918 def _set_default_xf_indices(self):
919 # Set the default index for each format. Only used for testing.
921 formats = list(self.formats)
923 # Delete the default url format.
924 del formats[1]
926 # Skip the default date format if set.
927 if self.default_date_format is not None:
928 del formats[1]
930 # Set the remaining formats.
931 for xf_format in formats:
932 xf_format._get_xf_index()
934 def _prepare_fonts(self):
935 # Iterate through the XF Format objects and give them an index to
936 # non-default font elements.
937 fonts = {}
938 index = 0
940 for xf_format in self.xf_formats:
941 key = xf_format._get_font_key()
942 if key in fonts:
943 # Font has already been used.
944 xf_format.font_index = fonts[key]
945 xf_format.has_font = 0
946 else:
947 # This is a new font.
948 fonts[key] = index
949 xf_format.font_index = index
950 xf_format.has_font = 1
951 index += 1
953 self.font_count = index
955 # For DXF formats we only need to check if the properties have changed.
956 for xf_format in self.dxf_formats:
957 # The only font properties that can change for a DXF format are:
958 # color, bold, italic, underline and strikethrough.
959 if (
960 xf_format.font_color
961 or xf_format.bold
962 or xf_format.italic
963 or xf_format.underline
964 or xf_format.font_strikeout
965 ):
966 xf_format.has_dxf_font = 1
968 def _prepare_num_formats(self):
969 # User defined records in Excel start from index 0xA4.
970 unique_num_formats = {}
971 num_formats = []
972 index = 164
974 for xf_format in self.xf_formats + self.dxf_formats:
975 num_format = xf_format.num_format
977 # Check if num_format is an index to a built-in number format.
978 if not isinstance(num_format, str):
979 num_format = int(num_format)
981 # Number format '0' is indexed as 1 in Excel.
982 if num_format == 0:
983 num_format = 1
985 xf_format.num_format_index = num_format
986 continue
987 elif num_format == "0":
988 # Number format '0' is indexed as 1 in Excel.
989 xf_format.num_format_index = 1
990 continue
991 elif num_format == "General":
992 # The 'General' format has an number format index of 0.
993 xf_format.num_format_index = 0
994 continue
996 if num_format in unique_num_formats:
997 # Number xf_format has already been used.
998 xf_format.num_format_index = unique_num_formats[num_format]
999 else:
1000 # Add a new number xf_format.
1001 unique_num_formats[num_format] = index
1002 xf_format.num_format_index = index
1003 index += 1
1005 # Only increase font count for XF formats (not DXF formats).
1006 if xf_format.xf_index:
1007 num_formats.append(num_format)
1009 self.num_formats = num_formats
1011 def _prepare_borders(self):
1012 # Iterate through the XF Format objects and give them an index to
1013 # non-default border elements.
1014 borders = {}
1015 index = 0
1017 for xf_format in self.xf_formats:
1018 key = xf_format._get_border_key()
1020 if key in borders:
1021 # Border has already been used.
1022 xf_format.border_index = borders[key]
1023 xf_format.has_border = 0
1024 else:
1025 # This is a new border.
1026 borders[key] = index
1027 xf_format.border_index = index
1028 xf_format.has_border = 1
1029 index += 1
1031 self.border_count = index
1033 # For DXF formats we only need to check if the properties have changed.
1034 has_border = re.compile(r"[^0:]")
1036 for xf_format in self.dxf_formats:
1037 key = xf_format._get_border_key()
1039 if has_border.search(key):
1040 xf_format.has_dxf_border = 1
1042 def _prepare_fills(self):
1043 # Iterate through the XF Format objects and give them an index to
1044 # non-default fill elements.
1045 # The user defined fill properties start from 2 since there are 2
1046 # default fills: patternType="none" and patternType="gray125".
1047 fills = {}
1048 index = 2 # Start from 2. See above.
1050 # Add the default fills.
1051 fills["0:0:0"] = 0
1052 fills["17:0:0"] = 1
1054 # Store the DXF colors separately since them may be reversed below.
1055 for xf_format in self.dxf_formats:
1056 if xf_format.pattern or xf_format.bg_color or xf_format.fg_color:
1057 xf_format.has_dxf_fill = 1
1058 xf_format.dxf_bg_color = xf_format.bg_color
1059 xf_format.dxf_fg_color = xf_format.fg_color
1061 for xf_format in self.xf_formats:
1062 # The following logical statements jointly take care of special
1063 # cases in relation to cell colors and patterns:
1064 # 1. For a solid fill (_pattern == 1) Excel reverses the role of
1065 # foreground and background colors, and
1066 # 2. If the user specifies a foreground or background color
1067 # without a pattern they probably wanted a solid fill, so we fill
1068 # in the defaults.
1069 if (
1070 xf_format.pattern == 1
1071 and xf_format.bg_color != 0
1072 and xf_format.fg_color != 0
1073 ):
1074 tmp = xf_format.fg_color
1075 xf_format.fg_color = xf_format.bg_color
1076 xf_format.bg_color = tmp
1078 if (
1079 xf_format.pattern <= 1
1080 and xf_format.bg_color != 0
1081 and xf_format.fg_color == 0
1082 ):
1083 xf_format.fg_color = xf_format.bg_color
1084 xf_format.bg_color = 0
1085 xf_format.pattern = 1
1087 if (
1088 xf_format.pattern <= 1
1089 and xf_format.bg_color == 0
1090 and xf_format.fg_color != 0
1091 ):
1092 xf_format.pattern = 1
1094 key = xf_format._get_fill_key()
1096 if key in fills:
1097 # Fill has already been used.
1098 xf_format.fill_index = fills[key]
1099 xf_format.has_fill = 0
1100 else:
1101 # This is a new fill.
1102 fills[key] = index
1103 xf_format.fill_index = index
1104 xf_format.has_fill = 1
1105 index += 1
1107 self.fill_count = index
1109 def _prepare_defined_names(self):
1110 # Iterate through the worksheets and store any defined names in
1111 # addition to any user defined names. Stores the defined names
1112 # for the Workbook.xml and the named ranges for App.xml.
1113 defined_names = self.defined_names
1115 for sheet in self.worksheets():
1116 # Check for Print Area settings.
1117 if sheet.autofilter_area:
1118 hidden = 1
1119 sheet_range = sheet.autofilter_area
1120 # Store the defined names.
1121 defined_names.append(
1122 ["_xlnm._FilterDatabase", sheet.index, sheet_range, hidden]
1123 )
1125 # Check for Print Area settings.
1126 if sheet.print_area_range:
1127 hidden = 0
1128 sheet_range = sheet.print_area_range
1129 # Store the defined names.
1130 defined_names.append(
1131 ["_xlnm.Print_Area", sheet.index, sheet_range, hidden]
1132 )
1134 # Check for repeat rows/cols referred to as Print Titles.
1135 if sheet.repeat_col_range or sheet.repeat_row_range:
1136 hidden = 0
1137 sheet_range = ""
1138 if sheet.repeat_col_range and sheet.repeat_row_range:
1139 sheet_range = sheet.repeat_col_range + "," + sheet.repeat_row_range
1140 else:
1141 sheet_range = sheet.repeat_col_range + sheet.repeat_row_range
1142 # Store the defined names.
1143 defined_names.append(
1144 ["_xlnm.Print_Titles", sheet.index, sheet_range, hidden]
1145 )
1147 defined_names = self._sort_defined_names(defined_names)
1148 self.defined_names = defined_names
1149 self.named_ranges = self._extract_named_ranges(defined_names)
1151 def _sort_defined_names(self, names):
1152 # Sort the list of list of internal and user defined names in
1153 # the same order as used by Excel.
1155 # Add a normalize name string to each list for sorting.
1156 for name_list in names:
1157 (defined_name, _, sheet_name, _) = name_list
1159 # Normalize the defined name by removing any leading '_xmln.'
1160 # from internal names and lowercasing the string.
1161 defined_name = defined_name.replace("_xlnm.", "").lower()
1163 # Normalize the sheetname by removing the leading quote and
1164 # lowercasing the string.
1165 sheet_name = sheet_name.lstrip("'").lower()
1167 name_list.append(defined_name + "::" + sheet_name)
1169 # Sort based on the normalized key.
1170 names.sort(key=operator.itemgetter(4))
1172 # Remove the extra key used for sorting.
1173 for name_list in names:
1174 name_list.pop()
1176 return names
1178 def _prepare_drawings(self):
1179 # Iterate through the worksheets and set up chart and image drawings.
1180 chart_ref_id = 0
1181 ref_id = 0
1182 drawing_id = 0
1183 image_ids = {}
1184 header_image_ids = {}
1185 background_ids = {}
1187 # Store the image types for any embedded images.
1188 for image_data in self.embedded_images.images:
1189 image_type = image_data[1]
1190 self.image_types[image_type] = True
1191 if image_data[3]:
1192 self.has_embedded_descriptions = True
1194 image_ref_id = len(self.embedded_images.images)
1196 for sheet in self.worksheets():
1197 chart_count = len(sheet.charts)
1198 image_count = len(sheet.images)
1199 shape_count = len(sheet.shapes)
1201 header_image_count = len(sheet.header_images)
1202 footer_image_count = len(sheet.footer_images)
1203 has_background = sheet.background_image
1204 has_drawing = False
1206 if not (
1207 chart_count
1208 or image_count
1209 or shape_count
1210 or header_image_count
1211 or footer_image_count
1212 or has_background
1213 ):
1214 continue
1216 # Don't increase the drawing_id header/footer images.
1217 if chart_count or image_count or shape_count:
1218 drawing_id += 1
1219 has_drawing = True
1221 # Prepare the background images.
1222 if sheet.background_image:
1223 if sheet.background_bytes:
1224 filename = ""
1225 image_data = sheet.background_image
1226 else:
1227 filename = sheet.background_image
1228 image_data = None
1230 (
1231 image_type,
1232 _,
1233 _,
1234 _,
1235 _,
1236 _,
1237 digest,
1238 ) = get_image_properties(filename, image_data)
1240 self.image_types[image_type] = True
1242 if digest in background_ids:
1243 ref_id = background_ids[digest]
1244 else:
1245 image_ref_id += 1
1246 ref_id = image_ref_id
1247 background_ids[digest] = image_ref_id
1248 self.images.append([filename, image_type, image_data])
1250 sheet._prepare_background(ref_id, image_type)
1252 # Prepare the worksheet images.
1253 for index in range(image_count):
1254 filename = sheet.images[index][2]
1255 image_data = sheet.images[index][10]
1256 (
1257 image_type,
1258 width,
1259 height,
1260 name,
1261 x_dpi,
1262 y_dpi,
1263 digest,
1264 ) = get_image_properties(filename, image_data)
1266 self.image_types[image_type] = True
1268 if digest in image_ids:
1269 ref_id = image_ids[digest]
1270 else:
1271 image_ref_id += 1
1272 ref_id = image_ref_id
1273 image_ids[digest] = image_ref_id
1274 self.images.append([filename, image_type, image_data])
1276 sheet._prepare_image(
1277 index,
1278 ref_id,
1279 drawing_id,
1280 width,
1281 height,
1282 name,
1283 image_type,
1284 x_dpi,
1285 y_dpi,
1286 digest,
1287 )
1289 # Prepare the worksheet charts.
1290 for index in range(chart_count):
1291 chart_ref_id += 1
1292 sheet._prepare_chart(index, chart_ref_id, drawing_id)
1294 # Prepare the worksheet shapes.
1295 for index in range(shape_count):
1296 sheet._prepare_shape(index, drawing_id)
1298 # Prepare the header images.
1299 for index in range(header_image_count):
1300 filename = sheet.header_images[index][0]
1301 image_data = sheet.header_images[index][1]
1302 position = sheet.header_images[index][2]
1304 (
1305 image_type,
1306 width,
1307 height,
1308 name,
1309 x_dpi,
1310 y_dpi,
1311 digest,
1312 ) = get_image_properties(filename, image_data)
1314 self.image_types[image_type] = True
1316 if digest in header_image_ids:
1317 ref_id = header_image_ids[digest]
1318 else:
1319 image_ref_id += 1
1320 ref_id = image_ref_id
1321 header_image_ids[digest] = image_ref_id
1322 self.images.append([filename, image_type, image_data])
1324 sheet._prepare_header_image(
1325 ref_id,
1326 width,
1327 height,
1328 name,
1329 image_type,
1330 position,
1331 x_dpi,
1332 y_dpi,
1333 digest,
1334 )
1336 # Prepare the footer images.
1337 for index in range(footer_image_count):
1338 filename = sheet.footer_images[index][0]
1339 image_data = sheet.footer_images[index][1]
1340 position = sheet.footer_images[index][2]
1342 (
1343 image_type,
1344 width,
1345 height,
1346 name,
1347 x_dpi,
1348 y_dpi,
1349 digest,
1350 ) = get_image_properties(filename, image_data)
1352 self.image_types[image_type] = True
1354 if digest in header_image_ids:
1355 ref_id = header_image_ids[digest]
1356 else:
1357 image_ref_id += 1
1358 ref_id = image_ref_id
1359 header_image_ids[digest] = image_ref_id
1360 self.images.append([filename, image_type, image_data])
1362 sheet._prepare_header_image(
1363 ref_id,
1364 width,
1365 height,
1366 name,
1367 image_type,
1368 position,
1369 x_dpi,
1370 y_dpi,
1371 digest,
1372 )
1374 if has_drawing:
1375 drawing = sheet.drawing
1376 self.drawings.append(drawing)
1378 # Remove charts that were created but not inserted into worksheets.
1379 for chart in self.charts[:]:
1380 if chart.id == -1:
1381 self.charts.remove(chart)
1383 # Sort the workbook charts references into the order that the were
1384 # written to the worksheets above.
1385 self.charts = sorted(self.charts, key=lambda chart: chart.id)
1387 self.drawing_count = drawing_id
1389 def _extract_named_ranges(self, defined_names):
1390 # Extract the named ranges from the sorted list of defined names.
1391 # These are used in the App.xml file.
1392 named_ranges = []
1394 for defined_name in defined_names:
1395 name = defined_name[0]
1396 index = defined_name[1]
1397 sheet_range = defined_name[2]
1399 # Skip autoFilter ranges.
1400 if name == "_xlnm._FilterDatabase":
1401 continue
1403 # We are only interested in defined names with ranges.
1404 if "!" in sheet_range:
1405 sheet_name, _ = sheet_range.split("!", 1)
1407 # Match Print_Area and Print_Titles xlnm types.
1408 if name.startswith("_xlnm."):
1409 xlnm_type = name.replace("_xlnm.", "")
1410 name = sheet_name + "!" + xlnm_type
1411 elif index != -1:
1412 name = sheet_name + "!" + name
1414 named_ranges.append(name)
1416 return named_ranges
1418 def _get_sheet_index(self, sheetname):
1419 # Convert a sheet name to its index. Return None otherwise.
1420 sheetname = sheetname.strip("'")
1422 if sheetname in self.sheetnames:
1423 return self.sheetnames[sheetname].index
1424 else:
1425 return None
1427 def _prepare_vml(self):
1428 # Iterate through the worksheets and set up the VML objects.
1429 comment_id = 0
1430 vml_drawing_id = 0
1431 vml_data_id = 1
1432 vml_header_id = 0
1433 vml_shape_id = 1024
1434 vml_files = 0
1435 comment_files = 0
1437 for sheet in self.worksheets():
1438 if not sheet.has_vml and not sheet.has_header_vml:
1439 continue
1441 vml_files += 1
1443 if sheet.has_vml:
1444 if sheet.has_comments:
1445 comment_files += 1
1446 comment_id += 1
1447 self.has_comments = True
1449 vml_drawing_id += 1
1451 count = sheet._prepare_vml_objects(
1452 vml_data_id, vml_shape_id, vml_drawing_id, comment_id
1453 )
1455 # Each VML should start with a shape id incremented by 1024.
1456 vml_data_id += 1 * int((1024 + count) / 1024)
1457 vml_shape_id += 1024 * int((1024 + count) / 1024)
1459 if sheet.has_header_vml:
1460 vml_header_id += 1
1461 vml_drawing_id += 1
1462 sheet._prepare_header_vml_objects(vml_header_id, vml_drawing_id)
1464 self.num_vml_files = vml_files
1465 self.num_comment_files = comment_files
1467 def _prepare_tables(self):
1468 # Set the table ids for the worksheet tables.
1469 table_id = 0
1470 seen = {}
1472 for sheet in self.worksheets():
1473 table_count = len(sheet.tables)
1475 if not table_count:
1476 continue
1478 sheet._prepare_tables(table_id + 1, seen)
1479 table_id += table_count
1481 def _prepare_metadata(self):
1482 # Set the metadata rel link.
1483 self.has_embedded_images = self.embedded_images.has_images()
1484 self.has_metadata = self.has_embedded_images
1486 for sheet in self.worksheets():
1487 if sheet.has_dynamic_arrays:
1488 self.has_metadata = True
1489 self.has_dynamic_functions = True
1491 def _add_chart_data(self):
1492 # Add "cached" data to charts to provide the numCache and strCache
1493 # data for series and title/axis ranges.
1494 worksheets = {}
1495 seen_ranges = {}
1496 charts = []
1498 # Map worksheet names to worksheet objects.
1499 for worksheet in self.worksheets():
1500 worksheets[worksheet.name] = worksheet
1502 # Build a list of the worksheet charts including any combined charts.
1503 for chart in self.charts:
1504 charts.append(chart)
1505 if chart.combined:
1506 charts.append(chart.combined)
1508 for chart in charts:
1509 for c_range in chart.formula_ids.keys():
1510 r_id = chart.formula_ids[c_range]
1512 # Skip if the series has user defined data.
1513 if chart.formula_data[r_id] is not None:
1514 if c_range not in seen_ranges or seen_ranges[c_range] is None:
1515 data = chart.formula_data[r_id]
1516 seen_ranges[c_range] = data
1517 continue
1519 # Check to see if the data is already cached locally.
1520 if c_range in seen_ranges:
1521 chart.formula_data[r_id] = seen_ranges[c_range]
1522 continue
1524 # Convert the range formula to a sheet name and cell range.
1525 (sheetname, cells) = self._get_chart_range(c_range)
1527 # Skip if we couldn't parse the formula.
1528 if sheetname is None:
1529 continue
1531 # Handle non-contiguous ranges like:
1532 # (Sheet1!$A$1:$A$2,Sheet1!$A$4:$A$5).
1533 # We don't try to parse them. We just return an empty list.
1534 if sheetname.startswith("("):
1535 chart.formula_data[r_id] = []
1536 seen_ranges[c_range] = []
1537 continue
1539 # Warn if the name is unknown since it indicates a user error
1540 # in a chart series formula.
1541 if sheetname not in worksheets:
1542 warn(
1543 "Unknown worksheet reference '%s' in range "
1544 "'%s' passed to add_series()" % (sheetname, c_range)
1545 )
1546 chart.formula_data[r_id] = []
1547 seen_ranges[c_range] = []
1548 continue
1550 # Find the worksheet object based on the sheet name.
1551 worksheet = worksheets[sheetname]
1553 # Get the data from the worksheet table.
1554 data = worksheet._get_range_data(*cells)
1556 # Add the data to the chart.
1557 chart.formula_data[r_id] = data
1559 # Store range data locally to avoid lookup if seen again.
1560 seen_ranges[c_range] = data
1562 def _get_chart_range(self, c_range):
1563 # Convert a range formula such as Sheet1!$B$1:$B$5 into a sheet name
1564 # and cell range such as ( 'Sheet1', 0, 1, 4, 1 ).
1566 # Split the range formula into sheetname and cells at the last '!'.
1567 pos = c_range.rfind("!")
1568 if pos > 0:
1569 sheetname = c_range[:pos]
1570 cells = c_range[pos + 1 :]
1571 else:
1572 return None, None
1574 # Split the cell range into 2 cells or else use single cell for both.
1575 if cells.find(":") > 0:
1576 (cell_1, cell_2) = cells.split(":", 1)
1577 else:
1578 (cell_1, cell_2) = (cells, cells)
1580 # Remove leading/trailing quotes and convert escaped quotes to single.
1581 sheetname = sheetname.strip("'")
1582 sheetname = sheetname.replace("''", "'")
1584 try:
1585 # Get the row, col values from the Excel ranges. We do this in a
1586 # try block for ranges that can't be parsed such as defined names.
1587 (row_start, col_start) = xl_cell_to_rowcol(cell_1)
1588 (row_end, col_end) = xl_cell_to_rowcol(cell_2)
1589 except AttributeError:
1590 return None, None
1592 # We only handle 1D ranges.
1593 if row_start != row_end and col_start != col_end:
1594 return None, None
1596 return sheetname, [row_start, col_start, row_end, col_end]
1598 def _prepare_sst_string_data(self):
1599 # Convert the SST string data from a dict to a list.
1600 self.str_table._sort_string_data()
1602 def _get_packager(self):
1603 # Get and instance of the Packager class to create the xlsx package.
1604 # This allows the default packager to be over-ridden.
1605 return Packager()
1607 ###########################################################################
1608 #
1609 # XML methods.
1610 #
1611 ###########################################################################
1613 def _write_workbook(self):
1614 # Write <workbook> element.
1616 schema = "http://schemas.openxmlformats.org"
1617 xmlns = schema + "/spreadsheetml/2006/main"
1618 xmlns_r = schema + "/officeDocument/2006/relationships"
1620 attributes = [
1621 ("xmlns", xmlns),
1622 ("xmlns:r", xmlns_r),
1623 ]
1625 self._xml_start_tag("workbook", attributes)
1627 def _write_file_version(self):
1628 # Write the <fileVersion> element.
1630 app_name = "xl"
1631 last_edited = 4
1632 lowest_edited = 4
1633 rup_build = 4505
1635 attributes = [
1636 ("appName", app_name),
1637 ("lastEdited", last_edited),
1638 ("lowestEdited", lowest_edited),
1639 ("rupBuild", rup_build),
1640 ]
1642 if self.vba_project:
1643 attributes.append(("codeName", "{37E998C4-C9E5-D4B9-71C8-EB1FF731991C}"))
1645 self._xml_empty_tag("fileVersion", attributes)
1647 def _write_file_sharing(self):
1648 # Write the <fileSharing> element.
1649 if self.read_only == 0:
1650 return
1652 attributes = [("readOnlyRecommended", 1)]
1654 self._xml_empty_tag("fileSharing", attributes)
1656 def _write_workbook_pr(self):
1657 # Write <workbookPr> element.
1658 default_theme_version = 124226
1659 attributes = []
1661 if self.vba_codename:
1662 attributes.append(("codeName", self.vba_codename))
1663 if self.date_1904:
1664 attributes.append(("date1904", 1))
1666 attributes.append(("defaultThemeVersion", default_theme_version))
1668 self._xml_empty_tag("workbookPr", attributes)
1670 def _write_book_views(self):
1671 # Write <bookViews> element.
1672 self._xml_start_tag("bookViews")
1673 self._write_workbook_view()
1674 self._xml_end_tag("bookViews")
1676 def _write_workbook_view(self):
1677 # Write <workbookView> element.
1678 attributes = [
1679 ("xWindow", self.x_window),
1680 ("yWindow", self.y_window),
1681 ("windowWidth", self.window_width),
1682 ("windowHeight", self.window_height),
1683 ]
1685 # Store the tabRatio attribute when it isn't the default.
1686 if self.tab_ratio != 600:
1687 attributes.append(("tabRatio", self.tab_ratio))
1689 # Store the firstSheet attribute when it isn't the default.
1690 if self.worksheet_meta.firstsheet > 0:
1691 firstsheet = self.worksheet_meta.firstsheet + 1
1692 attributes.append(("firstSheet", firstsheet))
1694 # Store the activeTab attribute when it isn't the first sheet.
1695 if self.worksheet_meta.activesheet > 0:
1696 attributes.append(("activeTab", self.worksheet_meta.activesheet))
1698 self._xml_empty_tag("workbookView", attributes)
1700 def _write_sheets(self):
1701 # Write <sheets> element.
1702 self._xml_start_tag("sheets")
1704 id_num = 1
1705 for worksheet in self.worksheets():
1706 self._write_sheet(worksheet.name, id_num, worksheet.hidden)
1707 id_num += 1
1709 self._xml_end_tag("sheets")
1711 def _write_sheet(self, name, sheet_id, hidden):
1712 # Write <sheet> element.
1713 attributes = [
1714 ("name", name),
1715 ("sheetId", sheet_id),
1716 ]
1718 if hidden == 1:
1719 attributes.append(("state", "hidden"))
1720 elif hidden == 2:
1721 attributes.append(("state", "veryHidden"))
1723 attributes.append(("r:id", "rId" + str(sheet_id)))
1725 self._xml_empty_tag("sheet", attributes)
1727 def _write_calc_pr(self):
1728 # Write the <calcPr> element.
1729 attributes = [("calcId", self.calc_id)]
1731 if self.calc_mode == "manual":
1732 attributes.append(("calcMode", self.calc_mode))
1733 attributes.append(("calcOnSave", "0"))
1734 elif self.calc_mode == "autoNoTable":
1735 attributes.append(("calcMode", self.calc_mode))
1737 if self.calc_on_load:
1738 attributes.append(("fullCalcOnLoad", "1"))
1740 self._xml_empty_tag("calcPr", attributes)
1742 def _write_defined_names(self):
1743 # Write the <definedNames> element.
1744 if not self.defined_names:
1745 return
1747 self._xml_start_tag("definedNames")
1749 for defined_name in self.defined_names:
1750 self._write_defined_name(defined_name)
1752 self._xml_end_tag("definedNames")
1754 def _write_defined_name(self, defined_name):
1755 # Write the <definedName> element.
1756 name = defined_name[0]
1757 sheet_id = defined_name[1]
1758 sheet_range = defined_name[2]
1759 hidden = defined_name[3]
1761 attributes = [("name", name)]
1763 if sheet_id != -1:
1764 attributes.append(("localSheetId", sheet_id))
1765 if hidden:
1766 attributes.append(("hidden", 1))
1768 self._xml_data_element("definedName", sheet_range, attributes)
1771# A metadata class to share data between worksheets.
1772class WorksheetMeta(object):
1773 """
1774 A class to track worksheets data such as the active sheet and the
1775 first sheet.
1777 """
1779 def __init__(self):
1780 self.activesheet = 0
1781 self.firstsheet = 0
1784# A helper class to share embedded images between worksheets.
1785class EmbeddedImages(object):
1786 """
1787 A class to track duplicate embedded images between worksheets.
1789 """
1791 def __init__(self):
1792 self.images = []
1793 self.image_indexes = {}
1795 def get_image_index(self, image, digest):
1796 image_index = self.image_indexes.get(digest)
1798 if image_index is None:
1799 self.images.append(image)
1800 image_index = len(self.images)
1801 self.image_indexes[digest] = image_index
1803 return image_index
1805 def has_images(self):
1806 return len(self.images) > 0