java上传Excel并解析存储数据库

一、引入maven依赖

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

二、controller层

@RequestMapping(value = "/importWorkExcel", method = RequestMethod.POST, produces = "application/json;charset=utf-8")
    public void importWorkExcel(@RequestParam("excel") MultipartFile multipartFile) throws BusinessException,IOException, ParseException {
        workSpotService.importWorkExcel(multipartFile);
    }

三、serviceImpl层

@Override
    public void importWorkExcel(MultipartFile file) throws IOException, ParseException {

        //检查文件
        checkFile(file);
        //获得WorkBook对象
        Workbook workbook = getWorkBook(file);
        if(workbook != null){
            for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
                //获得当前sheet表
                Sheet sheet = workbook.getSheetAt(sheetNum);
                if(sheet == null){
                    continue;
                }
                //获得当前sheet的开始行
                int firstRowNum = sheet.getFirstRowNum();
                //获得当前sheet的结束行
                int lastRowNum = sheet.getLastRowNum();
                //循环前两行之外的所有行
                for(int rowNum = firstRowNum+2;rowNum <= lastRowNum;rowNum++){
                    //获得当前行
                    Row row = sheet.getRow(rowNum);
                    if(row == null){
                        continue;
                    }
                    //获得当前行的开始行
                    int firstCellNum = row.getFirstCellNum();
                    //获得当前行的列数
                    int lastCellNum = row.getPhysicalNumberOfCells();
                    String workName = "";
                    String leaderName = "";
                    Integer leaderId = null;
                    Date startTime = null;
                    Date endTime = null;
                    String levelStr = "";
                    Integer level = null;
                    String majorStr = "";
                    Integer major = null;
                    for(int cellNum = 0; cellNum<row.getLastCellNum();cellNum++){
                        Cell cell = row.getCell(cellNum);
//                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        if(cellNum == 2){
                            workName = String.valueOf(cell);
                        }
                        if(cellNum == 3){
                            majorStr = String.valueOf(cell);
                        }
                        if(cellNum == 7){
                            leaderName = String.valueOf(cell);
                        }

                        if(cellNum == 8){
                            String time = getCellValueByCell(cell);
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                            startTime = sdf.parse(time);
                        }
                        if(cellNum == 9){
                            String time = getCellValueByCell(cell);
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                            endTime = sdf.parse(time);
                        }
                        if(cellNum == 13){
                            levelStr = String.valueOf(cell);
                        }
                    }

                    //查询用户id
                    leaderId = userMapper.getIdByUserName(leaderName);
                    //判断风险id
                    if(levelStr.contains("一级")){
                        level = 1;
                    } else if(levelStr.contains("二级")){
                        level = 2;
                    } else if(levelStr.contains("三级")){
                        level = 3;
                    } else if(levelStr.contains("四级")){
                        level = 4;
                    } else if(levelStr.contains("高风险")){
                        level = 5;
                    } else if(levelStr.contains("中风险")){
                        level = 6;
                    } else if(levelStr.contains("低风险")){
                        level = 7;
                    }else if(levelStr.contains("通信A级")){
                        level = 8;
                    }else if(levelStr.contains("通信B级")){
                        level = 9;
                    }else if(levelStr.contains("通信C级")){
                        level = 10;
                    }else {
                        level = 1;
                    }
                    //判断专业
                    if(majorStr.contains("变电")){
                        major = 1;
                    } else if(majorStr.contains("输电")){
                        major = 2;
                    } else if(majorStr.contains("配电")){
                        major = 3;
                    } else if(majorStr.contains("配农网工程")){
                        major = 4;
                    } else if(majorStr.contains("基建")){
                        major = 5;
                    } else if(majorStr.contains("外部出资工程")){
                        major = 6;
                    } else if(majorStr.contains("充电桩工程")){
                        major = 7;
                    } else if(majorStr.contains("营销")){
                        major = 8;
                    } else if(majorStr.contains("网络通信")){
                        major = 9;
                    } else if(majorStr.contains("小型基建")){
                        major = 10;
                    }

                    WorkSpot workSpot = new WorkSpot();
                    workSpot.setName(workName);
                    workSpot.setProName("");
                    workSpot.setLeaderId(leaderId);
                    workSpot.setScheduleStartDate(startTime);
                    workSpot.setScheduleEndDate(endTime);
                    workSpot.setLevel(level);
                    workSpot.setState(1);
                    workSpot.setCreateUserId(UserUtil.getUserId());
                    workSpot.setCreateTime(new Date());
                    workSpotMapper.insert(workSpot);
                }
            }
        }

    }

    /**=============================辅助方法================================**/

    public static Workbook getWorkBook(MultipartFile file){
        //获得文件名
        String fileName = file.getOriginalFilename();
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            //获取excel文件的io流
            InputStream is = file.getInputStream();
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if (fileName.endsWith("xls")) {
                //2003
                workbook = new HSSFWorkbook(is);
            } else if (fileName.endsWith("xlsx")) {
                //2007
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workbook;
    }
//检查是否是Excel表格格式
    public static void checkFile(MultipartFile file)throws IOException{
        if(null == file){
            System.out.println("文件不存在");
            throw new FileNotFoundException("文件不存在！");
        }

        String fileName = file.getOriginalFilename();
        if(!fileName.endsWith("xls") && !fileName.endsWith("xlsx")){
            System.out.println(fileName+"不是Excel文件！");
            throw new IOException(fileName+"不是Excel文件！");
        }
    }

    public static File MultipartFileToFile(MultipartFile multiFile) {
        // 获取文件名
        String fileName = multiFile.getOriginalFilename();
        // 获取文件后缀
        String prefix = fileName.substring(fileName.lastIndexOf("."));
        // 若需要防止生成的临时文件重复,可以在文件名后添加随机码

        try {
            File file = File.createTempFile(fileName, prefix);
            multiFile.transferTo(file);
            return file;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
//  处理单元格内容为时间
    public static String getCellValueByCell(Cell cell) {
        //判断是否为null或空串
        if (cell==null || cell.toString().trim().equals("")) {
            return "";
        }
        String cellValue = "";
        int cellType=cell.getCellType();
        switch (cellType) {
            case Cell.CELL_TYPE_NUMERIC: // 数字
                short format = cell.getCellStyle().getDataFormat();
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = null;
                    //System.out.println("cell.getCellStyle().getDataFormat()="+cell.getCellStyle().getDataFormat());
                    if (format == 20 || format == 32) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else if (format == 14 || format == 31 || format == 57 || format == 58) {
                        // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                        double value = cell.getNumericCellValue();
                        Date date = org.apache.poi.ss.usermodel.DateUtil
                                .getJavaDate(value);
                        cellValue = sdf.format(date);
                    }else {// 日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    }
                    try {
                        cellValue = sdf.format(cell.getDateCellValue());// 日期
                    } catch (Exception e) {
                        try {
                            throw new Exception("exception on get date data !".concat(e.toString()));
                        } catch (Exception e1) {
                            e1.printStackTrace();
                        }
                    }finally{
                        sdf = null;
                    }
                }  else {
                    BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
                    cellValue = bd.toPlainString();// 数值 这种用BigDecimal包装再获取plainString，可以防止获取到科学计数值
                }
                break;
            case Cell.CELL_TYPE_STRING: // 字符串
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN: // Boolean
                cellValue = cell.getBooleanCellValue()+"";;
                break;
            case Cell.CELL_TYPE_FORMULA: // 公式
                cellValue = cell.getCellFormula();
                break;
            case Cell.CELL_TYPE_BLANK: // 空值
                cellValue = "";
                break;
            case Cell.CELL_TYPE_ERROR: // 故障
                cellValue = "ERROR VALUE";
                break;
            default:
                cellValue = "UNKNOW VALUE";
                break;
        }
        return cellValue;
    }