需要导入的包
<!-- hutool工具包 -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<!-- JSR 303 规范验证包 -->
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-validator</artifactId>
<version>5.2.4.Final</version>
</dependency>
<!-- easypoi依赖 -->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>${easypoi.version}</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>${easypoi.version}</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>${easypoi.version}</version>
</dependency>
服务消费者
@PostMapping(value = "/batchAddGoodsByExcel")
void batchAddGoodsByExcel(@RequestPart("file") MultipartFile file,
@RequestParam(value = "merchantId", required = true) String merchantId,
HttpServletResponse response) {
String fileName = file.getOriginalFilename();
log.info("文件名是：{}", fileName);
Response fileResponse = goodsFeign.batchAddGoodsByExcel(file, merchantId);
InputStream is = null;
try {
is = fileResponse.body().asInputStream();
FileUtil.fileExportCommon("导入商品错误信息表.xls", is, response);
} catch (IOException e) {
e.printStackTrace();
}
}
Feign
@PostMapping(value = "/goods-item/batchAddGoodsByExcel", consumes = MediaType.MULTIPART_FORM_DATA_VALUE)
Response batchAddGoodsByExcel(@RequestPart("file") MultipartFile file, @RequestParam(value =
"merchantId", required = true) String merchantId);
服务提供者
实体
获取错误消息需要实现IExcelModel类
@Data
public class ExcelAddGoods implements Serializable, IExcelModel {
private static final long serialVersionUID = 6906719125697903013L;
@Excel(name = "商品名称")
@NotEmpty(message = "不能为空")
private String goodsName;
@Excel(name = "商品分组")
@NotEmpty(message = "不能为空")
private String goodsGroup;
@Excel(name = "商品类目")
@NotEmpty(message = "不能为空")
private String goodsCategory;
@Excel(name = "规格名称")
@NotEmpty(message = "不能为空")
private String specName;
@Excel(name = "价格/元")
@NotNull(message = "不能为空")
private BigDecimal goodsPrice;
@Excel(name = "包装费/元")
@NotNull(message = "不能为空")
private BigDecimal packagePrice;
@Excel(name = "最大库存")
@NotNull(message = "不能为空")
@Max(value = 9999, message = "最多9999")
@Min(value = 1, message = "最少为1")
private Integer goodsMaxStock;
@Excel(name = "属性")
private String goodsAttributes;
@Excel(name = "商品描述")
private String goodsDesc;
@Excel(name = "标签")
private String goodsTag;
@Excel(name = "最小购买量（默认无限制）")
@NotNull(message = "不能为空")
@Max(value = 99, message = "最多99")
@Min(value = 0, message = "最少为0")
private Integer minBuyNum;
@Excel(name = "原材料")
private String material;
private String errorMsg;
@Override
public String getErrorMsg() {
return errorMsg;
}
@Override
public void setErrorMsg(String s) {
this.errorMsg = s;
}
}
下面这个实体是为了获取父类的错误消息的，最终返回的错误信息实体就是以这个实体返回的
@Data
public class ExcelAddGoodsFailed extends ExcelAddGoods {
private static final long serialVersionUID = 6906719125697903013L;
@Excel(name = "错误消息")
private String errorMsg;
@Override
public String getErrorMsg() {
return errorMsg;
}
@Override
public void setErrorMsg(String s) {
this.errorMsg = s;
}
public static ExcelAddGoodsFailed excelAddGoods2Failed(ExcelAddGoods excelAddGoods) {
ExcelAddGoodsFailed failed = new ExcelAddGoodsFailed();
failed.setErrorMsg(excelAddGoods.getErrorMsg());
failed.setGoodsAttributes(excelAddGoods.getGoodsAttributes());
failed.setGoodsCategory(excelAddGoods.getGoodsCategory());
failed.setGoodsDesc(excelAddGoods.getGoodsDesc());
failed.setGoodsGroup(excelAddGoods.getGoodsGroup());
failed.setGoodsName(excelAddGoods.getGoodsName());
failed.setGoodsMaxStock(excelAddGoods.getGoodsMaxStock());
failed.setGoodsPrice(excelAddGoods.getGoodsPrice());
failed.setGoodsTag(excelAddGoods.getGoodsTag());
failed.setMaterial(excelAddGoods.getMaterial());
failed.setMinBuyNum(excelAddGoods.getMinBuyNum());
failed.setPackagePrice(excelAddGoods.getPackagePrice());
failed.setSpecName(excelAddGoods.getSpecName());
return failed;
}
public static List<ExcelAddGoodsFailed> excelAddGoods2Faileds(List<ExcelAddGoods> excelAddGoodsList) {
List<ExcelAddGoodsFailed> failedList = new ArrayList<>();
for (ExcelAddGoods excelAddGoods : excelAddGoodsList) {
failedList.add(excelAddGoods2Failed(excelAddGoods));
}
return failedList;
}
}
controller
@PostMapping(value = "/batchAddGoodsByExcel", consumes = MediaType.MULTIPART_FORM_DATA_VALUE)
void batchAddGoodsByExcel(@RequestPart("file") MultipartFile file, @RequestParam(value =
"merchantId", required = true) String merchantId, HttpServletResponse response) {
log.info("上传的文件名是：{},商家ID是：{}", file.getOriginalFilename(), merchantId);
goodsItemService.batchAddGoodsByExcel(file, merchantId, response);
}
service
@Override
public void batchAddGoodsByExcel(MultipartFile file, String merchantId,
HttpServletResponse response) {
try {
ExcelImportResult<ExcelAddGoods> result = ExcelUtil.importExcelMore(file, 0, 1,
ExcelAddGoods.class);
// 这是校验成功的数据
List<ExcelAddGoods> successList = result.getList();
log.info("成功的数据是：{}", JSONUtil.toJsonStr(successList));
//getFailWorkbook()和getFailList()里面的就是所有校验失败的excel数据
List<ExcelAddGoods> failList = result.getFailList();
// Workbook failWorkbook = result.getFailWorkbook();
List<ExcelAddGoodsFailed> addGoodsFaileds = ExcelAddGoodsFailed.excelAddGoods2Faileds(failList);
//将错误excel信息返回给客户端
ExportParams exportParams = new ExportParams();
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, ExcelAddGoodsFailed.class, addGoodsFaileds);
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("导入商品错误信息表", "UTF-8") +
".xls");
response.setCharacterEncoding("UTF-8");
workbook.write(response.getOutputStream());
} catch (Exception e) {
e.printStackTrace();
}
}
工具类
@Slf4j
public class FileUtil {
/**
* @param fileName 文件名，需要带上后缀
* @param is 文件输入流
* @param response 返回流
*/
public static void fileExportCommon(String fileName, InputStream is, HttpServletResponse response) {
OutputStream os = null;
try {
response.setCharacterEncoding("UTF-8");
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName));
IOUtils.copy(is, response.getOutputStream());
response.flushBuffer();
// os = response.getOutputStream();
// byte[] b = new byte[1024];
// int len;
// while ((len = is.read(b)) != -1) {
// os.write(b, 0, len);
// }
// response.flushBuffer();
} catch (IOException e) {
e.printStackTrace();
log.error("文件下载失败....{}", e.getMessage());
} finally {
if (null != is) {
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
导入工具
public class ExcelUtil {
/**
*
* @param file 导入的文件
* @param titleRows 标题占的行
* @param headerRows 头占的行
* @param pojoClass 实体对象
* @param <T>
* @return
* @throws Exception
*/
public static <T> ExcelImportResult<T> importExcelMore(MultipartFile file, Integer titleRows, Integer headerRows,
Class<T> pojoClass) throws Exception {
if (file == null) {
return null;
}
ImportParams params = new ImportParams();
//开启验证，代表导入这里是需要验证的（根据字段上的注解）
params.setNeedVerify(true);
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
return ExcelImportUtil.importExcelMore(file.getInputStream(), pojoClass, params);
}
}
测试
导入的Excel，故意导入有问题的文件。
image.png
结果
image.png
如果导入的Excel文件中有没校验通过的数据，那么就会自动将这些数据进行返回下载。如下：
image.png
** 这里只是演示了easypoi的基本的校验方式，还有自定义的方式可以使用，这里因为没使用到，所以没做展示，有需要了解的同学可以自行百度哈 **
