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

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 fileName As String = "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
  AllHeader
  AllFooter
  OddHeader
  OddFooter
  EvenHeader
  EvenFooter
  FirstHeader
  FirstFooter
End Enum

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

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.

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.

Imports DocumentFormat.OpenXml.Packaging
Imports 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 Sub XLInsertHeaderFooter(
  ByVal fileName As String, ByVal sheetName As String, _
  ByVal textToInsert As String, ByVal type As HeaderType)

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

Const fileName As String = "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 document As SpreadsheetDocument =
  SpreadsheetDocument.Open(fileName, True)
  Dim wbPart As WorkbookPart = document.WorkbookPart
  ' Code removed here.
End Using

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.

Dim theSheet As Sheet = wbPart.Workbook.Descendants(Of Sheet)(). _
  Where(Function(s) s.Name = sheetName).FirstOrDefault()
If theSheet Is Nothing Then
  Return
End If

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.

Dim wsPart As WorksheetPart =
  CType(wbPart.GetPartById(theSheet.Id), WorksheetPart)
Dim ws As Worksheet = wsPart.Worksheet

' Worksheet is nothing? You have a damaged workbook!
If ws Is Nothing Then
  Return
End If

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.

Dim hf As HeaderFooter = ws.Descendants(Of HeaderFooter).FirstOrDefault()
If hf Is Nothing Then
  hf = New HeaderFooter()
  ws.AppendChild(Of HeaderFooter)(hf)
End If

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

If hf IsNot Nothing Then
  ' Code removed here.
End If

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

Select Case type
  Case HeaderType.EvenHeader,
    HeaderType.EvenFooter, HeaderType.OddHeader,
    HeaderType.OddFooter
    ' Even or odd only? Add a differentOddEven attribute 
    ' and set it to "1".
    hf.DifferentOddEven = True

  Case HeaderType.FirstFooter, HeaderType.FirstHeader
    ' First page only? Add a differentFirst attribute and set it to "1".
    hf.DifferentFirst = True
End Select

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.

Select Case 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

  Case HeaderType.AllFooter
    hf.EvenFooter = New EvenFooter
    hf.EvenFooter.Text = textToInsert

    hf.OddFooter = New OddFooter
    hf.OddFooter.Text = textToInsert

  Case HeaderType.EvenFooter
    hf.EvenFooter = New EvenFooter
    hf.EvenFooter.Text = textToInsert

  Case HeaderType.EvenHeader
    hf.EvenHeader = New EvenHeader
    hf.EvenHeader.Text = textToInsert

  Case HeaderType.OddFooter
    hf.OddFooter = New OddFooter
    hf.OddFooter.Text = textToInsert

  Case HeaderType.OddHeader
    hf.OddHeader = New OddHeader
    hf.OddHeader.Text = textToInsert

  Case HeaderType.FirstHeader
    hf.FirstHeader = New FirstHeader
    hf.FirstHeader.Text = textToInsert

  Case HeaderType.FirstFooter
    hf.FirstFooter = New FirstFooter
    hf.FirstFooter.Text = textToInsert
End Select

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

ws.Save()

Sample Procedure

The complete sample procedure includes the following code.

Public Sub XLInsertHeaderFooter(
  ByVal fileName As String, ByVal sheetName As String, _
  ByVal textToInsert As String, ByVal type As HeaderType)

  Using document As SpreadsheetDocument = SpreadsheetDocument.Open(fileName, True)
    Dim wbPart As WorkbookPart = document.WorkbookPart

    ' Find the sheet with the supplied name, and then use 
    ' that Sheet object to retrieve a reference to the appropriate 
    ' worksheet.
    Dim theSheet As Sheet = wbPart.Workbook.Descendants(Of Sheet)().
      Where(Function(s) s.Name = sheetName).FirstOrDefault()
    If theSheet Is Nothing Then
      Return
    End If

    Dim wsPart As WorksheetPart = 
      CType(wbPart.GetPartById(theSheet.Id), WorksheetPart)
    Dim ws As Worksheet = wsPart.Worksheet

    ' Worksheet is nothing? You have a damaged workbook!
    If ws Is Nothing Then
      Return
    End If

    ' Retrieve a reference to the header/footer node, if it exists.
    Dim hf As HeaderFooter = 
      ws.Descendants(Of HeaderFooter).FirstOrDefault()
    If hf Is Nothing Then
      hf = New HeaderFooter()
      ws.AppendChild(Of HeaderFooter)(hf)
    End If

    ' The HeaderFooter node should be there, at this point!
    If hf IsNot Nothing Then
      ' You've found the node. Now add the header or footer.
      ' Deal with the attributes first:
      Select Case type
        Case HeaderType.EvenHeader, HeaderType.EvenFooter, 
          HeaderType.OddHeader, HeaderType.OddFooter
          ' Even or odd only? Add a differentOddEven 
          ' attribute and set it to "1".
          hf.DifferentOddEven = True

       Case HeaderType.FirstFooter, HeaderType.FirstHeader
          ' First page only? Add a differentFirst attribute 
          ' and set it to "1".
          hf.DifferentFirst = True
      End Select

      Select Case 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

        Case HeaderType.AllFooter
          hf.EvenFooter = New EvenFooter
          hf.EvenFooter.Text = textToInsert

          hf.OddFooter = New OddFooter
          hf.OddFooter.Text = textToInsert

        Case HeaderType.EvenFooter
          hf.EvenFooter = New EvenFooter
          hf.EvenFooter.Text = textToInsert

        Case HeaderType.EvenHeader
          hf.EvenHeader = New EvenHeader
          hf.EvenHeader.Text = textToInsert

        Case HeaderType.OddFooter
          hf.OddFooter = New OddFooter
          hf.OddFooter.Text = textToInsert

        Case HeaderType.OddHeader
          hf.OddHeader = New OddHeader
          hf.OddHeader.Text = textToInsert

        Case HeaderType.FirstHeader
          hf.FirstHeader = New FirstHeader
          hf.FirstHeader.Text = textToInsert

        Case HeaderType.FirstFooter
          hf.FirstFooter = New FirstFooter
          hf.FirstFooter.Text = textToInsert
      End Select
    End If

    ws.Save()
  End Using
End Sub

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.

Watch the video

Length: 00:10:06

Grab the Code

• Open XML SDK 2.0 for Microsoft Office

• Open XML Format SDK 2.0 Code Snippets for Visual Studio 2008

• Blog: Brian Jones & Zeyad Rajabi

• Open XML Developer Center

• Office Development in Visual Studio

• Outlook Developer Center

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