开发中经常会遇到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" onsubmit="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)