java code to save excel data to mysql

Solutions on MaxInterview for java code to save excel data to mysql by the best coders in the world

showing results for - "java code to save excel data to mysql"

06 Jun 2016

1 package DB;  
2 import java.sql.Connection;  
3 import java.sql.DriverManager;  
4 import java.sql.SQLException;  
5 public class DB_Connection {  
6   private Connection con;  
7    public DB_Connection()  
8   {  
9          try  
10         {  
11               String conUrl="jdbc:mysql://localhost:3306/MyTestDb";  
12               String userName="root";  
13               String pass="root";  
14               Class.forName("com.mysql.jdbc.Driver");  
15                 con=DriverManager.getConnection(conUrl,userName,pass);  
16         }  
17         catch(SQLException s)  
18         {  
19             System.out.println(s);  
20         }  
21         catch(ClassNotFoundException c)  
22         {  
23              System.out.println(c);  
24         }  
25   }  
26   public Connection getConn() {  
27     return con;  
28   }  
29   public void setConn(Connection con) {  
30     this.con = con;  
31   }  
32 }  
33

source

Erika

17 Aug 2016

1 <%@ page language="java" import="java.sql.*" contentType="text/html; charset=ISO-8859-1"  
2   pageEncoding="ISO-8859-1"%>  
3 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">  
4 <%@ page import ="java.util.Date" %>  
5 <%@ page import ="java.io.*" %>  
6 <%@ page import ="java.io.FileNotFoundException" %>  
7 <%@ page import ="java.io.IOException" %>  
8 <%@ page import ="java.util.Iterator" %>  
9 <%@ page import ="java.util.ArrayList" %>  
10 //Apache POI Libraries  
11 <%@ page import ="org.apache.poi.hssf.usermodel.HSSFCell" %>  
12 <%@ page import ="org.apache.poi.hssf.usermodel.HSSFRow" %>  
13 <%@ page import ="org.apache.poi.hssf.usermodel.HSSFSheet" %>  
14 <%@ page import ="org.apache.poi.hssf.usermodel.HSSFWorkbook" %>  
15 <%@ page import ="org.apache.poi.poifs.filesystem.POIFSFileSystem" %>  
16 <html>  
17 <head>  
18 <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">  
19 <title>Insert title here</title>  
20 </head>  
21 <body>  
22 <jsp:useBean id="connection" class="DB.DB_Connection" scope="page">  
23   <jsp:setProperty name="connection" property="*"/>  
24 </jsp:useBean>  
25 <%!    
26 Connection con;  
27 PreparedStatement ps=null;  
28 public static ArrayList readExcelFile(String fileName)  
29 {  
30   /** --Define a ArrayList  
31     --Holds ArrayList Of Cells  
32    */  
33   ArrayList cellArrayLisstHolder = new ArrayList();  
34   try{  
35   /** Creating Input Stream**/  
36     FileInputStream myInput = new FileInputStream(fileName);  
37   /** Create a POIFSFileSystem object**/  
38   POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);  
39   /** Create a workbook using the File System**/  
40    HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);  
41    /** Get the first sheet from workbook**/  
42   HSSFSheet mySheet = myWorkBook.getSheetAt(0);  
43   /** We now need something to iterate through the cells.**/  
44    Iterator rowIter = mySheet.rowIterator();  
45    while(rowIter.hasNext()){  
46      HSSFRow myRow = (HSSFRow) rowIter.next();  
47      Iterator cellIter = myRow.cellIterator();  
48      ArrayList cellStoreArrayList=new ArrayList();  
49      while(cellIter.hasNext()){  
50        HSSFCell myCell = (HSSFCell) cellIter.next();  
51        cellStoreArrayList.add(myCell);  
52      }  
53      cellArrayLisstHolder.add(cellStoreArrayList);  
54    }  
55   }catch (Exception e){e.printStackTrace(); }  
56   return cellArrayLisstHolder;  
57 }%>  
58 <%  
59 String fileName="testExcel.xls"; //testExcel.xls Excel File name  
60 //Read an Excel File and Store in a ArrayList  
61 ArrayList dataHolder=readExcelFile(fileName);  
62 //Print the data read  
63 //printCellDataToConsole(dataHolder);  
64 con=connection.getConn();  
65 String query="insert into Student values(?,?,?)";  
66 ps=con.prepareStatement(query);  
67 int count=0;  
68 ArrayList cellStoreArrayList=null;  
69 //For inserting into database  
70 for (int i=1;i < dataHolder.size(); i++) {  
71   cellStoreArrayList=(ArrayList)dataHolder.get(i);  
72     ps.setString(1,((HSSFCell)cellStoreArrayList.get(0)).toString());  
73     ps.setString(2,((HSSFCell)cellStoreArrayList.get(1)).toString());  
74     ps.setString(3,((HSSFCell)cellStoreArrayList.get(2)).toString());  
75    count= ps.executeUpdate();  
76     System.out.print(((HSSFCell)cellStoreArrayList.get(2)).toString() + "t");  
77     }  
78 //For checking data is inserted or not?  
79   if(count>0)  
80     { %>  
81         Following deatils from Excel file have been inserted in student table of database  
82           <table>  
83             <tr>  
84               <th>Student's Name</th>  
85               <th>Class</th>  
86               <th>Age</th>  
87             </tr>  
88     <% for (int i=1;i < dataHolder.size(); i++) {  
89   cellStoreArrayList=(ArrayList)dataHolder.get(i);%>  
90   <tr>  
91     <td><%=((HSSFCell)cellStoreArrayList.get(0)).toString() %></td>  
92     <td><%=((HSSFCell)cellStoreArrayList.get(1)).toString() %></td>  
93     <td><%=((HSSFCell)cellStoreArrayList.get(2)).toString() %></td>  
94   </tr>  
95     <%}  
96    }  
97   else  
98   {%>  
99   <center> Details have not been inserted!!!!!!!!!</center>  
100   <%  }  %>  
101     </table>  
102 </body>  
103 </html>  
104

source

Enrico

09 Jul 2016

1   import java.io.*;
2      import java.sql.*;
3      import java.util.*;
4      import java.util.regex.*;
5      import org.apache.poi.hssf.usermodel.*;
6      import org.apache.poi.poifs.filesystem.POIFSFileSystem;
7     
8      public class Insert {
9      public static void main( String [] args ) {
10    	String fileName="C:\\File.xls";
11    	Vector dataHolder=read(fileName);
12    	saveToDatabase(dataHolder);
13    }
14	public static Vector read(String fileName)    {
15    	Vector cellVectorHolder = new Vector();
16    	try{
17    		FileInputStream myInput = new FileInputStream(fileName);
18   	    	POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
19            HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
20            HSSFSheet mySheet = myWorkBook.getSheetAt(0);
21           Iterator rowIter = mySheet.rowIterator(); 
22           while(rowIter.hasNext()){
23        	  HSSFRow myRow = (HSSFRow) rowIter.next();
24        	  Iterator cellIter = myRow.cellIterator();
25        	  Vector cellStoreVector=new Vector();
26        	  while(cellIter.hasNext()){
27        		  HSSFCell myCell = (HSSFCell) cellIter.next();
28        		  cellStoreVector.addElement(myCell);
29        	  }
30        	  cellVectorHolder.addElement(cellStoreVector);
31          }
32    	}catch (Exception e){e.printStackTrace(); }
33    	return cellVectorHolder;
34    }
35	private static void saveToDatabase(Vector dataHolder) {
36        String username="";
37		String password="";
38		for (int i=0;i<dataHolder.size(); i++){
39                   Vector cellStoreVector=(Vector)dataHolder.elementAt(i);
40			for (int j=0; j < cellStoreVector.size();j++){
41				HSSFCell myCell = (HSSFCell)cellStoreVector.elementAt(j);
42				String st = myCell.toString();
43				 username=st.substring(0,1);
44				 password=st.substring(0);
45							}
46			try{
47    Class.forName("com.mysql.jdbc.Driver").newInstance();
48    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root", "root");
49	Statement stat=con.createStatement();
50	int k=stat.executeUpdate("insert into login(username,password) value('"+username+"','"+password+"')");
51	System.out.println("Data is inserted");
52	stat.close();
53	con.close();
54	}
55	catch(Exception e){}
56	}
57	}
58      }
59

source