maven添加
<!-- easypoi的支持 -->
<!--easy poi http://easypoi.mydoc.io/#text_217736 https://gitee.com/lemur/easypoi-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.1.0</version>
</dependency>
<!-- excel工具 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>
编写工具类
ExcelExportUtil
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateUtil;
import com.cbcs.common.exception.ValidateException;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.util.ObjectUtils;
import org.springframework.util.ReflectionUtils;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.*;
/**
* <p> 导出工具类 </p>
**/
@Slf4j
@Component
public class ExcelExportUtil {
public static String port;
@Value("${server.port}")
public void setPort(String port) {
ExcelExportUtil.port = port;
}
/**
* 导出到输出流
*
* @param fileName 导出文件名称
* @param sheetName 导出的sheetName
* @param templateFileName 指定服务器下面的模板名称
* @param response 响应对象
* @param exportClass 需要导出的类型
* @param recordList 需要导出的列表
*/
public static <T> void exportExcel(String fileName, String sheetName, String templateFileName, HttpServletResponse response, Class<T> exportClass, Collection<T> recordList) {
if (CollectionUtil.isEmpty(recordList)) {
throw new ValidateException("导出数据为空!");
}
// 导出模板地址信息
TemplateExportParams params = new TemplateExportParams(String.format("http://localhost:%s/%s", port, templateFileName));
// 设置 sheetName
if (StringUtils.isNotBlank(sheetName)) {
params.setSheetName(sheetName);
}
// 导出模板数据map
Map<String, Object> map = new HashMap<>();
map.put("date", DateUtil.format(new Date(), DatePattern.CHINESE_DATE_PATTERN));
// 导出列表数据
List<Map<String, String>> listMap = new ArrayList<>(recordList.size());
// 需要导出的字段
Map<String, Field> exportField = getExportField(exportClass);
Map<String, String> mapRecord;
for (T record: recordList) {
mapRecord = new HashMap<>();
for (Map.Entry<String, Field> fieldEntry: exportField.entrySet()) {
String key = fieldEntry.getKey();
Field field = fieldEntry.getValue();
field.setAccessible(true);
mapRecord.put(key, Optional.ofNullable(ReflectionUtils.getField(field, record)).map(String::valueOf).orElse(""));
}
listMap.add(mapRecord);
}
map.put("maplist", listMap);
// 进行模板封装导出对象
try {
Workbook workbook = cn.afterturn.easypoi.excel.ExcelExportUtil.exportExcel(params, map);
ServletOutputStream outputStream = response.getOutputStream();
response.reset();
//设置响应头,
fileName = URLEncoder.encode(fileName, "UTF-8") + templateFileName.substring(templateFileName.lastIndexOf("."));
response.setHeader("Content-disposition", "attachment; filename=" + fileName);
response.setContentType("application/msexcel");
workbook.write(outputStream);
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
throw new ValidateException("导出模板文件不存在!");
}
}
/**
* 获取需要导出的字段
*
* @param exportClass 需要导出的类型
* @return 导出的字段
*/
private static <T> Map<String, Field> getExportField(Class<T> exportClass) {
Field[] declaredFields = exportClass.getDeclaredFields();
Map<String, Field> fieldMap = null;
if (!ObjectUtils.isEmpty(declaredFields)) {
fieldMap = new HashMap<>();
for(Field field : declaredFields) {
fieldMap.put(field.getName(), field);
}
}
return fieldMap;
}
}