SpringBoot二十五：整合Easypoi

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。

代码托管：springboot_easypoi