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_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_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_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 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_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
assert '<printOptions horizontalCentered="1" verticalCentered="1">' 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
assert "<printOptions" not in xml_string
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_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_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_style():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('F1').value = '13%'
ws.column_dimensions['F'].style_index = 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 _write_worksheets(self, archive, shared_string_table, 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, shared_string_table,
style_writer.get_style_by_hash()))
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 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}, {})
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