java - POI: not iterating through all excel row while inserting values to database -
i have written code reads data excel sheet inserts data database. can read data when comes posting values database 1 row being posted. here code wrote please help.
private void addbtnactionperformed(java.awt.event.actionevent evt) { try { fileinputstream file = new fileinputstream(new file("new.xls")); org.apache.poi.ss.usermodel.workbook workbook = workbookfactory.create(file); org.apache.poi.ss.usermodel.sheet sheet = workbook.getsheetat(0); iterator<row> rowiterator = sheet.iterator(); rowiterator.next(); while(rowiterator.hasnext()) { row row = rowiterator.next(); //for each row, iterate through each columns iterator<cell> celliterator = row.celliterator(); while(celliterator.hasnext()) { cell cell = celliterator.next(); //this change cell types string cell.setcelltype(cell.cell_type_string); switch(cell.getcelltype()) { case cell.cell_type_boolean: system.out.println("boolean===>>>"+cell.getbooleancellvalue() + "\t"); break; case cell.cell_type_numeric: break; case cell.cell_type_string: break; } } qt=row.getcell(0).getstringcellvalue(); qg=row.getcell(1).getstringcellvalue(); mgr=row.getcell(2).getstringcellvalue(); tap=row.getcell(3).getstringcellvalue(); prof = row.getcell(4).getstringcellvalue(); cs=row.getcell(5).getstringcellvalue(); ss=row.getcell(6).getstringcellvalue(); sw=row.getcell(7).getstringcellvalue(); pmo=row.getcell(8).getstringcellvalue(); eo=row.getcell(9).getstringcellvalue(); learner=row.getcell(10).getstringcellvalue(); ttp=row.getcell(11).getstringcellvalue(); system.out.println(qt+qg+mgr+tap+prof+cs+ss+sw+pmo+eo+learner+ttp); system.out.println(""); } file.close(); up here can read 6 rows , display them console. code save values database saves 1 row instead of 6.
preparedstatement pst = null; try{ class.forname("org.apache.derby.jdbc.embeddeddriver"); connection con = drivermanager.getconnection("jdbc:derby:mtd","herbert","elsie1*#"); string sql="insert app.wsp(qualificationtype ,qualificationgroup ,managers ,professionals,"+ "techniciansassociateprofessionals,clericalsupport,servicesales,skilledworkers,plantmachineoperators,"+ "elementaryoccupation,learners,totaltrainingplanned)"+ "values(?,?,?,?,?,?,?,?,?,?,?,?)"; pst = con.preparestatement(sql); pst.setstring(1, qt); pst.setstring(2, qg); pst.setstring(3, mgr); pst.setstring(4, tap); pst.setstring(5, prof); pst.setstring(6, cs); pst.setstring(7, ss); pst.setstring(8, sw); pst.setstring(9, pmo); pst.setstring(10, eo); pst.setstring(11, learner); pst.setstring(12, ttp); pst.executeupdate(); updatetable(); joptionpane.showmessagedialog(null,"values inserted successfully"); } catch(exception e){ joptionpane.showmessagedialog(null, e); } } catch (filenotfoundexception e) { e.printstacktrace(); } catch (ioexception e) { e.printstacktrace(); } catch (invalidformatexception ex) { logger.getlogger(ofo.class.getname()).log(level.severe, null, ex); } catch (encrypteddocumentexception ex) { logger.getlogger(ofo.class.getname()).log(level.severe, null, ex); }
ok, requested. code not tested, bugs may remain.
note that
you shouldn't need call
class.forname()load database driver more (read introducing paragraphs drivermanager).you should use try-with-resources make code more robust , easier maintain
reuse prepared statements
please use dataformatter cell values text (or better yet use correct types in sql)
code:
private void addbtnactionperformed(java.awt.event.actionevent evt) { // sql insert statement string sql="insert app.wsp(qualificationtype ,qualificationgroup ,managers ,professionals,"+ "techniciansassociateprofessionals,clericalsupport,servicesales,skilledworkers,plantmachineoperators,"+ "elementaryoccupation,learners,totaltrainingplanned)"+ "values(?,?,?,?,?,?,?,?,?,?,?,?)"; // use try-with resources! // btw: class.forname() no more required installed drivers in java 7 try (fileinputstream file = new fileinputstream("new.xls"); connection con = drivermanager.getconnection("jdbc:derby:mtd","herbert","elsie1*#"); preparedstatement pst = con.preparestatement(sql)) { // create workbook org.apache.poi.ss.usermodel.workbook workbook = workbookfactory.create(file); org.apache.poi.ss.usermodel.sheet sheet = workbook.getsheetat(0); iterator<row> rowiterator = sheet.iterator(); // skip first line (headline) rowiterator.next(); // use retrieve strings numeric cells dataformatter df = new dataformatter(); while(rowiterator.hasnext()) { row row = rowiterator.next(); // cell values qt=df.formatcellvalue(row.getcell(0)); qg=df.formatcellvalue(row.getcell(1)); mgr=df.formatcellvalue(row.getcell(2)); tap=df.formatcellvalue(row.getcell(3)); prof =df.formatcellvalue(row.getcell(4)); cs=df.formatcellvalue(row.getcell(5)); ss=df.formatcellvalue(row.getcell(6)); sw=df.formatcellvalue(row.getcell(7)); pmo=df.formatcellvalue(row.getcell(8)); eo=df.formatcellvalue(row.getcell(9)); learner=df.formatcellvalue(row.getcell(10)); ttp=df.formatcellvalue(row.getcell(11)); // print console system.out.println(qt+qg+mgr+tap+prof+cs+ss+sw+pmo+eo+learner+ttp); system.out.println(""); // write database pst.setstring(1, qt); pst.setstring(2, qg); pst.setstring(3, mgr); pst.setstring(4, tap); pst.setstring(5, prof); pst.setstring(6, cs); pst.setstring(7, ss); pst.setstring(8, sw); pst.setstring(9, pmo); pst.setstring(10, eo); pst.setstring(11, learner); pst.setstring(12, ttp); pst.executeupdate(); } // do? updatetable(); // show success dialog joptionpane.showmessagedialog(null,"values inserted successfully"); } catch(exception e){ // show error dialog joptionpane.showmessagedialog(null, e); } }
Comments
Post a Comment