package LinkChecker;
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.ExtenXLS.ExcelTools;
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 Extentech Inc.
*
*/
public class SheetsterRESTClient implements Runnable {
// change this to the ID of the report template
private static final int REPORT_ID = 329;
/*
* NOTE: set the following credentials:
*/
private String username = "", password = "",
serveraddress = "http://127.0.0.1:8000", sessionAuthTicket = null,
memeId;
String portalSheetname = "Sheet1", errorSheetname = "Sheet1", dataSheetName="Sheet1";
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?meme_id=" + memeId+"&sessionAuthTicket=" + sessionAuthTicket;
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";
// copy existing template that you have already uploaded
String new_book_url = srvurl + "/workbook/id/"+REPORT_ID+"/csv/workbook/copy";
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
String strx = new String(getHTTPBytes(urlstr));
// Logger.logInfo(urlstr + ":" + strx);
return strx;
}
/**
* 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);
else
Logger.logWarn("Getting Valid 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();
}
/**
* @param celladdr
* @param val
* @throws Exception
*/
public void addCell(String celladdr, String sn, Object val)
throws Exception{
String cellval = "/workbook/id/" + getMemeId()+"/json/cell/add/"+sn+"!"; // always use Sheet1
cellval += celladdr;
cellval += "/";
cellval += val.toString();
getHTTPData(cellval);
}
/**
* bulk sets a rows of data into sheet
*
* @param objarr
*/
public void setDataRow(Object[][] objarr, String sn, int row)
throws Exception{
for(int t=0;t