def write_workbook(workbook):
"""Write the core workbook xml."""
root = Element('{%s}workbook' % SHEET_MAIN_NS)
if LXML:
_nsmap = {'r':REL_NS}
root = Element('{%s}workbook' % SHEET_MAIN_NS, nsmap=_nsmap)
wb_props = {}
if workbook.code_name is not None:
wb_props['codeName'] = workbook.code_name
SubElement(root, '{%s}workbookPr' % SHEET_MAIN_NS, wb_props)
# book views
book_views = SubElement(root, '{%s}bookViews' % SHEET_MAIN_NS)
SubElement(book_views, '{%s}workbookView' % SHEET_MAIN_NS,
{'activeTab': '%d' % workbook._active_sheet_index}
)
# worksheets
sheets = SubElement(root, '{%s}sheets' % SHEET_MAIN_NS)
for idx, sheet in enumerate(workbook.worksheets + workbook.chartsheets, 1):
sheet_node = SubElement(
sheets, '{%s}sheet' % SHEET_MAIN_NS,
{'name': sheet.title, 'sheetId': '%d' % idx,
'{%s}id' % REL_NS: 'rId%d' % idx})
if not sheet.sheet_state == 'visible':
if len(workbook._sheets) == 1:
raise ValueError("The only worksheet of a workbook cannot be hidden")
sheet_node.set('state', sheet.sheet_state)
# external references
if getattr(workbook, '_external_links', []):
external_references = SubElement(root, '{%s}externalReferences' % SHEET_MAIN_NS)
# need to match a counter with a workbook's relations
counter = len(workbook.worksheets) + 3 # strings, styles, theme
if workbook.vba_archive:
counter += 1
for idx, _ in enumerate(workbook._external_links, counter+1):
ext = Element("{%s}externalReference" % SHEET_MAIN_NS, {"{%s}id" % REL_NS:"rId%d" % idx})
external_references.append(ext)
# Defined names
defined_names = SubElement(root, '{%s}definedNames' % SHEET_MAIN_NS)
_write_defined_names(workbook, defined_names)
# Defined names -> autoFilter
for i, sheet in enumerate(workbook.worksheets):
auto_filter = sheet.auto_filter.ref
if not auto_filter:
continue
name = SubElement(
defined_names, '{%s}definedName' % SHEET_MAIN_NS,
dict(name='_xlnm._FilterDatabase', localSheetId=str(i), hidden='1'))
name.text = "'%s'!%s" % (sheet.title.replace("'", "''"),
absolute_coordinate(auto_filter))
SubElement(root, '{%s}calcPr' % SHEET_MAIN_NS,
{'calcId': '124519', 'fullCalcOnLoad': '1'})
return tostring(root)
def write_workbook(workbook):
"""Write the core workbook xml."""
root = Element("{%s}workbook" % SHEET_MAIN_NS)
if LXML:
_nsmap = {"r": REL_NS}
root = Element("{%s}workbook" % SHEET_MAIN_NS, nsmap=_nsmap)
wb_props = {}
if workbook.code_name is not None:
wb_props["codeName"] = workbook.code_name
SubElement(root, "{%s}workbookPr" % SHEET_MAIN_NS, wb_props)
# book views
book_views = SubElement(root, "{%s}bookViews" % SHEET_MAIN_NS)
SubElement(book_views, "{%s}workbookView" % SHEET_MAIN_NS, {"activeTab": "%d" % workbook._active_sheet_index})
# worksheets
sheets = SubElement(root, "{%s}sheets" % SHEET_MAIN_NS)
for i, sheet in enumerate(workbook.worksheets, 1):
sheet_node = SubElement(
sheets,
"{%s}sheet" % SHEET_MAIN_NS,
{"name": sheet.title, "sheetId": "%d" % i, "{%s}id" % REL_NS: "rId%d" % i},
)
if not sheet.sheet_state == Worksheet.SHEETSTATE_VISIBLE:
if len(workbook.worksheets) == 1:
raise ValueError("The only worksheet of a workbook cannot be hidden")
sheet_node.set("state", sheet.sheet_state)
# external references
if getattr(workbook, "_external_links", []):
external_references = SubElement(root, "{%s}externalReferences" % SHEET_MAIN_NS)
# need to match a counter with a workbook's relations
counter = len(workbook.worksheets) + 3 # strings, styles, theme
if workbook.vba_archive:
counter += 1
for idx, _ in enumerate(workbook._external_links, counter + 1):
ext = Element("{%s}externalReference" % SHEET_MAIN_NS, {"{%s}id" % REL_NS: "rId%d" % idx})
external_references.append(ext)
# Defined names
defined_names = SubElement(root, "{%s}definedNames" % SHEET_MAIN_NS)
_write_defined_names(workbook, defined_names)
# Defined names -> autoFilter
for i, sheet in enumerate(workbook.worksheets):
auto_filter = sheet.auto_filter.ref
if not auto_filter:
continue
name = SubElement(
defined_names,
"{%s}definedName" % SHEET_MAIN_NS,
dict(name="_xlnm._FilterDatabase", localSheetId=str(i), hidden="1"),
)
name.text = "'%s'!%s" % (sheet.title.replace("'", "''"), absolute_coordinate(auto_filter))
SubElement(root, "{%s}calcPr" % SHEET_MAIN_NS, {"calcId": "124519", "fullCalcOnLoad": "1"})
return tostring(root)
def write_workbook(workbook):
"""Write the core workbook xml."""
root = Element('{%s}workbook' % SHEET_MAIN_NS)
SubElement(root, '{%s}fileVersion' % SHEET_MAIN_NS,
{'appName': 'xl', 'lastEdited': '4', 'lowestEdited': '4', 'rupBuild': '4505'})
SubElement(root, '{%s}workbookPr' % SHEET_MAIN_NS,
{'defaultThemeVersion': '124226', 'codeName': 'ThisWorkbook'})
# book views
book_views = SubElement(root, '{%s}bookViews' % SHEET_MAIN_NS)
SubElement(book_views, '{%s}workbookView' % SHEET_MAIN_NS,
{'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, '{%s}sheets' % SHEET_MAIN_NS)
for i, sheet in enumerate(workbook.worksheets, 1):
sheet_node = SubElement(
sheets, '{%s}sheet' % SHEET_MAIN_NS,
{'name': sheet.title, 'sheetId': '%d' % i,
'{%s}id' % REL_NS: 'rId%d' % i })
if not sheet.sheet_state == Worksheet.SHEETSTATE_VISIBLE:
if len(workbook.worksheets) == 1:
raise ValueError("The only worksheet of a workbook cannot be hidden")
sheet_node.set('state', sheet.sheet_state)
# external references
if getattr(workbook, '_external_links', []):
external_references = SubElement(root, '{%s}externalReferences' % SHEET_MAIN_NS)
# need to match a counter with a workbook's relations
counter = len(workbook.worksheets) + 3 # strings, styles, theme
if workbook.vba_archive:
counter += 1
for idx, _ in enumerate(workbook._external_links, counter+1):
ext = Element("{%s}externalReference" % SHEET_MAIN_NS, {"{%s}id" % REL_NS:"rId%d" % idx})
external_references.append(ext)
# Defined names
defined_names = SubElement(root, '{%s}definedNames' % SHEET_MAIN_NS)
_write_defined_names(workbook, defined_names)
# Defined names -> autoFilter
for i, sheet in enumerate(workbook.worksheets):
auto_filter = sheet.auto_filter.ref
if not auto_filter:
continue
name = SubElement(
defined_names, '{%s}definedName' % SHEET_MAIN_NS,
dict(name='_xlnm._FilterDatabase', localSheetId=str(i), hidden='1'))
name.text = "'%s'!%s" % (sheet.title.replace("'", "''"),
absolute_coordinate(auto_filter))
SubElement(root, '{%s}calcPr' % SHEET_MAIN_NS,
{'calcId': '124519', 'calcMode': 'auto', 'fullCalcOnLoad': '1'})
return tostring(root)
def write_string_table(string_table):
"""Write the string table xml."""
out = BytesIO()
with xmlfile(out) as xf:
with xf.element("sst", xmlns=SHEET_MAIN_NS, uniqueCount="%d" % len(string_table)):
for key in string_table:
el = Element('si')
text = SubElement(el, 't')
text.text = key
if key.strip() != key:
text.set(PRESERVE_SPACE, 'preserve')
xf.write(el)
return out.getvalue()
def write_string_table(string_table):
"""Write the string table xml."""
out = BytesIO()
with xmlfile(out) as xf:
with xf.element("sst", xmlns=SHEET_MAIN_NS, uniqueCount="%d" % len(string_table)):
for key in string_table:
el = Element('si')
text = SubElement(el, 't')
text.text = key
if key.strip() != key:
text.set(PRESERVE_SPACE, 'preserve')
xf.write(el)
return out.getvalue()
def _write_cell_styles(self):
""" write styles combinations based on ids found in tables """
# writing the cellXfs
cell_xfs = SubElement(self._root, 'cellXfs',
count='%d' % len(self.styles))
# default
def _get_default_vals():
return dict(numFmtId='0', fontId='0', fillId='0',
xfId='0', borderId='0')
for st in self.styles:
vals = _get_default_vals()
if st.font != 0:
vals['fontId'] = "%d" % (st.font)
vals['applyFont'] = '1'
if st.border != 0:
vals['borderId'] = "%d" % (st.border)
vals['applyBorder'] = '1'
if st.fill != 0:
vals['fillId'] = "%d" % (st.fill)
vals['applyFill'] = '1'
if st.number_format != 0:
vals['numFmtId'] = '%d' % st.number_format
vals['applyNumberFormat'] = '1'
node = SubElement(cell_xfs, 'xf', vals)
if st.alignment != 0:
node.set("applyProtection", '1')
al = self.alignments[st.alignment]
el = al.to_tree()
node.append(el)
if st.protection != 0:
node.set('applyProtection', '1')
prot = self.protections[st.protection]
el = prot.to_tree()
node.append(el)
def write_workbook(workbook):
"""Write the core workbook xml."""
root = Element('{%s}workbook' % SHEET_MAIN_NS)
SubElement(
root, '{%s}fileVersion' % SHEET_MAIN_NS, {
'appName': 'xl',
'lastEdited': '4',
'lowestEdited': '4',
'rupBuild': '4505'
})
SubElement(root, '{%s}workbookPr' % SHEET_MAIN_NS, {
'defaultThemeVersion': '124226',
'codeName': workbook.code_name
})
# book views
book_views = SubElement(root, '{%s}bookViews' % SHEET_MAIN_NS)
SubElement(
book_views, '{%s}workbookView' % SHEET_MAIN_NS, {
'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, '{%s}sheets' % SHEET_MAIN_NS)
for i, sheet in enumerate(workbook.worksheets, 1):
sheet_node = SubElement(
sheets, '{%s}sheet' % SHEET_MAIN_NS, {
'name': sheet.title,
'sheetId': '%d' % i,
'{%s}id' % REL_NS: 'rId%d' % i
})
if not sheet.sheet_state == Worksheet.SHEETSTATE_VISIBLE:
if len(workbook.worksheets) == 1:
raise ValueError(
"The only worksheet of a workbook cannot be hidden")
sheet_node.set('state', sheet.sheet_state)
# external references
if getattr(workbook, '_external_links', []):
external_references = SubElement(
root, '{%s}externalReferences' % SHEET_MAIN_NS)
# need to match a counter with a workbook's relations
counter = len(workbook.worksheets) + 3 # strings, styles, theme
if workbook.vba_archive:
counter += 1
for idx, _ in enumerate(workbook._external_links, counter + 1):
ext = Element("{%s}externalReference" % SHEET_MAIN_NS,
{"{%s}id" % REL_NS: "rId%d" % idx})
external_references.append(ext)
# Defined names
defined_names = SubElement(root, '{%s}definedNames' % SHEET_MAIN_NS)
_write_defined_names(workbook, defined_names)
# Defined names -> autoFilter
for i, sheet in enumerate(workbook.worksheets):
auto_filter = sheet.auto_filter.ref
if not auto_filter:
continue
name = SubElement(
defined_names, '{%s}definedName' % SHEET_MAIN_NS,
dict(name='_xlnm._FilterDatabase', localSheetId=str(i),
hidden='1'))
name.text = "'%s'!%s" % (sheet.title.replace(
"'", "''"), absolute_coordinate(auto_filter))
SubElement(root, '{%s}calcPr' % SHEET_MAIN_NS, {
'calcId': '124519',
'calcMode': 'auto',
'fullCalcOnLoad': '1'
})
return tostring(root)
def write_workbook(workbook):
"""Write the core workbook xml."""
root = Element('{%s}workbook' % SHEET_MAIN_NS)
SubElement(
root, '{%s}fileVersion' % SHEET_MAIN_NS, {
'appName': 'xl',
'lastEdited': '4',
'lowestEdited': '4',
'rupBuild': '4505'
})
SubElement(root, '{%s}workbookPr' % SHEET_MAIN_NS, {
'defaultThemeVersion': '124226',
'codeName': 'ThisWorkbook'
})
# book views
book_views = SubElement(root, '{%s}bookViews' % SHEET_MAIN_NS)
SubElement(
book_views, '{%s}workbookView' % SHEET_MAIN_NS, {
'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, '{%s}sheets' % SHEET_MAIN_NS)
for i, sheet in enumerate(workbook.worksheets):
sheet_node = SubElement(
sheets, '{%s}sheet' % SHEET_MAIN_NS, {
'name': sheet.title,
'sheetId': '%d' % (i + 1),
'{%s}id' % REL_NS: '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, '{%s}definedNames' % SHEET_MAIN_NS)
# Defined names -> named ranges
for named_range in workbook.get_named_ranges():
name = SubElement(defined_names, '{%s}definedName' % SHEET_MAIN_NS,
{'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
# Defined names -> autoFilter
for i, sheet in enumerate(workbook.worksheets):
#continue
auto_filter = sheet.auto_filter.ref
if not auto_filter:
continue
name = SubElement(
defined_names, '{%s}definedName' % SHEET_MAIN_NS,
dict(name='_xlnm._FilterDatabase', localSheetId=str(i),
hidden='1'))
name.text = "'%s'!%s" % (sheet.title.replace(
"'", "''"), absolute_coordinate(auto_filter))
SubElement(root, '{%s}calcPr' % SHEET_MAIN_NS, {
'calcId': '124519',
'calcMode': 'auto',
'fullCalcOnLoad': '1'
})
return tostring(root)
def write_workbook(workbook):
"""Write the core workbook xml."""
root = Element('{%s}workbook' % SHEET_MAIN_NS)
SubElement(root, '{%s}fileVersion' % SHEET_MAIN_NS,
{'appName': 'xl', 'lastEdited': '4', 'lowestEdited': '4', 'rupBuild': '4505'})
SubElement(root, '{%s}workbookPr' % SHEET_MAIN_NS,
{'defaultThemeVersion': '124226', 'codeName': 'ThisWorkbook'})
# book views
book_views = SubElement(root, '{%s}bookViews' % SHEET_MAIN_NS)
SubElement(book_views, '{%s}workbookView' % SHEET_MAIN_NS,
{'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, '{%s}sheets' % SHEET_MAIN_NS)
for i, sheet in enumerate(workbook.worksheets):
sheet_node = SubElement(
sheets, '{%s}sheet' % SHEET_MAIN_NS,
{'name': sheet.title, 'sheetId': '%d' % (i + 1),
'{%s}id' % REL_NS: '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, '{%s}definedNames' % SHEET_MAIN_NS)
# Defined names -> named ranges
for named_range in workbook.get_named_ranges():
name = SubElement(defined_names, '{%s}definedName' % SHEET_MAIN_NS,
{'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
# Defined names -> autoFilter
for i, sheet in enumerate(workbook.worksheets):
#continue
auto_filter = sheet.auto_filter.ref
if not auto_filter:
continue
name = SubElement(
defined_names, '{%s}definedName' % SHEET_MAIN_NS,
dict(name='_xlnm._FilterDatabase', localSheetId=str(i), hidden='1'))
name.text = "'%s'!%s" % (sheet.title.replace("'", "''"),
absolute_coordinate(auto_filter))
SubElement(root, '{%s}calcPr' % SHEET_MAIN_NS,
{'calcId': '124519', 'calcMode': 'auto', 'fullCalcOnLoad': '1'})
return get_document_content(root)
