java - How to compare excel file table and mysql table and insert data into mysql column wise using jsp? -
i new in jsp , apache poi library, have 1 excel file data:my excel sheet
and have 1 mysql table "data" column name "address" , "name". using following code insertion excel data mysql table. data insertion complete.
<%@ page language="java" import="java.sql.*" contenttype="text/html charset=iso-8859-1" pageencoding="iso-8859-1"%> <!doctype html public "-//w3c//dtd html 4.01 transitional//en" "http://www.w3.org/tr/html4/loose.dtd"> <%@ page import ="java.util.date" %> <%@ page import ="java.io.*" %> <%@ page import ="java.io.filenotfoundexception" %> <%@ page import ="java.io.ioexception" %> <%@ page import ="java.util.iterator" %> <%@ page import ="java.util.arraylist" %> <%@ page import ="javax.servlet.http.httpservletrequest"%> <%@ page import ="org.apache.poi.hssf.usermodel.hssfcell" %> <%@ page import ="org.apache.poi.hssf.usermodel.hssfrow" %> <%@ page import ="org.apache.poi.hssf.usermodel.hssfsheet" %> <%@ page import ="org.apache.poi.hssf.usermodel.hssfworkbook" %> <%@ page import ="org.apache.poi.poifs.filesystem.poifsfilesystem" %> <html> <head> <meta http-equiv="content-type" content="text/html; charset=iso-8859-1"> </head> <body> <%! connection con; preparedstatement ps=null; statement stmt= null; public static arraylist readexcelfile(string filename) { arraylist cellarraylisstholder = new arraylist(); try{ fileinputstream myinput = new fileinputstream(filename); poifsfilesystem myfilesystem = new poifsfilesystem(myinput); hssfworkbook myworkbook = new hssfworkbook(myfilesystem); hssfsheet mysheet = myworkbook.getsheetat(0); iterator rowiter = mysheet.rowiterator(); while(rowiter.hasnext()){ hssfrow myrow = (hssfrow) rowiter.next(); iterator celliter = myrow.celliterator(); arraylist cellstorearraylist=new arraylist(); while(celliter.hasnext()){ hssfcell mycell = (hssfcell) celliter.next(); cellstorearraylist.add(mycell); } cellarraylisstholder.add(cellstorearraylist); } }catch (exception e){e.printstacktrace(); } return cellarraylisstholder; }%> <% string file = request.getparameter("file"); string filename="/home/data.xls"; arraylist dataholder=readexcelfile(filename); try { class.forname("com.mysql.jdbc.driver"); con=drivermanager.getconnection("jdbc:mysql://localhost:3306/mysql","root",""); stmt =con.createstatement(); string query="insert data(address, name) values(?,?)"; ps=con.preparestatement(query); int count=0; arraylist cellstorearraylist=null; for(int i=1;i<dataholder.size();i++) { cellstorearraylist=(arraylist)dataholder.get(i); ps.setstring(1,((hssfcell)cellstorearraylist.get(0)).tostring()); ps.setstring(2,((hssfcell)cellstorearraylist.get(1)).tostring()); count= ps.executeupdate(); } if(count>0) { %> following deatils excel file have been inserted in student table of database <table> <tr> <th>address</th> <th>name</th> </tr> <% (int j=1;j < dataholder.size(); j++) { cellstorearraylist=(arraylist)dataholder.get(j);%> <tr> <td><%=((hssfcell)cellstorearraylist.get(0)).tostring() %></td> <td><%=((hssfcell)cellstorearraylist.get(1)).tostring() %></td> </tr> <%} } else {%> <center> details have not been inserted!!!!!!!!!</center> <% } }catch(exception e) {}%> </table> </body> </html> here excel sheet have 2 columns, in case excel sheet have more 2 columns. column quantity in excel sheet , mysql table same, column sequence not same. means suppose in excel sheet have 4 column like: "address", "name", "age", "class", , mysql table have 4 column in sequence like: "address", "age", "name", "class". in case data insertion not done successfully. means "age" , "name" column data insertion exchange each other. solution check excel sheet column name , mysql table column name, , insert data particular column. position of column may vary data insertion done true. please give me solution this. correct code if have made mistake. thank in advance.
Comments
Post a Comment