我有一个关于java的insert查询的问题。我已经从excel文件中读取了数据,我想使用这些数据导入mysql,如果excel文件有3列,我可以使用 String sql="INSERT INTO tablename(column1,column2,colum3) value(...) 但是如果excel文件没有定义列数(例如 excel_1.xlsx 有3列, excel_2.xlsx 有4列)。如何使用插入查询?有人能给我一些建议吗?
String sql="INSERT INTO tablename(column1,column2,colum3) value(...)
excel_1.xlsx
excel_2.xlsx
luaexgnf1#
你可以这样做:
FileInputStream excelFile = new FileInputStream(new File("yourExcelsheet.xsls")); Workbook workbook = new XSSFWorkbook(excelFile); Sheet datatypeSheet = workbook.getSheetAt(0); Iterator<Row> iterator = datatypeSheet.iterator(); //int columnCount = 4; String columnArray[] = new String[]{"column1","column2","column3","column4","column5","column6"}; //iterate row-wise while (iterator.hasNext()) { String sql="INSERT INTO tablename"; String columnNames = ""; String valueString = ""; String value = ""; int i=0; Row currentRow = iterator.next(); Iterator<Cell> cellIterator = currentRow.iterator(); //iterate column-wise while (cellIterator.hasNext()) { Cell currentCell = cellIterator.next(); value = currentCell.getStringCellValue(); if(i != 0) { columnNames += ","; valueString += ","; } columnNames += columnArray[i] ; valueString += value; i++; } sql += "(" + columnNames + ") values (" + valueString + ");"; System.out.println(sql); }
输出:插入tablename(column1,column2,column3,column4)值(5,5,5,5);
1条答案
按热度按时间luaexgnf1#
你可以这样做:
输出:
插入tablename(column1,column2,column3,column4)值(5,5,5,5);