cross
New Foswiki release 2.1.6 is available with important security fixes.
Sourceforge foswiki email lists being discontinued. Subscribe to the new Foswiki announce and discuss lists at MailingLists
TIP SpreadsheetReaderPlugin is not installed on Foswiki.org.

SpreadsheetReaderPlugin

Read data from a spreadsheet

This plugin can read common spreadsheet documents and renders them as HTML. Supported file formats are:

  • xls
  • xlsx
  • ods
  • csv

Examples

%SPREADSHEET{"test.xlsx" sheet="2"}%

Syntax

The %SPREADHSHEET{...}% makro takes the following parameters:

Parameter Description Default
"..." or attachment="..." file name of an uploaded spreadsheet file  
web="...", topic="..." location of the spreadsheet file current topic
password="..." password to unlock a protected spreadsheet  
sep="..." (only csv) column separator for csv files  
strip="on/off" boolean flag to strip whitespaces of a cell content on
quote="..." (only csv) quote character for cells in a csv files  
sheets="..." or sheet="..." sheets to extract (1-based); this may a single sheet or a comma separated list of sheets, or the keyword all 1
rows="..." list of rows to extract data from; this may be a comma separated list of row numbers, a list of row intervals (see explanation below), or the keyword =all" all
cols="..." list of columns to extract data from; this is either a comma separated list of column numbers or letters, a list of column intervals, or the keyword =all" all
class="..." css class to add to the HTML table being rendered foswikiTable
showattrs="on/off" boolean switch to extract cell attributes such as bold, italic, foreground- and background colors, alignment; set this to off to get the raw HTML table only; note that this currently only works reliably for xls and xlsx files on
showindex="on/off" boolean switch to display a row index off
<headerName_include="..." regular expression a column must match for the row to be included in the output (see explanation below)  
<headerName_exclude="..." regular expression a column must not match for the row to be included in the output  

Rows and column intervals

The rows and columns to be displayed may be specified using a list of intervals such as

rows="1,2,5-9,12"

This will select rows 1,2,5,6,7,8,9 and 12.

Similarly columns may be specified using an interval description of uppercase alphabetic letters such as in

cols="-F,K-"

This will select all columns except columns G, H, I, J.

An interval of rows (or columns) can be either a closed interval starting at a specific row up to an ending row, or be a half-open interval such as cols="K-" which will select all columns from K on, or rows=-10 to list the first ten rows.

Filtering rows

The two parameters <headerName_include="..." and <headerName_exclude="..." may be used to filter rows by regular expressions.

Example:

%SPREADSHEET{
  "calculation.xls"
  ITSystems_include="foswiki"
}%

This will list only those rows where the value of the row "IT-Systems" matches the given expression. Note that the actual parameter to depict the column to be checked is generated from the first line of the spreadsheet which is considered to be the header line. To please the attribute parser specification of TML, parameter names are normalized to a certain degree stripping off any non-alpabetic non-numeric characters. That's why the parameter is callded ITSystems_include when reading values of the "IT-Systems" column.

Installation Instructions

You do not need to install anything in the browser to use this extension. The following instructions are for the administrator who installs the extension on the server.

Open configure, and open the "Extensions" section. "Extensions Operation and Maintenance" Tab -> "Install, Update or Remove extensions" Tab. Click the "Search for Extensions" button. Enter part of the extension name or description and press search. Select the desired extension(s) and click install. If an extension is already installed, it will not show up in the search results.

You can also install from the shell by running the extension installer as the web server user: (Be sure to run as the webserver user, not as root!)
cd /path/to/foswiki
perl tools/extension_installer <NameOfExtension> install

If you have any problems, or if the extension isn't available in configure, then you can still install manually from the command-line. See https://foswiki.org/Support/ManuallyInstallingExtensions for more help.

Dependencies

NameVersionDescription
Spreadsheet::Read>=0.78Required
Spreadsheet::ReadSXC>=0.20Required
Spreadsheet::ParseExcel>=0.65Required
Spreadsheet::ParseXLSX>=0.27Required
Text::CSV_XS>=1.35Required

Change History

31 May 2018 fix use of uninitialized variable; fix strip whitespaces
28 May 2018 fix encoding of spreadsheets; implement row and column interval syntax
17 Apr 2018 initial release
Topic revision: r3 - 31 May 2018, MichaelDaum - This page was cached on 14 Aug 2018 - 07:21.

The copyright of the content on this website is held by the contributing authors, except where stated elsewhere. See Copyright Statement. Creative Commons License    Legal Imprint    Privacy Policy