Hi Nansh,
One easy way to do this is to save your .xlsx as something like a "text (tab delimited)" type file using Excel and then use Perl to process it as a simple... well tab delimited .txt file. I actually do this a lot.
If you do want a way to do it with Perl directly, here is some unrelated (and VERY crude) example code of my own using of Spreadsheet::ParseXLSX I wrote as a quick one-off awhile back. It may at least get you started along with the examples provided by the author in CPAN.
#!/usr/bin/perl
use utf8;
use 5.022;
use strict;
use Spreadsheet::ParseXLSX;
use HTTP::Tiny;
BEGIN
{
$| = 1; # turn on forced buffer flushing
say "Performing overhead tasks...";
$| = 0; # turn off forced buffer flushing
}
my @input_files = glob "*.xlsx";
die "ABORT: There were no .xlsx files found in the local directory" un
+less @input_files;
say "Reading Parts List(s)...";
my @parts;
foreach my $input_file (@input_files)
{
my $parser = Spreadsheet::ParseXLSX->new;
my $workbook = $parser->parse($input_file);
foreach my $worksheet ( $workbook->worksheets() )
{
my ( $row_min, $row_max ) = $worksheet->row_range();
my ( $col_min, $col_max ) = $worksheet->col_range();
foreach my $row ( $row_min+1 .. $row_max )
{
my $cell = $worksheet->get_cell( $row, $col_min );
next unless $cell;
push @parts, $cell->value();
}
}
}
say "Fetching Featured Products...";
my (@products, @links);
foreach my $part (@parts)
{
my $url = 'http://www.digikey.com/product-search/en?keywords=' . $
+part;
my $response = HTTP::Tiny->new->get($url);
if ( $response->{success} )
{
if ( $response->{content} =~ /Featured Product.*?<a href="(.*?
+)".*?>(.*?)<\/a>/si )
{
say "Success: $part, $2, $1";
push @links, $1;
push @products, $2;
}
else
{
say "Success: $part, but no Featured Product listed";
push @links, '-';
push @products, '-';
}
}
else
{
say "Failed: $part, unable to connect to \"$url\"";
}
}
say "Creating \"Featured_Products.txt\" Output File...";
open(my $out_fh, ">", "Featured_Products.txt") or die "Cannot open \"F
+eatured_Products.txt\": $!.";
print $out_fh "DK Part Number\tFeatured Product Text\tFeatured Product
+ Link\n";
print $out_fh "$parts[$_]\t$products[$_]\t$links[$_]\n" foreach (0 ..
+$#parts);
close $out_fh;
say "FINISHED";
END
{
require ExtUtils::MakeMaker; # placing this require statement here
+ for convenient copy-paste of this END block code to other programs.
ExtUtils::MakeMaker::prompt('---Press Enter to quit---') if ($?);
+# stop and prompt for acknowledgement if program exits with a non-zer
+o value.
}
EDIT: Whoops, said unrelated when I meant related... not sure what my brain was doing there.
Just another Perl hooker - But the purist monks keep telling me I should do it for love, not money.
|