Google Charts: Visualization - GeoChart
Markers Example w EXCELU :-)
  strona główna:
A po co ten Excel ;-)
 
 
 
Display live data on your site   
Google chart tools are powerful, simple to use, and free.   
Try out our rich gallery of interactive charts and data tools.  
  Using Google Charts
                                             brzmi dobrze??? :-)  
                                             Noo… tylko jak to wykorzystać?  
 
     No właśnie.. Google stworzyło masę rzeczy które udostepnia za free każdermu kto chce skorzystać :-) Jak jednak piszą   
rozwiązania dość łatwo wykorzystac na własnej stronie internetowej. Dziś zaprezentuję sposób wykorzystania jednego z wykresów  
google w Excelu.  
 
Czy cała zabawa ma sens??  
Jaki efekt możemy osiągnąć?  
Czy nie ma już podobnych  
gotowych rozwiazań?  
 
Ogólnie można osiagnąć podobny   
wykorzystująć wykres babelkowy  
którego tłem będzie mapa,  
skalę ustawimy na stałą i ręczenie  
dobierzemy współrzędne miast.  
Niepotrzebne elementy takiego  
wykresu (lilnie, osie, …)  
poukrywamy.  
Przykład na excelforum.pl  
 
Innym sposobem, o którym  
pisał: OShon jest dodatek  
Bing do E2013  
Wyglada obiecująco :-) ale że nie   
dysponuję najnowszym Excelem  
to powstał pomysł napisania  
podobnego narzędzia dla  
dowolnej wersji Excela. Z pomoca przyszły rozwiązania Google charts. I jak to wygląda?? Przykład powyżej :-)  
 
Jak więc stworzyć taki wykres?
 
Źródłem takiego wykresu jest kod JavaScript zamieszczony na tymczasowej stronie html.   Google GeoChart
Dokumentacja
 A jak pokazać taką stronę w obiekcie Excela? - proste :-) WebBrowser  
 
Co więc musimy zrobić?  
1. Wstawić kontrolkę WebBrowser do Arkusza  
2. Na podstawie przykładowego kodu JavaScript z dokumentacji GeoChart napisać  
   procedurę tworzącą plik html z kodem będącym połączeniem przykładu z naszymi  
   danymi.  
3. Utworzona tak stronę internetową wyświetlić na naszej WebBrowser  
4. Plik html usunąć.  
 
Wstawienie kontrolki - proste - >>>  
 
Przykład kodu z dokumentacji GeoChart też nie jest jakoś nieziemsko skomplikowany. ;-)  
 
 
<html>  
  <head>  
    <script type='text/javascript' src='https://www.google.com/jsapi'></script>  
    <script type='text/javascript'>  
     google.load('visualization', '1', {'packages': ['geochart']});  
     google.setOnLoadCallback(drawMarkersMap);  
   
      function drawMarkersMap() {  
      var data = google.visualization.arrayToDataTable([  
        ['City',   'Population', 'Area'],
 
        ['Rome',      2761477,    1285.31],  
        ['Milan',     1324110,    181.76],  
        ['Naples',    959574,     117.27],  
        ['Turin',     907563,     130.17],  
        ['Palermo',   655875,     158.9],  
        ['Genoa',     607906,     243.60],  
        ['Bologna',   380181,     140.7],  
        ['Florence',  371282,     102.41],  
        ['Fiumicino', 67370,      213.44],  
        ['Anzio',     52192,      43.43],  
        ['Ciampino',  38262,      11]  
      ]);  
   
      var options = {
 
        region: 'IT',  
        displayMode: 'markers',  
        colorAxis: {colors: ['green', 'blue']}  
      };  
   
      var chart = new google.visualization.GeoChart(document.getElementById('chart_div'));  
      chart.draw(data, options);  
    };  
    </script>  
  </head>  
  <body>  
    <div id="chart_div" style="width: 900px; height: 500px;"></div>  
  </body>  
</html>
 
 
 
No i nasze zadanie: - kodem VBA stworzyć "taki" plik html.  
   Tabela z danymi ma by stworzona na podstawie danych z komórek Excela  
Zakres z danymi w moim arkuszu nazwałęm tblDane i jest to zakres [B4:D15]  
Potrzebuje funkcji która z tablicy/zakresu stworzy ciąg reprezentujący  
tablicę JavaScript który będzie argumentem metody:  
google.visualization.arrayToDataTable  
Wielu danych nie przerabiałem ale na tą chwilę wystarcza mi funkcja:  
 
 
Function TblToString(tblDane As Variant) As String  
    Dim tbl As Variant: tbl = tblDane  
    Dim i As Long, j As Integer  
    Dim strText As String  
    Dim bFlag As Boolean  
      
    For i = 1 To UBound(tbl)  
        strText = strText & "["  
        For j = 1 To UBound(tbl, 2)  
            Select Case TypeName(tbl(i, j))  
                Case "String": strText = strText & "'" & tbl(i, j) & "',"  
                Case "Double": strText = strText & Replace(tbl(i, j), ",", ".") & ","  
                Case Else: bFlag = True  
            End Select  
        Next  
        strText = Left(strText, Len(strText) - 1)  
        If Not bFlag Then strText = strText & "],"  
        bFlag = False  
    Next  
    TblToString = Left(strText, Len(strText) - 1)  
End Function  
 
Teraz procedura tworząca plik html  
 
Sub HTMLTempfile(strHTMLFilePAth As String, _  
                 tblDane As String, _  
                 hexMin As String, hexMax As String)  
    Dim arrLines(1 To 33) As String, i As Long  
    arrLines(1) = "<html>"  
    arrLines(2) = "    <head>"  
    arrLines(3) = "        <script type='text/javascript' src='https://www.google.com/jsapi'></script>"  
    arrLines(4) = "        <script type='text/javascript'>"  
    arrLines(5) = "         google.load('visualization', '1', {'packages': ['geochart']});"  
    arrLines(6) = "         google.setOnLoadCallback(drawMarkersMap);"  
    arrLines(7) = ""  
    arrLines(8) = "           function drawMarkersMap() {"  
    arrLines(9) = "           var data = google.visualization.arrayToDataTable(["  
    arrLines(10) = tblDane  
    arrLines(11) = "          ]);"  
    arrLines(12) = ""  
    arrLines(13) = "          var options = {"  
    arrLines(14) = "            region: 'PL',"  
    arrLines(15) = "            displayMode: 'markers',"  
    arrLines(16) = "            colorAxis: {colors: ['#" & hexMin & "', '#" & hexMax & "']},"  
    arrLines(17) = "            backgroundColor: 'white',"  
    arrLines(18) = "            magnifyingGlass: {enable: true, zoomFactor: 5},"  
    arrLines(19) = "            legend: {textStyle: {color: 'red', fontSize: 16}},"  
    arrLines(20) = "            sizeAxis: {minValue: 10,  maxSize: 20},"  
    arrLines(21) = "            datalessRegionColor: 'F0F0F0',"  
    arrLines(22) = "            markerOpacity: 0.9"  
    arrLines(23) = "          };"  
    arrLines(24) = ""  
    arrLines(25) = "          var chart = new google.visualization.GeoChart(document.getElementById('chart_div'));"  
    arrLines(26) = "          chart.draw(data, options);"  
    arrLines(27) = "        };"  
    arrLines(28) = "       </script>"  
    arrLines(29) = "    </head>"  
    arrLines(30) = "    <body>"  
    arrLines(31) = "        <div id=""chart_div"" style=""width: 600px; height: 400px;""></div>"  
    arrLines(32) = "    </body>"  
    arrLines(33) = "</html>"  
 
    Dim intNR As Integer: intNR = VBA.FreeFile  
              
    Open strHTMLFilePAth For Output As #intNR  
        For i = 1 To UBound(arrLines)  
            Print #intNR, arrLines(i)  
        Next  
    Close #intNR  
          
End Sub
 
 
    Procedura oprócz ścieżki pod którą zapisze się nasz plik html, tabeli z danymi przyjmuje jeszcze skalę   Inny sposób prezentowania danych na mapie
kolorów jaka będzie wykorzystana do wykresu. W arkuszu graniczne kolory można określić poprzez podanie  
liczb składowych R,G,B dla min i max. Kolor ze składowych zwracany jest funkcją: LabelRGB (była już -->>)  
 
 
Fragment:  
 
    arrLines(13) = "          var options = {"  
    arrLines(14) = "            region: 'PL',"  
    arrLines(15) = "            displayMode: 'markers',"  
    arrLines(16) = "            colorAxis: {colors: ['#" & hexMin & "', '#" & hexMax & "']},"  
    arrLines(17) = "            backgroundColor: 'white',"  
    arrLines(18) = "            magnifyingGlass: {enable: true, zoomFactor: 5},"  
    arrLines(19) = "            legend: {textStyle: {color: 'red', fontSize: 16}},"  
    arrLines(20) = "            sizeAxis: {minValue: 10,  maxSize: 20},"  
    arrLines(21) = "            datalessRegionColor: 'F0F0F0',"  
    arrLines(22) = "            markerOpacity: 0.9"  
    arrLines(23) = "          };"  
 
wymaga dodatkowych "dwóch" słów wyjaśnienia:   Configuration Options
Całość opisana jest w dokumentacji -->>  
Ja wykorzystałem:  
     region: 'PL'               sprawa jasna :-) Niestety pomomo ze istnieje norma: ISO 3166-2:PL określająca regiony Polski to np.:   wikipedia ISO_3166-2:PL
                                  określenie rerionu jako PL-MA (Małopolskie) nie działa :-(. Miejmy nadziejé æe kiedyß bédzie :-)  
                                  Możemy więc rozwiązanie stosować na planie dowolnego kraju Europy jak również wybranego kontynentu  
                                  czy choćby Świata :-) Jednak pojedyńczego województwa lub jeszcze mniejszych jednostek "zabawka"  
                                  jeszcze nie obsługuje. :-)  
 
     displayMode: 'markers'                 ten parametr określa rodzaj Mapy GeoChart. Inny to 'regions' - może kiedyś ;-))  
 
     colorAxis: {colors: ['#" & hexMin & "', '#" & hexMax & "']},          to uzupełniam kodem - skala kolorów wykresu.  
                                                                                            argumentaki ma być kolor w postaci HexString np.: '#FF0000'  
 
     backgroundColor: 'white',             kolor tła. Mi biały pasuje, ale można to okreslić dowolnie (HexString)  
 
     magnifyingGlass: {enable: true, zoomFactor: 5},               To "Szkło Powiększające" które pokazuje się gdy elementy sa  
                                                                                    blisko siebie. W moim przykładzie jakoś nie chce działać :-|  
 
     legend: {textStyle: {color: 'red', fontSize: 16}},          Wielkość i kolor tego                                                 paska.  
 
     sizeAxis: {minValue: 10,  maxSize: 20},           Wielkość minimalna i maksymalna bąbelka.  
 
     datalessRegionColor: 'F0F0F0'                        Kolor Mapy - domyslnie szary. Tu można go zmienić.  
 
     markerOpacity: 0.9                   Stopien Przezroczystości Babelków (0.0 - 1.0)  
 
 
No i jeszcze np.: pod przyciskiem procedura łącząca wszystko w całość.  
 
Private Sub CommandButton1_Click()  
    Dim strPAth As String: strPAth = ThisWorkbook.Path & "\temp.html"  
      
    With Me  
        HTMLTempfile strPAth, _  
                     TblToString([tblDane]), _  
                     Hex(.[H5]) & Hex(.[I5]) & Hex(.[J5]), _  
                     Hex(.[H7]) & Hex(.[I7]) & Hex(.[J7])  
    End With  
 
    With Me.WebBrowser1  
        .Navigate strPAth  
         Do: DoEvents: Loop Until .ReadyState = READYSTATE_COMPLETE  
                  
        .Document.parentWindow.scrollto 10, 15  
        .Document.Body.Scroll = "no"   Przykład do pobrania.
      
    End With  
          
    VBA.Kill strPAth  
End Sub