Untitled

<!DOCTYPE html>
 <%@ page import="java.sql.*" %>
 <%ResultSet resultset =null;%>
 <% String month[]={"Jan","Feb","Mar","Apr","May","June","July","Aug","Sep","Oct","Nov","Dec"}; %>
 <% int i=month.length-1; %>
 <% int j=0; %>


 <HTML>
 <HEAD>
<TITLE>REPORT MODULE</TITLE>
<link rel="stylesheet" href="css/jquery-ui.css">
<link rel="stylesheet" href="css/style.css">
 <script src="js/jquery-1.11.1.js"></script>
<script src="js/jquery-ui.js"></script>

<script>
$(function() {
  $( "#tabs" ).tabs();
});
</script>


</HEAD>

<BODY>
<div class="wrapper">
    <div class="container">
        <div id="tabs">
            <ul>
                <li><a href="#Report">Report Module</a></li>

            </ul>
            <div id="Report">

 <%
try{
 Class.forName("org.postgresql.Driver").newInstance();
 Connection connection =
     DriverManager.getConnection
        ("jdbc:postgresql://localhost:2280/CASTCOE3?user=operator&password=CastAIP");

   Statement statement = connection.createStatement() ;

   resultset =statement.executeQuery("select acc_name, project_name from Account") ;

 %>


 <form method="post" action="ExportController">
<label for="acc_Name">Account Name/Project      Name:</label>&nbsp;&nbsp;&nbsp;&nbsp;
    <select name="accountName">
    <%  while(resultset.next()){ %>
        <option><%= resultset.getString(1)+"/"+resultset.getString(2)%></option>
    <% } %>
    </select> <br />
    <label for="month">Month:</label>&nbsp;&nbsp;&nbsp;&nbsp;
    <select name="month">
    <% while(j<=i){ %>
    <option><%= month[j] %></option>
    <% j++; %>
    <% } %>
    </select> <br /> <br />


<input type="submit" name="Export to Excel" value="Export to Excel"></a>


                </form>

            </div>


 <%
 //**Should I input the codes here?**
    }
    catch(Exception e)
    {
         out.println("wrong entry"+e);
    }
 %>


 </BODY>
 </HTML>

package com.coetool.servlet.controller;


 import java.sql.*;
 import java.io.*;

 import javax.servlet.ServletException;


 import javax.servlet.http.HttpServlet;
 import javax.servlet.http.HttpServletRequest;
 import javax.servlet.http.HttpServletResponse;


 import org.apache.poi.hssf.usermodel.HSSFRow;
 import org.apache.poi.hssf.usermodel.HSSFSheet;
 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 import org.apache.poi.ss.usermodel.*;


 public class ExportController extends HttpServlet {


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

    String[] b=new String[5];
    String c=null;
    int e = 0;


    try{
        Class.forName("org.postgresql.Driver");
        Connection connection =
                 DriverManager.getConnection
                    ("jdbc:postgresql://localhost:2280/CASTCOE3?user=operator&password=CastAIP");

               Statement statement = connection.createStatement() ;


               if(request.getParameter("Export to Excel")!=null){

        String AccountName = request.getParameter("accountName");


        b=AccountName.split("/");
        c=b[0];
        String mon=request.getParameter("month");
        String a[]={"Jan","Feb","Mar","Apr","May","June","July","Aug","Sep","Oct","Nov","Dec"};
        int d[]={1,2,3,4,5,6,7,8,9,10,11,12};
        for(int j=0;j<=11;j++){
            if(a[j].equals(mon)){
                e=d[j];
                break;}
            else {
                e=d[j];}}


                   String s="SELECT DISTINCT a.project_id, a.acc_name, a.project_name, a.iot,a.ilc_code, a.active, a.license_no, b.app_fte, b.contact_person, c.cost_call_date from Account a INNER JOIN Application b USING (acc_id) INNER JOIN account_version c USING (acc_id) where a.acc_name="+"'"+c+"'"+"AND EXTRACT (MONTH FROM c.cost_call_date)="+e;

                   ResultSet rs = statement.executeQuery(s);
                    HSSFWorkbook workbook = new HSSFWorkbook();
                    HSSFSheet sheet = workbook.createSheet("Account_Version");
                    HSSFRow rowhead = sheet.createRow((short) 0);
                    rowhead.createCell((short) 0).setCellValue("Project ID");
                    rowhead.createCell((short) 1).setCellValue("Account Name");
                    rowhead.createCell((short) 2).setCellValue("Project Name");
                    rowhead.createCell((short) 3).setCellValue("IOT");
                    rowhead.createCell((short) 4).setCellValue("Account Code");
                    rowhead.createCell((short) 5).setCellValue("FTE");
                    rowhead.createCell((short) 6).setCellValue("License Key");
                    rowhead.createCell((short) 7).setCellValue("Contact Person");


                    rowhead.createCell((short) 8).setCellValue("Month");
                    rowhead.createCell((short) 9).setCellValue("IsActive");
                    rowhead.createCell((short) 10).setCellValue("Comments");

                    int i = 1;
                    if(request.getParameter("Export to Excel")!=null && rs==null){
                        alert("No Data for the selected fields");
                    }
                    if(request.getParameter("Export to Excel")!=null && rs!=null) {
                        alert("Report generated successfully");

                    while (rs.next()){
                        HSSFRow row = sheet.createRow((short) i);
                        row.createCell((short) 0).setCellValue(rs.getString("project_id"));
                        row.createCell((short) 1).setCellValue(rs.getString("acc_name"));
                        row.createCell((short) 2).setCellValue(rs.getString("project_name"));
                        row.createCell((short) 3).setCellValue(rs.getString("iot"));
                        row.createCell((short) 4).setCellValue(rs.getString("ilc_code"));
                        row.createCell((short) 5).setCellValue(rs.getInt("app_fte"));
                        row.createCell((short) 6).setCellValue(rs.getInt("license_no"));
                        row.createCell((short) 7).setCellValue(rs.getString("contact_person"));
                        row.createCell((short) 8).setCellValue(mon);
                        row.createCell((short) 9).setCellValue(rs.getString("active"));

                        i++;
                    }


                    String yemi = "C:/Users/IBM_ADMIN/Desktop/test.xls";
                    FileOutputStream fileOut = new FileOutputStream(yemi);
                    workbook.write(fileOut);
                    fileOut.close();}
    }}


                     catch (ClassNotFoundException e1) {
                       e1.printStackTrace();
                    } catch (SQLException e1) {
                        e1.printStackTrace();
                    } catch (FileNotFoundException e1) {
                        e1.printStackTrace();
                    } catch (IOException e1) {
                        e1.printStackTrace();

    }


    request.getRequestDispatcher("index5.jsp").forward(request, response);

}

private void alert(String string) {
    // TODO Auto-generated method stub

}


 }