blib/lib/Excel/XLSX.pm

filter: Statementstmt 90.0% 18 / 20 Branchbran n/a Conditioncond n/a MC/DCmcdc n/a Subroutinesub 100% 7 / 7 Podpod n/a totaltotal 92.5% 25 / 27 CC 1 SCAR 0.4
CC
1
Cov
90%
BEGIN
1.3
BEGIN
1.0
BEGIN
1.0
CRAP 1.0
↑ Summary
1 package Excel::XLSX;
2 # ABSTRACT: Read and write Excel XLSX data
3  
4 2 ▶use 5.014;
Subroutine BEGIN: called CC=1 SCAR=0.0
5 2 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
6 2 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
7  
8 2 ▶use base 'Exporter';
Subroutine BEGIN: called CC=1 SCAR=0.0
9  
10 2 ▶use Excel::Writer::XLSX;
Subroutine BEGIN: called CC=1 SCAR=0.0
11 2 ▶use PerlX::Maybe;
Subroutine BEGIN: called CC=1 SCAR=0.0
12 0 ▶use Spreadsheet::ParseXLSX;
Subroutine BEGIN: called CC=1 SCAR=2.6
13 use YAML::XS 'Dump';
14  
15 our $VERSION = '1.04'; # VERSION
16  
17 our @EXPORT_OK = qw( from_xlsx to_xlsx );
18  
19 sub from_xlsx {
20 my ($raw_xlsx_data_in) = @_;
21  
22 my $workbook_in = Spreadsheet::ParseXLSX->new->parse(\$raw_xlsx_data_in);
23  
24 my $align_h = [ undef, qw( left center right fill justify center_across ) ];
25 my $align_v = [ qw( top vcenter bottom vjustify ) ];
26  
27 my ( $workbook_data, $format_defs );
28 for my $worksheet_in ( $workbook_in->worksheets ) {
29 my ( $row_min, $row_max ) = $worksheet_in->row_range;
30 my ( $col_min, $col_max ) = $worksheet_in->col_range;
31  
32 my $worksheet = {
33 name => $worksheet_in->get_name,
34 row_heights => [ ( $worksheet_in->get_row_heights )[ 0 .. $row_max ] ],
35 col_widths => [ ( $worksheet_in->get_col_widths )[ 0 .. $col_max ] ],
36 merged_areas => scalar $worksheet_in->get_merged_areas,
37 is_portrait => ( $worksheet_in->is_portrait ) ? 1 : 0,
38 paper => $worksheet_in->get_paper,
39 margin_left => $worksheet_in->get_margin_left,
40 margin_right => $worksheet_in->get_margin_right,
41 margin_top => $worksheet_in->get_margin_top,
42 margin_bottom => $worksheet_in->get_margin_bottom,
43 margin_header => $worksheet_in->get_margin_header,
44 margin_footer => $worksheet_in->get_margin_footer,
45 print_scale => $worksheet_in->get_print_scale,
46 fit_to_pages => [ $worksheet_in->get_fit_to_pages ],
47 };
48  
49 for my $row ( $row_min .. $row_max ) {
50 for my $col ( $col_min .. $col_max ) {
51 if ( my $cell = $worksheet_in->get_cell( $row, $col ) ) {
52 my $format_id;
53 if ( my $format = $cell->get_format ) {
54 my $font = $format->{Font};
55 my $fill = $format->{Fill};
56 my $border_style = $format->{BdrStyle};
57 my $border_color = [ map {
58 s/^#//;
59 ( /^[0-9a-fA-F]+$/ ) ? hex($_) : undef;
60 } $format->{BdrColor} ];
61  
62 my $format_data = {
63 (
64 ($font) ? (
65 maybe font => $font->{Name} || undef,
66 maybe size => $font->{Height} || undef,
67 maybe color => ( $font->{Color} and $font->{Color} ne '#000000' )
68 ? $font->{Color}
69 : undef,
70 maybe bold => ( $font->{Bold} ) ? 1 : undef,
71 maybe italic => ( $font->{Italic} ) ? 1 : undef,
72 maybe underline => ( $font->{UnderlineStyle} )
73 ? $font->{UnderlineStyle}
74 : undef,
75 maybe font_strikeout => ( $font->{Strikeout} ) ? 1 : undef,
76 maybe font_script => ( $font->{Super} ) ? $font->{Super} : undef,
77 ) : (),
78 ),
79 maybe num_format => $format->{FmtIdx},
80 maybe locked => ( $format->{Lock} ) ? 1 : undef,
81 maybe hidden => ( $format->{Hidden} ) ? 1 : undef,
82 maybe align => ( $format->{AlignH} )
83 ? $align_h->[ $format->{AlignH} ]
84 : undef,
85 maybe valign => ( defined $format->{AlignV} )
86 ? $align_v->[ $format->{AlignV} ]
87 : undef,
88 maybe text_wrap => ( $format->{Wrap} ) ? 1 : undef,
89 maybe rotation => $format->{Rotate} || undef,
90 maybe indent => $format->{Indent} || undef,
91 maybe shrink => $format->{Shrink} || undef,
92 maybe text_justlast => ( $format->{JustLast} ) ? 1 : undef,
93 (
94 ($fill) ? (
95 maybe pattern => $fill->[0] || undef,
96 maybe bg_color => $fill->[1],
97 maybe fg_color => ( $fill->[2] and uc $fill->[2] ne '#FFFFFF' )
98 ? $fill->[2]
99 : undef,
100 ) : (),
101 ),
102 (
103 ($border_style) ? (
104 maybe left => $border_style->[0],
105 maybe right => $border_style->[1],
106 maybe top => $border_style->[2],
107 maybe bottom => $border_style->[3],
108 ) : (),
109 ),
110 (
111 ($border_color) ? (
112 maybe left_color => $border_color->[0],
113 maybe right_color => $border_color->[1],
114 maybe top_color => $border_color->[2],
115 maybe bottom_color => $border_color->[3],
116 ) : (),
117 ),
118 };
119  
120 if ( $format_data and %$format_data ) {
121 my $format_yaml = Dump($format_data);
122 unless ( exists $format_defs->{$format_yaml} ) {
123 push( @{ $workbook_data->{formats} }, $format_data );
124 $format_defs->{$format_yaml} = @{ $workbook_data->{formats} } - 1;
125 }
126 $format_id = $format_defs->{$format_yaml};
127 }
128 }
129  
130 my $value = $cell->unformatted;
131 $worksheet->{cells}{$row}{$col} = {
132 value => $value,
133 formatted => $cell->value,
134 maybe format_id => $format_id,
135 } if ( defined $format_id or defined $value and $value =~ /\S/ );
136 }
137 }
138 }
139  
140 push( @{ $workbook_data->{worksheets} }, $worksheet );
141 }
142  
143 return $workbook_data;
144 }
145  
146 sub to_xlsx {
147 my ($workbook_data) = @_;
148  
149 open( my $fh, '>:raw', \my $raw_xlsx_data_out );
150 my $workbook_out = Excel::Writer::XLSX->new($fh);
151  
152 my $formats = [
153 map {
154 my $valign = delete $_->{valign};
155 my $format = $workbook_out->add_format(%$_);
156 $format->set_align($valign) if ( defined $valign );
157 $format;
158 } @{ $workbook_data->{formats} }
159 ];
160  
161 for my $worksheet ( @{ $workbook_data->{worksheets} } ) {
162 my $worksheet_out = $workbook_out->add_worksheet( $worksheet->{name} );
163  
164 unless ( $worksheet->{is_portrait} ) {
165 $worksheet_out->set_landscape;
166 }
167 else {
168 $worksheet_out->set_portrait;
169 }
170 $worksheet_out->set_paper ( $worksheet->{paper} ) if ( exists $worksheet->{paper} );
171 $worksheet_out->set_margin_left ( $worksheet->{margin_left} ) if ( exists $worksheet->{margin_left} );
172 $worksheet_out->set_margin_right ( $worksheet->{margin_right} ) if ( exists $worksheet->{margin_right} );
173 $worksheet_out->set_margin_top ( $worksheet->{margin_top} ) if ( exists $worksheet->{margin_top} );
174 $worksheet_out->set_margin_bottom( $worksheet->{margin_bottom} ) if ( exists $worksheet->{margin_bottom} );
175 $worksheet_out->set_header( undef, $worksheet->{margin_header} ) if ( exists $worksheet->{margin_header} );
176 $worksheet_out->set_footer( undef, $worksheet->{margin_footer} ) if ( exists $worksheet->{margin_header} );
177 $worksheet_out->set_print_scale( $worksheet->{print_scale} ) if ( exists $worksheet->{print_scale} );
178 $worksheet_out->fit_to_pages( @{ $worksheet->{fit_to_pages} } ) if ( exists $worksheet->{fit_to_pages} );
179  
180 for my $row ( keys %{ $worksheet->{cells} } ) {
181 for my $col ( keys %{ $worksheet->{cells}{$row} } ) {
182 $worksheet_out->write(
183 $row,
184 $col,
185 $worksheet->{cells}{$row}{$col}{value},
186 ( exists $worksheet->{cells}{$row}{$col}{format_id} )
187 ? $formats->[ $worksheet->{cells}{$row}{$col}{format_id} ]
188 : undef,
189 );
190 }
191 }
192  
193 for my $area ( @{ $worksheet->{merged_areas} } ) {
194 my ( $first_row, $first_col, $last_row, $last_col ) = @$area;
195  
196 my $first_format = ( exists $worksheet->{cells}{$first_row}{$first_col}{format_id} )
197 ? $formats->[ $worksheet->{cells}{$first_row}{$first_col}{format_id} ]
198 : undef;
199  
200 my $last_format = ( exists $worksheet->{cells}{$last_row}{$last_col}{format_id} )
201 ? $formats->[ $worksheet->{cells}{$last_row}{$last_col}{format_id} ]
202 : undef;
203  
204 my $format = $workbook_out->add_format;
205 $format->copy( $first_format // $last_format );
206  
207 if ($last_format) {
208 my (
209 $bottom, $bottom_color,
210 $diag_border, $diag_color, $diag_type,
211 $left, $left_color,
212 $right, $right_color,
213 $top, $top_color
214 ) = split( ':', $last_format->get_border_key );
215  
216 $format->set_right( $right );
217 $format->set_bottom( $bottom );
218 $format->set_right_color( $right_color );
219 $format->set_bottom_color( $bottom_color );
220 }
221  
222 $worksheet_out->merge_range(
223 @$area,
224 $worksheet->{cells}{$first_row}{$first_col}{value},
225 $format,
226 );
227 }
228  
229 my ( $row, $col ) = ( 0, 0 );
230 $worksheet_out->set_row ( $row++, $_ ) for ( @{ $worksheet->{row_heights} } );
231 $worksheet_out->set_column( $col, $col++, $_ ) for ( @{ $worksheet->{col_widths } } );
232 $worksheet_out->set_column( 0, 0, $worksheet->{col_widths }[0] );
233 }
234 $workbook_out->close;
235  
236 return $raw_xlsx_data_out;
237 }
238  
239 1;
240  
241 __END__
↑ Summary