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

writing the header content only once in excel using perl ???

P: 65
Hi All,

To Moderator :
Sorry i have posted this querry to member rather putting in the forum.

To All:

Problem stmt:

I am reading the data from the excel files & writing its content to another excel file.problem is that everytime it reads data it writes all the content.
i want the script to write the header only once.

output look like this:
Expand|Select|Wrap|Line Numbers
  1. DD/MM/YYYY    HH:MM:SS    r/s    w/s    kr/s    kw/s
  2. 18/01/2008    18:00:00    0.5    2.9    15    31.5
  3. 18/01/2008    18:00:00    5    0    0    0
  4.  
  5. DD/MM/YYYY    HH:MM:SS    r/s    w/s    kr/s    kw/s
  6. 18/01/2008    18:00:00    0.5    1.9    13    30.5
  7. 18/01/2008    18:00:00    2    0    0    0
  8.  
  9. DD/MM/YYYY    HH:MM:SS    r/s    w/s    kr/s    kw/s
  10. 18/01/2008    18:00:00    0.5    3.9    11    32.5
  11. 18/01/2008    18:00:00    0    0    1    0
  12.  
in the above output data, i need script to write header only once.

script goes like this :
Expand|Select|Wrap|Line Numbers
  1. # Build an array of the stats type to be collated
  2. #my @stat_type=split(/:/, $PARAMS{COLLATE_STAT_TYPES});
  3.     @stat_type=@uniq;
  4.   # for each of the stats type, read the xls of each hour and write into a consolidated xls.
  5.   foreach my $stat_type_token (@stat_type){
  6.  
  7.             my $con_wb = Spreadsheet::WriteExcel::Big->new("$PARAMS{INPUT_FILE_BASE_PATH}$stat_type_token.xls");
  8.             my $con_excel = $con_wb->add_worksheet();
  9.  
  10.             my $con_row = 0;
  11.  
  12.                 my @stat_files=split(/:/, $PARAMS{COLLATE_YYYYMMDD_HH});
  13.  
  14.                   foreach my $stat_files_token (@stat_files) {
  15.  
  16.  
  17.                       my $source_xl_name = $PARAMS{INPUT_FILE_BASE_PATH}.$stat_type_token."-".$stat_files_token.".xls";
  18.  
  19.  
  20.                     my $source_excel = new Spreadsheet::ParseExcel;
  21.                     my $source_book = $source_excel->Parse($source_xl_name) or die "Could not open source Excel file $source_xl_name !";
  22.  
  23.                     my $source_sheet = $source_book->{Worksheet}[0];
  24.                     print "Processing $stat_type_token-$stat_files_token.xls please wait\n";
  25.                     foreach my $row_index ($source_sheet->{MinRow} .. $source_sheet->{MaxRow}) {
  26.  
  27.                               my $con_col = 0;
  28.  
  29.                               foreach my $col_index ($source_sheet->{MinCol} .. $source_sheet->{MaxCol}) {
  30.  
  31.                                        my $source_cell = $source_sheet->{Cells}[$row_index][$col_index];
  32.  
  33.  
  34.                                                if (defined $source_cell) {
  35.                                                   $con_excel->write($con_row, $con_col, $source_cell->Value );
  36.                                                }
  37.  
  38.  
  39.  
  40.                                         $con_col++;
  41.  
  42.                               }
  43.  
  44.                              $con_row++; 
  45.                              $consol_rows++;
  46.  
  47.                              # max row count in Excel is 65536 rows.
  48.                              # open a new sheet if the max row is reached.
  49.                              if ( $consol_rows > 65000 ) {
  50.  
  51.                                            $consol_rows = 0;
  52.                                            $con_row = 0;
  53.                                            $con_col = 0;
  54.                                            $con_excel = $con_wb->add_worksheet();
  55.                                        }
  56.  
  57.  
  58.                     }
  59.  
  60.         }    
  61.  
  62.       $con_wb->close();
  63.  
  64.  
  65.   }
  66.  
Can anyone help me on this ???

Sorry again kevin,jeff & moderator !!!!

Regards,
Vijayarl
Sep 30 '08 #1
Share this Question
Share on Google+
12 Replies


Icecrack
Expert 100+
P: 174
try this, this code is untested.

but this should be what you want...


Replace:

Expand|Select|Wrap|Line Numbers
  1.  if (defined $source_cell) {
  2.                                                   $con_excel->write($con_row, $con_col, $source_cell->Value );
  3.                                                }
  4.  

With:

Expand|Select|Wrap|Line Numbers
  1.  $a=1;
  2.  if (defined $source_cell) {
  3.  
  4.  if (($source_cell->Value eq "DD/MM/YYYY") 
  5.  or ($source_cell->Value eq "HH:MM:SS") 
  6.  or ($source_cell->Value eq "r/s") 
  7.  or ($source_cell->Value eq "w/s") 
  8.  or ($source_cell->Value eq "kr/s") 
  9.  or ($source_cell->Value eq "kw/s"))
  10. {
  11. if ($a < 6)
  12. {
  13.     $con_excel->write($con_row, $con_col, $source_cell->Value );
  14.     $a++;
  15.  
  16. }
  17. }
  18. else
  19. {
  20. $con_excel->write($con_row, $con_col, $source_cell->Value );
  21. }  
  22. }  
Oct 1 '08 #2

P: 65
Thanks Icecrack for giving a try...

i have followed what u said but still am getting the same result ie, multiple headers in output file.

hey can we do like this, everytime script writes the content check for the first line content in the output file.if it exists then skip the line else append the data in the excel file.
can we do this approach ??? fi so how to do that in the script ???

Regards,
vijayarl
Oct 1 '08 #3

nithinpes
Expert 100+
P: 410
Thanks Icecrack for giving a try...

hey can we do like this, everytime script writes the content check for the first line content in the output file.if it exists then skip the line else append the data in the excel file.
can we do this approach ??? fi so how to do that in the script ???

Regards,
vijayarl
We can do that. For this, replace the following portion of script:
Expand|Select|Wrap|Line Numbers
  1. if (defined $source_cell) { 
  2.                  $con_excel->write($con_row, $con_col, $source_cell->Value ); 
  3.                                         } 
  4.  
with:

Expand|Select|Wrap|Line Numbers
  1.  my $head_cell = $source_sheet->{Cells}[0][$col_index];
  2.   if (defined $source_cell) { 
  3. unless(($source_cell->Value  eq $head_cell -> Value) && ($source_cell ne $head_cell) ) {
  4.               $con_excel->write($con_row, $con_col, $source_cell->Value ) ; 
  5.                                                } } 
  6.  
Oct 1 '08 #4

P: 65
hi nithinpes !!!,

I try what u have said, it works fine if we have only one <stat>.xls file.

bu the problem is there will be a more than 1 <stat_type>.xls file, the above code what i have pasted will collect all the data from the <stat_type>.xls & writes them in one<stat_type>.xls file.

i mean i will be having these *.xls file:
Expand|Select|Wrap|Line Numbers
  1. iostat-xn-20080118-1800.xls
  2. iostat-xn-20080118-1900.xls
  3. iostat-xn-20080118-2000.xls
  4. netstat-i-20080118-1800.xls
  5. netstat-i-20080118-1900.xls
  6. prstat-Ls-20080118-1800.xls
  7. prstat-Ls-20080118-1900.xls
  8. prstat-Lvs-20080118-2000.xls
  9. prstat-Lvs-20080118-2000.xls
  10.  
Each of these file will have header, while reading these file, everytime script writes the header in the output file.

i will get the output file as , (after reading each of the stat_type.xls):
Expand|Select|Wrap|Line Numbers
  1. iostat-xn.xls
  2. netstat-i.xls
  3. prstat-Ls.xls
  4. prstat-Lvs.xls
  5.  
All i want is write header only once in each of the output file...

can we do this ???

Thanks
Vijayarl
Oct 1 '08 #5

P: 4
try like this
Expand|Select|Wrap|Line Numbers
  1.  if (defined $source_cell) {
  2.  if (!($source_cell->Value =~ /^DD/))   ### newly added
  3.   { ### newly added 
  4.  $con_excel->write($con_row, $con_col, $source_cell->Value );
  5.   } ### newly added
  6.  }
  7.  
Oct 1 '08 #6

P: 65
No !!!! still didn't work...
still i get the same result :-(
Oct 1 '08 #7

Icecrack
Expert 100+
P: 174
try like this

Expand|Select|Wrap|Line Numbers
  1.  if (defined $source_cell) {
  2.  if (!($source_cell->Value =~ /^DD/))   ### newly added
  3.   { ### newly added 
  4.  $con_excel->write($con_row, $con_col, $source_cell->Value );
  5.   } ### newly added
}

please use code tags,
Oct 1 '08 #8

P: 65
Hi All,

I thought of doing like this now, just delete the first line(column) from the excel file then write the content in one excel file.

ie, if i have 8 stat excel file
1. open the 2nd file, delete the 1st column repeat the same for other 6 files.
2. start wiritng the content in 1 excel files.

but my problem is how to delete the 1st column (line) in excel file using perl ??
if i get this i guess i can achive other things .......


Thanks,
Vijayarl
Oct 1 '08 #9

P: 65
Hi Everyone,

Going forward a step ahead... i was able to wirte the contents but without any header in the output file.

Expand|Select|Wrap|Line Numbers
  1. # Build an array of the stats type to be collated
  2. #my @stat_type=split(/:/, $PARAMS{COLLATE_STAT_TYPES});
  3.     @stat_type=@uniq;
  4.   # for each of the stats type, read the xls of each hour and write into a consolidated xls.
  5.   foreach my $stat_type_token (@stat_type){
  6.  
  7.             my $con_wb = Spreadsheet::WriteExcel::Big->new("$PARAMS{INPUT_FILE_BASE_PATH}$stat_type_token.xls");
  8.             my $con_excel = $con_wb->add_worksheet();
  9.             my $con_row = 0;
  10.             my @stat_files=split(/:/, $PARAMS{COLLATE_YYYYMMDD_HH});
  11.             my $first_file="yes";
  12.                   foreach my $stat_files_token (@stat_files) {
  13.                       my $source_xl_name = $PARAMS{INPUT_FILE_BASE_PATH}.$stat_type_token."-".$stat_files_token.".xls";
  14.                     my $source_excel = new Spreadsheet::ParseExcel;
  15.                     my $source_book = $source_excel->Parse($source_xl_name) or die "Could not open source Excel file $source_xl_name !";
  16.                     my $source_sheet = $source_book->{Worksheet}[0];
  17.                     print "Processing $stat_type_token-$stat_files_token.xls please wait\n";
  18.                     foreach my $row_index ($source_sheet->{MinRow} .. $source_sheet->{MaxRow}) {
  19.                         next if $row_index eq (($source_sheet->{MinRow}) && ($first_file eq "no"));
  20.                         ####$first_file="no";
  21.                               my $con_col = 0;
  22.                               foreach my $col_index ($source_sheet->{MinCol} .. $source_sheet->{MaxCol}) {
  23.                                 #next if $row_index eq (($source_sheet->{MinRow}) && ($first_file eq "no"));
  24.                                        my $source_cell = $source_sheet->{Cells}[$row_index][$col_index];
  25.                                                #if (defined $source_cell) {    
  26.                                             # if (($source_cell->Value =~ /^DD/)) 
  27.                                             #     { 
  28.                                             #         $con_excel->write($con_row, $con_col, $source_cell->Value );
  29.                                             #     }
  30.                                                  ###$con_excel->write($con_row, $con_col, $source_cell->Value );
  31.                                                #}
  32.                                        my $head_cell = $source_sheet->{Cells}[0][$col_index]; 
  33.                                     if (defined $source_cell) {
  34.                                     #print "$source_cell\n";
  35.                                     ###unless(!($source_cell->Value  eq $head_cell -> Value) && ($source_cell ne $head_cell)) { 
  36.                                     ###unless(($source_cell->Value eq $head_cell -> Value) && ($source_cell ne $head_cell)) { 
  37.                                     $con_excel->write($con_row, $con_col, $source_cell->Value ) ;  
  38.                                         } 
  39.                                     #} 
  40.                                         $con_col++;    
  41.                               }
  42.                              $con_row++; 
  43.                              $consol_rows++;
  44.                              # max row count in Excel is 65536 rows.
  45.                              # open a new sheet if the max row is reached.
  46.                              if ( $consol_rows > 65000 ) {
  47.                                            $consol_rows = 0;
  48.                                            $con_row = 0;
  49.                                            $con_col = 0;
  50.                                            $con_excel = $con_wb->add_worksheet();
  51.                                        }
  52.                             $first_file="no";
  53.  
  54.                     } 
  55.         }    
  56.       $con_wb->close();
  57.   }
  58.   print "Processing Done. Time to analyse\n";
  59.  
all i did is, introduce a flag variable, to check for the 2nd file name & then used next if statment to skip the header content.

Expand|Select|Wrap|Line Numbers
  1. next if $row_index eq (($source_sheet->{MinRow}) && ($first_file eq "no"));
  2.  
now what should i change in the script, so that i will get the output file having only once header content.

any help plz !!!!!

Regards,
Vijayarl
Oct 3 '08 #10

nithinpes
Expert 100+
P: 410
The logic is perfectly right, but for the line where you modify the value of $first_file. This should be after the first iteration of row index, at the end of foreach my $row_index() block

Expand|Select|Wrap|Line Numbers
  1.  
  2. foreach my $row_index ($source_sheet->{MinRow} .. $source_sheet->{MaxRow})  {
  3. .
  4. .
  5. .  foreach my $col_index(....) {
  6. .
  7. .
  8. ;  }
  9.  
  10. $first_file = "no";
  11.    }
  12.  
Oct 3 '08 #11

P: 65
hi nithinpes,

i tried what u said..but still i get the output file without header content..

Expand|Select|Wrap|Line Numbers
  1. foreach my $row_index ($source_sheet->{MinRow} .. $source_sheet->{MaxRow}) {
  2.                         next if $row_index eq (($source_sheet->{MinRow}) && ($first_file ne "no"));
  3.                               my $con_col = 0;
  4.                               foreach my $col_index ($source_sheet->{MinCol} .. $source_sheet->{MaxCol}) {
  5.                                        my $source_cell = $source_sheet->{Cells}[$row_index][$col_index];
  6.                                        my $head_cell = $source_sheet->{Cells}[0][$col_index]; 
  7.                                     if (defined $source_cell) {
  8.                                     unless(($source_cell->Value ne $head_cell -> Value) && ($source_cell eq $head_cell)) { 
  9.                                     $con_excel->write($con_row, $con_col, $source_cell->Value );
  10.                                         } 
  11.                                     } 
  12.                                         $con_col++;
  13.                                     $first_file="no";
  14.                               }
  15.  
even tried placing the $first_file variable outside the forloop
Expand|Select|Wrap|Line Numbers
  1. foreach my $row_index ($source_sheet->{MinRow} .. $source_sheet->{MaxRow}) {
  2.                         next if $row_index eq (($source_sheet->{MinRow}) && ($first_file ne "no"));
  3.                               my $con_col = 0;
  4.                               foreach my $col_index ($source_sheet->{MinCol} .. $source_sheet->{MaxCol}) {
  5.                                        my $source_cell = $source_sheet->{Cells}[$row_index][$col_index];
  6.                                        my $head_cell = $source_sheet->{Cells}[0][$col_index]; 
  7.                                     if (defined $source_cell) {
  8.                                     unless(($source_cell->Value ne $head_cell -> Value) && ($source_cell eq $head_cell)) { 
  9.                                     $con_excel->write($con_row, $con_col, $source_cell->Value );
  10.                                         } 
  11.                                     } 
  12.                                         $con_col++;
  13.                               }
  14. $first_file="no";
  15.  
but i got the same result :-(
don't knw where am doing mistake ??? not getting any idea ... look like am blocked...

plz help me..

Regards,
Vijayarl
Oct 3 '08 #12

P: 65
Hi All,

Thanks for your patience reply.....

At last i got the desired result..

Thanks to sycoogtit for his reply...

Thanks,
Vijayarl

Working Code:
Expand|Select|Wrap|Line Numbers
  1. # Build an array of the stats type to be collated
  2. #my @stat_type=split(/:/, $PARAMS{COLLATE_STAT_TYPES});
  3. @stat_type=@uniq;
  4. # for each of the stats type, read the xls of each hour and write into a consolidated xls.
  5.  
  6. foreach my $stat_type_token (@stat_type){
  7.  
  8.     my $con_wb = Spreadsheet::WriteExcel::Big->new("$PARAMS{INPUT_FILE_BASE_PATH}$stat_type_token.xls");
  9.     my $con_excel = $con_wb->add_worksheet();
  10.     my $con_row = 0;
  11.     my @stat_files=split(/:/, $PARAMS{COLLATE_YYYYMMDD_HH});
  12.     my $first_file="yes";
  13.  
  14.     foreach my $stat_files_token (@stat_files) {
  15.  
  16.         my $source_xl_name = $PARAMS{INPUT_FILE_BASE_PATH}.$stat_type_token."-".$stat_files_token.".xls";
  17.         my $source_excel = new Spreadsheet::ParseExcel;
  18.         my $source_book = $source_excel->Parse($source_xl_name) or die "Could not open source Excel file $source_xl_name !";
  19.         my $source_sheet = $source_book->{Worksheet}[0];
  20.         print "Processing $stat_type_token-$stat_files_token.xls please wait\n";
  21.  
  22.         foreach my $row_index ($source_sheet->{MinRow} .. $source_sheet->{MaxRow}){
  23.  
  24.             #next if $row_index eq (($source_sheet->{MinRow}) && ($first_file ne "no"));
  25.             next if $row_index == $source_sheet->{MinRow} && $first_file eq "no";
  26.             my $con_col = 0;
  27.  
  28.             foreach my $col_index ($source_sheet->{MinCol} .. $source_sheet->{MaxCol}){
  29.  
  30.                 my $source_cell = $source_sheet->{Cells}[$row_index][$col_index];
  31.                 my $head_cell = $source_sheet->{Cells}[$row_index][$col_index];
  32.                 if (defined $source_cell) {
  33.                     $con_excel->write($con_row, $con_col, $source_cell->Value );
  34.                 }
  35.                 $con_col++;    
  36.             }
  37.             $con_row++;
  38.             $consol_rows++;
  39.             # max row count in Excel is 65536 rows.
  40.             # open a new sheet if the max row is reached.
  41.             if ( $consol_rows > 65000 ) {
  42.                 $consol_rows = 0;
  43.                 $con_row = 0;
  44.                 $con_col = 0;
  45.                 $con_excel = $con_wb->add_worksheet();
  46.             }
  47.         }
  48.         $first_file="no";
  49.     }
  50.     $con_wb->close();
  51. }
  52. &write_output;
  53. #&call_sendmail;
  54. print "Processing Done. Time to analyse\n";
  55.  
Oct 7 '08 #13

Post your reply

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