excel java sql文件怎么打开,java解析excel生成sql文件

public classTest {public static void insertEnvInfo() throwsIOException{

String filePath= "D:/stg2ip/env_stg.xls";　　//指定本地的數據目錄

String slqPath = "D:/stg2ip/sql/evn_stg.sql";　　//指定生成文件目錄

try{

InputStream in= newFileInputStream(filePath);

Workbook wb= newHSSFWorkbook(in);

in.close();int numberOfSheets =wb.getNumberOfSheets();

String sql= "";

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

List list = new ArrayList<>();

Map map= newHashMap();

sql= "";

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

Map map1 =new HashMap<>();for(ExcelBean bean :excelBean){if(StringUtils.isBlank(map1.get(bean.getAppnickname())))

map1.put(bean.getAppnickname(), bean.getServerhost());else{

map1.put(bean.getAppnickname(), map1.get(bean.getAppnickname())+";"+bean.getServerhost());　　//map的value進行合並

}

}

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 `appautotest`.`app_sys_env_info` (`app_nick_name`, `scope`, `key`, `value`, `createtime`, `updatetime`) VALUES ('%s', 'BXSTG5', 'server_host', '%s', now(), now());\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();　　//創建一個單獨ExcelBean、用於要合並的列(本文只取用了app_nick_name、server_host兩個列)

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

Row row=sheetAt.getRow(rowNum);

String app_nick_name= "";

String server_host= "";

String war_name= "";

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

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

app_nick_name=cell.getStringCellValue();

bean.setAppnickname(app_nick_name);

}

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

server_host=cell.getStringCellValue();

bean.setServerhost(server_host);

}

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

war_name=cell.getStringCellValue();

}

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

manager=cell.getStringCellValue();

}

}

list.add(bean);

}returnlist;

}public static void main(String args[]) throwsIOException{

insertEnvInfo();

}

}