1#!/usr/bin/perl 2 3####################################################################### 4# 5# A demo of an Column chart with a data table on the X-axis using 6# Excel::Writer::XLSX. 7# 8# Copyright 2000-2021, John McNamara, jmcnamara@cpan.org 9# 10 11use strict; 12use warnings; 13use Excel::Writer::XLSX; 14 15my $workbook = Excel::Writer::XLSX->new( 'chart_data_table.xlsx' ); 16my $worksheet = $workbook->add_worksheet(); 17my $bold = $workbook->add_format( bold => 1 ); 18 19# Add the worksheet data that the charts will refer to. 20my $headings = [ 'Number', 'Batch 1', 'Batch 2' ]; 21my $data = [ 22 [ 2, 3, 4, 5, 6, 7 ], 23 [ 10, 40, 50, 20, 10, 50 ], 24 [ 30, 60, 70, 50, 40, 30 ], 25 26]; 27 28$worksheet->write( 'A1', $headings, $bold ); 29$worksheet->write( 'A2', $data ); 30 31# Create a column chart with a data table. 32my $chart1 = $workbook->add_chart( type => 'column', embedded => 1 ); 33 34# Configure the first series. 35$chart1->add_series( 36 name => '=Sheet1!$B$1', 37 categories => '=Sheet1!$A$2:$A$7', 38 values => '=Sheet1!$B$2:$B$7', 39); 40 41# Configure second series. Note alternative use of array ref to define 42# ranges: [ $sheetname, $row_start, $row_end, $col_start, $col_end ]. 43$chart1->add_series( 44 name => '=Sheet1!$C$1', 45 categories => [ 'Sheet1', 1, 6, 0, 0 ], 46 values => [ 'Sheet1', 1, 6, 2, 2 ], 47); 48 49# Add a chart title and some axis labels. 50$chart1->set_title( name => 'Chart with Data Table' ); 51$chart1->set_x_axis( name => 'Test number' ); 52$chart1->set_y_axis( name => 'Sample length (mm)' ); 53 54# Set a default data table on the X-Axis. 55$chart1->set_table(); 56 57# Insert the chart into the worksheet (with an offset). 58$worksheet->insert_chart( 'D2', $chart1, { x_offset => 25, y_offset => 10 } ); 59 60 61# 62# Create a second chart. 63# 64my $chart2 = $workbook->add_chart( type => 'column', embedded => 1 ); 65 66# Configure the first series. 67$chart2->add_series( 68 name => '=Sheet1!$B$1', 69 categories => '=Sheet1!$A$2:$A$7', 70 values => '=Sheet1!$B$2:$B$7', 71); 72 73# Configure second series. 74$chart2->add_series( 75 name => '=Sheet1!$C$1', 76 categories => [ 'Sheet1', 1, 6, 0, 0 ], 77 values => [ 'Sheet1', 1, 6, 2, 2 ], 78); 79 80# Add a chart title and some axis labels. 81$chart2->set_title( name => 'Data Table with legend keys' ); 82$chart2->set_x_axis( name => 'Test number' ); 83$chart2->set_y_axis( name => 'Sample length (mm)' ); 84 85# Set a data table on the X-Axis with the legend keys showm. 86$chart2->set_table( show_keys => 1 ); 87 88# Hide the chart legend since the keys are show on the data table. 89$chart2->set_legend( position => 'none' ); 90 91# Insert the chart into the worksheet (with an offset). 92$worksheet->insert_chart( 'D18', $chart2, { x_offset => 25, y_offset => 10 } ); 93 94$workbook->close(); 95 96__END__ 97