package Excel_Deal;
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.IOException;
import java.util.ArrayList;
import java.util.List;
public class Create_Table_From_Excel_Hive {
public static void main(String[] args) throws IOException {
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("D:\\software\\idea\\work_place\\code_change\\src\\main\\resources\\create_table_end.xlsx");
//获取excel文件的工作簿对象
XSSFWorkbook sheets = new XSSFWorkbook(fileInputStream);
// System.out.println(sheets.getSheetAt(0));
// System.out.println(sheets.getAllNames());
//获取工作簿中的工作表对象
sheet = sheets.getSheet("sheet1");
//以下生成sql语句
//直接写出sql头语句,可根据实际情况修改
// System.out.println("insert into student (id, name, sex, age) values ");
//获取行数
// System.out.println(sheet+"");
rows = sheet.getPhysicalNumberOfRows();
//因为excel中第一行通常为各个列的名字,故舍去,从第二行开始,索引为1
for (int i = 1; i < rows; i++) {
// list.clear();
newCell = "";
//得到Excel工作表的行
row = sheet.getRow(i);
//的得到列数
columns = row.getPhysicalNumberOfCells();
// System.out.println(row.getCell(2).toString()+"__________________________-");
if (row.getCell(0).toString().equals("1.0")) {
System.out.println("drop table if exists "+row.getCell(1).toString()+"."+row.getCell(2).toString()+";\n"+
"CREATE external TABLE " +row.getCell(1).toString()+"."+row.getCell(2).toString()+"\n" +" ("
);
}
// System.out.println(row.getCell(1).toString()+"___________");
//循环遍历一行中的每一列数据
for (int j = 0; j < columns; j++) { //columns == 7 总共有几列
// System.out.println(columns);
if (j==0 ||j==1 || j==2 || j==3 ){
continue;
}
//获取一列的数据
String cell = row.getCell(j).toString();
//为了符合sql规范,第一列数据前不需要加”,“,故作判断
if (j == 0 || j==1 || j==2 || j==3 || j==4) {
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==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).toString().equals("1.0")){
if (row.getCell(3).toString().equals(" ")){
result02="";
}else {
result02=row.getCell(3).toString();
}
newCell = "" + newCell +" COMMENT '"+ row.getCell(6).toString().replace(" ","")+"'" +"\n" + ")" +
" COMMENT '"+result02.trim()+"'\n"
+
"row format delimited fields terminated by '\\001' stored as parquet;";
//除去数字中的”.0“
String rpStr = newCell.replace(".0", "");
System.out.println(rpStr);
// 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);
}
// for (int j = 0; j < list.size() ; j++) {
// System.out.println("-----"+list.get(j)+"-----");
// }
}
//流操作后关闭流,养成好习惯
fileInputStream.close();
sheets.close();
}
}
excel需要两步操作。
1.空值替换为空格(一个)
2.末尾加1开头的任意数据