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_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_init(self):
self.assertEqual(
Worksheet(Row([0, 1, 2])),
Worksheet(Worksheet(Row([0, 1, 2]))))
self.assertEqual(
Row([0, 1, 2]),
Row(Row([0, 1, 2])))
def test_getitem(self):
ws = self.wk['Ws-0']
self.assertEqual(ws[0], Row(['Id', 'Val-1', 'Val-2', 'Val-3']))
self.assertEqual(ws[0:1], Worksheet([Row(['Id', 'Val-1', 'Val-2', 'Val-3'])]))
self.assertEqual(ws[1:3], Worksheet([
Row(['a0\taa0\naaa0', 1, 2., True]),
Row([u'b0\u20ac', 3, 4., False]),
]))
self.assertEqual(ws[0][1], 'Val-1')
self.assertEqual(ws[0][2:4], Row(['Val-2', 'Val-3']))
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_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_ne(self):
wk = deepcopy(self.wk)
self.assertEqual(self.wk != wk, False)
wk = deepcopy(self.wk)
wk['Ws-3'] = Worksheet()
self.assertEqual(self.wk != wk, True)
self.assertEqual(wk != self.wk, True)
wk = deepcopy(self.wk)
wk['Ws-2'].append(Row())
self.assertEqual(self.wk != wk, True)
self.assertEqual(wk != self.wk, True)
wk = deepcopy(self.wk)
wk['Ws-2'][0].append(None)
self.assertEqual(self.wk != wk, True)
self.assertEqual(wk != self.wk, True)
wk = deepcopy(self.wk)
wk['Ws-1'][1][2] = 3.5
self.assertEqual(self.wk != wk, True)
self.assertEqual(wk != self.wk, True)
wk = deepcopy(self.wk)
wk['Ws-1'][1][2] = 'test'
self.assertEqual(self.wk != wk, True)
self.assertEqual(wk != self.wk, True)
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(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_remove_empty_final_cols(self):
ws = Worksheet()
ws.append(Row(['a', None, None]))
ws.append(Row(['b', None, 'd', None, '']))
self.assertEqual(len(ws), 2)
ws.remove_empty_final_cols()
self.assertEqual(len(ws), 2)
self.assertEqual(ws[0], Row(['a', None, None]))
self.assertEqual(ws[1], Row(['b', None, 'd']))
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.]))
def test_difference(self):
wk = deepcopy(self.wk)
self.assertEqual(self.wk.difference(wk), {})
self.assertEqual(str(self.wk.difference(wk)), '')
wk = deepcopy(self.wk)
wk['Ws-3'] = Worksheet()
self.assertEqual(self.wk.difference(wk), {'Ws-3': 'Sheet not in self'})
self.assertEqual(wk.difference(self.wk), {'Ws-3': 'Sheet not in other'})
self.assertEqual(str(wk.difference(self.wk)), 'Sheet Ws-3:\n Sheet not in other')
wk = deepcopy(self.wk)
wk['Ws-2'].append(Row())
self.assertEqual(self.wk.difference(wk), {'Ws-2': WorksheetDifference({4: 'Row not in self'})})
self.assertEqual(wk.difference(self.wk), {'Ws-2': WorksheetDifference({4: 'Row not in other'})})
self.assertEqual(str(wk.difference(self.wk)), 'Sheet Ws-2:\n Row 5:\n Row not in other')
wk = deepcopy(self.wk)
wk['Ws-2'][0].append(None)
self.assertEqual(self.wk.difference(wk),
{'Ws-2': WorksheetDifference({0: RowDifference({3: 'Cell not in self'})})})
self.assertEqual(wk.difference(self.wk),
{'Ws-2': WorksheetDifference({0: RowDifference({3: 'Cell not in other'})})})
self.assertEqual(str(wk.difference(self.wk)), 'Sheet Ws-2:\n Row 1:\n Cell D: Cell not in other')
wk = deepcopy(self.wk)
wk['Ws-1'][1][2] = 3.5
self.assertEqual(self.wk.difference(wk),
{'Ws-1': WorksheetDifference({1: RowDifference({2: CellDifference('2.0 != 3.5')})})})
self.assertEqual(wk.difference(self.wk),
{'Ws-1': WorksheetDifference({1: RowDifference({2: CellDifference('3.5 != 2.0')})})})
wk = deepcopy(self.wk)
wk['Ws-1'][1][2] = 'test'
self.assertEqual(self.wk.difference(wk),
{'Ws-1': WorksheetDifference({1: RowDifference({2: CellDifference('2.0 != test')})})})
self.assertEqual(wk.difference(self.wk),
{'Ws-1': WorksheetDifference({1: RowDifference({2: CellDifference('test != 2.0')})})})
with self.assertRaisesRegex(ValueError, '`other` must be an instance of `Workbook`'):
self.wk.difference('wk')
wk = deepcopy(self.wk)
wk['Ws-1'] = 'Ws-1'
with self.assertRaisesRegex(ValueError, '`other` must be an instance of `Worksheet`'):
self.wk.difference(wk)
wk = deepcopy(self.wk)
wk['Ws-1'][1] = ['a1', 1, 2.]
with self.assertRaisesRegex(ValueError, '`other` must be an instance of `Row`'):
self.wk.difference(wk)
def test_eq(self):
wk = deepcopy(self.wk)
self.assertEqual(self.wk == wk, True)
wk = deepcopy(self.wk)
wk['Ws-3'] = Worksheet()
self.assertEqual(self.wk == wk, False)
self.assertEqual(wk == self.wk, False)
wk = deepcopy(self.wk)
wk['Ws-2'].append(Row())
self.assertEqual(self.wk == wk, False)
self.assertEqual(wk == self.wk, False)
wk = deepcopy(self.wk)
wk['Ws-2'][0].append(None)
self.assertEqual(self.wk == wk, False)
self.assertEqual(wk == self.wk, False)
wk = deepcopy(self.wk)
wk['Ws-1'][1][2] = 3.5
self.assertEqual(self.wk == wk, False)
self.assertEqual(wk == self.wk, False)
wk = deepcopy(self.wk)
wk['Ws-1'][1][2] = 'test'
self.assertEqual(self.wk == wk, False)
self.assertEqual(wk == self.wk, False)
self.assertEqual(self.wk == 'wk', False)
self.assertEqual(self.wk != 'wk', True)
self.assertEqual(self.wk['Ws-1'] == 'Ws-1', False)
self.assertEqual(self.wk['Ws-1'] != 'Ws-1', True)
self.assertEqual(self.wk['Ws-1'][1] == ['a1', 1, 2.], False)
self.assertEqual(self.wk['Ws-1'][1] != ['a1', 1, 2.], True)
wk = deepcopy(self.wk)
wk['Ws-1'] = 'ws'
self.assertEqual(self.wk == wk, False)
self.assertEqual(wk == self.wk, False)
wk = deepcopy(self.wk)
wk['Ws-1'][1] = ['a1', 1, 2.]
self.assertEqual(self.wk == wk, False)
self.assertEqual(wk == self.wk, False)
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)
def run(cls, taxon, reactions, filename):
""" Save a list of reaction kinetic data to an Excel workbook or set of csv/tsv files
Args:
taxon (:obj:`str`): taxon
reactions (:obj:`list` of :obj:`datanator.datanator.SabioResult`): list of reactions and their kinetic data
filename (:obj:`str`): filename to store the list reaction kinetic data
"""
wb = Workbook()
style = WorkbookStyle()
# taxon
style['Genetics'] = WorksheetStyle(head_rows=1,
head_columns=0,
head_row_font_bold=True,
head_row_fill_fgcolor='CCCCCC',
row_height=15)
ws = wb['Genetics'] = Worksheet()
ws.append(Row(['Name', 'NCBI ID']))
ws.append(Row([taxon, taxonomy_util.Taxon(taxon).get_ncbi_id()]))
# kinetics
style['Kinetics'] = WorksheetStyle(head_rows=1,
head_columns=0,
head_row_font_bold=True,
head_row_fill_fgcolor='CCCCCC',
row_height=15)
ws = wb['Kinetics'] = Worksheet()
ws.append(
Row([
'Name', 'Sabio Reaction IDs', 'Vmax Median Value',
'Vmax Median Entry', 'Vmax Min Entry', 'Vmax Max Entry',
'Vmax Proximity', 'Vmax Lift Info'
'Closest Vmax', 'Sabio Entry IDs', 'Closest Vmax Values',
'Km Median Value', 'Km Median Entry', 'Km Min Enry',
'Km Max Entry', 'Km Proximity', 'Km Lift Info',
'Closest Km Sabio Entry IDs', 'Closest Km Values'
]))
for i_row, rxn in enumerate(reactions):
ws.append(
Row([
rxn.id,
cls._format_list(rxn.reaction_ids),
# vmax information
cls._format_object(rxn.vmax_data.median_entry, "vmax"),
cls._format_object(rxn.vmax_data.median_entry),
cls._format_object(rxn.vmax_data.min_entry),
cls._format_object(rxn.vmax_data.max_entry),
cls._format_proximity(rxn.vmax_data.closest_entries),
cls._format_object(rxn.vmax_data, "lift_info"),
cls._format_list(rxn.vmax_data.closest_entry_ids),
cls._format_list(rxn.vmax_data.closest_values),
# km information
cls._format_object(rxn.km_data.median_entry, "km"),
cls._format_object(rxn.km_data.median_entry),
cls._format_object(rxn.km_data.min_entry),
cls._format_object(rxn.km_data.max_entry),
cls._format_proximity(rxn.km_data.closest_entries),
cls._format_object(rxn.km_data, "lift_info"),
cls._format_list(rxn.km_data.closest_entry_ids),
cls._format_list(rxn.km_data.closest_values),
]))
# save to file
write(filename, wb, style=style)
def run2(cls, taxon, reactions, filename):
""" Save a list of reaction kinetic data to an Excel workbook or set of csv/tsv files
Args:
taxon (:obj:`str`): taxon name
reactions (:obj:`list` of :obj:`datanator.datanator.SabioResult`): list of reactions and their kinetic data
filename (:obj:`str`): filename to store the list reaction kinetic data
"""
# kinetics
style['Kinetics'] = WorksheetStyle(head_rows=2,
head_columns=0,
head_row_font_bold=True,
head_row_fill_fgcolor='CCCCCC',
row_height=15)
ws = wb['Kinetics'] = Worksheet()
ws.append(
Row([
'',
'Vmax',
'',
'',
'',
'',
''
'',
'',
'',
'Km',
'',
'',
'',
'',
''
'',
'',
'',
'Cross references',
'',
'',
]))
ws.append(
Row([
'ID',
'Median value',
'Median entry',
'Proximity',
'Lift Info'
'Closest Vmax',
'Min',
'Max',
'Sabio Entry IDs',
'Closest Values',
'Median',
'Median Entry',
'Proximity',
'Lift Info'
'Closest Vmax',
'Min',
'Max',
'Sabio Entry IDs',
'Closest Values',
'EC number',
'Predicted EC number',
'SABIO IDs',
]))
for i_row, rxn in enumerate(reactions):
row = [
rxn.id,
# vmax
cls._format_object(rxn.vmax_data.median_entry, "vmax"),
cls._format_object(rxn.vmax_data.median_entry),
cls._format_object(rxn.vmax_data.min_entry),
cls._format_object(rxn.vmax_data.max_entry),
cls._format_proximity(rxn.vmax_data.closest_entries),
cls._format_object(rxn.vmax_data, "lift_info"),
cls._format_list(rxn.vmax_data.closest_entry_ids),
cls._format_list(rxn.vmax_data.closest_values),
# km
cls._format_object(rxn.km_data.median_entry, "km"),
cls._format_object(rxn.km_data.median_entry),
cls._format_object(rxn.km_data.min_entry),
cls._format_object(rxn.km_data.max_entry),
cls._format_proximity(rxn.km_data.closest_entries),
cls._format_object(rxn.km_data, "lift_info"),
cls._format_list(rxn.km_data.closest_entry_ids),
cls._format_list(rxn.km_data.closest_values),
# cross references
rxn.ec_number,
cls._format_list(rxn.predicted_ec_numbers),
cls._format_list(rxn.reaction_ids),
]
ws.append(Row(row))
def test_remove_empty_final_rows(self):
ws = Worksheet()
ws.append(Row(['a', 'b']))
ws.append(Row([None, None]))
ws.append(Row(['c', 'd']))
ws.append(Row([None, None]))
ws.append(Row([None, None]))
self.assertEqual(len(ws), 5)
ws.remove_empty_final_rows()
self.assertEqual(len(ws), 3)
self.assertEqual(list(ws[0]), ['a', 'b'])
self.assertEqual(list(ws[1]), [None, None])
self.assertEqual(list(ws[2]), ['c', 'd'])
