Ola amigos,
Como ficou a rotina:
FUNCTION ExcelConnection( cFileName, cVersion )
LOCAL oConexao
DO CASE
CASE ValType( cVersion ) == "C"
CASE ".xlsx" $ Lower( cFileName ); cVersion := "12.0" // XLSX
OTHERWISE ; cVersion := "8.0" // 97/2000/XP
ENDCASE
try
oConexao := win_OleCreateObject( "ADODB.Connection" )
oConexao:ConnectionString := ;
[Provider=Microsoft.ACE.OLEDB.12.0;Data Source=] + cFileName + ;
[;Extended Properties="Excel ] + cVersion + [;HDR=NO;IMEX=1";]
/* // sem cabecario
oConexao:ConnectionString := ;
[Provider=Microsoft.ACE.OLEDB.12.0;Data Source=] + cFileName + ;
[;Extended Properties="Excel ] + cVersion + [;HDR=YES";]
*/
catch
hwg_msgstop([Erro na abertura do arquivo, por favor verifique],[Aviso do sistema])
return nil
endtry
RETURN oConexao
Function teste_cast(cFILE, cFILE1)
LOCAL oExcel, oRs, aRowList, aColList, xCol, aColLista, xColLis, xTAMA
Private aPEDIDO:={}, aTUDO:={}, oQuery, aITENS :={}, TITULO:={}
IF !EMPTY(aDESTINO)
oQuery := "SELECT * FROM [" +ALLTRIM(cABA_PEDI)+"$]"
oExcel := ExcelConnection( @cFILE )
oExcel:Open()
oRs := oExcel:Execute( oQUERY )
aRowList := oRs:GetRows()
oRs:Close()
oExcel:Close()
aRowList := InverseArray( aRowList )
FOR EACH aColList IN aRowList
FOR EACH xCol IN aColList
IF valtype(xCOL) = [C]
AADD( aTUDO, xCol )
ELSEif valtype(xCOL) = [N]
AADD( aTUDO, STR(xCol) )
ELSEIF valtype(xCOL) = [U]
AADD(aTUDO, [ ])
ELSE
AADD(aTUDO, [ ])
ENDIF
NEXT
AADD( aPEDIDO, aTUDO )
aTUDO:={}
NEXT
IF LEN(aPEDIDO) > 0
FOR EACH aColLista IN aPEDIDO
FOR EACH xCOLLIS IN aColLista
AADD( TITULO, xCOLLIS ) // MONTAR CABECARIO DO BROWSE
NEXT
NEXT
xTAMA:=LEN(aPEDIDO) // TIRAR O CABECARIO DA TABELA DEIXAR SOMENTE OS DADOS
ADEL ( aPEDIDO, 1 )
ASize( aPEDIDO, xTAMA-1 )
BROWSE_ARRAY_EXCEL( @aPEDIDO, @TITULO )
ELSE
HWG_MSGINFO([nada])
ENDIF
ENDIF
RETURN NIL
FUNCTION BROWSE_ARRAY_EXCEL( aMATA, aTITULO )
LOCAL oDLG_MATRIZ, oPage1, aBr2, nI:=0
INIT DIALOG oDLG_MATRIZ TITLE "Tabela EXCEL " AT 0,0 SIZE 1000, 600 ;
FONT HFont():Add( 'tahoma', 0, -13, 400,,,) STYLE WS_CAPTION+DS_CENTER
@ 10,30 BROWSE aBr2 ARRAY SIZE oDLG_MATRIZ:nWidth-30, oDLG_MATRIZ:nHeight-100 ;
STYLE WS_TABSTOP+WS_HSCROLL FONT HFont():Add( 'tahoma', 0, -13, 400,,,)
aBr2:aArray := aMATA
Hwg_CreateArList( aBr2, aMATA )
aBr2:lESC := .T.
aBr2:lAdjRight := .T.
FOR nI := 1 TO Len(aBr2:aColumns)
aBr2:aColumns[nI]:heading := aTITULO[nI]
IF (aBr2:aColumns[nI]):Type == "C"
aBr2:aColumns[nI]:nJusHead := DT_LEFT
aBr2:aColumns[nI]:Length := LEN(aTITULO[nI])+10
ELSE
aBr2:aColumns[nI]:nJusHead := DT_LEFT
aBr2:aColumns[nI]:Length := 15
ENDIF
IF (aBr2:aColumns[nI]):Type == "C"
aBr2:aColumns[nI]:nJusLin := DT_LEFT
aBr2:aColumns[nI]:Picture := [@!]
ELSEIF (aBr2:aColumns[nI]):Type == "N"
aBr2:aColumns[nI]:nJusLin := DT_RIGHT
aBr2:aColumns[nI]:Picture := [@E 999,999,999.99]
ENDIF
NEXT
FOR nG := 1 TO Len(aBr2:aColumns)
aBr2:aColumns[nG]:bColorFoot := {|ng| {x_BLUE, x_YELLOW} }
NEXT
@ oDLG_MATRIZ:nWidth-130,oDLG_MATRIZ:nHeight-55 BUTTONEX "Sai&r" SIZE 120,38 STYLE WS_TABSTOP ;
ON CLICK {|| oDLG_MATRIZ:CLOSE() }
ACTIVATE DIALOG oDLG_MATRIZ
RETURN(.T.)