Method Details

• getIndex

  int getIndex()

  Gets the index number of this PivotCache within the collection of objects.

  
   worksheet.getRange("A1:B3").setValue(new Object[][] {
       {"Name", "Score"},
       {"Alice", 100},
       {"Bob", 90}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B3"));
   int index = pivotCache.getIndex();
   

  Returns:

  The index number of this PivotCache within the collection of objects.

• getRecordCount

  int getRecordCount()

  Gets the number of records in the PivotTable cache.

  This value reflects the number of source records currently stored in the cache.

  
   worksheet.getRange("A1:B4").setValue(new Object[][] {
       {"Region", "Sales"},
       {"East", 100},
       {"West", 200},
       {"East", 150}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
   int recordCount = pivotCache.getRecordCount();
   

  Returns:

  The number of records in the PivotTable cache.

• getRefreshDate

  @Deprecated Date getRefreshDate()

  Deprecated.

  Use getRefreshLocalDateTime() instead.

  Gets the date on which the PivotTable cache was last refreshed.

  This method is obsolete. Use getRefreshLocalDateTime() instead.

  Returns:

  The date on which the PivotTable cache was last refreshed.

• getRefreshLocalDateTime

  LocalDateTime getRefreshLocalDateTime()

  Gets the local date and time on which the PivotTable cache was last refreshed.

  Use this method to retrieve the refresh timestamp recorded for the cache after calling refresh().

  
   worksheet.getRange("A1:B3").setValue(new Object[][] {
       {"Name", "Score"},
       {"Alice", 100},
       {"Bob", 200}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B3"));
   pivotCache.refresh();
   LocalDateTime refreshTime = pivotCache.getRefreshLocalDateTime();
   

  Returns:

  The local date and time on which the PivotTable cache was last refreshed.

• getRefreshName

  String getRefreshName()

  Gets the name of the person who last refreshed the PivotTable cache.

  Use this method to retrieve the refresh user recorded in the cache metadata.

  
   worksheet.getRange("A1:B3").setValue(new Object[][] {
       {"Name", "Score"},
       {"Alice", 100},
       {"Bob", 200}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B3"));
   pivotCache.refresh();
   String refreshName = pivotCache.getRefreshName();
   

  Returns:

  The name of the person who last refreshed the PivotTable cache.

• getSourceData

  IRange getSourceData()

  Gets the data source for the PivotTable report.

  Returns the IRange currently used by this PivotTable cache as its source data. Use setSourceData(IRange) to change the source range.

  
   worksheet.getRange("A1:C4").setValue(new Object[][] {
       {"Product", "Category", "Amount"},
       {"Apple", "Fruit", 100},
       {"Pear", "Fruit", 200},
       {"Carrot", "Vegetable", 150}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C4"));
   IRange sourceData = pivotCache.getSourceData();
   

  Returns:

  The source data range for the PivotTable report.

• setSourceData

  void setSourceData(IRange value)

  Sets the data source for the PivotTable report.

  Use this method to change the IRange that provides the source data for this pivot cache.

  
   worksheet.getRange("A1:B3").setValue(new Object[][] {
       {"Product", "Amount"},
       {"Apple", 100},
       {"Orange", 200}
   });
   worksheet.getRange("D1:E3").setValue(new Object[][] {
       {"Product", "Amount"},
       {"Banana", 150},
       {"Pear", 180}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B3"));
   pivotCache.setSourceData(worksheet.getRange("D1:E3"));
   

  Parameters:

  value - The range that contains the source data for the PivotTable report.

• createPivotTable

  IPivotTable createPivotTable(IRange TableDestination)

  Creates a PivotTable report based on this PivotCache object.

  The specified destination identifies the upper-left cell of the PivotTable report. The destination range must be on a worksheet in the workbook that contains this PivotCache object.

  
   worksheet.getRange("A1:C4").setValue(new Object[][] {
       {"Product", "Category", "Amount"},
       {"Apple", "Fruit", 100},
       {"Pear", "Fruit", 200},
       {"Carrot", "Vegetable", 150}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C4"));
   IPivotTable pivotTable = pivotCache.createPivotTable(worksheet.getRange("E1"));
   

  Parameters:

  TableDestination - The cell in the upper-left corner of the PivotTable report's destination range. The destination range must be on a worksheet in the workbook that contains this PivotCache object; null is not supported.

  Returns:

  The created IPivotTable object.

• createPivotTable

  IPivotTable createPivotTable(IRange TableDestination, String TableName)

  Creates a new IPivotTable report from this pivot cache at the specified destination.

  The pivot table is created on the worksheet that contains TableDestination. TableDestination specifies the upper-left cell of the destination area where the resulting pivot table will be placed.

  
   worksheet.getRange("A1:C4").setValue(new Object[][] {
       {"Product", "Region", "Amount"},
       {"Apple", "East", 100},
       {"Apple", "West", 120},
       {"Pear", "East", 90}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C4"));
   IPivotTable pivotTable = pivotCache.createPivotTable(worksheet.getRange("E1"), "SalesSummary");
   pivotTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.RowField);
   

  Parameters:

  TableDestination - The upper-left cell of the destination range where the new pivot table report will be placed. This value must not be null.

  TableName - The name of the new pivot table report. This value can be null.

  Returns:

  The newly created IPivotTable.

• refresh

  void refresh()

  Updates the PivotTable cache from its source data.

  Use this method after changing the cache source range to reload the cached records used by PivotTable reports that are based on this IPivotCache.

  
   worksheet.getRange("A1:B3").setValue(new Object[][] {{"Region", "Sales"}, {"East", 120}, {"West", 150}});
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B3"));
   pivotCache.createPivotTable(worksheet.getRange("D1"));
   worksheet.getRange("B3").setValue(180);
   pivotCache.refresh();
   

  Throws:

  IllegalStateException - if the source sheet of the PivotCache cannot be found.

• getRefreshOnFileOpen

  boolean getRefreshOnFileOpen()

  Gets a value indicating whether the PivotTable cache is automatically updated each time the workbook is opened.

  The default value is false.

  
   worksheet.getRange("A1:B4").setValue(new Object[][] {
       {"Category", "Amount"},
       {"Fruit", 100},
       {"Vegetable", 200},
       {"Fruit", 150}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
   pivotCache.setRefreshOnFileOpen(true);
   boolean refreshOnFileOpen = pivotCache.getRefreshOnFileOpen();
   

  Returns:

  true if the PivotTable cache is automatically updated each time the workbook is opened; otherwise, false.

• setRefreshOnFileOpen

  void setRefreshOnFileOpen(boolean value)

  Sets a value indicating whether the PivotTable cache is automatically updated each time the workbook is opened.

  The default value is false.

  
   worksheet.getRange("A1:B4").setValue(new Object[][] {
       {"Category", "Amount"},
       {"Fruit", 100},
       {"Vegetable", 200},
       {"Fruit", 150}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
   pivotCache.setRefreshOnFileOpen(true);
   

  Parameters:

  value - true if the PivotTable cache is automatically updated each time the workbook is opened; otherwise, false.