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

Hello,

I am parsing/reading excel file and would like to read the cell contents so i use LastRow and LastCol as described in the tutorial (link below). It worked great for most part but for certain worksheets it does not give right values. It gives 1 & 1 for row and col respectively. I have 8 worksheets in one file and 7 worksheets got right LastRow and Col but only one had a problem. Any idea ? It had freeze pane and macros (all did).


http://www.perlmonks.org/index.pl?node_id=153486
 my $sheet = $oBook->Worksheets($iSheet);  
 my $name=$oBook->Worksheets($iSheet)->{Name};

 my $LastRow = $sheet->UsedRange->Find({What=>"*",SearchDirection=>xlP
+revious,SearchOrder=>xlByRows})->{Row};

 my $LastCol = $sheet->UsedRange->Find({What=>"*",SearchDirection=>xlP
+revious,SearchOrder=>xlByColumns})->{Column};

[download]

Is there any way to get the boundary. Usually my data range is 89-90 columns and 2K-3K rows.

Thank you
UVS

Replies are listed 'Best First'.
Re: Win32::OLE Excel LastCol not working consistently
by Nkuvu (Priest) on Oct 25, 2007 at 18:38 UTC

    It's been a while since I did Excel interactions, but I recall using the xlLastCell special cell. For example:

    # Get the last cell for the sheet (address returned as absolute)
my $last_cell = $worksheet->Range("A1")->EntireColumn->SpecialCells(xl
+CellTypeLastCell)->{Address};
my ($max_column, $max_row) = $last_cell =~ /\$(\w+)\$(\d+)/;

    [download]

    Whether or not that's the most efficient way to do it... I don't know. I do also know that with some spreadsheets I ran into some very odd values -- the spreadsheet wasn't particularly large, but the last cell was reported as IT3991 or similar.

    Added:
    Some of the replies in this SoPW have similar approaches. Specifically, bmann's response seems to be a cleaner bit of code than my sample above.

[reply]
[d/l]
      Thank you. I tried your code it did give some values but not right. The Column was right but row was not. I had last row at 109 but it gave me 1972.

      I also saw links you sent but doesnt answer the last row/col question. I thought there should be a function GetLastRow or GetLastCol - may be not!

[reply]
        Ok it works now!!
        This one particular worksheet had a formula that was not right and it was in column B so it only gave last column=1. After correcting the problem, the old code worked as expected. 
        my $LastRow = $sheet->UsedRange->Find({What=>"*",SearchDirection=>xlPr
+evious,SearchOrder=>xlByRows})->{Row};
 my $LastCol = $sheet->UsedRange->Find({What=>"*",SearchDirection=>xlP
+revious,SearchOrder=>xlByColumns})->{Column};

        [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom