def test_pathlib_readxl(self): mypath = Path('./testbook.xlsx') db = xl.readxl(fn=mypath, ws=[ 'types', ]) self.assertEqual(11, db.ws('types').index(1, 1))
def test_SelectedSheetReading(self): db = xl.readxl('testbook.xlsx', ('empty', 'types')) db_ws_names = db.ws_names db_ws_names.sort() true_ws_names = ['empty', 'types'] true_ws_names.sort() self.assertEqual(db_ws_names, true_ws_names)
def test_reading_written_ws(self): file_path = 'temporary_test_file.xlsx' db = xl.Database() db.add_ws('new_ws') xl.writexl(db, file_path) db = xl.readxl(file_path) self.assertEqual(db.ws_names, ['new_ws']) os.remove(file_path)
def test_filehandle_readxl(self): mypath = Path('./testbook.xlsx') if sys.version_info[0] == 3: with open(mypath, 'rb') as f: db = xl.readxl(fn=f, ws=[ 'types', ]) self.assertEqual(11, db.ws('types').index(1, 1))
def test_ReadFileStream(self): with open('testbook.xlsx', 'rb') as f: db = xl.readxl(f, ('empty', 'types')) db_ws_names = db.ws_names db_ws_names.sort() true_ws_names = ['empty', 'types'] true_ws_names.sort() self.assertEqual(true_ws_names, db_ws_names)
def test_reading_written_cells(self): file_path = 'temporary_test_file.xlsx' if file_path in os.listdir('.'): os.remove(file_path) db = xl.Database() db.add_ws('new_ws', {}) ws = db.ws('new_ws') ws.update_index(row=4, col=2, val=42) xl.writexl(db, file_path) db = xl.readxl(file_path) self.assertEqual(db.ws('new_ws').index(4, 2), 42) os.remove(file_path)
def test_bad_fn_type(self): with self.assertRaises(ValueError) as e: db = xl.readxl(fn=1) self.assertEqual(e, 'Error - Incorrect file entry ({}).'.format('1'))
import os, sys # 3rd party lib support if sys.version_info[0] == 3: from pathlib import Path else: from pathlib2 import Path # local lib imports from pylightxl import pylightxl as xl if 'test' in os.listdir('.'): # running from top level os.chdir('./test') DB = xl.readxl('./testbook.xlsx') class TestReadxl_BadInput(TestCase): def test_bad_fn_type(self): with self.assertRaises(ValueError) as e: db = xl.readxl(fn=1) self.assertEqual(e, 'Error - Incorrect file entry ({}).'.format('1')) def test_bad_fn_exist(self): with self.assertRaises(ValueError) as e: db = xl.readxl('bad') self.assertEqual(e, 'Error - File ({}) does not exit.'.format('bad')) def test_bad_fn_ext(self):
def test_bad_readxl_sheetnames(self): with self.assertRaises(ValueError) as e: _ = xl.readxl(fn='./testbook.xlsx', ws='not-a-sheet') self.assertRaises( 'Error - Sheetname ({}) is not in the workbook.'.format( 'not-a-sheet'), e)
def test_bad_readxl_sheetnames(self): with self.assertRaises(ValueError) as e: db = xl.readxl('./testbook.xlsx', ('not-a-sheet',)) self.assertRaises(e, 'Error - Sheetname ({}) is not in the workbook.'.format('not-a-sheet'))
def test_bad_fn_ext(self): with self.assertRaises(UserWarning) as e: _ = xl.readxl('test_read.py') self.assertEqual( 'pylightxl - Incorrect Excel file extension ({}). ' 'File extension supported: .xlsx .xlsm'.format('py'), e)
def test_bad_fn_exist(self): with self.assertRaises(UserWarning) as e: _ = xl.readxl('bad') self.assertEqual( 'pylightxl - File ({}) does not exit.'.format('bad'), e)
def test_bad_fn_type(self): with self.assertRaises(UserWarning) as e: _ = xl.readxl(fn=1) self.assertEqual( 'pylightxl - Incorrect file entry ({}).'.format('1'), e)
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_openpyxl(self): # test that pylightxl is able to write to a openpyxl output excel file (docProps/app.xml) is different than expected db = xl.readxl('openpyxl.xlsx') xl.writexl(db, 'newopenpyxl.xlsx')
def test_bad_fn_exist(self): with self.assertRaises(ValueError) as e: db = xl.readxl('bad') self.assertEqual(e, 'Error - File ({}) does not exit.'.format('bad'))
def test_bad_fn_ext(self): with self.assertRaises(ValueError) as e: db = xl.readxl('test_read.py') self.assertEqual(e, 'Error - Incorrect Excel file extension ({}). ' 'File extension supported: .xlsx .xlsm'.format('py'))
def test_bad_readxl_sheetnames(self): with self.assertRaises(UserWarning) as e: _ = xl.readxl(fn='./testbook.xlsx', ws='not-a-sheet') self.assertRaises( 'pylightxl - Sheetname ({}) is not in the workbook.'.format( 'not-a-sheet'), e)
def test_bad_readxl_extension(self): with self.assertRaises(UserWarning) as e: _ = xl.readxl(fn='./input.csv') self.assertRaises( 'pylightxl - Incorrect Excel file extension ({}). ' 'File extension supported: .xlsx .xlsm'.format('csv'), e)
from unittest import TestCase import os, sys # 3rd party lib support if sys.version_info[0] == 3: from pathlib import Path else: from pathlib2 import Path # local lib imports from pylightxl import pylightxl as xl if 'test' in os.listdir('.'): # running from top level os.chdir('./test') DB = xl.readxl('./merged_cells.xlsx') class TestMergedCells(TestCase): def common_tst(self, sheet_name, merged_cells): sheet = DB.ws(sheet_name) merged_cells_reported = sheet.merged_cells self.assertEqual(len(merged_cells), len(merged_cells_reported)) self.assertEqual(set(merged_cells_reported), set(merged_cells.keys())) for k, v in merged_cells.items(): rlo, rhi, clo, chi = k self.assertEqual(sheet.index(rlo, clo), "{}-{}".format(sheet_name, v)) def test_ws1(self): sheet_name = "Sheet1"