469,289 Members | 2,330 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,289 developers. It's quick & easy.

Perl - Win32::OLE - Excel Formulas

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.
Mar 31 '11 #1
13 4483
miller
1,089 Expert 1GB
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
Mar 31 '11 #2
Hi Miller,
Thanks for your Interest & I found the answer.

Expand|Select|Wrap|Line Numbers
  1. "=IF(RC[$prev]>RC[$curr],RC[$prev]-RC[$curr],\"PASS\")"
  2.  
The reason why i am using Win32::OLE is, no need of additional Module Installation.
Apr 1 '11 #3
miller
1,089 Expert 1GB
Of course, Just note that anytime you want to use quotes " instead of an interpolated string, you can use the qq operator instead.

Expand|Select|Wrap|Line Numbers
  1. qq{=IF(RC[$prev]>RC[$curr],RC[$prev]-RC[$curr],"PASS")}
  2.  
Apr 1 '11 #4
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?
Apr 2 '11 #5
RonB
589 Expert Mod 512MB
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.
Expand|Select|Wrap|Line Numbers
  1. my $letter = 'a';
  2. $letter = chr(ord($letter)+5);
Apr 2 '11 #6
miller
1,089 Expert 1GB
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
Apr 3 '11 #7
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.
Apr 4 '11 #8
miller
1,089 Expert 1GB
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?
Apr 4 '11 #9
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.
Apr 4 '11 #10
miller
1,089 Expert 1GB
You don't need that incremental translation using chr and ord. Instead just rely on your original variable.

Expand|Select|Wrap|Line Numbers
  1. my $column = 'A';
  2.  
  3. for my $i (1..100) {
  4.     print "$column ";
  5.     $column++;
  6. }
  7.  
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
Apr 4 '11 #11
miller
1,089 Expert 1GB
Note, an even cleaner way of doing it is to use the .. operator.

Expand|Select|Wrap|Line Numbers
  1. for my $column ('A'..'CV') {
  2.     print "$column ";
  3. }
  4.  
Apr 4 '11 #12
miller
1,089 Expert 1GB
It's also good to know a functional way to translate a column number into excel notation. Here's one way to do that

Expand|Select|Wrap|Line Numbers
  1. sub excel_col {
  2.     my $val = shift;
  3.  
  4.     my $str = '';
  5.  
  6.     do {
  7.         $val-- if $str ne '';
  8.         my $r = $val % 26;
  9.         $val = ($val - $r) / 26;
  10.         $str = chr(ord('A') + $r) . $str;
  11.     } while ($val > 0);
  12.  
  13.     return $str;
  14. }
  15.  
  16. for my $i (0..1000) {
  17.     print excel_col($i) . ' ';
  18. }
  19.  
Apr 4 '11 #13
Thanks Miller. I had used alternative way as per my use case.(with some limitations)
But yours will be applied in broader manner.
Apr 5 '11 #14

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by M. David Allen | last post: by
2 posts views Thread by usenet | last post: by
1 post views Thread by user1357 | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.