Insert Data into database from Excel File

Posted by
For inserting Excel file data into database, First we have to read the Excel file.Then insert data into database.
For reading Excel file,we will use Apache POI. Download the POI library from following link:
Extract zip files from this and use following library in your project:

  1. poi-ooxml-schemas-3.8-20120326
  2. poi-3.8-20120326
  3. dom4j-1.6.1
  4. xmlbeans-2.3.0

For MySql connection use mysql-connector-java-5.1.7-bin jar file. Include all these jars into lib folder.
Now  create a java bean for connecting to MySQL database.Its not necessary ,you can also create connection in your jsp page.
Hierarchy of

For inserting Excel file data into database

Code  For

 package DB;  
 import java.sql.Connection;  
 import java.sql.DriverManager;  
 import java.sql.SQLException;  
 public class DB_Connection {  
   private Connection con;  
    public DB_Connection()  
               String conUrl="jdbc:mysql://localhost:3306/MyTestDb";  
               String userName="root";  
               String pass="root";  
         catch(SQLException s)  
         catch(ClassNotFoundException c)  
   public Connection getConn() {  
     return con;  
   public void setConn(Connection con) {  
     this.con = con;  

Now Code for reading excel file and inserting data of  excel file into MySql table i.e. StudentreadExcel2.jsp

 <%@ page language="java" import="java.sql.*" contentType="text/html; charset=ISO-8859-1"  
 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "">  
 <%@ page import ="java.util.Date" %>  
 <%@ page import ="*" %>  
 <%@ page import ="" %>  
 <%@ page import ="" %>  
 <%@ 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" %>  
 <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">  
 <title>Insert title here</title>  
 <jsp:useBean id="connection" class="DB.DB_Connection" scope="page">  
   <jsp:setProperty name="connection" property="*"/>  
 Connection con;  
 PreparedStatement ps=null;  
 public static ArrayList readExcelFile(String fileName)  
   /** --Define a ArrayList  
     --Holds ArrayList Of Cells  
   ArrayList cellArrayLisstHolder = new ArrayList();  
   /** 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();  
      HSSFRow myRow = (HSSFRow);  
      Iterator cellIter = myRow.cellIterator();  
      ArrayList cellStoreArrayList=new ArrayList();  
        HSSFCell myCell = (HSSFCell);  
   }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  
 String query="insert into Student values(?,?,?)";  
 int count=0;  
 ArrayList cellStoreArrayList=null;  
 //For inserting into database  
 for (int i=1;i < dataHolder.size(); i++) {  
    count= ps.executeUpdate();  
     System.out.print(((HSSFCell)cellStoreArrayList.get(2)).toString() + "t");  
 //For checking data is inserted or not?  
     { %>  
         Following deatils from Excel file have been inserted in student table of database  
               <th>Student's Name</th>  
     <% for (int i=1;i < dataHolder.size(); i++) {  
     <td><%=((HSSFCell)cellStoreArrayList.get(0)).toString() %></td>  
     <td><%=((HSSFCell)cellStoreArrayList.get(1)).toString() %></td>  
     <td><%=((HSSFCell)cellStoreArrayList.get(2)).toString() %></td>  
   <center> Details have not been inserted!!!!!!!!!</center>  
   <%  }  %>  

Structure of Student Table:
Create table Student(name varchar(2),class varchar(20),Age varchar(4));

For inserting Excel file data into database

You may also like following posts:

  1. Insert Data into MS Access from Excel
  2. Insertion into multiple tables using stored procedure


  1. Thanks sir for the post, my question is where should i save testExcel.xls file in computer. Please tell me location for saving this file. I am using linux(Fedora 15) operating system.

  2. Where should i save my testExcel.xls file in computer? I am using linux(Fedora 15) operating system. Currently my testExcel.xls file in "/home" location.

  3. I have tried this code bt it is saving only 3 rows data in excel sheet instead of saving all the data. can you please tell me the solution

Leave a Reply