Save to my list
Unsave this
Created on
0
Jan 2013
Last activity
0
Aug 2018

Short Description

Toolbox to create and manipulate result tables with plug-ins and protocols, and export as text file or to your favorite spreadsheet software

Documentation

General description

Workbooks is a library that allows users and developers to handle and manipulate results via workbooks (also known as spreadsheets). Workbooks uses the Apache POI library, and is therefore able to read/write common file formats including those used by Open Office's 'Calc' and Microscoft's 'Excel'.

Workbooks comes with the following components:

  • A Var structure for developers to store algorithm output from plug-ins and protocols
  • Two blocks to import and export workbooks in Protocols
  • A standalone interface to load and modify existing workbooks (still evolving to add more features!)
  • A simple API to create workbooks programmatically in no time

 

For scripters

Here is a sample script showing you how to create (and show) a workbook (screenshot above) in just a few lines in the Script Editor:

importClass(Packages.plugins.adufour.workbooks.Workbooks)
importClass(Packages.plugins.adufour.workbooks.IcySpreadSheet)
importClass(Packages.java.awt.Color)
// Create an empty workbook
wb = Workbooks.createEmptyWorkbook();
// Get a (possibly new) sheet
sheet = Workbooks.getSheet(wb, "Test");
// Set the header row (all at once, easier to write!)
sheet.setRow(0, "Col 0", "Col 1", "Some other column"); // etc.
// Assign a few cell values
// NB: give any object (unknown types are converted to text)
sheet.setValue(0, 0, "Name");
sheet.setValue(1, 0, 3);
// Need to insert a formula?
// NB: this is the standard formula syntax. The first (corner) cell is called "A1"
sheet.setFormula(1, 1, "A2 * A2");
// How about changing the background color?
sheet.setFillColor(1, 0, Color.cyan);
// Finally, show the workbook on screen
// with a nice window title and whether the table should be editable
Workbooks.show(wb, "Workbook test", false);

For developers

Here is a sample code showing you how to create (and show) a workbook (screenshot above) in just a few lines in a Java plugin:

// Some import statements you might need (in case you hate auto-imports ;))
import org.apache.poi.ss.usermodel.Workbook; // NOTE: this comes from Apache POI
import plugins.adufour.workbooks.Workbooks;
import plugins.adufour.workbooks.IcySpreadSheet;
/**
* A test to make sure the plug-in works as intended. Also useful as a sample code
*/
public static void test()
{
// Create an empty workbook
Workbook wb = Workbooks.createEmptyWorkbook();

// Get a (possibly new) sheet
IcySpreadSheet sheet = Workbooks.getSheet(wb, "Test");

// Set the header row (all at once, easier to write!)
sheet.setRow(0, "Col 0", "Col 1", "Some other column"); // etc.

// Assign a few cell values
// NB: give any object (unknown types are converted to text)
sheet.setValue(0, 0, "Name");
sheet.setValue(1, 0, 3);

// Need to insert a formula?
// NB: this is the standard formula syntax. The first (corner) cell is called "A1"
sheet.setFormula(1, 1, "A2 * A2");

// How about changing the background color?
sheet.setFillColor(1, 0, Color.cyan);

// Finally, show the workbook on screen
// with a nice window title and whether the table should be editable
Workbooks.show(wb, "Workbook test", false);
}

Copyright note: the 'Workbooks' icon is a modified version of the K-spread icon available at http://openiconlibrary.sourceforge.net

Resources needing this

Leave a Review

Leave a review
Cancel review
View full changelog
Close changelog

Changelog

  • Version 3.4.6.0 • Released on: 2019-03-20 14:59:57
    Download
    Description:

    Default workbook format for VarWorkBook is now XLSX (not anymore XLS, check if that bring regression).

  • Version 3.4.5.0 • Released on: 2018-08-06 10:52:40
    Download
    Description:

    Fixed another memory leak on workbook refresh

  • Version 3.4.4.0 • Released on: 2018-08-03 16:42:15
    Download
    Description:

    Fixed a memory leak due to internal use of EzDialog

  • Version 3.4.3.0 • Released on: 2017-07-12 16:34:55
    Download
    Description:

    Fixed an issue causing the same workbook to fail to save twice (second time would cause an error)

  • Version 3.4.2.0 • Released on: 2016-11-18 16:24:25
    Download
    Description:

    Added an explicit error message when trying to merge files of different format

  • Version 3.4.1.0 • Released on: 2016-09-29 09:56:54
    Download
    Description:

    * Fixed an issue when switching pages in the GUI (the formula field would not know what to display)
    * Fixed issue when merging sheets into a text file (was read using the incorrect Workbook format)
    * Some minor housekeeping

  • Version 3.4.0.0 • Released on: 2016-07-25 14:44:33
    Download
    Description:

    * Workbooks should (finally!) support the XLSX format (and extend beyond the 65535-line limit).
    * Workbooks now depends on the (new) Apache POI plugin

  • Version 3.3.7.0 • Released on: 2016-07-12 15:57:57
    Download
    Description:

    Added several plotting features:
    - 2-variable histograms
    - 2-variable scatter plots
    - 3-variable scatter plots

  • Version 3.3.6.0 • Released on: 2016-07-08 16:05:41
    Download
    Description:

    Fixed an issue causing the histogram to focus only on the first sheet of the workbook instead of the active sheet

  • Version 3.3.5.0 • Released on: 2016-06-23 17:33:50
    Download
    Description:

    Yet another attempt to improve the histogram display

  • Version 3.3.4.0 • Released on: 2016-06-23 15:23:02
    Download
    Description:

    Improved the way histogram range is calculated

  • Version 3.3.3.0 • Released on: 2016-06-10 14:18:40
    Download
    Description:

    Fixed an issue causing boolean cell values (false/true) to display as "0.0"

  • Version 3.3.2.0 • Released on: 2016-06-01 15:32:36
    Download
    Description:

    Another potential fix for the Windows-centric file merging issues...

  • Version 3.3.1.0 • Released on: 2016-05-26 12:17:49
    Download
    Description:

    Attempt to fix the "file opened" issue on Windows

  • Version 3.3.0.0 • Released on: 2016-05-26 11:52:03
    Download
    Description:

    * Add a utility function to collect all numeric values from a given column
    * EXPERIMENTAL: new plotting functionality (currently provides 1D histogram plots for any column)

  • Version 3.2.4.0 • Released on: 2016-05-18 13:41:15
    Download
    Description:

    Ugly Bugfix to the latest class loading issue: reverting back to the (legacy) XLS format (and its limitations, most notably the limit in maximum number of lines)

  • Version 3.2.3.1 • Released on: 2016-05-18 10:27:22
    Download
    Description:

    Re-uploaded jar file

  • Version 3.2.3.0 • Released on: 2016-04-29 10:48:50
    Download
    Description:

    Updated to the latest version of the POI library (brought by Icy 1.8)

  • Version 3.2.2.0 • Released on: 2016-02-11 09:06:17
    Download
    Description:

    New API to remove all rows in a sheet

  • Version 3.2.1.0 • Released on: 2016-02-10 10:28:30
    Download
    Description:

    Fixed a cell-formatting issue

  • Version 3.2.0.0 • Released on: 2016-01-12 15:50:49
    Download
    Description:

    * Improved support for XSSF (XLSX) format
    * Included XMLBeans 2.6.0 binaires into archive
    * Included DOM4J 2.0.0-RC1 binaries into archive

  • Version 3.1.4.0 • Released on: 2015-12-22 14:50:49
    Download
    Description:

    Fixed an issue causing column sorting not to work properly

  • Version 3.1.3.0 • Released on: 2015-12-14 11:38:08
    Download
    Description:

    * New: Workbooks.open(File) and Workbooks.open(String)
    * Added test method to test static/script access (useful for API learners)

  • Version 3.1.2.0 • Released on: 2015-10-26 14:14:36
    Download
    Description:

    Merge options are now offered wen saving workbooks in standalone mode

  • Version 3.1.1.0 • Released on: 2015-07-29 09:47:34
    Download
    Description:

    Display a proper error message when no workbook is available to save

  • Version 3.1.0.0 • Released on: 2015-07-11 19:48:45
    Download
    Description:

    * Added color support
    * Fixed incorrect cell value displayed after sorting rows
    * Cell background color is now displayed in the application

  • Version 3.0.1.0 • Released on: 2015-07-06 14:00:41
    Download
    Description:

    The open button is now hidden for read-only editors

  • Version 3.0.0.0 • Released on: 2015-06-30 01:33:41
    Download
    Description:

    A major update for some major new features!
    * Reworked editor component:
    - Tables are now preserved instead of being recreated on every update (*much* faster)
    - Integrated load/save buttons
    - Fixed column sorting
    - Column selector remains visible after showing/hiding a column
    * Plugin uses the standard EzPlug skin
    * New high-level API to manipulate workbook sheets
    * Export is now possible as text or spreadsheet (to match the WorkbookToFile block)
    * Other performance improvements and code cleaning

  • Version 2.3.5.0 • Released on: 2015-03-19 13:54:24
    Download
    Description:

    Fixed the value sorting process when clicking on a column header

  • Version 2.3.4.0 • Released on: 2015-03-04 13:59:28
    Download
  • Version 2.3.3.0 • Released on: 2015-01-30 13:57:14
    Download
    Description:

    Fixed an issue with some empty cells

  • Version 2.3.2.0 • Released on: 2015-01-13 10:58:20
    Download
    Description:

    Fixed 4000 cell-style limitation (only a handful are actually ever used)

  • Version 2.3.1.0 • Released on: 2015-01-12 10:49:59
    Download
    Description:

    Fixed the name of an enumeration variable (renamed by mistake)

  • Version 2.3.0.0 • Released on: 2015-01-11 23:50:25
    Download
    Description:

    * Added static methods for script access
    * Workbooks can now be merged when saved to disk
    * Text export adjusted to mark sheet names with "=="
    * Multiple sheets will be read from text files if marked with "=="

  • Version 2.2.0.0 • Released on: 2015-01-08 16:29:53
    Download
    Description:

    * Fixed formulas not working properly
    * Added row header
    * Added option to use the first data row as header (useful for sorting purposes)
    * Formula box is now always visible (it remains not editable)

  • Version 2.1.0.0 • Released on: 2015-01-07 15:45:09
    Download
    Description:

    Fixed an issue where some rows would sometimes not display (although they exist in the data table)

  • Version 2.0.2.0 • Released on: 2014-12-02 17:56:08
    Download
    Description:

    Improved error messages on save failure

  • Version 2.0.1.0 • Released on: 2014-10-21 23:52:01
    Download
    Description:

    Removed deprecated calls

  • Version 2.0.0.0 • Released on: 2014-02-11 09:35:23
    Download
    Description:

    Improved graphical interface:
    - More responsive when refreshing
    - Columns can be hidden
    WorkbookToFile block:
    - Option to save as spreadsheet or text
    - Text file is now tab-delimited
    - Now accessible within scripts

  • Version 1.0.0.1 • Released on: 2013-01-28 12:59:12
    Download
    Description:

    Updated plugin classname

  • Version 1.0.0.0 • Released on: 2013-01-28 11:46:10
    Download
    Description:

    Cleaned project (renamed from POI) and fixed dependencies (EzPlug was missing)

  • Version 0.0.1.0 • Released on: 2013-01-11 17:39:01
    Download