首先导入jar包：
<!--EasyPoi导入导出--><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-base</artifactId><version>3.0.3</version><exclusions><exclusion><artifactId>guava</artifactId><groupId>com.google.guava</groupId></exclusion></exclusions></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>
封装实体类信息：
UserExcel：
import cn.afterturn.easypoi.excel.annotation.Excel;import cn.afterturn.easypoi.excel.annotation.ExcelTarget;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import java.io.Serializable;import java.util.Date;/*** @author gourd*/@Data@AllArgsConstructor@NoArgsConstructor@ExcelTarget("userExcel")public class UserExcel implements Serializable {/*** 名称*/@Excel(name = "姓名",orderNum = "1")private String name;/*** 年龄*/@Excel(name = "年龄",orderNum = "2",groupName = "基本信息")private Integer age;/*** 性别*/@Excel(name = "性别",orderNum = "2",replace = {"男_M", "女_F","未知_X"},groupName = "基本信息")private String sex;/*** 手机*/@Excel(name = "手机",orderNum = "3",width = 20,groupName = "联系方式")private String mobilePhone;/*** 邮箱*/@Excel(name = "邮箱",orderNum = "4",width = 20,groupName = "联系方式", isWrap=true)private String email;/*** 昵称*/@Excel(name = "昵称",orderNum = "5",groupName = "其他信息")private String nickName;@Excel(name = "生日", exportFormat = "yyyy-MM-dd", orderNum = "6",groupName = "其他信息")private Date birth;}
DeptExcel：
import cn.afterturn.easypoi.excel.annotation.Excel;import cn.afterturn.easypoi.excel.annotation.ExcelCollection;import cn.afterturn.easypoi.excel.annotation.ExcelTarget;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import java.io.Serializable;import java.util.List;/*** @author gourd*/@Data@AllArgsConstructor@NoArgsConstructor@ExcelTarget("deptExcel")public class DeptExcel implements Serializable {/*** 部门代码*/@Excel(name = "部门代码", orderNum = "2", width = 20, needMerge = true)private String code;/*** 部门名称*/@Excel(name = "部门名称", orderNum = "1", width = 20, needMerge = true)private String name;@ExcelCollection(name = "员工信息", orderNum = "3")private List<UserExcel> users;}
excel导入导出工具类：
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;import mons.lang3.StringUtils;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.springframework.web.multipart.MultipartFile;import javax.servlet.http.HttpServletResponse;import java.beans.PropertyDescriptor;import java.io.File;import java.io.IOException;import java.io.InputStream;import java.lang.reflect.Field;import java.lang.reflect.Method;import .URLEncoder;import java.util.List;import java.util.Map;import java.util.NoSuchElementException;/*** @author gourd*/@Slf4jpublic class ExcelUtil {public static boolean isExcel(String filePath){return filePath.matches("^.+\\.(?i)(xls)$");}public static boolean isExcel(String filePath){return filePath.matches("^.+\\.(?i)(xlsx)$");}/*** 得到Workbook对象* @param file* @return* @throws IOException*/public static Workbook getWorkBook(MultipartFile file) throws IOException{//这样写 excel 能兼容03和07InputStream is = file.getInputStream();Workbook hssfWorkbook = null;try {hssfWorkbook = new HSSFWorkbook(is);} catch (Exception ex) {is =file.getInputStream();hssfWorkbook = new XSSFWorkbook(is);}return hssfWorkbook;}/*** 得到错误信息* @param sb* @param list* @param i* @param obj* @param name 用哪个属性名去表明不和规定的数据* @param msg* @throws Exception*/public static void getWrongInfo(StringBuilder sb,List list,int i,Object obj,String name,String msg) throws Exception{Class clazz=obj.getClass();Object str=null;//得到属性名数组Field[] fields = clazz.getDeclaredFields();for(Field f : fields){if(f.getName().equals(name)){//用来得到属性的get和set方法PropertyDescriptor pd = new PropertyDescriptor(f.getName(), clazz);//得到get方法Method getMethod=pd.getReadMethod();str = getMethod.invoke(obj);}}if(i==0) {sb.append(msg + str + ";");}else if(i==(list.size()-1)) {sb.append(str + "</br>");}else {sb.append(str + ";");}}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);}}public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {try {// 告诉浏览器用什么软件可以打开此文件response.setHeader("content-Type", "application/vnd.ms-excel");//设置浏览器响应头对应的Content-dispositionresponse.setHeader("Content-Disposition","attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));//编码response.setCharacterEncoding("UTF-8");workbook.write(response.getOutputStream());} catch (IOException e) {e.printStackTrace();}finally {try {workbook.close();} catch (IOException e) {e.printStackTrace();}}}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){e.printStackTrace();} catch (Exception e) {e.printStackTrace();}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){e.printStackTrace();} catch (Exception e) {e.printStackTrace();}return list;}}
excel导入导出service：
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 cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;import mon.data.BaseResponse;import mon.rbac.entity.RbacDepart;import mon.rbac.entity.RbacUser;import mon.rbac.service.RbacUserService;import mon.utils.CollectionCopyUtil;import mon.utils.DateUtil;import mon.utils.ExcelUtil;import com.gourd.file.excel.entity.DeptExcel;import com.gourd.file.excel.entity.UserExcel;import lombok.extern.slf4j.Slf4j;import org.apache.poi.ss.usermodel.Workbook;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import org.springframework.web.multipart.MultipartFile;import javax.servlet.http.HttpServletResponse;import java.io.IOException;import java.util.*;/*** @author gourd*/@Service@Slf4jpublic class ExcelService {@Autowiredprivate RbacUserService userService;/*** 单sheet导出** @param response*/public void export(HttpServletResponse response) {// 模拟从数据库获取需要导出的数据List<RbacUser> personList = userService.findAll();List<UserExcel> userExcelList = CollectionCopyUtil.copyList(personList,UserExcel.class);//导出操作ExcelUtil.exportExcel(userExcelList,"用户列表","用户", UserExcel.class,"用户信息"+ DateUtil.date2Str(new Date(),"YYYY-MM-dd-HH-mm") +".xls",response);}/*** 多sheet导出示例** @return*/public void exportSheets(HttpServletResponse response) {// 准备测试数据List<UserExcel> userExcels = new ArrayList<>();UserExcel userExcel = new UserExcel();userExcel.setName("gourd");userExcel.setAge(23);userExcel.setSex("M");userExcel.setMobilePhone("10086");userExcel.setEmail("");userExcel.setNickName("gourd");userExcel.setBirth(new Date());userExcels.add(userExcel);UserExcel userExcel2 = new UserExcel();userExcel2.setName("葫芦");userExcel2.setAge(23);userExcel2.setSex("X");userExcel2.setMobilePhone("110");userExcel2.setEmail("136@");userExcel2.setNickName("gourd");userExcel2.setBirth(new Date());userExcels.add(userExcel2);List<DeptExcel> deptExcels = new ArrayList<>();DeptExcel deptExcel = new DeptExcel();deptExcel.setCode("T-01");deptExcel.setName("技术一部");deptExcel.setUsers(userExcels);deptExcels.add(deptExcel);DeptExcel deptExcel2 = new DeptExcel();deptExcel2.setCode("T-02");deptExcel2.setName("技术二部");deptExcel2.setUsers(userExcels);deptExcels.add(deptExcel2);// 创建参数对象ExportParams exportParams1 = new ExportParams();// 设置sheet得名称exportParams1.setSheetName("部门人员信息");ExportParams exportParams2 = new ExportParams();exportParams2.setSheetName("用户信息");// 创建sheet1使用得mapMap<String, Object> deptDataMap = new HashMap<>(4);// title的参数为ExportParams类型deptDataMap.put("title", exportParams1);// 模版导出对应得实体类型deptDataMap.put("entity", DeptExcel.class);// sheet中要填充得数据deptDataMap.put("data", deptExcels);// 创建sheet2使用得mapMap<String, Object> userDataMap = new HashMap<>(4);userDataMap.put("title", exportParams2);userDataMap.put("entity", UserExcel.class);userDataMap.put("data", userExcels);// 将sheet1和sheet2使用得map进行包装List<Map<String, Object>> sheetsList = new ArrayList<>();sheetsList.add(deptDataMap);sheetsList.add(userDataMap);// 执行方法Workbook workbook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);ExcelUtil.downLoadExcel("用户信息"+ DateUtil.date2Str(new Date(),"YYYY-MM-dd-HH-mm") +".xls",response,workbook);}/*** 单sheet导入** @param file*/public BaseResponse importExcel(MultipartFile file) {ImportParams importParams = new ImportParams();// 距离表头中间有几行不要的数据importParams.setHeadRows(1);// 表头在第几行importParams.setTitleRows(1);// 需要验证importParams.setNeedVerfiy(true);try {ExcelImportResult<UserExcel> result = ExcelImportUtil.importExcelMore(file.getInputStream(), UserExcel.class,importParams);List<UserExcel> successList = result.getList();for (UserExceldemoExcel : successList) {System.out.println(demoExcel);}}catch (Exception e) {log.error("导入失败:{}",e);return BaseResponse.failure("导入失败！请检查导入文档的格式是否正确");}return BaseResponse.ok("导入成功");}/*** 多sheet导入** @param file* @return* @throws IOException*/public BaseResponse importSheets(MultipartFile file){try {// 根据file得到Workbook,主要是要根据这个对象获取,传过来的excel有几个sheet页Workbook workBook = ExcelUtil.getWorkBook(file);StringBuilder sb=new StringBuilder();ImportParams params = new ImportParams();// 循环工作表Sheetfor (int numSheet = 0; numSheet < workBook.getNumberOfSheets(); numSheet++) {// 表头在第几行params.setTitleRows(0);// 距离表头中间有几行不要的数据params.setStartRows(1);// 第几个sheet页params.setStartSheetIndex(numSheet);// 验证数据params.setNeedVerfiy(true);if(numSheet==0){ExcelImportResult<UserExcel> result =ExcelImportUtil.importExcelMore(file.getInputStream(),UserExcel.class, params);// 校验是否合格if(result.isVerfiyFail()){// 不合格的数据List<UserExcel> errorList = result.getList();// 拼凑错误信息,自定义for(int i=0;i<errorList.size();i++){ExcelUtil.getWrongInfo(sb, errorList, i, errorList.get(i), "name", "用户信息不合法");}}// 合格的数据List<UserExcel > successList = result.getList();// 业务逻辑}else if(numSheet==1){ExcelImportResult<DeptExcel> result =ExcelImportUtil.importExcelMore(file.getInputStream(),DeptExcel.class, params);// 校验是否合格if(result.isVerfiyFail()){// 不合格的数据List<RbacDepart> errorList = result.getList();// 拼凑错误信息,自定义for(int i=0;i<errorList.size();i++){ExcelUtil.getWrongInfo(sb, errorList, i, errorList.get(i), "name", "部门信息不合法");}}// 校验合格的数据List<DeptExcel> successList = result.getList();// 业务逻辑}}} catch (Exception e) {log.error("导入失败:{}",e);return BaseResponse.failure("导入失败！请检查导入文档的格式是否正确");}return BaseResponse.ok("导入成功！");}}
Controller掉用：
import mon.data.BaseResponse;import com.gourd.file.excel.service.ExcelService;import io.swagger.annotations.Api;import io.swagger.annotations.ApiOperation;import lombok.extern.slf4j.Slf4j;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.*;import org.springframework.web.multipart.MultipartFile;import javax.servlet.http.HttpServletResponse;/*** @author gourd*/@RestController@RequestMapping("/excel")@Api(tags = "excel", description = "excel控制器")@Slf4jpublic class ExcelController {@Autowiredprivate ExcelService excelService;@GetMapping("/single")@ApiOperation(value="导出")public void export(HttpServletResponse response) {excelService.export(response);}@GetMapping("/sheets")@ApiOperation(value="导出多个sheet")public void exportSheets(HttpServletResponse response) {excelService.exportSheets(response);}@PostMapping("/single")@ApiOperation(value="导入")public BaseResponse importExcel(@RequestParam("file") MultipartFile file) {return excelService.importExcel(file);}@PostMapping(value = "/sheets")@ApiOperation(value="多sheet导入")public BaseResponse importSheets(MultipartFile file){return excelService.importSheets(file);}}
调用export-sheets接口导出结果截图：
如果你使用的POI请参考：/HXNLYW/article/details/96830248
@Excel注解属性解释：
