引入依赖
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.3.0</version>
<exclusions>
<exclusion>
<artifactId>ognl</artifactId>
<groupId>ognl</groupId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.3.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.3.0</version>
</dependency>test.java
public static void main(String[] args) {
String templatePath = "."
+ File.separator + "SignDetection"
+ File.separator + "SignDetection.xlsx";
String outputPath = "."
+ File.separator + "SignDetection"
+ File.separator + "output";
List<Student> students = new LinkedList<>();
students.add(new Student("1","张","17"));
students.add(new Student("2","李","27"));
String targetPath=EasyPoiUtil.exportFromTemplate(students, templatePath, outputPath);
String fileName = "体征监控 " + LocalDate.now() + ".xls";
//生成文件流，返回前端
// EasyPoiUtil.fileStream(fileName, targetPath, response);
}student.java
@Data
public class Student {
private String id;
private String name;
private String age;
public Student(String id ,String name,String age){
this.id=id;
this.name=name;
this.age=age;
}
}easyPoiUtil.java
package com.lianmed.mchcgen.util.excel;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class EasyPoiUtil {
private static final Logger log = LoggerFactory.getLogger(EasyPoiUtil.class);
// 默认文件名前缀
private static final String DEFAULT_FILE_NAME = "OutputExcel";
// 默认文件名后缀
private static final String DEFAULT_FILE_SUFFIX = ".xls";
// 默认导出对象KEY值
private static final String DEFAULT_MAP_KEY = "result";
private static final String SHEET_NAME_KEY = "sheetName";
private static final DateTimeFormatter fileFormatter = DateTimeFormatter.ofPattern("yyyyMMddHHmmss");
private static final DateTimeFormatter logFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
/**
* 根据Excel模板导出统计表
*
* @param objects List对象，类型为 List<Object>
* @param templatePath 模板文件路径
* @param outputPath 输出文件路径
* @return 返回生成文件路径
*/
public static String exportFromTemplate(List<? extends Object> objects, String templatePath, String outputPath) {
return exportFromTemplate(objects, templatePath, outputPath, true);
}
/**
* 根据Excel模板导出统计表
*
* @param objects List对象，类型为 List<Object>
* @param templatePath 模板文件路径
* @param outputPath 输出文件路径
* @param sheetsIndex 需要导出的sheet索引数组
* @return 返回生成文件路径
*/
public static String exportFromTemplate(List<? extends Object> objects, String templatePath, String outputPath, Integer[] sheetsIndex) {
String fileName = DEFAULT_FILE_NAME + fileFormatter.format(LocalDateTime.now()) + DEFAULT_FILE_SUFFIX;
TemplateExportParams template = getTemplateExportParams(templatePath,sheetsIndex);
return export(template, formatListToMap(null, objects), outputPath, fileName);
}
/**
* 根据Excel模板导出统计表（横向遍历）
*
* @param objects List对象，类型为 List<Object>
* @param templatePath 模板文件路径
* @param outputPath 输出文件路径
* @param isColForEach 是否横向遍历
* @return 返回生成文件路径
*/
public static String exportFromTemplate(List<? extends Object> objects, String templatePath, String outputPath, Boolean isColForEach) {
return exportFromTemplate(null, objects, templatePath, outputPath, isColForEach);
}
/**
* 根据Excel模板导出统计表
*
* @param sheetNames 指定导出sheet的名称集合
* @param objects List对象，类型为 List<Object>
* @param templatePath 模板文件路径
* @param outputPath 输出文件路径
* @param isColForEach 是否横向遍历
* @return 返回生成文件路径
*/
public static String exportFromTemplate(List<String> sheetNames, List<? extends Object> objects, String templatePath, String outputPath, Boolean isColForEach) {
String fileName = DEFAULT_FILE_NAME + fileFormatter.format(LocalDateTime.now()) + DEFAULT_FILE_SUFFIX;
TemplateExportParams template = getTemplateExportParams(templatePath, isColForEach);
if (objects != null && objects.size() > 0 && objects.get(0) instanceof List) {
if (sheetNames != null && objects != null && sheetNames.size() != objects.size()) {
log.error("指定表名应该跟表数量相同");
return null;
}
Map<Integer, List<Map<String, Object>>> map = new HashMap<>();
for (int i = 0; i < objects.size(); i++) {
map.put(i, formatListToList(sheetNames.get(i), (List) objects.get(i)));
}
return exportExcelClone(template, map, outputPath, fileName);
}
return export(template,
formatListToMap((sheetNames != null && sheetNames.size() > 0) ? sheetNames.get(0) : null, objects),
outputPath, fileName);
}
private static String exportExcelClone(TemplateExportParams templateExportParams, Map objects, String outputPath, String outputFileName) {
// 输出文件
String filePath = outputPath + File.separator + outputFileName;
log.info("开始导出" + outputFileName + ", 开始时间为" + logFormatter.format(LocalDateTime.now()));
Workbook workbook = ExcelExportUtil.exportExcelClone(objects, templateExportParams);
return writeFile(filePath, outputPath, outputFileName, workbook);
}
/**
* 导出方法
*
* @param templateExportParams 模板对象
* @param objects List对象，类型为 List<Object>
* @param outputPath 输出文件路径
* @param outputFileName 输出文件名称
* @return
*/
private static String export(TemplateExportParams templateExportParams, Map objects, String outputPath, String outputFileName) {
// 输出文件
String filePath = outputPath + "/" + outputFileName;
log.info("开始导出" + outputFileName + ", 开始时间为" + logFormatter.format(LocalDateTime.now()));
Workbook workbook = ExcelExportUtil.exportExcel(templateExportParams, objects);
return writeFile(filePath, outputPath, outputFileName, workbook);
}
/**
* 格式化入参，转换成Easy Poi能识别的Map入参类型
*
* @param sheetName
* @param objects
* @return
*/
private static Map formatListToMap(String sheetName, List<? extends Object> objects) {
// 将List转成JSONArray
JSONArray arrays = new JSONArray();
for (Object object : objects) {
arrays.add(JSON.parseObject(JSONObject.toJSONString(object)));
}
// 将数据放入Map中，调用 Easy Poi 的导出方法
Map<String, Object> map = new HashMap<>();
map.put(DEFAULT_MAP_KEY, arrays);
if (StringUtils.isNotBlank(sheetName)) {
map.put(SHEET_NAME_KEY, sheetName);
}
return map;
}
/**
* 格式化入参，转换成Easy Poi能识别的入参类型
*
* @param sheetName
* @param objects
* @return
*/
private static List<Map<String, Object>> formatListToList(String sheetName, List<? extends Object> objects) {
// 将List转成JSONArray
List<Map<String, Object>> results = new ArrayList<>();
results.add(formatListToMap(StringUtils.isNotBlank(sheetName) ? sheetName : null, objects));
return results;
}
/**
* 获取模板导出参数对象
* @param templatePath
* @param isColForEach 是否横向遍历
* @return
*/
private static TemplateExportParams getTemplateExportParams(String templatePath, Boolean isColForEach) {
TemplateExportParams params = new TemplateExportParams(templatePath);
params.setColForEach(isColForEach);
return params;
}
/**
*
* @param filePath
* @param outputPath
* @param outputFileName
* @param workbook
* @return
*/
private static String writeFile(String filePath, String outputPath, String outputFileName, Workbook workbook) {
File outputFile = new File(outputPath);
if (!outputFile.exists()) {
outputFile.mkdirs();
}
FileOutputStream fos = null;
try {
// 通过文件流写对应的文件，并返回文件路径
fos = new FileOutputStream(filePath);
workbook.write(fos);
} catch (FileNotFoundException e) {
e.printStackTrace();
log.error("导出异常, 文件异常" + outputFileName + ", 结束时间为" + logFormatter.format(LocalDateTime.now()));
return null;
} catch (IOException e) {
log.error("导出异常, 写文件流异常" + outputFileName + ", 结束时间为" + logFormatter.format(LocalDateTime.now()));
return null;
} finally {
try {
fos.close();
} catch (IOException e) {
log.error("导出异常, 关闭文件输出流异常" + outputFileName + ", 结束时间为" + logFormatter.format(LocalDateTime.now()));
}
}
log.info("结束导出" + outputFileName + ", 结束时间为" + logFormatter.format(LocalDateTime.now()));
return filePath;
}
/**
* 格式化入参，转换成Easy Poi能识别的入参类型
*
* @param objects
* @return
*/
private static Map formatList(List<? extends Object> objects) {
// 将List转成JSONArray
JSONArray arrays = JSONArray.parseArray(JSONArray.toJSONString(objects));
// 将数据放入Map中，调用 Easy Poi 的导出方法
Map<String, Object> map = new HashMap<>();
map.put(DEFAULT_MAP_KEY, arrays);
return map;
}
/**
* 获取模板导出参数对象
* @param templatePath
* @return
*/
private static TemplateExportParams getTemplateExportParams(String templatePath) {
return new TemplateExportParams(templatePath);
}
/**
* 获取模板导出参数对象
* @param templatePath
* @param sheetsIndex 需要导出sheet的索引，参数为数组类型 Integer[]
* @return
*/
private static TemplateExportParams getTemplateExportParams(String templatePath, Integer[] sheetsIndex) {
TemplateExportParams params = new TemplateExportParams(templatePath);
params.setSheetNum(sheetsIndex);
return params;
}
/**
*
* @param name 文件名
* @param url 生成的文件路径
* @param response 响应体
*/
public static void fileStream(String name, String url,HttpServletResponse response) {
String fileName = name;
FileInputStream ips = null;
ServletOutputStream out = null;
try {
File file = new File(url);
ips = new FileInputStream(file);
response.setContentType("multipart/form-data");
response.setCharacterEncoding("UTF-8");
fileName = URLEncoder.encode(fileName, "utf-8");
response.addHeader("content-disposition", "attachment; filename=" + fileName + "");
out = response.getOutputStream();
int len = 0;
byte[] buffer = new byte[1024 * 10];
while ((len = ips.read(buffer)) != -1){
out.write(buffer,0,len);
}
out.flush();
ips.close();
out.close();
}catch (Exception e){
log.info(e.getMessage());
}
}
}模板
版权声明：本文为weixin_41427294原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。