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 value(self):
dest_cells = []
for ws, xlrange in self.destinations:
dest_cells.append(
"'%s'!%s" %
(ws.title.replace("'", "''"), absolute_coordinate(xlrange)))
return ",".join(dest_cells)
def write_names(self):
defined_names = copy(self.wb.defined_names)
# Defined names -> autoFilter
for idx, sheet in enumerate(self.wb.worksheets):
auto_filter = sheet.auto_filter.ref
if auto_filter:
name = DefinedName(name='_FilterDatabase',
localSheetId=idx,
hidden=True)
name.value = u"{0}!{1}".format(
quote_sheetname(sheet.title),
absolute_coordinate(auto_filter))
defined_names.append(name)
# print titles
if sheet.print_titles:
name = DefinedName(name="Print_Titles", localSheetId=idx)
name.value = ",".join([
u"{0}!{1}".format(quote_sheetname(sheet.title), r)
for r in sheet.print_titles.split(",")
])
defined_names.append(name)
# print areas
if sheet.print_area:
name = DefinedName(name="Print_Area", localSheetId=idx)
name.value = ",".join([
u"{0}!{1}".format(quote_sheetname(sheet.title), r)
for r in sheet.print_area
])
defined_names.append(name)
self.package.definedNames = defined_names
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 print_area(self, value):
"""
Range of cells in the form A1:D4 or list of ranges
"""
if isinstance(value, basestring):
value = [value]
self._print_area = [absolute_coordinate(v) for v in value]
def print_area(self, value):
"""
Range of cells in the form A1:D4 or list of ranges
"""
if isinstance(value, basestring):
value = [value]
self._print_area = [absolute_coordinate(v) for v in value]
def assignname(sourcecell, targetcell):
if sourcecell.value is None:
msg = 'missing range name for ' + sourcecell.parent.title + '!' + sourcecell.coordinate
print(msg)
writelogsheet(sourcecell.parent.parent, msg)
logging.warning('Template has problems: %s' % msg)
#write to log bad range
else:
#check to see if named range is already there
sourcecell.parent.parent.defined_names.delete(
sourcecell.value) #does this crash if name isn't there?
#if it is either delete or edit
#if not add it
newaddress = "'" + targetcell.parent.title + "'" + '!' + absolute_coordinate(
targetcell.coordinate)
#print('cell' + cell.coordinate + ' new address:' + str(newaddress))
newname = defined_name.DefinedName(name=str(sourcecell.value),
attr_text=newaddress)
sourcecell.parent.parent.defined_names.append(newname)
def value(self):
dest_cells = []
for ws, xlrange in self.destinations:
dest_cells.append("'%s'!%s" % (ws.title.replace("'", "''"),
absolute_coordinate(xlrange)))
return ",".join(dest_cells)
def write_workbook(workbook):
"""Write the core workbook xml."""
wb = workbook
wb.rels = RelationshipList()
root = WorkbookPackage()
props = WorkbookProperties() # needs a mapping to the workbook for preservation
if wb.code_name is not None:
props.codeName = wb.code_name
if wb.excel_base_date == CALENDAR_MAC_1904:
props.date1904 = True
root.workbookPr = props
# workbook protection
root.workbookProtection = wb.security
# book views
active = get_active_sheet(wb)
if wb.views:
wb.views[0].activeTab = active
root.bookViews = wb.views
# worksheets
for idx, sheet in enumerate(wb._sheets, 1):
sheet_node = ChildSheet(name=sheet.title, sheetId=idx, id="rId{0}".format(idx))
rel = Relationship(type=sheet._rel_type, Target=sheet.path)
wb.rels.append(rel)
if not sheet.sheet_state == 'visible':
if len(wb._sheets) == 1:
raise ValueError("The only worksheet of a workbook cannot be hidden")
sheet_node.state = sheet.sheet_state
root.sheets.append(sheet_node)
# external references
for link in wb._external_links:
# need to match a counter with a workbook's relations
rId = len(wb.rels) + 1
rel = Relationship(type=link._rel_type, Target=link.path)
wb.rels.append(rel)
ext = ExternalReference(id=rel.id)
root.externalReferences.append(ext)
# Defined names
defined_names = copy(wb.defined_names) # don't add special defns to workbook itself.
# Defined names -> autoFilter
for idx, sheet in enumerate(wb.worksheets):
auto_filter = sheet.auto_filter.ref
if auto_filter:
name = DefinedName(name='_FilterDatabase', localSheetId=idx, hidden=True)
name.value = u"{0}!{1}".format(quote_sheetname(sheet.title),
absolute_coordinate(auto_filter)
)
defined_names.append(name)
# print titles
if sheet.print_titles:
name = DefinedName(name="Print_Titles", localSheetId=idx)
name.value = ",".join([u"{0}!{1}".format(quote_sheetname(sheet.title), r)
for r in sheet.print_titles.split(",")])
defined_names.append(name)
# print areas
if sheet.print_area:
name = DefinedName(name="Print_Area", localSheetId=idx)
name.value = ",".join([u"{0}!{1}".format(quote_sheetname(sheet.title), r)
for r in sheet.print_area])
defined_names.append(name)
root.definedNames = defined_names
# pivots
pivot_caches = set()
for pivot in wb._pivots:
if pivot.cache not in pivot_caches:
pivot_caches.add(pivot.cache)
c = PivotCache(cacheId=pivot.cacheId)
root.pivotCaches.append(c)
rel = Relationship(Type=pivot.cache.rel_type, Target=pivot.cache.path)
wb.rels.append(rel)
c.id = rel.id
wb._pivots = [] # reset
root.calcPr = wb.calculation
return tostring(root.to_tree())
def write_workbook(workbook):
"""Write the core workbook xml."""
wb = workbook
wb.rels = RelationshipList()
root = WorkbookPackage()
props = WorkbookProperties(
) # needs a mapping to the workbook for preservation
if wb.code_name is not None:
props.codeName = wb.code_name
if wb.excel_base_date == CALENDAR_MAC_1904:
props.date1904 = True
root.workbookPr = props
# book views
active = get_active_sheet(wb)
view = BookView(activeTab=active)
root.bookViews = [view]
# worksheets
for idx, sheet in enumerate(wb._sheets, 1):
sheet_node = ChildSheet(name=sheet.title,
sheetId=idx,
id="rId{0}".format(idx))
rel = Relationship(type=sheet._rel_type, Target=sheet.path)
wb.rels.append(rel)
if not sheet.sheet_state == 'visible':
if len(wb._sheets) == 1:
raise ValueError(
"The only worksheet of a workbook cannot be hidden")
sheet_node.state = sheet.sheet_state
root.sheets.append(sheet_node)
# external references
for link in wb._external_links:
# need to match a counter with a workbook's relations
rId = len(wb.rels) + 1
rel = Relationship(type=link._rel_type, Target=link.path)
wb.rels.append(rel)
ext = ExternalReference(id=rel.id)
root.externalReferences.append(ext)
# Defined names
defined_names = copy(
wb.defined_names) # don't add special defns to workbook itself.
# Defined names -> autoFilter
for idx, sheet in enumerate(wb.worksheets):
auto_filter = sheet.auto_filter.ref
if auto_filter:
name = DefinedName(name='_FilterDatabase',
localSheetId=idx,
hidden=True)
name.value = u"{0}!{1}".format(quote_sheetname(sheet.title),
absolute_coordinate(auto_filter))
defined_names.append(name)
# print titles
if sheet.print_titles:
name = DefinedName(name="Print_Titles", localSheetId=idx)
name.value = ",".join([
u"{0}!{1}".format(quote_sheetname(sheet.title), r)
for r in sheet.print_titles.split(",")
])
defined_names.append(name)
# print areas
if sheet.print_area:
name = DefinedName(name="Print_Area", localSheetId=idx)
name.value = ",".join([
u"{0}!{1}".format(quote_sheetname(sheet.title), r)
for r in sheet.print_area
])
defined_names.append(name)
root.definedNames = defined_names
root.calcPr = CalcProperties(calcId=124519, fullCalcOnLoad=True)
return tostring(root.to_tree())
def write_workbook(workbook):
"""Write the core workbook xml."""
wb = workbook
wb.rels = RelationshipList()
root = WorkbookPackage()
props = WorkbookProperties()
if wb.code_name is not None:
props.codeName = wb.code_name
root.workbookPr = props
# book views
active = get_active_sheet(wb)
view = BookView(activeTab=active)
root.bookViews =[view]
# worksheets
for idx, sheet in enumerate(wb._sheets, 1):
sheet_node = ChildSheet(name=sheet.title, sheetId=idx, id="rId{0}".format(idx))
rel = Relationship(
type=sheet._rel_type,
Target='{0}s/{1}'.format(sheet._rel_type, sheet._path)
)
wb.rels.append(rel)
if not sheet.sheet_state == 'visible':
if len(wb._sheets) == 1:
raise ValueError("The only worksheet of a workbook cannot be hidden")
sheet_node.state = sheet.sheet_state
root.sheets.append(sheet_node)
# external references
if wb._external_links:
# need to match a counter with a workbook's relations
rId = len(wb.rels)
for idx, link in enumerate(wb._external_links, 1):
ext = ExternalReference(id="rId{0}".format(rId + idx))
rel = Relationship(type=link._rel_type,
Target='{0}s/{1}'.format(link._rel_type, link._path)
)
root.externalReferences.append(ext)
wb.rels.append(rel)
# Defined names
defined_names = copy(wb.defined_names) # don't add special defns to workbook itself.
# Defined names -> autoFilter
for idx, sheet in enumerate(wb.worksheets):
auto_filter = sheet.auto_filter.ref
if auto_filter:
name = DefinedName(name='_FilterDatabase', localSheetId=idx, hidden=True)
name.value = "{0}!{1}".format(quote_sheetname(sheet.title),
absolute_coordinate(auto_filter)
)
defined_names.append(name)
# print titles
if sheet.print_titles:
name = DefinedName(name="Print_Titles", localSheetId=idx)
name.value = quote_sheetname(sheet.print_titles)
defined_names.append(name)
# print areas
if sheet.print_area:
name = DefinedName(name="Print_Area", localSheetId=idx)
name.value = ",".join(["{0}!{1}".format(quote_sheetname(sheet.title), r)
for r in sheet.print_area])
defined_names.append(name)
root.definedNames = defined_names
root.calcPr = CalcProperties(calcId=124519, fullCalcOnLoad=True)
return tostring(root.to_tree())
