Jump to content
Sign in to follow this  
water

Update the Excel UDF that comes with AutoIt

Recommended Posts

I posted some code that gets the formula in a cell, rather than what is displayed. Example the cell formula is "=sum(A1:a5)", displayed 10, it will get the "=sum(A1:a5)" instead of the 10.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

I have change a bit here and there in the Excel UDF myself. Have a few requests..

  • Additional possibilities in the font parameters. Right now can only change bold, italic, underline. Perhaps something else than a (long) parameter list is better, different functions ? Not sure.. Font .name and .size at least
  • Set shading and coloring of cells
  • Control of borders on range / cell, both linestyle, width and color
  • Control of print area (mark area for printout)
  • Run macro on excel side (execute VBA).
Some examples:

With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With

With Selection.Font
        .Name = "Calibri"
        .Size = 14
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With

With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With

With Selection
   
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With

I am just a hobby programmer, and nothing great to publish right now.

Share this post


Link to post
Share on other sites

Thanks a lot for your post!

The Excel UDF will always just contain basic functions. A function in the UDF has to do some processing because otherwise the function could be replaced by simply calling a method or accessing a property.

Therefore there will be no function to run a macro (we removed the macro function from the Word UDF).

You use

$oExcel.Run(macroname, parameter1, ....)

Maybe there wil be a function to work with ranges. The result can then be passed to other functions. Will have to think about.

For the Enumeration part there might be an even simpler solution. Don't have more info at the moment.

I will certainly read the blog, looks quite interesting.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-09-05 - Version 1.5.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

I'd posted #2199 in BugTracker a while back, but in last-minute clean-up I'd truncated the SetError() statements down to a single parameter, i.e: SetError(1) instead of SetError(1,0,0). That earned the ticket a thumbs-down. Even though it was suggested I correct and reactivate the ticket, I never did.

Anyway, _ExcelSheetAddNew() causes AutoIt to crash if you specify a workbook name that already exists. When that happens, it also causes Excel to create a new and unwanted generically-named worksheet such as "Sheet1", "Sheet2", etc.

I've been using the following version for a long time successfully. It adds an "@error = 2 " return, and avoids both the crash and the creation of a misnamed worksheet.

;                |@error=2   - Specified sheet already exists

Func _ExcelSheetAddNew($oExcel, $sName = "")
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If $sName Then
        For $_ws In $oExcel.ActiveWorkbook.Sheets
            If $_ws.Name = $sName Then Return SetError(2, 0, 0)
        Next
    EndIf
    $oExcel.ActiveWorkBook.WorkSheets.Add().Activate()
    If $sName Then $oExcel.ActiveSheet.Name = $sName
    Return 1
EndFunc ;==>_ExcelSheetAddNew
Edited by Spiff59

Share this post


Link to post
Share on other sites

I've also had an issue with _ExcelBookOpen() in that if you call it to open a workbook in write-mode ($fReadOnly = False) and the file is already open by another user, _ExcelBookOpen() still returns no error, and returns an object to a read-only copy of the spreadsheet. You have no way of knowing if you can proceed with updates. I've been using the following for a couple years, that inserts a FileOpen/FileClose test, and returns a new @error=3 condition if the file is in use. The test is only performed when $fReadOnly = False. Maybe there is a better way to go about it? Regardless, when you request write access to a spreadsheet, _ExcelBookOpen() should tell you whether or not it has been granted.

Func _ExcelBookOpen2($sFilePath, $fVisible = True, $fReadOnly = False, $sPassword = "", $sWritePassword = "")
    If Not FileExists($sFilePath) Then Return SetError(2, 0, 0)
    $fVisible = ($fVisible > 0)
    $fReadOnly = ($fReadOnly > 0)
    If Not $fReadOnly Then ; first check for open file - prior to launching Excel
        If FileOpen($sFilePath, 1) = -1 Then Return SetError(3, 0, 0)
        FileClose($sFilePath)
    EndIf
    Local $oExcel = ObjCreate("Excel.Application")
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    With $oExcel
        .Visible = $fVisible
        .WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, $sPassword, $sWritePassword)
        If (Not $fReadOnly) And .ActiveWorkBook.Readonly Then ; second check for open file - after launching Excel
            $oExcel.Quit
            $oExcel = ""
            Return SetError(3, 0, 0)
        EndIf
;      Select the first *visible* worksheet.
        For $i = 1 To .ActiveWorkbook.Sheets.Count
            If .ActiveWorkbook.Sheets($i).Visible = -1 Then ; $xlSheetVisible
                .ActiveWorkbook.Sheets($i).Select()
                ExitLoop
            EndIf
        Next
    EndWith
    Return $oExcel
EndFunc ;==>_ExcelBookOpen

Edit: regarding "Maybe there is a better way to go about it?". The FileOpen()/FileClose() test is much faster than actually launching an instance of Excel, but has the miniscule potential of finding a file closed, and then by the time Excel is launched, someone had opened it and you'll again be working with a read-only object and not know it. Maybe a second test should be added, testing the $fReadOnly flag, that would return the same @error=3 condition (as well as closing the instance of Excel) if the spreadsheet is not open in write-mode? I've yet to encounter that situation, where someone opened a file in the instance between the FileOpen() test and launching Excel, but it is remotely possible.

Edit2: I updated the code with 2 tests to check if the file was returned in a read-only state. The second test would be the more reliable. The only reason to keep the first test would be that the FileOpen()/FileClose() method is much faster than launching Excel and opening the workbook. But, the frequency that one will request a file in write-mode and find it already open is probably low enough that we could just delete the initial FileOpen()/FileClose() test?

Edited by Spiff59

Share this post


Link to post
Share on other sites

Thank you very much for your suggestions.

I will test them as soon as I'm back from vacation (end of september).

In the the cold and dark season I will have plenty of time to play with the UDF.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-09-05 - Version 1.5.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

Just wanted to say "Thank you Water and the other dudes" - I am very happy to see this course of action and I greatly appreciate your efforts.

<loud applause>


Regards,Andy (no, the other one)

Share this post


Link to post
Share on other sites

Anything special you want to see in the Excel UDF?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-09-05 - Version 1.5.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

Water

I just started using Autoit recently and wished I found your UDF before creating much of my own interface to EXCEL.

Project:

I am creating GUI that runs a Document Marquee that roates various Documents , i.e Word, PDF, Excel on a large display.

There is one Excel document that is a maintenance log that is READ Only

Request:

I have an issue in which I can give focus to focus to the READ ONLY Excel Workbook.

Is there a plan in your next UDF ( or does anyone know how ) to be able to Activate ( give focus to) a particular Read Only workbook without getting a "Invalid Index"

I apologize for the indirect addressing , I created an editable array for management to change documents in the Marquee.

Code:

$logtxt = $Title[$Row][$Col] & " Attempt Active"

Call("WritetoLog", $logtxt)

$oExcel = ObjCreate("Excel.Application") ; Create initial Excel Object

$oExcel.WorkBooks($Title[$Row][$Col]).Activate() ; Activate open workbook

I use a Log system to a txt file to trap my errors instead of Popups

09/13/2012 08:52:37 maintenance crossover.xlsx [Read-Only] Attempt Active

09/13/2012 08:52:37 COM Error Hex Number is: 80020009

09/13/2012 08:52:37 COM Error Description is: Invalid index.

Note: The READ-ONLY file will open but I can not switch to it via Activate()

If I don't make maintenance crossover a Read-Only Activate() works just fine.

Share this post


Link to post
Share on other sites

Use the Excel UDF that comes with AutoIt. Function _ExcelSheetActivate activates the desired sheet and returns an error if the sheet number is invalid (doesn't crash).


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-09-05 - Version 1.5.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

Water

Thank you for the response.

I didn't want to take the time to replace all my code so I examined the Excel UDF , extracted what I needed and came up with.

$oExcel.WorkBooks($Title[$Row][$Col]).Sheets($Tab[$Row][$Col]).Select()

I still get Invalid Index Errors on Read Only when attempting to Select a read only worksheet.

09/27/2012 13:57:49 WorkBook maintenance crossover.xlsx [Read-Only] Tab maint log Attempt

09/27/2012 13:57:49 COM Error Hex Number is: 80020009

09/27/2012 13:57:49 COM Error Description is: Invalid index.

Any thoughts on what may be causing it?

A thought has occurred to me while writing this plea.

I made the Document Marquee yo be a scripted based GUI that can an launch a particular document, loop through all documents or configure a GUI button to document.. The launch of document was coded to act as a single loop, i.e. the document opened and was then activated. The act of activating the last open document is redundant but should not cause any errors

The document opens with no error but if I activate a read only EXCEL document I get an error.

Share this post


Link to post
Share on other sites

RamadSula,

could you please do me a favour and open a new thread on the subject?

This thread is to discuss functionality of the existing Excel UDF and how to modify it.

Thanks!


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-09-05 - Version 1.5.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

I did a UDF for OOo/LibO Calc that includes some error checking on sheets, cells, and converting cell references, which could probably be applied here, if that would be helpful.

Share this post


Link to post
Share on other sites

Also, if there is a way to make this backwards compatible with as many versions of Excel as possible, that would be helpful. I know it may seem improbable/impossible, but I still run Excel 2000 at home, while running Excel 2010 at work. I try to write AutoIT scripts that will work in both places.

Share this post


Link to post
Share on other sites

If your scripts work with Excel 2000 now they should work with the brushed up version of the UDF as well.

Backward compatibility is one of the main goals.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-09-05 - Version 1.5.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

Hi Spiff59,

I've reopened ticket #2199 and implemented the solution you provided.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-09-05 - Version 1.5.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

I've also had an issue with _ExcelBookOpen() in that if you call it to open a workbook in write-mode ($fReadOnly = False) and the file is already open by another user, _ExcelBookOpen() still returns no error, and returns an object to a read-only copy of the spreadsheet. You have no way of knowing if you can proceed with updates. I've been using the following for a couple years, that inserts a FileOpen/FileClose test, and returns a new @error=3 condition if the file is in use. The test is only performed when $fReadOnly = False. Maybe there is a better way to go about it? Regardless, when you request write access to a spreadsheet, _ExcelBookOpen() should tell you whether or not it has been granted.

I have been thinking about this problem and I think we should stick with what Excel provides. So no FileOpen/FileClose, just let Excel do the checking.

If the workbook can't be opened read/write should we

  • Return "success" and set @extended to 1 to indicate that the workbook was opened read/only. The user then has to decide if he wants to work with the read/only version
  • Return "error" and close the read/only version of the workbook (as you suggested in your example code)
I would stick with the first solution because this behaviour is as it was before (backward compatibility). But the user gets additional information what happened.

What do you think?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-09-05 - Version 1.5.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Langmeister
      Hi, I'm sure that this is a simple question regarding webdriver udf but my search on the forum did not result in the exact same scenario that I need. I will adapt it to my corporate needs but at first I want it to work with an example everybody can reproduce if needed.
      I navigate to https://www.daysoftheyear.com/ and want to find out what special day today is. Using the following Code returns the wanted text that today is 'shark awareness day' in the console but it is not pasted in notepad as it is not copied as it probably should be.
      #include "wd_core.au3" #include "wd_helper.au3" Local $sDesiredCapabilities, $sSession, $sID _WD_Option("Driver", "C:\Program Files (x86)\AutoIt3\chromedriver.exe") _WD_Option('Port', 9515) _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"') $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true }}}}' _WD_Startup() $sSession = _WD_CreateSession($sDesiredCapabilities) _WD_Navigate($sSession, "https://www.daysoftheyear.com/?timezone_offset=nan") _WD_LoadWait($sSession) Sleep(3000) Local $sElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "/html/body/div[2]/header/div/div[1]/div/div[2]/h3/a") _WD_ElementAction($sSession, $sElement, 'text') ClipGet() Run("notepad.exe") Sleep(500) Send("^v") Scite returns this, therefore the text has been found but the last mile to paste it to notepad or excel does not work for me.
      __WD_Get: URL=HTTP://127.0.0.1:9515/session/b72166b774d7fefb258b3721fc1d4306/element/657e6b5d-31a9-46a8-85c4-acb7f72bf659/text __WD_Get: StatusCode=200; $iResult = 0; $sResponseText={"value":"Shark Awareness Day"}... _WD_ElementAction: {"value":"Shark Awareness Day"}... Thanks in advance! 
       
    • By pat4005
      A tiny UDF that can shoot magic packets at your computers to wake them the heck up. All credits to Olish.
      The only parameter it needs to be specified – is your machine's MAC-address (ip address (the second parameter), at which you will be sending magic packet is generating automatically from @IPAddress1 macro)
      Example:
      _WoL_WakeDevice('001CC0CAED7A') ; the second parameter (if necessery) must be a broadcast address of your local network segment (i.e. 192.168.0.255 for a network 192.168.0.0)  
      _WakeOnLan.au3
    • By GOSM
      Hey,
      as part of a larger Programm I am trying to copy the values from one cell in an Excel file to another. The code I am using is the folowing.
       
      Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_RangeCopyPaste($oExcel.ActiveSheet, "V4:W43", "X4", False, $xlPasteValues, Default, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "Error copying rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "2 Rows successfully pasted from the clipboard to row 1.") As I understood it the option $xlPasteValues should do the trick  but it is still copying the inserted formulas. I attached a test code and excel file.
      Thanks for the help Simon
      Copy Values Test.zip
    • By Chimp
      An "improper" "unusual" use of Excel
      the script allows the creation of artistic images by simply coloring the background of the individual cells of the Excel workbook.
      Although definitely useless ... I find it quite funny though
      have a good time
      many thanks to @UEZ , @Malkey , @water
      p.s.
      I think the pixelite + color to array process can be simplified, but I used the two ready-made functions provided by UEZ and Malkey. I thank both of you (credits  and links in listing)
      p.p.s.
      strange behaviour: while excell is filling cells, if you move the mouse pointer off the excell window, the fill speed increases ... (?)

       
      ; =============================================================================================================================== ; Name ..........: Excel in art ; Description ...: This script allows the creation of artistic images in Excel from a choosed picture. ; The picture is done by simply coloring the background of the individual cells of an Excel workbook. ; Although definitely useless, I find it quite funny though ; ; Return values .: An artistic image in an Excel workbook ; Author ........: Addiego Gianni (chimp) ; Modified ......: ; Remarks .......: Many thanks to UEZ, Malkey and Water ; Related .......: ; Link ..........: ; Example .......: ; =============================================================================================================================== #include <GDIPlus.au3> #include <Excel.au3> _PixelsToCells(50) ; <-- Parameter 50 is the cumber of horizontal Excel cells to be filled MsgBox(64, "All done", "Excel art is ready") Func _PixelsToCells($iHcells = 50) ; Check application object Local $oExcel = _Excel_Open() If Not IsObj($oExcel) Then MsgBox(16, "Error", "Sorry, You need to have 'Excel' intalled") ; Choose Image File Local $sPath = FileOpenDialog("Choose Image File", @ScriptDir & "", "Images (*.gif;*.png;*.jpg;*.bmp)| All (*.*)") If $sPath = '' Then Exit MsgBox(16, "Error", "Sorry, no image was chosen") ; Create a new Excel workbook $oWorkbook = _Excel_BookNew($oExcel, 1) ; initialize GDI+ _GDIPlus_Startup() Local $hBmp = _GDIPlus_BitmapCreateFromFile($sPath) Local $iWidth = _GDIPlus_ImageGetWidth($hBmp) ; get image width ; Local $iHeight = _GDIPlus_ImageGetHeight($hBmp) ; not needed here Local $iStep = $iWidth / $iHcells ; calculate the pixelation factor Local $hBitmap_new = _GDIPlus_PixelateBitmap($hBmp, $iStep) ; pixelate the image Local $aPixelColors = _FileImageToArray($hBitmap_new) ; get pixel colors ; reduce Excel columns width Local $xx = 1, $yy = 1 For $iCol = 0 To UBound($aPixelColors, 2) - 1 Step $iStep $oWorkbook.Sheets(1).Columns($xx).ColumnWidth = 1 $xx += 1 Next ; reduce Excel rows height For $iRow = 0 To UBound($aPixelColors) - 1 Step $iStep $oWorkbook.Sheets(1).Rows($yy).RowHeight = 9 $yy += 1 Next $xx = 1 $yy = 1 For $iRow = 0 To UBound($aPixelColors) - 1 Step $iStep For $iCol = 0 To UBound($aPixelColors, 2) - 1 Step $iStep $oWorkbook.Sheets(1).Range(_Excel_ColumnToLetter($xx) & $yy).Interior.Color = Number("0x" & $aPixelColors[$iRow][$iCol]) $xx += 1 Next $yy += 1 $xx = 1 Next _GDIPlus_BitmapDispose($hBmp) _GDIPlus_Shutdown() EndFunc ;==>_PixelsToCells ; by UEZ ; https://www.autoitscript.com/forum/topic/167707-imagepixelate/?do=findComment&comment=1227509 Func _GDIPlus_PixelateBitmap($hBitmap, $iPixelate, $bSmooth = 1) Local $iWidth = _GDIPlus_ImageGetWidth($hBitmap), $iHeight = _GDIPlus_ImageGetHeight($hBitmap) Local $iNewW = Round($iWidth / $iPixelate, 0), $iNewH = Round($iHeight / $iPixelate, 0) Local $hBitmap_scaled = _GDIPlus_BitmapCreateFromScan0($iNewW, $iNewH) Local $hCtxt = _GDIPlus_ImageGetGraphicsContext($hBitmap_scaled) Local $iInterpolation = 5 If $bSmooth Then $iInterpolation = $GDIP_INTERPOLATIONMODE_BILINEAR _GDIPlus_GraphicsSetInterpolationMode($hCtxt, $iInterpolation) _GDIPlus_GraphicsDrawImageRect($hCtxt, $hBitmap, 0, 0, $iNewW, $iNewH) _GDIPlus_GraphicsDispose($hCtxt) Local $hBitmap_pixelated = _GDIPlus_BitmapCreateFromScan0($iWidth, $iHeight) $hCtxt = _GDIPlus_ImageGetGraphicsContext($hBitmap_pixelated) _GDIPlus_GraphicsSetInterpolationMode($hCtxt, $GDIP_INTERPOLATIONMODE_NearestNeighbor) _GDIPlus_GraphicsDrawImageRectRect($hCtxt, $hBitmap_scaled, 0, 0, $iNewW, $iNewH, -$iPixelate, -$iPixelate, $iWidth + 2 * $iPixelate, $iHeight + 2 * $iPixelate) _GDIPlus_GraphicsDispose($hCtxt) Return $hBitmap_pixelated EndFunc ;==>_GDIPlus_PixelateBitmap ; by Malkey ; https://www.autoitscript.com/forum/topic/112540-is-there-a-function-for-reading-images-into-2d-arrays/?do=findComment&comment=788472 Func _FileImageToArray($hImage) Local $Reslt, $stride, $format, $Scan0, $iIW, $iIH ; , $hImage Local $v_Buffer, $width, $height ; _GDIPlus_Startup() ; $hImage = _GDIPlus_ImageLoadFromFile($sFileName) $iIW = _GDIPlus_ImageGetWidth($hImage) $iIH = _GDIPlus_ImageGetHeight($hImage) ProgressOn("Progress Bar", "Filling a " & $iIW & " x " & $iIH & " size array.", "0 percent") $Reslt = _GDIPlus_BitmapLockBits($hImage, 0, 0, $iIW, $iIH, $GDIP_ILMREAD, $GDIP_PXF32ARGB) ;Get the returned values of _GDIPlus_BitmapLockBits () $width = DllStructGetData($Reslt, "width") $height = DllStructGetData($Reslt, "height") $stride = DllStructGetData($Reslt, "stride") $format = DllStructGetData($Reslt, "format") $Scan0 = DllStructGetData($Reslt, "Scan0") Local $aArray[$height][$width] For $j = 0 To $iIH - 1 For $i = 0 To $iIW - 1 $v_Buffer = DllStructCreate("dword", $Scan0 + ($j * $stride) + ($i * 4)) $aArray[$j][$i] = StringRegExpReplace(Hex(DllStructGetData($v_Buffer, 1), 6), "(.{2})(.{2})(.{2})", "\3\2\1") ; To RGB format Next ProgressSet(Int(100 * $j / ($iIH)), Int(100 * $j / ($iIH)) & " percent") Next _GDIPlus_BitmapUnlockBits($hImage, $Reslt) ProgressOff() _GDIPlus_ImageDispose($hImage) Return $aArray EndFunc ;==>_FileImageToArray  
    • By MrCreatoR
      This UDF allows to create formatted label using pseudo element RichLabel (RichEdit actually). Formating is set by using special modificator similar to <font> tag in Html.
      Notes: This UDF is a transformation-continuation of related UDF


      Example:
      Download:
      GUIRichLabel_1.2.zip
      Small syntax related fix: GUIRichLabel_1.1.zip
      GUIRichLabel_1.1.zip
       
      History version:
×
×
  • Create New...