Import excel data to mysql

Question

0.00/5 (No votes)

See more:

I am trying to import the excel data to the myql database. but iam getting error like this:"

org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)

"

What I have tried:

<html>
<head>
 </head>
  <body>

  <form  action ="Excel.jsp"  method="post"  >

    <input type="file" name= file size="20" id="file"  />
      <input type="submit" value="Submit" />
</form>
</body>
 </html>

This is my jsp code:

<%@ 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" %>
 <%@ page import ="java.sql.*" %>

	
	<%@ page import ="java.util.Iterator" %>
	<%@ page import ="org.apache.poi.ss.usermodel.Cell" %>
	<%@ page import ="org.apache.poi.ss.usermodel.Row" %>
	<%@ page import ="org.apache.poi.xssf.usermodel.XSSFSheet" %>
	<%@ page import ="org.apache.poi.xssf.usermodel.XSSFWorkbook" %>
	<%@ 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>
  <%!     
  Connection con;
  PreparedStatement ps=null;
  Statement stmt= 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(new File("C:/excel/f/testExcel.xlsx"));
        FileInputStream file;
		file = new FileInputStream(new File("C:/excel/f/testExcel.xls"));

       /** Create a POIFSFileSystem object**/
       POIFSFileSystem myFileSystem = new POIFSFileSystem(file);

        /** Create a workbook using the File System**/
       // HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        /** Get the first sheet from workbook**/
        // HSSFSheet mySheet = myWorkBook.getSheetAt(0);
        XSSFSheet sheet = workbook.getSheetAt(0);

        /** We now need something to iterate through the cells.**/
            //Iterator rowIter = mySheet.rowIterator();
        	Iterator<Row> rowIterator = sheet.iterator();
         
        /* 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);
        } */
        	while (rowIterator.hasNext())
        	{
        		Row row = rowIterator.next();
        		//For each row, iterate through all the columns
        		Iterator<Cell> cellIterator = row.cellIterator();
        		 ArrayList cellStoreArrayList=new ArrayList();
        		while (cellIterator.hasNext())
        		{
        			Cell cell = cellIterator.next();
        			 cellStoreArrayList.add(cell);
                }
              cellArrayLisstHolder.add(cellStoreArrayList);
         }
        
       }catch (Exception e)
      {
    	   
    	   e.printStackTrace(); 
       }
       return cellArrayLisstHolder;
      }%>
      <%
       String file = request.getParameter("file");

        String fileName=""+file+""; //testExcel.xls Excel File name

           //Read an Excel File and Store in a ArrayList
               ArrayList dataHolder=readExcelFile(fileName);
              //Print the data read
               //printCellDataToConsole(dataHolder);

          try
               {
               Class.forName("com.mysql.jdbc.Driver");
                con=DriverManager.getConnection("jdbc:mysql://localhost:3306/mytestbd","root","baseauto");
                stmt =con.createStatement();
               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,((Cell)cellStoreArrayList.get(0)).toString());
               ps.setString(2,((Cell)cellStoreArrayList.get(1)).toString());
               ps.setString(3,((Cell)cellStoreArrayList.get(2)).toString());
                 count= ps.executeUpdate();
                 //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 j=1;j < dataHolder.size(); j++) {
          cellStoreArrayList=(ArrayList)dataHolder.get(j);%>
            <tr>
                <td><%=((Cell)cellStoreArrayList.get(0)).toString() %></td>
                <td><%=((Cell)cellStoreArrayList.get(1)).toString() %></td>
                <td><%=((Cell)cellStoreArrayList.get(2)).toString() %></td>
           </tr>
         <%}
              }
             else
                {%>
             <center> Details have not been inserted!!!!!!!!!</center>

                       <%    }   
                    }catch(Exception e)
                 {}%>
                </table>

                 </body>
                  </html>

Posted 10-Apr-18 0:21am

Karthik siva

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dinesh Singh Kushwaha (Dev) · Answer 1 · 2018-04-10T01:02:00

Solution 1

These links may help you out :
Import Data from Excel to Mysql Database[^]
Import data Excel files to Database[^]

Posted 10-Apr-18 1:02am

Dinesh Singh Kushwaha (Dev)

Updated 10-Apr-18 2:49am

v2