Adding elements of an array to a list

[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...

[l3ill]

can you post some more of the surrounding code?

there are several reasons why this wont work:

EndFunc Missing, $IstServerNames not declared, #incudes missing...

[MarcW]

Sure thing.

expandcollapse popup

#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!

[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

[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.

[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 May 14, 2010 by zorphnog

[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...

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 May 14, 2010 by billo

[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!

[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.

[MarcW]

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

[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.

[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.

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

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

[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.

[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?

[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.

[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.

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 May 17, 2010 by GEOSoft

[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

Edited May 17, 2010 by PsaltyDS

[GEOSoft]

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

[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...

[GEOSoft]

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

That should use up a few minutes.