// 新規ワークブックの作成 var workbook = new GrapeCity.Documents.Excel.Workbook(); object[,] data = { { "受注番号", "商品", "カテゴリ", "金額", "日付", "産地" }, { 1, "にんじん", "野菜", 230000, "2020/1/1", "イギリス"}, { 2, "にんじん", "野菜", 470000, "2020/1/1", "オーストラリア"}, { 3, "にんじん", "野菜", 200000, "2020/1/1", "日本"}, { 4, "にんじん", "野菜", 370000, "2020/1/1", "アメリカ"}, { 5, "にんじん", "野菜", 400000, "2020/1/1", "ドイツ"}, { 6, "にんじん", "野菜", 420000, "2020/1/1", "カナダ"}, { 7, "にんじん", "野菜", 370000, "2020/1/1", "フランス"}, { 8, "ブロッコリー", "野菜", 150000, "2020/1/1", "イギリス"}, { 9, "ブロッコリー", "野菜", 210000, "2020/1/1", "オーストラリア"}, { 10, "ブロッコリー", "野菜", 310000, "2020/1/1", "日本"}, { 11, "ブロッコリー", "野菜", 320000, "2020/1/1", "アメリカ"}, { 12, "ブロッコリー", "野菜", 480000, "2020/1/1", "ドイツ"}, { 13, "ブロッコリー", "野菜", 20000, "2020/1/1", "カナダ"}, { 14, "ブロッコリー", "野菜", 70000, "2020/1/1", "フランス"}, { 15, "アスパラガス", "野菜", 90000, "2020/1/1", "イギリス"}, { 16, "アスパラガス", "野菜", 360000, "2020/1/1", "オーストラリア"}, { 17, "アスパラガス", "野菜", 430000, "2020/1/1", "日本"}, { 18, "アスパラガス", "野菜", 450000, "2020/1/1", "アメリカ"}, { 19, "アスパラガス", "野菜", 380000, "2020/1/1", "ドイツ"}, { 20, "アスパラガス", "野菜", 270000, "2020/1/1", "カナダ"}, { 21, "アスパラガス", "野菜", 120000, "2020/1/1", "フランス"}, { 22, "バナナ", "果物", 460000, "2020/1/1", "イギリス"}, { 23, "バナナ", "果物", 390000, "2020/1/1", "オーストラリア"}, { 24, "バナナ", "果物", 210000, "2020/1/1", "日本"}, { 25, "バナナ", "果物", 200000, "2020/1/1", "アメリカ"}, { 26, "バナナ", "果物", 400000, "2020/1/1", "ドイツ"}, { 27, "バナナ", "果物", 290000, "2020/1/1", "カナダ"}, { 28, "バナナ", "果物", 220000, "2020/1/1", "フランス"}, { 29, "オレンジ", "果物", 120000, "2020/1/1", "イギリス"}, { 30, "オレンジ", "果物", 280000, "2020/1/1", "オーストラリア"}, { 31, "オレンジ", "果物", 170000, "2020/1/1", "日本"}, { 32, "オレンジ", "果物", 50000, "2020/1/1", "アメリカ"}, { 33, "オレンジ", "果物", 20000, "2020/1/1", "ドイツ"}, { 34, "オレンジ", "果物", 390000, "2020/1/1", "カナダ"}, { 35, "オレンジ", "果物", 480000, "2020/1/1", "フランス"}, { 36, "りんご", "果物", 360000, "2020/1/1", "イギリス"}, { 37, "りんご", "果物", 270000, "2020/1/1", "オーストラリア"}, { 38, "りんご", "果物", 180000, "2020/1/1", "日本"}, { 39, "りんご", "果物", 220000, "2020/1/1", "アメリカ"}, { 40, "りんご", "果物", 90000, "2020/1/1", "ドイツ"}, { 41, "りんご", "果物", 60000, "2020/1/1", "カナダ"}, { 42, "りんご", "果物", 250000, "2020/1/1", "フランス"}, { 43, "グレープ", "果物", 420000, "2020/1/1", "イギリス"}, { 44, "グレープ", "果物", 420000, "2020/1/1", "オーストラリア"}, { 45, "グレープ", "果物", 10000, "2020/1/1", "日本"}, { 46, "グレープ", "果物", 330000, "2020/1/1", "アメリカ"}, { 47, "グレープ", "果物", 420000, "2020/1/1", "ドイツ"}, { 48, "グレープ", "果物", 390000, "2020/1/1", "カナダ"}, { 49, "グレープ", "果物", 170000, "2020/1/1", "フランス"}, { 50, "たまねぎ", "野菜", 280000, "2020/1/1", "イギリス"}, { 51, "たまねぎ", "野菜", 20000, "2020/1/1", "オーストラリア"}, { 52, "たまねぎ", "野菜", 40000, "2020/1/1", "日本"}, { 53, "たまねぎ", "野菜", 450000, "2020/1/1", "アメリカ"}, { 54, "たまねぎ", "野菜", 340000, "2020/1/1", "ドイツ"}, { 55, "たまねぎ", "野菜", 20000, "2020/1/1", "カナダ"}, { 56, "たまねぎ", "野菜", 270000, "2020/1/1", "フランス"}, { 57, "トマト", "野菜", 80000, "2020/1/1", "イギリス"}, { 58, "トマト", "野菜", 480000, "2020/1/1", "オーストラリア"}, { 59, "トマト", "野菜", 170000, "2020/1/1", "日本"}, { 60, "トマト", "野菜", 250000, "2020/1/1", "アメリカ"}, { 61, "トマト", "野菜", 130000, "2020/1/1", "ドイツ"}, { 62, "トマト", "野菜", 150000, "2020/1/1", "カナダ"}, { 63, "トマト", "野菜", 20000, "2020/1/1", "フランス"}, { 64, "キャベツ", "野菜", 430000, "2020/1/1", "イギリス"}, { 65, "キャベツ", "野菜", 130000, "2020/1/1", "オーストラリア"}, { 66, "キャベツ", "野菜", 260000, "2020/1/1", "日本"}, { 67, "キャベツ", "野菜", 460000, "2020/1/1", "アメリカ"}, { 68, "キャベツ", "野菜", 120000, "2020/1/1", "ドイツ"}, { 69, "キャベツ", "野菜", 80000, "2020/1/1", "カナダ"}, { 70, "キャベツ", "野菜", 220000, "2020/1/1", "フランス"}, }; IWorksheet worksheet = workbook.Worksheets[0]; worksheet.Name = "ソース"; // ピボットテーブルのデータソースを追加 worksheet.Range["A1:F71"].Value = data; worksheet.Range["E2:E71"].NumberFormat = "yyyy/m/d;@"; worksheet.Range["D2:D71"].NumberFormat = "¥#,##0_);(¥#,##0)"; IWorksheet calculatedFieldSheet = workbook.Worksheets.Add(); calculatedFieldSheet.Name = "集計フィールド"; // ピボットテーブルを追加 IPivotCache pivotCache = workbook.PivotCaches.Create(worksheet.Range["A1:F71"]); IPivotTable calculatedFieldTable = calculatedFieldSheet.PivotTables.Add(pivotCache, calculatedFieldSheet.Range["A1"]); calculatedFieldTable.PivotFields["商品"].Orientation = PivotFieldOrientation.RowField; calculatedFieldTable.PivotFields["金額"].Orientation = PivotFieldOrientation.DataField; calculatedFieldTable.DataFields[0].NumberFormat = "¥#,##0_);(¥#,##0)"; // 集計フィールドを追加 calculatedFieldTable.CalculatedFields.Add("税金", "=金額 * 8%"); // 集計フィールドをデータフィールドとして設定 calculatedFieldTable.PivotFields["税金"].Orientation = PivotFieldOrientation.DataField; calculatedFieldTable.DataFields[1].NumberFormat = "¥#,##0_);(¥#,##0)"; calculatedFieldSheet.Range["A:I"].AutoFit(); calculatedFieldSheet.Activate(); // xlsx ファイルに保存 workbook.Save("CalculatedField.xlsx");
' 新規ワークブックの作成 Dim workbook As New Workbook Dim data As Object(,) = { {"受注番号", "商品", "カテゴリ", "金額", "日付", "産地"}, {1, "にんじん", "野菜", 230000, "2020/1/1", "イギリス"}, {2, "にんじん", "野菜", 470000, "2020/1/1", "オーストラリア"}, {3, "にんじん", "野菜", 200000, "2020/1/1", "日本"}, {4, "にんじん", "野菜", 370000, "2020/1/1", "アメリカ"}, {5, "にんじん", "野菜", 400000, "2020/1/1", "ドイツ"}, {6, "にんじん", "野菜", 420000, "2020/1/1", "カナダ"}, {7, "にんじん", "野菜", 370000, "2020/1/1", "フランス"}, {8, "ブロッコリー", "野菜", 150000, "2020/1/1", "イギリス"}, {9, "ブロッコリー", "野菜", 210000, "2020/1/1", "オーストラリア"}, {10, "ブロッコリー", "野菜", 310000, "2020/1/1", "日本"}, {11, "ブロッコリー", "野菜", 320000, "2020/1/1", "アメリカ"}, {12, "ブロッコリー", "野菜", 480000, "2020/1/1", "ドイツ"}, {13, "ブロッコリー", "野菜", 20000, "2020/1/1", "カナダ"}, {14, "ブロッコリー", "野菜", 70000, "2020/1/1", "フランス"}, {15, "アスパラガス", "野菜", 90000, "2020/1/1", "イギリス"}, {16, "アスパラガス", "野菜", 360000, "2020/1/1", "オーストラリア"}, {17, "アスパラガス", "野菜", 430000, "2020/1/1", "日本"}, {18, "アスパラガス", "野菜", 450000, "2020/1/1", "アメリカ"}, {19, "アスパラガス", "野菜", 380000, "2020/1/1", "ドイツ"}, {20, "アスパラガス", "野菜", 270000, "2020/1/1", "カナダ"}, {21, "アスパラガス", "野菜", 120000, "2020/1/1", "フランス"}, {22, "バナナ", "果物", 460000, "2020/1/1", "イギリス"}, {23, "バナナ", "果物", 390000, "2020/1/1", "オーストラリア"}, {24, "バナナ", "果物", 210000, "2020/1/1", "日本"}, {25, "バナナ", "果物", 200000, "2020/1/1", "アメリカ"}, {26, "バナナ", "果物", 400000, "2020/1/1", "ドイツ"}, {27, "バナナ", "果物", 290000, "2020/1/1", "カナダ"}, {28, "バナナ", "果物", 220000, "2020/1/1", "フランス"}, {29, "オレンジ", "果物", 120000, "2020/1/1", "イギリス"}, {30, "オレンジ", "果物", 280000, "2020/1/1", "オーストラリア"}, {31, "オレンジ", "果物", 170000, "2020/1/1", "日本"}, {32, "オレンジ", "果物", 50000, "2020/1/1", "アメリカ"}, {33, "オレンジ", "果物", 20000, "2020/1/1", "ドイツ"}, {34, "オレンジ", "果物", 390000, "2020/1/1", "カナダ"}, {35, "オレンジ", "果物", 480000, "2020/1/1", "フランス"}, {36, "りんご", "果物", 360000, "2020/1/1", "イギリス"}, {37, "りんご", "果物", 270000, "2020/1/1", "オーストラリア"}, {38, "りんご", "果物", 180000, "2020/1/1", "日本"}, {39, "りんご", "果物", 220000, "2020/1/1", "アメリカ"}, {40, "りんご", "果物", 90000, "2020/1/1", "ドイツ"}, {41, "りんご", "果物", 60000, "2020/1/1", "カナダ"}, {42, "りんご", "果物", 250000, "2020/1/1", "フランス"}, {43, "グレープ", "果物", 420000, "2020/1/1", "イギリス"}, {44, "グレープ", "果物", 420000, "2020/1/1", "オーストラリア"}, {45, "グレープ", "果物", 10000, "2020/1/1", "日本"}, {46, "グレープ", "果物", 330000, "2020/1/1", "アメリカ"}, {47, "グレープ", "果物", 420000, "2020/1/1", "ドイツ"}, {48, "グレープ", "果物", 390000, "2020/1/1", "カナダ"}, {49, "グレープ", "果物", 170000, "2020/1/1", "フランス"}, {50, "たまねぎ", "野菜", 280000, "2020/1/1", "イギリス"}, {51, "たまねぎ", "野菜", 20000, "2020/1/1", "オーストラリア"}, {52, "たまねぎ", "野菜", 40000, "2020/1/1", "日本"}, {53, "たまねぎ", "野菜", 450000, "2020/1/1", "アメリカ"}, {54, "たまねぎ", "野菜", 340000, "2020/1/1", "ドイツ"}, {55, "たまねぎ", "野菜", 20000, "2020/1/1", "カナダ"}, {56, "たまねぎ", "野菜", 270000, "2020/1/1", "フランス"}, {57, "トマト", "野菜", 80000, "2020/1/1", "イギリス"}, {58, "トマト", "野菜", 480000, "2020/1/1", "オーストラリア"}, {59, "トマト", "野菜", 170000, "2020/1/1", "日本"}, {60, "トマト", "野菜", 250000, "2020/1/1", "アメリカ"}, {61, "トマト", "野菜", 130000, "2020/1/1", "ドイツ"}, {62, "トマト", "野菜", 150000, "2020/1/1", "カナダ"}, {63, "トマト", "野菜", 20000, "2020/1/1", "フランス"}, {64, "キャベツ", "野菜", 430000, "2020/1/1", "イギリス"}, {65, "キャベツ", "野菜", 130000, "2020/1/1", "オーストラリア"}, {66, "キャベツ", "野菜", 260000, "2020/1/1", "日本"}, {67, "キャベツ", "野菜", 460000, "2020/1/1", "アメリカ"}, {68, "キャベツ", "野菜", 120000, "2020/1/1", "ドイツ"}, {69, "キャベツ", "野菜", 80000, "2020/1/1", "カナダ"}, {70, "キャベツ", "野菜", 220000, "2020/1/1", "フランス"}} Dim worksheet As IWorksheet = workbook.Worksheets(0) worksheet.Name = "ソース" ' ピボットテーブルのデータソースを追加 worksheet.Range("A1:F71").Value = data worksheet.Range("E2:E71").NumberFormat = "yyyy/m/d;@" worksheet.Range("D2:D71").NumberFormat = "¥#,##0_);(¥#,##0)" Dim calculatedFieldSheet As IWorksheet = workbook.Worksheets.Add() calculatedFieldSheet.Name = "集計フィールド" ' ピボットテーブルを追加 Dim pivotCache As IPivotCache = workbook.PivotCaches.Create(worksheet.Range("A1:F71")) Dim calculatedFieldTable As IPivotTable = calculatedFieldSheet.PivotTables.Add(pivotCache, calculatedFieldSheet.Range("A1")) calculatedFieldTable.PivotFields("商品").Orientation = PivotFieldOrientation.RowField calculatedFieldTable.PivotFields("金額").Orientation = PivotFieldOrientation.DataField calculatedFieldTable.DataFields(0).NumberFormat = "¥#,##0_);(¥#,##0)" ' 集計フィールドを追加 calculatedFieldTable.CalculatedFields.Add("税金", "=金額 * 8%") ' 集計フィールドをデータフィールドとして設定 calculatedFieldTable.PivotFields("税金").Orientation = PivotFieldOrientation.DataField calculatedFieldTable.DataFields(1).NumberFormat = "¥#,##0_);(¥#,##0)" calculatedFieldSheet.Range("A:I").AutoFit() calculatedFieldSheet.Activate() ' xlsx ファイルに保存 workbook.Save("CalculatedField.xlsx")