Import Excel data in Java


This time is Apache POI. I used it to import the data group created by Excel into Java processing.

What is Apache POI

Official name Apache POI Java library that can read and write Microsoft Office format files such as Word and Excel

I downloaded the following jar file from here.

poi-4.1.1.jar poi-ooxml-4.1.1.jar poi-ooxml-schemas-4.1.1.jar commons-collections4-4.4.jar commons-compress-1.19.jar xmlbeans-3.1.0.jar

Process made

I made a process to import the first sheet of the target Excel file, column A, into Java.

public class SamplePOI {
     *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);
                cell = row.getCell(0);
                String cellValue = getCellStringValue(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 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;

Try to run

When I load the Excel file containing the following hiragana and execute it, "Akasatana Hamayarawa" is output. image.png


[a Ka sa ta na ha ma ya ra wa]


I only did simple processing, but I felt that it was the same as VBA. It was a little difficult to understand that the cell type had to be taken into consideration when acquiring the contents of the cell.

When I try to insert a function in Excel and import it, it seems that the function itself is acquired as a value instead of the calculation result? So if you want the result, it seems a little difficult.

Recommended Posts

Import Excel data in Java
Importing Excel data in Java 2
Importing Excel data in Java 3
Save Java PDF in Excel
Java addition excel data validation
Display Firestore data in RecyclerView [Java]
Create variable length binary data in Java
Partization in Java
Optimize Java import declarations in IntelliJ IDEA
Rock-paper-scissors in Java
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)
Import files of the same hierarchy in Java
[java] sort in list
Read JSON in Java
Interpreter implementation in Java
Make Blackjack in Java
Rock-paper-scissors app 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
[Java] Data type ①-Basic type
Azure functions in java
Simple htmlspecialchars in Java
Print Java Excel Worksheet
Boyer-Moore implementation in Java
Hello World 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
Try using RocksDB in Java
Read binary files in Java 1
Java learning memo (data type)
Get EXIF information in Java
[Neta] Sleep Sort in Java
How to use JSON data in WebSocket communication (Java, JavaScript)
Java history in this world
Java merge & unmerge Excel cell