FiveTech Software tech support forums

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

xBrowse oBw:ToExcel()

https://fivetechsoft.com/forums/viewtopic.php?f=3&t=31716

Page 1 of 1

xBrowse oBw:ToExcel()

Posted: Tue Dec 15, 2015 12:58 pm
by avista
To escape from error if ListSeparator is changed on the PC i use this function GetListSeparator() in xBrowse class

cFormula:= "SUBTOTAL(" + ;
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 ) ) ) + ;
// "," + ;
GetListSeparator() + ;
oSheet:Range( oSheet:Cells( 2, nCol ), ;
oSheet:Cells( nRow - 1, nCol ) ):Address( .f., .f. ) + ;
")"

Code: Select all

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

/*
   New Function GetListSeparator()
   used to return "List Separator" from "Regional and Language Options"
   This character is used in oBw:ToExcel() METHOD
   by default in XBROWSE.PRG is ","
   but if it is changed in "Regional and Language Options" for example with "|"
   program crash
*/

Function GetListSeparator() // Returning "List Separator" from "Regional and Language Options"

// Managing Register services from FiveWin

#define  HKEY_CLASSES_ROOT       2147483648
#define  HKEY_CURRENT_USER       2147483649
#define  HKEY_LOCAL_MACHINE      2147483650
#define  HKEY_USERS              2147483651
#define  HKEY_PERFORMANCE_DATA   2147483652
#define  HKEY_CURRENT_CONFIG     2147483653
#define  HKEY_DYN_DATA           2147483654


   local cListSeparator := ","  // Default value
   local hKey, cName, uValue, n := 0

   if RegOpenKey( HKEY_CURRENT_USER,;
         "Control Panel\International", @hKey ) == 0

      while RegEnumValue( hKey, n++, @cName, @uValue ) == 0
            if cName = "sList"
               cListSeparator := left( uValue, 1 )
            endif
      end

   endif

return cListSeparator

//----------------------------------------------------------------------------//
 
Best regards,

Re: xBrowse oBw:ToExcel()

Posted: Wed Dec 16, 2015 10:34 pm
by nageswaragunupudi
The formula that is built above is translated by the function ExcelTranslate( cFormula ) before assigning to the cell in the next line.

The function ExcelTranslate replaces "," with the proper ListSeparator. This function is in olefuncs.prg.

Here ListSepator is obtained from Excel Object than from the Registry.
May I know if the version of FWH you are using contains this ExcelTranslate() function?

Re: xBrowse oBw:ToExcel()

Posted: Thu Dec 17, 2015 8:15 am
by avista
Mr. Rao,

I use FWH 14.12
xHarbour (Build 20141124)
bcc582

yes, FWH i am using contains this ExcelTranslate() function.

BUT program crash at line 7545
oSheet:Cells( nRow, nCol ):Formula := '=' + ExcelTranslate( cFormula )

ListSeparator to my PC is "|"

Error description: Error Excel.Application:ACTIVESHEET:CELLS/0 S_OK: _FORMULA
Args:
[ 1] = C =SUBTOTAL(9,I2:I87)

Stack Calls
===========
Called from: => TOLEAUTO:_FORMULA( 0 )
Called from: .\xbrowse.PRG => TXBROWSE:TOEXCEL( 7545 )

Best regards,

Re: xBrowse oBw:ToExcel()

Posted: Thu Dec 17, 2015 8:22 am
by nageswaragunupudi
Can you help ?
Can you check the result of oExcel:International[ 5 ] ?

Re: xBrowse oBw:ToExcel()

Posted: Thu Dec 17, 2015 8:33 am
by avista
Yes

I put msginfo just before line 7545 (where program crash)

the value of oExcel:International[ 5 ] is "|"

Regards,

Re: xBrowse oBw:ToExcel()

Posted: Thu Dec 17, 2015 8:51 am
by nageswaragunupudi
That is correct.

Then please also put another msginfo

msginfo( ExcelTranslate( cFormula ) ) // without applying your correction

Re: xBrowse oBw:ToExcel()

Posted: Thu Dec 17, 2015 9:19 am
by avista
Result,

SUBTOTAL(9,I2:I87)

Here is source of the ExcelTranslate() function

Code: Select all

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

function ExcelTranslate( cFunc )

   local cRet, nAt, cName

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

   if nExcelLangNo == nil
      ExcelLangID()
   endif
   cFunc       := Upper( cFunc )
   cRet        := cFunc
   if nExcelLangNo > 1 .and. nExcelLangNo <= 6
      // 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 ] )
         cRet     := StrTran( cRet, ',', ATAIL( aTranslates )[ nExcelLangNo ] )
      endif
   elseif nExcelLangNo != 1
      if cRet == "TRUE";  cRet := "1=1"; endif
      if cRet == "FALSE"; cRet := "1=0"; endif
   endif

return cRet

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

 
Regards,

Re: xBrowse oBw:ToExcel()

Posted: Thu Dec 17, 2015 9:24 am
by nageswaragunupudi
We need to fix exceltranslate function. I'll get back

Re: xBrowse oBw:ToExcel()

Posted: Thu Dec 17, 2015 1:45 pm
by nageswaragunupudi
Please use this revised function

Code: Select all

function ExcelTranslate( cFunc )

   local cRet, nAt, cName

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

   if nExcelLangNo == nil
      ExcelLangID()
   endif
   cFunc       := Upper( cFunc )
   cRet        := cFunc
   if nExcelLangNo > 1 .and. nExcelLangNo <= 6
      // 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
 

Re: xBrowse oBw:ToExcel()

Posted: Thu Dec 17, 2015 2:12 pm
by avista
I replaced the function
and included file olefuncs.prg in program

This error occured

Error description: Error BASE/1003 Variable does not exist: CLISTSEPARATOR

Stack Calls
===========
Called from: .\olefuncs.PRG => EXCELTRANSLATE( 184 )
Called from: .\xbrowse.PRG => SETEXCELLANGUAGE( 8791 )
Called from: .\xbrowse.PRG => TXBROWSE:TOEXCEL( 7339 )

Regards,

Re: xBrowse oBw:ToExcel()

Posted: Thu Dec 24, 2015 7:56 am
by avista
Up,

Re: xBrowse oBw:ToExcel()

Posted: Thu Dec 24, 2015 8:59 am
by nageswaragunupudi
We are using cListSeparator in versions subsequent to your version. We are deriving cListSeparator from oExcel:International[ 5 ].

We made suitable modification as proposed above in the next version and is working.
For older versions you may need to use oExcel:International[ 5 ] or your Regstry function to replace comma
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited