ExcelToArray/ExcelToArray.ahk at master · tmplinshi/ExcelToArray · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
; v1.1 (2018-7-19)
; https://github.com/tmplinshi/ExcelToArray

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 this.IsFileInUse(fPath) {
			try wb := this.GetWorkbook(fpath)
		}
		if !wb {
			xlObj := ComObjCreate("Excel.Application")
			xlObj.Workbooks.Open(fPath)
			wb := xlObj.ActiveWorkbook
		}

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

		xlObj.Quit

		return safeArr
	}

	GetWorkbook(fPath)
	{
		xls := ComObjActive("Excel.Application")

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

	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 && last_column
			lastCell := {row: last_row, column: last_column}
		else
		{
			lastCell := this.xlFindLastCell(oWorkbook, nSheet)
			if last_row
				lastCell.row := last_row
			else if last_column
				lastCell.column := last_column
		}
		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(oWorkbook, sheet := 1)
	{
		static xlByRows    := 1
		     , xlByColumns := 2
		     , xlPrevious  := 2

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

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