Пример #1
0
def write_worksheet(worksheet, string_table, style_table):
    """Write a worksheet to an xml file."""
    xml_file = StringIO()
    doc = XMLGenerator(xml_file, 'utf-8')
    start_tag(doc, 'worksheet',
            {'xml:space': 'preserve',
            'xmlns': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
            'xmlns:r': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'})
    start_tag(doc, 'sheetPr')
    tag(doc, 'outlinePr',
            {'summaryBelow': '%d' % (worksheet.show_summary_below),
            'summaryRight': '%d' % (worksheet.show_summary_right)})
    end_tag(doc, 'sheetPr')
    tag(doc, 'dimension', {'ref': '%s' % worksheet.calculate_dimension()})
    write_worksheet_sheetviews(doc, worksheet)
    tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'})
    write_worksheet_cols(doc, worksheet)
    write_worksheet_data(doc, worksheet, string_table, style_table)
    if worksheet.auto_filter:
        tag(doc, 'autoFilter', {'ref': worksheet.auto_filter})
    write_worksheet_hyperlinks(doc, worksheet)
    if worksheet._charts:
        tag(doc, 'drawing', {'r:id':'rId1'})
    end_tag(doc, 'worksheet')
    doc.endDocument()
    xml_string = xml_file.getvalue()
    xml_file.close()
    return xml_string
Пример #2
0
def write_worksheet_sheetviews(doc, worksheet):
    start_tag(doc, 'sheetViews')
    start_tag(doc, 'sheetView', {'workbookViewId': '0'})
    selectionAttrs = {}
    topLeftCell = worksheet.freeze_panes
    if topLeftCell:
        colName, row = coordinate_from_string(topLeftCell)
        column = column_index_from_string(colName)
        pane = 'topRight'
        paneAttrs = {}
        if column > 1:
            paneAttrs['xSplit'] = str(column - 1)
        if row > 1:
            paneAttrs['ySplit'] = str(row - 1)
            pane = 'bottomLeft'
            if column > 1:
                pane = 'bottomRight'
        paneAttrs.update(dict(topLeftCell=topLeftCell,
                              activePane=pane,
                              state='frozen'))
        tag(doc, 'pane', paneAttrs)
        selectionAttrs['pane'] = pane
        if row > 1 and column > 1:
            tag(doc, 'selection', {'pane': 'topRight'})
            tag(doc, 'selection', {'pane': 'bottomLeft'})

    selectionAttrs.update({'activeCell': worksheet.active_cell,
                           'sqref': worksheet.selected_cell})

    tag(doc, 'selection', selectionAttrs)
    end_tag(doc, 'sheetView')
    end_tag(doc, 'sheetViews')
Пример #3
0
def write_worksheet_cols(doc, worksheet, style_table):
    """Write worksheet columns to xml."""
    if worksheet.column_dimensions:
        start_tag(doc, 'cols')
        for column_string, columndimension in \
                iteritems(worksheet.column_dimensions):
            col_index = column_index_from_string(column_string)
            col_def = {'min': str(col_index), 'max': str(col_index)}
            if columndimension.width != -1:
                col_def['customWidth'] = '1'
            if not columndimension.visible:
                col_def['hidden'] = 'true'
            if columndimension.outline_level > 0:
                col_def['outlineLevel'] = str(columndimension.outline_level)
            if columndimension.collapsed:
                col_def['collapsed'] = 'true'
            if columndimension.auto_size:
                col_def['bestFit'] = 'true'
            if columndimension.style_index:
                col_def['style'] = str(style_table[hash(columndimension.style_index)])
            if columndimension.width > 0:
                col_def['width'] = str(columndimension.width)
            else:
                col_def['width'] = '9.10'
            tag(doc, 'col', col_def)
        end_tag(doc, 'cols')
Пример #4
0
def write_worksheet(worksheet, string_table, style_table):
    """Write a worksheet to an xml file."""
    xml_file = StringIO()
    doc = XMLGenerator(xml_file, "utf-8")
    start_tag(
        doc,
        "worksheet",
        {
            "xml:space": "preserve",
            "xmlns": "http://schemas.openxmlformats.org/spreadsheetml/2006/main",
            "xmlns:r": "http://schemas.openxmlformats.org/officeDocument/2006/relationships",
        },
    )
    start_tag(doc, "sheetPr")
    tag(
        doc,
        "outlinePr",
        {"summaryBelow": "%d" % (worksheet.show_summary_below), "summaryRight": "%d" % (worksheet.show_summary_right)},
    )
    end_tag(doc, "sheetPr")
    tag(doc, "dimension", {"ref": "%s" % worksheet.calculate_dimension()})
    write_worksheet_sheetviews(doc, worksheet)
    tag(doc, "sheetFormatPr", {"defaultRowHeight": "15"})
    write_worksheet_cols(doc, worksheet)
    write_worksheet_data(doc, worksheet, string_table, style_table)
    if worksheet.auto_filter:
        tag(doc, "autoFilter", {"ref": worksheet.auto_filter})
    write_worksheet_hyperlinks(doc, worksheet)
    if worksheet._charts:
        tag(doc, "drawing", {"r:id": "rId1"})
    end_tag(doc, "worksheet")
    doc.endDocument()
    xml_string = xml_file.getvalue()
    xml_file.close()
    return xml_string
Пример #5
0
def write_worksheet_sheetviews(doc, worksheet):
    start_tag(doc, "sheetViews")
    start_tag(doc, "sheetView", {"workbookViewId": "0"})
    selectionAttrs = {}
    topLeftCell = worksheet.freeze_panes
    if topLeftCell:
        colName, row = coordinate_from_string(topLeftCell)
        column = column_index_from_string(colName)
        pane = "topRight"
        paneAttrs = {}
        if column > 1:
            paneAttrs["xSplit"] = str(column - 1)
        if row > 1:
            paneAttrs["ySplit"] = str(row - 1)
            pane = "bottomLeft"
            if column > 1:
                pane = "bottomRight"
        paneAttrs.update(dict(topLeftCell=topLeftCell, activePane=pane, state="frozen"))
        tag(doc, "pane", paneAttrs)
        selectionAttrs["pane"] = pane
        if row > 1 and column > 1:
            tag(doc, "selection", {"pane": "topRight"})
            tag(doc, "selection", {"pane": "bottomLeft"})

    selectionAttrs.update({"activeCell": worksheet.active_cell, "sqref": worksheet.selected_cell})

    tag(doc, "selection", selectionAttrs)
    end_tag(doc, "sheetView")
    end_tag(doc, "sheetViews")
Пример #6
0
def write_worksheet_cols(doc, worksheet):
    """Write worksheet columns to xml."""
    if worksheet.column_dimensions:
        start_tag(doc, 'cols')
        for column_string, columndimension in \
                worksheet.column_dimensions.iteritems():
            col_index = column_index_from_string(column_string)
            col_def = {}
            col_def['collapsed'] = str(columndimension.style_index)
            col_def['min'] = str(col_index)
            col_def['max'] = str(col_index)
            if columndimension.width != \
                    worksheet.default_column_dimension.width:
                col_def['customWidth'] = 'true'
            if not columndimension.visible:
                col_def['hidden'] = 'true'
            if columndimension.outline_level > 0:
                col_def['outlineLevel'] = str(columndimension.outline_level)
            if columndimension.collapsed:
                col_def['collapsed'] = 'true'
            if columndimension.auto_size:
                col_def['bestFit'] = 'true'
            if columndimension.width > 0:
                col_def['width'] = str(columndimension.width)
            else:
                col_def['width'] = '9.10'
            tag(doc, 'col', col_def)
        end_tag(doc, 'cols')
Пример #7
0
    def write_header(self):

        fobj = get_temporary_file(filename=self._fileobj_header_name)
        doc = XMLGenerator(fobj, "utf-8")

        start_tag(
            doc,
            "worksheet",
            {
                "xml:space": "preserve",
                "xmlns": "http://schemas.openxmlformats.org/spreadsheetml/2006/main",
                "xmlns:r": "http://schemas.openxmlformats.org/officeDocument/2006/relationships",
            },
        )
        start_tag(doc, "sheetPr")
        tag(doc, "outlinePr", {"summaryBelow": "1", "summaryRight": "1"})
        end_tag(doc, "sheetPr")
        tag(doc, "dimension", {"ref": "A1:%s" % (self.get_dimensions())})
        start_tag(doc, "sheetViews")
        start_tag(doc, "sheetView", {"workbookViewId": "0"})
        tag(doc, "selection", {"activeCell": "A1", "sqref": "A1"})
        end_tag(doc, "sheetView")
        end_tag(doc, "sheetViews")
        tag(doc, "sheetFormatPr", {"defaultRowHeight": "15"})
        start_tag(doc, "sheetData")
Пример #8
0
def write_worksheet(worksheet, string_table, style_table):
    """Write a worksheet to an xml file."""
    xml_file = StringIO()
    doc = XMLGenerator(xml_file, 'utf-8')
    start_tag(
        doc, 'worksheet', {
            'xml:space':
            'preserve',
            'xmlns':
            'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
            'xmlns:r':
            'http://schemas.openxmlformats.org/officeDocument/2006/relationships'
        })
    start_tag(doc, 'sheetPr')
    tag(
        doc, 'outlinePr', {
            'summaryBelow': '%d' % (worksheet.show_summary_below),
            'summaryRight': '%d' % (worksheet.show_summary_right)
        })
    end_tag(doc, 'sheetPr')
    tag(doc, 'dimension', {'ref': '%s' % worksheet.calculate_dimension()})
    write_worksheet_sheetviews(doc, worksheet)
    tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'})
    write_worksheet_cols(doc, worksheet)
    write_worksheet_data(doc, worksheet, string_table, style_table)
    if worksheet.auto_filter:
        tag(doc, 'autoFilter', {'ref': worksheet.auto_filter})
    write_worksheet_hyperlinks(doc, worksheet)
    if worksheet._charts:
        tag(doc, 'drawing', {'r:id': 'rId1'})
    end_tag(doc, 'worksheet')
    doc.endDocument()
    xml_string = xml_file.getvalue()
    xml_file.close()
    return xml_string
Пример #9
0
def write_worksheet_cols(doc, worksheet):
    """Write worksheet columns to xml."""
    if worksheet.column_dimensions:
        start_tag(doc, "cols")
        for column_string, columndimension in worksheet.column_dimensions.iteritems():
            col_index = column_index_from_string(column_string)
            col_def = {}
            col_def["collapsed"] = str(columndimension.style_index)
            col_def["min"] = str(col_index)
            col_def["max"] = str(col_index)
            if columndimension.width != worksheet.default_column_dimension.width:
                col_def["customWidth"] = "true"
            if not columndimension.visible:
                col_def["hidden"] = "true"
            if columndimension.outline_level > 0:
                col_def["outlineLevel"] = str(columndimension.outline_level)
            if columndimension.collapsed:
                col_def["collapsed"] = "true"
            if columndimension.auto_size:
                col_def["bestFit"] = "true"
            if columndimension.width > 0:
                col_def["width"] = str(columndimension.width)
            else:
                col_def["width"] = "9.10"
            tag(doc, "col", col_def)
        end_tag(doc, "cols")
Пример #10
0
    def write_header(self):

        fobj = get_temporary_file(filename=self._fileobj_header_name)
        doc = XMLGenerator(fobj, 'utf-8')

        start_tag(
            doc, 'worksheet', {
                'xml:space':
                'preserve',
                'xmlns':
                'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
                'xmlns:r':
                'http://schemas.openxmlformats.org/officeDocument/2006/relationships'
            })
        start_tag(doc, 'sheetPr')
        tag(doc, 'outlinePr', {'summaryBelow': '1', 'summaryRight': '1'})
        end_tag(doc, 'sheetPr')
        tag(doc, 'dimension', {'ref': 'A1:%s' % (self.get_dimensions())})
        start_tag(doc, 'sheetViews')
        start_tag(doc, 'sheetView', {'workbookViewId': '0'})
        tag(doc, 'selection', {'activeCell': 'A1', 'sqref': 'A1'})
        end_tag(doc, 'sheetView')
        end_tag(doc, 'sheetViews')
        tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'})
        start_tag(doc, 'sheetData')
Пример #11
0
def write_worksheet_cols(doc, worksheet, style_table):
    """Write worksheet columns to xml."""
    if worksheet.column_dimensions:
        start_tag(doc, 'cols')
        for column_string, columndimension in \
                sorted(iteritems(worksheet.column_dimensions)):
            col_index = column_index_from_string(column_string)
            col_def = {'min': str(col_index), 'max': str(col_index)}
            if columndimension.width != -1:
                col_def['customWidth'] = '1'
            if not columndimension.visible:
                col_def['hidden'] = 'true'
            if columndimension.outline_level > 0:
                col_def['outlineLevel'] = str(columndimension.outline_level)
            if columndimension.collapsed:
                col_def['collapsed'] = 'true'
            if columndimension.auto_size:
                col_def['bestFit'] = 'true'
            if columndimension.style_index and hash(
                    columndimension.style_index) in style_table:
                col_def['style'] = str(style_table[hash(
                    columndimension.style_index)])
            if columndimension.width > 0:
                col_def['width'] = str(columndimension.width)
            else:
                col_def['width'] = '9.10'
            tag(doc, 'col', col_def)
        end_tag(doc, 'cols')
Пример #12
0
def write_worksheet_cols(doc, worksheet):
    """Write worksheet columns to xml."""
    if worksheet.column_dimensions:
        start_tag(doc, 'cols')
        for column_string, columndimension in \
                worksheet.column_dimensions.iteritems():
            col_index = column_index_from_string(column_string)
            col_def = {}
            col_def['collapsed'] = str(columndimension.style_index)
            col_def['min'] = str(col_index)
            col_def['max'] = str(col_index)
            if columndimension.width != \
                    worksheet.default_column_dimension.width:
                col_def['customWidth'] = 'true'
            if not columndimension.visible:
                col_def['hidden'] = 'true'
            if columndimension.outline_level > 0:
                col_def['outlineLevel'] = str(columndimension.outline_level)
            if columndimension.collapsed:
                col_def['collapsed'] = 'true'
            if columndimension.auto_size:
                col_def['bestFit'] = 'true'
            if columndimension.width > 0:
                col_def['width'] = str(columndimension.width)
            else:
                col_def['width'] = '9.10'
            tag(doc, 'col', col_def)
        end_tag(doc, 'cols')
Пример #13
0
def write_worksheet_sheetviews(doc, worksheet):
    start_tag(doc, 'sheetViews')
    start_tag(doc, 'sheetView', {'workbookViewId': '0'})
    selectionAttrs = {}
    topLeftCell = worksheet.freeze_panes
    if topLeftCell:
        colName, row = coordinate_from_string(topLeftCell)
        column = column_index_from_string(colName)
        pane = 'topRight'
        paneAttrs = {}
        if column > 1:
            paneAttrs['xSplit'] = str(column - 1)
        if row > 1:
            paneAttrs['ySplit'] = str(row - 1)
            pane = 'bottomLeft'
            if column > 1:
                pane = 'bottomRight'
        paneAttrs.update(
            dict(topLeftCell=topLeftCell, activePane=pane, state='frozen'))
        tag(doc, 'pane', paneAttrs)
        selectionAttrs['pane'] = pane
        if row > 1 and column > 1:
            tag(doc, 'selection', {'pane': 'topRight'})
            tag(doc, 'selection', {'pane': 'bottomLeft'})

    selectionAttrs.update({
        'activeCell': worksheet.active_cell,
        'sqref': worksheet.selected_cell
    })

    tag(doc, 'selection', selectionAttrs)
    end_tag(doc, 'sheetView')
    end_tag(doc, 'sheetViews')
Пример #14
0
    def append(self, row):
        """
        :param row: iterable containing values to append
        :type row: iterable
        """

        doc = self._get_content_generator()

        self._max_row += 1
        span = len(row)
        self._max_col = max(self._max_col, span)

        row_idx = self._max_row

        attrs = {'r': '%d' % row_idx, 'spans': '1:%d' % span}

        start_tag(doc, 'row', attrs)

        for col_idx, cell in enumerate(row):

            if cell is None:
                continue

            coordinate = '%s%d' % (get_column_letter(col_idx + 1), row_idx)
            attributes = {'r': coordinate}

            if isinstance(cell, bool):
                dtype = 'boolean'
            elif isinstance(cell, NUMERIC_TYPES):
                dtype = 'numeric'
            elif isinstance(cell, datetime.datetime):
                dtype = 'datetime'
                cell = self._shared_date.datetime_to_julian(cell)
                attributes['s'] = STYLES[dtype]['style']
            elif isinstance(cell, datetime.date):
                dtype = 'date'
                cell = self._shared_date.datetime_to_julian(cell)
                attributes['s'] = STYLES[dtype]['style']
            elif cell and cell[0] == '=':
                dtype = 'formula'
            else:
                dtype = 'string'
                cell = self._string_builder.add(cell)

            attributes['t'] = STYLES[dtype]['type']

            start_tag(doc, 'c', attributes)

            if dtype == 'formula':
                tag(doc, 'f', body='%s' % cell[1:])
                tag(doc, 'v')
            elif dtype == 'boolean':
                tag(doc, 'v', body='%d' % cell)
            else:
                tag(doc, 'v', body='%s' % cell)

            end_tag(doc, 'c')

        end_tag(doc, 'row')
Пример #15
0
def write_worksheet_mergecells(doc, worksheet):
    """Write merged cells to xml."""
    if len(worksheet._merged_cells) > 0:
        start_tag(doc, 'mergeCells')
        for range_string in worksheet._merged_cells:
            attrs = {'ref': range_string}
            tag(doc, 'mergeCell', attrs)
        end_tag(doc, 'mergeCells')
Пример #16
0
def write_worksheet_mergecells(doc, worksheet):
    """Write merged cells to xml."""
    if len(worksheet._merged_cells) > 0:
        start_tag(doc, 'mergeCells', {'count': str(len(worksheet._merged_cells))})
        for range_string in worksheet._merged_cells:
            attrs = {'ref': range_string}
            tag(doc, 'mergeCell', attrs)
        end_tag(doc, 'mergeCells')
Пример #17
0
    def append(self, row):

        """
        :param row: iterable containing values to append
        :type row: iterable
        """

        doc = self.doc

        self._max_row += 1
        span = len(row)
        self._max_col = max(self._max_col, span)

        row_idx = self._max_row

        attrs = {'r': '%d' % row_idx,
                 'spans': '1:%d' % span}

        start_tag(doc, 'row', attrs)

        for col_idx, cell in enumerate(row):

            if cell is None:
                continue

            coordinate = '%s%d' % (get_column_letter(col_idx+1), row_idx) 
            attributes = {'r': coordinate}

            if isinstance(cell, bool):
                dtype = 'boolean'
            elif isinstance(cell, NUMERIC_TYPES):
                dtype = 'numeric'
            elif isinstance(cell, (datetime.datetime, datetime.date)):
                dtype = 'datetime'
                cell = self._shared_date.datetime_to_julian(cell)
                attributes['s'] = STYLES[dtype]['style']
            elif cell and cell[0] == '=':
                dtype = 'formula'
            else:
                dtype = 'string'
                cell = self._string_builder.add(cell)

            attributes['t'] = STYLES[dtype]['type']

            start_tag(doc, 'c', attributes)

            if dtype == 'formula':
                tag(doc, 'f', body = '%s' % cell[1:])
                tag(doc, 'v')
            elif dtype == 'boolean':
                tag(doc, 'v', body = '%d' % cell)
            else:
                tag(doc, 'v', body = '%s' % cell)
            
            end_tag(doc, 'c')


        end_tag(doc, 'row')
Пример #18
0
    def append(self, row):

        """
        :param row: iterable containing values to append
        :type row: iterable
        """

        doc = self._get_content_generator()

        self._max_row += 1
        span = len(row)
        self._max_col = max(self._max_col, span)

        row_idx = self._max_row

        attrs = {"r": "%d" % row_idx, "spans": "1:%d" % span}

        start_tag(doc, "row", attrs)

        for col_idx, cell in enumerate(row):

            if cell is None:
                continue

            coordinate = "%s%d" % (get_column_letter(col_idx + 1), row_idx)
            attributes = {"r": coordinate}

            if isinstance(cell, bool):
                dtype = "boolean"
            elif isinstance(cell, NUMERIC_TYPES):
                dtype = "numeric"
            elif isinstance(cell, (datetime.datetime, datetime.date)):
                dtype = "datetime"
                cell = self._shared_date.datetime_to_julian(cell)
                attributes["s"] = STYLES[dtype]["style"]
            elif cell and cell[0] == "=":
                dtype = "formula"
            else:
                dtype = "string"
                cell = self._string_builder.add(cell)

            attributes["t"] = STYLES[dtype]["type"]

            start_tag(doc, "c", attributes)

            if dtype == "formula":
                tag(doc, "f", body="%s" % cell[1:])
                tag(doc, "v")
            elif dtype == "boolean":
                tag(doc, "v", body="%d" % cell)
            else:
                tag(doc, "v", body="%s" % cell)

            end_tag(doc, "c")

        end_tag(doc, "row")
Пример #19
0
def write_worksheet_hyperlinks(doc, worksheet):
    """Write worksheet hyperlinks to xml."""
    write_hyperlinks = False
    for cell in worksheet.get_cell_collection():
        if cell.hyperlink_rel_id is not None:
            write_hyperlinks = True
            break
    if write_hyperlinks:
        start_tag(doc, "hyperlinks")
        for cell in worksheet.get_cell_collection():
            if cell.hyperlink_rel_id is not None:
                attrs = {"display": cell.hyperlink, "ref": cell.get_coordinate(), "r:id": cell.hyperlink_rel_id}
                tag(doc, "hyperlink", attrs)
        end_tag(doc, "hyperlinks")
Пример #20
0
def write_worksheet_hyperlinks(doc, worksheet):
    """Write worksheet hyperlinks to xml."""
    write_hyperlinks = False
    for cell in worksheet.get_cell_collection():
        if cell.hyperlink_rel_id is not None:
            write_hyperlinks = True
            break
    if write_hyperlinks:
        start_tag(doc, 'hyperlinks')
        for cell in worksheet.get_cell_collection():
            if cell.hyperlink_rel_id is not None:
                attrs = {'display': cell.hyperlink,
                        'ref': cell.get_coordinate(),
                        'r:id': cell.hyperlink_rel_id}
                tag(doc, 'hyperlink', attrs)
        end_tag(doc, 'hyperlinks')
Пример #21
0
def write_worksheet_hyperlinks(doc, worksheet):
    """Write worksheet hyperlinks to xml."""
    write_hyperlinks = False
    for cell in worksheet.get_cell_collection():
        if cell.hyperlink_rel_id is not None:
            write_hyperlinks = True
            break
    if write_hyperlinks:
        start_tag(doc, 'hyperlinks')
        for cell in worksheet.get_cell_collection():
            if cell.hyperlink_rel_id is not None:
                attrs = {'display': cell.hyperlink,
                        'ref': cell.get_coordinate(),
                        'r:id': cell.hyperlink_rel_id}
                tag(doc, 'hyperlink', attrs)
        end_tag(doc, 'hyperlinks')
Пример #22
0
def write_worksheet_datavalidations(doc, worksheet):
    """ Write data validation(s) to xml."""
    # Filter out "empty" data-validation objects (i.e. with 0 cells)
    required_dvs = [x for x in worksheet._data_validations
                    if len(x.cells) or len(x.ranges)]
    count = len(required_dvs)
    if count == 0:
        return

    start_tag(doc, 'dataValidations', {'count': str(count)})
    for data_validation in required_dvs:
        start_tag(doc, 'dataValidation', data_validation.generate_attributes_map())
        if data_validation.formula1:
            tag(doc, 'formula1', body=data_validation.formula1)
        if data_validation.formula2:
            tag(doc, 'formula2', body=data_validation.formula2)
        end_tag(doc, 'dataValidation')
    end_tag(doc, 'dataValidations')
Пример #23
0
def write_worksheet_datavalidations(doc, worksheet):
    """ Write data validation(s) to xml."""
    # Filter out "empty" data-validation objects (i.e. with 0 cells)
    required_dvs = [
        x for x in worksheet._data_validations if len(x.cells) or len(x.ranges)
    ]
    count = len(required_dvs)
    if count == 0:
        return

    start_tag(doc, 'dataValidations', {'count': str(count)})
    for data_validation in required_dvs:
        start_tag(doc, 'dataValidation',
                  data_validation.generate_attributes_map())
        if data_validation.formula1:
            tag(doc, 'formula1', body=data_validation.formula1)
        if data_validation.formula2:
            tag(doc, 'formula2', body=data_validation.formula2)
        end_tag(doc, 'dataValidation')
    end_tag(doc, 'dataValidations')
Пример #24
0
def write_worksheet_conditional_formatting(doc, worksheet):
    """Write conditional formatting to xml."""
    for range_string, rules in iteritems(
            worksheet.conditional_formatting.cf_rules):
        if not len(rules):
            # Skip if there are no rules.  This is possible if a dataBar rule was read in and ignored.
            continue
        start_tag(doc, 'conditionalFormatting', {'sqref': range_string})
        for rule in rules:
            if rule['type'] == 'dataBar':
                # Ignore - uses extLst tag which is currently unsupported.
                continue
            attr = {'type': rule['type']}
            for rule_attr in ConditionalFormatting.rule_attributes:
                if rule_attr in rule:
                    attr[rule_attr] = str(rule[rule_attr])
            start_tag(doc, 'cfRule', attr)
            if 'formula' in rule:
                for f in rule['formula']:
                    tag(doc, 'formula', None, f)
            if 'colorScale' in rule:
                start_tag(doc, 'colorScale')
                for cfvo in rule['colorScale']['cfvo']:
                    tag(doc, 'cfvo', cfvo)
                for color in rule['colorScale']['color']:
                    if str(color.index).split(
                            ':'
                    )[0] == 'theme':  # strip prefix theme if marked as such
                        if str(color.index).split(':')[2]:
                            tag(
                                doc, 'color', {
                                    'theme': str(color.index).split(':')[1],
                                    'tint': str(color.index).split(':')[2]
                                })
                        else:
                            tag(doc, 'color',
                                {'theme': str(color.index).split(':')[1]})
                    else:
                        tag(doc, 'color', {'rgb': str(color.index)})
                end_tag(doc, 'colorScale')
            if 'iconSet' in rule:
                iconAttr = {}
                for icon_attr in ConditionalFormatting.icon_attributes:
                    if icon_attr in rule['iconSet']:
                        iconAttr[icon_attr] = rule['iconSet'][icon_attr]
                start_tag(doc, 'iconSet', iconAttr)
                for cfvo in rule['iconSet']['cfvo']:
                    tag(doc, 'cfvo', cfvo)
                end_tag(doc, 'iconSet')
            end_tag(doc, 'cfRule')
        end_tag(doc, 'conditionalFormatting')
Пример #25
0
def write_string_table(string_table):
    """Write the string table xml."""
    temp_buffer = StringIO()
    doc = XMLGenerator(temp_buffer, 'utf-8')
    start_tag(doc, 'sst', {'xmlns':
            'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
            'uniqueCount': '%d' % len(string_table)})
    strings_to_write = sorted(string_table.iteritems(),
            key=lambda pair: pair[1])
    for key in [pair[0] for pair in strings_to_write]:
        start_tag(doc, 'si')
        if key.strip() != key:
            attr = {'xml:space': 'preserve'}
        else:
            attr = {}
        tag(doc, 't', attr, key)
        end_tag(doc, 'si')
    end_tag(doc, 'sst')
    string_table_xml = temp_buffer.getvalue()
    temp_buffer.close()
    return string_table_xml
Пример #26
0
def write_worksheet(worksheet, string_table, style_table):
    """Write a worksheet to an xml file."""
    xml_file = StringIO()
    doc = XMLGenerator(out=xml_file, encoding='utf-8')
    start_tag(
        doc, 'worksheet', {
            'xml:space':
            'preserve',
            'xmlns':
            'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
            'xmlns:r':
            'http://schemas.openxmlformats.org/officeDocument/2006/relationships'
        })
    start_tag(doc, 'sheetPr')
    tag(
        doc, 'outlinePr', {
            'summaryBelow': '%d' % (worksheet.show_summary_below),
            'summaryRight': '%d' % (worksheet.show_summary_right)
        })
    if worksheet.page_setup.fitToPage:
        tag(doc, 'pageSetUpPr', {'fitToPage': '1'})
    end_tag(doc, 'sheetPr')
    tag(doc, 'dimension', {'ref': '%s' % worksheet.calculate_dimension()})
    write_worksheet_sheetviews(doc, worksheet)
    tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'})
    write_worksheet_cols(doc, worksheet)
    write_worksheet_data(doc, worksheet, string_table, style_table)
    if worksheet.auto_filter:
        tag(doc, 'autoFilter', {'ref': worksheet.auto_filter})
    write_worksheet_mergecells(doc, worksheet)
    write_worksheet_hyperlinks(doc, worksheet)

    options = worksheet.page_setup.options
    if options:
        tag(doc, 'printOptions', options)

    margins = worksheet.page_margins.margins
    if margins:
        tag(doc, 'pageMargins', margins)

    setup = worksheet.page_setup.setup
    if setup:
        tag(doc, 'pageSetup', setup)

    if worksheet.header_footer.hasHeader(
    ) or worksheet.header_footer.hasFooter():
        start_tag(doc, 'headerFooter')
        if worksheet.header_footer.hasHeader():
            tag(doc, 'oddHeader', None, worksheet.header_footer.getHeader())
        if worksheet.header_footer.hasFooter():
            tag(doc, 'oddFooter', None, worksheet.header_footer.getFooter())
        end_tag(doc, 'headerFooter')

    if worksheet._charts:
        tag(doc, 'drawing', {'r:id': 'rId1'})
    end_tag(doc, 'worksheet')
    doc.endDocument()
    xml_string = xml_file.getvalue()
    xml_file.close()
    return xml_string
Пример #27
0
    def write_header(self):

        doc = self.header

        start_tag(doc, 'worksheet',
                {'xml:space': 'preserve',
                'xmlns': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
                'xmlns:r': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'})
        start_tag(doc, 'sheetPr')
        tag(doc, 'outlinePr',
                {'summaryBelow': '1', 
                'summaryRight': '1'})
        end_tag(doc, 'sheetPr')
        tag(doc, 'dimension', {'ref': 'A1:%s' % (self.get_dimensions())})
        start_tag(doc, 'sheetViews')
        start_tag(doc, 'sheetView', {'workbookViewId': '0'})
        tag(doc, 'selection', {'activeCell': 'A1',
                'sqref': 'A1'})
        end_tag(doc, 'sheetView')
        end_tag(doc, 'sheetViews')
        tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'})
        start_tag(doc, 'sheetData')
Пример #28
0
    def write_header(self):

        fobj = self.get_temporary_file(filename=self._fileobj_header_name)
        doc = XMLGenerator(fobj, 'utf-8')

        start_tag(doc, 'worksheet',
                {
                'xmlns': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
                'xmlns:r': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'})
        start_tag(doc, 'sheetPr')
        tag(doc, 'outlinePr',
                {'summaryBelow': '1',
                'summaryRight': '1'})
        end_tag(doc, 'sheetPr')
        tag(doc, 'dimension', {'ref': 'A1:%s' % (self.get_dimensions())})
        start_tag(doc, 'sheetViews')
        start_tag(doc, 'sheetView', {'workbookViewId': '0'})
        tag(doc, 'selection', {'activeCell': 'A1',
                'sqref': 'A1'})
        end_tag(doc, 'sheetView')
        end_tag(doc, 'sheetViews')
        tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'})
        start_tag(doc, 'sheetData')
Пример #29
0
def write_worksheet_data(doc, worksheet, string_table, style_table):
    """Write worksheet data to xml."""
    start_tag(doc, 'sheetData')
    max_column = worksheet.get_highest_column()
    style_id_by_hash = style_table
    cells_by_row = {}
    for styleCoord in iterkeys(worksheet._styles):
        # Ensure a blank cell exists if it has a style
        worksheet.cell(styleCoord)
    for cell in worksheet.get_cell_collection():
        cells_by_row.setdefault(cell.row, []).append(cell)
    for row_idx in sorted(cells_by_row):
        row_dimension = worksheet.row_dimensions[row_idx]
        attrs = {'r': '%d' % row_idx,
                 'spans': '1:%d' % max_column}
        if not row_dimension.visible:
            attrs['hidden'] = '1'
        if row_dimension.height > 0:
            attrs['ht'] = str(row_dimension.height)
            attrs['customHeight'] = '1'
        start_tag(doc, 'row', attrs)
        row_cells = cells_by_row[row_idx]
        sorted_cells = sorted(row_cells, key=row_sort)
        for cell in sorted_cells:
            value = cell._value
            coordinate = cell.get_coordinate()
            attributes = {'r': coordinate}
            if cell.data_type != cell.TYPE_FORMULA:
                attributes['t'] = cell.data_type
            if coordinate in worksheet._styles:
                attributes['s'] = '%d' % style_id_by_hash[
                        hash(worksheet._styles[coordinate])]

            if value in ('', None):
                tag(doc, 'c', attributes)
            else:
                start_tag(doc, 'c', attributes)
                if cell.data_type == cell.TYPE_STRING:
                    tag(doc, 'v', body='%s' % string_table[value])
                elif cell.data_type == cell.TYPE_FORMULA:
                    tag(doc, 'f', body='%s' % value[1:])
                    tag(doc, 'v')
                elif cell.data_type == cell.TYPE_NUMERIC:
                    if isinstance(value, (long, decimal.Decimal)):
                        func = str
                    else:
                        func = repr
                    tag(doc, 'v', body=func(value))
                elif cell.data_type == cell.TYPE_BOOL:
                    tag(doc, 'v', body='%d' % value)
                else:
                    tag(doc, 'v', body='%s' % value)
                end_tag(doc, 'c')
        end_tag(doc, 'row')
    end_tag(doc, 'sheetData')
Пример #30
0
def write_worksheet_data(doc, worksheet, string_table, style_table):
    """Write worksheet data to xml."""
    start_tag(doc, 'sheetData')
    max_column = worksheet.get_highest_column()
    style_id_by_hash = style_table
    cells_by_row = {}
    for styleCoord in iterkeys(worksheet._styles):
        # Ensure a blank cell exists if it has a style
        worksheet.cell(styleCoord)
    for cell in worksheet.get_cell_collection():
        cells_by_row.setdefault(cell.row, []).append(cell)
    for row_idx in sorted(cells_by_row):
        row_dimension = worksheet.row_dimensions[row_idx]
        attrs = {'r': '%d' % row_idx, 'spans': '1:%d' % max_column}
        if not row_dimension.visible:
            attrs['hidden'] = '1'
        if row_dimension.height > 0:
            attrs['ht'] = str(row_dimension.height)
            attrs['customHeight'] = '1'
        start_tag(doc, 'row', attrs)
        row_cells = cells_by_row[row_idx]
        sorted_cells = sorted(row_cells, key=row_sort)
        for cell in sorted_cells:
            value = cell._value
            coordinate = cell.get_coordinate()
            attributes = {'r': coordinate}
            if cell.data_type != cell.TYPE_FORMULA:
                attributes['t'] = cell.data_type
            if coordinate in worksheet._styles:
                attributes['s'] = '%d' % style_id_by_hash[hash(
                    worksheet._styles[coordinate])]

            if value in ('', None):
                tag(doc, 'c', attributes)
            else:
                start_tag(doc, 'c', attributes)
                if cell.data_type == cell.TYPE_STRING:
                    tag(doc, 'v', body='%s' % string_table[value])
                elif cell.data_type == cell.TYPE_FORMULA:
                    tag(doc, 'f', body='%s' % value[1:])
                    tag(doc, 'v')
                elif cell.data_type == cell.TYPE_NUMERIC:
                    if isinstance(value, (long, decimal.Decimal)):
                        func = str
                    else:
                        func = repr
                    tag(doc, 'v', body=func(value))
                elif cell.data_type == cell.TYPE_BOOL:
                    tag(doc, 'v', body='%d' % value)
                else:
                    tag(doc, 'v', body='%s' % value)
                end_tag(doc, 'c')
        end_tag(doc, 'row')
    end_tag(doc, 'sheetData')
Пример #31
0
def write_worksheet_conditional_formatting(doc, worksheet):
    """Write conditional formatting to xml."""
    for range_string, rules in iteritems(worksheet.conditional_formatting.cf_rules):
        if not len(rules):
            # Skip if there are no rules.  This is possible if a dataBar rule was read in and ignored.
            continue
        start_tag(doc, 'conditionalFormatting', {'sqref': range_string})
        for rule in rules:
            if rule['type'] == 'dataBar':
                # Ignore - uses extLst tag which is currently unsupported.
                continue
            attr = {'type': rule['type']}
            for rule_attr in ConditionalFormatting.rule_attributes:
                if rule_attr in rule:
                    attr[rule_attr] = str(rule[rule_attr])
            start_tag(doc, 'cfRule', attr)
            if 'formula' in rule:
                for f in rule['formula']:
                    tag(doc, 'formula', None, f)
            if 'colorScale' in rule:
                start_tag(doc, 'colorScale')
                for cfvo in rule['colorScale']['cfvo']:
                    tag(doc, 'cfvo', cfvo)
                for color in rule['colorScale']['color']:
                    if str(color.index).split(':')[0] == 'theme':  # strip prefix theme if marked as such
                        if str(color.index).split(':')[2]:
                            tag(doc, 'color', {'theme': str(color.index).split(':')[1],
                                               'tint': str(color.index).split(':')[2]})
                        else:
                            tag(doc, 'color', {'theme': str(color.index).split(':')[1]})
                    else:
                        tag(doc, 'color', {'rgb': str(color.index)})
                end_tag(doc, 'colorScale')
            if 'iconSet' in rule:
                iconAttr = {}
                for icon_attr in ConditionalFormatting.icon_attributes:
                    if icon_attr in rule['iconSet']:
                        iconAttr[icon_attr] = rule['iconSet'][icon_attr]
                start_tag(doc, 'iconSet', iconAttr)
                for cfvo in rule['iconSet']['cfvo']:
                    tag(doc, 'cfvo', cfvo)
                end_tag(doc, 'iconSet')
            end_tag(doc, 'cfRule')
        end_tag(doc, 'conditionalFormatting')
Пример #32
0
def write_worksheet(worksheet, string_table, style_table):
    """Write a worksheet to an xml file."""
    xml_file = StringIO()
    doc = XMLGenerator(out=xml_file, encoding='utf-8')
    start_tag(doc, 'worksheet',
            {'xml:space': 'preserve',
            'xmlns': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
            'xmlns:r': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'})
    start_tag(doc, 'sheetPr')
    tag(doc, 'outlinePr',
            {'summaryBelow': '%d' % (worksheet.show_summary_below),
            'summaryRight': '%d' % (worksheet.show_summary_right)})
    if worksheet.page_setup.fitToPage:
        tag(doc, 'pageSetUpPr', {'fitToPage':'1'})
    end_tag(doc, 'sheetPr')
    tag(doc, 'dimension', {'ref': '%s' % worksheet.calculate_dimension()})
    write_worksheet_sheetviews(doc, worksheet)
    tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'})
    write_worksheet_cols(doc, worksheet)
    write_worksheet_data(doc, worksheet, string_table, style_table)
    if worksheet.auto_filter:
        tag(doc, 'autoFilter', {'ref': worksheet.auto_filter})
    write_worksheet_mergecells(doc, worksheet)
    write_worksheet_datavalidations(doc, worksheet)
    write_worksheet_hyperlinks(doc, worksheet)

    options = worksheet.page_setup.options
    if options:
        tag(doc, 'printOptions', options)

    margins = worksheet.page_margins.margins
    if margins:
        tag(doc, 'pageMargins', margins)

    setup = worksheet.page_setup.setup
    if setup:
        tag(doc, 'pageSetup', setup)

    if worksheet.header_footer.hasHeader() or worksheet.header_footer.hasFooter():
        start_tag(doc, 'headerFooter')
        if worksheet.header_footer.hasHeader():
            tag(doc, 'oddHeader', None, worksheet.header_footer.getHeader())
        if worksheet.header_footer.hasFooter():
            tag(doc, 'oddFooter', None, worksheet.header_footer.getFooter())
        end_tag(doc, 'headerFooter')

    if worksheet._charts or worksheet._images:
        tag(doc, 'drawing', {'r:id':'rId1'})
    end_tag(doc, 'worksheet')
    doc.endDocument()
    xml_string = xml_file.getvalue()
    xml_file.close()
    return xml_string
Пример #33
0
def write_worksheet_data(doc, worksheet, string_table, style_table):
    """Write worksheet data to xml."""
    start_tag(doc, "sheetData")
    max_column = worksheet.get_highest_column()
    style_id_by_hash = style_table
    cells_by_row = {}
    for cell in worksheet.get_cell_collection():
        cells_by_row.setdefault(cell.row, []).append(cell)
    for row_idx in sorted(cells_by_row):
        row_dimension = worksheet.row_dimensions[row_idx]
        attrs = {"r": "%d" % row_idx, "spans": "1:%d" % max_column}
        if row_dimension.height > 0:
            attrs["ht"] = str(row_dimension.height)
            attrs["customHeight"] = "1"
        start_tag(doc, "row", attrs)
        row_cells = cells_by_row[row_idx]
        sorted_cells = sorted(row_cells, key=row_sort)
        for cell in sorted_cells:
            value = cell._value
            coordinate = cell.get_coordinate()
            attributes = {"r": coordinate}
            attributes["t"] = cell.data_type
            if coordinate in worksheet._styles:
                attributes["s"] = "%d" % style_id_by_hash[hash(worksheet._styles[coordinate])]
            start_tag(doc, "c", attributes)
            if value is None:
                tag(doc, "v", body="")
            elif cell.data_type == cell.TYPE_STRING:
                tag(doc, "v", body="%s" % string_table[value])
            elif cell.data_type == cell.TYPE_FORMULA:
                tag(doc, "f", body="%s" % value[1:])
                tag(doc, "v")
            elif cell.data_type == cell.TYPE_NUMERIC:
                if isinstance(value, long):
                    func = str
                else:
                    func = repr
                tag(doc, "v", body=func(value))
            elif cell.data_type == cell.TYPE_BOOL:
                tag(doc, "v", body="%d" % value)
            else:
                tag(doc, "v", body="%s" % value)
            end_tag(doc, "c")
        end_tag(doc, "row")
    end_tag(doc, "sheetData")
Пример #34
0
def write_worksheet_data(doc, worksheet, string_table, style_table):
    """Write worksheet data to xml."""
    start_tag(doc, 'sheetData')
    max_column = worksheet.get_highest_column()
    style_id_by_hash = style_table
    cells_by_row = {}
    for cell in worksheet.get_cell_collection():
        cells_by_row.setdefault(cell.row, []).append(cell)
    for row_idx in sorted(cells_by_row):
        row_dimension = worksheet.row_dimensions[row_idx]
        attrs = {'r': '%d' % row_idx,
                 'spans': '1:%d' % max_column}
        if row_dimension.height > 0:
            attrs['ht'] = str(row_dimension.height)
            attrs['customHeight'] = '1'
        start_tag(doc, 'row', attrs)
        row_cells = cells_by_row[row_idx]
        sorted_cells = sorted(row_cells, key = row_sort)
        for cell in sorted_cells:
            value = cell._value
            coordinate = cell.get_coordinate()
            attributes = {'r': coordinate}
            attributes['t'] = cell.data_type
            if coordinate in worksheet._styles:
                attributes['s'] = '%d' % style_id_by_hash[
                        hash(worksheet._styles[coordinate])]
            start_tag(doc, 'c', attributes)
            if value is None:
                tag(doc, 'v', body='')
            elif cell.data_type == cell.TYPE_STRING:
                tag(doc, 'v', body = '%s' % string_table[value])
            elif cell.data_type == cell.TYPE_FORMULA:
                tag(doc, 'f', body = '%s' % value[1:])
                tag(doc, 'v')
            elif cell.data_type == cell.TYPE_NUMERIC:
                tag(doc, 'v', body = repr(value))
            elif cell.data_type == cell.TYPE_BOOL:
                tag(doc, 'v', body = '%d' % value)
            else:
                tag(doc, 'v', body = '%s' % value)
            end_tag(doc, 'c')
        end_tag(doc, 'row')
    end_tag(doc, 'sheetData')
Пример #35
0
def write_worksheet_data(doc, worksheet, string_table, style_table):
    """Write worksheet data to xml."""
    start_tag(doc, 'sheetData')
    max_column = worksheet.get_highest_column()
    style_id_by_hash = style_table
    cells_by_row = {}
    for cell in worksheet.get_cell_collection():
        cells_by_row.setdefault(cell.row, []).append(cell)
    for row_idx in sorted(cells_by_row):
        row_dimension = worksheet.row_dimensions[row_idx]
        attrs = {'r': '%d' % row_idx, 'spans': '1:%d' % max_column}
        if row_dimension.height > 0:
            attrs['ht'] = str(row_dimension.height)
            attrs['customHeight'] = '1'
        start_tag(doc, 'row', attrs)
        row_cells = cells_by_row[row_idx]
        sorted_cells = sorted(row_cells, key=row_sort)
        for cell in sorted_cells:
            value = cell._value
            coordinate = cell.get_coordinate()
            attributes = {'r': coordinate}
            attributes['t'] = cell.data_type
            if coordinate in worksheet._styles:
                attributes['s'] = '%d' % style_id_by_hash[hash(
                    worksheet._styles[coordinate])]
            start_tag(doc, 'c', attributes)
            if value is None:
                tag(doc, 'v', body='')
            elif cell.data_type == cell.TYPE_STRING:
                tag(doc, 'v', body='%s' % string_table[value])
            elif cell.data_type == cell.TYPE_FORMULA:
                tag(doc, 'f', body='%s' % value[1:])
                tag(doc, 'v')
            elif cell.data_type == cell.TYPE_NUMERIC:
                tag(doc, 'v', body=repr(value))
            elif cell.data_type == cell.TYPE_BOOL:
                tag(doc, 'v', body='%d' % value)
            else:
                tag(doc, 'v', body='%s' % value)
            end_tag(doc, 'c')
        end_tag(doc, 'row')
    end_tag(doc, 'sheetData')
Пример #36
0
def write_cfrules(doc, worksheet):
    """Write worksheet conditional formatting rules to xml."""
    i = 0
    if worksheet.cf_rules.values():
        for range, rules in worksheet.cf_rules.iteritems():
            if not len(rules):
                continue
            start_tag(doc, 'conditionalFormatting', {'sqref': range})
            for rule in rules:
                type = rule.type
                start_tag(doc, 'cfRule', {'type': type, 'priority': str(i)})
                attrs = {}
                if not rule.showValue:
                    attrs['showValue']="0"
                start_tag(doc, type, attrs)
                tag(doc, 'cfvo', {'type': rule.min.type, 'val': str(rule.min.val)})
                tag(doc, 'cfvo', {'type': rule.max.type, 'val': str(rule.max.val)})
                tag(doc, 'color', {'rgb': str(rule.color.index)})
                end_tag(doc, type)
                end_tag(doc, 'cfRule')
                i += 1
            end_tag(doc, 'conditionalFormatting')
Пример #37
0
def write_worksheet(worksheet, string_table, style_table):
    """Write a worksheet to an xml file."""
    if worksheet.xml_source:
        vba_root = fromstring(worksheet.xml_source)
    else:
        vba_root = None
    xml_file = StringIO()
    doc = XMLGenerator(out=xml_file, encoding='utf-8')
    start_tag(doc, 'worksheet', {'xmlns': SHEET_MAIN_NS, 'xmlns:r': REL_NS})
    if vba_root is not None:
        el = vba_root.find('{%s}sheetPr' % SHEET_MAIN_NS)
        if el is not None:
            codename = el.get('codeName', worksheet.title)
            start_tag(doc, 'sheetPr', {"codeName": codename})
        else:
            start_tag(doc, 'sheetPr')
    else:
        start_tag(doc, 'sheetPr')
    tag(
        doc, 'outlinePr', {
            'summaryBelow': '%d' % (worksheet.show_summary_below),
            'summaryRight': '%d' % (worksheet.show_summary_right)
        })
    if worksheet.page_setup.fitToPage:
        tag(doc, 'pageSetUpPr', {'fitToPage': '1'})
    end_tag(doc, 'sheetPr')
    tag(doc, 'dimension', {'ref': '%s' % worksheet.calculate_dimension()})
    write_worksheet_sheetviews(doc, worksheet)
    tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'})
    write_worksheet_cols(doc, worksheet, style_table)
    write_worksheet_data(doc, worksheet, string_table, style_table)
    if worksheet.auto_filter:
        tag(doc, 'autoFilter', {'ref': worksheet.auto_filter})
    write_worksheet_mergecells(doc, worksheet)
    write_worksheet_datavalidations(doc, worksheet)
    write_worksheet_hyperlinks(doc, worksheet)
    write_worksheet_conditional_formatting(doc, worksheet)

    options = worksheet.page_setup.options
    if options:
        tag(doc, 'printOptions', options)

    margins = worksheet.page_margins.margins
    if margins:
        tag(doc, 'pageMargins', margins)

    setup = worksheet.page_setup.setup
    if setup:
        tag(doc, 'pageSetup', setup)

    if worksheet.header_footer.hasHeader(
    ) or worksheet.header_footer.hasFooter():
        start_tag(doc, 'headerFooter')
        if worksheet.header_footer.hasHeader():
            tag(doc, 'oddHeader', None, worksheet.header_footer.getHeader())
        if worksheet.header_footer.hasFooter():
            tag(doc, 'oddFooter', None, worksheet.header_footer.getFooter())
        end_tag(doc, 'headerFooter')

    if worksheet._charts or worksheet._images:
        tag(doc, 'drawing', {'r:id': 'rId1'})

    # If vba is being preserved then add a legacyDrawing element so
    # that any controls can be drawn.
    if vba_root is not None:
        el = vba_root.find('{%s}legacyDrawing' % SHEET_MAIN_NS)
        if el is not None:
            rId = el.get('{%s}id' % REL_NS)
            tag(doc, 'legacyDrawing', {'r:id': rId})

    breaks = worksheet.page_breaks
    if breaks:
        start_tag(doc, 'rowBreaks', {
            'count': str(len(breaks)),
            'manualBreakCount': str(len(breaks))
        })
        for b in breaks:
            tag(doc, 'brk', {
                'id': str(b),
                'man': 'true',
                'max': '16383',
                'min': '0'
            })
        end_tag(doc, 'rowBreaks')

    # add a legacyDrawing so that excel can draw comments
    if worksheet._comment_count > 0:
        tag(doc, 'legacyDrawing', {'r:id': 'commentsvml'})

    end_tag(doc, 'worksheet')
    doc.endDocument()
    xml_string = xml_file.getvalue()
    xml_file.close()
    return xml_string
Пример #38
0
def write_etree(doc, element):
    start_tag(doc, element.tag, element)
    for e in element.getchildren():
        write_etree(doc, e)
    end_tag(doc, element.tag)
Пример #39
0
def write_worksheet(worksheet, string_table, style_table):
    """Write a worksheet to an xml file."""
    if worksheet.xml_source:
        vba_root = fromstring(worksheet.xml_source)
        register_namespace(
            "r",
            "http://schemas.openxmlformats.org/officeDocument/2006/relationships"
        )
        register_namespace(
            "", "http://schemas.openxmlformats.org/spreadsheetml/2006/main")
    else:
        vba_root = None
    xml_file = StringIO()
    doc = XMLGenerator(out=xml_file, encoding='utf-8')
    start_tag(
        doc, 'worksheet', {
            'xml:space':
            'preserve',
            'xmlns':
            'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
            'xmlns:r':
            'http://schemas.openxmlformats.org/officeDocument/2006/relationships'
        })
    if vba_root is not None:
        codename = vba_root.find(
            '{http://schemas.openxmlformats.org/spreadsheetml/2006/main}sheetPr'
        ).get('codeName', worksheet.title)
        start_tag(doc, 'sheetPr', {"codeName": codename})
    else:
        start_tag(doc, 'sheetPr')
    tag(
        doc, 'outlinePr', {
            'summaryBelow': '%d' % (worksheet.show_summary_below),
            'summaryRight': '%d' % (worksheet.show_summary_right)
        })
    if worksheet.page_setup.fitToPage:
        tag(doc, 'pageSetUpPr', {'fitToPage': '1'})
    end_tag(doc, 'sheetPr')
    tag(doc, 'dimension', {'ref': '%s' % worksheet.calculate_dimension()})
    write_worksheet_sheetviews(doc, worksheet)
    tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'})
    write_worksheet_cols(doc, worksheet)
    write_worksheet_data(doc, worksheet, string_table, style_table)
    if worksheet.auto_filter:
        tag(doc, 'autoFilter', {'ref': worksheet.auto_filter})
    write_worksheet_mergecells(doc, worksheet)
    write_worksheet_datavalidations(doc, worksheet)
    write_worksheet_hyperlinks(doc, worksheet)

    options = worksheet.page_setup.options
    if options:
        tag(doc, 'printOptions', options)

    margins = worksheet.page_margins.margins
    if margins:
        tag(doc, 'pageMargins', margins)

    setup = worksheet.page_setup.setup
    if setup:
        tag(doc, 'pageSetup', setup)

    if worksheet.header_footer.hasHeader(
    ) or worksheet.header_footer.hasFooter():
        start_tag(doc, 'headerFooter')
        if worksheet.header_footer.hasHeader():
            tag(doc, 'oddHeader', None, worksheet.header_footer.getHeader())
        if worksheet.header_footer.hasFooter():
            tag(doc, 'oddFooter', None, worksheet.header_footer.getFooter())
        end_tag(doc, 'headerFooter')

    if worksheet._charts or worksheet._images:
        tag(doc, 'drawing', {'r:id': 'rId1'})

    # if the sheet has an xml_source field then the workbook must have
    # been loaded with keep-vba true and we need to extract any control
    # elements.
    if vba_root is not None:
        for t in (
                '{http://schemas.openxmlformats.org/spreadsheetml/2006/main}legacyDrawing',
                '{http://schemas.openxmlformats.org/spreadsheetml/2006/main}controls'
        ):
            for elem in vba_root.findall(t):
                xml_file.write(
                    re.sub(r' xmlns[^ >]*', '',
                           tostring(elem).decode("utf-8")))

    breaks = worksheet.page_breaks
    if breaks:
        start_tag(doc, 'rowBreaks', {
            'count': str(len(breaks)),
            'manualBreakCount': str(len(breaks))
        })
        for b in breaks:
            tag(doc, 'brk', {
                'id': str(b),
                'man': 'true',
                'max': '16383',
                'min': '0'
            })
        end_tag(doc, 'rowBreaks')

    end_tag(doc, 'worksheet')
    doc.endDocument()
    xml_string = xml_file.getvalue()
    xml_file.close()
    return xml_string
Пример #40
0
def write_worksheet(worksheet, string_table, style_table):
    """Write a worksheet to an xml file."""
    if worksheet.xml_source:
        vba_root = fromstring(worksheet.xml_source)
    else:
        vba_root = None
    xml_file = StringIO()
    doc = XMLGenerator(out=xml_file, encoding='utf-8')
    start_tag(doc, 'worksheet',
            {'xml:space': 'preserve',
            'xmlns': SHEET_MAIN_NS,
            'xmlns:r': REL_NS})
    if vba_root is not None:
        codename = vba_root.find('{%s}sheetPr' % SHEET_MAIN_NS).get('codeName', worksheet.title)
        start_tag(doc, 'sheetPr', {"codeName": codename})
    else:
        start_tag(doc, 'sheetPr')
    tag(doc, 'outlinePr',
            {'summaryBelow': '%d' % (worksheet.show_summary_below),
            'summaryRight': '%d' % (worksheet.show_summary_right)})
    if worksheet.page_setup.fitToPage:
        tag(doc, 'pageSetUpPr', {'fitToPage':'1'})
    end_tag(doc, 'sheetPr')
    tag(doc, 'dimension', {'ref': '%s' % worksheet.calculate_dimension()})
    write_worksheet_sheetviews(doc, worksheet)
    tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'})
    write_worksheet_cols(doc, worksheet, style_table)
    write_worksheet_data(doc, worksheet, string_table, style_table)
    if worksheet.auto_filter:
        tag(doc, 'autoFilter', {'ref': worksheet.auto_filter})
    write_worksheet_mergecells(doc, worksheet)
    write_worksheet_datavalidations(doc, worksheet)
    write_worksheet_hyperlinks(doc, worksheet)

    for range_string, rules in worksheet.conditional_formatting.cf_rules.iteritems():
        if not len(rules):
            # Skip if there are no rules.  This is possible if a dataBar rule was read in and ignored.
            continue
        start_tag(doc, 'conditionalFormatting', {'sqref': range_string})
        for rule in rules:
            if rule['type'] == 'dataBar':
                # Ignore - uses extLst tag which is currently unsupported.
                continue
            attr = {'type': rule['type']}
            for rule_attr in ConditionalFormatting.rule_attributes:
                if rule_attr in rule:
                    attr[rule_attr] = str(rule[rule_attr])
            start_tag(doc, 'cfRule', attr)
            if 'formula' in rule:
                for f in rule['formula']:
                    tag(doc, 'formula', None, f)
            if 'colorScale' in rule:
                start_tag(doc, 'colorScale')
                for cfvo in rule['colorScale']['cfvo']:
                    tag(doc, 'cfvo', cfvo)
                for color in rule['colorScale']['color']:
                    if str(color.index).split(':')[0] == 'theme':  # strip prefix theme if marked as such
                        if str(color.index).split(':')[2]:
                            tag(doc, 'color', {'theme': str(color.index).split(':')[1],
                                               'tint': str(color.index).split(':')[2]})
                        else:
                            tag(doc, 'color', {'theme': str(color.index).split(':')[1]})
                    else:
                        tag(doc, 'color', {'rgb': str(color.index)})
                end_tag(doc, 'colorScale')
            if 'iconSet' in rule:
                iconAttr = {}
                for icon_attr in ConditionalFormatting.icon_attributes:
                    if icon_attr in rule['iconSet']:
                        iconAttr[icon_attr] = rule['iconSet'][icon_attr]
                start_tag(doc, 'iconSet', iconAttr)
                for cfvo in rule['iconSet']['cfvo']:
                    tag(doc, 'cfvo', cfvo)
                end_tag(doc, 'iconSet')
            end_tag(doc, 'cfRule')
        end_tag(doc, 'conditionalFormatting')

    options = worksheet.page_setup.options
    if options:
        tag(doc, 'printOptions', options)

    margins = worksheet.page_margins.margins
    if margins:
        tag(doc, 'pageMargins', margins)

    setup = worksheet.page_setup.setup
    if setup:
        tag(doc, 'pageSetup', setup)

    if worksheet.header_footer.hasHeader() or worksheet.header_footer.hasFooter():
        start_tag(doc, 'headerFooter')
        if worksheet.header_footer.hasHeader():
            tag(doc, 'oddHeader', None, worksheet.header_footer.getHeader())
        if worksheet.header_footer.hasFooter():
            tag(doc, 'oddFooter', None, worksheet.header_footer.getFooter())
        end_tag(doc, 'headerFooter')

    if worksheet._charts or worksheet._images:
        tag(doc, 'drawing', {'r:id':'rId1'})

    # if the sheet has an xml_source field then the workbook must have
    # been loaded with keep-vba true and we need to extract any control
    # elements.
    if vba_root is not None:
        for t in ('{%s}legacyDrawing' % SHEET_MAIN_NS,
                  '{%s}controls' % SHEET_MAIN_NS):
            for elem in vba_root.findall(t):
                xml_file.write(re.sub(r' xmlns[^ >]*', '', tostring(elem).decode("utf-8")))

    breaks = worksheet.page_breaks
    if breaks:
        start_tag(doc, 'rowBreaks', {'count': str(len(breaks)), 'manualBreakCount': str(len(breaks))})
        for b in breaks:
            tag(doc, 'brk', {'id': str(b), 'man': 'true', 'max': '16383', 'min': '0'})
        end_tag(doc, 'rowBreaks')

    end_tag(doc, 'worksheet')
    doc.endDocument()
    xml_string = xml_file.getvalue()
    xml_file.close()
    return xml_string
Пример #41
0
def write_etree(doc, element):
    start_tag(doc, element.tag, element)
    for e in element.getchildren():
        write_etree(doc, e)
    end_tag(doc, element.tag)
Пример #42
0
def write_worksheet(worksheet, string_table, style_table):
    """Write a worksheet to an xml file."""
    if worksheet.xml_source:
        vba_root = fromstring(worksheet.xml_source)
    else:
        vba_root = None
    xml_file = StringIO()
    doc = XMLGenerator(out=xml_file, encoding='utf-8')
    start_tag(doc, 'worksheet',
            {'xmlns': SHEET_MAIN_NS,
            'xmlns:r': REL_NS})
    if vba_root is not None:
        el = vba_root.find('{%s}sheetPr' % SHEET_MAIN_NS)
        if el is not None:
                codename =el.get('codeName', worksheet.title)
                start_tag(doc, 'sheetPr', {"codeName": codename})
        else:
                start_tag(doc, 'sheetPr')
    else:
        start_tag(doc, 'sheetPr')
    tag(doc, 'outlinePr',
            {'summaryBelow': '%d' % (worksheet.show_summary_below),
            'summaryRight': '%d' % (worksheet.show_summary_right)})
    if worksheet.page_setup.fitToPage:
        tag(doc, 'pageSetUpPr', {'fitToPage':'1'})
    end_tag(doc, 'sheetPr')
    tag(doc, 'dimension', {'ref': '%s' % worksheet.calculate_dimension()})
    write_worksheet_sheetviews(doc, worksheet)
    tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'})
    write_worksheet_cols(doc, worksheet, style_table)
    write_worksheet_data(doc, worksheet, string_table, style_table)
    if worksheet.auto_filter:
        tag(doc, 'autoFilter', {'ref': worksheet.auto_filter})
    write_worksheet_mergecells(doc, worksheet)
    write_worksheet_datavalidations(doc, worksheet)
    write_worksheet_hyperlinks(doc, worksheet)
    write_worksheet_conditional_formatting(doc, worksheet)


    options = worksheet.page_setup.options
    if options:
        tag(doc, 'printOptions', options)

    margins = worksheet.page_margins.margins
    if margins:
        tag(doc, 'pageMargins', margins)

    setup = worksheet.page_setup.setup
    if setup:
        tag(doc, 'pageSetup', setup)

    if worksheet.header_footer.hasHeader() or worksheet.header_footer.hasFooter():
        start_tag(doc, 'headerFooter')
        if worksheet.header_footer.hasHeader():
            tag(doc, 'oddHeader', None, worksheet.header_footer.getHeader())
        if worksheet.header_footer.hasFooter():
            tag(doc, 'oddFooter', None, worksheet.header_footer.getFooter())
        end_tag(doc, 'headerFooter')

    if worksheet._charts or worksheet._images:
        tag(doc, 'drawing', {'r:id':'rId1'})

    # If vba is being preserved then add a legacyDrawing element so
    # that any controls can be drawn.
    if vba_root is not None:
        el = vba_root.find('{%s}legacyDrawing' % SHEET_MAIN_NS)
        if el is not None:
            rId = el.get('{%s}id' % REL_NS)
            tag(doc, 'legacyDrawing', {'r:id': rId})

    breaks = worksheet.page_breaks
    if breaks:
        start_tag(doc, 'rowBreaks', {'count': str(len(breaks)), 'manualBreakCount': str(len(breaks))})
        for b in breaks:
            tag(doc, 'brk', {'id': str(b), 'man': 'true', 'max': '16383', 'min': '0'})
        end_tag(doc, 'rowBreaks')

    # add a legacyDrawing so that excel can draw comments
    if worksheet._comment_count > 0:
        tag(doc, 'legacyDrawing', {'r:id':'commentsvml'})

    end_tag(doc, 'worksheet')
    doc.endDocument()
    xml_string = xml_file.getvalue()
    xml_file.close()
    return xml_string
Пример #43
0
def write_worksheet(worksheet, string_table, style_table):
    """Write a worksheet to an xml file."""
    if worksheet.xml_source:
        vba_root = fromstring(worksheet.xml_source)
        register_namespace("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships")
        register_namespace("", "http://schemas.openxmlformats.org/spreadsheetml/2006/main")
    else:
        vba_root = None
    xml_file = StringIO()
    doc = XMLGenerator(out=xml_file, encoding='utf-8')
    start_tag(doc, 'worksheet',
            {'xml:space': 'preserve',
            'xmlns': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
            'xmlns:r': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'})
    if vba_root is not None:
        codename = vba_root.find('{http://schemas.openxmlformats.org/spreadsheetml/2006/main}sheetPr').get('codeName', worksheet.title)
        start_tag(doc, 'sheetPr', {"codeName": codename})
    else:
        start_tag(doc, 'sheetPr')
    tag(doc, 'outlinePr',
            {'summaryBelow': '%d' % (worksheet.show_summary_below),
            'summaryRight': '%d' % (worksheet.show_summary_right)})
    if worksheet.page_setup.fitToPage:
        tag(doc, 'pageSetUpPr', {'fitToPage':'1'})
    end_tag(doc, 'sheetPr')
    tag(doc, 'dimension', {'ref': '%s' % worksheet.calculate_dimension()})
    write_worksheet_sheetviews(doc, worksheet)
    tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'})
    write_worksheet_cols(doc, worksheet)
    write_worksheet_data(doc, worksheet, string_table, style_table)
    if worksheet.auto_filter:
        tag(doc, 'autoFilter', {'ref': worksheet.auto_filter})
    write_worksheet_mergecells(doc, worksheet)
    write_worksheet_datavalidations(doc, worksheet)
    write_worksheet_hyperlinks(doc, worksheet)

    options = worksheet.page_setup.options
    if options:
        tag(doc, 'printOptions', options)

    margins = worksheet.page_margins.margins
    if margins:
        tag(doc, 'pageMargins', margins)

    setup = worksheet.page_setup.setup
    if setup:
        tag(doc, 'pageSetup', setup)

    if worksheet.header_footer.hasHeader() or worksheet.header_footer.hasFooter():
        start_tag(doc, 'headerFooter')
        if worksheet.header_footer.hasHeader():
            tag(doc, 'oddHeader', None, worksheet.header_footer.getHeader())
        if worksheet.header_footer.hasFooter():
            tag(doc, 'oddFooter', None, worksheet.header_footer.getFooter())
        end_tag(doc, 'headerFooter')

    if worksheet._charts or worksheet._images:
        tag(doc, 'drawing', {'r:id':'rId1'})

    # if the sheet has an xml_source field then the workbook must have
    # been loaded with keep-vba true and we need to extract any control
    # elements.
    if vba_root is not None:
        for t in ('{http://schemas.openxmlformats.org/spreadsheetml/2006/main}legacyDrawing',
                  '{http://schemas.openxmlformats.org/spreadsheetml/2006/main}controls'):
            for elem in vba_root.findall(t):
                xml_file.write(re.sub(r' xmlns[^ >]*', '', tostring(elem).decode("utf-8")))

    breaks = worksheet.page_breaks
    if breaks:
        start_tag(doc, 'rowBreaks', {'count': str(len(breaks)), 'manualBreakCount': str(len(breaks))})
        for b in breaks:
            tag(doc, 'brk', {'id': str(b), 'man': 'true', 'max': '16383', 'min': '0'})
        end_tag(doc, 'rowBreaks')




    end_tag(doc, 'worksheet')
    doc.endDocument()
    xml_string = xml_file.getvalue()
    xml_file.close()
    return xml_string