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

   1 package CXGN::Pedigree::ParseUpload::Plugin::FamilyNameExcel;
   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
  11 sub _validate_with_plugin {
  12     my $self = shift;
  13     my $filename = $self->get_filename();
  14     my $schema = $self->get_chado_schema();
  15     my @error_messages;
  16     my %errors;
  17
  18     # Match a dot, extension .xls / .xlsx
  19     my ($extension) = $filename =~ /(\.[^.]+)$/;
  20     my $parser;
  21
  22     if ($extension eq '.xlsx') {
  23         $parser = Spreadsheet::ParseXLSX->new();
  24     }
  25     else {
  26         $parser = Spreadsheet::ParseExcel->new();
  27     }
  28
  29     my $excel_obj;
  30     my $worksheet;
  31
  32     #try to open the excel file and report any errors
  33     $excel_obj = $parser->parse($filename);
  34     if (!$excel_obj){
  35         push @error_messages, $parser->error();
  36         $errors{'error_messages'} = \@error_messages;
  37         $self->_set_parse_errors(\%errors);
  38         return;
  39     }
  40
  41     $worksheet = ($excel_obj->worksheets())[0]; #support only one worksheet
  42     if (!$worksheet){
  43         push @error_messages, "Spreadsheet must be on 1st tab in Excel (.xls) file";
  44         $errors{'error_messages'} = \@error_messages;
  45         $self->_set_parse_errors(\%errors);
  46         return;
  47     }
  48
  49     my ($row_min, $row_max) = $worksheet->row_range();
  50     my ($col_min, $col_max) = $worksheet->col_range();
  51     if (($col_max - $col_min)  < 1 || ($row_max - $row_min) < 1 ) { #must have header and at least one row of family name
  52         push @error_messages, "Spreadsheet is missing header or no family name data";
  53         $errors{'error_messages'} = \@error_messages;
  54         $self->_set_parse_errors(\%errors);
  55         return;
  56     }
  57
  58     #get column headers
  59     my $cross_name_head;
  60     my $family_name_head;
  61
  62     if ($worksheet->get_cell(0,0)) {
  63         $cross_name_head  = $worksheet->get_cell(0,0)->value();
  64         $cross_name_head =~ s/^\s+|\s+$//g;
  65     }
  66
  67     if ($worksheet->get_cell(0,1)) {
  68         $family_name_head  = $worksheet->get_cell(0,1)->value();
  69         $family_name_head =~ s/^\s+|\s+$//g;
  70     }
  71
  72     if (!$cross_name_head || $cross_name_head ne 'cross_unique_id' ) {
  73         push @error_messages, "Cell A1: cross_unique_id is missing from the header";
  74     }
  75
  76     if (!$family_name_head || $family_name_head ne 'family_name' ) {
  77         push @error_messages, "Cell A2: family_name is missing from the header";
  78     }
  79
  80     my %seen_cross_names;
  81
  82     for my $row (1 .. $row_max){
  83         my $row_name = $row+1;
  84         my $cross_name;
  85         my $family_name;
  86
  87         if ($worksheet->get_cell($row,0)) {
  88             $cross_name = $worksheet->get_cell($row,0)->value();
  89         }
  90         if ($worksheet->get_cell($row,1)) {
  91             $family_name = $worksheet->get_cell($row,1)->value();
  92         }
  93
  94         if (!$cross_name || $cross_name eq '') {
  95             push @error_messages, "Cell A$row_name: cross unique id missing";
  96         } else {
  97             $cross_name =~ s/^\s+|\s+$//g;
  98             $seen_cross_names{$cross_name}++;
  99         }
 100
 101         if (!$family_name || $family_name eq '') {
 102             push @error_messages, "Cell B$row_name: family name missing";
 103         }
 104     }
 105
 106
 107     my @crosses = keys %seen_cross_names;
 108     my $cross_validator = CXGN::List::Validate->new();
 109     my @crosses_missing = @{$cross_validator->validate($schema,'crosses',\@crosses)->{'missing'}};
 110
 111     if (scalar(@crosses_missing) > 0){
 112         push @error_messages, "The following cross unique ids are not in the database as uniquenames or synonyms: ".join(',',@crosses_missing);
 113     }
 114
 115     #store any errors found in the parsed file to parse_errors accessor
 116     if (scalar(@error_messages) >= 1) {
 117         $errors{'error_messages'} = \@error_messages;
 118         $self->_set_parse_errors(\%errors);
 119         return;
 120     }
 121
 122     return 1; #returns true if validation is passed
 123
 124 }
 125
 126
 127 sub _parse_with_plugin {
 128     my $self = shift;
 129     my $filename = $self->get_filename();
 130     my $schema = $self->get_chado_schema();
 131
 132     # Match a dot, extension .xls / .xlsx
 133     my ($extension) = $filename =~ /(\.[^.]+)$/;
 134     my $parser;
 135
 136     if ($extension eq '.xlsx') {
 137         $parser = Spreadsheet::ParseXLSX->new();
 138     }
 139     else {
 140         $parser = Spreadsheet::ParseExcel->new();
 141     }
 142
 143     my $excel_obj;
 144     my $worksheet;
 145
 146     $excel_obj = $parser->parse($filename);
 147     if (!$excel_obj){
 148         return;
 149     }
 150
 151     $worksheet = ($excel_obj->worksheets())[0];
 152     my ($row_min, $row_max) = $worksheet->row_range();
 153     my ($col_min, $col_max) = $worksheet->col_range();
 154
 155     my %cross_family_name;
 156
 157     for my $row (1 .. $row_max){
 158         my $cross_name;
 159         my $family_name;
 160
 161         if ($worksheet->get_cell($row,0)){
 162             $cross_name = $worksheet->get_cell($row,0)->value();
 163             $cross_name =~ s/^\s+|\s+$//g;
 164         }
 165
 166         if ($worksheet->get_cell($row,1)){
 167             $family_name = $worksheet->get_cell($row,1)->value();
 168             $family_name =~ s/^\s+|\s+$//g;
 169         }
 170
 171         #skip blank lines or lines with no cross name
 172         if (!$cross_name) {
 173             next;
 174         }
 175
 176         $cross_family_name{$cross_name} = $family_name;
 177
 178     }
 179
 180     $self->_set_parsed_data(\%cross_family_name);
 181
 182     return 1;
 183 }
 184
 185 1;