The Open XML Format allows you to generate, manipulate, or pull data from Word 2007, Excel 2007, and PowerPoint 2007 files. If you are working with Microsoft-based solutions, you can generate, manipulate, and pull data from documents using the following tools and technologies:
Note: If you are working with Java or PHP/Linux/Unix/Solaris, you can also find plenty of resources here and here.
Anyway, today I want to focus on a couple best practices for getting started with coding for Open XML Format SDK 2.0.
For the last couple of months I have been working with some Open XML gurus: Zeyad Rajabi, Linda Lanqing Brownell, Eric White, Joel Krist, and Ken Getz. We are working together on a new set of Visual How-tos for the Open XML Format SDK 2.0 and a set of 50+ code snippets for Visual Studio 2008 to be released soon on MSDN.
Quite frequently I hear from most of them a couple best practices that I’d like to share with you today: If you plan to build a solution using the Open XML Format SDK 2.0…
The very first thing we recommend you to do before you start writing code for a document solution, is to create the template. Depending on what kind of solution you want to build, you can use Word 2007, Excel 2007, or PowerPoint 2007 to create a template.
Imagine that you own a software company and you sent all your developers and consultants to the great Office and SharePoint sessions at TechEd this year. The end of the fiscal year is coming soon and you need expense data from each conference attendee ASAP. Developers and consultants plan to spend this weekend on New Orleans, so you ask them to fill out a Web-based Expense Report form that sends data to your company’s accounting database. Your accountant loves Excel 2007 and she asked you to export all expenses per/employee to Excel spreadsheets so she can review details before approving. CSV export won’t do this time because your accountant wants it all pretty and styled. You want to figure out a way of programmatically generate the Expense Report spreadsheets by pulling data from your accounting database. You need styles and formatting as well. After evaluating lots of tools and technologies, you decide that you want to build this solution using the super cool Open XML Format SDK 2.0. Now what?
The very first thing we recommend you to do before you start writing code is create the template using Excel 2007. You may ask your accountant to design the Expense Report template using Excel 2007, or you can always download tons of great templates from Office Online. Here’s my sample expense report template:
Open the template using Excel 2007 and add some dummy data. Don’t forget to save the template as Excel Workbook file (.xlsx). Now that you have a template to start with, you can start coding the solution.
The Open XML Format SDK 2.0 download ships with a set of tools that facilitate the process of building document solutions using the SDK. One of this tools is the DocumentReflector. This tool has the ability to open an existing Open XML document and dynamically generate C# source code that uses the Open XML SDK 2.0 typesafe classes to create the document parts. Exploring the source code created by DocumentReflector is a great way to become familiar with the Open XML SDK 2.0 classes and the Office document formats. Zeyad refers to this tool as the “Macro Recorder on vitamins” and I can tell you it rocks! The main idea is that once you have a solution template, you open it using the DocumentReflector and you get a set of autogenerated classes that you can later modify with Visual Studio 2008.
To illustrate using the DocumentReflector to create the base code for the Expense Report solution, follow the next steps:
At this point the code generated by DocumentReflector can be copied and pasted into the Visual Studio solution for reuse and learning purposes.
For testing purposes, you can create a Console application in Visual Studio 2008. Here are some generic steps:
6. Select the code for all of the methods inside of the GeneratedClass class from the DocumentReflector code window then copy and paste the code as methods of the Program class in the Program.cs file in the Visual Studio project.
7. Change the copied CreatePackage method to be private static so it can be called from the static Main method.
The AddParts method creates all the parts that you need in the Expense Report spreadsheet.
The GenerateSharedStringTablePart1() method contains the code that you need to create values for the table on the Expense Report. You can modify this code to iterate through the accounting database. You can pull expense report data by employee and replace the dummy data you entered.
7. Modify the Main method and add a call to the CreatePackage method.
Opening the workbook with Excel will display a workbook that looks just like the ExpenseReport.xlsx workbook created previously.
If you are looking for more end-to-end solutions using the Open XML Format SDK 2.0, you must see this articles:
Also, as mentioned before, Brian’s/Zeyad’s blog and Eric’s blog are always a great resource for Open XML code samples.
Coming soon the set of Open XML Format SDK 2.0 VHTs and code snippets for Visual Studio 2008!
PingBack from http://microsoft-sharepoint.simplynetdev.com/open-xml-format-sdk-20-getting-started-best-practices/
This is an excellent set of resources to get started with Open XML format.
So I hate to ask - any news of when an updated CTP/Beta/RC/Release of the OOXML SDK 2.0 is coming?!
Thank you for submitting this cool story - Trackback from DotNetShoutout
Top News Stories Microsoft Readies SharePoint Server 2010 Beta (PC World) Microsoft will launch an invitation
Does this provide the option to output the document in older formats, even though it is built in the newer one?
Dennis! Thanks so much for the kind comments.
Sean, more CTPs coming for sure in the next few months. I don't have confirmation of release dates, but I suggest that you follow Biran/Zeyad's blog.This is the best place for updates related to API CTP releases.
Guy, by installing the Compatibility Pack in addition to Microsoft Office 2000, Office XP, or Office 2003, you will be able to open, edit, and save files using the file formats new to Word, Excel, and PowerPoint 2007. The Compatibility Pack can also be used in conjunction with the Microsoft Office Word Viewer 2003, Excel Viewer 2003, and PowerPoint Viewer 2003 to view files saved in these new formats. Here's a link to the download:
What i mean is - can i output older formats "from the code".
we have clients on both office 2003 and 2007. We cannot move development to the Open SDK if it can only output in the new format. As clients will not undertake the rollout required to implement the compatability pack.
The SDK only outputs Open XML Format files, not binary formats. If you need to output 2003 binary formats and end-users of your solution can't deploy the compat pack, you can alternatively use the object model through automation. This is a good alternative if your application is running client-side. If your solution is running server-side, OM through interop is not supported.
-not sure if my post made the cut so trying again-
Excellent list of resources.
I have one more:
The New Excel 2007 File Format:
- How to locate worksheet data manually
- Changing properties of controls on worksheets
- Editing elements in an OpenXML file using VBA
- Adding RibbonX code to an Office OpenXML file using VBA
Encore cette fin de semaine, voici les quelques posts ou astuces que j’ai pu rencontrer sur la toile
Why doesn't DocumentReflector output VB code?
Introducing such a topic you'd like to congratulate you've let us know. Have good work
Great article. Thanks.
Awaiting to the VB version of the DocumentReflector...
Please be kind and do not forget the now out of date learners...
(En attendant avec impatience la version VB du documentReflector. Je soutiens entièrement la remarque de DD)