| 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__ | ||