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_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_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_read_formula_boolean(self):
wb = openpyxl.Workbook()
ws = wb.create_sheet('Sheet-1')
cell = ws.cell(row=1, column=1)
cell.data_type = openpyxl.cell.cell.TYPE_FORMULA
cell.value = '=TRUE()'
cell = ws.cell(row=1, column=2)
cell.data_type = openpyxl.cell.cell.TYPE_FORMULA
cell.value = '=TRUE'
cell = ws.cell(row=2, column=1)
cell.data_type = openpyxl.cell.cell.TYPE_FORMULA
cell.value = '=FALSE()'
cell = ws.cell(row=2, column=2)
cell.data_type = openpyxl.cell.cell.TYPE_FORMULA
cell.value = '=FALSE'
filename = path.join(self.tempdir, 'test.xlsx')
wb.save(filename)
wb2 = io.ExcelReader(filename).run()
self.assertEqual(wb2['Sheet-1'][0][0], True)
self.assertEqual(wb2['Sheet-1'][0][1], True)
self.assertEqual(wb2['Sheet-1'][1][0], False)
self.assertEqual(wb2['Sheet-1'][1][1], 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_read_formula_cache_string(self):
wb = openpyxl.Workbook()
ws = wb.create_sheet('Sheet-1')
cell = ws.cell(row=1, column=1)
cell.data_type = openpyxl.cell.cell.TYPE_FORMULA_CACHE_STRING
cell.value = '=1+2'
filename = path.join(self.tempdir, 'test.xlsx')
wb.save(filename)
io.ExcelReader(filename).run()
def test_excel_read_error(self):
wb = openpyxl.Workbook()
ws = wb.create_sheet('Sheet-1')
cell = ws.cell(row=1, column=1)
cell.data_type = openpyxl.cell.cell.TYPE_ERROR
cell.value = '#NAME?'
filename = path.join(self.tempdir, 'test.xlsx')
wb.save(filename)
with self.assertRaisesRegex(ValueError, 'Errors are not supported'):
io.ExcelReader(filename).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_excel_ignore_empty_final_rows_and_cols(self):
wb = openpyxl.Workbook()
ws = wb.create_sheet('Sheet-1')
cell = ws.cell(row=1, column=1)
cell.data_type = openpyxl.cell.cell.TYPE_STRING
cell.value = 'A1'
cell = ws.cell(row=1, column=2)
cell.data_type = openpyxl.cell.cell.TYPE_STRING
cell.value = 'A2'
cell = ws.cell(row=1, column=3)
cell.data_type = openpyxl.cell.cell.TYPE_NULL
cell.value = None
cell = ws.cell(row=2, column=1)
cell.data_type = openpyxl.cell.cell.TYPE_STRING
cell.value = 'B1'
cell = ws.cell(row=2, column=2)
cell.data_type = openpyxl.cell.cell.TYPE_STRING
cell.value = 'B2'
cell = ws.cell(row=2, column=3)
cell.data_type = openpyxl.cell.cell.TYPE_NULL
cell.value = None
cell = ws.cell(row=3, column=1)
cell.data_type = openpyxl.cell.cell.TYPE_NULL
cell.value = None
cell = ws.cell(row=3, column=2)
cell.data_type = openpyxl.cell.cell.TYPE_NULL
cell.value = None
cell = ws.cell(row=3, column=3)
cell.data_type = openpyxl.cell.cell.TYPE_NULL
cell.value = None
filename = path.join(self.tempdir, 'test.xlsx')
wb.save(filename)
wb = io.ExcelReader(filename).run()
self.assertEqual(len(wb['Sheet-1']), 2)
self.assertEqual(list(wb['Sheet-1'][0]), ['A1', 'A2'])
self.assertEqual(list(wb['Sheet-1'][1]), ['B1', 'B2'])
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_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_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_excel_read_valid_types_empty(self):
wb = openpyxl.Workbook()
ws = wb.create_sheet('Sheet-1')
cell = ws.cell(row=1, column=1)
cell.set_explicit_value(None, openpyxl.cell.cell.TYPE_STRING)
cell = ws.cell(row=1, column=2)
cell.set_explicit_value('', openpyxl.cell.cell.TYPE_STRING)
cell = ws.cell(row=2, column=1)
cell.set_explicit_value(None, openpyxl.cell.cell.TYPE_INLINE)
cell = ws.cell(row=2, column=2)
cell.set_explicit_value('', openpyxl.cell.cell.TYPE_INLINE)
cell = ws.cell(row=3, column=1)
cell.set_explicit_value(None, openpyxl.cell.cell.TYPE_NUMERIC)
cell = ws.cell(row=3, column=2)
cell.set_explicit_value('', openpyxl.cell.cell.TYPE_NUMERIC)
cell = ws.cell(row=4, column=1)
cell.set_explicit_value(None, openpyxl.cell.cell.TYPE_BOOL)
cell = ws.cell(row=4, column=2)
cell.set_explicit_value('', openpyxl.cell.cell.TYPE_BOOL)
cell = ws.cell(row=5, column=1)
cell.set_explicit_value(None, openpyxl.cell.cell.TYPE_NULL)
cell = ws.cell(row=5, column=2)
cell.set_explicit_value('', openpyxl.cell.cell.TYPE_NULL)
cell = ws.cell(row=6, column=2)
cell.set_explicit_value(
'end',
openpyxl.cell.cell.TYPE_STRING) # to force max row and column
filename = path.join(self.tempdir, 'test.xlsx')
wb.save(filename)
wb2 = openpyxl.load_workbook(filename)
self.assertEqual(wb2['Sheet-1'].cell(row=1, column=1).data_type,
openpyxl.cell.cell.TYPE_NULL)
self.assertEqual(wb2['Sheet-1'].cell(row=1, column=2).data_type,
openpyxl.cell.cell.TYPE_INLINE)
self.assertEqual(wb2['Sheet-1'].cell(row=2, column=1).data_type,
openpyxl.cell.cell.TYPE_NULL)
self.assertEqual(wb2['Sheet-1'].cell(row=2, column=2).data_type,
openpyxl.cell.cell.TYPE_INLINE)
self.assertEqual(wb2['Sheet-1'].cell(row=3, column=1).data_type,
openpyxl.cell.cell.TYPE_NULL)
self.assertEqual(wb2['Sheet-1'].cell(row=3, column=2).data_type,
openpyxl.cell.cell.TYPE_NULL)
self.assertEqual(wb2['Sheet-1'].cell(row=4, column=1).data_type,
openpyxl.cell.cell.TYPE_NULL)
self.assertEqual(wb2['Sheet-1'].cell(row=4, column=2).data_type,
openpyxl.cell.cell.TYPE_BOOL)
self.assertEqual(wb2['Sheet-1'].cell(row=5, column=1).data_type,
openpyxl.cell.cell.TYPE_NULL)
self.assertEqual(wb2['Sheet-1'].cell(row=5, column=2).data_type,
openpyxl.cell.cell.TYPE_NULL)
wb2 = io.ExcelReader(filename).run()
self.assertEqual(wb2['Sheet-1'][0][0], None)
self.assertEqual(wb2['Sheet-1'][0][1], None)
self.assertEqual(wb2['Sheet-1'][1][0], None)
self.assertEqual(wb2['Sheet-1'][1][1], None)
self.assertEqual(wb2['Sheet-1'][2][0], None)
self.assertEqual(wb2['Sheet-1'][2][1], None)
self.assertEqual(wb2['Sheet-1'][3][0], None)
self.assertEqual(wb2['Sheet-1'][3][1], None)
self.assertEqual(wb2['Sheet-1'][4][0], None)
self.assertEqual(wb2['Sheet-1'][4][1], None)
def test_excel_read_valid_types(self):
wb = openpyxl.Workbook()
ws = wb.create_sheet('Sheet-1')
cell = ws.cell(row=1, column=1)
cell.data_type = openpyxl.cell.cell.TYPE_STRING
cell.value = 'A1'
cell = ws.cell(row=2, column=1)
cell.data_type = openpyxl.cell.cell.TYPE_NUMERIC
cell.value = 2.5
cell = ws.cell(row=3, column=1)
cell.data_type = openpyxl.cell.cell.TYPE_NUMERIC
cell.value = None
cell = ws.cell(row=4, column=1)
cell.data_type = openpyxl.cell.cell.TYPE_BOOL
cell.value = True
cell = ws.cell(row=5, column=1)
cell.data_type = openpyxl.cell.cell.TYPE_BOOL
cell.value = False
cell = ws.cell(row=6, column=1)
cell.data_type = openpyxl.cell.cell.TYPE_NULL
cell.value = None
cell = ws.cell(row=7, column=1)
cell.data_type = openpyxl.cell.cell.TYPE_INLINE
cell.value = '<b>A7</b>'
cell = ws.cell(row=8, column=1)
cell.data_type = 'd'
cell.style = NamedStyle(name='datetime',
number_format='DD/MM/YYYY HH:MM:SS')
datetime_value_1 = datetime.strptime("30/10/2019 13:56:30",
'%d/%m/%Y %H:%M:%S')
cell.value = datetime_value_1
cell = ws.cell(row=9, column=1)
cell.data_type = 'd'
cell.style = NamedStyle(name='date', number_format='DD/MM/YYYY')
datetime_value_2 = datetime.strptime("30/10/2019", '%d/%m/%Y')
cell.value = datetime_value_2
cell = ws.cell(row=10, column=1)
cell.data_type = 'd'
cell.style = NamedStyle(name='time', number_format='HH:MM:SS')
datetime_value_3 = datetime.strptime("13:56:30", '%H:%M:%S')
cell.value = datetime_value_3
filename = path.join(self.tempdir, 'test.xlsx')
wb.save(filename)
wb2 = io.ExcelReader(filename).run()
self.assertEqual(wb2['Sheet-1'][0][0], 'A1')
self.assertEqual(wb2['Sheet-1'][1][0], 2.5)
self.assertEqual(wb2['Sheet-1'][2][0], None)
self.assertEqual(wb2['Sheet-1'][3][0], True)
self.assertEqual(wb2['Sheet-1'][4][0], False)
self.assertEqual(wb2['Sheet-1'][5][0], None)
self.assertEqual(wb2['Sheet-1'][6][0], '<b>A7</b>')
self.assertEqual(wb2['Sheet-1'][7][0], datetime_value_1)
self.assertEqual(wb2['Sheet-1'][8][0], datetime_value_2)
self.assertEqual(wb2['Sheet-1'][9][0], datetime_value_3)