bruno said:
As I was told to provide a code sample, here it is:
This function returns an array of lines, where every line
contains the cells of the excel spreadsheet seperated by tabs.
Cells that contain simple text or numbers are exported as expected,
cells that contain date values are exported as:
Win32::OLE::Variant=SCALAR(0x20d341c)
eg:
number text date
491 Stammdaten Win32::OLE::Variant=SCALAR(0x20d341c)
487 Abrechnung Win32::OLE::Variant=SCALAR(0x20d25e0)
482 Audiometrie Win32::OLE::Variant=SCALAR(0x20d1274)
#############################################################
# start of sample
# just paste and provide valid filename
use Win32::OLE::Const 'Microsoft Excel';
use strict;
use warnings; # ***always*** use warnings when debugging!!!
my $excelfile = "test.xls";
sub readexcelfile {
my $source = $_[0];
my @all;
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit');
my $Book = $Excel->Workbooks->Open("$source");
my $Sheet = $Book->Worksheets(1);
my $rows = $Sheet->UsedRange->Rows->Count;
my $blatt = $Sheet->Range("A1:K$rows")->{'Value'};
$Book->Close(0);
foreach my $ref_zeile (@$blatt) {
my $line = "";
for my $item (@$ref_zeile) {
Here, $item is not necessarily a string. It could be a
Win32::OLE::Variant object, which could be a variety of things, one of
which is a reference to a scalar. You get the message you got because
$item was a blessed reference to a scalar. A "quick fix" is to test to
see if $item is a reference, and, if it is, assume it is a reference to
a Win32::OLE::Variant Date object, and stringify the date. This can be
done with:
use Win32::OLE::NLS qw
LOCALE
ATE); #to get the constant
$line.=(ref($item)?$item->Date(DATE_LONGDATE):$item)."\t";
That will stringify an Excel date to something that prints useful info.
But if the $item is a Variant which is not a Date, this will mess up.
To treat the full range of possible $item's will involve testing the
item to see if it is a Win32::OLE::Variant object (hint: ref() ), and,
if it is, using a couple of methods from Win32::OLE::Variant, such as
the ->Type method to figure out what type of Variant it is, and then
calling an appropriate method to convert it according to an appropriate
format. I leave that as an exercise for the reader.
$line.=$item."\t";
}
push(@all,$line);
}
#@all = reverse sort buildvgl @all;
return @all;
}
#dummy main to read the file and dump the lines:
{
my @lines = readexcelfile($excelfile);
foreach(@lines) {
print "$_\n";
}
}
# end of sample
#############################################################
If there is a better way, or an easy way to directly access the file
(without excel needed to be installed on that particular workstation)
please let me know.
There are many ways to go, most of which are a lot less hassle than
using Win32::OLE with all the foibles of OLE. One would be to look on
CPAN for Excel-related modules. Spreadsheet:
arseExcel might do the
trick, and shouldn't require the presence of Excel (so you can use it on
any OS, even one for which Excel is not available). I know nothing
about this module other than that is exists.
Another would be to treat your spreadsheet as a database and use the DBI
module with DBD::Excel to access it. That is probably the easiest.
HTH.
