isis-module-excel

Table of Contents

• Screenshots
  • Installing the Fixture Data
  • Exporting items using the (example) bulk update manager
  • Importing Exporting Excel
  • View models represent the Excel rows
  • Limited pivot support for Import
• 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
  • 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

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:

Limited pivot support for Import

This module has support for pivot tables (export only) which is demonstrated by:

which are then downloaded and can be viewed in Microsoft Excel:

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.14.2</version>
  </dependency>

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

  @Override
  public List<Class<?>> 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.15.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)
        Blob excelBlob,
        Class<T> cls) { ... }
    public <T> List<T> fromExcel(                       // (2)
        Blob excelBlob,
        WorksheetSpec worksheetSpec) { ... }
    public List<List<?>> fromExcel(                     // (3)
        Blob excelBlob,
        List<WorksheetSpec> worksheetSpecs) { ... }
    public List<List<?>> fromExcel(                     // (4)
        Blob excelBlob,
        WorksheetSpec.Factory factory) { ... }
    public List<List<?>> fromExcel(                     // (5)
        Blob excelBlob,
        WorksheetSpec.Factory factory,
        WorksheetSpec.Sequencer sequencer,
        ) { ... }
    ...
}

1. converts a single-sheet workbook into a list of domain objects

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

3. converts a multiple-sheet workbook into a list of list of domain objects.

4. converts all "matched" worksheets for a multiple-sheet workbook, with the supplied WorksheetSpec.Factory returning the WorksheetSpec to handle the sheet

5. as previous, but with the sheets resequenced using the supplied WorksheetSpec.Sequencer.

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):

WorksheetSpec.java

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

1. the class of those domain objects

2. 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.

The WorksheetSpec.Factory API is:

WorksheetSpec.Factory.java

public class WorksheetSpec {
    ...
    public interface Factory {
        WorksheetSpec fromSheet(String sheetName);  // (1)
    }
}

1. returns the WorksheetSpec indicating how the sheet should be handled, or null otherwise.

And the Worksheet.Sequencer API is simply:

WorksheetSpec.Sequencer.java

public class WorksheetSpec {
    ...
    public interface Sequencer {
        List<WorksheetSpec> sequence(List<WorksheetSpec> specs);
    }
}

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)  { ... }

1. converts a list of domain objects to a single-sheet workbook, specifying the type of those domain objects.

2. converts a list of domain objects to a single-sheet workbook, using WorksheetContent (discussed below)

3. 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)
    ...
}

1. the list of domain objects to be exported as an excel sheet

2. the WorksheetSpec, describing the class of those domain objects and the worksheet name to use

In a like wise manner the following methods allow (annotated) domain objects to be exported to an Excel workbook in a pivot table.

public class ExcelService {
    ...
    public <T> Blob toExcelPivot(
            final List<T> domainObjects,
            final Class<T> cls,
            final String fileName) { ... }
    public <T> Blob toExcelPivot(
            final WorksheetContent worksheetContent,
            final String fileName) { ... }
    public Blob toExcelPivot(
            final List<WorksheetContent> worksheetContents,
            final String fileName)  { ... }

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);

more on the creation of pivot tables

In order to create a pivot table from a list of domain objects (normally Viewmodels) the following annotations on properties can be used.

@PivotRow

Indicates that the property will be used as row label in the pivot table (left most column). This annotation is mandatory and only 1 is allowed.

@PivotColumn(order = ..)

Indicates that the distinct values of the property will be used as column labels in the pivot table. This annotation is mandatory. More than 1 annotation is supported and they will be used in the order specified.

@PivotValue(order = .. , type = ..)

Indicates that the values of the property will be used as pivoted values in the pivot table. This annotation is mandatory. More than 1 annotation is supported and they will be used in the order specified. Type specifies the aggregation type, that defaults to AggregationType.SUM. At the moment the other supported type is AggregationType.COUNT

@PivotDecoration(order = ..)

Indicates that the distinct values of the property will be used as 'extra' values besides the row label. (They 'decorate' the label.) This annotation is optional. More than 1 annotation is supported and they will be used in the order specified. Decoration assumes that all distinct labels are decorated with the same values. This is not enforced however: the first decoration found will be used.

Here is the example used in the demo application

@DomainObject(nature = Nature.VIEW_MODEL)
public class ExcelModuleDemoPivot {

    ...

    @PivotRow
    private ExcelModuleDemoToDoItem.Subcategory subcategory;

    @PivotColumn(order = 1)
    private ExcelModuleDemoToDoItem.Category category;

    @PivotValue(order = 1, type = AggregationType.SUM)
    private BigDecimal cost;

}

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)
}

1. the URL to the Excel spreadsheet

2. a list of classes to process each of the sheets in the spreadsheet.

3. 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)
}

1. to look up execution parameters, and to call addResult(…​) (to make results available in the UI)

2. provided principally so that addResult(…​) can be called.

3. 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)

1. eg using google guava library

2. expect a single sheet

3. execute in the usual way

4. 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)
}

1. implement the ExcelFixtureRowHandler interface

2. getters and setters omitted

3. ExecutionContext can be used to pass parameters down to the row handler, and to call addResult

4. make available in the UI

5. return a list of objects instantiated by this row handler.

6. eg inject domain services/repositories to delegate to for instantiating objects

Related Modules

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

Change Log

• 1.14.2 - released against Isis 1.14.0, fixes #21

• 1.14.1 - released against Isis 1.14.0, fixes #19

• 1.14.0 - released against Isis 1.14.0

• 1.13.6 - maximum length for sheets is 31 chars, not 30 chars.

• 1.13.5.1 - patch for 1.13.5 (error with #toExcelPivot(…​).

• 1.13.5 - released against Isis 1.13.0; includes limited support for pivot tables; #17 (remove overloads for #fromExcel() and #toExcel()).
  

  Note	This release is a minor breaking change to the API. To convert the code, use cls.getSimpleName() as the (now mandatory) value of the sheet name. ensuring that its length does not exceed 30 characters.

• 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.14.2.20170430-0756). 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.2 origin

where

• 1.14.2 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.14.2 \
              1.15.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.14.2

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.