LastCol of an Excel spreadsheet as an alphanumeric using Win32::Ole

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

Humbly, I seek the wisdom of all monkdom in support of my attempt to automate getting the date ("date" is correct) contained in Row 1 of the last populated-Column in an Excel spreadsheet, using Win32::OLE.

By way of background for those spared the pain of Excel, the alphanumeric designations for cells in the top row of an Excel spreadsheet are: A1..Z1 followed by AA1..AZ1 followed by BA1..BZ1 and so on...

However, the standard (or so I'm led to believe) routine for identifying the last-populated-column,

$Win32::OLE::Warn = 3;                     # die ( UGLY ) on errors...
my $Excel =  Win32::OLE->new('Excel.Application', 'Quit');
my $Book = $Excel->Workbooks->Open($FN);
my $Sheet = $Book->Worksheets(1);         # select worksheet number 1

$LastCol = $Sheet->UsedRange->Find({What=>"*",
        SearchDirection=>xlPrevious,
        SearchOrder=>xlByColumns})->{Column};
[download]

... returns a decimal number (equal to the number of columns) in $LastCol).

That's a PITA, because Win32::OLE accepts ONLY an alphanumeric, as in this next snippet, to obtain the date itself from Row1, Last_Column,:

$lastdate =  $Sheet->Range("CK1")->Win32::OLE::valof({'Value'});
print "Data is THROUGH $lastdate \n\n";
[download]

The above generates a warning "Odd number of elements in anonymous hash at getdrills_time3.pl" but I'm pretty sure that's irrelevant because I created that particular problem as I tried to golf this question but the rest of the output is quite satisfactory: Data is THROUGH 11/17/2005

...whereas it gags when fed $LastCol:

$lastdate =  $Sheet->Range($LastCol)->Win32::OLE::valof({'Value'});
print "Data is THROUGH $lastdate \n\n";
[download]

The output of the above is:

Win32::OLE(0.1702) error 0x800a03ec<br>
    in METHOD/PROPERTYGET "Range" at getdrills_time3.pl line 32
[download]

Hence, what I wish to do is convert the numeric value of $LastCol ( "89" is what Win32:0LE returns for the last_column of a spreadsheet with data in "CK" columns) to its equivalent alphanumeric designator, CK1, which could then be passed to the last previous code snippet in place of $LastCol.

Clearly, I could read in Row 1, the dates, instead of skipping it, and extract the last date using the (mostly) the same data_extraction techniques in the main body of the script which is far too long for inclusion (as if this weren't). I'm not happy with that, as I'm afraid I'll slow execution or create memory issues.

As an alternate, I have tinkered with creating a hash which I could then use to look up each (reasonable && possible) $Last_col value (for Row1 only) as a number and an alphanumeric, after which extracting the unique alphanumeric equivalent of a given number is simple enough
...but that too seems awkward, kludgy and apt to pain my successors as they flatten their foreheads.

So, having found nothing I recognized as a solution in Win32::Ole docs, faqs, etc ( including explanations of Win32::Ole::Variant ) and much else, I pray for guidance.

Update: 2 hours; 3 valuable answers to a l-o-o-n-g SOPW. Monastery ++
Now to try each, with thanks to John, bmann and traveler!

Further update (200511231145): Again, ++ each of you. For future SOPW,

There seem to be a "gotcha'" I had not specified nor considered: behavior (under w2k, anyway) seems to be dependent on whether or not the data_file is open in Excel (and may vary from OS to OS) and perhaps also on some peculiarity of my full (ungolfed) code).
Working on w2k with Excel NOT open, I found that the suggestions below DO, in fact, WORK, but that [recusive ref to preceding, ?special case?] I MUST invoke Win32:OLE's "valof" to get the actual date, rather than a memory reference to that date, as in the following example:

$lastdate = $Sheet->Cells(1, $LastCol)->Win32::OLE::valof ({ 'Value'})
+;
print "jcmcnamara's version: $lastdate \n";
[download]

The same applies to bmann's first alternative while his second suggestion appears to depend on having the data_file OPEN in Excel.

And FWIW, for the script I'm actually passing on to my Fire Department, I opted to incorporate traveler's version (again, modified with addition of the valof function) solely because it may be slightly clearer, even if more intimidating, to some future maintainer... which concern is given high priority because I see NO immediate prospect that we'll have another perl_person in the house, but would like the code clear enough to perhaps encourage someone. IOW, if a roof comes in on \me, I'm trying to make it easier for the Dept to find someone to deal with next year's changes... and those which follow.

CAUTION: re all the points in this update, YMMV!

Comment on LastCol of an Excel spreadsheet as an alphanumeric using Win32::Ole Select or Download Code

Replies are listed 'Best First'.
Re: LastCol of an Excel spreadsheet as an alphanumeric using Win32::Ole by jmcnamara (Monsignor) on Nov 23, 2005 at 00:25 UTC
You should be able to get the value as follows (without converting to A1 notation): `$lastdate = $Sheet->Cells(1, $LastCol)->{Value};` [download] -- John.	[reply] [d/l]
Re: LastCol of an Excel spreadsheet as an alphanumeric using Win32::Ole by traveler (Parson) on Nov 23, 2005 at 00:38 UTC
I could not get your code to give me the last column, but this should work `use Win32::OLE; $Win32::OLE::Warn = 3; # die ( UGLY ) on errors... use Win32::OLE::Const 'Microsoft Excel'; my $Excel = Win32::OLE->new('Excel.Application', 'Quit'); my $Book = $Excel->Workbooks->Open("C:\\temp\\foo.xls"); my $Sheet = $Book->Worksheets(1); # select worksheet number 1 $date = $Sheet->UsedRange->End(xlToRight)->Cells(1,1)->{'Value'}; print "$date\n";` [download] at least, that is how I have accessed data in an Excel sheet before. HTH, --traveler	[reply] [d/l]
Re: LastCol of an Excel spreadsheet as an alphanumeric using Win32::Ole by bmann (Priest) on Nov 23, 2005 at 00:27 UTC
You can access a given cell by index, ie: `my $row = 1; my $date = $Sheet->Cells( $row, $LastCol )->{ Value };` [download] You can also get the last cell by using the SpecialCells method (caveat - Excel's notion of "Last Cell" is confusing at best): `my $date = $excel->ActiveCell->SpecialCells( xlLastCell )->{Value}; # assuming you've imported Excel's constants with Win32::OLE::Const` [download] HTH... BTW, I believe that valof is unnecessary in this case - perl sees the date as a date. If it is necessary, you'll need to change the call to `... = Win32::OLE::valof( $Sheet->... )` Update: Reading the other replies, I realize you want row 1. I thought you wanted the last cell. Added `$row = 1`	[reply] [d/l] [select]


P is for Practical
	PerlMonks