Reading excel file using apache poi in scala

Question

I'm trying to read a excel file using poi SXSSF. For some reason sheet.rowIterator is returning empty iterator even though there are rows in the sheet. Here is the code I have

import java.io.File
import org.apache.poi.xssf.streaming.SXSSFWorkbook
import org.apache.poi.xssf.usermodel.XSSFWorkbook
import scala.collection.JavaConverters._

class ExcelReader {
    final val fileName = "c:\\temp\\data-200.xlsx"
    def read(): Iterator[Contact] = {
        val file = new File(fileName)
        val workBook = new SXSSFWorkbook(new XSSFWorkbook(file),100)
        val sheet = workBook.getSheetAt(0) //this works gets sheet name
        Console.println(s"Sheet Name: ${sheet.getSheetName()}")
        val rowItr = sheet.rowIterator().asScala // this is empty iterator
        for (e <- rowItr) yield Contact(e.getCell(0).getStringCellValue(), 
               e.getCell(1).getStringCellValue(), 
               e.getCell(2).getStringCellValue(), 
               e.getCell(3).getStringCellValue(),
               e.getCell(4).getStringCellValue())
    }
}

Not sure what I'm doing wrong.

https://stackoverflow.com/questions/10053421/how-to-use-poi-sxssf-to-read-a-large-spreadsheet — Axel Richter, Jun 02 '17 at 16:39

score 1 · Answer 1 · answered Jun 02 '17 at 17:05

Here is a simple example of reading excel file that I have tried.

val myFile = new File("/home/sakoirala/Downloads/eu-historical-price-series_en.xls")

  val fis = new FileInputStream(myFile)

  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_BOOLEAN => {
              print(cell.getBooleanCellValue + "\t")
            }
            case Cell.CELL_TYPE_BLANK => {
              print("null" + "\t")
            }
            case _ => throw new RuntimeException(" this error occured when reading ")
            //        case Cell.CELL_TYPE_FORMULA => {print(cell.getF + "\t")}
          }
      }
      println("")
  }

Hope this helps!

Reading excel file using apache poi in scala

1 Answers1