Jump to content
Sign in to follow this  
MarcW

Adding elements of an array to a list

Recommended Posts

MarcW

I'm trying to add the row n, col 1 elements of an array to a list control. I'm not sure which is the best method, GUICtrlListViewAddItem or GUICtrlListViewAddArray, or even how to properly implement. Example code:

Func _ImportServers_Clicked()
    Local $sFileToOpen = ""
    Local $oExcel = ""
    
    $sFileToOpen = FileOpenDialog ("Select a file", "C:\", "Excel files(*.xlsx)")
    $oExcel = _ExcelBookOpen ($sFileToOpen, 0)
    $aServerInfo = _ExcelReadSheetToArray ($oExcel, 1, 1, 0, 0, False)
    _GUICtrlListView_AddArray($lstServerNames, $aServerInfo) <- Doesn't work...

Thank you in advance...

Share this post


Link to post
Share on other sites
MarcW

Sure thing.

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <GUIListBox.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <Excel.au3>
#Include <Array.au3>
#include <GuiListView.au3>

Opt("GUIOnEventMode", 1)
Opt("ExpandEnvStrings", 1)
Opt("GUIDataSeparatorChar", "|")

$lstServerNames = GUICtrlCreateList("", 23, 22, 141, 518)
    GUICtrlSetData(-1, "")
    GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")

;Globals

Global $aServerInfo[1][1]

;Functions

Func _ImportServers_CLICKED()
    Local $sFileToOpen = ""
    Local $oExcel = ""
    Local $sRows = ""
    Local $sCols = ""
        
    $sFileToOpen = FileOpenDialog ("Select a file", "C:\", "Excel files(*.xlsx)")
    $oExcel = _ExcelBookOpen ($sFileToOpen, 0)
    $aServerInfo = _ExcelReadSheetToArray ($oExcel, 1, 1, 0, 0, False)
    _GUICtrlListView_AddArray($lstServerNames, $aServerInfo)
    ;GUICtrlSetData($lstServerNames, $aServerInfo[3][3] & "|" & $aServerInfo[4][4])
    ;_ArrayDisplay($aServerInfo)
    _ExcelBookClose($oExcel, 0, 0)

EndFunc

Thanks for taking the time to help out!

Share this post


Link to post
Share on other sites
l3ill

Is the N:1 elements you want to add located in the excel sheet you are trying to open?

Does this code do anything on your PC? File Open Dialog for instance.

Basically, are your trying to add an Excel cell to a list view?

Bill

Share this post


Link to post
Share on other sites
MarcW

Yes. I have an excel sheet with a list of server names that need to be added to the list control. The number of names is never constant. Currently, I can run the script, the gui will appear, I select file>open, select the excel sheet which is then stored in an array, and I can display the array, so the rest of the code works. Its just getting the row n col 1 elements into the list control that is giving me an issue.

Share this post


Link to post
Share on other sites
zorphnog

A List and ListView are two totally different things. In your code you create a List and then try to populate it using a ListView function. So which one is it that you want, List or ListView?

Also, you are reading the entire excel sheet into an array. If you only want a certain column or row then you need to specify that. We can't help you if you don't provide more details about what it is you are trying to do.

Edit: You do realize that saying row n column 1 an exact cell and not a column or row? Do you want the elements in row n or column 1 (which by the way is backwards, rows are numbers; columns are letters)?

Edited by zorphnog

Share this post


Link to post
Share on other sites
l3ill

It seems that your script is far more complicated than what you are trying to accomplish.

Why not just Open your excel and read the cell to the list you need in a notepad or such.

Or am I on the wrong trolley...:idea:

Edit: You do realize that saying row n column 1 an exact cell and not a column or row? Do you want the elements in row n or column 1 (which by the way is backwards, rows are numbers; columns are letters)?

and here I am trying to ignore that ;-)

Edited by billo

Share this post


Link to post
Share on other sites
MarcW

Which one do I want, a list or a listview?

Well I've created the list control in the GUI, so I'm guessing a list, although to be honest I'm not sure of the difference.

"Also, you are reading the entire excel sheet into an array. If you only want a certain column or row then you need to specify that. We can't help you if you don't provide more details about what it is you are trying to do. "

I need ALL of column B (r1,cb = server1, r2,cb = server2, etc...), although how many non blank cells there will be will vary.

"It seems that your script is far more complicated than what you are trying to accomplish. Why not just Open your excel and read the cell to the list you need in a notepad or such."

The excell SS contains more than just that one column. It also contains ip address, nic 1 macs, nic 2 macs, etc..., for a total of 18 different columns, all of which will be utilized at one point or another. If storing all the information in an array is not the best method, then please enlighten me.

Thank you all so much for your help and guidance!

Share this post


Link to post
Share on other sites
PsaltyDS

A ListView can have multiple columns and more complex attributes for the items (like checkboxes and icons). If you want to make a simple list of a single column, then a List makes more sense than a ListView.

Read all of column B with:

$aColB = _ExcelReadSheetToArray($oExcel, 1, 2, 0, 1)

The results will be in $aColB[1][1] Thru $aColB[1][n], where n = the last used row.

You can also read one column with _ExcelReadArray(), but it requires you to specify how many rows, while _ExcelReadSheetToArray() will determine the last used row for itself.

:idea:


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
MarcW

Ok, that clears some things up, but I'm still not sure how to add each element to the $lstServerInfo list...

Share this post


Link to post
Share on other sites
PsaltyDS

Ok, that clears some things up, but I'm still not sure how to add each element to the $lstServerInfo list...

You have to decide if it's a ListBox (plain List control) or a ListView first.

For a ListBox you would just walk through the array of Excel data and add each one with GuiCtrlSetData() or _GUICtrlListBox_AddString().

For a ListView walk the array add each item with GuiCtrlSetData() or _GUICtrlListView_AddItem(). With the ListView there is a function to add an array of items in one pass with _GUICtrlListView_AddArray(), but the arrays returned by the _Excel* functions are not formatted correctly to use that way. You would have to rewrite the array in the correct format first.

:idea:


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
GEOSoft

You have to decide if it's a ListBox (plain List control) or a ListView first.

For a ListBox you would just walk through the array of Excel data and add each one with GuiCtrlSetData() or _GUICtrlListBox_AddString().

For a ListView walk the array add each item with GuiCtrlSetData() or _GUICtrlListView_AddItem(). With the ListView there is a function to add an array of items in one pass with _GUICtrlListView_AddArray(), but the arrays returned by the _Excel* functions are not formatted correctly to use that way. You would have to rewrite the array in the correct format first.

:idea:

For a ListBox, I think it's easier to use

#include<array.au3>
GUICtrlSetData($hListBox, _ArrayToString($myArray))

George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

Share this post


Link to post
Share on other sites
PsaltyDS

For a ListBox, I think it's easier to use

#include<array.au3>
GUICtrlSetData($hListBox, _ArrayToString($myArray))
Leaves him with the same problem if $myArray came from _ExcelReadSheetToArray()... the array format doesn't work without translation.

:idea:


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
GEOSoft

I've never toyed with the Excel UDF but the way I read the help file, it just generates a multi-dimension array which could be used with a ListView OR a single dimension array which could be used with the ListBox. What's the difference in the returned array that makes it so non-standard?


George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

Share this post


Link to post
Share on other sites
PsaltyDS

I've never toyed with the Excel UDF but the way I read the help file, it just generates a multi-dimension array which could be used with a ListView OR a single dimension array which could be used with the ListBox. What's the difference in the returned array that makes it so non-standard?

The _ExcelReadArray() function returns a simple 1D array that would work with your technique, but it requires you to specify the count.

The proposed solution was to use _ExcelReadSheetToArray(), which will determine last row used and return the correct sized array on its own. The problem there is that function returns a 2D array, which won't work with what you were doing.

:idea:


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
GEOSoft

The _ExcelReadArray() function returns a simple 1D array that would work with your technique, but it requires you to specify the count.

The proposed solution was to use _ExcelReadSheetToArray(), which will determine last row used and return the correct sized array on its own. The problem there is that function returns a 2D array, which won't work with what you were doing.

:idea:

Okay, I think I see what you mean now. There is no function to get the last used row of a particular column on the sheet so you don't know what to enter for $iNumCells.

What would happen if you used

_ExcelReadSheetToArray($oExcel , 1, 1, 0, 1)

Wouldn't that return a single dimension array of just that column?

Edited by GEOSoft

George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

Share this post


Link to post
Share on other sites
PsaltyDS

What would happen if you used

_ExcelReadSheetToArray($oExcel , 1, 1, 0, 1)

Wouldn't that return a single dimension array of just that column?

#include <Excel.au3>
#include <Array.au3>

Local $oExcel = _ExcelBookNew()

For $r = 1 To 10
    For $c = 1 To 10
        _ExcelWriteCell($oExcel, $r & " -- " & $c, $r, $c)
    Next
Next

$aArray1 = _ExcelReadArray($oExcel, 1, 1, 5, 1) ; Direction is Vertical
$aArray2 = _ExcelReadArray($oExcel, 1, 3, 5) ; Direction is Horizontal
$aArray3 = _ExcelReadSheetToArray($oExcel) ; Read whole sheet
$aArray4 = _ExcelReadSheetToArray($oExcel, 1, 1, 0, 1) ; Read all of col.1

_ArrayDisplay($aArray1, "Vertical")
_ArrayDisplay($aArray2, "Horizontal")
_ArrayDisplay($aArray3, "Whole Sheet")
_ArrayDisplay($aArray4, "Col. 1")

_ExcelBookClose($oExcel)

No, the function _ExcelReadSheetToArray() was designed to return the whole sheet by default, and at the time I thought starting from row 1 and column 1 made more sense because the 2D array indexes would then match the 1-based row/col numbers. So all of row 0 and col 0 is empty, except the row/col counts. It looks awkward to me now, but seemed like a good idea at the time. Your _ExcelReadSheetToArray($oExcel, 1, 1, 0, 1) in the script above results in a 2D array like this:

...     [0] [1]
[0]     10  1
[1]         1 -- 1
[2]         2 -- 1
[3]         3 -- 1
[4]         4 -- 1
[5]         5 -- 1
[6]         6 -- 1
[7]         7 -- 1
[8]         8 -- 1
[9]         9 -- 1
[10]        10 -- 1

:idea:

Edited by PsaltyDS

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
GEOSoft

First guess, the Excel UDF needs some added functionality like _ExcelReadColumnToArray() and _Excel_ReadRowToArray()


George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

Share this post


Link to post
Share on other sites
PsaltyDS

First guess, the Excel UDF needs some added functionality like _ExcelReadColumnToArray() and _Excel_ReadRowToArray()

Just needs the check for max used cell added to the existing _ExcelReadArray(). Been planning to get around to that for awhile...

:idea:


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
GEOSoft

Just needs the check for max used cell added to the existing _ExcelReadArray(). Been planning to get around to that for awhile...

:idea:

That should use up a few minutes.

George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

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  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.