[CodeStudy] Scala Excel Read: POI XSSF

Published: November 11, 2020 by SingularityKChen (Last updated: November 11, 2020 )

• Categories:
• CodeStudy 12

• Tags:
• Chisel 22
• POI 1
• Scala 10

• Scala Excel Read
  • Import
  • Read a workbook
  • Read a sheet
  • Read a row or a special cell
  • Get the total row and column numbers
  • Filter empty cells

Scala Excel Read

In Chisel tester, sometime we need to read some data from excel. In these situation, we need to use poi APIs of Java to read the Excel files.

Import

We need to import both Java io and poi.

import java.io.{File, FileInputStream}
import org.apache.poi.xssf.usermodel.{XSSFSheet, XSSFWorkbook}

Read a workbook

First step, new a file. Then using the poi to read this file.

val excelFile = new File(excelFileName)
val excelWorkbook = new XSSFWorkbook(new FileInputStream(excelFile))

Read a sheet

You can using either .getSheet("sheet name") or .getSheetAt(sheet_idx) to read a sheet.

val sheet: XSSFSheet = excelWorkbook.getSheet("sheetName")

Read a row or a special cell

Simply using sheet.getRow(rowIdx) and row.getCell(columnIdx) to get the row and special cell.

NOTICE: the index starts at zero.

If you want to specify the data type of this fill, you can using some function like cell.getRawValue or cell.getNumericCellValue or even cell.toString.

For more usage, you can just search cell.getValue in the IDEA.

Get the total row and column numbers

The row number can be obtained by sheet.getLastRowNum.

For one row, you can get the column number by row.getLastCellNum.

Filter empty cells

In some cases, there are empty or null cells and we don't want them.

We can judge them by cell.getStringCellValue.isEmpty.

Hence, if we need to filter them:

val row = sheet.getRow(rowIdx)
val columnNum = row.getLastCellNum
val nonemptyCells = Range(0, columnNum).map(x => row.getCell(x)).filter(x => !x.getStringCellValue.isEmpty)