Click here to Skip to main content
14,927,915 members
Please Sign up or sign in to vote.
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

1 solution

   
v2

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900