Excel 转换 Oracle 建表sql

package com.stream.excel;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.compress.utils.Lists;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ObjectUtils;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 数据库建表工具类
 *
 * @author xiaozhi  2022-01-21 20:03
 */
@Slf4j
public class SqlSplit {

    static final String XLS = "xls";
    static final String XLSX = "xlsx";

    public static String oracle(List<Map<String, String>> paramList, String db) {
        if (CollectionUtils.isEmpty(paramList)) {
            return "";
        }
        List<String> priKeyArray = Lists.newArrayList();
        StringBuilder sqlStr = new StringBuilder();
        Map<String, String> mapFirst = paramList.get(0);
        if (db == null || "".equals(db)) {
            sqlStr.append("CREATE TABLE " + mapFirst.get("f0") + "(\n");
        } else {
            sqlStr.append("CREATE TABLE " + db + "." + mapFirst.get("f0") + "(\n");
        }
        int length = paramList.size();
        for (int i = 0; i < length; i++) {
            Map<String, String> map = paramList.get(i);
            if (map.size() == 0) {
                continue;
            }
            sqlStr.append("" + map.get("f1") + "\t " + map.get("f2") + "\t");
            if (map.get("f3").equalsIgnoreCase("Y")) {
                priKeyArray.add(map.get("f1"));
                sqlStr.append("NOT NULL,\n");
                if (i >= length - 1 && priKeyArray.size() == 1) {
                    sqlStr.append("PRIMARY KEY (" + priKeyArray.get(0) + ")\n");
                    sqlStr.append(");\n");
                }
            } else {
                if (map.get("f4").equalsIgnoreCase("Y")) {
                    if (!ObjectUtils.isEmpty(map.get("f6")) && map.get("f6").endsWith(".0")) {
                        sqlStr.append(" DEFAULT ").append(map.get("f6").replaceAll(".0", "")).append(",\n");
                    } else if (!ObjectUtils.isEmpty(map.get("f6"))) {
                        sqlStr.append(" DEFAULT ").append(map.get("f6").toUpperCase()).append(",\n");
                    } else {
                        sqlStr.append(",\n");
                    }
                } else {
                    if (!ObjectUtils.isEmpty(map.get("f6")) && map.get("f6").endsWith(".0")) {
                        sqlStr.append(" DEFAULT ").append(map.get("f6").replaceAll(".0", "")).append(",\n");
                    } else if (!ObjectUtils.isEmpty(map.get("f6"))) {
                        sqlStr.append(" DEFAULT ").append(map.get("f6").toUpperCase()).append(",\n");
                    } else {
                        sqlStr.append("NOT NULL,\n");
                    }
                }
            }
            if ((length - 1) == i) {
                sqlStr.deleteCharAt(sqlStr.length() - 2).append(")\n");
            }
        }
        sqlStr.append("\n");
        for (int i = 1; i < length; i++) {
            Map<String, String> map = paramList.get(i);
            if (map.size() == 0) {
                continue;
            }
            if (db == null || "".equals(db)) {
                sqlStr.append("COMMENT ON COLUMN " + mapFirst.get("f0") + "." + map.get("f1") + " is '" + map.get("f5") + "");
            } else {
                sqlStr.append("COMMENT ON COLUMN " + db + "." + mapFirst.get("f0") + "." + map.get("f1") + " is '" + map.get("f5") + "");
            }
            if (!ObjectUtils.isEmpty(map.get("f6")) && map.get("f6").endsWith(".0")) {
                sqlStr.append("，默认：").append(map.get("f6").replaceAll(".0", "")).append("';\n");
            } else if (!ObjectUtils.isEmpty(map.get("f6"))) {
                sqlStr.append("，默认：").append(map.get("f6").toUpperCase()).append("';\n");
            } else {
                sqlStr.append("';\n");
            }
        }
        return sqlStr.toString();
    }

    /**
     * 解析excel
     *
     * @param path 路径
     * @return excel 内容
     * @throws Exception
     */
    public static List<List<Map<String, String>>> excelRead(String path) throws Exception {
        File excel = new File(path);
        String[] split = excel.getName().split("\\.");  //.是特殊字符，需要转义
        Workbook wb = null;
        FileInputStream fis = null;
        if (XLS.equals(split[1])) {
            fis = new FileInputStream(excel);
            wb = new HSSFWorkbook(fis);
        } else if (XLSX.equals(split[1])) {
            wb = new XSSFWorkbook(excel);
        } else {
            log.info("暂时不兼容");
            return new ArrayList<>();
        }
        List<List<Map<String, String>>> resultData = new ArrayList<List<Map<String, String>>>();
        for (int sheetIdx = 0; sheetIdx < wb.getNumberOfSheets(); sheetIdx++) {
            Sheet sheet = wb.getSheetAt(sheetIdx);
            int firstRow = sheet.getFirstRowNum() + 1;
            int lastRow = sheet.getLastRowNum();
            List<Map<String, String>> result = new ArrayList<Map<String, String>>();
            for (int i = firstRow; i < lastRow + 1; i++) {
                Row row = sheet.getRow(i);
                Map<String, String> tempMap = new HashMap<String, String>();
                if (row != null) {
                    int firstCell = row.getFirstCellNum();
                    int lastCell = row.getLastCellNum();
                    for (int j = firstCell; j < lastCell; j++) {
                        Cell cell = row.getCell(j);
                        if (cell != null && !("").equals(cell.toString())) {
                            tempMap.put("f" + j, cell.toString().trim());
                        }
                    }
                    result.add(tempMap);
                }
            }
            if (null != fis) {
                fis.close();
            }
            resultData.add(result);
        }
        return resultData;
    }

    public static void main(String[] args) {
        try {
            List<List<Map<String, String>>> excelXReadORCL = excelRead("/Users/huangzhigang/Downloads/工作簿2.xlsx");
            for (List<Map<String, String>> list : excelXReadORCL) {
                System.out.println(oracle(list, ""));
                System.out.println("-- >>>>>>>>>>>>>>>>>>");
            }
        } catch (Exception e) {
            log.info(e.getMessage(), e);
        }
    }

}