#include "FiveWin.ch"
FUNCTION Main()
LOCAL cArquivoCSV := "IBPTax.CSV" // coloque aqui o seu arquivo .CSV
LOCAL cArquivoDBF := "IBPTax.DBF" // coloque aqui o seu arquivo .DBF
LOCAL nPointer := 0
LOCAL nEol, cConteudo, cLinha
LOCAL nRegistro, cBuffer, nLenArq, nLido
LOCAL cEol := Chr( 13 ) + Chr( 10 )
LOCAL nHandle := FOpen( cArquivoCSV, 2 )
LOCAL aEstr := { ;
{ "codigo" , "C", 8, 0 }, ;
{ "ex" , "N", 3, 0 }, ;
{ "tabela" , "N", 1, 0 }, ;
{ "descricao" , "C", 255, 0 }, ;
{ "aliqnac" , "N", 5, 2 }, ;
{ "aliqimp" , "N", 5, 2 };
}
PRIVATE cCampo
DBCreate( cArquivoDBF, aEstr )
USE ( cArquivoDBF ) Shared New
nRegistro := 0
cBuffer := Space( 1200 ) // se houver linha maior aumente o 1200
nLenArq := FSeek( nHandle, nPointer, 2 ) // pega tamanho arquivo
FSeek( nHandle, nPointer, 0 ) // posiciona o pointer noinicio
nLido := FRead( nHandle , @cBuffer, 1200 )
nEol := AT( cEol, cBuffer )
nPointer += nEol + 1 // vamos ignorar a linha de nomes de campo
FSeek( nHandle, nPointer, 0 ) // posiciona o pointer na segunda linha
WHILE nEol > 0
nLido := FRead( nHandle , @cBuffer, 1200 )
nEol := AT( cEol, cBuffer )
IF nEol > 0
cLinha := Left( cBuffer, nEol - 1 )
APPEND BLANK // cria o registro vazio no dbf
// @ 10, 25 SAY "Salvando Registro => " + Str( ++nRegistro )
FOR x = 1 TO FCount()
cConteudo := SubSt( cLinha, 0, At( ";", cLinha ) )
cLinha := StrTran( cLinha, cConteudo, Nil, 1, 1 ) // remove apenas esta sequencia
cConteudo := Left( cConteudo, Len( cConteudo ) - 1 ) // tira o ";" do final
cCampo := Field( x )
IF ValType( &cCampo. ) = "N"
cConteudo := Val( cConteudo )
ENDIF
Replace &cCampo. WITH cConteudo // salva o campo
NEXT
ENDIF
nPointer += nEol + 1 // incrementa o pointer
IF nPointer >= nLenArq // se fim de arquivo,
EXIT // fim...
ELSE // se nao,
FSeek( nHandle, nPointer, 0 ) // posiciona o pointer
ENDIF
ENDDO
RETURN Nil
oRange := GetExcelRange( cExcelFile, , )
nCols := oRange:Columns:Count
nRows := oRange:Rows:Count
aData := ArrTranspose (oRange:Value)
oRange:WorkSheet:Parent:Close()
aDataD := {}
aadd( aDataD, {nColXls7, cCampoDbf} )
aadd( aDataD, {nColXls3, cCampoDbf} )
etc...
//-----------------------------------------//
Function Xls_A_Dbf(cAlias,aDataD,aData,nRows)
local nReg:=0, nRow, nCol, nColum, cCampo, uValue, uDest
FOR nRow := 2 TO nRows // 2 es la primera linea de datos (1=headers)
(cAlias)->(DbAppend())
FOR nCol := 1 to len(aDataD)
nColum := aDataD[nCol,1] //column xls fuente
cCampo := alltrim( aDataD[nCol,2] ) //campo tabla destino
if !Empty(nColum) //si celda esta configurada...
uValue := aData[nRow,nColum]
uDest := (cAlias)->&cCampo
if Valtype( uValue ) != ValType( uDest )
uValue := ConvertType( uValue, ValType(uDest) )
endif
(cAlias)->(FieldPut(FieldPos(cCampo), uValue))
endif
NEXT nCol
if ( nReg += 1 ) >= 60
nReg:=0
SysRefresh()
endif
NEXT nRow
SysRefresh()
RETURN NIL
FW_ExcelToDbf( oExcelRange,
cDbfFieldList,
lRangeHasHeaders )
#include "fivewin.ch"
Function FWEXCEL()
LOCAL oRange, lOpened:=.f., aData
LOCAL cExcelFileName := "COMPRAS.XLS"
// oRange := GetExcelRange( "COMPRAS.xls", , , @lOpened )
// oRange := GetExcelRange( [ cExcelFileName ], [ cSheetName ], [ acRange ], @lOpened )
oRange := GetExcelRange( [ cExcelFileName ], , , @lOpened )
if oRange == nil
? "Error en el XLS"
else
aData := ArrTranspose( oRange:Value )
if lOpened
oRange:WorkSheet:Parent:Close()
endif
xbrowser aData
USE COMPRAS NEW ALIAS CP
SELECT CP
FW_ExcelToDBF( oRange, nil, .t. )
endif
return nil
Another case:
Let us consider other case, where the headers of Excel sheet doe not match with any field names of the DBF. Assume that we can not or do not want to edit and modify the header names in the Excel sheet.
We want columns 1,2,3,4 of the sheet to be copied to fields "PROF,CLASSE,FLAG,AULA". Then :
FW_ExcelToDBF( oRange, "PROF,CLASSE,FLAG,AULA", .t. )
In case the excel sheet does not contain a header row at all, then:
FW_ExcelToDBF( oRange, "PROF,CLASSE,FLAG,AULA", .f. )
aStruct := { { "NAME", "C", 15, 0 }, ;
{ "DATE", "D", 8, 0 }, ;
{ "ADDRESS", "C", 15, 0 }, ;
{ "AMOUNT", "N", 12, 0 } }
Excel column "ID" --> Ignore
Excel column "FIRST" --> dbf field "NAME"
Excel column "HIREDATE" --> dbf field "DATE"
Excel column "CITY" --> dbf field "ADDRESS"
Excel column "SALARY" --> dbf field "AMOUNT"
function ImportXLS()
local oRange, aStruct
local cXls := "c:\tests\import.xlsx"
local cDbf := "c:\tests\import.dbf"
aStruct := { { "NAME", "C", 15, 0 }, ;
{ "DATE", "D", 8, 0 }, ;
{ "ADDRESS", "C", 15, 0 }, ;
{ "AMOUNT", "N", 12, 0 } }
DBCREATE( cDBF, aStruct, "DBFCDX", .T., "DST" ) // Create and keep open
// or open an existing DBF
oRange := GetExcelRange( cXls, "Sheet1", "B1:E6" )
FW_ExcelToDBF( oRange, "NAME,ADDRESS,AMOUNT,DATE", .t. )
XBROWSER
return nil
Return to FiveWin para Harbour/xHarbour
Users browsing this forum: No registered users and 116 guests