Contents tagged with Office

  • Never, never, never do Office interop on the server

    No Comments

    I’ve been telling people this for years and years, and still I stepped right into the mudhole a month ago. I blame stress, but still I shouldn’t have. It’s so easy to just add the interop assemblies and off you go… until you try running your stuff on the 2008 r2 server, hosted somewhere else, with just enough user rights. And even though I managed to get the !”#!¤¤”% working after a while, I got mem-leaks and follow-up probs due to the workarounds I had to do to get the interop working in the first place.

    Then I sat down and relaxed for a minute and realized I need to do this the right way, which is the OpenXML way.

    The thing is, OpenXML is – compared to Office-interop - not easy, and the Microsoft OpenXML SDK 2.0 is just lipstick on top of the XML and some things which were so easy to do with the interop assemblies gives you nightmares to do with OpenXML. Try fill in formsfields in a protected word document for example. I ditched the SDK and ended up doing it directly against the XML directly instead.

    I also had to fill out a few Excel spreadsheets with data, which was somewhat easier to handle than the Word-story above. But I ran into problems here too, because I wanted to do such a simple thing as setting text wrap on a few cells! I don’t know, but I’m sure Microsoft could have done something better for us poor developers that need to create docs on the server! Are we supposed to spend 40% of the coding time to just create a few simple docs, just because we go the OpenXML way? There are a quite a few pages on MSDN with samples, as well as code snippets to download, but as soon as you need to do something outside the “demo-path” you’re banging your head. Maybe I’m just stupid and doesn’t get it, or maybe I’m getting grumpy, because OpenXML is not a developer-friendly framework! There, feels much better now…

    Now, for Excel spreadsheets there’s good hope because you got more than a few decent packages out there to help you out, and the best one I’ve found so far is called ClosedXML on Codeplex. It’s free and it did everything I needed to do right out of the box! It took me 5 minutes to create the 2 different spreadsheets I needed for my project, including cell formatting, text wrapping and so on. Cheers to MDeLeon for doing this!

  • The Touch Wall Demonstration by Bill Gates

    No Comments

    This video has been around for a few months now, but it's still cool. It's not one of Bill's better demos as he's kind of standing in the way of the screen all the time, but it still shows off the Touch Wall/Surface capabilities pretty well. I'm sure we'll see something like this in class rooms and larger meeting rooms in the future. It's easy to learn to use, but the presenter will have to learn how move on the podium as well as work with the presentation and give the talk. Interesting... what do you think?

     

     

     

    The video sequence is from the Microsoft CEO Summit 2008.

  • Excel Automation - Example with Named Ranges

    No Comments

    excel And now to something completely different... the Excel automation. Every now and then you need to open up an Excel file and do stuff with it. In this case we needed to create and read from named ranges in Excel, and it's not overy easy to get information about how to do that, so I thought I might as well post some sample code here.

    To get started with Excel automation, there is a good KB-article on MSDN: http://support.microsoft.com/kb/q302094

    Now, here's a console app in .NET 3.5 using VB.NET (I prefer to work with VB.NET with Office Interop and VSTO), which opens Excel, adds a new workbook, creates a (3,5) string array with values, fills the a range with these values, creates a named range and gets the values again to see that it worked properly. 

    Imports Microsoft.Office.Interop

Module Module1

    Sub Main()
        Dim application = New Excel.Application
        application.Visible = True
        Dim workbook = application.Workbooks.Add()
        Dim worksheet As Excel._Worksheet = workbook.ActiveSheet

        Dim saRet(3, 5) As String

        For iRow = 0 To 3
            For iCol = 0 To 5
                saRet(iRow, iCol) = iRow.ToString() + "|" + iCol.ToString()
            Next iCol
        Next iRow
    
        'get a range, alternative 1
        'Dim range As Excel.Range = worksheet.Range("A1:E3", Reflection.Missing.Value)

        'get a range, alternative 2
        Dim range As Excel.Range = worksheet.Range("A1", Reflection.Missing.Value)
        range = range.Resize(3, 5)

        'set value of range to that of the string array
        range.Value = saRet
        'name the range, explicitly (using dollar $ sign)
        workbook.Names.Add("NamedRange", "=$A$1:$E$3")
    
        'clear range
        range = Nothing
        'get the values of the named range
        range = worksheet.Range("NamedRange")
        Dim values(,) As Object = range.Value
        Console.WriteLine("rows:" & values.GetUpperBound(0))
        Console.WriteLine("cols:" & values.GetUpperBound(1))
        Console.WriteLine("value of row 2, column 4 (D4) = " & values(2, 4))

        Console.WriteLine("Press key to exit...")
        Console.Read()
        workbook.Close(False)
        application.Quit()
    End Sub

End Module

    Hope this helps someone.