01-easyExcel应用

需求:利用EXCEL模板生成建/改库的SQL语句。

1.EXCEL：模板

模板

2.导入所需依赖

    <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

3.实现：
Excel模板对应的实体类：

package com.myspring.temp;

import com.alibaba.excel.annotation.ExcelProperty;

public class Excel {
    @ExcelProperty(value = "表名", index = 0) // 定义表头名称和位置,0代表第一列
    private String tableName;
    @ExcelProperty(value = "英文表名", index = 1)
    private String tableNameEN;
    @ExcelProperty(value = "字段名", index = 2)
    private String  fieldName;
    @ExcelProperty(value = "英文字段名", index = 3)
    private String fieldNameEN;
    @ExcelProperty(value = "字段类型", index = 4)
    private String fieldType;

    @Override
    public String toString() {
        return "Excel{" +
                "tableName='" + tableName + '\'' +
                ", tableNameEN='" + tableNameEN + '\'' +
                ", fieldName='" + fieldName + '\'' +
                ", fieldNameEN='" + fieldNameEN + '\'' +
                ", fieldType='" + fieldType + '\'' +
                '}';
    }

    public String getTableName() {
        return tableName;
    }

    public void setTableName(String tableName) {
        this.tableName = tableName;
    }

    public String getTableNameEN() {
        return tableNameEN;
    }

    public void setTableNameEN(String tableNameEN) {
        this.tableNameEN = tableNameEN;
    }

    public String getFieldName() {
        return fieldName;
    }

    public void setFieldName(String fieldName) {
        this.fieldName = fieldName;
    }

    public String getFieldNameEN() {
        return fieldNameEN;
    }

    public void setFieldNameEN(String fieldNameEN) {
        this.fieldNameEN = fieldNameEN;
    }

    public String getFieldType() {
        return fieldType;
    }

    public void setFieldType(String fieldType) {
        this.fieldType = fieldType;
    }
}

ExcelListener:

package com.myspring.temp;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Map;

//创建读取excel监听器
public class ExcelListener extends AnalysisEventListener<Excel> {
    //创建list集合封装最终的数据
    List<Excel> list = new ArrayList<Excel>();

    //一行一行去读取excle内容
    @Override
    public void invoke(Excel excel, AnalysisContext analysisContext) {

        //System.out.println("***"+excel.toString());
        list.add(excel);

    }

    //读取excel表头信息
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println("表头信息："+headMap);
    }

    //读取完成后执行
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        //所有表名
        HashSet<String> sets = new HashSet<String>();
        for (Excel ex:list) {
            sets.add(ex.getTableNameEN());
        }

        //根据表名循环总EXCEL记录，将表名相同行加入childList
        for (String tableName:sets) {
            List<Excel> childList = new ArrayList<Excel>();
            for (Excel ex:list) {
                if(tableName.equals(ex.getTableNameEN())){
                    childList.add(ex);
                }
            }
            Boolean flag = false;
            StringBuffer sqlStr = new StringBuffer();
            String  sqlCommet = null ;
            for (Excel ex:childList){

               if("ID".equals(ex.getFieldNameEN())){
                  flag = true;
                }
            }
            if(flag){
                sqlStr.append("CREATE TABLE YKYPLATFORM.").append(tableName).append(" (");
                for (Excel ex:childList){
                    //拼创建表字符串
                    if("ID".equals(ex.getFieldNameEN())){
                         sqlStr.append(ex.getFieldNameEN()).append(" ").append(ex.getFieldType()).append(" NOT NULL,");
                     }else {
                         sqlStr.append(ex.getFieldNameEN()).append(" ").append(ex.getFieldType()+",");
                     }
                                    }
                sqlStr.delete(sqlStr.length()-1,sqlStr.length());
                sqlStr.append(");");
                System.out.println(sqlStr);

            }else {
                for (Excel ex : childList) {
                    //拼更新表字符串
                    String  sqlAlter = "ALTER TABLE YKYPLATFORM."+tableName+" ADD "+ex.getFieldNameEN()+" "+ex.getFieldType()+";";
                    System.out.println(sqlAlter);
                    }
            }
            for (Excel ex : childList) {
                //拼注释
                sqlCommet ="COMMENT ON COLUMN  YKYPLATFORM."+tableName+"."+ex.getFieldNameEN()+" IS '"+ex.getFieldName()+"';";
                System.out.println(sqlCommet);
            }
        }
       
    }
}

客户端：

package com.myspring.temp;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.metadata.ReadSheet;

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.InputStream;

public class client {

    public static void main(String[] args) throws Exception {
//        // 写法1：
//        String fileName = "F:\\01.xlsx";
//        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
//        EasyExcel.read(fileName, Excel.class, new ExcelListener()).sheet().doRead();


        // 写法2：
        InputStream in = new BufferedInputStream(new FileInputStream("D:\\ABO-20190916\\MyCodes\\CDR覆盖WS数据规范\\VB源.xlsx"));
        ExcelReader excelReader = EasyExcel.read(in, Excel.class, new ExcelListener()).build();
        ReadSheet readSheet = EasyExcel.readSheet(4).build();
        excelReader.read(readSheet);
        // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
        excelReader.finish();
    }
}

结果：

image.png