FiveTech Support Forums

#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

   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