JavaRESTClient
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 ©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 ©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 ©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();
}
}