#!/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();