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_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_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_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_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_repr(self): with open_excel(visible=False) as wb: assert re.match(r'<larray.inout.xw_excel.Workbook \[Book\d+]>', repr(wb))