perl Win32 OLE Excel

[davidktw]

davidktw....davidktw... davidktw...

Just been to hell and back. You cryptically talked about #######.
It's nightmare on Elms Street. Freaking perl just don't know what to do with it. Hang !

All you need to do is to tell me to blast {ColumnWidth} wide open.
I just don't understand ++ increment just don't work on number from column or row position e.g. $j=$sheet->UsedRange->Cells(1,1)->End(xlToRight)->{Column}; $j++; This darn thingy doesn't work. It has to be $j=$j+1;

So you see..... It was hell. Just sharing so that those who use this thingy don't fall into the trap. Lesson learn.:

Didn't I told you not to use the "Text" property and mentioned it is a formatted string which is affected by the column length of the excel column ? I wouldn't ask you to adjust the ColumnWidth because it is not the right way to use "Text" property for your purpose.

As for the $j++, it works in Perl, I'm not sure how your codes are doing it and if you are doing it right or has they being overloaded. Read http://perldoc.perl.org/overload.html

 

[peterchan75]

Didn't I told you not to use the "Text" property and mentioned it is a formatted string which is affected by the column length of the excel column ? I wouldn't ask you to adjust the ColumnWidth because it is not the right way to use "Text" property for your purpose.

As for the $j++, it works in Perl, I'm not sure how your codes are doing it and if you are doing it right or has they being overloaded. Read http://perldoc.perl.org/overload.html

Ok ok I deserve it.
Indeed not overload.
It was at column 1091 integer. I want to index to next column that was 1092. $j++ didn't happen and $j=$j+1; did.

 

[davidktw]

Ok ok I deserve it.
Indeed not overload.
It was at column 1091 integer. I want to index to next column that was 1092. $j++ didn't happen and $j=$j+1; did.

$j=$sheet->UsedRange->Cells(1,1)->End(xlToRight)->{Column};
print Dumper($j);
$j++;
print Dumper($j);

What did the printout shows ?

 

[peterchan75]

my $new_last_col = $new_sheet->UsedRange->Cells(1,1)->End(xlToRight)->{Column};
print Dumper($new_last_col);
$new_last_col++;
print Dumper($new_last_col);
$k = $new_last_col+1;
print Dumper($k);

$VAR1 = 1110;
$VAR1 = 1111;
$VAR1 = 1112;

BUT if this, it doesn't work.

my $new_last_col = $new_sheet->UsedRange->Cells(1,1)->End(xlToRight)->{Column};
print Dumper($new_last_col);
$k=$new_last_col++;
print Dumper($k);
$k = $new_last_col+1;
print Dumper($k);

VAR1 = 1110;
VAR1 = 1110; # should be 1111
VAR1 = 1112; # this is correct, previous increment did happen

 

[davidktw]

my $new_last_col = $new_sheet->UsedRange->Cells(1,1)->End(xlToRight)->{Column};
print Dumper($new_last_col);
$new_last_col++;
print Dumper($new_last_col);
$k = $new_last_col+1;
print Dumper($k);

$VAR1 = 1110;
$VAR1 = 1111;
$VAR1 = 1112;

BUT if this, it doesn't work.

my $new_last_col = $new_sheet->UsedRange->Cells(1,1)->End(xlToRight)->{Column};
print Dumper($new_last_col);
$k=$new_last_col++;
print Dumper($k);
$k = $new_last_col+1;
print Dumper($k);

VAR1 = 1110;
VAR1 = 1110; # should be 1111
VAR1 = 1112; # this is correct, previous increment did happen

$k=$new_last_col++;

Are you sure you understand post incremental behaviour in all programming languages ?

int i = 0;
printf("%d\n", i++);

Do you know what get printed in the C codes above ?

Please do read this https://en.wikipedia.org/wiki/Increment_and_decrement_operators

 

[ykgoh]

Come in laugh.

But not everyone learned programming formally. Some simply picked up on their own without proper in depth training. We can learn to be forgiving. Even some IT grads can get this wrong. Trust me.

 

[peterchan75]

As an engineer, and I ain't no purist... as long as it works.
So no can do for $k=$j++; So, sure can... $j++; $k=$j;

davidktw, Don't you think $k=$j++; is more efficient coding wise ?

 

[peterchan75]

Come in laugh.

But not everyone learned programming formally. Some simply picked up on their own without proper in depth training. We can learn to be forgiving. Even some IT grads can get this wrong. Trust me.

Don't laugh !
perl array is freakish...
Power sia..... dynamic array.
I can have an array without knowing its size .... without using pointer.

 

[ykgoh]

Don't laugh !
perl array is freakish...
Power sia..... dynamic array.
I can have an array without knowing its size .... without using pointer.

I am not laughing at the dynamic array. It's not the problem why you are getting the unexpected values. Go read davidktw's post carefully.

 

[davidktw]

As an engineer, and I ain't no purist... as long as it works.
So no can do for $k=$j++; So, sure can... $j++; $k=$j;

davidktw, Don't you think $k=$j++; is more efficient coding wise ?

The rule of thumb is correctness before performance.

What you want to use is something like this

$k=++$j;

To answer you on performance, code wise it will be more terse, but performance wise, probably not at all or not much. Perl is too high level and it is running on TOP of a VM and there will be Optimisation too, so what you did is just doing the work of a compiler. The only way to tell is benchmarking.

 

[peterchan75]

@davidktw,
Thanks.
On a practical side. is this a "proper" way to create a 2D array?

push @array [split ',',join(',',@row)];

 

[davidktw]

@davidktw,
Thanks.
On a practical side. is this a "proper" way to create a 2D array?

push @array [split ',',join(',',@row)];

Your codes above is strange, why do you want to join the "row" array into a string and then split up the string back into array again?

For the codes above, you could have written as

push @array, \@row;
or
push @array, [@row]; //same effect but different approach

Btw Perl has no multidimensional array. It is an illusion. What you are doing is actually putting an array reference into another array. So each element of the outer array is a reference to another array.

 

[peterchan75]

I have a hard time grasping it too. Believe you me... that darn thing works. Since it's allusion, I can access the darn thing like $array[$i][$j] and sort the array like spreadsheet.

Btw, I might have tried what you have suggested. I will try again.

 

[davidktw]

I have a hard time grasping it too. Believe you me... that darn thing works. Since it's allusion, I can access the darn thing like $array[$i][$j] and sort the array like spreadsheet.

Btw, I might have tried what you have suggested. I will try again.

Chan, this is what we call sugar syntax. Try this.

https://gfx.github.io/perl.js/#use ...n"; print $a[0][2],"\n"; print $a[1][2],"\n";

 

[ykgoh]

Your codes above is strange, why do you want to join the "row" array into a string and then split up the string back into array again?

For the codes above, you could have written as

push @array, \@row;
or
push @array, [@row]; //same effect but different approach

Btw Perl has no multidimensional array. It is an illusion. What you are doing is actually putting an array reference into another array. So each element of the outer array is a reference to another array.

I am guessing the hack is a way to trim off unnecessary whitespaces at the start and end of a token. Through joining and then splitting, the whitespaces are ignored. But I may be wrong. Since whitespaces are not indicated as delimiters, they may still be preserved.

 

[davidktw]

I am guessing the hack is a way to trim off unnecessary whitespaces at the start and end of a token. Through joining and then splitting, the whitespaces are ignored. But I may be wrong. Since whitespaces are not indicated as delimiters, they may still be preserved.

It doesn't work this way for split function in Perl.

One approach to perform the intention you have described is

split / *,+ */, $haystack

Perl split function does not treat whitespace specially nor redundantly. Whitespace are just as meaningful as any other characters. So I'm afraid what Chan has presented is unlikely a hack

Below are some examples playing with the idea.

# perl -MData::Dumper -e '@a=("1   ","","  3"); print Dumper(split / *,+ */, join ",", @a), "\n"'
$VAR1 = '1';
$VAR2 = '3';

# perl -MData::Dumper -e '@a=("1   ","","  3"); print Dumper(split /,/, join ",", @a), "\n"'
$VAR1 = '1   ';
$VAR2 = '';
$VAR3 = '  3';

# perl -MData::Dumper -e '@a=("1   ","","","  3"); print Dumper(split / *,+ */, join ",", @a), "\n"'
$VAR1 = '1';
$VAR2 = '3';

 

[peterchan75]

I am guessing the hack is a way to trim off unnecessary whitespaces at the start and end of a token. Through joining and then splitting, the whitespaces are ignored. But I may be wrong. Since whitespaces are not indicated as delimiters, they may still be preserved.

Guessing....
I am guessing too on this $j=$k++;
Some perlmonk taught me that push join and splitting stuff and another 1 liner to output an array to a csv file. It works everytime.

 

[peterchan75]

Chan, this is what we call sugar syntax. Try this.

https://gfx.github.io/perl.js/#use ...n"; print $a[0][2],"\n"; print $a[1][2],"\n";

Wow! This is neat. Thanks.
I use test.pl on notepad++ to test all my code. I use that push array heck code to populate big array. perl is more cryptic and powerful than visual basic. Come to think of it, maybe a 2D array is just a bunch of rows with similar length. Don't all arrays does that in PL/1, Fortran, Cobol, Pascal etc. Maybe java, C++

PS: Line #6 shouldn't been executed... it should be subscript out of bound...

 

[davidktw]

Wow! This is neat. Thanks.
I use test.pl on notepad++ to test all my code. I use that push array heck code to populate big array. perl is more cryptic and powerful than visual basic. Come to think of it, maybe a 2D array is just a bunch of rows with similar length. Don't all arrays does that in PL/1, Fortran, Cobol, Pascal etc. Maybe java, C++

PS: Line #6 shouldn't been executed... it should be subscript out of bound...

Array in a lot of strongly typed languages that compiled to native codes implement Array concept more rigidly. Of course this is not for certain, compilers can still work wonders between syntax and actual underlying structures.

The reason why line #6 faulted is not because it is array out of bound access. it is because the return value is undef. "undef" is undefined and trying to print it shows the "use of uninitialised value" warning. Perl array or more accurately termed as List is dynamic in nature. You can always do $arr[0][1000] = 1; and new elements filled with "undef" values will be created if there is no filled values elements before it.

Perl goes a long way before visual basic and it is known to have one of the most interesting features as oppose to a lot of other programming languages out there today. Moving forward you will not find much chance having another language of this sort since the industry is definitely moving to simplier languages with more intelligent and dynamic-ness under the hood and also more type safe.

 

[davidktw]

Guessing....
I am guessing too on this $j=$k++;
Some perlmonk taught me that push join and splitting stuff and another 1 liner to output an array to a csv file. It works everytime.

Do show us the article you have read that does what you have suggested?

For your case of printing a CSV line for each row of data contained in an array, I would have done it this way.

{ local $,='","'; print '"', @row, '"', "\n" }

To ensure escaping are done properly, I would have done it more robustly this way,

{ local $,='","'; print '"', map { s/"/""/g } @row, '"', "\n" }

I believe this will be a single liner too, would it not be?