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

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

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