August, 2006

From the Source Force to you…

This is pretty cool. If you are interested in testing and playing around with the new 2007 Microsoft Office system client apps or Microsoft Office SharePoint Server 2007 without dealing with setup and installation, you might be interested in trying out the new 2007 Office System Virtual Labs.

These are the latest ones…

• Creating Workflows for Windows SharePoint Services 
• Introducing Content Types for Windows SharePoint Services 3.0 
• Understanding the 2007 Microsoft Office System User Experience and Interface Design
• Programmatic Manipulation of the Microsoft Office Open XML Formats

I already played around with the last one and it’s pretty neat. You get to work with a virtual machine and you can also download a lab manual with code that you can reuse later :).
 
You can subscribe to the RSS feed or checkout the MSDN Virtual Labs page to see the latest ones.

Enjoy,
-Erika

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
http://www.microsoft.com/france/msdn/office/default.mspx

Blogs:

• Laurent Ellerbach
  http://blogs.msdn.com/laurelle/
  http://thespoke.net/blogs/laurelle/default.aspx
• Groove blog:
  http://hommesetprocess.typepad.com/weblog_hommes_process/2006/07/groove_en_frana.html
• Franck Halmaert
  http://blogs.microsoft.fr/franckha/
  
• Christophe Lauer
  http://blogs.microsoft.fr/clauer/category/277.aspx

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
http://www.microsoft.com/italy/msdn/risorsemsdn/office/default.mspx

Community italiane

• http://www.ugisharepoint.it/
• http://www.shareoffice.it/
• http://www.microsoft.com/italy/msdn/risorsemsdn/community/elenco.mspx

Blogs:

• Fulvio Giaccari: http://blog.shareoffice.it/webmaster/
• Romeo Pruno: http://blogs.devleap.com/romeopruno/default.aspx
• Igor Macori: http://blogs.devleap.com/igor/default.aspx

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
http://www.microsoft.com/germany/msdn/office/default.mspx

Blogs:

• Thomas Dreller: http://blogs.msdn.com/diplo71/

Entwickler Communities:

• http://www.microsoft.com/germany/msdn/community/blogs.mspx

Japanese

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

MSDN:
http://www.microsoft.com/japan/msdn/office/

Office Online:
http://office.microsoft.com/ja-jp/default.aspx

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: http://www.microsoft.com/spanish/msdn/articulos/architectema/tema/prog_off.mspx

MSDN
http://www.microsoft.com/spanish/msdn/centro_recursos/office/default.mspx

Comunidad Office System

http://groups.msn.com/comunidadofficesystem/desarrollo1.msnw

Office Online
http://office.microsoft.com/es-es/default.aspx

Blogs:

• El Bruno
  http://brunocapuano.spaces.live.com/
• Luis Du Solier (desde Mexico :))
  http://sharepointmx.mvps.org/blogs/ldusolier/

Por favor lean este artículo de Luis acerca de colaboración con SharePoint:
http://sharepointmx.mvps.org/blogs/ldusolier/archive/2006/06/20/23.aspx

Other worldwide resources:

You can find more worldwide sites and international DLs here:

International Discussion Groups:
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx

MSDN Worldwide Site
http://msdn2.microsoft.com/en-us/worldwide/default.aspx

Office Online Worldwide sites:
http://office.microsoft.com/en-us/worldwide.aspx

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 :).

Enjoy!
~Erika

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:
   <connectionStrings>
    <add name="NorthwindConnectionString"
        connectionString="data source=(local);database=Northwind; integrated security=SSPI;persist security info=false;"
        providerName="System.Data.SqlClient" />
   </connectionStrings>
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">
         <assemblies>
           <add assembly="WindowsBase, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
      </assemblies>
    </compilation>
   
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.

[VB.NET]

<%@ 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" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Data-Driven Document Generation - SQL Server Sample</title>
</head>

<script runat='server'>
    
    Private Const strRelRoot As String = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument"
    
    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)
        buffer = Nothing
        
        ' 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
                    pkgFile.DeletePart(uriData)
                End If
                
                ' Load the custom XML data
                Dim pkgprtData As PackagePart = pkgFile.CreatePart(uriData, "application/xml")
                GetDataFromSQLServer(pkgprtData.GetStream, ddlCustomer.SelectedValue)
            End If
        Next
        
        ' Close the file
        pkgFile.Close()
        
        ' Return the result
        Response.ClearContent()
        Response.ClearHeaders()
        Response.AddHeader("content-disposition", "attachment; filename=document.docx")
        Response.ContentEncoding = System.Text.Encoding.UTF8
        memoryStream.WriteTo(Response.OutputStream)
        memoryStream.Close()
        Response.End()
    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)
        conn.Open()
        Dim cmd As SqlCommand = New SqlCommand(SqlStatement, conn)
        cmd.Parameters.AddWithValue("@customerID", customerID)
        Dim dr As SqlDataReader = cmd.ExecuteReader
        If dr.Read Then
            Dim writer As XmlWriter = XmlWriter.Create(stream)
            writer.WriteStartElement("Customer")
            writer.WriteElementString("CompanyName", CType(dr("CompanyName"), String))
            writer.WriteElementString("ContactName", CType(dr("ContactName"), String))
            writer.WriteElementString("ContactTitle", CType(dr("ContactTitle"), String))
            writer.WriteElementString("Phone", CType(dr("Phone"), String))
            writer.WriteEndElement()
            writer.Close()
        End If
        dr.Close()
        conn.Close()
    End Sub
    
    Protected Sub SubmitBtn_Click(ByVal sender As Object, ByVal e As EventArgs)
        CreateDocument()
    End Sub
</script>

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

[C#]

<%@ 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" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Data-Driven Document Generation - SQL Server Sample</title>
</head>
    <script language="C#" runat="server">
        
        private const string strRelRoot = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument";

        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);
            foreach (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);

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

            // Close the file
            pkgFile.Close();

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

            memoryStream.WriteTo(Response.OutputStream);

            memoryStream.Close();

            Response.End();
        }

        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";

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

                if (dr.Read()) {
                    XmlWriter writer = XmlWriter.Create(stream);
                    writer.WriteStartElement("Customer");
                    writer.WriteElementString("CompanyName", (string)dr["CompanyName"]);
                    writer.WriteElementString("ContactName", (string)dr["ContactName"]);
                    writer.WriteElementString("ContactTitle", (string)dr["ContactTitle"]);
                    writer.WriteElementString("Phone", (string)dr["Phone"]);
                    writer.WriteEndElement();
                    writer.Close();
                }
                dr.Close();
                conn.Close();
            }
        }

        protected void SubmitBtn_Click(object sender, EventArgs e) {
            CreateDocument();
        }
        
    </script>

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

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.

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: 

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.

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.

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:

http://msdn2.microsoft.com/en-us/library/ms406053.aspx

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.

Enjoy,

-Erika