1.概述
环境说明:
java1.8.0_131
springboot2.2.0.RELEASE
easyexcel2.2.6
fastjson1.2.70
2.maven依赖
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.0.RELEASE</version>
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.70</version>
</dependency>
</dependencies>
3.需要导出实体类
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import java.util.Date;
/**
* @author:Ding
* @date:2021/11/29 14:27
* @Desperation 导出对象实体类
*/
@ColumnWidth(20)//可以设置列宽
public class Person {
//人员id
@ExcelProperty(value = "人员id", index = 0)
private Long id;
//人员姓名
@ExcelProperty(value = "姓名", index = 1)
private String name;
//创建时间
@DateTimeFormat("yyyy年MM月dd日")
@ExcelProperty(value = "创建时间", index = 2)
private Date createTime;
//是否删除,导出时忽略
@ExcelIgnore
private Boolean isDel;
public Person(Long id, String name, Date createTime, Boolean isDel) {
this.id = id;
this.name = name;
this.createTime = createTime;
this.isDel = isDel;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public Boolean getDel() {
return isDel;
}
public void setDel(Boolean del) {
isDel = del;
}
}
4.导出excel方法
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.fastjson.JSON;
import org.springframework.stereotype.Component;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @author:Ding
* @date:2021/11/29 14:19
* @Desperation
*/
@Component
public class ExcelUtils {
/**
* 导出excel
*
* @param objects 数据List
* @param response 返回response对象
* @param fileName 返回文件的名称
* @param c 数据List的对象类
*/
public <T> void excelExport(List<?> objects, HttpServletResponse response, String fileName, Class<T> c) throws IOException {
if (objects.size() == 0)
return;
ServletOutputStream outputStream = null;
try {
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1") + ".xlsx");
outputStream = response.getOutputStream();
ExcelWriterBuilder write = EasyExcel.write(response.getOutputStream(), c).excelType(ExcelTypeEnum.XLS);
write.sheet(fileName).doWrite(objects);
if (outputStream != null) {
outputStream.close();
}
} catch (Exception e) {
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map<String, Object> map = new HashMap<>();
map.put("code", 500);
map.put("msg", "下载文件失败" + e.getMessage());
map.put("data", null);
response.getWriter().println(JSON.toJSONString(map));
}
}
}
5.controller
@RestController
public class ExcelController {
@Resource
ExcelUtils excelUtils;
@GetMapping("/export")
public void export(HttpServletResponse response) {
try {
List<Person> people = new ArrayList<>();
Person person1 = new Person(1L, "小白", new Date(), false);
Person person2 = new Person(2L, "小黑", new Date(), true);
people.add(person1);
people.add(person2);
excelUtils.excelExport(people, response, "人员", Person.class);
} catch (IOException e) {
e.printStackTrace();
}
}
}
6.测试
浏览器访问http://localhost:8080/export