Help me! Convertion of the files .XLS to .DBF
Paulo Takami
xHarbour.
Paulo - Jacarei/SP - Brazil
Massimo Belgrano
#include "MiniGui.ch"
#include "common.ch"
#DEFINE TAB CHR(9)
#DEFINE CR CHR(13)
#DEFINE LF CHR(10)
#DEFINE CRLF CR + LF
#define xlEdgeLeft 7 //borde izquierdo
#define xlEdgeTop 8 //borde superior de la celda
#define xlEdgeBottom 9 //borde inferior
#define xlEdgeRight 10 //borde derecho
#define xlContinuous 1 //lineas continuas
#define xlAutomatic -4105
#define xlInsideVertical 11
#define xlInsideHorizontal 12
#DEFINE xlWorkbookNormal -4143 // (0xhffffefd1)
#define xlNormal -4143
#define xlDouble -4119
#define xlMedium -4138 //linea mediana
#DEFINE XLLEFT -4131 // (0xhffffefdd)
#DEFINE XLRIGHT -4152 // (0xhffffefc8)
#DEFINE XLCENTER -4108 // (0xhffffeff4)
#define xlSolid 1
#define xlThin 2 //linea fina
#define xlThick 4
#define xlHtml 44 //formato html
#DEFINE F_HEADER 1
#DEFINE F_LEN 2
#DEFINE F_FMT 3
#DEFINE F_JUSTIFY 4
#xcommand WITH <oObject> DO => Self := <oObject>
#xcommand ENDWITH => Self := NIL
Static Function PasaAExcel(dFecha, cTipo)
//----------------------------------------
Local cAliasO := "TEMPORAL"
LOCAL oExcel, oHoja, oBook, I:=0
Local cFile, nFila
Local xCuenta, xRuc, xDoc, xNumero
Local nDebe, nHaber, nD30, nD60, nD90, nM91, cLinea, nDias
Local cDebe, cHaber, cD30, cD60, cD90, cM91, cDias, K
Local TGDebe , TGHaber, TGD30, TGD60, TGD90, TGM91
Local nTotReg := 0
Local nReg := 0
Local nTamano := 9
Local lBold := .T.
Local J := 1
Local cNombreFuente := "VERDANA"
Local aNegro := {}
Local NroLin := 6
if cTipo = 'C'
cFile := "Cobrar.xls"
else
cFile := "Pagar.xls"
endif
oExcel := TOleAuto():New( "Excel.Application" )
if Ole2TxtError() != 'S_OK'
MsgStop('Excel no está disponible!' )
RETURN Nil
endif
oExcel:WorkBooks:Add() // ("c:\minigui\Cobrar.xls")
oExcel:Visible := .T.
oHoja := oExcel:Get("ActiveSheet")
OpenDataBaseFile( cAliasO, cAliasO, .T., .F., RddSetDefault() )
(cAliasO)->(DbCLearIndex())
(cAliasO)->(DbSetIndex( "TEMPORAL.NTX") )
nTotReg := (cAliasO)->( Lastrec() )
(cAliasO)->(DbGotop() )
oHoja:Columns( 2 ):Set("ColumnWidth","30")
oHoja:Columns( 3 ):Set("ColumnWidth","15")
TLinea := 'EMPRESA DE PRUEBA'
oHoja:Cells(2,2):Select()
Copyclipboard(Tlinea)
oHoja:Paste()
if cTipo = 'C'
TLinea := 'DETALLE DE CUENTAS POR COBRAR EN SOLES AL ' + Dtoc( dFecha )
else
TLinea := 'DETALLE DE CUENTAS POR PAGAR EN DOLARES AL ' + Dtoc( dFecha )
endif
oHoja:Cells(4,2):Select()
Copyclipboard(Tlinea)
oHoja:Paste()
Store 0 to nDebe, nHaber, nD30, nD60, nD90, nM91, nDias
Store 0 to TGDebe , TGHaber, TGD30, TGD60, TGD90, TGM91
Contador := 0
nFila := 6
Tlinea := ''
cLinea := 'Cuenta' + TAB + 'R U C ' + TAB + 'Razon Social' + TAB + ;
'Doc' + TAB + 'Numero' + TAB + 'T' + TAB
+ 'Vou' + TAB + ;
'FDoc' + TAB + 'Debe S/.' + TAB + 'Haber S/.' + TAB
+ 'Dias' + TAB + ;
'0 - 30' + TAB + '31 - 60' + TAB + '61 - 90' + TAB
+ '91 a MAS' + TAB + ;
'Glosa' + TAB + hb_osnewline()
TLinea += cLinea
Contador ++
nReg ++
Form_CtaCte.Progress_2.Value := nReg/nTotReg*100
WHILE !(cAliasO)->( Eof() )
xCuenta := (cAliasO)->Cuenta
xRuc := (cAliasO)->RUC
Store 0 to nDebe, nHaber, nD30, nD60, nD90, nM91
WHILE !(cAliasO)->( Eof() ) .and. (cAliasO)->Cuenta = xCuenta
.and. (cAliasO)->RUC = xRuc
NroLin ++
cDias := Str((cAliasO)->Dias ,10,0)
cDebe := Str((cAliasO)->Debe ,15,2)
cHaber := Str((cAliasO)->Haber,15,2)
cD30 := Str((cAliasO)->D30 ,15,2)
cD60 := Str((cAliasO)->D60 ,15,2)
cD90 := Str((cAliasO)->D90 ,15,2)
cM91 := Str((cAliasO)->M91 ,15,2)
cLinea := (cAliasO)->Cuenta + TAB +
"'"+(cAliasO)->RUC + TAB + (cAliasO)->Nombre + TAB + ;
"'"+(cAliasO)->Doc + TAB +
"'"+(cAliasO)->Numero + TAB + "'"+(cAliasO)->T + TAB + ;
(cAliasO)->Voucher + TAB +
Dtoc((cAliasO)->FDoc) + TAB + cDebe + TAB + ;
cHaber + TAB + cDias
+ TAB + cD30 + TAB + ;
cD60 + TAB + cD90
+ TAB + cM91 + TAB + ;
(cAliasO)->Glosa + TAB + hb_osnewline()
TLinea += cLinea
nDebe += (cAliasO)->Debe
nHaber += (cAliasO)->Haber
nD30 += (cAliasO)->D30
nD60 += (cAliasO)->D60
nD90 += (cAliasO)->D90
nM91 += (cAliasO)->M91
(cAliasO)->( DbSkip() )
Contador ++
nReg ++
Form_CtaCte.Progress_2.Value := nReg/nTotReg*100
If Contador = 1000
oHoja:Cells(nFila,2):Select() //------Select row-------//
Copyclipboard(Tlinea) //----copy the data at the
clipboard-----//
oHoja:Paste() //----paste in the excel page-----//
ClearClipboard()
nFila := nfila + Contador
Contador = 0
TLinea := ''
Endif
END WHILE
TGDebe += nDebe
TGHaber += nHaber
TGD30 += nD30
TGD60 += nD60
TGD90 += nD90
TGM91 += nM91
cDebe := Str( nDebe ,15,2)
cHaber := Str( nHaber,15,2)
cD30 := Str( nD30 ,15,2)
cD60 := Str( nD60 ,15,2)
cD90 := Str( nD90 ,15,2)
cM91 := Str( nM91 ,15,2)
NroLin ++
cLinea := TAB + 'Total ' + xRUC + TAB + TAB + TAB + TAB +
TAB + TAB + TAB + ;
cDebe + TAB + cHaber + TAB + TAB + cD30 + TAB + cD60 + TAB + ;
cD90 + TAB + cM91 + TAB + hb_osnewline()
TLinea += cLinea
Aadd(aNegro,NroLin) // Guarda linea para despues colocarle bold
Contador ++
nReg ++
Form_CtaCte.Progress_2.Value := nReg/nTotReg*100
END WHILE
(cAliasO)->( DbCloseArea() )
cDebe := Str( TGDebe ,15,2)
cHaber := Str( TGHaber,15,2)
cD30 := Str( TGD30 ,15,2)
cD60 := Str( TGD60 ,15,2)
cD90 := Str( TGD90 ,15,2)
cM91 := Str( TGM91 ,15,2)
NroLin ++
cLinea := TAB + 'Total General' + TAB + TAB + TAB + TAB + TAB +
TAB + TAB + ;
cDebe + TAB + cHaber + TAB + TAB + cD30 + TAB +
cD60 + TAB + ;
cD90 + TAB + cM91 + TAB + hb_osnewline()
TLinea += cLinea
Aadd(aNegro,NroLin)
if Contador > 0
oHoja:Cells(nFila,2):Select() //------Select row-------//
Copyclipboard(Tlinea) //----copy the data at the clipboard-----//
oHoja:Paste() //----paste in the excel page-----//
ClearClipboard()
endif
cRango := "B1:Q6"
oHoja:Range( cRango ):Font:Bold := .T.
cRango := "B4:Q4"
oHoja:Range( cRango ):Font:Size := 12
oHoja:Range("B4:Q4"):Interior:ColorIndex := 8
PoneBordeCelda(cRango, oHoja)
//oHoja:Range( cRango ):Font:Color := RGB(0,255,0)
//oHoja:Range( cRango ):Font:ColorIndex := 3
cRango := "B6:Q6"
oHoja:Range( cRango ):Font:Size := 12
//Coloca Sombra
oHoja:Range( cRango ):Interior:ColorIndex := 15
oHoja:Range( cRango ):Interior:Pattern := xlSolid
/*
oHoja:Range("b7:b7"):Interior:ColorIndex := 1 // Negro
oHoja:Range("b8:b8"):Interior:ColorIndex := 2 // Blanco
oHoja:Range("b9:b9"):Interior:ColorIndex := 3 // Rojo
oHoja:Range("b10:b10"):Interior:ColorIndex := 4 // Verde Claro
oHoja:Range("b11:b11"):Interior:ColorIndex := 5 // Azul
oHoja:Range("b12:b12"):Interior:ColorIndex := 6 // Amarillo
oHoja:Range("b13:b13"):Interior:ColorIndex := 7 // Camote
oHoja:Range("b14:b14"):Interior:ColorIndex := 8 // Celeste
oHoja:Range("b15:b15"):Interior:ColorIndex := 9 // Marron
oHoja:Range("b16:b16"):Interior:ColorIndex := 10 // Verde Oscuro
oHoja:Range("b17:b17"):Interior:ColorIndex := 11 // Azul Oscuro
oHoja:Range("b18:b18"):Interior:ColorIndex := 12 // Mostaza
oHoja:Range("b19:b19"):Interior:ColorIndex := 13 // Morado
oHoja:Range("b20:b20"):Interior:ColorIndex := 14 //
*/
// setea el ancho de cada columna
oHoja:Cells(6,2):Select()
oHoja:Columns( 2 ):Set("ColumnWidth","7.3")
oHoja:Columns( 3 ):Set("ColumnWidth","16.8")
oHoja:Columns( 4 ):Set("ColumnWidth","33")
oHoja:Columns( 5 ):Set("ColumnWidth","3.6")
oHoja:Columns( 6 ):Set("ColumnWidth","11.5")
oHoja:Columns( 7 ):Set("ColumnWidth","2.4")
oHoja:Columns( 8 ):Set("ColumnWidth","4.5")
oHoja:Columns( 9 ):Set("ColumnWidth","10")
oHoja:Columns( 10 ):Set("ColumnWidth","11")
oHoja:Columns( 11 ):Set("ColumnWidth","11")
oHoja:Columns( 12 ):Set("ColumnWidth","5")
oHoja:Columns( 13 ):Set("ColumnWidth","11")
oHoja:Columns( 14 ):Set("ColumnWidth","11")
oHoja:Columns( 15 ):Set("ColumnWidth","11")
oHoja:Columns( 16 ):Set("ColumnWidth","11")
oHoja:Columns( 17 ):Set("ColumnWidth","35")
oHoja:Range("B4:Q4"):Set("HorizontalAlignment","7") // Centrado dado un rango
oHoja:Range("B6:B6"):Set("HorizontalAlignment","7") // Centrado de
una celda
oHoja:Range("C6:C6"):Set("HorizontalAlignment","7") // Centrado
oHoja:Range("D6:D6"):Set("HorizontalAlignment","7") // Centrado
oHoja:Range("E6:E6"):Set("HorizontalAlignment","7") // Centrado
oHoja:Range("F6:F6"):Set("HorizontalAlignment","7") // Centrado
oHoja:Range("G6:G6"):Set("HorizontalAlignment","7") // Centrado
oHoja:Range("H6:H6"):Set("HorizontalAlignment","7") // Centrado
oHoja:Range("I6:I6"):Set("HorizontalAlignment","7") // Centrado
oHoja:Range("J6:J6"):Set("HorizontalAlignment","7") // Centrado
oHoja:Range("K6:K6"):Set("HorizontalAlignment","7") // Centrado
oHoja:Range("L6:L6"):Set("HorizontalAlignment","7") // Centrado
oHoja:Range("M6:M6"):Set("HorizontalAlignment","7") // Centrado
oHoja:Range("N6:N6"):Set("HorizontalAlignment","7") // Centrado
oHoja:Range("O6:O6"):Set("HorizontalAlignment","7") // Centrado
oHoja:Range("P6:P6"):Set("HorizontalAlignment","7") // Centrado
oHoja:Range("Q6:Q6"):Set("HorizontalAlignment","7") // Centrado
oHoja:Columns( 10 ):Set("NumberFormat","#,###.00") // Formato de
celda numerica
oHoja:Columns( 11 ):Set("NumberFormat","#,###.00")
oHoja:Columns( 12 ):Set("NumberFormat","#,###")
oHoja:Columns( 13 ):Set("NumberFormat","#,###.##")
oHoja:Columns( 14 ):Set("NumberFormat","#,###.##")
oHoja:Columns( 15 ):Set("NumberFormat","#,###.##")
oHoja:Columns( 16 ):Set("NumberFormat","#,###.##")
For j:=1 to len(aNegro)
k := Alltrim( Str(aNegro[j],4,0) )
cRango := "B"+ k + ":Q" + k
oHoja:Range( cRango ):Font:Bold := .T.
Next j
cRango := "B6:Q"+Alltrim(Str(nReg+6,4,0))
PoneBordesExcel (cRango, oHoja)
Form_CtaCte.Progress_2.Value := 100
oExcel:Quit()
/*
oHoja:End() // CERRAMOS EL VINCULO CON LA HOJA
oExcel:End() // Y CERRAMOS EL VINCULO CON EXCEL
RELEASE oHoja
RELEASE oExcel
*/
Return Nil
*---------for use the clipboard----------*
#pragma BEGINDUMP
#define HB_OS_WIN_32_USED
#define _WIN32_WINNT 0x0400
#include <windows.h>
#include "hbapi.h"
#include "hbapiitm.h"
HB_FUNC ( COPYCLIPBOARD )
{
HGLOBAL hglbCopy;
char * lptstrCopy;
char * cStr = hb_parc( 1 );
int nLen = strlen( cStr );
if ( !OpenClipboard( GetActiveWindow() ) )
return;
EmptyClipboard();
hglbCopy = GlobalAlloc( GMEM_DDESHARE, (nLen+1) * sizeof(TCHAR) );
if (hglbCopy == NULL)
{
CloseClipboard();
return;
}
// Lock the handle and copy the text to the buffer.
lptstrCopy = (char*) GlobalLock( hglbCopy );
memcpy( lptstrCopy, cStr, nLen * sizeof(TCHAR));
lptstrCopy[nLen] = (TCHAR) 0; // null character
GlobalUnlock(hglbCopy);
// Place the handle on the clipboard.
SetClipboardData( CF_TEXT, hglbCopy );
CloseClipboard();
}
//vacia el clipboard
HB_FUNC(CLEARCLIPBOARD)
{
if ( !OpenClipboard( GetActiveWindow() ) )
return;
EmptyClipboard();
CloseClipboard();
}
#pragma ENDDUMP
//---------------------------------------
FUNCTION PoneBordeCelda (cRango, oHoja)
//---------------------------------------
oHoja:Range( cRango ):Borders( xlEdgeTop ):LineStyle := xlDouble
oHoja:Range( cRango ):Borders( xlEdgeLeft ):LineStyle := xlDouble
oHoja:Range( cRango ):Borders( xlEdgeRight ):LineStyle := xlDouble
oHoja:Range( cRango ):Borders( xlEdgeBottom ):LineStyle := xlDouble
RETURN
//---------------------------------------
FUNCTION PoneBordesExcel (cRango, oHoja)
//---------------------------------------
oHoja:Range( cRango ):Borders( xlEdgeTop ):LineStyle := xlDouble
oHoja:Range( cRango ):Borders( xlEdgeLeft ):LineStyle := xlDouble
oHoja:Range( cRango ):Borders( xlEdgeRight ):LineStyle := xlDouble
oHoja:Range( cRango ):Borders( xlEdgeBottom ):LineStyle := xlDouble
oHoja:Range( cRango ):Borders( xlInsideHorizontal ):LineStyle := xlContinuous
oHoja:Range( cRango ):Borders( xlInsideVertical ):LineStyle := xlContinuous
RETURN
[]s
Marcelo A. L. Carli
Marília/SP
Capital Nacional do Alimento ®
http://malcarli.vila.bol.com.br
http://marcelo.lx.com.br
http://www.marazambon.blogspot.com/
email / msn: malc...@terra.com.br
******************************************************************************
Se for repassar, apague o meu nome e endereço.
Ajude a combater a propagação de vírus e spams
coloque TODOS os destinatários em CÓPIA OCULTA (Cco / Bcc)
******************************************************************************
2011/8/23 Paulo Takami <pta...@gmail.com>:
> I help function to convertion files Excel to files DBF from Habour or
> xHarbour.
>
> Paulo - Jacarei/SP - Brazil
>
> --
> You received this message because you are subscribed to the Google
> Groups "Harbour Users" group.
> Unsubscribe: harbour-user...@googlegroups.com
> Web: http://groups.google.com/group/harbour-users
>
--
Massimo Belgrano
Delta Informatica S.r.l. (http://www.deltain.it/) (+39 0321 455962)
Analisi e sviluppo software per Lan e Web - Consulenza informatica - Formazione
matt johnson
In Excel you can save as a text file.
From: Paulo Takami <pta...@gmail.com>
To: Harbour Users <harbou...@googlegroups.com>
Sent: Tuesday, August 23, 2011 4:11 AM
Subject: [harbour-users] Help me! Convertion of the files .XLS to .DBF
I help function to convertion files Excel to files DBF from Habour or
xHarbour.
Paulo - Jacarei/SP - Brazil
You received this message because you are subscribed to the Google
Groups "Harbour Users" group.
Web: http://groups.google.com/group/harbour-users