Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
IronPython script which retrieves selected cells from an opened excel sheet and figures out the number of occurences for each of the letters used
import clr
# Not sure if the full qualified assembly name is needed - works for me if I omit it:
#clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')
clr.AddReference("Microsoft.Office.Interop.Excel")
from Microsoft.Office.Interop import Excel
from System.Runtime.InteropServices import Marshal
from collections import Counter
# The following IronPython script finds out how many alphabet-stickers my future wife needs for the wedding place cards
# It expects an excel instance to be opened and that the names of the guests are selected.
# It then concatinates all names and uses 'Counter' to find out how many instance of each letter occurs in the selected names
# Tested with Windows8.1, Excel 2013, IronPython 2.7.4 (2.7.0.40) on .NET 4.0.30319.34003 (32-bit)
# Get the active Excel Application to interact with it and retrieve the selected cells.
excel = Marshal.GetActiveObject("Excel.Application")
excel.DisplayAlerts = True
selection = excel.Selection
# Could also browse through the open workbook and filter rows without a selection
#excel = Excel.ApplicationClass()
#excel.Visible = True
#workbook = excel.ActiveWorkbook
#worksheet = workbook.ActiveSheet
# TODO: I guess this is not very pythonic...
selectedWords = ''
for row in selection.Rows:
for column in row.Columns:
if column.Value2 is not None:
selectedWords += column.Value2
result = Counter(selectedWords)
totalCount = 0
for key in sorted(result.iterkeys()):
letterCount = result[key]
totalCount += letterCount
print "%s: %s" % (key, letterCount)
print "Total: %i" % totalCount
raw_input()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment