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);
}
}