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.

Geek Ruby Poetry

From dougpete's delicious feed comes the 99 Bottles of Beer site. The site features the code to produce the lyrics in 1264 different programming languages and variations.

One that caught my eye was Ruby.



One commenter wrote:

This may not be the shortest and most efficient version, but it's definitely the version that was the most fun to read.
Especially the parts "wall.call" if you need the next bottle, "step = method :buy" if you have no beer left and "beer.drink" as what to do in each stanza.

I would actually call it a geek poem.

For those of you who don't code Ruby: Learn Ruby, so you can enjoy reading this poem and writing your next scripts/applications/whatever.

There are "poems" written in Actionscript, Pascal, Turing, APL and lots more.

Poetry is a bit of an unintentional recurring theme in this blog. There certainly are times when programming feels elegant and creative and expressive. It certainly has plenty of rules that constrain - syntax, keywords, etc. Maybe it is like Haiku on a grand scale!

Does Microsoft have a sense of humour?

This video about the Office Developer Guy may be evidence that they do.



Where do I get a whiteboard for the shower?

Do Programmers have a sense of humour?

Why yes they do! Is it too arcane for anyone else to be able to determine? Maybe. Here are some funny puzzles from a great post and a long list of comments. Can you guess what the idiom, song or movie is?

// idiom 3

injury += insult;

// idiom 6

a = getThickness('blood');
b = getThickness('water');
assert(a > b);

// idiom 9

prey = 'worm';
time = getCurrentTime();
if (time >= 4 && time <= 8) {
   bird.catch(prey);
}

// idiom 19
return way.my || way.high;

// idiom from comments

if (!fire) {
   smoke = null;
}

// song 1
sleep = false;
gogo();

// song 3;
var tiger[i];

// song 5
var it = now || never;

// song 10
compare(null, u);

// song 14
person.name = 'jude';
person.greet();

// song 16
var s1 = 'tutti';
var s2 = s1.replace('tu', 'fru');
print(s1 + ' ' + s2);

// song 20
var country = new Array('UK', 'Italy', 'USA', 'Spain');
giveBirth(country[2]);

// movie 1
while (i < infinity) {
   tomorrow = dies;
   i++;
}

// movie 2
int numerator = 1;
int denominator = 0;
int mission = numerator / denominator;