File Coverage

blib/lib/Excel/Writer/XLSX/CDF.pm
Criterion Covered Total %
statement 126 126 100.0
branch 33 40 82.5
condition 1 2 50.0
subroutine 15 15 100.0
pod 9 9 100.0
total 184 192 95.8


line stmt bran cond sub pod time code
1             use strict;
2 2     2   166503 use warnings;
  2         10  
  2         57  
3 2     2   8 use List::MoreUtils qw{first_index};
  2         2  
  2         44  
4 2     2   827  
  2         18916  
  2         11  
5             our $VERSION = '0.03';
6              
7             =head1 NAME
8              
9             Excel::Writer::XLSX::CDF - Generates Excel Document with Continuous Distribution Function Chart
10              
11             =head1 SYNOPSIS
12              
13             use Excel::Writer::XLSX::CDF;
14             my $writer = Excel::Writer::XLSX::CDF->new(
15             chart_title => "My Title",
16             group_names_sort => 1,
17             );
18             my @data = (
19             [group_name_A => 0.11], #group name is used to lable the chart series
20             [group_name_A => 0.21],
21             [group_name_A => 0.31],
22             [group_name_A => 0.41],
23             [group_name_B => 0.07],
24             [group_name_B => 0.13],
25             [group_name_Z => 0.10],
26             );
27             my $blob = $writer->generate(\@data); #returns Excel File in memory
28             my $filename = $writer->generate_file(\@data); #returns Excel File in tmp folder
29              
30             =head1 DESCRIPTION
31              
32             Generates Excel Document with Continuous Distribution Function Chart from the supplied data.
33              
34             =head1 CONSTRUCTOR
35              
36             =head2 new
37              
38             my $writer = Excel::Writer::XLSX::CDF->new(
39             chart_title => "Continuous Distribution Function (CDF)",
40             chart_y_label => "Probability",
41             chart_x_label => "",
42             group_names_sort => 0, #default 0 is in order of appearance in data
43             );
44              
45             =cut
46              
47             my $this = shift;
48             my $class = ref($this) ? ref($this) : $this;
49 4     4 1 4545 my $self = {@_};
50 4 50       13 bless $self, $class;
51 4         19 return $self;
52 4         9 }
53 4         12  
54             =head1 PROPERTIES
55              
56             =head2 chart_title
57              
58             Set and returns the title of the Excel chart
59              
60             Default: Continuous Distribution Function (CDF)
61              
62             =cut
63              
64             my $self = shift;
65             $self->{'chart_title'} = shift if @_;
66             $self->{'chart_title'} = 'Continuous Distribution Function (CDF)' unless defined $self->{'chart_title'};
67 6     6 1 5397 return $self->{'chart_title'};
68 6 100       21 }
69 6 100       20  
70 6         27 =head2 chart_y_label
71              
72             Set and returns the Y axis label of the Excel chart
73              
74             Default: Probability
75              
76             =cut
77              
78             my $self = shift;
79             $self->{'chart_y_label'} = shift if @_;
80             $self->{'chart_y_label'} = 'Probability' unless defined $self->{'chart_y_label'};
81             return $self->{'chart_y_label'};
82 6     6 1 13 }
83 6 100       16  
84 6 100       19 =head2 chart_x_label
85 6         23  
86             Set and returns the X axis label of the Excel chart
87              
88             Default: ""
89              
90             =cut
91              
92             my $self = shift;
93             $self->{'chart_x_label'} = shift if @_;
94             $self->{'chart_x_label'} = '' unless defined $self->{'chart_x_label'};
95             return $self->{'chart_x_label'};
96             }
97 6     6 1 10  
98 6 100       14 =head2 chart_legend_display
99 6 100       16  
100 6         22 Set and returns the legend display property for the Excel chart
101              
102             Default: 1
103              
104             =cut
105              
106             my $self = shift;
107             $self->{'chart_legend_display'} = shift if @_;
108             $self->{'chart_legend_display'} = 1 unless defined $self->{'chart_legend_display'};
109             return $self->{'chart_legend_display'};
110             }
111              
112 2     2 1 3 =head2 chart_colors
113 2 50       7  
114 2 50       7 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.
115 2         8  
116             Default: ['#FF0000', '#800000', '#FFFF00', '#808000', '#00FF00', '#008000', '#00FFFF', '#008080', '#0000FF', '#000080', '#FF00FF', '#800080']
117              
118             =cut
119              
120             my $self = shift;
121             $self->{'chart_colors'} = shift if @_;
122             $self->{'chart_colors'} = ['#FF0000', '#800000', '#FFFF00', '#808000',
123             '#00FF00', '#008000', '#00FFFF', '#008080',
124             '#0000FF', '#000080', '#FF00FF', '#800080'] unless $self->{'chart_colors'};
125             die('Error: chart_colors property must be and array reference') unless ref($self->{'chart_colors'}) eq 'ARRAY';
126             return $self->{'chart_colors'};
127 6     6 1 12 }
128 6 100       19  
129             =head2 group_names_sort
130              
131 6 100       30 Set and returns the alphabetical sort option for the group names. A true value Perl-wise will sort the group names before generating the Excel Workbook and a false value will use the order in which the groups were discovered in the data to generate the group names order.
132 6 50       35  
133 6         23 Default: 0
134              
135             =cut
136              
137             my $self = shift;
138             $self->{'group_names_sort'} = shift if @_;
139             $self->{'group_names_sort'} = 0 unless defined $self->{'group_names_sort'};
140             return $self->{'group_names_sort'};
141             }
142              
143             =head1 METHODS
144              
145 6     6 1 25 =head2 generate
146 6 100       19  
147 6 100       26 Generates an Excel Workbook in memory and returns the Workbook as a data blob stored in the returned scalar variable.
148 6         18  
149             my $blob = $writer->generate(\@data);
150              
151             =cut
152              
153             my $self = shift;
154             my $data = shift; #isa [[group=>value], [], [], ...]
155             my @groups = ();
156             my $series = {};
157              
158             foreach my $row (@$data) {
159             my $group = $row->[0];
160             my $value = $row->[1];
161             if (not exists $series->{$group}) {
162 2     2 1 6 push @groups, $group; #keep order
163 2         2 $series->{$group} = {count=>0, values=>[]};
164 2         5 }
165 2         4 $series->{$group}->{'count'}++;
166             push @{$series->{$group}->{'values'}}, $value;
167 2         7 }
168 6000         6305  
169 6000         5468 if ($self->group_names_sort) {
170 6000 100       7372 @groups = sort @groups;
171 6         27 }
172 6         19  
173             #Open string scalar reference as file handle for Excel::Writer::XLSX to write to
174 6000         5792 open my $fh, '>', \my $content or die("Error: Filehandle open error: $!");
175 6000         5289  
  6000         8405  
176             #Object for Excel Workbook
177             require Excel::Writer::XLSX;
178 2 100       16 my $workbook = Excel::Writer::XLSX->new($fh);
179 1         9  
180             #Add a worksheet chart as first tab so it shows when document is opened
181             my $chart = $workbook->add_chart(type=>'scatter', subtype=>'straight');
182              
183 2 50   1   63 #Add worksheet for chart legend groups
  1         11  
  1         2  
  1         11  
184             my $worksheet_groups = $workbook->add_worksheet('groups');
185             $worksheet_groups->write_string(0, 0, 'Group');
186 2         1703 $worksheet_groups->write_string(0, 1, 'Index');
187 2         145234 $worksheet_groups->write_string(0, 2, 'Count');
188             my $group_index = 0;
189              
190 2         801 #Colors for data series lines and legend
191             my @colors = @{$self->chart_colors};
192              
193 2         3690 #foreach group add worksheet, data and chart series
194 2         438 my @stats_groups = ();
195 2         166  
196 2         97 foreach my $group (@groups) {
197 2         90  
198             #Add series label for legend
199             $group_index++;
200 2         4 $worksheet_groups->write_string($group_index, 0, $group);
  2         11  
201             $worksheet_groups->write_number($group_index, 1, $group_index);
202              
203 2         5 #Add worksheet
204             my $worksheet = $workbook->add_worksheet("group_$group_index");
205 2         5  
206             #Add data to worksheet
207             my @values = sort {$a <=> $b} @{$series->{$group}->{'values'}};
208 6         1427 my $values_count = scalar(@values);
209 6         35 $worksheet_groups->write_number($group_index, 2, $values_count);
210 6         434 my @cdf = ();
211             my $loop = 0;
212             foreach my $value (@values) {
213 6         240 $loop++;
214             push @cdf, $loop/$values_count;
215             }
216 6         1768 $worksheet->write_row(A1 => [$group, 'Probability'] );
  49510         45845  
  6         179  
217 6         25 $worksheet->write_row(A2 => [\@values, \@cdf] );
218 6         67  
219 6         425 my $stat = {};
220 6         14 $stat->{'min'} = $values[0];
221 6         16 $stat->{'max'} = $values[-1];
222 6000         5215 my $p50_group_index = first_index {$_ >= 0.5} @cdf;
223 6000         6378 $stat->{'p50'} = $values[$p50_group_index];
224             my $p90_group_index = first_index {$_ >= 0.9} @cdf;
225 6         84 $stat->{'p90'} = $values[$p90_group_index];
226 6         1571  
227             push @stats_groups, $stat;
228 6         507407  
229 6         28 #Add data references to chart
230 6         21 my $color = shift @colors || 'black';
231 6     3000   125 $chart->add_series(
  3000         2609  
232 6         34 line => {color => $color},
233 6     5400   41 name => sprintf('=%s!$A$%s', 'groups', $group_index + 1), #groups header row is 0
  5400         4647  
234 6         25 categories => sprintf('=%s!$A$2:$A$%s', $worksheet->get_name, $values_count + 1),
235             values => sprintf('=%s!$B$2:$B$%s', $worksheet->get_name, $values_count + 1),
236 6         14 );
237              
238             } #foreach group
239 6   50     25  
240 6         89 my $maxset;
241             if (@stats_groups) {
242             require List::Util;
243             require Math::Round::SignificantFigures;
244              
245             my $max = List::Util::max(map {$_->{'max'}} @stats_groups);
246             my $p50 = Math::Round::SignificantFigures::ceilsigfigs(List::Util::max(map {$_->{'p50'}} @stats_groups) * 1.5, 2);
247             my $p90 = Math::Round::SignificantFigures::ceilsigfigs(List::Util::min(map {$_->{'p90'}} @stats_groups) * 1.5, 2);
248             $maxset = List::Util::max($p50, $p90);
249 2         711 $maxset = undef if $maxset > $max;
250 2 50       10 }
251 2         27  
252 2         597 #Configure chart
253             $chart->set_title( name => $self->chart_title );
254 2         546 $chart->set_y_axis(name => $self->chart_y_label, min => 0 , max => 1 );
  6         21  
255 2         5 $chart->set_x_axis(name => $self->chart_x_label, max => $maxset);
  6         19  
256 2         58 $chart->set_legend(none => 1) unless $self->chart_legend_display;
  6         14  
257 2         33  
258 2 50       7 #Write Excel output to filehandle
259             $workbook->close;
260              
261             return $content;
262 2         14  
263 2         153 }
264 2         307  
265 2 100       176 =head2 generate_file
266              
267             Returns Excel file name in temp folder
268 2         106  
269             use File::Copy qw{move};
270 2         1620122 my $filename = $writer->generate_file(\@data);
271             move $filename, '.';
272              
273             =cut
274              
275             require DateTime;
276             require File::Temp;
277              
278             my $self = shift;
279             my $ymd = DateTime->now->ymd;
280             my ($fh, $filename) = File::Temp::tempfile("excel-cdf-$ymd-XXXXXX", SUFFIX => '.xlsx', DIR => File::Temp::tempdir());
281             binmode($fh);
282             my $blob = $self->generate(@_);
283             print $fh $blob;
284             close($fh);
285 2     2 1 692 return $filename;
286 2         383302 }
287              
288 2         5 =head1 SEE ALSO
289 2         15  
290 2         829 L<Excel::Writer::XLSX>
291 2         1353  
292 2         12 =head1 AUTHOR
293 2         11458  
294 2         45 Michael R. Davis
295 2         18  
296             =head1 COPYRIGHT AND LICENSE
297              
298             MIT License
299              
300             Copyright (c) 2022 Michael R. Davis
301              
302             Permission is hereby granted, free of charge, to any person obtaining a copy
303             of this software and associated documentation files (the "Software"), to deal
304             in the Software without restriction, including without limitation the rights
305             to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
306             copies of the Software, and to permit persons to whom the Software is
307             furnished to do so, subject to the following conditions:
308              
309             The above copyright notice and this permission notice shall be included in all
310             copies or substantial portions of the Software.
311              
312             THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
313             IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
314             FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
315             AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
316             LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
317             OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
318             SOFTWARE.
319              
320             =cut
321              
322             1;