473,379 Members | 1,216 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

home > topics > perl > questions > writing the header content only once in excel using perl ???

Join Bytes to post your question to a community of 473,379 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 4004
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
header problem - want to display html AND download a file
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...
PHP
11
Auto writing Excel spreadsheets from Access (97 & 2K) fails to releaseExcel
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...
Microsoft Access / VBA
2
problem including perl header files
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...
C / C++
1
Malformed Header from script. Bad header.
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...
Apache Web Server
1
Writing data to excel..using DB query..pls help
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;
Perl
0
Writing French Accented Characters in Excel using Perl.
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;...
Perl
1
Invoke PERL from unix/linux, process and writing o/p data Excel sheet (Spread sheet)
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...
Perl
3
Content-Length header ignored?
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';...
ASP / Active Server Pages
4
cannot modify header info, headers already sent
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:...
PHP
1
Cloud Servers without Credit Card and Email Registration: A Simpler Way to Get on the Cloud
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
General
0
Wordpress or something else?
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
Content Management Systems
0
Easy Steps to Fix "Canon Printer Won't Connect to WiFi Network"
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
General
0
How to turn on java script in a villaon keypad mobile phone
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
Java
0
Batch import of multiple excel files into the database
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
Data Management
0
Merging data from multiple Excel files
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...
Data Management
0
Migrating Website to Cloud - Emmanuel Katto
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
General
1
Looking to do Android software development, any suggestions? Is flutter better?
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
General
0
How to build RAID in BIOS?
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...
Computer Hardware

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.

BYTES.COM © 2024
About Bytes
Terms Of Use
Privacy Policy
Sitemap
Advertise on Bytes:
Post a Job
Sponsored Posts
Platinum & Gold Sponsors
Hire Now!