Spreadsheet widget for the Jupyter Notebook¶
Installation¶
With conda:
$ conda install -c conda-forge ipysheet
With pip:
$ pip install ipysheet
To make it work for Jupyter lab:
$ jupyter labextension install ipysheet
If you have notebook 5.2 or below, you also need to execute:
$ jupyter nbextension enable --py --sys-prefix ipysheet
Getting started¶
Although ipysheet contains an object oriented interface, we recomment using the “state machine” based interface, similar to matplotlib’s pyplot/pylab interface. Comparible to matplotlib pylab interface, this interface keeps track of the current sheet. Using the cell function, Cell widgets are added to the current sheet.
Importing ipysheet and invoking the sheet function will create the default spreadsheet widget. The function returns a Sheet instance, leaving that expression as a last statement of a code cell will display it, otherwise use display(sheet)
.
Note that this documentation is a Jupyter notebook, and you can try it out directly on Binder:
[20]:
import ipysheet
sheet = ipysheet.sheet()
sheet
Using the cell function, we can create Cell widgets that are directly added to the current sheet.
[21]:
sheet = ipysheet.sheet(rows=3, columns=4)
cell1 = ipysheet.cell(0, 0, 'Hello')
cell2 = ipysheet.cell(2, 0, 'World')
cell_value = ipysheet.cell(2,2, 42.)
sheet
Events¶
Using link or observe we can link widgets together, or attach event handlers
[22]:
import ipywidgets as widgets
sheet = ipysheet.sheet(rows=3, columns=2, column_headers=False, row_headers=False)
cell_a = ipysheet.cell(0, 1, 1, label_left='a')
cell_b = ipysheet.cell(1, 1, 2, label_left='b')
cell_sum = ipysheet.cell(2, 1, 3, label_left='sum', read_only=True)
# create a slider linked to cell a
slider = widgets.FloatSlider(min=-10, max=10, description='a')
widgets.jslink((cell_a, 'value'), (slider, 'value'))
# changes in a or b should trigger this function
def calculate(change):
cell_sum.value = cell_a.value + cell_b.value
cell_a.observe(calculate, 'value')
cell_b.observe(calculate, 'value')
widgets.VBox([sheet, slider])
Cell ranges¶
Instead of referring to a single cell, we can also refer to cell ranges, rows and columns.
[23]:
sheet = ipysheet.sheet(rows=5, columns=4)
row = ipysheet.row(0, [0, 1, 2, 3], background_color="red")
column = ipysheet.column(1, ["a", "b", "c", "d"], row_start=1, background_color="green")
cells = ipysheet.cell_range([["hi", "ola"], ["ciao", "bonjour"], ["hallo", "guten tag"]],
row_start=1, column_start=2, background_color="yellow")
sheet
Calculations¶
Since this is such a common pattern, a helper decorator calculation is provided, shortening the above code considerably.
[24]:
import ipywidgets as widgets
sheet = ipysheet.sheet(rows=3, columns=2, column_headers=False, row_headers=False)
cell_a = ipysheet.cell(0, 1, 1, label_left='a')
cell_b = ipysheet.cell(1, 1, 2, label_left='b')
cell_sum = ipysheet.cell(2, 1, 3, label_left='sum', read_only=True)
# create a slider linked to cell a
slider = widgets.FloatSlider(min=-10, max=10, description='a')
widgets.jslink((cell_a, 'value'), (slider, 'value'))
@ipysheet.calculation(inputs=[cell_a, cell_b], output=cell_sum)
def calculate(a, b):
return a + b
widgets.VBox([sheet, slider])
Renderers¶
ipysheet is build on Handsontable, which allows custom renderers, which we also support.
[25]:
jscode_renderer_negative = """function (value) {
return {
backgroundColor: value < 0 ? 'red' : 'green'
};
}
"""
ipysheet.renderer(code=jscode_renderer_negative, name='negative');
[26]:
import random
s = ipysheet.sheet(rows=3, columns=4)
data = [[random.randint(-10, 10) for j in range(4)] for j in range(3)]
ipysheet.cell_range(data, renderer='negative')
s
If flexx is installed, Python code can be transpiled to JavaScript at runtime.
[27]:
def renderer_negative(value):
return {
'backgroundColor': 'orange' if value < 0 else ''
}
ipysheet.renderer(code=renderer_negative, name='negative_transpiled');
[28]:
import random
s = ipysheet.sheet(rows=3, columns=4)
data = [[random.randint(-10, 10) for j in range(4)] for j in range(3)]
ipysheet.cell_range(data, renderer='negative_transpiled')
s