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_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_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_string_table(string_table): """Write the string table xml.""" out = BytesIO() with xmlfile(out) as xf: with xf.element("sst", xmlns=SHEET_MAIN_NS, uniqueCount="%d" % len(string_table)): for key in string_table: el = Element('si') text = SubElement(el, 't') text.text = key if key.strip() != key: text.set(PRESERVE_SPACE, 'preserve') xf.write(el) return out.getvalue()
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_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_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): sheet_node = SubElement( sheets, '{%s}sheet' % SHEET_MAIN_NS, { 'name': sheet.title, 'sheetId': '%d' % (i + 1), '{%s}id' % REL_NS: 'rId%d' % (i + 1) }) if not sheet.sheet_state == sheet.SHEETSTATE_VISIBLE: sheet_node.set('state', sheet.sheet_state) # Defined names defined_names = SubElement(root, '{%s}definedNames' % SHEET_MAIN_NS) # Defined names -> named ranges for named_range in workbook.get_named_ranges(): name = SubElement(defined_names, '{%s}definedName' % SHEET_MAIN_NS, {'name': named_range.name}) if named_range.scope: name.set('localSheetId', '%s' % workbook.get_index(named_range.scope)) if isinstance(named_range, NamedRange): # as there can be many cells in one range, generate the list of ranges dest_cells = [] for worksheet, range_name in named_range.destinations: dest_cells.append("'%s'!%s" % (worksheet.title.replace( "'", "''"), absolute_coordinate(range_name))) # finally write the cells list name.text = ','.join(dest_cells) else: assert isinstance(named_range, NamedRangeContainingValue) name.text = named_range.value # Defined names -> autoFilter for i, sheet in enumerate(workbook.worksheets): #continue 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_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): sheet_node = SubElement( sheets, '{%s}sheet' % SHEET_MAIN_NS, {'name': sheet.title, 'sheetId': '%d' % (i + 1), '{%s}id' % REL_NS: 'rId%d' % (i + 1)}) if not sheet.sheet_state == sheet.SHEETSTATE_VISIBLE: sheet_node.set('state', sheet.sheet_state) # Defined names defined_names = SubElement(root, '{%s}definedNames' % SHEET_MAIN_NS) # Defined names -> named ranges for named_range in workbook.get_named_ranges(): name = SubElement(defined_names, '{%s}definedName' % SHEET_MAIN_NS, {'name': named_range.name}) if named_range.scope: name.set('localSheetId', '%s' % workbook.get_index(named_range.scope)) if isinstance(named_range, NamedRange): # as there can be many cells in one range, generate the list of ranges dest_cells = [] for worksheet, range_name in named_range.destinations: dest_cells.append("'%s'!%s" % (worksheet.title.replace("'", "''"), absolute_coordinate(range_name))) # finally write the cells list name.text = ','.join(dest_cells) else: assert isinstance(named_range, NamedRangeContainingValue) name.text = named_range.value # Defined names -> autoFilter for i, sheet in enumerate(workbook.worksheets): #continue 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 get_document_content(root)