ExTra: The Expenses Tracker

Object Model
ExTra provides a number of top-level references which give you access to the various objects in the model. These are:

sheet - the expense sheet itself, giving access to the name of the sheet and the start and end dates.

expenses - the collection of expense items belonging to this expense sheet. Each item has the expected fields like the date, description and amount as well as links to the currency, category and customer/project.

categories - a collection of all the categories referenced by the expense sheet. Iterating through this collection allows you to build summaries split by category.

customers - a collection of all the customers/projects referenced by the expense sheet. Iterating through this collection allows you to build summaries split by customer/project.

cashbought - a collection of all the cash bought referenced by the expense sheet. Iterating through this collection allows you to build a summary of exchange rates used.

Fields are referenced using dot notation, so sheet.fromDate is the start date for the expense sheet. Note that the date will be formatted according to the Excel cell format in the template. Objects are similarly referenced using dot notation, so expense.currency.code is the 3-letter currency code for a given expense. To reference these values from a cell, we enclose them in ${} e.g. ${expense.customer.name}.

In order to iterate through a collection of objects, we need to define an area to represent a template row, which will be replicated for each object in the collection. This is done using a comment and specifies the collection to iterate through, the name to give to the currency object and the last cell in the row (the comment being placed on the first cell). For example:

jx:each(items="expenses" var="expense" lastCell="L10")

Will iterate through the collection of expense items, assinging the name expense to the current item. The comment is placed on cell L1 and the last cell will be L10, so the 10 cells will be replicated for each expense item in the collection.

Finally we need to define the complete area of the sheet and this is done by placing a comment in cell A1 with the text:

jx:area(lastCell="L23")

There is much more that can be done with JXLS, including creating formulas for each row and creating multi-sheet workbooks. To read more about this, see the documentation for JXLS here.

 

Contents Previous: Templates