in reply to Merging Two Excel Files

A better title would be "Merge two excel files".

AFAIK, you get to be the first to solve this, good luck :) 

my $final =  Spreadsheet::WriteExcel->new('filename.xls');

for my $xls ( 'first.xls', 'second.xls' ){
    my $book = ...->read('second.xls');
    my $worksheet = $book->Worksheet(0);
    my $sheetf = $final->add_worksheet( $worksheet->{Name} );

    my ( $row_min, $row_max ) = $worksheet->row_range();
    my ( $col_min, $col_max ) = $worksheet->col_range();

    for my $row ( $row_min .. $row_max ) {
        for my $col ( $col_min .. $col_max ) {

            my $cell = $worksheet->get_cell( $row, $col );
            next unless $cell;

            $sheetf->write( $row , $col, $cell->unformatted  );
        }
    }
}

[download]
Of course you would want to take care of copying/mapping all the details, like Spreadsheet::ParseExcel::Format to Spreadsheet::WriteExcel::Format...

Replies are listed 'Best First'.
Re^2: Quickie Question Perl Excel
by longjohnsilver (Acolyte) on Feb 17, 2009 at 13:07 UTC
    Hi all you venerable perl folks,

    I used the above code snippet to merge the excel files and also added two lines of code so the worksheets got new names, but instead of parsing/writing the cell-format which seemed too time consuming to me i added a new format later on to the double-sheeted xls file. It works for me now, but i'll be getting back to the format copying issue. As for the executable xlsmerge posted on this thread i couldnt get it working, probably i'll have to take a closer look;

    Here's the slightly modified code: 

    for my $xls ( 'spreadsheet1.xls', 'spreadsheet2.xls' ){
    my $book = $oExcel->Parse($xls);
    my $worksheet = $book->{Worksheet}->[0]; 
    ## Give a name to both sheets
    if ($xls =~ /1/) {
        $namedsheet  = $final->add_worksheet('First Sheet'); 
    }
    elsif ($xls =~ /2/) {
        $namedsheet = $final->add_worksheet('Second Sheet'); 
    }
    my $sheetf = $namedsheet;
    my ( $row_min, $row_max ) = $worksheet->row_range();
    my ( $col_min, $col_max ) = $worksheet->col_range();

    for my $row ( $row_min .. $row_max ) {
        for my $col ( $col_min .. $col_max ) {
            my $cell = $worksheet->get_cell( $row, $col );
            next unless $cell; 
            $sheetf->write( $row , $col, $cell->unformatted );
        }
    }
}

    [download]


    Here some of the formatting snippets i used, hope they'll be insightful for someone 
    my $oExcel2 = new Spreadsheet::ParseExcel::SaveParser;
my $oBook = $oExcel2->Parse('double_sheet.xls');

# Font format
my $iF1 = $oBook->AddFont(
    Name      => 'Arial',
    Height    => 10,
    Bold      => 1, #Bold
    Italic    => 0, #Italic
    Underline => 0,
    Strikeout => 0,
    Super     => 0,
    Wrap      => 1 
);

# Cell Format
my $iFmt = $oBook->AddFormat(
    Font        => $oBook->{Font}[$iF1],
    AlignH      => 0,
    Fill        => [0, 43, 0],     # Filled with yellow 
    BdrStyle    => [2, 2, 2, 2],   # Border Right, Top
    BdrColor    => [0, 0, 0, 0],   # Right->Green
);

# Rewrite the cells with the new format
$oBook->AddCell(0, 0, 0, 'TEXT1', $iFmt);
$oBook->AddCell(0, 0, 1, 'TEXT2', $iFmt);
$oBook->AddCell(0, 0, 2, 'TEXT3', $iFmt);

# modify the column width
$oBook->{Worksheet}->[0]->{ColWidth}->[0]=14;
$oBook->{Worksheet}->[0]->{ColWidth}->[1]=14;
$oBook->{Worksheet}->[0]->{ColWidth}->[2]=33;

# Modify the column format based on column header

our $sheet;
our $col;
our $row;
our $header1_col;
our $header2_col;
our $header3_col;
our $number_1;
our $number_2;
our $date_1;
our $a = 0;



foreach $sheet (@{$oBook->{Worksheet}}) {
   foreach $col ($sheet->{MinCol} .. $sheet->{MaxCol}) {
       if ($sheet->{Cells}[0][$col]->{Val} eq "HEADER1") {
           $header1 = $col;
       }
       if ($sheet->{Cells}[0][$col]->{Val} eq "HEADER2") {
           $header2 = $col;
       }
       if ($sheet->{Cells}[0][$col]->{Val} eq "HEADER3") {
           $header3 = $col;
       }
   }
   foreach $row ($sheet->{MinRow}+1 .. $sheet->{MaxRow}) {
       $number_1 = $sheet->{Cells}[$row][$header1]->{Val};

       ### A way to convert comma separated numbers into excel  number
+s .. maybe there's a better way :/

       my $convert_1 = $number_1;
       $convert_1 =~ s/,/./;
       $oBook->AddCell($a, $row,$header1, $convert_1, $iFmtNum); 

       $number_2 = $sheet->{Cells}[$row][$header2]->{Val};
       ## Again a number conversion comma to dot

       my $convert_2 = $number_2;
       $convert_2 =~ s/,/./;
       $oBook->AddCell($a, $row,$header2, $convert_2, $iFmtNum);

       $date_1 = $sheet->{Cells}[$row][$header3]->{Val};

       ## A date conversion i needed to display it properly 
 on excel

       my $convert_date = $date_1;
       $convert_date =~ s/-0/-200/;
       $oBook->AddCell($a, $row,$header3, $convert_date, $iFmtNum);
   }
   $a = $a + 1;
}

    [download]

    It works now but i'm trying to make this code more elegant..

    Thx again for all your help,

    F
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom