Hi ,
For your help i am attaching the program for read data from excel and store the data in database. i am using the ms-sql . try with this program.
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) {
try
{
String fileName = "C:\\Book2.xls";
Vector dataHolder = read(fileName);
saveToDatabase(dataHolder);
}
catch(Exception e)
{
System.out.println(e);
}
}
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);
System.out.println("the size of the vector"+cellStoreVector);
for (int j = 0; j < cellStoreVector.size(); j++) {
HSSFCell myCell = (HSSFCell) cellStoreVector.elementAt(j);
String st = myCell.toString();
System.out.println("the size in string"+st);
username = st.substring(0);
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) {
System.out.println("the Excerption"+e);
}
}
}
}
You need to add
java POI jar in your library.
1
