def test_conditional_formatting_setRules(self): class WS(): conditional_formatting = ConditionalFormatting() worksheet = WS() rules = { 'A1:A4': [{ 'type': 'colorScale', 'priority': '13', 'colorScale': { 'cfvo': [{ 'type': 'min' }, { 'type': 'max' }], 'color': [Color('FFFF7128'), Color('FFFFEF9C')] } }] } worksheet.conditional_formatting.setRules(rules) temp_buffer = StringIO() doc = XMLGenerator(out=temp_buffer, encoding='utf-8') write_worksheet_conditional_formatting(doc, worksheet) doc.endDocument() xml = temp_buffer.getvalue() temp_buffer.close() expected = '<conditionalFormatting sqref="A1:A4"><cfRule type="colorScale" priority="1"><colorScale><cfvo type="min"></cfvo><cfvo type="max"></cfvo><color rgb="FFFF7128"></color><color rgb="FFFFEF9C"></color></colorScale></cfRule></conditionalFormatting>' diff = compare_xml(xml, expected) assert diff is None, diff
def test_conditional_formatting_addCustomRule(self): class WS(): conditional_formatting = ConditionalFormatting() worksheet = WS() dxfId = worksheet.conditional_formatting.addDxfStyle( self.workbook, None, None, None) worksheet.conditional_formatting.addCustomRule( 'C1:C10', { 'type': 'expression', 'dxfId': dxfId, 'formula': ['ISBLANK(C1)'], 'stopIfTrue': '1' }) temp_buffer = StringIO() doc = XMLGenerator(out=temp_buffer, encoding='utf-8') write_worksheet_conditional_formatting(doc, worksheet) doc.endDocument() xml = temp_buffer.getvalue() temp_buffer.close() assert dxfId == 0 expected = '<conditionalFormatting sqref="C1:C10"><cfRule dxfId="0" type="expression" stopIfTrue="1" priority="1"><formula>ISBLANK(C1)</formula></cfRule></conditionalFormatting>' diff = compare_xml(xml, expected) assert diff is None, diff
def _get_xml_iter(xml_source): if not hasattr(xml_source, 'name'): if isinstance(xml_source, str): return StringIO(xml_source) else: return StringIO(str(xml_source, 'utf-8')) else: xml_source.seek(0) return xml_source
def _write_worksheets(self, archive, shared_string_table, style_writer): drawing_id = 1 chart_id = 1 image_id = 1 shape_id = 1 for i, sheet in enumerate(self.workbook.worksheets): archive.writestr( PACKAGE_WORKSHEETS + '/sheet%d.xml' % (i + 1), write_worksheet(sheet, shared_string_table, style_writer.get_style_by_hash())) if sheet._charts or sheet._images or sheet.relationships: archive.writestr( PACKAGE_WORKSHEETS + '/_rels/sheet%d.xml.rels' % (i + 1), write_worksheet_rels(sheet, drawing_id)) if sheet._charts or sheet._images: dw = DrawingWriter(sheet) archive.writestr( PACKAGE_DRAWINGS + '/drawing%d.xml' % drawing_id, dw.write()) archive.writestr( PACKAGE_DRAWINGS + '/_rels/drawing%d.xml.rels' % drawing_id, dw.write_rels(chart_id, image_id)) drawing_id += 1 for chart in sheet._charts: cw = ChartWriter(chart) archive.writestr( PACKAGE_CHARTS + '/chart%d.xml' % chart_id, cw.write()) if chart._shapes: archive.writestr( PACKAGE_CHARTS + '/_rels/chart%d.xml.rels' % chart_id, cw.write_rels(drawing_id)) sw = ShapeWriter(chart._shapes) archive.writestr( PACKAGE_DRAWINGS + '/drawing%d.xml' % drawing_id, sw.write(shape_id)) shape_id += len(chart._shapes) drawing_id += 1 chart_id += 1 for img in sheet._images: buf = StringIO() img.image.save(buf, format='PNG') archive.writestr( PACKAGE_IMAGES + '/image%d.png' % image_id, buf.getvalue()) image_id += 1
def test_repair_central_directory(): from openpyxl.reader.excel import repair_central_directory, CENTRAL_DIRECTORY_SIGNATURE data_a = "foobarbaz" + CENTRAL_DIRECTORY_SIGNATURE data_b = "bazbarfoo1234567890123456890" # The repair_central_directory looks for a magic set of bytes # (CENTRAL_DIRECTORY_SIGNATURE) and strips off everything 18 bytes past the sequence f = repair_central_directory(StringIO(data_a + data_b), True) eq_(f.read(), data_a + data_b[:18]) f = repair_central_directory(StringIO(data_b), True) eq_(f.read(), data_b)
def test_conditional_formatting_addCustomRule(self): class WS(): conditional_formatting = ConditionalFormatting() worksheet = WS() dxfId = worksheet.conditional_formatting.addDxfStyle(self.workbook, None, None, None) worksheet.conditional_formatting.addCustomRule('C1:C10', {'type': 'expression', 'dxfId': dxfId, 'formula': ['ISBLANK(C1)'], 'stopIfTrue': '1'}) temp_buffer = StringIO() doc = XMLGenerator(out=temp_buffer, encoding='utf-8') write_worksheet_conditional_formatting(doc, worksheet) doc.endDocument() xml = temp_buffer.getvalue() temp_buffer.close() assert dxfId == 0 expected = '<conditionalFormatting sqref="C1:C10"><cfRule dxfId="0" type="expression" stopIfTrue="1" priority="1"><formula>ISBLANK(C1)</formula></cfRule></conditionalFormatting>' diff = compare_xml(xml, expected) assert diff is None, diff
def test_conditional_formatting_setRules(self): class WS(): conditional_formatting = ConditionalFormatting() worksheet = WS() rules = {'A1:A4': [{'type': 'colorScale', 'priority': '13', 'colorScale': {'cfvo': [{'type': 'min'}, {'type': 'max'}], 'color': [Color('FFFF7128'), Color('FFFFEF9C')]}}]} worksheet.conditional_formatting.setRules(rules) temp_buffer = StringIO() doc = XMLGenerator(out=temp_buffer, encoding='utf-8') write_worksheet_conditional_formatting(doc, worksheet) doc.endDocument() xml = temp_buffer.getvalue() temp_buffer.close() expected = '<conditionalFormatting sqref="A1:A4"><cfRule type="colorScale" priority="1"><colorScale><cfvo type="min"></cfvo><cfvo type="max"></cfvo><color rgb="FFFF7128"></color><color rgb="FFFFEF9C"></color></colorScale></cfRule></conditionalFormatting>' diff = compare_xml(xml, expected) assert diff is None, diff
def _write_worksheets(self, archive, shared_string_table, style_writer): drawing_id = 1 chart_id = 1 image_id = 1 shape_id = 1 for i, sheet in enumerate(self.workbook.worksheets): archive.writestr( PACKAGE_WORKSHEETS + "/sheet%d.xml" % (i + 1), write_worksheet(sheet, shared_string_table, style_writer.get_style_by_hash()), ) if sheet._charts or sheet._images or sheet.relationships: archive.writestr( PACKAGE_WORKSHEETS + "/_rels/sheet%d.xml.rels" % (i + 1), write_worksheet_rels(sheet, drawing_id) ) if sheet._charts or sheet._images: dw = DrawingWriter(sheet) archive.writestr(PACKAGE_DRAWINGS + "/drawing%d.xml" % drawing_id, dw.write()) archive.writestr( PACKAGE_DRAWINGS + "/_rels/drawing%d.xml.rels" % drawing_id, dw.write_rels(chart_id, image_id) ) drawing_id += 1 for chart in sheet._charts: cw = ChartWriter(chart) archive.writestr(PACKAGE_CHARTS + "/chart%d.xml" % chart_id, cw.write()) if chart._shapes: archive.writestr( PACKAGE_CHARTS + "/_rels/chart%d.xml.rels" % chart_id, cw.write_rels(drawing_id) ) sw = ShapeWriter(chart._shapes) archive.writestr(PACKAGE_DRAWINGS + "/drawing%d.xml" % drawing_id, sw.write(shape_id)) shape_id += len(chart._shapes) drawing_id += 1 chart_id += 1 for img in sheet._images: buf = StringIO() img.image.save(buf, format="PNG") archive.writestr(PACKAGE_IMAGES + "/image%d.png" % image_id, buf.getvalue()) image_id += 1
def write_string_table(string_table): """Write the string table xml.""" temp_buffer = StringIO() doc = XMLGenerator(out=temp_buffer, encoding='utf-8') start_tag(doc, 'sst', {'xmlns': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main', 'uniqueCount': '%d' % len(string_table)}) strings_to_write = sorted(string_table.items(), key=lambda pair: pair[1]) for key in [pair[0] for pair in strings_to_write]: start_tag(doc, 'si') if key.strip() != key: attr = {'xml:space': 'preserve'} else: attr = {} tag(doc, 't', attr, key) end_tag(doc, 'si') end_tag(doc, 'sst') string_table_xml = temp_buffer.getvalue() temp_buffer.close() return string_table_xml
def write_string_table(string_table): """Write the string table xml.""" temp_buffer = StringIO() doc = XMLGenerator(out=temp_buffer, encoding='utf-8') start_tag( doc, 'sst', { 'xmlns': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main', 'uniqueCount': '%d' % len(string_table) }) strings_to_write = sorted(string_table.items(), key=lambda pair: pair[1]) for key in [pair[0] for pair in strings_to_write]: start_tag(doc, 'si') if key.strip() != key: attr = {'xml:space': 'preserve'} else: attr = {} tag(doc, 't', attr, key) end_tag(doc, 'si') end_tag(doc, 'sst') string_table_xml = temp_buffer.getvalue() temp_buffer.close() return string_table_xml
def repair_central_directory(zipFile, is_file_instance): ''' trims trailing data from the central directory code taken from http://stackoverflow.com/a/7457686/570216, courtesy of Uri Cohen ''' f = zipFile if is_file_instance else open(zipFile, 'r+b') data = f.read() if hasattr(data, "decode"): data = data.decode("utf-8") pos = data.find( CENTRAL_DIRECTORY_SIGNATURE) # End of central directory signature if (pos > 0): sio = StringIO(data) sio.seek(pos + 22) # size of 'ZIP end of central directory record' sio.truncate() sio.seek(0) return sio f.seek(0) return f
def repair_central_directory(zipFile, is_file_instance): ''' trims trailing data from the central directory code taken from http://stackoverflow.com/a/7457686/570216, courtesy of Uri Cohen ''' f = zipFile if is_file_instance else open(zipFile, 'r+b') data = f.read() if hasattr(data, "decode"): data = data.decode("utf-8") pos = data.find(CENTRAL_DIRECTORY_SIGNATURE) # End of central directory signature if (pos > 0): sio = StringIO(data) sio.seek(pos + 22) # size of 'ZIP end of central directory record' sio.truncate() sio.seek(0) return sio f.seek(0) return f
def assert_equals_file_content(reference_file, fixture, filetype='xml'): if os.path.isfile(fixture): fixture_file = open(fixture) try: fixture_content = fixture_file.read() finally: fixture_file.close() else: fixture_content = fixture expected_file = open(reference_file) try: expected_content = expected_file.read() finally: expected_file.close() if filetype == 'xml': fixture_content = fromstring(fixture_content) pretty_indent(fixture_content) temp = BytesIO() ElementTree(fixture_content).write(temp) fixture_content = temp.getvalue() expected_content = fromstring(expected_content) pretty_indent(expected_content) temp = BytesIO() ElementTree(expected_content).write(temp) expected_content = temp.getvalue() fixture_lines = unicode(fixture_content).split('\n') expected_lines = unicode(expected_content).split('\n') differences = list(difflib.unified_diff(expected_lines, fixture_lines)) if differences: temp = StringIO() pprint(differences, stream=temp) assert False, 'Differences found : %s' % temp.getvalue()
def write_worksheet(worksheet, string_table, style_table): """Write a worksheet to an xml file.""" if worksheet.xml_source: vba_root = fromstring(worksheet.xml_source) else: vba_root = None xml_file = StringIO() doc = XMLGenerator(out=xml_file, encoding='utf-8') start_tag(doc, 'worksheet', {'xml:space': 'preserve', 'xmlns': SHEET_MAIN_NS, 'xmlns:r': REL_NS}) if vba_root is not None: codename = vba_root.find('{%s}sheetPr' % SHEET_MAIN_NS).get('codeName', worksheet.title) start_tag(doc, 'sheetPr', {"codeName": codename}) else: start_tag(doc, 'sheetPr') tag(doc, 'outlinePr', {'summaryBelow': '%d' % (worksheet.show_summary_below), 'summaryRight': '%d' % (worksheet.show_summary_right)}) if worksheet.page_setup.fitToPage: tag(doc, 'pageSetUpPr', {'fitToPage':'1'}) end_tag(doc, 'sheetPr') tag(doc, 'dimension', {'ref': '%s' % worksheet.calculate_dimension()}) write_worksheet_sheetviews(doc, worksheet) tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'}) write_worksheet_cols(doc, worksheet, style_table) write_worksheet_data(doc, worksheet, string_table, style_table) if worksheet.auto_filter: tag(doc, 'autoFilter', {'ref': worksheet.auto_filter}) write_worksheet_mergecells(doc, worksheet) write_worksheet_datavalidations(doc, worksheet) write_worksheet_hyperlinks(doc, worksheet) for range_string, rules in worksheet.conditional_formatting.cf_rules.iteritems(): if not len(rules): # Skip if there are no rules. This is possible if a dataBar rule was read in and ignored. continue start_tag(doc, 'conditionalFormatting', {'sqref': range_string}) for rule in rules: if rule['type'] == 'dataBar': # Ignore - uses extLst tag which is currently unsupported. continue attr = {'type': rule['type']} for rule_attr in ConditionalFormatting.rule_attributes: if rule_attr in rule: attr[rule_attr] = str(rule[rule_attr]) start_tag(doc, 'cfRule', attr) if 'formula' in rule: for f in rule['formula']: tag(doc, 'formula', None, f) if 'colorScale' in rule: start_tag(doc, 'colorScale') for cfvo in rule['colorScale']['cfvo']: tag(doc, 'cfvo', cfvo) for color in rule['colorScale']['color']: if str(color.index).split(':')[0] == 'theme': # strip prefix theme if marked as such if str(color.index).split(':')[2]: tag(doc, 'color', {'theme': str(color.index).split(':')[1], 'tint': str(color.index).split(':')[2]}) else: tag(doc, 'color', {'theme': str(color.index).split(':')[1]}) else: tag(doc, 'color', {'rgb': str(color.index)}) end_tag(doc, 'colorScale') if 'iconSet' in rule: iconAttr = {} for icon_attr in ConditionalFormatting.icon_attributes: if icon_attr in rule['iconSet']: iconAttr[icon_attr] = rule['iconSet'][icon_attr] start_tag(doc, 'iconSet', iconAttr) for cfvo in rule['iconSet']['cfvo']: tag(doc, 'cfvo', cfvo) end_tag(doc, 'iconSet') end_tag(doc, 'cfRule') end_tag(doc, 'conditionalFormatting') options = worksheet.page_setup.options if options: tag(doc, 'printOptions', options) margins = worksheet.page_margins.margins if margins: tag(doc, 'pageMargins', margins) setup = worksheet.page_setup.setup if setup: tag(doc, 'pageSetup', setup) if worksheet.header_footer.hasHeader() or worksheet.header_footer.hasFooter(): start_tag(doc, 'headerFooter') if worksheet.header_footer.hasHeader(): tag(doc, 'oddHeader', None, worksheet.header_footer.getHeader()) if worksheet.header_footer.hasFooter(): tag(doc, 'oddFooter', None, worksheet.header_footer.getFooter()) end_tag(doc, 'headerFooter') if worksheet._charts or worksheet._images: tag(doc, 'drawing', {'r:id':'rId1'}) # if the sheet has an xml_source field then the workbook must have # been loaded with keep-vba true and we need to extract any control # elements. if vba_root is not None: for t in ('{%s}legacyDrawing' % SHEET_MAIN_NS, '{%s}controls' % SHEET_MAIN_NS): for elem in vba_root.findall(t): xml_file.write(re.sub(r' xmlns[^ >]*', '', tostring(elem).decode("utf-8"))) breaks = worksheet.page_breaks if breaks: start_tag(doc, 'rowBreaks', {'count': str(len(breaks)), 'manualBreakCount': str(len(breaks))}) for b in breaks: tag(doc, 'brk', {'id': str(b), 'man': 'true', 'max': '16383', 'min': '0'}) end_tag(doc, 'rowBreaks') end_tag(doc, 'worksheet') doc.endDocument() xml_string = xml_file.getvalue() xml_file.close() return xml_string
def write_worksheet(worksheet, string_table, style_table): """Write a worksheet to an xml file.""" if worksheet.xml_source: vba_root = fromstring(worksheet.xml_source) else: vba_root = None xml_file = StringIO() doc = XMLGenerator(out=xml_file, encoding='utf-8') start_tag(doc, 'worksheet', {'xmlns': SHEET_MAIN_NS, 'xmlns:r': REL_NS}) if vba_root is not None: el = vba_root.find('{%s}sheetPr' % SHEET_MAIN_NS) if el is not None: codename =el.get('codeName', worksheet.title) start_tag(doc, 'sheetPr', {"codeName": codename}) else: start_tag(doc, 'sheetPr') else: start_tag(doc, 'sheetPr') tag(doc, 'outlinePr', {'summaryBelow': '%d' % (worksheet.show_summary_below), 'summaryRight': '%d' % (worksheet.show_summary_right)}) if worksheet.page_setup.fitToPage: tag(doc, 'pageSetUpPr', {'fitToPage':'1'}) end_tag(doc, 'sheetPr') tag(doc, 'dimension', {'ref': '%s' % worksheet.calculate_dimension()}) write_worksheet_sheetviews(doc, worksheet) tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'}) write_worksheet_cols(doc, worksheet, style_table) write_worksheet_data(doc, worksheet, string_table, style_table) if worksheet.auto_filter: tag(doc, 'autoFilter', {'ref': worksheet.auto_filter}) write_worksheet_mergecells(doc, worksheet) write_worksheet_datavalidations(doc, worksheet) write_worksheet_hyperlinks(doc, worksheet) write_worksheet_conditional_formatting(doc, worksheet) options = worksheet.page_setup.options if options: tag(doc, 'printOptions', options) margins = worksheet.page_margins.margins if margins: tag(doc, 'pageMargins', margins) setup = worksheet.page_setup.setup if setup: tag(doc, 'pageSetup', setup) if worksheet.header_footer.hasHeader() or worksheet.header_footer.hasFooter(): start_tag(doc, 'headerFooter') if worksheet.header_footer.hasHeader(): tag(doc, 'oddHeader', None, worksheet.header_footer.getHeader()) if worksheet.header_footer.hasFooter(): tag(doc, 'oddFooter', None, worksheet.header_footer.getFooter()) end_tag(doc, 'headerFooter') if worksheet._charts or worksheet._images: tag(doc, 'drawing', {'r:id':'rId1'}) # If vba is being preserved then add a legacyDrawing element so # that any controls can be drawn. if vba_root is not None: el = vba_root.find('{%s}legacyDrawing' % SHEET_MAIN_NS) if el is not None: rId = el.get('{%s}id' % REL_NS) tag(doc, 'legacyDrawing', {'r:id': rId}) breaks = worksheet.page_breaks if breaks: start_tag(doc, 'rowBreaks', {'count': str(len(breaks)), 'manualBreakCount': str(len(breaks))}) for b in breaks: tag(doc, 'brk', {'id': str(b), 'man': 'true', 'max': '16383', 'min': '0'}) end_tag(doc, 'rowBreaks') # add a legacyDrawing so that excel can draw comments if worksheet._comment_count > 0: tag(doc, 'legacyDrawing', {'r:id':'commentsvml'}) end_tag(doc, 'worksheet') doc.endDocument() xml_string = xml_file.getvalue() xml_file.close() return xml_string
def write_worksheet(worksheet, string_table, style_table): """Write a worksheet to an xml file.""" if worksheet.xml_source: vba_root = fromstring(worksheet.xml_source) register_namespace("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships") register_namespace("", "http://schemas.openxmlformats.org/spreadsheetml/2006/main") else: vba_root = None xml_file = StringIO() doc = XMLGenerator(out=xml_file, encoding='utf-8') start_tag(doc, 'worksheet', {'xml:space': 'preserve', 'xmlns': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main', 'xmlns:r': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'}) if vba_root is not None: codename = vba_root.find('{http://schemas.openxmlformats.org/spreadsheetml/2006/main}sheetPr').get('codeName', worksheet.title) start_tag(doc, 'sheetPr', {"codeName": codename}) else: start_tag(doc, 'sheetPr') tag(doc, 'outlinePr', {'summaryBelow': '%d' % (worksheet.show_summary_below), 'summaryRight': '%d' % (worksheet.show_summary_right)}) if worksheet.page_setup.fitToPage: tag(doc, 'pageSetUpPr', {'fitToPage':'1'}) end_tag(doc, 'sheetPr') tag(doc, 'dimension', {'ref': '%s' % worksheet.calculate_dimension()}) write_worksheet_sheetviews(doc, worksheet) tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'}) write_worksheet_cols(doc, worksheet) write_worksheet_data(doc, worksheet, string_table, style_table) if worksheet.auto_filter: tag(doc, 'autoFilter', {'ref': worksheet.auto_filter}) write_worksheet_mergecells(doc, worksheet) write_worksheet_datavalidations(doc, worksheet) write_worksheet_hyperlinks(doc, worksheet) options = worksheet.page_setup.options if options: tag(doc, 'printOptions', options) margins = worksheet.page_margins.margins if margins: tag(doc, 'pageMargins', margins) setup = worksheet.page_setup.setup if setup: tag(doc, 'pageSetup', setup) if worksheet.header_footer.hasHeader() or worksheet.header_footer.hasFooter(): start_tag(doc, 'headerFooter') if worksheet.header_footer.hasHeader(): tag(doc, 'oddHeader', None, worksheet.header_footer.getHeader()) if worksheet.header_footer.hasFooter(): tag(doc, 'oddFooter', None, worksheet.header_footer.getFooter()) end_tag(doc, 'headerFooter') if worksheet._charts or worksheet._images: tag(doc, 'drawing', {'r:id':'rId1'}) # if the sheet has an xml_source field then the workbook must have # been loaded with keep-vba true and we need to extract any control # elements. if vba_root is not None: for t in ('{http://schemas.openxmlformats.org/spreadsheetml/2006/main}legacyDrawing', '{http://schemas.openxmlformats.org/spreadsheetml/2006/main}controls'): for elem in vba_root.findall(t): xml_file.write(re.sub(r' xmlns[^ >]*', '', tostring(elem).decode("utf-8"))) breaks = worksheet.page_breaks if breaks: start_tag(doc, 'rowBreaks', {'count': str(len(breaks)), 'manualBreakCount': str(len(breaks))}) for b in breaks: tag(doc, 'brk', {'id': str(b), 'man': 'true', 'max': '16383', 'min': '0'}) end_tag(doc, 'rowBreaks') end_tag(doc, 'worksheet') doc.endDocument() xml_string = xml_file.getvalue() xml_file.close() return xml_string
def write_worksheet(worksheet, string_table, style_table): """Write a worksheet to an xml file.""" if worksheet.xml_source: vba_root = fromstring(worksheet.xml_source) register_namespace( "r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships" ) register_namespace( "", "http://schemas.openxmlformats.org/spreadsheetml/2006/main") else: vba_root = None xml_file = StringIO() doc = XMLGenerator(out=xml_file, encoding='utf-8') start_tag( doc, 'worksheet', { 'xml:space': 'preserve', 'xmlns': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main', 'xmlns:r': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships' }) if vba_root is not None: codename = vba_root.find( '{http://schemas.openxmlformats.org/spreadsheetml/2006/main}sheetPr' ).get('codeName', worksheet.title) start_tag(doc, 'sheetPr', {"codeName": codename}) else: start_tag(doc, 'sheetPr') tag( doc, 'outlinePr', { 'summaryBelow': '%d' % (worksheet.show_summary_below), 'summaryRight': '%d' % (worksheet.show_summary_right) }) if worksheet.page_setup.fitToPage: tag(doc, 'pageSetUpPr', {'fitToPage': '1'}) end_tag(doc, 'sheetPr') tag(doc, 'dimension', {'ref': '%s' % worksheet.calculate_dimension()}) write_worksheet_sheetviews(doc, worksheet) tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'}) write_worksheet_cols(doc, worksheet) write_worksheet_data(doc, worksheet, string_table, style_table) if worksheet.auto_filter: tag(doc, 'autoFilter', {'ref': worksheet.auto_filter}) write_worksheet_mergecells(doc, worksheet) write_worksheet_datavalidations(doc, worksheet) write_worksheet_hyperlinks(doc, worksheet) options = worksheet.page_setup.options if options: tag(doc, 'printOptions', options) margins = worksheet.page_margins.margins if margins: tag(doc, 'pageMargins', margins) setup = worksheet.page_setup.setup if setup: tag(doc, 'pageSetup', setup) if worksheet.header_footer.hasHeader( ) or worksheet.header_footer.hasFooter(): start_tag(doc, 'headerFooter') if worksheet.header_footer.hasHeader(): tag(doc, 'oddHeader', None, worksheet.header_footer.getHeader()) if worksheet.header_footer.hasFooter(): tag(doc, 'oddFooter', None, worksheet.header_footer.getFooter()) end_tag(doc, 'headerFooter') if worksheet._charts or worksheet._images: tag(doc, 'drawing', {'r:id': 'rId1'}) # if the sheet has an xml_source field then the workbook must have # been loaded with keep-vba true and we need to extract any control # elements. if vba_root is not None: for t in ( '{http://schemas.openxmlformats.org/spreadsheetml/2006/main}legacyDrawing', '{http://schemas.openxmlformats.org/spreadsheetml/2006/main}controls' ): for elem in vba_root.findall(t): xml_file.write( re.sub(r' xmlns[^ >]*', '', tostring(elem).decode("utf-8"))) breaks = worksheet.page_breaks if breaks: start_tag(doc, 'rowBreaks', { 'count': str(len(breaks)), 'manualBreakCount': str(len(breaks)) }) for b in breaks: tag(doc, 'brk', { 'id': str(b), 'man': 'true', 'max': '16383', 'min': '0' }) end_tag(doc, 'rowBreaks') end_tag(doc, 'worksheet') doc.endDocument() xml_string = xml_file.getvalue() xml_file.close() return xml_string
def write_worksheet(worksheet, string_table, style_table): """Write a worksheet to an xml file.""" if worksheet.xml_source: vba_root = fromstring(worksheet.xml_source) else: vba_root = None xml_file = StringIO() doc = XMLGenerator(out=xml_file, encoding='utf-8') start_tag(doc, 'worksheet', {'xmlns': SHEET_MAIN_NS, 'xmlns:r': REL_NS}) if vba_root is not None: el = vba_root.find('{%s}sheetPr' % SHEET_MAIN_NS) if el is not None: codename = el.get('codeName', worksheet.title) start_tag(doc, 'sheetPr', {"codeName": codename}) else: start_tag(doc, 'sheetPr') else: start_tag(doc, 'sheetPr') tag( doc, 'outlinePr', { 'summaryBelow': '%d' % (worksheet.show_summary_below), 'summaryRight': '%d' % (worksheet.show_summary_right) }) if worksheet.page_setup.fitToPage: tag(doc, 'pageSetUpPr', {'fitToPage': '1'}) end_tag(doc, 'sheetPr') tag(doc, 'dimension', {'ref': '%s' % worksheet.calculate_dimension()}) write_worksheet_sheetviews(doc, worksheet) tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'}) write_worksheet_cols(doc, worksheet, style_table) write_worksheet_data(doc, worksheet, string_table, style_table) if worksheet.auto_filter: tag(doc, 'autoFilter', {'ref': worksheet.auto_filter}) write_worksheet_mergecells(doc, worksheet) write_worksheet_datavalidations(doc, worksheet) write_worksheet_hyperlinks(doc, worksheet) write_worksheet_conditional_formatting(doc, worksheet) options = worksheet.page_setup.options if options: tag(doc, 'printOptions', options) margins = worksheet.page_margins.margins if margins: tag(doc, 'pageMargins', margins) setup = worksheet.page_setup.setup if setup: tag(doc, 'pageSetup', setup) if worksheet.header_footer.hasHeader( ) or worksheet.header_footer.hasFooter(): start_tag(doc, 'headerFooter') if worksheet.header_footer.hasHeader(): tag(doc, 'oddHeader', None, worksheet.header_footer.getHeader()) if worksheet.header_footer.hasFooter(): tag(doc, 'oddFooter', None, worksheet.header_footer.getFooter()) end_tag(doc, 'headerFooter') if worksheet._charts or worksheet._images: tag(doc, 'drawing', {'r:id': 'rId1'}) # If vba is being preserved then add a legacyDrawing element so # that any controls can be drawn. if vba_root is not None: el = vba_root.find('{%s}legacyDrawing' % SHEET_MAIN_NS) if el is not None: rId = el.get('{%s}id' % REL_NS) tag(doc, 'legacyDrawing', {'r:id': rId}) breaks = worksheet.page_breaks if breaks: start_tag(doc, 'rowBreaks', { 'count': str(len(breaks)), 'manualBreakCount': str(len(breaks)) }) for b in breaks: tag(doc, 'brk', { 'id': str(b), 'man': 'true', 'max': '16383', 'min': '0' }) end_tag(doc, 'rowBreaks') # add a legacyDrawing so that excel can draw comments if worksheet._comment_count > 0: tag(doc, 'legacyDrawing', {'r:id': 'commentsvml'}) end_tag(doc, 'worksheet') doc.endDocument() xml_string = xml_file.getvalue() xml_file.close() return xml_string