ListView数据或数组生成Excel

许多实用脚本和封装函数, 可以让您编写脚本更加便捷高效

Moderators: tmplinshi, arcticir

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

ListView数据或数组生成Excel

26 Sep 2015, 10:40

生成 Excel 文件主要有几下几个方法:
  1. 把数据保存到剪贴板,然后一次性粘贴到 Excel
  2. 逐个设置每个单元格的数据...
  3. 设置 Excel 某个矩形区域的值为安全数组(SafeArray)
    示例
Class_Excel.ahk 用的是方法3。

例1 - 用数组生成 Excel 文件

Code: Select all

#Include Class_Excel.ahk

arr := [ ["编程语言", "官网"]
       , ["AutoHotkey", "http://ahkscript.org"]
       , ["aardio", "http://bbs.aau.cn/"] ]

Excel.CreateByArray("test.xlsx", arr)
例 2 - 用 ListView 数据生成 Excel 文件

Code: Select all

#Include Class_Excel.ahk

Gui, Add, ListView, w500 h200 Grid HwndHLV, 第一列|第二列|第三列
Loop, 5
	LV_Add("", A_Index "-1", A_Index "-2", A_Index "-3")
Gui, Add, Button, , 导出列表到Excel
Gui, Show
Return

Button导出列表到Excel:
	Excel.CreateByHLV("HLV.xlsx", HLV)
	MsgBox, 导出完毕!
Return

GuiClose:
ExitApp
Class_Excel.ahk --> https://gist.github.com/tmplinshi/7e2d75794e58def0d43e
bolang
Posts: 2
Joined: 14 Jan 2014, 09:44

Re: ListView数据或数组生成Excel

29 Oct 2015, 04:59

谢谢,最近一直在处理excel,用VBA方便很多。
Marvin1023
Posts: 45
Joined: 10 Nov 2015, 12:49

Re: ListView数据或数组生成Excel

07 Dec 2016, 23:10

This work with [Class] LV_Colors ?
Asus Maximus VIII Extrême - DDR4 16Go - Intel Core i7 - GTX 1080
tmplinshi
Posts: 1604
Joined: 01 Oct 2013, 14:57

Re: ListView数据或数组生成Excel

07 Dec 2016, 23:37

Marvin1023 wrote:This work with [Class] LV_Colors ?
Hi, this library is used to creating Excel file, data either from array or listview.
  • Excel.CreateByArray(OutputFileName, InputArray)
  • Excel.CreateByHLV(OutputFileName, ListViewHwnd)
LV_Colors does not affect ListView data. So yes.
Marvin1023
Posts: 45
Joined: 10 Nov 2015, 12:49

Re: ListView数据或数组生成Excel

07 Dec 2016, 23:41

Thank for reply
I will look at how to add color in excel file.
Asus Maximus VIII Extrême - DDR4 16Go - Intel Core i7 - GTX 1080
tmplinshi
Posts: 1604
Joined: 01 Oct 2013, 14:57

Re: ListView数据或数组生成Excel

08 Dec 2016, 00:13

Oh, I misunderstood you question before. I thought you were asking "is it working", but your real qustion is "Does this library save cell colors as well to the Excel file?". :) Well, I'll have a try.
tmplinshi
Posts: 1604
Joined: 01 Oct 2013, 14:57

Re: ListView数据或数组生成Excel

08 Dec 2016, 02:41

v1.01 (2016-12-8) - Added support for saving listview cell colors

Example:

Code: Select all

#Include, <Class_LV_Colors>
#Include, Class_Excel.ahk
Gui, Add, ListView, w400 r10 hwndHLV, aaaaaaa|bbbbbbb|cccc
Loop, 5
	LV_Add("", "Col1" A_Index, "Col2" A_Index, "Col3" A_Index)

CLV := New LV_Colors(HLV)
CLV.Row(2, 0xA6F9A6)
CLV.Cell(2, 2,, 0xff0000)
CLV.Cell(3, 2, 0xff0000)
Gui, Show

Excel.CreateByHLV("color.xlsx", HLV, CLV)
Return

GuiClose:
ExitApp
tmplinshi
Posts: 1604
Joined: 01 Oct 2013, 14:57

Re: ListView数据或数组生成Excel

08 Dec 2016, 04:19

v1.02 (2016-12-8) - Fixed some color bugs
Marvin1023
Posts: 45
Joined: 10 Nov 2015, 12:49

Re: ListView数据或数组生成Excel

08 Dec 2016, 10:14

Omg. good job :)
Asus Maximus VIII Extrême - DDR4 16Go - Intel Core i7 - GTX 1080
Marvin1023
Posts: 45
Joined: 10 Nov 2015, 12:49

Re: ListView数据或数组生成Excel

08 Dec 2016, 14:22

work with LV_Colors.Cell() and LV_Colors.Row()

Can you add LV_Colors.AlternateRows() and LV_Colors.AlternateCols() And Title column of Gui ListView.

I think your script will be complete.

Thank you.
Asus Maximus VIII Extrême - DDR4 16Go - Intel Core i7 - GTX 1080
tmplinshi
Posts: 1604
Joined: 01 Oct 2013, 14:57

Re: ListView数据或数组生成Excel

09 Dec 2016, 04:03

Done.

Code: Select all

v1.03 (2016-12-9) - Added support for LV_Colors's AlternateRows/AlternateCols
                  - Added 'IncludeLvHeader' option
Marvin1023
Posts: 45
Joined: 10 Nov 2015, 12:49

Re: ListView数据或数组生成Excel

09 Dec 2016, 05:20

Powerful :roll:
thank you very much :)

with two listview or more use:

Code: Select all

#Include, <CLASS_LV_COLORS>
#Include, <CLASS_EXCEL>

Gui, Add, ListView, x5 y5 w200 r10 vCTRL_LV1 hwndHLV1, aaaaaaa|bbbbbbb|cccccc|dddddd
Loop, 5
	LV_Add("", "Col1" A_Index, "Col2" A_Index, "Col3" A_Index, "Col4" A_Index)

CLV1 := New LV_Colors(HLV1)
CLV1.Row(2, 0xA6F9A6)
CLV1.Cell(2, 2,, 0xff0000)
CLV1.Cell(3, 2, 0xff0000)

Gui, Add, ListView, x5 y205 w200 r10 vCTRL_LV2 hwndHLV2, aaaaaaa|bbbbbbb|cccccc
Loop, 5
	LV_Add("", "Col1" A_Index, "Col2" A_Index, "Col3" A_Index)

CLV2 := New LV_Colors(HLV2)
CLV2.Row(2, 0xFF00CC)
CLV2.Cell(2, 2,, 0x50BFE6)
CLV2.Cell(3, 2, 0xFF355E)


Gui, Add, ListView, x210 y5 w200 r10 vCTRL_LV3 hwndHLV3, aaaaaaa|bbbbbbb
Loop, 10
	LV_Add("", "Col1" A_Index, "Col2" A_Index)

CLV3 := New LV_Colors(HLV3)
CLV3.AlternateRows(0xFF00CC)


Gui, Add, ListView, x210 y210 w200 r10 vCTRL_LV4 hwndHLV4, aaaaaaa|bbbbbbb|ccccccc|ddddddddd
Loop, 5
	LV_Add("", "Col1" A_Index, "Col2" A_Index, "Col3" A_Index, "Col4" A_Index)

CLV4 := New LV_Colors(HLV4)
CLV4.AlternateCols(0xFF00CC)

Gui, show

Gui, ListView, CTRL_LV1
Excel.CreateByHLV("ListView_1.xlsx", HLV1, CLV1, True)

Gui, ListView, CTRL_LV2
Excel.CreateByHLV("ListView_2.xlsx", HLV2, CLV2, True)

Gui, ListView, CTRL_LV3
Excel.CreateByHLV("ListView_3.xlsx", HLV3, CLV3, True)

Gui, ListView, CTRL_LV4
Excel.CreateByHLV("ListView_4.xlsx", HLV4, CLV4, True)
Return

GuiClose:
ExitApp
Return
Asus Maximus VIII Extrême - DDR4 16Go - Intel Core i7 - GTX 1080
tmplinshi
Posts: 1604
Joined: 01 Oct 2013, 14:57

Re: ListView数据或数组生成Excel

09 Dec 2016, 08:38

You're welcome. :)

I modified a bit.
v1.04 (2016-12-9) - 'IncludeLvHeader' option now doesn't need to change default listview
hitman
Posts: 21
Joined: 10 Aug 2014, 06:47

Re: ListView数据或数组生成Excel

13 Jan 2019, 10:50

用到了,谢谢大神

Return to “脚本函数”

Who is online

Users browsing this forum: No registered users and 15 guests