Inserting a Header or Footer into an Excel 2010 Workbook by Using the Open XML SDK 2.0 for Microsoft Office
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
 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 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 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:
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:
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. 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. 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(); } }  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  About the Author |
