一.pom引入
<!--easypoi导出excel start-->
<!--easypoi-base 导入导出的工具包,可以完成Excel导出,导入,Word的导出,Excel的导出功能-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.0.3</version>
</dependency>
<!--easypoi-web 耦合了spring-mvc 基于AbstractView,极大的简化spring-mvc下的导出功能-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.0.3</version>
</dependency>
<!--easypoi-annotation 基础注解包,作用与实体对象上,拆分后方便maven多工程的依赖管理-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.0.3</version>
</dependency>
<!--easypoi导出excel end-->
<!-- 文件上传 start-->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<!-- 文件上传 end-->
二.编写实体类（easyPoi注解看官网 http://easypoi.mydoc.io/）
- 此处注意必须要有空构造函数，否则会报错“对象创建错误”
- 关于注解@Excel，其他还有@ExcelCollection，@ExcelEntity ，@ExcelIgnore，@ExcelTarget等，此处我们用不到，可以去官方查看更多
package com.lyancafe.material.model;
import cn.afterturn.easypoi.excel.annotation.Excel;
/**
* @author 孙创业
*/
public class UserModel {
@Excel(name = "用户Id", orderNum = "0")
private int id;
@Excel(name = "用户名", orderNum = "1")
private String userName;
@Excel(name = "密码", orderNum = "2")
private String password;
@Excel(name = "姓名", orderNum = "3")
private String realName;
@Excel(name = "电话", orderNum = "4")
private String tel;
@Excel(name = "年龄", orderNum = "5")
private int age;
@Excel(name = "地址", orderNum = "6")
private String address;
public UserModel() {
}
public UserModel(int id) {
this.id = id;
}
public UserModel(int id, String userName, String password, String realName, String tel, int age, String address) {
this.id = id;
this.userName = userName;
this.password = password;
this.realName = realName;
this.tel = tel;
this.age = age;
this.address = address;
}
public String getTel() {
return tel;
}
public void setTel(String tel) {
this.tel = tel;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getRealName() {
return realName;
}
public void setRealName(String realName) {
this.realName = realName;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "UserModel{" +
"id=" + id +
", userName='" + userName + '\'' +
", password='" + password + '\'' +
", realName='" + realName + '\'' +
", tel='" + tel + '\'' +
", age=" + age +
", address='" + address + '\'' +
'}';
}
}
三.写FileUtil工具类（导入导出公用方法）
package com.lyancafe.material.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.commons.lang.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;
/**
* @author scy 2018/8/26
*/
public class FileUtil {
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());*/
}
return list;
}
}
四.Controller中写导入导出的方法
/**
* SpringBoot+easyPoi一行代码搞定excel导出
* @return
*/
@RequestMapping("export")
public void export(HttpServletResponse response){
//从数据库获取需要导出的数据
List<UserModel> userModelList =userBo.getExportDatas();
//导出操作
FileUtil.exportExcel(userModelList,"用户信息","用户制作scy",UserModel.class,"用户.xls",response);
}
/**
* SpringBoot+easyPoi一行代码搞定excel导入
* @return
*/
@RequestMapping("importExcel")
public String importExcel(){
String filePath = "F:\\用户.xls";
//解析excel，
List<UserModel> userModelList= FileUtil.importExcel(filePath,1,1,UserModel.class);
//也可以使用MultipartFile,使用 FileUtil.importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass)导入
System.out.println("导入数据一共【"+userModelList.size()+"】行");
//TODO 保存数据库.是个集合需要遍历
for(UserModel userModelLists : userModelList) {
String userName = userModelLists.getUserName();
String password = userModelLists.getPassword();
//数据库查重（写个查询的方法），名字和密码重复就不插入，否则插入
UserModel byNameAndPassword = userBo.findByNameAndPassword(userName, password);
if (byNameAndPassword==null){
userBo.saveImport(userModelLists);
}
}
return "redirect:/user/getAllUser";
}
五.ServiceImpl(我写的boImpl)层
/**
* SpringBoot+easyPoi一行代码搞定excel导入导出
*/
@Override
public List<UserModel> getExportDatas(){
List<UserModel> userModels = userDao.getExportDatas();
return userModels;
}
/**
* 保存导入的用户
*/
@Override
public void saveImport(UserModel userModel){
userDao.saveImport(userModel);
}
六.service(我写的bo)层
/**
* SpringBoot+easyPoi一行代码搞定excel导入导出
* @return
*/
List<UserModel> getExportDatas();
/**
* 保存导入的用户
* @param userModel
* @return
*/
void saveImport(UserModel userModel);
七.dao层
/**
* SpringBoot+easyPoi一行代码搞定excel导入导出
* @return
*/
List<UserModel> getExportDatas();
/**
* 保存导入的用户
* @param userModel
* @return
*/
void saveImport(UserModel userModel);
八.dao.xml层
<!--SpringBoot+easyPoi一行代码搞定excel导入导出-->
<select id="getExportDatas" resultType="com.lyancafe.material.model.UserModel">
select * from t_user
</select>
<!--保存导入的用户-->
<insert id="saveImport" parameterType="com.lyancafe.material.model.UserModel">
insert into t_user(userName, age, realName, tel, address)
values(#{userName}, #{age}, #{realName},#{tel},#{address})
</insert>
九.allUser.jsp层（页面只需要写这两句）
<div><a href="<%=basePath%>user/export">导出用户信息</a></div>
<div><a href="<%=basePath%>user/importExcel">导入用户信息</a></div>
十.导入导出的页面展示
十一.导出的模板展示
有什么不懂得可以问我，如果恢复不及时加QQ：501397578
