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

Discussion in 'Ruby' started by Kevin Howe, Feb 3, 2005.

  1. Kevin Howe

    Kevin Howe Guest

    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
    Kevin Howe, Feb 3, 2005
    #1
    1. Advertising

  2. 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?

    On Feb 3, 2005, at 12:50 PM, Kevin Howe wrote:

    > 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/
    Francis Hwang, Feb 4, 2005
    #2
    1. Advertising

  3. Kevin Howe

    Kevin Howe Guest

    > 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, Feb 4, 2005
    #3
  4. Kevin Howe

    Kevin Howe Guest

    Re: [ANN] LXL (Like Excel) 0.1.1

    Released a small update LXL 0.1.1

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

    Regards,
    Kevin
    Kevin Howe, Feb 4, 2005
    #4
  5. 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 ...

    On Feb 3, 2005, at 9:55 PM, Kevin Howe wrote:

    >> 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
    >
    >
    >
    >


    Francis Hwang
    http://fhwang.net/
    Francis Hwang, Feb 5, 2005
    #5
  6. Kevin Howe

    Kevin Howe Guest

    > 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, Feb 6, 2005
    #6
  7. Kevin Howe

    Kevin Howe Guest

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

    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
    Kevin Howe, Feb 6, 2005
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. warpcat
    Replies:
    9
    Views:
    2,953
    Russell Warren
    Jun 22, 2006
  2. Kevin Howe

    [ANN] LXL (Like Excel) 0.3.0

    Kevin Howe, Feb 13, 2005, in forum: Ruby
    Replies:
    1
    Views:
    114
    Kevin Howe
    Feb 13, 2005
  3.  James
    Replies:
    0
    Views:
    160
    James
    May 19, 2011
  4. Will James
    Replies:
    22
    Views:
    606
    Will James
    May 26, 2011
  5. Silveira Neto

    Math formulas easy editor like tinyMCE

    Silveira Neto, Aug 25, 2007, in forum: Javascript
    Replies:
    0
    Views:
    146
    Silveira Neto
    Aug 25, 2007
Loading...

Share This Page