Last active
August 22, 2023 09:09
-
-
Save tmplinshi/7e2d75794e58def0d43e to your computer and use it in GitHub Desktop.
Create excel file from array or listview https://autohotkey.com/boards/viewtopic.php?f=6&t=51022
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
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)' | |
ChangeLog: | |
v1.05 (2017-8-17) - Added "Column AutoFit"; Added .xls file support | |
v1.04 (2016-12-9) - 'IncludeLvHeader' option now doesn't need to change default listview | |
v1.03 (2016-12-9) - Added support for LV_Colors's AlternateRows/AlternateCols | |
- Added 'IncludeLvHeader' option | |
v1.02 (2016-12-8) - Fixed some color bugs | |
v1.01 (2016-12-8) - Added support for saving listview cell colors | |
*/ | |
Class Excel { | |
CreateByArray(outputFile, arr, colCount="") { | |
this.Array_To_SafeArray(arr, SafeArray, colCount) | |
this.SafeArray_To_Excel(SafeArray, outputFile) | |
} | |
CreateByHLV(outputFile, HLV, Obj_LV_Colors := "", IncludeLvHeader := false) { | |
ControlGet, lvData, List,,, ahk_id %HLV% | |
ControlGet, rowCount, List, Count,, ahk_id %HLV% | |
ControlGet, colCount, List, Count Col,, ahk_id %HLV% | |
SafeArray := ComObjArray(VT_VARIANT:=12, rowCount, colCount) | |
Loop, Parse, lvData, `n, `r | |
{ | |
row := A_Index - 1 | |
Loop, Parse, A_LoopField, %A_Tab% | |
SafeArray[row, A_Index-1] := A_LoopField | |
} | |
this.HLV := HLV | |
this.SafeArray_To_Excel(SafeArray, outputFile, Obj_LV_Colors, IncludeLvHeader) | |
} | |
Class ApplyColors | |
{ | |
DoIt(SafeArray, oExcel, Obj_LV_Colors) { | |
if !IsObject(Obj_LV_Colors) | |
return | |
sheet := oExcel.ActiveSheet | |
this.rowCount := SafeArray.MaxIndex(1) + 1 | |
this.colCount := SafeArray.MaxIndex(2) + 1 | |
this.Handle_AlternateRows(Obj_LV_Colors, sheet) | |
this.Handle_Rows(Obj_LV_Colors, sheet) | |
this.Handle_AlternateCols(Obj_LV_Colors, sheet) | |
this.Handle_Cells(Obj_LV_Colors, sheet) | |
} | |
Handle_AlternateRows(Obj_LV_Colors, sheet) { | |
if !Obj_LV_Colors.AltRows || (this.rowCount < 2) | |
return | |
Loop, % this.rowCount { | |
if !Mod(A_Index, 2) && !Obj_LV_Colors.Rows.HasKey(A_Index) { | |
cell1 := sheet.Cells(A_Index, 1) | |
cell2 := sheet.Cells(A_Index, this.colCount) | |
if Obj_LV_Colors.HasKey("ARB") | |
try sheet.Range(cell1, cell2).Interior.Color := Obj_LV_Colors.ARB | |
if Obj_LV_Colors.HasKey("ART") | |
try sheet.Range(cell1, cell2).Font.Color := Obj_LV_Colors.ART | |
} | |
} | |
} | |
Handle_AlternateCols(Obj_LV_Colors, sheet) { | |
if !Obj_LV_Colors.AltCols || (this.colCount < 2) | |
return | |
Loop, % this.colCount { | |
if !Mod(A_Index, 2) { | |
cell1 := sheet.Cells(1, A_Index) | |
cell2 := sheet.Cells(this.rowCount, A_Index) | |
if Obj_LV_Colors.HasKey("ACB") | |
try sheet.Range(cell1, cell2).Interior.Color := Obj_LV_Colors.ACB | |
if Obj_LV_Colors.HasKey("ACT") | |
try sheet.Range(cell1, cell2).Font.Color := Obj_LV_Colors.ACT | |
} | |
} | |
} | |
Handle_Rows(Obj_LV_Colors, sheet) { | |
For nRow, oColor in Obj_LV_Colors.Rows { | |
if Obj_LV_Colors.IsStatic { | |
nRow := this.MapIDToIndex(nRow, Obj_LV_Colors.HWND) | |
} | |
cell1 := sheet.Cells(nRow, 1) | |
cell2 := sheet.Cells(nRow, this.colCount) | |
if oColor.HasKey("B") | |
try sheet.Range(cell1, cell2).Interior.Color := oColor.B | |
if oColor.HasKey("T") | |
try sheet.Range(cell1, cell2).Font.Color := oColor.T | |
} | |
} | |
Handle_Cells(Obj_LV_Colors, sheet) { | |
For nRow, oCols in Obj_LV_Colors.Cells { | |
if Obj_LV_Colors.IsStatic { | |
nRow := this.MapIDToIndex(nRow, Obj_LV_Colors.HWND) | |
} | |
For nCol, oColor in oCols { | |
if oColor.HasKey("B") | |
try sheet.Cells(nRow, nCol).Interior.Color := oColor.B | |
if oColor.HasKey("T") | |
try sheet.Cells(nRow, nCol).Font.Color := oColor.T | |
} | |
} | |
} | |
MapIDToIndex(ID, HWND) { | |
SendMessage, 0x10B5, % ID, 0, , % "ahk_id " . HWND ; LVM_MAPIDTOINDEX | |
Return ErrorLevel + 1 | |
} | |
} | |
Array_To_SafeArray(arr, ByRef SafeArray, colCount="") { | |
rowCount := arr.MaxIndex() | |
If !colCount | |
colCount := IsObject(arr.1) ? arr.1.MaxIndex() : 1 | |
SafeArray := ComObjArray(VT_VARIANT:=12, rowCount, colCount) | |
Loop, % arr.MaxIndex() | |
{ | |
row := A_Index - 1 | |
If IsObject( subArr := arr[A_Index] ) | |
Loop, % subArr.MaxIndex() | |
SafeArray[row, A_Index-1] := subArr[A_Index] | |
Else | |
SafeArray[row, 0] := arr[A_Index] | |
} | |
} | |
SafeArray_To_Excel(SafeArray, ExcelFile, Obj_LV_Colors="", IncludeLvHeader=false) { | |
If !InStr(ExcelFile, ":") ; Ensure is fullpath | |
ExcelFile := A_ScriptDir "\" ExcelFile | |
xl := ComObjCreate("Excel.Application") | |
xl.DisplayAlerts := False | |
xl.Workbooks.Add | |
rowCount := SafeArray.MaxIndex(1) + 1 | |
colCount := SafeArray.MaxIndex(2) + 1 | |
sheet := xl.activeSheet | |
cell1 := sheet.cells(1, 1) | |
cell2 := sheet.cells(rowCount, colCount) | |
sheet.Range(cell1, cell2).NumberFormatLocal := "@" ; Set format to Text | |
sheet.Range(cell1, cell2).value := SafeArray | |
col_start := sheet.Columns(1) | |
col_end := sheet.Columns(colCount) | |
sheet.Range(col_start, col_end).EntireColumn.AutoFit | |
if Obj_LV_Colors { | |
this.ApplyColors.DoIt(SafeArray, xl, Obj_LV_Colors) | |
} | |
if IncludeLvHeader { | |
sheet.Rows("1:1").Insert(xlDown:=-4121) | |
cell1 := sheet.cells(1, 1) | |
cell2 := sheet.cells(1, colCount) | |
sheet.Range(cell1, cell2).value := this.Build_LvHdr_SafeArr(colCount) | |
sheet.Range(cell1, cell2).Font.Bold := true | |
} | |
if (ExcelFile ~= "i)\.xls$") | |
XlFileFormat := 56 ; xlExcel8 | |
else | |
XlFileFormat := 51 ; xlWorkbookDefault | |
sheet.SaveAs(ExcelFile, XlFileFormat) | |
xl.Quit | |
} | |
Build_LvHdr_SafeArr(colCount) { | |
SafeArray_Hdr := ComObjArray(VT_VARIANT:=12, 1, colCount) | |
Loop %colCount% { | |
SafeArray_Hdr[0, A_Index-1] := this.LV_GetHeaderText(this.HLV, A_Index) | |
} | |
return SafeArray_Hdr | |
} | |
LV_GetHeaderText(hwndLV, ColumnNumber) { | |
static LVM_GETHEADER := 0x101F | |
, HDM_GETITEM := A_IsUnicode ? 0x120B : 0x1203 ; HDM_GETITEMW : HDM_GETITEMA | |
, HDI_TEXT := 0x0002 | |
SendMessage, LVM_GETHEADER, 0, 0,, ahk_id %hwndLV% | |
if !(hWndHeader := ErrorLevel) | |
return | |
VarSetCapacity(hdi, 48, 0) | |
VarSetCapacity(HDITEMTEXT, 1024 * (A_IsUnicode ? 2 : 1), 0) | |
NumPut(HDI_TEXT, hdi, 0, "UInt") | |
NumPut(&HDITEMTEXT, hdi, 8, "Ptr") | |
NumPut(1024, hdi, 8 + (A_PtrSize * 2), "Int") | |
SendMessage, HDM_GETITEM, ColumnNumber-1, &hdi,, ahk_id %hWndHeader% | |
return StrGet( NumGet(hdi, 8) ) | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#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) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment