利用POI读取excel

1、poi简介
　　Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
２、maven依赖

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

３、ExcelReaderUtil.java

public class ExcelReaderUtil {    // 默认单元格内容为数字时格式    private static DecimalFormat df = new DecimalFormat("0");    // 默认单元格格式化日期字符串    private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");    public static class ExcelRow {        //所在行标,从1开始        private int row;        //该行数据        private List<Object> datas = new ArrayList<>();        public int getRow() {            return row;        }        public void setRow(int row) {            this.row = row;        }        public List<Object> getDatas() {            return datas;        }        public void setDatas(List<Object> datas) {            this.datas = datas;        }        @Override        public String toString() {            return "[" + datas + "]";        }    }    /**     * 读取Excel文件，支持2007和2003两种版本     * 去掉空行，空单元格对应"",浮点数原样输出     * @param inputStream   输入流     * @param containTitle  是否包含表头     * @return     * @throws IOException     * List<List<Object>>     */    @SuppressWarnings("deprecation")    public static List<ExcelRow> readExcel(InputStream inputStream, boolean isExcel2007, boolean containTitle) throws IOException {        List<ExcelRow> rowList = new ArrayList<>();        try {            //初始化Workbook对象            Workbook wb = null;            if (isExcel2007) {                wb = new XSSFWorkbook(inputStream);            } else {                wb = new HSSFWorkbook(inputStream);            }            //读取标题行，获取列数            Sheet sheet = wb.getSheetAt(0);            //第一列开始的行标            int firstRowNum = sheet.getFirstRowNum();            int lastRowNum = sheet.getLastRowNum();            Row row = sheet.getRow(firstRowNum);            //表头列数            int firstCellNum = row.getFirstCellNum();            int lastCellNum = row.getLastCellNum();            //遍历数据表,i是游标，要将有数据的读完，中间没数据的行也加入            int i = firstRowNum;            if (!containTitle) {                i =  firstRowNum +1;            }            for (; i < lastRowNum + 1 ; i++) {                row = sheet.getRow(i);                //一行数据                List<Object> colList = new ArrayList<Object>();                if (row == null) {                    continue;                }                boolean allBlank = true;                for (int j = firstCellNum; j < lastCellNum ; j++) {                    Cell cell = row.getCell(j);                    Object value = "";                    if (cell == null) {                        colList.add(value);                        continue;                    }                    switch (cell.getCellType()) {                        case XSSFCell.CELL_TYPE_STRING:                            value = cell.getStringCellValue();                            break;                        case XSSFCell.CELL_TYPE_NUMERIC:                            if ("@".equals(cell.getCellStyle().getDataFormatString())) {                                value = df.format(cell.getNumericCellValue());                            } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {                                double num = cell.getNumericCellValue();                                if(isInteger(num)) {                                    value = df.format(cell.getNumericCellValue());                                } else {                                    value = num;                                }                            } else {                                value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));                            }                            break;                        case XSSFCell.CELL_TYPE_BOOLEAN:                            value = Boolean.valueOf(cell.getBooleanCellValue());                            break;                        case XSSFCell.CELL_TYPE_BLANK:                            value = "";                            break;                        default:                            value = cell.toString();                    }// end switch                    if(StringUtils.isNotBlank(value.toString())){                        allBlank = false;                    }                    colList.add(value);                } // end for j                if (!allBlank) {                    ExcelRow excelRow = new ExcelRow();                    excelRow.setRow(i+1);                    excelRow.setDatas(colList);                    rowList.add(excelRow);                }            } // end for i            wb.close();        } catch (OfficeXmlFileException e){            throw new IOException("文件后缀与实际文件类型不符", e);        }        return rowList;    }    //判断整数（int）    private static boolean isInteger(double value) {        if(value % 1 == 0){// 是这个整数，小数点后面是0            return true;        }else{//不是整数，小数点后面不是0            return false;        }    }}

4、调用

boolean isExcel2007 = false;if (StringUtils.endsWithIgnoreCase(fileName, ".xlsx")) {    isExcel2007 = true;}List<ExcelRow> datas = ExcelReaderUtil.readExcel(file.getInputStream(), isExcel2007, true);String col1 = (String) datas.get(i).getDatas().get(0);