Skip to content

Instantly share code, notes, and snippets.

@tobi-tobsen
Last active May 17, 2021 01:28
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save tobi-tobsen/8376239 to your computer and use it in GitHub Desktop.
Save tobi-tobsen/8376239 to your computer and use it in GitHub Desktop.
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