AXL numeric values

Print Thread

AXL numeric values #37459 09 Jul 24 08:55 AM
Joined: Jun 2001 Posts: 3,406 Porto-Portugal/Rio de Janeiro-... J Jorge Tavares - UmZero OP Member
OP Jorge Tavares - UmZero Member J Joined: Jun 2001 Posts: 3,406 Porto-Portugal/Rio de Janeiro-...	Hi Jack, I'm using Fn'LibXL'Write(row, col, value) to populate spreadsheets but numeric values are marked with the warning "number as text" and do not update the SUM() formulas in the totals row. Checking the libxl.bsi, I found that I can send a fourth parameter Fn'LibXL'Write(row, col, value, hformat) Should this hformat parameter solves the problem informing Excel about numeric format? Is there a list for the supported values of hformat? Thanks in advance Jorge Tavares UmZero - SoftwareHouse Brasil/Portugal

Re: AXL numeric values [Re: Jorge Tavares - UmZero] #37460 09 Jul 24 09:00 AM
Joined: Jun 2001 Posts: 3,406 Porto-Portugal/Rio de Janeiro-... J Jorge Tavares - UmZero OP Member
OP Jorge Tavares - UmZero Member J Joined: Jun 2001 Posts: 3,406 Porto-Portugal/Rio de Janeiro-...	Wait, I think I found it in libxl.def , obviously ! numeric format identifiers (see http://libxl.com/format.html ) define NUMFMT_GENERAL = 0 define NUMFMT_NUMBER = 1 define NUMFMT_NUMBER_D2 = 2 define NUMFMT_NUMBER_SEP = 3 define NUMFMT_NUMBER_SEP_D2 = 4 define NUMFMT_CURRENCY_NEGBRA = 5 define NUMFMT_CURRENCY_NEGBRARED = 6 define NUMFMT_CURRENCY_D2_NEGBRA = 7 define NUMFMT_CURRENCY_D2_NEGBRARED = 8 define NUMFMT_PERCENT = 9 define NUMFMT_PERCENT_D2 = 10 define NUMFMT_SCIENTIFIC_D2 = 11 define NUMFMT_FRACTION_ONEDIG = 12 define NUMFMT_FRACTION_TWODIG = 13 define NUMFMT_DATE = 14 define NUMFMT_CUSTOM_D_MON_YY = 15 define NUMFMT_CUSTOM_D_MON = 16 define NUMFMT_CUSTOM_MON_YY = 17 define NUMFMT_CUSTOM_HMM_AM = 18 define NUMFMT_CUSTOM_HMMSS_AM = 19 define NUMFMT_CUSTOM_HMM = 20 define NUMFMT_CUSTOM_HMMSS = 21 define NUMFMT_CUSTOM_MDYYYY_HMM = 22 define NUMFMT_NUMBER_SEP_NEGBRA = 37 define NUMFMT_NUMBER_SEP_NEGBRARED = 38 define NUMFMT_NUMBER_D2_SEP_NEGBRA = 39 define NUMFMT_NUMBER_D2_SEP_NEGBRARED= 40 define NUMFMT_ACCOUNT = 41 define NUMFMT_ACCOUNTCUR = 42 define NUMFMT_ACCOUNT_D2 = 43 define NUMFMT_ACCOUNT_D2_CUR = 44 ! [101] was 43 define NUMFMT_CUSTOM_MMSS = 45 ! [101] was 44 define NUMFMT_CUSTOM_H0MMSS = 46 ! [101] 45 define NUMFMT_CUSTOM_MMSS0 = 47 ! [101] 46 define NUMFMT_CUSTOM_000P0E_PLUS0 = 48 ! [101] 47 define NUMFMT_TEXT = 49 ! [101] 48 Jorge Tavares UmZero - SoftwareHouse Brasil/Portugal

Re: AXL numeric values [Re: Jorge Tavares - UmZero] #37461 09 Jul 24 02:55 PM

Joined: Jun 2001

Posts: 11,794

Woodland Hills, CA

Jack McGregor Offline

Member

Jack McGregor

Member

Joined: Jun 2001

Posts: 11,794

Woodland Hills, CA

Those NUMFMT_ identifiers are not interchangeable with the format handles (hformat). In fact, they are just one of the many attributes that go into generating a format handle, although you could create a generic numeric format handle using just the NUMFMT_ identifier with ....

Code

hformat = Fn'LibXL'AddFormat'SetAttributes(numfmtid=NUMFMT_NUMBER)
call Fn'LibXL'Write(row, col, value, hformat)

But if you're just using the most generic numeric format, you can skip the explicit hformat parameter and instead just supply the value parameter as an f or b type variable, as noted in the comments at the top of the Fn'LibXL'Write() function ....

Code

!	value (any type) [in] value to write
!			f or b indicates num, i is bool, "" is blank, "=..." is formula, else string

This relies on a somewhat exotic ASB feature, i.e. the ability of the called function to see what data types were passed to it -- via .argtyp() -- even though the caller's data type gets automatically converted to the type defined in the function parameter list. Fn'LibXL'Write() first adds the current hsheet and then calls FnLibXL'Sheet'Write() which then, based on the value type passed, uses one of several different low-level LibXL calls to create the proper kind of cell -- xlSheetWriteStr, xlSheetWriteNum, etc.. You still may want to pass an explicit hformat handle to have more control over the formatting, but this should take care of the issue you're encountering with string vs numeric values.

Code

Function Fn'LibXL'SheetWrite(hsheet = 0 as SheetHandle:inputonly, &
                row as b4:inputonly, col as b4:inputonly, value as s0:inputonly, &
                hformat = 0 as FormatHandle:inputonly) as i4

    map1 f8val,f,8								
    map1 ivalue,i,4

    !depending on type of value parameter, use correct function

    if (.argtyp(4) and ARGTYP_MASK) = ARGTYP_S then      ! string value
        if value = "" then
            FuncSig$ = "xlSheetWriteBlank"+Fn'DynLib'AW$(dlCtl)+"(IiiI)B"
            xcall DYNLIB, dlCtl, FuncSig$, libXL.hSheet, fn'xlrow(row), fn'xlcol(col), &		! [101]
                hformat, Fn'LibXL'SheetWrite
        elseif value[1,1] = "=" and instr(2,value,"=") = 0 then ! [120] (avoid confusing "====" with formula
            FuncSig$ = "xlSheetWriteFormula"+Fn'DynLib'AW$(dlCtl)+"(Iii"+Fn'DynLib'StrSig$(dlCtl,STR_CONST)+"I)B"
            xcall DYNLIB, dlCtl, FuncSig$, libXL.hSheet, fn'xlrow(row), fn'xlcol(col), value[2,-1], hformat, Fn'LibXL'SheetWrite  ! [101]
        else
            FuncSig$ = "xlSheetWriteStr"+Fn'DynLib'AW$(dlCtl)+"(Iii"+Fn'DynLib'StrSig$(dlCtl,STR_CONST)+"I)B"
            xcall DYNLIB, dlCtl, FuncSig$, libXL.hSheet, fn'xlrow(row), fn'xlcol(col), &		! [101]
                value, hformat, Fn'LibXL'SheetWrite
        endif

    elseif (.argtyp(4) and ARGTYP_MASK) = ARGTYP_I then   ! signed integer value
        ivalue = value
        FuncSig$ = "xlSheetWriteBool"+Fn'DynLib'AW$(dlCtl)+"(IiiiI)B"
        xcall DYNLIB, dlCtl, FuncSig$, libXL.hSheet, fn'xlrow(row), fn'xlcol(col), &		! [101]
            ivalue, hformat, Fn'LibXL'SheetWrite
    else                                                  ! unsigned integer or floating value
        f8val = val(value)
        FuncSig$ = "xlSheetWriteNum"+Fn'DynLib'AW$(dlCtl)+"(IiidI)B"
        xcall DYNLIB, dlCtl, FuncSig$, libXL.hSheet, fn'xlrow(row), fn'xlcol(col), &		! [101]
            f8val, hformat, Fn'LibXL'SheetWrite
    endif
    ...

Re: AXL numeric values [Re: Jorge Tavares - UmZero] #37464 10 Jul 24 09:32 AM
Joined: Jun 2001 Posts: 3,406 Porto-Portugal/Rio de Janeiro-... J Jorge Tavares - UmZero OP Member
OP Jorge Tavares - UmZero Member J Joined: Jun 2001 Posts: 3,406 Porto-Portugal/Rio de Janeiro-...	Hi Jack, Thank you so much for the details, which confirms my suspicious, but was betrayd by laziness to try. In this specific case, all values sent to Fn'LibXL'Write() are in a string and I thought: "would it handle as a number if the numbers go in numeric variables?" What it does, as you described, but to apply the necessary logic properly it would take some time, and it was not that urgent so, I decided to check documentation, poorly done. Anyway, I'm happy for the additional knowledge in your reply. Jorge Tavares UmZero - SoftwareHouse Brasil/Portugal

Moderated by Jack McGregor, Ty Griffin