Import and Export to Excel

Re: Import and Export to Excel

Postby nageswaragunupudi » Tue Oct 11, 2016 5:47 pm

No
As of now only 2 levels.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: Import and Export to Excel

Postby gautxori » Wed Oct 12, 2016 8:06 am

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()



Code: Select all  Expand view

      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 1

Image

IMAGEN 2

Image


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
User avatar
gautxori
 
Posts: 69
Joined: Thu Feb 25, 2010 12:44 pm
Location: Plentzia (Bizkaia)

Re: Import and Export to Excel

Postby nageswaragunupudi » Wed Oct 12, 2016 8:20 am

Can you provide a sample that we can build at our place and test?
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: Import and Export to Excel

Postby gautxori » Wed Oct 12, 2016 11:27 am

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.
Un saludo
___________________________________________________
La mente es como un paracaídas, solo funciona si se abre
Harbour 3.2.0dev (r1601050904) , Fivewin 16.04
User avatar
gautxori
 
Posts: 69
Joined: Thu Feb 25, 2010 12:44 pm
Location: Plentzia (Bizkaia)

Re: Import and Export to Excel

Postby damianodec » Tue Mar 21, 2017 7:47 am

hi
I have this Browse with double headers
Image

and this is in Excel:
Image

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)
User avatar
damianodec
 
Posts: 414
Joined: Wed Jun 06, 2007 2:58 pm
Location: Italia

Re: Import and Export to Excel

Postby damianodec » Fri Mar 24, 2017 10:01 am

hi,
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)
User avatar
damianodec
 
Posts: 414
Joined: Wed Jun 06, 2007 2:58 pm
Location: Italia

Re: Import and Export to Excel

Postby stefano » Fri Mar 24, 2017 6:33 pm

Puoi costruirti le pagine di Excel

Code: Select all  Expand view

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


Cercando nel forum trovi altri comandi ...
oAs:Cells( 2, 5 ):FormulaLocal := "=CONTA.VALORI(A4:A20000)"
oAs:SaveAs("Nome File")

saluti
Stefano
FWH 14.11 + xHarbour + bcc582
stefano
 
Posts: 80
Joined: Tue Mar 25, 2008 9:03 pm
Location: ITALIA

Re: Import and Export to Excel

Postby damianodec » Mon Mar 27, 2017 10:15 am

grazie Stefano,
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)
User avatar
damianodec
 
Posts: 414
Joined: Wed Jun 06, 2007 2:58 pm
Location: Italia

Re: Import and Export to Excel

Postby nageswaragunupudi » Mon Mar 27, 2017 3:13 pm

is it possible get in excel the same double headers?


As of now, oBrw:ToExcel() method exports all headers, data and footers of XBrowse but not Group 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  Expand view
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
User avatar
nageswaragunupudi
 
Posts: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: Import and Export to Excel

Postby damianodec » Tue Mar 28, 2017 10:08 am

Excellent support
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)
User avatar
damianodec
 
Posts: 414
Joined: Wed Jun 06, 2007 2:58 pm
Location: Italia

Re: Import and Export to Excel

Postby Marc Venken » Wed Jun 14, 2017 2:53 pm

nageswaragunupudi wrote:
is it possible get in excel the same double headers?


As of now, oBrw:ToExcel() method exports all headers, data and footers of XBrowse but not Group 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  Expand view
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 oBrw:ToExcel() will use all colums of the browse to export.
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 23.04 with Harbour
User avatar
Marc Venken
 
Posts: 1338
Joined: Tue Jun 14, 2016 7:51 am
Location: Belgium

Re: Import and Export to Excel

Postby nageswaragunupudi » Wed Jun 14, 2017 3:19 pm

Yes
Specify array of columns as 3rd parameter.
ToExcel( nil, nil, aCols )
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: Import and Export to Excel

Postby ukoenig » Wed Jun 14, 2017 5:25 pm

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

Image

Your function

Image

oBrw1:ToExcel()

Image

Two new buttons :

Code: Select all  Expand view

// -------------------------------------------------------------------- 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
 


regards
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.
User avatar
ukoenig
 
Posts: 4043
Joined: Wed Dec 19, 2007 6:40 pm
Location: Germany

Re: Import and Export to Excel

Postby nageswaragunupudi » Wed Jun 14, 2017 5:49 pm

if you are using latest version of FWH you should not add the code. It is already builtin
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: Import and Export to Excel

Postby ryugarai27 » Tue Apr 23, 2019 2:48 am

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 )



Code: Select all  Expand view
#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() )


Regards,
rblatoza (FWH1706 + xharbour 1.2.3 + Pelles C)
User avatar
ryugarai27
 
Posts: 65
Joined: Fri Feb 13, 2009 12:03 pm
Location: Manila, Philippines

PreviousNext

Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: Maurizio, nageswaragunupudi and 12 guests