Loading ...
Sorry, an error occurred while loading the content.

Get the rowid in Excel using perl

Expand Messages
  • ramaparvathi
    Hi, My program is to read a excel file. Search for a value in the first column of the file. And get the row number for that value found in the file. I have got
    Message 1 of 3 , Aug 23, 2007
    • 0 Attachment
      Hi,
      My program is to read a excel file.
      Search for a value in the first column of the file.
      And get the row number for that value found in the file.
      I have got the code to work to find the value but I am not able
      to find the number.
      Can someone please help me.
      Thanks,
      Rama

      #!/usr/bin/perl
      use strict;
      use Win32::OLE qw(in with);
      use Win32::OLE::Const 'Microsoft Excel';
      $Win32::OLE::Warn = 3; # die
      on errors...
      my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
      || Win32::OLE->new('Excel.Application', 'Quit'); # get already
      active Excel
      #
      application or open new
      my $Book = $Excel->Workbooks->Open("C:\\test.xls"); # open Excel file
      my $Sheet = $Book->Worksheets(3); # select worksheet number 1
      my $array = $Sheet->Range("A1:A19")->{'Value'}; # get the
      contents
      my $test_id1 = "107";
      $Book->Close;
      foreach my $ref_array (@$array) { # loop
      through the array
      $count++; # referenced by $array
      foreach my $scalar (@$ref_array)
      {
      $count++;
      if ($test_id1 =~ m/$scalar/)
      {
      print "Found";

      }
      }
      }
    • Vijaya Kumar
      Hi, Your code is working well in my system. I think, you need to check in your excel. Regards Vijay ... file
      Message 2 of 3 , Aug 24, 2007
      • 0 Attachment
        Hi,

        Your code is working well in my system.

        I think, you need to check in your excel.

        Regards
        Vijay

        --- In perl-beginner@yahoogroups.com, "ramaparvathi"
        <ramaparvathi@...> wrote:
        >
        > Hi,
        > My program is to read a excel file.
        > Search for a value in the first column of the file.
        > And get the row number for that value found in the file.
        > I have got the code to work to find the value but I am not able
        > to find the number.
        > Can someone please help me.
        > Thanks,
        > Rama
        >
        > #!/usr/bin/perl
        > use strict;
        > use Win32::OLE qw(in with);
        > use Win32::OLE::Const 'Microsoft Excel';
        > $Win32::OLE::Warn = 3; # die
        > on errors...
        > my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
        > || Win32::OLE->new('Excel.Application', 'Quit'); # get already
        > active Excel
        > #
        > application or open new
        > my $Book = $Excel->Workbooks->Open("C:\\test.xls"); # open Excel
        file
        > my $Sheet = $Book->Worksheets(3); # select worksheet number 1
        > my $array = $Sheet->Range("A1:A19")->{'Value'}; # get the
        > contents
        > my $test_id1 = "107";
        > $Book->Close;
        > foreach my $ref_array (@$array) { # loop
        > through the array
        > $count++; # referenced by $array
        > foreach my $scalar (@$ref_array)
        > {
        > $count++;
        > if ($test_id1 =~ m/$scalar/)
        > {
        > print "Found";
        >
        > }
        > }
        > }
        >
      • Vijaya Kumar
        Hi Try the following code. It prints the row number Regards Vijay #!/usr/bin/perl use strict; use Win32::OLE qw(in with); use Win32::OLE::Const Microsoft
        Message 3 of 3 , Aug 24, 2007
        • 0 Attachment
          Hi
          Try the following code. It prints the row number

          Regards
          Vijay

          #!/usr/bin/perl
          use strict;
          use Win32::OLE qw(in with);
          use Win32::OLE::Const 'Microsoft Excel';
          $Win32::OLE::Warn = 3; # die on errors...
          my $Excel = Win32::OLE-> GetActiveObject('Excel.Application') ||
          Win32::OLE-> new('Excel. Application' , 'Quit'); # get already active
          Excel application or open new
          my $Book = $Excel->Workbooks->Open("C:\\test.xls"); # open Excel file
          my $Sheet = $Book->Worksheets(3); # select worksheet number 1
          my $array = $Sheet->Range("A1:A19")->{'Value'}; # get the contents
          my $test_id1 = "107";
          $Book->Close;
          my $count = 0;

          foreach my $ref_array (@$array)
          { # loop through the array
          $count++; # referenced by $array
          foreach my $scalar (@$ref_array)
          {
          #$count++;
          if ($test_id1 =~ m/$scalar/)
          {
          print $count;
          #print "Found";
          }
          }
          }

          --- In perl-beginner@yahoogroups.com, "ramaparvathi"
          <ramaparvathi@...> wrote:
          >
          > Hi,
          > My program is to read a excel file.
          > Search for a value in the first column of the file.
          > And get the row number for that value found in the file.
          > I have got the code to work to find the value but I am not able
          > to find the number.
          > Can someone please help me.
          > Thanks,
          > Rama
          >
          > #!/usr/bin/perl
          > use strict;
          > use Win32::OLE qw(in with);
          > use Win32::OLE::Const 'Microsoft Excel';
          > $Win32::OLE::Warn = 3; # die
          > on errors...
          > my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
          > || Win32::OLE->new('Excel.Application', 'Quit'); # get already
          > active Excel
          > #
          > application or open new
          > my $Book = $Excel->Workbooks->Open("C:\\test.xls"); # open Excel
          file
          > my $Sheet = $Book->Worksheets(3); # select worksheet number 1
          > my $array = $Sheet->Range("A1:A19")->{'Value'}; # get the
          > contents
          > my $test_id1 = "107";
          > $Book->Close;
          > foreach my $ref_array (@$array) { # loop
          > through the array
          > $count++; # referenced by $array
          > foreach my $scalar (@$ref_array)
          > {
          > $count++;
          > if ($test_id1 =~ m/$scalar/)
          > {
          > print "Found";
          >
          > }
          > }
          > }
          >
        Your message has been successfully submitted and would be delivered to recipients shortly.