AdSenseHeader

Read the excel file data and store it mysql database using java   
 
    First Download the apache poi jar
 Sample program for read the .xls file data and store that data in mysql database using java 
 
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.util.SystemOutLogger;

public class XlsRead {
    /**

     * @param args

     */

    public static void main(String[] args) throws Exception,SQLException{
        // TODO Auto-generated method stub
         String filename = "E:\\ramuexcel.xls";
         FileInputStream fis = null;
         int i=0,k=0;
         Connection con=null;
         PreparedStatement ps=null;
         try
         {
             fis = new FileInputStream(filename);
             HSSFWorkbook workbook = new HSSFWorkbook(fis);
             HSSFSheet sheet = workbook.getSheetAt(0);
             Class.forName("com.mysql.jdbc.Driver");  
             con=(Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306                        /msp","root","ramu");
             ps=con.prepareStatement("ramu");
             List servicename = new ArrayList();
             List serviceDesc=new ArrayList();
             Iterator rows = sheet.rowIterator();
             while (rows.hasNext()) {
                 HSSFRow row = (HSSFRow) rows.next();
                 Iterator cells = row.cellIterator();
                 while(cells.hasNext())
                 {
                     HSSFCell cell=(HSSFCell) cells.next();
                     if(cell.getCellNum()==3)
                     {
                         servicename.add(cell);
                     }
                     if(cell.getCellNum()==6)
                     {
                         serviceDesc.add(cell);
      /** For Handling the Apostrophe symbol in description **/                                      
                         String textc=serviceDesc.get(i).toString();
                         String change=textc.replace("'", "''");
                         ps.executeUpdate("UPDATE services set description='"+change+"'where name='"+servicename.get(i++)+"'");
                         System.out.println(k+++"Sucess");                   
                     }
             }
             }             
         }catch(Exception e)
         {
             e.printStackTrace();
         }
    }
}

5 comments:

  1. why u have mentioned ramu in prepared statement instead of passing query????

    ReplyDelete
  2. Its took string type argument so placed ramu instead of ???

    ReplyDelete
  3. This comment has been removed by a blog administrator.

    ReplyDelete

 
Top