Perl - Win32::OLE - Excel Formulas

[Prasanna CRN]

I am a Perl Programmer & i am using WIN32::OLE for Excel operations.

I wanted to use conditional formulas like 'if' in excel via Perl.

Thanks in Advance.

[miller]

You don't need that incremental translation using chr and ord. Instead just rely on your original variable.

my $column = 'A';
 
for my $i (1..100) {
    print "$column ";
    $column++;
}
 

Outputs

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH AI AJ
AK AL AM AN AO AP AQ AR AS AT AU AV AW AX AY AZ BA BB BC BD BE BF BG BH BI BJ
BK BL BM BN BO BP BQ BR BS BT BU BV BW BX BY BZ CA CB CC CD CE CF CG CH CI CJ CK
CL CM CN CO CP CQ CR CS CT CU CV

[miller]

Note, an even cleaner way of doing it is to use the .. operator.

for my $column ('A'..'CV') {
    print "$column ";
}
 

[miller]

It's also good to know a functional way to translate a column number into excel notation. Here's one way to do that

sub excel_col {
    my $val = shift;
 
    my $str = '';
 
    do {
        $val-- if $str ne '';
        my $r = $val % 26;
        $val = ($val - $r) / 26;
        $str = chr(ord('A') + $r) . $str;
    } while ($val > 0);
 
    return $str;
}
 
for my $i (0..1000) {
    print excel_col($i) . ' ';
}
 

[Prasanna CRN]

Thanks Miller. I had used alternative way as per my use case.(with some limitations)
But yours will be applied in broader manner.