isis-module-excel

This module, intended for use with Apache Isis, provides a domain service so that a collection of (view model) object scan be exported to an Excel spreadsheet, or recreated by importing from Excel.

It also provides a fixture to allow data to be imported from an Excel spreadsheet, with each row either corresponding to a persistent entity or alternatively to a view model which in turn persists data.

The underlying technology used is Apache POI.

Screenshots

The following screenshots show an example app’s usage of the module.

Installing the Fixture Data

The fixture data creates a set of todo items in various categories:

Exporting items using the (example) bulk update manager

The example app has a bulk update manager as a wrapper around the ExcelService:

The (example) bulk update manager allows the end-user to define a criteria to exporting a (sub)set of items:

which are then downloaded …

... and can be viewed in Microsoft Excel:

Importing Exporting Excel

Using Excel the user can update data:

... and the use the (example) bulk update manager to import:

specifying the updated spreadsheet in the dialog:

View models represent the Excel rows

For each row in the spreadsheet the ExcelService instantiates a corresponding view model.

The view model can then provide a bulk apply action…

to update the corresponding entity:

How to run the Demo App

The prerequisite software is:

Java JDK 8 (>= 1.9.0) or Java JDK 7 (<= 1.8.0)
- note that the compile source and target remains at JDK 7
maven 3 (3.2.x is recommended).

To build the demo app:

git clone https://github.com/isisaddons/isis-module-excel.git
mvn clean install

To run the demo app:

cd webapp
mvn jetty:run

Then log on using user: sven, password: pass

How to configure/use

You can either use this module "out-of-the-box", or you can fork this repo and extend to your own requirements.

"Out-of-the-box"

To use "out-of-the-box":

update your classpath by adding this dependency in your dom project’s pom.xml:

<dependency>
    <groupId>org.isisaddons.module.excel</groupId>
    <artifactId>isis-module-excel-dom</artifactId>
    <version>1.13.0</version>
</dependency>

if using AppManifest, then update its getModules() method:

@Override
public List&lt;Class&lt;?&gt;&gt; getModules() {
    return Arrays.asList(
        ...
        org.isisaddons.module.excel.ExcelModule.class,
    );
}

otherwise, update your WEB-INF/isis.properties:

isis.services-installer=configuration-and-annotation
isis.services.ServicesInstallerFromAnnotation.packagePrefix=
                ...,\
                org.isisaddons.module.excel,\
                ...

Check for later releases by searching Maven Central Repo.

"Out-of-the-box" (-SNAPSHOT)

If you want to use the current -SNAPSHOT, then the steps are the same as above, except:

when updating the classpath, specify the appropriate -SNAPSHOT version:
```
<version>1.14.0-SNAPSHOT</version>
```

add the repository definition to pick up the most recent snapshot (we use the Cloudbees continuous integration service). We suggest defining the repository in a <profile>:

<profile>
    <id>cloudbees-snapshots</id>
    <activation>
        <activeByDefault>true</activeByDefault>
    </activation>
    <repositories>
        <repository>
            <id>snapshots-repo</id>
            <url>http://repository-estatio.forge.cloudbees.com/snapshot/</url>
            <releases>
                <enabled>false</enabled>
            </releases>
            <snapshots>
                <enabled>true</enabled>
            </snapshots>
        </repository>
    </repositories>
</profile>

Forking the repo

If instead you want to extend this module’s functionality, then we recommend that you fork this repo. The repo is structured as follows:

pom.xml - parent pom
dom - the module implementation, depends on Isis applib
fixture - fixtures, holding a sample domain objects and fixture scripts; depends on dom
integtests - integration tests for the module; depends on fixture
webapp - demo webapp (see above screenshots); depends on dom and fixture

Only the dom project is released to Maven Central Repo. The versions of the other modules are purposely left at 0.0.1-SNAPSHOT because they are not intended to be released.

`ExcelService` API

The ExcelService is intended for use by domain object classes.

API

The API exposed by ExcelService breaks into two.

Import

The first set of methods allow domain objects to be read (imported) from an Excel workbook:

public class ExcelService {
    public <T> List<T> fromExcel(                       // (1)
        final Blob excelBlob,
        final Class<T> cls) { ... }
    public <T> List<T> fromExcel(                       // (2)
        final Blob excelBlob,
        final WorksheetSpec worksheetSpec) { ... }
    public List<List<?>> fromExcel(                     // (3)
        final Blob excelBlob,
        final List<WorksheetSpec> worksheetSpecs) { ... }
    ...
}

converts a single-sheet workbook into a list of domain objects
converts a single-sheet workbook into a list of domain objects, using WorksheetSpec (discussed below)
converts a multiple-sheet workbook into a list of list of domain objects.

The WorksheetSpec is a data structure that specifies what is on each worksheet of the Excel workbook (ie which sheet of the workbook to read to obtain the domain objects):

public class WorksheetSpec {
    public <T> WorksheetSpec (
        final Class<T> cls,                 // (1)
        final String sheetName) { ... }     // (2)
    ...
}

the class of those domain objects
the name of the sheet to be read. If omitted, then the simple name of the domain object class will be used. In all cases the sheet name must be 30 characters or less in length.

Export

The second set of methods allow domain objects to be written out (exported) to an Excel workbook:

public class ExcelService {
    ...
    public <T> Blob toExcel(                                            // (1)
            final List<T> domainObjects,
            final Class<T> cls,
            final String fileName) { ... }
    public <T> Blob toExcel(                                            // (2)
            final WorksheetContent worksheetContent,
            final String fileName) { ... }
    public Blob toExcel(
            final List<WorksheetContent> worksheetContents,             // (3)
            final String fileName)  { ... }

converts a list of domain objects to a single-sheet workbook, specifying the type of those domain objects.
converts a list of domain objects to a single-sheet workbook, using WorksheetContent (discussed below)
converts a list of worksheet contents to a multi-sheet workbook

The fileName provided is used as the name of the returned Blob

Here WorksheetContent is a data structure that wraps the list of domain objects to be exported along with the afore-mentioned WorksheetSpec:

public class WorksheetContent {
    public <T> WorksheetContent(
        final List<T> domainObjects,        // (1)
        final WorksheetSpec spec) { ... }   // (2)
    ...
}

the list of domain objects to be exported as an excel sheet
the WorksheetSpec, describing the class of those domain objects and the worksheet name to use

Usage

Given:

public class ToDoItemExportImportLineItem extends AbstractViewModel { ... }

which are wrappers around ToDoItem entities:

final List<ToDoItem> items = ...;
final List<ToDoItemExportImportLineItem> toDoItemViewModels =
    Lists.transform(items,
        new Function<ToDoItem, ToDoItemExportImportLineItem>(){
            @Override
            public ToDoItemExportImportLineItem apply(final ToDoItem toDoItem) {
                return container.newViewModelInstance(
                    ToDoItemExportImportLineItem.class,
                    bookmarkService.bookmarkFor(toDoItem).getIdentifier());
            }
        });

then the following creates an Isis Blob (bytestream) containing the spreadsheet of these view models:

return excelService.toExcel(
         toDoItemViewModels, ToDoItemExportImportLineItem.class, fileName);

and conversely:

Blob spreadsheet = ...;
List<ToDoItemExportImportLineItem> lineItems =
    excelService.fromExcel(spreadsheet, ToDoItemExportImportLineItem.class);

recreates view models from a spreadsheet.

Alternatively, more control can be obtained using WorksheetSpec and WorksheetContent:

WorksheetSpec spec = new WorksheetSpec(ToDoItemExportImportLineItem.class, "line-items");

// export
WorksheetContents contents = new WorkbookContents(toDoItemViewModels, spec);
Blob spreadsheet = excelService.toExcel(contents, fileName);

// import
List<List> objects = excelService.fromExcel(spreadsheet, Collections.singletonList(spec));
List<ToDoItemExportImportLineItem> items = objects.get(0);

`ExcelFixture`

The ExcelFixture is intended for use as part of the application’s fixtures, as used for prototyping/demos and for integration tests. Behind the scenes it (re)uses the ExcelService.

API

The constructor for the ExcelFixture is:

public class ExcelFixture {
    public ExcelFixture(
        final URL excelResource,                        // (1)
        final Class... classes) {                       // (2)
            ...
        }
    }
    public void setExcelResourceName(String rn) { ... } // (3)
}

the URL to the Excel spreadsheet
a list of classes to process each of the sheets in the spreadsheet.
optionally, specify the name of the sheet. This is used only to disambiguate any results added to the FixtureResultList (displayed in the UI) if multiple spreadsheets are loaded using different ExcelFixture instances.

Each of the classes must either be a persistable entity or must implement the ExcelFixtureRowHandler interface:

public interface ExcelFixtureRowHandler {
    List<Object> handleRow(
            final FixtureScript.ExecutionContext executionContext,  // (1)
            final ExcelFixture excelFixture,                        // (2)
            final Object previousRow);                              // (3)
}

to look up execution parameters, and to call addResult(…) (to make results available in the UI)
provided principally so that addResult(…) can be called.
to support sparsely populated spreadsheets where a null cell means to use the value from the previous row. Particularly useful for spreadsheets that group together multiple entities (eg category/subcategory/item).

The fixture is instantiated and executed in the usual way, as per any other fixture script.

The fixture uses the class name to lookup the sheet of the workbook:

it first tries to find a sheet with the class' simpleName
if a sheet cannot be found, and if the class' simpleName ends with "RowHandler", then it will look for a sheet without this suffix.

For example, the class ExcelModuleDemoToDoItemRowHandler will match a sheet named "ExcelModuleDemoToDoItemRowHandler".

Note	Excel sheet names can be no longer than 30 characters

Assuming the sheet has been located, the fixture will instantiate an instance of the class for each row, and set the properties of the sheet according to the headers. If the class is persistable, it will then attempt to persist the object using DomainObjectContainer#persist(…). Otherwise (where the class implements ExcelFixtureRowHandler), the handleRow(…) method will be called.

The fixture makes all created objects available to the caller through two accessors:

getObjects() returns all objects created by any of the sheets
getObjects(Class) returns all objects created by an entity/row handler for a given sheet

Usage

The ExcelFixture is used as follows:

final URL excelResource = Resources.getResource(getClass(), "ToDoItems.xlsx");                              // (1)
final ExcelFixture excelFixture = new ExcelFixture(excelResource, ExcelModuleDemoToDoItemRowHandler.class); // (2)
executionContext.executeChild(this, excelFixture);                                                          // (3)
List<Object> items = excelFixture.getObjects(ExcelModuleDemoToDoItemRowHandler.class);                      // (4)

eg using google guava library
expect a single sheet
execute in the usual way
obtain the objects created by the ExcelModuleDemoToDoItemRowHandler for its corresponding sheet

where:

public class ExcelModuleDemoToDoItemRowHandler implements ExcelFixtureRowHandler {          // (1)
    ...                                                                                     // (2)
    @Override
    public List<Object> handleRow(
            final FixtureScript.ExecutionContext executionContext,                          // (3)
            final ExcelFixture fixture,
            final Object previousRow) {
        final ExcelModuleDemoToDoItem toDoItem = ...;
        executionContext.addResult(fixture, todoItem);                                      // (4)
        return Collections.<Object>singletonList(toDoItem);                                 // (5)
    }
    ..                                                                                      // (6)
}

implement the ExcelFixtureRowHandler interface
getters and setters omitted
ExecutionContext can be used to pass parameters down to the row handler, and to call addResult
make available in the UI
return a list of objects instantiated by this row handler.
eg inject domain services/repositories to delegate to for instantiating objects

See also the Excel wicket extension, which makes every collection downloadable as an Excel spreadsheet.

Change Log

1.13.1 - released against Isis 1.13.0; includes #15 (multiple sheets)
1.13.0 - released against Isis 1.13.0
1.12.0 - released against Isis 1.12.0
1.11.0 - released against Isis 1.11.0
1.10.0 - released against Isis 1.10.0; includes ExcelFixture
1.9.0 - released against Isis 1.9.0
1.8.0 - released against Isis 1.8.0
1.7.0 - released against Isis 1.7.0
1.6.0 - re-released as part of isisaddons, with classes under package org.isisaddons.module.excel

Legal Stuff

License

Copyright 2014-2016 Dan Haywood

Licensed under the Apache License, Version 2.0 (the
"License"); you may not use this file except in compliance
with the License.  You may obtain a copy of the License at

    http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing,
software distributed under the License is distributed on an
"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
KIND, either express or implied.  See the License for the
specific language governing permissions and limitations
under the License.

Dependencies

In addition to Apache Isis, this module depends on:

org.apache.poi:poi (ASL v2.0 License)
org.apache.poi:poi-ooxml (ASL v2.0 License)
org.apache.poi:poi-ooxml-schemas (ASL v2.0 License)

Maven deploy notes

Only the dom module is deployed, and is done so using Sonatype’s OSS support (see user guide).

Release to Sonatype’s Snapshot Repo

To deploy a snapshot, use:

pushd dom
mvn clean deploy
popd

The artifacts should be available in Sonatype’s Snapshot Repo.

Release an Interim Build

If you have commit access to this project (or a fork of your own) then you can create interim releases using the interim-release.sh script.

The idea is that this will - in a new branch - update the dom/pom.xml with a timestamped version (eg 1.13.0.20161017-0738). It then pushes the branch (and a tag) to the specified remote.

A CI server such as Jenkins can monitor the branches matching the wildcard origin/interim/* and create a build. These artifacts can then be published to a snapshot repository.

For example:

sh interim-release.sh 1.14.0 origin

where

1.14.0 is the base release
origin is the name of the remote to which you have permissions to write to.

Release to Maven Central

The release.sh script automates the release process. It performs the following:

performs a sanity check (mvn clean install -o) that everything builds ok
bumps the pom.xml to a specified release version, and tag
performs a double check (mvn clean install -o) that everything still builds ok
releases the code using mvn clean deploy
bumps the pom.xml to a specified release version

For example:

sh release.sh 1.13.1 \
              1.14.0-SNAPSHOT \
              dan@haywood-associates.co.uk \
              "this is not really my passphrase"

where

$1 is the release version
$2 is the snapshot version
$3 is the email of the secret key (~/.gnupg/secring.gpg) to use for signing
$4 is the corresponding passphrase for that secret key.

Other ways of specifying the key and passphrase are available, see the `pgp-maven-plugin’s documentation).

If the script completes successfully, then push changes:

git push origin master
git push origin 1.13.1

If the script fails to complete, then identify the cause, perform a git reset --hard to start over and fix the issue before trying again. Note that in the dom’s `pom.xml the nexus-staging-maven-plugin has the autoReleaseAfterClose setting set to true (to automatically stage, close and the release the repo). You may want to set this to false if debugging an issue.

According to Sonatype’s guide, it takes about 10 minutes to sync, but up to 2 hours to update search.

johandoornenbal/isis-module-excel

isis-module-excel

Screenshots

Installing the Fixture Data

Exporting items using the (example) bulk update manager

Importing Exporting Excel

View models represent the Excel rows

How to run the Demo App

How to configure/use

"Out-of-the-box"

"Out-of-the-box" (-SNAPSHOT)

Forking the repo

ExcelService API

API

Import

Export

Usage

ExcelFixture

API

Usage

Related Modules

Change Log

Legal Stuff

License

Dependencies

Maven deploy notes

Release to Sonatype’s Snapshot Repo

Release an Interim Build

Release to Maven Central

`ExcelService` API

`ExcelFixture`