How to print the header only once in the new Excel

[ravir81]

Hi,

I am working on excel reporting using Perl. I am facing problem with writing the header part only once for all the excels created using Perl.

Here is the code :

#!/usr/local/bin/perl
 
use strict;
use Spreadsheet::ParseExcel;
use Spreadsheet::WriteExcel;
 
my %UniqueCell_Row = ();
my $excel_name; 
 
$excel_name = "D:\\RAVINDRAN\\Excel-Email-sending\\name.xls";
print "Excel Name -> <$excel_name> \n";
 
my $workbook = Spreadsheet::ParseExcel::Workbook->Parse($excel_name);
 
print "Book -> <$workbook> \n";
my $cont_col;
my $cell_value;
my @cols;
 
### To get the particular columns all the value
foreach my $sheet (@{$workbook->{Worksheet}}) {
    printf("Sheet: %s\n", $sheet->{Name});
 
    foreach my $col ($sheet->{MinCol} .. $sheet->{MaxCol}) {
        print "Column -> <$col> \n";
        print "$sheet->{Cells}[0][$col]->{Val} \n";
        if ($sheet->{Cells}[0][$col]->{Val} eq "Dep") {
            $cont_col = $col;
            print "Column Number -> $cont_col\n";
            last;
        }
    }
 
    for(
        my $iR = $sheet->{MinRow};
        defined $sheet->{MaxRow} && $iR <= $sheet->{MaxRow}; 
        $iR++
    ){
 
        # fetch columns
        my $cell = $sheet->{Cells}[$iR][$cont_col];
        print "sheet->{Cells}[$iR][$cont_col] \n";
        print "cell->{Val} ----> <$cell->{Val}> \n";
        $cell_value = $cell->{Val};
        push(@cols,$cell_value);
        #push ( @{$UniqueCell_Row{$cell_value}},"$row,$col,$cell_value" );
    }
 
    print "Colllummmn : <@cols> \n";
}
 
## This is just to print all the columns value
foreach my $sheet (@{$workbook->{Worksheet}}) {
    printf("Sheet: %s\n", $sheet->{Name});
    foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) {
        foreach my $col ($sheet->{MinCol} ..  $sheet->{MaxCol}) {
 
            my $cell = $sheet->{Cells}[$row][$col];
            my $cell_value = $cell->{Val};
 
            if ( $cell ) {
                printf("( %s , %s ) => %s\n", $row, $col, $cell->{Val});
                $UniqueCell_Row{$row}{$col} = $cell_value;
            }
            if ( $cell && $cell_value eq "Dep" ) {
                printf("Department Cell Value is : ( %s , %s ) => %s\n", $row, $col, $cell->{Val});
                #push ( @{$UniqueCell_Row{$cell_value}},"$row,$col,$cell_value" );
            }
        }
    }
}
 
## This is just to get the unique record
my @row_cont = ();
foreach my $row ( sort keys %UniqueCell_Row ) {
    foreach my $cell ( sort keys %{$UniqueCell_Row{$row}} ) {
        print "Rows and Column -----> <$row> | <$cell> | $UniqueCell_Row{$row}{$cell} \n";
        #if ( $UniqueCell_Row{$row}{$cont_col}
 
    }
}
 
print "========================================= \n";
 
my @Dup;
my %uniqISU = ();
my $duplicate;
my $unique;
 
foreach my $row ( sort keys %UniqueCell_Row ) {
    foreach my $cell ( sort keys %{$UniqueCell_Row{$row}} ) {
        print "Rows and Column -----> <$row> | <$cell> | $UniqueCell_Row{$row}{$cont_col} \n";
        if ( grep( /$UniqueCell_Row{$row}{$cont_col}/, @Dup ) ) {
            print "Present : <$UniqueCell_Row{$row}{$cont_col}> | <@cols> \n";
            $duplicate = $UniqueCell_Row{$row}{$cont_col};
            $uniqISU{$duplicate}{$row}{$cell} = $UniqueCell_Row{$row}{$cell};
        }
        else {
            print "NOT PRESENT : <$UniqueCell_Row{$row}{$cont_col}> \n";
            push @Dup, $UniqueCell_Row{$row}{$cont_col};
            $duplicate = $UniqueCell_Row{$row}{$cont_col};
            $uniqISU{$duplicate}{$row}{$cell} = $UniqueCell_Row{$row}{$cell};
        }
    }
}
 
print "***************************************************** \n";
foreach my $cell_val ( sort keys %uniqISU ) {
    print "Cell Value ---> <$cell_val> \n";
    foreach my $rows ( sort keys %{$uniqISU{$cell_val}} ) {
        foreach my $cell ( sort keys %{$uniqISU{$cell_val}{$rows}} ) {
            print "Cell_val -> <$cell_val> | rows -> <$rows> | columns -> <$cell> | Col Val -> <$uniqISU{$cell_val}{$rows}{$cell}> \n";
        }
    }
}
 
my $format;
my ($header1,$header2,$header3);
my %header;
 
### Creating an excel for each cell_val.
foreach my $cell_val ( sort keys %uniqISU ) {
    my $header = 1;
    my $workbook;
    my $sheet1;
 
    if ( $cell_val ne "Dep" ) {
        $workbook = Spreadsheet::WriteExcel->new("$cell_val.xls");
        $sheet1 = $workbook->add_worksheet("$cell_val");
 
        # Add a Format
        $format = $workbook->add_format();
        $format->set_bold();
        $format->set_size(12);
        $format->set_color('blue');
        $format->set_align('center');
 
        #$sheet1->set_column(0, 0);
    }
 
    my $i=0;
    my $j=0;
    my $y;
    foreach my $rows ( sort keys %{$uniqISU{$cell_val}} ) {
        $y = 0;
        foreach my $cell ( sort keys %{$uniqISU{$cell_val}{$rows}} ) {
 
            if ( $header == 1 && $cell_val eq "Dep" ) {
                $header{$cell_val}{$rows}{$cell} = $uniqISU{$cell_val}{$rows}{$cell};
            }
            elsif ( $cell_val ne "Dep" ) {
                print "Y -> <$y> \n";
                $sheet1->write($header{$cell_val},$header{$cell_val}{$rows},$header{$cell_val}{$rows}{$cell}) if ( $header == 1 );
                $sheet1->repeat_rows(0);
                $sheet1->write($i, $y, $uniqISU{$cell_val}{$rows}{$cell});
                #$sheet1->write($rows, $cell, $uniqISU{$cell_val}{$rows}{$cell});
            }
            $header++;
            $y++;
        }
        $i++;
    }
}

[ravir81]

Could anyone please tell me how to get the header only once for all the excel created using the cell_val.

Thanks in Advance.
Ravi

[nithinpes]

Could anyone please tell me how to get the header only once for all the excel created using the cell_val.

Thanks in Advance.
Ravi

You may look into this similar thread.