引入依赖
cn.afterturn easypoi-base4.3.0 ognl ognl cn.afterturn easypoi-web4.3.0 cn.afterturn easypoi-annotation4.3.0
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 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");
public static String exportFromTemplate(List objects, String templatePath, String outputPath) {
return exportFromTemplate(objects, templatePath, outputPath, true);
}
public static String exportFromTemplate(List 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);
}
public static String exportFromTemplate(List objects, String templatePath, String outputPath, Boolean isColForEach) {
return exportFromTemplate(null, objects, templatePath, outputPath, isColForEach);
}
public static String exportFromTemplate(List sheetNames, List 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>> 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);
}
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);
}
private static Map formatListToMap(String sheetName, List objects) {
// 将List转成JSONArray
JSONArray arrays = new JSONArray();
for (Object object : objects) {
arrays.add(JSON.parseObject(JSONObject.toJSONString(object)));
}
// 将数据放入Map中，调用 Easy Poi 的导出方法
Map map = new HashMap<>();
map.put(DEFAULT_MAP_KEY, arrays);
if (StringUtils.isNotBlank(sheetName)) {
map.put(SHEET_NAME_KEY, sheetName);
}
return map;
}
private static List> formatListToList(String sheetName, List objects) {
// 将List转成JSONArray
List> results = new ArrayList<>();
results.add(formatListToMap(StringUtils.isNotBlank(sheetName) ? sheetName : null, objects));
return results;
}
private static TemplateExportParams getTemplateExportParams(String templatePath, Boolean isColForEach) {
TemplateExportParams params = new TemplateExportParams(templatePath);
params.setColForEach(isColForEach);
return params;
}
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;
}
private static Map formatList(List objects) {
// 将List转成JSONArray
JSONArray arrays = JSONArray.parseArray(JSONArray.toJSONString(objects));
// 将数据放入Map中，调用 Easy Poi 的导出方法
Map map = new HashMap<>();
map.put(DEFAULT_MAP_KEY, arrays);
return map;
}
private static TemplateExportParams getTemplateExportParams(String templatePath) {
return new TemplateExportParams(templatePath);
}
private static TemplateExportParams getTemplateExportParams(String templatePath, Integer[] sheetsIndex) {
TemplateExportParams params = new TemplateExportParams(templatePath);
params.setSheetNum(sheetsIndex);
return params;
}
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());
}
}
}
模板
