taverna-workbench-results-view/src/main/java/net/sf/taverna/t2/workbench/views/results/saveactions/SaveAllResultsAsExcel.java - incubator-taverna-workbench - Git at Google

 /*******************************************************************************
  * Copyright (C) 2007 The University of Manchester
  *
  *  Modifications to the initial code base are copyright of their
  *  respective authors, or their employers as appropriate.
  *
  *  This program is free software; you can redistribute it and/or
  *  modify it under the terms of the GNU Lesser General Public License
  *  as published by the Free Software Foundation; either version 2.1 of
  *  the License, or (at your option) any later version.
  *
  *  This program is distributed in the hope that it will be useful, but
  *  WITHOUT ANY WARRANTY; without even the implied warranty of
  *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  *  Lesser General Public License for more details.
  *
  *  You should have received a copy of the GNU Lesser General Public
  *  License along with this program; if not, write to the Free Software
  *  Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307
  ******************************************************************************/
 package net.sf.taverna.t2.workbench.views.results.saveactions;

 import static java.lang.Math.max;
 import static java.util.Arrays.asList;
 import static net.sf.taverna.t2.baclava.factory.DataThingFactory.bake;
 import static net.sf.taverna.t2.workbench.icons.WorkbenchIcons.saveIcon;
 import static org.apache.poi.ss.usermodel.CellStyle.BORDER_NONE;
 import static org.apache.poi.ss.usermodel.CellStyle.BORDER_THIN;
 import static org.apache.poi.ss.usermodel.CellStyle.SOLID_FOREGROUND;

 import java.beans.IntrospectionException;
 import java.io.File;
 import java.io.FileNotFoundException;
 import java.io.FileOutputStream;
 import java.io.IOException;
 import java.util.Collection;

 import javax.swing.AbstractAction;

 import net.sf.taverna.t2.baclava.DataThing;
 import net.sf.taverna.t2.baclava.iterator.BaclavaIterator;

 import org.apache.poi.hssf.usermodel.HSSFCell;
 import org.apache.poi.hssf.usermodel.HSSFCellStyle;
 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.hssf.util.HSSFColor;

 /**
  * Stores the entire map of result objects to disk as a single XML data document.
  *
  * @author Tom Oinn
  */
 public class SaveAllResultsAsExcel extends SaveAllResultsSPI {
 	private static final long serialVersionUID = -2759817859804112070L;

 	HSSFWorkbook wb = null;
 	HSSFSheet sheet = null;
 	HSSFCellStyle headingStyle = null;
 	HSSFCellStyle[] styles = null;

 	public SaveAllResultsAsExcel() {
 		super();
 		putValue(NAME, "Save as Excel");
 		putValue(SMALL_ICON, saveIcon);
 	}

 	@Override
 	public AbstractAction getAction() {
 		return new SaveAllResultsAsExcel();
 	}

 	@Override
 	protected void saveData(File f) throws IOException {
 		try {
 			generateSheet();
 		} catch (IntrospectionException e) {
 			throw new IOException("failed to generate excel sheet model", e);
 		}
 		saveSheet(f);
 	}

 	/**
 	 * Generate the Excel sheet from the DataThing's in the map. All of the
 	 * results are shown in the same spreadsheet, but in different columns. Flat
 	 * lists are shown vertically, 2d lists as a matrix, and deeper lists are
 	 * flattened to 2d.
 	 *
 	 * @throws IntrospectionException
 	 */
 	void generateSheet() throws IntrospectionException {
 		wb = new HSSFWorkbook();
 		setStyles();
 		sheet = wb.createSheet("Workflow results");
 		sheet.setDisplayGridlines(false);
 		int currentCol = 0;

 		for (String portName : chosenReferences.keySet()) {
 			logger.debug("Output for : " + portName);
 			DataThing resultValue = bake(getObjectForName(portName));
 			// Check whether there's a textual type
 			Boolean textualType = isTextual(resultValue.getDataObject());
 			if (textualType == null || !textualType)
 				continue;
 			logger.debug("Output is textual");
 			getCell(currentCol, 0).setCellValue(portName);
 			getCell(currentCol, 0).setCellStyle(headingStyle);
 			int numCols = 1;
 			int numRows = 1;
 			int currentRow = 0;
 			BaclavaIterator rows;
 			try {
 				rows = resultValue.iterator("l('')");
 			} catch (IntrospectionException ex) {
 				// Not a list, single value. We'll fake the iterator
 				DataThing fakeValues = new DataThing(
 						asList(resultValue.getDataObject()));
 				rows = fakeValues.iterator("l('')");
 			}
 			/*
 			 * If we only have one row, we'll show each value on a new row
 			 * instead
 			 */
 			boolean isFlat = rows.size() == 1;
 			while (rows.hasNext()) {
 				DataThing row = (DataThing) rows.next();
 				/*
 				 * Even increase first time, as we don't want to overwrite our
 				 * header
 				 */
 				currentRow++;
 				BaclavaIterator bi = row.iterator("''");
 				while (bi.hasNext()) {
 					DataThing containedThing = (DataThing) bi.next();
 					String containedValue = (String) containedThing.getDataObject();
 					int columnOffset = 0;
 					int[] location = bi.getCurrentLocation();
 					if (!isFlat && location.length > 0) {
 						columnOffset = location[location.length - 1];
 						numCols = Math.max(numCols, columnOffset + 1);
 					}
 					logger.debug("Storing in cell " + (currentCol + columnOffset) + " "
 							+ currentRow + ": " + containedValue);
 					getCell(currentCol + columnOffset, currentRow).setCellValue(containedValue);
 					if (isFlat)
 						currentRow++;
 				}
 			}
 			numRows = max(numRows, currentRow);

 			// Set the styles
 			for (int x = currentCol; x < currentCol + numCols; x++)
 				for (int y = 1; y < numRows + 1; y++)
 					setStyle(currentCol, x, y);
 			sheet.setColumnWidth(currentCol + numCols, 200);
 			currentCol += numCols + 1;
 		}
 	}

 	void setStyle(int currentCol, int column, int row) {
 		if (!hasValue(column, row))
 			return;
 		HSSFCell cell = getCell(column, row);
 		int n = 0, s = 0, w = 0, e = 0;
 		if (row < 2 || !hasValue(column, row - 1))
 			n = 1;
 		if (column == currentCol || !hasValue(column - 1, row))
 			w = 1;
 		if (!hasValue(column, row + 1))
 			s = 1;
 		if (!hasValue(column + 1, row))
 			e = 1;
 		int index = n + 2 * s + 4 * e + 8 * w;
 		cell.setCellStyle(styles[index]);
 	}

 	void setStyles() {
 		headingStyle = wb.createCellStyle();
 		headingStyle.setBorderTop(BORDER_THIN);
 		headingStyle.setBorderBottom(BORDER_THIN);
 		headingStyle.setBorderLeft(BORDER_THIN);
 		headingStyle.setBorderRight(BORDER_THIN);
 		headingStyle.setFillBackgroundColor(HSSFColor.LIGHT_YELLOW.index);
 		headingStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
 		headingStyle.setFillPattern(SOLID_FOREGROUND);
 		styles = new HSSFCellStyle[16];
 		for (int n = 0; n < 2; n++)
 			for (int s = 0; s < 2; s++)
 				for (int e = 0; e < 2; e++)
 					for (int w = 0; w < 2; w++) {
 						int index = n + 2 * s + 4 * e + 8 * w;
 						styles[index] = wb.createCellStyle();
 						styles[index].setBorderTop(n == 1 ? BORDER_THIN
 								: BORDER_NONE);
 						styles[index].setBorderBottom(s == 1 ? BORDER_THIN
 								: BORDER_NONE);
 						styles[index].setBorderRight(e == 1 ? BORDER_THIN
 								: BORDER_NONE);
 						styles[index].setBorderLeft(w == 1 ? BORDER_THIN
 								: BORDER_NONE);
 						styles[index].setFillBackgroundColor(HSSFColor.GOLD.index);
 						styles[index].setFillForegroundColor(HSSFColor.GOLD.index);
 						styles[index].setFillPattern(SOLID_FOREGROUND);
 					}
 	}

 	/**
 	 * Check if o is a String or contains elements that satisfy isTextual(o)
 	 * <p>
 	 * Traverse down the Collection o if possible, and check the tree of collection at the deepest
 	 * level.
 	 * </p>
 	 *
 	 * @param o
 	 *            Object to check
 	 * @return true if o is a String or is a Collection that contains a string at the deepest level.
 	 *         false if o is not a String or Collection, or if it is a collection that contains
 	 *         non-strings.
 	 *         null if o is a Collection, but it is empty or contains nothing but Collections.
 	 */
 	Boolean isTextual(Object o) {
 		if (o instanceof String)
 			// We dug down and found a string. Hurray!
 			return true;
 		if (o instanceof Collection) {
 			for (Object child : (Collection<?>) o) {
 				Boolean isTxt = isTextual(child);
 				if (isTxt == null)
 					// Unknown, try next one
 					continue;
 				return isTxt;
 			}
 			/*
 			 * We looped through and found just empty collections (or we are an
 			 * empty collection), we don't know.
 			 */
 			return null;
 		}
 		// No, sorry mate.. o was neither a String or Collection
 		return false;
 	}

 	/**
 	 * Get a cell at the given coordinates, create it if needed.
 	 *
 	 * @param column
 	 * @param row
 	 * @return
 	 */
 	HSSFCell getCell(int column, int row) {
 		HSSFRow srow = sheet.getRow(row);
 		if (srow == null)
 			srow = sheet.createRow(row);
 		HSSFCell scell = srow.getCell(column);
 		if (scell == null)
 			scell = srow.createCell(column);
 		return scell;
 	}

 	/**
 	 * Check if a cell has a value.
 	 *
 	 * @param column
 	 * @param row
 	 * @return
 	 */
 	boolean hasValue(int column, int row) {
 		HSSFRow srow = sheet.getRow(row);
 		if (srow == null)
 			return false;
 		HSSFCell scell = srow.getCell(column);
 		if (scell == null)
 			return false;
 		return true;
 	}

 	/**
 	 * Save the generated worksheet to a file
 	 *
 	 * @param file
 	 *            to save to
 	 * @throws FileNotFoundException
 	 * @throws IOException
 	 */
 	void saveSheet(File file) throws IOException {
 		FileOutputStream fos = new FileOutputStream(file);
 		wb.write(fos);
 		fos.close();
 	}

 	@Override
 	protected String getFilter() {
 		return "xls";
 	}
 }
	/*******************************************************************************
	* Copyright (C) 2007 The University of Manchester
	*
	* Modifications to the initial code base are copyright of their
	* respective authors, or their employers as appropriate.
	*
	* This program is free software; you can redistribute it and/or
	* modify it under the terms of the GNU Lesser General Public License
	* as published by the Free Software Foundation; either version 2.1 of
	* the License, or (at your option) any later version.
	*
	* This program is distributed in the hope that it will be useful, but
	* WITHOUT ANY WARRANTY; without even the implied warranty of
	* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
	* Lesser General Public License for more details.
	*
	* You should have received a copy of the GNU Lesser General Public
	* License along with this program; if not, write to the Free Software
	* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307
	******************************************************************************/
	package net.sf.taverna.t2.workbench.views.results.saveactions;

	import static java.lang.Math.max;
	import static java.util.Arrays.asList;
	import static net.sf.taverna.t2.baclava.factory.DataThingFactory.bake;
	import static net.sf.taverna.t2.workbench.icons.WorkbenchIcons.saveIcon;
	import static org.apache.poi.ss.usermodel.CellStyle.BORDER_NONE;
	import static org.apache.poi.ss.usermodel.CellStyle.BORDER_THIN;
	import static org.apache.poi.ss.usermodel.CellStyle.SOLID_FOREGROUND;

	import java.beans.IntrospectionException;
	import java.io.File;
	import java.io.FileNotFoundException;
	import java.io.FileOutputStream;
	import java.io.IOException;
	import java.util.Collection;

	import javax.swing.AbstractAction;

	import net.sf.taverna.t2.baclava.DataThing;
	import net.sf.taverna.t2.baclava.iterator.BaclavaIterator;

	import org.apache.poi.hssf.usermodel.HSSFCell;
	import org.apache.poi.hssf.usermodel.HSSFCellStyle;
	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.hssf.util.HSSFColor;

	/**
	* Stores the entire map of result objects to disk as a single XML data document.
	*
	* @author Tom Oinn
	*/
	public class SaveAllResultsAsExcel extends SaveAllResultsSPI {
	private static final long serialVersionUID = -2759817859804112070L;

	HSSFWorkbook wb = null;
	HSSFSheet sheet = null;
	HSSFCellStyle headingStyle = null;
	HSSFCellStyle[] styles = null;

	public SaveAllResultsAsExcel() {
	super();
	putValue(NAME, "Save as Excel");
	putValue(SMALL_ICON, saveIcon);
	}

	@Override
	public AbstractAction getAction() {
	return new SaveAllResultsAsExcel();
	}

	@Override
	protected void saveData(File f) throws IOException {
	try {
	generateSheet();
	} catch (IntrospectionException e) {
	throw new IOException("failed to generate excel sheet model", e);
	}
	saveSheet(f);
	}

	/**
	* Generate the Excel sheet from the DataThing's in the map. All of the
	* results are shown in the same spreadsheet, but in different columns. Flat
	* lists are shown vertically, 2d lists as a matrix, and deeper lists are
	* flattened to 2d.
	*
	* @throws IntrospectionException
	*/
	void generateSheet() throws IntrospectionException {
	wb = new HSSFWorkbook();
	setStyles();
	sheet = wb.createSheet("Workflow results");
	sheet.setDisplayGridlines(false);
	int currentCol = 0;

	for (String portName : chosenReferences.keySet()) {
	logger.debug("Output for : " + portName);
	DataThing resultValue = bake(getObjectForName(portName));
	// Check whether there's a textual type
	Boolean textualType = isTextual(resultValue.getDataObject());
	if (textualType == null \|\| !textualType)
	continue;
	logger.debug("Output is textual");
	getCell(currentCol, 0).setCellValue(portName);
	getCell(currentCol, 0).setCellStyle(headingStyle);
	int numCols = 1;
	int numRows = 1;
	int currentRow = 0;
	BaclavaIterator rows;
	try {
	rows = resultValue.iterator("l('')");
	} catch (IntrospectionException ex) {
	// Not a list, single value. We'll fake the iterator
	DataThing fakeValues = new DataThing(
	asList(resultValue.getDataObject()));
	rows = fakeValues.iterator("l('')");
	}
	/*
	* If we only have one row, we'll show each value on a new row
	* instead
	*/
	boolean isFlat = rows.size() == 1;
	while (rows.hasNext()) {
	DataThing row = (DataThing) rows.next();
	/*
	* Even increase first time, as we don't want to overwrite our
	* header
	*/
	currentRow++;
	BaclavaIterator bi = row.iterator("''");
	while (bi.hasNext()) {
	DataThing containedThing = (DataThing) bi.next();
	String containedValue = (String) containedThing.getDataObject();
	int columnOffset = 0;
	int[] location = bi.getCurrentLocation();
	if (!isFlat && location.length > 0) {
	columnOffset = location[location.length - 1];
	numCols = Math.max(numCols, columnOffset + 1);
	}
	logger.debug("Storing in cell " + (currentCol + columnOffset) + " "
	+ currentRow + ": " + containedValue);
	getCell(currentCol + columnOffset, currentRow).setCellValue(containedValue);
	if (isFlat)
	currentRow++;
	}
	}
	numRows = max(numRows, currentRow);

	// Set the styles
	for (int x = currentCol; x < currentCol + numCols; x++)
	for (int y = 1; y < numRows + 1; y++)
	setStyle(currentCol, x, y);
	sheet.setColumnWidth(currentCol + numCols, 200);
	currentCol += numCols + 1;
	}
	}

	void setStyle(int currentCol, int column, int row) {
	if (!hasValue(column, row))
	return;
	HSSFCell cell = getCell(column, row);
	int n = 0, s = 0, w = 0, e = 0;
	if (row < 2 \|\| !hasValue(column, row - 1))
	n = 1;
	if (column == currentCol \|\| !hasValue(column - 1, row))
	w = 1;
	if (!hasValue(column, row + 1))
	s = 1;
	if (!hasValue(column + 1, row))
	e = 1;
	int index = n + 2 * s + 4 * e + 8 * w;
	cell.setCellStyle(styles[index]);
	}

	void setStyles() {
	headingStyle = wb.createCellStyle();
	headingStyle.setBorderTop(BORDER_THIN);
	headingStyle.setBorderBottom(BORDER_THIN);
	headingStyle.setBorderLeft(BORDER_THIN);
	headingStyle.setBorderRight(BORDER_THIN);
	headingStyle.setFillBackgroundColor(HSSFColor.LIGHT_YELLOW.index);
	headingStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
	headingStyle.setFillPattern(SOLID_FOREGROUND);
	styles = new HSSFCellStyle[16];
	for (int n = 0; n < 2; n++)
	for (int s = 0; s < 2; s++)
	for (int e = 0; e < 2; e++)
	for (int w = 0; w < 2; w++) {
	int index = n + 2 * s + 4 * e + 8 * w;
	styles[index] = wb.createCellStyle();
	styles[index].setBorderTop(n == 1 ? BORDER_THIN
	: BORDER_NONE);
	styles[index].setBorderBottom(s == 1 ? BORDER_THIN
	: BORDER_NONE);
	styles[index].setBorderRight(e == 1 ? BORDER_THIN
	: BORDER_NONE);
	styles[index].setBorderLeft(w == 1 ? BORDER_THIN
	: BORDER_NONE);
	styles[index].setFillBackgroundColor(HSSFColor.GOLD.index);
	styles[index].setFillForegroundColor(HSSFColor.GOLD.index);
	styles[index].setFillPattern(SOLID_FOREGROUND);
	}
	}

	/**
	* Check if o is a String or contains elements that satisfy isTextual(o)
	* <p>
	* Traverse down the Collection o if possible, and check the tree of collection at the deepest
	* level.
	* </p>
	*
	* @param o
	* Object to check
	* @return true if o is a String or is a Collection that contains a string at the deepest level.
	* false if o is not a String or Collection, or if it is a collection that contains
	* non-strings.
	* null if o is a Collection, but it is empty or contains nothing but Collections.
	*/
	Boolean isTextual(Object o) {
	if (o instanceof String)
	// We dug down and found a string. Hurray!
	return true;
	if (o instanceof Collection) {
	for (Object child : (Collection<?>) o) {
	Boolean isTxt = isTextual(child);
	if (isTxt == null)
	// Unknown, try next one
	continue;
	return isTxt;
	}
	/*
	* We looped through and found just empty collections (or we are an
	* empty collection), we don't know.
	*/
	return null;
	}
	// No, sorry mate.. o was neither a String or Collection
	return false;
	}

	/**
	* Get a cell at the given coordinates, create it if needed.
	*
	* @param column
	* @param row
	* @return
	*/
	HSSFCell getCell(int column, int row) {
	HSSFRow srow = sheet.getRow(row);
	if (srow == null)
	srow = sheet.createRow(row);
	HSSFCell scell = srow.getCell(column);
	if (scell == null)
	scell = srow.createCell(column);
	return scell;
	}

	/**
	* Check if a cell has a value.
	*
	* @param column
	* @param row
	* @return
	*/
	boolean hasValue(int column, int row) {
	HSSFRow srow = sheet.getRow(row);
	if (srow == null)
	return false;
	HSSFCell scell = srow.getCell(column);
	if (scell == null)
	return false;
	return true;
	}

	/**
	* Save the generated worksheet to a file
	*
	* @param file
	* to save to
	* @throws FileNotFoundException
	* @throws IOException
	*/
	void saveSheet(File file) throws IOException {
	FileOutputStream fos = new FileOutputStream(file);
	wb.write(fos);
	fos.close();
	}

	@Override
	protected String getFilter() {
	return "xls";
	}
	}