Getting Listview headers and Text headers in the first row of Excel Topic is solved

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
chngrcn
Posts: 190
Joined: 29 Feb 2016, 08:55

Getting Listview headers and Text headers in the first row of Excel

Post by chngrcn » 10 Jun 2021, 09:51

Dear members of the forum...
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}
	}
}


Attachments
Test1.ahk
(5.97 KiB) Downloaded 18 times

[The extension xlsm has been deactivated and can no longer be displayed.]

User avatar
Xeo786
Posts: 759
Joined: 09 Nov 2015, 02:43
Location: Karachi, Pakistan

Re: Getting Listview headers and Text headers in the first row of Excel

Post by Xeo786 » 24 Jun 2021, 02:11

first row can be separated from array using for loop and a_index (i) and LV_ModifyCol can also change titles following changing in your code are suppose to put all heading

Code: Select all

GetImportData:
LV_DELETE()
SplashTextOn, , , Loading...
;~ path = %A_ScriptDir%\
arr := ExcelToArray("Test.xlsm")

for i, dat in arr
	if i = 1
		for k, v in dat
			LV_ModifyCol(k, , v)
	else
		LV_Add("", dat*)

arr := ""
SplashTextOff
gosub GuiSize
Return
"When there is no gravity, there is absolute vacuum and light travel with no time" -Game changer theory
chngrcn
Posts: 190
Joined: 29 Feb 2016, 08:55

Re: Getting Listview headers and Text headers in the first row of Excel

Post by chngrcn » 25 Jun 2021, 03:31

Xeo786 wrote:
24 Jun 2021, 02:11
first row can be separated from array using for loop and a_index (i) and LV_ModifyCol can also change titles following changing in your code are suppose to put all heading

Firstly, thanks for your response.
it makes sense to assign the first line to the listview headers.
I want to assign titles to the label. How can I do that?

Code: Select all

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, 
User avatar
Xeo786
Posts: 759
Joined: 09 Nov 2015, 02:43
Location: Karachi, Pakistan

Re: Getting Listview headers and Text headers in the first row of Excel  Topic is solved

Post by Xeo786 » 26 Jun 2021, 03:03

You can use GuiControl while assigning first row to listview's heading, the same way you are using GuiControl in label LV1

Code: Select all

GetImportData:
LV_DELETE()
SplashTextOn, , , Loading...
;~ path = %A_ScriptDir%\
arr := ExcelToArray("Test.xlsm")

for i, dat in arr
{	
	if i = 1
	{	
		for k, v in dat
		{
			LV_ModifyCol(k, , v)
			GuiControl,, Text%k%, % v
		}
	}
	else
		LV_Add("", dat*)
}
arr := ""
SplashTextOff
gosub GuiSize
Return
"When there is no gravity, there is absolute vacuum and light travel with no time" -Game changer theory
Post Reply

Return to “Ask for Help (v1)”