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:

[1]:

import ipysheet
sheet = ipysheet.sheet()
sheet

Using the cell function, we can create Cell widgets that are directly added to the current sheet.

[2]:

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

Note: The examples below contain event handler written in Python that needs a running kernel, they will not work in the pure html documentation. They do work in binder!

[3]:

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.

[4]:

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.

[5]:

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.

[6]:

jscode_renderer_negative = """function (value) {
  return {
    backgroundColor: value < 0 ?  'red' : 'green'
  };
}
"""
ipysheet.renderer(code=jscode_renderer_negative, name='negative');

[7]:

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.

[8]:

def renderer_negative(value):
    return {
        'backgroundColor': 'orange' if value < 0 else ''
    }
ipysheet.renderer(code=renderer_negative, name='negative_transpiled');

[9]:

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

API reference¶

Note that everything is accessible from the ipysheet namespace. For example, you can do from ipysheet import sheet, from_dataframe

ipysheet.easy¶

Easy context-based interface for generating a sheet and cells.

Comparable to matplotlib pylab interface, this interface keeps track of the current sheet. Using the cell function, Cell widgets are added to the current sheet.

ipysheet.easy.sheet(key=None, rows=5, columns=5, column_width=None, row_headers=True, column_headers=True, stretch_headers='all', cls=<class 'ipysheet.sheet.Sheet'>, **kwargs)[source]¶

Creates a new Sheet instance or retrieves one registered with key, and sets this as the ‘current’.

If the key argument is given, and no sheet is created before with this key, it will be registered under this key. If this function is called again with the same key argument, that Sheet instance will be returned.

Parameters:

key (string) – If not used before, register the sheet under this key. If used before, return the previous Sheet instance registered with this key.
rows (int) – The number of rows in the sheet
columns (int) – The number of columns in the sheet
row_headers (bool, list) – Either a boolean specifying if row headers should be displayed or not, or a list of strings containing the row headers
column_headers (bool, list) – Either a boolean specifying if column headers should be displayed or not, or a list of strings containing the column headers

Returns:

The new Sheet widget, or if key is given, the previously created sheet registered with this key.

Example

>>> from ipysheet import sheet, current
>>>
>>> s1 = sheet('key1')
>>> s2 = sheet('key2')
>>>
>>> assert s2 is current()
>>> assert s1 is sheet('key1')
>>> assert s1 is current()

ipysheet.easy.current()[source]¶

Returns:	the current `Sheet` instance

ipysheet.easy.cell(row, column, value=0.0, type=None, color=None, background_color=None, font_style=None, font_weight=None, style=None, label_left=None, choice=None, read_only=False, numeric_format='0.000', date_format='YYYY/MM/DD', renderer=None, **kwargs)[source]¶

Adds a new Cell widget to the current Sheet

Parameters:

row (int) – Zero based row index where to put the cell in the sheet
column (int) – Zero based column index where to put the cell in the sheet
value (int, float, string, bool, Widget) – The value of the cell
type (string) – Type of cell, options are: text, numeric, checkbox, dropdown, numeric, date, widget. If type is None, the type is inferred from the type of the value being passed, numeric (float or int type), boolean (bool type), widget (any widget object), or else text. When choice is given the type will be assumed to be dropdown. The types refer (currently) to the handsontable types: https://handsontable.com/docs/6.2.2/demo-custom-renderers.html
color (string) – The text color in the cell
background_color (string) – The background color in the cell
read_only (bool) – Whether the cell is editable or not
numeric_format (string) – Numbers format
date_format (string) – Dates format
time_format (string) – Time format
renderer (string) – Renderer name to use for the cell

Returns:

The new Cell widget.

Example

>>> from ipysheet import sheet, cell
>>>
>>> s1 = sheet()
>>> cell(0, 0, 36.)            # The Cell type will be 'numeric'
>>> cell(1, 0, True)           # The Cell type will be 'checkbox'
>>> cell(0, 1, 'Hello World!') # The Cell type will be 'text'
>>> c = cell(1, 1, True)
>>> c.value = False            # Dynamically changing the cell value at row=1, column=1

ipysheet.easy.calculation(inputs, output, initial_calculation=True)[source]¶

A decorator that assigns to output cell a calculation depending on the inputs

Parameters:

inputs (list of widgets, or (widget, 'traitname') pairs) – List of all widget, whose values (default ‘value’, otherwise specified by ‘traitname’) are input of the function that is decorated
output (widget or (widget, 'traitname')) – The output of the decorator function will be assigned to output.value or output.<traitname>.
initial_calculation (bool) – When True the calculation will be done directly for the first time.

Example

>>> from ipywidgets import IntSlider
>>> from ipysheet import cell, calculation
>>>
>>> a = cell(0, 0, value=1)
>>> b = cell(1, 0, value=IntSlider(value=2))
>>> c = IntSlider(max=56)
>>> d = cell(3, 0, value=1)
>>>
>>> @calculation(inputs=[a, (b, 'value'), (c, 'max')], output=d)
>>> def add(a, b, c):
>>>     return a + b + c

ipysheet.easy.row(row, value, column_start=0, column_end=None, type=None, color=None, background_color=None, font_style=None, font_weight=None, style=None, choice=None, read_only=False, numeric_format='0.000', date_format='YYYY/MM/DD', renderer=None, **kwargs)[source]¶

Create a Cell widget, representing multiple cells in a sheet, in a horizontal row

Parameters:

row (int) – Zero based row index where to put the row in the sheet
value (list) – The list of cell values representing the row
column_start (int) – Which column the row will start, default 0.
column_end (int) – Which column the row will end, default is the last.
type (string) – Type of cell, options are: text, numeric, checkbox, dropdown, numeric, date, widget. If type is None, the type is inferred from the type of the value being passed, numeric (float or int type), boolean (bool type), widget (any widget object), or else text. When choice is given the type will be assumed to be dropdown. The types refer (currently) to the handsontable types: https://handsontable.com/docs/6.2.2/demo-custom-renderers.html
color (string) – The text color in the cell
background_color (string) – The background color in the cell
read_only (bool) – Whether the cell is editable or not
numeric_format (string) – Numbers format
date_format (string) – Dates format
time_format (string) – Time format
renderer (string) – Renderer name to use for the cell

Returns:

The new Cell widget.

Example

>>> from ipysheet import sheet, row
>>>
>>> s1 = sheet()
>>> row(0, [1, 2, 3, 34, 5])                    # The Cell type will be 'numeric'
>>> row(1, [True, False, True], column_start=2) # The Cell type will be 'checkbox'

ipysheet.easy.column(column, value, row_start=0, row_end=None, type=None, color=None, background_color=None, font_style=None, font_weight=None, style=None, choice=None, read_only=False, numeric_format='0.000', date_format='YYYY/MM/DD', renderer=None, **kwargs)[source]¶

Create a Cell widget, representing multiple cells in a sheet, in a vertical column

Parameters:

column (int) – Zero based column index where to put the column in the sheet
value (list) – The list of cell values representing the column
row_start (int) – Which row the column will start, default 0.
row_end (int) – Which row the column will end, default is the last.
type (string) – Type of cell, options are: text, numeric, checkbox, dropdown, numeric, date, widget. If type is None, the type is inferred from the type of the value being passed, numeric (float or int type), boolean (bool type), widget (any widget object), or else text. When choice is given the type will be assumed to be dropdown. The types refer (currently) to the handsontable types: https://handsontable.com/docs/6.2.2/demo-custom-renderers.html
color (string) – The text color in the cell
background_color (string) – The background color in the cell
read_only (bool) – Whether the cell is editable or not
numeric_format (string) – Numbers format
date_format (string) – Dates format
time_format (string) – Time format
renderer (string) – Renderer name to use for the cell

Returns:

The new Cell widget.

Example

>>> from ipysheet import sheet, column
>>>
>>> s1 = sheet()
>>> column(0, [1, 2, 3, 34, 5])                 # The Cell type will be 'numeric'
>>> column(1, [True, False, True], row_start=2) # The Cell type will be 'checkbox'

ipysheet.easy.cell_range(value, row_start=0, column_start=0, row_end=None, column_end=None, transpose=False, squeeze_row=False, squeeze_column=False, type=None, color=None, background_color=None, font_style=None, font_weight=None, style=None, choice=None, read_only=False, numeric_format='0.000', date_format='YYYY/MM/DD', renderer=None, **kwargs)[source]¶

Create a Cell widget, representing multiple cells in a sheet

Parameters:

value (list) – The list of cell values representing the range
row_start (int) – Which row the range will start, default 0.
column_start (int) – Which column the range will start, default 0.
row_end (int) – Which row the range will end, default is the last.
column_end (int) – Which column the range will end, default is the last.
transpose (bool) – Whether to interpret the value array as value[column_index][row_index] or not.
squeeze_row (bool) – Take out the row dimensions, meaning only value[column_index] is used.
squeeze_column (bool) – Take out the column dimensions, meaning only value[row_index] is used.
type (string) – Type of cell, options are: text, numeric, checkbox, dropdown, numeric, date, widget. If type is None, the type is inferred from the type of the value being passed, numeric (float or int type), boolean (bool type), widget (any widget object), or else text. When choice is given the type will be assumed to be dropdown. The types refer (currently) to the handsontable types: https://handsontable.com/docs/6.2.2/demo-custom-renderers.html
color (string) – The text color in the cell
background_color (string) – The background color in the cell
read_only (bool) – Whether the cell is editable or not
numeric_format (string) – Numbers format
date_format (string) – Dates format
time_format (string) – Time format
renderer (string) – Renderer name to use for the cell

Returns:

The new Cell widget.

Example

>>> from ipysheet import sheet, cell_range
>>>
>>> s1 = sheet()
>>> cell_range([[1, 2, 3, 34, 5], [6, 7, 8, 89, 10]])

ipysheet.easy.hold_cells(*args, **kwds)[source]¶

Hold adding any cell widgets until leaving this context.

This may give a better performance when adding many cells.

Example

>>> from ipysheet import sheet, cell, hold_cells
>>>
>>> sheet(rows=10,columns=10)
>>> with hold_cells()
>>>    for i in range(10):
>>>        for j in range(10):
>>>            cell(i, j, value=i * 10 + j)
>>> # at this line, the Cell widgets are added

ipysheet.easy.renderer(code, name)[source]¶

Create a Renderer widget

Parameters:	code (string or code or function object) – If a string object, it is assumed to be a JavaScript snippet, else it is assumed to be a function or code object and will be transpiled to javascript using flexxui/pscript. name (string) – Name of the renderer
Returns:	The new `Renderer` widget.

Example

>>> from ipysheet import sheet, renderer, cell
>>>
>>> s1 = sheet()
>>>
>>> def renderer_negative(instance, td, row, col, prop, value, cellProperties):
>>>     Handsontable.renderers.TextRenderer.apply(this, arguments);
>>>     if value < 0:
>>>         td.style.backgroundColor = 'orange'
>>>     else:
>>>         td.style.backgroundColor = ''
>>>
>>> renderer(code=renderer_negative, name='negative');
>>> cell(0, 0, 36, renderer='negative')  # Will be white
>>> cell(1, 0, -36, renderer='negative') # Will be orange

ipysheet.pandas_loader¶

ipysheet.pandas_loader.from_dataframe(dataframe)[source]¶

Helper function for creating a sheet out of a Pandas DataFrame

Parameters:	dataframe (Pandas DataFrame) –
Returns:	`Sheet` widget

Example

>>> import numpy as np
>>> import pandas as pd
>>> from ipysheet import from_dataframe
>>>
>>> dates = pd.date_range('20130101', periods=6)
>>> df = pd.DataFrame(np.random.randn(6, 4), index=dates, columns=list('ABCD'))
>>>
>>> sheet = from_dataframe(df)
>>> display(sheet)

ipysheet.pandas_loader.to_dataframe(sheet)[source]¶

Helper function for creating a Pandas DataFrame out of a sheet

Parameters:	sheet (Sheet widget) –
Returns:	A Pandas DataFrame

Example

>>> import ipysheet
>>>
>>> sheet = ipysheet.sheet(rows=3, columns=4)
>>> ipysheet.cell(0, 0, 'Hello')
>>> ipysheet.cell(2, 0, 'World')
>>>
>>> df = to_dataframe(sheet)
>>> display(df)

ipysheet.numpy_loader¶

ipysheet.numpy_loader.from_array(array)[source]¶

Helper function for creating a sheet out of a NumPy Array

Parameters:	array (NumPy Array) –
Returns:	`Sheet` widget

Example

>>> import numpy as np
>>> from ipysheet import from_array
>>>
>>> arr = np.random.randn(6, 26)
>>>
>>> sheet = from_array(arr)
>>> display(sheet)

ipysheet.numpy_loader.to_array(sheet)[source]¶

Helper function for creating a NumPy Array out of a sheet

Parameters:	sheet (Sheet widget) –
Returns:	A NumPy Array

Example

>>> import ipysheet
>>>
>>> sheet = ipysheet.sheet(rows=3, columns=4)
>>> ipysheet.cell(0, 0, 'Hello')
>>> ipysheet.cell(2, 0, 'World')
>>>
>>> arr = to_array(sheet)
>>> display(arr)

ipysheet.sheet¶

class ipysheet.sheet.Sheet(**kwargs)[source]¶

Bases: ipywidgets.widgets.domwidget.DOMWidget

cells¶: An instance of a Python tuple.

column_headers¶: A trait type representing a Union type.

column_resizing¶: A boolean (True, False) trait.

column_width¶: A trait type representing a Union type.

columns¶: A casting version of the int trait.

named_cells¶: An instance of a Python dict.

row_headers¶: A trait type representing a Union type.

row_resizing¶: A boolean (True, False) trait.

rows¶: A casting version of the int trait.

search_token¶: A trait for unicode strings.

stretch_headers¶: A trait for unicode strings.

class ipysheet.sheet.Cell(**kwargs)[source]¶

Bases: ipywidgets.widgets.widget.Widget

choice¶: An instance of a Python list.

column_end¶: A casting version of the int trait.

column_start¶: A casting version of the int trait.

date_format¶: A trait for unicode strings.

name¶: A trait for unicode strings.

numeric_format¶: A trait for unicode strings.

read_only¶: A boolean (True, False) trait.

renderer¶: A trait for unicode strings.

row_end¶: A casting version of the int trait.

row_start¶: A casting version of the int trait.

squeeze_column¶: A boolean (True, False) trait.

squeeze_row¶: A boolean (True, False) trait.

style¶: An instance of a Python dict.

time_format¶: A trait for unicode strings.

transpose¶: A boolean (True, False) trait.

type¶: A trait for unicode strings.

value¶: A trait which allows any value.

class ipysheet.sheet.Range(**kwargs)[source]¶

Bases: ipywidgets.widgets.widget.Widget

value¶: A trait type representing a Union type.

Spreadsheet widget for the Jupyter Notebook¶

Installation¶

Getting started¶

Events¶

Cell ranges¶

Calculations¶

Renderers¶

API reference¶

ipysheet.easy¶

ipysheet.pandas_loader¶

ipysheet.numpy_loader¶

ipysheet.sheet¶