pom文件引入以下依赖
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.1.0</version>
</dependency>
或者引入下面的依赖
<!-- easy-poi -->
<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的用法示例
直接将List<Map<String, Object>>数据导出为excel示例(无需模板)
/**
* 直接导出(无需模板) 注:此方式存在一些不足之处，在对性能、excel要求比较严格时不推荐使用
*
* @throws IOException
*/
@Test
public void directExportExcel() throws IOException {
// Map作为每一行的数据容器，List作为行的容器
List<Map<String, Object>> rowDataList = new ArrayList<>();
// 每个ExcelExportEntity存放Map行数据的key
List<ExcelExportEntity> keyList = new ArrayList<>();
Map<String, Object> aRowMap;
final int COMMON_KEY_INDEX = 10;
for (int i = 0; i < 5; i++) {
// 一个Map对应一行数据（如果需要导出多行数据，那么需要多个Map）
aRowMap = new HashMap<>(16);
for (int j = 0; j < COMMON_KEY_INDEX; j++) {
String key = j + "";
aRowMap.put(key, "坐标(" + i + "," + j + ")");
}
rowDataList.add(aRowMap);
// 同一列对应的cell,在从Map里面取值时，会共用同一个key
// 因此ExcelExportEntity的个数要保持和列数做多的行 的map.size()大小一致
if (i == 0) {
ExcelExportEntity excelExportEntity;
for (int j = 0; j < COMMON_KEY_INDEX; j++) {
excelExportEntity = new ExcelExportEntity();
excelExportEntity.setKey(j + "");
// 设置cell宽
excelExportEntity.setWidth(15D);
// 设置cell是否自动换行
excelExportEntity.setWrap(true);
keyList.add(excelExportEntity);
}
}
}
// excel总体设置
ExportParams exportParams = new ExportParams();
// 不需要标题
exportParams.setCreateHeadRows(false);
// 指定sheet名字
exportParams.setSheetName("直接导出数据测试");
// 生成workbook 并导出
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, keyList, rowDataList);
File savefile = new File("E:\\temp\\easypoi");
if (!savefile.exists()) {
boolean result = savefile.mkdirs();
System.out.println("目录不存在，创建" + result);
}
FileOutputStream fos = new FileOutputStream("E:\\temp\\easypoi\\坐标.xls");
workbook.write(fos);
fos.close();
}
通过注解，直接将Object(集合)数据导出为excel示例(无需模板)
/**
* 对象---直接导出(无需模板) 注:如果模型 的父类的属性也有@Excel注解，那么导出excel时，会连该模型的父类的属性也一会儿导出
*
* @throws IOException
*/
@Test
public void directExportExcelByObject() throws IOException {
List<Student> list = new ArrayList<>(16);
Student student;
Random random = new Random();
for (int i = 0; i < 10; i++) {
student = new Student(i + "", "name" + i, random.nextInt(2), random.nextInt(100), new Date(),
"className" + i);
student.setSchoolName("学校名称" + i);
student.setSchoolAddress("学校地址" + i);
list.add(student);
}
ExportParams exportParams = new ExportParams();
exportParams.setSheetName("我是sheet名字");
// 生成workbook 并导出
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, Student.class, list);
File savefile = new File("E:/temp/easypoi");
if (!savefile.exists()) {
boolean result = savefile.mkdirs();
System.out.println("目录不存在，创建" + result);
}
FileOutputStream fos = new FileOutputStream("E:/temp/easypoi/学生.xls");
workbook.write(fos);
fos.close();
}
使用模板将Map<String, Object>数据导出为excel示例(需要模板)
// 模板导出---Map组装数据
// 注:.xls的模板可以导出.xls文件，也可以导出xlsx的文件;同样的, .xlsx的模板可以导出.xls文件，也可以导出xlsx的文件;
@Test
public void templateExportExcelByMap() throws IOException {
// 加载模板
TemplateExportParams params = new TemplateExportParams("templates/templateMap.xls");
Map<String, Object> map = new HashMap<>(16);
map.put("title", "全亚洲,最帅气人员名单");
map.put("date", "2018-12-05");
map.put("interviewer", "JustryDeng");
List<Map<String, Object>> list = new ArrayList<>(16);
Map<String, Object> tempMap;
for (int i = 0; i < 5; i++) {
tempMap = new HashMap<>();
tempMap.put("name", "邓沙利文");
tempMap.put("gender", new Random().nextInt(2) == 0 ? "男" : "女");
tempMap.put("age", new Random().nextInt(90) + 11);
tempMap.put("hobby", "活的，女的！！！");
tempMap.put("handsomeValue", "100分(满分100分)");
tempMap.put("motto", "之所以只帅到了全亚洲，是因为其他地方审美不同！");
list.add(tempMap);
}
map.put("dataList", list);
// 生成workbook 并导出
Workbook workbook = ExcelExportUtil.exportExcel(params, map);
File savefile = new File("E:/temp/easypoi");
if (!savefile.exists()) {
boolean result = savefile.mkdirs();
System.out.println("目录不存在,进行创建,创建" + (result ? "成功!" : "失败！"));
}
FileOutputStream fos = new FileOutputStream("E:/temp/easypoi/采访结果.xls");
workbook.write(fos);
fos.close();
}
使用模板将Object数据导出为excel示例(需要模板)
// 模板导出---对象组装数据
// 注:实际上仍然是"模板导出---Map组装数据",不过这里借助了工具类，将对象先转换为了Map<String, Object>
// 注:.xls的模板可以导出.xls文件，也可以导出xlsx的文件;同样的, .xlsx的模板可以导出.xls文件，也可以导出xlsx的文件;
@Test
public void templateExportExcelByObject() throws IOException, IllegalAccessException {
// 加载模板
TemplateExportParams params = new TemplateExportParams("templates/templateObject.xlsx");
// 组装数据
InterviewResult interviewResult = new InterviewResult();
interviewResult.setTitle("全亚洲最帅人员名单");
interviewResult.setInterviewer("邓沙利文");
interviewResult.setDate("2018-12-05");
List<HandsomeBoy> list = new ArrayList<>(8);
interviewResult.setList(list);
HandsomeBoy handsomeBoy;
for (int i = 0; i < 5; i++) {
handsomeBoy = new HandsomeBoy();
handsomeBoy.setAge(20 + i);
handsomeBoy.setGender(i % 2 == 0 ? "女" : "男");
handsomeBoy.setHandsomeValue(95 + i + "(满分100分)");
handsomeBoy.setHobby("女。。。。");
handsomeBoy.setMotto("我是一只小小小小鸟~");
handsomeBoy.setName("JustryDeng");
list.add(handsomeBoy);
}
// 生成workbook 并导出
Workbook workbook = ExcelExportUtil.exportExcel(params, objectToMap(interviewResult));
File savefile = new File("E:/temp/easypoi");
if (!savefile.exists()) {
boolean result = savefile.mkdirs();
System.out.println("目录不存在,进行创建,创建" + (result ? "成功!" : "失败！"));
}
FileOutputStream fos = new FileOutputStream("E:/temp/easypoi/采访结果.xlsx");
workbook.write(fos);
fos.close();
}
/**
* 对象转换为Map<String, Object>的工具类
*
* @param obj 要转换的对象
* @return
* @throws IllegalAccessException
*/
private static Map<String, Object> objectToMap(Object obj) throws IllegalAccessException {
Map<String, Object> map = new HashMap<>(16);
Class<?> clazz = obj.getClass();
for (Field field : clazz.getDeclaredFields()) {
field.setAccessible(true);
String fieldName = field.getName();
Object value = field.get(obj);
map.put(fieldName, value);
}
return map;
}
模板标签语法
在这里插入图片描述
ExcelUtils导入导出excel
package cn.com.javakf.easypoi.utils;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
public class ExcelUtils {
/**
* excel 导出
*
* @param list 数据
* @param title 标题
* @param sheetName sheet名称
* @param pojoClass pojo类型
* @param fileName 文件名称
* @param isCreateHeader 是否创建表头
* @param response
*/
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,
boolean isCreateHeader, HttpServletResponse response) throws IOException {
ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
exportParams.setCreateHeadRows(isCreateHeader);
defaultExport(list, pojoClass, fileName, response, exportParams);
}
/**
* excel 导出
*
* @param list 数据
* @param title 标题
* @param sheetName sheet名称
* @param pojoClass pojo类型
* @param fileName 文件名称
* @param response
*/
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,
HttpServletResponse response) throws IOException {
defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF));
}
/**
* excel 导出
*
* @param list 数据
* @param pojoClass pojo类型
* @param fileName 文件名称
* @param response
* @param exportParams 导出参数
*/
public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, ExportParams exportParams,
HttpServletResponse response) throws IOException {
defaultExport(list, pojoClass, fileName, response, exportParams);
}
/**
* excel 导出
*
* @param list 数据
* @param fileName 文件名称
* @param response
*/
public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response)
throws IOException {
defaultExport(list, fileName, response);
}
/**
* 默认的 excel 导出
*
* @param list 数据
* @param pojoClass pojo类型
* @param fileName 文件名称
* @param response
* @param exportParams 导出参数
*/
private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response,
ExportParams exportParams) throws IOException {
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
downLoadExcel(fileName, response, workbook);
}
/**
* 默认的 excel 导出
*
* @param list 数据
* @param fileName 文件名称
* @param response
*/
private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response)
throws IOException {
Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
downLoadExcel(fileName, response, workbook);
}
/**
* 下载
*
* @param fileName 文件名称
* @param response
* @param workbook excel数据
*/
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook)
throws IOException {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename="
+ URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLSX.getValue(), "UTF-8"));
workbook.write(response.getOutputStream());
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
/**
* excel 导入
*
* @param filePath excel文件路径
* @param titleRows 标题行
* @param headerRows 表头行
* @param pojoClass pojo类型
* @param <T>
* @return
*/
public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass)
throws IOException {
if (StringUtils.isBlank(filePath)) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
params.setNeedSave(true);
params.setSaveUrl("/excel/");
try {
return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
} catch (NoSuchElementException e) {
throw new IOException("模板不能为空");
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
/**
* excel 导入
*
* @param file excel文件
* @param pojoClass pojo类型
* @param <T>
* @return
*/
public static <T> List<T> importExcel(MultipartFile file, Class<T> pojoClass) throws IOException {
return importExcel(file, 1, 1, pojoClass);
}
/**
* excel 导入
*
* @param file excel文件
* @param titleRows 标题行
* @param headerRows 表头行
* @param pojoClass pojo类型
* @param <T>
* @return
*/
public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass)
throws IOException {
return importExcel(file, titleRows, headerRows, false, pojoClass);
}
/**
* excel 导入
*
* @param file 上传的文件
* @param titleRows 标题行
* @param headerRows 表头行
* @param needVerfiy 是否检验excel内容
* @param pojoClass pojo类型
* @param <T>
* @return
*/
public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, boolean needVerfiy,
Class<T> pojoClass) throws IOException {
if (file == null) {
return null;
}
try {
return importExcel(file.getInputStream(), titleRows, headerRows, needVerfiy, pojoClass);
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
/**
* excel 导入
*
* @param inputStream 文件输入流
* @param titleRows 标题行
* @param headerRows 表头行
* @param needVerfiy 是否检验excel内容
* @param pojoClass pojo类型
* @param <T>
* @return
*/
public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows,
boolean needVerify, Class<T> pojoClass) throws IOException {
if (inputStream == null) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
params.setSaveUrl("/excel/");
params.setNeedSave(true);
params.setNeedVerify(needVerify);
try {
return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
} catch (NoSuchElementException e) {
throw new IOException("excel文件不能为空");
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
/**
* Excel 类型枚举
*/
enum ExcelTypeEnum {
XLS("xls"), XLSX("xlsx");
private String value;
ExcelTypeEnum(String value) {
this.value = value;
}
public String getValue() {
return value;
}
public void setValue(String value) {
this.value = value;
}
}
}
package cn.com.javakf.easypoi.controller;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import cn.com.javakf.easypoi.model.Person;
import cn.com.javakf.easypoi.utils.ExcelUtils;
@RestController
@RequestMapping("excel")
public class ExcelController {
/**
* 导出
*
* @param response
* @throws IOException
*/
@RequestMapping(value = "/export", method = RequestMethod.GET)
public void exportExcel(HttpServletResponse response) throws IOException {
List<Person> personList = new ArrayList<>();
for (int i = 0; i < 5; i++) {
Person person = new Person();
person.setName("张三" + i);
person.setUsername("张三" + i);
person.setPhoneNumber("18888888888");
person.setImageUrl("/static/person.jpg");
personList.add(person);
}
ExcelUtils.exportExcel(personList, "员工信息", "员工信息sheet", Person.class, "员工信息表", response);
}
/**
* 导入
*
* @param file
* @return
* @throws IOException
*/
@RequestMapping(value = "/import", method = RequestMethod.POST)
public Object importExcel(@RequestParam("file") MultipartFile file) throws IOException {
long start = System.currentTimeMillis();
List<Person> personList = ExcelUtils.importExcel(file, Person.class);
System.out.println("导入excel所花时间：" + (System.currentTimeMillis() - start) + "'ms");
return personList;
}
}
package cn.com.javakf.easypoi.model;
import java.io.Serializable;
import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;
import lombok.EqualsAndHashCode;
@Data
@EqualsAndHashCode(callSuper = false)
public class Person implements Serializable {
private static final long serialVersionUID = 1L;
/**
* 姓名
*/
@Excel(name = "姓名", orderNum = "0", width = 15)
private String name;
/**
* 登录用户名
*/
@Excel(name = "用户名", orderNum = "1", width = 15)
private String username;
@Excel(name = "手机号码", orderNum = "2", width = 15)
private String phoneNumber;
/**
* 人脸图片
*/
@Excel(name = "人脸图片", orderNum = "3", width = 15, height = 30, type = 2)
private String imageUrl;
}
处理导出时图片路径问题
package cn.com.javakf.easypoi.listener;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.net.URL;
import java.net.URLConnection;
import org.apache.poi.util.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import cn.afterturn.easypoi.cache.manager.IFileLoader;
public class FileLoaderImpl implements IFileLoader {
private static final Logger LOGGER = LoggerFactory
.getLogger(cn.afterturn.easypoi.cache.manager.FileLoaderImpl.class);
@Override
public byte[] getFile(String url) {
InputStream fileis = null;
ByteArrayOutputStream baos = null;
try {
// 判断是否是网络地址
if (url.startsWith("http")) {
URL urlObj = new URL(url);
URLConnection urlConnection = urlObj.openConnection();
urlConnection.setConnectTimeout(30);
urlConnection.setReadTimeout(60);
urlConnection.setDoInput(true);
fileis = urlConnection.getInputStream();
} else {
// 先用绝对路径查询,再查询相对路径
try {
fileis = new FileInputStream(url);
} catch (FileNotFoundException e) {
// 获取项目文件
fileis = FileLoaderImpl.class.getClassLoader().getResourceAsStream(url);
if (fileis == null) {
fileis = FileLoaderImpl.class.getResourceAsStream(url);
}
}
}
baos = new ByteArrayOutputStream();
byte[] buffer = new byte[1024];
int len;
while ((len = fileis.read(buffer)) > -1) {
baos.write(buffer, 0, len);
}
baos.flush();
return baos.toByteArray();
} catch (Exception e) {
LOGGER.error(e.getMessage(), e);
} finally {
IOUtils.closeQuietly(fileis);
IOUtils.closeQuietly(baos);
}
LOGGER.error(fileis + "这个路径文件没有找到,请查询");
return null;
}
}
package cn.com.javakf.easypoi.listener;
import org.springframework.boot.context.event.ApplicationReadyEvent;
import org.springframework.context.ApplicationListener;
import org.springframework.stereotype.Component;
import cn.afterturn.easypoi.cache.manager.POICacheManager;
@Component
public class ExcelListener implements ApplicationListener<ApplicationReadyEvent> {
@Override
public void onApplicationEvent(ApplicationReadyEvent event) {
POICacheManager.setFileLoader(new FileLoaderImpl());
}
}
注：
通过模板导只能以xls为结尾,xlsx为结尾时不能正常打开。
不用模板xls,xlsx都能正常打开,通过ExcelType设置,ExcelType.HSSF:xls ExcelType.XSSF:xlsx。
