SXSSFworkbook导出excel

最新推荐文章于 2024-08-16 10:51:30 发布

认真工作的小码农

最新推荐文章于 2024-08-16 10:51:30 发布

阅读量663

点赞数

文章标签： java mysql

本文链接：https://blog.csdn.net/weixin_43125025/article/details/106803037

版权

1.需要的依赖，项目基于ssm框架，利用的是mybatis分页插件

<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.11</version>
			<scope>compile</scope>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.11</version>
			<scope>compile</scope>
		</dependency>
		#分页插件
			<dependency>
			<groupId>com.github.pagehelper</groupId>
			<artifactId>pagehelper</artifactId>
			<version>4.1.6</version>
		</dependency>

2.controller层的编写

String fileName = DateFormatUtils.format(new Date(), "yyyyMMdd") + ApplyConstant.EXCEL_FILE_SUFFIX;
            String userAgent = request.getHeader("User-Agent");
            // 针对IE或者以IE为内核的浏览器：
            if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
                fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
            } else {
                fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
            }
            wb = apiService.exportExcelList(blackoutUserRequestVo);
            response.setContentType("application/octet-stream;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
            wb.write(response.getOutputStream());

3.service层编写

  logger.info("导出excel文件接口,请求参数：【{}】", JSONObject.toJSONString(blackoutUserRequestVo));
        long starts = System.currentTimeMillis();
        Map<String,Object> map = new HashMap<>(8);
        Workbook wb = new SXSSFWorkbook(7000); // 创建工作簿，1000代表只在内存中保存1000条。
        String[] titles = {"编号","名称","地址","数据","用户分类","分类"};
        String[] fileId = {"connums","conname","conaddr","voltages","conCus","consTagType"};

        blackoutUserRequestVo.setRows(30000);//三万行一个sheet页 写多个sheet页
        PageHelper.startPage(blackoutUserRequestVo.getPageNum(), blackoutUserRequestVo.getPageSize());// 这里是只在第一页开始,每次查询2000条，插入到excel中
        //这一步直接查表
        int sheets = 1;
        List<Map<String, Object>> list = apiMapper.getResult();
        while (list != null && list.size() > 0) { // 当查询数据不为空的时候继续设置数据入excel
            Sheet sh = wb.createSheet("第"+sheets+"页"); // 创建第一页
            Row titleRow = sh.createRow(0); // 创建第一行
            for (int i = 0; i < titles.length; i++) { // excel表头
                titleRow.createCell(i).setCellValue(titles[i]);
            }
            sheets++;
            BlackoutUserRequestVo blk = new BlackoutUserRequestVo();
            blk.setRows(30000);
            for (int i = 0; i < list.size(); i++) {
                Row row = sh.createRow(blk.getStart() + i + 1);
                for (int k = 0; k < fileId.length; k++) {
                    row.createCell(k).setCellValue(StringUtil.format(list.get(i).get(fileId[k])));
                }
            }
            blackoutUserRequestVo.setPage(blackoutUserRequestVo.getPageNum() + 1);
//false 是不进行统计行数的
            PageHelper.startPage(blackoutUserRequestVo.getPageNum(), blackoutUserRequestVo.getPageSize(), false);
        list = apiMapper.getResult();
        }
        logger.error("导出excel耗时"+(System.currentTimeMillis() - starts));
        return wb;