#include "fivewin.ch"
#include "adodef.ch"
function Main()
local oCn, cSql, oRs, oRec
local cFile := "test.xlsx"
loCAL cDbf := "customer.dbf"
local cTable, aStruct, aData, aCols
SET DATE GERMAN
SET CENTURY ON
FWNumFormat( "E", .t. )
SetGetColorFocus()
if !( File( cFile ) .and. File( cDbf ) )
? "Files do not exist"
return nil
endif
cTable := cFileNoExt( cDbf )
oCn := FW_OpenADOExcelBook( TrueName( cFile ) )
TRY
oCn:Execute( "DROP TABLE [" + cTable + "]" )
CATCH
END
USE ( cDbf ) NEW SHARED
aStruct := DBSTRUCT()
aCols := ArrTranspose( aStruct )[ 1 ]
if MsgNoYes( "Use ADOX?" )
FW_ADOX_CreateExcelTable( oCn, cTable, aStruct )
else
cSql := FW_XLCreateTableSQL( cTable, aStruct )
oCn:Execute( cSql )
endif
? "Table created"
oRs := FW_OpenRecordSet( oCn, "select * from [" + cTable + "]" )
XBROWSER FWAdoStruct( oRs ) TITLE "STRUCTURE"
MsgRun( "Adding Data", cTable, <||
aData := FW_DbfToArray()
AEval( aData, { |aVals,i| oRs:AddNew( aCols, DateCheck( aVals ) ) } )
return nil
> )
oRs:MoveFirst()
oRec := TDataRow():New( oRs )
oRec:Edit()
XBROWSER oRs TITLE cTable FASTEDIT AUTOFIT
oCn:Close()
return nil
//----------------------------------------------------------------------------//
function FW_XLCreateTableSQL( cName, aStruct )
local cSql := "CREATE TABLE " + cName + " ( "
local aCols := Array( Len( aStruct ) )
AEval( aStruct, <|aFld,i|
if i > 1
cSql += ", "
endif
cSql += "[" + aFld[ 1 ] + "] "
cSql += If( aFld[ 2 ] == "C", "VARCHAR(255)", ;
If( aFld[ 2 ] $ "DT=@", "DATE", ;
If( aFld[ 2 ] == "L", "LOGICAL", ;
If( aFld[ 2 ] $ "+N", "DOUBLE", "TEXT" ) ) ) )
return nil
> )
return cSql + " )"
//----------------------------------------------------------------------------//
function FW_ADOX_CreateExcelTable( oCn, cTable, aStruct )
local oCat := CreateObject( "ADOX.Catalog" )
local oTable, aFld, n, cType
local oCol, oCol2
local nType, nLen
oCat:ActiveConnection := oCn
cTable := Lower( cTable )
oTable := CreateObject( "ADOX.Table" )
oTable:Name := cTable
AEval( aStruct, <|aFld,i|
local nType := ;
If( aFld[ 2 ] == "C", adVarWChar, ;
If( aFld[ 2 ] $ "DT=@", adDate, ;
If( aFld[ 2 ] == "L", adBoolean, ;
If( aFld[ 2 ] $ "+N", adDouble, adLongVarWChar ) ) ) )
oTable:Columns:Append( aFld[ 1 ], nType )
if i == 7
oCat:Tables:Append( oTable )
oTable := oCat:Tables( cTable )
endif
return nil
> )
return nil
//----------------------------------------------------------------------------//
function DateCheck( aVals )
AEval( aVals, { |u,i| If( ValType( u ) $ "DT" .and. u < {^ 1900/01/01 }, ;
aVals[ i ] := nil, nil ) } )
return aVals
//----------------------------------------------------------------------------//