def _write_cell_style(self): cell_styles = SubElement(self._root, 'cellStyles', {'count': '1'}) SubElement(cell_styles, 'cellStyle', { 'name': "Normal", 'xfId': "0", 'builtinId': "0" })
def _write_series_color_node(node, serie): """write series color """ # edge color line = SubElement(node, '{%s}ln' % DRAWING_NS) fill = SubElement(line, '{%s}solidFill' % DRAWING_NS) SubElement(fill, '{%s}srgbClr' % DRAWING_NS, {'val': serie.color})
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_fills(self): fills = SubElement(self._root, 'fills', {'count': '2'}) fill = SubElement(fills, 'fill') SubElement(fill, 'patternFill', {'patternType': 'none'}) fill = SubElement(fills, 'fill') SubElement(fill, 'patternFill', {'patternType': 'gray125'}) return fills
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 _write_border(self, node, border): """Write the child elements for an individual border section""" border_node = SubElement(node, 'border', dict(border)) for tag, elem in border.children: side = SubElement(border_node, tag, dict(elem)) if elem.color is not None: self._write_color(side, elem.color)
def write_comments_vml(self): root = Element("xml") shape_layout = SubElement(root, "{%s}shapelayout" % officens, {"{%s}ext" % vmlns: "edit"}) SubElement(shape_layout, "{%s}idmap" % officens, { "{%s}ext" % vmlns: "edit", "data": "1" }) shape_type = SubElement( root, "{%s}shapetype" % vmlns, { "id": "_x0000_t202", "coordsize": "21600,21600", "{%s}spt" % officens: "202", "path": "m,l,21600r21600,l21600,xe" }) SubElement(shape_type, "{%s}stroke" % vmlns, {"joinstyle": "miter"}) SubElement(shape_type, "{%s}path" % vmlns, { "gradientshapeok": "t", "{%s}connecttype" % officens: "rect" }) for i, comment in enumerate(self.comments, 1026): shape = self._write_comment_shape(comment, i) root.append(shape) return tostring(root)
def write_chart(self): """write chart""" chart = SubElement(self.root, '{%s}chart' % CHART_NS) self._write_title(chart) self._write_layout(chart) self._write_legend(chart) SubElement(chart, '{%s}plotVisOnly' % CHART_NS, {'val': '1'})
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_number_formats(self): number_format_table = {} number_format_list = [] exceptions_list = [] num_fmt_id = 165 # start at a greatly higher value as any builtin can go num_fmt_offset = 0 for style in self._style_list: if not style.number_format in number_format_list : number_format_list.append(style.number_format) for number_format in number_format_list: if number_format.is_builtin(): btin = number_format.builtin_format_id(number_format.format_code) number_format_table[number_format] = btin else: number_format_table[number_format] = num_fmt_id + num_fmt_offset num_fmt_offset += 1 exceptions_list.append(number_format) num_fmts = SubElement(self._root, 'numFmts', {'count':'%d' % len(exceptions_list)}) for number_format in exceptions_list : SubElement(num_fmts, 'numFmt', {'numFmtId':'%d' % number_format_table[number_format], 'formatCode':'%s' % number_format.format_code}) return number_format_table
def _write_fonts(self): """ add fonts part to root return {font.crc => index} """ fonts = SubElement(self._root, 'fonts') # default font_node = SubElement(fonts, 'font') SubElement(font_node, 'sz', {'val': '11'}) SubElement(font_node, 'color', {'theme': '1'}) SubElement(font_node, 'name', {'val': 'Calibri'}) SubElement(font_node, 'family', {'val': '2'}) SubElement(font_node, 'scheme', {'val': 'minor'}) # others table = {} index = 1 for st in self.styles: if st.font != DEFAULTS.font and st.font not in table: font_node = SubElement(fonts, 'font') table[st.font] = index index += 1 self._write_font(font_node, st.font) fonts.attrib["count"] = "%d" % index return table
def _write_fonts(self): """ add fonts part to root return {font.crc => index} """ fonts = SubElement(self._root, 'fonts') # default font_node = SubElement(fonts, 'font') SubElement(font_node, 'sz', {'val':'11'}) SubElement(font_node, 'color', {'theme':'1'}) SubElement(font_node, 'name', {'val':'Calibri'}) SubElement(font_node, 'family', {'val':'2'}) SubElement(font_node, 'scheme', {'val':'minor'}) # others table = {} index = 1 for st in self.styles: if st.font != DEFAULTS.font and st.font not in table: font_node = SubElement(fonts, 'font') table[st.font] = index index += 1 self._write_font(font_node, st.font) fonts.attrib["count"] = "%d" % index return table
def to_tree(self, tagname=None): if tagname is None: tagname = self.tagname attrs = dict(self) el = Element(tagname, attrs) for n in self.__nested__: value = getattr(self, n) if isinstance(value, tuple): if hasattr(el, 'extend'): el.extend(self._serialise_nested(value)) else: # py26 nolxml for _ in self._serialise_nested(value): el.append(_) elif value: SubElement(el, n, val=safe_string(value)) for child in self.__elements__: obj = getattr(self, child) if isinstance(obj, tuple): for v in obj: if hasattr(v, 'to_tree'): el.append(v.to_tree(tagname=child)) else: SubElement(el, child).text = v elif obj is not None: el.append(obj.to_tree(tagname=child)) return el
def _write_legend(self, chart): if self.chart.show_legend: legend = SubElement(chart, '{%s}legend' % CHART_NS) SubElement(legend, '{%s}legendPos' % CHART_NS, {'val': self.chart.legend.position}) SubElement(legend, '{%s}layout' % CHART_NS)
def write_comments_vml(self): root = Element("xml") shape_layout = SubElement(root, "{%s}shapelayout" % officens, {"{%s}ext" % vmlns: "edit"}) SubElement(shape_layout, "{%s}idmap" % officens, { "{%s}ext" % vmlns: "edit", "data": "1" }) shape_type = SubElement( root, "{%s}shapetype" % vmlns, { "id": "_x0000_t202", "coordsize": "21600,21600", "{%s}spt" % officens: "202", "path": "m,l,21600r21600,l21600,xe" }) SubElement(shape_type, "{%s}stroke" % vmlns, {"joinstyle": "miter"}) SubElement(shape_type, "{%s}path" % vmlns, { "gradientshapeok": "t", "{%s}connecttype" % officens: "rect" }) for idx, comment in enumerate(self.comments, 1026): shape = _shape_factory() col, row = coordinate_from_string(comment.ref) row -= 1 column = column_index_from_string(col) - 1 shape.set('id', "_x0000_s%04d" % idx) client_data = shape.find("{%s}ClientData" % excelns) client_data.find("{%s}Row" % excelns).text = str(row) client_data.find("{%s}Column" % excelns).text = str(column) root.append(shape) return tostring(root)
def write_worksheet_rels(worksheet, drawing_id, comments_id): """Write relationships for the worksheet to xml.""" root = Element('{%s}Relationships' % PKG_REL_NS) for rel in worksheet.relationships: attrs = {'Id': rel.id, 'Type': rel.type, 'Target': rel.target} if rel.target_mode: attrs['TargetMode'] = rel.target_mode SubElement(root, '{%s}Relationship' % PKG_REL_NS, attrs) if worksheet._charts or worksheet._images: attrs = { 'Id': 'rId1', 'Type': '%s/drawing' % REL_NS, 'Target': '../drawings/drawing%s.xml' % drawing_id } SubElement(root, '{%s}Relationship' % PKG_REL_NS, attrs) if worksheet._comment_count > 0: # there's only one comments sheet per worksheet, # so there's no reason to call the Id rIdx attrs = { 'Id': 'comments', 'Type': COMMENTS_NS, 'Target': '../comments%s.xml' % comments_id } SubElement(root, '{%s}Relationship' % PKG_REL_NS, attrs) attrs = { 'Id': 'commentsvml', 'Type': VML_NS, 'Target': '../drawings/commentsDrawing%s.vml' % comments_id } SubElement(root, '{%s}Relationship' % PKG_REL_NS, attrs) 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': 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_print_settings(self): settings = SubElement(self.root, '{%s}printSettings' % CHART_NS) SubElement(settings, '{%s}headerFooter' % CHART_NS) margins = dict([(k, safe_string(v)) for (k, v) in iteritems(self.chart.print_margins)]) SubElement(settings, '{%s}pageMargins' % CHART_NS, margins) SubElement(settings, '{%s}pageSetup' % CHART_NS)
def _write_borders(self): borders = SubElement(self._root, 'borders') # default border = SubElement(borders, 'border') SubElement(border, 'left') SubElement(border, 'right') SubElement(border, 'top') SubElement(border, 'bottom') SubElement(border, 'diagonal') # others table = {} index = 1 for st in self._style_list: if st.borders != DEFAULTS.borders and st.borders not in table: table[st.borders] = index border = SubElement(borders, 'border') # caution: respect this order for side in ('left', 'right', 'top', 'bottom', 'diagonal'): obj = getattr(st.borders, side) if obj.border_style is None or obj.border_style == 'none': node = SubElement(border, side) else: node = SubElement(border, side, {'style':obj.border_style}) self._unpack_color(node, obj.color.index) index += 1 borders.attrib["count"] = str(index) return table
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_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_cell_style_xfs(self): cell_style_xfs = SubElement(self._root, 'cellStyleXfs', {'count': '1'}) SubElement(cell_style_xfs, 'xf', { 'numFmtId': "0", 'fontId': "0", 'fillId': "0", 'borderId': "0" })
def _write_style_names(self): styles = self.wb._named_styles cell_styles = SubElement(self._root, 'cellStyles', count=str(len(styles))) for idx, style in enumerate(styles.values()): attrs = dict(style) attrs['xfId'] = str(idx) SubElement(cell_styles, 'cellStyle', attrs)
def _write_number_formats(self): node = SubElement(self._root, 'numFmts', count="%d" % len(self.number_formats)) for idx, nf in enumerate(self.number_formats, 164): SubElement(node, 'numFmt', { 'numFmtId': '%d' % idx, 'formatCode': '%s' % nf })
def write_header_footer(worksheet): header = worksheet.header_footer.getHeader() footer = worksheet.header_footer.getFooter() if header or footer: tag = Element('headerFooter') if header: SubElement(tag, 'oddHeader').text = header if footer: SubElement(tag, 'oddFooter').text = footer return tag
def _write_series(self, subchart): for i, series in enumerate(self.chart): ser = SubElement(subchart, '{%s}ser' % CHART_NS) SubElement(ser, '{%s}idx' % CHART_NS, {'val':safe_string(i)}) SubElement(ser, '{%s}order' % CHART_NS, {'val':safe_string(i)}) if series.title: tx = SubElement(ser, '{%s}tx' % CHART_NS) SubElement(tx, '{%s}v' % CHART_NS).text = series.title if isinstance(self, LineChart): marker = SubElement(ser, "{%s}marker" % CHART_NS) SubElement(marker, "{%s}symbol" % CHART_NS, val=safe_string(series.marker)) if series.color: sppr = SubElement(ser, '{%s}spPr' % CHART_NS) self._write_series_color(sppr, series) if series.error_bar: self._write_error_bar(ser, series) if series.labels: self._write_series_labels(ser, series) if series.xvalues: self._write_series_xvalues(ser, series) val = SubElement(ser, self.series_type) self._write_serial(val, series.reference)
def _write_fills(self): fills = SubElement(self._root, 'fills', {'count':'2'}) fill = SubElement(fills, 'fill') SubElement(fill, 'patternFill', {'patternType':'none'}) fill = SubElement(fills, 'fill') SubElement(fill, 'patternFill', {'patternType':'gray125'}) table = {} index = 2 for st in self._style_list: if st.fill != DEFAULTS.fill and st.fill not in table: table[st.fill] = index fill = SubElement(fills, 'fill') if st.fill.fill_type != DEFAULTS.fill.fill_type: node = SubElement(fill, 'patternFill', {'patternType':st.fill.fill_type}) if st.fill.start_color != DEFAULTS.fill.start_color: self._unpack_color(node, st.fill.start_color.index, 'fgColor') if st.fill.end_color != DEFAULTS.fill.end_color: self._unpack_color(node, st.fill.end_color.index, 'bgColor') index += 1 fills.attrib["count"] = str(index) return table
def _write_anchor(self, node, drawing): x, y, w, h = drawing.get_emu_dimensions() if drawing.anchortype == "oneCell": anchor = SubElement(node, '{%s}oneCellAnchor' % SHEET_DRAWING_NS) xdrfrom = SubElement(anchor, '{%s}from' % SHEET_DRAWING_NS) SubElement(xdrfrom, '{%s}col' % SHEET_DRAWING_NS).text = safe_string(drawing.anchorcol) SubElement(xdrfrom, '{%s}colOff' % SHEET_DRAWING_NS).text = safe_string(x) SubElement(xdrfrom, '{%s}row' % SHEET_DRAWING_NS).text = safe_string(drawing.anchorrow) SubElement(xdrfrom, '{%s}rowOff' % SHEET_DRAWING_NS).text = safe_string(y) else: anchor = SubElement(node, '{%s}absoluteAnchor' % SHEET_DRAWING_NS) SubElement(anchor, '{%s}pos' % SHEET_DRAWING_NS, { 'x': safe_string(x), 'y': safe_string(y) }) SubElement(anchor, '{%s}ext' % SHEET_DRAWING_NS, { 'cx': safe_string(w), 'cy': safe_string(h) }) return anchor
def _write_colors(self): """ Workbook contains a different colour index. """ if self.wb._colors == COLOR_INDEX: return cols = SubElement(self._root, "colors") rgb = SubElement(cols, "indexedColors") for color in self.wb._colors: SubElement(rgb, "rgbColor", rgb=color)
def _write_serial(self, node, reference, literal=False): is_ref = hasattr(reference, 'pos1') data_type = reference.data_type number_format = getattr(reference, 'number_format') mapping = {'n':{'ref':'numRef', 'cache':'numCache'}, 's':{'ref':'strRef', 'cache':'strCache'}} if is_ref: ref = SubElement(node, '{%s}%s' %(CHART_NS, mapping[data_type]['ref'])) SubElement(ref, '{%s}f' % CHART_NS).text = str(reference) data = SubElement(ref, '{%s}%s' %(CHART_NS, mapping[data_type]['cache'])) values = reference.values else: data = SubElement(node, '{%s}numLit' % CHART_NS) values = (1,) if data_type == 'n': SubElement(data, '{%s}formatCode' % CHART_NS).text = number_format or 'General' SubElement(data, '{%s}ptCount' % CHART_NS, {'val':str(len(values))}) for j, val in enumerate(values): point = SubElement(data, '{%s}pt' % CHART_NS, {'idx':str(j)}) val = safe_string(val) SubElement(point, '{%s}v' % CHART_NS).text = val
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_rels(self, chart_id, image_id): root = Element("{%s}Relationships" % PKG_REL_NS) i = 0 for i, chart in enumerate(self._sheet._charts): attrs = {'Id' : 'rId%s' % (i + 1), 'Type' : '%s/chart' % REL_NS, 'Target' : '../charts/chart%s.xml' % (chart_id + i) } SubElement(root, '{%s}Relationship' % PKG_REL_NS, attrs) for j, img in enumerate(self._sheet._images): attrs = {'Id' : 'rId%s' % (i + j + 1), 'Type' : '%s/image' % REL_NS, 'Target' : '../media/image%s.png' % (image_id + j) } SubElement(root, '{%s}Relationship' % PKG_REL_NS, attrs) return tostring(root)
def _write_dxfs(self): if self._style_properties and 'dxf_list' in self._style_properties: dxfs = SubElement( self._root, 'dxfs', {'count': str(len(self._style_properties['dxf_list']))}) for d in self._style_properties['dxf_list']: dxf = SubElement(dxfs, 'dxf') if 'font' in d and d['font'] is not None: font_node = SubElement(dxf, 'font') if d['font'].color is not None: self._write_color(font_node, d['font'].color) ConditionalElement(font_node, 'b', d['font'].bold, 'val') ConditionalElement(font_node, 'i', d['font'].italic, 'val') ConditionalElement(font_node, 'u', d['font'].underline != 'none', {'val': d['font'].underline}) ConditionalElement(font_node, 'strike', d['font'].strikethrough) if 'fill' in d: f = d['fill'] fill = SubElement(dxf, 'fill') if f.fill_type: node = SubElement(fill, 'patternFill', {'patternType': f.fill_type}) else: node = SubElement(fill, 'patternFill') if f.start_color != DEFAULTS.fill.start_color: self._write_color(node, f.start_color, 'fgColor') if f.end_color != DEFAULTS.fill.end_color: self._write_color(node, f.end_color, 'bgColor') if 'border' in d: borders = d['border'] border = SubElement(dxf, 'border') # caution: respect this order for side in ('left', 'right', 'top', 'bottom'): obj = getattr(borders, side) if obj.border_style is None or obj.border_style == 'none': node = SubElement(border, side) else: node = SubElement(border, side, {'style': obj.border_style}) self._write_color(node, obj.color) else: dxfs = SubElement(self._root, 'dxfs', {'count': '0'}) return dxfs
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_text(self, node, shape): """ write text in the shape """ tx_body = SubElement(node, '{%s}txBody' % CHART_DRAWING_NS) SubElement(tx_body, '{%s}bodyPr' % DRAWING_NS, {'vertOverflow':'clip'}) SubElement(tx_body, '{%s}lstStyle' % DRAWING_NS) p = SubElement(tx_body, '{%s}p' % DRAWING_NS) if shape.text: r = SubElement(p, '{%s}r' % DRAWING_NS) rpr = SubElement(r, '{%s}rPr' % DRAWING_NS, {'lang':'en-US'}) fill = SubElement(rpr, '{%s}solidFill' % DRAWING_NS) SubElement(fill, '{%s}srgbClr' % DRAWING_NS, {'val':shape.text_color}) SubElement(r, '{%s}t' % DRAWING_NS).text = shape.text else: SubElement(p, '{%s}endParaRPr' % DRAWING_NS, {'lang':'en-US'})
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_alignment(self, node, alignment): values = dict(alignment) if values.get('horizontal', 'general') == 'general': del values['horizontal'] if values.get('vertical', 'bottom') == 'bottom': del values['vertical'] SubElement(node, 'alignment', values)
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 _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_fills(self): fills = SubElement(self._root, 'fills', {'count':'2'}) fill = SubElement(fills, 'fill') SubElement(fill, 'patternFill', {'patternType':'none'}) fill = SubElement(fills, 'fill') SubElement(fill, 'patternFill', {'patternType':'gray125'}) table = {} index = 2 for st in self.styles: if st.fill != DEFAULTS.fill and st.fill not in table: table[st.fill] = index node = SubElement(fills, 'fill') if isinstance(st.fill, PatternFill): self._write_pattern_fill(node, st.fill) elif isinstance(st.fill, GradientFill): self._write_gradient_fill(node, st.fill) index += 1 fills.attrib["count"] = str(index) return table
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 _write_borders(self): borders = SubElement(self._root, 'borders') # default border = SubElement(borders, 'border') SubElement(border, 'left') SubElement(border, 'right') SubElement(border, 'top') SubElement(border, 'bottom') SubElement(border, 'diagonal') # others table = {} index = 1 for st in self.styles: if st.border != DEFAULTS.border and st.border not in table: table[st.border] = index self._write_border(borders, st.border) index += 1 borders.attrib["count"] = str(index) return table
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_fonts(self): """ add fonts part to root return {font.crc => index} """ fonts = SubElement(self._root, 'fonts') # default font_node = SubElement(fonts, 'font') SubElement(font_node, 'sz', {'val':'11'}) SubElement(font_node, 'color', {'theme':'1'}) SubElement(font_node, 'name', {'val':'Calibri'}) SubElement(font_node, 'family', {'val':'2'}) SubElement(font_node, 'scheme', {'val':'minor'}) # others table = {} index = 1 for st in self._style_list: if st.font != DEFAULTS.font and st.font not in table: table[st.font] = index font_node = SubElement(fonts, 'font') SubElement(font_node, 'sz', {'val':str(st.font.size)}) self._unpack_color(font_node, st.font.color.index) SubElement(font_node, 'name', {'val':st.font.name}) SubElement(font_node, 'family', {'val':'2'}) # Don't write the 'scheme' element because it appears to prevent # the font name from being applied in Excel. #SubElement(font_node, 'scheme', {'val':'minor'}) ConditionalElement(font_node, "b", st.font.bold) ConditionalElement(font_node, "i", st.font.italic) ConditionalElement(font_node, "u", st.font.underline == 'single') index += 1 fonts.attrib["count"] = str(index) return table
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_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_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_fonts(self): fonts_node = SubElement(self._root, 'fonts', count="%d" % len(self.fonts)) for font in self.fonts: fonts_node.append(font.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_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)