How to get the column name in a spreadsheet app?

Discussion in 'Perl Misc' started by Martin M., Aug 8, 2006.

  1. Martin M.

    Martin M. Guest

    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%3A%3AConvertAA ]
    _______________________________

    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
     
    Martin M., Aug 8, 2006
    #1
    1. Advertising

  2. Martin M. wrote:

    > 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";

    --
    Bart
     
    Bart Van der Donck, Aug 8, 2006
    #2
    1. Advertising

  3. Martin M.

    Martin M. Guest

    > 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
     
    Martin M., Aug 8, 2006
    #3
  4. Martin M. wrote:

    > > 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";
    > >

    > 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;

    --
    Bart
     
    Bart Van der Donck, Aug 8, 2006
    #4
  5. Martin M.

    Martin M. Guest

    Here is the corresponding AppleScript code:

    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
     
    Martin M., Aug 8, 2006
    #5
  6. Re: Here is the corresponding AppleScript code:

    Martin M. wrote:

    > 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 :)

    --
    Bart
     
    Bart Van der Donck, Aug 8, 2006
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Leszek

    Get column name, first record name

    Leszek, Jan 19, 2005, in forum: ASP .Net
    Replies:
    2
    Views:
    452
    Leszek
    Jan 19, 2005
  2. ding feng
    Replies:
    2
    Views:
    2,906
    ding feng
    Jun 25, 2003
  3. colo
    Replies:
    3
    Views:
    589
  4. Tim Chase
    Replies:
    7
    Views:
    317
    Dennis Lee Bieber
    Jul 20, 2012
  5. Peter Otten
    Replies:
    0
    Views:
    223
    Peter Otten
    Jul 19, 2012
Loading...

Share This Page