Get Listview data from excel

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

Get Listview data from excel

22 Jun 2018, 02:06

Code: Select all

Gui, Add, Button, x502 y30 w110 h40 , Excel File Select
Gui, Add, ListView, x22 y30 w470 h450 , ListView
Gui, Add, Edit, x502 y90 w120 h30 , Column A
Gui, Add, Edit, x502 y130 w120 h30 , Column B
Gui, Add, Edit, x502 y170 w120 h30 , Column C
Gui, Add, Edit, x502 y210 w120 h30 , Column D
Gui, Add, Edit, x502 y250 w120 h30 , Column E
Gui, Add, Edit, x502 y290 w120 h30 , Column F
Gui, Add, Edit, x502 y330 w120 h30 , Column H
Gui, Show, w647 h515, Get Listview data from excel
return

GuiClose:
ExitApp

hello;
button, the file selection menu will open and only files with xls, xlsx extensions will be listed. I selected the excel file
I want to list the columns A, B, C, D, E, F, G, H in ListView.

Thanks for your help.
tmplinshi
Posts: 1604
Joined: 01 Oct 2013, 14:57

Re: Get Listview data from excel

22 Jun 2018, 03:22

Hi, I just posted a function ExcelToArray

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",,, 8)

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

GuiClose:
ExitApp
chngrcn
Posts: 190
Joined: 29 Feb 2016, 08:55

Re: Get Listview data from excel

22 Jun 2018, 05:12

Image

Did I get this error? I did not run it .. Where am I making mistakes?
tmplinshi
Posts: 1604
Joined: 01 Oct 2013, 14:57

Re: Get Listview data from excel

22 Jun 2018, 05:26

It seems it's because the editor SciTE4AutoHotkey not using the script directory to run the script.

Try specify the full path of ExcelToArray.ahk:
#Include D:\Desktop\ExcelToArray.ahk

Or if ExcelToArray.ahk is located in the same directory as the script, you can either use #Include %A_ScriptDir%\ExcelToArray.ahk

or:

Code: Select all

SetWorkingDir %A_ScriptDir%
#Include ExcelToArray.ahk
chngrcn
Posts: 190
Joined: 29 Feb 2016, 08:55

Re: Get Listview data from excel

22 Jun 2018, 05:45

Code: Select all

>"C:\Program Files\AutoHotkey\AutoHotkey.exe" /ErrorStdOut "C:\Users\POYRAZ\Desktop\1\ExcelToArray.ahk"    
C:\Users\POYRAZ\Desktop\1\ExcelToArray.ahk (36) : ==> Call to nonexistent function.
     Specifically: ExcelToArray("test.xlsx",,, 8)
>Exit code: 2    Time: 0.1619
Line 36 error.. Why ?

Image
tmplinshi
Posts: 1604
Joined: 01 Oct 2013, 14:57

Re: Get Listview data from excel

22 Jun 2018, 06:17

Wait, did you deleted the content of ExcelToArray.ahk that I provide? Don't do that.
Download this zip file, then unpack all files, then run Example.ahk.
chngrcn
Posts: 190
Joined: 29 Feb 2016, 08:55

Re: Get Listview data from excel

22 Jun 2018, 06:40

It's worked.
thank you. just select the file myself with the help of the button, as in the example I added, and click through the listview to get to the editbox which is given in that line.
User avatar
divanebaba
Posts: 804
Joined: 20 Dec 2016, 03:53
Location: Diaspora

Re: Get Listview data from excel

25 Jun 2018, 17:50

Hello Can.

You asked in PM for help.
Is this code similar to what you have desired?

Code: Select all

#NoEnv
#SingleInstance Force
SetBatchLines -1
#Include ExcelToArray.ahk 	; <--- This file has to be in same folder as your script
							; or your Lib-directory in AutoHotKey install-path (C:\Program Files\AutoHotkey\Lib)
							; otherwise, you have to determine whole path
Gui, Add, ListView, xm w700 r10 Grid, A|B|C|D|E|F|G|H|I
Loop, 9
	LV_ModifyCol(A_Index, 75)
FileSelectFile, OutputVar , , c:\Users\POYRAZ\Desktop\1\, , Excel Files (*.xls; *.xlsx)
Gosub ImportData
Gui, Show,, Get Listview data from excel
Return

ImportData:
	SplashTextOn, , , Loading...
		arr := ExcelToArray(OutputVar)
		for i, dat in arr
			LV_Add("", dat*)
	SplashTextOff
Return

GuiClose:
ExitApp
I just have modified the example from tmplinshi. His ExcelToArray.ahk is real good stuff. :salute:
chngrcn
Posts: 190
Joined: 29 Feb 2016, 08:55

Re: Get Listview data from excel

26 Jun 2018, 05:27

Code: Select all

Gui, Add, Button, x502 y30 w110 h40 , Excel File Select
Gui, Add, ListView, x22 y30 w470 h450 , ListView
Gui, Add, Edit, x502 y90 w120 h30 , Column A
Gui, Add, Edit, x502 y130 w120 h30 , Column B
Gui, Add, Edit, x502 y170 w120 h30 , Column C
Gui, Add, Edit, x502 y210 w120 h30 , Column D
Gui, Add, Edit, x502 y250 w120 h30 , Column E
Gui, Add, Edit, x502 y290 w120 h30 , Column F
Gui, Add, Edit, x502 y330 w120 h30 , Column H
Gui, Show, w647 h515, Get Listview data from excel
return

GuiClose:
ExitApp

I want to integrate this gui menu.
button, the file selection menu will open and only files with xls, xlsx extensions will be listed. I selected the excel file
I want to list the columns A, B, C, D, E, F, G, H in ListView.
If I select which line in ListView I get the data for the line in the editbox
User avatar
divanebaba
Posts: 804
Joined: 20 Dec 2016, 03:53
Location: Diaspora

Re: Get Listview data from excel

26 Jun 2018, 11:24

CODE
Nice greetings from Aachen :mrgreen: :mrgreen:
tmplinshi
Posts: 1604
Joined: 01 Oct 2013, 14:57

Re: Get Listview data from excel

26 Jun 2018, 12:43

Hi divanebaba,
You should check the event in the LV1 lable, because there will be many events occurs.

Code: Select all

#NoEnv
#SingleInstance Force
SetBatchLines -1
#Include ExcelToArray.ahk 	; <--- This file has to be in same folder as your script
							; or your Lib-directory in AutoHotKey install-path (C:\Program Files\AutoHotkey\Lib)
							; otherwise, you have to determine whole path

Gui, Font, s10
Gui, Add, ListView, grid AltSubmit x22 y30 w620 h450 vLV1 gLV1 HWNDhLV -Multi, A|B|C|D|E|F|G|H
Gui, Add, Button, x+30 w110 h40 gSelector, Excel File Select
Gui, Add, Edit, y+50 w120 h30 vCol1, Column A
Gui, Add, Edit, w120 h30 vCol2, Column B
Gui, Add, Edit, w120 h30 vCol3, Column C
Gui, Add, Edit, w120 h30 vCol4, Column D
Gui, Add, Edit, w120 h30 vCol5, Column E
Gui, Add, Edit, w120 h30 vCol6, Column F
Gui, Add, Edit, w120 h30 vCol7, Column G
Gui, Add, Edit, w120 h30 vCol8, Column H

LvCtrl_SetWindowTheme(hLV)
LvCtrl_SetRowHeight(30)
							
Loop, 9
	LV_ModifyCol(A_Index, 75)

Gui, Show,, Get Listview data from excel
Return

ImportData:
	SplashTextOn, , , Loading...
		arr := ExcelToArray(ExcelFile,,, 8)
		for i, dat in arr
			LV_Add("", dat*)
		arr := ""
	SplashTextOff
Return

GuiClose:
ExitApp

Selector:
	FileSelectFile, ExcelFile , , c:\Users\POYRAZ\Desktop\1\, , Excel Files (*.xls; *.xlsx)
	if ExcelFile
	{
		Gosub ImportData
		LV_MODIFY(1, "SELECT") ; preselect first row
	}
return



LV1:
	if !(A_GuiEvent == "I")
		return

	if A_EventInfo
	{
		ControlGet, values, List, Selected,, ahk_id %hLV%
		values := StrSplit(values, "`t")
	}
	else
		values := ""
	
	Loop, 8
		GuiControl,, Col%A_Index%, % values[A_Index]
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%
}
The number 8 in ExcelToArray(ExcelFile,,, 8) is the max column number, since you only want to read columns A/B/C/D/E/F/G/H. But if the excel only has 8 columns, then you can just use ExcelToArray(ExcelFile).
User avatar
divanebaba
Posts: 804
Joined: 20 Dec 2016, 03:53
Location: Diaspora

Re: Get Listview data from excel

26 Jun 2018, 13:07

tmplinshi wrote:Hi divanebaba,
You should check the event in the LV1 lable, because there will be many events occurs.
...
The number 8 in ExcelToArray(ExcelFile,,, 8) is the max column number, since you only want to read columns A/B/C/D/E/F/G/H. But if the excel only has 8 columns, then you can just use ExcelToArray(ExcelFile).
@tmplinshi
Thanks for your note. It was not my intention to give fully tested code. I just wanted to give chngrcn lightly direction.
You`re right. I gave him direction AND misdirection at same time. :mrgreen: :mrgreen:

EDIT: @tmplinshi - I've tested your code. Works fine, but looks not as beginner friendly as my code. Now chngrcn can choose or combine the codes.
For advanced users your code is certainly very good example how to code professional. And a nightmare for beginners. :crazy: :crazy:
tmplinshi
Posts: 1604
Joined: 01 Oct 2013, 14:57

Re: Get Listview data from excel

26 Jun 2018, 13:22

divanebaba wrote:You`re right. I gave him direction AND misdirection at same time. :mrgreen: :mrgreen:
That's what I concerned. :) That's a serious issue in the LV1 label, I just can't ignore it. :headwall:
divanebaba wrote:EDIT: @tmplinshi - I've tested your code. Works fine, but looks not as beginner friendly as my code.
Well, I agree. :shifty:
chngrcn
Posts: 190
Joined: 29 Feb 2016, 08:55

Re: Get Listview data from excel

28 Jun 2018, 01:04

Thanks for your help. there is only one problem ..

It brings the first line in the excel file. It does not list the second and third full lines.
tmplinshi
Posts: 1604
Joined: 01 Oct 2013, 14:57

Re: Get Listview data from excel

28 Jun 2018, 02:07

Did you mean the content of the cell has multiple lines, and the GUI didn't show fully? If this is the case, then increase the height of listview rows and the edit controls:

Code: Select all

LvCtrl_SetRowHeight(80)

Code: Select all

Gui, Add, Edit, y+50 w120 r3 vCol1, Column A ; 'r3' means three lines of height
chngrcn
Posts: 190
Joined: 29 Feb 2016, 08:55

Re: Get Listview data from excel

28 Jun 2018, 04:25

add delete buttons,
with the help of these buttons;
When we click on the add button, we add the data in the editboxes to the listview and update it in the excel file at the same time.

when we press the delete button;
When you delete any line through ListView, delete the data in the excel file.

as a result, you are in sync with the excel file.
User avatar
divanebaba
Posts: 804
Joined: 20 Dec 2016, 03:53
Location: Diaspora

Re: Get Listview data from excel

01 Jul 2018, 14:30

Hello chngrcn

I have add an export-code from ListView to (New)Excel-file. How to save and rename this, could be your next issue.
The code is only recommended for less data. For large data, using the csv_library is maybe better solution.
Quick & dirty

Return to “Ask for Help (v1)”

Who is online

Users browsing this forum: marypoppins_1 and 114 guests