Tell me how to add the filtering option to the head row as in the screenshot? Using Visual studio, SDK DocumentFormat.OpenXML.

 WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
 var sheetData = new SheetData();
 worksheetPart.Worksheet = new Worksheet(sheetData);
 
 Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), 
 SheetId = sheetId, Name = "Equipment" };
 sheets.AppendChild(sheet);
 
 Row headerRow = new Row();
 
 InsertCell(headerRow, 1, "Column 10", CellValues.String);
 InsertCell(headerRow, 2, "Column 20", CellValues.String);

Tell me how to add the filtering option to the head row as in the screenshot? Using Visual studio, SDK DocumentFormat.OpenXML.

 WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
 var sheetData = new SheetData();
 worksheetPart.Worksheet = new Worksheet(sheetData);
 
 Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), 
 SheetId = sheetId, Name = "Equipment" };
 sheets.AppendChild(sheet);
 
 Row headerRow = new Row();
 
 InsertCell(headerRow, 1, "Column 10", CellValues.String);
 InsertCell(headerRow, 2, "Column 20", CellValues.String);

• c#
• excel
• visual-studio
• asp-core
• openxml

1 Answer 1

Just need to create AutoFilter object and append to sheet. You could try following sample:

        string filePath = "FilteredExcel.xlsx";

        using (SpreadsheetDocument document = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
        {
            WorkbookPart workbookPart = document.AddWorkbookPart();
            workbookPart.Workbook = new Workbook();

            WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            SheetData sheetData = new SheetData();

            // Define header row
            sheetData.Append(new Row(
                new Cell() { CellReference = "A1", DataType = CellValues.String, CellValue = new CellValue("Name") },
                new Cell() { CellReference = "B1", DataType = CellValues.String, CellValue = new CellValue("Age") },
                new Cell() { CellReference = "C1", DataType = CellValues.String, CellValue = new CellValue("City") }
            ));

            // Append data rows directly
            sheetData.Append(new Row(
                new Cell() { CellReference = "A2", DataType = CellValues.String, CellValue = new CellValue("Alice") },
                new Cell() { CellReference = "B2", DataType = CellValues.String, CellValue = new CellValue("30") },
                new Cell() { CellReference = "C2", DataType = CellValues.String, CellValue = new CellValue("New York") }
            ));

            sheetData.Append(new Row(
                new Cell() { CellReference = "A3", DataType = CellValues.String, CellValue = new CellValue("Bob") },
                new Cell() { CellReference = "B3", DataType = CellValues.String, CellValue = new CellValue("25") },
                new Cell() { CellReference = "C3", DataType = CellValues.String, CellValue = new CellValue("London") }
            ));


            worksheetPart.Worksheet = new Worksheet(sheetData);

            // Add AutoFilter to the first row (A1:C1)
            AutoFilter autoFilter = new AutoFilter() { Reference = "A1:C1" };
            worksheetPart.Worksheet.Append(autoFilter);
            worksheetPart.Worksheet.Save();

            Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
            Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" };
            sheets.Append(sheet);

            workbookPart.Workbook.Save();
        }

        Console.WriteLine("Excel file with filtering created successfully!");