| Newbie | | Join Date: Nov 2008
Posts: 5
| |
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++;
-
}
-
}
|
