Java导出Excel百万条数据表格数据实现分页easyexcel方法和Apach POI方法

退无可退而立版

已于 2024-04-15 09:12:26 修改

阅读量3.3k

点赞数 1

分类专栏：数据库 Java 文章标签： java excel 开发语言

于 2023-03-17 17:14:47 首次发布

本文链接：https://blog.csdn.net/beiback/article/details/129622725

版权

Java 同时被 2 个专栏收录

75 篇文章 1 订阅

订阅专栏

数据库

40 篇文章 1 订阅

订阅专栏

`关闭程序的查询sql语句打印日志功能，否则速度很慢`

注意：此导出excel样式只是简单设置而已

1、创建实体类 ExcelModel 导出数据库对应实体数据列表

简单样式设计

MAVEN仓库依赖或者下载依赖jar包

<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.1</version>
</dependency>

关于样式可以看源码默认值进行更改默认设置

参考一些样式  
https://zhuanlan.zhihu.com/p/481547301
https://blog.csdn.net/weixin_47215296/article/details/126102600

@Getter
@Setter
@ContentRowHeight(16)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER)
@HeadRowHeight(30)
@ColumnWidth(35)
@HeadFontStyle(fontName="宋体")
@HeadStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER,shrinkToFit = BooleanEnum.TRUE)

public class ExcelModel {
	@ExcelIgnore		//忽略此表头内容
    private Integer id;// 主键ID
    @ExcelProperty(value = "姓名") //表头内容
    private String name;// 姓名
	@ExcelProperty(value = "年龄")
    private Integer age;// age
	@ExcelProperty(value = "地址")
    private String address;// 地址
	@ExcelProperty(value = "性别")
    private String sex;// 性别
    @ExcelIgnore	
	private Integer pageStartIndex;//起始索引 查询索引位置
	@ExcelIgnore	
    private Integer pageSize;//每页数量  相当于数据库内数据步长
}

1.1创建mapper接口

@Param 选择引入 import org.apache.ibatis.annotations.Param;

	Integer selecttxxxCount(@Param("model")ExcelModel  model);
	List<ExcelModel> selectData(@Param("model")ExcelModel   model);

1.2创建mapper.xml

	<select id="selecttxxxCount" resultType="Integer">
		select  *
		from table_name  
		<where>
		动态sql筛选条件
		</where>
	</select>	


	<select id="selectData" resultType="ExcelModel">
		select  *
		from table_name  
		<where>
		动态sql筛选条件
		</where>
		<if test="model.pageStartIndex!=null and model.pageSize!=null">
	limit #{model.pageStartIndex},#{model.pageSize}
	</select>

2、创建 services 实现类

客户端是下载的作用，数据的处理是在服务器端进行的

xxxClass    类   作用接收前端传递来的对应数据参数 比如 页码、每页大小 其他筛选条件等
xxxentity   类对应的实例
xxxMapper   对应的mapper文件名
//响应输出流在下方使用了  这个就不用了
ServletOutputStream outputStream = response.getOutputStream();

2.1、方法1==》核心代码分页写入–适应数据量百万

select * from table_name  limit  startIndex,step
举例  select * from table_name  limit 15,1000

xxxentity.setPageStartIndex(i * page_size);   ---对应起始索引
xxxentity.setPageSize(page_size);			--对应查询的记录行数

//核心代码
    public void exportExcelData(HttpSession session, HttpServletResponse response, xxxClass xxxentity) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = new String(("定义导出excel名").getBytes(), "ISO8859_1");
        response.setHeader("Content-disposition", "attachment; filename=" +
                fileName + (new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")).format(new Date()) + ".xlsx");
        //查询总条数
        List<Integer> total = xxxMapper.selecttxxxCount(xxxentity);       
        //计算页数
        // 定义每个sheet数据数量
        int page_size = 3000;
        //计算将分多少页
        int export_pagenumber = total % page_size > 0 ? total / page_size + 1 : total / page_size;
		//创建输出流 
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), xxxClass.class).build();
         WriteSheet writeSheet = new WriteSheet();	//创建工作簿
        for (int i = 0; i < export_pagenumber; i++) {           
            writeSheet.setSheetName("第"+(i+1)+"页");
            xxxentity.setPageStartIndex(i * page_size);  //某处起始索引  （固定步长行数*第几个i）
            xxxentity.setPageSize(page_size);//从起始索引开始对应的截取数据 step 步长行数
            List<xxxClass> dataList = xxxMapper.selectData(xxxentity);
            //每循环一次  查出的数据进行写入一个sheet 工作簿
            excelWriter.write(dataList,writeSheet);
        }
        //循环结束   
        excelWriter.finish();//整个Excel文件写数据结束
        response.flushBuffer();//强制刷新
    }

2.2、方法2==》一次性写入 --适应数量1万左右

//核心代码
    public void exportExcelData(HttpSession session, HttpServletResponse response, xxxClass xxxentity) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename="+new Date()+"defineExcelName.xlsx");
  
	//一次性查询数据
		List<xxxClass> dataList= xxxMapper.selectData(xxxentity);
        //循环结束   
       EasyExcel.write(response.getOutputStream(), xxxClass.class).sheet("单个sheet工作簿名称").doWrite(dataList);
    }

2.3、代码误区 ----其实这种方法本质是一次性写入 --错误

导致内存溢出（OOM）

 List<xxxClass> datalist = new ArrayList<>();
       for (int i = 0; i < export_pagenumber; i++) {
            xxxentity.setPageStartIndex(i * page_size);
            xxxentity.setPageSize(page_size);
            List<xxxClass> list= xxxMapper.selectData(xxxentity);
			datalist.addAll(list.subList(0,list.size()));//将查询出的集合集中收集在datalist
        }
EasyExcel.write(response.getOutputStream(), xxxClass.class).sheet("单个sheet工作簿名称").doWrite(dataList);

3、性能比较

在这里插入图片描述

4、导出excel

在这里插入图片描述

5、oom 内存溢出

6、sax模式

SAX解析器读取输入文档并在处理文档时将每个事件推给文档处理器（MyContentHandler）。与DOM相比，SAX解析器能提供更好的性能优势，它提供对XML文档内容的有效低级访问。SAX模型最大的优点是内存消耗小，因为整个文档无需一次加载到内存中，这使SAX解析器可以解析大于系统内存的文档。另外，你无需像在DOM中那样为所有节点创建对象。最后，SAX“推”模型可用于广播环境，能够同时注册多个ContentHandler，并行接收事件，而不是在一个管道中一个接一个地进行处理。

7、Apach POI --大数据量—写不好容易内存溢出

//核心代码
    public void exportExcelData(HttpSession session, HttpServletResponse response, xxxClass xxxentity) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = new String(("定义导出excel名").getBytes(), "ISO8859_1");
        response.setHeader("Content-disposition", "attachment; filename=" +
                fileName + (new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")).format(new Date()) + ".xlsx");
        //查询总条数
        List<Integer> total = xxxMapper.selecttxxxCount(xxxentity);       
        //计算页数
        // 定义每个sheet数据数量
        int page_size = 3000;
        //计算将分多少页
        int export_pagenumber = total % page_size > 0 ? total / page_size + 1 : total / page_size;
		//创建输出流 
		SXSSFWorkbook wbook = new SXSSFWorkbook(1500);	//	1500表示1500行数据时进行写到磁盘不至于内存溢出
 		Sheet sheet = null;
        for (int i = 0; i < export_pagenumber; i++) {      
            sheet = wbook.createSheet("第"+(i+1)+"页");//新建一个sheet
            xxxentity.setPageStartIndex(i * page_size);  //某处起始索引  （固定步长行数*第几个i）
            xxxentity.setPageSize(page_size);//从起始索引开始对应的截取数据 step 步长行数
            List<xxxClass> dataList = xxxMapper.selectData(xxxentity);
            //每循环一次  查出的数据进行写入一个sheet 工作簿
			writeDataToExcel(sheet,dataList);
        }
        //循环结束    
        wbook.write(response.getOutputStream());//写出数据到输出流
        wbook.dispose();
    }


private int currRow = 0;
private void writeDataToExcel(Sheet sheet ,List<xxxClass> xxxentityList){
 for( int j=0;j<xxxentityList.size();j++){
  	Row row = sheet.createRow(currRow++);
  	int k = 0;
   row.createCell(k++).setCellValue(xxxentity.get(j).getId());
   row.createCell(k++).setCellValue(xxxentity.get(j).getUserName());
   row.createCell(k++).setCellValue(xxxentity.get(j).getAge());
 }
}

8 当一个sheet数据量大于100000百万行数据进行分工作簿

    @SneakyThrows(IOException.class)
    @ApiOperation(value = "xxxx记录导出")
    @PostMapping("/xxxxxx")
    public void exporportEasyExcel(HttpServletResponse response,@RequestBody xxxClass xxxentity) {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = new String(("定义导出excel名").getBytes(), "ISO8859_1");
        response.setHeader("Content-disposition", "attachment; filename=" +
                fileName + (new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")).format(new Date()) + ".xlsx");
        List<xxxClass > total = xxxMapper.pageExcel(xxxentity);
        int sheet_max_row = 4;
        int pageNumber = 0;
        int writeCount = 0;
        int step = 2;
        int export_pagenumber = total.size() % step > 0 ? total.size() / step + 1 : total.size() / step;
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(),
         xxxClass.class).build();
        WriteSheet writeSheet = new WriteSheet();
        int i =0;
        for (int i = 0; i < export_pagenumber; i++) {
            xxxentity.setPageStartIndex(i * step);
            xxxentity.setPageSize(step);
            List<xxxClass> dataList = xxxMapper.pageExcel(xxxentity);
            writeCount += dataList.size();
            if (writeCount > sheet_max_row*pageNumber){
               // writeSheet.setSheetName("xxx记录"+pageNumber+"页");
                writeSheet = EasyExcel.writerSheet("第"+(pageNumber++)+"页记录").build();
            }
            excelWriter.write(dataList,writeSheet);
        }
        excelWriter.finish();
        response.flushBuffer();
    }

前端浏览器也可以进行接收并导出下载

import axios from "axios";
import dayjs from "dayjs";

/*Axios 是一个流行的基于 Promise 的 JavaScript HTTP 客户端库，用于在浏览器和Node.js环境中进行HTTP请求。
它提供了一种简单、直观的方式来发送异步 HTTP 请求，并支持处理请求和响应的拦截器、数据转换、取消请求等功能。
使用 Axios 可以轻松地发送 GET、POST、PUT、DELETE 等各种类型的请求，并处理服务器返回的响应数据。*/

function exprotExcelData(url: string, data: object,excelExportName: string = "") {//data? 为需要导出哪些条件的数据 需要进行筛选
    axios({
        method: "POST",
        url: url,
        baseURL: '',//基础api
        data: data,
        responseType: "blob", //定义为blob  二进制文件
    }).then((data) => {
        if (!data) {
            console.error("数据文件下载失败")
        }
        let url = window.URL.createObjectURL(
            new Blob([data.data], { type: "application/vnd.ms-excel" })
        );
        let link = document.createElement("a");
        link.style.display = "none";
        link.href = url;
        let excelExportNameFormat = excelExportName + dayjs().format("YYYY-MM-DD hh:mm");//定义下载导出的文件名称加一些日期格式
        link.setAttribute("download", excelExportNameFormat + ".xlsx");
        document.body.appendChild(link);// 将<a>元素附加到body中
        link.click();
        document.body.removeChild(link); //从文档的 <body> 元素中移除名为 "link" 的子元素。document.body.removeChild(link) 表示删除 <body> 元素下的一个子元素，其中 link 是要删除的子元素。
        window.URL.revokeObjectURL(url); /*window.URL.revokeObjectURL(url) 是用于释放通过 window.URL.createObjectURL() 创建的 URL 对象的方法。
在使用 window.URL.createObjectURL() 方法创建 URL 对象时，通常是为了在浏览器中展示或下载一个 Blob 或 File 对象。一旦完成了对该 URL 的使用，为了释放内存并防止内存泄漏，应该调用 window.URL.revokeObjectURL() 方法来撤销该 URL，使其无效。
在代码中，url 是之前通过 window.URL.createObjectURL() 创建的 URL 对象。通过调用 window.URL.revokeObjectURL(url)，就可以释放和撤销该 URL 对象。*/
    });
}

Excel表头

List<List> headList = new ArrayList<>();
ExcelWriter excelWriter= null;
excelWriter= EasyExcel.write(response.getOutputStream()).head(headList).build();