def test_setitem(self):
with open_excel(visible=False) as wb:
# sheet did not exist, str value
wb['sheet1'] = 'sheet1 content'
wb['sheet2'] = 'sheet2 content'
assert wb.sheet_names() == ['sheet1', 'sheet2']
# sheet did exist, str value
wb['sheet2'] = 'sheet2 content v2'
assert wb.sheet_names() == ['sheet1', 'sheet2']
assert wb['sheet2']['A1'].value == 'sheet2 content v2'
# sheet did not exist, Sheet value
wb['sheet3'] = wb['sheet1']
assert wb.sheet_names() == ['sheet1', 'sheet2', 'sheet3']
assert wb['sheet3']['A1'].value == 'sheet1 content'
# sheet did exist, Sheet value
wb['sheet2'] = wb['sheet1']
assert wb.sheet_names() == ['sheet1', 'sheet2', 'sheet3']
assert wb['sheet2']['A1'].value == 'sheet1 content'
with open_excel(visible=False, app="new") as wb2:
assert wb.app != wb2.app
with pytest.raises(ValueError) as e_info:
wb2['sheet1'] = wb['sheet1']
assert e_info.value.args[0] == "cannot copy a sheet from one instance of Excel to another"
# group key
arr = ndtest((3, 3))
for label in arr.b:
wb[label] = arr[label].dump()
assert arr[label].equals(wb[label].load())
def test_setitem(self):
with open_excel(visible=False) as wb:
# sheet did not exist, str value
wb['sheet1'] = 'sheet1 content'
wb['sheet2'] = 'sheet2 content'
assert wb.sheet_names() == ['sheet1', 'sheet2']
# sheet did exist, str value
wb['sheet2'] = 'sheet2 content v2'
assert wb.sheet_names() == ['sheet1', 'sheet2']
assert wb['sheet2']['A1'].value == 'sheet2 content v2'
# sheet did not exist, Sheet value
wb['sheet3'] = wb['sheet1']
assert wb.sheet_names() == ['sheet1', 'sheet2', 'sheet3']
assert wb['sheet3']['A1'].value == 'sheet1 content'
# sheet did exist, Sheet value
wb['sheet2'] = wb['sheet1']
assert wb.sheet_names() == ['sheet1', 'sheet2', 'sheet3']
assert wb['sheet2']['A1'].value == 'sheet1 content'
with open_excel(visible=False, app="new") as wb2:
with pytest.raises(ValueError) as e_info:
wb2['sheet1'] = wb['sheet1']
assert e_info.value.args[
0] == "cannot copy a sheet from one instance of Excel to another"
def test_setitem(self):
with open_excel(visible=False) as wb:
# sheet did not exist, str value
wb['sheet1'] = 'sheet1 content'
wb['sheet2'] = 'sheet2 content'
assert wb.sheet_names() == ['sheet1', 'sheet2']
# sheet did exist, str value
wb['sheet2'] = 'sheet2 content v2'
assert wb.sheet_names() == ['sheet1', 'sheet2']
assert wb['sheet2']['A1'].value == 'sheet2 content v2'
# sheet did not exist, Sheet value
wb['sheet3'] = wb['sheet1']
assert wb.sheet_names() == ['sheet1', 'sheet2', 'sheet3']
assert wb['sheet3']['A1'].value == 'sheet1 content'
# sheet did exist, Sheet value
wb['sheet2'] = wb['sheet1']
assert wb.sheet_names() == ['sheet1', 'sheet2', 'sheet3']
assert wb['sheet2']['A1'].value == 'sheet1 content'
with open_excel(visible=False, app="new") as wb2:
with pytest.raises(ValueError) as e_info:
wb2['sheet1'] = wb['sheet1']
assert e_info.value.args[
0] == "cannot copy a sheet from one instance of Excel to another"
# group key
arr = ndtest((3, 3))
for label in arr.b:
wb[label] = arr[label].dump()
assert arr[label].equals(wb[label].load())
def test_scalar_convert(self):
with open_excel(visible=False) as wb:
sheet = wb[0]
# set a few values
sheet['A1'] = 1
rng = sheet['A1']
assert int(rng) == 1
assert float(rng) == 1.0
assert rng.__index__() == 1
sheet['A2'] = 1.0
rng = sheet['A2']
assert int(rng) == 1
assert float(rng) == 1.0
# Excel stores everything as float so we cannot really make the difference between 1 and 1.0
assert rng.__index__() == 1
sheet['A3'] = 1.5
rng = sheet['A3']
assert int(rng) == 1
assert float(rng) == 1.5
with pytest.raises(
TypeError,
match=
"only integer scalars can be converted to a scalar index"):
rng.__index__()
def test_repr(self):
with open_excel(visible=False) as wb:
sheet = wb[0]
arr1 = ndtest((2, 3))
sheet['A1'] = arr1
res = repr(sheet['A1:C2'])
assert res == """\
def test_repr(self):
with open_excel(visible=False) as wb:
sheet = wb[0]
arr1 = ndtest((2, 3))
sheet['A1'] = arr1
res = repr(sheet['A1:C2'])
assert res == """\
def test_aggregate(self):
with open_excel(visible=False) as wb:
sheet = wb[0]
arr1 = ndtest((2, 3))
# no header so that we have an uniform dtype for the whole sheet
sheet['A1'] = arr1
res = sheet['A1:C2'].sum()
assert res == 15
def test_aggregate(self):
with open_excel(visible=False) as wb:
sheet = wb[0]
arr1 = ndtest((2, 3))
# no header so that we have an uniform dtype for the whole sheet
sheet['A1'] = arr1
res = sheet['A1:C2'].sum()
assert res == 15
def test_aslarray(self):
with open_excel(visible=False) as wb:
sheet = wb[0]
arr1 = ndtest([Axis(2), Axis(3)])
# no header so that we have an uniform dtype for the whole sheet
sheet['A1'] = arr1
res1 = aslarray(sheet['A1:C2'])
assert res1.equals(arr1)
assert res1.dtype == arr1.dtype
def test_aslarray(self):
with open_excel(visible=False) as wb:
sheet = wb[0]
arr1 = ndrange((2, 3))
# no header so that we have an uniform dtype for the whole sheet
sheet['A1'] = arr1
res1 = aslarray(sheet['A1:C2'])
assert larray_equal(res1, arr1)
assert res1.dtype == arr1.dtype
def test_aslarray(self):
with open_excel(visible=False) as wb:
sheet = wb[0]
arr1 = ndtest([Axis(2), Axis(3)])
# no header so that we have an uniform dtype for the whole sheet
sheet['A1'] = arr1
res1 = aslarray(sheet['A1:C2'])
assert res1.equals(arr1)
assert res1.dtype == arr1.dtype
def test_asarray(self):
with open_excel(visible=False) as wb:
sheet = wb[0]
arr1 = ndtest((2, 3))
# no header so that we have an uniform dtype for the whole sheet
sheet['A1'] = arr1
res1 = np.asarray(sheet)
assert np.array_equal(res1, arr1.data)
assert res1.dtype == arr1.dtype
def test_asarray(self):
with open_excel(visible=False) as wb:
sheet = wb[0]
arr1 = ndtest((2, 3))
# no header so that we have an uniform dtype for the whole sheet
sheet['A1'] = arr1
res1 = np.asarray(sheet['A1:C2'])
assert np.array_equal(res1, arr1.data)
assert res1.dtype == arr1.dtype
def test_open_excel(self):
# not using context manager because we call .quit manually
wb1 = open_excel(visible=False)
app1 = wb1.app
wb1.close()
# anything using wb1 will fail
with pytest.raises(Exception):
wb1.sheet_names()
wb2 = open_excel(visible=False)
app2 = wb2.app
assert app1 == app2 == xw_excel.global_app
# this effectively close all workbooks but leaves the instance intact (this is probably due to us keeping a
# reference to it).
app1.quit()
# anything using wb2 will fail
with pytest.raises(Exception):
wb2.sheet_names()
# in any case, this should work
with open_excel(visible=False) as wb:
wb['test'] = 'content'
def test_open_excel(self):
# not using context manager because we call .quit manually
wb1 = open_excel(visible=False)
app1 = wb1.app
wb1.close()
# anything using wb1 will fail
with pytest.raises(Exception):
wb1.sheet_names()
wb2 = open_excel(visible=False)
app2 = wb2.app
assert app1 == app2 == excel.global_app
# this effectively close all workbooks but leaves the instance intact (this is probably due to us keeping a
# reference to it).
app1.quit()
# anything using wb2 will fail
with pytest.raises(Exception):
wb2.sheet_names()
# in any case, this should work
with open_excel(visible=False) as wb:
wb['test'] = 'content'
def test_links(self):
data_dir = TEST_DATA_PATH / 'excel_with_links'
fpath1 = data_dir / 'BookA.xlsx'
fpath2 = data_dir / 'BookB.xlsx'
fpath3 = data_dir / 'BookC.xlsx'
assert read_excel(fpath1)['link to cell with link to other workbook',
'value from link'] == 4
assert read_excel(fpath2)['cell with link to other workbook',
'formula value'] == 4
assert read_excel(fpath3).i[0, 0] == 3
with open_excel(fpath1) as a, open_excel(fpath2) as b, open_excel(
fpath3) as c:
c[0]['B2'] = 41
a.save()
b.save()
c.save()
assert read_excel(fpath1)['link to cell with link to other workbook',
'value from link'] == 42
assert read_excel(fpath2)['cell with link to other workbook',
'formula value'] == 42
assert read_excel(fpath3).i[0, 0] == 41
with open_excel(fpath1) as a, open_excel(fpath2) as b, open_excel(
fpath3) as c:
c[0]['B2'] = 3
a.save()
b.save()
c.save()
assert read_excel(fpath1)['link to cell with link to other workbook',
'value from link'] == 4
assert read_excel(fpath2)['cell with link to other workbook',
'formula value'] == 4
assert read_excel(fpath3).i[0, 0] == 3
def test_open_excel(self):
import pywintypes
# not using context manager because we call .quit manually
wb1 = open_excel(visible=False)
app1 = wb1.app
# close workbook but leave app instance open (anything using wb1 will fail now)
wb1.close()
# disable faulthandler to avoid annoying "Windows fatal exception" messages in the console
# See https://github.com/pywinauto/pywinauto/issues/858
# and https://stackoverflow.com/questions/57523762/pytest-windows-fatal-exception-code-0x8001010d
faulthandler_enabled = faulthandler.is_enabled()
if faulthandler_enabled:
faulthandler.disable()
with pytest.raises(pywintypes.com_error):
wb1.sheet_names()
if faulthandler_enabled:
faulthandler.enable()
wb2 = open_excel(visible=False)
app2 = wb2.app
assert app1 == app2 == xw_excel.global_app
# this effectively close all workbooks but leaves the instance intact (this is probably due to us keeping a
# reference to it).
app1.quit()
# anything using wb2 will fail
if faulthandler_enabled:
faulthandler.disable()
with pytest.raises(pywintypes.com_error):
wb2.sheet_names()
if faulthandler_enabled:
faulthandler.enable()
# in any case, this should work
with open_excel(visible=False) as wb:
wb['test'] = 'content'
def test_getitem(self):
with open_excel(visible=False) as wb:
sheet = wb[0]
assert isinstance(sheet, xw_excel.Sheet)
# this might not be true on non-English locale
assert sheet.name == 'Sheet1'
# this might not work on non-English locale
sheet = wb['Sheet1']
assert isinstance(sheet, xw_excel.Sheet)
assert sheet.name == 'Sheet1'
with pytest.raises(KeyError) as e_info:
wb['this_sheet_does_not_exist']
assert e_info.value.args[0] == "Workbook has no sheet named this_sheet_does_not_exist"
def test_getitem(self):
with open_excel(visible=False) as wb:
sheet = wb[0]
assert isinstance(sheet, excel.Sheet)
# this might not be true on non-English locale
assert sheet.name == 'Sheet1'
# this might not work on non-English locale
sheet = wb['Sheet1']
assert isinstance(sheet, excel.Sheet)
assert sheet.name == 'Sheet1'
with pytest.raises(KeyError) as e_info:
wb['this_sheet_does_not_exist']
assert e_info.value.args[
0] == "Workbook has no sheet named this_sheet_does_not_exist"
def to_excel(self, raw_data, axes_names, vlabels, hlabels):
try:
data = self._from_selection(raw_data, axes_names, vlabels, hlabels)
if data is None:
return
self._to_excel(data)
except NotImplementedError:
data = self.selection_to_chain(raw_data, axes_names, vlabels, hlabels)
if data is None:
return
# convert (row) generators to lists then array
# TODO: the conversion to array is currently necessary even though xlwings will translate it back to a list
# anyway. The problem is that our lists contains numpy types and especially np.str_ crashes xlwings.
# unsure how we should fix this properly: in xlwings, or change _selection_data to return only standard
# Python types.
array = np.array([list(r) for r in data])
wb = la.open_excel()
wb[0]['A1'] = array
def test_array_method(self):
with open_excel(visible=False) as wb:
sheet = wb[0]
# normal test array
arr1 = ndtest((2, 3))
sheet['A1'] = arr1.dump()
res1 = sheet.array('B2:D3', 'A2:A3', 'B1:D1', names=['a', 'b'])
assert arr1.equals(res1)
# array with int labels
arr2 = ndtest('0..1;0..2')
sheet['A1'] = arr2.dump()
res2 = sheet.array('B2:D3', 'A2:A3', 'B1:D1')
# larray_equal passes even if the labels are floats...
assert arr2.equals(res2)
# so we check the dtype explicitly
assert res2.axes[0].labels.dtype == arr2.axes[0].labels.dtype
assert res2.axes[1].labels.dtype == arr2.axes[1].labels.dtype
def test_array_method(self):
with open_excel(visible=False) as wb:
sheet = wb[0]
# normal test array
arr1 = ndtest((2, 3))
sheet['A1'] = arr1.dump()
res1 = sheet.array('B2:D3', 'A2:A3', 'B1:D1', names=['a', 'b'])
assert arr1.equals(res1)
# array with int labels
arr2 = ndtest('0..1;0..2')
sheet['A1'] = arr2.dump()
res2 = sheet.array('B2:D3', 'A2:A3', 'B1:D1')
# larray_equal passes even if the labels are floats...
assert arr2.equals(res2)
# so we check the dtype explicitly
assert res2.axes[0].labels.dtype == arr2.axes[0].labels.dtype
assert res2.axes[1].labels.dtype == arr2.axes[1].labels.dtype
def test_get_and_set_item(self):
arr = ndtest((2, 3))
with open_excel(visible=False) as wb:
sheet = wb[0]
# set a few values
sheet['A1'] = 1.5
sheet['A2'] = 2
sheet['A3'] = True
sheet['A4'] = 'toto'
# array without header
sheet['A5'] = arr
# array with header
sheet['A8'] = arr.dump()
# object array with a *numpy* NaN (dc2019 infamous 65535 bug)
obj_arr = ndtest((2, 3)).astype(object)
obj_arr['a0', 'b1'] = np.float64('nan')
assert type(obj_arr['a0', 'b1']) is np.float64
obj_arr_dump = obj_arr.dump()
# [['a\\b', 'b0', 'b1', 'b2'], ['a0', 0, nan, 2], ['a1', 3, 4, 5]]
# float and *not* np.float64, otherwise it gets converted to 65535 when written to Excel
assert type(obj_arr_dump[1][2]) is float
sheet['A12'] = obj_arr_dump
# read them back
assert sheet['A1'].value == 1.5
assert sheet['A2'].value == 2
assert sheet['A3'].value is True
assert sheet['A4'].value == 'toto'
# array without header
assert np.array_equal(sheet['A5:C6'].value, arr.data)
# array with header
assert sheet['A8:D10'].load().equals(arr)
assert sheet['A12:D14'].load().equals(obj_arr, nans_equal=True)
def test_get_and_set_item(self):
arr = ndtest((2, 3))
with open_excel(visible=False) as wb:
sheet = wb[0]
# set a few values
sheet['A1'] = 1.5
sheet['A2'] = 2
sheet['A3'] = True
sheet['A4'] = 'toto'
# array without header
sheet['A5'] = arr
# array with header
sheet['A8'] = arr.dump()
# read them back
assert sheet['A1'].value == 1.5
assert sheet['A2'].value == 2
assert sheet['A3'].value == True
assert sheet['A4'].value == 'toto'
# array without header
assert np.array_equal(sheet['A5:C6'].value, arr.data)
# array with header
assert arr.equals(sheet['A8:D10'].load())
def test_get_and_set_item(self):
arr = ndtest((2, 3))
with open_excel(visible=False) as wb:
sheet = wb[0]
# set a few values
sheet['A1'] = 1.5
sheet['A2'] = 2
sheet['A3'] = True
sheet['A4'] = 'toto'
# array without header
sheet['A5'] = arr
# array with header
sheet['A8'] = arr.dump()
# read them back
assert sheet['A1'].value == 1.5
assert sheet['A2'].value == 2
assert sheet['A3'].value == True
assert sheet['A4'].value == 'toto'
# array without header
assert np.array_equal(sheet['A5:C6'].value, arr.data)
# array with header
assert arr.equals(sheet['A8:D10'].load())
def test_scalar_convert(self):
with open_excel(visible=False) as wb:
sheet = wb[0]
# set a few values
sheet['A1'] = 1
rng = sheet['A1']
assert int(rng) == 1
assert float(rng) == 1.0
assert rng.__index__() == 1
sheet['A2'] = 1.0
rng = sheet['A2']
assert int(rng) == 1
assert float(rng) == 1.0
# Excel stores everything as float so we cannot really make the difference between 1 and 1.0
assert rng.__index__() == 1
sheet['A3'] = 1.5
rng = sheet['A3']
assert int(rng) == 1
assert float(rng) == 1.5
with pytest.raises(TypeError) as e_info:
rng.__index__()
assert e_info.value.args[0] == "only integer scalars can be converted to a scalar index"
def test_repr(self):
with open_excel(visible=False) as wb:
sheet = wb[0]
assert re.match(r'<larray.inout.xw_excel.Sheet \[Book\d+]Sheet1>',
repr(sheet))
def test_repr(self):
with open_excel(visible=False) as wb:
sheet = wb[0]
assert re.match('<larray.inout.xw_excel.Sheet \[Book\d+\]Sheet1>', repr(sheet))
def test_rename(self):
with open_excel(visible=False) as wb:
wb['sheet_name'] = 'sheet content'
wb['sheet_name'].name = 'renamed'
assert wb.sheet_names() == ['renamed']
def test_delitem(self):
with open_excel(visible=False) as wb:
wb['sheet1'] = 'sheet1 content'
wb['sheet2'] = 'sheet2 content'
del wb['sheet1']
assert wb.sheet_names() == ['sheet2']
def generate_example_files(csv=True, excel=True, hdf5=True):
from larray_eurostat import eurostat_get
def prepare_eurostat_data(dataset_name, countries):
arr = eurostat_get(dataset_name)[X.unit['NR'], X.age['TOTAL'],
X.sex['M,F']]
arr = arr[X.time[::-1]][2013:2017]
arr = arr.rename('sex', 'gender')
arr = arr.set_labels(gender='Male,Female')
arr = arr.rename('geo', 'country')
country_codes = list(countries.keys())
country_names = list(countries.values())
if dataset_name == 'migr_imm1ctz':
# example of an array with ambiguous axes
arr = arr['COMPLET', X.citizen[country_codes],
X.country[country_codes]].astype(int)
arr = arr.rename('citizen', 'citizenship')
arr = arr.set_labels('citizenship', country_names)
arr = arr.set_labels('country', country_names)
arr = arr.transpose('country', 'citizenship', 'gender', 'time')
else:
arr = arr[country_codes].astype(int)
arr = arr.set_labels('country', country_names)
arr = arr.transpose('country', 'gender', 'time')
return arr
countries = {'BE': 'Belgium', 'FR': 'France', 'DE': 'Germany'}
benelux = {'BE': 'Belgium', 'LU': 'Luxembourg', 'NL': 'Netherlands'}
# Arrays
population = prepare_eurostat_data('demo_pjan', countries)
population.meta.title = 'Population on 1 January by age and sex'
population.meta.source = 'table demo_pjan from Eurostat'
# ----
population_benelux = prepare_eurostat_data('demo_pjan', benelux)
population_benelux.meta.title = 'Population on 1 January by age and sex (Benelux)'
population_benelux.meta.source = 'table demo_pjan from Eurostat'
# ----
population_5_countries = population.extend(
'country', population_benelux[['Luxembourg', 'Netherlands']])
population_5_countries.meta.title = 'Population on 1 January by age and sex (Benelux + France + Germany)'
population_5_countries.meta.source = 'table demo_pjan from Eurostat'
# ----
births = prepare_eurostat_data('demo_fasec', countries)
births.meta.title = "Live births by mother's age and newborn's sex"
births.meta.source = 'table demo_fasec from Eurostat'
# ----
deaths = prepare_eurostat_data('demo_magec', countries)
deaths.meta.title = 'Deaths by age and sex'
deaths.meta.source = 'table demo_magec from Eurostat'
# ----
immigration = prepare_eurostat_data('migr_imm1ctz', benelux)
immigration.meta.title = 'Immigration by age group, sex and citizenship'
immigration.meta.source = 'table migr_imm1ctz from Eurostat'
# Groups
even_years = population.time[2014::2] >> 'even_years'
odd_years = population.time[2013::2] >> 'odd_years'
# Session
ses = Session({
'country': population.country,
'country_benelux': immigration.country,
'citizenship': immigration.citizenship,
'gender': population.gender,
'time': population.time,
'even_years': even_years,
'odd_years': odd_years,
'population': population,
'population_benelux': population_benelux,
'population_5_countries': population_5_countries,
'births': births,
'deaths': deaths,
'immigration': immigration
})
ses.meta.title = 'Demographic datasets for a small selection of countries in Europe'
ses.meta.source = 'demo_jpan, demo_fasec, demo_magec and migr_imm1ctz tables from Eurostat'
# EUROSTAT DATASET
if csv:
ses.save(os.path.join(DATA_DIR, 'demography_eurostat'))
if excel:
ses.save(os.path.join(DATA_DIR, 'demography_eurostat.xlsx'))
if hdf5:
ses.save(os.path.join(DATA_DIR, 'demography_eurostat.h5'))
# EXAMPLE FILES
years = population.time[2013:2015]
population = population[years]
population_narrow = population['Belgium,France'].sum('gender')
births = births[years]
deaths = deaths[years]
immigration = immigration[years]
# Dataframes (for testing missing axis/values)
df_missing_axis_name = population.to_frame(fold_last_axis_name=False)
df_missing_values = population.to_frame(fold_last_axis_name=True)
df_missing_values.drop([('France', 'Male'), ('Germany', 'Female')],
inplace=True)
if csv:
examples_dir = os.path.join(DATA_DIR, 'examples')
population.to_csv(os.path.join(examples_dir, 'population.csv'))
births.to_csv(os.path.join(examples_dir, 'births.csv'))
deaths.to_csv(os.path.join(examples_dir, 'deaths.csv'))
immigration.to_csv(os.path.join(examples_dir, 'immigration.csv'))
df_missing_axis_name.to_csv(os.path.join(
examples_dir, 'population_missing_axis_name.csv'),
sep=',',
na_rep='')
df_missing_values.to_csv(os.path.join(examples_dir,
'population_missing_values.csv'),
sep=',',
na_rep='')
population_narrow.to_csv(os.path.join(examples_dir,
'population_narrow_format.csv'),
wide=False)
if excel:
with open_excel(os.path.join(DATA_DIR, 'examples.xlsx'),
overwrite_file=True) as wb:
wb['population'] = population.dump()
wb['births'] = births.dump()
wb['deaths'] = deaths.dump()
wb['immigration'] = immigration.dump()
wb['population_births_deaths'] = population.dump()
wb['population_births_deaths']['A9'] = births.dump()
wb['population_births_deaths']['A17'] = deaths.dump()
wb['population_missing_axis_name'] = ''
wb['population_missing_axis_name']['A1'].options(
).value = df_missing_axis_name
wb['population_missing_values'] = ''
wb['population_missing_values']['A1'].options(
).value = df_missing_values
# wb['population_narrow_format'] = population_narrow.dump(wide=False)
wb.save()
population_narrow.to_excel(os.path.join(DATA_DIR, 'examples.xlsx'),
'population_narrow_format',
wide=False)
Session({
'country': population.country,
'gender': population.gender,
'time': population.time,
'population': population
}).save(os.path.join(DATA_DIR, 'population_only.xlsx'))
Session({
'births': births,
'deaths': deaths
}).save(os.path.join(DATA_DIR, 'births_and_deaths.xlsx'))
if hdf5:
examples_h5_file = os.path.join(DATA_DIR, 'examples.h5')
population.to_hdf(examples_h5_file, 'population')
births.to_hdf(examples_h5_file, 'births')
deaths.to_hdf(examples_h5_file, 'deaths')
immigration.to_hdf(examples_h5_file, 'immigration')
def test_repr(self):
with open_excel(visible=False) as wb:
assert re.match('<larray.inout.xw_excel.Workbook \[Book\d+\]>', repr(wb))
def generate_tests_files():
tests = {
'1d': 3,
'2d': "a=1..3; b=b0,b1",
'2d_classic': "a=a0..a2;b=b0..b2",
'3d': "a=1..3; b=b0,b1; c=c0..c2",
'int_labels': "a=0..2; b=0..2; c=0..2",
'missing_values': "a=1..3; b=b0,b1; c=c0..c2",
'unsorted': "a=3..1; b=b1,b0; c=c2..c0",
'position': "a=1..3; b=b0,b1; c=c0..c2"
}
wb = open_excel(os.path.join(DATA_DIR, 'test.xlsx'), overwrite_file=True)
wb_narrow = open_excel(os.path.join(DATA_DIR, 'test_narrow.xlsx'),
overwrite_file=True)
for name, dim in tests.items():
arr = ndtest(dim)
if name == '2d_classic':
df = arr.to_frame(fold_last_axis_name=False)
# wide format
df.to_csv(os.path.join(DATA_DIR, f'test{name}.csv'),
sep=',',
na_rep='')
wb[name] = ''
wb[name]['A1'].options().value = df
# narrow format
df = arr.to_series(name='value')
df.to_csv(os.path.join(DATA_DIR, f'test{name}_narrow.csv'),
sep=',',
na_rep='',
header=True)
wb_narrow[name] = ''
wb_narrow[name]['A1'].options().value = df
elif name == 'missing_values':
df = arr.to_frame(fold_last_axis_name=True)
# wide format
df = df.drop([(2, 'b0'), (3, 'b1')])
df.to_csv(os.path.join(DATA_DIR, f'test{name}.csv'),
sep=',',
na_rep='')
wb[name] = ''
wb[name]['A1'].options().value = df
# narrow format
df = arr.to_series(name='value')
df = df.drop([(2, 'b0'), (2, 'b1', 'c1'), (3, 'b1')])
df.to_csv(os.path.join(DATA_DIR, f'test{name}_narrow.csv'),
sep=',',
na_rep='',
header=True)
wb_narrow[name] = ''
wb_narrow[name]['A1'].options().value = df
elif name == 'position':
# wide format
wb[name] = ''
wb[name]['D3'] = arr.dump()
# narrow format
wb_narrow[name] = ''
wb_narrow[name]['D3'] = arr.dump(wide=False)
else:
# wide format
arr.to_csv(os.path.join(DATA_DIR, f'test{name}.csv'))
wb[name] = arr.dump()
# narrow format
arr.to_csv(os.path.join(DATA_DIR, f'test{name}_narrow.csv'),
wide=False)
wb_narrow[name] = arr.dump(wide=False)
wb.save()
wb.close()
wb_narrow.save()
wb_narrow.close()
def test_delitem(self):
with open_excel(visible=False) as wb:
wb['sheet1'] = 'sheet1 content'
wb['sheet2'] = 'sheet2 content'
del wb['sheet1']
assert wb.sheet_names() == ['sheet2']
def test_repr(self):
with open_excel(visible=False) as wb:
assert re.match(r'<larray.inout.xw_excel.Workbook \[Book\d+]>',
repr(wb))
def test_rename(self):
with open_excel(visible=False) as wb:
wb['sheet_name'] = 'sheet content'
wb['sheet_name'].name = 'renamed'
assert wb.sheet_names() == ['renamed']
