def test_header_footer(self):
ws = Worksheet(self.wb)
ws.header_footer.left_header.text = "Left Header Text"
ws.header_footer.center_header.text = "Center Header Text"
ws.header_footer.center_header.font_name = "Arial,Regular"
ws.header_footer.center_header.font_size = 6
ws.header_footer.center_header.font_color = "445566"
ws.header_footer.right_header.text = "Right Header Text"
ws.header_footer.right_header.font_name = "Arial,Bold"
ws.header_footer.right_header.font_size = 8
ws.header_footer.right_header.font_color = "112233"
ws.header_footer.left_footer.text = "Left Footer Text\nAnd &[Date] and &[Time]"
ws.header_footer.left_footer.font_name = "Times New Roman,Regular"
ws.header_footer.left_footer.font_size = 10
ws.header_footer.left_footer.font_color = "445566"
ws.header_footer.center_footer.text = "Center Footer Text &[Path]&[File] on &[Tab]"
ws.header_footer.center_footer.font_name = "Times New Roman,Bold"
ws.header_footer.center_footer.font_size = 12
ws.header_footer.center_footer.font_color = "778899"
ws.header_footer.right_footer.text = "Right Footer Text &[Page] of &[Pages]"
ws.header_footer.right_footer.font_name = "Times New Roman,Italic"
ws.header_footer.right_footer.font_size = 14
ws.header_footer.right_footer.font_color = "AABBCC"
xml_string = write_worksheet(ws, None, None)
assert '<headerFooter>' in xml_string
assert '<oddHeader>&L&"Calibri,Regular"&K000000Left Header Text&C&"Arial,Regular"&6&K445566Center Header Text&R&"Arial,Bold"&8&K112233Right Header Text</oddHeader>' in xml_string
assert '<oddFooter>&L&"Times New Roman,Regular"&10&K445566Left Footer Text_x000D_And &D and &T&C&"Times New Roman,Bold"&12&K778899Center Footer Text &Z&F on &A&R&"Times New Roman,Italic"&14&KAABBCCRight Footer Text &P of &N</oddFooter></headerFooter>' in xml_string
assert '</headerFooter>' in xml_string
ws = Worksheet(self.wb)
xml_string = write_worksheet(ws, None, None)
assert "<headerFooter>" not in xml_string
assert "<oddHeader>" not in xml_string
assert "<oddFooter>" not in xml_string
def test_header_footer(self):
ws = Worksheet(self.wb)
ws.header_footer.left_header.text = "Left Header Text"
ws.header_footer.center_header.text = "Center Header Text"
ws.header_footer.center_header.font_name = "Arial,Regular"
ws.header_footer.center_header.font_size = 6
ws.header_footer.center_header.font_color = "445566"
ws.header_footer.right_header.text = "Right Header Text"
ws.header_footer.right_header.font_name = "Arial,Bold"
ws.header_footer.right_header.font_size = 8
ws.header_footer.right_header.font_color = "112233"
ws.header_footer.left_footer.text = "Left Footer Text\nAnd &[Date] and &[Time]"
ws.header_footer.left_footer.font_name = "Times New Roman,Regular"
ws.header_footer.left_footer.font_size = 10
ws.header_footer.left_footer.font_color = "445566"
ws.header_footer.center_footer.text = "Center Footer Text &[Path]&[File] on &[Tab]"
ws.header_footer.center_footer.font_name = "Times New Roman,Bold"
ws.header_footer.center_footer.font_size = 12
ws.header_footer.center_footer.font_color = "778899"
ws.header_footer.right_footer.text = "Right Footer Text &[Page] of &[Pages]"
ws.header_footer.right_footer.font_name = "Times New Roman,Italic"
ws.header_footer.right_footer.font_size = 14
ws.header_footer.right_footer.font_color = "AABBCC"
xml_string = write_worksheet(ws, None, None)
assert '<headerFooter>' in xml_string
assert '<oddHeader>&L&"Calibri,Regular"&K000000Left Header Text&C&"Arial,Regular"&6&K445566Center Header Text&R&"Arial,Bold"&8&K112233Right Header Text</oddHeader>' in xml_string
assert '<oddFooter>&L&"Times New Roman,Regular"&10&K445566Left Footer Text_x000D_And &D and &T&C&"Times New Roman,Bold"&12&K778899Center Footer Text &Z&F on &A&R&"Times New Roman,Italic"&14&KAABBCCRight Footer Text &P of &N</oddFooter></headerFooter>' in xml_string
assert '</headerFooter>' in xml_string
ws = Worksheet(self.wb)
xml_string = write_worksheet(ws, None, None)
assert "<headerFooter>" not in xml_string
assert "<oddHeader>" not in xml_string
assert "<oddFooter>" not in xml_string
def test_printer_settings(self):
ws = Worksheet(self.wb)
ws.set_printer_settings(Worksheet.PAPER_SIZE_LEGAL, Worksheet.ORIENTATION_LANDSCAPE)
xml_string = write_worksheet(ws, None, None)
assert '<pageSetup paperSize="5" orientation="landscape"></pageSetup>' in xml_string
ws = Worksheet(self.wb)
xml_string = write_worksheet(ws, None, None)
assert "<pageSetup paperSize" not in xml_string
def test_printer_settings(self):
ws = Worksheet(self.wb)
ws.set_printer_settings(Worksheet.PAPER_SIZE_LEGAL,
Worksheet.ORIENTATION_LANDSCAPE)
xml_string = write_worksheet(ws, None, None)
assert '<pageSetup paperSize="5" orientation="landscape"></pageSetup>' in xml_string
ws = Worksheet(self.wb)
xml_string = write_worksheet(ws, None, None)
assert "<pageSetup paperSize" not in xml_string
def test_page_margins(self):
ws = Worksheet(self.wb)
ws.page_margins.left = 2.0
ws.page_margins.right = 2.0
ws.page_margins.top = 2.0
ws.page_margins.bottom = 2.0
ws.page_margins.header = 1.5
ws.page_margins.footer = 1.5
xml_string = write_worksheet(ws, None, None)
assert '<pageMargins left="2.00" right="2.00" top="2.00" bottom="2.00" header="1.50" footer="1.50"></pageMargins>' in xml_string
ws = Worksheet(self.wb)
xml_string = write_worksheet(ws, None, None)
assert '<pageMargins' not in xml_string
def test_page_margins(self):
ws = Worksheet(self.wb)
ws.page_margins.left = 2.0
ws.page_margins.right = 2.0
ws.page_margins.top = 2.0
ws.page_margins.bottom = 2.0
ws.page_margins.header = 1.5
ws.page_margins.footer = 1.5
xml_string = write_worksheet(ws, None, None)
assert '<pageMargins left="2.00" right="2.00" top="2.00" bottom="2.00" header="1.50" footer="1.50"></pageMargins>' in xml_string
ws = Worksheet(self.wb)
xml_string = write_worksheet(ws, None, None)
assert '<pageMargins' not in xml_string
def test_printer_settings(self):
ws = Worksheet(self.wb)
ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE
ws.page_setup.paperSize = ws.PAPERSIZE_TABLOID
ws.page_setup.fitToPage = True
ws.page_setup.fitToHeight = 0
ws.page_setup.fitToWidth = 1
xml_string = write_worksheet(ws, None, None)
assert '<pageSetup orientation="landscape" paperSize="3" fitToHeight="0" fitToWidth="1"></pageSetup>' in xml_string
assert '<pageSetUpPr fitToPage="1"></pageSetUpPr>' in xml_string
ws = Worksheet(self.wb)
xml_string = write_worksheet(ws, None, None)
assert "<pageSetup" not in xml_string
assert "<pageSetUpPr" not in xml_string
def test_printer_settings(self):
ws = Worksheet(self.wb)
ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE
ws.page_setup.paperSize = ws.PAPERSIZE_TABLOID
ws.page_setup.fitToPage = True
ws.page_setup.fitToHeight = 0
ws.page_setup.fitToWidth = 1
xml_string = write_worksheet(ws, None, None)
assert '<pageSetup orientation="landscape" paperSize="3" fitToHeight="0" fitToWidth="1"></pageSetup>' in xml_string
assert '<pageSetUpPr fitToPage="1"></pageSetUpPr>' in xml_string
ws = Worksheet(self.wb)
xml_string = write_worksheet(ws, None, None)
assert "<pageSetup" not in xml_string
assert "<pageSetUpPr" not in xml_string
def test_printer_settings(self):
ws = Worksheet(self.wb)
ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE
ws.page_setup.paperSize = ws.PAPERSIZE_TABLOID
ws.page_setup.fitToPage = True
ws.page_setup.fitToHeight = 0
ws.page_setup.fitToWidth = 1
ws.page_setup.horizontalCentered = True
ws.page_setup.verticalCentered = True
xml = write_worksheet(ws, None, None)
expected = """
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<sheetPr>
<outlinePr summaryRight="1" summaryBelow="1"/>
<pageSetUpPr fitToPage="1"/>
</sheetPr>
<dimension ref="A1:A1"/>
<sheetViews>
<sheetView workbookViewId="0">
<selection sqref="A1" activeCell="A1"/>
</sheetView>
</sheetViews>
<sheetFormatPr defaultRowHeight="15"/>
<sheetData/>
<printOptions horizontalCentered="1" verticalCentered="1"/>
<pageSetup orientation="landscape" paperSize="3" fitToHeight="0" fitToWidth="1"/>
</worksheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_page_margins(self):
ws = Worksheet(self.wb)
ws.page_margins.left = 2.0
ws.page_margins.right = 2.0
ws.page_margins.top = 2.0
ws.page_margins.bottom = 2.0
ws.page_margins.header = 1.5
ws.page_margins.footer = 1.5
xml = write_worksheet(ws, None, None)
expected = """
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<sheetPr>
<outlinePr summaryRight="1" summaryBelow="1"/>
</sheetPr>
<dimension ref="A1:A1"/>
<sheetViews>
<sheetView workbookViewId="0">
<selection sqref="A1" activeCell="A1"/>
</sheetView>
</sheetViews>
<sheetFormatPr defaultRowHeight="15"/>
<sheetData/>
<pageMargins left="2.00" right="2.00" top="2.00" bottom="2.00" header="1.50" footer="1.50"/>
</worksheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_printer_settings(self):
ws = Worksheet(self.wb)
ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE
ws.page_setup.paperSize = ws.PAPERSIZE_TABLOID
ws.page_setup.fitToPage = True
ws.page_setup.fitToHeight = 0
ws.page_setup.fitToWidth = 1
ws.page_setup.horizontalCentered = True
ws.page_setup.verticalCentered = True
xml = write_worksheet(ws, None, None)
expected = """
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<sheetPr>
<outlinePr summaryRight="1" summaryBelow="1"/>
<pageSetUpPr fitToPage="1"/>
</sheetPr>
<dimension ref="A1:A1"/>
<sheetViews>
<sheetView workbookViewId="0">
<selection sqref="A1" activeCell="A1"/>
</sheetView>
</sheetViews>
<sheetFormatPr baseColWidth="10" defaultRowHeight="15"/>
<sheetData/>
<printOptions horizontalCentered="1" verticalCentered="1"/>
<pageMargins left="0.75" right="0.75" top="1" bottom="1" header="0.5" footer="0.5"/>
<pageSetup orientation="landscape" paperSize="3" fitToHeight="0" fitToWidth="1"/>
</worksheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_page_margins(self):
ws = Worksheet(self.wb)
ws.page_margins.left = 2.0
ws.page_margins.right = 2.0
ws.page_margins.top = 2.0
ws.page_margins.bottom = 2.0
ws.page_margins.header = 1.5
ws.page_margins.footer = 1.5
xml = write_worksheet(ws, None, None)
expected = """
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<sheetPr>
<outlinePr summaryRight="1" summaryBelow="1"/>
</sheetPr>
<dimension ref="A1:A1"/>
<sheetViews>
<sheetView workbookViewId="0">
<selection sqref="A1" activeCell="A1"/>
</sheetView>
</sheetViews>
<sheetFormatPr baseColWidth="10" defaultRowHeight="15"/>
<sheetData/>
<pageMargins left="2" right="2" top="2" bottom="2" header="1.5" footer="1.5"/>
</worksheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_conditional_formatting_add3ColorScale(self):
self.worksheet.conditional_formatting.add3ColorScale('B1:B10', 'percentile', 10, 'FFAA0000', 'percentile', 50,
'FF0000AA', 'percentile', 90, 'FF00AA00')
xml = write_worksheet(self.worksheet, None, None)
expected = '<worksheet xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xml:space="preserve" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><sheetPr><outlinePr summaryRight="1" summaryBelow="1"></outlinePr></sheetPr><dimension ref="A1:A1"></dimension><sheetViews><sheetView workbookViewId="0"><selection sqref="A1" activeCell="A1"></selection></sheetView></sheetViews><sheetFormatPr defaultRowHeight="15"></sheetFormatPr><sheetData></sheetData><conditionalFormatting sqref="B1:B10"><cfRule type="colorScale" priority="1"><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></conditionalFormatting></worksheet>'
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_short_number():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('A1').value = 1234567890
content = write_worksheet(ws, {}, {})
assert_equals_file_content(os.path.join(DATADIR, 'writer', 'expected', \
'short_number.xml'), content)
def test_write_worksheet():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('F42').value = 'hello'
content = write_worksheet(ws, {'hello': 0}, {})
assert_equals_file_content(os.path.join(DATADIR, 'writer', 'expected', \
'sheet1.xml'), content)
def test_write_worksheet():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('F42').value = 'hello'
content = write_worksheet(ws, {'hello': 0}, {})
assert_equals_file_content(os.path.join(DATADIR, 'writer', 'expected', \
'sheet1.xml'), content)
def test_write_height():
wb = Workbook()
ws = wb.create_sheet()
ws.cell("F1").value = 10
ws.row_dimensions[ws.cell("F1").row].height = 30
content = write_worksheet(ws, {}, {})
assert_equals_file_content(os.path.join(DATADIR, "writer", "expected", "sheet1_height.xml"), content)
def test_write_hyperlink():
wb = Workbook()
ws = wb.create_sheet()
ws.cell("A1").value = "test"
ws.cell("A1").hyperlink = "http://test.com"
content = write_worksheet(ws, {"test": 0}, {})
assert_equals_file_content(os.path.join(DATADIR, "writer", "expected", "sheet1_hyperlink.xml"), content)
def test_write_style():
wb = Workbook()
ws = wb.create_sheet()
ws.cell("F1").value = "13%"
style_id_by_hash = StyleWriter(wb).get_style_by_hash()
content = write_worksheet(ws, {}, style_id_by_hash)
assert_equals_file_content(os.path.join(DATADIR, "writer", "expected", "sheet1_style.xml"), content)
def test_decimal():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('A1').value = decimal.Decimal('3.14')
content = write_worksheet(ws, {}, {})
assert_equals_file_content(os.path.join(DATADIR, 'writer', 'expected', \
'decimal.xml'), content)
def test_freeze_panes_both():
wb = Workbook()
ws = wb.create_sheet()
ws.cell("F42").value = "hello"
ws.freeze_panes = "D4"
content = write_worksheet(ws, {"hello": 0}, {})
assert_equals_file_content(os.path.join(DATADIR, "writer", "expected", "sheet1_freeze_panes_both.xml"), content)
def _write_worksheets(self, archive, style_writer):
drawing_id = 1
chart_id = 1
image_id = 1
shape_id = 1
comments_id = 1
for i, sheet in enumerate(self.workbook.worksheets):
archive.writestr(
PACKAGE_WORKSHEETS + '/sheet%d.xml' % (i + 1),
write_worksheet(sheet, self.shared_strings,
style_writer.styles))
if (sheet._charts or sheet._images or sheet.relationships
or sheet._comment_count > 0):
archive.writestr(
PACKAGE_WORKSHEETS + '/_rels/sheet%d.xml.rels' % (i + 1),
write_worksheet_rels(sheet, drawing_id, comments_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)) # TODO remove this dependency
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)) # TODO remove this dependency
sw = ShapeWriter(chart._shapes)
archive.writestr(
PACKAGE_DRAWINGS + '/drawing%d.xml' % drawing_id,
sw.write(shape_id)) # TODO remove this dependency
shape_id += len(chart._shapes)
drawing_id += 1
chart_id += 1
image_id = self._write_images(sheet._images, archive, image_id)
if sheet._comment_count > 0:
cw = CommentWriter(sheet)
archive.writestr(PACKAGE_XL + '/comments%d.xml' % comments_id,
cw.write_comments())
archive.writestr(
PACKAGE_XL +
'/drawings/commentsDrawing%d.vml' % comments_id,
cw.write_comments_vml())
comments_id += 1
def _write(self):
from openpyxl.drawing.spreadsheet_drawing import SpreadsheetDrawing
from openpyxl.writer.worksheet import write_worksheet
self._drawing = SpreadsheetDrawing()
self._drawing.charts = self._charts
self._drawing.images = self._images
return write_worksheet(self)
def test_short_number():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('A1').value = 1234567890
content = write_worksheet(ws, {}, {})
assert_equals_file_content(os.path.join(DATADIR, 'writer', 'expected', \
'short_number.xml'), content)
def test_decimal():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('A1').value = decimal.Decimal('3.14')
content = write_worksheet(ws, {}, {})
assert_equals_file_content(os.path.join(DATADIR, 'writer', 'expected', \
'decimal.xml'), content)
def test_write_hidden_worksheet():
wb = Workbook()
ws = wb.create_sheet()
ws.sheet_state = ws.SHEETSTATE_HIDDEN
ws.cell("F42").value = "hello"
content = write_worksheet(ws, {"hello": 0}, {})
assert_equals_file_content(os.path.join(DATADIR, "writer", "expected", "sheet1.xml"), content)
def test_write_bool():
wb = Workbook()
ws = wb.create_sheet()
ws.cell("F42").value = False
ws.cell("F43").value = True
content = write_worksheet(ws, {}, {})
assert_equals_file_content(os.path.join(DATADIR, "writer", "expected", "sheet1_bool.xml"), content)
def _write(self):
from openpyxl.drawing.spreadsheet_drawing import SpreadsheetDrawing
from openpyxl.writer.worksheet import write_worksheet
self._drawing = SpreadsheetDrawing()
self._drawing.charts = self._charts
self._drawing.images = self._images
return write_worksheet(self)
def test_write_bool():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('F42').value = False
ws.cell('F43').value = True
content = write_worksheet(ws, {}, {})
assert_equals_file_content(os.path.join(DATADIR, 'writer', 'expected', \
'sheet1_bool.xml'), content)
def test_merge(self):
ws = Worksheet(self.wb)
string_table = {'':'', 'Cell A1':'Cell A1','Cell B1':'Cell B1'}
ws.cell('A1').value = 'Cell A1'
ws.cell('B1').value = 'Cell B1'
xml_string = write_worksheet(ws, string_table, None)
assert '<c r="B1" t="s"><v>Cell B1</v></c>' in xml_string
ws.merge_cells('A1:B1')
xml_string = write_worksheet(ws, string_table, None)
assert '<c r="B1" t="s"><v>Cell B1</v></c>' not in xml_string
assert '<mergeCells><mergeCell ref="A1:B1"></mergeCell></mergeCells>' in xml_string
ws.unmerge_cells('A1:B1')
xml_string = write_worksheet(ws, string_table, None)
assert '<mergeCell ref="A1:B1"></mergeCell>' not in xml_string
def test_write_style():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('F1').value = '13%'
style_id_by_hash = StyleWriter(wb).get_style_by_hash()
content = write_worksheet(ws, {}, style_id_by_hash)
assert_equals_file_content(os.path.join(DATADIR, 'writer', 'expected', \
'sheet1_style.xml'), content)
def test_write_hyperlink():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('A1').value = "test"
ws.cell('A1').hyperlink = "http://test.com"
content = write_worksheet(ws, {'test': 0}, {})
assert_equals_file_content(os.path.join(DATADIR, 'writer', 'expected', \
'sheet1_hyperlink.xml'), content)
def test_write_height():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('F1').value = 10
ws.row_dimensions[ws.cell('F1').row].height = 30
content = write_worksheet(ws, {}, {})
assert_equals_file_content(os.path.join(DATADIR, 'writer', 'expected', \
'sheet1_height.xml'), content)
def test_conditional_formatting_addCustomRule(self):
dxfId = self.worksheet.conditional_formatting.addDxfStyle(self.workbook, None, None, None)
self.worksheet.conditional_formatting.addCustomRule('C1:C10', {'type': 'expression', 'dxfId': dxfId, 'formula': ['ISBLANK(C1)'], 'stopIfTrue': '1'})
xml = write_worksheet(self.worksheet, None, None)
assert dxfId == 0
expected = '<worksheet xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xml:space="preserve" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><sheetPr><outlinePr summaryRight="1" summaryBelow="1"></outlinePr></sheetPr><dimension ref="A1:A1"></dimension><sheetViews><sheetView workbookViewId="0"><selection sqref="A1" activeCell="A1"></selection></sheetView></sheetViews><sheetFormatPr defaultRowHeight="15"></sheetFormatPr><sheetData></sheetData><conditionalFormatting sqref="C1:C10"><cfRule dxfId="0" type="expression" stopIfTrue="1" priority="1"><formula>ISBLANK(C1)</formula></cfRule></conditionalFormatting></worksheet>'
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write_style():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('F1').value = '13%'
style_id_by_hash = StyleWriter(wb).get_style_by_hash()
content = write_worksheet(ws, {}, style_id_by_hash)
assert_equals_file_content(os.path.join(DATADIR, 'writer', 'expected', \
'sheet1_style.xml'), content)
def test_merge(self):
ws = Worksheet(self.wb)
string_table = {'': '', 'Cell A1': 'Cell A1', 'Cell B1': 'Cell B1'}
ws.cell('A1').value = 'Cell A1'
ws.cell('B1').value = 'Cell B1'
xml_string = write_worksheet(ws, string_table, None)
assert '<v>Cell B1</v>' in xml_string
ws.merge_cells('A1:B1')
xml_string = write_worksheet(ws, string_table, None)
assert '<v>Cell B1</v>' not in xml_string
assert '<mergeCells count="1"><mergeCell ref="A1:B1"></mergeCell></mergeCells>' in xml_string
ws.unmerge_cells('A1:B1')
xml_string = write_worksheet(ws, string_table, None)
assert '<mergeCell ref="A1:B1"/>' not in xml_string
def test_write_hyperlink():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('A1').value = "test"
ws.cell('A1').hyperlink = "http://test.com"
content = write_worksheet(ws, {'test': 0}, {})
assert_equals_file_content(os.path.join(DATADIR, 'writer', 'expected', \
'sheet1_hyperlink.xml'), content)
def test_write_bool():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('F42').value = False
ws.cell('F43').value = True
content = write_worksheet(ws, {}, {})
assert_equals_file_content(os.path.join(DATADIR, 'writer', 'expected', \
'sheet1_bool.xml'), content)
def test_write_formula():
wb = Workbook()
ws = wb.create_sheet()
ws.cell("F1").value = 10
ws.cell("F2").value = 32
ws.cell("F3").value = "=F1+F2"
content = write_worksheet(ws, {}, {})
assert_equals_file_content(os.path.join(DATADIR, "writer", "expected", "sheet1_formula.xml"), content)
def test_decimal():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('A1').value = decimal.Decimal('3.14')
content = write_worksheet(ws, {}, {})
reference_file = os.path.join(DATADIR, 'writer', 'expected', 'decimal.xml')
with open(reference_file) as expected:
diff = compare_xml(content, expected.read())
assert diff is None, diff
def test_write_formula():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('F1').value = 10
ws.cell('F2').value = 32
ws.cell('F3').value = '=F1+F2'
content = write_worksheet(ws, {}, {})
assert_equals_file_content(os.path.join(DATADIR, 'writer', 'expected', \
'sheet1_formula.xml'), content)
def test_short_number():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('A1').value = 1234567890
content = write_worksheet(ws, {}, {})
reference_file = os.path.join(DATADIR, 'writer', 'expected', 'short_number.xml')
with open(reference_file) as expected:
diff = compare_xml(content, expected.read())
assert diff is None, diff
def test_write_worksheet():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('F42').value = 'hello'
content = write_worksheet(ws, {'hello': 0}, {})
reference_file = os.path.join(DATADIR, 'writer', 'expected', 'sheet1.xml')
with open(reference_file) as expected:
diff = compare_xml(content, expected.read())
assert diff is None, diff
def test_short_number():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('A1').value = 1234567890
content = write_worksheet(ws, {}, {})
reference_file = os.path.join(DATADIR, 'writer', 'expected', 'short_number.xml')
with open(reference_file) as expected:
diff = compare_xml(content, expected.read())
assert diff is None, diff
def test_decimal():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('A1').value = decimal.Decimal('3.14')
content = write_worksheet(ws, {}, {})
reference_file = os.path.join(DATADIR, 'writer', 'expected', 'decimal.xml')
with open(reference_file) as expected:
diff = compare_xml(content, expected.read())
assert diff is None, diff
def test_write_worksheet():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('F42').value = 'hello'
content = write_worksheet(ws, {'hello': 0}, {})
reference_file = os.path.join(DATADIR, 'writer', 'expected', 'sheet1.xml')
with open(reference_file) as expected:
diff = compare_xml(content, expected.read())
assert diff is None, diff
def _write_worksheets(self, archive):
drawing_id = 1
chart_id = 1
image_id = 1
shape_id = 1
comments_id = 1
vba_controls_id = 1
for i, sheet in enumerate(self.workbook.worksheets):
archive.writestr(PACKAGE_WORKSHEETS + '/sheet%d.xml' % (i + 1),
write_worksheet(sheet, self.workbook.shared_strings,
))
if (sheet._charts or sheet._images
or sheet.relationships
or sheet._comment_count > 0
or sheet.vba_controls is not None):
rels = write_rels(sheet, drawing_id, comments_id, vba_controls_id)
archive.writestr(
PACKAGE_WORKSHEETS + '/_rels/sheet%d.xml.rels' % (i + 1),
tostring(rels)
)
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)) # TODO remove this dependency
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)) # TODO remove this dependency
sw = ShapeWriter(chart._shapes)
archive.writestr(PACKAGE_DRAWINGS + '/drawing%d.xml' % drawing_id,
sw.write(shape_id)) # TODO remove this dependency
shape_id += len(chart._shapes)
drawing_id += 1
chart_id += 1
image_id = self._write_images(sheet._images, archive, image_id)
if sheet._comment_count > 0:
cw = CommentWriter(sheet)
archive.writestr(PACKAGE_XL + '/comments%d.xml' % comments_id,
cw.write_comments())
archive.writestr(PACKAGE_XL + '/drawings/commentsDrawing%d.vml' % comments_id,
cw.write_comments_vml())
comments_id += 1
if sheet.vba_controls is not None:
vba_controls_id += 1
def test_write_formula():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('F1').value = 10
ws.cell('F2').value = 32
ws.cell('F3').value = '=F1+F2'
content = write_worksheet(ws, {}, {})
assert_equals_file_content(os.path.join(DATADIR, 'writer', 'expected', \
'sheet1_formula.xml'), content)
def test_conditional_formatting_setRules(self):
rules = {'A1:A4': [{'type': 'colorScale', 'priority': '13',
'colorScale': {'cfvo': [{'type': 'min'}, {'type': 'max'}],
'color': [Color('FFFF7128'), Color('FFFFEF9C')]}}]}
self.worksheet.conditional_formatting.setRules(rules)
xml = write_worksheet(self.worksheet, None, None)
expected = '<worksheet xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xml:space="preserve" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><sheetPr><outlinePr summaryRight="1" summaryBelow="1"></outlinePr></sheetPr><dimension ref="A1:A1"></dimension><sheetViews><sheetView workbookViewId="0"><selection sqref="A1" activeCell="A1"></selection></sheetView></sheetViews><sheetFormatPr defaultRowHeight="15"></sheetFormatPr><sheetData></sheetData><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></worksheet>'
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write_height():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('F1').value = 10
ws.row_dimensions[ws.cell('F1').row].height = 30
content = write_worksheet(ws, {}, {})
reference_file = os.path.join(DATADIR, 'writer', 'expected', 'sheet1_height.xml')
with open(reference_file) as expected:
diff = compare_xml(content, expected.read())
assert diff is None, diff
def test_write_auto_filter():
wb = Workbook()
ws = wb.worksheets[0]
ws.cell("F42").value = "hello"
ws.auto_filter = "A1:F1"
content = write_worksheet(ws, {"hello": 0}, {})
assert_equals_file_content(os.path.join(DATADIR, "writer", "expected", "sheet1_auto_filter.xml"), content)
content = write_workbook(wb)
assert_equals_file_content(os.path.join(DATADIR, "writer", "expected", "workbook_auto_filter.xml"), content)
def test_write_hyperlink():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('A1').value = "test"
ws.cell('A1').hyperlink = "http://test.com"
content = write_worksheet(ws, {'test': 0}, {})
reference_file = os.path.join(DATADIR, 'writer', 'expected', 'sheet1_hyperlink.xml')
with open(reference_file) as expected:
diff = compare_xml(content, expected.read())
assert diff is None, diff
def test_freeze_panes_both():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('F42').value = 'hello'
ws.freeze_panes = 'D4'
strings = create_string_table(wb)
content = write_worksheet(ws, strings, {})
reference_file = os.path.join(DATADIR, 'writer', 'expected', 'sheet1_freeze_panes_both.xml')
with open(reference_file) as expected:
diff = compare_xml(content, expected.read())
assert diff is None, diff
def test_write_hidden_worksheet():
wb = Workbook()
ws = wb.create_sheet()
ws.sheet_state = ws.SHEETSTATE_HIDDEN
ws.cell('F42').value = 'hello'
strings = create_string_table(wb)
content = write_worksheet(ws, strings, {})
reference_file = os.path.join(DATADIR, 'writer', 'expected', 'sheet1.xml')
with open(reference_file) as expected:
diff = compare_xml(content, expected.read())
assert diff is None, diff
def test_write_style():
wb = Workbook(guess_types=True)
ws = wb.create_sheet()
ws.cell('F1').value = '13%'
ws._styles['F'] = ws._styles['F1']
style_id_by_hash = StyleWriter(wb).get_style_by_hash()
content = write_worksheet(ws, {}, style_id_by_hash)
reference_file = os.path.join(DATADIR, 'writer', 'expected', 'sheet1_style.xml')
with open(reference_file) as expected:
diff = compare_xml(content, expected.read())
assert diff is None, diff
def test_write_auto_filter():
wb = Workbook()
ws = wb.worksheets[0]
ws.cell('F42').value = 'hello'
ws.auto_filter = 'A1:F1'
content = write_worksheet(ws, {'hello': 0}, {})
assert_equals_file_content(os.path.join(DATADIR, 'writer', 'expected', \
'sheet1_auto_filter.xml'), content)
content = write_workbook(wb)
assert_equals_file_content(os.path.join(DATADIR, 'writer', 'expected', \
'workbook_auto_filter.xml'), content)
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_write_auto_filter():
wb = Workbook()
ws = wb.worksheets[0]
ws.cell('F42').value = 'hello'
ws.auto_filter.ref = 'A1:F1'
content = write_worksheet(ws, {'hello': 0}, {})
reference_file = os.path.join(DATADIR, 'writer', 'expected', 'sheet1_auto_filter.xml')
with open(reference_file) as expected:
diff = compare_xml(content, expected.read())
assert diff is None
content = write_workbook(wb)
reference_file = os.path.join(DATADIR, 'writer', 'expected', 'workbook_auto_filter.xml')
with open(reference_file) as expected:
diff = compare_xml(content, expected.read())
assert diff is None, diff
def test_write_formula():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('F1').value = 10
ws.cell('F2').value = 32
ws.cell('F3').value = '=F1+F2'
ws.cell('A4').value = '=A1+A2+A3'
ws.formula_attributes['A4'] = {'t': 'shared', 'ref': 'A4:C4', 'si': '0'}
ws.cell('B4').value = '='
ws.formula_attributes['B4'] = {'t': 'shared', 'si': '0'}
ws.cell('C4').value = '='
ws.formula_attributes['C4'] = {'t': 'shared', 'si': '0'}
content = write_worksheet(ws, {}, {})
reference_file = os.path.join(DATADIR, 'writer', 'expected', 'sheet1_formula.xml')
with open(reference_file) as expected:
diff = compare_xml(content, expected.read())
assert diff is None, diff
def test_write_auto_filter_sort_condition():
wb = Workbook()
ws = wb.worksheets[0]
ws.cell('A1').value = 'header'
ws.cell('A2').value = 1
ws.cell('A3').value = 0
ws.auto_filter.ref = 'A2:A3'
ws.auto_filter.add_sort_condition('A2:A3', descending=True)
content = write_worksheet(ws, {'header': 0}, {})
reference_file = os.path.join(DATADIR, 'writer', 'expected', 'sheet1_auto_filter_sort_condition.xml')
with open(reference_file) as expected:
diff = compare_xml(content, expected.read())
assert diff is None
content = write_workbook(wb)
reference_file = os.path.join(DATADIR, 'writer', 'expected', 'workbook_auto_filter.xml')
with open(reference_file) as expected:
diff = compare_xml(content, expected.read())