// 新規ワークブックの作成 var workbook = new GrapeCity.Documents.Excel.Workbook(); object[,] sourceData = new object[,] { { "受注番号", "商品", "カテゴリ", "金額", "日付", "産地" }, { 1, "にんじん", "野菜", 427000, new DateTime(2018, 7, 6), "アメリカ" }, { 2, "ブロッコリー", "野菜", 823900, new DateTime(2018, 7, 7), "イギリス" }, { 3, "バナナ", "果物", 61700, new DateTime(2018, 7, 8), "アメリカ" }, { 4, "バナナ", "果物", 838400, new DateTime(2018, 7, 10), "カナダ" }, { 5, "アスパラガス", "野菜", 262600, new DateTime(2018, 7, 10), "ドイツ" }, { 6, "オレンジ", "果物", 361000, new DateTime(2018, 7, 11), "アメリカ" }, { 7, "ブロッコリー", "野菜", 906200, new DateTime(2018, 7, 11), "オーストラリア" }, { 8, "バナナ", "果物", 690600, new DateTime(2018, 7, 16), "フィリピン" }, { 9, "りんご", "果物", 241700, new DateTime(2018, 7, 16), "日本" }, { 10, "りんご", "果物", 743100, new DateTime(2018, 7, 16), "カナダ" }, { 11, "バナナ", "果物", 825000, new DateTime(2018, 7, 16), "ドイツ" }, { 12, "ブロッコリー", "野菜", 701200, new DateTime(2018, 7, 18), "アメリカ" }, { 13, "にんじん", "野菜", 190300, new DateTime(2018, 7, 20), "ドイツ" }, { 14, "ブロッコリー", "野菜", 282400, new DateTime(2018, 7, 22), "カナダ" }, { 15, "りんご", "果物", 694600, new DateTime(2018, 7, 24), "日本" }, }; IWorksheet worksheet = workbook.Worksheets[0]; worksheet.Range["G1:L16"].Value = sourceData; worksheet.Range["G:L"].ColumnWidth = 15; var pivotcache = workbook.PivotCaches.Create(worksheet.Range["G1:L16"]); var pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range["A1"], "pivottable1"); worksheet.Range["J1:J16"].NumberFormat = "¥#,##0"; var field_product = pivottable.PivotFields[1]; field_product.Orientation = PivotFieldOrientation.RowField; var field_Amount = pivottable.PivotFields[3]; field_Amount.Orientation = PivotFieldOrientation.DataField; field_Amount.NumberFormat = "¥#,##0"; var field_Country = pivottable.PivotFields[5]; field_Country.Orientation = PivotFieldOrientation.PageField; // 行をフィルタ処理 field_product.PivotItems["ブロッコリー"].Visible = false; field_product.PivotItems["バナナ"].Visible = false; field_product.PivotItems["オレンジ"].Visible = false; // 産地をフィルタ処理 field_Country.PivotItems["アメリカ"].Visible = false; field_Country.PivotItems["カナダ"].Visible = false; worksheet.Range["A:B"].EntireColumn.AutoFit(); // xlsx ファイルに保存 workbook.Save("FilterItemsInPivotTable.xlsx");
このデモにはVBコードはありません。