Pages

Thursday, October 1, 2015

Excel to Database

import java.io.FileInputStream;

import java.io.IOException;

import java.sql.DriverManager;

import java.sql.SQLException;



import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;



import com.mysql.jdbc.Connection;

import com.mysql.jdbc.PreparedStatement;





public class ExlToDbase

{



/**

* @param args

*/

public static void main(String[] args)

{

try

{

Class.forName("com.mysql.jdbc.Driver");

Connection conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/mydbase","root","ALPHAS");

conn.setAutoCommit(false);

PreparedStatement pstm = null ;

PreparedStatement pstm1 = null ;

PreparedStatement pstm2 = null ;

PreparedStatement pstm3 = null ;

FileInputStream input = new FileInputStream("D:\\fileLoad.xlsx");



XSSFWorkbook wb = new XSSFWorkbook(input);

XSSFSheet sheet = wb.getSheetAt(0);

Row row;

int k= 1;



int i=1;

while (i<=sheet.getLastRowNum()  )   //for(int i=1; i<=sheet.getLastRowNum(); i++)

{

row = sheet.getRow(i);

//int id = (int) row.getCell(0).getNumericCellValue();

String w = row.getCell(0).getStringCellValue();

//String address = row.getCell(2).getStringCellValue();



//System.out.println("Import rows "+i);



char[] ch = w.toCharArray();

int y = w.length();



for (int m = y ; m <= y; m--)

{



     String pl1 = new String(ch,0,m);

     if(pl1.length()>=2)

     {

        //int l=8;

      char[] dh = new char[y];

       int c=y-1;

       int g = y-1;

       int h = 1;

   

       for (int j=c; j<=y; j--)

    {

        if(m>2&&h<=8)

        {

        String mpm  = new String(dh,0,y);

            dh[g] = ch[j];

         

                String pl = new String(ch,0,m);

               // String mpm  = new String(dh,0,y);

              --m;

                h++;

               g--;

               k++;

if(k==y-1)

{

String sql1 = "insert into  Stml values('"+pl+"')";

String sql2 = "insert into  Sffl values('"+mpm.trim()+"" +"')";

String sql3 = "insert into mop values('"+w+"','"+pl+"','"+mpm.trim()+""+"')";

//java.sql.PreparedStatement psmt = conn.prepareStatement(sql);

pstm1 = (PreparedStatement) conn.prepareStatement(sql1);

pstm2 = (PreparedStatement) conn.prepareStatement(sql2);

pstm3 = (PreparedStatement) conn.prepareStatement(sql3);



 //psmt.execute();

pstm1.execute();

pstm2.execute();

pstm3.execute();



}

        }

    }

     }

}

System.out.println("Import rows "+i);

i++;

}



conn.commit();

pstm.close();

conn.close();

input.close();

System.out.println("Success import excel to mysql table");





}

catch(ClassNotFoundException e)

{

System.out.println(e);

}

catch(SQLException ex)

{

System.out.println(ex);

}

catch(IOException ioe)

{

System.out.println(ioe);

}



}



}



a)Aim of code is to read each row i cell 0 from excel sheet.



b)After reading applying for loop on each value.



c)But it reads only one value and does not apply for loop on it.

Please  solve this issue.