|P is for Practical|
LastCol of an Excel spreadsheet as an alphanumeric using Win32::Oleby ww (Bishop)
|on Nov 22, 2005 at 23:43 UTC||Need Help??|
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,
... 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,:
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
The output of the above is:
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
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.
Further update (200511231145): Again, ++ each of you. For future SOPW,
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!