Excel-Writer-XLSX-CDF

 view release on metacpan or  search on metacpan

README.md  view on Meta::CPAN

49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
Set and returns the Y axis label of the Excel chart
 
Default: Probability
 
## chart\_x\_label
 
Set and returns the X axis label of the Excel chart
 
Default: ""
 
## chart\_legend\_display
 
Set and returns the legend display property for the Excel chart
 
Default: 1
 
## chart\_colors
 
Set and returns an array reference of Excel color codes to use for each CDF in group order.  The default color once all colors are used is black.
 
Default: \['#FF0000', '#800000', '#FFFF00', '#808000', '#00FF00', '#008000', '#00FFFF', '#008080', '#0000FF', '#000080', '#FF00FF', '#800080'\]
 
## group\_names\_sort

lib/Excel/Writer/XLSX/CDF.pm  view on Meta::CPAN

131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
 
=cut
 
sub chart_x_min {
  my $self           = shift;
  $self->{'chart_x_min'} = shift if @_;
  $self->{'chart_x_min'} = undef unless exists $self->{'chart_x_min'};
  return $self->{'chart_x_min'};
}
 
=head2 chart_legend_display
 
Set and returns the legend display property for the Excel chart
 
Default: 1
 
=cut
 
sub chart_legend_display {
  my $self           = shift;
  $self->{'chart_legend_display'} = shift if @_;
  $self->{'chart_legend_display'} = 1 unless defined $self->{'chart_legend_display'};
  return $self->{'chart_legend_display'};
}
 
=head2 chart_colors
 
Set and returns an array reference of Excel color codes to use for each CDF in group order.  The default color once all colors are used is black.
 
Default: ['#FF0000', '#800000', '#FFFF00', '#808000', '#00FF00', '#008000', '#00FFFF', '#008080', '#0000FF', '#000080', '#FF00FF', '#800080']
 
=cut

lib/Excel/Writer/XLSX/CDF.pm  view on Meta::CPAN

220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
#Open string scalar reference as file handle for Excel::Writer::XLSX to write to
open my $fh, '>', \my $content or die("Error: Filehandle open error: $!");
 
#Object for Excel Workbook
require Excel::Writer::XLSX;
my $workbook         = Excel::Writer::XLSX->new($fh);
 
#Add a worksheet chart as first tab so it shows when document is opened
my $chart            = $workbook->add_chart(type=>'scatter', subtype=>'straight');
 
#Add worksheet for chart legend groups
my $worksheet_groups = $workbook->add_worksheet('groups');
$worksheet_groups->write_string(0, 0, 'Group');
$worksheet_groups->write_string(0, 1, 'Index');
$worksheet_groups->write_string(0, 2, 'Count');
my $group_index      = 0;
 
#Colors for data series lines and legend
my @colors          = @{$self->chart_colors};
 
#foreach group add worksheet, data and chart series
my @stats_groups    = ();
 
foreach my $group (@groups) {
 
  #Add series label for legend
  $group_index++;
  $worksheet_groups->write_string($group_index, 0, $group);
  $worksheet_groups->write_number($group_index, 1, $group_index);
 
  #Add worksheet
  my $worksheet    = $workbook->add_worksheet("group_$group_index");
 
  #Add data to worksheet
  my @values       = sort {$a <=> $b} @{$series->{$group}->{'values'}};
  my $values_count = scalar(@values);

lib/Excel/Writer/XLSX/CDF.pm  view on Meta::CPAN

304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
  if (!defined($minset)) {
    $minset = undef; #allow for calculation in the future
  } elsif (defined($minset) and $minset =~ m/\Aauto\Z/i) {
    $minset = undef;
  }
 
  #Configure chart
  $chart->set_title( name => $self->chart_title                                  );
  $chart->set_y_axis(name => $self->chart_y_label, min => 0      , max => 1      );
  $chart->set_x_axis(name => $self->chart_x_label, min => $minset, max => $maxset);
  $chart->set_legend(none => 1) unless $self->chart_legend_display;
 
  #Write Excel output to filehandle
  $workbook->close;
 
  return $content;
 
}
 
=head2 generate_file

t/002_generate.t  view on Meta::CPAN

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
  skip "Error: Cannot create a temp file. Read-only file system?", 4 unless ($tempdir && -d $tempdir && -r $tempdir);
  unlink $tempdir;
 
  my @data     = map {
                      [ one   => rand            ],
                      [ two   => int(10*rand)/10 ],
                      [ three => int(50*rand)/50 ],
                     } (1 .. 1000);
 
  {
    my $e        = Excel::Writer::XLSX::CDF->new(chart_title => "", chart_x_label => "", chart_y_label => "", chart_legend_display=>0);
    my $filename = $e->generate_file(\@data);
    ok(-r $filename, 'generate_file');
    ok(-s $filename, 'generate_file');
    diag("Created: $filename");
  }
 
  {
    my $e        = Excel::Writer::XLSX::CDF->new(
                                                 chart_title          => "My Title",
                                                 chart_x_label        => "My X Axis",
                                                 chart_y_label        => "My Y Axis",
                                                 chart_legend_display => 1,
                                                 chart_colors         => ['#EE1111', '#11EE11', '#1111EE'],
                                                 group_names_sort     => 1,
                                                );
    my $filename = $e->generate_file(\@data);
    ok(-r $filename, 'generate_file');
    ok(-s $filename, 'generate_file');
    diag("Created: $filename");
  }
 
  {
    my $e        = Excel::Writer::XLSX::CDF->new(
                                                 chart_title          => "My Title",
                                                 chart_x_label        => "My X Axis",
                                                 chart_y_label        => "My Y Axis",
                                                 chart_legend_display => 1,
                                                 chart_colors         => ['#EE1111', '#11EE11', '#1111EE'],
                                                 group_names_sort     => 1,
                                                 chart_x_min          => 0.4,
                                                 chart_x_max          => 0.6,
                                                );
    my $filename = $e->generate_file(\@data);
    ok(-r $filename, 'generate_file');
    ok(-s $filename, 'generate_file');
    diag("Created: $filename");
  }
 
  {
    my $e        = Excel::Writer::XLSX::CDF->new(
                                                 chart_title          => "My Title",
                                                 chart_x_label        => "My X Axis",
                                                 chart_y_label        => "My Y Axis",
                                                 chart_legend_display => 1,
                                                 chart_colors         => ['#EE1111', '#11EE11', '#1111EE'],
                                                 group_names_sort     => 1,
                                                 chart_x_min          => 'auto',
                                                 chart_x_max          => 'auto',
                                                );
    my $filename = $e->generate_file(\@data);
    ok(-r $filename, 'generate_file');
    ok(-s $filename, 'generate_file');
    diag("Created: $filename");
  }
 
  {
    my @additional = map {[one => $_/10]} (-5 .. 15);
    my $e        = Excel::Writer::XLSX::CDF->new(
                                                 chart_title          => "My Title",
                                                 chart_x_label        => "My X Axis",
                                                 chart_y_label        => "My Y Axis",
                                                 chart_legend_display => 1,
                                                 chart_colors         => ['#EE1111', '#11EE11', '#1111EE'],
                                                 group_names_sort     => 1,
                                                );
    my $filename = $e->generate_file([@data, @additional]);
    ok(-r $filename, 'generate_file');
    ok(-s $filename, 'generate_file');
    diag("Created: $filename");
  }
}