1.pom文件
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.wu</groupId>
<artifactId>springboot-easyexcel</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>springboot-easyexcel</name>
<description>springboot-easyexcel</description>
<properties>
<java.version>1.8</java.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<spring-boot.version>2.3.12.RELEASE</spring-boot.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.11</version>
<exclusions>
<exclusion>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
</exclusion>
</exclusions>
</dependency>
<!-- fastjson -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.58</version>
</dependency>
</dependencies>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-dependencies</artifactId>
<version>${spring-boot.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.1</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
<encoding>UTF-8</encoding>
</configuration>
</plugin>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<version>${spring-boot.version}</version>
<configuration>
<mainClass>com.wu.SpringbootEasyexcelApplication</mainClass>
<skip>true</skip>
</configuration>
<executions>
<execution>
<id>repackage</id>
<goals>
<goal>repackage</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
</build>
</project>
2.工具类
package com.wu.util;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.codec.Charsets;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
public class EasyExcelUtil {
/**
*
* @param response 响应
* @param fileName 文件名称
* @param sheetName sheet名称
* @param headNameList 头部名称
* @param fieldList 字段名称
* @param dataList 数据
* @param <T>
* @throws IOException
*/
public static <T> void export(HttpServletResponse response, String fileName, String sheetName, List<String> headNameList,List<String> fieldList, List<T> dataList) throws IOException {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding(Charsets.UTF_8.name());
fileName = URLEncoder.encode(fileName, Charsets.UTF_8.name());
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
//EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(dataList);
//数据
List<List<Object>> resultDataList=EasyExcelUtil.dataList(fieldList,JSON.parseArray(JSON.toJSONString(dataList)));
EasyExcelFactory.write(response.getOutputStream()).sheet(sheetName).head(EasyExcelUtil.headList(headNameList)).doWrite(resultDataList);
}
/**
* 封装表格标题
*
* @param headNameList 标题数据
* @return 表格标题集合¬
*/
public static List<List<String>> headList(List<String> headNameList) {
List<List<String>> headList = new ArrayList<>();
for (String headName:headNameList) {
List<String> list=new ArrayList<>();
list.add(headName);
headList.add(list);
}
return headList;
}
/**
* 封装导出字段的数据结构
*
* @param fieldList 字段
* @param dataObj 数据集合
* @return 字段值集合
*/
public static List<List<Object>> dataList(List<String> fieldList, JSONArray dataObj) {
List<List<Object>> dataList = new ArrayList<>();
for (Object dataObjValue : dataObj) {
List<Object> data = new ArrayList<>();
for (String field:fieldList) {
data.add(((JSONObject) JSON.toJSON(dataObjValue)).get(field));
}
dataList.add(data);
}
return dataList;
}
}
3.实体类
package com.wu.dto;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class StuDto {
private String name;
private Integer age;
}
4.controller测试
package com.wu.controller;
import com.wu.dto.StuDto;
import com.wu.util.EasyExcelUtil;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
@RestController
public class TestController {
@GetMapping("/export")
public void export(HttpServletResponse response) throws IOException {
String fileName="文件";
String sheetName="sheet1";
//头部
List<String> headNameList=new ArrayList<>();
headNameList.add("名字");
headNameList.add("年龄");
//字段
List<String> fieldList=new ArrayList<>();
fieldList.add("name");
fieldList.add("age");
//数据
List<StuDto> dataList=new ArrayList<>();
StuDto stuDto1=new StuDto("张三",4);
StuDto stuDto2=new StuDto("李四",28);
dataList.add(stuDto1);
dataList.add(stuDto2);
EasyExcelUtil.export(response,fileName,sheetName,headNameList,fieldList,dataList);
}
}