Jump to content

Parsing Cytometry Log File Into An Excel Spreadsheet


Recommended Posts

I work in a research lab and we frequently run tests on a Flow Cytometry machine. The end product is a statistics file. Each "sample" that we run in the machine takes up 11 lines in the statistics file and we only need a few figures from each. I have been painstakenly going through these statistics files extracting the relevant data by hand. On average each batch I do has about 600 samples and at 11 lines each you can see how this could get mind numbing. Human error is also effecting the data when I am transferring it over to excel.

So I suppose my first question is what would you guys suggest as the best method of extracting the relevant information out of each entry in the log? Would you suggest storing it in an array or writing it to a temporary file or putting it directly into an excel file while extracting it (using Randall's ExcelCom)?

Here is an example of the raw log file that I am having to work with (I have bolded the information I need):

File Name Sample ID Acquisition Date Gate Gated Events Total Events X Parameter Y Parameter Quad Location Tube Panel Label Events % Gated % Total X Geo Mean Y Geo Mean Median

Data.0405.040 DBA.LN-CD4/CD25 05-Apr-06 No Gate 14558 14558 FSC-H FSC-Height (Linear) SSC-H SSC-Height (Linear) . tube #12 Krishna-1 copy R1 9958 68.40 . 492.90 208.31 .

Data.0405.040 DBA.LN-CD4/CD25 05-Apr-06 G1 9958 14558 FL1-H CD4-FITC (Log) FL2-H CD25-PE (Log) 12, 20 tube #12 Krishna-1 copy UL 39 0.39 . 5.72 64.70 .

Data.0405.040 DBA.LN-CD4/CD25 05-Apr-06 G1 9958 14558 FL1-H CD4-FITC (Log) FL2-H CD25-PE (Log) 12, 20 tube #12 Krishna-1 copy UR 466 4.68 . 111.08 141.08 .

Data.0405.040 DBA.LN-CD4/CD25 05-Apr-06 G1 9958 14558 FL1-H CD4-FITC (Log) FL2-H CD25-PE (Log) 12, 20 tube #12 Krishna-1 copy LL 3255 32.69 . 5.73 2.97 .

Data.0405.040 DBA.LN-CD4/CD25 05-Apr-06 G1 9958 14558 FL1-H CD4-FITC (Log) FL2-H CD25-PE (Log) 12, 20 tube #12 Krishna-1 copy LR 6198 62.24 . 89.52 3.35 .

. DBA.LN-CD4/CD25 . . . . . . . tube #12 . All . 100.00 68.40 36.42 . 72.34

. DBA.LN-CD4/CD25 . . . . . . . tube #12 . M1 . 36.10 24.69 6.12 . 6.73

. DBA.LN-CD4/CD25 . . . . . . . tube #12 . M2 . 63.18 43.21 102.01 . 124.09

. DBA.LN-CD4/CD25 . . . . . . . tube #12 . All . 100.00 68.40 3.88 . 3.49

. DBA.LN-CD4/CD25 . . . . . . . tube #12 . M1 . 88.28 60.39 3.47 . 3.52

. DBA.LN-CD4/CD25 . . . . . . . tube #12 . M2 . 5.40 3.70 117.49 . 132.75

Data.0405.001 B6.SP-Unstained 05-Apr-06 No Gate 25946 25946 FSC-H FSC-Height (Linear) SSC-H SSC-Height (Linear) . tube #1 Krishna-1 R1 9980 38.46 . 462.33 219.94 .

Data.0405.001 B6.SP-Unstained 05-Apr-06 G1 9980 25946 FL1-H FL1-Height (Log) FL2-H FL2-Height (Log) 12, 20 tube #1 Krishna-1 UL 4 0.04 . 4.31 63.49 .

Data.0405.001 B6.SP-Unstained 05-Apr-06 G1 9980 25946 FL1-H FL1-Height (Log) FL2-H FL2-Height (Log) 12, 20 tube #1 Krishna-1 UR 8 0.08 . 23.29 43.08 .

Data.0405.001 B6.SP-Unstained 05-Apr-06 G1 9980 25946 FL1-H FL1-Height (Log) FL2-H FL2-Height (Log) 12, 20 tube #1 Krishna-1 LL 9962 99.82 . 2.36 3.03 .

Data.0405.001 B6.SP-Unstained 05-Apr-06 G1 9980 25946 FL1-H FL1-Height (Log) FL2-H FL2-Height (Log) 12, 20 tube #1 Krishna-1 LR 6 0.06 . 23.40 9.50 .

. B6.SP-Unstained . . . . . . . tube #1 . All . 100.00 38.46 2.37 . 2.33

. B6.SP-Unstained . . . . . . . tube #1 . M1 . 99.91 38.43 2.36 . 2.33

. B6.SP-Unstained . . . . . . . tube #1 . M2 . 0.09 0.03 32.55 . 19.63

. B6.SP-Unstained . . . . . . . tube #1 . All . 100.00 38.46 3.04 . 3.16

. B6.SP-Unstained . . . . . . . tube #1 . M1 . 95.74 36.83 3.17 . 3.25

. B6.SP-Unstained . . . . . . . tube #1 . M2 . 0.18 0.07 34.77 . 20.91

So for the first sample (Data.0405.040) The information I want to put into an excel sheet would be:

Data.0405.040, DBA.LN-CD4/CD25, FL1-H CD4-FITC (Log), FL2-H CD25-PE (Log), 63.18, 5.40, 102.01, 117.49

And for the second sample here (Data.0405.001) I would need:

Data.0405.001, B6.SP-Unstained, FL1-H FL1-Height (Log), Fl2-H-FL2-Height (log), 0.09, 0.18, 32.55, 34.77

I realize this log file might be confusing so I have put this example in excel and have taken a screenshot to show you how each column seperates (each category is seperated by a <Tab> in the raw log file).

Posted Image

It should also be noted that the line that contains:

File Name Sample ID Acquisition Date Gate Gated Events Total Events X Parameter Y Parameter Quad Location Tube Panel Label Events % Gated % Total X Geo Mean Y Geo Mean Median

Is only in the log file once, at the very begining.

So.. now that I have explained the problem are there any suggestions or guidance that can be given to helping me solve this problem?

Some ideas I had were that I would first need to find out how many samples there are in the file. To do this I would have a loop see how many total lines there are in the file (subtract 1 because of the heading line) then divide this number by 11 (how many lines per sample). Once I had this, I would know how many times I am going to need to parse the data in the file.

Do I need to be taking each line as a whole string, use tab as a delimiter and store the relevant information into a temporary file? Then once it is all done I will have a loop to put those into an excel file using Randall's excel com? Or should I do it on the fly while it is parsing each samples 11 lines?

Can anyone throw together an example to get me started on this?

I'm fairly new to the autoit community but from what I can tell you all are a very helpful and supportive bunch. Thanks in advance to any advice/examples that are posted. Glad to be a part of the AutoIt community!

AutoIt Scripts:Aimbot: Proof of Concept - PixelSearching Aimbot with several search/autoshoot/lock-on techniques.Sliding Toolbar - Add a nice Sliding Toolbar to your next script. Click the link to see an animation of it in action!FontInfo UDF - Get list of system fonts, or search to see if a particular font is installed.Get Extended Property UDF - Retrieve a files extended properties (e.g., video/image dimensions, file version, bitrate of song/video, etc)
Link to comment
Share on other sites

Hi,

Here's first try; please adjust and let me know..

Best, Randall

;filereader.au3

#include<ExcelCom.au3>

#include"Array2s.au3"

;local $ar_Final[1],$r,$s_Split=" " ; $s_Split=@tab

local $ar_Final[1],$r,$s_Split=@tab

$Txtfile=@ScriptDir&"\LogFile.txt"

$FilePath=@ScriptDir&"\Array.xls"

$ReadFile=FileOpen($Txtfile,0)

$Line=FileReadLine($ReadFile)

while 1

$Line=FileReadLine($ReadFile)

If @error = -1 Then ExitLoop

$r+=1

redim $ar_Final[ubound($ar_Final)+1]

$ar_Final[$r]=_Read11Lines($ReadFile,$Line,$s_Split)

;MsgBox(0,"","$ar_Final[$r]="&$ar_Final[$r])

WEnd

FileClose($Txtfile)

_ArrayDisplay($ar_Final,"")

_ArrayDelete($ar_Final,0)

$ar2_Array=_Array2DCreateFromArray($ar_Final )

_Array2dDisplay($ar2_Array, "Sorted", 0)

;_ArrayViewText($ar2_Array, 'Display')

;_Array2DTranspose( $ar2_Array) ;? needed before transfer to XL?

;$XLArrayAddress=_XLArrayWrite($ar2_Array,$FilePath,2,"A1",1) ;not tested yet

exit

func _Read11Lines($ReadFile,$Line,$s_Split)

local $ar_Needed[20],$n=1

$ar_Temp=StringSplit($Line,$s_Split)

$s_Needed=$ar_Temp[1]&"|"&$ar_Temp[2]

;MsgBox(0,"","$s_Needed="&$s_Needed)

while $n<11

$n+=1

$Line=FileReadLine($ReadFile)

If @error = -1 Then

MsgBox(0,"","Error, not 11 lines?")

ExitLoop

EndIf

if $n=2 then

$ar_Temp=StringSplit($Line,$s_Split)

$s_Needed&="|"&$ar_Temp[7]&"|"&$ar_Temp[8]

EndIf

if $n=8 then

$ar_Temp=StringSplit($Line,$s_Split)

$s_Needed&="|"&$ar_Temp[14]

$s_Delay=$ar_Temp[16]

EndIf

if $n=11 then

$ar_Temp=StringSplit($Line,$s_Split)

$s_Needed&="|"&$ar_Temp[14] &"|"&$s_Delay&"|"&$ar_Temp[16]

EndIf

;MsgBox(0,"","$s_Needed="&$s_Needed)

WEnd

return $s_Needed

EndFunc ;==>_Read10Lines

Link to comment
Share on other sites

Excellent Randall... this extracts the data beautifully. I'll modify this to my needs quite nicely.

I'll post here when I've finished even though I doubt anyone else in the world would want this particular script.

AutoIt Scripts:Aimbot: Proof of Concept - PixelSearching Aimbot with several search/autoshoot/lock-on techniques.Sliding Toolbar - Add a nice Sliding Toolbar to your next script. Click the link to see an animation of it in action!FontInfo UDF - Get list of system fonts, or search to see if a particular font is installed.Get Extended Property UDF - Retrieve a files extended properties (e.g., video/image dimensions, file version, bitrate of song/video, etc)
Link to comment
Share on other sites

hi,

OK, here it is with comments and the extra UDF if you are interested in that; more array handling to examine!

Best, Randall

;filereader.au3 ; randallc

#include<ExcelCom.au3>

#include"Array2s.au3"; use for $ar2_Array=_Array2DCreateFromArray($ar_Final )/_Array2dDisplay($ar2_Array, "Sorted", 0)

;#include"Array2Ds.au3" ; use for $ar2_Array=_Array2DCreateFromArray($ar_Final )/_Array2dDisplay($ar2_Array, "Sorted", 0)

;and for _ArrayViewText($ar2_Array, 'Display')

;local $ar_Final[1],$r,$s_Split=" " ; $s_Split=@tab

local $ar_Final[1],$r,$s_Split=@tab

$Txtfile=@ScriptDir&"\LogFile.txt"

$FilePath=@ScriptDir&"\Array.xls"

$ReadFile=FileOpen($Txtfile,0)

$Line=FileReadLine($ReadFile) ;read first line which is not needed; then ignore

while 1

$Line=FileReadLine($ReadFile); read line1 of each 11; if not exists, exit ; send line 1 to the func for processing

If @error = -1 Then ExitLoop

$r+=1

redim $ar_Final[ubound($ar_Final)+1] ; every extra line of results needs array to be increased dimension to accept extra

$ar_Final[$r]=_Read11Lines($ReadFile,$Line,$s_Split) ; call the func to process the next 11 lines of text file

;MsgBox(0,"","$ar_Final[$r]="&$ar_Final[$r])

WEnd

FileClose($Txtfile)

_ArrayDisplay($ar_Final,"")

_ArrayDelete($ar_Final,0) ;([0]) element not needed

$ar2_Array=_Array2DCreateFromArray($ar_Final ) ;#include"Array2s.au3" ; use from my UDF to change 1D array of lines ("|" delimited)

;to 2D array

_Array2dDisplay($ar2_Array, "Sorted", 0);#include"Array2s.au3" ; change back to 1D array only tempoirary for quick view

;_ArrayViewText($ar2_Array, 'Display');#include"Array2Ds.au3" ; better 2D array viewer; try it!

;_Array2DTranspose( $ar2_Array) ;? needed before transfer to XL?;#include"Array2s.au3" [Excel arrays are the other way round?]

;$XLArrayAddress=_XLArrayWrite($ar2_Array,$FilePath,2,"A1",1) ;not tested yet

exit

;===================================================================================================

=====

func _Read11Lines($ReadFile,$Line,$s_Split)

local $n=1 ; line counter

$ar_Temp=StringSplit($Line,$s_Split) ; split first log file line of the 11 into a temp array

$s_Needed=$ar_Temp[1]&"|"&$ar_Temp[2] ; start building pipe - delimited string to return to the main script for this 11 lines

;MsgBox(0,"","$s_Needed="&$s_Needed)

while $n<11

$n+=1

$Line=FileReadLine($ReadFile)

If @error = -1 Then

MsgBox(0,"","Error, not 11 lines?")

ExitLoop

EndIf

if $n=2 then ; for line 2 of 11

$ar_Temp=StringSplit($Line,$s_Split); split nth log file line of the 11 into a temp array

$s_Needed&="|"&$ar_Temp[7]&"|"&$ar_Temp[8]; add to pipe - delimited string

EndIf

if $n=8 then ; for line 8 of 11

$ar_Temp=StringSplit($Line,$s_Split); split nth log file line of the 11 into a temp array

$s_Needed&="|"&$ar_Temp[14]; add to pipe - delimited string

$s_Delay=$ar_Temp[16] ; keep out-of sequence for next string add as "$s_Delay"

EndIf

if $n=11 then ; for line 11 of 11

$ar_Temp=StringSplit($Line,$s_Split); split nth log file line of the 11 into a temp array

$s_Needed&="|"&$ar_Temp[14] &"|"&$s_Delay&"|"&$ar_Temp[16]; add to pipe - delimited string ; include "$s_Delay"

EndIf

;MsgBox(0,"","$s_Needed="&$s_Needed)

WEnd

return $s_Needed ; return building pipe - delimited string to return to the main script for this 11 lines

EndFunc ;==>_Read10Lines

;Array2Ds.au3 ; version 2_2

; _ArrayViewText($ar2_Array, 'Title', $i_RowStart, $i_ColumnStart, $i_ZeroRowAsHeader,$Transpose, $Width, $Height, $Left, $Right)

;[in Array2.au3 as below];

;]UDFs to help Array2D usage; various authors; in development or anyone to re-write better?...

; _Array2DCreateFromArray($ar1_Array_Row1,$Displayindex=1, $ZeroRowAsHeader=0)

; _Array2DCreateFirstRow( $aRowArray)

; _ArrayInsert2D(ByRef $avArray, $aRowArray, $nRow = '')

; _ArrayDelete2D(ByRef $avArray, $nRow = '')

; _ArrayReplace2DRow(ByRef $avArray, $aRowArray, $nRow = '')

; _ArrayReplace2DColumn(ByRef $avArray, $nRow = '')

; _ArrayInsert2DColumn(ByRef $avArray, $aRowArray, $nRow = '')

; _ArrayDelete2DColumn(ByRef $avArray, $nRow = '')

; _ArrayToBase1($Array)

; _ArrayToBase0($ar_Array)

; _StringSplit_B0($s_String,$s_Delimiter="|",$i_Flag="0")

; _Array2DTranspose(ByRef $ar_Array)

; _Array2DToD(ByRef $ar_Array, $s_Title = "Array contents", $n_Index = 1, $Line = 0, $s_i_Column = 0)

#region Main

#include-once

#include <Misc.au3>

#include <File.au3>

;#include <sqlite.au3>

#include <Array.au3>

;#include "Array2.au3"

#include "Array2s.au3"

;#include "Array2String.au3"

#include <GuiConstants.au3>

#include <GuiListView.au3>

;global $hndListView_Array1Box_Display = GUICtrlCreateListView($sTemp, 0, 16, $Width, $Height - 40, $LVS_SHOWSELALWAYS, BitOR($LVS_EX_GRIDLINES, $LVS_EX_HEADERDRAGDROP, $LVS_EX_FULLROWSELECT, $LVS_EX_REGIONAL))

#endregion Main

Func _hndButton_Array1Box_Transpose( $hndListView_Array1Box_Display, $Array, $sTitle , $i_StartRow,$i_StartColumn,$i_ZeroRowAsHeader, byref $i_Transpose,$Width = 500, $Height = 500, $Left = -1, $Top = -1)

;msgbox (0,"at start _hndButton_Array1Box_Transpose","$i_Transpose="&$i_Transpose)

_LockAndWait()

$ArrayDimensions=ubound($Array,0)

if $ArrayDimensions = 2 Then

_GUICtrlListViewDeleteAllItems($hndListView_Array1Box_Display)

if $i_Transpose = 0 Then

$i_Transpose = 1

$Dim1 = 2

$Dim2 = 1

$sTemp = 'Index'

For $i = 0 To UBound($Array, $Dim2) - 1

$sTemp = $sTemp & '|' & $i

Next

GUICtrlDelete($hndListView_Array1Box_Display)

$hndListView_Array1Box_Display = GUICtrlCreateListView($sTemp, 0, 16, $Width, $Height - 40, bitor($LVS_SHOWSELALWAYS,$LVS_EDITLABELS), BitOR($LVS_EX_GRIDLINES, $LVS_EX_HEADERDRAGDROP, $LVS_EX_FULLROWSELECT, $LVS_EX_REGIONAL))

GUICtrlSetResizing($hndListView_Array1Box_Display, BitOR($GUI_DockLeft, $GUI_DockTop, $GUI_DockRight, $GUI_DockBottom))

For $i = 0 To UBound($Array, $Dim1)-1

$sTemp = $Array[0][$i]

For $j = 1 To UBound($Array, $Dim2) - 1

$sTemp = $sTemp & '|' & $Array[$j][$i]

Next ;$j

if $i_ZeroRowAsHeader = 1 and $i=0 Then

$sTemp = $sTemp & '|';& '|'

GUICtrlDelete($hndListView_Array1Box_Display)

$hndListView_Array1Box_Display = GUICtrlCreateListView("Index|"&$sTemp, 0, 16, $Width, $Height - 40, bitor($LVS_SHOWSELALWAYS,$LVS_EDITLABELS), BitOR($LVS_EX_GRIDLINES, $LVS_EX_HEADERDRAGDROP, $LVS_EX_FULLROWSELECT, $LVS_EX_REGIONAL))

GUICtrlSetResizing($hndListView_Array1Box_Display, BitOR($GUI_DockLeft, $GUI_DockTop, $GUI_DockRight, $GUI_DockBottom))

EndIf

if ($i=0 and $i_StartColumn=0) or ($i>0) then GUICtrlCreateListViewItem('[' & StringRight(' ' & $i, 4) & ']|' & $sTemp, $hndListView_Array1Box_Display)

if $i_StartRow then _GUICtrlListViewHideColumn ($hndListView_Array1Box_Display, 1)

Next ;$i Next ;$i

Else

$i_Transpose = 0

$Dim1 = 1

$Dim2 = 2

$sTemp = 'Index'

For $i = 0 To UBound($Array, $Dim2) - 1

$sTemp = $sTemp & '|' & $i

Next

GUICtrlDelete($hndListView_Array1Box_Display)

$hndListView_Array1Box_Display = GUICtrlCreateListView($sTemp, 0, 16, $Width, $Height - 40, bitor($LVS_SHOWSELALWAYS,$LVS_EDITLABELS), BitOR($LVS_EX_GRIDLINES, $LVS_EX_HEADERDRAGDROP, $LVS_EX_FULLROWSELECT, $LVS_EX_REGIONAL))

GUICtrlSetResizing($hndListView_Array1Box_Display, BitOR($GUI_DockLeft, $GUI_DockTop, $GUI_DockRight, $GUI_DockBottom))

For $i = 0 To UBound($Array, $Dim1)-1

$sTemp = $Array[$i][0]

For $j = 1 To UBound($Array, $Dim2) - 1

$sTemp = $sTemp & '|' & $Array[$i][$j]

Next ;$j

if $i_ZeroRowAsHeader = 1 and $i=0 Then

$sTemp = $sTemp & '|';& '|'

GUICtrlDelete($hndListView_Array1Box_Display)

$hndListView_Array1Box_Display = GUICtrlCreateListView("Index|"&$sTemp, 0, 16, $Width, $Height - 40, bitor($LVS_SHOWSELALWAYS,$LVS_EDITLABELS), BitOR($LVS_EX_GRIDLINES, $LVS_EX_HEADERDRAGDROP, $LVS_EX_FULLROWSELECT, $LVS_EX_REGIONAL))

GUICtrlSetResizing($hndListView_Array1Box_Display, BitOR($GUI_DockLeft, $GUI_DockTop, $GUI_DockRight, $GUI_DockBottom))

EndIf

if ($i=0 and $i_StartRow=0) or ($i>0) then GUICtrlCreateListViewItem('[' & StringRight(' ' & $i, 4) & ']|' & $sTemp, $hndListView_Array1Box_Display)

if $i_StartColumn=1 then _GUICtrlListViewHideColumn ($hndListView_Array1Box_Display, 1)

Next ;$i

EndIf

EndIf

_ResetLockWait()

EndFunc ;==>_hndButton_Array1Box_Transpose

Func _ArrayViewText( byref $Array, $sTitle = 'Array Box', $i_StartRow=0,$i_StartColumn=0,$i_ZeroRowAsHeader=0, $i_Transpose = 0,$Width = 800, $Height = 600, $Left = -1, $Top = -1) ;,$Displayindex=1, $ZeroRowAsHeader=0)

;===============================================================================

;_ArrayViewText($ar2_Array, 'Title', $i_RowStart, $i_ColumnStart, $i_ZeroRowAsHeader,$Transpose, $Width, $Height, $Left, $Right)

; Function Name: _ArrayViewText ; was _Array1Box()

; Description: Shows an array in a GUI window. Improved version of _Array1Display

; Parameter(s): $Array - An array, 1d or 2d, 0-based or 1-based

; $i_StartRow ; 1 or 0 or 2- tells whether to display first (zero) array row

; $i_StartColumn ; 1 or 0 or 2- tells whether to display first (zero) array Column

; $i_Transpose ;1 or 0 - tells when to transpose display

; $ZeroRowAsHeader ; 1 or 0 tells how to display Header

; $sTitle - the title to put on the output window

; $Width, $Height, $Left, $Top - Set the size and position of the output window

; Requirement(s): $Array must be 1d or 2d.

; Return Value(s): Integer, 0 or more = 0-based index of selected item

; -1 = no item was selected

; Error=1 = $Array is not an array

; Author(s): Mike Ratzlaff <mike@ratzlaff.org> // modified randall Clapp <randallc@ozemail.com.au>

; Revision: 20050728A

;

;===============================================================================

Local $AllDone = 0, $msg, $i, $j, $ArrayDimensions = UBound($Array, 0), $ArrayMax, $sTemp

Local $hndForm_Array1Box_Main, $hndListView_Array1Box_Display, $hndButton_Array1Box_Close

local $sTxtFile, $i_TempStartColumn=0

$Dim1 = 1

$Dim2 = 2

If $i_Transpose = 1 Then

$Dim1 = 2

$Dim2 = 1

EndIf

If ($i_Transpose = 1 and $i_StartRow=1) or ($i_Transpose = 0 and $i_StartColumn=1) Then

$i_TempStartColumn=1

EndIf

If $ArrayDimensions = 0 Then

SetError(1)

Return 'Given variable is not an array'

EndIf

Select

Case $ArrayDimensions = 1

$ArrayMax = UBound($Array) - 1

Case $ArrayDimensions = 2

$ArrayMax = UBound($Array, $Dim1) - 1

EndSelect

If $Height < 100 Then $Height = 100

If $Width < 500 Then $Width = 500

$hndForm_Array1Box_Main = GUICreate($sTitle, $Width, $Height, $Left, $Top, $WS_OVERLAPPEDWINDOW + $WS_VISIBLE + $WS_CLIPSIBLINGS)

Local $editCtrl = "Edit1";name of the Edit control that pops up when edit a listitem....

Local $editFlag = 0, $start, $diff, $doubleclicked;, $msg, $listview, $Gui

If $ArrayDimensions = 1 Then

$sTemp = 'Index|Value'

ElseIf $ArrayDimensions = 2 Then

$sTemp = 'Index'

For $i = $i_TempStartColumn To UBound($Array, $Dim2) - 1

$sTemp = $sTemp & '|' & $i

Next

EndIf

GUICtrlCreateLabel('Basic Array Rows = (' & UBound($Array) & ')'&'.....Basic Array Columns = (' & UBound($Array,2) & ')', 0, 0, $Width, 16)

GUICtrlSetResizing(-1, BitOR($GUI_DockLeft, $GUI_DockTop, $GUI_DockRight, $GUI_DOCKHEIGHT))

$hndListView_Array1Box_Display = GUICtrlCreateListView($sTemp, 0, 16, $Width, $Height - 40, bitor($LVS_SHOWSELALWAYS,$LVS_EDITLABELS), BitOR($LVS_EX_GRIDLINES, $LVS_EX_HEADERDRAGDROP, $LVS_EX_FULLROWSELECT, $LVS_EX_REGIONAL))

GUICtrlSetResizing($hndListView_Array1Box_Display, BitOR($GUI_DockLeft, $GUI_DockTop, $GUI_DockRight, $GUI_DockBottom))

;Create Controls, Show GUI

$hndButton_Array1Box_Close = GUICtrlCreateButton('&Close', $Width - 80, $Height - 24, 70, 24)

GUICtrlSetResizing($hndButton_Array1Box_Close, BitOR($GUI_DockRight, $GUI_DockBottom, $GUI_DockSize))

GUICtrlSetState($hndButton_Array1Box_Close, BitOR($GUI_DefButton, $GUI_FOCUS))

$hndButton_Array1Box_Transpose = GUICtrlCreateButton('&Transpose', $Width - 160, $Height - 24, 70, 24)

GUICtrlSetResizing($hndButton_Array1Box_Transpose, BitOR($GUI_DockRight, $GUI_DockBottom, $GUI_DockSize))

GUICtrlSetState($hndButton_Array1Box_Transpose, ($GUI_DefButton))

$hndButton_Array1Box_Header = GUICtrlCreateButton('&Header', $Width - 240, $Height - 24, 70, 24)

GUICtrlSetResizing($hndButton_Array1Box_Header, BitOR($GUI_DockRight, $GUI_DockBottom, $GUI_DockSize))

GUICtrlSetState($hndButton_Array1Box_Header, ($GUI_DefButton))

$hndButton_Array1Box_ZeroRow= GUICtrlCreateButton('&ZeroRow', $Width - 320, $Height - 24, 70, 24)

GUICtrlSetResizing($hndButton_Array1Box_ZeroRow, BitOR($GUI_DockRight, $GUI_DockBottom, $GUI_DockSize))

GUICtrlSetState($hndButton_Array1Box_ZeroRow, ($GUI_DefButton))

$hndButton_Array1Box_ZeroColumn = GUICtrlCreateButton('&ZeroColumn', $Width - 400, $Height - 24, 70, 24)

GUICtrlSetResizing($hndButton_Array1Box_ZeroColumn, BitOR($GUI_DockRight, $GUI_DockBottom, $GUI_DockSize))

GUICtrlSetState($hndButton_Array1Box_ZeroColumn, ($GUI_DefButton))

$hndButton_Array1Box_TextSelect = GUICtrlCreateButton('&Text' & 'Selected', $Width - 480, $Height - 24, 70, 24)

GUICtrlSetResizing($hndButton_Array1Box_TextSelect, BitOR($GUI_DockRight, $GUI_DockBottom, $GUI_DockSize))

GUICtrlSetState($hndButton_Array1Box_TextSelect, ($GUI_DefButton))

;$hndButton_Array1Box_ExcelSelect = GUICtrlCreateButton('&Excel' & 'Selected', $Width - 280, $Height - 24, 80, 24)

;GUICtrlSetResizing($hndButton_Array1Box_ExcelSelect, BitOR($GUI_DockRight, $GUI_DockBottom, $GUI_DockSize))

;GUICtrlSetState($hndButton_Array1Box_ExcelSelect, ($GUI_DefButton))

GUISetState(@SW_SHOW, $hndForm_Array1Box_Main)

Dim $B_DESCENDING[_GUICtrlListViewGetSubItemsCount ($hndListView_Array1Box_Display) ]

;Display Array

Select

Case $ArrayDimensions = 1 ;1-Dimensional Array

For $i = 0 To $ArrayMax

GUICtrlCreateListViewItem($i & '|' & $Array[$i], $hndListView_Array1Box_Display)

Next ;$i

Case ($ArrayDimensions = 2) ;And ($i_Transpose = 0) and $i_StartColumn=1000;2-Dimensional Array

;==============================================================================================

_LockAndWait()

$sTemp = 'Index'

For $i = 0 To UBound($Array, 2) - 1

$sTemp = $sTemp & '|' & $i

Next

GUICtrlDelete($hndListView_Array1Box_Display)

$hndListView_Array1Box_Display = GUICtrlCreateListView($sTemp, 0, 16, $Width, $Height - 40, bitor($LVS_SHOWSELALWAYS,$LVS_EDITLABELS), BitOR($LVS_EX_GRIDLINES, $LVS_EX_HEADERDRAGDROP, $LVS_EX_FULLROWSELECT, $LVS_EX_REGIONAL))

GUICtrlSetResizing($hndListView_Array1Box_Display, BitOR($GUI_DockLeft, $GUI_DockTop, $GUI_DockRight, $GUI_DockBottom))

For $i = 0 To UBound($Array, 1)-1

$sTemp = $Array[$i][0]

For $j = 1 To UBound($Array, 2) - 1

$sTemp = $sTemp & '|' & $Array[$i][$j]

Next ;$j

GUICtrlCreateListViewItem('[' & StringRight(' ' & $i, 4) & ']|' & $sTemp, $hndListView_Array1Box_Display)

Next ;$i

if ($ArrayDimensions = 2) And ($i_Transpose = 1) then

$i_Transpose=_Iif($i_Transpose=1, 0, 1)

_hndButton_Array1Box_Transpose($hndListView_Array1Box_Display,$Array, $sTitle , $i_StartRow,$i_StartColumn,$i_ZeroRowAsHeader, $i_Transpose,$Width , $Height, $Left, $Top )

Else

_ResetLockWait()

EndIf

;==============================================================================================

Case Else ;Unhandled Type

EndSelect

Do

$dll = DllOpen("user32.dll")

$msg = GUIGetMsg(1)

If $msg[0] = $GUI_EVENT_PRIMARYDOWN Then

Local $focus = ControlGetFocus(WinGetTitle($hndForm_Array1Box_Main))

If (StringInStr($focus, "SysListView3")) Then

$diff = TimerDiff($start)

; Read the current mouse-doubleclick-settings from registry

$mousespeed = RegRead("HKCU\Control Panel\Mouse", "DoubleClickSpeed")

If $mousespeed = "" Then $mousespeed = 500

If $diff < $mousespeed And $doubleclicked = 0 Then

DllClose($dll)

$ar_Items=_GUICtrlListViewGetItemTextArray($hndListView_Array1Box_Display)

if IsArray($ar_Items) then

$rowIndex=number(StringReplace(StringReplace($ar_Items[1],"[",""),"]",""))

If ($rowIndex>-1) Then

$ar_SingleArray=_Array2DToD( $Array, $sTitle, 0, $rowIndex , ($i_Transpose=0))

$ar_SingleArray=_ArrayToBase0($ar_SingleArray)

_Main($ar_SingleArray)

$sTemp=_ArrayToString($ar_SingleArray,"|")

;if $i_Transpose=1 then _ArrayReplace2DColumn( $Array,$ar_SingleArray,$rowIndex)

;if $i_Transpose=0 then _ArrayReplace2DRow( $Array,$ar_SingleArray, $rowIndex)

if $i_Transpose=1 then _ArrayReplace2DColumn( $Array,$sTemp,$rowIndex)

if $i_Transpose=0 then _ArrayReplace2DRow( $Array,$sTemp, $rowIndex)

$i_Check=(($i_Transpose=0)* ($i_StartColumn))or(($i_Transpose)* ($i_StartRow))

$i_Check=_Iif($i_Check=True,1,0)

;$sTemp=_ArrayToString( $ar_SingleArray,"|" )

_GUICtrlListViewInsertItem($hndListView_Array1Box_Display,$rowIndex,'[' & StringRight(' ' & $rowIndex, 4) & ']|' & $sTemp)

_GUICtrlListViewDeleteItemsSelected ($hndListView_Array1Box_Display)

$dll = DllOpen("user32.dll")

$doubleclicked = 1

EndIf

EndIf

Else

$doubleclicked = 0

EndIf

$start = TimerInit()

EndIf

EndIf

Select

Case $msg[0] = $GUI_EVENT_CLOSE

$AllDone = 1

Case $msg[0] = $hndButton_Array1Box_Close

$AllDone = 1

Case $msg[0] = $hndButton_Array1Box_Header

$i_ZeroRowAsHeader=_Iif($i_ZeroRowAsHeader=1, 0, 1)

$i_Transpose=_Iif($i_Transpose=1, 0, 1)

_hndButton_Array1Box_Transpose($hndListView_Array1Box_Display,$Array, $sTitle , $i_StartRow,$i_StartColumn,$i_ZeroRowAsHeader, $i_Transpose,$Width , $Height, $Left, $Top )

Case $msg[0] = $hndButton_Array1Box_ZeroRow

$i_StartRow=_Iif($i_StartRow=1, 0, 1)

if $i_StartRow and not $i_Transpose then ;$i_StartRow=0

_GUICtrlListViewDeleteItem ($hndListView_Array1Box_Display, 0)

Elseif $i_StartRow Then ;$i_StartRow=0

_GUICtrlListViewHideColumn ($hndListView_Array1Box_Display, 1)

Else ;really $i_StartRow=1, but here $i_StartRow=0

$ar_SingleArray=_Array2DToD( $Array, $sTitle, 0, 0 , ($i_Transpose=0))

$ar_SingleArray=_ArrayToBase0($ar_SingleArray)

$i_Check=($i_Transpose=1)*($i_StartRow=1); rem these are around wrong way

$i_Check=_Iif($i_Check=True,1,0)

$sTemp=_ArrayToString( $ar_SingleArray,"|",$i_Check )

if $i_Transpose=0 then _GUICtrlListViewInsertItem($hndListView_Array1Box_Display,0,'[' & StringRight(' ' & 0, 4) & ']|' & $sTemp)

if $i_Check=0 then

_GUICtrlListViewSetColumnWidth ($hndListView_Array1Box_Display, 1, $LVSCW_AUTOSIZE);$LVSCW_AUTOSIZE

EndIf

EndIf

Case $msg[0] = $hndButton_Array1Box_ZeroColumn

$i_StartColumn=_Iif($i_StartColumn=1, 0, 1)

if $i_StartColumn and $i_Transpose then

_GUICtrlListViewDeleteItem ($hndListView_Array1Box_Display, 0)

Elseif $i_StartColumn Then

_GUICtrlListViewHideColumn ($hndListView_Array1Box_Display, 1)

Else

$ar_SingleArray=_Array2DToD( $Array, $sTitle, 0, 0 , ($i_Transpose=0))

$ar_SingleArray=_ArrayToBase0($ar_SingleArray)

$i_Check=($i_Transpose=1)*($i_StartColumn=1); rem these are around wrong way

$i_Check=_Iif($i_Check=True,1,0)

$sTemp=_ArrayToString( $ar_SingleArray,"|",$i_Check )

if $i_Transpose=1 then _GUICtrlListViewInsertItem($hndListView_Array1Box_Display,0,'[' & StringRight(' ' & 0, 4) & ']|' & $sTemp)

if $i_Check=0 then

_GUICtrlListViewSetColumnWidth ($hndListView_Array1Box_Display, 1, $LVSCW_AUTOSIZE);$LVSCW_AUTOSIZE

EndIf

EndIf

Case $msg[0] = $hndButton_Array1Box_Transpose

_hndButton_Array1Box_Transpose($hndListView_Array1Box_Display,$Array, $sTitle , $i_StartRow,$i_StartColumn,$i_ZeroRowAsHeader, $i_Transpose,$Width , $Height, $Left, $Top )

Case $msg[0] = $hndButton_Array1Box_TextSelect

$sTxtFile = @ScriptDir & "\ListView.txt"

Local $a_indices = _GUICtrlListViewGetSelectedIndices ($hndListView_Array1Box_Display, 1)

If (IsArray($a_indices)) Then

Local $i

$f = FileOpen( $sTxtFile, 2)

For $i = 1 To $a_indices[0]

$ExcelLine = StringReplace(_GUICtrlListViewGetItemText ($hndListView_Array1Box_Display, $a_indices[$i]), "|", @TAB)

FileWriteLine( $f,$ExcelLine)

Next

FileClose( $f );

EndIf

RunWait("Notepad.exe " & $sTxtFile)

Case $msg[0] = $hndListView_Array1Box_Display

$i_OrderNumber = GUICtrlGetState($hndListView_Array1Box_Display) ;Sort the list by the column header clicking

GUISetState(@SW_LOCK)

_GUICtrlListViewSort ($hndListView_Array1Box_Display, $B_DESCENDING, GUICtrlGetState($hndListView_Array1Box_Display))

GUISetState(@SW_UNLOCK)

EndSelect

DllClose($dll)

Until $AllDone

;Destroy GUI, Return index of selected item

$i = ControlListView(WinGetTitle($sTitle), '', $hndListView_Array1Box_Display, "GetSelected")

If $i = '' Then $i = -1

GUIDelete($hndForm_Array1Box_Main)

Return $i

EndFunc ;==>_ArrayViewText

Func _MonitorEditState(ByRef $h_gui, ByRef $editCtrl, ByRef $editFlag, ByRef $listview, ByRef $dll, ByRef $msg, ByRef $start, ByRef $diff, ByRef $doubleclicked)

If $msg[0] = $GUI_EVENT_PRIMARYDOWN Then

Local $focus = ControlGetFocus(WinGetTitle($h_gui))

Local $pos = ControlGetPos($h_gui, "", $focus)

Local $mpos = MouseGetPos()

If ControlCommand($h_gui, "", $editCtrl, "IsVisible", "") Then

If $mpos[0] < $pos[0] Or $mpos[0] > $pos[0] + $pos[2] Or _

$mpos[1] < $pos[1] Or $mpos[1] > $pos[1] + $pos[3] Then

CancelEdit($listview)

$editFlag = 0

EndIf

Else

If (StringInStr($focus, "SysListView3")) Then

$diff = TimerDiff($start)

; Read the current mouse-doubleclick-settings from registry

$mousespeed = RegRead("HKCU\Control Panel\Mouse", "DoubleClickSpeed")

If $mousespeed = "" Then $mousespeed = 700

If $diff < $mousespeed And $doubleclicked = 0 Then

Rename($listview)

$doubleclicked = 1

Else

$doubleclicked = 0

EndIf

$start = TimerInit()

EndIf

EndIf

EndIf

Local $pressed = _IsPressedMod($dll)

If $editFlag And $pressed = 13 Then; pressed enter

Update($h_gui, $editCtrl, $listview)

ElseIf $editFlag And $pressed = 27 Then; pressed esc

CancelEdit($listview)

$editFlag = 0

ElseIf Not $editFlag And $pressed = 113 Then; pressed f2

Rename($listview)

$editFlag = 1

EndIf

Sleep(50)

If ControlCommand($h_gui, "", $editCtrl, "IsVisible", "") Then

If $editFlag = 0 Then

$editFlag = 1

Rename($listview)

EndIf

Else

$editFlag = 0

EndIf

EndFunc ;==>_MonitorEditState

Func Rename(ByRef $listview)

Local $itemIndex = _GUICtrlListViewGetCurSel ($listview)

GUICtrlSendMsg($listview, $LVM_EDITLABEL, $itemIndex, 0)

HotKeySet("{Enter}", "Enter")

EndFunc ;==>Rename

Func _Main(byref $Array1)

Local $editCtrl = "Edit1";name of the Edit control that pops up when edit a listitem....

Local $editFlag = 0, $listview, $Gui, $start, $diff, $doubleclicked, $msg

$Gui = GUICreate("Edit-able ListViewItems")

$CloseButton = GUICtrlCreateButton("Close", 300, 300, 100, 20)

$listview = GUICtrlCreateListView("Items", 10, 10, 300, 200, $LVS_EDITLABELS);Important Style!!!

; Populate list and make it wide enough to see

For $i = 0 To ubound($Array1)-1

GUICtrlCreateListViewItem($Array1[$i], $listview)

Next

GUICtrlSendMsg($listview, 0x101E, 0, -1);$listview, LVM_SETCOLUMNWIDTH, 0, resize to widest value

GUISetState(@SW_SHOW)

$dll = DllOpen("user32.dll")

While 1

$msg = GUIGetMsg(1)

_MonitorEditState($Gui, $editCtrl, $editFlag, $listview, $dll, $msg, $start, $diff, $doubleclicked)

Select

Case $msg[0] = $GUI_EVENT_CLOSE Or $msg[0] = $CloseButton

for $i=0 to _GUICtrlListViewGetItemCount($listview) - 1

$Array1[$i]=_GUICtrlListViewGetItemText($listview,$i)

Next

GUIDelete($Gui)

ExitLoop

Case Else

EndSelect

WEnd

DllClose($dll)

EndFunc ;==>_Main

Func Enter()

; just a dummy function

EndFunc ;==>Enter

Func Update(ByRef $h_gui, ByRef $editCtrl, ByRef $listview)

Local $newText = ControlGetText($h_gui, "", $editCtrl)

Local $item = GUICtrlRead($listview)

GUICtrlSetData($item, $newText)

HotKeySet("{Enter}")

Send("{Enter}");quit edit mode

$editFlag = 0

$update = 0

EndFunc ;==>Update

Func CancelEdit(ByRef $listview)

GUICtrlSendMsg($listview, $LVM_CANCELEDITLABEL, 0, 0)

EndFunc ;==>CancelEdit

Func _IsPressedMod($dll = "user32.dll")

Local $aR, $bRv, $hexKey, $i

For $i = 8 To 128

$hexKey = '0x' & Hex($i, 2)

$aR = DllCall($dll, "int", "GetAsyncKeyState", "int", $hexKey)

If $aR[0] <> 0 Then Return $i

Next

Return 0

EndFunc ;==>_IsPressedMod

Func _LockAndWait()

Local $Cursor_WAIT

GUISetState(@SW_LOCK)

GUISetCursor($Cursor_WAIT, 1)

EndFunc ;==>_LockAndWait

Func _ResetLockWait()

local $Cursor_ARROW

GUISetState(@SW_UNLOCK)

GUISetCursor($Cursor_ARROW, 1)

EndFunc ;==>_ResetLockWait

;func _SQLViewDBTable($DBFile,$s_Table)

;Local $aResult, $iColumns, $sErrorMsg

;_SQLite_Startup()

;_SQLite_Open($DBFile)

;if @error > 0 Then

; MsgBox(0,"","Error Opening/Creating Database")

;Else

; Local $res,$row,$col,$err

; $tmp = "SELECT ROWID,* FROM "&$s_Table&";"

; $dbg = _SQLite_GetTable2d(-1,$tmp,$aResult,$row,$col,$err)

; if Not $dbg = $SQLITE_OK Then

; MsgBox(0,"SQLite Error",$err)

; Else

; _ArrayViewText( $aResult, 'Array Box',0,0,1,0,800,600)

; EndIf

;EndIf

;_SQLite_Close(-1)

;_SQLite_Shutdown()

;return $aResult

;EndFunc

;Array2s.au3 ; version 2_3

;Array2.au3 ; version _1_21 ; ; Dec5th2005

;UDFs to help Array2D usage; various authors; in development or anyone to re-write better?...

; _Array2DCreateFromArray($ar1_Array_Row1,$Displayindex=1, $ZeroRowAsHeader=0)

; _Array2DCreateFirstRow( $aRowArray)

; _ArrayInsert2D(ByRef $avArray, $aRowArray, $nRow = '')

; _ArrayDelete2D(ByRef $avArray, $nRow = '')

; _ArrayReplace2DRow(ByRef $avArray, $aRowArray, $nRow = '')

; _ArrayReplace2DColumn(ByRef $avArray, $nRow = '')

; _ArrayInsert2DColumn(ByRef $avArray, $aRowArray, $nRow = '')

; _ArrayDelete2DColumn(ByRef $avArray, $nRow = '')

; _ArrayToBase1($Array)

; _ArrayToBase0($ar_Array)

; _StringSplit_B0($s_String,$s_Delimiter="|",$i_Flag="0")

; _Array2DTranspose(ByRef $ar_Array)

; _Array2DToD(ByRef $ar_Array, $s_Title = "Array contents", $n_Index = 1, $Line = 0, $s_i_Column = 0)

;[in Array2D]_ArrayViewText($ar2_Array, 'Title', $i_RowStart, $i_ColumnStart, $i_ZeroRowAsHeader,$Transpose, $Width, $Height, $Left, $Right)

#region Main

#include-once

;#include <sqlite.au3>

#include <Array.au3>

#include <File.au3>

#endregion Main

func _Array2DToArStrings(byref $ar2_Array,byref $s_Row2)

_Array2DTranspose( $ar2_Array)

redim $s_Row2[ubound($ar2_Array,2)]

for $i=0 to ubound($ar2_Array,2)-1

$ar_Temp=_Array2DToD( $ar2_Array,"",0,$i)

_ArrayDelete($ar_Temp,0)

$s_Row2[$i]=_ArrayToString($ar_Temp,"|")

Next

_Array2DTranspose( $ar2_Array)

EndFunc

func _ArrayFieldSort( byref $ar_Array2d,$i_Asc=1,$i_Index1=0,$i_Index2="",$i_Index3="",$i_Index4="",$i_Index5="")

local $ar_Index[6]

$ar_Index[1]=$i_Index1

$ar_Index[2]=$i_Index2

$ar_Index[3]=$i_Index3

$ar_Index[4]=$i_Index4

$ar_Index[5]=$i_Index5

;concatenate Primary and Secondary index fields into extra column

ReDim $ar_Array2d[uBound($ar_Array2d)][uBound($ar_Array2d,2) +1] ;room for extra column

For $r = 0 To UBound($ar_Array2d) -1

For $t = 1 To UBound($ar_Index)-1

if $ar_Index[$t]<>"" then

$ar_Array2d[$r][uBound($ar_Array2d,2)-1] &= $ar_Array2d[$r][$ar_Index[$t]]

EndIf

Next

Next

_ArraySort($ar_Array2d, $i_Asc, 0, 0, UBound($ar_Array2d,2), UBound($ar_Array2d,2) -1)

ReDim $ar_Array2d[uBound($ar_Array2d)][uBound($ar_Array2d,2) -1] ;kill added column

EndFunc ;==>_ArrayFieldSort

Func _Array2dDisplay(ByRef $ar_Array, $s_Title="Array contents", $n_Index=1)

;-------------------------------------

; Display 2 dimensional array

Local $output = ""

Local $r, $c

If Not IsArray($ar_Array) Then Return -1

For $r = $n_Index to UBound($ar_Array,1) - 1

$output = $output & @LF

For $c = 0 to UBound($ar_Array,2) - 1

$output = $output & $ar_Array[$r][$c] & " "

Next

Next

MsgBox(4096, $s_Title, $output)

Return

EndFunc ;==>_Array2dDisplay

Func _FileTo2dArray($sFilePath, ByRef $aArray)

;--------------------------------------

Local $hFile

Local $str

Local $r, $c

Local $aLocal[1]

Local $aStr

_FileReadToArray($sFilePath, $aLocal)

$aStr = StringSplit($aLocal[1], ",")

ReDim $aArray[$aLocal[0]][$aStr[0]]

For $r = 1 To $aLocal[0]

$aStr = StringSplit($aLocal[$r], ",")

For $c = 1 To UBound($aArray,2)

$aArray[$r-1][$c-1] = $aStr[$c]

Next

Next

;_Array2dDisplay($aArray, "Before Sort",0)

Return

EndFunc ;==>_FileTo2dArray

Func _Array2DCreateFirstRow( $aRowString)

local $avArray[1][1]

_ArrayInsert2D( $avArray, $aRowString)

return $avArray

EndFunc ;==>_Array2DCreateFirstRow

Func _ArrayInsert2D(ByRef $avArray, $aRowString, $nRow = '')

Local $number_of_columns = UBound($avArray, 2), $number_of_rows = UBound($avArray), $nSize = 0,$arraysize

$aRowArray = _Stringsplit_B0($aRowString)

If $number_of_columns < (UBound($aRowArray) ) Then $number_of_columns = UBound($aRowArray)

If $nRow > $number_of_rows - 1 Then

SetError(2)

Return(2)

EndIf

if $avArray[0][0] <> '' Then $number_of_rows = $number_of_rows + 1

If $number_of_columns < ubound($aRowArray) Then $number_of_columns = ubound($aRowArray)

ReDim $avArray[$number_of_rows ][$number_of_columns]

If $nRow == '' Then

;_ArrayDisplay($aRowArray,"")

$arraysize = UBound($aRowArray);Find array size

For $c = 0 To $arraysize-1

$avArray[$number_of_rows - 1][$c] = $aRowArray[$c]

Next

SetError(0)

Return(0)

EndIf

For $r = $number_of_rows - 1 To $nRow Step -1

For $c = 0 To ubound($avArray,2)-1

If $r <> $nRow Then

$avArray[$r][$c] = $avArray[$r - 1][$c]

$avArray[$r - 1][$c] = ''

Else

If not ($c > ubound($aRowArray)-1) Then

$avArray[$r][$c] = $aRowArray[$c]

EndIf

EndIf

Next

Next

SetError(0)

Return(0)

EndFunc ;==>_ArrayInsert2D

Func _ArrayReplace2DRow(ByRef $avArray, $aRowString, $nRow = '')

$aRowArray = _Stringsplit_B0($aRowString)

Local $number_of_columns = UBound($avArray, 2), $number_of_rows = UBound($avArray), $nSize = 0

If $number_of_columns < (UBound($aRowArray) ) Then $number_of_columns = UBound($aRowArray)

If $nRow > $number_of_rows - 1 Then

SetError(2)

Return(2)

EndIf

If $number_of_columns < ubound($aRowArray) Then $number_of_columns = ubound($aRowArray)

ReDim $avArray[$number_of_rows ][$number_of_columns]

For $c = 0 To ubound($avArray,2)-1

If not ($c > ubound($aRowArray)-1) Then

$avArray[$nRow][$c] = $aRowArray[$c]

EndIf

Next

SetError(0)

Return(0)

EndFunc ;==>_ArrayReplace2DRow

Func _ArrayReplace2DColumn(ByRef $avArray, $aRowString, $nColumn = '')

$aRowArray = _Stringsplit_B0($aRowString)

Local $number_of_columns = UBound($avArray, 2), $number_of_rows = UBound($avArray), $nSize = 0

If $number_of_columns < (UBound($aRowArray) ) Then $number_of_columns = UBound($aRowArray)

If $nColumn > $number_of_columns - 1 Then

SetError(2)

Return(2)

EndIf

If $number_of_columns < ubound($aRowArray) Then $number_of_columns = ubound($aRowArray)

ReDim $avArray[$number_of_rows ][$number_of_columns]

For $c = 0 To ubound($avArray,1)-1

If not ($c > ubound($aRowArray)-1) Then

$avArray[$c][$nColumn] = $aRowArray[$c]

EndIf

Next

SetError(0)

Return(0)

EndFunc ;==>_ArrayReplace2DColumn

Func _ArrayDelete2D(ByRef $avArray, $nRow = '')

Local $number_of_columns = UBound($avArray, 2), $number_of_rows = UBound($avArray), $nSize = 0

If $nRow > $number_of_rows - 1 Then

SetError(2)

Return(2)

EndIf

If $number_of_rows == 1 Or StringLower($nRow) == 'all' Then

ReDim $avArray[1][1]

$avArray[0][0] = ''

SetError(0)

Return(0)

EndIf

If $nRow == '' Then

$avArray[$number_of_rows - 1][0] = ''

For $x = 0 To $number_of_rows - 1

If $nSize < ubound($avArray,2) Then $nSize = ubound($avArray,2)

Next

ReDim $avArray[$number_of_rows - 1][$nSize + 1]

SetError(0)

Return(0)

EndIf

$avArray[$nRow][0] = ''

For $x = 0 To $number_of_rows - 1

If $nSize < ubound($avArray,2) Then $nSize = ubound($avArray,2)

Next

For $r = $nRow To $number_of_rows - 2

For $c = 0 To $number_of_columns -1

$avArray[$r][$c] = ''

Next

$avArray[$r][0] = $avArray[$r + 1][0]

For $c = 1 To ubound($avArray,2)-1

$avArray[$r][$c] = $avArray[$r + 1][$c]

Next

Next

ReDim $avArray[$number_of_rows - 1][$nSize ]

SetError(0)

Return(0)

EndFunc ;==>_ArrayDelete2D

Func _ArrayInsert2DColumn(ByRef $avArray, $aRowString, $nColumn = '')

;$aRowArray = _Stringsplit_B0($aRowString)

_Array2DTranspose( $avArray)

;_Array2DManage($avArray, $aRowArray,1,$nColumn)

_ArrayInsert2D($avArray, $aRowString,$nColumn)

_Array2DTranspose( $avArray)

return @error

EndFunc ;==>_ArrayInsert2D

Func _ArrayDelete2DColumn(ByRef $avArray, $nColumn = '')

_Array2DTranspose( $avArray)

;_Array2DManage($avArray, "",2,$nColumn)

_ArrayDelete2D($avArray,$nColumn)

_Array2DTranspose( $avArray)

return @error

EndFunc ;==>_ArrayDelete2D

Func _ArrayToBase1($Array)

local $Array_1[ubound($Array)+1]

$Array_1[0]=ubound($Array)

for $i=1 to ubound($Array)

$Array_1[$i]=$Array[$i-1]

Next

return $Array_1

EndFunc ;==>_ArrayToBase1

Func _ArrayToBase0($ar_Array)

local $ar_Array_0[ubound($ar_Array)-1]

for $i=0 to ubound($ar_Array_0)-1

$ar_Array_0[$i]=$ar_Array[$i+1]

Next

return $ar_Array_0

EndFunc ;==>_ArrayToBase0

Func _StringSplit_B0($s_String,$s_Delimiter="|",$i_Flag="0")

; SYNTAX _StringSplit_0($s_String[,[$s_Delimiter="|"],[$i_Flag="0"]])

$ar_Array=StringSplit($s_String,$s_Delimiter)

local $ar_Array_0[ubound($ar_Array)-1]

for $i=0 to ubound($ar_Array)-2

$ar_Array_0[$i]=$ar_Array[$i+1]

Next

return $ar_Array_0

EndFunc ;==>_StringSplit_B0

Func _Array2DToD(ByRef $ar_Array, $s_Title = "Array contents", $n_Index = 1, $Line = 0, $s_i_Column = 0)

; Change Line "X" to 1 dimensional array; [Randallc - I have ***lifted it from Forum at some stage]

Local $output = ""

Local $r, $e, $Swap

If $n_Index <> 0 Then $n_Index = 1; otherwise I can't cope!

If $s_i_Column <> 0 Then $s_i_Column = 1; otherwise I can't cope!

If $Line < 0 Then $Line = 0; otherwise I can't cope!

;If $Line>UBound($ar_Array,1+($s_i_Column=0))+($n_Index=0)-2 then $Line=UBound($ar_Array,)+($n_Index=0)-2 ; otherwise I can't cope!

If $Line > UBound($ar_Array, 1+ ($s_i_Column = 0)) + ($n_Index = 0) - 2 Then $Line = UBound($ar_Array, 1+ ($s_i_Column = 0)) + ($n_Index = 0) - 2; otherwise I can't cope!

Dim $Array[uBound($ar_Array, 1 + $s_i_Column) + ($n_Index = 0) ]

$Array[0] = UBound($ar_Array, 1 + $s_i_Column) + ($n_Index = 0)

If Not IsArray($ar_Array) Then Return -1

For $r = $n_Index To UBound($ar_Array, 1 + $s_i_Column) - 1

$e = $r

$NewLine = $Line

If $s_i_Column = 1 Then

$NewLine = $r

$e = $Line

EndIf

$Array[$r+ ($n_Index = 0) ] = $ar_Array[$e][$NewLine]

Next

;_ArrayDisplay($Array,$s_Title&"Line"&$Line)

Return $Array

EndFunc ;==>_Array2DTo1D

Func _Array2DTranspose(ByRef $ar_Array)

If IsArray($ar_Array) Then

Dim $ar_ExcelValueTrans[uBound($ar_Array, 2) ][uBound($ar_Array, 1) ] ;ubound($s_i_ExcelValue,2)-1, ubound($s_i_ExcelValue,1)-1)

For $j = 0 To UBound($ar_Array, 2) - 1

For $numb = 0 To UBound($ar_Array, 1) - 1

$ar_ExcelValueTrans[$j][$numb] = $ar_Array[$numb][$j]

Next

Next

$ar_Array = $ar_ExcelValueTrans

Else

MsgBox(0, "", "No Array to transpose")

EndIf

EndFunc ;==>_ArrayTranspose2D

Func _Array2DCreateFromArray($ar1_Array_Strings)

$ar2_Array=_Array2DCreateFirstRow($ar1_Array_Strings[0])

for $i= 1 to ubound ($ar1_Array_Strings)-1

_ArrayInsert2D($ar2_Array, $ar1_Array_Strings[$i])

Next

Return $ar2_Array

EndFunc ;==>_Array2DCreateFromArray

Sorry, I could'nt add any more attachments.

Best, Randall

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...