Spring Boot - Excel导入功能

Excel导入功能

1.引入poi依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>1.1.2-beta5</version>
</dependency>

2.创建Excel导出工具类

public class EasyExcelUtils {
    /**
     * 读取某个 sheet 的 Excel
     *
     * @param excel       文件
     * @param rowModel    实体类映射，继承 BaseRowModel 类
     * @param sheetNo     sheet 的序号 从1开始
     * @param headLineNum 表头行数，默认为1
     * @return Excel 数据 list
     */
    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo, int headLineNum) throws IOException {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return null;
        }
        reader.read(new Sheet(sheetNo, headLineNum, rowModel.getClass()));
        return excelListener.getDataList();
    }

    /**
     * 返回 ExcelReader
     *
     * @param excel         需要解析的 Excel 文件
     * @param excelListener new ExcelListener()
     * @throws IOException
     */
    private static ExcelReader getReader(MultipartFile excel, ExcelListener excelListener) throws IOException {
        String filename = excel.getOriginalFilename();
        if (filename != null && (filename.toLowerCase().endsWith(".xls") || filename.toLowerCase().endsWith(".xlsx"))) {
            InputStream is = new BufferedInputStream(excel.getInputStream());
            return new ExcelReader(is, null, excelListener, false);
        } else {
            return null;
        }
    }
}

3.创建EasyExcel回调监听

@Data
@EqualsAndHashCode(callSuper = true)
public class ExcelListener extends AnalysisEventListener {
    //自定义用于暂时存储data
    private List<Object> dataList = new ArrayList<>();
    //导入表头
    private String importHeads = "";
    //模版表头
    private String modelHeads = "";

    /**
     * 通过 AnalysisContext 对象还可以获取当前 sheet，当前行等数据
     */
    @Override
    public void invoke(Object o, AnalysisContext analysisContext) {
        Integer currentRowNum = analysisContext.getCurrentRowNum();
        //获取导入表头，默认第一行为表头
        if (currentRowNum == 0) {
            try {
                Map<String, Object> m = objToMap(o);
                for (Object v : m.values()) {
                    importHeads += String.valueOf(v).trim() + ",";
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        } else {
            dataList.add(o);
        }
    }

    /**
     * 读取完之后的操作
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        //获取模版表头
        ExcelHeadProperty ehp = analysisContext.getExcelHeadProperty();
        for (List<String> s : ehp.getHead()) {
            modelHeads += s.get(0) + ",";
        }
    }

    //Object转换为Map
    public Map<String, Object> objToMap(Object obj) throws Exception {
        Map<String, Object> map = new LinkedHashMap<String, Object>();
        Field[] fields = obj.getClass().getDeclaredFields();
        for (Field field : fields) {
            field.setAccessible(true);
            map.put(field.getName(), field.get(obj));
        }
        return map;
    }
}

4.创建service

@Service
public class ExcelService {
    //上传Excel批量保存
    public String uploadAndSaveList(MultipartFile file) throws IOException {
        List<Object> list = EasyExcelUtils.readExcel(file, new 你的实体类(), 1, 2);
        if (list != null && list.size() > 0) {
            List<你的实体类> entities = new ArrayList<>();
            for (Object o : list) {
                你的实体类 entity = (你的实体类) o;
                //todo 在这里可以对实体类进行赋值等操作
                entities.add(entity);
            }
            //todo 这里批量保存刚刚处理好的实体类
            this.saveBatch(entities);
        }
        return "success";
    }
}

5.创建controller

@RestController
@RequestMapping("excel/")
public class ExcelController {
    @Autowired
    private ExcelService excelService;
    /**
     * 文件上传
     */
    @PostMapping("upload")
    public String upload(@RequestParam("file") MultipartFile file, HttpServletRequest req) throws IOException {
        return excelService.uploadAndSaveList(file);
    }
}

Q&A

没有cell_style_map字段

BaseRowModel类中有一个属性，就是这个属性导致了报错。

private Map<Integer,CellStyle> cellStyleMap = new HashMap<Integer,CellStyle>();

需要在实体类中加一句这个：

/*
    这个是EasyExcel自带的字段，要兼容mybatis就要把这个写上
 */
@TableField(exist = false)
private Map<Integer, CellStyle> cellStyleMap = new HashMap<Integer, CellStyle>();