ExcelToArray.ahk

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

class ExcelToArray
{
	DoIt(FileName, nSheet := 1)
	{
		if !FileExist(FileName)
			throw, "File Not Exist!"
		
		safeArr := this.GetSafeArrFromXlFile(FileName, nSheet)
		ret := this.SafeArr_To_AHKArr(safeArr)
		return ret
	}

	GetSafeArrFromXlFile(FileName, nSheet := 1)
	{
		fPath := this.GetFullPath(FileName)
		bFileInUse := this.IsFileInUse(fPath)

		xl := ComObjGet(fPath)
		safeArr := this.GetSafeArr(xl, nSheet)

		if !bFileInUse
			xl.close()

		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(xl, nSheet := 1)
	{
		sheet := xl.Sheets(nSheet)

		lastCell := this.xlFindLastCell(xl, nSheet)
		cell_begin := sheet.cells(1, 1)
		cell_end   := sheet.cells(lastCell.row, lastCell.column)

		return safeArr := sheet.Range(cell_begin, cell_end).FormulaR1C1
	}

	GetFullPath(FileName)
	{
		Loop, % FileName
			return A_LoopFileLongPath
	}

	IsFileInUse(FileName)
	{
		return FileExist(FileName) && !FileOpen(FileName, "rw")
	}

	xlFindLastCell(xl, sheet := 1)
	{
		static xlByRows    := 1
		     , xlByColumns := 2
		     , xlPrevious  := 2

		lastRow := xl.Sheets(sheet).Cells.Find("*", , , , xlByRows   , xlPrevious).Row
		lastCol := xl.Sheets(sheet).Cells.Find("*", , , , xlByColumns, xlPrevious).Column

		return {row: lastRow, column: lastCol}
	}
}