BioMart::Formatter XLS
SummaryIncluded librariesPackage variablesSynopsisDescriptionGeneral documentationMethods
Toolbar
WebCvsRaw content
Summary
BioMart::Formatter::XLS
Package variables
No package variables defined.
Included modules
Digest::SHA qw ( sha256_base64 )
Readonly
Spreadsheet::WriteExcel
Spreadsheet::WriteExcel::Big
Inherit
BioMart::FormatterI
Synopsis
The XLS Formatter returns XLS format data
for a BioMart querys ResultTable
Description
When given a BioMart::ResultTable containing the results of
a BioMart::Query the XLS Formatter will return XLS file, open
in the browser if it supports xls mime type.
Methods
_new
No description
Code
closeWorkBook
No description
Code
getDisplayNames
No description
Code
getFileType
No description
Code
getMimeType
No description
Code
getXlsString
No description
Code
isBinary
No description
Code
printResults
No description
Code
processQuery
No description
Code
Methods description
None available.
Methods code
_newdescriptionprevnextTop
sub _new {
    my ($self) = @_;
    $self->SUPER::_new();
}
closeWorkBookdescriptionprevnextTop
sub closeWorkBook {
	my $self = shift;
	$self->get('workbook')->close;
}
getDisplayNamesdescriptionprevnextTop
sub getDisplayNames {
	return undef;
}
getFileTypedescriptionprevnextTop
sub getFileType {
    return 'xls';
}
getMimeTypedescriptionprevnextTop
sub getMimeType {
    return 'application/vnd.ms-excel';
}
getXlsStringdescriptionprevnextTop
sub getXlsString {
    my $self = shift;
    return $self->get('xlsString');
}
isBinarydescriptionprevnextTop
sub isBinary {
    return 1;
}

1;
}
printResultsdescriptionprevnextTop
sub printResults {
	my ($self, $filehandle, $lines, $uniqueResults) = @_;
	
	# on web26 the filehandle was getting lost/corrupted so best to create from scratch - not necessary if using 
# just WriteExcel for some reason. HOWEVER this fix broke all other setups so revert - to be investigated
# in the future if it appears anywhere else
# $filehandle = IO::File->new();
# my $tie = ref tied( *STDOUT );# always 'Apache' even for gz on web26 - hence don't get zipped output
# tie *$filehandle, $tie;
# binmode($filehandle);
my $workbook = Spreadsheet::WriteExcel::Big->new($filehandle); #---------------------------------------- worksheet properties
#binmode(STDOUT);
#my $workbook = Spreadsheet::WriteExcel::Big->new("/homes/syed/Desktop/temp9/biomart-web/lib/BioMart/Formatter/perl.xls");
#my $workbook = Spreadsheet::WriteExcel::Big->new(\*STDOUT);
#tie *XLS => $r; # Tie to the Apache::RequestRec object
#binmode(*XLS);
#my $workbook = Spreadsheet::WriteExcel::Big->new(\*XLS);
# Add a worksheet
my $worksheet = $workbook->add_worksheet(); $self->attr('workbook', $workbook); $self->attr('worksheet', $worksheet); $self->attr('rows', 0); $self->attr('columns', 0); my $format = $self->get('workbook')->addformat(); $format->set_align('left'); $format->set_bg_color('white'); $format->set_color('black'); $self->attr('format', $format); $self->attr('colWidth', undef); #-------------------------------------------------------------
my @displayNames = $self->getTextDisplayNames(); my $formatDisplay = $self->get('workbook')->addformat(); $formatDisplay->set_bold(); $formatDisplay->set_align('centre'); $formatDisplay->set_bg_color('black'); $formatDisplay->set_color('white'); my $width; foreach(@displayNames) { $width->{$self->get('columns')} = length($_) * 1.2 ; $self->get('worksheet')->set_column($self->get('columns'), $self->get('columns'), $width->{$self->get('columns')}); $self->get('worksheet')->write($self->get('rows'), $self->get('columns'), $_, $formatDisplay); $self->set('columns', $self->get('columns') + 1); } $self->set('rows', $self->get('rows') + 1); $self->get('worksheet')->freeze_panes(1, 0); # Freeze the first row
$self->set('colWidth', $width); #------------------------------------------------------------------------------------
my $new_row; my $new_row_contents; my $rtable = $self->get('result_table'); my $counter = 0; my $row; my %collisions; while ($rtable->hasMoreRows) { $self->set('columns',0); $new_row = (); $row = $rtable->nextRow; if ($uniqueResults) { no warnings 'uninitialized'; my $hash = sha256_base64("@{$row}"); next if exists $collisions{$hash}; $collisions{$hash} = undef; } $counter++; if (!$row || ($lines && $counter > $lines)) { $self->closeWorkBook; return; } map { $_ ||= q{}; } @$row; my $attribute_positions = $self->get('attribute_positions'); my $attribute_url_positions = $self->get('attribute_url_positions'); my $attribute_url = $self->get('attribute_url'); for (my $i = 0; $i < @{$attribute_positions}; $i++) { if ($$attribute_url[$i]) { my @url_data = map {$$row[$_]} @{$$attribute_url_positions[$i]}; my $url_string = sprintf($$attribute_url[$i],@url_data); #push @{$new_row}, $url_string, $$row[$$attribute_positions[$i]];
$new_row_contents->{value} = $$row[$$attribute_positions[$i]]; $new_row_contents->{URL} = $url_string; } else { $new_row_contents->{value} = $$row[$$attribute_positions[$i]]; $new_row_contents->{URL} = undef; } push @{$new_row}, $new_row_contents; $new_row_contents = (); } #------------------------------------------------------------------------------
# Enclose non-numeric values in double quotes & escape the quotes already in them
foreach(@{$new_row}) { if($self->get('colWidth')->{$self->get('columns')} < length($_->{value}) ) { if ( length($_->{value}) > 200) { $self->get('colWidth')->{$self->get('columns')} = 200; # maximum column display length
$self->get('worksheet')->set_column($self->get('columns'), $self->get('columns'), 200); } else { $self->get('colWidth')->{$self->get('columns')} = length($_->{value}); $self->get('worksheet')->set_column($self->get('columns'), $self->get('columns'), length($_->{value})); } } if($_->{URL}) { ## tab below with $_->{value} is prepended to handle hyperlinks on numeric values.
$self->get('worksheet')->write_url($self->get('rows'), $self->get('columns'), $_->{URL}, $_->{value} ); # , $self->get('format'));
} else { $self->get('worksheet')->write($self->get('rows'), $self->get('columns'), $_->{value} ); # , $self->get('format'));
} $self->set('columns', $self->get('columns') + 1); } #------------------------------------------------------------------------------
$self->set('rows', $self->get('rows') + 1); } $self->closeWorkBook; return;
}
processQuerydescriptionprevnextTop
sub processQuery {
    my ($self, $query) = @_;
    $self->set('original_attributes',[@{$query->getAllAttributes()}]) 
	if ($query->getAllAttributes());
    $query = $self->setHTMLAttributes($query);
    $self->set('query',$query);
    return $query;
}
General documentation
AUTHOR - Syed Haider, Damian SmedleyTop
CONTACTTop
This module is part of the BioMart project
http://www.biomart.org
Questions can be posted to the mart-dev mailing list: mart-dev@ebi.ac.uk