Invalid CellReference: A00_ALL

[lonly197]

When I read a worksheet with the name "A00_ALL", a "java.lang.IllegalArgumentException: Invalid CellReference: A00_ALL" exception occurred.

    val conf = new SparkConf().setMaster("local").setAppName(getClass.getName)
    val spark = SparkSession.builder.config(conf).getOrCreate()

    print("Loading File...")
    val file = "file:/Users/xxx/data/a00_all.xlsx"
    val data = spark.read.format("com.crealytics.spark.excel")
      .option("dataAddress", "A00_ALL")
      .option("useHeader", "true")
      .option("inferSchema", "true")
      .load(file)

    print("Print Schema")
    data.printSchema()

Exception in thread "main" java.lang.IllegalArgumentException: Invalid CellReference: A00_ALL
	at org.apache.poi.ss.util.CellReference.separateRefParts(CellReference.java:395)
	at org.apache.poi.ss.util.CellReference.<init>(CellReference.java:113)
	at org.apache.poi.ss.util.AreaReference.<init>(AreaReference.java:59)
	at com.crealytics.spark.excel.DataLocator$$anonfun$parseRangeAddress$2.apply(DataLocator.scala:48)
	at com.crealytics.spark.excel.DataLocator$$anonfun$parseRangeAddress$2.apply(DataLocator.scala:48)
	at scala.util.Try.getOrElse(Try.scala:79)
	at com.crealytics.spark.excel.DataLocator$.parseRangeAddress(DataLocator.scala:48)
	at com.crealytics.spark.excel.DataLocator$.apply(DataLocator.scala:63)
	at com.crealytics.spark.excel.DefaultSource.createRelation(DefaultSource.scala:29)
	at com.crealytics.spark.excel.DefaultSource.createRelation(DefaultSource.scala:18)
	at com.crealytics.spark.excel.DefaultSource.createRelation(DefaultSource.scala:12)
	at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:341)
	at org.apache.spark.sql.DataFrameReader.loadV1Source(DataFrameReader.scala:239)
	at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:227)
	at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:174)

[Qurashetufail]

Hi @lonly197,

Is this issue resolved now? Please let me know how you resolved this issue.
I am using spark 2.2.2 version
Thanks.
Tufail

[cerveada]

I think dataAddress should have different format than is used here, take a look at the examples in readme of this project.

[quanghgx]

Hi @lonly197 and @Qurashetufail
I think @cerveada is right. For the original worksheet in the screenshot, this one should works

   .option("dataAddress", "'A00_ALL'!A1")

Going to resolve this ticket, feel free to reopen it in case of further issue.