C# 使用 Office.Interop 设置数据透视表的表样式

Question

我正在尝试为使用 office.interop 创建的数据透视表设置样式，它可以工作所有内容，但样式不起作用

我也尝试设置表格样式和表格样式2，但没有人工作

Microsoft.Office.Interop.Excel.PivotCaches pch = workBook.PivotCaches((; sheetData.Activate((;

        pch.Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase, range).CreatePivotTable(sheetPivot.Cells[pivotRow, 1], "PivTbl_1", Type.Missing, Type.Missing);
        Microsoft.Office.Interop.Excel.PivotTable pvt = sheetPivot.PivotTables("PivTbl_1") as Microsoft.Office.Interop.Excel.PivotTable;
        var ptStyle = workBook.TableStyles["PivotStyleMedium2"];
        pvt.ShowTableStyleColumnHeaders = true;
        pvt.ShowTableStyleColumnStripes = true;
        pvt.ShowTableStyleLastColumn = true;
        pvt.ShowTableStyleRowHeaders = true;
        pvt.ShowTableStyleRowStripes = true;
        pvt.TableStyle = ptStyle.Name;
        pvt.TableStyle2 = ptStyle;
        foreach (var item in provider.RowGroupDescriptions)
        {
            var field = ((Microsoft.Office.Interop.Excel.PivotField)pvt.PivotFields(item.PropertyName));
            field.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField;
        }
        foreach (var item in provider.ColumnGroupDescriptions)
        {
            var field = ((Microsoft.Office.Interop.Excel.PivotField)pvt.PivotFields(item.PropertyName));
            field.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlColumnField;
        }
        foreach (var item in provider.AggregateDescriptions)
        {
            var field = ((Microsoft.Office.Interop.Excel.PivotField)pvt.PivotFields((item as PropertyAggregateDescription).PropertyName));
            field.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField;
            field.Function = Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum;
        }
        foreach (var item in provider.FilterDescriptions)
        {
            var field = ((Microsoft.Office.Interop.Excel.PivotField)pvt.PivotFields(item.PropertyName));
            field.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlPageField;
            var items = (item.Condition as Telerik.Pivot.Core.Filtering.ItemsFilterCondition).DistinctCondition.Items;
            pvt.RefreshTable();
            Microsoft.Office.Interop.Excel.PivotItems _PivotItems = (Microsoft.Office.Interop.Excel.PivotItems)field.PivotItems();
            foreach (Microsoft.Office.Interop.Excel.PivotItem _PivotItem in _PivotItems)
            {
                if (items.Contains(_PivotItem.Caption))
                    _PivotItem.Visible = true;
                else
                    _PivotItem.Visible = false;
            }
        }
        sheetPivot.PivotTables("PivTbl_1").DataPivotField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlColumnField;
        sheetPivot.Columns.AutoFit();
        sheetPivot.Activate();
        workBook.SaveAs(FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
        workBook.Close();

Answer 1

已解决直接设置样式名称

的问题

     Microsoft.Office.Interop.Excel.PivotTable pvt = sheetPivot.PivotTables("PivTbl_1") as Microsoft.Office.Interop.Excel.PivotTable;
        pvt.ShowTableStyleColumnHeaders = true;
        pvt.ShowTableStyleColumnStripes = true;
        pvt.ShowTableStyleLastColumn = true;
        pvt.ShowTableStyleRowHeaders = true;
        pvt.ShowTableStyleRowStripes = true;
        pvt.TableStyle2 = "PivotStyleMedium9";