Post by flyingDman » 03 Sep 2022, 18:51
Existem várias tentativas no fórum. Como este:
viewtopic.php?f=6&t=95251
Eu gosto deste método:
viewtopic.php?t=102348&p=454940#p454989. Instale a função em uma "library" ou inclua em seu script.
Isso requer um download da Microsoft:
https://www.microsoft.com/en-us/download/details.aspx?id=54920. Depois de instalar este "Database Engine", não há mais nenhuma ação a ser tomada.
Exemplo:
Code: Select all
datasource := "C:\Users\" A_UserName "\Scripts\ado_data_test1.xlsx"
arr := getXcells(datasource)
cols := arr.1.maxindex()
for x,y in Arr
for a,b in y
z .= (a = 1 ? "" : "`t") . b . (a = cols ? "`n" : "")
msgbox % trim(z,"`n") ;~ clipboard := trim(z,"`n")
função:
Code: Select all
getXcells(datasource, sheet := "sheet1") ; requires https://www.microsoft.com/en-us/download/details.aspx?id=54920
{
global colcnt, rowcnt
arr := [], rowcnt := 0
objConnection := ComObjCreate("ADODB.Connection"), objRecordSet := ComObjCreate("ADODB.Recordset")
try objConnection.Open("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" . dataSource . "; Extended Properties='Excel 12.0 xml;HDR=no;IMEX=1';")
catch
{
msgbox,48,, Error! Your data could not be retrieved, 2
return
}
try objRecordset.Open("Select * FROM [" Sheet "$]", objConnection, 3, 3, 1) ; adOpenStatic = 3 , adLockOptimistic = 3 , adCmdText = 1
catch
{
msgbox,48,, Error! %Sheet% does not exist, 2
return
}
pFields := objRecordset.Fields
while !objRecordset.EOF
{
row := [], ++rowcnt
Loop, % colcnt := pFields.Count
row[A_Index] := pFields.Item(A_Index-1).value
arr.push(row)
objRecordset.MoveNext
}
objRecordSet.Close()
objConnection.Close()
objRecordSet := ""
objConnection := ""
return arr
}
Existem várias tentativas no fórum. Como este: https://www.autohotkey.com/boards/viewtopic.php?f=6&t=95251
Eu gosto deste método: https://www.autohotkey.com/boards/viewtopic.php?t=102348&p=454940#p454989. Instale a função em uma "library" ou inclua em seu script.
Isso requer um download da Microsoft: https://www.microsoft.com/en-us/download/details.aspx?id=54920. Depois de instalar este "Database Engine", não há mais nenhuma ação a ser tomada.
Exemplo:
[code]datasource := "C:\Users\" A_UserName "\Scripts\ado_data_test1.xlsx"
arr := getXcells(datasource)
cols := arr.1.maxindex()
for x,y in Arr
for a,b in y
z .= (a = 1 ? "" : "`t") . b . (a = cols ? "`n" : "")
msgbox % trim(z,"`n") ;~ clipboard := trim(z,"`n")
[/code]
função:
[code]getXcells(datasource, sheet := "sheet1") ; requires https://www.microsoft.com/en-us/download/details.aspx?id=54920
{
global colcnt, rowcnt
arr := [], rowcnt := 0
objConnection := ComObjCreate("ADODB.Connection"), objRecordSet := ComObjCreate("ADODB.Recordset")
try objConnection.Open("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" . dataSource . "; Extended Properties='Excel 12.0 xml;HDR=no;IMEX=1';")
catch
{
msgbox,48,, Error! Your data could not be retrieved, 2
return
}
try objRecordset.Open("Select * FROM [" Sheet "$]", objConnection, 3, 3, 1) ; adOpenStatic = 3 , adLockOptimistic = 3 , adCmdText = 1
catch
{
msgbox,48,, Error! %Sheet% does not exist, 2
return
}
pFields := objRecordset.Fields
while !objRecordset.EOF
{
row := [], ++rowcnt
Loop, % colcnt := pFields.Count
row[A_Index] := pFields.Item(A_Index-1).value
arr.push(row)
objRecordset.MoveNext
}
objRecordSet.Close()
objConnection.Close()
objRecordSet := ""
objConnection := ""
return arr
}[/code]