|
| xlsgen > overview > Visual components |
|
Visual components
xlsgen has an open architecture which allows general-purpose ActiveX controls to be instantiated, parametrized using Excel formulas, and create snapshot pictures in Excel.
Visual components are a very powerful feature of xlsgen that goes beyond Excel capabilities :
The xlsgen install has a sample code called visual component written in no less than 7 programming languages, showing the basics of visual components.
Also, xlsgen installs four such controls :
A Vumeter
A vumeter is an ActiveX control whose purpose is to provide a clue of where a target value is inside boundaries. A vumeter can be customized like changing colors in order to put an emphasis, adding a title, and so on.
What identifies the vumeter as an ActiveX control is the unique identifier : Vumeter.ARsTdesign.1.
Here is an example of how to instantiate a vumeter using xlsgen code.
| Java code |
// create a vumeter, and customize it
XlsWorksheet wksht = workbook.AddWorksheet("sheet");
// NewVisualComponent(progId, row1, col1, row2, col2,
// topoffset, leftoffset, bottomoffset, rightoffset)
XlsVisualComponent compVumeter = wksht.NewVisualComponent("Vumeter.ARsTdesign.1",
1,1, 10,3, 0,0,0,0);
wksht.putNumber(4,4, 65);
// setting parameter: Value
compVumeter.getParameterByName("SetValue").putFormula("=$D$4");
// reading parameter: Value
String value = compVumeter.getParameterByName("GetValue").getFormula();
// setting parameter: Title
compVumeter.getParameterByName("SetTitle").putValueAsLabel("(revenue)");
// setting parameter: ColorRightSlice
compVumeter.getParameterByName("SetColorRightSlice").putFormula("=8947967"); // RGB shade of blue (8947967 = 0x8888FF)
|
| VB code |
' create a vumeter, and customize it
Dim wksht As IXlsWorksheet
Set wksht = workbook.AddWorksheet("sheet")
' NewVisualComponent(progId, row1, col1, row2, col2,
' topoffset, leftoffset, bottomoffset, rightoffset)
Dim compVumeter As IXlsVisualComponent
Set compVumeter = wksht.NewVisualComponent("Vumeter.ARsTdesign.1", _
1, 1, 10, 3, 0, 0, 0, 0)
wksht.Number(4, 4) = 65
' setting parameter: Value
compVumeter.ParameterByName("SetValue").Formula = "=$D$4"
' reading parameter: Value
Dim value As String
value = compVumeter.ParameterByName("GetValue").Formula
' setting parameter: Title
compVumeter.ParameterByName("SetTitle").ValueAsLabel = "(revenue)"
' setting parameter: ColorRightSlice
compVumeter.ParameterByName("SetColorRightSlice").Formula = "=8947967" ' 8947967 = &H8888FF
|
| C# code |
// create a vumeter, and customize it
IXlsWorksheet wksht = workbook.AddWorksheet("sheet");
// NewVisualComponent(progId, row1, col1, row2, col2,
// topoffset, leftoffset, bottomoffset, rightoffset)
IXlsVisualComponent compVumeter = wksht.NewVisualComponent("Vumeter.ARsTdesign.1",
1,1, 10,3, 0,0,0,0);
wksht.set_Number(4,4, 65);
// setting parameter: Value
compVumeter.get_ParameterByName("SetValue").Formula = "=$D$4";
// reading parameter: Value
String val = compVumeter.get_ParameterByName("GetValue").Formula;
// setting parameter: Title
compVumeter.get_ParameterByName("SetTitle").ValueAsLabel = "(revenue)";
// setting parameter: ColorRightSlice
compVumeter.get_ParameterByName("SetColorRightSlice").Formula = "=8947967"; // RGB shade of blue (8947967 = 0x8888FF)
|
| C/C++ code |
// create a vumeter and customize it
//
xlsgen::IXlsWorksheetPtr wksht = workbook->AddWorksheet(L"sheet");
// NewVisualComponent(progId, row1, col1, row2, col2,
// topoffset, leftoffset, bottomoffset, rightoffset)
xlsgen::IXlsVisualComponentPtr comp = wksht->NewVisualComponent(L"Vumeter.ARsTdesign.1",
1,1, 10,3, 0,0,0,0);
wksht->Number[4][4] = 65;
// setting parameter: Value
comp->ParameterByName[L"SetValue"]->Formula = L"=$D$4";
// reading parameter: Value
_bstr_t value = comp->ParameterByName[L"GetValue"]->Formula;
// setting parameter: Title
comp->ParameterByName[L"SetTitle"]->ValueAsLabel = L"(revenue)";
// setting parameter: ColorRightSlice
comp->ParameterByName[L"SetColorRightSlice"]->Formula = L"=8947967"; // shade of blue (8947967 = 0x8888FF)
|
The vu-meter comes as part of the xlsgen package, but you can of course use any other third-party vu-meter ActiveX control out there. The vu-meter from the xlsgen package has the following programmable properties :
Min : minimum value to be taken into account. Defaults to 0.
Max : maximum value to be taken into account. Defaults to 100.
Value : the value to display.
OldValue : sets the old value to display. The old value and current value are both displayed.
Title : optional title to display.
ColorRightSlice : 24-bit RGB color of the slice on the right of the value.
ColorLeftSlice : 24-bit RGB color of the slice on the left of the value.
ColorOfValue : 24-bit RGB color of the value.
A Vumeter with different parameters
Microsoft ships Visual Basic 6.0 with a freely redistribuable chart control. If you have not installed Visual Basic 6.0, it is probable you can't use this ActiveX control. The corresponding file is C:\Windows\System32\MSCHRT20.OCX, which is an ActiveX control. The identifier is : MSChart20Lib.MSChart.2. This ActiveX control has nothing to do with Excel. Here is an example of how to use it, what we do is set the data source to draw the bar columns from.
The Microsoft chart control
| Java code |
// create a chart, and attach two series
XlsVisualComponent compChart = wksht.NewVisualComponent("MSChart20Lib.MSChart.2",
1,5, 14,8, 0,0,0,0);
wksht.putLabel(4,6, "revenue");
wksht.putNumber(5,6, 50);
wksht.putNumber(6,6, 70);
wksht.putNumber(7,6, 100);
wksht.putNumber(8,6, 60);
wksht.putLabel(4,7, "cost");
wksht.putNumber(5,7, 50);
wksht.putNumber(6,7, 40);
wksht.putNumber(7,7, 30);
wksht.putNumber(8,7, 20);
// setting parameter: ChartData
XlsVisualComponentParameter paramChartData = compChart.getParameterByName("SetChartData");
paramChartData.putFormula("=$F$4:$G$8");
|
| VB code |
' create a chart, and attach two series
Dim compChart As IXlsVisualComponent
Set compChart = wksht.NewVisualComponent("MSChart20Lib.MSChart.2", _
1, 5, 14, 8, 0, 0, 0, 0)
wksht.Label(4, 6) = "revenue"
wksht.Number(5, 6) = 50
wksht.Number(6, 6) = 70
wksht.Number(7, 6) = 100
wksht.Number(8, 6) = 60
wksht.Label(4, 7) = "cost"
wksht.Number(5, 7) = 50
wksht.Number(6, 7) = 40
wksht.Number(7, 7) = 30
wksht.Number(8, 7) = 20
' setting parameter: ChartData
Dim paramChartData As IXlsVisualComponentParameter
Set paramChartData = compChart.ParameterByName("SetChartData")
paramChartData.Formula = "=$F$4:$G$8"
|
| C# code |
// create a chart, and attach two series
IXlsVisualComponent compChart = wksht.NewVisualComponent("MSChart20Lib.MSChart.2",
1,5, 14,8, 0,0,0,0);
wksht.set_Label(4,6, "revenue");
wksht.set_Number(5,6, 50);
wksht.set_Number(6,6, 70);
wksht.set_Number(7,6, 100);
wksht.set_Number(8,6, 60);
wksht.set_Label(4,7, "cost");
wksht.set_Number(5,7, 50);
wksht.set_Number(6,7, 40);
wksht.set_Number(7,7, 30);
wksht.set_Number(8,7, 20);
// setting parameter: ChartData
IXlsVisualComponentParameter paramChartData = compChart.get_ParameterByName("SetChartData");
paramChartData.Formula = "=$F$4:$G$8";
|
| C/C++ code |
// create a chart (using the Microsoft chart control)
//
xlsgen::IXlsVisualComponentPtr comp = wksht->NewVisualComponent(L"MSChart20Lib.MSChart.2",
1,5, 14,8, 0,0,0,0);
wksht->Label[4][6] = L"revenue";
wksht->Number[5][6] = 50;
wksht->Number[6][6] = 70;
wksht->Number[7][6] = 100;
wksht->Number[8][6] = 60;
wksht->Label[4][7] = L"cost";
wksht->Number[5][7] = 50;
wksht->Number[6][7] = 40;
wksht->Number[7][7] = 30;
wksht->Number[8][7] = 20;
// setting parameter: ChartData
xlsgen::IXlsVisualComponentParameterPtr param = comp->ParameterByName[L"SetChartData"];
param->Formula = L"=$F$4:$G$8";
|
sparklines
See sparklines.
Google charts
There is a dedicated page in the documentation.
Geomaps
There is a dedicated page in the documentation.
Bubble clouds
There is a dedicated page in the documentation.
Traffic lights
There is a dedicated page in the documentation.
Gauge
There is a dedicated page in the documentation.
It is possible to not only insert and render any ActiveX control, also to create snapshots at any moment. Towards that end, the IXlsVisualComponent interface includes extraction methods :
By iterating the mechanism 1) setting values to parameters 2) rendering, you can build interactive or sequential snapshots of controls. For instance, this can be used in interactive business intelligence dashboards.
Once you know the identifier (progId) of the ActiveX control, you can run it. If you don't set any of its parameters, it can still render into a snapshot that can be inserted in an Excel spreadsheet, but the interesting scenarios occur by working with parameters. ActiveX controls usually provide a healthy number of parameters to customize their behavior and layout. For instance, if you are working with a chart control, expect to be able to select the chart type, the data source, and so on.
To get the list of parameters, you can do it in at least two ways :
Using the object browser to get the list of parameters
| Java code |
// create a vumeter and list the parameters
XlsVisualComponent compVumeter = wksht.NewVisualComponent("Vumeter.ARsTdesign.1",
1,1, 10,3, 0,0,0,0);
long nbParameters = compVumeter.getCount();
for (int i = 1; i <= nbParameters; i++)
{
XlsVisualComponentParameter param = compVumeter.getParameterByIndex(i);
String name = param.getName();
int t = param.getType();
String nativeType = param.getNativeType();
}
|
| VB code |
' create a vumeter and list the parameters
Dim compVumeter As IXlsVisualComponent
Set compVumeter = wksht.NewVisualComponent("Vumeter.ARsTdesign.1", _
1, 1, 10, 3, 0, 0, 0, 0)
Dim nbParameters
nbParameters = compVumeter.Count
For i = 1 To nbParameters
Dim param As IXlsVisualComponentParameter
Set param = compVumeter.ParameterByIndex(i)
Dim name As String
name = param.name
Dim t
t = param.Type
Dim nativeType As String
nativeType = param.nativeType
Next
|
| C# code |
// create a vumeter and list the parameters
IXlsVisualComponent compVumeter = wksht.NewVisualComponent("Vumeter.ARsTdesign.1",
1,1, 10,3, 0,0,0,0);
long nbParameters = compVumeter.Count;
for (int i = 1; i <= nbParameters; i++)
{
IXlsVisualComponentParameter param = compVumeter.get_ParameterByIndex(i);
String name = param.Name;
enumDataType t = param.Type;
String nativeType = param.NativeType;
System.Console.WriteLine("{0} ({1})", name, nativeType);
}
|
| C/C++ code |
// create a vumeter and list the parameters
//
xlsgen::IXlsVisualComponentPtr comp = wksht->NewVisualComponent(L"Vumeter.ARsTdesign.1",
1,1, 10,3, 0,0,0,0);
long nbParameters = comp->Count;
for (long i = 1; i <= nbParameters; i++)
{
xlsgen::IXlsVisualComponentParameterPtr param = comp->ParameterByIndex[i];
_bstr_t name = param->Name;
int t = param->Type;
_bstr_t nativeType = param->NativeType;
WCHAR s[256];
swprintf(s, L"%s (%s)\r\n", (LPWSTR)name, (LPWSTR)nativeType);
OutputDebugStringW(s);
}
|
A number of programming environments allow to build your own ActiveX controls. Whether you are working with VB, Delphi, C++, VB.NET/C#, there is always a way to do it. Here are a couple suggestions :
xlsgen documentation. © ARsT Design all rights reserved.