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.