Read Excel file by Apache POI : java

maven project

dependencies (pom.xml)

    <dependencies>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        
        ...
        ...
        ...
    </dependencies>

java code

package com.blogspot.na5cent.excellearning;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * @author redcrow
 */
public class ReadExcel {

    private static final Logger LOG = LoggerFactory.getLogger(ReadExcel.class);
    private static final String EXCEL_PATH = "D:\\project\\ExcelLearning\\src\\main\\resources\\excel";

    public static void main(String[] args) {
        File directory = new File(EXCEL_PATH);
        if (!directory.isDirectory()) {
            return;
        }
        File[] listFiles = directory.listFiles();
        if (listFiles == null) {
            return;
        }

        for (File file : listFiles) {
            if (file.getName().endsWith(".xls")) {
                InputStream inputStream = null;
                try {
                    inputStream = new FileInputStream(file);
                    HSSFWorkbook workbook = new HSSFWorkbook(inputStream);

                    int numberOfSheets = workbook.getNumberOfSheets();
                    for (int index = 0; index < numberOfSheets; index = index + 1) {
                        HSSFSheet sheet = workbook.getSheetAt(index);
                        Iterator<Row> rowIterator = sheet.iterator();

                        while (rowIterator.hasNext()) {
                            Row row = rowIterator.next();
                            Iterator<Cell> cellIterator = row.cellIterator();

                            while (cellIterator.hasNext()) {
                                Cell cell = cellIterator.next();
                                String value = getCellValue(cell);

                                Object params[] = {
                                    index, 
                                    cell.getRowIndex(), 
                                    cell.getColumnIndex(), 
                                    value
                                };

                                LOG.debug("[Sheet, Row, Cell, value] => [{}, {}, {}, {}]", params);
                            }
                            LOG.debug("");
                        }
                        LOG.debug("----------------------------------------------------");
                    }
                } catch (Exception ex) {
                    LOG.warn(null, ex);
                } finally {
                    if (inputStream != null) {
                        try {
                            inputStream.close();
                        } catch (IOException ex) {
                            LOG.warn(null, ex);
                        }
                    }
                }
                LOG.debug("=======================================================");
            }
        }

    }

    private String getCellValue(Cell cell) {
        String value = "";
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                value = cell.getBooleanCellValue() + "";
                break;
            case Cell.CELL_TYPE_NUMERIC:
                value = cell.getNumericCellValue() + "";
                break;
            case Cell.CELL_TYPE_STRING:
                value = cell.getStringCellValue() + "";
                break;
        }

        return value;
    }
}

อ่านเพิ่มเติมได้ที่ : http://poi.apache.org/