def test_to_array():
sheet = ipysheet.sheet(rows=5, columns=4)
ipysheet.cell(0, 0, value=True)
ipysheet.row(1, value=[2, 34, 543, 23])
ipysheet.column(3, value=[1.2, 1.3, 1.4, 1.5, 1.6])
arr = ipysheet.to_array(sheet)
expected = np.array([[True, None, None, 1.2], [2, 34, 543, 1.3],
[None, None, None, 1.4], [None, None, None, 1.5],
[None, None, None, 1.6]])
assert np.all(arr == expected)
def function_to_sheet(func, name, values):
sh = ipysheet.sheet(rows=len(name), columns=len(values), row_headers=name, column_headers=False)
x = ipysheet.row(0, values, numeric_format='0.00000')
y = ipysheet.row(1, [func(v) for v in values], numeric_format='0.00000')
# x = ipysheet.row(0, values)
# y = ipysheet.row(1, [func(v) for v in values])
@ipysheet.calculation(inputs=[x], output=y, initial_calculation=True)
def calculate(a):
return [func(i) for i in a]
return sh
def add_data(self, data):
if self._main_data is None:
self._main_data = data
else:
raise Exception('data is already set')
cell_data = []
for i in self._main_data.main_components:
cell_data.append(list(self._main_data.get_data(i)))
cell_data = [list(col) for col in zip(*cell_data)]
assert max(map(len, cell_data)) == min(map(len, cell_data))
num_row, num_col = len(cell_data), len(cell_data[0])
self._sheet.columns = num_col
self._sheet.rows = num_row
self.rows = []
for i in range(len(cell_data)):
self.rows.append(ipysheet.row(i, cell_data[i]))
return True
def _add_data(self):
cell_data = []
for i in self._main_data.main_components:
cell_data.append(list(self._main_data.get_data(i)))
cell_data = [list(col) for col in zip(*cell_data)]
assert max(map(len, cell_data)) == min(map(len, cell_data))
num_row, num_col = len(cell_data), len(cell_data[0])
self._sheet.columns = num_col
self._sheet.rows = num_row
self.rows = []
for i in range(len(cell_data)):
self.rows.append(ipysheet.row(i, cell_data[i]))
return True
def test_cell_values():
cell = ipysheet.cell(0, 0, value=True)
assert cell.value is True
assert cell.type == 'checkbox'
cell = ipysheet.cell(0, 0, value=1.2)
assert cell.value == 1.2
assert cell.type == 'numeric'
cell = ipysheet.cell(0, 0, value=1)
assert cell.value == 1
assert cell.type == 'numeric'
cell = ipysheet.Cell(value='1.2')
assert cell.value == '1.2'
assert cell.type is None
cell = ipysheet.row(0, [True, False])
assert cell.value == [True, False]
assert cell.type == 'checkbox'
cell = ipysheet.row(0, [0, 1.2])
assert cell.value == [0, 1.2]
assert cell.type == 'numeric'
cell = ipysheet.row(0, [0, 1])
assert cell.value == [0, 1]
assert cell.type == 'numeric'
cell = ipysheet.row(0, ['a', 'b'])
assert cell.value == ['a', 'b']
assert cell.type == 'text'
cell = ipysheet.row(0, [True, 0])
assert cell.type == 'numeric'
cell = ipysheet.row(0, [True, 'bla'])
assert cell.type is None
cell = ipysheet.cell(0, 0, choice=['a', 'b'])
assert cell.type == 'dropdown'
def test_row_and_column():
ipysheet.sheet(rows=3, columns=4)
ipysheet.row(0, [0, 1, 2, 3])
ipysheet.row(0, [0, 1, 2])
ipysheet.row(0, [0, 1, 2], column_end=2)
ipysheet.row(0, [0, 1, 2], column_start=1)
with pytest.raises(ValueError):
ipysheet.row(0, [0, 1, 2, 4, 5])
with pytest.raises(ValueError):
ipysheet.row(0, [0, 1], column_end=3)
with pytest.raises(ValueError):
ipysheet.row(0, [0, 1, 2, 4], column_start=1)
row = ipysheet.row(0, [0, 1, 2, 3])
with pytest.raises(ValueError):
row.value = [0, 1, 2]
with pytest.raises(ValueError):
row.value = 1
row.value = [0, 1, 2, 4]
assert row.value == [0, 1, 2, 4]
ipysheet.column(0, [0, 1, 2])
ipysheet.column(0, [0, 1])
ipysheet.column(0, [0, 1], row_end=1)
ipysheet.column(0, [0, 1], row_start=1)
with pytest.raises(ValueError):
ipysheet.column(0, [0, 1, 2, 3])
with pytest.raises(ValueError):
ipysheet.column(0, [0, 1], row_end=0)
with pytest.raises(ValueError):
ipysheet.column(0, [0, 1, 2, 4], row_start=1)
col = ipysheet.column(0, [0, 1, 2])
with pytest.raises(ValueError):
col.value = [0, 1]
with pytest.raises(ValueError):
col.value = 1
col.value = [0, 1, 3]
assert col.value == [0, 1, 3]
def test_to_dataframe():
sheet = ipysheet.sheet(rows=5, columns=4)
ipysheet.cell(0, 0, value=True)
ipysheet.row(1, value=[2, 34, 543, 23])
ipysheet.column(3, value=[1.2, 1.3, 1.4, 1.5, 1.6])
df = ipysheet.to_dataframe(sheet)
assert np.all(df['A'].tolist() == [True, 2, None, None, None])
assert np.all(df['B'].tolist() == [None, 34, None, None, None])
assert np.all(df['C'].tolist() == [None, 543, None, None, None])
assert np.all(df['D'].tolist() == [1.2, 1.3, 1.4, 1.5, 1.6])
sheet = ipysheet.sheet(rows=4,
columns=4,
column_headers=['c0', 'c1', 'c2', 'c3'],
row_headers=['r0', 'r1', 'r2', 'r3'])
ipysheet.cell_range([
[2, 34, 543, 23],
[1, 1, 1, 1],
[2, 2, 222, 22],
[2, 0, 111, 11],
],
row_start=0,
column_start=0,
transpose=True)
df = ipysheet.to_dataframe(sheet)
assert np.all(df['c0'].tolist() == [2, 34, 543, 23])
assert np.all(df['c1'].tolist() == [1, 1, 1, 1])
assert np.all(df['c2'].tolist() == [2, 2, 222, 22])
assert np.all(df['c3'].tolist() == [2, 0, 111, 11])
sheet = ipysheet.sheet(rows=4,
columns=4,
column_headers=['t0', 't1', 't2', 't3'])
ipysheet.cell_range([
[2, 34, 543, 23],
[1, 1, 1, 1],
[2, 2, 222, 22],
[2, 0, 111, 11],
],
row_start=0,
column_start=0,
transpose=False)
df = ipysheet.to_dataframe(sheet)
assert np.all(df['t0'].tolist() == [2, 1, 2, 2])
assert np.all(df['t1'].tolist() == [34, 1, 2, 0])
assert np.all(df['t2'].tolist() == [543, 1, 222, 111])
assert np.all(df['t3'].tolist() == [23, 1, 22, 11])
sheet = ipysheet.sheet(rows=0, columns=0)
df = ipysheet.to_dataframe(sheet)
assert np.all(df == pd.DataFrame())
sheet = ipysheet.sheet(rows=4, columns=1)
ipysheet.column(0,
['2019/02/28', '2019/02/27', '2019/02/26', '2019/02/25'],
type='date')
df = ipysheet.to_dataframe(sheet)
assert [_format_date(x) for x in df['A'].tolist()
] == ['2019/02/28', '2019/02/27', '2019/02/26', '2019/02/25']