problem with report to excel

[bart]

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.

I changed the file report.prg '=SUBTOTAAL(9;' but stil have the same error.

[nageswaragunupudi]

SUBTOTAAL or SUBTOTAL ?
What is Excel installation language?

[bart]

Hello Mr. Rao,

Excel is installed in Dutch.

So '=SUBTOTAL(9,' was changed into 'SUBTOTAAL(9;'

Kind regards,
Bart

[nageswaragunupudi]

Excel is installed in Dutch.

So '=SUBTOTAL(9,' was changed into 'SUBTOTAAL(9;'

I see.
So will you be kind enough to let us know what is working correctly with Dutch installation of Excel?
Also please try oBrw:ToExcel() from XBrowse,
Both have the same language issues for SUBTOTAL.
Please help us to support Dutch installation by advising us the correct keyword and usage.
Thanks in advance.

[bart]

Hello Mr. Rao,

The Excel formula in Dutch is "=SUBTOTAAL(9;"

However when changing the report.prg file with the correct formula this has no effect.

Application
===========
Path and name: C:\Users\bartv\OneDrive\BKH\fwbkhmain.exe (64 bits)
Size: 4,384,768 bytes
Compiler version: Harbour 3.2.0dev (r1412151448)
FiveWin Version: FWH 15.01
Windows version: 6.2, Build 9200

Time from start: 0 hours 0 mins 31 secs
Error occurred at: 04/09/2023, 13:24:57
Error description: (DOS Error -2147352567) WINOLE/1006 (0x800A03EC): _FORMULA
Args:
[ 1] = C =SUBTOTAL(9,D4:D12)

Stack Calls
===========
Called from: => TOLEAUTO:_FORMULA( 0 )
Called from: .\source\classes\REPORT.PRG => TREPORT:TOEXCEL( 0 )
Called from: .\source\classes\RPREVIEW.PRG => (b)TPREVIEW( 0 )
Called from: .\source\classes\RPREVIEW.PRG => TPREVIEW:EXPORTTOMSEXCEL( 0 )
Called from: .\source\classes\RPREVIEW.PRG => (b)TPREVIEW_BUILDBUTTONBAR( 0 )
Called from: .\source\classes\BTNBMP.PRG => TBTNBMP:CLICK( 0 )
Called from: .\source\classes\BTNBMP.PRG => TBTNBMP:LBUTTONUP( 0 )
Called from: .\source\classes\CONTROL.PRG => TCONTROL:HANDLEEVENT( 0 )
Called from: .\source\classes\BTNBMP.PRG => TBTNBMP:HANDLEEVENT( 0 )
Called from: .\source\classes\WINDOW.PRG => _FWH( 0 )
Called from: => SYSREFRESH( 0 )
Called from: .\source\function\MSGRUN.PRG => STOPUNTIL( 0 )
Called from: .\source\classes\RPREVIEW.PRG => TPREVIEW:ACTIVATE( 0 )
Called from: .\source\classes\RPREVIEW.PRG => RPREVIEW( 0 )
Called from: .\source\classes\REPORT.PRG => (b)TREPORT_NEW( 0 )
Called from: .\source\classes\REPORT.PRG => TREPORT:ACTIVATE( 0 )
Called from: fwbkhreport.prg => PRINT_VERKOOPDAGBOEK( 245 )

Thanks in advance for your help.

Best regards
Bart

[nageswaragunupudi]

As of now we are supporting Excel language translations for only
1 English, 2 Spanish, 3 French, 4 Portuguese, 5 German, 6 Italian
'en', 'es', 'fr', 'pt', 'de', 'it'

With your help and support, we will try to provide support for Dutch also.

Please help me with this information:
1) Like 'de' for German, 'es' for Spanish, what is it for Dutch?
2) Please provide translations for:
SUBTOTAL( -> SUBTOTAAL(
SUM( --> ???
TRUE --> ???
FALSE --> ???

Please run the small test and let us know the results:

Code: Select all | Expand

oExcel         := ExcelObj()
? nExcelLangID   := oExcel:LanguageSettings:LanguageID( 2 )
? cDecimalSeparator    := oExcel:International[ 3 ]
? cThousandsSeparator  := oExcel:International[ 4 ]
? cListSeparator       := oExcel:International[ 5 ]
 

If you don't mind me asking, are you from Netherlands?

[nageswaragunupudi]

Please try if this change is working for you

Code: Select all | Expand

            cFormula:= "SUBTOTAAL(" + ;
                        LTrim( Str( FW_DeCode( IfNil( oCol:nFooterType, 0 ), AGGR_SUM, 9, AGGR_MAX, 4, AGGR_MIN, 5, ;
                        AGGR_COUNT, 3, AGGR_AVG, 1, AGGR_STDEV, 7, AGGR_STDEVP, 8, 9 ) ) ) + ;
                        ";" + ;
                        oSheet:Range( oSheet:Cells( 2, nCol ), ;
                                      oSheet:Cells( nRow - 1, nCol ) ):Address( .f., .f. ) + ;
                        ")"
            oSheet:Cells( nRow, nCol ):Formula := '=' + cFormula
 

[bart]

Hello Mr. Rao,

1) Like 'de' for German, 'es' for Spanish, what is it for Dutch? -> 'nl' is the iso code for Dutch and Flemish.
2) Please provide translations for:
SUBTOTAL( -> SUBTOTAAL(
SUM( --> ??? - > SOM(
TRUE --> ??? -> WAAR
FALSE --> ??? -> ONWAAR

Language ID = 1043
Decimal separator = ,
Thousands separator = .
List separator = ;

I live in Belgium.

Best regards
Bart

[nageswaragunupudi]

Can you please make these changes in
fwh\source\function\olefuncs.prg
and test with toexcel() methods of Report and XBrowse please?

Please replace these three functions

Code: Select all | Expand

function ExcelLangID()
function ExcelLang()
function ExcelTranslate( cFunc )
 

with these modified functions:

Code: Select all | Expand

function ExcelLangID()

   local oExcel
   local aID   := { ;
   { 1033, 2057, 10249, 4105, 9225, 14345, 6153, 8201, 5129, 13321, 7177, 11273, 12297 }, ;
   { 3082,1034,11274,16394,13322,9226,5130,7178,12298,17418,4106,18442,;
                     58378,2058,19466,6154,15370,10250,20490,21514,14346,8202 }, ;
   { 1036,2060,11276,3084,9228,12300,15372,5132,13324,6156,14348,58380,8204,10252,4108,7180 }, ;
   { 1046, 2070 }, ;
   { 1031,3079,5127,4103,2055 }, ;
   { 1040, 2064 }, ;
   { 1043 } }

   // 1 English, 2 Spanish, 3 French, 4 Portuguese, 5 German, 6 Italian, 7 Dutch

   if nExcelLangID == nil
      oExcel         := ExcelObj()
      nExcelLangID   := If( oExcel == nil, 0, oExcel:LanguageSettings:LanguageID( 2 ) )
      nExcelLangNo   := AScan( aID, { |a| AScan( a, nExcelLangID ) > 0 } )
      TRY
         cDecimalSeparator    := oExcel:International[ 3 ]
         cThousandsSeparator  := oExcel:International[ 4 ]
         cListSeparator       := oExcel:International[ 5 ]
      CATCH
      END
   endif

return nExcelLangID

//----------------------------------------------------------------------------//

function ExcelLang()

   if nExcelLangNo == nil
      ExcelLangID()
   endif

return { 'en', 'es', 'fr', 'pt', 'de', 'it', 'nl' }[ Max( 1, nExcelLangNo ) ]

//----------------------------------------------------------------------------//

function ExcelTranslate( cFunc )

   local cRet, nAt, cName

   // 1 English, 2 Spanish, 3 French, 4 Portuguese, 5 German, 6 Italian, 7 Dutch
   local aTranslates := { ;
   { "TRUE",      "VERDADERO",   "VRAI",        "VERDADEIRO", "WAHR",          "VERO",       "WAAR"   }, ;
   { "FALSE",     "FALSO",       "FAUX",        "FALSO",      "FALSCH",        "FALSO",      "ONWAAR" }, ;
   { "SUM(",      "SUMA(",       "SOMME(",      "SOMA(",      "SUMME(",        "SOMMA(",     "SOM("   }, ;
   { "SUBTOTAL(", "SUBTOTALES(", "SOUS.TOTAL(", "SUBTOTAL(",  "TEILERGEBNIS(", "SUBTOTALE(", "SUBTOTAAL(" }, ;
   { ',',         ';',           ';',           ';',          ';',              ';',         ';' } ;
   }

   if nExcelLangNo == nil
      ExcelLangID()
   endif
   cFunc       := Upper( cFunc )
   cRet        := cFunc
   if nExcelLangNo > 1 .and. nExcelLangNo <= 7
      // Translation required
      cName    := cFunc
      if ( nAt := At( '(', cFunc ) ) > 0
         cName    := Left( cFunc, nAt )
      endif
      if ( nAt := AScan( aTranslates, { |a| a[ 1 ] == cName } ) ) > 0
         cRet     := StrTran( cFunc, cName, aTranslates[ nAt, nExcelLangNo ] )
      endif
   elseif nExcelLangNo != 1
      if cRet == "TRUE";  cRet := "1=1"; endif
      if cRet == "FALSE"; cRet := "1=0"; endif
   endif

   if cListSeparator != ','
      cRet     := StrTran( cRet, ',', cListSeparator )
   endif

return cRet
 

Please provide your feedback.
Please use the original xbrowse and report programs of FWH without modifications.