import java.io.FileInputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelToDb {
public static void main( String [] args ) {
String fileName="F:/workspace/test/exceldatabase.xlsx";
Vector dataHolder=read(fileName);
saveToDatabase(dataHolder);
}
public static Vector read(String fileName) {
Vector cellVectorHolder = new Vector();
try{
FileInputStream myInput = new FileInputStream(fileName);
//POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
XSSFWorkbook myWorkBook = new XSSFWorkbook(myInput);
XSSFSheet mySheet = myWorkBook.getSheetAt(0);
Iterator rowIter = mySheet.rowIterator();
while(rowIter.hasNext()){
XSSFRow myRow = (XSSFRow) rowIter.next();
Iterator cellIter = myRow.cellIterator();
//Vector cellStoreVector=new Vector();
List list = new ArrayList();
while(cellIter.hasNext()){
XSSFCell myCell = (XSSFCell) cellIter.next();
list.add(myCell);
}
cellVectorHolder.addElement(list);
}
}catch (Exception e){e.printStackTrace(); }
return cellVectorHolder;
}
private static void saveToDatabase(Vector dataHolder) {
String EMPID="";
String EMPNAME="";
String DEG="";
String SALARY="";
String DEPT="";
System.out.println(dataHolder);
for(Iterator iterator = dataHolder.iterator();iterator.hasNext();) {
List list = (List) iterator.next();
EMPID = list.get(0).toString();
EMPNAME = list.get(1).toString();
DEG = list.get(2).toString();
SALARY = list.get(3).toString();
DEPT = list.get(4).toString();
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/excel", "raj", "rjvd");
System.out.println("connection made...");
PreparedStatement stmt=con.prepareStatement("INSERT INTO emp_tbl(EMPID,EMPNAME,DEG,SALARY,DEPT) VALUES(?,?,?,?,?)");
stmt.setString(1, EMPID);
stmt.setString(2, EMPNAME);
stmt.setString(3, DEG);
stmt.setString(4, SALARY);
stmt.setString(5, DEPT);
stmt.executeUpdate();
System.out.println("Data is inserted");
stmt.close();
con.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
}
}
0 Comments