16.excel

poi
Alibaba EasyExcel

https://www.yuque.com/easyexcel/doc/read

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


## 16.1 编写excel专用的pojo

```java
public class PrivilegeData {

    @ExcelProperty("权限编号")
    private Long id;
    @ExcelProperty("权限规则")
    private String uri;
    @ExcelProperty("权限名称")
    private String name;
    @ExcelProperty("权限创建时间")
    private Date created;
    @ExcelProperty("权限状态")
    private Integer status;

    //***
}

16.2 编写下载的controller

/**
 * Created by zxd on 2021/7/15 15:39
 */
@WebServlet("/privilege/export")
public class DownPrivilegeExcelController extends HttpServlet {


    private PrivilegeService privilegeService=new PrivilegeServiceImpl();


    @Override
    protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("权限", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");


        //查询所有的权限
        List<Privilege> privilegeList = privilegeService.findPrivilegeAll();


        List<PrivilegeData> privilegeDataList=new ArrayList<>();

        for (Privilege privilege:privilegeList) {

            PrivilegeData privilegeData=new PrivilegeData();

            privilegeData.setId(privilege.getId());
            privilegeData.setUri(privilege.getUri());
            privilegeData.setStatus(privilege.getStatus());
            privilegeData.setName(privilege.getName());
            privilegeData.setCreated(privilege.getCreated());

            privilegeDataList.add(privilegeData);
        }


        EasyExcel.write(response.getOutputStream(), PrivilegeData.class).sheet("模板").doWrite(privilegeDataList);
    }
}

16.3 excel导入

编写excel监听器

/**
 * Created by zxd on 2021/7/15 16:01
 */
public class PrivilegeDataListener  extends AnalysisEventListener<PrivilegeData> {
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    List<PrivilegeData> list = new ArrayList<PrivilegeData>();

    private PrivilegeService privilegeService=new PrivilegeServiceImpl();

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data
     *            one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(PrivilegeData data, AnalysisContext context) {
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }
    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
    }
    /**
     * 加上存储数据库
     */
    private void saveData() {


        for (PrivilegeData privilegeData:list) {

            Privilege privilege=new Privilege();
            privilege.setName(privilegeData.getName());
            privilege.setStatus(privilegeData.getStatus());
            privilege.setUri(privilegeData.getUri());

            privilegeService.insertPrivilege(privilege);

        }

    }
}

编写controller

@WebServlet("/privilege/import")
@MultipartConfig(maxFileSize = 1024*1024*3)
public class UploadPrivilegeExcelController extends HttpServlet {


    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

        //1.没有配置上传
        //2.没有提交事务
        Part part = req.getPart("excel");


        EasyExcel.read(part.getInputStream(), PrivilegeData.class, new PrivilegeDataListener()).sheet().doRead();

        //提交事务
        MybatisUtils.commitTransaction();
        MybatisUtils.closeSqlSession();

        //返回成功
        ResponseUtils.responseToJson(JsonUtils.objectToJson(ResultUtils.buildSuccess()),resp);

    }


}