def setUp(self):
# test data set
wk = self.wk = Workbook()
ws0 = wk['Ws-0'] = Worksheet()
ws0.append(Row(['Id', 'Val-1', 'Val-2', 'Val-3']))
ws0.append(Row(['a0\taa0\naaa0', 1, 2., True]))
ws0.append(Row([u'b0\u20ac', 3, 4., False]))
ws0.append(Row(['c0', 5, 6., None]))
ws1 = wk['Ws-1'] = Worksheet()
ws1.append(Row(['Id', 'Val-1', 'Val-2']))
ws1.append(Row(['a1', 1, 2.]))
ws1.append(Row(['b1', 3, 4.]))
ws1.append(Row(['c1', 5, 6.]))
ws2 = wk['Ws-2'] = Worksheet()
ws2.append(Row(['Id', 'Val-1', 'Val-2']))
ws2.append(Row(['a2', 1, 2.]))
ws2.append(Row(['b2', 3, 4.]))
ws2.append(Row(['c2', 5, 6.]))
style = self.style = io.WorkbookStyle()
style['Ws-0'] = io.WorksheetStyle(extra_rows=2, extra_columns=2)
style['Ws-1'] = io.WorksheetStyle(extra_rows=2, extra_columns=2)
style['Ws-2'] = io.WorksheetStyle(extra_rows=2, extra_columns=2)
# create temp directory
self.tempdir = mkdtemp()
def test_write_excel_pattern_error(self):
filename = path.join(self.tempdir, 'test.xlsx')
style = self.style
style['Ws-0'] = io.WorksheetStyle(
head_rows=0,
head_columns=0,
head_row_fill_pattern='UNDEFINED_PATTERN',
head_row_fill_fgcolor='CCCCCC',
row_height=float('nan'),
col_width=10.,
extra_rows=float('inf'),
extra_columns=float('inf'))
with self.assertRaisesRegex(ValueError, 'Unsupported pattern'):
io.ExcelWriter(filename).run(self.wk, style=style)
filename = path.join(self.tempdir, 'test.xlsx')
style = self.style
style['Ws-0'] = io.WorksheetStyle(
head_rows=0,
head_columns=0,
title_row_fill_pattern='UNDEFINED_PATTERN',
title_row_fill_fgcolor='CCCCCC',
row_height=float('nan'),
col_width=10.,
extra_rows=float('inf'),
extra_columns=float('inf'))
with self.assertRaisesRegex(ValueError, 'Unsupported pattern'):
io.ExcelWriter(filename).run(self.wk, style=style)
def test_read_write_excel(self):
# write to file
filename = path.join(self.tempdir, 'test.xlsx')
io.ExcelWriter(filename).run(self.wk, style=self.style)
self.assertTrue(path.isfile(filename))
# write to file with style
style = self.style
style['Ws-0'] = io.WorksheetStyle(title_rows=1,
head_rows=1,
head_columns=1,
head_row_font_bold=True,
head_row_fill_fgcolor='CCCCCC',
row_height=15,
extra_columns=2,
extra_rows=2)
io.ExcelWriter(filename).run(self.wk, style=style)
self.assertTrue(path.isfile(filename))
# read from file
wk = io.ExcelReader(filename).run()
# assert content is the same
ws = wk['Ws-0']
self.assertIsInstance(ws[1][0], string_types)
self.assertIsInstance(ws[1][1], integer_types)
self.assertIsInstance(ws[1][2], integer_types)
self.assertIsInstance(ws[1][3], bool)
self.assertEqual(ws[2][0], u'b0\u20ac')
self.assertEqual(ws[3][3], None)
self.assertEqual(wk, self.wk)
def test_formula_hyperlink(self):
wb = Workbook()
ws0 = wb['Ws'] = Worksheet()
ws0.append(Row(['abc', 'def', 'ghi']))
ws0[0][0] = Formula('="abc"', 'abc')
wb_1 = Workbook()
ws0 = wb_1['Ws'] = Worksheet()
ws0.append(Row(['abc', 'def', 'ghi']))
ws0[0][0] = Formula('="abc"', 'abc')
style = io.WorkbookStyle()
style['Ws'] = io.WorksheetStyle(hyperlinks=[
io.Hyperlink(0, 1, 'https://google.com', tip='Click to view def')
])
filename = path.join(self.tempdir, 'test.xlsx')
io.ExcelWriter(filename).run(wb, style=style)
wb_2 = io.ExcelReader(filename).run()
wb_2['Ws'][0][0].value = 'abc'
self.assertEqual(wb_2, wb_1)
filename = path.join(self.tempdir, 'test*.csv')
io.SeparatedValuesWriter(filename).run(wb, style=style)
wb_2 = io.SeparatedValuesReader(filename).run()
wb_2['Ws'][0][0] = Formula('="abc"', wb_2['Ws'][0][0])
self.assertEqual(wb_2, wb_1)
def test_excel_merge_cells(self):
wb = Workbook()
ws0 = wb['Ws-0'] = Worksheet()
ws0.append(Row([None, 'Vals', 'Vals', 'Vals']))
ws0.append(Row([None, 'Vals 1-2', 'Vals 1-2', None]))
ws0.append(Row([None, 'Vals 1-2', 'Vals 1-2', None]))
ws0.append(Row(['Id', 'Val-1', 'Val-2', 'Val-3']))
ws0.append(Row(['a0\taa0\naaa0', 1, 2., True]))
ws0.append(Row([u'b0\u20ac', 3, 4., False]))
ws0.append(Row(['c0', 5, 6., None]))
style = io.WorkbookStyle()
style['Ws-0'] = io.WorksheetStyle(head_rows=4,
merge_ranges=[(0, 0, 0, 0),
(0, 1, 0, 3),
(1, 1, 2, 2)],
blank_head_fill_fgcolor='EEEEEE',
merged_head_fill_fgcolor='AAAAAA')
filename = path.join(self.tempdir, 'test.xlsx')
io.ExcelWriter(filename).run(wb, style=style)
wb_2 = io.ExcelReader(filename).run()
self.assertEqual(wb_2, wb)
filename = path.join(self.tempdir, 'test-*.csv')
io.SeparatedValuesWriter(filename).run(wb, style=style)
wb_2 = io.SeparatedValuesReader(filename).run()
self.assertEqual(wb_2, wb)
def test_write_excel_long_message_truncation(self):
filename = path.join(self.tempdir, 'test.xlsx')
style = self.style
style['Ws-0'] = io.WorksheetStyle(head_rows=1,
head_columns=0,
head_row_fill_pattern='solid',
head_row_fill_fgcolor='CCCCCC',
row_height=15.01,
col_width=10.,
extra_rows=2,
extra_columns=2)
validation = io.WorkbookValidation()
validation['Ws-0'] = io.WorksheetValidation(fields=[
io.FieldValidation(input_title='Enter a identifier' * 100,
input_message='A unique string' * 100,
error_title='Enter a identifier' * 100,
error_message='A unique string' * 100,
type=io.FieldValidationType.length,
criterion=io.FieldValidationCriterion['<='],
allowed_scalar_value=255),
None,
io.FieldValidation(input_title='Enter a second value',
input_message='A float',
type=io.FieldValidationType.decimal,
criterion=io.FieldValidationCriterion['>='],
minimum_scalar_value=-1000.),
io.FieldValidation(input_title='Enter a third value',
input_message='A float',
type=io.FieldValidationType.any),
])
io.ExcelWriter(filename).run(self.wk,
style=style,
validation=validation)
self.assertTrue(path.isfile(filename))
def test_write_excel_show_inf_rows_cols(self):
filename = path.join(self.tempdir, 'test.xlsx')
style = self.style
style['Ws-0'] = io.WorksheetStyle(head_rows=1,
head_columns=1,
head_row_fill_pattern='solid',
head_row_fill_fgcolor='CCCCCC',
row_height=float('nan'),
col_width=10.,
extra_rows=float('inf'),
extra_columns=float('inf'))
io.ExcelWriter(filename).run(self.wk, style=style)
self.assertTrue(path.isfile(filename))
def test_excel_merge_cells_error(self):
wb = Workbook()
ws0 = wb['Ws-0'] = Worksheet()
ws0.append(Row([None, 'Vals', None, None]))
ws0.append(Row([None, 'Vals 1-2', None, None]))
ws0.append(Row([None, None, None, None]))
ws0.append(Row(['Id', 'Val-1', 'Val-2', 'Val-3']))
ws0.append(Row(['a0\taa0\naaa0', 1, 2., True]))
ws0.append(Row([u'b0\u20ac', 3, 4., False]))
ws0.append(Row(['c0', 5, 6., None]))
style = io.WorkbookStyle()
style['Ws-0'] = io.WorksheetStyle(
merge_ranges=[(0, 0, 0, 0), (0, 1, 0, 3), (1, 1, 2, 2), (3, 0, 3,
3)])
filename = path.join(self.tempdir, 'test.xlsx')
with self.assertRaisesRegex(ValueError, 'can have at most 1 value'):
io.ExcelWriter(filename).run(wb, style=style)