[ANN] LXL (Like Excel) 0.1.0 - A mini-language that mimics Microsoft Excel formulas.

[Kevin Howe]

Hi all,

I've just released LXL (Like Excel) 0.1.0, a mini-language that mimics
Microsoft Excel formulas. It can be easily extended with new constants and
functions.

http://www.rubyforge.org/projects/lxl/

Install
-------

gem install lxl

Usage
-----

formulas = %{
((1+2)*(10-6))/2;
DATETIME("2004-11-22 11:11:00")=DATE(2004,11,22)+TIME(11,11,00);
IN(" is ", "this is a string");
LIST(1, "two", 3.0);
IN("b", LIST("a", "b", "c"));
AND(TRUE, NULL);
OR(TRUE, FALSE);
IF(1+1=2, "yes", "no");
}

# single formula
puts LXL.eval('5+5').inspect
# => 10

# multiple formulas separated by semi-colon
puts LXL.eval(formulas).inspect
# => [6, true, true, [1, "two", 3.0], true, false, true, "yes"]

See API docs for more information.

Regards,
Kevin

 

[Francis Hwang]

Kevin,

Pretty interesting. So why did you write this? Did you have particular
applications in mind? Would LXL be suitable for, say, exporting the
logic in an advanced Excel spreadsheet into something in Ruby?

Hi all,

I've just released LXL (Like Excel) 0.1.0, a mini-language that mimics
Microsoft Excel formulas. It can be easily extended with new constants
and
functions.

http://www.rubyforge.org/projects/lxl/

Install
-------

gem install lxl

Usage
-----

formulas = %{
((1+2)*(10-6))/2;
DATETIME("2004-11-22 11:11:00")=DATE(2004,11,22)+TIME(11,11,00);
IN(" is ", "this is a string");
LIST(1, "two", 3.0);
IN("b", LIST("a", "b", "c"));
AND(TRUE, NULL);
OR(TRUE, FALSE);
IF(1+1=2, "yes", "no");
}

# single formula
puts LXL.eval('5+5').inspect
# => 10

# multiple formulas separated by semi-colon
puts LXL.eval(formulas).inspect
# => [6, true, true, [1, "two", 3.0], true, false, true, "yes"]

See API docs for more information.

Regards,
Kevin

Francis Hwang
http://fhwang.net/

 

[Kevin Howe]

Pretty interesting. So why did you write this? Did you have particular

applications in mind? Would LXL be suitable for, say, exporting the
logic in an advanced Excel spreadsheet into something in Ruby?

Pretty interesting. So why did you write this? Did you have particular
applications in mind? Would LXL be suitable for, say, exporting the
logic in an advanced Excel spreadsheet into something in Ruby?

Hi Francis,

I developed it to allow customer-defined workflow rules for their web system
(an HR/Purchasing system). For instance, when a purchase order is submitted,
different things happen depending on:

- what office is was submitted from
- what employee submitted it
- what permissions the employee has
- the amount of the purchase order
- the type of the purchase (bi-weekly/monthly, office supplies/consumables)
- etc.

Rules are applied to these criteria to decide what happens next:

- are they over budget by more than $1000? Deny it.
- are they under budget by more than $500? Approve it.
- if submitted from head office, submit to a supervisor for approval.
- etc.

These rules were hard coded at first just to get things going, but these
factors are constanly in flux so they needed to be controlled at the
user-level. Ex: Last month those rules were fine, but this month one of the
offices needs to be able to purchase without a limit, another office needs
to be auto-approved for office supply purchases, but still limited for all
other purchases. A supervisor has gone on vacation for two weeks, and for
that period all approval requests should be sent to another supervisor
instead. The kinds of rule-changes are constantly happenning and need to be
put into effect immediately - they can't be calling an IT person for every
change. The solution was to allow them to program these rules themselves,
using a limited but user-friendly language. Excel formulas were the ideal
choice in this case because the entire company is already intimately
familiar with what they are and how they work. LXL provides the basic
language, the parsing and the basic functions (math operators, if/and/or
conditionals, date/time, etc). Then you extend it with your own custom
constant values and functions. You might have something like
IF(AND(OFFICE="HQ",REMAINING_BUDGET<500)) for instance.

That's the general idea

Regards,
Kevin

 

[Kevin Howe]

Released a small update LXL 0.1.1

Adds :SYMBOL recognition, and provides register_constant/register_function
methods.

Regards,
Kevin

 

[Francis Hwang]

Huh. So you've got another example of a domain-specific language, and
somebody else using Ruby might've chosen to use in-Ruby features to
allow employees to write business rules. Maybe something like:

add_rule {
notify_supervisor if @expense.office == 'HQ' and @budget.remaining <
500
}

Which, on some aesthetic level, might be better than representing it
with Excel's function syntax, but then maybe that's moot if your
coworkers are familiar with Excel. It never ceases to amaze me how
people can become power users of complex software like Excel or
FileMaker, but some of them get quite flummoxed when they're presented
with similar concepts expressed with different syntax. Maybe that's the
difference between a programmer and a power user.

Anyway, not meaning to criticize your decision; I probably would've
done the same thing in your situation. I just find it pretty
interesting. It's almost like it might've better if your coworkers had
been trained in Ruby to start, instead of Excel. Also makes me think
about how Ruby could take over the world if it had products like Excel
or FileMaker that used Ruby as their native scripting language ...

Hi Francis,

I developed it to allow customer-defined workflow rules for their web
system
(an HR/Purchasing system). For instance, when a purchase order is
submitted,
different things happen depending on:

- what office is was submitted from
- what employee submitted it
- what permissions the employee has
- the amount of the purchase order
- the type of the purchase (bi-weekly/monthly, office
supplies/consumables)
- etc.

Rules are applied to these criteria to decide what happens next:

- are they over budget by more than $1000? Deny it.
- are they under budget by more than $500? Approve it.
- if submitted from head office, submit to a supervisor for approval.
- etc.

These rules were hard coded at first just to get things going, but
these
factors are constanly in flux so they needed to be controlled at the
user-level. Ex: Last month those rules were fine, but this month one
of the
offices needs to be able to purchase without a limit, another office
needs
to be auto-approved for office supply purchases, but still limited for
all
other purchases. A supervisor has gone on vacation for two weeks, and
for
that period all approval requests should be sent to another supervisor
instead. The kinds of rule-changes are constantly happenning and need
to be
put into effect immediately - they can't be calling an IT person for
every
change. The solution was to allow them to program these rules
themselves,
using a limited but user-friendly language. Excel formulas were the
ideal
choice in this case because the entire company is already intimately
familiar with what they are and how they work. LXL provides the basic
language, the parsing and the basic functions (math operators,
if/and/or
conditionals, date/time, etc). Then you extend it with your own custom
constant values and functions. You might have something like
IF(AND(OFFICE="HQ",REMAINING_BUDGET<500)) for instance.

That's the general idea

Regards,
Kevin

Francis Hwang
http://fhwang.net/

 

[Kevin Howe]

Huh. So you've got another example of a domain-specific language, and

somebody else using Ruby might've chosen to use in-Ruby features to
allow employees to write business rules. Maybe something like:

add_rule {
notify_supervisor if @expense.office == 'HQ' and @budget.remaining <
500
}

A couple of issues with that (in my particular situation)

1) Formulas are modified via the web, and I don't put web input anywhere
near an eval statement if I can help it. The parser enforces the syntax
and catches anything unusual.

2) Even the example code above isn't simple enough.
The users are not programmers and aren't looking to be.
They wanted something that looked as much like plain english as possible,
something that reads more like a note than like a program.
To them using == for "equals" is strange, as are symbols like {} and @.

Which, on some aesthetic level, might be better than representing it
with Excel's function syntax, but then maybe that's moot if your
coworkers are familiar with Excel. It never ceases to amaze me how
people can become power users of complex software like Excel or
FileMaker, but some of them get quite flummoxed when they're presented
with similar concepts expressed with different syntax. Maybe that's the
difference between a programmer and a power user.
Anyway, not meaning to criticize your decision; I probably would've
done the same thing in your situation. I just find it pretty
interesting. It's almost like it might've better if your coworkers had
been trained in Ruby to start, instead of Excel. Also makes me think
about how Ruby could take over the world if it had products like Excel
or FileMaker that used Ruby as their native scripting language ...

If only

 

[Kevin Howe]

Version 0.2.0 has been released

http://rubyforge.org/projects/lxl/

CHANGES:

- Double quotes only used to define strings.

- Embedded quote escaping by doubling them up: ="This is a ""quoted""
string."

- Text/Formula split. Formulas start with =, anything else is seen as a
string

- :SYMBOL parsing removed. register_symbols added to enable symbols as
constants

- Case insensitive function and constant names

- Semi-Colons no longer parsed as a token (still used as statement
separator)

- General refactoring (code/doc cleanup)

Regards,
Kevin