def test_conditional_formatting_update(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.update(rules) temp_buffer = BytesIO() doc = XMLGenerator(out=temp_buffer) write_worksheet_conditional_formatting(doc, worksheet) doc.endDocument() xml = temp_buffer.getvalue() temp_buffer.close() diff = compare_xml(xml, """ <conditionalFormatting sqref="A1:A4"> <cfRule type="colorScale" priority="1"> <colorScale> <cfvo type="min" /> <cfvo type="max" /> <color rgb="FFFF7128" /> <color rgb="FFFFEF9C" /> </colorScale> </cfRule> </conditionalFormatting> """) assert diff is None, diff
def test_conditional_formatting_customRule(self): class WS(): conditional_formatting = ConditionalFormatting() worksheet = WS() worksheet.conditional_formatting.add( 'C1:C10', { 'type': 'expression', 'formula': ['ISBLANK(C1)'], 'stopIfTrue': '1', 'dxf': {} }) worksheet.conditional_formatting.setDxfStyles(self.workbook) temp_buffer = BytesIO() doc = XMLGenerator(out=temp_buffer) write_worksheet_conditional_formatting(doc, worksheet) doc.endDocument() xml = temp_buffer.getvalue() temp_buffer.close() diff = compare_xml( xml, """ <conditionalFormatting sqref="C1:C10"> <cfRule dxfId="0" type="expression" stopIfTrue="1" priority="1"> <formula>ISBLANK(C1)</formula> </cfRule> </conditionalFormatting> """) assert diff is None, diff
def standard(): archive = ZipFile("std.zip", "w", ZIP_DEFLATED) out = BytesIO() doc = XMLGenerator(out) for i in range(1000000): start_tag(doc, "test") end_tag(doc, "test") doc.endDocument() archive.writestr("sheet.xml", out.getvalue(), ZIP_DEFLATED) archive.close()
def buffered(): archive = EnhZipFile("buffered.zip", "w", ZIP_DEFLATED) out = archive.start_entry(ZipInfo("sheet.xml")) #out.write = partial(out.write, compress_type=ZIP_DEFLATED) doc = XMLGenerator(out) for i in range(1000000): start_tag(doc, "test") end_tag(doc, "test") doc.endDocument() out.close() archive.close()
def test_conditional_font(self): """Test to verify font style written correctly.""" class WS(): conditional_formatting = ConditionalFormatting() worksheet = WS() # Create cf rule redFill = PatternFill(start_color=Color('FFEE1111'), end_color=Color('FFEE1111'), patternType=fills.FILL_SOLID) whiteFont = Font(color=Color("FFFFFFFF")) worksheet.conditional_formatting.add('A1:A3', CellIsRule(operator='equal', formula=['"Fail"'], stopIfTrue=False, font=whiteFont, fill=redFill)) worksheet.conditional_formatting.setDxfStyles(self.workbook) # First, verify conditional formatting xml temp_buffer = BytesIO() doc = XMLGenerator(out=temp_buffer) write_worksheet_conditional_formatting(doc, worksheet) doc.endDocument() xml = temp_buffer.getvalue() temp_buffer.close() diff = compare_xml(xml, """ <conditionalFormatting sqref="A1:A3"> <cfRule dxfId="0" operator="equal" priority="1" type="cellIs"> <formula>"Fail"</formula> </cfRule> </conditionalFormatting> """) assert diff is None, diff # Second, verify conditional formatting dxf styles w = StyleWriter(self.workbook) w._write_dxfs() xml = get_xml(w._root) diff = compare_xml(xml, """ <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <dxfs count="1"> <dxf> <font> <color rgb="FFFFFFFF" /> </font> <fill> <patternFill patternType="solid"> <fgColor rgb="FFEE1111" /> <bgColor rgb="FFEE1111" /> </patternFill> </fill> </dxf> </dxfs> </styleSheet> """) assert diff is None, diff
def test_conditional_formatting_update(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.update(rules) temp_buffer = BytesIO() doc = XMLGenerator(out=temp_buffer) write_worksheet_conditional_formatting(doc, worksheet) doc.endDocument() xml = temp_buffer.getvalue() temp_buffer.close() diff = compare_xml( xml, """ <conditionalFormatting sqref="A1:A4"> <cfRule type="colorScale" priority="1"> <colorScale> <cfvo type="min" /> <cfvo type="max" /> <color rgb="FFFF7128" /> <color rgb="FFFFEF9C" /> </colorScale> </cfRule> </conditionalFormatting> """) assert diff is None, diff
def test_formula_rule(self): class WS(): conditional_formatting = ConditionalFormatting() worksheet = WS() worksheet.conditional_formatting.add('C1:C10', FormulaRule(formula=['ISBLANK(C1)'], stopIfTrue=True)) worksheet.conditional_formatting.setDxfStyles(self.workbook) temp_buffer = BytesIO() doc = XMLGenerator(out=temp_buffer) write_worksheet_conditional_formatting(doc, worksheet) doc.endDocument() xml = temp_buffer.getvalue() temp_buffer.close() diff = compare_xml(xml, """ <conditionalFormatting sqref="C1:C10"> <cfRule dxfId="0" type="expression" stopIfTrue="1" priority="1"> <formula>ISBLANK(C1)</formula> </cfRule> </conditionalFormatting> """) assert diff is None, diff
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.protection.enabled: tag(doc, 'sheetProtection', {'objects': '1', 'scenarios': '1', 'sheet': '1'}) write_worksheet_autofilter(doc, worksheet) 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) 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.protection.enabled: tag(doc, 'sheetProtection', { 'objects': '1', 'scenarios': '1', 'sheet': '1' }) write_worksheet_autofilter(doc, worksheet) 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, shared_strings): """Write a worksheet to an xml file.""" if worksheet.xml_source: vba_root = fromstring(worksheet.xml_source) else: vba_root = None xml_file = BytesIO() doc = XMLGenerator(out=xml_file) start_tag(doc, 'worksheet', {'xmlns': SHEET_MAIN_NS, 'xmlns:r': REL_NS}) vba_attrs = {} if vba_root is not None: el = vba_root.find('{%s}sheetPr' % SHEET_MAIN_NS) if el is not None: vba_attrs['codeName'] = el.get('codeName', worksheet.title) start_tag(doc, 'sheetPr', vba_attrs) 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) write_worksheet_format(doc, worksheet) write_worksheet_cols(doc, worksheet) write_worksheet_data(doc, worksheet, shared_strings) if worksheet.protection.sheet: tag(doc, 'sheetProtection', dict(worksheet.protection)) write_worksheet_autofilter(doc, worksheet) write_worksheet_mergecells(doc, worksheet) write_worksheet_conditional_formatting(doc, worksheet) write_worksheet_datavalidations(doc, worksheet) write_worksheet_hyperlinks(doc, worksheet) options = worksheet.page_setup.options if options: tag(doc, 'printOptions', options) tag(doc, 'pageMargins', dict(worksheet.page_margins)) setup = worksheet.page_setup.setup if setup: tag(doc, 'pageSetup', setup) write_header_footer(doc, worksheet) 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}) write_pagebreaks(doc, worksheet) # 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, shared_strings): """Write a worksheet to an xml file.""" xml_file = BytesIO() doc = XMLGenerator(out=xml_file) start_tag(doc, 'worksheet', {'xmlns': SHEET_MAIN_NS, 'xmlns:r': REL_NS}) props = write_properties(worksheet, worksheet.vba_code) xml_file.write(tostring(props)) dim = Element('dimension', {'ref': '%s' % worksheet.calculate_dimension()}) xml_file.write(tostring(dim)) views = write_sheetviews(worksheet) xml_file.write(tostring(views)) fmt = write_format(worksheet) xml_file.write(tostring(fmt)) cols = write_cols(worksheet) if cols is not None: xml_file.write(tostring(cols)) write_worksheet_data(doc, worksheet) if worksheet.protection.sheet: prot = Element('sheetProtection', dict(worksheet.protection)) xml_file.write(tostring(prot)) af = write_autofilter(worksheet) if af is not None: xml_file.write(tostring(af)) merge = write_mergecells(worksheet) if merge is not None: xml_file.write(tostring(merge)) cfs = write_conditional_formatting(worksheet) for cf in cfs: xml_file.write(tostring(cf)) dvs = write_datavalidation(worksheet) if dvs: xml_file.write(tostring(dvs)) hyper = write_hyperlinks(worksheet) if hyper is not None: xml_file.write(tostring(hyper)) options = worksheet.page_setup.options if options: tag(doc, 'printOptions', options) tag(doc, 'pageMargins', dict(worksheet.page_margins)) setup = worksheet.page_setup.setup if setup: tag(doc, 'pageSetup', setup) hf = write_header_footer(worksheet) if hf is not None: xml_file.write(tostring(hf)) if worksheet._charts or worksheet._images: tag(doc, 'drawing', {'r:id': 'rId1'}) if worksheet.vba_controls is not None: xml = Element("{%s}legacyDrawing" % SHEET_MAIN_NS, {"{%s}id" % REL_NS : worksheet.vba_controls}) xml_file.write(tostring(xml)) breaks = write_pagebreaks(worksheet) if breaks is not None: xml_file.write(tostring(breaks)) # 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 buffered(): archive = EnhZipFile("buffered.zip", "w", ZIP_DEFLATED) out = archive.start_entry(ZipInfo("sheet.xml")) #out.write = partial(out.write, compress_type=ZIP_DEFLATED) doc = XMLGenerator(out) for i in range(1000000): start_tag(doc, "test") end_tag(doc, "test") doc.endDocument() out.close() archive.close() if __name__ == "__main__": zf = EnhZipFile('t.zip', 'w') import time from openpyxl.xml.functions import XMLGenerator, start_tag, end_tag w = zf.start_entry(ZipInfo('t.txt', time.localtime()[:6])) doc = XMLGenerator(w) start_tag(doc, "test") end_tag(doc, "test") doc.endDocument() w.write(u"Line1\n") w.write(u"Line2\n") w.close() zf.finish_entry() w = zf.start_entry(ZipInfo('p.txt', time.localtime()[:6])) w.write("Some text\n") w.close() zf.close()
def test_conditional_font(self): """Test to verify font style written correctly.""" class WS(): conditional_formatting = ConditionalFormatting() worksheet = WS() # Create cf rule redFill = PatternFill(start_color=Color('FFEE1111'), end_color=Color('FFEE1111'), patternType=fills.FILL_SOLID) whiteFont = Font(color=Color("FFFFFFFF")) worksheet.conditional_formatting.add( 'A1:A3', CellIsRule(operator='equal', formula=['"Fail"'], stopIfTrue=False, font=whiteFont, fill=redFill)) worksheet.conditional_formatting.setDxfStyles(self.workbook) # First, verify conditional formatting xml temp_buffer = BytesIO() doc = XMLGenerator(out=temp_buffer) write_worksheet_conditional_formatting(doc, worksheet) doc.endDocument() xml = temp_buffer.getvalue() temp_buffer.close() diff = compare_xml( xml, """ <conditionalFormatting sqref="A1:A3"> <cfRule dxfId="0" operator="equal" priority="1" type="cellIs"> <formula>"Fail"</formula> </cfRule> </conditionalFormatting> """) assert diff is None, diff # Second, verify conditional formatting dxf styles w = StyleWriter(self.workbook) w._write_dxfs() xml = get_xml(w._root) diff = compare_xml( xml, """ <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <dxfs count="1"> <dxf> <font> <color rgb="FFFFFFFF" /> </font> <fill> <patternFill patternType="solid"> <fgColor rgb="FFEE1111" /> <bgColor rgb="FFEE1111" /> </patternFill> </fill> </dxf> </dxfs> </styleSheet> """) assert diff is None, diff