import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
public class SqlCreate {
/**
* @param args
* @throws IOException
* @throws FileNotFoundException
*/
private static String path = "F:\\15xsd修改\\03_SMD"; // execl保存路径
private static String savepath = "F:\\15xsd修改\\增量脚本\\zlsql2.txt"; //拼接后的sql保存路径
public static void main(String[] args) {
try {
File file = new File(path); //此处file不是文件,是路径
FileOutputStream out = new FileOutputStream(savepath);
// OutputStream out = new FileOutputStream(savepath);
Workbook wb = null;
int count=0;
for (File f : file.listFiles()) { //通过".listFiles()"方法遍历路径下文件
count++;
System.out.println("获取文件:" + f+"----"+count);
InputStream excelFileInputStream = null;
excelFileInputStream = new FileInputStream(f);
wb = WorkbookFactory.create(excelFileInputStream);
excelFileInputStream.close();
Sheet st = wb.getSheet("TAB");
Sheet sc = wb.getSheet("COL");
int stl = st.getLastRowNum();
int scl = sc.getLastRowNum();
Row rowst = st.getRow(4);
Cell cellschema = rowst.getCell(2);
// System.out.println(cellschema);
String sql = "";
for (int i = 0; i < scl; i++) {
// String sql="";
Row row = sc.getRow(i);//获取Excel的行,下标从0开始
if (row == null) {//若行为空,则遍历下一行
continue;
}
Cell cell = row.getCell(1);//获取指定单元格,单元格从左到右下标从0开始
Cell cell2=row.getCell(0);
if (cell != null
&& cell.getStringCellValue().equals("C_DJHKYKWDCL")) {
// sql += "ALTER TABLE " + cellschema + "."+cell2
// + " RENAME N_DJHKYKWDCL TO C_DJHKYKWDCL;"+"\n" ;
sql += "ALTER TABLE " + cellschema + "."+cell2
+ " ALTER C_DJHKYKWDCL TYPE TEXT;"+"\n" ;
System.out.println(sql);
byte[]a = sql.getBytes();
out.write(a);
}
}
}
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
poi解析Excel,遍历表结构,获取单元格内容,拼接sql语句,写入TXT文件
最新推荐文章于 2022-09-08 10:54:20 发布