http://www.perlmonks.org?node_id=925637

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.