473,396 Members | 1,900 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,396 software developers and data experts.

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

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
12 4006
Icecrack
174 Expert 100+
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
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
410 Expert 256MB
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
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
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
No !!!! still didn't work...
still i get the same result :-(
Oct 1 '08 #7
Icecrack
174 Expert 100+
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
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
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
410 Expert 256MB
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
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
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

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

Similar topics

2
by: mike | last post by:
ok here's the problem: I have a page that displays a form for user to select individual fields and to specify their own criteria which is used to query a database and then create an excel...
11
by: Mr. Smith | last post by:
Hello all, My code can successfully open, write to, format and save several worksheets in a workbook then save it by a given name, close and quit excel. My problem is that if I try and do it...
2
by: shruti | last post by:
hiii all I'm tryin to call a perl script from a C program in following 2 ways- 1.By callin system function. But there's some problem because the system function is not able to executeany...
1
by: Shalako | last post by:
I check my error log and see these entries: malformed header from script. Bad header= Missing gauge reports are ind: padata.pl /perl/pema/padata.pl did not send an HTTP header malformed...
1
by: vijaymohan | last post by:
Hi ..I am very new to perl..can some one help me with this script pls.. I am Querying database and writing data to excel.. Here is my script: #!/usr/bin/perl -w use strict; use DBI;
0
by: shintu | last post by:
Hallo, I am trying to write french accented characters in Excel worksheet using my perl script , But I am stuck here as I couldnt find a way of writing it !: My code: use strict;...
1
by: pvenu | last post by:
Hi, I know basic perl (regular expressions, pattern matching, string manipulation, reading writing into text files). Yet, my requirement is to read an input text file -> process this input file...
3
by: Bob Murdoch | last post by:
I'm using the following to send a binary file to a user: Response.AddHeader('Content-Disposition','attachment;filename=' + Request('FileName') + ';'); var vType = 'application/octetstream';...
4
by: JRough | last post by:
I have this section at the end of a page ------------------- if ($_POST== 'Open in Excel'){ if (empty($data)) { $data = "\n(0) Records Found!\n";} header("Content-type:...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.