百度网盘下载链接(永久有效): https://pan.baidu.com/s/1NNC9ueOl3OOwuXFGXUJsRg
ps:都是给予layui 2.5.5版本

使用方法:

前端

1:在对应页面得js中 配置以下属性

  2. /**excel导入插件必带的自定义的参数*/
  3. var TOOL_EXCEL_FIELD = [{
  4.     field:"name",
  5.     condition:["notNull","lessThan11"],
  6.     title:"账号"
  7. },{
  8.     field:"password",
  9.     condition:["notNull","lessThan18"],
  10.     title:"密码"
  11. },{
  12.     field:"nickName",
  13.     condition:["notNull","lessThan10"],
  14.     title:"名称"
  15. },{
  16.     field:"sex",
  17.     condition:["notNull","lessThan1"],
  18.     title:"性别"
  19. },{
  20.     field:"phone",
  21.     condition:["notNull","phoneNumber"],
  22.     title:"电话"
  23. },{
  24.     field:"email",
  25.     condition:["notNull","email"],
  26.     title:"邮件"
  27. },{
  28.     field:"roleName",
  29.     condition:["notNull","lessThan100"],
  30.     title:"角色"
  31. },{
  32.     field:"type",
  33.     condition:["notNull","lessThan100"],
  34.     title:"类型"
  35. },{
  36.     field:"description",
  37.     condition:["notNull","lessThan100"],
  38.     title:"描述"
  39. },{
  40.     field:"status",
  41.     condition:["notNull","lessThan100"],
  42.     title:"状态"
  43. }];
  44. var TOOL_EXCEL_CHECK_URL = "/user/excelInput";//后台检查数据是否合格
  45. var TOOL_EXCEL_BATCHADD_URL = "user/batchAdd";//后台批量新增的方法
  46. var TOOL_EXCEL_INSTANCE_FILE = "/uploadfiles/excel/用户导入示例.xlsx";//下载模板
  47. var TOOL_EXCEL_EXPORT_URL = "/user/findByPage/dataGrid";//导出的查询方法
  48. var TOOL_EXCEL_EXPORT_NAME = "用户表";//导出的文件名称
  49. /**--------------------*/
  50. //响应事件
  51. var active = {
  52.   exportExcel: function () {//导出
  53.     inletExportExcel();
  54.   }, importExcel: function () {//导入
  55.     inletImportExcel();
  56.   }
  57. };
  59. //注册点击事件
  60. $('.demoTable .layui-btn').on('click', function () {
  61.   var type = $(this).data('type');
  62.   active[type] ? active[type].call(this) : '';
  63. });

2:在对应的页面中引入”myExcelTool.js”与”md5.js”,”md5.js”是用于导入的excel中含”password”字段将自动进行加密
3:在对应的html页面中写入以下html语句

  1. <button type="button" class="layui-btn layui-btn-sm " data-type="importExcel">
  2.   <img src="../../images/importExcel.png"> 导入
  3. </button>
  4. <button type="button" class="layui-btn layui-btn-sm " data-type="exportExcel">
  5.   <img src="../../images/exportExcel.png"> 导出
  6. </button>
  7. <%--excel插件必带div--%>
  8. <div id="showExcelData" style="height: 1px">
  9.     <div class="layui-tab">
  10.         <ul class="layui-tab-title" id="showExcelData_head">
  11.         </ul>
  12.         <div class="layui-tab-content" id="showExcelData_body" style="overflow-x:hidden;overflow-y:hidden;">
  13.         </div>
  14.     </div>
  15. </div>

后端

1:pom.xml的jar配置请直接参考项目
1.1web.xml配置请直接参考项目
2:对应的实体类
2.1需要导入的实体类必须将所有的参数直接写出,不能用父类继承代替
2.2需要两个构造器 第一个无关紧要 第二个用于控制层设置参数

  1. public class User{
  2.     private String password;//密码
  3.     private String nickName;//昵称 张三\李四
  4.     private String sex;//性别
  5.     private String phone;//联系电话
  6.     private String email;//邮箱
  7.     private String headPortrait;//头像
  8.     private Role role;//角色
  9.     private String type;//类型
  10.     private List<Attachment> attachment;//附件
  11.     /**为了满足表格筛选插件与excel导入不过于麻烦,因此加上此参数,小小的冗余更加方便*/
  12.     private String roleName;//角色名称
  13.     //两个构造器
  14.     public User() {}
  15.     //用户辅助excel导入:快捷的设置好参数
  16.     public User(String name, String password, String nickName, String sex, String phone, String email, String roleName, String type, String description, String status) {
  17.         this.password = password;
  18.         this.roleName = roleName;
  19.         this.type = type;
  20.         super.setDescription(description);
  21.         super.setName(name);
  22.         super.setStatus(status);
  23.         this.nickName = nickName;
  24.         this.sex = sex;
  25.         this.phone = phone;
  26.         this.email = email;
  27.     }
  28.     get()...
  30.     set()...
  31. }

3:控制层必须继承公共的控制层
3.1在控制层的父类需要引入”ExcelUtil.java”帮助类

  1. /**
  2.  * 控制层的父类,综合各大控制层的增啥改查基础方法统一实现
  3.  */
  4. public abstract class AbsSuperController {
  5.     //设置excel必要的方法(负责将原始数据装入指定对象中)
  6.     public abstract Object toolExcel_packagParam(List<String> listParam);
  7.         /**
  8.      * 插件:excel预览
  9.      *
  10.      * @param toolExcelListTemp:因为list集合与文件上传有冲突,所以改为string类型
  11.      * @param file:excel本体
  12.      * @param request
  13.      * @return
  14.      */
  15.     @RequestMapping("excelInput")
  16.     @ResponseBody
  17.     public Back excelInput(String toolExcelListTemp, MultipartFile file, HttpServletRequest request) {
  18.         List<ToolExcel> toolExcelList = getToolExcelList(toolExcelListTemp);
  19.         System.out.println("接收的对象:" + toolExcelList);
  20.         System.out.println("接收的文件:" + file);
  21.         System.out.println(file.getOriginalFilename() + ":" + file.getSize());
  22.         Back back = new Back();
  23.         MultipartFile excl = file;
  24.         if (request instanceof MultipartHttpServletRequest) {
  25.             //说明文件不为空
  26.             if (!excl.isEmpty()) {
  27.                 try {
  28.                     String fileName = excl.getOriginalFilename();
  29.                     //转化为流的形式
  30.                     InputStream is = excl.getInputStream();
  31.                     List<Back> excelRead = getExcelRead(fileName, is, true);//过滤第一行
  32.                     ///以下参数将是最终返回参数////
  33.                     Boolean signFull_inner = true, signFull = true;//检查是否合格
  34.                     List<Back> backList = new ArrayList<>();//将excel处理完的数据装入集合
  35.                     /////////////////////////////
  36.                     for (int sheetNum = 0; sheetNum < excelRead.size(); sheetNum++) {
  37.                         String sheetName = excelRead.get(sheetNum).getMsg();//取出名字
  38.                         System.out.println("正在解析:" + sheetName);
  39.                         List<Row> list = excelRead.get(sheetNum).getExcelSheetList();//取出名字对应的excel
  40.                         List<Back> backList_inner = new ArrayList<>();//将excel数据装入集合
  41.                         signFull_inner = true;//每次循环前重置内循环的错误标记
  42.                         //首先是读取行 也就是一行一行读，然后在取到列，遍历行里面的行，根据行得到列的值
  43.                         for (Row row : list) {
  44.                             List<String> excelValue = new ArrayList<>();
  45.                             for (int i = 0; i < toolExcelList.size(); i++) {
  46.                                 excelValue.add(ExcelUtil.getValue(row.getCell(i)));
  47.                             }
  48.                             Back back_1 = new Back(0, " ", null);//不适用new的话没法让集合装入(虽然会加大对内存的消耗,但是java垃圾销毁机制将在for循环结束清楚这些内存)
  49.                             List<Object> errorList = new ArrayList<>();//每行对应的错误下标集合
  50.                             for (int i = 0; i < excelValue.size(); i++) {
  51.                                 if (toolExcelList.get(i).getCondition() != null && toolExcelList.get(i).getCondition().size() > 0) {//存在条件
  52.                                     int tempContainLessThan = isContainLessThan(toolExcelList.get(i).getCondition());
  53.                                     if (toolExcelList.get(i).getCondition().indexOf("notNull") >= 0 && "".equals(excelValue.get(i))) {//要求本字段不能为空
  54.                                         errorList.add(i + 1);//标记本行哪一个下标出现问题
  55.                                         back_1.setCode(-6);
  56.                                         back_1.setMsg(back_1.getMsg() + "第" + (row.getRowNum()) + "行第" + (i + 1) + "列的角色名不能为空!_");
  57.                                         signFull_inner = false;//标记本次预览不合格
  58.                                     }
  59.                                     if (tempContainLessThan >= 0 && excelValue.get(i).length() > tempContainLessThan) {//要求本字段小于100个字符
  60.                                         errorList.add(i + 1);
  61.                                         back_1.setCode(-6);
  62.                                         back_1.setMsg(back_1.getMsg() + "第" + (row.getRowNum()) + "行第" + (i + 1) + "中含有过长文本(最大每格字数为:"+tempContainLessThan+")_");
  63.                                         signFull_inner = false;
  64.                                     }
  65.                                     if (toolExcelList.get(i).getCondition().indexOf("phoneNumber") >= 0 && isNumLegal(excelValue.get(i)) == false) {//要求本字段必须为手机号码
  66.                                         errorList.add(i + 1);
  67.                                         back_1.setCode(-6);
  68.                                         back_1.setMsg(back_1.getMsg() + "第" + (row.getRowNum()) + "行第" + (i + 1) + "中号码格式不正确_");
  69.                                         signFull_inner = false;
  70.                                     }
  71.                                     if (toolExcelList.get(i).getCondition().indexOf("email") >= 0 && isEmail(excelValue.get(i)) == false) {//要求本字段必须为邮箱
  72.                                         errorList.add(i + 1);
  73.                                         back_1.setCode(-6);
  74.                                         back_1.setMsg(back_1.getMsg() + "第" + (row.getRowNum()) + "行第" + (i + 1) + "中邮箱格式不正确_");
  75.                                         signFull_inner = false;
  76.                                     }
  77.                                 }
  78.                             }
  79.                             back_1.setData(toolExcel_packagParam(excelValue));//设置对象由子类完成
  80.                             back_1.setErrorList(errorList);
  81.                             backList_inner.add(back_1);
  82.                         }
  83.                         System.out.println("预览成功,数据规范:" + signFull_inner + "\n集合大小:" + backList_inner.size() + "\n" + backList_inner);
  84.                         if (signFull_inner) {
  85.                             backList.add(new Back(0, sheetName, backList_inner));
  86.                         } else {
  87.                             signFull = false;
  88.                             backList.add(new Back(1, sheetName, backList_inner));
  89.                         }
  91.                     }
  92.                     back.setExcelSheetList(null);
  93.                     back.setExcelList(backList);
  94.                     if (signFull) {//全部的sheet都没出现错误
  95.                         back.setCode(0);
  96.                     } else {//sheet中出现数据不规范
  97.                         back.setCode(1);
  98.                     }
  99.                 } catch (Exception e) {
  100.                     e.printStackTrace();
  101.                     back = new Back(-4, "上传失败,上传出现异常", file.getOriginalFilename());
  102.                     System.out.println("预览出现异常");
  103.                 }
  104.             } else {
  105.                 System.out.println("预览失败,文件为空");
  106.                 back = new Back(-5, "上传失败,文件为空", file.getOriginalFilename());
  107.             }
  108.         }
  109.         return back;
  110.     }
  113.     /**
  114.      * 正则表达
  115.      * 手机号码由11位数字组成，
  116.      * 匹配格式：前三位固定格式+后8位任意数
  117.      * 此方法中前三位格式有：
  118.      * 13+任意数
  119.      * 15+除4的任意数
  120.      * 18+除1和4的任意数
  121.      * 17+除9的任意数
  122.      * 147
  123.      */
  124.     private static boolean isNumLegal(String phone) {
  125.         String regExp = "^((13[0-9])|(15[^4])|(18[0,2,3,5-9])|(17[0-8])|(147))\\d{8}$";
  126.         Pattern p = Pattern.compile(regExp);
  127.         Matcher m = p.matcher(phone);
  128.         return m.matches();
  129.     }
  131.     /**
  132.      * 描述：是否是邮箱.
  133.      *
  134.      * @param str 指定的字符串
  135.      * @return 是否是邮箱:是为true，否则false
  136.      */
  137.     private static Boolean isEmail(String str) {
  138.         Boolean isEmail = false;
  139.         String expr = "^([a-zA-Z0-9_\\-\\.]+)@((\\[[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.)|(([a-zA-Z0-9\\-]+\\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})$";
  141.         if (str.matches(expr)) {
  142.             isEmail = true;
  143.         }
  144.         return isEmail;
  145.     }
  147.     /**判断条件中是否包含lessThan+数量这个字段*/
  148.     private static int isContainLessThan(List<String> list){
  149.         for (String temp : list){
  150.             if (temp.indexOf("lessThan")>=0){
  151.                 return Integer.parseInt(temp.substring(8));
  152.             }
  153.         }
  154.         return -1;
  155.     }
  157.     /**
  158.      * 解析接收的excel必要的参数
  159.      *
  160.      * @param str
  161.      * @return
  162.      */
  163.     public static List<ToolExcel> getToolExcelList(String str) {
  164.         str = str.substring(1, str.length() - 2).replaceAll(" +", "");
  165.         System.out.println(str);
  167.         String[] strs = str.split("}");
  168.         List<ToolExcel> toolExcelList = new ArrayList<>();
  169.         for (int i = 0; i < strs.length; i++) {
  170.             ToolExcel toolExcel = new ToolExcel();
  171.             if (strs[i].indexOf("\"field\":\"") >= 0) {
  172.                 toolExcel.setField(strs[i].substring(strs[i].indexOf("\"field\":\"") + 9, strs[i].indexOf("\",\"", strs[i].indexOf("\"field\":\""))));
  173.             }
  174.             if (strs[i].indexOf("\"title\":\"") >= 0) {
  175.                 toolExcel.setTitle(strs[i].substring(strs[i].indexOf("\"title\":\"") + 9, strs[i].length() - 1));
  176.             }
  177.             if (strs[i].indexOf("\"condition\":[\"") >= 0) {
  178.                 toolExcel.setCondition(Arrays.asList(strs[i].substring(strs[i].indexOf("\"condition\":[\"") + 13, strs[i].indexOf("],\"", strs[i].indexOf("\"condition\":[\""))).replaceAll("\"+", "").split(",")));
  179.             }
  180.             toolExcelList.add(toolExcel);
  181.         }
  182.         return toolExcelList;
  183.     }
  185.     /**
  186.      * 获取解析文件行数据
  187.      * @param fileName : 文件地址
  188.      * @param isTitle  : 是否过滤第一行解析
  189.      * @return
  190.      * @throws Exception
  191.      */
  192.     public static List<Back> getExcelRead(String fileName, InputStream is, boolean isTitle) throws Exception{
  193.         try {
  194.             List<Back> backList = new ArrayList<>();
  195.             //判断其兼容版本 调用了判断版本的方法
  196.             Workbook workbook = getWorkbook(fileName,is);
  197.             for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
  198.                 Sheet sheet = workbook.getSheetAt(sheetNum);
  199.                 int count = 0;
  200.                 List<Row> list = new ArrayList<Row>();
  201.                 for (Row row : sheet) {
  202.                     // 跳过第一行的目录
  203.                     if (count == 0 && isTitle) {
  204.                         count++;
  205.                         continue;
  206.                     }
  207.                     list.add(row);
  208.                 }
  209.                 backList.add(new Back(sheet.getSheetName(),list));
  210.             }
  211.             System.out.println("excel共解析出:"+backList.size());
  212.             return backList;
  213.         } catch (Exception e) {
  214.             throw e;
  215.         }
  216.     }
  218.     /**判断版本的方法*/
  219.     public static Workbook getWorkbook(String fileName, InputStream is) throws Exception{
  220.         Workbook workbook = null;
  221.         try {
  222.             /** 判断文件的类型，是2003还是2007 */
  223.             boolean isExcel2003 = true;
  224.             if (WDWUtil.isExcel2007(fileName)) {
  225.                 isExcel2003 = false;
  226.             }
  228.             if (isExcel2003) {
  229.                 workbook = new HSSFWorkbook(is);
  230.             } else {
  231.                 workbook = new XSSFWorkbook(is);
  232.             }
  233.         } catch (Exception e) {
  234.             throw e;
  235.         }
  236.         return workbook;
  237.     }
  239.     /**得到celL值的方法：*/
  240.     public static String getValue(Cell cell){
  241.         if (cell == null || cell.equals(""))
  242.         {
  243.             return String.valueOf("");
  244.         }
  245.         else {
  246.             switch (cell.getCellTypeEnum()) {
  247.                 case BOOLEAN:
  248.                     return String.valueOf(cell.getBooleanCellValue());
  250.                 case NUMERIC:
  251.                     double value = cell.getNumericCellValue();
  252.                     return new BigDecimal(value).toString();
  254.                 case STRING:
  255.                     return String.valueOf(cell.getStringCellValue());
  257.                 default:
  258.                     return String.valueOf(cell.getStringCellValue());
  260.             }
  261.         }
  262.     }
  1. /**
  2.  * 用户管理控制层
  3.  */
  4. @Controller
  5. @RequestMapping("user")
  6. public class UserController extends AbsSuperController implements IUser {
  7.     //用于导入装载属性
  8.     @Override
  9.     public Object toolExcel_packagParam(List<String> listParam) {
  10.         return new User(listParam.get(0), listParam.get(1), listParam.get(2), listParam.get(3), listParam.get(4), listParam.get(5),
  11.                 listParam.get(6), listParam.get(7), listParam.get(8), listParam.get(9));
  12.     }
  13. }