python-openpyxl 1.7.0+ds1-1 / usr / share / pyshared / openpyxl / writer / workbook.py

# file openpyxl/writer/workbook.py

# Copyright (c) 2010-2011 openpyxl
#
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:
#
# The above copyright notice and this permission notice shall be included in
# all copies or substantial portions of the Software.
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
# THE SOFTWARE.
#
# @license: http://www.opensource.org/licenses/mit-license.php
# @author: see AUTHORS file

"""Write the workbook global settings to the archive."""

# package imports
from openpyxl.shared.xmltools import Element, SubElement
from openpyxl.cell import absolute_coordinate
from openpyxl.shared.xmltools import get_document_content, fromstring, register_namespace
from openpyxl.shared.ooxml import NAMESPACES, ARC_CORE, ARC_WORKBOOK, \
       ARC_APP, ARC_THEME, ARC_STYLE, ARC_SHARED_STRINGS, ARC_CONTENT_TYPES
from openpyxl.shared.date_time import datetime_to_W3CDTF
from openpyxl.namedrange import NamedRange, NamedRangeContainingValue


def write_properties_core(properties):
    """Write the core properties to xml."""
    root = Element('cp:coreProperties', {'xmlns:cp': NAMESPACES['cp'],
            'xmlns:xsi': NAMESPACES['xsi'], 'xmlns:dc': NAMESPACES['dc'],
            'xmlns:dcterms': NAMESPACES['dcterms'],
            'xmlns:dcmitype': NAMESPACES['dcmitype'], })
    SubElement(root, 'dc:creator').text = properties.creator
    SubElement(root, 'cp:lastModifiedBy').text = properties.last_modified_by
    SubElement(root, 'dcterms:created', \
            {'xsi:type': 'dcterms:W3CDTF'}).text = \
            datetime_to_W3CDTF(properties.created)
    SubElement(root, 'dcterms:modified',
            {'xsi:type': 'dcterms:W3CDTF'}).text = \
            datetime_to_W3CDTF(properties.modified)
    SubElement(root, 'dc:title').text = properties.title
    SubElement(root, 'dc:description').text = properties.description
    SubElement(root, 'dc:subject').text = properties.subject
    SubElement(root, 'cp:keywords').text = properties.keywords
    SubElement(root, 'cp:category').text = properties.category
    return get_document_content(root)


def write_content_types(workbook):
    """Write the content-types xml."""
    seen = set()
    if workbook.vba_archive:
        root = fromstring(workbook.vba_archive.read(ARC_CONTENT_TYPES))
        register_namespace('', 'http://schemas.openxmlformats.org/package/2006/content-types')
        for elem in root.findall('{http://schemas.openxmlformats.org/package/2006/content-types}Override'):
            seen.add(elem.attrib['PartName'])
    else:
        root = Element('Types', {'xmlns': 'http://schemas.openxmlformats.org/package/2006/content-types'})
        SubElement(root, 'Override', {'PartName': '/' + ARC_THEME, 'ContentType': 'application/vnd.openxmlformats-officedocument.theme+xml'})
        SubElement(root, 'Override', {'PartName': '/' + ARC_STYLE, 'ContentType': 'application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml'})
        SubElement(root, 'Default', {'Extension': 'rels', 'ContentType': 'application/vnd.openxmlformats-package.relationships+xml'})
        SubElement(root, 'Default', {'Extension': 'xml', 'ContentType': 'application/xml'})
        SubElement(root, 'Default', {'Extension': 'png', 'ContentType': 'image/png'})
        SubElement(root, 'Override', {'PartName': '/' + ARC_WORKBOOK, 'ContentType': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml'})
        SubElement(root, 'Override', {'PartName': '/' + ARC_APP, 'ContentType': 'application/vnd.openxmlformats-officedocument.extended-properties+xml'})
        SubElement(root, 'Override', {'PartName': '/' + ARC_CORE, 'ContentType': 'application/vnd.openxmlformats-package.core-properties+xml'})
        SubElement(root, 'Override', {'PartName': '/' + ARC_SHARED_STRINGS, 'ContentType': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml'})

    drawing_id = 1
    chart_id = 1

    for sheet_id, sheet in enumerate(workbook.worksheets):
        part_name = '/xl/worksheets/sheet%d.xml' % (sheet_id + 1)
        if part_name not in seen:
            SubElement(root, 'Override',
                       {'PartName': part_name,
                        'ContentType': 'application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml'})
        if sheet._charts or sheet._images:
            part_name = '/xl/drawings/drawing%d.xml' % drawing_id
            if part_name not in seen:
                SubElement(root, 'Override',
                           {'PartName': part_name,
                            'ContentType': 'application/vnd.openxmlformats-officedocument.drawing+xml'})
            drawing_id += 1

            for chart in sheet._charts:
                part_name = '/xl/charts/chart%d.xml' % chart_id

                if part_name not in seen:
                    SubElement(root, 'Override',
                               {'PartName': part_name,
                                'ContentType': 'application/vnd.openxmlformats-officedocument.drawingml.chart+xml'})
                    chart_id += 1
                    if chart._shapes:
                        part_name = '/xl/drawings/drawing%d.xml' % drawing_id
                        if part_name not in seen:
                            SubElement(root, 'Override',
                                       {'PartName': part_name,
                                        'ContentType': 'application/vnd.openxmlformats-officedocument.drawingml.chartshapes+xml'})
                        drawing_id += 1

    return get_document_content(root)


def write_properties_app(workbook):
    """Write the properties xml."""
    worksheets_count = len(workbook.worksheets)
    root = Element('Properties', {'xmlns': 'http://schemas.openxmlformats.org/officeDocument/2006/extended-properties',
            'xmlns:vt': 'http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes'})
    SubElement(root, 'Application').text = 'Microsoft Excel'
    SubElement(root, 'DocSecurity').text = '0'
    SubElement(root, 'ScaleCrop').text = 'false'
    SubElement(root, 'Company')
    SubElement(root, 'LinksUpToDate').text = 'false'
    SubElement(root, 'SharedDoc').text = 'false'
    SubElement(root, 'HyperlinksChanged').text = 'false'
    SubElement(root, 'AppVersion').text = '12.0000'

    # heading pairs part
    heading_pairs = SubElement(root, 'HeadingPairs')
    vector = SubElement(heading_pairs, 'vt:vector',
            {'size': '2', 'baseType': 'variant'})
    variant = SubElement(vector, 'vt:variant')
    SubElement(variant, 'vt:lpstr').text = 'Worksheets'
    variant = SubElement(vector, 'vt:variant')
    SubElement(variant, 'vt:i4').text = '%d' % worksheets_count

    # title of parts
    title_of_parts = SubElement(root, 'TitlesOfParts')
    vector = SubElement(title_of_parts, 'vt:vector',
            {'size': '%d' % worksheets_count, 'baseType': 'lpstr'})
    for ws in workbook.worksheets:
        SubElement(vector, 'vt:lpstr').text = '%s' % ws.title
    return get_document_content(root)


def write_root_rels(workbook):
    """Write the relationships xml."""
    root = Element('Relationships', {'xmlns':
            'http://schemas.openxmlformats.org/package/2006/relationships'})
    SubElement(root, 'Relationship', {'Id': 'rId1', 'Target': ARC_WORKBOOK,
            'Type': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument'})
    SubElement(root, 'Relationship', {'Id': 'rId2', 'Target': ARC_CORE,
            'Type': 'http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties'})
    SubElement(root, 'Relationship', {'Id': 'rId3', 'Target': ARC_APP,
            'Type': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties'})
    return get_document_content(root)


def write_workbook(workbook):
    """Write the core workbook xml."""
    root = Element('workbook', {'xmlns': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
            'xml:space': 'preserve', 'xmlns:r': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'})
    SubElement(root, 'fileVersion', {'appName': 'xl', 'lastEdited': '4',
            'lowestEdited': '4', 'rupBuild': '4505'})
    SubElement(root, 'workbookPr', {'defaultThemeVersion': '124226',
            'codeName': 'ThisWorkbook'})
    book_views = SubElement(root, 'bookViews')
    SubElement(book_views, 'workbookView', {'activeTab': '%d' % workbook.get_index(workbook.get_active_sheet()),
            'autoFilterDateGrouping': '1', 'firstSheet': '0', 'minimized': '0',
            'showHorizontalScroll': '1', 'showSheetTabs': '1',
            'showVerticalScroll': '1', 'tabRatio': '600',
            'visibility': 'visible'})
    # worksheets
    sheets = SubElement(root, 'sheets')
    for i, sheet in enumerate(workbook.worksheets):
        sheet_node = SubElement(sheets, 'sheet', {'name': sheet.title,
                'sheetId': '%d' % (i + 1), 'r:id': 'rId%d' % (i + 1)})
        if not sheet.sheet_state == sheet.SHEETSTATE_VISIBLE:
            sheet_node.set('state', sheet.sheet_state)

    # Defined names
    defined_names = SubElement(root, 'definedNames')
    # named ranges
    for named_range in workbook.get_named_ranges():
        name = SubElement(defined_names, 'definedName',
                {'name': named_range.name})
        if named_range.scope:
            name.set('localSheetId', '%s' % workbook.get_index(named_range.scope))

        if isinstance(named_range, NamedRange):
            # as there can be many cells in one range, generate the list of ranges
            dest_cells = []
            for worksheet, range_name in named_range.destinations:
                dest_cells.append("'%s'!%s" % (worksheet.title.replace("'", "''"),
                absolute_coordinate(range_name)))

            # finally write the cells list
            name.text = ','.join(dest_cells)
        else:
            assert isinstance(named_range, NamedRangeContainingValue)
            name.text = named_range.value

    # autoFilter
    for i, sheet in enumerate(workbook.worksheets):
        #continue
        auto_filter = sheet.auto_filter
        if not auto_filter:
            continue
        name = SubElement(defined_names, 'definedName',
                dict(name='_xlnm._FilterDatabase',
                     localSheetId=str(i),
                     hidden='1'))
        name.text = "'%s'!%s" % (sheet.title.replace("'", "''"),
                                  absolute_coordinate(auto_filter))

    SubElement(root, 'calcPr', {'calcId': '124519', 'calcMode': 'auto',
            'fullCalcOnLoad': '1'})
    return get_document_content(root)


def write_workbook_rels(workbook):
    """Write the workbook relationships xml."""
    root = Element('Relationships', {'xmlns':
            'http://schemas.openxmlformats.org/package/2006/relationships'})
    for i in range(len(workbook.worksheets)):
        SubElement(root, 'Relationship', {'Id': 'rId%d' % (i + 1),
                'Type': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet',
                'Target': 'worksheets/sheet%s.xml' % (i + 1)})
    rid = len(workbook.worksheets) + 1
    SubElement(root, 'Relationship',
            {'Id': 'rId%d' % rid, 'Target': 'sharedStrings.xml',
            'Type': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings'})
    SubElement(root, 'Relationship',
            {'Id': 'rId%d' % (rid + 1), 'Target': 'styles.xml',
            'Type': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles'})
    SubElement(root, 'Relationship',
            {'Id': 'rId%d' % (rid + 2), 'Target': 'theme/theme1.xml',
            'Type': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme'})
    if workbook.vba_archive:
        SubElement(root, 'Relationship',
                   {'Id': 'rId%d' % (rid + 3), 'Target': 'vbaProject.bin',
                    'Type': 'http://schemas.microsoft.com/office/2006/relationships/vbaProject'})
    return get_document_content(root)