def test_db_ws_names(self):
# locally defined to return an empty list
db = xl.Database()
self.assertEqual([], db.ws_names)
def test_db_init(self):
# locally defined to return an empty ws
db = xl.Database()
self.assertEqual({}, db._ws)
class TestDatabase(TestCase):
db = xl.Database()
def test_db_badsheet(self):
db = xl.Database()
with self.assertRaises(UserWarning) as e:
db.ws('not a sheet')
self.assertEqual(
'pylightxl - Sheetname (not a sheet) is not in the database',
e)
def test_db_init(self):
# locally defined to return an empty ws
db = xl.Database()
self.assertEqual({}, db._ws)
def test_db_repr(self):
self.assertEqual('pylightxl.Database', str(DB))
def test_db_ws_names(self):
# locally defined to return an empty list
db = xl.Database()
self.assertEqual([], db.ws_names)
def test_db_add_ws(self):
db = xl.Database()
db.add_ws(ws='test1', data={})
self.assertEqual('pylightxl.Database.Worksheet',
str(db.ws(ws='test1')))
self.assertEqual(['test1'], db.ws_names)
db.add_ws('test2')
self.assertEqual(['test1', 'test2'], db.ws_names)
def test_db_remove_ws(self):
db = xl.Database()
db.add_ws('one')
db.add_ws('two')
db.add_ws('three')
db.remove_ws(ws='two')
self.assertEqual(['one', 'three'], db.ws_names)
self.assertEqual(False, 'two' in db._ws.keys())
# remove one thats not in the db
self.assertEqual(None, db.remove_ws('not real'))
def test_namedranges(self):
db = xl.Database()
# single entry
db.add_nr(ws='one', name='r1', address='A1')
self.assertEqual({'r1': 'one!A1'}, db.nr_names)
# multi entry
db.add_nr(ws='two', name='r2', address='A2:A3')
self.assertEqual({'r1': 'one!A1', 'r2': 'two!A2:A3'}, db.nr_names)
# overwrite by name
db.add_nr(ws='three', name='r1', address='A3')
self.assertEqual({'r1': 'three!A3', 'r2': 'two!A2:A3'}, db.nr_names)
# overwrite by address
db.add_nr(ws='three', name='r3', address='A3')
self.assertEqual({'r3': 'three!A3', 'r2': 'two!A2:A3'}, db.nr_names)
# overwrite by both name and address
db.add_nr(ws='three', name='r3', address='A4')
self.assertEqual({'r3': 'three!A4', 'r2': 'two!A2:A3'}, db.nr_names)
# remove $ references
db.add_nr(ws='three', name='r3', address='$A$4')
self.assertEqual({'r3': 'three!A4', 'r2': 'two!A2:A3'}, db.nr_names)
# remove a nr
db.remove_nr(name='r3')
self.assertEqual({'r2': 'two!A2:A3'}, db.nr_names)
# call a nr that is not in there
self.assertEqual([[]], db.nr('not real'))
def test_namedrange_val(self):
db = xl.Database()
db.add_ws('sh1')
db.ws('sh1').update_address('A1', 11)
db.ws('sh1').update_address('B1', 12)
db.ws('sh1').update_address('C2', 23)
db.add_nr(name='table1', ws='sh1', address='A1')
db.add_nr(name='table2', ws='sh1', address='A1:C2')
self.assertEqual([[11]], db.nr(name='table1'))
self.assertEqual([[11, 12, ''], ['', '', 23]], db.nr(name='table2'))
db.ws('sh1').update_address('A1', '=11')
db.ws('sh1').update_address('B1', '=12')
db.ws('sh1').update_address('C2', '=23')
self.assertEqual([['=11']], db.nr(name='table1', formula=True))
self.assertEqual([['=11', '=12', ''], ['', '', '=23']],
db.nr(name='table2', formula=True))
def test_rename_ws(self):
db = xl.Database()
db.add_ws('one')
db.ws('one').update_address('A1', 10)
db.add_ws('two')
db.ws('two').update_address('A1', 20)
db.add_ws('three')
db.ws('three').update_address('A1', 30)
# rename to overlapping name should keep the data of the "two", "one" should be removed
db.rename_ws('one', 'two')
self.assertEqual(['two', 'three'], db.ws_names)
self.assertEqual(10, db.ws('two').address('A1'))
# name a ws thats not in db
self.assertEqual(None, db.rename_ws('not real', 'new'))
# rename to new sheet
db.rename_ws('three', 'four')
self.assertEqual(['two', 'four'], db.ws_names)
def test_writecsv(self):
db = xl.Database()
db.add_ws('sh1')
db.add_ws('sh2')
db.ws('sh1').update_index(1,1, 10)
db.ws('sh1').update_index(1,2, 10.0)
db.ws('sh1').update_index(1,3, '10.0')
db.ws('sh1').update_index(1,4, True)
db.ws('sh1').update_index(2,1, 20)
db.ws('sh1').update_index(2,2, 20.0)
db.ws('sh1').update_index(2,3, '20.0')
db.ws('sh1').update_index(2,4, False)
db.ws('sh1').update_index(3,5, ' ')
db.ws('sh2').update_index(1,1, 'sh2')
if 'outcsv_sh1.csv' in os.listdir('.'):
os.remove('outcsv_sh1.csv')
if 'outcsv_sh2.csv' in os.listdir('.'):
os.remove('outcsv_sh2.csv')
xl.writecsv(db=db, fn='outcsv', delimiter='\t', ws='sh1')
with open('outcsv_sh1.csv', 'r') as f:
lines = []
while True:
line = f.readline()
if not line:
break
line = line.replace('\n', '').replace('\r', '')
lines.append(line.split('\t'))
self.assertEqual(['10', '10.0', '10.0', 'True', ''], lines[0])
self.assertEqual(['20', '20.0', '20.0', 'False', ''], lines[1])
self.assertEqual(['', '', '', '', ' '], lines[2])
if 'outcsv_sh1.csv' in os.listdir('.'):
os.remove('outcsv_sh1.csv')
if 'outcsv_sh2.csv' in os.listdir('.'):
os.remove('outcsv_sh2.csv')
xl.writecsv(db=db, fn='outcsv')
self.assertTrue('outcsv_sh1.csv' in os.listdir('.'))
self.assertTrue('outcsv_sh2.csv' in os.listdir('.'))
if 'outcsv_sh1.csv' in os.listdir('.'):
os.remove('outcsv_sh1.csv')
if 'outcsv_sh2.csv' in os.listdir('.'):
os.remove('outcsv_sh2.csv')
f = io.StringIO()
xl.writecsv(db=db, fn=f)
f.seek(0)
self.assertEqual('10,10.0,10.0,True,\n', f.readline())
self.assertEqual('20,20.0,20.0,False,\n', f.readline())
self.assertEqual(',,,, \n', f.readline())
self.assertEqual('sh2\n', f.readline())
def test_integration_alt_writer(self):
db = xl.Database()
# cleanup failed test workbook
if 'temp_wb.xlsx' in os.listdir('.'):
os.remove('temp_wb.xlsx')
if '_pylightxl_temp_wb.xlsx' in os.listdir('.'):
shutil.rmtree('_pylightxl_temp_wb.xlsx')
# create the "existing workbook"
db.add_ws(ws='sh1', data={'A1': {'v':'one', 'f': '', 's': ''},
'A2': {'v':1, 'f': '', 's': ''},
'A3': {'v':1.0, 'f': '', 's': ''},
'A4': {'v':'one', 'f': 'A1', 's': ''},
'A5': {'v':6, 'f': 'A2+5', 's': ''},
'B1': {'v': 'one', 'f': '', 's': ''},
'B2': {'v': 1, 'f': '', 's': ''},
'B3': {'v': 1.0, 'f': '', 's': ''},
'B4': {'v': 'one', 'f': 'A1', 's': ''},
'B5': {'v': 6, 'f': 'A2+5', 's': ''},
})
db.add_ws(ws='sh2')
xl.writexl(db, 'temp_wb.xlsx')
# all changes will be registered as altered xl writer since the filename exists
db.ws(ws='sh1').update_address('B1', 'two')
db.ws(ws='sh1').update_address('B2', 2)
db.ws(ws='sh1').update_address('B3', 2.0)
# was a formula now a string that looks like a formula
db.ws(ws='sh1').update_address('B4', 'A1&"_"&"two"')
db.ws(ws='sh1').update_address('B5', '=A2+10')
db.ws(ws='sh1').update_address('C6', 'new')
db.add_ws(ws='sh3')
db.ws(ws='sh3').update_address('A1', 'one')
xl.writexl(db, 'temp_wb.xlsx')
# check the results made it in correctly
db_alt = xl.readxl(fn='temp_wb.xlsx')
self.assertEqual([6, 3], db_alt.ws('sh1').size)
self.assertEqual('one', db_alt.ws('sh1').address('A1'))
self.assertEqual(1, db_alt.ws('sh1').address('A2'))
self.assertEqual(1.0, db_alt.ws('sh1').address('A3'))
self.assertEqual('', db_alt.ws('sh1').address('A4'))
self.assertEqual('A1', db_alt.ws('sh1')._data['A4']['f'])
self.assertEqual('', db_alt.ws('sh1').address('A5'))
self.assertEqual('A2+5', db_alt.ws('sh1')._data['A5']['f'])
self.assertEqual('two', db_alt.ws('sh1').address('B1'))
self.assertEqual(2, db_alt.ws('sh1').address('B2'))
self.assertEqual(2.0, db_alt.ws('sh1').address('B3'))
self.assertEqual('A1&"_"&"two"', db_alt.ws('sh1').address('B4'))
self.assertEqual('', db_alt.ws('sh1')._data['B4']['f'])
self.assertEqual('', db_alt.ws('sh1').address('B5'))
self.assertEqual('A2+10', db_alt.ws('sh1')._data['B5']['f'])
self.assertEqual('new', db_alt.ws('sh1').address('C6'))
self.assertEqual([0, 0], db_alt.ws('sh2').size)
self.assertEqual('', db_alt.ws('sh2').address('A1'))
self.assertEqual([1, 1], db_alt.ws('sh3').size)
self.assertEqual('one', db_alt.ws('sh3').address('A1'))
# cleanup failed test workbook
if 'temp_wb.xlsx' in os.listdir('.'):
os.remove('temp_wb.xlsx')
def test_app_text(self):
xml_base = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>\r\n' \
'<Properties xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes" xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties">\r\n' \
'<Application>Microsoft Excel</Application>\r\n' \
'<DocSecurity>0</DocSecurity>\r\n' \
'<ScaleCrop>false</ScaleCrop>\r\n' \
'<HeadingPairs>\r\n' \
'<vt:vector baseType="variant" size="{vector_size}">\r\n' \
'<vt:variant>\r\n' \
'<vt:lpstr>Worksheets</vt:lpstr>\r\n' \
'</vt:variant>\r\n' \
'<vt:variant>\r\n' \
'<vt:i4>{ws_size}</vt:i4>\r\n' \
'</vt:variant>\r\n' \
'{variant_tag_nr}' \
'</vt:vector>\r\n' \
'</HeadingPairs>\r\n' \
'<TitlesOfParts>\r\n' \
'<vt:vector baseType="lpstr" size="{vt_size}">\r\n' \
'{many_tag_vt}\r\n' \
'</vt:vector>\r\n' \
'</TitlesOfParts>\r\n' \
'<Company></Company>\r\n' \
'<LinksUpToDate>false</LinksUpToDate>\r\n' \
'<SharedDoc>false</SharedDoc>\r\n' \
'<HyperlinksChanged>false</HyperlinksChanged>\r\n' \
'<AppVersion>16.0300</AppVersion>\r\n' \
'</Properties>'
tag_vt = '<vt:lpstr>{sheet_name}</vt:lpstr>\r\n'
many_tag_vt = tag_vt.format(sheet_name='Sheet1') + \
tag_vt.format(sheet_name='Sheet2') + \
tag_vt.format(sheet_name='Sheet3') + \
tag_vt.format(sheet_name='Sheet4') + \
tag_vt.format(sheet_name='Sheet5') + \
tag_vt.format(sheet_name='Sheet6') + \
tag_vt.format(sheet_name='Sheet7') + \
tag_vt.format(sheet_name='Sheet8') + \
tag_vt.format(sheet_name='Sheet9') + \
tag_vt.format(sheet_name='Sheet10')
db = xl.Database()
db.add_ws('Sheet1',{})
db.add_ws('Sheet2',{})
db.add_ws('Sheet3',{})
db.add_ws('Sheet4',{})
db.add_ws('Sheet5',{})
db.add_ws('Sheet6',{})
db.add_ws('Sheet7',{})
db.add_ws('Sheet8',{})
db.add_ws('Sheet9',{})
db.add_ws('Sheet10',{})
self.assertEqual(xl.writexl_new_app_text(db), xml_base.format(vector_size=2,
ws_size=10,
variant_tag_nr='',
vt_size=10,
many_tag_vt=many_tag_vt))
db.add_nr('range1', 'sheet1', 'A1')
db.add_nr('range2', 'sheet1', 'A2:A5')
variant_tag_nr = '<vt:variant><vt:lpstr>Named Ranges</vt:lpstr></vt:variant>\r\n' \
'<vt:variant><vt:i4>2</vt:i4></vt:variant>\r\n'
# python 2 does not keep dict order
if sys.version_info[0] >= 3:
many_tag_vt += '<vt:lpstr>range1</vt:lpstr>\r\n'
many_tag_vt += '<vt:lpstr>range2</vt:lpstr>\r\n'
else:
many_tag_vt += '<vt:lpstr>range2</vt:lpstr>\r\n'
many_tag_vt += '<vt:lpstr>range1</vt:lpstr>\r\n'
self.assertEqual(xl.writexl_new_app_text(db), xml_base.format(vector_size=4,
ws_size=10,
variant_tag_nr=variant_tag_nr,
vt_size=12,
many_tag_vt=many_tag_vt))