> Le 2 déc. 2018 à 00:00, Kirk Brooks via 4D_Tech <[email protected]> a
> écrit :
>
> I had need to calculate column references for an Excel spreadsheet for
> columns greater than 26. You know, so you can come up with the reference
> like: A1:AG1.
Hi Kirk,
I had the same problem in the past… I used a similar solution, but recursive:
++++++
//Utl_spreadsheetColumn (column_l) -> text
//convert column number to spreadsheet reference
C_TEXT($0;$out_t)
C_LONGINT($1;$unit_l;$base_l;$column_l)
//_
$out_t:=""
Case of
: (Count parameters<1)
//nope
: ($1<1)
//nope
Else
$column_l:=$1
$unit_l:=(($column_l-1)%26)+1
$base_l:=($column_l-1)\26
If ($base_l>0)
$out_t:=Utl_spreadsheetColumn ($base_l)+$out_t
End if
$out_t:=$out_t+Char($unit_l+64)
End case
$0:=$out_t
++++++
unit test:
++++++
APPEND TO ARRAY($col_al;1)
APPEND TO ARRAY($ut_at;"a")
APPEND TO ARRAY($col_al;26)
APPEND TO ARRAY($ut_at;"z")
APPEND TO ARRAY($col_al;27)
APPEND TO ARRAY($ut_at;"aa")
APPEND TO ARRAY($col_al;702)
APPEND TO ARRAY($ut_at;"zz")
APPEND TO ARRAY($col_al;703)
APPEND TO ARRAY($ut_at;"aaa")
APPEND TO ARRAY($ut_at;"aaa")
APPEND TO ARRAY($col_al;1024)
For ($i_l;1;Size of array($col_al))
$ref_t:=Utl_spreadsheetColumn ($col_al{$i_l})
ASSERT($ref_t=$ut_at{$i_l})
End for
++++++
--
Arnaud de Montard
**********************************************************************
4D Internet Users Group (4D iNUG)
Archive: http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:[email protected]
**********************************************************************
