最近在做excel数据导出时,发现了一款挺好用的excel处理开源框架:easy-excel,阿里巴巴出品,github上已有10.7K Star,整个使用下来比较突出的有两点:
-
容易上手,无论是注解还是api比较好用
-
相比Apache poi,比较节约内存,避免内存溢出等问题。
下面以excel的数据导出的demo为例,谈谈该框架的应用实践
-
使用@ExcelProperty 可以指定excel列表的标题和顺序,index用来指定写入excel标题顺序,通过该注解,实现对象与excel标题的映射。
package com.model;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
public class User extends BaseRowModel {
/**
* 用户id
*/
@ExcelProperty(value = "用户id", index = 0)
private String userId;
/**
* 年龄
*/
@ExcelProperty(value = "年龄", index = 1)
private String age;
/**
* 薪水
*/
@ExcelProperty(value = "薪水", index = 2)
private String salary;
/**
* 职业
*/
@ExcelProperty(value = "职业", index = 3)
private String profession;
public String getUserId() {
return userId;
}
public void setUserId(String userId) {
this.userId = userId;
}
public String getAge() {
return age;
}
public void setAge(String age) {
this.age = age;
}
public String getSalary() {
return salary;
}
public void setSalary(String salary) {
this.salary = salary;
}
public String getProfession() {
return profession;
}
public void setProfession(String profession) {
this.profession = profession;
}
}
-
excel下载首先要设置好HttpServletResponse参数,指定文件名时注意编码,以免出现中文乱码。
-
服务端向客户端游览器发送文件时,如果是浏览器支持的文件类型,一般会默认使用浏览器打开,比如txt、jpg等,会直接在浏览器中显示,如果需要提示用户保存,就要利用Content-Disposition进行一下处理,关键在于一定要加上attachment
response.setHeader("Content-Disposition", "attachment;filename=" + fileName+".xlsx");
4.完整代码如下:
/**
* Alipay.com Inc. Copyright (c) 2004-2019 All Rights Reserved.
*/
package com.controller;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.google.common.collect.Lists;
import com.model.User;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
/**
* excel下载
*/
@RestController
@RequestMapping("/user")
public class ExcelDownLoadController {
private Logger logger = LoggerFactory.getLogger(ExcelDownLoadController.class);
@RequestMapping(value = "/download", method = RequestMethod.GET)
public void downLoadExcel(HttpServletResponse response) {
try {
this.setUpResponseAttributes(response);
ByteArrayOutputStream stream = this.writeToExcel();
this.writeToResponse(stream,response);
} catch (Exception e) {
logger.error("downLoadExcel error", e);
throw new RuntimeException("下载excel异常");
}
}
/**
* 基于easyExcel框架,把数据写入到excel
*
* @return
*/
private ByteArrayOutputStream writeToExcel() {
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
ExcelWriter excelWriter = new ExcelWriter(byteArrayOutputStream, ExcelTypeEnum.XLSX);
Sheet sheet = new Sheet(1, 0, User.class);
excelWriter.write(Lists.newArrayList(buildUserData()), sheet);
excelWriter.finish();
return byteArrayOutputStream;
}
/**
* 针对excel下载,设置相关的HttpServletResponse属性
*
* @param response
*/
private void setUpResponseAttributes(HttpServletResponse response) throws UnsupportedEncodingException {
response.setHeader("Pragma", "No-cache");
response.setHeader("Cache-Control", "No-cache");
response.setDateHeader("Expires", 0);
response.setContentType("application/vnd.ms-excel");
//解决中文乱码问题
String fileName = new String("用户名单".getBytes("gbk"),"iso8859-1");
// 设定输出文件头
response.setHeader("Content-Disposition", "attachment;filename=" + fileName+".xlsx");
}
/**
* 将byteStream写进response输出流中
*
* @param byteStream excel字节流
* @param response 返回结果
* @throws IOException
*/
private void writeToResponse(ByteArrayOutputStream byteStream, HttpServletResponse response) throws IOException {
try {
ServletOutputStream responseStream = response.getOutputStream();
byteStream.writeTo(responseStream);
responseStream.flush();
} catch (Exception e) {
logger.error("writeToResponse error", e);
throw e;
} finally {
byteStream.close();
}
}
/**
* 构建一条用户数据,用来测试
*
* @return
*/
private User buildUserData() {
User user = new User();
user.setUserId("10241000");
user.setAge("20");
user.setProfession("学生");
user.setSalary("2000");
return user;
}
}
5.excel下载效果
启动服务后,http://127.0.0.1:8080/user/download 下载excel,效果如下图所示
图片
github开源地址: https://github.com/alibaba/easyexcel
更多内容欢迎关注个人微信公众号,一起成长!