POI实现Excel文件上传及数据导入

1、引入POI架包

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.14</version>
</dependency>

2、前端文件上传页面，注意点：enctype="multipart/form-data;"和type="file"

<form id="imp" method="post" action="${base}/rest/textCheck/importWarnInfo"     
            enctype="multipart/form-data;charset=utf-8">
       <input id="files" name="file" type="file" style="display: none;"accept=".xls,.xlsx">
</form>

3、后台接口设计，注意点：MultipartFile file

    @ResponseBody
    @RequestMapping(value = "/importWarnInfo")
    public DataMessage importWarnInfo(MultipartFile file) {
        return textCheckService.importWarnInfo(file);
    }

4、Excel解析

    @Override
    public DataMessage importWarnInfo(MultipartFile file) {
        InputStream in = null;
        int importSuccessNum = 0;// 导入成功数量
        int importErrorNum = 0;// 导入失败数量
        Workbook workBook = null;
        try {
            DecimalFormat df = new DecimalFormat("####0");
            in = file.getInputStream();
            //1、创建工作簿
            workBook = WorkbookFactory.create(in);
            //2、获取sheet对象 下标是从0开始
            Sheet sheet = workBook.getSheetAt(0);
            //3、获取数据行数
            int rowCount = sheet.getPhysicalNumberOfRows(); // 获取总行数
            WarnInfo warnInfo = null;
            //声明封装数据集合
            List<WarnInfo> warnInfoList = new ArrayList<WarnInfo>();
            //4、遍历每一行
            for (int r = 1; r < rowCount; r++) {
                warnInfo = new WarnInfo();
                //5、获取每一行对象
                Row row = sheet.getRow(r);
                if (null == row || null == row.getCell(0)) {
                    continue;   //空的跳过
                }
                int cellCount = 8;//row.getLastCellNum(); // 获取总列数
                //6、遍历每一列
                for (int c = 1; c < cellCount; c++) {
                    String cellValue = null;
                    Cell cell = row.getCell(c);
                    if (null == cell) {
                        LoggerUtil.error(this.getClass(), "预警信息导入->第" + r + "行的第" + (c + 1) + "值为空");
                        break;
                    }
                    //7、获取单元格类型，根据类型获取值，做相应的格式处理
                    int cellType = cell.getCellType();
                    switch (cellType) {
                        case Cell.CELL_TYPE_STRING: // 文本
                            cellValue = cell.getStringCellValue();
                            break;
                        case Cell.CELL_TYPE_NUMERIC: // 数字、日期
                            if (DateUtil.isCellDateFormatted(cell)) {
                                cellValue = com.myfams.framework.base.utils.DateUtil.dateToString(cell.getDateCellValue(), com.myfams.framework.base.utils.DateUtil.LONG_DATE_NUMBER_FORMAT); // 日期型
                            } else {
                                cellValue = df.format(cell.getNumericCellValue()); // 数字
                            }
                            break;
                        case Cell.CELL_TYPE_BOOLEAN: // 布尔型
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                            break;
                        case Cell.CELL_TYPE_BLANK: // 空白
                            cellValue = cell.getStringCellValue();
                            break;
                        case Cell.CELL_TYPE_ERROR: // 错误
                            cellValue = "错误";
                            break;
                        case Cell.CELL_TYPE_FORMULA: // 公式
                            cellValue = "错误";
                            break;
                        default:
                            cellValue = "错误";
                    }
                    if (StringUtils.isNotEmpty(cellValue)) {
                        setValue(c, warnInfo, cellValue);
                    } else {
                        LoggerUtil.error(this.getClass(), "预警信息导入->第" + r + "行的第" + (c + 1) + "值为空");
                        break;
                    }
                }
                if (StringUtils.isNotEmpty(warnInfo.getFundCode())) {
                    warnInfoList.add(warnInfo);
                    importSuccessNum++;
                } else {
                    importErrorNum++;
                }
            }
            //8、保存数据
            textCheckDao.saveOrUpdateWarnInfoList(warnInfoList);
        } catch (Exception e) {
            LoggerUtil.error(this.getClass(),"导入异常",e);
            return DataMessage.error("导入失败！");
        } finally {
            //9、关闭资源
            if (null != in) {
                try {
                    in.close();
                } catch (IOException e) {
                    LoggerUtil.error(this.getClass(),"关流异常",e);
                }
            }
            if (null != workBook) {
                try {
                    workBook.close();
                } catch (IOException e) {
                    LoggerUtil.error(this.getClass(),"关流异常",e);
                }
            }
        }
        String messageStr = "导入成功<br/>导入成功" + importSuccessNum + "条，导入失败" + importErrorNum + "条<br/>";
        return DataMessage.success(messageStr);
    }