def write_comments(self): # produce xml root = Element("{%s}comments" % SHEET_MAIN_NS) authorlist_tag = SubElement(root, "{%s}authors" % SHEET_MAIN_NS) for author in self.authors: leaf = SubElement(authorlist_tag, "{%s}author" % SHEET_MAIN_NS) leaf.text = author commentlist_tag = SubElement(root, "{%s}commentList" % SHEET_MAIN_NS) for comment in self.comments: attrs = { 'ref': comment._parent.coordinate, 'authorId': '%d' % self.authors.index(comment.author), 'shapeId': '0' } comment_tag = SubElement(commentlist_tag, "{%s}comment" % SHEET_MAIN_NS, attrs) text_tag = SubElement(comment_tag, "{%s}text" % SHEET_MAIN_NS) run_tag = SubElement(text_tag, "{%s}r" % SHEET_MAIN_NS) SubElement(run_tag, "{%s}rPr" % SHEET_MAIN_NS) t_tag = SubElement(run_tag, "{%s}t" % SHEET_MAIN_NS) t_tag.text = comment.text return tostring(root)
def write_cell(worksheet, cell, styled=None): coordinate = cell.coordinate attributes = {'r': coordinate} if styled: attributes['s'] = '%d' % cell.style_id if cell.data_type != 'f': attributes['t'] = cell.data_type value = cell._value el = Element("c", attributes) if value is None or value == "": return el if cell.data_type == 'f': shared_formula = worksheet.formula_attributes.get(coordinate, {}) if (shared_formula.get('t') == 'shared' and 'ref' not in shared_formula): value = None formula = SubElement(el, 'f', shared_formula) if value is not None: formula.text = value[1:] value = None if cell.data_type == 's': value = worksheet.parent.shared_strings.add(value) cell_content = SubElement(el, 'v') if value is not None: cell_content.text = safe_string(value) return el
def etree_write_cell(xf, worksheet, cell, styled=None): value, attributes = _set_attributes(cell, styled) el = Element("c", attributes) if value is None or value == "": xf.write(el) return if cell.data_type == 'f': shared_formula = worksheet.formula_attributes.get(cell.coordinate, {}) formula = SubElement(el, 'f', shared_formula) if value is not None: formula.text = value[1:] value = None if cell.data_type == 's': inline_string = SubElement(el, 'is') text = SubElement(inline_string, 't') text.text = value whitespace(text) else: cell_content = SubElement(el, 'v') if value is not None: cell_content.text = safe_string(value) xf.write(el)
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 etree_write_cell(xf, worksheet, cell, styled=None): coordinate = cell.coordinate attributes = {'r': coordinate} if styled: attributes['s'] = '%d' % cell.style_id if cell.data_type != 'f': attributes['t'] = cell.data_type value = cell._value if cell.data_type == "d": if cell.parent.parent.iso_dates: if isinstance(value, timedelta): value = days_to_time(value) value = value.isoformat() else: attributes['t'] = "n" value = to_excel(value, worksheet.parent.epoch) if cell._comment is not None: comment = CommentRecord.from_cell(cell) worksheet._comments.append(comment) el = Element("c", attributes) if value is None or value == "": xf.write(el) return if cell.data_type == 'f': shared_formula = worksheet.formula_attributes.get(coordinate, {}) formula = SubElement(el, 'f', shared_formula) if value is not None: formula.text = value[1:] value = None if cell.data_type == 's': value = worksheet.parent.shared_strings.add(value) cell_content = SubElement(el, 'v') if value is not None: cell_content.text = safe_string(value) if cell.hyperlink: worksheet._hyperlinks.append(cell.hyperlink) xf.write(el)
def etree_write_cell(xf, worksheet, cell, styled=None): coordinate = cell.coordinate attributes = {'r': coordinate} if styled: attributes['s'] = '%d' % cell.style_id if cell.data_type != 'f': attributes['t'] = cell.data_type value = cell._value if cell.data_type == "d": if cell.parent.parent.iso_dates: if isinstance(value, timedelta): value = days_to_time(value) value = value.isoformat() else: attributes['t'] = "n" value = to_excel(value) if cell._comment is not None: comment = CommentRecord.from_cell(cell) worksheet._comments.append(comment) el = Element("c", attributes) if value is None or value == "": xf.write(el) return if cell.data_type == 'f': shared_formula = worksheet.formula_attributes.get(coordinate, {}) formula = SubElement(el, 'f', shared_formula) if value is not None: formula.text = value[1:] value = None if cell.data_type == 's': value = worksheet.parent.shared_strings.add(value) cell_content = SubElement(el, 'v') if value is not None: cell_content.text = safe_string(value) if cell.hyperlink: worksheet._hyperlinks.append(cell.hyperlink) xf.write(el)
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_comments(self): # produce xml root = Element("{%s}comments" % SHEET_MAIN_NS) authorlist_tag = SubElement(root, "{%s}authors" % SHEET_MAIN_NS) for author in self.authors: leaf = SubElement(authorlist_tag, "{%s}author" % SHEET_MAIN_NS) leaf.text = author commentlist_tag = SubElement(root, "{%s}commentList" % SHEET_MAIN_NS) for comment in self.comments: attrs = {'ref': comment._parent.coordinate, 'authorId': self.author_to_id[comment.author], 'shapeId': '0'} comment_tag = SubElement(commentlist_tag, "{%s}comment" % SHEET_MAIN_NS, attrs) text_tag = SubElement(comment_tag, "{%s}text" % SHEET_MAIN_NS) run_tag = SubElement(text_tag, "{%s}r" % SHEET_MAIN_NS) SubElement(run_tag, "{%s}rPr" % SHEET_MAIN_NS) t_tag = SubElement(run_tag, "{%s}t" % SHEET_MAIN_NS) t_tag.text = comment.text return get_document_content(root)
def etree_write_cell(xf, worksheet, cell, styled=None): value, attributes = _set_attributes(cell, styled) el = Element("c", attributes) if value is None or value == "": xf.write(el) return if cell.data_type == 'f': attrib = {} if isinstance(value, ArrayFormula): attrib = dict(value) value = value.text elif isinstance(value, DataTableFormula): attrib = dict(value) value = None formula = SubElement(el, 'f', attrib) if value is not None and not attrib.get('t') == "dataTable": formula.text = value[1:] value = None if cell.data_type == 's': inline_string = SubElement(el, 'is') text = SubElement(inline_string, 't') text.text = value whitespace(text) else: cell_content = SubElement(el, 'v') if value is not None: cell_content.text = safe_string(value) xf.write(el)
def write_cell(worksheet, cell, styled=None): coordinate = cell.coordinate attributes = {'r': coordinate} if styled: attributes['s'] = '%d' % cell.style_id if cell.data_type != 'f': attributes['t'] = cell.data_type value = cell._value if cell._comment is not None: comment = CommentRecord._adapted(cell.comment, cell.coordinate) worksheet._comments.append(comment) el = Element("c", attributes) if value is None or value == "": return el if cell.data_type == 'f': shared_formula = worksheet.formula_attributes.get(coordinate, {}) formula = SubElement(el, 'f', shared_formula) if value is not None: formula.text = value[1:] value = None if cell.data_type == 's': value = worksheet.parent.shared_strings.add(value) cell_content = SubElement(el, 'v') if value is not None: cell_content.text = safe_string(value) if cell.hyperlink: worksheet._hyperlinks.append(cell.hyperlink) return el
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_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') if key.strip() != key: el.set(PRESERVE_SPACE, 'preserve') text = SubElement(el, 't') text.text = key xf.write(el) return out.getvalue()
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)
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': 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)