// 新規ワークブックの作成 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:F1"].Value = new string[] { "受注番号", "商品", "カテゴリ", "金額", "日付", "産地" }; IRange dataRange = worksheet.Range["A2:F71"]; dataRange.Value = data; worksheet.Range["E2:E71"].NumberFormat = "yyyy/m/d;@"; // ピボットテーブル用のワークシートを追加 #region ピボットテーブル用のワークシートを追加 IWorksheet general = workbook.Worksheets.Add(); general.Name = "通常"; IWorksheet grandTotalSheet = workbook.Worksheets.Add(); grandTotalSheet.Name = "総計に対する比率"; IWorksheet percentOfSheet = workbook.Worksheets.Add(); percentOfSheet.Name = "基準値に対する比率"; IWorksheet differentSheet = workbook.Worksheets.Add(); differentSheet.Name = "基準値との差分"; IWorksheet percentOfParentColumnSheet = workbook.Worksheets.Add(); percentOfParentColumnSheet.Name = "親列集計に対する比率"; IWorksheet rankSheet = workbook.Worksheets.Add(); rankSheet.Name = "昇順での順位"; #endregion // ピボットキャッシュを追加 IPivotCache pivotCache = workbook.PivotCaches.Create(worksheet.Range["A1:F71"]); #region 通常 IPivotTable normalTable = general.PivotTables.Add(pivotCache, general.Range["A1"]); normalTable.PivotFields["カテゴリ"].Orientation = PivotFieldOrientation.RowField; normalTable.PivotFields["商品"].Orientation = PivotFieldOrientation.RowField; normalTable.PivotFields["産地"].Orientation = PivotFieldOrientation.ColumnField; normalTable.PivotFields["金額"].Orientation = PivotFieldOrientation.DataField; general.Range["A:I"].AutoFit(); #endregion #region 総計に対する比率 IPivotTable grandTotalTable = grandTotalSheet.PivotTables.Add(pivotCache, grandTotalSheet.Range["A1"]); grandTotalTable.PivotFields["カテゴリ"].Orientation = PivotFieldOrientation.RowField; grandTotalTable.PivotFields["商品"].Orientation = PivotFieldOrientation.RowField; grandTotalTable.PivotFields["産地"].Orientation = PivotFieldOrientation.ColumnField; grandTotalTable.PivotFields["金額"].Orientation = PivotFieldOrientation.DataField; // 計算の種類を設定 IPivotField dataField = grandTotalTable.DataFields[0]; dataField.Calculation = PivotFieldCalculation.PercentOfTotal; grandTotalSheet.Range["A:I"].AutoFit(); #endregion #region 基準値（アメリカ）に対する比率 IPivotTable percentOfTable = percentOfSheet.PivotTables.Add(pivotCache, percentOfSheet.Range["A1"]); percentOfTable.PivotFields["カテゴリ"].Orientation = PivotFieldOrientation.RowField; percentOfTable.PivotFields["商品"].Orientation = PivotFieldOrientation.RowField; percentOfTable.PivotFields["産地"].Orientation = PivotFieldOrientation.ColumnField; percentOfTable.PivotFields["金額"].Orientation = PivotFieldOrientation.DataField; // 計算の種類、基準フィールド、基準値を設定 IPivotField percentOfTableDataField = percentOfTable.DataFields[0]; percentOfTableDataField.Calculation = PivotFieldCalculation.PercentOf; percentOfTableDataField.BaseField = "産地"; percentOfTableDataField.BaseItem = "アメリカ"; percentOfSheet.Range["A:I"].AutoFit(); #endregion #region 基準値（アメリカ）との差分 IPivotTable differentTable = differentSheet.PivotTables.Add(pivotCache, differentSheet.Range["A1"]); differentTable.PivotFields["カテゴリ"].Orientation = PivotFieldOrientation.RowField; differentTable.PivotFields["商品"].Orientation = PivotFieldOrientation.RowField; differentTable.PivotFields["産地"].Orientation = PivotFieldOrientation.ColumnField; differentTable.PivotFields["金額"].Orientation = PivotFieldOrientation.DataField; // 計算の種類、基準フィールド、基準値を設定 IPivotField differentField = differentTable.DataFields[0]; differentField.Calculation = PivotFieldCalculation.DifferenceFrom; differentField.BaseField = "産地"; differentField.BaseItem = "アメリカ"; differentSheet.Range["A:I"].AutoFit(); #endregion #region 親列集計に対する比率 IPivotTable percentOfParentColumnTable = percentOfParentColumnSheet.PivotTables.Add(pivotCache, percentOfParentColumnSheet.Range["A1"]); percentOfParentColumnTable.PivotFields["カテゴリ"].Orientation = PivotFieldOrientation.RowField; percentOfParentColumnTable.PivotFields["商品"].Orientation = PivotFieldOrientation.RowField; percentOfParentColumnTable.PivotFields["産地"].Orientation = PivotFieldOrientation.ColumnField; percentOfParentColumnTable.PivotFields["金額"].Orientation = PivotFieldOrientation.DataField; // 計算の種類を設定 IPivotField parentField = percentOfParentColumnTable.DataFields[0]; parentField.Calculation = PivotFieldCalculation.PercentOfParentColumn; percentOfParentColumnSheet.Range["A:I"].AutoFit(); #endregion #region 昇順での順位 IPivotTable rankTable = rankSheet.PivotTables.Add(pivotCache, rankSheet.Range["A1"]); rankTable.PivotFields["カテゴリ"].Orientation = PivotFieldOrientation.RowField; rankTable.PivotFields["商品"].Orientation = PivotFieldOrientation.RowField; rankTable.PivotFields["産地"].Orientation = PivotFieldOrientation.ColumnField; rankTable.PivotFields["金額"].Orientation = PivotFieldOrientation.DataField; // 計算の種類、基準フィールドを設定 IPivotField rankField = rankTable.DataFields[0]; rankField.Calculation = PivotFieldCalculation.RankAscending; rankField.BaseField = "産地"; rankSheet.Range["A:I"].AutoFit(); #endregion general.Activate(); // xlsx ファイルに保存 workbook.Save("ShowValueAs.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:F1").Value = New String() {"受注番号", "商品", "カテゴリ", "金額", "日付", "産地"} Dim dataRange As IRange = worksheet.Range("A2:F71") dataRange.Value = data worksheet.Range("E2:E71").NumberFormat = "yyyy/m/d;@" ' ピボットテーブル用のワークシートを追加 Dim general As IWorksheet = workbook.Worksheets.Add() general.Name = "通常" Dim grandTotalSheet As IWorksheet = workbook.Worksheets.Add() grandTotalSheet.Name = "総計に対する比率" Dim percentOfSheet As IWorksheet = workbook.Worksheets.Add() percentOfSheet.Name = "基準値に対する比率" Dim differentSheet As IWorksheet = workbook.Worksheets.Add() differentSheet.Name = "基準値との差分" Dim percentOfParentColumnSheet As IWorksheet = workbook.Worksheets.Add() percentOfParentColumnSheet.Name = "親列集計に対する比率" Dim rankSheet As IWorksheet = workbook.Worksheets.Add() rankSheet.Name = "昇順での順位" ' ピボットキャッシュを追加 Dim pivotCache As IPivotCache = workbook.PivotCaches.Create(worksheet.Range("A1:F71")) ' 通常 Dim normalTable As IPivotTable = general.PivotTables.Add(pivotCache, general.Range("A1")) normalTable.PivotFields("カテゴリ").Orientation = PivotFieldOrientation.RowField normalTable.PivotFields("商品").Orientation = PivotFieldOrientation.RowField normalTable.PivotFields("産地").Orientation = PivotFieldOrientation.ColumnField normalTable.PivotFields("金額").Orientation = PivotFieldOrientation.DataField general.Range("A:I").AutoFit() ' 総計に対する比率 Dim grandTotalTable As IPivotTable = grandTotalSheet.PivotTables.Add(pivotCache, grandTotalSheet.Range("A1")) grandTotalTable.PivotFields("カテゴリ").Orientation = PivotFieldOrientation.RowField grandTotalTable.PivotFields("商品").Orientation = PivotFieldOrientation.RowField grandTotalTable.PivotFields("産地").Orientation = PivotFieldOrientation.ColumnField grandTotalTable.PivotFields("金額").Orientation = PivotFieldOrientation.DataField ' 計算の種類を設定 Dim dataField As IPivotField = grandTotalTable.DataFields(0) dataField.Calculation = PivotFieldCalculation.PercentOfTotal grandTotalSheet.Range("A:I").AutoFit() ' 基準値（アメリカ）に対する比率 Dim percentOfTable As IPivotTable = percentOfSheet.PivotTables.Add(pivotCache, percentOfSheet.Range("A1")) percentOfTable.PivotFields("カテゴリ").Orientation = PivotFieldOrientation.RowField percentOfTable.PivotFields("商品").Orientation = PivotFieldOrientation.RowField percentOfTable.PivotFields("産地").Orientation = PivotFieldOrientation.ColumnField percentOfTable.PivotFields("金額").Orientation = PivotFieldOrientation.DataField ' 計算の種類、基準フィールド、基準値を設定 Dim percentOfTableDataField As IPivotField = percentOfTable.DataFields(0) percentOfTableDataField.Calculation = PivotFieldCalculation.PercentOf percentOfTableDataField.BaseField = "産地" percentOfTableDataField.BaseItem = "アメリカ" percentOfSheet.Range("A:I").AutoFit() ' 基準値（アメリカ）との差分 Dim differentTable As IPivotTable = differentSheet.PivotTables.Add(pivotCache, differentSheet.Range("A1")) differentTable.PivotFields("カテゴリ").Orientation = PivotFieldOrientation.RowField differentTable.PivotFields("商品").Orientation = PivotFieldOrientation.RowField differentTable.PivotFields("産地").Orientation = PivotFieldOrientation.ColumnField differentTable.PivotFields("金額").Orientation = PivotFieldOrientation.DataField ' 計算の種類、基準フィールド、基準値を設定 Dim differentField As IPivotField = differentTable.DataFields(0) differentField.Calculation = PivotFieldCalculation.DifferenceFrom differentField.BaseField = "産地" differentField.BaseItem = "アメリカ" differentSheet.Range("A:I").AutoFit() ' 親列集計に対する比率 Dim percentOfParentColumnTable As IPivotTable = percentOfParentColumnSheet.PivotTables.Add(pivotCache, percentOfParentColumnSheet.Range("A1")) percentOfParentColumnTable.PivotFields("カテゴリ").Orientation = PivotFieldOrientation.RowField percentOfParentColumnTable.PivotFields("商品").Orientation = PivotFieldOrientation.RowField percentOfParentColumnTable.PivotFields("産地").Orientation = PivotFieldOrientation.ColumnField percentOfParentColumnTable.PivotFields("金額").Orientation = PivotFieldOrientation.DataField ' 計算の種類を設定 Dim parentField As IPivotField = percentOfParentColumnTable.DataFields(0) parentField.Calculation = PivotFieldCalculation.PercentOfParentColumn percentOfParentColumnSheet.Range("A:I").AutoFit() ' 昇順での順位 Dim rankTable As IPivotTable = rankSheet.PivotTables.Add(pivotCache, rankSheet.Range("A1")) rankTable.PivotFields("カテゴリ").Orientation = PivotFieldOrientation.RowField rankTable.PivotFields("商品").Orientation = PivotFieldOrientation.RowField rankTable.PivotFields("産地").Orientation = PivotFieldOrientation.ColumnField rankTable.PivotFields("金額").Orientation = PivotFieldOrientation.DataField ' 計算の種類、基準フィールドを設定 Dim rankField As IPivotField = rankTable.DataFields(0) rankField.Calculation = PivotFieldCalculation.RankAscending rankField.BaseField = "産地" rankSheet.Range("A:I").AutoFit() general.Activate() ' xlsx ファイルに保存 workbook.Save("ShowValueAs.xlsx")