将Excel导出为SQL语句

2021-09-09 23:51:12

需求说明：公司做项目前进行需求分析，确定表结构后需要建表，如果照着表格去敲，那就太麻烦了，所以想到了自动生成SQL语句。

思路大概就是：解析Excel，拼接SQL语句，输出SQL文件。

第三方jar包：POI（解析Excel）

先建立一个maven项目。

pom依赖：

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0"

         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

    <modelVersion>4.0.0</modelVersion>

    <groupId>com.transition</groupId>

    <artifactId>excel-to-sql</artifactId>

    <version>1.0-SNAPSHOT</version>

    <dependencies>

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->

        <dependency>

            <groupId>org.apache.poi</groupId>

            <artifactId>poi</artifactId>

            <version>3.17</version>

        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->

        <dependency>

            <groupId>org.apache.poi</groupId>

            <artifactId>poi-ooxml</artifactId>

            <version>3.17</version>

        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->

        <dependency>

            <groupId>org.apache.poi</groupId>

            <artifactId>poi-ooxml-schemas</artifactId>

            <version>3.17</version>

        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.xmlbeans/xmlbeans -->

        <dependency>

            <groupId>org.apache.xmlbeans</groupId>

            <artifactId>xmlbeans</artifactId>

            <version>2.6.0</version>

        </dependency>

        <!-- https://mvnrepository.com/artifact/dom4j/dom4j -->

        <dependency>

            <groupId>dom4j</groupId>

            <artifactId>dom4j</artifactId>

            <version>1.6.1</version>

        </dependency>

    </dependencies>

    <build>

        <plugins>

            <!-- 编译插件 -->

            <plugin>

                <groupId>org.apache.maven.plugins</groupId>

                <artifactId>maven-compiler-plugin</artifactId>

                <version>3.7.0</version>

                <configuration>

                    <source>1.8</source>

                    <target>1.8</target>

                </configuration>

            </plugin>

        </plugins>

    </build>

</project>

目录结构

将Excel导出为SQL语句

Keys.Java

package com.transition.core.common;

public class Keys {

    public static final String TABLE_PRE = "DROP TABLE IF EXISTS ";

    public static final String TABLE_SUFF = "ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=";

    public static final String C_T = "CREATE TABLE ";

    public static final String N_N = " NOT NULL ";

    public static final String A_I = "AUTO_INCREMENT";

    public static final String P_K = "PRIMARY KEY ";

    public static final String F_K = " FOREIGN KEY ";

    public static final String COMMENT = " COMMENT ";

    public static final String DEFAULT = " DEFAULT ";

}

DataTable.Java

package com.transition.core.entity;

import java.util.ArrayList;

import java.util.List;

/**

 * 数据库表

 */

public class DataTable {

    private String tableName;   // 数据库表名

    private String comment;     //数据库注释

    private List<TableField> fields = new ArrayList<>();

    public DataTable() {

    }

    public DataTable(String tableName, String comment) {

        this.tableName = tableName;

        this.comment = comment;

    }

    public String getTableName() {

        return tableName;

    }

    public void setTableName(String tableName) {

        this.tableName = tableName;

    }

    public String getComment() {

        return comment;

    }

    public void setComment(String comment) {

        this.comment = comment;

    }

    public List<TableField> getFields() {

        return fields;

    }

    public void setFields(List<TableField> fields) {

        this.fields = fields;

    }

    @Override

    public String toString() {

        return "DataTable{" +

                "tableName='" + tableName + '\'' +

                ", comment='" + comment + '\'' +

                ", fields=" + fields +

                '}';

    }

}

TableField.Java

package com.transition.core.entity;

/**

 * 表字段

 */

public class TableField {

    private String fieldName;   // 字段名

    private String fieldType;   // 字段类型

    private String isNull;      // 是否为空

    private String defaultValue;// 默认值

    private String comment;     // 注释

    public TableField() {

    }

    public TableField(String fieldName, String fieldType, String isNull, String defaultValue, String comment) {

        this.fieldName = fieldName;

        this.fieldType = fieldType;

        this.isNull = isNull;

        this.defaultValue = defaultValue;

        this.comment = comment;

    }

    public String getFieldName() {

        return fieldName;

    }

    public void setFieldName(String fieldName) {

        this.fieldName = fieldName;

    }

    public String getFieldType() {

        return fieldType;

    }

    public void setFieldType(String fieldType) {

        this.fieldType = fieldType;

    }

    public String getIsNull() {

        return isNull;

    }

    public void setIsNull(String isNull) {

        this.isNull = isNull;

    }

    public String getDefaultValue() {

        return defaultValue;

    }

    public void setDefaultValue(String defaultValue) {

        this.defaultValue = defaultValue;

    }

    public String getComment() {

        return comment;

    }

    public void setComment(String comment) {

        this.comment = comment;

    }

    @Override

    public String toString() {

        return "TableField{" +

                "fieldName='" + fieldName + '\'' +

                ", fieldType='" + fieldType + '\'' +

                ", isNull='" + isNull + '\'' +

                ", defaultValue='" + defaultValue + '\'' +

                ", comment='" + comment + '\'' +

                '}';

    }

}

Core

package com.transition.core;

import com.transition.core.common.Keys;

import com.transition.core.entity.DataTable;

import com.transition.core.entity.TableField;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.CellType;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;

import java.util.ArrayList;

import java.util.List;

public class Core {

    private static int tableNum = 0;

    private static int sentry = -1;  // 哨兵

    public static List<DataTable> readXlsx(String path){

        List<DataTable> list = new ArrayList<>();

        System.out.println("读取...");

        InputStream in = null;

        try {

            in = new FileInputStream(path);

            XSSFWorkbook xss = new XSSFWorkbook(in);

            for(int i = 0; i < xss.getNumberOfSheets(); i++){

                XSSFSheet sheet = xss.getSheetAt(i);

                if(sheet == null)

                    continue;

                for(int r = 0; r <= sheet.getLastRowNum(); r++){

                    XSSFRow row = sheet.getRow(r);

                    if(row != null){

                        // 如果哨兵等于0，代表这一行是不需要读取的。continue之后自减1

                        if (sentry-- == 0){

                            continue;

                        }

                        /*

                            _NONE(-1),

                            NUMERIC(0),

                            STRING(1),

                            FORMULA(2),

                            BLANK(3),

                            BOOLEAN(4),

                            ERROR(5);

                         */

                        CellType cellType2 = row.getCell(2).getCellTypeEnum();

                        CellType cellType3 = row.getCell(3).getCellTypeEnum();

                        CellType cellType4 = row.getCell(4).getCellTypeEnum();

                        if (cellType2 == CellType.BLANK && cellType3 == CellType.BLANK && cellType4 == CellType.BLANK){

                            ++tableNum;

                            sentry = 0;

                            String name = row.getCell(0).getStringCellValue().toLowerCase();

                            String comment = row.getCell(1).getStringCellValue();

                            list.add(new DataTable(name, comment));

                            System.out.println("----------------------Table-------------------");

                            System.out.println("表名：" + name + ",注释：" + comment);

                            continue;

                        }

                        if(cellType3 == CellType.NUMERIC)

                            list.get(list.size()-1).getFields().add(new TableField(row.getCell(0).getStringCellValue(),

                                    row.getCell(1).getStringCellValue(),

                                    row.getCell(2).getStringCellValue(),

                                    Math.round(row.getCell(3).getNumericCellValue())+"",

                                    row.getCell(4).getStringCellValue()));

                        else

                            list.get(list.size()-1).getFields().add(new TableField(row.getCell(0).getStringCellValue(),

                                    row.getCell(1).getStringCellValue(),

                                    row.getCell(2).getStringCellValue(),

                                    row.getCell(3).getStringCellValue(),

                                    row.getCell(4).getStringCellValue()));

                    }

                }

            }

            in.close();

        } catch (FileNotFoundException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        } catch (IOException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

        return list;

    }

    public static void exportToSQL(List<DataTable> list, String sqlPath) throws IOException {

        final List<String> sql = new ArrayList<>();

        list.forEach(e -> {

            sentry = 1;

            StringBuilder sb = new StringBuilder();

            sb.append(Keys.TABLE_PRE + "`" + e.getTableName() + "`;\n");

            sb.append(Keys.C_T + "`" + e.getTableName() + "` (\n");

            e.getFields().forEach(c -> {

                // 只有主键有自增

                if (sentry == 1){

                    sb.append("`" + c.getFieldName().toLowerCase() + "` " + c.getFieldType().toLowerCase() + Keys.N_N + Keys.A_I + Keys.COMMENT + "'" + c.getComment() + "',\n");

                    sentry = 0;

                }else {

                    // 如果无默认值

                    if (c.getDefaultValue() == null || c.getDefaultValue().equals("")){

                        // 如果不为空，或者无标志，一律按照不为空处理

                        if ("N".equals(c.getIsNull()) || "".equals(c.getIsNull()) || c.getIsNull() == null)

                            sb.append("`" + c.getFieldName().toLowerCase() + "` " + c.getFieldType().toLowerCase() + Keys.N_N + Keys.COMMENT + "'" + c.getComment() + "',\n");

                        else

                            sb.append("`" + c.getFieldName().toLowerCase() + "` " + c.getFieldType().toLowerCase() + Keys.COMMENT + "'" + c.getComment() + "',\n");

                    } else{

                        // 如果不为空，或者无标志，一律按照不为空处理

                        if ("N".equals(c.getIsNull()) || "".equals(c.getIsNull()) || c.getIsNull() == null)

                            sb.append("`" + c.getFieldName().toLowerCase() + "` " + c.getFieldType().toLowerCase() + Keys.N_N + Keys.DEFAULT + "'" + c.getDefaultValue() + "'" + Keys.COMMENT + "'" + c.getComment() + "',\n");

                        else

                            sb.append("`" + c.getFieldName().toLowerCase() + "` " + c.getFieldType().toLowerCase() + Keys.DEFAULT + "'" + c.getDefaultValue() + "'" + Keys.COMMENT + "'" + c.getComment() + "',\n");

                    }

                }

            });

            sb.append(Keys.P_K + "(`" + e.getFields().get(0).getFieldName().toLowerCase() + "`)\n" +

                    ") " + Keys.TABLE_SUFF + "'" + e.getComment() + "';\n\n");

            System.out.println(sb.toString());

            sql.add(sb.toString());

        });

        File file = new File(sqlPath);

        FileOutputStream out = new FileOutputStream(file);

        sql.forEach(s -> {

            try {

                out.write(s.getBytes());

            } catch (IOException e) {

                e.printStackTrace();

            }

        });

        out.close();

    }

    public static int getTableNum() {

        return tableNum;

    }

}

RunApplication

package com.transition.core;

import com.transition.core.entity.DataTable;

import java.io.IOException;

import java.util.List;

public class RunApplication {

    public static void main(String[] args) throws IOException {

        List<DataTable> tables = Core.readXlsx("table2.xlsx");

        // tables.forEach(e -> System.out.println(e));

        Core.exportToSQL(tables,"test.sql");

        System.out.println("TableNumber : " + Core.getTableNum());

    }

}

我要读取table2.xlsx，输出到test.sql

将Excel导出为SQL语句

将Excel导出为SQL语句

运行，输出结果：

将Excel导出为SQL语句

查看sql文件

将Excel导出为SQL语句

在Navicat里面运行这个sql文件

将Excel导出为SQL语句

将Excel导出为SQL语句

将Excel导出为SQL语句

可以看到，字段名全部是小写，类型，长度，字段注释都正确设置，是否为空也符合要求。

不过有个事情：如果你要添加外键依赖，触发器之类的要自己手动去配置。另外，这个代码有点特定为这种格式的表格，如果你不是这种结果的表格，很可能不成功。

如果你不会改就叫我好了

不过，如果你有很多表的话，帮你生成已经节省很多时间了，还要啥自行车啊

上一篇：Github+Hexo,搭建专有博客


下一篇：css初始