[]
DEMOS DOCS API PRICING
FREE TRIAL
(Showing Draft Content)

ICalculatedFields

Interface ICalculatedFields

All Superinterfaces:
Iterable<IPivotField>
public interface ICalculatedFields extends Iterable<IPivotField>
A collection of IPivotField objects that represents all the calculated fields in the specified PivotTable report.

Use this collection to access, add, and remove calculated fields for a PivotTable. Each item in the collection represents a calculated field defined in the PivotTable report.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Product", "Amount"},
     {"Apple", 120},
     {"Orange", 90},
     {"Apple", 80}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
 ICalculatedFields calculatedFields = pivotTable.getCalculatedFields();
 calculatedFields.add("DoubleAmount", "=Amount*2");

  • Method Details

    • getCount

      int getCount()
      Returns the number of calculated fields.

      This method returns the number of calculated fields in the collection returned by IPivotTable.getCalculatedFields() for a PivotTable report.

      
 Object[][] sourceData = {
     {"Product", "Amount"},
     {"Apple", 100},
     {"Pear", 200}
 };
 worksheet.getRange("A1:B3").setValue(sourceData);
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B3"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "PivotTable1");
 pivotTable.getCalculatedFields().add("Tax", "=Amount*0.1");
 int count = pivotTable.getCalculatedFields().getCount();
      Returns:
      The number of calculated fields in this collection.

    • get

      IPivotField get(int index)
      Gets the calculated field by index.

      Use this method to retrieve a calculated field from the collection returned by IPivotTable.getCalculatedFields().

      
 worksheet.getRange("A1:B3").setValue(new Object[][] {
     {"Product", "Amount"},
     {"Bike", 100},
     {"Bike", 200}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B3"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"));
 pivotTable.getCalculatedFields().add("Tax", "=Amount*0.1");
 IPivotField field = pivotTable.getCalculatedFields().get(0);
      Parameters:
      index - The index of the calculated field.
      Returns:
      The calculated field.

    • get

      IPivotField get(String fieldName)
      Get calculated field by name.

      Use this method to retrieve a calculated field from the collection returned by IPivotTable.getCalculatedFields().

      
 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Product", "Amount"},
     {"Apple", 120},
     {"Orange", 90},
     {"Apple", 80}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"));
 pivotTable.getCalculatedFields().add("Tax", "=Amount*0.1");
 IPivotField field = pivotTable.getCalculatedFields().get("Tax");
      Parameters:
      fieldName - The name of the calculated field to retrieve.
      Returns:
      The calculated field that matches the specified name.
      Throws:
      IllegalArgumentException - if no calculated field matches fieldName.

    • add

      IPivotField add(String Name, String Formula)
      Creates a new calculated field. Returns a IPivotField object.

      Use this method to add a custom formula-based field to a PivotTable when built-in summary functions are not sufficient. The formula corresponds to the IPivotField.getFormula() value of the created field.

      
 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Product", "Amount"},
     {"A", 1200},
     {"B", 800},
     {"A", 600}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"));
 IPivotField calculatedField = pivotTable.getCalculatedFields().add("Tax", "=IF(Amount > 1000, 3% * Amount, 0)");
 calculatedField.setOrientation(PivotFieldOrientation.DataField);
      Parameters:
      Name - The name of the calculated field.
      Formula - The formula for the calculated field.
      Returns:
      The calculated field.

    • remove

      void remove(String fieldName)
      Removes the calculated fields by name.

      Use this method to delete a calculated field from the calculated field collection of a PivotTable.

      
 worksheet.getRange("A1:B3").setValue(new Object[][] {
     {"Product", "Amount"},
     {"Apple", 100},
     {"Orange", 200}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B3"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "pivotTable1");
 pivotTable.getCalculatedFields().add("Tax", "=Amount*0.1");
 pivotTable.getCalculatedFields().remove("Tax");
      Parameters:
      fieldName - The name of the calculated field to remove.