java - Insert Excel Data to database -

java - Insert Excel Data to database -

i suppose insert excel sheet info database java programme not getting actual output. excel sheet not contain headings given in row need each row , create insert script.

the info should insert straight in database

client page acessdate processtime bytes 12.34.56.9876545 hello.jsp 6/3/2014 5:52:16 pm 3 123 12.34.56.9876545 signout.jsp 6/3/2014 6:21:16 pm 0 1724 12.34.56.9876545 login.jsp 6/3/2014 6:21:16 pm 0 4937

i shoud output as

insert clickstream(client,page,accessdate,processtime,bytes) value('"+12.34.56.9876545 +"','"+hello.jsp+"','"+ 6/3/2014 5:52:16 pm+"','"+3+"','"+1724+"'"

i useing code not getting , see if code working or not cant able right output

import java.io.fileinputstream; import java.sql.connection; import java.sql.drivermanager; import java.sql.statement; import java.util.iterator; import java.util.vector; import org.apache.poi.openxml4j.opc.opcpackage; import org.apache.poi.poifs.filesystem.poifsfilesystem; import org.apache.poi.xssf.usermodel.xssfcell; import org.apache.poi.xssf.usermodel.xssfrow; import org.apache.poi.xssf.usermodel.xssfsheet; import org.apache.poi.xssf.usermodel.xssfworkbook; public class insertexcelfiledata { public static void main( string [] args ) { string filename="f:\\book.xlsx"; vector dataholder=read(filename); savetodatabase(dataholder); } public static vector read(string filename) { vector cellvectorholder = new vector(); try{ fileinputstream myinput = new fileinputstream(filename); //poifsfilesystem myfilesystem = new poifsfilesystem(myinput); xssfworkbook myworkbook = new xssfworkbook(myinput); xssfsheet mysheet = myworkbook.getsheetat(0); iterator rowiter = mysheet.rowiterator(); while(rowiter.hasnext()){ xssfrow myrow = (xssfrow) rowiter.next(); iterator celliter = myrow.celliterator(); vector cellstorevector=new vector(); while(celliter.hasnext()){ xssfcell mycell = (xssfcell) celliter.next(); cellstorevector.addelement(mycell); } cellvectorholder.addelement(cellstorevector); } }catch (exception e){e.printstacktrace(); } homecoming cellvectorholder; } private static void savetodatabase(vector dataholder) { string client=""; string page=""; string accessdate=""; string processtime=""; string bytes=""; (int i=0;i<dataholder.size(); i++){ vector cellstorevector=(vector)dataholder.elementat(i); (int j=0; j < cellstorevector.size();j++){ xssfcell mycell = (xssfcell)cellstorevector.elementat(j); string st = mycell.tostring(); client=st; page=st.substring(0); system.out.print(st); } try{ class.forname("com.jnetdirect.jsql.jsqldriver").newinstance(); connection con = drivermanager.getconnection("jdbc:jsqlconnect://12.33.44.55/database=environment", "root", "root"); statement stat=con.createstatement(); int k=stat.executeupdate("insert clickstream(client,page,accessdate,processtime,bytes) value('"+client+"','"+page+"','"+accessdate+"','"+processtime+"','"+bytes+"'"); system.err.print(k); system.out.println("data inserted"); stat.close(); con.close(); } catch(exception e){} } } }

please help code work properly

actually there little problem iteration. hope code may help somewhat

import java.io.fileinputstream; import java.sql.*; import java.util.arraylist; import java.util.iterator; import java.util.list; import java.util.vector; import org.apache.poi.xssf.usermodel.xssfcell; import org.apache.poi.xssf.usermodel.xssfrow; import org.apache.poi.xssf.usermodel.xssfsheet; import org.apache.poi.xssf.usermodel.xssfworkbook; public class exceltodb { public static void main( string [] args ) { string filename="g:\\book.xlsx"; vector dataholder=read(filename); savetodatabase(dataholder); } public static vector read(string filename) { vector cellvectorholder = new vector(); try{ fileinputstream myinput = new fileinputstream(filename); //poifsfilesystem myfilesystem = new poifsfilesystem(myinput); xssfworkbook myworkbook = new xssfworkbook(myinput); xssfsheet mysheet = myworkbook.getsheetat(0); iterator rowiter = mysheet.rowiterator(); while(rowiter.hasnext()){ xssfrow myrow = (xssfrow) rowiter.next(); iterator celliter = myrow.celliterator(); //vector cellstorevector=new vector(); list list = new arraylist(); while(celliter.hasnext()){ xssfcell mycell = (xssfcell) celliter.next(); list.add(mycell); } cellvectorholder.addelement(list); } }catch (exception e){e.printstacktrace(); } homecoming cellvectorholder; } private static void savetodatabase(vector dataholder) { string clientadd=""; string page=""; string accessdate=""; string processtime=""; string bytes=""; system.out.println(dataholder); for(iterator iterator = dataholder.iterator();iterator.hasnext();) { list list = (list) iterator.next(); clientadd = list.get(0).tostring(); page = list.get(1).tostring(); accessdate = list.get(2).tostring(); processtime = list.get(3).tostring(); seek { class.forname("com.mysql.jdbc.driver").newinstance(); connection con = drivermanager.getconnection("jdbc:mysql://localhost:3306/test", "root", "welcome"); system.out.println("connection made..."); preparedstatement stmt=con.preparestatement("insert clickstream(clientadd,page,accessdate,processtime) values(?,?,?,?)"); stmt.setstring(1, clientadd); stmt.setstring(2, page); stmt.setstring(3, accessdate); stmt.setstring(4, processtime); stmt.executeupdate(); system.out.println("data inserted"); stmt.close(); con.close(); } grab (classnotfoundexception e) { e.printstacktrace(); } grab (sqlexception e) { e.printstacktrace(); } grab (instantiationexception e) { e.printstacktrace(); } grab (illegalaccessexception e) { e.printstacktrace(); } } } }

java database excel oracle