[JAVA] I want to get the value of Cell transparently regardless of CellType (Apache POI)

As long as I work as a Java-loving SIer who supplies systems to Japanese companies that love Excel, Apache POI is inevitable. POI has a class (interface) called Cell that abstracts Excel cells, but it is surprisingly troublesome to retrieve values from this Cell. In particular, it is necessary to call methods according to the display format of __ cells (CellType), and when a method that does not match CellType is called, an exception is thrown immediately __. Was it done (´ ・ ω ・ ｀)

Therefore, I decided to prepare a utility method __ that can transparently get the cell value regardless of __CellType. The simple specifications are as follows.

--Only BLANK, BOOLEAN, NUMERIC, and STRING of the CellType supported by POI are targeted. However, it also supports dates. --The return value is of type ʻObject. Cast or String # valueOf or whatever you like. --Throw an exception for unsupported ʻERROR and FORMULA --The following is a sample, so it throws a RuntimeException, but when actually using it, throw a proper exception (?). --null Not safe. That is, if the argument is null, it throws NullPointerException. Another exception is when a cell of CellType that is not supported by POI is passed as an argument just in case.

package my.excel;

import java.util.Objects;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;

final public class CellUtils {

    public static Object getCellValue(Cell cell) {
        Objects.requireNonNull(cell, "cell is null");
        
        CellType cellType = cell.getCellTypeEnum();
        if (cellType == CellType.BLANK) {
            return null;
        } else if (cellType == CellType.BOOLEAN) {
            return cell.getBooleanCellValue();
        } else if (cellType == CellType.ERROR) {
            throw new RuntimeException("Error cell is unsupported");
        } else if (cellType == CellType.FORMULA) {
            throw new RuntimeException("Formula cell is unsupported");
        } else if (cellType == CellType.NUMERIC) {
            if (DateUtil.isCellDateFormatted(cell)) {
                return cell.getDateCellValue();
            } else {
                return cell.getNumericCellValue();
            }
        } else if (cellType == CellType.STRING) {
            return cell.getStringCellValue();
        } else {
            throw new RuntimeException("Unknow type cell");
        }
    }
    
}

Let's actually use it (´ ・ ω ・ ｀) First, prepare the following sample.xlsx.

After that, write the following code and check if it can be read correctly.

package my.excel;

import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class Main {
    public static void main(String ... args) {
        Path path = Paths.get("C:\\hogehoge\\sample.xlsx");
        try (Workbook workbook = WorkbookFactory.create(Files.newInputStream(path))) {
            Sheet sheet = workbook.getSheetAt(0);
            for (int i = 0; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                Cell cell = row.getCell(1); //Get the cell in column B.
                Object value = CellUtils.getCellValue(cell);
                System.out.println(value);
            }
        } catch (IOException 
                    | InvalidFormatException 
                    | EncryptedDocumentException e) {
            e.printStackTrace();
        } 
    }
 }

If you look at the results of compiling and running-you can see that it's working as expected. I did it (´ ・ ω ・ ｀)

ABC
true
100.0
Mon Jan 02 00:00:00 JST 2017