def _write_serial(self, node, reference, literal=False): cache = reference.values if isinstance(cache[0], basestring): typ = 'str' else: typ = 'num' if not literal: if typ == 'num': ref = SubElement(node, 'c:numRef') else: ref = SubElement(node, 'c:strRef') SubElement(ref, 'c:f').text = str(reference) if typ == 'num': data = SubElement(ref, 'c:numCache') else: data = SubElement(ref, 'c:strCache') else: data = SubElement(node, 'c:numLit') if typ == 'num': SubElement(data, 'c:formatCode').text = 'General' if literal: values = (1,) else: values = cache SubElement(data, 'c:ptCount', {'val':str(len(values))}) for j, val in enumerate(values): point = SubElement(data, 'c:pt', {'idx':str(j)}) SubElement(point, 'c:v').text = str(val)
def write_workbook(workbook): """Write the core workbook xml.""" root = Element('workbook', {'xmlns': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main', 'xml:space': 'preserve', 'xmlns:r': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'}) SubElement(root, 'fileVersion', {'appName': 'xl', 'lastEdited': '4', 'lowestEdited': '4', 'rupBuild': '4505'}) SubElement(root, 'workbookPr', {'defaultThemeVersion': '124226', 'codeName': 'ThisWorkbook'}) book_views = SubElement(root, 'bookViews') SubElement(book_views, 'workbookView', {'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, 'sheets') for i, sheet in enumerate(workbook.worksheets): sheet_node = SubElement(sheets, 'sheet', {'name': sheet.title, 'sheetId': '%d' % (i + 1), 'r:id': '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, 'definedNames') # named ranges for named_range in workbook.get_named_ranges(): name = SubElement(defined_names, 'definedName', {'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 # autoFilter for i, sheet in enumerate(workbook.worksheets): #continue auto_filter = sheet.auto_filter if not auto_filter: continue name = SubElement(defined_names, 'definedName', dict(name='_xlnm._FilterDatabase', localSheetId=str(i), hidden='1')) name.text = "'%s'!%s" % (sheet.title.replace("'", "''"), absolute_coordinate(auto_filter)) SubElement(root, 'calcPr', {'calcId': '124519', 'calcMode': 'auto', 'fullCalcOnLoad': '1'}) return get_document_content(root)
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_root_rels(workbook): """Write the relationships xml.""" root = Element('Relationships', { 'xmlns': 'http://schemas.openxmlformats.org/package/2006/relationships' }) SubElement( root, 'Relationship', { 'Id': 'rId1', 'Target': ARC_WORKBOOK, 'Type': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument' }) SubElement( root, 'Relationship', { 'Id': 'rId2', 'Target': ARC_CORE, 'Type': 'http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties' }) SubElement( root, 'Relationship', { 'Id': 'rId3', 'Target': ARC_APP, 'Type': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties' }) return get_document_content(root)
def _write_cell_xfs(self, number_format_table, fonts_table, fills_table, borders_table): """ write styles combinations based on ids found in tables """ # writing the cellXfs cell_xfs = SubElement(self._root, 'cellXfs', {'count':'%d' % (len(self._style_list) + 1)}) # default def _get_default_vals(): return dict(numFmtId='0', fontId='0', fillId='0', xfId='0', borderId='0') SubElement(cell_xfs, 'xf', _get_default_vals()) for st in self._style_list: vals = _get_default_vals() if hash(st.font) != hash(style.DEFAULTS.font): vals['fontId'] = fonts_table[hash(st.font)] vals['applyFont'] = '1' if hash(st.borders) != hash(style.DEFAULTS.borders): vals['borderId'] = borders_table[hash(st.borders)] vals['applyBorder'] = '1' if hash(st.fill) != hash(style.DEFAULTS.fill): vals['fillId'] = fills_table[hash(st.fill)] vals['applyFill'] = '1' if st.number_format != style.DEFAULTS.number_format: vals['numFmtId'] = '%d' % number_format_table[st.number_format] vals['applyNumberFormat'] = '1' if hash(st.alignment) != hash(style.DEFAULTS.alignment): vals['applyAlignment'] = '1' node = SubElement(cell_xfs, 'xf', vals) if hash(st.alignment) != hash(style.DEFAULTS.alignment): alignments = {} for align_attr in ['horizontal', 'vertical']: if hash(getattr(st.alignment, align_attr)) != hash(getattr(style.DEFAULTS.alignment, align_attr)): alignments[align_attr] = getattr(st.alignment, align_attr) if hash(st.alignment.wrap_text) != hash(style.DEFAULTS.alignment.wrap_text): alignments['wrapText'] = '1' if st.alignment.indent > 0: alignments['indent'] = '%s' % st.alignment.indent if st.alignment.text_rotation > 0: alignments['textRotation'] = '%s' % st.alignment.text_rotation elif st.alignment.text_rotation < 0: alignments['textRotation'] = '%s' % (90 - st.alignment.text_rotation) SubElement(node, 'alignment', alignments)
def _write_print_settings(self, root): settings = SubElement(root, 'c:printSettings') SubElement(settings, 'c:headerFooter') try: # Python 2 print_margins_items = iteritems(self.chart.print_margins) except AttributeError: # Python 3 print_margins_items = self.chart.print_margins.items() margins = dict([(k, str(v)) for (k, v) in print_margins_items]) SubElement(settings, 'c:pageMargins', margins) SubElement(settings, 'c:pageSetup')
def write_worksheet_rels(worksheet, idx): """Write relationships for the worksheet to xml.""" root = Element('Relationships', {'xmlns': 'http://schemas.openxmlformats.org/package/2006/relationships'}) 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, 'Relationship', attrs) if worksheet._charts or worksheet._images: attrs = {'Id' : 'rId1', 'Type' : 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing', 'Target' : '../drawings/drawing%s.xml' % idx } SubElement(root, 'Relationship', attrs) return get_document_content(root)
def write_rels(self, chart_id, image_id): root = Element('Relationships', {'xmlns' : 'http://schemas.openxmlformats.org/package/2006/relationships'}) i = 0 for i, chart in enumerate(self._sheet._charts): attrs = {'Id' : 'rId%s' % (i + 1), 'Type' : 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/chart', 'Target' : '../charts/chart%s.xml' % (chart_id + i) } SubElement(root, 'Relationship', attrs) for j, img in enumerate(self._sheet._images): attrs = {'Id' : 'rId%s' % (i + j + 1), 'Type' : 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/image', 'Target' : '../media/image%s.png' % (image_id + j) } SubElement(root, 'Relationship', attrs) return get_document_content(root)
def _write_style(self, node): """ write style theme """ style = SubElement(node, 'cdr:style') ln_ref = SubElement(style, 'a:lnRef', {'xmlns:a':self.schema, 'idx':'2'}) scheme_clr = SubElement(ln_ref, 'a:schemeClr', {'val':'accent1'}) SubElement(scheme_clr, 'a:shade', {'val':'50000'}) fill_ref = SubElement(style, 'a:fillRef', {'xmlns:a':self.schema, 'idx':'1'}) SubElement(fill_ref, 'a:schemeClr', {'val':'accent1'}) effect_ref = SubElement(style, 'a:effectRef', {'xmlns:a':self.schema, 'idx':'0'}) SubElement(effect_ref, 'a:schemeClr', {'val':'accent1'}) font_ref = SubElement(style, 'a:fontRef', {'xmlns:a':self.schema, 'idx':'minor'}) SubElement(font_ref, 'a:schemeClr', {'val':'lt1'})
def _write_text(self, node, shape): """ write text in the shape """ tx_body = SubElement(node, 'cdr:txBody') SubElement(tx_body, 'a:bodyPr', {'xmlns:a':self.schema, 'vertOverflow':'clip'}) SubElement(tx_body, 'a:lstStyle', {'xmlns:a':self.schema}) p = SubElement(tx_body, 'a:p', {'xmlns:a':self.schema}) if shape.text: r = SubElement(p, 'a:r') rpr = SubElement(r, 'a:rPr', {'lang':'en-US'}) fill = SubElement(rpr, 'a:solidFill') SubElement(fill, 'a:srgbClr', {'val':shape.text_color}) SubElement(r, 'a:t').text = shape.text else: SubElement(p, 'a:endParaRPr', {'lang':'en-US'})
def _write_error_bar(self, node, serie): flag = {ErrorBar.PLUS_MINUS:'both', ErrorBar.PLUS:'plus', ErrorBar.MINUS:'minus'} eb = SubElement(node, 'c:errBars') SubElement(eb, 'c:errBarType', {'val':flag[serie.error_bar.type]}) SubElement(eb, 'c:errValType', {'val':'cust'}) plus = SubElement(eb, 'c:plus') self._write_serial(plus, serie.error_bar.values, literal=(serie.error_bar.type == ErrorBar.MINUS)) minus = SubElement(eb, 'c:minus') self._write_serial(minus, serie.error_bar.values, literal=(serie.error_bar.type == ErrorBar.PLUS))
def write_rels(self, drawing_id): root = Element('Relationships', {'xmlns' : 'http://schemas.openxmlformats.org/package/2006/relationships'}) attrs = {'Id' : 'rId1', 'Type' : 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/chartUserShapes', 'Target' : '../drawings/drawing%s.xml' % drawing_id } SubElement(root, 'Relationship', attrs) return get_document_content(root)
def write_properties_core(properties): """Write the core properties to xml.""" root = Element( 'cp:coreProperties', { 'xmlns:cp': NAMESPACES['cp'], 'xmlns:xsi': NAMESPACES['xsi'], 'xmlns:dc': NAMESPACES['dc'], 'xmlns:dcterms': NAMESPACES['dcterms'], 'xmlns:dcmitype': NAMESPACES['dcmitype'], }) SubElement(root, 'dc:creator').text = properties.creator SubElement(root, 'cp:lastModifiedBy').text = properties.last_modified_by SubElement(root, 'dcterms:created', \ {'xsi:type': 'dcterms:W3CDTF'}).text = \ datetime_to_W3CDTF(properties.created) SubElement(root, 'dcterms:modified', {'xsi:type': 'dcterms:W3CDTF'}).text = \ datetime_to_W3CDTF(properties.modified) return get_document_content(root)
def write_workbook_rels(workbook): """Write the workbook relationships xml.""" root = Element('Relationships', { 'xmlns': 'http://schemas.openxmlformats.org/package/2006/relationships' }) for i in range(len(workbook.worksheets)): SubElement( root, 'Relationship', { 'Id': 'rId%d' % (i + 1), 'Type': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet', 'Target': 'worksheets/sheet%s.xml' % (i + 1) }) rid = len(workbook.worksheets) + 1 SubElement( root, 'Relationship', { 'Id': 'rId%d' % rid, 'Target': 'sharedStrings.xml', 'Type': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings' }) SubElement( root, 'Relationship', { 'Id': 'rId%d' % (rid + 1), 'Target': 'styles.xml', 'Type': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles' }) SubElement( root, 'Relationship', { 'Id': 'rId%d' % (rid + 2), 'Target': 'theme/theme1.xml', 'Type': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme' }) return get_document_content(root)
def write(self): """ write a chart """ root = Element('c:chartSpace', {'xmlns:c':"http://schemas.openxmlformats.org/drawingml/2006/chart", 'xmlns:a':"http://schemas.openxmlformats.org/drawingml/2006/main", 'xmlns:r':"http://schemas.openxmlformats.org/officeDocument/2006/relationships"}) SubElement(root, 'c:lang', {'val':self.chart.lang}) self._write_chart(root) self._write_print_settings(root) self._write_shapes(root) return get_document_content(root)
def _write_cell_style(self): cell_styles = SubElement(self._root, 'cellStyles', {'count':'1'}) cell_style = SubElement(cell_styles, 'cellStyle', {'name':"Normal", 'xfId':"0", 'builtinId':"0"})
def write_content_types(workbook): """Write the content-types xml.""" root = Element('Types', { 'xmlns': 'http://schemas.openxmlformats.org/package/2006/content-types' }) SubElement( root, 'Override', { 'PartName': '/' + ARC_THEME, 'ContentType': 'application/vnd.openxmlformats-officedocument.theme+xml' }) SubElement( root, 'Override', { 'PartName': '/' + ARC_STYLE, 'ContentType': 'application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml' }) SubElement( root, 'Default', { 'Extension': 'rels', 'ContentType': 'application/vnd.openxmlformats-package.relationships+xml' }) SubElement(root, 'Default', { 'Extension': 'xml', 'ContentType': 'application/xml' }) SubElement(root, 'Default', { 'Extension': 'png', 'ContentType': 'image/png' }) SubElement( root, 'Override', { 'PartName': '/' + ARC_WORKBOOK, 'ContentType': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml' }) SubElement( root, 'Override', { 'PartName': '/' + ARC_APP, 'ContentType': 'application/vnd.openxmlformats-officedocument.extended-properties+xml' }) SubElement( root, 'Override', { 'PartName': '/' + ARC_CORE, 'ContentType': 'application/vnd.openxmlformats-package.core-properties+xml' }) SubElement( root, 'Override', { 'PartName': '/' + ARC_SHARED_STRINGS, 'ContentType': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml' }) drawing_id = 1 chart_id = 1 for sheet_id, sheet in enumerate(workbook.worksheets): SubElement( root, 'Override', { 'PartName': '/xl/worksheets/sheet%d.xml' % (sheet_id + 1), 'ContentType': 'application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml' }) if sheet._charts or sheet._images: SubElement( root, 'Override', { 'PartName': '/xl/drawings/drawing%d.xml' % drawing_id, 'ContentType': 'application/vnd.openxmlformats-officedocument.drawing+xml' }) drawing_id += 1 for chart in sheet._charts: SubElement( root, 'Override', { 'PartName': '/xl/charts/chart%d.xml' % chart_id, 'ContentType': 'application/vnd.openxmlformats-officedocument.drawingml.chart+xml' }) chart_id += 1 if chart._shapes: SubElement( root, 'Override', { 'PartName': '/xl/drawings/drawing%d.xml' % drawing_id, 'ContentType': 'application/vnd.openxmlformats-officedocument.drawingml.chartshapes+xml' }) drawing_id += 1 return get_document_content(root)
def _write_cell_style_xfs(self): cell_style_xfs = SubElement(self._root, 'cellStyleXfs', {'count':'1'}) xf = SubElement(cell_style_xfs, 'xf', {'numFmtId':"0", 'fontId':"0", 'fillId':"0", 'borderId':"0"})
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 hash(st.borders) != hash(style.DEFAULTS.borders) and hash(st.borders) not in table: table[hash(st.borders)] = str(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) attrs = {} else: node = SubElement(border, side, {'style':obj.border_style}) if str(obj.color.index).split(':')[0] == 'theme': # strip prefix theme if marked as such if str(obj.color.index).split(':')[2]: SubElement(node, 'color', {'theme':str(obj.color.index).split(':')[1], 'tint':str(obj.color.index).split(':')[2]}) else: SubElement(node, 'color', {'theme':str(obj.color.index).split(':')[1]}) else: SubElement(node, 'color', {'rgb':str(obj.color.index)}) index += 1 borders.attrib["count"] = str(index) return table
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 hash(st.fill) != hash(style.DEFAULTS.fill) and hash(st.fill) not in table: table[hash(st.fill)] = str(index) fill = SubElement(fills, 'fill') if hash(st.fill.fill_type) != hash(style.DEFAULTS.fill.fill_type): node = SubElement(fill, 'patternFill', {'patternType':st.fill.fill_type}) if hash(st.fill.start_color) != hash(style.DEFAULTS.fill.start_color): if str(st.fill.start_color.index).split(':')[0] == 'theme': # strip prefix theme if marked as such if str(st.fill.start_color.index).split(':')[2]: SubElement(node, 'fgColor', {'theme':str(st.fill.start_color.index).split(':')[1], 'tint':str(st.fill.start_color.index).split(':')[2]}) else: SubElement(node, 'fgColor', {'theme':str(st.fill.start_color.index).split(':')[1]}) else: SubElement(node, 'fgColor', {'rgb':str(st.fill.start_color.index)}) if hash(st.fill.end_color) != hash(style.DEFAULTS.fill.end_color): if str(st.fill.end_color.index).split(':')[0] == 'theme': # strip prefix theme if marked as such if str(st.fill.end_color.index).split(':')[2]: SubElement(node, 'bgColor', {'theme':str(st.fill.end_color.index).split(':')[1], 'tint':str(st.fill.end_color.index).split(':')[2]}) else: SubElement(node, 'bgColor', {'theme':str(st.fill.end_color.index).split(':')[1]}) else: SubElement(node, 'bgColor', {'rgb':str(st.fill.end_color.index)}) index += 1 fills.attrib["count"] = str(index) return table
def _write_axis(self, plot_area, axis, label): ax = SubElement(plot_area, label) SubElement(ax, 'c:axId', {'val':str(axis.id)}) scaling = SubElement(ax, 'c:scaling') SubElement(scaling, 'c:orientation', {'val':axis.orientation}) if label == 'c:valAx': SubElement(scaling, 'c:max', {'val':str(float(axis.max))}) SubElement(scaling, 'c:min', {'val':str(float(axis.min))}) SubElement(ax, 'c:axPos', {'val':axis.position}) if label == 'c:valAx': SubElement(ax, 'c:majorGridlines') SubElement(ax, 'c:numFmt', {'formatCode':"General", 'sourceLinked':'1'}) if axis.title != '': title = SubElement(ax, 'c:title') tx = SubElement(title, 'c:tx') rich = SubElement(tx, 'c:rich') SubElement(rich, 'a:bodyPr') SubElement(rich, 'a:lstStyle') p = SubElement(rich, 'a:p') pPr = SubElement(p, 'a:pPr') SubElement(pPr, 'a:defRPr') r = SubElement(p, 'a:r') SubElement(r, 'a:rPr', {'lang':self.chart.lang}) t = SubElement(r, 'a:t').text = axis.title SubElement(title, 'c:layout') SubElement(ax, 'c:tickLblPos', {'val':axis.tick_label_position}) SubElement(ax, 'c:crossAx', {'val':str(axis.cross)}) SubElement(ax, 'c:crosses', {'val':axis.crosses}) if axis.auto: SubElement(ax, 'c:auto', {'val':'1'}) if axis.label_align: SubElement(ax, 'c:lblAlgn', {'val':axis.label_align}) if axis.label_offset: SubElement(ax, 'c:lblOffset', {'val':str(axis.label_offset)}) if label == 'c:valAx': if self.chart.type == Chart.SCATTER_CHART: SubElement(ax, 'c:crossBetween', {'val':'midCat'}) else: SubElement(ax, 'c:crossBetween', {'val':'between'}) SubElement(ax, 'c:majorUnit', {'val':str(float(axis.unit))})
def _write_shapes(self, root): if self.chart._shapes: SubElement(root, 'c:userShapes', {'r:id':'rId1'})
def write(self, shape_id): root = Element('c:userShapes', {'xmlns:c' : 'http://schemas.openxmlformats.org/drawingml/2006/chart'}) for shape in self._shapes: anchor = SubElement(root, 'cdr:relSizeAnchor', {'xmlns:cdr' : "http://schemas.openxmlformats.org/drawingml/2006/chartDrawing"}) xstart, ystart, xend, yend = shape.get_coordinates() _from = SubElement(anchor, 'cdr:from') SubElement(_from, 'cdr:x').text = str(xstart) SubElement(_from, 'cdr:y').text = str(ystart) _to = SubElement(anchor, 'cdr:to') SubElement(_to, 'cdr:x').text = str(xend) SubElement(_to, 'cdr:y').text = str(yend) sp = SubElement(anchor, 'cdr:sp', {'macro':'', 'textlink':''}) nvspr = SubElement(sp, 'cdr:nvSpPr') SubElement(nvspr, 'cdr:cNvPr', {'id':str(shape_id), 'name':'shape %s' % shape_id}) SubElement(nvspr, 'cdr:cNvSpPr') sppr = SubElement(sp, 'cdr:spPr') frm = SubElement(sppr, 'a:xfrm', {'xmlns:a':self.schema}) # no transformation SubElement(frm, 'a:off', {'x':'0', 'y':'0'}) SubElement(frm, 'a:ext', {'cx':'0', 'cy':'0'}) prstgeom = SubElement(sppr, 'a:prstGeom', {'xmlns:a':self.schema, 'prst':str(shape.style)}) SubElement(prstgeom, 'a:avLst') fill = SubElement(sppr, 'a:solidFill', {'xmlns:a':self.schema}) SubElement(fill, 'a:srgbClr', {'val':shape.color}) border = SubElement(sppr, 'a:ln', {'xmlns:a':self.schema, 'w':str(shape._border_width)}) sf = SubElement(border, 'a:solidFill') SubElement(sf, 'a:srgbClr', {'val':shape.border_color}) self._write_style(sp) self._write_text(sp, shape) shape_id += 1 return get_document_content(root)
def _write_legend(self, chart): if self.chart.show_legend: legend = SubElement(chart, 'c:legend') SubElement(legend, 'c:legendPos', {'val':self.chart.legend.position}) SubElement(legend, 'c:layout')
def write(self): """ write drawings for one sheet in one file """ root = Element('xdr:wsDr', {'xmlns:xdr' : "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing", 'xmlns:a' : "http://schemas.openxmlformats.org/drawingml/2006/main"}) for i, chart in enumerate(self._sheet._charts): drawing = chart.drawing # anchor = SubElement(root, 'xdr:twoCellAnchor') # (start_row, start_col), (end_row, end_col) = drawing.coordinates # # anchor coordinates # _from = SubElement(anchor, 'xdr:from') # x = SubElement(_from, 'xdr:col').text = str(start_col) # x = SubElement(_from, 'xdr:colOff').text = '0' # x = SubElement(_from, 'xdr:row').text = str(start_row) # x = SubElement(_from, 'xdr:rowOff').text = '0' # _to = SubElement(anchor, 'xdr:to') # x = SubElement(_to, 'xdr:col').text = str(end_col) # x = SubElement(_to, 'xdr:colOff').text = '0' # x = SubElement(_to, 'xdr:row').text = str(end_row) # x = SubElement(_to, 'xdr:rowOff').text = '0' # we only support absolute anchor atm (TODO: oneCellAnchor, twoCellAnchor x, y, w, h = drawing.get_emu_dimensions() anchor = SubElement(root, 'xdr:absoluteAnchor') SubElement(anchor, 'xdr:pos', {'x':str(x), 'y':str(y)}) SubElement(anchor, 'xdr:ext', {'cx':str(w), 'cy':str(h)}) # graph frame frame = SubElement(anchor, 'xdr:graphicFrame', {'macro':''}) name = SubElement(frame, 'xdr:nvGraphicFramePr') SubElement(name, 'xdr:cNvPr', {'id':'%s' % i, 'name':'Graphique %s' % i}) SubElement(name, 'xdr:cNvGraphicFramePr') frm = SubElement(frame, 'xdr:xfrm') # no transformation SubElement(frm, 'a:off', {'x':'0', 'y':'0'}) SubElement(frm, 'a:ext', {'cx':'0', 'cy':'0'}) graph = SubElement(frame, 'a:graphic') data = SubElement(graph, 'a:graphicData', {'uri':'http://schemas.openxmlformats.org/drawingml/2006/chart'}) SubElement(data, 'c:chart', { 'xmlns:c':'http://schemas.openxmlformats.org/drawingml/2006/chart', 'xmlns:r':'http://schemas.openxmlformats.org/officeDocument/2006/relationships', 'r:id':'rId%s' % (i + 1)}) SubElement(anchor, 'xdr:clientData') for i, img in enumerate(self._sheet._images): drawing = img.drawing x, y, w, h = drawing.get_emu_dimensions() anchor = SubElement(root, 'xdr:absoluteAnchor') SubElement(anchor, 'xdr:pos', {'x':str(x), 'y':str(y)}) SubElement(anchor, 'xdr:ext', {'cx':str(w), 'cy':str(h)}) pic = SubElement(anchor, 'xdr:pic') name = SubElement(pic, 'xdr:nvPicPr') SubElement(name, 'xdr:cNvPr', {'id':'%s' % i, 'name':'Picture %s' % i}) SubElement(SubElement(name, 'xdr:cNvPicPr'), 'a:picLocks', {'noChangeAspect':"1" if img.nochangeaspect else '0','noChangeArrowheads':"1" if img.nochangearrowheads else '0'}) blipfill = SubElement(pic, 'xdr:blipFill') SubElement(blipfill, 'a:blip', { 'xmlns:r': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships', 'r:embed': 'rId%s' % (i + 1), 'cstate':'print' }) SubElement(blipfill, 'a:srcRect') SubElement(SubElement(blipfill, 'a:stretch'), 'a:fillRect') sppr = SubElement(pic, 'xdr:spPr', {'bwMode':'auto'}) frm = SubElement(sppr, 'a:xfrm') # no transformation SubElement(frm, 'a:off', {'x':'0', 'y':'0'}) SubElement(frm, 'a:ext', {'cx':'0', 'cy':'0'}) SubElement(SubElement(sppr, 'a:prstGeom', {'prst':'rect'}), 'a:avLst') SubElement(sppr, 'a:noFill') ln = SubElement(sppr, 'a:ln', {'w':'1'}) SubElement(ln, 'a:noFill') SubElement(ln, 'a:miter', {'lim':'800000'}) SubElement(ln, 'a:headEnd') SubElement(ln, 'a:tailEnd', {'type':'none', 'w':'med', 'len':'med'}) SubElement(sppr, 'a:effectLst') SubElement(anchor, 'xdr:clientData') return get_document_content(root)
def _write_series(self, subchart): for i, serie in enumerate(self.chart._series): ser = SubElement(subchart, 'c:ser') SubElement(ser, 'c:idx', {'val':str(i)}) SubElement(ser, 'c:order', {'val':str(i)}) if serie.legend: tx = SubElement(ser, 'c:tx') self._write_serial(tx, serie.legend) if serie.color: sppr = SubElement(ser, 'c:spPr') if self.chart.type == Chart.BAR_CHART: # fill color fillc = SubElement(sppr, 'a:solidFill') SubElement(fillc, 'a:srgbClr', {'val':serie.color}) # edge color ln = SubElement(sppr, 'a:ln') fill = SubElement(ln, 'a:solidFill') SubElement(fill, 'a:srgbClr', {'val':serie.color}) if serie.error_bar: self._write_error_bar(ser, serie) marker = SubElement(ser, 'c:marker') SubElement(marker, 'c:symbol', {'val':serie.marker}) if serie.labels: cat = SubElement(ser, 'c:cat') self._write_serial(cat, serie.labels) if self.chart.type == Chart.SCATTER_CHART: if serie.xvalues: xval = SubElement(ser, 'c:xVal') self._write_serial(xval, serie.xreference) yval = SubElement(ser, 'c:yVal') self._write_serial(yval, serie.reference) else: val = SubElement(ser, 'c:val') self._write_serial(val, serie.reference)
def _write_dxfs(self): dxfs = SubElement(self._root, 'dxfs', {'count':'0'})
def _write_table_styles(self): table_styles = SubElement(self._root, 'tableStyles', {'count':'0', 'defaultTableStyle':'TableStyleMedium9', 'defaultPivotStyle':'PivotStyleLight16'})
def _write_chart(self, root): chart = self.chart ch = SubElement(root, 'c:chart') self._write_title(ch) plot_area = SubElement(ch, 'c:plotArea') layout = SubElement(plot_area, 'c:layout') mlayout = SubElement(layout, 'c:manualLayout') SubElement(mlayout, 'c:layoutTarget', {'val':'inner'}) SubElement(mlayout, 'c:xMode', {'val':'edge'}) SubElement(mlayout, 'c:yMode', {'val':'edge'}) SubElement(mlayout, 'c:x', {'val':str(chart._get_margin_left())}) SubElement(mlayout, 'c:y', {'val':str(chart._get_margin_top())}) SubElement(mlayout, 'c:w', {'val':str(chart.width)}) SubElement(mlayout, 'c:h', {'val':str(chart.height)}) if chart.type == Chart.SCATTER_CHART: subchart = SubElement(plot_area, 'c:scatterChart') SubElement(subchart, 'c:scatterStyle', {'val':str('lineMarker')}) else: if chart.type == Chart.BAR_CHART: subchart = SubElement(plot_area, 'c:barChart') SubElement(subchart, 'c:barDir', {'val':'col'}) else: subchart = SubElement(plot_area, 'c:lineChart') SubElement(subchart, 'c:grouping', {'val':chart.grouping}) self._write_series(subchart) SubElement(subchart, 'c:marker', {'val':'1'}) SubElement(subchart, 'c:axId', {'val':str(chart.x_axis.id)}) SubElement(subchart, 'c:axId', {'val':str(chart.y_axis.id)}) if chart.type == Chart.SCATTER_CHART: self._write_axis(plot_area, chart.x_axis, 'c:valAx') else: self._write_axis(plot_area, chart.x_axis, 'c:catAx') self._write_axis(plot_area, chart.y_axis, 'c:valAx') self._write_legend(ch) SubElement(ch, 'c:plotVisOnly', {'val':'1'})
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 hash(st.font) != hash(style.DEFAULTS.font) and hash(st.font) not in table: table[hash(st.font)] = str(index) font_node = SubElement(fonts, 'font') SubElement(font_node, 'sz', {'val':str(st.font.size)}) if str(st.font.color.index).split(':')[0] == 'theme': # strip prefix theme if marked as such if str(st.font.color.index).split(':')[2]: SubElement(font_node, 'color', {'theme':str(st.font.color.index).split(':')[1], 'tint':str(st.font.color.index).split(':')[2]}) else: SubElement(font_node, 'color', {'theme':str(st.font.color.index).split(':')[1]}) else: SubElement(font_node, 'color', {'rgb':str(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'}) if st.font.bold: SubElement(font_node, 'b') if st.font.italic: SubElement(font_node, 'i') if st.font.underline == 'single': SubElement(font_node, 'u') index += 1 fonts.attrib["count"] = str(index) return table
def _write_title(self, chart): if self.chart.title != '': title = SubElement(chart, 'c:title') tx = SubElement(title, 'c:tx') rich = SubElement(tx, 'c:rich') SubElement(rich, 'a:bodyPr') SubElement(rich, 'a:lstStyle') p = SubElement(rich, 'a:p') pPr = SubElement(p, 'a:pPr') SubElement(pPr, 'a:defRPr') r = SubElement(p, 'a:r') SubElement(r, 'a:rPr', {'lang':self.chart.lang}) t = SubElement(r, 'a:t').text = self.chart.title SubElement(title, 'c:layout')