Hi all,

I am fairly new to Perl and am trying to accomplish the following tasks. I have an Excel workbook (say, workbook1.xls), which contains several worksheets (let’s say three – named sheet1, sheet2, sheet3). Each worksheet contains a different number of rows. I would like to:

1. loop through all sheets

2. Read all cells in the first column of each sheet

3. Write the value of the cells in the first column to some other column, with the same row. I do not want to “copy” the values, but rather to read them and then write them (which will give the same outcome, of course).

4. Save it to a new workbook (Workbook2.xls)

I have a code which I have modified based on some code I found on the web, but the problem is that while it reads off the different sheets, it only writes to the first sheet. Can anyone of you help me figure out what is wrong in the code, and how to fix it?

Thanks, Tzachi

use strict;
    use warnings;
    use Spreadsheet::ParseExcel;
    use Spreadsheet::ParseExcel::SaveParser;


    use Spreadsheet::ParseExcel;
    my $parser   = Spreadsheet::ParseExcel->new();
    my $workbook = $parser->parse("c:/workbook1.xls");
    my $workbook1 = $parser->parse("c:/workbook1.xls ");
    my @locations;
    
    if ( !defined $workbook ) {
        die $parser->error(), ".\n";
    }

    for my $worksheet ( $workbook->worksheets() ) {
        my $current_sheet = $worksheet->get_name();
        @locations=(); #empty the locations array before populating it
+ again with a new sheet;
        my ( $row_min, $row_max ) = $worksheet->row_range();
        my ( $col_min, $col_max ) = $worksheet->col_range();
#for my $row ( 1 .. $row_max ) {
        for my $row ( 0 .. $row_max ) {
            for my $col ( 0 ) {

                my $cell = $worksheet->get_cell( $row, $col );
                next unless $cell;
                               push(@locations,$worksheet->get_cell( $
+row, $col )->value());
                #print "Row, Col    = ($row, $col)\n";
                #print "Value       = ", $cell->value(),       "\n";
                #print "Unformatted = ", $cell->unformatted(), "\n";
                print "Sheet = $current_sheet \n";
                #print "\n";
                
                 my $parser   = new Spreadsheet::ParseExcel::SaveParse
+r;
                 my $template = $parser->Parse("("c:/workbook1.xls"");
                 my $worksheet= $template->worksheet(0);
                
                  # Get the format from the cell
                           my $format   = $template->{Worksheet}[0]
                            #my $format   = $worksheet
                            ->{Cells}[$row][5]
                            ->{FormatNo};
                 # Write data to some cells
                   $template->AddCell(0, $row,   5,   $locations[$row]
+,     $format);
                   $template->AddCell(0, $row, 6, "Helloooo", $format)
+;         
                   
                     
                    print "Row, Col    = ($row, $col)\n";
                    print "Value       = ", $cell->value(),       "\n"
+;
                    print "$locations[$row] \n";
                  my $workbook;
                  $workbook = $template->SaveAs("("c:/workbook1.xls"")
+;    
            }
        }   
    }
   
   use Spreadsheet::ParseExcel::SaveParser;
   use Spreadsheet::ParseExcel;
     #Add a new worksheet
     my $parser1   = new Spreadsheet::ParseExcel::SaveParser;
     my $template1 = $parser1->Parse("("c:/workbook1.xls"");
    $template1->AddWorksheet('New Data1');


    {
        # SaveAs generates a lot of harmless warnings about unset
        # Worksheet properties. You can ignore them if you wish.
        local $^W = 0;

        # Rewrite the file or save as a new file
        $workbook = $template1->SaveAs("("c:/workbook2.xls"");
    }

    # Use Spreadsheet::WriteExcel methods
    my $worksheet  = $workbook->sheets(0);

    #$worksheet->write($row+2, $col, "World2");

    $workbook->close();
[download]