Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

Re^2: Append new line in excel sheets

by perl_new_b (Novice)
on Jul 11, 2014 at 20:42 UTC ( [id://1093310]=note: print w/replies, xml ) Need Help??


in reply to Re: Append new line in excel sheets
in thread Append new line in excel sheets

Hi countzero, First of all thanks a lot for the respnse and suggestion buddy. I have tried this solution ...since it reads the file inmemory so its memory usage hits upto 1GB for only 11MB file . So I dropped it. Is ther any simpler module which can do that? Thanks in advance.

Replies are listed 'Best First'.
Re^3: Append new line in excel sheets
by Laurent_R (Canon) on Jul 11, 2014 at 22:00 UTC
    since it reads the file inmemory so its memory usage hits upto 1GB for only 11MB file .
    And so what? Does the program fail? 1 GB in memory is quite large, but is not necessarily a problem with current computers. (Although I am quite a bit surprised that it should take that much.)

    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
Re^3: Append new line in excel sheets
by CountZero (Bishop) on Jul 12, 2014 at 13:00 UTC
    Is the memory usage a problem? Do you get out of memory errors? If not, I wouldn't worry.

    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.

    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

      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; }

      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

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://1093310]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others rifling through the Monastery: (5)
As of 2024-03-29 13:56 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found