Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

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

by tobias_hofer (Friar)
on Apr 12, 2013 at 07:42 UTC ( #1028314=note: print w/ replies, xml ) Need Help??


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

Hi

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

Cheers!
Tobias


Comment on Re: Get Cell Value while Reading Excel file and not the formula
Re^2: Get Cell Value while Reading Excel file and not the formula
by PBegin (Initiate) on Apr 14, 2013 at 06:28 UTC

    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!

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

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (5)
As of 2014-12-28 23:12 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

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





    Results (183 votes), past polls