ExportStatesToSpreadsheet

JSP

How do you get a list off of a database to analyze it in a spreadsheet? I’m glad you asked!

This example gets the list of 50 state names and abbreviations from a reference table for download into an OpenOffice spreadsheet. Would you want to analyze such a list as this particular one? I dunno! This just a “How to” example.

Very minor changes are nice-to-have for Microsoft Excel, but the idea is to get the data in CSV format and make it available to the user with a suggestion of how to access it. For example, my OS is set to ask me what I want to open the data with or where do I want to save it under what file name, so that means there is some flexibility in how it works. Don't always assume the jargon means what it appears to mean...

Here is a partial screenshot of the finished export into OpenOffice.



Depending on you system, before you have your results in the spreadsheet, you may face a couple of brief dialogs. Once your download is completed, you may be prompted to specify what to use to view the content. Of course,  this example is focused on OpenOffice, so we would choose OpenOffice, but clearly you have plenty of freedom to specify anything you want (it’s only CSV file format data… I’ll produce an XML version, at some point.)



Having picked our OpenOffice application, we are prompted to qualify the import data. Note that we can refer to the preview at the bottom of the dialog, if needed. In this case, the fields are delimited by a “tab” character. Yes, I know, “Should the extension not have been tsv?” No, the convention for the extension is CSV. That’s all there is to it. CSV is the convention.



So! How did we get there from nowhere? Well, first things first… In a few terse sentences, the following are the “GOTCHYAS” that I overcame.

1. You need to let the client know what type of data it is getting, so an easy thing to do is specify the extension as csv.

response.addHeader(“Content-Disposition”,”attachment; filename=\”" + “USstates.csv” + “\”");

The escaped quotes around the file name are for in case you want embedded spaces or perhaps some other strangeness in your file name.

2. You need to specify the content type. You can do this a couple of ways, but it is best to go with convention and make it a page directive.

<%@ page contentType=”application/vnd.oasis.opendocument.spreadsheet”  . . .   %>

3. Be careful to not inadvertently create blank lines with the page directive HTML. Any linefeed outside of the JSP code delimiters will result in a blank line at the top of your spreadsheet. If you notice mysterious blank lines in your spreadsheet, just recall this admonition and you will know what’s what…

With all that out of the way, here is the code. First, here is the SQL (PostgreSQL function).

– DROP FUNCTION plpgsqlfGetStates; CREATE OR REPLACE FUNCTION plpgsqlfGetStates RETURNS SETOF reference_state AS $$ SELECT name, abbr FROM reference_state; $$ LANGUAGE SQL;

Here is the JSP code. &lt;%@ page contentType="application/vnd.oasis.opendocument.spreadsheet" language="java" import="java.text.*" import="java.io.*" import="java.sql.*" import="java.util.*" errorPage="" %>&lt;%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %>&lt;% /* * ExportStatesToOpenOffice.jsp * * Copyright (c) 2013 Alfred J Wheeler * All rights reserved. * * This software is the proprietary * information of Alfred J Wheeler * */ /**  * The purpose of this application is to get a list * by means of calling a stored procedure that can be  * opened into an OpenDocument spreadsheet. * * SYNTAX *  ExportStatesToOpenOffice.jsp * * @author      9/18/2013 created by Alfred J Wheeler. * * @version     0.1 * */ /*  * Initialize some variables */ //String message = ""; //long dt = 0; //String title = null; Connection conn = null; Statement st = null; ResultSet rs = null; //String sql = ""; /* * Get a date time stamp. Set a page title, if needed... */ //dt = System.currentTimeMillis; //title = "Export States To OpenOffice"; /* * Get the database connection values, etc... from the properties file * ( This eventually will be externalized to reduce code clutter... ) */ String p = application.getRealPath("/") + "properties/Demo.properties"; InputStream propsFile; Properties appProp = new Properties; try { propsFile = new FileInputStream(p); appProp.load(propsFile); propsFile.close; } catch (IOException ioe) { out.println("&lt;PRE>"); out.println("I/O Exception."); ioe.printStackTrace; out.println("&lt;/PRE>"); } String dbDriver = "" + appProp.getProperty("dbDriver"); String dbUrl   = "" + appProp.getProperty("dbUrl"); String dbUid   = "" + appProp.getProperty("dbUid"); String dbUpw   = "" + appProp.getProperty("dbUpw"); //String css     = "" + appProp.getProperty("css"); /* * Open the database */ try { Class.forName(dbDriver); conn = DriverManager.getConnection(dbUrl, dbUid, dbUpw); st = conn.createStatement; /* 	 * GOTCHYA Note: * Be mindful of the open and close terminators * ( left-angle-bracket-percent-sign, right-angle-bracket-percent-sign ) * because * if you have them on separate lines for readability, * then you are implicitly asking for a newline. * 	 * if you see otherwise unexplained blank lines at the * top of your spreadsheet, * check if you have somehow inadvertently created them * in your HTML... * 	 */ 	response.addHeader("Content-Disposition","attachment; filename=\"" + "USstates.csv" + "\""); rs = st.executeQuery("SELECT * FROM plpgsqlfGetStates"); while(rs.next) { out.print ( rs.getString("name") ); out.print ( "\t" ); out.println ( rs.getString("abbr") ); } 	} // end try catch ( SQLException ex ) { out.println ( "&lt;pre>" ); out.println ( "---" ); out.println ( "--                      FATAL ERROR IN SQL" ); out.println ( "---" ); out.println ( "-- " ); while ( ex != null ) { out.println ("-- SQLState: " + ex.getSQLState ); out.println ("-- Message: " + ex.getMessage ); out.println ("-- Vendor:  " + ex.getErrorCode ); ex = ex.getNextException ; out.println ( "-- " ); } 		out.println ( "---" ); } // end catch catch ( java.lang.Exception ex ) { /* 		 * Got some other type of exception. Dump it. */ 		out.println ( "&lt;pre>" ); out.println ( "---" ); ex.printStackTrace ; out.println("exception: " + ex.getMessage ); } // end catch finally { // No action for now } // end finally %>

It’s short. It’s sweet. Most importantly, it works! That’s it for this example! ‘Glad I could help!