Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked

Get Cell Value while Reading Excel file and not the formula

by PBegin (Initiate)
on Apr 12, 2013 at 07:18 UTC ( #1028308=perlquestion: print w/ replies, xml ) Need Help??
PBegin has asked for the wisdom of the Perl Monks concerning the following question:

I am new to Perl! I am reading a cell having a formula in an excel file and getting a zero cell value when I use get_cell method. This cell value had been computed previously by another perl program. When I click the cell in the written excel file, it shows the formula in the formula bar and not the value of the cell. How can I change the formula bar to have value in excel file using perl, so that I can use this value to do further computation.

Comment on Get Cell Value while Reading Excel file and not the formula
Replies are listed 'Best First'.
Re: Get Cell Value while Reading Excel file and not the formula
by hdb (Prior) on Apr 12, 2013 at 07:40 UTC

    You need to provide some context if you expect an answer. For example, which modules are you using, how does the code look like writing into the cell, reading from the cell etc.

    In Excel, the formula bar always shows the formula in the cell and not the value. I assume, that is where it's name coming from. At least, if there is a formula in the cell.

Re: Get Cell Value while Reading Excel file and not the formula
by tobias_hofer (Friar) on Apr 12, 2013 at 07:42 UTC


    When the cell is computed by a different perl program before, you are sure that this program has stored the value to that cell? However, showing a bit of code or something like a flow-chart what your want to do may help us understand your Issue ;-)


      Thank you for the quick responses. Following is my code: 1st program - To compute formula and write it to an excel.

      #!/usr/bin/perl use 5.010; use strict; use warnings; use Spreadsheet::ParseExcel; use Spreadsheet::WriteExcel; my $parser = Spreadsheet::ParseExcel->new(); my $ReadWorkbook = $parser->parse('Read.xls'); my $WriteWorkbook = Spreadsheet::WriteExcel->new('Write.xls'); my $WriteWorksheet = $WriteWorkbook->add_worksheet('Data'); my $formula = $WriteWorksheet->store_formula('=1-(F1/E1)'); for my $ReadWorksheet ($ReadWorkbook->Worksheet('Data')){ my ($row_min, $row_max) = $ReadWorksheet->row_range(); my ($col_min, $col_max) = $ReadWorksheet->col_range(); my $cell = $ReadWorksheet->get_cell($row, $col); next unless $cell; $WriteWorksheet->write($row, $col, $cell->value()); my $f_change = $WriteWorkbook->add_format(); $f_change->set_num_format('0.00%'); my $limit = 5; for my $row($row_min..$limit){ $WriteWorksheet->repeat_formula($row, 13, $formula1, $ +f_change, 'F1','F'.($row+1), 'E1', 'E'.($row+1)); } } } }

      The above code works fine to write the calculated values in the excel 'write.xls'. But the following code to access the computed values from the "write.xls" file returns value 0 for the corresponding cells when get_cell function is called. 2nd program - To read data from 'write.xls' and apply some conditions xxx and write it to a new file 'final.xls' '<\p>

      #!/usr/bin/perl use 5.010; use strict; use warnings; use Spreadsheet::ParseExcel; #use Spreadsheet::WriteExcel; my $parser = Spreadsheet::ParseExcel->new(); my $ReadWorkbook = $parser->parse('write.xls'); #my $WriteWorkbook = Spreadsheet::WriteExcel->new('final.xls'); #my $WriteWorksheet = $WriteWorkbook->add_worksheet('Data'); for my $ReadWorksheet($ReadWorkbook->Worksheet('Data')){ my ($row_min, $row_max) = $ReadWorksheet->row_range(); my ($col_min, $col_max) = $ReadWorksheet->col_range(); for my $row($row_min..$row_max){ for my $col ( $col_min..$col_max){ my $cell = $ReadWorksheet->get_cell($row, $col); #condition XXX{ $WriteWorksheet->write($row, $col, $cell->value()); } } }

      This code writes 0 into the cells which were previously computed by the 1st program. "gives a comment that the number has been written in text". I have tried 'pastespecial using OLE excel' to access the number format in computed cells (to avoid the formula) in 'write.xls' to do further calculations. It works the first time program opens the file. But when I close and open the file again, clicking on the computed cells shows the formula and not the number. How can I get the value of the cell in my program other than get_cell function which returns a "#value" of the cells and not the number value". Thanks again for the help!<\p>

        Also, Is there a module to write and read the same excel file in the same program? Thanks!
Re: Get Cell Value while Reading Excel file and not the formula
by Anonymous Monk on Apr 12, 2013 at 07:39 UTC
    What are you talking about? Use things like module names and program names, or post code

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1028308]
Approved by Corion
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (4)
As of 2015-11-29 18:13 GMT
Find Nodes?
    Voting Booth?

    What would be the most significant thing to happen if a rope (or wire) tied the Earth and the Moon together?

    Results (752 votes), past polls