Using IronSpread and Regular Expressions in Excel

I get a lot of traffic on this blog related to Regular Expressions in Excel, much of it from http://stackoverflow.com/questions/4556910/how-do-i-get-regex-support-in-excel-via-a-function-or-custom-function.  There are comments there that indicate that it does not work with Office 2010.

I stumbled upon www.ironspread.com, which allows you to use Python to "program" Excel 2007 and 2010 sheets.  Python has excellent support for Regular Expressions.  It looks like there are other libraries for Python that can be used with Excel and OpenOffice; PyUNO for one.

I quickly cooked up the following example using a regular expression for valid email addresses found at http://www.regular-expressions.info/email.html.

The Excel sheet looks like:

The Python program looks like:

 # see http://www.regular-expressions.info/email.html for discussion about  
 # regular expressions to check email addresses  
 import re  
 emailRE = r"[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+(?:[A-Z]{2}|com|org|net|edu|gov|mil|biz|info|mobi|name|aero|asia|jobs|museum)\b"  
 for i in range(1, 100):  
   currCellValue = Cell(i,1).value  
   if currCellValue == 0:  
     break  
   try:  
     m = re.match(emailRE, currCellValue).group(0)  
     if m == currCellValue:  
       # matches full address  
       Cell(i,2).value = ""  
       Cell(i,3).value = ""  
     else:  
       # matches part of address  
       Cell(i,2).value = "Suggestion"  
       Cell(i,3).value = m  
   except:  
     # no match  
     Cell(i,2).value = "Invalid"  
     Cell(i,3).value = ""  

(formatted with http://codeformatter.blogspot.ca/)

and once finished, columns B and C are filled in

I am a novice programmer in this environment, so I hope there are no glaring newbie mistakes and that the example is illustrative.

IronSpread installs a Python editor, IDLE, and a command shell which can be used to test your work.  I still love Sublime Text 2 as an editor, so give a command like:
execfile("c:/Users/Ross/Documents/EmailAddresses.py")
in the shell to run the program saved by Sublime.  Any print statements will output to the shell.  I have linked to the Excel Sheet and Python program above if you want to try them out.

Spectacular Sears Successes

I don't usually feature my family here (with rare exceptions), but will now.  Jonathan was one of two actors in Salt-Water Moon at the Sears Provincial Showcase and came away with an award of excellence for acting together with Sam.  The play, by the late David French, is a lovely story and the two actors, together with their stage manager, director, costume and set designers rendered it beautifully.

Here is a piece from the radio where you can get a sense of their Newfoundland accents and stage chemistry.

And here they are with their award.

Two cewebrities at one session

I am at the OAME 2012 Conference in Kingston, showing folks the new CLIPS wrapper, calculator and Multiplying Fractions Activities in development mode.  I did get a chance to see Dan Meyer's presentation on Thursday "Why Kids Hate Word Problems".  It was a sensible, craftful, and powerful appeal.

A delightful part was that by coincidence I sat next to another internet sensation, Mr. O.  Faithful readers of this blog (a bit of useful fiction) will remember my earlier post about this teacher at my alma mater and his 15 minutes of internet fame.  That post had some of the details wrong, apparently.  Mr. O was flown to Portugal for the making of a movie about the perfect circle and spoke at Art Basel, in Switzerland, about his experience.  He even spoke to producers at the Letterman show. Apparently, the video was shot in June 2006 by Glebe's webmaster, who you can hear in the background, and posted to their site where it received about as much interest as this blog.  In January, some kid in North Dakota posted it to YouTube, College Humor and some other sites.  It went viral and was YouTube's featured video for two weeks.  Mr. O. did not until that time even know what YouTube was.  He does now.

In case you require documentation, I have embedded the footage from the World Championship.

You say minus I say ptooey

I have started a vigorous comment thread in response to Keith Devlin's Huffington Post post.  Maybe you would like to weigh in...

$(-3)^\frac{-3}{2}$

Latex Equation created with the help of codecogs.com

Apple bites

I have to admit that after watching Apple's iBooks Author video, I was trying to figure out how to get an OS 10.7 computer to download the authoring application and an iPad to test the results and imagining authoring all kinds of marvelous content but then I got to thinking...

• you mean I can't view my publication on any computer, even a Mac?
• you mean that if I want to sell my publication, it has to be through Apple's store?
• you mean that Apple wants to be the sole provider of textbook delivery?

Now I am thinking that it is almost a moral imperative to take a pass.  

Having an easy application to create digital materials is a wonderful idea.  Forcing those materials to live in a proprietary format, available for sale in an exclusive Apple store is not.  Isn't that what Steve Jobs said he didn't like about Flash?

I guess I agree with dougpete (with thanks for the links).

Trying out Trello

A real nugget came through by Google Reader from Joel on Software - the guy behind the Joel Test of how good a software team is (mathclips.ca gets at most 3/12 on it, BTW).  His company, Fog Creek Software, produces Trello, an online collaboration tool, that is currently free.

The CLIPS team has been trying it out for a couple of weeks and I think it is really promising.  The notification history provides a progress log.  You can create multiple checklists and easily monitor progress and you can send notifications to specific members of the team.  You can see how the Trello team uses it, by going to their development board, likely after you have signed up.  Best of all, it feels like the value of using it exceeds the effort of maintaining it.

I expect that my massive following (100 readers according to Google Reader) will flood their servers to the breaking point moments after I press the Publish button since no one I have mentioned it to has heard about it.  Here is their announcement video which is a good introduction:

AS2 Farming out compile work to a swf

Thanks to Crystal's vivid imagination, the CLIPS team has created a Hops on Lines Tool that consists of 27 .as files averaging 25K or so.  When compiled, the .swf is about 700K.  When an activity uses the Hops Tool as well as other classes, the compile can be very slow or can max out available memory and fail.  We decided to farm out the job of compiling to another .swf and load it into any activities that require it.

Unfortunately, this means that the activity.swf cannot be run standalone.  Users will either have to be online, so that the Hops .swf can be found at mathclips.ca, or they will have to save the Hops .swf to a predictable location on their hard drive.

Originally, an instance of the Hops tool was created using a call to a static create method as described in a previous post.

     HopsOnLines.create(target, "instanceName", initObj);

To farm out this work and keep all the current code backwards compatible, we rename HopsOnLines to be OriginalHopsOnLines and make the new HopsOnLines.as contain:

class HopsOnLines extends MovieClip {

 public static function init(myCreatorReference:MovieClip){
  _root.theToolCreator = myCreatorReference;
 }

 public static function create(container:MovieClip, instanceName:String, initObj:Object, depth) {
  return _root.theToolCreator.create(container, instanceName, initObj, depth);
 }
}

In order to use this new version of the class, an initialization step must be performed to tell the class where the movieclip containing the compiled swf is.
The .fla used to create the .swf contains:

System.security.allowDomain("*");
import HopsOnLinesOld;
stop();

function create(target:MovieClip, toolName:String, initObject:Object, depth:Number){
 return HopsOnLinesOld.create(target, toolName, initObject, depth);
}

In this way, the .fla compiles the HopsOnLinesOld, with all its imported classes and exposes a create method that simply shuttles the job to the legacy creator.  This is one of the few times when the .fla is many times smaller than the compiled .swf.

In order to use this compiled swf. An activity must load it into a movieclip. When it is completed, call init to tell it where that movieclip is, and then any HopsOnLines.create statements will work without any modifications. Here is some sample code from an activity .fla:

import HopsOnLines.as;
System.security.allowDomain("*");

var testingLoader:MovieClipLoader = new MovieClipLoader();
var testingListener:Object = new Object();

testingListener._parent = this;
testingLoader.addListener(testingListener);

this.createEmptyMovieClip("testingContainer", this.getNextHighestDepth());
this.createEmptyMovieClip("testingLooper", this.getNextHighestDepth());

testingLoader.loadClip("http://www.myDomain.ca/HopsOnLinesCreator.swf", this.testingContainer);

testingListener.onLoadInit = function(which){
 trace("onLoadInit with "+which);
 this._parent.testingContainer.init(this._parent.testingContainer);
 this._parent.testingContainer.create(this._parent, "myHops_mc", {_x:50, _y:50});
 if (this._parent.myHops_mc == undefined){
  trace("onLoadInit has an error not picked up by onLoadError");
 }
}

There are a couple of things that I still wonder about. I get incompatible context errors related to security and sometimes they seem to make it not work. Also, if the file does not exist, onLoadInit seems to be called rather that onLoadError.  Also, I seemed to have the make the reference in the new .as file to _root - I had tried a static property and a _global but that did not work as well, I am not sure why.