How to extract data from Excel by using Win32 OLE

plagent has asked for the wisdom of the Perl Monks concerning the following question:

I loaded the following packages to extract information from Excel file.

use strict;
use FileHandle;
use Win32::OLE;
use Win32::OLE qw(in with);
use Win32::OLE::Variant;
use Win32::OLE::Const 'Microsoft Excel';
[download]

However, I noticed that if the cell in Excel file contains such value "0.898124", the output instead is changed to integer type, like "0833561".

Here is my method to extract the corresponding value: $Sheet->Cells($row_num,$label_hash_ref->{'alt'})->{'Value'};

I googled the answer. Someone suggested that I should use this method:

$Sheet->Cells($row_num,$label_hash_ref->{'alt'})->value();

But it failed.

Then other post said this way:

Variant(VT_R8, $Sheet->Cells($row_num,$label_hash_ref->{'ESP'})->value());

But it failed.

So, my question is , how to extract the original format from Excel file and keep them untouched as it was. Thanks.

Comment on How to extract data from Excel by using Win32 OLE Select or Download Code

Replies are listed 'Best First'.
Re: How to extract data from Excel by using Win32 OLE by ww (Archbishop) on Dec 14, 2013 at 12:07 UTC
Unless you're stuck on using Win32::OLE (which represents fine work by Jan Dubois) or need to parse some version of Excel not supported John McNamara's Spreadsheet::ParseExcel module, you may wish to consider the latter.	[reply]
Re^2: How to extract data from Excel by using Win32 OLE by plagent (Novice) on Dec 16, 2013 at 06:00 UTC
I installed the module you mentioned. But it ensued another problem which complained that "No Excel data found in file.", even I carefully examined the path to the Excel files. My system is Win8.	[reply]
Re: How to extract data from Excel by using Win32 OLE by davies (Monsignor) on Dec 14, 2013 at 23:16 UTC
Unlike Excel, Perl is case sensitive. Assuming that your code posted here is exactly as in your problem, case looks like being part of the issue. My code (again, assuming everything else is correct) would be `$Sheet->Cells($row_num,$label_hash_ref->{'alt'})->Value;`. Braces around `Value` are optional. If this isn't your problem, try finding out what those cell co-ordinates are and querying that cell explicitly. I suspect you're not giving us all the information we need. I've never seen a pure integer with a leading zero returned from Win32::OLE in the way you describe. Regards, John Davies	[reply] [d/l] [select]
Re^2: How to extract data from Excel by using Win32 OLE by plagent (Novice) on Dec 16, 2013 at 06:09 UTC
Hi John, I changed to the upcase, but it still kept same, so I think this is not my problem. I think my co-ordinations, as specified in $row_num and $label_hash_ref->{'alt'}, are right because nearby values in different columns are extracted correctly. Because it is the only line to extract data from Excel files, I am still not sure how to do next. Thanks in advance.	[reply]
Re^3: How to extract data from Excel by using Win32 OLE by davies (Monsignor) on Dec 18, 2013 at 15:32 UTC
I can't reproduce your problem. The following code works for me: `use strict; use warnings; use Win32::OLE; my $xl = Win32::OLE->new('Excel.Application'); $xl->{Visible} = 1; my $wb = $xl->Workbooks->Add; for my $nSht (2..$wb->Sheets->{Count}) { $wb->Sheets(2)->Delete; } my $Sheet = $wb->Sheets(1); $Sheet->Cells(2,2)->{Value} = 0.898124; print $Sheet->Cells(2,2)->Value; $xl->{DisplayAlerts} = 0; $xl->Quit;` [download] If you can't solve your problem from this, please post your own SSCCE (~~http://sscce.org/~~ https://web.archive.org/web/20160926072757/http://sscce.org), otherwise I, at least, will be able to do little more. Regards, John Davies	[reply] [d/l]