Add Header to DocumentFormat.OpenXml.Spreadsheet

Question

Working on an excel sheet and I would like to add the worksheet name to the center header. Below is my code.

var headerFooter = worksheetPart1.Worksheet.Elements < HeaderFooter>().FirstOrDefault();

if(headerFooter == null)
    headerFooter = new HeaderFooter();

headerFooter.DifferentOddEven = false;
headerFooter.DifferentFirst = false;

var oddHeader = new OddHeader("&C&A");
headerFooter.Append(oddHeader);

worksheetPart1.Worksheet.Append(headerFooter);

This code however leads to an error opening up the Excel sheet.

Answer 1

Headers and footers are displayed only in Page Layout view, Print Preview, and on printed pages. If you want to define a header, you need to define PageMargins, PageSetup, SheetDimension and add a few namespace declarations. Your WorksheetPart should be generated like something below:

private static void GenerateWorksheetPart1Content(WorksheetPart worksheetPart1)
{
    Worksheet worksheet1 = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac xr xr2 xr3" } };
    worksheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
    worksheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
    worksheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
    worksheet1.AddNamespaceDeclaration("xr", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision");
    worksheet1.AddNamespaceDeclaration("xr2", "http://schemas.microsoft.com/office/spreadsheetml/2015/revision2");
    worksheet1.AddNamespaceDeclaration("xr3", "http://schemas.microsoft.com/office/spreadsheetml/2016/revision3");
    worksheet1.SetAttribute(new OpenXmlAttribute("xr", "uid", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision", "{971DAD3B-06EC-4344-A9C0-EF4D3CB78B7F}"));
    SheetDimension sheetDimension1 = new SheetDimension() { Reference = "A1" };

    SheetViews sheetViews1 = new SheetViews();
    SheetView sheetView1 = new SheetView() { TabSelected = true, View = SheetViewValues.PageLayout, ZoomScaleNormal = (UInt32Value)100U, WorkbookViewId = (UInt32Value)0U };

    sheetViews1.Append(sheetView1);
    SheetFormatProperties sheetFormatProperties1 = new SheetFormatProperties() { DefaultRowHeight = 15D, DyDescent = 0.25D };
    SheetData sheetData1 = new SheetData();
    PageMargins pageMargins1 = new PageMargins() { Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };
    PageSetup pageSetup1 = new PageSetup() { PaperSize = (UInt32Value)9U, Orientation = OrientationValues.Portrait, Id = "rId1" };

    HeaderFooter headerFooter1 = new HeaderFooter();
    OddHeader oddHeader1 = new OddHeader();
    oddHeader1.Text = "&CTest1";

    headerFooter1.Append(oddHeader1);

    worksheet1.Append(sheetDimension1);
    worksheet1.Append(sheetViews1);
    worksheet1.Append(sheetFormatProperties1);
    worksheet1.Append(sheetData1);
    worksheet1.Append(pageMargins1);
    worksheet1.Append(pageSetup1);
    worksheet1.Append(headerFooter1);

    worksheetPart1.Worksheet = worksheet1;
}

which can be evoked by:

static void Main(string[] args)
{
    CreateSpreadsheetWorkbook("Test.xlsx");
}

public static void CreateSpreadsheetWorkbook(string filepath)
{
    // Create a spreadsheet document by supplying the filepath.
    // By default, AutoSave = true, Editable = true, and Type = xlsx.
    SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
        Create(filepath, SpreadsheetDocumentType.Workbook);

    // Add a WorkbookPart to the document.
    WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
    workbookpart.Workbook = new Workbook();

    // Add a WorksheetPart to the WorkbookPart.
    WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
    GenerateWorksheetPart1Content(worksheetPart);

    // Add Sheets to the Workbook.
    Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
        AppendChild<Sheets>(new Sheets());

    // Append a new worksheet and associate it with the workbook.
    Sheet sheet = new Sheet()
    {
        Id = spreadsheetDocument.WorkbookPart.
        GetIdOfPart(worksheetPart),
        SheetId = 1,
        Name = "mySheet"
    };
    sheets.Append(sheet);


    workbookpart.Workbook.Save();

    // Close the document.
    spreadsheetDocument.Close();
}