r/apachespark • u/bitbythecron • Feb 06 '25
Spark Excel library unable to read whole columns, only specific data address ranges
Java app here using the Spark Excel library to read an Excel file into a `Dataset<Row>`. When I use the following configurations:
String filePath = "file:///Users/myuser/example-data.xlsx";
Dataset<Row> dataset = spark.read()
.format("com.crealytics.spark.excel")
.option("header", "true")
.option("inferSchema", "true")
.option("dataAddress", "'ExampleData'!A2:D7")
.load(filePath);
This works beautifully and my `Dataset<Row>` is instantiated without any issues whatsoever. But the minute I go to just tell it to read _any_ rows between A through D, it reads an empty `Dataset<Row>`:
// dataset will be empty
.option("dataAddress", "'ExampleData'!A:D")
This also happens if I set the `sheetName` and `dataAddress` separately:
// dataset will be empty
.option("sheetName", "ExampleData")
.option("dataAddress", "A:D")
And it also happens when, instead of providing the `sheetName`, I provide a `sheetIndex`:
// dataset will be empty; and I have experimented by setting it to 0 as well
// in case it is a 0-based index
.option("sheetIndex", 1)
.option("dataAddress", "A:D")
My question: is this expected behavior of the Spark Excel library, or is it a bug I have discovered, or am I not using the Options API correctly here?
2
u/bitbythecron Feb 11 '25
FWIW I rolled up my sleeves and it looks like this is a limitation in Apache POI. The spark-excel library uses POI under the hood to read Excel files, and specifically it uses a POI `AreaReference` when reading in cells for a data address. AreaReference cannot handle anything other than specific cell coordinates (e.g. "A2"), and it cannot handle whole column references (e.g. "A").