Class_Excel - Create excel file from array or listview

[tmplinshi]

Posted 2 years ago on chinese subforum, I guess I should post here.

Source

Functions:

• CreateByArray(outputFile, arr)
• CreateByHLV(outputFile, HLV, Obj_LV_Colors := "", IncludeLvHeader := false)
  • HLV - Listview hwnd
  • Obj_LV_Colors - Created by using 'New LV_Colors(HLV)'

Example1 - Array to excel

Code: Select all

#Include Class_Excel.ahk

arr := [ ["Programming Language", "URL"]
       , ["AutoHotkey", "https://autohotkey.com"]
       , ["FreeBASIC", "https://freebasic.net/"]
       , ["newLISP", "http://www.newlisp.org/"]
       , ["LÖVE", "https://love2d.org/"]
       , ["aardio", "http://www.aardio.com/"] ]
       
Excel.CreateByArray("test.xlsx", arr)

Example2 - Listview to excel

Code: Select all

#Include Class_Excel.ahk

Gui, Add, ListView, w500 h200 Grid HwndHLV, Colum1|Colum2|Colum3
Loop, 5
	LV_Add("", A_Index "-1", A_Index "-2", A_Index "-3")
Gui, Add, Button, , ExportToExcel
Gui, Show
Return

ButtonExportToExcel:
	Excel.CreateByHLV("HLV.xlsx", HLV)
Return

GuiClose:
ExitApp

Example3 - Listview to excel (include colors)

Code: Select all

#Include, <Class_LV_Colors> ; https://autohotkey.com/boards/viewtopic.php?t=1081
#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, 0x92F7F6)
CLV.Cell(2, 2,, 0xff0000)
CLV.Cell(3, 2, 0xff0000)
CLV.AlternateCols(0xFBE99C, 0xF8930F)
CLV.AlternateRows(0xD6FBD6, 0xF80F0F)
Gui, Show

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

GuiClose:
ExitApp

Related script: ExcelToArray

[burque505]

@tmplinshi, thanks for this also! For those who may not have the classes, I've attached a .7z archive with your examples, Class_Excel.ahk and Class_LV_Colors.ahk.
By the way, have you done anything with EPPlus.dll? I have seen your post here about ExcelLibrary, and I thought you might be interested in EPPlus.

Create advanced Excel spreadsheets using .NET, without the need of interop.
EPPlus is a .NET library that reads and writes Excel files using the Office Open XML format (xlsx). EPPlus has no dependencies other than .NET.

I've been trying to use it with Clr_LoadLibrary(), but no luck so far.
Regards,
burque505

tmplinshi.7z

(5.96 KiB) Downloaded 74 times

[tmplinshi]

@burque505 I tried some hours on EPPlus, it's really like solving a puzzle . Fortunately I finally made some working code, I've replied in your topic Help with EPPlus.dll and AHK.

[burque505]

I saw that, thank you!!!!!! After using dnspy that qwerty12 mentioned, I thought, gee, this is going to be easy. Everything is listed! But .
That is really great, you made my weekend.
Regards,
burque505