Page 1 of 1

problem with report to excel

Posted: Tue Oct 15, 2013 5:18 am
by Patrick Chalvet
Each time I try to export report to excel I have this error;

Time from start: 0 hours 0 mins 27 secs
Error occurred at: 13/10/2013, 17:50:08
Error description: (DOS Error -2147352567) WINOLE/1006 (0x800A03EC): _FORMULA
Args:
[ 1] = C =SUBTOTAL(9,B7:B25)

Stack Calls
===========
Called from: => TOLEAUTO:_FORMULA( 0 )
Called from: .\source\classes\REPORT.PRG => TREPORT:TOEXCEL( 2167 )
Called from: .\source\classes\RPREVIEW.PRG => (b)TPREVIEW( 81 )
Called from: .\source\classes\RPREVIEW.PRG => TPREVIEW:EXPORTTOMSEXCEL( 0 )
Called from: .\source\classes\RPREVIEW.PRG => (b)TPREVIEW_BUILDBUTTONBAR( 362 )
Called from: .\source\classes\BTNBMP.PRG => TBTNBMP:CLICK( 471 )
Called from: .\source\classes\BTNBMP.PRG => TBTNBMP:LBUTTONUP( 662 )
Called from: .\source\classes\CONTROL.PRG => TCONTROL:HANDLEEVENT( 1723 )
Called from: .\source\classes\BTNBMP.PRG => TBTNBMP:HANDLEEVENT( 1465 )
Called from: .\source\classes\WINDOW.PRG => _FWH( 3234 )
Called from: => SYSREFRESH( 0 )
Called from: .\source\function\MSGRUN.PRG => STOPUNTIL( 60 )
Called from: .\source\classes\RPREVIEW.PRG => TPREVIEW:ACTIVATE( 143 )
Called from: .\source\classes\RPREVIEW.PRG => RPREVIEW( 1767 )
Called from: .\source\classes\REPORT.PRG => (b)TREPORT_NEW( 170 )
Called from: .\source\classes\REPORT.PRG => TREPORT:ACTIVATE( 850 )
Called from: Exobal.prg => NBALCLI( 1453 )
Called from: EXO.PRG => (b)BUILDMENU( 509 )
Called from: .\source\classes\MENU.PRG => TMENU:COMMAND( 463 )
Called from: .\source\classes\WINDOW.PRG => TWINDOW:COMMAND( 1050 )
Called from: .\source\classes\MDIFRAME.PRG => TMDIFRAME:COMMAND( 255 )
Called from: => TMDIFRAME:HANDLEEVENT( 0 )
Called from: .\source\classes\WINDOW.PRG => _FWH( 3234 )
Called from: => WINRUN( 0 )
Called from: .\source\classes\WINDOW.PRG => TMDIFRAME:ACTIVATE( 1003 )
Called from: EXO.PRG => MAIN( 80 )

Harbour 3.2.0dev (r1307180024)
Fivewin 13.08
Microsoft Visual Studio 2010
Version 10.0.30319.1 RTMRel
Version installée : VC Express

Re: problem with report to excel

Posted: Tue Oct 15, 2013 4:00 pm
by driessen
Patrick,

Can you put your source here?

Thanks.

Re: problem with report to excel

Posted: Tue Oct 15, 2013 4:23 pm
by Patrick Chalvet

Code: Select all

FUNCTION nbalcli(gl,complet)
local ofont1,ofont2

default complet to .f.
default gl to .f.

if faire
DEFINE FONT oFont1 NAME "andale mono" SIZE 0,-9
DEFINE FONT oFont2 NAME "andale mono" SIZE 0,-9 BOLD

mess := if(gl,"LE GRAND LIVRE","LA BALANCE")

opar := oserver:query("select * from param")
moisref := str(pannee,4)+"-"+strzero(pmois,2)
arrete := ctod("01/"+strzero(pmois,2)+"/"+str(pannee,4))

if complet
 laq := "select facture.*,clients.nom as nnom from facture inner join clients on clients.tiers = facture.tiers where facture.tiers between "+c2s(cli1)+" and "+c2s(cli2)+;
       " and facture.interne = "+c2s(.F.)+ " and left(datef,7) <= "+c2s(moisref)
 lorder := " order by facture.tiers,facture.datef"
else
 laq := "select facture.*,clients.nom as nnom from facture inner join clients on clients.tiers = facture.tiers "
 laq += "where facture.interne = "+c2s(.F.)+ " and left(datef,7) <= "+c2s(moisref)+" and (reglement+acompte) <> montant"

 lorder := " order by nnom,facture.datef"
endif

//tribal := alert("Balance génerale",{"Générale","Par commerciaux"})
//lorder := if( tribal = 2," order by commerciaux,tiers,datef"," order by nom,tiers,datef")

bal := oserver:query(laq+lorder)
memowrit("laq.sql",bal:cquery)

lbtiers:= bal:tiers

REPORT oReport TITLE  opar:societe,mess+" CLIENTS","Au "+right(dtoc(arrete),7) LEFT ;
               FOOTER ("Page: ")+ltrim(str(oReport:nPage,3)) ;
               FONT oFont1, oFont2  PREVIEW
if gl
COLUMN TITLE "Renseignement" DATA dtoc(bal:datef)+" -- "+bal:factno SIZE 50 FONT 1 GRID
COLUMN TITLE "Debit"         DATA bal:montant FONT 2 PICTURE "@Z 999 999 999"   TOTAL RIGHT GRID
COLUMN TITLE "Crédit"        DATA bal:reglement+bal:acompte   FONT 2 PICTURE "@Z 999 999 999"  TOTAL RIGHT GRID
COLUMN TITLE "Solde"         DATA bal:montant-(bal:reglement+bal:acompte)   FONT 2 PICTURE "@Z 999 999 999"   TOTAL RIGHT GRID
else
COLUMN TITLE "Libellé"   DATA pad(" ",45) SIZE 45 FONT 1 GRID
COLUMN TITLE "Debit"     DATA bal:montant FONT 2 PICTURE "@Z 999 999 999"   TOTAL RIGHT GRID
COLUMN TITLE "Crédit"    DATA bal:reglement+bal:acompte   FONT 2 PICTURE "@Z 999 999 999"  TOTAL RIGHT GRID
COLUMN TITLE "Solde"     DATA bal:montant-(bal:reglement+bal:acompte)   FONT 2 PICTURE "@Z 999 999 999"   TOTAL RIGHT GRID
endif

GROUP ON bal:nnom  FOOTER padl(lbtiers,6,'0')+" "+oreport:agroups[1]:cvalue  FONT 2
END REPORT

oReport:oTitle:aFont[1] := {|| 2 }
setrsql(bal,oreport)
oreport:lsummary := if(gl,.f.,.t.)
oreport:ljoin := .t.
oreport:lboxontotal := .t.
oreport:odevice:lPrvModal := .t.
oreport:odevice:cdir := chepers

ACTIVATE REPORT oReport ON STARTGROUP  (lbtiers:= bal:tiers)
oFont1:End()
oFont2:End()

endif
return nil

function setrsql(osql,oreport)
oreport:bSkip  := {|n| osql:skip(n)}
oreport:bwhile := {|| ! osql:Eof() }  
oreport:bInit  := {|| osql:GOTOP() }
return nil

 

Re: problem with report to excel

Posted: Tue Oct 15, 2013 6:01 pm
by nageswaragunupudi
Which language is your Excel is installed for?

Re: problem with report to excel

Posted: Tue Oct 15, 2013 6:33 pm
by Patrick Chalvet
Excel is installed in French
Thanks

Re: problem with report to excel

Posted: Tue Oct 15, 2013 6:53 pm
by nageswaragunupudi
Will you please make this correction in report.prg?

Please locate the string '=SUBTOTAL(9,'
and substitute it with
'=SOUS.TOTAL(9;'

We would be glad if you can let us know if this modification works for French installation of Excel.

Re: problem with report to excel

Posted: Tue Oct 15, 2013 7:32 pm
by Patrick Chalvet
Thank you for your answer.
with this change
Please locate the string '=SUBTOTAL(9,'
and substitute it with
'=SOUS.TOTAL(9;'
no error,
but when the report is summarize
oreport:lsummary := .T. //if(gl,.f.,.t.)
Excel sheet does not reflect the fivewin report. it contains details
I prepare samples to show this behavior
thank you again
Patrick

Re: problem with report to excel

Posted: Tue Oct 15, 2013 7:34 pm
by nageswaragunupudi
At present, method toExcel() does not support summary reports.
Only full reports.
Please let us know if the group and report totals are exported to excel correctly without errors with this change ( for full reports ).

Re: problem with report to excel

Posted: Wed Oct 16, 2013 4:07 am
by Patrick Chalvet
With this change ( for full reports ),
the group and report totals are exported to excel correctly without errors

Do you think that this Method toExcel() will support summary reports ?

Re: problem with report to excel

Posted: Wed Oct 16, 2013 4:32 am
by nageswaragunupudi
We shall provide that in future releases.