def write_worksheet(worksheet): """Write a worksheet to an xml file.""" ws = worksheet ws._rels = RelationshipList() ws._hyperlinks = [] out = BytesIO() with xmlfile(out) as xf: with xf.element('worksheet', xmlns=SHEET_MAIN_NS): props = ws.sheet_properties.to_tree() xf.write(props) dim = SheetDimension(ref=ws.calculate_dimension()) xf.write(dim.to_tree()) xf.write(ws.views.to_tree()) cols = ws.column_dimensions.to_tree() ws.sheet_format.outlineLevelCol = ws.column_dimensions.max_outline xf.write(ws.sheet_format.to_tree()) if cols is not None: xf.write(cols) # write data write_rows(xf, ws) if ws.protection: xf.write(ws.protection.to_tree()) if ws.auto_filter: xf.write(ws.auto_filter.to_tree()) if ws.sort_state: xf.write(ws.sort_state.to_tree()) merge = write_mergecells(ws) if merge is not None: xf.write(merge) cfs = write_conditional_formatting(ws) for cf in cfs: xf.write(cf) if ws.data_validations: xf.write(ws.data_validations.to_tree()) hyper = write_hyperlinks(ws) if hyper: xf.write(hyper.to_tree()) options = ws.print_options if dict(options): new_element = options.to_tree() xf.write(new_element) margins = ws.page_margins.to_tree() xf.write(margins) setup = ws.page_setup if dict(setup): new_element = setup.to_tree() xf.write(new_element) if bool(ws.HeaderFooter): xf.write(ws.HeaderFooter.to_tree()) if ws.page_breaks: xf.write(ws.page_breaks.to_tree()) drawing = write_drawing(ws) if drawing is not None: xf.write(drawing) # if there is an existing vml file associated with this sheet or if there # are any comments we need to add a legacyDrawing relation to the vml file. if (ws.legacy_drawing is not None or ws._comments): legacyDrawing = Related(id="anysvml") xml = legacyDrawing.to_tree("legacyDrawing") xf.write(xml) tables = _add_table_headers(ws) if tables: xf.write(tables.to_tree()) xml = out.getvalue() out.close() return xml
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 read_worksheets(self): for sheet, rel in self.parser.find_sheets(): if rel.target not in self.valid_files: continue if "chartsheet" in rel.Type: self.read_chartsheet(sheet, rel) continue rels_path = get_rels_path(rel.target) rels = RelationshipList() if rels_path in self.valid_files: rels = get_dependents(self.archive, rels_path) if self.read_only: ws = ReadOnlyWorksheet(self.wb, sheet.name, rel.target, self.shared_strings) self.wb._sheets.append(ws) continue else: fh = self.archive.open(rel.target) ws = self.wb.create_sheet(sheet.name) ws._rels = rels ws_parser = WorksheetReader(ws, fh, self.shared_strings, self.data_only) ws_parser.bind_all() # assign any comments to cells for r in rels.find(COMMENTS_NS): src = self.archive.read(r.target) comment_sheet = CommentSheet.from_tree(fromstring(src)) for ref, comment in comment_sheet.comments: ws[ref].comment = comment # preserve link to VML file if VBA if self.wb.vba_archive and ws.legacy_drawing: ws.legacy_drawing = rels[ws.legacy_drawing].target for t in ws_parser.tables: src = self.archive.read(t) xml = fromstring(src) table = Table.from_tree(xml) ws.add_table(table) drawings = rels.find(SpreadsheetDrawing._rel_type) for rel in drawings: charts, images = find_images(self.archive, rel.target) for c in charts: ws.add_chart(c, c.anchor) for im in images: ws.add_image(im, im.anchor) pivot_rel = rels.find(TableDefinition.rel_type) for r in pivot_rel: pivot_path = r.Target src = self.archive.read(pivot_path) tree = fromstring(src) pivot = TableDefinition.from_tree(tree) pivot.cache = self.parser.pivot_caches[pivot.cacheId] ws.add_pivot(pivot) ws.sheet_state = sheet.state
def _write_rels(self): rels = RelationshipList() rels.Relationship = self._rels return rels.to_tree()
def read_worksheets(self): comment_warning = """Cell '{0}':{1} is part of a merged range but has a comment which will be removed because merged cells cannot contain any data.""" for sheet, rel in self.parser.find_sheets(): if rel.target not in self.valid_files: continue if "chartsheet" in rel.Type: self.read_chartsheet(sheet, rel) continue rels_path = get_rels_path(rel.target) rels = RelationshipList() if rels_path in self.valid_files: rels = get_dependents(self.archive, rels_path) if self.read_only: ws = ReadOnlyWorksheet(self.wb, sheet.name, rel.target, self.shared_strings) ws.sheet_state = sheet.state self.wb._sheets.append(ws) continue else: fh = self.archive.open(rel.target) ws = self.wb.create_sheet(sheet.name) ws._rels = rels ws_parser = WorksheetReader(ws, fh, self.shared_strings, self.data_only) ws_parser.bind_all() # assign any comments to cells for r in rels.find(COMMENTS_NS): src = self.archive.read(r.target) comment_sheet = CommentSheet.from_tree(fromstring(src)) for ref, comment in comment_sheet.comments: try: ws[ref].comment = comment except AttributeError: c = ws[ref] if isinstance(c, MergedCell): warnings.warn( comment_warning.format(ws.title, c.coordinate)) continue # preserve link to VML file if VBA if self.wb.vba_archive and ws.legacy_drawing: ws.legacy_drawing = rels[ws.legacy_drawing].target else: ws.legacy_drawing = None for t in ws_parser.tables: src = self.archive.read(t) xml = fromstring(src) table = Table.from_tree(xml) ws.add_table(table) drawings = rels.find(SpreadsheetDrawing._rel_type) for rel in drawings: charts, images = find_images(self.archive, rel.target) for c in charts: ws.add_chart(c, c.anchor) for im in images: ws.add_image(im, im.anchor) pivot_rel = rels.find(TableDefinition.rel_type) for r in pivot_rel: pivot_path = r.Target src = self.archive.read(pivot_path) tree = fromstring(src) pivot = TableDefinition.from_tree(tree) pivot.cache = self.parser.pivot_caches[pivot.cacheId] ws.add_pivot(pivot) ws.sheet_state = sheet.state