Great monks and others, hi
I'm trying to "merge" several single-worksheeded Excel files into one multi-worksheeded huge file.
I do use Spreadsheet::ParseXLSX, Spreadsheet::ParseExcel::Format and Excel::Writer::XLSX to acheive it and it do work corectly. For data only.
I can't do anything with the images!
Somebody can help?
Here is the wonderfull but not completed code:
# Pour une meilleure programmation use strict; use warnings; $|++; use Spreadsheet::ParseXLSX; use Spreadsheet::ParseExcel::Format; use Excel::Writer::XLSX; my @files=("file1.xlsx","file2.xlsx","file3.xlsx"); my $fichier_sortie="out_merged.xlsx"; my $out_workbook = Excel::Writer::XLSX->new($fichier_sortie); my $parser = Spreadsheet::ParseXLSX->new; for my $f (0..$#files) { print "=== ".$files[$f]." =======================\n"; my $in_workbook = $parser->parse($files[$f]); if ( !defined $in_workbook ) { die $parser->error(), ".\n"; } my $in_worksheet = $in_workbook->worksheet(0); my $sheet = $out_workbook->add_worksheet($in_worksheet->get_name() +); my ( $row_min, $row_max ) = $in_worksheet->row_range(); my ( $col_min, $col_max ) = $in_worksheet->col_range(); for my $row ($row_min..$row_max) { for my $col ($col_min..$col_max) { my $cell = $in_worksheet->get_cell( $row, $col ); next unless $cell; my $in_format = $cell->get_format(); my $out_format= $out_workbook->add_format(); CopyFormat($in_format, $out_format); $sheet->write($row, $col, $cell->value(),$out_format); } } if(defined($in_worksheet->get_merged_areas())) { my $merged_areas = $in_worksheet->get_merged_areas(); my $cnt=0; while (defined($merged_areas->[$cnt])) { my $first_row=$merged_areas->[$cnt]->[0]; my $first_col=$merged_areas->[$cnt]->[1]; my $last_row=$merged_areas->[$cnt]->[2]; my $last_col=$merged_areas->[$cnt]->[3]; my $cell = $in_worksheet->get_cell( $first_row, $first_col + ); my $in_format = $cell->get_format(); my $out_format= $out_workbook->add_format(); CopyFormat($in_format, $out_format); $sheet->merge_range( $first_row, $first_col, $last_row, $l +ast_col, $cell->value(), $out_format ); $cnt++; } } } $out_workbook->close(); ## SUBS ############################################################## +######### sub CopyFormat() { use Switch; my $in_format=shift; my $out_format=shift; # Font my $font=$in_format->{Font}; $out_format->set_font($font->{Name}); $out_format->set_bold($font->{Bold}); $out_format->set_italic($font->{Italic}); $out_format->set_size($font->{Height}); $out_format->set_underline($font->{UnderlineStyle}); $out_format->set_color($font->{Color}); $out_format->set_font_strikeout($font->{Strikeout}); $out_format->set_font_script($font->{Super}); #Format my $align; switch( $in_format->{AlignH}) { #case 0 { $align='No alignment';} case 1 { $align='left';} case 2 { $align='center';} case 3 { $align='right';} case 4 { $align='fill';} case 5 { $align='justify';} case 6 { $align='center_across';} #case 7 { $align='Distributed/Equal spaced';} else { $align='';} } $out_format->set_align($align); switch( $in_format->{AlignV}) { case 0 { $align='top';} case 1 { $align='vcenter';} case 2 { $align='bottom';} case 3 { $align='vjustify';} #case 4 { $align='Distributed/Equal spaced';} else { $align='';} } $out_format->set_align($align); $out_format->set_indent($in_format->{Indent}); $out_format->set_text_wrap($in_format->{Wrap}); $out_format->set_shrink($in_format->{Shrink}); my $rotation=$in_format->{Rotate}; if (!defined($rotation)) { $rotation=0; } elsif ($rotation==255) { $rotation=270; } $out_format->set_rotation($rotation); $out_format->set_text_justlast($in_format->{JustLast}); # $in_format->{ReadDir}); my $border=$in_format->{BdrStyle}; $out_format->set_bottom($border->[3]); $out_format->set_top($border->[2]); $out_format->set_left($border->[0]); $out_format->set_right($border->[1]); my $border_color=$in_format->{BdrColor}; if(defined($border_color->[3])) {$out_format->set_bottom_color($bo +rder_color->[3]);} if(defined($border_color->[2])) {$out_format->set_top_color($borde +r_color->[2]);} if(defined($border_color->[0])) {$out_format->set_left_color($bord +er_color->[0]);} if(defined($border_color->[1])) {$out_format->set_right_color($bor +der_color->[1]);} # (my$kind, my$style, my$color)=$in_format->{BdrDiag}; # $out_format->set_diag_type($kind); # $out_format->set_diag_border($style); # $out_format->set_diag_color($color); my $fill=$in_format->{Fill}; $out_format->set_pattern($fill->[0]); if ($fill->[0]!=0) { $out_format->set_fg_color($fill->[2]); $out_format->set_bg_color($fill->[1]); } # $in_format->{Lock}); # $in_format->{Hidden}); # $in_format->{Style}); }
In reply to Spreadsheet::ParseXLSX and images in Excel by the_hawk_1
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |