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[0].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); } } }

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[0].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); }

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);
}

Product Support Forum | Print this page