u(functionName,p1,p2,p3...) x(formula,p1,p2,p3...) Where formula is a spreadsheet formula or built-in function, followed by a variable list of parameters. The passed formula can take advantage of xLite "placeholders" e.g. x("20+ :1 + :2",20,40) will return 80, x("upper(:1:)","abc") returns ABC. The formula's text, with any placeholders replaced, is passed to Excel's Application object's Evaluate method, so not withstanding some of the limitations associated with this function (see here) it's possible to use most Excel formulas within a SQL statement. Only built-in functions (i.e not User Defined Functions) can be called in this manner. To call a UDF use the u() function. When specifying the formula do not include the leading "=", this will ensure formula is evaluated inline along with the rest of the SQL statement. If preceded by "=", the formula is passed back as text. This can sometimes be useful either for testing purposes or to get around Application.Evaluate limitations. f(address,p1,p2,p3...) The f() function (aka Worksheet Function) pastes the function's p1,p2,p3.. parametes to the address specified. An additional "Run ID" integer parameter is prepended to the parameter list. This Run ID can then be referenced by the xLiteReturn() function to pass back a result value to the calling SQL statement. This allows for the development of "Worksheet Functions", i.e. functions callable by SQL but developed using standard cascading Excel formulas. Good for testing complex calculation workbooks and for production use where data volumes are low (very VERY slow compared to the u() or x() functions). Two further methods of developing functions afforded by xLite are xLitePyScript and xLiteWorkbook. I'll cover xLiteWorkbook in a future post. xLitePyScript(address,p1,p2,p3) Unlike u(),x() and f() which are SQlite user defined functions (i.e. are called inline on SQL statement like any other SQLite function), xLitePyScript is an Excel UDF and must be wrapped by the u() function when referenced in SQL (this is likely to change in near future, a py() function?). The address can be either a string representation of an Excel range e.g. "Sheet2!A3:Sheet2!D6" or a named range. If the range is a single cell the function will expect to find the full Python script within, if a multi-cell range then the script will utilise cells as Python indents. The scripts are in fact anonymous parameterless Python functions. Parameters are passed via xLite "placeholders" which are replaced before the script is passed to the Python interpreter e.g.
It this case 2nd parameter is less than 40, return 1st parameter increased by 10% otherwise return unchanged. To see the above in action download the test_worksheet_functions.xls from here ... |
Master Datasmith? Well, I could style myself a data analyst/systems integrator/reporting analyst/DBA/business analyst/programmer but I'd end up all /'d out. I hope the term datasmith conveys the craft nature of my work with data, for it is a craft that I'm particularly good at (hence the master adjective) and also take great pride in. Read more ... Gobán Saor My father who came from a long line of stone masons and master builders, told me many of these stories and also used to take me to a magical island in our local bogland that he called the Gobán Saor’s island. (Now better known as the discovery place of the Derrynaflan Chalice ). Continuing in the tradition of freelance craftsman (data mason rather than stone mason) I’ve used gobansaor as my nom de plume in forums, online apps etc. over the years, partially to keep a tradition alive but to be honest usually because I’m sure nobody else will have taken the name already  |
Introducing xLite >
User Defined Functions u(),x(),f(), xLitePyScriptposted 8 Dec 2009 07:32 by Tom Gleeson [ updated 11 Feb 2010 03:54 ] |