[Java]Read Excel File Using Apache POI API

標籤：

讀取以下兩種格式的Excel ： *.xls  and *.xlsx

用Apache POI API來實現，需要用到 HSSF 和 XSSF 的類庫

HSSF is the POI Project‘s pure Java implementation of the Excel ‘97(-2007) (.xls) file format.

XSSF is the POI Project‘s pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.

 

These 4 JARs are needed to read excel:

將這四個JAR包加入到Java Build Path裡面去

Java code to read Excel :

package com.file.properties;import java.io.*;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.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;class ReadExcel {static void readXlsx(File inputFile) {try {        // Get the workbook instance for XLSX file        XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(inputFile));        // Get first sheet from the workbook        XSSFSheet sheet = wb.getSheetAt(0);        Row row;        Cell cell;        // Iterate through each rows from first sheet        Iterator<Row> rowIterator = sheet.iterator();        while (rowIterator.hasNext())         {                row = rowIterator.next();                // For each row, iterate through each columns                Iterator<Cell> cellIterator = row.cellIterator();                                while (cellIterator.hasNext())                 {                cell = cellIterator.next();                switch (cell.getCellType())                 {                case Cell.CELL_TYPE_BOOLEAN:                        System.out.println(cell.getBooleanCellValue());                        break;                case Cell.CELL_TYPE_NUMERIC:                        System.out.println(cell.getNumericCellValue());                        break;                case Cell.CELL_TYPE_STRING:                        System.out.println(cell.getStringCellValue());                        break;                case Cell.CELL_TYPE_BLANK:                        System.out.println(" ");                        break;                default:                        System.out.println(cell);                }                }        }}catch (Exception e) {        System.err.println("Exception :" + e.getMessage());}}static void readXls(File inputFile) {try {        // Get the workbook instance for XLS file        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(inputFile));        // Get first sheet from the workbook        HSSFSheet sheet = workbook.getSheetAt(0);        Cell cell;        Row row;        // Iterate through each rows from first sheet        Iterator<Row> rowIterator = sheet.iterator();                while (rowIterator.hasNext())         {                row = rowIterator.next();                // For each row, iterate through each columns                Iterator<Cell> cellIterator = row.cellIterator();                                while (cellIterator.hasNext())                 {                cell = cellIterator.next();                switch (cell.getCellType())                 {                case Cell.CELL_TYPE_BOOLEAN:                        System.out.println(cell.getBooleanCellValue());                        break;                case Cell.CELL_TYPE_NUMERIC:                        System.out.println(cell.getNumericCellValue());                        break;                case Cell.CELL_TYPE_STRING:                        System.out.println(cell.getStringCellValue());                        break;                case Cell.CELL_TYPE_BLANK:                        System.out.println(" ");                        break;                default:                        System.out.println(cell);                }                }        }} catch (FileNotFoundException e) {        System.err.println("Exception" + e.getMessage());}catch (IOException e) {        System.err.println("Exception" + e.getMessage());}}public static void main(String[] args) {        File inputFile = new File("D:\\SoapUIStudy\\input.xls");        File inputFile2 = new File("D:\\SoapUIStudy\\input.xlsx");        readXls(inputFile);        readXlsx(inputFile2);}}

 Result :

User in xls
Password in xls
U1
P1
U2
P2
User in xlsx
Password in xlsx
User1
Password1
User2
Password2
User3
Password3

[Java]Read Excel File Using Apache POI API