Read/Write in Excel Sheet Using Apache POI With Scala

Yes, you read the title it right — using Apache POI, you can easily read and write in an MS Excel file using Java/Scala.

So, before we get started with the implementation, let's have a quick introduction of Apache POI.

Apache POI is a Java API for manipulating several file formats based upon the Office Open XML standards (OOXML) and Microsoft's OLE 2 Compound Document format (OLE2). In short, you can read and write MS Excel files using Java.

Apache POI contains classes and methods to work on all OLE2 compound documents of the MS Office.

Let's get started with the implementation:

1. First of all, you need to include the dependency in your build.sbt:

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

2. Once you have the XSSFSheet object, you can get  row(getRow), cell(getCell) , and the value of the cell by using setCellValuein the excel sheet.
   You can generate your own data or manipulate your existing data to write it in the excel file. In the below code snippet, I am generating some dummy data for storing it in the excel sheet and then simply writing it in the excel file. Keep in mind that the getRow  method represents the one row ahead of the given input on a sheet. For getRow(3) — this method represents the fourth row on a sheet instead of the third row. If you ask for a row that is not defined, it will give the null.

  val dummyData = (for (i <- 1 to 5)
    yield List(i, i, i, i, i, i, i, i, i, i, i, i)).toList

for (r <- 22 to 26; c <- 1 to 12) {
    val row = sheet.getRow(r - 1)
   try { 
    val cell = row.getCell(c)
        cell.setCellValue(dummyData(r - 22)(c - 1))
    } catch {
      case ex: Exception =>
        val cell = row.createCell(c)
        cell.setCellValue(dummyData(r - 22)(c - 1))
    }
 }

  inputStream.close()
  val fos = new FileOutputStream("""D:\xlsx\test.xlsx""")
  workbook.write(fos)
  fos.close()

Thanks, guys, for reading this post. I hope you found this article helpful.

Happy coding!