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

In reply to Re^2: Quickie Question Perl Excel by longjohnsilver
in thread Merging Two Excel Files by longjohnsilver

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.