Erika Ehrli - Adventures with Office Products & Technologies
MSDN & TechNet: Releasing Office, SharePoint, Exchange & Lync Centers and content for developers and IT professionals.

  • Erika Ehrli - Adventures with Office Products & Technologies

    How to: Magic with SharePoint 2003, uploading files using a Web service


    Some time ago I blogged about my intentions of uploading files to a SharePoint Document Library site from a local folder using some kind of Web service. After doing some research and some tests, I found a very easy way to do that and now I want to share with you the approach I followed since you might find it useful as well.


    To build this solution, you need to install Office SharePoint Portal Server 2003 and follow the next steps:

    • Create a Document Library
    • Grant access to the users that will upload files to the Document Library:

    To install the Web service:

    1. Download ODC_WritingCustomWebServicesSampleSPPT.EXE.
    2. Extract the download contents to your hard drive and Run build.bat.
      • Note: This will install the Web service on the _vti_bin virtual directory inside the Default Web Site.
    3. Open the IIS Management Console (INETMGR) and navigate to the the _vti_bin virtual directory (inside the Default Web Site).
    4. Find SPFiles.asmx, right click, and Browse.
    5. Navigate to http://localhost/_vti_bin/SPFiles.asmx to validate if you have installed successfully the Web Service.

    To consume the Web Service:

    1. From your managed application, add a reference to the http://localhost/_vti_bin/SPFiles.asmx Web Service.
    2. Call the Web service DocumentLoader
    3. Create a helper class to upload files. You can use the helper class I created.
      using System;

      /// <summary>
      /// A sample SharePointHelper class to upload files
      /// </summary>
      public class SharePointUploadHelper {
      private string _sharepointDocumentLibrary;

      SharePointUploadHelper() {

      public string UploadDocumentsToSharePoint(string fileName) {

            DocumentLoader.SPFiles svcDocLoader 
      = new DocumentLoader.SPFiles();
      svcDocLoader.PreAuthenticate = true;
      svcDocLoader.Credentials CredentialCache.DefaultCredentials;

      strPath fileName;
      strFile strPath.Substring(strPath.LastIndexOf("\\") + 1);
      strDestination _sharepointDocumentLibrary;

      FileStream fStream = new FileStream(strPath, System.IO.FileMode.Open);
      [] binFile = new byte[(int)fStream.Length];
      fStream.Read(binFile, 0, (int)fStream.Length);
      result svcDocLoader.UploadDocument(strFile, binFile, strDestination);

    4. Create a Web Form, Win Form, or console application that will require a user to upload files.
    5. Call the UploadDocumentsToSharePoint method of the SharePointUploadHelper class, for example:
      protected void btnLoadFile_Click(object sender, EventArgs e)
              SharePointUploadHelper fh 
      = new SharePointUploadHelper();
      serverTempFilePath Server.MapPath(@"/yourApplication");
      lblUploadResults.Text fh.UploadDocumentsToSharePoint(serverTempFilePath);
    6. Open the configuration file (i.e. Web.config) and turn on impersonation.
      <identity impersonate="true" />
    7. Add a configuration key that points to the SharePoint Document Library where you will upload files.
      <add key="SharePointDocLibrary" value="http://myServerName/myDocumentLibrary"/>
    8. Build your application
    9. Run and test the application, and there, a great web service.

    I can tell you it works, just keep in mind the download is a code sample.



  • Erika Ehrli - Adventures with Office Products & Technologies

    How to: Uploading a File to a SharePoint Library Site from a Local Folder


    I am currently developing an application that will help my team (Office Developer Documents) to manage all the metadata associated with the publishing process in MSDN. The name of the tool is Rawhide and it’s a Web-based application. I am really excited of developing this tool because I am working with Whidbey, ASP.NET 2.0, SQL Server, Windows Sharepoint Services, and a cool set Office development features. Soon I will start adding Office “12” enhancements such as Excel Services and Windows Sharepoint Services “v3”. I can’t wait!

    I am learning a lot from my peers about how interesting it is to publish content in MSDN and I hope this tool will improve our working experience and become a great source of code snippets that I can share with the community.

    Today I was reading the newsgroups and I was looking at lots of posts where developers are looking for code that will help to upload programmatically files to a SharePoint library. I was surprised to see that there are lots of developers struggling with this issue. I am too :). One of Rawhide’s features is to submit our articles, code samples, and art to a SharePoint Library.

    I found two great articles that are helping me out to accomplish my mission. I know there are many ways of doing that, but this is the recommended one by Microsoft and I love it because it has just what I needed and it has code in C# and VB.NET:

    Uploading a File to a SharePoint Site from a Local Folder

    I also found this page that has all the information I need to work with the SPFileClass.

    And finally, I found this interesting blog entry at Bill Simser’s blog where he talks about the Syncronization of Office Document Properties with WSS Document Libraries. I haven't tested this yet, but apparently, if you create the following properties in a Sharepoint library or list:

    Hyperlink base

    "The MS/Office documents that are upload inherit such corresponding custom MS/Office properties : "


    As soon as I complete my "upload files to SharePoint" features in Rawhide, I will keep you posted with the outcome.

  • Erika Ehrli - Adventures with Office Products & Technologies

    Extracting Microsoft Office Application Properties without automation


    Every file created by a Microsoft Office application supports a set of built-in document properties. In addition, you can add your own custom properties to an Office document either manually or through code. You can use document properties to create, maintain, and track information about an Office document such as when it was created, who the author is, where it is stored, and so on. To get or set the properties you can use automation to extract the Microsoft Office application properties.

    Take a look at the following links for samples:;EN-US;Q303296&

    But what happens if you are working with a Web-based application and you want to avoid the use of automation in a Web server…

    I found a nice workaround to extract Office document properties without using automation. You can use the Dsofile, an in-process ActiveX component that allows you to read and to edit the OLE document properties that are associated with Microsoft Office files, such as the following:
    • Microsoft Excel workbooks
    • Microsoft PowerPoint presentations
    • Microsoft Word documents
    • Microsoft Project projects
    • Microsoft Visio drawings
    • Other files without those Office products installed

    If you are working with a managed application follow the next steps:

    1. Download and install the DSO File control.
    2. Add a reference to InteropDSOfile.dll to your managed Web application.
    3. Create a new Web form and copy the following code.
      <%@ Page Language="C#" %>

      <script runat="server">
      protected void btnLoadFile_Click(object sender, EventArgs e)
      // Define a path to save the file in the server
      string serverTempFilePath Server.MapPath(@"/yourpath/" + FileUpload1.FileName);

      // Create the DSOFile document
      DSOFile.OleDocumentPropertiesClass oleDocument = new DSOFile.OleDocumentPropertiesClass();
      DSOFile.SummaryProperties summaryProperties;


      // Extract the properties
      summaryProperties oleDocument.SummaryProperties;
      tbTitle.Text summaryProperties.Title;
      tbAuthors.Text summaryProperties.Author;
      tbCompany.Text summaryProperties.Company;
      tbNumPages.Text summaryProperties.PageCount.ToString();
      tbWordCount.Text summaryProperties.WordCount.ToString();

      // Close the DSOFile.OleDocumentPropertiesClass

      <html xmlns="">
      <head runat="server">
      <form id="form1" runat="server">
      </strong><br />
      br />
      table border="1">
      <td valign="top">
                              File upload:
      <asp:FileUpload ID="FileUpload1" runat="server" />
      asp:Button ID="btnLoadFile" runat="server" OnClick="btnLoadFile_Click" Text="Load File Properties" /><br />
      <asp:TextBox ID="tbTitle" runat="server"></asp:TextBox> 
      <asp:TextBox ID="tbAuthors" runat="server"></asp:TextBox> 
      <asp:TextBox ID="tbCompany" runat="server"></asp:TextBox> 
                              Number of Pages:
      <asp:TextBox ID="tbNumPages" runat="server"></asp:TextBox></td>
                              Word count:
      <asp:TextBox ID="tbWordCount" runat="server"></asp:TextBox> 

    4. If you run the previous Web form you will get something like this:

    You can also extract custom properties using the DSOFile control.

    Have a peek and enjoy!

  • Erika Ehrli - Adventures with Office Products & Technologies

    How to Export Data to Excel from an ASP.NET Application + Avoid the File Format Differ Prompt


    This is a common task for ASP.NET developers. You have a Web application where you expose data from a database, Web service, or third-party API and you need an "Export to Excel" button. I have used different approaches to export to Excel from a Web application. Here's some options and guidance of when to use each one.

    1. Generate a CSV file: If you open a CSV file in Excel, you can see a table with data. You can use this approach if you don't need control over formatting, styles, or workbook structure. I have used this approach when I pull data from a data source and I don't need to render it on the browser. For example, you are working on a stock options solution and you connect to a Web service that pulls stock market prices. Your users don't want to see detailed stock information on a browser and prefer that you generate and Excel file where they can pivot stock prices.
    2. Generate an XML file: (with or without an XSLT, depending if you want to control the schema). Have you opened an XML file in Excel? This is pretty cool. Excel allows you to open XML files with our without a schema. You can also see a table of data in Excel and can have some control on formatting if you use a schema. I have used this approach when I have a data source that is already in XML format.
    3. Generate from GridView: I like this one a lot and I think it's a common practice between ASP.NET developers. Some page forms in ASP.NET Web applications display data in data controls. The GridView is a popular control that displays data in a table format. You can use it to bind to data source controls, such as SqlDataSource. You can export to Excel from a GridView using a StringWriter and an HtmlTextWriter. You can use this approach if you already have a page with a GridView. You already did a round-trip to get the data from any given source, so why do it twice? The issue is that you have little control over formatting, style, or workbook structure.
    4. Generate an Excel file using the Open XML SDK 2.0: If you use this approach you gain absolute control of the spreadsheet format and content. For example, you can generate a worksheet with a table and another one with a chart based on the same data source. You can have control over formats, styles, content, and document structure. Zeyad has a great post where he provides a detailed sample for this approach: Document Assembly Solution for SpreadsheetML.

    Note: I can't stop to mention the big no-no… using the Excel PIA to generate a spreadsheet server-side. This is not a good practice and it's not recommended or supported, so let's forget about this one.

    Now, for those of you who are working with any of the first three approaches, you may have seen the prompt of file format differ each time you export to Excel.

    You get this message because you are opening a file in Microsoft Office Excel 2007 that contains content that does not match the files extension.

    I am not very annoyed about this, but I know some people are. By default, a user can decide whether to open the file when the warning message is displayed, but you can control user-notification either:

    1. Updating the registry if you need to control the user-notification on a few PCs.
    2. Using a Group Policy Setting if you need to control the user-notification on lots of PCs.

    Here's a KB article that provides detailed steps for both options: When you open a file in Excel 2007, you receive a warning that the file format differs from the format that the file name extension specifies.

    Here's a quick code sample in C# for approach 3 (Export from GridView) that you can use to export to Excel. I changed my registry following the steps explained in the previous article and it worked like a charm. No prompt! 

    <%@ Page Language="C#" AutoEventWireup="true" EnableEventValidation="false" %>

    <%@ Import Namespace="System.IO" %>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">
    <html xmlns="">
    <head id="Head1" runat="server">
    <title>Export to Excel - GridView Sample</title>

    <script language="C#" runat="server">
    // Get files from selected path    
    private void BindFiles() {
            DirectoryInfo di 
    = new DirectoryInfo(tbPath.Text);
    gvFiles.DataSource di.GetFiles();

    protected void btnExportToExcel_Click(object sender, EventArgs e) {
    //Export to Excel from a GridView
    protected void ExportToExcel() {
    Response.Buffer = true;
    Response.ContentType "application/";
    Response.Charset "";
    .EnableViewState = false;

    System.IO.StringWriter sw = new System.IO.StringWriter();
    System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);



    protected void Page_Load(object sender, EventArgs e) {

    public override void VerifyRenderingInServerForm(Control control) {


    <form id="form1" runat="server">
                My Files
    <table border="0" cellpadding="0" cellspacing="0" style="width: 100%; height: 12%">
    <asp:TextBox ID="tbPath" runat="server" Width="600px" Text="C:/"></asp:TextBox>
    <asp:Button ID="btnExportToExcel" runat="server" Text="ExportToExcel" 
    ="btnExportToExcel_Click" />
    <asp:GridView ID="gvFiles" runat="server">
    <br />

    Happy Friday!

  • Erika Ehrli - Adventures with Office Products & Technologies

    Data-driven document generation with Word 2007 and the Office XML File Formats: Part 2


    So you are looking for a component/technology/solution to help you generate Office files (documents, workbooks, and presentations) using a server-side application, and of course, using managed code.

    I can tell you that this need has been brought to my attention at some conferences, questions in DLs, customer feedback, and as a common customer requirement when I was working as a consultant. You know that using automation and interop is definitely not an option. However, the Office XML File Formats are a great option.

    You can build a server-side application using Visual Studio to generate data-rich documents using the Office XML File Formats and  the .NET Framework 3.0 (aka Microsoft WinFX ). Here's how...

    In my previous blog entry I demonstrated how to build and Word 2007 template and connect an item in the data store. You built a customer letter document template with content controls that map to an XML file. In this blog entry, I will show you how to create a server-side data-driven document generation application that will allow you to select a company name to generate a custom letter. The application will retrieve customer data from a SQL Server database and use the customer letter document template to build a new document that displays customer data based on a user selection. The document will display the following information:

    • Company Name
    • Contact Name
    • Contact Title
    • Phone

    To create a server-side application that pulls data from a SQL Server database and generates a new Word 2007 document


    You can create a Web-based application that enables users to select a company name and generate a custom letter. The Web-based application retrieves customer data from a Microsoft SQL Server database, opens the customer letter document template, and creates a new document that displays customer data based on a user selection. This application does not require the use of Word 2007 or VBA. You can use your favorite managed code (Microsoft Visual Basic .NET or C#) language to build this application. To build this application, do the following:

    1. Open Microsoft Visual Studio 2005 or Microsoft Visual Web Developer 2005.
    2. Create an ASP.NET Web site and name it SqlServerSample.
    3. Connect the ASP.NET Web site to a Microsoft SQL Server database.
    4. Add a connection string to the Web.config file as follows:
      <add name="NorthwindConnectionString"
      ="data source=(local);database=Northwind; integrated security=SSPI;persist security info=false;"
      ="System.Data.SqlClient" />
    5. Add the CustomerLetterTemplate.docx to the App_Data folder.
    6. Download and install the Microsoft .NET Framework 3.0 (formerly Microsoft WinFX).
    7. Configure the assembly in the Web.config file as follows:
      <compilation debug="false">
      <add assembly="WindowsBase, Version=, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
    8. Create a Web Form and replace the default.aspx code with the following sample code.

    The following sample shows how to bind to a Microsoft SQL Server database to retrieve data based on a customer selection and create a new document based on the CustomerLetterTemplate.docx.


    <%@ Page Language="VB" AutoEventWireup="true" %>
    <%@ Import 
    Namespace="System.Data" %>
    <%@ Import 
    Namespace="System.Data.SqlClient" %>
    <%@ Import 
    Namespace="System.IO" %>
    <%@ Import 
    Namespace="System.IO.Packaging" %>
    <%@ Import 
    Namespace="System.Xml" %>
    <!DOCTYPE html 
    PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">
    <html xmlns
    <head id
    ="Head1" runat="server">
        <title>Data-Driven Document Generation - SQL Server Sample</title>

    <script runat
    Private Const strRelRoot As String = ""
    Private Sub CreateDocument()
    ' Get the template file and create a stream from it
    Const TemplateFile As String = "~/App_Data/CustomerTemplate.docx"
    ' Read the file into memory
    Dim buffer() As Byte = File.ReadAllBytes(Server.MapPath(TemplateFile))
    Dim memoryStream As MemoryStream = New MemoryStream(buffer, True)
    ' Open the document in the stream and replace the custom XML part
    Dim pkgFile As Package Package.Open(memoryStream, FileMode.Open, FileAccess.ReadWrite)
    Dim pkgrcOfficeDocument As PackageRelationshipCollection pkgFile.GetRelationshipsByType(strRelRoot)
    For Each pkgr As PackageRelationship In pkgrcOfficeDocument
    If (pkgr.SourceUri.OriginalString "/"Then
    ' Add a custom XML part to the package
    Dim uriData As Uri = New Uri("/customXML/item1.xml", UriKind.Relative)
    If pkgFile.PartExists(uriData) Then
    ' Delete template "/customXML/item1.xml" part
    End If
    ' Load the custom XML data
    Dim pkgprtData As PackagePart pkgFile.CreatePart(uriData, "application/xml")
                    GetDataFromSQLServer(pkgprtData.GetStream, ddlCustomer.SelectedValue)
    End If
    ' Close the file
    ' Return the result
    "content-disposition""attachment; filename=document.docx")
    End Sub
        Private Sub 
    GetDataFromSQLServer(ByVal stream As Stream, ByVal customerID As String)
    'Connect to a Microsoft SQL Server database and get data
    Dim source As String = ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString
    Const SqlStatement As String = "SELECT CompanyName, ContactName, ContactTitle, Phone FROM Customers WHERE CustomerID=@customerID"
    Dim conn As SqlConnection = New SqlConnection(source)
    Dim cmd As SqlCommand = New SqlCommand(SqlStatement, conn)
    "@customerID", customerID)
    Dim dr As SqlDataReader cmd.ExecuteReader
    If dr.Read Then
    writer As XmlWriter XmlWriter.Create(stream)
    "CompanyName"CType(dr("CompanyName"), String))
    "ContactName"CType(dr("ContactName"), String))
    "ContactTitle"CType(dr("ContactTitle"), String))
    "Phone"CType(dr("Phone"), String))
    End If
    End Sub
        Protected Sub 
    SubmitBtn_Click(ByVal sender As ObjectByVal As EventArgs)
    End Sub

        <form id
    ="form1" runat="server">
                    Customer Letter Generator</h1>
                <table border
    ="0" cellpadding="0" cellspacing="0" style="width: 100%; height: 12%">
    Choose a customer:</td>
                            <asp:DropDownList ID
    ="ddlCustomer" runat="server" AutoPostBack="True" DataSourceID="CustomerData"
    DataTextField="CompanyName" DataValueField="CustomerID" Width="301px">
                            <asp:SqlDataSource ID
    ="CustomerData" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
    SelectCommand="SELECT [CustomerID], [CompanyName] FROM [Customers]" ProviderName="<%$ ConnectionStrings:NorthwindConnectionString.ProviderName %>">
            <br />
            <asp:Button ID
    ="Button1" runat="server" OnClick="SubmitBtn_Click" Text="Create Letter"
    Width="123px" />


    <%@ Page Language="C#" AutoEventWireup="true"%>
    <%@ Import Namespace
    ="System.Data" %>
    <%@ Import Namespace
    ="System.Data.SqlClient" %>    
    <%@ Import Namespace
    ="System.IO" %>
    <%@ Import Namespace
    ="System.IO.Packaging" %>
    <%@ Import Namespace
    ="System.Xml" %>

    <!DOCTYPE html PUBLIC 
    "-//W3C//DTD XHTML 1.0 Transitional//EN" "">

    <html xmlns
    ="" >
    <head id
    ="Head1" runat="server">
        <title>Data-Driven Document Generation - SQL Server Sample</title>
        <script language
    ="C#" runat="server">
    private const string strRelRoot "";

            private void 
    CreateDocument() {
    // Get the template file and create a stream from it
    const string TemplateFile @"~/App_Data/CustomerTemplate.docx";

    // Read the file into memory
    byte[] buffer File.ReadAllBytes(Server.MapPath(TemplateFile));
    MemoryStream memoryStream = new MemoryStream(buffer, true);
    buffer = null;

    // Open the document in the stream and replace the custom XML part
    Package pkgFile Package.Open(memoryStream, FileMode.Open, FileAccess.ReadWrite);
    PackageRelationshipCollection pkgrcOfficeDocument pkgFile.GetRelationshipsByType(strRelRoot);
    (PackageRelationship pkgr in pkgrcOfficeDocument) {
    if (pkgr.SourceUri.OriginalString == "/") {
                        // Add a custom XML part to the package
    Uri uriData = new Uri("/customXML/item1.xml", UriKind.Relative);

    (pkgFile.PartExists(uriData)) {
    // Delete template "/customXML/item1.xml" part
    // Load the custom XML data
    PackagePart pkgprtData pkgFile.CreatePart(uriData, "application/xml");
    GetDataFromSQLServer(pkgprtData.GetStream(), ddlCustomer.SelectedValue);

    // Close the file

    // Return the result
    Response.AddHeader("content-disposition""attachment; filename=document.docx");
    Response.ContentEncoding System.Text.Encoding.UTF8;




    private void GetDataFromSQLServer(Stream stream, string customerID) {
    //Connect to a Microsoft SQL Server database and get data
    String source ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
                const string 
    SqlStatement =
    "SELECT CompanyName, ContactName, ContactTitle, Phone FROM Customers WHERE CustomerID=@customerID";

    (SqlConnection conn = new SqlConnection(source)) {
    SqlCommand cmd = new SqlCommand(SqlStatement, conn);
    cmd.Parameters.AddWithValue("@customerID", customerID);
    SqlDataReader dr cmd.ExecuteReader();

    (dr.Read()) {
                        XmlWriter writer 
    writer.WriteElementString("CompanyName", (string)dr["CompanyName"]);
    writer.WriteElementString("ContactName", (string)dr["ContactName"]);
    writer.WriteElementString("ContactTitle", (string)dr["ContactTitle"]);
    writer.WriteElementString("Phone", (string)dr["Phone"]);

    protected void SubmitBtn_Click(object sender, EventArgs e) {

        <form id
    ="form1" runat="server">
                <h1>Customer Letter Generator</h1>
                <table border
    ="0" cellpadding="0" cellspacing="0" style="width: 100%; height: 12%">
                            Choose a customer:</td>
    ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
    SelectCommand="SELECT [CustomerID], [CompanyName] FROM [Customers]" ProviderName="<%$ ConnectionStrings:NorthwindConnectionString.ProviderName %>">
            <br />
    Text="Create Letter"
    Width="123px" />

    If you build and run this application, you will see something like this:
    As you can see, the code is simple and optimized for performance and memory. You can start building data-driven document solutions using this code. If your data source is an Access database, Web service, Oracle database, text file, or any other data source, it doesn't matter. Bottom-line, you only need to change the data access layer code. What matters the most is that using this technology you will be able to build server-side applications that generate Office documents using managed-code. I just love the potential that the Office XML File Formats provide and many thanks to Tristan Davis for sharing his knowledge on how to do this :). 
    Finally, I found out today that Microsoft published a virtual lab related with the Office XML File Formats and I wanted to share this as well:
    Programmatic Manipulation of the Microsoft Office Open XML Formats

    Learn how to manipulate Microsoft Office system documents using the Microsoft Office Open XML Formats without the 2007 release. Work through scenarios involving programmatically manipulating documents using the Microsoft Office Open XML Formats.

  • Erika Ehrli - Adventures with Office Products & Technologies

    Data-driven document generation with Word 2007 and the Office XML File Formats: Part 1


    Last week I had a chance to write some interesting code samples. I missed coding a lot, and I felt a little rusty after working in different projects. But I had a lot of fun and I want to share with you one of the samples I wrote: A simple ASP.NET 2.0 solution that retrieves data from SQL Server and generates a Word 2007 document using the Office XML File formats, WinFX, and XML Mappings in Word 2007.

    One of the most common requirements for applications that work with data is "data-driven document generation." No matter what the data source is -could be an Access database, SQL database, Web service, SharePoint list, Excel spreadsheet, XML file, Word document, or multiple sources. The typical "export data and create Word documents" feature is a common need.

    How to do it? Two simple steps:

    1. The Word part: Create a Word 2007 document template.
    2. The Cool app part: Create an application that pulls data from a datasource and generates new documents based on the Word 2007 document template.

    Word 2007 allows you to build data-driven document generation solutions. You can create a document template with a custom XML part and use Content Controls to bind to custom XML data using XML Mapping. Next, you can create a managed application to build a new document based on the template. The managed application will open the document template, retrieve data from a SQL Server database to build a new custom XML part, replace the template’s custom XML part , and save it as a new document.

    This little sample will show you how to build a document template from scratch and create a server-side application that generates documents that display data stored in a SQL Server database. How did I learn to do this, well, believe it or not, I watched the following Office Dev Con session:

    CD305—Word 2007 XML Programmability: Data/View Separation and Rich Eventing for Custom XML Solutions

    Presenter: Tristan Davis; 59 minutes (180 MB)
    In this session, we introduce new XML capabilities, then dive into the functionality of the Office XML data store and how it can be leveraged to build solutions that will strongly tie Word documents to your business processes.

    ..and then asked Tristan Davis some questions related with XML Mapping.

    Business Scenario: Custom Customer Letter

    To demonstrate how to build and Word 2007 template and connect an item in the data store to a SQL Server database, you will first build a customer letter document template with content controls that map to an XML file. Next, you will create a server-side data-driven document generation application that will allow you to select a company name to generate a custom letter. The application will retrieve customer data from a SQL Server database and use the customer letter document template to build a new document that displays customer data based on a user selection. The document will display the following information:

    • Company Name
    • Contact Name
    • Contact Title
    • Phone

    To create a Word template and define the XML Mappings for each content control.

    Use the following steps to create a Microsoft Office Word 2007 template.

    1. Open Word 2007.

    2. Create a document.

    3. Create content controls to bind to a node in the data store.

    What are content controls? Content controls are predefined pieces of content. There are several types of content controls, including text blocks, drop-down menus, combo boxes, calendar controls, and pictures. You can map these content controls to an element in an XML file. Using XPath expressions, you can programmatically map content in an XML file to a content control. This enables you to write a simple and short application to manipulate and modify data in a document. 

    4. Set the XML mapping on the content control.

    XML mapping is a feature of Word 2007 that enables you to create a link between a document and an XML file. This creates true data/view separation between the document formatting and the custom XML data.

    To load a custom XML part, you must first add a new data store to a Document object by using the Add method of the CustomXMLParts collection. This appends a new, empty data store to the document. Because it is empty, you cannot use it yet. Next, you must load a custom XML part from an XML file into the data store, by calling the Load method of the CustomXMLPart object, using a valid path to an XML file as the parameter.

    5. Add four plain text content controls in the following order.

    • Content control for Company Name
    • Content control for Contact Name
    • Content control for Contact Title
    • Content control for Phone

    6. Save the template document as C:\CustomerLetterTemplate.docx.

    To set an XML mapping on a content control

    Note In this procedure, you map the content control to a sample custom XML file. You will create a valid custom XML file, save it to your hard drive, and add a data store to the document that contains the information to which you want to map.

    1. Create a text file and save it as c:\CustomerData.xml.

    2. Paste the following into the text file and save it: 

    <?xml version="1.0"?>
    <CompanyName>Alfreds Futterkiste</CompanyName>
    <ContactName>Maria Anders</ContactName>
    <ContactTitle>Sales Representative</ContactTitle>

    3. Now, map each content control to bind to the <CompanyName>, <ContactName>, <ContactTitle>, and <Phone> and nodes of the previous custom XML part.

    4. Open the Visual Basic editor and run the following VBA code to add a data store to your template document.

    This sample code demonstrates how to attach an XML file to a document, so that it becomes an available data store item.

    ' Load CustomerData.xml file
    4).Load ("c:\CustomerData.xml")

    To create an XML mapping, you use an XPath expression to the node in the custom XML data part to which you want to map a content control. After you add a data store to your document (and the data store points to a valid XML file), you are ready to map one of its nodes to a content control. To do this, pass a String containing a valid XPath to a ContentControl object by using the SetMapping method of the XMLMapping object (via the XMLMapping property of the ContentControl object).

    5. Open the Visual Basic editor and run the following VBA code to bind content controls to items in the data store.

    Dim strXPath1 As String
    ActiveDocument.ContentControls(1).XMLMapping.SetMapping strXPath1

    Dim strXPath2 As String
    ActiveDocument.ContentControls(2).XMLMapping.SetMapping strXPath2

    Dim strXPath3 As String
    ActiveDocument.ContentControls(3).XMLMapping.SetMapping strXPath3

    Dim strXPath4 As String
    ActiveDocument.ContentControls(4).XMLMapping.SetMapping strXPath4

    You have your template file and you are ready to start your document generation application. I love ASP.NET and I created a server-side application to show you how to generate documents on the server using the Office XML File Formats. You can build a document generation application using Win Forms, a console application, a shared Add-in or your preferred application depending of your business needs.

    If you want to learn more about Word 2007 and document templates, read this article:

    This blog entry is too long already, so I will split it here and leave you with the intrigue of what part 2 will bring. Stay connected and you will soon know the happy ending for this walkthrough.



  • Erika Ehrli - Adventures with Office Products & Technologies

    Hosting Office in .NET applications


    If you are building a .NET application and you are struggling to find a way to host Word documents, Excel spreadsheets, Visio drawings, Project files, or PowerPoint presentations, you might consider using the Visual C++ ActiveX Control for hosting Office documents. I learned about this download resource and I was shocked to see how you can add it to your solution and start working with Office applications in less than a minute. You can open Office applications, format content, update documents, save changes, print, and more inside your application. The Office Framer Control is an Active X control written in C++. The download includes source code to extend the functionality and sample code to use it inside Visual Basic 6 and HTML pages. You can also enable and disable specific functionality by using a full set of properties, methods, and events exposed for customization.

    You can also host the control inside managed applications following these simple steps:

    1. Download and install the Office Framer Control 1.2 Sample.
    2. Start Visual Studio .NET. 
    3. Open your WinForms or WebForms application.
    4. Add the DSO Framer Control to the Toolbox: 
      • On the Tools menu, click Customize Toolbox (in Visual Studio .NET 2002), or click Add/Remove Toolbox Items (in Visual Studio .NET 2003), and then click the COM Components tab. 
      • On the COM Components tab, select DSO Framer Control Object. 
      • Click OK to close the Customize Toolbox dialog box.
    5. Select the DSO Framer Control icon that appears in the ToolBox and drag-and-drop the control over the Form or Web Form.
    6. Adjust the control’s docking/size as needed.
    7. Build and run the application.
    8. Create a new document to test the control.
    I have to warn you that the control is not supported, the control and the source code is provided AS-IS for customization. However, you still can extend the functionality and it does the job, so have a peek and enjoy!

  • Erika Ehrli - Adventures with Office Products & Technologies

    How to programmatically generate Microsoft Excel AutoFiltered Lists with C#


    One of my favorite features in Microsoft Excel is AutoFilter. I love to manage lists of data in Excel where I can organize, sort, and filter my information in different columns.

    Filtering is a quick and easy way to find and work with a subset of data in a range. A filtered range displays only the rows that meet custom filter criteria defined in a search query or fitler specified for a column. Microsoft Excel provides two commands for filtering ranges: AutoFilter and Advanced Filter. Unlike sorting, filtering does not rearrange a range. Filtering temporarily hides rows you do not want displayed. When Excel filters rows, you can edit, format, chart, and print your range subset without rearranging or moving it. You can learn more about autofiltering here: All About AutoFilter.

    The best part is that you can programatically generate autofiltered lists from your managed applications thanks to the extensibility offered by the Microsoft Excel Primary Interop Assembly. You can use the Range.AutoFilter method to filter a list using AutoFilter. The following code sample (C# Console Application) generates an autofiltered list of all the directories and files that belong to given directory path. You can modify the code and send any path that you need.

    namespace ErikaEc.OfficeTools.Excel
    using System;
    Excel Microsoft.Office.Interop.Excel;

    DemoExcelAutoFiltering {
    static void Main(string[] args) {
                DirectoryInfo di 
    = new DirectoryInfo(@"C:\Program Files\Microsoft Office\OFFICE11\1033");
    ExcelFileReport efr = new ExcelFileReport(di);

    public class ExcelFileReport {
    private object _missing;
    Excel.Workbook _book;
    Excel.Worksheet _sheet;
    Excel.Range _rng;
    DirectoryInfo _di;
    ExcelHelper _eh = new ExcelHelper();

    ExcelFileReport(DirectoryInfo di) {
    _missing System.Reflection.Missing.Value;
    _row 4;

    public void DocumentDirectory(DirectoryInfo di) {
    foreach (DirectoryInfo d in di.GetDirectories()) {
    foreach (FileInfo f in di.GetFiles()) {
    _rng (Excel.Range)_sheet.Cells[_row, "A"];
    _rng.Value2 di.Name;
    _rng (Excel.Range)_sheet.Cells[_row, "B"];
    _rng.Value2 f.FullName;
    _rng (Excel.Range)_sheet.Cells[_row, "C"];
    _rng.Value2 f.Name;
    _rng (Excel.Range)_sheet.Cells[_row, "D"];
    _rng.Value2 f.Length;
    _rng (Excel.Range)_sheet.Cells[_row, "E"];
    _rng.Value2 f.Extension;
    _rng (Excel.Range)_sheet.Cells[_row, "F"];
    _rng.Value2 f.LastWriteTime.ToLongDateString();
    public void Generate(){
    string caption "File Analysis Results";
    heading1 "File Analysis Report for Folder " + _di.FullName;
    _book _eh.Create(caption, heading1);
    _sheet ((Excel.Worksheet)_book.ActiveSheet);

    private void SetAutoFilter(){
    string lastrow "F" + _row.ToString();
    _rng ((Excel.Worksheet)_book.ActiveSheet).get_Range("A4", lastrow);
    _rng.AutoFilter(1, _missing, Excel.XlAutoFilterOperator.xlAnd, _missing, true);
    _rng.Borders.LineStyle Excel.XlLineStyle.xlContinuous;
    public void WriteTableHeader(){
    _rng.Font.Bold = true;
    _rng.EntireRow.Font.Bold = true;

    _rng (Excel.Range)_sheet.Cells[_row, "A"];
    _rng.Value2 "Parent Directory";
    _rng (Excel.Range)_sheet.Cells[_row, "B"];
    _rng.Value2 "Full Path";
    _rng (Excel.Range)_sheet.Cells[_row, "C"];
    _rng.Value2 "File Name";
    _rng (Excel.Range)_sheet.Cells[_row, "D"];
    _rng.Value2 "Size";
    _rng (Excel.Range)_sheet.Cells[_row, "E"];
    _rng.Value2 "Type";
    _rng (Excel.Range)_sheet.Cells[_row, "F"];
    _rng.Value2 "Last Modified";

    _sheet.Columns.ColumnWidth 30;

    class ExcelHelper {
    private Excel.Application _excelApplication;
    ExcelHelper() {
    = new Excel.Application();

    public Excel.Workbook Create(string caption, string heading1) {
    try {
    _excelApplication.ScreenUpdating = false;
    _excelApplication.Visible = false;

    Excel.Workbook book _excelApplication.Workbooks.Add(Excel.XlSheetType.xlWorksheet);
    Excel.Worksheet sheet (Excel.Worksheet)book.ActiveSheet;

    Excel.Range r (Excel.Range)sheet.Cells[1"A"];
    r.Value2 heading1;
    r.EntireRow.Font.Bold = true;


    catch (Exception ex) {
    throw (ex);

    public void Close() {
    = true;
    _excelApplication.Visible = true;
    _excelApplication.DisplayAlerts = true;

    (_excelApplication != null) {
    _excelApplication = null;

    Run this application and give it a try!

  • Erika Ehrli - Adventures with Office Products & Technologies

    Office developer resources in your own language


    If you are into technology, you are mostly going to find documentation in English. However, if you speak another language, it’s always useful to find documentation in your own language. I am from Mexico and I am always pleased to find technical documentation in Spanish.

    Here is a list of international resources related to Office development that you might enjoy reading…

    Parlez-vous Français?…

    Office system 2007, c'est bien sûr une avalanche d'innovations, dont une nouvelle interface utilisateur qui facilite la découverte des fonctionnalités. Mais Office system 2007, c'est aussi une plateforme de collaboration et de gestion de contenus, avec laquelle vos applications métiers peuvent interagir.

    MSDN: Centre de développement Office et XML


    Non parlo italiano, ma potreste

    2007 Microsoft Office system è una soluzione avanzata che comprende applicazioni client e server, servizi e strumenti. Costruita sul formato XML e su una serie di tecnologie estendibili, ti consente facilmente di sviluppare applicazioni, dal business content management alla business intelligence, fino al project management e alla collaborazione in tempo reale.

    MSDN: Developer Center Microsoft Office

    Community italiane


    Sprechen Sie Deutsch?

    Microsoft Office in der Version 2007 bringt einige revolutionäre Neuerungen, z.B. in der Benutzeroberfläche - aber auch in der Art und Weise, wie Entwickler eigene Anwendungen unter dem Dach des weit verbreiteten Office Systems spielen lassen und dabei auf eine reichhaltige Fülle von Funktionen und Funktionalität zurückgreifen können.

    MSDN: Developer Center - Office Development


    Entwickler Communities:



    を利用した開発に慣れていない方には、このスクリーンキャストシリーズをお勧めします。Office 開発者 スクリーンキャスト バーチャル ツアーでは 2007 Microsoft Office System を利用した開発を始めるのに必要な学習教材を提供します。


    Office Online:


    Viva el Español!

    Microsoft Office System es una visión tecnológica que esta conformada por un conjunto de elementos totalmente integrados como: Programas, Servidores, Soluciones y Servicios, que permiten a los desarrolladores construír soluciones de negocios para conectar a la organización y a las personas con la información y los procesos de negocios rápida y fácilmente sobre una plataforma familiar.

    Acabo de darme el gusto de escribir algo en español en mi blog. Con todo gusto para la gente que habla español, ahí les van unas ligas que les pueden ser de utilidad:

    Artículos interesantes sobre desarrollo con Office:


    Comunidad Office System

    Office Online


    Por favor lean este artículo de Luis acerca de colaboración con SharePoint:

    Other worldwide resources:

    You can find more worldwide sites and international DLs here:

    International Discussion Groups:

    MSDN Worldwide Site

    Office Online Worldwide sites:

    I am planning to update this blog entry on a regular basis once I learn more about other resources. If you know about good international Office developer blogs and Web sites, please let me know and I will be glad to trackback.

    I have to say it, I had a lot of fun writing this blog entry :).


  • Erika Ehrli - Adventures with Office Products & Technologies

    Developer Map for the 2007 Microsoft Office System: Download your poster today!


    A couple of weeks ago I blogged about the treasure map for Office developers that we were building and I am glad to announce that we finished two weeks ago the poster version and it's ready to download! This is the third poster of the 2007 Office System Document: Developer Posters download family.

    The Developer Map for the 2007 Microsoft Office system helps developers visualize the different programs, servers, services and tools that will help them build solutions. You can drill down to each product and technology and learn about new features, objects, Web services, namespaces and schemas requiered to extend Office. There are more than 50 persons who worked to make this possible with the motivation of building a cool tool to evangelize Office. Based on the fact that Office 2007 is more than you think, we thought this was a great way to help developers understand the bigger picture and learn how Office products and technologies relate to each other.

    The goals of this poster are:

    1. Provide a roadmap that shows all Office products and technologies that have an interesting developer story.
    2. Categorize all extensibility technologies (green bars) and visually demonstrate how you can use them to extend specific products. For example, you can use Ribbon UI extensibility for Word 2007, Excel 2007, PowerPoint 2007, Outlook 2007 and Access 2007, but not for InfoPath 2007, Visio 2007, Project 2007, Groove 2007 or Communicator 2007.
    3. List most relevant developer features (blue text) added to Office (2007) programs.
    4. Make a clear distinction between all Office products and technologies (programs, servers, tools and services).
    5. Make a clear distinction between features and services offered by WSS 3.0 and Office SharePoint Server 2007.
    6. Highlight the programs you can extend and customize using VSTO and VSTA (you will see a logo where VSTO or VSTA applies).
    7. Explain the Add-in story by making a clear distinction between Shared Add-Ins, VSTO 2005 SE Application Level Add-Ins and COM Add-Ins.
    8. Provide the names of specific namespaces, Web services, interfaces or schemas (gray text) required to extend functionality of different Office products and technologies.
    9. Provide a list of most relevant objects (green text) used to extend Office programs, servers and WSS 3.0.
    10. Finally, the most challenging goal, summarize the most relevant facts and make it all fit on a limited rectangular space.

    BTW, if you have plans to attend Dev Connections in Las Vegas or Tech Ed Barcelona, you should visit the Office booths. We are sending nicely printed copies of this poster. I encourage you to get one (or more) b/c there's a lot of text and it looks better if you print it on a bigger piece of paper.

    I can't describe how happy I am because of this poster. I enjoyed working with all the people who made this possible, but what makes me happier is that I know this will help other developers understand quickly what took me months to figure out. 

    Cheers to Office and the whole galaxy of developer technologies it represents!

  • Erika Ehrli - Adventures with Office Products & Technologies

    How to indent an XML file or document


    Office has a deeper integration with XML technology and developers are always looking for tips and tricks to work with XML documents. Office provides support to work with XML and you might be one of those developers that is programmatically generating Word documents (using WordprocessingML) , Excel spreadsheets (using SpreadsheetML), PowerPoint slides (using PresentationML), or Visio diagrams using (DataDiagrammingML). I think it always comes handy to have a list of tips and tricks to work with XML, and today I will share with you three simple ways of indenting an XML file/document.

    Indenting XML files might sound as one of those netpick or nice-to-have enhancements that you don’t really need when you are working with XML. However, lots of applications and tools generate programmatically XML files and it always comes handy to open a nice and readable indented XML file instead of a “how can I edit this!” single line of eternal XML elements.

    For managed applications:

    • If you are generating XML files using and XmlTextWriter, you just need to do the following:

      Dim writer as XmlTextWriter = new XmlTextWriter("data.xml",nothing)

      XmlTextWriter writer = new XmlTextWriter("data.xml",null);
      writer.Formatting Formatting.Indented;
    • If you are generating XML files/documents using DOM (XmlDocument) , you can add an XmlTextWriter to indent the code and you will be done:

      Dim doc as XmlDocument = new XmlDocument()
      ' Save the document to a file and auto-indent the output.
      Dim writer as XmlTextWriter = new XmlTextWriter("data.xml",nothing)

      XmlDocument doc = new XmlDocument();
      // Save the document to a file and auto-indent the output.
      XmlTextWriter writer = new XmlTextWriter("data.xml",null);
      writer.Formatting Formatting.Indented;

    For any platform:

    • If you are generating XML files using an XSL transform file, you just need to add a simple line to your XSL file.

      <?xml version="1.0"?>
      <xsl:stylesheet version="1.0" xmlns:xsl="">
      <xsl:output method="xml" omit-xml-declaration="no" indent="yes" encoding="US-ASCII"/>

    Happy Office XML programming!

  • Erika Ehrli - Adventures with Office Products & Technologies

    Office "12" bloggers list


    To view the list of Office "12" bloggers from my previous blog entry you must have Microsoft® Internet Explorer 5.01 Service Pack 2 (SP2) or later and the Microsoft Office 2003 Web Components.

    For those of you that have a different browser here's the list:

    Topic Owner Name URL RSS feed
    Access 12 Erik Rucker A discussion of what's new in Access 12
    Document Services (ECM) Rob Lefferts Office SharePoint Document Services (ECM)
    Excel 12 Dave Gainer Microsoft Excel 12
    FrontPage 12 Rob Mauceri Rob Mauceri's FrontPage Blog
    Groove Mark Olson Marc's blog about the integration of Groove into Office 12.
    InfoPath 12 Tudor Toma InfoPath 12 - Tudor Toma
    Office 12 (General) Erika Ehrli Adventures in Office Development and .NET
    Office 12 (General) John Durant Office and a short, bald man
    Office 12 (General) Steven Sinofsky Steven Sinofsky's Microsoft TechTalk
    Office 12 Help Mike Kelly From my Office to Yours
    Office 12 New User Interface Jensen Harris Jensen Harris  An Office User Interface Blog
    Office 12 New XML File Format Brian Jones Brian Jones  Office XML Formats
    OneNote 12 Chris Pratley Chris Pratley's OneNote Blog
    OneNote 12 Owen Braun Owen Braun  OneNote 12
    Outlook 12 Melissa MacBeth Tasks and Time Management in Outlook
    Outlook 12 Michael Affronti michael affronti | microsoft outlook program manager
    Outlook 12 Will Kennedy Outlook 12
    Outlook 12 Extensibility Ryan Gregg Ryan's Look at Outlook Programmability
    PDF Support in Office 12 Cyndy Wessling Save as PDF in Office "12"
    Presentation and Graphics Brendan Busch Brendan Busch's blog on PowerPoint 12
    Project 12 Dieter Zirkler Dieter's ProjBlog
    Publisher 12 Jeff Bell On Microsoft Publisher and more
    SharePoint Products and Technologies Mike Fitz SharePoint Products and Technologies
    SharePoint Products and Technologies SharePoint Team A blog from the SharePoint Development Team
    SharePoint Products and Technologies WCM Team Web Content Management in Office "12"
    Visio 12 Eric Rockey What's new in Visio 12
    VSTO VSTO Team Microsoft Visual Studio 2005 Tools for the Microsoft Office System
    Windows Sharepoint Services PJ Hough Windows Sharepoint Services etc
    Word 12 Joe Friend Joe Friend  Microsoft Office Word
    XPS and Windows Digital Documents Andy Simonds My blog (Andy, GPM in Windows) about XPS and the Windows Digital Documents Team.

  • Erika Ehrli - Adventures with Office Products & Technologies

    Open XML File Formats: What is it, and how can I get started?


    While being at Tech Ed, a lot of people were interested in finding a way to programmatically generate documents without Interop. Some of the business scenarios contemplated generating over 5,000 documents and some IT professionals were interested in finding the best option. A great option to solve this business need is: The Open XML File Formats.

    Some people have been following the news and are even ahead of most of us already building solutions to generate documents using the Open XML File Formats. Some other people are not familiar with this technology and want to learn more about this, so here is a quick introduction for those of you who want to learn more about: What is it, and how you can get started. I have to warn you that this is going to be a long blog entry, but I promise it's worth the reading.

    What is it?

    The new formats improve file and data management, data recovery, and interoperability with line-of-business systems. They extend what is possible with the binary files of earlier versions. Any application that supports XML can access and work with data in the new file format. The application does not need to be part of the Microsoft Office system or even a Microsoft product. Users can also use standard transformations to extract or repurpose the data. In addition, security concerns are drastically reduced because the information is stored in XML, which is essentially plain text. Thus, the data can pass through corporate firewalls without hindrance.

    The new Open XML File Formats take advantage of the Open Packaging Conventions, which describe the method for packaging information in a file format and describe metadata, parts, and relationships. The new Open XML Format, with a few minor exceptions, is written entirely in XML and is contained in a .zip file. This creates significant advantages over the old binary file format:

    • The file size is much smaller because of ZIP compression.
    • The file is much more robust because it is broken up into different document parts. Should one part become damaged (for example, a part describing headers), the rest of the document remains intact and still opens successfully.
    • The file is easier to work with programmatically because of the new structure. For example, it is easier to access embedded content, such as images, because they are stored in their native format inside the file.
    • Custom XML is also easier to work with because it is stored in its own part, separate from the XML that describes the bulk of a document.

    The old binary file format was created when priorities in software differed from the priorities of today. Back then, the ability to transfer a Word document from computer to computer using a floppy disc ranked very high, and the tight structure of a binary format worked well. As software advanced, other priorities became clear, such as the ability to write code against a file format and make it as robust as possible. XML is a clear solution.

    Microsoft began to address this issue in previous versions of Microsoft Office by introducing SpreadSheetML and WordprocessingML. However, only now, with the 2007 release of Microsoft Office, have the goals that were conceived as far back as 1999 been accomplished fully. By including the XML File Format inside a ZIP container, the benefit of a small compressed file format is also realized. Excel 2007 and PowerPoint 2007 share this new file format technology, described by the Open Packaging Conventions. Together, the shared formats are called the Microsoft Office Open XML Formats. The new Word 2007 XML Format is the default file format, although the old binary file format is still available in the 2007 Microsoft Office system.

    An easy way to look inside the new file format is to save a Word 2007 document in the new default format and then rename the file with a .zip extension. By double-clicking the renamed file, you can open and look at its contents. Inside the file, you can see the document parts that make up the file, along with the relationships that describe how the parts interact with one another. However, it is important to note that, with a few exceptions defined within the Open Packaging Conventions, the actual file directory structure is arbitrary. The relationships of the files within the package, not the file structure, are what determine file validity. You can rearrange and rename the parts of an Word 2007 file inside its .zip container if you update the relationships properly so that the document parts continue to relate to one another as designed. If the relationships are accurate, the file opens without error. The initial file structure in a Word 2007 file is simply the default structure created by Word. This default structure enables developers to determine the composition of Word 2007 files easily.

    Contents of a sample document in a ZIP file

    How can I get started?

    The easiest way to modify a Word 2007 XML file programmatically is to use the System.IO.Packaging class in the Microsoft® Windows® Software Development Kit (SDK) for Beta 2 of Windows Vista and WinFX Runtime Components. Using this technology, you can easily update header and footer files programmatically across numerous Word 2007 documents stored on a server.

    We published recently some resources that might be of your interest if you are trying to learn more about the Open XML File Formats:

    Open XML Snippets

    • Open XML: Get OfficeDocument Part: Given an Open XML file, retrieve the part with the relationship type.

    Microsoft Office Excel Snippets

    • Excel: Add Custom UI: This snippet adds a custom UI Ribbon part to a given workbook.
    • Excel: Delete Comments by a specific User: This snippet deletes all comments from a given user from a given workbook.
    • Excel: Delete Worksheet: This snippet deletes the specified worksheet from within a given workbook and resets the selected worksheet to the next one on the list. Returns true if successful, false if failure.
    • Excel: Delete Excel 4.0 Macro sheets: This snippet deletes all the Excel 4.0 Macro (XLM) sheets from a given workbook.
    • Excel: Retrieve hidden rows or columns: This snippet returns a list of hidden row numbers or column names from a given workbook and worksheet.
    • Excel: Export Chart: Given a workbook and title of a chart, this snippet exports the chart as a Chart (.crtx) file.
    • Excel: Get Cell Value: Given a workbook, worksheet and cell address, this snippet returns the value of the cell as a string.
    • Excel: Get Comments as XML: Given a workbook, this snippet returns all the comments as an XmlDocument.
    • Excel: Get Hidden Worksheets: This snippet returns a list containing the name and type of all hidden sheets in a given workbook.
    • Excel: Get Worksheet Information: This snippet returns a list containing the name and type of all sheets in a given workbook.
    • Excel: Get Cell for Reading: Given a workbook, worksheet and cell address, this snippet demonstrates how to navigate to the cell to retrieve its contents. The cell must exist for the function to find it.
    • Excel: Get Cell for Writing: Given a workbook, worksheet and cell address, this snippet demonstrates how to navigate to the cell to set its value. If the cell does not exist, the snippet creates it.
    • Excel: Insert Custom XML: Given a workbook and a custom XML value, this snippet inserts the custom XML into the workbook.
    • Excel: Insert Header or Footer: Given a workbook, worksheet and text to insert and a header or footer type, this snippet inserts the header or footer with the given text into the worksheet.
    • Excel: Insert a Numeric Value into a Cell: Given a workbook, worksheet, cell address and numeric value, this snippet inserts the value into the cell.
    • Excel: Insert a String Value into a Cell: Given a workbook, worksheet, cell address and string value, this snippet inserts the value into the cell.
    • Excel: Set Recalc Option: Given a workbook and a RecalcOption, this snippet sets the recalculation property to the new option.

    Microsoft Office PowerPoint Snippets

    • PowerPoint: Delete Comments by User: Given a presentation and a user name, this snippet deletes all comments by that user.
    • PowerPoint: Delete Slide by Title: Given a presentation and slide title, this snippet deletes the first instance of a slide with that title (titles are not unique).
    • PowerPoint: Get Slide Count: This snippet returns the number of slides in a given presentation.
    • PowerPoint: Get Slide Titles: Given a presentation, this snippet returns a list of the slide titles in the order presented.
    • PowerPoint: Modify Slide Title: Given a presentation, old slide title, and new slide title, this snippet changes the first instance of a slide with the given title to the new value. The snippet returns true if successful, false if not successful.
    • PowerPoint: Reorder Slides: Given a presentation, an original position, and a new position, attempt to place the slide from the original position into the new position within the deck. If the original position is outside the range of the number of slides in the deck, use the last slide. If the new position is outside the range of slides in the deck, put the selected slide at the end of the deck. The snippet returns the loctation wher the slide was placed, or -1 on failure.
    • PowerPoint: Replace Image: Given a presentation, slide title and image file, this snippet replaces the first image on the slide with the given image.
    • PowerPoint: Retrieve Slide Location by Title: Given a presentation and a slide title, this snippet returns the 0-based location of the first slide with a matching title.

    Microsoft Office Word Snippets

    • Word: Accept Revisions: Given a document and an author name, this snippet accepts the revisions by that author.
    • Word: Add Header: Given a document and a stream containing valid header content, add the stream content as a header in the document.
    • Word: Convert DOCM to DOCX: Given a macro-enabled document (.docm), this snippet removes the VBA project and converts the file to a macro-free Word Document (.docx).
    • Word: Remove Comments: Given a Word Document, this snippet removes all the comments.
    • Word: Remove Headers and Footers: This snippet removes all headers and footers from a given Word document.
    • Word: Remove Hidden Text: This snippet removes any hidden text in a given document.
    • Word: Replace Style: Given a document and valid header content, this snippet adds the content as a header in the document.
    • Word: Retrieve Application Property: Given a document name and an app property, this snippet returns the value of the property.
    • Word: Retrieve Core Property: Given a document name and a core property, this snippet returns the value of the property.
    • Word: Retrieve Custom Property: Given a document name and a custom property, this snippet returns the value of the property.
    • Word: Retrieve Table of Contents: Given a document name, this snippet returns a table of contents as an XmlDocument.
    • Word: Set Application Property: This snippet sets a property’s value given a document name, application property and value. The snippet returns the old value if successful.
    • Word: Set Core Property: Given a document name, a core property, and property value, this snippet sets the property value.
    • Word: Set Custom Property: Given a document name, a custom property, and a value, this snippet sets the property’s value. If the property does not exist, create it. Returns true if successful, false if not.
    • Word: Set Print Orientation: Given a document name, this snippet sets the print orientation for all sections in the document.

    Download them here!

    Finally, if you want to stay current with new resources to work with the Open XML File Formats, go to the XML in Office Developer Portal. We launched this portal recently to create a special section of the MSDN Office Developer Center where you will find bloggers, technical articles, code samples, developer documentation, and multimedia presentations on working with XML in Office.

    Happy Office XML programming!
  • Erika Ehrli - Adventures with Office Products & Technologies

    Open XML Format SDK 2.0: Getting Started Best Practices


    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…

    1. Always start with a template for your solution
    2. Use DocumentReflector to get started with coding

    Always start with the template for your solution

    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.

    Use DocumentReflector to get started with coding

    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:

    1. Open the DocumentReflector tool: The DocumentReflector tool is located in the Tools folder under the Open XML Format SDK 2.0 installation folder. Use Windows Explorer to navigate to the Tools folder and double-click the DocumentReflector.exe file to launch the DocumentReflector tool.
    2. Select the DocumentReflector's File | Open menu and in the Open dialog browse to the folder containing the ExpenseReport.xlsx workbook created previously, select the workbook, and click on the Open button. DocumentReflector will open the workbook and display the content of the document. Clicking on the top-level Package node will display the generated code that can be used to create the entire package.


    At this point the code generated by DocumentReflector can be copied and pasted into the Visual Studio solution for reuse and learning purposes.

    Using Visual Studio 2008 to create a Windows console application

    For testing purposes, you can create a Console application in Visual Studio 2008. Here are some generic steps:

    1. Open Visual Studio 2008.
    2. Create a new C# Windows Console Application project.
    3. Add a reference to the Open XML API assembly.
    4. Add a reference to the WindowsBase assembly.

      The next steps involve copying code generated by the DocumentReflector tool to the Visual Studio project. The DocumentReflector tool provided with the Open XML Format SDK 2.0 allows users to open a valid Open XML document, choose an XML element, part, or the whole package, and have DocumentReflector generate a C# class that can create the selected document parts using the Open XML Format SDK 2.0 classes.
    5. Select the using statements from the top of the DocumentReflector code window then copy and paste them to the top of the Program.cs file in the Visual Studio project.


    using DocumentFormat.OpenXml.Packaging
    ap = DocumentFormat.OpenXml.ExtendedProperties
    vt = DocumentFormat.OpenXml.VariantTypes
    a = DocumentFormat.OpenXml.Drawing
    op = DocumentFormat.OpenXml.CustomProperties;

    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.


    private static void CreatePackage(string filePath) { 
    using (SpreadsheetDocument package
                SpreadsheetDocumentType.Workbook)) { 



    The AddParts method creates all the parts that you need in the Expense Report spreadsheet.


    private static void AddParts(SpreadsheetDocument parent) { 
               var extendedFilePropertiesPart1
    = parent.AddNewPart<ExtendedFilePropertiesPart>("rId3")

    var coreFilePropertiesPart1 = parent.AddNewPart<CoreFilePropertiesPart>("rId2")

    var workbookPart1 = parent.AddWorkbookPart()

    var workbookStylesPart1 = workbookPart1.AddNewPart<WorkbookStylesPart>("rId3")

    var themePart1 = workbookPart1.AddNewPart<ThemePart>("rId2")

    var worksheetPart1 = workbookPart1.AddNewPart<WorksheetPart>("rId1")

    var spreadsheetPrinterSettingsPart1 = worksheetPart1.AddNewPart<SpreadsheetPrinterSettingsPart>("rId1")

    var calculationChainPart1 = workbookPart1.AddNewPart<CalculationChainPart>("rId5")

    var sharedStringTablePart1 = workbookPart1.AddNewPart<SharedStringTablePart>("rId4")

    var customFilePropertiesPart1 = parent.AddNewPart<CustomFilePropertiesPart>("rId4")


    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.

    private static SharedStringTable GenerateSharedStringTablePart1() { 
             var element

    new SharedStringItem( 
    new Text("Name")), 
    new SharedStringItem( 
    new Text("Department")), 
    new SharedStringItem( 
    new Text("Manager")), 
    new SharedStringItem( 
    new Text("Position")), 
    new SharedStringItem( 
    new Text("From")), 
    new SharedStringItem( 
    new Text("To")), 
    new SharedStringItem( 
    new Text("Date")), 
    new SharedStringItem( 
    new Text("Account")), 
    new SharedStringItem( 
    new Text("Description")), 
    new SharedStringItem( 
    new Text("Transport")), 
    new SharedStringItem( 
    new Text("Fuel")), 
    new SharedStringItem( 
    new Text("Meals")), 
    new SharedStringItem( 
    new Text("Phone")), 
    new SharedStringItem( 
    new Text("Subtotal")), 
    new SharedStringItem( 
    new Text("Advances")), 
    new SharedStringItem( 
    new Text("For Office Use Only")), 
    new SharedStringItem( 
    new Text("Misc.")), 
    new SharedStringItem( 
    new Text("Hotel")), 
    new SharedStringItem( 
    new Text("Entertainment")), 
    new SharedStringItem( 
    new Text("PURPOSE:")), 
    new SharedStringItem( 
    new Text("STATEMENT NUMBER:")), 
    new SharedStringItem( 
    new Text("PAY PERIOD:")), 
    new SharedStringItem( 
    new Text("EMPLOYEE INFORMATION:")), 
    new SharedStringItem( 
    new Text("Total")), 
    new SharedStringItem( 
    new Text("APPROVED:")), 
    new SharedStringItem( 
    new Text("NOTES: "){ Space = "preserve" }), 
    new SharedStringItem( 
    new Text("    SSN"){ Space = "preserve" }), 
    new SharedStringItem( 
    new Text("    Employee ID"){ Space = "preserve" }), 
    new SharedStringItem( 
    new Text("Expense report")), 
    new SharedStringItem( 
    new Text("TechEd 2009")), 
    new SharedStringItem( 
    new Text("Erika Ehrli Cabral")), 
    new SharedStringItem( 
    new Text("123456")), 
    new SharedStringItem( 
    new Text("12345678")), 
    new SharedStringItem( 
    new Text("Office Development")), 
    new SharedStringItem( 
    new Text("JPBagel")), 
    new SharedStringItem( 
    new Text("Delicious breakfast")), 
    new SharedStringItem( 
    new Text("Developer (in my dreams)")) 
                 ){ Count
    = (UInt32Value)38U, UniqueCount = (UInt32Value)37U }

    7. Modify the Main method and add a call to the CreatePackage method.


    static void Main(string[] args){ 
    // Create an Excel workbook named ExpenseReportTest.xlsx 
        // in the current folder. You can write some code here to iterate through
        // your accounting database and generate one Spreadsheet per employee.    


    8. Build and run the sample. Using the code shown above the sample application will create an Excel workbook named ExpenseReportTest.xlsx located in the Visual Studio project's Debug or Release build folder depending on the selected build mode.

    Opening the workbook with Excel will display a workbook that looks just like the ExpenseReport.xlsx workbook created previously.

    More resources

    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!

  • Erika Ehrli - Adventures with Office Products & Technologies

    Football World Cup 2006 Excel workbooks


    Have you ever thought about all the data that is stored in Microsoft Office Excel workbooks around the world? Excel workbooks are an extraordinary data store, but they also offer great potential as a resource of dynamic information that can move across data-management workflows and business processes.

    The previous paragraph was stolen from my last column. Of course, when I wrote this article I was not thinking about some other cool and interesting data that you can store in Excel workbooks. My mind is set to think about Office programs as work-related applications and I am always thinking about ways that will help developers discover cool features (such as SpreadsheetML) to solve business problems. I sometimes forget about all the fun that applications like Excel can provide.

    The 2006 Fifa World Cup is starting this month, and some people are starting to create Excel templates that can help you keep track of the results of the game matches for you.

    You can download the 2006 World Cup Tracker. An Excel template published recently on Office Online (English version).

    I am also attaching a very cool Excel workbook (Mundial 2006) that my friend Juan Balmori from Mexico shared with me. This Excel workbook helps to track game statistics and is a great option if you are looking for a template in Spanish.

    Also, Franck Halmaert, the Office PM in France, sent me this great link for a French version.

    Have fun watching the games and forget about work for a while :).

  • Erika Ehrli - Adventures with Office Products & Technologies

    Want a video blog?


    Videos are great eye candy for Web sites and blogs are the latest .com most popular content. So now imagine having them together. According to WIKIPEDIA, a blog that includes video is better known as Vlog and vlogging is the latest trend in blogging.

    The reason is simple: a lot of people love videos. It's a pleasure watching something funny or learning tricks while watching and listening to a demo. You are seeing and listening to the world and it's just taking what you can read somewhere a step further.

    Watching Webcasts, trying out Virtual Labs or downloading Screencasts will help you to reduce your learning curve for new technologies and MSDN is on top of it. The same happens with blogs. If you post code samples to your blog, you might consider trying to record a demo as a video and post it. Readers will get a better experience because they will get to see what the code is doing.

    I have been doing some research and here are some tips I want to share in case you are interested in exploring the concept.

    To start creating my own videos and I needed two things:

    1. The tools:

      I just got my license for Camtasia (and I promise nobody is paying me for the free marketing). I did my benchmark exercise to evaluate this software along with some others, and Camtasia offered the features I needed. You can consider it as a good option if you are planning to start recording demos. Of course once you record the video, you need to post it to a server so everyone can watch it.

    2. The code for embedding the videos:

      So here's the deal. You can have different video formats, such as:

    • AVI video files
    • Macromedia Flash (SWF) movie files
    • Windows Media (WMV) streaming media file
    • QuickTime (MOV) movie files
    • Custom production files


    I am interested in working with WMV files and using Windows Media Player client to embed videos into Web pages. Fortunately, there's and ActiveX control (WMPlayer.OCX) that you can use through script. Bad news is some blog sites will not allow you to run scripts and you will be limited to use HTML. One thing you can do is create an html page, host it in a different server (a lot of bloggers do the same with images) and use an IFrame to pull the video page to your blog. Here is some sample code that you can use to embed videos into HTML pages:

    <title>Render Video</title>
    <!-- This is the code you need. -->

    <script language="JavaScript">

    var WMP7;

    = new ActiveXObject("WMPlayer.OCX.7");
    else if (window.GeckoActiveXObject)
    = new GeckoActiveXObject("WMPlayer.OCX.7");

    // Windows Media Player 7 Code
    if ( WMP7 )
    document.write ('<OBJECT ID=MediaPlayer ');
    .write (' CLASSID=CLSID:6BF52A52-394A-11D3-B153-00C04F79FAA6');
    .write (' standby="Loading Microsoft Windows Media Player components..."');
    .write (' TYPE="application/x-oleobject" width="400" height="400">');
    .write ('<PARAM NAME="url" VALUE="">');
    .write ('<PARAM NAME="AutoStart" VALUE="false">');
    .write ('<PARAM NAME="ShowControls" VALUE="1">');
    .write ('<PARAM NAME="uiMode" VALUE="mini">');
    .write ('</OBJECT>');

    // Windows Media Player 6.4 Code
    //IE Code
    document.write ('<OBJECT ID=MediaPlayer ');
    .write ('CLASSID=CLSID:22d6f312-b0f6-11d0-94ab-0080c74c7e95 ');
    .write ('CODEBASE=,4,5,715 ');
    .write ('standby="Loading Microsoft Windows Media Player components..." ');
    .write ('TYPE="application/x-oleobject" width="400" height="400">');
    .write ('<PARAM NAME="FileName" VALUE="">');
    .write ('<PARAM NAME="AutoStart" VALUE="false">');
    .write ('<PARAM NAME="ShowControls" VALUE="1">');

    //Netscape code
    document.write ('    <Embed type="application/x-mplayer2"');
    .write ('        pluginspage=""');
    .write ('        filename=""');
    .write ('        src=""');
    .write ('        Name=MediaPlayer');
    .write ('        ShowControls=1');
    .write ('        ShowDisplay=1');
    .write ('        ShowStatusBar=1');
    .write ('        width=400');
    .write ('        height=400>');
    .write ('    </embed>');

    .write ('</OBJECT>');



    If you use the previous code and an IFrame, videos in your blog can look like this:

    This KB article has code samples to embed videos using VBScript or Jscript and here are some useful resources for working with the Windows Media Player object model and downloading the latest version:

    Happy Vlogging!


  • Erika Ehrli - Adventures with Office Products & Technologies

    Announcing the Open XML Format SDK 1.0


    We have great news for all Open XML developers who have waited so patiently for the fully supported release of the Open XML Format SDK 1.0. Today we published the final bits and online documentation on MSDN. You can get the SDK from the following locations:

    What is the Open XML Format SDK?

    I talked about this before in previous blog entries, but for those of you who are not familiar with this SDK, here's a brief intro.

    The Open XML Format SDK Technology Preview simplifies the task of manipulating Open XML packages. The Open XML Application Programming Interface (API) encapsulates many common tasks that developers perform on Open XML packages, so you can perform complex operations with just a few lines of code. Using this API, you can programmatically generate and manipulate Word 2007 documents, Excel 2007 spreadsheets, and PowerPoint 2007 presentations. The programming model uses managed code, so it's safe for server-side scenarios.

    The Open XML Format SDK also provides how-to articles and reference documentation that can help you get started with Open XML programming.

    What's New in the Open XML Format SDK 1.0

    The Open XML Format SDK 1.0 contains the changes described below:

    1. Renames the Microsoft.Office.DocumentFormat.OpenXml dll to DocumentFormat.OpenXml

    2. Renames the Microsoft.Office.DocumentFormat.OpenXml.Packaging namespace to DocumentFormat.OpenXml.Packaging

    3. Renames the Microsoft.Office.DocumentFormat.OpenXml namespace to DocumentFormat.OpenXml

    4. Adds support for validating by using the XmlSchemaSet object

    If you have written code using previous CTP releases, keep in mind that the first three are breaking changes. You need to delete the current reference to the Microsoft.Office.DocumentFormat.OpenXml.dll and replace it with DocumentFormat.OpenXml.dll. Also, you need to change the namespace references to DocumentFormat.OpenXml.Packaging and DocumentFormat.OpenXml.

    The last change has to do with a common ask from developers. Previous releases of the CTP allowed you to validate the contents of a part in an Open XML Package against a single schema file. However, it was not possible to validate against a collection of schemas. The Open XML Format SDK 1.0 provides an overloaded method of the ValidateXml method that allows you to validate a document part against a specific XmlSchemaSet object. The following sample code shows you how to validate the XML content of the MainDocumentPart part of a WordprocessingDocument package by calling the ValidateXml method of MainDocumentPart part. You pass a list of schemas to the ValidateXml method as an input parameter.


    ' Visual Basic
    ' How to validate the contents of a document part against
    ' a collection of schemas.
    Public Shared Sub ValidDocumentContent(document As String,
    As List (Of String))
    Dim schemas As New XmlSchemaSet()
    For Each schemaUri As String In schemaList
          schemas.Add(Nothing, schemaUri)
    Next schemaUri
       Using wordDoc
    As WordprocessingDocument =
    WordprocessingDocument.Open(document, False)
          wordDoc.MainDocumentPart.ValidateXml(schemas, Nothing)
    End Using
    End Sub

    // C#
    // How to validate the contents of a document part against
    // a collection of schemas.
    public static void ValidDocumentContent(string document,
    string> schemaList) {
       XmlSchemaSet schemas 
    = new XmlSchemaSet();
    (string schemaUri in schemaList) {
    null, schemaUri);
    using (WordprocessingDocument wordDoc 
    WordprocessingDocument.Open(document, false)) {

    Some additional improvements...

    While looking at the class diagram, I noticed that the latest version includes support for a new MailMergeRecipientDataPart. You can find detailed info about this new part here. Some other minor object model changes are documented at the readme.htm file of the download.

    Finally, we significantly improved the comments/descriptions of all the members included in the class reference documentation and Intellisense file. It's easier to learn how to use an API when the comments and member descriptions are in good shape.

    Roadmap and Development Timeline

    The Open XML API Version 1.0 is included with the Open XML Format SDK 1.0 and only contains the Open XML Packaging API. Open XML API Version 2.0 releases should contain all of the Open XML API components, including the Open XML Packaging API with further updates. In the next months, the Open XML Format SDK product team will be releasing CTPs of the Open XML API Version 2.0.

    As I mentioned earlier, the latest version has some breaking changes, so the MSDN team is also planning to update the code and content of the Open XML Format SDK related articles such as:

  • Manipulate Excel 2007 and PowerPoint 2007 Files with the Open XML Object Model (Part 1 of 2)
  • Manipulate Excel 2007 and PowerPoint 2007 Files with the Open XML Object Model (Part 2 of 2)
  • Manipulate Word 2007 Files with the Open XML Object Model (Part 1 of 3)
  • Manipulate Word 2007 Files with the Open XML Object Model (Part 2 of 3)
  • Manipulate Word 2007 Files with the Open XML Object Model (Part 3 of 3)
  • Build Server-Side Document Generation Solutions Using the Open XML Object Model (Part 1 of 2)
  • Build Server-Side Document Generation Solutions Using the Open XML Object Model (Part 2 of 2)
  • Additional Resources

    I recommend that you continue to monitor the following blogs for more news about the Open XML Format SDK:

    More code samples: site

    You can also watch Zeyad's and Eric's interview to learn more about the future of the Open XML Format SDK.

    Please continue to use the following two resources to ask questions to the product team and provide feedback :

    1. MSDN Forum: Open XML Format SDK: Use the forum to ask questions and provide suggestions. The product team is monitoring and moderating this forum.
    2. MSDN Library: Open XML Format SDK (Online version) Wiki: The online version of the SDK is Wiki enabled. This allows you to add comments and report bugs you find on documentation. To leave comments, use the "Add Community Content" link located at the bottom of each topic.


    Bookmark and Share
  • Erika Ehrli - Adventures with Office Products & Technologies

    OPML: 70 Office Development Bloggers and Counting!


    It all started with a few bloggers a little before Beta 2. People started blogging about the Ribbon, the cool ASP.NET 2.0 platform and WSS 3.0, Excel Services, the Office Open XML File Formats and InfoPath Forms Services. Lots of great features to build solutions using Office as a platform!

    Office development blogs are quite active and inviting for developers trying to learn what's new with the Microsoft Office system and diving more into specific products and technologies. When all this started, I added RSS feeds every week to Outlook until it became just a joke to have so many bloggers added to my RSS feed. There's hundreds if not thousands of people blogging about Office development and every day new blogs are born. I built a new list of Office development bloggers and a few days before launch, my list adds up to 70!

    Blog Name


    #region /* mads's thoughts */


    [XaMaLa] - Le poste de travail, un gisement de productivité encore peu exploité

    A discussion of what's new in Access 2007

    About OneNote

    Andrew May's WebLog

    Andy Simonds Blog

    Arpan Shah's Blog

    ArtLeo's WebLog

    Bill Morein's Weblog

    Brian Jones: Open XML Formats

    Building Office Business Applications

    Chris Castillo's WebLog

    Chris Pratley's OneNote Blog

    Chris's unofficial Office Live developer blog

    Clint's Access blog

    Coding for Office Small Business Accounting

    Cum Grano Salis

    doncampbell's weblog

    Doug Mahugh

    Erika Ehrli

    Evil Doctor PorkChop

    Fabulous Adventures In Coding

    frice's WebLog

    InfoPath Team Blog

    Jan Tielens' Bloggings

    Jensen Harris: An Office User Interface Blog

    Joe Friend: Microsoft Office Word

    John R. Durant's WebLog

    Julie's Blog

    Kevin Boske - Office Development

    Larry's Project Dev Blog

    LauraJ's Weblog

    Microsoft Excel 2007 (nee Excel 12)

    Microsoft Office PowerPoint

    Microsoft Office Project 2007

    Microsoft SharePoint Products and Technologies Team Blog

    Microsoft Small Business Applications

    Microsoft Visual Studio Tools for the Microsoft Office System

    Microsoft Visual Studio Tools for the Microsoft Office System, Version 2003

    MikeKelly's WebLog

    Misha Shneerson

    Murray Sargent: Math in Office

    Office Rocker!

    Office SharePoint Document Services (ECM)

    On Microsoft Publisher and more

    Outlook 12

    Owen Braun: OneNote 12

    Pashman's InfoPath Goldmine

    Patrick Smith: Office Programmability

    Patrick Tisseghem's Blog [MVP SharePoint]

    Project Programmability

    Project Server 201


    Rob Mauceri's FrontPage Blog

    Ryan's Look at Outlook Programmability

    search.subscribe.share in outlook 2007

    SGriffin's [MSFT] WebLog

    SharePoint Content and Resources

    SharePoint Products and Technologies - Pointing to Share the Knowledge

    SharePoint Unplugged

    Tasks and Time Management in Outlook

    The Microsoft Office Word Team's Blog

    The wonder that is the Microsoft Office system

    Third Of Five - just another voice in the Collective

    Visio 12 - Eric Rockey

    Westin's Technical Log

    Windows Sharepoint Services etc.

    Wouter van Vugt

    When I started posting lists of bloggers someone suggested that I should build an OPML list of Office Development Bloggers, so I did. You can download the OPML file here:

    You can use Outlook, Windows Live or other OPML readers to open this file and add your favorite RSS feeds.

    MSDN blogs: pending to add!

    How could I leave them out: Kathleen McGrath, Paul Stubbs, and the new Microsoft SharePoint Designer Team Blog

    Pranav's blog: You should check this one out, I hate me for not finding Pranav's blog before: This blog is all about Office Automation, VSTO, InfoPath and other programming stuff and the thing i believe on is "Real Stuff is Out Of The Box."

    New blogs:

    Make sure you check out Rob Barker's: Where in the "OBA" am I? and his first post.

    I am sure there's more blogs out there that I didn't list here. If you have an Office development blog or know of great bloggers that talk about VSTO, SharePoint, WSS or anything else related with Office, please let me know so we add them to the list. You don't need to have an MSDN or TechNet blog, you only need to be passionate about development with Office products and technologies. The list keeps growing every day.

    Community blogs: pending to add!


  • Erika Ehrli - Adventures with Office Products & Technologies

    2007 Microsoft Office System - Top Ten List of Resources for Developers and Architects


    Steve Ballmer announced yesterday the business availability of Vista, Office and Exchange. This means that businesses can deploy and start developing solutions that integrate these three products. You can read the executive e-mail here and you can watch the Webcast here. In case you are wondering, the consumer launch is scheduled for 1/31/2007. Microsoft also launched the Vista + Office PowerTogether site.

    So you have the products and it's always useful to find information that can help you get started. I compiled my top ten 14 list of resources for architects and solution developers that plan to start building solutions using the 2007 Microsoft Office system (including SharePoint of course) and VSTO.


    1. MSDN Office Developer Center: Since Beta 2, we have published 63 technical articles, 119 downloads (including videos), 5 white papers and 21 updates to SDKs. This a great place to hunt for developer content related to Office, SharePoint and VSTO resources. This month we published 24 content items and we update the portal every week. You can subscribe to our RSS feed here.
    2. Product and Technologies Portals: The MSDN Office Developer Center has product portals for Office programs, servers, services, tools and technologies that have a developer story. We update and add new content to the site each time we publish a related content item. So this is a good place to find code samples, videos, technical articles, channel 9 interviews, downloads, bloggers, webcasts, and virtual labs related to specific technologies.
    3. 2007 Microsoft Office System for Architects: This portal page is part of the MSDN Solution Architecture Center and provides a list of resources that are of interest for architects. Among other things, you can find information about Office Business Applications.
    4. MSDN Webcasts - The Master Office Application Development: This webcast series drills down to product features and contains demos that show how to develop solutions with different Office products and technologies. They combine detailed theory with cool demos.
    5. MSDN Virtual Labs: If you want to get your hands-on some code, this is a great place to start with. You can download the lab manuals and keep them for further reference. You can subscribe to the Office Virtual labs RSS feed here.
    6. Channel 9 Office Interviews: The Channel 9 team has defined a new level of communication between Microsoft and developers and one of the most popular content items offered on this space are video interviews. The interesting thing about these videos is that they allow us to see the human being behind the technology and learn more about the possibilities offered by a product or feature. The Channel 9 team continues to run more interviews and it's always fun to watch them.
    7. Office Developer Screencasts: If you have 10 minutes or less and you are interested in watching a demo, this is a good place to get started. Sometimes watching something in action is better than reading it somewhere.
    8. Microsoft Office System Developers Conference 2006 Videos: The Microsoft Office System Developers Conference featured more than 60 breakout sessions organized in eight technical tracks. We have had tons of hits to these videos and developers are quite interested in the Solutions and Partners track.
    9. 2007 Office System Document: Developer Posters: Office development is a huge story, we have lots of products and inside each product, tons of features, objects and namespaces. We have been working on three posters that cover some key technologies related to Office development. These posters help you understand the big picture of Office development and how products and technologies relate to each other. An image sometimes tells more than a thousand words.
    10. Office bloggers on MSDN: I am quite surprised to see all the awareness that people have for Office 2007 developer features. For previous releases of Office, developers started ramping up to new features some time after RTM was available. Thanks to blogs, developers are quite familiar with the features and have already started building solutions. I find quite useful reading Office blogs; little pieces of knowledge are stored in tons of blog entries. We have compiled a great list of Office bloggers, and we update it every now and then. Every day new blogs are born and MVPs and product team bloggers are changing the way we learn. I have no words to say how much I admire Jensen Harris for being the best Office evangelist. His blog is a MUST stop.
    11. Office Online: Even when the target audience of this site is end users, as a developer I have found tons of useful resources here. Office Online is always useful for templates, clip arts and the best help. You can even download free Office trials!
    12. Office Solution Showcase: I love this site because it "highlights how leading companies are solving critical business problems using solutions built with the Microsoft Office System." Scenarios are categorized by industry and by department. It's always useful to see what others in the industry have done.
    13. Compliance Features in the 2007 Microsoft Office System: It's the best document (I have read) about what's new for developers in the 2007 Microsoft Office system. This is THE paper you have to read if you are trying to understand new features that shipped with the 2007 Microsoft Office System and how to build solutions using them. This paper provides an overview of what you can do using: Workflow, Auditing, Digital Signatures, Content Types, Routings, Enterprise Search, Document Inspector, Excel Services, the new Office XML File Formats and more. It's focused on how to use new features for compliance, but it still provides a great overview.
    14. 10 Essential Resources for SharePoint Developers: Check out this great post by Randall Isenhour, the Microsoft Office SharePoint Server content manager. He has a great compilation of SharePoint resources for developers.

    Overwhelming? Yes, can be a lot of information if you are just entering the Office development zone. But once you start diving every site and hunting for resources you'll see it's not enough. We still have lots of code samples to build and technical articles to write. Lots of fun stuff is still scheduled in our queue.

  • Erika Ehrli - Adventures with Office Products & Technologies

    Visio Connector for Project/EPM/SPS


    You should check this out! Imtiyaz Mubarak (a developer, Microsoft Parter, and friend from India) along with Chris Castillo from the Visio team created and published a component that provides a Visio Add-In and a Project Add-In that integrates Visio 2003, Project Server and MS SharePoint Server. This component allows you to:

    • Generate Project Plans from the Visio Process Flow  Diagrams.
    • Generate Project Plans from WBS.
    • Generate SharePoint Tasks from the Visio Process Flow Diagrams.
    • Report the Status of the tasks from Microsoft Project Plan stored locally or in a Project Server.
    • Report the Status of the tasks from Microsoft SharePoint Portal Server.
    • Generate a Portfolio View in Visio from Microsoft Project Server.
    • Generate Visio Process Flow Diagram from MS Project Plan through MS Project Add-Ins.

    This component is published in GotDotNet and it’s really cool. This is the kind of stuff that solves business needs and I love that it integrates different Office technologies. I am sharing with you the link where you can download the solution along with source code and a document that explains the architecture and code.

     This is good stuff!

  • Erika Ehrli - Adventures with Office Products & Technologies

    Developer Map for the 2007 Microsoft Office System - Image Map (draft)


    We have been saying everywhere that "the Microsoft Office System is a complete set of client applications, servers, and services that provide organizations with the means to create, manage, and enhance business solutions." We have also published a full load of content for every product since Beta 2. Yet, we realize that as a developer, sometimes it's hard to understand the big picture and how all the little pieces (features and product specifics) can work together to help you build a real-world solution.

    Office is a huge family of products and technologies and we have been working with all product teams to create an overall map that helps developers to visually understand how all the pieces of the puzzle relate to each other, but more important, provide information of where on MSDN you can find technical documentation related to specific features. We want to go deep to namespaces, class members, and schemas for each product to list them all together on a single place.

    As you can imagine, space is a huge limitation. While trying to put all this together, I happen to agree with the "Microsoft 2007 Office system – It's more than you think" slogan. Office is HUGE and there's limited space to place all PIA objects, namespaces, web services, product features, schemas, names, descriptions and relationships for more than 25 products and technologies. However, we have found a great way to summarize the most relevant ideas and make it fit all together.

    I can share with you that I have found the exercise to be fascinating. Just imagine how much fun we've had having on a single meeting room PMs for all product teams + Developer Platform Evangelism + Marketing + Office Developer Documentation group and work together as a team to build the treasure map.

    We are about to conclude this project, but in the meantime, I just couldn't wait to share with you a small version of our treasure map. I built this conceptual diagram using PowerPoint 2007 and then generated an image map that provides links to places on MSDN and You can hover the map and drill-down to find detailed information for each product and technology. Of course, this is a petite version, but soon, you'll get the complete treasure map.

    So here goes...


  • Erika Ehrli - Adventures with Office Products & Technologies

    Developing Add-ins (XLLs) in Excel 2007


    If you are interested in learning about Microsoft Office Excel 2007 features that affect XLL add-ins and enable new XLL functionality, as well as changes to the XLL C API itself, we just published a great article by Steve Dalton that will help you get started.

    The intended audience for this article consists of Microsoft Visual C and Microsoft Visual C++ developers who already have experience developing Microsoft Office Excel add-ins, or XLLs. This article is not an introduction to XLL development although a brief overview is included. To make the most of this article, readers should be familiar with:

    • C and C++ language concepts and constructs. Code examples are written in C++.
    • Building DLLs that export functions.
    • The XLOPER data structure and other Excel data types, such as the floating point matrix structure (FP).
    • The add-in manager interface functions, such as xlAutoOpen and xlAutoClose.
    • XLOPER memory management (xlAutoFree, xlFree, and the use of xlbitDLLFree and xlbitXLFree).

    Read the article here:


  • Erika Ehrli - Adventures with Office Products & Technologies

    Announcing the Open XML Format SDK April CTP


    We are glad to announce that the Open XML Format SDK April CTP is available!

    You can download the new SDK from here:

    You can also find and online version of the SDK on the MSDN Library:

    Also, you can find permanent links to both the download and online version at the XML in Office Developer Portal and at the Office Open XML Formats Resource Center.

    What is the Open XML Format SDK?

    For those of you who are not familiar with this SDK, here's a brief intro.

    The Open XML Format SDK Technology Preview simplifies the task of manipulating Open XML packages. The Open XML Application Programming Interface (API) encapsulates many common tasks that developers perform on Open XML packages, so you can perform complex operations with just a few lines of code. Using this API, you can programmatically generate and manipulate Word 2007 documents, Excel 2007 spreadsheets, and PowerPoint 2007 presentations. The programming model uses managed code, so it's safe for server-side scenarios.

    The Open XML Format SDK also provides how-to articles and reference documentation that can help you get started with Open XML programming.

    What's New in the Open XML Format SDK April CTP?

    For those of you that have been working with the June 2007 CTP, here's a brief list of what's new with the April CTP:

    Product names and editorial changes

    1. The name of the SDK is "Open XML Format SDK" instead of "Microsoft SDK for Open XML Formats."

    2. We changed all references of "Open XML object model" to "Open XML Application Programming Interface (API)."

    API Changes

    1. Move AddNewPart<T>() from class OpenXmlPart to OpenXmlPartContainer class
    2. Add overload AddNewPart<T>() that receives a relationship Id parameter as input
    3. Support LINQ annotations on parts
    4. Move three document enumerations from namespace Microsoft.Office.DocumentFormat.OpenXml.Packaging to Microsoft.Office.DocumentFormat.OpenXml
    5. Naming changes for some objects

    This release also includes support for cool C# 3.0 and VB.NET 9.0 features, such as LINQ Annotations.

    If you want to see more detailed information, see What's New in the Open XML Format SDK April Technology Preview.

    Future of the SDK

    The Open XML API will release in two versions. Open XML API Version 1.0 is the updated version of the CTP in June 2007 and will only contain the Open XML Packaging API. Open XML API Version 2.0 will contain all of the Open XML API components, including the Open XML Packaging API with further updates. It will enforce validity of the content either in the original Open XML documents or being generated through this API. The purpose of this plan is to give out the long awaited Go-Live license of the existing Open XML Packaging API to external developers.

    We need your feedback

    The April 2008 CTP release has improvements based on customer feedback provided by the field and MVPs.

    For the May 2008 release of the Open XML API Version 1.0, we also plan to incorporate customer feedback to improve the SDK. If you have comments about the API or documentation, please use the following two resources:

    1. MSDN Forum: Open XML Format SDK: Use the forum to ask questions and provide suggestions. The product team is monitoring and moderating this forum.
    2. MSDN Library: Open XML Format SDK (Online version) Wiki: The online version of the SDK is Wiki enabled. This allows you to add comments and report bugs you find on documentation. To leave comments, use the "Add Community Content" link located at the bottom of each topic.

    Additional Resources

    I recommend you monitor the following blogs for more news about the Open XML Format SDK:

    More code samples: site

    You can also watch Zeyad's and Eric's interview to learn more about the future of the Open XML Format SDK.

    Have fun with the April CTP!

  • Erika Ehrli - Adventures with Office Products & Technologies

    Where in the World is all the Office 2003 Developer Content?


    The Office Developer Documentation group has published over 270 articles since the RTM of the 2007 Microsoft Office system. The team has also published 152 downloads, and 153 videos. That is a lot of content! The MSDN Office Developer Center has many portal pages that help promote these content items by product or by content type. It really is challenging to add so much content to a portal! With all that new content, one of the biggest challenges is making sure we keep promoting Office 2003 content.

    Coming from the field, I really understand that lots of companies are still using Office 2003, and as a developer, you may be faced with the need to find Office 2003 developer resources. The MSDN Office Developer Center has multiple Office 2003 resource pages organized by product and by content type. I thought you might enjoy having an interactive site map with links to all Office 2003 content resource centers on MSDN, so I built one for you:

    Office 2003 Technical ArticlesOffice 2003 Code SamplesOffice 2003 SDK and ReferenceOffice 2003 Tools and UtilitiesSharePoint Portal 2003Access 2003 Resource CenterAccess 2003 SupportAccess 2003 Developer ExtensionsExcel 2003 Resource CenterExcel 2003 SupportFrontPage 2003FrontPage 2003 SupportInfoPath 2003 Resource CenterInfoPath 2003 SupportOneNote 2003 Resource CenterOneNote 2003 SupportOutlook 2003 Resource CenterOutlook 2003 SupportPowerPoint 2003 Resource CenterPowerPoint 2003 SupportPublisher 2003 Resource CenterPublisher 2003 SupportVisio 2003 Resource CenterVisio 2003 SupportWord 2003 Resource CenterWord 2003 Support

    I almost forget about Project 2003! Here are the links:

    Speaking of interactive maps, you should check out the new interactive map with free Christmas images build by the Office Online Clip Art team.

    Another cool blog to check out is the new Microsoft SharePoint Developer Documentation team blog.


  • Erika Ehrli - Adventures with Office Products & Technologies

    A common business need: Generating server-side documents on the fly


    I gathered a list of common Open XML questions related to programmability:

    1. What are the Open XML File Formats and what can I do with them?
    2. Can you show me the internal structure of a Word 2007 document?
    3. What are WordprocessingML, SpreadsheetML, PresentationML, and DrawingML?
    4. Do you have a .NET API that I can use to generate documents programmatically (server-side)?
    5. What is the architecture of a server-side OBA document generation solution?
    6. How can I generate a document programmatically and have more control over document content?
    7. How do you add images to an Open XML document?
    8. How can I pull data from my data source and create a table in a document?
    9. How can I add styles and format to my document content?
    10. What about compatibility with previous versions of Office?

    Office Business Applications + Open XML File Formats

    When you are trying to create a document assembly solution and you are want to understand how you can use the Open XML File Formats to generate a document programmatically, you may be faced to some of the previous questions. All this questions have been answered in multiple MSDN articles, SDKs, blogs, trainings, forums, and newsgroups. However, I am the kind of person that loves end-to-end documentation and code samples that take you from zero to a working solution. We all have limited time to learn new technologies and walkthrough articles and code sample downloads are always a nice option.

    Some time ago I tried to do the same thing and I blogged to show you how to generate a document using a document template, content controls, and XML mapping. I also created a little video and article that shows how to bind custom xml to a document template. This approach is great when you are trying to replace placeholder data in document templates like an invoice or contract. However, your business needs may be different and you may want to have more control over document content and formatting. In that case a better approach would be to manipulate the WordProcessingML content stored in different document parts.

    I wrote a new article that helps answer the Open XML questions listed in this blog entry. I split the article in two parts and a code sample download. I start by discussing all the theory and basic concepts you need to learn to work with the Open XML File Formats. For example, I talk about Open XML Package Architecture, WordprocessingML basics, the Open XML object model, and the conceptual architecture of a document integration solution.

    The second part explains all the coding that needs to happen to generate a simple sales document from scratch. I show you how to deal with images, tables, styles, and formatting. I also show how to create a helper class that pulls data from your line of business systems (in this case the AdventureWorks sample database to keep the LOB piece as simple as possible), and a helper class that uses the Open XML object model and WordprocessingML to create a document.

    You can find the articles and code samples here:

    Many thanks go to Doug Mahugh, Wouter van Vugt, and Frank Rice for sharing all their knowledge and helping me put this together. I hope this helps you get started with custom document generation with Open XML.


  • Page 1 of 8 (188 items) 12345»