Reading Excel files

Reading document

Start by importing scalaql:

import scalaql._
import scalaql.excel.CellResolutionStrategy

// Docs classes
import scalaql.docs.ExcelData._

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

Assume you have an Excel file like the following:

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

Which looks like:

Input excel document

To read it, you should first specify ExcelReadConfig:

First, you defined a Query as usual:

val query = select[OrderInfo]
// query: Query[From[OrderInfo], OrderInfo] = FROM(ExcelData::OrderInfo)

Then you can quickly view the file content.
NOTE: This specific Excel file has UpperCased headers.

query
  .show(truncate=false)
  .run(
    from(
      excel
        .read[OrderInfo]
        .option(Naming.Capitalize)
        .option(CellResolutionStrategy.NameBased)
        .file(ordersPath)
    )
  )
// +----------+-------+--------+-------+-----+--------+------+
// |orderDate |region |rep     |item   |units|unitCost|total |
// +----------+-------+--------+-------+-----+--------+------+
// |2021-01-06|East   |Jones   |Pencil |95   |1.0     |189.0 |
// |2021-01-23|Central|Kivell  |Binder |50   |19.0    |999.0 |
// |2021-02-09|Central|Jardine |Pencil |36   |4.0     |179.0 |
// |2021-02-26|Central|Gill    |Pen    |27   |19.0    |539.0 |
// |2021-03-15|West   |Sorvino |Pencil |56   |2.0     |167.0 |
// |2021-04-01|East   |Jones   |Binder |60   |4.0     |299.0 |
// |2021-04-18|Central|Andrews |Pencil |75   |1.0     |149.0 |
// |2021-05-05|Central|Jardine |Pencil |90   |4.0     |449.0 |
// |2021-05-22|West   |Thompson|Pencil |32   |1.0     |63.0  |
// |2021-06-08|East   |Jones   |Binder |60   |8.0     |539.0 |
// |2021-06-25|Central|Morgan  |Pencil |90   |4.0     |449.0 |
// |2021-07-12|East   |Howard  |Binder |29   |1.0     |57.0  |
// |2021-07-29|East   |Parent  |Binder |81   |19.0    |1619.0|
// |2021-08-15|East   |Jones   |Pencil |35   |4.0     |174.0 |
// |2021-09-01|Central|Smith   |Desk   |2    |125.0   |250.0 |
// |2021-09-18|East   |Jones   |Pen Set|16   |15.0    |255.0 |
// |2021-10-05|Central|Morgan  |Binder |28   |8.0     |251.0 |
// |2021-10-22|East   |Jones   |Pen    |64   |8.0     |575.0 |
// |2021-11-08|East   |Parent  |Pen    |15   |19.0    |299.0 |
// |2021-11-25|Central|Kivell  |Pen Set|96   |4.0     |479.0 |
// |2021-12-12|Central|Smith   |Pencil |67   |1.0     |86.0  |
// +----------+-------+--------+-------+-----+--------+------+
// only showing top 20 rows
//

Reading Excel files

Reading document​

Reading document