Generic Flex Data Exporter to CSV File

One of the requirements from my customer is the extraction of data from a collection and the ability to view it in Excel.  In my first attempt, I used the Windows clipboard to store the data from the ArrayCollection of the DataGrid.  This worked well for small data sets.  However, when handling large data sets, it took too long and sometimes the Flash player would time out.

I wanted a more reliable and efficient way to extract the data.  With the help of BlazeDS, my Flex application can receive a collection from Java and convert it to an ArrayCollection.  My question was, could it send an ArrayCollection to Java?  Sure enough, BlazeDS has a Java ArrayCollection class that extends an ArrayList.  The Java ArrayCollection is a collection of HashMaps.  Each HashMap contains a row of data with key/value pairs representing the data fields and values.

To take advantage of the Java ArrayCollection, I created the Java class FlexDataExporter.  This class performs the following steps:

1. Receives the Flex ArrayCollection and the file name used in the creation of the CSV file
2. Converts the collection to a Object array
3. Creates a header comma delimited record
4. Loops through each element in the array and creates the comma delimited detail records
5. Stores the comma delimited records in a String array
6. Saves the String array to a session attribute to be used downstream by a JSP page
7. Returns the JSP page name, location and parameter values back to the Flex application

Below is the FlexDataExporter class:

package com.brychka.export;

import flex.messaging.*;
import flex.messaging.io.ArrayCollection;
import flex.messaging.util.UUIDUtils;

import java.io.*;
import java.text.*;
import java.util.*;

import javax.servlet.*;
import javax.servlet.http.*;


public class FlexDataExporter extends HttpServlet implements Serializable{

    private static final long serialVersionUID = 2L;
    
    public HttpServletRequest request;
    public HttpServletResponse response;
    public FlexSession session;


    public FlexDataExporter() {
        request = FlexContext.getHttpRequest();
        session = FlexContext.getFlexSession();
        response = FlexContext.getHttpResponse();
    }
    
    public Object  exportData(
            String reportName, ArrayCollection arrayCollection) throws IOException {

    	String jspFileName = null;
    	String[] data = null;

    	try {
    	    data = getData(arrayCollection);
    	    
        	//Create a unique value for the data key.
        	String dataKey = UUIDUtils.createUUID(false);
    		
    	    // Save data to session attribute to be picked up by the JSP page
    	    session.setAttribute(dataKey, data);
            
            // Build a string of the JSP information that will be passes back to the Flex application
    	    HttpServletRequest req = FlexContext.getHttpRequest();
            String contextRoot = null;
            contextRoot = req.getContextPath();
            jspFileName = contextRoot + "/jsp/downloadCSV.jsp?reportName=" + reportName + "&dataKey=" + dataKey;

        } catch (Exception e) {

            e.printStackTrace();

		}
        return jspFileName;
    }
    
    public String[] getData(ArrayCollection arrayCollection) {
		Object[] elements = arrayCollection.toArray(); 
		int rows = elements.length;
		String[] results = new String[rows + 1];
		StringBuffer result = new StringBuffer(1000);
		int noOfColumns = 0;
        HashMap map = null;
        Set columns = null;
        DateFormat dateFormatter;

        dateFormatter = DateFormat.getDateTimeInstance(DateFormat.SHORT,
                			DateFormat.MEDIUM);
	    
        // Loop through the array 
        for (int i = 0; i < rows; i++) {
	      //The column names and values are stored in a HashMap for each row
  	      map = (HashMap)elements[i];
	      
  	      // Create header row
  	      if (i == 0) {
		     // Get the list of columns
	    	 columns = map.keySet();
	    	 
	    	 // Remove unnecessary columns.  This could be an
	    	 // array passed in by the Flex application.
	    	 // This removes the specified element from this set 
	    	 // if it is present (optional operation). 
	    	 columns.remove("mx_internal_uid");
	    	 columns.remove("uid");
	    	 columns.remove("_nulls");
		     
		     // Create header row by looping through the HashMap keys (columns)
	    	 noOfColumns = columns.size();
		     int j = 0;
		     Iterator itr = columns.iterator();
		     result.setLength(0);
		     while(itr.hasNext()) {
	    	    String columnName = (String)itr.next();
	    	    result.append(columnName);
    	        j++;
	    	    
    	      if (j < noOfColumns)
	    	      result.append(",");
	   	      }
	         results[i] = result.toString();
	 		 //System.out.println(result);
	      }
	      
	      // Create detail row by looping through the HashMap keys and
	      // getting the column value
	      int j = 0;
	      Iterator itr = columns.iterator();
		  result.setLength(0);
	      while(itr.hasNext()) {
	    	  Object columnFinalValue = null;
	    	  Object columnName = itr.next();
	    	  Object columnValue = map.get(columnName);
	    	  if (columnValue instanceof Date) {
	    		  columnFinalValue = dateFormatter.format(columnValue);
	    	  } else if (columnValue instanceof String){
	    		  columnFinalValue = "\"" + columnValue + "\"";
	    	  } else {
	    		  columnFinalValue = columnValue; 
	    	  }
    	      result.append(columnFinalValue);
    	      j++;

    	      if (j < noOfColumns)
    	    	  result.append(",");
   	      }
	      
         results[i + 1] = result.toString();
 		 //System.out.println(result);
	    }
	    return results;
    }

}

Once the FlexDataExporter has built the CSV data, the data is ready to send to the client computer. I created a simple JSP page, downloadCSV.jsp, to handle this task. It obtains the String array from the session attribute, displays the browser download prompt to the user, and downloads the data to a file on the client computer. This JSP page is called from within the Flex application. The JSP page is displayed below:

<%--
/* downloadCSV.jsp 
 * Opens the Download prompt and downloads the data stored in the session attribute
 * as an attachment
 */  
--%>

<%@ page errorPage="error.jsp" %>

<%
//Get the parameters
String reportName=request.getParameter("reportName");
String dataKey=request.getParameter("dataKey");

// Get the data to download
String[] results = (String[]) session.getAttribute(dataKey);

//Re-gain memory
session.removeAttribute(dataKey);

// Display download prompt and download data to file 
response.setContentType("application/csv");
response.setHeader("content-disposition","attachment; filename=" + reportName + ".csv"); 

out.clear();
int index = results.length;
for (int i = 0; i < index; i++) {
	out.println(results[i]);	
}
out.flush();

%>

 

I also created an error.jsp page to display any errors triggered from the downloadCSV.jsp page.  The code for this page is listed below:

<!-- error.jsp -->
<%@ page isErrorPage="true" %>
<%@ page import="java.io.*" %>

<html>
<head>
<title>
Application Sample
</title>
<link rel="stylesheet" type="text/css" href="../stylesheet.css" title="Style">
</head>

<body bgcolor="white">
<span class="bnew">The application encountered this error :</span>
<pre>
<% exception.printStackTrace(new PrintWriter(out)); %>
</pre>
</body>
</html>
 

This completes the directions for the server code.  Next, we need to add a remote object to our Flex application to call the Java method.  I created the main.mxml application which shows how to set up the remote object.  The application will pass the ArrayCollection and file name to the Java class by calling the method exportData( ).  The listener exportResultHandler( ) will receive the JSP page name and parameters returned from the exportData( ) method and will navigate to the JSP page that triggers the download.  The main.mxml code is below:

<?xml version="1.0" encoding="utf-8"?>
<mx:Application xmlns:mx="http://www.adobe.com/2006/mxml" layout="absolute"
	 creationComplete="init()">
	<mx:Script>
		<![CDATA[
	   	import mx.rpc.remoting.RemoteObject;
		import mx.rpc.events.ResultEvent;
		import mx.rpc.events.FaultEvent;
		import mx.collections.ArrayCollection;
		import mx.controls.Alert;

		private var stateData:Array=[	
	        {name:"California", code:"CA"},
            {name:"Pennsylvania", code:"PA"},
            {name:"Arizona", code:"AZ"},
            {name:"Texas", code:"TX"},
            {name:"Illinois", code:"IL"},
            {name:"New York", code:"NY"},
        ];
		
		[Bindable]
		private var collection:ArrayCollection = null;
		
		
		private function init():void {
			collection = new ArrayCollection(stateData);
		}

	    private function exportDataWithRO():void {
		  export.exportData("ExportedData",collection);
		}
			
		private function exportResultHandler(event:ResultEvent):void {
            var jspFile:String = event.result as String;
            navigateToURL(new URLRequest(jspFile), "_blank");
        }

        private function exportFaultHandler(event:FaultEvent):void {
                Alert.show(event.fault.toString(),"Export Fault");
        }


		]]>
	</mx:Script>

	<mx:RemoteObject id="export" destination="FlexDataExporter" showBusyCursor="true" concurrency="last">
    	<mx:method name="exportData" result="exportResultHandler(event)" fault="exportFaultHandler(event)"/>
	</mx:RemoteObject>

	<mx:Panel title="Export Data Example" layout="vertical" horizontalAlign="center"
		 x="10" y="10" paddingBottom="5" width="299" height="263">
		<mx:DataGrid id="dg" dataProvider="{collection}" width="100%" height="100%">
		</mx:DataGrid>	
		<mx:Button label="Export Data" 
			toolTip="Export Data Using BlazeDS, Java and JSP" click="exportDataWithRO();"/>
	</mx:Panel>

</mx:Application>
 

Finally, the location of the FlexDataExporter Java class must be entered into the remoting-config.xml file located in the “WEB-INF/flex” directory.  This is used by the remote object created in the main.mxml application.  The Destination ID in the configuration file has to match the remote object’s Destination.  The code entered into the configuration file is below:

<?xml version="1.0" encoding="UTF-8"?>
<service id="remoting-service" 
    class="flex.messaging.services.RemotingService">

    <adapters>
        <adapter-definition id="java-object" class="flex.messaging.services.remoting.adapters.JavaAdapter" default="true"/>
    </adapters>

    <default-channels>
        <channel ref="my-amf"/>
    </default-channels>

	<destination id="FlexDataExporter">
	    <properties>
	        <source>com.brychka.export.FlexDataExporter</source>
	    </properties>
	</destination>

</service>

There is not a lot of error checking in this example because I was creating a proof-of-concept application.  Therefore, I would recommend that you add more error logic if you use this code in a production environment.  Also, you will need to enable pop-ups to allow the JSP page to open.

Give this a try.  Run it again, but this time, use a collection that has 10s of thousands of rows and see how quickly the CSV file is created.  In most cases, you can plug in any ArrayCollection without changing a single line of the Java and JSP code.  However, if your ArrayCollection is not made up of HashMaps, you will need to rework the Java code.  I tested this with a 50,000 row dataset running Tomcat on my Windows XP laptop and it produced a file in seconds. 

If you just want to download the data from only the columns displayed in the DataGrid, you could add an argument to the exportData method and pass an array of dataField values of the displayed columns.  You would then use the dataField values when selecting the keys from the HashMap.

This is a small example of what you can do when you use other technologies with Flex.  By harnessing these technologies, you expand the many options available with Flex applications.