Blog Archives

EJB downloading an excel file from JSP

The Calling Page

Let’s say you have a button on a JSP page that calls it. One of the problems is stopping the page opening as blank. So here’s the calling button

<form method="post" action="#">
 <input type="button" value="Download" onClick="return downloadButton();"/>
        </form>
        

The iFRame is used to trigger the excel call, and here’s the download button javascript.

function downloadButton() {
 
    document.getElementById("excel").src="exportxl.jsp?sql=select * from mytable";
    return false;
    
}

The Responding Page

Now the Calling JSP Page that goes off and gets the Excel Spreadsheet from the EJB, I have an EJB called server and the corresponding remote interface “server remote” note that these must be registered in the xml files in the web app so the web app can see the remote interface in the EJB

<%-- 
    Document   : exportxl
    Created on : Aug 1, 2011, 8:36:23 AM
    Author     : paulc
--%>
<%@ page import = "java.sql.*" %>
<%@ page import = "javax.naming.Context" %>
<%@ page import = "javax.naming.InitialContext; "%>
<%@ page import = "PHJ.*" %>
<%@page contentType="application/vnd.ms-excel" pageEncoding="UTF-8"%>
<%

    PHJ.ServerRemote jobManagerBean = null;
    try {
        Context c = new InitialContext();
        jobManagerBean = (PHJ.ServerRemote) c.lookup("java:comp/env/Server");
    } catch (Exception ne) {
        System.out.println("Could not find Job Manager Bean " + ne);
        out.println("Could not find Job Manager Bean");
        return;
    }

    response.setHeader("Content-disposition", "attachment; filename=excelexport.csv");

    String sql = "";
    if (request.getParameter("sql") != null) {
        sql = request.getParameter("sql");

        out.print(jobManagerBean.prepareCSVForExcel(sql));

    } else {
        out.println("Error, No SQL statement specified");

    }
    
    

%>

The EJB

And here’s the routine in the EJB.

 
    private Connection conn;
    private String dbName = "myJNDIRESOURCE"; // make sure you have JNDI resource setup 
    /* This routine gets a connection from the above DBName, however, if it can't find it it lists the available JNDI resourcees
     * to the console or log
     */ 
    private Connection getConnection() {
        if (conn != null) {
            try {
                if (!conn.isClosed()) {
                    return conn;
                }
            } catch (SQLException ex) {
                // Do nothing just carry on.
            }

        }
        InitialContext ic = null;

        Query q = null;

        try {
            // initialize JNDI lookup parameters
            ic = new InitialContext();
            DataSource ds = (DataSource) ic.lookup(dbName);
            conn = ds.getConnection();
        } catch (Exception e) {
            try {
                System.out.println("JNDI Resource " + dbName + " not found " + e);
                NamingEnumeration children = ic.list("");
                while (children.hasMore()) {
                    NameClassPair ncPair = (NameClassPair) children.next();
                    System.out.print(ncPair.getName() + " (type " + ncPair.getClassName() + ")");

                }
                System.out.println("**** CANNOT FIND DATABASE ************************");
                children = ic.list("jdbc");
                while (children.hasMore()) {
                    NameClassPair ncPair = (NameClassPair) children.next();
                    System.out.print(ncPair.getName() + " (type " + ncPair.getClassName() + ")");

                }
                System.out.println("****************************************");
            } catch (Exception f) {
                System.out.println("Error 57:" + f);
            }
            System.out.println("ERROR 52: " + e);
            return null;
        }
        return conn;

    }

    public String prepareCSVForExcel(String sSQL) {

        String sResult = "";
        try {
            conn = getConnection();
            Statement s = conn.createStatement();
            ResultSet r = s.executeQuery(sSQL);
            // feldnames
            for (int i = 1; i != r.getMetaData().getColumnCount(); ++i) {
                sResult = sResult + r.getMetaData().getColumnName(i) + ",";


            }
            sResult = sResult + "\n";

            while (r.next()) {
                for (int i = 1; i != r.getMetaData().getColumnCount(); ++i) {
                    try {
                    if (r.getObject(i) != null) {
                        sResult = sResult + r.getString(i) + ",";
                    } else {
                        sResult = sResult + ",";

                    }
                    } catch (Exception e) {
                        sResult = sResult + ","; // Happens when there is a conversion error to a tiemstamp
                    }


                }
                sResult=sResult+"\n";



            }




        } catch (Exception e) {
            return e + "";
        }
        return sResult;
    }
Advertisements