Jump to content
MarcioSalgado

Importar Excel para base de dados JSP

Recommended Posts

MarcioSalgado

Boa tarde estou a importar dados de um excel para uma base de dados SQL Server, e deparei-me com um problema que é o seguinte eu consigo importar o excel para a base de dados mas so se lhe der o caminho por exemplo "String fileName="C:/teste2.xls";".

O meu problema é o seguinte quando crio um index.jsp com o enctype="multipart/form-data" quando tento receber  no meu excelimport.jsp , este vem null.

e usei request.getParameter("file"); como poderam ver a baixo.

o meu objectivo sera ler o excel e enviar para uma pasta em especifico por exemplo C:/uploadDir/

citando que estou a usar as livrarias apache POI para importar excel e commons-fileupload.

index.jsp

<%-- 
    Document   : index.jsp
    Created on : Oct 16, 2017, 5:50:32 PM
    Author     : Marcio Salgado
--%>

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
   <head>
<title>JSP</title>
</head>
<body>
<form action="excelimport.jsp" method="post" enctype="multipart/form-data" >
<center>
<h1>Upload Excel</h1>
<table>
 <tr><td>File Upload:</td><td><input type="file" name="file"></td></tr>
<tr><td></td><td><input type="submit" value="Submit"/></td></tr>
</table>
</center>
</form>
</body>
</html>

 

excelimport,jsp

<%-- 
    Document   : index
    Created on : Oct 9, 2017, 11:11:52 AM
    Author     : Marcio Salgado
--%> 
<%@page import="java.util.List"%>
<%@page import="org.apache.commons.fileupload.FileItem"%>
<%@page import="org.apache.commons.fileupload.disk.DiskFileItemFactory"%>
<%@page import="org.apache.commons.fileupload.FileItemFactory"%>
<%@page import="org.apache.commons.fileupload.servlet.ServletFileUpload"%>
<%@ page language="java" import="java.sql.*" contentType="text/html; charset=ISO-8859-1"  
   pageEncoding="ISO-8859-1"%>
 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">  
 <%@ 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.Statement" %>

 <%@ 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" %>
 <html>  
 <head>  
 <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">  
 <title>Insert title here</title>  
 </head>  
 <body>  
 <jsp:useBean id="connection" class="DB.DB_Connection" scope="page">  
   <jsp:setProperty name="connection" property="*"/>  
 </jsp:useBean>       
     
     
     
 <%!    
   
 Connection con;  
 PreparedStatement ps=null;  
 ResultSet rs=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(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();  
    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);  
    }  
   }catch (Exception e){e.printStackTrace(); }  
   return cellArrayLisstHolder;  

 }%>  
 


 
 <%  

     
 //request.setAttribute("file", request.getParameter("file"));
 //String fileName = saveFile;
 String fileName = request.getParameter("file");

 //String fileName="C:/teste2.xls";
         //"C:/teste2.xls"; // Excel File name.xls
 //Read an Excel File and Store in a ArrayList  
 ArrayList dataHolder=readExcelFile(fileName);  
 //Print the data read  
 //printCellDataToConsole(dataHolder);  
 con=connection.getConn();  
 String query="insert into teste1 values(?,?,?,?,?,?,?,?,?)";  
 ps=con.prepareStatement(query);  
 int count=0;
 ArrayList cellStoreArrayList=null;  
 //For inserting into database  
 String cmdSelect="";
 for (int i=1;i < dataHolder.size(); i++) {  
   cellStoreArrayList=(ArrayList)dataHolder.get(i);  
   
     String bank = cellStoreArrayList.get(7).toString();
     
     cmdSelect = "SELECT codigo FROM bancos WHERE n_abrev like '%"+bank+"%' ";
     Statement stmt = connection.getConn().createStatement();
     System.out.println(cmdSelect);
     ResultSet rs = stmt.executeQuery(cmdSelect);
     String idBank = "";
     
     while (rs.next()) {
        idBank = rs.getString("codigo");
            }
     ps.setString(1,((HSSFCell)cellStoreArrayList.get(0)).toString());  
     ps.setString(2,((HSSFCell)cellStoreArrayList.get(1)).toString());  
     ps.setString(3,((HSSFCell)cellStoreArrayList.get(2)).toString());
     ps.setString(4,((HSSFCell)cellStoreArrayList.get(3)).toString()); 
     ps.setString(5,((HSSFCell)cellStoreArrayList.get(4)).toString()); 
     ps.setString(6,((HSSFCell)cellStoreArrayList.get(5)).toString()); 
     ps.setString(7,((HSSFCell)cellStoreArrayList.get(6)).toString()); 
     ps.setString(8,idBank); 
     ps.setString(9,((HSSFCell)cellStoreArrayList.get(8)).toString()); 
     count= ps.executeUpdate();  
     System.out.print(((HSSFCell)cellStoreArrayList.get(8)).toString() + "t");  
     }  
 
    


 
 //For checking data is inserted or not?  
   if(count>0)  
     { %>  
     
     

     Foram inseridos na base de dados os seguintes dados.  
           <table>  
             <tr>  
               <th>paciente</th>  
               <th>nome</th>  
               <th>motivo</th>  
               <th>pag</th>  
               <th>cheque</th>  
               <th>conta</th>  
               <th>data</th>  
               <th>banco</th>  
               <th>data Entrega</th>  
             </tr>  
     <% for (int i=1;i < dataHolder.size(); i++) {  
   cellStoreArrayList=(ArrayList)dataHolder.get(i);%>  
   <tr>  
     <td><%=((HSSFCell)cellStoreArrayList.get(0)).toString() %></td>  
     <td><%=((HSSFCell)cellStoreArrayList.get(1)).toString() %></td>  
     <td><%=((HSSFCell)cellStoreArrayList.get(2)).toString() %></td>  
     <td><%=((HSSFCell)cellStoreArrayList.get(3)).toString() %></td>  
     <td><%=((HSSFCell)cellStoreArrayList.get(4)).toString() %></td>  
     <td><%=((HSSFCell)cellStoreArrayList.get(5)).toString() %></td>  
     <td><%=((HSSFCell)cellStoreArrayList.get(6)).toString() %></td>  
     <td><%=((HSSFCell)cellStoreArrayList.get(7)).toString() %></td>  
     <td><%=((HSSFCell)cellStoreArrayList.get(8)).toString() %></td>  
   </tr>  
     <%}  
    }  
   else  
   {%>  
   <center> Details have not been inserted!!!!!!!!!</center>  
   <%  }  %>  
     </table>  
 </body>  
 </html>

 

Edited by MarcioSalgado

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

By using this site you accept our Terms of Use and Privacy Policy. We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.