开发中经常会遇到excel的处理，导入导出解析等等。正好最近在项目里面需要用到EasyPOI，非常
容易，简单上手，一行代码实现导入导出。
1、导入maven依赖
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.0.0</version>
</dependency>
在springboot项目里面也可以使用如下坐标
<dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-spring-boot-starter</artifactId> <version>4.0.0</version> </dependency>
2、导出功能
前端页面：
<form class="layui-form" action="/load" οnsubmit="return false" id="form"> <div class="layui-form-item"> <label class="layui-form-label">经办人</label> <div class="layui-input-inline"> <input type="text" name="transactor" id="transactor" placeholder="请输入经办人" autocomplete="off" class="layui-input"> </div> </div> <div class="layui-form-item"> <label class="layui-form-label">摘要</label> <div class="layui-input-inline"> <input type="text" name="summary" id="summary" placeholder="请输入摘要" autocomplete="off" class="layui-input"> </div> </div> <div class="layui-form-item"> <label class="layui-form-label">选择时间</label> <div class="layui-input-inline"> <input type="text" name="opetime" id="opetime" placeholder="请选择时间范围" autocomplete="off" class="layui-input"> </div> </div> <div class="layui-form-item"> <div class="layui-input-block"> <button class="layui-btn" lay-submit id="loadBt">导出</button> </button>--> </div> </div> </form>
<script>
layui.use(['layer', 'laydate'], function () {
var layer = layui.layer;
var laydate = layui.laydate;
laydate.render({
elem: '#opetime',
type:'date',
range:true,//开启左右面板,可以进行日期范围的选择，很好用
//trigger:'mouseover',//定义鼠标悬停时弹出控件
theme:'#393D49',//主题颜色
calendar:true//是否显示公历节日
});
});
layui.use('form', function () {
var form = layui.form;
var $ = layui.$;
$("#loadBt").click(function () {
transactor = $("#transactor").val(),
summary = $("#summary").val(),
opetime=$("#opetime").val();
$("#form").attr("onsubmit", "return true");
$("#form").submit();
$("#form").attr("onsubmit", "return false");
});
});
</script>
后台Controller层代码
/**
* @Author Steel.D
* @Description
* @Date 2019-7-30 16:45
* @Param excel的模型集合，请求，响应
* @return
**/
@RequestMapping("/load")
public void loadExcel(ModelMap map, HttpServletRequest request,
HttpServletResponse response){
//将接收的参数进行处理
String transactor = request.getParameter("transactor");
String summary = request.getParameter("summary");
String opetime = request.getParameter("opetime");
//传过来的数据格式为2019-09-12 - 2019-09-30 需要进行处理
opetime = opetime.replaceAll(" ", "");
Map<String,Object> params1 = new HashMap<>();
if ( StringUtils.isNotEmpty(opetime)){
String substring = opetime.substring(0, 10);
String substring1 = opetime.substring(11);
Date date = DateUtils.strToDate(substring);
Date date1 = DateUtils.strToDate(substring1);
params1.put("opetime",date);
params1.put("endtime",date1);
}
params1.put("transactor",transactor);
params1.put("summary",summary);
List<Finance> bills = financeDao.all(params1);
ExportParams params = new ExportParams("流水详情", "概览", ExcelType.XSSF);
params.setFreezeCol(2);
map.put(NormalExcelConstants.DATA_LIST, bills);
map.put(NormalExcelConstants.CLASS, Finance.class);
map.put(NormalExcelConstants.PARAMS, params);
map.put(NormalExcelConstants.FILE_NAME, "编易教育账单流水");
//封装数据进行数据导出 PoiBaseView.render(map,request,response,NormalExcelConstants.EASYPOI_EXCEL_VIEW);
}
3、导入Excel功能
前端代码：
<form class="layui-form" action="/upload" enctype="multipart/form-data" method="post"> <input type="file" name="file"><br> <input type="submit" value="提交" id="submit"> </form>
后台Controller，一行代码实现Excel解析
/**
* @Author Steel.D
* @Description
* @Date 2019-7-31 17:32
* @Param
* @return
**/
@PostMapping("/upload")
public ResponseInfo upload(@RequestParam("file") MultipartFile file){
//上传的Excel进行数据模型解析封装 ，四个参数分别为，上传的文件，excel表标题行数，头行数，实体类class
List<TStudent> tStudents = EasyPoiUtil.importExcel(file, 1, 1, TStudent.class);
studentService.sava(tStudents);
return new ResponseInfo("1","成功");
}
EasyPOI非常实用的一个工具类：
/**
* @Author Steel.D
* @Description easypoi导入导出通用工具类
* @Date 2019-7-31 9:29
* @Param
* @return
**/
public class EasyPoiUtil {
/**
* 功能描述：复杂导出Excel，包括文件名以及表名。创建表头
*
* @author Steel.D
* @Date 2019-7-31 9:30
* @param list 导出的实体类
* @param title 表头名称
* @param sheetName sheet表名
* @param pojoClass 映射的实体类
* @param isCreateHeader 是否创建表头
* @param fileName
* @param response
* @return
*/
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);
}
/**
* 功能描述：复杂导出Excel，包括文件名以及表名,不创建表头
*
* @author Steel.D
* @Date 2019-7-31 9:35
* @param list 导出的实体类
* @param title 表头名称
* @param sheetName sheet表名
* @param pojoClass 映射的实体类
* @param fileName 文件名
* @param response
* @return
*/
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));
}
/**
* 功能描述：Map 集合导出
*
* @author Steel.D
* @Date 2019-7-31 9:45
* @param list 实体集合
* @param fileName 导出的文件名称
* @param response
* @return
*/
public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
defaultExport(list, fileName, response);
}
/**
* 功能描述：默认导出方法
*
* @author Steel.D
* @Date 2019-7-31 9:50
* @param list 导出的实体集合
* @param fileName 导出的文件名
* @param pojoClass pojo实体
* @param exportParams ExportParams封装实体
* @param response
* @return
*/
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);
}
}
/**
* 功能描述：Excel导出
*
* @author Steel.D
* @Date 2019-7-31 10:35
* @param fileName 文件名称
* @param response
* @param workbook Excel对象
* @return
*/
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 RuntimeException(e);
}
}
/**
* 功能描述：默认导出方法
*
* @author SteeL.D
* @Date 2019-7-31 10:45
* @param list 导出的实体集合
* @param fileName 导出的文件名
* @param response
* @return
*/
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);
}
/**
* 功能描述：根据文件路径来导入Excel
*
* @author Steel.D
* @Date 2019-7-31 11:05
* @param filePath 文件路径
* @param titleRows 表标题的行数
* @param headerRows 表头行数
* @param pojoClass Excel实体类
* @return
*/
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 RuntimeException("模板不能为空");
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
/**
* 功能描述：根据接收的Excel文件来导入Excel,并封装成实体类
*
* @author Steel.D
* @Date 2019-7-31 11:30
* @param file 上传的文件
* @param titleRows 表标题的行数
* @param headerRows 表头行数
* @param pojoClass Excel实体类
* @return
*/
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 RuntimeException("excel文件不能为空");
} catch (Exception e) {
throw new RuntimeException(e.getMessage());
}
return list;
}
}
实体类导出的时候需要用到@Excel注解
@Excel(name="账单id") private Integer id; @Excel(name="经办人") private String transactor; @Excel(name = "摘要") private String summary; @Excel(name="详情",width = 50) private String details; @Excel(name="收入") private Integer income; @Excel(name = "支出") private Integer payment; @Excel(name = "累计") private Integer count; @Excel(name = "备注",width = 20) private String comments; @Excel(name="处理时间",exportFormat ="yyyy-MM-dd HH:mm:ss",width = 30)