最最最厉害的sql导出分割工具类

package com.bootdo.gi;


import java.io.*;
import java.lang.reflect.Array;
import java.util.*;

import javax.xml.parsers.ParserConfigurationException;


import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.util.SAXHelper;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;


/**
 * Created by ${伊佩} on 2019/7/24.
 */
public class ExcelPoiUtils {

    public static List<List<String>> tableslist = new ArrayList<>();

    public static ArrayList<String> stringlist = new ArrayList<>();

    public static HashMap<String, TableEntity> tabledata = new HashMap<>();


    /**
     * 设置属性
     */
    private String filename;
    private SheetContentsHandler handler;

    public ExcelPoiUtils(String filename) {
        this.filename = filename;
    }

    public ExcelPoiUtils setHandler(SheetContentsHandler handler) {
        this.handler = handler;
        return this;
    }

    /**
     * 解析
     */
    public void parse() {
        OPCPackage pkg = null;
        InputStream sheetInputStream = null;

        try {
            pkg = OPCPackage.open(filename, PackageAccess.READ);
            XSSFReader xssfReader = new XSSFReader(pkg);

        StylesTable styles = xssfReader.getStylesTable();
        ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(pkg);
        sheetInputStream = xssfReader.getSheetsData().next();

        processSheet(styles, strings, sheetInputStream);
    } catch (Exception e) {
        throw new RuntimeException(e.getMessage(), e);
    } finally {
        if (sheetInputStream != null) {
            try {
                sheetInputStream.close();
            } catch (IOException e) {
                throw new RuntimeException(e.getMessage(), e);
            }
        }
        if (pkg != null) {
            try {
                pkg.close();
            } catch (IOException e) {
                throw new RuntimeException(e.getMessage(), e);
            }
        }
    }
}

/**
 * 流程表
 *
 * @param styles
 * @param strings
 * @param sheetInputStream
 * @throws SAXException
 * @throws ParserConfigurationException
 * @throws IOException
 */
private void processSheet(StylesTable styles, ReadOnlySharedStringsTable strings, InputStream sheetInputStream) throws SAXException, ParserConfigurationException, IOException {
    XMLReader sheetParser = SAXHelper.newXMLReader();

    if (handler != null) {
        sheetParser.setContentHandler(new XSSFSheetXMLHandler(styles, strings, handler, false));
    } else {
        sheetParser.setContentHandler(new XSSFSheetXMLHandler(styles, strings, new SimpleSheetContentsHandler(), false));
    }

    sheetParser.parse(new InputSource(sheetInputStream));
}

/**
 * 简单的表格内容处理程序
 */
public static class SimpleSheetContentsHandler implements XSSFSheetXMLHandler.SheetContentsHandler {
    protected List<String> row = new LinkedList<>();

    @Override
    public void startRow(int rowNum) {
        row.clear();
    }

    @Override
    public void endRow(int rowNum) {
        System.err.println(rowNum + " : " + row);
    }

    @Override
    public void cell(String cellReference, String formattedValue, XSSFComment comment) {
        row.add(formattedValue);
    }

    @Override
    public void headerFooter(String text, boolean isHeader, String tagName) {

    }
}

/**
 * 测试方法main
 *
 * @param args
 * @throws Throwable
 */
public static void main(String[] args) throws Throwable {
    long start = System.currentTimeMillis();

    List<String> table = new ArrayList<>();
    new ExcelPoiUtils("D:\\301项目正式导入模板(批量).xlsx").setHandler(new SimpleSheetContentsHandler() {

        private List<String> fields;

        @Override
        public void endRow(int rowNum) {
            if (rowNum == 0) {
                // 第一行中文描述忽略
            } else if (rowNum == 1) {
                // 第二行字段名
                fields = row;
                DataInsertFielNmae(fields);
            } else {
                // 数据
                table.add(row.toString());
                String substring = row.toString().substring(1, row.toString().lastIndexOf("]"));
                String[] split = substring.split(", ");
                tableslist.add(Arrays.asList(split));
            }

        }
    }).parse();
    //调用添加数据方法
    DataInsertData();

}


/**
 * 字段解析到Map
 *
 * @param fieldList
 */
public static void DataInsertFielNmae(List<String> fieldList) {

    //设置表头
    for (int i = 0; i < fieldList.size(); i++) {
        //修正问题字段
        String s = fieldList.get(i).replaceAll("name:", "");
        //通过:切割字段
        String[] TableandColumnvaluesplit = s.split(":");
        TableEntity tableEntity = new TableEntity();

        if(TableandColumnvaluesplit[0].equals("sys_patient_info")){

        //判断Key值是否存在
        if (tabledata.get(TableandColumnvaluesplit[0]) != null) {

            //如果存在则添加值
            tableEntity.setColumnvalue(null);
            String column = tabledata.get(TableandColumnvaluesplit[0]).getColumn();
            String columnname = column + "," + TableandColumnvaluesplit[1];
            tabledata.get(TableandColumnvaluesplit[0]).setColumn(columnname);
            //生成当前字段的的数据的索引
            String columnnum = tabledata.get(TableandColumnvaluesplit[0]).getColumnnum();
            String columnindex = columnnum + i + ",";
            tabledata.get(TableandColumnvaluesplit[0]).setColumnnum(columnindex);
        } else {
            stringlist.add(TableandColumnvaluesplit[0]);
            tableEntity.setColumnvalue(null);
            tableEntity.setColumn(TableandColumnvaluesplit[1]);
            tabledata.put(TableandColumnvaluesplit[0], tableEntity);
            //获取挡墙字段在数据中的索引
            tabledata.get(TableandColumnvaluesplit[0]).setColumnnum(i + ",");
        }
    }
}
}
/**
 * 根据字段添加数据到Map
 */
public static void DataInsertData() {

    for (int i = 0; i < stringlist.size(); i++) {

        TableEntity tableEntity = tabledata.get(stringlist.get(i));
        String columnnum = tableEntity.getColumnnum();
        String[] split = columnnum.split(",");
        ArrayList<String> tablelins = new ArrayList<>();
        for (int j = 0; j < tableslist.size(); j++) {
            ArrayList<String> arrlist = new ArrayList<>();
            for (int k = 0; k < split.length; k++) {
                String s = tableslist.get(j).get(Integer.parseInt(split[k]));
                arrlist.add(s);
            }
            tablelins.add(arrlist.toString());
        }
        tabledata.get(stringlist.get(i)).setColumnvalue(tablelins);

    }
    SqlExprot();
}

/**
 * Sql文件转出
 */

public static void SqlExprot() {

    try {
        // 相对路径，如果没有则要建立一个新的output.txt文件
        File writeName = new File("D:\\一个大的sql文件.sql");
        writeName.createNewFile(); // 创建新文件,有同名的文件的话直接覆盖

        try (FileWriter writer = new FileWriter(writeName);
             BufferedWriter out = new BufferedWriter(writer)
        ) {
            // \r\n即为换行
            // \r\n即为换行
            out.write("SET NAMES utf8mb4;\r\n");
            out.write("SET FOREIGN_KEY_CHECKS = 0;\r\n");

            tabledata.forEach((x, y) -> {
                try {
                    out.write("\r\n");
                    out.write("-- ----------------------------\r\n" +
                            "-- Table structure for " + x + "\r\n" +
                            "-- ----------------------------");
                    out.write("DROP TABLE IF EXISTS `" + x + "`;\r\n");
                    out.write("CREATE TABLE `" + x + "`  (\r\n");

                    String[] Columnname = y.getColumn().split(",");
                    for (int i = 0; i < Columnname.length; i++) {

                        if (i != Columnname.length - 1) {
                            out.write("`" + Columnname[i] + "` varchar(155) CHARACTER SET utf8 COLLATE utf8_croatian_ci  COMMENT '无注释',\r\n");
                        } else {
                            out.write("`" + Columnname[i] + "` varchar(155) CHARACTER SET utf8 COLLATE utf8_croatian_ci  COMMENT '无注释'\r\n");
                        }
                    }
                    out.write(") ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_croatian_ci ROW_FORMAT = Dynamic;\r\n");

                    out.write("-- ----------------------------\r\n" +
                            "-- Records of `" + x + "`\r\n" +
                            "-- ----------------------------\r\n");
                    List<String> columnvalue = y.getColumnvalue();
                    for (int i = 0; i < columnvalue.size(); i++) {

                        String substring = columnvalue.get(i).substring(1, columnvalue.get(i).lastIndexOf("]"));
                        String[] split = substring.split(", ");
                        String valuessub = "";
                        for (int j = 0; j < split.length; j++) {
                            valuessub += "'" + split[j] + "',";
                        }
                        String values = valuessub.substring(1, valuessub.lastIndexOf(","));
                        out.write("INSERT INTO `" + x + "` VALUES ('" + values + ");\r\n");
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
            });
            // 把缓存区内容压入文件
            out.flush();
        }
        System.err.println("共计：" + stringlist.size() + "个数据表");
        System.out.println("写入成功 文件所在位置：" + writeName);
    } catch (IOException e) {
        e.printStackTrace();
        System.out.println("写入失败");
    }
}
}

class TableEntity {

/**
 * 字段名
 */
private String column;

/**
 * 数据
 */
private List<String> columnvalue = new ArrayList<>();

/***
 * 字段在excel中的列数
 */
private String columnnum;

@Override
public String toString() {
    return "TableEntity{" +
            "column='" + column + '\'' +
            ", columnvalue=" + columnvalue +
            ", columnnum='" + columnnum + '\'' +
            '}';
}

public TableEntity(String column, List<String> columnvalue, String columnnum) {
    this.column = column;
    this.columnvalue = columnvalue;
    this.columnnum = columnnum;
}

public String getColumnnum() {
    return columnnum;
}

public void setColumnnum(String columnnum) {
    this.columnnum = columnnum;
}

public TableEntity() {
}

public String getColumn() {
    return column;
}

public void setColumn(String column) {
    this.column = column;
}

public List<String> getColumnvalue() {
    return columnvalue;
}

public void setColumnvalue(List<String> columnvalue) {
    this.columnvalue = columnvalue;
}

public TableEntity(String column, List<String> columnvalue) {
    this.column = column;
    this.columnvalue = columnvalue;
}
}