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