分页查询和报表导出

Pages工具类

 public class Pages<T> implements Serializable{
        //总记录数
        private long totalNum;
        //每页记录数
        private int pageSize;
        //总页数
        private long totalPage;
        //当前页数
        private int currentPage;
        //列表数据
        private List<T> dataList;

        public Pages(long totalNum, int pageSize, long totalPage, int currentPage, List<T> dataList) {
            this.totalNum = totalNum;
            this.pageSize = pageSize;
            this.currentPage = currentPage;
            this.dataList = dataList;
            this.totalPage = (int) Math.ceil((double) totalNum/pageSize);
        }

        public Pages(Page<T> page, List<T> dataList) {
            this.dataList = dataList;
            this.totalNum = page.getTotal();
            this.pageSize = page.getPageSize();
            this.currentPage = page.getPageNum();
            this.totalPage = page.getPages();
        }

        public Pages() {
            super();
        }
    }

导出controller类

 static Map<String, String> ExcelStateMap = new LinkedHashMap<>();
        public String export(@RequestBody Student student){
            String path = "";
            ExcelStateMap.put(path, "Ready");
            this.cleanPreMap();
            ExcelStateMap.put(path, "Begin");
            try {
                path = exportService.export(student);
                logger.info("读取到的文件路径:" + path);
                if (StringUtils.isNotBlank(path)) {
                    if (!"queryempty".equals(path)) {
                        RespReportExcel respReportExcel = new RespReportExcel();
                        File tempFile = new File(path.trim());
                        String fileName = tempFile.getName();
                        respReportExcel.setFileName(fileName);
                        //文件转base64码
                        String strBase64 = null;
                        try (InputStream in = new FileInputStream(path);) {
                            //in.available()返回文件的字节长度
                            byte[] bytes = new byte[in.available()];
                            //将文件中的内容读入到数组中
                            in.read(bytes);
                            Base64.Encoder encoder = Base64.getEncoder();
                            strBase64 = encoder.encodeToString(bytes);
                        } catch (FileNotFoundException e) {
                            logger.info("{}", e);
                        }
                        respReportExcel.setPath(strBase64);
                        ExcelStateMap.put(path, "Complete");
                    } else {
                        path = "";
                    }
                } else {
                    logger.info("报表导出失败", e);
                }

            } catch (Exception e) {
                logger.info("报表导出失败", e);
            }
            return path;
        }


 /**
         * 数据太多的时候，清楚，防止占用内存过大 默认10000L
         * */
        public void cleanPreMap(){
            Long maxNum = 10000L;
            if (ExcelStateMap.size()>maxNum){
                for (Iterator<Map.Entry<String,String>> iter = ExcelStateMap.entrySet().iterator(); iter.hasNext();){
                    iter.remove();
                    break;;
                }
            }
        }

RespReportExcel 实体中的字段：

//文件路径
private String path;
//文件名
private String fileName;

service

    //查询导出数据
    public Pages<Student> allStudents(Student student){
        Page<Student> page = PageHelper.startPage(student.getPageNumber(),student.getPageSize());
        List<Student> listStudents = studentMapper.all(student);
        Pages<Student> pageList = new Pages<Student>(page,listStudents);
        return pageList;
    }
    /*
    * 查询总条数时设置方法名比查询数据时多加_COUNT
    * eg:
    *   <select id ='all'>
        <select id ='all_COUNT'>
    * */

	//导出
    public String export(Student student){
        String path =null;
        student.setPageNumber(0);
        student.setPageSize(0);
        Pages<Student> allList = this.allStudents(student);
        if (null == allList){
            return "queryempty";
        }
        List<Student> list = allList.getDataList();
        if (CollectionUtils.isEmpty(list)){
            return "queryempty";
        }
        //文件导出
        String reportPath = ""; //模板文件路径
        String writePath = ""; //导出文件路径
        File file = new File(reportPath);
        if (file != null){
            try {
                InputStream is = new FileInputStream(file);
                XSSFWorkbook wb = new XSSFWorkbook(is);
                XSSFSheet sheet = wb.getSheetAt(0);
                //起始行号
                int rowNum = 1;
                //序号
                int num = 0;
                for (Student stu : list) {
                    logger.info("导出数据"+stu.toString());
                    XSSFRow row = sheet.createRow(rowNum);
                    rowNum++;
                    num++;
                    XSSFCell cell0 = row.createCell(0);
                    XSSFCell cell1 = row.createCell(1);
                    XSSFCell cell2 = row.createCell(2);
                    XSSFCell cell3 = row.createCell(3);
                    XSSFCell cell4 = row.createCell(4);
                    cell0.setCellValue(num);
                    cell0.setCellValue(stu.getId());
                    cell0.setCellValue(stu.getName());
                    cell0.setCellValue(stu.getAge());
                    cell0.setCellValue(stu.getSex());
                }
                logger.info("导出数量："+list.size());
                path = writePath + "学生统计报表" + new SimpleDateFormat(new Date(),"yyyyMMddHHmmSS")+".xlsx";
                FileOutputStream os = new FileOutputStream(new File(path));
                wb.write(os);
                os.close();
                logger.info("学生统计报表到处路径:"+path);

            }catch (Exception e){
                logger.info("异常"+e.getMessage());
            }
        }else {
            return path = "noFile";
        }
        return path;
    }

模板样式：