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_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_convert_excel_to_csv(self):
filename_excel = path.join(self.tempdir, 'test.xlsx')
io.ExcelWriter(filename_excel).run(self.wk, style=self.style)
filename_pattern_separated_values = path.join(self.tempdir,
'test-*.csv')
io.convert(filename_excel, filename_pattern_separated_values)
self.assertTrue(
path.isfile(
filename_pattern_separated_values.replace(
'*', '{}').format('Ws-0')))
self.assertTrue(
path.isfile(
filename_pattern_separated_values.replace(
'*', '{}').format('Ws-1')))
self.assertTrue(
path.isfile(
filename_pattern_separated_values.replace(
'*', '{}').format('Ws-2')))
# read from files
wk = io.SeparatedValuesReader(filename_pattern_separated_values).run()
# assert content is the same
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_exceptions_excel(self):
filename = path.join(self.tempdir, 'test.foo')
with self.assertRaises(ValueError) as context:
io.ExcelWriter(filename)
self.assertIn("Extension of path", str(context.exception))
self.assertIn("must be '.xlsx'", str(context.exception))
with self.assertRaises(ValueError) as context:
io.ExcelReader(filename)
self.assertIn("Extension of path", str(context.exception))
self.assertIn("must be '.xlsx'", str(context.exception))
filename = path.join(self.tempdir, 'test.xlsx')
wk = deepcopy(self.wk)
wk['Ws-0'][0][1] = []
with self.assertRaisesRegex(ValueError, '^Unsupported type '):
io.ExcelWriter(filename).run(wk, style=self.style)
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_read_empty_worksheet(self):
wk = Workbook()
ws = wk['Ws'] = Worksheet()
filename = path.join(self.tempdir, 'test.xlsx')
io.ExcelWriter(filename).run(wk)
wk2 = io.ExcelReader(filename).run()
self.assertEqual(list(wk2.keys()), ['Ws'])
self.assertEqual(wk2['Ws'], Worksheet())
filename = path.join(self.tempdir, 'test-*.csv')
io.SeparatedValuesWriter(filename).run(wk)
wk2 = io.SeparatedValuesReader(filename).run()
self.assertEqual(list(wk2.keys()), ['Ws'])
self.assertEqual(wk2['Ws'], Worksheet())
def test_convert_exceptions(self):
source = path.join(self.tempdir, 'test.xlsx')
io.ExcelWriter(source).run(self.wk, style=self.style)
# copy excel->sv
dest = path.join(self.tempdir, 'test-*.csv')
io.convert(source, dest)
wk = io.SeparatedValuesReader(dest).run()
self.assertEqual(wk, self.wk)
# copy sv->excel
source = path.join(self.tempdir, 'test-2-*.csv')
dest = path.join(self.tempdir, 'test2-*.csv')
with self.assertRaisesRegex(ValueError, ' does not match any files$'):
io.convert(source, dest)
def test_convert_with_worksheet_order(self):
source = path.join(self.tempdir, 'test.xlsx')
io.ExcelWriter(source).run(self.wk, style=self.style)
dest = path.join(self.tempdir, 'test-2.xlsx')
io.convert(source,
dest,
worksheet_order=['Ws-3'],
ignore_extra_sheets=True,
style=self.style)
wk = io.ExcelReader(dest).run()
self.assertEqual(set(wk.keys()), set(['Ws-0', 'Ws-1', 'Ws-2']))
with self.assertRaisesRegex(ValueError, ' missing worksheets:'):
io.convert(source,
dest,
worksheet_order=['Ws-3'],
ignore_extra_sheets=False)
def test_convert(self):
source = path.join(self.tempdir, 'test.xlsx')
io.ExcelWriter(source).run(self.wk, style=self.style)
# copy excel->sv
dest = path.join(self.tempdir, 'test-*.csv')
io.convert(source, dest)
wk = io.SeparatedValuesReader(dest).run()
self.assertEqual(wk, self.wk)
# copy sv->excel
source = path.join(self.tempdir, 'test-*.csv')
dest = path.join(self.tempdir, 'test2.xlsx')
io.convert(source, dest, style=self.style)
wk = io.ExcelReader(dest).run()
self.assertEqual(wk, self.wk)
# copy same format - excel
source = path.join(self.tempdir, 'test.xlsx')
dest = path.join(self.tempdir, 'test3.xlsx')
io.convert(source, dest, style=self.style)
wk = io.ExcelReader(dest).run()
self.assertEqual(wk, self.wk)
# copy same format - csv
source = path.join(self.tempdir, 'test-*.csv')
dest = path.join(self.tempdir, 'test2-*.csv')
io.convert(source, dest)
wk = io.SeparatedValuesReader(dest).run()
self.assertEqual(wk, self.wk)
# negative examples
source = path.join(self.tempdir, 'test.xlsx')
dest = path.join(self.tempdir, 'test.xlsx')
self.assertRaises(ValueError, lambda: io.convert(source, dest))
source = path.join(self.tempdir, 'test.xlsx')
dest = path.join(self.tempdir, 'test.xlsx2')
self.assertRaises(ValueError, lambda: io.convert(source, dest))
source = path.join(self.tempdir, 'test.xlsx2')
dest = path.join(self.tempdir, 'test.xlsx')
self.assertRaises(ValueError, lambda: io.convert(source, dest))
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)