26007 - How to Create an Action to Directly Export to Excel from the JSP OLAPTableView

Sample 26007: How to Create an Action to Directly Export to Excel from the JSP OLAPTableView

Overview

This sample illustrates how to add an action to the pop-up menu of the column headers of an OLAPTableView in order to directly export the contents of the entire table to Microsoft Excel. This sample creates an OLAPTableViewComposite, which is a TransformationBean^TM that is composed of other TransformationBeans that include the following subcomponents: MenuBar, NavigationBar, OLAPTableView, OLAPDrillState, AppliedFilters, Title, and Footer.

Here are the steps for creating a simple OLAPTableView:

Create an OLAPDataSet.
Create an ActionProvider.
Create an OLAPTableView with the OLAPDataSet model and the ActionProvider.
Render the OLAPTableView.

The code shown on the Full Code tab of this sample creates an OLAPDataSet and ActionProvider and then creates an OLAPTableViewComposite tag with the OLAPDataSet and ActionProvider. The ActionProvider includes the Direct Export to Excel command.

The sample is based on the Model 2 Servlet template that is available in SAS^® AppDev Studio. For simplicity, this sample uses its own static data model for the OLAPTableView. In most real-world use cases, users will connect to live data and not create their own data model. For more information about how to connect the OLAPTableView to live data, see Sample 26014.

Customizations

This sample uses the available export commands to export the entire OLAPTableView contents to Microsoft Excel. The contents of the OLAPTableView are directly exported to Microsoft Excel without displaying the Export Selector.

There's a specific export command for each of the supported export output types:

Tab Separated Values
Comma Separated Values
Excel XML
HTML

This example shows how to use all of these export commands.

Export to Tab Separated Values

To use Tab Separated Values , change the BaseCommand in the servlet method customizeOLAPTableActions(..) to an OLAPExportToTabSeparatedCommand:

BaseCommand command = new OLAPExportToTabSeparatedCommand();

Export to Comma Separated Values

To use comma separated values, change the BaseCommand in the servlet method customizeOLAPTableActions(..) to an OLAPExportToCSVCommand:

BaseCommand command = new OLAPExportToCSVCommand();

Export to Excel XML

To export to XML, change the BaseCommand in the servlet method customizeOLAPTableActions(..) to an OLAPExportToExcelXMLCommand:

BaseCommand command = new OLAPExportToExcelXMLCommand();

Export to HTML

To export to HTML, perform these tasks:

Change the BaseCommand in the servlet method customizeOLAPTableActions(..) to an OLAPExportToHTMLCommand:

BaseCommand command = new OLAPExportToHTMLCommand();

Then, add the following code after the line that reads actionProvider.executeCommand(request, response, response.getWriter());

// to correctly export non english characters	
String charset = BaseUtil.getOutputCharacterEncoding(request);
response.setContentType("text/html; charset=" + charset);

To also export your HTML styles, add the following:

Add the following imports to the top of the servlet:

import com.sas.dataselectors.export.HTMLExportCommandsAttributeNames;
import com.sas.dataselectors.export.HTMLExportCSSInfoInterface;
import com.sas.dataselectors.export.HTMLExportCSSInfo;
import com.sas.entities.AttributeSetException; 
import info.UrlInfo;

Add the following code in servlet method customizeOLAPTableActions(..) before the line that reads customAction.setActionType("directExportToExcelAction");

HTMLExportCSSInfoInterface[] cssInfo = {new HTMLExportCSSInfo()}; 
String absoluteCssURL = "";
String cssURL = cssInfo[0].getCSSURL();
// add . in front of the default cssURL got from the HTMLExportCSSInfo object
if ( cssURL.startsWith("/") )
   cssURL = ((new StringBuffer(".")).append(cssURL)).toString();
if ( ! UrlInfo.isAbsoluteURL(cssURL) )
// transforms the relative URL ./styles/sasComponents.css
// into an absolute one http://localhost:8082/TableView/./styles/sasComponents.css
   absoluteCssURL = UrlInfo.getAbsoluteURL(cssURL, request);
else
   absoluteCssURL = cssURL;
// sets the absolute URL of the .css file
cssInfo[0].setCSSURL(absoluteCssURL);
try
{
// sets to the export command the info of the .css  file
// if more .css files are used, add a new HTMLExportCSSInfo 
// object to the cssInfo array for each .css file
   command.setAttribute(HTMLExportCommandsAttributeNames.CSS_INFO, cssInfo);
}
catch(AttributeSetException ase){}

Additional Documentation

For a list of samples for common tasks, see SAS Note 32218.
The SAS AppDev Studio Developer's Site contains detailed information that will assist you when developing Web applications with SAS AppDev Studio, including an API reference and the custom tag reference for the sas tagset.

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

Instructions

Build a new Web application project in SAS AppDev Studio by using the Model 2 Servlet template.
The template will generate a servlet and a JSP file, which, by default, are named Model2ExampleControllerServlet.java and Model2ExampleViewer.jsp. Replace the contents of these files with the code below.
Execute the servlet on the test Tomcat server.

Tip: For help with building a Web application project and testing a Web application, see SAS Note 32218.

Code for Model2ExampleViewer.jsp


<%@ taglib uri="http://www.sas.com/taglib/sas" prefix="sas" %>
<%@ page pageEncoding="UTF-8"%>
<html>
<head>
	<link href="styles/sasComponents.css" rel="STYLESHEET" type="text/css">
</head>
<body>
	
	<sas:OLAPTableViewComposite ref="olapTableView" scope="session"/>
	
</body>
</html>

Code for Model2ExampleControllerServlet.java

Required changes

If you did not use the default values during the generation of the project, you may need to update the following values.

The package for the servlet.
package servlets;
The servlet name.
public class Model2ExampleControllerServlet.java ...
The URLmapping for the action provider, which must match the servlet mapping for the servlet.
sas_actionProvider.setControllerURL(request.getContextPath()+ "/Model2Example");
The JSP page that the servlet forwards to.
RequestDispatcher rd = getServletContext().getRequestDispatcher("/Model2ExampleViewer.jsp");


package servlets;

import info.UrlInfo;

import java.io.IOException;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import com.sas.actionprovider.ActionList;
import com.sas.actionprovider.ActionOrderList;
import com.sas.actionprovider.Area;
import com.sas.actionprovider.BaseAction;
import com.sas.actionprovider.HttpAction;
import com.sas.actionprovider.HttpActionProvider;
import com.sas.actionprovider.support.ActionProviderSupportTypes;
import com.sas.actionprovider.support.olaptableview.HttpOLAPTableViewSupport;
import com.sas.commands.BaseCommand;
import com.sas.dataselectors.export.HTMLExportCSSInfo;
import com.sas.dataselectors.export.HTMLExportCSSInfoInterface;
import com.sas.dataselectors.export.HTMLExportCommandsAttributeNames;
import com.sas.entities.AttributeDescriptorInterface;
import com.sas.entities.AttributeSetException;
import com.sas.servlet.commands.olap.export.OLAPExportToTabSeparatedCommand;
import com.sas.servlet.tbeans.olaptableview.html.OLAPTableView;
import com.sas.servlet.tbeans.olaptableview.html.OLAPTableViewComposite;
import com.sas.servlet.tbeans.olaptableview.html.OLAPTableViewCompositeKeysInterface;
import com.sas.storage.olap.AxisInterface;
import com.sas.storage.olap.OLAPException;
import com.sas.storage.olap.TupleElementInterface;
import com.sas.storage.olap.TupleInterface;
import com.sas.storage.olap.embedded.Axis;
import com.sas.storage.olap.embedded.OLAPDataSet;
import com.sas.storage.olap.embedded.ResultSet;
import com.sas.storage.olap.embedded.ResultSetMetadata;
import com.sas.storage.olap.embedded.Tuple;
import com.sas.storage.olap.embedded.TupleElement;
import com.sas.swing.util.Action;

public class Model2ExampleControllerServlet extends
		javax.servlet.http.HttpServlet {
	private static final String OLAP_TABLE = "olapTableView";
	// Declare a default version ID since parent class implements
	// java.io.Serializable
	private static final long serialVersionUID = 1L;

	/*
	 * doPost() Respond to the Post message.
	 */
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// Note: Calling doGet to provide same behavior to POST and GET HTTP
		// methods.
		doGet(request, response);
	}

	/*
	 * doGet() Respond to the Get message.
	 */
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// Note: Add User DO_GET code here
		HttpSession session = request.getSession();

		// Setup the ActionProvider
		HttpActionProvider actionProvider = null;
		synchronized (session) {
			if (session != null) {
				actionProvider = (HttpActionProvider) session
						.getAttribute("actionProvider");
			}
			// if ActionProvider is null, create one and put it on the session
			if (actionProvider == null) {
				actionProvider = new HttpActionProvider();
				actionProvider.setLocale(request.getLocale());
				actionProvider.setControllerURL(request.getContextPath()
						+ "/Model2Example");
				actionProvider.setName("actionProvider");
				// store object in its scope
				if (session != null)
					session.setAttribute("actionProvider", actionProvider);
			}
			// else execute the ActionProvider command
			else {
				actionProvider.executeCommand(request, response, response
						.getWriter());
			}
		}

		OLAPTableViewComposite olapTableView = null;

		synchronized (session) {
			if (session != null) {
				olapTableView = (OLAPTableViewComposite) session
						.getAttribute("OLAPTableView1");
			}
			if (null == olapTableView) {
				olapTableView = new OLAPTableViewComposite();

				OLAPDataSet defaultOLAPTableModel = createStaticData();
				olapTableView.setModel(defaultOLAPTableModel);

				// set the actionProvider on the olap table
				olapTableView.setActionProvider(actionProvider);
				customizeOLAPTableActions(olapTableView, actionProvider,
						request);

				session.setAttribute(OLAP_TABLE, olapTableView);
			}
		}

		RequestDispatcher rd = getServletContext().getRequestDispatcher(
				"/Model2ExampleViewer.jsp");
		rd.forward(request, response);

	}

	private void customizeOLAPTableActions(
			OLAPTableViewComposite olapTableView,
			HttpActionProvider actionProvider, HttpServletRequest request) {

		// Add the new action
		BaseCommand command = new OLAPExportToTabSeparatedCommand();
		HttpAction customAction = new HttpAction(command);

		// for exporting HTML styles
		HTMLExportCSSInfoInterface[] cssInfo = { new HTMLExportCSSInfo() };
		String absoluteCssURL = "";
		String cssURL = cssInfo[0].getCSSURL();
		// add . in front of the default cssURL got from the
		// HTMLExportCSSInfo object
		if (cssURL.startsWith("/"))
			cssURL = ((new StringBuffer(".")).append(cssURL)).toString();
		if (!UrlInfo.isAbsoluteURL(cssURL))
			// transforms the relative URL ./styles/sasComponents.css
			// into an absolute one
			// http://localhost:8082/TableView/./styles/sasComponents.css
			absoluteCssURL = UrlInfo.getAbsoluteURL(cssURL, request);
		else
			absoluteCssURL = cssURL;
		// sets the absolute URL of the .css file
		cssInfo[0].setCSSURL(absoluteCssURL);
		try {
			// sets to the export command the info of the .css file
			// if more .css files are used, add a new HTMLExportCSSInfo
			// object to the cssInfo array for each .css file
			command.setAttribute(HTMLExportCommandsAttributeNames.CSS_INFO,
					cssInfo);
		} catch (AttributeSetException ase) {
		}
		// end of code to export HTML styles

		// associates a unique type to the action
		customAction.setActionType("directExportToExcelAction");
		// sets the name of the action displayed in the menu
		customAction.putValue(Action.NAME, "Direct Export to Excel");
		// the action is a custom one
		customAction.setCustom(true);
		/* Define a new action attribute, "model", */
		/* that corresponds to the commandâ��s "model" attribute. */
		/* Must mark the attribute as visible=false */
		/* because it is not possible to pass its value as a String */
		/*
		 * parameter on the request. Instead it will be stored on the
		 * HttpActionProvider
		 */
		/* and set on command just prior to execution */
		try {
			customAction.setAttribute("model", olapTableView.getModel());
			AttributeDescriptorInterface adi = customAction
					.getAttributeDescriptor("model");
			adi.setVisible(false);
			// tell the action provider support class that this value is
			// already set.
			adi.setModifiable(false);
			adi.setSupplementalProperty(BaseAction.DYNAMIC, Boolean.FALSE);

		} catch (Exception e) {
			throw new RuntimeException(e);
		}

		OLAPTableView viewer = (OLAPTableView) olapTableView
				.getComponent(OLAPTableViewCompositeKeysInterface.OLAPTABLEVIEW_TABLEDATA);
		if (viewer != null) {
			/* Register the viewer to the custom action */
			try {
				customAction
						.setAttribute(
								HTMLExportCommandsAttributeNames.VIEW_COMPONENT,
								viewer);
			} catch (AttributeSetException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			AttributeDescriptorInterface adi = customAction
					.getAttributeDescriptor(HTMLExportCommandsAttributeNames.VIEW_COMPONENT);
			adi.setVisible(false);
			/*
			 * Register the action to the action provider and specify the area
			 * of the viewer that the action is valid.
			 */
			java.util.Vector viewers = new java.util.Vector(1);
			viewers.add(viewer);
			/*
			 * setAction() on ROW_TITLE_AREA would display the action in the
			 * column header together with the column name
			 */
			actionProvider.setAction(customAction, viewers, new Area(
					HttpOLAPTableViewSupport.ROW_TITLE_AREA));
			/*
			 * We want the action be displayed in the pop menu of a column
			 * header, not in the column header itself
			 */
			ActionOrderList alist = actionProvider.getActionOrderList(
					ActionProviderSupportTypes.OLAP_TABLEVIEW_SUPPORT, viewer,
					HttpOLAPTableViewSupport.ROW_TITLE_AREA);
			System.out.println("alist: " + alist);

			if (alist != null) {
				// Remove the action (bottom) from the row title
				if (alist.size() > 1)
					alist.remove(alist.size() - 1);
				// Add the action to the bottom of the popup menu of the
				// row title
				if (alist.size() > 1) {
					ActionOrderList editlist = (ActionOrderList) (alist
							.get(alist.size() - 1));
					if (editlist != null) {
						editlist.add(ActionList.SEPARATOR);
						editlist.add("directExportToExcelAction");
					}
				}
			}
		}
	}

	private OLAPDataSet createStaticData() {
		OLAPDataSet defaultOLAPTableModel = null;
		try {
			TupleInterface tuple1 = new Tuple(new TupleElementInterface[] {
					new TupleElement("Candy"), new TupleElement("Sales") });
			TupleInterface tuple2 = new Tuple(new TupleElementInterface[] {
					new TupleElement("Candy"), new TupleElement("Profits") });
			TupleInterface tuple3 = new Tuple(new TupleElementInterface[] {
					new TupleElement("Magazines"), new TupleElement("Sales") });
			TupleInterface tuple4 = new Tuple(
					new TupleElementInterface[] {
							new TupleElement("Magazines"),
							new TupleElement("Profits") });

			TupleInterface tuple5 = new Tuple(
					new TupleElementInterface[] {
							new TupleElement("North Carolina"),
							new TupleElement("Bob") });
			TupleInterface tuple6 = new Tuple(new TupleElementInterface[] {
					new TupleElement("North Carolina"),
					new TupleElement("Mary") });
			TupleInterface tuple7 = new Tuple(new TupleElementInterface[] {
					new TupleElement("Virginia"), new TupleElement("Jean") });
			TupleInterface tuple8 = new Tuple(new TupleElementInterface[] {
					new TupleElement("Virginia"), new TupleElement("Joe") });
			TupleInterface tuple9 = new Tuple(new TupleElementInterface[] {
					new TupleElement("Tennessee"), new TupleElement("Tom") });
			TupleInterface tuple10 = new Tuple(new TupleElementInterface[] {
					new TupleElement("Tennessee"), new TupleElement("Dave") });

			Axis columnAxis = new Axis(AxisInterface.COLUMNS_AXIS, null,
					new TupleInterface[] { tuple1, tuple2, tuple3, tuple4 });
			columnAxis.setAxisHeaders(new String[] { "Product", "" });
			columnAxis.setUniqueLevelNames(new String[] { "[Products]",
					"[Measures]" });
			Axis rowAxis = new Axis(AxisInterface.ROWS_AXIS, null,
					new TupleInterface[] { tuple5, tuple6, tuple7, tuple8,
							tuple9, tuple10 });
			rowAxis.setAxisHeaders(new String[] { "State", "Name" });
			rowAxis.setUniqueLevelNames(new String[] { "[State]", "[Name]" });
			AxisInterface[] axes = { columnAxis, rowAxis };

			// create an instance of result set metadata with the axes
			ResultSetMetadata resultSetMetadata = new ResultSetMetadata(axes);
			ResultSet resultSet = new ResultSet();
			resultSet.setResultSetMetadata(resultSetMetadata);
			Object[] values = new Object[] { "500", "150", "200", "75", "450",
					"105", "300", "85", "335", "80", "120", "25", "570", "175",
					"150", "35", "475", "125", "205", "40", "610", "230", "95",
					"30" };
			resultSet.setCells(0, 23, values);
			defaultOLAPTableModel = new OLAPDataSet(resultSet);
		} catch (OLAPException oe) {
			oe.printStackTrace();
		}
		return defaultOLAPTableModel;

	}
}