Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

TCOM: Referencing cols with numbers instead of letters

92 views
Skip to first unread message

sd

unread,
May 20, 2012, 1:14:14 AM5/20/12
to
Greetings,

I'm using TCOM to build my Excel spreadsheets and have many cols/rows
with data. I want to reference my columns in numbers instead of
letters because I want to reference them and do data manipulations and
such. Anyone find a concise how to do this?

This will work:
set range [$worksheet Range "A${row1}:B${row2}"]

But this will not (assuming col hold an integer value):
set range [$worksheet Range "${col1}${row1}:${col2}${row2}"]

Thanks

/sd

sd

unread,
May 20, 2012, 1:40:43 AM5/20/12
to
Just found the answer at http://wiki.tcl.tk/1821

Something like this:
set range [$cells Range [$cells Item $row1 $col1] [$cells Item $row2
$col2]]

praful

unread,
May 21, 2012, 8:38:04 AM5/21/12
to
On May 20, 10:40 am, sd <sham...@hotmail.com> wrote:
> Just found the answer athttp://wiki.tcl.tk/1821
>
> Something like this:
> set range [$cells Range [$cells Item $row1 $col1] [$cells Item $row2
> $col2]]

you might want to use TcomExcel (http://www.posoft.de/html/
extTcomExcel.html)
it has many basic excel function, which might help with this and
others.

sd

unread,
May 22, 2012, 9:23:46 AM5/22/12
to
That link is broken.

Now I'm trying to find out how to selecte multiple ranges all at once
while referencing cols in numbers. Similar to:
set valuesRange [$worksheet Range "A10:A20,C10:E20"]

I tried:
set range [$cells Range [$cells Item 10 1] [$cells Item 20 1]],[$cells
Range [$cells Item 10 3] [$cells Item 20 5]]

But it didn't work

APN

unread,
May 22, 2012, 10:21:55 AM5/22/12
to
On 5/22/2012 6:53 PM, sd wrote:

> That link is broken.
>
The link is http://www.posoft.de/html/extTcomExcel.html

sd

unread,
May 22, 2012, 10:52:46 AM5/22/12
to
Thanks.
If anyone knows how to reference non-adjacent cell, please do share.

Andreas Leitgeb

unread,
May 22, 2012, 11:25:51 AM5/22/12
to
sd <sha...@hotmail.com> wrote:
> Now I'm trying to find out how to selecte multiple ranges all at once
> while referencing cols in numbers. Similar to:
> set valuesRange [$worksheet Range "A10:A20,C10:E20"]
> I tried:
> set range [$cells Range [$cells Item 10 1] [$cells Item 20 1]],[$cells
> Range [$cells Item 10 3] [$cells Item 20 5]]
> But it didn't work

I don't know much about tcom and excel, but if your problem is
converting a numeric column specification to a letter string,
then I guess, it should not be all that hard to convert it in
your script before calling [$worksheet Range ...]

proc colname {num} {
# for 1 to 26 return A to Z
# for 27 to 702 return AA to ZZ
# for 703 to ...
}

sd

unread,
May 23, 2012, 11:57:27 AM5/23/12
to
On May 22, 11:25 am, Andreas Leitgeb <a...@gamma.logic.tuwien.ac.at>
wrote:
> sd <sham...@hotmail.com> wrote:
> > Now I'm trying to find out how to selecte multiple ranges all at once
> > while referencing cols innumbers.  Similar to:
> > set valuesRange [$worksheet Range "A10:A20,C10:E20"]
> > I tried:
> > set range [$cells Range [$cells Item 10 1] [$cells Item 20 1]],[$cells
> > Range [$cells Item 10 3] [$cells Item 20 5]]
> > But it didn't work
>
> I don't know much about tcom andexcel, but if your problem is
> converting a numericcolumnspecification to a letter string,
> then I guess, it should not be all that hard toconvertit in
> your script before calling [$worksheet Range ...]
>
> proc colname {num} {
>    # for 1 to 26 return A to Z
>    # for 27 to 702 return AA to ZZ
>    # for 703 to ...
>
>
>
> }- Hide quoted text -
>
> - Show quoted text -


I was hoping there would be a straight forward way of doing that, but
writing a small proc is always an option:

proc colN {n} {
if {$n<=26} {puts [format %c [expr {$n+64}]]
} else {
set a [expr {$n/26}] ;# rounded down
set b [expr {$n%$a}] ;# remainder of division
set c [expr {$n - $a*26}]
if {$c==0} {set c 26}
puts [format %c [expr {$a+64}]][format %c [expr {$c+64}]]
}
}

Andreas Leitgeb

unread,
May 24, 2012, 5:07:04 AM5/24/12
to
if {$n <= 0} { error "Invalid column"
} elseif {$n<=26} {
puts [format %c [expr {$n+64}]]
} elseif {$n <= 702} {
incr n -27
puts [format %c%c [expr {$n/26+65}] [expr {$n%26+65}]]
} else { error "Unsupported column" }
}

Andreas Leitgeb

unread,
May 24, 2012, 6:26:54 AM5/24/12
to
Andreas Leitgeb <a...@gamma.logic.tuwien.ac.at> wrote:
> sd <sha...@hotmail.com> wrote:
>> On May 22, 11:25 am, Andreas Leitgeb <a...@gamma.logic.tuwien.ac.at>
>> wrote:
>>> sd <sham...@hotmail.com> wrote:
>>> > Now I'm trying to find out how to selecte multiple ranges all at once
>>> > while referencing cols innumbers.  Similar to:
>>> > set valuesRange [$worksheet Range "A10:A20,C10:E20"]
>>> > I tried:
>>> > set range [$cells Range [$cells Item 10 1] [$cells Item 20 1]],[$cells
>>> > Range [$cells Item 10 3] [$cells Item 20 5]]
>>> > But it didn't work
>>>
>>> I don't know much about tcom andexcel, but if your problem is
>>> converting a numericcolumnspecification to a letter string,
>>> then I guess, it should not be all that hard toconvertit in
>>> your script before calling [$worksheet Range ...]
>>>
>>> proc colname {num} {
>>>    # for 1 to 26 return A to Z
>>>    # for 27 to 702 return AA to ZZ
>>>    # for 703 to ...
>>>
>>>
>>>
>>> }- Hide quoted text -
>>>
>>> - Show quoted text -
>>
>>
>> I was hoping there would be a straight forward way of doing that, but
>> writing a small proc is always an option:
>> proc colN {n} { [...] }
> proc colN {n} { [...] }

and for unlimited columns:

proc colN {n} {
set res ""
while {[incr n -1] >= 0} {
set res [format %c%s [expr {$n%26+65}] $res]
set n [ expr {$n/26}]
}
return $res
}

Test:
foreach arg {1 26 27 702 703 18278 18279} {
puts [format "%5d %s" $arg [colN $arg]]
}

0 new messages