Create a graphic with graphgdiplus udf with time axis

[badeend]

Hi,

 

I'm trying to create a graphic with a time axis from an excel file. 

I had to change the times to timepassed in seconds because otherwise i could not get the graphic to plot like i wanted.

This could work on a cleaner way but i can't fix it.

The big question i'm having is if i can get the handle from the lines i plot so that when i plot (for example) 8 i can select which ones i can show on the graph.

Or is the only way to do this plot the graphic again every time with the lines that i want to visualize.

The excel contains the following data for now :

column 1: value1 to be plotted

column2: date of all the values

column3: empty (i use this in my code)

column4: value2 to be plotted

1143,186

2018-02-08 23:13:21,000

15,12461

I added my code after this. Thanks for any help!!

Quote

#include <Array.au3>
#include <Excel.au3>
#include <date.au3>
#include <GraphGDIPlus.au3>
#include <WinAPISys.au3>

$iFullDesktopWidth = _WinAPI_GetSystemMetrics(78)
$iFullDesktopHeight = _WinAPI_GetSystemMetrics(79) ;Get desktop metrics to use full screen
;msgbox(0,0,$iFullDesktopHeight & " " &  $iFullDesktopWidth) 

local $workbook = _Excel_BookAttach("Book1")
local $valuearray = _Excel_RangeRead($workbook)
local $excelwindow = WinActivate("Book1")
winmove($excelwindow ,"Book1 - Excel",0,($iFullDesktopHeight/2),$iFullDesktopWidth,($iFullDesktopHeight/2)-20) ; Read values from excel to array

Local $ubound1 = ubound($valuearray)
local $date
local $counter =0
_ArrayDisplay($valuearray)
while $valuearray[$counter][1] <> ""        ;clean the date and time values that i get from my excel 
$aTemp = StringSplit($valuearray[$counter][1], "")
;_ArrayDisplay($aTemp)
$sSecond =  $aTemp[13]&$aTemp[14]
$sMinute= $aTemp[11]&$aTemp[12]
$sHour = $aTemp[9]&$aTemp[10]
$date = $aTemp[7]&$aTemp[8] & "/" & $aTemp[5]&$aTemp[6]
$valuearray[$counter][2] = 3600 *$sHour + 60* $sMinute + $sSecond
$valuearray[$counter][1]= $sHour&":"&$sMinute & " " & $date
$counter = $counter + 1
   WEnd
   $counter = $counter -1
_ArrayDisplay($valuearray)
local $timepassed = $valuearray[$counter][2] - $valuearray[0][2]
ConsoleWrite($Ssecond/$counter& " " & $counter & " " & $timepassed & " " & $Ssecond) ; convert all values to time passed for x-axis

;Finishded

Opt("GUIOnEventMode", 1)

;$aGraphArray[11]=["00:00:00","00:20:00","01:30:00","02:00:00","03:00:00","03:30:00","04:00:00"]
$GUI = GUICreate("",$iFullDesktopWidth,$iFullDesktopHeight/2)

winmove($GUI,"exaprodis",0,0)
GUISetOnEvent(-3,"_Exit")
GUISetState()

;----- Create Graph area -----
$Graph = _GraphGDIPlus_Create($GUI,40,30,($iFullDesktopWidth-80),($iFullDesktopHeight-120)/2,0xFF000000,0xFF88B3DD)

local $ticks = 10

$tijdperstuk =$timepassed/$ticks ;tijd per tick
$part = $counter / $ticks ; welke label op welke tick
;MsgBox(0,0,$tijdperstuk & " " & $part)

;_ArrayDisplay($Graph[11])
;----- Set X axis range from begin to end -----
_GraphGDIPlus_Set_RangeX($Graph,$valuearray[0][2],$valuearray[$counter][2],$ticks,1,1)
_GraphGDIPlus_Set_RangeY($Graph,-5,18,23,1,1)

;----- Set Y axis range from -5 to 5 -----
_GraphGDIPlus_Set_GridX($Graph,4,0xFF6993BE)
_GraphGDIPlus_Set_GridY($Graph,4,0xFF6993BE)

;----- Draw the graph -----
_Draw_Graph1()

_Draw_Graph2()
local $ahTicksLabelsX[] =  $Graph[11]
_ArrayDisplay($ahTicksLabelsX)
        For $i = 1 To (UBound($ahTicksLabelsX) - 1)
            GUICtrlSetData($ahTicksLabelsX[$i],$valuearray[$i*$part][1]) ;change the labels from original labels
                    next

While GUIGetMsg() <> -3
    Sleep(100)

WEnd

Func _Draw_Graph1()
   local $teller = 0
    ;----- Set line color and size -----
  local $pen1 = _GraphGDIPlus_Set_PenColor($Graph,0xFF325D87)
    _GraphGDIPlus_Set_PenSize($Graph,2)

    ;----- draw lines -----
    $First = True
    For $teller = 0 to $counter
        If $First = True Then _GraphGDIPlus_Plot_Start($Graph,$valuearray[$teller][2],$valuearray[$teller][0])
        $First = False
        _GraphGDIPlus_Plot_Line($Graph,$valuearray[$teller][2],$valuearray[$teller][0])

     Next
 _GraphGDIPlus_Refresh($Graph)
EndFunc

Func _Draw_Graph2()
   local $teller = 0
    ;----- Set line color and size -----
  local $pen1 = _GraphGDIPlus_Set_PenColor($Graph,0xFF325D87)
    _GraphGDIPlus_Set_PenSize($Graph,2)

    ;----- draw lines -----
    $First = True
    For $teller = 0 to $counter
        If $First = True Then _GraphGDIPlus_Plot_Start($Graph,$valuearray[$teller][2],$valuearray[$teller][3])
        $First = False
        _GraphGDIPlus_Plot_Line($Graph,$valuearray[$teller][2],$valuearray[$teller][3])

     Next
 _GraphGDIPlus_Refresh($Graph)
EndFunc

Func _GammaFunction1($iZ)
    $nProduct = ((2^$iZ) / (1 + $iZ))
    For $n = 2 to 1000
        $nProduct *= ((1 + (1/$n))^$iZ) / (1 + ($iZ / $n))
    Next
    Return (1/$iZ) * $nProduct
EndFunc

Func _Exit1()
    ;----- close down GDI+ and clear graphic -----
    _GraphGDIPlus_Delete($GUI,$Graph)
    Exit
EndFunc

 

 

[water]

Did you have a look at my ExcelChart UDF?
Makes creating charts much easier including exporting in another format.

[badeend]

2 hours ago, water said:

Did you have a look at my ExcelChart UDF?
Makes creating charts much easier including exporting in another format.

Thanks, i'll look in to it. 

Problem is that i would like to move away from excel. 

I would like to make the chart in autoit and get the data directly from our sqlserver.

The problem is that i'm not so good at autoit so i would like to make each part individually to see if i can do it and if so create the whole program.

Thanks for your response!

[water]

You can use Excel to store the data or retrieve it from SQL.
ExcelChart doesn't mind where the data comes from. The UDF just takes some data and visualizes it.