Easypoi注解介绍:
@Excel:作用在字段上面,对应Excel的某一列的描述。
@ExcelCollection:集合,主要针对一对多的导出。
@ExcelIgnore:导出是忽略被注解的字段。
@ExcelTarget:作用于最外层的对象,描述这个对象的id,以便支持一个对象可以针对不同导出做出不同处理。
注解详情:
@Excel:
@ExcelCollection:
ImportParams:
Springboot示例:
1、pom文件中引入easypoi相应依赖:
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.0.3</version>
</dependency>
2、EasypoiUtil工具类:
package com.example.easypoi.easypoidemo.util;
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.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
import java.util.NoSuchElementException;
import java.io.File;
import java.net.URLEncoder;
import java.util.Map;
/**
* @ClassName EasypoiUtil
* @Description 导入、导出工具类
* @Author ZhaoDeLin
* @Date 2019/3/14 16:33
* @Email:casablanca523@163.com
**/
public class EasypoiUtil {
/**
*
* @param list 导出数据集合
* @param title 表头
* @param sheetName 工作表名
* @param pojoClass 实体类Class
* @param fileName 导出Excel名
* @param isCreateHeader
* @param response
* @throws Exception
*/
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) throws Exception {
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) throws Exception {
defaultExport(list, fileName, response);
}
private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
Workbook workbook = null;
try {
workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
} catch (Exception e) {
e.printStackTrace();
}
if (workbook != null)
try {
downLoadExcel(fileName, response, workbook);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws Exception {
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 Exception(e.getMessage());
}
}
private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws Exception {
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) throws Exception {
if (org.apache.commons.lang3.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 Exception("模板不能为空");
} catch (Exception e) {
e.printStackTrace();
throw new Exception(e.getMessage());
}
return list;
}
public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws Exception {
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 Exception("excel文件不能为空");
} catch (Exception e) {
throw new Exception(e.getMessage());
}
return list;
}
}
实体类:
注意:当进行Excel导入操作时,实体类中应该有无参的构造器,否则导入操作会抛异常,创建对象异常。
Controller层:
一对多导出:
一对多导出Excel:
更多有关Easypoi操作请前往http://easypoi.mydoc.io/#text_186901