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"
