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


in reply to Plot a chart

Adapt this test script and see if it works for you 
#!perl
use strict;
use Spreadsheet::WriteExcel;
my @data1 = map { int rand(10) }(0..34);
my @data2 = map { int rand(10) }(0..34);

my $workbook = Spreadsheet::WriteExcel->new('c:/temp/xyz_stat.xls') or
+ die $!;
my $sheet = $workbook->add_worksheet('sim_1');
$sheet->write_col('B2',\@data1);
$sheet = $workbook->add_worksheet('sim_2');
$sheet->write_col('CU2',\@data2);

my $worksheet_chart = $workbook->add_worksheet('chart');

my $chart = $workbook->add_chart(type => 'line', embedded => 1);
$chart->add_series( name=>'sim1', values=>'=sim_1!$B$2:$B$36');
$chart->add_series( name=>'sim2', values=>'=sim_2!$CU$2:$CU$36');
$worksheet_chart->insert_chart('E2', $chart, 15, 10);
$workbook->close;

[download]
poj

Replies are listed 'Best First'.
Re^2: Plot a chart
by wizsc (Initiate) on Jul 23, 2014 at 22:05 UTC
    Thanks, poj. Your script works just fine. I wonder if there's a scope issue in my script. Something inside the for loop is not right IMO. Does anyone see it?
[reply]
      Try reading your csv files with Text:CSV_XS, adding use strict; use warnings and some print diagnostics. 
      #!perl
use strict;
use warnings;
use Spreadsheet::WriteExcel;
use Text::CSV_XS;

my $csv = Text::CSV_XS->new({ 
  binary => 1, sep_char => ',', eol => "\n"}); 
  
my $xlsfile = 'c:/temp/xyz_stat1.xls';
my $workbook = Spreadsheet::WriteExcel->new($xlsfile) 
   or die $!;

my $num_files = $ARGV[0] || 0;
for my $k (1..$num_files){
  my $csvfile = $ARGV[$k];
  print "Opening $csvfile .. ";
  open my $fh, '<', $csvfile or die "Cannot open $csvfile : $!"; 
  my $sheet = $workbook->add_worksheet('sim_'.$k);
  my $ar = $csv->getline_all($fh);
  $sheet->write_col(0,0,$ar);
  close $fh;
  print scalar (@$ar). " lines read into sheet sim_$k\n";
}
$workbook->close;
print "$xlsfile created\n";

      [download]
      poj
[reply]
[d/l]
[select]

        The issue is that the numbers are being written as strings or characters, second column onward. So it's not able to print it. Either of these two fixes will work.

        1. Account for white spaces: 

        my @frame_stats = split(/,\s+/,$line);

        [download]

        2. Use "write_number": 

        $worksheet[$k]->write_number($frm_num, $col, $el);

        [download]

        Thanks a bunch for all your help!!

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom