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 Export Data to Excel from an ASP.NET Application + Avoid the File Format Differ Prompt

    • 51 Comments

    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" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head id="Head1" runat="server">
        
    <title>Export to Excel - GridView Sample</title>
    </head>

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

        
    protected void btnExportToExcel_Click(object sender, EventArgs e) {
            ExportToExcel()
    ;
        
    }
        
        
    //Export to Excel from a GridView
        
    protected void ExportToExcel() {
            Response.Clear()
    ;
            
    Response.Buffer = true;
            
    Response.ContentType "application/vnd.ms-excel";
            
    Response.AddHeader("content-disposition""attachment;filename=MyFiles.xls");
            
    Response.Charset "";
            this
    .EnableViewState = false;

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

            
    gvFiles.RenderControl(htw);

            
    Response.Write(sw.ToString());
            
    Response.End();
        
    }

        
    protected void Page_Load(object sender, EventArgs e) {
            BindFiles()
    ;
        
    }

        
    public override void VerifyRenderingInServerForm(Control control) {
        }

    </script>

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

    Happy Friday!

  • Erika Ehrli - Adventures with Office Products & Technologies

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

    • 62 Comments

    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:

    Subject
    Author
    Manager
    Company
    Category
    Keywords
    Comments
    Hyperlink base

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

    Subject0
    Author0
    Manager0
    Company0
    Category0
    Keywords0
    Comments0

    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

    How-to enable the Developer tab in the Ribbon bar

    • 10 Comments

    I am a new user to Office 2007 (Beta 2) and I am having fun discovering things around. If you already downloaded the 2007 Microsoft Office system Beta 2 and you are ready to start experimenting with Office, here is a nice trick related to the very cool Ribbon bar.

    The Ribbon provides a Developer tab that groups a set of commands related to Office programmability and extensibility. The following figure shows the Developer Ribbon (Word 2007).

    Developer tab in the Ribbon UI

    By default, the Developer tab is disabled. Why? My personal guess: Regular end-users don't know about Office programmability and extensibility, so they might never use the Developer tab. On the other hand, developers are a different set of users that have a completely distinct way of working with programs, solving problems, and discovering things. Developers have a great research spirit and will easily find how-to enable the Developer tab. Here's how...

    To show the Developer tab

    1. Open a 2007 Office System application, for example Word 2007.
    2. Click the Microsoft Office Button, and then click Word Options.
    3. In the Top options for working with Word section of the Word Options dialog box, click Personalize.
    4. Select Show Developer tab in the Ribbon, as shown in the next figure.
    5. Click OK.

      The Word Options dialog box

      Note   When you are in developer mode, you see the Developer tab in the Ribbon UI.

    Once you get this running and start playing around, the next step is to start researching about extending the Ribbon. You can extend the Ribbon to show custom tabs and command chunks. The new Ribbon page inside MSDN contains great resources (blogs, videos, articles and downloads) that will allow you to get started with extending the Ribbon.

    Don't miss Frank's articles and blog, he is a great writer and will be sharing more on extending the Ribbon with you guys.

  • Have fun with the Beta,

    -Erika

  • Erika Ehrli - Adventures with Office Products & Technologies

    How to programmatically generate Microsoft Excel AutoFiltered Lists with C#

    • 41 Comments

    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;
        using 
    System.IO;
        using 
    Excel Microsoft.Office.Interop.Excel;

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

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

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

            
    public void DocumentDirectory(DirectoryInfo di) {
                
    foreach (DirectoryInfo d in di.GetDirectories()) {
                    DocumentDirectory(d)
    ;
                
    }
                
                
    foreach (FileInfo f in di.GetFiles()) {
                    _row++
    ;
                    
    _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";
                string 
    heading1 "File Analysis Report for Folder " + _di.FullName;
                
    _book _eh.Create(caption, heading1);
                
    _sheet ((Excel.Worksheet)_book.ActiveSheet);
                
    WriteTableHeader();
                
    DocumentDirectory(_di);
                
    SetAutoFilter();
                
    _eh.Close();
            
    }

            
    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 
    ((Excel.Worksheet)_book.ActiveSheet).get_Range("A4""H3");
                
    _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;
            
            public 
    ExcelHelper() {
                _excelApplication 
    = new Excel.Application();
            
    }

            
    public Excel.Workbook Create(string caption, string heading1) {
                
    try {
                    _excelApplication.Caption 
    caption;
                    
    _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;

                    return 
    book;

                
    }
                
    catch (Exception ex) {
                    
    throw (ex);
                
    }
            }

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

                if 
    (_excelApplication != null) {
                    _excelApplication.Quit()
    ;
                    
    _excelApplication = null;
                    
    GC.Collect();
                    
    GC.WaitForPendingFinalizers();
                    
    GC.Collect();
                    
    GC.WaitForPendingFinalizers();
                
    }
            }
        }
    }

    Run this application and give it a try!

  • Erika Ehrli - Adventures with Office Products & Technologies

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

    • 48 Comments

    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"?>
    <Customer>
      
    <CompanyName>Alfreds Futterkiste</CompanyName>
      
    <ContactName>Maria Anders</ContactName>
      
    <ContactTitle>Sales Representative</ContactTitle>
      
    <Phone>030-0074321</Phone>
    </Customer>

    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
       
    ActiveDocument.CustomXMLParts.Add
       ActiveDocument.CustomXMLParts(
    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
    strXPath1 
    "/Customer/CompanyName"
    ActiveDocument.ContentControls(1).XMLMapping.SetMapping strXPath1

    Dim strXPath2 As String
    strXPath2 
    "/Customer/ContactName"
    ActiveDocument.ContentControls(2).XMLMapping.SetMapping strXPath2

    Dim strXPath3 As String
    strXPath3 
    "/Customer/ContactTitle"
    ActiveDocument.ContentControls(3).XMLMapping.SetMapping strXPath3

    Dim strXPath4 As String
    strXPath4 
    "/Customer/Phone"
    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:

    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

  • Erika Ehrli - Adventures with Office Products & Technologies

    How to indent an XML file or document

    • 26 Comments

    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:

      [VB.NET]
      Dim writer as XmlTextWriter = new XmlTextWriter("data.xml",nothing)
      writer.Formatting 
      Formatting.Indented

      [C#]
      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:

      [VB.NET]
      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)
      writer.Formatting 
      Formatting.Indented
      doc.Save(writer)


      [C#]
      XmlDocument doc = new XmlDocument();
      doc.LoadXml("<item><name>wrench</name></item>");
      // Save the document to a file and auto-indent the output.
      XmlTextWriter writer = new XmlTextWriter("data.xml",null);
      writer.Formatting Formatting.Indented;
      doc.Save(writer);

    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="http://www.w3.org/1999/XSL/Transform">
        
      <xsl:output method="xml" omit-xml-declaration="no" indent="yes" encoding="US-ASCII"/>

    Happy Office XML programming!

  • Erika Ehrli - Adventures with Office Products & Technologies

    Open XML Format SDK 2.0: Getting Started Best Practices

    • 20 Comments

    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:

    expenseRTemplate[1]

    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.

    docReflector[1]

    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.

    [C#]

    using DocumentFormat.OpenXml.Packaging
    using
    ap = DocumentFormat.OpenXml.ExtendedProperties
    using
    vt = DocumentFormat.OpenXml.VariantTypes
    using
    DocumentFormat.OpenXml
    using
    DocumentFormat.OpenXml.Spreadsheet
    using
    a = DocumentFormat.OpenXml.Drawing
    using
    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.

    [C#]

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

       

    }

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

    [C#]

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

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

              
    var workbookPart1 = parent.AddWorkbookPart()
              
    GenerateWorkbookPart1().Save(workbookPart1)

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

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

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

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

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

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

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

          

    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.

    [C#]
    private static SharedStringTable GenerateSharedStringTablePart1() { 
             var element

                 new
    SharedStringTable( 
                    
    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 }
             return
    element
        

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

    [C#]

    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.    

    CreatePackage("ExpenseReportTest.xlsx")
    }


    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

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

    • 69 Comments

    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.

    Prerequisites

    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;
      using 
      System.Net;
      using 
      System.IO;
      using 
      System.Configuration;
      using 
      System.Text;
      using 
      System.Web;
      using 
      System.Security;
      using 
      System.Web.Security;
      using 
      System.Web.UI;
      using 
      System.Web.UI.WebControls;
      using 
      System.Web.UI.WebControls.WebParts;
      using 
      System.Web.UI.HtmlControls;
      using 
      DocumentLoader;

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

         public 
      SharePointUploadHelper() {
            _sharepointDocumentLibrary 
      ConfigurationManager.AppSettings["SharePointDocLibrary"];
         
      }

         
      public string UploadDocumentsToSharePoint(string fileName) {

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

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

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

            return 
      (result);
         
      }
      }
    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();
              string 
      serverTempFilePath Server.MapPath(@"/yourApplication");
              
      contentFileUpload.PostedFile.SaveAs(serverTempFilePath);
              
      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.

    Enjoy!

    -Erika

  • Erika Ehrli - Adventures with Office Products & Technologies

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

    • 49 Comments

    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 ObjectByVal 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.


    Enjoy!
    ~Erika
  • Erika Ehrli - Adventures with Office Products & Technologies

    Office developer resources in your own language

    • 29 Comments

    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:

    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

    Blogs:

    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:

    Entwickler Communities:

     

    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:

    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

  • Erika Ehrli - Adventures with Office Products & Technologies

    Hosting Office in .NET applications

    • 41 Comments

    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

    Developing Add-ins (XLLs) in Excel 2007

    • 13 Comments

    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: http://msdn2.microsoft.com/en-us/library/aa730920.aspx

    Enjoy!
    ~Erika

  • Erika Ehrli - Adventures with Office Products & Technologies

    Extracting Microsoft Office Application Properties without automation

    • 56 Comments

    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:

    http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q303296&

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

    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);
              
      FileUpload1.PostedFile.SaveAs(serverTempFilePath);

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

              
      oleDocument.Open(serverTempFilePath,
                      
      true,
                      DSOFile.dsoFileOpenOptions.dsoOptionOpenReadOnlyIfNoWriteAccess)
      ;

              
      // 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
              
      oleDocument.Close(false);
          
      }
      </script>

      <html xmlns="http://www.w3.org/1999/xhtml">
      <head runat="server">
          
      <title>DSOFileDemo</title>
      </head>
      <body>
          
      <form id="form1" runat="server">
              
      <div>
                  
      <strong>
                  DSOFileDemo
      </strong><br />
                  <
      br />
                  <
      table border="1">
                      
      <tr>
                          
      <td valign="top">
                              File upload:
      </td>
                          
      <td>
                              
      <asp:FileUpload ID="FileUpload1" runat="server" />
                              <
      asp:Button ID="btnLoadFile" runat="server" OnClick="btnLoadFile_Click" Text="Load File Properties" /><br />
                          </
      td>
                      
      </tr>
                      
      <tr>
                          
      <td>
                              Title:
      </td>
                          
      <td>
                              
      <asp:TextBox ID="tbTitle" runat="server"></asp:TextBox> 
                          
      </td>
                      
      </tr>
                      
      <tr>
                          
      <td>
                              Author:
      </td>
                          
      <td>
                              
      <asp:TextBox ID="tbAuthors" runat="server"></asp:TextBox> 
                          
      </td>
                      
      </tr>
                      
      <tr>
                          
      <td>
                              Company:
      </td>
                          
      <td>
                              
      <asp:TextBox ID="tbCompany" runat="server"></asp:TextBox> 
                          
      </td>
                      
      </tr>
                      
      <tr>
                          
      <td>
                              Number of Pages:
      </td>
                          
      <td>
                              
      <asp:TextBox ID="tbNumPages" runat="server"></asp:TextBox></td>
                      
      </tr>
                      
      <tr>
                          
      <td>
                              Word count:
      </td>
                          
      <td>
                              
      <asp:TextBox ID="tbWordCount" runat="server"></asp:TextBox> 
                          
      </td>
                      
      </tr>
                  
      </table>
              
      </div>
          
      </form>
      </body>
      </html>

    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

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

    • 28 Comments

    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

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

    • 21 Comments

    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 http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument 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

    Want a video blog?

    • 16 Comments

    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:

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

        
    <script language="JavaScript">

    var WMP7;

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

    // Windows Media Player 7 Code
    if ( WMP7 )
    {
         
    document.write ('<OBJECT ID=MediaPlayer ');
         document
    .write (' CLASSID=CLSID:6BF52A52-394A-11D3-B153-00C04F79FAA6');
         document
    .write (' standby="Loading Microsoft Windows Media Player components..."');
         document
    .write (' TYPE="application/x-oleobject" width="400" height="400">');
         document
    .write ('<PARAM NAME="url" VALUE="http://download.microsoft.com/download/5/7/1/57139364-d7af-4c18-9ce3-5149a3a727ca/EC304_Friend.wmv">');
         document
    .write ('<PARAM NAME="AutoStart" VALUE="false">');
         document
    .write ('<PARAM NAME="ShowControls" VALUE="1">');
         document
    .write ('<PARAM NAME="uiMode" VALUE="mini">');
         document
    .write ('</OBJECT>');
    }

    // Windows Media Player 6.4 Code
    else
    {
         
    //IE Code
         
    document.write ('<OBJECT ID=MediaPlayer ');
         document
    .write ('CLASSID=CLSID:22d6f312-b0f6-11d0-94ab-0080c74c7e95 ');
         document
    .write ('CODEBASE=http://activex.microsoft.com/activex/controls/mplayer/en/nsmp2inf.cab#Version=6,4,5,715 ');
         document
    .write ('standby="Loading Microsoft Windows Media Player components..." ');
         document
    .write ('TYPE="application/x-oleobject" width="400" height="400">');
         document
    .write ('<PARAM NAME="FileName" VALUE="http://download.microsoft.com/download/5/7/1/57139364-d7af-4c18-9ce3-5149a3a727ca/EC304_Friend.wmv">');
         document
    .write ('<PARAM NAME="AutoStart" VALUE="false">');
         document
    .write ('<PARAM NAME="ShowControls" VALUE="1">');

         
    //Netscape code
         
    document.write ('    <Embed type="application/x-mplayer2"');
         document
    .write ('        pluginspage="http://www.microsoft.com/windows/windowsmedia/"');
         document
    .write ('        filename="http://go.microsoft.com/?linkid=5519355"');
         document
    .write ('        src="http://download.microsoft.com/download/5/7/1/57139364-d7af-4c18-9ce3-5149a3a727ca/EC304_Friend.wmv"');
         document
    .write ('        Name=MediaPlayer');
         document
    .write ('        ShowControls=1');
         document
    .write ('        ShowDisplay=1');
         document
    .write ('        ShowStatusBar=1');
         document
    .write ('        width=400');
         document
    .write ('        height=400>');
         document
    .write ('    </embed>');

         document
    .write ('</OBJECT>');
    }

        
    </script>

    </body>
    </html>

    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

  • Erika Ehrli - Adventures with Office Products & Technologies

    How to Upload Files to SharePoint Server 2007 from ASP.NET Web Applications

    • 6 Comments

    Some time ago I  blogged about How to: Uploading a File to a SharePoint Library Site from a Local Folder and How to: Magic with SharePoint 2003, uploading files using a Web service. Some people found these samples useful and some people asked if I could provide sample code that works with SharePoint Server 2007.

    For some time I asked around and realized that there are multiple possible ways to accomplish this task in SharePoint 2007. Here are some popular approaches I’ve learned about and some great possibilities and drawbacks for each:

    • Copy Web service (Copy.asmx): The Copy Web service provides services for copying files within a SharePoint site and between SharePoint sites. Some people have created great samples that show you how to use this Web service to “upload” files while having some control over content types and metadata. This approach actually copies content from one SharePoint site to another, which is great, but may not be the best option if you want to upload files from your local drive to a SharePoint site.
    • Imaging Web Service (Imaging.asmx): This Web service provides methods that enable you to create and manage picture libraries. This WS has an upload method that allows you to upload different files (including non-image files) to SharePoint Image libraries (only) on the current Web site.  However, you can’t use this Web service to upload files to a regular document library (Shared Documents).
    • HTTP PUT method: There’s a third option that allows you to upload files (any file type) from your local drive to a predefined SharePoint list by using the HTTP PUT method. This method is simple and you don’t need to create SharePoint code to upload files. However, for the same reason, you won’t have programmatic control over file metadata on SharePoint lists. It’s super useful though, and I believe this is the best possible way (that I know of so far) to upload files to SharePoint Server 2007.

    For this last approach, Joel Krist has created a new Office Visual How-to that shows how to use the HTTP PUT method to programmatically upload files from an ASP.NET Web application to a Microsoft Office SharePoint Server 2007 site: Uploading Files to SharePoint Server 2007 from ASP.NET Web Applications by Using the HTTP PUT Method. This article provides code samples in both VB and C# and a video that shows how to this.

    Here’s a preview of the video from that same article.

    Enjoy!

  • Erika Ehrli - Adventures with Office Products & Technologies

    What’s New in Office 2010 and SharePoint 2010 (Technical Preview post)

    • 7 Comments

    I was out of town and had to miss the excitement of blogging about the launch of the Office Technical Preview yesterday. Lucky me, today I have tons of links and trackbacks to other blog posts to share!

    What’s New in Office 2010 and SharePoint 2010?

    Yesterday, Stephen Elop announced the technical preview milestone for Microsoft Office 2010 at the WorldWide Partner Conference.

    From the Press Pass:

    Office 2010 and related products will deliver innovative capabilities and provide new levels of flexibility and choice that will help people:

    • Work anywhere with Office Web applications — the lightweight Web browser versions of Word, PowerPoint, Excel and OneNote — that provide access to documents from virtually anywhere and preserve the look and feel of a document regardless of device.
    • Collaborate better with co-authoring in Microsoft Word 2010, Microsoft PowerPoint 2010 and Microsoft OneNote 2010, and advanced e-mail management and calendaring capabilities in Microsoft Outlook 2010, including the option for users to ”ignore” unwanted threads.
    • Bring ideas to life with video and picture editing, broadcast capability in Microsoft PowerPoint 2010, easy document preparation through the new Microsoft Office Backstage view, and new Sparklines in Microsoft Excel 2010 to visualize data and spot trends more quickly.

    Read more here: http://www.microsoft.com/presspass/press/2009/jul09/07-13Office2010WPCPR.mspx

    What’s new in….

    What’s new for developers in Office 2010 and SharePoint 2010 (TP)?

    Office 2010 Roadmap (TP)
    Tools productivity UI Platform Application Extensibility
    • Fluent UI (Ribbon) support added to more products
    • Backstage view and context menu programmability support
    • Excel “slicers” and “sparklines” extensibility
    • Word, OneNote, PowerPoint, Visio, and Outlook enriched APIs.
    • Improved Outlook object model 

    Read more details at John Durant’s great post.

    SharePoint 2010 Roadmap (TP):
    • Visual Studio 2010 SharePoint tool
    • Language Integrated Query to SharePoint
    • Developer Dashboard
    • Business Connectivity Services
    • Client Object Model
    • Silverlight Web Part

    Read more details at Paul Andrew’s post and at the Developer Sneak peek video:

    Office and SharePoint development blogs:

    More links and resources:

    Availability to the Technical Preview program is “by invitation only” and is not broadly available to the public.  There is a waitlist you can sign up for, found here.

    I’ll keep updating this blog entry as I hear more.

  • Erika Ehrli - Adventures with Office Products & Technologies

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

    • 15 Comments

    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

    Football World Cup 2006 Excel workbooks

    • 16 Comments

    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.
    http://blogs.microsoft.fr/franckha/archive/2005/12/26/16932.aspx

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

  • Erika Ehrli - Adventures with Office Products & Technologies

    Office Business Applications: What is it, and how can I get started?

    • 9 Comments

    I’ve had this topic in my blog wish-list for a while and today is the day to talk about this. I have been listening the term “OBA” so I decided to research a bit on the topic to understand what is it and put together a list of resources that can help developers and architects get started. I realize some of you might already know about this, but I know some people are not familiar with this technology, so here goes a quick intro for all of you who want to know what is it and how to get started.

    What is it?
    Office Business Applications are a new category of business applications that connect line-of-business systems and processes with the people that use them through a familiar user interface: Microsoft Office. Basically, you have back-end systems (data access layer) and you can build a middle-tier of business logic to connect into business processes in line-of-business applications such as Enterprise Resource Planning (ERP), Customer Relationship Management (CRM), and Supply Chain Management (SCM), this is your business-logic layer. We are still missing the presentation layer. Instead of building a Web application, Windows client application, or use a specialized CRM UI, you use Microsoft Office programs as the presentation layer and let users interact and run business processes while using the programs that are already running. Finally, you can also integrate to SharePoint sites that use Web Parts to report data.

     

    I stole this little diagram from an MSDN article and I recommend reading Atanu's article: Building Better Business Applications Using the 2007 Microsoft Office System if you are interested in learning more about the architecture of OBA solutions.

    So why use Office as your presentation layer? One of the advantages of using Microsoft Office as a front-end to work on business processes is that it’s easier for users to work with an application that is already open. No need to have a context-switch between Office and a specialized CRM UI. Anyway, you have an open window running Outlook all day…

    Customers are building OBAs using Office 2003, SharePoint Portal Server 2003, and Visual Studio Tools for Office, so technically, you don’t have to use Office 2007 for an OBA. However, the 2007 Microsoft Office system, Office SharePoint Server 2007 and VSTO 2005 SE provide built-in features (such as workflow, Excel services, Ribbon extensibility, InfoPath Web forms, the Business Data Catalog, the Office Open XML File Formats and more) that help you get there with less code. Let’s just say it’s easier to build.

    How to get started?
    A good thing to know is that we released a couple of weeks ago the Reference Application Pack for Supply Change Management that will help you ramp-up.

    The OBA RAP for Supply Chain Management includes a reference application with scenarios for collaboration between the different levels of a multi-tier supply chain starting with a retailer, and then extending back through a manufacturer and a tier-1 supplier.  In addition to the application and associated templates and scripts, it includes a white paper outlining the architecture and implementation, and screen-capture demos.   The OBA RAP for Supply Chain Management is available for download at http://www.microsoft.com/downloads/details.aspx?familyid=35409d4f-6d3d-4c1c-9390-cc0d70422ad6&displaylang=en
     

    What are the pre-requisites?  What do I need to run the reference app?
    To use the OBA RAP for SCM, you need to have the following:
    • Windows Server 2003
    • Microsoft Office SharePoint Server 2007
    • Microsoft Office 2007
    • WinFX runtime June CTP
    • Visual Studio 2005
    • WinFX SDK June CTP
    • Visual Studio extensions for WF 2.2 & WinFX
    • VSTO “3”
    • SQL Server 2005
    • Active Directory
     

    The package includes:
    • A reference solution and a reference implementation for building Office Business Applications using Office 2007
    • An OBA for Supply Chain Management, with scenarios for collaboration between the different levels of a multi-tier supply chain starting with a retailer, and then extending back through a manufacturer and a tier-1 supplier
    • Downloadable bits, including:
    • Demo websites and site templates
    • Supporting documents (.doc, .xls) for reference scenarios
    • .NET Assemblies (workflows, utilities, activity libraries)
    • Web services (.asmx)
    • Reference data
    • XSLT files
    • BDC XML files
    • Scripts
    • White paper on how to architect and build a supply chain OBA
    • Screen-capture demos

    OBA: useful resources...

    • Channel 9: Javed Sikander: Office 2007- Office Business Application
    • Javed Sikander: OBA blog: Talk to Javed if you want to learn more about OBA.
    • OBA RAP for Supply Chain Management
    • Office Business Applications Developer Portal

    Enjoy!
    ~Erika

  • Erika Ehrli - Adventures with Office Products & Technologies

    NEW must-read book for Microsoft Office Project VBA programmers!

    • 6 Comments

    Some time ago I blogged about Project Developer Resources and I want to add a new resource to my list.

    Rod Gill (Project MVP) just contacted me to let me know about his new book! He compiled a lot of VBA code samples for working with Project (98-2007), so if you are a Project developer, you might consider getting this book:

    VBA Programming for Microsoft Office Project 1998-200X applies to all Micrsoft Project versions through Microsoft Office Project 2007 professional and standard editions with an introduction to new features in the 2007 version. MVP author Rod Gill brings his years of expertise into this first of a kind offering focusing exclusively on VBA programming for Project. Not only is this a must have for users who hope to maximize their Project client value through VBA programming and macros, it's a wonderful get started guide for Office VBA programming in general. Learn from one of the world's most reknown experts on Project VBA!

    You can find more information about this book here:

    http://www.projectvbabook.com/

  • Erika Ehrli - Adventures with Office Products & Technologies

    Office "12" bloggers list

    • 21 Comments

    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 http://blogs.msdn.com/access/ http://blogs.msdn.com/access/rss.aspx
    Document Services (ECM) Rob Lefferts Office SharePoint Document Services (ECM) http://blogs.msdn.com/roble/ http://blogs.msdn.com/roble/rss.aspx
    Excel 12 Dave Gainer Microsoft Excel 12 http://blogs.msdn.com/excel/ http://blogs.msdn.com/excel/rss.aspx
    FrontPage 12 Rob Mauceri Rob Mauceri's FrontPage Blog http://blogs.msdn.com/rmauceri/ http://blogs.msdn.com/rmauceri/rss.aspx
    Groove Mark Olson Marc's blog about the integration of Groove into Office 12. http://blogs.msdn.com/marco/ http://blogs.msdn.com/marco/rss.aspx
    InfoPath 12 Tudor Toma InfoPath 12 - Tudor Toma http://blogs.msdn.com/tudort/ http://blogs.msdn.com/tudort/rss.aspx
    Office 12 (General) Erika Ehrli Adventures in Office Development and .NET http://blogs.msdn.com/erikaehrli/ http://blogs.msdn.com/erikaehrli/rss.aspx
    Office 12 (General) John Durant Office and a short, bald man http://blogs.msdn.com/johnrdurant/ http://blogs.msdn.com/johnrdurant/rss.aspx
    Office 12 (General) Steven Sinofsky Steven Sinofsky's Microsoft TechTalk http://blogs.msdn.com/techtalk/ http://blogs.msdn.com/techtalk/rss.aspx
    Office 12 Help Mike Kelly From my Office to Yours http://blogs.msdn.com/mikekelly/ http://blogs.msdn.com/mikekelly/rss.aspx
    Office 12 New User Interface Jensen Harris Jensen Harris  An Office User Interface Blog http://blogs.msdn.com/jensenh/ http://blogs.msdn.com/jensenh/rss.aspx
    Office 12 New XML File Format Brian Jones Brian Jones  Office XML Formats http://blogs.msdn.com/brian_jones/ http://blogs.msdn.com/brian_jones/rss.aspx
    OneNote 12 Chris Pratley Chris Pratley's OneNote Blog http://blogs.msdn.com/chris_pratley/ http://blogs.msdn.com/chris_pratley/rss.aspx
    OneNote 12 Owen Braun Owen Braun  OneNote 12 http://blogs.msdn.com/owen_braun/ http://blogs.msdn.com/owen_braun/rss.aspx
    Outlook 12 Melissa MacBeth Tasks and Time Management in Outlook http://blogs.msdn.com/melissamacbeth/ http://blogs.msdn.com/melissamacbeth/rss.aspx
    Outlook 12 Michael Affronti michael affronti | microsoft outlook program manager http://blogs.msdn.com/michael_affronti/ http://blogs.msdn.com/michael_affronti/rss.aspx
    Outlook 12 Will Kennedy Outlook 12 http://blogs.msdn.com/willkennedy/ http://blogs.msdn.com/willkennedy/rss.aspx
    Outlook 12 Extensibility Ryan Gregg Ryan's Look at Outlook Programmability http://blogs.msdn.com/rgregg/ http://blogs.msdn.com/rgregg/rss.aspx
    PDF Support in Office 12 Cyndy Wessling Save as PDF in Office "12" http://blogs.msdn.com/cyndy_wessling/ http://blogs.msdn.com/cyndy_wessling/rss.aspx
    Presentation and Graphics Brendan Busch Brendan Busch's blog on PowerPoint 12 http://blogs.msdn.com/brendanb/ http://blogs.msdn.com/brendanb/rss.aspx
    Project 12 Dieter Zirkler Dieter's ProjBlog http://blogs.msdn.com/dieterz/ http://blogs.msdn.com/dieterz/rss.aspx
    Publisher 12 Jeff Bell On Microsoft Publisher and more http://blogs.msdn.com/jeff_bell/ http://blogs.msdn.com/jeff_bell/rss.aspx
    SharePoint Products and Technologies Mike Fitz SharePoint Products and Technologies http://blogs.msdn.com/mikefitz/ http://blogs.msdn.com/mikefitz/rss.apsx
    SharePoint Products and Technologies SharePoint Team A blog from the SharePoint Development Team http://blogs.msdn.com/sharepoint/ http://blogs.msdn.com/sharepoint/rss.apsx
    SharePoint Products and Technologies WCM Team Web Content Management in Office "12" http://blogs.msdn.com/wcm/ http://blogs.msdn.com/wcm/rss.aspx
    Visio 12 Eric Rockey What's new in Visio 12 http://blogs.msdn.com/eric_rockey/ http://blogs.msdn.com/eric_rockey/rss.aspx
    VSTO VSTO Team Microsoft Visual Studio 2005 Tools for the Microsoft Office System http://blogs.msdn.com/vsto2/ http://blogs.msdn.com/vsto2/rss.aspx
    Windows Sharepoint Services PJ Hough Windows Sharepoint Services etc  http://blogs.msdn.com/pjhough/ http://blogs.msdn.com/pjhough/rss.aspx
    Word 12 Joe Friend Joe Friend  Microsoft Office Word http://blogs.msdn.com/joe_friend/ http://blogs.msdn.com/joe_friend/rss.aspx
    XPS and Windows Digital Documents Andy Simonds My blog (Andy, GPM in Windows) about XPS and the Windows Digital Documents Team. http://blogs.msdn.com/andy_simonds/ http://blogs.msdn.com/andy_simonds/rss.aspx

  • Erika Ehrli - Adventures with Office Products & Technologies

    Announcing the Open XML Format SDK 1.0

    • 16 Comments

    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,
                                           schemaList
    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,
                                            List<
    string> schemaList) {
       XmlSchemaSet schemas 
    = new XmlSchemaSet();
       foreach 
    (string schemaUri in schemaList) {
          schemas.Add(
    null, schemaUri);
       
    }
       
    using (WordprocessingDocument wordDoc 
              
    WordprocessingDocument.Open(document, false)) {
          wordDoc.MainDocumentPart.ValidateXml(schemas, 
    null);
       
    }

    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: OpenXmlDeveloper.org 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.

    Enjoy!

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

    Generating Active Directory Diagrams with Visio 2003 and Visual Studio .NET 2003

    • 4 Comments

    Last week we published a new Office Talk column: Generating Active Directory Diagrams with Visio 2003 and Visual Studio .NET 2003. I am very happy about this and I have to say that I had a lot of fun writing this article and code.

    Everything started back in September at PDC when I met with a customer who asked how to build custom Active Directory Diagrams with Visio 2003. We talked about the possibilities and in general I explained how he could build an Active Directory diagram. Active Directory is one of my favorite topics :). When I came back to Redmond I met with my friend and peer Saul Candib, who is the programmer writer for Visio. We talked about the scenario and we decided to work together to build a code sample and a technical article that would help Office developers to learn how to:

    1. Connect and query an Active Directory from managed applications.
    2. Understand the fascinating ways in which you can use the Visio PIAs to create all sorts of custom visual solutions.
    3. How to build a Visio 2003 Add-in that generates a custom Active Directory Visio drawing.
    Sample Active Directory user diagram generated in Visio


    One important note for developers who are already working with Visual Studio 2005. The Visio 2003 SDK Add-in/Add-on wizard was designed for Visual Studio 2003, and that approach will not work with Visual Studio 2005. However, you can build a Shared Add-in (instead of using the Visio 2003 SDK Add-in/Add-on wizard) and everything will work just fine. Bill Morein, who is a Program Manager for Visio wrote this interesting blog entry where he explains how to create Visio COM Add-ins with Visual Studio 2005.

    I hope that you enjoy this article and find it useful if you are one of those developers who are currently looking for options to build custom Visio 2003 solutions. I can say that Saul and I were really happy to put this together and we would be quite pleased to know if you liked it :-).

    Have a peek and enjoy!

  • Page 1 of 8 (188 items) 12345»