def test_read_write_csv_no_glob(self):
wb = Workbook()
ws = wb['Sheet1'] = Worksheet()
ws.append(Row(['a', 'b', 'c']))
ws.append(Row(['d', 'e', 'f']))
ws.append(Row(['g', 'h', 'i']))
filename = path.join(self.tempdir, 'test.csv')
io.SeparatedValuesWriter(filename).run(wb)
wb2 = io.SeparatedValuesReader(filename).run()
wb2['Sheet1'] = wb2.pop('')
self.assertEqual(wb2, wb)
filename2 = path.join(self.tempdir, 'test2-*.csv')
io.convert(filename, filename2)
wb2 = io.SeparatedValuesReader(filename2).run()
wb2['Sheet1'] = wb2.pop('')
self.assertEqual(wb2, wb)
filename3 = path.join(self.tempdir, 'test3.csv')
io.convert(filename, filename3)
wb2 = io.SeparatedValuesReader(filename3).run()
wb2['Sheet1'] = wb2.pop('')
self.assertEqual(wb2, wb)
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_read_write_csv_no_glob_error(self):
wb = Workbook()
ws = wb['Sheet1'] = Worksheet()
ws.append(Row(['a', 'b', 'c']))
ws.append(Row(['d', 'e', 'f']))
ws = wb['Sheet2'] = Worksheet()
ws.append(Row(['g', 'h', 'i']))
ws.append(Row(['j', 'k', 'l']))
filename = path.join(self.tempdir, 'test.csv')
with self.assertRaisesRegex(ValueError, 'must have a glob pattern'):
io.SeparatedValuesWriter(filename).run(wb)
def test_get_sheet_names(self):
filename_pattern = path.join(self.tempdir, 'test-*.csv')
io.SeparatedValuesWriter(filename_pattern).run(self.wk)
self.assertTrue(
path.isfile(filename_pattern.replace('*', '{}').format('Ws-0')))
self.assertTrue(
path.isfile(filename_pattern.replace('*', '{}').format('Ws-1')))
self.assertTrue(
path.isfile(filename_pattern.replace('*', '{}').format('Ws-2')))
# read from files
filename_pattern = path.join(self.tempdir, 'test-2-*.csv')
with self.assertRaisesRegex(ValueError, 'does not match any files$'):
io.SeparatedValuesReader(filename_pattern).run()
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_csv_to_excel(self):
filename_pattern_separated_values = path.join(self.tempdir,
'test-*.csv')
io.SeparatedValuesWriter(filename_pattern_separated_values).run(
self.wk)
filename_excel = path.join(self.tempdir, 'test.xlsx')
io.convert(filename_pattern_separated_values,
filename_excel,
style=self.style)
self.assertTrue(path.isfile(filename_excel))
# read from files
wk = io.ExcelReader(filename_excel).run()
# assert content is the same
self.assertEqual(wk, self.wk)
def test_read_write_tsv(self):
# write to files
filename_pattern = path.join(self.tempdir, 'test-*.tsv')
io.SeparatedValuesWriter(filename_pattern).run(self.wk)
self.assertTrue(
path.isfile(filename_pattern.replace('*', '{}').format('Ws-0')))
self.assertTrue(
path.isfile(filename_pattern.replace('*', '{}').format('Ws-1')))
self.assertTrue(
path.isfile(filename_pattern.replace('*', '{}').format('Ws-2')))
# read from files
wk = io.SeparatedValuesReader(filename_pattern).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], float)
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)