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

Values of Formulas also not preserved with save parser ?

by devbond (Novice)
on Sep 06, 2013 at 11:34 UTC ( #1052695=perlquestion: print w/ replies, xml ) Need Help??
devbond has asked for the wisdom of the Perl Monks concerning the following question:

# Open an existing file with SaveParser my $parser = Spreadsheet::ParseExcel::SaveParser->new(); my $template = $parser->Parse('perl.xls'); my @s; $s[$_] = $template->worksheet( "cpzea0".$_."a0001" ) for 1..8; ###################################################################### +##################################################################### +################ #####This will retrieve the last row where value was written form the +first worksheet so that we can continue writing from the same place## +################ my $cell = $s[1]->get_cell(1000,1); $lrow= $cell->value; print $lrow; ###################################################################### +##################################################################### +############### my $workbook= $template->SaveAs('newfile.xls'); for ($i=0;$i<=7;$i++) { $s[$i+1] = $workbook->sheets($i) ; } ###################################################################### +##################################################################### +################ ####All the Formats will be described within this space############### +##################################################################### +################# my $text_format = $workbook->add_format( bold => 1, size => 10, font =>'arial', bg_color =>'green', align =>'center', border =>'1' ); my $text_format1 = $workbook->add_format( size => 10.5, font =>'consolas', align =>'center', ); my $text_format2 = $workbook->add_format( size => 10.5, font =>'consolas', align =>'center', bg_color =>'dark blue' +, border =>'1' ); my $text_format3=$workbook->add_format( size => 10.5, font =>'consolas', align =>'center', border =>'1' , color =>'red', bold =>'1', ); ###################################################################### +##################################################################### +################# $row=$lrow + 3; $col=1; $start=$row; for($i=1;$i<=8;$i++) { $f=0; for ($c=$col+1;$c<=8;$c++) { $s[$i]->write($start,$c,$header[$f++],$text_format); } } my $col = 1; for ($i=1;$i<=8;$i++) { for ($r=0;$r<=24;$r++) { $row++; for ($c=0;$c<=4;$c++) { $col++; $s[$i]->write($row,$col,$arrays[$i][$r][$c],$text_form +at1); } $col=1; } $row=$lrow + 3; } #This part will create Formulae for current day my $c=7 ; for ($i=1;$i<=8;$i++) { for ($r=$lrow + 4;$r<=$lrow + 28;$r++) { $c=$r + 1; $s[$i]->write($r,7,"=F$c/G$c",$text_format2); $s[$i]->write($r,8,"=F$c/SUM(C$c:D$c)",$text_format2); } } ###################################################################### +##################################################################### +############## #This part is for writting the last line of the data which calculates +the sum of each parameter for a given day############################ +############### my $g =2; $row=$lrow + 28; $val=$lrow + 5; for ($i=1;$i<=8;$i++) { $s[$i]->write($row,$g++,"Total",$text_format3); $s[$i]>write($row,$g++,"=SUM(D$val:D$row)",$text_format3); $s[$i]->write($row,$g++,"=SUM(E$val:E$row)",$text_format3); $s[$i]->write($row,$g++,"=SUM(F$val:F$row)",$text_format3); $s[$i]->write($row,$g++,"=SUM(G$val:G$row)",$text_format3); $g=2; } ###################################################################### +##################################################################### +################ $s[1]->write(1000,1,$lrow + 30); $workbook->close();

Comment on Values of Formulas also not preserved with save parser ?
Download Code
Re: Values of Formulas also not preserved with save parser ?
by keszler (Priest) on Sep 06, 2013 at 12:33 UTC
    And the question is?
Re: Values of Formulas also not preserved with save parser ?
by wanna_code_perl (Pilgrim) on Sep 06, 2013 at 14:26 UTC

    I'm not sure what your question is, but the title of the node seems to indicate a question about preserving formula values. A quick check of the Spreadsheet::ParseExcel documentation turned up this section:

    KNOWN PROBLEM

    This module can not get the values of fomulas in Excel files made with Spreadsheet::WriteExcel. Normaly(sic) (ie. By Excel application), formula has the result with it. But Spreadsheet::WriteExcel writes formula with no result. If you set your Excel application "Auto Calculation" off. (maybe Tool-Option-Calculation or something) You will see the same result.

    Does that address your problem?

    Regarding your code itself, may I suggest you take a careful read through perlstyle and consider adopting many of its suggestions.

    ###################################################################### +##################################################################### +################ ####All the Formats will be described within this space############### +##################################################################### +#################

    Finally, you have lines that are 155 characters long, which is rather hard to read. Perl is generally quite permissive with whitespace, so I suggest you insert some line breaks at logical places to keep your lines under 80 characters (or pick a different length, but IMHO 80 is plenty for Perl). Lines longer than that can be very hard to comprehend, even if someone happens to have their terminal/editor set wide enough to display them without breaks/scrolling.

      Finally, you have lines that are 155 characters long, which is rather hard to read. Perl is generally quite permissive with whitespace, so I suggest you insert some line breaks at logical places to keep your lines under 80 characters (or pick a different length, but IMHO 80 is plenty for Perl). Lines longer than that can be very hard to comprehend, even if someone happens to have their terminal/editor set wide enough to display them without breaks/scrolling.

      I agree long lines are horrible, but with line wrapping, formatting characters on, and line numbers turned on, long lines are easier but not easy, to work with.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (9)
As of 2014-07-29 23:17 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (229 votes), past polls