3 Replies - 295 Views - Last Post: 06 August 2019 - 11:20 AM

#1 aneehunk22   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 06-August 19

How to upload en excel file(xlsx, xls only) into MySQL using JSP

Posted 06 August 2019 - 09:07 AM

<%@page import="java.sql.Statement"%>
<%@page import="java.util.ArrayList"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.util.Date"%>
<%@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="java.io.File"%>
<%@page import="org.apache.commons.io.FilenameUtils"%>
<%@page import="org.apache.commons.fileupload.FileItem"%>
<%@page import="java.util.Iterator"%>
<%@page import="java.util.List"%>
<%@page import="org.apache.commons.fileupload.servlet.ServletFileUpload"%>
<%@page import="org.apache.commons.fileupload.disk.DiskFileItemFactory"%>
<%@page import="org.apache.commons.fileupload.FileItemFactory"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="javax.sql.*" %>

<!DOCTYPE html>
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>PRINT DATA FROM EXCEL FILE</title>
</head>
<body>
    <%
    try{
        String name,age,address=null;
       Class.forName("com.mysql.jdbc.Driver");
       Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/clarivateapp","root","[email protected]");

       con.setAutoCommit(false);
       PreparedStatement pstm = null ;
       [color="#FF00FF"]FileInputStream input = new FileInputStream("C:/yourexcelfile.xls");
       POIFSFileSystem fs = new POIFSFileSystem(input); //creating a new poi reference to the given excel file
       HSSFWorkbook wb = new HSSFWorkbook(fs);
       HSSFSheet sheet = wb.getSheetAt(0);[/color]
       Row row;
       Statement st=con.createStatement();
       for(int i=1; i<=sheet.getLastRowNum(); i++){  //points to the starting of excel i.e excel first row
           row = (Row) sheet.getRow(i);  //sheet number

                  if( row.getCell(0)==null) { name = "0";}  //suppose excel cell is empty then its set to 0 the variable
                  else name = row.getCell(0).toString();   //else copies cell data to name variable


                  if( row.getCell(1)==null) { age = "0"; }
                  else age= row.getCell(1).toString();

                  if( row.getCell(2)==null) { address = "0";   }
                  else  address   = row.getCell(2).toString();





    String sq="INSERT INTO workflow(Client_Name,Sender_Name,Subject) VALUES('"+name+"','"+age+"','"+address+"')";                   

           pstm = (PreparedStatement) con.prepareStatement(sq);//here we are using prepared statement because we are calling this statement for each row
           pstm.execute();
           System.out.println("Import rows "+i);
       }
       con.commit();
       pstm.close();
       con.close();
       input.close();
       System.out.println("Success import excel to mysql table");

   }
    
   catch(ClassNotFoundException e){
       out.println(e);
   }catch(SQLException ex){
      out.println(ex);
   }catch(IOException ioe){
      out.println(ioe);
   }


%>
  </body>
 </html>






one more think, how can I share user input for file location?
I Got an error in these four line below:
FileInputStream input = new FileInputStream("C:/yourexcelfile.xls");
POIFSFileSystem fs = new POIFSFileSystem(input); //creating a new poi reference to the given excel file
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);


Is This A Good Question/Topic? 0
  • +

Replies To: How to upload en excel file(xlsx, xls only) into MySQL using JSP

#2 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,276
  • Joined: 12-December 12

Re: How to upload en excel file(xlsx, xls only) into MySQL using JSP

Posted 06 August 2019 - 09:21 AM

What are you asking? What happens when you run that code and what are the exact error details?
Was This Post Helpful? 0
  • +
  • -

#3 aneehunk22   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 06-August 19

Re: How to upload en excel file(xlsx, xls only) into MySQL using JSP

Posted 06 August 2019 - 09:32 AM

I want upload an excel file into MySQL database but excel should not in CSV format.

Error





HTTP Status 500 Internal Server Error


Type Exception Report

Message Unable to compile class for JSP:

Description The server encountered an unexpected condition that prevented it from fulfilling the request.

Exception
org.apache.jasper.JasperException: Unable to compile class for JSP:

An error occurred at line: [19] in the generated java file: [C:\Aneeket\Macro 2019\Web\Eclipse\.metadata\.plugins\org.eclipse.wst.server.core\tmp0\work\Catalina\localhost\Clarivate\org\apache\jsp\CA\upload2_jsp.java]
Only a type can be imported. org.apache.poi.ss.usermodel.Cell resolves to a package

An error occurred at line: [20] in the generated java file: [C:\Aneeket\Macro 2019\Web\Eclipse\.metadata\.plugins\org.eclipse.wst.server.core\tmp0\work\Catalina\localhost\Clarivate\org\apache\jsp\CA\upload2_jsp.java]
Only a type can be imported. org.apache.poi.ss.usermodel.Row resolves to a package

An error occurred at line: [21] in the generated java file: [C:\Aneeket\Macro 2019\Web\Eclipse\.metadata\.plugins\org.eclipse.wst.server.core\tmp0\work\Catalina\localhost\Clarivate\org\apache\jsp\CA\upload2_jsp.java]
Only a type can be imported. org.apache.poi.xssf.usermodel.XSSFSheet resolves to a package

An error occurred at line: [22] in the generated java file: [C:\Aneeket\Macro 2019\Web\Eclipse\.metadata\.plugins\org.eclipse.wst.server.core\tmp0\work\Catalina\localhost\Clarivate\org\apache\jsp\CA\upload2_jsp.java]
Only a type can be imported. org.apache.poi.xssf.usermodel.XSSFWorkbook resolves to a package

An error occurred at line: [24] in the generated java file: [C:\Aneeket\Macro 2019\Web\Eclipse\.metadata\.plugins\org.eclipse.wst.server.core\tmp0\work\Catalina\localhost\Clarivate\org\apache\jsp\CA\upload2_jsp.java]
Only a type can be imported. org.apache.commons.io.FilenameUtils resolves to a package

An error occurred at line: [25] in the generated java file: [C:\Aneeket\Macro 2019\Web\Eclipse\.metadata\.plugins\org.eclipse.wst.server.core\tmp0\work\Catalina\localhost\Clarivate\org\apache\jsp\CA\upload2_jsp.java]
Only a type can be imported. org.apache.commons.fileupload.FileItem resolves to a package

An error occurred at line: [28] in the generated java file: [C:\Aneeket\Macro 2019\Web\Eclipse\.metadata\.plugins\org.eclipse.wst.server.core\tmp0\work\Catalina\localhost\Clarivate\org\apache\jsp\CA\upload2_jsp.java]
Only a type can be imported. org.apache.commons.fileupload.servlet.ServletFileUpload resolves to a package

An error occurred at line: [29] in the generated java file: [C:\Aneeket\Macro 2019\Web\Eclipse\.metadata\.plugins\org.eclipse.wst.server.core\tmp0\work\Catalina\localhost\Clarivate\org\apache\jsp\CA\upload2_jsp.java]
Only a type can be imported. org.apache.commons.fileupload.disk.DiskFileItemFactory resolves to a package

An error occurred at line: [30] in the generated java file: [C:\Aneeket\Macro 2019\Web\Eclipse\.metadata\.plugins\org.eclipse.wst.server.core\tmp0\work\Catalina\localhost\Clarivate\org\apache\jsp\CA\upload2_jsp.java]
Only a type can be imported. org.apache.commons.fileupload.FileItemFactory resolves to a package

An error occurred at line: [37] in the jsp file: [/CA/upload2.jsp]
FileInputStream cannot be resolved to a type
34:
35: con.setAutoCommit(false);
36: PreparedStatement pstm = null ;
37: FileInputStream input = new FileInputStream("C:/yourexcelfile.xls");
38: POIFSFileSystem fs = new POIFSFileSystem(input); //creating a new poi reference to the given excel file
39: HSSFWorkbook wb = new HSSFWorkbook(fs);
40: HSSFSheet sheet = wb.getSheetAt(0);


An error occurred at line: [37] in the jsp file: [/CA/upload2.jsp]
FileInputStream cannot be resolved to a type
34:
35: con.setAutoCommit(false);
36: PreparedStatement pstm = null ;
37: FileInputStream input = new FileInputStream("C:/yourexcelfile.xls");
38: POIFSFileSystem fs = new POIFSFileSystem(input); //creating a new poi reference to the given excel file
39: HSSFWorkbook wb = new HSSFWorkbook(fs);
40: HSSFSheet sheet = wb.getSheetAt(0);


An error occurred at line: [38] in the jsp file: [/CA/upload2.jsp]
POIFSFileSystem cannot be resolved to a type
35: con.setAutoCommit(false);
36: PreparedStatement pstm = null ;
37: FileInputStream input = new FileInputStream("C:/yourexcelfile.xls");
38: POIFSFileSystem fs = new POIFSFileSystem(input); //creating a new poi reference to the given excel file
39: HSSFWorkbook wb = new HSSFWorkbook(fs);
40: HSSFSheet sheet = wb.getSheetAt(0);
41: Row row;


An error occurred at line: [38] in the jsp file: [/CA/upload2.jsp]
POIFSFileSystem cannot be resolved to a type
35: con.setAutoCommit(false);
36: PreparedStatement pstm = null ;
37: FileInputStream input = new FileInputStream("C:/yourexcelfile.xls");
38: POIFSFileSystem fs = new POIFSFileSystem(input); //creating a new poi reference to the given excel file
39: HSSFWorkbook wb = new HSSFWorkbook(fs);
40: HSSFSheet sheet = wb.getSheetAt(0);
41: Row row;


An error occurred at line: [39] in the jsp file: [/CA/upload2.jsp]
HSSFWorkbook cannot be resolved to a type
36: PreparedStatement pstm = null ;
37: FileInputStream input = new FileInputStream("C:/yourexcelfile.xls");
38: POIFSFileSystem fs = new POIFSFileSystem(input); //creating a new poi reference to the given excel file
39: HSSFWorkbook wb = new HSSFWorkbook(fs);
40: HSSFSheet sheet = wb.getSheetAt(0);
41: Row row;
42: Statement st=con.createStatement();


An error occurred at line: [39] in the jsp file: [/CA/upload2.jsp]
HSSFWorkbook cannot be resolved to a type
36: PreparedStatement pstm = null ;
37: FileInputStream input = new FileInputStream("C:/yourexcelfile.xls");
38: POIFSFileSystem fs = new POIFSFileSystem(input); //creating a new poi reference to the given excel file
39: HSSFWorkbook wb = new HSSFWorkbook(fs);
40: HSSFSheet sheet = wb.getSheetAt(0);
41: Row row;
42: Statement st=con.createStatement();


An error occurred at line: [40] in the jsp file: [/CA/upload2.jsp]
HSSFSheet cannot be resolved to a type
37: FileInputStream input = new FileInputStream("C:/yourexcelfile.xls");
38: POIFSFileSystem fs = new POIFSFileSystem(input); //creating a new poi reference to the given excel file
39: HSSFWorkbook wb = new HSSFWorkbook(fs);
40: HSSFSheet sheet = wb.getSheetAt(0);
41: Row row;
42: Statement st=con.createStatement();
43: for(int i=1; i<=sheet.getLastRowNum(); i++){ //points to the starting of excel i.e excel first row


An error occurred at line: [41] in the jsp file: [/CA/upload2.jsp]
Row cannot be resolved to a type
38: POIFSFileSystem fs = new POIFSFileSystem(input); //creating a new poi reference to the given excel file
39: HSSFWorkbook wb = new HSSFWorkbook(fs);
40: HSSFSheet sheet = wb.getSheetAt(0);
41: Row row;
42: Statement st=con.createStatement();
43: for(int i=1; i<=sheet.getLastRowNum(); i++){ //points to the starting of excel i.e excel first row
44: row = (Row) sheet.getRow(i); //sheet number


An error occurred at line: [44] in the jsp file: [/CA/upload2.jsp]
Row cannot be resolved to a type
41: Row row;
42: Statement st=con.createStatement();
43: for(int i=1; i<=sheet.getLastRowNum(); i++){ //points to the starting of excel i.e excel first row
44: row = (Row) sheet.getRow(i); //sheet number
45:
46: if( row.getCell(0)==null) { name = "0";} //suppose excel cell is empty then its set to 0 the variable
47: else name = row.getCell(0).toString(); //else copies cell data to name variable


An error occurred at line: [78] in the jsp file: [/CA/upload2.jsp]
IOException cannot be resolved to a type
75: out.println(e);
76: }catch(SQLException ex){
77: out.println(ex);
78: }catch(IOException ioe){
79: out.println(ioe);
80: }
81:


Stacktrace:
org.apache.jasper.compiler.DefaultErrorHandler.javacError(DefaultErrorHandler.java:103)
org.apache.jasper.compiler.ErrorDispatcher.javacError(ErrorDispatcher.java:213)
org.apache.jasper.compiler.JDTCompiler.generateClass(JDTCompiler.java:522)
org.apache.jasper.compiler.Compiler.compile(Compiler.java:392)
org.apache.jasper.compiler.Compiler.compile(Compiler.java:362)
org.apache.jasper.compiler.Compiler.compile(Compiler.java:346)
org.apache.jasper.JspCompilationContext.compile(JspCompilationContext.java:603)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:399)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:385)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:329)
javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)


Note The full stack trace of the root cause is available in the server logs.


Apache Tomcat/9.0.19
Was This Post Helpful? 0
  • +
  • -

#4 g00se   User is online

  • D.I.C Lover
  • member icon

Reputation: 3643
  • View blog
  • Posts: 16,753
  • Joined: 20-September 08

Re: How to upload en excel file(xlsx, xls only) into MySQL using JSP

Posted 06 August 2019 - 11:20 AM

Leaving aside the many errors, why would you want to put a spreadsheet into a database? It will cause you a lot of hassle
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1