Exemple #1
0
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)
Exemple #2
0
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)
Exemple #3
0
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)
    # named ranges
    defined_names = SubElement(root, 'definedNames')
    for named_range in workbook.get_named_ranges():
        name = SubElement(defined_names, 'definedName',
                {'name': named_range.name})

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

        # for local ranges, we must check all the cells belong to the same sheet
        base_id = cell_ids[0]
        if named_range.local_only and all([x == base_id for x in cell_ids]):
            name.set('localSheetId', '%s' % base_id)

        # finally write the cells list
        name.text = ','.join(dest_cells)

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