Search · Index

Spreadsheet

Introduction

"The power of spreadsheets derives largely from the fact that human beings have a well developed intuition about spaces and a well developed notion of dependency between items. Thus, many people find it easier to perform complex calculations in a spreadsheet than writing the equivalent sequential program." ( http://en.wikipedia.org/wiki/Spreadsheet#Spreadsheet_programming )

"What spreadsheets should do is to allow, nay encourage, the use of data extracts from external sources, notably relational databases." ( http://linuxfinances.info/info/spreadsheets.html )

Vision Statement

Provides practical, scalable aspects of spreadsheet services. Imports tables and lists/columns/rows via textareas to bypass innumerable browser input field UI hell. Cell references can point to external values, such as table field lookups (with access controls).

Requirements

• able to import tables and lists/columns/rows via a single browser textarea
• Cell references can point to external values, such as table field lookups (with access controls).
• allow user defined functions/equations that repeat to "fill" entire columns. Helps to prevent need for pattern recognition techniques for finding "repeated patterns of formulae" ( http://linuxfinances.info/info/spreadsheets.html )
• use ns tcl [exp ] for cell calculations with a pre-defined set of functions that handle cell values in a list (aggregate functions), sum(), or previous value of same column, or sequential sum (so value can be used in row for something, such as running balance)
• identify and address any security risks, for example check for any way to inject sql via with [expr ]
• allow reference to other table values using a modified db_0or1row where table name.column must be identified in a controlled manner, such as via a package parameter.
• table can be as small as 1x1.
• There should be a way to create xml from tables, where the tags identify useful info, such as using XBRL: http://www.xbrl.org/WhatIsXBRL/
• There should be a way to present table as an html table, a textarea with delimited data, an xml file in openoffice format. See also: http://tracker.jot.com/screenshots/ for related uses.

Implementation Notes

Consider requiring a cell reference only left-or-above of current cell, to try to reduce "spaghetti" logic: " Spreadsheet models encourage the use of "spaghetti" logic, where cells point to cells that point to cells, and can grow into random networks of calculation logic;" ( http://linuxfinances.info/info/spreadsheets.html#SPPROBS ) The times I have used spagetti logic, I think I could just as well have created a separate table to handle the calculations.-tb

How to adapt to a site without customizing code?

• by extending parameters
• by extracting the ec_product template system to apply to spreadsheet "views". Cell references can be placed most anywhere, with a standardized default template view.

 cell references use Row, Column convention
 1. [cell rXcY] returns value of that location
 2. [cell cY] returns value of column in same table and current row
 [cellvalue key, column_name]
 [cellvalue row_nbr, col_nbr]
 [cellvalue row_nbr, column_name]
 [cellvalue key, column_nbr
   can reference other spreadsheets, but there needs to be some kind of dependency map to not permit looping.

Feature requests

Rajagopalan N. makes a point that business rules should be changeable from the user/admin perspective (see http://brms.findtechblogs.com/default.asp?item=515959 ). Specially configured spreadsheets might provide a suitable environment for adding some customizable business rules at the UI instead of in code.

Design notes/discussion

data model consists of 2 primary tables

• The table of sheets (each row represents a spreadsheet) with these fields: table_ref (object_id), table abbrev/name, css_ref, table_title, table_description, orientation (display row horizontally, else row vertically)
• The table of cells (each row represents a cell) with these fields: table_ref, value (returned by function or input value), format (formatting for value), style (css span class), function (or input value), depth (order of function's operation in context of other functions, determined by dependencies), name null (or a unique name, if row = 0, then column_name), row (number), column (number), title (a label when displaying cell as a single value), if row = 0 then column_title (pretty name?), last_calculated (handy when dependent info changes), last_modified (data entry change)

Rules

row 0 are defaults for column (name,function,value,format,title)

if a statistics datapoint precalculate x^2 per cell

What method to use to calculate values in a spreadsheet?

evaluate for all cases where last_modified of a cell > last_calculated and depth is 1 more than min depth of last_modified set, and order from left to right, top to bottom and set modified column to max ( dependent cells depth) + 1

Let's take a case where the sheet consists of 256 columns. Then sort index is row_nbr*256+col_nbr This means that a change in a cell function recalcs everything to the right and below... not efficient for these db styled spreadsheets. We want to only recalc what is necessary.

alternately, assume all cells default to level 1... this might be faster for some cases where there are few calculations, because all level 2+ depth fields get re-calced, but level 1 data is not recalculated, or for sequence columns, it's most likely that data near bottom gets recalced, while not having to recalc previous stuff. --note: having a restriction to reference left or above cells might allow a faster calculation analog.

Maybe use a pointer/node system? How would that work?

The single column dbs seem to be a take-off on this approach, maybe there is some insight for aggregate functions there.. hmm.  Sql has functions that aggregate the enire column or calculates using any references within the same row.

---

Categories: OpenACS (ec-g2)

No comments available

Add comment