带你实现java根据表结构动态导入导出Excel

你知道的越多，你不知道的越多
点赞再看，养成习惯
如果您有疑问或者见解，欢迎指教：
企鹅：869192208

前言

最近在甲方爸爸的要(威)求(逼)下，项目经理带来了客户的全新需求，希望能够在原有编辑表单填写的过程中，简化列表数据输入过程，通过下载列表对应的数据表的表结构作为 excel 模板，然后客户只需要根据模板提示填写 excel 内容，再导入到数据库中，最终回显到编辑列表。

通过简化这个需求，抽象出核心功能，那就是根据表结构，动态实现数据模板的导出和数据的导入，趁着清明节三天假期，研究一下这个需求的实现逻辑。

思路

• 使用 POI 作为 excel 的导入导出支撑
• 需要同时支持 oracle 和 mysql 数据库
• 导出：

1. sql 查出数据库指定表的表字段名、数据类型和注释等信息
2. 将表的字段名，数据类型和注释信息分别输出到 excel 前三行，形成模板

• 导入：

3. 先读取字段和字段类型，将其存入 LinkedHashMap
4. 获取一共有多少个字段需要导入，以此为数据循环的截止参数（字段数量和每行的数据个数一致）
5. 获取表中的内容，因为前三列分别是字段名、字段类型和描述，从第4行开始才是 excel 中需要插入数据库的内容
6. 循环获取数据每一行的每一个单元格，根据单元格的数据类型，将数据转换成 String 类型的数据，存储到 List 集合
7. 组装插入数据库的 insert 语句，表名是动态传入的，跟导出模板一致；字段分别是 excel 模板解析出来的首行字段，以",“分隔；插入数据以”?“充当占位符，以”,"分隔
8. 将LinkedHashMap存储的字段对应的数据类型为基础，将数据填充到每一个"?"占位符

代码实现

代码中实现了 mysql 和 oracle 两种数据库的动态导入导出 excel，目前代码运行的是 oracle 数据库的操作，如果需要运行 mysql 数据库的操作，需要在 main 方法里面，将 mysql 的连接参数和获取表结构的代码注释去掉，同时将 oracle 的连接参数和获取表结构的代码注释即可。

建表语句

oracle：

-- ----------------------------
-- Table structure for APPR_EXCEL_DEMO
-- ----------------------------
DROP TABLE "APPR_EXCEL_DEMO";
CREATE TABLE "APPR_EXCEL_DEMO" (
"ID" VARCHAR2(50 BYTE) NOT NULL ,
"USER_NAME" VARCHAR2(255 BYTE) NULL ,
"SEX" NUMBER(2) NULL ,
"BIRTHDAY" DATE NULL ,
"PID" VARCHAR2(50 BYTE) NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;
COMMENT ON TABLE "APPR_EXCEL_DEMO" IS 'excel导入导出测试表';
COMMENT ON COLUMN "APPR_EXCEL_DEMO"."ID" IS '主键';
COMMENT ON COLUMN "APPR_EXCEL_DEMO"."USER_NAME" IS '用户名称';
COMMENT ON COLUMN "APPR_EXCEL_DEMO"."SEX" IS '性别';
COMMENT ON COLUMN "APPR_EXCEL_DEMO"."BIRTHDAY" IS '生日';
COMMENT ON COLUMN "APPR_EXCEL_DEMO"."PID" IS '父id';

-- ----------------------------
-- Indexes structure for table APPR_EXCEL_DEMO
-- ----------------------------

-- ----------------------------
-- Checks structure for table APPR_EXCEL_DEMO
-- ----------------------------
ALTER TABLE "APPR_EXCEL_DEMO" ADD CHECK ("ID" IS NOT NULL);

-- ----------------------------
-- Primary Key structure for table APPR_EXCEL_DEMO
-- ----------------------------
ALTER TABLE "APPR_EXCEL_DEMO" ADD PRIMARY KEY ("ID");

mysql

-- Source Database(数据库名称)：exceldemo
-- ----------------------------
-- Table structure for appr_excel_demo
-- ----------------------------
DROP TABLE IF EXISTS `appr_excel_demo`;
CREATE TABLE `appr_excel_demo` (
  `id` varchar(50) NOT NULL COMMENT '主键',
  `user_name` varchar(255) DEFAULT NULL COMMENT '用户名-字符串',
  `sex` int(2) DEFAULT NULL COMMENT '性别',
  `birthday` datetime DEFAULT NULL COMMENT '生日',
  `pid` varchar(50) DEFAULT NULL COMMENT '父id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

package excel;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * <h2>根据表结构动态导入导出Excel</h2>
 *
 * @author xymy
 * @date 2021-01-05 19:21
 */
public class ExcelDemo {
    //oracle查询数据库表结构sql
    private static final String getTableColumnsOracle = "SELECT A.DATA_TYPE \"data_type\", B.COLUMN_NAME \"column_name\", substr(B.COMMENTS,0,decode(instr(B.COMMENTS,'@'),NULL,255,0,255,instr(B.COMMENTS,'@'))-1) \"column_comment\"\n" +
            "\t\t  FROM USER_TAB_COLUMNS A\n" +
            "\t\t  LEFT JOIN USER_COL_COMMENTS B\n" +
            "\t\t    ON B.TABLE_NAME = A.TABLE_NAME\n" +
            "\t\t   AND A.COLUMN_NAME = B.COLUMN_NAME\n" +
            "\t\t WHERE A.TABLE_NAME = ?\n" +
            "order by b.COLUMN_NAME";
    //mysql查询数据库表结构sql
    //private static final String getTableColumnsMysql = "select column_name,column_comment,data_type,column_type,is_nullable from information_schema.columns where table_name=? and table_schema=? order by ORDINAL_POSITION asc";
    private static final String getTableColumnsMysql = "SELECT\n" +
            "\ta.column_name,\n" +
            "\ta.column_comment,\n" +
            "\ta.data_type,\n" +
            "\ta.column_type,\n" +
            "\ta.is_nullable,\n" +
            "\tCASE\n" +
            "WHEN b.COLUMN_NAME = a.column_name THEN\n" +
            "\t'true'\n" +
            "ELSE\n" +
            "\t'false'\n" +
            "END is_pk\n" +
            "FROM\n" +
            "\tinformation_schema. COLUMNS a\n" +
            "JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE b ON a.table_name = b.table_name\n" +
            "WHERE\n" +
            "\ta.table_name = ?\n" +
            "AND a.table_schema = ?\n" +
            "ORDER BY\n" +
            "\ta.ORDINAL_POSITION ASC";

    //定义excel操作的根目录
    private static String PATH="D:/excel/";
    private static DruidDataSource dataSource=null;

    /**
     * 构造函数完成数据库的连接和连接对象的生成
     * @throws Exception
     */
    public ExcelDemo(){

    }

    public void getDbConnect(String url, String driverClassName, String username, String password) throws Exception  {
        try{
            if(dataSource==null){
                dataSource=new DruidDataSource();
                //设置连接参数
                dataSource.setUrl(url);
                dataSource.setDriverClassName(driverClassName);
                dataSource.setUsername(username);
                dataSource.setPassword(password);
                //配置初始化大小、最小、最大
                dataSource.setInitialSize(1);
                dataSource.setMinIdle(1);
                dataSource.setMaxActive(20);
                //连接泄漏监测
                dataSource.setRemoveAbandoned(true);
                dataSource.setRemoveAbandonedTimeout(30);
                //配置获取连接等待超时的时间
                dataSource.setMaxWait(20000);
                //配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
                dataSource.setTimeBetweenEvictionRunsMillis(20000);
                //防止过期
                dataSource.setValidationQuery("SELECT 'x' from dual");
                dataSource.setTestWhileIdle(true);
                dataSource.setTestOnBorrow(true);
            }
        }catch(Exception e){
            throw e;
        }
    }

    /**
     * 取得已经构造生成的数据库连接
     * @return 返回数据库连接对象
     * @throws Exception
     */
    public Connection getConnect(String url, String driverClassName, String username, String password) throws Exception{
        Connection con=null;
        try {
            getDbConnect(url, driverClassName, username, password);
            con=dataSource.getConnection();
        } catch (Exception e) {
            throw e;
        }
        return con;
    }


    public static void main(String[] args) throws Exception {
        //测试的表
        String tableName = "appr_excel_demo";
        //mysql测试数据库
        String tableSchema = "exceldemo";

        //配置mysql连接参数
        /*String url = "jdbc:mysql://127.0.0.1:3306/exceldemo?useUnicode=true&characterEncoding=utf-8";
        String driverClassName = "com.mysql.jdbc.Driver";
        String username = "root";
        String password = "123456";*/
        //配置oracle连接参数
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:oanet";
        String driverClassName = "oracle.jdbc.driver.OracleDriver";
        String username = "root";
        String password = "123456";

        List<Map<String, String>> list = new ArrayList<Map<String, String>>();
        ExcelDemo dbConnect = new ExcelDemo();
        Connection connection = dbConnect.getConnect(url, driverClassName, username, password);
        //mysql获取数据库表结构
        /*PreparedStatement ps = connection.prepareStatement(getTableColumnsMysql);
        ps.setString(1, tableName);
        ps.setString(2, tableSchema);*/
        //oracle获取数据库表结构
        PreparedStatement ps = connection.prepareStatement(getTableColumnsOracle);
        ps.setString(1, tableName);
        ResultSet rs = ps.executeQuery();
        while(rs.next()){
            System.out.println(rs.getString("data_type") + " | " + rs.getString("column_name") + " | " + rs.getString("column_comment"));
            Map<String, String> map = new HashMap<String, String>();
            map.put("columnName", rs.getString("column_name"));
            map.put("dataType", rs.getString("data_type"));
            map.put("desc", rs.getString("column_comment"));
            list.add(map);
        }

        list.stream().forEach(l -> {
            System.out.println(l.get("columnName") + "," + l.get("dataType") + "," + l.get("desc"));
        });
        //将库表的结构导出到excel形成模板
        exportExcel(list, "testWrite07excel.xlsx");

        //将excel的数据导入到数据库
        importExcel(connection, tableName, "testRead07excel.xlsx");
    }

    /**
     * 导入excel
     * @throws Exception
     */
    private static void importExcel(Connection connection, String tableName, String fileName) throws Exception {
        //获取文件
        FileInputStream fileInputStream = new FileInputStream(PATH + fileName);

        //获取工作薄
        Workbook workbook = new XSSFWorkbook(fileInputStream);
        //得到表
        Sheet sheet = workbook.getSheetAt(0);
        //所有字段和类型的集合
        LinkedHashMap<String, String> columns = new LinkedHashMap<>();
        //记录一行有多少单元格
        int cellCount = 0;
        //获取0-字段名称、1-字段类型、2-字段描述
        Row rowColumnName = sheet.getRow(0);
        Row rowDataType = sheet.getRow(1);
        if (rowColumnName != null && rowDataType != null){
            //获取一行中有多少个单元格
            cellCount = rowColumnName.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                //获取单元
                Cell cellColumnName = rowColumnName.getCell(cellNum);
                Cell cellDataType = rowDataType.getCell(cellNum);
                if (cellColumnName != null){
                    //获取类型
                    String cellColumnNameValue = cellColumnName.getStringCellValue();
                    String cellDataTypeValue = cellDataType.getStringCellValue();
                    columns.put(cellColumnNameValue, cellDataTypeValue);
                    System.out.print(cellColumnNameValue+","+ cellDataTypeValue + " | ");
                }
            }
            System.out.println();
        }

        //获取表中的内容,从第4行开始是excel中需要插入数据库的内容
        int rowCount = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 3; rowNum < rowCount; rowNum++) {
            //一行记录所有值的集合
            List<String> values = new ArrayList<>();
            Row rowData = sheet.getRow(rowNum);
            if (rowData != null){
                //读取列
                for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                    System.out.print("【" + (rowNum+1) + "-" + (cellNum+1) + "】");

                    Cell cell = rowData.getCell(cellNum);
                    //匹配列的数据类型
                    if (cell != null){
                        int cellType = cell.getCellType();
                        String cellValue = "";

                        switch (cellType){
                            case HSSFCell.CELL_TYPE_STRING://字符串
                                System.out.print("【STRING】");
                                cellValue = cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN://布尔值
                                System.out.print("【BOOLEAN】");
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC://数字类型
                                System.out.print("【NUMERIC】");

                                if (HSSFDateUtil.isCellDateFormatted(cell)){//日期
                                    System.out.print("【日期】");
                                    Date date = cell.getDateCellValue();
                                    SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                    cellValue = formatter.format(date);
                                }else{
                                    // 不是日期格式，则防止当数字过长时以科学计数法显示
                                    System.out.print("【转换成字符串】");
                                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                    cellValue = cell.toString();
                                }
                                break;
                            case HSSFCell.CELL_TYPE_BLANK://空
                                System.out.print("【BLANK】");
                                break;
                            case Cell.CELL_TYPE_ERROR:
                                System.out.print("【数据类型错误】");
                                break;
                        }
                        System.out.println(cellValue);
                        values.add(cellValue);
                    }
                }
            }
            //将数据插入数据库
            //组装所有的插入字段
            String inserts = "";
            //组装所有的插入值的占位符
            String v = "";
            List<String> types = new ArrayList<>();
            for (String s : columns.keySet()) {
                System.out.println("key=" + s + " value=" + columns.get(s));
                inserts = inserts + s + ",";
                v = v + "?,";
                types.add(columns.get(s));
            }

            String insertSql = "insert into "+tableName+"("+inserts.substring(0, inserts.lastIndexOf(","))+")values("+v.substring(0, v.lastIndexOf(","))+")";
            System.out.println(insertSql);
            PreparedStatement ps = connection.prepareStatement(insertSql);
            //构造真正的需要插入的值
            for (int i = 1; i<= types.size(); i++){
                String type = types.get(i-1);
                String value = values.get(i-1);
                System.out.println(type + "-" + value);
                if (type.contains("varchar")){//字符串
                    ps.setString(i, value);
                }else if (type.contains("int") || type.contains("number")){//数字
                    ps.setInt(i, Integer.parseInt(value));
                }else if (type.contains("date")){//日期
                    if (!value.isEmpty()){
                        ps.setTimestamp(i, new Timestamp(
                                new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(value).getTime()));
                    }else {
                        ps.setTimestamp(i, null);
                    }
                }
            }
            int execute = ps.executeUpdate();
            System.out.println(execute);
        }
        fileInputStream.close();
    }

    /**
     * 导出excel
     * @throws Exception
     */
    private static void exportExcel(List<Map<String, String>> list, String fileName) throws Exception {
        //创建簿
        Workbook workbook = new XSSFWorkbook();
        //创建表
        Sheet sheet = workbook.createSheet();
        //写数据
        for (int rowNum = 0; rowNum < 3; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < list.size(); cellNum++) {
                Cell cell = row.createCell(cellNum);
                if (rowNum == 0){
                    cell.setCellValue(list.get(cellNum).get("columnName"));
                }else if (rowNum == 1){
                    cell.setCellValue(list.get(cellNum).get("dataType"));
                }else if (rowNum == 2){
                    cell.setCellValue(list.get(cellNum).get("desc"));
                }
            }
        }
        System.out.println("导入模板完成");
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + fileName);
        workbook.write(fileOutputStream);
        fileOutputStream.close();
    }
}