EasyExcel web下载excel,多sheet页demo

EasyExcel web下载excel,多sheet页demo

pom.xml

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

<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-collections4</artifactId>
    <version>4.1</version>
</dependency>
<dependency>
    <groupId>org.apache.xmlbeans</groupId>
    <artifactId>xmlbeans</artifactId>
    <version>2.6.0</version>
</dependency>

 <dependency>
     <groupId>org.apache.poi</groupId>
     <artifactId>poi-ooxml</artifactId>
     <version>3.17</version>
 </dependency>

 <dependency>
     <groupId>org.apache.poi</groupId>
     <artifactId>poi-ooxml-schemas</artifactId>
     <version>3.17</version>
 </dependency>

 <dependency>
     <groupId>org.apche.poi</groupId>
     <artifactId>poi</artifactId>
     <version>3.17</version>
 </dependency>

前端js（lockPage(), unlockPage(),Dialog.alertECM()方法省略）

        /* 导出excel */
        var exportInterval;
        function exportExcel() {
        	//锁屏，可以替换为进度条
         	lockPage(); 
 			var params = "{";
 			params += "'START_TIME':" + "'" + startDate + "',";
            params += "'END_TIME':" + "'" + endDate + "'";
            params += "}";
            dataIsEmpty(params)
        }
        /*检查报表导出数据是否为空，非空再下载报表*/
        function dataIsEmpty(params) {
            var url = "/项目名/batch/batchScan!exportExcel.action";
            var strURL = url + "?params=" + encodeURI(params);
            $.ajax({
                url: "/项目名/batch/batchScan!dataIsEmpty.action";,
                data: {"params": params},
                success: function (data) {
                    if (data == "true") {
                        unlockPage();
                        Dialog.alertECM("导出报表数据为空，请重新输入导出条件！");
                    } else if (data == "exception") {
                        unlockPage();
                        Dialog.alertECM("导出报表数据异常，请稍后重试！")
                    } else {
                        window.location = strURL;
                        //定时器，每隔1s请求后端检查excel是否写入完毕
                        //若写入完毕自动弹出下载框，并且解开屏幕和删除定时器
                        exportInterval = setInterval(function () {
                            exportIsEnd();
                        }, 1000);
                    }
                },
                error: function (e) {
                    unlockPage();
                    Dialog.alertECM("导出报表数据失败，请稍后重试！");
                }
            });
        }
        
        /*检查报表导出是否结束*/
        function exportIsEnd() {
            var url =  var url ="/项目名/batch/batchScan!exportIsEnd.action"";
            $.ajax({
                url: url,
                success: function (data) {
                    if (data == "true") {
                    	//解屏，可以替换为进度条，完成后隐藏进度条
                        unlockPage();
                        //删除定时器
                        clearInterval(exportInterval);
                    } else if (data == "exception") {
                        unlockPage();
                        clearInterval(exportInterval);
                        Dialog.alertECM("导出报表数据异常，请稍后尝试！")
                    }
                },
                error: function (e) {
                    unlockPage();
                    clearInterval(exportInterval);
                    Dialog.alertECM("导出报表数据失败，请稍后尝试！")
                }
            });
        }

后端

	/*报表导出*/
	@RequestMapping(value = "/batchScan!exportExcel.action")
	public void exportExcel(HttpServletRequest request, HttpServletResponse response) {
		try {
			request.getSession().setAttribute("exportIsEnd", "false");
			response.setContentType("application/vnd.ms-excel");
			response.setCharacterEncoding("utf-8");
			// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
			String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
			response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
			ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), DemoData.class).build();
			for (int i = 0; i < 10; i++) {
				WriteSheet writeSheet = EasyExcel.writerSheet(i, "测试" + i).build();
				excelWriter.write(data(), writeSheet);
			}
			//不关闭会打不开excel
			excelWriter.finish();
			request.getSession().setAttribute("exportIsEnd", "true");
		} catch (Exception e) {
			request.getSession().setAttribute("exportIsEnd", "exception");
			e.printStackTrace();
		}

	}

	/*报表导出数据是否为空*/
	@RequestMapping(value = "/batchScan!dataIsEmpty.action")
	public void dataIsEmpty(HttpServletRequest request, HttpServletResponse response) {
		try {
			String paramsStr = request.getParameter("params");
			JSONObject paramsJson = JSONObject.fromObject(paramsStr);
			String startDate = paramsJson.getString("START_TIME");
			String endDate = paramsJson.getString("END_TIME");
			boolean flag = true;
			if (flag) {
				response.getWriter().write("true");
			}else {
				response.getWriter().write("false");
			}
		} catch (Exception e) {
			try {
				response.getWriter().write("exception");
			} catch (IOException ex) {
				ex.printStackTrace();
			}
			e.printStackTrace();
		}
	}

	/*检查报表导出是否完成*/
	@RequestMapping(value = "/batchScan!exportIsEnd.action")
	public static void exportIsEnd(HttpServletRequest request, HttpServletResponse response) {
		String exportIsEnd = request.getSession().getAttribute("exportIsEnd").toString();
		try {
			response.getWriter().write(exportIsEnd);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public List<DemoData> data() {
        List<DemoData> list = new ArrayList<DemoData>();
        for (int i = 0; i < 10; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + i);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        return list;
    }

写入excel的实体类

public class DemoData {
    /**@ColumnWidth 字段宽度，@ExcelProperty表头名 @ExcelIgnore不导出的字段*/
    @ColumnWidth(15)
    @ExcelProperty("字符串")
    private String string;
    @ColumnWidth(15)
    @ExcelProperty("日期")
    private Date date;
    @ColumnWidth(15)
    @ExcelProperty("数据")
    private Double doubleData;
    @ExcelIgnore
    private String name;
    //get,set方法省略
   }

ie浏览器点击没反应，原因是ie屏蔽了excel下载
ie屏蔽excel下载。点击ie的设置–internet选项–信任站点–自定义级别将active相关启用，注意要重启电脑才生效。

更多信息见官网：
阿里EasyExcel插件
EasyExcel项目git地址: https://github.com/alibaba/easyexcel
官网地址：https://alibaba-easyexcel.github.io