Jump to content

This site uses cookies. By continuing to browse the site you are agreeing to our use of cookies. Find out more here. X
X


Photo

Oracle SQL Report Generator


  • Please log in to reply
8 replies to this topic

#1 GreenCan

GreenCan

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 324 posts

Posted 02 January 2009 - 05:46 PM

and now in the correct forum... :whistle:
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)
AutoIt         
#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

Attached Files


Edited by GreenCan, 02 January 2009 - 06:59 PM.








#2 ptrex

ptrex

    Universalist

  • MVPs
  • 2,419 posts

Posted 02 January 2009 - 06:13 PM

@GreenCan

Undefined function on my side ?

_ExcelSheetNumUsedRowGet


Regards

ptrex

#3 GreenCan

GreenCan

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 324 posts

Posted 02 January 2009 - 07:03 PM

@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

#4 ptrex

ptrex

    Universalist

  • MVPs
  • 2,419 posts

Posted 02 January 2009 - 09:23 PM

@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

#5 joebob

joebob

    Seeker

  • Normal Members
  • 2 posts

Posted 08 January 2009 - 08:53 PM

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

#6 ptrex

ptrex

    Universalist

  • MVPs
  • 2,419 posts

Posted 09 January 2009 - 09:46 AM

@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

#7 joebob

joebob

    Seeker

  • Normal Members
  • 2 posts

Posted 09 January 2009 - 02:01 PM

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

#8 ptrex

ptrex

    Universalist

  • MVPs
  • 2,419 posts

Posted 09 January 2009 - 02:08 PM

@Joebob

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

Regards

ptrex

#9 GreenCan

GreenCan

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 324 posts

Posted 09 January 2009 - 04:04 PM

@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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users