Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    34
    Rep Power
    2

    How to export data from servlet to excel file through poi lib


    i have a servlet called NewServlet.java that takes user data from index.jsp i.e month , year and franchise num..and queries two database.

    index.jsp

    package mighty;
    <%@page contentType="text/html" pageEncoding="UTF-8"%>
    <!DOCTYPE html>
    <html>
    <form method="post" name="frm" action="NewServlet.java">
    <table border="0" width="300" align="center" bgcolor="#CDFFFF">
    <tr><td colspan=2 style="font-size:12pt;color:#00000;" align="center"><h3>Search Record</h3></td></tr>
    <tr><td ><b>franchise Number</b></td><td>: <input type="text" name="franno" id="franno">
    </td></tr>
    <tr><td ><b>month</b></td><td>: <input type="text" name="month" id="month">
    </td></tr>
    <tr><td ><b>year</b></td><td>: <input type="text" name="year" id="year">
    </td></tr>
    <tr><td colspan=2 align="center"><input type="submit" name="submit" value="Submit"></td></tr>
    </table>
    </form>
    </body>
    </html>


    NewServlet.java

    package mighty;

    import java.io.IOException;
    import java.io.PrintWriter;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.Statement;
    import java.util.ArrayList;
    import javax.servlet.ServletException;
    import javax.servlet.annotation.WebServlet;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;

    /**
    *
    * @author nomosa
    */
    @WebServlet(name = "NewServlet", urlPatterns = {"/NewServlet"})
    public class NewServlet extends HttpServlet {
    //connnects to the first databse (ESR)
    public static Connection getConnection(String db){
    Connection con = null;
    try{
    Class.forName("org.postgresql.Driver").newInstance();
    con = DriverManager.getConnection("jdbcostgresql://216.77.96.20:5432/ESR" + db ,"postgres","postgres");

    }catch (Exception ex){ }
    return con;
    }
    //connects to the second database(FRNACHISE DB)
    public static Connection getConnection2(String db){
    Connection conn = null;
    try{
    Class.forName("org.postgresql.Driver").newInstance();
    conn = DriverManager.getConnection("jdbcostgresql://216.77.96.20:5432/FranchiseDB" + db ,"postgres","postgres");

    }catch (Exception ex){ }
    return conn;
    }
    /**
    * Processes requests for both HTTP
    * <code>GET</code> and
    * <code>POST</code> methods.
    *
    * @param request servlet request
    * @param response servlet response
    * @throws ServletException if a servlet-specific error occurs
    * @throws IOException if an I/O error occurs
    */


    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
    doPost(request, response);
    }

    /**
    * Handles the HTTP
    * <code>POST</code> method.
    *
    * @param request servlet request
    * @param response servlet response
    * @throws ServletException if a servlet-specific error occurs
    * @throws IOException if an I/O error occurs
    */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
    response.setContentType("text/html");
    PrintWriter out = response.getWriter();

    Statement st = null;
    Statement st2= null;
    Statement st3 = null;
    Statement st4 = null;
    Statement st5 = null;

    try {
    System.out.println("Connected to the database");
    String franno = request.getParameter("franno");
    String month = request.getParameter("month");
    String year = request.getParameter("year");

    ArrayList al=null;
    ArrayList al2= null;
    ArrayList al3 = null;
    ArrayList al4 = null;
    ArrayList al5= null;

    //Array List to pint franchise number, month , year according to the user input
    ArrayList difflist =new ArrayList();
    String query = "select * from ticket where franno ='"+franno+"' and month='"+month+"' and year='"+year+"'order byfranno";
    System.out.println("query " + query);

    //Returns names of the franchises based on the farnchise number
    ArrayList difflist4 =new ArrayList();
    String query4 = "Select corpname from franchise where frannum = '"+franno+"'order byfranno";
    System.out.println("query4 " + query4);

    //array list to print ESR VALUES
    ArrayList difflist2 = new ArrayList();
    String query2 = "select franno, sum((ticket.sub_nontax + ticket.sub_tax + ticket.misc + ticket.disc + ticket.core_nontax + ticket.core_tax) - getxlinebycust(ticket.cust_id, ticket.inv_num::integer, date(ticket.inv_date))) AS ear-sales, date_part('month',inv_date) as salesmonth , date_part('year',inv_date) as salesyear from ticket, salesperson where ticket.mightyspid = salesperson.mightyspid and date_part('month',inv_date) = ? and date_part('year',inv_date) = ? and franno::integer in (?) group by franno, salesmonth,salesyear order by franno";
    System.out.println("query2 " + query2);

    //Array List to print Reported Values
    ArrayList difflist3 = new ArrayList();
    String query3 = "select franno,sum(territory +custodial) as reported,salesmonth,salesyear from salesdata,salesperson where salesperson.mightyspid = salesdata.mightyspid and salesmonth = ? and salesyear = ? franno::integer in (?) group by franno,salesmonth,salesyear order by franno";
    System.out.println("query3 " + query3);


    //ArraylIST TO Print difference in ESR-REPORTED
    ArrayList difflist5 = new ArrayList();
    //Note that is not a set difference, and won't output items in result2 that don't exist in result3. It's set subtraction.
    String query5 = "select DISTINCT rs2.column FROM rs21 LEFT JOIN rs2 ON rs3.column = rs2.column WHERE rs2.column IS NULL order by franno";
    System.out.println("query5" + query5);


    Connection con = getConnection("//216.77.96.20/ESR)");
    st = con.createStatement();
    st2 = con.createStatement();
    st3 = con.createStatement();
    st5 = con.createStatement();


    Connection conn= getConnection2("//216.77.96.20:5432/FranchiseDB");
    st4 = conn.createStatement();


    ResultSet rs = st.executeQuery(query);
    while(rs.next()){
    al = new ArrayList();
    al.add(rs.getString(1));
    al.add(rs.getString(3));
    al.add(rs.getString(4));

    System.out.println("al :: "+al);
    difflist.add(al);
    request.setAttribute("difflist",difflist);
    System.out.println("difflist " + difflist);

    ResultSet rs2 = st2.executeQuery(query2);
    while (rs2.next() ){
    al2 = new ArrayList ();
    al.add(rs2.getString(5));
    }
    System.out.println("al2 :: "+al2);
    difflist2.add(al);
    request.setAttribute("difflist2",difflist2);
    System.out.println("difflist2 " + difflist2);


    ResultSet rs3 = st3.executeQuery(query3);
    while (rs3.next() ){
    al3 = new ArrayList ();
    al3.add(rs3.getString(6));
    }
    System.out.println("al3 :: "+al3);
    difflist3.add(al3);
    request.setAttribute("difflist3",difflist3);
    System.out.println("difflist3 " + difflist3);
    }

    ResultSet rs4 = st4.executeQuery(query4);
    while (rs4.next()){
    al4.add(rs4.getString(2));
    }
    System.out.println("al4 :: "+al4);
    difflist.add(al4);
    request.setAttribute("difflist4",difflist4);
    System.out.println("difflist4 " + difflist4);

    ResultSet rs5 = st5.executeQuery(query5);
    while (rs5.next()){
    al5.add(rs5.getString(7));
    }
    System.out.println("al5 :: "+al5);
    difflist.add(al5);
    request.setAttribute("difflist5",difflist5);
    System.out.println("difflist5 " + difflist5);


    out.println("difflist " + difflist);
    out.println("difflist2 " + difflist2);
    out.println("difflist3 " + difflist3);
    out.println("difflist4 " + difflist4);
    out.println("difflist5 " + difflist5);


    con.close();
    conn.close();
    System.out.println("Disconnected from database");
    } catch (Exception e) {
    }
    }

    @Override
    public String getServletInfo() {
    return "Happy thanks Giving to Mighty Auto!!!!";
    }// </editor-fold>
    }
  2. #2
  3. Java Junkie
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2004
    Location
    Mobile, Alabama
    Posts
    4,022
    Rep Power
    1285
    What's the question?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    34
    Rep Power
    2

    the question is


    Originally Posted by bullet
    What's the question?
    when i run the project ..this is the error
    The requested resource (/mighty/NewServlet.java) is not available.

    and i would like the results to be displayed as an excel file .
    Please help.
  6. #4
  7. Java Junkie
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2004
    Location
    Mobile, Alabama
    Posts
    4,022
    Rep Power
    1285
    Originally Posted by nicholas.omosa
    when i run the project ..this is the error
    The requested resource (/mighty/NewServlet.java) is not available.

    and i would like the results to be displayed as an excel file .
    Please help.
    The resource error might be because the servlet was not added to your web.xml file or there may be a misspelling.

    To convert it to Excel, one way is to use the POI libraries to create an Excel file in a writeable area on the server.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    34
    Rep Power
    2

    rely


    Originally Posted by bullet
    The resource error might be because the servlet was not added to your web.xml file or there may be a misspelling.

    To convert it to Excel, one way is to use the POI libraries to create an Excel file in a writeable area on the server.
    now i have just checked my web.xml file it looks pretty fine
    and i have already downloaded the POI from the internet, however i do not know how to use it.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    34
    Rep Power
    2
    Originally Posted by nicholas.omosa
    now i have just checked my web.xml file it looks pretty fine
    and i have already downloaded the POI from the internet, however i do not know how to use it.
    <servlet>
    <servlet-name>NewServlet</servlet-name>
    <servlet-class>mighty.NewServlet</servlet-class>
    </servlet>
    <servlet-mapping>
    <servlet-name>NewServlet</servlet-name>
    <url-pattern>/NewServlet</url-pattern>
    </servlet-mapping>
    <session-config>
    <session-timeout>
    30
    </session-timeout>
    </session-config>
  12. #7
  13. Java Junkie
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2004
    Location
    Mobile, Alabama
    Posts
    4,022
    Rep Power
    1285
    Was NewServlet.java compiled correctly and the class file in the right directory?
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    34
    Rep Power
    2
    Originally Posted by bullet
    Was NewServlet.java compiled correctly and the class file in the right directory?
    well when i try debugging the NewServlet.java file, there is a pop up window that tells me to select servlet execution URI ,
    i have put /NewServlet then when i click ok it tells me Servlet does not have a main method
  16. #9
  17. Java Junkie
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2004
    Location
    Mobile, Alabama
    Posts
    4,022
    Rep Power
    1285
    What URL are you using to invoke the servlet?
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    34
    Rep Power
    2
    Originally Posted by bullet
    What URL are you using to invoke the servlet?
    i think this is where am having the issue because, i dont know what URL to use to invoke my servlet
  20. #11
  21. Java Junkie
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2004
    Location
    Mobile, Alabama
    Posts
    4,022
    Rep Power
    1285
    Originally Posted by nicholas.omosa
    i think this is where am having the issue because, i dont know what URL to use to invoke my servlet
    According to your web.xml file, it should be a URL like

    .../NewServlet

    Where the ... is your web site URL indicating where your servlet container is mounted.
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    34
    Rep Power
    2
    Originally Posted by bullet
    According to your web.xml file, it should be a URL like

    .../NewServlet

    Where the ... is your web site URL indicating where your servlet container is mounted.
    NewServlet is in a project called mighty
    so it that where my servlet container is mounted and if that is the case i have tried running it again and am having an error
  24. #13
  25. Java Junkie
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2004
    Location
    Mobile, Alabama
    Posts
    4,022
    Rep Power
    1285
    Originally Posted by nicholas.omosa
    NewServlet is in a project called mighty
    so it that where my servlet container is mounted and if that is the case i have tried running it again and am having an error
    How are you trying to run it?
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    34
    Rep Power
    2
    Originally Posted by bullet
    How are you trying to run it?

    there are two ways,
    1) i run the main project, then it gives me this error:
    The requested resource (/mighty/NewServlet) is not available.

    2) i run only the servlet then it asks for the url which in this case its mighty/NewServlet ...............mighty is the package name, NewServlet is the servlet name..then it just shows a white page .
  28. #15
  29. Java Junkie
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2004
    Location
    Mobile, Alabama
    Posts
    4,022
    Rep Power
    1285
    Originally Posted by nicholas.omosa
    there are two ways,
    1) i run the main project, then it gives me this error:
    The requested resource (/mighty/NewServlet) is not available.

    2) i run only the servlet then it asks for the url which in this case its mighty/NewServlet ...............mighty is the package name, NewServlet is the servlet name..then it just shows a white page .
    When you say you run, how exactly do you go about running it?
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo