SpringBoot整合easyexcel，导入参数校验，批注导出 |
您所在的位置：网站首页 › javaexcel导入数据出错后回滚所有数据 › SpringBoot整合easyexcel，导入参数校验，批注导出 |
SpringBoot整合easyexcel，导入参数校验，批注导出
|
思路
导入时，数据全部读取完，进行参数校验
如果参数校验失败后，将Excel导入的数据和校验错误信息，存到Redis中，最后将数据导出
添加依赖
com.alibaba
easyexcel
2.2.6
com.google.code.gson
gson
2.8.6
控制层
一个导入，一个下载模板，一个导出批注后的模板 @Autowired RedisUtil redisUtil; @Autowired GsonBuilder gsonBuilder; private static final String PREFIX = "easyExcel_"; public static final String SEND_LIST = "sendList_"; public static final String SEND_LIST_ERROR = "_error"; public static final Long EXPIRE_TIME = 60 * 10L; @GetMapping(value = "importExcel") @ApiOperation("导入") public Result importExcel( @RequestParam(value = "file", required = true) MultipartFile file ) throws IOException { SendListListener sendListListener = new SendListListener(); EasyExcel.read(file.getInputStream(), SendListExcel.class, sendListListener).sheet().doRead(); List listExcels = sendListListener.getListExcels(); Gson gson = gsonBuilder.create(); if (sendListListener.getExcelErrorMap().size() > 0) { String uuid = IdUtils.id32(); String key = PREFIX + SEND_LIST + uuid; redisUtil.set(key, gson.toJson(listExcels), EXPIRE_TIME); redisUtil.set(key + SEND_LIST_ERROR, gson.toJson(sendListListener.getExcelErrorMap()), EXPIRE_TIME); return Result.error(uuid); } listExcels.forEach(System.out::println); return Result.success(); } @GetMapping("downloadExcelTemplate") @ApiOperation("下载Excel模板") public void export(HttpServletResponse response) throws Exception { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); String fileName = "发送名单管理模板.xlsx"; response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8")); EasyExcel.write(response.getOutputStream(), SendListExcel.class).sheet("sheet1").doWrite(new ArrayList()); } @GetMapping("downloadErrorExcel") @ApiImplicitParams(value = { @ApiImplicitParam(name = "uuid", dataType = "String", value = "校验参数失败后，返回的uuid") }) @ApiOperation("下载批注后到错误excel") public void downloadErrorExcel( HttpServletResponse response, @RequestParam(value = "uuid", required = true) String uuid ) throws IOException { SimpleDateFormat fDate = new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss"); String fileName = fDate.format(new Date()) + ".xlsx"; response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); CommentWriteHandler commentWriteHandler = new CommentWriteHandler(); String key = PREFIX + SEND_LIST + uuid; String listExcelJson = (String) redisUtil.get(key); String listExcelErrorlJson = (String) redisUtil.get(key + SEND_LIST_ERROR); Gson gson = gsonBuilder.create(); if (listExcelJson != null && listExcelErrorlJson != null) { Type listExcelJsonType = new TypeToken() { }.getType(); List sendListExcels = gson.fromJson(listExcelJson, listExcelJsonType); Type listExcelErrorlJsonType = new TypeToken() { }.getType(); Map errorMap = gson.fromJson(listExcelErrorlJson, listExcelErrorlJsonType); commentWriteHandler.setExcelErrorMap(errorMap); EasyExcel.write(response.getOutputStream(), SendListExcel.class) .inMemory(Boolean.TRUE) .sheet("sheet1") //注册批注拦截器 .registerWriteHandler(commentWriteHandler) .doWrite(sendListExcels); } }导出实体类 import com.alibaba.excel.annotation.ExcelIgnoreUnannotated; import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.annotation.write.style.ColumnWidth; import com.alibaba.excel.annotation.write.style.ContentRowHeight; import com.alibaba.excel.annotation.write.style.HeadRowHeight; import lombok.Data; import java.io.Serializable; /** * @Description： * @Author：chenyanbin * @Date：2021/2/3 10:20 上午 * @Versiion：1.0 */ @Data @ExcelIgnoreUnannotated() @ContentRowHeight(10) @HeadRowHeight(20) public class SendListExcel implements Serializable { @ExcelProperty(value = "账号",index = 0) @ColumnWidth(20) private String account; @ExcelProperty(value = "模板编号",index = 1) @ColumnWidth(30) private String templateCode; @ExcelProperty(value = "类型",index = 2) @ColumnWidth(15) private String accountType; } EasyExcel监听器 import com.alibaba.excel.context.AnalysisContext; import org.apache.commons.lang3.StringUtils; import java.util.ArrayList; import java.util.List; /** * @Description： * @Author：chenyanbin * @Date：2021/2/3 10:40 上午 * @Versiion：1.0 */ public class SendListListener extends AnalysisEventListenerAdapter { private List listExcels = new ArrayList(); public SendListListener() { super(); listExcels.clear(); excelErrorMap.clear(); } /** * 每一条数据解析都会调用 */ @Override public void invoke(SendListExcel sendListExcel, AnalysisContext analysisContext) { listExcels.add(sendListExcel); } /** * 所有数据解析完成都会调用 */ @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { SendListExcel sle = null; boolean isMatch = true; for (int i = 0; i < listExcels.size(); i++) { sle = listExcels.get(i); isMatch = true; Integer accountCellIndex = EasyExcelUtil.getCellIndex(sle, "account"); if (accountCellIndex != null) { if (StringUtils.isAllBlank(sle.getAccount())) { setExcelErrorMaps(i, accountCellIndex, "账号不能为空！"); } } Integer templateCodeCellIndex = EasyExcelUtil.getCellIndex(sle, "templateCode"); if (templateCodeCellIndex != null) { if (StringUtils.isAllBlank(sle.getTemplateCode())) { setExcelErrorMaps(i, templateCodeCellIndex, "模板编号不能为空！"); } } Integer accountTypeCellIndex = EasyExcelUtil.getCellIndex(sle, "accountType"); if (accountTypeCellIndex != null) { if (StringUtils.isAllBlank(sle.getAccountType())) { setExcelErrorMaps(i, accountTypeCellIndex, "类型不能为空！"); } else { if ("sms".equals(sle.getAccountType()) || "email".equals(sle.getAccountType()) || "wechat".equals(sle.getAccountType())) { isMatch = false; } if (isMatch) { setExcelErrorMaps(i, accountTypeCellIndex, "类型只允许：sms、email、wechat"); } } } } } public List getListExcels() { return listExcels; } } 其他  
import com.alibaba.excel.write.handler.AbstractRowWriteHandler;
import org.apache.poi.ss.usermodel.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @Description：AbstractRowWriteHandler适配器
* @Author：chenyanbin
* @Date：2021/2/3 10:04 上午
* @Versiion：1.0
*/
public abstract class AbstractRowWriteHandlerAdapter extends AbstractRowWriteHandler {
protected Map excelErrorMap = new HashMap();
public void setExcelErrorMap(Map excelErrorMap) {
this.excelErrorMap = excelErrorMap;
}
/**
* 设置单元格批注
* @param sheet sheet
* @param rowIndex 行索引
* @param colIndex 列索引
* @param value 批注
*/
protected void setCellCommon(Sheet sheet, int rowIndex, int colIndex, String value) {
Workbook workbook = sheet.getWorkbook();
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
Row row = sheet.getRow(rowIndex);
if (row == null) {
return;
}
Cell cell = row.getCell(colIndex);
if (cell == null) {
cell = row.createCell(colIndex);
}
if (value == null) {
cell.removeCellComment();
return;
}
Drawing drawing = sheet.createDrawingPatriarch();
CreationHelper factory = sheet.getWorkbook().getCreationHelper();
ClientAnchor anchor = factory.createClientAnchor();
Row row1 = sheet.getRow(anchor.getRow1());
if (row1 != null) {
Cell cell1 = row1.getCell(anchor.getCol1());
if (cell1 != null) {
cell1.removeCellComment();
}
}
Comment comment = drawing.createCellComment(anchor);
RichTextString str = factory.createRichTextString(value);
comment.setString(str);
comment.setAuthor("admin");
cell.setCellComment(comment);
cell.setCellStyle(cellStyle);
}
}
AbstractRowWriteHandlerAdapter.java
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @Description：AnalysisEventListener适配器
* @Author：chenyanbin
* @Date：2021/2/3 10:09 上午
* @Versiion：1.0
*/
public abstract class AnalysisEventListenerAdapter extends AnalysisEventListener {
protected Map excelErrorMap = new HashMap();
public Map getExcelErrorMap() {
return excelErrorMap;
}
/**
* 设置批注集合
*
* @param rowsNum 行数
* @param cellIndex 单元格索引
* @param msg 错误信息
*/
protected void setExcelErrorMaps(int rowsNum, int cellIndex, String msg) {
if (excelErrorMap.containsKey(rowsNum)) {
List excelErrors = excelErrorMap.get(rowsNum);
excelErrors.add(new ExcelError(rowsNum, cellIndex, msg));
excelErrorMap.put(rowsNum, excelErrors);
} else {
List excelErrors = new ArrayList();
excelErrors.add(new ExcelError(rowsNum, cellIndex, msg));
excelErrorMap.put(rowsNum, excelErrors);
}
}
}
AnalysisEventListenerAdapter.java
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import java.util.List;
/**
* @Description：将参数校验失败的Exccel，添加批注后导出
* @Author：chenyanbin
* @Date：2021/2/3 10:38 上午
* @Versiion：1.0
*/
public class CommentWriteHandler extends AbstractRowWriteHandlerAdapter {
@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
if (!isHead){
Sheet sheet = writeSheetHolder.getSheet();
if (excelErrorMap.containsKey(relativeRowIndex)) {
List excelErrors = excelErrorMap.get(relativeRowIndex);
excelErrors.forEach(obj -> {
setCellCommon(sheet, obj.getRow() + 1, obj.getColumn(), obj.getErrorMsg());
});
}
}
}
}
CommentWriteHandler.java
import java.io.Serializable;
/**
* @Description：批注错误实体类
* @Author：chenyanbin
* @Date：2021/2/3 10:05 上午
* @Versiion：1.0
*/
public class ExcelError implements Serializable {
/** 第几行 从1开始计数 */
private int row;
/** 第几列 从1开始计数 */
private int column;
/** 错误消息 */
private String errorMsg;
public ExcelError(int row, int column, String errorMsg) {
this.row = row;
this.column = column;
this.errorMsg = errorMsg;
}
public int getRow() {
return row;
}
public int getColumn() {
return column;
}
public String getErrorMsg() {
return errorMsg;
}
@Override
public String toString() {
return "ExcelError{" +
"row=" + row +
", column=" + column +
", errorMsg='" + errorMsg + '\'' +
'}';
}
}
ExcelError.java
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.extern.slf4j.Slf4j;
import java.lang.reflect.Field;
/**
* @Description：EasyExcel工具类
* @Author：chenyanbin
* @Date：2021/2/3 10:01 上午
* @Versiion：1.0
*/
@Slf4j
public class EasyExcelUtil {
/**
* 获取Excel单元格的索引
*
* @param obj JavaBean对象
* @param fieldValue JavaBean字段值
* @return
*/
public static Integer getCellIndex(Object obj, String fieldValue) {
try {
Field declaredField = obj.getClass().getDeclaredField(fieldValue);
ExcelProperty annotation = declaredField.getAnnotation(ExcelProperty.class);
if (annotation == null) {
return null;
}
return annotation.index();
} catch (NoSuchFieldException e) {
log.error("error:", e);
}
return null;
}
}
EasyExcelUtil.java
功能演示
|
【本文地址】
今日新闻 |
推荐新闻 |