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:
from win32com.client import Dispatch
xlsapp = Dispatch(“Excel.Application”)
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.
url = “http://www.genecards.org/carddisp.pl?gene="
worksheet = xlsapp.Worksheets(“Tabella1”) # Changes depending on locale
row = 2 # We skip the header
address = "”
address = ‘’.join((url,worksheet.Cells(row,3).Value))
address = ‘’.join((url,“MARCH3”))
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
Voila’! 2900 links added in just a minute.