[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.

sample.PNG

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

Recommended Posts

I want to get the value of Cell transparently regardless of CellType (Apache POI)
I want to get the value in Ruby
In Apache POI 3.15, when I get the result of the formula, FormulaParseException occurs (the formula refers to "cell of sheet name including" ・ ")
I want to change the value of Attribute in Selenium of Ruby
[Ruby] I want to extract only the value of the hash and only the key
I want to get the field name of the [Java] field. (Old tale tone)
I want to var_dump the contents of the intent
I want to recursively get the superclass and interface of a certain class
[Java] How to get the maximum value of HashMap
I want to know the answer of the rock-paper-scissors app
I want to display the name of the poster of the comment
I want to be aware of the contents of variables!
I want to return the scroll position of UITableView!
I want to expand the clickable part of the link_to method
I want to change the log output settings of UtilLoggingJdbcLogger
I want to narrow down the display of docker ps
[Ruby] I want to reverse the order of the hash table
I want to temporarily disable the swipe gesture of UIPageViewController
I want to get a list of the contents of a zip file and its uncompressed size
I want to understand the flow of Spring processing request parameters
The story of Collectors.groupingBy that I want to keep for posterity
I want to limit the input by narrowing the range of numbers
I want to control the default error message of Spring Boot
[Android] I want to get the listener from the button in ListView
[Controller] I want to retrieve the numerical value of a specific column from the DB (my memo)
[Spring Boot] I want to add my own property file and get the value with env.getProperty ().
I want to display the number of orders for today using datetime.
I want to know the JSP of the open portlet when developing Liferay
I want to pass the argument of Annotation and the argument of the calling method to aspect
I want to get the IP address when connecting to Wi-Fi in Java
I want you to use Enum # name () for the Key of SharedPreference
I want to get a list of only unique character strings by excluding fixed character strings from the file name
[RxSwift] I want to deepen my understanding by following the definition of Observable
I want to control the start / stop of servers and databases with Alexa
What I tried when I wanted to get all the fields of a bean
[Rails / ActiveRecord] I want to validate the value before the type is converted (_before_type_cast)
I want to get only the time from Time type data ...! [Strftime] * Additional notes
I want to separate the handling of call results according to the API caller (call trigger)
I managed to get a blank when I brought the contents of Beans to the textarea
I want to see the contents of Request without saying four or five
How to get today's day of the week
I want to truncate after the decimal point
[Java] How to get the authority of the folder
I want to get the information of the class that inherits the UserDetails class of the user who is logged in with Spring Boot.
I want to find the MD5 checksum of a file in Java and get the result as a string in hexadecimal notation.
[Ruby] I want to make a program that displays today's day of the week!
Rails The concept of view componentization of Rails that I want to convey to those who want to quit
Install multiple submit buttons in Rails View to get the value of the pressed button
[Active Admin] I want to specify the scope of the collection to be displayed in select_box
[Rails] I want to display the link destination of link_to in a separate tab
# 1_JAVA I want to get the index number by specifying one character in the character string.
I want to reduce the number of unnecessary queries. From considering counter_cache to introducing counter_culture.
[Java] How to get the URL of the transition source
[Java] I want to calculate the difference from the date
I want to embed any TraceId in the log
I was addicted to the record of the associated model
I tried to summarize the state transition of docker
05. I tried to stub the source of Spring Boot
I want to judge the range using the monthly degree
I tried to reduce the capacity of Spring Boot
I want to dark mode with the SWT app