How to get the column name in a spreadsheet app?

[Martin M.]

Hi everybody,

I am currently developing a small AppleScript, which will help our
staff to automate tasks in our spreadsheet app (RagTime 6). But now I
am at a point, where I need a function in my AppleScript to determine
the name of a column name in a table. Here is an example, just to give
you an idea:

Position: 5 -> Column name: "E"
Position: 28 -> Column name: "AB"

Now, I found a Perl module, which does exactly this: Converting number
positions into column names, but...I am not (yet) familiar with Perl.
So I would like you to tell me, what this code is doing, enabling me to
use it in my AppleScript:

[Code from: http://search.cpan.org/perldoc?Spreadsheet::ConvertAA ]
_______________________________

sub ToAA($)
{
my $c = shift ;
confess "Invalid base10 '$c'" if($c =~ /[^0-9]/) ;

return('@') if $c == 0 ;

my $cell = "";

while($c)
{
use integer;
substr ($cell, 0, 0) = chr (--$c % 26 + ord "A");
$c /= 26;
}

return($cell) ;
}
_______________________________

Thanks so much in advance!


Best regards,

Martin


http://www.schoolscout24.de/wordpress-files/welistento.html

 

[Bart Van der Donck]

I am currently developing a small AppleScript, which will help our
staff to automate tasks in our spreadsheet app (RagTime 6). But now I
am at a point, where I need a function in my AppleScript to determine
the name of a column name in a table. Here is an example, just to give
you an idea:

Position: 5 -> Column name: "E"
Position: 28 -> Column name: "AB"

Now, I found a Perl module, which does exactly this: Converting number
positions into column names, but...I am not (yet) familiar with Perl.
[...]

No need for a module here:

my $nr = 5; # fill in position here
my $t = A;
++$t for (1..$nr-1);
print "Position: $nr -> Column name: $t\n";

 

[Martin M.]

No need for a module here:

my $nr = 5; # fill in position here
my $t = A;
++$t for (1..$nr-1);
print "Position: $nr -> Column name: $t\n";

Hi Bart,

thank you very much for posting this piece of code. I tried it on my
Mac and it works like a charm. The only thing is: I don't know how it
works

Of course, first your are creating a variable containing the position
value, then you are creating a second variable containing the A value.
But is this a string? Finally there is this kind of loop in line 3,
which loops $nr minus 1 times, thereby adding <something> to the A
value. Well, what is going on in line 3? ;-)

I am now using your code for getting the column names via the shell
('do shell script'-command in AppleScript), because it works just
beautiful and blazing fast, but I really want to know, how the magic is
done.

Thanks so much,

Martin

 

[Bart Van der Donck]

thank you very much for posting this piece of code. I tried it on my
Mac and it works like a charm. The only thing is: I don't know how it
works

Of course, first your are creating a variable containing the position
value, then you are creating a second variable containing the A value.
But is this a string? Finally there is this kind of loop in line 3,
which loops $nr minus 1 times, thereby adding <something> to the A
value. Well, what is going on in line 3? ;-)

Frankly I don't see much magic here Let's rewrite it a bit so it's
easy to follow:

#!/perl
use strict;
use warnings;

# Fill in position here that you wish to convert.
my $nr = 29;

# Just a start value, like first col in xls file
# (actually quotes around it is better practice).
# Yes it's an "ordinary" var.
my $t = 'A';

# Find the next column with ++ by just adding up
# the current value (A becomes B, AZ becomes BA,
# EWKLR becomes EWKLS, etc), it's like in Excel.
# You could do (0..$nr-2) or (2..$nr) as well.
for (1..$nr-1)
{
$t++;
}

# Print result to screen.
print $t;

 

[Martin M.]

Hi Bart,

thanks for explaining the code to me in detail. Now I really
understand, what is going on there and was finally able to write a
small function in AppleScript, which does the same:

http://www.schoolscout24.de/tmp/get_column_name.html

Well, yes, the code is 'longish', but it works

Once again: Thank you, you really helped me out!

Martin

 

[Bart Van der Donck]

thanks for explaining the code to me in detail. Now I really
understand, what is going on there and was finally able to write a
small function in AppleScript, which does the same:

http://www.schoolscout24.de/tmp/get_column_name.html

Now THAT was magic