SpringBoot中Excel文件数据导出至数据库关键代码

Controller

try {
//判断文件是否有数据，如果没有返回参数为空
if (multipartFile != null ){
infoService.readInfo(multipartFile);
result.setRespCode(10000000);
result.setRespMessage(“新增成功”);
}else{
throw new MsgException(“参数为空”);
}
}catch (Exception e){
result.setRespMessage(“新增失败:” + e.getMessage());
LOG.info(e.getMessage());
}
long end = System.currentTimeMillis();
LOG.info(“接口用时：” + (end - start) + “毫秒”);
return result;
}

Service实现类
//获取文件名
String name = multipartFile.getOriginalFilename();
System.out.println(name+“文件名”);

    //利用正则表达式根据文件名判断是否为.xls或者.xlsx的文件
    if(name.matches("^.+\\.(?i)(xls)$") || name.matches("^.+\\.(?i)(xlsx)$")){

    List<ExcelInfo> list = ExcelUtils.readInfo(multipartFile);
    if(!list.isEmpty()){
        excelInfoRepository.save(list);
    }else{
        throw new MsgException("excel参数为空");
        }
    }else{
        throw new MsgException("文件格式不正确");
    }

ExcelUitls
// 解析Excel
public static List readInfo(MultipartFile multipartFile) {
InputStream is = null;
HSSFWorkbook hssfWorkbook = null;
List infos = null;
FileOutputStream fos = null;
try {
//获取文件名
String fileName = multipartFile.getOriginalFilename();

        //设置一个文件夹
        File file = new File("excel/" + DateUtil.getDateString2(new Date()));

        //判断是否有该文件夹，没有的话新建一个
        if (!file.exists()) {
            file.mkdirs();
        }

        //获取文件的完整路径
        file = new File(file + "/" + (DateUtil.getDateString3(new Date()) + "-" + fileName));

        //获取输入流
        is = multipartFile.getInputStream();

        //创建一个文件输出流
        fos = new FileOutputStream(file);

        //创建一个缓冲区
        byte buffer[] = new byte[1024];

        //判断输入流中的数据是否读完的标识
        int len = 0;

        //循环将输入流读入到缓冲区中
        while ((len = is.read(buffer)) > 0) {
            fos.write(buffer, 0, len);
        }

        // new一个ArrayList集合存放数据
        infos = new ArrayList<>();

        //解析Excel数据
        is = new FileInputStream(file);
        hssfWorkbook = new HSSFWorkbook(is);
        HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
        /*
            保留代码，业务扩展可用
            HSSFRow titleCell = hssfSheet.getRow(0);
            int minCell = hssfRow.getFirstCellNum();
            int maxCell = hssfRow.getLastCellNum();
            HSSFCell id = hssfRow.getCell(0);
            info1.setId((long) id.getNumericCellValue());
        */
        for (int i = 1; i <= hssfSheet.getLastRowNum(); i++) {
            ExcelInfo info1 = new ExcelInfo();
            HSSFRow hssfRow = hssfSheet.getRow(i);
            HSSFCell name = hssfRow.getCell(1);
            HSSFCell age = hssfRow.getCell(2);
            info1.setName(name.getStringCellValue());
            info1.setAge((int) age.getNumericCellValue());
            infos.add(info1);
        }
    } catch (Exception e) {
        LOG.info("文件解析错误，文件不能解析" + e.getMessage());
        throw new MsgException("文件上传失败");
    } finally {
        //finally释放流
        try {
            if (is != null) {
                is.close();
            }
        } catch (Exception e) {
            LOG.info("输入流释放异常" + e.getMessage());
            e.printStackTrace();
        }
        try {
            if (hssfWorkbook != null) {
                hssfWorkbook.close();
            }
        } catch (Exception e) {
            LOG.info("Poi解析释放异常" + e.getMessage());
            e.printStackTrace();
        }
        try {
            if (fos != null) {
                fos.close();
            }
        } catch (Exception e) {
            LOG.info("输出流释放异常" + e.getMessage());
            e.printStackTrace();
        }
    }
    return infos;
}

//判断是否为Excel文件
public static void validExcel(MultipartFile multipartFile) {

    //获取文件名
    String fileName = multipartFile.getOriginalFilename();

    //利用正则表达式根据文件名判断是否为.xls或者.xlsx的文件
    if (fileName.matches("^.+\\.(?i)(xls)$") || fileName.matches("^.+\\.(?i)(xlsx)$")) {
        return;
    }
    throw new MsgException("文件格式不正确");
}