// 新規ワークブックの作成 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["A1:F16"].Value = sourceData; worksheet.Range["A:F"].ColumnWidth = 15; var pivotcache = workbook.PivotCaches.Create(worksheet.Range["A1:F16"]); var pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range["H7"], "pivottable1"); worksheet.Range["D2:D16"].NumberFormat = "¥#,##0"; worksheet.Range["I9:O11"].NumberFormat = "¥#,##0"; worksheet.Range["H:O"].ColumnWidth = 12; // 日本語化 worksheet.Range["E1:E16"].NumberFormat = "yyyy/mm/dd"; pivottable.GrandTotalName = @"総合計"; // ピボットテーブルのフィールドを設定 var field_Category = pivottable.PivotFields["カテゴリ"]; field_Category.Orientation = PivotFieldOrientation.RowField; var field_Product = pivottable.PivotFields["商品"]; field_Product.Orientation = PivotFieldOrientation.ColumnField; var field_Amount = pivottable.PivotFields["金額"]; field_Amount.Orientation = PivotFieldOrientation.DataField; field_Amount.NumberFormat = "$#,##0.00"; var field_Country = pivottable.PivotFields["産地"]; field_Country.Orientation = PivotFieldOrientation.PageField; // xlsx ファイルに保存 workbook.Save("createpivottable.xlsx");
' 新規ワークブックの作成 Dim workbook As New Workbook Dim sourceData As Object(,) = { {"受注 ID", "商品", "カテゴリ", "金額", " 日付", "産地"}, {1, "にんじん", "野菜", 427000, #2018-7-6#, "アメリカ"}, {2, "ブロッコリー", "野菜", 823900, #2018-7-7#, "イギリス"}, {3, "バナナ", "果物", 61700, #2018-7-8#, "アメリカ"}, {4, "バナナ", "果物", 838400, #2018-7-10#, "カナダ"}, {5, "アスパラガス", "野菜", 262600, #2018-7-10#, "ドイツ"}, {6, "オレンジ", "果物", 361000, #2018-7-11#, "アメリカ"}, {7, "ブロッコリー", "野菜", 906200, #2018-7-11#, "オーストラリア"}, {8, "バナナ", "果物", 690600, #2018-7-16#, "フィリピン"}, {9, "りんご", "果物", 241700, #2018-7-16#, "日本"}, {10, "りんご", "果物", 743100, #2018-7-16#, "カナダ"}, {11, "バナナ", "果物", 825000, #2018-7-16#, "ドイツ"}, {12, "ブロッコリー", "野菜", 701200, #2018-7-18#, "アメリカ"}, {13, "にんじん", "野菜", 190300, #2018-7-20#, "ドイツ"}, {14, "ブロッコリー", "野菜", 282400, #2018-7-22#, "カナダ"}, {15, "りんご", "果物", 694600, #2018-7-24#, "日本"} } Dim worksheet As IWorksheet = workbook.Worksheets(0) worksheet.Range("A1:F16").Value = sourceData worksheet.Range("A:F").ColumnWidth = 15 Dim pivotcache = workbook.PivotCaches.Create(worksheet.Range("A1:F16")) Dim pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range!H7, "pivottable1") worksheet.Range("D2:D16").NumberFormat = "\\\\#,##0" worksheet.Range("I9:O11").NumberFormat = "\\\\#,##0" worksheet.Range("H:O").ColumnWidth = 12 'ピボットテーブルのフィールドを設定 Dim field_Category = pivottable.PivotFields("カテゴリ") field_Category.Orientation = PivotFieldOrientation.RowField Dim field_Product = pivottable.PivotFields("商品") field_Product.Orientation = PivotFieldOrientation.ColumnField Dim field_Amount = pivottable.PivotFields("金額") field_Amount.Orientation = PivotFieldOrientation.DataField field_Amount.NumberFormat = "$#,##0.00" Dim field_Country = pivottable.PivotFields("産地") field_Country.Orientation = PivotFieldOrientation.PageField ' xlsx ファイルに保存 workbook.Save("createpivottable.xlsx")