Documents for Excel, Java Edition Documentation
Features / Custom Functions
In This Topic
    Custom Functions
    In This Topic

    GcExcel Java offers extensive support for adding custom functions, thus enabling users to implement custom arithmetic logic to spreadsheets. Custom functions run extremely fast, can be used to make web service calls, work similar to the native Excel functions, and can be used across all Excel platforms including major operating systems (Windows, Mac, Mobile OS and Office: both online and offline).

    For example, you can make use of company¡¯s proprietary functions, apply a nested formula with custom functions, or use a combination of standard built-in functions while handling complex spreadsheet calculations.

    In order to implement custom functions in GcExcel Java, you need to derive a class from the CustomFunction class and declare the custom function in the newly created class along with the function name, return type, and parameters.

    Using Code

    Shared below are some examples of custom functions that can be created and used to perform complex calculation tasks:  

    Note: GcExcel Java doesn't allow users to export custom functions i.e. saving custom functions to an excel file is not supported. If a user tries to do so, the #NAME exception will be thrown.

    Example 1: Conditional Sum Function

    In order to create and use custom conditional sum function in your spreadsheet, refer to the following example code. This function can sum cell values based on the desired display format or style (like cells with interior color as red).

    Java
    Copy Code
    // Step 1- Defining custom function: MyConditionalSum
    // Creating a new class MyConditionalSumFunctionX by inheriting the CustomFunction class
    class MyConditionalSumFunctionX extends CustomFunction 
    {
        public MyConditionalSumFunctionX() 
        {
            super("MyConditionalSum", FunctionValueType.Number, CreateParameters());
        }
        private static Parameter[] CreateParameters() 
        {
            Parameter[] parameters = new Parameter[254];
            for (int i = 0; i < 254; i++) 
            {
                parameters[i] = new Parameter(FunctionValueType.Object, true);
            }
            return parameters;
        }
        @Override
        public Object evaluate(Object[] arguments, ICalcContext context) 
        {
            double sum = 0d;
            for (Object argument : arguments) 
            {
                Iterable<Object> iterator = toIterable(argument);
                for (Object item : iterator) 
                {
                    if (item instanceof CalcError) 
                    {
                        return item;
                    } 
                    else if (item instanceof Double) 
                    {
                        sum += (double) item;
                    }
                }
            }
            return sum;
        }
        private static Iterable<Object> toIterable(Object obj) {
            if (obj instanceof Iterable) 
            {
              return (Iterable) obj;
            } 
            else if (obj instanceof Object[][]) 
            {
                List<Object> list = new ArrayList<Object>();
                Object[][] array = (Object[][]) obj;
                for (int i = 0; i < array.length; i++) 
                {
                    for (int j = 0; j < array[i].length; j++) 
                    {
                        list.add(array[i][j]);
                    }
                }
                return list;
            } 
            else if (obj instanceof CalcReference) 
            {
                List<Object> list = new ArrayList<Object>();
                CalcReference reference = (CalcReference) obj;
                for (IRange range : reference.getRanges()) 
                {
                    int rowCount = range.getRows().getCount();
                    int colCount = range.getColumns().getCount();
                    for (int i = 0; i < rowCount; i++) 
                    {
                        for (int j = 0; j < colCount; j++) 
                        {
                            if (range.getCells().get(i, j).getDisplayFormat().getInterior().getColor().equals(Color.getRed())) 
                            {
                                list.add(range.getCells().get(i, j).getValue());
                            }
                        }
                    }
                }
                return list;
            } 
            else 
            {
                List<Object> list = new ArrayList<Object>();
                list.add(obj);
                return list;
            }
        }
    }
    Java
    Copy Code
    // Step 2: Register the custom function using the AddCustomFunction method.
    Workbook workbook = new Workbook();
    Workbook.AddCustomFunction(new MyConditionalSumFunctionX());
    IWorksheet worksheet = workbook.getActiveSheet();
            
    // Step 3: Implement the custom function
    worksheet.getRange("A1:A10").setValue(new Object[][] 
    {     
    { 1 }, { 2 }, { 3 }, { 4 }, { 5 },
    { 6 }, { 7 }, { 8 }, { 9 }, { 10 } 
    });
    IFormatCondition cellValueRule = (IFormatCondition) worksheet.getRange("A1:A10").getFormatConditions()
    .add(FormatConditionType.CellValue, FormatConditionOperator.Greater, 5, null);
    cellValueRule.getInterior().setColor(Color.getRed());
    // Sum cells value which display format interior color are red.
    worksheet.getRange("C1").setFormula("=MyConditionalSum(A1:A10)");
    // Range["C1"]'s value is 40.
    Object result = worksheet.getRange("C1").getValue();
    // Display result in cell D1
    worksheet.getRange("D1").setValue(result);    

    Example 2: Custom Concatenation Function

    In order to create and use custom concatenation function in your spreadsheet, refer to the following example code.

    Java
    Copy Code
    // Step 1- Defining custom function: MyConcatenate 
    // Creating a new class MyConcatenateFunctionX by inheriting the CustomFunction class
    class MyConcatenateFunctionX extends CustomFunction 
    {
        public MyConcatenateFunctionX() {
            super("MyConcatenate", FunctionValueType.Text, CreateParameters());
        }
        static Parameter[] CreateParameters() 
        {
            Parameter[] parameters = new Parameter[254];
            for (int i = 0; i < 254; i++) 
            {
                parameters[i] = new Parameter(FunctionValueType.Variant);
            }
            return parameters;
        }
        @Override
        public Object evaluate(Object[] arguments, ICalcContext context) 
        {
            StringBuilder sb = new StringBuilder();
            for (Object argument : arguments) 
            {
                if (argument instanceof CalcError) 
                {
                    return argument;
                }
                if (argument instanceof String || argument instanceof Double) {
                    sb.append(argument);
                }
            }
            return sb.toString();
        }
    }
    Java
    Copy Code
    // Step 2: Register the custom function using the AddCustomFunction method.
    Workbook workbook = new Workbook();
    Workbook.AddCustomFunction(new MyConcatenateFunctionX());
    IWorksheet worksheet = workbook.getActiveSheet();
            
    // Step 3: Implement the custom function
    worksheet.getRange("A1").setFormula("=MyConcatenate(\"I\", \" \", \"work\", \" \",
    \"with\", \" \", \"GcExcel\", \".\")");
    worksheet.getRange("A2").setFormula("=MyConcatenate(A1, \"Documents.\")");
    // Value of cell A1 is "I work with GcExcel."
    Object resultA1 = worksheet.getRange("A1").getValue();
    // Value of cell A2 is "I work with GcExcel Documents."
    Object resultA2 = worksheet.getRange("A2").getValue();
    // Display result in cell D1
    worksheet.getRange("D1").setValue(resultA2);

    Example 3: Merged Range Function

    In order to create and use custom merged range function in your spreadsheet, refer to the following example code.

    Java
    Copy Code
    // Step 1- Defining custom function: MyIsMergedRange 
    // Creating a new class MyIsMergedRangeFunctionX by inheriting the CustomFunction class
    class MyIsMergedRangeFunctionX extends CustomFunction 
    {
        public MyIsMergedRangeFunctionX() 
        {
            super("MyIsMergedRange", FunctionValueType.Boolean,
                    new Parameter[] { new Parameter(FunctionValueType.Object, true) });
        }
        @Override
        public Object evaluate(Object[] arguments, ICalcContext context) 
        {
            if (arguments[0] instanceof CalcReference) {
                if (arguments[0] instanceof CalcReference) {
                    List<IRange> ranges = ((CalcReference) arguments[0]).getRanges();
                    for (IRange range : ranges) {
                        return range.getMergeCells();
                    }
                }
            }
            return false;
        }
    }
    Java
    Copy Code
    // Step 2: Register the custom function using the AddCustomFunction method.
    Workbook workbook = new Workbook();
    Workbook.AddCustomFunction(new MyIsMergedRangeFunctionX());
    IWorksheet worksheet = workbook.getActiveSheet();
            
    // Step 3: Implement the custom function
    worksheet.getRange("A1:B2").merge();
    worksheet.getRange("C1").setFormula("=MyIsMergedRange(A1)");
    worksheet.getRange("C2").setFormula("=MyIsMergedRange(H2)");
    // A1 is a merged cell, getRange("C1")'s value is true.
    Object resultC1 = worksheet.getRange("C1").getValue();
    // H2 is not a merged cell, getRange("C2")'s value is false.
    Object resultC2 = worksheet.getRange("C2").getValue();
    // Display result in cell D1
    worksheet.getRange("D1").setValue(resultC2);

    Example 4: Error Detection Function

    In order to create and use custom error detection function in your spreadsheet, refer to the following example code.

    Java
    Copy Code
    // Step 1- Defining custom function: MyIsError 
    // Creating a new class MyIsErrorFunctionX by inheriting the CustomFunction class
    class MyIsErrorFunctionX extends CustomFunction 
    {
              public MyIsErrorFunctionX() 
              {
                  super("MyIsError", FunctionValueType.Boolean, new Parameter[]{new Parameter(FunctionValueType.Variant)});
              }
              @Override
              public Object evaluate(Object[] arguments, ICalcContext context) 
              {
                  if (arguments[0] instanceof CalcError) 
                  {
                      if ((CalcError) arguments[0] != CalcError.None && (CalcError) arguments[0] != CalcError.GettingData) 
                      {
                          return true;
                      } else 
                      {
                          return false;
                      }
                  }
                  return false;
              }
    }
    Java
    Copy Code
    // Step 2: Register the custom function using the AddCustomFunction method.
    Workbook workbook = new Workbook();
    Workbook.AddCustomFunction(new MyIsErrorFunctionX());
    IWorksheet worksheet = workbook.getActiveSheet();
            
    // Step 3: Implement the custom function
    worksheet.getRange("A1").setValue(CalcError.Num);
    worksheet.getRange("A2").setValue(100);
    worksheet.getRange("B1").setFormula("=MyIsError(A1)");
    worksheet.getRange("B2").setFormula("=MyIsError(A2)");
    // getRange("B1")'s value is true.
    Object resultB1 = worksheet.getRange("B1").getValue();
    // getRange("B2")'s value is false.
    Object resultB2 = worksheet.getRange("B2").getValue();
    // Display result in cell D2
    worksheet.getRange("D2").setValue(resultB2);