需要導(dǎo)入的包
<!-- hutool工具包 -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<!-- JSR 303 規(guī)范驗(yàn)證包 -->
<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>
服務(wù)消費(fèi)者
@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("導(dǎo)入商品錯(cuò)誤信息表.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);
服務(wù)提供者
實(shí)體
獲取錯(cuò)誤消息需要實(shí)現(xiàn)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 = "規(guī)格名稱")
@NotEmpty(message = "不能為空")
private String specName;
@Excel(name = "價(jià)格/元")
@NotNull(message = "不能為空")
private BigDecimal goodsPrice;
@Excel(name = "包裝費(fèi)/元")
@NotNull(message = "不能為空")
private BigDecimal packagePrice;
@Excel(name = "最大庫(kù)存")
@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 = "標(biāo)簽")
private String goodsTag;
@Excel(name = "最小購(gòu)買量（默認(rèn)無(wú)限制）")
@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;
}
}
下面這個(gè)實(shí)體是為了獲取父類的錯(cuò)誤消息的，最終返回的錯(cuò)誤信息實(shí)體就是以這個(gè)實(shí)體返回的
@Data
public class ExcelAddGoodsFailed extends ExcelAddGoods {
private static final long serialVersionUID = 6906719125697903013L;
@Excel(name = "錯(cuò)誤消息")
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);
// 這是校驗(yàn)成功的數(shù)據(jù)
List<ExcelAddGoods> successList = result.getList();
log.info("成功的數(shù)據(jù)是：{}", JSONUtil.toJsonStr(successList));
//getFailWorkbook()和getFailList()里面的就是所有校驗(yàn)失敗的excel數(shù)據(jù)
List<ExcelAddGoods> failList = result.getFailList();
// Workbook failWorkbook = result.getFailWorkbook();
List<ExcelAddGoodsFailed> addGoodsFaileds = ExcelAddGoodsFailed.excelAddGoods2Faileds(failList);
//將錯(cuò)誤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("導(dǎo)入商品錯(cuò)誤信息表", "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();
}
}
}
}
}
導(dǎo)入工具
public class ExcelUtil {
/**
*
* @param file 導(dǎo)入的文件
* @param titleRows 標(biāo)題占的行
* @param headerRows 頭占的行
* @param pojoClass 實(shí)體對(duì)象
* @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();
//開(kāi)啟驗(yàn)證，代表導(dǎo)入這里是需要驗(yàn)證的（根據(jù)字段上的注解）
params.setNeedVerify(true);
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
return ExcelImportUtil.importExcelMore(file.getInputStream(), pojoClass, params);
}
}
測(cè)試
導(dǎo)入的Excel，故意導(dǎo)入有問(wèn)題的文件。
image.png
結(jié)果
image.png
如果導(dǎo)入的Excel文件中有沒(méi)校驗(yàn)通過(guò)的數(shù)據(jù)，那么就會(huì)自動(dòng)將這些數(shù)據(jù)進(jìn)行返回下載。如下：
image.png
** 這里只是演示了easypoi的基本的校驗(yàn)方式，還有自定義的方式可以使用，這里因?yàn)闆](méi)使用到，所以沒(méi)做展示，有需要了解的同學(xué)可以自行百度哈 **
