Excel

Reading tables from excel should work a very similar way to CSV. The default behaviour is a bit of a hail mary. It assumes the excel workbook is rather well behaved, and that a blindly configured apache POI RowIterator will capture appropriate data. You should not expect this method to be robust to blank rows / columns/ data elsewhere in the sheet.

import io.github.quafadas.table.{*, given}

val csv: ExcelIterator[("Column 1", "Column 2", "Column 3"), (String, String, String)] = Excel.resource("SimpleTable.xlsx", "Sheet1")
// csv: ExcelIterator[Tuple3["Column 1", "Column 2", "Column 3"], Tuple3[String, String, String]] = empty iterator
println(csv.toSeq.consoleFormatNt(fansi = false))
// | |    Column 1|    Column 2|    Column 3|
// +-+------------+------------+------------+
// |0|Row 1, Col 1|Row 1, Col 2|Row 1, Col 3|
// |1|Row 2, Col 1|Row 2, Col 2|Row 2, Col 3|
// |2|Row 3, Col 1|Row 3, Col 2|Row 3, Col 3|
// +-+------------+------------+------------+

val csv2 = Excel.resource("Numbers.xlsx", "Sheet1", TypeInferrer.FromAllRows)
// csv2: ExcelIterator[Tuple4["Doubles", "Int", "Longs", "Strings"], *:[Double, *:[Int, *:[Int, *:[String, EmptyTuple]]]]] = empty iterator
println(csv2.toSeq.consoleFormatNt(fansi = false))
// | |Doubles|Int|Longs|Strings|
// +-+-------+---+-----+-------+
// |0|    1.1|  1|    1|   blah|
// |1|    2.2|  2|    3|   blah|
// +-+-------+---+-----+-------+


val range = Excel.resource("Numbers.xlsx", "Sheet1", "A1:C3", TypeInferrer.FromAllRows)
// range: ExcelIterator[Tuple3["Doubles", "Int", "Longs"], *:[Double, *:[Int, *:[Int, EmptyTuple]]]] = empty iterator

println(range.toSeq.consoleFormatNt(fansi = false))
// | |Doubles|Int|Longs|
// +-+-------+---+-----+
// |0|    1.1|  1|    1|
// |1|    2.2|  2|    3|
// +-+-------+---+-----+

One can also read from an absolute path

import io.github.quafadas.table.*
val csv = Excel.absolutePath("path/to/SimpleTable.xlsx", "Sheet1")

Problems and Hints

It is strongly recommended to specify a complete range when working with Excel. e..g.

val range = Excel.resource("Numbers.xlsx", "Sheet1", "A1:C3", TypeInferrer.FromAllRows)

Although this may work,

val norange = Excel.resource("Numbers.xlsx", "Sheet1", TypeInferrer.FromAllRows) val norange = Excel.resource("Numbers.xlsx", "", TypeInferrer.FromAllRows)

Excel is somewhat pathological with regard to sheet boundaries. It is likely this will include blank cells, which will muck up type inference and reading ranges.

In general, trhe implementation here is not robust to Excel's flexibility (reading formula's is unimplemented) and assumes that we are working with a simple, well formed table.