export TREPORT to Excel in Preview

[lucasdebeltran]

Hello,

I need to export some TREPORTS to Excel.

The ideal will be as the Preview exports to Excel and Word, to include Excel.

Is it possible please?.

Thank you very much.

Regards!

[Adolfo]

Para eso hay buscador

http://forums.fivetechsupport.com/search.php?keywords=report+excel&terms=all&author=&sc=1&sf=all&sk=t&sd=d&sr=topics&st=0&ch=300&t=0&submit=Search

[lucasdebeltran]

Adolfo,

Thank you. I did it before .

But REPEXCEL does not work with latest Harbour builds.

I also read in somewhere that Mr. Nages was preparing this feature, as it is requested from many users.

Best regards,

[nageswaragunupudi]

If the report is simple, you may use oReport:ToExcel().
This method is available for quite sometime.
Any feedback is appreciated.

[lucasdebeltran]

Hello,

i did not know that method.

But it is not working:

This is a sample:

Code: Select all  Expand view

#include "FiveWin.ch"
#include "report.ch"
#include "inkey.ch"

Function main()

     LOCAL oFont1, oFont2
     LOCAL oReport


     DEFINE FONT oFont1 NAME "ARIAL" SIZE 0,-10
     DEFINE FONT oFont2 NAME "ARIAL" SIZE 0,-10 BOLD



     SetKey(VK_F2, { || oReport:ToExcel() })


     USE TEST  NEW
     dbgotop()



     REPORT oReport TITLE  "*** Mi Primer Reporte ***"  ;
          FONT oFont1, oFont2 ;
          PREVIEW CAPTION "Mensaje del título"

     //oReport:bPreInit:= { || DbGoTop() }

     COLUMN TITLE "St"         DATA Test->State

     COLUMN TITLE "Nombre", "Apellido" ;
            DATA  Test->First, Test->Last

     COLUMN TITLE "Direccion", "Ciudad"  ;
            DATA  Test->Street, Test->City

     COLUMN TITLE "   Salario"  ;
            DATA  Test->Salary ;
            TOTAL              ;
            RIGHT              ;
            PICTURE "9,999,999";
            FONT 2             ;
            SIZE 9



     END REPORT

     ACTIVATE REPORT oReport


     CLOSE TEST

     oFont1:End()
     oFont2:End()

RETURN NIL
 

I get:

Error description: (DOS Error -2147352567) WINOLE/1006 Argument error: _FORMULA
Args:
[ 1] = C =SUBTOTAL( 9, D4:D5)

Stack Calls
===========
Called from: => TOLEAUTO:_FORMULA( 0 )
Called from: .\source\classes\REPORT.PRG => TREPORT:TOEXCEL( 2313 )
Called from: test.prg => (b)MAIN( 18 )




If I edit report.ch and comment out:

Code: Select all  Expand view

if lTotal
      for nCol := 1 to nCols
         oCol     := ::aColumns[ nCol ]
         if oCol:lTotal
            WITH OBJECT oSheet:Cells( nRow, nCol )
               //:Formula := "=SUBTOTAL( 9, " + ;
               //   oSheet:Range( oSheet:Cells( nFirstRow, nCol ), ;
               //   oSheet:Cells( nRow - 1, nCol ) ):Address( .f., .f. ) + ")"
            END
            if ( nFont := Eval( oCol:bTotalFont ) ) != aFont[ nCol ]
               ::xlSetFont( oSheet:Cells( nRow, nCol ), ::aFont[ nFont ] )
            endif
         endif
      next
   endif
 

I get an Excel with headers and titles but no content:
http://i44.tinypic.com/34xgfv5.png


I am sorry about the bad news.

Thank your for our help and support.

regards,

[lucasdebeltran]

This is test.dbf:
http://uploaded.to/file/qpght3lt

[nageswaragunupudi]

First, please try with a simple sample.

Please add the line
oReport:bInit := { || TEST->( dbGoTop() ) }
This is necessary.

Please also remove the TOTAL clause and make a single line report.

Please let me know the result and then we shall make the enhancements you may like to have.

About error

Error description: (DOS Error -2147352567) WINOLE/1006 Argument error: _FORMULA
Args:
[ 1] = C =SUBTOTAL( 9, D4:D5)

The formula "SUBTOTAL" works well with English International installation of Excel. For installations in other languages we need to substitute "SUBTOTAL" with the keyword of the language of installation.

Depending on your feedback, we shall make the improvements necessary.

[lucasdebeltran]

Hello,

Thanks for reply.

Yes, with your code it is working fine:
oReport:bInit := { || TEST->( dbGoTop() ) }


But, does not do it automatically Treport?.

Can´t it be done by default in ToExcel() method?.


Sure, in spanish function SUBTOTAL is SUBTOTALES.

But I still get the error.

I use Excel 2003.

[nageswaragunupudi]

Yes, with your code it is working fine:
oReport:bInit := { || TEST->( dbGoTop() ) }

But, does not do it automatically Treport?.
Can´t it be done by default in ToExcel() method?.

The way TReport was originally written, it only knows how to go forward. It does not know how to go to the top or go backwards. Also it does not know if the source data is dbf or array or something else.

By the time preview is generated, TReport has already traversed the data till eof. Now if we ask TReport to go to top again and export to excel, we need to tell TReport how to go to Top of the data.

I proposed to use bInit, because I did not want to add another data/variable for this purpose alone.

Now, please let me know whether the ToExcel() method is working with two or more lines in a column. Depending on your feedback and needs let me see to what extent we can improve this method.

Sure, in spanish function SUBTOTAL is SUBTOTALES.

But I still get the error.

I use Excel 2003.

I guess you corrected the source of TReport.
Please correct like this:
Instead of this line:

Code: Select all  Expand view

                          oSheet:Cells( nRow, nCol ):Formula := "=SUBTOTAL(9," + cAdr + ")"
 

please substitute

Code: Select all  Expand view

                          oSheet:Cells( nRow, nCol ):Formula := "=SUBTOTALES(9;" + cAdr + ")"
 

Please note ; (semi-colon) after "SUBTOTALES(9"

I shall be glad if you can give me your feedback on this.

[lucasdebeltran]

Mr. Nages,

Thank you very much.

Yes, know it is working fine with SUBTOTALES(9;.


In case of array, what is the equivalent for oReport:bInit := { || dbGoTop() }?.

In order to avoid the use of bInit, I modified ToExcel() method and I added a dbgotop(). It is working fine.

My aim is to detect if datasoruce is a dbf or an Array. How can I detect is an array is the datasource of the Report?.

I think I can check bSkip?. In a DBF is always nil.

Best regards

[carlos vargas]

me parece que pueedes usar el metodo del propio xbrowse

::GoTop()


ya que este metodo hara el salto al primer registro
y ya se encarga de determinar si es un rdd o un array.

salu2
carlos vargas

[nageswaragunupudi]

me parece que pueedes usar el metodo del propio xbrowse

::GoTop()


ya que este metodo hara el salto al primer registro
y ya se encarga de determinar si es un rdd o un array.

salu2
carlos vargas

This discussion is about METHOD ToExcel() of TReport.
This is independent of XBrowse.

[lucasdebeltran]

That´s right.

But it is a good idea to create a new method in TReport, oreport:gotop(), so it´s called in ToExcel() method at beginning: ::gotop()

[carlos vargas]

rao,
its correct, trport <> xbrowse.

sorry for my fault.

salu2

[lucasdebeltran]

Up.

But it is a good idea to create a new method in TReport, oreport:gotop(), so it´s called in ToExcel() method at beginning: ::gotop()