SpringBoot +EasyExcel 实现excel 导出功能一般流程解析

无情 阅读:210 2021-03-31 13:45:36 评论:0

第一步:SpringBoot 项目添加EasyExcel  相关依赖:

		<!--集成EasyExcel --> 
		<dependency> 
			<groupId>com.alibaba</groupId> 
			<artifactId>easyexcel</artifactId> 
			<version>1.1.2-beat1</version> 
		</dependency>

第二步:编写EasyExcel 导出excel 的实体对象:

注意:EasyExcel 导出实体对象必须继承com.alibaba.excel.metadata.BaseRowModel 类

import java.io.Serializable; 
import java.util.Date; 
 
import com.alibaba.excel.annotation.ExcelProperty; 
import com.alibaba.excel.metadata.BaseRowModel; 
 
@SuppressWarnings("serial") 
public class XXXExcel extends BaseRowModel implements Serializable { 
	@ExcelProperty(value = "用户名称", index = 0) 
    private String username; 
	@ExcelProperty(value = "IP地址", index = 1) 
    private String ipAddress; 
	@ExcelProperty(value = "日期", index = 2, format="yyyy-MM-dd") 
    private Date createTime; 
	@ExcelProperty(value = "日志描述", index = 3) 
    private String logDesc; 
	public String getUsername() { 
		return username; 
	} 
	public void setUsername(String username) { 
		this.username = username; 
	} 
	public String getIpAddress() { 
		return ipAddress; 
	} 
	public void setIpAddress(String ipAddress) { 
		this.ipAddress = ipAddress; 
	} 
	public Date getCreateTime() { 
		return createTime; 
	} 
	public void setCreateTime(Date createTime) { 
		this.createTime = createTime; 
	} 
	public String getLogDesc() { 
		return logDesc; 
	} 
	public void setLogDesc(String logDesc) { 
		this.logDesc = logDesc; 
	} 
} 

第三:SpringMVC 中的Controller 层调用orm框架(jpa\hibernate\mybatis)完成数据查询转换为对应的实体对象,我们再将查询的实体对象转换为EasyExcel 导出的实体对象。

示列代码:

@ApiOperation(httpMethod = "GET", value = "后台日志文件导出功能") 
	@RequestMapping(value="/export", method={RequestMethod.GET}) 
	@ApiImplicitParams({ 
		@ApiImplicitParam(name = "username", value = "用户名称", required = false, dataType = "String", paramType = "query"), 
		@ApiImplicitParam(name = "ipAddress", value = "IP地址", required = false, dataType = "String", paramType = "query"), 
		@ApiImplicitParam(name = "page", value = "页码", required = false, dataType = "Integer", paramType = "query"), 
		@ApiImplicitParam(name = "limit", value = "页面大小", required = false, dataType = "Integer", paramType = "query") }) 
	public void fileDownload(HttpServletRequest request, HttpServletResponse response) throws IOException{ 
		response.setContentType("application/vnd.ms-excel;charset=utf-8"); 
		response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode("审计日志.xlsx", "UTF8")); 
		response.setHeader("FileName", URLEncoder.encode("审计日志.xlsx", "UTF8")); 
		// 解析请求参数 
		String username = request.getParameter("username"); 
		String ipAddress = request.getParameter("ipAddress"); 
		String page = request.getParameter("page"); 
		String limit = request.getParameter("limit"); 
		Map<String, Object> params = new HashMap<String, Object>(); 
		if(!StringUtils.isEmpty(username)){ 
			params.put("username", username); 
		} 
		if(!StringUtils.isEmpty(ipAddress)){ 
			params.put("ipAddress", ipAddress); 
		} 
		if (!StringUtils.isEmpty(page)) { 
			params.put("page", Integer.valueOf(page)); 
		} 
		if (!StringUtils.isEmpty(limit)) { 
			params.put("limit", Integer.valueOf(limit)); 
		} 
		// MyBatis 数据查询 
		PageParam rb = super.initPageBounds(params); 
		PageInfo<XXXRecord> list = service.selectPageList(params, rb); 
		List<XXXRecord> lists = list.getList();  
		// 查询实体转换为EasyExcel 导出实体 
		List<XXXRecordExcel> rows = new ArrayList<XXXRecordExcel>(); 
		if(!CollectionUtils.isEmpty(lists)){ 
			lists.stream().forEach(item->{ 
				XXXRecordExcel excel = new XXXRecordExcel(); 
				try { 
					DateConverter converter = new DateConverter(null); 
					converter.setPattern("yyyy-MM-dd"); 
					ConvertUtils.register(converter, java.util.Date.class); 
					BeanUtils.copyProperties(excel, item); 
				} catch (Exception e) { 
					// TODO Auto-generated catch block 
					log.error(e.getMessage()); 
				} 
				rows.add(excel); 
			}); 
		} 
		// 输出excel 文件 
		downloadExcel(response, XXXRecordExcel.class, rows); 
 
	    
	} 
	 
	private void downloadExcel(HttpServletResponse response, Class<? extends BaseRowModel> clazz, 
			List<? extends BaseRowModel> data) throws IOException { 
		OutputStream out = response.getOutputStream(); 
		ExcelWriter writer = EasyExcelFactory.getWriter(out); 
		Sheet sheet = new Sheet(1, 0, clazz); 
		writer.write(data, sheet); 
		writer.finish(); 
		out.flush(); 
	}

 

声明

1.本站遵循行业规范,任何转载的稿件都会明确标注作者和来源;2.本站的原创文章,请转载时务必注明文章作者和来源,不尊重原创的行为我们将追究责任;3.作者投稿可能会经我们编辑修改或补充。

发表评论
搜索
KIKK导航

KIKK导航

关注我们