= Excel

:Notice: Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements. See the NOTICE file distributed with this work for additional information regarding copyright ownership. The ASF licenses this file to you 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.

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

[source,xml,subs="attributes+"]
.pom.xml
----
<dependencyManagement>
    <dependencies>
        <dependency>
            <groupId>org.apache.causeway.extensions</groupId>
            <artifactId>causeway-extensions-excel</artifactId>
            <scope>import</scope>
            <type>pom</type>
            <version>{page-causewayprevv3}</version>
        </dependency>
    </dependencies>
</dependencyManagement>
----

[#dependencies]
=== 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 xref:userguide::modules.adoc[module]:

For xref:export-and-import[export/import]:

* add this dependency:
+
[source,xml,subs="attributes+"]
.pom.xml
----
<dependencies>
    <dependency>
        <groupId>org.apache.causeway.extensions</groupId>
        <artifactId>causeway-extensions-excel-applib</artifactId>
    </dependency>
</dependencies>
----

* and `@Import` this module:
+
[source,java]
.MyModule.java
----
@Configuration
@Import({
    CausewayModuleExtExcelApplib.class,
    // ...
})
public class MyModule { ... }
----


For xref:fixture-support[fixture support]:

* add this dependency:
+
[source,xml,subs="attributes+"]
.pom.xml
----
<dependencies>
    <dependency>
        <groupId>org.apache.causeway.extensions</groupId>
        <artifactId>causeway-extensions-excel-testing</artifactId>
    </dependency>
</dependencies>
----

* and `@Import` this module:
+
[source,java]
.MyModule.java
----
@Configuration
@Import({
    CausewayModuleExtExcelTesting.class,
    // ...
})
public class MyModule { ... }
----



[#export-and-import]
== Usage: Export and Import

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

[source,java]
----
@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:

[source,java]
.TransactionImportExportManager.java
----
@Named("transaction.TransactionImportExportManager")
@DomainObject(nature = Nature.VIEW_MODEL)
public class TransactionImportExportManager {

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

    // ...
}
----
<.> 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:

[source,java]
.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:

[source,java]
.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;
}
----



[#fixture-support]
== 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`

[source,java]
----
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

* xref:testing:fixtures:about.adoc[] (in the xref:testing::about.adoc[Testing Guide]).
