Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

Searching a pattern in a xlsx

by kaushik9918 (Sexton)
on Jun 21, 2018 at 05:50 UTC ( #1217094=perlquestion: print w/replies, xml ) Need Help??

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

Hi Can anyone here point me to an existing thread or suggest me a good short routine/function to find a user defined pattern in an xlsx and return its row and col number? thanks in advance

Replies are listed 'Best First'.
Re: Searching a pattern in a xlsx
by beech (Parson) on Jun 21, 2018 at 06:25 UTC
      Many Thanks will look up.
Re: Searching a pattern in a xlsx
by thanos1983 (Parson) on Jun 21, 2018 at 08:58 UTC

    Hello kaushik9918,

    Although the fellow Monk beech provided you already the best answer just for fun an alternative solution.

    Another possible way, with more lines of code is to simply read the excel file all sheets and search for the keyword while you iterate. On sample of code bellow I create a test sheet and then I iterate over it. For you the important part is where you read through the rest is unnecessary.

    #!/usr/bin/perl use strict; use warnings; use Data::Dumper; use feature 'say'; use Excel::Writer::XLSX; use Spreadsheet::Read qw(ReadData); my $workbook = Excel::Writer::XLSX->new( 'simple.xlsx' ); my $worksheet = $workbook->add_worksheet(); my @data_for_row = (1, 2, 3); my @table = ( [4, 5], [6, 7], ); my @data_for_column = (10, 11, 12); $worksheet->write( "A1", "Hi Excel!" ); $worksheet->write( "A2", "second row" ); $worksheet->write( "A3", \@data_for_row ); $worksheet->write( 4, 0, \@table ); $worksheet->write( 0, 4, [ \@data_for_column ] ); $workbook->close; # From this point onward is important my $book = ReadData ('simple.xlsx'); my @sheets = @$book[0]->{sheets}; my %hash; my $key_word = 'second row'; foreach my $sheet_number (@sheets) { my @rows = Spreadsheet::Read::rows($book->[$sheet_number]); foreach my $i (1 .. scalar @rows) { foreach my $j (1 .. scalar @{$rows[$i-1]}) { say "Cell/Row: " . chr(64+$j) . $i . " at sheet: ".$sheet_numb +er." key word: " . ($rows[$i-1][$j-1] // '') if ($rows[$i-1][$j-1] // '') eq $key_word; $hash{"Sheet: " . $sheet_number}{chr(64+$j) . "$i "} = ($rows[ +$i-1][$j-1] // ''); } } } # print Dumper \%hash; __END__ $ perl Cell/Row: A2 at sheet: 1 key word: second row

    Hope this helps, BR.

    Seeking for Perl wisdom...on the process of learning...not there...yet!
      Thank you for taking out time to write this, but I already know the sheet from where I want to read. So, I will filter out the parts I want from your code and check. thanks again!

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1217094]
Approved by beech
Front-paged by Corion
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (3)
As of 2021-05-15 09:10 GMT
Find Nodes?
    Voting Booth?
    Perl 7 will be out ...

    Results (150 votes). Check out past polls.