Freigeben über

Microsoft Excel and Twitter via Smart Tags in a VS 2008 Solution

  • Artikel

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:

Content I have written

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

Comments

  • Anonymous
    March 16, 2009
    Thank you for submitting this cool story - Trackback from DotNetShoutout

  • Anonymous
    March 16, 2009
    Nice work John, thanks for sharing. As soon as I set up Excel 2007 on my spare computer I'll try this out.

  • Anonymous
    March 18, 2009
    I’ve been experimenting with Twitter for the last couple of months and I am quite addicted to it now.

  • Anonymous
    March 18, 2009
    My colleague, John Durant, wrote an interesting little blog on how to integrate the Office client and

  • Anonymous
    April 05, 2009
    Great post as usual, however I find myself immediately scrolling to the bottom of your posts looking for the Rock Thought of the Day before reading the rest of the content.  Saddened that there was no RTOTD in this post.   Interesting use of Enterprise Library Cryptography block here as well, thanks for showing how to use it!