根据数据库字段动态生成excel模版下载，上传模版获取数据存入数据库(poi+java类反射)

 环境：mysql5.7.28+java8+Spring boot 2.2.4 +mybatis-plus3.10

 动态：根据需求，用户可以选择对应的字段生成excle模版 下载

 poi+反射：poi是excel的第三方jar，反射的作用是给表实体对象属性赋值，方便入库操作。

      现在很多的应用都有批量导入的功能，批量导入用的最多的也是excel。我们实际的项目中也用了很多这方面的功能，所以博主系统的CV了一下这方面的代码，下面分步骤进行该功能的实现。此方法的优点：不限于模版字段的排列顺序，避免过多的重复set代码。动态的生成模版信息。

      注意：数据库实体类属性变量名，要严格按照驼峰的模式命名，方便数据的读取，反射的赋值。

      如果数据量过大，建议采用多线程的方式导入数据，数据的分割根据实际情况，本文采用的是单线程方式执行。

1:依赖的jar包

       <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>

        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.4</version>
        </dependency>

2:生成对应数据库的模版

生成模版的前提是查询数据库有哪些字段，有了字段的信息，就可以根据java se中的流知识，生成对应的模版文件，下面的这个是查询表结构的所有信息。因为是要生成字段对应的模版，所以我们把sql修改一下即可

 select * from information_schema.COLUMNS where table_name = '表名'
    

查询字段的sql如下：

 select COLUMN_NAME from information_schema.COLUMNS where table_name = '表名'

同样的这个查询也可以用mybatis框架进行映射，返回的是List<String> 类型，对应的mapper层次如下。

    List<String> queryColumn();

到这步，我们的字段信息就有了，也就是excel的表头信息有了，下面就是根据表头信息生成对应的模版了。

 生成代码如下图：需要说明的是传入的参数：数据库的字段信息，模版的名称（可任意取），生成模版的路径所在地

 public static boolean createModel(List<String> list, String modelName, String modelPath) {
        boolean newFile = false;
//创建excel工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
//创建工作表sheet
        HSSFSheet sheet = workbook.createSheet();
//创建第一行
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell;
//设置样式
        CellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(IndexedColors.AQUA.getIndex());

//插入第一行数据的表头
        for (int i = 0; i < list.size(); i++) {
            cell = row.createCell(i);
            cell.setCellStyle(style);
            cell.setCellValue(list.get(i));
        }
//创建excel文件
        File file = new File(modelPath + File.separator + modelName);
        try {
//删除该文件夹下原来的模版文件
            deleteDir(new File(modelPath + File.separator));
//判断对应的文件夹是否有，无则新建
            File myPath = new File(modelPath);
            if (!myPath.exists()) {
                myPath.mkdir();
            }
//创建新的模版文件
            newFile = file.createNewFile();
            //将excel写入
            FileOutputStream stream = FileUtils.openOutputStream(file);
            workbook.write(stream);
            stream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return newFile;
    }

下面的是删除原来模版文件的工具方法

private static boolean deleteDir(File dir) {
        if (dir.isDirectory()) {
            String[] children = dir.list();
            if (children != null)
                //递归删除目录中的子目录下
                for (String child : children) {
                    boolean success = deleteDir(new File(dir, child));
                    if (!success) {
                        return false;
                    }
                }
        }
        // 目录此时为空，可以删除
        return dir.delete();
    }

文件已经生成了，剩下的就是下载文件。这里需要说明一下，如何动态的生成模版

动态的生成模版就是动态的获取数据库的字段，只需根据用户选取的数据，在sql的查询，或者代码中做修改。如下的not in就是排除这些不需要的字段，当然你也可以选择其他方式进行过滤，程序中过滤是最好的选择。

<select id="queryColumn" resultType="string">
        select COLUMN_NAME from information_schema.COLUMNS where table_name = 'cpa_account_list'
        and column_name not in ('account_type_id','account_status','create_time','id','out_time','use_time'
        ,'update_time')
</select>

生成模版后，通过浏览器访问即可下载。下载的代码如下：传入文件的生成路径，文件的名字，下载生成新的文件名（可任意）

 public static ResponseEntity<InputStreamResource> download(String filePath, String fileName, String newName) {
        String path;
        ResponseEntity<InputStreamResource> response = null;
        try {
            path = filePath + separator + fileName;
            log.info("下载的路径为-->[{}]", path);
            File file = new File(path);
            InputStream inputStream = new FileInputStream(file);
            HttpHeaders headers = new HttpHeaders();
            headers.add("Cache-Control", "no-cache, no-store, must-revalidate");
            headers.add("Content-Disposition",
                    "attachment; filename="
                            + new String(newName.getBytes(StandardCharsets.UTF_8)) + ".xlsx");
            headers.add("Pragma", "no-cache");
            headers.add("Expires", "0");
            response = ResponseEntity.ok().headers(headers)
                    .contentType(MediaType.parseMediaType("application/octet-stream"))
                    .body(new InputStreamResource(inputStream));
        } catch (FileNotFoundException e1) {
            log.error("找不到指定的文件", e1);
        }
        return response;
    }

最后就是在web层次调用上述方法，即可完成下载，博主的controlle代码如下，仅供参考

import org.springframework.core.io.InputStreamResource;
import org.springframework.http.ResponseEntity;

 @GetMapping(value = "/downloadModel", produces = "application/json;charset=UTF-8")
 @ApiOperation(value = "账号信息的模板下载", produces = "application/json;charset=UTF-8")
    public Object downloadAccountModel() {
        //文件名
        String modelFileName = "accountList.xlsx";
        //下载展示的文件名
        ResponseEntity<InputStreamResource> response = null;
        try {
            List<String> columns = cpaAccountListService.queryColumn();
//            传人数据库的字端，创建资料的模版
            boolean model = CpaDownloadFileUtil.createModel(columns, modelFileName, modelPath);
            if (model) response = CpaDownloadFileUtil.download(modelPath, modelFileName, "AccountListModel");
        } catch (Exception e) {
            e.printStackTrace();
            log.error("下载模板失败");
        }
        return response;
    }

采用的是swagger测试下载的结果如下 

3: 将模版的数据导入到数据库中

批量导入模版中的数据，关键点就是如何将数据准确的读取，生成java对象,放入集合中。其次是利用mybatis-plus的批量导入数据即可。

下面为读取excel的方法，只读取sheet0的数据。读取的数据为一行行数组。将数组放入到集合中返回。具体的解释，代码注释都有。

需要注意处理单元格数据为空的方法。

  /**
     * 解析excel
     * auth psy
     * @param inp excel InputStream.
     * @return 对应数据列表
     */
    public static List<List<Object>> readExcel(InputStream inp) {
        Workbook wb = null;
        try {
            wb = WorkbookFactory.create(inp);
//           获取地0个sheet的数据
            Sheet sheet = wb.getSheetAt(0);
            List<List<Object>> excels = new ArrayList<>();
//            遍历每一行数据
            int cellsNumber = 0;
            for (int i = 0; i <= sheet.getLastRowNum(); i++) {
                if (i == 0) {
//                   获取每一行总共的列数
                    cellsNumber = sheet.getRow(i).getPhysicalNumberOfCells();
                }
                List<Object> excelRows = new ArrayList<>();
                // 遍历每一行中的每一列中的
                for (int j = 0; j < cellsNumber; j++) {
//                    i和j组成二维坐标可以定位到对应到单元格内
                    Cell cell = sheet.getRow(i).getCell(j);
                    if (i >= 1) {
//                      如果单元格到内容为空就设置为"null"代表的是无数据
                        if (cell == null) {
                            excelRows.add("null");
                        } else {
//                          不是空值的单元格数据
                            excelRows.add(getValue(cell));
                        }
                    } else {
//                      该数据为表格的表头信息，单独存储与集合的首位
                        excelRows.add(getValue(cell));
                    }
                }
                excels.add(excelRows);
            }
            return excels;
        } catch (Exception e) {
            log.error("导入excel错误 : " + e.getMessage());
            return null;
        } finally {
            try {
                if (wb != null) {
                    wb.close();
                }
                if (inp != null) {
                    inp.close();
                }
            } catch (Exception e) {
                log.error("导入excel关流错误 : " + e.getMessage());
            }
        }
    }

由于poi的版本不同，获取excel数据的格式方法也不同，本文所使用的工具方法为下，传入的是数据的单元格的对象。

 public static String getValue(Cell cell) {
        String birthdayVal = null;
        switch (cell.getCellTypeEnum()) {
            case STRING:
                birthdayVal = cell.getRichStringCellValue().getString();
                break;
            case NUMERIC:
                if ("General".equals(cell.getCellStyle().getDataFormatString())) {
//                    此处为double类型的,转成对应的String类型数据
                    birthdayVal = Integer.toString(new Double(cell.getNumericCellValue()).intValue());
                } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
                    birthdayVal = DateToStr(cell.getDateCellValue());
                } else {
                    birthdayVal = DateToStr(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                }
                break;
        }
        return birthdayVal;
    }

    public static String DateToStr(Date date) {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        return format.format(date);
    }

模版数据中，第一行数据，ip_address为空 ，我读取的时候，设置为“null”，如下结果图显示，同时也发现，我读取的数据是一个数组（list）这个时候数据的读取已经完成了。

 以上都是excel数据的读取，这一步之后，我们如何将读取的数据，根据表头的信息赋值到对应的数据库中呢？这就是关键的地方，也是模版的存在的原因。

首先明确两点内容：1:模版的表头信息，就是数据库的字段

                                 2:数据库的字段与实体属性的对应是驼峰命名的方式（user_id--->userId）。

知道以上两点，问题就变成了如何将数据库的值，赋值到实体类的属性。思路：首先将数据库字段（表头）转成实体的属性变量名，然后通过每一行获取的数据，利用反射的原理，通过类属性名，将对应的单元格信息赋值到对象的属性中。最后保存到集合中，如此循环，便可以将excel对应的表数据，逐行赋值到每一个对象中了。最后就是批量入库操作。

下面是代码的实现：

web层面

 @PostMapping(value = "/addDataByModel", produces = "application/json;charset=UTF-8")
    @ApiOperation(value = "通过模版导入对应的资料数据", produces = "application/json;charset=UTF-8")
    public Object addDataByModel(MultipartFile file) {
        //文件名
        try {
            List<CpaDataList> cpaDataLists = null;
            InputStream inputStream = file.getInputStream();
            List<List<Object>> lists = CpaExcelUtil.readExcel(inputStream);
            if (lists != null) {
                cpaDataLists = CpaImportDbUtil.getCpaDataList(lists);
            }
            if (null != cpaDataLists) {
                boolean b = cpaDataListService.saveBatch(cpaDataLists, cpaDataLists.size());
                if (b) {
                    log.info("导入资料的的个数为--->[{}]", cpaDataLists.size());
                    return ReturnResult.success(ReturnMsg.SUCCESS.getCode(), ReturnMsg.SUCCESS.getMsg(), cpaDataLists.size());
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            log.error("通过模版导入资料数据出现异常！");
        }
        return ReturnResult.error(ReturnMsg.ERROR.getCode(), ReturnMsg.ERROR.getMsg());
    }

将读取的excel数据变成对应的集合，集合中是实体对象与数据库字段的对应

 public static List<CpaAccountList> getCpaAccountList(List<List<Object>> excels) throws Exception {
        List<CpaAccountList> cpaAccountLists = new ArrayList<>();
        CpaAccountList cpaAccount;
//       第一行代表的是该表格的数据库字段，需要单独拿出来进行处理
        List<Object> cellList = excels.get(0);
//      将首位数据移除
        excels.remove(0);
        String filedName;
        String value;
//      遍历每一行的数据
        for (List<Object> excel : excels) {
//            遍历每一行的中的每一列数据
            cpaAccount = new CpaAccountList();
            for (int i = 0; i < cellList.size(); i++) {
                filedName = cellList.get(i).toString();
                value = excel.get(i).toString();
                if ("null".equals(value)) {
                    continue;
                }
//               通过反射的方式，给属性值set value
                setValue(cpaAccount, cpaAccount.getClass(), filedName,
                        CpaAccountList.class.getDeclaredField(fieldToProperty(filedName)).getType(), value);
            }
            cpaAccount.setCreateTime(LocalDateTime.now());
            cpaAccount.setAccountStatus(1);
            cpaAccountLists.add(cpaAccount);
        }
        return cpaAccountLists;
    }

    /**
     * @return
     * @author PSY
     * @date 2020/2/25 15:21
     * @接口描述: 将数据库字段转换成类的属性
     * @parmes
     */
    public static String fieldToProperty(String field) {
        if (null == field) {
            return "";
        }
        char[] chars = field.toCharArray();
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < chars.length; i++) {
            char c = chars[i];
            if (c == '_') {
                int j = i + 1;
                if (j < chars.length) {
                    sb.append(StringUtils.upperCase(CharUtils.toString(chars[j])));
                    i++;
                }
            } else {
                sb.append(c);
            }
        }
        return sb.toString();
    }


    /**
     * @return
     * @author PSY
     * @date 2020/2/25 15:21
     * @接口描述: 通过属性，获取对应的set方法，并且设置值
     * @parmes
     */

    public static void setValue(Object obj, Class<?> clazz, String filedName, Class<?> typeClass, Object value) {
        filedName = fieldToProperty(filedName);
        String methodName = "set" + filedName.substring(0, 1).toUpperCase() + filedName.substring(1);
        try {
            Method method = clazz.getDeclaredMethod(methodName, typeClass);
            method.invoke(obj, getClassTypeValue(typeClass, value));
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    private static Object getClassTypeValue(Class<?> typeClass, Object value) {
//        对于String类型的有个强行转换成int类型的操作。
        if (typeClass == LocalDateTime.class && null != value) {

            DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            return LocalDateTime.parse(value.toString(), df);
        }
        if (typeClass == LocalDateTime.class) {
            return null;
        }
        if (typeClass == Integer.class) {
            value = Integer.valueOf(value.toString());
            return value;
        } else if (typeClass == short.class) {
            if (null == value) {
                return 0;
            }
            return value;
        } else if (typeClass == byte.class) {
            if (null == value) {
                return 0;
            }
            return value;
        } else if (typeClass == double.class) {
            if (null == value) {
                return 0;
            }
            return value;
        } else if (typeClass == long.class) {
            if (null == value) {
                return 0;
            }
            return value;
        } else if (typeClass == String.class) {
            if (null == value) {
                return "";
            }
            return value;
        } else if (typeClass == boolean.class) {
            if (null == value) {
                return true;
            }
            return value;
        } else if (typeClass == BigDecimal.class) {
            if (null == value) {
                return new BigDecimal(0);
            }
            return new BigDecimal(value + "");
        } else {
            return typeClass.cast(value);
        }
    }

以上代码关键的就是反射的应用，一一对应实体属性。

数据库中含有500条数据，导入成功。