import jxl.Sheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

public class Test {

public static void main(String[] args) {

try {
FileInputStream fileInputStream = new FileInputStream(
new File("D:\\OneDrive\\桌面\\test.xlsx"));
XSSFWorkbook wb = new XSSFWorkbook(fileInputStream);

int numberOfSheets = wb.getNumberOfSheets();//获取sheel页的个数
XSSFSheet sheet = null;
for (int i = 0; i < numberOfSheets; i++) {
sheet = wb.getSheetAt(i); // sheet 索引
createSql( sheet);
}

wb.close();

} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}

private static void createSql(XSSFSheet sheet) {
String sheetName = sheet.getSheetName();

int lastRowNum = sheet.getLastRowNum();
Row rowLast = sheet.getRow(lastRowNum);
String tableName = new DataFormatter().formatCellValue(rowLast.getCell(0));

Row row = null;
Cell cell = null;//英文表
Cell cell2 = null;//中文表
Cell cell3 = null;//字段名
Cell cell4 =null; //字段中文名
Cell cell5 = null ;//数据类型
Cell cell6 =null;//数据最大长度
Cell cell7 =null ;//小数位长度
Cell cell8 =null ;//是否可为空
Cell cell9=null; //是否主键
String columName = null; //列名
String columType = null; //列类型
String columComent = null;//列注释
String columLength = null;//列长度

String sqlCont = "";
StringBuilder strsql= new StringBuilder();

for (int i = 1; i <= lastRowNum; i++) {
row = sheet.getRow(i);
cell = row.getCell(0); // 表英文名
if (cell != null && !StringUtils.isEmpty(cell.getStringCellValue())) {
cell2 = row.getCell(1); // 表中文名
cell3 = row.getCell(4); // 英文字段
cell4= row.getCell(5);//字段中文
cell5 =row.getCell(9);//数据类型
cell6=row.getCell(11);//字段长度
cell7=row.getCell(12);//小数位数
cell8=row.getCell(14);//是否可为空
cell9=row.getCell(15);//是否主键

columName = cell3.getStringCellValue();
columType = cell5.getStringCellValue();
columComent = cell4.getStringCellValue();
columLength=new DataFormatter().formatCellValue(cell6);

if (cell5!=null && cell5.toString().toLowerCase().equals("varchar2")){
sqlCont += columName + " " + columType.replace("varchar2","varchar") +"("+columLength+")"+ " DEFAULT NULL COMMENT '" + columComent
+ "',\n";
}
else if (cell5!=null && cell5.toString().toLowerCase().equals("char")){
sqlCont += columName + " " + columType.replace("varchar2","varchar") +"("+columLength+")"+ " DEFAULT NULL COMMENT '" + columComent
+ "',\n";
}else if (cell5!=null && cell5.toString().toLowerCase().equals("number") && cell7!=null && cell7.getNumericCellValue()!=0){
sqlCont += columName + " " + columType +"("+columLength+","+(int)cell7.getNumericCellValue()+")"+ " DEFAULT NULL COMMENT '" + columComent
+ "',\n";
}else{
sqlCont += columName + " " + columType +"("+columLength+")"+ " DEFAULT NULL COMMENT '" + columComent
+ "',\n";
}

}
}

String sqlStart = "CREATE TABLE test_" + cell+ "(\n";
String sqlEnd = "\n)\nCOMMENT '" + cell2 + "'\nclustered by ( xxxx ) into 13 buckets STORED AS ORC tblproperties(\"transactional\"=\"true\"\");";

String sql = sqlStart + sqlCont.substring(0, sqlCont.length() - 2) + sqlEnd;

System.out.println(sql);
}
}