Leaderboard
Popular Content
Showing content with the highest reputation on 11/16/2022 in all areas
-
Version v3.5
11,662 downloads
ImageSearch UDF v3.5 - AutoIt Wrapper for ImageSearchDLL Advanced image search library for AutoIt with cache system and SIMD optimization. Overview ImageSearchDLL UDF is a high-performance image search library for AutoIt that enables you to find images on screen or within other images. Built with C++14 and optimized with SIMD instructions (AVX512/AVX2/SSE2), it provides fast and accurate image matching capabilities. Features High Performance: SIMD optimization (AVX512/AVX2/SSE2) for fast searching Multi-Monitor Support: Full support for multi-monitor setups with negative coordinates DPI Awareness: Thread-local DPI awareness without affecting AutoIt GUI Cache System: Persistent cache for 30-50% speed boost on repeated searches Image Scaling: Search for images at different scales (0.1x to 5.0x) Screen Capture: Direct screen capture with DPI-aware coordinates Mouse Automation: Precise mouse movement and clicking with multi-monitor support Requirements AutoIt: Version 3.3.16.1 or higher Windows: XP SP3 to Windows 11 Architecture: x86 or x64 (automatic detection) DLL: ImageSearchDLL v3.5 (included) Installation Download the UDF package Place ImageSearchDLL_UDF.au3 in your script directory Ensure the appropriate DLL is in the same directory: ImageSearchDLL_x64.dll for 64-bit AutoIt ImageSearchDLL_x86.dll for 32-bit AutoIt Not required in embedded version! (But need to install Visual C++ Redistributable 2015-2022) Include the UDF in your script: #include "ImageSearchDLL_UDF.au3" Quick Start Basic Image Search #include "ImageSearchDLL_UDF.au3" ; Search for a button on screen Local $aResult = _ImageSearch("button.png") If $aResult[0] > 0 Then ConsoleWrite("Found at: " & $aResult[1][0] & ", " & $aResult[1][1] & @CRLF) MouseClick("left", $aResult[1][0], $aResult[1][1]) Else ConsoleWrite("Image not found" & @CRLF) EndIf Wait for Image and Click ; Wait up to 5 seconds for button to appear, then click it If _ImageSearch_WaitClick(5000, "button.png") Then MsgBox(0, "Success", "Button clicked!") Else MsgBox(0, "Failed", "Button not found within 5 seconds") EndIf Screen Capture ; Capture a region and save as PNG _ImageSearch_ScreenCapture_SaveImage(@ScriptDir & "\screenshot.png", 100, 100, 600, 400) ; Capture full screen _ImageSearch_ScreenCapture_SaveImage(@ScriptDir & "\fullscreen.png") API Reference Startup & Configuration _ImageSearch_Startup() Initializes the ImageSearch library by loading the appropriate DLL. Returns: Success: 1 (DLL loaded successfully) Failure: 0 and sets @error Remarks: Must be called before using any search functions Automatically called on script start DLL v3.5+ uses thread-local DPI awareness and won't affect AutoIt GUI _ImageSearch_Shutdown() Closes the DLL and cleans up resources. _ImageSearch_SetDllPath($sPath) Sets a custom DLL path (must be called before _ImageSearch_Startup). Parameters: $sPath - Full path to the DLL file Returns: Success: 1 Failure: 0 (file not found) Core Search Functions _ImageSearch($sImagePath [, $iLeft, $iTop, $iRight, $iBottom [, $iScreen [, $iTolerance [, $iResults [, $iCenterPOS [, $fMinScale [, $fMaxScale [, $fScaleStep [, $iReturnDebug [, $iUseCache]]]]]]]]) Searches for an image within a specified screen area. Parameters: $sImagePath - Image file path(s), multiple separated by "|" $iLeft, $iTop, $iRight, $iBottom - Search region (0 = entire screen) $iScreen - Monitor index (-1 = virtual screen, 0 = primary, 1+ = specific monitor) $iTolerance - Color tolerance 0-255 (default: 10) $iResults - Max results 1-1024 (default: 1) $iCenterPOS - Return center (1) or top-left (0) coordinates (default: 1) $fMinScale, $fMaxScale - Scale range 0.1-5.0 (default: 1.0) $fScaleStep - Scale step (default: 0.1) $iReturnDebug - Debug mode (default: 0) $iUseCache - Enable cache (default: 1) Returns: Success: Array of found positions [count][X, Y, Width, Height] Failure: Empty array with @error set Example: ; Search for multiple images with scaling Local $aResult = _ImageSearch("icon1.png|icon2.png", 0, 0, 800, 600, -1, 10, 5, 1, 0.8, 1.2, 0.1) If $aResult[0] > 0 Then For $i = 1 To $aResult[0] ConsoleWrite("Match " & $i & " at: " & $aResult[$i][0] & ", " & $aResult[$i][1] & @CRLF) Next EndIf _ImageSearch_InImage($sSourceImage, $sTargetImage [, $iTolerance [, $iResults [, $iCenterPOS [, $fMinScale [, $fMaxScale [, $fScaleStep [, $iReturnDebug [, $iUseCache]]]]]]]]) Searches for a target image within a source image (file-to-file search). Parameters: $sSourceImage - Path to source image file $sTargetImage - Path to target image file(s), multiple separated by "|" Other parameters same as _ImageSearch Returns: Same as _ImageSearch Remarks: Useful for pre-processing images or testing without screen capture Example: $aResult = _ImageSearch_InImage("screenshot.png", "button.png", 20) _ImageSearch_hBitmap($hBitmapSource, $hBitmapTarget [, $iTolerance [, $iLeft [, $iTop [, $iRight [, $iBottom [, $iResults [, $iCenterPOS [, $fMinScale [, $fMaxScale [, $fScaleStep [, $iReturnDebug [, $iUseCache]]]]]]]]]]]]) Searches for a target bitmap within a source bitmap (memory-to-memory search). Parameters: $hBitmapSource - Handle to source bitmap (HBITMAP) $hBitmapTarget - Handle to target bitmap (HBITMAP) Other parameters same as _ImageSearch Returns: Same as _ImageSearch Remarks: Fastest method for repeated searches (no disk I/O) Bitmaps must be created with GDI/GDI+ functions Screen Capture Functions _ImageSearch_CaptureScreen([$iLeft, $iTop, $iRight, $iBottom [, $iScreen]]) Capture screen region and return as HBITMAP handle. Parameters: $iLeft, $iTop, $iRight, $iBottom - Capture region (default: 0 = full screen) $iScreen - Monitor index (default: -1 = virtual screen) Returns: Success: HBITMAP handle (must DeleteObject when done) Failure: 0 and sets @error Example: $hBitmap = _ImageSearch_CaptureScreen(0, 0, 800, 600) ; ... use $hBitmap ... _WinAPI_DeleteObject($hBitmap) _ImageSearch_ScreenCapture_SaveImage($sImageFile [, $iLeft [, $iTop [, $iRight [, $iBottom [, $iScreen]]]]]) Captures a screen region and saves it directly to an image file in one call. Parameters: $sImageFile - Output file path (extension determines format: .bmp, .png, .jpg/.jpeg) $iLeft, $iTop, $iRight, $iBottom - Capture region (default: 0 = full screen) $iScreen - Monitor index (default: 0 = primary screen) Returns: Success: True (1) Failure: False (0) and sets @error Remarks: Automatically detects format from file extension ~2x faster than separate capture + save operations JPEG quality is fixed at 100% (highest quality) Uses DPI-aware capture (accurate on all DPI scales) Example: ; Capture full primary screen to PNG _ImageSearch_ScreenCapture_SaveImage(@ScriptDir & "\screenshot.png") ; Capture region on monitor 2 to JPEG _ImageSearch_ScreenCapture_SaveImage(@ScriptDir & "\region.jpg", 100, 100, 600, 400, 2) _ImageSearch_hBitmapLoad($sImageFile [, $iAlpha [, $iRed [, $iGreen [, $iBlue]]]]) Load image file and convert to HBITMAP handle. Parameters: $sImageFile - Path to image file $iAlpha, $iRed, $iGreen, $iBlue - Background color components 0-255 (default: 0 = transparent) Returns: Success: HBITMAP handle (must DeleteObject when done) Failure: 0 and sets @error Example: $hBitmap = _ImageSearch_hBitmapLoad("image.png", 255, 255, 255, 255) ; White background ; ... use $hBitmap ... _WinAPI_DeleteObject($hBitmap) Mouse Functions _ImageSearch_MouseMove($iX, $iY [, $iSpeed [, $iScreen]]) Moves mouse cursor to coordinates (supports negative coordinates on multi-monitor). Parameters: $iX, $iY - Target coordinates (-1 = keep current position) $iSpeed - Speed 0-100 (0=instant, default: 0) $iScreen - Monitor index (default: -1 = virtual screen) Returns: 1 on success, 0 on failure _ImageSearch_MouseClick([$sButton [, $iX [, $iY [, $iClicks [, $iSpeed [, $iScreen]]]]]]) Clicks mouse at coordinates (screen or current position). Parameters: $sButton - Button: "left", "right", "middle" (default: "left") $iX, $iY - Coordinates (-1 = current position) $iClicks - Number of clicks (default: 1) $iSpeed - Speed 0-100 (0=instant, default: 0) $iScreen - Monitor index (default: -1 = virtual screen) Returns: 1 on success, 0 on failure _ImageSearch_MouseClickWin($sTitle, $sText, $iX, $iY [, $sButton [, $iClicks [, $iSpeed]]]) Clicks mouse in a window. Parameters: $sTitle - Window title/class/handle $sText - Window text $iX, $iY - Relative coordinates in window $sButton - Button (default: "left") $iClicks - Number of clicks (default: 1) $iSpeed - Speed 0-100 (default: 0) Returns: 1 on success, 0 on failure Wait & Click Functions _ImageSearch_Wait($iTimeout, $sImagePath [, $iLeft [, $iTop [, $iRight [, $iBottom [, $iScreen [, $iTolerance [, $iResults [, $iCenterPOS [, $fMinScale [, $fMaxScale [, $fScaleStep [, $iReturnDebug [, $iUseCache [, $iMaxAttempts]]]]]]]]]]]]]) Waits for an image to appear on screen with timeout and optional max attempts limit. Parameters: $iTimeout - Timeout in milliseconds (0 = wait forever) $sImagePath - Image file path(s), multiple separated by "|" $iMaxAttempts - Max number of search attempts (0 = unlimited, default: 0) Other parameters same as _ImageSearch Returns: Success: 2D Array (same as _ImageSearch) Timeout: Empty array with [0][0] = 0 Example: ; Wait 5 seconds for button (unlimited attempts) $aResult = _ImageSearch_Wait(5000, "button.png") If $aResult[0] > 0 Then MouseClick("left", $aResult[1][0], $aResult[1][1]) Else MsgBox(0, "Timeout", "Button not found") EndIf _ImageSearch_WaitClick($iTimeout, $sImagePath [, $sButton [, $iClicks [, $iLeft [, $iTop [, $iRight [, $iBottom [, $iScreen [, $iTolerance [, $iResults [, $iCenterPOS [, $fMinScale [, $fMaxScale [, $fScaleStep [, $iReturnDebug [, $iUseCache]]]]]]]]]]]]]) Waits for an image and clicks it when found. Parameters: $iTimeout - Timeout in milliseconds (0 = wait forever) $sImagePath - Image file path(s) $sButton - Mouse button: "left", "right", "middle" (default: "left") $iClicks - Number of clicks (default: 1) Other parameters same as _ImageSearch Returns: Success: 1 (image found and clicked) Timeout: 0 (image not found) Monitor Functions _ImageSearch_Monitor_GetList() Gets a list of all connected display monitors and their properties. Returns: Success: The number of monitors found. @extended contains a detailed log. Failure: 0 and sets @error Remarks: Populates the global $g_aMonitorList Called automatically by _ImageSearch_Startup _ImageSearch_Monitor_ToVirtual($iMonitor, $iX, $iY) Converts local monitor coordinates to virtual screen coordinates. Parameters: $iMonitor - The 1-based index of the monitor $iX, $iY - Coordinates relative to the monitor's top-left corner Returns: Success: A 2-element array [$vX, $vY] containing virtual screen coordinates Failure: 0 and sets @error _ImageSearch_Monitor_FromVirtual($iMonitor, $iX, $iY) Converts virtual screen coordinates to local monitor coordinates. Parameters: $iMonitor - The 1-based index of the monitor $iX, $iY - Virtual screen coordinates Returns: Success: A 2-element array [$lX, $lY] containing local monitor coordinates Failure: 0 and sets @error _ImageSearch_Monitor_Current() Detects which monitor contains the current mouse cursor position. Returns: Success: Monitor index (1-based) where the cursor is located Failure: 0 and sets @error _ImageSearch_Monitor_GetAtPosition([$iX [, $iY]]) Returns detailed information string about the monitor at specified position. Parameters: $iX, $iY - Coordinates (default: -1 = use mouse cursor position) Returns: Success: String describing the monitor (e.g., "Monitor 2: 1920x1080 (Primary)") Failure: Error message string Window Coordinate Functions _ImageSearch_Window_ToScreen($hWnd, $iX, $iY [, $bClientArea]) Converts window-relative coordinates to screen (virtual desktop) coordinates. Parameters: $hWnd - Window handle or title $iX, $iY - Coordinates relative to window $bClientArea - True = relative to client area, False = relative to window (default: True) Returns: Success: A 2-element array [$screenX, $screenY] containing screen coordinates Failure: 0 and sets @error _ImageSearch_Window_FromScreen($hWnd, $iScreenX, $iScreenY [, $bClientArea]) Converts screen (virtual desktop) coordinates to window-relative coordinates. Parameters: $hWnd - Window handle or title $iScreenX, $iScreenY - Screen coordinates $bClientArea - True = relative to client area, False = relative to window (default: True) Returns: Success: A 2-element array [$winX, $winY] containing window-relative coordinates Failure: 0 and sets @error Cache & Info Functions _ImageSearch_WarmUpCache($sImagePaths [, $bEnableCache]) Pre-loads images into cache for faster subsequent searches. Parameters: $sImagePaths - Pipe-separated list of images to preload $bEnableCache - Enable persistent cache (default: True) Returns: Success: Number of images cached Failure: 0 Example: _ImageSearch_WarmUpCache("btn1.png|btn2.png|icon.png") _ImageSearch_ClearCache() Clears the internal bitmap and location cache. Remarks: Useful for freeing memory or forcing re-scan after image updates Clears both in-memory cache and persistent disk cache _ImageSearch_GetDllInfo([$bForceRefresh]) Gets comprehensive DLL information in INI format. Parameters: $bForceRefresh - Force refresh of cached info (default: True) Returns: Multi-line string in INI format with sections: [DLL] - DLL name, version, architecture, author [OS] - OS name, version, build, platform [CPU] - Threads, SSE2, AVX2, AVX512 support [SCREEN] - Virtual screen, scale, monitors with individual resolutions [CACHE] - Location cache, bitmap cache, pool size _ImageSearch_GetInfo() Gets formatted DLL and system information for display. Returns: Formatted string with DLL info, cache status, and screen information _ImageSearch_GetDllValue($sSection, $sKey) Quick accessor to read any value from cached DLL Info. Parameters: $sSection - Section name (DLL, OS, CPU, SCREEN, CACHE) $sKey - Key name Returns: Value string or "" if not found Example: $sVersion = _ImageSearch_GetDllValue("DLL", "Version") $sOSName = _ImageSearch_GetDllValue("OS", "Name") $iThreads = _ImageSearch_GetDllValue("CPU", "Threads") _ImageSearch_GetLastResult() Gets the raw DLL return string from the last search. Returns: Raw result string (e.g., "{2}[100|200|32|32,150|250|32|32]") Remarks: Useful for debugging or custom parsing _ImageSearch_GetScale([$iScreen]) Gets the DPI scale factor for a specific monitor as a decimal number. Parameters: $iScreen - Monitor index (0 = Primary, 1+ = specific monitor number) Returns: Scale factor as number (e.g., 1.0, 1.25, 1.5) or 0 if not found Example: $fScale = _ImageSearch_GetScale(0) ; Get primary monitor scale (e.g., 1.25) $fScale = _ImageSearch_GetScale(2) ; Get monitor 2 scale Examples Advanced Search with Multiple Images and Scaling #include "ImageSearchDLL_UDF.au3" ; Search for multiple UI elements with different scales Local $sImages = "button_ok.png|button_cancel.png|icon_settings.png" Local $aResult = _ImageSearch($sImages, 0, 0, 1920, 1080, -1, 15, 10, 1, 0.8, 1.3, 0.1, 0, 1) If $aResult[0] > 0 Then ConsoleWrite("Found " & $aResult[0] & " matches:" & @CRLF) For $i = 1 To $aResult[0] ConsoleWrite(" Match " & $i & ": X=" & $aResult[$i][0] & ", Y=" & $aResult[$i][1] & ", W=" & $aResult[$i][2] & ", H=" & $aResult[$i][3] & @CRLF) Next Else ConsoleWrite("No matches found" & @CRLF) EndIf Multi-Monitor Screen Capture #include "ImageSearchDLL_UDF.au3" ; Get monitor information _ImageSearch_Monitor_GetList() ConsoleWrite("Detected " & $g_aMonitorList[0][0] & " monitors" & @CRLF) ; Capture each monitor separately For $i = 1 To $g_aMonitorList[0][0] Local $sFile = @ScriptDir & "\monitor_" & $i & ".png" _ImageSearch_ScreenCapture_SaveImage($sFile, 0, 0, 0, 0, $i) ConsoleWrite("Captured monitor " & $i & " to: " & $sFile & @CRLF) Next ; Capture entire virtual desktop _ImageSearch_ScreenCapture_SaveImage(@ScriptDir & "\virtual_desktop.png", 0, 0, 0, 0, -1) Automated UI Testing #include "ImageSearchDLL_UDF.au3" ; Pre-load images for better performance _ImageSearch_WarmUpCache("login_button.png|username_field.png|password_field.png") ; Wait for login screen and interact If _ImageSearch_WaitClick(10000, "login_button.png") Then ConsoleWrite("Login button clicked" & @CRLF) ; Find username field and click Local $aUsername = _ImageSearch_Wait(5000, "username_field.png") If $aUsername[0] > 0 Then MouseClick("left", $aUsername[1][0], $aUsername[1][1]) Send("myusername") ; Find password field and click Local $aPassword = _ImageSearch_Wait(5000, "password_field.png") If $aPassword[0] > 0 Then MouseClick("left", $aPassword[1][0], $aPassword[1][1]) Send("mypassword") Send("{ENTER}") EndIf EndIf Else MsgBox(0, "Error", "Login screen not found within 10 seconds") EndIf Error Codes Code Constant Description -1 $IMGSE_INVALID_PATH Invalid file path -2 $IMGSE_FAILED_TO_LOAD_IMAGE Failed to load image -3 $IMGSE_FAILED_TO_GET_SCREEN_DC Failed to get screen device context -4 $IMGSE_INVALID_SEARCH_REGION Invalid search region -5 $IMGSE_INVALID_PARAMETERS Invalid parameters -6 $IMGSE_INVALID_SOURCE_BITMAP Invalid source bitmap -7 $IMGSE_INVALID_TARGET_BITMAP Invalid target bitmap -9 $IMGSE_RESULT_TOO_LARGE Result too large -10 $IMGSE_INVALID_MONITOR Invalid monitor Performance Tips Use Cache: Enable cache for repeated searches to get 30-50% speed boost Pre-load Images: Use _ImageSearch_WarmUpCache() during initialization Limit Search Area: Specify search regions instead of full screen when possible Optimize Tolerance: Use appropriate tolerance values (5-15 for most cases) Use Appropriate Scale Range: Limit scale range to what you actually need Monitor Selection: Use specific monitor index for faster searches on multi-monitor setups Image Format: BMP files load faster than PNG/JPG but are larger Memory Management: Always call _WinAPI_DeleteObject() for HBITMAP handles Changelog Version 3.5 Added thread-local DPI awareness (no GUI resize issues) Enhanced multi-monitor support with individual monitor scales Improved cache system with persistent disk cache Added _ImageSearch_ScreenCapture_SaveImage() for direct file saving Performance optimizations with SIMD instructions Better error handling and debugging information License This project is licensed under the MIT License - see the LICENSE file for details. Author Dao Van Trong - TRONG.PRO Thank you for using ImageSearch UDF! 🚀 ☕ Support My Work Enjoy my work? Buy me a 🍻 or tip via ❤️ PayPal Happy Automating! 🚀 ___________________________1 point -
Detect window until it closes in a loop, if it terminates then do
engagewithrage reacted to rudi for a topic
64 is the info symbol, one of a few values I know by heart. 30 is the timout value for the MsgBox() As @OJBakker mentioned already, pls have a look at the documentation for MsgBox() A very nice tool is the Message Box Code Wizzard, available by pressing [alt]+[w] from SciTE editor. There you can set a lot of things to design your message boxes. Many things are done through this numeric, fist value to be passed to the MsgBox() function. The wizzard will calculate the value as needed to match your selections. And finally a suggestion: MsgBox(64"blahblah"m30) is messed up, broken sample code. Pls. try to be precisely!1 point -
Excel file generation UDF (don't need Excel to be installed)
Abdelrahman reacted to jerome for a topic
Hi all, I'm back with an Excel file generation UDF that can manage multi-sheet workbook but without the need to have Excel installed on the computer... You can add more functions if you want but keep always my name in the post of greetings. ;---------------------------------------------------------------------------------------------- ; Copyright Jerome DERN 2010 ; --------------------------------------------------------------------------------------------- #Include <File.au3> ; Const for cell format Const $XMLXLS_STRING = "String" Const $XMLXLS_NUMBER = "Number" Const $XMLXLS_SDATE = "Short Date" Const $XMLXLS_PERCENT = "Percent" Const $XMLXLS_SCIENTIFIC= "Scientific" Const $XMLXLS_DATE = "[$-F400]h:mm:ss\ AM/PM" Const $XMLXLS_FRACTION = "#" "?/?" Const $XMLXLS_COMPTAB = "_-* #,##0.00\ "€"_-;\-* #,##0.00\ "€"_-;_-* "-"??\ "€"_-;_-@_-" ; Constants for cell alignment Const $XMLXLS_CENTER = "Center" Const $XMLXLS_RIGHT = "Right" Const $XMLXLS_LEFT = "Left" Const $XMLXLS_BOTTOM = "Bottom" Const $XMLXLS_TOP = "Top" Const $XMLXLS_JUSTIFIED = "Justify" ; Font style Global Enum Step *2 $XMLXLS_BOLD, $XMLXLS_ITALIC, $XMLXLS_STRIKETHROUGH, $XMLXLS_UNDERLINE, $XMLXLS_SUBSCRIPT, $XMLXLS_SUPERSCRIPT ; BitOr Values for border Global Enum Step *2 $XMLXLS_BDRIGHT, $XMLXLS_BDLEFT, $XMLXLS_BDTOP, $XMLXLS_BDBOTTOM, $XMLXLS_BDCROSS1, $XMLXLS_BDCROSS2 ; Page Set up Const $XMLXLS_LANDSCAPE = "Landscape" Const $XMLXLS_PORTRAIT = "Portrait" $demo1 = True $file = FileOpen("text.xls", 2) ; Create Workbook, active sheet will be se second one (2) _XLSCreateWorkbook($file, 2) ; Define styles _XLSCreateStyles($file) ; Define a new personal style _XLSAddStyle($file, "Header", "Arial", 11, $XMLXLS_BOLD, "000000", "", $XMLXLS_CENTER, $XMLXLS_CENTER, BitOR($XMLXLS_BDRIGHT, $XMLXLS_BDLEFT, $XMLXLS_BDTOP, $XMLXLS_BDBOTTOM)) _XLSAddStyle($file, "Table", "Arial", 11, 0, "000000", "", $XMLXLS_LEFT, $XMLXLS_CENTER, BitOR($XMLXLS_BDRIGHT, $XMLXLS_BDLEFT, $XMLXLS_BDTOP, $XMLXLS_BDBOTTOM)) ; Finish style definition _XLSCloseStyles($file) ; In demo1 case the sheets are generated from an array in memory ; limitation: named area can't be defined If $demo1 Then ; a typical excel sheet memory reservation (can be adjusted to real need) Dim $Sheet1[65536][255] Dim $Sheet2[65536][255] ; Create sheet 1 _XLSInitSheet($Sheet1, "Page 1") _XLSSetRowHeight($Sheet1, 1, 40) _XLSSetColumnWidth($Sheet1, 1, 100) _XLSSetColumnWidth($Sheet1, 2, 110) _XLSSetColumnWidth($Sheet1, 3, 120) _XLSSetCell($Sheet1, 1, 1, "Qty", "", "", "Header") _XLSSetCell($Sheet1, 1, 2, "Value", "", "", "Header") _XLSSetCell($Sheet1, 1, 3, "Total", "", "", "Header") _XLSSetCell($Sheet1, 2, 1, 2, "", $XMLXLS_NUMBER, "Table", "qty") _XLSSetCell($Sheet1, 2, 2, 4, "", $XMLXLS_NUMBER, "Table", "value") ;_XLSSetCell($Sheet1, 2, 3, 0, "RC[-2]*RC[-1]", $XMLXLS_NUMBER, "Table") _XLSSetCell($Sheet1, 2, 3, 0, "qty*value", $XMLXLS_NUMBER, "Table") _XLSGenerateFromArray($file, $Sheet1, $XMLXLS_LANDSCAPE) ; Create sheet 2 _XLSInitSheet($Sheet2, "Page 2") _XLSSetCell($Sheet2, 1, 1, "Name", "", "", "Header") _XLSSetCell($Sheet2, 1, 2, "Value", "", "", "Header") _XLSSetCell($Sheet2, 2, 1, "Beer", "", $XMLXLS_STRING, "Table") _XLSSetCell($Sheet2, 2, 2, 4.0, "", $XMLXLS_NUMBER, "Table") _XLSGenerateFromArray($file, $Sheet2) Else ; in this demo, sheets are generated from direct commands to library ; Create a new worksheet _XLSAddWorkSheet($file, "Sheet1") ; Define specific names of this sheet (optional) _XLSCreateWSNames($file) ; Print area is a specific name for printing zone _XLSAddWSName($file, "Print_Area", "Sheet1!R1C1:R65535C255") ; Finish definition of names _XLSCloseWSNames($file) ; Start Worksheet content _XLSStartWSDefinition($file) ; Create a row _XLSAddRowInWorkSheet($file) ; Add data to first row, column 1 _XLSAddCellInRow($file, "C1", "", $XMLXLS_STRING, "Table") ; Add data to first row, column 2 _XLSAddCellInRow($file, "C2", "", $XMLXLS_STRING) ; Finish row definition _XLSCloseRow($file) ; Create a new row (2nd one) _XLSAddRowInWorkSheet($file) ; Add data to second row, column 1 _XLSAddCellInRow($file, "1", "", $XMLXLS_NUMBER) ; Add data to second row, column 2 _XLSAddCellInRow($file, "2", "RC[-1]*2", $XMLXLS_NUMBER) ; Finish row definition _XLSCloseRow($file) ; Close Worksheet content _XLSCloseWorkSheet($file, $XMLXLS_PORTRAIT, 1, 1, 1, 1, 0.5, 0.5) ; Add a second Worksheet _XLSAddWorkSheet($file, "Sheet2") _XLSStartWSDefinition($file) ; Create first row definition _XLSAddRowInWorkSheet($file) ; Add data to first row, column 1 _XLSAddCellInRow($file, "CC1", "", $XMLXLS_STRING) ; Add data to first row, column 2 _XLSAddCellInRow($file, "CC2", "", $XMLXLS_STRING) ; Finish row definition _XLSCloseRow($file) ; Close Worksheet content _XLSCloseWorkSheet($file) EndIf ; Close Worksbook content, file is ready to use _XLSCloseWorkBook($file) ShellExecute("text.xls") ; ------------------------------------------------------------------------------------------------------------------------------------- ; ------------------------------------------------------- ARRAY TO XML PRIMITIVES ----------------------------------------------------- ; ------------------------------------------------------------------------------------------------------------------------------------- ; ----------------------------------------------------------------------------------- ; Initialize sheet : Set sheet name and set active size to 1 cell ; ----------------------------------------------------------------------------------- Func _XLSInitSheet(ByRef $array, $name) ; $array[0][0] store the name of the sheet;the maximum row number used;the maximum column number used $array[0][0] = $name & ";1;1" EndFunc ; ----------------------------------------------------------------------------------- ; Set row height ; ----------------------------------------------------------------------------------- Func _XLSSetRowHeight(ByRef $array, $row, $height) ; array[x][0] store the height of each row $array[$row][0] = $height EndFunc Func _XLSSetColumnWidth(ByRef $array, $col, $width) ; array[0][x] store the width of each column $array[0][$col] = $width EndFunc ; ------------------------------------------------------------------------------------ ; This format a cell in case of an array usage + generation with _XLSGenerateFromArray function ; ------------------------------------------------------------------------------------ Func _XLSSetCell(ByRef $array, $row, $col, $data, $formulae, $type=$XMLXLS_STRING, $style="Default", $name="") Local $decode If $formulae<>"" Then $data="" if $type = "" Then $type = $XMLXLS_STRING If $style = "" Then $style = "Default" $array[$row][$col] = $data & @CR & $formulae & @CR & $type & @CR & $style & @CR & $name ; check if max of row and column are to be updated $decode = StringSplit($array[0][0], ";") If $decode[0]<3 Then Return If $row>$decode[2] Then $decode[2]=$row If $col>$decode[3] Then $decode[3]=$col $array[0][0]=$decode[1]&";"&$decode[2]&";"&$decode[3] EndFunc ; ---------------------------------------------------------------------------------- ; Generate a sheet from a 2D array. array[0][0] has the sheet name, other array[row][col] ; are strings formatted using _XLSBuildCell function ; ---------------------------------------------------------------------------------- Func _XLSGenerateFromArray($file, ByRef $array, $orientation=$XMLXLS_PORTRAIT, $mbottom = 0.98, $mleft=0.78, $mright=0.78, $mtop=0.98, $hmargin=0.5, $fmargin=0.5) Local $col, $row, $sname, $decode, $data, $formula, $type, $style, $name, $height ; get array real size $decode = StringSplit($array[0][0], ";") If $decode[0] < 3 Then Return $sname = $decode[1] $maxrow = $decode[2] $maxcol = $decode[3] ; Create a new worksheet _XLSAddWorkSheet($file, $sname) ; Define specific names of this sheet (optional) _XLSCreateWSNames($file) ; Print area is a specific name for printing zone _XLSAddWSName($file, "Print_Area", "'"&$sname&"'" & "!R1C1:R"&$maxrow&"C"&$maxcol) ; Small patch in order to Manage here named cells Local $temp = $file, $nbnames=0 Dim $cellnames[1000][2] $file =_TempFile() ; Finish definition of names _XLSCloseWSNames($file) ; Start Worksheet content _XLSStartWSDefinition($file) ; Write column size if needed For $col=1 to $maxcol If $array[0][$col] <> "" Then _XLSColumnSize($file, $col, $array[0][$col]) Next ; generate sheet content For $row=1 to $maxrow $height = -1 If $array[$row][0] <> "" Then $height = $array[$row][0] ; Create a row _XLSAddRowInWorkSheet($file, $height) For $col=1 to $maxcol $data = "" $formula = "" $type = $XMLXLS_STRING $style = "Default" $name = "" If $array[$row][$col]<>"" Then $e = StringSplit($array[$row][$col], @CR) If $e[0]=5 Then $data = $e[1] $formula = $e[2] $type = $e[3] $style = $e[4] $name = $e[5] ;, named cell not yet implemented If $name<>"" Then $cellnames[$nbnames][0] = $name $cellnames[$nbnames][1] = "'"&$sname&"'"&"!R"&$row&"C"&$col $nbnames+=1 EndIf EndIf EndIf If $type="" Then $type = $XMLXLS_STRING If $style="" Then $style = "Default" ; Add data to first row, column 1 _XLSAddCellInRow($file, $data, $formula, $type, $style) Next ; Finish row definition _XLSCloseRow($file) Next ; Add named cell found For $col=1 to $nbnames _XLSAddWSName($temp, $cellnames[$col-1][0], $cellnames[$col-1][1]) Next ; merge files Dim $aRecords _FileReadToArray($file,$aRecords) FileDelete($file) $file = $temp For $col = 1 to $aRecords[0] FileWriteLine($file, $aRecords[$col]) Next ; Close Worksheet content _XLSCloseWorkSheet($file, $orientation, $mbottom, $mleft, $mright, $mtop, $hmargin, $fmargin) EndFunc ; ------------------------------------------------------------------------------------------------------------------------------------- ; ------------------------------------------------------- XML BASE PRIMITIVES --------------------------------------------------------- ; ------------------------------------------------------------------------------------------------------------------------------------- ; -------------------------------------------------------------------------------------------------- ; Create (Define) a new Excel workbook ; -------------------------------------------------------------------------------------------------- Func _XLSCreateWorkbook($file, $activesheet=1) If IsString($file) Then FileDelete($file) $activesheet -= 1 FileWriteLine($file, '<?xml version="1.0"?>') FileWriteLine($file, '<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">') FileWriteLine($file, '<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"><Author>XLSXMLGenerator</Author><Created>'&@YEAR&'-'&@MON&'-'&@MDAY&'T'&@HOUR&':'&@MIN&':'&@SEC&'</Created><Company>XLSXML</Company></DocumentProperties>') FileWriteLine($file, '<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"><ActiveSheet>'&$activesheet&'</ActiveSheet></ExcelWorkbook>') EndFunc ; -------------------------------------------------------------------------------------------------- ; Create (Define) a Style section ; -------------------------------------------------------------------------------------------------- Func _XLSCreateStyles($file) FileWriteLine($file, ' <Styles>') FileWriteLine($file, ' <Style ss:ID="Default" ss:Name="Normal"><Alignment ss:Vertical="Bottom"/><Borders/><Font ss:Size="11"/></Style>') EndFunc ; -------------------------------------------------------------------------------------------------- ; Create (Define) a new style. Borders must be constructed with BitOR() of desired border attribute ; -------------------------------------------------------------------------------------------------- Func _XLSAddStyle($file, $name="S21", $font="Arial", $size=11, $fstyle=0, $pcolor="000000", $icolor="", $AlignHor=$XMLXLS_CENTER, $AlignVer=$XMLXLS_BOTTOM, $Border=0) Local $f, $fontfam="Swiss" FileWriteLine($file, ' <Style ss:ID="' & $name &'">') FileWriteLine($file, ' <Alignment ss:Horizontal="' & $AlignHor & '" ss:Vertical="' & $AlignVer &'"/>') ;$Border = BitOR($XMLXLS_BDRIGHT, $XMLXLS_BDLEFT, $XMLXLS_BDTOP, $XMLXLS_BDBOTTOM, $XMLXLS_BDCROSS1, $XMLXLS_BDCROSS2) If $Border>0 Then FileWriteLine($file, ' <Borders>') If BitAnd($Border, $XMLXLS_BDBOTTOM)>0 Then FileWriteLine($file, ' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>') If BitAnd($Border, $XMLXLS_BDLEFT)>0 Then FileWriteLine($file, ' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>') If BitAnd($Border, $XMLXLS_BDRIGHT)>0 Then FileWriteLine($file, ' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>') If BitAnd($Border, $XMLXLS_BDTOP)>0 Then FileWriteLine($file, ' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>') If BitAnd($Border, $XMLXLS_BDCROSS1)>0 Then FileWriteLine($file, ' <Border ss:Position="DiagonalLeft" ss:LineStyle="Continuous" ss:Weight="1"/>') If BitAnd($Border, $XMLXLS_BDCROSS2)>0 Then FileWriteLine($file, ' <Border ss:Position="DiagonalRight" ss:LineStyle="Continuous" ss:Weight="1"/>') If $Border>0 Then FileWriteLine($file, ' </Borders>') $f = ' <Font ss:FontName="' & $font & '" x:Family="' & $fontfam & '" ss:Size="' & $size & '" ' ;$fstyle = BitOR($XMLXLS_BOLD, $XMLXLS_ITALIC, $XMLXLS_STRIKETHROUGH, $XMLXLS_UNDERLINE, $XMLXLS_SUBSCRIPT) If BitAnd($fstyle, $XMLXLS_BOLD)>0 Then $f &= 'ss:Bold="1" ' If BitAnd($fstyle, $XMLXLS_ITALIC)>0 Then $f &= 'ss:Italic="1" ' If BitAnd($fstyle, $XMLXLS_STRIKETHROUGH)>0 Then $f &= 'ss:StrikeThrough="1" ' If BitAnd($fstyle, $XMLXLS_UNDERLINE)>0 Then $f &= 'ss:Underline="Single" ' If BitAnd($fstyle, $XMLXLS_SUBSCRIPT)>0 Then $f &= 'ss:VerticalAlign="Subscript" ' If BitAnd($fstyle, $XMLXLS_SUPERSCRIPT)>0 Then $f &= 'ss:VerticalAlign="Superscript" ' If $pcolor <>"" Then $f &= 'ss:Color="#'&$pcolor&'" ' $f &= '/>' If $icolor<>"" Then FileWriteLine($file, '<Interior ss:Color="#'&$icolor&'" ss:Pattern="Solid"/>') FileWriteLine($file, $f) FileWriteLine($file, ' </Style>') EndFunc ; -------------------------------------------------------------------------------------------------- ; Close styles definitions ; -------------------------------------------------------------------------------------------------- Func _XLSCloseStyles($file) FileWriteLine($file, ' </Styles>') EndFunc ; -------------------------------------------------------------------------------------------------- ; Create (Define) a new Excel worksheet ; -------------------------------------------------------------------------------------------------- Func _XLSAddWorkSheet($file, $sheet) FileWriteLine($file, ' <Worksheet ss:Name="' & $sheet & '">') EndFunc ; -------------------------------------------------------------------------------------------------- ; Create (Define) an range named section ; -------------------------------------------------------------------------------------------------- Func _XLSCreateWSNames($file) FileWriteLine($file, ' <Names>') EndFunc ; -------------------------------------------------------------------------------------------------- ; Create (Define) a new range name ; -------------------------------------------------------------------------------------------------- Func _XLSAddWSName($file, $name="Print_Area", $location="'Sheet1'!R1C1:R65535C255") FileWriteLine($file, ' <NamedRange ss:Name="' & $Name & '" ss:RefersTo="=' & $location & '"/>') ; Ex: <NamedRange ss:Name="qty" ss:RefersTo="='Page 1'!R2C1"/> ; Ex: <NamedRange ss:Name="value" ss:RefersTo="='Page 1'!R2C2"/> EndFunc ; -------------------------------------------------------------------------------------------------- ; Close Worksheet named range section ; -------------------------------------------------------------------------------------------------- Func _XLSCloseWSNames($file) FileWriteLine($file, ' </Names>') EndFunc ; -------------------------------------------------------------------------------------------------- ; Begin WorkSheet definition ; -------------------------------------------------------------------------------------------------- Func _XLSStartWSDefinition($file) FileWriteLine($file, ' <Table x:FullColumns="1" x:FullRows="1">') EndFunc ; -------------------------------------------------------------------------------------------------- ; Define column size ; -------------------------------------------------------------------------------------------------- Func _XLSColumnSize($file, $col, $size) FileWriteLine($file, ' <Column ss:Index="'&$col&'" ss:AutoFitWidth="0" ss:Width="'&$size&'"/>') EndFunc ; -------------------------------------------------------------------------------------------------- ; Add a new row in current worksheet ; -------------------------------------------------------------------------------------------------- Func _XLSAddRowInWorkSheet($file, $height=-1) If $height = -1 Then FileWriteLine($file, ' <Row ss:AutoFitHeight="1">') Else FileWriteLine($file, ' <Row ss:AutoFitHeight="0" ss:Height="'& $height &'">') EndIf EndFunc ; -------------------------------------------------------------------------------------------------- ; Add a cell (column) in the cirrent row of the current worksheet ; -------------------------------------------------------------------------------------------------- Func _XLSAddCellInRow($file, $data, $formula="", $type=$XMLXLS_STRING, $style="Default") Local $Cell =' <Cell ' If $formula <> "" Then $Cell &= 'ss:Formula="=' & $formula & '" ' ; formulae example: RC[-1]*2 or R1C1*2 ; formulae example: Sheet2!R[1]C*2 FileWriteLine($file, $Cell & 'ss:StyleID="' & $style & '"><Data ss:Type="' & $type & '">' & $data & '</Data></Cell>') EndFunc ; -------------------------------------------------------------------------------------------------- ; Close current row definition ; -------------------------------------------------------------------------------------------------- Func _XLSCloseRow($file) FileWriteLine($file, ' </Row>') EndFunc ; -------------------------------------------------------------------------------------------------- ; Close current worksheet definition ; -------------------------------------------------------------------------------------------------- Func _XLSCloseWorkSheet($file, $orientation=$XMLXLS_PORTRAIT, $mbottom = 0.98, $mleft=0.78, $mright=0.78, $mtop=0.98, $hmargin=0.5, $fmargin=0.5) FileWriteLine($file, ' </Table>') FileWriteLine($file, ' <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">') FileWriteLine($file, ' <PageSetup>') FileWriteLine($file, ' <Layout x:Orientation="'&$orientation&'"/>') FileWriteLine($file, ' <Header x:Margin="'&$hmargin&'"/>') FileWriteLine($file, ' <Footer x:Margin="'&$fmargin&'"/>') FileWriteLine($file, ' <PageMargins x:Bottom="'&$mbottom&'" x:Left="'&$mleft&'" x:Right="'&$mright&'" x:Top="'&$mtop&'"/>') FileWriteLine($file, ' </PageSetup>') FileWriteLine($file, ' </WorksheetOptions>') FileWriteLine($file, ' <ss:ActiveSheet>Page 2</ss:ActiveSheet>') FileWriteLine($file, ' </Worksheet>') EndFunc ; -------------------------------------------------------------------------------------------------- ; Close Excel workbook, the file now is readable by excel ; -------------------------------------------------------------------------------------------------- Func _XLSCloseWorkBook($file) FileWriteLine($file, '</Workbook>') If Not IsString($file) Then FileClose($file) EndFunc ; ------------------------------------------------------------------------------- ; Create a relative (to $l,$c) formulae to access to $l2,$c2 in sheet $sheet ; ------------------------------------------------------------------------------- Func _XLSlc2rc($l1, $c1, $l2, $C2, $sheet) Local $dc, $dl, $f $dc = $c2-$c1 $dl = $l2-$l1 $f="R" if $sheet <> "" Then $f = $sheet & "!" & $f if $dl<>0 Then $f &= '[' & $dl & ']' $f &= "C" if $dc<>0 Then $f &= '[' & $dc & ']' Return $f EndFunc1 point -
Excel file generation UDF (don't need Excel to be installed)
Abdelrahman reacted to MrCreatoR for a topic
Ok here is the fix for my problem above: Replace this in the udf: Func _XLSAddStyle($file, $name="S21", $font="Arial", $size=11, $fstyle=0, $pcolor="000000", $icolor="", $AlignHor=$XMLXLS_CENTER, $AlignVer=$XMLXLS_BOTTOM, $Border=0) Local $f, $fontfam="Swiss" FileWriteLine($file, ' <Style ss:ID="' & $name &'">') FileWriteLine($file, ' <Alignment ss:Horizontal="' & $AlignHor & '" ss:Vertical="' & $AlignVer &'"/>') With this: Func _XLSAddStyle($file, $name="S21", $font="Arial", $size=11, $fstyle=0, $pcolor="000000", $icolor="", $AlignHor=$XMLXLS_CENTER, $AlignVer=$XMLXLS_BOTTOM, $Border=0, $WrapText=1) Local $f, $fontfam="Swiss" FileWriteLine($file, ' <Style ss:ID="' & $name &'">') FileWriteLine($file, ' <Alignment ss:Horizontal="' & $AlignHor & '" ss:Vertical="' & $AlignVer &'" ss:WrapText="' & $WrapText & '"/>')1 point -
Excel file generation UDF (don't need Excel to be installed)
Abdelrahman reacted to taietel for a topic
jerome, nice work! I have added a line in your script, Func _XLSCreateWorkbook($file, $activesheet=1) If IsString($file) Then FileDelete($file) $activesheet -= 1 FileWriteLine($file, '<?xml version="1.0"?>') FileWriteLine($file, '<?mso-application progid="Excel.Sheet"?>') ;<<<<<<<<<<<< this line FileWriteLine($file, '<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">') FileWriteLine($file, '<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"><Author>XLSXMLGenerator</Author><Created>'&@YEAR&'-'&@MON&'-'&@MDAY&'T'&@HOUR&':'&@MIN&':'&@SEC&'</Created><Company>XLSXML</Company></DocumentProperties>') FileWriteLine($file, '<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"><ActiveSheet>'&$activesheet&'</ActiveSheet></ExcelWorkbook>') EndFunc to avoid the message that appears when using Office2007/2010. Thanks for sharing. M.I.1 point