SheetsterBusinessRule.java

package JavaRESTClient;

/** 
 * SheetsterCommander.java
 *
 * ##LICENSE##
 *
 * Sep 17, 2011
 *
 * @author john
 * 
 */

import java.util.HashMap;
import java.util.Map;
import com.extentech.ExtenXLS.ExcelTools;
import com.extentech.toolkit.Logger;
/**
 * SheetsterBusinessRule is a sample REST Client for DocsFree Sheetster Server demonstrating
 * creation and execution of business logic from Java using the REST api
 *  
 * @author John McMahon :: Sep 17, 2011 :: Copyright &copy;2011 <a href = "http://www.extentech.com">Extentech Inc.</a>
 *
 */
public class SheetsterBusinessRule extends SheetsterRESTClient {
	private Map rules = new HashMap();
	private String sheetname = "Sheet1", firstDataCell = "A1", lastDataCell = null;
	
	/**
	 * Test a basic business rule contained in an Excel formula
	 * 			
	 * example business rule:
	 * 
	 * 1. find first quarter sales leader using VLOOKUP 
	 * 
	 * 		=VLOOKUP(A1,Sheet1!A2:C6,1,true)
	 * 
	 * 2. calculate bonus
	 * 
	 * @param args
	 */
	public static void main(String[] args) {
		// create in-memory Spreadsheet for calculation
		SheetsterBusinessRule cmd = new SheetsterBusinessRule();
		
		// TODO: enter the appropriate credentials here
		cmd.setServeraddress("http://127.0.0.1:8080");
		cmd.setUsername("admin@acme.com");
		cmd.setPassword("test");
		
		try {
			// login the user session
			cmd.authenticate();
			
			// create a new workbook
			cmd.createDocument();
			
			// NOTE: Important IF range_lookup is either TRUE or is omitted, 
			// the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.
			
			// first name, last name, sales totals for quarter
			Object[][] data =  {{new Float(123333.340), "Larry", 	"Bennett"},
								{new Float(258662.240), "Sarah", 	"Moore"},
								{new Float(344762.240), "Carl", 	"Parker"},
								{new Float(441762.240), "May",	 	"Lee"},
								{new Float(543469.080), "Timmy", 	"Vang"}};
			
			cmd.addData(data);
			
			// if this were tiered, ceiling might be 300000, resulting in "Carl Parker" being tier sales leader
			Float ceiling = new Float(1000000);
			cmd.addCell("C1",ceiling);
			
			/**
			 * business rule:
			 * 
			 * 1. find name of quarter sales leader using VLOOKUP 
			 * 
			 * 		=VLOOKUP(A1,Sheet1!A2:C6,1,true)
			 * 
			 * 2. calculate bonus
			 * 
			 * 
			 */
			cmd.setRule("SalesLeader", "=CONCATENATE(VLOOKUP(C1,A2:C6,2,true),\" \",VLOOKUP(C1,A2:C6,3,true))");
			Object salesperson =  cmd.execRule("SalesLeader");
			Logger.logInfo("The Sales Leader is: " + salesperson);
			
			cmd.setRule("TopSales", "=VLOOKUP(C1,A2:C6,1,true)");
			Object topsales = cmd.execRule("TopSales");
			Logger.logInfo("Sales for "+salesperson+" in quarter were: " + topsales );
			
			cmd.setRule("Bonus", "=SUM("+topsales+"*.1)");
			Logger.logInfo(salesperson + "'s bonus is: " + cmd.execRule("Bonus"));
						
			
			// clean it up
			cmd.destroyDocument();
			
			// let's take a look at the Spreadsheet
			// yes it is removed from database
			// but yes still in server RAM
			cmd.launchBrowser();
			
		}catch (Exception e) {
			Logger.logErr("create workbook failed.",e);
		}
	}
	
	
	/**
	 * bulk inserts rows of data into sheet
	 * 
	 * In order to leave room for the business logic calculations this 
	 * Adds cells starting at A3
	 * 
	 * @param objarr
	 */
	public void addData(Object[][] objarr)
	throws Exception{
		for(int t=0;t<objarr.length;t++){ // iterate rows
			Object[] bo = objarr[t];
			for(int x=0;x<bo.length;x++){ // put together
				String celladdr = ExcelTools.getAlphaVal(x);
				celladdr += (t+2);
				addCell(celladdr, bo[x]);;
			}
		}
	}
	
	/**
	 * adds a named business rule (formula) to the sheet at A2
	 * 
	 * @param s
	 * @param fmla
	 */
	public void setRule(String s, String fmla)
	throws Exception{
		rules.put(s,fmla);
		String cmd = "rename";
		if(rules.size()>1) // handle multiple rules
			cmd = "copy";
		else{ // delete distracting sheets
			deleteSheet("Sheet2");
			deleteSheet("Sheet3");
		}
		
		String rename = "/workbook/id/" + super.getMemeId() + "/json/sheet/"+cmd+"/" + sheetname + "/" + s;
		
		rename = getHTTPData(rename);
		if(rename.contains("\"sheetname\":\""+ s))
			sheetname = s;
		
		addCell("A1", s);
		addCell("B1", fmla);
	};
	
	
	/**
	 * executes and return the business rule
	 * 
	 * @param rulename
	 * @return
	 * @throws Exception 
	 */
	public Object execRule(String rulename)
	throws Exception{
		return getCell("B1");
	}
	
	
	/**
	 * delete a worksheet
	 * 
	 * @param sheetname
	 * @throws Exception
	 */
	public void deleteSheet(String sheetname)
	throws Exception{
		String delete = "/workbook/id/" + super.getMemeId() + "/json/sheet/delete/"+sheetname+"/0";
		getHTTPData(delete);
	}
	
	/**
	 * @param celladdr
	 * @param val
	 * @throws Exception
	 */
	public void addCell(String celladdr, Object val)
	throws Exception{
		String addcmd = "/workbook/id/" + getMemeId()+"/json/cell/add/"+sheetname+"!"; // always use Sheet1 
		String cellval = addcmd;
		cellval += celladdr;
		cellval += "/";
		cellval += val.toString();
		getHTTPData(cellval);
	}
	
	/**
	 * @param celladdr
	 * @throws Exception
	 */
	public Object getCell(String celladdr)
	throws Exception{
		String getcmd = "/workbook/id/" + getMemeId()+"/txt/cell/get/"+sheetname+"!"; // always use Sheet1 
		String cellval = getcmd;
		cellval += celladdr;
		cellval += "/0";
		
		return getHTTPObject(cellval);
	}
}

SheetsterLoadTest.java

package JavaRESTClient;

/** 
 * SheetsterCommander.java
 *
 * ##LICENSE##
 *
 * Sep 17, 2011
 *
 * @author john
 * 
 */

import com.extentech.ExtenXLS.ExcelTools;
import com.extentech.toolkit.Logger;
/**
 * SheetsterLoadTest is a sample REST Client for DocsFree Sheetster Server demonstrating
 * creation and execution of business logic from Java using the REST api
 *  
 * @author John McMahon :: Aug 29, 2011 :: Copyright &copy;2011 <a href = "http://www.extentech.com">Extentech Inc.</a>
 *
 */
public class SheetsterLoadTest extends SheetsterRESTClient implements Runnable {
	private String sheetname = "Sheet1";
	
	
	/**
	 * create a bunch of concurrent "users" performing expensive operations
	 * 
	 * @param args
	 */
	public static void main(String[] args) {
		// simulate a 1000 users
		for(int x=0;x<100;x++){
		
			// create in-memory Spreadsheet for calculation
			SheetsterLoadTest cmd = new SheetsterLoadTest();
			Thread txx = new Thread(cmd);
			txx.start();
			
		}
	}
			
	/**
	 * threaded runner allows stress testing
	 * 
	 * @see java.lang.Runnable#run()
	 */
	public void run() {
		
		// TODO: enter the appropriate credentials here
		this.setServeraddress("http://127.0.0.1:8000");
		this.setUsername("admin@acme.com");
		this.setPassword("T1TAN1UM");
		
		try {
			// login the user session
			this.authenticate();
			this.setMemeId("3");
			String celladdr = "C5";
			String[] bo = {"234.2","2342.2","1111","3523"};
			for(int t=0;t<bo.length;t++)
				this.addCell(celladdr, bo[t]);
			
			celladdr = "D6";
			String[] box = {"234.2","2342.2","1111","3523"};
			for(int t=0;t<box.length;t++)
				this.addCell(celladdr, box[t]);
				
			celladdr = "E4";
			String[] bot = {"234.2","2342.2","1111","3523"};
			for(int t=0;t<bot.length;t++)
				this.addCell(celladdr, bot[t]);
			// create a new workbook
			createDocument();
			
			// first name, last name, sales totals for quarter
			Object[][] data =  {{new Float(123333.340), "Larry", 	"Bennett"},
								{new Float(258662.240), "Sarah", 	"Moore"},
								{new Float(344762.240), "Carl", 	"Parker"},
								{new Float(441762.240), "May",	 	"Lee"},
								{new Float(543469.080), "Timmy", 	"Vang"}};
			
			addData(data);
			
			// clean it up
			destroyDocument();
			
		}catch (Exception e) {
			Logger.logErr("SheetsterLoad Test Failed.",e);
		}
	}
	
	
	/**
	 * bulk inserts rows of data into sheet
	 * 
	 * In order to leave room for the business logic calculations this 
	 * Adds cells starting at A3
	 * 
	 * @param objarr
	 */
	public void addData(Object[][] objarr)
	throws Exception{
		for(int t=0;t<objarr.length;t++){ // iterate rows
			Object[] bo = objarr[t];
			for(int x=0;x<bo.length;x++){ // put together
				String celladdr = ExcelTools.getAlphaVal(x);
				celladdr += (t+2);
				addCell(celladdr, bo[x]);;
			}
		}
	}
	
	
	/**
	 * delete a worksheet
	 * 
	 * @param sheetname
	 * @throws Exception
	 */
	public void deleteSheet(String sheetname)
	throws Exception{
		String delete = "/workbook/id/" + super.getMemeId() + "/json/sheet/delete/"+sheetname+"/0";
		getHTTPData(delete);
	}
	
	/**
	 * @param celladdr
	 * @param val
	 * @throws Exception
	 */
	public void addCell(String celladdr, Object val)
	throws Exception{
		String addcmd = "/workbook/id/" + getMemeId()+"/json/cell/add/"+sheetname+"!"; // always use Sheet1 
		String cellval = addcmd;
		cellval += celladdr;
		cellval += "/";
		cellval += java.net.URLEncoder.encode(val.toString(), "UTF-8");
		System.out.println("setting val: " + val + " on cell: " + celladdr);
		getHTTPData(cellval);
	}
	
	/**
	 * @param celladdr
	 * @throws Exception
	 */
	public Object getCell(String celladdr)
	throws Exception{
		String getcmd = "/workbook/id/" + getMemeId()+"/txt/cell/get/"+sheetname+"!"; // always use Sheet1 
		String cellval = getcmd;
		cellval += celladdr;
		cellval += "/0";
		
		return getHTTPObject(cellval);
	}
}

SheetsterRESTClient.java

package JavaRESTClient;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLConnection;
import java.util.zip.GZIPInputStream;
import com.extentech.toolkit.Logger;
/**
 * Helper class that encapsulates REST api calls to DocsFree Sheetster which provides for simplified REST handling
 * 
 * @author john :: Sep 17, 2011 :: Copyright &copy;2011 <a href = "http://www.extentech.com">Extentech Inc.</a>
 *
 */
public class SheetsterRESTClient implements Runnable {
	/*
	 * NOTE: set the following credentials:
	 */
	private String username = "", password = "",
			serveraddress = "http://127.0.0.1:8000", sessionAuthTicket = null,
			memeId;
	
	private boolean debug = true;
	/**
	 * @param debug
	 *            The debug to set.
	 */
	public void setDebug(boolean debug) {
		this.debug = debug;
	}
	/**
	 * @return Returns the memeId.
	 */
	public String getMemeId() {
		return memeId;
	}
	/**
	 * @param memeId
	 *            The memeId to set.
	 */
	public void setMemeId(String memeId) {
		this.memeId = memeId;
	}
	/**
	 * @return Returns the username.
	 */
	public String getUsername() {
		return username;
	}
	/**
	 * @param username
	 *            The username to set.
	 */
	public void setUsername(String u) {
		username = u;
	}
	/**
	 * @return Returns the password.
	 */
	public String getPassword() {
		return password;
	}
	/**
	 * @param password
	 *            The password to set.
	 */
	public void setPassword(String p) {
		password = p;
	}
	/**
	 * @return Returns the serveraddress.
	 */
	public String getServeraddress() {
		return serveraddress;
	}
	/**
	 * @param serveraddress
	 *            The serveraddress to set.
	 */
	public void setServeraddress(String s) {
		serveraddress = s;
	}
	
	/**
	 * Open a browser window and loads the current document
	 */
	public void launchBrowser(){
		String openstr = serveraddress + "/index.jsp?sessionAuthTicket=" + sessionAuthTicket + "&meme_id=" + memeId;
		launchBrowser(openstr);
	}
	/**
	 * launches native browser
	 * 
	 * @param cmd
	 */
	public static void launchBrowser(String cmd) {
		try {
			edu.stanford.cs.ejalbert.BrowserLauncher.openURL(cmd);
		} catch (Exception e) {
			Logger.logErr("WARNING: ServerControl.launchBrowser failed: " + e);
		}
	}
	/**
	 * run through the tests
	 * 
	 * @param args
	 */
	public static void main(String[] args) {
		try {
			String sid = null;
			SheetsterRESTClient test3 = new SheetsterRESTClient();
			test3.run();
			String openstr = test3.serveraddress
					+ "/index.jsp?sessionAuthTicket=" + test3.sessionAuthTicket
					+ "&meme_id=" + test3.memeId;
			Logger.logInfo("Opening: " + openstr);
			launchBrowser(openstr);
			for (int t = 0; t < 10; t++) {
				SheetsterRESTClient test = new SheetsterRESTClient();
				Thread tx = new Thread(test);
				tx.start();
				// cache session id
				if (sid == null)
					sid = test.session;
				else
					test.session = sid;
				try {
					long sleeps = Math.round(Math.random() * 3000);
					Logger.logInfo("New Session in :" + (float) sleeps / 1000
							+ " seconds.");
					Thread.sleep(sleeps);
				} catch (Exception e) {
					;
				}
			}
		} catch (Exception e) {
			Logger.logErr("SheetsterRESTClient failed" + e.toString());
		}
	}
	/**
	 * threaded runner allows stress testing
	 * 
	 * @see java.lang.Runnable#run()
	 */
	public void run() {
		try {
			// authenticate and create a new workbook
			createDocumentFromURL(serveraddress, username,password);
			// test a variety of REST commands
			testRESTapi(serveraddress);
		} catch (Exception e) {
			Logger.logErr("SheetsterRESTClient failed: " + e.toString());
		}
	}
	/**
	 * runs a suite of JSON/XML assertions against a running Server
	 * 
	 * optional load testing
	 * 
	 * @param srvurl
	 * @param testload
	 */
	public void testRESTapi(String srvurl) throws Exception {
		if (srvurl == null)
			srvurl = "http://127.0.0.1:8000"; // default to 127.0.0.1
		// create new document on server, return document ID String
		createDocumentFromURL(srvurl, username, password);
		String[] reqs = {
				// the usual init steps...
				// memeId + "/xml/workbook/copy/0", // test copy existing
				memeId + "/json/workbook/getsheetnames/",
				memeId + "/json/row/getheader/Sheet1/",
				memeId + "/json/style/getall/",
				memeId + "/json/namedrange/getallranges/",
				memeId + "/json/sheet/get/Sheet1/",
				memeId + "/json/chart/getcharts/Sheet1/",
				// memeId + "/json/cellbinder/getboundranges/Sheet1/",
				memeId + "/json/workbook/getrestlog/0",
				// insert a new sheet named "New Sheet" at index 0
				memeId + "/xml/sheet/add/New Sheet/0",
				// insert a new cell at "New Sheet!A1" with the initial value of
				// "Hello World!"
				memeId + "/xml/cell/get/New Sheet!A1/",
				// insert a new cell at "New Sheet!A2" with the initial value of
				// "=SUM(10+11)"
				memeId + "/json/cell/add/New%20Sheet!A2/=SUM(10+11)",
				// create a named range
				memeId + "/json/namedrange/createnamedrange/New Sheet/A1:A2?rangeName=input_1",
				
				// access a named range
				memeId + "/json/namedrange/getcellvalues/input_1",
				
				// runs the cellbinder to merge data with loaded workbook
				// memeId + "/json/cellbinder/bind/1314?year=2008",
				// gets a whole row of JavaScript Cell Objects from row 1:
				memeId + "/json/cellrange/get/Sheet1!A1:VU1",
				memeId + "/json/sheet/get/Sheet1/",
				memeId + "/json/cell/add/Sheet1!B3/%3Dsum(b2%2B100)",
				memeId + "/json/messaging/getmessages/",
				memeId + "/json/workbook/getsheetnames/",
				memeId + "/json/style/getall/",
				memeId + "/json/col/resize/Sheet1!A/324",
				// some more advanced stuff...
				memeId + "/json/col/resize/Sheet1!A/324",
				// rename this doc
				memeId + "/xml/workbook/setname/workbook/new name",
				memeId + "/json/col/resize/Sheet1!A/324",
				memeId + "/xml/cell/add/Sheet1!c4/5000",
				memeId + "/xml/cell/add/Sheet1!c5/1500",
				memeId + "/xml/cell/add/Sheet1!C6/=sum(c4+c5)",
		// cleanup
		// memeId + "/json/workbook/delete"
		};
		// convert human-readable commands to REST urls
		for (int t = 0; t < reqs.length; t++) {
			try {
				// TODO: implement assertions
				String svx = srvurl + "/workbook/id/" + reqs[t];
				if (debug)
					Logger.logInfo("TESTING: "+ com.extentech.ExtenXLS.plugin.builtin.REST2English.getTranslated(svx));
				if (debug)
					Logger.logInfo("COMMAND: " + svx);
				if (debug)
					Logger.logInfo("RESULT FROM SERVER: " + getHTTPData(svx)
							+ "\r\n");
			} catch (Exception ex) {
				Logger.logErr("SheetsterRESTClient testServer failed: "
						+ ex.toString());
			}
		}
	}
	
	/**
	 * create a WebWorkBook
	 * 
	 * @return
	 * @throws Exception
	 */
	public void createDocument() throws Exception {
		createDocumentFromURL(serveraddress, username, password);
	}
	/**
	 * create a WebWorkBook
	 * 
	 * @return
	 * @throws Exception
	 */
	public String destroyDocument() throws Exception {
		
		// cleanup
		String delete = "/workbook/id/" +memeId + "/json/workbook/delete";
		return getHTTPData(delete);
	}
	
	/**
	 * Creating the document from a URL
	 * 
	 * 
	 * Sep 13, 2011
	 * 
	 * @param srvurl
	 * @param username
	 * @param password
	 * @return
	 * @throws IOException
	 * @throws MalformedURLException
	 */
	public void createDocumentFromURL(String srvurl, String username,
			String password) throws Exception {
		authenticate();
		// create new workbook -- do the usual init steps
		String new_book_url = srvurl + "/workbook/id/-1/csv/workbook/getid";
		memeId = new String(getHTTPBytes(new_book_url));
	}
	/**
	 * Logs in the user with the credentials set on this object
	 * 
	 */
	public void authenticate() throws Exception {
		if (session == null) {
			// authenticate the user
			String login_url = serveraddress
					+ "/workbook/id/-2/txt/system/login/?username=" + username
					+ "&password=" + password;
			sessionAuthTicket = new String(getHTTPBytes(login_url));
			if (debug)
				Logger
						.logInfo("SheetsterRESTClient successfully logged in user got session auth ticket: "
								+ sessionAuthTicket);
		}
	}
	String session = null;

	/**
	 * fetch the contents of a URL as a buest-guess Java data type...
	 * 
	 * @param urlstr
	 * @return Object value returned from Server
	 * @throws IOException
	 * @throws MalformedURLException
	 */
	public Object getHTTPObject(String urlstr) throws Exception {
		// Create a URL object from urlString
		String ret = new String(getHTTPBytes(urlstr));
		// try numbers
		try{
			Double d = Double.parseDouble(ret);
			return d;
		}catch(NumberFormatException de){
			try{
				Float f = Float.parseFloat(ret);
				return f;
			}catch(NumberFormatException fe){
				try{
					Integer i  = Integer.parseInt(ret);
					return i;
				}catch(NumberFormatException ie){
					// ok not a number
				}
			}
		}
		// try boolean
		if(ret.equalsIgnoreCase("true")){
			return new Boolean(true);
		}else if(ret.equalsIgnoreCase("false")){
			return new Boolean(false);
		}
		
		//finally returns string val
		return ret;
		
	}
	
	/**
	 * fetch the contents of a URL as a string...
	 * 
	 * @param urlstr
	 * @return String value returned from Server
	 * @throws IOException
	 * @throws MalformedURLException
	 */
	public String getHTTPData(String urlstr) throws Exception {
		// Create a URL object from urlString
		return new String(getHTTPBytes(urlstr));
	}
	/**
	 * fetch the contents of a URL as a byte array
	 * 
	 * @param urlstr
	 * @return byte values returned from server
	 * @throws IOException
	 * @throws MalformedURLException
	 */
	public byte[] getHTTPBytes(String urlstr) throws Exception {
		if(!urlstr.startsWith(serveraddress)){
			urlstr = serveraddress + urlstr;
		}
		
		// Create a URL object from urlString
		URL pageURL = new URL(urlstr);
		// Open a connection to the URL
		URLConnection pageConnection;
		pageConnection = pageURL.openConnection();
		if (session != null)
			pageConnection.addRequestProperty("Cookie", session);
		// TODO: Use Base64 Security over HTTPS
		pageConnection.setConnectTimeout(10000);
		// Get the InputStream from the URL connection
		InputStream webPageInputStream;
		pageConnection.connect();
		webPageInputStream = pageConnection.getInputStream();
		// handle the InputStream encoding (for gzip output)
		String enc = pageConnection.getContentEncoding();
		try {
			if (enc.equalsIgnoreCase("gzip"))
				webPageInputStream = new GZIPInputStream(pageConnection
						.getInputStream());
		} catch (NullPointerException e) {
			;// normal
		}
		// Read the web page via the InputStream
		ByteArrayOutputStream webPageData = new ByteArrayOutputStream();
		int totalBytesRead = 0;
		boolean moreToRead = true;
		byte[] readBuf = new byte[2048]; // Read the web page in 4K chunks
		while (moreToRead) {
			int numBytesRead = 0;
			try {
				numBytesRead = webPageInputStream.read(readBuf);
			} catch (IOException e) {
				moreToRead = false;
			}
			if (numBytesRead > 0) {
				totalBytesRead += numBytesRead;
				webPageData.write(readBuf, 0, numBytesRead);
			} else
				moreToRead = false;
		}
		// Logger.logInfo("Communicator read: " + totalBytesRead +
		// " bytes from:" + urlstr);
		webPageInputStream.close();
		// webPageData.setLength(totalBytesRead);
		// Pull the session ID out of the connection headers
		if (session == null)
			try {
				session = pageConnection.getHeaderField("Set-Cookie");
				if (session == null)
					throw new Exception("session cookie not present");
				if (!session.startsWith("sessionid="))
					throw new Exception("invalid session cookie");
				// Strip off any extraneous header fields
				if (session.contains(";"))
					session = session.substring(0, session.indexOf(";"));
			} catch (Exception e) {
				throw new RuntimeException("failed to parse session cookie", e);
			}
		// 
		return webPageData.toByteArray();
	}
}

Browser not supported