java poi读取excel文件（xlsx）

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

先maven导入jar包

<dependency>
    <groupId></groupId>
    <artifactId>poi</artifactId>
    <version>3.8</version>
</dependency> 

<dependency> 
    <groupId></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 = (sheetIndex);
            XSSFRow row;
            XSSFCell cell;
            Object value;
            for (int i = (), rowCount = 0; rowCount < (); i++) {
                row = (i);
                colList = new ArrayList<Object>();
                //当读取行为空时
                if (row == null) {
                    //判断是否是最后一行，不是最后一行添加上无数据的集合
                    if (i != ()) {
                        (colList);
                    }
                    continue;
                } else {
                    rowCount++;
                }
                for (int j = (); j <= (); j++) {
                    if (j < 0) {
                        continue;
                    }
                    cell = (j);
                    //当该单元格为空
                    if (cell == null || () == ) {
                        //判断是否是该行中最后一个单元格
                        if (j != ()) {
                            ("");
                        }
                        continue;
                    }
                    //根据数据类型来获取值
                    switch (()) {
                        case STRING:
                            value = ();
                            break;
                        case NUMERIC:
                            if (().getDataFormatString().contains("m/d/yy")) {
                                value = (());
                            } else if (().getDataFormatString().contains("yyyy/m/d")) {
                                value = (());
                            } else if (().getDataFormatString().equals("General")) {
                                value = getRealStringValueOfDouble(());
                            } else {
                                try {
                                    value = getRealStringValueOfDouble(());
                                }catch (Exception e){
                                    value=();
                                }
                            }
                            break;
                        case BOOLEAN:
                            value = (());
                            break;
                        case BLANK:
                            value = "";
                            break;
                        case FORMULA: //公式类型
                            value = parseFormula(cell);
                            break;
                        default:
                            value = ();
                    }
                    (value);
                }
                (colList);
            }
            ();
        } catch (Exception e) {
            ();
            return null;
        } finally {
            try {
                if (null != fileInputStream) {
                    ();
                }
            } catch (IOException e) {
                ();
            }
        }
        return rowList;
    }


    /**
     * 解析公式
     * @param cell - 单元格
     * @return String - 结果
     */
    public static String parseFormula(Cell cell) {
        String data = null;
        switch (()) {
            case NUMERIC:
                if (0 == ().getDataFormat()) {
                    data = ("%.4f", ());
                } else {
                    data = (());
                }
                break;
            case STRING:
                data = (());
                break;
            case BOOLEAN:
                data = (());
                break;
            case ERROR:
                data = (());
                break;
            default:
                data = ();
        }
        return data;
    }

    private static String getRealStringValueOfDouble(Double d) {
        String doubleStr = ();
        boolean b = ("E");
        int indexOfPoint = ('.');
        if (b) {
            int indexOfE = ('E');
            BigInteger xs = new BigInteger((indexOfPoint
                    + (), indexOfE));
            int pow = ((indexOfE
                    + ()));
            int xsLen = ().length;
            int scale = xsLen - pow > 0 ? xsLen - pow : 0;
            doubleStr = ("%." + scale + "f", d);
        } else {
            Pattern p = (".0$");
             m = (doubleStr);
            if (()) {
                doubleStr = (".0", "");
            }
        }
        return doubleStr;
    }