FiveTech Software tech support forums

www.FiveTechSoft.com
https://forums.fivetechsupport.com/

Import and Export to Excel

https://forums.fivetechsupport.com/viewtopic.php?f=3&t=20763

Page 2 of 3

Re: Import and Export to Excel

PostPosted: Tue Oct 11, 2016 5:47 pm
by nageswaragunupudi
No
As of now only 2 levels.

Re: Import and Export to Excel

PostPosted: Wed Oct 12, 2016 8:06 am
by gautxori
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

Re: Import and Export to Excel

PostPosted: Wed Oct 12, 2016 8:20 am
by nageswaragunupudi
Can you provide a sample that we can build at our place and test?

Re: Import and Export to Excel

PostPosted: Wed Oct 12, 2016 11:27 am
by gautxori
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.

Re: Import and Export to Excel

PostPosted: Tue Mar 21, 2017 7:47 am
by damianodec
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

Re: Import and Export to Excel

PostPosted: Fri Mar 24, 2017 10:01 am
by damianodec
hi,
any help is is appreciated.

Re: Import and Export to Excel

PostPosted: Fri Mar 24, 2017 6:33 pm
by stefano
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

Re: Import and Export to Excel

PostPosted: Mon Mar 27, 2017 10:15 am
by damianodec
grazie Stefano,
pensavo ci fosse una funzione preconfezionata in xBrowse.

Re: Import and Export to Excel

PostPosted: Mon Mar 27, 2017 3:13 pm
by nageswaragunupudi
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
 

Re: Import and Export to Excel

PostPosted: Tue Mar 28, 2017 10:08 am
by damianodec
Excellent support
thank you, I'll try it later...

Re: Import and Export to Excel

PostPosted: Wed Jun 14, 2017 2:53 pm
by Marc Venken
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 ?

Re: Import and Export to Excel

PostPosted: Wed Jun 14, 2017 3:19 pm
by nageswaragunupudi
Yes
Specify array of columns as 3rd parameter.
ToExcel( nil, nil, aCols )

Re: Import and Export to Excel

PostPosted: Wed Jun 14, 2017 5:25 pm
by ukoenig
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 :?:

Re: Import and Export to Excel

PostPosted: Wed Jun 14, 2017 5:49 pm
by nageswaragunupudi
if you are using latest version of FWH you should not add the code. It is already builtin

Re: Import and Export to Excel

PostPosted: Tue Apr 23, 2019 2:48 am
by ryugarai27
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)
All times are UTC
Page 2 of 3
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/