Peggy

how to detect excel into Provisions array

1 post in this topic

7.thumb.jpg.a73ee017bef13d6114b7ffc47b3b

 

I have a excel file(),original Variable as below:

 

$aData[][] = [["Mary", 5, "Apple", "Banana", "Tomato", "Watermelon", "Strawberry"], _
              ["Sam", 3, "Apple", "Tomato", "Strawberry"], _
              ["Jack", 3, "Banana", "Watermelon", "Strawberry"]]

I want to use one Variable  to represent each Variable  as below:(Easy to expand the number)


$aData[][] = [$Name, $amount , $fruit]

 

But I don't know how to code,It really baffles me,I want to ask for your assistance~~
Thank you very very much!!!!!!!!!!
:D

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

  • Similar Content

    • Rimoun
      By Rimoun
      Hello everyone
      I would like to have some help regarding my issue.
      I am trying to extract some information from excel sheet, I use _Excel_RangeFind then I get the array for the value then I check the array. In case the array is true the next will be to get some information based on the extracted array if no array because the value is not found it gives a messages box that the value is not found.
      My problem that the button works for one time only if i tried any value which exist in the sheet it gives me that value not found.
      I suspect that there is a problem regarding While loop.
      here is my code
      #include <GUIConstantsEx.au3> #include <WinAPI.au3> #Include <GuiListBox.au3> #include <WindowsConstants.au3> #include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> #include <ExcelConstants.au3> if FileExists ("result.txt") Then Sleep (100) Else readxl() EndIf Global $oExcel = _Excel_Open(False,False) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Global $oWorkbook = _Excel_BookOpen($oExcel, "D:\info.xlsx",False,False) If @error Then MsgBox($MB_SYSTEMMODAL, "Excel Error", "Error opening workbook '" & $oWorkbook & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oExcel) Exit EndIf Local $lab1 = _Excel_RangeRead($oWorkbook, Default,"B1") Local $lab2 = _Excel_RangeRead($oWorkbook, Default,"C1") Local $lab3 = _Excel_RangeRead($oWorkbook, Default,"D1") Local $lab4 = _Excel_RangeRead($oWorkbook, Default,"E1") Global $sResult1,$sResult2,$sResult3,$sResult4,$sResult5,$sResult6 Global $asKeyWords = stringsplit (FileRead (@ScriptDir & "\result.txt"),@CRLF) Global Const $xlUp = -4162 ;~ _Main() Local $hGUI, $hList, $hInput, $aSelected, $sChosen, $hUP, $hDOWN, $hENTER, $hESC Local $sCurrInput = "", $aCurrSelected[2] = [-1, -1], $iCurrIndex = -1, $hListGUI = -1 $hGUI = GUICreate("Rimo System", 253, 270, 192, 124) Global $hInput = GUICtrlCreateInput("", 24, 48, 169, 21) Global $Label1 = GUICtrlCreateLabel("Rimo System", 80, 16, 150, 25) GUICtrlSetFont(-1, 14, 800, 0, "MS Serif") Global $Input2 = GUICtrlCreateInput("", 72, 144, 161, 21) Global $Input3 = GUICtrlCreateInput("", 72, 176, 161, 21) Global $Input4 = GUICtrlCreateInput("", 72, 208, 161, 21) Global $Input5 = GUICtrlCreateInput("", 72, 240, 161, 21) $Input6 = GUICtrlCreateInput("", 72, 272, 161, 21) $Input7 = GUICtrlCreateInput("", 72, 304, 161, 21) $Button1 = GUICtrlCreateButton("Get Info", 72, 88, 89, 33) $Label2 = GUICtrlCreateLabel("Label2", 16, 144, 36, 17) GUICtrlSetData( -1,$lab1) $Label3 = GUICtrlCreateLabel("Label3", 16, 176, 36, 17) GUICtrlSetData( -1,$lab2) $Label4 = GUICtrlCreateLabel("Label4", 16, 208, 36, 17) GUICtrlSetData( -1,$lab3) $Label5 = GUICtrlCreateLabel("Label5", 16, 240, 36, 17) GUICtrlSetData( -1,$lab4) $Label6 = GUICtrlCreateLabel("", 16, 272, 36, 17) $Label7 = GUICtrlCreateLabel("", 16, 304, 36, 17) $Button2 = GUICtrlCreateButton("Cancel", 112, 416, 121, 25) GUISetState(@SW_SHOW, $hGUI) Global $sSearch = guictrlread ($hInput) $hUP = GUICtrlCreateDummy() $hDOWN = GUICtrlCreateDummy() $hENTER = GUICtrlCreateDummy() $hESC = GUICtrlCreateDummy() Dim $AccelKeys[4][2] = [["{UP}", $hUP], ["{DOWN}", $hDOWN], ["{ENTER}", $hENTER], ["{ESC}", $hESC]] GUISetAccelerators($AccelKeys) While 1 Switch GUIGetMsg() Case $GUI_EVENT_CLOSE ExitLoop Case $Button2 Exit Case $Button1 Global $aResult = _Excel_RangeFind($oWorkbook, guictrlread($hInput) ,"A2:A2000") Global $aExtract = _ArrayExtract($aResult, 0, 0, 2, 2) if _elementExists($aExtract,0) Then getdata() Else MsgBox(0,"","Value Does Not Exist") EndIf Case $hESC If $hListGUI <> -1 Then ; List is visible. GUIDelete($hListGUI) $hListGUI = -1 Else ExitLoop EndIf Case $hUP If $hListGUI <> -1 Then ; List is visible. $iCurrIndex -= 1 If $iCurrIndex < 0 Then $iCurrIndex = 0 EndIf _GUICtrlListBox_SetCurSel($hList, $iCurrIndex) EndIf Case $hDOWN If $hListGUI <> -1 Then ; List is visible. $iCurrIndex += 1 If $iCurrIndex > _GUICtrlListBox_GetCount($hList) - 1 Then $iCurrIndex = _GUICtrlListBox_GetCount($hList) - 1 EndIf _GUICtrlListBox_SetCurSel($hList, $iCurrIndex) EndIf Case $hENTER If $hListGUI <> -1 And $iCurrIndex <> -1 Then ; List is visible and a item is selected. $sChosen = _GUICtrlListBox_GetText($hList, $iCurrIndex) EndIf Case $hList $sChosen = GUICtrlRead($hList) EndSwitch Sleep(10) $aSelected = _GetSelectionPointers($hInput) If GUICtrlRead($hInput) <> $sCurrInput Or $aSelected[1] <> $aCurrSelected[1] Then ; Input content or pointer are changed. $sCurrInput = GUICtrlRead($hInput) $aCurrSelected = $aSelected ; Get pointers of the string to replace. $iCurrIndex = -1 If $hListGUI <> -1 Then ; List is visible. GUIDelete($hListGUI) $hListGUI = -1 EndIf $hList = _PopupSelector($hGUI, $hListGUI, _CheckInputText($sCurrInput, $aCurrSelected)) ; ByRef $hListGUI, $aCurrSelected. EndIf If $sChosen <> "" Then GUICtrlSendMsg($hInput, 0x00B1, $aCurrSelected[0], $aCurrSelected[1]) ; $EM_SETSEL. _InsertText($hInput, $sChosen) $sCurrInput = GUICtrlRead($hInput) GUIDelete($hListGUI) $hListGUI = -1 $sChosen = "" EndIf WEnd GUIDelete($hGUI) Func _CheckInputText($sCurrInput, ByRef $aSelected) Local $sPartialData = "" If (IsArray($aSelected)) And ($aSelected[0] <= $aSelected[1]) Then Local $aSplit = StringSplit(StringLeft($sCurrInput, $aSelected[0]), " ") $aSelected[0] -= StringLen($aSplit[$aSplit[0]]) If $aSplit[$aSplit[0]] <> "" Then For $A = 1 To $asKeyWords[0] If StringLeft($asKeyWords[$A], StringLen($aSplit[$aSplit[0]])) = $aSplit[$aSplit[0]] And $asKeyWords[$A] <> $aSplit[$aSplit[0]] Then $sPartialData &= $asKeyWords[$A] & "|" EndIf Next EndIf EndIf Return $sPartialData EndFunc ;==>_CheckInputText Func _PopupSelector($hMainGUI, ByRef $hListGUI, $sCurr_List) Local $hList = -1 If $sCurr_List = "" Then Return $hList EndIf $hListGUI = GUICreate("", 280, 160, 23, 62, $WS_POPUP, BitOR($WS_EX_TOOLWINDOW, $WS_EX_TOPMOST, $WS_EX_MDICHILD), $hMainGUI) $hList = GUICtrlCreateList("", 0, 0, 170, 150, BitOR(0x00100000, 0x00200000)) GUICtrlSetData($hList, $sCurr_List) GUISetControlsVisible($hListGUI) ; To Make Control Visible And Window Invisible. GUISetState(@SW_SHOWNOACTIVATE, $hListGUI) Return $hList EndFunc ;==>_PopupSelector Func _InsertText(ByRef $hEdit, $sString) #cs Description: Insert A Text In A Control. Returns: Nothing #ce Local $aSelected = _GetSelectionPointers($hEdit) GUICtrlSetData($hEdit, StringLeft(GUICtrlRead($hEdit), $aSelected[0]) & $sString & StringTrimLeft(GUICtrlRead($hEdit), $aSelected[1])) Local $iCursorPlace = StringLen(StringLeft(GUICtrlRead($hEdit), $aSelected[0]) & $sString) GUICtrlSendMsg($hEdit, 0x00B1, $iCursorPlace, $iCursorPlace) ; $EM_SETSEL. EndFunc ;==>_InsertText Func _GetSelectionPointers($hEdit) Local $aReturn[2] = [0, 0] Local $aSelected = GUICtrlRecvMsg($hEdit, 0x00B0) ; $EM_GETSEL. If IsArray($aSelected) Then $aReturn[0] = $aSelected[0] $aReturn[1] = $aSelected[1] EndIf Return $aReturn EndFunc ;==>_GetSelectionPointers Func GUISetControlsVisible($hWnd) ; By Melba23. Local $aControlGetPos = 0, $hCreateRect = 0, $hRectRgn = _WinAPI_CreateRectRgn(0, 0, 0, 0) Local $iLastControlID = _WinAPI_GetDlgCtrlID(GUICtrlGetHandle(-1)) For $i = 3 To $iLastControlID $aControlGetPos = ControlGetPos($hWnd, '', $i) If IsArray($aControlGetPos) = 0 Then ContinueLoop $hCreateRect = _WinAPI_CreateRectRgn($aControlGetPos[0], $aControlGetPos[1], $aControlGetPos[0] + $aControlGetPos[2], $aControlGetPos[1] + $aControlGetPos[3]) _WinAPI_CombineRgn($hRectRgn, $hCreateRect, $hRectRgn, 2) _WinAPI_DeleteObject($hCreateRect) Next _WinAPI_SetWindowRgn($hWnd, $hRectRgn, True) _WinAPI_DeleteObject($hRectRgn) EndFunc Func _elementExists($array, $element) If $element > UBound($array)-1 Then Return False ; element is out of the array bounds Return True ; element is in array bounds EndFunc Func getdata() ;~ Local $sResult1 = _Excel_RangeRead($oWorkbook, Default,StringReplace(StringReplace ($aExtract[0],"$",""),"A","B")) ;~ Local $sResult2 = _Excel_RangeRead($oWorkbook, Default,StringReplace(StringReplace ($aExtract[0],"$",""),"A","C")) ;~ Local $sResult3 = _Excel_RangeRead($oWorkbook, Default,StringReplace(StringReplace ($aExtract[0],"$",""),"A","D")) ;~ Local $sResult4 = _Excel_RangeRead($oWorkbook, Default,StringReplace(StringReplace ($aExtract[0],"$",""),"A","E")) ;~ Local $sResult5 = _Excel_RangeRead($oWorkbook, Default,StringReplace(StringReplace ($aExtract[0],"$",""),"A","F")) ;~ Local $sResult6 = _Excel_RangeRead($oWorkbook, Default,StringReplace(StringReplace ($aExtract[0],"$",""),"A","G")) guictrlsetdata($Input2,$sResult1) guictrlsetdata($Input3,$sResult2) guictrlsetdata($Input4,$sResult3) guictrlsetdata($Input5,$sResult4) guictrlsetdata($Input6,$sResult5) guictrlsetdata($Input7,$sResult6) _Excel_Close($oExcel,Default,True) EndFunc Func readxl() Global $oExcel = _Excel_Open(False,False) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Global $oWorkbook = _Excel_BookOpen($oExcel, "D:\info.xlsx",False,False) LOcal Const $xlUp = -4162 With $oWorkbook.ActiveSheet ; process active sheet $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) ; get a Range that contains the last used cells $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count ; get the the row count for the range starting in row/column 1 and ending at the last used row/column $iLastCell = .Cells($iRowCount + 1, "B").End($xlUp).Row ProgressOn("Copying Cells", "Copying Cells progress", "0%") For $i = 2 to $iLastCell Local $total = Int(($i/$iLastCell)*100) Local $sResult3 = _Excel_RangeRead($oWorkbook, Default, "A" & $i) FileWriteLine("result.txt",$sResult3) ProgressSet(($i/$iLastCell)*100, $total & "%") Next ;~ FileWrite("result.txt",$sResult3) ProgressSet(100, "Done", "Complete") Sleep (1500) ProgressOff() _Excel_Close($oExcel,Default,True) EndWith EndFunc  
    • robertocm
      By robertocm
      change linked image paths in excel 2007 Open XML Files with AutoIt and 7-zip:
      #include <File.au3> ;Change this Local $sFind = "C:\Users\MyUserName\Documents\MyImageFolder\My%20Image1.png" Local $sReplace = "C:\Users\ANOTHERUSERNAME\Documents\AnotherImageFolder\My%20Image1.png" Local Const $sMessage = "Directory to change excel image paths" Local $sFileSelectFolder = FileSelectFolder($sMessage, "") Local $sTempDir = @ScriptDir & "\testdir" ;Required 7-zip Local $PathZipProgram = @ProgramFilesDir & "\7-Zip\" If Not(FileExists($PathZipProgram & "\7z.exe")) Then MsgBox(16, "", "7z.exe not found in path " & $PathZipProgram) Exit EndIf ;look for excel files in selected directory and all subdirectories Local $SFileList = _FileListToArrayRec($sFileSelectFolder, "*.xls.;*.xlsm", $FLTAR_FILES, $FLTAR_RECUR, $FLTAR_NOSORT, $FLTAR_FULLPATH) If Not @error Then For $i = 1 To $SFileList[0] DirRemove($sTempDir, 1) ;use x command to keep the folder stucture, -aoa Overwrite All existing files without prompt, use -r to unzip the subfolders from the zip file RunWait('"' & $PathZipProgram & '7z.exe" x -aoa -r "' & $SFileList[$i] & '" -o"' & $sTempDir & '" -y', $PathZipProgram, @SW_HIDE) __ReplaceImagePaths($sTempDir, $sFind, $sReplace) RunWait('"' & $PathZipProgram & '7z.exe" a -r "' & $SFileList[$i] & '" "' & $sTempDir & '\*" -tzip -y', $PathZipProgram, @SW_HIDE) Next Else MsgBox(16, "Error", "No files were found in the folder specified.") EndIf DirRemove($sTempDir, 1) Func __ReplaceImagePaths($sTempDir, $sFind, $sReplace) ;List all files with .xml.rels extension in the directory \xl\drawings\_rels Local $aFileList = _FileListToArray($sTempDir & "\xl\drawings\_rels", "*.xml.rels", 1, True) If @error = 1 Then ;MsgBox (0, "", "Path was invalid") SplashTextOn("Title", "Path was invalid", -1, -1, -1, -1, 1, "", 24) Sleep(2000) SplashOff() Exit EndIf If @error = 4 Then ;MsgBox (0, "No files", "No files were found") SplashTextOn("Title", "No files were found", -1, -1, -1, -1, 1, "", 24) Sleep(2000) SplashOff() Exit EndIf Local $iRetval ;Loop through the array For $i = 1 To $aFileList[0] $iRetval = _ReplaceStringInFile($aFileList[$i], $sFind, $sReplace) Next EndFunc  
      Some references:
      https://stackoverflow.com/questions/37145369/change-path-to-picture-links-in-excel http://www.jkp-ads.com/Articles/Excel2007FileFormat.asp
    • TheDcoder
      By TheDcoder
      Hello
      I am relatively new to the world of Microsoft Office and the Excel UDF.
      I am trying to loop through every row in a spreadsheet and get the text/values from each column in the given row... so far I have looked into the Help file for the Excel UDF and the wiki page for Excel UDF but I have no idea about how this is done ... This is all I have in my script:
      Global $oExcel = _Excel_Open(False, False, False, False, True) Global Const $sSpreadsheet = @ScriptDir & '\data.xlsx' Global $oSpreadsheet = _Excel_BookOpen($oExcel, $sSpreadsheet, True, False) ; ... I am placing my bet on the _Excel_Range functions... especially _Excel_RangeRead. I don't know how $vRange works so I would be glad if someone can point me in the right direction . What I would ideally like is to get all of the contents of the spreadsheet (it's just a normal text one) in a 2D array.
      Thanks in Advance!
    • hcI
      By hcI
      Hello, i'm making a little script that lets users enter a question and if the script can't answer to, it ask what would be the answer (a bit like siri on IPhones). And I try to display the "supposed array" I still have an error.. I searched on Google and got nothing looked in the help file too.. I'm here to know if someone have a solution to my error please.
      Here is where $a and $b should become arrays :
      Dim $dossier = @AppDataDir & "\Siri2" Dim $chemin = $dossier & "\data.ini" Global $a = _InfoRead($chemin, 0) Global $b = _InfoRead($chemin, 1) with this function :
      Func _InfoRead($path, $mode = 0) If DirGetSize($dossier) = -1 Then Return SetError(-4) If Not FileExists($chemin) Then Return SetError(-5) Local $readed = IniReadSection($path, "data") If @error Then Return SetError(-3) $size = $readed[0][0] Local $return[$size] For $i = 1 To $size Step 1 $return[$i - 1] = $readed[$i][$mode] Next Return $return EndFunc and the thing that i don't understand is that, after _InfoRead return affected to $a, I can't use _ArrayDisplay because of  error 1 "$aArray is not an array" (and same fpr the variable $b)..
      Can please someone help me to understand why is it doing this ?

      -hcI