view release on metacpan or  search on metacpan

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

    my ($raw_xlsx_data_in) = @_;

    my $workbook_in = Spreadsheet::ParseXLSX->new->parse(\$raw_xlsx_data_in);

    my $align_h = [ undef, qw( left center right fill justify center_across ) ];
    my $align_v = [ qw( top vcenter bottom vjustify ) ];

    my ( $workbook_data, $format_defs );
    for my $worksheet_in ( $workbook_in->worksheets ) {
        my ( $row_min, $row_max ) = $worksheet_in->row_range;
        my ( $col_min, $col_max ) = $worksheet_in->col_range;

        my $worksheet = {
            name          => $worksheet_in->get_name,
            row_heights   => [ ( $worksheet_in->get_row_heights )[ 0 .. $row_max ] ],
            col_widths    => [ ( $worksheet_in->get_col_widths  )[ 0 .. $col_max ] ],
            merged_areas  => scalar $worksheet_in->get_merged_areas,
            is_portrait   => ( $worksheet_in->is_portrait ) ? 1 : 0,
            paper         => $worksheet_in->get_paper,
            margin_left   => $worksheet_in->get_margin_left,
            margin_right  => $worksheet_in->get_margin_right,
            margin_top    => $worksheet_in->get_margin_top,
            margin_bottom => $worksheet_in->get_margin_bottom,
            margin_header => $worksheet_in->get_margin_header,
            margin_footer => $worksheet_in->get_margin_footer,
            print_scale   => $worksheet_in->get_print_scale,
            fit_to_pages  => [ $worksheet_in->get_fit_to_pages ],
        };

        for my $row ( $row_min .. $row_max ) {
            for my $col ( $col_min .. $col_max ) {
                if ( my $cell = $worksheet_in->get_cell( $row, $col ) ) {
                    my $format_id;
                    if ( my $format = $cell->get_format ) {
                        my $font         = $format->{Font};
                        my $fill         = $format->{Fill};
                        my $border_style = $format->{BdrStyle};
                        my $border_color = [ map {
                            s/^#//;
                            ( /^[0-9a-fA-F]+$/ ) ? hex($_) : undef;
                        } $format->{BdrColor} ];

                        my $format_data = {
                            (
                                ($font) ? (
                                    maybe font  => $font->{Name} || undef,
                                    maybe size  => $font->{Height} || undef,
                                    maybe color => ( $font->{Color} and $font->{Color} ne '#000000' )
                                        ? $font->{Color}
                                        : undef,
                                    maybe bold      => ( $font->{Bold} ) ? 1 : undef,
                                    maybe italic    => ( $font->{Italic} ) ? 1 : undef,
                                    maybe underline => ( $font->{UnderlineStyle} )
                                        ? $font->{UnderlineStyle}
                                        : undef,
                                    maybe font_strikeout => ( $font->{Strikeout} ) ? 1 : undef,
                                    maybe font_script    => ( $font->{Super} ) ? $font->{Super} : undef,
                                ) : (),
                            ),
                            maybe num_format => $format->{FmtIdx},
                            maybe locked     => ( $format->{Lock} ) ? 1 : undef,
                            maybe hidden     => ( $format->{Hidden} ) ? 1 : undef,
                            maybe align      => ( $format->{AlignH} )
                                ? $align_h->[ $format->{AlignH} ]
                                : undef,
                            maybe valign => ( defined $format->{AlignV} )
                                ? $align_v->[ $format->{AlignV} ]
                                : undef,
                            maybe text_wrap     => ( $format->{Wrap} ) ? 1 : undef,
                            maybe rotation      => $format->{Rotate} || undef,
                            maybe indent        => $format->{Indent} || undef,
                            maybe shrink        => $format->{Shrink} || undef,
                            maybe text_justlast => ( $format->{JustLast} ) ? 1 : undef,
                            (
                                ($fill) ? (
                                    maybe pattern  => $fill->[0] || undef,
                                    maybe bg_color => $fill->[1],
                                    maybe fg_color => ( $fill->[2] and uc $fill->[2] ne '#FFFFFF' )
                                        ? $fill->[2]
                                        : undef,
                                ) : (),
                            ),
                            (
                                ($border_style) ? (
                                    maybe left   => $border_style->[0],
                                    maybe right  => $border_style->[1],
                                    maybe top    => $border_style->[2],
                                    maybe bottom => $border_style->[3],
                                ) : (),
                            ),
                            (
                                ($border_color) ? (
                                    maybe left_color   => $border_color->[0],
                                    maybe right_color  => $border_color->[1],
                                    maybe top_color    => $border_color->[2],
                                    maybe bottom_color => $border_color->[3],
                                ) : (),
                            ),
                        };

                        if ( $format_data and %$format_data ) {
                            my $format_yaml = Dump($format_data);
                            unless ( exists $format_defs->{$format_yaml} ) {
                                push( @{ $workbook_data->{formats} }, $format_data );
                                $format_defs->{$format_yaml} = @{ $workbook_data->{formats} } - 1;
                            }
                            $format_id = $format_defs->{$format_yaml};
                        }
                    }

                    my $value = $cell->unformatted;
                    $worksheet->{cells}{$row}{$col} = {
                        value           => $value,
                        formatted       => $cell->value,
                        maybe format_id => $format_id,
                    } if ( defined $format_id or defined $value and $value =~ /\S/ );
                }
            }
        }

        push( @{ $workbook_data->{worksheets} }, $worksheet );