perl Win32 OLE Excel

[peterchan75]

Hi All,

I am trying to use perl to update some excel spreadsheet using Win32 OLE.
1. Comparing ($sheet->Cells(1,1)->{Value} eq $input_array[0]) didn't work. @input_array contained 1 to 5 character string.
But it worked with this ($sheet->Cells(1,1)->{Text} eq $input_array[0]) . Any idea ?

2. Trying to assign a text string to a cell.
$sheet->Cells(1,1)->{Text} = $input_array[0] didn't work.
But $sheet->Cells(1,1)->{Value} = $input_array[0] worked. Any idea ?

Thanks.

 

[ykgoh]

According to ActiveState document, text is the formatted form of the value being displayed. Could it be a read only field?

To retrieve the formatted value of a cell you should use the {'Text'} property instead of the {'Value'} property. This returns exactly what is being displayed on the screen though! If the column is not wide enough, you get a value of '######':
my $array = $Sheet->Range("A8:B9")->{'Text'};

Most examples showing how to get or set the value use the {' Value'} property instead. I notice you did not use single quote to enclose yours. Not sure if this would cause any trouble.

As to why the eq operator didn't work, best is to inspect the value in a debugger. Maybe due to how OLE represent or encode string or due to extra whitespaces, the comparison failed. You have to verify it yourself.

 

[peterchan75]

I notice the single quote thingy. Tried that too but the result is just the same. I was contemplating whether to do the update in Excel but the number crunching heavy lifting are done in perl. So I tried to push it from perl but it seems like I am hitting a mind field.

 

[davidktw]

Hi All,

I am trying to use perl to update some excel spreadsheet using Win32 OLE.
1. Comparing ($sheet->Cells(1,1)->{Value} eq $input_array[0]) didn't work. @input_array contained 1 to 5 character string.
But it worked with this ($sheet->Cells(1,1)->{Text} eq $input_array[0]) . Any idea ?

2. Trying to assign a text string to a cell.
$sheet->Cells(1,1)->{Text} = $input_array[0] didn't work.
But $sheet->Cells(1,1)->{Value} = $input_array[0] worked. Any idea ?

Thanks.

What ykgoh has mention could be quite right. Text property could be just read-only. setting value to it may not have effect. You are using a String comparator "eq" to compare with the value obtained from the "Value" field. I recommend you Dump out the value using print Dumper(...), see what datatype you are getting from the "Value" field.

 

[peterchan75]

I am not a computer language purist. As long as it works consistently, then it's fine with me. So far so good. I was dredging google for this but didn't find anything on string comparison and assignment. Anyway this Win32 OLE is piece of ancient history.

 

[davidktw]

I am not a computer language purist. As long as it works consistently, then it's fine with me. So far so good. I was dredging google for this but didn't find anything on string comparison and assignment. Anyway this Win32 OLE is piece of ancient history.

What does any of the response above has to do with language purist or not? We are giving you the reason why "eq" didn't work as expected and I ask you to test out what you are comparing.

 

[peterchan75]

What does any of the response above has to do with language purist or not? We are giving you the reason why "eq" didn't work as expected and I ask you to test out what you are comparing.

Sorry. I said, "I am not" does not mean that I am implying others are. The documentation for this is few and far between. I will take it as is and make a note in the script and move on. Peace!

Anyway, I did a Dumper....
$VAR1 = bless( do{\(my $o = 43224360)}, 'Win32::OLE::Variant' ); ->{Value}
$VAR2 = '5/16/2016'; ->{Text}
$VAR3 = '5/16/2016'; - from array
$VAR1 = 'AAU'; ->{Value}
$VAR2 = 'AAU'; ->{Text}
$VAR3 = 'AAU'; - from array

Seems like it's trying to do something on the date string.

 

[davidktw]

Sorry. I said, "I am not" does not mean that I am implying others are. The documentation for this is few and far between. I will take it as is and make a note in the script and move on. Peace!

Anyway, I did a Dumper....
$VAR1 = bless( do{\(my $o = 43224360)}, 'Win32::OLE::Variant' ); ->{Value}
$VAR2 = '5/16/2016'; ->{Text}
$VAR3 = '5/16/2016'; - from array
$VAR1 = 'AAU'; ->{Value}
$VAR2 = 'AAU'; ->{Text}
$VAR3 = 'AAU'; - from array

Seems like it's trying to do something on the date string.

For your understanding, Value field doesn't necessarily return a String. Sometimes it can be a Win32::OLE::Variant Object. That is why your "eq" will fails at times. You shouldn't use "Text" field for comparison too because in the event the column is not width enough, you may get "#####" as value instead of the string. "Text" field is a formatted string value, not just a string. You should be using the Value field instead and in the event of encountering a Win32::OLE::Variant object, use the methods in it to format the variant to your intended datatype and compare. Do read up http://search.cpan.org/~jdb/Win32-OLE-0.1712/lib/Win32/OLE/Variant.pm

 

[peterchan75]

@davidktw,
Thanks.
Thank goodness for my data source is from csv file as such, it's void of any format. It looks like I have escape from that trap.

 

[davidktw]

@davidktw,
Thanks.
Thank goodness for my data source is from csv file as such, it's void of any format. It looks like I have escape from that trap.

Formatting is done by Excel when you extract that piece of information from the Excel file. I don't think it has anything to do with whether you first imported that information from CSV or your hand input it yourself into Excel. I believe the Win32::OLE::Variant is also an indication that there is more than one way to represent the data when you have consumed from your CSV or Excel, which is if you just blindly use "Text" field as your comparison operand, you might be getting surprises.

If your input is just plain old CSV, why do you even need to use Win32::OLE for your purpose ? Module Text::CSV will do just fine. In fact, you can use skip using Win32::OLE and also use Excel::Writer::XLSX for your Excel output

 

[peterchan75]

Surprises !
I really hate them !
Here is my data flow.
1. Data spew out from database.
2. perl to cruch them. I used to do it in Excel VBA. Slow.
3. Output report from perl to excel via Win32 OLE.
Just need to populate the data in excel spreadsheet. Sometimes do some simple arithmetic. Cannot touch Excel files as they nice charts and pivots. Don't like to mess with them anymore.

 

[davidktw]

Surprises !
I really hate them !
Here is my data flow.
1. Data spew out from database.
2. perl to cruch them. I used to do it in Excel VBA. Slow.
3. Output report from perl to excel via Win32 OLE.
Just need to populate the data in excel spreadsheet. Sometimes do some simple arithmetic. Cannot touch Excel files as they nice charts and pivots. Don't like to mess with them anymore.

For (1), you can use OLE/ODBC data source to your external data source, unless you feel this method doesn't work for you, it' ok.

For (2), if your data is consumed from the database and then to be outputted into Excel, why is there involvement of Win32::OLE, all those comparison and so forth shouldn't haven't reach the output stage yet. You can just perform all these inside perl first, unless you are reading some existing data from the target excel and manipulating what you have consumed from the database ?

For (3), like i have mentioned, you don't need to use Win32::OLE. Just use a more dedicated Excel module found in CPAN for your use.

 

[peterchan75]

I could generate new set of excel reports everyday from perl. But that's too much. I used to crunch data with Excel VBA so output to Excel is seamless. But now I crunch with perl and need a way to get the data to Excel. I need to synchronize the data between them, so need to compare.

 

[davidktw]

I could generate new set of excel reports everyday from perl. But that's too much. I used to crunch data with Excel VBA so output to Excel is seamless. But now I crunch with perl and need a way to get the data to Excel. I need to synchronize the data between them, so need to compare.

From your description, do you mean you are using perl to access data in the excel, manipulate it and then output back into the same Excel workbook ?

 

[peterchan75]

From your description, do you mean you are using perl to access data in the excel, manipulate it and then output back into the same Excel workbook ?

Yes. I need to find where the data end in Excel and push in new data from perl and do cumulation/summation. So Win32 OLE is the right path ?

 

[ykgoh]

Yes. I need to find where the data end in Excel and push in new data from perl and do cumulation/summation. So Win32 OLE is the right path ?

Are you using Excel spreadsheet as a data store or you are extensively using Excel to do data analytics 90% interactively of the time?

Those accumulation and summations are best left to spreadsheet or database. No point writing own codes just to add and sum by looping line by line records.

If you're writing some Perl codes just to interface with remote data sources or do a little conversion or formatting, I can understand. If you're trying to reimplement the Excel SUM function or SQL SUM function, I think it's a bad idea.

 

[ykgoh]

Do you own the source database? For serious and efficient calculation, if you know SQL, you can do it using stored procedures and views. It is efficient that way.

Your Excel can then just draw the data out for presentation using charts.

You can also consider looking at business intelligence products like SQL Server Analysis Services and reporting solutions like Reporting Services or Crystal Report.

 

[davidktw]

Yes. I need to find where the data end in Excel and push in new data from perl and do cumulation/summation. So Win32 OLE is the right path ?

Win32::OLE is just an interface for external system to interact with Microsoft products that offers such interface. No right or wrong, just if it get your job done with complexity you can manage and performance you can accept or not.

Read ykgoh's post and see if that approach is something you can consider. It will indeed be more efficient if you can source your data from a database instead. Even an embedded database like SQLite can give you better performance and more standard query simplicity compared to an ad-hoc solution.

All you need is keep a template of the excel that you are going to produce, have your data inserted into database for query before you extract them and place them into the excel template and then output into a separate file.

 

[peterchan75]

Can't touch the data source.
Just take the data and crunch them into information.
Put the info in Excel. Excel files hold a few thousand lines and purge after a few years. Anyway, the perl codes are working fine. Will see how things go. If it ain't broke, don't fix it. If Win32 OLE is the only 2 way communication available then I can live with it.

 

[peterchan75]

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