Importing Excel data in Java 2

Continuing from the last time, this time is also the contents of Apache POI.

About getting cell value

About the part that was described in another method about the part to get the value of the previous cell. It doesn't seem like the cell values are simply brought in as is. Excerpt from the previous method as it is.

    private static String getCellStringValue(Cell cell) {
        String retStr;
        CellType cellType = cell.getCellType();
        switch (cellType) {
        case STRING:
            retStr = cell.getStringCellValue();
        case NUMERIC:
            retStr = String.valueOf(cell.getNumericCellValue());
        case BOOLEAN:
            retStr = String.valueOf(cell.getBooleanCellValue());
        case FORMULA:
            retStr = String.valueOf(cell.getCellFormula());
        case ERROR:
            retStr = String.valueOf(cell.getErrorCellValue());
            retStr = "";
        return retStr;

Here, the type of cell to be acquired (variable type? State? In Java) is first determined. It seems that the method used for acquisition changes depending on the type.

About cell type

You can get the cell type with the getCellType method. Specifically, there are the following seven. ・ ** CellType._NONE ** Unknown cell. I'm not sure, but it's a cell that rarely appears.

・ ** CellType.BLANK ** Blank cell.

・ ** CellType.BOOLEAN ** Boolean. For cells that ask TRUE, FALSE, or authenticity (such as "= A1 = A2").

・ ** CellType.ERROR ** error. Perhaps a calculation error is a cell that has an error in the formula.

・ ** CellType.FORMULA ** Formula. A cell that is calculated like "= A1 + B1" and displayed as the calculated value on the display. Since the formula itself can be obtained by using the getCellFormula method, "= A1 + B1" can be obtained as it is by converting to String.

・ ** CellType.NUMERIC ** Numerical value. A cell that contains numbers or dates.

・ ** CellType.STRING ** String. A cell whose content is text. If you used numbers as strings on Excel, the numbers will come here as well. (Should)


public class SamplePOI2 {

     *This process
    public static void main(String[] args) {
        //Enter the full path of the Excel file you want to import here
        String ExcelPath = "";

        //Objects for Excel
        Workbook wb;
        Sheet sh;
        Row row;
        Cell cell;

        //List to hold the acquired data
        List<String> columnA_List = new ArrayList<>();

        try (InputStream is = new FileInputStream(ExcelPath)) {

            //Import the target Excel file into Java
            wb = WorkbookFactory.create(is);
            //Specify the first sheet of the target file
            sh = wb.getSheetAt(0);
            //Get the maximum row in the sheet
            int rowMaxA = sh.getLastRowNum();

            //Turn the loop for the maximum row and get the cell of column A as a String type
            for (int i = 0; i <= rowMaxA; i++) {
                row = sh.getRow(i);
                if (row == null) {
                cell = row.getCell(0);
                String cellValue = getCellTypes(cell);

            //Output to console

            for (String outStr : columnA_List) {

        } catch (Exception e) {

     *Determines the state of the cell and returns it as a String type.
    private static String checkCellType(Cell cell) {
        String retStr="";
        CellType cellType = cell.getCellType();
        switch (cellType) {
        case _NONE:
            retStr ="_NONE";
        case BLANK:
            retStr ="BLANK";
        case BOOLEAN:
            retStr ="BOOLEAN";
        case ERROR:
            retStr ="ERROR";
        case FORMULA:
            retStr ="FORMULA";
        case NUMERIC:
            retStr ="NUMERIC";
        case STRING:
            retStr ="STRING";
        return retStr;

image.png The result of loading this Excel is



have become.

About acquisition

Once you know the cell type, get the value for each cell type. STRING getStringCellValue() Or cell.getRichStringCellValue().getString() Obtained at. I don't really understand the difference.

NUMERIC If you want to make it int type getNumericCellValue() If you want to get Date type such as date getDateCellValue()

FORMULA getCellFormula() Expression can be obtained as String type

ERROR getErrorCellValue() Get as an error code. (byte)

BOOLEAN It can be used as it is with boolean type. getBooleanCellValue()

It is like this.

Recommended Posts

Importing Excel data in Java 2
Importing Excel data in Java 3
Import Excel data in Java
Save Java PDF in Excel
Java addition excel data validation
Display Firestore data in RecyclerView [Java]
Create variable length binary data in Java
Changes in Java 11
Rock-paper-scissors in Java
Java creates a pie chart in Excel
Delete blank rows / columns in Java Excel
Pi in Java
FizzBuzz in Java
Add, read, and delete Excel comments in Java
Organized memo in the head (Java --Data type)
[java] sort in list
Read JSON in Java
Make Blackjack in Java
Constraint programming in Java
Put java8 in centos7
NVL-ish guy in Java
Combine arrays in Java
"Hello World" in Java
Callable Interface in Java
Comments in Java source
[Java] Data type ①-Basic type
Azure functions in java
Format XML in Java
Simple htmlspecialchars in Java
Print Java Excel Worksheet
Boyer-Moore implementation in Java
Hello World in Java
Use OpenCV in Java
webApi memorandum in java
Type determination in Java
Ping commands in Java
Various threads in java
Heapsort implementation (in java)
Zabbix API in Java
ASCII art in Java
Compare Lists in Java
Express failure in Java
[Java] Main data types
Create JSON in Java
Date manipulation in Java 8
What's new in Java 8
Use PreparedStatement in Java
What's new in Java 9,10,11
Parallel execution in Java
Initializing HashMap in Java
Java basic data types
Vectorize and image MNIST handwritten digit image data in Java
How to create a data URI (base64) in Java
Try using RocksDB in Java
Avoid Yubaba's error in Java
Java learning memo (data type)
Get EXIF information in Java
Get weather forecasts from Watson Weather Company Data in simple Java
How to use JSON data in WebSocket communication (Java, JavaScript)
Edit ini in Java: ini4j