Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

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

Title:
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!
  • 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
  • Outside of code tags, you may need to use entities for some characters:
            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?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others browsing the Monastery: (6)
    As of 2014-11-24 00:31 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      My preferred Perl binaries come from:














      Results (134 votes), past polls