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

[jbrugger]

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.

[jmcnamara]

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.

[jbrugger]

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 :(