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


# !/usr/bin/perl -w

use warnings;
use strict;
use Data::Dump qw[ pp ];
use Spreadsheet::XLSX;
use Spreadsheet::ParseXLSX;
my %DivisionHash;
my $inputFile;
my $rowCity;
my $cellCity;
my $prejob;

$prejob = shift;
$inputFile = shift;
$rowCity = shift;
$cellCity = shift;

open INPUT, "<$inputFile" or die "Unable to open input file: $inputFil
+e !!!\nExiting...";
open INPUT2, "<SeedList.L.txt" or die "Unable to open input file: Seed
+List.L.txt !!!\nExiting...";
open OUTPUT, ">$prejob.SeedList.L.txt" or die "Unable to create/overwr
+ite output file:  !!!\nExiting...";


while (<INPUT2>)
   {print OUTPUT $_;}
close INPUT2;


   while (<INPUT>)
   {
   chomp;
   my $division = uc(trimspaces(substr($_,207-1,20)));
 # my $DivisionHash = substr($_,207-1,20);
     if (not exists $DivisionHash{$division}){
        $DivisionHash{$division} = $_;
      # print STDOUT $DivisionHash{$division};
       }# exit;
    }
close INPUT;

 my $filename = "\\Aldi_Production_Emad.xlsx";
# Parse excel file
 my $parser = Spreadsheet::ParseXLSX->new;

 my $workbook = $parser->parse("$filename");

if ( !defined $workbook ) {
        die $parser->error(), ".\n";
    }
# Get cell value from excel sheet1 row 1 column 2


my $worksheet = $workbook->worksheet(0);

my $cell = $worksheet->get_cell($rowCity,$cellCity);


# Print the cell value when not blank
if ( defined $cell and $cell->value() ne "") {
    my $value = $cell->value();
    $value = uc(trimspaces($value));
    
 # print STDOUT "this is value: $value";
# exit;
# if($DivisionHash eq $value){
      # print OUTPUT $_ . "\n";
# }
    if(exists ($DivisionHash{$value})) {
    # print STDOUT $DivisionHash{$value};
    # exit;
         # $_ = $DivisionHash{$value};
        
          print OUTPUT $DivisionHash{$value} . "\n";
         }
        else {
        print STDERR "Value $value Doesn't exist \n";
        exit;
        }
    }


close OUTPUT;


sub trimspaces {
  my @argsarray = @_;
  $argsarray[0] =~ s/^\s+//;
  $argsarray[0] =~ s/\s+$//;
  return $argsarray[0];
}

[download]

I worked on the code and fixed it, thanks for your help guys

Replies are listed 'Best First'.
Re: loop through xlsx and get specific column cell values
by Athanasius (Archbishop) on Apr 17, 2016 at 03:17 UTC

    Hello emadmahou,

    I’m not at all clear on what you are trying to do. It would be a great help if you could give (small!) sample files, one each for $inputFile and “test.xlsx”, together with the output (in “$inputFile.seed.txt”) you want to generate.

    Here are some preliminary observations:

    1. The variable %DivisionHash is declared but never used.
    2. Since neither the name of the Excel file nor its contents change during the loop, there is no need to re-parse it on every loop iteration. Parse it once, before the loop, and access the parse information in the loop as needed.
    3. Since your values are strings, you should compare then using eq, not ==: if ($DivisionHash eq $value). (ww stole my thunder on this one!)
    4. The commented-out sections of your code seem to be leftover from an earlier design. By all means keep them for reference (in a separate file, say), but it will help you to “see” your code more clearly if you remove them from the current script.
    5. Likewise, there are places where the comments don’t match the code: 
      # Get cell value from excel sheet1 row 1 column 2
for my $worksheet ( $workbook->worksheets() ) {

      [download]
      and unexplained magic numbers: 
      my $cell = $worksheet->get_cell(1,9);

      [download]
      Cleaning up the code, and documenting what it currently does, will help you to clarify its structure and logic.

    Hope that helps,

    Athanasius <°(((>< contra mundum Iustus alius egestas vitae, eros Piratica,

[reply]
[d/l]
[select]
      ;-) I didn't steal it; I merely flashed on it...
      ... and in doing so, missed the critical (en)lightening of points 1,2,4 and 5.
          Well done and + +

      Spirit of the Monastery

[reply]
Re: loop through xlsx and get specific column cell values
by ww (Archbishop) on Apr 17, 2016 at 03:08 UTC
    "the values I am comparing are city names not numeric "

    So why, in line 62, are you testing with == instead of eq?


    Spirit of the Monastery


[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom