Java使用easyexcel导出到excel并下载

Java使用easyexcel导出到excel并下载

使用工具easyexcel
工具介绍：easyexcel重写了poi对07版Excel的解析，能够原本一个3M的excel用POI sax依然需要100M左右内存降低到KB级别，并且再大的excel不会出现内存溢出，03版依赖POI的sax模式。在上层做了模型转换的封装，让使用者更加简单方便

比Hutool导出excel还要简单

1，pom文件

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>{latestVersion}</version>
</dependency>

2，Java映射模型

旧方法

需要注意的就是红框两点

更新

根据最新更新的git上来看，不需要继承BaseRowModel了，这也符合使用习惯
只需要注解 @ExcelProperty(value = “标识” ,index = 0)，不需要加入到excel列中只需要加注解 @ExcelIgnore

3，controller层

旧方法

~~@GetMapping("/download")
	public void download(HttpServletResponse response,PageQuery page ,Date time ,String keyword,String[] levelArr, String[] typeArr, String[] substationArr, String[] equipmentArr) {
		OutputStream out = null;
		ExcelWriter writer = null;
        try {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmm");
    		String date = sdf.format(new Date());
    		//通过查询组装数据
    		List<Alarm> alarmlist = alarmService.downloadResults(pageDate, time, "测试", levelArr, typeArr, substationArr, equipmentArr);
    		//拼接导出的文件名
    		//String fileName = new String(("告警信息"+date).getBytes("UTF-8"),"ISO-8859-1")+".xlsx";
    		String fileName = date + new String(("告警数据").getBytes("UTF-8"),"ISO-8859-1") + ".xlsx";
    		//response.setCharacterEncoding("utf-8");
            //response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.addHeader("Content-Disposition", "attachment;filename="+fileName);
    		//下载到浏览器默认地址
    		out = response.getOutputStream();
    		writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
    		Sheet sheet1 = new Sheet(1, 0, Alarm.class);
            sheet1.setSheetName("sheet1");
    		writer.write(alarmlist, sheet1);
    		alarmlist.clear();
		} catch (Exception e) {
			log.error("告警信息下载失败"+e.getMessage(),e);
		} finally {
			try {
				out.flush();
				writer.finish();
				out.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
        }
	}~~ 

使用方式也有更新

@GetMapping("/download")
    public void download(HttpServletResponse response,Integer page, Integer size, String types, Date startTime, Date deadline, String likeWord){
        try {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmm");
            String date = sdf.format(new Date());
            List<Event> alarmlist = service.download(page, size, types, startTime, deadline, likeWord);
            String fileName = "Alarm"+date + ".xlsx";
            response.setHeader("Content-Disposition", "attachment;filename="+fileName);
            EasyExcel.write(response.getOutputStream(), Event.class).sheet("模板").doWrite(alarmlist);
        } catch (Exception e) {
            log.error("告警信息下载失败"+e.getMessage(),e);
        }
    }

需要注意的是，导出的文件名，不可以有空格，也就是new SimpleDateFormat(“yyyyMMddHHmm”);不可以写成new SimpleDateFormat(“yyyyMMdd HHmm”);

4，导出文件格式