You write a program to do this. Such a program will use Spreadsheet::ParseExcel (or Spreadsheet::Read) and Spreadsheet::WriteExcel. If I find time, I'll update this post with a reference to a program xlsmerge (or did I call it xlscat), which combines Sheets from several Workbooks into a new Workbook.

Update: See xlsmerge - Merge worksheets from different Excel files into one Workbook

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]

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

Update: Ok, I see now it wasn't a coincidence. Don't mind me.

First of all i'm thanking you all, and secondly at the moment i'm trying to mess with the translation of the parseexcel format into the writeexcel one. I'm still not sure about which code skeleton to use. I'll be posting the results. Have yourself a nice weekend.

Thx again,

F