Spread Windows Forms 17
Spread Windows Forms 17.0 Product Documentation / Developer's Guide / Formulas in Cells / Creating and Using a Visual Function
In This Topic
    Creating and Using a Visual Function
    In This Topic

    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.

    C#
    Copy Code 
    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);
      }
   }
}

    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.

    C#
    Copy Code 
     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);
}