title: How to store data of excel by browsing the file in mysql database table using jsp
body: <p>I'm new to Apache Poi and File handling java.So i want some help</p>
  
  <p>I had done by excel file path in the code but now i want to do without giving filepath in code.</p>
  
  <p>So now my question is
  How to store data of excel by browsing the file in mysql database table using jsp???</p>
  
  <p>I want to select the excel file by browsing the computer.</p>
  
  <p><strong>index.jsp</strong></p>
  
  <pre><code>&lt;body&gt;
    &lt;form action="upload.jsp" method="post" enctype="multipart/form-data"&gt;
          &lt;table&gt;
              &lt;tr&gt;
                  &lt;td &gt;&lt;B&gt;UPLOAD THE FILE&lt;/B&gt;&lt;/td&gt;
              &lt;/tr&gt;
              &lt;tr&gt;
                  &lt;td&gt;&lt;b&gt;Choose the file To Upload:&lt;/b&gt;&lt;/td&gt;
                  &lt;td&gt;&lt;INPUT NAME="file" TYPE="file"&gt;&lt;/td&gt;
              &lt;/tr&gt;
              &lt;tr&gt;
                  &lt;td&gt;&lt;input type="submit" value="Upload File"&gt; &lt;/td&gt;
              &lt;/tr&gt;
          &lt;/table&gt;
    &lt;/form&gt;
  &lt;/body&gt;
  </code></pre>
  
  <p><strong>upload.jsp</strong></p>
  
  <pre><code>&lt;body&gt;
  
  &lt;%@ page import="java.io.*" %&gt;
  &lt;%@ page import="java.sql.*" %&gt;
  &lt;%@ page import="java.util.*"%&gt;
  
  
  &lt;%@ page import="org.apache.poi.ss.usermodel.*"%&gt;
  &lt;%@ page import="org.apache.poi.xssf.usermodel.XSSFSheet"%&gt;
  &lt;%@ page import="org.apache.poi.xssf.usermodel.XSSFWorkbook"%&gt;
  
  &lt;%
      String saveFile="";
      String contentType = request.getContentType();
  
      if ((contentType != null) &amp;&amp; (contentType.indexOf("multipart/form-data") &gt;= 0))
      {
          DataInputStream in = new DataInputStream(request.getInputStream());
          int formDataLength = request.getContentLength();
          byte dataBytes[] = new byte[formDataLength];
          int byteRead = 0;
          int totalBytesRead = 0;
  
          while (totalBytesRead &lt; formDataLength)
          {
              byteRead = in.read(dataBytes, totalBytesRead,formDataLength);
              totalBytesRead += byteRead;
          }
  
          String file = new String(dataBytes);
  
          saveFile = file.substring(file.indexOf("filename=\"") + 10);
          saveFile = saveFile.substring(0, saveFile.indexOf("\n"));
          saveFile = saveFile.substring(saveFile.lastIndexOf("\\") + 1,saveFile.indexOf("\""));
  
          int lastIndex = contentType.lastIndexOf("=");
  
          String boundary = contentType.substring(lastIndex + 1,contentType.length());
  
          int pos;
  
          pos = file.indexOf("filename=\"");
          pos = file.indexOf("\n", pos) + 1;
          pos = file.indexOf("\n", pos) + 1;
          pos = file.indexOf("\n", pos) + 1;
  
          int boundaryLocation = file.indexOf(boundary, pos) - 4;
  
          int startPos = ((file.substring(0, pos)).getBytes()).length;
  
          int endPos = ((file.substring(0, boundaryLocation)).getBytes()).length;
  
          File ff = new File(saveFile);
  
          FileOutputStream fileOut = new FileOutputStream(ff);
          fileOut.write(dataBytes, startPos, (endPos - startPos));
  
          fileOut.flush();
          fileOut.close();
  %&gt;
  
  &lt;table&gt;
      &lt;tr&gt;
          &lt;td&gt;&lt;b&gt;You have successfully upload the file:&lt;/b&gt;
              &lt;% out.println(saveFile);
              %&gt;
          &lt;/td&gt;
      &lt;/tr&gt;
  &lt;/table&gt;
  
      &lt;%
          Connection connection = null;
          ResultSet resultSet = null;
          PreparedStatement preparedStatement = null;
          FileInputStream fileInputStream;
              Statement stmt = null;
              File f = null;
              int count = 0;
  
          ArrayList&lt;String&gt; mylist = new ArrayList&lt;String&gt;();
  
          try
          {
              Class.forName("com.mysql.jdbc.Driver");  
  
              connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/parag","root","tiger"); 
              System.out.println("connection sucessfulyyy");
  
  
              f = new File(saveFile);
  
              preparedStatement = connection.prepareStatement("insert into exlogin(username) values(?)");
  
              fileInputStream = new FileInputStream(f);
  
              preparedStatement.setBinaryStream(1, (InputStream)fileInputStream, (int)(f.length()));
  
              preparedStatement.executeUpdate();
  
              System.out.println("connection sucessfulyyy");
  
              resultSet = stmt.executeQuery("SELECT COUNT(*) FROM exlogin");
  
               while(resultSet.next())
                   {
  
                           int val = resultSet.getInt(1);
  
                           System.out.println(val);
  
                          count = val+1;
  
                  }
  
                  preparedStatement.setInt(1,count);
  
                  /* We should now load excel objects and loop through the worksheet data */
                      fileInputStream  = new FileInputStream(f);
                      System.out.println("FileInputStream Object created..! "+filePath);
  
                  /* Load workbook */
                  XSSFWorkbook workbook = new XSSFWorkbook (fileInputStream);
                  System.out.println("XSSFWorkbook Object created..! ");
  
                  /* Load worksheet */
                  XSSFSheet sheet = workbook.getSheetAt(0);
                  System.out.println("XSSFSheet Object created..! ");
  
                  // we loop through and insert data
              Iterator&lt;Row&gt; ite = sheet.rowIterator();
                  System.out.println("Row Iterator invoked..! ");
  
                  while(ite.hasNext()) 
                 {
                      Row row = (Row) ite.next(); 
  
                      Iterator&lt;Cell&gt; cellIterator = row.cellIterator();
                      int index = 1;
  
                      while(cellIterator.hasNext()) 
                      {
                          Cell cell = cellIterator.next();
  
                          switch(cell.getCellType()) 
                          { 
                               case Cell.CELL_TYPE_STRING: //handle string columns
                                      preparedStatement.setString(index, cell.getStringCellValue()); 
                                      System.out.println("getting cell value..! "+cell.getStringCellValue());
  
                                        break;
                                case Cell.CELL_TYPE_NUMERIC: //handle double data
  
                                      double i = (double)cell.getNumericCellValue();
  
                                      preparedStatement.setFloat(index, (float) cell.getNumericCellValue());
  
                                      break;
  
                              }
                                 index++;
  
                      }
                  //we can execute the statement before reading the next row
                  preparedStatement.executeUpdate();
                  }
  
                  /* Close input stream */
                     fileInputStream.close();
                     /* Close prepared statement */
                     preparedStatement.close();
  
                     /* Close connection */
                     connection.close();
  
  
  
  
          }
  
          catch(Exception e)
          {
              e.printStackTrace();
          }
      }
      %&gt;
  
  &lt;/body&gt;
  </code></pre>
  
  <p><strong>Database Table</strong></p>
  
  <pre><code>CREATE TABLE exlogin
  (
    username varchar(100)
  );
  </code></pre>
  
  <p><strong>Excel File Data</strong></p>
  
  <pre><code>username
  parag
  ram
  shree
  tejas
  jyoti
  </code></pre>
  
  <p>Thanks for any help</p>