Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

Spreadsheet::WriteExcel::Chart y-axis behaving not as expected

by jbrugger (Parson)
on Sep 13, 2011 at 07:30 UTC ( #925637=perlquestion: print w/ replies, xml ) Need Help??
jbrugger has asked for the wisdom of the Perl Monks concerning the following question:

Hi all, I use SpreadSheet::WriteExel to generate charts.
The thing is however that the y-axis is not behaving as i expect. Perhaps i can explain using this example:
#!/usr/bin/perl use strict; use warnings; use Spreadsheet::WriteExcel; use Spreadsheet::WriteExcel::Utility qw( xl_range_formula ); my @data=( [' ', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', +'m'], ['a', 7.9, 8.1, 8.1, 8.0, 7.9, 7.9, 8.1, 7.9, 8.4, 8.1, 8.0, 8.6, +8.0], ['b', 7.9, 8.0, 7.9, 8.0, 7.8, 8.0, 8.0, 8.0, 8.2, 8.3, 8.1, 8.4, +8.1], ['c', 8.1, 8.1, 8.1, 8.0, 7.9, 8.2, 8.1, 8.1, 8.1, 8.1, 8.8, 8.0, +8.0], ['d', 8.4, 8.1, 8.0, 8.0, 8.1, 7.9, 8.2, 8.4, 8.1, 8.1, 8.0, 7.7, +8.2], ['e', 8.0, 8.0, 8.0, 8.0, 7.9, 8.0, 8.1, 8.1, 8.1, 8.1, 8.2, 8.4, +8.1], ['f', 8.0, 8.0, 7.6, 8.1, 8.0, 8.1, 8.0, 8.2, 8.1, 8.0, 8.1, 7.6, +7.7], ['g', 8.4, 8.0, 8.0, 8.2, 7.7, 8.0, 8.1, 8.1, 8.0, 8.2, 8.1, 7.7, +8.1], ['h', 7.9, 8.0, 8.0, 8.0, 8.0, 8.1, 8.0, 8.1, 8.0, 8.1, 8.3, 8.2, +8.2], ['i', 8.0, 8.0, 8.0, 7.9, 7.9, 7.9, 8.1, 8.0, 8.0, 8.0, 7.6, 7.9, +8.0], ['j', 8.0, 8.0, 8.0, 8.0, 7.9, 8.0, 8.1, 8.1, 8.1, 8.2, 8.4, 8.0, +8.4] ); my $workbook = Spreadsheet::WriteExcel->new( 'chart.xls' ); my $worksheet = $workbook->add_worksheet('testRight'); my $i=1; my $chart = $workbook->add_chart( type => 'line', embedded => 1 ); foreach (@data) { $worksheet->write( $i, 0, $_ ); $chart->add_series( 'categories' => xl_range_formula( 'testRight', 1, 1, 1, 13), 'values' => xl_range_formula( 'testRight', $i, $i, 1, 13 ) +, 'name' => $_->[0], ) if $i > 1; $i++; } $worksheet->insert_chart( 'B15', $chart ); @data=( [' ', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', +'m'], ['a', 7.9, 8.1, 8.1, 8.0, 7.9, 7.9, 8.1, 7.9, 8.4, 8.1, 8.1, 8.0, +8.6], ['b', 7.9, 8.0, 7.9, 8.0, 7.8, 8.0, 8.0, 8.0, 8.0, 8.2, 8.3, 8.1, +8.4], ['c', 8.1, 8.1, 8.1, 8.0, 7.9, 8.2, 8.1, 8.1, 8.1, 8.1, 8.8, 8.0, +8.6], ['d', 8.1, 8.2, 8.0, 8.0, 8.0, 8.1, 8.1, 8.2, 8.2, 8.2, 8.0, 8.0, +7.7], ['e', 8.4, 8.0, 8.0, 8.0, 7.8, 8.1, 7.9, 8.1, 8.2, 8.1, 8.3, 8.0, +7.5], ['f', 7.9, 7.8, 7.9, 7.9, 7.6, 7.9, 8.0, 8.0, 8.1, 8.3, 9.2, 8.0, +9.1], ['g', 8.0, 8.0, 8.1, 8.1, 7.9, 8.0, 8.2, 8.1, 8.2, 8.2, 7.6, 8.0, +9.0], ['h', 7.9, 8.0, 8.0, 8.0, 8.0, 8.1, 8.0, 8.1, 8.0, 8.1, 8.3, 8.2, +8.2], ['i', 8.0, 8.0, 8.0, 7.9, 7.9, 7.9, 8.1, 8.0, 8.0, 8.0, 7.6, 7.9, +8.0], ['j', 8.0, 8.0, 8.0, 8.0, 7.9, 8.0, 8.1, 8.1, 8.1, 8.2, 8.4, 8.0, +8.4] ); $worksheet = $workbook->add_worksheet('testWrong'); $i=1; $chart = $workbook->add_chart( type => 'line', embedded => 1 ); foreach (@data) { $worksheet->write( $i, 0, $_ ); $chart->add_series( 'categories' => xl_range_formula( 'testWrong', 1, 1, 1, 13), 'values' => xl_range_formula( 'testWrong', $i, $i, 1, 13 ) +, 'name' => $_->[0], ) if $i > 1; $i++; } $worksheet->insert_chart( 'B15', $chart ); $workbook->close();
As you can see in the 'wrong' tab, the y-axis holds a different scale than the first one, Is there a way to set the 'range' of the y-axis so i'm able to set the minimum and maximum value using Spreadsheet::WriteExcel?
I cant't find it in the docs.
The problem occurs in OpenOffice as well as in Excel.
"We all agree on the necessity of compromise. We just can't agree on when it's necessary to compromise." - Larry Wall.

Comment on Spreadsheet::WriteExcel::Chart y-axis behaving not as expected
Download Code
Re: Spreadsheet::WriteExcel::Chart y-axis behaving not as expected
by jmcnamara (Monsignor) on Sep 13, 2011 at 10:48 UTC

    I don't actually see any substantive difference between the two charts in Excel 2007. I think that you probably want to avoid writing a data series on the chart for the first row of each data array:

    ... if ($i > 1) { $chart->add_series( 'categories' => xl_range_formula( 'testRight', 1, 1, 1, 13 +), 'values' => xl_range_formula( 'testRight', $i, $i, 1, +13 ), 'name' => $_->[0], ); } ...

    In which case the issue is more evident.

    Unfortunately, what you are seeing is a results of Excel's automatic axis scaling. There isn't (and probably won't be) any way to override this with Spreadsheet::WriteExcel. It is intended as a feature in Excel::Writer::XLSX but even that is probably a few months off.

    Update: Manual scaling is now supported in Excel::Writer::XLSX >= version 0.36.

    --
    John.

      Hi John, Thanks for your answer, the first row you mentioned is correct.

      Thing is however that the y-axis on the first chart is from 6,5 to 9, and the second one from 0 to 10.
      That makes the 2nd one more or less unreadable, where the first one is 'zoomed in' so the differences between the sets are better visible.
      To bad if it's true that we cannot change it :(
      "We all agree on the necessity of compromise. We just can't agree on when it's necessary to compromise." - Larry Wall.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (12)
As of 2014-07-31 17:42 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (249 votes), past polls