Spread Windows Forms Product Documentation

Creating and Using a Visual Function

Spread for Winforms provides support for working with data visualization functions in the spreadsheet.

Data Visualization functions are custom functions that allows users to add custom logic (either painting the logic or applying the style format logic) to create a visual in a cell that can be used in a formula like custom functions. The IsVisual property of the FunctionVisualizer class can be used to indicate whether the function used in the spreadsheet is a data visualization function or not.

Creating a Data Visualization Function

For creating a data visualization function in the spreadsheet, users need to first create a custom Data Visualizer class that inherits from the FunctionVisualizer class. Further, the ApplyFormat() method and the IsShowCell() method of the FunctionVisualizer class can be used to work with data visualization functions in the spreadsheet.

Refer to the following example code to create a data visualization function.

public class ErrorFunctionVisualizer : FunctionVisualizer
{
protected override void PaintCell(System.Drawing.Graphics graphics,
System.Drawing.Rectangle rect, object cellValue, ref StyleFormat styleFormat, IWorksheet worksheet)
  {
   rect.Width--;
   rect.Height--;
  
     if (cellValue is VisualizationData visualizationData)
     {
       IEvaluationContext context =                      
       worksheet.Workbook.WorkbookSet.
       CalculationEngine.EvaluationContext;
        if (visualizationData.Value.GetValue(context) is GrapeCity.CalcEngine.CalcError)
        {
         System.Drawing.Pen pen =
         new System.Drawing.Pen(System.Drawing.ColorTranslator.
         FromHtml(visualizationData.Parameters[1].GetText(context)));
         pen.Width = 2;
         graphics.DrawEllipse(pen, rect);
         pen.Dispose();
         }
      }
       else if (cellValue is GrapeCity.CalcEngine.CalcError)
      {
       graphics.DrawRectangle(System.Drawing.Pens.Red, rect);
      }
   }
}

Public Class ErrorFunctionVisualizer
    Inherits FunctionVisualizer

    Protected Overrides Sub PaintCell(graphics As System.Drawing.Graphics,
                                       rect As System.Drawing.Rectangle,
                                       cellValue As Object,
                                       ByRef styleFormat As StyleFormat,
                                       worksheet As IWorksheet)

        rect.Width -= 1
        rect.Height -= 1

        If TypeOf cellValue Is VisualizationData Then

            Dim visualizationData As VisualizationData =
                CType(cellValue, VisualizationData)

            Dim context As IEvaluationContext =
                worksheet.Workbook.WorkbookSet.
                CalculationEngine.EvaluationContext

            If TypeOf visualizationData.Value.GetValue(context) Is GrapeCity.CalcEngine.CalcError Then

                Dim pen As New System.Drawing.Pen(
                    System.Drawing.ColorTranslator.FromHtml(
                        visualizationData.Parameters(1).GetText(context)
                    )
                )

                pen.Width = 2
                graphics.DrawEllipse(pen, rect)
                pen.Dispose()

            End If

        ElseIf TypeOf cellValue Is GrapeCity.CalcEngine.CalcError Then

            graphics.DrawRectangle(System.Drawing.Pens.Red, rect)

        End If

    End Sub
End Class

Using a Data Visualization Function

While using a data visualization function, users can assign any name for the function to use it inside the formula but it is important to remember that the prefix "VF" must be used before the name of the data visualization function.

Refer to the following example code to use the existing data visualization function in the spreadsheet.

private void DataVisualizationFunction_Load(object sender, EventArgs e)
{
   /* Using a Data Visualization Function
      To use the FunctionVisualizer, you must create a new
      instance of VisualFunction and pass a new instance of
      the FunctionVisualizer in the constructor */
    
     fpSpread1.AddCustomFunction(new VisualFunction("ERROR",
     1, 2, FunctionAttributes.Variant, new ErrorFunctionVisualizer()));
  
   /* Prefix "VF." is required before the VisualFunction's name
      for using the custom VisualFunction in a cell formula */
    
     fpSpread1.AsWorkbook().ActiveSheet.Cells["B2"].Formula = "VF.ERROR(1/0, A1)";
     fpSpread1.AsWorkbook().ActiveSheet.Cells["A1"].Value =
     System.Drawing.ColorTranslator.ToHtml(System.Drawing.Color.Red);
}

Private Sub DataVisualizationFunction_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    ' Using a Data Visualization Function
    ' To use the FunctionVisualizer, you must create a new
    ' instance of VisualFunction and pass a new instance of
    ' the FunctionVisualizer in the constructor

    fpSpread1.AddCustomFunction(
        New VisualFunction("ERROR",
                           1,
                           2,
                           FunctionAttributes.Variant,
                           New ErrorFunctionVisualizer())
    )

    ' Prefix "VF." is required before the VisualFunction's name
    ' for using the custom VisualFunction in a cell formula

    fpSpread1.AsWorkbook().ActiveSheet.Cells("B2").Formula = "VF.ERROR(1/0, A1)"

    fpSpread1.AsWorkbook().ActiveSheet.Cells("A1").Value =
        System.Drawing.ColorTranslator.ToHtml(System.Drawing.Color.Red)

End Sub