//ピボットテーブルフィールドを設定します
var field_Category = pivottable.PivotFields["Category"];
field_Category.Orientation = PivotFieldOrientation.RowField;

var field_Product = pivottable.PivotFields["Product"];
field_Product.Orientation = PivotFieldOrientation.ColumnField;

var field_Amount = pivottable.PivotFields["Amount"];
field_Amount.Orientation = PivotFieldOrientation.DataField;

var field_Country = pivottable.PivotFields["Country"];
field_Country.Orientation = PivotFieldOrientation.PageField;

//フィールド量関数を設定します
field_Amount.Function = ConsolidationFunction.Average;

//レベル1の製品
var field_product = pivottable.PivotFields["Product"];
field_product.Orientation = PivotFieldOrientation.RowField;

//レベル2のカテゴリ
var field_category = pivottable.PivotFields["Category"];
field_category.Orientation = PivotFieldOrientation.RowField;

var field_Amount = pivottable.PivotFields[3];
field_Amount.Orientation = PivotFieldOrientation.DataField;

//カテゴリはレベル1に、製品はレベル2になります
field_product.Position = 1;
field_category.Position = 0;

// 列と行の総計を表示するためにPivotTableレポートを設定します
pivottable.ColumnGrand = true;
pivottable.RowGrand = true;

// PivotTableのLayoutRowTypeをTabularRowに設定します
pivottable.SetRowAxisLayout(LayoutRowType.TabularRow);

//ピボットテーブルのレイアウトを設定します
field_Category.LayoutForm = LayoutFormType.Tabular;
field_Category.LayoutBlankLine = true;

field_Country.LayoutForm = LayoutFormType.Outline;
field_Country.LayoutCompactRow = false;

//小計の位置を設定します
field_Country.LayoutSubtotalLocation = SubtotalLocationType.Bottom;
field_Country.ShowAllItems = true;

// ピボットテーブルのフィールドを構成します
var field_Date = pivottable.PivotFields["Date"];
field_Date.Orientation = PivotFieldOrientation.PageField;

// PivotFieldの「Category」の名前を「Type of Category」に変更します
var field_Category = pivottable.PivotFields["Category"];
field_Category.Name = "Type of Category";
field_Category.Orientation = PivotFieldOrientation.RowField;

var field_Product = pivottable.PivotFields["Product"];
field_Product.Orientation = PivotFieldOrientation.ColumnField;

var field_Amount = pivottable.PivotFields["Amount"];
field_Amount.Orientation = PivotFieldOrientation.DataField;
     

var field_Country = pivottable.PivotFields["Country"];
field_Country.Orientation = PivotFieldOrientation.RowField;

// DataFieldの「Sum of Amount」の名前を「Amount Total」に変更します
pivottable.DataFields[0].Name = "Amount Total";

var field_product = pivottable.PivotFields["Product"];
field_product.Orientation = PivotFieldOrientation.RowField;

var field_Amount = pivottable.PivotFields[3];
field_Amount.Orientation = PivotFieldOrientation.DataField;

//PivotCacheのソースデータを変更します
worksheet.Range["D8"].Value = 3000;

//キャッシュのデータをピボットテーブルに同期させます
worksheet.PivotTables[0].Refresh();

//ピボットテーブルフィールドを設定します
var field_Category = pivottable.PivotFields["Category"];
field_Category.Orientation = PivotFieldOrientation.RowField;

var field_Product = pivottable.PivotFields["Product"];
field_Product.Orientation = PivotFieldOrientation.ColumnField;

//「Amount」フィールドにSum関数を適用します
var field_Amount = pivottable.PivotFields["Amount"];
pivottable.AddDataField(field_Amount, "sum amount", ConsolidationFunction.Sum);

//「Amount」フィールドにCount関数を適用します
var field_Amount2 = pivottable.PivotFields["Amount"];
pivottable.AddDataField(field_Amount2, "count amount", ConsolidationFunction.Count);

IWorksheet calculatedFieldSheet = workbook.Worksheets.Add();
calculatedFieldSheet.Name = "CalculatedField";
        
//ピボットテーブルを追加します
IPivotCache pivotCache = workbook.PivotCaches.Create(worksheet.Range["A1:F71"]);
IPivotTable calculatedFieldTable = calculatedFieldSheet.PivotTables.Add(pivotCache, calculatedFieldSheet.Range["A1"]);
calculatedFieldTable.PivotFields["Product"].Orientation = PivotFieldOrientation.RowField;
calculatedFieldTable.PivotFields["Amount"].Orientation = PivotFieldOrientation.DataField;
calculatedFieldTable.DataFields["Sum of Amount"].NumberFormat = "$#,##0_);($#,##0)";
        
//集計フィールドを追加します
calculatedFieldTable.CalculatedFields.Add("Tax", "=IF(Amount > 1000, 3% * Amount, 0)");
        
//集計フィールドをデータフィールドとして設定します
calculatedFieldTable.PivotFields["Tax"].Orientation = PivotFieldOrientation.DataField;
calculatedFieldTable.DataFields["Sum of Tax"].NumberFormat = "$#,##0_);($#,##0)";

//指定したフィールドの集計アイテムを取得します
ICalculatedItems countryCalcItems = calculatedItemTable.PivotFields["Country"].CalculatedItems();
ICalculatedItems productCalcItems = calculatedItemTable.PivotFields["Product"].CalculatedItems();

//数式を使用していくつかの集計アイテムを追加します
countryCalcItems.Add("Oceania", "=Country[Australia]+Country[NewZealand]");
countryCalcItems.Add("America", "=Country[Canada]");
IPivotItem myPivotItem = countryCalcItems.Add("Europe", "=Country[France]");

//集計アイテムの数式を変更します
myPivotItem.Formula = "=Country[France]+Country[Germany]";

//定数値を使用して集計アイテムを追加します
productCalcItems.Add("IPhone 13", "=2500");

//CalculatedItemaのカウントを取得します
Console.Write("Calculated Items count : " + countryCalcItems.Count);

//集計アイテムを削除します
countryCalcItems.Remove("America");

IPivotTable percentOfTable = percentOfSheet.PivotTables.Add(pivotCache, percentOfSheet.Range["A1"]);
percentOfTable.PivotFields["Category"].Orientation = PivotFieldOrientation.RowField;
percentOfTable.PivotFields["Product"].Orientation = PivotFieldOrientation.RowField;
percentOfTable.PivotFields["Country"].Orientation = PivotFieldOrientation.ColumnField;
percentOfTable.PivotFields["Amount"].Orientation = PivotFieldOrientation.DataField;
        
//計算の種類、基準フィールド、基準フィールド項目を設定します
IPivotField percentOfTableDataField = percentOfTable.DataFields["Sum of Amount"];
percentOfTableDataField.Calculation = PivotFieldCalculation.PercentOf;
percentOfTableDataField.BaseField = "Country";
percentOfTableDataField.BaseItem = "Australia";
        
percentOfSheet.Range["A:I"].AutoFit();

//レイアウト更新を延期します
pivottable.DeferLayoutUpdate = true;

//ピボットテーブルフィールドを設定します
var field_Category = pivottable.PivotFields["Category"];
field_Category.Orientation = PivotFieldOrientation.RowField;

var field_Product = pivottable.PivotFields["Product"];
field_Product.Orientation = PivotFieldOrientation.ColumnField;

var field_Amount = pivottable.PivotFields["Amount"];
field_Amount.Orientation = PivotFieldOrientation.DataField;

//ピボットテーブルを更新します
pivottable.Update();

//レイアウトと書式オプションを設定します
pivottable.PageFieldOrder = Order.OverThenDown;
pivottable.PageFieldWrapCount = 2;

pivottable.CompactRowIndent = 2;

pivottable.ErrorString = "Error";
pivottable.NullString = "Empty";

pivottable.DisplayErrorString = true;
pivottable.DisplayNullString = true;

//「Product」フィールド内のアイテムを並べ替えます 
field_Product.AutoSort(SortOrder.Descending);

//詳細な範囲を取得して、そのスタイルを設定します
foreach (var item in pivottable.PivotRowAxis.PivotLines)
{
    if (item.LineType == PivotLineType.Subtotal)
    {
        item.PivotLineCells[0].Range.Interior.Color = Color.GreenYellow;
    }
}