The wikiCalc program has two features that facilitate its use as a web-based system for displaying data. The first is the HTML text-output cell format setting and the second is the wkcHTTP built-in spreadsheet function. The WKCcallableUtilities.pl program, included in the wikiCalc distribution, is a sample program to illustrate interfacing with the wkcHTTP function and makes use of the HTML formatting for one of its functions.
You have the option of treating cell text in a variety of ways for display. The Plain Text setting displays characters as their normal ASCII/UTF-8 rendering, with line breaks displayed as line breaks. The wiki setting formats the text in a manner similar to many wikis, providing simple markup commands for indicating bold, italics, headers, lists, links, etc. Finally, the HTML setting treats the characters as HTML text, with HTML tags passed through to the browser unmodified.
Cell values in wikiCalc have a type and sub-type attribute. For example, there are numeric values, text values, and error values. Within numeric, there are date and time sub-types, percent, currency, etc. Within text, there is plain text, HTML text, and wiki text. Various functions and forms of input create values in an appropriate type. (For example, the NOW() function returns the a date/time value.) The default "General" format looks to those value types to determine the display format.
You can explicitly create an HTML text value using the built-in wkcHTML function. Alternatively, you can display any text interpreted as HTML using an HTML cell text format setting.
The wkcHTTP wikiCalc-specific function gives wikiCalc access to external web services during recalculation. It accesses a specific URL on the Internet using the HTTP (web) protocol and can provide optional arguments in a manner similar to an HTML <form>. The web page/service accessed returns a value which becomes the value of the function for use in a formula. That value can be numeric, text, or error.
You use wkcHTTP like this:
wkcHTTP(url, timeout, errorvalue, method, arg1, arg2, ...)
An example would be:
wkcHTTP("http://www.domain.com/cgi-bin/WKCcallableUtilities.pl",
10, "Error using HTTP", "GET", "rand", 100)
All of the arguments are optional except for url. Spaces between operators and values are ignored and make it easier to type (especially in Multi-line Editing which will wrap the text to fit the display better than single line editing that just scrolls sideward). Like all functions, the function name "wkcHTTP" is case-insensitive, so you can use WKCHTTP.
The url is the URL, including "http://", to which an HTTP request is made. A common way to provide the URL is to have it as a text value in another cell, perhaps formatted with the "hidden" display attribute, and just use a cell reference as this first argument to wkcHTTP.
The timeout is the number of seconds after which a timeout error is assumed. The default is 10 seconds.
The errorvalue is the value to be returned by the function if there is an error (timeout, URL not found, etc.). For example, if it is the errorvalue is the number 0, then a zero will be returned, but if it is a text message "Error", the text value "Error" will be returned. The default is "".
The method is either the text string "GET" or "POST". These tell the function which type of HTTP request to make. The default is "GET". A "GET" request may have a limit on the amount of characters, so for arguments with large amounts of text, you may want to use "POST".
An HTTP request is made to the URL along with the values of the arguments that follow the method. Cell ranges are treated as if all the cells in the range were typed in one after another in order. The arguments are formatted in the request just like HTML form arguments in an HTTP request. They are passed to the URL as parameters "V1", "V2", etc. The format of each passed value is "Tvalue", where "T" is N (numeric), T (text), B (blank), or E (error).
In the example above, the URL requested from the target server would be:
http://www.domain.com/cgi-bin/WKCcallableUtilities.pl?V1=Trand&V2=N100
The URL should return a plain text response with a value in the form:
Tvalue
Here "T" is "N" for a numeric value (followed by just numbers, with an optional minus sign and/or decimal point), "T" for text, "H" for HTML text, or "E" for an error value (with the value being text to describe the error), and "value" is the value itself. Text may be any ASCII (just the 96 7-bit characters) or UTF-8 string of bytes.
Included with the wikiCalc distribution is the WKCcallableUtilities.pl program. This is a normal Perl program to be installed on a normal web server. It is designed to be used from wikiCalc through the wkcHTTP function. It can be run on any server -- it does not have to be the same server running wikiCalc. It does not specifically access the wikiCalc data files.
When invoked, the first argument (V1) is the function to be performed. The functions are as follows:
Retrieve another web page and return a portion of it found with a regular expression as follows:
V1=regexget, V2=url, V3=regex, V4=returntype
The regular expression (for Perl) should include a capturing set of parenthesis and $1 will be returned as the value of the function. The "returntype" is: N, T, or H for number, text, or HTML. A text error value will be returned if there is an error.
For example, the following function call is similar to the function call used in the screencast demo of wikiCalc to get stock quotes:
wkcHTTP("http://www.domain.com/cgi-bin/WKCcallableUtilities.pl",
10, "Error", "GET",
"regexget", "http://finance.yahoo.com/q?s=msft",
"Last Trade:.+?<b>(.+?)<", "N")
This call retrieves the Yahoo web page for Microsoft stock. It then uses a regular expression to search for the characters "Last Trade:" and then returns the characters following that which are within "<b>" and "</b>". Those characters are to be treated as a numeric value.
In the screencast demo, the URL is actually created using an expression like the following that adds the stock symbol in another cell to the start of the web page address:
"http://finance.yahoo.com/q?s="&B3
Return the HTML for a graph of values in the argument list, with optional text labels after them. Numeric values will be graphed and text values will be used as the labels in the same order.
This is not a "real" graphing program. It just produces a very simple bar chart using HTML tables and <div> tags for demo purposes.
The arguments are:
V1=graph, V2=totalHeightInPixels, V3=totalWidthInPixels, V4=color,
V5, etc.=value1, value2..., label1, label2...
The color is a CSS type of color specification, like "red" or "#FF0000".
Here is an example of a graph that would be created using the following function:
wkcHTTP("http://appropriate-url/WKCcallableUtilities.pl",
10, "Error", "GET",
"graph", 100, 300, "green",
5, 10, 1, 3, "First", "Second", "Third", "Fourth")
|
|
|
| ||||||||
| First | Second | Third | Fourth |
These features have a variety of security issues to consider. They include: An author can include any HTML in a sheet to be published, including Javascript, forms, etc.; the wkcHTTP function can invoke any URL, including ones with protocols other than "http://"; and WKCcallableUtilities.pl may be accessed by any browser or program on the web that has its URL.
WKCcallableUtilities.pl (through its regexget function) could possibly be used by other users as a way to access data elsewhere on the web (and maybe even on your server with appropriately created URLs) with your server acting as a proxy. The implications of this have not been fully worked out. Behind a corporate firewall this may not be an issue but it must be considered by someone knowledgeable about the hosting environment.
The WKCcallableUtilities.pl code is made available for demonstration purposes only. It provides a working piece of code you can use to help learn how to interface with wikiCalc. In a production environment you will probably want to run your own code to be called by wkcHTTP. To be safe you may not want to copy the WKCcallableUtilities.pl file to a remote server or at least be careful to not let it get execute permission. (On many server systems Perl files are not automatically executable by CGI and must be explicitly made executable. Make sure that is the case with your system if you are depending upon this form of protection.)
Periodically check the bug list and do web searches to see if any wikiCalc vulnerabilities have been reported here or elsewhere.