Reading Excel file with Scala

Question

I am writing a quick test that registers a user with the data from a spreadsheet.

The idea is Go to the website > click register > Read excel rows A1 and B1 for email and password > use this data on registration site> finish the registration > log out > Register a new user with information from rows A2 and B2 > continue until rows in the spreadsheet are empty.

I have managed to automate the registration process with random user information and now I just need to make it do the same with the specific email and password taken from the spreadsheet.

I have tried using Apache Poi, but not exactly sure how to use it and how to make it loop itself until the end of the spreadsheet.

This what I have so far but i believe it's wrong:

val myData = new File("/desktop/files.file.xmls")

val fis = new FileInputStream(myData)

val myWorkbook = new HSSFWorkbook(fis)

val mySheet = myWorkbook.getSheetAt(0)

val rowIterator = mySheet.iterator()

while(rowIterator.hasNext){

val row = rowIterator.next()

  val cellIterator = row.cellIterator()

  while(cellIterator.hasNext) {
    val cell = cellIterator.next()
      cell.getCellType match {
        case Cell.CELL_TYPE_STRING => {
          print(cell.getStringCellValue + "\t")
        }
        case Cell.CELL_TYPE_NUMERIC => {
          print(cell.getNumericCellValue + "\t")
        }

        case Cell.CELL_TYPE_BLANK => {
          print("null" + "\t")
        }

      }
  }
  println("")

Answer 1

Just as a note, I'm using poi 3.17. So my build.sbt has

"org.apache.poi" % "poi" % "3.17"
"org.apache.poi" % "poi-ooxml" % "3.17"

in it. If you're using a different version then state so in the question and I'll update my answer.

Here's my example excel file:

First off, imports:

import org.apache.poi.ss.usermodel.{ DataFormatter, WorkbookFactory, Row }
import java.io.File
import collection.JavaConversions._ // lets you iterate over a java iterable

Then you can pull in your file with the WorkbookFactory and get your sheet:

val f = new File("Example.xlsx")
val workbook = WorkbookFactory.create(f)
val sheet = workbook.getSheetAt(0) // Assuming they're in the first sheet here.

Next, if you pay attention to the type of Sheet you'll notice that it implements Iterable<Row> which means that you can just use for with it to loop over all the rows:

for (row <- sheet) {
    // Do things
}

provided that you don't need to return anything from the loop of course. If you need to do that, you should be able to do

sheet.map { row => }

Next, to get the actual value of the cell you'll want a formatter:

val formatter = new DataFormatter()

and then to pull the A column, you call getCell on the row at index 0:

val maybeA = Option(row.getCell(0, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL)) // lift null to None
val maybeB = Option(row.getCell(1, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL))

then I assume you only want to do something if you have both of these cells, in which case you can leverage a for comprehension across these two:

val maybeEmailAndPass = for {
    a <- maybeA
    b <- maybeB
} yield {
    val email = formatter.formatCellValue(a) 
    val pass = formatter.formatCellValue(b)
    (email, pass)
}
println(maybeEmailAndPass)

then if you have something you can do whatever you want with it.

And the above ran on my example gives me

Some((Row1 Email,Row1 Pass))
Some((Row2 Email,Row2 Pass))