Java使用POI导入excel记录

1.controller：

@PostMapping("/import-excel")
@Transactional
public AjaxResult importExcel(@RequestPart(value = "file") MultipartFile file) throws Exception {
    String result = manufacturerService.importExcel(file);
    return AjaxResult.success(result);
}

2.service：

String importExcel(MultipartFile file) throws Exception;

3.Impl：

@Override
@Transactional
public String importExcel(MultipartFile file) throws Exception {
    List<Manufacturer> manufacturerList = new ArrayList<>();
    String fileName = file.getOriginalFilename();
    assert fileName != null;
    String name = fileName.substring(0, fileName.indexOf("."));
    String hzm = fileName.substring(name.length() + 1);
    if (StringUtils.equals(hzm, "xlsx")) {
        //根据路径获取这个操作excel表格
        XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());
        //根据页面index 获取sheet页
        XSSFSheet sheet = wb.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        for (int i = 1; i <= lastRowNum; i++) {
            XSSFRow row = sheet.getRow(i);  // 获取行
            if (row != null) {
                List<String> list = new ArrayList<>();
                for (int j = 0; j <= 8; j++) {
                    if (row.getCell(j) != null) {
                        row.getCell(j).setCellType(CellType.STRING);
                        String value = row.getCell(j).getStringCellValue();
                        if (value != null && !"".equals(value))
                            list.add(value);
                    } else {
                        row.createCell(j);
                    }
                }
                if (!list.isEmpty()) {
                    Manufacturer manufacturer = new Manufacturer();
                    if (StringUtils.equals(hzm, "xlsx")) {
                        manufacturer.setName(row.getCell(1).getStringCellValue());
                        manufacturer.setCredit(row.getCell(2).getStringCellValue());
                        manufacturer.setAbbreviation(row.getCell(3).getStringCellValue());
                        manufacturer.setAddress(row.getCell(4).getStringCellValue());
                        manufacturer.setContactPerson(row.getCell(5).getStringCellValue());
                        String nameExcel = row.getCell(6).getStringCellValue();
                        if (!StringUtils.isEmpty(nameExcel)) {
                            DistrictDict dict = recManufacturerDao.findByManufacturerName(nameExcel);
                            manufacturer.setDescriptionId(dict.getId());
                        }
                        manufacturer.setContactPhone(row.getCell(7).getStringCellValue());
                        String categoryNameExcel = row.getCell(8).getStringCellValue();
                        if (!StringUtils.isEmpty(categoryNameExcel)) {
                            DevCategory devCategory = devCategoryMapper.findByCategoryName(categoryNameExcel);
                            manufacturer.setDeviceCategoryId(devCategory.getId());
                        }
                    }
                    manufacturerList.add(manufacturer);
                }
            } else {
                row = sheet.createRow(i);
            }
        }
    } else {
        throw new RuntimeException("请您导入后缀为：xlsx类型的Excel文件");
    }
    Map<String, Manufacturer> nameMapManuParam = Map.of();
    if (!CollectionUtils.isEmpty(manufacturerList)) {
        nameMapManuParam = manufacturerList.stream()
                .filter(item -> item.getName() != null).distinct()
                .collect(Collectors.toMap(Manufacturer::getName, Manufacturer -> Manufacturer));
    }
    List<Manufacturer> manuListDB = recManufacturerDao.findAllManu();
    Set<String> dbManuName = manuListDB.stream().map(Manufacturer::getName).collect(Collectors.toSet());
    List<Manufacturer> newManuList = nameMapManuParam.keySet().stream().filter(nameParam -> !dbManuName.contains(nameParam)).map(nameMapManuParam::get).collect(Collectors.toList());
    int insertNum = 0;
    if (!CollectionUtils.isEmpty(newManuList)) {
        insertNum = recManufacturerDao.insertBatch(newManuList);
    }
    List<Manufacturer> beUpdateList = nameMapManuParam.keySet().stream().filter(dbManuName::contains).map(nameMapManuParam::get).collect(Collectors.toList());
    int updateNum = 0;
    if (!CollectionUtils.isEmpty(beUpdateList)) {
        recManufacturerDao.updateByExcel(beUpdateList);
        updateNum = beUpdateList.size();
    }
    return "成功导入：" + (insertNum + updateNum) + "条数据。新增：" + insertNum + "条数据; 更新：" + updateNum + "条数据";
}

4.Mapper：

int insertBatch(@Param("manufacturerList") List<Manufacturer> manufacturerList);

Integer updateByExcel(@Param("beUpdateList") List<Manufacturer> beUpdateList);

5.xml：

<insert id="insertBatch">
    INSERT INTO rec_manufacturer ( abbreviation, address, contact_person, contact_phone, credit, name, description_id, device_category_id)
    VALUES
    <foreach collection="manufacturerList" item="manufacturer" separator=",">
        (#{manufacturer.abbreviation}, #{manufacturer.address}, #{manufacturer.contactPerson}, #{manufacturer.contactPhone}, #{manufacturer.credit}, #{manufacturer.name}, #{manufacturer.descriptionId}, #{manufacturer.deviceCategoryId})
    </foreach>
</insert>

<update id="updateByExcel" parameterType="integer">
    <foreach collection="beUpdateList" item="d" separator=";">
        UPDATE rec_manufacturer
        <set>
            <if test="d.abbreviation != '' and d.abbreviation != null">
                abbreviation = #{d.abbreviation},
            </if>
            <if test="d.address != '' and d.address != null">
                address = #{d.address},
            </if>
            <if test="d.contactPerson != '' and d.contactPerson != null">
                contact_person = #{d.contactPerson},
            </if>
            <if test="d.contactPhone != '' and d.contactPhone != null">
                contact_phone = #{d.contactPhone},
            </if>
            <if test="d.credit != '' and d.credit != null">
                credit = #{d.credit},
            </if>
            <if test="d.name != '' and d.name != null">
                name = #{d.name},
            </if>
            <if test="d.descriptionId != '' and d.descriptionId != null">
                description_id = #{d.descriptionId},
            </if>
            <if test="d.deviceCategoryId != '' and d.deviceCategoryId != null">
                device_category_id = #{d.deviceCategoryId},
            </if>
        </set>
        where name = #{d.name}
    </foreach>
</update>