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)