Data Visualization Functions in Spread.NET

January 08 2020
0 Comments

Spread.NET 13 WinForms introduces a new feature that can define a custom function visualizer that draws a custom data visualization in the cell and/or applies a format to the cell. This feature is similar to custom sparklines, but easier to implement using the new Calculation Engine to accept the custom function arguments.

The following topics are covered in this blog:

Creating a Data Visualization Function
Using a Data Visualization Function
Creating a QR Code Data Visualization Function
Applying a Format
Samples

Creating a Data Visualization Function

To use this feature, you must implement a custom data visualizer class which inherits from GrapeCity.Spreadsheet.FunctionVisualizer.

QRCodeFunctionVisualizer

public class QRCodeFunctionVisualizer : FunctionVisualizer
{
    protected override void PaintCell(Graphics graphics, Rectangle rect, object cellValue, ref StyleFormat styleFormat, IWorksheet worksheet)
    {
        ECCLevel eccLevel = ECCLevel.L;
        bool forceUtf8 = false;
        bool utf8BOM = false;
        EciMode eciMode = 0;
        int requestedVersion = -1;
        if (cellValue is VisualizationData visualizationData)
        {
            IEvaluationContext context = worksheet.Workbook.WorkbookSet.CalculationEngine.EvaluationContext;
            string level = visualizationData.Parameters[0].GetText(context);
            eccLevel = (QRCodeGenerator.ECCLevel)(level == "L" ? 0 : level == "M" ? 1 : level == "Q" ? 2 : 3);

            using (QRCodeGenerator qrGenerator = new QRCodeGenerator())
            {
                using (QRCodeData qrCodeData = qrGenerator.CreateQrCode(visualizationData.Value.GetText(context), eccLevel, forceUtf8, utf8BOM, (EciMode)eciMode, requestedVersion))
                {
                    using (QRCode qrCode = new QRCode(qrCodeData))
                    {
                        using (Bitmap image = qrCode.GetGraphic(20, System.Drawing.Color.Black, System.Drawing.Color.White, null, rect.Width))
                        {
                            graphics.DrawImage(image, rect);
                        }
                    }
                }
            }
        }
    }
}

The code above shows how the FunctionVisualizer can access the VisualizationData passed into the function for cellValue and use the EvaluationContext from the CalculationEngine to fetch the function arguments from the VisualizationData.Parameter.

The following code applies the QRCodeFunctionVisualizer to the FpSpread control on the form:

Form_Load

fpSpread1.AddCustomFunction(new VisualFunction("QRCode", 1, 2, FunctionAttributes.Variant, new QRCodeFunctionVisualizer()));
var worksheet = fpSpread1.AsWorkbook().ActiveSheet;
worksheet.Cells["B1"].Value = "/spread";
worksheet.Cells["C1"].Value = "L";
worksheet.Cells["A1"].Formula = "VF.QRCode(B1, C1)";
worksheet.Columns[0].ColumnWidth = 200;
worksheet.Rows[0].RowHeight = 200;

The result shows the QR code in cell A1:

Figure 1: QRCodeFunctionVisualizer in action

Applying a Format

A VisualFunction can apply a format to the cell instead of drawing a visualization. The next example shows a custom FunctionVisualizer which can do both:

CustomFunctionVisualizer

public class CustomFunctionVisualizer : FunctionVisualizer
{
    bool _IsVisual = true;
    bool _IsShowCell = true;
    public CustomFunctionVisualizer(bool isVisual = true)
    {
        _IsVisual = isVisual;
        if (_IsVisual)
        {
            _IsShowCell = false;
        }
    }

    protected override void PaintCell(Graphics graphics, Rectangle rect, object cellValue, ref StyleFormat styleFormat, IWorksheet worksheet)
    {
        rect.Width--;
        rect.Height--;

        if (cellValue is double)
        {
            graphics.DrawRectangle(System.Drawing.Pens.Blue, rect);
            if ((double)cellValue > 0)
            {
                graphics.DrawString("Positive", new System.Drawing.Font("Arial", 10f), Brushes.Blue, new PointF(rect.X, rect.Y));
            }
            else if ((double)cellValue < 0)
            {
                graphics.DrawString("Negative", new System.Drawing.Font("Arial", 10f), Brushes.Blue, new PointF(rect.X, rect.Y));
            }
            else
            {
                graphics.DrawString("Zero", new System.Drawing.Font("Arial", 10f), Brushes.Blue, new PointF(rect.X, rect.Y));
            }
        }
        else if (cellValue is GrapeCity.CalcEngine.CalcError)
        {
            graphics.DrawRectangle(System.Drawing.Pens.Red, rect);
            graphics.DrawString("Error", new System.Drawing.Font("Arial", 10f), Brushes.Blue, new PointF(rect.X, rect.Y));
        }
    }

    public override bool IsShowCell(object cellValue)
    {
        return _IsShowCell;
    }

    public override bool IsVisual
    {
        get
        {
            return _IsVisual;
        }
    }

    public override void ApplyFormat(object cellValue, ref StyleFormat styleFormat)
    {
        if (cellValue is GrapeCity.CalcEngine.CalcError)
        {
            styleFormat.Fill = new Fill(GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Yellow));
            base.ApplyFormat(cellValue, ref styleFormat);
        }
    }
}

The CustomFunctionVisualizer above can operate as either visual and draw a custom data visualization in the cell, or nonvisual and instead apply a yellow fill when the cell value is a CalcError.

The CustomFuctionVisualizer is used with this custom SUMVisualFunction:

SUMVisualFunction

public class SUMVisualFunction : VisualFunction
{
    public SUMVisualFunction(string visualizerName, int minArgs, int maxArgs, FunctionAttributes attributes, IFunctionVisualizer visualizer)
        : base(visualizerName, minArgs, maxArgs, attributes, visualizer)
    {
    }

    protected override bool Evaluate(IArguments arguments, IValue result)
    {
        IEvaluationContext context = arguments.EvaluationContext;
        int count = arguments.Count;
        double sum = 0;

        for (int i = 0; i < count; i++)
        {
            IValue argValue = arguments[i];
            switch (argValue.ValueType)
            {
                case GrapeCity.CalcEngine.ValueType.Error:
                    result.SetValue(argValue.GetError(context));
                    return true;
                case GrapeCity.CalcEngine.ValueType.Reference:
                case GrapeCity.CalcEngine.ValueType.AdjustableReference:
                    RangeReference range = argValue.GetReference(context, 0);
                    Sum(context, range, argValue.GetReferenceSource(context), ref sum, ref result);
                    if (result.ValueType == GrapeCity.CalcEngine.ValueType.Error)
                        return true;
                    break;
                case GrapeCity.CalcEngine.ValueType.MultiReference:
                    int refCount = argValue.ReferenceCount;
                    var sheet = argValue.GetReferenceSource(context);
                    for (int refIndex = 0; refIndex < refCount; refIndex++)
                    {
                        Sum(context, argValue.GetReference(context, refIndex), argValue.GetReferenceSource(context), ref sum, ref result);
                        if (result.ValueType == GrapeCity.CalcEngine.ValueType.Error)
                            return true;
                    }
                    break;
                case GrapeCity.CalcEngine.ValueType.Reference3D:
                    result.SetValue(CalcError.Value);
                    return true;
                case GrapeCity.CalcEngine.ValueType.ValuesArray:
                    sum += Sum(context, argValue.GetValuesArray());
                    break;
                default:
                    sum += argValue.GetNumber(context);
                    break;
            }
        }

        result.SetValue(sum);
        return true;
    }

    private double Sum(IEvaluationContext context, IValuesArray valuesArray)
    {
        double sum = 0.0;
        for (int i = 0; i < valuesArray.RowCount; i++)
        {
            for (int j = 0; j < valuesArray.ColumnCount; j++)
            {
                IPrimitiveValue value = valuesArray[i, j];
                switch (value.ValueType)
                {
                    case PrimitiveValueType.Error:
                        context.Error = value.GetError(context);
                        return 0.0;
                    default:
                        sum += value.GetNumber(context);
                        break;
                }
            }
        }
        return sum;
    }

    private void Sum(IEvaluationContext context, RangeReference range, IReferenceSource sheet, ref double sum, ref IValue result)
    {
        for (int row = range.Row; row <= range.Row2; row++)
        {
            for (int col = range.Column; col <= range.Column2; col++)
            {
                CellValue value = CellValue.Invalid;
                sheet.GetValue(context, row, col, ref value);
                if (value.Type2 == GrapeCity.CalcEngine.ValueType.Error)
                {
                    result.SetValue(value.Error);
                    return;
                }
                sum += value.Number;
            }
        }
        result.SetValue(context, sum);
    }
}

The following code in the -Form_Load_ event initializes the custom SUMVisualFunction:

Form_Load

fpSpread1.AddCustomFunction(new SUMVisualFunction("SUM1", 1, 3, FunctionAttributes.Variant, new CustomFunctionVisualizer()));
fpSpread1.AddCustomFunction(new SUMVisualFunction("SUM2", 1, 3, FunctionAttributes.Variant, new CustomFunctionVisualizer(false)));
IWorksheet sheet = fpSpread1.AsWorkbook().ActiveSheet;
sheet.Cells["A1"].Value = 10;
sheet.Cells["A3"].Value = 20;
sheet.Cells["C3"].Formula = "VF.SUM1(A:A (1:1,3:3))";
sheet.Cells["D4"].Formula = "VF.SUM2(A1,2)";
sheet.Cells["C3"].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Orange);
sheet.Cells["D4"].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Orange);

The cells C3 and D4 show the SUM1 and SUM2 functions evaluating, with the first showing a visualization and the second one showing a style:

Figure 2: SUMVisualFunction in action

Note that the cell style applied to C3 does not show, since the custom visual function returns False for IsShowCell. When the cell is edited, then the style shows while editing the formula:

Figure 3: Cell style shows while editing visual function cell