johnnny

dynamical array and loading variables from excel

8 posts in this topic

#1 ·  Posted

Hello, I would like to make my job easier and I was thinking about creating a script in Autoit. But I'm new and I'm still learning.

So may i ask for help?

A description of the first part over which I'm thinking is to load excel with the current cast of cells, which is dynamic, then load each row into the field and then load each column as a variable in each row. I'm not sure if my thinking is correct.

Then I want to paste each variable into a form on the web. Then load the next line and re-enter the form. Do this for selected rows I set, for example when the type is for clothes or for all.

Thank you for helping me!

exapmle of my table:

           
Spoiler
ID type title color price description
1 stuff book green 100      new book
2 clothes t-shirt black 20          bla bla bla
3 stuff car black 250        bla bla bla
4 clothes shirt yellow 20        bla bla bla
5 clothes t-shirt black 30        bli bla blo
6 stuff        
7          
8    

 

my code: 

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

Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, "D:\PRODEJ\seznam.xlsx")

If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & "D:\PRODEJ\seznam.xlsx'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

;$Row = $oExcel.Application.ActivSheet.Row ; this dont work i dont know why
;ConsoleWrite("$Row")

$nb_columns = $oExcel.ActiveSheet.UsedRange.Columns.Count
ConsoleWrite("$nb_columns" & @CRLF) ; i dont see value of this variable in console
$nb_rows = $oExcel.ActiveSheet.UsedRange.Rows.Count
Local $array_data_excel[$nb_rows][$nb_columns]
Local $i
;Local $j
For $i = 1 To $nb_rows - 1 ; start from second row in excel
    ;For $j = 0 To $nb_columns-1 ;dont know how to use this for dynamical loading column
    $array_data_excel[$i][0] = $oExcel.Activesheet.Cells($i + 1, 1).Value
    ;getVar$jValue;here i want to get into variable this
    $array_data_excel[$i][1] = $oExcel.Activesheet.Cells($i + 1, 2).Value
    ;getVard$jValue;here i want to get into variable this
    $array_data_excel[$i][2] = $oExcel.Activesheet.Cells($i + 1, 3).Value
    ;getVar$jValue;here i want to get into variable this
    $array_data_excel[$i][3] = $oExcel.Activesheet.Cells($i + 1, 4).Value
    ;getVar$jValue;here i want to get into variable this
    $array_data_excel[$i][4] = $oExcel.Activesheet.Cells($i + 1, 5).Value
    ;getVar$jValue;here i want to get into variable this
    $array_data_excel[$i][5] = $oExcel.Activesheet.Cells($i + 1, 6).Value
    ;getVar$jValue;here i want to get into variable this

    ;Display for Testing
    _ArrayDisplay($array_data_excel)

Next
;Display for Testing
_ArrayDisplay($array_data_excel)


_Excel_Close($oExcel, True)
_Excel_BookClose($oWorkbook, True)

 

Share this post


Link to post
Share on other sites



#2 ·  Posted

If you just need an array of the full sheet you can use _Excel_RangeRead() to return the full array. The consolewrite doesn't show the value because you have enclosed the variable in double quotes. This just displays as text then.

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

Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, "D:\test.xlsx")

If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & "D:\test.xlsx'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

;$Row = $oExcel.Application.ActivSheet.Row ; this dont work i dont know why
;ConsoleWrite("$Row")

Local $as_RangeRead = _Excel_RangeRead($oWorkbook)

;~ $nb_columns = $oExcel.ActiveSheet.UsedRange.Columns.Count
;~ ConsoleWrite($nb_columns & @CRLF) ; i dont see value of this variable in console
;~ $nb_rows = $oExcel.ActiveSheet.UsedRange.Rows.Count
;~ Local $array_data_excel[$nb_rows][$nb_columns]
;~ Local $i
;~ ;Local $j
;~ For $i = 1 To $nb_rows - 1 ; start from second row in excel
;~  ;For $j = 0 To $nb_columns-1 ;dont know how to use this for dynamical loading column
;~  $array_data_excel[$i][0] = $oExcel.Activesheet.Cells($i + 1, 1).Value
;~  ;getVar$jValue;here i want to get into variable this
;~  $array_data_excel[$i][1] = $oExcel.Activesheet.Cells($i + 1, 2).Value
;~  ;getVard$jValue;here i want to get into variable this
;~  $array_data_excel[$i][2] = $oExcel.Activesheet.Cells($i + 1, 3).Value
;~  ;getVar$jValue;here i want to get into variable this
;~  $array_data_excel[$i][3] = $oExcel.Activesheet.Cells($i + 1, 4).Value
;~  ;getVar$jValue;here i want to get into variable this
;~  $array_data_excel[$i][4] = $oExcel.Activesheet.Cells($i + 1, 5).Value
;~  ;getVar$jValue;here i want to get into variable this
;~  $array_data_excel[$i][5] = $oExcel.Activesheet.Cells($i + 1, 6).Value
;~  ;getVar$jValue;here i want to get into variable this

;~  ;Display for Testing
;~  _ArrayDisplay($array_data_excel)

;~ Next
;Display for Testing
_ArrayDisplay($as_RangeRead)
;~ _ArrayDisplay($array_data_excel)

_Excel_BookClose($oWorkbook, True)
_Excel_Close($oExcel, True)

 

Share this post


Link to post
Share on other sites

#3 ·  Posted

Thank you for your insert. 

But, i am not sure if i need array of all asheet in this point. I need work with every row in loop and all field in row get into different variables for next work with them.

and this does not work for me as well:

ConsoleWrite('nb_columns' & @CRLF) ; i dont see value of this variable in console

Thank you

Share this post


Link to post
Share on other sites

#4 ·  Posted

2 hours ago, johnnny said:

I need work with every row in loop and all field in row

Sounds like the full sheet to me.

2 hours ago, johnnny said:

and this does not work for me as well:

ConsoleWrite('nb_columns' & @CRLF) ; i dont see value of this variable in console

I know, I explained why in my first post.

IMO it is better to call RangeRead once and then process the values you want from the array than use multiple calls for each row\column. The code is cleaner than nested for loops

Based on the sheet in your post and the info provided (and without too much thinking), I would start with something like this, unless I'm not understanding your requirements fully.

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

; set constants for the columns that RangeRead returns
; if your final sheet is different from the one you added in your
; post these will need to be changed to represent the columns you want
Global Enum _
        $ID_COL, _
        $TYPE_COL, _
        $TITLE_COL, _
        $COLOR_COL, _
        $PRICE_COL, _
        $DESCRIPTION_COL

Local $oExcel = _Excel_Open()
If @error Then Close("Error creating the Excel application object.", @error, @extended)

Local $oWorkbook = _Excel_BookOpen($oExcel, "D:\test.xlsx") ; replace with your file path
If @error Then Close("Error opening workbook '" & "D:\test.xlsx'.", @error, @extended)

; read the full sheet to an array
Local $as_RangeRead = _Excel_RangeRead($oWorkbook)
If @error Then Close("Error reading range to an array", @error, @extended)

; example of getting the values from the array and desired column
RangeReadArray_GetValues($as_RangeRead, $DESCRIPTION_COL)

Close()

; example to get each field value
Func RangeReadArray_GetValues(ByRef $as_Array, $i_Column)
    For $i = 1 To UBound($as_Array) - 1
        MsgBox(0, 'Row: ' & $i + 1 & ' Column: ' & $i_Column + 1, 'Value: ' & $as_Array[$i][$i_Column])
    Next
EndFunc   ;==>RangeReadArray_GetValues

Func Close($s_ErrorMsg = '', $i_Error = 0, $i_Extended = 0)
    If $s_ErrorMsg Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", _
            $s_ErrorMsg & @CRLF & _
            "@error = " & $i_Error & ", @extended = " & $i_Extended)

    _Excel_BookClose($oWorkbook, True)
    _Excel_Close($oExcel, True)
    Exit
EndFunc   ;==>Close

 

Share this post


Link to post
Share on other sites

#5 ·  Posted

5 hours ago, benners said:

Sounds like the full sheet to me.

I need rows, cause i will work with rows. Sometimes will need only one row, sometimes range of rows. I want to have it adjustable.

Quote

I know, I explained why in my first post.

i tryed double quotes, simple quotes. Nothing works for me. :( 

Quote

IMO it is better to call RangeRead once and then process the values you want from the array than use multiple calls for each row\column. The code is cleaner than nested for loops

agreed 

Quote

Based on the sheet in your post and the info provided (and without too much thinking), I would start with something like this, unless I'm not understanding your requirements fully.

i see, its look so better then my. I just wanted rows, not columns. Tryed to do for rows, but i am little confused and dont uderstand very well $DESCRIPTION_COL variable. I guess, that if i do something like this:  ... i will get first column, but no row.

RangeReadArray_GetValues($as_RangeRead, $DESCRIPTION_COL = 0)

I will try describe my problem more...

For example i have row in my excel and form on website where i want to put these fields. In one loop just get one row from excel and fill form on web from these variables. Or for second row or third row depence what i will need. In the future there will be about 50 rows, and i dont want to fill form manualy. So my first thougt was get excel values for each row into separate variables. 

Can you just please do this for rows and for two maybe? I can see the logical step better maybe. 

Thank you very much

Share this post


Link to post
Share on other sites

#6 ·  Posted

3 hours ago, johnnny said:

i tryed double quotes, simple quotes. Nothing works for me

To get the ConsoleWrite to work with the variable, you remove any quotes, single or double from around it or it just gets written as text and not the value that the variable holds.

ConsoleWrite($nb_columns & @CRLF)

Now as to the row selection. This works. It looks a lot because I added a gui so you can select the rows that are available in the array. I don't know how you were going to select the rows so just made a way up

#include <Array.au3>
#include <ButtonConstants.au3>
#include <Excel.au3>
#include <GUIConstantsEx.au3>
#include <GuiListView.au3>
#include <ListViewConstants.au3>
#include <MsgBoxConstants.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>

Opt("GUIOnEventMode", 1)

Global $g_idListview

Global $g_oExcel = _Excel_Open()
If @error Then Close("Error creating the Excel application object.", @error, @extended)

Global $g_oWorkbook = _Excel_BookOpen($g_oExcel, "D:\test.xlsx") ; replace with your file path
If @error Then Close("Error opening workbook '" & "D:\test.xlsx'.", @error, @extended)

; read the full sheet to an array
Global $g_as_RangeRead = _Excel_RangeRead($g_oWorkbook)
If @error Then Close("Error reading range to an array", @error, @extended)

Draw_GUI() ; draw a gui to allow row selection

Func Draw_GUI()
    GUICreate("", 213, 265, 1011, 460)
    GUISetOnEvent($GUI_EVENT_CLOSE, 'Close_Clicked')

    $g_idListview = GUICtrlCreateListView("", 24, 40, 58, 206, $LVS_SMALLICON, BitOR($WS_EX_CLIENTEDGE, $LVS_EX_CHECKBOXES))
    Listview_Load() ; load the listview with the row numbers

    GUICtrlCreateButton("OK", 104, 56, 75, 25)
    GUICtrlSetOnEvent(-1, 'OK_Clicked')

    GUICtrlCreateButton("Close", 104, 88, 75, 25)
    GUICtrlSetOnEvent(-1, 'Close_Clicked')

    GUICtrlCreateLabel("Select the ID rows", 10, 8, 129, 20, $SS_CENTER)
    GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")

    GUISetState(@SW_SHOW)

    While 1
        Sleep(100)
    WEnd
EndFunc   ;==>Draw_GUI

Func Listview_Load()
    ; load the ID column into the listview
    For $i = 1 To UBound($g_as_RangeRead) - 1
        GUICtrlCreateListViewItem($g_as_RangeRead[$i][0], $g_idListview) ; add an item to the listview for each row in the array
    Next
EndFunc   ;==>Listview_Load

Func OK_Clicked()
    Local $s_Row = ''

    ; get the rows that you want to get the values for
    For $i = 1 To _GUICtrlListView_GetItemCount($g_idListview)
        If _GUICtrlListView_GetItemChecked($g_idListview, $i - 1) Then $s_Row &= _GUICtrlListView_GetItemText($g_idListview, $i - 1) & '|'
    Next

    $s_Row = StringTrimRight($s_Row, 1) ; remove the trailing delimiter

    ; get the array values for the selected rows
    RangeReadArray_GetValues(StringSplit($s_Row, '|')) ; send an array of the selected rows to the function
EndFunc   ;==>OK_Clicked

Func RangeReadArray_GetValues($as_Row)
    Local $i_Row = 0

    ; loop through the array getting the column values for the selected rows
    For $i = 1 To $as_Row[0] ; loop through the array of rows you selected
        $i_Row = $as_Row[$i] ; this is the current row you are reading it is the ID number NOT the Excel row number

        For $j = 1 To UBound($g_as_RangeRead, $UBOUND_COLUMNS) - 1 ; loop thropugh the rangeread array columns ignoring the ID column
            ; show the values. When you write your code for entering into the web page put it here. $g_as_RangeRead[$i][$j] is the column value
            MsgBox(0, 'Row: ' & $i_Row, $g_as_RangeRead[0][$j] & ': ' & $g_as_RangeRead[$i_Row][$j])
        Next

        Sleep(20)
    Next
EndFunc   ;==>RangeReadArray_GetValues

Func Close_Clicked()
    Close()
EndFunc   ;==>Close_Clicked

Func Close($s_ErrorMsg = '', $i_Error = 0, $i_Extended = 0)
    GUIDelete()

    If $s_ErrorMsg Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", _
            $s_ErrorMsg & @CRLF & _
            "@error = " & $i_Error & ", @extended = " & $i_Extended)

    _Excel_BookClose($g_oWorkbook, True)
    _Excel_Close($g_oExcel, True)

    Exit
EndFunc   ;==>Close

 

Share this post


Link to post
Share on other sites

#7 ·  Posted

12 minutes ago, benners said:

To get the ConsoleWrite to work with the variable, you remove any quotes, single or double from around it or it just gets written as text and not the value that the variable holds.

ConsoleWrite($nb_columns & @CRLF)

Now as to the row selection. This works. It looks a lot because I added a gui so you can select the rows that are available in the array. I don't know how you were going to select the rows so just made a way up

#include <Array.au3>
#include <ButtonConstants.au3>
#include <Excel.au3>
#include <GUIConstantsEx.au3>
#include <GuiListView.au3>
#include <ListViewConstants.au3>
#include <MsgBoxConstants.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>

Opt("GUIOnEventMode", 1)

Global $g_idListview

Global $g_oExcel = _Excel_Open()
If @error Then Close("Error creating the Excel application object.", @error, @extended)

Global $g_oWorkbook = _Excel_BookOpen($g_oExcel, "D:\test.xlsx") ; replace with your file path
If @error Then Close("Error opening workbook '" & "D:\test.xlsx'.", @error, @extended)

; read the full sheet to an array
Global $g_as_RangeRead = _Excel_RangeRead($g_oWorkbook)
If @error Then Close("Error reading range to an array", @error, @extended)

Draw_GUI() ; draw a gui to allow row selection

Func Draw_GUI()
    GUICreate("", 213, 265, 1011, 460)
    GUISetOnEvent($GUI_EVENT_CLOSE, 'Close_Clicked')

    $g_idListview = GUICtrlCreateListView("", 24, 40, 58, 206, $LVS_SMALLICON, BitOR($WS_EX_CLIENTEDGE, $LVS_EX_CHECKBOXES))
    Listview_Load() ; load the listview with the row numbers

    GUICtrlCreateButton("OK", 104, 56, 75, 25)
    GUICtrlSetOnEvent(-1, 'OK_Clicked')

    GUICtrlCreateButton("Close", 104, 88, 75, 25)
    GUICtrlSetOnEvent(-1, 'Close_Clicked')

    GUICtrlCreateLabel("Select the ID rows", 10, 8, 129, 20, $SS_CENTER)
    GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")

    GUISetState(@SW_SHOW)

    While 1
        Sleep(100)
    WEnd
EndFunc   ;==>Draw_GUI

Func Listview_Load()
    ; load the ID column into the listview
    For $i = 1 To UBound($g_as_RangeRead) - 1
        GUICtrlCreateListViewItem($g_as_RangeRead[$i][0], $g_idListview) ; add an item to the listview for each row in the array
    Next
EndFunc   ;==>Listview_Load

Func OK_Clicked()
    Local $s_Row = ''

    ; get the rows that you want to get the values for
    For $i = 1 To _GUICtrlListView_GetItemCount($g_idListview)
        If _GUICtrlListView_GetItemChecked($g_idListview, $i - 1) Then $s_Row &= _GUICtrlListView_GetItemText($g_idListview, $i - 1) & '|'
    Next

    $s_Row = StringTrimRight($s_Row, 1) ; remove the trailing delimiter

    ; get the array values for the selected rows
    RangeReadArray_GetValues(StringSplit($s_Row, '|')) ; send an array of the selected rows to the function
EndFunc   ;==>OK_Clicked

Func RangeReadArray_GetValues($as_Row)
    Local $i_Row = 0

    ; loop through the array getting the column values for the selected rows
    For $i = 1 To $as_Row[0] ; loop through the array of rows you selected
        $i_Row = $as_Row[$i] ; this is the current row you are reading it is the ID number NOT the Excel row number

        For $j = 1 To UBound($g_as_RangeRead, $UBOUND_COLUMNS) - 1 ; loop thropugh the rangeread array columns ignoring the ID column
            ; show the values. When you write your code for entering into the web page put it here. $g_as_RangeRead[$i][$j] is the column value
            MsgBox(0, 'Row: ' & $i_Row, $g_as_RangeRead[0][$j] & ': ' & $g_as_RangeRead[$i_Row][$j])
        Next

        Sleep(20)
    Next
EndFunc   ;==>RangeReadArray_GetValues

Func Close_Clicked()
    Close()
EndFunc   ;==>Close_Clicked

Func Close($s_ErrorMsg = '', $i_Error = 0, $i_Extended = 0)
    GUIDelete()

    If $s_ErrorMsg Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", _
            $s_ErrorMsg & @CRLF & _
            "@error = " & $i_Error & ", @extended = " & $i_Extended)

    _Excel_BookClose($g_oWorkbook, True)
    _Excel_Close($g_oExcel, True)

    Exit
EndFunc   ;==>Close

 

You are awesome!
Thank you for this moment. I will continue and let you know later.
Nice rest of weekend
 

Share this post


Link to post
Share on other sites

#8 ·  Posted

No worries. Post back if there's any issues

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