一. 前言

easypoi功能如同名字easy,主打的功能就是容易,让一个没见接触过poi的人员就可以方便的写出Excel导出,Excel模板导出,Excel导入,Word模板导出,通过简单的注解和模板语言(熟悉的表达式语法),完成以前复杂的写法

二. 功能

Excel自适应xls和xlsx两种格式,word只支持docx模式

「1.Excel导入」

注解导入 Map导入大数据量导入sax模式导入文件保存文件校验字段校验

「2.Excel导出」

注解导出模板导出 html导出

「3.Excel转html」
「4.word导出」
「5.pdf导出」

三. Easypoi介绍

首先抛出一个疑问, Easypoi为谁而开发?

不太熟悉poi的
不想写太多重复太多的
只是简单的导入导出的
喜欢使用模板的

总之Easypoi为这4类开发者而生。

四. 环境搭建

1、新建SpringBoot项目，引入pom依赖

    <parent>        <groupId>org.springframework.boot</groupId>        <artifactId>spring-boot-starter-parent</artifactId>        <version>2.1.0.RELEASE</version>        <relativePath/>    </parent>
    <!-- 设jdk版本1.8和项目编码格式UTF-8 -->    <properties>        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>        <java.version>1.8</java.version>    </properties>
    <dependencies>        <!-- 集成easypoi组件 .导出excel http://easypoi.mydoc.io/ -->        <dependency>            <groupId>cn.afterturn</groupId>            <artifactId>easypoi-base</artifactId>            <version>3.2.0</version>        </dependency>        <dependency>            <groupId>cn.afterturn</groupId>            <artifactId>easypoi-web</artifactId>            <version>3.2.0</version>        </dependency>        <dependency>            <groupId>cn.afterturn</groupId>            <artifactId>easypoi-annotation</artifactId>            <version>3.2.0</version>        </dependency>
        <!-- 这个依赖相当于上面 easypoi-base、easypoi-web、easypoi-annotation这3个依赖,可以引入上面3个依赖，也可以引入下面这一个，两个方案二选一-->        <!-- 	<dependency>                <groupId>cn.afterturn</groupId>                <artifactId>easypoi-spring-boot-starter</artifactId>                <version>3.2.0</version>            </dependency> -->
        <!-- 集成springboot web组件 -->        <dependency>            <groupId>org.springframework.boot</groupId>            <artifactId>spring-boot-starter-web</artifactId>        </dependency>        <!-- 集成springboot test组件 -->        <dependency>            <groupId>org.springframework.boot</groupId>            <artifactId>spring-boot-starter-test</artifactId>            <scope>test</scope>        </dependency>        <!-- 集成lombok 框架 -->        <dependency>            <groupId>org.projectlombok</groupId>            <artifactId>lombok</artifactId>        </dependency>        <!-- 文件上传组件 -->        <dependency>            <groupId>commons-fileupload</groupId>            <artifactId>commons-fileupload</artifactId>            <version>1.3.1</version>        </dependency>        <!--阿里巴巴 fastjson -->        <dependency>            <groupId>com.alibaba</groupId>            <artifactId>fastjson</artifactId>            <version>1.2.30</version>        </dependency>    </dependencies>
    <build>        <plugins>            <plugin>                <groupId>org.springframework.boot</groupId>                <artifactId>spring-boot-maven-plugin</artifactId>            </plugin>        </plugins>    </build>

2. 定义需要导出的实体对象User

「补充说明」：@Setter、@Getter、@ToString这三个注解是使用Lombok插件，不知道的另行百度即可；

import java.util.Date;
import javax.validation.constraints.NotBlank;
import cn.afterturn.easypoi.excel.annotation.Excel;import cn.afterturn.easypoi.excel.annotation.ExcelTarget;import lombok.Getter;import lombok.Setter;import lombok.ToString;
/** * <pre> * @author cao_wencao * @date 2018年12月13日 下午4:50:18 * </pre> */@ExcelTarget("20")@Setter@Getter@ToStringpublic class User implements java.io.Serializable{	@Excel(name = "id", width=15)	@NotBlank(message = "该字段不能为空")	private Integer id;
	@Excel(name = "姓名", orderNum = "0", width=30)	private String name;
	@Excel(name = "性别", replace = { "男_1", "女_2" }, orderNum = "1", width=30)	private String sex;
	@Excel(name = "生日", exportFormat = "yyyy-MM-dd",  orderNum = "2", width=30)	private String birthday;}

3. Excel导入导出工具类

❝

封装了调用EasyPoi APi底层接口的Excel导入导出工具类，直接调用工具类即可

❞

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;import org.apache.commons.lang3.StringUtils;import org.apache.poi.ss.usermodel.Workbook;import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;import java.io.File;import java.io.IOException;import java.net.URLEncoder;import java.util.List;import java.util.Map;import java.util.NoSuchElementException;
//Excel导入导出工具类public class ExcelUtils {	public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,			boolean isCreateHeader, HttpServletResponse response) {		ExportParams exportParams = new ExportParams(title, sheetName);		exportParams.setCreateHeadRows(isCreateHeader);		defaultExport(list, pojoClass, fileName, response, exportParams);	}
	public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,			HttpServletResponse response) {		defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));	}
	public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {		defaultExport(list, fileName, response);	}
	private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response,			ExportParams exportParams) {		Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);		if (workbook != null)			;		downLoadExcel(fileName, response, workbook);	}
	private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {		try {			response.setCharacterEncoding("UTF-8");			response.setHeader("content-Type", "application/vnd.ms-excel");			response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));			workbook.write(response.getOutputStream());		} catch (IOException e) {			// throw new NormalException(e.getMessage());		}	}
	private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {		Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);		if (workbook != null)			;		downLoadExcel(fileName, response, workbook);	}
	public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {		if (StringUtils.isBlank(filePath)) {			return null;		}		ImportParams params = new ImportParams();		params.setTitleRows(titleRows);		params.setHeadRows(headerRows);		List<T> list = null;		try {			list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);		} catch (NoSuchElementException e) {			// throw new NormalException("模板不能为空");		} catch (Exception e) {			e.printStackTrace();			// throw new NormalException(e.getMessage());		}		return list;	}
	public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows,			Class<T> pojoClass) {		if (file == null) {			return null;		}		ImportParams params = new ImportParams();		params.setTitleRows(titleRows);		params.setHeadRows(headerRows);		List<T> list = null;		try {			list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);		} catch (NoSuchElementException e) {			// throw new NormalException("excel文件不能为空");		} catch (Exception e) {			// throw new NormalException(e.getMessage());			System.out.println(e.getMessage());		}		return list;	}
}

4. 封装接口，定义Service

分别定义查询导出数据和保存导入数据的方法，我这个demo中就写假接口，不操作数据库，自己用的时候自行开发接口从数据库存取

import java.util.Date;import java.util.List;
import org.springframework.stereotype.Service;
import com.google.common.collect.Lists;import com.thinkingcao.demo.easypoi.entity.User;
/** * <pre> * @author cao_wencao * @date 2018年12月13日 下午5:37:17 * </pre> */@Servicepublic class UserService {		public List<User> findAll() {		List<User> listAll = Lists.newArrayList();		List<User> list = Lists.newArrayList();		User user = new User();		user.setId(10);		user.setName("张三");		user.setSex("男");		user.setBirthday(new Date().toString());		User user1 = new User();		user1.setId(20);		user1.setName("李四");		user1.setSex("男");		user1.setBirthday(new Date().toString());		user.setBirthday(new Date().toString());		User user2 = new User();		user2.setId(20);		user2.setName("王五");		user2.setSex("男");		user2.setBirthday(new Date().toString());		list.add(user);		list.add(user1);		list.add(user2);		listAll.addAll(list);		return listAll;	}}

5. 导出方法的Controller，ExcelExportController

import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;
import com.thinkingcao.demo.easypoi.entity.User;import com.thinkingcao.demo.easypoi.service.UserService;import com.thinkingcao.demo.easypoi.utils.ExcelUtils;
/** * <pre> * @author cao_wencao * @date 2018年12月13日 下午6:16:59 * </pre> */@RestController@RequestMapping("/excel/export")public class ExcelExportController {	@Autowired	private UserService userService;
	@GetMapping("/exportExcel")	public void export(HttpServletResponse response) {		System.out.println(1);		// 模拟从数据库获取需要导出的数据		List<User> personList = userService.findAll();		// 导出操作		ExcelUtils.exportExcel(personList, "easypoi导出功能", "导出sheet1", User.class, "测试user.xls", response);
	}}

6. 导入方法的Controller，ExcelImportController

import java.io.IOException;import java.util.List;
import org.springframework.web.bind.annotation.PostMapping;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RequestParam;import org.springframework.web.bind.annotation.RestController;import org.springframework.web.multipart.MultipartFile;
import com.alibaba.fastjson.JSONObject;import com.fasterxml.jackson.databind.util.JSONPObject;import com.thinkingcao.demo.easypoi.entity.User;
import cn.afterturn.easypoi.excel.ExcelImportUtil;import cn.afterturn.easypoi.excel.entity.ImportParams;import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;import lombok.extern.slf4j.Slf4j;
/** * <pre> * @author cao_wencao * @date 2018年12月13日 下午6:17:10 * </pre> */@RestController@RequestMapping("/excel/import")@Slf4jpublic class ExcelImportController {
	@PostMapping("/importExcel")	public String importExcel2(@RequestParam("file") MultipartFile file) {		ImportParams importParams = new ImportParams();		// 数据处理		importParams.setHeadRows(1);		importParams.setTitleRows(1);		// 需要验证		importParams.setNeedVerfiy(false);               		try {			ExcelImportResult<User> result = ExcelImportUtil.importExcelMore(file.getInputStream(), User.class,					importParams);			List<User> userList = result.getList();			for (User User : userList) {				// System.out.println(User);				log.info("从Excel导入数据到数据库的详细为 ：{}", JSONObject.toJSONString(User));				//TODO 将导入的数据做保存数据库操作			}			log.info("从Excel导入数据一共 {} 行 ", userList.size());		} catch (IOException e) {			log.error("导入失败：{}", e.getMessage());		} catch (Exception e1) {			log.error("导入失败：{}", e1.getMessage());		}		return "导入成功";	}}

7.创建启动类SpringBootEasyPoiApp

import org.springframework.boot.SpringApplication;import org.springframework.boot.autoconfigure.SpringBootApplication;
/** * <pre> * @author cao_wencao * @date 2018年12月13日 下午4:38:24 * </pre> */@SpringBootApplicationpublic class SpringBootEasyPoiApp {
	/**	 * <pre>  	 * @author cao_wencao	 * @param args	 * </pre>  	 */	public static void main(String[] args) {		SpringApplication.run(SpringBootEasyPoiApp.class, args);	}
}