Importar DBF para CSV e/ou XLS de forma rápida

rochinha · November 24, 2018

Amiguinhos,

Eis uma forma rápida de importação de tabelas .DBF para arquivos .CSV e/ou .XLS de forma rápida:

use tabela 
oExcel := TOleAuto():New( "Excel.Application" )
oWorkbook := oExcel:WorkBooks
oWorkbook:Open( (DBF_TEMP) )
oExcel:DisplayAlerts := .f.
oExcel:Visible := .t.
oWorksheet := oExcel:Get( "ActiveSheet" )
oWorksheet:SaveAs( cPathDados + "DBF_TEMP.CSV",  6 )
oWorksheet:SaveAs( cPathDados + "DBF_TEMP.XLS", 56 )

Só não resolvi o processo preso na task manager.

rochinha · November 24, 2018

Amiguinhos,

Correção:

oExcel := TOleAuto():New( "Excel.Application" )
oWorkbook := oExcel:WorkBooks
oWorkbook:Open( "c:\temp\tabela.DBF" )
oExcel:DisplayAlerts := .f.
oExcel:Visible := .t.
oWorksheet := oExcel:Get( "ActiveSheet" )
oWorksheet:SaveAs( "c:\temp\tabela.CSV",  6 ) // Segundo parâmetro informa o tipo de saída
oWorksheet:SaveAs( "c:\temp\tabela.XLS", 56 ) // Segundo parâmetro informa o tipo de saída

Informativo:

fonte: https://support.microsoft.com/pt-br/help/291308/how-to-select-cells-ranges-by-using-visual-basic-procedures-in-excel

// Defines para o segundo parâmetro

#define xlCSV   6 // CSV   *.csv
#define xlCSVMSDOS   24 // MSDOS CSV   *.csv
#define xlCSVWindows   23 // Windows CSV   *.csv
#define xlDBF3   8 // Dbase 3 format   *.dbf
#define xlExcel8   56 // Excel 97-2003 Workbook   *.xls
#define xlHtml   44 // HTML format   *.htm; *.html
#define xlTextMSDOS   21 // MSDOS Text   *.txt
#define xlWorkbookNormal -4143 // Workbook normal   *.xls

Edited November 24, 2018 by rochinha
Acrescentar informações

kapiaba · November 26, 2018

Buenas, alguém pode testar com uma versão mais recente do FiveWin, para ver se funcionar essa versão?
Lástima: Não tenho excel instalado. somet eo BROffice.

#include "FiveWin.ch"
	#define xlCSV              6   // CSV                    *.csv
#define xlCSVMSDOS        24   // MSDOS CSV              *.csv
#define xlCSVWindows      23   // Windows CSV            *.csv
#define xlDBF3             8   // Dbase 3 format         *.dbf
#define xlExcel8          56   // Excel 97-2003 Workbook *.xls
#define xlHtml            44   // HTML format            *.htm; *.html
#define xlTextMSDOS       21   // MSDOS Text             *.txt
#define xlWorkbookNormal -4143 // Workbook normal        *.xls
	FUNCTION DbfToXls()
	   LOCAL oExcel, oWorkbook, oWorksheet, oError
	   USE CUSTOMER
	   // NAO FUNCIONA, VERSAO VELHA?
   // oExcel := TOleAuto():New( "Excel.Application" )
	   TRY
	      oExcel := GetActiveObject( "Excel.Application" )
	   CATCH
	      TRY
	         oExcel := CreateObject( "Excel.Application" )
	      CATCH oError
	         Alert( "ERROR! Excel not avialable. [" + Ole2TxtError()+ "]" + oError:description )
	         // RETURN NIL
	      END
	   END
	   oWorkbook := oExcel:WorkBooks:Add()
   // oWorkbook := oExcel:WorkBooks
   oWorkbook:Open( "C:\TEMP\CUSTOMER.DBF" )
	   oExcel:DisplayAlerts := .F.
   oExcel:Visible := .T.
	   oWorksheet := oExcel:Get( "ActiveSheet" )
   oWorksheet:SaveAs( "C:\TEMP\CUSTOMER.CSV",  6 ) // Segundo parâmetro informa o tipo de saída
   oWorksheet:SaveAs( "C:\TEMP\CUSTOMER.XLS", 56 ) // Segundo parâmetro informa o tipo de saída
	   // oExcel:WorkBooks:Close()
   // oExcel:Application:Quit()
	RETURN NIL
	// FIM

kapiaba · November 26, 2018

Gentileza, testar esta versão também:

#include "Fivewin.ch"
#include "Splitter.ch"
	//----------------------------------------------------------------------------//
	REQUEST DBFCDX
	#define LWIDTH   364
	STATIC oWnd
STATIC oWndChild
STATIC oMsgBar
STATIC oBar
STATIC oVSplitL
STATIC oPnelCtrlL
STATIC oPnelCtrlR
STATIC oExcel, oBook
STATIC oBrw
	//----------------------------------------------------------------------------//
	FUNCTION Main()
	   LOCAL oSay
   LOCAL nSize  := 32
   LOCAL cPanel := "TPanel()"   // "TScrollPanel()"
	   DEFINE WINDOW oWnd //MDI //SIZE 600, 400
   oWnd:SetMenu( HazMenu() )
	   DEFINE MSGBAR oMsgBar OF oWnd PROMPT "Excel" 2015
	   oPnelCtrlL := &( cPanel ):New( if( !Empty( oBar ), oBar:nBottom + 6, 2 ), ;
      4, ;
      ScreenHeight() - 6 - if( !Empty( oMsgBar ), oMsgBar:nHeight(), 2 ), ;
      LWIDTH - 8, ;
      oWnd )
	   oPnelCtrlL:SetColor( CLR_MAGENTA, CLR_GRAY )
   oPnelCtrlL:bRClicked := { | o | MsgInfo( "1" ) }
   //oPnelCtrlL:nOpacity := 128
  
   oPnelCtrlR := &( cPanel ):New( if( !Empty( oBar ), oBar:nBottom + 6, 2 ), ;
      LWIDTH + 2, ;
      ScreenHeight() - 6 - if( !Empty( oMsgBar ), oMsgBar:nHeight(), 2 ), ;
      ScreenWidth() - 4, ; // - LWIDTH - 8, ;
   oWnd )
	   oPnelCtrlR:SetColor( CLR_MAGENTA, CLR_HGRAY )
   oPnelCtrlR:bRClicked := { | o | MsgInfo( "2" ) }
   //oPnelCtrlL:nOpacity := 128
	
   @ if( !Empty( oBar ), oBar:nBottom + 4, 2 ), LWIDTH - 4 SPLITTER oVSplitL ;
      VERTICAL ;
      PREVIOUS CONTROLS oPnelCtrlL ;
      HINDS CONTROLS oPnelCtrlR    ;
      LEFT MARGIN 0 ;
      RIGHT MARGIN 8 ;
      SIZE 2, ScreenHeight() - if( !Empty( oBar ), oBar:nBottom + 10, 0 ) - ;
      if( !Empty( oMsgBar ), oMsgBar:nHeight(), 2 ) ;
      COLOR CLR_RED ;
      PIXEL OF oWnd
	
   ACTIVATE WINDOW oWnd MAXIMIZED VALID MyValid() ;
      ON INIT ( oPnelCtrlL:bResized := { || oBrw:AdjClient() } ) ;
      ON RESIZE ( oVSplitL:AdjLeft() )
	RETURN nil
	//----------------------------------------------------------------------------//
	FUNCTION HazMenu()
	   LOCAL oMenu
	   MENU oMenu 2015
   MENUITEM "XBrowse" ACTION MyBrowse()
   MENUITEM "Excel"
   MENU
   MENUITEM "Sample" ACTION MyExcel()
	   //MENUITEM "Dbf to Excel" ACTION DbfExcel()
	   MENUITEM "Acces to Excel" ACTION AccesExcel() WHEN !Empty( oExcel )
   ENDMENU
   MENUITEM "Move Panel" ACTION oPnelCtrlR:Move( oPnelCtrlR:nTop + 40, oPnelCtrlR:nLeft + 40 )
	   //oMenu:AddMdi()
	   MENUITEM "Exit" ACTION oWnd:End()
   ENDMENU
   
   RETURN oMenu
	//----------------------------------------------------------------------------//
	FUNCTION MyValid()
	   IF !Empty( oExcel )
      IF !Empty( oBook )
         oBook:Close()
      ENDIF
      oExcel:Quit()
      oExcel := nil
      DbCloseAll()
      oWnd:End()
   ENDIF
	   RETURN .T.
	//----------------------------------------------------------------------------//
	FUNCTION HazMdiChild
	   DEFINE WINDOW oWndChild MDICHILD OF oWnd;
      TITLE "MDI Child - Excel" NOSYSMENU //VSCROLL //           MENU oMenu
	   ACTIVATE WINDOW oWndChild;
      VALID MyValid() ;   //!GETKEYSTATE( VK_ESCAPE );
   MAXIMIZED
	RETURN nil
	//----------------------------------------------------------------------------//
	FUNCTION MyExcel()
	   LOCAL oObject
   LOCAL oQ
   LOCAL cSource
   LOCAL oHoja
   LOCAL lSw      := .T.
     
   TRY
      oExcel = GetActiveObject( "Excel.Application" )
   CATCH
      TRY
         oExcel = CreateObject( "Excel.Application" )
      CATCH
         MsgInfo( "Excel is not installed on this PC. You need Excel to continue" )
         lSw   := .F.
      END
   END
	   IF lSw
      IF Empty( oExcel )
         oExcel    := CreateObject( "Excel.Application" )
      ENDIF
	      oBook     := oExcel:WorkBooks:Add()
      oHoja     := oExcel:ActiveSheet()
	      SetWindowLong( oExcel:hWnd, - 16, "L" )
	      oExcel:DisplayAlerts     := .F.
      oExcel:ActiveWorkBook:Protect( "" , .F. , .F. )
      oExcel:ScreenUpdating    := .F.
        
      WITH OBJECT oExcel
         // No lo necesito si quiero modificar las coordenadas
         :WindowState := - 4137   //Normal  xlNormal    //-4137   xlMaximized
           
         //:Top    := 40
         //:Left   := 100
         //:Height := 500  //Min( nHeight, oExcel:UsableHeight )
         //:Width  := 766  //Min( nWidth, oExcel:UsableWidth )
      END
	      //oExcel:Application:WindowState       := -4137
      oExcel:DisplayDocumentActionTaskPane  := .F.
	      oExcel:Visible           := .T.
      oExcel:DisplayAlerts     := .F.
      // No lo uso
      //SetForeGroundWindow( oExcel:hWnd )
      SetParent( oExcel:hWnd, oPnelCtrlR:hWnd )
      oExcel:ScreenUpdating    := .T.
        
   ENDIF
	RETURN lSw
	//----------------------------------------------------------------------------//
	FUNCTION MyBrowse()
	   LOCAL cAlias
   
   SELECT( 1 )
   USE Customer
   cAlias := Alias()
   @ 1, 1 XBROWSE oBrw DATASOURCE cAlias OF oPnelCtrlL AUTOSORT PIXEL
   
   oBrw:CreateFromCode()
	RETURN nil
	//----------------------------------------------------------------------------//
	//----------------------------------------------------------------------------//
	/*
//DriverId= 22 - 278 - 790   // Excel - Ojo con las versiones
//DriverId= 25   // MS Access
//DriverId= 277  // Dbase ¿?
//DriverId= 21   // Dbase III
//DriverId= 26   // Paradox
//DriverId= 27   // TXT, CSV, ASC, TAB files
//DriverId= 533  // dBase 5.0
*/
	//----------------------------------------------------------------------------//
	FUNCTION AccesExcel()
	   LOCAL oHoja
   LOCAL oObject
   LOCAL oQ
   LOCAL cSource
   
   cSource   := "ODBC;DSN=MS Access Database;DBQ=C:\Fwh\samples\xbrtest.mdb;"
   cSource   += "DefaultDir=c:\Fwh\samples;DriverId=25;FIL=MS Access;"
   cSource   += "MaxBufferSize=2048;PageTimeout=5;"
	
   IF Empty( oExcel )
      oExcel    := CreateObject( "Excel.Application" )
   ENDIF
   IF Empty( oBook )
      oBook     := oExcel:WorkBooks:Add()
   ENDIF
   oHoja     := oExcel:ActiveSheet()
   
   
   oObject := oHoja:ListObjects:Add( 0, cSource, .T. , .T. , oHoja:Range( "$B$2" ) )
   
   //oObject:SourceType  := 0            // xlSrcExternal - XlListObjectSourceType Enumeration (Excel)
   //oObject:Source      := cSource
   //oObject:LinkSource  := .T.
   //oObject:HasHeaders  := .T.
   //oObject:Destination := oHoja:Range("$B$10")
   
   oQ   := oObject:QueryTable()
   WITH OBJECT oQ
      :CommandText := "SELECT CUSTOMER.ID, CUSTOMER.First, CUSTOMER.Last, CUSTOMER.Street, "
      :CommandText += "CUSTOMER.City, CUSTOMER.State, CUSTOMER.Zip "
      :CommandText += CRLF + "FROM C:\Fwh1791\samples\xbrtest.mdb.CUSTOMER CUSTOMER "
      :CommandText += CRLF + "ORDER BY CUSTOMER.ID"
      :RowNumbers             := .F.
      :FillAdjacentFormulas   := .F.
      :PreserveFormatting     := .T.
      :RefreshPeriod          := 0
      :RefreshOnFileOpen      := .F.
      :RefreshStyle           := 1 //xlInsertDeleteCells       //XlCellInsertionMode Enumeration (Excel)
      :AdjustColumnWidth      := .T.
      :PreserveFormatting     := .T.
      :BackgroundQuery        := .T.
      :PreserveColumnInfo     := .T.
      :SavePassword           := .F.
      //:SaveData               := .T.
      :ListObject:DisplayName := "Tabla_Consulta_desde_MS_Access_Database"
      :Refresh()
   END
	   //oExcel:Quit()
	RETURN nil
	//----------------------------------------------------------------------------//

rochinha · November 29, 2018

Buenas, alguém pode testar com uma versão mais recente do FiveWin, para ver se funcionar essa versão?
Lástima: Não tenho excel instalado. somet eo BROffice.


#include "FiveWin.ch"
	#define xlCSV              6   // CSV                    *.csv
#define xlCSVMSDOS        24   // MSDOS CSV              *.csv
#define xlCSVWindows      23   // Windows CSV            *.csv
#define xlDBF3             8   // Dbase 3 format         *.dbf
#define xlExcel8          56   // Excel 97-2003 Workbook *.xls
#define xlHtml            44   // HTML format            *.htm; *.html
#define xlTextMSDOS       21   // MSDOS Text             *.txt
#define xlWorkbookNormal -4143 // Workbook normal        *.xls
	FUNCTION DbfToXls()
	   LOCAL oExcel, oWorkbook, oWorksheet, oError
	   USE CUSTOMER
	   // NAO FUNCIONA, VERSAO VELHA?
   // oExcel := TOleAuto():New( "Excel.Application" )
	   TRY
	      oExcel := GetActiveObject( "Excel.Application" )
	   CATCH
	      TRY
	         oExcel := CreateObject( "Excel.Application" )
	      CATCH oError
	         Alert( "ERROR! Excel not avialable. [" + Ole2TxtError()+ "]" + oError:description )
	         // RETURN NIL
	      END
	   END
	   oWorkbook := oExcel:WorkBooks:Add()
   // oWorkbook := oExcel:WorkBooks
   oWorkbook:Open( "C:\TEMP\CUSTOMER.DBF" )
	   oExcel:DisplayAlerts := .F.
   oExcel:Visible := .T.
	   oWorksheet := oExcel:Get( "ActiveSheet" )
   oWorksheet:SaveAs( "C:\TEMP\CUSTOMER.CSV",  6 ) // Segundo parâmetro informa o tipo de saída
   oWorksheet:SaveAs( "C:\TEMP\CUSTOMER.XLS", 56 ) // Segundo parâmetro informa o tipo de saída
	   // oExcel:WorkBooks:Close()
   // oExcel:Application:Quit()
	RETURN NIL
	// FIM

Este seu código tem erro, você instânciou a função ADD(). Copia denovo meu código e ai funcionará.

O caminho precisa estar completo para a tabela .DBF e os arquivos de salvamento.

As 3 opções funcionam com o código corrigido sendo que

1-oExcel := TOleAuto():New( "Excel.Application" ) vai abrir o Excel.
2-oExcel := GetActiveObject( "Excel.Application" ) vai chamar o Excel Ativo, ou seja, o Excel precisa estar aberto.
3-oExcel := CreateObject( "Excel.Application" ) vai abrir o Excel.

@braços.

kapiaba · November 29, 2018

Na minha versão,

oExcel := TOleAuto():New( "Excel.Application" )

Não funciona.

rochinha · November 30, 2018

Amiguinhos,

Você usa (x)Harbour, então a CreateObject() servirá.

Sign In

Importar DBF para CSV e/ou XLS de forma rápida

Recommended Posts

rochinha

Link to comment

Share on other sites

rochinha

Link to comment

Share on other sites

kapiaba

Link to comment

Share on other sites

kapiaba

Link to comment

Share on other sites

rochinha

Link to comment

Share on other sites

kapiaba

Link to comment

Share on other sites

rochinha

Link to comment

Share on other sites

Join the conversation

Browse

Activity