Oracle SQL Report Generator

[GreenCan]

and now in the correct forum...

Hello Oracle guys,

Throw out your expensive Business Object and give your end user a small but powerfull Report generator based on (almost) pure PL/SQL

This script will open an SQL and preview the result in a ListView. You can then export to CSV or to a formatted Excel

What is required?

0. You need the to include ExcelCOM_UDF.au3

1. You need an Oracle DB server of course, if you try this script without a proper database connection, it will crash

2. You'll need an Oracle ODBC client on your PC

3. Modify the Oracle SQL.ini (I didn't include a config for this, so take Notepad)

[sql Directory]

Directory=SQL\ ==> directory where to store the SQL files

[Voiceread]

Voice=1 ==> Small spoken message when the SQL result is ready

Speachtext=Job completed ==> That's the message

[Database connection]

SERVER=Your_Oracle_server.world ==> The Oracle server

UserID=Oracle_UserID ==> (readonly) UserID to connect to Oracle

Password=Oracle_UserPWD ==> Password for the UserID

[Excel]

Separator=; ==> This is the csv export column separator, change to whatever is appropriate to your environment

4. You will have to make SQL files ( filename.sql). I attach a few generic examples for your tests.

5. Put the SQL files in the Directory as defined in Oracle SQL.ini

6. Parameters.

I added some input possibilities in the SQL. That is of course not PL/SQL but it creates input flexibility

These are the possible parameters:

AA. date period:

You can enter a two dates defined as a from/to question. Default will be today (sysdate)

ff/ff/ffff and tt/tt/tttt

example:

select * from TABLE

where DATE

BETWEEN to_Date(('ff/ff/ffff'),'dd/mm/yyyy')

AND to_Date(('tt/tt/tttt'),'dd/mm/yyyy')+1

-- A single day is a period covering 1 day

BB. text input

Input text string preceded by the question

example:

select * from TABLE

where ITEM = '\&textTown=BRUSSELS\&text'

Both AA and BB can be combined

example:

select * from TABLE

where

ITEM = '\&textTown=BRUSSELS\&text' and

DATE

BETWEEN to_Date(('ff/ff/ffff'),'dd/mm/yyyy')

AND to_Date(('tt/tt/tttt'),'dd/mm/yyyy')+1

The parameters are not restricted to the 'where' clause, in the example

'Example of complex SQL with multiple params.sql' you can see that I used it also in a 'CASA WHEN'

CC. Average and Total indicators:

-- CALCULATE TOTAL

-- CALCULATE AVERAGE

If these strings are present (remember! they should be at the end of the SQL script), when exported to Excel, an extra row will be added with totals or averages (don't do both together)

Warning:

1. Do not put any comments before the SQL script.

You can add however your comments a the bottom of the SQL script.

2. Working with dates:

Because of the peculiar way that autoit works with date/time data you will have to convert the dates into text using

to_char(sysdate, 'DD/MM/YY HH24:MI') or similar.

If you don't do that, the field will give a date in the format '20090102160532' which will be taken as a number by Excel.

Let me know if you like it.

GreenCan

Oracle_SQL.au3 source (See examples in zip file)

expandcollapse popup

#include <GUIConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#Include <File.au3>
#include <Array.au3>
#include <Process.au3>
#include <DateTimeConstants.au3>
#include <Date.au3>
#include <ListViewConstants.au3>
#include <ButtonConstants.au3>
#include <ExcelCOM_UDF.au3> 

Dim $oMyError, $listview, $SQL_Array[1], $Request_Totals
Global $from, $to, $mainwindow , $adoSQL, $Voice, $Speach_text, $connection
Global $from, $to, $SQLResultWindow 
Global $TextInput, $Question, $replace_string

; Initializes COM handler
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

Opt("TrayIconHide", 1) 
Opt("TrayIconDebug", 1)        
Opt("ExpandEnvStrings", 1)     
Opt("ExpandVarStrings", 1)    
Opt("GUIDataSeparatorChar","|")

#Region ini
; Open and read ini file
$FileHandle = FileOpen("Oracle SQL.ini", 0)
$inifile = FileRead($FileHandle)
; read section and ini_key 
$Directory = _IniMem_Read($inifile,"SQL Directory","Directory")
$Voice = _IniMem_Read($inifile,"Voiceread","Voice")
$Speach_text = _IniMem_Read($inifile,"Voiceread","Speachtext")
$server = _IniMem_Read($inifile,"Database connection","SERVER")
$UserID = _IniMem_Read($inifile,"Database connection","UserID")
$Password = _IniMem_Read($inifile,"Database connection","Password")
$connection = "DRIVER={Microsoft ODBC for Oracle};SERVER=" & $server & ";User Id=" & $UserID & ";Password=" & $Password & ";" ; Database connection definition
$Separator = _IniMem_Read($inifile,"Excel","Separator")
#EndRegion ini

#Region SQL List
; SQL list
$FileList=_FileListToArray($Directory, "*.sql", 1)
$File_List = ""
For $i = 0 To (UBound($FileList)-1)
    $File_List = $File_List & StringLeft ( $FileList[$i], StringLen ($FileList[$i])-4)  & "|"
Next
; remove  the last ';'
$File_List = StringLeft ( $File_List, StringLen ($File_List)-1 )

; Create GUI
$MainWindow = GuiCreate("Oracle SQL Report Generator", 400, 400)
GuiSetIcon("DATABASE.ICO", 0)

; Create LIST
GuiCtrlCreateLabel("Select a SQL script", 5, 10, 300, 15)
$SelectSQL = GuiCtrlCreateList("", 5, 30, 390, 320)
GuiCtrlSetData(-1, $File_List, 1)

; Buttons
$MainbuttonGo = GuiCtrlCreateButton("Go", 10, 360, 100, 30, $BS_DEFPUSHBUTTON)
$MainbuttonClose = GuiCtrlCreateButton("Close", 290, 360, 100, 30)

GuiSetState(@SW_SHOW,$MainWindow)

; Run the GUI until the dialog is closed
While 1
    $mainmsg = GUIGetMsg()
        
    Select
        Case $mainmsg = $GUI_EVENT_CLOSE 
            Exit
        Case $mainmsg = $MainbuttonClose
            Exit        
        Case $mainmsg = $MainbuttonGo 
            ;MsgBox(0,"","go")
            $choice = GUICtrlRead($SelectSQL)
            ;MsgBox(0,"",$choice & ".sql")
            If $choice = "" Then
                MsgBox(0,"","Please select a SQL script first")
            Else
                GuiSetState(@SW_HIDE,$MainWindow)
                executeSQL($Directory , $choice & ".sql")
                GuiSetState(@SW_SHOW,$MainWindow)
            EndIf

    EndSelect   
WEnd
#EndRegion SQL List

Exit
#FUNCTION# ==============================================================
Func executeSQL($Directory, $file)
    Dim $oMyError, $listview, $SQL_Array[1], $Request_Totals
    ;Global $from, $to, $SQLResultWindow 
    ;Global $TextInput, $Question, $replace_string

    $Report_name = StringLeft ( $file, StringLen ($file)-4)

    $adoSQL = ReadTextFileTest($Directory & $file)

    if $adoSQL = "" Then
        MsgBox(0,"SQL Report","Unable to open SQL")
        Return
    EndIf

    ; set title in array 
    $SQL_Array[0] = $Report_name

    ; analyse the SQL for variable Input

    ; Search the SQL for the first occurence of \\&text string, (text input required)
    $Mypos = StringInStr ( $adoSQL, "\&text" ,0,1)
    if $Mypos > 0 Then
        $TextInput= True
        ; find the end of the input string
        $Mypos2 = StringInStr ( $adoSQL, "\&text"  ,0,2)
        $varinput = StringMid($adoSQL,$Mypos, $Mypos2 - $Mypos + 6)
        ; find the input question 
        $Question = StringMid($varinput,7, StringInStr ( $varinput, "=",0,1 ) - 7)
        ; check if predefined content
        $replace_string = StringMid($varinput,8  + StringLen($Question), StringInStr ( $varinput, "\&text",0,2 ) - 8  - StringLen($Question))
    Else
        $TextInput= False
    EndIf

    #Region manipulate parameter Input
    ; Search the SQL for the first occurence of ff/ff/ffff string, (input required)
    $Mypos = StringInStr ( $adoSQL, "ff/ff/ffff" ,0,1)
    if $Mypos > 0 Then
        $DateInput = True
        $from = _Date_Time_GetLocalTime()
        $to = _Date_Time_GetLocalTime() 
        Do 
            $input = input_period()
            $from_YYYYMMDD = StringRight($from,4) & "-" & StringMid($from,4,2) & "-" & StringLeft($from,2)
            $to_YYYYMMDD = StringRight($to,4) & "-" & StringMid($to,4,2) & "-" & StringLeft($to,2)
            ; if the dates are not correct, display error
            If $input = False Then
                Return
            ElseIf $to_YYYYMMDD < $from_YYYYMMDD Then
                MsgBox(0,"Error","Period mismatch")
            EndIf
            If $TextInput= True Then
                If $replace_string = False Then
                    Return
                ElseIf $replace_string = "" Then
                    MsgBox(0,"Error"," Input required")
                EndIf           
            EndIf
        Until $to_YYYYMMDD >= $from_YYYYMMDD

        ; replace all occurencies of the varibale by the input values
        $adoSQL = StringReplace ( $adoSQL, "ff/ff/ffff", $from, 0)
        $adoSQL = StringReplace ( $adoSQL, "tt/tt/tttt", $to, 0)
        If $TextInput= True Then
            $adoSQL = StringReplace ( $adoSQL, $varinput, $replace_string, 0)   
        EndIf
    Else
        $DateInput = False
        ; no date input but if text input required
        If $TextInput= True Then
            Do 
                $replace_string = input_text($Question, $replace_string)
                ; if the dates are not correct, display error
                If $replace_string = False Then
                    Return
                ElseIf $replace_string = "" Then
                    MsgBox(0,"Error"," Input required")
                EndIf
            Until $replace_string <> ""
            ; replace all occurencies of the varibale by the input value
            $adoSQL = StringReplace ( $adoSQL, $varinput, $replace_string, 0)   
        EndIf
    EndIf
    #cs  put the new content in a text file for testing
    $file = FileOpen ( "filename.txt", 2 )
    If $file = -1 Then
        MsgBox(0, "Error", "Unable to open file.")
        Exit
    EndIf
    FileWrite($file, $adoSQL)
    FileClose($file)
    #ce
    #EndRegion manipulate parameter Input

    #Region Oracle 
    $adoCon = ObjCreate( "ADODB.Connection" )    ; Create a COM ADODB Object  with the Beta version

    With $adoCon
        .connectionString = ($connection)
        .Open
    EndWith

    $adoRs = ObjCreate ("ADODB.Recordset") ; Create a Record Set to handle the SELECT SQL
    $adoRs.CursorType = 2
    $adoRs.LockType = 3

    ; Create a GUI window for the SQL result
    $SQLResultWindow = GUICreate($Report_name & " - SQL Result", 800, 530, -1, -1, -1, $WS_EX_ACCEPTFILES)
    GUISetBkColor(0x00E0FFFF)  ; will change background color
    $plswait = GuiCtrlCreateLabel("Please wait...", 10, 510)
    GUICtrlSetCursor($plswait, 15) ; set the cursor to hourglass over the label
    GUISetCursor(15)
    GUISetState()


# ==> Start
Local $_Left_pos, $_Top_pos, $_GUI_NAME
$_Left_pos = 338 ; Replace with correct position
$_Top_pos = 521 ; Replace with correct position
$_GUI_NAME = $SQLResultWindow
_GuiImageHole($_GUI_NAME, $_Left_pos, $_Top_pos, 220, 32)
# <== End


    ; execute the SQL
    $adoRs.Open($adoSql, $adoCon)

    With $adoRs
        $Title = ""
        For $i = 0 To .Fields.Count - 1
            $Title = $Title &  .Fields( $i ).Name  & "|"
        Next
        ; remove  the last ';'
        $Title = StringLeft ( $Title, StringLen ($Title)-1 )
        ; Creates a ListView control 
        Opt("GUIDataSeparatorChar","|") ;"|" is the default
        ; add the title to the array
        _ArrayAdd($SQL_Array, $Title) 
        $listview = GUICtrlCreateListView($Title, 10, 10, 780, 480,-1,$LVS_EX_GRIDLINES)
        GUICtrlSetCursor($listview, 15) 
        If .RecordCount Then
            $count=0
            While Not .EOF
                $count=$count+1
                $Item = ""
                For $i = 0 To .Fields.Count - 1
                    $Item = $Item &  .Fields( $i ).Value & "|"
                Next
                ; remove  the last ';'
                $Item = StringLeft ( $Item, StringLen ($Item)-1 )   
                ; add the element to the array      
                _ArrayAdd($SQL_Array, $Item)
                $content = GUICtrlCreateListViewItem($Item, $listview)
                .MoveNext
            WEnd
        EndIf
    EndWith
    ; set cursor back to normal
    GUICtrlSetCursor($plswait, 2)
    GUICtrlSetCursor($listview, 2) 
    GUISetCursor(2)
    ; remove the please wait
    GUICtrlSetState ( $plswait, $GUI_HIDE )

    #EndRegion Oracle

    ; smiley =:) talk to the user
    if $Voice = 1 Then
        _TalkOBJ($Speach_text)
    EndIf

    #Region browser Menu
    $buttonSQL = GUICtrlCreateButton("SQL", 10, 500, 70, 20)
    $buttonExport = GUICtrlCreateButton("Export", 90, 500, 70, 20)
    $buttonExportCSV = GUICtrlCreateButton("Export CSV", 170, 500, 70, 20)
    $buttonAbout = GUICtrlCreateButton("?", 690, 500, 20, 20 )
    $ButtonClose = GUICtrlCreateButton("Close", 720, 500, 70, 20)   
    if UBound($SQL_Array)=2 Then  ; if there are no records to display... (only Main title and field titles will be here)
        GUICtrlSetState ( $buttonExport, $GUI_DISABLE )
        GUICtrlSetState ( $buttonExportCSV, $GUI_DISABLE )
    EndIf

    GUISetState()
    Do
        $msg = GUIGetMsg()
        Select
        Case $msg = $ButtonClose
                GUIDelete($SQLResultWindow)
                return
            Case $msg = $buttonAbout 
                about()
            Case $msg = $buttonSQL
                MsgBox(0,"SQL" , $adoSQL)   
            Case $msg = $buttonExportCSV
                ; create in Temp directory
                $Temp_path = EnvGet("TEMP")
                $File_name = StringReplace($SQL_Array[0]," ","_") & ".csv"
                $file = FileOpen($Temp_path & "\" & $File_name, 2)
                ; Check if file opened for writing OK
                If $file = -1 Then
                    MsgBox(0, "Error", @error & @CR & "Unable to create file.")
                EndIf
                For $i = 1 To (UBound($SQL_Array) - 1)
                    FileWrite($file, StringReplace($SQL_Array[$i],"|",$Separator) & @CRLF)
                Next
                FileClose($file)
                $rc = _RunDos("start " & $Temp_path & "\" & $File_name)
            Case $msg = $buttonExport
                ; create excel spreadsheet here
                $pleasewait = GUICreate("Creating Excel Sheet", 50, 60, 100, 200, $WS_BORDER, $WS_EX_TOPMOST)
                GuiCtrlCreateLabel("Please wait...", 20, 10)
                GUISetState()               
                Export_to_Excel()
                GUIDelete($pleasewait)  
        EndSelect
    Until $msg = $GUI_EVENT_CLOSE
    GUIDelete($SQLResultWindow)
    #EndRegion browser Menu
EndFunc ;==>executeSQL
#FUNCTION# ==============================================================
Func About()
    msgbox(64, "Oracle SQL Report Generator" , "By GreenCan"  & @CR & "October 2008")
    Return
EndFunc  ;==>About
#FUNCTION# ==============================================================
Func _TalkOBJ($s_text)
    Local $o_speech
    $o_speech = ObjCreate("SAPI.SpVoice")
    $o_speech.Speak ($s_text)
    $o_speech = ""
EndFunc ;==>_TalkOBJ
#FUNCTION# ==============================================================
Func MyErrFunc()
; error Handler
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"Oracle Report","We intercepted a COM Error !"       & @CRLF  & @CRLF & _
             "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
             "err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "         & @TAB & $HexNumber              & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
             "err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
             "err.helpcontext is: "    & @TAB & $oMyError.helpcontext _
            )
            exit
Endfunc ;==>MyErrFunc
#FUNCTION# ==============================================================
Func ReadTextFileTest($file)
    ; Open and read the SQL text file
    $file = FileOpen($file, 0) 
    ; Check if file opened for reading OK
    If $file = -1 Then
        MsgBox(0, "Error", "Unable to open file " & $file)
        return ""
    Else
        $adoSQL = FileRead($file)
    EndIf
    FileClose($file)
    return $adoSQL
Endfunc ;==>ReadTextFileTest
#FUNCTION# ==============================================================
Func input_period()
    Local $msg, $run_ok
    ; check if a combined input text and dates is needed
    If $TextInput = True Then ; we need a bigger window
        $window_width = 160 + StringLen($Question)*4
        $input_Window = GUICreate("SQL input", $window_width, 200, -1, -1,$WS_BORDER)

        GuiCtrlCreateLabel("From:", 10, 20)
        $from = GUICtrlCreateDate($from, $window_width  - 115 , 20, 100, 20,$DTS_SHORTDATEFORMAT)
        GuiCtrlCreateLabel("To:", 10, 50)
        $to = GUICtrlCreateDate($to, $window_width  - 115 , 50, 100, 20,$DTS_SHORTDATEFORMAT)       
        GuiCtrlCreateLabel($Question, 10, 80)
        $replace_string = guictrlcreateinput($replace_string, $window_width  - 115 , 80, 100, 20)
        $run_ok = GUICtrlCreateButton("Run", ($window_width  - 70)/2, 130, 70, 20, $BS_DEFPUSHBUTTON )
    Else
        $input_Window = GUICreate("SQL input", 160, 170, -1, -1,$WS_BORDER)
        GuiCtrlCreateLabel("From:", 10, 20)
        $from = GUICtrlCreateDate($from, 45, 20, 100, 20,$DTS_SHORTDATEFORMAT)
        GuiCtrlCreateLabel("To:", 10, 50)
        $to = GUICtrlCreateDate($to, 45, 50, 100, 20,$DTS_SHORTDATEFORMAT)  
        $run_ok = GUICtrlCreateButton("Run", 45, 100, 70, 20, $BS_DEFPUSHBUTTON )
    EndIf
    
    GUISetState()   
    ; Run the GUI until the dialog is closed
    Do
        $msg = GUIGetMsg()
        Select
        Case $msg = $run_ok
            If $TextInput = True Then
                $replace_string = GUICtrlRead($replace_string)
            EndIf
            $from = GUICtrlRead($from)
            $to = GUICtrlRead($to)
            GUIDelete($input_Window)
            Return True
        EndSelect
    Until $msg = $GUI_EVENT_CLOSE
    GUIDelete($input_Window)
    Return False    
EndFunc ;==>input_period
#FUNCTION# ==============================================================
Func input_text($Question, $replace_string)
    Local $msg, $run_ok
    $window_width = 160 + StringLen($Question)*4
    $input_text = GUICreate("SQL input", $window_width, 170, -1, -1,$WS_BORDER)
    GuiCtrlCreateLabel($Question, 10, 20)
    $replace_string = guictrlcreateinput($replace_string, $window_width  - 115 , 20, 100, 20)


    $run_ok = GUICtrlCreateButton("Run", ($window_width  - 70)/2 , 100, 70, 20, $BS_DEFPUSHBUTTON)
    GUISetState()   
    ; Run the GUI until the dialog is closed
    Do
        $msg = GUIGetMsg()
        Select
        Case $msg = $run_ok
            $replace_string = GUICtrlRead($replace_string)
            GUIDelete($input_text)
            return $replace_string
        EndSelect
    Until $msg = $GUI_EVENT_CLOSE
    GUIDelete($input_text)
    Return False
EndFunc ;==>input_text
#FUNCTION# ==============================================================
Func Export_to_Excel()
    Local $oExcel = _ExcelBookNew(0) ; 1 = visible ; 0 = hidden

    ; write each line
    For $rows = 0 To (UBound($SQL_Array) - 1)
        $single_line = StringSplit ( $SQL_Array[$rows], "|")
        For $colums = 1 To (UBound($single_line) - 1)
            ; Write a message to the cell of the first sheet
            ; remove leading blank for numbers
            $results = _ExcelWriteCell($oExcel, StringStripWS($single_line[$colums],1), $rows+1,$colums)
            if $results<1 Then
                MsgBox(0,"",$results & @cr & @error)
            EndIf
        Next
    Next

    ; check if Totals are requested when exporting to Excel
    $Mypos = StringInStr ( $adoSQL, "CALCULATE TOTAL" )
    if $Mypos > 0 Then
        $Request_Totals = True
        $CellTitle = "Totals"   
    Else
        $Request_Totals = False
    EndIf

    $Mypos2 = StringInStr ( $adoSQL, "CALCULATE AVERAGE" )
    if $Mypos2 > 0 Then
        $Request_Average = True
        $CellTitle = "Averages"
    Else
        $Request_Average = False
    EndIf

    ; Generate Automatic Totals
    $Totals = False ; Default is No Totals or averages
    For $i = 1 To (UBound($single_line) - 1)
        ; all the variable are strings, so converting
        ; string to decimal
        ; string to hh:mm

        ; strip the leading white space first
        $content = StringStripWS($single_line[$i], 1 )
    
        ; Format each column according to the value found in the last row.
        if _ValidateString($content, "1234567890.") then ; this is a number
            if $Request_Totals = True Then
                $formula = "=SUM(" & ColumnLetter($i)  & "3:" & ColumnLetter($i) &  string($rows) & ")"
                $cell = ColumnLetter($i) &  string($rows+2)
                $results = _ExcelWriteFormula($oExcel, $formula ,  $cell)       
                $Totals = True
            ElseIf  $Request_Average = True Then
                $formula = "=Average(" & ColumnLetter($i)  & "3:" & ColumnLetter($i) &  string($rows) & ")"
                $cell = ColumnLetter($i) &  string($rows+2)
                $results = _ExcelWriteFormula($oExcel, $formula ,  $cell)       
                $Totals = True
            EndIf
            if not _ValidateString($content, "1234567890") then ; this are no integers
                ; Format Array records
                $sRangeOrRowStart = 3
                $iColStart = $i
                $iRowEnd = UBound($SQL_Array)+2
                $iColEnd = $i
                $sFormat = "0.00"
                _ExcelNumberFormat($oExcel, $sFormat, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd)
                ; set horizontal alignment 
                $sRangeOrRowStart = 2 ; including the column title
                $sHorizAlign = "right"
                _ExcelHorizontalAlignSet($oExcel, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd, $sHorizAlign)
            EndIf   
        ElseIf _ValidateString($content, "1234567890:") then ; this is a HH:MM format
            if $Request_Totals = True Then
                $formula = "=SUM(" & ColumnLetter($i)  & "3:" & ColumnLetter($i) &  string($rows) & ")"
                $cell = ColumnLetter($i) &  string($rows+2)
                $results = _ExcelWriteFormula($oExcel, $formula ,  $cell)   
                $Totals = True  
            ElseIf $Request_Average = True Then
                $formula = "=Average(" & ColumnLetter($i)  & "3:" & ColumnLetter($i) &  string($rows) & ")"
                $cell = ColumnLetter($i) &  string($rows+2)
                $results = _ExcelWriteFormula($oExcel, $formula ,  $cell)   
                $Totals = True  
            EndIf
        
            ; Format Array records
            $sRangeOrRowStart = 3
            $iColStart = $i
            $iRowEnd = UBound($SQL_Array)+2
            $iColEnd = $i
            $sFormat = "[u]:mm"
            _ExcelNumberFormat($oExcel, $sFormat, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd)
            ; set horizontal alignment 
            $sRangeOrRowStart = 2 ; including the column title
            $sHorizAlign = "right"
            _ExcelHorizontalAlignSet($oExcel, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd, $sHorizAlign)  
        EndIf
        if $results<1 Then
            MsgBox(0,"",$results & @cr & @error)
        EndIf
    Next

    If $Totals = True and ($Request_Totals = True or $Request_Average = True) Then
        _ExcelWriteCell($oExcel, $CellTitle, $rows+1,1)
        ; Totals Line exist thus color it
        ; font color
        $sRangeOrRowStart = $rows+1
        $iColStart = 1
        $iRowEnd = $rows+2
        $iColEnd = $colums-1
        $iColorIndex = 255
        $hColor = 0x000000
        _ExcelFontSetColor($oExcel, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd, $iColorIndex, $hColor)

        ; color the background
        $sRangeOrRowStart = $rows+1
        $iColStart = 1
        $iRowEnd = $rows+2
        $iColEnd = $colums-1
        $iColorIndex = 255
        $hColor = 0x0FFFFC
        _ExcelCellColorSet($oExcel, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd, $iColorIndex, $hColor)

        ; set bold, italic and underline
        $sRangeOrRowStart = $rows+1
        $iColStart = 1
        $iRowEnd = $rows+2
        $iColEnd = $colums-1
        $fBold = True
        $fItalic = False
        $fUnderline = False
        _ExcelFontSetProperties($oExcel, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd, $fBold, $fItalic, $fUnderline)
    EndIf

    ; Array titles

    ; font color
    $sRangeOrRowStart = 2
    $iColStart = 1
    $iRowEnd = 2
    $iColEnd = $colums-1
    $iColorIndex = 255
    $hColor = 0x000000
    _ExcelFontSetColor($oExcel, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd, $iColorIndex, $hColor)

    ; color the background
    $sRangeOrRowStart = 2
    $iColStart = 1
    $iRowEnd = 2
    $iColEnd = $colums-1
    $iColorIndex = 255
    $hColor = 0x533FF66
    _ExcelCellColorSet($oExcel, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd, $iColorIndex, $hColor)

    ; Now Merge the main title line
    $fDoMerge = True
    $sRangeOrRowStart = 1
    $iColStart = 1
    $iRowEnd = 1
    $iColEnd = $colums-1
    $results = _ExcelCellMerge($oExcel, $fDoMerge, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd)
    if $results<1 Then
        MsgBox(0,"",$results & @cr & @error)
    EndIf   

    ; set cell range main  Title

    ; font color
    $sRangeOrRowStart = 1
    $iColStart = 1
    $iRowEnd = 1
    $iColEnd = 1
    $iColorIndex = 255
    $hColor = 0xFFFF00
    _ExcelFontSetColor($oExcel, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd, $iColorIndex, $hColor)

    ; color the background
    $sRangeOrRowStart = 1
    $iColStart = 1
    $iRowEnd = 1
    $iColEnd = 1
    $iColorIndex = 255
    $hColor = 0x5A0500
    _ExcelCellColorSet($oExcel, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd, $iColorIndex, $hColor)

    ; set bold, italic and underline
    $sRangeOrRowStart = 1
    $fBold = True
    $fItalic = True
    $fUnderline = False
    _ExcelFontSetProperties($oExcel, $sRangeOrRowStart, 1, 1, 1, $fBold, $fItalic, $fUnderline)

    ; set the font
    $sRangeOrRowStart = 1
    $iColStart = 1
    $iRowEnd = 1
    $iColEnd = 1 
    $sFontName = "Arial Rounded MT Bold"
    _ExcelFontSet($oExcel, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd, $sFontName)

    ; set the font size
    $sRangeOrRowStart = 1
    $iColStart = 1
    $iRowEnd = 1
    $iColEnd = 1
    $iFontSize = 15
    _ExcelFontSetSize($oExcel, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd, $iFontSize)

    ;set row height
    $iRow = "1:1"
    $vHeight= 50
    _ExcelRowHeightSet($oExcel, $iRow, $vHeight)

    ; set horizontal alignment of title only
    $sRangeOrRowStart = 1
    $iColStart = 1 
    $iRowEnd = 1
    $iColEnd = $colums-1
    $sHorizAlign = "center"

    _ExcelHorizontalAlignSet($oExcel, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd, $sHorizAlign)

    ; set vertical alignment
    $sRangeOrRowStart = 1
    $iColStart = 1 
    $iRowEnd = UBound($SQL_Array)
    $iColEnd = $colums-1

    $sVertAlign = "center"
    _ExcelVerticalAlignSet($oExcel, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd , $sVertAlign)

    ; autofit height and width
    $iRow = "1:" & _ExcelSheetNumUsedRowGet($oExcel, 1)
    $vHeight = "autofit"
    _ExcelRowHeightSet($oExcel, $iRow, $vHeight)
    
    ; Set the column width of the specified column.
    $vColumn = "A:" & ColumnLetter($iColEnd) 
    $vWidth = "autofit"
    _ExcelColWidthSet($oExcel, $vColumn, $vWidth)

    ; Set the name of the active sheet.
    $sSheetName = "SQL result"
    _ExcelSheetNameSet($oExcel, $sSheetName)
    
    ; now show the sheet
    _ExcelShow($oExcel)             
EndFunc ;==>Export_to_Excel
#FUNCTION# ==============================================================       
func _ValidateString($string, $allowed)
    ;Validatestring by spyrorocks
    $tstring = stringsplit($string, "")
    for $i = 1 to $tstring[0]
        $isgood = true;
        if stringinstr($allowed, $tstring[$i]) = 0 then
            $isgood = false;
        endif
        if $isgood = false then return false
    next
    return true
EndFunc ;==>_ValidateString
#FUNCTION# ==============================================================
Func ColumnLetter($ColumnNumber) 
    If $ColumnNumber > 26 Then
        $ColumnLetter = Chr(Int(($ColumnNumber - 1) / 26) + 64) & Chr( (Mod($ColumnNumber - 1, 26)) + 65)
    Else
        ; Columns A-Z
        $ColumnLetter = Chr($ColumnNumber + 64)
    EndIf
Return $ColumnLetter
EndFunc ;==>ColumnLetter
#FUNCTION# ==============================================================
; Name...........: _IniMem_Read
; Description ...: Reads a value from a standard format ini string
; Syntax.........: _IniMem_Read($s_ini, $s_Section, $s_key, [$s_default])
; Parameters ....: $s_ini - The ini string
;                  $s_Section  - The section you want to read
;               $s_Key - The key you want to get
;               $s_default - the return value when unable to read the key (default = "")
; Return values .: Success - value of the selected key
;                  Failure - $s_default
; Author ........: Alek
; =========================================================================
Func _IniMem_Read($s_ini, $s_Section, $s_key, $s_default = "")
    $s_ini = StringSplit($s_ini, @CRLF)
    For $x = 1 To $s_ini[0]
        If $s_ini[$x] = "[" & $s_Section & "]" Then ExitLoop
    Next

    If $x > $s_ini[0] Then Return $s_default
    
    For $i = $x+1 To $s_ini[0]
        If StringLeft($s_ini[$i],1) = "[" And StringRight($s_ini[$i],1) = "]" Then ExitLoop
        If $s_ini[$i] = "" Then ContinueLoop
        
        If StringLeft($s_ini[$i], StringLen($s_key)) = $s_key Then Return StringTrimLeft($s_ini[$i], StringLen($s_key) + 1)
    Next
    
    Return $s_default
EndFunc ;==>_IniMem_Read
#FUNCTION# ==============================================================
;===============================================================================
; Function:     _ExcelSheetNumUsedRowGet
; Description:      Return the number of rows ibeing used in the specified worksheet.
; Syntax:           _ExcelSheetNumUsedRowGet($oExcel, $sSheetName)
; Parameter(s):     $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                    $sSheetName - The sheet name to be checked.
; Requirement(s):   None
; Return Value(s):  On Success - Returns the number of rows being used in the specified worksheet.
;                   On Failure - Returns 0 and sets @error on errors:
;                        @error=1 - Specified object does not exist
; Author(s):        Stanley Lim <voided_lim@yahoo.com.sg>
; Note(s):          None
;
;===============================================================================
Func _ExcelSheetNumUsedRowGet($oExcel, $iSheetNum)
    Dim $iRowCount
    
    If NOT IsObj($oExcel) Then
        $iRowCount = SetError(1, 0, 0)
    Else
        $oExcel.Application.DisplayAlerts = 0
        $oExcel.Application.ScreenUpdating = 0
        $iRowCount = $oExcel.ActiveWorkbook.Sheets($iSheetNum).UsedRange.Rows.Count
        $oExcel.Application.DisplayAlerts = 1
        $oExcel.Application.ScreenUpdating = 1
    EndIf
    
    Return $iRowCount
    
EndFunc;==>_ExcelSheetNumUsedRowGet
#FUNCTION# ==============================================================
#Region Perforated image
#comments-start
    The lines below will generate the perforated image (bewteen start and end)
    Move these lines into your GUI code, usually just before GUISetState()
    Don't forget to fill in the correct coordinates for $Left_pos, $Top_pos
    and enter the GUI Window Handle in the last line

# ==> Start
Local $_Left_pos, $_Top_pos, $_GUI_NAME
$_Left_pos = 10 ; Replace with correct position
$_Top_pos =  _WinAPI_GetSystemMetrics(4) ; Just below the title bar (Must #include <WinAPI.au3> if using this function!!!)
$_GUI_NAME = 'The name of your GUI window'
_GuiImageHole($_GUI_NAME, $_Left_pos, $_Top_pos, 220, 32)
# <== End

#comments-end

#FUNCTION# ==============================================================
Func _GuiImageHole($window_handle, $pos_x, $pos_y,$Image_Width ,$Image_Height)
    Local $aClassList, $aM_Mask, $aMask
#Region picture array
Local $PictArray[621]
$PictArray[0] = '1,1,8,1'
$PictArray[1] = '12,1,26,1'
$PictArray[2] = '31,1,58,1'
$PictArray[3] = '62,1,93,1'
$PictArray[4] = '98,1,123,1'
$PictArray[5] = '128,1,141,1'
$PictArray[6] = '146,1,173,1'
$PictArray[7] = '178,1,193,1'
$PictArray[8] = '195,1,210,1'
$PictArray[9] = '215,1,220,1'
$PictArray[10] = '1,2,8,2'
$PictArray[11] = '12,2,26,2'
$PictArray[12] = '30,2,42,2'
$PictArray[13] = '46,2,57,2'
$PictArray[14] = '62,2,77,2'
$PictArray[15] = '81,2,93,2'
$PictArray[16] = '98,2,110,2'
$PictArray[17] = '113,2,123,2'
$PictArray[18] = '128,2,141,2'
$PictArray[19] = '146,2,158,2'
$PictArray[20] = '162,2,173,2'
$PictArray[21] = '177,2,192,2'
$PictArray[22] = '197,2,210,2'
$PictArray[23] = '215,2,220,2'
$PictArray[24] = '1,3,7,3'
$PictArray[25] = '13,3,25,3'
$PictArray[26] = '31,3,42,3'
$PictArray[27] = '47,3,57,3'
$PictArray[28] = '62,3,76,3'
$PictArray[29] = '81,3,93,3'
$PictArray[30] = '98,3,109,3'
$PictArray[31] = '114,3,123,3'
$PictArray[32] = '129,3,140,3'
$PictArray[33] = '146,3,157,3'
$PictArray[34] = '162,3,172,3'
$PictArray[35] = '178,3,192,3'
$PictArray[36] = '197,3,210,3'
$PictArray[37] = '214,3,220,3'
$PictArray[38] = '1,4,7,4'
$PictArray[39] = '13,4,23,4'
$PictArray[40] = '33,4,41,4'
$PictArray[41] = '47,4,55,4'
$PictArray[42] = '64,4,76,4'
$PictArray[43] = '81,4,92,4'
$PictArray[44] = '99,4,109,4'
$PictArray[45] = '114,4,122,4'
$PictArray[46] = '129,4,139,4'
$PictArray[47] = '148,4,157,4'
$PictArray[48] = '162,4,171,4'
$PictArray[49] = '179,4,191,4'
$PictArray[50] = '197,4,209,4'
$PictArray[51] = '215,4,220,4'
$PictArray[52] = '1,5,6,5'
$PictArray[53] = '13,5,22,5'
$PictArray[54] = '33,5,42,5'
$PictArray[55] = '46,5,55,5'
$PictArray[56] = '65,5,76,5'
$PictArray[57] = '81,5,90,5'
$PictArray[58] = '100,5,109,5'
$PictArray[59] = '114,5,122,5'
$PictArray[60] = '129,5,138,5'
$PictArray[61] = '148,5,158,5'
$PictArray[62] = '162,5,170,5'
$PictArray[63] = '180,5,191,5'
$PictArray[64] = '197,5,207,5'
$PictArray[65] = '216,5,220,5'
$PictArray[66] = '1,6,6,6'
$PictArray[67] = '14,6,22,6'
$PictArray[68] = '34,6,41,6'
$PictArray[69] = '47,6,55,6'
$PictArray[70] = '65,6,75,6'
$PictArray[71] = '82,6,89,6'
$PictArray[72] = '101,6,109,6'
$PictArray[73] = '114,6,121,6'
$PictArray[74] = '130,6,138,6'
$PictArray[75] = '149,6,157,6'
$PictArray[76] = '163,6,170,6'
$PictArray[77] = '181,6,190,6'
$PictArray[78] = '198,6,206,6'
$PictArray[79] = '217,6,220,6'
$PictArray[80] = '1,7,5,7'
$PictArray[81] = '15,7,21,7'
$PictArray[82] = '34,7,40,7'
$PictArray[83] = '48,7,54,7'
$PictArray[84] = '65,7,75,7'
$PictArray[85] = '82,7,89,7'
$PictArray[86] = '101,7,109,7'
$PictArray[87] = '115,7,121,7'
$PictArray[88] = '130,7,137,7'
$PictArray[89] = '149,7,156,7'
$PictArray[90] = '163,7,170,7'
$PictArray[91] = '181,7,190,7'
$PictArray[92] = '199,7,206,7'
$PictArray[93] = '217,7,220,7'
$PictArray[94] = '1,8,5,8'
$PictArray[95] = '15,8,21,8'
$PictArray[96] = '34,8,39,8'
$PictArray[97] = '48,8,54,8'
$PictArray[98] = '66,8,74,8'
$PictArray[99] = '83,8,89,8'
$PictArray[100] = '101,8,107,8'
$PictArray[101] = '116,8,120,8'
$PictArray[102] = '131,8,137,8'
$PictArray[103] = '150,8,155,8'
$PictArray[104] = '164,8,170,8'
$PictArray[105] = '182,8,189,8'
$PictArray[106] = '199,8,205,8'
$PictArray[107] = '218,8,220,8'
$PictArray[108] = '1,9,4,9'
$PictArray[109] = '16,9,20,9'
$PictArray[110] = '35,9,39,9'
$PictArray[111] = '49,9,54,9'
$PictArray[112] = '66,9,73,9'
$PictArray[113] = '83,9,88,9'
$PictArray[114] = '102,9,107,9'
$PictArray[115] = '116,9,119,9'
$PictArray[116] = '131,9,136,9'
$PictArray[117] = '150,9,154,9'
$PictArray[118] = '165,9,169,9'
$PictArray[119] = '182,9,188,9'
$PictArray[120] = '199,9,205,9'
$PictArray[121] = '218,9,220,9'
$PictArray[122] = '1,10,4,10'
$PictArray[123] = '17,10,20,10'
$PictArray[124] = '23,10,24,10'
$PictArray[125] = '35,10,38,10'
$PictArray[126] = '49,10,54,10'
$PictArray[127] = '67,10,73,10'
$PictArray[128] = '84,10,87,10'
$PictArray[129] = '102,10,106,10'
$PictArray[130] = '117,10,119,10'
$PictArray[131] = '132,10,136,10'
$PictArray[132] = '139,10,140,10'
$PictArray[133] = '151,10,154,10'
$PictArray[134] = '165,10,169,10'
$PictArray[135] = '178,10,179,10'
$PictArray[136] = '183,10,188,10'
$PictArray[137] = '200,10,205,10'
$PictArray[138] = '218,10,220,10'
$PictArray[139] = '1,11,3,11'
$PictArray[140] = '18,11,19,11'
$PictArray[141] = '23,11,24,11'
$PictArray[142] = '36,11,38,11'
$PictArray[143] = '50,11,53,11'
$PictArray[144] = '56,11,56,11'
$PictArray[145] = '63,11,64,11'
$PictArray[146] = '67,11,72,11'
$PictArray[147] = '84,11,87,11'
$PictArray[148] = '91,11,91,11'
$PictArray[149] = '103,11,105,11'
$PictArray[150] = '117,11,118,11'
$PictArray[151] = '122,11,122,11'
$PictArray[152] = '133,11,135,11'
$PictArray[153] = '139,11,139,11'
$PictArray[154] = '151,11,153,11'
$PictArray[155] = '165,11,169,11'
$PictArray[156] = '171,11,171,11'
$PictArray[157] = '178,11,179,11'
$PictArray[158] = '183,11,187,11'
$PictArray[159] = '200,11,204,11'
$PictArray[160] = '219,11,220,11'
$PictArray[161] = '1,12,2,12'
$PictArray[162] = '6,12,7,12'
$PictArray[163] = '14,12,14,12'
$PictArray[164] = '23,12,24,12'
$PictArray[165] = '32,12,33,12'
$PictArray[166] = '36,12,37,12'
$PictArray[167] = '40,12,41,12'
$PictArray[168] = '47,12,47,12'
$PictArray[169] = '50,12,53,12'
$PictArray[170] = '55,12,56,12'
$PictArray[171] = '63,12,64,12'
$PictArray[172] = '68,12,72,12'
$PictArray[173] = '85,12,86,12'
$PictArray[174] = '90,12,91,12'
$PictArray[175] = '99,12,99,12'
$PictArray[176] = '103,12,105,12'
$PictArray[177] = '117,12,118,12'
$PictArray[178] = '121,12,122,12'
$PictArray[179] = '129,12,130,12'
$PictArray[180] = '133,12,134,12'
$PictArray[181] = '138,12,139,12'
$PictArray[182] = '147,12,148,12'
$PictArray[183] = '151,12,153,12'
$PictArray[184] = '156,12,157,12'
$PictArray[185] = '165,12,168,12'
$PictArray[186] = '170,12,171,12'
$PictArray[187] = '178,12,180,12'
$PictArray[188] = '183,12,187,12'
$PictArray[189] = '201,12,203,12'
$PictArray[190] = '207,12,207,12'
$PictArray[191] = '219,12,220,12'
$PictArray[192] = '1,13,1,13'
$PictArray[193] = '5,13,7,13'
$PictArray[194] = '14,13,15,13'
$PictArray[195] = '22,13,24,13'
$PictArray[196] = '32,13,33,13'
$PictArray[197] = '36,13,37,13'
$PictArray[198] = '39,13,41,13'
$PictArray[199] = '46,13,47,13'
$PictArray[200] = '50,13,52,13'
$PictArray[201] = '55,13,56,13'
$PictArray[202] = '63,13,65,13'
$PictArray[203] = '68,13,71,13'
$PictArray[204] = '74,13,74,13'
$PictArray[205] = '90,13,91,13'
$PictArray[206] = '99,13,100,13'
$PictArray[207] = '104,13,105,13'
$PictArray[208] = '107,13,109,13'
$PictArray[209] = '114,13,115,13'
$PictArray[210] = '121,13,122,13'
$PictArray[211] = '129,13,130,13'
$PictArray[212] = '137,13,139,13'
$PictArray[213] = '147,13,148,13'
$PictArray[214] = '152,13,152,13'
$PictArray[215] = '155,13,157,13'
$PictArray[216] = '162,13,163,13'
$PictArray[217] = '166,13,167,13'
$PictArray[218] = '170,13,171,13'
$PictArray[219] = '178,13,180,13'
$PictArray[220] = '184,13,186,13'
$PictArray[221] = '189,13,190,13'
$PictArray[222] = '201,13,202,13'
$PictArray[223] = '206,13,208,13'
$PictArray[224] = '216,13,217,13'
$PictArray[225] = '220,13,220,13'
$PictArray[226] = '4,14,7,14'
$PictArray[227] = '14,14,16,14'
$PictArray[228] = '21,14,24,14'
$PictArray[229] = '32,14,34,14'
$PictArray[230] = '39,14,41,14'
$PictArray[231] = '47,14,48,14'
$PictArray[232] = '51,14,51,14'
$PictArray[233] = '54,14,56,14'
$PictArray[234] = '63,14,65,14'
$PictArray[235] = '73,14,75,14'
$PictArray[236] = '82,14,83,14'
$PictArray[237] = '89,14,91,14'
$PictArray[238] = '99,14,101,14'
$PictArray[239] = '107,14,109,14'
$PictArray[240] = '114,14,115,14'
$PictArray[241] = '120,14,122,14'
$PictArray[242] = '129,14,131,14'
$PictArray[243] = '136,14,139,14'
$PictArray[244] = '147,14,149,14'
$PictArray[245] = '155,14,157,14'
$PictArray[246] = '162,14,164,14'
$PictArray[247] = '166,14,167,14'
$PictArray[248] = '169,14,171,14'
$PictArray[249] = '178,14,181,14'
$PictArray[250] = '188,14,190,14'
$PictArray[251] = '206,14,207,14'
$PictArray[252] = '216,14,217,14'
$PictArray[253] = '220,14,220,14'
$PictArray[254] = '1,15,6,15'
$PictArray[255] = '15,15,17,15'
$PictArray[256] = '19,15,24,15'
$PictArray[257] = '32,15,35,15'
$PictArray[258] = '38,15,41,15'
$PictArray[259] = '47,15,49,15'
$PictArray[260] = '54,15,56,15'
$PictArray[261] = '63,15,66,15'
$PictArray[262] = '72,15,75,15'
$PictArray[263] = '82,15,83,15'
$PictArray[264] = '88,15,91,15'
$PictArray[265] = '99,15,102,15'
$PictArray[266] = '106,15,108,15'
$PictArray[267] = '114,15,116,15'
$PictArray[268] = '119,15,122,15'
$PictArray[269] = '130,15,139,15'
$PictArray[270] = '147,15,150,15'
$PictArray[271] = '154,15,156,15'
$PictArray[272] = '162,15,164,15'
$PictArray[273] = '169,15,171,15'
$PictArray[274] = '179,15,182,15'
$PictArray[275] = '187,15,190,15'
$PictArray[276] = '198,15,199,15'
$PictArray[277] = '205,15,207,15'
$PictArray[278] = '216,15,218,15'
$PictArray[279] = '1,16,6,16'
$PictArray[280] = '15,16,23,16'
$PictArray[281] = '32,16,41,16'
$PictArray[282] = '47,16,49,16'
$PictArray[283] = '53,16,56,16'
$PictArray[284] = '64,16,68,16'
$PictArray[285] = '70,16,75,16'
$PictArray[286] = '83,16,91,16'
$PictArray[287] = '100,16,108,16'
$PictArray[288] = '115,16,116,16'
$PictArray[289] = '119,16,122,16'
$PictArray[290] = '130,16,138,16'
$PictArray[291] = '147,16,156,16'
$PictArray[292] = '163,16,165,16'
$PictArray[293] = '168,16,171,16'
$PictArray[294] = '179,16,190,16'
$PictArray[295] = '198,16,201,16'
$PictArray[296] = '203,16,207,16'
$PictArray[297] = '216,16,219,16'
$PictArray[298] = '1,17,7,17'
$PictArray[299] = '14,17,23,17'
$PictArray[300] = '32,17,40,17'
$PictArray[301] = '47,17,55,17'
$PictArray[302] = '64,17,74,17'
$PictArray[303] = '83,17,90,17'
$PictArray[304] = '100,17,108,17'
$PictArray[305] = '115,17,122,17'
$PictArray[306] = '129,17,138,17'
$PictArray[307] = '147,17,155,17'
$PictArray[308] = '163,17,171,17'
$PictArray[309] = '179,17,190,17'
$PictArray[310] = '198,17,207,17'
$PictArray[311] = '216,17,220,17'
$PictArray[312] = '1,18,7,18'
$PictArray[313] = '14,18,23,18'
$PictArray[314] = '32,18,40,18'
$PictArray[315] = '48,18,55,18'
$PictArray[316] = '64,18,74,18'
$PictArray[317] = '83,18,90,18'
$PictArray[318] = '100,18,108,18'
$PictArray[319] = '115,18,122,18'
$PictArray[320] = '129,18,138,18'
$PictArray[321] = '147,18,156,18'
$PictArray[322] = '163,18,171,18'
$PictArray[323] = '179,18,190,18'
$PictArray[324] = '198,18,207,18'
$PictArray[325] = '216,18,220,18'
$PictArray[326] = '1,19,7,19'
$PictArray[327] = '15,19,23,19'
$PictArray[328] = '32,19,40,19'
$PictArray[329] = '48,19,55,19'
$PictArray[330] = '64,19,75,19'
$PictArray[331] = '83,19,90,19'
$PictArray[332] = '100,19,107,19'
$PictArray[333] = '116,19,122,19'
$PictArray[334] = '130,19,138,19'
$PictArray[335] = '147,19,155,19'
$PictArray[336] = '164,19,171,19'
$PictArray[337] = '175,19,175,19'
$PictArray[338] = '179,19,190,19'
$PictArray[339] = '198,19,207,19'
$PictArray[340] = '216,19,220,19'
$PictArray[341] = '1,20,7,20'
$PictArray[342] = '15,20,22,20'
$PictArray[343] = '32,20,40,20'
$PictArray[344] = '48,20,55,20'
$PictArray[345] = '59,20,60,20'
$PictArray[346] = '64,20,75,20'
$PictArray[347] = '82,20,90,20'
$PictArray[348] = '100,20,107,20'
$PictArray[349] = '116,20,122,20'
$PictArray[350] = '130,20,138,20'
$PictArray[351] = '147,20,155,20'
$PictArray[352] = '164,20,171,20'
$PictArray[353] = '175,20,176,20'
$PictArray[354] = '179,20,190,20'
$PictArray[355] = '198,20,206,20'
$PictArray[356] = '216,20,220,20'
$PictArray[357] = '1,21,6,21'
$PictArray[358] = '15,21,22,21'
$PictArray[359] = '27,21,28,21'
$PictArray[360] = '32,21,39,21'
$PictArray[361] = '48,21,55,21'
$PictArray[362] = '59,21,60,21'
$PictArray[363] = '64,21,74,21'
$PictArray[364] = '83,21,89,21'
$PictArray[365] = '100,21,107,21'
$PictArray[366] = '116,21,122,21'
$PictArray[367] = '129,21,137,21'
$PictArray[368] = '142,21,143,21'
$PictArray[369] = '148,21,155,21'
$PictArray[370] = '164,21,171,21'
$PictArray[371] = '174,21,176,21'
$PictArray[372] = '179,21,190,21'
$PictArray[373] = '198,21,206,21'
$PictArray[374] = '216,21,220,21'
$PictArray[375] = '1,22,6,22'
$PictArray[376] = '15,22,22,22'
$PictArray[377] = '26,22,28,22'
$PictArray[378] = '32,22,39,22'
$PictArray[379] = '48,22,55,22'
$PictArray[380] = '59,22,60,22'
$PictArray[381] = '64,22,74,22'
$PictArray[382] = '82,22,90,22'
$PictArray[383] = '94,22,95,22'
$PictArray[384] = '100,22,106,22'
$PictArray[385] = '116,22,121,22'
$PictArray[386] = '129,22,138,22'
$PictArray[387] = '142,22,143,22'
$PictArray[388] = '148,22,154,22'
$PictArray[389] = '164,22,170,22'
$PictArray[390] = '174,22,176,22'
$PictArray[391] = '179,22,190,22'
$PictArray[392] = '198,22,206,22'
$PictArray[393] = '211,22,211,22'
$PictArray[394] = '216,22,220,22'
$PictArray[395] = '1,23,6,23'
$PictArray[396] = '15,23,22,23'
$PictArray[397] = '26,23,28,23'
$PictArray[398] = '32,23,40,23'
$PictArray[399] = '48,23,55,23'
$PictArray[400] = '59,23,60,23'
$PictArray[401] = '64,23,74,23'
$PictArray[402] = '83,23,89,23'
$PictArray[403] = '94,23,95,23'
$PictArray[404] = '100,23,107,23'
$PictArray[405] = '116,23,121,23'
$PictArray[406] = '129,23,137,23'
$PictArray[407] = '141,23,143,23'
$PictArray[408] = '147,23,155,23'
$PictArray[409] = '164,23,170,23'
$PictArray[410] = '174,23,176,23'
$PictArray[411] = '180,23,189,23'
$PictArray[412] = '198,23,206,23'
$PictArray[413] = '210,23,211,23'
$PictArray[414] = '216,23,220,23'
$PictArray[415] = '1,24,6,24'
$PictArray[416] = '15,24,21,24'
$PictArray[417] = '26,24,28,24'
$PictArray[418] = '32,24,40,24'
$PictArray[419] = '48,24,55,24'
$PictArray[420] = '58,24,60,24'
$PictArray[421] = '64,24,74,24'
$PictArray[422] = '83,24,89,24'
$PictArray[423] = '94,24,95,24'
$PictArray[424] = '100,24,107,24'
$PictArray[425] = '115,24,121,24'
$PictArray[426] = '129,24,137,24'
$PictArray[427] = '141,24,143,24'
$PictArray[428] = '147,24,156,24'
$PictArray[429] = '163,24,170,24'
$PictArray[430] = '174,24,176,24'
$PictArray[431] = '180,24,189,24'
$PictArray[432] = '198,24,206,24'
$PictArray[433] = '210,24,212,24'
$PictArray[434] = '216,24,220,24'
$PictArray[435] = '1,25,6,25'
$PictArray[436] = '15,25,21,25'
$PictArray[437] = '26,25,28,25'
$PictArray[438] = '32,25,40,25'
$PictArray[439] = '47,25,55,25'
$PictArray[440] = '58,25,60,25'
$PictArray[441] = '64,25,74,25'
$PictArray[442] = '82,25,89,25'
$PictArray[443] = '94,25,96,25'
$PictArray[444] = '100,25,108,25'
$PictArray[445] = '115,25,121,25'
$PictArray[446] = '129,25,137,25'
$PictArray[447] = '141,25,144,25'
$PictArray[448] = '148,25,155,25'
$PictArray[449] = '163,25,170,25'
$PictArray[450] = '174,25,176,25'
$PictArray[451] = '180,25,189,25'
$PictArray[452] = '198,25,205,25'
$PictArray[453] = '210,25,212,25'
$PictArray[454] = '216,25,220,25'
$PictArray[455] = '1,26,6,26'
$PictArray[456] = '10,26,11,26'
$PictArray[457] = '15,26,22,26'
$PictArray[458] = '26,26,28,26'
$PictArray[459] = '32,26,40,26'
$PictArray[460] = '47,26,55,26'
$PictArray[461] = '58,26,61,26'
$PictArray[462] = '64,26,74,26'
$PictArray[463] = '83,26,89,26'
$PictArray[464] = '93,26,96,26'
$PictArray[465] = '100,26,108,26'
$PictArray[466] = '115,26,120,26'
$PictArray[467] = '125,26,125,26'
$PictArray[468] = '130,26,137,26'
$PictArray[469] = '141,26,144,26'
$PictArray[470] = '148,26,156,26'
$PictArray[471] = '163,26,170,26'
$PictArray[472] = '174,26,176,26'
$PictArray[473] = '180,26,189,26'
$PictArray[474] = '198,26,205,26'
$PictArray[475] = '210,26,212,26'
$PictArray[476] = '216,26,220,26'
$PictArray[477] = '1,27,6,27'
$PictArray[478] = '10,27,11,27'
$PictArray[479] = '15,27,21,27'
$PictArray[480] = '25,27,28,27'
$PictArray[481] = '33,27,40,27'
$PictArray[482] = '47,27,55,27'
$PictArray[483] = '58,27,61,27'
$PictArray[484] = '64,27,73,27'
$PictArray[485] = '78,27,78,27'
$PictArray[486] = '83,27,89,27'
$PictArray[487] = '93,27,96,27'
$PictArray[488] = '100,27,108,27'
$PictArray[489] = '115,27,120,27'
$PictArray[490] = '125,27,126,27'
$PictArray[491] = '130,27,137,27'
$PictArray[492] = '141,27,144,27'
$PictArray[493] = '148,27,156,27'
$PictArray[494] = '162,27,170,27'
$PictArray[495] = '174,27,176,27'
$PictArray[496] = '180,27,189,27'
$PictArray[497] = '193,27,193,27'
$PictArray[498] = '198,27,205,27'
$PictArray[499] = '210,27,212,27'
$PictArray[500] = '216,27,220,27'
$PictArray[501] = '1,28,5,28'
$PictArray[502] = '10,28,11,28'
$PictArray[503] = '15,28,21,28'
$PictArray[504] = '25,28,28,28'
$PictArray[505] = '33,28,40,28'
$PictArray[506] = '43,28,43,28'
$PictArray[507] = '47,28,55,28'
$PictArray[508] = '58,28,61,28'
$PictArray[509] = '65,28,73,28'
$PictArray[510] = '78,28,78,28'
$PictArray[511] = '83,28,89,28'
$PictArray[512] = '93,28,96,28'
$PictArray[513] = '100,28,108,28'
$PictArray[514] = '114,28,120,28'
$PictArray[515] = '124,28,126,28'
$PictArray[516] = '130,28,137,28'
$PictArray[517] = '141,28,144,28'
$PictArray[518] = '148,28,156,28'
$PictArray[519] = '162,28,170,28'
$PictArray[520] = '174,28,176,28'
$PictArray[521] = '180,28,189,28'
$PictArray[522] = '193,28,194,28'
$PictArray[523] = '198,28,205,28'
$PictArray[524] = '209,28,212,28'
$PictArray[525] = '217,28,220,28'
$PictArray[526] = '1,29,5,29'
$PictArray[527] = '10,29,11,29'
$PictArray[528] = '15,29,21,29'
$PictArray[529] = '25,29,28,29'
$PictArray[530] = '33,29,40,29'
$PictArray[531] = '43,29,43,29'
$PictArray[532] = '47,29,55,29'
$PictArray[533] = '58,29,61,29'
$PictArray[534] = '65,29,73,29'
$PictArray[535] = '77,29,78,29'
$PictArray[536] = '83,29,88,29'
$PictArray[537] = '93,29,96,29'
$PictArray[538] = '100,29,108,29'
$PictArray[539] = '114,29,120,29'
$PictArray[540] = '124,29,126,29'
$PictArray[541] = '130,29,136,29'
$PictArray[542] = '140,29,144,29'
$PictArray[543] = '149,29,156,29'
$PictArray[544] = '162,29,170,29'
$PictArray[545] = '174,29,176,29'
$PictArray[546] = '181,29,188,29'
$PictArray[547] = '193,29,194,29'
$PictArray[548] = '199,29,205,29'
$PictArray[549] = '209,29,212,29'
$PictArray[550] = '217,29,220,29'
$PictArray[551] = '1,30,4,30'
$PictArray[552] = '9,30,12,30'
$PictArray[553] = '16,30,21,30'
$PictArray[554] = '25,30,28,30'
$PictArray[555] = '33,30,40,30'
$PictArray[556] = '43,30,43,30'
$PictArray[557] = '47,30,55,30'
$PictArray[558] = '58,30,61,30'
$PictArray[559] = '65,30,73,30'
$PictArray[560] = '77,30,79,30'
$PictArray[561] = '83,30,88,30'
$PictArray[562] = '93,30,96,30'
$PictArray[563] = '100,30,108,30'
$PictArray[564] = '114,30,119,30'
$PictArray[565] = '123,30,126,30'
$PictArray[566] = '130,30,136,30'
$PictArray[567] = '140,30,144,30'
$PictArray[568] = '149,30,155,30'
$PictArray[569] = '159,30,159,30'
$PictArray[570] = '162,30,170,30'
$PictArray[571] = '174,30,177,30'
$PictArray[572] = '181,30,188,30'
$PictArray[573] = '193,30,194,30'
$PictArray[574] = '199,30,204,30'
$PictArray[575] = '209,30,212,30'
$PictArray[576] = '216,30,220,30'
$PictArray[577] = '1,31,4,31'
$PictArray[578] = '7,31,12,31'
$PictArray[579] = '16,31,20,31'
$PictArray[580] = '24,31,29,31'
$PictArray[581] = '33,31,39,31'
$PictArray[582] = '43,31,43,31'
$PictArray[583] = '47,31,54,31'
$PictArray[584] = '59,31,61,31'
$PictArray[585] = '66,31,72,31'
$PictArray[586] = '77,31,79,31'
$PictArray[587] = '83,31,88,31'
$PictArray[588] = '92,31,96,31'
$PictArray[589] = '101,31,107,31'
$PictArray[590] = '115,31,119,31'
$PictArray[591] = '121,31,126,31'
$PictArray[592] = '130,31,136,31'
$PictArray[593] = '139,31,145,31'
$PictArray[594] = '148,31,155,31'
$PictArray[595] = '158,31,159,31'
$PictArray[596] = '163,31,170,31'
$PictArray[597] = '174,31,177,31'
$PictArray[598] = '182,31,187,31'
$PictArray[599] = '191,31,195,31'
$PictArray[600] = '199,31,204,31'
$PictArray[601] = '208,31,212,31'
$PictArray[602] = '217,31,220,31'
$PictArray[603] = '1,32,13,32'
$PictArray[604] = '15,32,21,32'
$PictArray[605] = '23,32,38,32'
$PictArray[606] = '42,32,44,32'
$PictArray[607] = '47,32,54,32'
$PictArray[608] = '59,32,61,32'
$PictArray[609] = '67,32,71,32'
$PictArray[610] = '76,32,79,32'
$PictArray[611] = '83,32,87,32'
$PictArray[612] = '91,32,97,32'
$PictArray[613] = '100,32,106,32'
$PictArray[614] = '110,32,111,32'
$PictArray[615] = '115,32,154,32'
$PictArray[616] = '157,32,180,32'
$PictArray[617] = '182,32,195,32'
$PictArray[618] = '199,32,205,32'
$PictArray[619] = '207,32,213,32'
$PictArray[620] = '216,32,220,32'

#EndRegion picture array

    ; get the size of the active window
    $size = WinGetClientSize($window_handle)
    $Window_width = $size[0]
    $Window_height = $size[1] + 40 ; include height of title bar up to 30 dots
    ; First hide the window
    $aClassList = StringSplit(_WinGetClassListEx($window_handle), @LF)
    $aM_Mask = DllCall('gdi32.dll', 'long', 'CreateRectRgn', 'long', 0, 'long', 0, 'long', 0, 'long', 0)
    ; rectangle A - left side
    $aMask = DllCall('gdi32.dll', 'long', 'CreateRectRgn', 'long', 0, 'long', 0, 'long', $pos_x, 'long', $Window_height)
    DllCall('gdi32.dll', 'long', 'CombineRgn', 'long', $aM_Mask[0], 'long', $aMask[0], 'long', $aM_Mask[0], 'int', 2)
    ; rectangle B - Top
    $aMask = DllCall('gdi32.dll', 'long', 'CreateRectRgn', 'long', 0, 'long', 0, 'long', $Window_width, 'long', $pos_y)
    DllCall('gdi32.dll', 'long', 'CombineRgn', 'long', $aM_Mask[0], 'long', $aMask[0], 'long', $aM_Mask[0], 'int', 2)
    ; rectangle C - Right side
    $aMask = DllCall('gdi32.dll', 'long', 'CreateRectRgn', 'long', $pos_x  + $Image_Width , 'long', 0 , 'long', $Window_width + 30, 'long', $Window_height)
    DllCall('gdi32.dll', 'long', 'CombineRgn', 'long', $aM_Mask[0], 'long', $aMask[0], 'long', $aM_Mask[0], 'int', 2)
    ; rectangle D - Bottom
    $aMask = DllCall('gdi32.dll', 'long', 'CreateRectRgn', 'long', 0 , 'long', $pos_y + $Image_Height, 'long', $Window_width, 'long', $Window_height)
    DllCall('gdi32.dll', 'long', 'CombineRgn', 'long', $aM_Mask[0], 'long', $aMask[0], 'long', $aM_Mask[0], 'int', 2)
    ; now unhide all regions as defined  in array $PictArray
    For $i = 0 To (UBound($PictArray) - 1)
        $Block_value = StringSplit($PictArray[$i],',')
        $aMask = DllCall('gdi32.dll', 'long', 'CreateRectRgn', 'long', $pos_x + $Block_value[1] - 1 , 'long', $pos_y + $Block_value[2], 'long', $pos_x + $Block_value[3], 'long', $pos_y + $Block_value[4] -1)
        DllCall('gdi32.dll', 'long', 'CombineRgn', 'long', $aM_Mask[0], 'long', $aMask[0], 'long', $aM_Mask[0], 'int', 2)
    Next
    DllCall('user32.dll', 'long', 'SetWindowRgn', 'hwnd', $window_handle, 'long', $aM_Mask[0], 'int', 1)
    $PictArray='' ; empty array
EndFunc  ;==>_GuiImageHole
#FUNCTION# ==============================================================
Func _WinGetClassListEx($sTitle)
    Local $sClassList = WinGetClassList($sTitle)
    Local $aClassList = StringSplit($sClassList, @LF)
    Local $sRetClassList = '', $sHold_List = '|'
    Local $aiInHold, $iInHold
    For $i = 1 To UBound($aClassList) - 1
        If $aClassList[$i] = '' Then ContinueLoop
        If StringRegExp($sHold_List, '\|' & $aClassList[$i] & '~(\d+)\|') Then
            $aiInHold = StringRegExp($sHold_List, '.*\|' & $aClassList[$i] & '~(\d+)\|.*', 1)
            $iInHold = Number($aiInHold[UBound($aiInHold)-1])
            If $iInHold = 0 Then $iInHold += 1
            $aClassList[$i] &= '~' & $iInHold + 1
            $sHold_List &= $aClassList[$i] & '|'
            $sRetClassList &= $aClassList[$i] & @LF
        Else
            $aClassList[$i] &= '~1'
            $sHold_List &= $aClassList[$i] & '|'
            $sRetClassList &= $aClassList[$i] & @LF
        EndIf
    Next
    Return StringReplace(StringStripWS($sRetClassList, 3), '~', '')
EndFunc ;==>_WinGetClassListEx
#FUNCTION# ==============================================================
#EndRegion Perforated image

Oracle_SQL.zip

Edited January 2, 2009 by GreenCan

[ptrex]

@GreenCan

Undefined function on my side ?

_ExcelSheetNumUsedRowGet

Regards

ptrex

[GreenCan]

@GreenCan

Undefined function on my side ?

Regards

ptrex

Thanks ptrex.

Right, I added a function to ExcelCOM_UDF.au3. I should never have done this...

I corrected this and included the _ExcelSheetNumUsedRowGet in the main script. I loaded the latest script in my first message

Please try again

Thanks

GreenCan

[ptrex]

@GreenCan

Very nice done. Works like a charm.

But it's not (yet) a replacement for Business Objects Reporting.

Maybe you can use my Excel PivotTable example I posted yesterday as a reporting interface to Oracle.

Gives you drag and drop capabilities on all the fields for a multidimentional view on the data.

Regards

ptrex

[joebob]

I like your program, but am unable to get the correct results when querying the v$session view. It appears to return the first row once for every row in the table. I used the following query:

select

sid "sid"

, serial# "serial"

, username "username"

, command "command"

, status "status"

, schemaname "schema"

, osuser "OSUser"

, process "Process"

, machine "Machine"

, terminal "Terminal"

, program "Program"

, module "Module"

from v$session

Any suggestion would be appreciated

[ptrex]

@joebob

This is hard to debug from at distance.

Best is to run it from SQLPLus and check both results to start with.

regards

ptrex

[joebob]

Thanks, I did run it is SQL*Plus before writing you. It gives the correct result. I tried reducing the query as show below, but it still returns the data from the first row once for every row in the view

select

sid

, serial#

, username

from v$session

[ptrex]

@Joebob

Than best send a PM to the author the this tool -> GreenCan

Regards

ptrex

[GreenCan]

@joebob,

thanks for your message

Your views seem to work fine at my side. I tested it on my server and the SQL returned 76 rows with all unique SIDs. To be sure I ran the same SQL in Oracle SQL Plus and also in Aqua Data Studio, all gave the same results.

I don't know what could happen on your side. Maybe you can send me an example of what SQL Plus gave and the SQL Report Generator?

I need more specific information to be able to understand what is happening exactly.

Did you change anything in the code, specifically between line 217 and 258?

GreenCan