perl - Using Spreadsheet::WriteExcel -

i want extract information 1 excel sheet , re-format one. data structure looks like:

      col1      col2 row1  school    1 row2  dean      john row3  no.stu.   55 row4  irrelevant stuff row5  school2   2 row6  dean      tony row7  no. stu.  60 row8  irrelevant stuff row9  school    3 row10 dean      james row11 no.stu.   56 row12 no. teacher 20 

the output achieve is:

      col1 col2 col3 row1 school dean no.stu.  no. teacher row2 1      john  55 row3 2      tony  60 row4 3      james 56       20 

and code have been advised use extract information excel following (thanks hdb perlmonks).

use strict; use warnings;  use spreadsheet::parseexcel;  ($infile) = @argv;  $parser   = spreadsheet::parseexcel->new(); $workbook = $parser->parse($infile); die $parser->error unless defined $workbook; ($worksheet) = $workbook->worksheets();  %data; # accumulate data here $row = 0; $school = 0; while (1) {     $cell = $worksheet->get_cell($row, 0);     last unless defined($cell);     $key =  $cell->value();     $data = $worksheet->get_cell($row++, 1)->value();     if( $key eq "school" ) {         $school = $data;     }     else {         $data{$school}{$key} = $data;     } } 

now next step re-write new excel sheet. how that?

your code bit broken. here mine:

use strict; use warnings;  use spreadsheet::parseexcel; use findbin qw($bin);  ($infile) = @argv;  $parser   = spreadsheet::parseexcel->new(); $workbook = $parser->parse("$bin/test.xls"); die $parser->error unless defined $workbook; ($worksheet) = $workbook->worksheets();  %data; $row    = 0; $school = ""; while (1) {     $cell = $worksheet->get_cell( $row, 0 );     last unless defined($cell);      $key = $cell->value();     $value = $worksheet->get_cell( $row++, 1 )->value();      if ( $key eq "school" ) {          $school = $value;         next;     }      $data{$school}->{$key} = $value; } sleep 1;  use spreadsheet::writeexcel;  $workbook = spreadsheet::writeexcel->new('result.xls');  $worksheet = $workbook->add_worksheet(); $col = 0; $row = 0;  $worksheet->write( $row++, $col,     [ "school", "dean", "no.stu.", "no. teacher" ] );  foreach $school ( sort keys %data ) {      $worksheet->write( $row++, $col,         [ $school, @{ $data{$school} }{ "dean", "no.stu.", "no. teacher" } ] ); } $workbook->close();