lib/CXGN/Pedigree/ParseUpload/Plugin/StoreExistingProgeniesExcel.pm

   1 package CXGN::Pedigree::ParseUpload::Plugin::StoreExistingProgeniesExcel;
   2
   3 use Moose::Role;
   4 use Spreadsheet::ParseExcel;
   5 use Spreadsheet::ParseXLSX;
   6 use CXGN::Stock::StockLookup;
   7 use SGN::Model::Cvterm;
   8 use Data::Dumper;
   9 use CXGN::List::Validate;
  10 use CXGN::Stock::RelatedStocks;
  11
  12
  13 sub _validate_with_plugin {
  14     return 1; #storing after validation plugin
  15 }
  16
  17 sub _parse_with_plugin {
  18     my $self = shift;
  19     my $filename = $self->get_filename();
  20     my $schema = $self->get_chado_schema();
  21
  22     # Match a dot, extension .xls / .xlsx
  23     my ($extension) = $filename =~ /(\.[^.]+)$/;
  24     my $parser;
  25
  26     if ($extension eq '.xlsx') {
  27         $parser = Spreadsheet::ParseXLSX->new();
  28     }
  29     else {
  30         $parser = Spreadsheet::ParseExcel->new();
  31     }
  32
  33     my $excel_obj;
  34     my $worksheet;
  35
  36     $excel_obj = $parser->parse($filename);
  37     if (!$excel_obj){
  38         return;
  39     }
  40
  41     $worksheet = ($excel_obj->worksheets())[0];
  42     my ($row_min, $row_max) = $worksheet->row_range();
  43     my ($col_min, $col_max) = $worksheet->col_range();
  44
  45     my %cross_progenies_hash;
  46
  47     for my $row (1 .. $row_max){
  48         my $cross_name;
  49         my $progeny_name;
  50
  51         if ($worksheet->get_cell($row,0)){
  52             $cross_name = $worksheet->get_cell($row,0)->value();
  53             $cross_name =~ s/^\s+|\s+$//g;
  54         }
  55         if ($worksheet->get_cell($row,1)){
  56             $progeny_name = $worksheet->get_cell($row,1)->value();
  57             $progeny_name =~ s/^\s+|\s+$//g;
  58         }
  59         #skip blank lines or lines with no name, type and parent
  60         if (!$cross_name && !$progeny_name) {
  61             next;
  62         }
  63
  64         push @{$cross_progenies_hash{$cross_name}}, $progeny_name;
  65     }
  66     $self->_set_parsed_data(\%cross_progenies_hash);
  67     return 1;
  68 }
  69
  70 1;