[Bio] / FigKernelScripts / read_write_excel.pl Repository:
ViewVC logotype

View of /FigKernelScripts/read_write_excel.pl

Parent Directory Parent Directory | Revision Log Revision Log

Revision 1.1 - (download) (as text) (annotate)
Wed May 24 20:12:14 2006 UTC (13 years, 5 months ago) by overbeek
Branch: MAIN
CVS Tags: mgrast_dev_08112011, rast_rel_2009_05_18, mgrast_dev_08022011, rast_rel_2014_0912, rast_rel_2008_06_18, myrast_rel40, rast_rel_2008_06_16, mgrast_dev_05262011, rast_rel_2008_12_18, mgrast_dev_04082011, rast_rel_2008_07_21, rast_rel_2010_0928, rast_2008_0924, mgrast_version_3_2, mgrast_dev_12152011, rast_rel_2008_04_23, mgrast_dev_06072011, rast_rel_2008_09_30, rast_rel_2009_0925, rast_rel_2010_0526, rast_rel_2014_0729, mgrast_dev_02212011, rast_rel_2010_1206, mgrast_release_3_0, mgrast_dev_03252011, rast_rel_2010_0118, mgrast_rel_2008_0924, mgrast_rel_2008_1110_v2, rast_rel_2009_02_05, rast_rel_2011_0119, mgrast_rel_2008_0625, mgrast_release_3_0_4, mgrast_release_3_0_2, mgrast_release_3_0_3, mgrast_release_3_0_1, mgrast_dev_03312011, mgrast_release_3_1_2, mgrast_release_3_1_1, mgrast_release_3_1_0, mgrast_dev_04132011, rast_rel_2008_10_09, mgrast_dev_04012011, rast_release_2008_09_29, mgrast_rel_2008_0806, mgrast_rel_2008_0923, mgrast_rel_2008_0919, rast_rel_2009_07_09, rast_rel_2010_0827, mgrast_rel_2008_1110, myrast_33, rast_rel_2011_0928, rast_rel_2008_09_29, mgrast_rel_2008_0917, rast_rel_2008_10_29, mgrast_dev_04052011, mgrast_dev_02222011, rast_rel_2009_03_26, mgrast_dev_10262011, rast_rel_2008_11_24, rast_rel_2008_08_07, HEAD
RAE: Initial release of a simple script to read and write excel files to allow easy parsing of data from annotators



A simple script that reads and writes excel files. This is a wrapper that I should probably turn into a module somehow. However, at the moment you can just add your own modifications where shown below.

There are lots of occasions where we need to get data out of excel files, process the data, and then write it back to an excel file. 

This does most of that for you, you juse need to add code to do the modifications at the place I noted below. 

=head2 notes

1. The modules Spreadsheet::ParseExcel  and Spreadsheet::WriteExcel are not currently part of the SEED distribution (but will be soon) and are located in /home/seed/Rob/perl

2. The cell indexing is zero based, so you can look for a specific cell.

3. At the moment you can only work on one worksheet at a time, but it is easy to drag things between sheets.


use strict;
use lib '/home/seed/Rob/perl/lib/perl5/site_perl/5.8.5/';
use Spreadsheet::ParseExcel;
use Spreadsheet::WriteExcel;

my ($wanted_sheet, $file, $outf);
while (@ARGV)
    my $t=shift @ARGV;
    if ($t eq "-s") {$wanted_sheet=shift @ARGV}
    elsif ($t eq "-f") {$file=shift @ARGV}
    elsif ($t eq "-o") {$outf=shift @ARGV}

my $usage = <<EOF;
-f excel file
-o file to write
-s sheet to work on

die $usage unless ($wanted_sheet && $file && $outf);

my $excelobj=new Spreadsheet::ParseExcel;
my $parse=Spreadsheet::ParseExcel::Workbook->Parse($file);
my $write=Spreadsheet::WriteExcel->new($outf);
my $write_sheet=$write->add_worksheet($wanted_sheet);

# these two global variables are used for storing color and format data that is preserved accross the workbook.
my %allformats; my %colorcode;

foreach my $sheet ($parse->Worksheet($wanted_sheet))
    print STDERR "Working on ", $sheet->{Name}, "\n";
    my ($minrow, $maxrow)=$sheet->RowRange;
    my ($mincol, $maxcol)=$sheet->ColRange;
    print STDERR "Rows are $minrow to $maxrow and cols are $mincol to $maxcol\n";
    foreach my $x ($minrow .. $maxrow)
        foreach my $y ($mincol .. $maxcol)
            my $cell=$sheet->Cell($x, $y);
            next unless ($cell);
            my $format=reformat($cell);
            my $value=$cell->Value;
            ############# MODIFY HERE
            # $value contains the value of this cell, and it will be written out here. If you want to modify it do so here.
            # If you want to add more things to the worksheet, add another write method like this one below, 
            # $x and $y are the 0-based offsets of the cells.
            # $format is the color etc, of the cell, and is not required if you make a separate method
            $write_sheet->write($x, $y, $value, $format);


=head1 reformat

A take a cell and return the code for the format. 

use: my $format=reformat($cell)

The two modules do not read/write excel formats the same way (D'Oh) and so this routine converts between the two.


sub reformat {
    my ($cell)=@_;
    my ($fill, $front, $back)=@{$cell->{Format}->{Fill}};
    my $bold=$cell->{Format}->{Font}->{Bold};
    my $font=$cell->{Format}->{Font}->{Name};
    my $font_color=$cell->{Format}->{Font}->{Color};
    my ($ls, $rs, $ts, $bs)=@{$cell->{Format}->{BdrStyle}};
    my ($lc, $rc, $tc, $bc)=@{$cell->{Format}->{BdrColor}};
# Create a unique index for each format.
    my $conc;
    foreach my $c ($fill,$front,$back,$bold,$font, $font_color,$ls, $rs, $ts, $bs, $lc, $rc, $rc, $bc) {$conc.=$c if (defined $c)}

    unless ($allformats{$conc})
        my %add;

        # define the new formating objects here. Note that with colors you need to get the rgb code and 
        # add it to the worksheet. Yuck
        (defined $front)         && ($add{-bg_color}=add_custom_color($front));
        (defined $fill)         && ($add{-pattern}=$fill);
        (defined $bold)         && ($add{-bold}=$bold);
        (defined $font_color)   && ($add{-color}=add_custom_color($font_color));
        (defined $font)         && ($add{-font}=$font);

        (defined $bs)         && ($add{-bottom}=$bs);
        (defined $ts)         && ($add{-top}=$ts);
        (defined $ls)         && ($add{-left}=$ls);
        (defined $rs)         && ($add{-right}=$rs);
        (defined $bc)         && ($add{-bottom_color}=add_custom_color($bc));
        (defined $tc)         && ($add{-top_color}=add_custom_color($tc));
        (defined $lc)         && ($add{-left_color}=add_custom_color($lc));
        (defined $rc)         && ($add{-right_color}=add_custom_color($rc));


    return $allformats{$conc};

=head1 add_custom_color

We have to redfine the excel colors all the time, since they are not standard. Drat.

This just keeps track of what is assigned where. Pass in an rgb, and get back the color code for it.

use: my $color=add_custom_color($rgb);


sub add_custom_color {
    my $col=shift;
    my $rgb=$excelobj->ColorIdxToRGB($col);
    if (length($rgb) == 6) {$rgb="#".$rgb}
    unless ($colorcode{$rgb})
        my $lastcolor=19;
        foreach my $k (keys %colorcode) {($colorcode{$k} > $lastcolor) ? ($lastcolor=$colorcode{$k}) : 1}
        $colorcode{$rgb}=$write->set_custom_color($lastcolor, $rgb);
    return $colorcode{$rgb};

MCS Webmaster
ViewVC Help
Powered by ViewVC 1.0.3