Java导入excel文件（xlsx）

java导入excel文件（xlsx）

项目背景：
1、SpringBoot项目
2、前端上传excel文件，后端读取文件内容中的数据并保存到数据库中
废话不多说，直接上代码

1、controller代码

@ResponseBody
@PostMapping("/importCheckSpeaker")
public Object importCheckSpeaker(@RequestParam("file") MultipartFile file, HttpServletRequest request, HttpServletResponse response) {
    if (file.isEmpty()) {
        return CheckParamUtil.getResultMap(ErrorMessage.UPLOAD_ERROR, "文件为空", null);
    }
    try {
        int count = speakerCheckService.importCheckSpeaker(file);
        if (count > 0) {
            return CheckParamUtil.getsuccess("导入成功");
        }
    } catch (Exception e) {
        logger.error(e.getMessage(), e);
    }
    return CheckParamUtil.getErrorMap(ErrorMessage.UPLOAD_ERROR);
}

2、service代码

public int importCheckSpeaker(MultipartFile file) throws IOException {
    InputStream is = file.getInputStream();
    List<CheckSpeakerEntity> checkSpeakerList = new ArrayList<>();
    //1.创建工作簿
    HSSFWorkbook workbook = new HSSFWorkbook(is);
    HSSFSheet sheet = null;
    HSSFRow row = null;
    //2.遍历Excel中所有的sheet
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        sheet = workbook.getSheetAt(i);
        if (sheet == null) {
            continue;
        }
        //3.遍历当前sheet中的所有行(从第二行开始，序号为1)
        for (int j = 1; j < sheet.getLastRowNum(); j++) {
            row = sheet.getRow(j);
            if (row == null) {
                continue;
            }
            CheckSpeakerEntity checkSpeakerEntity = new CheckSpeakerEntity();
            //4.把每个单元格的值赋给对象的对应属性
            checkSpeakerEntity.setVoice_id(row.getCell(0).getStringCellValue());
            checkSpeakerEntity.setCtrl_num(row.getCell(1).getStringCellValue());
            checkSpeakerEntity.setOrder_num(Integer.parseInt(row.getCell(2).getStringCellValue()));
            checkSpeakerEntity.setCtrl_name(row.getCell(3).getStringCellValue());
            checkSpeakerEntity.setDiscard_flag(Integer.parseInt(row.getCell(4).getStringCellValue()));
            checkSpeakerList.add(checkSpeakerEntity);
        }
    }
    //5.遍历完成，保存数据
    return speakerCheckDao.insertCheckSpeakerList(checkSpeakerList);
}

3、dao层代码

public int insertCheckSpeakerList(List<CheckSpeakerEntity> checkSpeakerList) {
    return sqlSessionTemplate.insert("speakerCheck.insertCheckSpeakerList", checkSpeakerList);
}

4、Mapper文件

<!-- 批量插入数据 -->
<insert id="insertCheckSpeakerList" useGeneratedKeys="true" keyColumn="id" keyProperty="id" parameterType="java.util.List">
    insert into speaker_check
    (voice_id, ctrl_num, order_num, ctrl_name, discard_flag, create_time)
    values
    <foreach collection="checkSpeakerList" item="checkSpeaker" index="index" separator=",">
        (
        #{checkSpeaker.voice_id}, #{checkSpeaker.ctrl_num},
        #{checkSpeaker.order_num},#{checkSpeaker.ctrl_name},
        #{checkSpeaker.discard_flag}, #{checkSpeaker.create_time}
        )
    </foreach>
</insert>