Silly, fun and useful things to do with Resolver One
  • HOME
  • About
  • Articles
  • Fun
  • Snippets
  • Disclaimer
  • Useful Links

Site by Voidspace

Email Michael

Download Resolver One

RSS Feed RSS Feed

Resolver Hacks contains information and code for doing fun and useful things with Resolver One. This site is created and maintained by Michael Foord, not by Resolver Systems. Please read the disclaimer before using any of the code on this site.

Site Built with rest2web

Creative Commons License

IronPython Wiki

IronPython in Action IronPython in Action

Spreadsheet Functions

Contents

  • Introduction
    • Case-Sensitivity
    • Using Cell Objects and Cell Values
  • LN
  • POWER
  • PRODUCT

Introduction

Resolver has many built-in functions, which are available to you to use within formulae and from user code. Some of these are 'standard' functions, used by several different spreadsheet programs. Not all of functions provided by other spreadsheets are available in Resolver yet, which may cause you difficulties when you import spreadsheets.

However, easily adding new code to a spreadsheet (whether it be functions, classes or variables) and using it from formulae is where Resolver shines! In fact, adding new functions that you can use in your formulae is one of the easiest ways that you can get started with extending Resolver through user code.

This page has a few additional functions implemented. If you find others that you need, let me know as they are generally easy to implement. Also let me know if you write any of your own, and I can put them up here for other people.

To use the functions here, either copy them to the Pre-constants User Code, or put them in a Python module and import them.

Case-Sensitivity

Spreadsheet functions tend to be case-insensitive. So if I write the formulae =sum(A1, A2), it is equivalent to =SUM(A1, A2).

Python is case-sensitive however. To get round this problem, Resolver knows about its spreadsheet functions, and whenever you use them in a formula it converts them to uppercase in the generated formula code.

(So you can't use the built in Python sum function in Resolver formulae, Resolver will always translate it into a call to the Resolver SUM function. You can get round this, should you really need to, by aliasing the Python sum function with another name. E.g. Put pythonSum = sum in the Pre-constants user code, and you can use pythonSum in your formulae.)

Resolver won't know that new functions we add are spreadsheet functions, so it won't treat them as case insensitive. You will have to use the same casing in your formulae as you use for your function definitions. In general, the Resolver spreadsheet functions have all uppercase names, so it is probably a good idea to stick to that convention.

Using Cell Objects and Cell Values

When you write the formula =A1 + A2, what you are saying is that you want the current cell to have its value be the value in cell A1 plus the value stored in A2.

This gets converted to generated formula code something like this:

workbook['Sheet1'].A3 = workbook['Sheet1'].A1 + workbook['Sheet1'].A2

Accessing cells on worksheets like this is the easiest way of using or setting the values contained in cells. Sometimes you want to access the cell object, rather than just fetching or setting the value. This might be to call the Offset method on the cell, or setting traits like the BackColor, Bold or borders. You can access cell objects using the Cells attribute of worksheets:

workbook['Sheet1'].Cells.A3.Bold = True
workbook['Sheet1'].Cells.A3.Error = Exception("Something went wrong!")

If you define your own function then calling that function with cell references inside formulae will automatically have the values contained in the cell passed in. The formula =function(A1, A2) will converted into the following generated code:

workbook['Sheet1'].A3 = function(workbook['Sheet1'].A1, workbook['Sheet1'].A2)

If you need function that handles cell ranges (or even whole worksheets) as well as cells values, they will have to be a bit cleverer.

If you do need any of the functions here to do more than just take values from cells, then let me know. It is slightly more complicated, but not very difficult.

LN

from System.Math import Log as LN

The LN function returns the natural logarithm of a number.

USAGE: =LN(45), =LN(A1)

POWER

from System.Math import Pow as POWER

The POWER function takes two numbers and returns the first number raised to the power of the second.

USAGE: =POWER(2, 10), =POWER(A1, B4)

Note

These first two examples have provided mathematical functions using System.Math from .NET. This has a wide range of function that you might want to use. You can also use the Python standard library math module, which has a similar (but not identical) range of mathematical functions.

PRODUCT

from operator import mul
def PRODUCT(*args):
    return reduce(mul, args)

The PRODUCT function takes any number of input arguments, and returns the value obtained by multiplying them together.

USAGE: =PRODUCT(1, 3, 0.5, 7), =PRODUCT(2.1, B2, C9)


Hosted by Webfaction

Return to Top

Last edited Mon Dec 10 14:21:42 2007.

Copyright ©2007 Michael Foord. All rights reserved. Design by Elemental Works. Logo by FuchsiaShock. Valid XHTML & CSS.

This work is licensed under a Creative Commons License.