序:需求是往数据库批量造测试数据、数量较多。用代码直接生成sql文件会省很多事。
注、引包:poi.jar系列引入即可。
package demo; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.util.ArrayList; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Map; 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.xssf.usermodel.XSSFWorkbook; public class excelToSql { public static void executeInfo() throws IOException{ String filePath = "D:/FavoriteVideo/testExcel.xlsx"; //指定本地的数据目录 String slqPath = "D:/FavoriteVideo/testSql.sql"; //指定生成文件目录 try { InputStream in = new FileInputStream(filePath); Workbook wb = new XSSFWorkbook(in); in.close(); String sql = ""; Sheet sheetAt = wb.getSheetAt(0); int lastRowNum = sheetAt.getLastRowNum(); Map map = new HashMap(); sql = ""; List<ExcelBean> excelBean = getExcelBean(sql, sheetAt, lastRowNum); Map<String,String> map1 =new HashMap<>(); for(ExcelBean bean :excelBean){ map1.put(bean.getTestName(),bean.getUser_()); } Iterator<Map.Entry<String, String>> it = map1.entrySet().iterator(); while (it.hasNext()) { Map.Entry<String, String> 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 = new File(slqPath); if(!file.exists()){ file.createNewFile(); } byte[] bytes = sql.getBytes(); OutputStream os = new FileOutputStream(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<ExcelBean> getExcelBean(String sql, Sheet sheetAt, int lastRowNum) { List<ExcelBean> 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); } return list; } public static void main(String args[]) throws IOException{ long startTime = System.currentTimeMillis(); executeInfo(); long endTime = System.currentTimeMillis(); System.out.println("耗时为m:" + (endTime - startTime)/1000); } }
excel测试数据:
生成.sql文件:
生成的.sql直接拿入数据库执行即可。(oracle库记得更改日期字段哦..)
end_