引入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>最新版本</version>
</dependency>
建议使用3.1.0+以上的版本
一. 将静态数据导入Excel
(1) 创建一个springboot项目
(2)创建实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
@ExcelProperty(value = "编号") //Excel表格名称
private Integer id;
@ExcelProperty(value ="姓名")
private String name;
@ExcelProperty(value ="性别")
private String sex;
@ExcelProperty(value ="地址")
private String address;
@ExcelIgnore //Excel中不显示该行数据
private String hobby;
}
(3)创建测试类
@Test
void contextLoads() {
//导出的Excel文件的位置
String fileName ="D:\\JAVAXM\\esayexcel\\src\\main\\eight.xlsx";
List<Student> data=new ArrayList<>();
data.add(new Student(1,"chicken","man","上海","篮球"));
data.add(new Student(2,"顶真","男","西藏","smoke"));
data.add(new Student(3,"菜徐琨","man","北京","rap"));
data.add(new Student(4,"蔡旭困","man","上海","sing"));
// 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
EasyExcel.write(fileName, Student.class).sheet("男明星").doWrite(data);
}
运行成功。
二.使用web中的写并且失败的时候返回json
//引入josn依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>2.0.25</version>
</dependency>
@Controller
public class ExcelTest {
@GetMapping("download")
public void downloadFailedUsingJson(HttpServletResponse response) throws IOException {
// 这里注意 有同学反应使用swagger 会导致各种问题,请直接用浏览器或者用postman
try {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
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");
List<Student> data=new ArrayList<>();
data.add(new Student(1,"chicken","man","上海","篮球"));
data.add(new Student(2,"顶真","男","西藏","smoke"));
data.add(new Student(3,"菜徐琨","man","北京","rap"));
data.add(new Student(4,"蔡旭困","man","上海","sing"));
// 这里需要设置不关闭流
EasyExcel.write(response.getOutputStream(), Student.class).autoCloseStream(Boolean.FALSE).sheet("男明星")
.doWrite(data);
} catch (Exception e) {
// 重置response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map<String, String> map = MapUtils.newHashMap();
map.put("status", "failure");
map.put("message", "下载文件失败" + e.getMessage());
response.getWriter().println(JSON.toJSONString(map));
}
}
}
访问 localhost:8080/downlad 会自动下载