Sign in to follow this  
Followers 0
golfinhu

_ExcelReadSheetToArrayEX() much faster than the original

19 posts in this topic

#1 ·  Posted (edited)

Hello everyone, i had an excel file with 450 rows and 9 columns, and using _ExcelReadSheetToArray() takes about 30 seconds to read to array, which i found very time consuming!

So i rewrote the code and now i can read to array the same file in less than 1 second.

So i decided to share here!

The new code:

#include <Excel.au3>
; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelReadSheetToArrayEx
; Description ...: Create a 2D array from the rows/columns of the active worksheet.
; Syntax.........: _ExcelReadSheetToArray($oExcel[, $iStartRow = 1[, $iStartColumn = 1[, $iRowCnt = 0[, $iColCnt = 0]]]])
; Parameters ....: $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                 $iStartRow - Row number to start reading, defaults to 1 (first row)
;                 $iStartColumn - Column number to start reading, defaults to 1 (first column)
;                 $iRowCnt - Count of rows to read, defaults to 0 (all)
;                 $iColCnt - Count of columns to read, defaults to 0 (all)
; Return values .: Success    - Returns a 2D array with the specified cell contents by [$row][$col]
;                 Failure  - Returns 0 and sets @error on errors:
;                 |@error=1  - Specified object does not exist
;                 |@error=2  - Start parameter out of range
;                 |@extended=0 - Row out of range
;                 |@extended=1 - Column out of range
;                 |@error=3 - Count parameter out of range
;                 |@extended=0 - Row count out of range
;                 |@extended=1 - Column count out of range
; Author ........: SEO; Rewrited by Golfinhu.
; Modified.......: litlmike (added Column shift parameter to Start Array Column on 0) and PsaltyDS 01/04/08 - 2D version _ExcelReadSheetToArray()
; Remarks .......: Returned array has row count in [0][0] and column count in [0][1].
;                 Except for the counts above, row 0 and col 0 of the returned array are empty, as actual
;                 cell data starts at [1][1] to match R1C1 numbers.
;                 By default the entire sheet is returned.
;                 If the sheet is empty [0][0] and [0][1] both = 0.
; Related .......:
; Link ..........:
; Example .......: No
; ===============================================================================================================================
Func _ExcelReadSheetToArrayEx($oExcel, $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 0, $iColCnt = 0)
Local $avRET[1][2] = [[0, 0]] ; 2D return array
; Test inputs
If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
If $iStartRow < 1 Then Return SetError(2, 0, 0)
If $iStartColumn < 1 Then Return SetError(2, 1, 0)
If $iRowCnt < 0 Then Return SetError(3, 0, 0)
If $iColCnt < 0 Then Return SetError(3, 1, 0); Get size of current sheet as R1C1 string
;    Note: $xlCellTypeLastCell and $x1R1C1 are constants declared in ExcelCOM_UDF.au3
Local $sLastCell = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)
; Extract integer last row and col
$sLastCell = StringRegExp($sLastCell, "(d+)", 3)
Local $iLastRow = $sLastCell[0]
Local $iLastColumn = $sLastCell[1]; Return 0's if the sheet is blank
If $sLastCell = "R1C1" And $oExcel.Activesheet.Cells($iLastRow, $iLastColumn).Value = "" Then Return $avRET
; Check input range is in bounds
If $iStartRow > $iLastRow Then Return SetError(2, 0, 0)
If $iStartColumn > $iLastColumn Then Return SetError(2, 1, 0)
If $iStartRow + $iRowCnt - 1 > $iLastRow Then Return SetError(3, 0, 0)
If $iStartColumn + $iColCnt - 1 > $iLastColumn Then Return SetError(3, 1, 0); Check for defaulted counts
If $iRowCnt = 0 Then $iRowCnt = Number($iLastRow)
If $iColCnt = 0 Then $iColCnt = Number($iLastColumn)
;Read data
Local $aArray = $oExcel.ActiveSheet.Range($oExcel.Cells($iStartRow, $iStartColumn), $oExcel.Cells($iRowCnt, $iColCnt)).Value
Dim $avRET[UBound($aArray, 2) + 1][UBound($aArray)] = [[UBound($aArray, 2), UBound($aArray)]] ;Declare Array again and set row and col count
For $i = 0 To UBound($aArray, 1) - 1
  For $j = 0 To UBound($aArray, 2) - 1
   $avRET[$j + 1][$i] = $aArray[$i][$j]
  Next
Next
Return $avRET
EndFunc   ;==>_ExcelReadSheetToArrayEx

the modification is very simple, but gave a huge difference in time!

:)

Edited by golfinhu
1 person likes this

Share this post


Link to post
Share on other sites



Thanks for share , very fast ( try 2000 rows & 20 columns ) just 1sec

Share this post


Link to post
Share on other sites

Oh My GOD! Not just a little, A LOT faster. This saves me hours on huge excel files. I wish I'd found it sooner. Thank you soooo much!

Can you reverse it for time savings as well? WritesheetToArrayEX? That would be GREAT!

Thanks,

Share this post


Link to post
Share on other sites

Question, I am not sure your function is respecting the start column parameter. I am passing "21" in a sheet with 22 columns and it is building a large array starting at Col 1.

$truefalseArray=_ExcelReadSheetToArrayEx($IMARCTXT,1,21,0,1)

I am on Excel 2010. Any thoughts?


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

This function tries to put the row count in element [0][0] and the column count in element [0][1].

That is problematical if you've requested the return of a single column array.

Maybe this would be a better finish for the function:

;Read data
Local $aArray = $oExcel.ActiveSheet.Range($oExcel.Cells($iStartRow, $iStartColumn), $oExcel.Cells($iRowCnt, $iColCnt)).Value
Local $rows = UBound($aArray, 2)
Local $cols = UBound($aArray)
Local $avRET[$rows + 1][$cols] = [[$rows]]
If $cols > 1 Then $avRET[0][1] = $cols
For $i = 0 To ($cols - 1)
    For $j = 0 To ($rows - 1)
        $avRET[$j + 1][$i] = $aArray[$i][$j]
    Next
Next
Return $avRET
EndFunc   ;==>_ExcelReadSheetToArrayEx

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

Question, I am not sure your function is respecting the start column parameter.

You're correct, this function has some indexing issues. It also blows up in one area of code if the return is only a single cell and another area if the return is a single column. The $iColShift parameter is removed (defaulting to enabled behavior) making it a potential script-breaker.

Most of our spreadsheets here at work have at least portions that are protected, and I quickly noticed this version, as well as the production version, give a COM error if run on a protected worksheet.

The production version also returns a row/column count of 1/1 when run on an empty sheet (the docs say it should be 0/0), and it returns an extra blank column when the (peculiar) $iColShift parameter is enabled.

This example below (I believe) fixes the indexing and single-cell/column issues of the example in this thread, and restores the $iColShift parameter. It also, while retaining golfinhu's speed advantage over the production version, corrects the production version error when searching an empty sheet and removes the extra column returned when $iColShift is enabled.

; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelReadSheetToArray
; Description ...: Create a 2D array from the rows/columns of the active worksheet.
; Syntax.........: _ExcelReadSheetToArray($oExcel[, $iStartRow = 1[, $iStartColumn = 1[, $iRowCnt = 0[, $iColCnt = 0]]]])
; Parameters ....: $oExcel - excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                $iStartRow - Row number to start reading, defaults to 1 (first row)
;                $iStartColumn - Column number to start reading, defaults to 1 (first column)
;                $iRowCnt - Count of rows to read, defaults to 0 (all)
;                $iColCnt - Count of columns to read, defaults to 0 (all)
;                $iColShift - Match R1C1 column position, or start array in column 0. Default is 0 (match R1C1 values)
; Return values .: Success  - Returns a 2D array with the specified cell contents by [$row][$col]
;                Failure  - Returns 0 and sets @error on errors:
;                |@error=1  - Specified object does not exist
;                |@error=2  - Start parameter out of range
;                |@extended=0 - Row out of range
;                |@extended=1 - Column out of range
;                |@error=3 - Count parameter out of range
;                |@extended=0 - Row count out of range
;                |@extended=1 - Column count out of range
; Author ........: SEO
; Modified.......: PsaltyDS 01/04/08 - 2D version, litlmike - Column shift parm, Golfinhu - Speed enhancement, Spiff59 - Allow protected sheets
; Remarks .......: Returned array has row count in [0][0] and column count in [0][1] (unless
;                Except for the counts above, row 0 and col 0 of the returned array are empty, as actual
;                cell data starts at [1][1] to match R1C1 numbers.
;                By default the entire sheet is returned.
;                If the sheet is empty [0][0] and [0][1] both = 0.
; Related .......:
; Link ..........:
; Example .......: No
; ===============================================================================================================================
Func _ExcelReadSheetToArray($oExcel, $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 0, $iColCnt = 0, $iColShift = 0)
; Parameter edits
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If $iStartRow < 1 Then Return SetError(2, 0, 0)
    If $iStartColumn < 1 Then Return SetError(2, 1, 0)
    If $iRowCnt < 0 Then Return SetError(3, 0, 0)
    If $iColCnt < 0 Then Return SetError(3, 1, 0)

    Local $iLastRow = $oExcel.Activesheet.UsedRange.Rows.Count
    Local $iLastColumn = $oExcel.Activesheet.UsedRange.Columns.Count
    If ($iLastRow + $iLastColumn = 2) And $oExcel.Activesheet.Cells(1, 1).Value = "" Then ; empty result
        Local $avRET[1][2] = [[0, 0]]
        Return $avRET
    EndIf

; Parameter edits (continued)
    If $iStartRow > $iLastRow Then Return SetError(2, 0, 0)
    If $iStartColumn > $iLastColumn Then Return SetError(2, 1, 0)
    If $iStartRow + $iRowCnt - 1 > $iLastRow Then Return SetError(3, 0, 0)
    If $iStartColumn + $iColCnt - 1 > $iLastColumn Then Return SetError(3, 1, 0); Check for defaulted counts
    If $iRowCnt Then
        $iLastRow = $iStartRow + $iRowCnt - 1
    Else
        $iRowCnt = $iLastRow - $iStartRow + 1
    EndIf
    If $iColCnt Then
        $iLastColumn = $iStartColumn + $iColCnt - 1
    Else
        $iColCnt = $iLastColumn - $iStartColumn + 1
    EndIf

; Read data
    Local $aArray = $oExcel.ActiveSheet.Range($oExcel.Cells($iStartRow, $iStartColumn), $oExcel.Cells($iLastRow, $iLastColumn)).Value
    If Not IsArray($aArray) Then ; single-cell result
        Local $avRET[2][2] = [[1, 1],["", $aArray]]
        Return $avRET
    EndIf

; Convert Col/Row array from Excel to Row/Col
    Local $avRET[$iRowCnt + 1][$iColCnt + ($iColCnt = 1 Or $iColShift = 0)] = [[$iRowCnt, $iColCnt]]
    For $i = 1 To $iColCnt
        For $j = 1 To $iRowCnt
           $avRET[$j][$i - $iColShift] = $aArray[$i - 1][$j - 1]
        Next
    Next
    Return $avRET
EndFunc   ;==>_ExcelReadSheetToArray

Edit: Oops! Forgot to mention this version can also process protected worksheets.

Edited by Spiff59

Share this post


Link to post
Share on other sites

this is the fastest function i got to read excel file. great!

Share this post


Link to post
Share on other sites

At the moment I'm about to update the builtin Excel UDF. All those (speed) enhancements will be part of the new UDF.

Please check this for what I plan to do. Suggestions are always welcome!

I have a running to see how important backward compatibility is for users. Please vote!

Based on the poll some design decisions will be made!


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

and even faster and does not useneed office & object ect ect, but only for the XLSX (Office 2007 or more recent)

Ciao.

Edited by DXRW4E

apps-odrive.pngdrive_app_badge.png box-logo.png new_logo.png MEGA_Logo.png

Share this post


Link to post
Share on other sites

I just speed tested the function from post #9 and _XLSXReadToArray in a head to head test, and the one in post #9 is at least twice as fast as your's. Although your's does have the added advantage of not needing to have Excel installed, which is always being asked for.


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

#13 ·  Posted (edited)

I just speed tested the function from post #9 and _XLSXReadToArray in a head to head test, and the one in post #9 is at least twice as fast as your's

you mean post #8 right ???

I'm really very curious about your test ???, can you just post exactly all the the script to that used during the your test ?????

because if it's true what you said, means that your test and x 4 times faster than the Office 2013, this does not seem very logical hmmmmmmmmm

the and 2 times faster than Office 2013 because it uses a different way (or direct way) to read the file, but using the object, or the same way of the office, I do not see how he could never be faster than the same office, and not faster but 4 times faster than hmmmmmmmmm

Although your's does have the added advantage of not needing to have Excel installed, which is always being asked for.

this is not little, that makes a really big difference in my opinion Edited by DXRW4E

apps-odrive.pngdrive_app_badge.png box-logo.png new_logo.png MEGA_Logo.png

Share this post


Link to post
Share on other sites

#14 ·  Posted (edited)

Here's the exact script I used, I just changed out the file name used because it wouldn't make much sense to include that as well.

#include <Array.au3>
#include "_XLSXReadToArray.au3"
#include <excel.au3>
Local $a1, $b1
$a1 = TimerInit()
$b1 = _XLSXReadToArray("insert a large xlsx file here")
$a1 = TimerDiff($a1)
_ArrayDisplay($b1, $a1)
ConsoleWrite("Time1 - " & $a1 & @LF)
$a1 = TimerInit()
$File = _ExcelBookOpen("insert a large xlsx file here", 0)
$b1 = __ExcelReadSheetToArray($File)
$a1 = TimerDiff($a1)
_ArrayDisplay($b1, $a1)
ConsoleWrite("Time1 - " & $a1 & @LF)
_ExcelBookClose($File)
Exit
; #FUNCTION# ====================================================================================================================
; Name...........: __ExcelReadSheetToArray
; Description ...: Create a 2D array from the rows/columns of the active worksheet.
; Syntax.........: _ExcelReadSheetToArray($oExcel[, $iStartRow = 1[, $iStartColumn = 1[, $iRowCnt = 0[, $iColCnt = 0]]]])
; Parameters ....: $oExcel - excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;            $iStartRow - Row number to start reading, defaults to 1 (first row)
;            $iStartColumn - Column number to start reading, defaults to 1 (first column)
;            $iRowCnt - Count of rows to read, defaults to 0 (all)
;            $iColCnt - Count of columns to read, defaults to 0 (all)
;            $iColShift - Match R1C1 column position, or start array in column 0. Default is 0 (match R1C1 values)
; Return values .: Success - Returns a 2D array with the specified cell contents by [$row][$col]
;            Failure - Returns 0 and sets @error on errors:
;            [email="|@error=1"]|@error=1[/email] - Specified object does not exist
;            [email="|@error=2"]|@error=2[/email] - Start parameter out of range
;            [email="|@extended=0"]|@extended=0[/email] - Row out of range
;            [email="|@extended=1"]|@extended=1[/email] - Column out of range
;            [email="|@error=3"]|@error=3[/email] - Count parameter out of range
;            [email="|@extended=0"]|@extended=0[/email] - Row count out of range
;            [email="|@extended=1"]|@extended=1[/email] - Column count out of range
; Author ........: SEO
; Modified.......: PsaltyDS 01/04/08 - 2D version, litlmike - Column shift parm, Golfinhu - Speed enhancement, Spiff59 - Allow protected sheets
; Remarks .......: Returned array has row count in [0][0] and column count in [0][1] (unless
;            Except for the counts above, row 0 and col 0 of the returned array are empty, as actual
;            cell data starts at [1][1] to match R1C1 numbers.
;            By default the entire sheet is returned.
;            If the sheet is empty [0][0] and [0][1] both = 0.
; Related .......:
; Link ..........:
; Example .......: No
; ===============================================================================================================================
Func __ExcelReadSheetToArray($oExcel, $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 0, $iColCnt = 0, $iColShift = 0)
; Parameter edits
If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
If $iStartRow < 1 Then Return SetError(2, 0, 0)
If $iStartColumn < 1 Then Return SetError(2, 1, 0)
If $iRowCnt < 0 Then Return SetError(3, 0, 0)
If $iColCnt < 0 Then Return SetError(3, 1, 0)
Local $iLastRow = $oExcel.Activesheet.UsedRange.Rows.Count
Local $iLastColumn = $oExcel.Activesheet.UsedRange.Columns.Count
If ($iLastRow + $iLastColumn = 2) And $oExcel.Activesheet.Cells(1, 1).Value = "" Then ; empty result
Local $avRET[1][2] = [[0, 0]]
Return $avRET
EndIf
; Parameter edits (continued)
If $iStartRow > $iLastRow Then Return SetError(2, 0, 0)
If $iStartColumn > $iLastColumn Then Return SetError(2, 1, 0)
If $iStartRow + $iRowCnt - 1 > $iLastRow Then Return SetError(3, 0, 0)
If $iStartColumn + $iColCnt - 1 > $iLastColumn Then Return SetError(3, 1, 0); Check for defaulted counts
If $iRowCnt Then
$iLastRow = $iStartRow + $iRowCnt - 1
Else
$iRowCnt = $iLastRow - $iStartRow + 1
EndIf
If $iColCnt Then
$iLastColumn = $iStartColumn + $iColCnt - 1
Else
$iColCnt = $iLastColumn - $iStartColumn + 1
EndIf
; Read data
Local $aArray = $oExcel.ActiveSheet.Range($oExcel.Cells($iStartRow, $iStartColumn), $oExcel.Cells($iLastRow, $iLastColumn)).Value
If Not IsArray($aArray) Then ; single-cell result
Local $avRET[2][2] = [[1, 1],["", $aArray]]
Return $avRET
EndIf
; Convert Col/Row array from Excel to Row/Col
Local $avRET[$iRowCnt + 1][$iColCnt + ($iColCnt = 1 Or $iColShift = 0)] = [[$iRowCnt, $iColCnt]]
For $i = 1 To $iColCnt
For $j = 1 To $iRowCnt
$avRET[$j][$i - $iColShift] = $aArray[$i - 1][$j - 1]
Next
Next
Return $avRET
EndFunc ;==>_ExcelReadSheetToArray

As to the point of it being able to open an Excel file without Excel installed, the size of the difference only applies to a subset of people that would use an Excel function on a spreadsheet. It's nice to have, but I don't see it as a really big difference to those that already have Excel installed, especially if it's slower.

EDIT: And yes, I meant the script in post 8, not 9, mistyped it

Edited by BrewManNH

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

#15 ·  Posted (edited)

NO ehhhhh, as I suspected and as I said above and as a logical (object in autoit, they will never be faster than the same Office ehhhhh, if 2 times faster than Office 2013, was not logical that the__ExcelReadSheetToArray will be 2 times faster than ), __ExcelReadSheetToArray and 3 times more slowly then

#include <Array.au3>
#include "_XLSXReadToArray.au3"
#include <excel.au3>
Local $a1, $b1
InetGet("http://go.microsoft.com/fwlink/?LinkID=245778", @WindowsDir & "TempBulletinSearch.xlsx")
$a1 = TimerInit()
$b1 = _XLSXReadToArray(@WindowsDir & "TempBulletinSearch.xlsx")
$a1 = TimerDiff($a1)
_ArrayDisplay($b1, $a1)
ConsoleWrite("Time1 - " & $a1 & @LF)
$a1 = TimerInit()
$File = _ExcelBookOpen(@WindowsDir & "TempBulletinSearch.xlsx", 0)
$b1 =  __ExcelReadSheetToArray($File)
$a1 = TimerDiff($a1)
_ArrayDisplay($b1, $a1)
ConsoleWrite("Time1 - " & $a1 & @LF)
_ExcelBookClose($File)
Exit
; #FUNCTION# ====================================================================================================================
; Name...........: __ExcelReadSheetToArray
; Description ...: Create a 2D array from the rows/columns of the active worksheet.
; Syntax.........: _ExcelReadSheetToArray($oExcel[, $iStartRow = 1[, $iStartColumn = 1[, $iRowCnt = 0[, $iColCnt = 0]]]])
; Parameters ....: $oExcel - excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;               $iStartRow - Row number to start reading, defaults to 1 (first row)
;               $iStartColumn - Column number to start reading, defaults to 1 (first column)
;               $iRowCnt - Count of rows to read, defaults to 0 (all)
;               $iColCnt - Count of columns to read, defaults to 0 (all)
;               $iColShift - Match R1C1 column position, or start array in column 0. Default is 0 (match R1C1 values)
; Return values .: Success  - Returns a 2D array with the specified cell contents by [$row][$col]
;               Failure  - Returns 0 and sets @error on errors:
;               [email="|@error=1"]|@error=1[/email]  - Specified object does not exist
;               [email="|@error=2"]|@error=2[/email]  - Start parameter out of range
;               [email="|@extended=0"]|@extended=0[/email] - Row out of range
;               [email="|@extended=1"]|@extended=1[/email] - Column out of range
;               [email="|@error=3"]|@error=3[/email] - Count parameter out of range
;               [email="|@extended=0"]|@extended=0[/email] - Row count out of range
;               [email="|@extended=1"]|@extended=1[/email] - Column count out of range
; Author ........: SEO
; Modified.......: PsaltyDS 01/04/08 - 2D version, litlmike - Column shift parm, Golfinhu - Speed enhancement, Spiff59 - Allow protected sheets
; Remarks .......: Returned array has row count in [0][0] and column count in [0][1] (unless
;               Except for the counts above, row 0 and col 0 of the returned array are empty, as actual
;               cell data starts at [1][1] to match R1C1 numbers.
;               By default the entire sheet is returned.
;               If the sheet is empty [0][0] and [0][1] both = 0.
; Related .......:
; Link ..........:
; Example .......: No
; ===============================================================================================================================
Func __ExcelReadSheetToArray($oExcel, $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 0, $iColCnt = 0, $iColShift = 0)
 ; Parameter edits
 If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
 If $iStartRow < 1 Then Return SetError(2, 0, 0)
 If $iStartColumn < 1 Then Return SetError(2, 1, 0)
 If $iRowCnt < 0 Then Return SetError(3, 0, 0)
 If $iColCnt < 0 Then Return SetError(3, 1, 0)
 Local $iLastRow = $oExcel.Activesheet.UsedRange.Rows.Count
 Local $iLastColumn = $oExcel.Activesheet.UsedRange.Columns.Count
 If ($iLastRow + $iLastColumn = 2) And $oExcel.Activesheet.Cells(1, 1).Value = "" Then ; empty result
  Local $avRET[1][2] = [[0, 0]]
  Return $avRET
 EndIf
 ; Parameter edits (continued)
 If $iStartRow > $iLastRow Then Return SetError(2, 0, 0)
 If $iStartColumn > $iLastColumn Then Return SetError(2, 1, 0)
 If $iStartRow + $iRowCnt - 1 > $iLastRow Then Return SetError(3, 0, 0)
 If $iStartColumn + $iColCnt - 1 > $iLastColumn Then Return SetError(3, 1, 0); Check for defaulted counts
 If $iRowCnt Then
  $iLastRow = $iStartRow + $iRowCnt - 1
 Else
  $iRowCnt = $iLastRow - $iStartRow + 1
 EndIf
 If $iColCnt Then
  $iLastColumn = $iStartColumn + $iColCnt - 1
 Else
  $iColCnt = $iLastColumn - $iStartColumn + 1
 EndIf
 ; Read data
 Local $aArray = $oExcel.ActiveSheet.Range($oExcel.Cells($iStartRow, $iStartColumn), $oExcel.Cells($iLastRow, $iLastColumn)).Value
 If Not IsArray($aArray) Then ; single-cell result
  Local $avRET[2][2] = [[1, 1],["", $aArray]]
  Return $avRET
 EndIf
 ; Convert Col/Row array from Excel to Row/Col
 Local $avRET[$iRowCnt + 1][$iColCnt + ($iColCnt = 1 Or $iColShift = 0)] = [[$iRowCnt, $iColCnt]]
 For $i = 1 To $iColCnt
  For $j = 1 To $iRowCnt
   $avRET[$j][$i - $iColShift] = $aArray[$i - 1][$j - 1]
  Next
 Next
 Return $avRET
EndFunc   ;==>_ExcelReadSheetToArray

>"C:\Program Files\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.exe" /run /prod /ErrorStdOut /in "C:UsersDXRW4EDesktoptest.au3" /UserParams

+>16:59:28 Starting AutoIt3Wrapper v.2.1.0.33 Environment(Language:0409 Keyboard:00000409 OS:WIN_8/ CPU:X86 OS:X86)

>Running AU3Check (1.54.22.0) from:C:Program FilesAutoIt3

+>16:59:29 AU3Check ended.rc:0

>Running:(3.3.8.1):C:Program FilesAutoIt3autoit3.exe "C:UsersDXRW4EDesktoptest.au3"

--> Press Ctrl+Alt+F5 to Restart or Ctrl+Break to Stop

Time1 _XLSXReadToArray - 10686.4807752785

Time1 __ExcelReadSheetToArray - 28028.814724749

+>17:01:21 AutoIT3.exe ended.rc:0

>Exit code: 0 Time: 114.286

Edited by DXRW4E

apps-odrive.pngdrive_app_badge.png box-logo.png new_logo.png MEGA_Logo.png

Share this post


Link to post
Share on other sites

#16 ·  Posted (edited)

not to mention after this (Date Posted) look here

Excel 2013

Posted Image

__ExcelReadSheetToArray

Posted Image

_XLSXReadToArray

Posted Image

however all ok, it was only to clarify

the speed is not important, I mean in general important and that one thing work well, after the speed less importance (I also believe the bug "Date" in __ExcelReadSheetToArray i think it will solve quickly and immediately, i think and a small thing, as mentioned above was just to clarify)

Good job to all

Edited by DXRW4E

apps-odrive.pngdrive_app_badge.png box-logo.png new_logo.png MEGA_Logo.png

Share this post


Link to post
Share on other sites

There is a difference in the output from the 2 functions in the dates, but on my computer, running Win7 x64, this is what I get for times.

>Running:(3.3.8.1):E:DropboxAppautoit3.exe "E:DropboxAutoItScriptstest.au3"

--> Press Ctrl+Alt+F5 to Restart or Ctrl+Break to Stop

Time1 - 14395.4843033773

Time1 - 5187.9234554614

+>11:43:01 AutoIT3.exe ended.rc:0

>Exit code: 0 Time: 64.978

>Running:(3.3.8.1):E:DropboxAppautoit3_x64.exe "E:DropboxAutoItScriptstest.au3"

--> Press Ctrl+Alt+F5 to Restart or Ctrl+Break to Stop

Time1 - 12466.4412559892

Time1 - 5485.35816632844

+>11:44:19 AutoIT3.exe ended.rc:0

>Exit code: 0 Time: 45.920

The top one is running autoit as a 32 bit app, the second as a 64 bit app.

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

#18 ·  Posted (edited)

Time1 - 14395.4843033773 or Time1 - 12466.4412559892

I find this really strange, because it is impossible ?? that to me in x86 (in very old and slow pc) and faster that in x64 (Time1 _XLSXReadToArray - 10686.4807752785) hmmmmmmm, maybe something wrong when you do the test

I think (as logical) the REGEXP way and more direct (for this reason and also faster than the office, because it ignores many things and goes directly to the point, read only what you need, because the regexp in Autoit I do not think and faster than of what uses Office2013 ehhhhhhh), and logically normally should be faster than __ExcelReadSheetToArray or the use of Excel Object in Autoit, or so I believe

may be used as a converter (without having office installed), seen that a time had the array, after and very easy to write the file xml or csv ot txt ect ec, or be of help for script like

sorry again for my english

Good job to All

Edited by DXRW4E

apps-odrive.pngdrive_app_badge.png box-logo.png new_logo.png MEGA_Logo.png

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  
Followers 0