OREPORT:TOEXCEL()

[FranciscoA]

Dear G. N. Rao.
Can you tell me where I'm wrong or I'm doing wrong?
I'm trying to use the magnificent oReport METHOD: ToExcel ().I made the changes suggested in this post: viewtopic.php?f=3&t=23809&hilit=formula # p128306, but it don't works.

I modified the classe report.prg like this:

Line 2178:

Code: Select all  Expand view

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

Line 2288:

Code: Select all  Expand view

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

Line 2305:

Code: Select all  Expand view

WITH OBJECT oSheet:Cells( nRow, nCol )
     // :Formula := "=SUBTOTAL( 9, " + ;     //English
     :Formula := "=SUBTOTALES( 9; " + ;     //Spanish
     oSheet:Range( oSheet:Cells( nFirstRow, nCol ), ;
     oSheet:Cells( nRow - 1, nCol ) ):Address( .f., .f. ) + ")"
END
 

It compiles fine but gives errors when running as the following picture:


Uploaded with ImageShack.us

The code I use is as follows: (It works well on commun reports, but not export to Excel)

Code: Select all  Expand view

  REPORT oReport;
      TITLE  EMPRESA, MEMBRE +" - "+SUCURSAL,;
             "REPORTE DE INVENTARIO"+FECHADOR(dFecha);
      HEADER "Pag No. "+alltrim(str(oReport:nPage)) RIGHT ;
      FOOTER " "," "," "," "," ","Elaborado Por:___________________________________________ ", " ", " ", " " CENTERED ;
      PREVIEW


      COLUMNS....
   END REPORT


     IF oReport:lCreated
        oReport:bStartPage:={|| oReport:aColumns[7]:ntotal:=0 }
        oReport:bEndPage:={|| nTotAcum+=oReport:aColumns[7]:ntotal }
        oReport:bPostEnd:={|| oReport:Say(1,"VALOR TOTAL DE INVENTARIO",2), oReport:Say(7,transform(nTotAcum,"9,999,999,999.99"),2) }
        oReport:cPageTotal := "TOTAL DE ESTA PAGINA..."
        oReport:cGrandTotal := "TOTAL DE ESTA PAGINA..."


        oReport:bInit := {|| oMaeprodt:GoTop() }   //oMaeprodt proviene de un MySql:Query(
        oReport:bSkip := {|| oMaeprodt:Skip() }
        oReport:ToExcel()
 
     ENDIF


     ACTIVATE REPORT oReport WHILE !oMaeprodt:eof()
.....
 

Best regards.

Fwh1204-xHarbour-MySql-TmySql
Windows 7 Ultimate - Excel 2007

[FranciscoA]

Hello, this is for information only.
Please let's take a look at this little fault using oBrw: ToExcel () in XBrowse() function. (xbrowse.prg)
Look at the data in the excel sheet shaded yellow, and the shaded line xbrowse. (Data is not complete)

I am using MySql-TMySql.



Regards.

[FranciscoA]

To all:

Hello, this is for information only.
Please let's take a look at this little fault using oBrw: ToExcel () in XBrowse() function. (xbrowse.prg)
Look at the data in the excel sheet shaded yellow, and the shaded line xbrowse. (Data is not complete)

Please disregard this last post.
The problem was solved by rebuilding the table MySql.
I Do not know what could be the fault, MySql did not send me any message.

Sorry!

On the other hand, I remain pending on the case of oReport: ToExcel ()

Thanks.

[nageswaragunupudi]

I understand that XBrowse ToExcel() is working well on non-english versions of Excel, but Report's ToExcel() is not working in such cases.
If you confirm this, I shall work on improving report's toexcel() method to work well on other language installations also the same as xbrowse's toExcel method.

[FranciscoA]

Dear nageswaragunupudi, Thanks for your attention.

Using the same sample and using the original classe I got the error as follows:

Application
===========
Path and name: D:\FAPSOFT\NEWFIC32_MYSQL\Wfic32.Exe (32 bits)
Size: 2,446,336 bytes
Compiler version: xHarbour build 1.2.1 Intl. (SimpLex) (Rev. 9445)
FiveWin Version: FWHX 12.03
Windows version: 6.1, Build 7601 Service Pack 1

Time from start: 0 hours 0 mins 19 secs
Error occurred at: 09/06/2012, 20:38:43
Error description: Error Excel.Application:WORKBOOKS:ADD:ACTIVESHEET:CELLS/0 S_OK: _FORMULA
Args:
[ 1] = C =SUBTOTAL( 9, G7:G8)

Stack Calls
===========
Called from: => TOLEAUTO:_FORMULA( 0 )
Called from: .\source\classes\REPORT.PRG => TREPORT:TOEXCEL( 2307 )
Called from: D:\FAPSOFT\NEWFIC~1\wfic32.prg => IMPMAEREP( 876 )
Called from: D:\FAPSOFT\NEWFIC~1\wfic32.prg => (b)MENUREPMAEPT( 234 )
Called from: .\source\classes\MENU.PRG => TMENU:ACTIVATE( 494 )
Called from: D:\FAPSOFT\NEWFIC~1\wfic32.prg => MAEIMPRI( 224 )
Called from: D:\FAPSOFT\NEWFIC~1\wfic32.prg => (b)MAEPRODT( 111 )
Called from: .\source\classes\BUTTON.PRG => TBUTTONBMP:CLICK( 157 )
Called from: .\source\classes\CONTROL.PRG => TBUTTON:HANDLEEVENT( 1654 )
Called from: .\source\classes\BUTTONB.PRG => TBUTTONBMP:HANDLEEVENT( 189 )
Called from: .\source\classes\WINDOW.PRG => _FWH( 3153 )
Called from: => SENDMESSAGE( 0 )
Called from: .\source\classes\DIALOG.PRG => TDIALOG:COMMAND( 379 )
Called from: => TWINDOW:HANDLEEVENT( 0 )
Called from: .\source\classes\DIALOG.PRG => TDIALOG:HANDLEEVENT( 886 )
Called from: => DIALOGBOX( 0 )
Called from: .\source\classes\DIALOG.PRG => TDIALOG:ACTIVATE( 270 )
Called from: D:\FAPSOFT\NEWFIC~1\wfic32.prg => MAEPRODT( 140 )
Called from: D:\FAPSOFT\NEWFIC~1\wfic32.prg => (b)BUILDMENU( 460 )
Called from: .\source\classes\MENU.PRG => TMENU:COMMAND( 461 )
Called from: .\source\classes\WINDOW.PRG => TWINDOW:COMMAND( 1027 )
Called from: .\source\classes\MDIFRAME.PRG => TMDIFRAME:COMMAND( 247 )
Called from: => TMDIFRAME:HANDLEEVENT( 0 )
Called from: .\source\classes\WINDOW.PRG => _FWH( 3153 )
Called from: => WINRUN( 0 )
Called from: .\source\classes\WINDOW.PRG => TMDIFRAME:ACTIVATE( 980 )
Called from: D:\FAPSOFT\NEWFIC~1\wfic32.prg => MAIN( 262 )

Best regards

[nageswaragunupudi]

Yes I agree that this line generates runtime error on non-English installations of Excel. We need to modify the method to adopt the same approach as in XBrowse's ToExcel() method, which deals with this issue.

Meanwhile, will you please try a small modification?
In the line 2178 of the report.prg, please substitute :Formula with :ForumlaLocal and check if the error still persists.

I

[FranciscoA]

Dear Rao, the errors persists, in both ways:

oSheet:Cells( nRow, nCol ):FormulaLocal := "=SUBTOTAL(9," + cAdr + ")" //English // [ 1] = C =SUBTOTAL( 9, G7:G8)

or
oSheet:Cells( nRow, nCol ):FormulaLocal := "=SUBTOTALES(9;" + cAdr + ")" //Spanish // as in previous images

Regards

[FranciscoA]

Using DBFs : ( classe modified from SUBTOTAL to "SUBTOTALES")

This image is the original report
[img=http://img831.imageshack.us/img831/3803/usingdbf1.th.jpg]

This is the exportation oReport:ToExcel()
[img=http://img210.imageshack.us/img210/6783/usingdbf2.th.jpg]

[lucasdebeltran]

Hello,

Any update?.

I am interested too.

Thanks.

[jose_murugosa]

Hola, estoy teniendo el mismo problema, es decir este mensaje:

Error description: Error Excel.Application:WORKBOOKS:ADD:ACTIVESHEET:CELLS/0 S_OK: _FORMULA

Ahora, ocurre algo interesante, el sistema está en red, y el error solo se produce en una terminal específica y no en todas, las otras 6 que usan el programa no dan problemas.

Tal vez esto aporte alguna pista

[ORibeiro]

I have the same problem with numeric format in another language.

viewtopic.php?f=3&t=29456

This code show the problem with Excel at Portuguese from Brazil:

Code: Select all  Expand view

#include 'fivewin.ch'
#include 'xbrowse.ch'

function Main()
   local oWnd, oBar, oBrw, nTotal:=0
   /*
   A. American format: "999,999,999.99"
   E. European format: "999.999.999,99"
   I. Indian   format: "99,99,999.99"
   */
   FwNumFormat( 'E', .T. )  // European Format
   DBCREATE("TESTXLS",{{"DESC   ","C",010,000},;
                       {"VAL1   ","N",010,001},;
                       {"VAL2   ","N",010,002},;
                       {"VAL3   ","N",010,003},;
                       {"VAL4   ","N",010,004},;
                       {"VAL5   ","N",010,005}})

   USE TESTXLS NEW ALIAS TESTXLS
   FOR nTotal=1 To 20
      APPEND BLANK
      REPL DESC WITH "Linha "+STRZERO(nTotal,2)
      REPL VAL1 WITH 99999 * nTotal / 777
      REPL VAL2 WITH 99999 * nTotal / 777
      REPL VAL3 WITH 99999 * nTotal / 777
      REPL VAL4 WITH 99999 * nTotal / 777
      REPL VAL5 WITH 99999 * nTotal / 777
   NEXT

   DEFINE WINDOW ownd

   DEFINE BUTTONBAR oBar OF oWnd SIZE 80,32 2007

   DEFINE BUTTON OF oBar PROMPT 'Excel' ;
          ACTION MsgMeter( { |oMeter, oText, oDlg, lEnd | ;
          Export2Excel( oBrw, oMeter, oText, oDlg, @lEnd ) } )

   DEFINE BUTTON OF oBar PROMPT 'Quit'  ACTION WndMain():End()

   SET MESSAGE OF oWnd TO '' 2007

   @ 0,0 XBROWSE oBrw OF oWnd ALIAS 'TESTXLS' AUTOCOLS CELL LINES

   // Realmente nao funciona quando exporto para o Broffice/Excel
   oBrw:aCols[2]:bFooter = {|| TRANSFORM(Field->VAL1, "@E 9.999.999,99") }
   oBrw:aCols[3]:bFooter = {|| TRANSFORM(Field->VAL2, "@E 9.999.999,99") }
   oBrw:aCols[4]:bFooter = {|| TRANSFORM(Field->VAL3, "@E 9.999.999,99") }
   oBrw:aCols[5]:bFooter = {|| TRANSFORM(Field->VAL4, "@E 9.999.999,99") }
   oBrw:aCols[6]:bFooter = {|| TRANSFORM(Field->VAL5, "@E 9.999.999,99") }

   oBrw:MakeTotals()
   oBrw:refresh()

   oBrw:CreateFromCode()

   oWnd:oClient      := oBrw

   ACTIVATE WINDOW oWnd MAXIMIZED

   CLOSE DATA

return nil

static function Export2Excel( oBrw, oMeter, oText, oDlg, lEnd )
   oBrw:ToExcel( { |n,t| oMeter:nTotal := t, ;
                         oMeter:Set( n ), ;
                         oText:SetText( Str(n) + '/' + Str(t) ), ;
                         oDlg:Update(), .t. } )
return nil
 

[jose_murugosa]

Estoy teniendo este error también, lo curioso es que este reporte que da error lo da en 3 de las 11 terminales pero no en las restantes ocho.

Sería interesante saber que puede ocasionar este error, pareciera ser algo externo a la clase, tal vez algun detalle de configuración del Excel.