xlsgen > overview > Google charts

 

 
native support for Google charts in xlsgen

 

Introducing Google charts

Google charts are fixed representations calculated and rendered over an internet connection (which is therefore required). All Excel chart types are available. A number of formatting properties are made available through xlsgen.

Sometimes in the future, it is possible Google charts will replace today's Microsoft OWC chart controls used for rendering spreadsheets in xlsgen (print, preview, PDF, XPS, HTML, ...) so that the client application may be able to pick his preferred choice.

xlsgen exposes the Google charts to a client application through an ActiveX control. There are already two ActiveX controls distributed by xlsgen, Vu-meters and Sparklines. Technically speaking all three ActiveX controls are embedded in the same and unique file : Sparklines.ocx, which has all 3 APIs.

Properties are being passed general formulas. They can be as simple as literals, such as in =120, or cell references, such as in =$E$4, or "real" formulas such as in =SUM(B2:B10). It transfers a lot of value to the client application which can indeed implement complex scenarios and various dynamics.

 

Google charts properties

Property nameDescription
SetType(enumGoogleChartsType)sets the Google chart type.
typedef enum
{
  [helpstring("Chart type, vertical bars")]            charttype_barsvertical         = 0,
  [helpstring("Chart type, vertical bars, stacked")]   charttype_barsverticalstacked  = 1,
  [helpstring("Chart type, horizontal bars")]          charttype_barshorizontal       = 2,
  [helpstring("Chart type, horizontal bars, stacked")] charttype_barshorizontalstacked = 3,
  [helpstring("Chart type, lines")]                    charttype_lines                = 4,
  [helpstring("Chart type, lines with markers")]       charttype_linesmarkers         = 5,
  [helpstring("Chart type, scatter, two series at least")] charttype_scatter              = 6,
  [helpstring("Chart type, bubbles, two series at least")] charttype_bubbles              = 7,
  [helpstring("Chart type, pie")]                      charttype_pie                  = 8,
  [helpstring("Chart type, pie 3D")]                   charttype_pie3d                = 9,
  [helpstring("Chart type, donut")]                    charttype_donut                = 10,
  [helpstring("Chart type, radar")]                    charttype_radar                = 11,
  [helpstring("Chart type, areas")]                    charttype_areas                = 12,
  [helpstring("Chart type, stock, 4 series")]          charttype_stockLowOpenCloseHigh = 13
} enumGoogleChartsType;

SetTitle(BSTR)sets the optional title to display.
AddSeries(VARIANT)sets an additional series of values.
SetLabels(VARIANT)sets labels.
SetSeriesTitle(BSTR)sets the current series title.
SetSeriesColor(int rgb)sets the current series color as a RGB value.
SetShowLegend(BOOL)sets whether the legend should show up. Default is : yes.
SetAxisXTitle(BSTR)sets the X axis title.
SetAxisYTitle(BSTR)sets the Y axis title.
SetScale(int scale)sets the chart scale, in percent. Default value is : 100.

 

Creating Google charts

Example 1 : pie 3D

Java code
XlsWorkbook wbk = engine.New( "googlecharts_pie.xls" );

XlsWorksheet wksht = wbk.AddWorksheet("sheet");

// row1, col1, row2, col2, topoffset, leftoffset, bottomoffset, rightoffsset

XlsVisualComponent comp = wksht.NewVisualComponent("GoogleCharts.ARsTdesign.1",
   1,1, 10,3, 0,0,0,0);

comp.getParameterByName("SetType").putValueAsNumber(9); // 9 = pie 3D

comp.getParameterByName("AddSeries").putFormula("={12;21;30}");

comp.getParameterByName("SetLabels").putFormula("={\"orange\";\"apple\";\"lemon\"}");

comp.getParameterByName("SetTitle").putValueAsLabel("pie 3D");
VB code
Dim wbk As IXlsWorkbook 
Set wbk = engine.New( "googlecharts_pie.xls" )

Dim wksht As IXlsWorksheet 
Set wksht = wbk.AddWorksheet("sheet")

' row1, col1, row2, col2, topoffset, leftoffset, bottomoffset, rightoffsset

Dim comp As IXlsVisualComponent 
Set comp = wksht.NewVisualComponent("GoogleCharts.ARsTdesign.1",  1,1, 10,3, 0,0,0,0)

comp.ParameterByName("SetType").ValueAsNumber = 9 // 9 = pie 3D

comp.ParameterByName("AddSeries").Formula = "={12;21;30}"

comp.ParameterByName("SetLabels").Formula = "={""orange"";""apple"";""lemon""}"

comp.ParameterByName("SetTitle").ValueAsLabel = "pie 3D"
C# code
IXlsWorkbook wbk = engine.New( "googlecharts_pie.xls" );

IXlsWorksheet wksht = wbk.AddWorksheet("sheet");

// row1, col1, row2, col2, topoffset, leftoffset, bottomoffset, rightoffsset

IXlsVisualComponent comp = wksht.NewVisualComponent("GoogleCharts.ARsTdesign.1",
   1,1, 10,3, 0,0,0,0);

comp.get_ParameterByName("SetType").ValueAsNumber = 9; // 9 = pie 3D

comp.get_ParameterByName("AddSeries").Formula = "={12;21;30}";

comp.get_ParameterByName("SetLabels").Formula = "={\"orange\";\"apple\";\"lemon\"}";

comp.get_ParameterByName("SetTitle").ValueAsLabel = "pie 3D";
C/C++ code
xlsgen::IXlsWorkbookPtr wbk = engine->New( L"googlecharts_pie.xls" );

xlsgen::IXlsWorksheetPtr wksht = wbk->AddWorksheet(L"sheet");

// row1, col1, row2, col2, topoffset, leftoffset, bottomoffset, rightoffsset

xlsgen::IXlsVisualComponentPtr comp = wksht->NewVisualComponent(L"GoogleCharts.ARsTdesign.1",
   1,1, 10,3, 0,0,0,0);

comp->ParameterByName[L"SetType"]->ValueAsNumber = 9; // 9 = pie 3D

comp->ParameterByName[L"AddSeries"]->Formula = L"={12;21;30}";

comp->ParameterByName[L"SetLabels"]->Formula = L"={\"orange\";\"apple\";\"lemon\"}";

comp->ParameterByName[L"SetTitle"]->ValueAsLabel = L"pie 3D";

 

Example 2 : bars

Java code
XlsWorkbook wbk = engine.New( "googlecharts_bars.xls" );

XlsWorksheet wksht = wbk.AddWorksheet("sheet");

// row1, col1, row2, col2, topoffset, leftoffset, bottomoffset, rightoffsset

XlsVisualComponent comp = wksht.NewVisualComponent("GoogleCharts.ARsTdesign.1",
   1,1, 14,5, 0,0,0,0);

comp.getParameterByName("SetType").putFormula("=0"); // 0 = charttype_barsvertical

comp.getParameterByName("AddSeries").putFormula("={10;20;30}");
comp.getParameterByName("SetSeriesTitle").putValueAsLabel("serie 1");

comp.getParameterByName("AddSeries").putFormula("={60;30;10}");
comp.getParameterByName("SetSeriesTitle").putValueAsLabel("serie 2");

comp.getParameterByName("AddSeries").putFormula("={40;50;60}");
comp.getParameterByName("SetSeriesTitle").putValueAsLabel("serie 3");

comp.getParameterByName("SetScale").putValueAsNumber(130);

comp.getParameterByName("SetSeriesColor").putValueAsNumber(0xFF0000);

comp.getParameterByName("SetTitle").putValueAsLabel("bars");

comp.getParameterByName("SetAxisXTitle").putValueAsLabel("axis X");

comp.getParameterByName("SetLabels").putFormula("={\"q2000\";\"q2001\";\"q2002\"}");
VB code
Dim wbk As IXlsWorkbook 
Set wbk = engine.New( "googlecharts_bars.xls" )

Dim wksht As IXlsWorksheet 
Set wksht = wbk.AddWorksheet("sheet")

' row1, col1, row2, col2, topoffset, leftoffset, bottomoffset, rightoffsset

Dim comp As IXlsVisualComponent 
Set comp = wksht.NewVisualComponent("GoogleCharts.ARsTdesign.1",  1,1, 14,5, 0,0,0,0)

comp.ParameterByName("SetType").Formula = "=0" ' 0 = charttype_barsvertical

comp.ParameterByName("AddSeries").Formula = "={10;20;30}"
comp.ParameterByName("SetSeriesTitle").ValueAsLabel = "serie 1"

comp.ParameterByName("AddSeries").Formula = "={60;30;10}"
comp.ParameterByName("SetSeriesTitle").ValueAsLabel = "serie 2"

comp.ParameterByName("AddSeries").Formula = "={40;50;60}"
comp.ParameterByName("SetSeriesTitle").ValueAsLabel = "serie 3"

comp.ParameterByName("SetScale").ValueAsNumber = 130

comp.ParameterByName("SetSeriesColor").ValueAsNumber = &HFF0000

comp.ParameterByName("SetTitle").ValueAsLabel = "bars"

comp.ParameterByName("SetAxisXTitle").ValueAsLabel = "axis X"

comp.ParameterByName("SetLabels").Formula = "={""q2000"";""q2001"";""q2002""}"
C# code
IXlsWorkbook wbk = engine.New( "googlecharts_bars.xls" );

IXlsWorksheet wksht = wbk.AddWorksheet("sheet");

// row1, col1, row2, col2, topoffset, leftoffset, bottomoffset, rightoffsset

IXlsVisualComponent comp = wksht.NewVisualComponent("GoogleCharts.ARsTdesign.1",
   1,1, 14,5, 0,0,0,0);

comp.get_ParameterByName("SetType").Formula = "=0"; // 0 = charttype_barsvertical

comp.get_ParameterByName("AddSeries").Formula = "={10;20;30}";
comp.get_ParameterByName("SetSeriesTitle").ValueAsLabel = "serie 1";

comp.get_ParameterByName("AddSeries").Formula = "={60;30;10}";
comp.get_ParameterByName("SetSeriesTitle").ValueAsLabel = "serie 2";

comp.get_ParameterByName("AddSeries").Formula = "={40;50;60}";
comp.get_ParameterByName("SetSeriesTitle").ValueAsLabel = "serie 3";

comp.get_ParameterByName("SetScale").ValueAsNumber = 130;

comp.get_ParameterByName("SetSeriesColor").ValueAsNumber = 0xFF0000;

comp.get_ParameterByName("SetTitle").ValueAsLabel = "bars";

comp.get_ParameterByName("SetAxisXTitle").ValueAsLabel = "axis X";

comp.get_ParameterByName("SetLabels").Formula = "={\"q2000\";\"q2001\";\"q2002\"}";
C/C++ code
xlsgen::IXlsWorkbookPtr wbk = engine->New( L"googlecharts_bars.xls" );

xlsgen::IXlsWorksheetPtr wksht = wbk->AddWorksheet(L"sheet");

// row1, col1, row2, col2, topoffset, leftoffset, bottomoffset, rightoffsset

xlsgen::IXlsVisualComponentPtr comp = wksht->NewVisualComponent(L"GoogleCharts.ARsTdesign.1",
   1,1, 14,5, 0,0,0,0);

comp->ParameterByName[L"SetType"]->Formula = L"=0"; // 0 = charttype_barsvertical

comp->ParameterByName[L"AddSeries"]->Formula = L"={10;20;30}";
comp->ParameterByName[L"SetSeriesTitle"]->ValueAsLabel = L"serie 1";

comp->ParameterByName[L"AddSeries"]->Formula = L"={60;30;10}";
comp->ParameterByName[L"SetSeriesTitle"]->ValueAsLabel = L"serie 2";

comp->ParameterByName[L"AddSeries"]->Formula = L"={40;50;60}";
comp->ParameterByName[L"SetSeriesTitle"]->ValueAsLabel = L"serie 3";

comp->ParameterByName[L"SetScale"]->ValueAsNumber = 130;

comp->ParameterByName[L"SetSeriesColor"]->ValueAsNumber = 0xFF0000;

comp->ParameterByName[L"SetTitle"]->ValueAsLabel = L"bars";

comp->ParameterByName[L"SetAxisXTitle"]->ValueAsLabel = L"axis X";

comp->ParameterByName[L"SetLabels"]->Formula = L"={\"q2000\";\"q2001\";\"q2002\"}";

 

xlsgen documentation. © ARsT Design all rights reserved.