Setting Autofilter to Excel Sheet using Open XML with Powershell

Question

I create a XLSX file in powershell, using Open XML 2.19. Now I try to append an AutoFilter to the first row.

In the Microsoft documentation a sheet has AutoFilter settings. But my $sheet has no such property or method.

Do I need directly manipulate the XML strucuture or is there another way to set an AutoFilter?

Edit:

The most promissing approach I found is here, but I don't have a GetPreceedingElement in my $spreadsheetDocument

public static void ApplyAutofilter(string fileName, string sheetName, string reference)
{
    using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
    {
        IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == sheetName);
        var arrSheets = sheets as Sheet[] ?? sheets.ToArray();

        string relationshipId = arrSheets.First().Id.Value;
        var worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);

        var autoFilter = new AutoFilter() { Reference = reference };
        OpenXmlElement preceedingElement = GetPreceedingElement(worksheetPart);
        worksheetPart.Worksheet.InsertAfter(autoFilter, preceedingElement);

        worksheetPart.Worksheet.Save();
    }
}

Answer 1

Finally I found why it didn´t work.

ObenXML knows two diffent Sheets

$workSheet = New-Object DocumentFormat.OpenXml.Spreadsheet.Worksheet 

and

$sheet = New-Object DocumentFormat.OpenXml.Spreadsheet.Sheet

$workSheet needs not to be defined explicitely. I did it this way:

$worksheetPart = Invoke-GenericMethod -InputObject $workbookpart -MethodName AddNewPart -GenericType DocumentFormat.OpenXml.Packaging.WorksheetPart
$sheet = New-Object DocumentFormat.OpenXml.Spreadsheet.Sheet
    $sheet.Id = $spreadsheetDocument.WorkbookPart.GetIdOfPart($worksheetPart)
$sheet.SheetId = New-Object DocumentFormat.OpenXml.UInt32Value $sheetNo
    $sheet.Name = "$sheetName"
    $sheets.Append($sheet)

$sheetData = New-Object DocumentFormat.OpenXml.Spreadsheet.SheetData
$worksheetPart.Worksheet = New-Object DocumentFormat.OpenXml.Spreadsheet.Worksheet($sheetData) 

In this case the Filter must be apllied to the Worksheet property of the Sheetpart

$autoFilter = New-Object DocumentFormat.OpenXml.Spreadsheet.AutoFilter
$autoFilter.Reference = "A1:Y1"
$worksheetPart.Worksheet.Append($autoFilter)