Import and Export to Excel
- nageswaragunupudi
- Posts: 8017
- Joined: Sun Nov 19, 2006 5:22 am
- Location: India
- Contact:
Re: Import and Export to Excel
Estoy usando lMergeVert y no me "repinta" bien cuando cambio de OBRA entre líneas:
Me explico
La obra 490 tiene 3 Presupuestos
La obra 492 tiene 4 Presupuestos
En la Imagen 1 se ve como la Columna OBRA del segundo BROWSE esta correcta, pero cuando me cambio a la obra 492 (imagen 2) esta aparece 2 veces .
ajusta dos veces la OBRA, La ajusta a 3 registros (Obra 490) y también a 4 registros (Obra 492)
He probado ya todas las opciones y combinaciones de ellas que he vito por el foro pero sin resultado
como estas
oLbxP:aCols[1]:lMergeVert := .t.
oLbxP:lMergeVert := .t.
oLbxP:aCols[1]:WorkMergeData()
oLbxp:refresh()
IMAGEN 1
IMAGEN 2
Alguna Idea ¿?
p.d. Mis disculpas por escribir en castellano en foros en Ingles
Me explico
La obra 490 tiene 3 Presupuestos
La obra 492 tiene 4 Presupuestos
En la Imagen 1 se ve como la Columna OBRA del segundo BROWSE esta correcta, pero cuando me cambio a la obra 492 (imagen 2) esta aparece 2 veces .
ajusta dos veces la OBRA, La ajusta a 3 registros (Obra 490) y también a 4 registros (Obra 492)
He probado ya todas las opciones y combinaciones de ellas que he vito por el foro pero sin resultado
como estas
oLbxP:aCols[1]:lMergeVert := .t.
oLbxP:lMergeVert := .t.
oLbxP:aCols[1]:WorkMergeData()
oLbxp:refresh()
Code: Select all
DEFINE DIALOG oDDAbmLins OF oWndIva RESOURCE "IVA_LINS"
REDEFINE BUTTON oBtnNewp ID 4002 OF oDDAbmLins ACTION (cTipmov:="" ,Insertarp())
REDEFINE BUTTON oBtnModp ID 4003 OF oDDAbmLins ACTION (cTipmov:="M",Modificap())
REDEFINE BUTTON oBtnDelp ID 4004 OF oDDAbmLins ACTION (cTipmov:="" ,Eliminarp())
REDEFINE BUTTON oBtnLin ID 4006 OF oDDAbmLins ACTION (deshabilitap(),oDbarra:Enable(),oBtnNew:Setfocus())
REDEFINE BUTTON oBtnCtop ID 4001 OF oDDAbmLins ACTION (If(Helppre(),VolHpre(.t.),VolHpre(.f.)),.T.)
REDEFINE GET oConcepp VAR cConcepp ID 104 OF oDDAbmLins VALID Conceptop()
REDEFINE GET oDescrip VAR cDescrip ID 105 OF oDDAbmLins
REDEFINE GET oImportp VAR nImportp PICTURE "@E 999,999,999.99" ID 106 OF oDDAbmLins VALID Importep()
REDEFINE SAY oDifp PROMPT nDifp ID 4005 OF oDDAbmLins
REDEFINE XBROWSE oLbx ID 110 OF oDDAbmlins;
HEADERS "NºObra","Asiento","Fecha","Cuenta","Cpto.","Descripcion","Base","Tipo";
COLUMNS "Obra", "Asiento", "fecha","Cuenta","Concepto","Descrip","Base","tipo" ;
SIZES 50,50,65,55,30,210,90,33;
ALIAS ("bliva");
ON CHANGE (Toma_Lin(), PonerScope("bliva","pliva",oLbxp),refrescapre())
// olBx:lHScroll:=.F. // windows style en el recurso = 0x50210000 SI, lHScroll .f. NO funciona
// oLbx:aCols[1]:lMergeVert := .t. // si lo pongo casca el programa
oLbx:nMarqueeStyle := MARQSTYLE_HIGHLROW // HighL Row := 5
oLbx:aCols[1]:bClrstd := {|| { CLR_BLACK, RGB(233,230,249) }}
REDEFINE XBROWSE oLbxp ID 120 OF oDDAbmlins;
HEADERS "Obra","Prespto","Descripcion","Importe";
COLUMNS "PL_OBRA","PL_PRES","PL_DESC","PL_IMPORT" ;
SIZES 45,40,175,80;
FOOTERS AUTOCOLS LINES CELL ;
ALIAS "pliva";
ON CHANGE (refrescapre())
oLbxp:bClrSelFocus := { || { CLR_BLUE, nRGB( 230, 255, 230 ) } }
oLbxp:aCols[1]:lMergeVert := .t.
// oLbxP:nMarqueeStyle := MARQSTYLE_HIGHLCELL // HighL Row := 5 MARQSTYLE_DOTEDCELL 1 MARQSTYLE_SOLIDCELL 2 MARQSTYLE_HIGHLCELL 3 MARQSTYLE_HIGHLROWRC 4 MARQSTYLE_HIGHLROW 5
oLbxp:aCols[1]:bClrstd := {|| { CLR_BLUE, RGB(250,252,213) }} // lo quito porque pongo lMergeVert
oLbxp:lFooter=.T.
oLbxp:aCols[4]:nFooterType := AGGR_TOTAL
ACTIVATE DIALOG oDDAbmLins NOWAIT;
ON INIT (oDifp:Disable(), Toma_Lin(),deshabilitap(),oLbx:Refresh(), oLbxp:Refresh())
IMAGEN 2
Alguna Idea ¿?
p.d. Mis disculpas por escribir en castellano en foros en Ingles
Un saludo
___________________________________________________
La mente es como un paracaídas, solo funciona si se abre
Harbour 3.2.0dev (r1601050904) , Fivewin 16.04
___________________________________________________
La mente es como un paracaídas, solo funciona si se abre
Harbour 3.2.0dev (r1601050904) , Fivewin 16.04
- nageswaragunupudi
- Posts: 8017
- Joined: Sun Nov 19, 2006 5:22 am
- Location: India
- Contact:
Re: Import and Export to Excel
Can you provide a sample that we can build at our place and test?
Regards
G. N. Rao.
Hyderabad, India
G. N. Rao.
Hyderabad, India
Re: Import and Export to Excel
Hello, Mr Rao
make an example is too complex master/detail etc.. and not worth it. only say that this effect after an operation to insert, update or delete the XBROWSE occurs.
Anyway thank you very much for everything.
make an example is too complex master/detail etc.. and not worth it. only say that this effect after an operation to insert, update or delete the XBROWSE occurs.
Anyway thank you very much for everything.
Un saludo
___________________________________________________
La mente es como un paracaídas, solo funciona si se abre
Harbour 3.2.0dev (r1601050904) , Fivewin 16.04
___________________________________________________
La mente es como un paracaídas, solo funciona si se abre
Harbour 3.2.0dev (r1601050904) , Fivewin 16.04
- damianodec
- Posts: 372
- Joined: Wed Jun 06, 2007 2:58 pm
- Location: Italia
- Contact:
Re: Import and Export to Excel
hi
I have this Browse with double headers
and this is in Excel:
is it possible get in excel the same double headers?
thank you
ciao
Damiano
I have this Browse with double headers
and this is in Excel:
is it possible get in excel the same double headers?
thank you
ciao
Damiano
FiveWin for xHarbour 17.09 - Sep. 2017 - Embarcadero C++ 7.00 for Win32
FWH 64 for Harbour 19.06 (MSVC++) Jun. 2019 - Harbour 3.2.0dev (r1904111533)
Visual Studio 2019 - Pelles C V.8.00.60 (Win64)
FWH 64 for Harbour 19.06 (MSVC++) Jun. 2019 - Harbour 3.2.0dev (r1904111533)
Visual Studio 2019 - Pelles C V.8.00.60 (Win64)
- damianodec
- Posts: 372
- Joined: Wed Jun 06, 2007 2:58 pm
- Location: Italia
- Contact:
Re: Import and Export to Excel
hi,
any help is is appreciated.
any help is is appreciated.
FiveWin for xHarbour 17.09 - Sep. 2017 - Embarcadero C++ 7.00 for Win32
FWH 64 for Harbour 19.06 (MSVC++) Jun. 2019 - Harbour 3.2.0dev (r1904111533)
Visual Studio 2019 - Pelles C V.8.00.60 (Win64)
FWH 64 for Harbour 19.06 (MSVC++) Jun. 2019 - Harbour 3.2.0dev (r1904111533)
Visual Studio 2019 - Pelles C V.8.00.60 (Win64)
Re: Import and Export to Excel
Puoi costruirti le pagine di Excel
Cercando nel forum trovi altri comandi ...
oAs:Cells( 2, 5 ):FormulaLocal := "=CONTA.VALORI(A4:A20000)"
oAs:SaveAs("Nome File")
saluti
Stefano
Code: Select all
oExcel := CreateObject( "Excel.Application" )
oExcel:WorkBooks:Add()
oAs := oExcel:Activesheet()
oAs:Cells:Font:Name := "Calibri"
oAs:Cells:Font:Size := 11
oAs:Columns( 1 ):ColumnWidth := 17
oAs:Columns( 2 ):ColumnWidth := 150
oAs:Cells( 3, 1 ):Value := "Prog"
oAs:Cells( 3, 2 ):Value := "Note"
n = 1
for n = 1 to 2
oAs:Cells(3,n):Borders(7):LineStyle := 1
oAs:Cells(3,n):Borders(8):LineStyle := 1
next
Use archivio
go top
n = 4
do while !eof()
sysrefresh()
oAs:Cells( n, 1 ):Value := archivio->c1) // prog
oAs:Cells( n, 2 ):Value := archivio->c2) // campo note
n = n+1
skip
enddo
n1 = 1
for n1 = 1 to 2
oAs:Cells(n-1,n1):Borders(9):LineStyle := 1
next
oAs:Columns( "A:B" ):WrapText = .T.
/*
oAs:Name := "NC"
* oAs:Columns( "A:T" ):AutoFit()
oAs:Columns( "A:Z" ):VerticalAlignment := -4108
oAs:Columns( "A:Z" ):HorizontalAlignment := -4108
oAs:Columns( "C:C" ):HorizontalAlignment := -4131
oAs:Columns( "Q:Q" ):HorizontalAlignment := -4131
oAs:Columns( "W:W" ):WrapText = .F.
oAs:Range("I2:Q2"):interior:color := rgb(184,204,228)
oAs:Range("I3:Q3"):interior:color := rgb(217,217,217)
oAs:Range("A3:H3"):interior:color := rgb(54,96,146)
oAs:Range("A3:H3"):font:color := rgb(255,255,255)
*/
oExcel:visible := .T
oAs:Cells( 2, 5 ):FormulaLocal := "=CONTA.VALORI(A4:A20000)"
oAs:SaveAs("Nome File")
saluti
Stefano
FWH 14.11 + xHarbour + bcc582
- damianodec
- Posts: 372
- Joined: Wed Jun 06, 2007 2:58 pm
- Location: Italia
- Contact:
Re: Import and Export to Excel
grazie Stefano,
pensavo ci fosse una funzione preconfezionata in xBrowse.
pensavo ci fosse una funzione preconfezionata in xBrowse.
FiveWin for xHarbour 17.09 - Sep. 2017 - Embarcadero C++ 7.00 for Win32
FWH 64 for Harbour 19.06 (MSVC++) Jun. 2019 - Harbour 3.2.0dev (r1904111533)
Visual Studio 2019 - Pelles C V.8.00.60 (Win64)
FWH 64 for Harbour 19.06 (MSVC++) Jun. 2019 - Harbour 3.2.0dev (r1904111533)
Visual Studio 2019 - Pelles C V.8.00.60 (Win64)
- nageswaragunupudi
- Posts: 8017
- Joined: Sun Nov 19, 2006 5:22 am
- Location: India
- Contact:
Re: Import and Export to Excel
As of now, oBrw:ToExcel() method exports all headers, data and footers of XBrowse but not Group Headers.is it possible get in excel the same double headers?
In FWH 17.03 oBrw:ToExcel() will export Group Headers also.
I am suggesting a function which adds Group Headers the excel sheet now being exported by XBrowse.
Please use this function to export from xbrowse instead of directly calling oBrw:ToExcel()
Code: Select all
function XbrToExcelWithGroups( oBrw )
local oExcel, oSheet
local n, nStart := 0, nUpto, cGrp, cPrv, oRange
if oBrw:lGrpHeader == .t.
oSheet := oBrw:ToExcel()
oExcel := oSheet:Parent:Application
WITH OBJECT oSheet:Rows( "1:1" )
:Insert()
:Font:Bold := .t.
END
for n := 1 to Len( oBrw:aCols )
cGrp := oBrw:aCols[ n ]:cGrpHdr
if Empty( cGrp )
cPrv := nil
if nStart > 0
oRange := oSheet:Range( oSheet:Cells( 1, nStart ), oSheet:Cells( 1, nUpto ) )
oRange:MergeCells := .t.
oRange:HorizontalAlignment := -4108
endif
nStart := 0
nUpto := 0
oRange := oSheet:Range( oSheet:Cells( 1, n ), oSheet:Cells( 2, n ) )
oRange:MergeCells := .t.
else
if cGrp == cPrv
nUpto := n
else
oSheet:Cells( 1, n ):Value := cGrp
cPrv := cGrp
if nStart > 0
oRange := oSheet:Range( oSheet:Cells( 1, nStart ), oSheet:Cells( 1, nUpto ) )
oRange:MergeCells := .t.
oRange:HorizontalAlignment := -4108
endif
nStart := n
nUpto := n
endif
endif
next
endif
return nil
Regards
G. N. Rao.
Hyderabad, India
G. N. Rao.
Hyderabad, India
- damianodec
- Posts: 372
- Joined: Wed Jun 06, 2007 2:58 pm
- Location: Italia
- Contact:
Re: Import and Export to Excel
Excellent support
thank you, I'll try it later...
thank you, I'll try it later...
FiveWin for xHarbour 17.09 - Sep. 2017 - Embarcadero C++ 7.00 for Win32
FWH 64 for Harbour 19.06 (MSVC++) Jun. 2019 - Harbour 3.2.0dev (r1904111533)
Visual Studio 2019 - Pelles C V.8.00.60 (Win64)
FWH 64 for Harbour 19.06 (MSVC++) Jun. 2019 - Harbour 3.2.0dev (r1904111533)
Visual Studio 2019 - Pelles C V.8.00.60 (Win64)
- Marc Venken
- Posts: 727
- Joined: Tue Jun 14, 2016 7:51 am
Re: Import and Export to Excel
I know that oBrw:ToExcel() will use all colums of the browse to export.nageswaragunupudi wrote:As of now, oBrw:ToExcel() method exports all headers, data and footers of XBrowse but not Group Headers.is it possible get in excel the same double headers?
In FWH 17.03 oBrw:ToExcel() will export Group Headers also.
I am suggesting a function which adds Group Headers the excel sheet now being exported by XBrowse.
Please use this function to export from xbrowse instead of directly calling oBrw:ToExcel()Code: Select all
function XbrToExcelWithGroups( oBrw ) local oExcel, oSheet local n, nStart := 0, nUpto, cGrp, cPrv, oRange if oBrw:lGrpHeader == .t. oSheet := oBrw:ToExcel() oExcel := oSheet:Parent:Application WITH OBJECT oSheet:Rows( "1:1" ) :Insert() :Font:Bold := .t. END for n := 1 to Len( oBrw:aCols ) cGrp := oBrw:aCols[ n ]:cGrpHdr if Empty( cGrp ) cPrv := nil if nStart > 0 oRange := oSheet:Range( oSheet:Cells( 1, nStart ), oSheet:Cells( 1, nUpto ) ) oRange:MergeCells := .t. oRange:HorizontalAlignment := -4108 endif nStart := 0 nUpto := 0 oRange := oSheet:Range( oSheet:Cells( 1, n ), oSheet:Cells( 2, n ) ) oRange:MergeCells := .t. else if cGrp == cPrv nUpto := n else oSheet:Cells( 1, n ):Value := cGrp cPrv := cGrp if nStart > 0 oRange := oSheet:Range( oSheet:Cells( 1, nStart ), oSheet:Cells( 1, nUpto ) ) oRange:MergeCells := .t. oRange:HorizontalAlignment := -4108 endif nStart := n nUpto := n endif endif next endif return nil
I know that I can erase (hide) colums in Xbrowse, and then call the export in order to have a selected exell file.
But is it also possible to have oBrw:ToExcel(col1,col3,col6,col9) or a array of selected cols ?
Marc Venken
Using: FWH 20.08 with Harbour
Using: FWH 20.08 with Harbour
- nageswaragunupudi
- Posts: 8017
- Joined: Sun Nov 19, 2006 5:22 am
- Location: India
- Contact:
Re: Import and Export to Excel
Yes
Specify array of columns as 3rd parameter.
ToExcel( nil, nil, aCols )
Specify array of columns as 3rd parameter.
ToExcel( nil, nil, aCols )
Regards
G. N. Rao.
Hyderabad, India
G. N. Rao.
Hyderabad, India
Re: Import and Export to Excel
Mr. Rao,
The test is showing different results adding Your function and from oBrw:ToExcel()
I added two testbuttons and Your function to the 1. sample
CODE_1.prg
FUNCTION GRPC1_SEC1( oFld1, nSavePage ) // 1. Sample of section 1
Your function
oBrw1:ToExcel()
Two new buttons :
regards
Uwe
The test is showing different results adding Your function and from oBrw:ToExcel()
I added two testbuttons and Your function to the 1. sample
CODE_1.prg
FUNCTION GRPC1_SEC1( oFld1, nSavePage ) // 1. Sample of section 1
Your function
oBrw1:ToExcel()
Two new buttons :
Code: Select all
// -------------------------------------------------------------------- SECTION 1 Page 1 - 4
FUNCTION GRPC1_SEC1( oFld1, nSavePage )
LOCAL aBitmaps1, oTitle, oText1, oBtn1, oBtn2
...
...
...
@ 250, 25 BTNBMP oBtn1 OF oFld1:aDialogs[1] ;
SIZE 80, 15 PIXEL 2007 ;
NOBORDER ;
PROMPT " &Export sample 1 " ;
FILENAME c_Path1 + "EXCEL.bmp" ;
ACTION XBRTOEXCELWITHGROUPS( oBrw1 ) ;
FONT oSFont ;
LEFT
oBtn1:cToolTip = { "Excel","EXPORT", 1, CLR_BLACK, 14089979 }
oBtn1:SetColor( 0, )
@ 250, 120 BTNBMP oBtn2 OF oFld1:aDialogs[1] ;
SIZE 80, 15 PIXEL 2007 ;
NOBORDER ;
PROMPT " &oBrw1:ToExcel() " ;
FILENAME c_Path1 + "EXCEL.bmp" ;
ACTION oBrw1:ToExcel() ;
FONT oSFont ;
LEFT
oBtn2:cToolTip = { "Excel","EXPORT", 1, CLR_BLACK, 14089979 }
oBtn2:SetColor( 0, )
RETURN NIL
Uwe
Since 1995 ( the first release of FW 1.9 )
i work with FW.
If you have any questions about special functions, maybe i can help.
i work with FW.
If you have any questions about special functions, maybe i can help.
- nageswaragunupudi
- Posts: 8017
- Joined: Sun Nov 19, 2006 5:22 am
- Location: India
- Contact:
Re: Import and Export to Excel
if you are using latest version of FWH you should not add the code. It is already builtin
Regards
G. N. Rao.
Hyderabad, India
G. N. Rao.
Hyderabad, India
- ryugarai27
- Posts: 65
- Joined: Fri Feb 13, 2009 12:03 pm
- Location: Manila, Philippines
- Contact:
Re: Import and Export to Excel
Hi Rao,
The function ArrTranspose( oRange:Value ) produces an 'out of memory' error when using large Excel file:
Application
===========
Path and name: D:\projects\fwh1706\excelrange\excelrange2.exe (32 bits)
Size: 3,486,720 bytes
Compiler version: xHarbour 1.2.3 Intl. (SimpLex) (Build 20161218)
FiveWin version: FWHX 17.06
C compiler version: Borland/Embarcadero C++ 7.0 (32-bit)
Windows version: 6.1, Build 7601 Service Pack 1
Time from start: 0 hours 0 mins 39 secs
Error occurred at: 04/23/19, 10:30:11
Error description: Error Excel.Application:WORKBOOKS:OPEN:ACTIVESHEET:USEDRANGE/14 E_OUTOFMEMORY: VALUE
Args:
Stack Calls
===========
Called from: => TOLEAUTO:VALUE( 0 )
Called from: excelrange2.prg => TEST( 12 )
Regards,
rblatoza (FWH1706 + xharbour 1.2.3 + Pelles C)
The function ArrTranspose( oRange:Value ) produces an 'out of memory' error when using large Excel file:
Application
===========
Path and name: D:\projects\fwh1706\excelrange\excelrange2.exe (32 bits)
Size: 3,486,720 bytes
Compiler version: xHarbour 1.2.3 Intl. (SimpLex) (Build 20161218)
FiveWin version: FWHX 17.06
C compiler version: Borland/Embarcadero C++ 7.0 (32-bit)
Windows version: 6.1, Build 7601 Service Pack 1
Time from start: 0 hours 0 mins 39 secs
Error occurred at: 04/23/19, 10:30:11
Error description: Error Excel.Application:WORKBOOKS:OPEN:ACTIVESHEET:USEDRANGE/14 E_OUTOFMEMORY: VALUE
Args:
Stack Calls
===========
Called from: => TOLEAUTO:VALUE( 0 )
Called from: excelrange2.prg => TEST( 12 )
Code: Select all
#include "fivewin.ch"
Function test()
Local oRange,lOpened:=.f.
Local aData
oRange := GetExcelRange( ExePath() + "Large file - All Data.xlsx" )
aData := ArrTranspose( oRange:Value )
xbrowse( aData )
oRange := NIL
return nil
function ExePath()
return cFilePath( GetModuleFileName() )
rblatoza (FWH1706 + xharbour 1.2.3 + Pelles C)