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
When an error value is typed in cell A1, then the visual function cells update:
Figure 4: Error values showing custom data visualization and style
Samples
The sample code for the QR code visualizer is included in the v13 control explorer demo that is included in the trial download.
If you have any questions about this tutorial, please them them in the comment thread below.
Sean Lawyer