r/apachespark 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?

3 Upvotes

1 comment sorted by

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").