def test_serialise(self, Border, Side): medium_blue = Side(border_style='medium', color=Color(colors.BLUE)) bd = Border( left=medium_blue, right=medium_blue, top=medium_blue, bottom=medium_blue, outline=False, diagonalDown=True, ) xml = tostring(bd.to_tree()) expected = """ <border diagonalDown="1" outline="0"> <left style="medium"> <color rgb="000000FF"></color> </left> <right style="medium"> <color rgb="000000FF"></color> </right> <top style="medium"> <color rgb="000000FF"></color> </top> <bottom style="medium"> <color rgb="000000FF"></color> </bottom> <diagonal /> </border> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_print_autofilter(Unicode_Workbook): wb = Unicode_Workbook ws = wb.active from openpyxl25.worksheet.filters import AutoFilter ws.auto_filter.ref = "A1:A10" ws.auto_filter.add_filter_column(0, ["Kiwi", "Apple", "Mango"]) xml = write_workbook(wb) expected = """ <workbook xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <workbookPr/> <workbookProtection/> <bookViews> <workbookView activeTab="0"/> </bookViews> <sheets> <sheet name="Düsseldorf Sheet" sheetId="1" state="visible" r:id="rId1"/> </sheets> <definedNames> <definedName localSheetId="0" hidden="1" name="_xlnm._FilterDatabase">'Düsseldorf Sheet'!$A$1:$A$10</definedName> </definedNames> <calcPr calcId="124519" fullCalcOnLoad="1"/> </workbook> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_write(self, Chartsheet): sheetview = ChartsheetView(tabSelected=True, zoomScale=80, workbookViewId=0, zoomToFit=True) chartsheetViews = ChartsheetViewList(sheetView=[sheetview]) pageMargins = PageMargins(left=0.7, right=0.7, top=0.75, bottom=0.75, header=0.3, footer=0.3) drawing = Drawing("rId1") item = Chartsheet(sheetViews=chartsheetViews, pageMargins=pageMargins, drawing=drawing) expected = """ <chartsheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"> <sheetViews> <sheetView tabSelected="1" zoomScale="80" workbookViewId="0" zoomToFit="1"/> </sheetViews> <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/> <drawing r:id="rId1"/> </chartsheet> """ xml = tostring(item.to_tree()) diff = compare_xml(xml, expected) assert diff is None, diff
def test_write_comments(worksheet, write_worksheet): ws = worksheet worksheet._comments = True xml = write_worksheet(ws) expected = """ <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"> <sheetPr> <outlinePr summaryBelow="1" summaryRight="1"/> <pageSetUpPr/> </sheetPr> <dimension ref="A1:A1"/> <sheetViews> <sheetView workbookViewId="0"> <selection activeCell="A1" sqref="A1"/> </sheetView> </sheetViews> <sheetFormatPr baseColWidth="8" defaultRowHeight="15"/> <sheetData/> <pageMargins bottom="1" footer="0.5" header="0.5" left="0.75" right="0.75" top="1"/> <legacyDrawing r:id="anysvml"></legacyDrawing> </worksheet> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_zvalues(self, Series): series = Series("Sheet!A2:A5", xvalues="Sheet!B2:B5", zvalues="Sheet!C2:C5") series.__elements__ = ('xVal', 'yVal', 'bubbleSize') xml = tostring(series.to_tree()) expected = """ <ser> <xVal> <numRef> <f>Sheet!$B$2:$B$5</f> </numRef> </xVal> <yVal> <numRef> <f>Sheet!$A$2:$A$5</f> </numRef> </yVal> <bubbleSize> <numRef> <f>Sheet!$C$2:$C$5</f> </numRef> </bubbleSize> </ser> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_ctor(self, ComplexObject): style = ComplexObject() ft1 = Font(family=2, sz=11, name="Arial") ft2 = Font(bold=True) style.fonts = [ft1, ft2] expected = """ <style> <fonts count="2"> <font> <name val="Arial" /> <family val="2"></family> <sz val="11"></sz> </font> <font> <b val="1"></b> </font> </fonts> </style> """ tree = style.__class__.fonts.to_tree('fonts', style.fonts) tree = style.to_tree() xml = tostring(tree) diff = compare_xml(xml, expected) assert diff is None, diff
def test_from_tree(self): from openpyxl25.chart.series import Series, attribute_mapping src = """ <ser> <idx val="0"/> <order val="0"/> <spPr> <a:ln xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"> <a:prstDash val="solid" /> </a:ln> </spPr> <val> <numRef> <f>Blatt1!$A$1:$A$12</f> </numRef> </val> </ser> """ node = fromstring(src) ser = Series.from_tree(node) assert ser.idx == 0 assert ser.order == 0 assert ser.val.numRef.ref == 'Blatt1!$A$1:$A$12' ser.__elements__ = attribute_mapping['area'] xml = tostring(ser.to_tree()) diff = compare_xml(xml, src) assert diff is None, diff
def test_conditional_formatting(WriteOnlyWorksheet): from openpyxl25.formatting.rule import CellIsRule ws = WriteOnlyWorksheet rule = CellIsRule(operator='lessThan', formula=['C$1'], stopIfTrue=True) ws.conditional_formatting.add("C1:C10", rule) ws.close() with open(ws.filename) as src: xml = src.read() expected = """ <worksheet xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <sheetPr> <outlinePr summaryRight="1" summaryBelow="1"/> <pageSetUpPr/> </sheetPr> <sheetViews> <sheetView workbookViewId="0"> <selection sqref="A1" activeCell="A1"/> </sheetView> </sheetViews> <sheetFormatPr baseColWidth="8" defaultRowHeight="15"/> <sheetData /> <conditionalFormatting sqref="C1:C10"> <cfRule operator="lessThan" priority="1" stopIfTrue="1" type="cellIs"> <formula>C$1</formula> </cfRule> </conditionalFormatting> </worksheet>""" diff = compare_xml(xml, expected) assert diff is None, diff
def test_append(WriteOnlyWorksheet, _writer, doc): ws = WriteOnlyWorksheet ws.writer = _writer next(ws.writer) ws.append([1, "s"]) ws.append(['2', 3]) ws.append(i for i in [1, 2]) ws.writer.close() xml = doc.getvalue() expected = """ <sheetData> <row> <v>1</v> <v>s</v> </row> <row> <v>2</v> <v>3</v> </row> <row> <v>1</v> <v>2</v> </row> </sheetData> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_write_height(WriteOnlyWorksheet): from openpyxl.worksheet.dimensions import RowDimension ws = WriteOnlyWorksheet ws.row_dimensions[1].height = 10 ws.append([4]) ws.close() with open(ws.filename) as src: xml = src.read() expected = """ <worksheet xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <sheetPr> <outlinePr summaryRight="1" summaryBelow="1"/> <pageSetUpPr/> </sheetPr> <sheetViews> <sheetView workbookViewId="0"> <selection sqref="A1" activeCell="A1"/> </sheetView> </sheetViews> <sheetFormatPr baseColWidth="8" defaultRowHeight="15"/> <sheetData> <row customHeight="1" ht="10" r="1"> <c r="A1" t="n"> <v>4</v> </c> </row> </sheetData> </worksheet> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_data_validations(WriteOnlyWorksheet): from openpyxl25.worksheet.datavalidation import DataValidation ws = WriteOnlyWorksheet dv = DataValidation(sqref="A1") ws.data_validations.append(dv) ws.close() with open(ws.filename) as src: xml = src.read() expected = """ <worksheet xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <sheetPr> <outlinePr summaryRight="1" summaryBelow="1"/> <pageSetUpPr/> </sheetPr> <sheetViews> <sheetView workbookViewId="0"> <selection sqref="A1" activeCell="A1"/> </sheetView> </sheetViews> <sheetFormatPr baseColWidth="8" defaultRowHeight="15"/> <sheetData /> <dataValidations count="1"> <dataValidation allowBlank="0" showErrorMessage="1" showInputMessage="1" sqref="A1" /> </dataValidations> </worksheet>""" diff = compare_xml(xml, expected) assert diff is None, diff
def test_frozen_panes(WriteOnlyWorksheet): ws = WriteOnlyWorksheet ws.freeze_panes = 'D4' ws.close() with open(ws.filename) as src: xml = src.read() expected = """ <worksheet xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <sheetPr> <outlinePr summaryRight="1" summaryBelow="1"/> <pageSetUpPr/> </sheetPr> <sheetViews> <sheetView workbookViewId="0"> <pane xSplit="3" ySplit="3" topLeftCell="D4" activePane="bottomRight" state="frozen"/> <selection pane="topRight"/> <selection pane="bottomLeft"/> <selection pane="bottomRight" activeCell="A1" sqref="A1"/> </sheetView> </sheetViews> <sheetFormatPr baseColWidth="8" defaultRowHeight="15"/> <sheetData/> </worksheet> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_three_colors(self, ColorScaleRule): cfRule = ColorScaleRule(start_type='percentile', start_value=10, start_color='FFAA0000', mid_type='percentile', mid_value=50, mid_color='FF0000AA', end_type='percentile', end_value=90, end_color='FF00AA00') xml = tostring(cfRule.to_tree()) expected = """ <cfRule priority="0" type="colorScale"> <colorScale> <cfvo type="percentile" val="10"></cfvo> <cfvo type="percentile" val="50"></cfvo> <cfvo type="percentile" val="90"></cfvo> <color rgb="FFAA0000"></color> <color rgb="FF0000AA"></color> <color rgb="FF00AA00"></color> </colorScale> </cfRule> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_write(self, CustomChartsheetViews): from openpyxl25.chartsheet.custom import CustomChartsheetView pageMargins = PageMargins(left=0.2362204724409449, right=0.2362204724409449, top=0.7480314960629921, bottom=0.7480314960629921, header=0.3149606299212598, footer=0.3149606299212598) customChartsheetView = CustomChartsheetView( guid="{C43F44F8-8CE9-4A07-A9A9-0646C7C6B826}", scale=88, zoomToFit=1, pageMargins=pageMargins) customChartsheetViews = CustomChartsheetViews( customSheetView=[customChartsheetView]) expected = """ <customSheetViews> <customSheetView guid="{C43F44F8-8CE9-4A07-A9A9-0646C7C6B826}" scale="88" state="visible" zoomToFit="1"> <pageMargins left="0.2362204724409449" right="0.2362204724409449" top="0.7480314960629921" bottom="0.7480314960629921" header="0.3149606299212598" footer="0.3149606299212598" /> </customSheetView> </customSheetViews> """ xml = tostring(customChartsheetViews.to_tree()) diff = compare_xml(xml, expected) assert diff is None, diff
def test_ctor(self, SortCondition): cond = SortCondition(ref='A2:A3', descending=True) expected = """ <sortCondtion descending="1" ref="A2:A3"></sortCondtion> """ xml = tostring(cond.to_tree()) diff = compare_xml(xml, expected)
def test_simple_styles(datadir): import datetime from openpyxl25 import Workbook from openpyxl25.styles.protection import Protection from openpyxl25.styles import numbers from openpyxl25.styles.stylesheet import write_stylesheet wb = Workbook() wb.guess_types = True ws = wb.active now = datetime.date.today() for idx, v in enumerate(['12.34%', now, 'This is a test', '31.31415', None], 1): ws.append([v]) _ = ws.cell(column=1, row=idx).style_id # set explicit formats ws['D9'].number_format = numbers.FORMAT_NUMBER_00 ws['D9'].protection = Protection(locked=True) ws['D9'].style_id ws['E1'].protection = Protection(hidden=True) ws['E1'].style_id assert len(wb._cell_styles) == 5 stylesheet = write_stylesheet(wb) datadir.chdir() with open('simple-styles.xml') as reference_file: expected = reference_file.read() xml = tostring(stylesheet) diff = compare_xml(xml, expected) assert diff is None, diff
def test_conditional_formatting_customRule(self): worksheet = self.ws worksheet.conditional_formatting.add( 'C1:C10', Rule( **{ 'type': 'expression', 'formula': ['ISBLANK(C1)'], 'stopIfTrue': '1', })) cfs = write_conditional_formatting(worksheet) xml = b"" for cf in cfs: xml += tostring(cf) diff = compare_xml( xml, """ <conditionalFormatting sqref="C1:C10"> <cfRule type="expression" stopIfTrue="1" priority="1"> <formula>ISBLANK(C1)</formula> </cfRule> </conditionalFormatting> """) assert diff is None, diff
def test_empty_dv(self, DataValidationList, DataValidation): dv = DataValidation() dvs = DataValidationList(dataValidation=[dv]) xml = tostring(dvs.to_tree()) expected = '<dataValidations count="0"/>' diff = compare_xml(xml, expected) assert diff is None, diff
def test_title_maker(): """ Create a title element from a string preserving line breaks. """ from openpyxl25.chart.title import title_maker text = "Two-line\nText" title = title_maker(text) xml = tostring(title.to_tree()) expected = """ <title xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"> <tx> <rich> <a:bodyPr /> <a:p> <a:r> <a:t>Two-line</a:t> </a:r> </a:p> <a:p> <a:r> <a:t>Text</a:t> </a:r> </a:p> </rich> </tx> </title> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_ctor(self, ManualLayout): layout = ManualLayout(layoutTarget="inner", xMode="edge", yMode="factor", wMode="factor", hMode="edge", x=10, y=50, w=4, h=100) xml = tostring(layout.to_tree()) expected = """ <manualLayout> <layoutTarget val="inner"></layoutTarget> <xMode val="edge"></xMode> <yMode val="factor"></yMode> <wMode val="factor"></wMode> <hMode val="edge"></hMode> <x val="10"></x> <y val="50"></y> <w val="4"></w> <h val="100"></h> </manualLayout> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_conditional_font(worksheet_with_cf, write_conditional_formatting): """Test to verify font style written correctly.""" # Create cf rule from openpyxl25.styles import PatternFill, Font, Color from openpyxl25.formatting.rule import CellIsRule redFill = PatternFill(start_color=Color('FFEE1111'), end_color=Color('FFEE1111'), patternType='solid') whiteFont = Font(color=Color("FFFFFFFF")) ws = worksheet_with_cf ws.conditional_formatting.add( 'A1:A3', CellIsRule(operator='equal', formula=['"Fail"'], stopIfTrue=False, font=whiteFont, fill=redFill)) cfs = write_conditional_formatting(ws) xml = b"" for cf in cfs: xml += tostring(cf) diff = compare_xml( xml, """ <conditionalFormatting sqref="A1:A3"> <cfRule operator="equal" priority="1" type="cellIs" dxfId="0" stopIfTrue="0"> <formula>"Fail"</formula> </cfRule> </conditionalFormatting> """) assert diff is None, diff
def test_print_titles(Unicode_Workbook): wb = Unicode_Workbook ws = wb.active ws.print_title_rows = '1:5' xml = write_workbook(wb) expected = """ <workbook xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <workbookPr/> <workbookProtection/> <bookViews> <workbookView activeTab="0"/> </bookViews> <sheets> <sheet name="Düsseldorf Sheet" sheetId="1" state="visible" r:id="rId1"/> </sheets> <definedNames> <definedName localSheetId="0" name="_xlnm.Print_Titles">'Düsseldorf Sheet'!1:5</definedName> </definedNames> <calcPr calcId="124519" fullCalcOnLoad="1"/> </workbook> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_shape(): from openpyxl25.comments.shape_writer import _shape_factory shape = _shape_factory(2,3) xml = tostring(shape) expected = """ <v:shape xmlns:v="urn:schemas-microsoft-com:vml" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office" fillcolor="#ffffe1" style="position:absolute; margin-left:59.25pt;margin-top:1.5pt;width:108pt;height:59.25pt;z-index:1;visibility:hidden" type="#_x0000_t202" o:insetmode="auto"> <v:fill color2="#ffffe1"/> <v:shadow color="black" obscured="t"/> <v:path o:connecttype="none"/> <v:textbox style="mso-direction-alt:auto"> <div style="text-align:left"/> </v:textbox> <x:ClientData ObjectType="Note"> <x:MoveWithCells/> <x:SizeWithCells/> <x:AutoFill>False</x:AutoFill> <x:Row>2</x:Row> <x:Column>3</x:Column> </x:ClientData> </v:shape> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_ctor(SampleProperties): expected = """ <coreProperties xmlns="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <dc:creator>TEST_USER</dc:creator> <dc:title>The title</dc:title> <dc:description>The description</dc:description> <dc:subject>The subject</dc:subject> <dc:identifier>The identifier</dc:identifier> <dc:language>The language</dc:language> <dcterms:created xsi:type="dcterms:W3CDTF">2010-04-01T20:30:00Z</dcterms:created> <dcterms:modified xsi:type="dcterms:W3CDTF">2010-04-05T14:05:30Z</dcterms:modified> <lastModifiedBy>SOMEBODY</lastModifiedBy> <category>The category</category> <contentStatus>The status</contentStatus> <version>2.5</version> <revision>0</revision> <keywords>one, two, three</keywords> <lastPrinted>2014-10-14T10:30:00Z</lastPrinted> </coreProperties> """ xml = tostring(SampleProperties.to_tree()) diff = compare_xml(xml, expected) assert diff is None, diff
def test_write(self, WebPublishItems): from openpyxl25.chartsheet.publish import WebPublishItem webPublish_6433 = WebPublishItem(id=6433, divId="Views_6433", sourceType="chart", sourceRef="", sourceObject="Chart 1", destinationFile="D:\Publish.mht", title="First Chart", autoRepublish=False) webPublish_64487 = WebPublishItem(id=64487, divId="Views_64487", sourceType="chart", sourceRef="Ref_545421", sourceObject="Chart 15", destinationFile="D:\Publish_12.mht", title="Second Chart", autoRepublish=True) webPublishItems = WebPublishItems( webPublishItem=[webPublish_6433, webPublish_64487]) expected = """ <WebPublishItems count="2"> <webPublishItem id="6433" divId="Views_6433" sourceType="chart" sourceRef="" sourceObject="Chart 1" destinationFile="D:\Publish.mht" title="First Chart" autoRepublish="0"/> <webPublishItem id="64487" divId="Views_64487" sourceType="chart" sourceRef="Ref_545421" sourceObject="Chart 15" destinationFile="D:\Publish_12.mht" title="Second Chart" autoRepublish="1"/> </WebPublishItems> """ xml = tostring(webPublishItems.to_tree()) diff = compare_xml(xml, expected) assert diff is None, diff
def test_ctor(self, Number): number = Number(v=24) xml = tostring(number.to_tree()) expected = """ <n v="24"/> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_ctor(self, Missing): missing = Missing() xml = tostring(missing.to_tree()) expected = """ <m /> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_ctor(self, Error): error = Error(v="error") xml = tostring(error.to_tree()) expected = """ <e v="error" /> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_ctor(self, Boolean): boolean = Boolean() xml = tostring(boolean.to_tree()) expected = """ <b v="0" /> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_ctor(self, DateTimeField): record = DateTimeField(v=datetime(2016, 3, 24)) xml = tostring(record.to_tree()) expected = """ <d v="2016-03-24T00:00:00"/> """ diff = compare_xml(xml, expected) assert diff is None, diff