JAVA导入EXCEl模板生成hive sql、批量导入数据

1.导入EXCEL模板生成hive SQL

import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;

public static String excelToSql(MultipartFile file ,String filePath) throws IOException {
           String result02 = "";
           //声明XSSFSheet对象
//            XSSFSheet sheet = null;
           String newCell = "";//用作生成符合sql规范的字符串
           XSSFRow row = null;//excel表中的行
           int rows = 0;//excel表中的行数
           int columns = 0;//excel表中的列数
           StringBuilder stringBuilder = new StringBuilder();

           //判断是否为excel
           String originalFilename = file.getOriginalFilename();
           try {
               //System.out.println(System.getProperty("user.dir"));查看当前文件夹路径，方便下方输入文件位置
               InputStream fileInputStream = file.getInputStream();
               //建立输入流获取excle文件对象
//               FileInputStream fileInputStream = new FileInputStream(originalFilename);
               FileOutputStream fos = new FileOutputStream(filePath);
               //获取excel文件的工作簿对象
               XSSFWorkbook sheets = new XSSFWorkbook(fileInputStream);

               for (XSSFSheet sheet : sheets) {
                   String sheetName = sheet.getSheetName();
                   //获取行数
                   rows = sheet.getPhysicalNumberOfRows();
                   // excel模板规格，前4行非表字段
                   if (rows > 4) {
                       stringBuilder.append("drop table if exists traffic." + sheetName + ";\n");
                       stringBuilder.append("CREATE external TABLE IF NOT EXISTS ");
                       stringBuilder.append("traffic." + sheetName + "(\n");
                   }
                   //因为excel中第一行通常为各个列的名字，故舍去，从第二行开始，索引为1
                   for (int i = 0; i < rows; i++) {

                       //得到Excel工作表的行
                       row = sheet.getRow(i);
                       //得到列数
                       columns = row.getPhysicalNumberOfCells();

                       //获取表中文名
                       if (i == 1) {
                           if (sheet.getRow(i).getCell(1).toString().equals("")) {
                               result02 = "";
                           } else {
                               result02 = row.getCell(1).toString();
                           }
                       }

                       // 当excel 的当前行，的第5格为表名时，开始创建表
                       if (i > 3 && i < rows - 1) {
                           if (!row.getCell(0).toString().replace(" ", "").equals("")) {
                               stringBuilder.append(row.getCell(0) + " String " + "COMMENT '" + row.getCell(2).toString().replace(" ", "") + "' ,\n");
                           }
                       }

                       //一行数据读取结束，为了符合sql规范，最后一行结尾应该用”;“表示，故作判断
                       //判断是否是最后一行
                       if (rows > 4 && i == rows - 1) {
                           if (!row.getCell(0).toString().replace(" ", "").equals("")) {
                               stringBuilder.append(row.getCell(0) + " String " + "COMMENT '" + row.getCell(2).toString().replace(" ", "") + "'");
                           }
                           stringBuilder.append(") COMMENT '" + result02.trim() + "'\n");
                           stringBuilder.append("row format delimited fields terminated by '&' lines terminated by '\n' stored as parquet; \n");
                       }
                   }
               }
               String sql = stringBuilder.toString();
               fos.write(sql.getBytes());
               //流操作后关闭流，养成好习惯
               fileInputStream.close();
               sheets.close();
               fos.close();
           }catch (Exception e){
              e.printStackTrace();
           }
           return stringBuilder.toString();
    }

传入两个参数 excel文档 ，sql路径
2.EXCEL模板导入数据

import com.newtouch.schedule.beans.Dictionary;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import java.util.ArrayList;
import java.util.List;

public class ExcelToTable {

    public static List<Dictionary> excelToTable(MultipartFile file) {
        XSSFRow row = null;//excel表中的行
        int rows = 0;//excel表中的行数
        int columns = 0;//excel表中的列数
        String field = "";
        Dictionary dictionary  = null;
        List<Dictionary> dictionaries = new ArrayList<>();


        try {
            //1.获取文件并读取数据
            //判断是否为excel
            String originalFilename = file.getOriginalFilename();
            boolean isExcel = ExcelUtils.validateExcel(originalFilename);
            if (isExcel) {
                /*  不管2003 .xls还是2007 .xlsx 都是
                 *  用 new XSSFWorkbook(file.getInputStream())将MultipartFile文件转为Workbook文件
                 */
                XSSFWorkbook sheets = new XSSFWorkbook(file.getInputStream());
                for (XSSFSheet sheet :sheets){
                    String sheetName = sheet.getSheetName();
                    String engName  = "";

                    //获取行数
                    rows = sheet.getPhysicalNumberOfRows();
                    for (int i = 0; i < rows; i++) {

                        String fieldName = "";
                        String fieldType = "";
                        String fieldDescription = "";
                        String remark = "";
                        //得到Excel工作表的当前行
                        row = sheet.getRow(i);
                        //得到列数
                        columns = row.getPhysicalNumberOfCells();
                        // i == 0 英文名，i == 1 中文名称
                        if (i == 1) {
                          engName  =    row.getCell(1).toString().replace(" ","");
                        }

                        if(i>3){
                            fieldName = row.getCell(0).toString().replace(" ","");
                            fieldType = row.getCell(1).toString().replace(" ","");
                            fieldDescription = row.getCell(2).toString().replace(" ","");
                            remark = row.getCell(3).toString().replace(" ","");
                        }
                        dictionary = new Dictionary(sheetName,engName,fieldName,fieldType,fieldDescription,remark);
                        dictionaries.add(dictionary);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return dictionaries;
    }
}