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.

ConversionReportItems is merely a generic list: List<CrmReportItem> that is holding my data.

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();
    }
}

I have a helper function that I got some someone’s blog ( sorry, I can’t remember who ):

private static Cell CreateTextCell(string header, string text, int index)
{
    // New Cell 
    Cell cell = new Cell { DataType = CellValues.InlineString, CellReference = header + index };

    // Create Text object 
    Text t = new Text { Text = text };

    // Append Text to InlineString object 
    InlineString inlineString = new InlineString();
    inlineString.AppendChild(t);

    // Append InlineString to Cell 
    cell.AppendChild(inlineString);

    return cell;
}