前言
java读取Excel的驱动包:
链接:https://pan.baidu.com/s/1ejCR9sS2OUmttFYpQnJkKQ
提取码:58rm
实现1:
从Excel中读取表名,由于每个字段会对应一个表名,故读取的某列会有若干个连续的表名出现,所以用set集合来存储表名避免重复。
package com.my.cn;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.util.HashSet;
import java.util.LinkedHashSet;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
public class ShellScript {
public static void main(String[] args) throws IOException, BiffException {
int sheetnum = 10;
int columns = 8;
String name = "YZ";
String outPath = "C://Users/Administrator/Desktop/shell/" + name + "/";
ShellScript sspt = new ShellScript();
LinkedHashSet<String> set = new LinkedHashSet<>();
//存储在Excel中读取到的某列数据,由于获取的数据要互补相同因此使用set集合来存储
sspt.wListName(set,sheetnum,columns);
//生成shell脚本
sspt.getShellScript(set,outPath,name);
}
public void getShellScript(LinkedHashSet<String> set, String outPath,String name) throws IOException {
int line = 1;
for (String s : set) {
BufferedWriter writer = new BufferedWriter(new FileWriter(new File(outPath + name + "_" + line + "_" + s + "_BF.sh")));
String nameOne = "hadoop fs -test -e hdfs://hdpdw01:8020/user/hadoop/hzswdw/Historical_filing_area/"
+ name + "_L/" + "L_" + name + "_" + s + "_L_$time";
String nameTwo = "hadoop distcp -m 5 hdfs://hdpdw01:8020/user/hadoop/sqoop/"
+ name + "_Q/" + "S_" + name + "_" + s + "_Q";
String nameThree = "hdfs://hdpdw01:8020/user/hadoop/hzswdw/Historical_filing_area/"
+ name + "_L/" + "L_" + name + "_" + s + "_L_$time";
writer.write("#!bin/bash\n");
writer.write("time=$(date " + "+%y-%m-%d" + ")\n");
writer.write("######################################################\n");
writer.write("#用途:用于原始数据的历史归档\n");
writer.write("#创建人:\n");
writer.write("#创建时间:\n");
writer.write("#修改人:\n");
writer.write("#修改时间:\n");
writer.write("#修改内容:\n");
writer.write("#######################################################\n");
writer.write(nameOne + "\n");
writer.write("if [ $? -eq 0 ]; then\n");
writer.write("exit\n");
writer.write("else\n");
writer.write(nameTwo + " " + nameThree + "\n");
writer.write("fi");
line ++;
writer.close();
}
}
public void wListName(HashSet<String> set,int sheetnum,int columns) throws BiffException, IOException {
Workbook workbook=Workbook.getWorkbook(new File("E:\\项目\\我的任务\\数据\\数据.xls"));
//2:获取第一个工作表sheet
Sheet sheet=workbook.getSheet(sheetnum);
for(int i=1;i<sheet.getRows();i++){
Cell cell=sheet.getCell(columns,i);
String s = cell.getContents();
//去除字符串中的换行符
s = s.replace("\n", "");
if (!s.equals("")) {
set.add(s);
}
}
System.out.println(set);
System.out.println(set.size());
workbook.close();
}
}
实现2:
实现2与实现1的不同之处在于,实现2使用Reader类的readLine()方法来读取txt文件的一行数据。
package com.my.cn;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.util.LinkedHashSet;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
public class SQLLoad {
public static void main(String[] args) throws IOException, BiffException {
int sheetnum = 4;
int columns = 11;
String name = "gis";
String sqlname = null;
String outPath = "C://Users/Administrator/Desktop/loadsql/" + name + "/";
//2:获取第一个工作表sheet
BufferedReader reader = new BufferedReader(new FileReader(new File("C:\\Users\\Administrator\\Desktop\\2.txt")));
while ((sqlname = reader.readLine()) != null) {
BufferedWriter writer = new BufferedWriter(new FileWriter(new File(outPath + sqlname)));
writer.write("#!bin\bash\n");
writer.write("hive -f " + sqlname);
writer.close();
}
}
}