java快速搞定excel导出（easypoi）

水深无声 2021-12-14 05:57 364阅读 0赞

一、在pom.xml中添加easypoi的maven依赖

<dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-base</artifactId>
                <version>3.0.3</version>
            </dependency>
            <dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-web</artifactId>
                <version>3.0.3</version>
            </dependency>
            <dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-annotation</artifactId>
                <version>3.0.3</version>
            </dependency>

二、编写实体类，使用@Excel注解指定要导出的字段

package com.ldy.demo.vo;
    
    import java.io.Serializable;
    
    import org.jeecgframework.poi.excel.annotation.Excel;
    
    public class UserVO implements Serializable {
    
    	private static final long serialVersionUID = -4237467344258292698L;
    
    	// id不导出，不用加@Excel注解
    	private Integer id;
    
    	@Excel(name = "姓名")
    	private String name;
    
    	@Excel(name = "年龄", type = 10)
    	private Integer age;
    
    	@Excel(name = "电话")
    	private String phone;
    
    	@Excel(name = "邮箱")
    	private String email;
    
    	@Excel(name = "联系地址")
    	private String address;
    
    	public Integer getId() {
    		return id;
    	}
    
    	public void setId(Integer id) {
    		this.id = id;
    	}
    
    	public String getName() {
    		return name;
    	}
    
    	public void setName(String name) {
    		this.name = name;
    	}
    
    	public Integer getAge() {
    		return age;
    	}
    
    	public void setAge(Integer age) {
    		this.age = age;
    	}
    
    	public String getPhone() {
    		return phone;
    	}
    
    	public void setPhone(String phone) {
    		this.phone = phone;
    	}
    
    	public String getEmail() {
    		return email;
    	}
    
    	public void setEmail(String email) {
    		this.email = email;
    	}
    
    	public String getAddress() {
    		return address;
    	}
    
    	public void setAddress(String address) {
    		this.address = address;
    	}
    
    }

**@Excel注解参数说明**

<table> 
 <thead> 
  <tr> 
   <th>属性</th> 
   <th>类型</th> 
   <th>类型</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>name</td> 
   <td>String</td> 
   <td>null</td> 
   <td>列名</td> 
  </tr> 
  <tr> 
   <td>needMerge</td> 
   <td>boolean</td> 
   <td>fasle</td> 
   <td>纵向合并单元格</td> 
  </tr> 
  <tr> 
   <td>orderNum</td> 
   <td>String</td> 
   <td>"0"</td> 
   <td>列的排序,支持name_id</td> 
  </tr> 
  <tr> 
   <td>replace</td> 
   <td>String[]</td> 
   <td>{}</td> 
   <td>值得替换 导出是{a_id,b_id} 导入反过来</td> 
  </tr> 
  <tr> 
   <td>savePath</td> 
   <td>String</td> 
   <td>"upload"</td> 
   <td>导入文件保存路径</td> 
  </tr> 
  <tr> 
   <td>type</td> 
   <td>int</td> 
   <td>1</td> 
   <td>导出类型 1 是文本 2 是图片,3 是函数,10 是数字 默认是文本</td> 
  </tr> 
  <tr> 
   <td>width</td> 
   <td>double</td> 
   <td>10</td> 
   <td>列宽</td> 
  </tr> 
  <tr> 
   <td>height</td> 
   <td>double</td> 
   <td>10</td> 
   <td>列高,后期打算统一使用@ExcelTarget的height,这个会被废弃,注意</td> 
  </tr> 
  <tr> 
   <td>isStatistics</td> 
   <td>boolean</td> 
   <td>fasle</td> 
   <td>自动统计数据,在追加一行统计,把所有数据都和输出这个处理会吞没异常,请注意这一点</td> 
  </tr> 
  <tr> 
   <td>isHyperlink</td> 
   <td>boolean</td> 
   <td>false</td> 
   <td>超链接,如果是需要实现接口返回对象</td> 
  </tr> 
  <tr> 
   <td>isImportField</td> 
   <td>boolean</td> 
   <td>true</td> 
   <td>校验字段,看看这个字段是不是导入的Excel中有,如果没有说明是错误的Excel,读取失败,支持name_id</td> 
  </tr> 
  <tr> 
   <td>exportFormat</td> 
   <td>String</td> 
   <td>""</td> 
   <td>导出的时间格式,以这个是否为空来判断是否需要格式化日期</td> 
  </tr> 
  <tr> 
   <td>importFormat</td> 
   <td>String</td> 
   <td>""</td> 
   <td>导入的时间格式,以这个是否为空来判断是否需要格式化日期</td> 
  </tr> 
  <tr> 
   <td>format</td> 
   <td>String</td> 
   <td>""</td> 
   <td>时间格式,相当于同时设置了exportFormat 和 importFormat</td> 
  </tr> 
  <tr> 
   <td>databaseFormat</td> 
   <td>String</td> 
   <td>"yyyyMMddHHmmss"</td> 
   <td>导出时间设置,如果字段是Date类型则不需要设置 数据库如果是string 类型,这个需要设置这个数据库格式,用以转换时间格式输出</td> 
  </tr> 
  <tr> 
   <td>numFormat</td> 
   <td>String</td> 
   <td>""</td> 
   <td>数字格式化,参数是Pattern,使用的对象是DecimalFormat</td> 
  </tr> 
  <tr> 
   <td>imageType</td> 
   <td>int</td> 
   <td>1</td> 
   <td>导出类型 1 从file读取 2 是从数据库中读取 默认是文件 同样导入也是一样的</td> 
  </tr> 
  <tr> 
   <td>suffix</td> 
   <td>String</td> 
   <td>""</td> 
   <td>文字后缀,如% 90 变成90%</td> 
  </tr> 
  <tr> 
   <td>isWrap</td> 
   <td>boolean</td> 
   <td>true</td> 
   <td>是否换行 即支持\n</td> 
  </tr> 
  <tr> 
   <td>mergeRely</td> 
   <td>int[]</td> 
   <td>{}</td> 
   <td>合并单元格依赖关系,比如第二列合并是基于第一列 则{1}就可以了</td> 
  </tr> 
  <tr> 
   <td>mergeVertical</td> 
   <td>boolean</td> 
   <td>fasle</td> 
   <td>纵向合并内容相同的单元格</td> 
  </tr> 
 </tbody> 
</table>

三、在controller层编写导出excel接口

@RequestMapping(value = "/exportData", method=RequestMethod.GET)
    	@ResponseBody
    	public void exportData(HttpServletResponse response) {
                    //调用service代码获取用户数据
    		List<UserVO> list = userService.getAllUser();
    		Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), UserVO.class, list);
    		try {
    			String name = "用户数据.xls";
    			String fileName = new String(name.getBytes("UTF-8"), "ISO-8859-1");
    			response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
    	                response.setContentType("application/vnd.ms-excel;charset=UTF-8");
                            workbook.write(response.getOutputStream());
    			response.flushBuffer();
    			logger.info("用户数据导出成功");
    		} catch (IOException e) {
    			logger.error("用户数据导出失败，原因：", e);
    		} finally {
    			try {
    				response.getOutputStream().close();
    			} catch (IOException e) {
    				e.printStackTrace();
    			}
    		}
    	}

四、在前端页面给导出按钮添加点击事件，执行导出

五、前端导出的js代码参考如下：

//导出数据
    	function exportData(){
    		bootbox.confirm({  
    	            title: "请确认",
    	            message: "您确定要导出吗",  
    	            buttons: {  
    		        confirm: {  
    		            label: '确定',  
    		            className: 'btn blue'  
    		        },  
    		        cancel: {  
    		            label: '取消',  
    		            className: 'btn default'  
    		        }  
    		    },  
    		    callback: function(result) {  
    		        if(result) {  
    		            var url = "${ctx}/user/exportData?tmp=1"
    		            window.location.href=url;
    		        }
    		    }     
    		});
    	}