Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

Re^2: Get Cell Value while Reading Excel file and not the formula

by PBegin (Initiate)
on Apr 14, 2013 at 06:28 UTC ( #1028601=note: print w/ replies, xml ) Need Help??


in reply to Re: Get Cell Value while Reading Excel file and not the formula
in thread Get Cell Value while Reading Excel file and not the formula

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>


Comment on Re^2: Get Cell Value while Reading Excel file and not the formula
Select or Download Code
Re^3: Get Cell Value while Reading Excel file and not the formula
by PBegin (Initiate) on Apr 14, 2013 at 07:00 UTC
    Also, Is there a module to write and read the same excel file in the same program? Thanks!

      What I think is happening is the following:

      Your "write" program generates an Excel file containing some formulae.

      These formulae never get calculated unless you open the file in Excel and calculate.

      If I then use your "read" program to read the file, I get "0.00%" values from the formula cells.

      If I first save the spreadsheet in Excel, then the "read" program returns proper values.

      My interpretation is that by using Spreadsheet::WriteExcel, formulae will never be calculated, which makes sense as no Excel is present necessarily. Your dilemma now is that you will not have the results from these formulae before you open the spreadsheet in Excel and have it calculated and saved. In which case using "Win32::OLE" would be the better solution. Then Perl would always interact with the spreadsheet through Excel and you can read/write as you like.

      I have looked at the module documentation but I found nothing that confirms or contradicts my hypothesis.

      Just for completeness, here is my reduced "write" script:

      #!/usr/bin/perl use 5.010; use strict; use warnings; use Spreadsheet::WriteExcel; my $WriteWorkbook = Spreadsheet::WriteExcel->new('Write.xls'); my $WriteWorksheet = $WriteWorkbook->add_worksheet('Data'); my $formula = $WriteWorksheet->store_formula('=1-(F1/E1)'); my $f_change = $WriteWorkbook->add_format(); $f_change->set_num_format('0.00%'); for my $row ( 1..4 ) { for my $col ( 4..5 ) { $WriteWorksheet->write($row, $col, rand()); } $WriteWorksheet->repeat_formula($row, 13, $formula, $f_change, 'F1', +'F'.($row+1), 'E1', 'E'.($row+1)); }

      and my "read" script:

      #!/usr/bin/perl use 5.010; use strict; use warnings; use Spreadsheet::ParseExcel; my $parser = Spreadsheet::ParseExcel->new(); my $ReadWorkbook = $parser->parse('write.xls'); 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); print "$row,$col,",$cell->value(),"\n" if defined($cell); } } }

        What I think is happening is the following:

        Could be :)

        perldoc Spreadsheet::ParseExcel |grep -i formula

        This module cannot read the values of formulas from files created with Spreadsheet::WriteExcel unless the user specified the values when creating the file (which is generally not the case). The reason for this is that Spreadsheet::WriteExcel writes the formula but not the formula result since it isn't in a position to calculate arbitrary Excel formulas without access to Excel's formula engine.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (10)
As of 2014-12-29 14:46 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (190 votes), past polls