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>
标签：Java,String,POI,excel,name,getStringCellValue,getCell,manufacturer,row From： https://www.cnblogs.com/sensenh/p/18349023