大数量导出EasyExcel

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel

 尽量使用最新版

如果maven工程，引入相关依赖

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

如果是一个普通web项目，导入相关jar包

下载地址 https://download.csdn.net/download/qq_34139393/85502738

注意：easyexce 要求poi版本最低为3.17，jdk版本1.8+

实现导出Excel

	@RequestMapping("/repeatedWrite")
	@ResponseBody
    public void repeatedWrite(HttpServletRequest request,HttpServletResponse response) {
        long startTime=System.currentTimeMillis();   //获取开始时间
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        try {
        	//设置导出的文件名子
			String fileName = URLEncoder.encode("导出文件测试", "UTF-8");
			response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
			ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
			//这里为了测试固定写为创建10个sheet页，每个sheet页100条数据
	        for (int i = 0; i < 10; i++) {
		          Map<String, Object> pageMap = new HashMap<String,Object>();
		          pageMap.put("page", i+1);
		          pageMap.put("rows", 100);
		          //分页查询数据
		          List<DemoDataVo> data = demoDataVoMapper.findAllData(pageMap);
		          //按分页创建sheet页
		          WriteSheet mainSheet = EasyExcel.writerSheet(i, "第" +(i+1) +"页").head(DemoDataVo.class).build();
				  excelWriter.write(data,mainSheet);
	        }
			excelWriter.finish();
		} catch (IOException e) {
			e.printStackTrace();
		}
        long endTime=System.currentTimeMillis(); //获取结束时间
        System.out.println("程序运行时间： "+(endTime-startTime)+"ms");
    }

实现带连接导出（点击连接跳转到具体某个sheet页）

/**
     * 重复多次写入
     * 带超链接 调整制定sheet页
     */
	@RequestMapping("/repeatedWriteLinkSheet")
	@ResponseBody
    public void repeatedWriteLinkSheet(HttpServletRequest request,HttpServletResponse response) {
        long startTime=System.currentTimeMillis();   //获取开始时间
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        try {
        	//设置导出的文件名子
			String fileName = URLEncoder.encode("导出文件测试", "UTF-8");
			response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
			ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
	        for (int i = 0; i < 10; i++) {
		          Map<String, Object> pageMap = new HashMap<String,Object>();
		          pageMap.put("page", i+1);
		          pageMap.put("rows", 100);
		          //分页查询数据
		          List<DemoDataVo> data = demoDataVoMapper.findAllData(pageMap);
		          for (DemoDataVo demoDataVo : data) {
		        	  //设置超链接
		        	  WriteCellData<String> hyperlink = new WriteCellData<>(demoDataVo.getP1());
		        	  demoDataVo.setHyperlink(hyperlink);
		        	  HyperlinkData hyperlinkData = new HyperlinkData();
		              hyperlink.setHyperlinkData(hyperlinkData);
		              hyperlinkData.setAddress("#第3页!A10");
		              hyperlinkData.setHyperlinkType(HyperlinkType.DOCUMENT);
		              demoDataVo.setHyperlink(hyperlink);
				  }
		          //按分页创建sheet页
		          WriteSheet mainSheet = EasyExcel.writerSheet(i, "第" +(i+1) +"页").head(DemoDataVo.class).build();
				  excelWriter.write(data,mainSheet);
	        }
			excelWriter.finish();
		} catch (IOException e) {
			e.printStackTrace();
		}
        long endTime=System.currentTimeMillis(); //获取结束时间
        System.out.println("程序运行时间： "+(endTime-startTime)+"ms");
    }

官网地址 写excel · 语雀