场景说明

在实际开发中，经常需要解析Excel数据来插入数据库，而且通常会有一些要求，比如：全部校验成功才入库、校验成功入库，校验失败返回提示（总数、成功数、失败数、失败每行明细、导出失败文件明细…）

代码实现

数据库表

CREATE TABLE `forlan_student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;


<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.3</version>
</dependency>

导入模板

public class ForlanStudentExcelModule {

    @ExcelProperty(value = "姓名", index = 0)
    private String name;

    @ExcelProperty(value = "年龄", index = 1)
    private Integer age;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }
}

错误失败模板

@HeadStyle(horizontalAlignment = HorizontalAlignment.LEFT)
@ContentStyle(horizontalAlignment = HorizontalAlignment.LEFT)
public class ForlanStudentErrorExcelModule {

    @ColumnWidth(20)
    @ExcelProperty(value = "失败原因", index = 0)
    private String excelOneLineErrorMsg;

    @ColumnWidth(10)
    @ExcelProperty(value = "姓名", index = 1)
    private String name;

    @ColumnWidth(10)
    @ExcelProperty(value = "年龄", index = 2)
    private Integer age;

    public String getExcelOneLineErrorMsg() {
        return excelOneLineErrorMsg;
    }

    public void setExcelOneLineErrorMsg(String excelOneLineErrorMsg) {
        this.excelOneLineErrorMsg = excelOneLineErrorMsg;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "ForlanStudentErrorExcelModule{" +
                "excelOneLineErrorMsg='" + excelOneLineErrorMsg + '\'' +
                ", name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}


@RestController
public class ExcelController {

@Autowired
private ForlanStudentService forlanStudentService;

@RequestMapping("/excel/import")
public String importFromExcel(@RequestParam(value = "file") MultipartFile param) {
    // 校验文件类型
    String fileName = param.getOriginalFilename();
    if (StringUtils.isEmpty(fileName) || !(fileName.endsWith(".xlsx") || fileName.endsWith(".xls"))) {
        return "文件后缀需为.xlsx或.xls";
    }
    return forlanStudentService.doImport(param);
}

}


public interface ForlanStudentService {
    String doImport(MultipartFile param);
}

a、主方法

@Override
public String doImport(MultipartFile param) {
    String result = "导入成功";
    try (InputStream inputStream = param.getInputStream()) {
        // 解析Excel对象流转成需要的对象
        List<ForlanStudent> forlanStudentList = processExcel(inputStream);
        // 最终入库数据
        List<ForlanStudent> insertData = new ArrayList<>();

        // 校验数据，并填充符合的数据
        List<ForlanStudentErrorExcelModule> forlanStudentErrorExcelModule = checkDataAndFill(forlanStudentList, insertData);
        if (!CollectionUtils.isEmpty(forlanStudentErrorExcelModule)) {
            // 要求全部校验通过的话，这里可以直接return
            // 需要的话，转成JSON返回，好看些
            result = forlanStudentErrorExcelModule.toString();
            // 可以生成错误文件，返回错误文件路径
            // result = generateExceptionFile(forlanStudentErrorExcelModule);
        }
        if(!CollectionUtils.isEmpty(insertData)){
            // 数据入库，根据自己需要写
            forlanStudentDao.insertBatch(insertData);
        }
    } catch (Exception e) {
        e.printStackTrace();
        return "导入失败";
    }
    return result;
}

b、解析Excel数据转为List对象

private List<ForlanStudent> processExcel(InputStream inputStream) throws Exception {
    List<ForlanStudent> forlanStudentList = new ArrayList<>();

    Integer maxRows = 100;
    // 导入模板表头
    List<String> chineseHeader = Arrays.asList("姓名", "年龄");
    // 0是表头
    final int headerRows = 0;

    try (Workbook workbook = WorkbookFactory.create(inputStream)) {
        Sheet sheet = workbook.getSheetAt(0);
        int totalRow = sheet.getLastRowNum();
        if (totalRow == 0) {
            throw new Exception("文件模板错误");
        } else if (totalRow - headerRows > maxRows) {
            throw new Exception(String.format("单次导入数据不能超过%s条", maxRows));
        }

        // 遍历每行
        for (int rowIndex = 0; rowIndex <= totalRow; rowIndex++) {
            Row currentRow = sheet.getRow(rowIndex);
            if (currentRow == null) {
                continue;
            }
            // 读取数据行
            List<String> cellList = new ArrayList<>();
            for (int columnIndex = 0; columnIndex <= 1; columnIndex++) {
                Cell currentCell = currentRow.getCell(columnIndex);
                cellList.add(formatCellValue(currentCell));
            }

            // 校验模板是否正确
            if (rowIndex <= headerRows) {
                if (rowIndex == 0 && !cellList.equals(chineseHeader)) {
                    throw new Exception("文件模板错误");
                }
                continue;
            }

            if (null != cellList && !cellList.isEmpty()) {
                ForlanStudent forlanStudent = new ForlanStudent();
                forlanStudent.setName(cellList.get(0));
                forlanStudent.setAge(Integer.valueOf(cellList.get(1)));
                forlanStudentList.add(forlanStudent);
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
        throw new Exception(e.getMessage());
    }

    return forlanStudentList;
}

public static String formatCellValue(Cell cell) {

    if (cell == null) {
        return "";
    }
    if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
        return String.valueOf(cell.getBooleanCellValue());
    } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {

        if (HSSFDateUtil.isCellDateFormatted(cell)) {
            double d = cell.getNumericCellValue();
            Date date = HSSFDateUtil.getJavaDate(d);
            return new SimpleDateFormat("yyyy/MM/dd HH:mm:ss\"").format(date);
        } else {
            // 强制将数字转字符串
            DecimalFormat format = new DecimalFormat("0.00");
            Number value = cell.getNumericCellValue();
            String phone = format.format(value).replace(".00", "");
            return String.valueOf(phone);
        }
    } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        return String.valueOf(cell.getNumericCellValue());
    } else {
        try {
            return cell.getStringCellValue() == null ? "" : cell.getStringCellValue().trim();
        } catch (Exception e) {
            return cell.toString() == null ? "" : cell.toString().trim();
        }
    }
}

c、校验数据，并填充入库数据、错误数据行

private List<ForlanStudentErrorExcelModule> checkDataAndFill(List<ForlanStudent> forlanStudentList, List<ForlanStudent> insertData) {
    List<ForlanStudentErrorExcelModule> errorExcelModules = new ArrayList<>();

    // 校验数据，支持拓展功能，比如，统计总量、成功数、失败数...
    forlanStudentList.forEach(p -> {
        if (StringUtils.isBlank(p.getName()) || null == p.getAge()) {
            ForlanStudentErrorExcelModule forlanStudentErrorExcelModule = new ForlanStudentErrorExcelModule();
            BeanUtils.copyProperties(p, forlanStudentErrorExcelModule);
            forlanStudentErrorExcelModule.setExcelOneLineErrorMsg("请填写必填项");
            errorExcelModules.add(forlanStudentErrorExcelModule);
            return;
        }
        if (p.getAge() < 0) {
            ForlanStudentErrorExcelModule forlanStudentErrorExcelModule = new ForlanStudentErrorExcelModule();
            BeanUtils.copyProperties(p, forlanStudentErrorExcelModule);
            forlanStudentErrorExcelModule.setExcelOneLineErrorMsg("年龄不能小于0");
            errorExcelModules.add(forlanStudentErrorExcelModule);
            return;
        }

        // 如果没有跳过，说明符合入库
        ForlanStudent forlanStudent = new ForlanStudent();
        BeanUtils.copyProperties(p, forlanStudent);
        insertData.add(forlanStudent);
    });

    return errorExcelModules;
}

d、错误数据行原因生成文件

private String generateExceptionFile(List<ForlanStudentErrorExcelModule> forlanStudentErrorExcelModuleList) {
    File file = new File("导入文件校验失败原因.xlsx");
    ExcelWriter excelWriter = EasyExcel.write(file).build();
    WriteSheet errorDataSheet = EasyExcel.writerSheet("导入失败原因").head(ForlanStudentErrorExcelModule.class).build();
    excelWriter.write(forlanStudentErrorExcelModuleList, errorDataSheet);
    excelWriter.finish();
    // 可以上传到OOS或者七牛云...然后然后路径
    return file.getPath();
}

e、复制对象内容

public class BeanUtils {

    public static Map<String, BeanCopier> beanCopierMap = new HashMap<String, BeanCopier>();

    public static void copyListProperties(List source, List desc, Class descClazz) {
        for (Object o : source) {
            try {
                Object d = descClazz.newInstance();
                copyProperties(o, d);
                desc.add(d);
            } catch (InstantiationException e) {
                throw new RuntimeException(e);
            } catch (IllegalAccessException e) {
                throw new RuntimeException(e);
            }
        }

    }

    public static void copyProperties(Object source, Object target) {
        if (source != null) {
            String beanKey = generateKey(source.getClass(), target.getClass());
            if (!beanCopierMap.containsKey(beanKey)) {
                BeanCopier copier = BeanCopier.create(source.getClass(), target.getClass(), false);
                beanCopierMap.put(beanKey, copier);
            }
            beanCopierMap.get(beanKey).copy(source, target, null);
        }
    }

    private static String generateKey(Class<?> cls1, Class<?> cls2) {
        return cls1.toString() + cls2.toString();
    }
}