In the scenario I have added below, I can bring the data in the excel file named Test.xlsm to the listview in the gui.
I'm asking for your help with one thing.
- I want the listview titles, the red text in the first line in the excel file to come as the title.
- also, I want the red text in the first line of the excel file to come as a title for the tags up to vText1, vText2 .... vText21.
Thank you in advance for your help.
Code: Select all
#NoEnv
#SingleInstance Force
SetBatchLines -1
Gui, Add, Edit, x22 y39 w130 h20 vCol1, Edit
Gui, Add, Edit, x162 y39 w130 h20 vCol2, Edit
Gui, Add, Edit, x302 y39 w130 h20 vCol3, Edit
Gui, Add, Edit, x442 y39 w130 h20 vCol4, Edit
Gui, Add, Edit, x582 y39 w130 h20 vCol5, Edit
Gui, Add, Edit, x722 y39 w130 h20 vCol6, Edit
Gui, Add, Edit, x862 y39 w130 h20 vCol7, Edit
Gui, Add, Edit, x22 y89 w130 h20 vCol8, Edit
Gui, Add, Edit, x162 y89 w130 h20 vCol9, Edit
Gui, Add, Edit, x302 y89 w130 h20 vCol10, Edit
Gui, Add, Edit, x442 y89 w130 h20 vCol11, Edit
Gui, Add, Edit, x582 y89 w130 h20 vCol12, Edit
Gui, Add, Edit, x722 y89 w130 h20 vCol13, Edit
Gui, Add, Edit, x862 y89 w130 h20 vCol14, Edit
Gui, Add, Edit, x22 y139 w130 h20 vCol15, Edit
Gui, Add, Edit, x162 y139 w130 h20 vCol16, Edit
Gui, Add, Edit, x302 y139 w130 h20 vCol17, Edit
Gui, Add, Edit, x442 y139 w130 h20 vCol18, Edit
Gui, Add, Edit, x582 y139 w130 h20 vCol19, Edit
Gui, Add, Edit, x722 y139 w130 h20 vCol20, Edit
Gui, Add, Edit, x862 y139 w130 h20 vCol21, Edit
Gui, Add, Text, x22 y19 w130 h20 vText1,
Gui, Add, Text, x162 y19 w130 h20 vText2,
Gui, Add, Text, x302 y19 w130 h20 vText3,
Gui, Add, Text, x442 y19 w130 h20 vText4,
Gui, Add, Text, x582 y19 w130 h20 vText5,
Gui, Add, Text, x722 y19 w130 h20 vText6,
Gui, Add, Text, x862 y19 w130 h20 vText7,
Gui, Add, Text, x22 y69 w130 h20 vText8,
Gui, Add, Text, x162 y69 w130 h20 vText9,
Gui, Add, Text, x302 y69 w130 h20 vText10,
Gui, Add, Text, x442 y69 w130 h20 vText11,
Gui, Add, Text, x582 y69 w130 h20 vText12,
Gui, Add, Text, x722 y69 w130 h20 vText13,
Gui, Add, Text, x862 y69 w130 h20 vText14,
Gui, Add, Text, x22 y119 w130 h20 vText15,
Gui, Add, Text, x162 y119 w130 h20 vText16,
Gui, Add, Text, x302 y119 w130 h20 vText17,
Gui, Add, Text, x442 y119 w130 h20 vText18,
Gui, Add, Text, x582 y119 w130 h20 vText19,
Gui, Add, Text, x722 y119 w130 h20 vText20,
Gui, Add, Text, x862 y119 w130 h20 vText21,
Gui, Add, Button, x22 y599 w100 h30 gGetImportData , File Load
Gui, Add, Button, x132 y599 w100 h30 , Button
Gui, Add, Button, x242 y599 w100 h30 , Button
Gui, Add, Button, x352 y599 w100 h30 , Button
Gui, Add, Button, x462 y599 w100 h30 , Button
Gui, Add, Button, x572 y599 w100 h30 , Button
Gui, Add, Button, x682 y599 w100 h30 , Button
Gui, Add, Button, x792 y599 w100 h30 , Button
Gui, Add, Button, x902 y599 w100 h30 , Button
Gui, Add, ListView, AltSubmit grid -Multi +hscroll LV0x1 x22 y169 w980 h340 vLV1 gLV1 HWNDhLV, ||||||||||||||||||||||||||||||||||||||||
LvCtrl_SetWindowTheme(hLV)
LvCtrl_SetRowHeight(10)
Loop, 21
LV_ModifyCol(A_Index, 100)
Gui, Show, w1019 h648, TEST GUI
return
LV1:
if !(A_GuiEvent == "I")
return
if A_EventInfo
{
ControlGet, values, List, Selected,, ahk_id %hLV%
values := StrSplit(values, "`t")
}
else
values := ""
Loop, 21
GuiControl,, Col%A_Index%, % values[A_Index]
return
ImportData:
SplashTextOn, , , Loading...
arr := ExcelToArray(ExcelFile,,, 21)
for i, dat in arr
LV_Add("", dat*)
arr := ""
SplashTextOff
gosub GuiSize
Return
GetImportData:
LV_DELETE()
SplashTextOn, , , Loading...
;~ path = %A_ScriptDir%\
arr := ExcelToArray("Test.xlsm")
for i, dat in arr
LV_Add("", dat*)
arr := ""
SplashTextOff
gosub GuiSize
Return
GuiSize:
LV_ModifyCol(2,0)
Loop,21
LV_ModifyCol(A_Index+1,"AutoHdr")
Return
GuiClose:
ExitApp
return
LvCtrl_SetRowHeight(Height) {
LV_SetImageList( DllCall( "ImageList_Create", Int,2, Int, Height, Int,0x18, Int,1, Int,1 ), 1 )
}
LvCtrl_SetWindowTheme(hLV) {
DllCall("UxTheme.dll\SetWindowTheme", "Ptr", hLV, "WStr", "Explorer", "Ptr", 0)
GuiControl, +LV0x14000, %hLV%
}
; v1.0 (2018-6-22)
; 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)
bFileInUse := this.IsFileInUse(fPath)
xl := ComObjGet(fPath)
safeArr := this.GetSafeArr(xl, nSheet, last_row, last_column)
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, last_row := "", last_column := "")
{
sheet := xl.Sheets(nSheet)
if last_row && last_column
lastCell := {row: last_row, column: last_column}
else
{
lastCell := this.xlFindLastCell(xl, 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(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}
}
}