Scala org apache poi openxml4j exceptions InvalidFormatException Your InputStream was neither an OLE2 stream nor an OOXML stream

Question

Hi Team,

I am trying to read an excel file using Spark CLI, but I am getting "org.apache.poi.openxml4j.exceptions.InvalidFormatException: Your InputStream was neither an OLE2 stream, nor an OOXML stream" error.

Below is the code I am using:

import com.crealytics.spark.excel

val df = spark.read.format("com.crealytics.spark.excel")
.option("useHeader", "true")
.option("startColumn", 0)
.option("treatEmptyValuesAsNulls", "false")
.option("inferSchema", "false")
.option("location", "/home/Desktop/lucky/logs.xlsx")
.option("addColorColumns", "False")
.load()

Answer 1

Try executing the below code,

def readExcel(file: String): DataFrame = sqlContext.read
    .format("com.crealytics.spark.excel")
​    .option("location", file)
​    .option("useHeader", "true")
​    .option("treatEmptyValuesAsNulls", "true")
​    .option("inferSchema", "true")
​    .option("addColorColumns", "False")
​    .load()

val data = readExcel("path to your excel file")
​
data.show(false)

If you want to know more about Apache Spark Scala, It's highly recommended to go for the Spark Certification Course today.