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

Problem faced while creating Excel file !!!!

P: 65
Hi Everyone,

Am trying to collate the individual xls files into a single xls file.i have written this code.
Expand|Select|Wrap|Line Numbers
  1. use strict;
  2. use Spreadsheet::WriteExcel;
  3. use Spreadsheet::ParseExcel;
  4. use Spreadsheet::WriteExcel::Big;
  5.  
  6. my $consol_rows=0;
  7. my $token;
  8. my $file_name;
  9.  
  10. my $config_file = "C:\\Perl_Tools_ICON_PROD_SVAP_2_15\\config.txt";
  11.  
  12. my %PARAMS;
  13. my $CONFIG = "";
  14. my $MMSS = "";
  15.  
  16. # parse the configuration text file entries into PARAMS associative array
  17. open (CONFIGFILE, "$config_file") or die;
  18.  
  19. while (<CONFIGFILE>) {    
  20.     chomp;
  21.     if($_!~ /QUIT_TXT/ ){
  22.  
  23.             if ( ! ( $_ =~ /^#/ )) {
  24.                $CONFIG = $CONFIG . $_ . " ";
  25.  
  26.         }
  27.     }
  28.     else{
  29.         last;        
  30.         }
  31.  
  32.     }
  33. close (CONFIGFILE);
  34.  
  35. %PARAMS = split(/\s+/, $CONFIG);
  36.  
  37. # Build an array of the stats type to be collated
  38. my @stat_type=split(/:/, $PARAMS{COLLATE_STAT_TYPES});
  39.  
  40.   # for each of the stats type, read the xls of each hour and write into a consolidated xls.
  41.   foreach my $stat_type_token (@stat_type){
  42.  
  43.             my $con_wb = Spreadsheet::WriteExcel::Big->new("$PARAMS{INPUT_FILE_BASE_PATH}$stat_type_token.xls");
  44.             my $con_excel = $con_wb->add_worksheet();
  45.  
  46.             my $con_row = 0;
  47.  
  48.                 my @stat_files=split(/:/, $PARAMS{COLLATE_YYYYMMDD_HH});
  49.  
  50.                   foreach my $stat_files_token (@stat_files) {
  51.  
  52.                       my $source_xl_name = $PARAMS{INPUT_FILE_BASE_PATH}.$stat_type_token."-".$stat_files_token.".xls";
  53.                                         my $source_excel = new Spreadsheet::ParseExcel;
  54.                     my $source_book = $source_excel->Parse($source_xl_name) or die "Could not open source Excel file $source_xl_name !";
  55.  
  56.                     my $source_sheet = $source_book->{Worksheet}[0];
  57.  
  58.  
  59.                     print "Processing $stat_type_token-$stat_files_token xls please wait\n";
  60.                     foreach my $row_index ($source_sheet->{MinRow} .. $source_sheet->{MaxRow}) {
  61.  
  62.                               my $con_col = 0;
  63.  
  64.                               foreach my $col_index ($source_sheet->{MinCol} .. $source_sheet->{MaxCol}) {
  65.  
  66.                                        my $source_cell = $source_sheet->{Cells}[$row_index][$col_index];
  67.  
  68.  
  69.                                                if (defined $source_cell) {
  70.                                                   $con_excel->write($con_row, $con_col, $source_cell->Value );
  71.                                                }
  72.  
  73.  
  74.  
  75.                                         $con_col++;
  76.  
  77.                               }
  78.  
  79.                              $con_row++; 
  80.                              $consol_rows++;
  81.  
  82.                              # max row count in Excel is 65536 rows.
  83.                              # open a new sheet if the max row is reached.
  84.                              if ( $consol_rows > 65000 ) {
  85.  
  86.                                            $consol_rows = 0;
  87.                                            $con_row = 0;
  88.                                            $con_col = 0;
  89.                                            $con_excel = $con_wb->add_worksheet();
  90.                                        }
  91.  
  92.  
  93.                     }
  94.  
  95.         }    
  96.  
  97.       $con_wb->close();
  98.  
  99.  
  100.   }
  101.  
  102.   print "Processing Done. Time to analyse\n";
  103.  
  104.  
<b>Problem is :</b>
with this line (line no 54)
Expand|Select|Wrap|Line Numbers
  1. my $source_book = $source_excel->Parse($source_xl_name) or die "Could not open source Excel file $source_xl_name !";
  2.  
As soon this line get executed, system stops execution & give the message that it could not open the file<br>
but the required file is getting created with just the file name.xls not with full file name-date.xls

ex: it creates sar-u.xls file but not with sar-u-20080118-1800.xls<br>

i don't know where am doing mistake in this line<br>
Expand|Select|Wrap|Line Numbers
  1. my $source_xl_name = $PARAMS{INPUT_FILE_BASE_PATH}.$stat_type_token."-".$stat_files_token.".xls";
  2.  
can anyone help me why is it not creating the file with full name ???<br>

Regards,<br>
Vijayarl
Sep 18 '08 #1
Share this Question
Share on Google+
2 Replies


KevinADC
Expert 2.5K+
P: 4,059
print the scalars in question to the screen while the program runs to make sure they are defined with the correct values.
Sep 18 '08 #2

P: 65
print the scalars in question to the screen while the program runs to make sure they are defined with the correct values.

Thanks Kevin.... it worked now !!!!! :-)

Vijayarl
Sep 18 '08 #3

Post your reply

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