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!
Hi Great Developers,
Is it possible for any C++ developers to use OpenXML and Excel to generate SpreadSheet in C++ language. I was very disappointed to see not a single code snippet in C++ to create an Excel Spreadsheet. Can you please provide which compiles and executes and generates Excel spreadsheet?
Awaiting your reply.
When trying to run the tools i get an exception:
System.IO.FileLoadException: Could not load file or assembly 'DocumentFormat.OpenXml, Version=2.0.3930.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
File name: 'DocumentFormat.OpenXml, Version=2.0.3930.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
however the assembly that ships in the lib folder is of version 2.0.4330.0. Looks to me that tools that shipped with August CTP were not recompiled with the assembly version that shipped in August CTP
I have created an Excel 2007 spreadsheet using DocumentFormat.OpenXml.Spreadsheet. This file is supposed to be used as an attachment to an email message. However, I get errors when opening it stating that this file cannot be opened by using Microsoft Excel. "Do you want to search... online..?" I want to save this file in Excel 2003 format so I won't get that message. What do I do? I don't want users having trouble reading the attached Excel doc.