def parse(self):
src = self.archive.read(self.workbook_part_name)
node = fromstring(src)
package = WorkbookPackage.from_tree(node)
if package.properties.date1904:
self.wb.excel_base_date = CALENDAR_MAC_1904
self.wb.code_name = package.properties.codeName
self.wb.active = package.active
self.wb.views = package.bookViews
self.sheets = package.sheets
self.wb.calculation = package.calcPr
self.caches = package.pivotCaches
#external links contain cached worksheets and can be very big
if not self.wb.keep_links:
package.externalReferences = []
for ext_ref in package.externalReferences:
rel = self.rels[ext_ref.id]
self.wb._external_links.append(
read_external_link(self.archive, rel.Target)
)
if package.definedNames:
package.definedNames._cleanup()
self.wb.defined_names = package.definedNames
self.wb.security = package.workbookProtection
def parse(self):
src = self.archive.read(self.workbook_part_name)
node = fromstring(src)
package = WorkbookPackage.from_tree(node)
if package.properties.date1904:
self.wb.excel_base_date = CALENDAR_MAC_1904
self.wb.code_name = package.properties.codeName
self.wb.active = package.active
self.wb.views = package.bookViews
self.sheets = package.sheets
self.wb.calculation = package.calcPr
self.caches = package.pivotCaches
#external links contain cached worksheets and can be very big
if not self.wb.keep_links:
package.externalReferences = []
for ext_ref in package.externalReferences:
rel = self.rels[ext_ref.id]
self.wb._external_links.append(
read_external_link(self.archive, rel.Target))
if package.definedNames:
package.definedNames._cleanup()
self.wb.defined_names = package.definedNames
self.wb.security = package.workbookProtection
def parse(self):
src = self.archive.read(ARC_WORKBOOK)
node = fromstring(src)
package = WorkbookPackage.from_tree(node)
if package.properties.date1904:
wb.excel_base_date = CALENDAR_MAC_1904
self.wb.code_name = package.properties.codeName
self.wb.active = package.active
self.sheets = package.sheets
for ext_ref in package.externalReferences:
rel = self.rels[ext_ref.id]
self.wb._external_links.append(read_external_link(self.archive,
rel.Target))
if package.definedNames:
self.wb.defined_names = package.definedNames
def test_broken_sheet_ref(self, datadir, recwarn, WorkbookParser):
from openpyxl.workbook.parser import WorkbookPackage
datadir.chdir()
with open("workbook_missing_id.xml", "rb") as src:
xml = src.read()
node = fromstring(xml)
wb = WorkbookPackage.from_tree(node)
archive = ZipFile(BytesIO(), "a")
archive.write("workbook_links.xml", ARC_WORKBOOK)
archive.writestr(ARC_WORKBOOK_RELS, b"<root />")
parser = WorkbookParser(archive, ARC_WORKBOOK)
parser.sheets = wb.sheets
sheets = parser.find_sheets()
list(sheets)
w = recwarn.pop()
assert issubclass(w.category, UserWarning)
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())
