Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
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
Replies are listed 'Best First'.
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.
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?

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 cooling their heels in the Monastery: (6)
As of 2015-07-29 06:13 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (260 votes), past polls