Automatizace tvorby grafů

Doc. Dr. Vladimír Homola, Ph.D.

Abstrakt

Grafické vyjádření numerických hodnot podává bezesporu názornou informaci o charakteru dat. Řada programových produktů pro uchování resp. zpracování dat umožňuje jejich grafickou prezentaci - zejména databázové systémy a tabulkové procesory, které navíc nabízí uživatelům široké možnosti vlastní úpravy grafů. Vytvořit dva tři grafy interaktivním způsobem není problém. Problémem však bývá vytvořit desítky a stovky principielně stejných grafů, lišících se jen zdrojovými daty. Článek popisuje princip databázové aplikace vytvořené pro grafickou prezentaci hloubkové distribuce pro popel ve zhruba 380 průzkumných vrtech uhelné pánve, a to jako dílčí úlohu v rámci většího projektu.  Pracuje v prostředí Windows firmy Microsoft, pro uložení dat používá datový model Microsoft Database (MDB), pro grafické výstupy sešit tabulkového procesoru Excel (XLS) a pro generování programové prostředí interpretu Visual Basic dostupné téměř ve všech profesionálních aplikacích (zvláště Excel, Access, Word, Autocad, Corel atd).

Abstract

Graphical presentation of numeric data gives indisputably the transparent information about the data relations. Many of software data processing systems give the possibility of graphical data presentation - mainly database systems, and spreadsheets. These ones, in addition, allow the user wide range of own customization. There is no problem to create interactively two or three graphs. The problem is to create tenths or hundreds of graphs, in principle the same but based on different data. The article describes the database application created for the graphical presentation of the ash depth distribution in exploration coal deposits bores. This application was developed as the partial task of the  wide-ranging project. It runs under the Windows operating system. For data manipulation, the Microsoft Database (MDB) model is used. The graphs are generated into the Excel workbook. Finally, the Visual Basic is used as the programming environment.

Key words

Graph, Database, Spreadsheet, Workbook, Excel, Ash, Borehole, Basic.

Zjednodušený popis problému

Jsou dána data popisují percentuelní obsah popela, jak byl analyzován v hloubce H vrtu V. Vrt je identifikován celočíselným kódem, hloubka je vyjádřena v metrech jako desetinné číslo, obsah popela v procentech rovněž jako desetinné číslo. Vrtů je kolem 380. Úkolem je vytvořit kolem 380 grafů znázorňujících pro každý vrt procento popela v jednotlivých hloubkových úrovních.

Takto definovaný úkol je jen malou součástí komplexně řešeného projektu značného rozsahu. Proto jsou zdrojová data v tabulkách jedné relační databáze poměrně složité struktury a zvláště velkého objemu - cca 80 MB. Např. popis každého z vrtů včetně jeho souřadnic je v jedné tabulce VRTY, vlastní analýzy popela ve druhé tabulce ZAKL_ANAL, seznamy katastrálních území v dalších tabulkách atd. Pro komplexní popis databáze viz [1]. Zjednodušeně lze však realitu popsanou daty v databázi znázornit např. obrázkem 1.
 


Obr. 1: Modelová situace

 

Zjednodušený popis řešení

Do grafického znázornění distribuce popela je díky komplexně pojaté databázi třeba vybírat a předzpracovávat data z několika tabulek. Proto má tvorba každého grafu dvě části: jednak výběr a příprava zdrojových dat grafu, jednak tvorba a úprava vlastního grafu pro připravená data. Tyto dvě činnosti se cyklicky opakují pro všechny vrty.

Požadované grafy jsou umístěny v sešitu Excelu. Ze tří možností - jeden list se všemi grafy, několik listů s několika grafy, jeden graf v jednom listě - byla zvolena první možnost. Výsledkem bude jeden nově vytvořený sešit s jedním listem obsahujícím všechny grafy. Generující aplikace má rovněž tři možnosti volby prostředí - prostředí databázového programu (Access), tabulkového procesoru (Excel) nebo samostatného překladače (VB) - z nichž byla pro větší univerzálnost zvolena možnost třetí. Všechny konstrukce v tomto článku popisované však lze použít v kterémkoliv prostředí, např. i Wordu apod.

Znázornit algoritmus úlohy lze vývojovým diagramem na obrázku 2. Jednotlivé očíslované kroky jsou pak níže v článku komentovány popřípadě podrobněji rozvedeny.
 


Obr. 2: Vývojový diagram řešení

 

Výsledný sešit

Výsledek chodu shora popsaného algoritmu je tedy sešit obsahující list se zhruba 380 grafy, kde před každým předchází tabulka dat potřebných pro graf. Následující tabulka obsahuje data pro jeden z vrtů; referenční hloubka v [m.n.v] je ve sloupcích Do a Od, procento popela ve sloupci Popel; ve sloupci Sesyp je pak indikace toho, že hodnoty ve sloupci Popel jsou získány analýzou ze sesypu v daných hloubkových úrovních:
 

Do Od Mocnost Popel Sesyp
178,17 177,47 0,70 21,89  
177,47 176,22 1,25 24,93  
176,22 176,02 0,20 81,37  
176,02 174,97 1,05 26,48  
174,97 173,87 1,10 20,79  
173,87 172,47 1,40 13,84  
172,47 171,47 1,00 15,27  
171,47 170,47 1,00 14,87  
170,47 169,47 1,00 19,50  
169,47 168,02 1,45 12,03  
168,02 167,67 0,35 71,18  
167,67 167,17 0,50 62,07  
167,17 166,47 0,70 73,69  
166,47 165,47 1,00 82,34  
165,47 164,37 1,10 72,80  
164,37 164,12 0,25 22,14  
164,12 163,77 0,35 64,76  
163,77 161,97 1,80 0,00  
161,97 161,67 0,30 12,35  
178,17 173,87 4,30 26,80 SE
173,87 168,02 5,85 15,01 SE

Tab. 1: Data jednoho vrtu

 

Graf vygenerovaný na základě dat předchozí tabulky následuje v sešitu za tabulkou:
 


Obr. 3: Výsledek algoritmu pro jeden vrt

 

Společné označení

V dalším textu bude pro konkretizaci použito následující společné označení:

Případní zájemci o vlastní obdobnou aplikaci použijí svého konkrétního umístění resp. označení.

Potřebné reference

Aplikace psaná v prostředí Visual Basic používá jednak knihovnu s objektovým modelem databáze, jednak knihovnu s objektovým modelem sešitu. Obě knihovny se zpřístupní pomocí hlavního menu vývojového prostředí Project / References... vyhledáním položek

Microsoft DAO 3.6 Object Library

pro databázi (verze 3.6 odpovídá - zhruba řečeno - Windowsům 2000, XP a vyšším), resp.

Microsoft Excel ii Object Library

pro sešit resp. aplikaci Excelu jako takovou (ii odpovídá - zhruba řečeno - verzi Officů: 9 pro Office 2000, 11 pro Office 2003).

Řídící část podrobněji

Dále jsou jednotlivé kroky řídící části aplikace (tj. 1, 2 a 8) komentovány a rozvedeny podrobněji.

Seznam vrtů (1)

Z praktických důvodů (především pro rychlost zpracování) je seznam vrtů vytvořen jako dynamické celočíselné pole naplněné z tabulky vrtů. Úplný text této části aplikace je tento:

Dim Vrty() as Long

Dim db As Database
Dim rs As Recordset
Dim nn As Long

Set db = DBEngine.OpenDatabase("D:\DATAUHLI\VRTY.MDB")
Set rs = db.OpenRecordset("VRTY")

nn = 0
ReDim Vrty (0 To 0)
Vrty(0) = 0
Do While Not rst.EOF
   nn = nn + 1
   ReDim Preserve Vrty(0 To nn)
   Vrty(nn) = rs!KOD
   rs.MoveNext
Loop
  
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

Po provedení těchto příkazů je pole Vrty vytvořeno, naplněno celočíselnými kódy vrtů ze sloupce KOD tabulky VRTY a hodnota Ubound (Vrty ) = nn  odevzdá počet vrtů.

Nový sešit (2) a v něm první list

Pro další zpracování bude potřebná instance aplikace Excelu. Nový sešit (jako objekt - označme ho SesitG) se proto asi nejjednodušeji vytvoří použitím metod této aplikace. Jeho data (jako soubor, označme ho POPEL.XLS) se uloží metodou SaveAs objektu SesitG. Dále: podle nastavení instalace Excelu se nový sešit vytváří s kolekcí jistého počtu listů, nejméně jednoho. Určitě tedy existuje "list č. 1" - označme ho ListG a do něj ukládejme data a grafy:

Dim Exc as Excel.Application
Dim SesitG as Workbook
Dim ListG as Worksheet

Set Exc = New Excel.Application
Set SesitG = Exc.Workbooks.Add
set ListG = SesitG.Worksheets(1)

V této fázi je ještě vhodná nastavit některé vlastnosti Excelu (jako aplikace), které tvorbu grafů urychlí resp. zcela automatizují. Především se Excelu zabrání neustálému "překreslování" (i když na pozadí, tak jakoby probíhá) sešitu nastavením vlastnosti ScreenUpdating. Dále se zabrání dotazování se uživatele na potvrzení různých stavů, např. vypouštění přebytečných listů apod. nastavením vlastnosti DisplayAlerts:

Exc.DisplayAlerts = False
Exc.ScreenUpdating = False

Cyklus pro všechny vrty

Kroky 4 až 7 vývojového diagramu jsou realizovány v cyklu přes všechny vrty:

Dim ii As Long

For ii = 1 To nn
   ZpracujVrt Vrty (ii), ListG
Next

kde programová jednotka ZpracujVrt pro lepší vývoj i čitelnost soustřeďuje jeden logický celek - zpracování jednoho vrtu (první parametr) a umístění jeho dat a grafu do listu (druhý parametr). Zahrnuje tedy kroky 3 - 7 vývojového diagramu.

Uložení a zavření sešitu (8)

Po zpracování všech vrtů předchozím cyklem zbývá pouze sešit zavřít a uvolnit všechny zdroje:

SesitG.Save
SesitG.Close
Set SesitG= Nothing

Exc.Quit
Set Exc = Nothing

Jednotlivý vrt podrobněji

Dále jsou jednotlivé kroky aplikace pro jeden vrt (tj. 3 až 7) komentovány a rozvedeny podrobněji. Jak bylo řečeno shora, jsou pro přehlednost realizovány programovou jednotkou

Sub ZpracujVrt (KodVrtu As Long, ListG As Worksheet)
   ...
End Sub

Zjištění pozice v listě (3)

Každý vrt je zpracován do listu jako tabulka dat následovaná grafem (viz obr. shora). Tyto dvojice následují jedna za druhou, shora dolu. Data se umísťují vždy zleva počínaje sloupcem A, graf vždy zarovnaný na levý okraj listu. S horizontálním umístěním tedy problém není. Je však problém s vertikálním umístěním.

Řekněme, že mezi jednotlivými vrty bude svislá mezera 3 řádky. Pokud by byl vrt v listě zastoupen jen svými daty, stačí metodou UsedRange listu zjistit obdélníkovou oblast doposud obsazenou daty a další vrt umístit 3 řádky za její spodní okraj. Zmíněný problém spočívá v tom, že metoda UsedRange zahrnuje pouze buňky listu, nikoliv nad nimi se rozprostírající graf.

Proto bylo použito následujícího obratu: Vkládaným grafům se určí rozměry (to je rozumné už pro jednotnost) a ty se udávají v jednotkách bod (= point = 1/72 palce). Na druhé straně vlastnost Top resp. Bottom oblasti (a specielně jednoho řádku)  obsahují vzdálenost horního resp. spodního okraje oblasti od horního okraje listu, a to taktéž v bodech.

První vrt bude umístěn počínaje prvním řádkem listu, jeho graf po dvou volných řádcích za daty (tj. ve vzdálenosti Top [řádku N+2], kde N je počet řádků oblasti dat, od horního okraje listu - označme tuto vzdálenost Tg). Je-li Vg výška grafu, pak poslední řádek náležející do oblasti prvního vrtu je poslední takový, který má Top ještě menší než Tg+Vg. Data druhého grafu se tedy umístí o 3 řádky pod ním.

Funkce, která dodá číslo prvního řádku, mající od horního okraje listu qList vzdálenost alespoň qBodu, a to počínaje řádkem číslo qStart, může být např. tato:

Function NajdiRadekShora (qList As Worksheet, qBodu As Double, qStart As Long) As Long
   Dim ii As Long
   ii = qStart - 1
   Do ii = ii + 1: Loop While qList.Rows(ii).Top < qBodu
   NajdiRadekShora = ii
End Function

Umístění dat vrtu do listu (4)

Primárně jsou - jak bylo řečeno shora - všechna potřebná data umístěna v relační databázi formátu MDB. Existuje několik způsobů, jak je z tohoto zdroje umístit do listu sešitu Excelu. Popisované řešení používá velmi rychlý způsob, a to import dat pomocí SQL. Princip spočívá v tom, že aplikace naváže pomocí připojovacího řetězce (connection string) kontakt s databázovým prostředím, předá mu požadavek textem obsahujícím buď jméno tabulky dat, jméno příkazu Select z SQL nebo přímo tento příkaz, získá od databázového prostředí datový výsledek a ten umístí do obdélníkové oblasti určené buňkou levého horního rohu; tuto oblast může přímo pojmenovat.

Úplný text funkce, která toto vše provádí, je následující:

Function ImportujMDB( _
   qAdresar As String, _
   qDatabaze As String, _
   qTabulka As String, _
   qList As Worksheet, _
   qOblast As String, _
   qCil As Range) As Range

  Dim qt As QueryTable
  Dim cn As String

  cn = "ODBC;DSN=Databáze MS Access;"
  cn = cn + "DBQ=" + qAdresar+ "\" + qDatabaze + ";"
  cn = cn + "DefaultDir=" + qAdresar+ ";"
  cn = cn + "DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"

  Set qt = qList.QueryTables.Add(Connection:=cn, Destination:=qCil)
  With qt
    .CommandText = qTabulka
    .Name = qOblast
    .FieldNames = True
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SaveData = True
    .Refresh BackgroundQuery:=False
  End With
  Set ImportujMDB = qList.Range(qOblast)

 Set qt = Nothing

End Function

V této funkci jest parametrem qTabulka textový řetězec. Výborná vlastnost databázového prostředí je to, že tímto textovým řetězcem může být nejen jméno skutečné tabulky dat, ale i jméno uloženého příkazu Select z SQL a dokonce přímo i jeho text. Díky tomu, že příkaz Select je nesmírně silným příkazem, je zřejmé, že to jen násobí možnosti použití mechanismu importu dat do sešitu. Už jen výběr z "datové" tabulky a "číselníkové" tabulky bez nutnosti dalších konstrukcí (např. klauzulí left resp. right join) jednak zjednodušuje celou aplikaci, jednak ji podstatně urychluje. Příkaz Select zajišťující zdroj dat do jedné tabulky na listu sešitu má tvar:

Select
  A.HLDO as Do, A.HLOD as Od, A.MOC as Mocnost, A.AD as Popel, A.SESYP as Sesyp
  From ANALYZY A left join VRTY V on A.ADRESA = V.ADRESA
  Where A.ADRESA = Trim(Str(qKodV))
  Order by A.SESYP, A.HLOD desc, A.HLDO desc

Vložení grafu (5)

Vložení grafu spočívá ve vytvoření nového objektu třídy ChartObject v kolekci ChartObjects, nejpohodlněji metodou Add kolekce. Tento objekt udržuje informace o poloze, rozměrech apod. Pole Chart objektu ChartObject ukazuje na vlastní graf, který udržuje informace o vlastním grafu: typu, zdrojových datech, umístění apod.

Vložení grafu je vhodné pro lepší vývoj i čitelnost realizovat jako samostatnou programovou jednotku. V popisovaném řešení byla zvolena jednotka typu funkce, která předává po svém volání hodnotu vzdálenosti 10 bodů pod grafem od horního okraje listu - to je velmi pohodlné pro řešení komplikace s hledáním místa pro umístění dat následujícího grafu. Text funkce, která toto vše provádí, může být tento:

Public Function VlozGrafVrtu(ws As Worksheet, rg As Range, vr As Long) As Double

  Const cSirkaGrafu = 500
  Const cVyskaGrafu = 350

  Dim lGraf As Chart
  Dim lGrafy As ChartObjects
  Dim lChartObject As ChartObject
  Dim lJmListu As String

  Set lChartObject = ws.ChartObjects.Add(0, 0, cSirkaGrafu, cVyskaGrafu)
  lChartObject.Top = rg.Top + rg.Height + 10

  Set lGraf = lChartObject.Chart
  lJmListu = ws.Name

  With lGraf
    .ChartType = xlXYScatterLines
    .SetSourceData Source:=rg, PlotBy:=xlColumns
    .Location Where:=xlLocationAsObject, Name:=lJmListu
  End With

  ' Teď se musí graf nastavit znova, protože po vytvoření byl jiného typu než po přemístění .Location
  Set lGrafy = ws.ChartObjects
  Set lChartObject = lGrafy.Item(lGrafy.Count)
  Set lGraf = lChartObject.Chart

  ' Funkce předá vzdálenost 10 bodů pod grafem
  VlozGrafVrtu = lChartObject.Top + lChartObject.Height + 10

End Function

Formátování grafu (6)

Označíme-li identifikátorem lGraf objekt třídy Chart (tedy vlastní graf), pak formátování grafu spočívá ve vhodném nastavení vlastností těch objektů, které jsou součástí grafu. jedná se především o

Serie = lGraf.SeriesCollection(1)
OblastGrafu = lGraf.PlotArea
Nadpis = lGraf.ChartTitle
OsaX = lGraf.Axis(1)
OsaY = lGraf.Axis(2)

Objektu Serie se nastaví především Serie.Border určující vzhled jednak bodů grafu, jednak čáry, která je spojuje. Jde o tyto vlastnosti (příklad přiřazení konkrétních hodnot):

.LineStyle = xlContinuous
.ColorIndex = 50
.Weight = xlMedium
.Smooth = False
.MarkerBackgroundColorIndex = 7
.MarkerForegroundColorIndex = 46
.MarkerStyle = xlX
.MarkerSize = 5
.Shadow = False

Objektu OblastGrafu se nastaví především rozměry a umístění (.Width, .Height, .Top=0, .Left=0). Rozměry jsou dynamicky odvozené z rozměru grafu tak, aby plocha oblasti zabírala maximum.

Objektu Nadpis se nastaví především vlastnost .Text (zde označení konkrétního vrtu) a dále vlastnost .Font (typ písma, velikost, barva). Umístění .Top a .Left se dynamicky odvodí z rozměru grafu tak, aby nadpis byl umístěný nahoře uprostřed.

Objektům OsaX a OsaY se nastaví vlastnosti určující měřítko na nich. Jde zejména o tyto vlastnosti (příklad přiřazení konkrétních hodnot):

.HasTitle = True
.MajorTickMark = xlOutside
.MinorTickMark = xlNone
.TickLabelPosition = xlNextToAxis
.MinimumScale = qMin
.MaximumScale = qMax
.MinorUnit = 1
.MajorUnit = qMajor
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone

a dále vlastnosti popisu osy .AxisTitle.Characters.Text (text popisu) a .AxisTitle.Font (písmo popisu). Vlastnost .TickLabels se týká popisu samotné "stupnice" (.NumberFormat a .Font).

Zapamatování pozice (7)

V odstavci "Zjištění pozice v listě" (viz) je popsán problém při zjišťování volného místa v listě s vloženými grafy. V odstavci "Vložení grafu" je ukázána funkce, která vloží graf a předá vzdálenost jeho spodního okraje od horního okraje listu. Konečně opět v odstavci "Zjištění pozice v listě" je ukázána funkce, která zjistí číslo prvního řádku takového, že je vzdálen od horního okraje listu o danou vzdálenost.

Je tedy zřejmé, že zapamatování volné pozice pro vložení následujícího vrtu (nejdříve jeho dat) spočívá jen ve volání funkce NajdiRadekShora s parametrem rovným hodnotě předané funkcí VlozGrafVrtu.

Závěr

Článek se pokusil sice ve zjednodušené a schematické, přesto však použitelné formě ukázat možnosti použití standardního programového vybavení. Tím tabulkový procesor Excel ze sady Microsoft Office dnes rozhodně je. V praxi se vyskytuje řada obdobných situací, kdy je zapotřebí zpracovat množství dat v podstatě stejným postupem. Odborní pracovníci však často volí cestu "ruční" přípravy a protože opakovaná jednotvárná činnost je především časově náročná, volí cestu nejrůznějších sjednocení, míchání a průniku informací. Výsledek je pak nejrůznější, jen ne přehledný a na první pohled vypovídající o vlastnostech zkoumané veličiny.

Přitom výše předložené úryvky programu ani nemusí být psány kompletně "od začátku". Specielně Excel obsahuje mechanismus tzv. maker, což je záznam předváděné činnosti do posloupnosti příkazů jazyka Basic, které provedou totéž. Stačí pak vygenerované příkazy zkopírovat, mírně upravit, zobecnit vložením do jednotek s parametry apod. Upřímně řečeno, ani autor tohoto článku nepostupuje při první konstrukci nějakého objektu jinak.

Literatura

[1] HONĚK, J., HOŇKOVÁ, K., JELÍNEK, J., STANĚK, F: Univerzální systém zobrazení hornin, přechodných hornin a uhlí. In Sb. vědeckých prací VŠB – TU Ostrava, monografie 9, rok 2003, ročník XLIX, řada hornicko-geologická, s. 69 91.

 

 

 

Rev. 04 / 2008