Home Tutorial Java Poi Insert excel file data into database Using Java Programming

 
 

Share on Google+Share on Google+
Insert excel file data into database Using Java Programming
Posted on: October 29, 2009 at 12:00 AM
Advertisement
In this section, you will learn how to insert excel file data into the database using Java Programming Language.

Insert excel file data into database in Java Program

In this PHP tutorial section, you will learn how to insert excel file data into the database. We have used Jakarta POI api for inserting excel file data into database in this purpose. With the use of HSSFRow and HSSFCell, the Iterator class iterates every row and every cell and stored the excel file values into the vector.This Vector data is then used to store file values into the database.

Here is the code of Insert excel file data into Database Using java programming:

   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 {
      public static void main( String [] args ) {
    	String fileName="C:\\File.xls";
    	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);
            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 saveToDatabase(Vector dataHolder) {
        String username="";
		String password="";
		for (int i=0;i<dataHolder.size(); i++){
                   Vector cellStoreVector=(Vector)dataHolder.elementAt(i);
			for (int j=0; j < cellStoreVector.size();j++){
				HSSFCell myCell = (HSSFCell)cellStoreVector.elementAt(j);
				String st = myCell.toString();
				 username=st.substring(0,1);
				 password=st.substring(0);
							}
			try{
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root", "root");
	Statement stat=con.createStatement();
	int k=stat.executeUpdate("insert into login(username,password) value('"+username+"','"+password+"')");
	System.out.println("Data is inserted");
	stat.close();
	con.close();
	}
	catch(Exception e){}
	}
	}
      }
    
Advertisement

Related Tags for Insert excel file data into database Using Java Programming:


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: October 29, 2009

Recommend the tutorial

Advertisements Advertisements
 

 

 

DMCA.com