testing - Reading Excel file with Scala

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

1 Answer

  1. Matthew- Reply

    2019-11-14

    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:

    enter image description here

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>