Toolbar

Summary

BioMart::Formatter::XLS

Package variables

Included modules

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

Methods description

None available.

Methods code

sub _new {

    my ($self) = @_;
    $self->SUPER::_new();

}

sub closeWorkBook {

	my $self = shift;
	$self->get('workbook')->close;

}

sub getDisplayNames {

	return undef;

}

sub getFileType {

    return 'xls';

}

sub getMimeType {

    return 'application/vnd.ms-excel';

}

sub getXlsString {

    my $self = shift;
    return $self->get('xlsString');

}

sub isBinary {

    return 1;
}

1;

}

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;

}

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

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