Jump to content

Leaderboard

Popular Content

Showing content with the highest reputation on 11/16/2022 in all areas

  1. Trong

    ImageSearchUDF

    Danyfirex reacted to Trong for a file

    More information about "ImageSearchUDF"

    Version 2025.5.25.5

    11,487 downloads

    High-Performance ImageSearch UDF & DLL for AutoIt This project provides a highly optimized UDF (User Defined Function) and two versions of a DLL (Dynamic-Link Library) for fast and flexible image searching on the screen using AutoIt. It serves as a powerful replacement for standard image search functions, delivering superior speed, especially on modern CPUs, by leveraging advanced SIMD instructions. ✨ Key Features Superior Speed: The modern version utilizes the AVX2 instruction set to accelerate the search speed by several factors compared to traditional methods. Two DLL Versions: Provides both a modern version (optimized for speed) and a legacy version (for Windows XP support). Multi-Image Search: Search for multiple image files in a single function call by separating paths with a pipe (|). Multi-Scale Searching: Automatically search for an image across a range of sizes (e.g., from 80% to 120% of its original size). Color Tolerance: Find images even with slight color variations by setting a tolerance value (0-255). Transparent Color Support: Specify a color in the source image to be ignored during the search. Flexible Result Handling: Find and return the first match. Find and return all matches on the screen. Limit the maximum number of results. Smart (Hybrid) DLL Loading: The UDF prioritizes an external DLL for maximum performance and automatically falls back to an embedded DLL to ensure the script always runs. Unicode Support: Works flawlessly with file paths containing Unicode characters. Thread-Safe: The DLL is designed to operate stably in multi-threaded scenarios. Debug Information: Provides an option to return a detailed debug string for easy troubleshooting. 🚀 The Two DLL Versions The project offers two DLL versions to meet different needs: 1. ImageSearch_x86.dll ImageSearch_x64.dll (Modern Version) (Attached in the same UDF folder - Because the DLL file with AVX2 support is large in size) This is the recommended version for most users. Strengths: AVX2 Support: Leverages Advanced Vector Extensions 2 on modern CPUs to process multiple pixels in parallel, resulting in extremely fast search speeds. Built with modern C++, ensuring stability and efficiency. Limitations: Not compatible with Windows XP. When to use: When you need maximum performance on Windows 7, 8, 10, 11, and newer. 2. ImageSearch_XP.dll (Legacy Version) (Embedded in UDF code) This version is created for backward compatibility. Strengths: Windows XP Compatibility: Works well on the Windows XP (SP3) operating system. Limitations: No AVX2 Support: Search speed will be significantly slower than the modern version on AVX2-supported CPUs. When to use: When your script must run in a Windows XP environment. ⚙️ How the UDF Works The ImageSearch_UDF.au3 file uses a very smart "hybrid" DLL loading mechanism: Prioritize External DLL: When the _ImageSearch function is called, the UDF first looks for ImageSearch_x86.dll and ImageSearch_x64.dll in the same directory as the script (@ScriptDir). If found, it uses this file to achieve the best performance (with AVX2 if available). Fallback to Embedded DLL: If the external DLL is not found, the UDF will automatically extract and use a legacy (non-AVX2) compatible DLL version that is embedded within it as a hex string. ➡️ This ensures that your script can always run, even if you forget to copy the DLL file. However, for the highest speed, always place the modern ImageSearch_x86.dll and ImageSearch_x64.dll next to your script. 📦 Setup Place the DLL file: Copy ImageSearch_x86.dll and ImageSearch_x64.dll (the modern version) into the same directory as your AutoIt script file. Include the UDF in your script: Use the line #include <ImageSearch_UDF.au3> in your script. 📖 API Reference The main function for performing an image search. _ImageSearch($sImageFile, [$iLeft = 0], [$iTop = 0], [$iRight = 0], [$iBottom = 0], [$iTolerance = 10], [$iTransparent = 0xFFFFFFFF], [$iMultiResults = 0], [$iCenterPOS = 1], [$iReturnDebug = 0], [$fMinScale = 1.0], [$fMaxScale = 1.0], [$fScaleStep = 0.1], [$iFindAllOccurrences = 0]) Parameters Parameter Type Default Description $sImageFile String - Path to the image file. To search for multiple images, separate paths with a pipe (` $iLeft Int 0 The left coordinate of the search area. 0 defaults to the entire screen. $iTop Int 0 The top coordinate of the search area. 0 defaults to the entire screen. $iRight Int 0 The right coordinate of the search area. 0 defaults to the entire screen. $iBottom Int 0 The bottom coordinate of the search area. 0 defaults to the entire screen. $iTolerance Int 10 Color tolerance (0-255). A higher value allows for greater color variation. $iTransparent Int 0xFFFFFFFF The color (in 0xRRGGBB format) to be ignored in the source image. 0xFFFFFFFF means no transparency. $iMultiResults Int 0 The maximum number of results to return. 0 means no limit. $iCenterPOS Bool 1 (True) If True, the returned X/Y coordinates will be the center of the found image. If False, they will be the top-left corner. $iReturnDebug Bool 0 (False) If True, the function returns a debug string instead of the results array. $fMinScale Float 1.0 The minimum scaling factor for the search (e.g., 0.8 for 80%). Must be >= 0.1. $fMaxScale Float 1.0 The maximum scaling factor for the search (e.g., 1.2 for 120%). $fScaleStep Float 0.1 The increment to use when searching between min and max scales. Must be >= 0.01. $iFindAllOccurrences Bool 0 (False) If False, the search stops after the first match. If True, it finds all possible matches. Return Value On Success: Returns a 2D array containing the coordinates of the found images. $aResult[0][0] = The number of matches found. $aResult[1] to $aResult[$aResult[0][0]] = An array for each match. $aResult[$i][0] = X coordinate $aResult[$i][1] = Y coordinate $aResult[$i][2] = Width of the found image $aResult[$i][3] = Height of the found image On Failure / No Match: Sets @error to 1 and returns 0. In Debug Mode: If $iReturnDebug is True, returns a string containing detailed information about the last search operation. 💻 Examples Example 1: Basic Search Find the first occurrence of button.png on the screen. #include <ImageSearch_UDF.au3> Local $aResult = _ImageSearch("C:\images\button.png") If @error Then MsgBox(48, "Error", "Image not found on screen.") Else Local $iCount = $aResult[0][0] Local $iX = $aResult[1][0] Local $iY = $aResult[1][1] MsgBox(64, "Success", "Found " & $iCount & " image(s). First match is at: " & $iX & ", " & $iY) MouseMove($iX, $iY, 20) ; Move mouse to the center of the found image EndIf Example 2: Advanced Search (Multiple Images, Tolerance, Scaling) Search for icon1.png or icon2.png within a specific region, with a tolerance of 20 and scaling from 90% to 110%. Find all occurrences. #include <ImageSearch_UDF.au3> Local $sImages = "icon1.png|icon2.png" Local $iTolerance = 20 Local $fMinScale = 0.9 Local $fMaxScale = 1.1 Local $fStep = 0.05 Local $aResult = _ImageSearch($sImages, 500, 300, 1200, 800, $iTolerance, 0xFFFFFFFF, 0, True, False, $fMinScale, $fMaxScale, $fStep, True) If @error Then MsgBox(48, "Error", "No matching images found in the specified region.") Else Local $iCount = $aResult[0][0] ConsoleWrite("Found " & $iCount & " total matches." & @CRLF) For $i = 1 To $iCount ConsoleWrite("Match #" & $i & ": X=" & $aResult[$i][0] & ", Y=" & $aResult[$i][1] & ", W=" & $aResult[$i][2] & ", H=" & $aResult[$i][3] & @CRLF) Next EndIf Example 3: Using Debug Mode To diagnose issues, use the $iReturnDebug parameter. #include <ImageSearch_UDF.au3> Local $2dDLLResult = _ImageSearch("image_not_exist.png", 0, 0, 0, 0, 10, 0xFFFFFFFF, 0, True, True) ConsoleWrite(">> DLL Return: " & $g_sLastDllReturn & @CRLF) ; Example output: {0}[No Match Found] | DEBUG: File=image_not_exist.png, Rect=(0,0,1920,1080), Tol=10, Trans=0xffffffff, Multi=0, Center=1, FindAll=0, AVX2=true, Scale=(1.00,1.00,0.10) SPECIAL NOTE: The function always returns a 2D array for both results and errors. Credits Author: Dao Van Trong - TRONG.PRO Source Dll on GitHub: daovantrong/ImageSearchDLL: A DLL for finding an image on the screen
    1 point
  2. rudi
    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
  3. jerome
    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 = "#&quot; &quot;?/?" Const $XMLXLS_COMPTAB = "_-* #,##0.00\ &quot;€&quot;_-;\-* #,##0.00\ &quot;€&quot;_-;_-* &quot;-&quot;??\ &quot;€&quot;_-;_-@_-" ; 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 EndFunc
    1 point
  4. MrCreatoR
    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
  5. taietel
    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
×
×
  • Create New...