Greetings, esteemed Perl Monks!

I have spent several hours searching for an answer. I'd sincerely appreciate any help.

I'm working on a script using Strawberry Perl and Win32::OLE, to read and write (manipulate) Excel spreadsheets. The input files to be processed by this script don't have all the rows numbers. I assumed that spreadsheet rows are always numbered with contiguous natural numbers, beginning with 1. Ex: A1,A2,A3,A4,A5,A6...

Well, the spreadsheets I need to process have rows numbered: A1,A3,A4,A5,A6,A217,A219,A220,A221,A350, ...

( What fresh hell is this?!??! )

So my question is -- How do I do "some computation" for every cell in a given column, given noncontiguous row numbers? Sure, I can do the following:

### Get the size of the area to process.
my $min_row = get_min_row();
my $max_row = get_max_row();

### Get something like D1-E23
my $range = "D$min_row:F$max_row";
my $selected_range = $sheet->Range($range);
my $array_ref = $selected_range->{'Value'};
my $current_row = $min_row;
foreach my $row_ref ( @$array_ref ) {
    ### Get values from columns, D, E, and F.
    my ( $d_val, $e_val, $f_val ) = @$row_ref;

    ### Do some stuff.
    if ( $d_val > 7 ) {
        my @array = split /$e_val/, $f_val;
        my $copied_range = "$current_row:$current_row";
        foreach my $a ( @array ) {
            $sheet->rows($copied_range)->copy();
            $sheet->Cells($current_row, 1)->EntireRow->Insert;
            $sheet->Range($copied_range)->select();
            $sheet->Paste();
            $sheet->Range("S" . $current_row)->{Value} = $a;
        }
    }

    $current_row++; ### This doesn't work.
}

[download]

This won't always work. I'm trying to keep track of the current row number in a counter variable ( $current_row ). Simply incrementing the counter won't work. If only there were something like a "row_number" attribute that I could peek at, to determine which row is currently being processed. Any ideas?

Thanks in advance!

In reply to Win32::OLE and Excel: Iterating when certain row numbers are missing by ownlifeful

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.