Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling

Comment on

( #3333=superdoc: print w/replies, xml ) Need Help??

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>

In reply to Re^2: Get Cell Value while Reading Excel file and not the formula by PBegin
in thread Get Cell Value while Reading Excel file and not the formula by PBegin

Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":

  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?

    What's my password?
    Create A New User
    and all is quiet...

    How do I use this? | Other CB clients
    Other Users?
    Others musing on the Monastery: (6)
    As of 2018-06-19 11:07 GMT
    Find Nodes?
      Voting Booth?
      Should cpanminus be part of the standard Perl release?

      Results (113 votes). Check out past polls.