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
