java生成sql文件_java解析excel生成sql文件

packagedemo;importjava.io.File;importjava.io.FileInputStream;importjava.io.FileNotFoundException;importjava.io.FileOutputStream;importjava.io.IOException;importjava.io.InputStream;importjava.io.OutputStream;importjava.util.ArrayList;importjava.util.HashMap;importjava.util.Iterator;importjava.util.List;importjava.util.Map;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.ss.usermodel.Sheet;importorg.apache.poi.ss.usermodel.Workbook;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;public classexcelToSql {public static void executeInfo() throwsIOException{

String filePath= "D:/FavoriteVideo/testExcel.xlsx"; //指定本地的数据目录

String slqPath = "D:/FavoriteVideo/testSql.sql"; //指定生成文件目录

try{

InputStream in= newFileInputStream(filePath);

Workbook wb= newXSSFWorkbook(in);

in.close();

String sql= "";

Sheet sheetAt= wb.getSheetAt(0);int lastRowNum =sheetAt.getLastRowNum();

Map map= newHashMap();

sql= "";

List excelBean =getExcelBean(sql, sheetAt, lastRowNum);

Map map1 =new HashMap<>();for(ExcelBean bean :excelBean){map1.put(bean.getTestName(),bean.getUser_());

}

Iterator> it =map1.entrySet().iterator();while(it.hasNext()) {

Map.Entry entry =it.next();

System.out.println("key= " + entry.getKey() + " and value= " +entry.getValue());

sql+= String.format("INSERT IGNORE INTO `test_table_info` (`testName`, `updateTime`, `createTime`, `user_`) VALUES ('%s', now(), now(), '%s');\n", entry.getKey(), entry.getValue());

}

System.out.println(map1);

System.out.println(map1.size());

File file= newFile(slqPath);if(!file.exists()){

file.createNewFile();

}byte[] bytes =sql.getBytes();

OutputStream os= newFileOutputStream(slqPath);

os.write(bytes);

os.flush();

os.close();

System.out.println("sheets");

}catch(FileNotFoundException e) {

e.printStackTrace();

}catch(IOException e) {

e.printStackTrace();

}

}private static List getExcelBean(String sql, Sheet sheetAt, intlastRowNum) {

List list = new ArrayList<>();for (int rowNum=0; rowNum <= lastRowNum; rowNum++){

ExcelBean bean= new ExcelBean(); //创建一个单独Bean、

System.out.println("rowNum = "+rowNum);

Row row=sheetAt.getRow(rowNum);

String testName= "";

String user_= "";if (row != null){

Cell cell= row.getCell(0);if (cell != null) {

testName=cell.getStringCellValue();

bean.setTestName(testName);

}

cell= row.getCell(1);if (cell != null) {

user_=cell.getStringCellValue();

bean.setUser_(user_);

}/*** 更多列在这里补充*/}

list.add(bean);

}returnlist;

}public static void main(String args[]) throwsIOException{long startTime =System.currentTimeMillis();

executeInfo();long endTime =System.currentTimeMillis();

System.out.println("耗时为m:" + (endTime - startTime)/1000);

}

}