0

i'm doing one task where i want to upload excel data in oracle database but when i try to run code and after upload .xls file its giving me file not found exception D:\GODBFILES\NETBEANS PROJECTS\NetBeansProjects\TestWebApplication\build\web\null (The system cannot find the file specified), my concern is from any location file should be upload in db I updated code in fileinputstream im write hard coded path and file name but i dont want this filepath and filename should dynamically fetch and store in file

for uploading file i'm using below jar

commons-fileupload-1.3.jar, commons-io-2.4.jar, poi-3.9.jar, cos.jar, poi-ooxml-3.9.xml

<html>
    <head>
        <title>TODO supply a title</title>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
    </head>
    <body>
        <form id="f1" enctype="multipart/form-data" action="xlsUpload_01.jsp" method="post">
            <table align="center" border="1">
                <tr>
                    <td>Enter File name</td>
                    <td><input type="text" name="id"></td>

                </tr>
                <tr>
                    <td>Select File</td>
                     <td><input type="file"  name="xlsfile" />
                </tr>

            </table>
            <p>
            <center>
                <input align="center" type="submit" value="Upload File" name="btnsubmit"/>
            </center>

            </p>
        </form>
    </body>
</html>

xlsupload_01.jsp

    <%@ page language="java" contentType="text/html; charset=UTF-8"
            pageEncoding="UTF-8"%>
         <%@page import="java.sql.*" %>
        <%@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="org.apache.poi.hssf.usermodel.*" %>
        <%@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" %>
        <%@page import="org.apache.poi.ss.usermodel.Cell" %>
        <%@page import ="org.apache.poi.ss.usermodel.Row"%>
        <%@page import="org.apache.poi.ss.usermodel.Sheet" %>
        <%@page import="org.apache.poi.ss.usermodel.Workbook" %>
        <%@page import="com.oreilly.servlet.MultipartRequest" %>
        <%@page import="org.apache.poi.xssf.usermodel.*"%>
        <%@page import="org.apache.poi.xssf.usermodel.XSSFWorkbook"%>

         <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
    <!DOCTYPE html>
    <html>
        <head>
            <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
            <title>JSP Page</title>
        </head>
        <body>
      <%

     ArrayList CellArrayListHolder=new ArrayList();
     Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con=DriverManager.getConnection("jdbc:oracle:thin:@172.18.114.213:1821:xe","se","Spacess");


 **FileInputStream file=new FileInputStream(new File("D:\\GODBFILES\\NETBEANS PROJECTS\\upload\\hello.xlsx"));
XSSFWorkbook workbook=new XSSFWorkbook(file);**

    Sheet firstSheet=workbook.getSheetAt(0);
    Iterator<Row> iterator=firstSheet.iterator();
    int count=0;
    while(iterator.hasNext())
    {
        XSSFRow nextrow=(XSSFRow)iterator.next();
        ArrayList rowarraylist=new ArrayList();
        Iterator<Cell> cellIterator=nextrow.cellIterator();

        while(cellIterator.hasNext())
        {
            XSSFCell cell=(XSSFCell)cellIterator.next();
            rowarraylist.add(cell);
        }
        CellArrayListHolder.add(rowarraylist);
    }
           out.println(CellArrayListHolder);
           ArrayList rowarraylist=null;
           PreparedStatement st=con.prepareStatement("insert into DYNAMIC_INSERT values(?)");

    for(int i=1;i<CellArrayListHolder.size();i++)
    {
        rowarraylist=(ArrayList)CellArrayListHolder.get(i);
        st.setString(1, rowarraylist.get(0).toString());
        //st.executeUpdate();
        count=st.executeUpdate();

    }
    if(count>0)
    {
           out.println("<script type=\"text/javascript\">");
       out.println("alert('File added');");
       out.println("</script>");    
    }




      %>
        </body>
    </html>
thatjeffsmith
  • 20,522
  • 6
  • 37
  • 120
Atul
  • 11
  • 8
  • You didn't post any of the fileupload code that is landing the uploaded file to the filesystem. See this Q on uploading : https://stackoverflow.com/questions/2422468/how-to-upload-files-to-server-using-jsp-servlet – Kris Rice May 29 '18 at 14:10

1 Answers1

0

Here i got solution 

 <%@page import="java.util.List"%>
<%@page import="org.apache.commons.fileupload.FileItem"%>
<%@page import="org.omg.PortableServer.Servant"%>
<%@page import="org.apache.commons.fileupload.FileItemFactory"%>
<%@page import="java.nio.file.Paths"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
        pageEncoding="UTF-8"%>
     <%@page import="java.sql.*" %>
    <%@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="org.apache.poi.hssf.usermodel.*" %>
    <%@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" %>
    <%@page import="org.apache.poi.ss.usermodel.Cell" %>
    <%@page import ="org.apache.poi.ss.usermodel.Row"%>
    <%@page import="org.apache.poi.ss.usermodel.Sheet" %>
    <%@page import="org.apache.poi.ss.usermodel.Workbook" %>
    <%@page import="com.oreilly.servlet.MultipartRequest" %>
    <%@page import="org.apache.poi.xssf.usermodel.*"%>
    <%@page import="org.apache.poi.xssf.usermodel.XSSFWorkbook"%>
    <%@page import="org.apache.commons.fileupload.disk.DiskFileItemFactory"%>
<%@page import="org.apache.commons.fileupload.servlet.ServletFileUpload"%>


     <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
  <%

    try
    {
 ArrayList CellArrayListHolder=new ArrayList();
 Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection con=DriverManager.getConnection("jdbc:oracle:thin:@172.18.114.213:1871:godb","xe","se");
      String contentType = request.getContentType();
if ((contentType != null) && (contentType.indexOf("multipart/form-data") >= 0)) {
DataInputStream in = new DataInputStream(request.getInputStream());
int formDataLength = request.getContentLength();
byte dataBytes[] = new byte[formDataLength];
int byteRead = 0;
int totalBytesRead = 0;
while (totalBytesRead < formDataLength) {
byteRead = in.read(dataBytes, totalBytesRead,formDataLength);
totalBytesRead += byteRead;
}
String file = new String(dataBytes);
String saveFile = file.substring(file.indexOf("filename=\"") + 10);
saveFile = saveFile.substring(0, saveFile.indexOf("\n"));
saveFile = saveFile.substring(saveFile.lastIndexOf("\\") + 1,saveFile.indexOf("\""));
out.println(saveFile);
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;

FileOutputStream fileOut = new FileOutputStream(saveFile);
fileOut.write(dataBytes, startPos, (endPos - startPos));
fileOut.flush();
fileOut.close();

%>
<%

PreparedStatement psmnt = null;
FileInputStream fis;
//try {
//Class.forName("com.mysql.jdbc.Driver").newInstance();
//connection = DriverManager.getConnection(connectionURL, "root", "root");
File file1 = new File(saveFile);
FileInputStream file_inut=new FileInputStream(file1);
XSSFWorkbook workbook=new XSSFWorkbook(file_inut);
Sheet firstsheet=workbook.getSheetAt(0);
Iterator<Row> iterator=firstsheet.iterator();
int count=0;
while(iterator.hasNext())
{
   XSSFRow nextrow=(XSSFRow)iterator.next();
    ArrayList rowarraylist=new ArrayList();
    Iterator<Cell> cellIterator=nextrow.cellIterator();

    while(cellIterator.hasNext())
    {
        XSSFCell cell=(XSSFCell)cellIterator.next();
        rowarraylist.add(cell);
    }
    CellArrayListHolder.add(rowarraylist);
}
       out.println(CellArrayListHolder);
       ArrayList rowarraylist=null;
       PreparedStatement st=con.prepareStatement("insert into DYNAMIC_INSERT values(?)");

for(int i=1;i<CellArrayListHolder.size();i++)
{
    rowarraylist=(ArrayList)CellArrayListHolder.get(i);
     //st.setString(1, file_name);
    st.setString(1, rowarraylist.get(0).toString());
    //st.executeUpdate();
    count=st.executeUpdate();

}
if(count>0)
{
       out.println("<script type=\"text/javascript\">");
   out.println("alert('File added');");
      out.println("location='xlsUpload.html';");
   out.println("</script>");    
}
}
}
catch(Exception ex)
{
 out.println("<script type=\"text/javascript\">");
   out.println("alert('File not found');");
      out.println("location='xlsUpload.html';");
   out.println("</script>");    

}


  %>
    </body>
</html>
Atul
  • 11
  • 8