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(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)
