java springboot---POI---excel批量导入

pom.xml导入maven

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

写文件检测函数

public static String checkFile(MultipartFile file) throws IOException {
        if (null == file) {// 判断文件是否存在
            return "文件不存在";//文件不存在
        }
        String fileName = file.getOriginalFilename();  // 获得文件名
        if (fileName != null && !fileName.endsWith("xls") && !fileName.endsWith("xlsx")) {// 判断文件是否是excel文件
            return "上传的不是excel文件";//上传的不是excel文件
        }
        return fileName;
    }

单元格内容检测函数

public static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        DecimalFormat df = new DecimalFormat("0");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                String dataFormat = cell.getCellStyle().getDataFormatString();    // 单元格格式
                boolean isDate = DateUtil.isCellDateFormatted(cell);
                if ("General".equals(dataFormat)) {
                    cellValue = df.format(cell.getNumericCellValue());
                } else if (isDate) {
                    cellValue = sdf.format(cell.getDateCellValue());
                }
                break;
case Cell.CELL_TYPE_STRING: //字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN: //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA: //公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case Cell.CELL_TYPE_BLANK: //空值
                cellValue = "";
                break;
            case Cell.CELL_TYPE_ERROR: //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

设置日期格式

public static String stringDateProcess(Cell cell) {
        String result = new String();
        if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
            SimpleDateFormat sdf = null;
            if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
                sdf = new SimpleDateFormat("HH:mm ");
            } else {// 日期
                sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            }
            Date date = cell.getDateCellValue();
            result = sdf.format(date);
        } else if (cell.getCellStyle().getDataFormat() == 58) {
            // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            double value = cell.getNumericCellValue();
            Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
            result = sdf.format(date);
        } else {
            double value = cell.getNumericCellValue();
            CellStyle style = cell.getCellStyle();
            DecimalFormat format = new DecimalFormat();
            String temp = style.getDataFormatString();
            // 单元格设置成常规
            if (temp.equals("General")) {
                format.applyPattern("#");
            }
            result = format.format(value);
        }
        return result;
    }

ctrl中添加

/**
     * 批量查询后导入培训证书
     *
     * @return Integer 插入的记录数
     *
     * @date 2019年10月18日
     */
    @Transactional(readOnly = false, rollbackFor = Exception.class)
    @Override
    public AjaxResult uploadExcel(MultipartFile excelInput) throws Exception {
        String fileName = checkFile(excelInput);
        if ("文件不存在".equals(fileName) || "上传的不是excel文件".equals(fileName)) {
            throw new Exception("请上传Excel格式的文件");
        }
        Workbook workbook = null;
        InputStream inputStream = excelInput.getInputStream();
        List<String[]> list = new ArrayList<String[]>();
        try {
            workbook = WorkbookFactory.create(inputStream);
            int numberOfSheets = workbook.getNumberOfSheets();
            Sheet sheet = workbook.getSheetAt(0);
            /**获得当前sheet的开始行*/
            int firstRowNum = sheet.getFirstRowNum();
            /**获得当前sheet的结束行*/
            int lastRowNum = sheet.getLastRowNum();
            System.out.println(lastRowNum);
            /*遍历每行*/
            for (int rowNum = 2; rowNum <= lastRowNum; rowNum++) {
                /**获得当前行*/
                Row row = sheet.getRow(rowNum);
                if (row == null) {
                    continue;
                }
                /**获得当前行的开始列*/
                int firstCellNum = row.getFirstCellNum();
                /**获得当前行的結束列数*/
                int lastCellNum = row.getLastCellNum();
                if (lastCellNum != 8) {
                    /**必须要有三列数据*/
                    throw new Exception("上传的数据不能有为空值！");
                }
                String[] cells = new String[lastCellNum];
                /**循环当前行*/
                for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
                    Cell cell = row.getCell(cellNum);
                    cells[cellNum] = getCellValue(cell);
                }
                list.add(cells);
            }
            /**插入数据*/
            for (String[] strs : list) {
                String laborName = strs[0];
                String laborIdcode = strs[1];
                String enterpriseName = strs[2];
                String postName = strs[3];
                String categoryCode = strs[4];
                String certificateBeginDate = strs[5];
                String certificateEndDate = strs[6];
                String sendCategoryUnit = strs[7];
                SimpleDateFormat date = new SimpleDateFormat("yyyy-MM-dd");
                Date t1 = date.parse(certificateBeginDate);
                Date t2 = date.parse(certificateEndDate);
                LwCertificateInfo lwCertificateInfo = new LwCertificateInfo();
                lwCertificateInfo.setLaborName(laborName);
                lwCertificateInfo.setLaborIdcode(laborIdcode);
                lwCertificateInfo.setEnterpriseName(enterpriseName);
                lwCertificateInfo.setPostName(postName);
                lwCertificateInfo.setCategoryCode(categoryCode);
                lwCertificateInfo.setCertificateBeginDate(t1);
                lwCertificateInfo.setCertificateEndDate(t2);
                lwCertificateInfo.setSendCategoryUnit(sendCategoryUnit);
                lwCertificateInfoService.add(lwCertificateInfo);
//                /**图片导入*/
//                pictureWithWord(lwCertificateInfo);
//                /**批量导入生成二维码*/
//                qrCord(lwCertificateInfo);
            }
        } catch (EncryptedDocumentException | InvalidFormatException e1) {
            e1.printStackTrace();
        } finally {
            if (null != inputStream) {
                inputStream.close();
            }
        }
        return null;
    }