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();
         }
    }
}

8 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
  4. It's very useful post and i had good experience with this salesforce training in Chennai who are offering good certificaiton assistance. I would say salesforce training is a best way to get certified on crm.

    ReplyDelete
  5. Gone through this wonderful coures called Salesforce Certification Training in Dallas who are offering fully practical course, who parent is Salesforce Training in USA and they have students at Salesforce Training classes in Canada institutes.

    ReplyDelete

 
Top