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]

I personally would use Spreadsheet::WriteExcel as Win32::OLE does not provide great documentation.

At the very least, maybe you could create a test excel file with functions and examine it using Win32::OLE to determine how they are included.

- Miller

[Prasanna CRN]

Hi Miller,
Thanks for your Interest & I found the answer.

"=IF(RC[$prev]>RC[$curr],RC[$prev]-RC[$curr],\"PASS\")"
 

The reason why i am using Win32::OLE is, no need of additional Module Installation.

[miller]

Of course, Just note that anytime you want to use quotes " instead of an interpolated string, you can use the qq operator instead.

qq{=IF(RC[$prev]>RC[$curr],RC[$prev]-RC[$curr],"PASS")}
 

[Prasanna CRN]

Thanks Miller.

But Folks, any idea on Arithmetic Operation with Strings?

Ex : I can do auto increment of string : my $a = 'A';
$a ++ ; #Will give 'B'

Like that, i want to do : $a += 5 ; #I expect 'F', but perl throws error on this. Any idea on my thing?

[RonB]

When you do $a++, perl will increment the ascii value, but when you do $a += 5, you're attempting to add a numerical number which is not the same.

To accomplish what you want, you'd need to do this.

my $letter = 'a';
$letter = chr(ord($letter)+5);

[miller]

As RonB pointed out, the fact that the increment ++ and decrement -- operators work on strings is a special feature. To get the same behavior out of += or any other operator would require overloading them.

The best method is to just use the method that RonB demonstrated.

- Miller

[Prasanna CRN]

Thanks Guys for your Awesome Response!!!

RonB,
I accept that. But even with this option i have a problem:
my $letter = 'A';
my $test;

for ($i = 1; $i <= 100; $i++){

$test = chr(ord($letter));
print "Orig Char : $letter; Exp Char : $test\n";
$letter++;
}

This will fine upto 26 (A to Z), After 26th Iteration, $letter will become 'AA' where the function will fail (Since it will take 1 char as argument).

Any alternative way to address this problem.

[miller]

Yes, if you increment a string it will go from A to Z and then to AA AB, etc. You say that's not what you want, but you haven't described what your ideal behavior actually is?

What are you wanting your string incrementor to do? In theory you're going through 100 values, what do you want them to be?

[Prasanna CRN]

Miller,
At the begining i mentioned that i am working in Excel.
The Excel Cells Starts from 'A' & it goes like AA - AZ, BA - BZ & so on.. This is my Use case!

Hope it will narrow your ideas.

[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.