Python usefulness

I’ve again seen how useful and powerful Python can be. The other day I had to prepare an Excel spreadsheet (sadly) which among other things needed to contain links to the GeneCards database for each gene listed. There were more than 2900 genes listed, so adding links by hand would have been suicidal.

That’s when Python, through its Windows extensions, comes into play. First of all I created a module for COM objects using the makepy utility. Then it was a matter of importing the right modules and initialize the COM object:

[code lang=“python”] from win32com.client import Dispatch

xlsapp = Dispatch(“Excel.Application”) xlsapp.Workbooks.Add()[/code]

Then it was the turn to open the right file, select the right worksheet, and add the for loop to iterate through the cells. Since Excel stupidly changes MARCH3 to a date, I had to catch the relevant TypeError exception and add the link manually.

[code lang=“python”] url = “http://www.genecards.org/carddisp.pl?gene=" xlsapp.Workbooks.Open(“C:/myfile.xls”) worksheet = xlsapp.Worksheets(“Tabella1”) # Changes depending on locale

row = 2 # We skip the header

while worksheet.Cells(row,3).Value: address = "” try: address = ‘’.join((url,worksheet.Cells(row,3).Value)) except TypeError: address = ‘’.join((url,“MARCH3”)) worksheet.Hyperlinks.Add(worksheet.Cells(row,3),address row +=1 [/code]

worksheet.Cells takes the row and the column as parameters, and Value is its contents. Hyperlinks.Add is the method used to attach the links (without modifying the contents). After we’re done we clean up with

[code lang=“python”] xlsapp.Save() [/code]

Voila’! 2900 links added in just a minute.