From version 1.2 of Inforama it is possible to use spreadsheet documents as data sources. Spreadsheet documents are "queried" with a SQL-like language and the retrieved data are used to create the result documents. The steps below describe how to set up the Spreadsheet DataSource , and define Data Sets. All the screenshots below are from the sample project which is attached. The sample project's resources folder contains the spreadsheet that is used as a data source.

Setting up a spreadsheet document as a data source

Each Spreadsheet Datasource refers to only one spreadsheet document (one file containing spreadsheet document). In order to set up a new data source go to Data->Connection/Data Sources, press the New button, choose the Spreadsheet document as the type of data source that you want to add and finally point to a file containing the document that you want to use.

Creating spreadsheet data source

Creating Data Sets from a Spreadsheet Data Source

Data sets referring to a spreadsheet data source are defined by a SQL-like queries. Each spreadsheet data source, i.e. file containing spreadsheet, can be thought of as a database while the sheets inside the spreadsheet document can be thought of as database tables.

So, in order to build a data set, a SQL query, must be written. The query should follow the syntax,

 SELECT [select_arguments] FROM [from_argument] WHERE [where_arguments] GROUP_BY [group_by_argument] HAVING [having_arguments] 

The [select_arguments] should be specified a coma separated list of the following expressions: column_name, *, count(*), count(column_name), sum(column_name), min(column_name), max(column_name), avg(column_name)

The meaning of the above is the same as in the SQL querying the regular database, the only difference is that column names shouldn't be prefixed with the name of the table that they belong to.

The [from_argument] should define the name of a single sheet which is to be queried. In the current (1.2) version of Inforama joining multiple sheets isn't yet supported thus there should be always exactly one specified.

The [where_arguments] specify conditions. Each condition should be in the form: column_name operator scalar_value, e.g. WHERE First Name = 'Joe' and Second Name = 'Bloggs'

The the meaning of a [group_by_arguments] is exactly the same as in a standard SQL language (column or columns by which the result of the query should be grouped)

The [having_arguments] specify conditions (in the same way as [where_arguments] do) that referr to the GROUP BY columns.

Examples

All documents described below are part of the attached project. The spreadsheet file, used as a data source, consists of two simple sheets, like shown below.

sheet1

sheet2

The sreadsheet file itself is stored int the sample project's resources folder.

document1

Document listing all employees and all customers in two separate tables. The data sets AllCustomers and allEmployees which are used in this document are specified as follows:

allCustomers :: select * from sheet1 where Type='Customer'

allEmployees :: select * from sheet2 where Type='Employee'

document1 in embed OO editor

the result PDF document

document2

Document listing all customers and employees whose balance (Balance column) is greater then the value of the client provided $P{balance} parameter. The data set usersBal that is used is defined as:

usersBal :: select First Name, Last Name, Balance from sheet1 where Balance > $P{balance} 

document2 in embed OO editor

the result PDF document

document3

Document listing all accounts and their balances (sum on the Amount column) from the sheet2. The data set accounts which is used is defined as:

accounts :: select Account, sum(Amount) from sheet2 group by Account

document3 in embed OO editor

the result PDF document