From cbeb59c5bd39ed62a4d3b1e7dc29656f7ce970a2 Mon Sep 17 00:00:00 2001 From: nickmorales Date: Fri, 8 Jul 2016 20:47:16 +0200 Subject: [PATCH] phenotype spreadsheet creation with handling of predef cols --- lib/CXGN/Trial/Download/Plugin/ExcelBasic.pm | 52 ++++++++++++++++++---------- 1 file changed, 33 insertions(+), 19 deletions(-) diff --git a/lib/CXGN/Trial/Download/Plugin/ExcelBasic.pm b/lib/CXGN/Trial/Download/Plugin/ExcelBasic.pm index 4c33702bd..2e3ba10e5 100644 --- a/lib/CXGN/Trial/Download/Plugin/ExcelBasic.pm +++ b/lib/CXGN/Trial/Download/Plugin/ExcelBasic.pm @@ -1,6 +1,7 @@ package CXGN::Trial::Download::Plugin::ExcelBasic; use Moose::Role; +use JSON; sub verify { my $self = shift; @@ -34,12 +35,22 @@ sub download { my $bold = $workbook->add_format(); $bold->set_bold(); + my $json = JSON->new(); + my @predefined_columns; + foreach (keys %{$self->predefined_columns}) { + if ($self->predefined_columns->{$_}) { + push @predefined_columns, $_; + } + } + my $predefined_columns_json = $json->encode(\@predefined_columns); + my $trial = CXGN::Trial->new( { bcs_schema => $schema, trial_id => $trial_id }); $ws->write(0, 0, 'Spreadsheet ID'); $ws->write('0', '1', 'ID'.$$.time()); $ws->write(0, 2, 'Spreadsheet format'); $ws->write(0, 3, "BasicExcel"); $ws->write(1, 0, 'Trial name'); $ws->write(1, 1, $trial->get_name(), $bold); $ws->write(2, 0, 'Description'); $ws->write(2, 1, $trial->get_description(), $bold); $ws->write(3, 0, "Trial location"); $ws->write(3, 1, $trial->get_location()->[1], $bold); + $ws->write(4, 0, "Predefined Columns"); $ws->write(4, 1, $predefined_columns_json, $bold); $ws->write(1, 2, 'Operator'); $ws->write(1, 3, "Enter operator here"); $ws->write(2, 2, 'Date'); $ws->write(2, 3, "Enter date here"); $ws->data_validation(2,3, { validate => "date", criteria => '>', value=>'1000-01-01' }); @@ -50,44 +61,45 @@ sub download { $num_col_before_traits = 6; my @column_headers = qw | plot_name accession_name plot_number block_number is_a_control rep_number |; for(my $n=0; $n<@column_headers; $n++) { - $ws->write(5, $n, $column_headers[$n]); + $ws->write(6, $n, $column_headers[$n]); } my @ordered_plots = sort { $a <=> $b} keys(%design); for(my $n=0; $n<@ordered_plots; $n++) { my %design_info = %{$design{$ordered_plots[$n]}}; - $ws->write($n+6, 0, $design_info{plot_name}); - $ws->write($n+6, 1, $design_info{accession_name}); - $ws->write($n+6, 2, $design_info{plot_number}); - $ws->write($n+6, 3, $design_info{block_number}); - $ws->write($n+6, 4, $design_info{is_a_control}); - $ws->write($n+6, 5, $design_info{rep_number}); + $ws->write($n+7, 0, $design_info{plot_name}); + $ws->write($n+7, 1, $design_info{accession_name}); + $ws->write($n+7, 2, $design_info{plot_number}); + $ws->write($n+7, 3, $design_info{block_number}); + $ws->write($n+7, 4, $design_info{is_a_control}); + $ws->write($n+7, 5, $design_info{rep_number}); } } elsif ($self->data_level eq 'plants') { - + $num_col_before_traits = 7; my $pre_col = $self->predefined_columns; if ($pre_col) { my $num_predefined_col = scalar keys %$pre_col; - $num_col_before_traits = 7 + $num_predefined_col; my @column_headers = qw | plant_name plot_name accession_name plot_number block_number is_a_control rep_number |; foreach (keys %$pre_col) { - push @column_headers, $_; + if ($pre_col->{$_}) { + push @column_headers, $_; + $num_col_before_traits++; + } } for(my $n=0; $nwrite(5, $n, $column_headers[$n]); + $ws->write(6, $n, $column_headers[$n]); } } else { - $num_col_before_traits = 7; my @column_headers = qw | plant_name plot_name accession_name plot_number block_number is_a_control rep_number |; for(my $n=0; $n<@column_headers; $n++) { - $ws->write(5, $n, $column_headers[$n]); + $ws->write(6, $n, $column_headers[$n]); } } my @ordered_plots = sort { $a <=> $b} keys(%design); - my $line = 6; + my $line = 7; for(my $n=0; $n<@ordered_plots; $n++) { my %design_info = %{$design{$ordered_plots[$n]}}; my $plant_names = $design_info{plant_names}; @@ -118,8 +130,10 @@ sub download { if ($pre_col) { my $pre_col_ind = 7; foreach (keys %$pre_col) { - $ws->write($line, $pre_col_ind, $pre_col->{$_}); - $pre_col_ind++; + if ($pre_col->{$_}) { + $ws->write($line, $pre_col_ind, $pre_col->{$_}); + $pre_col_ind++; + } } } @@ -149,7 +163,7 @@ sub download { for (my $i = 0; $i < @trait_list; $i++) { #if (exists($cvinfo{$trait_list[$i]})) { #$ws->write(5, $i+6, $cvinfo{$trait_list[$i]}->display_name()); - $ws->write(5, $i+$num_col_before_traits, $trait_list[$i]); + $ws->write(6, $i+$num_col_before_traits, $trait_list[$i]); #} #else { # print STDERR "Skipping output of trait $trait_list[$i] because it does not exist\n"; @@ -162,10 +176,10 @@ sub download { if ($cvinfo{$trait_list[$i]}) { my $format = $cvinfo{$trait_list[$i]}->format(); if ($format eq "numeric") { - $ws->data_validation($n+6, $i+$num_col_before_traits, { validate => "any" }); + $ws->data_validation($n+7, $i+$num_col_before_traits, { validate => "any" }); } elsif ($format =~ /\,/) { # is a list - $ws->data_validation($n+6, $i+$num_col_before_traits, { + $ws->data_validation($n+7, $i+$num_col_before_traits, { validate => 'list', value => [ split ",", $format ] }); -- 2.11.4.GIT