Microsoft Excel and Twitter via Smart Tags in a VS 2008 Solution
I recent wrote (“Twitter from Excel 2007”) about how Chandoo (Plus JP) caught my attention with their quirky take on updating Twitter from Excel. In there, I said I would lay down an expanded, more useful version. In so doing, I believe I have also provided a raison d’être for the thing in the first place!
Here’s the shakedown (keeping in mind that These postings are provided "AS IS" with no warranties, and confer no rights.” :
I wrote the VS 2008 Add-in for Excel 2007 with a smart tag. The smart tag recognizes phrases or keywords that you have typed into Excel and then, with one click, let’s you post the cell (containing the keyword[s]) or List to Twitter.
You can change what/how the smart tag recognizes (for example using regular expressions), and I have personally written much on this topic already on MSDN (see list of resources at the end of this post). You can also change how the smart tag action handler works of which I have also written a fair amount (see resources again).
Gilding the lily even further, when I post a dialog for your Twitter credentials I encrypt the username and password and store the hashes statefully so that the next time you do the one-click Twitter post you don’t need to re-enter your credentials. I just decrypt them and use the stored ones. This is not mega-industrial strength security practice, but it sure beats putting passwords in the code or storing them as plain-text. At the same, it also provides a superior user experience, because the user does not have to repeatedly provide credentials which would be, at the very least, annoying.
Making this work inside of Word is a snap—very, very trivial. Again, look at the documentation on creating smart tags, and you’ll see how easy that is.
In the near future, I am going to figure out how to do this for Facebook.
Obviously, you’ll need a Twitter account to test this. So, sit back with your bag of M&M’s and strap in for the technical detail!
1) For the password encryption/decryption I used the Microsoft Patterns & Practices Application Blocks 4.1. I strongly recommend working through one of the samples before attempting to go it alone. I’ve done a lot with these blocks over the years, and they are never as ‘plug-and-go’ as one hopes. Anyway, in those blocks is a Security.Cryptographer building block. I use it, and I show you everything I do in this post.
2) Create a VS 2008 Excel 2007 Add-in using VB.NET
3) In the new project, add a form called “TwitterLogin”. Configure it to look like this:
4) Your control names should be the following:
| Control Type | Name/Caption |
| Label | Username: |
| Label | Password: |
| TextBox | txtUserName |
| TextBox | txtPassword |
| Button | cmdLogin, caption is “Login” |
| TwitterLogin (form) | Twitter Login |
5) Add references so that they look like this:
6) Add a new class called HashHelper.
7) Add a new app.config file
8) Your overall project contents should look like this (you see a key file in here also, and you’ll be adding it in another step or two):
9) Configure the app.config file to use a new symmetric provider for the encryption/description by right-clicking on app.config in VS. Open the file in the configuration editor by selecting Edit Enterprise Library Configuration on the context menu.
10) In the editor, right click on the Application (the path with your app.config file name) and select New | Cryptography Application Block.
11) Select Symmetric Providers in the new block just added and choose New. Then, select Symmetric Algorithm Provider.
12) In the resulting dialog, select RijndaelManaged as the type, and press the Generate button to automatically generate a key, click OK. You’ll want the name/path of the key to be in the directory of your application (could be anywhere but it’s easier to manager this way) as shown in the previous solution explorer image.
13) Name your new provider “symmProvider”. Verify that it look like this one:
14) Close the app.config file and save your changes.
15) Return to your TwitterLogin form and double-click the button to bring up the cmdLogin_click event handler. Add this to that handler:
Dim UserNameEncrypt As String
Dim PasswordEncrypt As String
UserNameEncrypt = HashHelper.EncryptText(txtUsername.Text)
PasswordEncrypt = HashHelper.EncryptText(txtPassword.Text)
My.Settings.Username = UserNameEncrypt
My.Settings.Password = PasswordEncrypt
16) Make sure you have included the following Imports statements at the top of the class for that form:
Imports System.Security.Cryptography
Imports Microsoft.Practices.EnterpriseLibrary.Security.Cryptography
17) Close and save the form.
18) Open the HashHelper.vb file
19) Add the following definition code to that file. Your login form and add-in code will be calling into this code to encrypt and decrypt the password as well as store their hashes.
Imports Microsoft.Practices.EnterpriseLibrary.Security.Cryptography
Imports System.Text
Friend Class HashHelper
Private Sub New()
End Sub
' Hash provider name must match app.config
Private Const Provider As String = "symmProvider"
Public Shared Function EncryptText(ByVal plainText As String) As String
Dim returnText As String
returnText = Cryptographer.EncryptSymmetric(Provider, plainText)
Return returnText
End Function
Public Shared Function DecryptText(ByVal HashString As String) As String
Dim DecryptedString As String
DecryptedString = Cryptographer.DecryptSymmetric(Provider, HashString)
Return DecryptedString
End Function
End Class
20) Open your ThisAddIn.vb code file and add these Imports statements:
Imports Microsoft.Office.Tools.Excel
Imports System.Windows.Forms
Imports Microsoft.Office.Interop.SmartTag
Imports System.Net
Imports System.IO
Imports Microsoft.Practices.EnterpriseLibrary.Security.Cryptography
21) Add the following global variable to your ThisAddIn Class:
Private TwitterTag As TwitterSmartTag
22) Add this to your ThisAddIn_Startup method:
TwitterTag = New TwitterSmartTag()
Me.VstoSmartTags.Add(TwitterTag)
23) Add a new class called TwitterSmartTag and add code so it looks like this:
Public Class TwitterSmartTag
Inherits SmartTag
' Declare Actions for this SmartTag
WithEvents Action1 As New Action("")
WithEvents Action2 As New Action("whatever")
Public Sub New()
MyBase.New("https://painjunkie.spaces.live.com/jrd#TwitterTag", _
"Excel-to-Twitter Smart Tag")
Me.Terms.AddRange(New String() {"Soulfly", "Motorhead", "Judas Priest", "Tool", "Pantera"})
Actions = New Action() {Action1}
End Sub
Protected Overrides Sub Recognize(ByVal text As String, _
ByVal site As ISmartTagRecognizerSite, _
ByVal tokenList As ISmartTagTokenList)
' Determine whether each smart tag term exists in
' the document text.
Dim Term As String
For Each Term In Me.Terms
' Search the cell text for the first instance of
' the current smart tag term.
Dim index As Integer = Me.CellText.IndexOf(Term, 0)
If (index >= 0) Then
' Create a smart tag token and a property bag for the
' recognized term.
Dim propertyBag As ISmartTagProperties = _
site.GetNewPropertyBag()
' Write a new property value.
Dim key As String = "Key1"
propertyBag.Write(key, DateTime.Now)
' Attach the smart tag to the term in the document
Me.PersistTag(propertyBag)
' This implementation only finds the first instance
' of a smart tag term in the cell.
Exit For
End If
Next
End Sub
Private Sub Action1_BeforeCaptionShow(ByVal sender As Object, ByVal e As Microsoft.Office.Tools.Excel.ActionEventArgs) Handles Action1.BeforeCaptionShow
Dim ClickedAction As Action = sender
If e.Range.ListObject Is Nothing Then
sender.Caption = "Tweet this cell"
Else
sender.Caption = "Tweet this list"
End If
End Sub
' This action displays the property value for the term.
Private Sub Action1_Click(ByVal sender As Object, _
ByVal e As ActionEventArgs) Handles Action1.Click
Dim propertyBag As ISmartTagProperties = e.Properties
Dim key As String = "Key1"
Dim Tweeter As New TweetThis()
Try
If My.Settings.Username.Length = 0 Or My.Settings.Password.Length = 0 Then
Dim LoginForm As New TwitterLogin()
LoginForm.ShowDialog()
End If
If Tweeter.TweetIt(e.Range.Text).Length > 0 Then
Else
MessageBox.Show("Twitter successfully updated!", "Twitter Status", MessageBoxButtons.OK)
Exit Try
End If
Throw New Exception
Catch ex As Exception
MessageBox.Show(ex.Message, "Twitter Update Error", MessageBoxButtons.OK)
End Try
End Sub
End Class
24) Lastly, add this class, TweetThis, which actually does the work of posting to your Twitter account:
Public Class TweetThis
Public Function TweetIt(ByVal msg As String) As String
Dim username, password As String
Try
username = HashHelper.DecryptText(My.Settings.Username)
password = HashHelper.DecryptText(My.Settings.Password)
System.Net.ServicePointManager.Expect100Continue = False
Dim bytes() As Byte = System.Text.Encoding.ASCII.GetBytes("status=" & msg)
Dim request As HttpWebRequest = CType(WebRequest.Create("https://twitter.com/statuses/update.xml"), HttpWebRequest)
request.Credentials = New System.Net.NetworkCredential(username, password)
request.Method = "POST"
request.ContentType = "application/x-www-form-urlencoded"
request.ContentLength = bytes.Length
Dim reqStream As Stream = request.GetRequestStream()
reqStream.Write(bytes, 0, bytes.Length)
reqStream.Close()
Dim response As HttpWebResponse = request.GetResponse
Dim reader As New System.IO.StreamReader(response.GetResponseStream)
Dim retValue As String = reader.ReadToEnd()
reader.Close()
Return ""
Catch ex As Exception
Return "error"
End Try
End Function
End Class
25) That about does it. Build the solution and make sure things look good there. Then, run it and see what happens.
BTW: Follow me:
https://painjunkie.spaces.live.com/
https://twitter.com/johnrdurant
Smart Tags Development Resources:
Office Developer Center (just search for ‘Smart Tags’ there and you’ll see pretty good links)
Technorati Tags: Office 2007,XML,Office Development,OBA,OOXML,Content Controls,Microsoft Office System,Microsoft Word,VBA,Excel,Twitter