VSTO & .NET & Excel

May 8, 2010

Excellent Learning Tool for Open XML SDK 2.0

Filed under: .NET & Excel, .NET Books, SQL Server, VSTO & Excel, VSTO Books — Dennis M Wallentin @ 7:18 pm

For some weeks ago Microsoft announced that they had launched the final version of Open XML SDK 2.0 for Microsoft Office. With this SDK we can create and manipulate Excel workbooks in code without involving Excel and also without having Excel installed.

What comes as a bonus with SDK 2.0 is the Open XML SDK 2.0 Productivity Tool for Microsoft Office. I was very pleased and surprised to discover that this is a multipurpose tool that allows us to a) generate reflected code, i.e to see the correct code to interact with the Open XML Documents file format, b) comparing source and target files code and b) validate code. We can also via the tool easily get access to the Open XML documentation.

The process to generate reflected code is very simple:
1. Create an Excel workbook and customize it as You want the final product to look like. Save it with the Open XML file format.
2. Open the Productivity Tool and open the created Excel workbook.
3. Navigate to the part of the created Excel workbook you want the code for.
4. Hit the button Reflect Code.
5. Done!

The following screen shot shows the tool in action:

However, we cannot control which language the generated reflected code is presented to us. The output is always in C# so if we want to see the generated VB.NET code we need to convert the generated C# code to VB.NET with a tool like Code Converter from Telerik.

The blog Brian Jones & Zeyad Rajabi: Office Solutions has published an excellent map over available online (MSFT’) resources for Open XML, which can be found at the following URL Zeyad Rajabi’s Open XML SDK Blog Map.

Personally I’m still in the initially phase and up to this date I have only done five smaller Excel jobs that involved manipulation of the Open XML file format. Have You done any work that involves it and if yes what are Your experiences?  

The more I work with it and the more I explore the above tool the more attractive it becomes. The real potential, at least to me, is that with Open XML SDK installed on a server we can generate Excel Reports in a smooth and structural way without having Excel installed. 

Enjoy it!

Kind regards,
Dennis

Advertisements

9 Comments »

  1. Wow. WOW. I think that’s about all I can say.

    The thought of using the Open XML SDK had intimidated me too much to even try. But this looks outstanding. Kind of like a macro-recorder for Open XML.

    What a great looking tool, and an outstanding write-up Dennis. Thanks!

    – Mike

    Comment by Mike Rosenblum — May 8, 2010 @ 10:03 pm

  2. Mike,

    When I opened the tool for the first time it took me some seconds before I realized its real power.

    The only negative I can say is that it does not generate reflected code in VB.NET

    But I’m very happy with it 🙂

    Kind regards,
    Dennis

    Comment by XL-Dennis — May 9, 2010 @ 10:34 am

  3. Very interesting,

    >>The more I work with it and the more I explore the above tool the more attractive it becomes. The real potential, at least to me, is that with Open XML SDK installed on a server we can generate Excel Reports in a smooth and structural way without having Excel installed.

    To me, at this point Excel becomes like a code library that your basically using to create/format (etc) some data. Doing it “long hand” with this SDK, seems like hard work, but if it’s not that hard, and quicker (?) then it could be useful.

    Dennis, do you know if this will create, for example shapes, so you could say draw a flow chart?

    Thanks
    Ross

    Comment by ross — May 10, 2010 @ 11:01 am

    • Ross,

      Yes, from what I have done shapes and charts are well supported. However, it can rather quick turn into a great number of code lines.

      What I have done is not to create workbooks from scratch. Instead I have been manipulating some part(s) of the workbooks, especially connection string and tables.

      Kind regards,
      Dennis

      Comment by Dennis Wallentin — May 10, 2010 @ 1:21 pm

    • > “To me, at this point Excel becomes like a code library that your basically using to create/format (etc) some data. Doing it “long hand” with this SDK, seems like hard work, but if it’s not that hard, and quicker (?) then it could be useful.”

      Hey Ross, I don’t know about speed, and I don’t see myself using this for everyday tasks where automation can be used… but use of the SDK is ideas when you want to be able to create or manipulate Excel documents on a server, or any other situation where you would not want or have Excel installed.

      Comment by Mike Rosenblum — May 10, 2010 @ 1:45 pm

  4. >>What I have done is not to create workbooks from scratch. Instead I have been manipulating some part(s) of the workbooks, especially connection string and tables.

    Yes this makes a lot of sense, small changes I mean…

    >>the SDK is ideas when you want to be able to create or manipulate Excel documents on a server, or any other situation where you would not want or have Excel installed.

    Yeah, I’m know what you mean, my take is that the later situation never is, I cant remember a case where I have needed an excel document, but not had a copy of excel to hand, so to speak.

    As for on the server. “We” had a interesting project where we needed to create, on demand “office” documents (.doc) for org structures, for a parts of a very large organisation. The issue with automating word on the serve was the speed, it was very slow to create the document in real time, and you would need multiple instances running, opening and closing etc. I think in this situation it could be worth going down the document creations with SDK route, if it was more speedy.

    Is there a fundermental issue with running office apps on a server? (not as a server!;-))

    Comment by ross — May 11, 2010 @ 11:47 am

  5. Ross,

    Running Office applications on a server has never been supported by MSFT. After all, the Office suite is a desktop product.

    That’s why MSFT also market the VSTO concept as well as the Open XML File Format as good options to use on a server. OK, for a great number of years there exist other solutions to generate Excel reports etc but in the MSFT world we now have supported server options.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — May 11, 2010 @ 11:58 am

    • > “Is there a fundermental issue with running office apps on a server?

      Yes, as Dennis said, VSTO has capabilities to deal with this, and the OpenXML creation and manipulation is another. But standard automation from the server has issues. For for more on this see Considerations for server-side Automation of Office (http://support.microsoft.com/kb/257757).

      – Mike

      Comment by Mike Rosenblum — May 11, 2010 @ 1:58 pm

      • thanks mike, I think I’ve come across that article before…it explains the limitations well.

        Comment by ross — May 14, 2010 @ 3:49 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: