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:
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("http://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("http://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:
http://painjunkie.spaces.live.com/
http://twitter.com/johnrdurant
Smart Tags Development Resources:
Content I have written
Office Developer Center (just search for ‘Smart Tags’ there and you’ll see pretty good links)