Hi All, I am trying to compare the excel file and a word doc.I have few keywords in an excel sheet and when they match in word doc,I should print the whole sentence which contains the keyword to another output file,an excel sheet.As of now I am just able to print the matched keyword in output file.I need help to extract the sentence.Please see the code below which I am using.

#!/usr/bin/perl –w


    use strict;

    use warnings;

    use Spreadsheet::ParseExcel;

    use Spreadsheet::WriteExcel;


    my $FileName = "/Users/Reku/Documents/nfrFinal.xls";

    my $parser   = Spreadsheet::ParseExcel->new();

    # Create a new Excel file

my $FileName1 = "/Users/Reku/Documents/nfrOutput2.xls";

my $workbook1 = Spreadsheet::WriteExcel->new($FileName1);


# Add a worksheet

my $worksheet1 = $workbook1->add_worksheet("NFR");


$worksheet1->write(0,0,"NFR");

$worksheet1->write(0,1,"Keyword");

$worksheet1->write(0,2,"Occurance");

    

    my $workbook = $parser->parse($FileName);

    my $srs = '/Users/Reku/Documents/SRSSample.doc';

    die $parser->error(), ".\n" if ( !defined $workbook );

        

       my $col1=0;

       my $flag=0;

       my $attribute=0;

       my $cell2=0;

        

    # Following block is used to Iterate through all worksheets

    # in the workbook and print the worksheet content 


    for my $worksheet ( $workbook->worksheets() ) 

    {


        # Find out the worksheet ranges

        my ( $row_min, $row_max ) = $worksheet->row_range();

        my ( $col_min, $col_max ) = $worksheet->col_range();

        

        my $col=0;

        

      for my $col ( $col_min..$row_max ) {

     for my $row ( $row_min .. $row_max ) {

     

         

                 

                  my $count=1;

         

                 

                

                # Return the cell object at $row and $col

               my $cell = $worksheet->get_cell( $row, $col );

                $cell2 = $worksheet->get_cell(0,$col);

               

               

                $attribute = $cell2->value();

               $worksheet1->write(0,$col,$attribute);

               

                next unless $cell;

               my $value =$cell->value();

            

            

                if($value eq "")

                {

                }

                else

                {

                open('FILE',$srs) or die $1;  

while(<FILE>)

{

my $String = $_;

my @temp=split(' ',$String);

foreach my $loop(@temp)

{

$count++    if($loop =~m/$value/i)

}

  }

  $col1++;

            my $match=$cell2->value();

         

   if($count>1)  {           

print "\n$value occured $count time(s) in ur srs,so u should consider 
+$match\n";

#if($match eq $attribute){

$worksheet1->write($count,$col,$value);

#}

#$worksheet1->write($col1,1,$value);

#$worksheet1->write($col1,2,$count);

#$col1++;

                 }

                close(FILE);

                }

            }

            

        }

[download]
}

In reply to Word and Excel by PerlStart

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.