java poi读取excel文件（xlsx）_poi读取xlsx文件

读取excel文件的方法有许多种，这篇文章主要描述通过poi读取excel文件。

先maven导入jar包

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.8</version>
</dependency> 
 
<dependency> 
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.8</version>
</dependency>

 读取后缀为“xlsx”的excel文件代码 （“xls”不适用，“xls”得用 HSSFWorkbook）。

    /**
     * @param file       需要读取的Excel文件
     * @param sheetIndex 读取的Excel文件中的表格下标
     * @return 数据的坐标，对应的值
     */
    public static ArrayList<ArrayList<Object>> readExcel(File file, Integer sheetIndex) {
        ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
        FileInputStream fileInputStream = null;
        try {
            ArrayList<Object> colList;
            fileInputStream = new FileInputStream(file);
            XSSFWorkbook wb = new XSSFWorkbook(fileInputStream);
            XSSFSheet sheet = wb.getSheetAt(sheetIndex);
            XSSFRow row;
            XSSFCell cell;
            Object value;
            for (int i = sheet.getFirstRowNum(), rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows(); i++) {
                row = sheet.getRow(i);
                colList = new ArrayList<Object>();
                //当读取行为空时
                if (row == null) {
                    //判断是否是最后一行，不是最后一行添加上无数据的集合
                    if (i != sheet.getPhysicalNumberOfRows()) {
                        rowList.add(colList);
                    }
                    continue;
                } else {
                    rowCount++;
                }
                for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                    if (j < 0) {
                        continue;
                    }
                    cell = row.getCell(j);
                    //当该单元格为空
                    if (cell == null || cell.getCellTypeEnum() == CellType.BLANK) {
                        //判断是否是该行中最后一个单元格
                        if (j != row.getLastCellNum()) {
                            colList.add("");
                        }
                        continue;
                    }
                    //根据数据类型来获取值
                    switch (cell.getCellTypeEnum()) {
                        case STRING:
                            value = cell.getStringCellValue();
                            break;
                        case NUMERIC:
                            if (cell.getCellStyle().getDataFormatString().contains("m/d/yy")) {
                                value = SimpleDateFormatAddUtil.DateTOString(cell.getDateCellValue());
                            } else if (cell.getCellStyle().getDataFormatString().contains("yyyy/m/d")) {
                                value = SimpleDateFormatAddUtil.DateTOString(cell.getDateCellValue());
                            } else if (cell.getCellStyle().getDataFormatString().equals("General")) {
                                value = getRealStringValueOfDouble(cell.getNumericCellValue());
                            } else {
                                try {
                                    value = getRealStringValueOfDouble(cell.getNumericCellValue());
                                }catch (Exception e){
                                    value=cell.toString();
                                }
                            }
                            break;
                        case BOOLEAN:
                            value = Boolean.valueOf(cell.getBooleanCellValue());
                            break;
                        case BLANK:
                            value = "";
                            break;
                        case FORMULA: //公式类型
                            value = parseFormula(cell);
                            break;
                        default:
                            value = cell.toString();
                    }
                    colList.add(value);
                }
                rowList.add(colList);
            }
            wb.close();
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (null != fileInputStream) {
                    fileInputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return rowList;
    }
 
 
    /**
     * 解析公式
     * @param cell - 单元格
     * @return String - 结果
     */
    public static String parseFormula(Cell cell) {
        String data = null;
        switch (cell.getCachedFormulaResultTypeEnum()) {
            case NUMERIC:
                if (0 == cell.getCellStyle().getDataFormat()) {
                    data = String.format("%.4f", cell.getNumericCellValue());
                } else {
                    data = String.valueOf(cell.getNumericCellValue());
                }
                break;
            case STRING:
                data = String.valueOf(cell.getRichStringCellValue());
                break;
            case BOOLEAN:
                data = String.valueOf(cell.getBooleanCellValue());
                break;
            case ERROR:
                data = String.valueOf(cell.getErrorCellValue());
                break;
            default:
                data = cell.getCellFormula();
        }
        return data;
    }
 
    private static String getRealStringValueOfDouble(Double d) {
        String doubleStr = d.toString();
        boolean b = doubleStr.contains("E");
        int indexOfPoint = doubleStr.indexOf('.');
        if (b) {
            int indexOfE = doubleStr.indexOf('E');
            BigInteger xs = new BigInteger(doubleStr.substring(indexOfPoint
                    + BigInteger.ONE.intValue(), indexOfE));
            int pow = Integer.valueOf(doubleStr.substring(indexOfE
                    + BigInteger.ONE.intValue()));
            int xsLen = xs.toByteArray().length;
            int scale = xsLen - pow > 0 ? xsLen - pow : 0;
            doubleStr = String.format("%." + scale + "f", d);
        } else {
            Pattern p = Pattern.compile(".0$");
            java.util.regex.Matcher m = p.matcher(doubleStr);
            if (m.find()) {
                doubleStr = doubleStr.replace(".0", "");
            }
        }
        return doubleStr;
    }