creating pivot tables in excel via openxml

Question

i need to make manipulation on excel file and create few complex pivot tables(using filters) using openxml. additionaly if someone know if its posible to use the BMC control-m on the batch application?

i tried implementing in sheet2 the pivot table but it keeps staying empty without pivot table

          PivotTableDefinition abc = GeneratePivotTablePart1();
            SheetData xlPivotSheetData = new SheetData(abc);
            
            WorksheetPart worksheetPart2 = workbookpart.AddNewPart<WorksheetPart>();
            Worksheet worksheet2 = new Worksheet();

            worksheet2.AppendChild(xlPivotSheetData);
            worksheetPart2.Worksheet = worksheet2;

            //package.WorkbookPart.Workbook.AppendChild(new Sheets()
            //package.WorkbookPart.Workbook.GetFirstChild<Sheets>().AppendChild(new Sheet()                    //= abc;
            Sheet sheet2 = new Sheet()
            {
                Id = app.WorkbookPart.GetIdOfPart(worksheetPart2),//package.WorkbookPart.WorksheetParts.First()),

                SheetId = 2,

                Name = "PivotTable"

            };
            sheets.Append(sheet2);

this part is the function creating the pivot table

   private static PivotTableDefinition GeneratePivotTablePart1()
    {
        var element =
            new PivotTableDefinition(
                new Location()
                {
                    Reference = "DataSource!A1:D5",
                    FirstHeaderRow = (UInt32Value)1U,
                    FirstDataRow = (UInt32Value)2U,
                    FirstDataColumn = (UInt32Value)1U
                },
                new PivotFields(
                    new PivotField(
                        new Items(
                            new Item() { Index = (UInt32Value)0U },
                            new Item() { Index = (UInt32Value)1U },
                            new Item() { ItemType = ItemValues.Default }
                        )
                        { Count = (UInt32Value)3U }
                    )
                    {
                        Axis = PivotTableAxisValues.AxisRow,
                        ShowAll = false
                    },
                    new PivotField()
                    {
                        DataField = true,
                        ShowAll = false
                    },
                    new PivotField(
                        new Items(
                            new Item() { Index = (UInt32Value)0U },
                            new Item() { Index = (UInt32Value)1U },
                            new Item() { ItemType = ItemValues.Default }
                        )
                        { Count = (UInt32Value)3U }
                    )
                    {
                        Axis = PivotTableAxisValues.AxisColumn,
                        ShowAll = false
                    },
                    new PivotField() { ShowAll = false },
                    new PivotField() { ShowAll = false }
                )
                { Count = (UInt32Value)5U },
                new RowFields(
                    new Field() { Index = 0 }
                )
                { Count = (UInt32Value)1U },
                new RowItems(
                    new RowItem(
                        new MemberPropertyIndex()),
                    new RowItem(
                        new MemberPropertyIndex() { Val = 1 }),
                    new RowItem(
                        new MemberPropertyIndex()
                    )
                    { ItemType = ItemValues.Grand }
                )
                { Count = (UInt32Value)3U },
                new ColumnFields(
                    new Field() { Index = 2 }
                )
                { Count = (UInt32Value)1U },
                new ColumnItems(
                    new RowItem(
                        new MemberPropertyIndex()),
                    new RowItem(
                        new MemberPropertyIndex() { Val = 1 }),
                    new RowItem(
                        new MemberPropertyIndex()
                    )
                    { ItemType = ItemValues.Grand }
                )
                { Count = (UInt32Value)3U },
                new DataFields(
                    new DataField()
                    {
                        Name = "Sales by Country and Period",
                        Field = (UInt32Value)1U,
                        BaseField = 0,
                        BaseItem = (UInt32Value)0U,
                        NumberFormatId = (UInt32Value)44U
                    }
                )
                { Count = (UInt32Value)1U },
                new Formats(
                    new Format(
                        new PivotArea()
                        {
                            Outline = false,
                            CollapsedLevelsAreSubtotals = true,
                            FieldPosition = (UInt32Value)0U
                        }
                    )
                    { FormatId = (UInt32Value)1U }
                )
                { Count = (UInt32Value)1U },
                new PivotTableStyle()
                {
                    Name = "PivotStyleLight16",
                    ShowRowHeaders = true,
                    ShowColumnHeaders = true,
                    ShowRowStripes = false,
                    ShowColumnStripes = false,
                    ShowLastColumn = true
                }
            )
            {
                Name = "PivotTable11",
                CacheId = (UInt32Value)57U,
                ApplyNumberFormats = false,
                ApplyBorderFormats = false,
                ApplyFontFormats = false,
                ApplyPatternFormats = false,
                ApplyAlignmentFormats = false,
                ApplyWidthHeightFormats = true,
                DataCaption = "Values",
                UpdatedVersion = 3,
                MinRefreshableVersion = 3,
                ShowCalculatedMembers = false,
                UseAutoFormatting = true,
                ItemPrintTitles = true,
                CreatedVersion = 3,
                Indent = (UInt32Value)0U,
                Outline = true,
                OutlineData = true,
                MultipleFieldFilters = false,
                RowHeaderCaption = "Countries",
                ColumnHeaderCaption = "Periods",
                FieldListSortAscending = true
            };

        return element;
    }