Skip to main content

how to export data from servlet to excel using poi

Posted by nicholas.omosa on November 10, 2012 at 6:31 PM PST

Summary

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"%>

Search Record

franchise Number:
month:
year:
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("jdbc:postgresql://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("jdbc:postgresql://216.77.96.20:5432/FranchiseDB" + db ,"postgres","postgres"); }catch (Exception ex){ } return conn; } /** * Processes requests for both HTTP * GET and * POST 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 * POST 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!!!!"; }// }