Export (0) Print
Expand All
Expand Minimize

Inserting a Header or Footer into an Excel 2010 Workbook by Using the Open XML SDK 2.0 for Microsoft Office

Office 2010

Office Visual How To

Summary:  Use strongly typed classes in the Open XML SDK 2.0 for Microsoft Office to insert a new header or footer into a Microsoft Office or Microsoft Excel 2010 document, without loading the document into Excel.

Applies to:   Microsoft Office 2010 | Visual Studio 2010 | 2007 Microsoft Office system

Published:   July 2010

Provided by:   Ken Getz, MCW Technologies, LLC

Overview

The Open XML file formats make it possible to work with headers and footers in an Excel 2007 or Excel 2010 document. The Open XML SDK 2.0 adds strongly typed classes that are designed to simplify access to the Open XML file formats. The SDK simplifies the task of working with headers and footers, and the code sample that is included with this Visual How To describes how to use the SDK to achieve this goal.

The sample that is included with this Visual How To includes code that allows you to insert a new header or footer into an Excel 2007 or Excel 2010 document. To use the sample, install the Open XML SDK 2.0, available from the link listed in the Explore It section. The sample also uses code included as part of a set of code snippets for the Open XML SDK 2.0. The Explore It section also includes a link to the full set of code snippets, although you can use the sample without downloading and installing the code snippets.

The sample application adds a header or footer in a document that you supply, calling the XLInsertHeaderFooter method in the sample to do the work. The method enables you to add a header or footer that indicates on which group of pages the header should appear. The calls to the method are similar to the following code example.

const string fileName = @"C:\temp\test.xlsx"
XLInsertHeaderFooter(fileName, "Sheet1", 
  "This is my header", HeaderType.EvenHeader);
XLInsertHeaderFooter(fileName, "Sheet1", 
  "This is my footer", HeaderType.AllFooter);

The sample code also includes an enumeration that defines the various possible locations for the header or footer─the XLInsertHeaderFooter procedure requires that you to supply one of these values when you pass it a workbook, sheet name, and header text.

public enum HeaderType : int
{
  AllHeader,
  AllFooter,
  OddHeader,
  OddFooter,
  EvenHeader,
  EvenFooter,
  FirstHeader,
  FirstFooter
}

Be aware that this enumeration is not supplied by the SDK. It enables you to indicate to the procedure where you want the header or footer to be added. The AllHeader and AllFooter cases do not match any internal Excel functionality. Instead, these handle the case in which you have specified an even or odd header or footer, and also want a header or footer on each page. This way, you can have the DifferentOddEven property set to True. However, you can still manage to have one header or footer that appears on all pages. You can do this within the user interface, but there is nothing built into the Open XML SDK 2.0, nor into the Excel file format directly, that supports this.

Before using the code, it is important to understand how headers and footers are stored in an Excel document. The Open XML SDK 2.0 includes, in its tool directory, a useful application named OpenXmlSdkTool.exe, shown in Figure 1. This tool enables you to open a document and view its various parts and the hierarchy of parts. Figure 1 shows the test document after running the code in this sample, and in the right-hand panes, the tool displays both the XML for the part and reflected C# code that you could use to generate the contents of the part.

Figure 1 shows the Open XML SDK 2.0 Productivity Tool that enables you to view the Open XML content of a document.

Figure 1. Productivity tool

Test document after running the code

If you examine the XML content in Figure 1, you will find information, similar to the following, about the code in this Visual How To:

  • The Excel file format stores header and footer information in a part of type HeaderFooter.

  • The HeaderFooter part is a child of a part of type Worksheet, which is a child of the particular sheet your code modifies.

  • The HeaderFooter part has a Boolean DifferentOddEven property, which indicates whether the sheet uses different headers and footers on odd and even pages. (This explains why the sample code includes the AllFooter and AllHeader options. Without this, you would have to specify the same header or footer two times, if you had set the DifferentOddEven property to True, but wanted a single header or footer on all pages.)

  • Although this sample does not use this feature, Excel supports the option to include a different header or footer for the first page. The sample code does include the ability to set this option, by specifying the FirstHeader or FirstFooter enumerated value when you create the header or footer.

Code It

The sample provided with this Visual How To includes the code that is needed to add a header or footer to an Excel 2007 or Excel 2010 document. The following sections describe the code in detail.

Setting Up References

To use the code from the Open XML SDK 2.0, add several references to your project. The sample project includes these references, but in your own code, you would need to explicitly reference the following assemblies:

  • WindowsBase─This reference may be set for you, depending on the kind of project that you create.

  • DocumentFormat.OpenXml─Installed by the Open XML SDK 2.0.

Also, you should add the following using/Imports statements to the top of your code file.

using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

Examining the Procedure

The XLInsertHeaderFooter procedure accepts four parameters, indicating:

  • The name of the workbook to modify (string).

  • The name of the sheet to which to add a header or footer (string).

  • The content of the header or footer (string).

  • The type of header or footer (one of the values in the HeaderType enumeration).

public static void XLInsertHeaderFooter(
  string fileName, string sheetName, 
  string textToInsert, HeaderType type)

To call the procedure, pass all the parameter values, as shown in the following code example.

const string fileName = @"C:\temp\test.xlsx"
XLInsertHeaderFooter(fileName, "Sheet1", 
  "This is my header", HeaderType.EvenHeader);
XLInsertHeaderFooter(fileName, "Sheet1", 
  "This is my footer", HeaderType.AllFooter);

Retrieving a Reference to the Worksheet Part

The code starts by opening the specified workbook in read/write mode and retrieving a reference to the workbook part.

using (SpreadsheetDocument document = 
  SpreadsheetDocument.Open(fileName, true))
{
  WorkbookPart wbPart = document.WorkbookPart;
  // Code removed here.
}

The code then looks within the descendants of the workbook within the workbook part, of type Sheet, where the name matches the name that you specified when you called the procedure. Because the sheet may not exist, the code uses the FirstOrDefault method, which returns a null reference (rather than raising an exception, as does the First method) if the search fails. If the search fails, the code quietly returns without taking action. Be aware that this code only examines the XML content within the Workbook object, looking for a Sheet element with the correct name. It does not retrieve a reference to the corresponding part.

Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().
  Where(s => s.Name == sheetName).FirstOrDefault();
if (theSheet == null)
{
  return;
}

Given a reference to the Sheet element, the code needs to retrieve a reference to the Worksheet part, and then the Worksheet itself. The code uses the Id property, and the workbook part GetPartById method, to retrieve a reference to the corresponding WorksheetPart. The code then retrieves a reference to the Worksheet property of the part to get at the data contained within the part. If it cannot retrieve the worksheet, something is wrong with the workbook, and the code again quietly returns.

WorksheetPart wsPart = 
  (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
Worksheet ws = wsPart.Worksheet;

// Worksheet is nothing? You have a damaged workbook!
if (ws == null)
{
  return;
}

Working with the Header or Footer

Given a reference to the worksheet, the code looks within its XML content to find the first HeaderFooter element. In this case, if the worksheet does not currently contain the element, the code creates it.

HeaderFooter hf = ws.Descendants<HeaderFooter>().FirstOrDefault();
if (hf == null)
{
  hf = new HeaderFooter();
  ws.AppendChild<HeaderFooter>(hf);
}

Now, assume that the element exists. However, the code still confirms this.

if (hf != null)
{
  // Code removed here.
}

The code handles the attributes of the HeaderFooter element, setting the DifferentOddEven or DifferentFirst properties appropriately.

switch (type)
{
  case HeaderType.EvenHeader:
  case HeaderType.EvenFooter:
  case HeaderType.OddHeader:
  case HeaderType.OddFooter:
    // Even or odd only? Add a differentOddEven attribute and set 
    // it to "1".
    hf.DifferentOddEven = true;
    break;

  case HeaderType.FirstFooter:
  case HeaderType.FirstHeader:
    hf.DifferentFirst = true;
    break;
}

Finally, depending on the type of header or footer, you specified when you called the procedure, the code creates a new object that represents the header or footer, and sets its Text property appropriately.

switch (type)
{
  // This code creates new header elements, even if they
  // already exist. Either way, you end up with a "fresh" element.

  case HeaderType.AllHeader:
    hf.EvenHeader = new EvenHeader();
    hf.EvenHeader.Text = textToInsert;

    hf.OddHeader = new OddHeader();
    hf.OddHeader.Text = textToInsert;
    break;

    case HeaderType.AllFooter:
      hf.EvenFooter = new EvenFooter();
      hf.EvenFooter.Text = textToInsert;

    hf.OddFooter = new OddFooter();
    hf.OddFooter.Text = textToInsert;
    break;

  case HeaderType.EvenFooter:
    hf.EvenFooter = new EvenFooter();
    hf.EvenFooter.Text = textToInsert;
    break;

  case HeaderType.EvenHeader:
    hf.EvenHeader = new EvenHeader();
    hf.EvenHeader.Text = textToInsert;
    break;

  case HeaderType.OddFooter:
    hf.OddFooter = new OddFooter();
    hf.OddFooter.Text = textToInsert;
    break;

  case HeaderType.OddHeader:
    hf.OddHeader = new OddHeader();
    hf.OddHeader.Text = textToInsert;
    break;

  case HeaderType.FirstHeader:
    hf.FirstHeader = new FirstHeader();
    hf.FirstHeader.Text = textToInsert;
    break;

  case HeaderType.FirstFooter:
    hf.FirstFooter = new FirstFooter();
    hf.FirstFooter.Text = textToInsert;
    break;
}

As a final step, the code saves the worksheet part.

ws.Save();

Sample Procedure

The complete sample procedure includes the following code.

public static void XLInsertHeaderFooter(
  string fileName, string sheetName, 
  string textToInsert, HeaderType type)
{
  using (SpreadsheetDocument document = 
    SpreadsheetDocument.Open(fileName, true))
  {
    WorkbookPart wbPart = document.WorkbookPart;

    // Find the sheet with the supplied name, and then use 
    // that Sheet object to retrieve a reference to 
    // the appropriate worksheet.
    Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().
      Where(s => s.Name == sheetName).FirstOrDefault();
    if (theSheet == null)
    {
      return;
    }
    WorksheetPart wsPart = 
      (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
    Worksheet ws = wsPart.Worksheet;

    // Worksheet is nothing? You have a damaged workbook!
    if (ws == null)
    {
      return;
    }

    // Retrieve a reference to the header/footer node, if it exists.
    HeaderFooter hf = ws.Descendants<HeaderFooter>().FirstOrDefault();
    if (hf == null)
    {
      hf = new HeaderFooter();
      ws.AppendChild<HeaderFooter>(hf);
    }

    // The HeaderFooter node should be there, at this point!
    if (hf != null)
    {
      // You've found the node. Now add the header or footer.
      // Deal with the attributes first:
      switch (type)
      {
        case HeaderType.EvenHeader:
        case HeaderType.EvenFooter:
        case HeaderType.OddHeader:
        case HeaderType.OddFooter:
          // Even or odd only? Add a differentOddEven attribute and set 
          // it to "1".
          hf.DifferentOddEven = true;
          break;

        case HeaderType.FirstFooter:
        case HeaderType.FirstHeader:
          hf.DifferentFirst = true;
          break;
      }

      switch (type)
      {
        // This code creates new header elements, even if they
        // already exist. Either way, you end up with a 
        // "fresh" element.
        case HeaderType.AllHeader:
          hf.EvenHeader = new EvenHeader();
          hf.EvenHeader.Text = textToInsert;

          hf.OddHeader = new OddHeader();
          hf.OddHeader.Text = textToInsert;
          break;

        case HeaderType.AllFooter:
          hf.EvenFooter = new EvenFooter();
          hf.EvenFooter.Text = textToInsert;

          hf.OddFooter = new OddFooter();
          hf.OddFooter.Text = textToInsert;
          break;

        case HeaderType.EvenFooter:
          hf.EvenFooter = new EvenFooter();
          hf.EvenFooter.Text = textToInsert;
          break;

        case HeaderType.EvenHeader:
          hf.EvenHeader = new EvenHeader();
          hf.EvenHeader.Text = textToInsert;
          break;

        case HeaderType.OddFooter:
          hf.OddFooter = new OddFooter();
          hf.OddFooter.Text = textToInsert;
          break;

        case HeaderType.OddHeader:
          hf.OddHeader = new OddHeader();
          hf.OddHeader.Text = textToInsert;
          break;

        case HeaderType.FirstHeader:
          hf.FirstHeader = new FirstHeader();
          hf.FirstHeader.Text = textToInsert;
          break;

        case HeaderType.FirstFooter:
          hf.FirstFooter = new FirstFooter();
          hf.FirstFooter.Text = textToInsert;
          break;
      }
    }
    ws.Save();
  }
}
Read It

The example presented here includes many of the issues that you will encounter when you are working with the Open XML SDK 2.0. Each example is slightly different. However, the basic concepts are the same. For the most part, unless you understand the structure of the part you are trying to work with, even the Open XML SDK 2.0 will not make it possible to interact with the part. One issue that takes time to internalize is the difference between using the SDK to get at strongly typed objects representing the parts within the document, versus dealing with the XML within the content of the parts. This example shows both techniques. If you investigate objects before you write code you will save time.

See It

Watch the video

Watch video

Length: 00:10:06

Click to grab code

Grab the Code

Explore It

About the Author
Ken Getz is a senior consultant with MCW Technologies. He is coauthor of ASP .NET Developers Jumpstart (Addison-Wesley, 2002), Access Developer's Handbook (Sybex, 2001), and VBA Developer's Handbook, 2nd Edition (Sybex, 2001).

Community Additions

ADD
Show:
© 2014 Microsoft