MediaWiki SpreadSheet

From Wiki
Jump to: navigation, search

These are notes for a spreadsheet extension for MediaWiki. It's a fairly simple spreadsheet that basically treats cells as variable names.

  • A spreadsheet item is defined by a ss tag. Parameters are:
    • cell - Defines this cell's name (mandatory)
    • value - A numeric value
    • calc - A formula
    • show - Display the value in the cell, with optional formatting
  • A cell without a value or calc tag is implicitly shown. If show with a formatting string is present, it will be displayed according to the format string.
  • To define cell A1 with a value of 3.1415926535897, we'd say <ss cell="a1" value="3.1415926535897">.
  • If we'd like to this same value to be display with only 4 digits of precision, we'd say <ss cell="a1" value="3.1415926535897" show="%f.4> (note the sprintf() like formatting).
  • If no format tag is present, the value displayed is as it was defined (values are stored as strings, but handled as numbers for calculations, so <ss cell="a1" value="0003" show> will display as 0003).
  • Cells must be defined before being used. In short, no forward references.
  • Formulas may use any PHP supported operator, and use PHP's evaluation order. [1]
  • Before evaluation, each cell reference is replaced with that cells value, after checking that it's a valid number (to prevent code injection hacks).

Normally, cell values are not shown. However, if we have a list of prices and we'd like to the values to be shown as they're defined, and then totaled at the end, we'd do something like this (we'll use a table for convenience).

<ss cell="b1" value="7"> <!-- Define our tax rate -->
!'''Item''' || '''Cost'''
| Spaghetti noodles || <ss cell="a1" value="1.19" show="$%f.2">
| Marinara sauce || <ss cell="a2" value="2.39" show="$%f.2">
| French bread || <ss cell="a3" value=".99" show="$%f.2">
| Garlic cloves || <ss cell="a4" value="1.45" show="$%f.2">
| '''Sub-total''' || <ss cell="a5" calc="a1+a2+a3+a4" format="$%f.2">
| Tax (<ss cell="b1">%) || <ss cell="a6" calc="a5*(b1/100)" show="$%f.2">
| '''Total''' || <ss cell="a7" calc="a5+a6" show="$%f.2">

Which would result in a table displayed like this:

Item Cost
Spaghetti noodles $1.19
Marinara sauce $2.39
French bread $0.99
Garlic cloves $1.45
Sub-total $5.02
Tax (7%) $0.35
Total $5.37