Jump to content
Sign in to follow this  
UEZ

[Solved] Create an array from filtered Excel sheet

Recommended Posts

What is the best way to create an array from a filtered Excel sheet?

Let say I've set a filter this way

_Excel_FilterSet($oWorkbook, Default, Default, 10, "AutoIt", $xlFilterValues)

How can I create an array only from the filtered values only using a range from "T2:ACxxx" whereas xxx is the last line of the selection?

 

Thanks.

Edited by UEZ

Please don't send me any personal message and ask for support! I will not reply!

Selection of finest graphical examples at Codepen.io

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

Share this post


Link to post
Share on other sites

This will grab all visible rows from a range:

$oRange = $oAppl.ActiveSheet.Range("a1:E100")
For $oRow In $oRange.Rows
    If $oRow.Hidden = False Then
        ConsoleWrite($oRow.Cells(1, 1).Value & @CRLF)
    EndIf
Next

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2020-03-26 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-02 - Version 1.6.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-02 - Version 1.2.0.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

Thanks water but it doesn't work,

In Excel after filtering I get the information in the buttom: 620 of 2833 RECORDS FOUND.

But when I count it using

_Excel_FilterSet($oWorkbook, Default, Default, 19, "Network", $xlFilterValues)

Local $oRgn = $oWorkbook.Activesheet.Range("a2:a2833")

$i = 0
For $oRow In $oRgn.Rows
    If $oRow.Hidden = False Then
        $i += 1
        ConsoleWrite($i & " = " & $oRow.Cells($i, 19).Value & @CRLF)
    EndIf
Next

then the last $i is 68 only.

Edited by UEZ

Please don't send me any personal message and ask for support! I will not reply!

Selection of finest graphical examples at Codepen.io

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

Share this post


Link to post
Share on other sites

How do you define $oRgn?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2020-03-26 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-02 - Version 1.6.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-02 - Version 1.2.0.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

Ok, my mistake. A2833 was set in ConsoleWrite and not in $oRgn.

 

Anyhow, the output is 

1 = Client Name
...
501 = DC OPS
502 = 
503 = 
504 = 
505 = 
506 = 
507 = 
508 = 
509 = 
510 = 
511 = 
512 = 
513 = 
514 = 
515 = 
516 = 
517 = 
518 = 
519 = 
520 = 
521 = 
522 = 
523 = 
524 = 
525 = 
526 = 
527 = 
528 = 
529 = 
530 = 
531 = 
532 = 
533 = 
534 = 
535 = 
536 = 
537 = 
538 = 
539 = 
540 = 
541 = 
542 = 
543 = 
544 = 
545 = 
546 = 
547 = 
548 = 
549 = 
550 = 
551 = 
552 = 
553 = 
554 = 
555 = 
556 = 
557 = 
558 = 
559 = 
560 = 
561 = 
562 = 
563 = 
564 = 
565 = 
566 = 
567 = 
568 = 
569 = 
570 = 
571 = 
572 = 
573 = 
574 = 
575 = 
576 = 
577 = 
578 = 
579 = 
580 = 
581 = 
582 = 
583 = 
584 = 
585 = 
586 = 
587 = 
588 = 
589 = 
590 = 
591 = 
592 = 
593 = 
594 = 
595 = 
596 = 
597 = 
598 = 
599 = 
600 = 
601 = 
602 = 
603 = 
604 = 
605 = 
606 = 
607 = 
608 = 
609 = 
610 = 
611 = 
612 = 
613 = 
614 = 
615 = 
616 = 
617 = 
618 = 
619 = 
620 = 
621 =

But it must be only of the same value -> NETWORK.

Edited by UEZ

Please don't send me any personal message and ask for support! I will not reply!

Selection of finest graphical examples at Codepen.io

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

Share this post


Link to post
Share on other sites

As $oRow only consists of a single row it should be:

ConsoleWrite($i & " = " & $oRow.Cells(1, 19).Value & @CRLF)

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2020-03-26 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-02 - Version 1.6.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-02 - Version 1.2.0.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

Ah, yes - now it works.

Thanks.

 

How can I get the number 2833 (amount of rows)?

Edited by UEZ

Please don't send me any personal message and ask for support! I will not reply!

Selection of finest graphical examples at Codepen.io

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

Share this post


Link to post
Share on other sites
$oRgn.Rows.Count

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2020-03-26 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-02 - Version 1.6.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-02 - Version 1.2.0.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

Another option without the loop might look like this.  I think it should work but I defer to @Water in all things Excel (and au3 in general):

$oRange=$oWorkbook.ActiveSheet.Range("A2:a28337").Rows.SpecialCells($xlCellTypeVisible)
local $aResult = _Excel_RangeRead($oWorkbook, 2, $oRange)
_ArrayDisplay($aResult)

 

Edited by Jfish

Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites

Unfortunately this doesn't work. _Excel_RangeRead only works on a single range and - in this case - will only return line 1.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2020-03-26 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-02 - Version 1.6.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-02 - Version 1.2.0.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

The problem is that after using the SpecialCells method, you are returned a Range of non-continuous areas, which you can't use the Rows property on.

$oRange = $oWorkbook.ActiveSheet.Usedrange.SpecialCells($xlCellTypeVisible)
Local $iRowCount = 0
For $oArea In $oRange.Areas
    $iRowCount = $iRowCount + $oArea.Rows.Count
Next
ConsoleWrite($iRowCount & @CRLF)

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2020-03-26 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-02 - Version 1.6.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-02 - Version 1.2.0.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

Ah, thanx, I tried also the areas but it didn't work as what I did.

That means I've to run 2x the range, once to get the end and the 2nd time to create the result as an array.

 


Please don't send me any personal message and ask for support! I will not reply!

Selection of finest graphical examples at Codepen.io

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

Share this post


Link to post
Share on other sites

The problem is that after using the SpecialCells method, you are returned a Range of non-continuous areas, which you can't use the Rows property on.

$oRange = $oWorkbook.ActiveSheet.Usedrange.SpecialCells($xlCellTypeVisible)
Local $iRowCount = 0
For $oArea In $oRange.Areas
    $iRowCount = $iRowCount + $oArea.Rows.Count
Next
ConsoleWrite($iRowCount & @CRLF)

 

Thanks for that explanation.  I had a question teed up to ask what you meant by "single range" but now I get it.  Thanks!

 


Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites

I think I will provide a solution to get the filtered rows in a single array and add that to the Excel wiki.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2020-03-26 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-02 - Version 1.6.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-02 - Version 1.2.0.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

Ah, thanx, I tried also the areas but it didn't work as what I did.

That means I've to run 2x the range, once to get the end and the 2nd time to create the result as an array.

 

This example returns all visible data in one go:

$oRange = $oWorkbook.ActiveSheet.Usedrange.SpecialCells($xlCellTypeVisible)
Local $aResult[1][$oRange.Columns.Count], $aContent
; Read the data of all Ranges in the Area and concatenate the returned arrays.
For $oArea In $oRange.Areas
    $aContent = _Excel_RangeRead($oWorkbook, Default, $oArea)
    _ArrayConcatenate($aResult, $aContent)
Next
_ArrayDisplay($aResult)

Could you please test if this works for you?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2020-03-26 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-02 - Version 1.6.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-02 - Version 1.2.0.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

Well water,

optically it seems to be in one go but when you check out the stuff under the hood it look different. ;)

I'm loading a comma seperated text file whereas line 1 is some text only and the column headers are in line 2.

You script generates all entries including a blank line 0 which is the line 1 in the text file. The generated array has 622 entries (minus empty line and minus header = 620 entries).

That's ok.

 

It takes approx. 26 seconds to generate the array!


Please don't send me any personal message and ask for support! I will not reply!

Selection of finest graphical examples at Codepen.io

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

Share this post


Link to post
Share on other sites

26 seconds to process 2866 records?
Did you just measure the code I posted above or is this the whole script (reading the CSV, filtering etc.)?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2020-03-26 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-02 - Version 1.6.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-02 - Version 1.2.0.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 added a few timers to my code. Most time is spent in _Excel_RangeRead.
Which values do you get?

Local $hSpecialCells, $iSpecialCells = 0, $hRangeRead, $iRangeRead = 0, $hArrayConcat, $iArrayConcat = 0
$hSpecialCells = TimerInit()
$oRange = $oWorkbook.ActiveSheet.Usedrange.SpecialCells($xlCellTypeVisible)
$iSpecialCells = TimerDiff($hSpecialCells)
Local $aResult[1][$oRange.columns.Count], $aContent
; Read the data of all Ranges in the Area and concatenate the returned arrays.
For $oArea In $oRange.Areas
    $hRangeRead = TimerInit()
    $aContent = _Excel_RangeRead($oWorkbook, Default, $oArea)
    $iRangeRead = $iRangeRead + TimerDiff($hRangeRead)
    $hArrayConcat = TimerInit()
    _ArrayConcatenate($aResult, $aContent)
    $iArrayConcat = $iArrayConcat + TimerDiff($hArrayConcat)
Next
MsgBox(0, "", "SpecialCells: " & $iSpecialCells & @CRLF & _
              "RangeRead: " & $iRangeRead & @CRLF & _
              "ArrayConcat: " & $iArrayConcat)
_ArrayDisplay($aResult)

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2020-03-26 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-02 - Version 1.6.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-02 - Version 1.2.0.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 nooneclose
      I need to dynamically resize my 2d array while looping. 
      I know this code:
      ReDim $rArray[UBound($rArray) + 1] works for the rows, however, I also need to increase the columns. How would i go about increasing both rows and columns while looping? 
    • 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 Zaoka
      Hi guys
      Need little help with filtering.
      I'm trying to filter specific weeks from power pivot table using this code recorded with Excel VBA :
       
      #include <Excel.au3> Global $oExcel = _Excel_Open() Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\....\Orders.xlsb") $oWorkbook.PivotTables("PivotTable1").PivotFields( _ "[Report 2].[Week].[Week]").VisibleItemsList = Array( _ "[Report 2].[Week].&[10]", "[Report 2].[Week].&[11]", _ "[Report 2].[Week].&[12]", "[Report 2].[Week].&[13]", _ "[Report 2].[Week].&[14]", "[Report 2].[Week].&[15]") But get error
      error: Array(): undefined function. Not sure how to resolve this.
×
×
  • Create New...