ExcelToArray - Read excel data to AHK array

[tmplinshi]

Source (GitHub)

Usage
arr := ExcelToArray(FileName, nSheet, last_row, last_column)

• FileName - The excel file path.
• nSheet - (Optional) Sheet number. Default is 1.
• last_row - (Optional) Last row number.
• last_column - (Optional) Last column number.

Example of an output array:

Code: Select all

[
  ["a1", "b1", "c1"],
  ["a2", "b2", "c2"]
]

Example: Load excel data to Listview

Code: Select all

#NoEnv
SetBatchLines -1
#Include ExcelToArray.ahk

Gui, Add, ListView, xm w700 r10 Grid, A|B|C|D|E|F|G|H|I
Loop, 9
	LV_ModifyCol(A_Index, 75)
Gui, Show,, Get Listview data from excel

SplashTextOn, , , Loading...
Gosub, ImportData
SplashTextOff
Return

ImportData:
	arr := ExcelToArray("test.xlsx")

	for i, dat in arr
		LV_Add("", dat*)
Return

GuiClose:
ExitApp

Note: This function uses sheet.Range(cell_begin, cell_end).FormulaR1C1 (instead of .Value) to read the data, it can avoids the numbers such as 1.2 converted into 1.200000. But if the cell contains formula, the formula code itself will be read, not the actual value.

Related script: Create excel file from array or listview

[burque505]

@tmplinshi, thank you! That works very well. Nice trick using dat* to add any number of colums.
Regards,
burque505

[tmplinshi]

Replaced ComObjGet with ComObjCreate. ComObjGet has issues on some Excel versions (or Windows version?), it will make the already opened Excel window blank.

[burque505]

Thanks for the update. This is extremely useful.
Regards, burque505

[Leo_Flo]

This is a great script. And it would help me a lot. However I am a newbie to AHK. I understand that the data is stored as an object array, but I could not figure out how to get the data from the array. Could someone help here? How do I get the data out of the array?
I tried this in the example, but it doesn't work:

Code: Select all

arr := ExcelToArray("test.xlsx",1,"","")
Loop, %arr0%
	MsgBox % arr%A_Index%
	

This is also not working:

Code: Select all

arr := ExcelToArray("test.xlsx",1,"","")
Loop, 6
	MsgBox % arr[A_Index]
	

[Leo_Flo]

Ok, I got it now. It's arr[1,2] for the first row and second column.

Code: Select all

arr := ExcelToArray("test.xlsx",1,"","")
NR=0
loop,4
{
	NR++
	loop,9
		msgbox,4096,%A_INDEX% / %NR%, % arr[NR,A_INDEX]
	
}

[Xeo786]

Would it not be better if we use ComObjGet instead of ComObjCreate?

Code: Select all

	GetSafeArrFromXlFile(FileName, nSheet := 1, last_row := "", last_column := "")
	{
		fPath := this.GetFullPath(FileName)

		;if this.IsFileInUse(fPath) {
			; try wb := this.GetWorkbook(fpath) 
		;}
		if !wb {
			; xlObj := ComObjCreate("Excel.Application")
			; xlObj.Workbooks.Open(fPath)
			;wb := xlObj.ActiveWorkbook
			wb:= ComObjGet(fPath)
		}

		safeArr := this.GetSafeArr(wb, nSheet, last_row, last_column)

		xlObj.Quit

		return safeArr
	}
	
	/* it doesn't matter even if workbook is open
		GetWorkbook(fPath)
	{
		xls := ComObjActive("Excel.Application")

		Loop, % xls.WorkBooks.Count
		{
			if ( xls.WorkBooks(A_Index).FullName = fPath )
				return xls.WorkBooks(A_Index)
		}
	}
	*/

now we do not need xlfind

Code: Select all

	GetSafeArr(oWorkbook, nSheet := 1, last_row := "", last_column := "")
	{
		sheet := oWorkbook.Sheets(nSheet)
		
		if (last_row  = "") or (last_column = "")
			return sheet.UsedRange.FormulaR1C1
		else
		{
			cell_begin := sheet.cells(1, 1)
			cell_end   := sheet.cells(last_row, last_column)
			return safeArr := sheet.Range(cell_begin, cell_end).FormulaR1C1
		}
		
	}

[Xeo786]

Thank you so much @tmplinshi, now AHK class is more clear to me,

Replaced ComObjGet with ComObjCreate. ComObjGet has issues on some Excel versions (or Windows version?), it will make the already opened Excel window blank.

comobjget working for me, it was because of line 77 condition "if last_row && last_column" the code did not worked for me I changed it

Code: Select all

ExcelToArray(FileName, nSheet := 1, last_row := "", last_column := "")
{
	return ExcelToArray.DoIt(FileName, nSheet, last_row, last_column)
}

class ExcelToArray
{
	DoIt(FileName, nSheet := 1, last_row := "", last_column := "")
	{
		if !FileExist(FileName)
			throw, "File Not Exist!"
		
		safeArr := this.GetSafeArrFromXlFile(FileName, nSheet, last_row, last_column)
		ret := this.SafeArr_To_AHKArr(safeArr)
		return ret
	}
	
	GetSafeArrFromXlFile(FileName, nSheet := 1, last_row := "", last_column := "")
	{
		fPath := this.GetFullPath(FileName)
		if !wb {
			wb:= ComObjGet(fPath)
		}
		safeArr := this.GetSafeArr(wb, nSheet, last_row, last_column)
		xlObj.Quit
		return safeArr
	}
	
	SafeArr_To_AHKArr(SafeArr)
	{
		ret := []
		
		rowCount := SafeArr.MaxIndex(1)
		colCount := SafeArr.MaxIndex(2)
		
		Loop, % rowCount
		{
			row := A_Index
			
			arr := []
			Loop, % colCount
				arr.push( SafeArr[row, A_Index] )
			
			ret.push(arr)
		}
		
		return ret
	}
	
	GetSafeArr(oWorkbook, nSheet := 1, last_row := "", last_column := "")
	{
		sheet := oWorkbook.Sheets(nSheet)
		
		if (last_row  = "") or (last_column = "")
			return sheet.UsedRange.FormulaR1C1
		else
		{
			cell_begin := sheet.cells(1, 1)
			cell_end   := sheet.cells(last_row, last_column)
			return safeArr := sheet.Range(cell_begin, cell_end).FormulaR1C1
		}
		
	}
	
	GetFullPath(FileName)
	{
		Loop, % FileName
			return A_LoopFileLongPath
	}
	
}

[Chunjee]

This made my little excel project super easy. Awesome and thank you.