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