Sara has asked for the wisdom of the Perl Monks concerning the following question:

Hi all, I am using SpreadSheet::WriteExcel, and was wonduring if there is a function under that module to do loop. the problem I am runnig through is: my first worksheet contains a lot of datas like in sheet1 I have the following : 
  FileName      ErrorType
  one.cxx          588
  one.cxx          588
  one.cxx          588
  two.cxx          333
  three.cxx        588
  four.cxx         666
  four.cxx         666
  five.cxx         588
  six.cxx          666

[download]
and I want in my sheet2 to have a summary like that 
  fileName          Error    588     333       666
one.cxx                       3  
two.cxx                               1  
three.cxx                     1
four.cxx                                        2         
.
.
.

[download]

where # 3 under the the error 588 indicating , how many times , that error occured fo the file one.cxx.

so basically , I am scaning the second column in sheet1 and counting the occurence of each error and display how many times it occures for each file in sheet2. Is there a trick to do the loop using the module, if not can someone give me a hint in writing that excel formula :(.

Thanks

Edited: ~Fri Jun 28 19:39:15 2002 (GMT), by footpad:
Added <CODE> and <P> formatting tags.

Replies are listed 'Best First'.
Re: perl and excel
by Aristotle (Chancellor) on Jun 28, 2002 at 19:22 UTC

    First of all, please read the Writeup Formatting Tips - your post prior to editing was almost illegible and made your intent nearly impossible to discern. Now that we have covered that,

    The Perl way of doing this would be a hash of hashes: 
    my %error_per_file;
my %error_type;

while(my ($filename, $errnum) = get_row_elements($some,$params,$here))
+ {
    $error_type{$errnum}++;
    $error_per_file{$filename}->{$errnum}++;
}

    [download]

    Substitute get_row_elements() with whatever delivers your colums. You then have all filenames as keys of the %error_per_file hash. The value will be a reference to a hash, whose keys in turn are the error numbers, and the values are the number of occurences of that error.

    If all of that looks very strange, I suggest reading the perlreftut tutorial and perlref documentation to understand what's going on here.

    Writing them back is a bit complicated:

    my $column = 1;
my %column_for = map ($_ => $column++), sort keys %error_type;
$column_for{Filename} = 0;

    [download]
    Now we have a hash whose keys are column headers and whose values are the corresponding column numbers. $worksheet->write(0, $column_for{$col_name}, $col_name) for keys %column_for; Here we have put the elements in their corresponding columns, at row 0 of the worksheet. Finally, we populate the rest of the rows, one at a time: 
    my $row = 1;
for my $filename (sort keys %error_per_file){
    $worksheet->write($row, 0, $filename)
    my ($errnum, $occurences);
    $worksheet->write($row, $column_for{$errnum}, $occurences)
        while ($errnum, $occurences) = each %{$error_per_file{$filenam
+e}};
    ++$row;
}

    [download]
    ____________
    Makeshifts last the longest.
[reply]
[d/l]
[select]
Re: perl and excel
by thor (Priest) on Jun 29, 2002 at 00:30 UTC
    looks awfully similar to what someone posted here...;)

    thor

[reply]

Back to Seekers of Perl Wisdom