//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); object[,] data = { {1, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 2300, "1/1/2020", "United Kingdom"}, {2, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 4700, "1/1/2020", "Australia"}, {3, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 2000, "1/1/2020", "NewZealand"}, {4, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 3700, "1/1/2020", "United States"}, {5, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 4000, "1/1/2020", "Germany"}, {6, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 4200, "1/1/2020", "Canada"}, {7, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 3700, "1/1/2020", "France"}, {8, "Mi LED 40inch", "Consumer Electronics", 1500, "1/1/2020", "United Kingdom"}, {9, "Mi LED 40inch", "Consumer Electronics", 2100, "1/1/2020", "Australia"}, {10, "Mi LED 40inch", "Consumer Electronics", 3100, "1/1/2020", "NewZealand"}, {11, "Mi LED 40inch", "Consumer Electronics", 3200, "1/1/2020", "United States"}, {12, "Mi LED 40inch", "Consumer Electronics", 4800, "1/1/2020", "Germany"}, {13, "Mi LED 40inch", "Consumer Electronics", 200, "1/1/2020", "Canada"}, {14, "Mi LED 40inch", "Consumer Electronics", 700, "1/1/2020", "France"}, {15, "Sennheiser HD 4.40-BT", "Consumer Electronics", 900, "1/1/2020", "United Kingdom"}, {16, "Sennheiser HD 4.40-BT", "Consumer Electronics", 3600, "1/1/2020", "Australia"}, {17, "Sennheiser HD 4.40-BT", "Consumer Electronics", 4300, "1/1/2020", "NewZealand"}, {18, "Sennheiser HD 4.40-BT", "Consumer Electronics", 4500, "1/1/2020", "United States"}, {19, "Sennheiser HD 4.40-BT", "Consumer Electronics", 3800, "1/1/2020", "Germany"}, {20, "Sennheiser HD 4.40-BT", "Consumer Electronics", 2700, "1/1/2020", "Canada"}, {21, "Sennheiser HD 4.40-BT", "Consumer Electronics", 1200, "1/1/2020", "France"}, {22, "Iphone XR", "Mobile", 4600, "1/1/2020", "United Kingdom"}, {23, "Iphone XR", "Mobile", 3900, "1/1/2020", "Australia"}, {24, "Iphone XR", "Mobile", 2100, "1/1/2020", "NewZealand"}, {25, "Iphone XR", "Mobile", 2000, "1/1/2020", "United States"}, {26, "Iphone XR", "Mobile", 4000, "1/1/2020", "Germany"}, {27, "Iphone XR", "Mobile", 2900, "1/1/2020", "Canada"}, {28, "Iphone XR", "Mobile", 2200, "1/1/2020", "France"}, {29, "Samsung S9", "Mobile", 1200, "1/1/2020", "United Kingdom"}, {30, "Samsung S9", "Mobile", 2800, "1/1/2020", "Australia"}, {31, "Samsung S9", "Mobile", 1700, "1/1/2020", "NewZealand"}, {32, "Samsung S9", "Mobile", 500, "1/1/2020", "United States"}, {33, "Samsung S9", "Mobile", 200, "1/1/2020", "Germany"}, {34, "Samsung S9", "Mobile", 3900, "1/1/2020", "Canada"}, {35, "Samsung S9", "Mobile", 4800, "1/1/2020", "France"}, {36, "OnePlus 7Pro", "Mobile", 3600, "1/1/2020", "United Kingdom"}, {37, "OnePlus 7Pro", "Mobile", 2700, "1/1/2020", "Australia"}, {38, "OnePlus 7Pro", "Mobile", 1800, "1/1/2020", "NewZealand"}, {39, "OnePlus 7Pro", "Mobile", 2200, "1/1/2020", "United States"}, {40, "OnePlus 7Pro", "Mobile", 900, "1/1/2020", "Germany"}, {41, "OnePlus 7Pro", "Mobile", 600, "1/1/2020", "Canada"}, {42, "OnePlus 7Pro", "Mobile", 2500, "1/1/2020", "France"}, {43, "Redmi 7", "Mobile", 4200, "1/1/2020", "United Kingdom"}, {44, "Redmi 7", "Mobile", 4200, "1/1/2020", "Australia"}, {45, "Redmi 7", "Mobile", 100, "1/1/2020", "NewZealand"}, {46, "Redmi 7", "Mobile", 3300, "1/1/2020", "United States"}, {47, "Redmi 7", "Mobile", 4200, "1/1/2020", "Germany"}, {48, "Redmi 7", "Mobile", 3900, "1/1/2020", "Canada"}, {49, "Redmi 7", "Mobile", 1700, "1/1/2020", "France"}, {50, "Bose 785593-0050", "Consumer Electronics", 2800, "1/1/2020", "United Kingdom"}, {51, "Bose 785593-0050", "Consumer Electronics", 200, "1/1/2020", "Australia"}, {52, "Bose 785593-0050", "Consumer Electronics", 400, "1/1/2020", "NewZealand"}, {53, "Bose 785593-0050", "Consumer Electronics", 4500, "1/1/2020", "United States"}, {54, "Bose 785593-0050", "Consumer Electronics", 3400, "1/1/2020", "Germany"}, {55, "Bose 785593-0050", "Consumer Electronics", 200, "1/1/2020", "Canada"}, {56, "Bose 785593-0050", "Consumer Electronics", 2700, "1/1/2020", "France"}, {57, "Canon EOS 1500D", "Consumer Electronics", 800, "1/1/2020", "United Kingdom"}, {58, "Canon EOS 1500D", "Consumer Electronics", 4800, "1/1/2020", "Australia"}, {59, "Canon EOS 1500D", "Consumer Electronics", 1700, "1/1/2020", "NewZealand"}, {60, "Canon EOS 1500D", "Consumer Electronics", 2500, "1/1/2020", "United States"}, {61, "Canon EOS 1500D", "Consumer Electronics", 1300, "1/1/2020", "Germany"}, {62, "Canon EOS 1500D", "Consumer Electronics", 1500, "1/1/2020", "Canada"}, {63, "Canon EOS 1500D", "Consumer Electronics", 200, "1/1/2020", "France"}, {64, "Haier 394L 4Star", "Consumer Electronics", 4300, "1/1/2020", "United Kingdom"}, {65, "Haier 394L 4Star", "Consumer Electronics", 1300, "1/1/2020", "Australia"}, {66, "Haier 394L 4Star", "Consumer Electronics", 2600, "1/1/2020", "NewZealand"}, {67, "Haier 394L 4Star", "Consumer Electronics", 4600, "1/1/2020", "United States"}, {68, "Haier 394L 4Star", "Consumer Electronics", 1200, "1/1/2020", "Germany"}, {69, "Haier 394L 4Star", "Consumer Electronics", 800, "1/1/2020", "Canada"}, {70, "Haier 394L 4Star", "Consumer Electronics", 2200, "1/1/2020", "France"}, }; IWorksheet worksheet = workbook.Worksheets[0]; worksheet.Name = "Source"; // add data source. worksheet.Range["A1:F1"].Value = new string[] { "Order ID", "Product", "Category", "Amount", "Date", "Country" }; IRange dataRange = worksheet.Range["A2:F71"]; dataRange.Value = data; worksheet.Range["E2:E71"].NumberFormat = "m/d/yyyy;@"; // add worksheets for pivot table. #region add worksheets for pivot table. IWorksheet general = workbook.Worksheets.Add(); general.Name = "General"; IWorksheet grandTotalSheet = workbook.Worksheets.Add(); grandTotalSheet.Name = "Percent Of Grand Total"; IWorksheet percentOfSheet = workbook.Worksheets.Add(); percentOfSheet.Name = "Percent Of"; IWorksheet differentSheet = workbook.Worksheets.Add(); differentSheet.Name = "Different From"; IWorksheet percentOfParentColumnSheet = workbook.Worksheets.Add(); percentOfParentColumnSheet.Name = "Percent Of Parent Column"; IWorksheet rankSheet = workbook.Worksheets.Add(); rankSheet.Name = "Rank Smallest To Largest"; #endregion // add pivot cache. IPivotCache pivotCache = workbook.PivotCaches.Create(worksheet.Range["A1:F71"]); #region general IPivotTable normalTable = general.PivotTables.Add(pivotCache, general.Range["A1"]); normalTable.PivotFields["Category"].Orientation = PivotFieldOrientation.RowField; normalTable.PivotFields["Product"].Orientation = PivotFieldOrientation.RowField; normalTable.PivotFields["Country"].Orientation = PivotFieldOrientation.ColumnField; normalTable.PivotFields["Amount"].Orientation = PivotFieldOrientation.DataField; general.Range["A:I"].AutoFit(); #endregion #region grand total IPivotTable grandTotalTable = grandTotalSheet.PivotTables.Add(pivotCache, grandTotalSheet.Range["A1"]); grandTotalTable.PivotFields["Category"].Orientation = PivotFieldOrientation.RowField; grandTotalTable.PivotFields["Product"].Orientation = PivotFieldOrientation.RowField; grandTotalTable.PivotFields["Country"].Orientation = PivotFieldOrientation.ColumnField; grandTotalTable.PivotFields["Amount"].Orientation = PivotFieldOrientation.DataField; // set show value as IPivotField dataField = grandTotalTable.DataFields[0]; dataField.Calculation = PivotFieldCalculation.PercentOfTotal; grandTotalSheet.Range["A:I"].AutoFit(); #endregion #region different from australia IPivotTable differentTable = differentSheet.PivotTables.Add(pivotCache, differentSheet.Range["A1"]); differentTable.PivotFields["Category"].Orientation = PivotFieldOrientation.RowField; differentTable.PivotFields["Product"].Orientation = PivotFieldOrientation.RowField; differentTable.PivotFields["Country"].Orientation = PivotFieldOrientation.ColumnField; differentTable.PivotFields["Amount"].Orientation = PivotFieldOrientation.DataField; // set show value as, base field, base item. IPivotField differentField = differentTable.DataFields[0]; differentField.Calculation = PivotFieldCalculation.DifferenceFrom; differentField.BaseField = "Country"; differentField.BaseItem = "Australia"; differentSheet.Range["A:I"].AutoFit(); #endregion #region percent of australia 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; // set show value as, base field, base item. IPivotField percentOfTableDataField = percentOfTable.DataFields[0]; percentOfTableDataField.Calculation = PivotFieldCalculation.PercentOf; percentOfTableDataField.BaseField = "Country"; percentOfTableDataField.BaseItem = "Australia"; percentOfSheet.Range["A:I"].AutoFit(); #endregion #region percent of parent row IPivotTable percentOfParentColumnTable = percentOfParentColumnSheet.PivotTables.Add(pivotCache, percentOfParentColumnSheet.Range["A1"]); percentOfParentColumnTable.PivotFields["Category"].Orientation = PivotFieldOrientation.RowField; percentOfParentColumnTable.PivotFields["Product"].Orientation = PivotFieldOrientation.RowField; percentOfParentColumnTable.PivotFields["Country"].Orientation = PivotFieldOrientation.ColumnField; percentOfParentColumnTable.PivotFields["Amount"].Orientation = PivotFieldOrientation.DataField; // set show value as, base field, base item. IPivotField parentField = percentOfParentColumnTable.DataFields[0]; parentField.Calculation = PivotFieldCalculation.PercentOfParentColumn; percentOfParentColumnSheet.Range["A:I"].AutoFit(); #endregion #region rank smallest to largest IPivotTable rankTable = rankSheet.PivotTables.Add(pivotCache, rankSheet.Range["A1"]); rankTable.PivotFields["Category"].Orientation = PivotFieldOrientation.RowField; rankTable.PivotFields["Product"].Orientation = PivotFieldOrientation.RowField; rankTable.PivotFields["Country"].Orientation = PivotFieldOrientation.ColumnField; rankTable.PivotFields["Amount"].Orientation = PivotFieldOrientation.DataField; // set show value as, base field, base item. IPivotField rankField = rankTable.DataFields[0]; rankField.Calculation = PivotFieldCalculation.RankAscending; rankField.BaseField = "Country"; rankSheet.Range["A:I"].AutoFit(); #endregion general.Activate(); // Save to an excel file workbook.Save("ShowValueAs.xlsx");
' Create a new Workbook Dim workbook As New Workbook Dim data As Object(,) = { {1, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 2300, "1/1/2020", "United Kingdom"}, {2, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 4700, "1/1/2020", "Australia"}, {3, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 2000, "1/1/2020", "NewZealand"}, {4, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 3700, "1/1/2020", "United States"}, {5, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 4000, "1/1/2020", "Germany"}, {6, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 4200, "1/1/2020", "Canada"}, {7, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 3700, "1/1/2020", "France"}, {8, "Mi LED 40inch", "Consumer Electronics", 1500, "1/1/2020", "United Kingdom"}, {9, "Mi LED 40inch", "Consumer Electronics", 2100, "1/1/2020", "Australia"}, {10, "Mi LED 40inch", "Consumer Electronics", 3100, "1/1/2020", "NewZealand"}, {11, "Mi LED 40inch", "Consumer Electronics", 3200, "1/1/2020", "United States"}, {12, "Mi LED 40inch", "Consumer Electronics", 4800, "1/1/2020", "Germany"}, {13, "Mi LED 40inch", "Consumer Electronics", 200, "1/1/2020", "Canada"}, {14, "Mi LED 40inch", "Consumer Electronics", 700, "1/1/2020", "France"}, {15, "Sennheiser HD 4.40-BT", "Consumer Electronics", 900, "1/1/2020", "United Kingdom"}, {16, "Sennheiser HD 4.40-BT", "Consumer Electronics", 3600, "1/1/2020", "Australia"}, {17, "Sennheiser HD 4.40-BT", "Consumer Electronics", 4300, "1/1/2020", "NewZealand"}, {18, "Sennheiser HD 4.40-BT", "Consumer Electronics", 4500, "1/1/2020", "United States"}, {19, "Sennheiser HD 4.40-BT", "Consumer Electronics", 3800, "1/1/2020", "Germany"}, {20, "Sennheiser HD 4.40-BT", "Consumer Electronics", 2700, "1/1/2020", "Canada"}, {21, "Sennheiser HD 4.40-BT", "Consumer Electronics", 1200, "1/1/2020", "France"}, {22, "Iphone XR", "Mobile", 4600, "1/1/2020", "United Kingdom"}, {23, "Iphone XR", "Mobile", 3900, "1/1/2020", "Australia"}, {24, "Iphone XR", "Mobile", 2100, "1/1/2020", "NewZealand"}, {25, "Iphone XR", "Mobile", 2000, "1/1/2020", "United States"}, {26, "Iphone XR", "Mobile", 4000, "1/1/2020", "Germany"}, {27, "Iphone XR", "Mobile", 2900, "1/1/2020", "Canada"}, {28, "Iphone XR", "Mobile", 2200, "1/1/2020", "France"}, {29, "Samsung S9", "Mobile", 1200, "1/1/2020", "United Kingdom"}, {30, "Samsung S9", "Mobile", 2800, "1/1/2020", "Australia"}, {31, "Samsung S9", "Mobile", 1700, "1/1/2020", "NewZealand"}, {32, "Samsung S9", "Mobile", 500, "1/1/2020", "United States"}, {33, "Samsung S9", "Mobile", 200, "1/1/2020", "Germany"}, {34, "Samsung S9", "Mobile", 3900, "1/1/2020", "Canada"}, {35, "Samsung S9", "Mobile", 4800, "1/1/2020", "France"}, {36, "OnePlus 7Pro", "Mobile", 3600, "1/1/2020", "United Kingdom"}, {37, "OnePlus 7Pro", "Mobile", 2700, "1/1/2020", "Australia"}, {38, "OnePlus 7Pro", "Mobile", 1800, "1/1/2020", "NewZealand"}, {39, "OnePlus 7Pro", "Mobile", 2200, "1/1/2020", "United States"}, {40, "OnePlus 7Pro", "Mobile", 900, "1/1/2020", "Germany"}, {41, "OnePlus 7Pro", "Mobile", 600, "1/1/2020", "Canada"}, {42, "OnePlus 7Pro", "Mobile", 2500, "1/1/2020", "France"}, {43, "Redmi 7", "Mobile", 4200, "1/1/2020", "United Kingdom"}, {44, "Redmi 7", "Mobile", 4200, "1/1/2020", "Australia"}, {45, "Redmi 7", "Mobile", 100, "1/1/2020", "NewZealand"}, {46, "Redmi 7", "Mobile", 3300, "1/1/2020", "United States"}, {47, "Redmi 7", "Mobile", 4200, "1/1/2020", "Germany"}, {48, "Redmi 7", "Mobile", 3900, "1/1/2020", "Canada"}, {49, "Redmi 7", "Mobile", 1700, "1/1/2020", "France"}, {50, "Bose 785593-0050", "Consumer Electronics", 2800, "1/1/2020", "United Kingdom"}, {51, "Bose 785593-0050", "Consumer Electronics", 200, "1/1/2020", "Australia"}, {52, "Bose 785593-0050", "Consumer Electronics", 400, "1/1/2020", "NewZealand"}, {53, "Bose 785593-0050", "Consumer Electronics", 4500, "1/1/2020", "United States"}, {54, "Bose 785593-0050", "Consumer Electronics", 3400, "1/1/2020", "Germany"}, {55, "Bose 785593-0050", "Consumer Electronics", 200, "1/1/2020", "Canada"}, {56, "Bose 785593-0050", "Consumer Electronics", 2700, "1/1/2020", "France"}, {57, "Canon EOS 1500D", "Consumer Electronics", 800, "1/1/2020", "United Kingdom"}, {58, "Canon EOS 1500D", "Consumer Electronics", 4800, "1/1/2020", "Australia"}, {59, "Canon EOS 1500D", "Consumer Electronics", 1700, "1/1/2020", "NewZealand"}, {60, "Canon EOS 1500D", "Consumer Electronics", 2500, "1/1/2020", "United States"}, {61, "Canon EOS 1500D", "Consumer Electronics", 1300, "1/1/2020", "Germany"}, {62, "Canon EOS 1500D", "Consumer Electronics", 1500, "1/1/2020", "Canada"}, {63, "Canon EOS 1500D", "Consumer Electronics", 200, "1/1/2020", "France"}, {64, "Haier 394L 4Star", "Consumer Electronics", 4300, "1/1/2020", "United Kingdom"}, {65, "Haier 394L 4Star", "Consumer Electronics", 1300, "1/1/2020", "Australia"}, {66, "Haier 394L 4Star", "Consumer Electronics", 2600, "1/1/2020", "NewZealand"}, {67, "Haier 394L 4Star", "Consumer Electronics", 4600, "1/1/2020", "United States"}, {68, "Haier 394L 4Star", "Consumer Electronics", 1200, "1/1/2020", "Germany"}, {69, "Haier 394L 4Star", "Consumer Electronics", 800, "1/1/2020", "Canada"}, {70, "Haier 394L 4Star", "Consumer Electronics", 2200, "1/1/2020", "France"}} Dim worksheet As IWorksheet = workbook.Worksheets(0) worksheet.Name = "Source" worksheet.Range("A1:F1").Value = New String() {"Order ID", "Product", "Category", "Amount", "Date", "Country"} Dim dataRange As IRange = worksheet.Range("A2:F71") dataRange.Value = data worksheet.Range("E2:E71").NumberFormat = "m/d/yyyy;@" Dim general As IWorksheet = workbook.Worksheets.Add() general.Name = "General" Dim grandTotalSheet As IWorksheet = workbook.Worksheets.Add() grandTotalSheet.Name = "Percent Of Grand Total" Dim percentOfSheet As IWorksheet = workbook.Worksheets.Add() percentOfSheet.Name = "Percent Of" Dim differentSheet As IWorksheet = workbook.Worksheets.Add() differentSheet.Name = "Different From" Dim percentOfParentColumnSheet As IWorksheet = workbook.Worksheets.Add() percentOfParentColumnSheet.Name = "Percent Of Parent Column" Dim rankSheet As IWorksheet = workbook.Worksheets.Add() rankSheet.Name = "Rank Smallest To Largest" Dim pivotCache As IPivotCache = workbook.PivotCaches.Create(worksheet.Range("A1:F71")) Dim normalTable As IPivotTable = general.PivotTables.Add(pivotCache, general.Range("A1")) normalTable.PivotFields("Category").Orientation = PivotFieldOrientation.RowField normalTable.PivotFields("Product").Orientation = PivotFieldOrientation.RowField normalTable.PivotFields("Country").Orientation = PivotFieldOrientation.ColumnField normalTable.PivotFields("Amount").Orientation = PivotFieldOrientation.DataField general.Range("A:I").AutoFit() Dim grandTotalTable As IPivotTable = grandTotalSheet.PivotTables.Add(pivotCache, grandTotalSheet.Range("A1")) grandTotalTable.PivotFields("Category").Orientation = PivotFieldOrientation.RowField grandTotalTable.PivotFields("Product").Orientation = PivotFieldOrientation.RowField grandTotalTable.PivotFields("Country").Orientation = PivotFieldOrientation.ColumnField grandTotalTable.PivotFields("Amount").Orientation = PivotFieldOrientation.DataField Dim dataField As IPivotField = grandTotalTable.DataFields(0) dataField.Calculation = PivotFieldCalculation.PercentOfTotal grandTotalSheet.Range("A:I").AutoFit() Dim differentTable As IPivotTable = differentSheet.PivotTables.Add(pivotCache, differentSheet.Range("A1")) differentTable.PivotFields("Category").Orientation = PivotFieldOrientation.RowField differentTable.PivotFields("Product").Orientation = PivotFieldOrientation.RowField differentTable.PivotFields("Country").Orientation = PivotFieldOrientation.ColumnField differentTable.PivotFields("Amount").Orientation = PivotFieldOrientation.DataField Dim differentField As IPivotField = differentTable.DataFields(0) differentField.Calculation = PivotFieldCalculation.DifferenceFrom differentField.BaseField = "Country" differentField.BaseItem = "Australia" differentSheet.Range("A:I").AutoFit() Dim percentOfTable As IPivotTable = 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 Dim percentOfTableDataField As IPivotField = percentOfTable.DataFields(0) percentOfTableDataField.Calculation = PivotFieldCalculation.PercentOf percentOfTableDataField.BaseField = "Country" percentOfTableDataField.BaseItem = "Australia" percentOfSheet.Range("A:I").AutoFit() Dim percentOfParentColumnTable As IPivotTable = percentOfParentColumnSheet.PivotTables.Add(pivotCache, percentOfParentColumnSheet.Range("A1")) percentOfParentColumnTable.PivotFields("Category").Orientation = PivotFieldOrientation.RowField percentOfParentColumnTable.PivotFields("Product").Orientation = PivotFieldOrientation.RowField percentOfParentColumnTable.PivotFields("Country").Orientation = PivotFieldOrientation.ColumnField percentOfParentColumnTable.PivotFields("Amount").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("Category").Orientation = PivotFieldOrientation.RowField rankTable.PivotFields("Product").Orientation = PivotFieldOrientation.RowField rankTable.PivotFields("Country").Orientation = PivotFieldOrientation.ColumnField rankTable.PivotFields("Amount").Orientation = PivotFieldOrientation.DataField Dim rankField As IPivotField = rankTable.DataFields(0) rankField.Calculation = PivotFieldCalculation.RankAscending rankField.BaseField = "Country" rankSheet.Range("A:I").AutoFit() general.Activate() ' save to an excel file workbook.Save("ShowValueAs.xlsx")