easyexcle-导入
将excel数据导入，如果有数据异常，则导出异常数据，并在前面加入两列，错误行号和错误信息
实现
@Data
public abstract class BaseImportData {
/**
* 错误行号
*/
private Integer rowIndex;
/**
* 错误信息
*/
private String errorMsg;
}
@Slf4j
public abstract class AbstractEventListener<M, T extends BaseImportData> extends AnalysisEventListener<T> {
private static final int BATCH_COUNT = 1000;
private List<T> invalidDataList = new ArrayList<>();
private List<M> dataList = new ArrayList<>();
private Set<T> duplicateDataSet = new HashSet<>();
private int batchCount;
public AbstractEventListener() {
this(BATCH_COUNT);
}
public AbstractEventListener(int batchCount) {
this.batchCount = batchCount;
}
@Override
public void invoke(T data, AnalysisContext context) {
Integer rowIndex = context.readRowHolder().getRowIndex() + 1;
data.setRowIndex(rowIndex);
before(data, context);
if (!duplicateDataSet.add(data)){
//如果数据重复
data.setErrorMsg("重复数据");
invalidDataList.add(data);
return;
}
if (!validate(data, invalidDataList, dataList)) {
return;
}
if (dataList.size() >= BATCH_COUNT) {
saveBatch(dataList);
dataList.clear();
}
}
protected void before(T data, AnalysisContext context) {
//do nothing
}
protected boolean validate(T data, List<T> invalidDataList, List<M> dataList) {
M model = createModel(data);
if (!doValidate(data, model)) {
invalidDataList.add(data);
return false;
}
dataList.add(model);
return true;
}
/**
* 验证数据完整性
* 1.校验数据完整性
* 2.校验数据唯一性
*
* @param data
* @param model
* @return
*/
protected abstract boolean doValidate(T data, M model);
/**
* 保存批量数据
*
* @param dataList
*/
protected abstract void saveBatch(List<M> dataList);
/**
* 创建对象
*
* @param data
* @return
*/
protected abstract M createModel(T data);
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
log.info("解析完成");
if (!dataList.isEmpty()) {
saveBatch(dataList);
dataList.clear();
}
duplicateDataSet.clear();
}
/**
* 获取无效数据
*
* @return
*/
public List<T> getInvalidDataList() {
return invalidDataList;
}
}
@Service
public class EasyExcelService {
public OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
//创建本地文件
fileName = java.net.URLEncoder.encode(fileName + ".xlsx", "UTF-8");
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
return response.getOutputStream();
}
public <T extends BaseImportData> boolean importData(MultipartFile file, AbstractEventListener<?, T> listener, Class<T> clazz,
Supplier<OutputStream> supplier) throws IOException {
EasyExcel.read(file.getInputStream(), clazz, listener).sheet().doRead();
List<T> invalidDataList = listener.getInvalidDataList();
if (CollectionUtils.isEmpty(invalidDataList)) {
return true;
}
List<List<String>> header = getErrorHeader(clazz);
WriteCellStyle errorCellStyle = new WriteCellStyle();
errorCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
errorCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
WriteCellStyle normalCellStyle = new WriteCellStyle();
// 创建样式策略
/* HorizontalCellStyleStrategy styleStrategy = new HorizontalCellStyleStrategy(
normalCellStyle, // 头样式
Arrays.asList(errorCellStyle, errorCellStyle) // 内容样式
);*/
//列样式，前两列标色
AbstractVerticalCellStyleStrategy verticalCellStyleStrategy = new AbstractVerticalCellStyleStrategy() {
@Override
protected WriteCellStyle headCellStyle(Head head) {
Integer columnIndex = head.getColumnIndex();
if (columnIndex == 0 || columnIndex == 1) {
return errorCellStyle;
}
return null;
}
@Override
protected WriteCellStyle contentCellStyle(Head head) {
Integer columnIndex = head.getColumnIndex();
if (columnIndex == 0 || columnIndex == 1) {
return errorCellStyle;
}
return null;
}
};
List<List<Object>> lists = convertToErrorData(invalidDataList);
EasyExcel.write(supplier.get()).head(header).registerWriteHandler(verticalCellStyleStrategy).sheet().doWrite(lists);
return false;
}
private <T extends BaseImportData> List<List<Object>> convertToErrorData(List<T> invalidDataList) {
List<List<Object>> errorData = new ArrayList<>();
for (T data : invalidDataList) {
List<Object> rowData = new ArrayList<>();
// 添加错误行号（第一列）
rowData.add(data.getRowIndex());
// 添加错误信息（第二列）
rowData.add(data.getErrorMsg());
// 添加原始数据
Field[] fields = data.getClass().getDeclaredFields();
for (Field field : fields) {
if (field.isAnnotationPresent(ExcelProperty.class)) {
try {
field.setAccessible(true);
rowData.add(field.get(data));
} catch (IllegalAccessException e) {
rowData.add(null);
}
}
}
errorData.add(rowData);
}
return errorData;
}
/**
* 获取导出表头
* @param clazz
* @return
* @param <T>
*/
private <T extends BaseImportData> List<List<String>> getErrorHeader(Class<T> clazz) {
Field[] declaredFields = clazz.getDeclaredFields();
List<List<String>> originHeader = Stream.of(declaredFields).filter(field -> field.isAnnotationPresent(ExcelProperty.class))
.map(field -> field.getAnnotation(ExcelProperty.class))
.map(excel -> new ArrayList<>(Arrays.asList(excel.value()))).collect(Collectors.toList());
List<List<String>> header = new ArrayList<>();
header.add(Arrays.asList("错误行号"));
header.add(Arrays.asList("错误信息"));
header.addAll(originHeader);
return header;
}
}
示例
@Data
public class SimpleImportData extends BaseImportData {
@ExcelProperty(value = "姓名", index = 0)
private String name;
@ExcelProperty(value = "居住地", index = 1)
private String address;
@ExcelProperty(value = "年龄", index = 2)
private Integer age;
}
@Slf4j
public class SimpleImportListener extends AbstractEventListener<SimpleImportModel, SimpleImportData> {
@Override
protected boolean doValidate(SimpleImportData data, SimpleImportModel model) {
if (StrUtil.isBlank(data.getName())) {
data.setErrorMsg("用户姓名为空");
return false;
}
BeanUtils.copyProperties(data,model);
return true;
}
@Override
protected void saveBatch(List<SimpleImportModel> dataList) {
log.info("保存数据：{}", JSON.toJSONString(dataList));
}
@Override
protected SimpleImportModel createModel(SimpleImportData data) {
return new SimpleImportModel();
}
}
@Data
public class SimpleImportModel {
private String name;
private String address;
private Integer age;
}
