easyexcel导入数据、导出数据、下载模版

 导入数据

//传入参数,读取信息
        EasyExcelFactory.readBySax(new BufferedInputStream(inputStream), new Sheet(1, 1, YearCheckExcel.class), new AnalysisEventListener() {
            private List<Object> easyExcelList = new ArrayList<>();
            private int excel_batch_count = 200;
            @Override
            public void invoke(Object o, AnalysisContext analysisContext) {
                easyExcelList.add(o);
                if (easyExcelList.size() >= excel_batch_count) {
                    saveToDataBase();
                    log.info("清空list");
                    easyExcelList.clear();
                }
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                saveToDataBase();
                log.info("清空list");
                easyExcelList.clear();
            }
            private void saveToDataBase(){
                log.info("开始导入。。。");
            }
        });

导出数据

href="javascript:window.location.href = '${ctxPath}/export?' + $('#searchForm').serialize();"

 

    @GetMapping("exportExcel")
    public void exportExcel(Integer pageNo,
                                        Integer pageSize
                                        String orderBy) {
        OutputStream outputStream = ...
        List<XXXExcel> XXXExcels = new ArrayList<>();
        pageSize = 0;
		//调用查询分页接口
        Page<Entity> entityPage = queryPage(pageNo, pageSize, orderBy);
        List<Entity> list = (List<Entity>) entityPage.getList();
        for (int i = 0; i < list.size(); i++) {
            Entity entity = list.get(i);
            entity.setXuHao(i + 1);
            XXXExcels.add(entity);
        }
        EasyExcel.write(outputStream, XXXExcel.class)
                .excelType(ExcelTypeEnum.XLSX)
                .sheet("信息")
                .doWrite(XXXExcels);
    }

​​​​​​​

    @GetMapping(value = "exportXxx", produces = "text/html;charset=UTF-8")
    public void exportXxx(@RequestParam Map params, HttpServletResponse response, HttpServletRequest request) throws IOException {
        Xxx xxx= JSON.parseObject(JSON.toJSONString(params), Xxx.class);
        OutputStream outputStream = response.getOutputStream();
        try {
            //添加响应头信息
            response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode("xxx.xlsx", "UTF-8"));
            response.setContentType("text/html;charset=UTF-8");//设置类型
            response.setHeader("Pragma", "No-cache");//设置头
            response.setHeader("Cache-Control", "no-cache");//设置头
            //实例化 ExcelWriter
            ExcelWriter writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX, true);
            //实例化表单
            Sheet sheet = new Sheet(1, 0, Xxx.class);
            sheet.setSheetName("xxx数据");

            //获取数据
            List<XxxExcel> xxxExcels = new ArrayList<>();
            List<LinkedHashMap> maps = xxxService.findXxxsEntity(xxx);
            for (int i = 0; i < maps.size(); i++) {
                LinkedHashMap linkedHashMap = maps.get(i);
               xxxExcels.add(JSON.parseObject(JSON.toJSONString(linkedHashMap), Xxx.class));
            }
            //输出
            writer.write(xxxExcels, sheet);
            writer.finish();
            outputStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

下载模版

    @GetMapping(value = "downloadXxx", produces = "text/html;charset=UTF-8")
    public void downloadXxx(HttpServletResponse response) {
        try {
            //添加响应头信息
            response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode("xxx（模版）.xlsx", "UTF-8"));
            response.setContentType("text/html;charset=UTF-8");//设置类型
            response.setHeader("Pragma", "No-cache");//设置头
            response.setHeader("Cache-Control", "no-cache");//设置头

            OutputStream outputStream = response.getOutputStream();
            //实例化 ExcelWriter
            ExcelWriter writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX, true);

            //实例化表单
            Sheet sheet = new Sheet(1, 0, XxxExcel.class);
            sheet.setSheetName("xxx数据");
            //获取数据
            List<XxxExcel> xxxExcels = null;

            //输出
            writer.write(null, sheet);
            writer.finish();
            outputStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }