element. + + style = 'text-align:' + align + + attributes = [('style', style)] + + self._xml_start_tag('div', attributes) + + if font: + # Write the font element. + self._write_font(font) + + self._xml_end_tag('div') + + def _write_font(self, font): + # Write the element. + caption = font["caption"] + face = 'Calibri' + size = 220 + color = '#000000' + + attributes = [ + ('face', face), + ('size', size), + ('color', color), + ] + + self._xml_data_element('font', caption, attributes) + + def _write_comment_client_data(self, row, col, visible, vertices): + # Write the element. + object_type = 'Note' + + attributes = [('ObjectType', object_type)] + + self._xml_start_tag('x:ClientData', attributes) + + # Write the x:MoveWithCells element. + self._write_move_with_cells() + + # Write the x:SizeWithCells element. + self._write_size_with_cells() + + # Write the x:Anchor element. + self._write_anchor(vertices) + + # Write the x:AutoFill element. + self._write_auto_fill() + + # Write the x:Row element. + self._write_row(row) + + # Write the x:Column element. + self._write_column(col) + + # Write the x:Visible element. + if visible: + self._write_visible() + + self._xml_end_tag('x:ClientData') + + def _write_button_client_data(self, button): + # Write the element. + macro = button["macro"] + vertices = button["vertices"] + + object_type = 'Button' + + attributes = [('ObjectType', object_type)] + + self._xml_start_tag('x:ClientData', attributes) + + # Write the x:Anchor element. + self._write_anchor(vertices) + + # Write the x:PrintObject element. + self._write_print_object() + + # Write the x:AutoFill element. + self._write_auto_fill() + + # Write the x:FmlaMacro element. + self._write_fmla_macro(macro) + + # Write the x:TextHAlign element. + self._write_text_halign() + + # Write the x:TextVAlign element. + self._write_text_valign() + + self._xml_end_tag('x:ClientData') + + def _write_move_with_cells(self): + # Write the element. + self._xml_empty_tag('x:MoveWithCells') + + def _write_size_with_cells(self): + # Write the element. + self._xml_empty_tag('x:SizeWithCells') + + def _write_visible(self): + # Write the element. + self._xml_empty_tag('x:Visible') + + def _write_anchor(self, vertices): + # Write the element. + (col_start, row_start, x1, y1, col_end, row_end, x2, y2) = vertices[:8] + + strings = [col_start, x1, row_start, y1, col_end, x2, row_end, y2] + strings = [str(i) for i in strings] + + data = ", ".join(strings) + + self._xml_data_element('x:Anchor', data) + + def _write_auto_fill(self): + # Write the element. + data = 'False' + + self._xml_data_element('x:AutoFill', data) + + def _write_row(self, data): + # Write the element. + self._xml_data_element('x:Row', data) + + def _write_column(self, data): + # Write the element. + self._xml_data_element('x:Column', data) + + def _write_print_object(self): + # Write the element. + self._xml_data_element('x:PrintObject', 'False') + + def _write_text_halign(self): + # Write the element. + self._xml_data_element('x:TextHAlign', 'Center') + + def _write_text_valign(self): + # Write the element. + self._xml_data_element('x:TextVAlign', 'Center') + + def _write_fmla_macro(self, data): + # Write the element. + self._xml_data_element('x:FmlaMacro', data) + + def _write_imagedata(self, image_index, o_title): + # Write the element. + attributes = [ + ('o:relid', 'rId' + str(image_index)), + ('o:title', o_title), + ] + + self._xml_empty_tag('v:imagedata', attributes) + + def _write_formulas(self): + # Write the element. + self._xml_start_tag('v:formulas') + + # Write the v:f elements. + self._write_formula('if lineDrawn pixelLineWidth 0') + self._write_formula('sum @0 1 0') + self._write_formula('sum 0 0 @1') + self._write_formula('prod @2 1 2') + self._write_formula('prod @3 21600 pixelWidth') + self._write_formula('prod @3 21600 pixelHeight') + self._write_formula('sum @0 0 1') + self._write_formula('prod @6 1 2') + self._write_formula('prod @7 21600 pixelWidth') + self._write_formula('sum @8 21600 0') + self._write_formula('prod @7 21600 pixelHeight') + self._write_formula('sum @10 21600 0') + + self._xml_end_tag('v:formulas') + + def _write_formula(self, eqn): + # Write the element. + attributes = [('eqn', eqn)] + + self._xml_empty_tag('v:f', attributes) diff -r 000000000000 -r 0abe6bac47a6 test.tool_dependencies.rnachipintegrator/rnachipintegrator/1.0.0/lib/python2.7/site-packages/xlsxwriter/vml.pyc Binary file test.tool_dependencies.rnachipintegrator/rnachipintegrator/1.0.0/lib/python2.7/site-packages/xlsxwriter/vml.pyc has changed diff -r 000000000000 -r 0abe6bac47a6 test.tool_dependencies.rnachipintegrator/rnachipintegrator/1.0.0/lib/python2.7/site-packages/xlsxwriter/workbook.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/test.tool_dependencies.rnachipintegrator/rnachipintegrator/1.0.0/lib/python2.7/site-packages/xlsxwriter/workbook.py Wed Feb 24 09:25:18 2016 -0500 @@ -0,0 +1,1545 @@ +############################################################################### +# +# Workbook - A class for writing the Excel XLSX Workbook file. +# +# Copyright 2013-2016, John McNamara, jmcnamara@cpan.org +# + +# Standard packages. +import sys +import re +import os +import operator +from warnings import warn +from datetime import datetime +from zipfile import ZipFile, ZIP_DEFLATED +from struct import unpack + +from .compatibility import str_types, force_unicode + +# Package imports. +from . import xmlwriter +from xlsxwriter.worksheet import Worksheet +from xlsxwriter.chartsheet import Chartsheet +from xlsxwriter.sharedstrings import SharedStringTable +from xlsxwriter.format import Format +from xlsxwriter.packager import Packager +from .utility import xl_cell_to_rowcol +from xlsxwriter.chart_area import ChartArea +from xlsxwriter.chart_bar import ChartBar +from xlsxwriter.chart_column import ChartColumn +from xlsxwriter.chart_doughnut import ChartDoughnut +from xlsxwriter.chart_line import ChartLine +from xlsxwriter.chart_pie import ChartPie +from xlsxwriter.chart_radar import ChartRadar +from xlsxwriter.chart_scatter import ChartScatter +from xlsxwriter.chart_stock import ChartStock + + +class Workbook(xmlwriter.XMLwriter): + """ + A class for writing the Excel XLSX Workbook file. + + + """ + + ########################################################################### + # + # Public API. + # + ########################################################################### + + def __init__(self, filename=None, options={}): + """ + Constructor. + + """ + + super(Workbook, self).__init__() + + self.filename = filename + + self.tmpdir = options.get('tmpdir', None) + self.date_1904 = options.get('date_1904', False) + self.strings_to_numbers = options.get('strings_to_numbers', False) + self.strings_to_formulas = options.get('strings_to_formulas', True) + self.strings_to_urls = options.get('strings_to_urls', True) + self.nan_inf_to_errors = options.get('nan_inf_to_errors', False) + self.default_date_format = options.get('default_date_format', None) + self.optimization = options.get('constant_memory', False) + self.in_memory = options.get('in_memory', False) + self.excel2003_style = options.get('excel2003_style', False) + self.default_format_properties = \ + options.get('default_format_properties', {}) + + self.worksheet_meta = WorksheetMeta() + self.selected = 0 + self.fileclosed = 0 + self.filehandle = None + self.internal_fh = 0 + self.sheet_name = 'Sheet' + self.chart_name = 'Chart' + self.sheetname_count = 0 + self.chartname_count = 0 + self.worksheets_objs = [] + self.charts = [] + self.drawings = [] + self.sheetnames = [] + self.formats = [] + self.xf_formats = [] + self.xf_format_indices = {} + self.dxf_formats = [] + self.dxf_format_indices = {} + self.palette = [] + self.font_count = 0 + self.num_format_count = 0 + self.defined_names = [] + self.named_ranges = [] + self.custom_colors = [] + self.doc_properties = {} + self.localtime = datetime.now() + self.num_vml_files = 0 + self.num_comment_files = 0 + self.x_window = 240 + self.y_window = 15 + self.window_width = 16095 + self.window_height = 9660 + self.tab_ratio = 500 + self.str_table = SharedStringTable() + self.vba_project = None + self.vba_is_stream = False + self.vba_codename = None + self.image_types = {} + self.images = [] + self.border_count = 0 + self.fill_count = 0 + self.drawing_count = 0 + self.calc_mode = "auto" + self.calc_on_load = True + self.allow_zip64 = False + self.calc_id = 124519 + + # We can't do 'constant_memory' mode while doing 'in_memory' mode. + if self.in_memory: + self.optimization = False + + # Add the default cell format. + if self.excel2003_style: + self.add_format({'xf_index': 0, 'font_family': 0}) + else: + self.add_format({'xf_index': 0}) + + # Add a default URL format. + self.default_url_format = self.add_format({'color': 'blue', + 'underline': 1}) + + # Add the default date format. + if self.default_date_format is not None: + self.default_date_format = \ + self.add_format({'num_format': self.default_date_format}) + + def __del__(self): + """Close file in destructor if it hasn't been closed explicitly.""" + try: + if not self.fileclosed: + self.close() + except: + raise Exception("Exception caught in workbook destructor. " + "Explicit close() may be required for workbook.") + + def __enter__(self): + """Return self object to use with "with" statement.""" + return self + + def __exit__(self, type, value, traceback): + """Close workbook when exiting "with" statement.""" + self.close() + + def add_worksheet(self, name=None): + """ + Add a new worksheet to the Excel workbook. + + Args: + name: The worksheet name. Defaults to 'Sheet1', etc. + + Returns: + Reference to a worksheet object. + + """ + return self._add_sheet(name, is_chartsheet=False) + + def add_chartsheet(self, name=None): + """ + Add a new chartsheet to the Excel workbook. + + Args: + name: The chartsheet name. Defaults to 'Sheet1', etc. + + Returns: + Reference to a chartsheet object. + + """ + return self._add_sheet(name, is_chartsheet=True) + + def add_format(self, properties={}): + """ + Add a new Format to the Excel Workbook. + + Args: + properties: The format properties. + + Returns: + Reference to a Format object. + + """ + format_properties = self.default_format_properties.copy() + + if self.excel2003_style: + format_properties = {'font_name': 'Arial', 'font_size': 10, + 'theme': 1 * -1} + + format_properties.update(properties) + + xf_format = Format(format_properties, + self.xf_format_indices, + self.dxf_format_indices) + + # Store the format reference. + self.formats.append(xf_format) + + return xf_format + + def add_chart(self, options): + """ + Create a chart object. + + Args: + options: The chart type and subtype options. + + Returns: + Reference to a Chart object. + + """ + + # Type must be specified so we can create the required chart instance. + chart_type = options.get('type') + if chart_type is None: + warn("Chart type must be defined in add_chart()") + return + + if chart_type == 'area': + chart = ChartArea(options) + elif chart_type == 'bar': + chart = ChartBar(options) + elif chart_type == 'column': + chart = ChartColumn(options) + elif chart_type == 'doughnut': + chart = ChartDoughnut(options) + elif chart_type == 'line': + chart = ChartLine(options) + elif chart_type == 'pie': + chart = ChartPie(options) + elif chart_type == 'radar': + chart = ChartRadar(options) + elif chart_type == 'scatter': + chart = ChartScatter(options) + elif chart_type == 'stock': + chart = ChartStock(options) + else: + warn("Unknown chart type '%s' in add_chart()" % chart_type) + return + + # Set the embedded chart name if present. + if 'name' in options: + chart.chart_name = options['name'] + + chart.embedded = True + chart.date_1904 = self.date_1904 + + self.charts.append(chart) + + return chart + + def add_vba_project(self, vba_project, is_stream=False): + """ + Add a vbaProject binary to the Excel workbook. + + Args: + vba_project: The vbaProject binary file name. + is_stream: vba_project is an in memory byte stream. + + Returns: + Nothing. + + """ + if not is_stream and not os.path.exists(vba_project): + warn("VBA project binary file '%s' not found." + % force_unicode(vba_project)) + return -1 + + self.vba_project = vba_project + self.vba_is_stream = is_stream + + def close(self): + """ + Call finalization code and close file. + + Args: + None. + + Returns: + Nothing. + + """ + if not self.fileclosed: + self.fileclosed = 1 + self._store_workbook() + + def set_properties(self, properties): + """ + Set the document properties such as Title, Author etc. + + Args: + properties: Dictionary of document properties. + + Returns: + Nothing. + + """ + self.doc_properties = properties + + def set_calc_mode(self, mode, calc_id=None): + """ + Set the Excel calculation mode for the workbook. + + Args: + mode: String containing one of: + * manual + * auto_except_tables + * auto + + Returns: + Nothing. + + """ + self.calc_mode = mode + + if mode == 'manual': + self.calc_on_load = False + elif mode == 'auto_except_tables': + self.calc_mode = 'autoNoTable' + + # Leave undocumented for now. Rarely required. + if calc_id: + self.calc_id = calc_id + + def define_name(self, name, formula): + # Create a defined name in Excel. We handle global/workbook level + # names and local/worksheet names. + """ + Create a defined name in the workbook. + + Args: + name: The defined name. + formula: The cell or range that the defined name refers to. + + Returns: + Nothing. + + """ + sheet_index = None + sheetname = '' + + # Remove the = sign from the formula if it exists. + if formula.startswith('='): + formula = formula.lstrip('=') + + # Local defined names are formatted like "Sheet1!name". + sheet_parts = re.compile(r'^(.*)!(.*)$') + match = sheet_parts.match(name) + + if match: + sheetname = match.group(1) + name = match.group(2) + sheet_index = self._get_sheet_index(sheetname) + + # Warn if the sheet index wasn't found. + if sheet_index is None: + warn("Unknown sheet name '%s' in defined_name()" + % force_unicode(sheetname)) + return -1 + else: + # Use -1 to indicate global names. + sheet_index = -1 + + # Warn if the defined name contains invalid chars as defined by Excel. + if (not re.match(r'^[\w\\][\w\\.]*$', name, re.UNICODE) + or re.match(r'^\d', name)): + warn("Invalid Excel characters in defined_name(): '%s'" + % force_unicode(name)) + return -1 + + # Warn if the defined name looks like a cell name. + if re.match(r'^[a-zA-Z][a-zA-Z]?[a-dA-D]?[0-9]+$', name): + warn("Name looks like a cell name in defined_name(): '%s'" + % force_unicode(name)) + return -1 + + # Warn if the name looks like a R1C1 cell reference. + if (re.match(r'^[rcRC]$', name) + or re.match(r'^[rcRC]\d+[rcRC]\d+$', name)): + warn("Invalid name '%s' like a RC cell ref in defined_name()" + % force_unicode(name)) + return -1 + + self.defined_names.append([name, sheet_index, formula, False]) + + def worksheets(self): + """ + Return a list of the worksheet objects in the workbook. + + Args: + None. + + Returns: + A list of worksheet objects. + + """ + return self.worksheets_objs + + def use_zip64(self): + """ + Allow ZIP64 extensions when writing xlsx file zip container. + + Args: + None. + + Returns: + Nothing. + + """ + self.allow_zip64 = True + + def set_vba_name(self, name=None): + """ + Set the VBA name for the workbook. By default the workbook is referred + to as ThisWorkbook in VBA. + + Args: + name: The VBA name for the workbook. + + Returns: + Nothing. + + """ + if name is not None: + self.vba_codename = name + else: + self.vba_codename = 'ThisWorkbook' + + ########################################################################### + # + # Private API. + # + ########################################################################### + + def _assemble_xml_file(self): + # Assemble and write the XML file. + + # Prepare format object for passing to Style.pm. + self._prepare_format_properties() + + # Write the XML declaration. + self._xml_declaration() + + # Write the workbook element. + self._write_workbook() + + # Write the fileVersion element. + self._write_file_version() + + # Write the workbookPr element. + self._write_workbook_pr() + + # Write the bookViews element. + self._write_book_views() + + # Write the sheets element. + self._write_sheets() + + # Write the workbook defined names. + self._write_defined_names() + + # Write the calcPr element. + self._write_calc_pr() + + # Close the workbook tag. + self._xml_end_tag('workbook') + + # Close the file. + self._xml_close() + + def _store_workbook(self): + # Assemble worksheets into a workbook. + packager = Packager() + + # Add a default worksheet if non have been added. + if not self.worksheets(): + self.add_worksheet() + + # Ensure that at least one worksheet has been selected. + if self.worksheet_meta.activesheet == 0: + self.worksheets_objs[0].selected = 1 + self.worksheets_objs[0].hidden = 0 + + # Set the active sheet. + for sheet in self.worksheets(): + if sheet.index == self.worksheet_meta.activesheet: + sheet.active = 1 + + # Convert the SST strings data structure. + self._prepare_sst_string_data() + + # Prepare the worksheet VML elements such as comments and buttons. + self._prepare_vml() + + # Set the defined names for the worksheets such as Print Titles. + self._prepare_defined_names() + + # Prepare the drawings, charts and images. + self._prepare_drawings() + + # Add cached data to charts. + self._add_chart_data() + + # Prepare the worksheet tables. + self._prepare_tables() + + # Package the workbook. + packager._add_workbook(self) + packager._set_tmpdir(self.tmpdir) + packager._set_in_memory(self.in_memory) + xml_files = packager._create_package() + + # Free up the Packager object. + packager = None + + xlsx_file = ZipFile(self.filename, "w", compression=ZIP_DEFLATED, + allowZip64=self.allow_zip64) + + # Add XML sub-files to the Zip file with their Excel filename. + for os_filename, xml_filename, is_binary in xml_files: + if self.in_memory: + # The files are in-memory StringIOs. + if is_binary: + xlsx_file.writestr(xml_filename, os_filename.getvalue()) + else: + xlsx_file.writestr(xml_filename, + os_filename.getvalue().encode('utf-8')) + else: + # The files are tempfiles. + xlsx_file.write(os_filename, xml_filename) + os.remove(os_filename) + + xlsx_file.close() + + def _add_sheet(self, name, is_chartsheet): + # Utility for shared code in add_worksheet() and add_chartsheet(). + + sheet_index = len(self.worksheets_objs) + name = self._check_sheetname(name, is_chartsheet) + + # Initialisation data to pass to the worksheet. + init_data = { + 'name': name, + 'index': sheet_index, + 'str_table': self.str_table, + 'worksheet_meta': self.worksheet_meta, + 'optimization': self.optimization, + 'tmpdir': self.tmpdir, + 'date_1904': self.date_1904, + 'strings_to_numbers': self.strings_to_numbers, + 'strings_to_formulas': self.strings_to_formulas, + 'strings_to_urls': self.strings_to_urls, + 'nan_inf_to_errors': self.nan_inf_to_errors, + 'default_date_format': self.default_date_format, + 'default_url_format': self.default_url_format, + 'excel2003_style': self.excel2003_style, + } + + if is_chartsheet: + worksheet = Chartsheet() + else: + worksheet = Worksheet() + + worksheet._initialize(init_data) + + self.worksheets_objs.append(worksheet) + self.sheetnames.append(name) + + return worksheet + + def _check_sheetname(self, sheetname, is_chartsheet=False): + # Check for valid worksheet names. We check the length, if it contains + # any invalid chars and if the sheetname is unique in the workbook. + invalid_char = re.compile(r'[\[\]:*?/\\]') + + # Increment the Sheet/Chart number used for default sheet names below. + if is_chartsheet: + self.chartname_count += 1 + else: + self.sheetname_count += 1 + + # Supply default Sheet/Chart sheetname if none has been defined. + if sheetname is None: + if is_chartsheet: + sheetname = self.chart_name + str(self.chartname_count) + else: + sheetname = self.sheet_name + str(self.sheetname_count) + + # Check that sheet sheetname is <= 31. Excel limit. + if len(sheetname) > 31: + raise Exception("Excel worksheet name '%s' must be <= 31 chars." % + sheetname) + + # Check that sheetname doesn't contain any invalid characters + if invalid_char.search(sheetname): + raise Exception( + "Invalid Excel character '[]:*?/\\' in sheetname '%s'" % + sheetname) + + # Check that the worksheet name doesn't already exist since this is a + # fatal Excel error. The check must be case insensitive like Excel. + for worksheet in self.worksheets(): + if sheetname.lower() == worksheet.name.lower(): + raise Exception( + "Sheetname '%s', with case ignored, is already in use." % + sheetname) + + return sheetname + + def _prepare_format_properties(self): + # Prepare all Format properties prior to passing them to styles.py. + + # Separate format objects into XF and DXF formats. + self._prepare_formats() + + # Set the font index for the format objects. + self._prepare_fonts() + + # Set the number format index for the format objects. + self._prepare_num_formats() + + # Set the border index for the format objects. + self._prepare_borders() + + # Set the fill index for the format objects. + self._prepare_fills() + + def _prepare_formats(self): + # Iterate through the XF Format objects and separate them into + # XF and DXF formats. The XF and DF formats then need to be sorted + # back into index order rather than creation order. + xf_formats = [] + dxf_formats = [] + + # Sort into XF and DXF formats. + for xf_format in self.formats: + if xf_format.xf_index is not None: + xf_formats.append(xf_format) + + if xf_format.dxf_index is not None: + dxf_formats.append(xf_format) + + # Pre-extend the format lists. + self.xf_formats = [None] * len(xf_formats) + self.dxf_formats = [None] * len(dxf_formats) + + # Rearrange formats into index order. + for xf_format in xf_formats: + index = xf_format.xf_index + self.xf_formats[index] = xf_format + + for dxf_format in dxf_formats: + index = dxf_format.dxf_index + self.dxf_formats[index] = dxf_format + + def _set_default_xf_indices(self): + # Set the default index for each format. Only used for testing. + + formats = list(self.formats) + + # Delete the default url format. + del formats[1] + + # Skip the default date format if set. + if self.default_date_format is not None: + del formats[1] + + # Set the remaining formats. + for xf_format in formats: + xf_format._get_xf_index() + + def _prepare_fonts(self): + # Iterate through the XF Format objects and give them an index to + # non-default font elements. + fonts = {} + index = 0 + + for xf_format in self.xf_formats: + key = xf_format._get_font_key() + if key in fonts: + # Font has already been used. + xf_format.font_index = fonts[key] + xf_format.has_font = 0 + else: + # This is a new font. + fonts[key] = index + xf_format.font_index = index + xf_format.has_font = 1 + index += 1 + + self.font_count = index + + # For DXF formats we only need to check if the properties have changed. + for xf_format in self.dxf_formats: + # The only font properties that can change for a DXF format are: + # color, bold, italic, underline and strikethrough. + if (xf_format.font_color or xf_format.bold or xf_format.italic + or xf_format.underline or xf_format.font_strikeout): + xf_format.has_dxf_font = 1 + + def _prepare_num_formats(self): + # User defined records in Excel start from index 0xA4. + num_formats = {} + index = 164 + num_format_count = 0 + + for xf_format in (self.xf_formats + self.dxf_formats): + num_format = xf_format.num_format + + # Check if num_format is an index to a built-in number format. + if not isinstance(num_format, str_types): + xf_format.num_format_index = int(num_format) + continue + + if num_format in num_formats: + # Number xf_format has already been used. + xf_format.num_format_index = num_formats[num_format] + else: + # Add a new number xf_format. + num_formats[num_format] = index + xf_format.num_format_index = index + index += 1 + + # Only increase font count for XF formats (not DXF formats). + if xf_format.xf_index: + num_format_count += 1 + + self.num_format_count = num_format_count + + def _prepare_borders(self): + # Iterate through the XF Format objects and give them an index to + # non-default border elements. + borders = {} + index = 0 + + for xf_format in self.xf_formats: + key = xf_format._get_border_key() + + if key in borders: + # Border has already been used. + xf_format.border_index = borders[key] + xf_format.has_border = 0 + else: + # This is a new border. + borders[key] = index + xf_format.border_index = index + xf_format.has_border = 1 + index += 1 + + self.border_count = index + + # For DXF formats we only need to check if the properties have changed. + has_border = re.compile(r'[^0:]') + + for xf_format in self.dxf_formats: + key = xf_format._get_border_key() + + if has_border.search(key): + xf_format.has_dxf_border = 1 + + def _prepare_fills(self): + # Iterate through the XF Format objects and give them an index to + # non-default fill elements. + # The user defined fill properties start from 2 since there are 2 + # default fills: patternType="none" and patternType="gray125". + fills = {} + index = 2 # Start from 2. See above. + + # Add the default fills. + fills['0:0:0'] = 0 + fills['17:0:0'] = 1 + + # Store the DXF colors separately since them may be reversed below. + for xf_format in self.dxf_formats: + if xf_format.pattern or xf_format.bg_color or xf_format.fg_color: + xf_format.has_dxf_fill = 1 + xf_format.dxf_bg_color = xf_format.bg_color + xf_format.dxf_fg_color = xf_format.fg_color + + for xf_format in self.xf_formats: + # The following logical statements jointly take care of special + # cases in relation to cell colors and patterns: + # 1. For a solid fill (_pattern == 1) Excel reverses the role of + # foreground and background colors, and + # 2. If the user specifies a foreground or background color + # without a pattern they probably wanted a solid fill, so we fill + # in the defaults. + if (xf_format.pattern == 1 and xf_format.bg_color != 0 + and xf_format.fg_color != 0): + tmp = xf_format.fg_color + xf_format.fg_color = xf_format.bg_color + xf_format.bg_color = tmp + + if (xf_format.pattern <= 1 and xf_format.bg_color != 0 + and xf_format.fg_color == 0): + xf_format.fg_color = xf_format.bg_color + xf_format.bg_color = 0 + xf_format.pattern = 1 + + if (xf_format.pattern <= 1 and xf_format.bg_color == 0 + and xf_format.fg_color != 0): + xf_format.bg_color = 0 + xf_format.pattern = 1 + + key = xf_format._get_fill_key() + + if key in fills: + # Fill has already been used. + xf_format.fill_index = fills[key] + xf_format.has_fill = 0 + else: + # This is a new fill. + fills[key] = index + xf_format.fill_index = index + xf_format.has_fill = 1 + index += 1 + + self.fill_count = index + + def _prepare_defined_names(self): + # Iterate through the worksheets and store any defined names in + # addition to any user defined names. Stores the defined names + # for the Workbook.xml and the named ranges for App.xml. + defined_names = self.defined_names + + for sheet in self.worksheets(): + # Check for Print Area settings. + if sheet.autofilter_area: + hidden = 1 + sheet_range = sheet.autofilter_area + # Store the defined names. + defined_names.append(['_xlnm._FilterDatabase', + sheet.index, sheet_range, hidden]) + + # Check for Print Area settings. + if sheet.print_area_range: + hidden = 0 + sheet_range = sheet.print_area_range + # Store the defined names. + defined_names.append(['_xlnm.Print_Area', + sheet.index, sheet_range, hidden]) + + # Check for repeat rows/cols referred to as Print Titles. + if sheet.repeat_col_range or sheet.repeat_row_range: + hidden = 0 + sheet_range = '' + if sheet.repeat_col_range and sheet.repeat_row_range: + sheet_range = (sheet.repeat_col_range + ',' + + sheet.repeat_row_range) + else: + sheet_range = (sheet.repeat_col_range + + sheet.repeat_row_range) + # Store the defined names. + defined_names.append(['_xlnm.Print_Titles', + sheet.index, sheet_range, hidden]) + + defined_names = self._sort_defined_names(defined_names) + self.defined_names = defined_names + self.named_ranges = self._extract_named_ranges(defined_names) + + def _sort_defined_names(self, names): + # Sort the list of list of internal and user defined names in + # the same order as used by Excel. + + # Add a normalize name string to each list for sorting. + for name_list in names: + (defined_name, _, sheet_name, _) = name_list + + # Normalize the defined name by removing any leading '_xmln.' + # from internal names and lowercasing the string. + defined_name = defined_name.replace('_xlnm.', '').lower() + + # Normalize the sheetname by removing the leading quote and + # lowercasing the string. + sheet_name = sheet_name.lstrip("'").lower() + + name_list.append(defined_name + "::" + sheet_name) + + # Sort based on the normalized key. + names.sort(key=operator.itemgetter(4)) + + # Remove the extra key used for sorting. + for name_list in names: + name_list.pop() + + return names + + def _prepare_drawings(self): + # Iterate through the worksheets and set up chart and image drawings. + chart_ref_id = 0 + image_ref_id = 0 + drawing_id = 0 + x_dpi = 96 + y_dpi = 96 + + for sheet in self.worksheets(): + chart_count = len(sheet.charts) + image_count = len(sheet.images) + shape_count = len(sheet.shapes) + + header_image_count = len(sheet.header_images) + footer_image_count = len(sheet.footer_images) + has_drawing = False + + if not (chart_count or image_count or shape_count + or header_image_count or footer_image_count): + continue + + # Don't increase the drawing_id header/footer images. + if chart_count or image_count or shape_count: + drawing_id += 1 + has_drawing = True + + # Prepare the worksheet charts. + for index in range(chart_count): + chart_ref_id += 1 + sheet._prepare_chart(index, chart_ref_id, drawing_id) + + # Prepare the worksheet images. + for index in range(image_count): + filename = sheet.images[index][2] + image_data = sheet.images[index][10] + (image_type, width, height, name, x_dpi, y_dpi) = \ + self._get_image_properties(filename, image_data) + image_ref_id += 1 + + sheet._prepare_image(index, image_ref_id, drawing_id, width, + height, name, image_type, x_dpi, y_dpi) + + # Prepare the worksheet shapes. + for index in range(shape_count): + sheet._prepare_shape(index, drawing_id) + + # Prepare the header images. + for index in range(header_image_count): + + filename = sheet.header_images[index][0] + image_data = sheet.header_images[index][1] + position = sheet.header_images[index][2] + + (image_type, width, height, name, x_dpi, y_dpi) = \ + self._get_image_properties(filename, image_data) + + image_ref_id += 1 + + sheet._prepare_header_image(image_ref_id, width, height, + name, image_type, position, + x_dpi, y_dpi) + + # Prepare the footer images. + for index in range(footer_image_count): + + filename = sheet.footer_images[index][0] + image_data = sheet.footer_images[index][1] + position = sheet.footer_images[index][2] + + (image_type, width, height, name, x_dpi, y_dpi) = \ + self._get_image_properties(filename, image_data) + + image_ref_id += 1 + + sheet._prepare_header_image(image_ref_id, width, height, + name, image_type, position, + x_dpi, y_dpi) + + if has_drawing: + drawing = sheet.drawing + self.drawings.append(drawing) + + # Remove charts that were created but not inserted into worksheets. + for chart in self.charts[:]: + if chart.id == -1: + self.charts.remove(chart) + + # Sort the workbook charts references into the order that the were + # written to the worksheets above. + self.charts = sorted(self.charts, key=lambda chart: chart.id) + + self.drawing_count = drawing_id + + def _get_image_properties(self, filename, image_data): + # Extract dimension information from the image file. + height = 0 + width = 0 + x_dpi = 96 + y_dpi = 96 + + if not image_data: + # Open the image file and read in the data. + fh = open(filename, "rb") + data = fh.read() + else: + # Read the image data from the user supplied byte stream. + data = image_data.getvalue() + + # Get the image filename without the path. + image_name = os.path.basename(filename) + + # Look for some common image file markers. + marker1 = (unpack('3s', data[1:4]))[0] + marker2 = (unpack('>H', data[:2]))[0] + marker3 = (unpack('2s', data[:2]))[0] + + if sys.version_info < (2, 6, 0): + # Python 2.5/Jython. + png_marker = 'PNG' + bmp_marker = 'BM' + else: + # Eval the binary literals for Python 2.5/Jython compatibility. + png_marker = eval("b'PNG'") + bmp_marker = eval("b'BM'") + + if marker1 == png_marker: + self.image_types['png'] = 1 + (image_type, width, height, x_dpi, y_dpi) = self._process_png(data) + + elif marker2 == 0xFFD8: + self.image_types['jpeg'] = 1 + (image_type, width, height, x_dpi, y_dpi) = self._process_jpg(data) + + elif marker3 == bmp_marker: + self.image_types['bmp'] = 1 + (image_type, width, height) = self._process_bmp(data) + + else: + raise Exception("%s: Unknown or unsupported image file format." + % filename) + + # Check that we found the required data. + if not height or not width: + raise Exception("%s: no size data found in image file." % filename) + + # Store image data to copy it into file container. + self.images.append([filename, image_type, image_data]) + + if not image_data: + fh.close() + + return image_type, width, height, image_name, x_dpi, y_dpi + + def _process_png(self, data): + # Extract width and height information from a PNG file. + offset = 8 + data_length = len(data) + end_marker = False + width = 0 + height = 0 + x_dpi = 96 + y_dpi = 96 + + # Look for numbers rather than strings for Python 2.6/3 compatibility. + marker_ihdr = 0x49484452 # IHDR + marker_phys = 0x70485973 # pHYs + marker_iend = 0X49454E44 # IEND + + # Search through the image data to read the height and width in the + # IHDR element. Also read the DPI in the pHYs element. + while not end_marker and offset < data_length: + + length = (unpack('>I', data[offset + 0:offset + 4]))[0] + marker = (unpack('>I', data[offset + 4:offset + 8]))[0] + + # Read the image dimensions. + if marker == marker_ihdr: + width = (unpack('>I', data[offset + 8:offset + 12]))[0] + height = (unpack('>I', data[offset + 12:offset + 16]))[0] + + # Read the image DPI. + if marker == marker_phys: + x_density = (unpack('>I', data[offset + 8:offset + 12]))[0] + y_density = (unpack('>I', data[offset + 12:offset + 16]))[0] + units = (unpack('b', data[offset + 16:offset + 17]))[0] + + if units == 1: + x_dpi = x_density * 0.0254 + y_dpi = y_density * 0.0254 + + if marker == marker_iend: + end_marker = True + continue + + offset = offset + length + 12 + + return 'png', width, height, x_dpi, y_dpi + + def _process_jpg(self, data): + # Extract width and height information from a JPEG file. + offset = 2 + data_length = len(data) + end_marker = False + width = 0 + height = 0 + x_dpi = 96 + y_dpi = 96 + + # Search through the image data to read the height and width in the + # 0xFFC0/C2 element. Also read the DPI in the 0xFFE0 element. + while not end_marker and offset < data_length: + + marker = (unpack('>H', data[offset + 0:offset + 2]))[0] + length = (unpack('>H', data[offset + 2:offset + 4]))[0] + + # Read the image dimensions. + if marker == 0xFFC0 or marker == 0xFFC2: + height = (unpack('>H', data[offset + 5:offset + 7]))[0] + width = (unpack('>H', data[offset + 7:offset + 9]))[0] + + # Read the image DPI. + if marker == 0xFFE0: + units = (unpack('b', data[offset + 11:offset + 12]))[0] + x_density = (unpack('>H', data[offset + 12:offset + 14]))[0] + y_density = (unpack('>H', data[offset + 14:offset + 16]))[0] + + if units == 1: + x_dpi = x_density + y_dpi = y_density + + if units == 2: + x_dpi = x_density * 2.54 + y_dpi = y_density * 2.54 + + # Workaround for incorrect dpi. + if x_dpi == 1: + x_dpi = 96 + if y_dpi == 1: + y_dpi = 96 + + if marker == 0xFFDA: + end_marker = True + continue + + offset = offset + length + 2 + + return 'jpeg', width, height, x_dpi, y_dpi + + def _process_bmp(self, data): + # Extract width and height information from a BMP file. + width = (unpack(' 0: + xf = self.add_format({'font_name': 'Tahoma', 'font_size': 8, + 'color_indexed': 81, 'font_only': True}) + xf._get_xf_index() + + # Set the workbook vba_codename if one of the sheets has a button and + # the workbook has a vbaProject binary. + if has_button and self.vba_project and self.vba_codename is None: + self.set_vba_name() + + def _prepare_tables(self): + # Set the table ids for the worksheet tables. + table_id = 0 + seen = {} + + for sheet in self.worksheets(): + table_count = len(sheet.tables) + + if not table_count: + continue + + sheet._prepare_tables(table_id + 1, seen) + table_id += table_count + + def _add_chart_data(self): + # Add "cached" data to charts to provide the numCache and strCache + # data for series and title/axis ranges. + worksheets = {} + seen_ranges = {} + charts = [] + + # Map worksheet names to worksheet objects. + for worksheet in self.worksheets(): + worksheets[worksheet.name] = worksheet + + # Build a list of the worksheet charts including any combined charts. + for chart in self.charts: + charts.append(chart) + if chart.combined: + charts.append(chart.combined) + + for chart in charts: + + for c_range in chart.formula_ids.keys(): + r_id = chart.formula_ids[c_range] + + # Skip if the series has user defined data. + if chart.formula_data[r_id] is not None: + if (c_range not in seen_ranges + or seen_ranges[c_range] is None): + data = chart.formula_data[r_id] + seen_ranges[c_range] = data + continue + + # Check to see if the data is already cached locally. + if c_range in seen_ranges: + chart.formula_data[r_id] = seen_ranges[c_range] + continue + + # Convert the range formula to a sheet name and cell range. + (sheetname, cells) = self._get_chart_range(c_range) + + # Skip if we couldn't parse the formula. + if sheetname is None: + continue + + # Handle non-contiguous ranges like: + # (Sheet1!$A$1:$A$2,Sheet1!$A$4:$A$5). + # We don't try to parse them. We just return an empty list. + if sheetname.startswith('('): + chart.formula_data[r_id] = [] + seen_ranges[c_range] = [] + continue + + # Warn if the name is unknown since it indicates a user error + # in a chart series formula. + if sheetname not in worksheets: + warn("Unknown worksheet reference '%s' in range " + "'%s' passed to add_series()" + % (force_unicode(sheetname), force_unicode(c_range))) + chart.formula_data[r_id] = [] + seen_ranges[c_range] = [] + continue + + # Find the worksheet object based on the sheet name. + worksheet = worksheets[sheetname] + + # Get the data from the worksheet table. + data = worksheet._get_range_data(*cells) + + # TODO. Handle SST string ids if required. + + # Add the data to the chart. + chart.formula_data[r_id] = data + + # Store range data locally to avoid lookup if seen again. + seen_ranges[c_range] = data + + def _get_chart_range(self, c_range): + # Convert a range formula such as Sheet1!$B$1:$B$5 into a sheet name + # and cell range such as ( 'Sheet1', 0, 1, 4, 1 ). + + # Split the range formula into sheetname and cells at the last '!'. + pos = c_range.rfind('!') + if pos > 0: + sheetname = c_range[:pos] + cells = c_range[pos + 1:] + else: + return None, None + + # Split the cell range into 2 cells or else use single cell for both. + if cells.find(':') > 0: + (cell_1, cell_2) = cells.split(':', 1) + else: + (cell_1, cell_2) = (cells, cells) + + # Remove leading/trailing quotes and convert escaped quotes to single. + sheetname = sheetname.strip("'") + sheetname = sheetname.replace("''", "'") + + try: + # Get the row, col values from the Excel ranges. We do this in a + # try block for ranges that can't be parsed such as defined names. + (row_start, col_start) = xl_cell_to_rowcol(cell_1) + (row_end, col_end) = xl_cell_to_rowcol(cell_2) + except: + return None, None + + # We only handle 1D ranges. + if row_start != row_end and col_start != col_end: + return None, None + + return sheetname, [row_start, col_start, row_end, col_end] + + def _prepare_sst_string_data(self): + # Convert the SST string data from a dict to a list. + self.str_table._sort_string_data() + + ########################################################################### + # + # XML methods. + # + ########################################################################### + + def _write_workbook(self): + # Write element. + + schema = 'http://schemas.openxmlformats.org' + xmlns = schema + '/spreadsheetml/2006/main' + xmlns_r = schema + '/officeDocument/2006/relationships' + + attributes = [ + ('xmlns', xmlns), + ('xmlns:r', xmlns_r), + ] + + self._xml_start_tag('workbook', attributes) + + def _write_file_version(self): + # Write the element. + + app_name = 'xl' + last_edited = 4 + lowest_edited = 4 + rup_build = 4505 + + attributes = [ + ('appName', app_name), + ('lastEdited', last_edited), + ('lowestEdited', lowest_edited), + ('rupBuild', rup_build), + ] + + if self.vba_project: + attributes.append( + ('codeName', '{37E998C4-C9E5-D4B9-71C8-EB1FF731991C}')) + + self._xml_empty_tag('fileVersion', attributes) + + def _write_workbook_pr(self): + # Write element. + default_theme_version = 124226 + attributes = [] + + if self.vba_codename: + attributes.append(('codeName', self.vba_codename)) + if self.date_1904: + attributes.append(('date1904', 1)) + + attributes.append(('defaultThemeVersion', default_theme_version)) + + self._xml_empty_tag('workbookPr', attributes) + + def _write_book_views(self): + # Write element. + self._xml_start_tag('bookViews') + self._write_workbook_view() + self._xml_end_tag('bookViews') + + def _write_workbook_view(self): + # Write element. + attributes = [ + ('xWindow', self.x_window), + ('yWindow', self.y_window), + ('windowWidth', self.window_width), + ('windowHeight', self.window_height), + ] + + # Store the tabRatio attribute when it isn't the default. + if self.tab_ratio != 500: + attributes.append(('tabRatio', self.tab_ratio)) + + # Store the firstSheet attribute when it isn't the default. + if self.worksheet_meta.firstsheet > 0: + firstsheet = self.worksheet_meta.firstsheet + 1 + attributes.append(('firstSheet', firstsheet)) + + # Store the activeTab attribute when it isn't the first sheet. + if self.worksheet_meta.activesheet > 0: + attributes.append(('activeTab', self.worksheet_meta.activesheet)) + + self._xml_empty_tag('workbookView', attributes) + + def _write_sheets(self): + # Write element. + self._xml_start_tag('sheets') + + id_num = 1 + for worksheet in self.worksheets(): + self._write_sheet(worksheet.name, id_num, worksheet.hidden) + id_num += 1 + + self._xml_end_tag('sheets') + + def _write_sheet(self, name, sheet_id, hidden): + # Write element. + attributes = [ + ('name', name), + ('sheetId', sheet_id), + ] + + if hidden: + attributes.append(('state', 'hidden')) + + attributes.append(('r:id', 'rId' + str(sheet_id))) + + self._xml_empty_tag('sheet', attributes) + + def _write_calc_pr(self): + # Write the element. + attributes = [('calcId', self.calc_id)] + + if self.calc_mode == 'manual': + attributes.append(('calcMode', self.calc_mode)) + attributes.append(('calcOnSave', "0")) + elif self.calc_mode == 'autoNoTable': + attributes.append(('calcMode', self.calc_mode)) + + if self.calc_on_load: + attributes.append(('fullCalcOnLoad', '1')) + + self._xml_empty_tag('calcPr', attributes) + + def _write_defined_names(self): + # Write the element. + if not self.defined_names: + return + + self._xml_start_tag('definedNames') + + for defined_name in self.defined_names: + self._write_defined_name(defined_name) + + self._xml_end_tag('definedNames') + + def _write_defined_name(self, defined_name): + # Write the element. + name = defined_name[0] + sheet_id = defined_name[1] + sheet_range = defined_name[2] + hidden = defined_name[3] + + attributes = [('name', name)] + + if sheet_id != -1: + attributes.append(('localSheetId', sheet_id)) + if hidden: + attributes.append(('hidden', 1)) + + self._xml_data_element('definedName', sheet_range, attributes) + + +# A metadata class to share data between worksheets. +class WorksheetMeta(object): + """ + A class to track worksheets data such as the active sheet and the + first sheet. + + """ + + def __init__(self): + self.activesheet = 0 + self.firstsheet = 0 diff -r 000000000000 -r 0abe6bac47a6 test.tool_dependencies.rnachipintegrator/rnachipintegrator/1.0.0/lib/python2.7/site-packages/xlsxwriter/workbook.pyc Binary file test.tool_dependencies.rnachipintegrator/rnachipintegrator/1.0.0/lib/python2.7/site-packages/xlsxwriter/workbook.pyc has changed diff -r 000000000000 -r 0abe6bac47a6 test.tool_dependencies.rnachipintegrator/rnachipintegrator/1.0.0/lib/python2.7/site-packages/xlsxwriter/worksheet.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/test.tool_dependencies.rnachipintegrator/rnachipintegrator/1.0.0/lib/python2.7/site-packages/xlsxwriter/worksheet.py Wed Feb 24 09:25:18 2016 -0500 @@ -0,0 +1,6473 @@ +############################################################################### +# +# Worksheet - A class for writing the Excel XLSX Worksheet file. +# +# Copyright 2013-2016, John McNamara, jmcnamara@cpan.org +# + +# Standard packages. +import re +import tempfile +import codecs +import os + +from warnings import warn + +# Standard packages in Python 2/3 compatibility mode. +from .compatibility import StringIO +from .compatibility import defaultdict +from .compatibility import namedtuple +from .compatibility import force_unicode +from .compatibility import num_types, str_types + +# Package imports. +from . import xmlwriter +from .format import Format +from .drawing import Drawing +from .shape import Shape +from .xmlwriter import XMLwriter +from .utility import xl_rowcol_to_cell +from .utility import xl_rowcol_to_cell_fast +from .utility import xl_cell_to_rowcol +from .utility import xl_col_to_name +from .utility import xl_range +from .utility import xl_color +from .utility import get_sparkline_style +from .utility import supported_datetime +from .utility import datetime_to_excel_datetime +from .utility import quote_sheetname + + +############################################################################### +# +# Decorator functions. +# +############################################################################### +def convert_cell_args(method): + """ + Decorator function to convert A1 notation in cell method calls + to the default row/col notation. + + """ + def cell_wrapper(self, *args, **kwargs): + + try: + # First arg is an int, default to row/col notation. + if len(args): + int(args[0]) + except ValueError: + # First arg isn't an int, convert to A1 notation. + new_args = list(xl_cell_to_rowcol(args[0])) + new_args.extend(args[1:]) + args = new_args + + return method(self, *args, **kwargs) + + return cell_wrapper + + +def convert_range_args(method): + """ + Decorator function to convert A1 notation in range method calls + to the default row/col notation. + + """ + def cell_wrapper(self, *args, **kwargs): + + try: + # First arg is an int, default to row/col notation. + if len(args): + int(args[0]) + except ValueError: + # First arg isn't an int, convert to A1 notation. + if ':' in args[0]: + cell_1, cell_2 = args[0].split(':') + row_1, col_1 = xl_cell_to_rowcol(cell_1) + row_2, col_2 = xl_cell_to_rowcol(cell_2) + else: + row_1, col_1 = xl_cell_to_rowcol(args[0]) + row_2, col_2 = row_1, col_1 + + new_args = [row_1, col_1, row_2, col_2] + new_args.extend(args[1:]) + args = new_args + + return method(self, *args, **kwargs) + + return cell_wrapper + + +def convert_column_args(method): + """ + Decorator function to convert A1 notation in columns method calls + to the default row/col notation. + + """ + def column_wrapper(self, *args, **kwargs): + + try: + # First arg is an int, default to row/col notation. + if len(args): + int(args[0]) + except ValueError: + # First arg isn't an int, convert to A1 notation. + cell_1, cell_2 = [col + '1' for col in args[0].split(':')] + _, col_1 = xl_cell_to_rowcol(cell_1) + _, col_2 = xl_cell_to_rowcol(cell_2) + new_args = [col_1, col_2] + new_args.extend(args[1:]) + args = new_args + + return method(self, *args, **kwargs) + + return column_wrapper + + +############################################################################### +# +# Named tuples used for cell types. +# +############################################################################### +cell_string_tuple = namedtuple('String', 'string, format') +cell_number_tuple = namedtuple('Number', 'number, format') +cell_blank_tuple = namedtuple('Blank', 'format') +cell_boolean_tuple = namedtuple('Boolean', 'boolean, format') +cell_formula_tuple = namedtuple('Formula', 'formula, format, value') +cell_arformula_tuple = namedtuple('ArrayFormula', + 'formula, format, value, range') + + +############################################################################### +# +# Worksheet Class definition. +# +############################################################################### +class Worksheet(xmlwriter.XMLwriter): + """ + A class for writing the Excel XLSX Worksheet file. + + """ + + ########################################################################### + # + # Public API. + # + ########################################################################### + + def __init__(self): + """ + Constructor. + + """ + + super(Worksheet, self).__init__() + + self.name = None + self.index = None + self.str_table = None + self.palette = None + self.optimization = 0 + self.tmpdir = None + self.is_chartsheet = False + + self.ext_sheets = [] + self.fileclosed = 0 + self.excel_version = 2007 + self.excel2003_style = False + + self.xls_rowmax = 1048576 + self.xls_colmax = 16384 + self.xls_strmax = 32767 + self.dim_rowmin = None + self.dim_rowmax = None + self.dim_colmin = None + self.dim_colmax = None + + self.colinfo = {} + self.selections = [] + self.hidden = 0 + self.active = 0 + self.tab_color = 0 + + self.panes = [] + self.active_pane = 3 + self.selected = 0 + + self.page_setup_changed = False + self.paper_size = 0 + self.orientation = 1 + + self.print_options_changed = False + self.hcenter = 0 + self.vcenter = 0 + self.print_gridlines = 0 + self.screen_gridlines = 1 + self.print_headers = 0 + + self.header_footer_changed = False + self.header = '' + self.footer = '' + self.header_footer_aligns = True + self.header_footer_scales = True + self.header_images = [] + self.footer_images = [] + self.header_images_list = [] + + self.margin_left = 0.7 + self.margin_right = 0.7 + self.margin_top = 0.75 + self.margin_bottom = 0.75 + self.margin_header = 0.3 + self.margin_footer = 0.3 + + self.repeat_row_range = '' + self.repeat_col_range = '' + self.print_area_range = '' + + self.page_order = 0 + self.black_white = 0 + self.draft_quality = 0 + self.print_comments = 0 + self.page_start = 0 + + self.fit_page = 0 + self.fit_width = 0 + self.fit_height = 0 + + self.hbreaks = [] + self.vbreaks = [] + + self.protect_options = {} + self.set_cols = {} + self.set_rows = defaultdict(dict) + + self.zoom = 100 + self.zoom_scale_normal = 1 + self.print_scale = 100 + self.is_right_to_left = 0 + self.show_zeros = 1 + self.leading_zeros = 0 + + self.outline_row_level = 0 + self.outline_col_level = 0 + self.outline_style = 0 + self.outline_below = 1 + self.outline_right = 1 + self.outline_on = 1 + self.outline_changed = False + + self.original_row_height = 15 + self.default_row_height = 15 + self.default_row_pixels = 20 + self.default_col_pixels = 64 + self.default_row_zeroed = 0 + + self.names = {} + self.write_match = [] + self.table = defaultdict(dict) + self.merge = [] + self.row_spans = {} + + self.has_vml = False + self.has_header_vml = False + self.has_comments = False + self.comments = defaultdict(dict) + self.comments_list = [] + self.comments_author = '' + self.comments_visible = 0 + self.vml_shape_id = 1024 + self.buttons_list = [] + self.vml_header_id = 0 + + self.autofilter_area = '' + self.autofilter_ref = None + self.filter_range = [] + self.filter_on = 0 + self.filter_range = [] + self.filter_cols = {} + self.filter_type = {} + + self.col_sizes = {} + self.row_sizes = {} + self.col_formats = {} + self.col_size_changed = False + self.row_size_changed = False + + self.last_shape_id = 1 + self.rel_count = 0 + self.hlink_count = 0 + self.hlink_refs = [] + self.external_hyper_links = [] + self.external_drawing_links = [] + self.external_comment_links = [] + self.external_vml_links = [] + self.external_table_links = [] + self.drawing_links = [] + self.vml_drawing_links = [] + self.charts = [] + self.images = [] + self.tables = [] + self.sparklines = [] + self.shapes = [] + self.shape_hash = {} + self.drawing = 0 + + self.rstring = '' + self.previous_row = 0 + + self.validations = [] + self.cond_formats = {} + self.dxf_priority = 1 + self.is_chartsheet = 0 + self.page_view = 0 + + self.vba_codename = None + + self.date_1904 = False + self.hyperlinks = defaultdict(dict) + + self.strings_to_numbers = False + self.strings_to_urls = True + self.nan_inf_to_errors = False + self.strings_to_formulas = True + + self.default_date_format = None + self.default_url_format = None + + self.row_data_filename = None + self.row_data_fh = None + self.worksheet_meta = None + self.vml_data_id = None + self.vml_shape_id = None + + self.row_data_filename = None + self.row_data_fh = None + self.row_data_fh_closed = False + + self.vertical_dpi = 0 + self.horizontal_dpi = 0 + + @convert_cell_args + def write(self, row, col, *args): + """ + Write data to a worksheet cell by calling the appropriate write_*() + method based on the type of data being passed. + + Args: + row: The cell row (zero indexed). + col: The cell column (zero indexed). + *args: Args to pass to sub functions. + + Returns: + 0: Success. + -1: Row or column is out of worksheet bounds. + other: Return value of called method. + + """ + # Check the number of args passed. + if not len(args): + raise TypeError("write() takes at least 4 arguments (3 given)") + + # The first arg should be the token for all write calls. + token = args[0] + + # Write None as a blank cell. + if token is None: + return self.write_blank(row, col, *args) + + # Write boolean types. + if isinstance(token, bool): + return self.write_boolean(row, col, *args) + + # Write datetime objects. + if supported_datetime(token): + return self.write_datetime(row, col, *args) + + # Write number types. + if isinstance(token, num_types): + return self.write_number(row, col, *args) + + # Write string types. + if isinstance(token, str_types): + # Map the data to the appropriate write_*() method. + if token == '': + return self.write_blank(row, col, *args) + + elif self.strings_to_formulas and token.startswith('='): + return self.write_formula(row, col, *args) + + elif self.strings_to_urls and re.match('(ftp|http)s?://', token): + return self.write_url(row, col, *args) + + elif self.strings_to_urls and re.match('mailto:', token): + return self.write_url(row, col, *args) + + elif self.strings_to_urls and re.match('(in|ex)ternal:', token): + return self.write_url(row, col, *args) + + elif self.strings_to_numbers: + try: + f = float(token) + if (self.nan_inf_to_errors or + (not self._isnan(f) and not self._isinf(f))): + return self.write_number(row, col, f, *args[1:]) + except ValueError: + # Not a number, write as a string. + pass + + return self.write_string(row, col, *args) + + else: + # We have a plain string. + return self.write_string(row, col, *args) + + # We haven't matched a supported type. Try float. + try: + f = float(token) + return self.write_number(row, col, f, *args[1:]) + except ValueError: + pass + except TypeError: + raise TypeError("Unsupported type %s in write()" % type(token)) + + # Finally try string. + try: + str(token) + return self.write_string(row, col, *args) + except ValueError: + raise TypeError("Unsupported type %s in write()" % type(token)) + + @convert_cell_args + def write_string(self, row, col, string, cell_format=None): + """ + Write a string to a worksheet cell. + + Args: + row: The cell row (zero indexed). + col: The cell column (zero indexed). + string: Cell data. Str. + format: An optional cell Format object. + + Returns: + 0: Success. + -1: Row or column is out of worksheet bounds. + -2: String truncated to 32k characters. + + """ + str_error = 0 + + # Check that row and col are valid and store max and min values. + if self._check_dimensions(row, col): + return -1 + + # Check that the string is < 32767 chars. + if len(string) > self.xls_strmax: + string = string[:self.xls_strmax] + str_error = -2 + + # Write a shared string or an in-line string in optimisation mode. + if self.optimization == 0: + string_index = self.str_table._get_shared_string_index(string) + else: + string_index = string + + # Write previous row if in in-line string optimization mode. + if self.optimization and row > self.previous_row: + self._write_single_row(row) + + # Store the cell data in the worksheet data table. + self.table[row][col] = cell_string_tuple(string_index, cell_format) + + return str_error + + @convert_cell_args + def write_number(self, row, col, number, cell_format=None): + """ + Write a number to a worksheet cell. + + Args: + row: The cell row (zero indexed). + col: The cell column (zero indexed). + number: Cell data. Int or float. + cell_format: An optional cell Format object. + + Returns: + 0: Success. + -1: Row or column is out of worksheet bounds. + + """ + if self._isnan(number) or self._isinf(number): + if self.nan_inf_to_errors: + if self._isnan(number): + return self.write_formula(row, col, '#NUM!', cell_format, + '#NUM!') + elif self._isinf(number): + return self.write_formula(row, col, '1/0', cell_format, + '#DIV/0!') + else: + raise TypeError( + "NAN/INF not supported in write_number() " + "without 'nan_inf_to_errors' Workbook() option") + + # Check that row and col are valid and store max and min values. + if self._check_dimensions(row, col): + return -1 + + # Write previous row if in in-line string optimization mode. + if self.optimization and row > self.previous_row: + self._write_single_row(row) + + # Store the cell data in the worksheet data table. + self.table[row][col] = cell_number_tuple(number, cell_format) + + return 0 + + @convert_cell_args + def write_blank(self, row, col, blank, cell_format=None): + """ + Write a blank cell with formatting to a worksheet cell. The blank + token is ignored and the format only is written to the cell. + + Args: + row: The cell row (zero indexed). + col: The cell column (zero indexed). + blank: Any value. It is ignored. + cell_format: An optional cell Format object. + + Returns: + 0: Success. + -1: Row or column is out of worksheet bounds. + + """ + # Don't write a blank cell unless it has a format. + if cell_format is None: + return 0 + + # Check that row and col are valid and store max and min values. + if self._check_dimensions(row, col): + return -1 + + # Write previous row if in in-line string optimization mode. + if self.optimization and row > self.previous_row: + self._write_single_row(row) + + # Store the cell data in the worksheet data table. + self.table[row][col] = cell_blank_tuple(cell_format) + + return 0 + + @convert_cell_args + def write_formula(self, row, col, formula, cell_format=None, value=0): + """ + Write a formula to a worksheet cell. + + Args: + row: The cell row (zero indexed). + col: The cell column (zero indexed). + formula: Cell formula. + cell_format: An optional cell Format object. + value: An optional value for the formula. Default is 0. + + Returns: + 0: Success. + -1: Row or column is out of worksheet bounds. + + """ + # Check that row and col are valid and store max and min values. + if self._check_dimensions(row, col): + return -1 + + # Hand off array formulas. + if formula.startswith('{') and formula.endswith('}'): + return self.write_array_formula(row, col, row, col, formula, + cell_format, value) + + # Remove the formula '=' sign if it exists. + if formula.startswith('='): + formula = formula.lstrip('=') + + # Write previous row if in in-line string optimization mode. + if self.optimization and row > self.previous_row: + self._write_single_row(row) + + # Store the cell data in the worksheet data table. + self.table[row][col] = cell_formula_tuple(formula, cell_format, value) + + return 0 + + @convert_range_args + def write_array_formula(self, first_row, first_col, last_row, last_col, + formula, cell_format=None, value=0): + """ + Write a formula to a worksheet cell. + + Args: + first_row: The first row of the cell range. (zero indexed). + first_col: The first column of the cell range. + last_row: The last row of the cell range. (zero indexed). + last_col: The last column of the cell range. + formula: Cell formula. + cell_format: An optional cell Format object. + value: An optional value for the formula. Default is 0. + + Returns: + 0: Success. + -1: Row or column is out of worksheet bounds. + + """ + + # Swap last row/col with first row/col as necessary. + if first_row > last_row: + first_row, last_row = last_row, first_row + if first_col > last_col: + first_col, last_col = last_col, first_col + + # Check that row and col are valid and store max and min values + if self._check_dimensions(last_row, last_col): + return -1 + + # Define array range + if first_row == last_row and first_col == last_col: + cell_range = xl_rowcol_to_cell(first_row, first_col) + else: + cell_range = (xl_rowcol_to_cell(first_row, first_col) + ':' + + xl_rowcol_to_cell(last_row, last_col)) + + # Remove array formula braces and the leading =. + if formula[0] == '{': + formula = formula[1:] + if formula[0] == '=': + formula = formula[1:] + if formula[-1] == '}': + formula = formula[:-1] + + # Write previous row if in in-line string optimization mode. + if self.optimization and first_row > self.previous_row: + self._write_single_row(first_row) + + # Store the cell data in the worksheet data table. + self.table[first_row][first_col] = cell_arformula_tuple(formula, + cell_format, + value, + cell_range) + + # Pad out the rest of the area with formatted zeroes. + if not self.optimization: + for row in range(first_row, last_row + 1): + for col in range(first_col, last_col + 1): + if row != first_row or col != first_col: + self.write_number(row, col, 0, cell_format) + + return 0 + + @convert_cell_args + def write_datetime(self, row, col, date, cell_format=None): + """ + Write a date or time to a worksheet cell. + + Args: + row: The cell row (zero indexed). + col: The cell column (zero indexed). + date: Date and/or time as a datetime object. + cell_format: A cell Format object. + + Returns: + 0: Success. + -1: Row or column is out of worksheet bounds. + + """ + # Check that row and col are valid and store max and min values. + if self._check_dimensions(row, col): + return -1 + + # Write previous row if in in-line string optimization mode. + if self.optimization and row > self.previous_row: + self._write_single_row(row) + + # Convert datetime to an Excel date. + number = self._convert_date_time(date) + + # Add the default date format. + if cell_format is None: + cell_format = self.default_date_format + + # Store the cell data in the worksheet data table. + self.table[row][col] = cell_number_tuple(number, cell_format) + + return 0 + + @convert_cell_args + def write_boolean(self, row, col, boolean, cell_format=None): + """ + Write a boolean value to a worksheet cell. + + Args: + row: The cell row (zero indexed). + col: The cell column (zero indexed). + boolean: Cell data. bool type. + cell_format: An optional cell Format object. + + Returns: + 0: Success. + -1: Row or column is out of worksheet bounds. + + """ + # Check that row and col are valid and store max and min values. + if self._check_dimensions(row, col): + return -1 + + # Write previous row if in in-line string optimization mode. + if self.optimization and row > self.previous_row: + self._write_single_row(row) + + if boolean: + value = 1 + else: + value = 0 + + # Store the cell data in the worksheet data table. + self.table[row][col] = cell_boolean_tuple(value, cell_format) + + return 0 + + # Write a hyperlink. This is comprised of two elements: the displayed + # string and the non-displayed link. The displayed string is the same as + # the link unless an alternative string is specified. The display string + # is written using the write_string() method. Therefore the max characters + # string limit applies. + # + # The hyperlink can be to a http, ftp, mail, internal sheet, or external + # directory urls. + @convert_cell_args + def write_url(self, row, col, url, cell_format=None, + string=None, tip=None): + """ + Write a hyperlink to a worksheet cell. + + Args: + row: The cell row (zero indexed). + col: The cell column (zero indexed). + url: Hyperlink url. + format: An optional cell Format object. + string: An optional display string for the hyperlink. + tip: An optional tooltip. + Returns: + 0: Success. + -1: Row or column is out of worksheet bounds. + -2: String longer than 32767 characters. + -3: URL longer than Excel limit of 255 characters + -4: Exceeds Excel limit of 65,530 urls per worksheet + """ + # Set the displayed string to the URL unless defined by the user. + if string is None: + string = url + + # Default to external link type such as 'http://' or 'external:'. + link_type = 1 + + # Remove the URI scheme from internal links. + if re.match("internal:", url): + url = url.replace('internal:', '') + string = string.replace('internal:', '') + link_type = 2 + + # Remove the URI scheme from external links and change the directory + # separator from Unix to Dos. + if re.match("external:", url): + url = url.replace('external:', '') + url = url.replace('/', '\\') + string = string.replace('external:', '') + string = string.replace('/', '\\') + + # Strip the mailto header. + string = string.replace('mailto:', '') + + # Check that row and col are valid and store max and min values + if self._check_dimensions(row, col): + return -1 + + # Check that the string is < 32767 chars + str_error = 0 + if len(string) > self.xls_strmax: + warn("Ignoring URL since it exceeds Excel's string limit of " + "32767 characters") + return -2 + + # Copy string for use in hyperlink elements. + url_str = string + + # External links to URLs and to other Excel workbooks have slightly + # different characteristics that we have to account for. + if link_type == 1: + # Escape URL unless it looks already escaped. + if not re.search('%[0-9a-fA-F]{2}', url): + # Can't use url.quote() here because it doesn't match Excel. + url = url.replace('%', '%25') + url = url.replace('"', '%22') + url = url.replace(' ', '%20') + url = url.replace('<', '%3c') + url = url.replace('>', '%3e') + url = url.replace('[', '%5b') + url = url.replace(']', '%5d') + url = url.replace('^', '%5e') + url = url.replace('`', '%60') + url = url.replace('{', '%7b') + url = url.replace('}', '%7d') + + # Split url into the link and optional anchor/location. + if '#' in url: + url, url_str = url.split('#', 1) + else: + url_str = None + + # Add the file:/// URI to the url for Windows style "C:/" link and + # Network shares. + if re.match('\w:', url) or re.match(r'\\', url): + url = 'file:///' + url + + # Convert a .\dir\file.xlsx link to dir\file.xlsx. + url = re.sub(r'^\.\\', '', url) + + # Excel limits the escaped URL and location/anchor to 255 characters. + tmp_url_str = url_str or '' + if len(url) > 255 or len(tmp_url_str) > 255: + warn("Ignoring URL '%s' with link or location/anchor > 255 " + "characters since it exceeds Excel's limit for URLS" % + force_unicode(url)) + return -3 + + # Check the limit of URLS per worksheet. + self.hlink_count += 1 + + if self.hlink_count > 65530: + warn("Ignoring URL '%s' since it exceeds Excel's limit of " + "65,530 URLS per worksheet." % force_unicode(url)) + return -5 + + # Write previous row if in in-line string optimization mode. + if self.optimization == 1 and row > self.previous_row: + self._write_single_row(row) + + # Add the default URL format. + if cell_format is None: + cell_format = self.default_url_format + + # Write the hyperlink string. + self.write_string(row, col, string, cell_format) + + # Store the hyperlink data in a separate structure. + self.hyperlinks[row][col] = { + 'link_type': link_type, + 'url': url, + 'str': url_str, + 'tip': tip} + + return str_error + + @convert_cell_args + def write_rich_string(self, row, col, *args): + """ + Write a "rich" string with multiple formats to a worksheet cell. + + Args: + row: The cell row (zero indexed). + col: The cell column (zero indexed). + string_parts: String and format pairs. + cell_format: Optional Format object. + + Returns: + 0: Success. + -1: Row or column is out of worksheet bounds. + -2: String truncated to 32k characters. + -3: 2 consecutive formats used. + + """ + tokens = list(args) + cell_format = None + str_length = 0 + string_index = 0 + + # Check that row and col are valid and store max and min values + if self._check_dimensions(row, col): + return -1 + + # If the last arg is a format we use it as the cell format. + if isinstance(tokens[-1], Format): + cell_format = tokens.pop() + + # Create a temp XMLWriter object and use it to write the rich string + # XML to a string. + fh = StringIO() + self.rstring = XMLwriter() + self.rstring._set_filehandle(fh) + + # Create a temp format with the default font for unformatted fragments. + default = Format() + + # Convert list of format, string tokens to pairs of (format, string) + # except for the first string fragment which doesn't require a default + # formatting run. Use the default for strings without a leading format. + fragments = [] + previous = 'format' + pos = 0 + + for token in tokens: + if not isinstance(token, Format): + # Token is a string. + if previous != 'format': + # If previous token wasn't a format add one before string. + fragments.append(default) + fragments.append(token) + else: + # If previous token was a format just add the string. + fragments.append(token) + + # Keep track of actual string str_length. + str_length += len(token) + previous = 'string' + else: + # Can't allow 2 formats in a row. + if previous == 'format' and pos > 0: + return -3 + + # Token is a format object. Add it to the fragment list. + fragments.append(token) + previous = 'format' + + pos += 1 + + # If the first token is a string start the element. + if not isinstance(fragments[0], Format): + self.rstring._xml_start_tag('r') + + # Write the XML elements for the $format $string fragments. + for token in fragments: + if isinstance(token, Format): + # Write the font run. + self.rstring._xml_start_tag('r') + self._write_font(token) + else: + # Write the string fragment part, with whitespace handling. + attributes = [] + + if re.search('^\s', token) or re.search('\s$', token): + attributes.append(('xml:space', 'preserve')) + + self.rstring._xml_data_element('t', token, attributes) + self.rstring._xml_end_tag('r') + + # Read the in-memory string. + string = self.rstring.fh.getvalue() + + # Check that the string is < 32767 chars. + if str_length > self.xls_strmax: + return -2 + + # Write a shared string or an in-line string in optimisation mode. + if self.optimization == 0: + string_index = self.str_table._get_shared_string_index(string) + else: + string_index = string + + # Write previous row if in in-line string optimization mode. + if self.optimization and row > self.previous_row: + self._write_single_row(row) + + # Store the cell data in the worksheet data table. + self.table[row][col] = cell_string_tuple(string_index, cell_format) + + return 0 + + @convert_cell_args + def write_row(self, row, col, data, cell_format=None): + """ + Write a row of data starting from (row, col). + + Args: + row: The cell row (zero indexed). + col: The cell column (zero indexed). + data: A list of tokens to be written with write(). + format: An optional cell Format object. + Returns: + 0: Success. + other: Return value of write() method. + + """ + for token in data: + error = self.write(row, col, token, cell_format) + if error: + return error + col += 1 + + return 0 + + @convert_cell_args + def write_column(self, row, col, data, cell_format=None): + """ + Write a column of data starting from (row, col). + + Args: + row: The cell row (zero indexed). + col: The cell column (zero indexed). + data: A list of tokens to be written with write(). + format: An optional cell Format object. + Returns: + 0: Success. + other: Return value of write() method. + + """ + for token in data: + error = self.write(row, col, token, cell_format) + if error: + return error + row += 1 + + return 0 + + @convert_cell_args + def insert_image(self, row, col, filename, options={}): + """ + Insert an image with its top-left corner in a worksheet cell. + Args: + row: The cell row (zero indexed). + col: The cell column (zero indexed). + filename: Path and filename for image in PNG, JPG or BMP format. + options: Position, scale, url and data stream of the image. + Returns: + 0: Success. + """ + x_offset = options.get('x_offset', 0) + y_offset = options.get('y_offset', 0) + x_scale = options.get('x_scale', 1) + y_scale = options.get('y_scale', 1) + url = options.get('url', None) + tip = options.get('tip', None) + anchor = options.get('positioning', None) + image_data = options.get('image_data', None) + + if not image_data and not os.path.exists(filename): + warn("Image file '%s' not found." % force_unicode(filename)) + return -1 + + self.images.append([row, col, filename, x_offset, y_offset, + x_scale, y_scale, url, tip, anchor, image_data]) + + @convert_cell_args + def insert_textbox(self, row, col, text, options=None): + """ + Insert an textbox with its top-left corner in a worksheet cell. + Args: + row: The cell row (zero indexed). + col: The cell column (zero indexed). + text: The text for the textbox. + options: Textbox options. + Returns: + 0: Success. + """ + if options is None: + options = {} + + x_offset = options.get('x_offset', 0) + y_offset = options.get('y_offset', 0) + x_scale = options.get('x_scale', 1) + y_scale = options.get('y_scale', 1) + + self.shapes.append([row, col, x_offset, y_offset, + x_scale, y_scale, text, options]) + + @convert_cell_args + def insert_chart(self, row, col, chart, options={}): + """ + Insert an chart with its top-left corner in a worksheet cell. + Args: + row: The cell row (zero indexed). + col: The cell column (zero indexed). + chart: Chart object. + options: Position and scale of the chart. + + Returns: + 0: Success. + """ + + # Ensure a chart isn't inserted more than once. + if (chart.already_inserted or chart.combined + and chart.combined.already_inserted): + + warn('Chart cannot be inserted in a worksheet more than once.') + return + else: + chart.already_inserted = True + + if chart.combined: + chart.combined.already_inserted = True + + x_offset = options.get('x_offset', 0) + y_offset = options.get('y_offset', 0) + x_scale = options.get('x_scale', 1) + y_scale = options.get('y_scale', 1) + + # Allow Chart to override the scale and offset. + if chart.x_scale != 1: + x_scale = chart.x_scale + + if chart.y_scale != 1: + y_scale = chart.y_scale + + if chart.x_offset: + x_offset = chart.x_offset + + if chart.y_offset: + y_offset = chart.y_offset + + self.charts.append([row, col, chart, + x_offset, y_offset, + x_scale, y_scale]) + + @convert_cell_args + def write_comment(self, row, col, comment, options={}): + """ + Write a comment to a worksheet cell. + + Args: + row: The cell row (zero indexed). + col: The cell column (zero indexed). + comment: Cell comment. Str. + options: Comment formatting options. + + Returns: + 0: Success. + -1: Row or column is out of worksheet bounds. + -2: String longer than 32k characters. + + """ + # Check that row and col are valid and store max and min values + if self._check_dimensions(row, col): + return -1 + + # Check that the comment string is < 32767 chars. + if len(comment) > self.xls_strmax: + return -2 + + self.has_vml = 1 + self.has_comments = 1 + + # Process the properties of the cell comment. + self.comments[row][col] = \ + self._comment_params(row, col, comment, options) + + def show_comments(self): + """ + Make any comments in the worksheet visible. + + Args: + None. + + Returns: + Nothing. + + """ + self.comments_visible = 1 + + def set_comments_author(self, author): + """ + Set the default author of the cell comments. + + Args: + author: Comment author name. String. + + Returns: + Nothing. + + """ + self.comments_author = author + + def get_name(self): + """ + Retrieve the worksheet name. + + Args: + None. + + Returns: + Nothing. + + """ + # There is no set_name() method. Name must be set in add_worksheet(). + return self.name + + def activate(self): + """ + Set this worksheet as the active worksheet, i.e. the worksheet that is + displayed when the workbook is opened. Also set it as selected. + + Note: An active worksheet cannot be hidden. + + Args: + None. + + Returns: + Nothing. + + """ + self.hidden = 0 + self.selected = 1 + self.worksheet_meta.activesheet = self.index + + def select(self): + """ + Set current worksheet as a selected worksheet, i.e. the worksheet + has its tab highlighted. + + Note: A selected worksheet cannot be hidden. + + Args: + None. + + Returns: + Nothing. + + """ + self.selected = 1 + self.hidden = 0 + + def hide(self): + """ + Hide the current worksheet. + + Args: + None. + + Returns: + Nothing. + + """ + self.hidden = 1 + + # A hidden worksheet shouldn't be active or selected. + self.selected = 0 + + # TODO. Should add a check to see if the sheet is the global + # activesheet or firstsheet and reset them. + + def set_first_sheet(self): + """ + Set current worksheet as the first visible sheet. This is necessary + when there are a large number of worksheets and the activated + worksheet is not visible on the screen. + + Note: A selected worksheet cannot be hidden. + + Args: + None. + + Returns: + Nothing. + + """ + self.hidden = 0 # Active worksheet can't be hidden. + self.worksheet_meta.firstsheet = self.index + + @convert_column_args + def set_column(self, firstcol, lastcol, width=None, cell_format=None, + options={}): + """ + Set the width, and other properties of a single column or a + range of columns. + + Args: + firstcol: First column (zero-indexed). + lastcol: Last column (zero-indexed). Can be same as firstcol. + width: Column width. (optional). + cell_format: Column cell_format. (optional). + options: Dict of options such as hidden and level. + + Returns: + 0: Success. + -1: Column number is out of worksheet bounds. + + """ + # Ensure 2nd col is larger than first. + if firstcol > lastcol: + (firstcol, lastcol) = (lastcol, firstcol) + + # Don't modify the row dimensions when checking the columns. + ignore_row = True + + # Set optional column values. + hidden = options.get('hidden', False) + collapsed = options.get('collapsed', False) + level = options.get('level', 0) + # Store the column dimension only in some conditions. + if cell_format or (width and hidden): + ignore_col = False + else: + ignore_col = True + + # Check that each column is valid and store the max and min values. + if self._check_dimensions(0, lastcol, ignore_row, ignore_col): + return -1 + if self._check_dimensions(0, firstcol, ignore_row, ignore_col): + return -1 + + # Set the limits for the outline levels (0 <= x <= 7). + if level < 0: + level = 0 + if level > 7: + level = 7 + + if level > self.outline_col_level: + self.outline_col_level = level + + # Store the column data. Padded for sorting. + self.colinfo["%05d" % firstcol] = [firstcol, lastcol, width, + cell_format, hidden, level, + collapsed] + + # Store the column change to allow optimizations. + self.col_size_changed = True + + # Store the col sizes for use when calculating image vertices taking + # hidden columns into account. Also store the column formats. + + # Set width to zero if col is hidden + if hidden: + width = 0 + + for col in range(firstcol, lastcol + 1): + self.col_sizes[col] = width + if cell_format: + self.col_formats[col] = cell_format + + return 0 + + def set_row(self, row, height=None, cell_format=None, options={}): + """ + Set the width, and other properties of a row. + + Args: + row: Row number (zero-indexed). + height: Row width. (optional). + cell_format: Row cell_format. (optional). + options: Dict of options such as hidden, level and collapsed. + + Returns: + 0: Success. + -1: Row number is out of worksheet bounds. + + """ + # Use minimum col in _check_dimensions(). + if self.dim_colmin is not None: + min_col = self.dim_colmin + else: + min_col = 0 + + # Check that row is valid. + if self._check_dimensions(row, min_col): + return -1 + + if height is None: + height = self.default_row_height + + # Set optional row values. + hidden = options.get('hidden', False) + collapsed = options.get('collapsed', False) + level = options.get('level', 0) + + # If the height is 0 the row is hidden and the height is the default. + if height == 0: + hidden = 1 + height = self.default_row_height + + # Set the limits for the outline levels (0 <= x <= 7). + if level < 0: + level = 0 + if level > 7: + level = 7 + + if level > self.outline_row_level: + self.outline_row_level = level + + # Store the row properties. + self.set_rows[row] = [height, cell_format, hidden, level, collapsed] + + # Store the row change to allow optimizations. + self.row_size_changed = True + + if hidden: + height = 0 + + # Store the row sizes for use when calculating image vertices. + self.row_sizes[row] = height + + def set_default_row(self, height=None, hide_unused_rows=False): + """ + Set the default row properties. + + Args: + height: Default height. Optional, defaults to 15. + hide_unused_rows: Hide unused rows. Optional, defaults to False. + + Returns: + Nothing. + + """ + if height is None: + height = self.default_row_height + + if height != self.original_row_height: + # Store the row change to allow optimizations. + self.row_size_changed = True + self.default_row_height = height + + if hide_unused_rows: + self.default_row_zeroed = 1 + + @convert_range_args + def merge_range(self, first_row, first_col, last_row, last_col, + data, cell_format=None): + """ + Merge a range of cells. + + Args: + first_row: The first row of the cell range. (zero indexed). + first_col: The first column of the cell range. + last_row: The last row of the cell range. (zero indexed). + last_col: The last column of the cell range. + data: Cell data. + cell_format: Cell Format object. + + Returns: + 0: Success. + -1: Row or column is out of worksheet bounds. + other: Return value of write(). + + """ + # Merge a range of cells. The first cell should contain the data and + # the others should be blank. All cells should have the same format. + + # Excel doesn't allow a single cell to be merged + if first_row == last_row and first_col == last_col: + warn("Can't merge single cell") + return + + # Swap last row/col with first row/col as necessary + if first_row > last_row: + (first_row, last_row) = (last_row, first_row) + if first_col > last_col: + (first_col, last_col) = (last_col, first_col) + + # Check that column number is valid and store the max value + if self._check_dimensions(last_row, last_col) == -1: + return + + # Store the merge range. + self.merge.append([first_row, first_col, last_row, last_col]) + + # Write the first cell + self.write(first_row, first_col, data, cell_format) + + # Pad out the rest of the area with formatted blank cells. + for row in range(first_row, last_row + 1): + for col in range(first_col, last_col + 1): + if row == first_row and col == first_col: + continue + self.write_blank(row, col, '', cell_format) + + @convert_range_args + def autofilter(self, first_row, first_col, last_row, last_col): + """ + Set the autofilter area in the worksheet. + + Args: + first_row: The first row of the cell range. (zero indexed). + first_col: The first column of the cell range. + last_row: The last row of the cell range. (zero indexed). + last_col: The last column of the cell range. + + Returns: + Nothing. + + """ + # Reverse max and min values if necessary. + if last_row < first_row: + (first_row, last_row) = (last_row, first_row) + if last_col < first_col: + (first_col, last_col) = (last_col, first_col) + + # Build up the print area range "Sheet1!$A$1:$C$13". + area = self._convert_name_area(first_row, first_col, + last_row, last_col) + ref = xl_range(first_row, first_col, last_row, last_col) + + self.autofilter_area = area + self.autofilter_ref = ref + self.filter_range = [first_col, last_col] + + def filter_column(self, col, criteria): + """ + Set the column filter criteria. + + Args: + col: Filter column (zero-indexed). + criteria: Filter criteria. + + Returns: + Nothing. + + """ + if not self.autofilter_area: + warn("Must call autofilter() before filter_column()") + return + + # Check for a column reference in A1 notation and substitute. + try: + int(col) + except ValueError: + # Convert col ref to a cell ref and then to a col number. + col_letter = col + (_, col) = xl_cell_to_rowcol(col + '1') + + if col >= self.xls_colmax: + warn("Invalid column '%s'" % col_letter) + return + + (col_first, col_last) = self.filter_range + + # Reject column if it is outside filter range. + if col < col_first or col > col_last: + warn("Column '%d' outside autofilter() column range (%d, %d)" + % (col, col_first, col_last)) + return + + tokens = self._extract_filter_tokens(criteria) + + if not (len(tokens) == 3 or len(tokens) == 7): + warn("Incorrect number of tokens in criteria '%s'" % criteria) + + tokens = self._parse_filter_expression(criteria, tokens) + + # Excel handles single or double custom filters as default filters. + # We need to check for them and handle them accordingly. + if len(tokens) == 2 and tokens[0] == 2: + # Single equality. + self.filter_column_list(col, [tokens[1]]) + elif (len(tokens) == 5 and tokens[0] == 2 and tokens[2] == 1 + and tokens[3] == 2): + # Double equality with "or" operator. + self.filter_column_list(col, [tokens[1], tokens[4]]) + else: + # Non default custom filter. + self.filter_cols[col] = tokens + self.filter_type[col] = 0 + + self.filter_on = 1 + + def filter_column_list(self, col, filters): + """ + Set the column filter criteria in Excel 2007 list style. + + Args: + col: Filter column (zero-indexed). + filters: List of filter criteria to match. + + Returns: + Nothing. + + """ + if not self.autofilter_area: + warn("Must call autofilter() before filter_column()") + return + + # Check for a column reference in A1 notation and substitute. + try: + int(col) + except ValueError: + # Convert col ref to a cell ref and then to a col number. + col_letter = col + (_, col) = xl_cell_to_rowcol(col + '1') + + if col >= self.xls_colmax: + warn("Invalid column '%s'" % col_letter) + return + + (col_first, col_last) = self.filter_range + + # Reject column if it is outside filter range. + if col < col_first or col > col_last: + warn("Column '%d' outside autofilter() column range " + "(%d,%d)" % (col, col_first, col_last)) + return + + self.filter_cols[col] = filters + self.filter_type[col] = 1 + self.filter_on = 1 + + @convert_range_args + def data_validation(self, first_row, first_col, last_row, last_col, + options): + """ + Add a data validation to a worksheet. + + Args: + first_row: The first row of the cell range. (zero indexed). + first_col: The first column of the cell range. + last_row: The last row of the cell range. (zero indexed). + last_col: The last column of the cell range. + options: Data validation options. + + Returns: + 0: Success. + -1: Row or column is out of worksheet bounds. + -2: Incorrect parameter or option. + """ + # Check that row and col are valid without storing the values. + if self._check_dimensions(first_row, first_col, True, True): + return -1 + if self._check_dimensions(last_row, last_col, True, True): + return -1 + + # List of valid input parameters. + valid_parameters = { + 'validate': True, + 'criteria': True, + 'value': True, + 'source': True, + 'minimum': True, + 'maximum': True, + 'ignore_blank': True, + 'dropdown': True, + 'show_input': True, + 'input_title': True, + 'input_message': True, + 'show_error': True, + 'error_title': True, + 'error_message': True, + 'error_type': True, + 'other_cells': True, + } + + # Check for valid input parameters. + for param_key in options.keys(): + if param_key not in valid_parameters: + warn("Unknown parameter '%s' in data_validation()" % param_key) + return -2 + + # Map alternative parameter names 'source' or 'minimum' to 'value'. + if 'source' in options: + options['value'] = options['source'] + if 'minimum' in options: + options['value'] = options['minimum'] + + # 'validate' is a required parameter. + if 'validate' not in options: + warn("Parameter 'validate' is required in data_validation()") + return -2 + + # List of valid validation types. + valid_types = { + 'any': 'none', + 'any value': 'none', + 'whole number': 'whole', + 'whole': 'whole', + 'integer': 'whole', + 'decimal': 'decimal', + 'list': 'list', + 'date': 'date', + 'time': 'time', + 'text length': 'textLength', + 'length': 'textLength', + 'custom': 'custom', + } + + # Check for valid validation types. + if not options['validate'] in valid_types: + warn("Unknown validation type '%s' for parameter " + "'validate' in data_validation()" % options['validate']) + return -2 + else: + options['validate'] = valid_types[options['validate']] + + # No action is required for validation type 'any' if there are no + # input messages to display. + if (options['validate'] == 'none' + and options.get('input_title') is None + and options.get('input_message') is None): + return -2 + + # The any, list and custom validations don't have a criteria so we use + # a default of 'between'. + if (options['validate'] == 'none' + or options['validate'] == 'list' + or options['validate'] == 'custom'): + options['criteria'] = 'between' + options['maximum'] = None + + # 'criteria' is a required parameter. + if 'criteria' not in options: + warn("Parameter 'criteria' is required in data_validation()") + return -2 + + # List of valid criteria types. + criteria_types = { + 'between': 'between', + 'not between': 'notBetween', + 'equal to': 'equal', + '=': 'equal', + '==': 'equal', + 'not equal to': 'notEqual', + '!=': 'notEqual', + '<>': 'notEqual', + 'greater than': 'greaterThan', + '>': 'greaterThan', + 'less than': 'lessThan', + '<': 'lessThan', + 'greater than or equal to': 'greaterThanOrEqual', + '>=': 'greaterThanOrEqual', + 'less than or equal to': 'lessThanOrEqual', + '<=': 'lessThanOrEqual', + } + + # Check for valid criteria types. + if not options['criteria'] in criteria_types: + warn("Unknown criteria type '%s' for parameter " + "'criteria' in data_validation()" % options['criteria']) + return -2 + else: + options['criteria'] = criteria_types[options['criteria']] + + # 'Between' and 'Not between' criteria require 2 values. + if (options['criteria'] == 'between' or + options['criteria'] == 'notBetween'): + if 'maximum' not in options: + warn("Parameter 'maximum' is required in data_validation() " + "when using 'between' or 'not between' criteria") + return -2 + else: + options['maximum'] = None + + # List of valid error dialog types. + error_types = { + 'stop': 0, + 'warning': 1, + 'information': 2, + } + + # Check for valid error dialog types. + if 'error_type' not in options: + options['error_type'] = 0 + elif not options['error_type'] in error_types: + warn("Unknown criteria type '%s' for parameter 'error_type' " + "in data_validation()" % options['error_type']) + return -2 + else: + options['error_type'] = error_types[options['error_type']] + + # Convert date/times value if required. + if options['validate'] == 'date' or options['validate'] == 'time': + + if options['value']: + if not supported_datetime(options['value']): + warn("Data validation 'value/minimum' must be a " + "datetime object.") + return -2 + else: + date_time = self._convert_date_time(options['value']) + # Format date number to the same precision as Excel. + options['value'] = "%.15g" % date_time + + if options['maximum']: + if not supported_datetime(options['maximum']): + warn("Conditional format 'maximum' must be a " + "datetime object.") + return -2 + else: + date_time = self._convert_date_time(options['maximum']) + options['maximum'] = "%.15g" % date_time + + # Check that the input title doesn't exceed the maximum length. + if options.get('input_title') and len(options['input_title']) > 32: + warn("Length of input title '%s' exceeds Excel's limit of 32" + % force_unicode(options['input_title'])) + return -2 + + # Check that the error title doesn't exceed the maximum length. + if options.get('error_title') and len(options['error_title']) > 32: + warn("Length of error title '%s' exceeds Excel's limit of 32" + % force_unicode(options['error_title'])) + return -2 + + # Check that the input message doesn't exceed the maximum length. + if (options.get('input_message') + and len(options['input_message']) > 255): + warn("Length of input message '%s' exceeds Excel's limit of 255" + % force_unicode(options['input_message'])) + return -2 + + # Check that the error message doesn't exceed the maximum length. + if (options.get('error_message') + and len(options['error_message']) > 255): + warn("Length of error message '%s' exceeds Excel's limit of 255" + % force_unicode(options['error_message'])) + return -2 + + # Check that the input list doesn't exceed the maximum length. + if options['validate'] == 'list' and type(options['value']) is list: + formula = self._csv_join(*options['value']) + if len(formula) > 255: + warn("Length of list items '%s' exceeds Excel's limit of " + "255, use a formula range instead" + % force_unicode(formula)) + return -2 + + # Set some defaults if they haven't been defined by the user. + if 'ignore_blank' not in options: + options['ignore_blank'] = 1 + if 'dropdown' not in options: + options['dropdown'] = 1 + if 'show_input' not in options: + options['show_input'] = 1 + if 'show_error' not in options: + options['show_error'] = 1 + + # These are the cells to which the validation is applied. + options['cells'] = [[first_row, first_col, last_row, last_col]] + + # A (for now) undocumented parameter to pass additional cell ranges. + if 'other_cells' in options: + options['cells'].extend(options['other_cells']) + + # Store the validation information until we close the worksheet. + self.validations.append(options) + + @convert_range_args + def conditional_format(self, first_row, first_col, last_row, last_col, + options=None): + """ + Add a conditional format to a worksheet. + + Args: + first_row: The first row of the cell range. (zero indexed). + first_col: The first column of the cell range. + last_row: The last row of the cell range. (zero indexed). + last_col: The last column of the cell range. + options: Conditional format options. + + Returns: + 0: Success. + -1: Row or column is out of worksheet bounds. + -2: Incorrect parameter or option. + """ + # Check that row and col are valid without storing the values. + if self._check_dimensions(first_row, first_col, True, True): + return -1 + if self._check_dimensions(last_row, last_col, True, True): + return -1 + + if options is None: + options = {} + + # Copy the user defined options so they aren't modified. + options = options.copy() + + # List of valid input parameters. + valid_parameter = { + 'type': True, + 'format': True, + 'criteria': True, + 'value': True, + 'minimum': True, + 'maximum': True, + 'min_type': True, + 'mid_type': True, + 'max_type': True, + 'min_value': True, + 'mid_value': True, + 'max_value': True, + 'min_color': True, + 'mid_color': True, + 'max_color': True, + 'multi_range': True, + 'bar_color': 1} + + # Check for valid input parameters. + for param_key in options.keys(): + if param_key not in valid_parameter: + warn("Unknown parameter '%s' in conditional_formatting()" % + param_key) + return -2 + + # 'type' is a required parameter. + if 'type' not in options: + warn("Parameter 'type' is required in conditional_formatting()") + return -2 + + # List of valid validation types. + valid_type = { + 'cell': 'cellIs', + 'date': 'date', + 'time': 'time', + 'average': 'aboveAverage', + 'duplicate': 'duplicateValues', + 'unique': 'uniqueValues', + 'top': 'top10', + 'bottom': 'top10', + 'text': 'text', + 'time_period': 'timePeriod', + 'blanks': 'containsBlanks', + 'no_blanks': 'notContainsBlanks', + 'errors': 'containsErrors', + 'no_errors': 'notContainsErrors', + '2_color_scale': '2_color_scale', + '3_color_scale': '3_color_scale', + 'data_bar': 'dataBar', + 'formula': 'expression'} + + # Check for valid validation types. + if options['type'] not in valid_type: + warn("Unknown validation type '%s' for parameter 'type' " + "in conditional_formatting()" % options['type']) + return -2 + else: + if options['type'] == 'bottom': + options['direction'] = 'bottom' + options['type'] = valid_type[options['type']] + + # List of valid criteria types. + criteria_type = { + 'between': 'between', + 'not between': 'notBetween', + 'equal to': 'equal', + '=': 'equal', + '==': 'equal', + 'not equal to': 'notEqual', + '!=': 'notEqual', + '<>': 'notEqual', + 'greater than': 'greaterThan', + '>': 'greaterThan', + 'less than': 'lessThan', + '<': 'lessThan', + 'greater than or equal to': 'greaterThanOrEqual', + '>=': 'greaterThanOrEqual', + 'less than or equal to': 'lessThanOrEqual', + '<=': 'lessThanOrEqual', + 'containing': 'containsText', + 'not containing': 'notContains', + 'begins with': 'beginsWith', + 'ends with': 'endsWith', + 'yesterday': 'yesterday', + 'today': 'today', + 'last 7 days': 'last7Days', + 'last week': 'lastWeek', + 'this week': 'thisWeek', + 'continue week': 'continueWeek', + 'last month': 'lastMonth', + 'this month': 'thisMonth', + 'continue month': 'continueMonth'} + + # Check for valid criteria types. + if 'criteria' in options and options['criteria'] in criteria_type: + options['criteria'] = criteria_type[options['criteria']] + + # Convert date/times value if required. + if options['type'] == 'date' or options['type'] == 'time': + options['type'] = 'cellIs' + + if 'value' in options: + if not supported_datetime(options['value']): + warn("Conditional format 'value' must be a " + "datetime object.") + return -2 + else: + date_time = self._convert_date_time(options['value']) + # Format date number to the same precision as Excel. + options['value'] = "%.15g" % date_time + + if 'minimum' in options: + if not supported_datetime(options['minimum']): + warn("Conditional format 'minimum' must be a " + "datetime object.") + return -2 + else: + date_time = self._convert_date_time(options['minimum']) + options['minimum'] = "%.15g" % date_time + + if 'maximum' in options: + if not supported_datetime(options['maximum']): + warn("Conditional format 'maximum' must be a " + "datetime object.") + return -2 + else: + date_time = self._convert_date_time(options['maximum']) + options['maximum'] = "%.15g" % date_time + + # Swap last row/col for first row/col as necessary + if first_row > last_row: + first_row, last_row = last_row, first_row + + if first_col > last_col: + first_col, last_col = last_col, first_col + + # Set the formatting range. + # If the first and last cell are the same write a single cell. + if first_row == last_row and first_col == last_col: + cell_range = xl_rowcol_to_cell(first_row, first_col) + start_cell = cell_range + else: + cell_range = xl_range(first_row, first_col, last_row, last_col) + start_cell = xl_rowcol_to_cell(first_row, first_col) + + # Override with user defined multiple range if provided. + if 'multi_range' in options: + cell_range = options['multi_range'] + cell_range = cell_range.replace('$', '') + + # Get the dxf format index. + if 'format' in options and options['format']: + options['format'] = options['format']._get_dxf_index() + + # Set the priority based on the order of adding. + options['priority'] = self.dxf_priority + self.dxf_priority += 1 + + # Special handling of text criteria. + if options['type'] == 'text': + + if options['criteria'] == 'containsText': + options['type'] = 'containsText' + options['formula'] = ('NOT(ISERROR(SEARCH("%s",%s)))' + % (options['value'], start_cell)) + elif options['criteria'] == 'notContains': + options['type'] = 'notContainsText' + options['formula'] = ('ISERROR(SEARCH("%s",%s))' + % (options['value'], start_cell)) + elif options['criteria'] == 'beginsWith': + options['type'] = 'beginsWith' + options['formula'] = ('LEFT(%s,%d)="%s"' + % (start_cell, + len(options['value']), + options['value'])) + elif options['criteria'] == 'endsWith': + options['type'] = 'endsWith' + options['formula'] = ('RIGHT(%s,%d)="%s"' + % (start_cell, + len(options['value']), + options['value'])) + else: + warn("Invalid text criteria 'options['criteria']' " + "in conditional_formatting()") + + # Special handling of time time_period criteria. + if options['type'] == 'timePeriod': + + if options['criteria'] == 'yesterday': + options['formula'] = 'FLOOR(%s,1)=TODAY()-1' % start_cell + + elif options['criteria'] == 'today': + options['formula'] = 'FLOOR(%s,1)=TODAY()' % start_cell + + elif options['criteria'] == 'tomorrow': + options['formula'] = 'FLOOR(%s,1)=TODAY()+1' % start_cell + + elif options['criteria'] == 'last7Days': + options['formula'] = \ + ('AND(TODAY()-FLOOR(%s,1)<=6,FLOOR(%s,1)<=TODAY())' % + (start_cell, start_cell)) + + elif options['criteria'] == 'lastWeek': + options['formula'] = \ + ('AND(TODAY()-ROUNDDOWN(%s,0)>=(WEEKDAY(TODAY())),' + 'TODAY()-ROUNDDOWN(%s,0)<(WEEKDAY(TODAY())+7))' % + (start_cell, start_cell)) + + elif options['criteria'] == 'thisWeek': + options['formula'] = \ + ('AND(TODAY()-ROUNDDOWN(%s,0)<=WEEKDAY(TODAY())-1,' + 'ROUNDDOWN(%s,0)-TODAY()<=7-WEEKDAY(TODAY()))' % + (start_cell, start_cell)) + + elif options['criteria'] == 'continueWeek': + options['formula'] = \ + ('AND(ROUNDDOWN(%s,0)-TODAY()>(7-WEEKDAY(TODAY())),' + 'ROUNDDOWN(%s,0)-TODAY()<(15-WEEKDAY(TODAY())))' % + (start_cell, start_cell)) + + elif options['criteria'] == 'lastMonth': + options['formula'] = \ + ('AND(MONTH(%s)=MONTH(TODAY())-1,OR(YEAR(%s)=YEAR(' + 'TODAY()),AND(MONTH(%s)=1,YEAR(A1)=YEAR(TODAY())-1)))' % + (start_cell, start_cell, start_cell)) + + elif options['criteria'] == 'thisMonth': + options['formula'] = \ + ('AND(MONTH(%s)=MONTH(TODAY()),YEAR(%s)=YEAR(TODAY()))' % + (start_cell, start_cell)) + + elif options['criteria'] == 'continueMonth': + options['formula'] = \ + ('AND(MONTH(%s)=MONTH(TODAY())+1,OR(YEAR(%s)=YEAR(' + 'TODAY()),AND(MONTH(%s)=12,YEAR(%s)=YEAR(TODAY())+1)))' % + (start_cell, start_cell, start_cell, start_cell)) + + else: + warn("Invalid time_period criteria 'options['criteria']' " + "in conditional_formatting()") + + # Special handling of blanks/error types. + if options['type'] == 'containsBlanks': + options['formula'] = 'LEN(TRIM(%s))=0' % start_cell + + if options['type'] == 'notContainsBlanks': + options['formula'] = 'LEN(TRIM(%s))>0' % start_cell + + if options['type'] == 'containsErrors': + options['formula'] = 'ISERROR(%s)' % start_cell + + if options['type'] == 'notContainsErrors': + options['formula'] = 'NOT(ISERROR(%s))' % start_cell + + # Special handling for 2 color scale. + if options['type'] == '2_color_scale': + options['type'] = 'colorScale' + + # Color scales don't use any additional formatting. + options['format'] = None + + # Turn off 3 color parameters. + options['mid_type'] = None + options['mid_color'] = None + + options.setdefault('min_type', 'min') + options.setdefault('max_type', 'max') + options.setdefault('min_value', 0) + options.setdefault('max_value', 0) + options.setdefault('min_color', '#FF7128') + options.setdefault('max_color', '#FFEF9C') + + options['min_color'] = xl_color(options['min_color']) + options['max_color'] = xl_color(options['max_color']) + + # Special handling for 3 color scale. + if options['type'] == '3_color_scale': + options['type'] = 'colorScale' + + # Color scales don't use any additional formatting. + options['format'] = None + + options.setdefault('min_type', 'min') + options.setdefault('mid_type', 'percentile') + options.setdefault('max_type', 'max') + options.setdefault('min_value', 0) + options.setdefault('max_value', 0) + options.setdefault('min_color', '#F8696B') + options.setdefault('mid_color', '#FFEB84') + options.setdefault('max_color', '#63BE7B') + + options['min_color'] = xl_color(options['min_color']) + options['mid_color'] = xl_color(options['mid_color']) + options['max_color'] = xl_color(options['max_color']) + + # Set a default mid value. + if 'mid_value' not in options: + options['mid_value'] = 50 + + # Special handling for data bar. + if options['type'] == 'dataBar': + + # Color scales don't use any additional formatting. + options['format'] = None + + options.setdefault('min_type', 'min') + options.setdefault('max_type', 'max') + options.setdefault('min_value', 0) + options.setdefault('max_value', 0) + options.setdefault('bar_color', '#638EC6') + + options['bar_color'] = xl_color(options['bar_color']) + + # Store the validation information until we close the worksheet. + if cell_range in self.cond_formats: + self.cond_formats[cell_range].append(options) + else: + self.cond_formats[cell_range] = [options] + + @convert_range_args + def add_table(self, first_row, first_col, last_row, last_col, + options=None): + """ + Add an Excel table to a worksheet. + + Args: + first_row: The first row of the cell range. (zero indexed). + first_col: The first column of the cell range. + last_row: The last row of the cell range. (zero indexed). + last_col: The last column of the cell range. + options: Table format options. (Optional) + + Returns: + 0: Success. + -1: Not supported in optimisation mode. + -2: Row or column is out of worksheet bounds. + -3: Incorrect parameter or option. + """ + table = {} + col_formats = {} + + if options is None: + options = {} + + if self.optimization == 1: + warn("add_table() isn't supported when set_optimization() is on") + return -1 + + # Check that row and col are valid without storing the values. + if self._check_dimensions(first_row, first_col, True, True): + return -2 + if self._check_dimensions(last_row, last_col, True, True): + return -2 + + # List of valid input parameters. + valid_parameter = { + 'autofilter': True, + 'banded_columns': True, + 'banded_rows': True, + 'columns': True, + 'data': True, + 'first_column': True, + 'header_row': True, + 'last_column': True, + 'name': True, + 'style': True, + 'total_row': True, + } + + # Check for valid input parameters. + for param_key in options.keys(): + if param_key not in valid_parameter: + warn("Unknown parameter '%s' in add_table()" % param_key) + return -3 + + # Turn on Excel's defaults. + options['banded_rows'] = options.get('banded_rows', True) + options['header_row'] = options.get('header_row', True) + options['autofilter'] = options.get('autofilter', True) + + # Set the table options. + table['show_first_col'] = options.get('first_column', False) + table['show_last_col'] = options.get('last_column', False) + table['show_row_stripes'] = options.get('banded_rows', False) + table['show_col_stripes'] = options.get('banded_columns', False) + table['header_row_count'] = options.get('header_row', 0) + table['totals_row_shown'] = options.get('total_row', False) + + # Set the table name. + if 'name' in options: + name = options['name'] + table['name'] = name + + if ' ' in name: + warn("Name '%s' in add_table() cannot contain spaces" + % force_unicode(name)) + return -3 + + # Warn if the name contains invalid chars as defined by Excel. + if (not re.match(r'^[\w\\][\w\\.]*$', name, re.UNICODE) + or re.match(r'^\d', name)): + warn("Invalid Excel characters in add_table(): '%s'" + % force_unicode(name)) + return -1 + + # Warn if the name looks like a cell name. + if re.match(r'^[a-zA-Z][a-zA-Z]?[a-dA-D]?[0-9]+$', name): + warn("Name looks like a cell name in add_table(): '%s'" + % force_unicode(name)) + return -1 + + # Warn if the name looks like a R1C1 cell reference. + if (re.match(r'^[rcRC]$', name) + or re.match(r'^[rcRC]\d+[rcRC]\d+$', name)): + warn("Invalid name '%s' like a RC cell ref in add_table()" + % force_unicode(name)) + return -1 + + # Set the table style. + if 'style' in options: + table['style'] = options['style'] + # Remove whitespace from style name. + table['style'] = table['style'].replace(' ', '') + else: + table['style'] = "TableStyleMedium9" + + # Swap last row/col for first row/col as necessary. + if first_row > last_row: + (first_row, last_row) = (last_row, first_row) + if first_col > last_col: + (first_col, last_col) = (last_col, first_col) + + # Set the data range rows (without the header and footer). + first_data_row = first_row + last_data_row = last_row + + if 'header_row' in options: + first_data_row += 1 + + if 'total_row' in options: + last_data_row -= 1 + + # Set the table and autofilter ranges. + table['range'] = xl_range(first_row, first_col, + last_row, last_col) + + table['a_range'] = xl_range(first_row, first_col, + last_data_row, last_col) + + # If the header row if off the default is to turn autofilter off. + if not options['header_row']: + options['autofilter'] = 0 + + # Set the autofilter range. + if options['autofilter']: + table['autofilter'] = table['a_range'] + + # Add the table columns. + col_id = 1 + table['columns'] = [] + + for col_num in range(first_col, last_col + 1): + # Set up the default column data. + col_data = { + 'id': col_id, + 'name': 'Column' + str(col_id), + 'total_string': '', + 'total_function': '', + 'total_value': 0, + 'formula': '', + 'format': None, + 'name_format': None, + } + + # Overwrite the defaults with any use defined values. + if 'columns' in options: + # Check if there are user defined values for this column. + user_data = options['columns'][col_id - 1] + + if user_data: + # Get the column format. + xformat = user_data.get('format', None) + + # Map user defined values to internal values. + if user_data.get('header'): + col_data['name'] = user_data['header'] + + col_data['name_format'] = user_data.get('header_format') + + # Handle the column formula. + if 'formula' in user_data and user_data['formula']: + formula = user_data['formula'] + + # Remove the formula '=' sign if it exists. + if formula.startswith('='): + formula = formula.lstrip('=') + + # Covert Excel 2010 "@" ref to 2007 "#This Row". + formula = formula.replace('@', '[#This Row],') + + col_data['formula'] = formula + + for row in range(first_data_row, last_data_row + 1): + self.write_formula(row, col_num, formula, xformat) + + # Handle the function for the total row. + if user_data.get('total_function'): + function = user_data['total_function'] + + # Massage the function name. + function = function.lower() + function = function.replace('_', '') + function = function.replace(' ', '') + + if function == 'countnums': + function = 'countNums' + if function == 'stddev': + function = 'stdDev' + + col_data['total_function'] = function + + formula = \ + self._table_function_to_formula(function, + col_data['name']) + + value = user_data.get('total_value', 0) + + self.write_formula(last_row, col_num, formula, xformat, + value) + + elif user_data.get('total_string'): + # Total label only (not a function). + total_string = user_data['total_string'] + col_data['total_string'] = total_string + + self.write_string(last_row, col_num, total_string, + user_data.get('format')) + + # Get the dxf format index. + if xformat is not None: + col_data['format'] = xformat._get_dxf_index() + + # Store the column format for writing the cell data. + # It doesn't matter if it is undefined. + col_formats[col_id - 1] = xformat + + # Store the column data. + table['columns'].append(col_data) + + # Write the column headers to the worksheet. + if options['header_row']: + self.write_string(first_row, col_num, col_data['name'], + col_data['name_format']) + + col_id += 1 + + # Write the cell data if supplied. + if 'data' in options: + data = options['data'] + + i = 0 # For indexing the row data. + for row in range(first_data_row, last_data_row + 1): + j = 0 # For indexing the col data. + for col in range(first_col, last_col + 1): + if i < len(data) and j < len(data[i]): + token = data[i][j] + if j in col_formats: + self.write(row, col, token, col_formats[j]) + else: + self.write(row, col, token, None) + j += 1 + i += 1 + + # Store the table data. + self.tables.append(table) + + return table + + @convert_cell_args + def add_sparkline(self, row, col, options): + """ + Add sparklines to the worksheet. + + Args: + row: The cell row (zero indexed). + col: The cell column (zero indexed). + options: Sparkline formatting options. + + Returns: + 0: Success. + -1: Row or column is out of worksheet bounds. + -2: Incorrect parameter or option. + + """ + + # Check that row and col are valid without storing the values. + if self._check_dimensions(row, col, True, True): + return -1 + + sparkline = {'locations': [xl_rowcol_to_cell(row, col)]} + + # List of valid input parameters. + valid_parameters = { + 'location': True, + 'range': True, + 'type': True, + 'high_point': True, + 'low_point': True, + 'negative_points': True, + 'first_point': True, + 'last_point': True, + 'markers': True, + 'style': True, + 'series_color': True, + 'negative_color': True, + 'markers_color': True, + 'first_color': True, + 'last_color': True, + 'high_color': True, + 'low_color': True, + 'max': True, + 'min': True, + 'axis': True, + 'reverse': True, + 'empty_cells': True, + 'show_hidden': True, + 'plot_hidden': True, + 'date_axis': True, + 'weight': True, + } + + # Check for valid input parameters. + for param_key in options.keys(): + if param_key not in valid_parameters: + warn("Unknown parameter '%s' in add_sparkline()" % param_key) + return -1 + + # 'range' is a required parameter. + if 'range' not in options: + warn("Parameter 'range' is required in add_sparkline()") + return -2 + + # Handle the sparkline type. + spark_type = options.get('type', 'line') + + if spark_type not in ('line', 'column', 'win_loss'): + warn("Parameter 'type' must be 'line', 'column' " + "or 'win_loss' in add_sparkline()") + return -2 + + if spark_type == 'win_loss': + spark_type = 'stacked' + sparkline['type'] = spark_type + + # We handle single location/range values or list of values. + if 'location' in options: + if type(options['location']) is list: + sparkline['locations'] = options['location'] + else: + sparkline['locations'] = [options['location']] + + if type(options['range']) is list: + sparkline['ranges'] = options['range'] + else: + sparkline['ranges'] = [options['range']] + + range_count = len(sparkline['ranges']) + location_count = len(sparkline['locations']) + + # The ranges and locations must match. + if range_count != location_count: + warn("Must have the same number of location and range " + "parameters in add_sparkline()") + return -2 + + # Store the count. + sparkline['count'] = len(sparkline['locations']) + + # Get the worksheet name for the range conversion below. + sheetname = quote_sheetname(self.name) + + # Cleanup the input ranges. + new_ranges = [] + for spark_range in sparkline['ranges']: + + # Remove the absolute reference $ symbols. + spark_range = spark_range.replace('$', '') + + # Remove the = from formula. + spark_range = spark_range.lstrip('=') + + # Convert a simple range into a full Sheet1!A1:D1 range. + if '!' not in spark_range: + spark_range = sheetname + "!" + spark_range + + new_ranges.append(spark_range) + + sparkline['ranges'] = new_ranges + + # Cleanup the input locations. + new_locations = [] + for location in sparkline['locations']: + location = location.replace('$', '') + new_locations.append(location) + + sparkline['locations'] = new_locations + + # Map options. + sparkline['high'] = options.get('high_point') + sparkline['low'] = options.get('low_point') + sparkline['negative'] = options.get('negative_points') + sparkline['first'] = options.get('first_point') + sparkline['last'] = options.get('last_point') + sparkline['markers'] = options.get('markers') + sparkline['min'] = options.get('min') + sparkline['max'] = options.get('max') + sparkline['axis'] = options.get('axis') + sparkline['reverse'] = options.get('reverse') + sparkline['hidden'] = options.get('show_hidden') + sparkline['weight'] = options.get('weight') + + # Map empty cells options. + empty = options.get('empty_cells', '') + + if empty == 'zero': + sparkline['empty'] = 0 + elif empty == 'connect': + sparkline['empty'] = 'span' + else: + sparkline['empty'] = 'gap' + + # Map the date axis range. + date_range = options.get('date_axis') + + if date_range and '!' not in date_range: + date_range = sheetname + "!" + date_range + + sparkline['date_axis'] = date_range + + # Set the sparkline styles. + style_id = options.get('style', 0) + style = get_sparkline_style(style_id) + + sparkline['series_color'] = style['series'] + sparkline['negative_color'] = style['negative'] + sparkline['markers_color'] = style['markers'] + sparkline['first_color'] = style['first'] + sparkline['last_color'] = style['last'] + sparkline['high_color'] = style['high'] + sparkline['low_color'] = style['low'] + + # Override the style colors with user defined colors. + self._set_spark_color(sparkline, options, 'series_color') + self._set_spark_color(sparkline, options, 'negative_color') + self._set_spark_color(sparkline, options, 'markers_color') + self._set_spark_color(sparkline, options, 'first_color') + self._set_spark_color(sparkline, options, 'last_color') + self._set_spark_color(sparkline, options, 'high_color') + self._set_spark_color(sparkline, options, 'low_color') + + self.sparklines.append(sparkline) + + @convert_range_args + def set_selection(self, first_row, first_col, last_row, last_col): + """ + Set the selected cell or cells in a worksheet + + Args: + first_row: The first row of the cell range. (zero indexed). + first_col: The first column of the cell range. + last_row: The last row of the cell range. (zero indexed). + last_col: The last column of the cell range. + + Returns: + 0: Nothing. + """ + pane = None + + # Range selection. Do this before swapping max/min to allow the + # selection direction to be reversed. + active_cell = xl_rowcol_to_cell(first_row, first_col) + + # Swap last row/col for first row/col if necessary + if first_row > last_row: + (first_row, last_row) = (last_row, first_row) + + if first_col > last_col: + (first_col, last_col) = (last_col, first_col) + + # If the first and last cell are the same write a single cell. + if (first_row == last_row) and (first_col == last_col): + sqref = active_cell + else: + sqref = xl_range(first_row, first_col, last_row, last_col) + + # Selection isn't set for cell A1. + if sqref == 'A1': + return + + self.selections = [[pane, active_cell, sqref]] + + def outline_settings(self, outline_on=1, outline_below=1, outline_right=1, + outline_style=0): + """ + Control outline settings. + + Args: + outline_on: Outlines are visible. Optional, defaults to True. + outline_below: Show row outline symbols below the outline bar. + Optional, defaults to True. + outline_right: Show column outline symbols to the right of the + outline bar. Optional, defaults to True. + outline_style: Use Automatic style. Optional, defaults to False. + + Returns: + 0: Nothing. + """ + self.outline_on = outline_on + self.outline_below = outline_below + self.outline_right = outline_right + self.outline_style = outline_style + + self.outline_changed = True + + @convert_cell_args + def freeze_panes(self, row, col, top_row=None, left_col=None, pane_type=0): + """ + Create worksheet panes and mark them as frozen. + + Args: + row: The cell row (zero indexed). + col: The cell column (zero indexed). + top_row: Topmost visible row in scrolling region of pane. + left_col: Leftmost visible row in scrolling region of pane. + + Returns: + 0: Nothing. + + """ + if top_row is None: + top_row = row + + if left_col is None: + left_col = col + + self.panes = [row, col, top_row, left_col, pane_type] + + @convert_cell_args + def split_panes(self, x, y, top_row=None, left_col=None): + """ + Create worksheet panes and mark them as split. + + Args: + x: The position for the vertical split. + y: The position for the horizontal split. + top_row: Topmost visible row in scrolling region of pane. + left_col: Leftmost visible row in scrolling region of pane. + + Returns: + 0: Nothing. + + """ + # Same as freeze panes with a different pane type. + self.freeze_panes(x, y, top_row, left_col, 2) + + def set_zoom(self, zoom=100): + """ + Set the worksheet zoom factor. + + Args: + zoom: Scale factor: 10 <= zoom <= 400. + + Returns: + Nothing. + + """ + # Ensure the zoom scale is in Excel's range. + if zoom < 10 or zoom > 400: + warn("Zoom factor %d outside range: 10 <= zoom <= 400" % zoom) + zoom = 100 + + self.zoom = int(zoom) + + def right_to_left(self): + """ + Display the worksheet right to left for some versions of Excel. + + Args: + None. + + Returns: + Nothing. + + """ + self.is_right_to_left = 1 + + def hide_zero(self): + """ + Hide zero values in worksheet cells. + + Args: + None. + + Returns: + Nothing. + + """ + self.show_zeros = 0 + + def set_tab_color(self, color): + """ + Set the color of the worksheet tab. + + Args: + color: A #RGB color index. + + Returns: + Nothing. + + """ + self.tab_color = xl_color(color) + + def protect(self, password='', options=None): + """ + Set the password and protection options of the worksheet. + + Args: + password: An optional password string. + options: A dictionary of worksheet objects to protect. + + Returns: + Nothing. + + """ + if password != '': + password = self._encode_password(password) + + if not options: + options = {} + + # Default values for objects that can be protected. + defaults = { + 'sheet': True, + 'content': False, + 'objects': False, + 'scenarios': False, + 'format_cells': False, + 'format_columns': False, + 'format_rows': False, + 'insert_columns': False, + 'insert_rows': False, + 'insert_hyperlinks': False, + 'delete_columns': False, + 'delete_rows': False, + 'select_locked_cells': True, + 'sort': False, + 'autofilter': False, + 'pivot_tables': False, + 'select_unlocked_cells': True} + + # Overwrite the defaults with user specified values. + for key in (options.keys()): + + if key in defaults: + defaults[key] = options[key] + else: + warn("Unknown protection object: '%s'" % key) + + # Set the password after the user defined values. + defaults['password'] = password + + self.protect_options = defaults + + @convert_cell_args + def insert_button(self, row, col, options={}): + """ + Insert a button form object into the worksheet. + + Args: + row: The cell row (zero indexed). + col: The cell column (zero indexed). + options: Button formatting options. + + Returns: + 0: Success. + -1: Row or column is out of worksheet bounds. + + """ + button = self._button_params(row, col, options) + + self.buttons_list.append(button) + + self.has_vml = 1 + + ########################################################################### + # + # Public API. Page Setup methods. + # + ########################################################################### + def set_landscape(self): + """ + Set the page orientation as landscape. + + Args: + None. + + Returns: + Nothing. + + """ + self.orientation = 0 + self.page_setup_changed = True + + def set_portrait(self): + """ + Set the page orientation as portrait. + + Args: + None. + + Returns: + Nothing. + + """ + self.orientation = 1 + self.page_setup_changed = True + + def set_page_view(self): + """ + Set the page view mode. + + Args: + None. + + Returns: + Nothing. + + """ + self.page_view = 1 + + def set_paper(self, paper_size): + """ + Set the paper type. US Letter = 1, A4 = 9. + + Args: + paper_size: Paper index. + + Returns: + Nothing. + + """ + if paper_size: + self.paper_size = paper_size + self.page_setup_changed = True + + def center_horizontally(self): + """ + Center the page horizontally. + + Args: + None. + + Returns: + Nothing. + + """ + self.print_options_changed = True + self.hcenter = 1 + + def center_vertically(self): + """ + Center the page vertically. + + Args: + None. + + Returns: + Nothing. + + """ + self.print_options_changed = True + self.vcenter = 1 + + def set_margins(self, left=0.7, right=0.7, top=0.75, bottom=0.75): + """ + Set all the page margins in inches. + + Args: + left: Left margin. + right: Right margin. + top: Top margin. + bottom: Bottom margin. + + Returns: + Nothing. + + """ + self.margin_left = left + self.margin_right = right + self.margin_top = top + self.margin_bottom = bottom + + def set_header(self, header='', options=None, margin=None): + """ + Set the page header caption and optional margin. + + Args: + header: Header string. + margin: Header margin. + options: Header options, mainly for images. + + Returns: + Nothing. + + """ + header_orig = header + header = header.replace('&[Picture]', '&G') + + if len(header) >= 255: + warn('Header string must be less than 255 characters') + return + + if options is not None: + # For backward compatibility allow options to be the margin. + if not isinstance(options, dict): + options = {'margin': options} + else: + options = {} + + # For backward compatibility. + if margin is not None: + options['margin'] = margin + + # Reset the list in case the function is called more than once. + self.header_images = [] + + if options.get('image_left'): + self.header_images.append([options.get('image_left'), + options.get('image_data_left'), + 'LH']) + + if options.get('image_center'): + self.header_images.append([options.get('image_center'), + options.get('image_data_center'), + 'CH']) + + if options.get('image_right'): + self.header_images.append([options.get('image_right'), + options.get('image_data_right'), + 'RH']) + + placeholder_count = header.count('&G') + image_count = len(self.header_images) + + if placeholder_count != image_count: + warn("Number of header images (%s) doesn't match placeholder " + "count (%s) in string: %s" + % (image_count, placeholder_count, header_orig)) + self.header_images = [] + return + + if 'align_with_margins' in options: + self.header_footer_aligns = options['align_with_margins'] + + if 'scale_with_doc' in options: + self.header_footer_scales = options['scale_with_doc'] + + self.header = header + self.margin_header = options.get('margin', 0.3) + self.header_footer_changed = True + + if image_count: + self.has_header_vml = True + + def set_footer(self, footer='', options=None, margin=None): + """ + Set the page footer caption and optional margin. + + Args: + footer: Footer string. + margin: Footer margin. + options: Footer options, mainly for images. + + Returns: + Nothing. + + """ + footer_orig = footer + footer = footer.replace('&[Picture]', '&G') + + if len(footer) >= 255: + warn('Footer string must be less than 255 characters') + return + + if options is not None: + # For backward compatibility allow options to be the margin. + if not isinstance(options, dict): + options = {'margin': options} + else: + options = {} + + # For backward compatibility. + if margin is not None: + options['margin'] = margin + + # Reset the list in case the function is called more than once. + self.footer_images = [] + + if options.get('image_left'): + self.footer_images.append([options.get('image_left'), + options.get('image_data_left'), + 'LF']) + + if options.get('image_center'): + self.footer_images.append([options.get('image_center'), + options.get('image_data_center'), + 'CF']) + + if options.get('image_right'): + self.footer_images.append([options.get('image_right'), + options.get('image_data_right'), + 'RF']) + + placeholder_count = footer.count('&G') + image_count = len(self.footer_images) + + if placeholder_count != image_count: + warn("Number of footer images (%s) doesn't match placeholder " + "count (%s) in string: %s" + % (image_count, placeholder_count, footer_orig)) + self.footer_images = [] + return + + if 'align_with_margins' in options: + self.header_footer_aligns = options['align_with_margins'] + + if 'scale_with_doc' in options: + self.header_footer_scales = options['scale_with_doc'] + + self.footer = footer + self.margin_footer = options.get('margin', 0.3) + self.header_footer_changed = True + + if image_count: + self.has_header_vml = True + + def repeat_rows(self, first_row, last_row=None): + """ + Set the rows to repeat at the top of each printed page. + + Args: + first_row: Start row for range. + last_row: End row for range. + + Returns: + Nothing. + + """ + if last_row is None: + last_row = first_row + + # Convert rows to 1 based. + first_row += 1 + last_row += 1 + + # Create the row range area like: $1:$2. + area = '$%d:$%d' % (first_row, last_row) + + # Build up the print titles area "Sheet1!$1:$2" + sheetname = quote_sheetname(self.name) + self.repeat_row_range = sheetname + '!' + area + + @convert_column_args + def repeat_columns(self, first_col, last_col=None): + """ + Set the columns to repeat at the left hand side of each printed page. + + Args: + first_col: Start column for range. + last_col: End column for range. + + Returns: + Nothing. + + """ + if last_col is None: + last_col = first_col + + # Convert to A notation. + first_col = xl_col_to_name(first_col, 1) + last_col = xl_col_to_name(last_col, 1) + + # Create a column range like $C:$D. + area = first_col + ':' + last_col + + # Build up the print area range "=Sheet2!$C:$D" + sheetname = quote_sheetname(self.name) + self.repeat_col_range = sheetname + "!" + area + + def hide_gridlines(self, option=1): + """ + Set the option to hide gridlines on the screen and the printed page. + + Args: + option: 0 : Don't hide gridlines + 1 : Hide printed gridlines only + 2 : Hide screen and printed gridlines + + Returns: + Nothing. + + """ + if option == 0: + self.print_gridlines = 1 + self.screen_gridlines = 1 + self.print_options_changed = True + elif option == 1: + self.print_gridlines = 0 + self.screen_gridlines = 1 + else: + self.print_gridlines = 0 + self.screen_gridlines = 0 + + def print_row_col_headers(self): + """ + Set the option to print the row and column headers on the printed page. + + Args: + None. + + Returns: + Nothing. + + """ + self.print_headers = 1 + self.print_options_changed = True + + @convert_range_args + def print_area(self, first_row, first_col, last_row, last_col): + """ + Set the print area in the current worksheet. + + Args: + first_row: The first row of the cell range. (zero indexed). + first_col: The first column of the cell range. + last_row: The last row of the cell range. (zero indexed). + last_col: The last column of the cell range. + + Returns: + 0: Success. + -1: Row or column is out of worksheet bounds. + + """ + # Set the print area in the current worksheet. + + # Ignore max print area since it is the same as no area for Excel. + if (first_row == 0 and first_col == 0 + and last_row == self.xls_rowmax - 1 + and last_col == self.xls_colmax - 1): + return + + # Build up the print area range "Sheet1!$A$1:$C$13". + area = self._convert_name_area(first_row, first_col, + last_row, last_col) + self.print_area_range = area + + def print_across(self): + """ + Set the order in which pages are printed. + + Args: + None. + + Returns: + Nothing. + + """ + self.page_order = 1 + self.page_setup_changed = True + + def fit_to_pages(self, width, height): + """ + Fit the printed area to a specific number of pages both vertically and + horizontally. + + Args: + width: Number of pages horizontally. + height: Number of pages vertically. + + Returns: + Nothing. + + """ + self.fit_page = 1 + self.fit_width = width + self.fit_height = height + self.page_setup_changed = True + + def set_start_page(self, start_page): + """ + Set the start page number when printing. + + Args: + start_page: Start page number. + + Returns: + Nothing. + + """ + self.page_start = start_page + + def set_print_scale(self, scale): + """ + Set the scale factor for the printed page. + + Args: + scale: Print scale. 10 <= scale <= 400. + + Returns: + Nothing. + + """ + # Confine the scale to Excel's range. + if scale < 10 or scale > 400: + warn("Print scale '%d' outside range: 10 <= scale <= 400" % scale) + return + + # Turn off "fit to page" option when print scale is on. + self.fit_page = 0 + + self.print_scale = int(scale) + self.page_setup_changed = True + + def set_h_pagebreaks(self, breaks): + """ + Set the horizontal page breaks on a worksheet. + + Args: + breaks: List of rows where the page breaks should be added. + + Returns: + Nothing. + + """ + self.hbreaks = breaks + + def set_v_pagebreaks(self, breaks): + """ + Set the horizontal page breaks on a worksheet. + + Args: + breaks: List of columns where the page breaks should be added. + + Returns: + Nothing. + + """ + self.vbreaks = breaks + + def set_vba_name(self, name=None): + """ + Set the VBA name for the worksheet. By default this is the + same as the sheet name: i.e., Sheet1 etc. + + Args: + name: The VBA name for the worksheet. + + Returns: + Nothing. + + """ + if name is not None: + self.vba_codename = name + else: + self.vba_codename = self.name + + ########################################################################### + # + # Private API. + # + ########################################################################### + def _initialize(self, init_data): + self.name = init_data['name'] + self.index = init_data['index'] + self.str_table = init_data['str_table'] + self.worksheet_meta = init_data['worksheet_meta'] + self.optimization = init_data['optimization'] + self.tmpdir = init_data['tmpdir'] + self.date_1904 = init_data['date_1904'] + self.strings_to_numbers = init_data['strings_to_numbers'] + self.strings_to_formulas = init_data['strings_to_formulas'] + self.strings_to_urls = init_data['strings_to_urls'] + self.nan_inf_to_errors = init_data['nan_inf_to_errors'] + self.default_date_format = init_data['default_date_format'] + self.default_url_format = init_data['default_url_format'] + self.excel2003_style = init_data['excel2003_style'] + + if self.excel2003_style: + self.original_row_height = 12.75 + self.default_row_height = 12.75 + self.default_row_pixels = 17 + self.margin_left = 0.75 + self.margin_right = 0.75 + self.margin_top = 1 + self.margin_bottom = 1 + self.margin_header = 0.5 + self.margin_footer = 0.5 + self.header_footer_aligns = False + + # Open a temp filehandle to store row data in optimization mode. + if self.optimization == 1: + # This is sub-optimal but we need to create a temp file + # with utf8 encoding in Python < 3. + (fd, filename) = tempfile.mkstemp(dir=self.tmpdir) + os.close(fd) + self.row_data_filename = filename + self.row_data_fh = codecs.open(filename, 'w+', 'utf-8') + + # Set as the worksheet filehandle until the file is assembled. + self.fh = self.row_data_fh + + def _assemble_xml_file(self): + # Assemble and write the XML file. + + # Write the XML declaration. + self._xml_declaration() + + # Write the root worksheet element. + self._write_worksheet() + + # Write the worksheet properties. + self._write_sheet_pr() + + # Write the worksheet dimensions. + self._write_dimension() + + # Write the sheet view properties. + self._write_sheet_views() + + # Write the sheet format properties. + self._write_sheet_format_pr() + + # Write the sheet column info. + self._write_cols() + + # Write the worksheet data such as rows columns and cells. + if self.optimization == 0: + self._write_sheet_data() + else: + self._write_optimized_sheet_data() + + # Write the sheetProtection element. + self._write_sheet_protection() + + # Write the phoneticPr element. + if self.excel2003_style: + self._write_phonetic_pr() + + # Write the autoFilter element. + self._write_auto_filter() + + # Write the mergeCells element. + self._write_merge_cells() + + # Write the conditional formats. + self._write_conditional_formats() + + # Write the dataValidations element. + self._write_data_validations() + + # Write the hyperlink element. + self._write_hyperlinks() + + # Write the printOptions element. + self._write_print_options() + + # Write the worksheet page_margins. + self._write_page_margins() + + # Write the worksheet page setup. + self._write_page_setup() + + # Write the headerFooter element. + self._write_header_footer() + + # Write the rowBreaks element. + self._write_row_breaks() + + # Write the colBreaks element. + self._write_col_breaks() + + # Write the drawing element. + self._write_drawings() + + # Write the legacyDrawing element. + self._write_legacy_drawing() + + # Write the legacyDrawingHF element. + self._write_legacy_drawing_hf() + + # Write the tableParts element. + self._write_table_parts() + + # Write the extLst and sparklines. + self._write_ext_sparklines() + + # Close the worksheet tag. + self._xml_end_tag('worksheet') + + # Close the file. + self._xml_close() + + def _check_dimensions(self, row, col, ignore_row=False, ignore_col=False): + # Check that row and col are valid and store the max and min + # values for use in other methods/elements. The ignore_row / + # ignore_col flags is used to indicate that we wish to perform + # the dimension check without storing the value. The ignore + # flags are use by set_row() and data_validate. + + # Check that the row/col are within the worksheet bounds. + if row < 0 or col < 0: + return -1 + if row >= self.xls_rowmax or col >= self.xls_colmax: + return -1 + + # In optimization mode we don't change dimensions for rows + # that are already written. + if not ignore_row and not ignore_col and self.optimization == 1: + if row < self.previous_row: + return -2 + + if not ignore_row: + if self.dim_rowmin is None or row < self.dim_rowmin: + self.dim_rowmin = row + if self.dim_rowmax is None or row > self.dim_rowmax: + self.dim_rowmax = row + + if not ignore_col: + if self.dim_colmin is None or col < self.dim_colmin: + self.dim_colmin = col + if self.dim_colmax is None or col > self.dim_colmax: + self.dim_colmax = col + + return 0 + + def _convert_date_time(self, dt_obj): + # Convert a datetime object to an Excel serial date and time. + return datetime_to_excel_datetime(dt_obj, self.date_1904) + + def _convert_name_area(self, row_num_1, col_num_1, row_num_2, col_num_2): + # Convert zero indexed rows and columns to the format required by + # worksheet named ranges, eg, "Sheet1!$A$1:$C$13". + + range1 = '' + range2 = '' + area = '' + row_col_only = 0 + + # Convert to A1 notation. + col_char_1 = xl_col_to_name(col_num_1, 1) + col_char_2 = xl_col_to_name(col_num_2, 1) + row_char_1 = '$' + str(row_num_1 + 1) + row_char_2 = '$' + str(row_num_2 + 1) + + # We need to handle special cases that refer to rows or columns only. + if row_num_1 == 0 and row_num_2 == self.xls_rowmax - 1: + range1 = col_char_1 + range2 = col_char_2 + row_col_only = 1 + elif col_num_1 == 0 and col_num_2 == self.xls_colmax - 1: + range1 = row_char_1 + range2 = row_char_2 + row_col_only = 1 + else: + range1 = col_char_1 + row_char_1 + range2 = col_char_2 + row_char_2 + + # A repeated range is only written once (if it isn't a special case). + if range1 == range2 and not row_col_only: + area = range1 + else: + area = range1 + ':' + range2 + + # Build up the print area range "Sheet1!$A$1:$C$13". + sheetname = quote_sheetname(self.name) + area = sheetname + "!" + area + + return area + + def _sort_pagebreaks(self, breaks): + # This is an internal method used to filter elements of a list of + # pagebreaks used in the _store_hbreak() and _store_vbreak() methods. + # It: + # 1. Removes duplicate entries from the list. + # 2. Sorts the list. + # 3. Removes 0 from the list if present. + if not breaks: + return + + breaks_set = set(breaks) + + if 0 in breaks_set: + breaks_set.remove(0) + + breaks_list = list(breaks_set) + breaks_list.sort() + + # The Excel 2007 specification says that the maximum number of page + # breaks is 1026. However, in practice it is actually 1023. + max_num_breaks = 1023 + if len(breaks_list) > max_num_breaks: + breaks_list = breaks_list[:max_num_breaks] + + return breaks_list + + def _extract_filter_tokens(self, expression): + # Extract the tokens from the filter expression. The tokens are mainly + # non-whitespace groups. The only tricky part is to extract string + # tokens that contain whitespace and/or quoted double quotes (Excel's + # escaped quotes). + # + # Examples: 'x < 2000' + # 'x > 2000 and x < 5000' + # 'x = "foo"' + # 'x = "foo bar"' + # 'x = "foo "" bar"' + # + if not expression: + return [] + + token_re = re.compile(r'"(?:[^"]|"")*"|\S+') + tokens = token_re.findall(expression) + + new_tokens = [] + # Remove single leading and trailing quotes and un-escape other quotes. + for token in tokens: + if token.startswith('"'): + token = token[1:] + + if token.endswith('"'): + token = token[:-1] + + token = token.replace('""', '"') + + new_tokens.append(token) + + return new_tokens + + def _parse_filter_expression(self, expression, tokens): + # Converts the tokens of a possibly conditional expression into 1 or 2 + # sub expressions for further parsing. + # + # Examples: + # ('x', '==', 2000) -> exp1 + # ('x', '>', 2000, 'and', 'x', '<', 5000) -> exp1 and exp2 + + if len(tokens) == 7: + # The number of tokens will be either 3 (for 1 expression) + # or 7 (for 2 expressions). + conditional = tokens[3] + + if re.match('(and|&&)', conditional): + conditional = 0 + elif re.match('(or|\|\|)', conditional): + conditional = 1 + else: + warn("Token '%s' is not a valid conditional " + "in filter expression '%s'" % (conditional, expression)) + + expression_1 = self._parse_filter_tokens(expression, tokens[0:3]) + expression_2 = self._parse_filter_tokens(expression, tokens[4:7]) + + return expression_1 + [conditional] + expression_2 + else: + return self._parse_filter_tokens(expression, tokens) + + def _parse_filter_tokens(self, expression, tokens): + # Parse the 3 tokens of a filter expression and return the operator + # and token. The use of numbers instead of operators is a legacy of + # Spreadsheet::WriteExcel. + operators = { + '==': 2, + '=': 2, + '=~': 2, + 'eq': 2, + + '!=': 5, + '!~': 5, + 'ne': 5, + '<>': 5, + + '<': 1, + '<=': 3, + '>': 4, + '>=': 6, + } + + operator = operators.get(tokens[1], None) + token = tokens[2] + + # Special handling of "Top" filter expressions. + if re.match('top|bottom', tokens[0].lower()): + value = int(tokens[1]) + + if value < 1 or value > 500: + warn("The value '%d' in expression '%s' " + "must be in the range 1 to 500" % (value, expression)) + + token = token.lower() + + if token != 'items' and token != '%': + warn("The type '%s' in expression '%s' " + "must be either 'items' or '%'" % (token, expression)) + + if tokens[0].lower() == 'top': + operator = 30 + else: + operator = 32 + + if tokens[2] == '%': + operator += 1 + + token = str(value) + + if not operator and tokens[0]: + warn("Token '%s' is not a valid operator " + "in filter expression '%s'" % (token[0], expression)) + + # Special handling for Blanks/NonBlanks. + if re.match('blanks|nonblanks', token.lower()): + # Only allow Equals or NotEqual in this context. + if operator != 2 and operator != 5: + warn("The operator '%s' in expression '%s' " + "is not valid in relation to Blanks/NonBlanks'" + % (tokens[1], expression)) + + token = token.lower() + + # The operator should always be 2 (=) to flag a "simple" equality + # in the binary record. Therefore we convert <> to =. + if token == 'blanks': + if operator == 5: + token = ' ' + else: + if operator == 5: + operator = 2 + token = 'blanks' + else: + operator = 5 + token = ' ' + + # if the string token contains an Excel match character then change the + # operator type to indicate a non "simple" equality. + if operator == 2 and re.search('[*?]', token): + operator = 22 + + return [operator, token] + + def _encode_password(self, plaintext): + # Encode the worksheet protection "password" as a simple hash. + # Based on the algorithm by Daniel Rentz of OpenOffice. + i = 0 + count = len(plaintext) + digits = [] + + for char in plaintext: + i += 1 + char = ord(char) << i + low_15 = char & 0x7fff + high_15 = char & 0x7fff << 15 + high_15 >>= 15 + char = low_15 | high_15 + digits.append(char) + + password_hash = 0x0000 + + for digit in digits: + password_hash ^= digit + + password_hash ^= count + password_hash ^= 0xCE4B + + return "%X" % password_hash + + def _prepare_image(self, index, image_id, drawing_id, width, height, + name, image_type, x_dpi, y_dpi): + # Set up images/drawings. + drawing_type = 2 + (row, col, _, x_offset, y_offset, + x_scale, y_scale, url, tip, anchor, _) = self.images[index] + + width *= x_scale + height *= y_scale + + # Scale by non 96dpi resolutions. + width *= 96.0 / x_dpi + height *= 96.0 / y_dpi + + dimensions = self._position_object_emus(col, row, x_offset, y_offset, + width, height) + + # Convert from pixels to emus. + width = int(0.5 + (width * 9525)) + height = int(0.5 + (height * 9525)) + + # Create a Drawing obj to use with worksheet unless one already exists. + if not self.drawing: + drawing = Drawing() + drawing.embedded = 1 + self.drawing = drawing + + self.external_drawing_links.append(['/drawing', + '../drawings/drawing' + + str(drawing_id) + + '.xml', None]) + else: + drawing = self.drawing + + drawing_object = [drawing_type] + drawing_object.extend(dimensions) + drawing_object.extend([width, height, name, None, url, tip, anchor]) + + drawing._add_drawing_object(drawing_object) + + if url: + rel_type = "/hyperlink" + target_mode = "External" + + if re.match('(ftp|http)s?://', url): + target = url + + if re.match('external:', url): + target = url.replace('external:', '') + + if re.match("internal:", url): + target = url.replace('internal:', '#') + target_mode = None + + self.drawing_links.append([rel_type, target, target_mode]) + + self.drawing_links.append(['/image', + '../media/image' + + str(image_id) + '.' + + image_type]) + + def _prepare_shape(self, index, drawing_id): + # Set up shapes/drawings. + drawing_type = 3 + + (row, col, x_offset, y_offset, + x_scale, y_scale, text, options) = self.shapes[index] + + width = options.get('width', self.default_col_pixels * 3) + height = options.get('height', self.default_row_pixels * 6) + + width *= x_scale + height *= y_scale + + dimensions = self._position_object_emus(col, row, x_offset, y_offset, + width, height) + + # Convert from pixels to emus. + width = int(0.5 + (width * 9525)) + height = int(0.5 + (height * 9525)) + + # Create a Drawing obj to use with worksheet unless one already exists. + if not self.drawing: + drawing = Drawing() + drawing.embedded = 1 + self.drawing = drawing + + self.external_drawing_links.append(['/drawing', + '../drawings/drawing' + + str(drawing_id) + + '.xml', None]) + else: + drawing = self.drawing + + shape = Shape('rect', 'TextBox', options) + shape.text = text + + drawing_object = [drawing_type] + drawing_object.extend(dimensions) + drawing_object.extend([width, height, None, shape, None, + None, None]) + + drawing._add_drawing_object(drawing_object) + + def _prepare_header_image(self, image_id, width, height, name, image_type, + position, x_dpi, y_dpi): + # Set up an image without a drawing object for header/footer images. + + # Strip the extension from the filename. + name = re.sub('\..*$', '', name) + + self.header_images_list.append([width, height, name, position, + x_dpi, y_dpi]) + + self.vml_drawing_links.append(['/image', + '../media/image' + + str(image_id) + '.' + + image_type]) + + def _prepare_chart(self, index, chart_id, drawing_id): + # Set up chart/drawings. + drawing_type = 1 + + (row, col, chart, x_offset, y_offset, x_scale, y_scale) = \ + self.charts[index] + + chart.id = chart_id - 1 + + # Use user specified dimensions, if any. + width = int(0.5 + (chart.width * x_scale)) + height = int(0.5 + (chart.height * y_scale)) + + dimensions = self._position_object_emus(col, row, x_offset, y_offset, + width, height) + + # Set the chart name for the embedded object if it has been specified. + name = chart.chart_name + + # Create a Drawing obj to use with worksheet unless one already exists. + if not self.drawing: + drawing = Drawing() + drawing.embedded = 1 + self.drawing = drawing + + self.external_drawing_links.append(['/drawing', + '../drawings/drawing' + + str(drawing_id) + + '.xml']) + else: + drawing = self.drawing + + drawing_object = [drawing_type] + drawing_object.extend(dimensions) + drawing_object.extend([width, height, name, None]) + + drawing._add_drawing_object(drawing_object) + + self.drawing_links.append(['/chart', + '../charts/chart' + + str(chart_id) + + '.xml']) + + def _position_object_emus(self, col_start, row_start, x1, y1, + width, height): + # Calculate the vertices that define the position of a graphical + # object within the worksheet in EMUs. + # + # The vertices are expressed as English Metric Units (EMUs). There are + # 12,700 EMUs per point. Therefore, 12,700 * 3 /4 = 9,525 EMUs per + # pixel + (col_start, row_start, x1, y1, + col_end, row_end, x2, y2, x_abs, y_abs) = \ + self._position_object_pixels(col_start, row_start, x1, y1, + width, height) + + # Convert the pixel values to EMUs. See above. + x1 = int(0.5 + 9525 * x1) + y1 = int(0.5 + 9525 * y1) + x2 = int(0.5 + 9525 * x2) + y2 = int(0.5 + 9525 * y2) + x_abs = int(0.5 + 9525 * x_abs) + y_abs = int(0.5 + 9525 * y_abs) + + return (col_start, row_start, x1, y1, col_end, row_end, x2, y2, + x_abs, y_abs) + + # Calculate the vertices that define the position of a graphical object + # within the worksheet in pixels. + # + # +------------+------------+ + # | A | B | + # +-----+------------+------------+ + # | |(x1,y1) | | + # | 1 |(A1)._______|______ | + # | | | | | + # | | | | | + # +-----+----| OBJECT |-----+ + # | | | | | + # | 2 | |______________. | + # | | | (B2)| + # | | | (x2,y2)| + # +---- +------------+------------+ + # + # Example of an object that covers some of the area from cell A1 to B2. + # + # Based on the width and height of the object we need to calculate 8 vars: + # + # col_start, row_start, col_end, row_end, x1, y1, x2, y2. + # + # We also calculate the absolute x and y position of the top left vertex of + # the object. This is required for images. + # + # The width and height of the cells that the object occupies can be + # variable and have to be taken into account. + # + # The values of col_start and row_start are passed in from the calling + # function. The values of col_end and row_end are calculated by + # subtracting the width and height of the object from the width and + # height of the underlying cells. + # + def _position_object_pixels(self, col_start, row_start, x1, y1, + width, height): + # col_start # Col containing upper left corner of object. + # x1 # Distance to left side of object. + # + # row_start # Row containing top left corner of object. + # y1 # Distance to top of object. + # + # col_end # Col containing lower right corner of object. + # x2 # Distance to right side of object. + # + # row_end # Row containing bottom right corner of object. + # y2 # Distance to bottom of object. + # + # width # Width of object frame. + # height # Height of object frame. + # + # x_abs # Absolute distance to left side of object. + # y_abs # Absolute distance to top side of object. + x_abs = 0 + y_abs = 0 + + # Adjust start column for negative offsets. + while x1 < 0 and col_start > 0: + x1 += self._size_col(col_start - 1) + col_start -= 1 + + # Adjust start row for negative offsets. + while y1 < 0 and row_start > 0: + y1 += self._size_row(row_start - 1) + row_start -= 1 + + # Ensure that the image isn't shifted off the page at top left. + if x1 < 0: + x1 = 0 + + if y1 < 0: + y1 = 0 + + # Calculate the absolute x offset of the top-left vertex. + if self.col_size_changed: + for col_id in range(col_start): + x_abs += self._size_col(col_id) + else: + # Optimisation for when the column widths haven't changed. + x_abs += self.default_col_pixels * col_start + + x_abs += x1 + + # Calculate the absolute y offset of the top-left vertex. + if self.row_size_changed: + for row_id in range(row_start): + y_abs += self._size_row(row_id) + else: + # Optimisation for when the row heights haven't changed. + y_abs += self.default_row_pixels * row_start + + y_abs += y1 + + # Adjust start column for offsets that are greater than the col width. + while x1 >= self._size_col(col_start): + x1 -= self._size_col(col_start) + col_start += 1 + + # Adjust start row for offsets that are greater than the row height. + while y1 >= self._size_row(row_start): + y1 -= self._size_row(row_start) + row_start += 1 + + # Initialise end cell to the same as the start cell. + col_end = col_start + row_end = row_start + + width = width + x1 + height = height + y1 + + # Subtract the underlying cell widths to find end cell of the object. + while width >= self._size_col(col_end): + width -= self._size_col(col_end) + col_end += 1 + + # Subtract the underlying cell heights to find end cell of the object. + + while height >= self._size_row(row_end): + height -= self._size_row(row_end) + row_end += 1 + + # The end vertices are whatever is left from the width and height. + x2 = width + y2 = height + + return ([col_start, row_start, x1, y1, col_end, row_end, x2, y2, + x_abs, y_abs]) + + def _size_col(self, col): + # Convert the width of a cell from user's units to pixels. Excel rounds + # the column width to the nearest pixel. If the width hasn't been set + # by the user we use the default value. If the column is hidden it + # has a value of zero. + max_digit_width = 7 # For Calabri 11. + padding = 5 + pixels = 0 + + # Look up the cell value to see if it has been changed. + if col in self.col_sizes and self.col_sizes[col] is not None: + width = self.col_sizes[col] + + # Convert to pixels. + if width == 0: + pixels = 0 + elif width < 1: + pixels = int(width * (max_digit_width + padding) + 0.5) + else: + pixels = int(width * max_digit_width + 0.5) + padding + else: + pixels = self.default_col_pixels + + return pixels + + def _size_row(self, row): + # Convert the height of a cell from user's units to pixels. If the + # height hasn't been set by the user we use the default value. If + # the row is hidden it has a value of zero. + pixels = 0 + + # Look up the cell value to see if it has been changed + if row in self.row_sizes: + height = self.row_sizes[row] + + if height == 0: + pixels = 0 + else: + pixels = int(4.0 / 3.0 * height) + else: + pixels = int(4.0 / 3.0 * self.default_row_height) + + return pixels + + def _comment_params(self, row, col, string, options): + # This method handles the additional optional parameters to + # write_comment() as well as calculating the comment object + # position and vertices. + default_width = 128 + default_height = 74 + + params = { + 'author': None, + 'color': '#ffffe1', + 'start_cell': None, + 'start_col': None, + 'start_row': None, + 'visible': None, + 'width': default_width, + 'height': default_height, + 'x_offset': None, + 'x_scale': 1, + 'y_offset': None, + 'y_scale': 1, + } + + # Overwrite the defaults with any user supplied values. Incorrect or + # misspelled parameters are silently ignored. + for key in options.keys(): + params[key] = options[key] + + # Ensure that a width and height have been set. + if not params['width']: + params['width'] = default_width + if not params['height']: + params['height'] = default_height + + # Set the comment background color. + params['color'] = xl_color(params['color']).lower() + + # Convert from Excel XML style color to XML html style color. + params['color'] = params['color'].replace('ff', '#', 1) + + # Convert a cell reference to a row and column. + if params['start_cell'] is not None: + (start_row, start_col) = xl_cell_to_rowcol(params['start_cell']) + params['start_row'] = start_row + params['start_col'] = start_col + + # Set the default start cell and offsets for the comment. These are + # generally fixed in relation to the parent cell. However there are + # some edge cases for cells at the, er, edges. + row_max = self.xls_rowmax + col_max = self.xls_colmax + + if params['start_row'] is None: + if row == 0: + params['start_row'] = 0 + elif row == row_max - 3: + params['start_row'] = row_max - 7 + elif row == row_max - 2: + params['start_row'] = row_max - 6 + elif row == row_max - 1: + params['start_row'] = row_max - 5 + else: + params['start_row'] = row - 1 + + if params['y_offset'] is None: + if row == 0: + params['y_offset'] = 2 + elif row == row_max - 3: + params['y_offset'] = 16 + elif row == row_max - 2: + params['y_offset'] = 16 + elif row == row_max - 1: + params['y_offset'] = 14 + else: + params['y_offset'] = 10 + + if params['start_col'] is None: + if col == col_max - 3: + params['start_col'] = col_max - 6 + elif col == col_max - 2: + params['start_col'] = col_max - 5 + elif col == col_max - 1: + params['start_col'] = col_max - 4 + else: + params['start_col'] = col + 1 + + if params['x_offset'] is None: + if col == col_max - 3: + params['x_offset'] = 49 + elif col == col_max - 2: + params['x_offset'] = 49 + elif col == col_max - 1: + params['x_offset'] = 49 + else: + params['x_offset'] = 15 + + # Scale the size of the comment box if required. + if params['x_scale']: + params['width'] = params['width'] * params['x_scale'] + + if params['y_scale']: + params['height'] = params['height'] * params['y_scale'] + + # Round the dimensions to the nearest pixel. + params['width'] = int(0.5 + params['width']) + params['height'] = int(0.5 + params['height']) + + # Calculate the positions of the comment object. + vertices = self._position_object_pixels( + params['start_col'], params['start_row'], params['x_offset'], + params['y_offset'], params['width'], params['height']) + + # Add the width and height for VML. + vertices.append(params['width']) + vertices.append(params['height']) + + return ([row, col, string, params['author'], + params['visible'], params['color']] + [vertices]) + + def _button_params(self, row, col, options): + # This method handles the parameters passed to insert_button() as well + # as calculating the comment object position and vertices. + + default_height = self.default_row_pixels + default_width = self.default_col_pixels + + button_number = 1 + len(self.buttons_list) + button = {'row': row, 'col': col, 'font': {}} + params = {} + + # Overwrite the defaults with any user supplied values. Incorrect or + # misspelled parameters are silently ignored. + for key in options.keys(): + params[key] = options[key] + + # Set the button caption. + caption = params.get('caption') + + # Set a default caption if none was specified by user. + if caption is None: + caption = 'Button %d' % button_number + + button['font']['caption'] = caption + + # Set the macro name. + if params.get('macro'): + button['macro'] = '[0]!' + params['macro'] + else: + button['macro'] = '[0]!Button%d_Click' % button_number + + # Ensure that a width and height have been set. + params['width'] = params.get('width', default_width) + params['height'] = params.get('height', default_height) + + # Set the x/y offsets. + params['x_offset'] = params.get('x_offset', 0) + params['y_offset'] = params.get('y_offset', 0) + + # Scale the size of the button if required. + params['width'] = params['width'] * params.get('x_scale', 1) + params['height'] = params['height'] * params.get('y_scale', 1) + + # Round the dimensions to the nearest pixel. + params['width'] = int(0.5 + params['width']) + params['height'] = int(0.5 + params['height']) + + params['start_row'] = row + params['start_col'] = col + + # Calculate the positions of the button object. + vertices = self._position_object_pixels( + params['start_col'], params['start_row'], params['x_offset'], + params['y_offset'], params['width'], params['height']) + + # Add the width and height for VML. + vertices.append(params['width']) + vertices.append(params['height']) + + button['vertices'] = vertices + + return button + + def _prepare_vml_objects(self, vml_data_id, vml_shape_id, vml_drawing_id, + comment_id): + comments = [] + # Sort the comments into row/column order for easier comparison + # testing and set the external links for comments and buttons. + row_nums = sorted(self.comments.keys()) + + for row in row_nums: + col_nums = sorted(self.comments[row].keys()) + + for col in col_nums: + # Set comment visibility if required and not user defined. + if self.comments_visible: + if self.comments[row][col][4] is None: + self.comments[row][col][4] = 1 + + # Set comment author if not already user defined. + if self.comments[row][col][3] is None: + self.comments[row][col][3] = self.comments_author + + comments.append(self.comments[row][col]) + + self.external_vml_links.append(['/vmlDrawing', + '../drawings/vmlDrawing' + + str(vml_drawing_id) + + '.vml']) + + if self.has_comments: + self.comments_list = comments + + self.external_comment_links.append(['/comments', + '../comments' + + str(comment_id) + + '.xml']) + + count = len(comments) + start_data_id = vml_data_id + + # The VML o:idmap data id contains a comma separated range when there + # is more than one 1024 block of comments, like this: data="1,2". + for i in range(int(count / 1024)): + vml_data_id = '%s,%d' % (vml_data_id, start_data_id + i + 1) + + self.vml_data_id = vml_data_id + self.vml_shape_id = vml_shape_id + + return count + + def _prepare_header_vml_objects(self, vml_header_id, vml_drawing_id): + # Set up external linkage for VML header/footer images. + + self.vml_header_id = vml_header_id + + self.external_vml_links.append(['/vmlDrawing', + '../drawings/vmlDrawing' + + str(vml_drawing_id) + '.vml']) + + def _prepare_tables(self, table_id, seen): + # Set the table ids for the worksheet tables. + for table in self.tables: + table['id'] = table_id + + if table.get('name') is None: + # Set a default name. + table['name'] = 'Table' + str(table_id) + + # Check for duplicate table names. + name = table['name'].lower() + + if name in seen: + raise Exception("invalid duplicate table name '%s' found." % + table['name']) + else: + seen[name] = True + + # Store the link used for the rels file. + self.external_table_links.append(['/table', + '../tables/table' + + str(table_id) + + '.xml']) + table_id += 1 + + def _table_function_to_formula(self, function, col_name): + # Convert a table total function to a worksheet formula. + formula = '' + + subtotals = { + 'average': 101, + 'countNums': 102, + 'count': 103, + 'max': 104, + 'min': 105, + 'stdDev': 107, + 'sum': 109, + 'var': 110, + } + + if function in subtotals: + func_num = subtotals[function] + formula = "SUBTOTAL(%s,[%s])" % (func_num, col_name) + else: + warn("Unsupported function '%s' in add_table()" % function) + + return formula + + def _set_spark_color(self, sparkline, options, user_color): + # Set the sparkline color. + if user_color not in options: + return + + sparkline[user_color] = {'rgb': xl_color(options[user_color])} + + def _get_range_data(self, row_start, col_start, row_end, col_end): + # Returns a range of data from the worksheet _table to be used in + # chart cached data. Strings are returned as SST ids and decoded + # in the workbook. Return None for data that doesn't exist since + # Excel can chart series with data missing. + + if self.optimization: + return () + + data = [] + + # Iterate through the table data. + for row_num in range(row_start, row_end + 1): + # Store None if row doesn't exist. + if row_num not in self.table: + data.append(None) + continue + + for col_num in range(col_start, col_end + 1): + + if col_num in self.table[row_num]: + cell = self.table[row_num][col_num] + + if type(cell).__name__ == 'Number': + # Return a number with Excel's precision. + data.append("%.15g" % cell.number) + + elif type(cell).__name__ == 'String': + # Return a string from it's shared string index. + index = cell.string + string = self.str_table._get_shared_string(index) + + data.append(string) + + elif (type(cell).__name__ == 'Formula' + or type(cell).__name__ == 'ArrayFormula'): + # Return the formula value. + value = cell.value + + if value is None: + value = 0 + + data.append(value) + + elif type(cell).__name__ == 'Blank': + # Return a empty cell. + data.append('') + else: + + # Store None if column doesn't exist. + data.append(None) + + return data + + def _csv_join(self, *items): + # Create a csv string for use with data validation formulas and lists. + + # Convert non string types to string. + items = [str(item) if not isinstance(item, str_types) else item + for item in items] + + return ','.join(items) + + ########################################################################### + # + # The following font methods are, more or less, duplicated from the + # Styles class. Not the cleanest version of reuse but works for now. + # + ########################################################################### + def _write_font(self, xf_format): + # Write the element. + xml_writer = self.rstring + + xml_writer._xml_start_tag('rPr') + + # Handle the main font properties. + if xf_format.bold: + xml_writer._xml_empty_tag('b') + if xf_format.italic: + xml_writer._xml_empty_tag('i') + if xf_format.font_strikeout: + xml_writer._xml_empty_tag('strike') + if xf_format.font_outline: + xml_writer._xml_empty_tag('outline') + if xf_format.font_shadow: + xml_writer._xml_empty_tag('shadow') + + # Handle the underline variants. + if xf_format.underline: + self._write_underline(xf_format.underline) + + # Handle super/subscript. + if xf_format.font_script == 1: + self._write_vert_align('superscript') + if xf_format.font_script == 2: + self._write_vert_align('subscript') + + # Write the font size + xml_writer._xml_empty_tag('sz', [('val', xf_format.font_size)]) + + # Handle colors. + if xf_format.theme: + self._write_color('theme', xf_format.theme) + elif xf_format.color_indexed: + self._write_color('indexed', xf_format.color_indexed) + elif xf_format.font_color: + color = self._get_palette_color(xf_format.font_color) + self._write_rstring_color('rgb', color) + else: + self._write_rstring_color('theme', 1) + + # Write some other font properties related to font families. + xml_writer._xml_empty_tag('rFont', [('val', xf_format.font_name)]) + xml_writer._xml_empty_tag('family', [('val', xf_format.font_family)]) + + if xf_format.font_name == 'Calibri' and not xf_format.hyperlink: + xml_writer._xml_empty_tag('scheme', + [('val', xf_format.font_scheme)]) + + xml_writer._xml_end_tag('rPr') + + def _write_underline(self, underline): + # Write the underline font element. + attributes = [] + + # Handle the underline variants. + if underline == 2: + attributes = [('val', 'double')] + elif underline == 33: + attributes = [('val', 'singleAccounting')] + elif underline == 34: + attributes = [('val', 'doubleAccounting')] + + self.rstring._xml_empty_tag('u', attributes) + + def _write_vert_align(self, val): + # Write the font sub-element. + attributes = [('val', val)] + + self.rstring._xml_empty_tag('vertAlign', attributes) + + def _write_rstring_color(self, name, value): + # Write the element. + attributes = [(name, value)] + + self.rstring._xml_empty_tag('color', attributes) + + def _get_palette_color(self, color): + # Convert the RGB color. + if color[0] == '#': + color = color[1:] + + return "FF" + color.upper() + + def _isnan(self, x): + # Workaround for lack of math.isnan in Python 2.5/Jython. + return x != x + + def _isinf(self, x): + # Workaround for lack of math.isinf in Python 2.5/Jython. + return (x - x) != 0 + + def _opt_close(self): + # Close the row data filehandle in optimization mode. + if not self.row_data_fh_closed: + self.row_data_fh.close() + self.row_data_fh_closed = True + + def _opt_reopen(self): + # Reopen the row data filehandle in optimization mode. + if self.row_data_fh_closed: + filename = self.row_data_filename + self.row_data_fh = codecs.open(filename, 'a+', 'utf-8') + self.row_data_fh_closed = False + self.fh = self.row_data_fh + + ########################################################################### + # + # XML methods. + # + ########################################################################### + + def _write_worksheet(self): + # Write the element. This is the root element. + + schema = 'http://schemas.openxmlformats.org/' + xmlns = schema + 'spreadsheetml/2006/main' + xmlns_r = schema + 'officeDocument/2006/relationships' + xmlns_mc = schema + 'markup-compatibility/2006' + ms_schema = 'http://schemas.microsoft.com/' + xmlns_x14ac = ms_schema + 'office/spreadsheetml/2009/9/ac' + + attributes = [ + ('xmlns', xmlns), + ('xmlns:r', xmlns_r)] + + # Add some extra attributes for Excel 2010. Mainly for sparklines. + if self.excel_version == 2010: + attributes.append(('xmlns:mc', xmlns_mc)) + attributes.append(('xmlns:x14ac', xmlns_x14ac)) + attributes.append(('mc:Ignorable', 'x14ac')) + + self._xml_start_tag('worksheet', attributes) + + def _write_dimension(self): + # Write the element. This specifies the range of + # cells in the worksheet. As a special case, empty + # spreadsheets use 'A1' as a range. + + if self.dim_rowmin is None and self.dim_colmin is None: + # If the min dimensions are not defined then no dimensions + # have been set and we use the default 'A1'. + ref = 'A1' + + elif self.dim_rowmin is None and self.dim_colmin is not None: + # If the row dimensions aren't set but the column + # dimensions are set then they have been changed via + # set_column(). + + if self.dim_colmin == self.dim_colmax: + # The dimensions are a single cell and not a range. + ref = xl_rowcol_to_cell(0, self.dim_colmin) + else: + # The dimensions are a cell range. + cell_1 = xl_rowcol_to_cell(0, self.dim_colmin) + cell_2 = xl_rowcol_to_cell(0, self.dim_colmax) + ref = cell_1 + ':' + cell_2 + + elif (self.dim_rowmin == self.dim_rowmax and + self.dim_colmin == self.dim_colmax): + # The dimensions are a single cell and not a range. + ref = xl_rowcol_to_cell(self.dim_rowmin, self.dim_colmin) + else: + # The dimensions are a cell range. + cell_1 = xl_rowcol_to_cell(self.dim_rowmin, self.dim_colmin) + cell_2 = xl_rowcol_to_cell(self.dim_rowmax, self.dim_colmax) + ref = cell_1 + ':' + cell_2 + + self._xml_empty_tag('dimension', [('ref', ref)]) + + def _write_sheet_views(self): + # Write the element. + self._xml_start_tag('sheetViews') + + # Write the sheetView element. + self._write_sheet_view() + + self._xml_end_tag('sheetViews') + + def _write_sheet_view(self): + # Write the element. + attributes = [] + + # Hide screen gridlines if required + if not self.screen_gridlines: + attributes.append(('showGridLines', 0)) + + # Hide zeroes in cells. + if not self.show_zeros: + attributes.append(('showZeros', 0)) + + # Display worksheet right to left for Hebrew, Arabic and others. + if self.is_right_to_left: + attributes.append(('rightToLeft', 1)) + + # Show that the sheet tab is selected. + if self.selected: + attributes.append(('tabSelected', 1)) + + # Turn outlines off. Also required in the outlinePr element. + if not self.outline_on: + attributes.append(("showOutlineSymbols", 0)) + + # Set the page view/layout mode if required. + if self.page_view: + attributes.append(('view', 'pageLayout')) + + # Set the zoom level. + if self.zoom != 100: + if not self.page_view: + attributes.append(('zoomScale', self.zoom)) + if self.zoom_scale_normal: + attributes.append(('zoomScaleNormal', self.zoom)) + + attributes.append(('workbookViewId', 0)) + + if self.panes or len(self.selections): + self._xml_start_tag('sheetView', attributes) + self._write_panes() + self._write_selections() + self._xml_end_tag('sheetView') + else: + self._xml_empty_tag('sheetView', attributes) + + def _write_sheet_format_pr(self): + # Write the element. + default_row_height = self.default_row_height + row_level = self.outline_row_level + col_level = self.outline_col_level + + attributes = [('defaultRowHeight', default_row_height)] + + if self.default_row_height != self.original_row_height: + attributes.append(('customHeight', 1)) + + if self.default_row_zeroed: + attributes.append(('zeroHeight', 1)) + + if row_level: + attributes.append(('outlineLevelRow', row_level)) + if col_level: + attributes.append(('outlineLevelCol', col_level)) + + if self.excel_version == 2010: + attributes.append(('x14ac:dyDescent', '0.25')) + + self._xml_empty_tag('sheetFormatPr', attributes) + + def _write_cols(self): + # Write the element and