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.
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.
