Skip to main content

Writing Excel files

Start by importing scalaql:

import scalaql._
import scalaql.excel.CellResolutionStrategy

// Docs classes

// Imports for examples
import java.nio.file.Paths
import java.time.LocalDate

A beautiful case is how it's now easy to generate an Excel report! Let's do some aggregations.

Assume you would like to have the following report:

case class OrderReport(region: String, records: List[OrdersPerDate])

case class OrdersPerDate(
date: LocalDate,
totalProfitPerDate: BigDecimal,
records: List[RepresentativeOrders])

case class RepresentativeOrders(
rep: String,
totalItemsPerRep: Int,
totalProfitPerRep: BigDecimal,
avgOrderPrice: BigDecimal)

You could generate it with the following Query:

val reportAggregation: Query[From[OrderInfo], OrderReport] = select[OrderInfo]
.aggregate { (region, orders) =>
.report(_.orderDate, _.rep)((date, rep, orders) =>
orders.sumBy(_.units) &&
orders.sumBy(order => order.unitCost * order.units) &&
orders.avgBy(order => order.unitCost * order.units)
).map { case (totalItems, totalProfit, avgPrice) =>
RepresentativeOrders(rep, totalItems, totalProfit, avgPrice)
.combine((date, ordersPerRep) =>
ordersPerRep.sumBy(_.totalProfitPerRep) &&
).map { case (totalProfit, ordersList) =>
OrdersPerDate(date, totalProfit, ordersList)
.map(ordersPerDate => OrderReport(region, ordersPerDate))
// reportAggregation: Query[From[OrderInfo], OrderReport] = FROM(ExcelData::OrderInfo) -> GROUP BY(String) -> AGGREGATE(MdocSession::App::OrderReport)

Then you could simply write it to an Excel file:
NOTE: The Excel file used for this example has UpperCased headers.
NOTE 2: Before writing, we would specify the correct headers naming for the report:

val ordersPath = Paths.get("docs/src/main/resources/orders_data.xlsx")
// ordersPath: java.nio.file.Path = docs/src/main/resources/orders_data.xlsx
val excelReportPath = Paths.get("docs/target/orders_report.xlsx")
// excelReportPath: java.nio.file.Path = docs/target/orders_report.xlsx

.option(headers = true)

It will generate the following table: Report