insert excel value in to oracle data base

Hi All I am using this code for saving excel value to oracle database table. code is working properly but in table i am geting some diffrent type value like... o org.apache.poi.hssf.usermodel.HSSFCell@19360e2

i am not getting right value...

my code is...

import java.io.*;
import java.sql.*;
import java.util.*;
import java.util.regex.*;
import org.apache.poi.hssf.usermodel.*; 
import org.apache.poi.poifs.filesystem.POIFSFileSystem; 
public class Insert { 

ConnectionToCustomDatabase dba=new ConnectionToCustomDatabase();

public static void main( String [] args ) { 
        String fileName="c:/hhh.xls"; 
        Vector dataHolder=read(fileName); 
        saveToDatabase(dataHolder);    } 
    public static Vector read(String fileName) 
       {        Vector cellVectorHolder = new Vector(); 
       try{ 
           System.out.println("try is executing");
           FileInputStream myInput = new FileInputStream(fileName); 
           System.out.println("try is executing1");
           POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput); 
           System.out.println("try is executing2");
           HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem); 
           System.out.println("try is executing3");
           HSSFSheet mySheet = myWorkBook.getSheetAt(0); 
           System.out.println("try is executing4");
           Iterator rowIter = mySheet.rowIterator(); 
           while(rowIter.hasNext()){ 
               HSSFRow myRow = (HSSFRow) rowIter.next(); 
               Iterator cellIter = myRow.cellIterator(); 
               Vector cellStoreVector=new Vector(); 
               System.out.println("try is executing5");
               while(cellIter.hasNext()){ 
                   HSSFCell myCell = (HSSFCell) cellIter.next(); 
                   cellStoreVector.addElement(myCell); 
                   } 
               cellVectorHolder.addElement(cellStoreVector); 
                 } 
            }
               catch (Exception e){
                   e.printStackTrace(); 
                                   } 
                    return cellVectorHolder; 
                 } 
                     private static void saveToDatabase(Vector dataHolder) { 
                         System.out.println("try is executing6"); 
                    String username=""; 
                     String password=""; 
                     for (int i=0;i<dataHolder.size(); i++){ 
                         Vector cellStoreVector=(Vector)dataHolder.elementAt(i); 
                         for (int j=0; j &lt; cellStoreVector.size();j++){ 
                             HSSFCell myCell = (HSSFCell)cellStoreVector.elementAt(j);
                             String st = myCell.toString(); 
                             username=st.substring(0,1); 
                             password=st.substring(0); 
                             } 
                         try{ 
                             System.out.println("try is executing7");
                             ConnectionToCustomDatabase dba;
                             Connection con = null;
                             PreparedStatement pstmt = null;
                             ResultSet rs = null;
                             //String sqlquery ="";
                             dba = new ConnectionToCustomDatabase();
                             con = dba.getVIS2Connection();

PreparedStatement stat=con.prepareStatement("insert into login(username,password1) values('"+username+"','"+password+"')");       

                         System.out.println(username);
                         System.out.println(password);
                         stat.execute();   
                                                     pstmt = con.prepareStatement(qry);
                            rs = pstmt.executeQuery();
                         System.out.println("try is executing8");
                         // System.out.println(k);
                         System.out.println("Data is inserted");
                         // stat.executeQuery()
                    if(rs.next())
                    {
                        String uu=rs.getString("username");
                        String pp1=rs.getString("password1");
                        System.out.println(uu);
                        System.out.println(pp1);

                    }
                          rs.close();
                         con.close();
                     }     
                     catch(Exception e)
                     {
                         System.out.print(e);
                     } 
                     }
                 }     
             }

plz find the bug on this code...and reply

December 3, 2010 at 3:16 PM

Hi Friend,

You can read the data from the excel file using JDBC. For this you need to create dsn connection.

Follow these steps to create a dsn connection:

1) Go to the control Panel>>Administrative Tools>>Data Sources.

2)Click Add button and select Driver do Microsoft Excel driver. Click finish button

3)Add Data Source Name and click select workbook button to select the workbook whose data is to be retrieved.

4)Click ok and compile the following code:

import java.sql.*;
import java.util.*;

public class InsertIExcelValueToDatabase{
    public static void main(String[] args)throws Exception{
    ArrayList list1=new ArrayList();
    ArrayList list2=new ArrayList();
    try{
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
         Connection con = DriverManager.getConnection("jdbc:odbc:excel");
         Statement stmt = con.createStatement();
         ResultSet rs = stmt.executeQuery("select  name, address from [Sheet1$]");
         while (rs.next()) {
            String name = rs.getString("name");
            String address = rs.getString("address");
            list1.add(name);
            list2.add(address);
            System.out.println(name + "  "+address);
        }
         con.close();

         Class.forName("com.mysql.jdbc.Driver");
          Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
         Statement st = conn.createStatement();
         for(int i=0;i<list2.size();i++){
             String nn=list1.get(i).toString();
             String add=list2.get(i).toString();

             st.executeUpdate("insert into data1(name,address) values('"+nn+"','"+add+"')");
              System.out.println(nn+" "+add);
         }
    }
    catch(Exception e){
        System.out.println(e);
    }
       }
        }

Here excel is the data source name and Sheet1 is the excel sheet name.

Thanks

December 3, 2010 at 5:17 PM

thanks dear But If i upload this Excel file then how excel data will mapp to oracle database table. My project requrement is when somebuddy fill a excel form then all form value will save on database table..is it any solution of this problem. Thanks dear Yogesh

December 21, 2011 at 1:26 PM

hi i need to test ur code to check bugs so please send complete code