Introduction#

The SpreadSheet filter allows to make use of spreadsheet functions inside Wiki tables. For example it is possible to sum up values using the function sum(above()). It's also possible to save tables and generate Barcharts(uses the ImageGen Plugin).

The Plugin is Open Source (LGPL) -> Download Sourcecode.

The usage description is also available in German, see SpreadSheetFilter Benutzung.
Eine deutsche Benutzungsbeschreibung siehe auf SpreadSheetFilter Benutzung.

Installation#

  • Download the spreadsheet.jar and place it into the WEB-INF/libs directory.
  • Modify the WEB-INF/classes/filters.xml to include the spreadsheet filter. It should look similar to this:
<?xml version="1.0"?>
<pagefilters>
   <filter>
     <class>org.wikiwizard.jspwiki.spreadsheet.SpreadSheetFilter</class>
   </filter>
</pagefilters>

Note: If you use the ExplodedTableFilter, you should put the SpreadSheetFilter after the ExplodedTableFilter.

Example#

The SpreadSheetFilter converts


||Product||Price
|Product A|9.99
|Product B|29.99
|Product C|100.0
||Total price||%CALC{ sum(above()) }%

to following code:

||Product||Price
|Product A|9.99
|Product B|29.99
|Product C|100.0
||Total price||139.98

The SpreadSheetFilter generates BarCharts using %TABLE{ name=..}% to save a table and %CHART{ table=..}% to paste the Chart.

%TABLE{ name=t1 }%

||Product||Price
|Product A|9.99
|Product B|29.99
|Product C|100.0
||Total price||%CALC{ sum(above()) }%

%CHART{ table=t1 }%

More expressions#

All expressions are only valid inside tables and surrounded by %CALC{ ... }%, like in the example above.

  • 3+4
  • 2*(3+4)
  • square(5)
  • square(10)+sum(above())
  • sum(1,2,3,4,5)
  • sum(range(1,5))
  • uppercase("foo")
  • substring("hello spreadsheet!",0,5)
  • set("foo",3)
  • get("foo")
  • eval("1+2")

List of functions#

(Documentation needs to be done)

  • APPEND
  • LENGTH
  • LOWERCASE
  • REPLACE
  • SUBSTRING
  • TRIM
  • UPPERCASE
  • EVAL
  • EXEC
  • GET
  • SET
  • HELP
  • ADD
  • AVERAGE
  • DIVIDE
  • INT
  • LIST
  • MAX
  • MIN
  • MULTIPLY
  • PI
  • RANGE
  • ROUND
  • SQRT
  • SQUARE
  • SUBTRACT
  • SUM

Additional information#

  • The functions supported by the SpreadSheetFilter are provided by function providers. New functions can be added easily by extending the existing function providers or adding new function providers (e.g. there could be more functions for Strings, Math, RegEx etc). So feel free to contribute!
  • An idea is to extend the Parser to parse functions everywhere on the page, not only in tables.

Cell References#

Absolute Cell References#

You can reference a cell using the CELL function. The CELL function takes row and column arguments which start counting from zero at the top left corner of the table:
|1
|7
|%CALC{ CELL(0,0) }% 

gives a table that looks like

1
7
1

Relative Cell References#

It can be useful to reference cells relative to the current cell. For instance, I often create tables of time estimates for tasks. Using SUM(above()) I can add up the total number of hours for all the tasks and even insert more tasks into the table later. Using a relative cell reference I can then work out the total number of days by dividing the hour total by 8.

||effort||task
|      1|search for spreadsheet instructions on-line
|      1|download and read source code
|      1|write instructions for cell referencing
|     15|tend my chilli plants
||%CALC{ sum(above()) }%                          ||Total hours
||%CALC{ round(divide(cell(row()-1,col()),8)) }%  ||Total days

gives the following:

efforttask
1search for spreadsheet instructions on-line
1download and read source code
1write instructions for cell referencing
15tend my chilli plants
18Total hours
2Total days

--AlanMJackson

History#

The plugin was developed by the i3G Institute. Author is SteffenSchramm.

--ChristophSauer 03. July 2006

Added function to generates BarCharts

--AndreasBöttcher 11. August 2006

Discussion#

Q: Would it work with tables generated by plugins, like TasksQuery from TasksPlugin ?
Nascif Abousalh-Neto, 18-Jul-2006
A: No. Plugins return HTML code, but the SpreadSheetFilter parses Wiki markup. It uses the preTranslate() method and is run before the plugins are executed.
Hey, I am glad that you guys are integrating with the ImageGen package. What exactly is the limitation you found about Unfortunatly the Plugin could only generate one chart per wiki site. ?? I have many, many charts in my wikis, and even more then one per page. Maybe you are not using the title attribute?
Nascif Abousalh-Neto, 11-Aug-2006
You're right. I'll fix that these days.
Ok, problem fixed.
AndreasBöttcher 14. August 2006

A suggestion: instead of a filter, make it a regular plugin that parses a table defined in the plugin body. Then, if you use the technique implemented in ImageGen to support nested plugins, you would be able to evaluate tables (with formulas) generated by other plugins, like TasksPlugin, InsertTable and JSPlugin.

Also, if this code is implemented as a plugin, it could be consumed through nesting by ImageGen (no need to use the %CHART functionality).

For examples of this idea check ImageGenNestedPluginExample.

Regards, Nascif Abousalh-Neto, 23-Aug-2006


Questions:

Could you add some documentation on the aggregation functions, like above()? Are there any others?

Is it possible to use formulas that reference cells in the same row? I would like to have a table where the cell in the third column is the sum of the previous two cells in the same row. How could I do that?

Any plans to support operations on dates?

Thanks, Nascif Abousalh-Neto, 23-Aug-2006

Add new attachment

Only authorized users are allowed to upload new attachments.
« This page (revision-45) was last changed on 12-Sep-2010 15:33 by Wouter Van daele