Import excel using java

Below is the code which can be used directly to import data from excel in to database using HSSFPOI in java.

This code also shows the implementation of vector in java.


Vector is an array list with extended properties which follow the dynamic and automatic addition of data at run-time.

Unlike array, Vector can grow and  shrink as needed to accommodate its size adding and removing items even after the Vector has been created.

The Vector class implements dynamic array of objects.


package importExcel;

      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 ImportUsersExcel {
   
         
      public static void main( String [] args ) {
      
          String fileName="C:\\Documents and Settings\\user\\Desktop\\USERS.xls";
       
        //Storing the Excel data in a vector
        Vector dataHolder=read(fileName);
       
        //Writing data to database
        saveToDB(dataHolder);
    }
     
     
        public static Vector read(String fileName)    {
        Vector cellVectorHolder = new Vector();
        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();
                  Vector cellStoreVector=new Vector();
                  while(cellIter.hasNext()){
                          HSSFCell myCell = (HSSFCell) cellIter.next();
                          cellStoreVector.addElement(myCell);
               }
                  cellVectorHolder.addElement(cellStoreVector);
          }
        }catch (Exception e){e.printStackTrace(); }
        return cellVectorHolder;
    }
       
     
       
        private static void saveToDB(Vector dataHolder) {
           
           
               //Variable declaration for columns in Sample Excel
                String username="";
                String password="";
                String st =null;
               
                 System.out.println("SIZE"+dataHolder.size());
             
                
                 //int i=1 will exclude the column headers in excel. If i=0 it will include first column in excel
                 for (int i=1;i<dataHolder.size(); i++){
                 
                     Vector cellStoreVector=(Vector)dataHolder.elementAt(i);

                   //get(0) will read data from first column
                    username= cellStoreVector.get(0).toString();
                   
                    //get(1) will read data from Second column   
                   password=cellStoreVector.get(1).toString();
                  
                   for (int j=0; j < cellStoreVector.size();j++){
                                HSSFCell myCell = (HSSFCell)cellStoreVector.elementAt(j);
                                 st = myCell.toString();
                               
                             //Below is the sample SWITCH CASE code which can be used to perform various operations based on Excel Field type
                              //BEGIN
                                 /*
                                 switch (myCell.getCellType()) {
                                 case HSSFCell.CELL_TYPE_NUMERIC:
                                     System.out.println(myCell.getNumericCellValue());
                                 break;

                                 case HSSFCell.CELL_TYPE_STRING:
                                     System.out.println(myCell.getStringCellValue());
                                 break;
                                 default: break;
                             }
                            
                             //END
                              */  
                            
                                
                                
                        }
   try{
      
       //MYSQL CONNECTION
     
       // Class.forName("com.mysql.jdbc.Driver").newInstance();
   
       //Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root", "");
      
      
      // ORACLE CONNECTION 
      
       //Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
 
       //Connection con = DriverManager.getConnection("jdbc:oracle:thin:@134.168.134.45:1521:orc);
      
      
     //   Statement stmt=con.createStatement();
      
       // int n=stmt.executeUpdate("insert into login(username,password) value('"+username+"','"+password+"')");
      
      
       System.out.println("User Name:"+username);
       System.out.println("Password:"+password);
      
      
       
        }
        catch(Exception e){
           
        }
        finally
        {
            // stmt.close();
            // con.close();
        }
        }
        }
      }