Click here to Skip to main content
14,601,060 members
Rate this:
Please Sign up or sign in to vote.
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

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

   
v2

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100