Excel

The Excel module provides the ability to read from Excel spreadsheets, which can be useful for certain use cases, eg bulk import of data. Each row of a spreadsheet can be mapped to a domain object, usually a view model. The view model can validate itself and then be applied.

The module also supports the opposite use case, converting a collection of domain objects into an Excel spreadsheet.

In addition, the library provides fixture support, allowing test data to be specified in a spreadsheet.

Setup

Dependency Management

In your application’s top level pom.xml, add a dependency for this module’s own bill of materials (BOM):

pom.xml
<dependencyManagement>
    <dependencies>
        <dependency>
            <groupId>org.apache.causeway.extensions</groupId>
            <artifactId>causeway-extensions-excel</artifactId>
            <scope>import</scope>
            <type>pom</type>
            <version>2.0.0</version>
        </dependency>
    </dependencies>
</dependencyManagement>

Dependencies / Imports

In the modules of your application that will use the Excel extension, add dependency/ies to pom.xml, and update the @Import of the corresponding module:

For export/import:

  • add this dependency:

    pom.xml
    <dependencies>
    <dependency>
        <groupId>org.apache.causeway.extensions</groupId>
        <artifactId>causeway-extensions-excel-applib</artifactId>
    </dependency>
</dependencies>

  • and @Import this module:

    MyModule.java
    @Configuration
@Import({
    CausewayModuleExtExcelApplib.class,
    // ...
})
public class MyModule { ... }

For fixture support:

  • add this dependency:

    pom.xml
    <dependencies>
    <dependency>
        <groupId>org.apache.causeway.extensions</groupId>
        <artifactId>causeway-extensions-excel-testing</artifactId>
    </dependency>
</dependencies>

  • and @Import this module:

    MyModule.java
    @Configuration
@Import({
    CausewayModuleExtExcelTesting.class,
    // ...
})
public class MyModule { ... }

Usage: Export and Import

Define a view model to act as a DTO. For example:

@Named("transaction.TransactionDto")
@DomainObject(nature = Nature.VIEW_MODEL)
public class TransactionDto {
    @Getter @Setter private String customerRef;
    @Getter @Setter private LocalDate date;
    @Getter @Setter private BigDecimal amount;
}

It’s common to define a "Manager" view model to control the process:

TransactionImportExportManager.java
@Named("transaction.TransactionImportExportManager")
@DomainObject(nature = Nature.VIEW_MODEL)
public class TransactionImportExportManager {

    List<TransactionDto> getTransactions() { ... }  (1)

    // ...
}
1 the transactions to be exported, typically mapped from an entity into the DTO

The action to export this list of transactions would be something like:

TransactionImportExportManager.java
@Action(semantics = SemanticsOf.SAFE)
public Blob exportToExcel(){
    return  excelService.toExcel(
            getTransactionDtos(),
            TransactionDto.class,
            "Sheet1",
            "Transactions.xlsx"
    );
}

And conversely, to obtain DTOs from the spreadsheet:

TransactionImportExportManager.java
@Action(semantics = SemanticsOf.IDEMPOTENT)
@CollectionLayout(paged = -1)
public TransactionImportExportManager importFromExcel(
        @Parameter(fileAccept = ".xlsx")
        final Blob spreadsheet) {
    List<TransactionDto> transactionDtos =
        excelService.fromExcel(spreadsheet, TransactionDto.class, "Sheet1");
    ...
    return this;
}

Usage: Fixture Support

The fixture support is provided through ExcelFixture, an implementation of FixtureScript that is initialized with a spreadsheet and a set of handler classes, one for each sheet. These handler classes implement ExcelFixtureRowHandler

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

The handleRow method is called for each row in the spreadsheet (of the appropriate sheet), and it is being responsible for handling the row of data from the spreadsheet. A simple implementation would just use an injected repository to create a new entity. A more sophisticated implementation would use the values in the spreadsheet to call other fixtures (using executionContext` and excelFixture).

See also