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 an index.jsp page that passes 3 values to the Newservlet1 ,
    ie. franchise name, month and year..using this servlet , it accesses two databases that then return data and its displayed as an excel file. however when i run the project i only see a blank excel worksheet.. why ?
    package mighty;

    import java.io.FileOutputStream;
    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 javax.servlet.ServletConfig;
    import javax.servlet.ServletException;
    import javax.servlet.annotation.WebServlet;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;


    @WebServlet(name = "NewServlet1", urlPatterns = {"/NewServlet1"})
    public class NewServlet1 extends HttpServlet {
    @Override
    public void init(ServletConfig config) throws ServletException {
    super.init(config);
    }
    @Override
    public void destroy() {
    }
    //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;
    }

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

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
    response.setContentType("application/vnd.ms-excel");

    HSSFWorkbook wb = new HSSFWorkbook ();
    HSSFSheet sheet = wb.createSheet("new sheet");

    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");

    //Array List to pint franchise number, month , year according to the user input
    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
    String query4 = "Select corpname from franchise where frannum = '"+franno+"'order byfranno";
    System.out.println("query4 " + query4);

    //array list to print ESR VALUES

    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) = '"+month+"' and date_part('year',inv_date) = '"+year+"' and franno::integer in ('"+franno+"') group by franno, salesmonth,salesyear order by franno";
    System.out.println("query2 " + query2);

    //Array List to print Reported Values

    String query3 = "select franno,sum(territory +custodial) as reported,salesmonth,salesyear from salesdata,salesperson where salesperson.mightyspid = salesdata.mightyspid and salesmonth = '"+month+"' and salesyear = '"+year+"' franno::integer in ('"+franno+"') group by franno,salesmonth,salesyear order by franno";
    System.out.println("query3 " + query3);


    //ArraylIST TO Print difference in ESR-REPORTED
    //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();

    HSSFRow row = sheet.createRow((short)0);
    HSSFRow rowhead = sheet.createRow((short)0);

    rowhead.createCell((short)0).setCellValue ("Franchise Name");
    rowhead.createCell((short)1).setCellValue ("Franchise Number");
    rowhead.createCell((short)2).setCellValue("Month");
    rowhead.createCell((short)3).setCellValue ("Year");
    rowhead.createCell((short)4).setCellValue ("ESR Values");
    rowhead.createCell((short)5).setCellValue ("Reported Values");
    rowhead.createCell((short)6).setCellValue ("Differnce");


    ResultSet rs = st.executeQuery(query);
    int index= 1;
    while(rs.next()){
    //CREATE CELLS
    HSSFCell cell = row.createCell(0);
    HSSFCell cell5 = row.createCell(5);
    HSSFCell cell6 = row.createCell(6);

    cell.setCellValue(rs.getString(1));
    cell5.setCellValue(rs.getString(3));
    cell6.setCellValue(rs.getString(4));
    index++;
    }
    int index2 = 1;
    ResultSet rs2 = st2.executeQuery(query2);
    while (rs2.next() ){
    // HSSFRow row = sheet.createRow((short)0);
    HSSFCell cell2 = row.createCell(2);
    cell2.setCellValue(rs2.getString(5));
    index2++;
    }

    int index3 = 1;
    ResultSet rs3 = st3.executeQuery(query3);
    while (rs3.next() ){
    HSSFCell cell3 = row.createCell(3);
    cell3.setCellValue(rs3.getString(6));
    index3++;
    }

    int index4= 1;
    ResultSet rs4 = st4.executeQuery(query4);
    while (rs4.next()){
    HSSFCell cell4 = row.createCell(4);
    cell4.setCellValue(rs4.getString(2));
    index4++;
    }

    int index5= 1;
    ResultSet rs5 = st5.executeQuery(query5);
    while (rs5.next()){
    HSSFCell cell7 = row.createCell(7);
    cell7.setCellValue(rs5.getString(7));
    index4++;
    index5++;
    }

    FileOutputStream fileOut = new FileOutputStream("c:\\excelFile.xls");
    wb.write(fileOut);
    fileOut.close();
    out.println("Data is saved in excel file.");
    con.close();
    conn.close();
    System.out.println("Disconnected from database");
    } catch (Exception e) {
    }
    }

    @Override
    public String getServletInfo() {
    return "report for Mighty Auto!!!!";
    }// </editor-fold>
    }


    index.jsp page
    <%@page contentType="text/html" pageEncoding="UTF-8"%>
    <!DOCTYPE html>
    <html>
    <body>
    <form action="NewServlet1" method="post">

    <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>
  2. #2
  3. Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    May 2004
    Location
    Superior, CO, USA
    Posts
    2,418
    Rep Power
    1713
    What have you done to debug it? Have you tried to print out the data before you put it into the spreadsheet?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    34
    Rep Power
    2
    Originally Posted by stdunbar
    What have you done to debug it? Have you tried to print out the data before you put it into the spreadsheet?
    thank you for your reply
    yes i have tried it doesn't show anything!
  6. #4
  7. Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    May 2004
    Location
    Superior, CO, USA
    Posts
    2,418
    Rep Power
    1713
    Ok, so what next? I'd go through every one of your queries and understand if they are outputting data. Make a copy of this file and get rid of all of the POI stuff - it's just in the way for now. Make sure you're getting the correct data from the database first and then, once it is, add the POI stuff back in.

    And, as a help, do not ever, ever, ever do this:

    java Code:
    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;


    This is the best possible way to drive yourself crazy. This code could be failing and you wouldn't know it. Yes, you'd get a NullPointerException but you won't know why. At the very least, change it to:

    java Code:
    try{
        Class.forName("org.postgresql.Driver").newInstance();
        con = DriverManager.getConnection("jdbcostgresql://216.77.96.20:5432/ESR" + db ,"postgres","postgres");
     
    }
    catch (Exception ex){
        ex.printStackTrace();
    }
    return con;


    so you can see if you catch any exceptions. There is rarely a good reason to ignore exceptions.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    34
    Rep Power
    2
    Originally Posted by stdunbar
    Ok, so what next? I'd go through every one of your queries and understand if they are outputting data. Make a copy of this file and get rid of all of the POI stuff - it's just in the way for now. Make sure you're getting the correct data from the database first and then, once it is, add the POI stuff back in.
    thank you i will review my code in some few hours i really appreciate it.

    And, as a help, do not ever, ever, ever do this:

    java Code:
    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;


    This is the best possible way to drive yourself crazy. This code could be failing and you wouldn't know it. Yes, you'd get a NullPointerException but you won't know why. At the very least, change it to:

    java Code:
    try{
        Class.forName("org.postgresql.Driver").newInstance();
        con = DriverManager.getConnection("jdbcostgresql://216.77.96.20:5432/ESR" + db ,"postgres","postgres");
     
    }
    catch (Exception ex){
        ex.printStackTrace();
    }
    return con;


    so you can see if you catch any exceptions. There is rarely a good reason to ignore exceptions.

    thank you very much i will change my code in the morning since right now i can not access the databases. I really appreciate or
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    34
    Rep Power
    2

    this is what i have done and its not working


    Originally Posted by nicholas.omosa
    thank you very much i will change my code in the morning since right now i can not access the databases. I really appreciate or
    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){
    ex.printStackTrace();
    }
    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){
    ex.printStackTrace();
    }
    return conn;
    }
  12. #7
  13. Java Junkie
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2004
    Location
    Mobile, Alabama
    Posts
    4,022
    Rep Power
    1285
    Could you elaborate on what you mean by not working?
  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
    Could you elaborate on what you mean by not working?
    when i run the index.jsp it prompts me to enter three things :
    1)franchise number.
    2)month //this is sales month
    3)year //this is sales year.

    after i input the three and press submit it takes me to NewServlet1 but it shows a plain white page..
    it should show me

    the franchise number, month , year, reported sales, etc.
  16. #9
  17. Java Junkie
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2004
    Location
    Mobile, Alabama
    Posts
    4,022
    Rep Power
    1285
    Did you replace the entire NewServlet.java with what you posted earlier?
  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
    Did you replace the entire NewServlet.java with what you posted earlier?
    i did i replaced the whole thing , the earlier one had issues with the web.xml so i created a new project and it seemed to working till this point where its not displaying any records
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    34
    Rep Power
    2
    Originally Posted by nicholas.omosa
    i did i replaced the whole thing , the earlier one had issues with the web.xml so i created a new project and it seemed to working till this point where its not displaying any records
    please tell me why this exel file is not displaying any records
  22. #12
  23. 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
    please tell me why this exel file is not displaying any records
    At the points where you execute the query, if you use System.out.println on them and check the logs file, what shows up?
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    34
    Rep Power
    2
    Originally Posted by bullet
    At the points where you execute the query, if you use System.out.println on them and check the logs file, what shows up?
    the main point is nothing shows up.nothing is displayed
  26. #14
  27. Java Junkie
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2004
    Location
    Mobile, Alabama
    Posts
    4,022
    Rep Power
    1285
    If you write a regular Java application to query the database, do you see the data then?
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    34
    Rep Power
    2
    Originally Posted by bullet
    If you write a regular Java application to query the database, do you see the data then?

    yes i do
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo