xlsgen > overview > Geomaps

 


sample 1 : US states color-coded geomaps in xlsgen


sample 2 : Europe countries color-coded geomaps in xlsgen

 

Introducing geomaps

Geomaps are earth map representations calculated and rendered over an internet connection (which is therefore required).

Interesting business intelligence scenarios include :

Countries, states, regions and districts are designated by a special coding, ISO3166. In addition to this, for US, United kingdom, Germany and France, a number of codes are prebuilt in xlsgen so that the client application can pass a friendly name such as "California" (referring to the state of California in the USA) instead of "US-CA" (the corresponding ISO3166 code).

 

Geomaps properties

Property nameDescription
SetTitle(BSTR)sets the optional title to display.
SetRegions(VARIANT)sets a series of regions (i.e. country codes or region codes or state codes). See ISO3166 for reference.
SetLabels(VARIANT)sets labels.
SetColors(VARIANT)optional. sets colors (RGB values). Random colors if this property is not set.
SetLegends(VARIANT)optional. sets legends.
SetShowLegend(BOOL)optional. sets whether the legend should show up. Default is : no.
SetBackgroundColor(int rgb)optional. sets the background color for countries/regions/states.
SetAdditionalColor(int rgb)optional. sets an additional color for countries/regions/states to avoid passing colors individually. Can be used to create color gradients (multi-variate).
SetLabelType(enumGeomapsLabelType labelType)optional. sets the label type for all labels. Default is : flag. 
typedef enum
{
        [helpstring("Geomaps, label type none")]            geomaps_label_none       = 0,
        [helpstring("Geomaps, label type arrow")]           geomaps_label_arrow      = 1,
        [helpstring("Geomaps, label type plus")]            geomaps_label_plus       = 2,
        [helpstring("Geomaps, label type diamond")]         geomaps_label_diamond    = 3,
        [helpstring("Geomaps, label type circle")]          geomaps_label_circle     = 4,
        [helpstring("Geomaps, label type cross")]           geomaps_label_cross      = 5,
        [helpstring("Geomaps, label type square")]          geomaps_label_square     = 6,
        [helpstring("Geomaps, label type flag")]            geomaps_label_flag       = 7,
        [helpstring("Geomaps, label type text")]            geomaps_label_text       = 8,
        [helpstring("Geomaps, label type annotation")]      geomaps_label_annotation = 9
} enumGeomapsLabelType;
SetLabelColor(int rgb)optional. sets the label color for all labels. Default is : black.
SetLabelFontSize(int fontSize)optional. sets the label font size for all labels. Default is : 10 pixels.
SetBubblesScale(int scale)optional. sets the scale for labels in percent. Can be used to show bubbles of different sizes. Default is : 100 percent.

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.

Once properties are set, it is possible to render and extract a PNG picture of the geomaps in question. See the IXlsVisualComponent interface for more information.

 

Creating Geomaps

Example 1 : Europe countries color-coded geomaps

C/C++ code
xlsgen::IXlsWorkbookPtr wbk = engine->New( L"geomaps.xls" );

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

xlsgen::IXlsVisualComponentPtr comp = wksht->NewVisualComponent(L"Geomaps.ARsTdesign.1",
   6,  // row1
   3,  // col1
   24, // row2
   7,  // col2
   0,0,0,0);

// strings or ISO3166 codes
comp->ParameterByName[L"SetRegions"]->Formula = L"={\"Greece\";\"Germany\";\"France\"}";

// RGB values encoded as strings
comp->ParameterByName[L"SetColors"]->Formula = L"={\"FF4444\";\"44FF44\";\"4444FF\"}";

// optional. strings
comp->ParameterByName[L"SetLabels"]->Formula = L"={\"50 (Greece)\";\"150 (Germany)\";\"100 (France)\"}";

// optional. strings
comp->ParameterByName[L"SetLegends"]->Formula = L"={\"Greece\";\"Germany\";\"France\"}";

 

Example 2 : US states color-coded geomaps

C/C++ code
xlsgen::IXlsWorkbookPtr wbk = engine->New( L"geomaps.xls" );

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

xlsgen::IXlsVisualComponentPtr comp = wksht->NewVisualComponent(L"Geomaps.ARsTdesign.1",
   6,  // row1
   3,  // col1
   38, // row2
   10,  // col2
   0,0,0,0);

// optional. string
comp->ParameterByName[L"SetTitle"]->ValueAsLabel = L"US unemployment in May 2012";

// strings or ISO3166 codes
comp->ParameterByName[L"SetRegions"]->Formula = L"={\"US-WA\";\"US-OR\";\"US-CA\";\"US-NV\";\"US-ID\";\"US-UT\";\"US-AZ\";
\"US-MT\";\"US-WY\";\"US-CO\";\"US-NM\";\"US-ND\";\"US-SD\";\"US-NE\";\"US-KS\";\"US-OK\";\"US-TX\";\"US-MN\";
\"US-IA\";\"US-MO\";\"US-AR\";\"US-LA\";\"US-WI\";\"US-IL\";\"US-MS\";\"US-MI\";\"US-IN\";\"US-KY\";\"US-TN\";
\"US-AL\";\"US-OH\";\"US-WV\";\"US-NY\";\"US-PA\";\"US-VA\";\"US-NC\";\"US-SC\";\"US-GA\";\"US-FL\";\"US-ME\";
\"US-VT\";\"US-NH\";\"US-MA\";\"US-RI\";\"US-CT\";\"US-NJ\";\"US-DE\";\"US-MD\"}";

// RGB values encoded as strings
comp->ParameterByName[L"SetColors"]->Formula = L"={\"C57F43\";\"B65F13\";\"B65F13\";\"B65F13\";\"E2BFA1\";\"F1DFD0\";\"D39F72\";
\"F1DFD0\";\"F1DFD0\";\"E2BFA1\";\"F1DFD0\";\"F1DFD0\";\"F1DFD0\";\"F1DFD0\";\"F1DFD0\";\"F1DFD0\";\"E2BFA1\";
\"D39F72\";\"F1DFD0\";\"D39F72\";\"F1DFD0\";\"F1DFD0\";\"D39F72\";\"B65F13\";\"C57F43\";\"B65F13\";\"B65F13\";
\"B65F13\";\"B65F13\";\"C57F43\";\"B65F13\";\"D39F72\";\"D39F72\";\"D39F72\";\"E2BFA1\";\"B65F13\";\"B65F13\";
\"C57F43\";\"B65F13\";\"D39F72\";\"E2BFA1\";\"F1DFD0\";\"D39F72\";\"B65F13\";\"E2BFA1\";\"D39F72\";\"D39F72\";\"E2BFA1\"}";

 

Color gradients

While in the previous examples, the color of each map subdivision was passed explicitely, as part of the SetColors method call, xlsgen supports implicits color coding such as color gradients.

What it takes is to use instead the SetAdditionalColor property, pass it any number of primary colors (you want to make two method calls in order to create a 2-color gradient for example), and the colors in between will be calculated automatically.

This is convenient for scenarios meant to highlight regions or states based on a sorted list, sorted by a meaningful indicator. For instance, one may want to show areas greener the closer they are to the top. Or an opposite scenario such as making subdivisions appear more red as the indicator takes values interpreted as red.

Here are two examples of this.

 

Example 3 : US states color-gradient


sample 3 : US states color-gradient in xlsgen

This example was achieved by making a few changes from the previous sample code : 1) remove the line where SetColors is being called ; 2) replace it by two calls to SetAdditionalColor, the first one passing the red color, i.e. the color where the gradient starts, the second call passing the green color, i.e. where the gradient stops. It goes like this, still in C++ :

C/C++ code
xlsgen::IXlsWorkbookPtr wbk = engine->New( L"geomaps.xls" );

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

xlsgen::IXlsVisualComponentPtr comp = wksht->NewVisualComponent(L"Geomaps.ARsTdesign.1",
   6,  // row1
   3,  // col1
   38, // row2
   10,  // col2
   0,0,0,0);

// optional. string
comp->ParameterByName[L"SetTitle"]->ValueAsLabel = L"US unemployment in May 2012";

// strings or ISO3166 codes
comp->ParameterByName[L"SetRegions"]->Formula = L"={\"US-WA\";\"US-OR\";\"US-CA\";\"US-NV\";\"US-ID\";\"US-UT\";\"US-AZ\";
\"US-MT\";\"US-WY\";\"US-CO\";\"US-NM\";\"US-ND\";\"US-SD\";\"US-NE\";\"US-KS\";\"US-OK\";\"US-TX\";\"US-MN\";
\"US-IA\";\"US-MO\";\"US-AR\";\"US-LA\";\"US-WI\";\"US-IL\";\"US-MS\";\"US-MI\";\"US-IN\";\"US-KY\";\"US-TN\";
\"US-AL\";\"US-OH\";\"US-WV\";\"US-NY\";\"US-PA\";\"US-VA\";\"US-NC\";\"US-SC\";\"US-GA\";\"US-FL\";\"US-ME\";
\"US-VT\";\"US-NH\";\"US-MA\";\"US-RI\";\"US-CT\";\"US-NJ\";\"US-DE\";\"US-MD\"}";

// color-gradient
comp->ParameterByName[L"SetAdditionalColor"]->ValueAsNumber = 0xFF0000; // red
comp->ParameterByName[L"SetAdditionalColor"]->ValueAsNumber = 0x00FF00; // green

and it produces the result captured above.

 

Example 4 : Euro zone GDP per country

 
sample 4 : Euro zone GDP per country, with and without associated values

In this sample, the data is actual Euro stats data available here :


sample 4 data : Euro stats

The data is sorted by GDP, and it is convenient to associate a color gradient to the GDP so that countries with higher GDP will be greener and countries with lower GDP will be more red.

And here is the full source code, data and geomaps creation in C++ :

C/C++ code
xlsgen::IXlsWorkbookPtr wbk = engine->New( L"geomaps.xls" );

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

xlsgen::IXlsVisualComponentPtr comp = wksht001->NewVisualComponent(L"Geomaps.ARsTdesign.1",
   1,  // row1
   3,  // col1
   29, // row2
   10,  // col2
   0,0,0,0);

// data source : http://epp.eurostat.ec.europa.eu/cache/ITY_PUBLIC/2-20062012-AP/EN/2-20062012-AP-EN.PDF

wksht001->Cell[1][1]->HtmlLabel = L"<span width=15.425781><font size=12><b>Country</b></font></span>";
wksht001->Cell[1][2]->HtmlLabel = L"<span width=12.570313><font size=12><b>GDP</b></font></span>";
wksht001->Cell[2][1]->HtmlLabel = L"<span>Luxembourg</span>";
wksht001->Cell[2][2]->HtmlFloat = L"<span>274</span>";
wksht001->Cell[3][1]->HtmlLabel = L"<span>Netherlands</span>";
wksht001->Cell[3][2]->HtmlFloat = L"<span>131</span>";
wksht001->Cell[4][1]->HtmlLabel = L"<span>Austria</span>";
wksht001->Cell[4][2]->HtmlFloat = L"<span>129</span>";
wksht001->Cell[5][1]->HtmlLabel = L"<span>Ireland</span>";
wksht001->Cell[5][2]->HtmlFloat = L"<span>127</span>";
wksht001->Cell[6][1]->HtmlLabel = L"<span>Sweden</span>";
wksht001->Cell[6][2]->HtmlFloat = L"<span>126</span>";
wksht001->Cell[7][1]->HtmlLabel = L"<span>Denmark</span>";
wksht001->Cell[7][2]->HtmlFloat = L"<span>125</span>";
wksht001->Cell[8][1]->HtmlLabel = L"<span>Germany</span>";
wksht001->Cell[8][2]->HtmlFloat = L"<span>120</span>";
wksht001->Cell[9][1]->HtmlLabel = L"<span>Belgium</span>";
wksht001->Cell[9][2]->HtmlFloat = L"<span>118</span>";
wksht001->Cell[10][1]->HtmlLabel = L"<span>Finland</span>";
wksht001->Cell[10][2]->HtmlFloat = L"<span>116</span>";
wksht001->Cell[11][1]->HtmlLabel = L"<span>United kingdom</span>";
wksht001->Cell[11][2]->HtmlFloat = L"<span>108</span>";
wksht001->Cell[12][1]->HtmlLabel = L"<span>France</span>";
wksht001->Cell[12][2]->HtmlFloat = L"<span>107</span>";
wksht001->Cell[13][1]->HtmlLabel = L"<span>Italy</span>";
wksht001->Cell[13][2]->HtmlFloat = L"<span>101</span>";
wksht001->Cell[14][1]->HtmlLabel = L"<span>Spain</span>";
wksht001->Cell[14][2]->HtmlFloat = L"<span>99</span>";
wksht001->Cell[15][1]->HtmlLabel = L"<span>Cyprus</span>";
wksht001->Cell[15][2]->HtmlFloat = L"<span>92</span>";
wksht001->Cell[16][1]->HtmlLabel = L"<span>Slovenia</span>";
wksht001->Cell[16][2]->HtmlFloat = L"<span>84</span>";
wksht001->Cell[17][1]->HtmlLabel = L"<span>Malta</span>";
wksht001->Cell[17][2]->HtmlFloat = L"<span>83</span>";
wksht001->Cell[18][1]->HtmlLabel = L"<span>Greece</span>";
wksht001->Cell[18][2]->HtmlFloat = L"<span>82</span>";
wksht001->Cell[19][1]->HtmlLabel = L"<span>Czech republic</span>";
wksht001->Cell[19][2]->HtmlFloat = L"<span>80</span>";
wksht001->Cell[20][1]->HtmlLabel = L"<span>Portugal</span>";
wksht001->Cell[20][2]->HtmlFloat = L"<span>77</span>";
wksht001->Cell[21][1]->HtmlLabel = L"<span>Slovakia</span>";
wksht001->Cell[21][2]->HtmlFloat = L"<span>73</span>";
wksht001->Cell[22][1]->HtmlLabel = L"<span>Estonia</span>";
wksht001->Cell[22][2]->HtmlFloat = L"<span>67</span>";
wksht001->Cell[23][1]->HtmlLabel = L"<span>Hungary</span>";
wksht001->Cell[23][2]->HtmlFloat = L"<span>66</span>";
wksht001->Cell[24][1]->HtmlLabel = L"<span>Poland</span>";
wksht001->Cell[24][2]->HtmlFloat = L"<span>65</span>";
wksht001->Cell[25][1]->HtmlLabel = L"<span>Lithuania</span>";
wksht001->Cell[25][2]->HtmlFloat = L"<span>62</span>";
wksht001->Cell[26][1]->HtmlLabel = L"<span>Latvia</span>";
wksht001->Cell[26][2]->HtmlFloat = L"<span>58</span>";
wksht001->Cell[27][1]->HtmlLabel = L"<span>Romania</span>";
wksht001->Cell[27][2]->HtmlFloat = L"<span>49</span>";
wksht001->Cell[28][1]->HtmlLabel = L"<span>Bulgaria</span>";
wksht001->Cell[28][2]->HtmlFloat = L"<span>45</span>";

comp->ParameterByName[L"SetTitle"]->ValueAsLabel = L"Euro zone GDP per country in 2011";

// countries
comp->ParameterByName[L"SetRegions"]->Formula = L"=A2:A28"; // area reference

// GDP
comp->ParameterByName[L"SetLabels"]->Formula  = L"=B2:B28"; // area reference

// color gradient
comp->ParameterByName[L"SetAdditionalColor"]->ValueAsNumber = 0x00FF00; // green
comp->ParameterByName[L"SetAdditionalColor"]->ValueAsNumber = 0xFF0000; // red

// background color
comp->ParameterByName[L"SetBackgroundColor"]->ValueAsNumber = 0xCCCCCC;

wksht001->PageSetup->PageOrientation = FALSE;
wksht001->Export->ExportAsPDF(L"geomaps.pdf");

wbk->Close();

 

Example 5 : random color-coding

When assigning an explicit color to a map subdivision is too much to ask, a client application can avoid setting colors altogether. When this happens, xlsgen will compute colors randomly. The example below illustrates it :


Random colors illustrating the German population by federal state

 

Example 6 : dynamic bubble sizes and other symbols

When it comes to stressing differences between adjacent map subdivisions, a client application may use an alternative to the label associated to each subdivision. There are two mechanisms : 1) different symbols 2) dynamic bubble sizes

Different symbols : the SetLabelType property can be used to display a symbol, other than text or a flag.

typedef enum
{
        [helpstring("Geomaps, label type none")]            geomaps_label_none       = 0,
        [helpstring("Geomaps, label type arrow")]           geomaps_label_arrow      = 1,
        [helpstring("Geomaps, label type plus")]            geomaps_label_plus       = 2,
        [helpstring("Geomaps, label type diamond")]         geomaps_label_diamond    = 3,
        [helpstring("Geomaps, label type circle")]          geomaps_label_circle     = 4,
        [helpstring("Geomaps, label type cross")]           geomaps_label_cross      = 5,
        [helpstring("Geomaps, label type square")]          geomaps_label_square     = 6,
        [helpstring("Geomaps, label type flag")]            geomaps_label_flag       = 7,
        [helpstring("Geomaps, label type text")]            geomaps_label_text       = 8,
        [helpstring("Geomaps, label type annotation")]      geomaps_label_annotation = 9
} enumGeomapsLabelType;

Dynamic bubble sizes illustrating the German population by federal state

When a symbol is set, it will be displayed at a fixed size on top of the corresponding map subdivision. A client application can actually customize the color of symbols by using the SetLabelColor and SetLabelFontSize properties.

An even more interesting scenario is to enable the dynamic calculation of symbol sizes in order to stress even more differences. In order to enable the calculation of symbol sizes, the SetBubblesScale property must be used in order to set the scale ratio.

The example below does exactly this using real statistical data from a German statistical firm.


German statistical data

And the corresponding source code is :

C/C++ code
xlsgen::IXlsWorkbookPtr wbk = engine->New( L"geomaps.xls" );

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

wksht001->Cell[1][2]->HtmlLabel = L"<span width=18><b>Federal state</b></span>";
wksht001->Cell[1][3]->HtmlLabel = L"<span width=13><b>Population</b></span>";
wksht001->Cell[2][2]->HtmlLabel = L"Baden-Wurttemberg";
wksht001->Cell[2][3]->HtmlFloat = L"10783791";
wksht001->Cell[3][2]->HtmlLabel = L"Bayern";
wksht001->Cell[3][3]->HtmlFloat = L"12583538";
wksht001->Cell[4][2]->HtmlLabel = L"Berlin";
wksht001->Cell[4][3]->HtmlFloat = L"3490445";
wksht001->Cell[5][2]->HtmlLabel = L"Brandenburg";
wksht001->Cell[5][3]->HtmlFloat = L"2497828";
wksht001->Cell[6][2]->HtmlLabel = L"Bremen";
wksht001->Cell[6][3]->HtmlFloat = L"660042";
wksht001->Cell[7][2]->HtmlLabel = L"Hamburg";
wksht001->Cell[7][3]->HtmlFloat = L"1796077";
wksht001->Cell[8][2]->HtmlLabel = L"Hessen";
wksht001->Cell[8][3]->HtmlFloat = L"6087166";
wksht001->Cell[9][2]->HtmlLabel = L"Mecklenburg-Vorpommern";
wksht001->Cell[9][3]->HtmlFloat = L"1636303";
wksht001->Cell[10][2]->HtmlLabel = L"Niedersachsen";
wksht001->Cell[10][3]->HtmlFloat = L"7920456";
wksht001->Cell[11][2]->HtmlLabel = L"Nordrhein-Westfalen";
wksht001->Cell[11][3]->HtmlFloat = L"17844472";
wksht001->Cell[12][2]->HtmlLabel = L"Rheinland-Pfalz";
wksht001->Cell[12][3]->HtmlFloat = L"4000461";
wksht001->Cell[13][2]->HtmlLabel = L"Saarland";
wksht001->Cell[13][3]->HtmlFloat = L"1014166";
wksht001->Cell[14][2]->HtmlLabel = L"Sachsen";
wksht001->Cell[14][3]->HtmlFloat = L"4137330";
wksht001->Cell[15][2]->HtmlLabel = L"Sachsen-Anhalt";
wksht001->Cell[15][3]->HtmlFloat = L"2317416";
wksht001->Cell[16][2]->HtmlLabel = L"Schleswig-Holstein";
wksht001->Cell[16][3]->HtmlFloat = L"2837738";
wksht001->Cell[17][2]->HtmlLabel = L"Thüringen";
wksht001->Cell[17][3]->HtmlFloat = L"2223610";


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

comp->ParameterByName[L"SetTitle"]->ValueAsLabel = L"German population by federal state";
comp->ParameterByName[L"SetRegions"]->Formula = L"=R2C2:R17C2";
comp->ParameterByName[L"SetLabels"]->Formula  = L"=R2C3:R17C3";
comp->ParameterByName[L"SetLegends"]->Formula = L"=R2C2:R17C2";
comp->ParameterByName[L"SetBackgroundColor"]->ValueAsNumber = 0xCCCCCC;
comp->ParameterByName[L"SetLabelType"]->ValueAsNumber  = 4; // circle symbol
comp->ParameterByName[L"SetLabelColor"]->ValueAsNumber  = 0xFFFF00;
comp->ParameterByName[L"SetLabelFontSize"]->ValueAsNumber  = 7;
comp->ParameterByName[L"SetBubblesScale"]->ValueAsNumber  = 600; // dynamic symbol sizes (600%)

wksht001->PageSetup->PageOrientation = FALSE;
wksht001->Export->ExportAsPDF(L"geomaps.pdf");


wbk->Close();

 

Example 7 : aggregate legend

The aggregate legend scenario comes into play whenever the goal is to associate color-coded map subdivisions and legend entries matching a given condition, for instance all map subdivisions where Population > 7 millions.

Using regular legend entries in the German population example would normally only result in having legend entries reflecting either German states or German population figures, whereas we want the legend to be self-explanatory for the geomap. That is what xlsgen allows to do by creating a conditional formula on one hand, to explicit the condition in question, and doing calculations behind the scene.

The corresponding geomap we would like to achieve bad on the German statistical data is :


Aggregate legend illustrating the German population by federal state

This results in the following source code :

C/C++ code
xlsgen::IXlsWorkbookPtr wbk = engine->New( L"geomaps.xls" );

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

wksht001->Cell[1][2]->HtmlLabel = L"<span width=18><b>Federal state</b></span>";
wksht001->Cell[1][3]->HtmlLabel = L"<span width=13><b>Population</b></span>";
wksht001->Cell[2][2]->HtmlLabel = L"Baden-Wurttemberg";
wksht001->Cell[2][3]->HtmlFloat = L"10783791";
wksht001->Cell[3][2]->HtmlLabel = L"Bayern";
wksht001->Cell[3][3]->HtmlFloat = L"12583538";
wksht001->Cell[4][2]->HtmlLabel = L"Berlin";
wksht001->Cell[4][3]->HtmlFloat = L"3490445";
wksht001->Cell[5][2]->HtmlLabel = L"Brandenburg";
wksht001->Cell[5][3]->HtmlFloat = L"2497828";
wksht001->Cell[6][2]->HtmlLabel = L"Bremen";
wksht001->Cell[6][3]->HtmlFloat = L"660042";
wksht001->Cell[7][2]->HtmlLabel = L"Hamburg";
wksht001->Cell[7][3]->HtmlFloat = L"1796077";
wksht001->Cell[8][2]->HtmlLabel = L"Hessen";
wksht001->Cell[8][3]->HtmlFloat = L"6087166";
wksht001->Cell[9][2]->HtmlLabel = L"Mecklenburg-Vorpommern";
wksht001->Cell[9][3]->HtmlFloat = L"1636303";
wksht001->Cell[10][2]->HtmlLabel = L"Niedersachsen";
wksht001->Cell[10][3]->HtmlFloat = L"7920456";
wksht001->Cell[11][2]->HtmlLabel = L"Nordrhein-Westfalen";
wksht001->Cell[11][3]->HtmlFloat = L"17844472";
wksht001->Cell[12][2]->HtmlLabel = L"Rheinland-Pfalz";
wksht001->Cell[12][3]->HtmlFloat = L"4000461";
wksht001->Cell[13][2]->HtmlLabel = L"Saarland";
wksht001->Cell[13][3]->HtmlFloat = L"1014166";
wksht001->Cell[14][2]->HtmlLabel = L"Sachsen";
wksht001->Cell[14][3]->HtmlFloat = L"4137330";
wksht001->Cell[15][2]->HtmlLabel = L"Sachsen-Anhalt";
wksht001->Cell[15][3]->HtmlFloat = L"2317416";
wksht001->Cell[16][2]->HtmlLabel = L"Schleswig-Holstein";
wksht001->Cell[16][3]->HtmlFloat = L"2837738";
wksht001->Cell[17][2]->HtmlLabel = L"Thüringen";
wksht001->Cell[17][3]->HtmlFloat = L"2223610";


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

comp->ParameterByName[L"SetTitle"]->ValueAsLabel = L"German population by federal state";
comp->ParameterByName[L"SetRegions"]->Formula = L"=R2C2:R17C2";
comp->ParameterByName[L"SetLabels"]->Formula  = L"=R2C3:R17C3";
comp->ParameterByName[L"SetColors"]->Formula  = L"=IF(R2C3:R17C3 >= 7000000;\"00FF00\";\"FF0000\")";
comp->ParameterByName[L"SetLegends"]->Formula = L"=IF(R2C3:R17C3 >= 7000000;\"> 7 millions\";\"< 7 millions\")";
comp->ParameterByName[L"SetBackgroundColor"]->ValueAsNumber = 0xCCCCCC;
comp->ParameterByName[L"SetLabelType"]->ValueAsNumber  = 8; // text symbol
comp->ParameterByName[L"SetLabelColor"]->ValueAsNumber  = 0xFFFF00;
comp->ParameterByName[L"SetLabelFontSize"]->ValueAsNumber  = 7;

wksht001->PageSetup->PageOrientation = FALSE;
wksht001->Export->ExportAsPDF(L"geomaps.pdf");


wbk->Close();

 

Business intelligence scenarios

It is possible to combine business intelligence and geomaps by using column names from your data instead of fragile cell references. Here goes an example of this :


Business intelligence scenarios are accomodated as well

C/C++ code
xlsgen::IXlsWorkbookPtr wbk = engine->Open( L"data.xls", L"" );

xlsgen::IXlsWorksheetPtr wksht = wbk->WorksheetByIndex[1];

wksht->Tables->InferFromWorksheet();

xlsgen::IXlsTableVisualComponentPtr vc = wksht->Tables->Item[1]->DataResults->TurnToVisualComponent(L"Geomaps.ARsTdesign.1");

vc->Formula[L"SetTitle"]   = L"=\"Sales (millions)\"";
vc->Formula[L"SetRegions"] = L"=[Country]";
vc->Formula[L"SetLabels"]  = L"=[Sales (millions)]";
vc->Formula[L"SetColors"]  = L"=IF([Sales (millions)]>=10;\"00FF00\";\"FF0000\")";
vc->Formula[L"SetLegends"] = L"=IF([Sales (millions)]>=10;\"> 10 millions\";\"< 10 millions\")";
vc->Formula[L"SetBackgroundColor"]  = L"=13421772"; //0xCCCCCC;
vc->Formula[L"SetLabelType"]  = L"=8"; // text
vc->Apply();

xlsgen::IXlsWorksheetPtr wksht_new = wbk->AddWorksheet(L"new");
wksht_new->InsertTableAt(wksht->Tables->Item[1], 5, 3);
wksht_new->PageSetup->PageOrientation = FALSE;
wksht_new->Export->ExportAsPDF(L"geomaps.pdf");

wbk->Close();

 

xlsgen documentation. © ARsT Design all rights reserved.