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

CalcReference

Class CalcReference

java.lang.Object
com.grapecity.documents.excel.CalcReference
public class CalcReference extends Object
Represents an area in a spreadsheet.

A CalcReference encapsulates one or more worksheet ranges that are used as a calculation reference. You can use this class to inspect whether the reference spans multiple areas, refers to another workbook, or represents a 3D reference, and to retrieve the referenced IRange objects or their values.


 CustomFunction function = new CustomFunction(
     "REFVALUES",
     FunctionValueType.Object,
     new Parameter[] { new Parameter(FunctionValueType.Object, true, false) }) {
         public Object evaluate(Object[] arguments, ICalcContext context) {
             CalcReference reference = (CalcReference) arguments[0];
             return reference.getValues();
         }
 };
 Workbook.AddCustomFunction(function);
 worksheet.getRange("A1:B2").setValue(new Object[][] {{1, 2}, {3, 4}});
 worksheet.getRange("C1").setFormula2("=REFVALUES(A1:B2)");
 Object result = worksheet.getRange("C1").getValue();

  • Constructor Details

    • CalcReference

      public CalcReference()

  • Method Details

    • getRangeCount

      public final int getRangeCount()
      Gets the count of the ranges in current area. 
      
 CustomFunction function = new CustomFunction(
     "REFRANGECOUNT",
     FunctionValueType.Number,
     new Parameter[] { new Parameter(FunctionValueType.Object, true, false) }) {
         public Object evaluate(Object[] arguments, ICalcContext context) {
             CalcReference reference = (CalcReference) arguments[0];
             return reference.getRangeCount();
         }
 };
 Workbook.AddCustomFunction(function);
 worksheet.getRange("A1:B2").setValue(new Object[][] {{1, 2}, {3, 4}});
 worksheet.getRange("C1").setFormula("=REFRANGECOUNT(A1:B2)");
 Object rangeCount = worksheet.getRange("C1").getValue();
      Returns:
      The range count.

    • getIsExternalReference

      public final boolean getIsExternalReference()
      Gets whether the reference is in another workbook. 
      
 CustomFunction function = new CustomFunction(
     "ISEXTERNALREF",
     FunctionValueType.Boolean,
     new Parameter[] { new Parameter(FunctionValueType.Object, true, false) }) {
         public Object evaluate(Object[] arguments, ICalcContext context) {
             CalcReference reference = (CalcReference) arguments[0];
             return reference.getIsExternalReference();
         }
 };
 Workbook.AddCustomFunction(function);
 worksheet.getRange("A1:B2").setValue(new Object[][] {{1, 2}, {3, 4}});
 worksheet.getRange("C1").setFormula("=ISEXTERNALREF(A1:B2)");
 Object external = worksheet.getRange("C1").getValue();
      Returns:
      True if the reference is an external reference, false otherwise.

    • getIs3DReference

      public final boolean getIs3DReference()
      Gets whether the reference is a 3D reference. 
      
 CustomFunction function = new CustomFunction(
     "IS3DREF",
     FunctionValueType.Boolean,
     new Parameter[] { new Parameter(FunctionValueType.Object, true, false) }) {
         public Object evaluate(Object[] arguments, ICalcContext context) {
             CalcReference reference = (CalcReference) arguments[0];
             return reference.getIs3DReference();
         }
 };
 Workbook.AddCustomFunction(function);
 worksheet.getRange("A1:B2").setValue(new Object[][] {{1, 2}, {3, 4}});
 worksheet.getRange("C1").setFormula("=IS3DREF(A1:B2)");
 Object is3D = worksheet.getRange("C1").getValue();
      Returns:
      True if the reference is a 3D reference, false otherwise.

    • getRanges

      public final List<IRange> getRanges()
      Gets all referenced ranges. 
      
 CustomFunction function = new CustomFunction(
     "REFRANGES",
     FunctionValueType.Number,
     new Parameter[] { new Parameter(FunctionValueType.Object, true, false) }) {
         public Object evaluate(Object[] arguments, ICalcContext context) {
             CalcReference reference = (CalcReference) arguments[0];
             return reference.getRanges().size();
         }
 };
 Workbook.AddCustomFunction(function);
 worksheet.getRange("A1:B2").setValue(new Object[][] {{1, 2}, {3, 4}});
 worksheet.getRange("C1").setFormula("=REFRANGES(A1:B2)");
 Object rangeCount = worksheet.getRange("C1").getValue();
      Returns:
      The range collection.

    • getValues

      public final Object getValues()
      Gets the values of all ranges in this reference.

      Use this method in custom-function evaluation logic to retrieve the values represented by a CalcReference argument.

      
 CustomFunction function = new CustomFunction(
     "REFVALUES",
     FunctionValueType.Object,
     new Parameter[] { new Parameter(FunctionValueType.Object, true, false) }) {
         public Object evaluate(Object[] arguments, ICalcContext context) {
             CalcReference reference = (CalcReference) arguments[0];
             return reference.getValues();
         }
 };
 Workbook.AddCustomFunction(function);
 worksheet.getRange("A1:B2").setValue(new Object[][] {{1, 2}, {3, 4}});
 worksheet.getRange("C1").setFormula2("=REFVALUES(A1:B2)");
 Object result = worksheet.getRange("C1").getValue();
      Returns:
      An object that contains the values of all referenced ranges.