Unfortunately .XLS files are a binary format which you cannot process on a row by row basis. Everything will be read in memory at once and I do not think there is another way around this.

I did find Spreadsheet::ParseExcel_XLHTML (an old module, last updated in 2009) which claims to be faster and does "less fancy stuff". It replaces the parsing subroutine of Spreadsheet::ParseExcel and perhaps uses less memory? I haven't tried it, so I have no idea if it works well. Also there is no ::Simple version of it. However, if you look at the source code of Spreadsheet::ParseExcel::Simple it seems easy to make a Spreadsheet::ParseExcel_XLHTML::Simple yourself.

Update: Unfortunately I cannot install Spreadsheet::ParseExcel_XLHTML since it needs bin:xlhtml which is not available on my PC.

Hi guys, thanks for the response, I figured out to calculate the no of rows using SaveParser but using this I'm unable to save the modified row. Im posting my code , kindly provide any feedback or any thing that I'm missing

sub to_test {
    my ($self, $in_file) = @_;
    $count = @_;
    #To Test
    util->logit("[$PARENT_PROC_NAME]: in_file[$in_file]");
    my $parser_temp = Spreadsheet::ParseExcel::SaveParser->new(
        CellHandler => \&cell_handler_1,
         NotSetCell  => 0
    );
    util->logit("[$PARENT_PROC_NAME]: parser_temp[$parser_temp]");
    my $sheet_ct = 0;
    my $Book = $parser_temp->Parse($in_file);
    util->logit("[$PARENT_PROC_NAME]: parser_temp[$parser_temp], glob_
+sheet_index[$glob_sheet_index],pre_row[$pre_row]");
    @count_of_sheet_rows[$glob_sheet_index] =  $pre_row;
    util->logit("[$PARENT_PROC_NAME]: count_of_sheet_rows_3[@count_of_
+sheet_rows], glob_sheet_index[$glob_sheet_index],pre_row[$pre_row]");
    #my $workbook = Spreadsheet::WriteExcel->new();
    #my $sheet_work = $parser_temp->worksheet(0);
    #$sheet_work->AddCell( 1, 0, 'Hi Excel!' );
    my $temp = "temp";
    my $new_path = "$out_file$underscore$temp$extension";
    my $Saved_Workbook = $Book->SaveAs($new_path);
    foreach my $worksheet ($Book->worksheets()) {
    my $value_row = @count_of_sheet_rows[$sheet_ct]+2;
    my $value_cell = 0;
    util->logit("[$PARENT_PROC_NAME]: count value_row[$value_row], wor
+ksheet[$worksheet]");
    #$worksheet->AddCell(@count_of_sheet_rows[$sheet_ct] + 1, 0, local
+time);
    $worksheet->AddCell($value_row, $value_cell, "World2");
    ##$worksheet->AddCell( 1, 0, 'Hi Excel!' );
       #$worksheet->write(@count_of_sheet_rows[$sheet_ct], 0, 'Hi Exce
+l!');
       $sheet_ct++;
   }
    #$Book->close();
    #$Book->Close();
    #$parser_temp->Quit();
    #$Saved_Workbook->SaveAs($new_path);
    $Saved_Workbook->close();
    #undef $Book;
    #undef $parser_temp;
    return $new_path;
}
[download]

Kindly suggest something...

We cannot run this code, as it is a solitary subroutine without any documentation of input and expected output. I suspect it does not even run under "use strict" and contains a lot of irrelevant code not directly linked to your problem.

Please, write a simple script that is only about your problem and then we may be able to assist.

Also you haven't answered my question about the problem(?) with the high(?) memory use. Any solution which is based on Spreadsheet::ParseExcel will run into the same problem.

CountZero

A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

My blog: Imperial Deltronics

since it reads the file inmemory so its memory usage hits upto 1GB for only 11MB file .

The next question is which version of Excel produced your files. Although I haven't used Perl to work on Excel files for at least 6 years (except one relatively small project writing fairly simple Excel files under Unix), my understanding is that the most recent Excel file format is really a zipped file containing a number of XML file components. If this is the case with your version, then updating the right file component might be sufficient. I have never done it, and have no idea whether it is simple or not, whether it works or not, but it might be a useful clue.

Another alternative might be to export your Excel file in CSV format, to update the CSV file, and to convert it back to Excel.

I dunno if this is gonna get you anywhere, but these are just two possible solutions.

The OP is given .XLS files which are binary files and can only be opened with the Spreadsheet::ParseExcel module and that fileformat can only be read in memory as a whole

The OP does not have Excel on his machine so he cannot transform the .XLS files in csv-files either.

CountZero

A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

My blog: Imperial Deltronics