mysql to excel using java code example

Example 1: java code to save excel data to mysql

package DB;  
 import java.sql.Connection;  
 import java.sql.DriverManager;  
 import java.sql.SQLException;  
 public class DB_Connection {  
   private Connection con;  
    public DB_Connection()  
   {  
          try  
         {  
               String conUrl="jdbc:mysql://localhost:3306/MyTestDb";  
               String userName="root";  
               String pass="root";  
               Class.forName("com.mysql.jdbc.Driver");  
                 con=DriverManager.getConnection(conUrl,userName,pass);  
         }  
         catch(SQLException s)  
         {  
             System.out.println(s);  
         }  
         catch(ClassNotFoundException c)  
         {  
              System.out.println(c);  
         }  
   }  
   public Connection getConn() {  
     return con;  
   }  
   public void setConn(Connection con) {  
     this.con = con;  
   }  
 }

Example 2: java code to save excel data to mysql

<%@ page language="java" import="java.sql.*" contentType="text/html; charset=ISO-8859-1"  
   pageEncoding="ISO-8859-1"%>  
 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">  
 <%@ page import ="java.util.Date" %>  
 <%@ page import ="java.io.*" %>  
 <%@ page import ="java.io.FileNotFoundException" %>  
 <%@ page import ="java.io.IOException" %>  
 <%@ page import ="java.util.Iterator" %>  
 <%@ page import ="java.util.ArrayList" %>  
 //Apache POI Libraries  
 <%@ page import ="org.apache.poi.hssf.usermodel.HSSFCell" %>  
 <%@ page import ="org.apache.poi.hssf.usermodel.HSSFRow" %>  
 <%@ page import ="org.apache.poi.hssf.usermodel.HSSFSheet" %>  
 <%@ page import ="org.apache.poi.hssf.usermodel.HSSFWorkbook" %>  
 <%@ page import ="org.apache.poi.poifs.filesystem.POIFSFileSystem" %>  
 <html>  
 <head>  
 <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">  
 <title>Insert title here</title>  
 </head>  
 <body>  
 <jsp:useBean id="connection" class="DB.DB_Connection" scope="page">  
   <jsp:setProperty name="connection" property="*"/>  
 </jsp:useBean>  
 <%!    
 Connection con;  
 PreparedStatement ps=null;  
 public static ArrayList readExcelFile(String fileName)  
 {  
   /** --Define a ArrayList  
     --Holds ArrayList Of Cells  
    */  
   ArrayList cellArrayLisstHolder = new ArrayList();  
   try{  
   /** Creating Input Stream**/  
     FileInputStream myInput = new FileInputStream(fileName);  
   /** Create a POIFSFileSystem object**/  
   POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);  
   /** Create a workbook using the File System**/  
    HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);  
    /** Get the first sheet from workbook**/  
   HSSFSheet mySheet = myWorkBook.getSheetAt(0);  
   /** We now need something to iterate through the cells.**/  
    Iterator rowIter = mySheet.rowIterator();  
    while(rowIter.hasNext()){  
      HSSFRow myRow = (HSSFRow) rowIter.next();  
      Iterator cellIter = myRow.cellIterator();  
      ArrayList cellStoreArrayList=new ArrayList();  
      while(cellIter.hasNext()){  
        HSSFCell myCell = (HSSFCell) cellIter.next();  
        cellStoreArrayList.add(myCell);  
      }  
      cellArrayLisstHolder.add(cellStoreArrayList);  
    }  
   }catch (Exception e){e.printStackTrace(); }  
   return cellArrayLisstHolder;  
 }%>  
 <%  
 String fileName="testExcel.xls"; //testExcel.xls Excel File name  
 //Read an Excel File and Store in a ArrayList  
 ArrayList dataHolder=readExcelFile(fileName);  
 //Print the data read  
 //printCellDataToConsole(dataHolder);  
 con=connection.getConn();  
 String query="insert into Student values(?,?,?)";  
 ps=con.prepareStatement(query);  
 int count=0;  
 ArrayList cellStoreArrayList=null;  
 //For inserting into database  
 for (int i=1;i < dataHolder.size(); i++) {  
   cellStoreArrayList=(ArrayList)dataHolder.get(i);  
     ps.setString(1,((HSSFCell)cellStoreArrayList.get(0)).toString());  
     ps.setString(2,((HSSFCell)cellStoreArrayList.get(1)).toString());  
     ps.setString(3,((HSSFCell)cellStoreArrayList.get(2)).toString());  
    count= ps.executeUpdate();  
     System.out.print(((HSSFCell)cellStoreArrayList.get(2)).toString() + "t");  
     }  
 //For checking data is inserted or not?  
   if(count>0)  
     { %>  
         Following deatils from Excel file have been inserted in student table of database  
           <table>  
             <tr>  
               <th>Student's Name</th>  
               <th>Class</th>  
               <th>Age</th>  
             </tr>  
     <% for (int i=1;i < dataHolder.size(); i++) {  
   cellStoreArrayList=(ArrayList)dataHolder.get(i);%>  
   <tr>  
     <td><%=((HSSFCell)cellStoreArrayList.get(0)).toString() %></td>  
     <td><%=((HSSFCell)cellStoreArrayList.get(1)).toString() %></td>  
     <td><%=((HSSFCell)cellStoreArrayList.get(2)).toString() %></td>  
   </tr>  
     <%}  
    }  
   else  
   {%>  
   <center> Details have not been inserted!!!!!!!!!</center>  
   <%  }  %>  
     </table>  
 </body>  
 </html>

Tags:

Sql Example

Recent Posts