# -*- coding: utf-8 -*-
"""
Developed by Massimo Di Pierro, optional component of web2py, BSDv3 license.
"""
from __future__ import print_function
import re
import pickle
import copy
import json
def quote(text):
return str(text).replace('\\', '\\\\').replace("'", "\\'")
class Node:
def __init__(self, name, value, url='.', readonly=False, active=True,
onchange=None, select=False, size=4, **kwarg):
self.url = url
self.name = name
self.value = str(value)
self.computed_value = ''
self.incoming = {}
self.outcoming = {}
self.readonly = readonly
self.active = active
self.onchange = onchange
self.size = size
self.locked = False
self.select = value if select and not isinstance(value, str) else False
def xml(self):
if self.select:
selectAttributes = dict(_name=self.name,_id=self.name,_size=self.size,
_onblur="ajax('%s/blur',['%s']);"%(self.url,self.name))
# _onkeyup="ajax('%s/keyup',['%s'], ':eval');"%(self.url,self.name),
# _onfocus="ajax('%s/focus',['%s'], ':eval');"%(self.url,self.name),
for k,v in selectAttributes.items():
self.select[k] = v
return self.select.xml()
else:
return """",
"text",
readable=False, writable=False,
formname=""))
# submit button label
form.elements("input [type=submit]").attributes["_value"] = \
T("Update database")
form.elements("textarea")[0].attributes["_style"] = "display: none;"
w2p_spreadsheet_update_script = SCRIPT('''
jQuery(
function(){
jQuery("").submit(function(){
jQuery("[name=]").val(JSON.stringify(
w2p_spreadsheet_data)
);
});
}
);
''')
# retrieve changes
if form.process().accepted:
data = form.vars.
changes = Sheet.updated(data)
# Do db I/O:
for table, rows in changes.iteritems():
for row, values in rows.iteritems():
db[table][row].update_record(**values)
# the action view should expose {{=form}}, {{=sheet}}, {{=myscript}}
return dict(form=form, sheet=sheet,
myscript=w2p_spreadseet_update_script)
Second method: Sending data updates with .ajax()
-spreadsheet page's view:
{{
=INPUT(_type="button", _value="update data",
_id="w2p_spreadsheet_update_data")
}}
{{=SCRIPT('''
jQuery(function(){
jQuery("#w2p_spreadsheet_update_data").click(
function(){
jQuery.ajax({url: "%s",
type: "POST",
data:
{data:
JSON.stringify(w2p_spreadsheet_data)}
}
);
}
);
});
''' % URL(c="default", f="modified"))}}
-controller: modified function
def modified():
data = request.vars.data
changes = Sheet.updated(data)
# (for db I/O see first method)
return "ok"
Third method:
When creating a Sheet instance, pass a kwarg update_button=
This step will auto process the updated data with db I/O (requires calling
.process() with db=). You must expose an html element which
supports the .click() event, i.e. a normal button.
# TODO:
# -¿SIGNED URLS?
# -Delete checkbox columns for each table and default
# -Deletable=True option for showing/hiding delete checkboxes
# -process() method support for db I/O
"""
regex = re.compile('(?'
+ r"[uU]?[rR]?'''([^']+|'{1,2}(?!'))*'''|"
+ r"'([^'\\]|\\.)*'|"
+ r'"""([^"]|"{1,2}(?!"))*"""|'
+ r'"([^"\\]|\\.)*")', re.DOTALL)
def dumps(self):
dump = pickle.dumps(self)
return dump
@staticmethod
def position(key):
""" Returns int row, int col for a 'rncn' formatted key'"""
try:
r, c = Sheet.pregex.findall(key)
r, c = int(r), int(c)
except (ValueError, IndexError, TypeError) as e:
error = "%s. %s" % \
("Unexpected position parameter",
"Must be a key of type 'rncn'")
raise ValueError(error)
return r, c
@staticmethod
def loads(data):
sheet = pickle.loads(data)
return sheet
@staticmethod
def updated(data):
""" Reads spreadsheet update information sent client-side.
Returns a dict with updated database rows/fields.
Structure:
{:{
:{:,
:,
...
},
...
}
}
data dict argument:
# columns: (a dict with colname, column index map)
# colnames: (a dict with column index, colname map)
# id_columns: list with id columns
# id_colnames: dict with id colname: column index map
# cells: dict of "rncn": value pairs
# modified: dict of modified cells for client-side
"""
data = json.loads(data)
# record update dict
changes = {}
# read column index per table
# table, id column map
tablenames = {}
for colname, i in data["id_colnames"].iteritems():
tablenames[colname.split(".")[0]] = i
# Separate multitable rows
# Identify each row id (old data)
# Build a dict with table/row/field
# update information.
# collect new data by row (modified):
for key, value in data["modified"].iteritems():
r, c = Sheet.position(key)
# don't apply repeated values
if data["cells"][key] != value:
# read tablename
tablename, fieldname = data["colnames"][str(c)].split(".")
# read db record id
row_id_key = "r%sc%s" % (r, tablenames[tablename])
row_id = data["cells"][row_id_key]
changes.setdefault(tablename, {})
changes[tablename].setdefault(row_id, {})
changes[tablename][row_id][fieldname] = value
return changes
def process(self, request, db=None, db_callback=None):
"""
call this in action that creates table, it will handle ajax callbacks
optional db (a DAL instance). It's required for db I/O
optional callback string. js commands to call after successful
ajax db update.
db_callback string format keys:
modified (number of rows updated)
"""
if not request.args(0) == "data":
# normal cell processing
cell = request.vars.keys()[0]
if request.args(0) == 'focus':
return "jQuery('#%(cell)s').val('%(value)s');" % \
dict(cell=cell, value=quote(self[cell].value))
value = request.vars[cell]
self[cell] = value
if request.args(0) == 'blur':
return "jQuery('#%(cell)s').val('%(value)s');" % \
dict(cell=cell, value=quote(self[cell].computed_value))
elif request.args(0) == 'keyup':
jquery = ''
for other_key in self.modified:
if other_key != cell:
jquery += "jQuery('#%(other_key)s').val('%(value)s');" % \
dict(other_key=other_key,
value=quote(self[other_key].computed_value))
else:
# spreadsheet db update
result = dict(modified=0,
errors=0,
message="",
db_callback="")
if db is not None:
data = request.vars["data"]
changes = self.updated(data)
# Do db I/O:
for table, rows in changes.iteritems():
for row, values in rows.iteritems():
db[table][row].update_record(**values)
result["modified"] += 1
if db_callback is not None:
result["db_callback"] = db_callback
else:
result["message"] = "Sheet.process Error. No db found."
return json.dumps(result)
return jquery
def get_attributes(self, data):
attributes = {}
for k in data.keys():
if k.startswith("_"):
attributes[k] = data[k]
return attributes
def even_or_odd(self, v):
""" Used for table row stripping """
if v % 2 == 0:
return "even"
else:
return "odd"
def __init__(self, rows, cols, url='.', readonly=False,
active=True, onchange=None, value=None, data=None,
headers=None, update_button="", c_headers=None,
r_headers=None, **kwarg):
"""
Arguments:
headers: a dict with "table.fieldname": name values
value: common value for all spreadsheet
(can be a lambda x, y: z or function reference)
Rows and cols values will be updated automatically to fit
the data boundaries when the data argument is a Rows object.
self.client: for storing sheet data client side
columns: a dict with colname, column index map
colnames: a dict with column index, colname map
id_columns: list with id columns
id_colnames: dict with id colname: column index map
cells: dict of "rncn": value pairs
modified: dict of modified cells for client-side
edition.
"""
self.rows = rows
self.cols = cols
self.url = url
self.nodes = {}
self.error = 'ERROR: %(error)s'
self.allowed_keywords = ['for', 'in', 'if', 'else', 'and', 'or', 'not',
'i', 'j', 'k', 'x', 'y', 'z', 'sum']
self.value = value
self.environment = {}
self.attributes = self.get_attributes(kwarg)
self.tr_attributes = {}
self.td_attributes = {}
self.c_headers = c_headers
self.r_headers = r_headers
self.data = data
self.readonly = readonly
self.update_button = update_button
self.client = {
"columns": {},
"colnames": {},
"id_columns": [],
"id_colnames": {},
"cells": {},
"modified": {},
"headers": headers
}
# if db and query:
if self.data is not None:
# retrieve row columns length
self.rows = len(self.data)
# retrieve rows length
self.cols = len(self.data.colnames)
# map row data to rncn values
for x, colname in enumerate(self.data.colnames):
self.client["columns"][colname] = x
self.client["colnames"][x] = colname
for x, row in enumerate(self.data):
for colname, y in self.client["columns"].iteritems():
key = "r%sc%s" % (x, y)
tablename, fieldname = colname.split(".")
try:
value = row[tablename][fieldname]
except (KeyError, AttributeError):
# single table query
value = row[fieldname]
self.client["cells"][key] = str(value)
# TODO: support different id keys
if ".id" in colname:
self.client["id_columns"].append(y)
self.client["id_colnames"][colname] = y
for k in xrange(self.rows * self.cols):
key = 'r%sc%s' % (k / self.cols, k % self.cols)
r, c = self.position(key)
if key in self.client["cells"]:
value = self.client["cells"][key]
# readonly id values
if c in self.client["id_columns"]:
readonly = True
else:
readonly = self.readonly
elif self.value is not None:
if callable(self.value):
value = self.value(r, c)
else:
value = self.value
else:
value = '0.00'
self.cell(key, value,
readonly, active, onchange)
exec('from math import *', {}, self.environment)
def delete_from(self, other_list):
indices = [k for (k, node) in enumerate(other_list) if k == node]
if indices:
del other_list[indices[0]]
def changed(self, node, changed_nodes=[]):
for other_node in node.outcoming:
if not other_node in changed_nodes:
changed_nodes.append(other_node)
self.changed(other_node, changed_nodes)
return changed_nodes
def define(self, name, obj):
self.environment[name] = obj
def cell(self, key, value, readonly=False, active=True,
onchange=None, select=False, **kwarg):
"""
key is the name of the cell
value is the initial value of the cell. It can be a formula "=1+3"
a cell is active if it evaluates formulas
Value can be a function(r, c) which returns a string
"""
if not self.regex.match(key):
raise SyntaxError("Invalid cell name: %s" % key)
else:
attributes = self.get_attributes(kwarg)
if attributes is not None:
self.td_attributes[key] = attributes
key = str(key)
r, c = self.position(key)
if callable(value):
value = value(r, c)
node = Node(key, value, self.url, readonly, active,
onchange, select=select, **kwarg)
self.nodes[key] = node
self[key] = value
def get_cell_arguments(self, data, default=None):
"""Reads cell arguments from a dict object"""
active = True
onchange = None
readonly = False
value = ""
if default is not None:
data.update(default)
if "active" in data:
active = data["active"]
if "readonly" in data:
readonly = data["readonly"]
if "onchange" in data:
onchange = data["onchange"]
if "value" in data:
value = data["value"]
return active, onchange, readonly, value
def row(self, row, cells, value=None, **kwarg):
# row: row index (0, 1, ...)
# cells: a sequence of values or a dict of dict with
# arg: value pairs
# one column example:
# {"0": {"value":1.0, "readonly":False, "active":True, "onchange":None}}
# value: common value for all cells
attributes = self.get_attributes(kwarg)
if attributes is not None:
self.tr_attributes[str(row)] = attributes
if isinstance(cells, dict):
for col, data in cells.iteritems():
key = "r%sc%s" % (row, col)
active, onchange, readonly, cell_value = \
self.get_cell_arguments(data, default=kwarg)
if value is None:
v = cell_value
else:
v = value
self.cell(key, v, active=active,
readonly=readonly,
onchange=onchange, **attributes)
else:
active, onchange, readonly, all_value = \
self.get_cell_arguments(kwarg)
for col, cell_value in enumerate(cells):
key = "r%sc%s" % (row, col)
if value is None:
v = cell_value
else:
v = value
self.cell(key, v, active=active,
onchange=onchange,
readonly=readonly, **attributes)
def column(self, col, cells, value=None, **kwarg):
"""
# col: column index (0, 1, ...)
# cells: a sequence of values or a dict of dict with
# arg: value pairs
# one row example:
# {"0": {"value":1.0, "readonly":False, "active":True, "onchange":None}}
# value: common value for all cells
"""
attributes = self.get_attributes(kwarg)
if isinstance(cells, dict):
for row, data in cells.iteritems():
key = "r%sc%s" % (row, col)
active, onchange, readonly, cell_value = \
self.get_cell_arguments(data, default=kwarg)
if value is None:
v = cell_value
else:
v = value
self.cell(key, v, active=active, readonly=readonly,
onchange=onchange, **attributes)
else:
active, onchange, readonly, all_value = \
self.get_cell_arguments(kwarg)
for row, cell_value in enumerate(cells):
key = "r%sc%s" % (row, col)
if value is None:
v = cell_value
else:
v = value
self.cell(key, v, active=active,
onchange=onchange, readonly=readonly,
**attributes)
def matrix(self, cells, starts="r0c0", ends=None, value=None, **kwarg):
"""
Insert a n x n matrix or a set of cells
# starts: upper left cell
# ends: lower right cell
# cells: a sequence of value sequences
# or a dict with "rncn" keys
# Example 1 cells:
# ((v11, v12, ... v1n),
(vn2, vn2, ... vnn))
# Example 2 cells:
# {"r0c0": {...}, ... "rncn": {...}}
# value: common value for all cells
"""
attributes = self.get_attributes(kwarg)
starts_r, starts_c = self.position(starts)
ends_r, ends_c = None, None
if ends is not None:
ends_r, ends_c = self.position(ends)
if isinstance(cells, dict):
for key, data in cells.iteritems():
r, c = self.position(key)
key = "r%sc%s" % (r + starts_r, c + starts_c)
active, onchange, readonly, cell_value = \
self.get_cell_arguments(data, default=kwarg)
if value is None:
v = cell_value
else:
v = value
if (ends is None) or ((ends_r >= r + starts_r) and
(ends_c >= c + starts_c)):
self.cell(key, v, active=active,
readonly=readonly,
onchange=onchange, **attributes)
else:
active, onchange, readonly, all_value = \
self.get_cell_arguments(kwarg)
for r, row in enumerate(cells):
for c, cell_value in enumerate(row):
if value is None:
v = cell_value
else:
v = value
key = "r%sc%s" % (r + starts_r, c + starts_c)
if (ends is None) or \
((ends_r >= r + starts_r) and
(ends_c >= c + starts_c)):
self.cell(key, v,
active=active,
onchange=onchange,
readonly=readonly,
**attributes)
def __setitem__(self, key, value):
key = str(key)
value = str(value)
node = self.nodes[key]
node.value = value
if value[:1] == '=' and node.active:
# clear all edges involving current node
for other_node in node.incoming:
del other_node.outcoming[node]
node.incoming.clear()
# build new edges
command = self.re_strings.sub("''", value[1:])
node.locked = False
for match in self.regex.finditer(command):
other_key = match.group()
if other_key == key:
self.computed_value = self.error % dict(error='cycle')
self.modified = {}
break
if other_key in self.nodes:
other_node = self.nodes[other_key]
other_node.outcoming[node] = True
node.incoming[other_node] = True
elif not other_key in self.allowed_keywords and \
not other_key in self.environment:
node.locked = True
node.computed_value = \
self.error % dict(
error='invalid keyword: ' + other_key)
self.modified = {}
break
self.compute(node)
else:
try:
node.computed_value = int(node.value)
except:
try:
node.computed_value = float(node.value)
except:
node.computed_value = node.value
self.environment[key] = node.computed_value
if node.onchange:
node.onchange(node)
self.modified = self.iterate(node)
def compute(self, node):
if node.value[:1] == '=' and not node.locked:
try:
exec('__value__=' + node.value[1:], {}, self.environment)
node.computed_value = self.environment['__value__']
del self.environment['__value__']
except Exception as e:
node.computed_value = self.error % dict(error=str(e))
self.environment[node.name] = node.computed_value
if node.onchange:
node.onchange(node)
def iterate(self, node):
output = {node.name: node.computed_value}
changed_nodes = self.changed(node)
while changed_nodes:
ok = False
set_changed_nodes = set(changed_nodes)
for (k, other_node) in enumerate(changed_nodes):
#print other_node, changed_nodes
if not set(other_node.incoming.keys()).\
intersection(set_changed_nodes):
#print 'ok'
self.compute(other_node)
output[other_node.name] = other_node.computed_value
#print other_node
del changed_nodes[k]
ok = True
break
if not ok:
return {}
return output
def __getitem__(self, key):
return self.nodes[str(key)]
def get_computed_values(self):
d = {}
for key in self.nodes:
node = self.nodes[key]
if node.value[:1] != '=' or not node.active:
d[key] = node.computed_value
return d
def set_computed_values(self, d):
for key in d:
if not key in self.nodes:
continue
node = self.nodes[key]
if node.value[:1] != '=' or not node.active:
node.value = d[key]
def sheet(self):
import gluon.html
(DIV, TABLE, TR, TD, TH, BR, SCRIPT) = \
(gluon.html.DIV, gluon.html.TABLE, gluon.html.TR, gluon.html.TD,
gluon.html.TH, gluon.html.BR, gluon.html.SCRIPT)
regex = re.compile('r\d+c\d+')
if not self.c_headers:
header = TR(TH(), *[TH('c%s' % c)
for c in range(self.cols)])
else:
header = TR(TH(), *[TH('%s' % c)
for c in self.c_headers])
rows = []
for r in range(self.rows):
if not self.r_headers:
tds = [TH('r%s' % r), ]
else:
tds = [TH('%s' % self.r_headers[r]), ]
for c in range(self.cols):
key = 'r%sc%s' % (r, c)
attributes = {"_class": "w2p_spreadsheet_col_%s" %
self.even_or_odd(c)}
if key in self.td_attributes:
attributes.update(self.td_attributes[key])
td = TD(self.nodes[key], **attributes)
tds.append(td)
attributes = {"_class": "w2p_spreadsheet_row_%s" %
self.even_or_odd(r)}
if str(r) in self.tr_attributes:
attributes.update(self.tr_attributes[str(r)])
rows.append(TR(*tds, **attributes))
attributes = {"_class": "w2p_spreadsheet"}
attributes.update(self.attributes)
table = TABLE(header, *rows, **self.attributes)
if len(self.client["cells"]) >= 1:
data = SCRIPT(
"""
var w2p_spreadsheet_data = %(data)s;
var w2p_spreadsheet_update_button = "%(update_button)s";
var w2p_spreadsheet_update_result = null;
function w2p_spreadsheet_update_cell(a){
// update data
w2p_spreadsheet_data.modified[this.id] = this.value;
}
function w2p_spreadsheet_update_db_callback(result){
w2p_spreadsheet_update_result = result;
eval(w2p_spreadsheet_update_result.db_callback);
}
function w2p_spreadsheet_update_db(){
// ajax background db update
jQuery.ajax({url: "%(url)s/data",
type: "POST",
data:
{data: JSON.stringify(w2p_spreadsheet_data)},
dataType: "json",
success: w2p_spreadsheet_update_db_callback
});
}
// add onchange cell update event
jQuery(function(){
jQuery(".%(name)s input").change(w2p_spreadsheet_update_cell);
});
if (w2p_spreadsheet_update_button != ""){
jQuery(function(){
jQuery("#" + w2p_spreadsheet_update_button).click(
w2p_spreadsheet_update_db);
});
}
""" % dict(data=json.dumps(self.client),
name=attributes["_class"],
url=self.url,
update_button=self.update_button))
# extra row for fieldnames
unsorted_headers = []
if self.client["headers"] is not None:
for fieldname, name in self.client["headers"].iteritems():
unsorted_headers.append((self.client["columns"][fieldname],
name))
else:
for fieldname, c in self.client["columns"].iteritems():
unsorted_headers.append((c, fieldname))
sorted_headers = [TH(), ] + \
[TH(header[1]) for header in sorted(unsorted_headers)]
table.insert(0, TR(*sorted_headers,
**{'_class': "%s_fieldnames" %
attributes["_class"]}))
else:
data = SCRIPT(""" // web2py Spreadsheets: no db data.""")
return DIV(table,
BR(),
TABLE(*[TR(TH(key), TD(self.nodes[key]))
for key in self.nodes if not regex.match(key)]),
data, **attributes)
def xml(self):
return self.sheet().xml()
if __name__ == '__main__':
s = Sheet(0, 0)
s.cell('a', value="2")
s.cell('b', value="=sin(a)")
s.cell('c', value="=cos(a)**2+b*b")
print(s['c'].computed_value)