java根据excel表格生成切片表模板文件_java excel导出切片-优快云博客

在这里插入图片描述
上图就是开发需求，
直接上代码
package tst;

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

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.*;

public class handsome {
//    static String  result_disappoint="";
//    static String result_disappoint01="";
//static StringBuffer result_disappoint =new StringBuffer();
//    StringBuffer getResult_disappoint=new StringBuffer();

//    static List result_disappoint =new ArrayList();
//    static List result_disappoint01=new ArrayList();

    static Map<String, String> result_disappoint = new TreeMap();
    static Map<String, String> result_disappoint01 = new TreeMap();

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

        SimpleDateFormat simpleDateFormat = new SimpleDateFormat();
        simpleDateFormat.applyPattern("yyyyMMdd" + "_" + "HHmmss");
        Date date = new Date();
        String Date_Sign = simpleDateFormat.format(date);

        FileOutputStream fos = new FileOutputStream("E:\\work\\234.hql", false);

//        Scanner scanner=new Scanner(System.in);
//        System.out.print("输入模板绝对路径：");
//        String hive_dir=scanner.nextLine();
//
//        String hive002=hive_dir.replace(".xlsx",".hql");
//        FileOutputStream fos=new FileOutputStream(hive002,false);

//        System.out.println("请指定excel中的具体sheet页名称。"+"\n"+"EXAMPLE:Sheet05");
//        String hive_dir_sheet=scanner.nextLine();
        int Num = 0;
        List<String> list = new ArrayList();
        List<String> list01 = new ArrayList();
        List<String> list02 = new ArrayList();
        List<String> list06 = new ArrayList();
        String result = "";
        String result02 = "";

        //声明XSSFSheet对象
        XSSFSheet sheet = null;
        String newCell = "";//用作生成符合sql规范的字符串
        XSSFRow row = null;//excel表中的行
        int rows = 0;//excel表中的行数
        int columns = 0;//excel表中的列数

        //System.out.println(System.getProperty("user.dir"));查看当前文件夹路径，方便下方输入文件位置
        //建立输入流获取excle文件对象
        FileInputStream fileInputStream =
                new FileInputStream("E:\\work\\123.xlsx");
//                new FileInputStream(hive_dir);
        //获取excel文件的工作簿对象
        XSSFWorkbook sheets = new XSSFWorkbook(fileInputStream);
//        System.out.println(sheets.getSheetAt(0));
//        System.out.println(sheets.getAllNames());
        //获取工作簿中的工作表对象
        sheet = sheets.getSheet("Sheet2");


        //以下生成sql语句
        //直接写出sql头语句，可根据实际情况修改
//        System.out.println("insert into student (id, name, sex, age) values ");
        //获取行数

//        System.out.println(sheet+"");
        rows = sheet.getPhysicalNumberOfRows();

        String title="------------------------------------------------------------------------------------------------\n" +
                "--  文件名  ： .hql\n" +
                "--  中文名称： \n" +
                "--  执行周期： \n" +
                "--  目标层次： \n" +
                "--  来源表  ： \n" +
                "--  目标表  ： \n" +
                "--  备注信息： \n" +
                "--  逻辑说明： \n" +
                "--   作者               日期             版本信息           说明\n" +
                "--  ----------------------------------------------------------------------------------------------     \n" +
                "--                                       v1.0              初始版本\n" +
                "-- ----------------------------------------------------------------------------------------------- \n" +
                "use dcpdm;\n" +
                "\n" +
                "--hivesetstart\n" +
                "\n" +
                "--variablesetstart\n" +
                "\n" +
                "--定义变量\n" +
                "set hivevar:v_date='#today#';\n" +
                "set hivevar:v_part='';";
        fos.write( (title).getBytes("UTF-8"));
        fos.write("\r\n".getBytes("UTF-8"));

        String title01="--删除分区\n" +"--alter table dcpdm."+sheet.getRow(1).getCell(4).toString()+"drop if exists partition(busi_dt=${v_date},src_sys_cd=${v_part});";
        fos.write( (title01).getBytes("UTF-8"));
        fos.write("\r\n".getBytes("UTF-8"));

        String title02="--删除分区\n" +"insert overwrite table dcpdm."+sheet.getRow(1).getCell(4).toString()+"partition (busi_dt=${v_date},src_sys_cd=${v_part})";
        fos.write( (title02).getBytes("UTF-8"));
        fos.write("\r\n".getBytes("UTF-8"));


        //因为excel中第一行通常为各个列的名字，故舍去，从第二行开始，索引为1
        for (int i = 1; i < rows; i++) {
//            System.out.println("行数是"+rows);
            if (i == rows - 1) {
                String output01 = "-- Running Success! End of script execution";
                System.out.println(output01);
//                fos.write(output01.getBytes());
                break;
            }

//          list.clear();

            newCell = "";
            //得到Excel工作表的行
            row = sheet.getRow(i);


//            if (! row.getCell(7).toString().equals("0.0")) {
                System.out.println("经给");
                 result_disappoint.add(row.getCell(4).toString());
//                result_disappoint.put(sheet.getRow(i).getCell(7).toString(),row.getCell(4).toString()+" "+row.getCell(5).toString());
                 result_disappoint01.add(row.getCell(5).toString());
                   result_disappoint01.put(sheet.getRow(i).getCell(0).toString(),row.getCell(5).toString());
                System.out.println("_________执行一次___________");
                System.out.println("第"+i+"次"+result_disappoint+"____"+result_disappoint01);
//                continue;
//            }


//            if ( row.getCell(7).toString().equals("0.0")) {
//                System.out.println("经过");
                result_disappoint = "";
                result_disappoint01 = "";
                System.out.println("_________执行一次___________");
                System.out.println("第"+i+"次"+result_disappoint+"____"+result_disappoint01);
//                continue;
//            }


            //的得到列数
            columns = row.getPhysicalNumberOfCells();

//            System.out.println(row.getCell(2).toString()+"__________________________-");
//            System.out.println( row.getCell(6).toString()+"______________");

//                  System.out.println(row.getCell(0).toString()+"得出结果！");
            if (row.getCell(0).toString().equals("1.0")) {                //表名落地
//                System.out.println("经过此行！");
                Num++;
//                System.out.println(sheet.getRow(i).getCell(3).toString()+" ----------");
                System.out.println("insert overwrite table dcpdm." + row.getCell(1).toString() + "partition (busi_dt=${v_date},src_sys_cd=${v_part})");


                result_disappoint01.clear();
                result_disappoint.clear();
//                System.out.println("第"+i+"次"+result_disappoint+"____"+result_disappoint01);


//                String Sign01="--第"+Num+"张表"+sheet.getRow(i).getCell(3).toString()+"\n"+"drop table if exists "+row.getCell(1).toString()+"."+row.getCell(2).toString()+";\n"+
//                        "CREATE  TABLE " +row.getCell(1).toString()+"."+row.getCell(2).toString()+"\n" +" (";
//                String Sign01= "--第"+Num+"张表 "+sheet.getRow(i).getCell(3).toString()+"\n"+"DROP TABLE IF EXISTS "+row.getCell(1).toString()+"."+row.getCell(2).toString()+";\n"+
//                        "CREATE TABLE IF NOT EXISTS " +row.getCell(1).toString()+"."+row.getCell(2).toString()+"\n" +"(";
                String Sing02 = "insert overwrite table dcpdm." + row.getCell(1).toString() + "partition (busi_dt=${v_date},src_sys_cd=${v_part})";

                fos.write(Sing02.getBytes("UTF-8"));
                fos.write("\r\n".getBytes("UTF-8"));


            }

//            System.out.println(row.getCell(1).toString()+"___________");

            String column="";
            String col03="";
            String result01="";
                if (row.getCell(1).toString().length() <= 60) {
                    if (row.getCell(5).toString().contains("etl_label")){
                        result01="\'"+row.getCell(0).toString()+"\'";
                    }else if (row.getCell(5).toString().contains("etl_trans_dt")){
                        result01="from_unixtime(unix_timestamp(),'yyyy-mm-dd hh:mm:ss') ";
                    }
                    else if (row.getCell(3).toString().contains("string")){
                        result01="trim("+row.getCell(1).toString()+")";
                    }else {
                       result01= row.getCell(1).toString();
                    }
                    int a01 = 60 - result01.length();
                    column = result01;
                    for (int a001 = 0; a001 <= a01; a001++) {
                        column = column + " ";
                    }
                }

            String columnb="";
            if (row.getCell(5).toString().length()<=30) {


                int a01 = 30 - row.getCell(5).toString().length();

                    columnb = row.getCell(5).toString();

                for (int a001b = 0; a001b <= a01; a001b++) {
                    columnb =columnb +" ";
                }
            }
            String start="";
            if ( i==1 ){start="select  "; }else {start="       ,";}

            System.out.println(start+column+"       as  "
                    +columnb+"-- "+
                    row.getCell(7).toString() );

            String sign03=start+column+"       as  "
                    +columnb+"-- "+
                    row.getCell(7).toString();



            fos.write( (sign03).getBytes("UTF-8"));
            fos.write("\r\n".getBytes("UTF-8"));


            //循环遍历一行中的每一列数据
            for (int j = 0; j < columns; j++) {   //columns == 7  总共有几列






                if (j == 0 || j == 1 || j == 3 || j == 5 || j == 7) {
                    continue;
                }


            }


//                //为了符合sql规范，第一列数据前不需要加”,“,故作判断
//                if (j == 0 || j==1 || j==2  || j==3 || j==4 || j==7 )  {
//                    newCell = newCell + cell;
//                    if (j==4){
//                        list.add(cell);
//                    }else
//                    if (j==1){
//                        list01.add(cell);
//                    }else
//                    if (j==2){
//                        list02.add(cell);
//                    }
//                    else
//                    if (j==7){
//                        newCell=newCell+"";
//                    }
//                }
//                else
//                if (j==6){
//                    list06.add(cell);
//                }
                else if (j==2){
                    newCell = newCell + "." + cell;
                }
//                else if (j==5){
//                    newCell = newCell + " " + cell;
//
//                }
//                else {
//                    newCell = newCell + "" + cell;
//                }
//
//            }


            //一行数据读取结束，为了符合sql规范，最后一行结尾应该用”;“表示，故作判断
//            if (i == rows - 1) {

//              if (sheet.getRow(i+1).getCell(1).toString()== null){
//                  System.out.println("End");
//              }else

//            if (sheet.getRow(i+1).getCell(0) !=null){}


//            if (sheet.getRow(i+1).getCell(0).toString().equals("1.0") ){ //每一行依次迭代，下方的 判断语句，判断表名，当下一行数据为新表的时候，处理上一张表末尾字段形式
//                if (row.getCell(3).toString().equals(" ")){       //表名为空格的置为空，否则则是原来的表名
//                    result02="";
//                }else {
//                    result02=row.getCell(3).toString();
//                }
//                String a01="";
//
//                StringBuffer stringBuffer=new StringBuffer();
//                Iterator<Map.Entry<String, String>> iterator = result_disappoint.entrySet().iterator();
//                while (iterator.hasNext()) {
//                    Map.Entry<String, String> entry = iterator.next();
//                    entry.getKey();
//                    entry.getValue();
//                    stringBuffer=stringBuffer.append(entry.getValue()+",");
//                }
//                String result011="";
//                String partition01="";
//                String end01="";
//                if (stringBuffer.length()==0){
//                    result011="";
//                    partition01="";
//                    end01="";
//                }else{
//                    result011=stringBuffer.substring(0, stringBuffer.length() -1);
//                    partition01="partitioned by (";
//                    end01=")";
//                }
//
//                newCell = "  " + newCell +" COMMENT  '"+ row.getCell(6).toString().replace(" ","")+"'" +"\n" + ")" +  //最后一个字段进行处理
//                        " COMMENT '"+result02.trim()+"'\n"+"" +
//
                        "partitioned by ("+result011+")"
//                        partition01+result011+end01
//                        +
//                        "ROW FORMAT DELIMITED FIELDS TERMINATED BY '\\001' STORED AS PARQUET;"+"\n";        //末尾生成的
//                //除去数字中的”.0“
//                String rpStr = newCell.replace(".0", "");
//                System.out.println(rpStr);

                String Sign11="" + newCell +" COMMENT  '"+ row.getCell(6).toString().replace(" ","")+"'" +"\n" + ")" +
                        " COMMENT '"+result02.trim()+"'\n"
                        +
                        "row format delimited fields terminated by '\\001' stored as parquet;";
//                String Sign11=rpStr;
//                fos.write(Sign11.getBytes("UTF-8"));
//                fos.write("\r\n".getBytes("UTF-8"));
//
//
                System.out.println(
                        "COMMENT ON table "+row.getCell(1).toString()+"."+row.getCell(2).toString()+ " IS \'"+row.getCell(3).toString().trim()+"\';"
                );
//                for (int j = 0; j < list.size() ; j++) {   //将字段注释做处理，如果为空格置为空，如果不是，则返回原注释。
                System.out.println("comment on column "+list01.get(j)+"."+list02.get(j)+"."+list.get(j)+" is "+"\""+list06.get(j)+"\";");
//
//                    if (list06.get(j).equals(" ")){
//                        result="";
//                    }else {
//                        result=list06.get(j);
//                    }

                    System.out.println("comment on column " + row.getCell(1).toString()+"."+row.getCell(2).toString()+"." +list.get(j)+" is "
                            +"\'"+result+"\';");
//                }
//                list.clear();
//                list01.clear();
//                list02.clear();
//                list06.clear();
//
//                System.out.println(
                          "Column ->" +row.getCell(4).toString()
//                );
//            }
              else if (sheet.getRow(i+1).getCell(1).toString()== null){
                  System.out.println("End");
              }
//
            comment on column t1.id is 'id';
//            else {   //处理表中非最后一行的逻辑
//                newCell = "  " + newCell + " COMMENT '"+row.getCell(6).toString().replace(" ","")+"',";
//                //除去数字中的”.0“
//                String rpStr = newCell.replace(".0", "");
//                System.out.println(rpStr);
//
//
//                String Sign16=rpStr;
//                fos.write(Sign16.getBytes("UTF-8"));
//                fos.write("\r\n".getBytes("UTF-8"));
//
//
//            }
            for (int j = 0; j < list.size() ; j++) {
                System.out.println("-----"+list.get(j)+"-----");
            }
//
//
//        }

            //流操作后关闭流，养成好习惯
            fileInputStream.close();
            sheets.close();

        }

        String title03="from dcods."+sheet.getRow(1).getCell(0).toString()+" a"+"\n"+"where and a.etl_dt = ${v_date};";
        fos.write( (title03).getBytes("UTF-8"));
        fos.write("\r\n".getBytes("UTF-8"));


    }
}