OpenXML Worksheet: Adding AutoFilter

I wasted more time than I want to think about on this, so here is a solution I hope will help anyone else generating an Excel worksheet and wanting to add Automatic Filtering.

This is a full, working sample.  Just take out the code I use to populate worksheet and insert your own.

Pay attention to the AutoFilter section I’ve highlighted below:

    using (SpreadsheetDocument spreadsheetDocument =
            SpreadsheetDocument.Create(System.IO.Path.Combine(workingDirectory,
            "ConversionReport.xlsx"), SpreadsheetDocumentType.Workbook))
    {
        SheetData sheetData = new SheetData();

        Row titleRow = new Row { RowIndex = (UInt32)1 };

        titleRow.AppendChild(CreateTextCell("A", "Entity Name", 1));
        titleRow.AppendChild(CreateTextCell("B", "Event Name", 1));
        titleRow.AppendChild(CreateTextCell("C", "Attribute Name", 1));
        titleRow.AppendChild(CreateTextCell("D", "Object Name", 1));
        titleRow.AppendChild(CreateTextCell("E", "Type Name", 1));
        titleRow.AppendChild(CreateTextCell("F", "Tab Name", 1));
        titleRow.AppendChild(CreateTextCell("G", "Section Name", 1));

        // Append Row to SheetData
        sheetData.AppendChild(titleRow);

        for (int i = 0; i < ConversionReportItems.Count; i++)
        {
            Row row = new Row { RowIndex = (UInt32)i + 2 };

            row.AppendChild(CreateTextCell("A", ConversionReportItems[i].EntityName, i + 2));
            row.AppendChild(CreateTextCell("B", ConversionReportItems[i].EventName, i + 2));
            row.AppendChild(CreateTextCell("C", ConversionReportItems[i].AttributeName, i + 2));
            row.AppendChild(CreateTextCell("D", ConversionReportItems[i].Name, i + 2));
            row.AppendChild(CreateTextCell("E", ConversionReportItems[i].TypeName, i + 2));
            row.AppendChild(CreateTextCell("F", ConversionReportItems[i].TabName, i + 2));
            row.AppendChild(CreateTextCell("G", ConversionReportItems[i].SectionName, i + 2));

            // Append Row to SheetData
            sheetData.AppendChild(row);
        }

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

        // Set the AutoFilter property to a range that is the size of the data
        // within the worksheet
        AutoFilter autoFilter1 = new AutoFilter()
            { Reference = "A1:G" + ConversionReportItems.Count + 1 };

        Worksheet sheet1 = new Worksheet();
        sheet1.Append(sheetData);
        sheet1.Append(autoFilter1);

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

        // 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 = "Conversion Report"
        };

        sheets.Append(sheet);

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