[Training Video - 6] [File Reading] [Java] Read Excel File Using Apache POI API - 张缤分

[Training Video - 6] [File Reading] [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 :

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

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);
}
}