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]

In reply to Reading and writing to Excel with multiple worksheets by Tzachi

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.