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_sheetviews(worksheet): views = Element('sheetViews') sheetviewAttrs = {'workbookViewId': '0'} if not worksheet.show_gridlines: sheetviewAttrs['showGridLines'] = '0' view = SubElement(views, 'sheetView', sheetviewAttrs) selectionAttrs = {} topLeftCell = worksheet.freeze_panes if topLeftCell: colName, row = coordinate_from_string(topLeftCell) column = column_index_from_string(colName) pane = 'topRight' paneAttrs = {} if column > 1: paneAttrs['xSplit'] = str(column - 1) if row > 1: paneAttrs['ySplit'] = str(row - 1) pane = 'bottomLeft' if column > 1: pane = 'bottomRight' paneAttrs.update(dict(topLeftCell=topLeftCell, activePane=pane, state='frozen')) view.append(Element('pane', paneAttrs)) selectionAttrs['pane'] = pane if row > 1 and column > 1: SubElement(view, 'selection', {'pane': 'topRight'}) SubElement(view, 'selection', {'pane': 'bottomLeft'}) selectionAttrs.update({'activeCell': worksheet.active_cell, 'sqref': worksheet.selected_cell}) SubElement(view, 'selection', selectionAttrs) return views
def _write_borders(self): """Write the child elements for an individual border section""" borders_node = SubElement(self._root, 'borders', count="%d" % len(self.borders)) for border in self.borders: borders_node.append(border.to_tree())
def _write_differential_styles(self): dxfs = SubElement(self._root, "dxfs", count=str(len(self.wb._differential_styles))) for fmt in self.wb._differential_styles: dxfs.append(fmt.to_tree()) return dxfs
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_external_link(links): """Serialise links to ranges in a single external worbook""" root = Element("{%s}externalLink" % SHEET_MAIN_NS) book = SubElement(root, "{%s}externalBook" % SHEET_MAIN_NS, {'{%s}id' % REL_NS:'rId1'}) external_ranges = SubElement(book, "{%s}definedNames" % SHEET_MAIN_NS) for l in links: external_ranges.append(Element("{%s}definedName" % SHEET_MAIN_NS, dict(l))) return 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_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_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.wb._cell_styles)) for style in self.wb._cell_styles: node = style.to_tree() cell_xfs.append(node) if style.applyAlignment: al = self.wb._alignments[style.alignmentId] el = al.to_tree() node.append(el) if style.applyProtection: prot = self.wb._protections[style.protectionId] el = prot.to_tree() node.append(el)
def write_autofilter(worksheet): auto_filter = worksheet.auto_filter if auto_filter.ref is None: return el = Element('autoFilter', ref=auto_filter.ref) if (auto_filter.filter_columns or auto_filter.sort_conditions): for col_id, filter_column in sorted( auto_filter.filter_columns.items()): fc = SubElement(el, 'filterColumn', colId=str(col_id)) attrs = {} if filter_column.blank: attrs = {'blank': '1'} flt = SubElement(fc, 'filters', attrs) for val in filter_column.vals: flt.append(Element('filter', val=val)) if auto_filter.sort_conditions: srt = SubElement(el, 'sortState', ref=auto_filter.ref) for sort_condition in auto_filter.sort_conditions: sort_attr = {'ref': sort_condition.ref} if sort_condition.descending: sort_attr['descending'] = '1' srt.append(Element('sortCondtion', sort_attr)) return el
def write_autofilter(worksheet): auto_filter = worksheet.auto_filter if auto_filter.ref is None: return el = Element('autoFilter', ref=auto_filter.ref) if (auto_filter.filter_columns or auto_filter.sort_conditions): for col_id, filter_column in sorted(auto_filter.filter_columns.items()): fc = SubElement(el, 'filterColumn', colId=str(col_id)) attrs = {} if filter_column.blank: attrs = {'blank': '1'} flt = SubElement(fc, 'filters', attrs) for val in filter_column.vals: flt.append(Element('filter', val=val)) if auto_filter.sort_conditions: srt = SubElement(el, 'sortState', ref=auto_filter.ref) for sort_condition in auto_filter.sort_conditions: sort_attr = {'ref': sort_condition.ref} if sort_condition.descending: sort_attr['descending'] = '1' srt.append(Element('sortCondtion', sort_attr)) return 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_fills(self): fills_node = SubElement(self._root, 'fills', count="%d" % len(self.fills)) for fill in self.fills: fills_node.append(fill.to_tree())
def _write_conditional_styles(self): dxfs = SubElement(self._root, "dxfs", count=str(len(self.wb.conditional_formats))) for fmt in self.wb.conditional_formats: dxfs.append(fmt.to_tree()) return dxfs
def _write_fonts(self): fonts_node = SubElement(self._root, 'fonts', count="%d" % len(self.fonts)) for font in self.fonts: fonts_node.append(font.to_tree())