By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,436 Members | 2,979 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,436 IT Pros & Developers. It's quick & easy.

How to print the header only once in the new Excel

P: 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 :

Expand|Select|Wrap|Line Numbers
  1. #!/usr/local/bin/perl
  2.  
  3. use strict;
  4. use Spreadsheet::ParseExcel;
  5. use Spreadsheet::WriteExcel;
  6.  
  7. my %UniqueCell_Row = ();
  8. my $excel_name; 
  9.  
  10. $excel_name = "D:\\RAVINDRAN\\Excel-Email-sending\\name.xls";
  11. print "Excel Name -> <$excel_name> \n";
  12.  
  13. my $workbook = Spreadsheet::ParseExcel::Workbook->Parse($excel_name);
  14.  
  15. print "Book -> <$workbook> \n";
  16. my $cont_col;
  17. my $cell_value;
  18. my @cols;
  19.  
  20. ### To get the particular columns all the value
  21. foreach my $sheet (@{$workbook->{Worksheet}}) {
  22.     printf("Sheet: %s\n", $sheet->{Name});
  23.  
  24.     foreach my $col ($sheet->{MinCol} .. $sheet->{MaxCol}) {
  25.         print "Column -> <$col> \n";
  26.         print "$sheet->{Cells}[0][$col]->{Val} \n";
  27.         if ($sheet->{Cells}[0][$col]->{Val} eq "Dep") {
  28.             $cont_col = $col;
  29.             print "Column Number -> $cont_col\n";
  30.             last;
  31.         }
  32.     }
  33.  
  34.     for(
  35.         my $iR = $sheet->{MinRow};
  36.         defined $sheet->{MaxRow} && $iR <= $sheet->{MaxRow}; 
  37.         $iR++
  38.     ){
  39.  
  40.         # fetch columns
  41.         my $cell = $sheet->{Cells}[$iR][$cont_col];
  42.         print "sheet->{Cells}[$iR][$cont_col] \n";
  43.         print "cell->{Val} ----> <$cell->{Val}> \n";
  44.         $cell_value = $cell->{Val};
  45.         push(@cols,$cell_value);
  46.         #push ( @{$UniqueCell_Row{$cell_value}},"$row,$col,$cell_value" );
  47.     }
  48.  
  49.     print "Colllummmn : <@cols> \n";
  50. }
  51.  
  52. ## This is just to print all the columns value
  53. foreach my $sheet (@{$workbook->{Worksheet}}) {
  54.     printf("Sheet: %s\n", $sheet->{Name});
  55.     foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) {
  56.         foreach my $col ($sheet->{MinCol} ..  $sheet->{MaxCol}) {
  57.  
  58.             my $cell = $sheet->{Cells}[$row][$col];
  59.             my $cell_value = $cell->{Val};
  60.  
  61.             if ( $cell ) {
  62.                 printf("( %s , %s ) => %s\n", $row, $col, $cell->{Val});
  63.                 $UniqueCell_Row{$row}{$col} = $cell_value;
  64.             }
  65.             if ( $cell && $cell_value eq "Dep" ) {
  66.                 printf("Department Cell Value is : ( %s , %s ) => %s\n", $row, $col, $cell->{Val});
  67.                 #push ( @{$UniqueCell_Row{$cell_value}},"$row,$col,$cell_value" );
  68.             }
  69.         }
  70.     }
  71. }
  72.  
  73. ## This is just to get the unique record
  74. my @row_cont = ();
  75. foreach my $row ( sort keys %UniqueCell_Row ) {
  76.     foreach my $cell ( sort keys %{$UniqueCell_Row{$row}} ) {
  77.         print "Rows and Column -----> <$row> | <$cell> | $UniqueCell_Row{$row}{$cell} \n";
  78.         #if ( $UniqueCell_Row{$row}{$cont_col}
  79.  
  80.     }
  81. }
  82.  
  83. print "========================================= \n";
  84.  
  85. my @Dup;
  86. my %uniqISU = ();
  87. my $duplicate;
  88. my $unique;
  89.  
  90. foreach my $row ( sort keys %UniqueCell_Row ) {
  91.     foreach my $cell ( sort keys %{$UniqueCell_Row{$row}} ) {
  92.         print "Rows and Column -----> <$row> | <$cell> | $UniqueCell_Row{$row}{$cont_col} \n";
  93.         if ( grep( /$UniqueCell_Row{$row}{$cont_col}/, @Dup ) ) {
  94.             print "Present : <$UniqueCell_Row{$row}{$cont_col}> | <@cols> \n";
  95.             $duplicate = $UniqueCell_Row{$row}{$cont_col};
  96.             $uniqISU{$duplicate}{$row}{$cell} = $UniqueCell_Row{$row}{$cell};
  97.         }
  98.         else {
  99.             print "NOT PRESENT : <$UniqueCell_Row{$row}{$cont_col}> \n";
  100.             push @Dup, $UniqueCell_Row{$row}{$cont_col};
  101.             $duplicate = $UniqueCell_Row{$row}{$cont_col};
  102.             $uniqISU{$duplicate}{$row}{$cell} = $UniqueCell_Row{$row}{$cell};
  103.         }
  104.     }
  105. }
  106.  
  107. print "***************************************************** \n";
  108. foreach my $cell_val ( sort keys %uniqISU ) {
  109.     print "Cell Value ---> <$cell_val> \n";
  110.     foreach my $rows ( sort keys %{$uniqISU{$cell_val}} ) {
  111.         foreach my $cell ( sort keys %{$uniqISU{$cell_val}{$rows}} ) {
  112.             print "Cell_val -> <$cell_val> | rows -> <$rows> | columns -> <$cell> | Col Val -> <$uniqISU{$cell_val}{$rows}{$cell}> \n";
  113.         }
  114.     }
  115. }
  116.  
  117. my $format;
  118. my ($header1,$header2,$header3);
  119. my %header;
  120.  
  121. ### Creating an excel for each cell_val.
  122. foreach my $cell_val ( sort keys %uniqISU ) {
  123.     my $header = 1;
  124.     my $workbook;
  125.     my $sheet1;
  126.  
  127.     if ( $cell_val ne "Dep" ) {
  128.         $workbook = Spreadsheet::WriteExcel->new("$cell_val.xls");
  129.         $sheet1 = $workbook->add_worksheet("$cell_val");
  130.  
  131.         # Add a Format
  132.         $format = $workbook->add_format();
  133.         $format->set_bold();
  134.         $format->set_size(12);
  135.         $format->set_color('blue');
  136.         $format->set_align('center');
  137.  
  138.         #$sheet1->set_column(0, 0);
  139.     }
  140.  
  141.     my $i=0;
  142.     my $j=0;
  143.     my $y;
  144.     foreach my $rows ( sort keys %{$uniqISU{$cell_val}} ) {
  145.         $y = 0;
  146.         foreach my $cell ( sort keys %{$uniqISU{$cell_val}{$rows}} ) {
  147.  
  148.             if ( $header == 1 && $cell_val eq "Dep" ) {
  149.                 $header{$cell_val}{$rows}{$cell} = $uniqISU{$cell_val}{$rows}{$cell};
  150.             }
  151.             elsif ( $cell_val ne "Dep" ) {
  152.                 print "Y -> <$y> \n";
  153.                 $sheet1->write($header{$cell_val},$header{$cell_val}{$rows},$header{$cell_val}{$rows}{$cell}) if ( $header == 1 );
  154.                 $sheet1->repeat_rows(0);
  155.                 $sheet1->write($i, $y, $uniqISU{$cell_val}{$rows}{$cell});
  156.                 #$sheet1->write($rows, $cell, $uniqISU{$cell_val}{$rows}{$cell});
  157.             }
  158.             $header++;
  159.             $y++;
  160.         }
  161.         $i++;
  162.     }
  163. }
Nov 12 '08 #1
Share this Question
Share on Google+
2 Replies


P: 5
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
Nov 12 '08 #2

nithinpes
Expert 100+
P: 410
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.
Nov 12 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.