spring boot整合easypoi实现导入、导出excel

1、添加pom

    <properties>
        <easypoi.version>4.0.0</easypoi.version>
    </properties>
 
    <!--EasyPoi导入导出-->
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-base</artifactId>
        <version>${easypoi.version}</version>
    </dependency>
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-web</artifactId>
        <version>${easypoi.version}</version>
    </dependency>
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-annotation</artifactId>
        <version>${easypoi.version}</version>
    </dependency>
    <!-- 文件上传 -->
    <dependency>
        <groupId>commons-fileupload</groupId>
        <artifactId>commons-fileupload</artifactId>
        <version>1.3.3</version>
    </dependency>

2、Utils类

utils类网上找的~忘原地址了，谢谢大虾~

import java.io.File;
import java.io.IOException;
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;
import lombok.extern.slf4j.Slf4j;

/**
 * 
 * @author ershuai
 * @date 2019年7月23日 下午2:01:06
 */
@Slf4j
public class FileUtils {

	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());
			log.error("down load excel: {}", e);
		}
	}
 
	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("模板不能为空");
			log.error("import excel NoSuchElementException: {}", e);
		} catch (Exception e) {
			e.printStackTrace();
			// throw new NormalException(e.getMessage());
			log.error("import excel Exception: {}", e);
		}
		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文件不能为空");
			log.error("import excel NoSuchElementException: {}", e);
		} catch (Exception e) {
			// throw new NormalException(e.getMessage());
			log.error("import excel Exception: {}", e);
		}
		return list;
	}

}

3、导出Excel

导出实体类；@Excel属性，参考：http://easypoi.mydoc.io/

import java.io.Serializable;
import java.util.Date;

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;

/**
 * 
 * @author ershuai
 * @date 2019年2月23日 下午3:50:59
 */
@Data
public class UserVo implements Serializable {

    /**
     * 
     */
    private static final long serialVersionUID = -6118059334688579641L;

    @Excel(name="昵称", width=15)
    private String nickName;
	
    @Excel(name="简介", width=20)
    private String userIntro;
	
    @Excel(name="性别", width=5, replace={"男_1", "女_2"})
    private Integer userSex;
	
    @Excel(name="注册时间", width=20, exportFormat="yyyy-MM-dd HH:mm:ss")
    private Date createTime;
	
}

导出方法

import java.util.ArrayList;
import java.util.Date;
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.RestController;

import com.ershuai.familybox.poi.toExport.user.UserVo;
import com.ershuai.familybox.utils.FileUtils;

@RestController
@RequestMapping("/common/excel")
public class ExcelController {

	/**
	 * 导出
	 * @author ershuai
	 * @date 2019年7月23日 下午2:19:56
	 * @param response
	 */
	@RequestMapping(value = "/export", method = RequestMethod.GET)
	public void toExport(HttpServletResponse response) {
		List<UserVo> users = new ArrayList<UserVo>();
		//...
		UserVo user = new UserVo();
		user.setNickName("张三");
		user.setUserIntro("我是张三，认识下！");
		user.setUserSex(1);
		user.setCreateTime(new Date());
		users.add(user);
		user = new UserVo();
		user.setNickName("李四");
		user.setUserIntro("我是李四，认识下！");
		user.setUserSex(2);
		user.setCreateTime(new Date());
		users.add(user);
		user = new UserVo();
		user.setNickName("王五");
		user.setUserIntro("我是王五，认识下！");
		user.setUserSex(1);
		user.setCreateTime(new Date());
		users.add(user);
		FileUtils.exportExcel(users, "测试导出 title" , "测试导出 sheet", UserVo.class, "测试导出.xls", response);
	}
}

4、导入Excel

以刚才导出的excel，进行导入测试

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>测试导入页面</title>
</head>
<body>
    <form action="http://ip:port/common/excel/import" method="post" enctype="multipart/form-data" >
    <input type="file" name="file" />
    <input type="submit" value="上传文件" />
    </form>
</body>
</html>

导入方法

import java.io.IOException;
import java.util.List;

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 com.alibaba.fastjson.JSON;
import com.ershuai.familybox.poi.toExport.user.UserVo;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import lombok.extern.slf4j.Slf4j;

@RestController
@RequestMapping("/common/excel")
@Slf4j
public class ExcelController {

	/**
	 * 导入
	 * @author ershuai
	 * @date 2019年7月23日 下午2:19:56
	 * @param file
	 */
	@RequestMapping(value = "/import", method = RequestMethod.POST)
	public void toImport(@RequestParam("file") MultipartFile file) {
		//...
		ImportParams importParams = new ImportParams();
		importParams.setHeadRows(1);//表头行数,默认1
		importParams.setTitleRows(1);//表格标题行数,默认0
		importParams.setNeedVerify(false);// 需要验证
		try {
			List<UserVo> users = ExcelImportUtil.importExcel(file.getInputStream(), UserVo.class, importParams);
			log.info(JSON.toJSONString(users));
		} catch (IOException e) {
			log.error("io exception: {}", e);
		} catch (Exception e) {
			log.error("exception: {}", e);
		}
	}
}

（* setHeadRows，setTitleRows，标题上面不管有多少行都要算表头~）

 

5、其他一些问题

5.1、easypoi有可能会和已引入的poi包有版本冲突，会报一些方法找不到、反射等错误，如下

java.lang.NoSuchMethodError: org.apache.poi.ss.usermodel.CellStyle.setAlignment(S)V…

java.lang.NoClassDefFoundError: …

java.lang.IllegalArgumentException: object is not an instance of declaring class…

提高版本号应该可以解决~最简单的同步到和easypoi一样的版本号

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>${apache.poi.version}</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>${apache.poi.version}</version>
</dependency>

5.2、The temporary upload location [/tmp/tomcat.xxx.xxx/work/Tomcat/localhost/ROOT] is not valid

我是部署到linux环境后，通过zuul网关上传，报上面的错。在网关的yml配置临时目录可以解决

server:
  tomcat:
    basedir: /data/tmp

5.2、通过zuul网关下载excel，内容乱码

我是因为开启了post filter，在run方法里面获取了一次body，又再设置了一次body造成的。应该是编码没处理好，具体解决办法还没有尝试，现在是通过判断response的header的Content-Type值有application/vnd.ms-excel，不获取body，返回的excel是正常的